Skip to content

Documentation: OneBeat Inventory Delta Function

Overview and Metadata

  • Purpose: This SQL function, ONEBEAT_GET_DELTA_INVENTORIES, is designed to extract and transform inventory data, providing a consolidated view of available, in-transit, and reserved stock quantities for the OneBeat store replenishment system. It specifically filters data relevant to a specified company, adhering to pilot phase conditions.
  • Data Refresh: On-demand or scheduled, depending on the integration and orchestration of the Snowflake environment. This function is typically called by a data pipeline or application.
  • Project Context: This function is a critical component of the Store Replenishment Project, currently in a pilot phase focused on Italian stores. Its output directly supports the calculation of replenishment needs.
  • Source Tables:
    • DW_LOADTARGET.DWH.ONEBEAT_COMPANY_RECOD: Contains company-specific recoding and pilot conditions.
    • DW_LOADTARGET.DWH.TBDWFT_STOCK_BY_SIZE: Core stock fact table, providing detailed inventory by size.
    • DW_LOADTARGET.DWH.WHS_ENABLED_ONEBEAT: Lists warehouses enabled for OneBeat, used for filtering relevant stock locations.
    • DW_LOADTARGET.DWH.VWDWFT_STOCK_W_TECNICA_BY_SIZE: Provides "technical location" stock, which represents stock adjustments or movements.
    • DW_LOADTARGET.DWH.TBDWMD_ARTICLE: Contains master data for articles, including business type and sales collection year.
  • Output: The function returns a table with aggregated inventory quantities (location_id, site_qty, sku_id, source_location_id, status_date, transit_qty, avoid_replenishment, reserved_qty), intended for direct consumption by the OneBeat replenishment logic or related dashboards.

Data Flow Diagram

flowchart TD
    subgraph Source Tables
        subgraph Sourcing

        B[TBDWFT_STOCK_BY_SIZE]:::source
        D[VWDWFT_STOCK_W_TECNICA_BY_SIZE]:::source

        end
        subgraph Filtering
        A[ONEBEAT_COMPANY_RECOD]:::source
        C[WHS_ENABLED_ONEBEAT]:::source
        E[TBDWMD_ARTICLE]:::source
        end
    end

    subgraph CTEs
        F[FILTERED_COMPANY_CODS]:::cte
        G[TOTAL_STOCK]:::cte
        H[FORMATTED_STOCK]:::cte
    end

    I[ONEBEAT_GET_DELTA_INVENTORIES Output]:::output

    A -- :COMPANY --> F
    B --> G
    C --> G
    D --> G
    E --> G
    F --> G
    G --> H
    H -- Remove if qty = 0 --> I

Executive Summary & Business Logic

Business Goal: The primary business objective of this query is to provide a precise and up-to-date snapshot of inventory levels across various locations (stores and warehouses) for a specific retail company. This data is crucial for the OneBeat system to accurately determine which products need to be replenished at which stores, ensuring optimal stock levels and minimizing lost sales due to out-of-stock situations. It consolidates different stock types (on-site, in-transit, reserved) into a unified view.

Key Business Rules: 1. Pilot Scope Filtering: The query strictly adheres to pilot conditions, processing data only for the COMPANY specified in the function call, which currently implies Italian stores for the pilot phase. 2. Stock Type Classification: Inventory quantities are meticulously categorized into site_qty (physical stock at a location, adjusted for technical movements), transit_qty (stock currently in movement between locations), and reserved_qty (stock allocated but not yet shipped). This classification is based on specific subject_type_cod and class_cod combinations. 3. Article Eligibility: Only articles that are designated as "sale products" (i.e., business_cod not equal to 'C') and belong to sales collections from 2023 onwards are considered for replenishment, ensuring focus on current and relevant merchandise.

Execution Parameters and Pilot Conditions

This function accepts three parameters: - START_DATE (NUMBER): The start date (in YYYYMMDD numeric format) for filtering stock posting dates. - END_DATE (NUMBER): The end date (in YYYYMMDD numeric format) for filtering stock posting dates. - COMPANY (VARCHAR): The name of the company for which inventory data should be retrieved. This parameter is crucial for enforcing the pilot conditions, as it limits the scope of the query to specific companies (e.g., 'BATA', 'AW_LAB') that are part of the Italy stores pilot. The FILTERED_COMPANY_CODS CTE ensures that only data related to the specified company's country_cod and company_cod is processed. These pilot conditions are subject to change as the project evolves.

Final Columns Data Dictionary

Column Name Data Type Description Notes
location_id VARCHAR Unique identifier for the inventory location (store or warehouse). Formatted as COUNTRY_PRIMARYCOMPANY_SUBJECTCOD for stores, or PRIMARY_WH for primary warehouses.
site_qty NUMBER The quantity of stock physically available at the location, adjusted for technical stock movements. Represents stock available for immediate replenishment. Derived from W_CL1000, W_CL2001, S_CL1000, and TECNICA stock classes.
sku_id VARCHAR Unique identifier for the Stock Keeping Unit (SKU). Formatted as COUNTRY_PRIMARYCOMPANY_ARTICLECOD_SIZEDES.
source_location_id VARCHAR The primary warehouse associated with the company. Used to identify the main source for replenishment.
status_date TIMESTAMP The date when the stock status was recorded. Converted from posting_dat_num (YYYYMMDD) to a timestamp.
transit_qty NUMBER The quantity of stock currently in transit to the location. Represents stock that is on its way and will soon be available. Derived from T_CL1000, F_CL1000, and S_CL2000 stock classes.
avoid_replenishment NUMBER Flag indicating whether replenishment should be avoided for this SKU at this location. Currently always 0, implying no specific conditions to avoid replenishment are applied within this function.
reserved_qty NUMBER The quantity of stock that is reserved or allocated. Represents stock that is not available for general replenishment. Derived from W_CL2001 stock class (negative values are converted to positive).

Data Flow Logic

FILTERED_COMPANY_CODS

  • Purpose: This CTE identifies the specific company codes and associated primary warehouses relevant to the current execution, based on the COMPANY parameter. This acts as the initial filter for the pilot project scope.
  • Inputs: DW_LOADTARGET.DWH.ONEBEAT_COMPANY_RECOD
  • Main Logic: It selects country_cod, company_name, company_cod, primary_company, and primary_wh from the ONEBEAT_COMPANY_RECOD table, filtering records where company_name matches the COMPANY parameter passed to the function.
  • Assumptions: Assumes that the ONEBEAT_COMPANY_RECOD table contains accurate mappings for company names to their respective codes and primary warehouses, and that the COMPANY parameter will correspond to an existing company_name in this table.

TOTAL_STOCK

  • Purpose: This CTE consolidates raw stock data from two different sources (TBDWFT_STOCK_BY_SIZE and VWDWFT_STOCK_W_TECNICA_BY_SIZE), applies initial filters, and enriches the data with company and warehouse information. It also converts stock_qty to a numeric type.
  • Inputs: DW_LOADTARGET.DWH.TBDWFT_STOCK_BY_SIZE, DW_LOADTARGET.DWH.WHS_ENABLED_ONEBEAT, DW_LOADTARGET.DWH.VWDWFT_STOCK_W_TECNICA_BY_SIZE, DW_LOADTARGET.DWH.TBDWMD_ARTICLE, FILTERED_COMPANY_CODS CTE.
  • Main Logic:
    • It uses a UNION ALL to combine stock data:
      • The first part queries TBDWFT_STOCK_BY_SIZE, joining with WHS_ENABLED_ONEBEAT to identify active warehouses and with FILTERED_COMPANY_CODS for pilot scope. It filters by posting_date_num (between START_DATE and END_DATE), flag_origin = 'S' (indicating unpacked stock), and specific subject_type_cod || '_' || class_cod values (W_CL1000, W_CL2001, S_CL2000, S_CL1000, T_CL1000, F_CL1000).
      • The second part queries VWDWFT_STOCK_W_TECNICA_BY_SIZE (technical stock), joining similarly with WHS_ENABLED_ONEBEAT and FILTERED_COMPANY_CODS. It negates stock_qty (stock_qty*(-1)) and assigns 'TECNICA' as stock_class.
    • Both parts are then joined with TBDWMD_ARTICLE to apply article-specific filters: business_cod <> 'C' (not sale products) and SALES_COLLECTION_YEAR_NUM >= '2023'.
  • Assumptions: Assumes that flag_origin = 'S' correctly identifies stock ready for replenishment. It also assumes that W_CL2001 stock represents a reserved or negative quantity that needs to be offset, and TECNICA stock represents adjustments that reduce available stock. The article master data is assumed to be accurate for filtering relevant products.

FORMATTED_STOCK

  • Purpose: This CTE transforms the raw stock data from TOTAL_STOCK into the final desired format, categorizing quantities into site_qty, transit_qty, and reserved_qty, and constructing location_id and sku_id based on business rules.
  • Inputs: TOTAL_STOCK CTE.
  • Main Logic:
    • It uses CASE statements to define location_id: if subject_cod length is greater than 3 (indicating a store), it's formatted as country_cod || '_' || primary_company || '_' || subject_cod; otherwise, it defaults to primary_wh.
    • sku_id is constructed as country_cod || '_' || primary_company || '_' || article_cod || '_' || size_des.
    • site_qty is populated based on stock_class (W_CL1000, W_CL2001, S_CL1000, TECNICA).
    • transit_qty is populated based on stock_class (T_CL1000, F_CL1000, S_CL2000).
    • reserved_qty is derived from W_CL2001 stock, with its quantity multiplied by -1 to represent a positive reserved amount.
    • status_date is converted from the numeric posting_dat_num to a TIMESTAMP.
    • avoid_replenishment is hardcoded to 0 in this version of the function.
  • Assumptions: Assumes that the length of subject_cod is a reliable indicator for distinguishing stores from other locations. The specific stock_class mappings to site_qty, transit_qty, and reserved_qty are accurate based on business definitions.

Source Code

```sql CREATE OR REPLACE FUNCTION DW_LOADTARGET.DWH.ONEBEAT_GET_DELTA_INVENTORIES(START_DATE NUMBER, END_DATE NUMBER, COMPANY VARCHAR)

RETURNS TABLE ( location_id VARCHAR , site_qty NUMBER , sku_id VARCHAR , source_location_id VARCHAR , status_date TIMESTAMP , transit_qty NUMBER , avoid_replenishment NUMBER , reserved_qty NUMBER ) AS $$ -- retrieve pilot conditions WITH FILTERED_COMPANY_CODS AS ( SELECT country_cod, company_name, company_cod, primary_company, primary_wh FROM DW_LOADTARGET.DWH.ONEBEAT_COMPANY_RECOD WHERE company_name = COMPANY )

, TOTAL_STOCK AS ( SELECT TOTAL.country_cod , TOTAL.company_cod , TOTAL.subject_cod , TOTAL.article_cod , TOTAL.size_des , TOTAL.stock_class , TOTAL.stock_qty , TOTAL.posting_dat_num , TOTAL.primary_company , TOTAL.primary_wh FROM ( SELECT STOCK.country_cod , STOCK.company_cod , STOCK.subject_cod , STOCK.article_cod , STOCK.size_des , STOCK.subject_type_cod || '' || STOCK.class_cod AS stock_class , STOCK.stock_qty::NUMBER as stock_qty , STOCK.posting_date_num as posting_dat_num -- utils -- , RECOD.primary_company , RECOD.primary_wh FROM DW_LOADTARGET.DWH.TBDWFT_STOCK_BY_SIZE STOCK LEFT JOIN DW_LOADTARGET.DWH.WHS_ENABLED_ONEBEAT WHS -- retrieve the "active" warehouse per each company ON WHS.country_cod = STOCK.country_cod AND WHS.company_cod = STOCK.company_cod AND WHS.whs_cod = STOCK.subject_cod INNER JOIN FILTERED_COMPANY_CODS RECOD -- pilot conditions (bata & aw_lab) ON STOCK.country_cod = RECOD.country_cod AND STOCK.company_cod = RECOD.company_cod WHERE 1=1 AND STOCK.posting_date_num BETWEEN START_DATE AND END_DATE AND STOCK.flag_origin = 'S' -- in magazzino ci bancali in pack (P), in bancali (S) sono già spacchettati by size. -- we filter the selected stock types
AND STOCK.subject_type_cod || '' || STOCK.class_cod IN ('W_CL1000', 'W_CL2001','S_CL2000', 'S_CL1000', 'T_CL1000', 'F_CL1000') AND (WHS.whs_cod IS NOT NULL OR LEN(STOCK.subject_cod)>3) UNION ALL -- query data from VWDWFT_STOCK_W_TECNICA_BY_SIZE and move the stock_qty to site as "LOCAZIONE TECNICA" SELECT TECNICA.country_cod , TECNICA.company_cod , subject_cod , article_cod , size_des , 'TECNICA' AS stock_class , stock_qty*(-1)::NUMBER AS stock_qty , posting_dat_num -- utils -- , RECOD.primary_company , RECOD.primary_wh FROM DW_LOADTARGET.DWH.VWDWFT_STOCK_W_TECNICA_BY_SIZE TECNICA INNER JOIN DW_LOADTARGET.DWH.WHS_ENABLED_ONEBEAT WHS ON WHS.country_cod = TECNICA.country_cod AND WHS.company_cod = TECNICA.company_cod AND WHS.whs_cod = TECNICA.subject_cod INNER JOIN FILTERED_COMPANY_CODS RECOD -- filtering pilot conditions (bata & aw_lab) ON STOCK.country_cod = RECOD.country_cod AND STOCK.company_cod = RECOD.company_cod WHERE 1=1 AND TECNICA.posting_dat_num BETWEEN START_DATE AND END_DATE

) TOTAL
INNER JOIN DW_LOADTARGET.DWH.TBDWMD_ARTICLE ARTICLE
    ON ARTICLE.country_cod = TOTAL.country_cod
    AND ARTICLE.article_cod = TOTAL.article_cod
WHERE 1=1
    -- article conditions
    AND ARTICLE.business_cod <> 'C' -- sale products
    AND ARTICLE.SALES_COLLECTION_YEAR_NUM >= '2023'

)

/ , REPLENISHMENT_MAPPED_STOCK AS ( SELECT TOTAL.country_cod , TOTAL.company_cod , TOTAL.subject_cod , TOTAL.article_cod , TOTAL.size_des , TOTAL.stock_class , TOTAL.stock_qty , TOTAL.posting_dat_num , TOTAL.primary_company , TOTAL.primary_wh , NVL(REPL.enable_flag,0) AS avoid_replenishment FROM TOTAL_STOCK T_STOCK LEFT OUTER JOIN DW_LOADTARGET.DWH.tabella_avoid_repl REPL ON REPL.country_cod = T_STOCK.country_cod AND REPL.company_cod = T_STOCK.company_cod AND REPL.article_cod = T_STOCK.article_cod AND REPL.size_des = T_STOCK.size_des AND REPL.store_cod = T_STOCK.store_cod AND REPL.posting_date_num = T_STOCK.posting_date_num ) /

, FORMATTED_STOCK AS ( SELECT CASE -- store condition WHEN LEN(subject_cod) > 3 THEN country_cod || ''|| primary_company ||'' || subject_cod ELSE primary_wh END AS location_id , country_cod || '' || primary_company || '' || article_cod || '_' || size_des AS sku_id -- SITE QUANTITY: 'W_CL1000', 'W_CL2001', 'S_CL1000' (W_CL2001 is negative) , CASE WHEN stock_class IN ('W_CL1000', 'W_CL2001', 'S_CL1000', 'TECNICA') THEN stock_qty ELSE 0 END AS site_qty -- TRANSIT QUANTITY: 'T_CL1000', 'F_CL1000', 'S_CL2000' ('S_CL2000' is set as transit quantity to the subject_cod) , CASE WHEN stock_class IN ('T_CL1000', 'F_CL1000', 'S_CL2000') THEN stock_qty ELSE 0 END AS transit_qty -- RESERVED QUANTITY: 'W_CL2001' is multiplied by -1 and set as the reserved quantity in the deafult warehouse WH1 -- since its the dual of the S_CL2000 (aggregated by wh) , CASE WHEN stock_class = 'W_CL2001' THEN stock_qty * (-1) ELSE 0 END AS reserved_qty , primary_wh AS source_location_id , TO_TIMESTAMP(DATE(posting_dat_num,'yyyymmdd')) as status_date , 0 AS avoid_replenishment , stock_class FROM TOTAL_STOCK )

-- Final grouping SELECT location_id -- here we sum the site_qty, i.e. from the total stock (W_CL1000) we remove the "LOCAZIONE TECNICA" and W_CL2001 stock -- in order to have the "available stock for replenishment" , SUM(site_qty) AS site_qty , sku_id , source_location_id , status_date , SUM(transit_qty) AS transit_qty , 0 AS avoid_replenishment

, SUM(reserved_qty) AS reserved_qty

FROM FORMATTED_STOCK GROUP BY ALL -- may happen that W_CL1000 + W_CL2001 (negative) - LOCAZIONE TECNICA = 0 in the warehouse HAVING NOT( SUM(site_qty) = 0 AND SUM(transit_qty) = 0 AND SUM(reserved_qty) = 0) $$ ;