***This Query will display the details(Columns) at employee & assignment level.
SELECT ppf.employee_number,
TO_CHAR (ppf.original_date_of_hire, 'DD-MON-YYYY') hire_date,
ppf.title, ppf.first_name, ppf.last_name, ppf.full_name,
(SELECT fl.meaning
FROM apps.fnd_lookup_values fl
WHERE 1 = 1
AND fl.lookup_type = 'SEX'
AND fl.lookup_code = ppf.sex
AND ROWNUM = 1) gender,
TO_CHAR (ppf.date_of_birth, 'DD-MON-YYYY') date_of_birth,
ppf.region_of_birth,
(SELECT fl.meaning nationality
FROM apps.fnd_lookup_values fl
WHERE 1 = 1
AND TRIM (ppf.per_information12) = fl.lookup_code
AND fl.lookup_type = 'AE_NATIONALITY'
AND fl.LANGUAGE = 'US'
AND ROWNUM = 1) country_of_birth,
(SELECT fl.meaning nationality
FROM apps.fnd_lookup_values fl
WHERE 1 = 1
AND TRIM (ppf.per_information18) = fl.lookup_code
AND fl.lookup_type = 'AE_NATIONALITY'
AND fl.LANGUAGE = 'US'
AND ROWNUM = 1) nationality,
(SELECT pac.segment1
FROM apps.per_person_analyses pa,
apps.per_special_info_types_v pst,
apps.per_analysis_criteria pac
WHERE 1 = 1
AND ppf.person_id = pa.person_id
AND SYSDATE BETWEEN NVL (pa.date_from, SYSDATE)
AND NVL (pa.date_to, SYSDATE)
AND pa.id_flex_num = pst.id_flex_num
AND pst.NAME = 'CNIA Pass Number'
AND pst.enabled_flag = 'Y'
AND pa.analysis_criteria_id = pac.analysis_criteria_id
AND pac.enabled_flag = 'Y'
AND ROWNUM = 1) cnia_pass_num,
(SELECT pac.segment1
FROM apps.per_person_analyses pa,
apps.per_special_info_types_v pst,
apps.per_analysis_criteria pac
WHERE 1 = 1
AND ppf.person_id = pa.person_id
AND SYSDATE BETWEEN NVL (pa.date_from, SYSDATE)
AND NVL (pa.date_to, SYSDATE)
AND pa.id_flex_num = pst.id_flex_num
AND pst.NAME = 'Emirate ID'
AND pst.enabled_flag = 'Y'
AND pa.analysis_criteria_id = pac.analysis_criteria_id
AND pac.enabled_flag = 'Y'
AND ROWNUM = 1) emirate_id,
(SELECT pac.segment1
FROM apps.per_person_analyses pa,
apps.per_special_info_types_v pst,
apps.per_analysis_criteria pac
WHERE 1 = 1
AND ppf.person_id = pa.person_id
AND SYSDATE BETWEEN NVL (pa.date_from, SYSDATE)
AND NVL (pa.date_to, SYSDATE)
AND pa.id_flex_num = pst.id_flex_num
AND pst.NAME = 'Health Certificate Number'
AND pst.enabled_flag = 'Y'
AND pa.analysis_criteria_id = pac.analysis_criteria_id
AND pac.enabled_flag = 'Y'
AND ROWNUM = 1) health_cert_num,
(SELECT pac.segment1
FROM apps.per_person_analyses pa,
apps.per_special_info_types_v pst,
apps.per_analysis_criteria pac
WHERE 1 = 1
AND ppf.person_id = pa.person_id
AND SYSDATE BETWEEN NVL (pa.date_from, SYSDATE)
AND NVL (pa.date_to, SYSDATE)
AND pa.id_flex_num = pst.id_flex_num
AND pst.NAME = 'Insurance Info.'
AND pst.enabled_flag = 'Y'
AND pa.analysis_criteria_id = pac.analysis_criteria_id
AND pac.enabled_flag = 'Y'
AND ROWNUM = 1) insu_card_num,
(SELECT pac.segment2
FROM apps.per_person_analyses pa,
apps.per_special_info_types_v pst,
apps.per_analysis_criteria pac
WHERE 1 = 1
AND ppf.person_id = pa.person_id
AND SYSDATE BETWEEN NVL (pa.date_from, SYSDATE)
AND NVL (pa.date_to, SYSDATE)
AND pa.id_flex_num = pst.id_flex_num
AND pst.NAME = 'Insurance Info.'
AND pst.enabled_flag = 'Y'
AND pa.analysis_criteria_id = pac.analysis_criteria_id
AND pac.enabled_flag = 'Y'
AND ROWNUM = 1) insu_company_name,
(SELECT pac.segment2
FROM apps.per_person_analyses pa,
apps.per_special_info_types_v pst,
apps.per_analysis_criteria pac
WHERE 1 = 1
AND ppf.person_id = pa.person_id
AND SYSDATE BETWEEN NVL (pa.date_from, SYSDATE)
AND NVL (pa.date_to, SYSDATE)
AND pa.id_flex_num = pst.id_flex_num
AND pst.NAME = 'Labor Card Info.'
AND pst.enabled_flag = 'Y'
AND pa.analysis_criteria_id = pac.analysis_criteria_id
AND pac.enabled_flag = 'Y'
AND ROWNUM = 1) labour_card_num,
(SELECT pac.segment1
FROM apps.per_person_analyses pa,
apps.per_special_info_types_v pst,
apps.per_analysis_criteria pac
WHERE 1 = 1
AND ppf.person_id = pa.person_id
AND SYSDATE BETWEEN NVL (pa.date_from, SYSDATE)
AND NVL (pa.date_to, SYSDATE)
AND pa.id_flex_num = pst.id_flex_num
AND pst.NAME = 'Passport Number'
AND pst.enabled_flag = 'Y'
AND pa.analysis_criteria_id = pac.analysis_criteria_id
AND pac.enabled_flag = 'Y'
AND ROWNUM = 1) passport_num,
(SELECT pac.segment1
FROM apps.per_person_analyses pa,
apps.per_special_info_types_v pst,
apps.per_analysis_criteria pac
WHERE 1 = 1
AND ppf.person_id = pa.person_id
AND SYSDATE BETWEEN NVL (pa.date_from, SYSDATE)
AND NVL (pa.date_to, SYSDATE)
AND pa.id_flex_num = pst.id_flex_num
AND pst.NAME = 'Unified Number'
AND pst.enabled_flag = 'Y'
AND pa.analysis_criteria_id = pac.analysis_criteria_id
AND pac.enabled_flag = 'Y'
AND ROWNUM = 1) unified_num,
(SELECT pac.segment1
FROM apps.per_person_analyses pa,
apps.per_special_info_types_v pst,
apps.per_analysis_criteria pac
WHERE 1 = 1
AND ppf.person_id = pa.person_id
AND SYSDATE BETWEEN NVL (pa.date_from, SYSDATE)
AND NVL (pa.date_to, SYSDATE)
AND pa.id_flex_num = pst.id_flex_num
AND pst.NAME = 'Work Permit ID'
AND pst.enabled_flag = 'Y'
AND pa.analysis_criteria_id = pac.analysis_criteria_id
AND pac.enabled_flag = 'Y'
AND ROWNUM = 1) work_permit_id,
ho.NAME ORGANIZATION, pj.NAME job, pp.NAME POSITION, pg.NAME grade,
pr.payroll_name, hl.location_code LOCATION, pam.assignment_number,
gsb.NAME ledger, hsk.segment2 civil_reg_num,
hsk.segment3 social_sec_num,
(SELECT DISTINCT TO_CHAR
(pps.actual_termination_date,
'DD-MON-YYYY'
)
FROM apps.per_periods_of_service pps
WHERE 1 = 1 AND ppf.person_id = pps.person_id)
actual_termination_date,
--Added on 16-Dec-2019 as per Raj Requirement
(SELECT DISTINCT TO_CHAR
(pps.projected_termination_date,
'DD-MON-YYYY'
)
FROM apps.per_periods_of_service pps
WHERE 1 = 1 AND ppf.person_id = pps.person_id)
projected_termination_date,
--Added on 19-Dec-2019 as per Raj Requirement,
TO_CHAR (ppf.last_update_date, 'DD-MON-YYYY') employee_update_date, --Added on 19-Dec-2019 as per Raj Requirement,
TO_CHAR (pam.last_update_date, 'DD-MON-YYYY') assignment_update_date --Added on 19-Dec-2019 as per Raj Requirement,
FROM apps.per_all_people_f ppf,
apps.per_all_assignments_f pam,
apps.hr_all_organization_units ho,
apps.per_jobs pj,
apps.per_all_positions pp,
apps.per_grades pg,
apps.pay_all_payrolls_f pr,
apps.hr_locations_all_tl hl,
apps.gl_ledgers gsb,
apps.hr_soft_coding_keyflex hsk
WHERE 1 = 1
-- AND ppf.employee_number = '1000032'
AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.person_id = pam.person_id
AND SYSDATE BETWEEN pam.effective_start_date AND pam.effective_end_date
AND pam.organization_id = ho.organization_id
AND pam.job_id = pj.job_id(+)
AND pam.position_id = pp.position_id(+)
AND pam.grade_id = pg.grade_id(+)
AND pam.payroll_id = pr.payroll_id(+)
AND pam.location_id = hl.location_id(+)
AND pam.set_of_books_id = gsb.ledger_id(+)
AND pam.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id(+)
AND ppf.employee_number = NVL (:p_emp_num, ppf.employee_number)
No comments:
Post a Comment