How You Use the Import Template Named Supply Chain Planning Key Customer Options
You must use the file-based data import (FBDI) template named Supply Chain Planning Key Customer Options (ScpKeyCustomerOptionsImportTemplate.xlsm) to specify key customers and control the data aggregation for non-key customers in your demand, demand and supply, and sales and operations plans.
For accessing the import template, see the File-Based Data Import (FBDI) for SCM guide in the Oracle Help Center.
How You Enter Data in the Import Template
The import template has these worksheets:
- KeyCusOptnHeader_: Use the columns on this worksheet to specify the predefined
Customer or Customer Zone hierarchy and its level. You can also specify the
aggregation level and whether zone-level aggregation should be done.
You must include only one hierarchy level on this worksheet.
You can’t specify a user-defined hierarchy, and the entered level must belong to a specified predefined hierarchy.
- KeyCusOptnMembers_: Use the columns on this worksheet to specify level members
that must be treated as key customers and for whom data must be maintained at
the Customer Site level (the lowest level of the Customer dimension) in the
MSC_MEASURE_DATA table.
For example, if you’ve entered the Zone level of the Customer Zone hierarchy, and you enter Zone A in the Level Member Name column, the data for all members in Zone A is maintained at the Customer Site level. Or, if you’ve entered the Customer level of the Customer hierarchy, and you enter Customer B in the Level Member Name column, the data for Customer B is maintained at the Customer Site level.
If you don’t enter any information on this worksheet for the hierarchy level for which you’ve entered 1 or 2 as the aggregation level on the KeyCusOptnHeader_ worksheet, then all the members of that hierarchy level are considered as key customers, and the aggregation of data for non-key customers won’t happen. Similarly, if you specify a hierarchy and level on the KeyCusOptnMembers_ worksheet but no members, then all the members of that hierarchy level are considered as key customers.
Note: You must ensure that the values for the Hierarchy Name and Level Name columns on the KeyCusOptnMembers_ worksheet match the values for the same columns on the KeyCusOptnHeader_ worksheet.
How You Set Aggregation Levels
On the KeyCusOptnHeader_ worksheet of the import template, you must set one of these aggregation levels for the hierarchy level:
- 1: When you enter this value, these two sets of data are available in
MSC_MEASURE_DATA:
- Data is stored at the Customer Site level for your key customers and non-key customers.
- Data is aggregated for your non-key customers to placeholder sites named <zone name>:All Other, where the zone refers to the underlying zone for a customer site. If a customer site doesn’t have an underlying zone, the data is aggregated to the placeholder Default:All Other site.
Note: Unless you want to create plans through which you access Customer Site-level data for non-key customers and create other plans through which you access aggregated data for non-key customers, you shouldn't use this aggregation level. - 2: When you enter this value, these two sets of data are available in
MSC_MEASURE_DATA:
- Data is stored at the Customer Site level for your key customers.
- Data is aggregated for your non-key customers to placeholder sites named <zone name>:All Other, where the zone refers to the underlying zone for a customer site. If a customer site doesn’t have an underlying zone, the data is aggregated to the placeholder Default:All Other site.
- 3: When you enter this value, no data aggregation happens, and the data for all
customers is stored at the Customer Site level in MSC_MEASURE_DATA.
Any previously aggregated data for non-key customers is lost after you collect data in the targeted mode using the Collect Planning Data page or Load Planning Data from Flat Files scheduled process.
No customer is considered as a key customer.
Consider an example in which you've six customers, as depicted in the following table:
Customer Name | Key Customer? | Zone | Customer Site |
---|---|---|---|
Cust_1 | Yes | Zone_1 | site_1 |
Cust_2 | Yes | Zone_1 | site_2 |
Cust_3 | No | Zone_2 | site_3 |
Cust_4 | No | Zone_2 | site_4 |
Cust_5 | No | Zone_3 | site_5 |
Cust_6 | No | Zone_3 | site_6 |
The following table depicts the data for these customers:
Item | Organization | Customer | Customer Site | Zone | Time | Quantity |
---|---|---|---|---|---|---|
Item_1 | Org_1 | Cust_1 | site_1 | Zone_1 | d1 | 10 |
Item_1 | Org_1 | Cust_2 | site_2 | Zone_1 | d1 | 10 |
Item_1 | Org_1 | Cust_3 | site_3 | Zone_2 | d1 | 10 |
Item_2 | Org_1 | Cust_3 | site_3 | Zone_2 | d1 | 10 |
Item_1 | Org_1 | Cust_4 | site_4 | Zone_2 | d1 | 200 |
Item_1 | Org_1 | Cust_5 | site_5 | Zone_3 | d1 | 10 |
Item_2 | Org_1 | Cust_5 | site_5 | Zone_3 | d1 | 10 |
Item_1 | Org_1 | Cust_6 | site_6 | Zone_3 | d1 | 200 |
The previously provided table also depicts how the data stored in MSC_MEASURE_DATA when the aggregation level is 3.
When the aggregation level is 1, and the data is aggregated by zone, the data is stored in MSC_MEASURE_DATA as follows:
Data | Item | Organization | Customer | Customer Site | Time | Quantity |
---|---|---|---|---|---|---|
Data for key customer | Item_1 | Org_1 | Cust_1 | site_1 | d1 | 10 |
Data for key customer | Item_1 | Org_1 | Cust_2 | site_2 | d1 | 10 |
Original data for non-key customer | Item_1 | Org_1 | Cust_3 | site_3 | d1 | 10 |
Original data for non-key customer | Item_2 | Org_1 | Cust_3 | site_3 | d1 | 10 |
Original data for non-key customer | Item_1 | Org_1 | Cust_4 | site_4 | d1 | 200 |
Original data for non-key customer | Item_1 | Org_1 | Cust_5 | site_5 | d1 | 10 |
Original data for non-key customer | Item_2 | Org_1 | Cust_5 | site_5 | d1 | 10 |
Original data for non-key customer | Item_1 | Org_1 | Cust_6 | site_6 | d1 | 200 |
Aggregated data for non-key customer | Item_1 | Org_1 | All Other | Zone_2:All Other | d1 | 210 |
Aggregated data for non-key customer | Item_2 | Org_1 | All Other | Zone_2:All Other | d1 | 10 |
Aggregated data for non-key customer | Item_1 | Org_1 | All Other | Zone_3:All Other | d1 | 210 |
Aggregated data for non-key customer | Item_2 | Org_1 | All Other | Zone_3:All Other | d1 | 10 |
Thus, data is available in MSC_MEASURE_DATA as follows:
- At the Customer Site level for the key customers (Cust_1 and Cust_2) and non-key customers (Cust_3, Cust_4, Cust_5, and Cust_6)
- At the aggregated level for the non-key customers (Cust_3, Cust_4, Cust_5, and Cust_6). In this case, the aggregated data is available under a placeholder customer named as All Other and in placeholder customer sites pertaining to the zones.
When the aggregation level is 2, and the data is aggregated by zone, the data is stored in MSC_MEASURE_DATA as follows:
Data | Item | Organization | Customer | Customer Site | Time | Quantity |
---|---|---|---|---|---|---|
Data for key customer | Item_1 | Org_1 | Cust_1 | site_1 | d1 | 10 |
Data for key customer | Item_1 | Org_1 | Cust_2 | site_2 | d1 | 10 |
Aggregated data for non-key customer | Item_1 | Org_1 | All Other | Zone_2:All Other | d1 | 210 |
Aggregated data for non-key customer | Item_2 | Org_1 | All Other | Zone_2:All Other | d1 | 10 |
Aggregated data for non-key customer | Item_1 | Org_1 | All Other | Zone_3:All Other | d1 | 210 |
Aggregated data for non-key customer | Item_2 | Org_1 | All Other | Zone_3:All Other | d1 | 10 |
Thus, data is available in MSC_MEASURE_DATA as follows:
- At the Customer Site level for the key customers (Cust_1 and Cust_2)
- At the aggregated level for the non-key customers (Cust_3, Cust_4, Cust_5, and Cust_6). In this case, the aggregated data is available under a placeholder customer named as All Other and in placeholder customer sites pertaining to the zones.
When you don’t use the import template, the data you collect or load at the Customer Site level is stored in MSC_MEASURE_DATA as is. Also, all the customers are considered as non-key customers, and their data is aggregated to the <zone name>:All Other sites, where the zone refers to the underlying zone for a customer site. If a customer site doesn’t have an underlying zone, the data is aggregated to the Default:All Other site.
How You Use the Import Template with the Checkbox on the Plan Options Page
The following table explains how access to data from plan tables and graphs is controlled by the use of the import template and Aggregate non-key customer data to an All Other level member checkbox on the Demand tab on the Plan Options page:
Import Template Used? | Aggregation Level | Checkbox Selected? | Access to Data in Plan Tables and Graphs |
---|---|---|---|
No | Not applicable | Yes | You can access only the aggregated data, which is available for the placeholder All Other customer under the placeholder <zone name>:All Other sites. For customer sites that don’t have an underlying zone, the data is aggregated to the placeholder Default:All Other site. |
No | Not applicable | No | You can access data at the Customer Site level for all customers. |
Yes | 1 | Yes |
|
Yes | 1 | No | You can access data at the Customer Site level for all customers. |
Yes | 2 | Yes |
|
Yes | 2 | No |
Note: This setup isn't recommended.
|
Yes | 3 | Yes | You can't access data for customers. Note: This setup isn't recommended. |
Yes | 3 | No | You can access data at the Customer Site level for all customers. |
How You Use the Ignore Zone Column
On the KeyCusOptnHeader_ worksheet, you must enter one of these values in the Ignore Zone column:
- Yes: When you enter this value, the data for non-key customers is aggregated to the placeholder Default:All Other site.
- No: When you enter this value, the data for non-key customers is aggregated to
placeholder sites named in the <zone name>:All Other format, where the zone
name refers to the underlying zone for a customer site. If there’s no underlying
zone for a customer site, the data is aggregated to the placeholder Default:All
Other site.
For example, if you’ve specified the Zone level of the Customer Zone hierarchy, the data for non-key customers is aggregated to sites named in the <zone name>:All Other format, where the zone names refer to the names of members of the Zone level. If the members are Zone1, Zone2, and Zone3, and only Zone1 is specified in the Level Member Name column on the KeyCusOptnMembers_ worksheet, then data for sites in Zone2 and Zone3 will be aggregated to the Zone2:All Other and Zone3:All Other sites.
Or, if you've specified the Customer level of the Customer predefined hierarchy, the data for non-key customers is aggregated to sites named in the <zone name>:All Other format, where the zone names refer to the names of the underlying zones of sites for members of the Customer level. If the members are Customer1, Customer2, and Customer3, and only Customer1 is specified in the Level Member Name column on the KeyCusOptnMembers_ worksheet, then data for the sites for Customer2 and Customer3 will be aggregated to the <zone name>:All Other sites.
If you don’t enter any value in this column, the value is considered as No.
Consider an example in which you've four customers, as depicted in the following table:
Customer | Key Customer? | Zone | Customer Sites |
---|---|---|---|
Customer_1 | Yes | Zone_1 | cust_site_1.1, cust_site_1.2 |
Customer_2 | No | Zone_2 | cust_site_2.1, cust_site_2.2 |
Customer_3 | No | Zone_3 | cust_site_3.1, cust_site_3.2 |
Customer_4 | No | No zone specified | cust_site_4.1, cust_site_4.2 |
If you choose to ignore the zones while aggregating the data for non-key customers, the following table depicts how data for these customers is stored:
If you choose to consider the zones while aggregating the data for non-key customers, the following table depicts how data for these customers is stored: