Results for “id_flex_code”

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

Overview

The view APPS.RG_SRS_DSS_HIERARCHIES_V is a reporting and integration object owned by the APPS schema and delivered as part of the RG – Application Report Generator product in Oracle EBS 12.1.1 and 12.2.2. Its documented description is restricted: "10SC ONLY". Despite its classification as a standard database view in the ETRM catalog, the definition is specifically scoped to the 10SC (Tennessee) localization context, meaning it is not intended for general-purpose use across all operating units or legislations.

The view presents a unified list of "hierarchies" usable by the Report Generator for structural report definitions. It combines values drawn from two sources — a literal lookup entry and the customer-side hierarchy repository — exposing each as a consistent four-column shape (NAME, HIERARCHY_ID, ID_FLEX_CODE, ID_FLEX_NUM). This structure lets downstream reporting or integration logic reference hierarchies by their stable numeric identifier rather than by name, which is the key concern when the search term is hierarchy_id.

Underlying Base Objects

The ETRM metadata for 12.2.2 documents two referenced base objects:

• GL_LOOKUPS (VIEW) — the General Ledger lookup view, used here to supply a synthetic "ALL" literal hierarchy row.
• RG_DSS_HIERARCHIES (SYNONYM) — the actual hierarchy repository referenced through a synonym, from which user-defined hierarchies are read.

The view text is a UNION of two SELECT statements. The first branch produces the literal row:

SELECT MEANING NAME, -1 HIERARCHY_ID, 'GL#' ID_FLEX_CODE, -100 ID_FLEX_NUM
  FROM GL_LOOKUPS
 WHERE LOOKUP_TYPE = 'LITERAL' AND LOOKUP_CODE = 'ALL'

The second branch reads the genuine hierarchies from RG_DSS_HIERARCHIES:

SELECT NAME, HIERARCHY_ID, ID_FLEX_CODE, ID_FLEX_NUM
  FROM RG_DSS_HIERARCHIES

The result is a source-independent list where the "ALL" pseudo-hierarchy carries sentinel values (HIERARCHY_ID = -1, ID_FLEX_NUM = -100) so that it can never collide with a real hierarchy identifier.

Key Columns

• NAME — the display name of the hierarchy. For the literal branch this is the lookup MEANING for lookup code 'ALL'; for the repository branch it is the stored hierarchy name. This is the human-readable label presented in Report Generator pick lists.
• HIERARCHY_ID — the numeric identifier of the hierarchy. This is the column most frequently referenced in downstream queries and joins. Real hierarchies carry their assigned identifiers; the "ALL" literal row is hard-coded to -1, serving as a reserved sentinel. Applications filtering by this column should treat -1 as the "no specific hierarchy / all hierarchies" selection.
• ID_FLEX_CODE — the key flexfield code describing which flexfield the hierarchy applies to. The literal row uses 'GL#', the General Ledger key flexfield. Rows from the repository carry their own flexfield code, allowing a single view to span multiple flexfield contexts.
• ID_FLEX_NUM — the numeric identifier of the flexfield structure associated with the hierarchy. The literal "ALL" row uses -100, another sentinel that cannot match a valid structure number.

Common Use Cases and Queries

The primary use case is populating Report Generator hierarchy selection lists, or driving conditional report logic against a chosen hierarchy. Because HIERARCHY_ID is the stable key, a typical query retrieves the identifier for a named hierarchy:

SELECT hierarchy_id, id_flex_code, id_flex_num
  FROM apps.rg_srs_dss_hierarchies_v
 WHERE name = :p_hierarchy_name;

A second pattern collapses the "ALL" case, filtering out the sentinel row when a concrete hierarchy is required:

SELECT hierarchy_id, name
  FROM apps.rg_srs_dss_hierarchies_v
 WHERE hierarchy_id > 0
 ORDER BY name;

Conversely, code that needs to detect the "all hierarchies" selection checks for the sentinel directly:

SELECT DECODE(hierarchy_id, -1, 'ALL', name)
  FROM apps.rg_srs_dss_hierarchies_v
 WHERE id_flex_code = 'GL#';

Because the definition relies on the 10SC localization and on the RG_DSS_HIERARCHIES synonym, usage should be validated against the specific environment and patch level before being embedded in custom concurrent programs or integrations. Where the view is present and valid, it remains the documented, supported means of resolving hierarchy_id values for Report Generator hierarchies.