VIEW: APPS.EDWBV_ORGA_ORG_LCV

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'