View: EDWBV_ORGA_ORG_LCV

View Text

SELECT TO_CHAR(ORG.ORGANIZATION_ID) || '-' || INST.INSTANCE_CODE ORGANIZATION_PK /* IF THERE IS NO OPERATING UNIT ABOVE THE ORGANIZATION */
, DECODE( OU.NAME
, NULL
, /* IF THE ORGANIZATION IS AN OPERATING UNIT */ DECODE( OI2.ORG_INFORMATION1
, 'OPERATING_UNIT'
, /* THEN POINT TO ITSELF AT THE OPERATING UNIT LEVEL */ TO_CHAR(ORG.ORGANIZATION_ID) || '-' || INST.INSTANCE_CODE
, /* ELSE POINT TO THE BUSINESS GROUP AT THE BUSINESS GROUP LEVEL */ TO_CHAR(BGR.BUSINESS_GROUP_ID) || '-' || INST.INSTANCE_CODE || '-BGRP')
, /* ELSE POINT TO THE OPERATING UNIT */ TO_CHAR(OU.ORGANIZATION_ID) || '-' || INST.INSTANCE_CODE) OPERATING_UNIT_FK /* IF ORGANIZATION IS NOT IN PRIMARY HIERARCHY */
, DECODE( TREE.ORGANIZATION_ID
, /* POINT TO NA_EDW ROW IN LEVEL 1 */ NULL
, '1-NA_EDW-' || INST.INSTANCE_CODE
, /* IF ORGANIZATION LEVEL IS GREATER THAN 8 */ DECODE(SIGN(TREE.ORGANIZATION_LEVEL-8)
, /* POINT TO NEAREST ANCESTOR IN LEVEL 1 */ 1
, '1-' || TREE.PARENT_ORGANIZATION_ID || '-' || INST.INSTANCE_CODE
, /* IF IT EQUALS 8
, POINT TO ITSELF IN LEVEL 1 */ 0
, '1-' || TREE.ORGANIZATION_ID || '-' || INST.INSTANCE_CODE
, /* OTHERWISE POINT TO PUSHED DOWN ORGANIZATION IN LEVEL 1 */ TO_CHAR(9-TREE.ORGANIZATION_LEVEL) || '-' || TREE.ORGANIZATION_ID || '-' || INST.INSTANCE_CODE || '-T1L' || TO_CHAR(9-TREE.ORGANIZATION_LEVEL))) ORG_TREE1_LVL1_FK
, ORG.NAME || ' (' || BGR.NAME || ')' NAME
, ORG.NAME || ' (' || BGR.NAME || ')' ORGANIZATION_DP
, ORG.DATE_FROM DATE_FROM
, ORG.DATE_TO DATE_TO
, BGR.NAME BUSINESS_GROUP
, HR_GENERAL.DECODE_LOOKUP('INTL_EXTL'
, ORG.INTERNAL_EXTERNAL_FLAG) INT_EXT_FLAG
, HR_GENERAL.DECODE_LOOKUP('ORG_TYPE'
, ORG.TYPE) ORG_TYPE
, MP.ORGANIZATION_CODE ORG_CODE
, TO_CHAR(MP.PRIMARY_COST_METHOD) ORG_PRIM_CST_MTHD
, INST.INSTANCE_CODE INSTANCE
, ORG.ORGANIZATION_ID ORGANIZATION_ID
, 'ORG' LEVEL_NAME
, OI3.ORG_INFORMATION2 PERSON_MANAGER_ID
, DECODE(OI4.ORG_INFORMATION2
, 'Y'
, DECODE(OI3.ORG_INFORMATION2
, NULL
, 'NA_EDW'
, OI3.ORG_INFORMATION2|| '-' || INST.INSTANCE_CODE || '-EMPLOYEE-PERS')
, 'NA_EDW') PERSON_MANAGER_FK
, NULL PERSON_MANAGER_FK_KEY
, NULL ORG_CAT1
, NULL ORG_CAT2
, NULL ORG_CAT3
, NULL ORG_CAT4
, NULL ORG_CAT5
, NULL ORG_CAT6
, NULL ORG_CAT7
, NULL ORG_CAT8
, NULL ORG_CAT9
, NULL ORG_CAT10
, NULL ORG_CAT11
, NULL ORG_CAT12
, NULL ORG_CAT13
, NULL ORG_CAT14
, NULL ORG_CAT15
, '_DF:PER:PER_ORGANIZATION_UNITS:ORG'
, '_DF:PER:HR_ORGANIZATION_INFORMATION:OI2'
, '_DF:PER:ORG DEVELOPER DF:OI2'
, GREATEST( NVL(ORG.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(TREE.ORG_LAST_UPDATED
, 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
, ORG.CREATION_DATE CREATION_DATE
FROM HR_ALL_ORGANIZATION_UNITS ORG
, PER_BUSINESS_GROUPS BGR
, HR_ORGANIZATION_INFORMATION OI1
, HR_ORGANIZATION_INFORMATION OI2
, HR_ORGANIZATION_INFORMATION OI3
, HR_ORGANIZATION_INFORMATION OI4
, HR_ALL_ORGANIZATION_UNITS OU
, MTL_PARAMETERS MP
, EDW_LOCAL_INSTANCE INST
, HRI_PRIMARY_HRCHYS TREE
WHERE ORG.BUSINESS_GROUP_ID = BGR.BUSINESS_GROUP_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'
AND TREE.ORGANIZATION_ID (+) = ORG.ORGANIZATION_ID
AND OI3.ORG_INFORMATION_CONTEXT (+) = 'ORGANIZATION NAME ALIAS'
AND ORG.ORGANIZATION_ID = OI3.ORGANIZATION_ID (+)
AND ORG.ORGANIZATION_ID = OI4.ORGANIZATION_ID (+)
AND OI4.ORG_INFORMATION_CONTEXT (+) = 'CLASS'
AND OI4.ORG_INFORMATION1 (+) = 'CC'
AND OI4.ORG_INFORMATION2 (+) = 'Y'

Columns