AS nationality,NVL(PAPF.ORIGINAL_DATE_OF_HIRE,PAPF.START_DATE) AS hire_date, PAPF.LAST_UPDATE_DATE AS person_updated_date, PAAF.LAST_UPDATE_DATE AS assignment_updated_date, XHIPV.u_id, XHIPV.family_name, XHIPV.given_name, XHIPV.in_date, XHIPV.employee_status, XHIPV.office_location
FROM PER_ALL_PEOPLE_F PAPF, PER_PERIODS_OF_SERVICE PPS, PER_PERSON_TYPES PPT , PER_PERSON_TYPE_USAGES_F PPU, PER_ALL_ASSIGNMENTS_F PAAF, PER_ALL_PEOPLE_F SUP, PER_JOBS PJ, PER_GRADES PG, HR_ALL_ORGANIZATION_UNITS HAOU, XXTP_HR_IDM_PEOPLE_V XHIPV
WHERE PPT.USER_PERSON_TYPE IN('Employee','Contractor') AND PPU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID AND PPU.PERSON_ID = PAPF.PERSON_ID
AND PAAF.PERSON_ID = PAPF.PERSON_ID AND PAPF.PERSON_ID = PPS.PERSON_ID AND PAAF.PRIMARY_FLAG = 'Y' AND PAAF.SUPERVISOR_ID = SUP.person_id(+) AND PAAF.job_id = PJ.job_id(+) AND PAAF.grade_id = PG.grade_id(+) AND PAAF.organization_id = HAOU.organization_id(+) AND PAAF.period_of_service_id = PPS.period_of_service_id(+) AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y' AND SUP.CURRENT_EMPLOYEE_FLAG(+) = 'Y'
AND(TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE) AND PPU.EFFECTIVE_END_DATE > sysdate
AND(TRUNC(SYSDATE) BETWEEN PPU.EFFECTIVE_START_DATE AND PPU.EFFECTIVE_END_DATE) AND(TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)
AND(TRUNC(SYSDATE) BETWEEN SUP.EFFECTIVE_START_DATE(+) AND SUP.EFFECTIVE_END_DATE(+)) AND PAPF.PERSON_ID = XHIPV.PERSON_ID UNION ALL
SELECT PAPF.PERSON_ID AS person_id, PAPF.FIRST_NAME AS first_name, PAPF.LAST_NAME AS last_name, PAPF.EMAIL_ADDRESS AS email_address, PPT.USER_PERSON_TYPE user_person_type, PAPF.EFFECTIVE_START_DATE AS effective_start_date, PAPF.EFFECTIVE_END_DATE AS effective_end_date, PAPF.NPW_NUMBER AS employee_number, PPS.ACTUAL_TERMINATION_DATE AS actual_termination_date, sysdate as SYSTEM_DATE, PAPF.BUSINESS_GROUP_ID AS business_group_id, PAAF.SUPERVISOR_ID AS supervisor_id , SUP.FULL_NAME AS supervisor_name, PJ.NAME AS job, PG.NAME AS grade, HAOU.NAME AS department, PAPF.TOWN_OF_BIRTH AS town_of_birth, PPU.PERSON_TYPE_ID AS person_type_id, PAPF.REGION_OF_BIRTH AS region_of_birth, PAPF.COUNTRY_OF_BIRTH AS country_of_birth, PAPF.NATIONAL_IDENTIFIER AS national_identifier, PAPF.TITLE AS title, PAPF.MARITAL_STATUS AS marital_status, PAPF.SEX AS sex, PAPF.DATE_OF_BIRTH AS date_of_birth, PAPF.NATIONALITY AS nationality,NVL(PAPF.ORIGINAL_DATE_OF_HIRE,PAPF.START_DATE) AS hire_date, PAPF.LAST_UPDATE_DATE AS person_updated_date, PAAF.LAST_UPDATE_DATE AS assignment_updated_date, XHIPV.u_id, XHIPV.family_name, XHIPV.given_name, XHIPV.in_date, XHIPV.employee_status, XHIPV.office_location
FROM PER_ALL_PEOPLE_F PAPF, PER_PERIODS_OF_PLACEMENT PPS, PER_PERSON_TYPES PPT , PER_PERSON_TYPE_USAGES_F PPU, PER_ALL_ASSIGNMENTS_F PAAF,
PER_ALL_PEOPLE_F SUP, PER_JOBS PJ, PER_GRADES PG, HR_ALL_ORGANIZATION_UNITS HAOU, XXTP_HR_IDM_PEOPLE_V XHIPV WHERE PPT.USER_PERSON_TYPE IN('Contingent Employee','Contingent Worker') AND PPU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID AND PPU.PERSON_ID = PAPF.PERSON_ID
AND PAAF.PERSON_ID = PAPF.PERSON_ID AND PAPF.PERSON_ID = PPS.PERSON_ID AND PAAF.PRIMARY_FLAG = 'Y' AND PAAF.SUPERVISOR_ID = SUP.person_id(+) AND PAAF.job_id = PJ.job_id(+) AND PAAF.grade_id = PG.grade_id(+) AND PAAF.organization_id = HAOU.organization_id(+) AND PAPF.CURRENT_NPW_FLAG = 'Y' AND SUP.CURRENT_EMPLOYEE_FLAG(+) = 'Y'
AND(TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE) AND PPU.EFFECTIVE_END_DATE > sysdate
AND (TRUNC(SYSDATE) BETWEEN PPU.EFFECTIVE_START_DATE AND PPU.EFFECTIVE_END_DATE) AND(TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)
AND(TRUNC(SYSDATE) BETWEEN SUP.EFFECTIVE_START_DATE(+) AND SUP.EFFECTIVE_END_DATE(+)) AND PAPF.PERSON_ID = XHIPV.PERSON_ID ) resultTable --
) --WHERE Row_Num BETWEEN
order by person_id
4.2 HRMS_CURRENT_FUTURE_EMPLOYEE_RECON_QUERY
SELECT * FROM ( SELECT resultTable.*, rownum Row_Num FROM (
SELECT PAPF.PERSON_ID AS person_id, PAPF.FIRST_NAME AS first_name, PAPF.LAST_NAME AS last_name, PAPF.EMAIL_ADDRESS AS email_address, PPT.USER_PERSON_TYPE user_person_type, PAPF.EFFECTIVE_START_DATE AS effective_start_date, PAPF.EFFECTIVE_END_DATE AS effective_end_date, PAPF.EMPLOYEE_NUMBER AS employee_number, PPS.ACTUAL_TERMINATION_DATE AS actual_termination_date, sysdate as SYSTEM_DATE, PAPF.BUSINESS_GROUP_ID AS business_group_id, PAAF.SUPERVISOR_ID AS supervisor_id , SUP.FULL_NAME AS supervisor_name, PJ.NAME AS job, PG.NAME AS grade, HAOU.NAME AS department, PAPF.LAST_UPDATE_DATE AS person_updated_date, PAAF.LAST_UPDATE_DATE AS assignment_updated_date ,PAPF.TOWN_OF_BIRTH AS town_of_birth, PPU.PERSON_TYPE_ID AS person_type_id, PAPF.REGION_OF_BIRTH AS region_of_birth, PAPF.COUNTRY_OF_BIRTH AS country_of_birth, PAPF.NATIONAL_IDENTIFIER AS national_identifier, PAPF.TITLE AS title, PAPF.MARITAL_STATUS AS marital_status, PAPF.SEX AS sex, PAPF.DATE_OF_BIRTH AS date_of_birth, PAPF.NATIONALITY AS nationality, NVL(PAPF.ORIGINAL_DATE_OF_HIRE,PAPF.START_DATE) AS hire_date, XHIPV.u_id, XHIPV.family_name, XHIPV.given_name, XHIPV.in_date, XHIPV.employee_status, XHIPV.office_location
FROM PER_ALL_PEOPLE_F PAPF, PER_PERIODS_OF_SERVICE PPS, PER_PERSON_TYPES PPT , PER_PERSON_TYPE_USAGES_F PPU, PER_ALL_ASSIGNMENTS_F PAAF, PER_ALL_PEOPLE_F SUP, PER_JOBS PJ, PER_GRADES PG, HR_ALL_ORGANIZATION_UNITS HAOU, XXTP_HR_IDM_PEOPLE_V XHIPV WHERE PPT.USER_PERSON_TYPE IN('Employee','Contractor') AND PPU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID AND PPU.PERSON_ID = PAPF.PERSON_ID
AND PAAF.PERSON_ID = PAPF.PERSON_ID AND PAPF.PERSON_ID = PPS.PERSON_ID AND PAAF.PRIMARY_FLAG = 'Y' AND PAAF.SUPERVISOR_ID = SUP.person_id(+) AND PAAF.job_id = PJ.job_id(+)
AND PAAF.grade_id = PG.grade_id(+) AND PAAF.organization_id = HAOU.organization_id(+) AND PAAF.period_of_service_id = PPS.period_of_service_id(+)
AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y' AND SUP.CURRENT_EMPLOYEE_FLAG(+) = 'Y'
AND(TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE) AND(TRUNC(SYSDATE) BETWEEN PPU.EFFECTIVE_START_DATE AND PPU.EFFECTIVE_END_DATE) AND(TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)
AND(TRUNC(SYSDATE) BETWEEN SUP.EFFECTIVE_START_DATE(+) AND SUP.EFFECTIVE_END_DATE(+)) AND PAPF.PERSON_ID = XHIPV.PERSON_ID
UNION
SELECT PAPF.PERSON_ID AS person_id, PAPF.FIRST_NAME AS first_name, PAPF.LAST_NAME AS last_name, PAPF.EMAIL_ADDRESS AS email_address, PPT.USER_PERSON_TYPE AS user_person_type, PAPF.EFFECTIVE_START_DATE AS effective_start_date, PAPF.EFFECTIVE_END_DATE AS effective_end_date,PAPF.EMPLOYEE_NUMBER AS employee_number, PPS.ACTUAL_TERMINATION_DATE AS actual_termination_date, sysdate as SYSTEM_DATE, PAPF.BUSINESS_GROUP_ID AS business_group_id, PAAF.SUPERVISOR_ID AS supervisor_id , SUP.FULL_NAME supervisor_name, PJ.NAME AS job, PG.NAME AS grade, HAOU.NAME AS department, PAPF.LAST_UPDATE_DATE AS person_updated_date, PAAF.LAST_UPDATE_DATE AS assignment_updated_date, PAPF.TOWN_OF_BIRTH AS town_of_birth, PAPF.PERSON_TYPE_ID AS person_type_id, PAPF.REGION_OF_BIRTH AS region_of_birth, PAPF.COUNTRY_OF_BIRTH AS country_of_birth, PAPF.NATIONAL_IDENTIFIER AS national_identifier, PAPF.TITLE AS title, PAPF.MARITAL_STATUS AS marital_status, PAPF.SEX AS sex, PAPF.DATE_OF_BIRTH AS date_of_birth, PAPF.NATIONALITY AS nationality, NVL(PAPF.ORIGINAL_DATE_OF_HIRE,PAPF.START_DATE) AS hire_date, XHIPV.u_id, XHIPV.family_name, XHIPV.given_name, XHIPV.in_date, XHIPV.employee_status, XHIPV.office_location
FROM PER_ALL_PEOPLE_F PAPF, PER_PERSON_TYPES PPT , PER_PERSON_TYPE_USAGES_F PPU, PER_ALL_ASSIGNMENTS_F PAAF, PER_ALL_PEOPLE_F SUP, PER_JOBS PJ, PER_GRADES PG, HR_ALL_ORGANIZATION_UNITS HAOU, PER_PERIODS_OF_SERVICE PPS, XXTP_HR_IDM_PEOPLE_V XHIPV
WHERE PPT.USER_PERSON_TYPE IN('Employee','Contractor') AND PPU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID AND PPU.PERSON_ID = PAPF.PERSON_ID AND PAAF.PERSON_ID = PAPF.PERSON_ID
AND PAAF.PRIMARY_FLAG = 'Y' AND PAAF.SUPERVISOR_ID = SUP.person_id(+) AND PAAF.job_id = PJ.job_id(+) AND PAAF.grade_id = PG.grade_id(+) AND PAAF.organization_id = HAOU.organization_id(+)
AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y' AND SUP.CURRENT_EMPLOYEE_FLAG(+) = 'Y' AND PAAF.period_of_service_id = PPS.period_of_service_id(+) AND PAPF.PERSON_ID = PPS.PERSON_ID AND PPS.DATE_START > SYSDATE
AND PPS.DATE_START BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE AND PPS.DATE_START BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE AND PPS.DATE_START BETWEEN PPU.EFFECTIVE_START_DATE AND PPU.EFFECTIVE_END_DATE
AND SUP.EFFECTIVE_START_DATE(+) <= PAAF.EFFECTIVE_START_DATE AND SUP.EFFECTIVE_END_DATE(+) > TRUNC(SYSDATE) AND PAPF.PERSON_ID = XHIPV.PERSON_ID
UNION
SELECT PAPF.PERSON_ID AS person_id, PAPF.FIRST_NAME AS first_name, PAPF.LAST_NAME AS last_name, PAPF.EMAIL_ADDRESS AS email_address, PPT.USER_PERSON_TYPE user_person_type, PAPF.EFFECTIVE_START_DATE AS effective_start_date, PAPF.EFFECTIVE_END_DATE AS effective_end_date, PAPF.NPW_NUMBER AS employee_number, PPS.ACTUAL_TERMINATION_DATE AS actual_termination_date, sysdate as SYSTEM_DATE, PAPF.BUSINESS_GROUP_ID AS business_group_id, PAAF.SUPERVISOR_ID AS supervisor_id , SUP.FULL_NAME AS supervisor_name, PJ.NAME AS job, PG.NAME AS grade, HAOU.NAME AS department, PAPF.LAST_UPDATE_DATE AS person_updated_date, PAAF.LAST_UPDATE_DATE AS assignment_updated_date,PAPF.TOWN_OF_BIRTH AS town_of_birth, PPU.PERSON_TYPE_ID AS person_type_id, PAPF.REGION_OF_BIRTH AS region_of_birth, PAPF.COUNTRY_OF_BIRTH AS country_of_birth, PAPF.NATIONAL_IDENTIFIER AS national_identifier, PAPF.TITLE AS title, PAPF.MARITAL_STATUS AS marital_status, PAPF.SEX AS sex, PAPF.DATE_OF_BIRTH AS date_of_birth, PAPF.NATIONALITY AS nationality,NVL(PAPF.ORIGINAL_DATE_OF_HIRE,PAPF.START_DATE) AS hire_date, XHIPV.u_id, XHIPV.family_name, XHIPV.given_name, XHIPV.in_date, XHIPV.employee_status, XHIPV.office_location
FROM PER_ALL_PEOPLE_F PAPF, PER_PERIODS_OF_PLACEMENT PPS, PER_PERSON_TYPES PPT , PER_PERSON_TYPE_USAGES_F PPU, PER_ALL_ASSIGNMENTS_F PAAF, PER_ALL_PEOPLE_F SUP, PER_JOBS PJ, PER_GRADES PG, HR_ALL_ORGANIZATION_UNITS HAOU, XXTP_HR_IDM_PEOPLE_V XHIPV WHERE PPT.USER_PERSON_TYPE IN('Contingent Employee','Contingent Worker') AND PPU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID AND PPU.PERSON_ID = PAPF.PERSON_ID
AND PAAF.PERSON_ID = PAPF.PERSON_ID AND PAPF.PERSON_ID = PPS.PERSON_ID AND PAAF.PRIMARY_FLAG = 'Y' AND PAAF.SUPERVISOR_ID = SUP.person_id(+) AND PAAF.job_id = PJ.job_id(+)
AND PAAF.grade_id = PG.grade_id(+) AND PAAF.organization_id = HAOU.organization_id(+) AND PAPF.CURRENT_NPW_FLAG = 'Y' AND SUP.CURRENT_EMPLOYEE_FLAG(+) = 'Y'
AND(TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE) AND (TRUNC(SYSDATE) BETWEEN PPU.EFFECTIVE_START_DATE AND PPU.EFFECTIVE_END_DATE) AND(TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)
AND(TRUNC(SYSDATE) BETWEEN SUP.EFFECTIVE_START_DATE(+) AND SUP.EFFECTIVE_END_DATE(+)) AND PAPF.PERSON_ID = XHIPV.PERSON_ID
UNION
SELECT PAPF.PERSON_ID AS person_id, PAPF.FIRST_NAME AS first_name, PAPF.LAST_NAME AS last_name, PAPF.EMAIL_ADDRESS AS email_address, PPT.USER_PERSON_TYPE AS user_person_type, PAPF.EFFECTIVE_START_DATE AS effective_start_date, PAPF.EFFECTIVE_END_DATE AS effective_end_date,PAPF.NPW_NUMBER AS employee_number, PPS.ACTUAL_TERMINATION_DATE AS actual_termination_date, sysdate as SYSTEM_DATE, PAPF.BUSINESS_GROUP_ID AS business_group_id, PAAF.SUPERVISOR_ID AS supervisor_id , SUP.FULL_NAME supervisor_name, PJ.NAME AS job, PG.NAME AS grade, HAOU.NAME AS department, PAPF.LAST_UPDATE_DATE AS person_updated_date, PAAF.LAST_UPDATE_DATE AS assignment_updated_date, PAPF.TOWN_OF_BIRTH AS town_of_birth, PAPF.PERSON_TYPE_ID AS person_type_id, PAPF.REGION_OF_BIRTH AS region_of_birth, PAPF.COUNTRY_OF_BIRTH AS country_of_birth, PAPF.NATIONAL_IDENTIFIER AS national_identifier, PAPF.TITLE AS title, PAPF.MARITAL_STATUS AS marital_status, PAPF.SEX AS sex, PAPF.DATE_OF_BIRTH AS date_of_birth, PAPF.NATIONALITY AS nationality,NVL(PAPF.ORIGINAL_DATE_OF_HIRE,PAPF.START_DATE) AS hire_date, XHIPV.u_id, XHIPV.family_name, XHIPV.given_name, XHIPV.in_date, XHIPV.employee_status, XHIPV.office_location
FROM PER_ALL_PEOPLE_F PAPF, PER_PERSON_TYPES PPT , PER_PERSON_TYPE_USAGES_F PPU, PER_ALL_ASSIGNMENTS_F PAAF, PER_ALL_PEOPLE_F SUP, PER_JOBS PJ, PER_GRADES PG, HR_ALL_ORGANIZATION_UNITS HAOU, PER_PERIODS_OF_PLACEMENT PPS, XXTP_HR_IDM_PEOPLE_V XHIPV WHERE PPT.USER_PERSON_TYPE IN('Contingent Worker','Contingent Employee') AND PPU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID AND PPU.PERSON_ID = PAPF.PERSON_ID AND PAAF.PERSON_ID = PAPF.PERSON_ID
AND PAAF.PRIMARY_FLAG = 'Y' AND PAAF.SUPERVISOR_ID = SUP.person_id(+) AND PAAF.job_id = PJ.job_id(+) AND PAAF.grade_id = PG.grade_id(+) AND PAAF.organization_id = HAOU.organization_id(+)
AND PAPF.CURRENT_NPW_FLAG = 'Y' AND SUP.CURRENT_EMPLOYEE_FLAG(+) = 'Y' AND PAPF.PERSON_ID = PPS.PERSON_ID AND PPS.DATE_START > SYSDATE
AND PPS.DATE_START BETWEEN
PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE AND PPS.DATE_START BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE AND PPS.DATE_START BETWEEN PPU.EFFECTIVE_START_DATE AND PPU.EFFECTIVE_END_DATE
AND SUP.EFFECTIVE_START_DATE(+) <= PAAF.EFFECTIVE_START_DATE AND SUP.EFFECTIVE_END_DATE(+) > TRUNC(SYSDATE) AND PAPF.PERSON_ID = XHIPV.PERSON_ID
) resultTable --