VIEW: APPS.DDR_R_RTL_ITEM_HCHY_UNION_V

Source

View Text - Preformatted

SELECT RTL_ORG_CD,RTL_ITEM_CMPNY_ID AS HCHY_ID,RTL_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_RTL_ITEM_CMPNY UNION SELECT RTL_ORG_CD,RTL_ITEM_DIV_ID AS HCHY_ID,RTL_DIV_CD AS HCHY_CD,'DIVISION' AS HCHY_LVL,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_RTL_ITEM_DIV UNION SELECT RTL_ORG_CD,RTL_ITEM_GRP_ID AS HCHY_ID,RTL_GRP_CD AS HCHY_CD,'GROUP' AS HCHY_LVL,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_RTL_ITEM_GRP UNION SELECT RTL_ORG_CD,RTL_ITEM_GRP_ID AS HCHY_ID,RTL_GRP_CD AS HCHY_CD,'DEPARTMENT' AS HCHY_LVL,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_RTL_ITEM_DEPT UNION SELECT RTL_ORG_CD,RTL_ITEM_CLASS_ID AS HCHY_ID,RTL_CLASS_CD AS HCHY_CD,'CLASS' AS HCHY_LVL,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_RTL_ITEM_CLASS UNION SELECT RTL_ORG_CD,RTL_ITEM_SBC_ID AS HCHY_ID,RTL_SBC_CD AS HCHY_CD,'SUBCLASS' AS HCHY_LVL,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_RTL_ITEM_SBC UNION SELECT RTL_ORG_CD,RTL_ITEM_ID AS HCHY_ID,RTL_ITEM_NBR AS HCHY_CD,'ITEM' AS HCHY_LVL,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_RTL_ITEM UNION SELECT RTL_ORG_CD,RTL_SKU_ITEM_ID AS HCHY_ID,RTL_SKU_ITEM_NBR AS HCHY_CD,'SKU' AS HCHY_LVL,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_RTL_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 UNION SELECT ORG_CD, -1 AS HCHY_ID, '-1' AS HCHY_CD, 'NA' 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_ORG

View Text - HTML Formatted

SELECT RTL_ORG_CD
, RTL_ITEM_CMPNY_ID AS HCHY_ID
, RTL_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_RTL_ITEM_CMPNY UNION SELECT RTL_ORG_CD
, RTL_ITEM_DIV_ID AS HCHY_ID
, RTL_DIV_CD AS HCHY_CD
, 'DIVISION' AS HCHY_LVL
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_RTL_ITEM_DIV UNION SELECT RTL_ORG_CD
, RTL_ITEM_GRP_ID AS HCHY_ID
, RTL_GRP_CD AS HCHY_CD
, 'GROUP' AS HCHY_LVL
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_RTL_ITEM_GRP UNION SELECT RTL_ORG_CD
, RTL_ITEM_GRP_ID AS HCHY_ID
, RTL_GRP_CD AS HCHY_CD
, 'DEPARTMENT' AS HCHY_LVL
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_RTL_ITEM_DEPT UNION SELECT RTL_ORG_CD
, RTL_ITEM_CLASS_ID AS HCHY_ID
, RTL_CLASS_CD AS HCHY_CD
, 'CLASS' AS HCHY_LVL
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_RTL_ITEM_CLASS UNION SELECT RTL_ORG_CD
, RTL_ITEM_SBC_ID AS HCHY_ID
, RTL_SBC_CD AS HCHY_CD
, 'SUBCLASS' AS HCHY_LVL
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_RTL_ITEM_SBC UNION SELECT RTL_ORG_CD
, RTL_ITEM_ID AS HCHY_ID
, RTL_ITEM_NBR AS HCHY_CD
, 'ITEM' AS HCHY_LVL
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_RTL_ITEM UNION SELECT RTL_ORG_CD
, RTL_SKU_ITEM_ID AS HCHY_ID
, RTL_SKU_ITEM_NBR AS HCHY_CD
, 'SKU' AS HCHY_LVL
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_RTL_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 UNION SELECT ORG_CD
, -1 AS HCHY_ID
, '-1' AS HCHY_CD
, 'NA' 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_ORG