VIEW: APPS.EDWBV_HR_PERM_SPSR1_L11_LCV

Source

View Text - Preformatted

SELECT '11-' || peo.person_id || '-' || inst.instance_code sprvsr_lvl11_pk ,'12-' || spv.supv_person_id || '-' || inst.instance_code sprvsr_lvl12_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' || ')' sprvsr_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 = 4 AND spv.supv_level+1 = spv.sub_level AND SYSDATE BETWEEN peo.effective_start_date AND peo.effective_end_date UNION SELECT '11-NA_EDW' || '-' || inst.instance_code sprvsr_lvl11pk ,'12-NA_EDW' || '-' || inst.instance_code sprvsr_lvl12_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 '11-' || PEO.PERSON_ID || '-' || INST.INSTANCE_CODE SPRVSR_LVL11_PK
, '12-' || SPV.SUPV_PERSON_ID || '-' || INST.INSTANCE_CODE SPRVSR_LVL12_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' || ')' SPRVSR_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 = 4
AND SPV.SUPV_LEVEL+1 = SPV.SUB_LEVEL
AND SYSDATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE UNION SELECT '11-NA_EDW' || '-' || INST.INSTANCE_CODE SPRVSR_LVL11PK
, '12-NA_EDW' || '-' || INST.INSTANCE_CODE SPRVSR_LVL12_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