Results for “pay_consolidation_sets”

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

Overview

PAY_CONSOLIDATION_SETS is a Payroll (PAY) module table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. As its description states, it stores "consolidation set of results of payroll processing." A consolidation set is a payroll grouping mechanism that allows multiple payroll runs to be processed and reported together, so that results from different pay periods or payroll definitions can be combined into a single consolidated view for reporting, costing, or payment purposes. Each row in this table represents one named consolidation set belonging to a specific business group, making it a foundational configuration object referenced by both payroll definitions and payroll run actions.

From a Data Vault modeling perspective, the mined foreign-key structure classifies PAY_CONSOLIDATION_SETS as hub-leaning. The table centers on a single, stable business concept — the consolidation set identity — with a surrogate primary key and descriptive attributes attached directly to it, which is characteristic of a hub with associated descriptive content.

Key Information Stored

The table is documented with nine physical columns, of which the most significant are:

• CONSOLIDATION_SET_ID — the surrogate primary key (PAY_CONSOLIDATION_SETS_PK) that uniquely identifies each consolidation set. This is the value referenced by dependent tables and is the appropriate join key in queries.
• BUSINESS_GROUP_ID — the foreign key to HR_ALL_ORGANIZATION_UNITS, scoping the consolidation set to a specific business group (enterprise). This enforces multi-tenant separation of payroll configuration data.
• CONSOLIDATION_SET_NAME — the user-facing, descriptive name of the consolidation set as defined during payroll configuration; this is the natural business identifier users recognize.
• COMMENTS — free-text annotation explaining the purpose or scope of the consolidation set.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — the standard Oracle EBS "WHO" audit columns tracking creation and last modification of each row.

Note that the unique index set includes PAY_CONSOLIDATION_SETS_PK on CONSOLIDATION_SET_ID, confirming the surrogate key as the definitive unique identifier. CONSOLIDATION_SET_NAME functions as the practical business-key candidate, though the documented metadata lists it as an attribute rather than a unique index column.

Common Use Cases and Queries

Consolidation sets are primarily used to group payroll results for consolidated reporting and for assignment to payroll definitions and payroll run actions. Typical scenarios include listing all consolidation sets for a business group, joining consolidation sets to the payrolls that reference them, and tracing which payroll actions were executed under a given consolidation set.

Representative query patterns grounded in the documented columns and keys:

• Listing consolidation sets by business group: SELECT consolidation_set_id, consolidation_set_name FROM pay_consolidation_sets WHERE business_group_id = :p_bg_id ORDER BY consolidation_set_name;
• Joining payrolls to their consolidation set: SELECT p.payroll_name, c.consolidation_set_name FROM pay_all_payrolls_f p JOIN pay_consolidation_sets c ON p.consolidation_set_id = c.consolidation_set_id WHERE p.effective_end_date IS NULL;
• Identifying payroll run actions associated with a consolidation set: SELECT a.payroll_action_id, c.consolidation_set_name FROM pay_payroll_actions a JOIN pay_consolidation_sets c ON a.consolidation_set_id = c.consolidation_set_id WHERE c.consolidation_set_id = :id;
• Audit and configuration review using the WHO columns, for example filtering sets modified after a given date via LAST_UPDATE_DATE.

These queries support consolidation reporting, payroll configuration validation, and troubleshooting of payroll runs grouped by consolidation set.

Related Objects

PAY_CONSOLIDATION_SETS participates in documented foreign-key relationships that define its role in the payroll data model:

• HR_ALL_ORGANIZATION_UNITS — referenced via PAY_CONSOLIDATION_SETS.BUSINESS_GROUP_ID, anchoring each consolidation set to its business group.
• PAY_ALL_PAYROLLS_F — references this table through CONSOLIDATION_SET_ID; payroll definitions can be associated with a consolidation set.
• PAY_PAYROLL_ACTIONS — references this table through CONSOLIDATION_SET_ID; each payroll run action may be tied to a consolidation set.
• PAY_CONSOLIDATION_SETS_PK — the primary key index enforcing uniqueness on CONSOLIDATION_SET_ID.

Together, these objects express the consuming relationships that make PAY_CONSOLIDATION_SETS a hub-leaning reference table within the Oracle Payroll configuration and processing model.