CONV_RATE_NEW

Executive Summary & Metadata

• Description:
  This stored procedure performs a full recalculation and validation of Conversion Rate (CR) data at hourly level for retail stores. It applies a sequence of business quality rules to identify unreliable days or time slots (e.g., invalid people counter status, no exits, no tickets, abnormal variance), flags invalid records with explicit rejection reasons, and loads only validated data into the final Conversion Rate fact table. It also enriches valid records with ticket-channel split metrics and comparability flags versus last year.

• Project Context:
  Retail Conversion Rate recalculation and data quality enforcement (Conversion Rate pilot / data quality framework).

• Execution Mode:
  Scheduled batch execution (typically after loading temporary conversion rate and sales staging tables).

• Return Type:
  NUMBER(38,0) – returns 0 upon successful completion.

• Impact:

• TRUNCATE: Multiple temporary tables in DWH_TMP and staging fact table.
• DELETE: Cleans existing records in TBDWFT_CONV_RATE_NEW for the recalculated date range.
• INSERT: Populates rejected and valid records into TBDWFT_CONV_RATE_NEW.
• UPDATE: Updates comparability flag (CR_FLG_COMP) on final data.

---

Parameters & Inputs

This procedure does not accept external parameters.

Parameter Name	Data Type	Default Value	Description / Business Usage
V_INIZIO	NUMBER	Derived	Minimum posting date number found in temporary conversion rate data; defines recalculation start date.
V_WK_INIZIO	NUMBER	Derived	Minimum posting year-week number found in temporary conversion rate data; defines recalculation start week.

---

Dependencies (Source Data)

Read Tables / Views

• DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE
• Conversion rate raw data at hourly granularity (customer in/out, tickets).

• Used as the primary dataset for validation rules.

• DW_LOADTARGET.DWH.TBDWFT_SALES

• Detailed sales transactions.

• Used to derive ticket counts per hour and channel.

• DW_LOADTARGET.DWH.TBDWMD_STORE_PCOUNTER

• Store people-counter master data.

• Used to validate counter status per store and day.

• DW_LOADTARGET.DWH.TBDWFT_TRANSMISSION_DATA

• Provides first and last sale hour per store/day.

• Used to derive store opening and closing hours.

• CR_GIORNI_COMPARABLE_ACT_SU_LY

• Comparable-day mapping versus last year.
• Used to flag records comparable with prior-year performance.

---

Detailed Logic Breakdown

Initialization

• All working and staging tables are truncated to guarantee a clean recalculation:
• TBDWFT_SALES_CONV_RATE
• TBTMP_STORE_INFO
• TBTMP_STORE_STATS
• TBTMP_STORE_ADV_STATS

• TBTMP_STORE_COMP

• The procedure identifies the earliest posting date and week present in temporary conversion rate data.
  These values define the recalculation window and ensure idempotent reloads.

• Existing records in TBDWFT_CONV_RATE_NEW from that date/week onward are deleted to prevent duplicates.

---

Sales-to-Conversion Alignment

• Sales data is filtered to:
• Only companies present in temporary conversion rate data.
• Only records on or after the recalculation start date/week.
• The result is loaded into TBDWFT_SALES_CONV_RATE, which later supports ticket split calculations.

---

Store Daily Context (TBTMP_STORE_INFO)

For each store/day present in conversion rate data: - Retrieves people-counter status (STATUS_P_C). - Derives opening (V_INIZIO) and closing (V_FINE) hours based on first and last sale hour. - This dataset drives multiple downstream validation rules.

---

Validation Rule 1: Invalid People Counter Status

• Business Rule:
  If the people counter status for a store/day is not valid (A or X), all hourly data for that day is unreliable.

• Action:

• All related hourly rows are inserted into TBDWFT_CONV_RATE_NEW with:
  • CR_FLAG = 'N'
  • REASON_N = 'STATUS PCOUNTER'
• These rows are removed from further processing.

---

Validation Rule 3: Opening Hours Filtering

• Business Rule:
  Only time slots within actual store opening hours are considered valid.

• Action:

• Hourly rows whose TIME_SLOT_COD falls between opening and closing hours are marked as valid (RIGA_BUONA = 'Y').

---

Daily Aggregation (TBTMP_STORE_STATS)

• Aggregates valid hourly data per store/day:
• Total customers in
• Total customers out
• Total tickets
• Provides daily-level metrics for additional quality checks.

---

Validation Rule 4: No Customer Exit

• Business Rule:
  If no customers exited the store during valid hours, conversion rate is meaningless.

• Action:

• Entire day is rejected with REASON_N = 'NO C_OUT'.
• Data is removed from further processing.

---

Validation Rule 4bis: No Tickets

• Business Rule:
  A day with zero tickets cannot produce a valid conversion rate.

• Action:

• Entire day is rejected with REASON_N = 'NO TICKET'.
• Data is removed from further processing.

---

Advanced Statistics (TBTMP_STORE_ADV_STATS)

For remaining valid days: - Calculates: - Average of customers in/out - Delta between in and out - Variance percentage - Conversion ratio (tickets / customers out)

These metrics support anomaly detection.

---

Validation Rule 5: Excessive Variance

• Business Rule:
  If variance between customer in and out is ≥ 10%, data is considered unreliable.

• Action:

• Entire day is rejected with REASON_N = 'VARIANZA'.

---

Validation Rule 7: Abnormal Ticket-to-Exit Ratio

• Business Rule:
  If ticket-to-exit ratio exceeds 3, people counting is assumed incorrect.

• Action:

• Entire day is rejected with REASON_N = 'RATIO TICKET_OUT'.

---

Final Load of Valid Records

• Remaining valid hourly rows (RIGA_BUONA = 'Y') are inserted into TBDWFT_CONV_RATE_NEW.
• Additional enrichments:
• Variance value.
• Ticket splits:
  • Retail tickets (channels 10, 20, 50, non-mixed).
  • DSS tickets non-mixed (channel 40 only).
  • DSS mixed tickets (same ticket across multiple channels).

---

Comparability Flag Update

• Updates CR_FLG_COMP based on prior-year comparability logic from CR_GIORNI_COMPARABLE_ACT_SU_LY.

---

Target Data Dictionary

Target Table: DW_LOADTARGET.DWH.TBDWFT_CONV_RATE_NEW

Column Name	Data Type	Description	Logic / Source
COUNTRY_COD	VARCHAR	Country code	From source conv_rate
COMPANY_COD	VARCHAR	Company identifier	From source conv_rate
STORE_COD	VARCHAR	Store identifier	From source conv_rate
POSTING_DATE_NUM	NUMBER	Calendar date	From source conv_rate
TIME_SLOT_COD	VARCHAR	Hourly time slot	From source conv_rate
CUSTOMER_IN_NUM	NUMBER	Customers entering	From source conv_rate
CUSTOMER_OUT_NUM	NUMBER	Customers exiting	From source conv_rate
TICKET_NUM	NUMBER	Total tickets	From source conv_rate
CR_FLAG	VARCHAR	Conversion rate validity flag	Derived from validation rules
REASON_N	VARCHAR	Rejection reason	Business rule applied
VARIANZA	NUMBER	In/Out variance (%)	From advanced stats
TICKET_RETAIL_NUM	NUMBER	Retail non-mixed tickets	Derived from sales
TICKET_DSS_NUM	NUMBER	DSS non-mixed tickets	Derived from sales
TICKET_MISTI_NUM	NUMBER	Mixed-channel tickets	Derived from sales
CR_FLG_COMP	VARCHAR	Comparable vs last year	From comparability table

---

Logic Flow Diagram (Mermaid)

flowchart TD
    %% Styles
    classDef param fill:#7a88bf,stroke:#01579b,stroke-width:2px
    classDef source fill:#b56a4a,stroke:#333,stroke-width:1px
    classDef step fill:#a8a04a,stroke:#fbc02d,stroke-width:2px
    %%classDef target fill:#bfb,stroke:#333,stroke-width:2px

    %% Nodes
    Start([Start Procedure]):::param
    Init[Truncate Temp Tables &<br>Calc Date Range]:::transform

    Src_Staging[TBTMP_CONV_RATE]:::source
    Src_Sales[TBDWFT_SALES]:::source
    Src_MD[Store/PCounter MD]:::source

    Prep_Sales[Load Sales Snapshot]:::transform
    Prep_Store[Load Store Info & Hours]:::transform

    Filter_Status{PCounter Status<br>Valid?}:::step
    Reject_Status[Reject: STATUS PCOUNTER]:::target

    Mark_Hours[Mark Valid Time Slots]:::transform
    Calc_Stats[Calc Daily Aggregates]:::transform

    Filter_Out{Cust Out >= 1?}:::step
    Reject_Out[Reject: NO C_OUT]:::target

    Filter_Tix{Tickets > 0?}:::step
    Reject_Tix[Reject: NO TICKET]:::target

    Calc_Adv[Calc Variance & Ratio]:::transform

    Filter_Var{Variance < 10%?}:::step
    Reject_Var[Reject: VARIANZA]:::target

    Filter_Ratio{Ticket/Out Ratio <= 3?}:::step
    Reject_Ratio[Reject: RATIO TICKET_OUT]:::target

    Final_Load[Insert Valid Rows with<br>Ticket Classification]:::target
    Update_Comp[Update Comparable Flag]:::transform
    End([End Procedure]):::param

    %% Flow
    Start --> Init
    Src_Staging --> Init
    Init --> Prep_Sales
    Src_Sales --> Prep_Sales

    Init --> Prep_Store
    Src_MD --> Prep_Store

    Prep_Store --> Filter_Status
    Filter_Status -- No --> Reject_Status
    Filter_Status -- Yes --> Mark_Hours

    Mark_Hours --> Calc_Stats
    Calc_Stats --> Filter_Out
    Filter_Out -- No --> Reject_Out
    Filter_Out -- Yes --> Filter_Tix

    Filter_Tix -- No --> Reject_Tix
    Filter_Tix -- Yes --> Calc_Adv

    Calc_Adv --> Filter_Var
    Filter_Var -- No --> Reject_Var
    Filter_Var -- Yes --> Filter_Ratio

    Filter_Ratio -- No --> Reject_Ratio
    Filter_Ratio -- Yes --> Final_Load

    Final_Load --> Update_Comp
    Update_Comp --> End

DECLARE

   V_INIZIO      NUMBER;
   V_WK_INIZIO   NUMBER;

BEGIN
    -- truncating temporary tables
  EXECUTE IMMEDIATE ''TRUNCATE TABLE DW_LOADTARGET.DWH.TBDWFT_SALES_CONV_RATE''; 

    EXECUTE IMMEDIATE ''TRUNCATE TABLE DW_LOADTARGET.DWH_TMP.TBTMP_STORE_INFO'';

    EXECUTE IMMEDIATE ''TRUNCATE TABLE DW_LOADTARGET.DWH_TMP.TBTMP_STORE_STATS'';

    EXECUTE IMMEDIATE ''TRUNCATE TABLE DW_LOADTARGET.DWH_TMP.TBTMP_STORE_ADV_STATS'';

    EXECUTE IMMEDIATE ''TRUNCATE TABLE DW_LOADTARGET.DWH_TMP.TBTMP_STORE_COMP'';

    -- taking min posting_date_num & week_num from temp conv_rate
    SELECT MIN (POSTING_DATE_NUM), MIN (POSTING_YEARWEEK_NUM)
        INTO :V_INIZIO, :V_WK_INIZIO
    FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE;

    -- delete data in conv_rate_new after these date_num/week_num for clean loading
    DELETE FROM DW_LOADTARGET.DWH.TBDWFT_CONV_RATE_NEW C
    WHERE C.POSTING_DATE_NUM >= :V_INIZIO
        AND C.POSTING_YEARWEEK_NUM >= :V_WK_INIZIO;

    -- insert into FINAL conv_rate data from sales table
    -- INNER JOINED with the TEMP conv_rate data
    INSERT INTO DW_LOADTARGET.DWH.TBDWFT_SALES_CONV_RATE
    SELECT COUNTRY_COD,
        S.COMPANY_COD,
        MOV_PROGR_COD,
        STORE_COD,
        CHANNEL_COD,
        POSTING_YEARWEEK_NUM,
        POSTING_DAT_NUM,
        SUBSTR (RECEIPT_COD, 1, 30) AS TICKET,
        HOUR_NUM
    FROM DW_LOADTARGET.DWH.TBDWFT_SALES S
    INNER JOIN
        (SELECT DISTINCT COMPANY_COD 
            FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE) CONV
    ON S.COMPANY_COD=CONV.COMPANY_COD
    AND POSTING_YEARWEEK_NUM >= :V_WK_INIZIO
    AND POSTING_DAT_NUM >= :V_INIZIO;

    -- insert into TEMP store_info gli store che sono nella TEMP conv_rate
    -- prendendo dati dalla P_COUNTER (status_P_C) e dalla TRANSMISSION_DATA
    INSERT INTO DW_LOADTARGET.DWH_TMP.TBTMP_STORE_INFO
    SELECT S.COMPANY_COD, 
        S.STORE_COD, 
        S.POSTING_DATE_NUM, 
        NVL(P.PCOUNTER_COD,''X'') AS STATUS_P_C, 
        /*   NVL((
            CASE 
                WHEN S.COMPANY_COD IN (''185C'',''186S'',''187W'')
                THEN 0
                ELSE FIRST_SALE_HOUR_NUM 
            END
            ), 3333 )
            AS V_INIZIO,
            NVL((CASE 
                WHEN S.COMPANY_COD IN (''185C'',''186S'',''187W'')
                THEN 9999
                ELSE
                    CASE 
                        WHEN LAST_SALE_HOUR_NUM = 0 
                        THEN 2200 
                        ELSE LAST_SALE_HOUR_NUM 
                    END
            END), 3333)
            AS V_FINE ,*/
        NVL(FIRST_SALE_HOUR_NUM,0) AS V_INIZIO,
        NVL(CASE 
                WHEN LAST_SALE_HOUR_NUM = 0 
                    THEN 2200 
                ELSE LAST_SALE_HOUR_NUM 
            END, 0) AS V_FINE 
    FROM (
            SELECT DISTINCT COMPANY_COD, STORE_COD, POSTING_DATE_NUM
            FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE
        ) AS S
    LEFT OUTER JOIN DW_LOADTARGET.DWH.TBDWMD_STORE_PCOUNTER P
        ON S.STORE_COD = P.STORE_COD
        AND S.POSTING_DATE_NUM BETWEEN P.FROM_DAT AND P.TO_DAT
    LEFT OUTER JOIN DW_LOADTARGET.DWH.TBDWFT_TRANSMISSION_DATA T
        ON S.STORE_COD = T.STORE_COD 
        AND S.POSTING_DATE_NUM = T.POSTING_DATE_NUM;


    -- 1) SE IL CONTAPERSONE NON HA STATO VALIDO SCARTO TUTTE LE FASCE ORARIE
    -- insert nella CONV_RATE_NEW con CR_FLAG = 'N' & REASON_N = 'STATUS_PCOUNTER' tutti i dati della conv_rate,
    -- il cui store per quel giorno non ha stato "valido"
    INSERT INTO DW_LOADTARGET.DWH.TBDWFT_CONV_RATE_NEW
    SELECT C.COUNTRY_COD,
        C.COMPANY_COD,
        C.STORE_COD,
        C.CHAIN_COD,
        C.STATUS_COD,
        C.POSTING_DATE_NUM,
        C.POSTING_YEARWEEK_NUM,
        C.POSTING_YEARMONTH_NUM,
        C.DAY_WEEK_NUM,
        C.DAY_FATT_WEEK_NUM,
        C.TIME_SLOT_COD,
        C.CUSTOMER_IN_NUM,
        C.CUSTOMER_OUT_NUM,
        C.TICKET_NUM,
        ''N'', -- CR_FLAG
        ''N'',
        ''STATUS PCOUNTER'' AS REASON_N,
        NULL,
        NULL,
        NULL,
        NULL     --per le righe scartate non calcolo lo split
    FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE C
    JOIN DW_LOADTARGET.DWH_TMP.TBTMP_STORE_INFO S
    ON C.STORE_COD = S.STORE_COD
        AND C.POSTING_DATE_NUM = S.POSTING_DATE_NUM
        AND S.STATUS_P_C NOT IN (''A'',''X'')
    WHERE C.COMPANY_COD NOT IN (''185C'',''186S'')
    ;

    -- delete dei dati appena inseriti dalla conv_rate
    DELETE 
        FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE C
    USING DW_LOADTARGET.DWH_TMP.TBTMP_STORE_INFO S 
    WHERE C.STORE_COD = S.STORE_COD
        AND C.POSTING_DATE_NUM = S.POSTING_DATE_NUM
        AND S.STATUS_P_C NOT IN (''A'',''X'')
        AND C.COMPANY_COD NOT IN (''185C'',''186S'');

/*
-- 2) SE NON CI SONO ORARI DI APERTURA E CHIUSURA SCARTO INTERO GIORNO

    INSERT INTO DW_LOADTARGET.DWH.TBDWFT_CONV_RATE_NEW
    SELECT C.COUNTRY_COD,
        C.COMPANY_COD,
        C.STORE_COD,
        C.CHAIN_COD,
        C.STATUS_COD,
        C.POSTING_DATE_NUM,
        C.POSTING_YEARWEEK_NUM,
        C.POSTING_YEARMONTH_NUM,
        C.DAY_WEEK_NUM,
        C.DAY_FATT_WEEK_NUM,
        C.TIME_SLOT_COD,
        C.CUSTOMER_IN_NUM,
        C.CUSTOMER_OUT_NUM,
        C.TICKET_NUM,
        ''N'',
        ''N'',
        ''NO APERTURA/CHIUSURA'' AS REASON_N,
        NULL,
        NULL,
        NULL,
        NULL     --per le righe scartate non calcolo lo split
    FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE C
    JOIN DW_LOADTARGET.DWH_TMP.TBTMP_STORE_INFO S
    ON C.STORE_COD = S.STORE_COD
    AND C.POSTING_DATE_NUM = S.POSTING_DATE_NUM
    --AND S.STATUS_P_C <> ''X'' --HEEREEEE
    AND S.STATUS_P_C NOT IN (''A'',''X'')
    WHERE S.V_INIZIO IS NULL;

    DELETE 
        FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE C
    USING DW_LOADTARGET.DWH_TMP.TBTMP_STORE_INFO S 
    WHERE C.STORE_COD = S.STORE_COD
        AND C.POSTING_DATE_NUM = S.POSTING_DATE_NUM
        -- AND S.STATUS_P_C <>''X''   --MODIF
        AND S.STATUS_P_C NOT IN (''A'',''X'')     
        AND S.V_INIZIO IS NULL;
 */


    -- 3) CONSIDERO BUONE SOLO LE FASCE ORARIE COMPRESE IN ORARIO DI APERTURA E CHIUSURA
    -- update righe conv_rate con RIGA_BUONA = 'Y' per tutte le fasce orarie che sono dentro gli orari di apertura e chiusura
    -- questi dipendono da V_INIZIO (che viene FIRST_SALE_HOUR della CONV_RATE) e V_FINE (che viene da LAST_SALE_HOUR della CONV_RATE)
    UPDATE DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE C
        SET RIGA_BUONA = ''Y''
        FROM DW_LOADTARGET.DWH_TMP.TBTMP_STORE_INFO S
    WHERE C.POSTING_DATE_NUM = S.POSTING_DATE_NUM
        AND C.COMPANY_COD = S.COMPANY_COD
        AND C.STORE_COD = S.STORE_COD
        AND TO_NUMBER (TIME_SLOT_COD) >= FLOOR (S.V_INIZIO / 100)
        AND TO_NUMBER (TIME_SLOT_COD) <= CASE WHEN s.V_FINE = 0 THEN 9999 ELSE CEIL (s.V_FINE / 100) END;

    -- insert nell store_stats statistiche dalla CONV_RATE (prendendo solo le righe buone, 
    -- ovvero quelle che erano buone prima + quelle aggiornate dal passo precedente)
    INSERT INTO DW_LOADTARGET.DWH_TMP.TBTMP_STORE_STATS
    SELECT COMPANY_COD, STORE_COD, POSTING_DATE_NUM, SUM (CUSTOMER_IN_NUM) as CUST_IN, SUM (CUSTOMER_OUT_NUM) AS CUST_OUT, SUM(TICKET_NUM) AS TICKETS
        FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE C
        WHERE   RIGA_BUONA = ''Y''
        GROUP BY COMPANY_COD, STORE_COD, POSTING_DATE_NUM;


    -- 4) SE NON ESCE ALMENO UNA PERSONA IN QUEL GIORNO NELLE ORE CONSIDERATE BUONE SCARTO TUTTE LE FASCE ORARIE
    -- insert nell CONV_RATE_NEW i dati dalla conv_rate (temp) con CR_FLAG = "N" & REASON_N = "NO C_OUT" 
    -- per tutti i dati del giorno in cui il numero cust_out <1
    INSERT INTO DW_LOADTARGET.DWH.TBDWFT_CONV_RATE_NEW
    SELECT c.COUNTRY_COD,
        c.COMPANY_COD,
        c.STORE_COD,
        c.CHAIN_COD,
        c.STATUS_COD,
        c.POSTING_DATE_NUM,
        c.POSTING_YEARWEEK_NUM,
        c.POSTING_YEARMONTH_NUM,
        c.DAY_WEEK_NUM,
        c.DAY_FATT_WEEK_NUM,
        c.TIME_SLOT_COD,
        c.CUSTOMER_IN_NUM,
        c.CUSTOMER_OUT_NUM,
        c.TICKET_NUM,
        ''N'',
        ''N'',
        ''NO C_OUT'' AS REASON_N,
        NULL,
        NULL,
        NULL,
        NULL        --per le righe scartate non calcolo lo split
    FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE C
    JOIN DW_LOADTARGET.DWH_TMP.TBTMP_STORE_STATS S
    ON C.store_cod = s.store_cod
        and c.posting_date_num = s.posting_date_num
    where s.cust_out < 1;

    -- delete dei dati appeni inseriti
    DELETE 
    FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE C
    USING DW_LOADTARGET.DWH_TMP.TBTMP_STORE_STATS S 
    WHERE C.STORE_COD = S.STORE_COD
        AND C.POSTING_DATE_NUM = S.POSTING_DATE_NUM
        AND S.CUST_OUT < 1;


    -- 4bis) SE NON ESCE NEMMENO UN TICKET (scontrino) IN QUEL GIORNO NELLE ORE CONSIDERATE BUONE SCARTO TUTTE LE FASCE ORARIE
    -- insert nell CONV_RATE_NEW i dati dalla conv_rate (temp) con CR_FLAG = "N" & REASON_N = "NO C_OUT" 
    -- per tutti i dati del giorno in cui il numero tickets=0 (nessun scontrino)
    INSERT INTO DW_LOADTARGET.DWH.TBDWFT_CONV_RATE_NEW
    SELECT c.COUNTRY_COD,
        c.COMPANY_COD,
        c.STORE_COD,
        c.CHAIN_COD,
        c.STATUS_COD,
        c.POSTING_DATE_NUM,
        c.POSTING_YEARWEEK_NUM,
        c.POSTING_YEARMONTH_NUM,
        c.DAY_WEEK_NUM,
        c.DAY_FATT_WEEK_NUM,
        c.TIME_SLOT_COD,
        c.CUSTOMER_IN_NUM,
        c.CUSTOMER_OUT_NUM,
        c.TICKET_NUM,
        ''N'',
        ''N'',
        ''NO TICKET'' AS REASON_N,
        NULL,
        NULL,
        NULL,
        NULL        --per le righe scartate non calcolo lo split
    FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE C
    JOIN DW_LOADTARGET.DWH_TMP.TBTMP_STORE_STATS S
    ON C.store_cod = s.store_cod
    and c.posting_date_num = s.posting_date_num
    where s.tickets = 0;

    -- delete dei dati appena inseriti
    DELETE 
    FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE C
    USING DW_LOADTARGET.DWH_TMP.TBTMP_STORE_STATS S  
    WHERE C.STORE_COD = S.STORE_COD
        AND C.POSTING_DATE_NUM = S.POSTING_DATE_NUM
        AND S.TICKETS = 0;


    // ----> DWH_TMP.TBTMP_STORE_ADV_STATS       
    -- calcolo statistiche avanzate per cust in/out (media, differenza, varianza) e "conversion rate" = tickets/cust_out
    INSERT INTO DW_LOADTARGET.DWH_TMP.TBTMP_STORE_ADV_STATS
    SELECT COMPANY_COD, STORE_COD, POSTING_DATE_NUM,
        (CUST_IN + CUST_OUT) / 2 AS V_MEDIA,
        CUST_IN - CUST_OUT AS V_DELTA,
        ROUND(ABS(V_DELTA/V_MEDIA*100),2) AS V_VARIANZA,
        ROUND (TICKETS / CUST_OUT, 2) AS V_RAPPORTO
    FROM DW_LOADTARGET.DWH_TMP.TBTMP_STORE_STATS 
    WHERE CUST_OUT > 0;


    -- 5) SE LA VARIANZA TRA INGRESSI E USCITE SUPERA IL 9% IN QUEL GIORNO SCARTO TUTTE LE FASCE ORARIE
    -- insert nell CONV_RATE_NEW i dati dalla conv_rate (temp) con CR_FLAG = "N" & REASON_N = "VARIANZA" 
    -- per tutti i dati del giorno in cui il numero V_VARIANZA>=10 (nessun scontrino)
    INSERT INTO DW_LOADTARGET.DWH.TBDWFT_CONV_RATE_NEW
    SELECT c.COUNTRY_COD,
        c.COMPANY_COD,
        c.STORE_COD,
        c.CHAIN_COD,
        c.STATUS_COD,
        c.POSTING_DATE_NUM,
        c.POSTING_YEARWEEK_NUM,
        c.POSTING_YEARMONTH_NUM,
        c.DAY_WEEK_NUM,
        c.DAY_FATT_WEEK_NUM,
        c.TIME_SLOT_COD,
        c.CUSTOMER_IN_NUM,
        c.CUSTOMER_OUT_NUM,
        c.TICKET_NUM,
        ''N'', -- CR_FLAG
        ''N'',
        ''VARIANZA'' AS REASON_N,
        s.V_VARIANZA,
        NULL,
        NULL,
        NULL        --per le righe scartate non calcolo lo split
    FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE C
    JOIN DW_LOADTARGET.DWH_TMP.TBTMP_STORE_ADV_STATS S --EMPTY
    ON C.store_cod = s.store_cod
    and c.posting_date_num = s.posting_date_num
    where s.V_VARIANZA >= 10;

    -- delete dati appena inseriti
    DELETE 
        FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE C
    USING DW_LOADTARGET.DWH_TMP.TBTMP_STORE_ADV_STATS S 
    WHERE C.STORE_COD = S.STORE_COD
        AND C.POSTING_DATE_NUM = S.POSTING_DATE_NUM
        AND S.V_VARIANZA >= 10;


    -- 7) SE IL RAPPORTO TICKET USCITE SUPERA IL TRE SIGNIFICA CHE SONO STATE CONTATE TROPPO POCHE PERSONE RISPETTO 
    -- AGLI SCONTRINI BATTUTI IN CASSA
    INSERT INTO DW_LOADTARGET.DWH.TBDWFT_CONV_RATE_NEW
    SELECT c.COUNTRY_COD,
        c.COMPANY_COD,
        c.STORE_COD,
        c.CHAIN_COD,
        c.STATUS_COD,
        c.POSTING_DATE_NUM,
        c.POSTING_YEARWEEK_NUM,
        c.POSTING_YEARMONTH_NUM,
        c.DAY_WEEK_NUM,
        c.DAY_FATT_WEEK_NUM,
        c.TIME_SLOT_COD,
        c.CUSTOMER_IN_NUM,
        c.CUSTOMER_OUT_NUM,
        c.TICKET_NUM,
        ''N'',
        ''N'',
        ''RATIO TICKET_OUT'' AS REASON_N,
        s.V_VARIANZA,
        NULL,
        NULL,
        NULL        --per le righe scartate non calcolo lo split
    FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE C
    JOIN DW_LOADTARGET.DWH_TMP.TBTMP_STORE_ADV_STATS S --EMPTY
    ON C.store_cod = s.store_cod
    and c.posting_date_num = s.posting_date_num
    where s.V_RAPPORTO > 3;

    DELETE 
        FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE C
    USING DW_LOADTARGET.DWH_TMP.TBTMP_STORE_ADV_STATS S --EMPTY
    WHERE C.STORE_COD = S.STORE_COD
        AND C.POSTING_DATE_NUM = S.POSTING_DATE_NUM
        AND S.V_RAPPORTO > 3;


    -- INSERISCO LE RIGHE RIMASTE BUONE A QUESTO PUNTO (where RIGA_BUONA = 'Y')
    INSERT INTO DW_LOADTARGET.DWH.TBDWFT_CONV_RATE_NEW
    SELECT c.COUNTRY_COD,
        c.COMPANY_COD,
        c.STORE_COD,
        c.CHAIN_COD,
        c.STATUS_COD,
        c.POSTING_DATE_NUM,
        c.POSTING_YEARWEEK_NUM,
        c.POSTING_YEARMONTH_NUM,
        c.DAY_WEEK_NUM,
        c.DAY_FATT_WEEK_NUM,
        c.TIME_SLOT_COD,
        c.CUSTOMER_IN_NUM,
        c.CUSTOMER_OUT_NUM,
        c.TICKET_NUM,
        c.RIGA_BUONA,
        ''Y'',
        c.REASON_N,
        s.V_VARIANZA AS VARIANZA,
        ( -- numero ticket retail (non-misti): conto quelli nella CONV_RATE che sono nella CONV_RATE TEMP E per il quale 
        -- NON esiste lo stesso ticket in un altro channel_cod
            SELECT COUNT (DISTINCT TICKET)
            FROM DW_LOADTARGET.DWH.TBDWFT_SALES_CONV_RATE S
            WHERE S.POSTING_YEARWEEK_NUM = C.POSTING_YEARWEEK_NUM
                AND S.COMPANY_COD = C.COMPANY_COD
                AND S.POSTING_DAT_NUM = C.POSTING_DATE_NUM
                AND S.STORE_COD = C.STORE_COD
                AND TO_NUMBER (S.HOUR_NUM) = TO_NUMBER (C.TIME_SLOT_COD)
                AND S.CHANNEL_COD IN (''10'', ''20'', ''50'')
                AND NOT EXISTS (
                    SELECT 1
                    FROM DW_LOADTARGET.DWH.TBDWFT_SALES_CONV_RATE S1
                    WHERE S.POSTING_YEARWEEK_NUM = S1.POSTING_YEARWEEK_NUM
                        AND S.COMPANY_COD = S1.COMPANY_COD
                        AND S.POSTING_DAT_NUM = S1.POSTING_DAT_NUM
                        AND S.STORE_COD = S1.STORE_COD
                        AND S.HOUR_NUM = S1.HOUR_NUM
                        AND S.TICKET = S1.TICKET
                        AND S.CHANNEL_COD <> S1.CHANNEL_COD
                )
        ) AS TICKET_RETAIL_NUM,
        ( -- numero ticket dss (non-misti): conto quelli nella CONV_RATE che sono nella CONV_RATE TEMP E per il quale 
        -- NON esiste lo stesso ticket in un altro channel_cod
            SELECT COUNT (DISTINCT TICKET)
            FROM DW_LOADTARGET.DWH.TBDWFT_SALES_CONV_RATE S
            WHERE S.POSTING_YEARWEEK_NUM = C.POSTING_YEARWEEK_NUM
                AND S.COMPANY_COD = C.COMPANY_COD
                AND S.POSTING_DAT_NUM = C.POSTING_DATE_NUM
                AND S.STORE_COD = C.STORE_COD
                AND TO_NUMBER (S.HOUR_NUM) = TO_NUMBER (C.TIME_SLOT_COD)
                AND S.CHANNEL_COD = ''40''
                AND NOT EXISTS (
                    SELECT 1
                    FROM DW_LOADTARGET.DWH.TBDWFT_SALES_CONV_RATE S1
                    WHERE S.POSTING_YEARWEEK_NUM = S1.POSTING_YEARWEEK_NUM
                        AND S.COMPANY_COD = S1.COMPANY_COD
                        AND S.POSTING_DAT_NUM = S1.POSTING_DAT_NUM
                        AND S.STORE_COD = S1.STORE_COD
                        AND S.HOUR_NUM = S1.HOUR_NUM
                        AND S.TICKET = S1.TICKET
                        AND S.CHANNEL_COD <> S1.CHANNEL_COD
                )
        ) AS TICKET_DSS_NUM,
        ( -- numero ticket dss (misti): conto quelli nella CONV_RATE che sono nella CONV_RATE TEMP E per il quale 
        -- esiste lo stesso ticket in un altro channel_cod
            SELECT COUNT (DISTINCT TICKET)
            FROM DW_LOADTARGET.DWH.TBDWFT_SALES_CONV_RATE S
            WHERE S.POSTING_YEARWEEK_NUM = C.POSTING_YEARWEEK_NUM
                AND S.COMPANY_COD = C.COMPANY_COD
                AND S.POSTING_DAT_NUM = C.POSTING_DATE_NUM
                AND S.STORE_COD = C.STORE_COD
                AND TO_NUMBER (S.HOUR_NUM) = TO_NUMBER (C.TIME_SLOT_COD)
                AND S.CHANNEL_COD = ''40''
                AND EXISTS (
                    SELECT 1
                    FROM DW_LOADTARGET.DWH.TBDWFT_SALES_CONV_RATE S1
                    WHERE S.POSTING_YEARWEEK_NUM = S1.POSTING_YEARWEEK_NUM
                            AND S.COMPANY_COD = S1.COMPANY_COD
                            AND S.POSTING_DAT_NUM = S1.POSTING_DAT_NUM
                            AND S.STORE_COD = S1.STORE_COD
                            AND S.HOUR_NUM = S1.HOUR_NUM
                            AND S.TICKET = S1.TICKET
                            AND S.CHANNEL_COD <> S1.CHANNEL_COD
                )
        ) AS TICKET_MISTI_NUM -- TICKET_MISTI_NUM
    FROM DW_LOADTARGET.DWH_TMP.TBTMP_CONV_RATE C
        JOIN DW_LOADTARGET.DWH_TMP.TBTMP_STORE_ADV_STATS S
        ON C.store_cod = s.store_cod
        and c.posting_date_num = s.posting_date_num
    WHERE RIGA_BUONA = ''Y'';


    -- AGGIORNO FLAG PER COMPARABILITA CON ANNO PRECEDENTE
    UPDATE DW_LOADTARGET.DWH.TBDWFT_CONV_RATE_NEW C
    SET CR_FLG_COMP = COMP.CR_COMPARABLE
    FROM CR_GIORNI_COMPARABLE_ACT_SU_LY COMP
    WHERE C.COMPANY_COD = COMP.COMPANY_COD
        AND C.STORE_COD = COMP.STORE_COD
        AND C.POSTING_DATE_NUM = COMP.POSTING_DATE_NUM
    ;     

RETURN 0;
END;
;