Results for “substring_length”

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

Overview

GCS_LEX_MAP_DRV_DETAILS is a transactional configuration table within the GCS – Financial Consolidation Hub product family in Oracle E-Business Suite. Its documented purpose is to store Transformation Rule Set derivation details — the low-level instructions that define how a source column value is derived, transformed, merged, or looked up when consolidation data is mapped from a source ledger to a target reporting structure. Each row in this table represents one derivation step attached to a parent derivation record, and the table therefore acts as the operational detail layer beneath the higher-level mapping and rule definitions maintained elsewhere in the GCS schema.

GCS is documented as obsolete in the 12.1.1 and 12.2.2 reference material, and the ETRM metadata explicitly notes that this object is not implemented in this database for the source environment examined. That statement should be interpreted as an implementation-specific observation rather than a statement that the table never exists in any EBS instance; deployments that still run Financial Consolidation Hub will retain the object. From a Data Vault modeling perspective, the mined FK structure classifies GCS_LEX_MAP_DRV_DETAILS as a link — a relationship table that associates a derivation with one or more column definitions. It is a modeling suggestion only, but it aligns with the fact that all three foreign keys point outward to parent definition tables.

Key Information Stored

The table carries sixteen documented columns. The most significant are the identifiers and transformation parameters:

• DERIVATION_DETAIL_ID — surrogate primary key, enforced by GCS_LEX_MAP_DRV_DETAILS_PK and mirrored by the unique index GCS_LEX_MAP_DRV_DETAILS_U1. It is also a business-key candidate.
• DERIVATION_ID — foreign key to GCS_LEX_MAP_DERIVATIONS, tying the detail row to its parent derivation rule.
• DETAIL_COLUMN_ID — foreign key to GCS_LEX_MAP_COLUMNS, identifying the target column being populated.
• LOOKUP_COLUMN_ID — foreign key to GCS_LEX_MAP_COLUMNS, identifying the source or lookup column used to resolve the value.
• DETAIL_CONSTANT — literal value assigned when the derivation is a constant rather than a column reference.
• STRING_ACTION_TYPE_CODE and STRING_MERGE_ORDER — define the string operation applied and the ordering when multiple string fragments are concatenated.
• SUBSTRING_START_INDEX and SUBSTRING_LENGTH — parameters for substring extraction from a source value.
• PLSQL_PARAM_NAME and PLSQL_PARAM_SOURCE_CODE — bind the derivation to a PL/SQL parameter name and the code path that supplies its value.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns maintained by the application's WHO columns convention.

Common Use Cases and Queries

Typical reporting and diagnostic queries join the detail rows back to their parent derivation and column definitions to reconstruct the full transformation logic used in a consolidation run. A representative pattern is:

• SELECT d.derivation_detail_id, d.derivation_id, c1.column_name target_col, c2.column_name lookup_col, d.string_action_type_code, d.substring_start_index, d.substring_length FROM gcs_lex_map_drv_details d, gcs_lex_map_derivations r, gcs_lex_map_columns c1, gcs_lex_map_columns c2 WHERE d.derivation_id = r.derivation_id AND d.detail_column_id = c1.column_id AND d.lookup_column_id = c2.column_id AND r.rule_set_id = :rule_set_id ORDER BY d.derivation_id, d.string_merge_order;
• Auditing which derivations rely on substring or constant logic, useful when validating a migration or reconciling differences between rule sets.
• Impact analysis before changing a lookup column: query all detail rows referencing a given LOOKUP_COLUMN_ID.
• Reconstructing historical rule definitions after upgrades by filtering on LAST_UPDATE_DATE.

Because the object is documented as obsolete and absent in some databases, queries should be guarded by a check against ALL_TABLES before execution in mixed environments.

Related Objects

The documented foreign keys identify three direct parents, which are the most significant related objects:

• GCS_LEX_MAP_DERIVATIONS — joined on DERIVATION_ID; provides the parent derivation rule set context.
• GCS_LEX_MAP_COLUMNS — joined on DETAIL_COLUMN_ID; supplies the target column metadata.
• GCS_LEX_MAP_COLUMNS — joined again on LOOKUP_COLUMN_ID; supplies the source column metadata. Both joins reference the same physical table and require distinct aliases in SQL.

Together these tables form the lexical mapping layer of Financial Consolidation Hub. No additional views, APIs, or dependent objects are documented in the ETRM metadata for this table, and the obsolete status of the GCS module means external integration points are unlikely to be supported in current 12.2.2 environments.