Go to primary content
Oracle® Retail Merchandising Foundation Cloud Service Operations Guide, Volume 1 - Batch Overviews and Designs
Release 19.2.000
F36502-03
  Go To Table Of Contents
Contents

Previous
Previous
 
Next
Next
 

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

Future Cost Engine Sync

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

Future Cost Engine Async

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 Batch

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

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.

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

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This shell script will refresh the pricing cost materialized view snapshot to reflect the most recent pricing cost for an item/location in from the future cost table. It will in turn insert/update into the WAC variance calculation table to reflect the change in WAC as a result of the change in the average cost of an item/location. This information is used by the WAC Variance report displayed by default in the Finance Analyst dashboard.

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)

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This shell script will refresh the WAC variance calculation table to show the change in WAC for an item/location, based on the change in the item/location's average cost during the day. It is used by the WAC Variance report shown by default in the Finance Analyst dashboard.

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