R12-Query For Payable Invoice data is stored in Oracle Account Payable Tables

Here we find which tables involved in storing the Data related to a PAYABLE INVOICE in Oracle Apps R12.

 AP_INVOICES_ALL

 SELECT ai.invoice_id,

         SUBSTR (aps.vendor_name, 1, 25) vendor_name,

         SUBSTR (ai.invoice_num, 1, 25) invoice_num,

         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 = '1234567'

         AND ai.vendor_id = aps.vendor_id(+)

         AND ai.vendor_site_id = avs.vendor_site_id(+)

ORDER BY ai.invoice_id;

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

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 = '1234567'

ORDER BY invoice_line_number,

         invoice_distribution_id,

         distribution_line_number;

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

AP_HOLD_CODES

SELECT *

  FROM AP_HOLD_CODES

 WHERE hold_lookup_code IN (SELECT hold_lookup_code

                              FROM AP_HOLDS_ALL

                             WHERE invoice_id = '1234567');

AP_INV_APRVL_HIST_ALL

  SELECT *

    FROM AP_INV_APRVL_HIST_ALL

   WHERE Invoice_id = '1234567'

ORDER BY 1;

AP_INVOICE_RELATIONSHIPS:

SELECT *

  FROM AP_INVOICE_RELATIONSHIPS

 WHERE original_invoice_id = '1234567';