DISPO

SELECT  lp.numcdebes, 
            lp.numlgnbes, 
            lp.cdpdt, 
            ISNULL(lp.qteservie,0), 
            SUM(ISNULL(la.qterecptmag,0)), 
            c.cdorigcde,		 
            c.cdtyppricde	
    FROM	cde_bes_client c,lgn_cde_bes_client_pdt lp,lgn_appro la
    WHERE c.cdorigcde = 'CLP'
            AND cdcdeexp = 0 
            AND	cdstatcdebescli = 'V' 
            AND	la.cdstatmail = NULL 
            AND ISNULL(cdenvmailmad,0) = 0 
            and c.numcdebes = lp.numcdebes
            AND la.numcdebes = lp.numcdebes
            AND la.numlgnbes = lp.numlgnbes
            AND ((cdtyppricde = 'I' AND numcdeweb <> NULL AND cdlgnweb = 1)
            OR  (cdtyppricde <> 'I' AND cdmodprevcli = 'M' AND EXISTS (SELECT 1 FROM email WHERE idemail = c.idprevenance)))
        GROUP BY lp.numcdebes, lp.numlgnbes,lp.cdpdt, lp.qteservie, c.cdorigcde, c.cdtyppricde  

retard

SELECT cbc.numcdebes,
        e.cdimail,
        "RETARD",
        0
FROM cde_bes_client cbc,
     email e
WHERE  cbc.cdorigcde = "CLP"
    AND ISNULL(cbc.cdstatcdebescli,'')="V"
    AND ISNULL(cbc.dtpromessecli,'19010101') >= '20130318'
    AND DATEADD(dd,ISNULL(@NBJ,0),ISNULL(cbc.dtpromessecli,'19010101')) < @DATEJ
    AND ISNULL(cbc.cdstatutmail,'') <> "RETARD"
    AND ISNULL(cdpartdecitre,'') NOT IN ('914','915')
    AND ISNULL(cdtyppricde,'') NOT IN ('I','PM')
    AND ISNULL(cdmodprevcli,'') = "M"
    AND e.idemail = cbc.idprevenance
    AND EXISTS (SELECT 1 
                FROM lgn_cde_bes_client_pdt lcbcp
                WHERE lcbcp.numcdebes = cbc.numcdebes 
                AND lcbcp.cdstatlgnbescli = "V"
                AND ISNULL(lcbcp.qtecde,0) >  (SELECT ISNULL(SUM(la.qterecptmag), 0) 
                                                FROM lgn_appro la
                                                WHERE la.numcdebes = lcbcp.numcdebes
                                                AND lcbcp.numlgnbes = la.numlgnbes))
    AND NOT EXISTS (SELECT 1 FROM env_email_applicatifs WHERE numcdebes = cbc.numcdebes AND cdtypemail="RETARD")
    AND NOT EXISTS (SELECT 1 FROM exclu_cde_pr_mail_appli WHERE numcdebes = cbc.numcdebes)
    AND isnull(e.cdnpai,0) = 0
UNION
    SELECT cbc.numcdebes,
        isnull((SELECT cdimail FROM client_marque_blanche WHERE numclient = cbc.numclient and idwebsite = cbc.idwebsite),
           dbo.getEmailByNumcdebes(NULL,cbc.numclient)),
        "RETARD",
        0
    FROM cde_bes_client cbc
    WHERE  cbc.cdorigcde = "CLP"
        AND ISNULL(cbc.cdstatcdebescli,'')="V"
        AND ISNULL(cbc.dtpromessecli,'19010101') >= '20130318'
        AND DATEADD(dd,ISNULL(@NBJ,0),ISNULL(cbc.dtpromessecli,'19010101')) < @DATEJ
        AND ISNULL(cbc.cdstatutmail,'') <> "RETARD"
        AND ISNULL(cdpartdecitre,'') NOT IN ('914','915')
        AND ISNULL(cdtyppricde,'') IN ('I','PM')
        AND EXISTS (SELECT 1 
                    FROM lgn_cde_bes_client_pdt lcbcp
                    WHERE lcbcp.numcdebes = cbc.numcdebes 
                    AND lcbcp.cdstatlgnbescli = "V"
                    AND ISNULL(lcbcp.qtecde,0) >  (SELECT ISNULL(SUM(la.qterecptmag), 0) 
                                                    FROM lgn_appro la
                                                    WHERE la.numcdebes = lcbcp.numcdebes
                                                    AND lcbcp.numlgnbes = la.numlgnbes))
    AND NOT EXISTS (SELECT 1 FROM env_email_applicatifs WHERE numcdebes = cbc.numcdebes AND cdtypemail="RETARD")
    AND NOT EXISTS (SELECT 1 FROM exclu_cde_pr_mail_appli WHERE numcdebes = cbc.numcdebes)

SUICDEMAG

SELECT DISTINCT wsc.numcdebes,
                cbc.cdorigcde,
                cbc.cdsite,
                cbc.idwebsite,
                cbc.cdmodreglt
        FROM web_suivi_cde wsc, 
            cde_bes_client cbc(index pk_cde_bes_client)
        WHERE wsc.numcdebes = cbc.numcdebes
        AND cbc.cdorigcde = "CLP"
        AND cbc.cdtyppricde NOT IN ("I","PM","E")
        AND cbc.cdstatcdebescli <>"E"
        AND ISNULL(wsc.cdenregtraite,0) = 2
        AND NOT EXISTS (SELECT 1 FROM exclu_cde_pr_mail_appli WHERE numcdebes = wsc.numcdebes)