Search Results as_accesses_u2

Overview

AS_TAP_PURGE_PUB is a public PL/SQL package in the APPS schema that performs maintenance on the Territory Access Profile (TAP) data model used by Oracle Territory Manager and related modules such as Sales, Service, and Incentive Compensation. Its principal purpose is to reconcile and remove orphaned or invalid rows held in the territory assignment reach tables. Over time, automated territory assignment processes and manual maintenance can leave rows in AS_TERRITORY_ACCESSES that no longer correspond to a valid access record in AS_ACCESSES_ALL_ALL, or that point to a territory whose active date range does not include the current system date. The package identifies these corrupt rows and physically deletes them, optionally using parallel execution to process large volumes efficiently.

The header of the package body identifies the entity as "PURGE TAP RECORDS" and defines internal constants G_CURSOR_LIMIT and G_NUM_REC, both fixed at 10,000, which govern cursor fetch and batching behavior. Trace and debug behavior is controlled through the AS_GAR package (AS_GAR.SETTRACE and AS_GAR.g_debug_flag), indicating that the package integrates with the EBS debugging and instrumentation framework.

Key Procedures and Functions

• PURGE_ACCESS_TABLES — The main driver procedure. It declares a cursor, c_get_corrupt, that applies the INDEX_FFS and parallel_index hints against AS_TERRITORY_ACCESSES (index AS_TERRITORY_ACCESSES_U1) and parallel hints against AS_ACCESSES_U1 to locate corrupt rows. The cursor returns the rowid and access_id of territory access rows that either have no matching access_id in AS_ACCESSES_ALL_ALL or whose territory has no active JTF_TERR_ALL record covering sysdate. Results are collected into PL/SQL associative structures (TBL_ROWID_TYPE and ACC_NUM_TYPE) so they can be batched for deletion. The procedure accepts an error buffer, a return code, and debug/trace mode parameters, and logs its start and completion through AS_GAR.LOG.
• PREPARE_PARALLEL_PROCESSING — Support procedure that determines the parallel degree for the purge operation, deriving values such as the minimum number of parallel processes, number of child workers, and actual workers. This enables the purge to scale on large TAP deployments without a single serial execution becoming a bottleneck.
• DELETE_ACCESS_RECORDS — Performs the actual row deletion of the corrupt AS_TERRITORY_ACCESSES entries identified by the cursor, working from the batched rowid collection so that deletes can be committed in controlled chunks.

Tables Accessed

• AS_TERRITORY_ACCESSES — The primary target of the purge. Rows here link territory definitions to access records; the package reads rowid and access_id values and deletes rows that fail the validation criteria.
• AS_ACCESSES_ALL_ALL — The authoritative parent of access records. The cursor uses a NOT EXISTS check against this table to identify territory access rows whose access_id no longer exists.
• JTF_TERR_ALL — The territory definition table. The cursor verifies that each territory's start_date_active and end_date_active bracket sysdate; rows referencing inactive or expired territories are flagged as corrupt.
• AS_TAP_PURGE_WORKING — A working/staging table used to hold intermediate results during the purge, allowing the cursor results and parallel workers to coordinate.
• PLITBLM — The standard PL/SQL index-by table type package referenced for the collection types used to batch rowids.

Usage Notes

AS_TAP_PURGE_PUB is a concurrent-program oriented package. It is typically submitted as an EBS concurrent request (using the ERRBUF/RETCODE signature convention) by an administrator performing territory data cleanup, or invoked from the Territory Manager administration forms. Because the package performs direct DML against AS_TERRITORY_ACCESSES, it should be run during periods of low territory reassignment activity to avoid contention with concurrent territory assignment jobs. The use of parallel hints means the request should be configured with an appropriate degree of parallelism on the target database. Although the ETRM metadata records no packages referencing AS_TAP_PURGE_PUB, its procedures are public and may be called from custom purge or reconciliation routines, but doing so requires the caller to manage debug/trace flags and interpret the returned error buffer and return code.