| Oracle® Retail Merchandising Foundation Cloud Service Operations Guide, Volume 1 - Batch Overviews and Designs Release 19.2.000 F36502-03 |
|
 Previous |
 Next |
| Oracle® Retail Merchandising Foundation Cloud Service Operations Guide, Volume 1 - Batch Overviews and Designs Release 19.2.000 F36502-03 |
|
Previous |
Next |
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.
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 |
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.
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.
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.
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.
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.
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
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.
|
Note: This process focuses on batch runs of the future cost engine. |
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.
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)
Purge Aged Cost Events (cost_event_purge_job) - background process
| 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 |
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.
| 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 |
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". |
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 |
| 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 |
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.
| 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 |
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.
| 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 |
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.
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 |
| 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 |
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.
| 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 |
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.
| 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) |
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.
