-- Stored procedure
CREATE PROCEDURE [C6MartPeriodicoImmobiliare].[sp_caricaBlacklist]
AS
BEGIN
 Declare @dtTrime as decimal;  
 set @dtTrime=convert(decimal,c6martperiodico.gettrimestre2(getdate(),0))  
 truncate table C6MartPeriodicoImmobiliare.blacklist
 insert into C6MartPeriodicoImmobiliare.blacklist
 select distinct
	   Rete
      ,case when codman <> '' then 'FF@' + CODMAN
	        when len(codfis)=11 then NULL else codfis end as codiceFiscale
	  ,case when LEN(codfis) =11 and codman='' then codfis else NULL end as Piva
	  ,case when codman<>'' then codman else NULL end as Codman
		,case when tipolett = '2' then 'BLACKLIST RFA MINUS CLIENTE SEI E IMMOBILIARE' 
			when tipolett = '1' then 'BLACKLIST RFA MINUS CLIENTE IMMOBILIARE' END AS  DescrErr
      ,getdate() as [DataIns]
     -- ,1 as [Bloccato]
from c6stagingPeriodico.wseidc 
where dttrim = @dtTrime and (tipolett='2' or TIPOLETT='1')
insert into C6MartPeriodicoImmobiliare.blacklist
select distinct 
	   ps.Rete
      ,case when codman <> '' then 'FF@' + CODMAN
	        when len(ps.codfis)=11 then NULL else ps.codfis end as codiceFiscale
	  ,case when LEN(ps.codfis) =11 and codman='' then ps.codfis else NULL end as Piva
	  ,case when codman<>'' then codman else NULL end as Codman
	, 'Profilo Scaduto' as DescrErr 
    , getdate() as [DataIns]
    -- , 1 as [Bloccato]
from c6stagingPeriodico.wseips ps
     join c6martperiodicoimmobiliare.w6imm imm on imm.RETE = ps.RETE and imm.CODFIS = ps.CODFIS
where 1=1
  and dttrim = @dtTrime 
  and imm.flag_in_perimetro='S' 
exec [C6MartPeriodicoImmobiliare].[Blacklist_Blocco_Tabellone] 'blocco' --aggiorna tabellone
END