Results for “projfunc_total_retained”

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

Overview

PA_SUMMARY_PROJECT_RETNS_V is an APPS-owned reporting view in the Oracle Project Accounting (PA) module. It exposes retention summary amounts held against projects, tasks, and customer agreements, combining the stored balances in the PA_SUMMARY_PROJECT_RETN table with descriptive attributes drawn from projects, tasks, agreements, customers, and lookups. The view is read-only in practice and is used primarily for project billing retention reporting, reconciliation, and integration extracts.

A distinctive characteristic of this view is that it enforces Oracle EBS project security at the SQL level. Each project, task, and customer descriptive column is wrapped in a call to PA_SECURITY.ALLOW_QUERY. Where the querying user is permitted to see the project, the real segment value, project name, task number, and task name are returned; where access is denied, the view substitutes a masked value derived from the TRANSLATION lookup code 'SECURED_DATA'. This means consumers of the view automatically receive row-level security without additional predicates, but they may see truncated or replaced descriptive text such as 'Secured Data' in place of actual values.

Underlying Base Objects

The view is defined over the following documented objects:

• PA_SUMMARY_PROJECT_RETN — the driving table (aliased F), holding the retention summary rows and all numeric measures.
• PA_PROJECTS_ALL (P) — source of project segment and name.
• PA_TASKS (T) — source of task number and task name; joined with outer-join syntax (+), so retention records at project level without a task still return.
• PA_AGREEMENTS_ALL (A) — source of the agreement number, joined on AGREEMENT_ID.
• HZ_CUST_ACCOUNTS and HZ_PARTIES — supply the customer account number and party name for the agreement customer.
• PA_PROJECT_CUSTOMERS (PC) — links project and customer, and supplies RETENTION_LEVEL_CODE.
• PA_LOOKUPS — used twice: LK1 for the RETENTION_LEVEL lookup (outer join), and LK for the TRANSLATION/SECURED_DATA masking lookup.
• PA_SECURITY — the PL/SQL package invoked in the SELECT list to apply project access rules.

Key Columns

• ROW_ID — row identifier from the underlying summary record.
• PROJECT_ID, PROJECT_NUMBER, PROJECT_NAME — project identifiers, subject to security masking.
• TASK_ID, TASK_NUMBER, TASK_NAME — task context; may be null for project-level retention.
• AGREEMENT_ID, AGREEMENT_NUM, CUSTOMER_ID, ACCOUNT_NUMBER, PARTY_NAME — agreement and customer detail.
• RETENTION_LEVEL_NAME — decoded retention level (defaulting to 'NO_RETENTION' when the project customer record carries no retention level code).
• INVPROC_CURRENCY_CODE, TOTAL_RETAINED, TOTAL_BILLED, TOTAL_WRITEOFF — invoice-processing currency retention balances.
• FUNDING_* , PROJECT_* , PROJFUNC_* — the same three measures (RETAINED, BILLED, WRITEOFF) expressed in funding, project, and project functional currency respectively. The PROJFUNC_TOTAL_BILLED column is the project functional currency billed amount and is frequently referenced directly in user queries.

Common Use Cases and Queries

Typical scenarios include retention balance reporting by project and customer, reconciliation of retained versus billed amounts across currencies, and extracts feeding account analysis or customer statements. Note that because security masking alters descriptive columns rather than suppressing rows, counts may not match operational expectations unless allowances are considered.

A representative query retrieving functional-currency billed and retained amounts for a project:

SELECT project_id,
       project_number,
       project_name,
       task_number,
       agreement_num,
       party_name,
       projfunc_currency_code,
       projfunc_total_billed,
       projfunc_total_retained,
       projfunc_total_writeoff
  FROM apps.pa_summary_project_retns_v
 WHERE project_id = :p_project_id
 ORDER BY task_number, agreement_num;

A second pattern aggregates billed retention by retention level for financial review:

SELECT retention_level_name,
       SUM(projfunc_total_billed)   billed,
       SUM(projfunc_total_retained) retained
  FROM apps.pa_summary_project_retns_v
 GROUP BY retention_level_name;

Because the view references several synonym-base objects and the PA_SECURITY package, queries should be issued from the APPS schema or from a schema with the appropriate synonyms and grants in place.