Search Results invoice_count

Overview

PA_PWP_LINK_STATUS_V is a Projects (PA) module view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its stated purpose is to report details of supplier invoices linked to a draft invoice, a relationship central to the Project Workbench Payment (PWP) process used by federal and commercial contractors who link accounts payable transactions to project draft invoices. The view aggregates the population of supplier invoices associated with each draft invoice, providing both an invoice count and two monetary measures that distinguish the project functional currency amount from the project currency amount. It is classified as VALID in the ETRM registry, meaning the definition compiles cleanly against the documented base objects.

The view is read-only and is typically consumed by reports, concurrent programs, or diagnostic queries rather than by transactional form logic. Because its definition spans both the PWP linking tables and the standard draft invoice expenditure distribution chain, it serves as a consolidated status line that reconciles the linking mechanism with the underlying project cost and revenue distribution data.

Underlying Base Objects

The view is defined over six synonym references: PA_PWP_LINKED_INVOICES, PA_PWP_AP_INV_DTL, PA_DRAFT_INVOICES, PA_DRAFT_INVOICE_ITEMS, PA_CUST_REV_DIST_LINES, and PA_EXPENDITURE_ITEMS. The definition is a UNION of two aggregation branches.

• The first branch joins PA_PWP_LINKED_INVOICES to PA_PWP_AP_INV_DTL on AP_INVOICE_ID and PROJECT_ID, grouping by PROJECT_ID and DRAFT_INVOICE_NUM to produce the distinct invoice count and summed functional and project currency amounts.
• The second branch traverses PA_DRAFT_INVOICES, PA_DRAFT_INVOICE_ITEMS, PA_CUST_REV_DIST_LINES, and PA_EXPENDITURE_ITEMS, joined to an inline aggregate over PA_PWP_AP_INV_DTL. It filters on SYSTEM_LINKAGE_FUNCTION = 'VI' and requires a non-null DOCUMENT_HEADER_ID, matching the expenditure item to the invoice header.

PA_PWP_AP_INV_DTL therefore acts as the common monetary source across both branches, while the draft invoice and expenditure item tables supply the project-side distribution context.

Key Columns

• PROJECT_ID — Primary identifier of the project against which the draft invoice and its linked supplier invoices are recorded.
• DRAFT_INVOICE_NUM — The draft invoice number that consumers use to associate a status line with a specific draft.
• INVOICE_COUNT — Count of distinct AP invoice IDs (first branch) or distinct document header IDs (second branch) linked to the draft invoice.
• PFC_AMOUNT — Sum of PROJFUNC_INVOICE_AMOUNT, the project functional currency amount.
• PC_AMOUNT — Sum of PROJ_INVOICE_AMOUNT, the project currency amount.

Comparing PFC_AMOUNT and PC_AMOUNT exposes currency conversion differences when the project functional and project currencies are not identical.

Common Use Cases and Queries

Typical scenarios include verifying that all supplier invoices expected against a draft invoice are actually linked, reconciling PWP link amounts to the project cost distribution, and diagnosing discrepancies where the linkage exists but the expenditure item distribution chain is incomplete.

• Listing all draft invoices with their link counts and amounts for a project.
• Identifying drafts where the invoice count differs from the expected number of linked AP invoices.
• Comparing PFC_AMOUNT and PC_AMOUNT to validate currency conversion.

A representative query is:

SELECT project_id,
       draft_invoice_num,
       invoice_count,
       pfc_amount,
       pc_amount
FROM   apps.pa_pwp_link_status_v
WHERE   project_id = :project_id
ORDER BY draft_invoice_num;

Because the view aggregates on the fly, restricting by PROJECT_ID or DRAFT_INVOICE_NUM is advisable on high-volume datasets.