Search Results purge_access_tables

Overview

AS_TAP_PURGE_PUB is a public PL/SQL package in the Oracle E-Business Suite (EBS) Applications schema (APPS), operating with AUTHID CURRENT_USER. Its stated purpose, per the package header comments, is to purge the AS_ACCESSES_ALL and AS_TERRITORY_ACCESSES tables. These are the core territory access tables maintained by Oracle Territory Manager (part of the AS/ASO sales foundation). Over time, as territory definitions, assignment rules, and access grants are re-evaluated for sales representatives, sales groups, and organizations, these tables accumulate large volumes of historical access rows. An unbounded growth of territory access data degrades the performance of territory-related queries and assignment processes. AS_TAP_PURGE_PUB provides a controlled, parallel-enabled purge mechanism to remove obsolete or superseded access records. The package header identifies it as version 120.2, last updated in August 2005, and shows a creation history dating to September 2003. It is classified as a public API (PUB), indicating that it is intended for supported external invocation rather than internal-only use. The purge is designed to support concurrent program submission, as evidenced by the standard ERRBUF and RETCODE OUT parameters on its main entry points.

Key Procedures and Functions

The package exposes three documented procedures:

• PURGE_ACCESS_TABLES — The principal entry point for the purge operation. It returns the standard concurrent program results through ERRBUF and RETCODE, and accepts debug and trace mode switches so that diagnostic output can be generated when required. It orchestrates the overall purge of territory access data.
• PREPARE_PARALLEL_PROCESSING — Prepares and calibrates the parallel execution of the purge. It receives a count, a minimum-number-of-parallel-processes threshold, and a requested number of child workers, and returns the actual number of workers used through X_ActualWorkersUsed. This procedure allows the purge to be partitioned across multiple worker processes, with the final worker count adjusted to suit the volume of data to be processed.
• DELETE_ACCESS_RECORDS — Performs the actual deletion of access rows for a given worker. It accepts the same ERRBUF, RETCODE, debug, and trace parameters as the main entry point, plus a worker identifier that scopes the deletion to one partition of the overall workload. Iterative invocation of this procedure across worker IDs constitutes the parallel purge phase.

Tables Accessed

According to the documented metadata, the package references the following tables through APPS synonyms:

• AS_ACCESSES_ALL_ALL — Stores the territory access records (grants linking resources or groups to territories). This is a primary target of the purge; obsolete access rows are deleted here.
• AS_TERRITORY_ACCESSES — Stores territory-to-access associations. As named directly in the package purpose, it is purged alongside the access table.
• AS_TAP_PURGE_WORKING — A working/staging table that supports the purge process itself, most likely holding intermediate sets of rows identified for deletion and enabling the workload to be split among parallel workers.
• JTF_TERR_ALL — The territory definitions table from the foundation (JTF) layer. It is referenced to establish which territory access data is subject to purging.
• PLITBLM — A general PL/SQL utility table used for numeric range processing; consistent with the package's use of counts and worker partitioning.

Usage Notes

AS_TAP_PURGE_PUB is typically invoked as a concurrent program (the ERRBUF/RETCODE signature and debug/trace switches are characteristic of EBS concurrent program wrappers), and can also be called from custom PL/SQL where an administrator or developer needs to purge territory access data. In the 12.1.1 and 12.2.2 code lines the package is not referenced by any other packaged API, confirming that it is designed as a top-level, standalone batch utility rather than a dependency of other code. The user query for prepare_parallel_processing indicates interest in the parallel control path: callers determine worker counts via PREPARE_PARALLEL_PROCESSING and then drive DELETE_ACCESS_RECORDS per worker. Because the procedures delete access records permanently, they should be run only during planned maintenance windows, with debug and trace modes enabled as necessary, and after appropriate backups or validation of the territory data to be retained.