Search Results pay_consolidation_sets_pkg




Overview
Explore in AI Chat

Overview

PAY_CONSOLIDATION_SETS_PKG is a server-side PL/SQL package owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Oracle Payroll (PAY) application. It encapsulates the core validation and key-generation logic that supports the definition, maintenance, and administration of payroll consolidation sets. A consolidation set is a grouping mechanism that allows multiple payrolls to be processed together — for example, when an organization runs several payrolls that must be consolidated for costing, reporting, or payment purposes. The package is declared with AUTHID CURRENT_USER, meaning that its procedures execute with the privileges of the invoking user rather than the definer, and therefore rely on the standard APPS synonym and grant model for access to the underlying base tables.

Rather than exposing a broad public API, the package provides a focused set of utility procedures that enforce data integrity rules and supply surrogate key values used by the consolidation set definition forms. It is classified as OTHER in the ETRM metadata, indicating that it is not a formal public API but an internal supporting package for the Payroll consolidation set maintenance screens.

Key Procedures and Functions

The package exposes three documented procedures, each serving a distinct validation or sequencing purpose:

  • GET_NEXT_SEQUENCE — Generates and returns the next available sequence value for a consolidation set identifier, accepting the consolidation set ID as an IN OUT parameter. This is the standard Oracle EBS surrogate-key generation pattern: the caller passes in the current value and receives the incremented value to be used when inserting a new consolidation set row.
  • CHECK_UNIQUE_NAME — Validates that a proposed consolidation set name is unique within the business group context. It accepts the consolidation set name, business group ID, and a ROWID (used to exclude the current record during an update, so that a row is not flagged as a duplicate of itself). This enforces the business rule that consolidation set names must not collide within a single business group.
  • CHECK_DELETE — Determines whether a given consolidation set may be safely deleted, based on the business group ID and consolidation set ID. It performs the referential checks needed to prevent deletion of a set that is still referenced by dependent payroll processing data.

Tables Accessed

The package reads and writes the following tables through APPS synonyms:

  • PAY_CONSOLIDATION_SETS — The primary transactional table holding consolidation set definitions. Used for uniqueness validation, deletion checks, and sequence assignment.
  • PAY_CONSOLIDATION_SETS_S — The corresponding translated/secure (TL-style) table that stores the language-dependent or secured attributes of consolidation sets. The CHECK_DELETE and CHECK_UNIQUE_NAME logic consults this table to ensure consistency across both definition and translation layers.
  • PAY_PAYROLL_ACTIONS — The payroll action table, referenced by CHECK_DELETE to detect whether consolidation sets are still in use by payroll run or action records. Presence of dependent actions blocks deletion, preserving referential integrity between payroll processing and consolidation set configuration.

Usage Notes

PAY_CONSOLIDATION_SETS_PKG is typically invoked indirectly through the Oracle Payroll consolidation set maintenance forms rather than by end users or external integrations. The form triggers call GET_NEXT_SEQUENCE when a new consolidation set is created, CHECK_UNIQUE_NAME on save or field validation to detect duplicate names, and CHECK_DELETE before committing a delete operation. The package is not referenced by any other documented packages, confirming its role as a leaf-level support utility rather than a shared API.

For custom development, the package may also be called from custom PL/SQL when programmatically maintaining consolidation sets, but this should be done cautiously: the ETRM classification of OTHER signals that Oracle does not guarantee the stability of these signatures across patch levels. The package is present in both Oracle EBS 12.1.1 and 12.2.2, with the header commentary reflecting its long-standing origin in the 11i code line. Developers extending consolidation set functionality should prefer the supported payroll APIs where available and treat this package as an internal implementation detail.