Results for “funding_currency_code”

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

Overview

PA_PROJECT_RETENTIONS is a Projects (PA) module table that stores retention summary balances for project contracts and agreements. Retention represents amounts withheld by a customer or funding party from invoice or payment amounts, typically until satisfactory completion of a project or a defined milestone. Rather than retaining detailed transaction-level retention lines, PA_PROJECT_RETENTIONS holds aggregated balances organized by project, task, agreement, and retention rule. It is the reporting and reconciliation layer that supports retention aging, release, and claim processing within Oracle Projects.

The table resides in the PA schema and is classified as VALID in the EBS 12.1.1 / 12.2.2 data dictionary. Its documented structure includes 22 columns and two unique indexes. Based on the foreign key topology, a Data Vault heuristic classification would treat PA_PROJECT_RETENTIONS as a link entity: it resolves the many-to-many relationships between projects, tasks, agreements, retention rules, and currencies, and carries the measurable retained balances as link-level descriptive attributes. It should not be modeled as a pure hub or a satellite, because its primary role is to associate these independent business entities while capturing cumulative monetary amounts.

Key Information Stored

The surrogate primary key is PROJECT_RETENTION_ID, enforced by the unique index PA_PROJECT_RETENTIONS_U1. A second unique index, PA_PROJECT_RETENTIONS_U2, defines the business-key candidate: PROJECT_ID, TASK_ID, AGREEMENT_ID, and RETENTION_RULE_ID. This combination guarantees one retention summary row per project, task, agreement, and retention rule, which is the grain at which balances are maintained.

• PROJECT_ID, TASK_ID, AGREEMENT_ID, RETENTION_RULE_ID — identify the project, task, agreement, and retention rule to which the retained balances apply.
• TOTAL_RETAINED — the primary retained amount in the invoice processing currency.
• PROJFUNC_CURRENCY_CODE, PROJFUNC_TOTAL_RETAINED — the project functional currency and its corresponding retained total.
• PROJECT_CURRENCY_CODE, PROJECT_TOTAL_RETAINED — the project currency and retained total.
• FUNDING_CURRENCY_CODE, FUNDING_TOTAL_RETAINED — the funding currency and retained total.
• INVPROC_CURRENCY_CODE — the invoice processing currency associated with the retained amounts.
• PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID — concurrent program context columns recording which program and request last populated or updated the row.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS audit trail columns.

Common Use Cases and Queries

The table is most commonly queried to report outstanding retention balances by project or agreement, to reconcile multi-currency retention totals, and to feed retention release or claim processes. A typical reporting query joins to PA_PROJECTS_ALL, PA_TASKS, and PA_AGREEMENTS_ALL to add descriptive context:

• Retention balance by project: SELECT project_id, SUM(projfunc_total_retained) FROM pa_project_retentions GROUP BY project_id;
• Detailed retention listing: SELECT r.project_id, r.task_id, r.agreement_id, r.retention_rule_id, r.total_retained, r.projfunc_total_retained FROM pa_project_retentions r WHERE r.project_id = :p_project_id;
• Multi-currency reconciliation comparing TOTAL_RETAINED, PROJFUNC_TOTAL_RETAINED, PROJECT_TOTAL_RETAINED, and FUNDING_TOTAL_RETAINED for a single retention record.
• Audit and lineage queries using REQUEST_ID against concurrent program run history to trace when a retention summary was last recalculated.

Related Objects

PA_PROJECT_RETENTIONS is tightly coupled to the following parent and reference objects through documented foreign keys:

• PA_PROJECTS_ALL — joined on PROJECT_ID; supplies project attributes.
• PA_TASKS — joined on TASK_ID; supplies task context.
• PA_AGREEMENTS_ALL — joined on AGREEMENT_ID; supplies agreement and funding terms.
• FND_CURRENCIES — referenced four times via PROJFUNC_CURRENCY_CODE, PROJECT_CURRENCY_CODE, FUNDING_CURRENCY_CODE, and INVPROC_CURRENCY_CODE for currency validation and descriptions.
• PA_RETENTION_RULES (referenced via RETENTION_RULE_ID) — the rule governing how retention is calculated and released.

These relationships confirm that PA_PROJECT_RETENTIONS serves as the consolidated retention junction for projects, tasks, agreements, rules, and currencies within Oracle Projects.