USP_REVENUEUPDATEBATCH_GETINSTALLMENTS_UPDATEFROMXML
Used to update a set of records defined by UFN_REVENUEUPDATEBATCH_GETINSTALLMENTS from the given xml string.
Parameters
Parameter | Parameter Type | Mode | Description |
---|---|---|---|
@BATCHREVENUEID | uniqueidentifier | IN | |
@XML | xml | IN | |
@CHANGEAGENTID | uniqueidentifier | IN | |
@CHANGEDATE | datetime | IN |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 1:00:24 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE PROCEDURE dbo.USP_REVENUEUPDATEBATCH_GETINSTALLMENTS_UPDATEFROMXML
(
@BATCHREVENUEID 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 (
[AMOUNT] money,
[DATE] datetime,
[ID] uniqueidentifier,
[INSTALLMENTID] uniqueidentifier,
[RECEIPTAMOUNT] money,
[SEQUENCE] int)
insert into @TempTbl select
[AMOUNT],
[DATE],
[ID],
[INSTALLMENTID],
[RECEIPTAMOUNT],
[SEQUENCE]
from dbo.UFN_REVENUEUPDATEBATCH_GETINSTALLMENTS_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.[BATCHREVENUEINSTALLMENT] where [BATCHREVENUEINSTALLMENT].ID in
(select ID from dbo.UFN_REVENUEUPDATEBATCH_GETINSTALLMENTS
(
@BATCHREVENUEID
)
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 [BATCHREVENUEINSTALLMENT]
set [BATCHREVENUEINSTALLMENT].[AMOUNT]=temp.[AMOUNT],
[BATCHREVENUEINSTALLMENT].[DATE]=temp.[DATE],
[BATCHREVENUEINSTALLMENT].[ID]=temp.[ID],
[BATCHREVENUEINSTALLMENT].[INSTALLMENTID]=temp.[INSTALLMENTID],
[BATCHREVENUEINSTALLMENT].[RECEIPTAMOUNT]=temp.[RECEIPTAMOUNT],
[BATCHREVENUEINSTALLMENT].[SEQUENCE]=temp.[SEQUENCE],
[BATCHREVENUEINSTALLMENT].CHANGEDBYID = @CHANGEAGENTID,
[BATCHREVENUEINSTALLMENT].DATECHANGED = @CHANGEDATE
from dbo.[BATCHREVENUEINSTALLMENT] inner join @TempTbl as [temp] on [BATCHREVENUEINSTALLMENT].ID = [temp].ID
where ([BATCHREVENUEINSTALLMENT].[AMOUNT]<>temp.[AMOUNT]) or
([BATCHREVENUEINSTALLMENT].[AMOUNT] is null and temp.[AMOUNT] is not null) or
([BATCHREVENUEINSTALLMENT].[AMOUNT] is not null and temp.[AMOUNT] is null) or
([BATCHREVENUEINSTALLMENT].[DATE]<>temp.[DATE]) or
([BATCHREVENUEINSTALLMENT].[DATE] is null and temp.[DATE] is not null) or
([BATCHREVENUEINSTALLMENT].[DATE] is not null and temp.[DATE] is null) or
([BATCHREVENUEINSTALLMENT].[ID]<>temp.[ID]) or
([BATCHREVENUEINSTALLMENT].[ID] is null and temp.[ID] is not null) or
([BATCHREVENUEINSTALLMENT].[ID] is not null and temp.[ID] is null) or
([BATCHREVENUEINSTALLMENT].[INSTALLMENTID]<>temp.[INSTALLMENTID]) or
([BATCHREVENUEINSTALLMENT].[INSTALLMENTID] is null and temp.[INSTALLMENTID] is not null) or
([BATCHREVENUEINSTALLMENT].[INSTALLMENTID] is not null and temp.[INSTALLMENTID] is null) or
([BATCHREVENUEINSTALLMENT].[RECEIPTAMOUNT]<>temp.[RECEIPTAMOUNT]) or
([BATCHREVENUEINSTALLMENT].[RECEIPTAMOUNT] is null and temp.[RECEIPTAMOUNT] is not null) or
([BATCHREVENUEINSTALLMENT].[RECEIPTAMOUNT] is not null and temp.[RECEIPTAMOUNT] is null) or
([BATCHREVENUEINSTALLMENT].[SEQUENCE]<>temp.[SEQUENCE]) or
([BATCHREVENUEINSTALLMENT].[SEQUENCE] is null and temp.[SEQUENCE] is not null) or
([BATCHREVENUEINSTALLMENT].[SEQUENCE] is not null and temp.[SEQUENCE] is null)
if @@Error <> 0
return 3;
-- insert new items
insert into [BATCHREVENUEINSTALLMENT]
([BATCHREVENUEID],
[AMOUNT],
[DATE],
[ID],
[INSTALLMENTID],
[RECEIPTAMOUNT],
[SEQUENCE],
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select @BATCHREVENUEID,
[AMOUNT],
[DATE],
[ID],
[INSTALLMENTID],
[RECEIPTAMOUNT],
[SEQUENCE],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
from @TempTbl as [temp]
where not exists (select ID from dbo.[BATCHREVENUEINSTALLMENT] as data where data.ID = [temp].ID)
if @@Error <> 0
return 4;
return 0;