View: EDWBV_ORGANIZATION_LCV

View Text

SELECT TO_CHAR(ORG.ORGANIZATION_ID) || '-' || I.INSTANCE_CODE
, DECODE( OU.NAME
, NULL
, DECODE( OI2.ORG_INFORMATION1
, 'OPERATING_UNIT'
, TO_CHAR(ORG.ORGANIZATION_ID) || '-' || I.INSTANCE_CODE
, TO_CHAR(BG.ORGANIZATION_ID) || '-' || I.INSTANCE_CODE || '-BGRP' )
, TO_CHAR(OU.ORGANIZATION_ID) || '-' || I.INSTANCE_CODE )
, ORG.NAME || ' (' || BG.NAME || ')'
, ORG.NAME || ' (' || BG.NAME || ')'
, ORG.DATE_FROM
, ORG.DATE_TO
, BG.NAME
, ORG.INTERNAL_EXTERNAL_FLAG
, ORG.TYPE
, MP.ORGANIZATION_CODE
, TO_CHAR(MP.PRIMARY_COST_METHOD)
, I.INSTANCE_CODE
, ORG.ORGANIZATION_ID
, 'ORG'
, GREATEST( NVL( ORG.LAST_UPDATE_DATE
, TO_DATE( '01/01/2000'
, 'DD/MM/YYYY' ))
, NVL( BG.LAST_UPDATE_DATE
, TO_DATE( '01/01/2000'
, 'DD/MM/YYYY' ))
, NVL( OU.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' )) )
, '_DF:PER:PER_ORGANIZATION_UNITS:ORG'
, '_DF:PER:HR_ORGANIZATION_INFORMATION:OI'
, '_DF:PER:ORG DEVELOPER DF:OI'
FROM HR_ALL_ORGANIZATION_UNITS ORG
, HR_ALL_ORGANIZATION_UNITS BG
, HR_ORGANIZATION_INFORMATION OI1
, HR_ALL_ORGANIZATION_UNITS OU
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE I
, HR_ORGANIZATION_INFORMATION OI2
WHERE ORG.BUSINESS_GROUP_ID = BG.ORGANIZATION_ID
AND ORG.ORGANIZATION_ID = OI1.ORGANIZATION_ID (+)
AND OI1.ORG_INFORMATION_CONTEXT (+) = 'ACCOUNTING INFORMATION'
AND TO_NUMBER(OI1.ORG_INFORMATION3) = OU.ORGANIZATION_ID (+)
AND ORG.ORGANIZATION_ID = MP.ORGANIZATION_ID (+)
AND ORG.ORGANIZATION_ID = OI2.ORGANIZATION_ID (+)
AND OI2.ORG_INFORMATION_CONTEXT (+) = 'CLASS'
AND OI2.ORG_INFORMATION1 (+) = 'OPERATING_UNIT'
AND OI2.ORG_INFORMATION2 (+) = 'Y'

Columns