Results for “credit_memo_reason_code”

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

Overview

PA_PROJ_INVOICES_VIEW is an APPS-owned database view that consolidates project billing and invoice information from Oracle Projects (PA). Its stated purpose within the E-Business Suite data model is to present a unified picture of all project invoices — covering draft, approved, released, and transferred states — enriched with customer, agreement, currency, and receivables context. The view is a reporting and integration artifact rather than a transactional object; it holds no data of its own and is defined entirely as a query over the underlying Projects billing tables and adjacent receivables and trading-community tables.

In both ETRM 12.1.1 and 12.2.2, the view carries a status of VALID in the APPS schema. Because it spans draft invoice headers, draft invoice items, agreements, customer accounts, and receivables payment terms, it is typically consumed by custom reports, extracts, and interfaces that need invoice-level aggregates without navigating the full normalized structure of PA_DRAFT_INVOICES and PA_DRAFT_INVOICE_ITEMS directly.

Underlying Base Objects

The documented base objects for the view are:

• PA_DRAFT_INVOICES (SYNONYM) — the invoice header source, supplying project ID, draft invoice number, RA invoice number, bill-through date, invoice date, approved/released dates, GL date, transfer status, and billing flags.
• PA_DRAFT_INVOICE_ITEMS (SYNONYM) — the line-level source, aggregated to produce amount and currency totals, including retention amounts and project-functional bill amounts.
• PA_AGREEMENTS_ALL (SYNONYM) — supplies agreement number, agreement type, customer order number, and line of account.
• HZ_CUST_ACCOUNTS_ALL and HZ_PARTIES (SYNONYMS) — provide bill-to and ship-to customer account numbers and party names, truncated to 50 bytes.
• GL_PERIOD_STATUSES (SYNONYM) — resolves the GL date to a period name.
• PA_LOOKUPS (VIEW) — decodes the transfer status code into a meaningful description.
• PA_IMPLEMENTATIONS_ALL (SYNONYM) — used as a driving implementation-context table.
• RA_TERMS (SYNONYM) — supplies payment term name.
• PA_PERIOD_PROCESS_PKG (PACKAGE) — referenced within the view definition for period-related logic.

The definition joins these objects and applies SUM aggregations over invoice items, so the result set is effectively invoice-level (one logical row per invoice header, with item amounts rolled up).

Key Columns

• PROJECT_ID — identifies the project associated with the invoice.
• DRAFT_INVOICE_NUM / RA_INVOICE_NUMBER — internal Projects draft identifier and the Receivables invoice number.
• BILL_THROUGH_DATE, INVOICE_DATE, APPROVED_DATE, RELEASED_DATE, GL_DATE — the key billing lifecycle dates; the GL date also resolves to a period name via GL_PERIOD_STATUSES.
• TRANSFER_STATUS_CODE / MEANING — the transfer state and its decoded description from PA_LOOKUPS.
• Amount columns — aggregated totals including SUM(ITEM.AMOUNT), SUM(ITEM.INV_AMOUNT), retention amounts (a negative retention line), and the net amount excluding retention. PROJFUNC_BILL_AMOUNT provides the project-functional billed amount.
• CUST_ACCOUNT_ID / ACCOUNT_NUMBER / PARTY_NAME — bill-to and ship-to customer identification.
• AGREEMENT_ID / AGREEMENT_NUM / AGREEMENT_TYPE — the governing project agreement.
• INV_CURRENCY_CODE — invoice currency.
• DRAFT_INVOICE_NUM_CREDITED, CREDIT_MEMO_REASON_CODE and the derived credit/cancel/write-off indicator — identify credit memo relationships.
• RETENTION_INVOICE_FLAG, BILL_TO_CONTACT_ID, SHIP_TO_CONTACT_ID, PAYMENT_SET_ID — additional billing attributes.

Common Use Cases and Queries

The view is commonly used for invoice status reporting, unbilled or transferred invoice reconciliation, customer-level billing summaries, and aging-style analysis based on the derived elapsed-days expression for pending transfers.

SELECT project_id,
       draft_invoice_num,
       ra_invoice_number,
       invoice_date,
       approved_date,
       released_date,
       transfer_status_code,
       inv_currency_code,
       amount
FROM   apps.pa_proj_invoices_view
WHERE  project_id = :p_project_id
ORDER BY invoice_date DESC;

For customer-level totals:

SELECT account_number,
       party_name,
       SUM(amount) total_billed
FROM   apps.pa_proj_invoices_view
WHERE  gl_date BETWEEN :p_from AND :p_to
GROUP BY account_number, party_name;

Because aggregation is performed within the view, consumers should be cautious when joining additional line-level tables, as row multiplication may distort totals. Access is typically restricted to the APPS schema or granted via a synonym, and queries should filter on indexed columns such as PROJECT_ID to avoid full scans of the underlying draft invoice tables.