Search Results fv_invoice_returns_all_u1

Overview

FV.FV_INVOICE_RETURNS_ALL is a transactional table in the Oracle E-Business Suite Financials (FV) schema that stores information about returned invoices used as part of due date calculation. It is a central component of the invoice-to-payment lifecycle, capturing the invoice identifiers, vendor information, amounts, and set of books context required to determine appropriate payment due dates when invoices are returned or adjusted. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, indicating a moderately update-intensive transactional profile typical of reference and workflow-driven data. The object is marked VALID in the ETRM 12.2.2 reference and carries 27 documented columns.

From a Data Vault modeling perspective, the mined classification is a link. This suggests the table functions as an associative entity bridging invoices, vendors, vendor sites, and sets of books rather than a pure reference hub or a descriptive satellite. Its primary key, FV_INVOICE_RETURNS_ALL_PK, is defined on INVOICE_ID, and the unique index FV_INVOICE_RETURNS_ALL_U1 provides a business-key candidate on the same column.

Key Information Stored

The most significant columns define the identity and context of each returned invoice record:

• INVOICE_ID (NUMBER 15, mandatory) — Surrogate primary key and business-key candidate; uniquely identifies the returned invoice.
• INVOICE_NUM (VARCHAR2 50) — Human-readable invoice number used for reconciliation and reporting.
• VENDOR_ID (NUMBER 15) — Foreign key to PO_VENDORS, identifying the supplier.
• VENDOR_SITE_ID (NUMBER 15) — Foreign key to PO_VENDOR_SITES_ALL, identifying the purchasing site.
• INVOICE_AMOUNT (NUMBER 15) — Monetary value of the returned invoice.
• SET_OF_BOOKS_ID (NUMBER 15) — Foreign key to GL_SETS_OF_BOOKS_11I, establishing the accounting ledger context.
• ORG_ID (NUMBER 15) — Organization identifier supporting multi-org access control.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE — Standard Who columns for audit lineage.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 (VARCHAR2 150 each) — Descriptive flexfield segments for extensible, client-specific attributes.

The unique index FV_INVOICE_RETURNS_ALL_U1 enforces uniqueness on INVOICE_ID, reinforcing the one-record-per-returned-invoice model.

Common Use Cases and Queries

This table is primarily consumed by due date calculation logic and by reporting on returned invoices. Typical queries include:

• Retrieving all returned invoices for a vendor: SELECT * FROM FV.FV_INVOICE_RETURNS_ALL WHERE VENDOR_ID = :vendor_id;
• Aggregating returned amounts by ledger: SELECT SET_OF_BOOKS_ID, SUM(INVOICE_AMOUNT) FROM FV.FV_INVOICE_RETURNS_ALL GROUP BY SET_OF_BOOKS_ID;
• Joining to AP_INVOICES_ALL to reconcile returned invoices against the payables subledger using INVOICE_ID.
• Multi-org reporting filtered by ORG_ID in conjunction with MO operating unit security profiles.

Because INVOICE_ID is both the primary key and the unique index column, lookups by invoice are highly efficient.

Related Objects

Documented foreign keys establish the following significant relationships:

• AP_INVOICES_ALL — joined via FV_INVOICE_RETURNS_ALL.INVOICE_ID = AP_INVOICES_ALL.INVOICE_ID; the core payables invoice header.
• PO_VENDORS — joined via VENDOR_ID; supplier master.
• PO_VENDOR_SITES_ALL — joined via VENDOR_SITE_ID; supplier site detail.
• GL_SETS_OF_BOOKS_11I — joined via SET_OF_BOOKS_ID; accounting ledger definition.
• FND_USER — referenced through LAST_UPDATED_BY and CREATED_BY for audit attribution.

These relationships confirm the table's role as a link associating invoice, vendor, site, and ledger dimensions, supporting both real-time due date processing and downstream payables reporting.