How Payable Invoices related data is stored in Oracle Apps R12

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

AP_BATCHES_ALL

AP_PAYMENT_SCHEDULES_ALL

 

Thanks Yogesh