--exec [C6StagingPeriodico].[test_compl_testo_sezione_adeg_PG_PROFESS_CURR_DTTRIM] 'F', 'MGNGPP55B25G224T'
CREATE procedure [C6StagingPeriodico].[test_compl_testo_sezione_adeg_PG_PROFESS_CURR_DTTRIM]
	@Rete nvarchar(1),
	@CodiceFiscale varchar(16)
AS
BEGIN
DECLARE @d nvarchar(8)
DECLARE @isPG integer
DECLARE @isPG_NQ char(1)
DECLARE @dtrim nvarchar(8)
set @dtrim = c6martperiodico.getTrimestre(getdate(), 0)
if @CodiceFiscale not like 'FF@%' 
	BEGIN
	select @isPG = FLAGPG from [C6StagingPeriodico].[WSEIAN2]
	WHERE codfis = @CodiceFiscale and RETE = @Rete 
	and DTTRIM=@dtrim
	if @isPG = 0
	BEGIN
		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.[FLAGPG]=adeg.[FLAGPG] and an.[FLAGNQP]=adeg.[FLAGNQP] and isNULL(an.[FLGPRLRDE],'')=isNULL(adeg.[FLGPRLRDE],'') 
		WHERE codfis  = @CodiceFiscale and RETE = @Rete
		and DTTRIM=@dtrim
	END
	ELSE /* isPG = 1 */
	BEGIN
		select @isPG_NQ = 		
		   CASE 
				WHEN FLAGNQP IS NULL or FLAGNQP ='' THEN 'N'
				ELSE FLAGNQP
			END 
		from [C6StagingPeriodico].[WSEIAN2]
		WHERE codfis = @CodiceFiscale and RETE = @Rete 
		and DTTRIM=@dtrim
		if @isPG_NQ = 'S'
		BEGIN
			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=@dtrim
		END
		ELSE  /* isPG_NQ = N */
		BEGIN
			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 adeg.[FLAGNQP]=@isPG_NQ
			WHERE codfis  = @CodiceFiscale and RETE = @Rete
			and DTTRIM=@dtrim
			--and an.[FLAGNQP]='N' and coalesce(adeg.[FLGPRLRDE],'yn')  like '%' + coalesce(an.[FLGPRLRDE],'') + '%'
		END
	END
	END
ELSE /* Mandatario - Fiduciaria */
	BEGIN
	DECLARE @CF as nvarchar(max)
	set @CF = replace(@CodiceFiscale, 'FF@', '')
	/* case PG = 0 forced, dont' considerate flags FLAGPG, FLAGNQP, FLGPRLRDE */
	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 RETE = @Rete
	and DTTRIM=@dtrim
	END
END