Search Results lookup_result_column_id

Overview

The GCS_LEX_MAP_DERIVATIONS table is a core data structure within the Oracle E-Business Suite (EBS) General Ledger Consolidation System (GCS), specifically for the Legal Entity Extract (LEX) and Mapping functionality in versions 12.1.1 and 12.2.2. This table serves as the central repository for defining the sequence and logic of derivation actions required to populate a target column within a mapping rule. It is essential for configuring the data transformation and enrichment processes that occur when source financial data is prepared for consolidation, ensuring accurate and rule-based population of target ledger attributes.

Key Information Stored

The table defines the execution order and type of each derivation step. The primary columns include DERIVATION_ID (the unique identifier), RULE_ID (linking to the parent rule in GCS_LEX_MAP_RULES), and DERIVATION_SEQUENCE (controlling the order of operations). The critical column DERIVATION_TYPE_CODE dictates the derivation logic, accepting values 'STR' for String Functions, 'PLS' for PL/SQL Functions, or 'LUT' for Lookup Table. Based on this type, specific columns become relevant: LOOKUP_TABLE_ID and LOOKUP_RESULT_COLUMN_ID are populated for 'LUT' derivations to identify the lookup source table and the specific column from which to retrieve the result. For 'PLS' derivations, the FUNCTION_NAME column stores the name of the custom PL/SQL function to execute. The table also contains standard Oracle EBS "Who" columns for auditing.

Common Use Cases and Queries

A primary use case is auditing and troubleshooting mapping rule logic. An administrator can query the derivations for a specific rule to understand the data flow. The column LOOKUP_RESULT_COLUMN_ID, as referenced in the user's search, is specifically queried to identify which column from a lookup table is used to provide a value for a target. A common reporting query would join this table to its related metadata tables to produce a human-readable mapping specification.

• Sample Query to List All Derivations for a Rule:
  SELECT d.derivation_sequence, d.derivation_type_code,
  s.structure_name AS lookup_table, c.column_name AS result_column,
  d.function_name
  FROM gcs.gcs_lex_map_derivations d
  LEFT JOIN gcs.gcs_lex_map_structs s ON d.lookup_table_id = s.structure_id
  LEFT JOIN gcs.gcs_lex_map_columns c ON d.lookup_result_column_id = c.column_id
  WHERE d.rule_id = &rule_id
  ORDER BY d.derivation_sequence;

Related Objects

The GCS_LEX_MAP_DERIVATIONS table is a central hub within the LEX mapping schema, with defined foreign key relationships to several key metadata tables and being referenced by condition and detail tables.

• Parent/Referenced Tables:
  • GCS_LEX_MAP_RULES: Linked via RULE_ID. This is the parent rule for which the derivation is defined.
  • GCS_LEX_MAP_STRUCTS: Linked via LOOKUP_TABLE_ID. Identifies the physical or logical lookup table structure when the derivation type is 'LUT'.
  • GCS_LEX_MAP_COLUMNS: Linked via LOOKUP_RESULT_COLUMN_ID. Identifies the specific column within the lookup table that provides the result value.
  • GCS_LEX_MAP_PLSQL_FUNCS: Linked via FUNCTION_NAME. References the metadata for a custom PL/SQL function when the derivation type is 'PLS'.
• Child/Referencing Tables:
  • GCS_LEX_MAP_CONDITIONS: References DERIVATION_ID. Stores conditional logic that may govern whether a derivation is applied.
  • GCS_LEX_MAP_DRV_DETAILS: References DERIVATION_ID. Stores detailed parameters, such as source column mappings or string function arguments, required to execute the derivation.