Search Results gen_real_time_sql

Overview

The APPS.JTY_TERR_ENGINE_GEN2_PVT package is a private (PVT) PL/SQL package within the Oracle E-Business Suite Territory Management module (JTY/JTF schema family). Its documented purpose, per the embedded header comments, is to generate the real time matching SQL used by the Territory Assignment engine. In Oracle EBS 12.1.1 and 12.2.2, territory assignment determines which sales territories, resources, and qualifiers apply to a given transaction, lead, opportunity, or customer record. Rather than storing fully denormalized, pre-computed territory membership for every possible record, the engine constructs SQL dynamically so that matching can occur against current data at runtime. This package is the component responsible for assembling that dynamic SQL text.

The package is declared AUTHID CURRENT_USER, meaning it executes with the privileges of the calling user rather than the definer, which is consistent with its role as an internal engine utility invoked within an APPS session. The header history indicates the object was created in July 2000 and carries a header revision dated August 2005, reflecting its long-standing place in the territory infrastructure.

Key Procedures and Functions

The documented interface exposes a single procedure:

• GEN_REAL_TIME_SQL — Generates the real-time matching SQL for the territory engine. It accepts a source identifier, a transaction identifier, a mode indicator, and a start/end date range, and returns the standard concurrent-program style output parameters (errbuf and retcode). The mode parameter governs the behavior of the generated statement, while the date range scopes the qualifiers and denormalization rules considered valid at execution time. The procedure is named for real-time generation precisely because the resulting SQL reflects live qualifier, source, and territory definitions rather than a static snapshot.

No other procedures or functions are documented for this package. Parameter lists are intentionally omitted here; only the documented signature in the ETRM metadata should be relied upon.

Tables Accessed

The package references a broad set of Territory Management and qualifier configuration tables through APPS synonyms, which is expected for an engine that must resolve qualifiers, sources, territories, and denormalization rules into executable SQL. The documented references include:

• Qualifier definition and usage tables: JTF_QUAL_TYPES_ALL, JTF_QUAL_TYPE_USGS_ALL, and JTF_QUAL_USGS_ALL supply the qualifier types and their usage assignments used to build matching predicates.
• Territory definition tables: JTF_TERR_ALL, JTF_TERR_QUAL_ALL, and JTF_TERR_QTYPE_USGS_ALL provide the territory records and the qualifier-to-territory associations the engine translates into SQL.
• Denormalization configuration: JTF_TERR_DENORM_RULES_ALL and JTY_DENORM_DEA_RULES_ALL define how territory and denormalized attribute rules are applied during matching.
• Resource access tables: JTF_TERR_RSC_ALL and JTF_TERR_RSC_ACCESS_ALL identify the resources associated with territories and their access assignments.
• Sources: JTF_SOURCES_ALL provides the source definitions that scope which transactions are evaluated.
• Denormalized value index tables: JTY_DEA_VALUES_IDX_HEADER, JTY_DEA_VALUES_IDX_HEADER_S, JTY_DEA_VALUES_IDX_DETAILS, and JTY_DEA_VALUES_IDX_DETAILS_S store the pre-indexed denormalized values that the generated SQL joins against for efficient real-time evaluation.

Usage Notes

As a PVT-classified package, JTY_TERR_ENGINE_GEN2_PVT is not intended as a public application programming interface. It is invoked internally by the Territory Management engine, and the metadata notes that it is referenced by one other package. It is not exposed through standard concurrent program registration or forms-based user interfaces; rather, it is called programmatically when territory matching SQL must be produced, for example during lead or opportunity assignment processing. Because SQL is generated dynamically, changes to qualifier and territory configurations take effect on the next invocation without requiring pre-compiled matching logic. Custom code should avoid calling private engine packages directly, since their signatures are not guaranteed across patches or upgrades.