Search Results jty_dea_attr_products_sql




Overview
Explore in AI Chat

Overview

JTY_DEA_ATTR_PRODUCTS_SQL is a CRM Foundation (JTF) transactional configuration table within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores attribute-level product SQL definitions that drive date effectivity assignment (DEA) processing. In practical terms, the table holds the batch SQL statements used by the Date Effectivity Assignment engine to match and assign date-effective attributes to products and territories. Each row represents a stored SQL fragment—typically associated with a source, transaction type, and program context—that the concurrent processing framework executes during DEA batch runs.

The ETRM metadata classifies this object, using a heuristic Data Vault model, as standalone. That classification is a modeling suggestion: the table's only documented foreign key points to FND_SECURITY_GROUPS (via SECURITY_GROUP_ID), and it has no inbound or outbound business-entity relationships mined from the FK structure. It therefore behaves as a reference/configuration satellite rather than a hub or link in a strict Data Vault sense, though its WHO-columns (CREATED_BY, LAST_UPDATED_BY, REQUEST_ID) give it the characteristic audit signature of a satellite-style record.

Key Information Stored

The physical schema in 12.2.2 exposes 20 columns owned by JTF. The most significant are:

The documented unique indexes (SYS_IL0000405290C00011$$ and SYS_IL0000405290C00020$$) correspond to LOB segment indexes for the two CLOB/nested SQL columns rather than business-key candidates. The true business key is effectively the combination of SOURCE_ID, TRANS_TYPE_ID, and ATTR_RELATION_PRODUCT, with DEA_ATTR_PRODUCTS_SQL_ID as the surrogate.

Common Use Cases and Queries

Technical consultants query this table to inspect or troubleshoot DEA batch matching logic, verify that SQL was generated correctly for a given source and transaction type, and audit concurrent program executions. Typical patterns include filtering by SOURCE_ID and TRANS_TYPE_ID to retrieve the active SQL, or joining to FND_CONCURRENT_REQUESTS on REQUEST_ID to trace generation. Administrators may review KEEP_FLAG before purging regenerated definitions.

SELECT dea_attr_products_sql_id, source_id, trans_type_id,
       attr_relation_product, batch_dea_match_sql
FROM   jtf.jty_dea_attr_products_sql
WHERE  source_id = :p_source
AND    trans_type_id = :p_trans_type;

Related Objects

The documented relationship set is minimal, reflecting the standalone classification. The principal joins are:

  • FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID to resolve the owning security group.
  • FND_CONCURRENT_REQUESTS / FND_CONCURRENT_PROGRAMS — joined via REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID to identify the concurrent job that populated the row.
  • FND_APPLICATION — resolves PROGRAM_APPLICATION_ID in program audit queries.
  • JTY_DEA_* companion tables (DEA attribute and product configuration tables) — logically related through ATTR_RELATION_PRODUCT, SOURCE_ID, and TRANS_TYPE_ID, though no enforced FK is documented.
  • FND_USER — joined on CREATED_BY / LAST_UPDATED_BY for audit reporting.