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
- Recalculate Weighted Average Cost (dealrecalcwac)
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 the actual income in order to update the deal actuals table at the item/location level for regular bill back deals and bill back rebate deals based on purchases, sales, and issues.
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 |
Recalculate Weighted Average Cost (dealrecalcwac)
Module Name | dealrecalcwac.ksh |
Description | Recalculate Weighted Average Cost |
Functional Area | Procurement |
Module Type | Business Processing |
Module Technology | Ksh |
Catalog ID | |
Wrapper Script | rmswrap_shell.ksh |
Design Overview
This job recalculates the weighted average cost (WAC) for item/locations associated with deals that have recalc_inventory_value_ind as 'Y'.
For billback/billback rebate deals, recalc_inventory_value_ind default to N which will drive the existing tran code 6 posting. If set to Y, will apportion the deal income to the item locations on the deal using a new tran code (deal cost adjustment - 19) which will result in recalculation of Average cost and tran code (deal cost variance - 69) will also get posted by this job.
If the Recalculate Inventory Value is set to Y (checked) then this job will call the inventory layer framework with the order-item or order-receipt-item or inventory layer id-item combination on each DEAL_ACTUALS_ITEM_LOC_DLY record for such deals. Look for rows in the inventory layer table using the combination mentioned above and having non-zero value of stock on hand. (This step tracks where the inventory from the order eligible for the deal now resides). For each of the location identified as carrying the order-item combination, adjust the WAC for the item using the below:
- A = Current WAC * stock_on_hand (from Item Loc SOH)
- B = Deal Income per Unit * SOH (Sum of Stock on Hand on Inventory Layer rows for the order-item-location combination)
- New WAC = (A-B) / stock_on_hand from ITEM_LOC_SOH
- Total Cost = Deal Income per Unit * Stock on Hand on Inventory Layer rows for the order-item or order-receipt-item or layer id-item combination
- Deal Income per Unit calculates against each row for current reporting period by dividing the actual_income with actual_turnover_units in DEAL_ACTUALS_ITEM_LOC_DLY table.
- This adjustment will be accompanied by a new tran code 19 (Deal Cost Adjustment).
Then a new cost variance tran code 69 (Cost Variance-Deal) should be recorded for the difference between the sum of the stock on hand on rows with the order-item or order-receipt-item or inventory layer id-item combination and the original quantity that is on deal. This represents the portion of the qunatity that is no longer in the retailer system. The Total Cost on the Cost Variance is computed as below.
- A = Qty accrued on deal (Actual turnover units on DEAL_ACTUALS_ITEM_LOC_DLY)
- B =Qty currently on inventory ledger at the location
- = Sum of stock on hand column on ITEM_LOC_SOH_LEDGER for records with matching order-item or order-receipt-item or order-layer id-item combination
- Total Cost= Deal Income per Unit * (A-B)
This cost variance would be recorded at the deal location.
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.