USP_APPEALMAILINGSETUP_GETLETTERS_UPDATEFROMXML

/*
Generated by Blackbaud AppFx Platform
Date:  11/30/2010 11:33:31 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=2.8.2022.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE PROCEDURE dbo.USP_APPEALMAILINGSETUP_GETLETTERS_UPDATEFROMXML 
(
@APPEALMAILINGSETUPID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@XML xml,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
)

as

set nocount on;

if @CHANGEAGENTID is null
    exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

if @CHANGEDATE is null 
    set @CHANGEDATE = getdate()

-- build a temporary table containing the values from the XML

declare @TempTbl table (
   [CHANNELCODE] tinyint,
   [CONSIDERREVENUEHISTORY] bit,
   [CONSTITUENTINCLUDECODE] tinyint,
   [EMAILPACKAGEID] uniqueidentifier,
   [EMAILSEGMENTID] uniqueidentifier,
   [HIGHREVENUEAMOUNT] money,
   [ID] uniqueidentifier,
   [LOWREVENUEAMOUNT] money,
   [MAILPACKAGEID] uniqueidentifier,
   [MAILSEGMENTID] uniqueidentifier,
   [NAME] nvarchar(100),
   [REVENUECRITERIACODE] tinyint,
   [SENDTOOPTIONCODE] tinyint,
   [SEQUENCE] int)

insert into @TempTbl select 
    [CHANNELCODE],
    [CONSIDERREVENUEHISTORY],
    [CONSTITUENTINCLUDECODE],
    [EMAILPACKAGEID],
    [EMAILSEGMENTID],
    [HIGHREVENUEAMOUNT],
    [ID],
    [LOWREVENUEAMOUNT],
    [MAILPACKAGEID],
    [MAILSEGMENTID],
    [NAME],
    [REVENUECRITERIACODE],
    [SENDTOOPTIONCODE],
    [SEQUENCE] 
from dbo.UFN_APPEALMAILINGSETUP_GETLETTERS_FROMITEMLISTXML(@XML)

update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

if @@Error <> 0
    return 1;

declare @contextCache varbinary(128);
declare @e int;

-- cache current context information 

set @contextCache = CONTEXT_INFO();

-- set CONTEXT_INFO to @CHANGEAGENTID 

if not @CHANGEAGENTID is null
    set CONTEXT_INFO @CHANGEAGENTID;

-- delete any items that no longer exist in the XML table

delete from dbo.[APPEALMAILINGSETUPLETTER] where [APPEALMAILINGSETUPLETTER].ID in 
    (select ID from dbo.UFN_APPEALMAILINGSETUP_GETLETTERS
    (
        @APPEALMAILINGSETUPID,
    @CURRENTAPPUSERID
    )
    EXCEPT select ID from @TempTbl)    

select @e=@@error;

-- reset CONTEXT_INFO to previous value 

if not @contextCache is null
    set CONTEXT_INFO @contextCache;

if @e <> 0
    return 2;

-- update the items that exist in the XML table and the db

update [APPEALMAILINGSETUPLETTER]
        set [APPEALMAILINGSETUPLETTER].[CHANNELCODE]=temp.[CHANNELCODE],
        [APPEALMAILINGSETUPLETTER].[CONSIDERREVENUEHISTORY]=temp.[CONSIDERREVENUEHISTORY],
        [APPEALMAILINGSETUPLETTER].[CONSTITUENTINCLUDECODE]=temp.[CONSTITUENTINCLUDECODE],
        [APPEALMAILINGSETUPLETTER].[EMAILPACKAGEID]=temp.[EMAILPACKAGEID],
        [APPEALMAILINGSETUPLETTER].[EMAILSEGMENTID]=temp.[EMAILSEGMENTID],
        [APPEALMAILINGSETUPLETTER].[HIGHREVENUEAMOUNT]=temp.[HIGHREVENUEAMOUNT],
        [APPEALMAILINGSETUPLETTER].[ID]=temp.[ID],
        [APPEALMAILINGSETUPLETTER].[LOWREVENUEAMOUNT]=temp.[LOWREVENUEAMOUNT],
        [APPEALMAILINGSETUPLETTER].[MAILPACKAGEID]=temp.[MAILPACKAGEID],
        [APPEALMAILINGSETUPLETTER].[MAILSEGMENTID]=temp.[MAILSEGMENTID],
        [APPEALMAILINGSETUPLETTER].[NAME]=temp.[NAME],
        [APPEALMAILINGSETUPLETTER].[REVENUECRITERIACODE]=temp.[REVENUECRITERIACODE],
        [APPEALMAILINGSETUPLETTER].[SENDTOOPTIONCODE]=temp.[SENDTOOPTIONCODE],
        [APPEALMAILINGSETUPLETTER].[SEQUENCE]=temp.[SEQUENCE],
        [APPEALMAILINGSETUPLETTER].CHANGEDBYID = @CHANGEAGENTID,
        [APPEALMAILINGSETUPLETTER].DATECHANGED = @CHANGEDATE

    from dbo.[APPEALMAILINGSETUPLETTER] inner join @TempTbl as [temp] on [APPEALMAILINGSETUPLETTER].ID = [temp].ID
    where ([APPEALMAILINGSETUPLETTER].[CHANNELCODE]<>temp.[CHANNELCODE]) or 
        ([APPEALMAILINGSETUPLETTER].[CHANNELCODE] is null and temp.[CHANNELCODE] is not null) or 
        ([APPEALMAILINGSETUPLETTER].[CHANNELCODE] is not null and temp.[CHANNELCODE] is null) or 
        ([APPEALMAILINGSETUPLETTER].[CONSIDERREVENUEHISTORY]<>temp.[CONSIDERREVENUEHISTORY]) or 
        ([APPEALMAILINGSETUPLETTER].[CONSIDERREVENUEHISTORY] is null and temp.[CONSIDERREVENUEHISTORY] is not null) or 
        ([APPEALMAILINGSETUPLETTER].[CONSIDERREVENUEHISTORY] is not null and temp.[CONSIDERREVENUEHISTORY] is null) or 
        ([APPEALMAILINGSETUPLETTER].[CONSTITUENTINCLUDECODE]<>temp.[CONSTITUENTINCLUDECODE]) or 
        ([APPEALMAILINGSETUPLETTER].[CONSTITUENTINCLUDECODE] is null and temp.[CONSTITUENTINCLUDECODE] is not null) or 
        ([APPEALMAILINGSETUPLETTER].[CONSTITUENTINCLUDECODE] is not null and temp.[CONSTITUENTINCLUDECODE] is null) or 
        ([APPEALMAILINGSETUPLETTER].[EMAILPACKAGEID]<>temp.[EMAILPACKAGEID]) or 
        ([APPEALMAILINGSETUPLETTER].[EMAILPACKAGEID] is null and temp.[EMAILPACKAGEID] is not null) or 
        ([APPEALMAILINGSETUPLETTER].[EMAILPACKAGEID] is not null and temp.[EMAILPACKAGEID] is null) or 
        ([APPEALMAILINGSETUPLETTER].[EMAILSEGMENTID]<>temp.[EMAILSEGMENTID]) or 
        ([APPEALMAILINGSETUPLETTER].[EMAILSEGMENTID] is null and temp.[EMAILSEGMENTID] is not null) or 
        ([APPEALMAILINGSETUPLETTER].[EMAILSEGMENTID] is not null and temp.[EMAILSEGMENTID] is null) or 
        ([APPEALMAILINGSETUPLETTER].[HIGHREVENUEAMOUNT]<>temp.[HIGHREVENUEAMOUNT]) or 
        ([APPEALMAILINGSETUPLETTER].[HIGHREVENUEAMOUNT] is null and temp.[HIGHREVENUEAMOUNT] is not null) or 
        ([APPEALMAILINGSETUPLETTER].[HIGHREVENUEAMOUNT] is not null and temp.[HIGHREVENUEAMOUNT] is null) or 
        ([APPEALMAILINGSETUPLETTER].[ID]<>temp.[ID]) or 
        ([APPEALMAILINGSETUPLETTER].[ID] is null and temp.[ID] is not null) or 
        ([APPEALMAILINGSETUPLETTER].[ID] is not null and temp.[ID] is null) or 
        ([APPEALMAILINGSETUPLETTER].[LOWREVENUEAMOUNT]<>temp.[LOWREVENUEAMOUNT]) or 
        ([APPEALMAILINGSETUPLETTER].[LOWREVENUEAMOUNT] is null and temp.[LOWREVENUEAMOUNT] is not null) or 
        ([APPEALMAILINGSETUPLETTER].[LOWREVENUEAMOUNT] is not null and temp.[LOWREVENUEAMOUNT] is null) or 
        ([APPEALMAILINGSETUPLETTER].[MAILPACKAGEID]<>temp.[MAILPACKAGEID]) or 
        ([APPEALMAILINGSETUPLETTER].[MAILPACKAGEID] is null and temp.[MAILPACKAGEID] is not null) or 
        ([APPEALMAILINGSETUPLETTER].[MAILPACKAGEID] is not null and temp.[MAILPACKAGEID] is null) or 
        ([APPEALMAILINGSETUPLETTER].[MAILSEGMENTID]<>temp.[MAILSEGMENTID]) or 
        ([APPEALMAILINGSETUPLETTER].[MAILSEGMENTID] is null and temp.[MAILSEGMENTID] is not null) or 
        ([APPEALMAILINGSETUPLETTER].[MAILSEGMENTID] is not null and temp.[MAILSEGMENTID] is null) or 
        ([APPEALMAILINGSETUPLETTER].[NAME]<>temp.[NAME]) or 
        ([APPEALMAILINGSETUPLETTER].[NAME] is null and temp.[NAME] is not null) or 
        ([APPEALMAILINGSETUPLETTER].[NAME] is not null and temp.[NAME] is null) or 
        ([APPEALMAILINGSETUPLETTER].[REVENUECRITERIACODE]<>temp.[REVENUECRITERIACODE]) or 
        ([APPEALMAILINGSETUPLETTER].[REVENUECRITERIACODE] is null and temp.[REVENUECRITERIACODE] is not null) or 
        ([APPEALMAILINGSETUPLETTER].[REVENUECRITERIACODE] is not null and temp.[REVENUECRITERIACODE] is null) or 
        ([APPEALMAILINGSETUPLETTER].[SENDTOOPTIONCODE]<>temp.[SENDTOOPTIONCODE]) or 
        ([APPEALMAILINGSETUPLETTER].[SENDTOOPTIONCODE] is null and temp.[SENDTOOPTIONCODE] is not null) or 
        ([APPEALMAILINGSETUPLETTER].[SENDTOOPTIONCODE] is not null and temp.[SENDTOOPTIONCODE] is null) or 
        ([APPEALMAILINGSETUPLETTER].[SEQUENCE]<>temp.[SEQUENCE]) or 
        ([APPEALMAILINGSETUPLETTER].[SEQUENCE] is null and temp.[SEQUENCE] is not null) or 
        ([APPEALMAILINGSETUPLETTER].[SEQUENCE] is not null and temp.[SEQUENCE] is null)

if @@Error <> 0
    return 3;    

-- insert new items

insert into [APPEALMAILINGSETUPLETTER] 
    ([APPEALMAILINGSETUPID], 
    [CHANNELCODE],
    [CONSIDERREVENUEHISTORY],
    [CONSTITUENTINCLUDECODE],
    [EMAILPACKAGEID],
    [EMAILSEGMENTID],
    [HIGHREVENUEAMOUNT],
    [ID],
    [LOWREVENUEAMOUNT],
    [MAILPACKAGEID],
    [MAILSEGMENTID],
    [NAME],
    [REVENUECRITERIACODE],
    [SENDTOOPTIONCODE],
    [SEQUENCE],                
    ADDEDBYID, 
    CHANGEDBYID, 
    DATEADDED, 
    DATECHANGED)
select @APPEALMAILINGSETUPID, 
    [CHANNELCODE],
    [CONSIDERREVENUEHISTORY],
    [CONSTITUENTINCLUDECODE],
    [EMAILPACKAGEID],
    [EMAILSEGMENTID],
    [HIGHREVENUEAMOUNT],
    [ID],
    [LOWREVENUEAMOUNT],
    [MAILPACKAGEID],
    [MAILSEGMENTID],
    [NAME],
    [REVENUECRITERIACODE],
    [SENDTOOPTIONCODE],
    [SEQUENCE], 
    @CHANGEAGENTID, 
    @CHANGEAGENTID, 
    @CHANGEDATE, 
    @CHANGEDATE
from @TempTbl as [temp]
where not exists (select ID from dbo.[APPEALMAILINGSETUPLETTER] as data where data.ID = [temp].ID)

if @@Error <> 0
    return 4;

return 0;