VIEW: APPS.EDWBV_HR_PERM_SPSR1_L5_LCV

Source

View Text - Preformatted

SELECT '5-' || peo.person_id || '-' || inst.instance_code sprvsr_lvl5_pk ,'6-' || spv.supv_person_id || '-' || inst.instance_code sprvsr_lvl6_fk ,inst.instance_code instance ,spv.creation_date creation_date ,spv.last_update_date last_update_date ,peo.full_name || ' (' || NVL(peo.employee_number, peo.applicant_number) ||',' || 'EMPLOYEE' || ')' name ,peo.full_name || ' (' || NVL(peo.employee_number, peo.applicant_number) ||',' || 'EMPLOYEE' || ')' sprsvr_dp ,peo.person_id person_id ,spv.sub_assignment_id assignment_id FROM per_all_people_f peo ,edw_local_instance inst ,hri_supv_hrchy_summary spv WHERE spv.sub_person_id = peo.person_id AND spv.sub_level = 10 AND spv.supv_level+1 = spv.sub_level AND SYSDATE BETWEEN peo.effective_start_date AND peo.effective_end_date UNION SELECT '5-NA_EDW' || '-' || inst.instance_code sprvsr_lvl5_pk ,'6-NA_EDW' || '-' || inst.instance_code sprvsr_lvl6_fk ,inst.instance_code instance ,inst.creation_date creation_date ,inst.last_update_date last_update_date ,null name ,null sprvsr_dp ,to_number(null) person_id ,to_number(null) assignment_id FROM edw_local_instance inst

View Text - HTML Formatted

SELECT '5-' || PEO.PERSON_ID || '-' || INST.INSTANCE_CODE SPRVSR_LVL5_PK
, '6-' || SPV.SUPV_PERSON_ID || '-' || INST.INSTANCE_CODE SPRVSR_LVL6_FK
, INST.INSTANCE_CODE INSTANCE
, SPV.CREATION_DATE CREATION_DATE
, SPV.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PEO.FULL_NAME || ' (' || NVL(PEO.EMPLOYEE_NUMBER
, PEO.APPLICANT_NUMBER) ||'
, ' || 'EMPLOYEE' || ')' NAME
, PEO.FULL_NAME || ' (' || NVL(PEO.EMPLOYEE_NUMBER
, PEO.APPLICANT_NUMBER) ||'
, ' || 'EMPLOYEE' || ')' SPRSVR_DP
, PEO.PERSON_ID PERSON_ID
, SPV.SUB_ASSIGNMENT_ID ASSIGNMENT_ID
FROM PER_ALL_PEOPLE_F PEO
, EDW_LOCAL_INSTANCE INST
, HRI_SUPV_HRCHY_SUMMARY SPV
WHERE SPV.SUB_PERSON_ID = PEO.PERSON_ID
AND SPV.SUB_LEVEL = 10
AND SPV.SUPV_LEVEL+1 = SPV.SUB_LEVEL
AND SYSDATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE UNION SELECT '5-NA_EDW' || '-' || INST.INSTANCE_CODE SPRVSR_LVL5_PK
, '6-NA_EDW' || '-' || INST.INSTANCE_CODE SPRVSR_LVL6_FK
, INST.INSTANCE_CODE INSTANCE
, INST.CREATION_DATE CREATION_DATE
, INST.LAST_UPDATE_DATE LAST_UPDATE_DATE
, NULL NAME
, NULL SPRVSR_DP
, TO_NUMBER(NULL) PERSON_ID
, TO_NUMBER(NULL) ASSIGNMENT_ID
FROM EDW_LOCAL_INSTANCE INST