Search Results sys_il0000405290c00020

Overview

JTF.JTY_DEA_ATTR_PRODUCTS_SQL is a transactional configuration table in the Oracle E-Business Suite JTF (CRM Foundation / Technology Foundation) schema. Its documented purpose is to store attribute product SQL definitions used for date effectivity assignment (DEA). In practice, the table acts as a repository of dynamically generated SQL text fragments that the date effectivity engine executes to match source records to products and transaction types during effectivity resolution.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, consistent with a transactional object that receives ongoing DML from concurrent programs and runtime processes. Under the heuristic Data Vault classification derived from its foreign-key structure, this object is modeled as a standalone structure rather than a classical hub, link, or satellite. The single documented foreign key (SECURITY_GROUP_ID referencing FND_SECURITY_GROUPS) is a hosted-environment security construct rather than a true business relationship, which supports the standalone classification. Architects designing a Data Vault representation should treat the surrogate key as the hub candidate and the SQL-bearing columns as descriptive satellite attributes.

Key Information Stored

The table contains twenty documented columns. The most operationally significant are:

• DEA_ATTR_PRODUCTS_SQL_ID — NUMBER, the surrogate primary key that uniquely identifies each SQL definition row.
• SOURCE_ID — identifies the source record or entity to which the attribute product SQL applies.
• TRANS_TYPE_ID — the transaction type the SQL is scoped to, tying the definition to a specific business transaction context.
• ATTR_RELATION_PRODUCT — numeric reference to the attribute relation product, linking the SQL to a product definition.
• PROGRAM_NAME — VARCHAR2(60); the name of the concurrent program or process that generated or owns the definition.
• BATCH_DEA_MATCH_SQL — CLOB(4000); the batch match SQL used to resolve date effectivity for source records.
• BATCH_DEA_MATCH_SQL_WITH_TERR — CLOB(4000); a variant of the match SQL that incorporates territory restrictions.
• KEEP_FLAG — VARCHAR2 flag controlling retention or regeneration behavior of the row.
• OBJECT_VERSION_NUMBER — supports optimistic locking during concurrent updates.
• Standard who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, plus the program who columns PROGRAM_ID, PROGRAM_LOGIN_ID, PROGRAM_APPLICATION_ID, REQUEST_ID, PROGRAM_UPDATE_DATE.
• SECURITY_GROUP_ID — used in hosted (multi-tenant) deployments; the documented foreign key to FND_SECURITY_GROUPS.

Two unique LOB indexes are documented: SYS_IL0000405290C00011$$ and SYS_IL0000405290C00020$$, both in APPS_TS_TX_DATA. These index the two CLOB columns (BATCH_DEA_MATCH_SQL and BATCH_DEA_MATCH_SQL_WITH_TERR respectively) and are infrastructure artifacts rather than business-key candidates. The user query string "sys_il0000405290c00020" corresponds directly to the second of these LOB index segments.

Common Use Cases and Queries

Typical usage centers on diagnosing date effectivity failures, auditing generated SQL, and reporting on configuration coverage. Because the table stores executable SQL text, DBAs and developers frequently inspect rows when match results are incorrect or when a concurrent program fails to resolve products.

A representative query listing active definitions by transaction type:

• SELECT DEA_ATTR_PRODUCTS_SQL_ID, SOURCE_ID, TRANS_TYPE_ID, PROGRAM_NAME, ATTR_RELATION_PRODUCT, KEEP_FLAG, LAST_UPDATE_DATE FROM JTF.JTY_DEA_ATTR_PRODUCTS_SQL ORDER BY TRANS_TYPE_ID, SOURCE_ID;

To locate rows for a specific business context, filter on TRANS_TYPE_ID and ATTR_RELATION_PRODUCT. To audit the generated SQL, select the CLOB columns with DBMS_LOB.SUBSTR:

• SELECT DEA_ATTR_PRODUCTS_SQL_ID, DBMS_LOB.SUBSTR(BATCH_DEA_MATCH_SQL, 4000, 1) AS MATCH_SQL FROM JTF.JTY_DEA_ATTR_PRODUCTS_SQL WHERE TRANS_TYPE_ID = :p_trans_type_id;

Reporting scenarios include identifying duplicate SQL definitions across sources, tracking definitions created or modified within a period using CREATION_DATE and LAST_UPDATE_DATE, and verifying territory-aware definitions by checking for non-null BATCH_DEA_MATCH_SQL_WITH_TERR.

Related Objects

The documented relationship data identifies one foreign key: SECURITY_GROUP_ID references FND_SECURITY_GROUPS, the standard multi-tenant security construct in hosted EBS environments. The primary integration points are the date effectivity engine components that consume the CLOB SQL and the concurrent programs recorded in PROGRAM_NAME, PROGRAM_ID, and REQUEST_ID, which link to FND_CONCURRENT_PROGRAMS and FND_CONCURRENT_REQUESTS. The surrogate key is referenced by child structures within the JTF date effectivity model, and the SOURCE_ID, TRANS_TYPE_ID, and ATTR_RELATION_PRODUCT columns act as logical join points to source, transaction type, and product attribute relation entities respectively. The two LOB segments (SYS_IL0000405290C00011$$ and SYS_IL0000405290C00020$$) are dependent infrastructure objects that must remain intact for CLOB access and are the typical source of the ORA-00942 or index-related errors reported against this table.