Sunday, 15 May 2016

Query to Find Approval Group details like Assignment Details, Approval Limit, Account ranges for approvals For iProcurement And Purchasing Users in Oracle Apps




  SELECT fndu.user_name,
         papf.full_name,
         paaf.person_id,
         pcr.control_group_id, PCGA.CONTROL_GROUP_NAME, 
         pcr.control_rule_id,
         pcr.last_update_date,
         pcr.rule_type_code,
         pcr.object_code,
         pcr.amount_limit,
         pcr.segment1_low low1,
         pcr.segment2_low low2,
         pcr.segment3_low low3,
         pcr.segment4_low low4,
         pcr.segment5_low low5,
         pcr.segment6_low low6,
         pcr.segment7_low low7,
         pcr.segment8_low low8,
         pcr.segment1_high high1,
         pcr.segment2_high high2,
         pcr.segment3_high high3,
         pcr.segment4_high high4,
         pcr.segment5_high high5,
         pcr.segment6_high high6,
         pcr.segment7_high high7,
         pcr.segment8_high high8
    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 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)
ORDER BY fndu.user_name, pcr.control_group_id, pcr.control_rule_id

No comments:

Post a Comment