USP_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS_CUSTOMUPDATEFROMXML

CREATE procedure dbo.USP_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS_CUSTOMUPDATEFROMXML
(
  @REVENUESPLITID 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,
   [BASECURRENCYID] uniqueidentifier,
   [CONSTITUENTID] uniqueidentifier,
   [EFFECTIVEDATE] datetime,
   [ID] uniqueidentifier,
   [REVENUERECOGNITIONTYPECODEID] uniqueidentifier,
   [DESIGNATIONID] uniqueidentifier,
   [DONORCHALLENGERECOGNITIONTYPECODE] tinyint,
   [RECOGNITIONCREDITFKID] uniqueidentifier)

insert into @TempTbl select 
    [AMOUNT],
    [BASECURRENCYID],
    [CONSTITUENTID],
    [EFFECTIVEDATE],
    [ID],
    [REVENUERECOGNITIONTYPECODEID],
    [DESIGNATIONID],
    [DONORCHALLENGERECOGNITIONTYPECODE],
    [RECOGNITIONCREDITFKID]
from dbo.UFN_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS_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.[REVENUERECOGNITION] where [REVENUERECOGNITION].ID in 
  (select ID from dbo.UFN_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS
  (
      @REVENUESPLITID
  )
  EXCEPT select ID from @TempTbl)

delete from dbo.[CONSTITUENTRECOGNITIONREVENUE] where RECOGNITIONCREDITID in 
  (select ID from dbo.UFN_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS
  (
      @REVENUESPLITID
  )
  EXCEPT select ID from @TempTbl)

delete from dbo.[RECOGNITIONCREDIT] where [RECOGNITIONCREDIT].ID in 
  (select ID from dbo.UFN_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS
  (
      @REVENUESPLITID
  )
  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 for original revenue and external sponsors

update [REVENUERECOGNITION]
    set [REVENUERECOGNITION].[AMOUNT]=temp.[AMOUNT],
        [REVENUERECOGNITION].[BASECURRENCYID]=temp.[BASECURRENCYID],
        [REVENUERECOGNITION].[CONSTITUENTID]=temp.[CONSTITUENTID],
        [REVENUERECOGNITION].[EFFECTIVEDATE]=temp.[EFFECTIVEDATE],
        [REVENUERECOGNITION].[REVENUERECOGNITIONTYPECODEID]=temp.[REVENUERECOGNITIONTYPECODEID],
        [REVENUERECOGNITION].CHANGEDBYID = @CHANGEAGENTID,
        [REVENUERECOGNITION].DATECHANGED = @CHANGEDATE
  from dbo.[REVENUERECOGNITION] inner join @TempTbl as [temp] on [REVENUERECOGNITION].ID = [temp].ID
  where temp.DONORCHALLENGERECOGNITIONTYPECODE in (0, 1) and
        (([REVENUERECOGNITION].[AMOUNT]<>temp.[AMOUNT]) or 
        ([REVENUERECOGNITION].[AMOUNT] is null and temp.[AMOUNT] is not null) or 
        ([REVENUERECOGNITION].[AMOUNT] is not null and temp.[AMOUNT] is null) or 
        ([REVENUERECOGNITION].[BASECURRENCYID]<>temp.[BASECURRENCYID]) or 
        ([REVENUERECOGNITION].[BASECURRENCYID] is null and temp.[BASECURRENCYID] is not null) or 
        ([REVENUERECOGNITION].[BASECURRENCYID] is not null and temp.[BASECURRENCYID] is null) or 
        ([REVENUERECOGNITION].[CONSTITUENTID]<>temp.[CONSTITUENTID]) or 
        ([REVENUERECOGNITION].[CONSTITUENTID] is null and temp.[CONSTITUENTID] is not null) or 
        ([REVENUERECOGNITION].[CONSTITUENTID] is not null and temp.[CONSTITUENTID] is null) or 
      ([REVENUERECOGNITION].[EFFECTIVEDATE]<>temp.[EFFECTIVEDATE]) or 
        ([REVENUERECOGNITION].[EFFECTIVEDATE] is null and temp.[EFFECTIVEDATE] is not null) or 
        ([REVENUERECOGNITION].[EFFECTIVEDATE] is not null and temp.[EFFECTIVEDATE] is null) or 
        ([REVENUERECOGNITION].[REVENUERECOGNITIONTYPECODEID]<>temp.[REVENUERECOGNITIONTYPECODEID]) or 
        ([REVENUERECOGNITION].[REVENUERECOGNITIONTYPECODEID] is null and temp.[REVENUERECOGNITIONTYPECODEID] is not null) or 
        ([REVENUERECOGNITION].[REVENUERECOGNITIONTYPECODEID] is not null and temp.[REVENUERECOGNITIONTYPECODEID] is null))

-- update the items that exist in the XML table and the db for internal sponsors

update [RECOGNITIONCREDIT]
    set [RECOGNITIONCREDIT].[AMOUNT]=temp.[AMOUNT],
        [RECOGNITIONCREDIT].[BASECURRENCYID]=temp.[BASECURRENCYID],
        [RECOGNITIONCREDIT].[CONSTITUENTID]=temp.[CONSTITUENTID],
        [RECOGNITIONCREDIT].[EFFECTIVEDATE]=temp.[EFFECTIVEDATE],
        [RECOGNITIONCREDIT].[USERRECOGNITIONTYPECODEID]=temp.[REVENUERECOGNITIONTYPECODEID],
        [RECOGNITIONCREDIT].CHANGEDBYID = @CHANGEAGENTID,
        [RECOGNITIONCREDIT].DATECHANGED = @CHANGEDATE,
        [RECOGNITIONCREDIT].[DESIGNATIONID] = temp.[DESIGNATIONID]
  from dbo.[RECOGNITIONCREDIT] inner join @TempTbl as [temp] on [RECOGNITIONCREDIT].ID = [temp].ID
  where temp.DONORCHALLENGERECOGNITIONTYPECODE = 2 and
        (([RECOGNITIONCREDIT].[AMOUNT]<>temp.[AMOUNT]) or 
        ([RECOGNITIONCREDIT].[AMOUNT] is null and temp.[AMOUNT] is not null) or 
        ([RECOGNITIONCREDIT].[AMOUNT] is not null and temp.[AMOUNT] is null) or 
        ([RECOGNITIONCREDIT].[BASECURRENCYID]<>temp.[BASECURRENCYID]) or 
        ([RECOGNITIONCREDIT].[BASECURRENCYID] is null and temp.[BASECURRENCYID] is not null) or 
        ([RECOGNITIONCREDIT].[BASECURRENCYID] is not null and temp.[BASECURRENCYID] is null) or 
        ([RECOGNITIONCREDIT].[CONSTITUENTID]<>temp.[CONSTITUENTID]) or 
        ([RECOGNITIONCREDIT].[CONSTITUENTID] is null and temp.[CONSTITUENTID] is not null) or 
        ([RECOGNITIONCREDIT].[CONSTITUENTID] is not null and temp.[CONSTITUENTID] is null) or 
        ([RECOGNITIONCREDIT].[EFFECTIVEDATE]<>temp.[EFFECTIVEDATE]) or 
        ([RECOGNITIONCREDIT].[EFFECTIVEDATE] is null and temp.[EFFECTIVEDATE] is not null) or 
        ([RECOGNITIONCREDIT].[EFFECTIVEDATE] is not null and temp.[EFFECTIVEDATE] is null) or 
        ([RECOGNITIONCREDIT].[USERRECOGNITIONTYPECODEID]<>temp.[REVENUERECOGNITIONTYPECODEID]) or 
        ([RECOGNITIONCREDIT].[USERRECOGNITIONTYPECODEID] is null and temp.[REVENUERECOGNITIONTYPECODEID] is not null) or 
        ([RECOGNITIONCREDIT].[USERRECOGNITIONTYPECODEID] is not null and temp.[REVENUERECOGNITIONTYPECODEID] is null)or 
        ([RECOGNITIONCREDIT].[DESIGNATIONID]<>temp.[DESIGNATIONID]) or 
        ([RECOGNITIONCREDIT].[DESIGNATIONID] is null and temp.[DESIGNATIONID] is not null) or 
        ([RECOGNITIONCREDIT].[DESIGNATIONID] is not null and temp.[DESIGNATIONID] is null))

if @@Error <> 0
  return 3; 

-- insert new items for original revenue and external sponsors

insert into [REVENUERECOGNITION] 
  ([REVENUESPLITID], 
    [AMOUNT],
    [BASECURRENCYID],
    [CONSTITUENTID],
    [EFFECTIVEDATE],
    [ID],
    [REVENUERECOGNITIONTYPECODEID],       
    ADDEDBYID, 
    CHANGEDBYID, 
    DATEADDED, 
    DATECHANGED)
select [RECOGNITIONCREDITFKID], 
    [AMOUNT],
    [BASECURRENCYID],
    [CONSTITUENTID],
    [EFFECTIVEDATE],
    [ID],
    [REVENUERECOGNITIONTYPECODEID], 
    @CHANGEAGENTID, 
    @CHANGEAGENTID, 
    @CHANGEDATE, 
    @CHANGEDATE
from @TempTbl as [temp]
where temp.DONORCHALLENGERECOGNITIONTYPECODE in (0, 1) and
  not exists (select ID from dbo.[REVENUERECOGNITION] as data where data.ID = [temp].ID)

-- insert new items for internal sponsors

insert into [RECOGNITIONCREDIT] 
  ([DONORCHALLENGEENCUMBEREDID], 
    [AMOUNT],
    [BASECURRENCYID],
   [CONSTITUENTID],
    [EFFECTIVEDATE],
    [ID],
    [USERRECOGNITIONTYPECODEID],
    [DESIGNATIONID],
    [RECOGNITIONCREDITTYPECODE],
    ADDEDBYID, 
    CHANGEDBYID, 
    DATEADDED, 
    DATECHANGED)
select [RECOGNITIONCREDITFKID], 
    [AMOUNT],
    [BASECURRENCYID],
    [CONSTITUENTID],
    [EFFECTIVEDATE],
    [ID],
    [REVENUERECOGNITIONTYPECODEID], 
    [DESIGNATIONID],
    1, --Donor challenge

    @CHANGEAGENTID, 
    @CHANGEAGENTID, 
    @CHANGEDATE, 
    @CHANGEDATE
from @TempTbl as [temp]
where temp.DONORCHALLENGERECOGNITIONTYPECODE = 2 and
  not exists (select ID from dbo.[RECOGNITIONCREDIT] as data where data.ID = [temp].ID)

if @@Error <> 0
  return 4;

return 0;