CREATE procedure [dbo].[UTIL_ESTRAI_COMPLEX_MODIFICATA]
as
select C.RETE,C.COD_FISCALE,D.EXPERIENCE,A.COD_INTERNO,A.COD_SOTTOPRODOTTO,C.TIPO_PRODOTTO,A.COD_ISIN,A.COMPLEX AS NEW_COMPLEX,
		B.COMPLEX AS OLD_COMPLEX		
from c6stagingperiodico.catitemref a
join c6staging.catitemref b --catalogo del giornaliero con complessità non modificata
on a.catalogueId = b.catalogueId
join c6martperiodico.patrimonio_bf c
on c.cod_prodotto = a.catalogueId
join c6martperiodico.mifid d
on c.cod_fiscale = d.cod_fiscale and c.rete = d.rete
where
	A.COD_INTERNO IN
	(
	'18',
	'19',
	'29',
	'35',
	'37',
	'38',
	'48',
	'49',
	'60',
	'BT',
	'ES',
	'FR',
	'FS',
	'FT',
	'FU',
	'GP',
	'KP',
	'OA',
	'PO',
	'PY',
	'SB',
	'PR',
	'PX',
	'SE',
	'SF',
	'SI',
	'ST',
	'SU',
	'K1',
	'K2',
	'K3',
	'K4',
	'K5',
	'K6',
	'K7'
	)
	OR
	(A.COD_INTERNO IN
		(
		'S1',
		'S2',
		'S3',
		'S4',
		'S5',
		'S6',
		'S7',
		'S8'
		)
		and A.cod_sottoprodotto not in ('20001','20002','20005','20008')
	)
	OR
	(	A.cod_interno in ('S1','S2','S4','S5','S8')
		and A.cod_sottoprodotto in ('20001','20002','20005','20008')
	)
	OR
	A.COD_INTERNO IN 
	(
	'A7',
	'A8'
	)
	OR
	A.COD_SOTTOPRODOTTO IN 
	(
	 '7149S'
	,'7145S'
	,'9968S'
	)
	OR
	A.COD_SOTTOPRODOTTO = '7146S'
	OR
	(
	A.COD_ISIN IN
	(
	 'DE000A0LJ552'
	,'DE000A0MG8R0'
	,'IT0004189368'
	,'GB00B1WVWP64'
	,'GB00B1Y3ZZ16'
	,'XS0354254111'
	,'GB00B2QM7Y61'
	,'XS0235278982'
	,'XS0179585186'
	)
	AND
	A.COD_INTERNO IN
	(
	 '00101740'
	,'09139480'
	,'09161420'
	,'00830060'
	,'09205930'
	,'00168130'
	,'08525040'
	,'08556110'
	,'09201230'
	)
	)
ORDER BY C.RETE,C.COD_FISCALE,D.EXPERIENCE desc