Search Results drop_temp_queues
Overview
AD_ZD_PREP is an internal Oracle E-Business Suite utility package owned by the APPS schema and delivered under the Advanced Data / Online Patching (AD_ZD) technology stack. Its principal business function is to prepare an E-Business Suite database for the editioning and online patching model introduced in EBS 12.2. The package generates and executes the DDL required so that application objects can be converted into editioned objects, moves XML schemas out of editioned schemas into the Non-Editioned APPS schema, recompiles database TYPE objects, repairs synonym metadata, and manages Advanced Queuing artifacts that would otherwise obstruct the edition-enablement process.
The header comment identifies the source file as ADZDPRPS.pls with a revision marker consistent with the 12.1.1 to 12.2 transition period, and the ETRM API classification records it as OTHER, reflecting that it is a supporting utility rather than a public application API. The package is documented with 29 procedures and functions and is not referenced by any other package, indicating that it is invoked directly by the patching infrastructure and administrative scripts rather than through layered PL/SQL call chains.
Key Procedures and Functions
The package exposes a small set of internal helper routines alongside its main public entry points.
- IS_TYPE_EVOLVED and IS_TYPE_EXISTS — utility functions that report whether a given schema type has been evolved or exists at all.
- CREATE_PATCH_SERVICE — creates the service identified by the constant C_PATCH_SERVICE, whose value is 'ebs_patch'.
- MOVE_XML_SCHEMAS — relocates XML schemas referenced by E-Business Suite tables into the Non-Editioned APPS schema.
- DO_PREP — the documented public entry point that drives DDL generation for the Online-Enablement process.
- RECOMPILE_TYPES — recompiles TYPE objects from their source schema, using a global list of types and invoking the ST API during DDL generation. It depends on COPY_TYPE and COPY_EVOLVED_TYPE.
- DROP_TEMP_QUEUES, DROP_QUEUES (X_OWNER, X_QUEUE_NAME), and STOP_QUEUE (X_QUEUE_NAME) — manage obsolete and temporary Advanced Queuing queues. STOP_QUEUE is the procedure associated with the search term "stop_queue"; it halts a queue prior to removal.
- ENABLE_EDITIONS and ENABLE_USER_4EDITION (X_USERNAME) — enable the editions mechanism globally and for a specified user.
- FIX_PUBLIC_SYNONYMS and FIX_PUBLIC_SYNONYM — resolve synonym issues: PUBLIC synonyms pointing at objects slated for editioning are dropped and replaced with equivalent private synonyms so dependencies remain valid.
- COPY_TYPES, COPY_TYPE, COPY_EVOLVED_TYPES, COPY_EVOLVED_TYPE, FIX_TYPES, and FIX_TYPE — supporting routines that copy and repair TYPE definitions during preparation.
- RECREATE_AQ_OBJECT — rebuilds Advanced Queuing objects after the editioning changes have been applied.
Tables Accessed
The package reads data dictionary views through APPS synonyms to identify objects requiring preparation. FND_ORACLE_USERID and DBA_USERS identify database users and their linkage to EBS accounts, which is essential for ENABLE_USER_4EDITION. DBA_OBJECTS, DBA_TABLES, DBA_VIEWS, DBA_TAB_COLUMNS, and DBA_TYPES are used to enumerate objects, columns, and types subject to editioning or recompilation. DBA_INVALID_OBJECTS and DBA_DEPENDENCIES support dependency analysis and revalidation after DDL changes. DBA_SYNONYMS is central to the FIX_PUBLIC_SYNONYMS logic, while DBA_XML_SCHEMAS and DBA_MVIEWS support MOVE_XML_SCHEMAS and related object handling. Advanced Queuing handling relies on DBA_QUEUES, DBA_QUEUE_TABLES, and DBA_SERVICES.
Usage Notes
AD_ZD_PREP is an infrastructure package rather than an end-user API. It is typically invoked by the Online Patching / editioning enablement driver of EBS 12.2, in particular through DO_PREP during the preparation phase that precedes adoption of editioned objects in 12.1.1 to 12.2 upgrade or conversion work. STOP_QUEUE and DROP_TEMP_QUEUES/DROP_QUEUES are used to quiesce and remove AQ queues that cannot participate in editioning, with RECREATE_AQ_OBJECT restoring them afterward. Because the package performs structural DDL, it should only be executed during controlled maintenance windows by administrators running the supported patching utilities; direct invocation from custom code is not recommended. The absence of references from other packages confirms its role as a scripted entry point rather than a reusable application service.