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.
9
Future Cost
The Future Cost Engine calculates the expected cost of an item/supplier/origin country/location at a given point into the future. These values are used to help in many scenarios (for example, when trying to determine what a margin will be at a point in the future, or when doing investment buying).
The future cost engine can execute as either a synchronous, asynchronous or batch process. The focus of this chapter is the batch processes. To support the discussion of the batch processes, there is general discussion of the engine that is also applicable to the synchronous and asynchronous execution of the engine.
There are also two other programs that are referenced in this chapter related to cost - Pricing Cost Refresh and WAC Refresh. These are both used to refresh materialized views that are used by the WAC Variance report that is displayed by default on the Finance Analyst dashboard.
Future Cost Events
There are three basic events that drive recalculation of FUTURE_COST. They are supplier cost changes, deals, and estimated landed cost components. When these events are added or removed from Merchandising, they impact the calculated values on future cost. These transactions are known as primary events.
There are other events that determine if primary events still apply to a given item/supplier/ origin country/location combination. They are reclassifications, merchandise hierarchy changes, organization hierarchy changes, cost zone location moves, item/cost zone changes, and supplier hierarchy changes. These are secondary events.
There are also two special events that cause new time lines to be created in FUTURE_COST. They are new item loc (when item/locations are ranged) and new item/supplier/country/location relationships (add and remove). These are initialization events.
The ITEM_LOC.PRIMARY_COST_PACK column plays a special roll in costing. When a primary costing pack is defined for an item, that item’s costing values are based on the primary_costing_pack not the item itself. When a primary costing pack is added, changed, or removed, this is a primary pack event.
Table 9-1 Cost Events and Cost Event Types
| Cost Event | Cost Event Type |
|---|---|
| Supplier Cost Change | Primary |
| Deal | Primary |
| ELC Component | Primary |
| Reclassification | Secondary |
| Merchandise hierarchy | Secondary |
| Organization hierarchy | Secondary |
| Cost zone location moves | Secondary |
Table 9-1 (Cont.) Cost Events and Cost Event Types
| Cost Event | Cost Event Type |
|---|---|
| Item/cost zone changes | Secondary |
| Supplier hierarchy | Secondary |
| New Item Location | Initialization |
| Item/supplier/country/location relationships | Initialization |
| Primary cost pack | Primary Pack |
| WF Cost Template | N/A |
| WF Cost Template Relationship | N/A |
| Deal Pass through | N/A |
Future Cost Engine Run Type Configuration
The Future Cost Engine can be configured by cost event type in one of three ways:
-
Synchronous
-
Asynchronous
-
Batch
The method to be used by each cost event type is controlled by the configuration defined in the COST_EVENT_RUN_TYPE_CONFIG table.
Synchronous
When running in synchronous mode, the Future Cost Engine is run in the same transaction as the client that calls it. For example if the cost change event is configured to run in synchronous mode, the work done in the Future Cost Engine for the approval of a cost change runs in the same transaction as the flipping of the status of the cost change to ‘A’ status. That means the user in the screen will have a busy cursor until the Future Cost Engine completes.
Cost event types with an EVENT_RUN_TYPE set to ‘SYNC’ on COST_EVENT_RUN_TYPE_CONFIG will run in synchronous mode.
Future Cost Engine -SYNC mode
Future Cost Engine -ASYNC mode
Future Cost Engine - BATCH mode
Note
This process flow focuses on batch recalculations, but synchronous or asynchronous processes could easily be substituted in this step.
-
fcthreadexec.pc prepares threads for processing
-
fcexec.pc recalculates future cost and writes it the future cost table (FUTURE_COST)
-
Merchandising processes use future cost information to determine investment buy, margin, and so on.
-
fc_pricechg.pc performs special calculation of pricing cost for franchise locations
-
costeventprg.pc purges aged cost events from the working cost event tables.
Program Summary
The following batch programs are included in this chapter:
-
Execute Batch Calculation/Recalculation of Future Cost Values (fcexec)
-
Future Cost Table Maintenance (future_cost_purge_job) - background process
-
Prepare Threads for Batch Calculation/Recalculation of Future Cost Values (fcthreadexec)
-
Pricing Cost Refresh (rms_oi_pricecostrefresh.ksh)
-
Purge Aged Cost Events (cost_event_purge_job) - background process
-
Use Pending Price Changes to Drive Recalculation of Pricing Cost for some Franchise Item/Locations (fc_pricechg)
-
WAC Refresh (rms_oi_wacvarrefresh.ksh)
Execute Batch Calculation/Recalculation of Future Cost Values (fcexec)
Module Name fcexec.pc Description Execute Batch Calculation/Recalculation of Future Cost Values Functional Area Costing Module Type Business Processing Module Technology ProC Catalog ID RMS223 Wrapper Script rmswrap_multi.ksh
Schedule
Oracle Retail Merchandising Batch Schedule
Design Overview
The fcexec.pc batch program executes the future cost engine in batch mode. Cost events set up to run in batch mode are threaded in the fcthreadexec.pc batch process and passed to the
future cost engine for processing by this program. This program should be always run after the fcthreadexec.pc batch.
This batch program only serves as a wrapper to call the cost engine, the Key Tables Affected section does not list the tables affected by the cost engine. The future cost engine is threaded by item/supplier/country/location.
Restart/Recovery
The logical unit of work for this batch program is the cost_event_process_id on the COST_EVENT table.
Design Assumptions
N/A
Future Cost Table Maintenance (future_cost_purge_job)
Module Name future_cost_purge_job Description Future Cost Table Maintenance Functional Area Costing 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 future cost table for purging. 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 future-cost data from future-cost table. 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.
Note
This process also supports archiving, if desired. For more information on how to configure this process for archiving, see the Merchandising Implementation Guide section titled “Background Configuration Process”.
Restart/Recovery
N/A
Locking Strategy
N/A
Security Considerations
N/A
Performance Considerations
N/A
Key Tables Affected
Table 9-2 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
| RMS_BATCH_STATUS | Yes | No | No | No |
| B8D_PROCESS_CONFIG | Yes | No | No | No |
| SYSTEM_OPTIONS | Yes | No | No | No |
| PERIOD | Yes | No | No | No |
| FUTURE_COST | Yes | No | No | Yes |
| B8D_FUTURE_COST_PURGE_STG | Yes | Yes | No | Yes |
| JOB_AUDIT_LOGS | No | Yes | No | No |
| FUTURE_COST_PRG_HIST | No | Yes | No | No |
I/O Specification
N/A
Prepare Threads for Batch Calculation/Recalculation of Future Cost Values (fcthreadexec)
Module Name fcthreadexec.pc Description Prepare Threads for Batch Calculation/Recalculation of Future Cost Values Functional Area Costing Module Type Admin Module Technology ProC Catalog ID RMS230 Wrapper Script rmswrap_multi.ksh
Schedule
Oracle Retail Merchandising Batch Schedule
Design Overview
The fcthreadexec.pc batch program is responsible for threading the cost events based on the max_tran_size that is provided in the cost_event_run_type_config table.
This program must always be run before the fcexec batch.
Restart/Recovery
The logical unit of work for this batch program is the cost_event_process_id on the COST_EVENT table.
Design Assumptions
N/A
Pricing Cost Refresh (rms_oi_pricecostrefresh.ksh)
Module Name rms_oi_pricecostrefresh.ksh Description Refreshes the MV_PRICING_COST to reflect the most recent pricing cost of an item/location in FUTURE_COST . Functional Area Financial Dashboard Module Type Admin Module Technology Ksh Catalog ID RMS477 Wrapper Script rmswrap_shell.ksh
Design Overview
This shell script will refresh the MV_PRICING_COST snapshot to reflect the most recent pricing cost for an item/location in FUTURE_COST . It will in turn insert/update into the RMS_OI_WAC_VARIANCE_CALC to reflect the change in WAC as a result of the change in the ITEM_LOC.AV_COST of an item/location. This information is used by the WAC Variance report displayed by default in the Finance Analyst dashboard.
Scheduling Constraints
| Schedule Information | Description |
|---|---|
| Processing Cycle | Every 6 AM outside the batch window. |
| Frequency | Daily |
| Scheduling Considerations | Must run beforerms_oi_wacvarrefresh.ksh |
| Pre-Processing | N/A |
| Schedule Information | Description |
|---|---|
| Post-Processing | N/A |
| Threading Scheme | N/A |
Restart/Recovery
N/A
Key Tables Affected
Table 9-3 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
| RMS_OI_WAC_VARIANCE_CALC | Yes | No | Yes | No |
| MV_PRICING_COST | Yes | No | No | No |
| ITEM_LOC_SOH | Yes | No | No | No |
| STORE | Yes | No | No | No |
| WH | Yes | No | No | No |
| CURRENCY_RATES | Yes | No | No | No |
Design Assumptions
N/A
Purge Aged Cost Events (cost_event_purge_job)
Module Name cost_event_purge_job Description Purge Aged Cost Events Functional Area Future Cost 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-step processing. It will have a threading assignment and a business logic processing.
Thread assignment program will filter eligible records from cost event table based on its purge criteria from system parameter settings. The Cost Events History Days parameter will determine cost events that were old/aged from the creation date. These cost event records should exist from Cost Event Configuration table. 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 specific cost event related tables as per Run Event Type. 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 9-4 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
| PURGE_CONFIG_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_COST_EVENT_PURGE_S TG | Yes | Yes | No | Yes |
| COST_EVENT | No | No | No | Yes |
| COST_EVENT_RESULT | No | No | No | Yes |
| COST_EVENT_THREAD | No | No | No | Yes |
| COST_EVENT_SUPP_COUNTR Y | No | No | No | Yes |
| COST_EVENT_NIL | No | No | No | Yes |
| COST_EVENT_PRIM_PACK | No | No | No | Yes |
| COST_EVENT_COST_CHG | No | No | No | Yes |
| COST_EVENT_RECLASS | No | No | No | Yes |
| COST_EVENT_DEAL | No | No | No | Yes |
| COST_EVENT_MERCH_HIER | No | No | No | Yes |
| COST_EVENT_ORG_HIER | No | No | No | Yes |
| COST_EVENT_COST_ZONE | No | No | No | Yes |
| COST_EVENT_ELC | No | No | No | Yes |
| COST_EVENT_SUPP_HIER | No | No | No | Yes |
| COST_EVENT_ITEM_COST_ZO NE | No | No | No | Yes |
| COST_EVENT_RUN_TYPE_CO NFIG | Yes | No | No | No |
| COST_EVENT_DEAL_PASSTHR U | No | No | No | Yes |
| COST_EVENT_COST RELATIONSHIP | No | No | No | Yes |
| COST_EVENT_COST_TMPL | No | No | No | Yes |
Design Assumptions
N/A
Purge Successfully Processed Cost Events (costeventprg)
Module Name b8dcostevntprgb.pls/ b8dcostevntprgs.pls Description Purge Successfully Processed Cost Events Functional Area Future Cost Module Type Admin Module Technology PL/SQL Catalog ID RMS203 Wrapper Script rmswrap_plsql.ksh
Schedule
Oracle Retail Merchandising Batch Schedule
Design Overview
This batch program purges tables used by the Future Cost calculation engine. Records from the COST_EVENT and its related tables are purged from the system based on the Cost Event History Days (cost_event_hist_days) system parameter.
Restart/Recovery
The logical unit of work is the event type on the COST_EVENT_RUN_TYPE_CONFIG table. Records are deleted serially per event type. Restart recovery is based on deleted records. Restarting on a failed run will resume from records not yet deleted on the prior failed run.
Design Assumptions
N/A
Use Pending Price Changes to Drive Recalculation of Pricing Cost for some Franchise Item/Locations (fc_pricechg)
Module Name fc_pricechg.ksh Description Use Pending Price Changes to Drive Recalculation of Pricing Cost for some Franchise Item/Locations Functional Area Future Cost Module Type Business Processing Module Technology ksh Catalog ID RMS497 Wrapper Script rmswrap_shell.ksh
Schedule
Oracle Retail Merchandising Batch Schedule
Design Overview
This script checks for any item/locations that have scheduled price changes for the next day. If there are corresponding item/location rows in the future cost table with the percent-off-retail type template associated then the pricing cost of those future cost records will be recalculated by this program.
Restart/Recovery
N/A
Design Assumptions
N/A
WAC Refresh (rms_oi_wacvarrefresh.ksh)
Module Name rms_oi_wacvarrefresh.ksh Description Refreshes the RMS_OI_WAC_VARIANCE_CALC with WAC update on an item/ location Functional Area Financial Dashboard Module Type Admin Module Technology Ksh Catalog ID RMS478 Runtime Parameters $UP (database connect string)
Design Overview
This shell script will refresh the RMS_OI_WAC_VARIANCE_CALC to show the change in WAC for an item/location, based on the change in the ITEM_LOC.AV_COST during the day. It is used by the WAC Variance report shown by default in the Finance Analyst dashboard.
Scheduling Constraints
| Schedule Information | Description |
|---|---|
| Processing Cycle | Ad Hoc. During the Day outside the batch window. |
| Frequency | Every 2 hours |
| Scheduling Considerations | Must run afterrms_oi_pricecostrefresh.ksh |
| Pre-Processing | N/A |
| Post-Processing | N/A |
| Threading Scheme | N/A |
N/A
Restart/Recovery
Key Tables Affected
Table 9-5 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
| RMS_OI_WAC_VARIANCE_CALC | Yes | No | Yes | No |
| MV_PRICING_COST | Yes | No | No | No |
| ITEM_LOC_SOH | Yes | No | No | No |
| STORE | Yes | No | No | No |
| WH | Yes | No | No | No |
| CURRENCY_RATES | Yes | No | No | No |
Design Assumptions
N/A
In this guide
- Guide: Batch Operations Guide
- Previous: 8 Cost Changes