1. Operations Guide
  2. Batch Processes

4 Batch Processes

This chapter provides an overview of the batch processes of Oracle Retail Allocation. It also provides information about the functions of the batch processes, the packages associated with the batches, and how to execute the Java-based batches.

Note:

Details around incoming/outgoing file storage are available in the "Merchandising File Transfer Service" section of the Merchandising Operations Guide - Vol 2.

Batch Processing Overview

Allocation contains a set of batch processes that are run in Java. Broadly, the batch processing falls into five categories:

  • Schedule Allocation batch - ScheduledAllocationBatchClient.java creates the child allocations for parent allocations that are scheduled for the day.

  • Daily Cleanup batch - SessionCleanUpBatchClient.java deletes data from the temporary tables used by the Allocations and Calculation engine.

  • Purge batches - Purge batches delete Allocation and Worksheet data from the Allocation tables, which were created before a certain time period.

  • Rule Level On Hand (RLOH) batches - For RLOH, there are six batch update processes that share the same java batch file; InventorySnapshotBatchClient.java.

  • Dashboard Refresh batch - The Dashboard Refresh batch refreshes both Stock to Sales and Top to Bottom Dashboard reports Data.

Batch Processes

The following table describes Oracle Retail Allocation's batch processes:

Table 4-1 Allocation's Batch Process and associated Java Packages

Batch Name Batch Process Package

Schedule Allocation Batch

ScheduledAllocationBatchClient.java

oracle.retail.apps.alc.batch.client

Daily Cleanup Batch

SessionCleanUpBatchClient.java

oracle.retail.apps.alc.batch.client

Purge Batches

PurgeBatchRunnable.java

oracle.retail.apps.alc.batch.client

RLOH Batch Update

InventorySnapshotBatchClient.java

oracle.retail.apps.alc.batch.client

Dashboard Refresh Batch

AlcDashboardCleanUp.ksh

oracle.retail.apps.alc.batch.cl ient

Running a Java-based Batch Process

To run a Java-based batch process, Oracle Retail provides sample shell scripts (.sh files) and batch files (.bat files). These sample shell scripts must be modified according to the retailer's installation. They perform the following internally:

  • Set up the Java runtime environment before the Java process is run.

  • Trigger the Java batch process.

Scheduler and Command Line

If the retailer uses a scheduler, arguments are placed into the scheduler.

If the retailer does not use a scheduler, arguments must be passed in at the command line.

For UNIX systems, the Java process is scheduled through an executable shell script (.sh file).

Note:

The AllocScheduleBatch.ksh and AlcDailyCleanUp.ksh batches can be run by an external scheduling system or a simple UNIX CRON job.

Running the Dashboard Refresh Batch

Take the following steps to run the Daily Cleanup batch:

  1. Login to the application server machine using <username>/<password>.

  2. Navigate to the batch folder. In the batch folder, verify that the AlcDashboardCleanUp.ksh file is present.

  3. Run the AlcDashboardCleanUp.ksh batch using the following command:

    ksh AlcDashboardCleanUp.ksh <systemadministratoralias>

The batch runs by taking the batch user from wallet.

Running the Schedule Allocation Batch

Installation and build scripts create the required user for running the batch in the wallet. There is no way you can cross check to determine whether the user is created inside the wallet other than running the batch scripts. However, you can see if the wallet is present in the environment by checking the wallet location. The wallet location is present in batch.properties file. The wallet is created with a user_id, password and an alias name.

Once the wallet is created the csm.wallet.path key in batch.properties file should be updated.

Only those users who have their role mapped to the SYSTEM_ADMINISTRATOR_JOB enterprise role in LDAP, have the privilege to execute the Schedule Allocation batch script. During installation, Allocation creates the SYSTEM_ADMINISTRATOR user, by default, in the Retail Wallet, which is mapped to the SYSTEM_ADMINISTRATOR_JOB enterprise role in LDAP. An alias for any new user mapped to SYSTEM_ADMINISTRATOR_JOB role in LDAP has to be created in the Wallet in order to execute the Schedule Allocation batch script.

Note:

Use the save_credential.sh script to create a new user in the Wallet. For more information on instructions to run the save_credential.sh script to add a new user, see the Oracle Retail Allocation Installation Guide.

The batch.properties file exposes a few configuration parameters related to concurrency management or parallel execution which need to be tuned by the retailer based on the volume of transactions. The concurrent processing in batch is implemented leveraging the standard Java Executor service APIs. The sample file with default configurations will be made available and need to be modified by the retailer to suit to their requirements.

The section below describes the properties that can be configured.

  • initialThreadLimit: Initial number of threads in the pool that are available to create child allocations. The default value is 5.

  • maxThreadLimit: Maximum number of threads that can be allowed in the pool. The default value is 10.

  • queueLimit: Size of queue of pending tasks to create child. The default value is 1.

  • providerUrl: Url of the server module (for example, t3://<weblogic host>:<port>). This parameter has to be configured by the retailer to point to the WebLogic Server on which Asynchronous application instance is deployed.

  • csm.wallet.partition.name: Partition name in the wallet (for example, alloc13)

  • csm.wallet.path: Location of Wallet

  1. Login to the application server machine using <username>/<password>.

  2. Navigate to the batch folder. If the batch folder is not found, the batch installation did not occur properly. In the batch folder, verify that the AllocScheduleBatch.ksh file is present.

  3. Run the AllocScheduleBatch.ksh batch using the following command: 

    ksh AllocScheduleBatch.ksh <systemadministratoralias>

    The batch runs by taking the batch user from wallet.

Running the Daily Cleanup Batch

Take the following steps to run the Daily Cleanup batch:

  1. Login to the application server machine using <username>/<password>.

  2. Navigate to the batch folder. In the batch folder, verify that the AlcDailyCleanUp.ksh file is present.

  3. Run the AlcDailyCleanUp.ksh batch using the following command:

    ksh AlcDailyCleanUp.ksh <systemadministratoralias>

    The batch runs by taking the batch user from wallet.

Running the Purge Batches

Use the following steps to run the Purge batches:

  1. Login to the application server machine using <username>/<password>.

  2. Navigate to the batch folder. In the batch folder, verify that the AlcPurgeAlloc.ksh and AlcPurgeWksht.ksh files are present.

  3. Run the both batch processes using the following command:

    ksh AlcPurgeAlloc.ksh <systemadministratoralias> PURGE_ALLOC
ksh AlcPurgeWksht.ksh <systemadministratoralias> PURGE_WORKSHEET

    The batch runs by taking the batch user from wallet.

Running the Rule Level On Hand Batch

Take the following steps to run the RLOH batch:

  1. Login to the application server machine using <username>/<password>. Once logged in, the default folder is /home/alcbatch.

  2. Before running the batch, make sure that all the corresponding profile properties are set. For that run the profile file first. Go to the Profiles folder inside alcbatch.

  3. If there are multiple environments, there are separate profile files for every machine (for example, QA, DEV, TEST). Make sure to identify the right profile file here. Most likely it will be the name of the environment, run the profile file - ./alc132Linuxdev (for example, Dev 13.2 Env).

  4. After running the profile successfully, go back to alcbatch. There are separate folders for every machine's batch under the alcbatch folder. Go to the current machine's folder. (Most likely the folder name would be same as your profile file name, in this case alc132Linuxdev).

  5. Run the following scripts inside the batch folder in the following order:

    • ksh AlcSnapshotSOH.ksh <BatchUserAlias>

    • ksh AlcSnapshotOnOrder.ksh <BatchUserAlias>

    • ksh AlcSnapshotAllocIn.ksh <BatchUserAlias>

    • ksh AlcSnapshotCrosslink.ksh <BatchUserAlias>

    • ksh AlcSnapshotAllocOut.ksh <BatchUserAlias>

    • ksh AlcSnapshotCustomerOrder.ksh <BatchUserAlias>

Summary of Executable Files

The following table describes the executable shell scripts and batch files:

Table 4-2 Scripts to initiate the deletion process

Executable Shell Scripts (UNIX) Executable Batch File For Windows Description

AllocScheduleBatch.ksh

No batch file is available

Triggers the schedule batch client.

AllocBatch.ksh

No batch file is available

Configures the environment variables sourced by other batch scripts. This script is not to be run/scheduled in a stand alone mode.

AlcSnapshotSOH.ksh

No batch file is available

AlcSnapshotOnOrder.ksh

No batch file is available

AlcSnapshotAllocOut.ksh

No batch file is available

AlcSnapshotCustomerOrder.ksh

No batch file is available

AlcSnapshotCrosslink.ksh

No batch file is available

AlcSnapshotAllocIn.ksh

No batch file is available

AlcDailyCleanUp.ksh

No batch file is available

Deletes data from the temporary tables.

AlcPurgeAlloc.ksh

No batch file is available

Deletes old Allocations from database table

AlcPurgeWksht.ksh

No batch file is available

Deletes old Worksheets from database table

AllocScheduleBatch Process Batch Design

The Allocation Auto Scheduler creates child allocations on pre-defined days of the week set by the Allocation user within the user interface. These allocations are created from an existing parent allocation. The auto creation of the child allocations must be called daily via a batch process at a scheduled time, set by the system administrator.

This process needs to be scheduled to run every day (using an external scheduling framework or UNIX CRON job).

Usage

The following command runs the AllocScheduleBatch job:

AllocScheduleBatch.ksh userAlias

Detail

This script is present under the $ALLOCHOME/batch folder.

Log File

log4j.xml is present under $ALLOCHOME/properties folder. This file is edited to specify desired log file location and name. To perform this action, change the value against param with name="file" in log4j.xml. Make sure that folder is already present on the file system and the batch user has write permission. Default value is set to ../logs/alloc133.log.

Properties File

The default batch properties file is present under $ALLOCHOME/properties/oracle/retail/alloc/batch.properties.

The properties below are defined. The default value may be edited.

  • initialThreadLimit initial number of threads in the pool that are available to create child allocations. The default value is 5.

  • maxThreadLimit maximum number of threads that can be allowed in the pool. The default value is 10.

  • queueLimit size of queue of pending tasks to create child. The default value is 1.

  • initialContextFactory specifies the JNDI context factory class (this should not be changed).

  • providerUrl url of the server module (e.g t3://<weblogic host>:<port> ). This parameter has to be configured by the retailer to point to the WebLogic Server on which the asynchronous application instance is deployed.

  • csm.wallet.partition.name is the partition name in the wallet that stores the credentials to authenticate batch user on WebLogic. For example, alloc13

  • csm.wallet.path is the path of the wallet file that stores WebLogic credentials.

Configuration

$ALLOCHOME/batch/AllocBatch.ksh should be edited by the retailer to specify appropriate value of following environment variables

  • ALLOCHOME: directory where batch client in installed

  • JAVA_HOME: directory where JDK is installed

Assumptions and Scheduling Notes

This job should be scheduled to run every day at the same time.

AlcDailyCleanUp Process Batch Design

Allocation has a number of temporary tables that store intermediate data while creating allocations and while performing calculations. The Daily Cleanup batch process deletes data from these temporary tables. Run this batch immediately after you run the Schedule Allocation batch.This process should be scheduled to run every day (using an external scheduling framework or UNIX CRON job).

Make sure you run this process while all users are offline from the system.

Usage

The following command runs the AlcDailyCleanUp job:

AlcDailyCleanUp.ksh <BatchUserAlias>

Detail

This script is present under the $ALLOCHOME/batch folder.

The temporary tables which are impacted by the AlcDailyCleanUp process are as follows:

Allocation session tables:

  • ALC_SESSION_SIZE_PROFILE_RATIO

  • ALC_SESSION_SIZE_PROFILE

  • ALC_SESSION_QUANTITY_LIMITS

  • ALC_SESSION_ITEM_LOC_EXCL

  • ALC_SESSION_ITEM_LOC

  • ALC_SESSION_GID_PROFILE_LIST

  • ALC_SESSION_GID_PROFILE

Worksheet session tables:

  • ALC_WORK_SESSION_ITEM_LOC

  • ALC_WORK_SESSION_ITEM_ALL

  • ALC_WORK_SESSION_ITEM

Temporary tables:

  • ALC_LOAD_TEMP

alc_calc_destination_temp

alc_calc_need_temp

alc_calc_rloh_temp

alc_calc_qty_limits_temp

alc_calc_rloh_item_temp

alc_merch_hier_rloh_temp

alc_calc_source_temp

alc_calc_need_dates_temp

Allocation approval tables:

  • ALC_SYNC_HEADER_TEMP

  • ALC_SYNC_DETAIL_TEMP

AlcPurgeAlloc AlcPurgeWksht Batch Processes Design

Allocation has a number of temporary tables that store intermediate data while creating allocations and while performing calculations. The Purge batch process deletes data from these temporary tables. Run this batch immediately after you run the Schedule Allocation batch. This process should be scheduled to run every day using an external scheduling framework. Make sure you run this process while all users are offline from the system.

Along with the above mentioned capability, this batch also allows provides for deletion of older allocations and worksheets created as a part of the Allocation application.

Usage

The following command runs the job:

AlcPurgeAlloc.ksh <systemadministratoralias> PURGE_ALLOC

AlcPurgeWksht.ksh <systemadministratoralias> PURGE_WORKSHEET

Details:

Allocation deletions are driven by the system option ALLOCATION_RETENTION_DAYS. Allocations exceeding the retention parameter become purge candidates as follows:

  • Scheduled Allocations Parents are deleted when their scheduled end date is greater than the allocation retention days parameter.

  • Allocations that are linked to Merchandising allocations in the ALC_XREF table are deleted when the Merchandising allocations they are linked to no longer exist in Merchandising.

  • Allocations that are not linked to Merchandising allocations in the ALC_XREF table are deleted when they have not been modified (ALC_ALLOC.LAST_UPDATE_DATE) for ALC_SYSTEM_OPTIONS.TP_ALLOC_RETENTION_DAYS days.

  • Allocations in Deleted status - user deleted through the UI.

Worksheets not associated to an allocation (WK worksheets) are deleted based on this setting. Worksheets associated to an allocation (WD worksheets) are deleted when the allocation they are related to is deleted (they follow Allocation deletion). Worksheet deletion is driven by a system option, WORKSHEET_RETENTION_DAYS. Worksheets purge criteria is as follows:

Worksheets not tied to an allocation (type = WK) are deleted when they are not be modified (ALC_WORK_HEADER.UPDATED_DATE) for TP_WORKSHEET_RETENTION_DAYS days.

Rule Level On Hand Pre-Aggregation Inventory Snapshot Batch Design

This batch process addresses the most significant performance issue within the Allocation product, the rule level on hand (RLOH) logic. This functionality requires current and future inventory lookups for potentially entire departments.

Inventory is currently only held in Merchandising at the transaction level item level. Departments in Merchandising can have tens of thousands of items under them. Multiply this by the hundreds of locations that can be on an allocation and RLOH can easily end up needing to retrieve inventory for millions of item/location combinations.

Usage

Six separate executables are called by one java batch process. The executables and the commands to run them are as follows:

  • AlcSnapshotOnOrder.ksh 

    ./AlcSnapshotOnOrder.ksh <BatchUserAlias>

  • AlcSnapshotCrosslink.ksh

    ./AlcSnapshotCrosslink.ksh <BatchUserAlias>

  • AlcSnapshotAllocIn.ksh

    ./AlcSnapshotAllocIn.ksh <BatchUserAlias>

  • AlcSnapshotSOH.ksh

    ./AlcSnapshotSOH.ksh <BatchUserAlias>

  • AlcSnapshotAllocOut.ksh

    ./AlcSnapshotAllocOut.ksh <BatchUserAlias>

  • AlcSnapshotCustomerOrder.ksh

    ./AlcSnapshotCustomerOrder.ksh <BatchUserAlias>

A remote interface can be called for each batch 

public interface IInventorySnapshotCoreRemote {
 
    public void createItemLocSOHSnapshot() throws AllocRemoteException;
    
    public void createOnOrderSnapshot() throws AllocRemoteException;
    
    public void createAllocInSnapshot() throws AllocRemoteException;
 
    public void createCrosslinkInSnapshot() throws AllocRemoteException;
 
    public void createAllocOutSnapshot() throws AllocRemoteException;
 
    public void createCustomerOrderSnapshot() throws AllocRemoteException;
}

Each method lines up with the appropriate PL-SQL function.

Detail

Retrieving inventory requires accessing four very large Merchandising tables:

  • ITEM_LOC_SOH - current inventory and components of future inventory

  • ORDLOC - on order component of future inventory

  • ALLOC_DETAIL - allocation in component of future inventory

  • TSFDETAIL - crosslink transfer component of future inventory

To improve RLOH performance, four new subclass level aggregated tables are created for use by Allocation

Table 4-3 RLOH Aggregated Tables

Table Candidate Key Source Tables

SUBCLASS_ITEM_LOC_SOH_EOD

Dept

Class

Subclass

Loc

ITEM_LOC_SOH

ITEM_MASTER

SUBCLASS_ON_ORDER_EOD

Dept

Class

Subclass

Loc

On_order_date

ORDLOC

ORDHEAD

ITEM_MASTER

PACKITEM_BREAKOUT

SUBCLASS_ALLOC_IN_EOD

Dept

Class

Subclass

Loc

Alloc_in_date

ALLOC_DETAIL

ALLOC_HEADER

ITEM_MASTER

PACKITEM_BREAKOUT

ORDHEAD

TSFHEAD

SUBCLASS_CROSSLINK_EOD

Dept

Class

Subclass

Loc

TSFHEAD

TSFDETAIL

ITEM_MASTER

PACKITEM_BREAKOUT

SUBCLASS_ALLOC_OUT_EOD

Dept

Class

Subclass

Loc

Alloc_Out_Date

ALLOC_DETAIL

ALLOC_HEADER

ITEM_MASTER

ORDHEAD

PACKITEM_BREAKOUT

ALC_SUBCLASS_CUST_ORDER_EOD

Dept

Class

Subclass

Loc

TSFHEAD

TSFDETAIL

ITEM_MASTER

PACKITEM_BREAKOUT

These tables are populated by the ALC_HIER_LVL_INV_SNAPSHOT_SQL package (called by a batch program) nightly.

Package Details

The package that needs to be called is ALC_HIER_LVL_INV_SNAPSHOT_SQL.

SQL> desc ALC_HIER_LVL_INV_SNAPSHOT_SQL
FUNCTION ROLLUP_ALLOC_IN RETURNS NUMBER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
O_ERROR_MESSAGE                VARCHAR2                IN/OUT
FUNCTION ROLLUP_CROSSLINK_IN RETURNS NUMBER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
O_ERROR_MESSAGE                VARCHAR2                IN/OUT
FUNCTION ROLLUP_IL_SOH RETURNS NUMBER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
O_ERROR_MESSAGE                VARCHAR2                IN/OUT
FUNCTION ROLLUP_ON_ORDER RETURNS NUMBER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
O_ERROR_MESSAGE                VARCHAR2                IN/OUT
------------------------------ ----------------------- ------ --------
ROLLUP_ALLOC_OUT (FUNCTION)<return value>  NUMBER   		   OUT 
ROLLUP_ALLOC_OUT                 
O_ERROR_MESSAGE                VARCHAR2                IN/OUT 
------------------------------ ----------------------- ------ --------
ROLLUP_CUSTOMER_ORDER (FUNCTION) <return value>  NUMBER   OUT 
ROLLUP_CUSTOMER_ORDER                 
O_ERROR_MESSAGE                VARCHAR2                IN/OUT 
------------------------------ ----------------------- ------ --------

There are six functions in the package. Each of the four functions in the package should be called by its own batch program.

  • AlcSnapshotSOH

  • AlcSnapshotAllocIn

  • AlcSnapshotOnOrder

  • AlcSnapshotCrosslink

  • AlcSnapshotAllocOut

  • AlcSnapshotCustomerOrder

Implementation

There are six different batch executables, each executable calling the appropriate method from the above ALC_HIER_LVL_INV_SNAPSHOT_SQL package. Clarifications on the batch functionality:

  • Each batch should state success or failure, whether an exception is caught or not.

  • There is no need for restart recovery, intermittent commits, or threading.

  • Login validation standard logic used by the scheduled alloc program should be applied here as well.

  • The programs are run sequentially. The correct order is documented in the Merchandising batch schedule and controlled by whichever scheduling tool used at a particular customer.

  • There are no special security requirements for the program. Any user who can log into the Allocation product can have the ability to run the batch processes.