Thursday, October 20, 2022

Query to get basic AP Invoice and Payment information from Oracle Apps R12

 







In this post, we have given the query which gives you the joins between AP invoice tables and relevant Payment tables.  


Tested instance: R12.2.4 


Tables Involved: 


  1. AP_INVOICE_PAYMENTS_ALL 

  1. AP_CHECKS_ALL  

  1. AP_LOOKUP_CODES  

  1. IBY_PAYMENT_METHODS_VL 

  1. AP_INVOICES_ALL   

  1. AP_SUPPLIERS    


Query:  


WITH ap_invoice_payments
AS 
(
  SELECT aip.amount amount,
         DECODE(aip.invoice_payment_type, 
                'PREPAY', alc1.displayed_field, 
                iby.payment_method_name
               ) payment_method,
         ac.check_number document_number,
         ac.check_date  check_date,
	 decode(ac.status_lookup_code, 'VOIDED', 'Y', 'N') void,
	 aip.invoice_id,
         aip.creation_date
    FROM ap_invoice_payments_all  aip,
         ap_checks_all          ac,
         ap_lookup_codes        alc1,
         iby_payment_methods_vl iby
   WHERE aip.check_id = ac.check_id
     AND alc1.lookup_type (+) = 'NLS TRANSLATION'
     AND alc1.lookup_code (+) = 'PREPAY'
     AND iby.payment_method_code (+) = ac.payment_method_code
 )
SELECT aia.invoice_id,
       aia.org_id org_id,
       aia.invoice_num ,
       aia.invoice_amount,
       (SELECT meaning
          FROM fnd_lookup_values
         WHERE lookup_type = 'INVOICE TYPE'
           AND lookup_code = aia.invoice_type_lookup_code) invoice_type,
       TRUNC(aia.creation_date) inv_creation_date,
       supp.vendor_name,
       (SELECT meaning
          FROM fnd_lookup_values
         WHERE lookup_type = 'VENDOR TYPE'
           AND lookup_code = supp.vendor_type_lookup_code) vendor_type,       
	   aia.cancelled_date,
	   aia.doc_sequence_value,
       (SELECT aprhis.approver_name
          FROM apps.ap_inv_aprvl_hist_all aprhis
         WHERE aprhis.approval_history_id =
               (SELECT MIN(hist.approval_history_id)
                  FROM apps.ap_inv_aprvl_hist_all hist
                 WHERE hist.invoice_id = aprhis.invoice_id 
                   AND hist.response <> 'STOPPED')
           and approver_name is not null
           and aprhis.invoice_id = aia.invoice_id) first_approver,
	   aia.payment_currency_code CURRENCY,
       aia.amount_paid amount_paid,
       pay.creation_date paid_date,
       TO_CHAR(aia.invoice_received_date, 'DD-MON-YYYY') invoice_received_date,
       pay.amount payment_amount,
       pay.check_date check_date,
       pay.payment_method,
       pay.document_number,
  FROM ap_invoices_all              aia,
       ap_invoice_payments          pay,
       ap_suppliers                 supp
 WHERE aia.cancelled_by IS NULL
   AND aia.invoice_id = pay.invoice_id(+)
   AND supp.vendor_id = aia.vendor_id
   AND NVL(pay.void, 'N') = 'N'
   AND NVL(TRUNC(supp.end_date_active), TRUNC(SYSDATE + 1)) >=  TRUNC(SYSDATE)
   AND supp.employee_id is null
   AND aia.wfapproval_status in ('MANUALLY APPROVED', 
                                 'NOT REQUIRED',
                                  'WFAPPROVED'
                                )
 ORDER BY aia.invoice_num

0 Responses to “Query to get basic AP Invoice and Payment information from Oracle Apps R12”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.