Results for “sys_il0000405533c00011”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The table JTF.JTY_TAE_ATTR_PRODUCTS_SQL is an Oracle E-Business Suite repository object owned by the JTF schema and registered in FND Design Data as JTF.JTY_TAE_ATTR_PRODUCTS_SQL. It resides in the APPS_TS_TX_DATA tablespace with a default PCTFREE of 10 and holds attribute product SQL definitions consumed by the Trading Application Exchange (TAE) framework. The table's core purpose is to persist the dynamically generated SQL statements that TAE uses during attribute matching and product qualification. Its three CLOB columns—BATCH_MATCH_SQL, BATCH_NM_MATCH_SQL, and BATCH_NMC_MATCH_SQL—store matching logic for legacy and two "new mode" matching variants, allowing the atomic replacement of generated SQL without code deployment.

The ETRM metadata classifies this object heuristically as standalone, meaning it has no downstream foreign keys pointing into it from other tables in the mined relationship model. From a Data Vault modeling perspective, this would be considered a satellite-style object: its rows are descriptive, versioned by the standard Who columns, and anchored by a surrogate key rather than participating in an explicit hub/link topology. The single documented foreign key (SECURITY_GROUP_ID → FND_SECURITY_GROUPS) supports hosted or multi-tenant deployments and does not change the standalone classification.

Key Information Stored

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

• ATTR_PRODUCTS_SQL_ID (NUMBER) — the surrogate primary key that uniquely identifies each stored SQL definition.
• SOURCE_ID and TRANS_TYPE_ID — identify the source and transaction type context for which the matching SQL applies.
• PROGRAM_NAME (VARCHAR2 60) — the logical name of the concurrent or generated program associated with the SQL block.
• ATTR_RELATION_PRODUCT (NUMBER) — links the SQL to an attribute-relation-product construct.
• BATCH_MATCH_SQL, BATCH_NM_MATCH_SQL, and BATCH_NMC_MATCH_SQL — three CLOB(4000) columns holding the actual matching SQL for baseline, new-mode, and new-mode-compatible processing paths.
• KEEP_FLAG (VARCHAR2) — flags rows that must be retained through regeneration cycles.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the standard Who columns that audit row lifecycle.
• PROGRAM_ID, PROGRAM_LOGIN_ID, PROGRAM_APPLICATION_ID, REQUEST_ID, PROGRAM_UPDATE_DATE — the concurrent-program Who columns identifying the request that last touched the row.
• SECURITY_GROUP_ID — the sole documented foreign key, referencing FND_SECURITY_GROUPS.
• OBJECT_VERSION_NUMBER — used for optimistic locking by the OAF/ADF framework.

The unique indexes documented are all LOB indexes (SYS_IL0000405533C00011$$, ...12$$, ...13$$) in APPS_TS_TX_DATA; these enforce uniqueness at the LOB segment level and are not business-key candidates in a semantic sense. The true business key remains the ID plus the SOURCE_ID/TRANS_TYPE_ID combination.

Common Use Cases and Queries

Typical scenarios include diagnosing why a specific transaction type is failing attribute matching, auditing which SQL variants exist for a source, and reviewing stale SQL after a patch or setup change. A representative query joining to the security group reference:

SELECT s.ATTR_PRODUCTS_SQL_ID,
       s.SOURCE_ID,
       s.TRANS_TYPE_ID,
       s.PROGRAM_NAME,
       s.KEEP_FLAG,
       s.OBJECT_VERSION_NUMBER,
       g.SECURITY_GROUP_NAME
FROM   JTF.JTY_TAE_ATTR_PRODUCTS_SQL s,
       FND_SECURITY_GROUPS g
WHERE  s.SECURITY_GROUP_ID = g.SECURITY_GROUP_ID
  AND  s.SOURCE_ID = :source_id
  AND  s.TRANS_TYPE_ID = :trans_type_id;

Because the SQL bodies are CLOB, DBMS_LOB.SUBSTR must be used to inspect fragments safely in SQL*Plus or a reporting tool. Concurrent program diagnostics can be driven off REQUEST_ID and PROGRAM_UPDATE_DATE to trace which request last regenerated a given row. Reporting use cases include inventory of distinct PROGRAM_NAME values and monitoring of rows where KEEP_FLAG is set but the underlying SQL has not been refreshed.

Related Objects

The documented relationship data identifies only one explicit foreign key, but operational dependencies include:

• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID; the only documented FK relationship.
• FND_CONCURRENT_REQUESTS — implied through REQUEST_ID and the Program Who columns for request traceability.
• JTF.JTY_TAE_ATTR_PRODUCTS (and sibling TAE attribute tables such as JTF_TAE_ATTR_PRODUCTS_B/_TL) — the parent TAE product definition tables that provide the ATTR_RELATION_PRODUCT context.
• JTF_TAE_* helper packages — PL/SQL APIs that generate and persist the CLOB match SQL into this table.
• FND_APPLICATION — resolves PROGRAM_APPLICATION_ID to an owning application.
• FND_USER — resolves CREATED_BY, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN to an operator identity.

Together these objects form the TAE attribute-matching subsystem, with JTY_TAE_ATTR_PRODUCTS_SQL acting as the persisted SQL store rather than a transactional master.