How Payable Invoices related Payment Data is stored in Oracle Apps R12

To 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’

              );

        );

Thanks Yogesh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s