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

Figure 9-1 Future Cost Engine - SYNC Mode

Asynchronous

When running in asynchronous mode, the Future Cost Engine is run in a separate transaction than the client that calls it. For example if the cost change event is configured to run in asynchronous mode, the work done in the Future Cost Engine for the approval of a cost change runs in a different transaction as the flipping of the status of the cost change to ‘A' status. This means that control returns to the user in the screen while the Future Cost Engine runs in the background.

Cost event types with an EVENT_RUN_TYPE set to ‘ASYNC' on COST_EVENT_RUN_TYPE_CONFIG runs in asynchronous mode.

Figure 9-2 Future Cost Engine - ASYNC Mode

Batch

When running in batch mode, the Future Cost Engine is run during the nightly batch run. For example if the cost change event is configured to run in batch mode, the work done in the Future Cost Engine for the approval of a cost change runs during the next batch run after the approval of the cost change.

Cost event types with an EVENT_RUN_TYPE set to ‘BATCH' on COST_EVENT_RUN_TYPE_CONFIG runs in batch mode.

The fcexec.pc batch program and its associated prepost pre job contain logic to run the Future Cost Engine in batch mode.

Figure 9-3 Future Cost Engine - Batch Mode

Future Cost Engine Concurrency Control

Concurrency control is handled in the Future Cost Engine by locking the FUTURE_COST table. The sole job of the Future Cost Engine is maintaining the FUTURE_COST table and its helper DEAL_ITEM_LOC_EXPLODE. The first step in processing is to lock the item/supplier/origin country/location combinations that the cost event covers (after the identification of item/supplier/origin country/location combinations and chunking has been done). If a lock cannot be obtained, another cost event is already processing some of the data that is required. When this occurs the Future Cost Engine stops processing and records the results accordingly and the cost event can be retried at a later time.

Future Cost Engine Error Handling

The COST_EVENT_RESULT table is used to track all runs of the Future Cost Engine whether or not they succeeded. The table records a cost event ID and thread ID, the result code, and any error message that may exist. A special screen is used to search/access the results

Future Cost Engine Threading/Chunking

The Future Cost Engine deals with large amounts of data. Its inputs can vary greatly in size. Its inputs can be one large driver or a group of smaller drivers.

In order to deal with this volume and variation in input a configurable threading/chunking mechanism is built into the Future Cost Engine.

When the transaction control is set to BATCH, the chunks are run in a threaded manner using the Pro*C batch program to coordinate execution.

Future Cost Process

Note:

This process focuses on batch runs of the future cost engine.

Figure 9-4 Future Cost Process

• Administrators configure the system (COST_EVENT_RUN_TYPE) to define which cost events types will be processed synchronously, asynchronously or in batch. Configuration by cost event type also determines some threading and chunking parameters.

• Merchandising transactions that should drive future cost recalculation write Cost Events (COST_EVENT and cost event type specific tables).

• Future Cost Engine recalculates future cost

  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

• Purge Aged Cost Events (costeventprg)

• 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 before rms_oi_wacvarrefresh.ksh
Pre-Processing	N/A
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_STG	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_COUNTRY	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_ZONE	No	No	No	Yes
COST_EVENT_RUN_TYPE_CONFIG	Yes	No	No	No
COST_EVENT_DEAL_PASSTHRU	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 Aged Cost Events (costeventprg)

Module Name	costeventprg.pc
Description	Purge Aged Cost Events
Functional Area	Future Cost
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS203
Wrapper Script	rmswrap.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 after rms_oi_pricecostrefresh.ksh
Pre-Processing	N/A
Post-Processing	N/A
Threading Scheme	N/A

Restart/Recovery

N/A

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