Tuesday 27 March 2012

ap tables

 AP_INVOICES_ALL

 SELECT
  ai.invoice_id,
  SUBSTR(ai.invoice_num,1,25)  invoice_num,
  SUBSTR(aps.vendor_name,1,25) vendor_name,
  ai.invoice_date,
  ai.invoice_amount,
  ai.base_amount,
  SUBSTR(ai.invoice_type_lookup_code,1,15) invoice_type_lookup_code,
  SUBSTR(ai.invoice_currency_code,1,3) invoice_currency_code,
  SUBSTR(ai.payment_currency_code,1,3) payment_currency_code,
  ai.legal_entity_id,
  ai.org_id
FROM
  AP_INVOICES_ALL ai,
  AP_SUPPLIERS aps,
  AP_SUPPLIER_SITES_ALL avs
WHERE ai.invoice_id   = '166014'
AND ai.vendor_id      = aps.vendor_id(+)
AND ai.vendor_site_id = avs.vendor_site_id(+)
ORDER BY ai.invoice_id ASC;



AP_INVOICE_LINES_ALL

SELECT
  line_number,
  line_type_lookup_code,
  line_source,
  accounting_date,
  period_name,
  deferred_acctg_flag,
  org_id
FROM
  AP_INVOICE_LINES_ALL
WHERE invoice_id = '166014'


AP_INVOICE_DISTRIBUTIONS_ALL

SELECT
  invoice_id,
  invoice_line_number,
  SUBSTR(distribution_line_number,1,8) distribution_line_number,
  SUBSTR(line_type_lookup_code,1,9) line_type_lookup_code,
  accounting_date,
  period_name,
  amount,
  base_amount,
  posted_flag,
  match_status_flag,
  encumbered_flag,
  SUBSTR(dist_code_combination_id,1,15) dist_code_combination_id,
  SUBSTR(accounting_event_id,1,15) accounting_event_id,
  SUBSTR(bc_event_id,1,15) bc_event_id,
  SUBSTR(invoice_distribution_id,1,15) invoice_distribution_id,
  SUBSTR(parent_reversal_id,1,15) parent_reversal_id,
  SUBSTR(po_distribution_id,1,15) po_distribution_id,
  org_id
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE invoice_id = '166014'
ORDER BY invoice_distribution_id,
         invoice_line_number,
          distribution_line_number ASC;

AP_HOLDS_ALL

SELECT
  held_by,
  hold_date,
  hold_lookup_code,
  SUBSTR(hold_reason,1,25) hold_reason,
  invoice_id,
  release_lookup_code,
  SUBSTR(release_reason,1,25) release_reason,
  status_flag,
  org_id
FROM
  AP_HOLDS_ALL
WHERE
  invoice_id = '166014';

AP_HOLD_CODES

SELECT *
FROM   AP_HOLD_CODES
WHERE  hold_lookup_code IN
  ( SELECT hold_lookup_code
      FROM AP_HOLDS_ALL
       WHERE invoice_id = '166014'
  ); 

AP_INV_APRVL_HIST_ALL

SELECT *
FROM   AP_INV_APRVL_HIST_ALL
WHERE  invoice_id = '166014'
ORDER BY 1;

AP_INVOICE_RELATIONSHIPS

SELECT *
FROM   AP_INVOICE_RELATIONSHIPS
WHERE  original_invoice_id    = '166014'
   OR  related_invoice_id     = '166014';



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'
              );
        );
Reconciliation - Tables Involved
    CE_STATEMENT_RECONCILS_ALL
    CE_STATEMENT_HEADERS
    CE_STATEMENT_LINES

Query:
SELECT DISTINCT cel.*
FROM   CE_STATEMENT_LINES cel,
       CE_STATEMENT_HEADERS ceh,
       CE_STATEMENT_RECONCILS_ALL csr,
       AP_INVOICE_PAYMENTS_ALL aip,
       AP_CHECKS_ALL ac
WHERE cel.statement_header_id   = ceh.statement_header_id
AND   ac.check_id               = aip.check_id
AND   aip.invoice_id            = '166014'
AND   aip.check_id              = ac.check_id
AND   ac.bank_account_id        = ceh.bank_account_id
AND   TO_CHAR(ac.check_number)  = cel.bank_trx_number
AND   csr.statement_line_id     = cel.statement_line_id;

Currencies - Tables Involved
    FND_CURRENCIES
    AP_INVOICES_ALL
    AP_SYSTEM_PARAMETERS_ALL

Query:
SELECT *
FROM   FND_CURRENCIES
WHERE  currency_code IN
  (SELECT a.invoice_currency_code
  FROM    AP_INVOICES_ALL a
  WHERE   a.invoice_id = '166014' 
  UNION 
  SELECT  b.payment_currency_code
  FROM    AP_INVOICES_ALL b
  WHERE   b.invoice_id = '166014' 
  UNION 
  SELECT  c.base_currency_code
  FROM    AP_SYSTEM_PARAMETERS_ALL c
  WHERE   NVL(c.org_id, -99) = '911'
  );

No comments:

Post a Comment