17 Stock Ledger
The stock ledger holds financial data that allows you to monitor your company's performance. It incorporates financial transactions related to merchandising activities, including sales, purchases, transfers, and markdowns; and is calculated weekly or monthly. The stock ledger accounts for inventory in buckets (how much inventory was returned, how much damaged, and so on). For additional information about stock ledger, including configuration and calculations, see the Merchandising Documentation Library (Doc ID: 1585843.1).
Figure 17-1 Process Flow - Stock Ledger
Different Merchandising transactions, such as sales, receipts, and adjustments, write to the working transaction data table (TRAN_DATA). Additionally, transactions can be uploaded from an external source using the External Transaction Data Upload (trandataload), which are then loaded using trandataprocess. This is the starting point for the batch processes shown above.
-
Stage Stock Ledger Transactions for Additional Processing (salstage) moves transaction data from the working table to the snapshot transaction data table for additional processing.
-
Daily Rollup of Transaction Data for Stock Ledger (saldly) rolls up the snapshot transaction data and persists it to the daily rollup table.
-
Append Stock Ledger Information to History Tables (salapnd) moves data from the snapshot transaction data table to the history table.
-
Weekly Rollup of Data/Calculations for Stock Ledger (salweek) rolls up daily stock ledger data to weekly stock ledger data.
-
Monthly Rollup of Data/Calculations for Stock Ledger (salmth) rolls up weekly stock ledger data to monthly stock ledger data.
-
End Of Half Rollup of Data/Calculations for Stock Ledger (saleoh) rolls up monthly stock ledger data to half level stock ledger data.
There are other programs in this section as well related to removing old records and capturing additional details for specific accounting requirements. For details on the trandataload upload, see Merchandising Operations Guide Volume 2.
Program Summary
The following batch designs are included in this functional area:
-
End Of Half Rollup of Data/Calculations for Stock Ledger (saleoh)
-
Monthly Rollup of Data/Calculations for Stock Ledger (salmth)
-
Stage Stock Ledger Transactions for Additional Processing (salstage)
-
Weekly Rollup of Data/Calculations for Stock Ledger (salweek)
Alternatively, for some of the purge processes, there is an option to run a background process to purge old data. These processes are:
Append Stock Ledger Information to History Tables (salapnd)
|
Module Name |
salapnd.pc |
|
Description |
Append Stock Ledger Information to History Tables |
|
Functional Area |
Stock Ledger |
|
Module Type |
Admin |
|
Module Technology |
ProC |
|
Catalog ID |
RMS335 |
|
Wrapper Script |
rmswrap_multi.ksh |
Design Overview
The purpose of this program is to move data from the staging table for transaction data into the historical transaction data table. This requires placing a lock on the staging table to ensure that no new data will be added to it while the movement is occurring (to handle trickling or real-time processing), moving the data to the historical table, and finally truncating the data from the staging table.
Daily Rollup of Transaction Data for Stock Ledger (saldly)
|
Module Name |
saldly.pc |
|
Description |
Daily Rollup of Transaction Data for Stock Ledger |
|
Functional Area |
Stock Ledger |
|
Module Type |
Business Processing |
|
Module Technology |
ProC |
|
Catalog ID |
RMS336 |
|
Wrapper Script |
rmswrap_multi.ksh |
Design Overview
This program is responsible for performing the daily summarization processing in the stock ledger in which transaction-level records are fetched from the transaction-level staging table and summed to the subclass/location/day/currency level. Once the records are summarized, they are written to the DAILY_DATA table in both primary as well as the local currency. If the local currency is same as the primary currency, the program will insert records only in local currency.
To call this program the end of day process for the stock ledger would not be completely correct, however, because a day does not really 'close' in the stock ledger until the month closes. Each time that the Daily Stock Ledger Processing program runs, all transaction-level data is processed, whether it is for the current date, a date since the last month closing or even a date prior to the last month closing. For transactions occurring on the current date or since the last month close, they are processed by simply summarizing the date and updating the current information on DAILY_DATA for the date of the transaction. However, if a transaction occurred prior to the last month that was closed (for example:. the transaction was dated 3/15 and the last end of month date was 3/20), then that transaction will be dated with the current date and summarized with the current date's records. Also, in this last case, a warning message will be written to the batch log that alerts you to the problem. The message you will receive is "*ALERT* Transactions have been found for previous months." The sadly post program identifies dept/class/subclass/location combinations within the transactions created during the day which are not available in week and month data tables. These combinations are seeded into the week and month data tables to ensure seamless roll up in the stock ledger.
End Of Half Rollup of Data/Calculations for Stock Ledger (saleoh)
|
Module Name |
saloeh.pc |
|
Description |
End Of Half Rollup of Data/Calculations for Stock Ledger |
|
Functional Area |
Stock Ledger |
|
Module Type |
Business Processing |
|
Module Technology |
ProC |
|
Catalog ID |
RMS337 |
|
Wrapper Script |
rmswrap_multi.ksh |
Design Overview
The End of Half Stock Ledger Processing is different from many of the other 'End of' processes in that it is also the program that controls how many months of stock ledger data remain on the tables, in addition to the updates to the Half Data table. This program should be run after the end-of-month processing for month 6 has run and before the end-of-month processing for month 1 has run.
The first step for this program is to delete records from stock ledger tables that are 18 months or older. Specifically, the tables that are deleted from are DAILY_DATA, WEEK_DATA, MONTH_DATA, HALF_DATA, and HALF_DATA_BUDGET. The 18-month limit is not a system parameter - it is hard-coded into the program.
The next step in this program is for new records to be written into HALF_DATA_BUDGET for each department/location for next year's half.
This program also rolls up the inter-stock take shrink amount and inter-stock take sales amount from the HALF_DATA table at the department/location level for this half and calculates the shrinkage percent to insert into HALF_DATA_BUDGET for the next year's half.
Restart/Recovery
There is no main driving cursor for this program. The different functions of this batch program have their own driving cursors. All the driving cursors are threaded by department using the v_restart_dept view. The logical unit of work (LUW) for the delete functions is a half number while the different insert functions have the following LUWs
-
half_data() - dept/class/subclass/location
-
half_data_budget() - dept/location
Data is committed every time the number of rows processed exceeds commit_max_ctr.
End of Year Inventory Position Snapshot (nwpyearend)
|
Module Name |
nwpyearend.pc |
|
Description |
End of Year Inventory Position Snapshot |
|
Functional Area |
Stock Count |
|
Module Type |
Business Processing |
|
Module Technology |
ProC |
|
Catalog ID |
RMS278 |
|
Wrapper Script |
rmswrap_multi.ksh |
Design Overview
This program takes a snapshot of the item's stock position and cost at the end of the year. When the end of year NWP snapshot process runs, it takes a snapshot of stock and weighted average cost (WAC) for every item/location combination currently holding stock. If there is not a record already on the NWP table for an item/location/year combination in the snapshot, a new record is added for that item/location/year combination.
Restart/Recovery
The logical unit of work for this program is set at the location/item level. Threading is done by supplier using the v_restart_store_wh view to thread properly. The commit_max_ctr field should be set to prevent excessive rollback space usage, and to reduce the overhead of file I/O. The changes will be posted when the commit_max_ctr value is reached and the value of the counter is subject to change based on implementation.
Design Assumptions
-
NWP refers to 'Niederstwertprinzip' and is a legal German accounting financial inventory reporting requirement for calculating year-end inventory position based on the last receipt cost.
-
The NWP Indicator system parameter supports this German specific inventory reporting requirement. For German customers, this needs to be 'Y' to allow for the annual NWP calculations & processes.
-
This is not relevant for customers outside Germany.
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.
Monthly Rollup of Data/Calculations for Stock Ledger (salmth)
|
Module Name |
salmth.pc |
|
Description |
Monthly Rollup of Data/Calculations for Stock Ledger |
|
Functional Area |
Stock Ledger |
|
Module Type |
Business Processing |
|
Module Technology |
ProC |
|
Catalog ID |
RMS343 |
|
Wrapper Script |
rmswrap_multi.ksh |
Design Overview
The Monthly Stock Ledger Processing program is responsible for performing the monthly summarization processing in the stock ledger in which day-level records are fetched from the transaction-level staging table and summed to the subclass/location/month level. Once the records are summarized, they are written to the MONTH_DATA table. This program processes one month for each program run - starting the latest month to be closed. For example, if it is currently June and both April and May are open, when the program runs, then only April will be closed.
In addition to the summarization processes done by this program, there are several month ending calculations done as well. The closing stock value, half to date goods available for sale (HTD GAFS), shrinkage and gross margin are calculated by calling a package function, based on the accounting method designated for the department - cost or retail. Additionally, the closing stock value for a processed month becomes opening stock value for the next month. Also, when this program is run, it will write a 'shell' record for the next month, populating the key fields on the table (subclass, location, and so on), the opening stock values at cost and retail, the inter-stock take sales and shrinkage amounts and the HTD GAFS at cost and retail. It may be noted that these shell records will be created only for those subclass/location/month combinations that have a non-zero value of either ending inventory, HTD GAFS or inter-stock take amounts.
This program can be run at any time during the month - not necessarily just at month-end. Open stock counts from the month may exist based on the system parameter (CLOSE_MTH_WITH_OPN_CNT_IND). If this indicator is 'Y', then retailers are able to keep a count open across a single month closing in the stock ledger and still close the month financially. A Unit & Value stock count is considered as open until all variances (both unit and value) have been reviewed and applied. Special processing exists if it is allowed and there are open stock counts from the current month. Open stock counts from previous months however cannot exist regardless of the setting.
Restart/Recovery
The logical unit of work (LUW) for this batch program is a dept/class/subclass/loc_type/location/currency_ind record. This batch program is threaded by department using the v_restart_dept view. Processed records are committed to the database after the LUW count has reached the commit_max_ctr.
Purge of Aged End of Year Inventory Positions (nwp_purge_job)
|
Module Name |
nwp_purge_job |
|
Description |
Purge of Aged End of Year Inventory Positions |
|
Functional Area |
Stock Ledger |
|
Module Type |
Admin - Ad hoc |
|
Module Technology |
Background Processing |
|
Catalog ID |
N/A |
|
Wrapper Script |
b8dwrap.ksh |
Design Overview
This background job is composed of two steps processing. It will have a threading assignment and a business logic processing.
Thread assignment program will filter eligible records from year-end inventory position table based on its purge criteria from system parameter settings. The NWP Retention Period parameter will determine certain amount of years have passed for NWP records before purging. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.
The Business logic program will process all records from the staging table. Using bulk processing, this program will delete the records from year-end inventory position table. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.
Key Tables Affected
Table 17-1 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
PERIOD |
Yes |
No |
No |
No |
|
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
|
RMS_BATCH_STATUS |
Yes |
No |
No |
No |
|
B8D_PROCESS_CONFIG |
Yes |
No |
No |
No |
|
JOB_AUDIT_LOGS |
No |
Yes |
No |
No |
|
B8D_NWP_PURGE_STG |
Ys |
Yes |
No |
Yes |
|
NWP |
Yes |
No |
No |
Yes |
Design Assumptions
-
NWP refers to 'Niederstwertprinzip' and is a legal German accounting financial inventory reporting requirement for calculating year-end inventory position based on the last receipt cost.
-
The NWP Indicator system parameter supports this German specific inventory reporting requirement. For German customers, this needs to be 'Y' to allow for the annual NWP calculations & processes.
-
This is not relevant for customers outside Germany.
Purge of Aged End of Year Inventory Positions (nwppurge)
|
Module Name |
nwppurge.pc |
|
Description |
Purge of Aged End of Year Inventory Positions |
|
Functional Area |
Stock Ledger |
|
Module Type |
Admin |
|
Module Technology |
ProC |
|
Catalog ID |
RMS277 |
|
Wrapper Script |
rmswrap.ksh |
Design Overview
This program purges the records from the table NWP after a certain amount of years have passed. The number of years is held in the configurable system level parameter NWP_RETENTION_PERIOD.
Restart/Recovery
Restart/recovery is not applicable, but the records will be committed based on the commit max counter setup in the restart control table.
Design Assumptions
-
NWP refers to 'Niederstwertprinzip' and is a legal German accounting financial inventory reporting requirement for calculating year-end inventory position based on the last receipt cost.
-
The NWP Indicator system parameter supports this German specific inventory reporting requirement. For German customers, this needs to be 'Y' to allow for the annual NWP calculations & processes.
-
This is not relevant for customers outside Germany.
Purge Stock Ledger History (salprg)
|
Module Name |
salprg.pc |
|
Description |
Purge Stock Ledger History |
|
Functional Area |
Stock Ledger |
|
Module Type |
Admin |
|
Module Technology |
ProC |
|
Catalog ID |
RMS344 |
|
Wrapper Script |
rmswrap.ksh |
Design Overview
This program is used to purge old transaction-level stock ledger records from the Transaction Data History table. The Retain Transaction Data (TRAN_DATA_RETAINED_DAYS_NO) system parameter is used to define how many days the Transaction Data History records should be kept in the system. This program will be run nightly to remove any records older than the current date - the "Retain Transaction Data" days.
This batch also purges data from the MONTH_DATA_ERRORS table in a manner similar to that used for TRAN_DATA_ERRORS. Records that have been posted to GL (posted to GL='Y') can be purged from the table during the subsequent batch run. Records posted to Clearing (Posted to GL='C'learing) during end-of-month processing for any given month will be purged by the batch during the end-of-month processing for the following month.
Purge Stock Ledger History (stkledgr_hist_purge_job)
|
Module Name |
stkledgr_hist_purge_job |
|
Description |
Purge Stock Ledger History |
|
Functional Area |
Stock Ledger |
|
Module Type |
Admin - Ad hoc |
|
Module Technology |
Background Processing |
|
Catalog ID |
N/A |
|
Wrapper Script |
b8dwrap.ksh |
Design Overview
This background job is composed of two steps processing. It will have a threading assignment and a business logic processing.
Thread assignment program will filter eligible records from general ledger key mapping table based on its purge criteria from system parameter settings. The Retain Transaction Data Days parameter will determine how many days the Transaction Data History records should be kept in the system. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.
The Business logic program will process all records from the staging table. Using bulk processing, this program will delete the records from general ledger key mapping table. PARTITION_SQL.PURGE_INTERVAL_PARTITION is also called passing the target table name "TRAN_DATA_HISTORY" and will execute the proper deletion/purging of records from target table by exercising table partitioning handling such as Dropping Interval Partition (same as truncate or delete from table). It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.
The decision to insert or not to insert the records into the history tables is based on the Archive Indicator and Archive Job Indicator from the Background Process Configuration table.
-
If both the Archive Indicator and Archive Job Indicator values are Y, then the data from the base tables are inserted into the history tables.
-
If both indicators are set to ‘N’, then the records are deleted from the base tables without inserting into the history tables.
Note:
For more information on how to configure this process for archiving, see the Merchandising Implementation Guide section titled "Background Process Configuration".
Key Tables Affected
Table 17-2 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
PERIOD |
Yes |
No |
No |
No |
|
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
|
RMS_BATCH_STATUS |
Yes |
No |
No |
No |
|
B8D_PROCESS_CONFIG |
Yes |
No |
No |
No |
|
JOB_AUDIT_LOGS |
No |
Yes |
No |
No |
|
B8D_STKLEDGR_HIST_PURGE_STG |
Yes |
Yes |
No |
Yes |
|
TRAN_DATA_HISTORY |
No |
No |
No |
Yes |
|
KEY_MAP_GL |
No |
No |
No |
Yes |
|
TRAN_DATA_HISTORY_PRG_HIST |
No |
Yes |
No |
No |
Stage Stock Ledger Transactions for Additional Processing (salstage)
|
Module Name |
salstage.pc |
|
Description |
Stage Stock Ledger Transactions for Additional Processing |
|
Functional Area |
Stock Ledger |
|
Module Type |
Business Processing |
|
Module Technology |
ProC |
|
Catalog ID |
RMS345 |
|
Wrapper Script |
rmswrap.ksh |
Design Overview
In order to make the rollup and extraction of the stock ledger transaction data flexible, this program moves the data on the TRAN_DATA to the IF_TRAN_DATA staging table. This will enable the processes that are writing records to TRAN_DATA to continue in a seamless manner, whereas the processes that rolls the data up to a different level or extract the data to external systems can work without affecting batch timetables.
This process will be achieved by locking the TRAN_DATA table and moving all of the data to the staging table. The original TRAN_DATA table will be emptied and the lock on the table will be released. Before this processing occurs, the staging table will first be emptied to ensure that data is not processed twice. Because the data on the TRAN_DATA and IF_TRAN_DATA tables is very transitional, these tables will fill up and be truncated at least once a day if not several times per day.
Stock Ledger Table Maintenance (salmaint)
|
Module Name |
salmaint.pc |
|
Description |
Stock Ledger Table Maintenance |
|
Functional Area |
Stock Ledger |
|
Module Type |
Admin |
|
Module Technology |
ProC |
|
Catalog ID |
RMS342 |
|
Wrapper Script |
rmswrap.ksh |
Stock Ledger Table Maintenance (stock_ledger_purge_job)
|
Module Name |
stock_ledger_purge_job |
|
Description |
Stock Ledger Table Maintenance |
|
Functional Area |
Stock Ledger |
|
Module Type |
Admin - Ad hoc |
|
Module Technology |
Background Processing |
|
Catalog ID |
N/A |
|
Wrapper Script |
b8dwrap.ksh |
Design Overview
This background job is composed of one step processing only. It will retain the business logic processing from the original batch program algorithm.
The Business logic program will invoke a call to a new program specific for handling historical tables such as Half Data table, and so on. that are considered partitioned tables. PARTITION_SQL.PURGE_INTERVAL_PARTITION is called passing each target table names "HALF_DATA", "DAILY_DATA", "WEEK_DATA", and "MONTH_DATA" This called program will execute the proper deletion/purging of records from target table by exercising table partitioning handling such as Dropping Interval Partition (same as truncate or delete from table).
Key Tables Affected
Table 17-3 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
|
SYSTEM_VARIABLES |
Yes |
No |
No |
No |
|
RMS_BATCH_STATUS |
Yes |
No |
No |
No |
|
JOB_AUDIT_LOGS |
No |
Yes |
No |
No |
|
ALL_PART_TABLES |
Yes |
No |
No |
No |
|
ALL_TAB_PARTITIONS |
Yes |
No |
No |
No |
|
HALF_DATA |
No |
No |
No |
Yes |
|
DAILY_DATA |
No |
No |
No |
Yes |
|
WEEK_DATA |
No |
No |
No |
Yes |
|
MONTH_DATA |
No |
No |
No |
Yes |
Weekly Rollup of Data/Calculations for Stock Ledger (salweek)
|
Module Name |
salweek.pc |
|
Description |
Weekly Rollup of Data/Calculations for Stock Ledger |
|
Functional Area |
Stock Ledger |
|
Module Type |
Business Processing |
|
Module Technology |
ProC |
|
Catalog ID |
RMS346 |
|
Wrapper Script |
rmswrap.ksh |
Design Overview
This program is responsible for performing the weekly summarization processing in the stock ledger. This program processes all weeks that are in the month for which month-end process has not been run, up to the current week. It rolls up data on DAILY_DATA, DAILY_DATA_TEMP and WEEK_DATA_TEMP to the corresponding dept/class/subclass/location/half-month/week/currency level and updates the WEEK_DATA table.
This program processes all weeks that are in the month for which month-end process has not been run, up to the current week. This program can be run at any time during the week - not necessarily just at week-end, as it must be run before the Monthly Stock Ledger Processing, which can be run at any time after the closing of a month.
In addition to the summarization processes done by this program, there are several week ending calculations done as well. The closing stock value, half to date goods available for sale (HTD GAFS), shrinkage and gross margin are calculated by calling a package function, based on the accounting method designated for the department - cost or retail. Additionally, the closing stock value for a processed week becomes opening stock value for the next week. Also, if this program is run at the end of the week, it will write a 'shell' record for the next week, populating the key fields on the table (subclass, location, and so on), the opening stock values at cost and retail and the HTD GAFS at cost and retail. It may be noted that these shell records will be created only for those subclass/location/ week combinations that have a non-zero value of ending inventory or a non-zero value of HTD GAFS.