SELECT ppx.employee_number staff_no, ppx.attribute14 requester_name,
hr_general.decode_organization (ppx.business_group_id) outstation,
pt.NAME, TO_CHAR (paa.date_start, 'DD-Mon-RRRR') from_date,
TO_CHAR (paa.date_end, 'DD-Mon-RRRR') TO_DATE, 'Approved' status,
TO_CHAR (TRUNC (paa.creation_date), 'DD-Mon-RRRR') creation_date,
xx.last_update_date submission_date, xx.creation_date approval_date,
ppx1.full_name approver_name
FROM per_absence_attendances paa,
per_absence_attendance_types pt,
per_people_x ppx,
fnd_user fu,
per_people_x ppx1,
(SELECT paa.creation_date, pth.last_update_date,
paa.absence_attendance_id
FROM pqh_ss_transaction_history pth,
pqh_ss_step_history psh,
per_absence_attendances paa
WHERE pth.transaction_history_id = psh.transaction_history_id
AND paa.absence_attendance_id = psh.pk1
AND pth.process_name = 'XX') xx
WHERE paa.business_group_id IN (XX)
AND paa.absence_attendance_type_id = pt.absence_attendance_type_id
AND pt.date_end IS NULL
AND paa.person_id = ppx.person_id
AND xx.absence_attendance_id = paa.absence_attendance_id
AND fu.user_id = paa.created_by
AND fu.employee_id = ppx1.person_id
hr_general.decode_organization (ppx.business_group_id) outstation,
pt.NAME, TO_CHAR (paa.date_start, 'DD-Mon-RRRR') from_date,
TO_CHAR (paa.date_end, 'DD-Mon-RRRR') TO_DATE, 'Approved' status,
TO_CHAR (TRUNC (paa.creation_date), 'DD-Mon-RRRR') creation_date,
xx.last_update_date submission_date, xx.creation_date approval_date,
ppx1.full_name approver_name
FROM per_absence_attendances paa,
per_absence_attendance_types pt,
per_people_x ppx,
fnd_user fu,
per_people_x ppx1,
(SELECT paa.creation_date, pth.last_update_date,
paa.absence_attendance_id
FROM pqh_ss_transaction_history pth,
pqh_ss_step_history psh,
per_absence_attendances paa
WHERE pth.transaction_history_id = psh.transaction_history_id
AND paa.absence_attendance_id = psh.pk1
AND pth.process_name = 'XX') xx
WHERE paa.business_group_id IN (XX)
AND paa.absence_attendance_type_id = pt.absence_attendance_type_id
AND pt.date_end IS NULL
AND paa.person_id = ppx.person_id
AND xx.absence_attendance_id = paa.absence_attendance_id
AND fu.user_id = paa.created_by
AND fu.employee_id = ppx1.person_id
Quite helpful. I have modified the same query and used base table per_all_people_f instead of secured view per_people_x
ReplyDeletesharing the same
SELECT ppx.employee_number emp_no, ppx.full_name requester_name,
hr_general.decode_organization (ppx.business_group_id) BG,
pt.NAME, TO_CHAR (paa.date_start, 'DD-Mon-RRRR hh:mm:ss') from_date,
TO_CHAR (paa.date_end, 'DD-Mon-RRRR hh:mm:ss') TO_DATE,
PAA.ABSENCE_ATTENDANCE_ID,
PAA.DATE_NOTIFICATION,
TO_CHAR (TRUNC (paa.creation_date), 'DD-Mon-RRRR hh:mm:ss') approval_date,
TO_CHAR(trunc(pth.last_update_date), 'DD-Mon-RRRR hh:mm:ss') Last_update,
ppx1.full_name approver_name
FROM per_absence_attendances paa,
per_absence_attendance_types pt,
per_all_people_f ppx,
fnd_user fu,
per_all_people_f ppx1,
pqh_ss_transaction_history pth,
pqh_ss_step_history psh
where 1=1
and pth.transaction_history_id = psh.transaction_history_id
AND paa.absence_attendance_id = psh.pk1
AND pth.process_name = 'HR_GENERIC_APPROVAL_PRC'
AND paa.absence_attendance_type_id = pt.absence_attendance_type_id
AND pt.date_end IS NULL
AND paa.person_id = ppx.person_id
AND fu.user_id = paa.created_by
AND fu.employee_id = ppx1.person_id
AND trunc(sysdate) between ppx.effective_start_date and ppx.effective_end_date
AND trunc(sysdate) between ppx1.effective_start_date and ppx1.effective_end_date
order by 1,2,3,5;