Search Results msd_cs_clmn_identifiers_vl




Overview
Explore in AI Chat

Overview

The view MSD_CS_CLMN_IDENTIFIERS_VL is an Oracle E-Business Suite database object owned by the APPS schema and defined within the MSD – Demand Planning product family. Its documented purpose is to expose Custom Stream Column identifiers, providing a language-aware, fully translated representation of the identifier definitions used by the Demand Planning custom stream framework. The "_VL" suffix indicates that the view joins a base entity table with its translation table and applies the session language, so that descriptive text returned to the user appears in the correct linguistic context.

In the context of Oracle EBS 12.1.1 and 12.2.2, this view functions as a reporting and integration access point rather than as a transaction-processing object. It consolidates identifier metadata — column identifiers, descriptions, prompts, and system flags — with the meaning derived from the lookup type MSD_CS_IDENTIFIERS. Because the view is documented as VALID in the ETRM repository, it can be relied upon by concurrent programs, BI Publisher data templates, and custom SQL reports that need to enumerate the identifiers available to a custom stream configuration without querying the underlying tables directly.

Underlying Base Objects

The view text documented in the ETRM metadata is built from three referenced objects: FND_LOOKUP_VALUES_VL (a view), and the synonyms MSD_CS_CLMN_IDENTIFIERS and MSD_CS_CLMN_IDENTIFIERS_TL. These synonyms resolve to the base identifier and identifier-translation tables in the APPS schema.

The join logic is straightforward. The base table MSD_CS_CLMN_IDENTIFIERS supplies the stored identifier attributes, while MSD_CS_CLMN_IDENTIFIERS_TL supplies the translatable description and user prompt. The two are joined on COLUMN_IDENTIFIER, and the translation row is restricted by LANGUAGE = USERENV('LANG'), which returns only the row matching the current session language. The lookup view FND_LOOKUP_VALUES_VL is outer-joined by matching LOOKUP_TYPE = 'MSD_CS_IDENTIFIERS' and LOOKUP_CODE = IDEN.IDENTIFIER_TYPE, which resolves the stored identifier type code into a user-facing meaning. This lookup dependency means that changes to the MSD_CS_IDENTIFIERS lookup values are reflected automatically in the view output.

Key Columns

The view exposes the following documented columns:

  • COLUMN_IDENTIFIER — The unique key for the custom stream column identifier; also the join key between the base and translation tables.
  • DESCRIPTION — The translated descriptive text for the identifier, sourced from the TL table.
  • IDENTIFIER_TYPE — The code classifying the identifier; participates in the lookup join.
  • IDENTIFIER_TYPE_DESC — The resolved meaning of the identifier type, drawn from FND_LOOKUP_VALUES_VL.
  • USER_PROMPT — The translatable prompt presented to the user when the identifier is selected.
  • SYSTEM_FLAG — Indicates whether the identifier is seeded by the application (system-defined) or user-defined.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns recording who created and last modified the row.

The IDENTIFIER_TYPE column is particularly relevant to the "identifier_type" search term, as it is the attribute that links each identifier to its classification and to the corresponding lookup meaning.

Common Use Cases and Queries

Typical uses include validating identifier configurations prior to custom stream setup, generating reference listings for demand planning administrators, and driving parameter lists (LOVs) in custom concurrent programs. The following query lists all identifiers with their translated type and prompt, ordering system-defined entries first:

  • SELECT column_identifier, identifier_type, identifier_type_desc, description, user_prompt, system_flag FROM apps.msd_cs_clmn_identifiers_vl ORDER BY system_flag, column_identifier;

To isolate user-defined identifiers:

  • SELECT column_identifier, description, user_prompt FROM apps.msd_cs_clmn_identifiers_vl WHERE system_flag = 'N';

To filter by a specific identifier type:

  • SELECT column_identifier, identifier_type_desc, description FROM apps.msd_cs_clmn_identifiers_vl WHERE identifier_type = :p_type;

Because the view already resolves the language via USERENV('LANG'), no additional language predicate is required in reporting queries. Callers should be aware that rows whose identifier type has no matching lookup value will return a null IDENTIFIER_TYPE_DESC, since the lookup is applied as an outer join.