Search Results source_table_id

Overview

The CN_TABLE_MAPS_ALL table is a core data definition table within the Oracle E-Business Suite Incentive Compensation (CN) module. It serves as a metadata repository that defines the mapping relationships between source and destination database tables. Specifically, its documented purpose is to map source tables to destination tables for Accounts Receivable (AR) column mappings. This functionality is essential for data transformation and integration processes, particularly when moving or synchronizing transactional data, such as commission-related records, between different entities or staging areas within the EBS architecture. As an ALL table, it is multi-org enabled, meaning it stores data for multiple operating units, with the ORG_ID column segregating the data.

Key Information Stored

The table's structure centers on identifying the source and destination entities of a mapping rule. The primary identifier is the TABLE_MAP_ID, which is unique per operating unit (ORG_ID). The two most critical foreign key columns are SOURCE_TABLE_ID and DESTINATION_TABLE_ID, which store references to the CN_OBJECTS_ALL table. This design indicates that the actual table names are stored in the CN_OBJECTS_ALL repository, and this table simply links them. The MODULE_ID column links to CN_MODULES_ALL_B, potentially categorizing the mapping rule within a specific functional module or process. The presence of a unique key on the combination of DESTINATION_TABLE_ID, SOURCE_TABLE_ID, and ORG_ID ensures that no duplicate mapping rules are defined for the same pair of tables within an operating unit.

Common Use Cases and Queries

This table is primarily accessed during the setup, execution, and auditing of data mapping and integration jobs in Incentive Compensation. A common use case is verifying the configured table mappings for a data load from a source transaction table to a destination compensation staging table. Database administrators or functional consultants might query this table to understand data flow dependencies or to troubleshoot mapping errors. A typical diagnostic query would join to CN_OBJECTS_ALL to resolve the object IDs into actual table names:

• SELECT tm.table_map_id, src.object_name source_table, dest.object_name destination_table, tm.org_id FROM cn_table_maps_all tm, cn_objects_all src, cn_objects_all dest WHERE tm.source_table_id = src.object_id AND tm.destination_table_id = dest.object_id AND tm.org_id = src.org_id AND tm.org_id = dest.org_id;

Another critical use case is as a parent record for detailed column mapping rules, which are stored in the related CN_COLUMN_MAPS_ALL table.

Related Objects

CN_TABLE_MAPS_ALL has defined relationships with several other CN module tables, forming the backbone of the mapping metadata.

• CN_OBJECTS_ALL (Foreign Key x2): Provides the actual table names for the SOURCE_TABLE_ID and DESTINATION_TABLE_ID columns. Joins are on SOURCE_TABLE_ID = CN_OBJECTS_ALL.OBJECT_ID and DESTINATION_TABLE_ID = CN_OBJECTS_ALL.OBJECT_ID, with matching ORG_ID.
• CN_MODULES_ALL_B (Foreign Key): Associates the table mapping with a specific module. Join is on MODULE_ID = CN_MODULES_ALL_B.MODULE_ID and matching ORG_ID.
• CN_COLUMN_MAPS_ALL (Parent): This is a critical child table. CN_TABLE_MAPS_ALL is referenced as a foreign key by CN_COLUMN_MAPS_ALL via CN_COLUMN_MAPS_ALL.TABLE_MAP_ID and matching ORG_ID. This relationship means that once a high-level table-to-table map is defined here, the specific column-level mappings are stored in CN_COLUMN_MAPS_ALL.