Search Results jty_dea_values_idx_header_s

Overview

JTY_TERR_ENGINE_GEN2_PVT is a private PL/SQL package body in the Oracle E-Business Suite Territory Management (also referred to as ETRM or Oracle Territory Manager) module. Its stated purpose is to generate the real-time matching SQL used by the territory assignment engine to evaluate whether a given entity — such as a customer, lead, opportunity, or resource — qualifies for membership in a territory. The package header declares that it is available for private use only, meaning it is an internal implementation package rather than a public API and should not be called directly by customer extensions.

The package body opens with the declaration of the global variables G_USER_ID and G_SYSDATE, initialized from FND_GLOBAL.USER_ID() and SYSDATE respectively, along with a comprehensive set of PL/SQL collection types based on columns of JTF_QUAL_USGS_ALL. These collections hold qualifier usage identifiers, comparison operators, low/high value character and number bounds, interest type identifiers, primary and secondary interest codes, currency codes, and value identifiers, and are used to assemble the dynamic matching logic in bulk. A logging helper, jty_log, is also declared to emit diagnostic messages at a configurable log level.

Key Procedures and Functions

The documented interface exposes a single procedure, GEN_REAL_TIME_SQL. Consistent with the package's stated purpose, this procedure builds the SQL statement used to perform real-time territory qualification. It consumes the territory, qualifier, and denormalization metadata stored in the repository tables and emits executable matching SQL that the territory engine runs against transaction data. The ETRM metadata does not publish the parameter list for this procedure, so callers must consult the package specification in the AD_APPS schema for the exact signature. No other procedures or functions are documented for this package body at the 12.2.2 release level.

Tables Accessed

GEN_REAL_TIME_SQL references the following tables through APPS synonyms:

• JTF_QUAL_TYPES_ALL, JTF_QUAL_TYPE_USGS_ALL, JTF_QUAL_USGS_ALL — define qualifier types and their usage, supplying the operators and value ranges that drive matching criteria.
• JTF_SOURCES_ALL — identifies the source system against which the generated SQL is scoped.
• JTF_TERR_ALL, JTF_TERR_QTYPE_USGS_ALL, JTF_TERR_QUAL_ALL — the territory definition, its associated qualifier types, and the qualifiers attached to each territory.
• JTF_TERR_DENORM_RULES_ALL, JTY_DENORM_DEA_RULES_ALL — denormalization rules that determine how qualifier values are flattened for efficient matching.
• JTF_TERR_RSC_ALL, JTF_TERR_RSC_ACCESS_ALL — territory resources and the resource-access rules used when qualifying sales representatives.
• JTY_DEA_VALUES_IDX_HEADER, JTY_DEA_VALUES_IDX_HEADER_S, JTY_DEA_VALUES_IDX_DETAILS, JTY_DEA_VALUES_IDX_DETAILS_S — the denormalized index headers and detail rows that store pre-computed qualifier values consumed during matching.

Usage Notes

JTY_TERR_ENGINE_GEN2_PVT is invoked internally by the territory engine whenever real-time territory qualification is required — for example, when a lead, opportunity, or service request is created or updated and territories must be re-evaluated. The metadata records that the package is referenced by one other package, reflecting this internal dependency. Because the package is classified as PVT and its header states it is for private use only, customers and partners should not call GEN_REAL_TIME_SQL from custom code; the supported approach is to use the public territory APIs and the concurrent programs that drive territory assignment. The G_USER_ID initialization from FND_GLOBAL.USER_ID() indicates that generated SQL may incorporate the current session user, making correct FND session context essential on any invocation path. The script header, versioned at 120.26.12020000.3, applies to both 12.1.1 and 12.2.2 environments.