Results for “retained_amount”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The PA_PWP_SUPPLIER_INVOICE_V view in the APPS schema belongs to the Oracle Projects (PA) product family and exposes supplier invoice details associated with project-related transactions. Within Oracle E-Business Suite 12.1.1 and 12.2.2, the view serves as a reporting and integration layer that consolidates invoice header information, payment status, hold indicators, and aggregated monetary amounts across multiple currencies. It is particularly relevant to Project Procurement and Payables workflows, where project managers and financial analysts require visibility into supplier invoices, amounts paid, amounts unpaid, retainage, and discounts at both the project functional currency and the project currency levels.

The view is defined with a VALID status and is owned by the APPS schema, meaning it is typically called from custom reports, Oracle Business Intelligence Publisher (BI Publisher) data templates, or ad-hoc SQL queries executed by users with appropriate APPS-level or synonym-based privileges.

Underlying Base Objects

The view is constructed over six documented base objects:

• PA_PWP_AP_INV_HDR (SYNONYM) — invoice header data such as invoice number, vendor, supplier name, invoice date, PO number, and hold flags.
• PA_PWP_AP_INV_DTL (SYNONYM) — invoice detail lines supplying project and currency information and the aggregated amount columns.
• PA_PROJECTS_ALL (SYNONYM) — project master data referenced for project identification and organizational context.
• PA_IMPLEMENTATIONS_ALL (SYNONYM) — implementation and set of books linkage.
• PA_LOOKUPS (VIEW) — lookup values providing the translated MEANING for payment status.
• GL_PERIOD_STATUSES (SYNONYM) — general ledger period status used in the subquery that computes open accounting period adjustments.

The header table is aliased HDR, the detail table DTL, and lookups LKP. Aggregation via SUM() over the detail rows produces a single row per invoice/project/currency combination.

Key Columns

The view exposes descriptive attributes and several numeric measures:

• Identification: INVOICE_ID, INVOICE_NUM, VENDOR_ID, SUPPLIER_NAME, SUPPLIER_SITE, INVOICE_DATE, PO_NUMBER.
• Status and holds: PAYMENT_STATUS (lookup meaning), PWP_HOLD_FLAG, DLV_HOLD_FLAG, HOLD_FLAG, HOLD_REASON.
• Project and currency context: PROJECT_ID, PROJFUNC_CURRENCY_CODE, PROJ_CURRENCY_CODE, INVOICE_CURRENCY.
• Invoice currency amounts: INVOICE_AMOUNT, AMOUNT_PAID, AMOUNT_UNPAID, RETAINED_AMOUNT, DISCOUNT_AMOUNT.
• Project currency amounts: PROJ_INVOICE_AMOUNT, PROJ_INV_PAID_AMOUNT, PROJ_INV_UNPAID_AMOUNT, PROJ_RETAINED_AMOUNT, PROJ_DISCOUNT_AMOUNT.
• Project functional currency amounts: PROJFUNC_INVOICE_AMOUNT, PROJFUNC_INV_PAID_AMOUNT, PROJFUNC_INV_UNPAID_AMOUNT, PROJFUNC_RETAINED_AMOUNT, PROJFUNC_DISCOUNT_AMOUNT.

Because the user searched for amount_paid, it is worth noting that AMOUNT_PAID reflects the sum of paid amounts in the invoice currency, while PROJ_INV_PAID_AMOUNT and PROJFUNC_INV_PAID_AMOUNT express the same measure in project currency and project functional currency, respectively.

Common Use Cases and Queries

Typical use cases include tracking supplier invoice payment status per project, reconciling paid versus unpaid amounts, monitoring retainage and discounts, and identifying invoices on PWP or delivery hold. A representative query filtering on AMOUNT_PAID is:

• SELECT INVOICE_NUM, SUPPLIER_NAME, PROJECT_ID, INVOICE_CURRENCY, AMOUNT_PAID, AMOUNT_UNPAID, PAYMENT_STATUS FROM APPS.PA_PWP_SUPPLIER_INVOICE_V WHERE AMOUNT_PAID > 0 ORDER BY INVOICE_DATE DESC;

Additional scenarios include aggregating paid amounts by supplier or project, and joining to PA_PROJECTS_ALL through PROJECT_ID for project name and number reporting. Because the view pre-aggregates detail lines, consumers should treat each returned row as an invoice-project-currency summary rather than a transaction-level record.