17 Sales History

RMS maintains sales history at a variety of levels. Item level sales history drives RMS replenishment, ratio build and is exported to planning applications (see chapter Integration – Planning in this document). RMS also maintains a smoothed average history for RPM. Sales history rolled up to levels of the merchandise hierarchy is used by Oracle Retail Allocation. Many clients also find sales history data useful for custom reporting.

Batch Design Summary

The following batch designs are included in this chapter:

• rpmmovavg.pc (Maintain Smoothed, Moving Average Sales History for RPM)

• hstbld.pc (Weekly Sales History Rollup by Department, Class, and Subclass)

• hstbld_diff.pc (Weekly Sales History Rollup by Diff)

• hstbldmth.pc (Monthly Sales History Rollup by Department, Class, and Subclass)

• hstbldmth_diff.pc (Monthly Sales History Rollup by Diffs)

• hstmthupd.pc (Monthly Stock on Hand, Retail and Average Cost Values Update)

• hstwkupd.pc (Weekly Stock on Hand and Retail Value Update for Item/Location)

• hstprg.pc (Purge Aged Sales History)

• hstprg_diff.pc (Purge Aged Sales History by Diff)

rpmmovavg (Maintain Smoothed, Moving Average Sales History for RPM)

Module Name	rpmmovavg.pc
Description	Maintain Smoothed, Moving Average Sales History for RPM
Functional Area	Sales History
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS319
Runtime Parameters	NA

Design Overview

This batch module will take the number of units sold from IF_TRAN_DATA table for all items designated for a particular store within a specified store/day, and maintain a smoothed average in the IF_RPM_SMOOTHED_AVG table.

Only the sales, which have a sales type of regular, are included. If the item is on promotion or clearance, then no updating is required. The units under normal sales will be considered as unadjusted units and will be taken for smoothed average. The threshold percent will be maintained at the department level.

This percent will be compared to the existing smoothed average value and used to limit the upper and lower boundaries for regular sales received. If the unadjusted units amount is outside of the boundaries, then the appropriate boundary amount will be substituted and become the adjusted units amount. If no threshold percent is defined for the department, it will be defaulted to 50%.

Scheduling Constraints

Table 17-1 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	The program picks the daily sales data from IF_TRAN_DATA table. It should run after salstage.pc
Pre-Processing	Salstage.pc
Post-Processing	NA
Threading Scheme	Threaded By STORE number

Restart/Recovery

The logical unit of work for this program is set at store/item level.

Restartability is implied based on item and store combination. Records will be committed to the database when commit_max_ctr defined in the RESTART_CONTROL table is reached.

Key Tables Affected

Table 17-2 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_MASTER	Yes	No	No	No
LOCATION_CLOSED	Yes	No	No	No
IF_TRAN_DATA	Yes	No	No	No
DEPS	Yes	No	No	No
IF_RPM_SMOOTHED_AVG	Yes	Yes	Yes	No

Input/Output Specification

NA

hstbld (Weekly Sales History Rollup by Department, Class, and Subclass)

Module Name	hstbld.pc
Description	Weekly Sales History Rollup by Department, Class, and Subclass
Functional Area	Sales History
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS239
Runtime Parameters	NA

Design Overview

The sales history rollup routine will extract sales history information for each item from the ITEM_MASTER, and ITEM_LOC_HIST (item location history) tables. The history information will be rolled up to the subclass, class, and dept level to be written to: dept_sales_hist (department/location/week/sales type), class_sales_hist (class/location/week/sales type), and subclass_sales_hist (subclass/location/week/sales type).

The rebuild program can be run in one of two ways:

First, if the program is run with a run-time parameter of ’rebuild', the program will read data (dept, class, and subclass) off the manually input HIST_REBUILD_MASK table, which will determine what to rebuild.

Secondly, if the program is run with a run-time parameter of ’weekly', the program will build sales information for all dept/class/subclass combinations only for the current end of week date.

Scheduling Constraints - Rebuild

Table 17-3 Scheduling Constraints - Rebuild

Schedule Information	Description
Frequency	As needed
Scheduling Considerations	Must run after complete weekly sales have been updated by the Sales Upload Program. Also should be re-run on demand when a sales rollup request has been given for a given dept, class or subclass
Pre-Processing	Prepost hstbld pre, if rebuild all
Post-Processing	Prepost hstbld post, to truncate the HIST_REBUILD_MASK table

Scheduling Constraints - Normal Weekly Processing

Table 17-4 Scheduling Constraints - Normal Weekly Processing

Schedule Information	Description
Frequency	Weekly
Scheduling Considerations	Must run after complete weekly sales have been updated by the Sales Upload Program
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	Threaded by location

Restart/Recovery

The logical unit of work for this program is set at the store/dept/class level. Threading is done by store using the v_restart_store view.

The commit_max_ctr field on the RESTART_CONTROL table will determine the number of transactions that equal a logical unit of work.

Key Tables Affected

Table 17-5 Key Tables Affected

Table	Select	Insert	Update	Delete
DEPT_SALES_HIST	No	Yes	Yes	No
CLASS_SALES_HIST	No	Yes	Yes	No
SUBCLASS_SALES_HIST	Yes	Yes	Yes	No
ITEM_MASTER	Yes	No	No	No
ITEM_LOC_HIST	Yes	No	No	No
PERIOD	Yes	No	No	No
SYSTEM_VARIABLES	Yes	No	No	No
HIST_REBUILD_MASK	Yes	No	No	No

hstbld_diff (Weekly Sales History Rollup by Diff)

Module Name	hstbld_diff.pc
Description	Weekly Sales History Rollup by Diff
Functional Area	Sales History
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS240

Design Overview

The sales history rollup routine will extract sales history information for each item_parent from the ITEM_LOC_HIST table. The history information will be rolled up to the item differentiator level to be written to: item_diff_loc_hist and item_parent_loc_hist.

For each item, data to be retrieved includes sales qty and stock. This data must be collected from several tables including ITEM_LOC_HIST, ITEM_LOC, and ITEM_MASTER.

Scheduling Constraints - Normal Weekly Processing

Table 17-6 Scheduling Constraints - Normal Weekly Processing

Schedule Information	Description
Frequency	Weekly
Scheduling Considerations	Must run after complete weekly sales have been updated by salesprocess.ksh
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Scheduling Constraints - Upon Request

Table 17-7 Scheduling Constraints - Upon Request

Schedule Information	Description
Frequency	As Needed
Scheduling Considerations	Should be re-run on demand when a sales rollup request has been given for a given style/color
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

NA

Key Tables Affected

Table 17-8 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_PARENT_LOC_HIST	No	Yes	Yes	No
ITEM_DIFF_LOC_HIST	No	Yes	Yes	No
ITEM_LOC	Yes	No	No	No
ITEM_LOC_HIST	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
SYSTEM_VARIABLES	Yes	No	No	No
PERIOD	Yes	No	No	No

hstbldmth (Monthly Sales History Rollup By Department, Class And Subclass)

Module Name	hstbldmth.pc
Description	Monthly Sales History Rollup by Department, Class, and Subclass
Functional Area	Sales History
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS241

Design Overview

The monthly sales history roll up routine will extract sales history information for each item from the ITEM_MASTER and ITEM_LOC_HIST_MTH (item location history by month) tables. The history information will be rolled up to the subclass, class and dept level to be written to: subclass_sales_hist_mth (subclass/location/month/sales type), class_sales_hist_mth (class/location/month/sales type) and dept_sales_hist_mth (department/location/month/sales type).

This program may be run in parallel with hstbld since they both read from HIST_REBUILD_MASK. The table HIST_REBUILD_MASK table must not be truncated before both programs finish running.

Scheduling Constraints

Table 17-9 Scheduling Constraints

Schedule Information	Description
Frequency	Monthly
Scheduling Considerations	Must run after complete monthly sales have been updated by Sales Upload program Also, should be re-run on demand when a sales rollup request has been given for a given dept, class and subclass This program may be run in parallel with hstbld since they both read from HIST_REBUILD_MASK. The table HIST_REBUILD_MASK table must not be truncated by associated prepost post jobs before both programs finish running
Pre-Processing	NA
Post-Processing	prepost hstbldmth post
Threading Scheme	Threaded by department

Restart/Recovery

The logical unit of work for the hstbldmth module is department, location, sales type and end of month date with a recommended commit counter setting of 1,000. Processed records are committed each time the record counter equals the maximum recommended commit number.

Key Tables Affected

Table 17-10 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_MASTER	Yes	No	No	No
ITEM_LOC_HIST_MTH	Yes	No	No	No
SUBCLASS_SALES_HIST_MTH	Yes	Yes	No	Yes
CLASS_SALES_HIST_MTH	Yes	Yes	No	Yes
DEPT_SALES_HIST_MTH	No	Yes	No	Yes
HIST_REBUILD_MASK	Yes	No	No	No
SYSTEM_VARIABLES	Yes	No	No	No
PERIOD	Yes	No	No	No

hstbldmth_diff (Monthly Sales History Rollup By Diffs)

Module Name	hstbldmth_diff.pc
Description	Monthly Sales History Rollup by Diffs
Functional Area	Sales History
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS242

Design Overview

The sales history rollup routine will extract sales history information for each ITEM_PARENT from the ITEM_LOC_HIST_MTH table and rolls the data to month level. The history information will be rolled up to the item differentiator level to be written to: item_diff_loc_hist_mth and item_parentloc_hist_mth. For each item, data to be retrieved includes sales quantity and stock. This data must be collected from several tables including ITEM_LOC_HIST_MTH, ITEM_LOC, and ITEM_MASTER.

Scheduling Constraints

Table 17-11 Scheduling Constraints

Schedule Information	Description
Frequency	Monthly
Scheduling Considerations	Must be run only at EOM date
Pre-Processing	NA
Post-Processing	hstmthupd.pc
Threading Scheme	NA

Restart/Recovery

NA

Locking Strategy

The package HSTBLD_DIFF_PROCESS locks the following tables for update:

ITEM_DIFF_LOC_HIST_MTH

ITEM_PARENTLOC_HIST_MTH

Key Tables Affected

Table 17-12 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_MASTER	Yes	No	No	No
ITEM_LOC	Yes	No	No	No
ITEM_LOC_HIST_MTH	Yes	No	No	No
ITEM_DIFF_LOC_HIST_MTH	No	Yes	Yes	No
ITEM_PARENTLOC_HIST_MTH	No	Yes	Yes	No
SYSTEM_VARIABLES	Yes	No	No	No
PERIOD	Yes	No	No	No

Integration Contract

NA

hstmthupd (Monthly Stock on Hand, Retail and Average Cost Values Update)

Module Name	hstmthupd.pc
Description	Monthly Stock on Hand, Retail and Average Cost Values Update
Functional Area	Sales History
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS158

Design Overview

This batch program runs monthly to update the stock on hand, retail values and average cost for each item/location on the ITEM_LOC_HIST_MTH (item location history by month) table. If the item/location does not exist on the ITEM_LOC_HIST_MTH table, then the new record is written to a comma delimited file which is later uploaded to ITEM_LOC_HIST_MTH table using SQL*Loader (hstmthupd.ctl).

Scheduling Constraints

Table 17-13 Scheduling Constraints

Schedule Information	Description
Frequency	Monthly
Scheduling Considerations	The program should be run on the last day of the month refeodinventory.ksh must run successfully prior to execution to ensure that ITEM_LOC_SOH_EOD is up-to-date
Pre-Processing	refeodinventory.ksh
Post-Processing	Run SQL*Loader using the control file hstmthupd.ctl to load data from the output file written by hstmthupd.pc for non-existent records on ITEM_LOC_HIST_MTH
Threading Scheme	Threaded by location (store)

Restart/Recovery

The logical unit of work for this program is the item/location record. Threading is done by store using the v_restart_store_wh view. The commit_max_ctr field on the RESTART_CONTROL table will determine the number of transactions that equal a logical unit of work. Table-based restart/recovery is used.

Key Tables Affected

Table 17-14 Key Tables Affected

Table	Select	Insert	Update	Delete
SYSTEM_VARIABLES	Yes	No	No	No
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
ITEM_LOC	Yes	No	No	No
ITEM_LOC_SOH_EOD	Yes	No	No	No
ITEM_LOC_HIST_MTH	Yes	No	Yes	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000175 hstmthupd.ctl

hstwkupd (Weekly Stock on Hand and Retail Value Update for Item/Location)

Module Name	hstwkupd.pc
Description	Weekly Stock on Hand and Retail Value Update for Item/Location
Functional Area	Sales History
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RMS159
Runtime Parameters	NA

Design Overview

This program runs weekly to update the current stock on hand, retail values and average cost for each item/location on ITEM_LOC_HIST is using SQL*Loader (hstwkupd.ctl). The program must be run on the last day of the week as scheduled.

Scheduling Constraints

Table 17-15 Scheduling Constraints

Schedule Information	Description
Frequency	Weekly
Scheduling Considerations	refeodinventory.ksh must run successfully prior to execution to ensure that ITEM_LOC_SOH_EOD is up-to-date
Pre-Processing	NA
Post-Processing	Run SQL*Loader using the control file hstwkupd.ctl to load data from the output file written by hstwkupd.pc for non-existent records on ITEM_LOC_HIST
Threading Scheme	Thread by location

Restart/Recovery

The logical unit of work for HSTWKUPD is item/location. The program is threaded by location using the v_restart_store_wh view.

Key Tables Affected

Table 17-16 Key Tables Affected

Table	Select	Insert	Update	Delete
ITEM_LOC	Yes	No	No	No
ITEM_LOC_SOH_EOD	Yes	No	No	No
ITEM_LOC_HIST	Yes	No	Yes	No
SYSTEM_VARIABLES	Yes	No	No	No
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	Determined by runtime parameter
Integration Contract	IntCon000176 hstwkupd.ctl

hstprg (Purge Aged Sales History)

Module Name	hstprg.pc
Description	Purge Aged Sales History
Functional Area	Sales Posting
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS244
Runtime Parameters	NA

Design Overview

Deletes records from ITEM_LOC_HIST, SUBCLASS_SALES_HIST, CLASS_SALES_HIST, DEPT_SALES_HIST and DAILY_SALES_DISCOUNT tables, where data is older than the specified number of months. Number of months for retention of fashion style history is specified by system_options.ITEM_HISTORY_MONTHS.

Scheduling Constraints

Table 17-17 Scheduling Constraints

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

Restart/Recovery

NA

Key Tables Affected

Table 17-18 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
PURGE_CONFIG_OPTIONS	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
ITEM_LOC_HIST	No	No	No	Yes
SUBCLASS_SALES_HIST	No	No	No	Yes
CLASS_SALES_HIST	No	No	No	Yes
DEPT_SALES_HIST	No	No	No	Yes
DAILY_SALES_DISCOUNT	No	No	No	Yes

Integration Contract

Integration Type	NA
File Name	NA
Integration Contract	NA

hstprg_diff (Purge Aged Sales History by Diff)

Module Name	hstprg_diff.pc
Description	Purge Aged Sales History by Diff
Functional Area	Sales History
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS245
Runtime Parameters	NA

Design Overview

The tables, ITEM_DIFF_LOC_HIST and ITEM_PARENT_LOC_HIST are purged of sales history differentiator data, which is older than a specified system set date. This date is stored in the purge_config_options.ITEM_HISTORY_MONTHS column.

Scheduling Constraints

Table 17-19 Scheduling Constraints

Schedule Information	Description
Frequency	Monthly
Scheduling Considerations	Should be run after hstbld_diff.pc
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

NA

Key Tables Affected

Table 17-20 Key Tables Affected

Table	Select	Insert	Update	Delete
PURGE_CONFIG_OPTIONS	Yes	No	No	No
PERIOD	Yes	No	No	No
ITEM_DIFF_LOC_HIST	No	No	No	Yes
ITEM_PARENT_LOC_HIST	No	No	No	Yes