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 Stock on Hand, Retail and Average Cost Values Update (hstmthupd)
-
Weekly Sales History Rollup by Department, Class, and Subclass (hstbld)
-
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:
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 |
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.
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 |
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.
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 |
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.
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.
-
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. -
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”.
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 |
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.
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 |
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.
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 |
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 |
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.
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 |
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.
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 |
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.