AP/AR Netting Sample SQL query

Oracle Applications R12 AP/AR Netting Sample SQL Queries

 

Netting between Oracle Accounts Payables and Oracle Account Receivables

 

AP/AR Netting:

 

AP/AR NETTING AUTOMATICALLY COMPARES PAYABLES TO RECEIVABLES AND CREATES THE APPROPRIATE TRANSACTION IN EACH SYSTEM    TO NET SUPPLIER INVOICES AND CUSTOMER INVOICES

 

 

1. Create Netting control Account in segment values. Account code 2111.
2. Create 01 Netting bank
3. Create 01 Netting Branch.
4. Create 02 Netting Account.
5. Query AP/AR receipt class (seeded) and attach the Netting Bank Account in AR.
6. Create “01 Netting Agreement”, for this navigation is Receipt>Netting>netting Agreement.
7. Create a standard invoice in AP module for the supplier Advance network Devices .my invoice number is rb1567.
8. Create an invoice in AR for the customer AT & T universal card.invoice number 10953.
9. Create netting Batch “netting batch”, navigation receipt>netting >netting batch.
10. Query your netting batch in netting batch window and click the view report icon, it will take you to view netting report page, click the run button.it will run a request.

11. Again query your batch, click view report icon and again press Run button it will show the proposed netting report.

 

Sample Sql-

 

REQUIREMENTS-   

1.ONLY THOSE SUPPLIERS FOR WHICH A VALID NETTING AGREEMENT EXIST WITH A CUSTOMER.
 2.THE PAYABLES INVOICES SELECTED FOR THE REPORT SHOULD INCLUDE ONLY VALIDATED AND APPROVED INVOICES.
 3.THE RECEIVABLES INVOICES SELECTED FOR THE REPORT SHOULD ONLY INCLUDE COMPLETED INVOICES.

To pull data from Oracle Account Payables of netting :

SELECT hou.NAME organization_name,
aia.invoice_currency_code invoice_currency,
asa.vendor_name supplier, asa.segment1 supplier_number,
assa.vendor_site_code vendor_site,
aia.invoice_num invoice_number,
NVL ((aia.invoice_amount - NVL (aia.amount_paid, 0)),NULL) invoice_due,
null party_site_name,
NULL trx_number,
NULL amount_due_remaining,
DECODE (aia.invoice_currency_code,'GBP', 'AAA',aia.invoice_currency_code )
FROM fun_net_suppliers_all fnsa,
ap_suppliers asa,
ap_invoices_all aia,
ap_supplier_sites_all assa,
hr_operating_units hou
WHERE asa.vendor_id = fnsa.supplier_id
AND asa.vendor_id = aia.vendor_id
AND asa.vendor_id(+) = assa.vendor_id
AND assa.vendor_site_id = NVL (fnsa.supplier_site_id, assa.vendor_site_id)
AND assa.vendor_id(+) = fnsa.supplier_id
AND fnsa.supplier_id = aia.vendor_id
AND hou.organization_id = aia.org_id(+)
AND assa.org_id = hou.organization_id(+)
--AND hou.name in (XXX)-- operating unit
AND asa.vendor_name = NVL (:p_vendor, asa.vendor_name)
AND aia.wfapproval_status in ('WFAPPROVED','MANUALLY APPROVED','APPROVED')
AND NOT EXISTS(SELECT 'N'
FROM ap_invoice_distributions_all
WHERE NVL(match_status_flag, 'C') IN ('T', 'C', 'N', 'S')
AND invoice_id=aia.invoice_id)
AND aia.invoice_date BETWEEN TO_DATE(:p_from_date,'YYYY/MM/DD HH24:MI:SS') and TO_DATE(:p_to_date,'YYYY/MM/DD HH24:MI:SS') ;

To pull data from Oracle Account Receivables of netting:

SELECT hou.NAME organization_name,

       rcta.invoice_currency_code invoice_currency,

       asa.vendor_name supplier, asa.segment1 supplier_number,

       null vendor_site, NULL invoice_number,

       NVL (NULL, NULL) invoice_due,hps.party_site_name party_site_name,rcta.trx_number trx_number,

       NVL (apsa.amount_due_remaining, NULL) amount_due_remaining,

       DECODE (rcta.invoice_currency_code,'GBP', 'AAA',rcta.invoice_currency_code)

  FROM ap_suppliers asa,                                                      

       ap_supplier_sites_all assa,

       hz_cust_accounts_all hcaa,

       ar_payment_schedules_all apsa,

       ra_customer_trx_all rcta,

       hz_cust_site_uses_all hcsua,

       hz_cust_acct_sites_all hcasa,

       hr_operating_units hou,

       fun_net_suppliers_all fnsa,

       fun_net_customers_all fnca,

       hz_party_sites hps

 WHERE asa.vendor_id(+) = assa.vendor_id

   AND fnca.cust_account_id = hcaa.cust_account_id

   AND hcaa.cust_account_id=rcta.bill_to_customer_id

   AND rcta.customer_trx_id = apsa.customer_trx_id

   AND hcaa.cust_account_id = hcasa.cust_account_id

   AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id(+)

   and hps.party_site_id=hcasa.party_site_id

   AND hcsua.site_use_id = NVL (rcta.bill_to_site_use_id, hcsua.site_use_id)                                               

   AND fnca.agreement_id = fnsa.agreement_id

   AND fnsa.supplier_id = asa.vendor_id

   AND fnsa.supplier_site_id=assa.vendor_site_id

   AND hou.organization_id(+) = rcta.org_id

-- AND hou.name in ('XXX')--operating unit

   AND asa.vendor_name = NVL (:p_vendor, asa.vendor_name)

   AND rcta.complete_flag='Y'

   AND rcta.trx_date BETWEEN TO_DATE(:p_from_date,'YYYY/MM/DD HH24:MI:SS')

                                                    and TO_DATE(:p_to_date,'YYYY/MM/DD HH24:MI:SS')