Search Results jty_trans_usg_pgm_sql




Overview
Explore in AI Chat

Overview

JTY_TRANS_USG_PGM_SQL is a transactional configuration table owned by the JTF schema (CRM Foundation) and deployed in Oracle E-Business Suite 12.1.1 and 12.2.2. As its ETRM description states, the table stores program SQL for bulk TAE (Territory Assignment Engine). Rather than holding the compiled program itself, it persists the SQL text fragments that the bulk TAE concurrent programs execute to evaluate transaction usage, real-time inserts, and batch reassignment logic against territory definitions.

The heuristic Data Vault classification mined from the foreign-key structure is standalone, meaning the modeler found no inbound or outbound dependencies strong enough to nominate the object as a hub, link, or satellite. In practice the table functions as a reference/lookup store of executable SQL keyed by transaction type and program version, with SECURITY_GROUP_ID providing the only documented foreign-key relationship.

Key Information Stored

The surrogate primary key is TRANS_USG_PGM_SQL_ID, which uniquely identifies each SQL definition row. The documented physical schema exposes 21 columns, of which the following are the most operationally significant:

The unique indexes documented (SYS_IL...C00011$$ through C00016$$) are LOB/interim indexes rather than conventional business keys; no natural unique key beyond the surrogate is confirmed.

Common Use Cases and Queries

Because the table is a configuration repository, its primary consumer is the bulk TAE concurrent program, which selects the appropriate SQL fragment at runtime. The most common query patterns are troubleshooting which SQL will execute and auditing enabled definitions.

  • Retrieving all active SQL definitions for a given transaction type: SELECT * FROM jtf.jty_trans_usg_pgm_sql WHERE trans_type_id = :id AND enabled_flag = 'Y';
  • Comparing real-time versus batch logic: select REAL_TIME_SQL, BATCH_TOTAL_SQL, BATCH_INCR_SQL for a specific PROGRAM_NAME and VERSION_NAME.
  • Verifying security-group scoping when territories fail to process: join SECURITY_GROUP_ID to FND_SECURITY_GROUPS and review the security_group_id value.
  • Auditing recent changes to SQL text using LAST_UPDATE_DATE and LAST_UPDATED_BY.

Related Objects

  • FND_SECURITY_GROUPS — the only documented foreign key; join on jty_trans_usg_pgm_sql.security_group_id = fnd_security_groups.security_group_id.
  • JTY_TRANS_USG_PGM — the parent TAE program definition table against which SOURCE_ID / TRANS_TYPE_ID / PROGRAM_NAME resolve.
  • JTY_TRANS_USG_ASSIGNMENTS — stores the usages the SQL evaluates during batch and real-time runs.
  • JTF_TERR / JTF_TERR_RSRC — territory and resource membership tables consumed by the TAE logic.
  • FND_CONCURRENT_PROGRAMS / FND_CONCURRENT_REQUESTS — the concurrent manager records under which the bulk TAE jobs execute.

These relationships should be validated against the customer’s specific 12.1.1 or 12.2.2 patch level before relying on them for reporting joins.