CREATE procedure [C6Mart].[InvioLetteraBenvenuto_ExcludedListApply]
as
begin
begin transaction
begin try
--taking documents to update
select distinct rete, codicefiscale, idreport 
into #tmp
from [C6Mart].[GESTIONE_PDF_FTP] p
where p.[DataInvio] is NULL and p.[FlagInvio] = 'N' and p.id_fk_zip = -1
  and CodiceFiscale in (select distinct CodiceFiscale from [C6Mart].[InvioLetteraBenvenuto_excludedlist] )
--'excluding' records from GESTIONE_PDF_FTP
update p
set p.[DataInvio] = getdate()
	,p.[FlagInvio] = 'S'
	,p.[id_fk_zip] = 999999
from [C6Mart].[GESTIONE_PDF_FTP] p
	inner join #tmp c on  c.codicefiscale = p.codicefiscale
	and c.rete = p.rete 
	and c.idreport = p.idreport
--updating excludedlist, setting data_aggriogamento for records affected
update e
set e.Data_aggiornamento = getdate()
from [C6Mart].[InvioLetteraBenvenuto_excludedlist] e
 inner join #tmp c on  c.codicefiscale = e.codicefiscale
commit transaction
end try
begin catch	
	DECLARE @Msg NVARCHAR(MAX)  
    SELECT @Msg=ERROR_MESSAGE() 
	rollback transaction
	RAISERROR('Error Occured: %s', 20, 101,@msg) --WITH LOG
end catch
end