Search Results total_write_off

Overview

PA_PROJECT_RETENTION_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, belonging to the Projects (PA) product family. As documented in the ETRM metadata for release 12.2.2, the view "stores retention summary balances." Retention in Oracle Projects represents amounts withheld by a customer from an invoice, typically a percentage of billed value, which is released only after contractual conditions are satisfied. This view consolidates retention balances at the project-and-customer level so that receivables and project accountants can analyze outstanding retained amounts without querying the underlying summary table directly. Because it is a view rather than a base table, it is read-only by nature and is intended for enquiry, reporting, and integration extracts rather than transactional maintenance. The join to PA_LOOKUPS converts the stored retention level code into a user-facing meaning, and the connection to PA_CUSTOMERS_V supplies customer name and number, making the view suitable for direct presentation in reports and concurrent programs.

Underlying Base Objects

The view is defined over four documented objects. PA_PROJECT_CUSTOMERS is the project-to-customer assignment table and drives the primary rows, supplying PROJECT_ID, CUSTOMER_ID and RETENTION_LEVEL_CODE. PA_SUMMARY_PROJECT_RETN holds the summarized retention figures and is joined to PA_PROJECT_CUSTOMERS on PROJECT_ID and CUSTOMER_ID using outer joins, meaning projects with no summarized retention activity still appear with null amounts. PA_CUSTOMERS_V is joined on CUSTOMER_ID to return the customer name and number. PA_LOOKUPS is joined with an outer join on LOOKUP_TYPE = 'RETENTION_LEVEL', and the lookup code is derived through a DECODE that substitutes 'NO_RETENTION' when RETENTION_LEVEL_CODE is null, ensuring a descriptive name is always available. The aggregation in the SELECT clause groups by project, customer, retention level, currency and record version.

Key Columns

• PROJECT_ID / CUSTOMER_ID — Composite identifier of the retention relationship.
• CUSTOMER_NAME / CUSTOMER_NUMBER — Descriptive customer attributes sourced from PA_CUSTOMERS_V.
• RETENTION_LEVEL_CODE / RETENTION_LEVEL_NAME — The stored retention level and its lookup meaning, defaulting to NO_RETENTION when null.
• INVPROC_CURRENCY_CODE — The invoice processing currency in which the retention amounts were summarized. This is the column most closely associated with the searched term and is the natural key for multi-currency aggregation.
• TOTAL_RETAINED — Sum of amounts withheld.
• TOTAL_BILLED — Sum of billed value against which retention was calculated.
• TOTAL_WRITE_OFF — Sum of retention amounts written off, named TOTAL_WRITE_OFF in the column list though referenced as TOTAL_WRITEOFF in the view text.
• RECORD_VERSION_NUMBER — Optimistic locking version attribute carried from PA_PROJECT_CUSTOMERS.

Common Use Cases and Queries

Typical scenarios include retention aging analysis, reconciliation of retained balances to Receivables, and extract feeds to financial data warehouses. Because INVPROC_CURRENCY_CODE is part of the GROUP BY, queries should filter or group on it to avoid mixing currencies.

Project-level retention summary:

• SELECT project_id, customer_name, retention_level_name, invproc_currency_code, total_retained, total_billed FROM pa_project_retention_v WHERE project_id = :p_project_id;

Retention by customer and currency:

• SELECT customer_number, customer_name, invproc_currency_code, SUM(total_retained) retained FROM pa_project_retention_v GROUP BY customer_number, customer_name, invproc_currency_code;

Projects with retention but no billing activity:

• SELECT project_id, customer_name, total_retained FROM pa_project_retention_v WHERE NVL(total_billed,0) = 0;