Search Results jty_tae_attr_products_sql




Overview
Explore in AI Chat

Overview

The table JTF.JTY_TAE_ATTR_PRODUCTS_SQL is a CRM Foundation (JTF) data object within Oracle E-Business Suite, valid in both the 12.1.1 and 12.2.2 releases. It belongs to the Trading Attribute Engine (TAE) framework, a rule-driven component used by Oracle Advanced Pricing, Oracle Trade Management, and related CRM modules to match transactional or batch data against configured attribute-based SQL conditions. The table stores attribute product SQL definitions — precomposed SQL fragments that the TAE runtime executes to qualify source records against products or programs during batch processing.

From a Data Vault modeling perspective, the mined FK metadata classifies this table as a standalone satellite-style object: it owns its own surrogate key (ATTR_PRODUCTS_SQL_ID) and references external constructs (notably FND_SECURITY_GROUPS) but is not itself a junction between two business hubs. This classification is heuristic and should be treated as a modeling suggestion rather than a normalization prescription.

Key Information Stored

The 21-column physical structure is dominated by SQL text fragments and WHO-audit columns. The most operationally significant columns include:

The unique indexes listed (SYS_IL0000405533C00011$$ through C00013$$) are function-based/LOB indexes on the SQL columns rather than classic business-key candidates; no traditional business unique key is documented, so ATTR_PRODUCTS_SQL_ID remains the definitive identifier.

Common Use Cases and Queries

Because this object drives TAE attribute qualification, typical usage centers on diagnosing batch matching behavior and auditing SQL persistence. A representative query to inspect SQL fragments for a transaction type:

SELECT ATTR_PRODUCTS_SQL_ID, SOURCE_ID, TRANS_TYPE_ID,
       PROGRAM_NAME, KEEP_FLAG
  FROM JTF.JTY_TAE_ATTR_PRODUCTS_SQL
 WHERE TRANS_TYPE_ID = :trans_type_id
   AND NVL(KEEP_FLAG,'N') = 'N';

Security-scoped reporting joins to the security group:

SELECT a.ATTR_PRODUCTS_SQL_ID, a.PROGRAM_NAME, s.SECURITY_GROUP_NAME
  FROM JTF.JTY_TAE_ATTR_PRODUCTS_SQL a,
       FND_SECURITY_GROUPS s
 WHERE a.SECURITY_GROUP_ID = s.SECURITY_GROUP_ID;

Common scenarios include troubleshooting stale or incorrect batch match SQL, verifying that TAE regeneration has produced current fragments, and confirming that concurrent request IDs (REQUEST_ID, PROGRAM_ID) tie the SQL creation to the nightly pricing or trade batch job. Reporting use cases include audit trails of who modified a SQL fragment and when.

Related Objects

  • FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; the sole documented FK.
  • Other JTY_TAE_* tables (e.g., attribute, source, and transaction type definition tables) that share SOURCE_ID / TRANS_TYPE_ID context.
  • FND_CONCURRENT_REQUESTS — join on REQUEST_ID to identify the generating concurrent program.
  • FND_APPLICATION — join on PROGRAM_APPLICATION_ID for program application context.
  • TAE runtime APIs and the Advanced Pricing / Trade Management batch engines consume these SQL fragments indirectly during qualification.

Because the object is classified as standalone, no direct child hubs or links are implied; dependency is expressed through the TAE application layer and security model rather than relational enforcement.