Search Results g_st_collecting

Overview

MSD_PURGE_LEG is a PL/SQL package body owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the Advanced Planning and Demand Planning (MSD/MSC) product family and, according to the ETRM metadata, is classified as an "OTHER" API rather than a public or private interface. Its core business function is the purging of staging and setup data associated with a specific planning instance. The package definition opens with a header comment confirming this scope: the procedure deletes or truncates all tables linked to the lookup type MSC_X_SETUP_ENTITY_CODE. This makes MSD_PURGE_LEG part of the housekeeping layer that allows planning administrators to clear stale or rejected records from the planning schema before re-running collection, refresh, or plan generation jobs. The user search term "st_status" is consistent with this role: packages in this family report status through constants such as G_SUCCESS and G_ERROR, and the deleted records frequently carry status indicator columns that the purge routine removes in bulk.

Key Procedures and Functions

The ETRM metadata documents one procedure for this package: LAUNCH_PROCEDURE. This is the externally addressable entry point and acts as the orchestrator for the purge run. It is the procedure typically called by a concurrent program or by other planning code to initiate the delete or truncate operation against a nominated planning instance.

The package source excerpt additionally exposes an internal procedure, delete_records, which is not included in the documented procedure list but is clearly central to the logic. It accepts an instance code, an instance ID, a flag for deleting rejected records, and a truncation flag. It resolves the set of target tables dynamically from the lookup table, then either truncates or deletes from each. The procedure maintains package-level state variables including v_batch_size, v_debug, v_program_status, and an accumulating lv_total row count. It also declares handlers for table_not_found (ORA-00942) and synonym_translation_invalid (ORA-00980), indicating awareness that dynamically selected tables may be missing or inaccessible.

Tables Accessed

The metadata records two referenced tables, both accessed through APPS synonyms:

• FND_LOOKUP_VALUES — read to resolve the list of purgeable tables. The cursor filters on ENABLED_FLAG = 'Y', VIEW_APPLICATION_ID = 700, SUBSTR(ATTRIBUTE1,1,3) = 'MSD', and LOOKUP_TYPE = 'MSC_X_SETUP_ENTITY_CODE'. This lookup type is effectively the control table that determines which MSD staging tables participate in the purge.
• MSC_APPS_INSTANCES — used to validate or resolve the planning instance (instance code and instance ID) against which the purge is executed, ensuring the correct application instance context is applied before records are removed.

Usage Notes

MSD_PURGE_LEG is not referenced by any other documented package, so it is invoked directly rather than as a nested dependency. Its principal use case is a concurrent program or administrative submission that purges planning setup and staging data for a selected instance, optionally including rejected records. The truncation flag allows the same package to switch between a full truncate (for a clean reset) and a row-level delete (for partial cleanup).

In both 12.1.1 and 12.2.2, callers should invoke LAUNCH_PROCEDURE rather than the internal delete_records routine. Because target tables are resolved dynamically at runtime, any customization must maintain the MSD-prefixed entries in the MSC_X_SETUP_ENTITY_CODE lookup; missing or disabled entries result in the error message MSC_PS_INVALID_LOOKUP and a program status of error. Debug output is controlled by v_debug, which defaults to TRUE, making this package useful during diagnostics of failed purge runs.