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;