Oracle® Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs 16.0.024 E89599-02 |
|
Previous |
Next |
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.
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)
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 |
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%.
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.
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 |
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.
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 |
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.
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 |
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.
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 |
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.
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 |
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.
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 |
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 |
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.
The package HSTBLD_DIFF_PROCESS locks the following tables for update:
ITEM_DIFF_LOC_HIST_MTH
ITEM_PARENTLOC_HIST_MTH
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 |
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).
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) |
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.
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 |
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.
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 |
The logical unit of work for HSTWKUPD is item/location. The program is threaded by location using the v_restart_store_wh view.
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 |
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.
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 |
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 |
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.