Search Results p_trace

Overview

AS_ATA_TOTAL_PUB is a public PL/SQL package in the Oracle EBS Applications schema (APPS) that supports territory assignment processing across the Oracle Advanced Sales (AS) and Oracle Proposals (PRP) product families. The package is part of the "Territory Access" (ATA) infrastructure, whose purpose is to evaluate transactional entities — accounts, leads, opportunities, quotes, and proposals — against configured territory definitions and then assign the resulting territory accesses to the appropriate sales resources. It is the mechanism by which the Oracle Territory Manager engine is invoked for "total" (as opposed to incremental or delta) assignment runs.

The package is declared with AUTHID CURRENT_USER, meaning all SQL statements execute under the privileges of the invoking user rather than the package owner. The header revision noted in the source (asxtatas.pls 120.4, dated 2005) indicates the package has been stable across the 12.1.1 and 12.2.2 release lines and is therefore reachable through the APPS synonym layer in both environments.

Key Procedures and Functions

The ETRM metadata documents six public routines. Each of the first five wraps a total territory-access assignment run for a distinct entity type, and each accepts common control parameters — an ERRBUF/RETCODE concurrent-program error pair, a submit flag, a filter or status argument, an additional WHERE clause, an analyzed-percentage hint, and the P_DEBUG and P_trace diagnostics switches. The P_trace parameter is the specific keyword of interest to callers; when populated (typically with 'Y'), it enables trace output for the territory assignment run, and it is exposed consistently on every assignment procedure in the package.

• Assign_Account_Terr_Accesses — Assigns territory accesses for accounts, filtered by account type and an optional additional WHERE clause.
• Assign_Lead_Terr_Accesses — Assigns territory accesses for leads, filtered by lead status.
• Assign_Oppty_Terr_Accesses — Assigns territory accesses for opportunities, filtered by opportunity status.
• Assign_Quote_Terr_Accesses — Assigns territory accesses for quotes, with flags to exclude ordered and expired quotes.
• Assign_Proposal_Terr_Accesses — Assigns territory accesses for proposals.
• DELETE_CHANGED_ENTITY — Removes entries from the "changed entity" staging queues once assignment processing has completed, keeping the delta-tracking tables clean.

Tables Accessed

The package reads and writes the following tables through APPS synonyms:

• AS_CHANGED_ACCOUNTS_ALL — the staging queue of accounts requiring territory re-evaluation.
• ASO_CHANGED_QUOTES — the equivalent queue for quotes.
• PRP_CHANGED_PROPOSALS — the equivalent queue for proposals.
• AS_TERR_RESOURCES_TMP — a temporary working table used to stage candidate territory-resource matches before final persistence.
• FND_USER — resolves the sales resources and, where applicable, the invoking user context for territory evaluation.
• FND_PROFILE_OPTIONS and FND_PROFILE_OPTION_VALUES — retrieve profile option settings that govern territory assignment behavior and debug/trace activation.
• DBMS_STATS — invoked to gather or refresh statistics on the staging tables during large assignment runs.
• PLITBLM — the PL/SQL table maintenance package, used for in-memory collection handling.

Usage Notes

This package is not intended for direct end-user invocation. It is typically called from the Oracle Territory Manager concurrent programs and from the territory assignment submission forms within the Advanced Sales and Proposals modules. The ERRBUF and RETCODE OUT parameters conform to the standard concurrent-program call signature, which allows the routines to be registered directly as PL/SQL stored procedure concurrent programs.

Custom code and diagnostics frequently call these procedures with P_trace => 'Y' to activate SQL trace output while diagnosing territory assignment performance in 12.1.1 and 12.2.2. The package is referenced by zero other packaged APIs per the ETRM metadata, confirming it sits at the top of the call hierarchy as a public entry point rather than serving as a shared internal library.