R12 - Query For Payable Invoice Payment Data is stored

TABLES INVOLVED:

AP_TERMS

AP_TERMS_LINES 

AP_PAYMENT_SCHEDULES_ALL 

AP_INVOICE_PAYMENTS_ALL 

AP_PAYMENT_DISTRIBUTIONS_ALL 

AP_CHECKS_ALL 

AP_PAYMENT_HISTORY_ALL 

AP_PAYMENT_HIST_DISTS 

AP_RECON_DISTRIBUTIONS_ALL  

AP_DOCUMENTS_PAYABLE 

IBY_DOCS_PAYABLE_ALL 

IBY_PAYMENTS_ALL 

IBY_PAY_INSTRUCTIONS_ALL 

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; 

AP_PAYMENT_DISTRIBUTIONS_ALL 

SELECT tab.*

FROM AP_PAYMENT_DISTRIBUTIONS_ALL tab,

AP_INVOICE_PAYMENTS_ALL aip

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; 

AP_PAYMENT_HIST_DISTS 

SELECT aphd.*

FROM   AP_PAYMENT_HIST_DISTS aphd,

 AP_INVOICE_DISTRIBUTIONS_ALL aid,

       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

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'

              );

        );