Search Results invoice_count




Overview
Explore in AI Chat

Overview

APPS.PA_PWP_LINK_STATUS_V is an Oracle E-Business Suite reporting view that consolidates the status of invoices linked to Project Web Portal (PWP) draft invoices. It is defined over the intersection of Oracle Payables and Oracle Projects data, joining AP invoice headers and distributions to the PA_PWP_LINKED_INVOICES linkage table on one side, and joining Project Billing draft invoice structures to expenditure items on the other. The view presents, per project and draft invoice number, a count of the distinct invoices contributing to the PWP linkage, together with project-functional-currency and project-currency amount totals.

The view is commonly consumed by Oracle Projects billing and PWP invoice-linking reports, as well as by custom reporting and reconciliation queries that need to confirm that supplier invoices have been correctly linked to project draft invoices. Because the two halves of the view are combined with UNION, each project / draft invoice combination is reported once from the Payables-linked population and once from the Projects expenditure population, which is useful where both linkage paths must be validated.

Underlying Base Objects

Per the documented ETRM 12.2.2 metadata, the view references six base objects, all exposed through APPS synonyms:

  • PA_PWP_LINKED_INVOICES — stores the association between PWP records, projects, draft invoice numbers, and AP invoice identifiers.
  • AP_INVOICES — Payables invoice header, supplying invoice currency and invoice identifier.
  • AP_INVOICE_DISTRIBUTIONS — Payables distribution lines, supplying project, task, expenditure item date, and distribution amount.
  • PA_DRAFT_INVOICES — Projects draft invoice header, keyed by project and draft invoice number.
  • PA_DRAFT_INVOICE_ITEMS — Draft invoice lines, providing the line number used to join to revenue distribution lines.
  • PA_CUST_REV_DIST_LINES — Customer revenue distribution lines, carrying the expenditure item identifier.
  • PA_EXPENDITURE_ITEMS — Project expenditure items, supplying raw cost and project raw cost and the document header identifier.

The view also calls the packaged function PAAP_PWP_PKG.GET_PROJ_CURR_AMT to convert AP distribution amounts into project functional and project currency, so PAAP_PWP_PKG is an implicit dependency of the view's execution path.

Key Columns

  • PROJECT_ID — Project identifier shared by both UNION branches; the primary grouping key.
  • DRAFT_INVOICE_NUM — Draft invoice number, the secondary grouping key used to associate linked AP invoices with a project draft invoice.
  • INVOICE_COUNT — The count of distinct invoices for the project/draft invoice combination. In the first branch this is COUNT(DISTINCT PWP.AP_INVOICE_ID); in the second it is COUNT(DISTINCT EI.DOCUMENT_HEADER_ID). This is the column most often queried when a user searches for "invoice_count".
  • PFC_AMOUNT — Project functional currency amount. In the Payables branch this is the sum of converted AP distribution amounts; in the Projects branch it is the sum of EI.RAW_COST.
  • PC_AMOUNT — Project currency amount. In the Payables branch it is the converted distribution amount in project currency; in the Projects branch it is the sum of EI.PROJECT_RAW_COST.

Common Use Cases and Queries

The view is typically used to verify how many supplier invoices have been linked to each project draft invoice and to reconcile linked invoice amounts against project expenditure costs.

Basic invoice count per project and draft invoice:

  • SELECT project_id, draft_invoice_num, invoice_count FROM apps.pa_pwp_link_status_v ORDER BY project_id, draft_invoice_num;

Aggregate invoice counts per project:

  • SELECT project_id, SUM(invoice_count) total_invoices FROM apps.pa_pwp_link_status_v GROUP BY project_id;

Amount reconciliation across both UNION branches for a given draft invoice:

  • SELECT draft_invoice_num, pfc_amount, pc_amount FROM apps.pa_pwp_link_status_v WHERE project_id = :project_id AND draft_invoice_num = :draft_invoice_num;

Because INVOICE_COUNT is derived via COUNT(DISTINCT ...) and PFC_AMOUNT/PC_AMOUNT via SUM over converted amounts, callers should filter by PROJECT_ID and DRAFT_INVOICE_NUM where possible to constrain the underlying AP and PA scans, and should note that a single project/draft invoice may return more than one row where both UNION branches produce records.