Search Results ece_rule_simple_lookup_u1

Overview

EC.ECE_RULE_SIMPLE_LOOKUP is a reference configuration table within the Oracle E-Business Suite (EBS) E-Commerce Gateway (ECE) module, part of the EDI Transaction Rule Manager (ETRM) framework. It stores the metadata required to resolve a column value dynamically during transaction processing, using a straightforward lookup definition. Rather than hard-coding a value, a rule can point the runtime processor at an external lookup table and column, optionally constrained by a WHERE clause, and the resulting SELECT statement is used to derive the appropriate value for the target transaction column.

A row is created in this table whenever a "simple lookup" rule is assigned to a column of a transaction. The core mechanics are that the columns LOOKUP_TABLE, LOOKUP_COLUMN, and LOOKUP_WHERE_CLAUSE together construct the SELECT statement, with the LOOKUP_WHERE_CLAUSE entered without the leading "WHERE" keyword. The object resides in the APPS_TS_SEED tablespace, consistent with its role as seeded configuration data. From a Data Vault modeling perspective, the heuristic classification returned by the metadata is standalone; the table acts as a descriptive hub/satellite holding the lookup definition, with a foreign key to ECE_COLUMN_RULES anchoring it to the parent column rule.

Key Information Stored

The table contains 14 documented columns. The most significant are:

• LOOKUP_RULE_ID (NUMBER(15), mandatory) — Surrogate primary key of the table, enforced by the unique index ECE_RULE_SIMPLE_LOOKUP_U1. Also the documented business-key candidate.
• COLUMN_RULE_ID (NUMBER(15)) — Foreign key referencing ECE_COLUMN_RULES, identifying the column rule to which this simple lookup is attached.
• LOOKUP_TABLE (VARCHAR2(50)) — Name of the external table from which the value is to be retrieved.
• LOOKUP_COLUMN (VARCHAR2(50)) — Name of the column within LOOKUP_TABLE that supplies the resolved value.
• LOOKUP_WHERE_CLAUSE (VARCHAR2(200)) — Optional filtering predicate used to narrow the lookup, entered without the "WHERE" keyword.
• Standard Who columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN provide audit and accountability metadata.
• Concurrent program columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE track the concurrent request and program that created or last updated the record.

Common Use Cases and Queries

The table is primarily used by the ETRM engine to resolve dynamic column values during EDI transaction processing, and secondarily as a configuration audit surface for developers and functional analysts. A typical query retrieves all simple lookup rules for a given column rule:

• SELECT LOOKUP_RULE_ID, COLUMN_RULE_ID, LOOKUP_TABLE, LOOKUP_COLUMN, LOOKUP_WHERE_CLAUSE FROM EC.ECE_RULE_SIMPLE_LOOKUP WHERE COLUMN_RULE_ID = :column_rule_id;
• To reconstruct the effective SELECT statement: concatenate 'SELECT ' || LOOKUP_COLUMN || ' FROM ' || LOOKUP_TABLE, appending ' WHERE ' || LOOKUP_WHERE_CLAUSE when the clause is populated.
• To audit recent configuration changes: query by PROGRAM_UPDATE_DATE or LAST_UPDATE_DATE to identify rules added or modified by a specific concurrent program.

Related Objects

• EC.ECE_COLUMN_RULES — Parent of the COLUMN_RULE_ID foreign key; the assignment context for each simple lookup rule.
• EC.ECE_RULE_SIMPLE_LOOKUP# — The internal dependent object generated for the base table.
• EC.ECE_RULE_SIMPLE_LOOKUP_PK / ECE_RULE_SIMPLE_LOOKUP_U1 — Primary key constraint and unique index on LOOKUP_RULE_ID.
• EC.ECE_RULE_DETAILS / EC.ECE_RULES — Related rule definition objects in the ETRM rule hierarchy that drive transaction processing.
• FND Concurrent Requests (FND_CONCURRENT_REQUESTS) — Joins via REQUEST_ID to trace the program that created or updated each configuration row.