Search Results pos_ap_expense_reports_v




Overview
Explore in AI Chat

Overview

POS_AP_EXPENSE_REPORTS_V is a Purchasing (PO) module view owned by the APPS schema in Oracle E-Business Suite. It presents a consolidated, reporting-ready projection of Oracle Payables invoice data that has been combined with expense-report context. As the name implies, the view is intended to serve as the data source for expense-report reporting screens and concurrent programs rather than for transactional posting. It bridges the Payables and Purchasing schemas so that expense-related liability documents, their payment schedules, vendor information, and employee attribution can be retrieved through a single SELECT statement.

The view exposes formatted, display-oriented column values. Monetary amounts are converted to strings using FND_CURRENCY.SAFE_GET_FORMAT_MASK with a mask of 30, and several derived attributes (PO number list, withheld amount, paid-by list, approval meanings, vendor site) are produced by way of PL/SQL package functions invoked directly inside the view text. This makes the object self-contained for reporting but sensitive to the performance of the underlying packages.

Underlying Base Objects

The documented ETRM 12.2.2 metadata lists the following referenced base objects: AP_EXPENSE_REPORT_HEADERS_ALL (synonym), AP_INVOICES_ALL (synonym), AP_LOOKUP_CODES (view), AP_PAYMENT_SCHEDULES_ALL (synonym), FND_LOOKUPS (view), PER_PEOPLE_X (view), PO_VENDORS (view), and PO_VENDOR_SITES_ALL (view). The view also references the packages FND_CURRENCY, FND_GLOBAL, HR_GENERAL, HR_PERSON_NAME, HR_SECURITY, POS_AP_INVOICES_PKG, and POS_AP_INVOICE_PAYMENTS_PKG.

The primary join is between AP_INVOICES_ALL (aliased AI) and AP_PAYMENT_SCHEDULES_ALL (aliased APS) on INVOICE_ID. Two independent accesses to AP_LOOKUP_CODES (ALC1 and ALC2) resolve the invoice type and payment status display values, while AP_LOOKUP_CODES and FND_LOOKUPS supply the approval meanings for the manager and AP approval flags. Vendor data is retrieved from PO_VENDORS and PO_VENDOR_SITES_ALL, employee name from PER_PEOPLE_X or the HR packages, and the PO number list and payment number list are derived from the POS_AP_INVOICES_PKG and POS_AP_INVOICE_PAYMENTS_PKG packages rather than from direct joins. Object access is mediated by HR_SECURITY and FND_GLOBAL for operating-unit and security context.

Key Columns

Common Use Cases and Queries

Typical use cases include expense-report inquiry forms, payables aging and withholding reports, PO-to-invoice reconciliation for expense-type documents, and approval status dashboards. Because the view pre-formats amounts and resolves descriptive fields, developers can avoid repeating decode and lookup logic. A representative query filtering by vendor and payment status follows:

SELECT report_primary_key, invoice_num, description, invoice_amount, amount_remaining, invoice_type, payment_status, po_number, payment_number, vendor_site_code, full_name FROM apps.pos_ap_expense_reports_v WHERE vendor_id = :p_vendor_id AND payment_status = :p_status ORDER BY invoice_date DESC;

For withholding analysis, the formatted withheld column can be selected directly:

SELECT invoice_num, invoice_currency_code, withheld_amount_currency_code, gross_amount, discount_amount_available FROM apps.pos_ap_expense_reports_v WHERE invoice_date BETWEEN :p_from AND :p_to;

Because the view invokes PL/SQL functions per row, queries should be constrained by indexed driving columns (INVOICE_ID, VENDOR_ID, INVOICE_DATE) to limit function execution and avoid full scans. Reporting tools should also be aware that amount columns are returned as strings rather than numerics, so aggregation must occur against the underlying AP tables when numeric precision is required.