Oracle® Retail Store Inventory Management Operations Guide Release 15.0 E65670-03 |
|
Previous |
Next |
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.
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.
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.
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.
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. |
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 |
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. |
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.
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.
Where the ship_date
is optional and a date is not entered, then the server date is used.
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 |
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.
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.
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 |
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.
The following command runs the CleanupShelfReplenishment batch job:
CleanupShelfReplenishment.sh
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.
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.
The following are the functions in the Restart/Recovery:
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.
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.”
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.
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.
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.
The following are the restart/recovery functions:
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>
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.
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.
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 |
This batch program looks for all the open transfers which have passed their not after date and are in valid state for closure.
This batch program looks for all the open vendor returns which are in valid state (Closed /Rejected) for closure.
This batch process finds active users that have passed their end date and updates their status in table SECURITY_USER to inactive.
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.
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.
The following command runs the DexnexFileParser batch:
DexnexParser.sh file_name
Where the file name(required): is the file name which contains the data.
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.
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
.
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 |
The following is the restart/recovery:
The number of concurrent extracting processes is configured through the batch.cfg file. For details, see the "batch.cfg".
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.
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
.
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 |
The number of concurrent extracting processes is configured through the batch.cfg file. For details, see the "batch.cfg".
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.
The following command runs the FulfillmentOrderPickReminders batch:
FulfillmentOrderPickReminders.sh
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.
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.
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.
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.
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.
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.
The following command runs the ItemRequest batch:
ItemRequest.sh
The number of concurrent extracting processes is configured through the batch.cfg file. For details, see the "batch.cfg".
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 |
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.
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.
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.
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.
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.
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.
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.
Table 4-22 Key tables for PosTransactionRetry Batch
Tables | Select | Insert | Update | Delete |
---|---|---|---|---|
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 |
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.
The following command runs the batch:
PriceChangeExtractRetry.sh <extract_id>
Where extract_id (required) is the extract ID in PRICE_CHANGE_WORKSHEET table.
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.
The number of concurrent extracting processes is configured through the batch.cfg file. For details, see the ”batch.cfg.”
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 |
This batch imports the promotion 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 more information on purging staged worksheet records, see the PurgePriceChangeWorksheet Batch.
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.
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.
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".
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.
This batch imports the regular 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 the PurgePriceChangeWorksheet Batch.
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
The following command runs the StockCountAuthorizeRecovery batch:
StockCountAuthorizeRecovery.sh
Table 4-29 Key tables for StockCountAuthorizeRecovery Batch
Tables | Select | Insert | 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 |
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.
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
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.
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
|
Table 4-31 Key tables for ThirdPartyStockCountImport Batch
Tables | Select | Insert | Update | Delete |
---|---|---|---|---|
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 |
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.
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".
The following command runs the TransfersNotAfterDateAlert batch:
TransfersNotAfterDateAlertBatch.sh
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.
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.
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.
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. |
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.
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 |