1. Scheduled Processes for SCM
  2. Load Planning Data from Flat Files

Load Planning Data from Flat Files

Use the Load Planning Data from Flat Files scheduled process to load data into the Oracle Fusion Cloud Supply Chain Planning data repository from external sources, such as legacy systems and third-party applications.

In addition, you can use the Load Planning Data from Flat Files scheduled process to supplement the data collected from an Oracle Fusion source system. For example, you can supplement the collected data with sourcing rule and assignment set data.

Initiate the Load Planning Data from Flat Files scheduled process using the Scheduled Processes task in the Setup and Maintenance work area or using the Load Planning Data from Files task found in the task panel drawer in most Supply Chain Planning work areas.

When to Use

Run this scheduled process whenever data needs to be updated in planning. For example, you might want to load transactional data, such as sales orders, purchase orders, and work orders, nightly to ensure planning has the updated supply and demand.

You can also run this process to capture reference data. The reference data changes less frequently so you need to run the process only when changes occur. For example, you only need to load planner data if an employee leaves the company or if a new planner is hired.

Privileges Required

  • Perform Order Orchestration and Planning Data Load (MSP_PERFORM_ORDER_ORCHESTRATION_AND_PLANNING_DATA_LOAD_PRIV)
  • Run Plan with Snapshot (MSC_RUN_PLAN_WITH_SNAPSHOT_PRIV)
    Note: This privilege is required in order to run the Create Trees for Dimensions scheduled process. The process is launched as part of the Load Planning Data From Flat Files scheduled process if the ZIP file contains data that is used in planning hierarchies, such as organizations, items, customers, and suppliers.

Specifications

Before running the Load Planning Data from Flat Files scheduled process, you must create one or more .csv files that contain the data you wish to upload. You must compress the CSV files and upload the compressed file to Oracle Universal Content Manager (UCM) because the Load Planning Data from Flat Files scheduled process cannot directly process the CSV files.

Familiarize yourself with the two collection type parameters associated with the Load Planning Data from Flat Files scheduled process:

  • The targeted collection type deletes the existing data for a given entity before loading the data contained in the .zip file.

    For example, let’s say you have a ZIP file that only contains the ItemCost.csv file and select the Targeted collection type. When the Load Planning Data from Flat Files scheduled process runs, it will delete any existing item cost records before inserting the valid item cost records contained within the CSV file. Other entities are not impacted because there wasn’t a CSV file for those entities in the ZIP file.

    Be cautious when you use the targeted collection type in combination with the Organization Group entity:

    • Use combinations for loading transactional data that are associated with an organization, such as sales orders or work orders. If you include an entity that isn't associated with an organization, such as customers, the organization group is ignored and all customers are deleted, before the customers contained in the CSV file are processed.
    • If you are running multiple targeted collections for different organization groups at the same time, ensure each organization group contains a distinct set of organizations, meaning an organization shouldn't be associated with two different organization groups.
  • The net change collection type inserts or updates existing data with the data provided in the CSV file.

    For example, let’s say you have a ZIP file that contains the Item.csv file that has five rows. If those five items and organization combinations already exist in the planning system. When the Load Planning Data from Flat Files scheduled process runs, the five rows in the planning files are updated with the data from the CSV file. If the five rows don't exist in the planning system, the rows would be inserted in the applicable planning table.

    If three of the five rows existed in the planning system, the three rows update with the latest values and two rows are inserted.

Caution: If you accidentally choose the targeted instead of the net change collection type, you must reload all the data for any entities that were included in the CSV file. There’s no other way to recover the data.

Check these specifications before you run the process.

Specification Description
Job Type Can run any time or be scheduled to run on a regular basis, such as nightly. For example, your company may have processes that automatically generate CSV files every night containing data that was changed throughout the day. After these files are compressed and uploaded to Oracle Universal Content Manager, they can then be loaded into planning using the Load Planning Data from Flat Files scheduled process in net change mode.
Frequency Can run nightly to update with the changes made earlier that day on the source system, or you can run multiple times throughout the day to notify the Oracle Supply Chain Planning modules of a change. For example, to notify planning of a large sales order that was just entered or of a change in resource availability due to machine failure.
Time of Day It’s typical for businesses to run the Load Planning Data from Flat Files scheduled process at night. However, you can also run this process during the day if the updates must get into planning.
Duration The duration of the Load Planning Data from Flat Files scheduled process varies, depending on how many CSV files are included in the ZIP file and if you're performing targeted or net change collections. For example, when first loading data into planning, you’ll be using targeted mode to load all organizations, items, customers, suppliers, and so on. This will take longer to process than if you were just loading changes that occurred the previous day.
Compatibility

Only one instance of the job should be running at any one time for the same source system and organization group.

  • When collecting entities that aren't associated with an organization, such as customers or suppliers, you shouldn’t specify an organization group because the organization group will be ignored.
  • Don’t run a targeted collection and net change collection for the same entity at the same time. If you are using organization groups, don’t run a targeted collection and net change collection for the same organization group and entity at the same time.
  • If running multiple jobs for the same source system and entity, such as sales orders, but different organization groups, ensure each organization has a distinct set of organizations that aren’t included in any other organization groups.

Parameters

Parameter Optional or Required Description Parameter Values Special Combinations Required Notes
Source System Required The name of the source system from where the data originated, or in the case of a Fusion source system, the name of the source system that is being supplemented with the CSV data. A valid source system name. None NA
Collection Type Required The type of collection. Determines if existing planning data is deleted before loading the new data contained in the ZIP file. See the Specifications section for more information on collection type. Net change (2)

Targeted (1)

 None The numeric values are used when the scheduled process is loaded using ERP Integrations REST service.
Data File Required Name of the ZIP file containing the CSV files to be processed. None None NA
Enable Organization Group Collection Optional Determines whether only the organizations included in the organization group should be processed. No (2)

Yes (1)

 If yes, then the Organization Group is required. The numeric values are used when the scheduled process is loaded using ERP Integrations REST service.
Organization Group Optional

The group of organizations to be processed when the scheduled process runs.

Any data in the ZIP file belonging to an organization that isn't in the specified organization group will be ignored.

Any data in the ZIP file that isn't specific to an organization, for example customers, will be processed without respect to the organization group.

 A valid organization group Enable Organization Group Collection parameter must be set to Yes to specify an organization group. NA
Retain Other External Forecast Designators Optional Retains the existing forecast designators when external forecast is uploaded through file-based data import (FBDI) in Oracle Supply Planning or Oracle Replenishment Planning for a specific forecast designator. No (2)

Yes (1)

 None The option is only applicable to Collection Type as Targeted.
Enable Date Range Purge Before Load for Time Dimensioned Measures Optional The field is for enabling the deletion of previously collected data by time range for the time-dimensioned measures included in the data file for the scheduled process. No (2)

Yes (1)

None
Number of Days Before Today Optional The field is for specifying the number of days before the current day for deletion of data from time-dimensioned measures. Numerical value greater than or equal to 0 Applicable only when parameter Enable Date Range Purge Before Load for Time Dimensioned Measures is set to "Yes" Null value is treated as 0
Number of Days After Today Optional The field is for specifying the number of days after the current day for deletion of data from time-dimensioned measures. Numerical value greater than or equal to 0 Applicable only when parameter Enable Date Range Purge Before Load for Time Dimensioned Measures is set to "Yes" Null value is treated as 0

Either leave parameter 'Number of Days after Today' blank or set it to 0 to purge the previously loaded measure data for the current or today's date.

Troubleshooting Information

  • You can view the status of the Load Planning Data from Flat Files scheduled process in the Scheduled Processes work area.
  • Refer to the applicable FBDI template for information on which data columns in the CSV file are required, as well as the valid values, and date formatting specifics. For example, date fields must be in YYYY/MM/DD format.
  • The CSV files must be compressed before uploading them to Oracle Universal Content Manager. Only compressed files should be specified in the Data File parameter.
  • When uploading the ZIP file to Oracle Universal Content Manager, select the scm/planningDataLoader/import account for your uploaded file to be available for selection when running the Load Planning Data from Flat Files scheduled process.
  • You can use a ZIP file uploaded to Oracle Universal Content Manager only once. After you submit the Load Planning Data from Flat Files scheduled process specifying the ZIP file, the status of the ZIP file updates and cannot be used again.
  • When the value specified for the source system parameter is a Fusion source system, only a subset of entities can be loaded using the Load Planning Data from Flat Files scheduled process.

    For example, you cannot load customers using the Load Planning Data from Flat Files scheduled process if the source system you selected is an Oracle Fusion source system, because customers are loaded using the Collect Planning Data task.

    You can find a list of the valid collections entities that can be used for a Fusion source system in the Planning Data Collections chapter in the Using Supply Chain Planning Common Features guide.

  • Special characters can cause errors in the Load Planning Data from Flat Files scheduled process. To avoid errors:
    • If special characters exist within the data, enclose the whole string in double quotes.
    • Ensure your data doesn’t contain double quotes within the middle of a string.
  • If the ZIP file contains data that is used in planning hierarchies, such as organizations, items, customers, and suppliers, then the Create Trees for Dimensions scheduled process is submitted. If you don’t have the necessary privilege to run this job, it will fail. See the Privileges Required section for information on what privileges are needed.
  • You can only load measure data using the scpMeasuresImportTemplate if the measure is editable and the measure values are stored (versus using an expression or calculated by planning).

    Currently, the only way to confirm whether the measure is a stored measure is to run the following query: “select name, Measure_code from fusion.msc_measures_vl where store_result = 1".

    You can open the Collected Measure Data UI in plan inputs to view the measure data that’s been uploaded via the FBDI process.

  • Measure data purge for the specified time range is applicable only for the data loaded using CSV files generated from any of these measure templates:
    • Supply Chain Planning Measures (ScpMeasuresImportTemplate.xlsm)
    • Supply Chain Planning Bookings History (ScpBookingHistoryImportTemplate.xlsm)
    • Supply Chain Planning Shipments History (ScpShipmentHistoryImportTemplate.xlsm)
    • Supply Chain Planning Option Bookings History (ScpOptionBookingHistoryImportTemplate.xlsm)
    • Supply Chain Planning Option Shipments History (ScpOptionShipmentHistoryImportTemplate.xlsm)
    • Supply Chain Planning Forecast Measures (ScpForecastMeasureImportTemplate.xlsm)
    • Supply Chain Causal Factors (ScpCausalFactorsImportTemplate.xlsm)
  • The measure data purge time range determined from the Number of Days Before Today and Number of Days After Today parameters must align exactly with the time range for which you are populating data for a single or multiple time dimensioned measures in the CSV files. If the specified purge time range is greater than the time range for which you are collecting data, then you would lose the previously collected measure data for the non matching time periods. For example, November 1 to November 19, 2023, is set as the purge data time range based on your parameter inputs and you are loading data for the measure from November 6 to November 19, 2023. Subsequently, you'll lose all the previously loaded data for the time period November 1 to November 5, 2023.
  • If Enable Date Range for Purging Time-Dimensioned Measures is set to "No", previously loaded measure data doesn’t get purged for the specified time range.
  • In most cases, you can view the data loaded via the Load Planning Data from Flat Files scheduled process in plan inputs. However if you are loading safety stock using the Safety Stock import template, you must query the msc_safetystock_txns table to see the data that was loaded.
  • You can launch the Load Planning from Flat Files scheduled process using ERP Integrations REST Service (either directly or using Oracle Integration Cloud ERP Adapter). For more details about this approach, see the documentation on the ERP Integration Service REST service in the REST API for Oracle Fusion Cloud Financials guide.