Stages of Calculations in the Order Calculation Batch

Launch

The batch is launched using the script rpm_create_orders_master.
Possible parameters:

--diagram '1,2' – calculate all diagrams related to 1 and 2.

--diagram_from 'TABLE_NAME, COLUMN' – take the list of diagrams from a table/column. Ignored if --diagram is present.

--days 7 – overrides the ordersDays option.

--date_shift 1 – shifts the current date forward.

--stores '21,34' – list of stores for which the calculation should be performed.

--warehouse '1,2,3' – list of warehouses to calculate, including related orders from stores.

--direct_orders – calculate only direct store-to-supplier orders, excluding warehouses.

--no_store_orders – do not calculate store orders, warehouses only.

--calc_calendars_only – calculate only new calendars.

--do_not_confirm – do not auto-confirm orders at the end of the calculation.

--send_email – when calculating all diagrams, send a statistics email to addresses in options-mail-to.

--force_send_email – always send a statistics email to addresses in options-mail-to, regardless of parameters.

--send_email_suppliers – send generated orders to suppliers (only if confirmed).

--use_budget – enable calculation according to budgets.

--concurrent_stores 2 – number of stores calculated simultaneously in a single thread (max 3).

--max_workers 3 – number of parallel threads for store calculation (minimum 2, but no more than available processors).

For debugging:

--localdb – use database connection settings from loader - local_connection.

--no_batches – do not launch additional scripts before or after calculations.

--do_not_touch_db – do not write anything to the database.

--skip_calculations – skip calculations.

--dblog – output all database queries being generated, with parameter substitutions applied.

Sometimes it is necessary to allocate more memory than default for calculations via execArgv--max-old-space-size= (in MB).

List of diagrams

If --diagram, --diagram_from, parameters are present, a list of diagrams for calculation is generated (method decideWhichDiagramsToLaunch + rpm_get_main_record_diagram_by_id, rpm_get_all_cross_dock_if_any, rpm_get_store_picking_diagrams_by_contract_and_whs_id).
If a diagram is of picking or cross-dock type, all diagrams that may potentially affect warehouse orders will be included.

Note: lists larger than 2,500 diagrams may cause the database parser to fail, and the query will not execute.

Calendars

The list of active diagrams and the latest calendar dates for them are retrieved (rpm_get_all_diagrams). Calendars are extended a certain number of days forward from the current date + the calendarDays parameter.

WARNING: existing calendars are not deleted or recalculated, unlike orders. To recalculate calendars, they must be deleted first.
If the --calc_calendars_only parameter is used, the batch ends at this point.

Cache table

A temporary table RPM_SKU_BY_DIAGRAMS is created, which acts as a stop-flag for running other order and diagram calculations.

Pre-processing

The SQL batch rpm_pre_calc_orders_batch is executed.

List of stores and warehouses

The list of stores is retrieved (rpm_get_stores_for_orders), sorted in descending order by average store sales. The list of warehouses is also retrieved. Warehouse lists are analyzed by supply scheme, then sorted so that warehouses at the end of the chain are calculated first, and those at the beginning last.

Warehouse balances

An object with warehouse stock balances is prepared (getEstimatedWhsStock).

Queries used:

• rpm_get_closest_picking_calendars

• rpm_get_forecasts_for_picking

• rpm_get_latest_stock_for_whs_stock_estimation

• rpm_get_whs_orders_by_delivery_date_period_for_picking

Each new order line subtracts from the warehouse balances here.

Forecast ID

The ID of the last forecast is cached (rpm_last_fcst_id). If not found, the calculation is terminated.

Calculating Stores in Sequence

The calculation is performed store by store, in descending order of average sales. If parallel calculation is enabled, some stores may be processed simultaneously, but groups are still processed in sorted order.

Procedure: calcOrdersByStore

Populating the Table for Calculations

The table RPM_SKU_BY_DIAGRAMS is populated with data for the store with three fields: DIAGRAM_ID, SKU_ID, STORE_ID. This table then limits the records used for order calculation (rpm_fill_tmp_table_with_skus_by_diagram).

Retrieving Data for Order Skeletons Formation

For each store, the main data is retrieved using the following queries:

• rpm_get_diagrams_by_store

• rpm_only_calendars_by_store

• rpm_get_skus_by_store

• rpm_get_presentation_stock_by_store

• rpm_get_add_presentation_stock_by_store

• rpm_get_latest_stock_by_store

• rpm_get_sku_parameters

• rpm_get_store_pick_zero_orders

• rpm_get_sales_holiday_uplift

Assortment matrices are also generated using the procedure rangeProvider.generateSkuRange. If the parameter calcOrdersForSubProducts is enabled, specifications are generated via getExpandedSpecifications and retrieved using the query rpm_get_if_bom_by_store.

Checking and Filtering Duplicates

Duplicates are checked for pairs CALENDAR_ID – SKU_ID and ORDER_ID – SKU_ID from the query rpm_get_skus_by_store. Duplicates are filtered out, and a message is logged.

Checking for Missing Links

Calendars are checked: records from rpm_get_skus_by_store for which diagrams from rpm_get_diagrams_by_store are not found, and calendars from rpm_only_calendars_by_store for which diagrams from rpm_get_diagrams_by_store are missing.

Combining Data

Data from the above queries is combined to form order skeletons using the procedure combineInitialData. Skeletons are generated for all SKUs in the found diagrams and for each calendar record that is in the range or returned by rpm_get_if_bom_by_store or rpm_get_product_keys.

Supplementing Data

Order skeletons are supplemented with data on presentations, stock, and SKU parameters using the procedure attachSkuDetails.

Completing Matrices

Assortment matrices are supplemented for substitute SKUs where the main product is missing using the procedure addKeySKURange.

Checking Order Duplicates

The procedure checkOrderDuplicates checks for duplicates by the key SKU_ID / ORDER_DATE / DELIVERY_DATE, keeping the first occurrence and deleting subsequent duplicates.

Reorganizing Pick-to-Zero Orders

Since pick-to-zero orders are retrieved by store order calendars, but actually the orders correspond to warehouse delivery dates with the nearest date according to the store schedule, order and delivery dates are reorganized. Extra orders between warehouse delivery dates are removed, and the order date is changed in the necessary orders (reorganizePickToZeroOrders). After that, duplicate checks by SKU_ID, ORDER_DATE, DELIVERY_DATE are repeated.

Additional Presentation

Additional presentation is added to orders via the procedure applyAdditionalLoad. Inside it, in prepareAddLoadsObj, it is retrieved using the query rpm_get_add_load_by_store.

Transforming Increases for Holidays

Holiday uplifts retrieved from the database via rpm_get_sales_holiday_uplift are transformed into an object for further use.

Analyzing Minimum and Maximum Dates

Minimum and maximum dates are analyzed for ORDER_DATE, DELIVERY_DATE, STOCK_DATE, and PLUS_D1D2_FCST (number).

Generating Matrix Map by Dates

The procedure generateSkuDateMapByStore generates a date map of assortment matrices.

Loading Forecast Data

Forecast data is retrieved via rpm_get_forecasts_by_store and rpm_get_forecasts_for_picking_by_store.

Additionally, procedure calcAndAdjustSkuSalesShares retrieves data for adjusting sales by day-of-week using queries rpm_get_sales_sku_share, rpm_get_elasticity_formulas, rpm_get_prices_for_store. Sales shares are recalculated according to price elasticity considering price changes within the week.

Checking Presence in Matrices

Order skeletons are checked for presence in the matrix by SKU_ID and ORDER_DATE, and the flag isInRange is added.

Supplementing Promotion Data

Promotion data is supplemented in order skeletons using attachPromoToOrders.

Distributing Forecast by Days

Weekly sales forecasts are distributed by days according to recalculated daily shares.

Adding Shipment Forecast

Shipment forecasts are added to daily sales forecasts if it is a warehouse (already retrieved by days using rpm_get_forecasts_for_picking_by_store).

Removing Non-Working Days

Non-working days are removed from daily forecasts according to the parameter non_working_dates.

Supplementing Warehouse Promotion Data

Warehouse orders are supplemented with promotion data using attachPromoToOrdersPicking.

Preparing Goods in Transit

Goods-in-transit object is prepared using the procedure prepareGoodsInTransitObj. Inside, data is retrieved from the database via rpm_get_store_orders_by_delivery_date_period and rpm_get_store_store_transfers_in_route.

Forecasting by Similar Items

For similar SKUs that are not in the matrix but have a contract and have order skeletons, sales forecasts are added from similar SKUs that are in the matrix but have no contract (addFcstForProductByKeys).

Generating Forecasts for Periods O1D1 and D1D2

The procedure distributeForecastsByOrderDays fills O1D1 and D1D2 forecasts according to STOCK_DATE, DELIVERY_DATE, NEXT_DELIVERY_DATE based on the consolidated daily forecast.

Calculating Ingredient Consumption Forecast

If the parameter calcOrdersForSubProducts is enabled:

1. Procedure getSubProductsForecasts retrieves forecasts for master products using rpm_get_master_products_forecasts_by_store and rpm_get_master_products_forecasts_picking_by_store and transforms them into ingredient consumption forecasts according to specifications from table IF_BOM.

2. Specifications are inverted using invertSpecifications.

3. Master product presentations are retrieved via getMasterProductStockPresObject and query rpm_get_master_products_stock_presentation.

4. Forecasts are added to ingredient orders by procedures attachSubProductsToOrders and distributeSubProductsForecastsByOrderDays.

Calculating Shelf-Life Limits

The maximum quantity that can be ordered to sell the product within a certain percentage of shelf life (SHELF_DAYS_LIMIT) is calculated.

Calculating Write-Off Forecasts

If the parameter doNotCalcWriteOffs is not enabled, projected write-offs D1 and D1D2 are calculated and added to orders (generateInitialWriteOffsEstimation).

Order Calculation

Orders are sorted by dates, separated by SKU, and calculated using calcActualOrders.

Summarizing Data by Ingredients

Forecasts, presentations, and stock data for master products using the same ingredient are summed in ingredient orders (initially in ingredient orders they were separate per master product) to save memory.

Adding “Tender” Flag

Orders eligible for contract change (tender) are flagged accordingly.

Deleting Orders

Existing unconfirmed orders from the current date, as well as additional pick-to-zero calendars and orders, are deleted from the database.

Creating Pick-to-Zero Calendars

Pick-to-zero calendars are created and inserted using createPickZeroCalendars.

Splitting Orders by Type

Orders are split by type using breakLinesBySeparateHeaders: pzprt, pzpr, pzt, prt, pz, pr, t.

Order Headers

Order IDs are assigned and orders are grouped into headers using assignOrderId and groupHeadersById.

Calculating Final Values

Final order totals are calculated using assignTotals. Orders that do not meet the minimum quantity or amount from the diagram are flagged for optimization (orderOptimizationMinPercent from the options file).

Optimizing Orders by Quantity or Total

The procedure optimizeOrderVolumesEqual optimizes orders by quantity or amount for diagrams where these parameters are set. If the order amount reaches the percentage specified by orderOptimizationMinPercent, it is raised to the minimum amount considering orderOptimizationShelfOverLimit (maximum shelf-life limit exceedance in percent) and orderOptimizationMaxStockDays (maximum stock in days).

Recalculating Order Totals After Optimization

Final order totals are recalculated using assignTotals. If optimization fails, the header retains the optimization-required flag.

Inserting into Database

Orders are inserted into the database.

Post-Processing

SQL batch rpm_after_create_store_orders_batch is executed.

Calculating warehouse orders

Выполняется в отсортированном порядке по их месту в цепочке поставок исключительно последовательно, без параллелизации.
Функция calcOrdersByStore с параметром isWhs=true

Determining a list with delivery (today)

Заполняется таблица RPM_SKU_BY_DIAGRAM для заказов склад-поставщик у которых дата доставки - сегодня

Clearing delivery boxes

Запросом rpm_clear_store_adjusted_delivery_today_order_delivery стирается adjusted_delivery для заказов магазинов, у которых дата доставки сегодня

Calculating warehouse orders

Запускается функцией calcStoreOrders c параметром isWhs только для диаграмм, у которых дата доставки сегодня.

Rebalancing store orders by balance

Для тех заказов магазинов, где дата доставки сегодня, запускается выравнивание заказов магазинов по остатку склада функцией alignPickingOrdersWithWhsStock

Recalculating future orders

Функцией recalcAdjustedPickingOrders выполняется пересчет будущих заказов для случаев, когда по сегодняшним заказом поменялось adjusted_delivery. Пересчитываются товары в пути, остаток D1 и сам заказ

Linking lots

Если установлена опция assignPickingOrderLots , выполняется процедура assignPickingOrderLots
rpm_get_picking_orders_for_lots_assignment
rpm_get_picking_lots_for_assignment
rpm_delete_assigned_lots
+ таблица RPM_ORDER_LINE_LOT

Recalculating remaining warehouse orders

Выполняется calcStoreOrders для склада с общим параметром/списком диаграмм

Saving picking orders

запрос rpm_save_picking_forecast_orders

Calculating cross-dock orders

Вызывается процедура calcCrossDockOrders для расчета кросс-док заказов склад-поставщик и оптимизации заказов магазинов под минимальное количество поставщика

Post-processing

SQL batch rpm_after_create_orders_batch is executed.

Clearing the cache

The temporary table RPM_SKU_BY_DIAGRAMS is deleted.
WARNING: from this point, actions that were previously blocked by this table may already be running.

Sending orders

If the parameter --send_email_suppliers is set, generated and confirmed orders are sent to suppliers. Emails are sent to the mailer cache, from where they are distributed after the batch completes.

Final script

SQL batch rpm_end_create_orders_batch is executed.

Sending statistics

Emails with statistics are sent according to the parameters --send_email and --force_send_email.