Search Results inv_holds

Overview

APPS.APFV_EXPENSE_REPORTS is a reporting view in the Oracle E-Business Suite Payables module that presents a consolidated, denormalized picture of expense reports and credit-card-sourced invoices together with their distribution lines, approval hierarchy, vendor, and employee attributes. The view is defined over AP_INVOICES and AP_INVOICE_DISTRIBUTIONS_ALL, joined to holds, vendor, general ledger code combinations, and HR person/assignment records, producing a single flat result set suitable for expense analysis, audit reporting, and integration extracts. Because it surfaces both header-level and distribution-level detail in one row, it is frequently used as the source for expense report inquiries, approval monitoring, and downstream data feeds into external systems.

The name reflects its functional scope: "APFV" denotes an Oracle Payables (AP) view (FV convention), and "EXPENSE_REPORTS" describes the primary business document type returned. The view is owned by APPS and is exposed as a synonym, making it accessible to custom reports, BI Publisher data models, and interface programs without requiring privileged access to the base tables.

Underlying Base Objects

The view is defined over the following documented base objects:

• AP_INVOICES (synonym) — invoice header, aliased as inv; supplies invoice number, voucher number, invoice date, invoice amount, source, VAT code, org and set of books.
• AP_INVOICE_DISTRIBUTIONS_ALL (synonym) — distribution lines, aliased as inv_dist; supplies line number, amount, currency, line type, exchange rate, and receipt flags.
• AP_HOLDS_ALL (synonym) — outer-joined (inv_holds) to expose any active holds on the invoice.
• GL_CODE_COMBINATIONS (synonym) — aliased as exp_gcc; joined on the distribution's code combination for account derivation.
• PO_VENDORS (view) — aliased as pov; provides vendor name and the employee linkage.
• PER_ALL_PEOPLE_F (synonym) — used twice, as emp (the expense-reporting employee) and sup (the supervisor).
• PER_ALL_ASSIGNMENTS_F (synonym) — aliased as ass; supplies the assignment and supervisor relationship for the employee.

Joins are predominantly equi-joins on invoice, distribution, and code combination IDs, with outer joins (+) applied to the holds, supervisor person, and the effective-dated HR assignment ranges. The driving filter restricts the result set to invoices where INVOICE_TYPE_LOOKUP_CODE = 'EXPENSE REPORT' or where INVOICE_TYPE_LOOKUP_CODE = 'STANDARD' and SOURCE = 'CREDIT CARD'.

Key Columns

• INVOICE_NUM, VOUCHER_NUM, INVOICE_DATE, TOTAL — invoice header identification and the NVL-protected invoice amount.
• HOLD_LOOKUP_CODE — the hold currently applied, resolved against AP_LOOKUP_CODES (HOLD CODE).
• SUPERVISOR_ID, EMPLOYEE_NUMBER, FULL_NAME — supervisor and employee identity from the HR joins.
• VENDOR_NAME, EMPLOYEE_ID — vendor of record and the employee associated with the expense report.
• DISTRIBUTION_LINE_NUMBER, AMOUNT, LINE_TYPE_LOOKUP_CODE — distribution detail; line type resolved against AP_LOOKUP_CODES (INVOICE DISTRIBUTION TYPE).
• RECEIPT_CURRENCY_CODE, RECEIPT_MISSING_FLAG, RECEIPT_REQUIRED_FLAG, JUSTIFICATION — receipt and policy compliance indicators.
• EXCHANGE_DATE, EXCHANGE_RATE, EXCHANGE_RATE_TYPE — currency conversion attributes for the distribution.
• VAT_CODE, ORG_ID, SET_OF_BOOKS_ID, INVOICE_ID — tax and organizational context keys.

Common Use Cases and Queries

Typical uses include expense report audits, hold analysis, supervisor-level spend roll-ups, and integration extracts. A representative query lists open expense report distributions with their holds and supervisors:

SELECT invoice_num, invoice_date, total, hold_lookup_code,
       employee_number, full_name AS employee,
       distribution_line_number, amount, line_type_lookup_code
FROM   apps.apfv_expense_reports
WHERE  org_id = :p_org_id
AND    invoice_date BETWEEN :p_from AND :p_to
ORDER BY invoice_num, distribution_line_number;

To summarize expense spend by employee and currency:

SELECT employee_number, full_name, receipt_currency_code,
       SUM(amount) total_amount
FROM   apps.apfv_expense_reports
WHERE  line_type_lookup_code = 'ITEM'
GROUP BY employee_number, full_name, receipt_currency_code;

Because the view already resolves lookup codes, joins HR attributes, and includes distribution lines, it eliminates the need for custom multi-table joins in most expense-reporting scenarios.