6 AIP Java/Oracle Batch Process Flow

The top level control scripts (cron_export.sh, cron_import.sh, cron_import_order.sh, pre_scale.sh, smooth_order.sh, scale_order.sh, post_scale.sh, cron_release.sh, cron_post_release.sh, and cron_purge.sh) are provided with the AIP installation. They may be used to run the AIP Oracle batch process from a UNIX scheduler. There are many other internal scripts called by these scripts that may have multi-threaded process calls; however, all the scripts called directly by cron_export.sh, cron_import.sh, cron_import_order.sh, cron_release.sh, and cron_purge.sh are run inline.

Table 6-1 describes the three categories of batch scripts based on their execution frequency.

Table 6-1 Batch Script Categories

Batch Scripts	Description
Intra-day Batch	These scripts are used for Intra-day batch processes and can be called multiple times in a single day. They are executed once for each Intra-day batch run. The Intra-day batch scripts currently apply to into-store purchase orders and into-store transfers only.
Overnight/Regular Batch	These scripts are used for regular overnight batch run and are executed once-a-day in normal mode. Some of these can be used in Intra-day mode also when executed with optional release wave parameter. These scripts apply to both into-store and into-warehouse type purchase orders and transfers.
Weekly/Monthly Batch	These scripts are executed less frequently like once-a-week or once-a-month.

Batch Scripts

Description

Intra-day Batch

These scripts are used for Intra-day batch processes and can be called multiple times in a single day. They are executed once for each Intra-day batch run.

The Intra-day batch scripts currently apply to into-store purchase orders and into-store transfers only.

Overnight/Regular Batch

These scripts are used for regular overnight batch run and are executed once-a-day in normal mode. Some of these can be used in Intra-day mode also when executed with optional release wave parameter.

These scripts apply to both into-store and into-warehouse type purchase orders and transfers.

Weekly/Monthly Batch

These scripts are executed less frequently like once-a-week or once-a-month.

Note:

The system currently does not permit Intra-day batch runs without overnight batch run. There must be an overnight/regular batch run first before the Intra-day batch runs can be scheduled. If no Intra-day batch run is desired, the overnight/regular batch scripts should be executed in regular mode only. Table 6-2 lists all the important batch scripts in sequence along with their execution frequency.

Execution Frequency of AIP Java/Oracle Batch Scripts

Table 6-2 provides information about the Execution Frequency of the AIP Java/Oracle batch scripts.

Table 6-2 AIP Java/Oracle Batch Scripts

Script Call	Description of Action	Initial Batch Run	Execution Frequency
batch_lock.sh Examples: batch_lock.sh -l 7 (release wave 7 lock) batch_lock.sh -l -1 (overnight lock)	Sets a lock indicating that batch is running. The lock prevent users from performing any save operations during overnight batch or from releasing orders early that are being re-planned Intra-day.	No	Intra-day, Daily
scripts/vdate.sh Examples: vdate.sh, vdate.sh inc, vdate.sh set export 20110331	Set/Get a specific AIP batch date. When used with inc, increment existing date. VDATE remains same during AIP-Oracle and AIP-RPAS batch processes even when actual systems date changes.	Yes	Daily
cron_export.sh	Exports all the DM and hierarchy information from AIP-Oracle to AIP-RPAS. Prepares Oracle database for next scaling and smoothing runs.	No	Daily
cron_import.sh dm	Imports all the DM and hierarchy information from AIP-RPAS into the AIP-Oracle database.	Yes	Daily
cron_import.sh sku-attributes	Imports the SKU cost, weight, volume information from external system into AIP-Oracle for PO scaling.	Yes	Daily
cron_import.sh om	Imports OM from external system into AIP-Oracle data for updating order details in the Oracle database.	No	Daily
pre_scale.sh	Prepares database for scaling the warehouse POs that will be loaded from AIP RPAS. Required only when scaling is desired.	No	Daily
prepare_for_intra_day_release.sh -w <wavenumber>	This script prepares Oracle database for loading and releasing given Intra-day wave. It should be run every time before loading Intra-day wave orders. Not needed for overnight load and release.	No	Intra-day
cron_import_order.sh -o transfer -d store -w <wavenumber>	Imports RPAS generated into-store transfers into AIP Oracle tables. When optional parameter wavenumber is passed, the script runs in Intra-day mode.	No	Intra-day, Daily
cron_import_order.sh -o transfer -d warehouse	Imports RPAS generated overnight into-warehouse transfers into AIP Oracle tables.	No	Daily
cron_release.sh -o transfer -d store -w <wavenumber>	Release into-store overnight or Intra-day (when optional parameter wavenumber) transfers to the tsf_mfqueue table.	No	Intra-day, Daily
cron_release.sh -o transfer -d warehouse	Release into-warehouse overnight transfers to the tsf_mfqueue table.	No	Daily
cron_import_order.sh -o purchase -d store -w <wavenumber>	Imports RPAS generated into-store overnight or Intra-day (when optional parameter wavenumber) purchase orders into AIP Oracle tables.	No	Intra-day, Daily
cron_release.sh -o purchase -d store -w <wavenumber>	Release into-store overnight and Intra-day purchase orders to the po_mfqueue table. When optional wavenumber parameter is passed, the script runs in Intra-day mode releasing Intra-day orders.	No	Daily, Intra-day
cron_import_order.sh -o purchase -d warehouse	Imports RPAS generated into-warehouse overnight purchase orders into AIP Oracle tables.	No	Daily
smooth_order.sh	Performs Order Smoothing after importing overnight into-warehouse POs.	No	Daily
scale_order.sh	Performs Supplier Scaling and/or Container Scaling after smoothing.	No	Daily
merge_order.sh	Moves scaled into-warehouse POs to the final database table for Release.	No	Daily
cron_release.sh -o purchase -d warehouse	Release into-warehouse overnight purchase orders to the po_mfqueue table.	No	Daily
tsf_po_export.sh -o all -d all	Execute only when overnight released orders are published to RMS through a flat file.	No	Daily
post_scale.sh	Exports modified warehouse POs as a result of smoothing and scaling and executed SPQ quantities to AIP-RPAS.	No	Daily
cron_post_release.sh -o all -d all	Performs following overnight post-release activities: Identifies overdue orders, Updates order history of into-warehouse orders, Refreshes into-warehouse forecast transfers in non_contents_order table.	No	Daily
batch_lock.sh -u	Clears the overnight or Intra-day lock set earlier.	No	Intra-day, Daily
cron_purge.sh -o all -d all	Deletes closed purchase orders and/or transfers that have exceeded their defined purge age. This script can be run in non-critical window.	No	Daily
cron_import.sh alerts	Imports DM alerts generated by AIP RPAS batch.	Yes	Daily
purge_log.sh	Purges the PLSQL batch log data. This can be run in non-critical window.	No	Daily
scripts/aip_purge.sh	Depending upon purge age parameters, this script purges the invalid or ineffective hierarchy and measure data from Oracle tables in order to maintain database tablespace. This can be scheduled to run less frequently like once a week or once a month.	No	Weekly or Monthly
generate_sched.sh	Generates order schedule based on order cycles and supply chain constraints.	No	Daily
cron_export_sched.sh	Extracts schedules for AIP RPAS.	No	Daily
generate_dcrp.sh	Generate receipt plans based on order details received from AIP RPAS and RMS.	No	Daily, Intra-day
cron_export_dcrp.sh	Extracts detailed cross dock constraint receipt plan AIP RPAS use.	No	Daily, Intra-day

Directory Structure

In addition to the scripts listed in Table 6-2, there are some additional control scripts and directories present in the integration home of AIP Oracle platform. Table 6-3 lists the extra files and directories in integration home.

Table 6-3 Directory Structure for the AIP Java/ Oracle Platform

Files and Directories	Explanation
aip_common_online.sh	This is a common script sourced by all AIP-Oracle shell scripts.
aip_env_online.sh	This is environment script that has DB alias, location of RETL configuration file, and so on. This script is sourced by aip_common_online.sh.
config.xml	The RETL configurations file containing database connection information.
data/	The directory that is used to store the tarred and compressed inbound data files for loading into AIP online by cron_import.sh and cron_import_order.sh scripts.
schema/	The directory of schema (xml) files used by RETL to load into and extract data from AIP online tables.
scripts/	The location of automation scripts for data processing.
temp/	The temporary folder required for Batch Script Architecture (BSA).
config/	The location of configuration files, such as import_dm.config, and so on.
logs/	The location of log files.
archive/	The location of the archived input data files.

Prerequisites for AIP Java/ Oracle Platform

The machine running interface scripts must have the ability to schedule jobs to execute scripts on a timed schedule.

RETL must be installed and available in the PATH. See the section, ”Compatibility and Hardware Requirements” of the Oracle Retail Advanced Inventory Planning Installation Guide for the supported version of RETL for this release.

The database schema must be installed and built with all interface procedures compiled and available. The database credentials should be secured in Oracle Wallet. Various parts of AIP-Oracle application assumes the availability of wallets alias to access the database.

AIP Java/Oracle Batch Process Flow

Figure 6-1 illustrates the AIP Java/Oracle batch process flow.

Figure 6-1 AIP Java/Oracle Batch Process Flow

Batch Process Description

The Oracle batch process consists of a series of data processing and transport (import/export) to and from AIP RPAS.

It begins with AIP Oracle sending Data Management setup information to AIP RPAS. AIP RPAS in turn uses that information to generate other setup information and exports them back to AIP Oracle to be used for schedule calculation.

Once AIP Oracle generates schedules, they are sent back to AIP RPAS. AIP RPAS uses schedule date to generate constrained receipt plans (CRP). CRPs are passed back to AIP Oracle to generate detailed constraint receipt plans (DCRP). DCRPs are then used as is input to order release and also sent back to AIP RPAS for further processing.

Into-store orders and into-store transfers set for replan can be replanned and released during the day (Intra-day) after overnight orders are released. While Intra-day replanning happens on the AIP-RPAS side, the Intra-day release occurs on the AIP-Oracle side. During Intra-day batch, only the replanned orders generated in AIP-RPAS are exchanged between the two sides.

Export from AIP Online to RPAS

To export from AIP Online to RPAS, perform the following procedure:

At the end of the business day, user-entered data from the AIP Online application must be transferred to RPAS before AIP RPAS batch begins.
The cron_export.sh process begins by running a pre-export, pre_aiponline_extract.sh, script. This script runs clean-up in the outbound directory for any data files from a previously failed export run. If cron_export.sh completes successfully, there should be no data files left in the outbound directory. The presence of these files can end up creating incorrect *.tar.Z files.
Step 2 is followed by a SQL query that resets required parameters in the Oracle database, including date and horizon information.
Data is then extracted from the appropriate tables and stored in .dat files. When the export is complete, the .dat files are compressed and tarred into .tar.Z files. and then waits for retrieval to be processed by RPAS interface scripts. In between the hierarchy and DM export calls, PLSQL functions are called to build scalable and smoothable assignments to prepare for next smoothing and scaling batch run.
After export is complete, the last_export parameter is reset in the Oracle database.
A log file is generated at ${INTEGRATION_HOME}/logs.

Import from RPAS and External System into AIP Online

To import from RPAS and external system into AIP Online, perform the following procedure:

After AIP RPAS batch has successfully completed, the AIP RPAS interface processes generate files for loading into the Oracle-based AIP Online application.

By default, these files are compressed and placed in the AIP RPAS domain. Specifically, the files are placed in the $AIPDOMAIN/interface/export directory. The names of the files are dm.tar.Z, dm_alerts.tar.Z, and srp.tar.Z.

Both overnight and Intra-day orders use the same tar filename, that is srp.tar.Z. All of these files are created and processed at different times of batch processing.
The inbound files from the RPAS server should be retrieved by FTP using a job scheduling application at different times.
Once files are successfully transferred and the scheduled job begins the cron_import.sh or cron_import_order.sh scripts, the files are copied to $BSA_ARCHIVE_DIR with new file names carrying timestamp. For example: dm.tar.Z.<timestamp>, dm_alerts.tar.Z.<timestamp>, and srp.tar.Z.<timestamp> and so on.
These files are unpacked into $ONL_INBOUND_DIR and the original tar.Z archives are removed.
Once the data files are extracted from the loading scripts cron_import.sh or cron_import_order.sh load the data from .dat files into the Oracle tables.

Cron_import.sh loads the dm.tar.Z and dm_alerts.tar.Z. cron_import_order.sh loads the srp.tar.Z.
A log file is generated at ${INTEGRATION_HOME}/logs.
External systems also provide data that loads directly into the Oracle database. These files (such as recycled order number, intransit/received quantities, sku-attributes, and so on) must be placed in the ONL_INBOUND_DIR by a job scheduling application task or the sending application.

These data files are loaded into Oracle database using cron_import.sh script.

Smooth and Scale Purchase Orders

After AIP-RPAS generated overnight warehouse purchase orders are loaded into Oracle tables, that is after completing cron_import_order.sh for overnight warehouse purchase orders, they go through order smoothing module followed by order scaling and container scaling module. Both order smoothing and scaling modules attempts to move orders to earlier delivery dates in order to meet their respective constraints.

Before smoothing occurs pre-scaling process prepares the database for scaling and smoothing prior to entering the critical batch window. At the completion of scaling the orders which have been moved or modified by smoothing or scaling are exported out of the oracle database and archived in the ocs.tar.Z file. This file must be moved to the AIP RPAS inbound directory for the final load step.

Purchase Order/Transfer Release from AIP Online to Merchandising System

The into-store and into-warehouse PO/TSF release processes are both preceded by validation to ensure that order definitions exist, order purge periods exist, and so forth. Both processes populate the PO_MFQUEUE and TSF_MFQUEUE tables, which are queried by the OrderSenderBean.

The Intra-day and overnight batch follow the same data flow in this regards.