SELECT fndu.user_name,
papf.full_name,
paaf.person_id,
PCGA.CONTROL_GROUP_NAME, COUNT(*)
FROM po_control_rules pcr,
po_position_controls_all ppca,PO_CONTROL_GROUPS_all PCGA,
po_control_functions pcf,
per_all_assignments_f paaf,
per_all_people_f papf,
fnd_user fndu
WHERE pcr.control_group_id = ppca.control_group_id
AND pcr.control_group_id = pcga.control_group_id
AND PCGA.control_group_name = 'Site Controller'
AND ppca.org_id = PCGA.ORG_ID
AND PCGA.ORG_ID = 9944
AND ppca.control_function_id = pcf.control_function_id
AND pcf.CONTROL_FUNCTION_NAME = 'Approve Purchase Requisitions' ---- THIS MAKES THE QUERY FOR PURCHASE REQUISITION APPROVALS
AND ppca.job_id = paaf.job_id --- THIS MAKES THE QUERY FOR JOB BASED APPROVALS
AND paaf.effective_end_date >= SYSDATE
AND papf.effective_end_date >= SYSDATE
AND paaf.person_id = papf.person_id
AND sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE
AND CONCAT (CONCAT (paaf.person_id, '-'),
NVL (paaf.object_version_number, 0)) IN
( SELECT CONCAT (CONCAT (person_id, '-'),
NVL (MAX (object_version_number), 0))
FROM per_all_assignments_f
WHERE person_id IN
(SELECT employee_id
FROM fnd_user where person_id is not null)
GROUP BY person_id)
AND paaf.person_id = fndu.employee_id
AND sysdate between fndu.START_DATE and NVL(fndu.end_date, SYSDATE+1)
GROUP BY fndu.user_name, papf.full_name, paaf.person_id, PCGA.CONTROL_GROUP_NAME
ORDER BY fndu.user_name
No comments:
Post a Comment