Search Results gl_cons_set_assignments




Overview
Explore in AI Chat

Overview

GL_CONS_SET_ASSIGNMENTS is a General Ledger (GL) table in the Oracle E-Business Suite that stores the assignments of consolidations to consolidation sets. Within Oracle EBS 12.1.1 and 12.2.2, consolidation sets provide a mechanism for grouping multiple consolidation definitions so that a parent entity can consolidate balances from several subsidiary sources in a single, repeatable run. This table is the association bridge that records which individual consolidations belong to a given consolidation set, and by extension, which child consolidation sets are nested within a parent set.

The table resides in the GL schema and is identified as VALID in the ETRM repository. Its documented physical schema in release 12.2.2 contains 24 columns. Because the table's purpose is to resolve a many-to-many association between consolidation sets and consolidations, the heuristic Data Vault classification mined from its foreign key structure is a link table. This classification should be treated as a modeling suggestion: GL_CONS_SET_ASSIGNMENTS functions as a transactional association entity whose grain is one row per consolidation-to-set assignment.

Key Information Stored

The primary key of the table, implemented through the constraint GL_CONS_SET_ASSIGNMENTS_PK, is the composite of CONSOLIDATION_SET_ID and CONSOLIDATION_ID. This composite key establishes the fundamental uniqueness of each assignment and represents the surrogate key for the link relationship. A unique index, GL_CONS_SET_ASSIGNMENTS_U1, is defined on the same pair (CONSOLIDATION_SET_ID, CONSOLIDATION_ID), confirming this combination as the business-key candidate that enforces one assignment row per set-and-consolidation pairing.

  • CONSOLIDATION_SET_ID — Foreign key to GL_CONSOLIDATION_SETS, identifying the consolidation set to which the assignment belongs.
  • CONSOLIDATION_ID — Foreign key to GL_CONSOLIDATION, identifying the individual consolidation definition being assigned to the set.
  • CHILD_CONSOLIDATION_SET_ID — Foreign key back to GL_CONSOLIDATION_SETS, enabling hierarchical nesting of consolidation sets within a parent set.
  • CREATION_DATE, CREATED_BY — Standard audit columns recording when and by whom the assignment row was created.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard audit columns tracking the most recent modification and the login session responsible for it.
  • CONTEXT and ATTRIBUTE1 through ATTRIBUTE15 — The standard Oracle EBS descriptive flexfield (DFF) columns, providing extensible, customer-defined attributes for the assignment.

The audit and flexfield columns are common to nearly all GL transactional and setup tables and support change tracking and localization requirements without altering the core association model.

Common Use Cases and Queries

Typical uses of this table center on determining which consolidations participate in a set, and on tracing the hierarchical relationship between parent and child consolidation sets. A common reporting query joins the assignment table to GL_CONSOLIDATION_SETS and GL_CONSOLIDATION to produce a readable inventory of consolidation set membership.

  • List all consolidations in a set: SELECT a.CONSOLIDATION_SET_ID, a.CONSOLIDATION_ID FROM GL_CONS_SET_ASSIGNMENTS a WHERE a.CONSOLIDATION_SET_ID = :set_id;
  • Resolve set membership with names: join GL_CONS_SET_ASSIGNMENTS to GL_CONSOLIDATION_SETS on CONSOLIDATION_SET_ID and to GL_CONSOLIDATION on CONSOLIDATION_ID.
  • Detect nested set hierarchies: filter on CHILD_CONSOLIDATION_SET_ID IS NOT NULL to identify parent sets that contain child consolidation sets.
  • Audit reconciliation: group by CONSOLIDATION_SET_ID to verify each set has the expected number of assigned consolidations, comparing against configuration documentation.

Additional reporting scenarios include identifying orphaned assignments after a consolidation is removed, and supporting migration or upgrade validation in which membership must be confirmed before and after a release move.

Related Objects

The table's relationships are defined entirely through its foreign keys, and the most significant related objects are the consolidation setup entities it references.

  • GL_CONSOLIDATION_SETS — Referenced by CONSOLIDATION_SET_ID (the owning set) and by CHILD_CONSOLIDATION_SET_ID (the nested child set).
  • GL_CONSOLIDATION — Referenced by CONSOLIDATION_ID; provides the consolidation definition assigned to the set.
  • GL_CONS_SET_ASSIGNMENTS_PK — The primary key constraint enforcing unique set-and-consolidation pairs.
  • GL_CONS_SET_ASSIGNMENTS_U1 — The unique index on (CONSOLIDATION_SET_ID, CONSOLIDATION_ID), reinforcing the business key.

Because consolidation processing reads set membership through this table, downstream consolidation run programs and GL reporting on consolidated balances depend directly on the accuracy of its contents.