VIEW: APPS.EDWBV_ORGA_TREE1_LVL2_LCV

Source

View Text - Preformatted

SELECT /* $Header: hrisvog.odf 120.0 2005/05/29 06:43:56 appldev noship $ */ '2-' || tree.organization_id || '-' || inst.instance_code org_tree1_lvl2_pk ,lvl3.org_tree1_lvl3_pk org_tree1_lvl3_fk ,inst.instance_code instance ,org.name || '(' || bgr.name || ')' name ,org.name || '(' || bgr.name || ')' primary_org_dp ,bgr.name business_group ,tree.organization_id organization_id ,GREATEST( NVL( org.last_update_date,to_date('01-01-2000','DD-MM-YYYY')), NVL(tree.org_last_updated,to_date('01-01-2000','DD-MM-YYYY'))) last_update_date ,org.creation_date creation_date FROM hr_all_organization_units bgr ,hr_all_organization_units org ,hri_primary_hrchys tree ,edw_orga_tree1_lvl3_lcv lvl3 ,edw_local_instance inst WHERE tree.organization_id = org.organization_id AND tree.business_group_id = bgr.organization_id AND tree.parent_organization_id = lvl3.organization_id AND tree.organization_level = 7 UNION SELECT '2-NA_EDW' || '-' || inst.instance_code org_tree1_lvl2_pk ,'3-NA_EDW-' || inst.instance_code org_tree1_lvl3_fk ,inst.instance_code instance ,NULL name ,NULL primary_org_dp ,NULL business_group ,to_number(NULL) organization_id ,to_date('01-01-1990','DD-MM-YYYY') last_update_date ,to_date('01-01-1990','DD-MM-YYYY') creation_date FROM edw_local_instance inst WITH READ ONLY

View Text - HTML Formatted

SELECT /* $HEADER: HRISVOG.ODF 120.0 2005/05/29 06:43:56 APPLDEV NOSHIP $ */ '2-' || TREE.ORGANIZATION_ID || '-' || INST.INSTANCE_CODE ORG_TREE1_LVL2_PK
, LVL3.ORG_TREE1_LVL3_PK ORG_TREE1_LVL3_FK
, INST.INSTANCE_CODE INSTANCE
, ORG.NAME || '(' || BGR.NAME || ')' NAME
, ORG.NAME || '(' || BGR.NAME || ')' PRIMARY_ORG_DP
, BGR.NAME BUSINESS_GROUP
, TREE.ORGANIZATION_ID ORGANIZATION_ID
, GREATEST( NVL( ORG.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(TREE.ORG_LAST_UPDATED
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))) LAST_UPDATE_DATE
, ORG.CREATION_DATE CREATION_DATE
FROM HR_ALL_ORGANIZATION_UNITS BGR
, HR_ALL_ORGANIZATION_UNITS ORG
, HRI_PRIMARY_HRCHYS TREE
, EDW_ORGA_TREE1_LVL3_LCV LVL3
, EDW_LOCAL_INSTANCE INST
WHERE TREE.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND TREE.BUSINESS_GROUP_ID = BGR.ORGANIZATION_ID
AND TREE.PARENT_ORGANIZATION_ID = LVL3.ORGANIZATION_ID
AND TREE.ORGANIZATION_LEVEL = 7 UNION SELECT '2-NA_EDW' || '-' || INST.INSTANCE_CODE ORG_TREE1_LVL2_PK
, '3-NA_EDW-' || INST.INSTANCE_CODE ORG_TREE1_LVL3_FK
, INST.INSTANCE_CODE INSTANCE
, NULL NAME
, NULL PRIMARY_ORG_DP
, NULL BUSINESS_GROUP
, TO_NUMBER(NULL) ORGANIZATION_ID
, TO_DATE('01-01-1990'
, 'DD-MM-YYYY') LAST_UPDATE_DATE
, TO_DATE('01-01-1990'
, 'DD-MM-YYYY') CREATION_DATE
FROM EDW_LOCAL_INSTANCE INST WITH READ ONLY