4 Batch Processes

This chapter provides the following:

An overview of SIM's batch processing
A summary of SIM batch lists
Batch scheduling and dependency list
A description of batch detail and how to run batch processes, along with key parameters

SIM batches are executed as Java batch processes. Most of the Java batch processes engage in some processing of their own. However, the majority of work is done by services running on the SIM server; the Java batch processes make remote calls to the server to access these services.

Note the following characteristics of SIM's Java batch processes:

They are not accessible through a graphical user interface (GUI).
They are scheduled by the retailer.
They are designed to process large volumes of data, depending upon the circumstances and process.

Running a Batch Process

SIM batch programs are run or scheduled through executable shell scripts (.sh files). Oracle Retail provides shell scripts for each SIM batch program.

The SIM batch program location is referred to as sim-batch-dir for the remainder of this chapter.

See the ”SIM Batch Scripts” in the Oracle Retail Store Inventory Management Installation Guide for batch install locations.

Each batch script performs the following internally:

Set up the class path before the Java process is run.
Start the Java batch process.

Do the following to configure a batch environment:

Batch user logs in as valid batch user to the machine where SIM batch scripts are installed. The batch user must have permission to execute the SIM shell script.
Set JAVA_HOME environment variable and add $JAVA_HOME/bin in the PATH environment variable. For example:
```
JAVA_HOME=<jre location>
PATH=$JAVA_HOME/bin:$PATH  
export PATH JAVA_HOME
```
Note:
This command can be saved in a .profile file; the batch user can execute .profile before running SIM batches.
Execute the batch script from <sim-batch-dir>/bin.

For more information about batch usage, see the "Batch Details" in this chapter.

Scheduler and the 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.

Return Value Batch Standards

The following guidelines describe the function return values and the program return values that SIM's batch processes utilize:

0 - The function completed without error, and processing should continue normally.
1 - A non-fatal error occurred (such as validation of an input record failed), and the calling function should either pass this error up another level or handle the exception.

Batch Logging

Relevant progress messages are logged with regard to batch program runtime information. The location of sim batch log and logging levels can be configured in log4j.xml file which is located at <sim-batch-dir>/resources directory.

The user running the batch process must have write permission on the directory into which the sim batch log is written, or the batch process will not run. If it is not acceptable to give the batch user permission for the default log directory, log4j.xml must be configured to use a different directory.

For more information, see the "Logging Information".

Note:

Some batch programs evoke Oracle stored procedure which runs on the Oracle database server, the log generated by the Oracle process may exist in different location which can be accessed by the Oracle database process. The log location is specified in batch detail section if it is different from the default batch log location.

Summary of SIM Batch List

Table 4-1 summarizes SIM's batch programs and includes a description of each batch program's business functionality. For a batch purge program list, see the "Batch Process Scheduling Notes".

Table 4-1 Batch Process Business Functionality and Dependencies

Batch Name	Description	Dependencies
AutoReceiveTransferDeliveries	This batch process auto-receives transfer deliveries.	No dependencies
AutoReplenishCapacity	The batch process auto-replenish shop-floor according to the capacity setup.	No dependencies
AutoTicketPrint	The batch process prints tickets.	No dependencies
CleanupShelfReplenishment	The end of day batch process runs at the end of each day to reset the delivery bay and close any open pending shelf replacements.	No dependencies
ClearancePriceChange	This batch process imports the clearance price changes set up in a price management system. SIM uses this data to update the price information of the items.	No dependencies
CloseDSDReceivings	This batch process auto confirms all the deliveries based on the system configuration.	No dependencies
CloseProdGroupSchedule	This batch process closes the product group schedule.	No dependencies
CloseTransferDeliveries	This batch process closes the transfer deliveries based on the store parameter " Auto Close Receipt."	No dependencies
CloseTransfers	This batch process will close transfers that have passed their not after date and are in valid state for closure.	No dependencies
CloseVendorReturn	This batch process will close all vendor returns that meet the closure business criteria.	No dependencies
DeactivateOldUsers	This batch program deactivates users when their end dates have reached specified date.	No dependencies
DexnexFileParser	This batch imports the direct delivery shipment records (PO, shipment and receipt) from Dex/Nex files.	No dependencies
ExtractUnitAmountStockCount	This batch generates UnitAmount stock counts.	No dependencies
ExtractUnitStockCount	This batch generates Unit stock counts.	No dependencies
FulfillmentOrderPickReminders	This batch sends out e-mail alerts for fulfillment order picks for which create date has expired.	No dependencies
FulfillmentOrderReminders	This batch process sends out e-mail alerts for fulfillment orders for which create date has expired.	No dependencies
GenerateItemQRCodeTicket	The batch creates item tickets or shelf labels for item QR code changes.	No dependencies
ItemPriceToHistory	This batch writes the active item price records into item price history table.	No dependencies
ItemRequest	The batch process generates item requests in pending or worksheet status for item request product group schedule which was scheduled for current date	No dependencies
PosTransactionImport	This batch imports ORXPOS sale and order transactions (SIMT-LOG file) into SIM.	No dependencies
PosTransactionRetry	This batch reprocess the pos transactions which are in error state.	No dependencies
PriceChangeExtractRetry	This batch retries failed Price Change extract from previous processing which are ready for retry.	No dependencies
ProblemLineStockCount	The batch goes through the list of items in the problem line group, determining which fall within the user specified parameters (negative SOH, negative available, and so forth). The system automatically creates a stock count from those items that do fall within the parameters.	No dependencies
PromotionPriceChange	This batch process imports the promotional price changes setup in a price management system. SIM uses this data to update the price information of the items.	No dependencies
RegularPriceChange	This batch process imports the permanent/regular price changes setup in a price management system. SIM uses this data to update price information of the items.	No dependencies
RetailSaleAuditImport	This batch program imports sales/order transaction data (SIM-ReSA file) that originated in Oracle Retail Xstore Point of Service.	No dependencies
ReturnNotAfterDateAlert	This batch process warns users x number of days in advance that the RTV/RTW is about to reach the Not After Date and must be dispatched.	No dependencies
StockCountAuthorizeRecovery	This batch process attempts to complete a failed stock count authorization.	No dependencies
StoreSequenceImport	This batch file import sequencing information like store sequence areas and items mapped to those areas from a flat file.	No dependencies
ThirdPartyStockCountImport	This batch process imports stock count file from a third-party counting system. The stock on hand quantities are updated for the existing unit and amount stock count records in SIM.	No dependencies
TransfersNotAfterDateAlert	This batch process warns users x number of days in advance that the transfer request is about to reach the Not After Date and must be dispatched.	No dependencies
TransfersOverdueBatch	This batch process sends user e-mail for dispatched transfers which have not been received after a number of days.	No dependencies
UINAttributeImport	This batch set up store UIN attributes on department/class level from the UIN Attribute import file.	No dependencies
WastageInventoryAdjustments	This batch process looks for wastage product groups that are scheduled for today and creates an inventory adjustment for each item in the scheduled product group, the wastage adjustment records are staged and published to external system (such as RMS).	No dependencies

Batch Process Scheduling Notes

Most SIM batches can be scheduled to run at any time (ad hoc) with no particular order, while some of batches might provide optimal results when batches are run in a particular order. Table 4-2 provides some scheduling recommendations:

Table 4-2 Batch Scheduling Notes

Batch Name	Schedule Type	Successor Depends on Success of Predecessor	Notes
PosTransactionImport RetailSaleAuditImport	Daily/ad hoc	Not required. The successor batch runs regardless of success/failure of the predecessor batch.	These batches should ideally be run together. Running them together to ensure inventory accuracy.
DeactivateOldUsers PurgeDeletedUsers PurgeUserPasswordHistory PurgeInvalidUserRoles PurgeUserCache	Ad hoc /Daily	Not required. The successor batch runs regardless of success/failure of the predecessor batch.	These batches should run on a continuous basis to ensure tight security and appropriate access to SIM.
CleanupShelfReplenishment	Daily		This batch should be run at least once a day if the delivery bay is used.
WastageInventoryAdjustments	Daily		This batch should run one time a day and the inventory adjustment MPS Worker must be enabled to ensure inventory accuracy.
AutoReceiveFinisherDeliveries AutoReceiveTransfers AutoReceiveWarehouseDeliveries	Daily/ad hoc	Not required. The successor batch runs regardless of success/failure of the predecessor batch.	These batches should be run at least once per day or for appropriate receipt closures.
ItemPriceToHistory	Daily		This batch should run once per day.

Batch Details

The following section summarizes SIM's batch processes and includes both an overview of each batch process business functionality, assumptions, and scheduling notes for each batch.

AutoReceiveTransfer Deliveries Batch

AutoReceiveTransferDeliveries does the following:

Retrieves a list of all stores.
Retrieves the auto receive config option for the location types (Store, Warehouse, and Finisher).
For each store, if the Auto Receive store parameter is set to Date Driven, then the batch auto-receives all deliveries that are in New and In Progress status and who's Ship Date added to the Auto Receive Number of Days is less than the current date.

Usage

The following command runs the AutoReceiveTransferDeliveries batch:

AutoReceiveTransferDeliveries.sh <ship_date>

Where the ship_date is optional and a date is not entered, then the server date is used.

Key Tables

Table 4-3 Key Tables for AutoReceiveTransfer Deliveries

Table	Select	Insert	Update	Delete
activity_history		Yes
config_store	Yes
inv_adjust_reason	Yes
item_uin	Yes		Yes	Yes
store_item_stock	Yes		Yes
store_item_stock_history		Yes
store_sequence_area	Yes
store_sequence_item	Yes
tsf	Yes		Yes
tsf_allocation	Yes		Yes
tsf_delv	Yes
tsf_delv_carton	Yes		Yes	Yes
tsf_delv_line_item	Yes		Yes
store	Yes

Restart/Recovery

Not Applicable

AutoReplenishCapacity Batch

The batch process looks for those product groups that are set up as shelf replenishment type that are scheduled for the current date. The batch will then create shelf replenishment records for the items defined in the product group according to their store sequence area capacity. After creating the records, it will confirm the shelf replenishment and thus updating shop-floor quantities for the respective items.

Usage

The following command runs the AutoReplenishCapacity batch:

AutoReplenishCapacity.sh <date>

Where the date is optional and if date is not entered, then the server date is used.

AutoTicketPrint Batch

The batch process ultimately sends tickets that match the product group items setup in the store for printing. First it finds pending Item Tickets within the store that matches the Auto Ticket Print product group for the day. Then it updates the quantity of the tickets if the refresh flag was enabled for that product group.

It further sorts the tickets based on either print order (if available), area sequence, item sequence, item hierarchy or a combination of all four. After that, it consolidates the tickets; removing identified duplicates, sends the tickets for print and finally mark all the tickets within that process as print submitted.

Usage

The following command runs the AutoTicketPrint batch:

AutoTicketPrint.sh

Key Tables

Table 4-4 Key Tables for AutoTicketPrint Batch

Tables	Select	Insert	Update	Delete
config_sysem
group_schedule_extract	Yes	Yes
item	Yes
item_price_v	Yes
item_sequence	Yes
item_ticket	Yes		Yes
item_ticket_detail_v	Yes
print_format
product_group	Yes
Product_group_schedule
cproduct_group_item_bkdn	Yes	Yes		Yes
product_group_sched_store	Yes
product_group_schedule_v	Yes
stock_item_v	Yes
store	Yes
store_printer
ticket_print		Yes
ticket_request			Yes

Restart/Recovery

Not Applicable

CleanupShelfReplenishment Batch

The end of day batch process runs at the end of each day to reset the delivery bay and close any open pending shelf replenishments. The system takes the entire inventory from the delivery bay and moves it to the back room. Any pending or in progress shelf replenishment are changed to a cancelled state. Users who are performing a shelf replenishment are kicked out of the system. That is, the batch process takes over the shelf replenishment user's application activity locking. The current user's shelf replenishment process is discarded without being saved. After the batch process is run, all shelf replenishments are either completed or cancelled, and the delivery bay has zero inventory.

Usage

The following command runs the CleanupShelfReplenishment batch job:

CleanupShelfReplenishment.sh

Key Tables

Table 4-5 Key Tables for CleanupShelfReplenishment Batch

Table	Select	Insert	Update	Delete
shelf_replenish			Yes
stock_item_stock			Yes

Restart/Recovery

Not applicable

ClearancePriceChange Batch

This batch imports the clearance price changes from flat file into SIM item price table.

There are two phases involve in the batch process. The file load phase loads the file into price change worksheet table; the extract phase kicks off multiple threads to extract the approved worksheet records into item price table.

To optimize price change batch import process, admin user can change the SIM system configuration parameter 'DAYS_TO_HOLD_PRICE_CHANGE_WORKSHEET' value to 0, the completed staged worksheet records will be deleted after price change records are extracted into item_price table. For purging staged worksheet records, see PurgePriceChangeWorksheet Batch.

Usage

The following command runs the ClearancePriceChange batch:

ClearancePriceChange.sh <file_name>

Where filename (required): the file location and name of the input price change file, the file path can be absolute path or relative path to batch program.

The clearance.price.import.dir can be set up during SIM application installation, see the Oracle Retail Store Inventory Management Installation Guide for details.

Example:

ClearancePriceChange.sh <clearance.price.import.dir >/clearance_file1.dat.

Key Tables

Table 4-6 Key Tables for ClearancePriceChange Batch

Table	Select	Insert	Update	Delete
batch_imp_exp	Yes	Yes	Yes
config_batch_imp_exp	Yes
price_change_worksheet	Yes	Yes	Yes	Yes
Item_price	Yes	Yes	Yes

Restart/Recovery

The following are the functions in the Restart/Recovery:

Backup

It is customer's responsibility to backup the original data files before processing. The batch process deletes the data file after the data is loaded into the worksheet table.

File Layout

See the ”Appendix: Batch File Layout Specifications”section for file layout details.

Threading

The file loading is single thread process, each file can only be loaded by a single thread, and the operation is all-or-none transaction.

Once the file is loaded into worksheet table, extracting the staged worksheet records can be processed concurrently.

The number of concurrent extracting processes is configured through the batch.cfg file. For details, see ”batch.cfg.”

CloseDSDReceivings Batch

This batch program looks for all the open vendor deliveries whose expected date added to store parameter ”Auto Close Days after Expected Date” is before today and automatically confirms all the vendor deliveries.

Usage

The following command runs the CloseDSDReceivings batch:

CloseDSDReceivings.sh

Key Table

Table 4-7 Key Tables for CloseDSDReceivings Batch

Table	Select	Insert	Update
activity_history		Yes
config_store	Yes
dsd	Yes		Yes
dsd_carton	Yes		Yes
dsd_line_item	Yes		Yes
dsd_line_item_att	Yes		Yes
dsd_line_item_uin	Yes		Yes
inv_adjust_reason	Yes
purchase_order	Yes		Yes
stock_item_v	Yes
store	Yes

Restart/Recovery

Not applicable

CloseProdGroupSchedule Batch

This batch program searches for all open product group schedules that have ended date before today (or user specified date), and change the product group schedule status to closed.

Usage

The following command runs the CloseProdGroupSchedule batch:

CloseProdGroupSchedule.sh <close_date>

Where the close_date is optional and a date is not entered, then the server date is used.

Key Tables

Table 4-8 Key table for CloseProdGroupSchedule Batch

Table	Select	Insert	Update	Delete
Product_group_schedule	Yes		Yes

Restart/Recovery

The following are the restart/recovery functions:

Error Handling and Logging

The following describes two phases:

File Loading Phase

If an error occurs during the file loading phase, the batch process will terminate and no records will be loaded into worksheet table. The batch import record in BATCH_IMP_EXP table will be marked as file to stage failed. The errors will be recorded in the batch log file.

If the error is due to database is unavailable or file not found, after the database is available or the file error is corrected, then retry file load can be processed using following command.

Retry file load:

ClearancePriceChange.sh <file_name>

Extract Phase

If an error occurs during the extracting worksheet phase, the batch process will mark the batch import record as import failed.

Retry extract records:

See the "PriceChangeExtractRetry Batch" for details.

CloseTransferDeliveries Batch

This batch program looks for all the open transfer deliveries and auto confirms all the transfer deliveries based on the store parameter ”Auto Close Receipt”

When the parameter value is ”0”, close the deliveries at the end of day today and when value is ”x” close the deliveries at the end of ”x” days stating from today.

Usage

The following command runs the CloseTransferDeliveries batch:

CloseTransferDeliveries.sh

Key Tables

Table 4-9 Key Tables for CloseTransferDeliveries Batch

Table	Select	Insert	Update	Delete
activity_history		Yes
config_store	Yes
inv_adjust_reason	Yes
item_uin	Yes		Yes	Yes
store_item_stock	Yes		Yes
store_item_stock_history		Yes
store_sequence_area	Yes
store_sequence_item	Yes
tsf	Yes		Yes
tsf_allocation	Yes		Yes
tsf_delv	Yes
tsf_delv_carton	Yes		Yes	Yes
tsf_delv_line_item	Yes		Yes
store	Yes

Restart/Recovery

Not applicable

CloseTransfers Batch

This batch program looks for all the open transfers which have passed their not after date and are in valid state for closure.

Usage

The following command runs the CloseTransfers batch:

CloseTransfers.sh

Key Tables

Table 4-10 Key Tables for CloseTransfers Batch

Table	Select	Insert	Update
activity_history		Yes
config_store	Yes
msp_staged_message		Yes
store	Yes
store_item_stock	Yes		Yes
tsf	Yes		Yes
tsf_line_item	Yes		Yes
tsf_ship	Yes
tsf_ship_carton	Yes
tsf_ship_line_item	Yes

Restart/Recovery

Not applicable

CloseVendorReturn Batch

This batch program looks for all the open vendor returns which are in valid state (Closed /Rejected) for closure.

Usage

The following command runs the CloseVendorReturn batch:

CloseVendorReturn.sh

Key Tables

Table 4-11 Key table for CloseVendorReturn Batch

Table	Select	Insert	Update	Delete
rtv	Yes	Yes	Yes
rtv_line_item	Yes	Yes	Yes	Yes
rtv_line_item_uin				Yes
store_item_stock	Yes		Yes
shipment_reason	Yes

Restart/Recovery

Not applicable

DeactivateOldUsers Batch

This batch process finds active users that have passed their end date and updates their status in table SECURITY_USER to inactive.

Usage

The following command runs the DeactivateOldUsers batch:

DeactivateOldUsers.sh <purge_date>

Where purge_date is optional and the date format must be dd/MM/yyyy if purge_date is specified.

Key Tables

Table 4-12 Key Tables for DeactivateOldUsers Batch

Tables	Select	Insert	Update	Delete
security_user			Yes
config_system	Yes

Restart/Recovery

Not applicable

DexnexFileParser Batch

This batch imports the direct delivery shipment records (PO, shipment and receipt) from Dex/Nex files in the DEX/NEX directory into SIM.

With the uploaded data, SIM processing creates a DEX/NEX direct delivery, allowing the store user to view, edit, and confirm the information contained in the DEX/NEX file before approving it so that it can become an in progress direct delivery.

Usage

The following command runs the DexnexFileParser batch:

DexnexParser.sh file_name

Where the file name(required): is the file name which contains the data.

Key Tables

Table 4-13 Key Tables for DexnexFileParser Batch

Table	Select	Insert	Update
store	Yes		Yes
config_store	Yes
store_item_stock	Yes	Yes
supplier	Yes
supplier_item	Yes
dsd	Yes	Yes
dsd_line_item	Yes	Yes
dsd_carton		Yes
purchase_order		Yes
purchase_order_line_item		Yes

Restart/Recovery

Not applicable

ExtractUnitAmountStockCount Batch

This batch program generates UnitAmount stock counts.

On a daily basis, the batch process creates the stock counts that are scheduled for the current day or future date which matches the next scheduled date. The system looks at all the scheduled stock count records and determines whether any are scheduled for today or the user-specified future date. The process creates the stock counts for each individual store. For example, if a scheduled count includes a list of five stores, then five separate stock count records are created.

If an all-location stock count is being run, the batch processing generates individual counts for every macro sequence location.

The date parameter is optional when running the Extract Stock Counts batch. If no date is provided, today's date is used.

Usage

The following command runs the ExtractUnitAmountStockCount batch:

ExtractUnitAmountStockCount.sh <extract_date>

Where the extract_date is optional; if specified, it must be in format of dd/MM/yyyy.

Key Tables

Table 4-14 Key Table for ExtractUnitAmountStockCount Batch

Table	Select	Insert	Update	Delete
group_schedule_extract		Yes		Yes
product_group	Yes
product_group_hierarchy	Yes
product_group_item	Yes
product_group_sched_store	Yes
product_group_schedule	Yes		Yes
product_group_item_bkdn		Yes		Yes
stock_count	Yes	Yes	Yes
stock_count_child		Yes	Yes
stock_count_line_item		Yes	Yes
stock_count_line_item_uin		Yes	Yes
item	Yes
store_item	Yes
store_item_stock	Yes
item_component	Yes

Restart/Recovery

The following is the restart/recovery:

Threading

The number of concurrent extracting processes is configured through the batch.cfg file. For details, see the "batch.cfg".

ExtractUnitStockCount Batch

This batch program generates Unit stock counts.

On a daily basis, the batch process creates the stock counts that are scheduled for the current day or future date which matches the next scheduled date. The system looks at all the scheduled stock count records and determines whether any are scheduled for today or the user specified future date. The process creates the stock counts for each individual store.For example, if a scheduled count includes a list of five stores, then five separate stock count records are created.

If the system is configured to use unguided stock counts, the batch process does not generate multiple counts even if the item is located at multiple locations within the store.

The date parameter is optional when running the Extract Stock Counts batch. If no date is provided, today's date is used.

Usage

The following command runs the ExtractUnitStockCount batch:

ExtractUnitStockCount.sh <extract_date>

Where the extract_date is optional; if specified, it must be in format of dd/MM/yyyy.

Key Tables

Table 4-15 Key Tables for ExtractUnitStockCount Batch

Table	Select	Insert	Update	Delete
group_schedule_extract		Yes		Yes
product_group	Yes
product_group_hierarchy	Yes
product_group_item	Yes
product_group_sched_store	Yes
product_group_schedule	Yes		Yes
product_group_item_bkdn		Yes		Yes
stock_count	Yes	Yes	Yes	Yes
stock_count_child		Yes	Yes	Yes
stock_count_line_item		Yes	Yes	Yes
stock_count_line_item_uin		Yes	Yes
item	Yes
store_item	Yes
store_item_stock	Yes
item_component	Yes

Restart/Recovery

Threading

The number of concurrent extracting processes is configured through the batch.cfg file. For details, see the "batch.cfg".

FulfillmentOrderPickReminders Batch

This batch process sends out e-mail alerts for fulfillment order picks for which create date has expired by minutes to hold customer orders before sending e-mail alert parameter value and the status is new or in progress.

Usage

The following command runs the FulfillmentOrderPickReminders batch:

FulfillmentOrderPickReminders.sh

Key Tables

Table 4-16 Key tables for FulfillmentOrderPickReminders Batch

Table	Select	Insert	Update	Delete
ful_ord_pick	Yes
ful_ord_pick_line_item	Yes
store_item_stock	Yes
store	Yes
comfig_system	Yes

Restart/Recovery

Not applicable

FulfillmentOrderReminders Batch

This batch process sends out e-mail alerts for fulfillment orders for which create date has expired by minutes to hold customer orders before sending e-mail alert parameter value.

Usage

The following command runs the FulfillmentOrderReminders batch:

FulfillmentOrderReminders.sh

Key Tables

Table 4-17 Key tables for FulfillmentOrderReminders Batch

Tables	Select	Insert	Update	Delete
config_system	Yes
config_store	Yes
store	Yes
store_item_stock	Yes
ful_ord	Yes
ful_ord_line_item	Yes

Restart/Recovery

Not applicable

GenerateItemQRCodeTicket Batch

The batch looks at the item QR code table for any QR type item that has a date equal to the date for which the batch is running. The batch creates item QR code item tickets or shelf labels provided that the store options (SEND_ITEM_TICKETS_TO_TICKETING_FOR_QR_CODE_CHANGE or SEND_SHELF_EDGE_LABELS_TO_TICKETING_FOR_QR_CODE_CHANGE) are set for the store.

The batch uses the following store options:

Send item tickets to ticketing for QR code changes.
Send item labels to ticketing for QR code changes.

Usage

The following command runs the batch:

GenerateItemQRCodeTicket.sh <batch_date>

Where the batch_date is optional and a date is not entered, then the server date is used.

Key Tables

Table 4-18 Key Table for GenerateItemQRCodeTicket Batch

Table	Select	Insert
config_store	Yes
item_qr_code	Yes
item_ticket		Yes
print_format	Yes
store_item_stock	Yes
supplier_item_manufacture	Yes

Restart/Recovery

Not applicable

ItemPriceToHistory Batch

This batch writes the active item price records into item price history table. After the active item prices are recorded in the item price history table, the batch updates the ITEM_PRICE table statuses as completed for these records.

Usage

The following command runs the batch:

ItemPriceToHistory.sh <date in format of dd/MM/yyyy >

Where the date (optional) is the date which the price effectives at or pricing ends at.

If date is not provided, then the batch client current time is used.

Key Tables

Table 4-19 Key tables for ItemPriceToHistory Batch

Table	Select	Insert	Update	Delete
item_price	Yes		Yes	Yes
item_price_history		Yes

Restart/Recovery

Not applicable

ItemRequest Batch

The batch process looks for those product groups that are set up as item request type that are scheduled for the current date. It generates the item request (with items and quantities) in a pending or worksheet status. The user (for example, a manager) can then add items, delete items, change quantities, and so on before submitting the data to the merchandising system. The merchandising system can generate POs or warehouse to store transfers as applicable. The batch also cancels out the expired item requests.

Usage

The following command runs the ItemRequest batch:

ItemRequest.sh

Threading:

The number of concurrent extracting processes is configured through the batch.cfg file. For details, see the "batch.cfg".

Key Tables

Table 4-20 Key tables for ItemRequest Batch

Table	Select	Insert	Update	Delete
item_request	Yes	Yes	Yes	Yes
item_request_line_item		Yes		Yes
product_group_schedule	Yes
product_group	Yes
product_group_item	Yes
product_group_sched_store	Yes
product_group_item_bkdn	Yes	Yes
product_group_hierarchy	Yes
item	Yes
store_item	Yes

Restart/Recovery

Not applicable

PosTransactionImport Batch

ORXPOS may post transactions to SIM either through web service route or through a flat file.

PosTransactionImport batch imports pos transaction records from the flat file (SIMT-LOG file) that came from ORXPOS into the SIM database staging table where polling timer framework will pick those staged requests and update the stock tables in SIM.

Usage

From SIM batch location, run command.

posTransactionImport.sh <filename1>

Where filename(required): is the file name input simt_log file that contains the transaction data. The path of the file can be absolute or relative to the batch client.

The <pos.sale.import.dir> can be set up during SIM application installation, see the Oracle Retail Store Inventory Management Installation Guide for details.

Example: PosTransactionImport.sh <pos.sale.import.dir>/simtlog_file1.dat.

Batch Detail

posTransactionImport batch process takes the sales/order transaction data and stage them to the SIM database staging table from where they are picked up by the polling timer framework to update the store item's inventory buckets (for example, store item's total quantity, shop floor quantity), if applicable.

The file will contain both sale and order transactions. The batch will assign separate request IDs to sales and order transactions.

For sale transactions, a single request ID cannot contain more than MAX_VALUE = 500 transaction line items with an exception that a single transaction ID cannot span across multiple request IDs.

For order transactions, a single request ID cannot contain more than MAX_VALUE = 500 transaction line items with an exception that a single customer order ID cannot span across multiple request IDs.

The file contains transactions for a single store.

Threading

posTransactionImport batch supports one file per thread. The operation is all or none transaction. For multiple file execution you can run multiple instances of the batch file providing each process with a separate audit file.

Error Handling, Logging, and File Archiving

posTransactionImport batch process will notify the user with an error message if the file staging fails. The staging process is all or none transaction so if an error occurs during the batch process, none of the transactions in the file will be staged. The user will need to rerun the same file again after resolving any errors.

Key Tables

Table 4-21 Key tables for PosTransactionImport Batch

Table	Select	Insert
pos_transaction	Yes	Yes
item	Yes
inv_adjust_reason	Yes

Restart/Recovery

Not applicable

PosTransactionRetry Batch

This batch processes previous failed ORXPOS transaction records (POS_TRANSACTION) which are ready to be retried (status = 3).

With the POSTransactionService Web service/SIMT_LOG file integration between Oracle Retail Xstore Point of Service and SIM, the ORXPOS Transactions (sale, return, void of sale, void of return order new, order cancel, order fulfill) are integrated into SIM and inventory updates take place in SIM.

If there is an error (status = 2) because of incorrect data, the PosTransactionRetry batch can be run to process the those failed records.

Usage

The following command runs the PosTransactionRetry batch:

PosTransactionRetry.sh <store_id>

Where store_id (required): is the store ID for which you want to process the error ORXPOS transactions which are read to be retried.

Key Tables

Table 4-22 Key tables for PosTransactionRetry Batch

Tables	Select	Insert	Update
pos_transaction	Yes	Yes	Yes
config_store	Yes
config_system	Yes
item	Yes
item_uin	Yes	Yes	Yes
store_uin_admin_item	Yes
store_item_stock	Yes		Yes
stock_count_line_item	Yes
stock_count_late_sale		Yes
inv_adjust_reason	Yes
ful_ord	Yes	Yes	Yes
ful_ord_line_item	Yes	Yes	Yes
ful_ord_dlv	Yes	Yes	Yes
ful_ord_dlv_line_item		Yes
ful_ord_dlv_line_item_uin		Yes
ful_ord_dlv_line_item_att		Yes
item_uin_problem			Yes

Restart/Recovery

Not applicable

PriceChangeExtractRetry Batch

This batch re-processes (extracts) the approved price change worksheet records which were missed from the previous price change batch executions (such as database server was down or dependent data was missing) after the issues are resolved.

Run this batch if price change batch (ClearancePriceChange, PromotionPriceChange, RegularPriceChange) encounters an error.

For batch import in question, user will need to inspect the price change worksheet records for the batch import). For records which are in status of failed extract (status = 7) or rejected (status = 4) and are recoverable once the errors are resolved (such as dependent data is now available, or db server is up), user needs to set the status back to 2 (approved) for re-processing.

Usage

The following command runs the batch:

PriceChangeExtractRetry.sh <extract_id>

Where extract_id (required) is the extract ID in PRICE_CHANGE_WORKSHEET table.

Key Tables

Table 4-23 Key tables for PriceChangeExtractRetry Batch

Tables	Select	Insert	Update	Delete
price_change_worksheet	Yes		Yes	Yes
item_price	Yes	Yes	Yes
config_system	Yes
print_format_type	Yes
item_ticket		Yes
ticket_format_hierarchy	Yes
item_uda	Yes
store_uin_admin_item	Yes
store_sequence_item	Yes

Restart/Recovery

This batch can be restarted/re-run from previous failures after the issues are resolved.

ProblemLineStockCount Batch

Before the batch process runs, the retailer establishes a group of items and item hierarchies (by associating them to the problem line group type) and selects applicable parameters (negative SOH, negative available, and so on). The problem line batch process goes through the list of items in the group, determining which fall within the parameters. The system automatically creates a stock count from those items that do fall within the parameters.

If an item is a problem line item (negative inventory for example) on a stock count, and the user does not get the chance to perform the stock count on it that day, the next day the item may no longer be a problem line (positive inventory). However, the system continues to create a stock count for that item because a problem existed at one time.

Usage

The following command runs ProblemLineStockCount batch:

ProblemLineStockCount.sh

Threading

The number of concurrent extracting processes is configured through the batch.cfg file. For details, see the ”batch.cfg.”

Key Tables

Table 4-24 Key tables for ProblemLineStockCount Batch

Tables	Select	Insert	Update	Delete
group_schedule_extract	Yes	Yes
prod_group_item_bkdn			Yes	Yes
stock_count	Yes	Yes	Yes	Yes
stock_count_line_item	Yes	Yes	Yes	Yes
stock_count_line_item_uin	Yes	Yes	Yes	Yes
stock_count_child	Yes	Yes	Yes	Yes
product_group_schedule	Yes		Yes
product_group	Yes
product_group_sched_store	Yes
item	Yes
store_item	Yes
stock_count_line_item	Yes

Restart/Recovery

Not Applicable

PromotionPriceChange Batch

This batch imports the promotion price changes from flat file into SIM item price table.

To optimize price change batch import process, admin user can change the SIM system configuration parameter 'DAYS_TO_HOLD_PRICE_CHANGE_WORKSHEET' value to 0, the completed staged worksheet records will be deleted after price change records are extracted into item_price table. For more information on purging staged worksheet records, see the PurgePriceChangeWorksheet Batch.

Usage

The following command runs the PromotionPriceChange batch:

PromotionPriceChange.sh <file_name>

Where filename (required): import file directory and name of the import file.

The < promotion.price.import.dir> can be set up during SIM application installation, see the Oracle Retail Store Inventory Management Installation Guide for details.

Example:

PromotionPriceChange.sh <promotion.price.import.dir> /promo_file1.dat.

Backup

It is customer's responsibility to backup the original data files before processing. The batch process deletes the data file after the data is loaded into the worksheet table.

File Layout

See the "Appendix: Batch File Layout Specifications" section for file layout details.

Threading

The file loading is single thread process, each file can only be loaded by a single thread, the operation is all-or-none transaction.

Once the file is loaded into worksheet table, extracting the staged worksheet records can be processed concurrently.

The number of concurrent extracting processes is configured through the batch.cfg file. For details, see the "batch.cfg".

Error Handling and Logging

The following describes the phases:

File Loading Phase

If an error occurs during the file loading phase, the batch process will terminate and no records will be loaded into worksheet table. The batch import record in BATCH_IMP_EXP table will be marked as file to stage failed. The errors will be recorded in the batch log file.

If the error is due to database is unavailable or file not found, after the database is available or the file error is corrected, then retry file load can be processed using following command:

Retry file load:

PromotionPriceChange.sh <file_name>
Extract Phase

If an error occurs during the extracting worksheet phase, the batch process will mark the batch import record as import failed.

Retry extract records:

See the "PriceChangeExtractRetry Batch" batch for details.

Key Tables

Table 4-25 Key tables for PromotionPriceChange Batch

Tables	Select	Insert	Update	Delete
price_change_worksheet	Yes		Yes	Yes
item_price	Yes	Yes	Yes
config_store	Yes
item_ticket		Yes
ticket_format_hierarchy	Yes
item_uda	Yes
print_format_type	Yes
ticket_request	Yes	Yes

Restart/Recovery

Not Applicable

RegularPriceChange Batch

This batch imports the regular price changes from flat file into SIM item price table.

Usage

The following command runs the RegularPriceChange batch:

RegularPriceChange.sh <file_name>

Where filename (required): import file directory and name of the import file.

The <regular.price.import.dir> can be set up during SIM application installation, see the Oracle Retail Store Inventory Management Installation Guide for details.

Example:

RegularPriceChange.sh <regular.price.import.dir>/regular_file1.dat.

Backup

It is customer's responsibility to backup the original data files before processing. The batch process deletes the data file after the data is loaded into the worksheet table.

File Layout

See the Appendix: Batch File Layout Specifications section for file layout details.

Threading

The file loading is single thread process, each file can only be loaded by a single thread, the operation is all-or-none transaction.

Once the file is loaded into worksheet table, extracting the staged worksheet records can be processed concurrently.

The number of concurrent extracting processes is configured through the batch.cfg file. For details, see the batch.cfg.

Error Handling and Logging

The following describes the phases:

File Loading Phase

If an error occurs during the file loading phase, the batch process will terminate and no records will be loaded into worksheet table. The batch import record in BATCH_IMP_EXP table will be marked as file to stage failed. The errors will be recorded in the batch log file.

If the error is due to database is unavailable or file not found, after the database is available or the file error is corrected, then retry file load can be processed using following command:

Retry file load:

RegularPriceChange.sh <file_name>
Extract Phase

If an error occurs during the extracting worksheet phase, the batch process will mark the batch import record as import failed.

Retry extract records:

For details, see the PriceChangeExtractRetry Batch.

Key Tables

Table 4-26 Key tables for RegularPriceChange Batch

Tables	Select	Insert	Update	Delete
price_change_worksheet	Yes	Yes	Yes	Yes
item_price	Yes	Yes	Yes
config_store	Yes
item_ticket		Yes
ticket_format_hierarchy	Yes
item_uda	Yes
print_format_type	Yes
ticket_request	Yes	Yes

Restart/Recovery

For more information, see the PriceChangeExtractRetry Batch

RetailSaleAuditImport Batch

This batch program imports sales/order transaction data (SIM-ReSA File) that originated in Oracle Retail Xstore Point of Service. The external audit system will provide in its sales upload file a percentage or quantity that indicates how much the inventory needs to be reduced by, in addition to the sold quantity.

For example, meat will become lighter as fluids evaporate. Other items, for example cheese or ham, will only be reduced when of the outside layers are cut off to sell the item.

SIM takes the sales transaction data to update the store item's inventory buckets. From the batch program, SIM learns about inventory movement (that is, what is sold, what is returned, what is reserved and what is fulfilled). Once SIM attains the data, SIM assumes that sales should be taken from the store's shelf-related inventory buckets. This assumption is important to SIM's shelf replenishment processing. Similarly, SIM assumes that returns should go to the backroom bucket; the system's logic is that returns must be inspected.

The batch also writes each failure record into a transaction log table.

Usage

From SIM batch location, run command:

RetailSaleAuditImport.sh <filename1>

Where filename(required): is the file name input ReSA file that contains the audit data.

The file path can be either absolute path or relative path to batch program.

Example:

RetailSaleAuditImport.sh <BATCH_DIR>/input/ReSA/audit_file1.dat

Batch Detail

RetailSaleAuditImport takes the sales/order transaction data and stage them to the SIM database staging table from where they are picked up by the polling timer framework to update the store item's inventory buckets (for example, store item's total quantity, shop floor quantity), if applicable.

The file will contain both sales and order transactions. The batch job combines the transaction number and register number to form the transaction ID in SIM. Request IDs are assigned to the transactions in such a way that a single request ID will not contain more than MAX_SIZE=500 records with an exception that a single transaction ID should not span across multiple request IDs.

Threading

RetailSaleAuditImport batch supports one file per thread. The operation is all or none transaction. For multiple, file execution you can run multiple instances of the batch file providing each process with a separate audit file.

Error Handling, Logging and File Archiving

RetailSaleAuditImport will notify the user with an error message if the file staging fails. The staging process is all or none transaction so if an error occurs during the batch process, none of the transactions in the file will be staged. The user will need to rerun the same file again after resolving any errors.

Key Tables

Table 4-27 Key tables for RetailSaleAuditImport Batch

Table	Select	Insert	Update	Delete
pos_transaction		Yes
inv_adjust_reason	Yes

Restart/Recovery

Not Applicable

ReturnNotAfterDateAlert Batch

This batch process warns users a number of days in advance that the RTV/RTW is about to reach the Not After date and must be dispatched. The value for the number of days of advance warning is configurable using the system's administration screens.

Usage

The following command runs the ReturnNotAfterDateAlert batch:

ReturnNotAfterDateAlert.sh

Key Tables

Table 4-28 Key table for ReturnNotAfterDateAlert Batch

Tables	Select	Insert	Update	Delete
rtv	Yes
rtv_line_item	Yes
shipment_reason	Yes
stock_item	Yes

Restart/Recovery

Not applicable

StockCountAuthorizeRecovery Batch

This batch process looks for stock counts that are stuck in Authorize Processing state. This is a unique state that appears when an error occurs during the final processing of a stock count. The batch attempts to fully authorize the stock count. Errors that occur during the batch process are logged to the server error logs and will indicate the reason for any further processing failures. Successfully authorized stock counts will move to authorized completed state.

Usage

The following command runs the StockCountAuthorizeRecovery batch:

StockCountAuthorizeRecovery.sh

Key Tables

Table 4-29 Key tables for StockCountAuthorizeRecovery Batch

Tables	Select	Update	Delete
stock_count	Yes	Yes
stock_count_child	Yes	Yes
stock_count_line_item	Yes	Yes
stock_count_line_item_uin	Yes
item_uin	Yes	Yes
store_item	Yes
store_item_stock		Yes
product_group_schedule	Yes
product_group_sched_store	Yes
store	Yes
stock_count_sale	Yes		Yes
inv_adjust_reason	Yes

Restart/Recovery

Not applicable

StoreSequenceImport Batch

This batch imports store sequencing information from a flat file. Before importing, the batch will delete the existing sequencing information including sequence items and sequence areas excluding no-location store area which is the default store sequence area.

Usage

The following command runs the StoreSequenceImport batch:

StoreSequenceImport.sh <filename1>

Where filename(required): is the file name input that contains the sequence data.

The file path can be either absolute path or relative path to batch program.

Example:

RetailSaleAuditImport.sh <BATCH_DIR>/input/Sequencing/store-sequence.dat

Key Tables

Table 4-30 Key tables for StoreSequenceImport Batch

Tables	Select	Insert	Update	Delete
store_sequence_area		Yes		Yes
store_sequence_item		Yes		Yes

Restart/Recovery

Not applicable

ThirdPartyStockCountImport Batch

This batch imports the stock count quantities which is setup in SIM and physical counting is conducted by a third party. The batch updates the store stock on hand quantities; invalid records are saved in the rejected item table.

For Auto-authorize Unit Amount stock count, the batch process also invokes the stock count authorization process automatically, the stock count authorization process will generate stock count result export file which can be imported by other merchandise system (such as RMS), if applicable.

For non-auto authorize stock count, after import batch complete, user will needs to go to SIM stock count authorization screen to manually authorize the stock count, the stock count authorization process will generate stock count result export file which can be imported by other merchandise system (such as RMS), if applicable.

Usage

ThirdPartyStockCountImport.sh <file_name>

Where filename (required): The import file directory and name of the import file.

The <stock.count.import.dir> can be set up during SIM application installation, see the Oracle Retail Store Inventory Management Installation Guide for details.

Example:

ThirdPartyStockCountImport.sh < stock.count.import.dir>/stock_count_imp1.dat

Note:

Export File Location

For auto-authorized unit amount stock count type, the batch will automatically invokes the stock count authorization process which generates the stock count results export file.

The export file location can be set up during SIM database installation. See the Oracle Retail Store Inventory Management Installation Guide for details.

If the export file location was not set up at the time as the SIM database was installed, then the following steps need to be set up by a system administrator and DBA.

The file location can be found by running the following query:

select * from dba_directories where directory_name = 'STOCK_COUNT_UPLOAD_DIR';

Export File Location Setup

System administrator

Setup physical file location on a server location where SIM database process can have access to, grant operation system permission read/write to SIM application schema user.
Database administrator
- Create directory object in SIM database: Create or replace directory STOCK_COUNT_UPLOAD_DIR as '&batch_stockcount_upload_dir';
- Grant privileges: Grant read, write on directory STOCK_COUNT_UPLOAD_DIR to &&sim_user_uppercase;

Key Tables

Table 4-31 Key tables for ThirdPartyStockCountImport Batch

Tables	Select	Insert	Update
stock_count_import	Yes		Yes
stock_count_rejected_item		Yes
stock_count	Yes		Yes
stock_count_child	Yes		Yes
stock_count_line_item	Yes		Yes
item_price	Yes
item	Yes
store_item	Yes
item_uin	Yes
stock_count_line_item_uin	Yes

Restart/Recovery

Not applicable

Integration Assumptions

RMS provides an item export file to external stock counting prior to the count in order for external stock counting to validate the items that are scanned.
The items coming from external stock counting are identified based on an RMS item number (for example, an RIN, UPC, or other number set up in RMS).
All quantities from external stock counting are assumed to be in the item's standard unit of measure (UOM) as established by RMS (for example, units, KG, and so on).
The external stock counting file sends the total quantity counted for each item, regardless of whether the item was counted in several areas of the store (rolled up total by item).
For items that exist in the SIM stock count records but do not have a counted quantity sent back from the external stock counting system, SIM assumes a count quantity of 0, and set this value on the stock count record.
For items that have a SOH quantity in SIM but have a stock counting count of 0, the discrepancy check uses the variance units (not the variance percentage) value to determine whether the item is discrepant, user can view the discrepant items through Stock Count Rejected Items PC screen after batch completes.

TransfersNotAfterDateAlert Batch

This batch process generates email alerts for any pending transfer requests with not after date coming up within number of days specified in the system parameter "Days to Send Email Alert Before Not After Date for Transfer Requests".

Usage

The following command runs the TransfersNotAfterDateAlert batch:

TransfersNotAfterDateAlertBatch.sh

Key Tables

Table 4-32 Key tables for TransfersNotAfterDateAlert Batch

Tables	Select	Insert	Update	Delete
tsf	Yes
tsf_line_item	Yes
store	Yes

Restart/Recovery

Not applicable

TransfersOverdue Batch

This batch process sends user e-mail for dispatched transfers which have not been received after a number of days. The value for the number of days for the e-mail alert is specified in the "Days Shipped Delivery Overdue Email Alert" system parameter which is configurable using the system's administration screen.

Usage

The following command runs the TransfersOverdue batch:

TransfersOverdueBatch.sh

Key Tables

Table 4-33 Key tables for TransfersOverdue Batch

Tables	Select	Insert	Update	Delete
tsf_delv	Yes
tsf_delv_carton	Yes

Restart/Recovery

Not applicable

UINAttributesImport Batch

This batch is used for process UIN Attribute file (see the Appendix UIN Attribute File Specification) to set up store item UIN admin records on department/class level for stores which system configurations have UIN_PROCESSING_ENABLED is true and AUTO_DEFAULT_UIN_ATTRIBUTES value is true.

The batch inserts/updates the STORE_UIN_ADMIN_DEPT table for the specified department/class, and insert into STORE_UIN_ADMIN_ITEM table for each item within the merchandise hierarchy, it then marks the store item as UIN required in STORE_ITEM table.

This batch can be used to set up store item admin records after SIM initial data seeding from RMS (Retail Merchandise System); it can also be used to set up bulk store UIN admin records by department/item level as an alternate way to the SIM GUI for setting up UIN attributes.

Usage

The following command runs the UpdateUINAttributes batch:

UINAttributeImport.sh <file_name>

Where filename (required): the file location and name of the import stock count file. The file path can be absolute path or relative path to batch program.

Key Tables

Table 4-34 Key table for UINAttributesImport Batch

Tables	Select	Insert	Update
store_uin_admin_item		Yes	Yes
store_uin_admin_dept		Yes	Yes
uin_attribute_import	Yes	Yes
item	Yes
config_system	Yes
print_format	Yes
store_item			Yes
uin_label	Yes

Restart/Recovery

Not applicable

WastageInventoryAdjustments Batch

This batch process looks for wastage product groups that are scheduled for today and creates an inventory adjustment for each item in the product group. The batch process uses amounts based on percentage/units. Note that if both a percentage and unit exist, the batch process applies the least amount of the two. For example, consider an item with a stock on hand value of 100. If the two values are 10% and 5 units, the batch process would create an inventory adjustment of 5 units for the item.

The batch process creates a completed inventory adjustment record using the adjustment reason of Shrinkage (code = 1) for each item that is published to the merchandising system.

Note:

Wastage is not run for items that require UINs.

Usage

Following command runs the WastageInventoryAdjustments batch:

WastageInventoryAdjustments.sh

After the batch process is complete, the retailer must run another batch WastageInventoryAdjustmentPublishJob.sh to publish the inventory adjustment generated by the above batch to the merchandising system.

Key Tables

Table 4-35 Key tables for WastageInventoryAdjustments Batch

Tables	Select	Insert	Update	Delete
group_schedule_extract	Yes	Yes		Yes
inv_adjust_reason	Yes
item	Yes
product_group	Yes
product_group_hierarchy	Yes
product_group_item	Yes
product_group_sched_str	Yes		Yes
product_group_schedule	Yes		Yes
schedule_group_item	Yes	Yes		Yes
store_item	Yes
store_item_stock	Yes		Yes
store_item_stock_history		Yes
store_item_stock_publish	Yes	Yes		Yes

Restart/Recovery

Not applicable