USP_REVENUE_LETTERS_CUSTOMUPDATEFROMXML

CREATE procedure dbo.USP_REVENUE_LETTERS_CUSTOMUPDATEFROMXML
(
  @REVENUEID uniqueidentifier,
  @LETTERS 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
  (
    [ACKNOWLEDGEDATE] datetime,
    [ACKNOWLEDGEEID] uniqueidentifier,
    [ID] uniqueidentifier,
    [LETTERCODEID] uniqueidentifier,
    [LETTERTYPECODE] tinyint,
    [TRIBUTEID] uniqueidentifier,
    [PROCESSDATE] datetime,
    [OUTOFDATE] bit,
    [CLEARDATES] bit
  );

  insert into
    @TempTbl
  select 
    [ACKNOWLEDGEDATE],
    [ACKNOWLEDGEEID],
    [ID],
    [LETTERCODEID],
    [LETTERTYPECODE],
    [TRIBUTEID],
    [PROCESSDATE],
    [OUTOFDATE],
    [CLEARDATES]
  from
    dbo.UFN_REVENUEUPDATEBATCH_GETLETTERS_FROMITEMLISTXML(@LETTERS);

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

  if @@Error <> 0
    return 1;

  --Add any constituents that were created in batch

  declare CONSTITUENTSTOCREATECURSOR cursor local fast_forward for
  select distinct
    ACKNOWLEDGEEID
  from
    @TempTbl
  where
    ACKNOWLEDGEEID not in (select ID from dbo.CONSTITUENT);

  open CONSTITUENTSTOCREATECURSOR;

  declare @BATCHREVENUECONSTITUENTID uniqueidentifier;
  fetch next from CONSTITUENTSTOCREATECURSOR into @BATCHREVENUECONSTITUENTID;

  while @@FETCH_STATUS = 0
  begin
    declare @CONSTITUENTID uniqueidentifier = null;
    exec USP_REVENUEBATCH_CONSTITUENT_ADD @CONSTITUENTID output, @CHANGEAGENTID, @BATCHREVENUECONSTITUENTID;

    update
      @TempTbl
    set
      ACKNOWLEDGEEID = @CONSTITUENTID
    where
      ACKNOWLEDGEEID = @BATCHREVENUECONSTITUENTID;

    exec dbo.USP_REVENUEBATCH_CONSTITUENT_DELETE @BATCHREVENUECONSTITUENTID, @CHANGEAGENTID;

    fetch next from CONSTITUENTSTOCREATECURSOR into @BATCHREVENUECONSTITUENTID;
  end

  close CONSTITUENTSTOCREATECURSOR;
  deallocate CONSTITUENTSTOCREATECURSOR;

  declare @contextCache varbinary(128) = 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.[REVENUELETTER]
  where
    [REVENUELETTER].ID in (select ID from dbo.UFN_REVENUE_GETLETTERS_2(@REVENUEID) EXCEPT select ID from @TempTbl where LETTERTYPECODE = 0);

  if @@error <> 0
  begin
    if not @contextCache is null
      set CONTEXT_INFO @contextCache;

    return 2;
  end

  --Cache error so we can reset the context cache

  declare @e int;

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

  delete from
    dbo.[REVENUETRIBUTELETTER]
  where
    [REVENUETRIBUTELETTER].ID in (select ID from dbo.UFN_REVENUE_GETLETTERS_2(@REVENUEID) EXCEPT select ID from @TempTbl where LETTERTYPECODE = 1);

  select @e = @@error;

  -- reset CONTEXT_INFO to previous value 

  if not @contextCache is null
    set CONTEXT_INFO @contextCache;

  if @e <> 0
    return 3;

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

  update
    dbo.REVENUELETTER
  set
    REVENUELETTER.LETTERCODEID = temp.LETTERCODEID,
    REVENUELETTER.ACKNOWLEDGEDATE = case when temp.CLEARDATES = 1 then null else temp.ACKNOWLEDGEDATE end,
    REVENUELETTER.ACKNOWLEDGEEID = temp.ACKNOWLEDGEEID,
    REVENUELETTER.PROCESSDATE = case when temp.CLEARDATES = 1 then null else temp.PROCESSDATE end,
    REVENUELETTER.OUTOFDATE = temp.OUTOFDATE,
    REVENUELETTER.CHANGEDBYID = @CHANGEAGENTID,
    REVENUELETTER.DATECHANGED = @CHANGEDATE
  from
    dbo.REVENUELETTER
  inner join
    @TempTbl as temp on REVENUELETTER.ID = temp.ID
  where
    temp.LETTERTYPECODE = 0 and
    (
      (REVENUELETTER.LETTERCODEID <> temp.LETTERCODEID) or 
      (REVENUELETTER.LETTERCODEID is null and temp.LETTERCODEID is not null) or 
      (REVENUELETTER.LETTERCODEID is not null and temp.LETTERCODEID is null) or 
      (REVENUELETTER.ACKNOWLEDGEDATE <> temp.ACKNOWLEDGEDATE) or 
      (REVENUELETTER.ACKNOWLEDGEDATE is null and temp.ACKNOWLEDGEDATE is not null) or 
      (REVENUELETTER.ACKNOWLEDGEDATE is not null and temp.ACKNOWLEDGEDATE is null) or 
      (REVENUELETTER.ACKNOWLEDGEEID <> temp.ACKNOWLEDGEEID) or 
      (REVENUELETTER.ACKNOWLEDGEEID is null and temp.ACKNOWLEDGEEID is not null) or 
      (REVENUELETTER.ACKNOWLEDGEEID is not null and temp.ACKNOWLEDGEEID is null) or
      (REVENUELETTER.OUTOFDATE <> temp.OUTOFDATE) or
      temp.CLEARDATES = 1
    );

  if @@Error <> 0
    return 4;    

  update
    dbo.REVENUETRIBUTELETTER
  set
    REVENUETRIBUTELETTER.REVENUETRIBUTEID= (select ID from dbo.REVENUETRIBUTE where REVENUEID = @REVENUEID and TRIBUTEID = temp.TRIBUTEID),
    REVENUETRIBUTELETTER.ACKNOWLEDGEDATE= case when temp.CLEARDATES = 1 then null else temp.ACKNOWLEDGEDATE end,
    REVENUETRIBUTELETTER.CONSTITUENTID=temp.ACKNOWLEDGEEID,
    REVENUETRIBUTELETTER.PROCESSDATE =case when temp.CLEARDATES = 1 then null else temp.PROCESSDATE end,
    REVENUETRIBUTELETTER.CHANGEDBYID = @CHANGEAGENTID,
    REVENUETRIBUTELETTER.DATECHANGED = @CHANGEDATE
  from
    dbo.REVENUETRIBUTELETTER
  inner join
    @TempTbl as temp on REVENUETRIBUTELETTER.ID = temp.ID
  where
    temp.LETTERTYPECODE = 1 and
  (
    (REVENUETRIBUTELETTER.TRIBUTELETTERCODEID<>temp.LETTERCODEID) or 
    (REVENUETRIBUTELETTER.TRIBUTELETTERCODEID is null and temp.LETTERCODEID is not null) or 
    (REVENUETRIBUTELETTER.TRIBUTELETTERCODEID is not null and temp.LETTERCODEID is null) or 
    (REVENUETRIBUTELETTER.ACKNOWLEDGEDATE<>temp.ACKNOWLEDGEDATE) or 
    (REVENUETRIBUTELETTER.ACKNOWLEDGEDATE is null and temp.ACKNOWLEDGEDATE is not null) or 
    (REVENUETRIBUTELETTER.ACKNOWLEDGEDATE is not null and temp.ACKNOWLEDGEDATE is null) or 
    (REVENUETRIBUTELETTER.CONSTITUENTID<>temp.ACKNOWLEDGEEID) or 
    (REVENUETRIBUTELETTER.CONSTITUENTID is null and temp.ACKNOWLEDGEEID is not null) or 
    (REVENUETRIBUTELETTER.CONSTITUENTID is not null and temp.ACKNOWLEDGEEID is null) or
    (REVENUETRIBUTELETTER.REVENUETRIBUTEID<>temp.TRIBUTEID) or 
    (REVENUETRIBUTELETTER.REVENUETRIBUTEID is null and temp.TRIBUTEID is not null) or 
    (REVENUETRIBUTELETTER.REVENUETRIBUTEID is not null and temp.TRIBUTEID is null) or
    temp.CLEARDATES = 1
  );

  if @@Error <> 0
    return 5;

  -- insert new items for revenue letter

  insert into dbo.REVENUELETTER
  (    
    ID,
    REVENUEID,
    LETTERCODEID,
    ACKNOWLEDGEDATE,
    ACKNOWLEDGEEID,
    ADDEDBYID,
    CHANGEDBYID,
    DATEADDED,
    DATECHANGED
  )
  select 
    ID,
    @REVENUEID,
    LETTERCODEID,
    ACKNOWLEDGEDATE = case when temp.CLEARDATES = 1 then null else temp.ACKNOWLEDGEDATE end,
    ACKNOWLEDGEEID, 
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CHANGEDATE,
    @CHANGEDATE
  from
    @TempTbl as temp
  where
    temp.LETTERTYPECODE = 0 and
    not exists (select ID from dbo.REVENUELETTER as data where data.ID = temp.ID);

  if @@Error <> 0
    return 6;

  -- insert new items for revenue tribute letter

  insert into dbo.REVENUETRIBUTELETTER
  (
    ID,
    REVENUETRIBUTEID,
    CONSTITUENTID,
    TRIBUTELETTERCODEID,
    ACKNOWLEDGEDATE,
    ADDEDBYID,
    CHANGEDBYID,
    DATEADDED,
    DATECHANGED
  )
  select 
    ID,
    (select ID from REVENUETRIBUTE where REVENUETRIBUTE.REVENUEID = @REVENUEID AND TRIBUTEID = temp.TRIBUTEID),
    ACKNOWLEDGEEID,
    LETTERCODEID,
    ACKNOWLEDGEDATE = case when temp.CLEARDATES = 1 then null else temp.ACKNOWLEDGEDATE end,
    @CHANGEAGENTID, 
    @CHANGEAGENTID, 
    @CHANGEDATE, 
    @CHANGEDATE
  from
    @TempTbl as temp
  where
    temp.LETTERTYPECODE = 1 and
    not exists (select ID from dbo.REVENUETRIBUTELETTER as data where data.ID = temp.ID);

  if @@Error <> 0
    return 7;

  return 0;