How To Set Up Data Collection 3w6n5s

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY Presented by Core Manufacturing Product Team This document is for versions 11.5.5 and above with the latest Data Collections patch applied to the system. The purpose of this document is to serve as a quick setup guide for Data Collections. It is targeted for systems where Data Collections is being run to allow for ATP based on Collected Data OR For systems where ATP is not being used, but Data Collections needs to be run in order for Scheduling in Order Management to complete successfully. The same setups apply for systems where ATP will be based on Planning Data and the APS Suite is being used for Advanced Supply Chain Planning (AS). However, running of Data Collection for AS is accomplished from the AS Responsibility, NOT from the OM Responsibility. Additional setups are required before AS is fully implemented. The screen shots in this document reflect data collection parameters as they should look after the application of the latest data collections patch. See Note 223026.1 List of High Priority Patches for more information. For more information please go to Metalink and review the Advanced Planning and Scheduling Self Service Toolkit (APS SST) Navigate to the Top Tech docs, then to Advanced Planning and Scheduling. There are many important articles and links on this page - particularly under Planning Data Pull and Snapshots on the Source Instance topics. Be sure to see the Documentation link. the Oracle Global Order Promising Implementation and 's Guide. Also see the Oracle Advanced Planning and Scheduling Implementation and s Guide, Volumes 1 & 2 Release 11i which has more information on running Data Collections for AS.

Oracle Corporation – For Internal and Customer Use only Rev 2.0

June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY Section One Section Two Section Three Section Four Section Five Section Six

Terminology ________________________ pg. 2-3 DBA activities ______________________ pg. 4 - 6 System activities ________ pg. 6 - 11 Activities ______________________ pg. 12 - 15 Trouble Shooting ____________________ pg. 16 References __________________________ pg. 17

Summary of the Steps to Complete The explanations in the following pages attempt to thoroughly explain each area of activities needed to successfully configure and run Data Collections for ATP from Order Management. These steps are shown below in a distilled form as a quickstep guide for more advanced s. DBA Activities 1. Apply the latest Data Collections Cumulative Patch in Note 223026.1 Section IV 2. Run Create ATP Partitions concurrent request 3. Run Drop Plan Partitions concurrent request to drop the default installed Plan Partitions 1 through 5 for ATP based on Collected Data System Activities 1. Setup the profile options 2. Check and setup the Standard Manager Workshifts 3. Start the Planning Manager Activities 1. Setup the Instance 2. Select the Organizations 3. Run ATP Data Collection

Oracle Corporation© – For Internal and Customer Use only [email protected]

1

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY

Section One – Terminology Advanced Planning and Scheduling Suite - APS The APS Suite which can include Advanced Supply Chain Planning (AS), Global Order Promising (GOP), Oracle Demand Planning (ODP), Collaborative Planning ( - 11.5.8 and above), Transportation Planning (TP - 11.5.10 and above) Available to Promise – ATP Generally used to describe ATP based on Collected Data. Global Available to Promise – GATP Generally used to describe ATP/CTP based on Planning Data Capable to Promise – CTP Part of GATP – generally used to describe ATP based on Planning Data where Resource Availability and Capacity is used to determine the date a product can be produced/procured and available for shipment. Global Order Promising – GOP The Application name for ATP, CTP and GATP and part of the APS Suite Data Collections The process of collecting data from the ERP Source transactional instance (or schemas) and gathering data in a recognizable form in the APS Destination instance/schema. The Source ERP information is stored in the product schemas ( INV, OM, BOM, WIP, MRP, PO, etc.) and collected to the MSC Schema for ATP and AS. For Demand Planning, information is also collected to the MSD schema where the Demand Planning specific information is stored. This data collections process is run from the Demand Planning responsibility and requires additional setup. Centralized Configuration A Centralized Configuration means only one instance is involved. This is usually used for ATP based on Collected data. This means that the ERP Source data and the APS Destination data are stored in one instance. Decentralized Configuration A Decentralized Configuration means there are two instances involved. These two instances (ERP Source and APS Destination) speak to each other via database links. This will mean that the ERP Source and APS Destination instances are located on separate boxes. This configuration is generally used when all or part of the APS suite is being used.

Oracle Corporation© – For Internal and Customer Use only [email protected]

2

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY Order Management – OM The scheduling process in OM es data to the ATP processes which determine the availability of a product and return the Schedule Ship Date. This process is invoked even if you are not using ATP since this is the process that checks to determine the status of an item. Operation Data Store – ODS The Collected Data that is used for ATP based on Collected Data. This is the raw ERP Source data that is stored in the MSC Schema under the PLAN_ID = -1 This data is also used to feed an AS Plan. Planning Data Store – PDS Created when an Advanced Supply Chain plan is run and will be stored in the MSC Schema under a PLAN_ID > 0. This can be used for ATP/CTP based on Planning Data. Planning Data Pull – PDP First step in Data Collections – The information in the ERP Source is gathered to the MSC_ST% Staging tables from the Source schemas/tables using snapshots, views and direct query on the source tables. Planning ODS Load Second step in Data Collections – The information in the MSC_ST% Staging tables is ed to temporary tables, manipulated and validated, then loaded into the MSC% base tables. Each MSC_ST% table has a corresponding MSC% base table. (e.g. MSC_ST_SYSTEM_ITEMS – MSC_SYSTEM_ITEMS)

Oracle Corporation© – For Internal and Customer Use only [email protected]

3

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY

Section Two - DBA Activities Patch Application See Note 223026.1 - Section IV - Data Collections – Apply the latest Cumulative Collections patch listed under that heading. No other patches are needed for Data Collections. The steps below are optional and can be accomplished at a later date. However, they can be critical to the overall implementation and should be reviewed and considered when applicable. It is highly recommended that patch 2405219 from Section V be applied to fix an issue with Gather Schema Statistics for partitioned tables. This is a small 2-file fix with critical benefits for performance in the MSC schema. If GOP is being used then the latest ATP rollup patch listed under Section II should also be applied. If the AS is going to be used, then the latest AS Rollup patch that is listed in Section I should also be applied to the system. Demand Planning has a listing of patches under Section VII that may be required if this application is being installed for the system. Note: In order to perform Basic ATP it is not necessary to have APS installed. When Order Management is installed, but APS is not installed, then APS is installed by default as a ‘Shared’ application under the Product Code MSC in order to enable ATP based on Collected Data. SQL> select p.application_id, p.status, a.application_short_name from fnd_product_installations p, fnd_application a where a.application_id=p.application_id and a.application_short_name in ('msc') This should return Status = S when the APS Suite is not licensed. Other values are: I – Installed N – Not installed Create ATP Partitions This request needs to be run from the AS Responsibility.

Oracle Corporation© – For Internal and Customer Use only [email protected]

4

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY MSC Schema Setup The default installation of APS sets up some of the MSC tables as partitioned tables and installs default partitions. There are two types of partitions. Instance Partitions – These are for the data collected from the ERP Source instance/schemas. They are designated by a __ (double underscore) before the partition number Plan Partitions – These are for Planning Data created by running an AS Plan. They are designated by _ (single underscore) before the partition number. The SQL that will reveal how these partitioned tables are setup is: SQL> select table_name, partition_name, num_rows, sample_size, last_analyzed, global_stats, _stats from all_tab_partitions where table_name like 'msc%' order by substr(partition_name,instr(partition_name,'_',-1,1)+1); An example of a partitioned table default setup is TABLE_NAME PARTITION_NAME MSC_BOMS BOMS_0 – The Template partition MSC_BOMS BOMS_1 – A plan partition MSC_BOMS BOMS__1 – The Instance partition for ODS data MSC_BOMS BOMS_2 – The rest are more default plan partitions MSC_BOMS BOMS_3 MSC_BOMS BOMS_4 MSC_BOMS BOMS_5 Note: Underscore ZERO (_0) represents the Template partition and should NEVER be removed or manipulated in any fashion. Also see the following for assistance in maintaining the APS instance: Note 137293.1 - How Can I Manage MSC Partitions This note lists the partitioned tables in the MSC schema Note 174500.1 - ODS-Destination Maintenance and Cleanup when the Source Instance Changes

Oracle Corporation© – For Internal and Customer Use only [email protected]

5

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY FOR ALL INSTALLATIONS – ATP based on Collected Data or Full AS Planning. Dropping the unneeded plan partitions. There are database issues that can affect performance when partitions are empty. Therefore, any unused partition (except _0) should be removed from the system. This is accomplished using the Drop Plan Partitions concurrent request. Then the request needs to be run for partition_num 1 through 5. AGAIN, do not run this for partition_num 0 (zero). If necessary, enable the request by navigating to: System >Concurrent>Program>Define Then query on the Shortname ‘MSCDFPAR’ Then check the ‘Enabled’ Box and Save. This request should now be available from Advanced Supply Chain Planning or System responsibilities. FOR CUSTOMERS USING APS PLANNING There should not be any unused partitions in the system, so please consult with the s and leave only a minimum number on the system – maybe just 1 or 2 to start the implementation. If more plan partitions are required later in the implementation, run ‘Create APS Partitions’ with parameters ‘plan_partition count’ = 1 (or more if needed immediately) and ‘inst_partition count’ = 0 to create a new plan partition. If the business plans to set MSC: Shared Plan Partition = Yes, then please consult the s Guide for more information about this setup. Also see Note 137293.1 - How Can I Manage MSC Partitions The only reason to use ‘inst_partition count’ = 1 would be if the ODS data is to be collected from a new ERP Source instance. If using AS Planning, then the AS and Implementation Guide should be used to make sure all necessary setups are completed for a full implementation. Note 179522.1 has more information on Data Collections Parameters when running this process from AS Responsibility for AS Planning.

Oracle Corporation© – For Internal and Customer Use only [email protected]

6

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY

Section Three - System Activities 1. There are several profile options that must be setup for Data Collections. Most all the profile options are discussed in more detail in Appendix A of AS s Guide. Navigate Profile > System 1.1 INV:Capable to promise 1.1.1 Set the profile = “ATP Based on Collected Data” if AS is not installed or if the ATP will not be using AS planning data. 1.1.2 Set the profile = “ATP/CTP Based on Planning Data” if AS is installed and the ATP will be using AS planning data. 1.1.3 Save 1.1.4 With a Decentralized installation - this profile must be set at both the ERP Source and the APS Destination instances for ATP to function properly.

1.2 MSC: Purge Stg Tbl Cntrl 1.2.1 Options = Yes/No – Use YES to Truncate the staging table during data collection process. 1.2.2 When Data Collections will retrieve data from a single instance (most common use of this process), set to Yes to improve performance and conserve disk space, or as separate processes for multiple instances. Oracle Corporation© – For Internal and Customer Use only [email protected]

7

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY 1.2.2.1 When Data Collections will retrieve data from multiple instances simultaneously, set to No to avoid data loss. If you are collecting from multiple instances separately, then this can be set to YES to improve performace of the individual request sets for each instance. 1.3 MSC: Temp Index Extent Size (Bytes) 1.3.1 The recommended setting is 1000000 (1meg) and controls the size of the next extent when creating temporary tables while running collections or copy plan. 1.4 MSC: Temp Table Extent Size (Bytes) 1.4.1 The recommended setting is 10000000 (10 meg) and controls the size of the next extent when creating temporary tables while running collections or copy plan. 1.5 MSC: Sales Orders Offset Days 1.5.1 This is used for Forecast Consumption during AS Planning where a Forecast is used instead of an MDS for the Demand schedule of a plan. 1.5.2 As setting of NULL or Zero will collect all Sales Orders for 9999 days. 1.5.2.1 The recommended setting for ATP based on Collected Data is a small number (e.g. 1). 1.5.2.2 For ATP/CTP based on Planning Data – the business will need to decide the appropriate setting. This number can be small if the Sales Order consumption of the Forecast is performed during the MDS Load and then the MDS is collected from the ERP source and used for the Demand schedule in the AS Plan. 1.5.3 The smaller this number, the faster Data Collections can complete Sales Order loading. 1.6 MSC: Enable ATP Summary Mode 1.6.1 Enables summarization of supply and demand data following the data collection and plan launch processes. These tables are only for ATP based on Collected Data. Valid values are: Yes: Enable ATP Summary Mode No: Disable ATP Summary Mode If this profile =YES and INV:Capable to Promise = ATP/CTP based on Planning Data, then the request Load ATP Summary Tables will run at the end of Data Collections and will error. This can be ignored OR set the profile to No in order to avoid the error. 1.7 MSC: Source Setup Required 1.7.1 This Profile is set automatically by the application. When set to Yes, the Setup requests are run during Data Collections. These requests create all the Source ERP instance database objects that are needed for Collections to complete successfully. 1.7.2 If Data Collections is failing or a Data Collections Cumulative patch has been applied, then this profile should be set to Yes so that all the Setup requests will be run and can be checked for failure. 1.7.3 Review Note 207644.1 for more information on these Setup requests and the standard Data Collections requests that are submitted when running Data Collections. Oracle Corporation© – For Internal and Customer Use only [email protected]

8

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY 1.8 MRP: Purge Batch Size 1.8.1 Default is 25000. This setting is used for Data Collections only if MSC: Purge Stg Tbl Cntrl = No. Generally, there is no reason to change this profile from the default setting. 1.9 MSC: Degree of Parallelism for Index Creation 1.9.1 Defaults to 0. Controls the use of parallel workers in data collections. Specifies how many parallel workers to use when creating indexes on the temporary tables during the ODS Load. This can be increased if there are performance problems with the ODS Load and system resources can be allotted. 1.10 MSC: Collection Window for Trading Partner Changes (Days) 1.10.1 At this time, this Profile must be NULL for Collections to function properly 1.10.2 For Collections when using ATP and for when running AS Plans. 1.10.3 Defaults as NULL. Specifies the number of days backwards that changes in customer names, customer sites, vendors, and vendor sites are collected. If null, then all changes are collected. Used when collections for the trading partners entity is run in complete mode. 1.11 MRP:Purchasing By Revision 1.11.1 This profile option may NOT be NULL 1.11.2 Most customers will set this profile = No, unless they have already set this to Yes as for business reasons. 1.11.3 The Master Scheduling/MRP s Guide has more information about this profile option. 1.12 INV:Purchasing By Revision 1.12.1 This profile option may NOT be NULL 1.12.2 Most customers will set this profile = No, unless they have already set this to Yes as for business reasons. 1.12.3 The Inventory s Guide has more information about this profile option. 1.13 .MSC: Sourcing History Start Date Offset (in months) 1.13.1 For Collections when running an AS Plan. 1.13.2 Defaults as Null. Offsets the starting date of sourcing history calculations and is used in with collections parameters Purge Sourcing History = Yes and Recalculate Sourcing History = Yes. These settings delete, then calculate Sourcing History from the Start date determined by the profile option value (in months). 1.13.3 System performance can be degraded when there is a high value for this profile option and a high volume of source transaction data.

Oracle Corporation© – For Internal and Customer Use only [email protected]

9

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY 2. Set the Standard Manager 2.1 Navigate > Concurrent > Manager > Define 2.2 Query the Standard Manager 2.3 Click on the Work Shifts button 2.4 Set the number of processes = 18 – 25 depending on the needs of your s. A setting of less than 15 is unadvisable.

Oracle Corporation© – For Internal and Customer Use only [email protected]

10

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY 3. The Planning Manager must be started and complete at least one successful cycle on the ERP Source Instance in order for Data Collections to complete successfully. 3.1 Navigate to Material Planning>Setup>Planning Manager 3.2 Choose an organization –This only needs to be done once for the instance – not for each organization. 3.3 The default of 30 seconds is OK. 3.4 Click on the start button. 3.5 If this is the first time the Planning Manager has been started on this instance, and there is a lot of data on the system, then the first run may take an extended period of time. 3.6 For issues with the Planning Manager – see the Master Scheduling/MRP SST on Metalink for more information and troubleshooting.

Oracle Corporation© – For Internal and Customer Use only [email protected]

11

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY

Section Four – Activities 1. Set up the instances 1.1 Navigate Order Management > Scheduling > ATP and Scheduling Setup > Instances 1.2 Insert an instance code. This code is limited to three characters and should be meaningful. Consult with the DBA for the database SID name and make the instance code similar to it. 1.3 Select the Instance Type from the list of values. For most companies Discrete is the best selection. Only use Discrete and Process if using Oracle Process Manufacturing (OPM). 1.4 Select your Source ERP instance version. 11i is used with a centralized configuration. 1.5 Make sure the Enable checkbox is checked. It will default as checked. 1.6 Scroll to the right and populate the GMT difference. 1.7 Scroll to the right and populate the currency code. This code must be all capitals. 1.8 Save

Oracle Corporation© – For Internal and Customer Use only [email protected]

12

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY

Oracle Corporation© – For Internal and Customer Use only [email protected]

13

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY 2. Now click on the Organizations button and select your organizations 2.1 OR navigate Order Management > Scheduling > Instances > Organizations button 2.2 Select the MASTER organization from the LOV in the ORG field. 2.2.1 Enabled checkbox will default checked – confirm this setting. 2.3 Continue adding the organizations that need to have the data collected. This would include all inventory and distribution organizations. If you are not sure what organizations are correct, then run the following SQL. 2.3.1 SQL> select organization_code, organization_id from mtl_parameters where organization_id in (select distinct organization_id from mtl_system_items_b) 2.4 Save

Oracle Corporation© – For Internal and Customer Use only [email protected]

14

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY 3. Running Data Collections for ATP based on Collected Data 3.1 NAV: Order Management > Scheduling > ATP Data Collection 3.2 Place the cursor in the ATP Data Pull – Parameters field. 3.3 Click on the LOV for the instance. 3.4 The default number of workers is 3. If there is a lot of data to be collected, then it is recommended that the number of workers be increased to 4 or 5. Less than this could result in the data collection running long. 3.5 Collection Method will default to Complete Refresh. Use the default the first time you run data collections. The frequency and method type are business decisions. 3.6 Complete Refresh: Purges most of the existing ODS data in the MSC tables and replaces most all the data. This type of data collection takes the longest amount of time and should be run during non-peak hours. 3.7 Net Change Refresh: This option will only collect data that has been modified since the previous collection was run without deleting any data. There are limitations on the type of data that can be collected by Net Change refresh. See the GOP s Guide for more information. 3.7.1 NOTE: Page 2-21 of the Feb 2003 GOP s Guide states the following: Cancelled sales orders, purchase orders, requisitions, and WIP jobs are not be picked up by Net Change Refresh collections. This is not correct – These changes are collected by Net Change Refresh 3.8 Click OK 3.9 Click Submit and the parameters for Planning ODS Load will default 3.9.1 OR for large amounts or data – Click in the parameters field and increase the number of workers to increase the speed of the process 3.9.2 Then click OK and Submit the request.

Oracle Corporation© – For Internal and Customer Use only [email protected]

15

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY

Section Five – Trouble Shooting For any issues not covered in this section, please search MetaLink at http://metalink.oracle.com Then navigate to Top Tech Docs>Advanced Planning and Scheduling and review the Planning Data Pull sections and also the Documentation link for more complete information. 1. Data Collections request completes with an Error: 1.1 See Note 207644.1 for more information on the Data Collections requests and re-run the process with MSC: Source Setup Required = Yes 1.2 It may be necessary to run reset the MSC_APPS_INSTANCES. ST_STATUS field by running the Purge Staging Tables request. See Note 118168.1 for more information. 2 Data Collections completes with a Warning: 2.1 These can generally be ignored and will not cause any problems for ATP. 2.2 The warnings can be researched in Metalink for more information if desired. 2.3 These errors can be caused when an APS Destination instance has been cloned and a new ERP Source instance is being used. See Note 174500.1 if this is a possible cause. 3 ATP Error Message – unable to meet latest acceptable date There could be many causes for this error. Some examples include: 3.1 ATP Rule is not defined. If ATP Rule has been added after the item is assigned to an organization. Please ensure the Org Item and Master Item are updated with the ATP Rule. Or set ATP Rule at Organization. 3.2 ATP Rule parameter: Infinite supply set to: Total Lead Time; Cumulative mfg. lead-time; or Cumulative total lead-time. The lead-time values must be defined on item attribute. ATP will not recognize lead Time values left Null. Null values should be changed to the number zero. Please ensure that the values are updated at Organization Item. 3.3 Data Collections has not been run or has completed in error. 3.4 Review the GOP s Guide for more information on setups and examples of ATP. 4 Planning Data Pull errors with - Another Data Pull process is running. or Staging tables' data is not collected or Please submit the request - Planning ODS LOAD. Navigate to Advanced Supply Chain Planner > Other > Request > Submit new request > Single request > Planning Data Collection – Purge staging tables. Set Validation = NO and launch the request. Once it is complete, rerun the data collections 5 For performance issues: 5.1 Check the profile options – several of these will cause long running Data Collections. 5.2 Check that patch 2405219 is applied as recommended and then run Gather Schema Statistics for ALL schemas.

Oracle Corporation© – For Internal and Customer Use only [email protected]

16

Rev 2.0 - June 2003

HOW TO SET UP DATA COLLECTION FOR ORDER MANAGEMENT ATP AVAILABILITY

Section Six - References Global Order Promising Implementation and s Guide – this should be the primary resource for checking setups and understanding GOP/ATP. Oracle Advanced Planning and Scheduling Implementation and s Guide, Volumes 1 & 2 Release 11i Note 131767.1 - APS SST Advanced Planning and Scheduling Self Service Toolkit Note 223026.1 - List of High Priority Patches for OM ATP AS and ODP Note 207644.1 - Data Collections Fails - All Errors -First Diagnostic Steps Note 148584.1- How to Rebuild Source Snapshots Used in the Data Collection or Planning Data Pull MSDP Note 179522.1 - Data Collection Use and Parameters Note 118168.1 - Data Collection Errors - Log File has no Errors or Indicates Another Data Pull is Running or that ODS Load Must Be Run Note 137293.1 - How Can I Manage MSC Partitions Note 174500.1 - ODS-Destination Maintenance and Cleanup when the Source Instance Changes

Oracle Corporation© – For Internal and Customer Use only [email protected]

17

Rev 2.0 - June 2003