Mirror of Oracle documentation

Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.

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

N/A

Restart/Recovery

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 itemparent-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 Description Functional Area

hstwkupd.pc Weekly Stock on Hand and Retail Value Update for Item/Location 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

---

In this guide

• Guide: Batch Operations Guide
• Previous: 14 Sales Posting
• Next: 16 Stock Count

Related chapters

• 11 Replenishment — Batch Operations Guide · shares ALL_PART_TABLES, ALL_TAB_PARTITIONS, PARTITION_SQL, PURGE_INTERVAL_PARTITION
• 7 Contracts — Batch Operations Guide · shares PURGE_CONFIG_OPTIONS, RESTART_CONTROL
• 2 Data Entities — Merchandising Cloud Services Data Conversion Implementation Guide · shares ITEM_LOC_HIST, SYSTEM_VARIABLES