Search Results msc_apps_instances_s

Overview

MSC_MANAGE_PLAN_PARTITIONS is an APPS-owned PL/SQL package body in the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) schema. Its purpose is to manage the physical and logical partitioning of planning data structures used by MSC (Material Planning / Supply Chain Planning) tables, particularly those tied to plans and planning instances. Oracle's planning products store large volumes of transient planning data — supply, demand, exceptions, and plan output — and partitioning is used to segregate that data by plan or by planning instance so that daily planning runs can truncate or drop partitions rather than perform expensive DELETE operations. This package provides the programmatic infrastructure that creates, drops, and analyzes those partitions. It is an internal utility package rather than a business-facing API; it is classified as OTHER in the ETRM metadata and is not referenced by any other database object, indicating that it is invoked primarily from concurrent programs, DDL maintenance routines, or installation scripts rather than from other PL/SQL packages.

Key Procedures and Functions

The package exposes 13 documented procedures and functions. GET_PLAN and GET_INSTANCE retrieve plan and planning-instance identifiers respectively, resolving the plan or instance whose partitions are to be managed. GET_PARTITION_NAME and GET_PARTITION_NUMBER derive the naming convention and ordinal position for a partition, ensuring consistent identifiers across create and drop operations.

• CREATE_INST_PARTITION — creates a partition on an MSC instance-level table.
• CREATE_EXIST_PLAN_PARTITIONS — creates partitions for plans that already exist in MSC_PLANS, used when partitioning is enabled after plans have been defined.
• CREATE_EXIST_INST_PARTITIONS — the instance-level counterpart, creating partitions for existing planning instances.
• CREATE_PARTITIONS — the overarching routine that drives partition creation, likely coordinating plan and instance partition generation.
• CREATE_FORCE_PARTITION and DROP_FORCE_PARTITION — create or remove a partition unconditionally, bypassing normal existence checks; used for recovery or administrative override.
• PURGE_PARTITION — clears data from an existing partition, typically between planning runs.
• DROP_EXIST_PLAN_PARTITIONS and DROP_EXIST_INST_PARTITIONS — remove partitions for existing plans and instances, used during cleanup or when partitioning is disabled.
• ANALYZE_PLAN — invokes statistics gathering, consistent with the dependency on FND_STATS, so the optimizer has current statistics after partition changes.

No parameter lists are documented; only purposes are inferred from naming and the dependencies listed.

Tables Accessed

The package reads and writes MSC_PLANS and MSC_PLANS_S to identify plans, and MSC_APPS_INSTANCES and MSC_APPS_INSTANCES_S to identify planning instances. MSC_PLAN_PARTITIONS and MSC_INST_PARTITIONS are the core metadata tables that record which partitions exist for each plan and instance; the package maintains these records in step with the physical DDL. DDL is executed through AD_DDL (the AD utilities DDL interface) and DBA_TABLES is queried to verify table and partition existence. FND_ORACLE_USERID and FND_PRODUCT_INSTALLATIONS supply installation and schema context, while DUAL is used for singleton queries. FND_API, FND_GLOBAL, FND_MESSAGE, FND_PROFILE, and FND_STATS provide standard error handling, session context, messaging, profile lookups, and statistics collection.

Usage Notes

MSC_MANAGE_PLAN_PARTITIONS is an internal infrastructure package, so it is normally invoked indirectly. In a 12.1.1 or 12.2.2 environment it is called during planning instance setup and plan creation, during the partition maintenance phase of the ASCP concurrent programs, and during patching or installation when partition schemes must be created or dropped. Because it performs DDL and requires privileges on AD_DDL and DBA_TABLES, invocations must run as a user with the necessary schema and dictionary access. Custom code should avoid calling this package directly; instead it should rely on the supported planning APIs and concurrent programs that manage plan and instance lifecycle, leaving partition management to this package.