[Home] [Help]
| Product: | PER - Human Resources |
|---|---|
| Description: | |
| Implementation/DBA Data: | Not implemented in this database |
SELECT TO_CHAR(OU.ORGANIZATION_ID) || '-' || I.INSTANCE_CODE
, TO_CHAR(LE.ORGANIZATION_ID) || '-' || I.INSTANCE_CODE
, 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( LEBG.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( OI2.LAST_UPDATE_DATE
, TO_DATE( '01/01/2000'
, 'DD/MM/YYYY' ))
, NVL( MP.LAST_UPDATE_DATE
, TO_DATE( '01/01/2000'
, 'DD/MM/YYYY' )) )
, OU.NAME || ' (' || OUBG.NAME || ')'
, OU.NAME || ' (' || OUBG.NAME || ')'
, OUBG.NAME
, OU.DATE_FROM
, OU.DATE_TO
, OU.INTERNAL_EXTERNAL_FLAG
, OU.TYPE
, MP.ORGANIZATION_CODE
, MP.PRIMARY_COST_METHOD
, I.INSTANCE_CODE
, OU.ORGANIZATION_ID
, 'OU'
, '_DF:PER:PER_ORGANIZATION_UNITS:OU'
, '_DF:PER:HR_ORGANIZATION_INFORMATION:OI1'
, '_DF:PER:ORG DEVELOPER DF:OI1'
FROM HR_ALL_ORGANIZATION_UNITS OU
, HR_ALL_ORGANIZATION_UNITS OUBG
, HR_ALL_ORGANIZATION_UNITS LE
, HR_ALL_ORGANIZATION_UNITS LEBG
, 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 LE.BUSINESS_GROUP_ID = LEBG.ORGANIZATION_ID
AND OU.ORGANIZATION_ID = MP.ORGANIZATION_ID (+)