Useful Queries for Purchase Requisitions

1.Query to find requisition total amount:

 SELECT nvl(SUM(decode(order_type_lookup_code, 
'RATE', amount, 
'FIXED PRICE', amount, 
quantity * unit_price)), 0) req_amount,
po_ame_setup_pvt.get_function_currency(max(requisition_header_id)) req_func_currency, 
po_ame_setup_pvt.get_rate_type(max(requisition_header_id)) rate_type

from   po_requisition_lines_all 
where requisition_header_id = po_ame_setup_pvt.get_new_req_header_id(3915049)
and  nvl(cancel_flag, 'N') = 'N'
and  nvl(modified_by_agent_flag, 'N') = 'N';
select approved_date from po_requisition_headers_all where requisition_header_id=3915049;

2.Latest Change Request/Change Order details:
Change Order gets created for a Requisition when the req lines created onto Purchase order and then  a change need in Requisition.

 SELECT MAX (change_request_group_id) change_request_group_id, MAX(last_update_date) last_update_date
           FROM po_change_requests
          WHERE     document_header_id = p_transaction_id

                AND document_type = 'REQ';

PO_REQUISITION_HEADERS_ALL.CHANGE_PENDING_FLAG => Indicates whether requisition is in Change Order process or not

3. Requisition amount from a change request

 SELECT NVL (
                   SUM (
                      DECODE (
                         pcr3.action_type,
                         'CANCELLATION', 0,
                         DECODE (
                            prl.order_type_lookup_code,
                            'RATE', NVL (pcr4.new_amount, prl.amount),
                            'FIXED PRICE', NVL (pcr4.new_amount, prl.amount),
                              NVL (pcr1.new_price, prl.unit_price)
                            * NVL (pcr2.new_quantity, prl.quantity)))),
                   0)
           FROM po_requisition_lines_all prl,
                po_change_requests pcr1,
                po_change_requests pcr2,
                po_change_requests pcr3,
                po_change_requests pcr4
          WHERE     prl.requisition_line_id = pcr1.document_line_id(+)
                AND pcr1.change_request_group_id(+) =
                       l_change_request_group_id
                AND pcr1.request_level(+) = 'LINE'
                AND pcr1.change_active_flag(+) = 'Y'
                AND pcr1.new_price(+) IS NOT NULL
                AND prl.requisition_line_id = pcr2.document_line_id(+)
                AND pcr2.change_request_group_id(+) =
                       l_change_request_group_id
                AND pcr2.request_level(+) = 'LINE'
                AND pcr2.action_type(+) = 'DERIVED'
                AND pcr2.new_quantity(+) IS NOT NULL
                AND prl.requisition_line_id = pcr4.document_line_id(+)
                AND pcr4.change_request_group_id(+) =
                       l_change_request_group_id
                AND pcr4.request_level(+) = 'LINE'
                AND pcr4.action_type(+) = 'DERIVED'
                AND pcr4.new_amount(+) IS NOT NULL
                AND prl.requisition_line_id = pcr3.document_line_id(+)
                AND pcr3.change_request_group_id(+) =
                       l_change_request_group_id
                AND pcr3.request_level(+) = 'LINE'
                AND pcr3.action_type(+) = 'CANCELLATION'
                AND prl.requisition_header_id = p_transaction_id
                AND NVL (prl.modified_by_agent_flag, 'N') = 'N'

                AND NVL (prl.cancel_flag, 'N') = 'N';

4. Last action on PO Requisition:

SELECT TRUNC (MAX (NVL (action_date, SYSDATE)))
           FROM PO_ACTION_HISTORY
          WHERE     1 = 1
                AND object_type_code = 'REQUISITION'
                AND object_id = p_transaction_id

                AND action_code = 'SUBMIT';

5. Project Managers for Requisition:

 SELECT ppp.person_id
             FROM po_requisition_lines_all pla,
                  po_req_distributions_all pda,
                  pa_project_players ppp
            WHERE     1 = 1
                  AND requisition_header_id = p_transaction_id
                  AND pda.requisition_line_id = pla.requisition_line_id
                  AND pda.project_id = ppp.project_id
                  AND ppp.person_id IS NOT NULL
                  AND TRUNC (SYSDATE) BETWEEN TRUNC (
                                                 NVL (ppp.start_date_active,
                                                      SYSDATE))
                                          AND TRUNC (
                                                 NVL (ppp.end_date_active,
                                                      SYSDATE + 1))
                  AND ppp.project_role_type = 'PROJECT MANAGER'

         GROUP BY ppp.person_id;

6. Task managers for a requisition:

  SELECT pt.task_manager_person_id person_id
           FROM po_requisition_lines_all pla,
                po_req_distributions_all pda,
                pa_tasks pt
          WHERE     1 = 1
                AND requisition_header_id = p_transaction_id
                AND pda.requisition_line_id = pla.requisition_line_id
                AND pda.task_id = pt.task_id
                AND pt.task_manager_person_id IS NOT NULL
                AND TRUNC (SYSDATE) BETWEEN TRUNC (pt.start_date)
                                        AND TRUNC (
                                               NVL (pt.completion_date,

                                                    SYSDATE + 1));

7. Requisition Preparer and requester details:

SELECT DISTINCT h.org_id,
                         h.approved_date,
                         h.preparer_id,
                         l.to_person_id requester_id    
           FROM po_requisition_headers_all h, po_requisition_lines_all l 
          WHERE     h.requisition_header_id = p_transaction_id

                AND h.requisition_header_id = l.requisition_header_id;