6 Deals
Deals are complex business processes that can either affect the cost a retailer pays for goods purchased from a supplier (off invoice deals) or generate income from suppliers/partners (billback/rebate deals). These basic types of deals require different processing. This chapter contains information about the batch processes that support all types of Deals.
For additional information about Deals, including detailed flow diagrams, see the Deals white paper found in the Merchandising Documentation Library (Doc ID: 1585843.1).
Program Summary
This chapter contains an overview of Deals related batch processes:
-
Close Expired Deals (deal_close_job) - background job
-
Deal Calculation Queue Insert Multithreading (batch_ditinsrt.ksh)
-
Purge Closed Deals (deal_purge_job) - background job
-
Purge Closed Deals Actuals Item/Location (deal_actuals_purge_job) - background job
See also the Merchandising Operations Guide Volume 2 for details on the following batch-based integrations related to deals:
-
Upload of Deals from 3rd Party Systems (dealupld)
-
Stage Complex Deal Invoice Information (vendinvc)
-
Stage Fixed Deal Invoice Information (vendinvf)
Calculate Actual Impact of Billback Deals (dealact)
|
Module Name |
dealact.pc |
|
Description |
UCalculate Actual Impact of Billback Deals |
|
Functional Area |
Deals |
|
Module Type |
Business Processing |
|
Module Technology |
ProC |
|
Catalog ID |
RMS206 |
|
Wrapper Script |
rmswrap_multi.ksh |
Design Overview
This program will run on a daily basis and calculate actuals information to update the deal actuals table at the item/location level for bill back non rebate deals, bill back purchase order rebate deals and bill back sales and receipts deals.
Calculate Weekly/Monthly Income Based on Turnover (dealinc)
|
Module Name |
dealinc.pc |
|
Description |
Calculate Weekly/Monthly Income Based on Turnover |
|
Functional Area |
Deals |
|
Module Type |
Business Processing |
|
Module Technology |
ProC |
|
Catalog ID |
RMS211 |
|
Wrapper Script |
rmswrap.ksh |
Design Overview
This program generates income for each item/location for bill-back deals.
Dealinc.pc retrieves deal attributes and actuals data from the deals tables for complex deals. It then calculates the income and will update the actuals table with the calculated income value. Additionally the program will insert the income value into the TEMP_TRAN_DATA table using the tran types deal sales and deal purchases.
Subsequent programs will run to perform forecast processing for active deals and to roll up TEMP_TRAN_DATA rows inserted by the multiple instances of this module and insert/update DAILY_DATA with the summed values and then insert details from TEMP_TRAN_DATA into TRAN_DATA. Income is calculated by retrieving threshold details for each deal component and determining how to perform the calculation (that is, Linear/Scalar, Actuals Earned/Pro-Rate).
Calculates/Update Forecasted Values for Deals (dealfct)
|
Module Name |
dealfct.pc |
|
Description |
Calculates/Update Forecasted Values for Deals |
|
Functional Area |
Deals |
|
Module Type |
Business Processing |
|
Module Technology |
ProC |
|
Catalog ID |
RMS209 |
|
Wrapper Script |
rmswrap_multi.ksh |
Design Overview
This program aggregates income for each item/location and recalculates forecasted values. It maintains forecast periods, deal component totals and deal totals.
After determining which active deals need to have forecast periods updated with actuals, the program will then sum up all the actuals for the deal reporting period and update the table with the summed values and change the period from a forecast period to a fixed period. The program will also adjust either the deal component totals or the remaining forecast periods to ensure that the deal totals remain correct. For each deal, the program will also maintain values held at header level.
Close Expired Deals (deal_close_job)
|
Module Name |
deal_close_job |
|
Description |
Close Expired Deals |
|
Functional Area |
Deals |
|
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 deal header table that reached their close date. The purpose of this module is to close any active deals that have reached their close date. Closed deals are still available in the system for reference and audit purposes, but as the deals are expired, they will not be applied or processed. 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 update the records from deal header table to "C"losed status. Any existing Deal records from the deal queue table will be re-inserted again through calling FUTURE_COST_EVENT_SQL.ADD_DEALS program. 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.
Close Expired Deals (dealcls)
|
Module Name |
dealcls.pc |
|
Description |
Close Expired Deals |
|
Functional Area |
Deals |
|
Module Type |
Admin |
|
Module Technology |
ProC |
|
Catalog ID |
RMS207 |
|
Wrapper Script |
rmswrap.ksh |
Daily Posting of Deal Income to Stock Ledger (dealday)
|
Module Name |
dealday.pc |
|
Description |
Daily Posting of Deal Income to Stock & General Ledgers |
|
Functional Area |
Deals |
|
Module Type |
Business Processing |
|
Module Technology |
ProC |
|
Catalog ID |
RMS208 |
|
Wrapper Script |
rmswrap.ksh |
Design Overview
This batch module posts all the deal income records to the Stock Ledger and the Genera Ledger.
This program extracts data inserted by dealinc.pc. In order to simplify this program, a dealday pre function (in prepost.pc) will sum up the data into a temporary table. A dealday post function (in prepost.pc) will copy data to transaction table and then purge temporary tables.
Deal Calculation Queue Insert Multithreading (batch_ditinsrt.ksh)
|
Module Name |
batch_ditinsrt.ksh |
|
Description |
Deal Calculation Queue Insert Multithreading |
|
Functional Area |
Deals |
|
Module Type |
Business Processing |
|
Module Technology |
Ksh |
|
Catalog ID |
RMS187 |
|
Wrapper Script |
N/A |
Restart/Recovery
A commit occurs when all details of a deal are processed. Inherent restart/recovery is achieved through deleting deals from the DEAL_QUEUE table when they are processed. Because DEAL_QUEUE is part of the driving cursor, processed deals will not be fetched again when the program restarts.
Insert into Deal Calculation Queue (ditinsrt)
|
Module Name |
ditinsrt.pc |
|
Description |
Insert into Deal Calculation Queue |
|
Functional Area |
Deals |
|
Module Type |
Business Processing |
|
Module Technology |
ProC |
|
Catalog ID |
RMS217 |
|
Wrapper Script |
N/A |
Design Overview
This batch program will populate the DEAL_CALC_QUEUE table with orders that may be affected by non vendor-funded, non PO-specific deals that are on the DEAL_QUEUE table (for future processing by orddscnt.pc).
Orders that had been applied to deals that no longer apply will also be inserted into the DEAL_CALC_QUEUE table. Processed records will then be deleted from the DEAL_QUEUE table
Restart/Recovery
A commit occurs when all details of a deal are processed.
Inherent restart/recovery is achieved through deleting deals from the DEAL_QUEUE table when they are processed. Because DEAL_QUEUE is part of the driving cursor, processed deals will not be fetched again when the program restarts.
Purge Closed Deals (deal_purge_job)
|
Module Name |
deal_purge_job |
|
Description |
Purge Closed Deals |
|
Functional Area |
Deals |
|
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 complex deal header and fixed deals tables based on its purge criteria from system parameter settings. The Deal History Months parameter will determine old/aged deals after they have held in specific number of months after they were closed. PO-specific deals will not be covered in this purge processing. 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 either complex deal related tables or fixed deal related tables depending if indicator is complex deal or not. For Fixed Deals, DELETE_RECORDS_SQL.DEL_FIXED_DEAL is called while complex deals will be processed with call to DELETE_RECORDS_SQL.DEL_DEAL. 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.
Key Tables Affected
Table 6-2 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
DEAL_HEAD |
Yes |
No |
No |
Yes |
|
SYSTEM_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_DEAL_PURGE_STG |
Yes |
Yes |
No |
Yes |
|
DEAL_HEAD_CFA_EXT |
No |
No |
No |
Yes |
|
FIXED_DEAL |
Yes |
No |
No |
Yes |
|
DEAL_ITEM_LOC_EXPLODE |
No |
No |
No |
Yes |
|
DEAL ACTUALS_FORECAST |
No |
No |
No |
Yes |
|
DEAL_ITEMLOC_DIV_GRP |
No |
No |
No |
Yes |
|
DEAL_ITEMLOC_DCS |
No |
No |
No |
Yes |
|
DEAL_ITEMLOC_ITEM |
No |
No |
No |
Yes |
|
DEAL_ITEMLOC_PARENT_DIFF |
No |
No |
No |
Yes |
|
DEAL_COMP_PROM |
No |
No |
No |
Yes |
|
DEAL_PROM |
No |
No |
No |
Yes |
|
DEAL_THRESHOLD_REV |
No |
No |
No |
Yes |
|
DEAL_THRESHOLD |
No |
No |
No |
Yes |
|
DEAL_QUEUE |
No |
No |
No |
Yes |
|
POP_TERMS_FULFILLMENT |
No |
No |
No |
Yes |
|
POP_TERMS_DEF |
No |
No |
No |
Yes |
|
DEAL_DETAIL |
No |
No |
No |
Yes |
|
FIXED_DEAL_MERCH_LOC |
No |
No |
No |
Yes |
|
FIXED_DEAL_MERCH |
No |
No |
No |
Yes |
|
FIXED_DEAL_DATES |
No |
No |
No |
Yes |
|
FIXED_DEAL_GL_REF_DATA |
No |
No |
No |
Yes |
|
KEY_MAP_GL |
No |
No |
No |
Yes |
Purge Closed Deals (dealprg)
|
Module Name |
dealprg.pc |
|
Description |
Purge Closed Deals |
|
Functional Area |
Deals |
|
Module Type |
Admin |
|
Module Technology |
ProC |
|
Catalog ID |
RMS212 |
|
Wrapper Script |
rmswrap.ksh |
Design Overview
The purpose of this batch program is to purge deals after they have been held in the system for the specified number of history months after they are closed. The number of months of history is defined in the PURGE_CONFIG_OPTIONS table in the DEAL_HISTORY_MONTHS column.
The batch program will also delete deal performance tables based on the specified number of history months. This program will not cover PO-specific deals, which will be purged with the PO.
Purge Closed Deals Actuals Item/Location (deal_actuals_purge_job)
|
Module Name |
deal_actuals_purge_job |
|
Description |
Purge Closed Deals |
|
Functional Area |
Deals |
|
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 complex deal header and deal actuals forecast tables based on updated last invoice date that were processed beyond the current date. 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 either deal actuals item-location table only. 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.
Key Tables Affected
Table 6-3 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
DEAL_HEAD |
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_DEAL_ACTUALS_PURGE_STG |
Yes |
Yes |
No |
No |
|
DEAL_ACTUALS_ITEM_LOC |
No |
No |
No |
Yes |
|
DEAL ACTUALS_FORECAST |
Yes |
No |
No |
No |
Update OTB After Deal Discounts (discotbapply)
|
Module Name |
discotbapply.pc |
|
Description |
Update OTB After Deal Discounts |
|
Functional Area |
Deals |
|
Module Type |
Business Processing |
|
Module Technology |
ProC |
|
Catalog ID |
RMS215 |
|
Wrapper Script |
rmswrap_multi.ksh |
Design Overview
Deals processing can change the cost on purchase orders. When this occurs (in the batch program orddscnt.pc), Open To Buy (OTB) must also be updated to ensure that budgets reflect reality. This program updates these OTB buckets.