Go to primary content
Oracle® Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs
16.0.024
E89599-02
  Go To Table Of Contents
Contents

Previous
Previous
 
Next
Next
 

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

Stock Ledger Process
  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