Search Results p_sql_trace

Overview

JTF_TAE_ASSIGN_PUB is a public PL/SQL API belonging to the Oracle E-Business Suite Territory Assignment Engine (TAE), delivered under the APPS schema. The package encapsulates the core Territory Assignment logic used by Oracle Sales, Oracle Telesales, and related CRM modules to determine which territories and territory resources qualify as "winners" for a given assignment target. As documented in the package header, its purpose is to provide "Joint Task Force applications territory manager public API's" for retrieving winning territories or territory resources using an input objects table as an assignment target.

The package exposes a single primary public entry point (GET_WINNERS) plus a supporting set of internal-facing procedures used for parallel processing, index management, change detection, and cleanup. Because it is classified as PUB (public), Oracle supports its invocation from external custom code, though the documented API surface is dominated by GET_WINNERS. The package notes that territories must be set up before invocation, making TAE territory definition a hard prerequisite.

Key Procedures and Functions

• GET_WINNERS — The principal public API. It returns winning resource IDs, winning resource names with details, or winning territory IDs for an assignment request. Its signature includes p_api_version_number, p_init_msg_list, p_SQL_Trace, p_Debug_Flag, standard FND message outputs (x_return_status, x_msg_count, x_msg_data), p_request_id, p_source_id, p_trans_object_type_id, p_target_type (default 'TAP'), and the concurrent-program style ERRBUF/RETCODE outputs.
• GET_WINNERS_PARALLEL and GET_WINNERS_PARALLEL_SETUP — Parallel-execution variants that allow territory winner determination to be split across worker processes, typically driven by a concurrent program request.
• PROCESS_LEVEL_WINNERS and PROCESS_FINAL_LEVEL_WINNERS — Procedures that evaluate winner results at intermediate and final territory qualifier levels, resolving the ranked output of territory matching.
• GET_SQL_FOR_CHANGES — Builds the dynamic SQL used to identify territories whose qualifiers have changed, supporting re-evaluation of already-assigned targets.
• FETCH_NM_REASSIGN_TRANS — Retrieves records requiring reassignment when territory definitions change and a non-manual (NM) reassignment is requested.
• DELETE_CHANGED_TERR_RECS — Removes stale rows from the changed-territory staging structures prior to reprocessing.
• DROP_TAE_TRANS_INDEXES and NM_TABLE_TRUNCATE_DROP_INDEX — Maintenance procedures that drop and manage indexes on TAE transaction tables to improve bulk-load performance.
• CLEAR_TRANS_DATA — Purges transient territory assignment transaction data after processing completes.

Tables Accessed

• JTF_TERR_ALL — The master territory definition table; the source of candidate territories evaluated against the assignment target.
• JTF_TERR_QTYPE_USGS_ALL — Stores which qualifier types a territory uses, driving the qualifier evaluation logic.
• JTF_TERR_RSC_ALL — Territory resource assignments; supplies the winning resource IDs and names returned by GET_WINNERS.
• JTF_TERR_USGS_ALL — Territory usage definitions that determine which transaction types a territory applies to.
• JTF_CHANGED_TERR_ALL — Staging table capturing territories whose definitions changed, used by the reassignment procedures.
• ALL_TABLES — Data dictionary view consulted during index maintenance to confirm object existence before DDL.
• UTL_FILE — Oracle-supplied package used for server-side file I/O, typically for debug or trace log output.

Usage Notes

GET_WINNERS is normally invoked from Oracle Sales and Telesales forms, from TAE concurrent programs that perform bulk territory reassignment, and from custom PL/SQL integrations that need programmatic territory assignment. Because it accepts p_SQL_Trace and p_Debug_Flag, it can emit SQL trace and debug diagnostics when a caller needs to profile territory matching performance.

Callers should set p_target_type appropriately ('TAP' is the documented default) and must already have functioning TAE territory setup. For large volume targets, the parallel variants should be preferred over the single-call GET_WINNERS to avoid excessive runtime. The package is documented as being referenced by zero other packages, meaning it is an external entry point rather than an internally chained API; any dependency direction runs from custom code or forms into this package, not the reverse.