VIEW: APPS.DDR_R_ORG_HCHY_UNION_V

Source

View Text - Preformatted

SELECT ORG_CD,-1 AS ORG_HCHY_ID,ORG_CD AS HCHY_CD,'ORGANIZATION' AS HCHY_LVL,MFG_ORG_CD,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 UNION SELECT ORG_CD,ORG_CHAIN_ID,CHAIN_CD,'CHAIN' AS HCHY_LVL,MFG_ORG_CD,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_ORG_CHAIN UNION SELECT ORG_CD,ORG_AREA_ID,AREA_CD,'AREA' AS HCHY_LVL,MFG_ORG_CD,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_ORG_AREA UNION SELECT ORG_CD,ORG_RGN_ID,RGN_CD,'REGION' AS HCHY_LVL,MFG_ORG_CD,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_ORG_RGN UNION SELECT ORG_CD,ORG_DSTRCT_ID,DSTRCT_CD,'DISTRICT' AS HCHY_LVL,MFG_ORG_CD,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_ORG_DSTRCT UNION SELECT ORG_CD,ORG_BSNS_UNIT_ID,BSNS_UNIT_CD,'BU' AS HCHY_LVL,MFG_ORG_CD,EFF_FROM_DT,EFF_TO_DT FROM DDR_R_ORG_BSNS_UNIT UNION SELECT '-1',-1,'-1','NA' AS HCHY_LVL,'-1',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 ORG_HCHY_ID, '-1' as HCHY_CD, 'NA' AS HCHY_LVL, '-1' as MFG_ORG_CD, 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 ORG_CD
, -1 AS ORG_HCHY_ID
, ORG_CD AS HCHY_CD
, 'ORGANIZATION' AS HCHY_LVL
, MFG_ORG_CD
, 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 UNION SELECT ORG_CD
, ORG_CHAIN_ID
, CHAIN_CD
, 'CHAIN' AS HCHY_LVL
, MFG_ORG_CD
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_ORG_CHAIN UNION SELECT ORG_CD
, ORG_AREA_ID
, AREA_CD
, 'AREA' AS HCHY_LVL
, MFG_ORG_CD
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_ORG_AREA UNION SELECT ORG_CD
, ORG_RGN_ID
, RGN_CD
, 'REGION' AS HCHY_LVL
, MFG_ORG_CD
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_ORG_RGN UNION SELECT ORG_CD
, ORG_DSTRCT_ID
, DSTRCT_CD
, 'DISTRICT' AS HCHY_LVL
, MFG_ORG_CD
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_ORG_DSTRCT UNION SELECT ORG_CD
, ORG_BSNS_UNIT_ID
, BSNS_UNIT_CD
, 'BU' AS HCHY_LVL
, MFG_ORG_CD
, EFF_FROM_DT
, EFF_TO_DT
FROM DDR_R_ORG_BSNS_UNIT UNION SELECT '-1'
, -1
, '-1'
, 'NA' AS HCHY_LVL
, '-1'
, 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 ORG_HCHY_ID
, '-1' AS HCHY_CD
, 'NA' AS HCHY_LVL
, '-1' AS MFG_ORG_CD
, 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