Search Results delete_msc_table

Overview

The PL/SQL package body APPS.MSC_PURGE_LID is a core data-management utility within the Oracle Advanced Supply Chain Planning (ASCP) module, which resides in the MSC schema of Oracle E-Business Suite. Its principal business function is to purge Local Instance Data (LID) and Operational Data Store (ODS) records associated with a given planning instance, plan, or collection. In distributed and multi-instance planning deployments, staging and ODS tables accumulate large volumes of transient planning data — demands, supplies, resource requirements, and job operation snapshots. The package provides the controlled, parameterized deletion logic used to reclaim space and reset these tables after a planning run, collection, or instance lifecycle event.

The header comment ($Header: MSCPPURB.pls 120.1.12010000.2) confirms the package is maintained under the standard MSC product file naming conventions, with the PURB suffix denoting a package body. Global variables such as v_instance_id, v_date, and v_request_id indicate the package is designed to operate within a concurrent-program execution context, capturing the calling request, user, and application identifiers for audit and error logging.

Key Procedures and Functions

The documented package exposes nine procedures and functions, plus internal helpers such as CHECK_ST_STATUS:

• PURGE_LID_TABLES — The primary entry point for purging local instance data tables in bulk.
• PURGE_ODS_TABLES_DEL — Deletes rows from ODS (Operational Data Store) tables.
• PURGE_ODS_DATA — Removes collected planning data from ODS staging structures.
• PURGE_ODS_LEG_DATA — Purges ODS data at the leg or routing level.
• PURGE_INST_ENTITY_ODS_DATA — Removes ODS data scoped to a specific instance entity.
• PURGE_INSTANCE_DATA — Purges all data belonging to a planning instance.
• PURGE_PLAN_DATA — Purges data associated with a specific plan.
• PURGE_INSTANCE_PLAN_DATA — Combines instance- and plan-level scope for targeted removal.
• PURGE_LOCALID_TABLE — Cleans a specific local-ID table (e.g., MSC_ITEM_ID_LID).

The internal function CHECK_ST_STATUS validates the staging-table state of an instance before purging. It reads enable_flag and st_status from MSC_APPS_INSTANCES and returns TRUE only when the status permits purging (e.g., G_ST_READY or G_ST_PURGING), raising FND messages such as MSC_ST_ERROR_DATA_EXIST or MSC_ST_ERROR_PULLING otherwise.

Tables Accessed

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

• MSC_APPS_INSTANCES — queried to validate instance enablement and staging status.
• FND_APPLICATION, FND_LOOKUP_VALUES, FND_ORACLE_USERID, FND_PRODUCT_INSTALLATIONS — support environment resolution, lookup validation, and logging context.
• MSC_DEMANDS, MSC_LOCAL_ID_DEMAND, MSC_LOCAL_ID_SUPPLY — demand and supply records subject to purge.
• MSC_DESIGNATORS, MSC_ITEM_ID_LID — item and designator local-ID data.
• MSC_JOB_OPERATIONS, MSC_JOB_OPERATION_NETWORKS, MSC_JOB_OP_RESOURCES, MSC_JOB_REQUIREMENT_OPS — manufacturing job-operation snapshots in ODS.
• MSC_RESOURCE_REQUIREMENTS — resource demand data.

Usage Notes

MSC_PURGE_LID is typically invoked indirectly through ASCP concurrent programs (e.g., "Purge Local Instance Data" / "Purge Staging Data") rather than called directly. The presence of v_request_id, v_prog_appl_id, and v_program_id globals confirms a concurrent-manager execution model. The package is not referenced by other documented packages, so custom code invoking it must pass a valid instance_id and ensure the target instance's staging status is READY or PURGING. Error logging relies on FND_MESSAGE, so failures surface through the standard concurrent request log.