Search Results ap_invoices_u3

Overview

AP.AP_INVOICES_ALL is the master header table for Oracle Payables. Every invoice entered into the system — standard, prepayment, credit memo, debit memo, expense report, interest, or withholding-related — is persisted as a single row in this table. The table is the parent of invoice distributions, scheduled payments, holds, approval history, prepayment applications, and payment records, making it the central transactional object of the AP module and a mandatory junction into Procurement, Projects, Assets, and General Ledger accounting flows in both 12.1.1 and 12.2.2.

The table is owned by the AP schema, uses FND Design Data SQLAP.AP_INVOICES_ALL, and resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. In 12.2.2 the documented physical schema carries 191 columns. Given its FK topology — numerous inbound and outbound relationships centered on the surrogate key INVOICE_ID — the heuristic Data Vault classification is hub. A dimensional model would therefore treat it as the invoice grain anchor, with satellites (approval, tax, and payment attributes) and links (supplier, batch, project, purchase order) hanging off it.

Key Information Stored

The primary key is the surrogate INVOICE_ID, enforced by unique index AP_INVOICES_U1. The second unique index, AP_INVOICES_U3, covers the business-key candidate (DOC_SEQUENCE_ID, DOC_SEQUENCE_VALUE) — this is the document sequence combination that guarantees legal sequential numbering for an invoice within a legal entity and document category. This is the index most users search for when investigating "ap_invoices_u3", typically to diagnose document sequence assignment, duplicate sequence values, or missing legal numbering on a transaction.

Key operational columns include:

• VENDOR_ID, VENDOR_SITE_ID — supplier and supplier site for the invoice.
• INVOICE_NUM, INVOICE_DATE, INVOICE_TYPE_LOOKUP_CODE — the supplier's invoice reference and type.
• INVOICE_AMOUNT, BASE_AMOUNT, AMOUNT_PAID, APPROVED_AMOUNT — financial measures at header level.
• INVOICE_CURRENCY_CODE, PAYMENT_CURRENCY_CODE, EXCHANGE_RATE, EXCHANGE_RATE_TYPE — currency and conversion data.
• BATCH_ID, ORG_ID, SET_OF_BOOKS_ID, LEGAL_ENTITY_ID — organizational and ledger context.
• PAYMENT_STATUS_FLAG, APPROVAL_STATUS, WFAPPROVAL_STATUS — lifecycle and workflow state.
• DOC_SEQUENCE_ID, DOC_SEQUENCE_VALUE, DOC_CATEGORY_CODE — the unique business-key candidate noted above.
• PROJECT_ID, TASK_ID, EXPENDITURE_TYPE — Projects integration.
• VOUCHER_NUM — the accounting voucher reference after posting.

Common Use Cases and Queries

Typical query patterns include locating an invoice by supplier reference, extracting approved invoices for payment batches, or auditing document sequence assignment. A useful diagnostic for the AP_INVOICES_U3 index is:

SELECT i.invoice_id, i.invoice_num, i.doc_sequence_id,
       i.doc_sequence_value, i.doc_category_code, i.legal_entity_id
FROM   ap_invoices_all i
WHERE  i.doc_sequence_id = :seq_id
ORDER BY i.doc_sequence_value;

To report invoices awaiting payment by supplier:

SELECT i.invoice_num, i.invoice_date, i.invoice_amount,
       i.amount_paid, i.payment_status_flag
FROM   ap_invoices_all i
WHERE  i.vendor_id = :vendor_id
AND    i.payment_status_flag <> 'Y';

Common reporting scenarios cover aging and hold analysis, withholding tax exposure, prepayment application, project expenditure linkage, and audit of legal invoice numbering under local regulations such as VAT and GST.

Related Objects

• AP_INVOICE_DISTRIBUTIONS_ALL — joins on INVOICE_ID; carries line-level accounting.
• AP_INVOICE_LINES_ALL — joins on INVOICE_ID; captures matched lines against POs and receipts.
• AP_PAYMENT_SCHEDULES_ALL — joins on INVOICE_ID; due dates and payment terms.
• AP_INVOICE_PAYMENTS_ALL — joins on INVOICE_ID; links to payments.
• AP_HOLDS_ALL — joins on INVOICE_ID; active and released holds.
• AP_INVOICE_RELATIONSHIPS — links original and related invoices on ORIGINAL_INVOICE_ID / RELATED_INVOICE_ID.
• PO_VENDORS and PO_VENDOR_SITES_ALL — supplier master via VENDOR_ID and VENDOR_SITE_ID.
• AP_BATCHES_ALL — invoice batch header via BATCH_ID.
• AP_EXPENSE_REPORT_HEADERS_ALL — expense report linkage for EXPENSE REPORT type invoices.
• PA_PROJECTS_ALL, PA_TASKS — project expenditure context via PROJECT_ID and TASK_ID.