USP_MKTASKLADDER_GETITEMLIST_UPDATEFROMXML

/*
Generated by Blackbaud AppFx Platform
Date:  11/30/2010 11:31:57 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=2.8.2022.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE PROCEDURE dbo.USP_MKTASKLADDER_GETITEMLIST_UPDATEFROMXML 
(
@ASKLADDERID 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 (
   [BASECURRENCYID] uniqueidentifier,
   [ID] uniqueidentifier,
   [ITEMVALUE1] decimal(20, 4),
   [ITEMVALUE2] decimal(20, 4),
   [ITEMVALUE3] decimal(20, 4),
   [ITEMVALUE4] decimal(20, 4),
   [ITEMVALUE5] decimal(20, 4),
   [MINIMUMENTRYAMOUNT] decimal(20, 4),
   [ORGANIZATIONEXCHANGERATEID] uniqueidentifier,
   [ORGANIZATIONITEMVALUE1] decimal(20, 4),
   [ORGANIZATIONITEMVALUE2] decimal(20, 4),
   [ORGANIZATIONITEMVALUE3] decimal(20, 4),
   [ORGANIZATIONITEMVALUE4] decimal(20, 4),
   [ORGANIZATIONITEMVALUE5] decimal(20, 4),
   [ORGANIZATIONMINIMUMENTRYAMOUNT] decimal(20, 4),
   [ROUNDTOAMOUNT] money,
   [TYPECODE] tinyint,
   [WRITEINTEXT] nvarchar(100))

insert into @TempTbl select 
    [BASECURRENCYID],
    [ID],
    [ITEMVALUE1],
    [ITEMVALUE2],
    [ITEMVALUE3],
    [ITEMVALUE4],
    [ITEMVALUE5],
    [MINIMUMENTRYAMOUNT],
    [ORGANIZATIONEXCHANGERATEID],
    [ORGANIZATIONITEMVALUE1],
    [ORGANIZATIONITEMVALUE2],
    [ORGANIZATIONITEMVALUE3],
    [ORGANIZATIONITEMVALUE4],
    [ORGANIZATIONITEMVALUE5],
    [ORGANIZATIONMINIMUMENTRYAMOUNT],
    [ROUNDTOAMOUNT],
    [TYPECODE],
    [WRITEINTEXT] 
from dbo.UFN_MKTASKLADDER_GETITEMLIST_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.[MKTASKLADDERITEM] where [MKTASKLADDERITEM].ID in 
    (select ID from dbo.UFN_MKTASKLADDER_GETITEMLIST
    (
        @ASKLADDERID
    )
    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 [MKTASKLADDERITEM]
        set [MKTASKLADDERITEM].[BASECURRENCYID]=temp.[BASECURRENCYID],
        [MKTASKLADDERITEM].[ID]=temp.[ID],
        [MKTASKLADDERITEM].[ITEMVALUE1]=temp.[ITEMVALUE1],
        [MKTASKLADDERITEM].[ITEMVALUE2]=temp.[ITEMVALUE2],
        [MKTASKLADDERITEM].[ITEMVALUE3]=temp.[ITEMVALUE3],
        [MKTASKLADDERITEM].[ITEMVALUE4]=temp.[ITEMVALUE4],
        [MKTASKLADDERITEM].[ITEMVALUE5]=temp.[ITEMVALUE5],
        [MKTASKLADDERITEM].[MINIMUMENTRYAMOUNT]=temp.[MINIMUMENTRYAMOUNT],
        [MKTASKLADDERITEM].[ORGANIZATIONEXCHANGERATEID]=temp.[ORGANIZATIONEXCHANGERATEID],
        [MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE1]=temp.[ORGANIZATIONITEMVALUE1],
        [MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE2]=temp.[ORGANIZATIONITEMVALUE2],
        [MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE3]=temp.[ORGANIZATIONITEMVALUE3],
        [MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE4]=temp.[ORGANIZATIONITEMVALUE4],
        [MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE5]=temp.[ORGANIZATIONITEMVALUE5],
        [MKTASKLADDERITEM].[ORGANIZATIONMINIMUMENTRYAMOUNT]=temp.[ORGANIZATIONMINIMUMENTRYAMOUNT],
        [MKTASKLADDERITEM].[ROUNDTOAMOUNT]=temp.[ROUNDTOAMOUNT],
        [MKTASKLADDERITEM].[TYPECODE]=temp.[TYPECODE],
        [MKTASKLADDERITEM].[WRITEINTEXT]=temp.[WRITEINTEXT],
        [MKTASKLADDERITEM].CHANGEDBYID = @CHANGEAGENTID,
        [MKTASKLADDERITEM].DATECHANGED = @CHANGEDATE

    from dbo.[MKTASKLADDERITEM] inner join @TempTbl as [temp] on [MKTASKLADDERITEM].ID = [temp].ID
    where ([MKTASKLADDERITEM].[BASECURRENCYID]<>temp.[BASECURRENCYID]) or 
        ([MKTASKLADDERITEM].[BASECURRENCYID] is null and temp.[BASECURRENCYID] is not null) or 
        ([MKTASKLADDERITEM].[BASECURRENCYID] is not null and temp.[BASECURRENCYID] is null) or 
        ([MKTASKLADDERITEM].[ID]<>temp.[ID]) or 
        ([MKTASKLADDERITEM].[ID] is null and temp.[ID] is not null) or 
        ([MKTASKLADDERITEM].[ID] is not null and temp.[ID] is null) or 
        ([MKTASKLADDERITEM].[ITEMVALUE1]<>temp.[ITEMVALUE1]) or 
        ([MKTASKLADDERITEM].[ITEMVALUE1] is null and temp.[ITEMVALUE1] is not null) or 
        ([MKTASKLADDERITEM].[ITEMVALUE1] is not null and temp.[ITEMVALUE1] is null) or 
        ([MKTASKLADDERITEM].[ITEMVALUE2]<>temp.[ITEMVALUE2]) or 
        ([MKTASKLADDERITEM].[ITEMVALUE2] is null and temp.[ITEMVALUE2] is not null) or 
        ([MKTASKLADDERITEM].[ITEMVALUE2] is not null and temp.[ITEMVALUE2] is null) or 
        ([MKTASKLADDERITEM].[ITEMVALUE3]<>temp.[ITEMVALUE3]) or 
        ([MKTASKLADDERITEM].[ITEMVALUE3] is null and temp.[ITEMVALUE3] is not null) or 
        ([MKTASKLADDERITEM].[ITEMVALUE3] is not null and temp.[ITEMVALUE3] is null) or 
        ([MKTASKLADDERITEM].[ITEMVALUE4]<>temp.[ITEMVALUE4]) or 
        ([MKTASKLADDERITEM].[ITEMVALUE4] is null and temp.[ITEMVALUE4] is not null) or 
        ([MKTASKLADDERITEM].[ITEMVALUE4] is not null and temp.[ITEMVALUE4] is null) or 
        ([MKTASKLADDERITEM].[ITEMVALUE5]<>temp.[ITEMVALUE5]) or 
        ([MKTASKLADDERITEM].[ITEMVALUE5] is null and temp.[ITEMVALUE5] is not null) or 
        ([MKTASKLADDERITEM].[ITEMVALUE5] is not null and temp.[ITEMVALUE5] is null) or 
        ([MKTASKLADDERITEM].[MINIMUMENTRYAMOUNT]<>temp.[MINIMUMENTRYAMOUNT]) or 
        ([MKTASKLADDERITEM].[MINIMUMENTRYAMOUNT] is null and temp.[MINIMUMENTRYAMOUNT] is not null) or 
        ([MKTASKLADDERITEM].[MINIMUMENTRYAMOUNT] is not null and temp.[MINIMUMENTRYAMOUNT] is null) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONEXCHANGERATEID]<>temp.[ORGANIZATIONEXCHANGERATEID]) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONEXCHANGERATEID] is null and temp.[ORGANIZATIONEXCHANGERATEID] is not null) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONEXCHANGERATEID] is not null and temp.[ORGANIZATIONEXCHANGERATEID] is null) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE1]<>temp.[ORGANIZATIONITEMVALUE1]) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE1] is null and temp.[ORGANIZATIONITEMVALUE1] is not null) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE1] is not null and temp.[ORGANIZATIONITEMVALUE1] is null) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE2]<>temp.[ORGANIZATIONITEMVALUE2]) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE2] is null and temp.[ORGANIZATIONITEMVALUE2] is not null) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE2] is not null and temp.[ORGANIZATIONITEMVALUE2] is null) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE3]<>temp.[ORGANIZATIONITEMVALUE3]) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE3] is null and temp.[ORGANIZATIONITEMVALUE3] is not null) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE3] is not null and temp.[ORGANIZATIONITEMVALUE3] is null) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE4]<>temp.[ORGANIZATIONITEMVALUE4]) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE4] is null and temp.[ORGANIZATIONITEMVALUE4] is not null) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE4] is not null and temp.[ORGANIZATIONITEMVALUE4] is null) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE5]<>temp.[ORGANIZATIONITEMVALUE5]) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE5] is null and temp.[ORGANIZATIONITEMVALUE5] is not null) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONITEMVALUE5] is not null and temp.[ORGANIZATIONITEMVALUE5] is null) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONMINIMUMENTRYAMOUNT]<>temp.[ORGANIZATIONMINIMUMENTRYAMOUNT]) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONMINIMUMENTRYAMOUNT] is null and temp.[ORGANIZATIONMINIMUMENTRYAMOUNT] is not null) or 
        ([MKTASKLADDERITEM].[ORGANIZATIONMINIMUMENTRYAMOUNT] is not null and temp.[ORGANIZATIONMINIMUMENTRYAMOUNT] is null) or 
        ([MKTASKLADDERITEM].[ROUNDTOAMOUNT]<>temp.[ROUNDTOAMOUNT]) or 
        ([MKTASKLADDERITEM].[ROUNDTOAMOUNT] is null and temp.[ROUNDTOAMOUNT] is not null) or 
        ([MKTASKLADDERITEM].[ROUNDTOAMOUNT] is not null and temp.[ROUNDTOAMOUNT] is null) or 
        ([MKTASKLADDERITEM].[TYPECODE]<>temp.[TYPECODE]) or 
        ([MKTASKLADDERITEM].[TYPECODE] is null and temp.[TYPECODE] is not null) or 
        ([MKTASKLADDERITEM].[TYPECODE] is not null and temp.[TYPECODE] is null) or 
        ([MKTASKLADDERITEM].[WRITEINTEXT]<>temp.[WRITEINTEXT]) or 
        ([MKTASKLADDERITEM].[WRITEINTEXT] is null and temp.[WRITEINTEXT] is not null) or 
        ([MKTASKLADDERITEM].[WRITEINTEXT] is not null and temp.[WRITEINTEXT] is null)

if @@Error <> 0
    return 3;    

-- insert new items

insert into [MKTASKLADDERITEM] 
    ([ASKLADDERID], 
    [BASECURRENCYID],
    [ID],
    [ITEMVALUE1],
    [ITEMVALUE2],
    [ITEMVALUE3],
    [ITEMVALUE4],
    [ITEMVALUE5],
    [MINIMUMENTRYAMOUNT],
    [ORGANIZATIONEXCHANGERATEID],
    [ORGANIZATIONITEMVALUE1],
    [ORGANIZATIONITEMVALUE2],
    [ORGANIZATIONITEMVALUE3],
    [ORGANIZATIONITEMVALUE4],
    [ORGANIZATIONITEMVALUE5],
    [ORGANIZATIONMINIMUMENTRYAMOUNT],
    [ROUNDTOAMOUNT],
    [TYPECODE],
    [WRITEINTEXT],                
    ADDEDBYID, 
    CHANGEDBYID, 
    DATEADDED, 
    DATECHANGED)
select @ASKLADDERID, 
    [BASECURRENCYID],
    [ID],
    [ITEMVALUE1],
    [ITEMVALUE2],
    [ITEMVALUE3],
    [ITEMVALUE4],
    [ITEMVALUE5],
    [MINIMUMENTRYAMOUNT],
    [ORGANIZATIONEXCHANGERATEID],
    [ORGANIZATIONITEMVALUE1],
    [ORGANIZATIONITEMVALUE2],
    [ORGANIZATIONITEMVALUE3],
    [ORGANIZATIONITEMVALUE4],
    [ORGANIZATIONITEMVALUE5],
    [ORGANIZATIONMINIMUMENTRYAMOUNT],
    [ROUNDTOAMOUNT],
    [TYPECODE],
    [WRITEINTEXT], 
    @CHANGEAGENTID, 
    @CHANGEAGENTID, 
    @CHANGEDATE, 
    @CHANGEDATE
from @TempTbl as [temp]
where not exists (select ID from dbo.[MKTASKLADDERITEM] as data where data.ID = [temp].ID)

if @@Error <> 0
    return 4;

return 0;