VIEW: APPS.DDR_R_MFG_ITEM_HCHY_UNION_V

Source

View Text - Preformatted

SELECT MFG_ORG_CD,MFG_ITEM_CMPNY_ID AS HCHY_ID,MFG_CMPNY_CD AS HCHY_CD,'COMPANY' AS HCHY_LVL,TO_DATE('01-JAN-1900','DD-MON-YYYY') AS EFF_FROM_DT,TO_DATE('01-JAN-2400','DD-MON-YYYY') AS EFF_TO_DT FROM DDR_R_MFG_ITEM_CMPNY UNION SELECT MFG_ORG_CD,MFG_ITEM_DIV_ID AS HCHY_ID,MFG_DIV_CD AS HCHY_CD,'DIVISION' AS HCHY_LVL,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_MFG_ITEM_DIV UNION SELECT MFG_ORG_CD,MFG_ITEM_GRP_ID AS HCHY_ID,MFG_GRP_CD AS HCHY_CD,'GROUP' AS HCHY_LVL,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_MFG_ITEM_GRP UNION SELECT MFG_ORG_CD,MFG_ITEM_CLASS_ID AS HCHY_ID,MFG_CLASS_CD AS HCHY_CD,'CLASS' AS HCHY_LVL,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_MFG_ITEM_CLASS UNION SELECT MFG_ORG_CD,MFG_ITEM_SBC_ID AS HCHY_ID,MFG_SBC_CD AS HCHY_CD,'SUBCLASS' AS HCHY_LVL,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_MFG_ITEM_SBC UNION SELECT MFG_ORG_CD,MFG_ITEM_ID AS HCHY_ID,MFG_ITEM_NBR AS HCHY_CD,'ITEM' AS HCHY_LVL,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_MFG_ITEM UNION SELECT MFG_ORG_CD,MFG_SKU_ITEM_ID AS HCHY_ID,MFG_SKU_ITEM_NBR AS HCHY_CD,'SKU' AS HCHY_LVL,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_MFG_SKU_ITEM UNION SELECT '-1',-1,'-1','NA',TO_DATE('01-JAN-1900','DD-MON-YYYY') AS EFF_FROM_DT,TO_DATE('01-JAN-2400','DD-MON-YYYY') AS EFF_TO_DT FROM DUAL

View Text - HTML Formatted

SELECT MFG_ORG_CD
, MFG_ITEM_CMPNY_ID AS HCHY_ID
, MFG_CMPNY_CD AS HCHY_CD
, 'COMPANY' AS HCHY_LVL
, TO_DATE('01-JAN-1900'
, 'DD-MON-YYYY') AS EFF_FROM_DT
, TO_DATE('01-JAN-2400'
, 'DD-MON-YYYY') AS EFF_TO_DT
FROM DDR_R_MFG_ITEM_CMPNY UNION SELECT MFG_ORG_CD
, MFG_ITEM_DIV_ID AS HCHY_ID
, MFG_DIV_CD AS HCHY_CD
, 'DIVISION' AS HCHY_LVL
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_MFG_ITEM_DIV UNION SELECT MFG_ORG_CD
, MFG_ITEM_GRP_ID AS HCHY_ID
, MFG_GRP_CD AS HCHY_CD
, 'GROUP' AS HCHY_LVL
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_MFG_ITEM_GRP UNION SELECT MFG_ORG_CD
, MFG_ITEM_CLASS_ID AS HCHY_ID
, MFG_CLASS_CD AS HCHY_CD
, 'CLASS' AS HCHY_LVL
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_MFG_ITEM_CLASS UNION SELECT MFG_ORG_CD
, MFG_ITEM_SBC_ID AS HCHY_ID
, MFG_SBC_CD AS HCHY_CD
, 'SUBCLASS' AS HCHY_LVL
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_MFG_ITEM_SBC UNION SELECT MFG_ORG_CD
, MFG_ITEM_ID AS HCHY_ID
, MFG_ITEM_NBR AS HCHY_CD
, 'ITEM' AS HCHY_LVL
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_MFG_ITEM UNION SELECT MFG_ORG_CD
, MFG_SKU_ITEM_ID AS HCHY_ID
, MFG_SKU_ITEM_NBR AS HCHY_CD
, 'SKU' AS HCHY_LVL
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_MFG_SKU_ITEM UNION SELECT '-1'
, -1
, '-1'
, 'NA'
, TO_DATE('01-JAN-1900'
, 'DD-MON-YYYY') AS EFF_FROM_DT
, TO_DATE('01-JAN-2400'
, 'DD-MON-YYYY') AS EFF_TO_DT
FROM DUAL