R12 – SQL-e dla modułu Płatności oraz Zobowiązań

Kopia z bloga Link

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’
              );
        );
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’
              );
        );

 

Ten wpis został opublikowany w kategorii AP, IBY, SQL-ki i oznaczony tagami , , . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz