Skip to content

Documentation: Daily Store Replenishment Transactions

Overview and Metadata

  • Purpose: This query processes various types of inventory movements (sales, returns, transfers) to provide a standardized view of stock transactions relevant for store replenishment. It harmonizes data from multiple operational systems to support inventory management and forecasting.
  • Data Refresh: Daily (based on START_DATE and END_DATE parameters).
  • Project Context: This query is a critical component of the ongoing Retail Store Replenishment Pilot Project for Italian stores.
  • Source Tables:
    • DW_LOADTARGET.DWH.ONEBEAT_COMPANY_COD_MAP
    • DW_LOADTARGET.DWH.TBDWFT_SALES
    • DW_LOADTARGET.DWH.TBDWMD_REASON
    • DW_LOADTARGET.DWH.TBDWMD_ARTICLE
    • DW_LOADTARGET.DWH.VWDWFT_RABT_MOV_NO_PACK
    • DW_LOADTARGET.DWH.VWDWFT_RABT_MOV_PACK_BY_SIZE
    • DW_LOADTARGET.DWH.WHS_ENABLED_ONEBEAT
  • Output: This query is intended to populate a table or create a reusable view, likely used by downstream systems for inventory analysis, reporting, and as input for replenishment algorithms.

Data Flow Diagram

flowchart LR
    subgraph Source Tables
        subgraph Sourcing
        B[TBDWFT_SALES]:::source
        E[VWDWFT_RABT_MOV_NO_PACK]:::source
        G[VWDWFT_RABT_MOV_PACK_BY_SIZE]:::source
        end
        subgraph Filtering
        A[ONEBEAT_COMPANY_RECOD]:::source
        C[TBDWMD_REASON]:::source
        D[TBDWMD_ARTICLE]:::source
        F[WHS_ENABLED_ONEBEAT]:::source
        end
    end

    subgraph CTEs
        H[FILTERED_COMPANY_CODS]:::cte
        I[SALES_RETURNS]:::cte
        J[TRANSACTIONS_ALL]:::cte
        K[FORMATTED_TRANSACTIONS]:::cte
        L[WHS_TO_STORE]:::cte
        M[STORE_TO_WHS]:::cte
        N[STORE_TO_STORE]:::cte
        O[DSS_STORE_TO_STORE]:::cte
        P[EXT_STORE]:::cte
        Q[EXT_WHS]:::cte
        R[STOCKIST]:::cte
        S[TRANSACTIONS_FINAL]:::cte
    end

    A -- :COMPANY --> H
    B --> I
    C -.-> I
    C -.-> J
    D -.-> I
    D -.-> J
    E --> J
    F -.-> J
    G --> J

    H -.-> I
    H -.-> J

    J -- formats --> K
    K --> L
    K --> M
    K --> N
    K --> O
    K --> P
    K --> Q
    K --> R

    I --> S
    L --> S
    M --> S
    N --> S
    O --> S
    P --> S
    Q --> S
    R --> S

    S -- sign + grouping --> U[Final Output Table/View]:::output

Executive Summary & Business Logic

Business Goal: The primary objective of this query is to provide a comprehensive and accurate record of all relevant inventory movements, including sales, returns, and inter-location transfers. This data is essential for enabling precise store replenishment calculations, reducing stockouts, minimizing overstock, and ultimately optimizing product availability in stores.

Key Business Rules: - Transaction Normalization: Quantities and sales values are consistently adjusted to reflect the true flow (inbound/outbound) of goods for each transaction type. For returns, quantities and values are inverted. - Pilot Scope: All data processing and output are currently strictly limited to transactions originating from and destined for Italian (ITA) stores. - Article Eligibility: Only articles marked for "selling" ( business_cod <> 'C') and from recent collections (sales_collection_year_num >= '2023') are included, excluding discontinued or non-sellable items. - Sales Channel Exclusion: Specific sales channels (e.g., channel_cod = '40') are excluded from sales calculations.

Data Flow Logic

FILTERED_COMPANY_CODS

  • Purpose: Filters company codes based on a specified COMPANY parameter, to process data for a particular retail entity.
  • Inputs: DW_LOADTARGET.DWH.ONEBEAT_COMPANY_COD_MAP
  • Main Logic: Selects all columns from the ONEBEAT_COMPANY_COD_MAP table where company_name matches the COMPANY input parameter.
  • Assumptions: Assumes the COMPANY parameter will always correspond to a valid company_name in the mapping table.

SALES_RETURNS

  • Purpose: Identifies and processes sales and return transactions, standardizing their quantities, values, and defining source/target locations.
  • Inputs: DW_LOADTARGET.DWH.TBDWFT_SALES, DW_LOADTARGET.DWH.TBDWMD_REASON, DW_LOADTARGET.DWH.TBDWMD_ARTICLE, FILTERED_COMPANY_CODS
  • Main Logic:
    • Joins sales data with reason, article, and filtered company code tables.
    • Filters transactions within a specified START_DATE and END_DATE and specifically for country_cod = 'ITA'.
    • Excludes articles not meant for selling or from older collections.
    • Excludes a specific sales channel (channel_cod = '40').
    • Aggregates sales data, determining if a transaction is a return based on rabh_class_cod = 'CL0004' or specific inverted_trs logic.
    • Standardizes quantity and sale_price to represent outbound for sales and inbound for returns.
    • Defines source_location_id as 'Client' for sales and event_location_id for returns, and vice-versa for target_location_id.
  • Assumptions:
    • rabh_class_cod = 'CL0004' reliably identifies return transactions.
    • ARRAY_MIN(inverted_trs) = 1 correctly flags specific groups of special returns.
    • The event_location_id logic correctly identifies the relevant store for marketplace sales.

TRANSACTIONS_ALL

  • Purpose: Consolidates and pre-processes all non-sales/return inventory movements, distinguishing between packed and non-packed goods.
  • Inputs: DW_LOADTARGET.DWH.VWDWFT_RABT_MOV_NO_PACK, DW_LOADTARGET.DWH.VWDWFT_RABT_MOV_PACK_BY_SIZE, DW_LOADTARGET.DWH.TBDWMD_ARTICLE, DW_LOADTARGET.DWH.TBDWMD_REASON, DW_LOADTARGET.DWH.WHS_ENABLED_ONEBEAT, FILTERED_COMPANY_CODS
  • Main Logic:
    • Uses a UNION ALL to combine data from VWDWFT_RABT_MOV_NO_PACK and VWDWFT_RABT_MOV_PACK_BY_SIZE.
    • Joins with article, reason, warehouse enablement, and filtered company code tables.
    • Applies similar date, country, and article filters as SALES_RETURNS (country_cod = 'ITA', business_cod <> 'C', SALES_COLLECTION_YEAR_NUM >= '2023').
    • Filters out specific inter-store compensation movements (class_cod IN ('CL0121', 'CL0021') AND (reason_cod LIKE 'LD407%' OR reason_cod LIKE '%409%')).
    • Generates a unique id for packed items using ROW_NUMBER().
  • Assumptions: The NO_PACK and PACK_BY_SIZE views provide distinct, non-overlapping transaction types for non-sales movements.

FORMATTED_TRANSACTIONS

  • Purpose: Standardizes the format of location IDs and other core transaction attributes from TRANSACTIONS_ALL.
  • Inputs: TRANSACTIONS_ALL
  • Main Logic:
    • Formats sku_id by concatenating country, primary company, article, and measure codes.
    • Applies conditional logic to store_cod and dss_store_cod to resolve them to either a specific store ID or the primary_wh (primary warehouse) if the code length is not typical for a store.
    • Filters out records where whs_cod is null and whs_flag is 'Y', or records where whs_flag is 'N'.
  • Assumptions: A store code length greater than 3 characters signifies a specific store, otherwise it refers to the primary warehouse.

WHS_TO_STORE

  • Purpose: Aggregates and formats inventory transfers from Warehouse to Store.
  • Inputs: FORMATTED_TRANSACTIONS
  • Main Logic:
    • Filters for class_cod values CL0113 (sending from WHS) and CL0020 (receiving at store).
    • Adjusts quantity and sale_price signs based on class_cod to represent a net movement.
    • Sets source_location_id and target_location_id based on the class_cod.
    • Determines type as 'out' for sending and 'in' for receiving.
    • Aggregates quantity, reason_cod, class_cod, and inverted_trs arrays.
  • Assumptions: CL0113 always represents an 'out' movement from a warehouse, and CL0020 an 'in' movement to a store.

STORE_TO_WHS

  • Purpose: Aggregates and formats inventory transfers from Store to Warehouse.
  • Inputs: FORMATTED_TRANSACTIONS
  • Main Logic:
    • Filters for class_cod values CL0120 (sending from store) and CL0013 (receiving at WHS).
    • Similar logic to WHS_TO_STORE for quantity/price signs, source/target locations, and type ('out' from store, 'in' to WHS).
  • Assumptions: CL0120 always represents an 'out' movement from a store, and CL0013 an 'in' movement to a warehouse.

STORE_TO_STORE

  • Purpose: Aggregates and formats inventory transfers between different stores, excluding specific DSS-related movements.
  • Inputs: FORMATTED_TRANSACTIONS
  • Main Logic:
    • Filters for class_cod values CL0121 (sending from store) and CL0021 (receiving at store), explicitly excluding reason_cod LIKE '%407%' (DSS sales).
    • Adjusts quantity/price signs, source/target locations, and type based on class_cod.
  • Assumptions: CL0121 represents an 'out' movement from a store, and CL0021 an 'in' movement to a store for non-DSS transfers.

DSS_STORE_TO_STORE

  • Purpose: Aggregates and formats Digital Sales System (DSS) related transfers between stores, classifying them as sales or returns.
  • Inputs: FORMATTED_TRANSACTIONS
  • Main Logic:
    • Filters for class_cod values CL0121 (sending from store for DSS) and CL0021 (receiving at store for DSS), specifically including reason_cod LIKE 'UD407%'.
    • Treats CL0121 as a 'sale' (from store to client) and CL0021 as a 'return' (from client to store).
  • Assumptions: reason_cod LIKE 'UD407%' reliably identifies DSS sales/returns, differentiating them from regular store-to-store transfers.

EXT_STORE

  • Purpose: Aggregates and formats inventory movements between external subjects (like suppliers) and stores.
  • Inputs: FORMATTED_TRANSACTIONS
  • Main Logic:
    • Filters for class_cod values CL0002 (inbound from external) and CL0102 (outbound to external), and store_type_cod = 'S' (store).
    • Sets 'Supplier' as the source_location_id for inbound and target_location_id for outbound.
    • Determines type as 'in' or 'out'.
  • Assumptions: CL0002 and CL0102 accurately represent external-to-store movements.

EXT_WHS

  • Purpose: Aggregates and formats inventory movements between external subjects (like suppliers) and warehouses.
  • Inputs: FORMATTED_TRANSACTIONS
  • Main Logic:
    • Filters for class_cod values CL0002 (inbound from external) and CL0102 (outbound to external), and store_type_cod = 'W' (warehouse).
    • Similar logic to EXT_STORE for location and type determination, but for warehouse movements.
  • Assumptions: CL0002 and CL0102 accurately represent external-to-warehouse movements.

STOCKIST

  • Purpose: Aggregates and formats inventory movements specifically to stockists.
  • Inputs: FORMATTED_TRANSACTIONS
  • Main Logic:
    • Filters for class_cod = 'CL0131'.
    • Assumes these are always outbound movements, thus inverting quantity and sale price.
    • Sets target_location_id as 'Stockists' and type as 'out'.
  • Assumptions: CL0131 exclusively represents outbound movements to stockists.

TRANSACTIONS_FINAL

  • Purpose: Unifies all classified and formatted transaction types into a single dataset, applies final location formatting, and filters out internal warehouse-to-warehouse movements.
  • Inputs: WHS_TO_STORE, STORE_TO_WHS, STORE_TO_STORE, DSS_STORE_TO_STORE, EXT_STORE, EXT_WHS, STOCKIST, SALES_RETURNS
  • Main Logic:
    • Combines all previous transaction-specific CTEs using UNION ALL.
    • Formats source_location_id and target_location_id: replaces any location ending with '5%' (indicating a non-Italian franchise) with 'Store_No_Italy'.
    • Converts transaction_date to a TIMESTAMP format.
    • Filters out records where quantity is zero.
    • Excludes movements where both source_location_id and target_location_id are identified as the primary_wh.
  • Assumptions:
    • The SPLIT_PART logic reliably identifies non-Italian franchise stores.
    • Warehouse-to-warehouse movements are not relevant for the replenishment model.

FIX_DELETION_TRANSACTIONS

  • Purpose: Corrects the sign of quantities and sale prices for any remaining negative transactions (e.g., deletions not previously accounted for) and inverts their source/target locations and types.
  • Inputs: TRANSACTIONS_FINAL
  • Main Logic:
    • Checks if quantity is negative.
    • If negative, quantity and sale_price are multiplied by -1.
    • source_location_id and target_location_id are swapped if quantity was negative.
    • type is inverted (e.g., 'in' becomes 'out', 'sale' becomes 'return') if quantity was negative.
  • Assumptions: Negative quantities at this stage represent 'deletion' or 'reversal' type transactions that need to be re-interpreted as positive movements from the new source to the new target.

Execution Parameters and Pilot Conditions

The query utilizes placeholder parameters, START_DATE and END_DATE, to define the processing window for transactions. Crucially, the query is currently filtered to process data exclusively for Italian stores (country_cod = 'ITA'). This is a pilot phase condition and will likely be modified or removed when the project scales to other countries. The article filters (ARTICLE.business_cod <> 'C' and ARTICLE.sales_collection_year_num >= '2023') are also specific conditions for the pilot.

Final Columns Data Dictionary

Column Name Data Type Description Notes
id VARCHAR Unique identifier for the transaction record. Derived from mov_progr_cod, potentially with row numbers for packed items.
quantity NUMERIC The net quantity of articles moved in the transaction. Positive for inbound, negative for outbound, adjusted for returns and reversals.
sale_price NUMERIC The net sales value (retail amount) of the articles moved. Represents the monetary value associated with the quantity, adjusted similarly to quantity.
sku_id VARCHAR Unique identifier for the Stock Keeping Unit (SKU). Concatenation of country, primary company, article, and measure codes.
source_location_id VARCHAR The origin of the inventory movement. Can be a specific store, warehouse, 'Client', 'Supplier', 'Stockists', or 'Store_No_Italy'.
target_location_id VARCHAR The destination of the inventory movement. Can be a specific store, warehouse, 'Client', 'Supplier', 'Stockists', or 'Store_No_Italy'.
transaction_date TIMESTAMP The date and time when the transaction was posted. Converted from yyyymmdd numeric format.
type VARCHAR Classification of the transaction movement. Examples: 'sale', 'return', 'in', 'out'.
currency VARCHAR The currency of the sale_price. Determined by company_cod (e.g., 'CZK' for '185C', 'EUR' otherwise).

Source Code

WITH FILTERED_COMPANY_CODS AS (
    SELECT * FROM DW_LOADTARGET.DWH.ONEBEAT_COMPANY_COD_MAP WHERE company_name = COMPANY
)

, SALES_RETURNS AS (
    SELECT
        id
        -- when the transaction ia a "reso" (identified whne rabh_class_cod = 'CL0004') or
        -- when the transaction is a group of ONLY special returns.
        , CASE WHEN (flag_reso = 'Y' OR ARRAY_MIN(inverted_trs) = 1) THEN sales_qty ELSE sales_qty*(-1) END AS quantity
        , CASE WHEN (flag_reso = 'Y' OR ARRAY_MIN(inverted_trs) = 1) THEN sales_value ELSE sales_value*(-1) END AS sale_price
        , sku_id
        , CASE WHEN (flag_reso = 'Y' OR ARRAY_MIN(inverted_trs) = 1) THEN 'Client' ELSE event_location_id END AS source_location_id
        , CASE WHEN (flag_reso = 'Y' OR ARRAY_MIN(inverted_trs) = 1) THEN event_location_id ELSE 'Client' END AS target_location_id
        , transaction_date
        , CASE WHEN (flag_reso = 'Y' OR ARRAY_MIN(inverted_trs) = 1) THEN 'return' ELSE 'sale' END AS type
        , currency

        -- UTILS
        , 'SALES_RETURNS' AS source, agg_qtys, reason_cods, [''] AS class_cods, inverted_trs
        , primary_company, primary_wh
    FROM (
        SELECT
            MAX(SALES.mov_progr_cod)::VARCHAR AS id
            , SALES.posting_dat_num AS transaction_date
            -- when the sale is in the channel_cod = 60 -> marketplace, so if the a store is present means tha it is managing the article
            , SALES.country_cod || '_' || RECOD.primary_company || '_' || 
                    CASE WHEN (SALES.channel_cod = '60' AND SALES.dss_store_cod LIKE '7%') THEN  SALES.dss_store_cod ELSE SALES.store_cod 
                END as event_location_id
            , SALES.country_cod || '_' || RECOD.primary_company || '_' || SALES.article_cod || '_' || SALES.measure_cod as sku_id
            -- flag to identify if the transaction is a return or a sale: CL0004 always identify returns
            , CASE WHEN REASON.rabh_class_cod = 'CL0004' THEN 'Y' ELSE 'N' END AS flag_reso
            , SUM(SALES.total_qty_num) AS sales_qty
            , SUM(SALES.net_amount_val) AS sales_value
            , CASE WHEN SALES.company_cod = '185C' THEN 'CZK' ELSE 'EUR' END AS currency

            -- UTILS
            , channel_cod, ARRAY_AGG(SALES.total_qty_num) AS agg_qtys, ARRAY_AGG(SALES.reason_cod) as reason_cods
            -- identify load INTO the system transactions:
            , ARRAY_AGG(CASE WHEN REASON.sign_cod = '+' THEN 1 ELSE 0 END) AS inverted_trs
            -- primary company cod:
            , RECOD.primary_company as primary_company, RECOD.primary_wh as primary_wh
        FROM  DW_LOADTARGET.DWH.TBDWFT_SALES SALES
        INNER JOIN DW_LOADTARGET.DWH.TBDWMD_REASON REASON   -- we need reason table to identify returns
            ON REASON.reason_cod=SALES.reason_cod
            and REASON.country_cod=SALES.country_cod
        INNER JOIN DW_LOADTARGET.DWH.TBDWMD_ARTICLE ARTICLE
            ON ARTICLE.country_cod = SALES.country_cod
            AND ARTICLE.article_cod = SALES.article_cod
        INNER JOIN DW_LOADTARGET.DWH.FILTERED_COMPANY_CODS RECOD
            ON RECOD.company_cod = SALES.company_cod
        WHERE 1=1
            AND SALES.posting_dat_num BETWEEN START_DATE AND END_DATE -- delta days transactions
            -- PILOT CONDITIONS
            AND SALES.country_cod = 'ITA'
            -- articles conditions
            AND ARTICLE.business_cod <> 'C' -- only selling articles
            AND ARTICLE.sales_collection_year_num >= '2023'
            -- exclude dss sales
            AND SALES.channel_cod <> '40'
        GROUP BY ALL
    )
)

-- transactions CTEs "NO_PACK"
, TRANSACTIONS_ALL AS (
    SELECT
        -- wharehouse flags
        WHS.whs_cod, CASE WHEN (LEN(NO_PACK.store_cod)=3 OR LEN(NO_PACK.dss_store_cod)=3) THEN 'Y' ELSE 'N' END AS whs_flag

        -- FILTERED_company_cods
        , RECOD.primary_company, RECOD.primary_wh

        , NO_PACK.mov_progr_cod::VARCHAR as id
        , NO_PACK.total_qty_num, NO_PACK.retail_amount_val, NO_PACK.country_cod, NO_PACK.company_cod
        , NO_PACK.article_cod, NO_PACK.measure_cod, NO_PACK.store_type_cod, NO_PACK.class_cod
        , NO_PACK.store_cod, NO_PACK.dss_store_cod, NO_PACK.posting_dat_num
        , REASON.sign_cod AS sign_cod, NO_PACK.reason_cod

    FROM DW_LOADTARGET.DWH.VWDWFT_RABT_MOV_NO_PACK NO_PACK
    INNER JOIN DW_LOADTARGET.DWH.TBDWMD_ARTICLE ARTICLE
        ON ARTICLE.country_cod = NO_PACK.country_cod
        AND ARTICLE.article_cod = NO_PACK.article_cod
    INNER JOIN DW_LOADTARGET.DWH.TBDWMD_REASON REASON   -- we need reason table to identify returns
        ON REASON.reason_cod = NO_PACK.reason_cod
        AND REASON.country_cod = NO_PACK.country_cod
    LEFT JOIN DW_LOADTARGET.DWH.WHS_ENABLED_ONEBEAT WHS
        ON WHS.country_cod = NO_PACK.country_cod
        AND WHS.company_cod = NO_PACK.company_cod
        AND (WHS.whs_cod = NO_PACK.store_cod OR WHS.whs_cod = NO_PACK.dss_store_cod)
    INNER JOIN DW_LOADTARGET.DWH.FILTERED_COMPANY_CODS RECOD
        ON RECOD.company_cod = NO_PACK.company_cod
    WHERE 1=1
        AND NO_PACK.posting_dat_num BETWEEN START_DATE AND END_DATE -- delta days transactions
        -- Pilot conditions
        AND NO_PACK.country_cod = 'ITA'
        AND ARTICLE.business_cod <> 'C' -- only selling articles
        AND ARTICLE.SALES_COLLECTION_YEAR_NUM >= '2023'
        -- we remove all the compensations movements store <-> store resulted from a INSTOREAPP SALE
        -- only the out movement from the store -> "sale" will be present.
        AND NOT (class_cod IN ('CL0121', 'CL0021') AND (NO_PACK.reason_cod LIKE 'LD407%' OR NO_PACK.reason_cod LIKE '%409%'))

    UNION ALL

    SELECT
        -- wharehouse flags
        WHS.whs_cod, CASE WHEN (LEN(PACK.store_cod)=3 OR LEN(PACK.dss_store_cod)=3) THEN 'Y' ELSE 'N' END AS whs_flag
        -- FILTERED_company_cods
        , RECOD.primary_company, RECOD.primary_wh

        , PACK.mov_progr_cod::VARCHAR || ROW_NUMBER() OVER (PARTITION BY PACK.mov_progr_cod ORDER BY PACK.measure_cod) AS id
        , PACK.total_qty_num, PACK.retail_amount_val, PACK.country_cod, PACK.company_cod, PACK.article_cod, PACK.measure_cod, PACK.store_type_cod, PACK.class_cod
        , PACK.store_cod, PACK.dss_store_cod, PACK.posting_dat_num
        , REASON.sign_cod AS sign_cod, PACK.reason_cod

    FROM DW_LOADTARGET.DWH.VWDWFT_RABT_MOV_PACK_BY_SIZE PACK
    INNER JOIN DW_LOADTARGET.DWH.TBDWMD_ARTICLE ARTICLE
        ON ARTICLE.country_cod = PACK.country_cod
        AND ARTICLE.article_cod = PACK.article_cod
    INNER JOIN DW_LOADTARGET.DWH.TBDWMD_REASON REASON   -- we need reason table to identify returns
        ON REASON.reason_cod = PACK.reason_cod
        AND REASON.country_cod = PACK.country_cod
    LEFT JOIN DW_LOADTARGET.DWH.WHS_ENABLED_ONEBEAT WHS
        ON WHS.country_cod = PACK.country_cod
        AND WHS.company_cod = PACK.company_cod
        AND (WHS.whs_cod = PACK.store_cod OR WHS.whs_cod = PACK.dss_store_cod)
    INNER JOIN DW_LOADTARGET.DWH.FILTERED_COMPANY_CODS RECOD
        ON RECOD.company_cod = PACK.company_cod
    WHERE 1=1
        AND PACK.posting_dat_num BETWEEN START_DATE AND END_DATE -- delta days transactions
        -- Pilot conditions
        AND PACK.country_cod = 'ITA'
        -- Articles conditions
        AND ARTICLE.business_cod <> 'C' -- only selling articles
        AND ARTICLE.SALES_COLLECTION_YEAR_NUM >= '2023'
        -- we remove all the (virtual AND physical) movements store <-> store RESULTED FROM a INSTOREAPP SALE
        -- !!! Unnecessary here?
        AND NOT (class_cod IN ('CL0121', 'CL0021') AND (PACK.reason_cod LIKE 'LD407%' OR PACK.reason_cod LIKE '%409%'))
)

-- combine and format the locations of both transactions CTEs
, FORMATTED_TRANSACTIONS AS (
    SELECT
        id
        , total_qty_num AS quantity
        , retail_amount_val as sale_price
        , country_cod || '_' || primary_company || '_' || article_cod || '_' || measure_cod as sku_id
        , store_type_cod
        , CASE 
            WHEN LEN(store_cod) > 3 THEN country_cod || '_' || primary_company || '_' || store_cod
            ELSE primary_wh END AS store_cod
        , CASE 
            WHEN LEN(dss_store_cod) > 3 THEN country_cod || '_' || primary_company || '_' || dss_store_cod
            ELSE primary_wh END AS dss_store_cod
        , '' AS type
        , posting_dat_num AS transaction_date
        , CASE WHEN company_cod = '185C' THEN 'CZK' ELSE 'EUR' END AS currency
        -- UTILS
        , class_cod, country_cod, company_cod, reason_cod, sign_cod, primary_company, primary_wh
    FROM TRANSACTIONS_ALL
    WHERE (whs_cod IS NOT NULL OR whs_flag = 'N')
)

-- WHS --> STORE
-- here we format the transactions from WAREHOUSE to STORE (sending (CL0113) and recieving (CL0020))
, WHS_TO_STORE AS (
    SELECT
        MAX(id) AS id
        , SUM(CASE WHEN class_cod = 'CL0113' THEN quantity * (-1) ELSE quantity END) AS quantity
        , SUM(CASE WHEN class_cod = 'CL0113' THEN sale_price * (-1) ELSE sale_price END) AS sale_price
        , sku_id
        , CASE WHEN class_cod = 'CL0113' THEN store_cod ELSE dss_store_cod END AS source_location_id
        , CASE WHEN class_cod = 'CL0113' THEN dss_store_cod ELSE store_cod END AS target_location_id
        , transaction_date
        , CASE WHEN class_cod = 'CL0113' THEN 'out' ELSE 'in' END AS type
        , currency

        -- UTILS
        , 'WHS_TO_STORE' AS source
        , ARRAY_AGG(quantity) AS agg_qtys
        , ARRAY_AGG(reason_cod) AS reason_cods
        , ARRAY_AGG(class_cod) AS class_cods
        -- here we check if the transaction is inverted: if the class_cod identify an unloading, but the the record is a loading in the system, 
        -- or viceversa if the class_cod identify a loading, but the record is an unloading in the system
        , ARRAY_AGG(CASE WHEN (class_cod='CL0113' AND sign_cod='+') OR (class_cod='CL0020' AND sign_cod='-') THEN 1 ELSE 0 END) AS inverted_trs
        , primary_company, primary_wh
    FROM FORMATTED_TRANSACTIONS
    WHERE class_cod IN ('CL0113', 'CL0020')
    GROUP BY ALL
)

-- WHS <-- STORE
-- here we format the transactions from STORE to WAREHOUSE (sending (CL0120) and recieving (CL0013))
, STORE_TO_WHS AS (
    SELECT
        MAX(id) AS id
        , SUM(CASE WHEN class_cod = 'CL0120' THEN quantity * (-1) ELSE quantity END) AS quantity
        , SUM(CASE WHEN class_cod = 'CL0120' THEN sale_price * (-1) ELSE sale_price END) AS sale_price
        , sku_id
        , CASE WHEN class_cod = 'CL0120' THEN store_cod ELSE dss_store_cod END AS source_location_id
        , CASE WHEN class_cod = 'CL0120' THEN dss_store_cod ELSE store_cod END AS target_location_id
        , transaction_date
        , CASE WHEN class_cod = 'CL0120' THEN 'out' ELSE 'in' END AS type
        , currency

        -- UTILS
        , 'STORE_TO_WHS' AS source
        , ARRAY_AGG(quantity) AS agg_qtys
        , ARRAY_AGG(reason_cod) AS reason_cods
        , ARRAY_AGG(class_cod) AS class_cods
        -- here we check if the transaction is inverted: if the class_cod identify an unloading, but the the record is a loading in the system, 
        -- or viceversa if the class_cod identify a loading, but the record is an unloading off the system
        , ARRAY_AGG(CASE WHEN (class_cod = 'CL0120' AND sign_cod='+') OR (class_cod = 'CL0013' AND sign_cod='-') THEN 1 ELSE 0 END) AS inverted_trs
        , primary_company, primary_wh
    FROM FORMATTED_TRANSACTIONS
    WHERE class_cod IN ('CL0120', 'CL0013')
    GROUP BY ALL
)

-- STORE <--> STORE
-- here we format the transactions from STORE to STORE (sending (CL0121) and recieving (CL0021))
, STORE_TO_STORE AS (
    SELECT
        MAX(id) AS id
        , SUM(CASE WHEN class_cod = 'CL0121' THEN quantity * (-1) ELSE quantity END) AS quantity
        , SUM(CASE WHEN class_cod = 'CL0121' THEN sale_price * (-1) ELSE sale_price END) AS sale_price
        , sku_id
        , CASE WHEN class_cod = 'CL0121' THEN store_cod ELSE dss_store_cod END AS source_location_id
        , CASE WHEN class_cod = 'CL0121' THEN dss_store_cod ELSE store_cod END AS target_location_id
        , transaction_date
        , CASE WHEN class_cod = 'CL0121' THEN 'out' ELSE 'in' END AS type
        , currency

        -- UTILS
        , 'STORE_TO_STORE' AS source
        , ARRAY_AGG(quantity) AS agg_qtys
        , ARRAY_AGG(reason_cod) AS reason_cods
        , ARRAY_AGG(class_cod) AS class_cods
        -- here we check if the transaction is inverted: if the class_cod identify an unloading, but the the record is a loading in the system, 
        -- or viceversa if the class_cod identify a loading, but the record is an unloading off the system
        , ARRAY_AGG(CASE WHEN (class_cod = 'CL0121' AND sign_cod='+') OR (class_cod = 'CL0021' AND sign_cod='-') THEN 1 ELSE 0 END) AS inverted_trs
        , primary_company, primary_wh
    FROM FORMATTED_TRANSACTIONS
    WHERE class_cod IN ('CL0121', 'CL0021') AND reason_cod NOT LIKE '%407%'
    GROUP BY ALL
)

-- DSS: STORE <--> STORE transactions => sale/return transactions
-- here we format the transactions related to a dss sale/return
, DSS_STORE_TO_STORE AS (
    SELECT
        MAX(id) AS id
        , SUM(CASE WHEN class_cod = 'CL0121' THEN quantity * (-1) ELSE quantity END) AS quantity
        , SUM(CASE WHEN class_cod = 'CL0121' THEN sale_price * (-1) ELSE sale_price END) AS sale_price
        , sku_id
        , CASE WHEN class_cod = 'CL0121' THEN store_cod ELSE 'Client' END AS source_location_id
        , CASE WHEN class_cod = 'CL0121' THEN 'Client' ELSE store_cod END AS target_location_id
        , transaction_date
        , CASE WHEN class_cod = 'CL0121' THEN 'sale' ELSE 'return' END AS type
        , currency

        -- UTILS
        , 'DSS_STORE_TO_STORE' AS source
        , ARRAY_AGG(quantity) AS agg_qtys
        , ARRAY_AGG(reason_cod) AS reason_cods
        , ARRAY_AGG(class_cod) AS class_cods
        -- here we check if the transaction is inverted: if the class_cod identify an unloading, but the the record is a loading in the system, 
        -- or viceversa if the class_cod identify a loading, but the record is an unloading off the system
        , ARRAY_AGG(CASE WHEN (class_cod = 'CL0121' AND sign_cod='+') OR (class_cod = 'CL0021' AND sign_cod='-') THEN 1 ELSE 0 END) AS inverted_trs
        , primary_company, primary_wh
    FROM FORMATTED_TRANSACTIONS
    WHERE class_cod IN ('CL0121', 'CL0021') AND reason_cod LIKE 'UD407%'
    GROUP BY ALL
)

-- EXT <--> STORE
-- here we format the transactions from EXTERNAL subject (Supplier) to STORE (sending (CL0002) and recieving (CL0102))
, EXT_STORE AS (
    SELECT
        MAX(id) AS id
        , SUM(CASE WHEN class_cod = 'CL0002' THEN quantity ELSE quantity * (-1) END) AS quantity
        , SUM(CASE WHEN class_cod = 'CL0002' THEN sale_price ELSE sale_price * (-1) END) AS sale_price
        , sku_id
        , CASE WHEN class_cod = 'CL0002' THEN 'Supplier' ELSE store_cod END AS source_location_id
        , CASE WHEN class_cod = 'CL0002' THEN store_cod ELSE 'Supplier' END AS target_location_id
        , transaction_date
        , CASE WHEN class_cod = 'CL0002' THEN 'in' ELSE 'out' END AS type
        , currency

        -- UTILS
        , 'EXT_STORE' AS source
        , ARRAY_AGG(quantity) AS agg_qtys
        , ARRAY_AGG(reason_cod) AS reason_cods
        , ARRAY_AGG(class_cod) AS class_cods
        -- here we check if the transaction is inverted: if the class_cod identify an unloading, but the the record is a loading in the system, 
        -- or viceversa if the class_cod identify a loading, but the record is an unloading off the system
        , ARRAY_AGG(CASE WHEN (class_cod = 'CL0002' AND sign_cod='-') OR (class_cod = 'CL0102' AND sign_cod='+') THEN 1 ELSE 0 END) AS inverted_trs
        , primary_company, primary_wh
    FROM FORMATTED_TRANSACTIONS
    WHERE class_cod IN ('CL0002', 'CL0102') AND store_type_cod = 'S'
    GROUP BY ALL
)

-- EXT <--> WHS
-- here we format the transactions from STORE to STORE (sending (CL0002) and recieving (CL0102))
, EXT_WHS AS (
    SELECT
        MAX(id) AS id
        , SUM(CASE WHEN class_cod = 'CL0002' THEN quantity ELSE quantity * (-1) END) AS quantity
        , SUM(CASE WHEN class_cod = 'CL0002' THEN sale_price ELSE sale_price * (-1) END) AS sale_price
        , sku_id
        , CASE WHEN class_cod = 'CL0002' THEN 'Supplier' ELSE store_cod END AS source_location_id
        , CASE WHEN class_cod = 'CL0002' THEN store_cod ELSE 'Supplier' END AS target_location_id
        , transaction_date
        , CASE WHEN class_cod = 'CL0002' THEN 'in' ELSE 'out' END AS type

        , currency
        -- UTILS
        , 'EXT_WHS' AS source
        , ARRAY_AGG(quantity) AS agg_qtys
        , ARRAY_AGG(reason_cod) AS reason_cods
        , ARRAY_AGG(class_cod) AS class_cods
        -- here we check if the transaction is inverted: if the class_cod identify an unloading, but the the record is a loading in the system, 
        -- or viceversa if the class_cod identify a loading, but the record is an unloading off the system
        , ARRAY_AGG(CASE WHEN (class_cod = 'CL0002' AND sign_cod='-') OR (class_cod = 'CL0102' AND sign_cod='+') THEN 1 ELSE 0 END) AS inverted_trs
        , primary_company, primary_wh
    FROM FORMATTED_TRANSACTIONS
    WHERE class_cod IN ('CL0002', 'CL0102') AND store_type_cod = 'W'
    GROUP BY ALL
)

-- STOCKISTs
-- here we format the transactions to STOCKISTs (only sending (CL0131))
, STOCKIST AS (
    SELECT
        MAX(id) AS id
        , SUM(quantity * (-1)) AS quantity
        , SUM(sale_price * (-1)) AS sale_price
        , sku_id
        , store_cod AS source_location_id
        , 'Stockists' AS target_location_id
        , transaction_date
        , 'out' AS type

        , currency
        -- UTILS
        , 'STOCKIST' AS source
        , ARRAY_AGG(quantity) AS agg_qtys
        , ARRAY_AGG(reason_cod) AS reason_cods
        , ARRAY_AGG(class_cod) AS class_cods
        -- here we check if the transaction is inverted: if the class_cod identify an unloading, but the the record is a loading in the system, 
        -- or viceversa if the class_cod identify a loading, but the record is an unloading off the system
        , ARRAY_AGG(CASE WHEN sign_cod='-' THEN 1 ELSE 0 END) AS inverted_trs
        , primary_company, primary_wh
    FROM FORMATTED_TRANSACTIONS
    WHERE class_cod = 'CL0131'
    GROUP BY ALL
)

, TRANSACTIONS_FINAL AS (
    -- final select of all transactions
    SELECT
        TRS.id
        , TRS.quantity
        , TRS.sale_price
        , TRS.sku_id
        -- FORMAT the source and target location IDs to be 'Franchise' **removed franchisee diff** or 'Store_No_Italy' when applicable
        , CASE WHEN SPLIT_PART(TRS.source_location_id,'_',-1) LIKE '5%' THEN 'Store_No_Italy'
            ELSE TRS.source_location_id END AS source_location_id
        , CASE WHEN SPLIT_PART(TRS.target_location_id,'_',-1) LIKE '5%' THEN 'Store_No_Italy'
            ELSE TRS.target_location_id END AS target_location_id
        , TO_TIMESTAMP(DATE(TRS.transaction_date,'yyyymmdd')) AS transaction_date
        , TRS.type
        , TRS.currency

        -- UTILS
        , TRS.source
        , TRS.agg_qtys
        , TRS.reason_cods
        , TRS.class_cods
        , TRS.inverted_trs
        , primary_company, primary_wh
    FROM
    (
        SELECT * FROM WHS_TO_STORE -- WHS --> STORE
        UNION ALL
        SELECT * FROM STORE_TO_WHS -- WHS <-- STORE
        UNION ALL
        SELECT * FROM STORE_TO_STORE -- STORE <--> STORE
        UNION ALL
        SELECT * FROM DSS_STORE_TO_STORE -- DSS STORE <--> STORE
        UNION ALL
        SELECT * FROM EXT_STORE -- EXT <--> STORE
        UNION ALL
        SELECT * FROM EXT_WHS -- EXT <--> WHS
        UNION ALL
        SELECT * FROM STOCKIST -- STOCKISTs
        UNION ALL
        SELECT * FROM SALES_RETURNS -- STORE <-> CLIENT
    ) AS TRS
    WHERE quantity <> 0
        -- we filter out all the movements warehouse to warehouse since we we assumed all be part of the cluster warehouse
        AND NOT (source_location_id = primary_wh AND target_location_id = primary_wh)
)

-- here we change the sign in case of records with negative quanities
-- we try to catch deletion records (reason_cod LIKE '%D') related to others that may appear in different days (so they don't cancel out)
--(we didn't insert the case for eventual sale/return mismatch as we think we covered every case)
, FIX_DELETION_TRANSACTIONS AS (
    SELECT
        id
        , CASE WHEN quantity < 0 THEN quantity * (-1) ELSE quantity END AS quantity
        , CASE WHEN quantity < 0 THEN sale_price * (-1) ELSE sale_price END AS sale_price
        , sku_id
        , CASE WHEN quantity < 0 THEN target_location_id ELSE source_location_id END AS source_location_id
        , CASE WHEN quantity < 0 THEN source_location_id ELSE target_location_id END AS target_location_id
        , transaction_date
        , CASE
        WHEN quantity < 0 THEN
            CASE 
                WHEN type IN ('in', 'out') THEN ARRAY_REMOVE(['in', 'out'], type::VARIANT)[0]
                WHEN type IN ('sale', 'return') THEN ARRAY_REMOVE(['sale', 'return'], type::VARIANT)[0]
            END
        ELSE type
        END AS type
        , currency
    FROM TRANSACTIONS_FINAL
)

SELECT
    -- final grouping: as we "revert" records some transactions might end up as same type of transactions already present before
    -- i.e. with the same sku_id, same target, same source, same date 
    MAX(id) as id
    , SUM(quantity) AS quantity
    , SUM(sale_price) AS sale_price
    , sku_id
    , source_location_id
    , target_location_id
    , transaction_date
    , type
    , currency
FROM FIX_DELETION_TRANSACTIONS
GROUP BY ALL