Search Results lookup_column_id

Overview

The GCS_LEX_MAP_DRV_DETAILS table is a core data definition object within the Oracle E-Business Suite General Ledger Consolidation System (GCS), specifically for the Legal Entity Extractor (LEX) mapping functionality in releases 12.1.1 and 12.2.2. This table stores the granular configuration rules for data derivation actions used during the extraction and transformation of financial data for consolidation. It defines the specific parameters for different derivation types, including string manipulation functions, PL/SQL function calls, and lookups against reference tables. The table operates as a child entity, where each row provides detailed instructions that belong to a parent derivation rule defined in GCS_LEX_MAP_DERIVATIONS, enabling complex data mapping logic within the consolidation setup.

Key Information Stored

The table's columns define the components of a derivation action. The primary identifier is DERIVATION_DETAIL_ID. The DERIVATION_ID foreign key links the detail to its parent rule. The type of action is dictated by STRING_ACTION_TYPE_CODE ('S' for Substring, 'C' for Copy String, 'F' for Fixed String). For string and copy actions, DETAIL_COLUMN_ID references the source staging table column, while SUBSTRING_START_INDEX and SUBSTRING_LENGTH define the extraction window. For fixed strings, DETAIL_CONSTANT holds the literal value. For lookup-based derivations, DETAIL_COLUMN_ID holds the source column to match, and the LOOKUP_COLUMN_ID foreign key specifies the target column in the lookup table for the join condition. For PL/SQL derivations, PLSQL_PARAM_NAME and PLSQL_PARAM_SOURCE_CODE define how parameters are passed to the function, sourcing from either a column ('C'), string ('S'), or number ('N').

Common Use Cases and Queries

A primary use case is auditing and troubleshooting the configured derivation logic for a legal entity mapping. An administrator may need to review all derivation details for a specific derivation rule to understand the data flow. A common query involves joining to the parent derivations and related column tables to get descriptive names.

• Querying Details for a Specific Derivation:
  SELECT drvd.*, cols.column_name
  FROM gcs.gcs_lex_map_drv_details drvd,
       gcs.gcs_lex_map_columns cols
  WHERE drvd.derivation_id = :p_derivation_id
  AND drvd.detail_column_id = cols.column_id(+)
  ORDER BY drvd.string_merge_order;
• Identifying Lookup-Based Derivations:
  SELECT drvd.derivation_detail_id, drvd.lookup_column_id, lkup_col.column_name AS lookup_column
  FROM gcs.gcs_lex_map_drv_details drvd,
       gcs.gcs_lex_map_columns lkup_col
  WHERE drvd.lookup_column_id IS NOT NULL
  AND drvd.lookup_column_id = lkup_col.column_id;

Related Objects

The table is centrally connected to other key mapping definition tables via foreign key constraints, forming a critical part of the LEX data model.

• GCS_LEX_MAP_DERIVATIONS: The parent table. The DERIVATION_ID column references GCS_LEX_MAP_DERIVATIONS to associate each detail row with its overarching derivation action.
• GCS_LEX_MAP_COLUMNS (via DETAIL_COLUMN_ID): References the source column in the staging interface table used for the derivation's input data.
• GCS_LEX_MAP_COLUMNS (via LOOKUP_COLUMN_ID): References the target column in a lookup table, defining the join condition for lookup-based derivation actions.