15 Sales History

Merchandising maintains sales history at a variety of levels. Item level sales history drives Merchandising replenishment, ratio build, and is exported to planning applications. Sales history rolled up to levels of the merchandise hierarchy is used by Oracle Retail Allocation. Rolled up sales history is also useful for custom reporting.

Program Summary

The following batch designs are included in this chapter:

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

• Monthly Sales History Rollup By Diffs (hstbldmth_diff)

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

• Purge Aged Sales History (hstprg)

• Purge Aged Sales History by Diff (hstprg_diff)

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

• Weekly Sales History Rollup by Diff (hstbld_diff)

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

As an alternative to some of the scheduled batch processes, there is also an option to run some of the above programs as a background process, instead of during the batch cycle. This includes:

• Purge Aged Sales History (history_purge_job)

• Purge Aged Sales History by Diff (hist_diff_purge_job)

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

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
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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.

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.

Design Assumptions

N/A

Monthly Sales History Rollup By Diffs (hstbldmth_diff)

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
Wrapper Script	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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.

Restart/Recovery

N/A

Locking Strategy

The package HSTBLD_DIFF_PROCESS locks the following tables for update:

ITEM_DIFF_LOC_HIST_MTH

ITEM_PARENTLOC_HIST_MTH

Design Assumptions

N/A

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

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
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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).

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.

I/O Specification

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

Purge Aged Sales History (history_purge_job)

Module Name	history_purge_job
Description	Purge Aged Sales History
Functional Area	Sales Posting
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Design Overview

This background job is composed of two steps processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records from department, class, subclass sales history tables based on its purge criteria from system parameter settings. The Item History Months parameter will determine record which is older than the specific number of retention months of fashion style history. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging table. Using bulk processing, this program will delete the records from sales history tables by department, class and subclass tables. It will also invoke a call to a new program specific for handling historical tables that are considered partitioned tables. PARTITION_SQL.PURGE_INTERVAL_PARTITION is called passing each target table names "ITEM_LOC_HIST", "ITEM_LOC_HIST_MTH", and "DAILY_SALES_DISCOUNT". This called program will execute the proper deletion/purging of records from target table by exercising table partitioning handling such as Dropping Interval Partition (same as truncate or delete from table). There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

The decision to insert or not to insert the records into the history tables is based on the Archive Indicator and Archive Job Indicator from the Background Process Configuration table.

1. If both the Archive Indicator and Archive Job Indicator values are Y, then the data from the base tables are inserted into the history tables.

2. If both indicators are set to N, then the records are deleted from the base tables without inserting into the history tables.

Note:

For more information on how to configure this process for archiving, see the Merchandising Implementation Guide section entitled “Background Process Configuration”.

Restart/Recovery

N/A

Key Tables Affected

Table 15-1 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
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_HIST_PURGE_STG	Yes	Yes	No	Yes
ALL_PART_TABLES	Yes	No	No	No
ALL_TAB_PARTITIONS	Yes	No	No	No
ITEM_LOC_HIST	No	No	No	Yes
ITEM_LOC_HIST_MTH	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
DAILY_SALES_DISCOUNT_PRG_HIST	No	Yes	No	No
ITEM_LOC_HIST_PRG_HIST	No	Yes	No	No

Purge Aged Sales History (hstprg)

Module Name	hstprg.pc
Description	Purge Aged Sales History
Functional Area	Sales Posting
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS244
Wrapper Script	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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.

Restart/Recovery

N/A

Design Assumptions

N/A

Purge Aged Sales History by Diff (hist_diff_purge_job)

Module Name	hist_diff_purge_job
Description	Purge Aged Sales History by Diff
Functional Area	Sales History
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two steps processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records from item-parent-location history by diff and item-location history by diff tables based on its purge criteria from system parameter settings. The Item History Months parameter will determine old sales history differentiator data on a specified system set date. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging table. Using bulk processing, this program will delete the records from sales history differentiator and item-parent-location history tables. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

Restart/Recovery

N/A

Key Tables Affected

Table 15-2 Key Tables Affected

Table	Select	Insert	Update	Delete
PURGE_CONFIG_OPTIONS	Yes	No	No	No
PERIOD	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_HIST_DIFF_PURGE_STG	Yes	Yes	No	Yes
ITEM_DIFF_LOC_HIST	No	No	No	Yes
ITEM_PARENT_LOC_HIST	No	No	No	Yes

Purge Aged Sales History by Diff (hstprg_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
Wrapper Script	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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.

Restart/Recovery

N/A

Design Assumptions

N/A

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

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
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The sales history rollup routine will extract sales history information for each item from the item and item location history tables. The history information will be rolled up to the subclass, class, and dept level to be written to history tables.

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.

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.

Design Assumptions

N/A

Weekly Sales History Rollup by Diff (hstbld_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
Wrapper Script	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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.

Restart/Recovery

N/A

Design Assumptions

N/A

Key Tables Affected

Table 15-3 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

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

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
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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.

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.

I/O Specification

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

Design Assumptions

N/A