Wednesday, October 31, 2012

Query for Requisition Approval groups

SELECT DISTINCT fndu.user_name, papf.full_name, paaf.person_id,
                papf.email_address, pcg.control_group_name, pcg.description,
                pcg.org_id, pcr.control_group_id, 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_functions pcf,
                per_all_assignments_f paaf,
                per_all_people_f papf,
                fnd_user fndu,
                po_control_groups pcg
          WHERE pcr.control_group_id = ppca.control_group_id
            AND pcr.control_group_id = pcg.control_group_id
            AND ppca.org_id = 866  Operating Unit
            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 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)
                   GROUP BY person_id)
            AND paaf.person_id = fndu.employee_id
            AND (   papf.email_address LIKE '%@XX.com'
                 OR papf.email_address LIKE '%@XXX.com'
                )
            AND fndu.user_name NOT LIKE '%@%'
       ORDER BY papf.full_name, pcr.control_group_id, pcr.control_rule_id

No comments: