eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.EDWBV_HR_PERM_ASSIGN_LCV

Source

View Text - Preformatted


SELECT peo.person_id || '-' || inst.instance_code || '-EMPLOYEE-PERS' assignment_pk ,'ALL' all_fk , decode(tree.sub_person_id, NULL, '1-NA_EDW-'|| inst.instance_code, decode(SIGN(tree.sub_level-14), 1,'1-' || hri_edw_dim_person.get_nearest_parent(peo.person_id) || '-' || inst.instance_code, 0,'1-'|| tree.sub_person_id || '-' || inst.instance_code, to_char(15-tree.sub_level) || '-' || tree.sub_person_id || '-' || inst.instance_code || '-TL' || to_char(15-tree.sub_level) )) sprvsr_lvl1_fk ,peo.full_name || ' (' || NVL(peo.employee_number, peo.applicant_number) || ',' || 'EMPLOYEE' || ')' name ,peo.full_name || ' (' || NVL(peo.employee_number, peo.applicant_number) || ',' || 'EMPLOYEE' || ')' person_dp ,bgr.name business_group ,NVL(peo.employee_number,peo.applicant_number) person_num ,peo.full_name full_name ,peo.last_name last_name ,pps.date_start start_date ,pps.actual_termination_date end_date ,hr_general.decode_lookup('YES_NO',NVL(peo.current_employee_flag,'N')) employee_flag ,hr_general.decode_lookup('YES_NO',NVL(peo.current_applicant_flag,'N')) applicant_flag ,hr_general.decode_lookup('YES_NO', hri_edw_dim_person.is_a_buyer(peo.person_id)) buyer_flag ,hr_general.decode_lookup('YES_NO', hri_edw_dim_person.is_a_planner(peo.person_id)) planner_flag ,hr_general.decode_lookup('YES_NO', hri_edw_dim_person.is_a_sales_rep(peo.person_id)) sales_rep_flag ,hr_general.decode_lookup('YES_NO','N') sys_gen_flag ,inst.instance_code instance ,peo.person_id person_id ,to_number(NULL) salesrep_id ,NULL planner_code ,peo.business_group_id organization_id ,peo.effective_start_date effective_start_date ,DECODE(peo.effective_end_date, to_date('31-12-4712','DD-MM-YYYY'), to_date(NULL), peo.effective_end_date) effective_end_date ,peo.first_name first_name ,peo.middle_names middle_names ,peo.previous_last_name previous_last_name ,peo.known_as known_as ,hr_general.decode_lookup('TITLE',peo.title) title ,peo.suffix name_suffix ,peo.pre_name_adjunct name_prefix ,peo.email_address email_address ,hr_general.decode_lookup('MAR_STATUS',peo.marital_status) marital_status ,hr_general.decode_lookup('YES_NO',peo.rehire_recommendation) rehire_rcmmndtn ,lng.nls_language crrspndnc_language ,peo.date_employee_data_verified date_emp_data_vrfd ,hr_general.decode_lookup('YES_NO',NVL(peo.registered_disabled_flag,'N')) disability_flag ,peo.resume_last_updated resume_updated_date ,hr_general.decode_lookup('YES_NO',NVL(peo.resume_exists,'N')) resume_exists ,hr_general.decode_lookup('SEX',peo.sex) gender ,peo.internal_location internal_location ,peo.mailstop mailstop ,hr_general.decode_lookup('YES_NO',peo.fast_path_employee) fast_path_employee ,peo.national_identifier national_identifier ,peo.date_of_birth date_of_birth ,peo.fte_capacity fte_capacity ,hr_general.decode_lookup('STUDENT_STATUS',peo.student_status) student_status ,peo.town_of_birth town_of_birth ,peo.region_of_birth region_of_birth ,ftv.territory_short_name country_of_birth ,hr_general.decode_lookup('NATIONALITY',peo.nationality) nationality ,peo.global_person_id global_person_id ,'_DF:PER:PER_PEOPLE:peo' ,'_DF:PER:Person Developer DF:peo' ,peo.creation_date creation_date ,greatest( NVL(peo.last_update_date,to_date('01-01-2000','DD-MM-YYYY')), NVL(tree.sub_last_ptntl_change,to_date('01-01-2000','DD-MM-YYYY')), NVL(bgr.last_update_date,to_date('01-01-2000','DD-MM-YYYY'))) last_update_date FROM edw_local_instance inst ,hr_all_organization_units bgr ,per_all_people_f peo ,per_periods_of_service pps ,fnd_languages lng ,fnd_territories_vl ftv ,hri_supv_hrchy_summary tree /* Generates list of latest date tracked end dates by person */ ,(select person_id, effective_start_date, effective_end_date from per_all_people_f peo minus /* Remove any person end date which has an later end date */ select person_id, effective_start_date, effective_end_date from per_all_people_f peo where exists (select 1 from per_all_people_f peo1 where peo1.person_id = peo.person_id and peo1.effective_end_date > peo.effective_end_date) ) peo_end WHERE /* If there are future dated changes, only select the current date tracked row */ ((SYSDATE < peo_end.effective_start_date AND SYSDATE BETWEEN peo.effective_start_date AND peo.effective_end_date) OR (SYSDATE >= peo_end.effective_start_date AND peo_end.effective_start_date = peo.effective_start_date)) AND peo_end.person_id = peo.person_id AND peo.business_group_id = bgr.organization_id AND peo.person_id = pps.person_id (+) AND NOT EXISTS (SELECT null FROM per_periods_of_service later_pps WHERE later_pps.person_id = peo.person_id AND later_pps.date_start > pps.date_start) AND peo.correspondence_language = lng.language_code (+) AND peo.country_of_birth = ftv.territory_code (+) AND peo.person_id = tree.sub_person_id (+) AND NVL(tree.supv_level, -2) + 1 = NVL(tree.sub_level, -2 + 1) and peo.person_id not in (select supv_person_id from hri_supv_hrchy_summary tree2 where tree2.supv_level = 0 AND tree2.supv_person_id = peo.person_id) UNION ALL /* all supervisors at top of supervisor hierarchy */ SELECT peo.person_id || '-' || inst.instance_code || '-EMPLOYEE-PERS' assignment_pk ,'ALL' all_fk ,decode(peo.person_id, tree.supv_person_id, decode(tree.supv_person_id, NULL, '1-NA_EDW-'|| inst.instance_code, decode(SIGN(tree.supv_level-15), 1,'1-' || tree.supv_person_id || '-' || inst.instance_code, 0,'1-'|| tree.supv_person_id || '-' || inst.instance_code, (to_char(15-tree.supv_level) || '-' || tree.supv_person_id || '-' || inst.instance_code || '-TL' || to_char(15-tree.supv_level)) ))) sprvsr_lvl1_fk ,peo.full_name || ' (' || NVL(peo.employee_number, peo.applicant_number) || ',' || 'EMPLOYEE' || ')' name ,peo.full_name || ' (' || NVL(peo.employee_number, peo.applicant_number) || ',' || 'EMPLOYEE' || ')' person_dp ,bgr.name business_group ,NVL(peo.employee_number,peo.applicant_number) person_num ,peo.full_name full_name ,peo.last_name last_name ,pps.date_start start_date ,pps.actual_termination_date end_date ,hr_general.decode_lookup('YES_NO',NVL(peo.current_employee_flag,'N')) employee_flag ,hr_general.decode_lookup('YES_NO',NVL(peo.current_applicant_flag,'N')) applicant_flag ,hr_general.decode_lookup('YES_NO', hri_edw_dim_person.is_a_buyer(peo.person_id)) buyer_flag ,hr_general.decode_lookup('YES_NO', hri_edw_dim_person.is_a_planner(peo.person_id)) planner_flag ,hr_general.decode_lookup('YES_NO', hri_edw_dim_person.is_a_sales_rep(peo.person_id)) sales_rep_flag ,hr_general.decode_lookup('YES_NO','N') sys_gen_flag ,inst.instance_code instance ,peo.person_id person_id ,to_number(NULL) salesrep_id ,NULL planner_code ,peo.business_group_id organization_id ,peo.effective_start_date effective_start_date ,DECODE(peo.effective_end_date, to_date('31-12-4712','DD-MM-YYYY'), to_date(NULL), peo.effective_end_date) effective_end_date ,peo.first_name first_name ,peo.middle_names middle_names ,peo.previous_last_name previous_last_name ,peo.known_as known_as ,hr_general.decode_lookup('TITLE',peo.title) title ,peo.suffix name_suffix ,peo.pre_name_adjunct name_prefix ,peo.email_address email_address ,hr_general.decode_lookup('MAR_STATUS',peo.marital_status) marital_status ,hr_general.decode_lookup('YES_NO',peo.rehire_recommendation) rehire_rcmmndtn ,lng.nls_language crrspndnc_language ,peo.date_employee_data_verified date_emp_data_vrfd ,hr_general.decode_lookup('YES_NO',NVL(peo.registered_disabled_flag,'N')) disability_flag ,peo.resume_last_updated resume_updated_date ,hr_general.decode_lookup('YES_NO',NVL(peo.resume_exists,'N')) resume_exists ,hr_general.decode_lookup('SEX',peo.sex) gender ,peo.internal_location internal_location ,peo.mailstop mailstop ,hr_general.decode_lookup('YES_NO',peo.fast_path_employee) fast_path_employee ,peo.national_identifier national_identifier ,peo.date_of_birth date_of_birth ,peo.fte_capacity fte_capacity ,hr_general.decode_lookup('STUDENT_STATUS',peo.student_status) student_status ,peo.town_of_birth town_of_birth ,peo.region_of_birth region_of_birth ,ftv.territory_short_name country_of_birth ,hr_general.decode_lookup('NATIONALITY',peo.nationality) nationality ,peo.global_person_id global_person_id ,'_DF:_DUMMY:PER:PER_PEOPLE:peo' ,'_DF:_DUMMY:PER:Person Developer DF:peo' ,peo.creation_date creation_date ,greatest( NVL(peo.last_update_date,to_date('01-01-2000','DD-MM-YYYY')), NVL(tree.supv_last_ptntl_change,to_date('01-01-2000','DD-MM-YYYY')), NVL(bgr.last_update_date,to_date('01-01-2000','DD-MM-YYYY'))) last_update_date FROM edw_local_instance inst ,hr_all_organization_units bgr ,per_all_people_f peo ,per_periods_of_service pps ,fnd_languages lng ,fnd_territories_vl ftv , (select distinct supv_person_id ,supv_assignment_id ,supv_level ,supv_last_ptntl_change from hri_supv_hrchy_summary shs where supv_level = 0 AND sub_level = supv_level+1) tree /* Generates list of latest date tracked end dates by person */ ,(select person_id, effective_start_date, effective_end_date from per_all_people_f peo minus /* Remove any person end date which has an later end date */ select person_id, effective_start_date, effective_end_date from per_all_people_f peo where exists (select 1 from per_all_people_f peo1 where peo1.person_id = peo.person_id and peo1.effective_end_date > peo.effective_end_date) ) peo_end WHERE /* If there are future dated changes, only select the current date tracked row */ ((SYSDATE < peo_end.effective_start_date AND SYSDATE BETWEEN peo.effective_start_date AND peo.effective_end_date) OR (SYSDATE >= peo_end.effective_start_date AND peo_end.effective_start_date = peo.effective_start_date)) AND peo_end.person_id = peo.person_id AND peo.business_group_id = bgr.organization_id AND peo.person_id = pps.person_id (+) AND NOT EXISTS (SELECT null FROM per_periods_of_service later_pps WHERE later_pps.person_id = peo.person_id AND later_pps.date_start > pps.date_start) AND peo.correspondence_language = lng.language_code (+) AND peo.country_of_birth = ftv.territory_code (+) AND peo.person_id = tree.supv_person_id UNION ALL /*all sales_reps*/ SELECT rs.salesrep_id || '-' || org.organization_id || '-' || inst.instance_code || '-SALESREP-PERS' assignment_pk ,'ALL' all_fk , to_char('1-NA_EDW-'|| inst.instance_code) sprvsr_lvl1_fk ,rs.name || '-' || org.name || '(' || 'SALESREP' || ')' name ,rs.name || '-' || org.name || '(' || 'SALESREP' || ')' person_dp ,org.name business_group ,rs.salesrep_number person_num ,rs.name full_name ,NULL last_name ,nvl(rs.start_date_active, rs.creation_date) start_date ,rs.end_date_active end_date ,hr_general.decode_lookup('YES_NO','N') employee_flag ,hr_general.decode_lookup('YES_NO','N') applicant_flag ,hr_general.decode_lookup('YES_NO','N') buyer_flag ,hr_general.decode_lookup('YES_NO','N') planner_flag ,hr_general.decode_lookup('YES_NO','Y') sales_rep_flag ,hr_general.decode_lookup('YES_NO','Y') sys_gen_flag ,inst.instance_code instance ,to_number(NULL) person_id ,rs.salesrep_id salesrep_id ,NULL planner_code ,rs.org_id organization_id ,nvl(rs.start_date_active, rs.creation_date) effective_start_date ,nvl(rs.end_date_active, to_date('4712/12/31', 'YYYY/MM/DD')) effective_end_date ,NULL first_name ,NULL middle_names ,NULL previous_last_name ,NULL known_as ,NULL title ,NULL name_suffix ,NULL name_prefix ,rs.email_address email_address ,NULL marital_status ,NULL rehire_rcmmndtn ,NULL crrspndnc_language ,to_date(NULL) date_emp_data_vrfd ,NULL disability_flag ,to_date(NULL) resume_updated_date ,NULL resume_exists ,NULL gender ,NULL internal_location ,NULL mailstop ,NULL fast_path_employee ,NULL national_identifier ,to_date(NULL) date_of_birth ,to_number(NULL) fte_capacity ,NULL student_status ,NULL town_of_birth ,NULL region_of_birth ,NULL country_of_birth ,NULL nationality ,NULL global_person_id ,'_DF:_DUMMY:PER:PER_PEOPLE:peo' ,'_DF:_DUMMY:PER:Person Developer DF:peo' ,rs.creation_date creation_date ,greatest( NVL( rs.last_update_date,to_date('01-01-2000','DD-MM-YYYY')), NVL(org.last_update_date,to_date('01-01-2000','DD-MM-YYYY'))) last_update_date FROM edw_local_instance inst, hr_all_organization_units org, ra_salesreps_all rs WHERE rs.org_id = org.organization_id (+) UNION ALL /*all planners*/ SELECT mp.planner_code || '-' || org.organization_id || '-' || inst.instance_code || '-PLANNER-PERS' assignment_pk ,'ALL' all_fk , to_char('1-NA_EDW-'|| inst.instance_code) sprvsr_lvl1_fk ,mp.planner_code || '-' || org.name || ' (' || 'PLANNER' || ')' name ,mp.planner_code || '-' || org.name || ' (' || 'PLANNER' || ')' person_dp ,org.name business_group ,NULL person_num ,mp.description full_name ,NULL last_name ,mp.creation_date start_date ,mp.disable_date end_date ,hr_general.decode_lookup('YES_NO','N') employee_flag ,hr_general.decode_lookup('YES_NO','N') applicant_flag ,hr_general.decode_lookup('YES_NO','N') buyer_flag ,hr_general.decode_lookup('YES_NO','Y') planner_flag ,hr_general.decode_lookup('YES_NO','N') salesrep_flag ,hr_general.decode_lookup('YES_NO','Y') sys_gen_flag ,inst.instance_code instance ,to_number(NULL) person_id ,to_number(NULL) salesrep_id ,mp.planner_code planner_code ,mp.organization_id organization_id ,mp.creation_date effective_start_date ,nvl(mp.disable_date,to_date('4712/12/31','YYYY/MM/DD')) effective_end_date ,NULL first_name ,NULL middle_names ,NULL previous_last_name ,NULL known_as ,NULL title ,NULL name_suffix ,NULL name_prefix ,mp.electronic_mail_address email_address ,NULL marital_status ,NULL rehire_rcmmndtn ,NULL crrspndnc_language ,to_date(NULL) date_emp_data_vrfd ,NULL disability_flag ,to_date(NULL) resume_updated_date ,NULL resume_exists ,NULL gender ,NULL internal_location ,NULL mailstop ,NULL fast_path_employee ,NULL national_identifier ,to_date(NULL) date_of_birth ,to_number(NULL) fte_capacity ,NULL student_status ,NULL town_of_birth ,NULL region_of_birth ,NULL country_of_birth ,NULL nationality ,NULL global_person_id ,'_DF:_DUMMY:PER:PER_PEOPLE:peo' ,'_DF:_DUMMY:PER:Person Developer DF:peo' ,mp.creation_date creation_date ,greatest( NVL(mp.last_update_date, to_date('01-01-2000','DD-MM-YYYY')), NVL(org.last_update_date,to_date('01-01-2000','DD-MM-YYYY'))) last_update_date FROM edw_local_instance inst ,hr_all_organization_units org ,mtl_planners mp WHERE mp.organization_id = org.organization_id

View Text - HTML Formatted

SELECT PEO.PERSON_ID || '-' || INST.INSTANCE_CODE || '-EMPLOYEE-PERS' ASSIGNMENT_PK
, 'ALL' ALL_FK
, DECODE(TREE.SUB_PERSON_ID
, NULL
, '1-NA_EDW-'|| INST.INSTANCE_CODE
, DECODE(SIGN(TREE.SUB_LEVEL-14)
, 1
, '1-' || HRI_EDW_DIM_PERSON.GET_NEAREST_PARENT(PEO.PERSON_ID) || '-' || INST.INSTANCE_CODE
, 0
, '1-'|| TREE.SUB_PERSON_ID || '-' || INST.INSTANCE_CODE
, TO_CHAR(15-TREE.SUB_LEVEL) || '-' || TREE.SUB_PERSON_ID || '-' || INST.INSTANCE_CODE || '-TL' || TO_CHAR(15-TREE.SUB_LEVEL) )) SPRVSR_LVL1_FK
, PEO.FULL_NAME || ' (' || NVL(PEO.EMPLOYEE_NUMBER
, PEO.APPLICANT_NUMBER) || '
, ' || 'EMPLOYEE' || ')' NAME
, PEO.FULL_NAME || ' (' || NVL(PEO.EMPLOYEE_NUMBER
, PEO.APPLICANT_NUMBER) || '
, ' || 'EMPLOYEE' || ')' PERSON_DP
, BGR.NAME BUSINESS_GROUP
, NVL(PEO.EMPLOYEE_NUMBER
, PEO.APPLICANT_NUMBER) PERSON_NUM
, PEO.FULL_NAME FULL_NAME
, PEO.LAST_NAME LAST_NAME
, PPS.DATE_START START_DATE
, PPS.ACTUAL_TERMINATION_DATE END_DATE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_EMPLOYEE_FLAG
, 'N')) EMPLOYEE_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_APPLICANT_FLAG
, 'N')) APPLICANT_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, HRI_EDW_DIM_PERSON.IS_A_BUYER(PEO.PERSON_ID)) BUYER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, HRI_EDW_DIM_PERSON.IS_A_PLANNER(PEO.PERSON_ID)) PLANNER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, HRI_EDW_DIM_PERSON.IS_A_SALES_REP(PEO.PERSON_ID)) SALES_REP_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') SYS_GEN_FLAG
, INST.INSTANCE_CODE INSTANCE
, PEO.PERSON_ID PERSON_ID
, TO_NUMBER(NULL) SALESREP_ID
, NULL PLANNER_CODE
, PEO.BUSINESS_GROUP_ID ORGANIZATION_ID
, PEO.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, DECODE(PEO.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
, TO_DATE(NULL)
, PEO.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, PEO.FIRST_NAME FIRST_NAME
, PEO.MIDDLE_NAMES MIDDLE_NAMES
, PEO.PREVIOUS_LAST_NAME PREVIOUS_LAST_NAME
, PEO.KNOWN_AS KNOWN_AS
, HR_GENERAL.DECODE_LOOKUP('TITLE'
, PEO.TITLE) TITLE
, PEO.SUFFIX NAME_SUFFIX
, PEO.PRE_NAME_ADJUNCT NAME_PREFIX
, PEO.EMAIL_ADDRESS EMAIL_ADDRESS
, HR_GENERAL.DECODE_LOOKUP('MAR_STATUS'
, PEO.MARITAL_STATUS) MARITAL_STATUS
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PEO.REHIRE_RECOMMENDATION) REHIRE_RCMMNDTN
, LNG.NLS_LANGUAGE CRRSPNDNC_LANGUAGE
, PEO.DATE_EMPLOYEE_DATA_VERIFIED DATE_EMP_DATA_VRFD
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.REGISTERED_DISABLED_FLAG
, 'N')) DISABILITY_FLAG
, PEO.RESUME_LAST_UPDATED RESUME_UPDATED_DATE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.RESUME_EXISTS
, 'N')) RESUME_EXISTS
, HR_GENERAL.DECODE_LOOKUP('SEX'
, PEO.SEX) GENDER
, PEO.INTERNAL_LOCATION INTERNAL_LOCATION
, PEO.MAILSTOP MAILSTOP
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PEO.FAST_PATH_EMPLOYEE) FAST_PATH_EMPLOYEE
, PEO.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PEO.DATE_OF_BIRTH DATE_OF_BIRTH
, PEO.FTE_CAPACITY FTE_CAPACITY
, HR_GENERAL.DECODE_LOOKUP('STUDENT_STATUS'
, PEO.STUDENT_STATUS) STUDENT_STATUS
, PEO.TOWN_OF_BIRTH TOWN_OF_BIRTH
, PEO.REGION_OF_BIRTH REGION_OF_BIRTH
, FTV.TERRITORY_SHORT_NAME COUNTRY_OF_BIRTH
, HR_GENERAL.DECODE_LOOKUP('NATIONALITY'
, PEO.NATIONALITY) NATIONALITY
, PEO.GLOBAL_PERSON_ID GLOBAL_PERSON_ID
, '_DF:PER:PER_PEOPLE:PEO'
, '_DF:PER:PERSON DEVELOPER DF:PEO'
, PEO.CREATION_DATE CREATION_DATE
, GREATEST( NVL(PEO.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(TREE.SUB_LAST_PTNTL_CHANGE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(BGR.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))) LAST_UPDATE_DATE
FROM EDW_LOCAL_INSTANCE INST
, HR_ALL_ORGANIZATION_UNITS BGR
, PER_ALL_PEOPLE_F PEO
, PER_PERIODS_OF_SERVICE PPS
, FND_LANGUAGES LNG
, FND_TERRITORIES_VL FTV
, HRI_SUPV_HRCHY_SUMMARY TREE /* GENERATES LIST OF LATEST DATE TRACKED END DATES BY PERSON */
, (SELECT PERSON_ID
, EFFECTIVE_START_DATE
, EFFECTIVE_END_DATE
FROM PER_ALL_PEOPLE_F PEO MINUS /* REMOVE ANY PERSON END DATE WHICH HAS AN LATER END DATE */ SELECT PERSON_ID
, EFFECTIVE_START_DATE
, EFFECTIVE_END_DATE
FROM PER_ALL_PEOPLE_F PEO
WHERE EXISTS (SELECT 1
FROM PER_ALL_PEOPLE_F PEO1
WHERE PEO1.PERSON_ID = PEO.PERSON_ID
AND PEO1.EFFECTIVE_END_DATE > PEO.EFFECTIVE_END_DATE) ) PEO_END
WHERE /* IF THERE ARE FUTURE DATED CHANGES
, ONLY SELECT THE CURRENT DATE TRACKED ROW */ ((SYSDATE < PEO_END.EFFECTIVE_START_DATE
AND SYSDATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE) OR (SYSDATE >= PEO_END.EFFECTIVE_START_DATE
AND PEO_END.EFFECTIVE_START_DATE = PEO.EFFECTIVE_START_DATE))
AND PEO_END.PERSON_ID = PEO.PERSON_ID
AND PEO.BUSINESS_GROUP_ID = BGR.ORGANIZATION_ID
AND PEO.PERSON_ID = PPS.PERSON_ID (+)
AND NOT EXISTS (SELECT NULL
FROM PER_PERIODS_OF_SERVICE LATER_PPS
WHERE LATER_PPS.PERSON_ID = PEO.PERSON_ID
AND LATER_PPS.DATE_START > PPS.DATE_START)
AND PEO.CORRESPONDENCE_LANGUAGE = LNG.LANGUAGE_CODE (+)
AND PEO.COUNTRY_OF_BIRTH = FTV.TERRITORY_CODE (+)
AND PEO.PERSON_ID = TREE.SUB_PERSON_ID (+)
AND NVL(TREE.SUPV_LEVEL
, -2) + 1 = NVL(TREE.SUB_LEVEL
, -2 + 1)
AND PEO.PERSON_ID NOT IN (SELECT SUPV_PERSON_ID
FROM HRI_SUPV_HRCHY_SUMMARY TREE2
WHERE TREE2.SUPV_LEVEL = 0
AND TREE2.SUPV_PERSON_ID = PEO.PERSON_ID) UNION ALL /* ALL SUPERVISORS AT TOP OF SUPERVISOR HIERARCHY */ SELECT PEO.PERSON_ID || '-' || INST.INSTANCE_CODE || '-EMPLOYEE-PERS' ASSIGNMENT_PK
, 'ALL' ALL_FK
, DECODE(PEO.PERSON_ID
, TREE.SUPV_PERSON_ID
, DECODE(TREE.SUPV_PERSON_ID
, NULL
, '1-NA_EDW-'|| INST.INSTANCE_CODE
, DECODE(SIGN(TREE.SUPV_LEVEL-15)
, 1
, '1-' || TREE.SUPV_PERSON_ID || '-' || INST.INSTANCE_CODE
, 0
, '1-'|| TREE.SUPV_PERSON_ID || '-' || INST.INSTANCE_CODE
, (TO_CHAR(15-TREE.SUPV_LEVEL) || '-' || TREE.SUPV_PERSON_ID || '-' || INST.INSTANCE_CODE || '-TL' || TO_CHAR(15-TREE.SUPV_LEVEL)) ))) SPRVSR_LVL1_FK
, PEO.FULL_NAME || ' (' || NVL(PEO.EMPLOYEE_NUMBER
, PEO.APPLICANT_NUMBER) || '
, ' || 'EMPLOYEE' || ')' NAME
, PEO.FULL_NAME || ' (' || NVL(PEO.EMPLOYEE_NUMBER
, PEO.APPLICANT_NUMBER) || '
, ' || 'EMPLOYEE' || ')' PERSON_DP
, BGR.NAME BUSINESS_GROUP
, NVL(PEO.EMPLOYEE_NUMBER
, PEO.APPLICANT_NUMBER) PERSON_NUM
, PEO.FULL_NAME FULL_NAME
, PEO.LAST_NAME LAST_NAME
, PPS.DATE_START START_DATE
, PPS.ACTUAL_TERMINATION_DATE END_DATE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_EMPLOYEE_FLAG
, 'N')) EMPLOYEE_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_APPLICANT_FLAG
, 'N')) APPLICANT_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, HRI_EDW_DIM_PERSON.IS_A_BUYER(PEO.PERSON_ID)) BUYER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, HRI_EDW_DIM_PERSON.IS_A_PLANNER(PEO.PERSON_ID)) PLANNER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, HRI_EDW_DIM_PERSON.IS_A_SALES_REP(PEO.PERSON_ID)) SALES_REP_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') SYS_GEN_FLAG
, INST.INSTANCE_CODE INSTANCE
, PEO.PERSON_ID PERSON_ID
, TO_NUMBER(NULL) SALESREP_ID
, NULL PLANNER_CODE
, PEO.BUSINESS_GROUP_ID ORGANIZATION_ID
, PEO.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, DECODE(PEO.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
, TO_DATE(NULL)
, PEO.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, PEO.FIRST_NAME FIRST_NAME
, PEO.MIDDLE_NAMES MIDDLE_NAMES
, PEO.PREVIOUS_LAST_NAME PREVIOUS_LAST_NAME
, PEO.KNOWN_AS KNOWN_AS
, HR_GENERAL.DECODE_LOOKUP('TITLE'
, PEO.TITLE) TITLE
, PEO.SUFFIX NAME_SUFFIX
, PEO.PRE_NAME_ADJUNCT NAME_PREFIX
, PEO.EMAIL_ADDRESS EMAIL_ADDRESS
, HR_GENERAL.DECODE_LOOKUP('MAR_STATUS'
, PEO.MARITAL_STATUS) MARITAL_STATUS
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PEO.REHIRE_RECOMMENDATION) REHIRE_RCMMNDTN
, LNG.NLS_LANGUAGE CRRSPNDNC_LANGUAGE
, PEO.DATE_EMPLOYEE_DATA_VERIFIED DATE_EMP_DATA_VRFD
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.REGISTERED_DISABLED_FLAG
, 'N')) DISABILITY_FLAG
, PEO.RESUME_LAST_UPDATED RESUME_UPDATED_DATE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, NVL(PEO.RESUME_EXISTS
, 'N')) RESUME_EXISTS
, HR_GENERAL.DECODE_LOOKUP('SEX'
, PEO.SEX) GENDER
, PEO.INTERNAL_LOCATION INTERNAL_LOCATION
, PEO.MAILSTOP MAILSTOP
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PEO.FAST_PATH_EMPLOYEE) FAST_PATH_EMPLOYEE
, PEO.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PEO.DATE_OF_BIRTH DATE_OF_BIRTH
, PEO.FTE_CAPACITY FTE_CAPACITY
, HR_GENERAL.DECODE_LOOKUP('STUDENT_STATUS'
, PEO.STUDENT_STATUS) STUDENT_STATUS
, PEO.TOWN_OF_BIRTH TOWN_OF_BIRTH
, PEO.REGION_OF_BIRTH REGION_OF_BIRTH
, FTV.TERRITORY_SHORT_NAME COUNTRY_OF_BIRTH
, HR_GENERAL.DECODE_LOOKUP('NATIONALITY'
, PEO.NATIONALITY) NATIONALITY
, PEO.GLOBAL_PERSON_ID GLOBAL_PERSON_ID
, '_DF:_DUMMY:PER:PER_PEOPLE:PEO'
, '_DF:_DUMMY:PER:PERSON DEVELOPER DF:PEO'
, PEO.CREATION_DATE CREATION_DATE
, GREATEST( NVL(PEO.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(TREE.SUPV_LAST_PTNTL_CHANGE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(BGR.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))) LAST_UPDATE_DATE
FROM EDW_LOCAL_INSTANCE INST
, HR_ALL_ORGANIZATION_UNITS BGR
, PER_ALL_PEOPLE_F PEO
, PER_PERIODS_OF_SERVICE PPS
, FND_LANGUAGES LNG
, FND_TERRITORIES_VL FTV
, (SELECT DISTINCT SUPV_PERSON_ID
, SUPV_ASSIGNMENT_ID
, SUPV_LEVEL
, SUPV_LAST_PTNTL_CHANGE
FROM HRI_SUPV_HRCHY_SUMMARY SHS
WHERE SUPV_LEVEL = 0
AND SUB_LEVEL = SUPV_LEVEL+1) TREE /* GENERATES LIST OF LATEST DATE TRACKED END DATES BY PERSON */
, (SELECT PERSON_ID
, EFFECTIVE_START_DATE
, EFFECTIVE_END_DATE
FROM PER_ALL_PEOPLE_F PEO MINUS /* REMOVE ANY PERSON END DATE WHICH HAS AN LATER END DATE */ SELECT PERSON_ID
, EFFECTIVE_START_DATE
, EFFECTIVE_END_DATE
FROM PER_ALL_PEOPLE_F PEO
WHERE EXISTS (SELECT 1
FROM PER_ALL_PEOPLE_F PEO1
WHERE PEO1.PERSON_ID = PEO.PERSON_ID
AND PEO1.EFFECTIVE_END_DATE > PEO.EFFECTIVE_END_DATE) ) PEO_END
WHERE /* IF THERE ARE FUTURE DATED CHANGES
, ONLY SELECT THE CURRENT DATE TRACKED ROW */ ((SYSDATE < PEO_END.EFFECTIVE_START_DATE
AND SYSDATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE) OR (SYSDATE >= PEO_END.EFFECTIVE_START_DATE
AND PEO_END.EFFECTIVE_START_DATE = PEO.EFFECTIVE_START_DATE))
AND PEO_END.PERSON_ID = PEO.PERSON_ID
AND PEO.BUSINESS_GROUP_ID = BGR.ORGANIZATION_ID
AND PEO.PERSON_ID = PPS.PERSON_ID (+)
AND NOT EXISTS (SELECT NULL
FROM PER_PERIODS_OF_SERVICE LATER_PPS
WHERE LATER_PPS.PERSON_ID = PEO.PERSON_ID
AND LATER_PPS.DATE_START > PPS.DATE_START)
AND PEO.CORRESPONDENCE_LANGUAGE = LNG.LANGUAGE_CODE (+)
AND PEO.COUNTRY_OF_BIRTH = FTV.TERRITORY_CODE (+)
AND PEO.PERSON_ID = TREE.SUPV_PERSON_ID UNION ALL /*ALL SALES_REPS*/ SELECT RS.SALESREP_ID || '-' || ORG.ORGANIZATION_ID || '-' || INST.INSTANCE_CODE || '-SALESREP-PERS' ASSIGNMENT_PK
, 'ALL' ALL_FK
, TO_CHAR('1-NA_EDW-'|| INST.INSTANCE_CODE) SPRVSR_LVL1_FK
, RS.NAME || '-' || ORG.NAME || '(' || 'SALESREP' || ')' NAME
, RS.NAME || '-' || ORG.NAME || '(' || 'SALESREP' || ')' PERSON_DP
, ORG.NAME BUSINESS_GROUP
, RS.SALESREP_NUMBER PERSON_NUM
, RS.NAME FULL_NAME
, NULL LAST_NAME
, NVL(RS.START_DATE_ACTIVE
, RS.CREATION_DATE) START_DATE
, RS.END_DATE_ACTIVE END_DATE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') EMPLOYEE_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') APPLICANT_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') BUYER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') PLANNER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'Y') SALES_REP_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'Y') SYS_GEN_FLAG
, INST.INSTANCE_CODE INSTANCE
, TO_NUMBER(NULL) PERSON_ID
, RS.SALESREP_ID SALESREP_ID
, NULL PLANNER_CODE
, RS.ORG_ID ORGANIZATION_ID
, NVL(RS.START_DATE_ACTIVE
, RS.CREATION_DATE) EFFECTIVE_START_DATE
, NVL(RS.END_DATE_ACTIVE
, TO_DATE('4712/12/31'
, 'YYYY/MM/DD')) EFFECTIVE_END_DATE
, NULL FIRST_NAME
, NULL MIDDLE_NAMES
, NULL PREVIOUS_LAST_NAME
, NULL KNOWN_AS
, NULL TITLE
, NULL NAME_SUFFIX
, NULL NAME_PREFIX
, RS.EMAIL_ADDRESS EMAIL_ADDRESS
, NULL MARITAL_STATUS
, NULL REHIRE_RCMMNDTN
, NULL CRRSPNDNC_LANGUAGE
, TO_DATE(NULL) DATE_EMP_DATA_VRFD
, NULL DISABILITY_FLAG
, TO_DATE(NULL) RESUME_UPDATED_DATE
, NULL RESUME_EXISTS
, NULL GENDER
, NULL INTERNAL_LOCATION
, NULL MAILSTOP
, NULL FAST_PATH_EMPLOYEE
, NULL NATIONAL_IDENTIFIER
, TO_DATE(NULL) DATE_OF_BIRTH
, TO_NUMBER(NULL) FTE_CAPACITY
, NULL STUDENT_STATUS
, NULL TOWN_OF_BIRTH
, NULL REGION_OF_BIRTH
, NULL COUNTRY_OF_BIRTH
, NULL NATIONALITY
, NULL GLOBAL_PERSON_ID
, '_DF:_DUMMY:PER:PER_PEOPLE:PEO'
, '_DF:_DUMMY:PER:PERSON DEVELOPER DF:PEO'
, RS.CREATION_DATE CREATION_DATE
, GREATEST( NVL( RS.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(ORG.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))) LAST_UPDATE_DATE
FROM EDW_LOCAL_INSTANCE INST
, HR_ALL_ORGANIZATION_UNITS ORG
, RA_SALESREPS_ALL RS
WHERE RS.ORG_ID = ORG.ORGANIZATION_ID (+) UNION ALL /*ALL PLANNERS*/ SELECT MP.PLANNER_CODE || '-' || ORG.ORGANIZATION_ID || '-' || INST.INSTANCE_CODE || '-PLANNER-PERS' ASSIGNMENT_PK
, 'ALL' ALL_FK
, TO_CHAR('1-NA_EDW-'|| INST.INSTANCE_CODE) SPRVSR_LVL1_FK
, MP.PLANNER_CODE || '-' || ORG.NAME || ' (' || 'PLANNER' || ')' NAME
, MP.PLANNER_CODE || '-' || ORG.NAME || ' (' || 'PLANNER' || ')' PERSON_DP
, ORG.NAME BUSINESS_GROUP
, NULL PERSON_NUM
, MP.DESCRIPTION FULL_NAME
, NULL LAST_NAME
, MP.CREATION_DATE START_DATE
, MP.DISABLE_DATE END_DATE
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') EMPLOYEE_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') APPLICANT_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') BUYER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'Y') PLANNER_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'N') SALESREP_FLAG
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, 'Y') SYS_GEN_FLAG
, INST.INSTANCE_CODE INSTANCE
, TO_NUMBER(NULL) PERSON_ID
, TO_NUMBER(NULL) SALESREP_ID
, MP.PLANNER_CODE PLANNER_CODE
, MP.ORGANIZATION_ID ORGANIZATION_ID
, MP.CREATION_DATE EFFECTIVE_START_DATE
, NVL(MP.DISABLE_DATE
, TO_DATE('4712/12/31'
, 'YYYY/MM/DD')) EFFECTIVE_END_DATE
, NULL FIRST_NAME
, NULL MIDDLE_NAMES
, NULL PREVIOUS_LAST_NAME
, NULL KNOWN_AS
, NULL TITLE
, NULL NAME_SUFFIX
, NULL NAME_PREFIX
, MP.ELECTRONIC_MAIL_ADDRESS EMAIL_ADDRESS
, NULL MARITAL_STATUS
, NULL REHIRE_RCMMNDTN
, NULL CRRSPNDNC_LANGUAGE
, TO_DATE(NULL) DATE_EMP_DATA_VRFD
, NULL DISABILITY_FLAG
, TO_DATE(NULL) RESUME_UPDATED_DATE
, NULL RESUME_EXISTS
, NULL GENDER
, NULL INTERNAL_LOCATION
, NULL MAILSTOP
, NULL FAST_PATH_EMPLOYEE
, NULL NATIONAL_IDENTIFIER
, TO_DATE(NULL) DATE_OF_BIRTH
, TO_NUMBER(NULL) FTE_CAPACITY
, NULL STUDENT_STATUS
, NULL TOWN_OF_BIRTH
, NULL REGION_OF_BIRTH
, NULL COUNTRY_OF_BIRTH
, NULL NATIONALITY
, NULL GLOBAL_PERSON_ID
, '_DF:_DUMMY:PER:PER_PEOPLE:PEO'
, '_DF:_DUMMY:PER:PERSON DEVELOPER DF:PEO'
, MP.CREATION_DATE CREATION_DATE
, GREATEST( NVL(MP.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))
, NVL(ORG.LAST_UPDATE_DATE
, TO_DATE('01-01-2000'
, 'DD-MM-YYYY'))) LAST_UPDATE_DATE
FROM EDW_LOCAL_INSTANCE INST
, HR_ALL_ORGANIZATION_UNITS ORG
, MTL_PLANNERS MP
WHERE MP.ORGANIZATION_ID = ORG.ORGANIZATION_ID