Results for “pa_proj_retn_exp_v”

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

Overview

PA_PROJ_RETN_EXP_V is a Projects (PA) module view owned by the APPS schema and defined in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes retention expenditure rules configured against project retention arrangements, allowing retention percentages, amounts, thresholds, and accumulated retained balances to be reported without recourse to the underlying normalized retention tables. The view is a reporting and integration convenience object: it resolves retention rule definitions from PA_PROJ_RETN_RULES and joins them to retention transactions in PA_PROJECT_RETENTIONS, while enriching the result set with project, task, agreement, and customer context. Its definition restricts output to rules whose retention level code is EXPENDITURE_TYPE, EXPENDITURE_CATEGORY, or NON_LABOR, which aligns directly with searches for the NON_LABOR retention dimension. The view applies Oracle Projects security through the PA_SECURITY package, so a querying user sees project numbers, names, and task identifiers only where the security function ALLOW_QUERY returns 'Y'; otherwise those descriptive columns are substituted with a truncated secured-data lookup value.

Underlying Base Objects

The view is constructed over the following documented objects: PA_PROJECT_RETENTIONS (aliased RT, the driving retention transaction table), PA_PROJ_RETN_RULES (aliased RL, supplying rule-level definitions such as percentage, amount, threshold, expenditure category, expenditure type, and non-labor resource), PA_PROJECTS_ALL and PA_TASKS, PA_AGREEMENTS_ALL, HZ_CUST_ACCOUNTS and HZ_PARTIES for customer identity, PA_LOOKUPS for the secured-data substitution value, and the PA_SECURITY package for row-level authorization. Joins to PA_TASKS are outer joins on both PROJECT_ID and TASK_ID, reflecting that retention rules may exist at the project level without a specific task. The rule join is an inner join on RETENTION_RULE_ID, and the customer and agreement joins ensure that agreement number, customer account number, and party name are returned where the retention record is tied to an agreement. Because the view joins HZ_CUST_ACCOUNTS and HZ_PARTIES, it inherits TCA naming semantics rather than the legacy customer tables.

Key Columns

• ROW_ID, RETENTION_RULE_ID — the row identity of the retention transaction and the governing retention rule identifier.
• RETENTION_LEVEL_CODE — indicates whether the rule applies at EXPENDITURE_TYPE, EXPENDITURE_CATEGORY, or NON_LABOR level; this is the filter column most relevant to non-labor retention analysis.
• PROJECT_ID, PROJECT_NUMBER, PROJECT_NAME — project identifiers, with descriptive columns subject to PA_SECURITY.ALLOW_QUERY.
• TASK_ID, TASK_NUMBER, TASK_NAME — optional task context, populated through outer joins to PA_TASKS.
• AGREEMENT_ID, AGREEMENT_NUM, CUSTOMER_ID, ACCOUNT_NUMBER, PARTY_NAME — agreement and customer attribution for the retention arrangement.
• EFFECTIVE_START_DATE, EFFECTIVE_END_DATE — the rule validity window, useful for point-in-time reporting.
• RETENTION_PERCENTAGE, RETENTION_AMOUNT, THRESHOLD_AMOUNT — the quantitative rule terms that determine how much is withheld.
• EXPENDITURE_CATEGORY, EXPENDITURE_TYPE, NON_LABOR_RESOURCE — the expenditure qualifiers targeted by the rule; NON_LABOR_RESOURCE identifies the specific non-labor resource for NON_LABOR-level rules.
• Currency and retained totals — INVPROC, FUNDING, PROJECT, and PROJFUNC currency codes with their corresponding TOTAL_RETAINED columns, giving accumulated retention in each currency context.

Common Use Cases and Queries

Typical reporting scenarios include reviewing all retention rules that target non-labor expenditure, reconciling retained amounts by currency, and validating rule setup before running retention processing. The following example returns non-labor retention rules for a given project:

SELECT retention_rule_id, retention_level_code, project_number, project_name,
  task_number, expenditure_category, expenditure_type, non_labor_resource,
  retention_percentage, retention_amount, threshold_amount,
  project_currency_code, project_total_retained
FROM apps.pa_proj_retn_exp_v
WHERE project_id = :p_project_id
  AND retention_level_code = 'NON_LABOR';

A second common pattern summarizes retained balances by agreement and funding currency:

SELECT agreement_num, customer_id, funding_currency_code,
  SUM(funding_total_retained) retained_total
FROM apps.pa_proj_retn_exp_v
GROUP BY agreement_num, customer_id, funding_currency_code;

Because descriptive project columns depend on PA_SECURITY.ALLOW_QUERY, queries intended for integration or extract purposes should select PROJECT_ID, TASK_ID, RETENTION_RULE_ID, and currency total columns, treating PROJECT_NUMBER, PROJECT_NAME, TASK_NUMBER, and TASK_NAME as presentation attributes. The view should be treated as read-only; retention rules and transactions must be maintained through the supported Projects forms and APIs against the base tables.