--exec [C6StagingPeriodico].[test_compl_testo_sezione_adeg_PG_QPROF] 'F', '97080600154'
--exec [C6StagingPeriodico].[test_compl_testo_sezione_adeg_PG_QPROF] 'S', 'SGHDMN66M07G920Z'
--exec [C6StagingPeriodico].[test_compl_testo_sezione_adeg_PG_QPROF] 'F', '00118220524'
--exec [C6StagingPeriodico].[test_compl_testo_sezione_adeg_PG_QPROF] 'F', 'ZZLLNI57B06D245V'
--exec [C6StagingPeriodico].[test_compl_testo_sezione_adeg_PG_QPROF] 'S', 'LTTGRG68T04I324Z'
CREATE procedure [C6StagingPeriodico].[test_compl_testo_sezione_adeg_PG_QPROF_20170620]
	@Rete nvarchar(1),
	@CodiceFiscale varchar(16)
AS
BEGIN
DECLARE @d nvarchar(8)
if @CodiceFiscale not like 'FF@%' 
	BEGIN
	select @d = max(DTTRIM) from 
	[C6StagingPeriodico].[WSEIAN2] an
	INNER JOIN [C6StagingPeriodico].[CASISTICHE_ADEGUATEZZA_PG_QPROF] adeg
	ON [CLCOMPL]=[COMPLESSITA] and [FLGCONC]=[CONCENTRAZIONE]
	and an.[FLAGPG]=adeg.[FLAGPG] and an.[FLAGNQP]=adeg.[FLAGNQP] and isNULL(an.[FLGPRLRDE],'')=isNULL(adeg.[FLGPRLRDE],'') 
	WHERE codfis = @CodiceFiscale and RETE = @Rete 
	SELECT top(1) ISNULL([TESTO_NOTA],'aa') as testo_nova FROM 
	[C6StagingPeriodico].[WSEIAN2] an
	INNER JOIN [C6StagingPeriodico].[CASISTICHE_ADEGUATEZZA_PG_QPROF] adeg
	ON [CLCOMPL]=[COMPLESSITA] and [FLGCONC]=[CONCENTRAZIONE]
	and an.[FLAGPG]=adeg.[FLAGPG] and an.[FLAGNQP]=adeg.[FLAGNQP] and isNULL(an.[FLGPRLRDE],'')=isNULL(adeg.[FLGPRLRDE],'') 
	WHERE codfis  = @CodiceFiscale and RETE = @Rete
	and DTTRIM=@d
	END
ELSE /* Mandatario */
	BEGIN
	DECLARE @CF as nvarchar(max)
	set @CF = replace(@CodiceFiscale, 'FF@', '')
	select @d = max(DTTRIM) from 
	[C6StagingPeriodico].[WSEIAN2] an
	INNER JOIN [C6StagingPeriodico].[CASISTICHE_ADEGUATEZZA_PG_QPROF] adeg
	ON [CLCOMPL]=[COMPLESSITA] and [FLGCONC]=[CONCENTRAZIONE]
	and an.[FLAGPG]=adeg.[FLAGPG] and an.[FLAGNQP]=adeg.[FLAGNQP] and isNULL(an.[FLGPRLRDE],'')=isNULL(adeg.[FLGPRLRDE],'') 
	WHERE codman = @CF
	SELECT top(1) ISNULL([TESTO_NOTA],'aa') as testo_nova FROM 
	[C6StagingPeriodico].[WSEIAN2] an
	INNER JOIN [C6StagingPeriodico].[CASISTICHE_ADEGUATEZZA_PG_QPROF] adeg
	ON [CLCOMPL]=[COMPLESSITA] and [FLGCONC]=[CONCENTRAZIONE] 
	and an.[FLAGPG]=adeg.[FLAGPG] and an.[FLAGNQP]=adeg.[FLAGNQP] and isNULL(an.[FLGPRLRDE],'')=isNULL(adeg.[FLGPRLRDE],'') 
	WHERE  codman = @CF 
	and DTTRIM=@d
	END
END