Sunday, 15 May 2016

List Employees which are active and has approval group as site controller in Oracle Apps

  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