Data Collection

This chapter covers the following topics:

About Data Collection

Data can be brought into Oracle Demand Planning through:

Seeded data collection programs copy data streams from the data source to the Demand Planning server. If data source is other than Oracle Applications, then the data can be inserted into the Oracle Demand Planning Server staging tables using a SQL*Loader program. The following figure shows the data collection process:

the picture is described in the document text

If the data is collected from an Oracle source, it can be optionally moved into the Demand Planning Server staging tables for data cleansing and then moved into the Demand Planning Server fact tables, or inserted directly into the Demand Planning Server fact tables. The next figure illustrates the process.

the picture is described in the document text

The profile MSD: One Step Collection controls the number of data collection steps that the user should invoke to populate the data in to the fact tables. If the value of the profile is selected as No, a two-step collection process is used as seen in the diagram above. In the first step, collection programs take the data from a specified source instance in to the Staging Tables where the data can be consolidated and cleansed by the user if required. In the second step, the pull programs carry the entire data, regardless of source instance over to the fact tables from the staging tables. Data stored in the fact table is overwritten by newly collected data within specific date ranges. This process of pulling data from the staging table empties the staging table for the next cycle of data collection. Both sets of tables exist on the Planning Server itself.

However, if the value of the profile is selected as Yes, the collection programs take the data directly into the fact tables without requiring the user to invoke pull programs. Selecting Yes means that the collected data is suitable to be used in Oracle Demand Planning and does not require to be cleansed in the staging tables. One Step Collection is only an additional user convenience, but it internally works as two step collection. The pull programs are launched automatically to bring the entire data for all the instances from staging table in to the fact tables.

Procedure for Collecting Data

There are several types of data you can collect: Shipment Data, Booking Data, Order Backlog, Currency Conversion, UOM Conversion, Manufacturing Forecast, Sales Forecast, Level Values, Time Data, Pricing Data, Custom Data Stream, BOM Collections, or Promotional History.

Depending on what data you select, a set of parameters for collecting the data becomes available. You need to complete those fields to indicate how you want the data collected. The following sections contain the details on how to collect the various types of data.

A recommended approach for organizing your data collection is to first collect the mandatory metadata entities, such as level values, time data, and UOM conversions. Then collect the optional metadata entities, such as currency conversions and bills of material. Finally, fact data entities, such as shipment data, booking data, price list, and so on can be collected in any order.

To collect Shipment data or Booking data:

Shipment Data and Booking Data are normally used as the basis for statistical forecasting to predict future demand patterns. These are the historical sales data from the Oracle Order Management system for all the Models, Option Classes, and Options.

Sales order lines store up-sell, cross-sell, and substitution relationship information. This data call also be brought into Oracle Demand Planning using the Booking history, Booking history - booked items, Shipment history, or Shipment history - shipped items data streams. For details about up-sell, cross-sell, and substitution, see: the Oracle Order Management User's Guide and the Oracle Advanced Supply Chain Planning User's Guide.

The table below indicates for each data stream, where the relationship is captured: ordered or original item.

the picture is described in the document text

  1. Choose the Demand Planning System Administrator responsibility.

  2. In the Navigator, select Collections > Collect from Oracle Systems > Shipment (or Booking) Data to open the Parameters window.

    the picture is described in the document text

  3. Complete the fields in the Parameters window.

    Field Function Legal Values
    Instance The Instance where the data is to be moved to the staging tables in the Demand Planning Server. Lookup Values
    Date From (Optional) The date from which to collect data. DATE
    Default = System date - 12 months
    Date To (Optional) The date to which to collect data. DATE

  4. When a date range is specified in the parameters, the next time data is collected from the same instance, it overrides the previous data for the specified date range for that instance. Any data beyond the date range from the previous collection is added to the staging table. This is how incremental data is brought into the Demand Planning Server. The specified dates refer to the booked date for the booking data collection and the shipped date for the shipment data collection.

  5. Select OK.

  6. Select Submit.

    Submitting the request starts the collection process from the source instance into the Demand Planning Server staging tables. The collection can be scheduled or can be set to execute immediately.

To collect Order Backlog data:

Oracle Demand Planning supports the collection of order backlog data from Oracle Order Management. Backlog refers to any sales order quantity that is currently booked, but has not yet been shipped or cancelled.

  1. Choose the Demand Planning System Administrator responsibility.

  2. To open the Custom Stream Collection window, select Collections > Collect from Oracle Systems > Order Backlog in the Navigator.

    the picture is described in the document text

  3. In Collection Parameters, complete the following fields:

    Field Function Legal Values
    Collection Type This field (view only) indicates the type of collection method, and is view only. When the type is Collect, the concurrent program collects data from the source to the destination table. When the type is Pull, the program moves data from the staging tables to the fact tables. To change the collection type, go to Collect/Pull Data in the navigation menu. Collect
    Single Step Collection It appears as checked if you have specified Yes for the profile option MSD_ONE_STEP_COLLECTION. The single step collection means that you want to bring the flexible data directly in to the Oracle Demand Planning fact tables rather than bringing the data first in to the staging tables and in the second step, pulling the data in to the fact tables. Check
    Instance Name of the source instance. This identifies which source instance to collect data. Lookup Values
    Complete Refresh The previously collected data are deleted from the staging or fact tables, depending on the tables into which you brought the data. Check
    Validate Data The data are always validated at the time of pulling into the fact tables. For details, see: Uploading Flexible Data Streams:. Check
    Stream Designator Name of the data collected at the time. This is specified by the user to identify the data collected during a collection process. VARCHAR
    Scope Indicates whether the collected data contains all the backlog sales orders, only the scheduled sales orders, or only the unscheduled sales orders.
    When set to All Orders, all the scheduled and unscheduled sales orders are collected.
    When set to Scheduled Orders Only, only the scheduled sales orders are collected.
    When set to Unscheduled Orders Only, only the unscheduled sales orders are collected.
    The backlog orders are collected based on their scheduled ship dates and ship from organizations.    		 All Orders (default), Scheduled Orders Only, or Unscheduled Orders Only
    Date From This date is used as a start date for data collection from source. The backlog quantity is collected for the specified period. Users can specify both past and future dates. If the start and end dates are left blank, all the order backlog data is collected. DATE
    Date To This date is used as an end date for data collection from source. The backlog quantity is collected for the specified period. Users can specify both past and future dates. If the start and end dates are left blank, all the order backlog data is collected. DATE
    Restrict to Orders of Type Specify an Order Type, such as Revenue or Non-Revenue. The backlog for only the specified type of orders is collected. The field is blank by default, in which case all the orders are collected regardless of the order type. VARCHAR
    Includes Orders on Hold Select Yes to bring sales orders, regardless of the hold status.
    Select No to exclude the orders on hold.    		 Yes or No (default)

To collect Currency Conversion or UOM Conversion data:

The Currency Conversion collection brings in the currency conversion rates from the general ledger rate table source data. In a multinational organization, demand planners may require the ability to view and analyze the forecast revenues in different currencies using the collected currency conversions. However, Oracle Demand Planning displays all the amounts (revenue) in one base currency selected in the profile options. Thus, the collected currency conversions are not used in the present release of Oracle Demand Planning.

The Unit of Measure Conversion collection brings in UOM conversions data from the selected source. This data helps to aggregate forecasts for items at all hierarchy levels within a dimension in a common unit.

  1. Choose the Demand Planning System Administrator responsibility.

  2. In the Navigator, select Collections > Collect from Oracle Systems > Currency Conversion (or UOM Conversion) Data.

  3. A Parameters window appears. For details, see: To collect Shipment data or Booking data:.

  4. Select OK.

  5. Select Submit.

To collect Manufacturing Forecast data:

The manufacturing forecast is a useful comparison to the statistical forecast. In case of major discrepancies, this may lead to root cause analysis, and ultimately an enhanced forecast by the Demand Planner. If the entries in a manufacturing forecast/forecast set use more than one type of bucket (for example, day and week), all the entries are considered to be in the bucket of the first entry. In other words, multiple buckets are not supported in Oracle Demand Planning.

  1. Choose the Demand Planning System Administrator responsibility.

  2. In the Navigator, select Collections > Collect from Oracle Systems > Manufacturing Forecast Data.

  3. A Parameters window appears.

  4. Select OK.

  5. Select Submit.

To collect Sales Forecast data from Oracle Sales Online to the Demand Planning Server:

Sales forecast numbers can be collected into Oracle Demand Planning from Oracle Sales Online, but only for the highest level sales groups. Users can allocate the data down to the lower levels using other data streams with data at the desired levels.

  1. Choose the Oracle Demand Planning System Administrator responsibility.

  2. To open the Sales Forecast Collection window, select Collections > Collect from Oracle Systems > Sales Forecast in the Navigator.

    the picture is described in the document text

  3. The following sales forecast data streams can be collected from this window: Sales forecast - best case, Sales forecast - worst case, Sales forecast - probable case, Sales forecast - pipeline, Sales forecast - weighted pipeline, Customer sales forecast, and Customer order forecast. Users need to select one or more data streams to collect the sales forecast amounts into Oracle Demand Planning. For details about each type of sales forecast, see: Input Parameters.

  4. Complete the fields in the Sales Forecast Collection window as follows:

    Field Function Legal Values
    Collection Type This (view only) field indicates the type of collection method. When the type is Collect, the concurrent program collects data from the source to the destination table. When the type is Pull, the program moves data from the staging tables to the fact tables. To change the collection type, go to Navigator > Collect/Pull Data > Sales Forecast. Collect
    Single Step Collection It appears as checked if you have specified Yes for the profile, MSD_ONE_STEP_COLLECTION. The single step collection means that you want to bring the flexible data directly in to the Oracle Demand Planning fact tables rather than bringing the data first in to the staging tables and in the second step, pulling the data in to the fact tables. Check
    Instance Name of the source instance. This identifies which source instance to collect data from. Lookup Values
    Stream Designator Name of the data collected from the source instance at a time.
    The Custom Stream Collection program allows you to select a previously collected stream designator, which is based on fact tables instead of staging tables.    		 VARCHAR
    Start Date This date is used as a start date for data collection of sales forecast numbers. DATE
    End Date This date is used as an end date for data collection of sales forecast numbers. DATE
    Forecast Category It is an Oracle Sales Online feature by which an interest type can roll up to two or more forecast categories. In such situations, you should specify a forecast category to avoid double counting. Oracle Demand Planning will not provide a list of forecast category values, and you need to know and specify the exact forecast category. If this field is left blank, sales forecast numbers for all the forecast categories are brought over to Oracle Demand Planning. VARCHAR
    Bucket Type Oracle Sales Online allows you to submit sales forecast numbers at several time levels such as, fiscal month and fiscal quarter. Typically, the business process will dictate the use of a bucket, however, you must select a bucket type from the list of fiscal calendar level values. Fiscal Month, Fiscal Quarter, or Fiscal Year
    Source of Collection There are three types of data:
    Opportunity Worksheet: Sales forecasts for a customer summed across all the inventory organizations, products, sales channels, and sales representatives
    Product Category Worksheet: Sales forecasts for an interest type summed across all the inventory organizations, customers, sales channels, and sales representatives
    Forecast Worksheet (submitted forecasts): Sales forecasts summed across all the inventory organizations, products, customers, sales channels, and sales representatives.    		 Opportunity Worksheet, Product Category Worksheet, or Forecast Worksheet

  5. Select Submit.

For details about Sales Forecast, see: Using Sales Forecasts and Opportunities.

To collect Level Values data:

The Level Value collection brings in level values for each dimension (except for the time dimension that is collected separately) with its association between the level values of the parent-child relationship in the hierarchy. Level value data can be collected for the following:

This filtering mechanism allows for the collection of only the level values necessary for demand planning, thus reducing the processing burden. Often, the forecasts do not include all the Customers. In such cases it is not necessary to bring all the customer names into Oracle Demand Planning. The Customer data is generally very huge and any filtering by Customers improves the system performance. To achieve this, a profile option, MSD_CUSTOMER_ATTRIBUTE is available in the source instance.

It is possible to customize the level value collection programs or self-service flat files to suit your needs. The level value collection program uses the columns specified in the Hierarchy Levels Form to collect level values from the source view. Thus, with the Demand Planning Hierarchy Levels Form, you can modify the relation view name and column mapping to collect level values from different source views other than the seeded one.

Demand classes are also collected as a part of level values collection.

Note: The format of the level value and level value description for Trading Partner Sites (Ship to Location) is as follows:

  1. External Sites: customer name: customer number: location: OU Name.

  2. Internal Sites: customer name: customer number: location: OU Name: Org Code (where Org Code is the Internal (Ship to) Organization to which the internal site belongs.)

  1. Choose the Demand Planning System Administrator responsibility.

  2. In the Navigator, select Collections > Collect from Oracle Systems > Level Values to open the Parameters window.

    the picture is described in the document text

  3. Complete the fields in the Parameters window.

    Field Function Legal Values
    Instance The Instance where the data is to be moved to the staging tables in the Demand Planning Server. Lookup Values
    Collection Type Type of collection choices are: All, Demand Plan, Dimension, Hierarchy, or Level. All, Demand Plan, Dimension, Hierarchy, or Level
    Collection Value List of Data Collection names based on the Collection Type. Lookup Values

  4. Select OK.

  5. Select Submit.

Once you have collected the level values data, you can tailor the display of your data. The descriptions for items and organizations are read and displayed by Oracle Demand Planning. You can choose to display short, medium, long or automatic names.

To collect Calendars (Time Data):

Oracle Demand Planning supports the collection of manufacturing and fiscal calendars via the collect time data functionality.

Time data collection brings in data for all the specified time hierarchy levels, such as manufacturing week. Each Time hierarchy (Manufacturing Calendar and Fiscal Calendar) is collected separately, and the demand is rolled up or down accordingly. For example, demand of a car model can be rolled from a daily forecast amount to monthly to quarterly to yearly in the Fiscal Calendar.

The appropriate selection of calendar type depends on the availability of source data in these calendars. The Gregorian calendar is built within Oracle Demand Planning while the manufacturing and fiscal calendars are collected from the source applications. The period types in the fiscal calendar should match the seeded Oracle Demand Planning levels (for example, month, quarter, and year) for the calendar to be usable in Oracle Demand Planning. You do not need to set up a new set of books or calendar but need to add the required fiscal periods from GL setups. For details of setting up fiscal (for example, Accounting) calendar, see: Oracle General Ledger User Guide.

If the existing time hierarchies (types of calendars) are not adequate, then load the calendars in a new time hierarchy called Composite Calendar. Composite calendars can be loaded via flat files. The composite calendars should be validated in Oracle Demand Planning to ensure that the various level values align properly.

Oracle Demand Planning does not support multiple instances. Users can inadvertently use two calendars of the same name. If multiple calendars of the same name that belong to different instances appear in Oracle Demand Planning, Oracle Demand Planning does not differentiate among them.

  1. Choose the Demand Planning System Administrator responsibility.

  2. In the Navigator, select Collections > Collect from Oracle Systems > Time Data to open the Parameters window.

    the picture is described in the document text

  3. Complete the fields in the Parameters window.

    Field Function Legal Values
    Instance The Instance from where the data is to be moved to the staging tables in the Demand Planning Server. Lookup Values
    Calendar Type Two types of Calendars. When no manufacturing calendar is specified in the collections parameter, then all manufacturing calendars associated to the valid Demand Planning organizations are collected. Fiscal Calendar or Manufacturing Calendar
    Calendar Code List of Calendar names based on the Calendar Type. If the user specifies a code, only the specified calendar is collected. Lookup Values
    Date From (Optional) The start date of the calendar. DATE
    Date To (Optional) The end date of the calendar. DATE

  4. Select OK.

  5. Select Submit.

To collect Calendars associated to Organizations:

  1. Choose the Demand Planning System Administrator responsibility.

  2. In the Navigator, select Collections > Collect from Oracle Systems > Time Data.

  3. A Parameters window appears. To collect the calendars for all the enabled organizations, the user selects the Manufacturing Calendar as the Calendar Type and leaves the Calendar Code blank in the parameters window.

  4. Select OK.

  5. Select Submit.

    This collection program retrieves and stores the Calendar-Organization association that is used to populate the seeded data stream, Workday Allocation Weights. For details about Workday Allocation Weights, see: Lowest Time Levels for the demand plan. The program ends in error if the organizations have not been enabled in Oracle Demand Planning.

To collect Pricing data:

The Pricing data collection is used to bring in price lists. Once a price list and the source instance are selected, the price list is brought into Oracle Demand Planning on time and product dimensions. The item prices and their effective dates are collected from the source ERP applications.

You can edit the pricing data in the Oracle Demand Planning staging table to reflect prices at any dimension level, such as product category level in the product dimension or customer level in the geography dimension. The specified price is applicable for all the child level values. In the same staging table, you can also specify the priority to manipulate price applicability. Higher priority prices, where 1 is higher than 2, are applied in case of a conflict. When the priorities are the same for conflicting prices, the prices are applied in the order in which they appear in the price list. For example, if conflicting item and product category level prices appear in a price list with the product category level price appearing last, the product category level price is applied.

There is no user interface to do such editing, and the editing should be done by a technical user. Qualifiers, modifiers, attributes, rounding factors, and other related functions of the Oracle Advanced Pricing application are not supported in Oracle Demand Planning.

Precedence is supported by Oracle Demand Planning. The standard price functionality allows prices to be supplied at any level of a hierarchy. If you supply data for a detailed cell that has multiple 'ancestor' prices available, then Oracle Demand Planning uses the price with the highest priority. If all eligible prices have equal priority, the price that is closest to the item level is used for the product dimension. For other dimensions, the last one physically loaded into the database is used. For example, if the price for an item has a lower precedence number (indicating higher priority) than that used by the product category, the item price is used for that item. The product category price is used for all the other items of the product category.

When forecasting, you can load all prices at the aggregate geography level for each group of similar products. These prices are given a default priority. However, you might make exceptions for specific countries or products. For example:

These exceptional prices can be given higher priority in order to ensure that they are applied instead of the default prices.

You must be careful when applying different prices at lower product levels such as item if the business is also supplying prices at both the high and low geography levels. In those circumstances, they must specify the lower-level product prices for both the high and low geography levels.

The following table provides a scenario where results vary dependent on the geography and product levels, and the price priority:

Geography Level Product Level Relative to Default Actual Value Price Example 1 Price Example 2
High High Default 215 300 300
High Intermediate Default-1 214 300 (inherited price) 300 (inherited price)
High Item (bottom level) Default-2 213 235 235
Low High Default-5 210 280 280
Low Intermediate Default-6 209 280 (inherited price) 280 (inherited price)
Low Item (bottom level) Default-7 208 280 (inherited price) 235

In the above example, a typical product is 300 EUR for a high geography level (priority 215), 280 EUR for a low geography level (priority 210), and 235 EUR when on sale. If the promotion price is only supplied for the high geography level (priority 213), then it is ignored by the low geography level because the 280 EUR price has a greater priority (210). An override price must also be supplied for this item in the low geography level (priority 208).

It is possible to have multiple price lines for the same item and for the same effective dates but with different units of measure. In such a scenario, you select a price line with a unit of measure that matches the item's unit of measure. If none of the price line units of measure match the item's units of measure, select a price line that has the primary unit of measure flag checked. If none of the price lines have the primary unit of measure flag checked, select the highest precedence (low value) price list and convert it into the item's primary unit of measure. Then apply a currency conversion to convert your price list currency to the demand planning currency.

Note: New price information is loaded into Oracle Demand Planning when a full download process is run. New price data is used to determine the amount based on the quantity. Revised prices, however, are not applied to existing data until the price list is reapplied by running the populate measures. Instead, modifications to existing data use the original price and updated quantity to compute the amount. There is an exception to this rule: typing an "NA" in a worksheet will access the new price data for that product.

If you are using the Oracle Advanced Pricing application, Oracle Demand Planning will by default use only those price lists for which Source System is specified as Oracle Advanced Pricing. To use price lists pertaining to other source systems, you need to specify the appropriate mapping from the Request Type MSD to the source system.

In Oracle Advance Pricing, the user can select an item price unit of measure (UOM), which could be different from the primary unit of measure for that item. The price unit of measure is supported in Oracle Demand Planning by converting the prices from the price unit of measure to the primary unit of measure. Multiple currencies can be used in Oracle Advanced Pricing. Oracle Demand Planning reads the price currency and converts it to the demand planning currency.

For details on pricing, see: the Oracle Advanced Pricing User's Guide.

  1. Choose the Demand Planning System Administrator responsibility.

  2. In the Navigator, select Collections > Collect from Oracle Systems > Pricing Data.

  3. A Parameters window appears.

  4. Select OK.

  5. Select Submit.

the picture is described in the document text

Once you have collected the pricing data, you can select the desired price list(s) when creating your forecast.

To collect Custom Stream data:

Custom Stream collection is used to collect data streams that are defined by you and are not seeded. For details, see: About Flexible Data Streams.

  1. Choose the Demand Planning System Administrator responsibility.

  2. In the Navigator, select Collections > Collect from Oracle Systems > Custom Stream Collection Data.

  3. A Parameters window appears. Select a data stream name. To complete, see:

    To collect Order Backlog data:.

  4. Select OK.

  5. Select Submit.

To collect BOM data:

BOM data is used for dependent demand forecasting of various optional components used in a product model.

  1. Choose the Demand Planning System Administrator responsibility.

  2. To open the Planning Data Collection window, select Collections > Collect from Oracle Systems > BOM Collections in the Navigator.

    the picture is described in the document text

  3. Now, click in the Parameters field. You can indicate from where and how you want to the data collected.

    the picture is described in the document text

  4. Complete the fields in the Parameters window:

    Field Function Legal Values
    Instance The Instance where the data is to be moved to the Staging tables in the Demand Planning Server. Lookup Values
    Number of Workers One or greater. This allows you to specify the number of workers for the data pull. Increase this number to increase the amount of computational resources to devoted to the data pull process. NUMBER
    Timeout (Minutes) The maximum amount of time you would like to allocate to the data pull process. If this process has not completed within this amount of time, it will be terminated with an error. NUMBER
    Purge All Previously Collected Data Setting this to Yes removes all data in the planning server associated with the selected source instance as the first step in the collections process. If you set this to Yes, the only allowable collection method is Complete Refresh. If you set this to No, the allowable collection methods are Targeted Replacement and Net Change. Yes or No
    Collection Method Whether Oracle Advanced Supply Chain Planning is installed with Oracle Demand Planning affects the options a System Administrator should select when performing Demand Planning BOM Collections.
    If Oracle Advanced Supply Chain Planning is installed and collections have been run, then the demand planning system administrator does not need to purge the previously collected data, and can simply perform net change refresh.
    If Oracle Advanced Supply Chain Planning is not installed, then the demand planning system administrator would perform the collections with these options, except when the snapshots grow excessive and need to be purged. In this case, the demand planning system administrator can select the Purge all Previously Collected Data option and wipe out the data. All necessary demand planning data will be collected.
    The demand planning system administrator should note that whenever Yes is set for Purge all Previously Collected Data, then Oracle Advanced Supply Chain Planning collections data will be wiped out and will need to be run again. This could render existing plans useless.
    The following collection methods exist:

    • The Net Change Refresh method copies only incremental changes for items and BOMs to the planning server, and thus, is faster.

    • The Targeted Refresh method deletes the previously collected data pertaining only to items and BOM.

    		 Net Change Refresh, Targeted Refresh

  5. Select OK.

  6. Select Submit.

    The Demand Planning BOM Data Collection program runs. This program is a two-step concurrent report set. In the first step, the program collects the BOM data from the source instance into the MSC_ST_BOM and MSC_ST_BOM_COMPONENTS tables in the staging area of the ASCP component.

    In the second step, the Demand Planning BOM Data Pull program takes the data and populates the MSD BOM Data Model, if the MSD: Calculate Planning Percentages Profile Option is set to Yes, Exclude Option Classes, but include Components. For details about the MSD: Calculate Planning Percentages profile, see: Demand Planning Profile Options.

    Since both the steps run automatically, you only need to select Submit once.

To collect Material Usage History for Unplanned Maintenance:

The material usage history - unplanned maintenance data stream is used to collect the historical material consumption associated with non-routine maintenance in Oracle Complex Material, Repair and Overhaul. In other words, this data stream collects the materials that are used historically at your facility for unplanned maintenance. This data stream is used in Oracle Demand Planning to forecast the material requirements anticipated for future non-routine maintenance.

  1. Choose the Demand Planning System Administrator responsibility.

  2. To open the Custom Stream Collection window, select Collections > Collect from Oracle Systems > Material Usage History - Unplanned Maintenance in the Navigator.

    the picture is described in the document text

  3. In this window, the Data Stream Name and Source of Data are automatically populated.

  4. In the Collection Parameters section of the window, the Collection Type and checkboxes are automatically populated.

  5. Select the Instance from the list of values.

  6. Enter your desired historical material data collection Start and End Dates.

  7. Select Submit. Or select Submit and Schedule. The completed quantity is collected.

To collect Material Requirements for Scheduled Visits:

The material requirements - scheduled visits data stream is used to collect the material tied to scheduled visits as forward looking firm demand. In other words, this data stream collects the scheduled visits with the associated place and time data that has been designated as firm demand. This data stream is used in Oracle Demand Planning as a reference forecast.

  1. Choose the Demand Planning System Administrator responsibility.

  2. In the Navigator, select Collections > Collect from Oracle Systems > Material Requirements - Scheduled Visits.

    The Custom Stream Collection window appears.

  3. In this window, the Data Stream Name and Source of Data are automatically populated.

  4. In the Collection Parameters section of the window, the Collection Type and check boxes are automatically populated.

  5. Select the Instance from the list of values.

  6. Enter the desired Start and End Dates.

  7. Select Submit. Or select Submit and Schedule.

To collect Material Requirements for Planned Maintenance:

The material requirements - planned maintenance data stream is used to collect all the material associated with a unit maintenance plan as a forecast data stream. The unit maintenance plan is the forecast of the required routine maintenance generated in Oracle Complex Maintenance, Repair and Overhaul. This data stream is used in Oracle Demand Planning as the forecast of service parts for the planned maintenance.

  1. Choose the Demand Planning System Administrator responsibility.

  2. In the Navigator, select Collections > Collect from Oracle Systems > Material Requirements - Planned Maintenance.

    The Custom Stream Collection window appears.

  3. In this window, the Data Stream Name and Source of Data are automatically populated.

  4. In the Collection Parameters section of the window, the Collection Type and check boxes are automatically populated.

  5. Select the Instance from the list of values.

  6. Enter your desired Start and End Dates.

  7. Select Submit. Or select Submit and Schedule.

To collect Material Usage History for Planned Maintenance:

The material usage history - planned maintenance data stream is used to collect the historical material consumption associated with routine maintenance in Oracle Complex Material, Repair and Overhaul. In other words, this data stream collects the materials that are used historically at your facility for planned maintenance.

  1. Choose the Demand Planning System Administrator responsibility.

  2. In the Navigator, select Collections > Collect from Oracle Systems > Material Usage History - Planned Maintenance.

    The Custom Stream Collection window appears. The data collection windows for all four CMRP streams are similar.

  3. In this window, the Data Stream Name and Source of Data are automatically populated.

  4. In the Collection Parameters section of the window, the Collection Type and check boxes are automatically populated.

  5. Select the Instance from the list of values.

  6. Enter your desired historical material data collection Start and End Dates.

  7. Select Submit. Or select Submit and Schedule.

To collect Service Parts Usage History:

The service parts usage history data stream is used to collect service parts consumption. For details on service parts data, service parts usage transactions in debriefs, and usage forecasting, see: Forecasting for Service Parts.

  1. Choose the Demand Planning System Administrator responsibility.

  2. To open the Custom Stream Collection window, select Collections > Collect from Oracle Systems > Service Parts Usage History in the Navigator.

    the picture is described in the document text

  3. In this window, the Data Stream Name and Source of Data are automatically populated.

  4. In Collection Parameters, complete the following fields:

    Field Function Legal Values
    Collection Type This field (view only) indicates the type of collection method, and is view only. When the type is Collect, the concurrent program collects data from the source to the destination table. When the type is Pull, the program moves data from the staging tables to the fact tables. To change the collection type, go to Collect/Pull Data in the navigation menu. Collect
    Single Step Collection It appears as checked if you have specified Yes for the profile option MSD_ONE_STEP_COLLECTION. The single step collection means that you want to bring the flexible data directly in to the Oracle Demand Planning fact tables rather than bringing the data first in to the staging tables and in the second step, pulling the data in to the fact tables. Check (default)
    Instance Name of the source instance. This identifies which source instance to collect data. Lookup Values
    Complete Refresh The previously collected data are deleted from the staging or fact tables, depending on the tables into which you brought the data. Check (default)
    Validate Data The data are always validated at the time of pulling into the fact tables. Check (default)
    Stream Designator Name of the data collected at the time. This is specified by the user to identify the data collected during a collection process. VARCHAR
    Date From This date is used as a start date for data collection from source. The backlog quantity is collected for the specified period. Users can specify both past and future dates. If the start and end dates are left blank, all the order backlog data is collected. DATE
    Date To This date is used as an end date for data collection from source. The backlog quantity is collected for the specified period. Users can specify both past and future dates. If the start and end dates are left blank, all the order backlog data is collected. DATE
    Geography Dimension Level You can select a level in the Geography Dimension for data analysis and display in Oracle Demand Planning. The selected level becomes the allocation floor in geography dimension for service part history data stream. Data is available at the level higher than the selected level in hierarchy of the selected level. Customer (default), Zone, and All Geography
    Source This is the application from where you are collecting the data. The Field Service value collects only field service usage transactions that are done via debrief process in Oracle Spares Management. The Depot Repair value collects the material requirements for repair jobs. Repair jobs are non-standard discrete jobs created in work-in-progress based on a repair order in Oracle Depot Repair. Material requirements from JTF tasks are also collected. Field service and Depot Repair (default), Field Service, Depot Repair
    Restrict to Install Base Trackable Items Yes means that the usage history of only those service parts is collected for which the Install Base Tracking item attribute (service attribute group in item master) is enabled in Oracle Inventory. Yes/No

  5. Select Submit. Or select Submit and Schedule.

To collect Service Parts Returns History:

The service parts returns history data stream is used to collect the returned service parts. For details on service parts data, service parts usage transactions in debriefs, and usage forecasting, see: Forecasting for Service Parts.

Note: Service parts return history can be used in Oracle Demand Planning to create a forecast of service part returns. However, the return forecast cannot be used outside of Oracle Demand Planning. The forecast cannot be published to Oracle Advanced Supply Chain Planning or a source ERP application.

  1. Choose the Demand Planning System Administrator responsibility.

  2. To open the Custom Stream Collection window, select Collections > Collect from Oracle Systems > Service Parts Returns History in the Navigator.

    The Service Parts Returns History Custom Data Collection window has the same fields as the service parts usage history custom data collection window, except for the Source field. See the table above for details on how to complete these fields.

    the picture is described in the document text

  3. Select Submit. Or select Submit and Schedule.

To collect Return History:

The return history data stream is used to collect the product returns.

  1. Choose the Demand Planning System Administrator responsibility.

  2. To open the Custom Stream Collection window, select Collections > Collect from Oracle Systems > Return History in the Navigator.

    the picture is described in the document text

  3. In Collection Parameters, complete the following fields:

    Field Function Legal Values
    Collection Type This field (view only) indicates the type of collection method, and is view only. When the type is Collect, the concurrent program collects data from the source to the destination table. When the type is Pull, the program moves data from the staging tables to the fact tables. To change the collection type, go to Collect/Pull Data in the navigation menu. Collect
    Single Step Collection It appears as checked if you have specified Yes for the profile option MSD_ONE_STEP_COLLECTION. The single step collection means that you want to bring the flexible data directly in to the Oracle Demand Planning fact tables rather than bringing the data first in to the staging tables and in the second step, pulling the data in to the fact tables. Check (default)
    Instance Name of the source instance. This identifies which source instance to collect data. Lookup Values
    Complete Refresh The previously collected data are deleted from the staging or fact tables, depending on the tables into which you brought the data. Check (default)
    Validate Data The data are always validated at the time of pulling into the fact tables. Check (default)
    Date From This date is used as a start date for data collection from source. The backlog quantity is collected for the specified period. Users can specify both past and future dates. If the start and end dates are left blank, all the order backlog data is collected. DATE
    Date To This date is used as an end date for data collection from source. The backlog quantity is collected for the specified period. Users can specify both past and future dates. If the start and end dates are left blank, all the order backlog data is collected. DATE
    Include RMA Type RMA (return material authorization) Type is a filter to restrict the collection of product returns to specific type of product returns such as 'advanced exchange' or 'repair and return'. By default, all the returns are collected. Lookup Values

  4. Select Submit. Or select Submit and Schedule.

To collect Promotional History:

The promotional history data stream is used to collect promotional sales history. Promotional sales history is the quantity of any sales order line for which a pricing modifier has been applied.

  1. Choose the Demand Planning System Administrator responsibility.

  2. To open the Custom Stream Collection window, select Collections > Collect from Oracle Systems > Promotional History in the Navigator.

    the picture is described in the document text

  3. In Collection Parameters, complete the following fields:

    Field Function Legal Values
    Collection Type This field (view only) indicates the type of collection method, and is view only. When the type is Collect, the concurrent program collects data from the source to the destination table. When the type is Pull, the program moves data from the staging tables to the fact tables. To change the collection type, go to Collect/Pull Data in the navigation menu. Collect
    Single Step Collection It appears as checked if you have specified Yes for the profile option MSD_ONE_STEP_COLLECTION. The single step collection means that you want to bring the flexible data directly in to the Oracle Demand Planning fact tables rather than bringing the data first in to the staging tables and in the second step, pulling the data in to the fact tables. Check (default)
    Instance Name of the source instance. This identifies which source instance to collect data. Lookup values
    Complete Refresh The previously collected data are deleted from the staging or fact tables, depending on the tables into which you brought the data. Check (default)
    Validate Data The data are always validated at the time of pulling into the fact tables. Check (default)
    Date From This date is used as a start date for promotional history collection from source. DATE
    Date To This date is used as an end date for promotional history collection from source. DATE
    Stream Designator The name by which the data collected in a collection run will be identified. VARCHAR
    Scope The scope parameters for data collection should be selected depending on which sales history (total sales) will drive the forecasting:

    • Booking History: Collect promotional history for all booked orders.

    • Shipment History: Collect promotional history for all shipped orders or all scheduled orders depending on your analytic needs.

    		 All Booked Orders, All Shipped Orders, or All Scheduled Orders

  4. Select Submit. Or select Submit and Schedule.

    The collection program collects only the promotional quantities (units) and not the dollar amounts.

Submit and Schedule

If you selected Submit and Schedule, then a Note appears with the schedule.

the picture is described in the document text

Select OK. Then the Requests window appears, which shows the status of the concurrent request.

Graphical Display of Hierarchies

It is important that the aggregation relationships for a specific level and its subordinate level in the hierarchy are properly established. Otherwise, inaccurate information is produced at the time of aggregation. For example, in a geographic area such as Asia-Pacific, if the countries are not correctly set up in the database, the Asia-Pacific level forecasts will not be accurate. Furthermore, due to dynamic business environment, relationships can change. This feature enables you to review and validate these level values within a dimension after the data collection. For example, if you want to find out the names of the product categories (product category hierarchy) within the product dimension, you can select the both the product dimension and the product category hierarchy from the menu, and then the system displays all the product category names from collected data on the window.

Hierarchy Level Values Viewer features include:

To run the hierarchy viewer:

  1. Choose the Demand Planning System Administrator responsibility.

  2. In the Navigator, select Dimension Values > Level Values.

  3. To open the Level Values window, select the values for the Dimension, Hierarchy, and Level fields and select Find.

  4. Select View Hierarchy.

    The Hierarchy Viewer appears.

  5. Choose the Dimension and Hierarchy from the list of the values in these fields to open the Hierarchy Viewer window.

    the picture is described in the document text

Pulling Data into Fact Tables

The following pull programs, which correspond with the types of data you can collect, are available in Oracle Demand Planning: Shipment Data, Book Data, Order Backlog, Currency Conversion, UOM Conversion, Manufacturing Forecast, Sales Forecast, Level Values, Time Data, Pricing Data, and Custom Data Stream. For details about these types of data, see: Procedure for Collecting Data.

The Pull Level Value concurrent program has an Incremental Upload option that allows you to specify if you want to bring only new level values and associations. Level Values collection will not delete level values and associations that are not loaded in this collection. The one-step level values collection always happens in the Complete Refresh Mode. For technical details about the data pull, see: Pulling data from Staging Table to Fact Table.

Net Change

The data are collected into Oracle Demand Planning in a net change fashion. Net change refers to collection of only that data which have changed since the last collection.

For building new demand plans, the Demand Planning Engine downloads fact data. For rebuilding demand plans, the Demand Planning Engine uses two dynamic SQL queries to download new or modified Level Values from the level value fact table by comparing the last_refresh_num column with the demand plan build date. The level values contain the newly inserted and modified (since the previous Demand Plan build) fact data.

The Net Change Refresh method collects fact data, reduces the downloading of unnecessary data from Demand Planning Server to Demand Planning Engine, and improves the overall performance of Demand Plan build. The Net Change Refresh method works as follows: a Refresh Number is generated at the beginning of collection that is used to track which records were created or deleted by a collection. Data from staging is inserted into the fact table. Each row stores the LAST_REFRESH_NUM and CREATED_BY_REFRESH_NUM to represent which collection sequence created this row. The ACTION_FLAG column holds the last action performed on the record. Fact data is marked deleted for overlapping period. The LAST_REFRESH_NUM column tells the refresh number that deleted the row, and ACTION_FLAG is set to Delete.

Integrating Legacy Data

Oracle Demand Planning provides the ability to collect data from legacy, (non-Oracle) systems via flat files. This feature enables the integration of hybrid system data sources. You can use planning data in Oracle Demand Planning, even when the source transaction system is not Oracle Enterprise Resource Planning. The flat files, provided as templates, are flexible enough to load all the data needed to run a demand plan. The flat files can be stored on the file system of the Demand Planning server or on a local hard drive.

There are several key processes that utilize concurrent programs to complete the legacy integration process. First, data is collected from source legacy systems by specifying the data file names and locations, and importing those data files into the Oracle Demand Planning Server staging tables. The users can then choose to manually clean the data within Oracle Demand Planning. Lastly, through the Pull Data process, the staging table data is pulled into the Oracle Demand Planning server fact tables, from where the data is sent to the Oracle Demand Planning engine.

Types of data collected include mandatory setup and level values data, fact data for seeded data streams, and custom data for custom data streams. There are two options for data collection that are discussed next: loading data files into Oracle Demand Planning staging tables or loading data files using the Self Service Interface.

Creating Data Files (option 1)

In this option, you collect flat file data by creating data files. You map the Oracle Demand Planning staging table control files to the legacy tables. Then you create scripts to extract data from the legacy system in the format prescribed by the control files. Finally, extract the legacy data to the data files using created scripts.

Once the data files are created, you can load the data files into Oracle Demand Planning. You need to specify the location of the control and the data files. Then you will run the concurrent SQL loader program to load and preprocess the flat files.

Note that if the Level Value flat files contain records pertaining to level id of Item, Product category, Product Family, Organization, Ship-to-location, or Customer, the collections will error out. To insert the corresponding level values pertaining to these levels into DP staging tables, the following flat files should be used:

To load data files into Oracle Demand Planning staging tables:

  1. Choose the Demand Planning System Administrator responsibility.

  2. To open the Planning Data Collection window, select Collections > Collect from Legacy Systems > Collect Flat File Data in the Navigator.

    the picture is described in the document text

  3. In the Planning Data Collection window, select the Parameters field for the Flat File Loader program. Note that the Planning Data Collection window represents the concurrent request set, as detailed at the end of this section.

  4. Specify the data file names.

  5. Enter OK.

  6. Select the Parameters field for the Flat File Loader or DP Pre-Processor program to open the Parameters window for the Flat File Loader program.

    the picture is described in the document text

  7. For the DP Pre-Processor program, another Parameters window opens.

    the picture is described in the document text

  8. Select Yes for the entities corresponding to the data files specified in the Flat File Loader program parameters.

    The details of the legacy integration concurrent programs are as follows:

    Concurrent Program Description Input/Output
    Flat File Loader Program Upload the data from flat file into the Oracle Demand Planning (and Oracle Advanced Supply Chain Planning) staging tables. It will have parameter to upload Item/Product Family, Trading Partner for Customer and Org, Trading Partner sites, Calendars, Booking data, Shipment data, Manufacturing forecast, Sales forecast, Sales Opportunity data, Currency conversions, UOM conversions, Level Values, Level Associations, Events, Event products, Price List, Item list price, and custom Data Stream. Input: Flat files and Control File.
    Output: Records in staging tables from the data file.
    Pre-Processor for Oracle Demand Planning Pre-processing legacy data includes automatic generation of source primary keys IDs for the records in the staging tables, sequential data loading in the order required, and data validation. This is the main program for DP legacy integration.
    If the id exists in LID tables, for example, msc_local_id_xxx and msd_local_id_setup, then the program will use the same ids to populate the staging tables.
    Validates some primary foreign key and entity attributes.    		 Input: Staging table records, LID table records, and user input for processing entities.
    Output: Records in staging table with source pk, records in LID tables if it comes first time, and records in error table.

  9. Enter OK.

  10. Submit the concurrent request.

Creating data files using self service user interface (option 2)

For this option, you need to create data files by downloading the templates and specifying the data in the templates.

Next, you load data files into Oracle Demand Planning by specifying the location of control and data files.

To load data files using Self Service user interface:

  1. Select the Demand Planning System Administrator responsibility.

  2. In the Navigator, select Collections > Collect from Legacy Systems > Collect Flat File Data - Self Service.

    The Self Service page appears.

  3. If needed, select Download link to download the templates zip file.

  4. Select Browse to select the data file to upload.

  5. Submit the concurrent request for DP Purge Program.

  6. Select Start Load Now to load the data file in Oracle Demand Planning.

    the picture is described in the document text

For details about the legacy integration control file formats, see: Oracle Advanced Planning and Scheduling Implementation and User's Guide.