External Transaction Data Process (trandataprocess.ksh)
|
Module Name |
trandataprocess.ksh |
|
Description |
External Transaction Data Process |
|
Functional Area |
Finance |
|
Module Type |
Business Processing |
|
Module Technology |
KSH |
|
Catalog ID |
RMS377 |
|
Wrapper Script |
rmswrap_shell_out.ksh |
Design Overview
This process, along with trandataload.ksh, provides a mechanism to write records directly into the TRAN_DATA tables based on a file from an external system. The primary purpose of this functionality is to allow additional costs to be included in stock ledger valuation that cannot be included based on existing Merchandise functionality. Records written to the TRAN_DATA tables do not necessarily have a connection to any Merchandising transaction, and are based on a determination made outside of Merchandising. The records written through this mechanism function exactly the same as records written by normal Merchandising processes. For cost based transactions, the information must be passed at an item/location level. For retail-based transactions, it can be at either an item/location or subclass/location level.
Note:
There is no support for recalculating or impacting unit inventory in Merchandising based on the transactions passed in, and only cost or retail value in the stock ledger is impacted - although the weighted average cost (WAC) may also be impacted if that method of accounting is used in Merchandising.
Trandataprocess batch processes the data on STAGE_EXT_TRAN_DATA and inserts into the TRAN_DATA table. This batch should be run after trandataload.ksh.
This batch validates the records on the staging table. The status records that fail validation are updated to 'E'rror on the staging table with error message.
The records which pass the validations are inserted into TRAN_DATA table and Weighted Average Cost is recalculated in case the WAC_recalc_ind is 'Y' for the record.
This script accepts the following input parameters:
-
Database Connect string.
-
Number of parallel threads - optional parameter. This is to override the value set on RESTART_CONTROL table.
This script calls the TRAN_DATA_IMPORT_SQL to import the transaction records on STAGE_EXT_TRAN_DATA table that haven't been processed yet. Each thread of the program processes a single chunk of data. After processing the Chunk, the status of the chunk is updated to 'P'rocessed.
The batch program performs the below validations on the staged records before inserting to TRAN_DATA. Status of the records which fail validations will be updated to 'E'rror on STAGE_EXT_TRAN_DATA along with the reasons for validation failure.
-
Validates Dept, Class, and Subclass against SUBCLASS table.
-
Validates location and loc_type against STORE and WH tables.
-
Validates tran_code against TRAN_DATA_CODES table.
-
If Item is not NULL validate if the item exists and is a transaction level item.
-
If Item is not NULL validate if the item belongs to the dept/class/subclass.
-
If Item not NULL validate if it is ranged to the location.
-
Validate that item is not a pack.
-
Item can be NULL only if it belongs to a Retail accounting department.
-
When RECAL_WAC_IND = 'Y', ITEM and TOTAL_COST should not be NULL.
-
Both total_cost and total_retail cannot be null.
-
The loc_type should be 'W' or 'S' or 'E'.
-
For TRAN_CODES - 37, 38, 63 and 64, GL_REF_NO should not be NULL
-
For TRAN_CODES - 22 and 23 total cost should not be NULL
-
For TRAN_CODES - 11, 12, 13, 14, 15, 16, 60, 80, and 81, total retail should not be NULL or total cost should be NULL.
-
For TRAN_CODES - 1, 4, 20, 24, 27, 30, 31, 37 and 38, total cost should not be NULL OR (total_retail should not be NULL and sellable_ind is 'Y')
Once records are validated, the batch program calculates the Weighted Average Cost (WAC) for the records with WAC_RECALC_IND = 'Y'. In case the calculated WAC <= 0 and if there is inventory present the location then a cost variance record (TRAN_CODE - 70) is inserted into TRAN_DATA. Cost variance transaction is also posted for those item locations which have no or negative inventory.