Tuesday, May 3, 2016

SQL Query to get all users who have access to create Requisitions and Purchase orders with their Approval Limits

 Below Query will give details of users who have access to create Requisitions and Purchase orders with their Approval Limits

SELECT user_name, user_id, full_name, country, job,
       xxta_poreq_user_resp ('PO', user_id) po_responsibilities,
       xxta_poreq_user_resp ('RQ', user_id) req_responsibilities, doc_name,
  FROM (SELECT DISTINCT fu.user_name, ppf.full_name,
                        ppf.per_information_category country, pj.NAME job,
                        fu.user_id, appr.doc_name, appr.appr_limit
                   FROM fnd_user fu,
                        per_all_people_f ppf,
                        per_all_assignments_f paf,
                        (SELECT   ppca.job_id,
                                  pcf.control_function_name doc_name,
                                  MAX (amount_limit) appr_limit
                             FROM po_position_controls_all ppca,
                                  po_control_functions pcf,
                                  po_control_rules pcr,
                                  po_control_groups_all pcga
                            WHERE ppca.control_group_id = pcr.control_group_id
                              AND pcf.control_function_id =
                              AND pcga.control_group_id = pcr.control_group_id
                              AND pcr.object_code = 'DOCUMENT_TOTAL'
                         GROUP BY ppca.job_id, pcf.control_function_name) appr,
                        per_jobs pj
                  WHERE EXISTS (
                           SELECT '1'
                             FROM fnd_compiled_menu_functions cmf,
                                  fnd_form_functions ff,
                                  fnd_form_functions_tl ffl,
                                  fnd_form_vl ffv,
                                  fnd_responsibility_vl rtl,
                                  fnd_user_resp_groups furg
                            WHERE cmf.function_id = ff.function_id
                              AND rtl.menu_id = cmf.menu_id
                              AND cmf.grant_flag = 'Y'
                              AND ff.function_id = ffl.function_id
                              AND ffv.form_id = ff.form_id
                              AND ffv.form_name IN ('POXPOEPO', 'POXRQERQ')
                              AND furg.responsibility_id =
                              AND furg.end_date IS NULL
                              AND rtl.end_date IS NULL
                              AND furg.user_id = fu.user_id)
                    AND fu.end_date IS NULL
                    AND fu.employee_id = ppf.person_id
                    AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
                                            AND ppf.effective_end_date
                    AND ppf.person_id = paf.person_id
                    AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date
                                            AND paf.effective_end_date
                    AND ppf.employee_number IS NOT NULL
                    AND paf.assignment_type IN ('E', 'C')
                    AND paf.job_id = appr.job_id
                    AND pj.job_id = paf.job_id
               ORDER BY user_name)

No comments: