How Payable Invoices related Payment Data is stored in Oracle Apps R12? (Oracle Payments, Oracle Payables)

                                                               

In this post, we will find the tables involved in storing the Payment Data related to the   Payable INVOICE ( Invoice_id = 166014 ). All the queries given in this post and their related posts were tested in R12.1.1 Instance. 

AP_TERMS

SELECT *

FROM   AP_TERMS

WHERE  term_id IN

       ( SELECT DISTINCT terms_id

           FROM   AP_INVOICES_ALL

             WHERE  invoice_id = '166014'

       );        

AP_TERMS_LINES 

SELECT *

FROM   AP_TERMS_LINES

WHERE  term_id IN

       ( SELECT DISTINCT terms_id

           FROM   AP_INVOICES_ALL

             WHERE  invoice_id = '166014'

       ); 

AP_PAYMENT_SCHEDULES_ALL 

SELECT

  amount_remaining,

  batch_id,

  due_date,

  gross_amount,

  hold_flag,

  invoice_id,

  payment_num,

  SUBSTR(payment_status_flag,1,1) payment_status_flag,

  org_id

FROM

  AP_PAYMENT_SCHEDULES_ALL

WHERE

  invoice_id = '166014'; 

AP_INVOICE_PAYMENTS_ALL 

SELECT

  check_id,

  SUBSTR(invoice_payment_id,1,15) invoice_payment_id,

  amount,

  payment_base_amount,

  invoice_base_amount,

  accounting_date,

  period_name,

  posted_flag,

  accounting_event_id,

  invoice_id,

  org_id

FROM

  AP_INVOICE_PAYMENTS_ALL

WHERE

  invoice_id = '166014'

ORDER BY check_id ASC; 

AP_PAYMENT_DISTRIBUTIONS_ALL 

SELECT tab.*

FROM   AP_INVOICE_PAYMENTS_ALL aip,

       AP_PAYMENT_DISTRIBUTIONS_ALL tab

WHERE  aip.invoice_payment_id = tab.invoice_payment_id

AND    aip.invoice_id         = '166014';

AP_CHECKS_ALL 

SELECT

  check_id,

  check_number,

  vendor_site_code,

  amount,

  base_amount,

  checkrun_id,

  checkrun_name,

  check_date,

  SUBSTR(status_lookup_code,1,15) status_lookup_code,

  void_date,

  org_id

FROM

  AP_CHECKS_ALL

WHERE check_id IN

      ( SELECT DISTINCT check_id

        FROM   AP_INVOICE_PAYMENTS_ALL

        WHERE  invoice_id = '166014'

      ); 

AP_PAYMENT_HISTORY_ALL 

SELECT

  payment_history_id,

  check_id,

  accounting_date,

  SUBSTR(transaction_type,1,20)    transaction_type,

  posted_flag,

  SUBSTR(accounting_event_id,1,10) accounting_event_id,

  rev_pmt_hist_id,

  org_id

FROM

  AP_PAYMENT_HISTORY_ALL

WHERE check_id IN

      (SELECT DISTINCT check_id

       FROM AP_INVOICE_PAYMENTS_ALL

       WHERE invoice_id = '166014'

      )

ORDER BY payment_history_id ASC; 

AP_PAYMENT_HIST_DISTS 

SELECT aphd.*

FROM   AP_INVOICE_DISTRIBUTIONS_ALL aid,

       AP_PAYMENT_HIST_DISTS aphd,

       AP_PAYMENT_HISTORY_ALL aph

WHERE  aid.invoice_id              = '166014'

AND    aid.invoice_distribution_id = aphd.invoice_distribution_id

AND    aph.payment_history_id      = aphd.payment_history_id;

AP_RECON_DISTRIBUTIONS_ALL  

SELECT *

FROM AP_RECON_DISTRIBUTIONS_ALL

WHERE check_id IN

  ( SELECT check_id

    FROM AP_INVOICE_PAYMENTS_ALL

    WHERE invoice_id = '166014'

  );

  

AP_DOCUMENTS_PAYABLE 

SELECT

  pay_proc_trxn_type_code,

  calling_app_doc_unique_ref1 check_id,

  calling_app_doc_unique_ref2 invoice_id,

  calling_app_doc_unique_ref4 invoice_payment_id,

  calling_app_doc_ref_number invoice_number,

  payment_function,

  payment_date,

  document_date,

  document_type,

  payment_currency_code,

  payment_amount,

  payment_method_code

FROM

  AP_DOCUMENTS_PAYABLE

WHERE calling_app_id              = 200  -- Application id for Payables

AND   calling_app_doc_unique_ref2 = '166014'; 

IBY_DOCS_PAYABLE_ALL 

SELECT *

FROM   IBY_DOCS_PAYABLE_ALL

WHERE  calling_app_id            = 200

AND    calling_app_doc_unique_ref2 = '166014';

IBY_PAYMENTS_ALL 

SELECT *

FROM   IBY_PAYMENTS_ALL

WHERE  payment_id IN

       (SELECT payment_id

        FROM   IBY_DOCS_PAYABLE_ALL

        WHERE   calling_app_id              = 200

        AND     calling_app_doc_unique_ref2 = '166014'

        ); 

IBY_PAY_INSTRUCTIONS_ALL 

SELECT *

FROM   IBY_PAY_INSTRUCTIONS_ALL

WHERE  payment_instruction_id IN

       (SELECT payment_instruction_id

        FROM IBY_PAYMENTS_ALL

        WHERE payment_id IN

              (SELECT payment_id

               FROM   IBY_DOCS_PAYABLE_ALL

               WHERE   calling_app_id              = 200

               AND     calling_app_doc_unique_ref2 = '166014'

              );

        );