Find all PO_Requisitions amounting greater than $100K (USD) and also there functional currency amount

Below query will help to find all PO_Requisitions amounting greater than $100K (USD) and also there functional currency amount. 


  SELECT prha.requisition_header_id,
         prha.segment1,
         gdr.conversion_rate,
         ROUND (
            SUM (
                 NVL (prda.req_line_quantity, 1)
               * NVL (prla.unit_price, 1)
               * NVL (
                    DECODE (gdr.from_currency, 'USD', 1, gdr.conversion_rate),
                    1)),
            2)
            usd_amount,
         gdr.to_currency,
         gdr.from_currency,
         prla.rate,
         ROUND (
            SUM (
                 NVL (prda.req_line_quantity, 1)
               * NVL (prla.unit_price, 1)
               * NVL (prla.rate, 1)),
            2)
            line_currency_amount,
         prla.currency_code line_currency,
         ROUND (
            SUM (NVL (prda.req_line_quantity, 1) * NVL (prla.unit_price, 1)),
            2)
            actual_requisition_amount,
         gs.currency_code func_currency,
         TRUNC (prha.creation_date) creation_date,
         gcc.segment3,
         prha.org_id
    FROM po_requisition_lines_all prla,
         po_req_distributions_all prda,
         po_requisition_headers_all prha,
         gl_code_combinations_kfv gcc,
         gl_sets_of_books gs,
         gl_daily_rates gdr
   WHERE     prha.requisition_header_id = prla.requisition_header_id
         AND NVL (prla.cancel_flag, 'N') <> 'Y'
         --         AND  prha.org_id = 9944
         AND prla.requisition_line_id = prda.requisition_line_id
         AND prha.authorization_status = 'APPROVED'
         AND prda.code_combination_id = gcc.code_combination_id
         AND gs.set_of_books_id = prda.set_of_books_id
         --         AND gs.currency_code <> prla.currency_code
         AND gdr.from_currency = gs.currency_code(+)
         AND gdr.to_currency(+) = 'USD'
         AND TRUNC (gdr.conversion_date) =
                TRUNC (NVL (prla.rate_date, prha.creation_date))
--         AND gcc.segment3 IN ('15510','15610','15620' )
--         AND prha.requisition_header_id = :1
GROUP BY prha.requisition_header_id,
         prha.segment1,
         prla.rate,
         gdr.conversion_rate,
         TRUNC (prha.creation_date),
         gcc.segment3,
         gdr.to_currency,
         gdr.from_currency,
         gs.currency_code,
         prla.currency_code,
         prha.org_id
  HAVING SUM (
              NVL (prda.req_line_quantity, 1)
            * NVL (prla.unit_price, 1)
            * NVL (DECODE (gdr.from_currency, 'USD', 1, gdr.conversion_rate),
                   1)) > 100000

Find Employee- Supervisor Hierarchy in Oracle Apps

  SELECT papf.employee_number,           papf.person_id,
           papf.full_name,
           ppg.segment1 officer_flag, --executive flag for officers (Y is officer, N is not)
           NVL (spapf.full_name, 'NONE') supervisor_name,
           spapf.person_id supv_person_id,
           LEVEL
      FROM per_people_x papf,
           PER_PEOPLE_X SPAPF,
           per_assignments_x paaf,
           pay_people_groups ppg
     WHERE     papf.person_id = paaf.person_id
           AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
           AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date
           and sysdate between SPAPF.EFFECTIVE_START_DATE and SPAPF.EFFECTIVE_END_DATE
           --AND xxx_person_type.is_employee(:p_eff_date, papf.person_id) = 'TRUE'
           AND paaf.assignment_type IN ('E', 'C')
           AND paaf.supervisor_id = spapf.person_id
           AND paaf.people_group_id = ppg.people_group_id
           AND paaf.primary_flag = 'Y'
START WITH papf.employee_number = :p_emp_num --emp num of employee or top level supervisor?
CONNECT BY PRIOR spapf.employee_number = papf.employee_number --AND LEVEL < :p_level
  ORDER BY LEVEL DESC
;

--------------------------------------------------------------------
OR

    SELECT LEVEL seq,
           e.person_id,
           e.grade_id,
           e.job_id,
           e.supervisor_id,
           e.employee_number,
           e.full_name
      FROM (SELECT DISTINCT paf.person_id,
                            paf.grade_id,
                            paf.job_id,
                            paf.supervisor_id,
                            ppf.employee_number,
                            ppf.full_name
              FROM    per_all_people_f ppf
                   LEFT JOIN
                      per_all_assignments_f paf
                   ON     ppf.person_id = paf.person_id
                      AND paf.person_id IS NOT NULL
                      AND (SYSDATE BETWEEN ppf.effective_start_date
                                       AND ppf.effective_end_date)
                      AND (SYSDATE BETWEEN paf.effective_start_date
                                       AND paf.effective_end_date)) e
CONNECT BY person_id = PRIOR supervisor_id
START WITH employee_number = :p_employee_number         --person_id = :P_PERSON_ID
  ORDER BY LEVEL
;

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

Find the persons with particular approval group 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

Script to change Workflow Notification Timeout from backend for easy testing in Oracle Apps

To set the value for Requisition Approval Time-out Reminder Notifications for both New Requisitions and Requisition Change Orders; one has to download the workflow and do the respective changes in Attribute and again upload the workflow. If one wants to do testing for approval process it's quite long process to do the changes and again revert back. 
Below queries will help to update the workflow notification values from back-end (all values are in min) for various reminder notifications. 

We need to run different scripts for Purchase Requisition and Requisition Change Orders.


Note: In all queries, The Time-out value will be shown in Minutes.

Scripts for Purchase Requisition

Queries to Verify and Update Existing Time-out Value in Minutes for the 1st Reminder Notification
------------------------------------------------------------------------------------------------

SELECT aav.PROCESS_ACTIVITY_ID, 
       pa.process_item_type,
       pa.process_name,
       pa.process_version,
       pa.activity_item_type,
       pa.activity_name,
       aav.value_type,
       aav.text_value,
       aav.number_value
  FROM wf_process_activities pa, wf_activity_attr_values aav
 WHERE    1=1 
       AND pa.process_name = 'XX_NOTIFY_APPROVER_PROCESS_O'
       AND aav.process_activity_id = pa.instance_id
       AND activity_name = 'PO_REQ_APPROVE'
       AND aav.name = '#TIMEOUT'


UPDATE WF_ACTIVITY_ATTR_VALUES
   SET NUMBER_VALUE = 2      --2880
 WHERE process_activity_id IN
          (SELECT aav.PROCESS_ACTIVITY_ID
             FROM wf_process_activities pa, wf_activity_attr_values aav
            WHERE     1 = 1
                  AND pa.process_name = 'XX_NOTIFY_APPROVER_PROCESS_O'
                  AND aav.process_activity_id = pa.instance_id
                  AND activity_name = 'PO_REQ_APPROVE'
                  AND aav.name = '#TIMEOUT')

Queries to Verify and Update Existing Time-out Value in Minutes for the 2nd Reminder Notification
----------------------------------------------------------------------------------------------------------
UPDATE WF_ACTIVITY_ATTR_VALUES
   SET NUMBER_VALUE = 2          --2880
 WHERE process_activity_id IN
          (SELECT aav.PROCESS_ACTIVITY_ID
             FROM wf_process_activities pa, wf_activity_attr_values aav
            WHERE     1 = 1
                  AND pa.process_name = 'XX_NOTIFY_APPROVER_PROCESS_O'
                  AND aav.process_activity_id = pa.instance_id
                  AND activity_name = 'PO_REQ_REMINDER1'
                  AND aav.name = '#TIMEOUT')

Queries to Verify and Update Existing Time-out Value in Minutes for the 3rd Reminder Notification
---------------------------------------------------------------------------------------------------------
UPDATE WF_ACTIVITY_ATTR_VALUES
   SET NUMBER_VALUE = 2         --2880
 WHERE process_activity_id IN
          (SELECT aav.PROCESS_ACTIVITY_ID
             FROM wf_process_activities pa, wf_activity_attr_values aav
            WHERE     1 = 1
                  AND pa.process_name = 'XX_NOTIFY_APPROVER_PROCESS_O'
                  AND aav.process_activity_id = pa.instance_id
                  AND activity_name = 'PO_REQ_REMINDER2'
                  AND aav.name = '#TIMEOUT')
   
Query to verify the values for all 3 Reminders Time-out values using a single query 
---------------------------------------------------------------------------------------------------------

SELECT pa.process_item_type,
       pa.process_name,
       pa.process_version,
       pa.activity_item_type,
       pa.activity_name,
       aav.value_type,
       aav.text_value,
       aav.number_value
FROM   apps.wf_process_activities pa,
       apps.wf_activity_attr_values aav
WHERE  pa.process_item_type = 'REQAPPRV'
       AND aav.process_activity_id = pa.instance_id
       AND aav.NAME = '#TIMEOUT' ;

Scripts for Requisition Change Order

Query to Verify Existing Time-out Value in Minutes for the Requisition Change Order Time-out  Notification  
-------------------------------------------------------------------------------------------------------------

SELECT aav.*, aav.number_value
FROM   apps.wf_activity_attr_values aav
WHERE  aav.NAME = '#TIMEOUT'
       AND aav.process_activity_id IN (SELECT pa.instance_id
                                       FROM   apps.wf_process_activities pa
                                       WHERE
           pa.activity_name = 'PO_REQ_APPROVE' AND
            pa.process_item_type = 'POREQCHA') ;

            
Query to update the Existing Time-out Value in Minutes for the Requisition Change Order Time-out  Notification
-----------------------------------------------------------------------------------------------------------
UPDATE apps.wf_activity_attr_values aav
SET    aav.number_value = 10080 -- In Minutes, for 7 days it is 7*24*60 = 10080
WHERE  aav.NAME = '#TIMEOUT'
       AND aav.process_activity_id IN (SELECT pa.instance_id
                                      FROM   apps.wf_process_activities pa
                                      WHERE
           pa.activity_name = 'PO_REQ_APPROVE' AND
            pa.process_item_type = 'POREQCHA') ;
commit;

Note: This will update 2 rows as it has 2 workflow processes linked to it in Requisition Change Order (POREQCHA) workflow.

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

List of Requisitions Approved by those who in Site Controller Group in Oracle Apps

WITH REQ_5000
     AS (  SELECT prha.requisition_header_id,
                  prha.segment1,prha.org_id,
                  SUM (
                     NVL (prda.req_line_quantity, 0) * NVL (prla.unit_price, 0))
                     amount,
             FROM po_requisition_lines_all prla,
                  po_req_distributions_all prda,
                  po_requisition_headers_all prha
            WHERE     prha.requisition_header_id = prla.requisition_header_id
                  AND NVL (prla.cancel_flag, 'N') <> 'Y'
                  AND prla.requisition_line_id = prda.requisition_line_id
                  AND prha.AUTHORIZATION_STATUS = 'APPROVED'
         --         AND       prla.requisition_header_id = :1
         GROUP BY prha.requisition_header_id, prha.segment1, prha.org_id
           HAVING SUM (
                       NVL (prda.req_line_quantity, 0)
                     * NVL (prla.unit_price, 0)) > 5000),
     MAX_APPROVER
     AS (  SELECT palh.document_id,
                  palh.approval_list_header_id,
                  MAX (approval_list_line_id) approval_list_line_id
             FROM po_approval_list_headers palh, po_approval_list_lines pall
            WHERE     1 = 1
                  --      AND palh.document_id = :1
                  AND palh.approval_list_header_id =
                         pall.approval_list_header_id
         GROUP BY palh.document_id, palh.approval_list_header_id)
SELECT req_5000.requisition_header_id,
       req_5000.segment1,req_5000.org_id,
       req_5000.amount,
       pall.approver_id,
       ppf.FULL_NAME
  FROM req_5000,
       max_approver,
       po_approval_list_lines pall,
       per_people_f ppf
 WHERE     1 = 1
       --      AND req_5000.requisition_header_id = :1
       AND req_5000.requisition_header_id = max_approver.document_id
       AND pall.approval_list_line_id = max_approver.approval_list_line_id
       AND ppf.person_id = pall.approver_id
       AND ppf.person_id  IN (select distinct person_id
                                from per_all_assignments_f paaf, po_position_controls_all ppca , PO_CONTROL_GROUPS_all pcga
                               where 1=1
                                 AND paaf.job_id = ppca.job_id
                                 AND ppca.control_group_id = pcga.control_group_id
                                 AND PCGA.control_group_name = 'Site Controller')
        AND ppf.person_id NOT IN (5262,20517,55021,55104,55296,55897,78271,78874,79790,81023,102046,106006,111306,114966,116526,137401,143367,143388,144166,149171,150407,158046,184591,185551,220756,222272,222760,223933,224233,235338,239709,240968)
--  List of all XX_FINANCE_APPROVERS
       AND SYSDATE BETWEEN ppf.effective_start_date
                       AND ppf.effective_end_date
--GROUP BY  pall.approver_id, ppf.FULL_NAME

List of Approver who in Site Controller Group

WITH REQ_5000
     AS (  SELECT prha.requisition_header_id,
                  prha.segment1,
                  SUM (
                     NVL (prda.req_line_quantity, 0) * NVL (prla.unit_price, 0))
                     amount
             FROM po_requisition_lines_all prla,
                  po_req_distributions_all prda,
                  po_requisition_headers_all prha
            WHERE     prha.requisition_header_id = prla.requisition_header_id
                  AND NVL (prla.cancel_flag, 'N') <> 'Y'
                  AND prla.requisition_line_id = prda.requisition_line_id
                  AND prha.AUTHORIZATION_STATUS = 'APPROVED'
         --         AND       prla.requisition_header_id = :1
         GROUP BY prha.requisition_header_id, prha.segment1
           HAVING SUM (
                       NVL (prda.req_line_quantity, 0)
                     * NVL (prla.unit_price, 0)) > 5000),
     MAX_APPROVER
     AS (  SELECT palh.document_id,
                  palh.approval_list_header_id,
                  MAX (approval_list_line_id) approval_list_line_id
             FROM po_approval_list_headers palh, po_approval_list_lines pall
            WHERE     1 = 1
                  --      AND palh.document_id = :1
                  AND palh.approval_list_header_id =
                         pall.approval_list_header_id
         GROUP BY palh.document_id, palh.approval_list_header_id)
SELECT --req_5000.requisition_header_id,
       --req_5000.segment1,
       --req_5000.amount,
       pall.approver_id,
       ppf.FULL_NAME,
       count(*)
  FROM req_5000,
       max_approver,
       po_approval_list_lines pall,
       per_people_f ppf
 WHERE     1 = 1
       --      AND req_5000.requisition_header_id = :1
       AND req_5000.requisition_header_id = max_approver.document_id
       AND pall.approval_list_line_id = max_approver.approval_list_line_id
       AND ppf.person_id = pall.approver_id
       AND ppf.person_id  IN (select distinct person_id
                                from per_all_assignments_f paaf, po_position_controls_all ppca , PO_CONTROL_GROUPS_all pcga
                               where 1=1
                                 AND paaf.job_id = ppca.job_id
                                 AND ppca.control_group_id = pcga.control_group_id
                                 AND PCGA.control_group_name = 'Site Controller')
-- List of all site controllers
        AND ppf.person_id NOT IN (5262,20517,55021,55104,55296,55897,78271,78874,79790,81023,102046,106006,111306,114966,116526,137401,143367,143388,144166,149171,150407,158046,184591,185551,220756,222272,222760,223933,224233,235338,239709,240968)
--  List of all XX_FINANCE_APPROVERS
       AND SYSDATE BETWEEN ppf.effective_start_date
                       AND ppf.effective_end_date
GROUP BY  pall.approver_id, ppf.FULL_NAME

---==========================================================================================================