SELECT
    A.AR_Ref,
    A.AR_Design,
    F.FA_Intitule,
    L.DE_No              AS Depot,
    L.LS_NoSerie         AS LotSerie,
    L.LS_Fabrication     AS DateFabrication,
    L.LS_Peremption      AS DatePeremption,
    L.LS_QteRestant      AS QteLotRestant,
    L.LS_QteRes          AS QteLotReservee,
    (L.LS_QteRestant - ISNULL(L.LS_QteRes,0)) AS QteLotDisponible,
    L.LS_LotEpuise,
    L.DL_NoIn,
    L.DL_NoOut,
    L.LS_MvtStock,
    L.LS_Complement
FROM dbo.F_ARTICLE A
JOIN dbo.F_FAMILLE  F ON F.FA_CodeFamille = A.FA_CodeFamille
JOIN dbo.F_LOTSERIE L ON L.AR_Ref = A.AR_Ref
WHERE UPPER(LTRIM(RTRIM(F.FA_Intitule))) IN (
    'HIGHLIGHT',
    'DIAMAND',
    'NATURAL',
    'CONTOUR',
    'SNOW WHITE',
    'GLOW',
    'ELITE',
    'ONE DAY',
    'CLAER VISION MENSUEL',
    'CLEAR VISION ONE DAY',
    'SEED1DAYPURE',
    'SEEDMONTHLYPURE'
)
AND ISNULL(L.LS_QteRestant,0) > 0
ORDER BY F.FA_Intitule, A.AR_Ref, L.DE_No, L.LS_NoSerie;