Search Results ece_rule_simple_lookup_pk

Overview

ECE_RULE_SIMPLE_LOOKUP is a table in the EC (e-Commerce Gateway) schema of Oracle E-Business Suite, documented as VALID in both 12.1.1 and 12.2.2. Per the ETRM metadata, it "contains the column rule simple lookup information." It supports the e-Commerce Gateway's rule engine, which drives the translation, validation, and mapping of inbound and outbound interface data. Where ECE_COLUMN_RULES defines a transformation or derivation instruction at the column level, ECE_RULE_SIMPLE_LOOKUP stores the concrete lookup specification that resolves that instruction — typically a source table, a source column, and an optional filter — so that the runtime process can retrieve a value without invoking a complex user-defined rule.

The heuristic Data Vault classification mined from the foreign-key structure is standalone. In modeling terms, this implies the table is neither a pure hub, link, nor satellite on its own; it can be treated as a dependent reference/detail structure anchored to its parent rule rather than as an independent business key repository.

Key Information Stored

• LOOKUP_RULE_ID — the surrogate primary key. It is the single column of the constraint ECE_RULE_SIMPLE_LOOKUP_PK and is also the sole column of the unique index ECE_RULE_SIMPLE_LOOKUP_U1, making it the documented business-key candidate.
• COLUMN_RULE_ID — foreign key to ECE_COLUMN_RULES. This is the link that ties a simple lookup definition to the parent column rule it serves.
• LOOKUP_TABLE — identifies the database table from which the simple lookup value is drawn.
• LOOKUP_COLUMN — identifies the specific column within LOOKUP_TABLE that supplies the returned value.
• LOOKUP_WHERE_CLAUSE — stores the predicate used to restrict the lookup, allowing conditional value resolution.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN capture standard who/when audit trail data.
• Concurrent program context — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE record the concurrent request and program that last touched the row.

The table contains 14 documented columns in total, of which the five functionally significant ones (LOOKUP_RULE_ID, COLUMN_RULE_ID, LOOKUP_TABLE, LOOKUP_COLUMN, LOOKUP_WHERE_CLAUSE) define the lookup behavior; the remainder are audit and concurrent-processing attributes.

Common Use Cases and Queries

Typical usage centers on inspecting or troubleshooting how a given column rule resolves its value. A common query joins the simple lookup to its parent rule:

• Resolve a lookup by parent rule: SELECT s.LOOKUP_RULE_ID, s.LOOKUP_TABLE, s.LOOKUP_COLUMN, s.LOOKUP_WHERE_CLAUSE FROM ECE_RULE_SIMPLE_LOOKUP s WHERE s.COLUMN_RULE_ID = :column_rule_id.
• Audit recent changes: filter on LAST_UPDATE_DATE and LAST_UPDATED_BY to identify who modified a lookup definition and when.
• Trace concurrent load activity: join REQUEST_ID to the concurrent request tables to find the process that created or updated a lookup row.
• Impact analysis: enumerate all simple lookups pointing at a specific LOOKUP_TABLE before that table is modified, preventing broken mappings in the gateway.

Related Objects

• ECE_COLUMN_RULES — the parent table; ECE_RULE_SIMPLE_LOOKUP.COLUMN_RULE_ID references it. This is the primary relationship for the table.
• ECE_RULE_SIMPLE_LOOKUP_PK / _U1 — the primary key constraint and unique index on LOOKUP_RULE_ID.
• Concurrent request tables — related via REQUEST_ID and PROGRAM_ID for process lineage.
• Gateway interface and mapping tables — consume column-rule output produced using these simple lookups.

Because the mined relationship data identifies only the ECE_COLUMN_RULES foreign key, that parent table is the most significant related object; broader dependencies flow through the e-Commerce Gateway rule engine rather than through additional documented foreign keys.