Wednesday, December 29, 2010

How Payable Invoices related data is stored in Oracle Apps R12? (Oracle Payables)

                                                                                  

In this post, we will find the tables involved in storing the Data related to an   PAYABLE INVOICE ( Invoice_id = 166014 ) in various modules of Oracle Apps R12. All the queries given in this post and their related posts were tested in R12.1.1 Instance.


 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';





Payable Invoice Data in other Modules
  1. Payables Invoice Data In Oracle Apps R12
  2. Payable Invoice Payment Data
  3. Payables Invoice Reconciliation Data
  4. Payable Invoice Suppliers Data
  5. Sub-Ledger Accounting Data of Payable Invoice
  6. Sub-Ledger Accounting data for Invoice Payments
  7. Query to Fetch Prepayment and Invoice Data

0 Responses to “How Payable Invoices related data is stored in Oracle Apps R12? (Oracle Payables)”

Post a Comment

Subscribe to: Post Comments (Atom)

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.


All Rights Reserved Oracle Apps Knowledge Sharing | Home by Team Search