BDNA Data Platform 5.4 July 2017 (Oracle) CAT_HW_TAXONOMY2012 - Bridge table between CAT_HW_PRODUCT and TAXONOMY2012
|
View Definition:
WITH tax_full AS ( SELECT CAT_HW_TAXONOMY2012_ID, CREATE_DATE, LAST_MODIFIED_DATE, TAXONOMY_VERSION, CASE WHEN CAT_TYPE='PRI' THEN TAXONOMY_DEFAULT
WHEN CAT_TYPE='PUB' THEN
CASE WHEN NVL(DEF_SUM,0) = 0 THEN TAXONOMY_DEFAULT
WHEN DEF_SUM > 0 THEN NULL
END
END TAXONOMY_DEFAULT , CAT_TAG_ID, CAT_HW_PRODUCT_ID, CAT_TAXONOMY2012_ID, TO_BE_DELETED, TO_BE_DELETED_ON, DELETE_REASON, PRIVATE_FLAG, IS_PRIVATE, PRIVATE_TYPE, REPLACEMENT_ID,CAT_TYPE FROM ( SELECT f_tcat.CAT_HW_TAXONOMY2012_ID, f_tcat.CREATE_DATE, f_tcat.LAST_MODIFIED_DATE, f_tcat.TAXONOMY_VERSION, f_tcat.TAXONOMY_DEFAULT, f_tcat.CAT_TAG_ID, f_tcat.CAT_HW_PRODUCT_ID, f_tcat.CAT_TAXONOMY2012_ID, f_tcat.TO_BE_DELETED, f_tcat.TO_BE_DELETED_ON, f_tcat.DELETE_REASON, f_tcat.PRIVATE_FLAG, f_tcat.IS_PRIVATE, f_tcat.PRIVATE_TYPE, f_tcat.REPLACEMENT_ID,'PUB' CAT_TYPE
,SUM(tpc.TAXONOMY_DEFAULT) OVER (partition by tpc.CAT_HW_PRODUCT_ID,tpc.CAT_TAG_ID) DEF_SUM
,CASE WHEN tpc.CAT_HW_PRODUCT_ID IS NOT NULL THEN
ROW_NUMBER() OVER (PARTITION BY tpc.CAT_HW_PRODUCT_ID,tpc.CAT_TAG_ID ORDER BY tpc.CAT_HW_PRODUCT_ID desc)
ELSE 1 END RN FROM TCAT_HW_TAXONOMY2012 f_tcat
LEFT JOIN TPC_HW_TAXONOMY2012 tpc ON f_tcat.CAT_HW_PRODUCT_ID=tpc.CAT_HW_PRODUCT_ID AND f_tcat.CAT_TAG_ID= tpc.CAT_TAG_ID UNION ALL SELECT tpc.CAT_HW_TAXONOMY2012_ID, tpc.CREATE_DATE, tpc.LAST_MODIFIED_DATE, tpc.TAXONOMY_VERSION, tpc.TAXONOMY_DEFAULT, tpc.CAT_TAG_ID, tpc.CAT_HW_PRODUCT_ID, tpc.CAT_TAXONOMY2012_ID, tpc.TO_BE_DELETED, tpc.TO_BE_DELETED_ON, tpc.DELETE_REASON, tpc.PRIVATE_FLAG, tpc.IS_PRIVATE, tpc.PRIVATE_TYPE, tpc.REPLACEMENT_ID,'PRI' CAT_TYPE
,NULL DEF_SUM
,NULL RN FROM TPC_HW_TAXONOMY2012 tpc ) tax_v WHERE 1=1 AND NVL(RN,1) = 1 ) SELECT CAT_HW_TAXONOMY2012_ID, CREATE_DATE, LAST_MODIFIED_DATE, TAXONOMY_VERSION, TAXONOMY_DEFAULT, CAT_TAG_ID, CAT_HW_PRODUCT_ID, CAT_TAXONOMY2012_ID, TO_BE_DELETED, TO_BE_DELETED_ON, DELETE_REASON, PRIVATE_FLAG, IS_PRIVATE, PRIVATE_TYPE, REPLACEMENT_ID FROM TAX_FULL f_tcat
WHERE CAT_TYPE='PUB'
AND NOT EXISTS
(SELECT 1
FROM TPC_HW_TAXONOMY2012 tpc_tab
WHERE tpc_tab.CAT_HW_TAXONOMY2012_ID=f_tcat.CAT_HW_TAXONOMY2012_ID
) UNION ALL SELECT CAT_HW_TAXONOMY2012_ID, CREATE_DATE, LAST_MODIFIED_DATE, TAXONOMY_VERSION, TAXONOMY_DEFAULT, CAT_TAG_ID, CAT_HW_PRODUCT_ID, CAT_TAXONOMY2012_ID, TO_BE_DELETED, TO_BE_DELETED_ON, DELETE_REASON, PRIVATE_FLAG, IS_PRIVATE, PRIVATE_TYPE, REPLACEMENT_ID FROM (SELECT COALESCE(tpc.CAT_HW_TAXONOMY2012_ID,tcat.CAT_HW_TAXONOMY2012_ID) CAT_HW_TAXONOMY2012_ID,COALESCE(tpc.CREATE_DATE,tcat.CREATE_DATE) CREATE_DATE,GREATEST(NVL(tpc.LAST_MODIFIED_DATE,TO_DATE('31-DEC-1900','DD-MON-YYYY')),tcat.LAST_MODIFIED_DATE) LAST_MODIFIED_DATE,tcat.TAXONOMY_VERSION,CASE WHEN COALESCE(tpc.TAXONOMY_DEFAULT,tcat.TAXONOMY_DEFAULT) = 0 AND tcat.TAXONOMY_DEFAULT =1 THEN tcat.TAXONOMY_DEFAULT
ELSE COALESCE(tpc.TAXONOMY_DEFAULT,tcat.TAXONOMY_DEFAULT)
END TAXONOMY_DEFAULT,tcat.CAT_TAG_ID,tcat.CAT_HW_PRODUCT_ID,tcat.CAT_TAXONOMY2012_ID,COALESCE(tpc.TO_BE_DELETED,tcat.TO_BE_DELETED) TO_BE_DELETED,COALESCE(tpc.TO_BE_DELETED_ON,tcat.TO_BE_DELETED_ON) TO_BE_DELETED_ON,COALESCE(tpc.DELETE_REASON,tcat.DELETE_REASON) DELETE_REASON,COALESCE(uuid.PRIVATE_FLAG,tcat.PRIVATE_FLAG) PRIVATE_FLAG, COALESCE(uuid.IS_PRIVATE,tcat.IS_PRIVATE) IS_PRIVATE, COALESCE(uuid.PRIVATE_TYPE,tcat.PRIVATE_TYPE) PRIVATE_TYPE, tcat.REPLACEMENT_ID FROM TPC_HW_TAXONOMY2012 tpc
LEFT JOIN TAX_FULL tcat ON tcat.CAT_HW_TAXONOMY2012_ID = tpc.CAT_HW_TAXONOMY2012_ID
INNER JOIN TPC_UUID uuid ON tpc.CAT_HW_TAXONOMY2012_ID = uuid.CAT_UUID_ID
AND SIGN(tpc.CAT_HW_TAXONOMY2012_ID )!= -1 AND tcat.CAT_TYPE='PUB' ) cat_tab UNION ALL SELECT tpc.CAT_HW_TAXONOMY2012_ID,tpc.CREATE_DATE,tpc.LAST_MODIFIED_DATE,tpc.TAXONOMY_VERSION,tpc.TAXONOMY_DEFAULT,tpc.CAT_TAG_ID,tpc.CAT_HW_PRODUCT_ID,tpc.CAT_TAXONOMY2012_ID,tpc.TO_BE_DELETED,tpc.TO_BE_DELETED_ON,tpc.DELETE_REASON,tpc.PRIVATE_FLAG,tpc.IS_PRIVATE,tpc.PRIVATE_TYPE,tpc.REPLACEMENT_ID FROM TPC_HW_TAXONOMY2012 tpc WHERE SIGN(tpc.CAT_HW_TAXONOMY2012_ID )= -1