Mirror of Oracle documentation
Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.
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:
-
Calculate Actual Impact of Billback Deals (dealact)
-
Calculate Weekly/Monthly Income Based on Turnover (dealinc)
-
Calculates/Update Forecasted Values for Deals (dealfct)
-
Close Expired Deals (deal_close_job) - background job
-
Close Expired Deals (dealcls)
-
Daily Posting of Deal Income to Stock Ledger (dealday)
-
Deal Calculation Queue Insert Multithreading (batch_ditinsrt.ksh)
-
Insert into Deal Calculation Queue (ditinsrt)
-
Purge Closed Deals (deal_purge_job) - background job
-
Purge Closed Deals (dealprg)
-
Purge Closed Deals Actuals Item/Location (deal_actuals_purge_job) - background job
-
Recalculate Weighted Average Cost (dealrecalcwac)
-
Update OTB After Deal Discounts (discotbapply)
-
Calculation of Fixed Deal Income for General Ledger (dealfinc)
-
Stage Fixed Deal Invoice Information (vendinvf)
See also the Merchandising Operations Guide Volume 2 for details on the following batchbased integrations related to deals:
-
Upload of Deals from 3rd Party Systems (dealupld)
-
Stage Complex Deal Invoice Information (vendinvc)
Calculation of Fixed Deal Income for General Ledger (dealfinc)
Schedule
Oracle Retail Merchandising Batch Schedule.
Design Overview
For postpaid fixed deals at merchandise level with Recalculate Inventory Value set to No, the program prorates fixed-deal income across deal department, class, subclass, and locations on collection dates. It writes the prorated income to STG_FIF_GL_DATA , together with KEY_MAP_GL and FIXED_DEAL_GL_REF_DATA .
For other cases, the program writes to TRAN_DATA : transaction code 8 when Recalculate Inventory Value is No, and transaction code 19 plus weighted-average-cost recalculation when it is Yes. For prepaid fixed deals, it writes the full fixed-deal amount with transaction code 8 on the first collection date and writes reversing transaction-code-8 entries on subsequent posting dates.
Restart/Recovery
The logical unit of work is a DEAL_ID . The program commits when the number of processed deal records reaches the restart-control-table commit maximum.
Integration Contract
The program publishes fixed-deal income for download from Merchandising through the STG_FIF_GL_DATA table.
Stage Fixed Deal Invoice Information (vendinvf)
Schedule
Oracle Retail Merchandising Batch Schedule.
Design Overview
The batch module creates staging-table records for fixed-type deals. Invoicing is driven by fixed-deal collection dates, and the amount to invoice is retrieved from the fixed-deal tables for each deal date.
The prepost vendinvf pre process truncates STAGE_FIXED_DEAL_HEAD and STAGE_FIXED_DEAL_DETAIL . The post process calls process_fixed_deal to update postpaid and prepaid fixed-deal claim status.
Restart/Recovery
The logical unit of work is the transaction comprising a deal_id and collection date. Data is committed when processed transactions reach or exceed max_commit_ctr .
Integration Contract
The staged fixed-deal invoice information is available for download from Merchandising.
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
Schedule
Oracle Retail Merchandising Batch Schedule
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.
Restart/Recovery
The database commit will take place when the number of deal_id/deal_detail_id records processed is equal to commit max counter in the restart control table.
Design Assumptions
N/A
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
Schedule
Oracle Retail Merchandising Batch Schedule
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).
Restart/Recovery
A commit will take place after the number of deals records processed is equal to the commit max counter from the RESTART_CONTROL table.
Design Assumptions
N/A
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
Schedule
Oracle Retail Merchandising Batch Schedule
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.
Restart/Recovery
A commit will take place after the number of deals records processed is equal to the commit max counter from the RESTART_CONTROL table.
Design Assumptions
N/A
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
Schedule
Oracle Retail Merchandising Batch Schedule
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.
Restart/Recovery
N/A
Key Tables Affected
Table 6-1 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
| RMS_BATCH_STATUS | Yes | No | No | No |
| B8D_PROCESS_CONFIG | Yes | No | No | No |
| JOB_AUDIT_LOGS | No | Yes | No | No |
| B8D_DEAL_CLOSE_STG | Yes | Yes | No | Yes |
| DEAL_HEAD | Yes | No | Yes | No |
| DEAL_QUEUE | Yes | Yes | No | No |
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
Schedule
Oracle Retail Merchandising Batch Schedule
Design Overview
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 because the deals are expired, they will not be applied or processed.
Restart/Recovery
N/A
Design Assumptions
N/A
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
Schedule
Oracle Retail Merchandising Batch Schedule
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.
Restart/Recovery
A commit will take place after the number of dept/class/subclass records processed is greater than or equal to the max counter from the RESTART_CONTROL table.
Design Assumptions
N/A
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
Schedule
Oracle Retail Merchandising Batch Schedule
Design Overview
The purpose of this module is to multithread the ditinsrt batch program.
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.
Design Assumptions
N/A
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
Schedule
Oracle Retail Merchandising Batch Schedule
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.
Design Assumptions
N/A
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
Schedule
Oracle Retail Merchandising Batch Schedule
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.
Restart/Recovery
N/A
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 |
Table 6-2 (Cont.) Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
| 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 |
| ed Deals (dealpr Module Name dealprg. | g) pc | |||
| Description Purge C | losed Deals | |||
| Functional Area Deals | ||||
| Module Type Admin | ||||
| Module Technology ProC | ||||
| Catalog ID RMS21 | 2 | |||
| Wrapper Script rmswra | p.ksh |
Purge Closed Deals (dealprg)
Schedule
Oracle Retail Merchandising Batch Schedule
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.
Restart/Recovery
This program has inherent restart/recovery since records that were processed are deleted from the table. As a result, the driving cursor will never fetch the same records again.
Design Assumptions
N/A
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
Schedule
Oracle Retail Merchandising Batch Schedule
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.
Restart/Recovery
N/A
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 |
Table 6-3 (Cont.) Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
| 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 |
Schedule
Oracle Retail Merchandising Batch Schedule
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 orderitem 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-receiptitem 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.
Restart/Recovery
N/A
Design Assumptions
N/A
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
Schedule
Oracle Retail Merchandising Batch Schedule
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.
Restart/Recovery
This program has inherent restart ability, because records are deleted from DISC_OTB_APPLY as they are processed. Array processing is used. Records are array fetched from DISC_OTB_APPLY table, processed and committed to the database.
Schedule
Oracle Retail Merchandising Batch Schedule
In this guide
- Guide: Batch Operations Guide
- Previous: 5 Purchase Order
- Next: 7 Contracts
Related chapters
- 5 Purchase Order — Batch Operations Guide · shares
DEAL_CALC_QUEUE,DEAL_COMP_PROM,DEAL_DETAIL,DEAL_HEAD - 17 Stock Ledger — Batch Operations Guide · shares
DAILY_DATA,KEY_MAP_GL,RESTART_CONTROL,TRAN_DATA - 6 Scheduled Integration — Inbound and Outbound Integration Guide · shares
DAILY_DATA,DEAL_ACTUALS_ITEM_LOC,DEAL_COMP_PROM,DEAL_HEAD - 5 ReSTful Web Services — Inbound and Outbound Integration Guide · shares
DEAL_ACTUALS_ITEM_LOC,DEAL_CALC_QUEUE,DEAL_COMP_PROM,DEAL_DETAIL - 6 Metrics — Retail Insights Cloud Service User Guide · shares
DEAL_ACTUALS_ITEM_LOC,DEAL_HEAD