Script for getting PO SLA Details

SELECT DISTINCT pap.last_name || ', ' || pap.first_name AS "BUYER",
                prh.segment1 AS "REQUISITION",
                (SELECT    pap2.last_name
                        || ', '
                        || pap2.first_name
                   FROM per_all_people_f pap2
                  WHERE pap2.person_id = pahv2.employee_id
                    AND TRUNC (effective_start_date) <= TRUNC (SYSDATE)
                    AND TRUNC (pap2.effective_end_date) >= TRUNC (SYSDATE))
                                                            AS "REQ_APPROVER",
                TRUNC (prh.approved_date) AS "REQ_APPROVAL_DT",
                DECODE
                   (prh.attribute_category,
                    'No', 'Opex('
                     || CASE
                           WHEN pla.purchase_basis = 'GOODS'
                           AND (SELECT plt.purchase_basis
                                  FROM po_line_types_b plt
                                 WHERE plt.line_type_id = prl.line_type_id) =
                                                                       'GOODS'
                              THEN
                                   -- tipo de aquisition_type
                                  'Goods'
                           WHEN pla.purchase_basis = 'SERVICES'
                           AND (SELECT plt.purchase_basis
                                  FROM po_line_types_b plt
                                 WHERE plt.line_type_id = prl.line_type_id) =
                                                                    'SERVICES'
                              THEN
                                   -- tipo de aquisition_type
                                  'Services'
                        END
                     || ')',
                    'Yes', prh.attribute_category
                   ) AS "ACQUISITION_TYPE",
                pha.segment1 AS "PURCHASE_ORDER",
                pahv.employee_name AS "PO_APPROVER",
                TRUNC (pha.approved_date) AS "PO_APPROVAL_DT",
                TRUNC (ABS (pha.approved_date - prh.approved_date)
                      ) AS "TIME_SPENT",
                NVL
                   ((SELECT   SUM ((pla.unit_price * pla.quantity
                                   )) amount_po
                         FROM po_headers_all pha2,
                              po_lines_all pla,
                              po_line_locations_all plla,
                              po_distributions_all pda,
                              hr_all_organization_units haou,
                              po_req_distributions_all prd,
                              po_requisition_lines_all prl,
                              po_requisition_headers_all prh2
                        WHERE 1 = 1
                          AND pla.po_header_id = pha2.po_header_id
                          AND pla.org_id = pha2.org_id
                          AND plla.po_header_id = pla.po_header_id
                          AND plla.po_line_id = pla.po_line_id
                          AND plla.org_id = pla.org_id
                          AND pda.po_header_id = plla.po_header_id
                          AND pda.po_line_id = plla.po_line_id
                          AND pda.line_location_id = plla.line_location_id
                          AND pda.org_id = plla.org_id
                          AND pha2.org_id = haou.organization_id
                          AND prd.distribution_id = pda.req_distribution_id
                          AND prd.org_id = pda.org_id
                          AND prl.requisition_line_id =
                                                       prd.requisition_line_id
                          AND prl.org_id = prd.org_id
                          AND prh2.requisition_header_id =
                                                     prl.requisition_header_id
                          AND prh2.org_id = prl.org_id
                          AND haou.organization_id = haou2.organization_id
                          --'IM BR OU'
                          AND pha2.type_lookup_code = pha.type_lookup_code
                          AND pha2.segment1 = pha.segment1
                          AND prh2.segment1 = prh.segment1
                     GROUP BY pha2.segment1, prh2.segment1),
                    0
                   ) AS sla_required,
                CASE
                   WHEN NVL
                          (  TRUNC (ABS (pha.approved_date - prh.approved_date)
                                   )
                           - (SELECT   SUM ((pla.unit_price * pla.quantity)
                                           ) amount_po
                                  FROM po_headers_all pha2,
                                       po_lines_all pla,
                                       po_line_locations_all plla,
                                       po_distributions_all pda,
                                       hr_all_organization_units haou,
                                       po_req_distributions_all prd,
                                       po_requisition_lines_all prl,
                                       po_requisition_headers_all prh2
                                 WHERE 1 = 1
                                   AND pla.po_header_id = pha2.po_header_id
                                   AND pla.org_id = pha2.org_id
                                   AND plla.po_header_id = pla.po_header_id
                                   AND plla.po_line_id = pla.po_line_id
                                   AND plla.org_id = pla.org_id
                                   AND pda.po_header_id = plla.po_header_id
                                   AND pda.po_line_id = plla.po_line_id
                                   AND pda.line_location_id =
                                                         plla.line_location_id
                                   AND pda.org_id = plla.org_id
                                   AND pha2.org_id = haou.organization_id
                                   AND prd.distribution_id =
                                                       pda.req_distribution_id
                                   AND prd.org_id = pda.org_id
                                   AND prl.requisition_line_id =
                                                       prd.requisition_line_id
                                   AND prl.org_id = prd.org_id
                                   AND prh2.requisition_header_id =
                                                     prl.requisition_header_id
                                   AND prh2.org_id = prl.org_id
                                   AND haou.organization_id =
                                                         haou2.organization_id
                                   AND pha2.type_lookup_code =
                                                          pha.type_lookup_code
                                   AND pha2.segment1 = pha.segment1
                                   AND prh2.segment1 = prh.segment1
                              GROUP BY pha2.segment1, prh2.segment1),
                           0
                          ) > 0
                      THEN 'DELAYED'
                   WHEN NVL (  TRUNC (ABS (  pha.approved_date
                                           - prh.approved_date
                                          )
                                     )
                             - (SELECT   SUM ((pla.unit_price * pla.quantity
                                              )
                                             ) amount_po
                                    FROM po_headers_all pha2,
                                         po_lines_all pla,
                                         po_line_locations_all plla,
                                         po_distributions_all pda,
                                         hr_all_organization_units haou,
                                         po_req_distributions_all prd,
                                         po_requisition_lines_all prl,
                                         po_requisition_headers_all prh2
                                   WHERE 1 = 1
                                     AND pla.po_header_id = pha2.po_header_id
                                     AND pla.org_id = pha2.org_id
                                     AND plla.po_header_id = pla.po_header_id
                                     AND plla.po_line_id = pla.po_line_id
                                     AND plla.org_id = pla.org_id
                                     AND pda.po_header_id = plla.po_header_id
                                     AND pda.po_line_id = plla.po_line_id
                                     AND pda.line_location_id =
                                                         plla.line_location_id
                                     AND pda.org_id = plla.org_id
                                     AND pha2.org_id = haou.organization_id
                                     AND prd.distribution_id =
                                                       pda.req_distribution_id
                                     AND prd.org_id = pda.org_id
                                     AND prl.requisition_line_id =
                                                       prd.requisition_line_id
                                     AND prl.org_id = prd.org_id
                                     AND prh2.requisition_header_id =
                                                     prl.requisition_header_id
                                     AND prh2.org_id = prl.org_id
                                     AND haou.organization_id =
                                                         haou2.organization_id
                                     AND pha2.type_lookup_code =
                                                          pha.type_lookup_code
                                     AND pha2.segment1 = pha.segment1
                                     AND prh2.segment1 = prh.segment1
                                GROUP BY pha2.segment1, prh2.segment1),
                             0
                            ) <= 0
                      THEN 'ON TIME'
                END AS status,
                NVL (ABS (  TRUNC (ABS (pha.approved_date - prh.approved_date))
                          - (SELECT   SUM ((pla.unit_price * pla.quantity)
                                          ) amount_po
                                 FROM po_headers_all pha2,
                                      po_lines_all pla,
                                      po_line_locations_all plla,
                                      po_distributions_all pda,
                                      hr_all_organization_units haou,
                                      po_req_distributions_all prd,
                                      po_requisition_lines_all prl,
                                      po_requisition_headers_all prh2
                                WHERE 1 = 1
                                  AND pla.po_header_id = pha2.po_header_id
                                  AND pla.org_id = pha2.org_id
                                  AND plla.po_header_id = pla.po_header_id
                                  AND plla.po_line_id = pla.po_line_id
                                  AND plla.org_id = pla.org_id
                                  AND pda.po_header_id = plla.po_header_id
                                  AND pda.po_line_id = plla.po_line_id
                                  AND pda.line_location_id =
                                                         plla.line_location_id
                                  AND pda.org_id = plla.org_id
                                  AND pha2.org_id = haou.organization_id
                                  AND prd.distribution_id =
                                                       pda.req_distribution_id
                                  AND prd.org_id = pda.org_id
                                  AND prl.requisition_line_id =
                                                       prd.requisition_line_id
                                  AND prl.org_id = prd.org_id
                                  AND prh2.requisition_header_id =
                                                     prl.requisition_header_id
                                  AND prh2.org_id = prl.org_id
                                  AND haou.organization_id =
                                                         haou2.organization_id
                                  AND pha2.type_lookup_code =
                                                          pha.type_lookup_code
                                  AND pha2.segment1 = pha.segment1
                                  AND prh2.segment1 = prh.segment1
                             GROUP BY pha2.segment1, prh2.segment1)
                         ),
                     0
                    ) AS RESULT
           FROM po_headers_all pha,
                po_lines_all pla,
                po_line_locations_all plla,
                po_distributions_all pda,
                hr_all_organization_units haou2,
                po_req_distributions_all prd,
                po_requisition_lines_all prl,
                po_requisition_headers_all prh,
                per_all_people_f pap,
                po_action_history_v pahv,
                po_action_history pahv2
          WHERE 1 = 1
            AND pla.po_header_id = pha.po_header_id
            AND pla.org_id = pha.org_id
            AND plla.po_header_id = pla.po_header_id
            AND plla.po_line_id = pla.po_line_id
            AND plla.org_id = pla.org_id
            AND pda.po_header_id = plla.po_header_id
            AND pda.po_line_id = plla.po_line_id
            AND pda.line_location_id = plla.line_location_id
            AND pda.org_id = plla.org_id
            AND pha.org_id = haou2.organization_id
            AND prd.distribution_id = pda.req_distribution_id
            AND prd.org_id = pda.org_id
            AND prl.requisition_line_id = prd.requisition_line_id
            AND prl.org_id = prd.org_id
            AND prh.requisition_header_id = prl.requisition_header_id
            AND prh.org_id = prl.org_id
            AND pap.person_id = pha.agent_id
            AND pahv.object_id = pha.po_header_id           
            AND pahv.action_code = 'APPROVE'
            AND pahv.object_type_code = 'PO'
            AND pahv.sequence_num =
                   (SELECT MAX (pahv_in.sequence_num)
                      FROM po_action_history_v pahv_in
                     WHERE pahv_in.action_code = pahv.action_code
                       AND pahv_in.object_type_code = pahv.object_type_code
                                              AND pahv_in.object_id = pahv.object_id)           
            AND pahv2.object_type_code(+) = 'REQUISITION'
            AND pahv2.action_code(+) = 'APPROVE'
            AND pahv2.object_id(+) = prh.requisition_header_id
            AND pahv2.sequence_num =
                   (SELECT MAX (pahv3.sequence_num)
                      FROM po_action_history_v pahv3
                     WHERE pahv3.object_id = pahv2.object_id
                       AND pahv2.action_code = pahv3.action_code
                       AND pahv2.object_type_code = pahv3.object_type_code)
            AND pha.authorization_status = 'APPROVED'
            AND pha.approved_flag = 'Y'
            AND pha.type_lookup_code =
                   DECODE (:p_po_type,
                           'ALL', pha.type_lookup_code,
                           :p_po_type
                          )
            AND pha.agent_id = NVL (:p_buyer_id, pha.agent_id)           
            AND DECODE (prh.attribute_category,
                        'No', 'Opex('
                         || CASE
                               WHEN pla.purchase_basis = 'GOODS'
                               AND (SELECT plt.purchase_basis
                                      FROM po_line_types_b plt
                                     WHERE plt.line_type_id = prl.line_type_id) =
                                                                       'GOODS'
                                  THEN                                     
                                      'Goods'
                               WHEN pla.purchase_basis = 'SERVICES'
                               AND (SELECT plt.purchase_basis
                                      FROM po_line_types_b plt
                                     WHERE plt.line_type_id = prl.line_type_id) =
                                                                    'SERVICES'
                                  THEN                                     
                                      'Services'
                            END
                         || ')',
                        'Yes', prh.attribute_category
                       ) =
                   NVL
                      (:p_aquisition_type,
                       DECODE
                          (prh.attribute_category,
                           'No', 'Opex('
                            || CASE
                                  WHEN pla.purchase_basis = 'GOODS'
                                  AND (SELECT plt.purchase_basis
                                         FROM po_line_types_b plt
                                        WHERE plt.line_type_id =
                                                              prl.line_type_id) =
                                                                       'GOODS'
                                     THEN                                         
                                         'Goods'
                                  WHEN pla.purchase_basis = 'SERVICES'
                                  AND (SELECT plt.purchase_basis
                                         FROM po_line_types_b plt
                                        WHERE plt.line_type_id =
                                                              prl.line_type_id) =
                                                                    'SERVICES'
                                     THEN                                         
                                         'Services'
                               END
                            || ')',
                           'Yes', prh.attribute_category
                          )
                      )
       ORDER BY 1

Script to extract Goods Received Date for Suppliers along with Invoice and PO Information

select (select hou.name from apps.hr_operating_units hou
where organization_id = (select org_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
                fetch first 1 row only)) "Entity_OU",
       TO_CHAR(ap.creation_date,'DD-MON-YYYY') "Vendor_Creation_Date",
       fu.user_name "Vendor_Created_by",
       ap.VENDOR_NAME "Vendor_Name",
       ap.SEGMENT1 "Vendor_Num", 
       (CASE
       WHEN ap.END_DATE_ACTIVE is NULL
       THEN 'Active'
       WHEN ap.END_DATE_ACTIVE is NOT NULL AND trunc(ap.END_DATE_ACTIVE) <= TRUNC(SYSDATE)
       THEN 'Inactive'
       ELSE 'Inactive'
       END)  "Vendor_Status",
        (select  vendor_site_code from apps.ap_supplier_sites_all
        where vendor_site_id = (select vendor_site_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)) "Vendor_Site",
       (select to_char(invoice_date,'DD-MON-YYYY') from apps.ap_invoices_all
       where vendor_id = ap.vendor_id
       order by creation_date
fetch first 1 row only) "First_Invoice_Date",
   (select user_name from apps.fnd_user
   where user_id = (select created_by from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only))  "Invoice_Created_By",
 (select  invoice_num from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  "Invoice_Number",
 (select  invoice_amount from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  "Invoice_Amount",
 (select  INVOICE_CURRENCY_CODE from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  "Invoice_Currency",
 (select  DESCRIPTION from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  "Invoice_Description",
 (select  AP_INVOICES_PKG.GET_APPROVAL_STATUS(apa.invoice_id,
apa.invoice_amount,
apa.payment_status_flag,
apa.invoice_type_lookup_code) from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  "Invoice_Status",
(SELECT distinct to_char(pha.creation_date, 'DD_MON-YYYY')     
  FROM po_headers_all pha
       ,po_distributions_all pda
       ,ap_invoice_distributions_all aid     
 WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aid.invoice_id= (select  invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  ) "PO_Creation_Date",
(SELECT distinct to_char(pha.APPROVED_DATE, 'DD_MON-YYYY')     
  FROM po_headers_all pha
       ,po_distributions_all pda
       ,ap_invoice_distributions_all aid     
 WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aid.invoice_id= (select  invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  ) "PO_Approval_Date",
   ( select user_name from apps.fnd_user where user_id =   (SELECT distinct pha.created_by     
  FROM apps.po_headers_all pha
       ,apps.po_distributions_all pda
       ,apps.ap_invoice_distributions_all aid     
 WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aid.invoice_id= (select  invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  )) "PO_Created_By",
(SELECT distinct pha.segment1   
  FROM apps.po_headers_all pha
       ,apps.po_distributions_all pda
       ,apps.ap_invoice_distributions_all aid     
 WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aid.invoice_id= (select  invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  ) "PO_Number",
( select sum(NVL(unit_price,0) * NVL(quantity,0)) from apps.po_lines_all pla
where po_header_id =   (SELECT distinct pha.po_header_id 
  FROM apps.po_headers_all pha
       ,apps.po_distributions_all pda
       ,apps.ap_invoice_distributions_all aid     
 WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aid.invoice_id= (select  invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  )) "PO_Amount",
(SELECT distinct pha.CURRENCY_CODE   
  FROM apps.po_headers_all pha
       ,apps.po_distributions_all pda
       ,apps.ap_invoice_distributions_all aid     
 WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aid.invoice_id= (select  invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  ) "PO_Currency",
(SELECT distinct pha.COMMENTS   
  FROM apps.po_headers_all pha
       ,apps.po_distributions_all pda
       ,apps.ap_invoice_distributions_all aid     
 WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aid.invoice_id= (select  invoice_id from apps.ap_invoices_all apa where apa.vendor_id = ap.vendor_id     
       order by creation_date
fetch first 1 row only)  ) "PO_Description"
from apps.ap_suppliers ap,
apps.fnd_user fu
where fu.user_id = ap.created_by

Script to Update Preparer/ Requester in Requisitions using API

Get data to be modified from customer and load it into a staging table. Use this table in the below script to update Preparer/ Requester for PO Requisitions.

DECLARE
l_msg_data          VARCHAR2(2000);
     l_msg_count         NUMBER;
     l_return_status     VARCHAR2(1);
     l_update_person     VARCHAR2(200);  
     l_old_personid      NUMBER;  
     l_new_personid      NUMBER;  
     l_document_type     VARCHAR2(200);  
     l_document_no_from  VARCHAR2(200);  
     l_document_no_to    VARCHAR2(200);    
     l_date_from         VARCHAR2(200);        
     l_date_to           VARCHAR2(200);
     l_commit_interval   NUMBER;
     x_date_from         DATE;
     x_date_to           DATE;
   
cursor c1 is
(
select * from  XX_req_prep_chg /*use the table with the loaded data*/

);

BEGIN
 
fnd_global.apps_initialize (/*Pass the values*/);
mo_global.set_policy_context ('S', /*Pass the value*/);
 
for r in c1
loop

   DBMS_OUTPUT.put_line ('Calling API');
 
   l_update_person      := 'ALL';  
    l_old_personid       := /*Assign the value*/;  
    l_new_personid       := /*Assign the value*/;  
    l_document_type      := NULL;  
    l_document_no_from   := r.requisition;
    l_document_no_to     := r.requisition;
    l_commit_interval    := 100;
   
   
     
             
        PO_Mass_Update_Req_GRP.Update_Persons(p_update_person    => l_update_person,
                              p_old_personid     => l_old_personid,
                                      p_new_personid     => l_new_personid,
                          p_document_type    => l_document_type,
                                      p_document_no_from => l_document_no_from,
                              p_document_no_to   => l_document_no_to,
                                      p_date_from        => x_date_from,
                              p_date_to          => x_date_to,
                                      p_commit_interval  => l_commit_interval,
                                      p_msg_data         => l_msg_data,
                                  p_msg_count        => l_msg_count,
                          p_return_status    => l_return_status);


   DBMS_OUTPUT.put_line ('API Return status: ' || l_return_status);
 
   DBMS_OUTPUT.put_line ('API p_msg_count: ' || l_msg_count);
 
   DBMS_OUTPUT.put_line ('Requisition ' || r.requisition);

commit;
 
end loop;


exception
when others then
DBMS_OUTPUT.put_line ('Inside Exception');
 
END;

Calculate PO Balances

-- Query to retrieve customer PO details

SELECT   obha.order_number, obha.sold_to_org_id, obha.cust_po_number,
         obha.header_id, obha.org_id,
         TO_DATE (obha.attribute16,'YYYY/MM/DD HH24:MI:SS') po_last_calc_date,
         obhe.start_date_active po_start_date,
         TO_CHAR (obhe.end_date_active, 'YYYY/MM/DD HH24:MI:SS') po_end_date,
         obla.ship_to_org_id, obla.line_id, obla.ordered_item,
         hca.account_number, hca.cust_account_id, hp.party_name
    FROM ont.oe_transaction_types_tl ottl,
         ont.oe_blanket_headers_all obha,
         oe_blanket_headers_ext obhe,
         oe_blanket_lines_all obla,
         oe_blanket_lines_ext oble,
         hz_cust_accounts hca,
         hz_parties hp
   WHERE 1 = 1
     AND ottl.transaction_type_id = obha.order_type_id
     AND obha.sold_to_org_id = hca.cust_account_id
     AND hp.party_id = hca.party_id
     AND obha.order_number = obhe.order_number
     AND obha.header_id = obla.header_id
     AND obla.line_id = oble.line_id
     AND ottl.NAME = 'CUST_PO_BSA'
     AND obha.cust_po_number = cust_po_i    -- Parameter 2
     AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (obhe.start_date_active),SYSDATE - 1)
                             AND NVL (TRUNC (obhe.end_date_active),SYSDATE + 1)
     AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (oble.start_date_active),SYSDATE - 1)
                             AND NVL (TRUNC (oble.end_date_active),SYSDATE + 1)
     AND ottl.LANGUAGE = 'US'
     AND obha.attribute2 = 'Open'                   --PO Status
     AND obha.attribute3 = 'Y'                      --Invoice PO
     AND hca.account_number = NVL (cust_i, hca.account_number)  -- Parameter 1
ORDER BY obha.order_number, obla.line_number;


-- Query to retrieve customer po details in Line level

SELECT   obha.order_number, obha.sold_to_org_id, obha.cust_po_number,
         obha.header_id, obha.org_id, obhe.start_date_active po_start_date,
         obhe.end_date_active po_end_date, hca.account_number,
         hcsua.site_use_id ship_to_org_id,
         TO_DATE (obha.attribute16,'YYYY/MM/DD HH24:MI:SS') po_last_calc_date
    FROM ont.oe_transaction_types_tl ottl,
         ont.oe_blanket_headers_all obha,
         oe_blanket_headers_ext obhe,
         hz_cust_accounts hca,
         hz_parties hp,
         hz_locations hl,
         hz_party_sites hps,
         hz_cust_acct_sites_all hcasa,
         hz_cust_site_uses_all hcsua
   WHERE 1 = 1
     AND ottl.transaction_type_id = obha.order_type_id
     AND obha.sold_to_org_id = hca.cust_account_id
     AND hp.party_id = hca.party_id
     AND hp.party_id = hps.party_id
     AND hl.location_id = hps.location_id
     AND hca.cust_account_id = hcasa.cust_account_id
     AND hps.party_site_id = hcasa.party_site_id
     AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
     AND hcsua.site_use_code = 'SHIP_TO'
     AND obha.order_number = obhe.order_number
     AND obha.cust_po_number = cust_po_i -- Parameter 2
     AND ottl.NAME = 'CUST_PO_BSA'
     AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (obhe.start_date_active), SYSDATE - 1)
                             AND NVL (TRUNC (obhe.end_date_active),SYSDATE + 1)
     AND ottl.LANGUAGE = 'US'
     AND obha.attribute2 = 'Open'                                  --PO Status
     AND obha.attribute3 = 'Y'                                    --Invoice PO
     AND hca.account_number = NVL (cust_acct_no_i, hca.account_number) -- Parameter 1
ORDER BY obha.order_number;




-- Query to retrieve party with invoice total amount

SELECT   party_name, account_number, rct.trx_number,
           NVL ((SELECT SUM (extended_amount)
                   FROM ra_customer_trx_lines_all
                  WHERE customer_trx_id = rct.customer_trx_id
                    AND line_type = 'LINE'),
                0
               )
         + NVL ((SELECT SUM (extended_amount)
                   FROM ra_customer_trx_lines_all
                  WHERE customer_trx_id = rct.customer_trx_id
                    AND line_type = 'TAX'),
                0
               ) inv_total_amt
    FROM hz_parties hzp,
         hz_cust_accounts hca,
         ra_customer_trx_all rct,
         ra_customer_trx_lines_all rctl,
         ra_cust_trx_line_gl_dist_all rctd,
         ra_cust_trx_types_all rctta
   WHERE hzp.party_id = hca.party_id
     AND hca.cust_account_id = rct.bill_to_customer_id
     AND rct.customer_trx_id = rctl.customer_trx_id
     AND rctl.customer_trx_line_id = rctd.customer_trx_line_id
     AND rct.cust_trx_type_id = rctta.cust_trx_type_id
     AND hca.account_number = NVL (cust_i, hca.account_number)
     AND rct.ship_to_site_use_id =
                          NVL (ship_to_site_use_id_i, rct.ship_to_site_use_id)
     AND UPPER (LTRIM (RTRIM (rctta.description))) LIKE '%INV%'
     AND rct.trx_date >= GREATEST (trx_start_dt_i, d_last_run_date_i)
     AND rct.purchase_order = customer_po_i
     AND rct.complete_flag = 'Y'
GROUP BY party_name, account_number, rct.trx_number, rct.customer_trx_id;



-- This block is used to calculate the Utilized amount

loop  
    n_net_amount:=0;
    n_vat_amount:=0;
    n_gross_amount:=0;
    n_invoice_amt:=0;
    n_adj_amt:=0;
    n_crm_amt:=0;
    n_dbm_amt:=0;
    n_tot_ship_fulfilled_amt:=0;
    n_line_counter:=0;
    n_insert_yn_flag:='Y';
                                   
    --
    -- Calculate Utilized amount for customer PO
                                   
    for inv_cur in cur_inv_amt(j.account_number,j.ship_TO_ORG_ID,j.po_start_date,j.po_last_calc_date,j.cust_po_number)
    loop
        n_invoice_amt:= n_invoice_amt+nvl(inv_cur.inv_total_amt,0);
    end loop;
                                       
    for inv_cur in cur_adj_amt(j.account_number,j.ship_TO_ORG_ID,j.po_start_date,j.po_last_calc_date,j.cust_po_number)
    loop
        n_adj_amt:=    n_adj_amt+nvl(inv_cur.adj_total_amt,0);
    end loop;
                                       
    for inv_cur in cur_crm_amt(j.account_number,j.ship_TO_ORG_ID,j.po_start_date,j.po_last_calc_date,j.cust_po_number)
    loop
        n_crm_amt:=    n_crm_amt+nvl(inv_cur.crm_total_amt,0);
    end loop;
                                       
    for inv_cur in cur_dbm_amt(j.account_number,j.ship_TO_ORG_ID,j.po_start_date,j.po_last_calc_date,j.cust_po_number)
    loop
        n_dbm_amt:=    n_dbm_amt+nvl(inv_cur.dbm_total_amt,0);
    end loop;
                                       
    n_tot_ship_fulfilled_amt:=n_invoice_amt+n_adj_amt+n_crm_amt+n_dbm_amt;                                  
    n_tot_fulfilled_amt:=n_tot_fulfilled_amt+n_tot_ship_fulfilled_amt;
                                   
end loop;