Search Results proj_invamt_onhold

Overview

PA_PWP_SUPPLIER_INV_SUMMARY_V is an APPS-owned database view in the Oracle E-Business Suite Projects (PA) module. Its documented purpose is to display supplier-wise invoice amounts for a project, consolidating accounts payable invoice data that has been interfaced or staged for project-related processing. The view is part of the PA PWP (Project Workbench/Project Invoice Summary) reporting layer and is typically consumed by project billing, cost, and invoice analysis reports and concurrent programs.

The view aggregates invoice amounts at the vendor and project level across multiple currency perspectives — project functional currency, project currency, and the transaction (invoice) currency. Critically, it also isolates amounts flagged as on hold through correlated subqueries keyed on HOLD_FLAG = 'Y', allowing users to distinguish invoices that are subject to payment holds from those that are not. The view is defined in the 12.2.2 ETRM metadata as VALID and is available in both 12.1.1 and 12.2.2 environments where the PA product is installed and the PWP schema objects have been deployed.

Underlying Base Objects

The documented base objects referenced by the view are:

• PA_PWP_AP_INV_HDR — the invoice header staging table. It supplies vendor identity (VENDOR_ID, SUPPLIER_NUM, SUPPLIER_NAME), PROJECT_ID, and the HOLD_FLAG used to segregate on-hold amounts. Its primary key, PA_PWP_AP_HDR_ID, is the join key that links header rows to detail rows.
• PA_PWP_AP_INV_DTL — the invoice detail/line staging table. It provides the currency codes (PROJFUNC_CURRENCY_CODE, PROJ_CURRENCY_CODE) and all monetary measures summed by the view, including the amount-paid, unpaid, retained, and discount columns.
• PA_PROJECTS_ALL — the master project table, referenced to resolve and validate project context.
• PA_IMPLEMENTATIONS_ALL — the Projects implementation/operating unit configuration table, used to derive reporting currency settings.
• GL_PERIOD_STATUSES — the General Ledger period status table, referenced to constrain the accounting periods in scope for the summarized balances.

The header and detail staging tables are joined on PA_PWP_AP_HDR_ID. The header is aliased HDR in the view text and drives the outer aggregation, while detail sums are rolled up by PROJECT_ID and currency combination.

Key Columns

• VENDOR_ID, SUPPLIER_NUM, SUPPLIER_NAME — supplier identifiers carried from the invoice header. SUPPLIER_SITE is exposed as NULL in the documented view text, indicating that supplier-site granularity is not currently derived.
• PROJECT_ID — the project to which the summarized invoices relate.
• PROJFUNC_CURRENCY_CODE / PROJ_CURRENCY_CODE — the project functional and project currencies used for reporting translations.
• PROJFUNC_INVOICE_AMOUNT, PROJ_INVOICE_AMOUNT, INVOICE_AMOUNT — invoiced amounts expressed in project functional currency, project currency, and the transaction currency respectively.
• AMOUNT_PAID / PROJFUNC_INV_PAID_AMOUNT / PROJ_INV_PAID_AMOUNT — paid portions of the invoice.
• AMOUNT_UNPAID / PROJFUNC_INV_UNPAID_AMOUNT / PROJ_INV_UNPAID_AMOUNT — outstanding, unpaid portions.
• RETAINED_AMOUNT and DISCOUNT_AMOUNT (with PROJFUNC_ and PROJ_ variants) — retained and early-payment discount components.
• PROJFUNC_INVAMT_ONHOLD / PROJ_INVAMT_ONHOLD — correlated subquery columns that return the invoiced amount for records where HOLD_FLAG = 'Y', matching vendor, project, and currency to the outer row.
• INVOICE_CURRENCY — exposed as NULL in the documented view text.

Common Use Cases and Queries

Typical uses include supplier spend analysis by project, aged invoice and hold reporting, and reconciliation of project-related AP invoice balances to the project functional ledger. A representative query retrieves supplier-level invoice totals for a project:

• SELECT supplier_name, projfunc_currency_code, projfunc_invoice_amount, projfunc_inv_paid_amount, projfunc_inv_unpaid_amount, projfunc_invamt_onhold FROM pa_pwp_supplier_inv_summary_v WHERE project_id = :project_id ORDER BY supplier_name;

To isolate suppliers with on-hold exposure:

• SELECT supplier_name, project_id, proj_invamt_onhold FROM pa_pwp_supplier_inv_summary_v WHERE proj_invamt_onhold > 0;

Because the view performs its own GROUP BY aggregation, filters on PROJECT_ID and vendor attributes are the most efficient predicates. When joins back to PA_PROJECTS_ALL are required for project names or numbers, join on PROJECT_ID. Note that the correlated subqueries reference PA_PWP_AP_HDR_ID as the join key between header and detail, which is the same key the user searched for; any direct reporting on the staging tables must preserve this linkage to reproduce the view's supplier-wise totals correctly.