create view OP_DATI_IMMOB as
select ric.ANNOBD, ric.NUMRIC, ui.*,
	(SELECT coalesce((SELECT SUM (C.IMP_CAUS) FROM IC_BOLL B, IC_CAUSBOL C 
		WHERE B.C_UTENTE=ric.c_utente AND B.ENTE=C.ENTE AND B.NR_BOLL=C.NR_BOLL AND B.TIPO_CONTR=C.TIPO_CONTR AND 
		B.D_SCAD <=CONVERT(varchar(8), getdate(), 112) ) - 
		(SELECT SUM(S.IMP_PAG) FROM IC_INCA I, IC_CAUSINC S 
		WHERE I.ENTE=S.ENTE AND I.NR_PAG=S.NR_PAG AND I.C_UTENTE=ric.c_utente and i.c_cassa <> 'ZA'),0)) IMP_MORO,
	(select top 1 coalesce(imp_canone,0) from ic_contr where ente=ric.ente and C_UTENTE=ric.C_UTENTE and d_fine='99991231')	IMP_CANONE
from OP_RICLAV ric
		inner join IC_UI ui on
			ric.ENTE=ui.ENTE and
			ric.C_IMMOB=ui.C_IMMOB and
			ric.c_ui=ui.c_ui
go

create view OP_INTERVENTI as
select a.ENTE, a.ANNOBD, a.NUMRIC, a.DTRLAV, a.C_IMMOB, a.C_UI, a.C_UTENTE, 
		a.CODINT+' - '+coalesce(c.DESCRI, 'Non codificato') INTERVENTO, 
		(case when b.ENTE is null then '---' else b.CODFOR+'-'+b.DESFOR end) FORNITORE, 
		(case when b.ENTE is null then '' else b.DTORDI end) DTORDI,
		a.NOTE
from OP_RICLAV a 
	LEFT JOIN OP_ORDINI b on b.ente = a.ente and b.annobd = a.annobd and b.numric = a.numric
	LEFT JOIN OP_TABINT_IN c on a.CODINT=c.CODTAB
go