Détails des calculs - PT2QE¶

-- Conditions tarifaires
cnd.ID_ACC IS NULL                           -- Pas d'accord
AND cnd.FG_CND_VLD = 'X'                     -- Condition valide
AND cnd.DT_FIN > SYSDATE                     -- Encore valide
AND cnd.ID_TYP_CND IN ('ZFAP', 'ZFSP', 'ZIAP', 'ZISP')
AND cnd.ID_SEQ IN ('A305', 'A565')           -- Séquences tarifaires actives

-- Clients
UPPER(TRIM(c.ID_TC_CG)) <> 'AUTRES'          -- Exclusion AUTRES
AND (
    (c.ID_STA_CLN IN ('10', '20', '30', '50') AND cnd.LC_SEQ_CLN = 'CLIENT')
    OR (c.ID_AGC <> 'A000' AND cnd.LC_SEQ_CLN IN ('HIERARCHIE CLIENT N4', 'HIERARCHIE CLIENT N5', 'HIERARCHIE CLIENT N6'))
)

-- Articles
a.ID_GMM <> 'PSN'                            -- Exclusion PSN
AND a.LC_HI1 <> 'Qualificatif article inconnu'
AND a.LC_HIC_SYSCO_N2 NOT IN ('Divers', 'Freezer')

-- ZOOM1 uniquement (filtre final)
WHERE ou.UNIVERS = 'ZOOM1'

UNIVERS = CASE
    -- ZOOM1 : RCI PI avec ou sans KAM
    WHEN ID_TC_CG = 'RCI' AND ID_TC_CIBLE = 'PINDEP' THEN 'ZOOM1'

    -- ZOOM1 : RCI GI SANS KAM
    WHEN ID_TC_CG = 'RCI' AND ID_TC_CIBLE = 'GINDEP'
        AND ID_KAM = 'NO_KAM' THEN 'ZOOM1'

    -- ZOOM2 : RCI GI AVEC KAM
    WHEN ID_TC_CG = 'RCI' AND ID_TC_CIBLE = 'GINDEP'
        AND ID_KAM <> 'NO_KAM' THEN 'ZOOM2'

    -- ZOOM1 : FG_HM = '1' (hors mercuriale) - HORS RSC
    WHEN FG_HM = '1' AND (
        (ID_TC_CG = 'RSI' AND ID_TC_CIBLE IN ('MP_AO_', 'GR_A_G', 'MPMAPA', 'SRCREG', 'CA_REG', 'CR_REG'))
        OR (ID_TC_CG = 'RCI' AND ID_TC_CIBLE IN ('PERSYS', 'PERCLI', 'AS_AUT'))
    ) THEN 'ZOOM1'

    -- ZOOM1 : RSC avec ID_MERC_HM = 'HM'
    WHEN ID_TC_CG = 'RSC' AND ID_TC_CIBLE IN ('CR_NAT', 'SRCNAT', 'MP_NAT', 'CA_NAT')
        AND TRIM(ID_MERC_HM) = 'HM' THEN 'ZOOM1'

    -- ZOOM2 : FG_HM = '0' (mercuriale)
    WHEN FG_HM = '0' AND (
        (ID_TC_CG = 'RSI' AND ID_TC_CIBLE IN ('MP_AO_', 'SRCREG', 'MPMAPA', 'CA_REG', 'CR_REG'))
        OR (ID_TC_CG = 'RCI' AND ID_TC_CIBLE = 'GC_REG')
    ) THEN 'ZOOM2'

    -- ZOOM2 : RCI GC_REG avec FG_HM = '1'
    WHEN ID_TC_CG = 'RCI' AND ID_TC_CIBLE = 'GC_REG'
        AND FG_HM = '1' THEN 'ZOOM2'

    -- ZOOM3 : RSC avec ID_MERC_HM <> 'HM'
    WHEN ID_TC_CG = 'RSC' AND ID_TC_CIBLE IN ('SRCNAT', 'CR_NAT', 'MP_NAT', 'CA_NAT')
        AND TRIM(ID_MERC_HM) <> 'HM' THEN 'ZOOM3'

    -- ZOOM3 : RCC
    WHEN ID_TC_CG = 'RCC' AND ID_TC_CIBLE IN ('RCHNAT', 'GCN3PL', 'GCNDIR') THEN 'ZOOM3'

    ELSE 'HORS PERIMETRE'
END

WITH LAST_FG_HM AS (
    SELECT 
        ID_CLN_KEY,
        ID_CLN,
        ID_ART,
        FG_HM as LAST_FG_HM,
        ID_MERC_HM,
        ROW_NUMBER() OVER (PARTITION BY ID_CLN, ID_ART ORDER BY DT_CDE DESC, ID_FAC DESC) as RN
    FROM SYS_FACTURE_LIGNE
    WHERE DT_CDE BETWEEN :start_date AND :end_date
        -- [filtres transactionnels]
)
SELECT * FROM LAST_FG_HM WHERE RN = 1

WITH QUARTER_BOUNDS AS (
    SELECT
        ID_EXF,
        NO_TRF,
        ID_EXF || '_Q' || NO_TRF as QUARTER_CODE,
        MIN(ID_SEM) as START_DATE,
        MAX(ID_SEM) as END_DATE
    FROM SYS_MD_CALENDRIER_SYSCO
    WHERE ID_EXF BETWEEN '{current_year - 2}' AND '{current_year}'
    GROUP BY ID_EXF, NO_TRF
)
SELECT
    QUARTER_CODE,
    START_DATE,
    END_DATE
FROM QUARTER_BOUNDS
WHERE END_DATE < TO_DATE('{reference_date}', 'YYYY-MM-DD')
ORDER BY ID_EXF DESC, NO_TRF DESC
FETCH FIRST 4 ROWS ONLY

FROM SYS_FACTURE_LIGNE f
WHERE f.DT_CDE BETWEEN :start_date AND :end_date
    AND f.ID_TYP_FAC = 'ZF2'                       -- Type de facture
    AND f.MT_GM4 IS NOT NULL                       -- Marge GM4 renseignée
    AND f.MT_CAB > 0                               -- CA positif
    AND f.QT_UF > 0                                -- Quantité positive
    AND COALESCE(TRIM(f.FG_PRESTA), '0') = '0'    -- Pas de prestation
    AND COALESCE(TRIM(f.FG_MARCHANDISE), ' ') IN ('X', '1')  -- Marchandise uniquement
    AND p.ID_PRP_KEY IS NOT NULL                   -- Prix référencé (exclusion promo via PRP_LABELS)

-- Total global (hors Prix promo)
SUM(CASE WHEN TRIM(op.LC_PRP_N1) <> 'Prix promo' THEN f.MT_CAB ELSE 0 END) as MT_CAB_4Q,
SUM(CASE WHEN TRIM(op.LC_PRP_N1) <> 'Prix promo' THEN f.QT_UF ELSE 0 END) as QT_UF_4Q,
SUM(CASE WHEN TRIM(op.LC_PRP_N1) <> 'Prix promo' THEN f.QT_KG ELSE 0 END) as QT_KG_4Q,

-- Prix fermes uniquement
SUM(CASE WHEN TRIM(op.LC_PRP_N1) = 'Prix fermes' THEN f.MT_CAB ELSE 0 END) as MT_CAB_4Q_FERMES,
SUM(CASE WHEN TRIM(op.LC_PRP_N1) = 'Prix fermes' THEN f.QT_UF ELSE 0 END) as QT_UF_4Q_FERMES,
SUM(CASE WHEN TRIM(op.LC_PRP_N1) = 'Prix fermes' THEN f.QT_KG ELSE 0 END) as QT_KG_4Q_FERMES,

-- Prix indexés uniquement
SUM(CASE WHEN TRIM(op.LC_PRP_N1) = 'Prix indexés' THEN f.MT_CAB ELSE 0 END) as MT_CAB_4Q_INDEXES,
SUM(CASE WHEN TRIM(op.LC_PRP_N1) = 'Prix indexés' THEN f.QT_UF ELSE 0 END) as QT_UF_4Q_INDEXES,
SUM(CASE WHEN TRIM(op.LC_PRP_N1) = 'Prix indexés' THEN f.QT_KG ELSE 0 END) as QT_KG_4Q_INDEXES

WITH LAST_QT_UF AS (
    SELECT 
        ID_CLN,
        ID_ART,
        QT_UF as LAST_QT_UF,
        ROW_NUMBER() OVER (PARTITION BY ID_CLN, ID_ART ORDER BY DT_CDE DESC, ID_FAC DESC) as RN
    FROM SYS_FACTURE_LIGNE
    WHERE DT_CDE BETWEEN :start_date AND :end_date
        AND QT_UF > 0
        -- [mêmes filtres transactionnels]
)
SELECT * FROM LAST_QT_UF WHERE RN = 1

SELECT 
    o.*,
    c.* 
FROM offers o
LEFT JOIN PT1CE_OPTIMAL_ZOOM1 c
    ON o.ID_ART = c.ID_ART
    AND o.TYPE_CLIENT = c.TYPE_CLIENT
    AND o.TYPE_RESTAURANT = c.TYPE_RESTAURANT
    AND NVL(o.GEO, 'NULL') = NVL(c.GEO, 'NULL')
    AND c.CUBE_TYPE = 'MASTER'
    AND c.STATUS = 'OPTIMAL'
WHERE c.ID_ART IS NOT NULL

SELECT 
    o.*,
    c.* 
FROM offers o
JOIN PT1CE_OPTIMAL_ZOOM1 c
    ON o.ID_ART = c.ID_ART
    AND c.TYPE_CLIENT = 'NATIONAL'
    AND c.TYPE_RESTAURANT = 'NATIONAL'
    AND (c.GEO = 'NATIONAL' OR c.GEO IS NULL)
    AND c.CUBE_TYPE = 'NATIONAL'
    AND c.STATUS = 'OPTIMAL'
WHERE NOT EXISTS (
    SELECT 1 FROM MATCH_MASTER m 
    WHERE m.ID_CLN = o.ID_CLN AND m.ID_ART = o.ID_ART
)

SELECT 
    o.*,
    NULL as [toutes les colonnes corridor]
FROM offers o
WHERE NOT EXISTS (SELECT 1 FROM MATCH_MASTER WHERE ...)
    AND NOT EXISTS (SELECT 1 FROM MATCH_NATIONAL WHERE ...)

PAS_ACTIF, PRB_ACTIF, PRB_RC_ACTIF, PRB_COLL_ACTIF, PRB_TO_USE,
BORNE_PL1_PL2, BORNE_PL2_PL3, BORNE_PL3_PL4,
BORNE_PL4_PL5, BORNE_PL5_PL6, BORNE_PL6_PLX

NEW_PAS, NEW_PRB, NEW_PRB_RC, NEW_PRB_COLL,
NEW_BORNE_PL1_PL2, NEW_BORNE_PL2_PL3, NEW_BORNE_PL3_PL4,
NEW_BORNE_PL4_PL5, NEW_BORNE_PL5_PL6, NEW_BORNE_PL6_PLX

PRICE_SENSITIVITY,  -- HIGH / MEDIUM / LOW
ECART_TYPE,         -- Écart-type des prix observés
CUBE_TYPE,          -- MASTER / NATIONAL
STATUS              -- OPTIMAL (seul statut retenu)

PRB_ACTIF = CASE 
    WHEN PRB_TO_USE = 1 THEN PRB_RC_ACTIF 
    ELSE PRB_COLL_ACTIF 
END

NEW_PRB = CASE 
    WHEN PRB_TO_USE = 1 THEN NEW_PRB_RC 
    ELSE NEW_PRB_COLL 
END

PCT_HAUSSE_PAS = CASE 
    WHEN PAS_ACTIF > 0 AND NEW_PAS > 0 
    THEN (NEW_PAS - PAS_ACTIF) / PAS_ACTIF
    ELSE 0
END

IS_PAS_BAISSE = CASE 
    WHEN NEW_PAS < PAS_ACTIF THEN 1 
    ELSE 0 
END

DECISION_PATH = CASE 
    WHEN IS_PAS_BAISSE = 1 THEN 'PAS_BAISSE_GEL_PRIX'
    -- ...
END

PRIX_RECOMMANDE = PRIX_TARIF_ACTUEL  -- Gel total, aucune hausse
PCT_HAUSSE_FINALE = 0

IS_PL1_ANCIENNES = CASE 
    WHEN PRIX_TARIF_ACTUEL <= PRB_ACTIF
        AND PRIX_TARIF_ACTUEL > BORNE_PL1_PL2
    THEN 1 
    ELSE 0 
END

DECISION_PATH = CASE 
    WHEN IS_PAS_BAISSE = 0 
        AND IS_PL1_ANCIENNES = 1 
    THEN 'PL1_CONSERVATION_PREMIUM'
    -- ...
END

-- Étape 1 : Conservation avec plancher de sécurité
PRIX_AVANT_CAPPING_PRB = GREATEST(PRIX_TARIF_ACTUEL, NEW_BORNE_PL2_PL3)

-- Étape 2 : Application capping PRB final
PRIX_RECOMMANDE = LEAST(PRIX_AVANT_CAPPING_PRB, NEW_PRB)

DECISION_PATH = CASE 
    WHEN IS_PAS_BAISSE = 0 
        AND IS_PL1_ANCIENNES = 0 
    THEN 'OPTIMISATION_STANDARD'
    -- ...
END

PRIX_AVANT_CAPPING_PRB = GREATEST(RECO1_AVEC_CAPPING, RECO2)
PRIX_RECOMMANDE = LEAST(PRIX_AVANT_CAPPING_PRB, NEW_PRB)

RECO1_BASE = CASE
    WHEN ed.PRIX_TARIF_ACTUEL > ed.NEW_BORNE_PL1_PL2 THEN ed.PRIX_TARIF_ACTUEL  -- Prix déjà en PL1, pas de changement
    WHEN ed.PRIX_TARIF_ACTUEL > ed.NEW_BORNE_PL2_PL3 THEN ed.NEW_BORNE_PL1_PL2  -- Remonter vers PL1
    WHEN ed.PRIX_TARIF_ACTUEL > ed.NEW_BORNE_PL3_PL4 THEN ed.NEW_BORNE_PL1_PL2  -- Remonter vers PL1
    WHEN ed.PRIX_TARIF_ACTUEL > ed.NEW_BORNE_PL4_PL5 THEN ed.NEW_BORNE_PL2_PL3  -- Remonter vers PL2
    WHEN ed.PRIX_TARIF_ACTUEL > ed.NEW_BORNE_PL5_PL6 THEN ed.NEW_BORNE_PL3_PL4  -- Remonter vers PL3
    WHEN ed.PRIX_TARIF_ACTUEL > ed.NEW_BORNE_PL6_PLX THEN ed.NEW_BORNE_PL5_PL6  -- Remonter vers PL5
    WHEN ed.PRIX_TARIF_ACTUEL >= ed.NEW_PAS THEN ed.NEW_BORNE_PL6_PLX  -- Remonter vers PL6
    ELSE ed.NEW_PAS  -- Remonter au PAS minimum
END

RECO2 = PRIX_TARIF_ACTUEL * (1 + PCT_HAUSSE_PAS)

où PCT_HAUSSE_PAS = (NEW_PAS - PAS_ACTIF) / PAS_ACTIF

-- Si PAS nul ou négatif
WHEN PAS_ACTIF <= 0 OR NEW_PAS <= 0 THEN RECO2 = PRIX_TARIF_ACTUEL

-- Si PAS en baisse (ne devrait pas arriver dans CHEMIN 3)
WHEN NEW_PAS < PAS_ACTIF THEN RECO2 < PRIX_TARIF_ACTUEL

RECO1_APRES_CAPPING_SENSIBILITE = CASE
    WHEN PRICE_SENSITIVITY = 'HIGH' AND CAPPING_HIGH IS NOT NULL THEN
        LEAST(RECO1_BASE, PRIX_TARIF_ACTUEL * (1 + CAPPING_HIGH))
    WHEN PRICE_SENSITIVITY = 'MEDIUM' AND CAPPING_MEDIUM IS NOT NULL THEN
        LEAST(RECO1_BASE, PRIX_TARIF_ACTUEL * (1 + CAPPING_MEDIUM))
    WHEN PRICE_SENSITIVITY = 'LOW' AND CAPPING_LOW IS NOT NULL THEN
        LEAST(RECO1_BASE, PRIX_TARIF_ACTUEL * (1 + CAPPING_LOW))
    ELSE RECO1_BASE
END

CAPPING_SENSIBILITE_APPLIED = CASE
    WHEN PRICE_SENSITIVITY = 'HIGH' AND CAPPING_HIGH IS NOT NULL 
        AND RECO1_BASE > PRIX_TARIF_ACTUEL * (1 + CAPPING_HIGH) 
    THEN 1
    -- ... (idem pour MEDIUM et LOW)
    ELSE 0
END

RECO1_AVEC_CAPPING = CASE
    WHEN LC_ATTRIBUT = 'Basiques' THEN
        LEAST(RECO1_APRES_CAPPING_SENSIBILITE, PRIX_TARIF_ACTUEL * 1.50)
    ELSE RECO1_APRES_CAPPING_SENSIBILITE
END

CAPPING_BASIQUES_APPLIED = CASE
    WHEN LC_ATTRIBUT = 'Basiques' 
        AND RECO1_APRES_CAPPING_SENSIBILITE > PRIX_TARIF_ACTUEL * 1.50
    THEN 1
    ELSE 0
END

PRIX_AVANT_CAPPING_PRB = GREATEST(RECO1_AVEC_CAPPING, RECO2)

RECO_TYPE = CASE
    WHEN RECO1_AVEC_CAPPING >= RECO2 THEN 'REPOSITIONNEMENT_PALIERS'
    ELSE 'HAUSSE_PROPORTIONNELLE_PAS'
END

RECO_SELECTIONNEE = CASE
    WHEN RECO1_AVEC_CAPPING >= RECO2 THEN 'RECO1_REPOSITIONNEMENT_PALIERS'
    ELSE 'RECO2_HAUSSE_PROPORTIONNELLE_PAS'
END

PRIX_RECOMMANDE = LEAST(PRIX_AVANT_CAPPING_PRB, NEW_PRB)

CAPPING_PRB_APPLIED = CASE
    WHEN PRIX_AVANT_CAPPING_PRB > NEW_PRB THEN 1
    ELSE 0
END

CAPPING_APPLIED = CASE
    -- Priorité 1 : Gel total (aucun mouvement)
    WHEN DECISION_PATH = 'PAS_BAISSE_GEL_PRIX' 
    THEN 'GEL_PAS'

    -- Priorité 2 : Capping PRB final (plafond absolu)
    WHEN CAPPING_PRB_APPLIED = 1 
    THEN 'PRB_FINAL'

    -- Priorité 3 : Plancher de sécurité (CHEMIN 2)
    WHEN PLANCHER_PL2_APPLIED = 1 
    THEN 'PLANCHER_PL2_PL3'

    -- Priorité 4 : Capping basiques (CHEMIN 3)
    WHEN CAPPING_BASIQUES_APPLIED = 1
    THEN 'BASIQUES_50PCT'

    -- Priorité 5 : Capping sensibilité (CHEMIN 3)
    WHEN CAPPING_SENSIBILITE_APPLIED = 1 
    THEN 'SENSIBILITE'

    -- Aucun capping appliqué
    ELSE 'NONE'
END

PCT_HAUSSE_FINALE = CASE 
    WHEN PRIX_TARIF_ACTUEL > 0 THEN
        (PRIX_RECOMMANDE - PRIX_TARIF_ACTUEL) / PRIX_TARIF_ACTUEL
    ELSE 0
END

POSITION_TARIF_ACTUEL_DANS_ANCIENNES_BORNES = CASE
    WHEN PRIX_TARIF_ACTUEL > PRB_ACTIF THEN 'ABOVE_PRB'
    WHEN PRIX_TARIF_ACTUEL >= BORNE_PL1_PL2 THEN 'PL1'
    WHEN PRIX_TARIF_ACTUEL >= BORNE_PL2_PL3 THEN 'PL2'
    WHEN PRIX_TARIF_ACTUEL >= BORNE_PL3_PL4 THEN 'PL3'
    WHEN PRIX_TARIF_ACTUEL >= BORNE_PL4_PL5 THEN 'PL4'
    WHEN PRIX_TARIF_ACTUEL >= BORNE_PL5_PL6 THEN 'PL5'
    WHEN PRIX_TARIF_ACTUEL >= BORNE_PL6_PLX THEN 'PL6'
    WHEN PRIX_TARIF_ACTUEL >= PAS_ACTIF THEN 'PLX'
    ELSE 'BELOW_PAS'
END

PALIER_TARIF_ACTUEL_VS_NOUVELLES_BORNES = CASE
    WHEN PRIX_TARIF_ACTUEL > NEW_PRB THEN 'ABOVE_PRB'
    WHEN PRIX_TARIF_ACTUEL >= NEW_BORNE_PL1_PL2 THEN 'PL1'
    WHEN PRIX_TARIF_ACTUEL >= NEW_BORNE_PL2_PL3 THEN 'PL2'
    WHEN PRIX_TARIF_ACTUEL >= NEW_BORNE_PL3_PL4 THEN 'PL3'
    WHEN PRIX_TARIF_ACTUEL >= NEW_BORNE_PL4_PL5 THEN 'PL4'
    WHEN PRIX_TARIF_ACTUEL >= NEW_BORNE_PL5_PL6 THEN 'PL5'
    WHEN PRIX_TARIF_ACTUEL >= NEW_BORNE_PL6_PLX THEN 'PL6'
    WHEN PRIX_TARIF_ACTUEL >= NEW_PAS THEN 'PLX'
    ELSE 'BELOW_PAS'
END

POSITION_NOUVEAU_PRIX_DANS_NOUVELLES_BORNES = CASE
    WHEN PRIX_RECOMMANDE > NEW_PRB THEN 'ABOVE_PRB'
    WHEN PRIX_RECOMMANDE >= NEW_BORNE_PL1_PL2 THEN 'PL1'
    WHEN PRIX_RECOMMANDE >= NEW_BORNE_PL2_PL3 THEN 'PL2'
    WHEN PRIX_RECOMMANDE >= NEW_BORNE_PL3_PL4 THEN 'PL3'
    WHEN PRIX_RECOMMANDE >= NEW_BORNE_PL4_PL5 THEN 'PL4'
    WHEN PRIX_RECOMMANDE >= NEW_BORNE_PL5_PL6 THEN 'PL5'
    WHEN PRIX_RECOMMANDE >= NEW_BORNE_PL6_PLX THEN 'PL6'
    WHEN PRIX_RECOMMANDE >= NEW_PAS THEN 'PLX'
    ELSE 'BELOW_PAS'
END

CREATE TABLE PT2QE_CAPPING_CUBES AS
-- Cubes MASTER
SELECT DISTINCT
    UNIVERS,
    TYPE_CLIENT,
    TYPE_RESTAURANT,
    GEO,
    'MASTER' as CUBE_TYPE,
    0.05 as CAPPING_HIGH,   -- Valeur par défaut
    0.15 as CAPPING_MEDIUM,
    0.20 as CAPPING_LOW
FROM enriched_offers
WHERE HAS_CORRIDOR = 1
    AND MATCH_TYPE = 'MASTER'
    AND UNIVERS = 'ZOOM1'

UNION ALL

-- Cubes NATIONAL
SELECT DISTINCT
    UNIVERS,
    'NATIONAL' as TYPE_CLIENT,
    'NATIONAL' as TYPE_RESTAURANT,
    'NATIONAL' as GEO,
    'NATIONAL' as CUBE_TYPE,
    0.03 as CAPPING_HIGH,   -- Plus conservateur
    0.10 as CAPPING_MEDIUM,
    0.15 as CAPPING_LOW
FROM enriched_offers
WHERE HAS_CORRIDOR = 1
    AND MATCH_TYPE = 'NATIONAL'
    AND UNIVERS = 'ZOOM1'

-- Mise à jour des cubes MASTER
UPDATE PT2QE_CAPPING_CUBES
SET CAPPING_HIGH = :high,
    CAPPING_MEDIUM = :medium,
    CAPPING_LOW = :low
WHERE TYPE_CLIENT = :type_client
    AND CUBE_TYPE = 'MASTER'

MERGE INTO PT2QE_RECOMMENDATIONS r
USING TEMP_CAPPING_CORRECTIONS c
ON (
    r.UNIVERS = c.UNIVERS
    AND r.TYPE_CLIENT = c.TYPE_CLIENT
    AND r.TYPE_RESTAURANT = c.TYPE_RESTAURANT
    AND NVL(r.GEO, 'NULL') = NVL(c.GEO, 'NULL')
)
WHEN MATCHED THEN UPDATE SET
    -- Recalcul RECO1_APRES_CAPPING_SENSIBILITE
    r.RECO1_APRES_CAPPING_SENSIBILITE = CASE
        WHEN r.PRICE_SENSITIVITY = 'HIGH' AND c.CAPPING_HIGH IS NOT NULL THEN
            LEAST(r.RECO1_BASE, r.PRIX_TARIF_ACTUEL * (1 + c.CAPPING_HIGH))
        -- ... (idem pour MEDIUM et LOW)
    END,

    -- Recalcul RECO1_AVEC_CAPPING (cascade avec basiques)
    r.RECO1_AVEC_CAPPING = CASE
        WHEN r.LC_ATTRIBUT = 'Basiques' THEN
            LEAST([nouvelle RECO1_APRES_CAPPING_SENSIBILITE], r.PRIX_TARIF_ACTUEL * 1.50)
        ELSE [nouvelle RECO1_APRES_CAPPING_SENSIBILITE]
    END,

    -- Recalcul PRIX_RECOMMANDE (selon DECISION_PATH)
    r.PRIX_RECOMMANDE = CASE
        WHEN r.DECISION_PATH = 'PAS_BAISSE_GEL_PRIX' 
        THEN r.PRIX_TARIF_ACTUEL

        WHEN r.DECISION_PATH = 'PL1_CONSERVATION_PREMIUM'
        THEN LEAST(GREATEST(r.PRIX_TARIF_ACTUEL, r.NEW_BORNE_PL2_PL3), r.NEW_PRB)

        WHEN r.DECISION_PATH = 'OPTIMISATION_STANDARD'
        THEN LEAST(GREATEST([nouvelle RECO1_AVEC_CAPPING], r.RECO2), r.NEW_PRB)
    END,

    -- Recalcul PCT_HAUSSE_FINALE
    r.PCT_HAUSSE_FINALE = (r.PRIX_RECOMMANDE - r.PRIX_TARIF_ACTUEL) / r.PRIX_TARIF_ACTUEL,

    -- Mise à jour flags de capping
    r.CAPPING_APPLIED = [recalcul complet],
    r.RECO_SELECTIONNEE = [recalcul],

    r.CALCULATION_DATE = SYSDATE

-- Par TYPE_CLIENT
SELECT
    'TYPE_CLIENT' as DIMENSION,
    TYPE_CLIENT as VALEUR,
    COUNT(*) as NB_OFFRES,
    COUNT(DISTINCT ID_CLN) as NB_CLIENTS,
    COUNT(DISTINCT ID_ART) as NB_ARTICLES,
    AVG(PRIX_TARIF_ACTUEL) as PRIX_MOY_ACTUEL,
    AVG(PRIX_RECOMMANDE) as PRIX_MOY_RECOMMANDE,
    AVG(PCT_HAUSSE_FINALE) * 100 as PCT_HAUSSE_MOY,
    MIN(PCT_HAUSSE_FINALE) * 100 as PCT_HAUSSE_MIN,
    MAX(PCT_HAUSSE_FINALE) * 100 as PCT_HAUSSE_MAX,
    STDDEV(PCT_HAUSSE_FINALE) * 100 as PCT_HAUSSE_STDDEV
FROM PT2QE_RECOMMENDATIONS
GROUP BY TYPE_CLIENT

-- Idem pour TYPE_RESTAURANT et UNIVERS

WITH IMPACT_CALC AS (
    SELECT
        TYPE_CLIENT,
        UNIVERS,
        COUNT(*) as NB_OFFRES,
        SUM(PRIX_TARIF_ACTUEL) as CA_ACTUEL,
        SUM(PRIX_RECOMMANDE) as CA_FUTUR,
        AVG(PCT_HAUSSE_FINALE) as HAUSSE_MOY,

        -- Distribution par tranche
        SUM(CASE WHEN PCT_HAUSSE_FINALE = 0 THEN 1 ELSE 0 END) as NB_SANS_HAUSSE,
        SUM(CASE WHEN PCT_HAUSSE_FINALE > 0 AND PCT_HAUSSE_FINALE <= 0.02 THEN 1 ELSE 0 END) as NB_0_2PCT,
        SUM(CASE WHEN PCT_HAUSSE_FINALE > 0.02 AND PCT_HAUSSE_FINALE <= 0.05 THEN 1 ELSE 0 END) as NB_2_5PCT,
        -- ... (autres tranches)

    FROM PT2QE_RECOMMENDATIONS
    GROUP BY TYPE_CLIENT, UNIVERS
)
SELECT
    TYPE_CLIENT,
    UNIVERS,
    NB_OFFRES,
    CA_ACTUEL,
    CA_FUTUR,
    (CA_FUTUR - CA_ACTUEL) as IMPACT_EUROS,
    ((CA_FUTUR - CA_ACTUEL) / CA_ACTUEL * 100) as IMPACT_PCT,
    HAUSSE_MOY * 100 as HAUSSE_MOY_PCT,

    -- Comptages
    NB_SANS_HAUSSE,
    NB_0_2PCT,
    NB_2_5PCT,
    -- ...

    -- Pourcentages
    (NB_SANS_HAUSSE * 100.0 / NB_OFFRES) as PCT_SANS_HAUSSE,
    (NB_0_2PCT * 100.0 / NB_OFFRES) as PCT_0_2,
    -- ...

FROM IMPACT_CALC

TRANCHE_HAUSSE = CASE
    WHEN PCT_HAUSSE_FINALE = 0 THEN '00. Pas de hausse'
    WHEN PCT_HAUSSE_FINALE <= 0.02 THEN '01. 0-2%'
    WHEN PCT_HAUSSE_FINALE <= 0.05 THEN '02. 2-5%'
    WHEN PCT_HAUSSE_FINALE <= 0.07 THEN '03. 5-7%'
    WHEN PCT_HAUSSE_FINALE <= 0.10 THEN '04. 7-10%'
    WHEN PCT_HAUSSE_FINALE <= 0.12 THEN '05. 10-12%'
    WHEN PCT_HAUSSE_FINALE <= 0.15 THEN '06. 12-15%'
    WHEN PCT_HAUSSE_FINALE <= 0.17 THEN '07. 15-17%'
    WHEN PCT_HAUSSE_FINALE <= 0.20 THEN '08. 17-20%'
    ELSE '09. Plus de 20%'
END

SELECT
    TRANCHE_HAUSSE,
    COUNT(*) as NB_OFFRES,
    COUNT(DISTINCT ID_CLN) as NB_CLIENTS_UNIQUES,
    COUNT(DISTINCT ID_ART) as NB_ARTICLES_UNIQUES,
    AVG(PRIX_TARIF_ACTUEL) as PRIX_MOY_ACTUEL,
    AVG(PRIX_RECOMMANDE) as PRIX_MOY_RECOMMANDE,
    MIN(PCT_HAUSSE_FINALE) * 100 as HAUSSE_MIN_PCT,
    MAX(PCT_HAUSSE_FINALE) * 100 as HAUSSE_MAX_PCT,
    AVG(PCT_HAUSSE_FINALE) * 100 as HAUSSE_MOY_PCT,

    -- Pourcentages calculés en post-traitement
    (NB_OFFRES / SUM(NB_OFFRES) OVER() * 100) as PCT_OFFRES,
    SUM(NB_OFFRES) OVER(ORDER BY TRANCHE_HAUSSE) / SUM(NB_OFFRES) OVER() * 100 as PCT_CUMULE
FROM [...]
GROUP BY TRANCHE_HAUSSE
ORDER BY TRANCHE_HAUSSE

SELECT
    -- Dimensions
    DECISION_PATH,
    RECO_SELECTIONNEE,

    -- Volumétrie
    COUNT(*) as NB_OFFRES,
    COUNT(DISTINCT ID_CLN) as NB_CLIENTS,
    COUNT(DISTINCT ID_ART) as NB_ARTICLES,

    -- Hausses
    AVG(PCT_HAUSSE_FINALE) * 100 as HAUSSE_MOY_PCT,
    MEDIAN(PCT_HAUSSE_FINALE) * 100 as HAUSSE_MEDIANE_PCT,

    -- Distribution des cappings
    SUM(CASE WHEN CAPPING_APPLIED = 'GEL_PAS' THEN 1 ELSE 0 END) as NB_CAP_GEL,
    SUM(CASE WHEN CAPPING_APPLIED = 'PRB_FINAL' THEN 1 ELSE 0 END) as NB_CAP_PRB,
    SUM(CASE WHEN CAPPING_APPLIED = 'PLANCHER_PL2_PL3' THEN 1 ELSE 0 END) as NB_CAP_PLANCHER,
    SUM(CASE WHEN CAPPING_APPLIED = 'BASIQUES_50PCT' THEN 1 ELSE 0 END) as NB_CAP_BASIQUES,
    SUM(CASE WHEN CAPPING_APPLIED = 'SENSIBILITE' THEN 1 ELSE 0 END) as NB_CAP_SENSIBILITE,
    SUM(CASE WHEN CAPPING_APPLIED = 'NONE' THEN 1 ELSE 0 END) as NB_SANS_CAPPING

FROM PT2QE_RECOMMENDATIONS
GROUP BY DECISION_PATH, RECO_SELECTIONNEE
ORDER BY DECISION_PATH, RECO_SELECTIONNEE

SELECT
    CAPPING_APPLIED,
    DECISION_PATH,
    RECO_SELECTIONNEE,
    COUNT(*) as NB_OFFRES,
    AVG(PCT_HAUSSE_FINALE) * 100 as HAUSSE_MOY_PCT
FROM PT2QE_RECOMMENDATIONS
GROUP BY CAPPING_APPLIED, DECISION_PATH, RECO_SELECTIONNEE
ORDER BY CAPPING_APPLIED, DECISION_PATH, RECO_SELECTIONNEE

SELECT
    -- Identifiants
    ID_CLN,
    LC_CLN,
    ID_ART,
    LC_ART,

    -- Dimensions
    TYPE_CLIENT,
    TYPE_RESTAURANT,
    GEO,
    UNIVERS,

    -- Infos condition
    ID_CND,
    TO_CHAR(DT_DEB_CONDITION, 'DD/MM/YYYY') as DT_DEB_CONDITION,
    TO_CHAR(DT_FIN_CONDITION, 'DD/MM/YYYY') as DT_FIN_CONDITION,

    -- Prix
    ROUND(PRIX_TARIF_ACTUEL, 3) as PRIX_TARIF_ACTUEL,
    ROUND(PRIX_RECOMMANDE, 3) as NOUVEAU_PRIX,

    -- Impact
    ROUND(PCT_HAUSSE_FINALE * 100, 2) as PCT_HAUSSE,
    ROUND(PRIX_RECOMMANDE - PRIX_TARIF_ACTUEL, 3) as HAUSSE_EUROS,

    -- Les 3 positions
    POSITION_TARIF_ACTUEL_DANS_ANCIENNES_BORNES,
    PALIER_TARIF_ACTUEL_VS_NOUVELLES_BORNES,
    POSITION_NOUVEAU_PRIX_DANS_NOUVELLES_BORNES,

    -- Contexte
    PRICE_SENSITIVITY,
    CASE
        WHEN PRIX_RECOMMANDE = RECO1_AVEC_CAPPING THEN 'REPOSITIONNEMENT'
        WHEN PRIX_RECOMMANDE = RECO2 THEN 'HAUSSE_PROPORTIONNELLE'
        ELSE 'AUTRE'
    END as TYPE_HAUSSE,

    -- Métadonnées
    TO_CHAR(CALCULATION_DATE, 'DD/MM/YYYY HH24:MI:SS') as DATE_CALCUL

FROM PT2QE_RECOMMENDATIONS
WHERE PRIX_RECOMMANDE IS NOT NULL
ORDER BY ID_CLN, ID_ART