select
ue.ue_nodossier,
codval.R01B08CODEVALIDATION_CODE,
codval.R01B08CODEVALIDATION_NOM
from
taj_portraitficher01 r01
inner join taj_r01 tr01
on tr01.porfch_id = r01.PORFCH_IDPARTIESIMPLE
inner join TAJ_R01B08ENUMCODEVALIDATION codval
on codval.R01B08CODEVALIDATION_ID = tr01.R01B08CODEVALIDATION_ID
inner join taj_portraitfiche pf
on pf.porfch_id = r01.porfch_id
inner join taj_portraitdossier pd
on pd.pordos_Id = pf.pordos_Id
inner join taj_ue ue
on ue.ue_Id = pd.ue_Id
Where
ue.ue_noDossier < 390000
and
ue.ue_noDossier >= 380000
and not exists (
select 1
from taj_Portraitdossier pd2
where pd2.stados_Id = 'C122213ABFB56A4E92D324947630A083' -- En travail
and pd2.ue_Id = ue.ue_Id
)
and codval.R01B08CODEVALIDATION_CODE <> 'CONDO'
--and tr01.r01_b07nbetages = 1
group by
ue.ue_nodossier,
codval.R01B08CODEVALIDATION_CODE,
codval.R01B08CODEVALIDATION_NOM
order by
ue.ue_nodossier DESC;