Search Results fv_invoice_dtl_v




Overview
Explore in AI Chat

Overview

FV_INVOICE_DTL_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite. It is delivered as part of the FV (Federal Financials) product family and carries a VALID status. The view is documented as the data source used to retrieve invoice details within the Document Cross-Reference form, a federal-specific inquiry screen that allows users to trace accounting and payment documents across the procurement, payables, and funds control cycles. In practice, the view flattens invoice-level and invoice-line-level Payables data into a single denormalized projection suitable for reporting, form-driven inquiry, and integration extracts.

The view is available in both Oracle EBS 12.1.1 and 12.2.2; the 12.2.2 ETRM metadata confirms the owner as APPS and lists the same base objects and column layout. Because it joins AP_INVOICES_ALL directly to AP_INVOICE_LINES_ALL, the view is most useful when consumers require header attributes repeated alongside each invoice line, such as in cross-reference auditing, federal payment reconciliation, or ad hoc federal reporting.

Underlying Base Objects

The view text selects from two base tables with an inner join on INVOICE_ID: AP_INVOICES_ALL (referenced as a synonym) and AP_INVOICE_LINES_ALL (also a synonym). The ETRM metadata additionally lists AP_INVOICE_DISTRIBUTIONS_ALL, AP_INVOICES_PKG, and AP_INVOICES_UTILITY_PKG as referenced objects. The distribution table is referenced indirectly through Payables logic associated with the analysis of invoice/line data, while the two packages supply derived status values. Specifically, AP_INVOICES_PKG.GET_APPROVAL_STATUS and AP_INVOICES_UTILITY_PKG.GET_HOLDS_COUNT are invoked as PL/SQL functions inside the SELECT list to compute approval status and the count of holds against each invoice.

Because the query filters with INVOICE_TYPE_LOOKUP_CODE NOT IN ('QUICKDEFAULT','QUICKMATCH'), quick invoices produced by the Quick Invoice entry paths are deliberately excluded. The result set therefore contains only standard invoice types — invoice lines that did pass through standard entry and matching routines.

Key Columns

The view exposes the following columns (names per the documented projection):

  • INVOICE_ID — Primary key of the invoice; joins back to AP_INVOICES_ALL and AP_INVOICE_DISTRIBUTIONS_ALL.
  • VENDOR_ID / VENDOR_SITE_ID — Supplier and supplier site identifiers used in Federal supplier reporting.
  • INVOICE_NUM, INVOICE_DATE, INVOICE_TYPE_LOOKUP_CODE, INVOICE_AMOUNT — Standard invoice header attributes carried onto every line row.
  • INVOICE_STATUS — Derived from AP_INVOICES_PKG.GET_APPROVAL_STATUS, based on amount, payment status flag and invoice type.
  • PAYMENT_STATUS_FLAG / STATUS — The raw flag is decoded in the SELECT list to 'NOT PAID', 'PARTIALLY PAID', or 'FULLY PAID'; STATUS presents the decoded text. Note the PROJECTED column list assigns PAYMENT_STATUS_FLAG to the decoded string and names the derived approval output STATUS.
  • HOLD_STATUS — Output of AP_INVOICES_UTILITY_PKG.GET_HOLDS_COUNT, i.e., the number of active holds on the invoice.
  • PAY_GROUP — Maps to PAY_GROUP_LOOKUP_CODE; controls payment batch selection.
  • INVOICE_LINE, INVOICE_LINE_AMOUNT, DESCRIPTION — Line number, line amount, and line description from AP_INVOICE_LINES_ALL.

Common Use Cases and Queries

Typical uses include Document Cross-Reference inquiry, federal disbursement reconciliation, hold and approval-status reporting, and vendor-level line detail extracts. A representative query listing invoice lines with decoded status is:

  • SELECT invoice_id, invoice_num, vendor_id, invoice_date, status, hold_status, pay_group, invoice_line, invoice_line_amount, description FROM apps.fv_invoice_dtl_v WHERE invoice_num = :p_invoice_num;
  • SELECT invoice_num, invoice_line, invoice_line_amount FROM apps.fv_invoice_dtl_v WHERE vendor_id = :p_vendor AND hold_status > 0;
  • SELECT pay_group, COUNT(DISTINCT invoice_id), SUM(invoice_line_amount) FROM apps.fv_invoice_dtl_v GROUP BY pay_group;

Consumers should account for the inner join: invoices with no lines will not appear, and header amounts are repeated on each line, so aggregation should use DISTINCT invoice_id or line-level columns depending on the requirement.