select distinct c2.nom_p,
c1.adr1,c1.lcom||' ('||c1.ptt||')',
ltrim(to_char(x.solde,'999G999G999D99','nls_numeric_characters = '', ''')),
to_char(last_day(to_date(s.terme,'yyyymm')),'dd/mm/yyyy')
,sum(nvl(i.mtech,0))
from client c1, v_civilite c2, evenement e, dossier d, ensemble en, contentieux co, solde x, L7546_HI_00.hiql i, solter s
where c1.role = '1'
and c1.code = c2.code_client(+)
and e.code = c1.code
and e.rang = constante_rang
and d.code='constante_code'
and c1.code = d.clie
and substr(d.Code,1,4) = en.code
and co.code(+) = d.ctx
and x.doss = e.doss
and x.cmpt = '41110000'
and s.doss=e.doss
and s.ster>0
and s.terme in
(select min(max(terme)) from solter where doss=e.doss and ster>0 group by terme)
and i.doss=e.doss
and i.qaamm in (select max(qaamm) from L7546_HI_00.hiql where doss= e.doss)
and i.rubr in (select code from t_rubr where regr='charges')
group by c2.nom_p,
c1.adr1, c1.lcom||' ('||c1.ptt||')', ltrim(to_char(x.solde,'999G999G999D99','nls_numeric_characters = '', ''')),to_char(last_day(to_date(s.terme,'yyyymm')),'dd/mm/yyyy'
Partager