View: EDWBV_ORGA_OPER_UNIT_LCV

View Text

SELECT TO_CHAR(OU.ORGANIZATION_ID) || '-' || I.INSTANCE_CODE OPERATING_UNIT_PK
, TO_CHAR(LE.ORGANIZATION_ID) || '-' || I.INSTANCE_CODE LEGAL_ENTITY_FK
, OU.NAME || ' (' || OUBG.NAME || ')' NAME
, OU.NAME || ' (' || OUBG.NAME || ')' OPERATING_UNIT_DP
, OUBG.NAME BUSINESS_GROUP
, OU.DATE_FROM DATE_FROM
, OU.DATE_TO DATE_TO
, HR_GENERAL.DECODE_LOOKUP('INTL_EXTL'
, OU.INTERNAL_EXTERNAL_FLAG) INT_EXT_FLAG
, HR_GENERAL.DECODE_LOOKUP('ORG_TYPE'
, OU.TYPE) ORG_TYPE
, MP.ORGANIZATION_CODE ORG_CODE
, MP.PRIMARY_COST_METHOD PRIMARY_CST_MTHD
, I.INSTANCE_CODE INSTANCE
, OU.ORGANIZATION_ID OPERATING_UNIT_ID
, 'OU' LEVEL_NAME
, '_DF:PER:PER_ORGANIZATION_UNITS:OU'
, '_DF:PER:HR_ORGANIZATION_INFORMATION:OI1'
, '_DF:PER:ORG DEVELOPER DF:OI1'
, GREATEST( NVL( OU.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM_YYYY'))
, NVL(OUBG.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM_YYYY'))
, NVL( LE.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM_YYYY'))
, NVL( OI1.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM_YYYY'))
, NVL( MP.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM_YYYY'))
, NVL( OI2.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM_YYYY'))) LAST_UPDATE_DATE
, OU.CREATION_DATE CREATION_DATE
FROM HR_ALL_ORGANIZATION_UNITS OU
, HR_ALL_ORGANIZATION_UNITS OUBG
, HR_ALL_ORGANIZATION_UNITS LE
, HR_ORGANIZATION_INFORMATION OI1
, HR_ORGANIZATION_INFORMATION OI2
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE I
WHERE OU.BUSINESS_GROUP_ID = OUBG.ORGANIZATION_ID
AND OU.ORGANIZATION_ID = OI1.ORGANIZATION_ID
AND OI1.ORG_INFORMATION_CONTEXT = 'CLASS'
AND OI1.ORG_INFORMATION1 = 'OPERATING_UNIT'
AND OI1.ORG_INFORMATION2 = 'Y'
AND OU.ORGANIZATION_ID = OI2.ORGANIZATION_ID
AND OI2.ORG_INFORMATION_CONTEXT = 'OPERATING UNIT INFORMATION'
AND TO_NUMBER(OI2.ORG_INFORMATION2) = LE.ORGANIZATION_ID
AND OU.ORGANIZATION_ID = MP.ORGANIZATION_ID (+)

Columns