20 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). This overview describes how the stock ledger is set up, the accounting methods that impact stock ledger calculations, the primary stock ledger tables, and the batch programs and PL/SQL packages that process data held on the tables.

Note: For additional information about stock ledger transaction posting, see Sales Posting. For additional information about integration of data (including month level stock ledger data) to the General Ledger, see Integration with General Ledger.

Stock Ledger Set Up and Accounting Methods

The operation of the stock ledger is dependent upon a number of options that you choose for your implementation of RMS. To understand how your company uses the stock ledger, you can examine the settings that are described here.

The stock ledger is implemented at the subclass level and supports both the retail and cost methods of accounting. The method of accounting may vary by department and is set on the department (DEPS) table in the profit_calc_type column. The '1' setting indicates that profit is calculated by direct cost. The '2' setting indicates that profit is calculated by retail inventory.

If you select the cost method of accounting, two options are available: average cost or standard cost. The chosen option is represented on the SYSTEM_OPTIONS table in the std_av_ind column, where the standard cost option is indicated by the 'S' setting, and the average cost option is indicated by the 'A' setting. The selected option then applies to all departments that use the cost method stock ledger option.

If you select the retail method of accounting, you can choose to implement the retail components of all transactions either to include value-added tax (VAT) or to exclude VAT. You accomplish through a system-level option vat_ind on the SYSTEM_OPTIONS table.

Note: If the value-added tax (VAT) system option is enabled in RMS, rolled-up stock ledger data values for the retail accounting method include value-added tax.

For sales history purposes, history is maintained based on the calendar that you choose. If your company uses the 4-5-4 calendar, sales history is tracked weekly. If you use the Gregorian (or 'normal') calendar, sales history is tracked monthly. The calendar setting is held on the SYSTEM_OPTIONS table in the calendar_454_ind column.

Process Flow

Figure 20-1 Process Flow - Stock Ledger

1. Assorted RMS Inventory and Sales Transactions write to the working transaction data table (TRAN_DATA).

2. Salstage.pc moves transaction data from the working table to the snapshot transaction data table (IF_TRAN_DATA) for additional processing.

3. Saldly.pc rolls up the snapshot transaction data (IF_TRAN_DATA) and persists it to the daily rollup table (DAILY_DATA).

4. Salapnd.pc moves data from the snapshot transaction data table (IF_TRAN_DATA) to the history table (TRAN_DATA_HISTORY).

5. Salweek.pc rolls up daily stock ledger data (DAILY_DATA) to weekly stock ledger data (WEEK_DATA).

6. Salmth.pc rolls up weekly stock ledger data (WEEK_DATA) to monthly stock ledger data (MONTH_DATA).

7. Saleoh.pc rolls up monthly stock ledger data (MONTH_DATA) to half level stock ledger data (HALF_DATA).

8. Salprg.pc deletes aged transaction history (TRAN_DATA_HISTORY).

Batch Design Summary

The following batch designs are included in this functional area:

• salstage.pc (Stage Stock Ledger Transactions for Additional Processing)

• salapnd.pc (Append Stock Ledger Information to History Tables)

• saldly.pc (Daily Rollup of Transaction Data for Stock Ledger)

• salweek.pc (Weekly Rollup of Data/Calculations for Stock Ledger)

• salmth.pc (Monthly Rollup of Data/Calculations for Stock Ledger)

• salmaint.pc (Stock Ledger Table Maintenance)

• saleoh.pc (End Of Half Rollup of Data/Calculations for Stock Ledger)

• salprg.pc (Purge Stock Ledger History)

• nwppurge.pc (Optional End of Year Inventory Position Purge)

• nwpyearend.pc (Optional End of Year Inventory Position Snapshot)

• stlgdnld (Daily or Weekly Download of Stock Ledger Data)

• Otbdlsal (Open To Buy Download Stock Ledger)

• trandataload.ksh (External Transaction Data Upload)

• trandataprocess.ksh (External Transaction Data Process)

salstage (Stage Stock Ledger Transactions for Additional Processing)

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
Runtime Parameters	N/A

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.

Scheduling Constraints

Table 20-1 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	This module should run after Sales Process (uploadsales.ksh and salesprocess.ksh) but before saldly.pc, salweek.pc and salapnd.pc, rpmmovavg.pc. Within the deal cycle, it should run before dealact.pc
Pre-Processing	salesprocess.ksh
Post-Processing	saldly salapnd salweek dealact rpmmovavg fifgldn1 fifgldn2
Threading Scheme	Threading is implicit via the use of the Oracle Parallel Query Option. The insert/select query should be tuned for each specific environment to achieve the best throughput

Restart/Recovery

NA

Key Tables Affected

Table 20-2 Key Tables Affected

Table	Select	Insert	Update	Delete
IF_TRAN_DATA	No	Yes	No	Yes
TRAN_DATA_A	Yes	Yes	No	Yes
TRAN_DATA_B	Yes	Yes	No	Yes
DEAL_PERF_TRAN_DATA	No	Yes	No	Yes
PERIOD	Yes	No	No	No
DEAL_PERF_DATA_TEMP	Yes	No	No	No
STORE	Yes	No	No	No
WH	Yes	No	No	No
PARTNER	Yes	No	No	No
ALL_CONSTRAINTS	Yes	No	No	No

Design Assumptions

NA

salapnd (Append Stock Ledger Information to History Tables)

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
Runtime Parameters	NA

Design Overview

The purpose of this program is to move data from the staging table for transaction data (IF_TRAN_DATA) into the historical transaction data table (TRAN_DATA_HISTORY). 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.

Scheduling Constraints

Table 20-3 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	After fifgldnld1.pc After fifgldnld2.pc After fifgldnld3.pc
Pre-Processing	salstage.pc, all extraction, and all processing
Post-Processing	NA
Threading Scheme	Threading will be implicit through the use of the Oracle Parallel Query Option. The insert/select query should be tuned for each specific environment to achieve the best throughput

Restart/Recovery

NA

Key Tables Affected

Table 20-4 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
SYSTEM_VARIABLES	Yes	No	No	No
IF_TRAN_DATA	Yes	No	No	No
TRAN_DATA_HISTORY	No	Yes	No	No

Design Assumptions

NA

saldly (Daily Rollup of Transaction Data for Stock Ledger)

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
Runtime Parameters	NA

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.

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 the user to the problem. The message the users will receive is "*ALERT* Transactions have been found for previous months."

Scheduling Constraints

Table 20-5 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	NA
Pre-Processing	Run salstage to move records from TRAN_DATA to IF_TRAN_DATA
Post-Processing	Salweek (on end of week day)
Threading Scheme	Threaded by department

Restart/Recovery

The logical unit of work is department/class/subclass. This batch program is multithreaded using the v_restart_dept view.

Key Tables Affected

Table 20-6 Key Tables Affected

Table	Select	Insert	Update	Delete
SA_STORE_DAY	Yes	No	No	No
SA_VOUCHER	Yes	No	Yes	No
STORE	Yes	No	No	No
PERIOD	Yes	No	No	No
SYSTEM_VARIABLES	Yes	No	No	No
IF_TRAN_DATA	Yes	No	No	No
DAILY_DATA	Yes	Yes	Yes	No
DAILY_DATA_TEMP	No	Yes	No	No
DAILY_DATA_BACKPOST	No	Yes	No	No
STORE	Yes	No	No	No
WH	Yes	No	No	No
PARTNER	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
MV_LOC_SOB	Yes	No	No	No

Design Assumption

NA

salweek (Weekly Rollup of Data/Calculations for Stock Ledger)

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
Runtime Parameters	NA

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.

Scheduling Constraints

Table 20-7 Scheduling Constraints

Schedule Information	Description
Frequency	Weekly
Scheduling Considerations	This program should run after saldly.pc, stkdly.pc, salapnd.pc and immediately before salmth.pc (in weeks that are at end of month)
Pre-Processing	prepost salweek pre
Post-Processing	prepost salweek post
Threading Scheme	Multithreaded on department

Restart/Recovery

The logical unit of work is dept/class/subclass combination. A commit will take place when number of dept/class/subclass combination records processed is equal to commit max counter in restart control table.

Key Tables Affected

Table 20-8 Key Tables Affected

Table	Select	Insert	Update	Delete
SALWEEK_RESTART_DEPT	Yes	No	No	No
SALWEEK_C_WEEK	Yes	No	No	No
SALWEEK_C_DAILY	Yes	No	No	No
DAILY_DATA	Yes	No	No	No
WEEK_DATA	Yes	Yes	Yes	No
PARTNER	Yes	No	No	No
STORE	Yes	No	No	No
WH	Yes	No	No	No
DEPS	Yes	No	No	No
HALF_DATA_BUDGET	Yes	No	No	No
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
SYSTEM_VARIABLES	Yes	No	No	No

Design Assumptions

NA

salmth (Monthly Rollup of Data/Calculations for Stock Ledger)

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
Runtime Parameters	NA

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.

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.

Scheduling Constraints

Table 20-9 Scheduling Constraints

Schedule Information	Description
Frequency	Monthly (end of month)
Scheduling Considerations	Can run any time after end-of-month date Salweek.pc must run prior to salmth.pc
Pre-Processing	NA
Post-Processing	Prepost salmth_post
Threading Scheme	Threaded by department

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.

Key Tables Affected

Table 20-10 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
SYSTEM_VARIABLES	Yes	No	No	No
STAKE_HEAD	Yes	No	No	No
STAKE_PROD_LOC	Yes	No	No	No
PARTNER	Yes	No	No	No
STORE	Yes	No	No	No
WH	Yes	No	No	No
MONTH_DATA	Yes	Yes	Yes	No
DAILY_DATA	Yes	No	No	No
DEPS	Yes	No	No	No
WEEK_DATA	Yes	No	No	No
HALF_DATA_BUDGET	Yes	No	No	No

Design Assumptions

NA

salmaint (Stock Ledger Table Maintenance)

Module Name	salmaint.pc
Description	Stock Ledger Table Maintenance
Functional Area	Stock Ledger
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS342
Runtime Parameters	NA

Design Overview

This module is run as either salmaint pre or salmaint post. The salmaint pre functionality adds partitions to the HALF_DATA, DAILY_DATA, WEEK_DATA and MONTH_DATA tables. The salmaint post functionality drops partitions or purges the above tables (if the table is not partitioned) for an old half.

Scheduling Constraints

Table 20-11 Scheduling Constraints

Schedule Information	Description
Frequency	Half yearly
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

NA

Locking Strategy

NA

Security Considerations

NA

Performance Considerations

NA

Key Tables Affected

Table 20-12 Key Tables Affected

Table	Select	Insert	Update	Delete
SYSTEM_OPTIONS	Yes	No	No	No
SYSTEM_VARIABLES	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

I/O Specification

NA

saleoh (End Of Half Rollup of Data/Calculations for Stock Ledger)

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
Runtime Parameters	NA

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, MONTH_DATA_BUDGET 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 for HALF_DATA, MONTH_DATA_BUDGET and HALF_DATA_BUDGET for the next half. It inserts one row into HALF_DATA for each subclass/location combination for the next half, six rows (one for every month of the half) into MONTH_DATA_BUDGET for each department/location for next year's half and one row 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.

Scheduling Constraints

Table 20-13 Scheduling Constraints

Schedule Information	Description
Frequency	Half yearly
Scheduling Considerations	Run at the end of the half, after the monthly process has been completed for month six (6) of the current half, and before the salmth process for the first month of the next half
Pre-Processing	Salmth, prepost saleoh pre
Post-Processing	NA
Threading Scheme	Threaded by department

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

• month_data_budget() - dept/location

• half_data_budget() - dept/location

• Data is committed every time the number of rows processed exceeds commit_max_ctr.

Key Tables Affected

Table 20-14 Key Tables Affected

Table	Select	Insert	Update	Delete
SYSTEM_OPTIONS	Yes	No	No	No
SYSTEM_VARIABLES	Yes	No	No	No
MONTH_DATA_BUDGET	Yes	Yes	No	Yes
HALF_DATA	Yes	Yes	No	No
HALF_DATA_BUDGET	Yes	Yes	No	No

Design Assumptions

NA

salprg (Purge Stock Ledger History)

Module Name	salprg.pc
Description	Purge Stock Ledger History
Functional Area	Stock Ledger
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS344
Runtime Parameters	NA

Design Overview

This program is used to purge old transaction-level stock ledger records from the Transaction Data History table (TRAN_DATA_HISTORY). 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.

Scheduling Constraints

Table 20-15 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

NA

Key Tables Affected

Table 20-16 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
TRAN_DATA_HISTORY	No	No	No	Yes
KEY_MAP_GL	No	No	No	Yes

Design Assumptions

NA

nwppurge (Purge of Aged End of Year Inventory Positions)

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
Runtime Parameters	NA

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.

Scheduling Constraints

Table 20-17 Scheduling Constraints

Schedule Information	Description
Frequency	Yearly
Scheduling Considerations	This program only needs to be scheduled for clients who use NWP processing. See Design Assumptions for more details
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

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.

Key Tables Affected

Table 20-18 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
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.

nwpyearend (End of Year Inventory Position Snapshot)

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
Runtime Parameters	NA

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.

Scheduling Constraints

Table 20-19 Scheduling Constraints

Schedule Information	Description
Frequency	Annually (last day of year)
Scheduling Considerations	Only needed in specific markets. See design considerations for more information
Pre-Processing	refeodinventory.ksh must run successfully prior to execution to ensure that ITEM_LOC_SOH_EOD is up-to-date
Post-Processing	NA
Threading Scheme	Multithreaded by store_wh

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.

Key Tables Affected

Table 20-20 Key Tables Affected

Table	Select	Insert	Update	Delete
NWP_FREEZE_DATE	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
NWP	Yes	Yes	Yes	No
ITEM_LOC_SOH_EOD	Yes	No	No	No

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.

stlgdnld (Daily or Weekly Download of Stock Ledger Data)

Module Name	stlgdnld.pc
Description	Weekly or Historical Download of Stock Ledger Data
Functional Area	Stock Ledger
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS17
Runtime Parameters	NA

Design Overview

This program extracts stock ledger data at the item level. The program can extract data for a historic period or for the most current complete week. The program accepts an input file that determines whether the extract is a historic extract or a weekly extract.

This program is often used in integration with RPAS applications.

Scheduling Constraints

Scheduling constraints vary depending on whether the program is run for normal weekly data or historical data.

Normal Weekly Data

Table 20-21 Normal Weekly Data

Schedule Information	Description
Frequency	Weekly
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	Multi-threaded by dept

Historical Data

Table 20-22 Historical Data

Schedule Information	Description
Frequency	As Needed
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	Multi-threaded by dept

Restart/Recovery

The logical unit of work for this program is set at item, location type, location and date. Threading is done by dept using the v_restart_dept view to thread properly.

The changes will be posted when the commit_max_ctr value is reached. The commit_max_ctr field should be set to prevent excessive rollback space usage, and to reduce the overhead of file I/O. The value of the counter is subject to change based on implementation.

Key Tables Affected

Table 20-23 Key Tables Affected

Table	Select	Insert	Update	Delete
TRAN_DATA_HISTORY	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
SYSTEM_VARIABLES	Yes	No	No	No
PERIOD	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	The input filename is a runtime parameter. The output filename is hardcoded to stkldgr%d.dat where %d is substituted with the domain id. Each run of the program can produce multiple output files, one for each department. Additional input parameters are defined in the input file
Integratin Contract	IntCon000034 (output file)

Input File Layout

Table 20-24 Input File Layout

Field Name	Field Type	Default Value	Description
Task Indicator	Char(1)	NA	Task Indicator. Valid values are 'H' - historical, 'W' - weekly
From Date	Char(8)	NA	From Date in 'YYYYMMDD' format
To Date	Char(8)	NA	To Date in 'YYYYMMDD' format

Output File Layout

Table 20-25 Output File Layout

Field Name	Field Type	Default Value	Description
Item	Char(25)	NA	Item number
Location Type	Char(1)	NA	Location Type Valid values are 'S','W'
Location	Number(20)	NA	Location Number
Eow_date	Char(8)	NA	End of Week date in 'YYYYMMDD' format
Update_Ind	Char(1)	NA	Update Indicator Valid values are 'I ' and 'U'
Regular_sales_retail	Number(25,4)	NA	Regular sales value (retail)
Regular_sales_cost	Number(25,4)	NA	Regular sales value (cost)
Regular_sales_units	Number(17,4)	NA	Regular sales value (units)
Promo_sales_retail	Number(25,4)	NA	Promo sales value (retail)
Promo_sales_cost	Number(25,4)	NA	Promo sales value (cost)
Promo_sales_units	Number(17,4)	NA	Promo sales value (units)
Clear_sales_retail	Number(25,4)	NA	Clearance sales value (retail)
Clear_sales_cost	Number(25,4)	NA	Clearance sales value (cost)
Clear_sales_units	Number(17,4)	NA	Clearance sales value (units)
Sales_retail_excluding_vat	Number(25,4)	NA	Sales value excluding vat (retail)
Custom_returns_retail	Number(25,4)	NA	Custom returns value (retail)
Custom_returns_cost	Number(25,4)	NA	Custom returns value (cost)
Custom_returns_units	Number(17,4)	NA	Custom returns value (units)
Rtv_retail	Number(25,4)	NA	Return to Vendor value (retail)
Rtv_cost	Number(25,4)	NA	Return to Vendor value (cost)
Rtv_units	Number(17,4)	NA	Return to Vendor value (units)
Reclass_in_retail	Number(25,4)	NA	Reclass In value (retail)
Reclass_in_cost	Number(25,4)	NA	Reclass In value (cost)
Reclass_in_units	Number(17,4)	NA	Reclass In value (units)
Reclass_out_retail	Number(25,4)	NA	Reclass Out value (retail)
Reclass_out_cost	Number(25,4)	NA	Reclass Out value (cost)
Reclass_out_units	Number(17,4)	NA	Reclass Out value (units)
Perm_markdown_value	Number(25,4)	NA	Permanent markdown value (retail)
Prom_markdown_value	Number(25,4)	NA	Promotion markdown value (retail)
Clear_markdown_value	Number(25,4)	NA	Clearance markdown value (retail)
Markdown_cancel_value	Number(25,4)	NA	Markdown cancel value
Markup_value	Number(25,4)	NA	Markup value
Markup_cancel_value	Number(25,4)	NA	Markup cancel value
Stock_adj_retail	Number(25,4)	NA	Stock adjustment value (retail)
Stock_adj_cost	Number(25,4)	NA	Stock adjustment value (cost)
Stock_adj_units	Number(17,4)	NA	Stock adjustment value (units)
Received_retail	Number(25,4)	NA	Received value (retail)
Received_cost	Number(25,4)	NA	Received value (cost)
Received_units	Number(17,4)	NA	Received value (units)
Tsf_in_retail	Number(25,4)	NA	Transfer In value (retail)
Tsf_in_cost	Number(25,4)	NA	Transfer In value (cost)
Tsf_in_units	Number(17,4)	NA	Transfer In value (units)
Tsf_out_retail	Number(25,4)	NA	Transfer Out value (retail)
Tsf_out_cost	Number(25,4)	NA	Transfer Out value (cost)
Tsf_out_units	Number(17,4)	NA	Transfer Out value (units)
Freight_cost	Number(25,4)	NA	Freight cost
Employee_disc_retail	Number(25,4)	NA	Employee disc (retail)
Cost_variance	Number(25,4)	NA	Cost variance
Wkroom_other_cost_sales	Number(25,4)	NA	Wkroom other sales (cost)
Cash_disc_retail	Number(25,4)	NA	Cash disc (retail)
Freight_claim_retail	Number(25,4)	NA	Freight Claim (retail)
Freight_claim_cost	Number(25,4)	NA	Freight Claim (cost)
Freight_claim_units	Number(25,4)	NA	Freight Claim (Units)
Stock_adj_cogs_retail	Number(25,4)	NA	Stock Adjust COGS (retail)
Stock_adj_cogs_cost	Number(25,4)	NA	Stock Adjust COGS (cost)
Stock_adj_cogs_units	Number(25,4)	NA	Stock Adjust COGS (Units)
Intercompany_in_retail	Number(25,4)	NA	Intercompany In value (retail)
Intercompany_in_cost	Number(25,4)	NA	Intercompany In value (cost)
Intercompany_in_units	Number(25,4)	NA	Intercompany In value (units)
Intercompany_out_retail	Number(25,4)	NA	Intercompany Out value (retail)
Intercompany_out_cost	Number(25,4)	NA	Intercompany Out value (cost)
Intercompany_out_units	Number(25,4)	NA	Intercompany Out value (units)
Intercompany_markup	Number(25,4)	NA	Intercompany Markup
Intercompany_markup_units	Number(25,4)	NA	Intercompany Markup (units)
Intercompany_markdown	Number(25,4)	NA	Intercompany Markdown
Intercompany_markdown_units	Number(25,4)	NA	Intercompany Markdown (units)
Wo_activity_upd_inv	Number(25,4)	NA	Work Order Activity - Update Inventory (cost)
Wo_activity_upd_inv_units	Number(25,4)	NA	Work Order Activity - Update Inventory (units)
Wo_activity_post_fin	Number(25,4)	NA	Work Order Activity - Post to Financials (retail)
Wo_activity_post_fin_units	Number(25,4)	NA	Work Order Activity - Post to Financials (units)

Design Assumptions

NA

otbdlsal (Open To Buy Download Stock Ledger)

Module Name	otbdlsal.pc
Description	Open To Buy Download Stock Ledger
Functional Area	OTB - Stock Ledger to Planning System Interface
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS16

Design Overview

This module will sum stock ledger data from the DAILY_DATA table and opening stock information from the WEEK_DATA table across the current week, grouping by department, class, subclass, location and date, and export the data to a flat file for use by an outside planning system.

Scheduling Constraints

Table 20-26 Scheduling Constraints

Schedule Information	Description
Frequency	Weekly
Scheduling Considerations	This program must be run after ORDUPD (order upload.) It also must be run after SALWEEK for the week just ended. This program and OTBDNLD can run anytime after SALWEEK, but SALDLY cannot run between OTBDNLD, OTBDLSAL and OTBDLORD
Pre-Processing	Ordupd.pc, salweek.pc
Post-Processing	NA
Threading Scheme	N/A. Table-based array processing is used to speed up performance

Restart/Recovery

The logical unit of work for the OTBDLSAL module is department, class, subclass and location. The commit_max_ctr field should be set to prevent excessive rollback space usage, and to reduce the overhead of the file I/O. The recommended commit counter setting is 10000 records. Each time the record counter equals the maximum recommended commit number, an application image array record will be written to the restart_start_array for restart/recovery if a fatal error occurs.

Locking Strategy

NA

Security Considerations

NA

Performance Considerations

NA

Key Tables Affected

Table 20-27 Key Tables Affected

Table	Select	Insert	Update	Delete
DAILY_DATA	Yes	No	No	No
WEEK_DATA	Yes	No	No	No
PERIOD	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	OTB - Stock Ledger to Planning System Interface IntCon00030

File Layout

Table 20-28 File Layout

Record Name	Field Name	Field Type	Default Value	Description
FHEAD	File Type Record Descriptor	Char(5)	FHEAD	Identifies file record type
	File Line Sequence Number	Number(10)	0000000001	Keeps track of the record's position in the file by line number
	File Type Definition	Char(4)	STKE	Identifies file as Stock Ledger Export
	File Create Date	Char(14)	vdate	Date file was written by batch program in YYYYMMDD format. Remaining six characters are blank.
FDETL	File Type Record Descriptor	Char(5)	FDETL	Identifies file record type
	File Line Sequence Number	Number(10)	line number in file	Keeps track of the record's position in the file by line number
	Transaction Set Control Number	Number(14)	sequence number	Used to force unique file check
	Department	Number(4)	NA	The ID number of a department
	Class	Number(4)	NA	The ID number of a class within the department given
	Subclass	Number(4)	NA	The ID number of a subclass within the class given
	Loc_type	Char(1)	NA	The type of the location from which stock ledger data was collected
	Location	Number(10)	NA	The location from which stock ledger data was collected
	Half No.	Number(5)	NA	The half number for this stock ledger data
	Month No.	Number(2)	NA	The month number in the half for this stock ledger data
	Week No.	Number(2)	NA	The week number in the month for this stock ledger data
	Open Stock Retail	Number(20,4)	NA	The retail opening stock from the week_data table *10000 (implied 4 decimal places) for this stock ledger period
	Open Stock Cost	Number(20,4)	NA	The cost opening stock from the week_data table *10000 (implied 4 decimal places) for this stock ledger period
	Stock Adjustments Retail	Number(20,4)	NA	The retail stock adjustments summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Stock Adjustments Cost	Number(20,4)	NA	The cost stock adjustments summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Purchases Retail	Number(20,4)	NA	The retail purchases summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Purchases Cost	Number(20,4)	NA	The cost purchases summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	RTV Retail	Number(20,4)	NA	The retail return to vendor amount summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	RTV Cost	Number(20,4)	NA	The cost return to vendor amount summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Freight Cost	Number(20,4)	NA	The freight cost summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Net Sales Retail	Number(20,4)	NA	The retail net sales summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Net Sales Cost	Number(20,4)	NA	The cost net sales summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Returns Retail	Number(20,4)	NA	The retail returns amount summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Returns Cost	Number(20,4)	NA	The cost returns amount summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Promotional Markdowns Retail	Number(20,4)	NA	The retail promotional markdowns summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Markdown Cancellations Retail	Number(20,4)	NA	The retail markdown cancellations summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Employee Discount Retail	Number(20,4)	NA	The retail employee discounts amount summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Workroom Amount	Number(20,4)	NA	The workroom amount summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Cash Discount Amount	Number(20,4)	NA	The cash discounts amount summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Sales Units	Number(12,4)	NA	The sales units summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Markups Retail	Number(20,4)	NA	The retail markups summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Markup Cancellations Retail	Number(20,4)	NA	The retail markup cancellations summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Clearance Markdowns Retail	Number(20,4)	NA	The retail clearance markdowns summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Permanent Markdowns Retail	Number(20,4)	NA	The retail permanent markdowns summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Freight Claim Retail	Number(20,4)	NA	The retail freight claim summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Freight Claim Cost	Number(20,4)	NA	The cost freight claim summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Stock Adjust Cost of Goods Sold (COGS) Retail	Number(20,4)	NA	The retail stock adjust COGS summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Stock Adjust Cost of Goods Sold (COGS) Cost	Number(20,4)	NA	The cost stock adjust COGS summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Inter-company In Retail	Number(20,4)	NA	The Inter-company In retail summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Inter-company In Cost	Number(20,4)	NA	The Inter-company In cost summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Inter-company Out Retail	Number(20,4)	NA	The Inter-company Out Retail summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Inter-company Out Cost	Number(20,4)	NA	The Inter-company Out Cost summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Inter-company Markup	Number(20,4)	NA	The Inter-company Markup summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Inter-company Markdown	Number(20,4)	NA	The Inter-company Markdown summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Work Order Activity Update Inventory	Number(20,4)	NA	The Work Order Activity Update Inventory summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
	Work Order Activity Post Finishing	Number(20,4)	NA	The Work Order Activity Post Finishing summed from the DAILY_DATA table *10000 (implied 4 decimal places) for this stock ledger period
FTAIL	File Type Record Descriptor	Char(5)	FTAIL	Identifies file record type
	File Line Sequence Number	Number(10)	NA	Keeps track of the record's position in the file by line number
	Control Number File Line Count	Number(10)	NA	Total number of all transaction lines, not including file header and trailer

trandataload.ksh (External Transaction Data Upload)

Module Name	trandataload.ksh
Description	External Transaction Data Upload
Functional Area	Finance
Module Type	Integration
Module Technology	KSH
Catalog ID	RMS 376
Runtime Parameters	NA

Design Overview

This process, along with trandataprocess.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 RMS transaction, and are based on a determination made outside of RMS. The records written through this mechanism function exactly the same as records written by normal RMS 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 RMS 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 RMS.

The trandataload script loads the staging table STAGE_EXT_TRAN_DATA table from a flat file using SQL Loader and divides the data into chunks to be processed in parallel threads based on the commit_max_counter and num_threads value on RESTART_CONTROL table.

This script accepts the following input parameters:

• Database Connect string

• File load indicator – This indicator is passed as Y if a flat file has to be loaded into the table STAGE_EXT_TRAN_DATA else its N

• Input file – This is the path of the input file. This is mandatory when File load indicator is Y.

The SQL loading from a flat file is optional in the script. If File load indicator is Y the program validates if the input file exists and logs an error in case the input file does not exist. The SQL Load (sqlldr) process loads the input file using control file - trandataload.ctl into the STAGE_EXT_TRAN_DATA table.

• A fatal error from sqlldr will halt the process.

• Rejected records are a non-fatal error and loader will continue processing and create bad file and discard files in case the input file does not match the expected format.

If the user has chosen not to load data into the staging table (File load indicator 'N') then the batch assumes that data has been loaded on the staging table from a different source. After the loading process is complete, the batch divides the data into chunks. If the staging table is empty or all the records are in 'P'rocessed status then the batch logs an appropriate error.

Chunking Logic

• Dense rank the staged records over Subclass, item and location.

• Divide the rank value by the commit max counter.

• Rounding the divided value gives the Chunk ID to which the particular value belongs to.

• Item can be NULL on the staging table, when NULL consider item to be ’-999'.

• This will make sure the records with same subclass value and having item as NULL and NOT NULL are not grouped together in a chunk.

Since records with item have to be processed differently, (WAC recalculation and Variance postings) the batch makes sure that they fall in a different chunk to those records which do not have item value.

The Chunk data is inserted into STAGE_EXT_TRAN_DATA_CHUNK table.

Scheduling Constraints

Table 20-29 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	This program only needs to be scheduled if data from external systems should be included in the stock ledger. If this functionality is used, this should be the first stock ledger process.
Pre-Processing	NA
Post-Processing	trandataprocess.ksh
Threading Scheme	NA

Restart/Recovery

NA

Key Tables Affected

Table 20-30 Key Tables Affected

Table	Select	Insert	Update	Delete
STAGE_EXT_TRAN_DATA	Yes	Yes	No	Yes
STAGE_EXT_TRAN_DATA_CHUNK	No	Yes	No	Yes

I/O Specification - Input File Specification

This batch uses SQL Loader to populate the staging table. The input file should be in pipe delimited format. Sample record structure would look like:

<item>|<dept>l<class>|<subclass>|<location>|<loc_type>|<tran_date>|<tran_code>|<adj_code>|<units>|<total_cost>|<total_retail>|<ref_no_1>|<ref_no_2>|<GL_ref_no>|<Old_unit_retail>|<New_unit_retail>|<Sales_type>|<VAT_rate>|<av_cost>|<ref_pack_no>|<total_cost_excl_elc>|<WAC_reclculate_ind>|<status>|<create_timestamp>|

File Layout

The table below specifies the detail of each field in the record.

Table 20-31 File Layout

Field Name	Field Type	Default Value	Description
Item	VARCHAR2(25)	NA	Item is an optional field. Transactions can be uploaded at the Subclass level also.
Dept	NUMBER(4)	NA	Mandatory Field
Class	NUMBER(4)	NA	Mandatory Field
Subclass	NUMBER(4)	NA	Mandatory Field
Location	NUMBER(10)	NA	Mandatory Field
Loc_type	VARCHAR2(1)	NA	Valid values - 'S', 'W', 'E'
Tran_data	DATE	NA	Mandatory Field
Tran_code	NUMBER(2)	NA	Mandatory Field
Adj_code	VARCHAR2(1)	NA	Valid values - 'C', 'U', 'A'
Units	NUMBER(12, 4)	NA	Mandatory Field
Total_cost	NUMBER(20, 4)	NA	NA
Total_retail	NUMBER(20, 4)	NA	NA
Ref_no_1	NUMBER(10)	NA	NA
Ref_no_2	NUMBER(10)	NA	NA
Gl_ref_no	NUMBER(10)	NA	NA
Old_unit_retail	NUMBER(20, 4)	NA	NA
New_unit_retail	NUMBER(20, 4)	NA	NA
Pgm_name	VARCHAR(100)	NA	NA
Sales_type	VARCHAR2(1)	NA	Valid values - 'C', 'R', 'P'
Vat_rate	NUMBER(12, 4)	NA	NA
Av_cost	NUMBER(20, 4)	NA	NA
Ref_pack_no	VARCHAR2(25)	NA	NA
Total_cost_excl_elc	NUMBER(20, 4)	NA	NA
Wac_recalculate_ind	VARCHAR2(1)	NA	If Weighted Average Cost of the Item-Location should be recalculated after uploading this transaction then this value should be passed as 'Y'.
Status	VARCHAR2(1)	'N'	This value will be defaulted to 'N' by this program. It will be updated to 'P' once it has been processed else to 'E' in case of Error.
Create_timestamp	DATE	Sysdate	NA

Design Assumptions

NA

trandataprocess.ksh (External Transaction Data Process)

Module Name	trandataprocess.ksh
Description	External Transaction Data Process
Functional Area	Finance
Module Type	Business Processing
Module Technology	KSH
Catalog ID	RMS377
Runtime Parameters	NA

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 RMS transaction, and are based on a determination made outside of RMS. The records written through this mechanism function exactly the same as records written by normal RMS 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 RMS 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 RMS.

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.

Scheduling Constraints

Table 20-32 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	This program only needs to be scheduled if data from external systems should be included in the stock ledger.
Pre-Processing	trandataload.ksh
Post-Processing	salstage
Threading Scheme	Trandataload.ksh divides the data into Chunks based on commit max counter. Each Data chunk will be processed by a single thread.

Restart/Recovery

NA

Key Tables Affected

Table 20-33 Key Tables Affected

Table	Select	Insert	Update	Delete
STAGE_EXT_TRAN_DATA	Yes	No	Yes	No
STAGE_EXT_TRAN_DATA_CHUNK	Yes	No	Yes	Yes
GTG_STG_EXT_TRAN_DATA	Yes	Yes	Yes	Yes
SUBCLASS	Yes	No	No	No
WH	Yes	No	No	No
STORE	Yes	No	No	No
TRAN_DATA_CODES	Yes	No	No	No
TRAN_DATA	Yes	Yes	No	No
ITEM_LOC_SOH	Yes	No	Yes	No
SYSTEM_OPTIONS	Yes	No	No	No
PERIOD	Yes	No	No	No
GTT_STAGE_EXT_TRAN_DATA_CALC	Yes	Yes	No	Yes
ITEM_MASTER	Yes	No	No	No
ITEM_LOC	Yes	Yes	No	Yes
DEPS	Yes	No	No	No

Design Assumptions

NA