Search Results pa_alloc_resources_n1

Overview

PA.PA_ALLOC_RESOURCES is a transactional table in the Oracle E-Business Suite Projects (PA) schema that stores the resources and allocation percentages associated with each allocation rule. Every row in this table defines one participating resource for a given rule, together with the percentage of that resource's amount that is fed into the allocation calculation. The table captures both basis resources, which determine how much cost or revenue is available to be allocated, and source resources, which define what is being distributed. Because allocation rules in Oracle Projects drive the redistribution of indirect costs, revenue, and budget amounts across projects, this table sits at the heart of the allocation engine and is consulted by the allocation process each time a rule is executed.

From a modeling standpoint the table behaves like a satellite-leaning structure. Its primary key, PA_ALLOC_RESOURCES_PK (RULE_ID, MEMBER_TYPE, RESOURCE_LIST_MEMBER_ID), is composed entirely of foreign or business attributes rather than a generated surrogate, and it hangs off the allocation rule header in PA_ALLOC_RULES_ALL via RULE_ID. A Data Vault practitioner would therefore treat PA_ALLOC_RULES_ALL as the hub, PA_ALLOC_RESOURCES as a satellite that records the descriptive percentages and flags at a point in time, and the resource list member identifier as a link reference into the resource list definition. This classification is a heuristic mined from the foreign-key topology and should be regarded as a modeling suggestion rather than a documented design fact.

Key Information Stored

• RULE_ID — The allocation rule identification number. Because it is the leading column of both the primary key and every unique index, it is the principal join back to PA_ALLOC_RULES_ALL.
• MEMBER_TYPE — A single-character flag indicating whether the row represents a source resource (‘S’) or a basis resource (‘B’). This distinction governs how the allocation engine interprets the associated percentage.
• RESOURCE_LIST_MEMBER_ID — The identifier of the resource list member, linking the allocation rule to the specific resource list entry that supplies the cost or revenue pool.
• RESOURCE_PERCENTAGE — The percentage of the resource amount to be used for the allocation. This is the principal numeric driver in the table and is commonly used in rate and pool analysis.
• EXCLUDE_FLAG — Indicates whether returned projects should be excluded from the allocation set, allowing rule designers to filter out projects that would otherwise be picked up.
• CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard Who columns that provide audit and change-tracking information for each row.
• TARGET_EXPND_TYPE and OFFSET_EXPND_TYPE — Reserved columns documented in the ETRM schema but marked as unused; they should not be relied upon for functional logic.

The business key is captured by the unique index PA_ALLOC_RESOURCES_U1 on (RULE_ID, MEMBER_TYPE, RESOURCE_LIST_MEMBER_ID), which enforces that only one percentage can exist per rule, per member type, and per resource list member. The non-unique index PA_ALLOC_RESOURCES_N1 on (RULE_ID, MEMBER_TYPE, EXCLUDE_FLAG) supports queries that filter by exclusion status within a rule.

Common Use Cases and Queries

Typical reporting and diagnostic scenarios include auditing which resources participate in a rule, verifying that basis percentages total one hundred, identifying rules that exclude returned projects, and tracing the full allocation chain for a project or cost pool. A representative query lists all resources for a rule, ordering basis before source members:

• SELECT RULE_ID, MEMBER_TYPE, RESOURCE_LIST_MEMBER_ID, RESOURCE_PERCENTAGE, EXCLUDE_FLAG FROM PA.PA_ALLOC_RESOURCES WHERE RULE_ID = :rule_id ORDER BY MEMBER_TYPE, RESOURCE_LIST_MEMBER_ID;
• SELECT RULE_ID, SUM(RESOURCE_PERCENTAGE) FROM PA.PA_ALLOC_RESOURCES WHERE MEMBER_TYPE = 'B' GROUP BY RULE_ID HAVING SUM(RESOURCE_PERCENTAGE) <> 100;
• SELECT a.RULE_ID, a.MEMBER_TYPE, a.RESOURCE_PERCENTAGE FROM PA.PA_ALLOC_RESOURCES a WHERE a.EXCLUDE_FLAG = 'Y';

These patterns are frequently embedded in reconciliation reports that validate allocation setup before period close, and in extracts that feed downstream cost modeling or data warehouse processes.

Related Objects

• PA.PA_ALLOC_RULES_ALL — The allocation rule header table; joined on RULE_ID, it supplies the rule name, type, and effective dates that give context to each resource row.
• PA.PA_ALLOC_RULES_B — The base-language rule definition, used when resolving rule names in reports.
• PA.PA_ALLOC_RULES_TL — The translated rule descriptions, joined on RULE_ID for multilingual reporting.
• PA.PA_RESOURCE_LIST_MEMBERS and related resource list tables — Resolve RESOURCE_LIST_MEMBER_ID to the underlying resource and organisation definition.
• PA.PA_ALLOC_RUN_RESOURCES — Captures the resources actually processed during an allocation run, allowing comparison against the setup held here.
• PA.PA_ALLOC_RUN_AMOUNTS — Stores the amounts calculated per run, useful for reconciling the percentage defined here against the value distributed.
• PA.PA_COST_DIST_ALLOCATIONS — Records the distributed cost results produced when these rules execute.

Together these objects form the allocation rule framework, with PA_ALLOC_RESOURCES acting as the detail satellite that defines precisely which resources are used, at what percentage, and under which exclusion conditions.