Advanced Supply Chain Planning

Agenda

Module Objective Memory Based Planning (MBP) Overview Ilog Technologies Supply Chain Optimizer High Level Scheduler Memory Based Planner (MBP) Memory Based Planner Database Maintenance Simulation Planning

Memory Based Planner

Oracle Applications 10.7 / 11.x / 11i Application Data Repository
APS Snapshots APS Views

Oracle Applications 10.7 / 11.x / 11i Application Data Repository

APS Snapshots APS Views

Advanced Planning and Scheduling

Global Available to Promise

Staging Tables Collection Work Bench

Key Transformation

Planning Data Store Planners Work Bench

Operational Data Store

Global Available to Promise Simulation Planning

$APPLCSF/out/ASCII Flat Files

Advanced Supply Chain Planning Planners Work ASCII Flat Files Supply Chain Optimizer High Level Scheduler Bench updates

Copyright Oracle Corporation, 2000. All rights reserved.

ASCP Processing
Planning Snapshot:
ODS and/or PDS writes to ASCII flat files. The practice of deleting these ASCII flat files is

rather common to save space. Please note that if these files are not present for a specific plan, simulation re-plans are not possible. The MBP would have to be launched again and new ASCII flat files would have to be created.
The process uses $APPLCSF, if set. If $APPLCSF is not set, $MSC_TOP/out is used.

They can be identified by their extensions of .ctl, .dis, .dat and .bad.
MBP Performs loop checking/ low level code computations while MBP loads data from flat

files

Memory Based Planner:

Load Flat files to MBP trees. MBP is invoked to prepare data for SCO Invoke SCO Invoke MBP for netting purposes Invoke HLS Write output to ascii flat files Load data to PDS, possibly consuming a partition

If the profile option Direct Load is set to yes, all SQL*Loader issues apply.

Oracle Advanced Supply Chain Planning Engine

Oracle Memory Based Planner (MBP)

Ilog Supply Chain Optimizer (SCO)

Ilog High Level Scheduler (HLS)

Advanced Supply Chain Planner (ASCP)

There are three components to the Advanced Supply Chain Planning Engine (ASCP). These three components share a common data model and are linked together in a single executable named MSONEW. Data conversion during processing does not need to occur, thus performance of the ASCP plan is not affected when data is transferred between Ilog modules and Oracle's Memory Based Planner (MBP).

The three modules perform specific functions critical in producing an MRP plan that can be produced within the manufacturing organization. Virtually every plan will have exceptions. From the inability to secure raw materials to resource shortages. ASCP recognizes these shortcomings and produces resource and material constrained plans that are optimized to meet business objectives while alerting the user to exceptions that exist while producing to the plan. Plans may also be optimized to meet business objectives such as On Time Delivery. ASCP Components: SCO - suggest planned orders after considering all constraints MBP - netting and planned order placement HLS - allocation and optimization of resource usage Advanced Supply Chain Planning (ASCP) operates in three modes; Unconstrained, Constrained, Optimized. An unconstrained plan offers the ability to execute ASCP without respect to material or capacity constraints. The MBP will assume infinite capacity and infinite supply. This is the logic offered previous to 11i. A constraint-based scheduling engine will ensure the plan is feasible and respects all of given material or resource constraints. A constrained plan respects the realistic limitations of the defined supply chain. Every entity in the supply chain, that is defined, will be exploited to the extent that it offers resources or materials required to meet the given demand. Materials and resources limit the maximum production output of the manufacturing plant. Alternate suppliers, resources and substitute components offer greater flexibility when key production participants are taxed beyond ability or availability. An optimized plan manipulates and delivers ASCP output that best matches the stated plan objectives. Objectives include maximum inventory turns, maximize on time delivery, minimize inventory costs, and maximize overall plan profit. ASCP simultaneously plans material and capacity while considering the weighted objectives of optimization settings. Constraints must be used if optimization if desired. If the plan is setup to maximize on time delivery, the ASCP engine will not violate available resources or stated supply to meet on time deliveries. Exception messages relating to late delivery are still likely given the available materials and resources.

ASCP creates infinite, constrained and/or optimized MRP plans that are coordinated for each organization defined in the plan.

ILOG
Only Ilog supports multiple, complementary optimization technologies covering the entire breadth of industry problems, from high-level planning to tactical operations. Based in France, Ilog has excelled at delivering programmatic solutions that resolve complex problems. Additional information concerning Ilog may be obtained at www.ilog.com.

Memory Based Planner - MSONEW

Oracle Advanced Supply Chain Planning Engine

Oracle Memory Based Planner (MBP)

Ilog Supply Chain Optimizer (SCO)

Ilog High Level Scheduler (HLS)

Third Generation of Material Requirements Planning in memory. What does Memory Based actually mean? The three executables, SCO, MBP, HLS make a unique executable, sharing the same memory. The advantages are: All executables and data are in memory, so critical I/o bottlenecks are avoided We use the existing MBP data model thus reducing the possibilities of new bugs Process lifecycle is shortened

ILOG CPLEX Supply Chain Optimizer - MSONEW

Oracle Advanced Supply Chain Planning Engine

Oracle Memory Based Planner (MBP)

Ilog Supply Chain Optimizer (SCO)

Ilog High Level Scheduler (HLS

C library used in solving large-scale optimization problems in the area of planning and resource

allocation.
Cplex uses techniques from linear programming and mixed integer programming. Constraint programming uses information contained in the problem to prune the search space,

in order to more rapidly identify feasible solutions. It is ideally suited for operational problems, which require fast, feasible answers. An MRP plan is good example of this. Given stated demand, constrained time, limited materials and resources, what is the proper order of manufacturing and what is the most efficient use of resources.
It is important to note that as you adjust Penalty Factors or Plan Objectives, the volume of

exception messages created will fluctuate. Resources considered in the problem area include: Transportation Supplier Capacity Material On Hand Available Resources

Supply Chain Optimizer (SCO) Overview

D e m a n d S u p p l y

Possible plans produced during SCO passes

Given Constraints

x x x x
Optimized, best fit plan honed to user settings

ths on fM tof :Cu RP M To

Available Resources

Tim e

Given Constraints

The SCO makes several passes at the planning problem eventually making the determination that further analysis would provide diminished results. As indicated in the diagram above, X represents attempts at problem resolution by the SCO.
Oracle ASCP solves the problem of planning the production of ordered products, given finite

capacity while considering practical industrial constraints.

Given an amount of TIME, as indicated by the setting of the MRP:Cutoff Months profile

option.

 Allocated with available RESOURCES as made available from the source Application Data

Store (ADS) instances.

Assigned a number of TASKS contained within demand, supply, netting, etc. Oracle ASCP stops the processing of SCO when the results produced are within an

acceptable tolerance range when compared to a best previous plan in the current session. The ASCP engine must take over at some point otherwise the SCO engine would execute until the results were as near perfection as possible.

High Level Scheduler - MSONEW

Oracle Advanced Supply Chain Planning Engine

Oracle Memory Based Planner (MBP)

Ilog Supply Chain Optimizer (SCO)

Ilog High Level Scheduler (HLS)

ILOG solver and optimization technology optimizes plans to strategic objectives. Incorporated with third-generation memory based planning, the High Level Scheduler (HLS) produces optimal resource usage decisions aiding in the delivery of production and distribution plans for each organization's resources. The ILOG Solver or High Level Scheduler is a C++ library for solving problems in planning, allocation, and optimization exploiting constraint programming and object oriented programming. The most efficient use of resources is an example of this type of problem. Given a proposed schedule of resource utilization, fine tune and hone the resource usage by grouping like activities, like products, comparing costs, demand dates and resource availability.

High Level Scheduler (HLS) Overview

Resource Utilization Before HLS

Day 1

8am

9am

10am

11am

12pm

1pm

2pm

3pm

4pm

5pm

Day 2

8am

9am

10am

11am

12pm

1pm

2pm

3pm

4pm

5pm

Day 3

8am

9am

10am

11am

12pm

1pm

2pm

3pm

4pm

5pm

Day 4

8am

9am

10am

11am

12pm

1pm

2pm

3pm

4pm

5pm

Day 5

8am

9am

10am

11am

12pm

1pm

2pm

3pm

4pm

5pm

Resource Utilization Before HLS

MBP produces planned orders with a start date that is indifferent to resource utilization concerns. Planned order start dates can leave a resource schedule similar to the schedule above. Above, the resource is insufficiently utilized as indicated by the gray shaded boxes. The white boxes indicate times when the resource is not being scheduled to fulfill a planned order.

High Level Scheduler (HLS) Overview

Resource Utilization After HLS

Day 1

8am

9am

10am 11am

12pm 1pm

2pm

3pm

4pm

5pm

Day 2

8am

9am

10am 11am

12pm 1pm

2pm

3pm

4pm

5pm

Day 3

8am

9am

10am

11am

12pm 1pm

2pm

3pm

4pm

5pm

Day 4

8am 8am

9am

10am 11am

12pm 1pm

2pm

3pm

4pm

5pm

Day 5

8am

9am

10am

11am

12pm 1pm

2pm

3pm

4pm

5pm

Resource Utilization After HLS

After the HLS finishes smoothing resource utilization, there may be resource downtime due to supply chain lags, resource maintenance and user defined plan objectives and penalties. The above graph indicates that HLS has utilized the resource to the best of its ability leaving few times of resource utilization lags. The results of HLS processing will change the planned order start date prior to the plan results being delivered to the end user. HLS Data Files If you are experiencing problems with the HLS, you may need the HLS data files. As data is unloaded from the MBP, data files are prepared that are loaded into HLS. These data files can be captured by executing the following commands:

It is highly recommended that an extraction of MBP data files be supplied to Development before discussing MSONEW/HLS behavior or MSONEW/HLS errors. Before proceeding, consider attempting the Advanced Supply Chain Planner Memory Based Planner after relinking the MSONEW executable: Adrelink.sh should be at version 115.58 or higher. Apply patch 1497013 to bring adrelink,sh up to this version. This patch is included in AD-D 1627493 and above and in 11i.AU.C 1622303 and above. The command to relink is as follows: issue adrelink.sh force=y "mso MSONEW" Extracting MBP and HLS debug and data files from the ASCP MSONEW executables: 1) Determine the unix shell being used: $ echo $SHELL /bin/ksh or $ env | grep SHELL SHELL=/bin/ksh 2) Depending upon the shell being used, set the FDWHOAMI variable: ksh (korne shell) $ export FDWHOAMI=0,704,20454 sh (bourne shell) $ FDWHOAMI=0,704,20454 $ export FDWHOAMI csh (cshell) setenv FDWHOAMI 0,704,20454 Verify that FDWHOAMI is set by: $ echo $FDWHOAMI 3) Make a directory that will hold the data files: $ mkdir data under $MSC_TOP. Ensure that space and privileges are adequate.

4) Attain the plan_id: The plan id may be obtained either from the planner work bench by using the drop down menu: Help/Diagnostics/Examine. Then look for plan_id. Alternatively, execute the following query from sqlplus: select plan_id from msc_plans where compile_designator='&plan_name'; 5) $MSO_TOP/bin/MSONEW <dbuser/passwd@db> 0 Y -d <plan_id> db is the database sid or instance 6) After the Memory Based Planner - MBP process completes, tar the files in the $MSC_TOP/data directory. - cd $MSC_TOP/data - tar -cuf data.tar data 7) Upload the tar file and log files from the MBP process to Metalink. * NT instructions will be available via Metalink.

Load all planning data from files into data structures in memory. (C - Function)

Run MBP and flush output data back to Memory. (C - Function)

if Supply Chain Optimizer Module is invoked: (C++ - Function). Run SCO and Output results to the common C-data structures

Plan problem in memory and update data structures with output results. MBP massages data before HLS is invoked

(C++ - Function). Run HLS and output results to the common C-data structures

Write data from Oracle Temporary Tables to ASCII Flat Files

Inside the Memory Based Planner

Loop checking is done in the snapshot as well as low level computations. Loop checking is done for WIP, Sourcing and BOM. In Unix, raise your stack size if looping becomes and issue.
The processing flow of MSONEW is new to APS. With the introduction of ILOG libraries,

additional data manipulation must take place. As we pass supply/demand information to ILOG and receive possible plans from ILOG, additional adjustments need to be made to the planned orders, time fences and dates.
The three sequential modules, discussed above, make ASCP. These modules, combined

together, make a unique executable, sharing the same memory. This, in memory data model, is the existing C MBP data model currently used in 10.7 and 11.x. The difference is that C++ data wrappers are added to the data to make it presentable to the ILOG modules.

The MBP is an Application Object Library (AOL) concurrent program called MSONEW. MBP will cache data objects within your APS server memory. MBP performs netting and flushes the results into ASCII flat files. Finally loading these changes into PDS NOTE: MSONEW is launched when Constraints or Optimization is used or licensed. MSCNEW is used when the constraint option is not chosen or installed.

Completion of ASCP When Using Plan Partitions

MSONEW Completes Processing

Planning Marks PDS Partition as Unusable

Unusable PDS Partition is Truncated

ASCII Flat Files Swapped into Unusable PDS Partition

Unusable PDS Partition is Truncated

ASCII Flat files produced by the MBP are deleted after being loaded into the PDS.

While the data is swapped from the Oracle temporary tables into the PDS data store, the

active partition is marked as unusable while the swap occurs.

This leaves remaining plans, residing in their respective plan partitions, active and available to

users via the Planner's Workbench. However, this functionality will not allow for the concurrent execution of multiple MBPs at the same time.

 Once the data is swapped from memory into the correct plan partition, the partition is

marked as active, execution occurs as before MBP ran.

If the ASCP process fails during the truncation and swapping process, the local partition

indexes could be left marked as unusable. Please see the section regarding partitions to enable the partition indexes.
If MSC:Share Plan Partitions is set to YES, the above functionality is not used. The MBP

will have to delete the MBP data from the prior successful MBP for the specific plan.

Load Simulation Changes from PDS

Applied = no and status = 0 This is done for each data that is changed via Planners Workbench

Load ASCII flat files from previous ASCP execution

Load data into MBP afresh

Perform all necessary MBP/SCO/HLS data manipulations Write ASCII flat files Update PDS data store Erase PDS ASCII Flat Files
Copyright Oracle Corporation, 2000. All rights reserved.

Simulation Planning
The five data groups that can be manipulated for simulation replan purposes are: Supply, Demand, Supplier Capacity, Resource Requirements, Resource Availability

Simulation Change Detection

Simulation changes are changes affected by the user using the PWB. These changes are saved by the PWB with applied = N, or 2, and status =0. These attributes are used to identify and load these changes back into MBP. Data changed by the workbench is written into the PDS. SCO and HLS may decide to plan for assemblies and/or components that have been affected by user changes introduced via the Planners Workbench. For example, adding a new resource would trigger additional replanning activities.

Simulation Planning
This SQL will reveal the rows in msc_demands that have been changed within the Planner's Workbench while preparing to execute a simulation plan. select SCHEDULE_DESIGNATOR_ID sch_desg, PLAN_ID, INVENTORY_ITEM_ID, SR_INSTANCE_ID, APPLIED from msc_demands where applied = 2; The above select would extract data from the Planning Data Store (PDS).

MSC_FILES
The MSC_FILES table is used to store the list of files which are used in the generation of an MPS, MRP or DRP plan. Data in this table is updated each time a plan is run. The FILE_TYPE column contains predefined values that are used during a planning run. No information is available concerning the file_type code meanings. The FILE_NAME column contains the name of the data file generated during the last plan run. This information may prove useful when saving data files for simulation planning purposes. Based upon the compile_designator found within msc_plans, the query below joins the plan_id of msc_plans to msc_files to retrieve the .dat files created during the planning run. These are the data files that are required for simulation planning purposes. select substr(file_name,1,50), file_creation_date from msc_files a, msc_plans b where b.compile_designator = '&my_plan' and a.plan_id = b.plan_id;

ASCP Performance
Performance problems are easy to detect, but the root cause is often difficult to diagnose. If you believe the performance of ASCP unacceptably slow or if you've noticed a degradation of performance over time, these general tips may improve your performance.

PCTFREE:
Make sure the database objects in the MSC schema have a PCTFREE of zero. Pctused and Pctfree determine the usage of Oracle blocks for row storage. Pctfree instructs the Oracle RDBMS to leave a percentage of the row length * Pctfree available for row expansion. Pctused instructs the Oracle RDBMS to begin using the same Oracle block for another row after row length * pctused is used. Pctused is not is important as pctfree. Additionally, Storage clauses control the spread of data within Oracle blocks. At a number of sites the major tuning act we have accomplished has been to improve the blocking factor on an otherwise fixed I/O bound application. Improper blocking and a pctfree > 0 can have a profound effect on performance. When building your indexes consider setting the pctfree to zero. If the table(s) indexed are not subject to growth, setting the pctfree may improve disk access. Set the pctfree to a lower number if the table is expected to grow. Since data rows within the PDS does not expand in size, instructing the Oracle RDBMS to fill each row, leaving zero bytes free for row expansion, is a logical approach.

Analyze (Gather Schema Statistics)

11i uses the Cost Based Optimizer (CBO). Your init.ora should reflect this change. It is recommended that you set this to CHOOSE or FIRST_ROWS. FIRST_ROWS simply returns the first rows from a data set as extracted from the RDBMS by SQL. This tends to give the appearance of improved performance, as the user does not have to wait for the entire data set to be extracted before being presented with the set. Analyze should be performed on a regular basis according to your resource usage. Obviously a shop where ASCP is ran more often would require more frequent execution of analyze. It is strongly recommended that you use the Oracle supplied utility for analyzing. $MSC_TOP/sql/MSCSTAT.sql will analyze the MSC schema. Alternatively, submit the concurrent program 'Gather Schema Statistics' for MSC.

Analyze each partition separately using the following: -- Gather ASCP Table Partition Statistics --- It is recommended that this form of analyze be executed after every -- Complete refresh, after a partial refresh that has added or deleted -- significant amounts of data or after an MBP plan has been executed. -- This script should be executed from SQL*Plus if executing Gather -- Schema Statistics for ALL or MSC has not enhanced performance. --------

PROMPT TABLE MSC_COMPONENT_SUBSTITUTES exec fnd_stats.gather_table_stats('MSC','MSC_COMPONENT_SUBSTITUTES',granularity=>'A LL'); PROMPT TABLE MSC_PLANS exec fnd_stats.gather_table_stats('MSC','MSC_PLANS',granularity=>'ALL'); PROMPT TABLE MSC_PLAN_ORGANIZATIONS exec fnd_stats.gather_table_stats('MSC','MSC_PLAN_ORGANIZATIONS',granularity=>'ALL'); PROMPT TABLE MSC_SYSTEM_ITEMS exec fnd_stats.gather_table_stats('MSC','MSC_SYSTEM_ITEMS',granularity=>'ALL'); PROMPT TABLE MSC_BOMS exec fnd_stats.gather_table_stats('MSC','MSC_BOMS',granularity=>'ALL'); PROMPT TABLE MSC_BOM_COMPONENTS exec fnd_stats.gather_table_stats('MSC','MSC_BOM_COMPONENTS',granularity=>'ALL'); PROMPT TABLE MSC_DEMANDS exec fnd_stats.gather_table_stats('MSC','MSC_DEMANDS',granularity=>'ALL'); PROMPT TABLE MSC_SUPPLIES exec fnd_stats.gather_table_stats('MSC','MSC_SUPPLIES',granularity=>'ALL'); PROMPT TABLE MSC_EXCEPTION_DETAILS exec fnd_stats.gather_table_stats('MSC','MSC_EXCEPTION_DETAILS',granularity=>'ALL'); PROMPT TABLE MSC_FULL_PEGGING exec fnd_stats.gather_table_stats('MSC','MSC_FULL_PEGGING',granularity=>'ALL');

PROMPT TABLE MSC_ITEM_CATEGORIES exec fnd_stats.gather_table_stats('MSC','MSC_ITEM_CATEGORIES',granularity=>'ALL'); PROMPT TABLE MSC_ITEM_EXCEPTIONS exec fnd_stats.gather_table_stats('MSC','MSC_ITEM_EXCEPTIONS',granularity=>'ALL'); PROMPT TABLE MSC_NET_RESOURCE_AVAIL exec fnd_stats.gather_table_stats('MSC','MSC_NET_RESOURCE_AVAIL',granularity=>'ALL'); PROMPT TABLE MSC_OPERATION_RESOURCES exec fnd_stats.gather_table_stats('MSC','MSC_OPERATION_RESOURCES',granularity=>'ALL') ; PROMPT TABLE MSC_OPERATION_RESOURCE_SEQS exec fnd_stats.gather_table_stats('MSC','MSC_OPERATION_RESOURCE_SEQS',granularity=>' ALL'); PROMPT TABLE MSC_RESOURCE_REQUIREMENTS exec fnd_stats.gather_table_stats('MSC','MSC_RESOURCE_REQUIREMENTS',granularity=>'A LL'); PROMPT TABLE MSC_ROUTINGS exec fnd_stats.gather_table_stats('MSC','MSC_ROUTINGS',granularity=>'ALL'); PROMPT TABLE MSC_ROUTING_OPERATIONS exec fnd_stats.gather_table_stats('MSC','MSC_ROUTING_OPERATIONS',granularity=>'ALL'); PROMPT TABLE MSC_SALES_ORDERS exec fnd_stats.gather_table_stats('MSC','MSC_SALES_ORDERS',granularity=>'ALL');

PCTINCREASE
When implementing the ASCP and APS suite, account for the pct increase for each database object and the default settings for the tablespace. Newly created database objects that lack a storage clause inherit the storage clause default from the tablespace in which the database object is created. For ASCP processing, Oracle recommends a pctincrease of 1. This will avoid many of the database errors that are associated with a higher pctincrease. Use the following SQL to determine pctincrease changes. This script will identify database objects that require pctincrease adjustment. select 'alter '||segment_type||' '||owner||'.'||segment_name||' storage(pctincrease 0);' from dba_segments where pct_increase > 0 and (segment_type = 'TABLE' or segment_type = 'INDEX') and owner in (select oracle_username from applsys.fnd_oracle_userid); The second script is for the 11i MSC schema, i.e., MSC. select 'alter '||segment_type||' '||owner||'.'||segment_name||' storage(pctincrease 0);' from dba_segments where pct_increase > 0 and (segment_type = 'TABLE' or segment_type = 'INDEX') and owner = 'MSC';

ASCP and Memory

As ASCP utilizes memory, the process may experience a memory shortage. This program

identifies the max SWAP area (memory) limit. At the shell prompt type: ulimt -a to identify Data swap area. This is a Unix parameter, which correlates to the Maxdsize parameter. Compile this program, CC -o <<file name >>, then execute the compiled file to find hardware memory limit. Verify that apps is using the same max memory allocated. If not do the following:

Modify the $AD_TOP/bin/adrelink.sh file. Under HP9000 settings is a variable called CPP_LDFLAGS. Default is set to: CPP_LDFLAGS='-Wl,-a,default,+s -L$(LIBHOME)' Change it to: CPP_LDFLAGS='-N -Wl,-a,default,+s -L$(LIBHOME)' Then relink the library with the following command: adrelink.sh force=Y ranlib=Y "mso MSONEW" Then restart the plan and check the memory used by ASCP. #include <stdlib.h> main(int argc, char **argv) { size_m chunk; char *pointer; long allocated; if (argc > 1) chunk = (size_m) atoi(argv[1])*1024; else chunk = 1024; for (allocated = 1; pointer = (char *)malloc(chunk); allocated++) ; printf("alocated %d\n", allocated*chunk); } /* end of program */

MAXEXTENTS
This script will identify the database objects that require max extent adjustments. While we are setting the maxextents to 4000, your operating system may allow for a higher setting. Of course, after your first complete iterations of the data collection and execution of the Advanced Supply Chain Planner, consider resizing the MSC schema according to your specific needs.

The first script is for the 11i applications schema, i.e., wip, mrp, inv, etc. set lines 130 set pages 0 set feedback off set heading off set termout off set verify off spo max_extent.sql select 'spo max_extent' from sys.dual; select 'set echo on' from sys.dual; select 'set term off' from sys.dual; select 'alter '||segment_type||' '||owner||'.'||segment_name||' storage(maxextents 4000);' from dba_segments where max_extents < 51 and (segment_type='TABLE' or segment_type='INDEX') and owner in (select oracle_username from applsys.fnd_oracle_userid); select 'set echo off' from sys.dual; select 'spo off' from sys.dual; spo off The second script is for the 11i MSC schema, i.e., MSC. select 'alter '||segment_type||' '||owner||'.'||segment_name||' storage(maxextents 4000);' from dba_segments where max_extents < 51 and (segment_type = 'TABLE' or segment_type = 'INDEX') and owner = 'MSC';