This appendix covers the following topics:
The list below shows the partitioned tables that are created in the MSC Schema for VCP. Understanding and managing the size of the VCP tablespaces (MSCD (Data) and MSCX (Indexes)) is discussed in Managing Tablespaces.
There are several reasons for these partitions: They allow for quick access to data striped by partition/plan_id, we can truncate a partition much quicker than deleting data for a specific plan_id, and we can use exchange partition function to swap partitions with TEMP tables.
We partition tables for different purposes in the applications:
MSC base tables are used to store collected data (ODS instance partition) or for plan data (PDS plan partition) or can be used for BOTH ODS and PDS purposes.
MSC_ST% staging tables are used for data collections (excluding MSC_ST%F).
MSC%F tables are used for Advanced Planning Command Center (APCC).
The %_MV tables (Materialized View) are special and have a separate partitioning scheme.
This list was compiled using the following SQL to help segregate the different types of tables.
Summary list of tables:
#1 - Get all tables select table_name from dba_part_tables where owner = 'MSC' #2 - Get the ODS and PDS tables SELECT table_name from dba_part_tables where owner = 'MSC' and table_name not in (select table_name from dba_part_tables where owner = 'MSC' and table_name like 'MSC_ST%' -- tables for Collections Staging tables union select table_name from dba_part_tables where owner = 'MSC' and table_name like 'MSC%F' -- tables for APCC ) order by table_name; #3 – Get Staging tables for Data Collections select table_name from dba_part_tables where owner = 'MSC' and table_name like 'MSC_ST%' and table_name not in (select table_name from dba_part_tables where owner = 'MSC' and table_name like 'MSC%F' -- tables for APCC ) order by table_name; #4 – Get APCC tables for Archive Plan data and APCC staging tables used to populate the APCC Plan Data select table_name from dba_part_tables where owner = 'MSC' and table_name like 'MSC%F' order by table_name;
These tables are used for either collected data ODS, planning data PDS, or both. Partitions are created accordingly.
| TABLE NAMES FOR ODS AND PDS BASE TABLES | PDS/OPS/BOTH |
|---|---|
| MSC_ALLOC_DEMANDS | Both |
| MSC_ALLOC_SUPPLIES | Both |
| MSC_ANALYSIS_AGGREGATE | PDS |
| MSC_ATP_PEGGING | PDS |
| MSC_ATP_SUMMARY_RES | Both |
| MSC_ATP_SUMMARY_SD | Both |
| MSC_ATP_SUMMARY_SO | ODS |
| MSC_ATP_SUMMARY_SUP | Both |
| MSC_BOMS | Both |
| MSC_BOM_COMPONENTS | Both |
| MSC_CRITICAL_PATHS | PDS |
| MSC_DELIVERY_DETAILS | ODS |
| MSC_DEMANDS | Both |
| MSC_DMD_SCN_METRICS | Both |
| MSC_DOC_ATTACHMENTS | ODS |
| MSC_EAM_ACT_ASSOCIATIONS | ODS |
| MSC_EAM_ASSET_EQUIP_DTLS | ODS |
| MSC_EXCEPTION_DETAILS | PDS |
| MSC_EXC_DETAILS_ALL | PDS |
| MSC_FULL_PEGGING | PDS |
| MSC_ITEM_CATEGORIES | ODS |
| MSC_ITEM_EXCEPTIONS | PDS |
| MSC_ITEM_HIERARCHY_MV | Special ** |
| MSC_ITEM_SUBSTITUTES | Both |
| MSC_JOB_OPERATIONS | Both |
| MSC_JOB_OPERATION_NETWORKS | Both |
| MSC_JOB_OP_RESOURCES | Both |
| MSC_JOB_OP_RES_INSTANCES | Both |
| MSC_JOB_REQUIREMENT_OPS | Both |
| MSC_LONG_TEXT | ODS |
| MSC_NET_RESOURCE_AVAIL | Both |
| MSC_NET_RES_INST_AVAIL | Both |
| MSC_OPERATION_RESOURCES | Both |
| MSC_OPERATION_RESOURCE_SEQS | Both |
| MSC_ORG_AGGR_IBUC | ODS |
| MSC_ORP_MATERIAL_PLANS | PDS |
| MSC_ORP_RESOURCE_PLANS | PDS |
| MSC_PART_DEMANDS | PDS |
| MSC_PART_PEGGING | PDS |
| MSC_PART_SUPPLIES | PDS |
| MSC_PLAN_CONSTRAINTS | PDS |
| MSC_PQ_RESULTS | PDS |
| MSC_REGIONS | ODS |
| MSC_REGION_LOCATIONS | ODS |
| MSC_RESOURCE_HIERARCHY_MV | Special ** |
| MSC_RESOURCE_INSTANCE_REQS | Both |
| MSC_RESOURCE_REQUIREMENTS | Both |
| MSC_ROUTINGS | Both |
| MSC_ROUTING_OPERATIONS | Both |
| MSC_RP_CTB_DONOR_COMPONENTS | PDS |
| MSC_RP_CTB_ORDER_COMPONENTS | PDS |
| MSC_RP_ITEM_UPDATES | PDS |
| MSC_RP_KPI | PDS |
| MSC_RP_RELEASED_ORDERS | PDS |
| MSC_SALES_ORDERS | ODS |
| MSC_SHORT_TEXT | ODS |
| MSC_SINGLE_LVL_PEG | PDS |
| MSC_SRP_ITEM_EXCEPTIONS | PDS |
| MSC_SUPPLIER_REQUIREMENTS | PDS |
| MSC_SUPPLIES | Both |
| MSC_SYSTEM_ITEMS | Both |
| MSC_VISITS | ODS |
| MSC_WORK_BREAKDOWN_STRUCT | ODS |
| MSC_WO_ATTRIBUTES | ODS |
| MSC_WO_MILESTONES | ODS |
| MSC_WO_OPERATION_REL | ODS |
| MSC_WO_SUB_COMP | Both |
| MSC_WO_TASK_HIERARCHY | ODS |
| MSC_ZONE_REGIONS | ODS |
Special ** see MV partitioned tables below for more information
There are partitions for the MSC_ST% staging tables that are controlled by the entries made in the Instances form from the Advanced Planning Administrator responsibility. The MSC_ST% staging tables are used in data collections and inserted when the Planning Data Pull process pulls data from the EBS source tables/views.
The partitions DEF and LEG are default partitions created at install. When you enter a line in the instances form, then triggers are used to create a specific partition for that ODS Instance partition
Example:
When a single ODS partition is created, then the list of partitions for a single table would appear as follows if the ODS instance partition is 21:
SELECT TABLE_NAME, PARTITION_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME like 'MSC_ST_SYSTEM_ITEMS'
| Table Name | Partition Name |
|---|---|
| MSC_ST_SYSTEM_ITEMS | ST_SYSTEM_ITEMS_LEG |
| MSC_ST_SYSTEM_ITEMS | ST_SYSTEM_ITEMS_DEF |
| MSC_ST_SYSTEM_ITEMS | SYSTEM_ITEMS_21 |
Note: The instance partition 21 does not contain the ST prefix for the partition name. Also, these staging tables do not have __ (double underscore) like the base table for collected data and only use a _ (single underscore).
Complete listing:
| TABLE NAMES FOR PARTITIONED STAGING TABLES |
|---|
| MSC_ST_BOMS |
| MSC_ST_BOM_COMPONENTS |
| MSC_ST_CALENDAR_DATES |
| MSC_ST_DELIVERY_DETAILS |
| MSC_ST_DEMANDS |
| MSC_ST_DEPARTMENT_RESOURCES |
| MSC_ST_DOC_ATTACHMENTS |
| MSC_ST_EAM_ACT_ASSOCIATIONS |
| MSC_ST_EAM_ASSET_EQUIP_DTLS |
| MSC_ST_ITEM_CATEGORIES |
| MSC_ST_ITEM_SUBSTITUTES |
| MSC_ST_JOB_OPERATIONS |
| MSC_ST_JOB_OP_RESOURCES |
| MSC_ST_JOB_REQUIREMENT_OPS |
| MSC_ST_LONG_TEXT |
| MSC_ST_NET_RESOURCE_AVAIL |
| MSC_ST_NET_RES_INST_AVAIL |
| MSC_ST_OPERATION_COMPONENTS |
| MSC_ST_OPERATION_RESOURCES |
| MSC_ST_OPERATION_RESOURCE_SEQS |
| MSC_ST_PROCESS_EFFECTIVITY |
| MSC_ST_REGIONS |
| MSC_ST_REGION_LOCATIONS |
| MSC_ST_RESOURCE_INSTANCE_REQS |
| MSC_ST_RESOURCE_REQUIREMENTS |
| MSC_ST_ROUTING_OPERATIONS |
| MSC_ST_SALES_ORDERS |
| MSC_ST_SHIFT_DATES |
| MSC_ST_SHORT_TEXT |
| MSC_ST_SOURCING_HISTORY |
| MSC_ST_SOURCING_RULES |
| MSC_ST_SUPPLIES |
| MSC_ST_SYSTEM_ITEMS |
| MSC_ST_TRADING_PARTNERS |
| MSC_ST_TRADING_PARTNER_SITES |
| MSC_ST_VISITS |
| MSC_ST_WORK_BREAKDOWN_STRUCT |
| MSC_ST_WO_ATTRIBUTES |
| MSC_ST_WO_MILESTONES |
| MSC_ST_WO_OPERATION_REL |
| MSC_ST_WO_SUB_COMP |
| MSC_ST_WO_TASK_HIERARCHY |
| MSC_ST_ZN_AGGR_IBUC |
| MSC_ST_ZONE_REGIONS |
Two tables are created as Materialized Views: MSC_RESOURCE_HIERARCHY_MV and MSC_ITEM_HIERARCHY_MV. They do not follow the standard partition creation rules.
Important: These tables should never be manipulated. They are not removed or changed by any process such as Drop Partition or Create APS Partition requests. SQL #8 -- Drop Plan Partitions drops plan partitions but does not drop these partitions. When using SQL #7 -- Delete All Tables Where the SR_INSTANCE_ID Column Appears and SQL #11 -- Update All Tables Where the SR_INSTANCE_ID Column Appears, these tables error in the script. This is not a problem and can be ignored. When the views are reinitialized, the proper information is written to the tables. You can use the SQL below to view information about these tables.
Select table_name, partition_name
From all_tab_partitions
Where table_name in ('MSC_ITEM_HIERARCHY_MV','MSC_RESOURCE_HIERARCHY_MV')
order by table_name
The installation of VCP creates the following partitions for these tables. These are the only tables where 0 partition may have data. This is expected if the VCP Plan partition_number is >31; Partition_1 is populated for plan partitions >31 and <61; Partition_2 is used for >61.
| Table Name | Partition Name |
|---|---|
| MSC_ITEM_HIERARCHY_MV | ITEM_HIERARCHY_0 |
| MSC_ITEM_HIERARCHY_MV | ITEM_HIERARCHY_1 |
| MSC_ITEM_HIERARCHY_MV | ITEM_HIERARCHY_2 |
| MSC_RESOURCE_HIERARCHY_MV | RES_HIERARCHY_0 |
| MSC_RESOURCE_HIERARCHY_MV | RES_HIERARCHY_1 |
| MSC_RESOURCE_HIERARCHY_MV | RES_HIERARCHY_2 |
Advanced Planning Command Center also uses partitioned tables. APCC handles its partitioning separately and automatically. These tables are NOT part of standard data collections and ASCP, IO, DRP or SPP plans. They are not affected by Drop Partition or Create APS Partitions requests. There are also APCC-specific MSC_ST%F staging tables. These are for APCC to use when moving data from PDS plan output to the APCC tables. They are not used in standard data collections.
| TABLE_NAMES FOR APCC PLAN TABLES AND APCC STAGING TABLES |
| MSC_BUDGETS_F |
| MSC_COSTS_F |
| MSC_DEMANDS_CUM_F |
| MSC_DEMANDS_F |
| MSC_DEMANTRA_F |
| MSC_EXCEPTIONS_F |
| MSC_ITEMS_F |
| MSC_ITEM_INVENTORY_F |
| MSC_ITEM_ORDERS_F |
| MSC_ITEM_WIPS_F |
| MSC_RESOURCES_CUM_F |
| MSC_RESOURCES_F |
| MSC_SUPPLIERS_F |
| MSC_SUPPLIES_F |
| MSC_ST_BUDGETS_F |
| MSC_ST_COSTS_F |
| MSC_ST_DEMANDS_CUM_F |
| MSC_ST_DEMANDS_F |
| MSC_ST_DEMANTRA_F |
| MSC_ST_DEMANTRA_ODS_F |
| MSC_ST_EXCEPTIONS_F |
| MSC_ST_ITEMS_F |
| MSC_ST_ITEM_INVENTORY_F |
| MSC_ST_ITEM_ORDERS_F |
| MSC_ST_ITEM_WIPS_F |
| MSC_ST_RESOURCES_CUM_F |
| MSC_ST_RESOURCES_F |
| MSC_ST_SUPPLIERS_F |
| MSC_ST_SUPPLIES_F |