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:
-
Login to the application server machine using <username>/<password>.
-
Navigate to the batch folder. In the batch folder, verify that the AlcDashboardCleanUp.ksh file is present.
-
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
-
Login to the application server machine using <username>/<password>.
-
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. -
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:
-
Login to the application server machine using <username>/<password>.
-
Navigate to the batch folder. In the batch folder, verify that the
AlcDailyCleanUp.ksh
file is present. -
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:
-
Login to the application server machine using <username>/<password>.
-
Navigate to the batch folder. In the batch folder, verify that the AlcPurgeAlloc.ksh and AlcPurgeWksht.ksh files are present.
-
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:
-
Login to the application server machine using <username>/<password>. Once logged in, the default folder is /home/alcbatch.
-
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.
-
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).
-
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).
-
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).
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.
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.
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.