1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
| SELECT aff.Code_Affaire
, sit.Code_Site
, aff.code_agence
, cep.EtatPro
, sit.ddeb_chantier
, sit.dfin_chantier
, aff.chargedaffaire
, aff.IntituleAffaire
, aff.TypeConvention
FROM Taffaire AS aff
LEFT OUTER JOIN
TSites AS sit
ON aff.Code_Affaire = sit.Code_Affaire
AND sit.ddeb_chantier >=
( SELECT DateDebChantierRef
FROM entreprise
)
LEFT OUTER JOIN
( SELECT gep.Code_Affaire
, gep.Code_site
, MAX(gep.EtatProjet) AS EtatPro
FROM GChangementEtatProjet AS gep
WHERE gep.DateFinEtat IS NULL
GROUP BY gep.Code_Affaire
, gep.Code_site
) AS cep
ON sit.Code_Affaire = cep.Code_Affaire
AND sit.Code_Site = cep.Code_site
WHERE cep.EtatPro = 'ENC'
AND aff.TypeConvention IN ('CPLQ', 'CPLT', 'CTEX')
AND aff.AnneeCvt >=
( SELECT AnneeCVTRef
FROM entreprise
)
AND EXISTS
( SELECT NULL
FROM GChangementEtatConvention AS gec
WHERE gec.EtatConvention NOT IN ('ANL', 'RSL')
AND gec.DateFinEtat IS NULL
AND gec.Code_Affaire = aff.Code_Affaire
)
AND NOT EXISTS
( SELECT NULL
FROM TuserSite AS usi
WHERE usi.Code_Affaire = aff.Code_Affaire
AND usi.Code_Site = sit.Code_Site
)
; |
Partager