USP_DISBURSEMENTPROCESSTEMPLATE_SIGNATURE_UPDATEFROMXML

create procedure dbo.USP_DISBURSEMENTPROCESSTEMPLATE_SIGNATURE_UPDATEFROMXML(
  @DISBURSEMENTPROCESSTEMPLATEID uniqueidentifier,
  @XML xml,
  @TYPECODE tinyint,
  @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();

  declare @TempTbl table(
    [ID] uniqueidentifier,
    [FROMAMOUNT] money,
    [BANKACCOUNTAUTHORIZEDSIGNATUREID] uniqueidentifier,
    [TYPECODE] tinyint);

  if @TYPECODE = 0
  begin
    insert into @TempTbl select
      [ID],
      [FROMAMOUNT],
      [BANKACCOUNTAUTHORIZEDSIGNATUREID],
      @TYPECODE
    from dbo.UFN_DISBURSEMENTPROCESSTEMPLATE_SIGNATURE1_FROMITEMLISTXML(@TYPECODE, @xml);
  end
  else
    insert into @TempTbl select
      [ID],
      [FROMAMOUNT],
      [BANKACCOUNTAUTHORIZEDSIGNATUREID],
      @TYPECODE
    from dbo.UFN_DISBURSEMENTPROCESSTEMPLATE_SIGNATURE2_FROMITEMLISTXML(@TYPECODE, @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 from dbo.DISBURSEMENTPROCESSTEMPLATESIGNATURE where DISBURSEMENTPROCESSTEMPLATESIGNATURE.ID in
    (select ID from dbo.DISBURSEMENTPROCESSTEMPLATESIGNATURE where DISBURSEMENTPROCESSTEMPLATESIGNATURE.DISBURSEMENTPROCESSTEMPLATEID = @DISBURSEMENTPROCESSTEMPLATEID
      and DISBURSEMENTPROCESSTEMPLATESIGNATURE.TYPECODE = @TYPECODE
    EXCEPT select ID from @TempTbl);

  -- reset CONTEXT_INFO to previous value 

    if not @contextCache is null
        set CONTEXT_INFO @contextCache;

  if @@error <> 0
        return 2;

  update dbo.DISBURSEMENTPROCESSTEMPLATESIGNATURE set
    DISBURSEMENTPROCESSTEMPLATESIGNATURE.ID = temp.ID,
    DISBURSEMENTPROCESSTEMPLATESIGNATURE.DISBURSEMENTPROCESSTEMPLATEID = @DISBURSEMENTPROCESSTEMPLATEID,
    DISBURSEMENTPROCESSTEMPLATESIGNATURE.FROMAMOUNT = temp.FROMAMOUNT,
    DISBURSEMENTPROCESSTEMPLATESIGNATURE.BANKACCOUNTAUTHORIZEDSIGNATUREID = temp.BANKACCOUNTAUTHORIZEDSIGNATUREID,
    DISBURSEMENTPROCESSTEMPLATESIGNATURE.TYPECODE = temp.TYPECODE,
    DISBURSEMENTPROCESSTEMPLATESIGNATURE.CHANGEDBYID = @CHANGEAGENTID,
    DISBURSEMENTPROCESSTEMPLATESIGNATURE.DATECHANGED = @CHANGEDATE
  from dbo.DISBURSEMENTPROCESSTEMPLATESIGNATURE
  inner join @TempTbl as temp on DISBURSEMENTPROCESSTEMPLATESIGNATURE.ID = temp.ID
  where (DISBURSEMENTPROCESSTEMPLATESIGNATURE.ID <> temp.ID) or
        (DISBURSEMENTPROCESSTEMPLATESIGNATURE.ID is null and temp.ID is not null) or
        (DISBURSEMENTPROCESSTEMPLATESIGNATURE.ID is not null and temp.ID is null) or
        (DISBURSEMENTPROCESSTEMPLATESIGNATURE.FROMAMOUNT <> temp.FROMAMOUNT) or
        (DISBURSEMENTPROCESSTEMPLATESIGNATURE.FROMAMOUNT is null and temp.FROMAMOUNT is not null) or
        (DISBURSEMENTPROCESSTEMPLATESIGNATURE.FROMAMOUNT is not null and temp.FROMAMOUNT is null) or
        (DISBURSEMENTPROCESSTEMPLATESIGNATURE.BANKACCOUNTAUTHORIZEDSIGNATUREID <> temp.BANKACCOUNTAUTHORIZEDSIGNATUREID) or
        (DISBURSEMENTPROCESSTEMPLATESIGNATURE.BANKACCOUNTAUTHORIZEDSIGNATUREID is null and temp.BANKACCOUNTAUTHORIZEDSIGNATUREID is not null) or
        (DISBURSEMENTPROCESSTEMPLATESIGNATURE.BANKACCOUNTAUTHORIZEDSIGNATUREID is not null and temp.BANKACCOUNTAUTHORIZEDSIGNATUREID is null)

  if @@Error <> 0
    return 3;

  insert into dbo.DISBURSEMENTPROCESSTEMPLATESIGNATURE(
    ID,
    DISBURSEMENTPROCESSTEMPLATEID,
    FROMAMOUNT,
    BANKACCOUNTAUTHORIZEDSIGNATUREID,
    TYPECODE,
    ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED)
  select
   ID,
    @DISBURSEMENTPROCESSTEMPLATEID,
    FROMAMOUNT,
    BANKACCOUNTAUTHORIZEDSIGNATUREID,
    @TYPECODE,
    @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CHANGEDATE,
        @CHANGEDATE
  from @TempTbl as temp
  where not exists (select ID from dbo.DISBURSEMENTPROCESSTEMPLATESIGNATURE as data where data.ID = temp.ID)

  if @@Error <> 0
        return 4;

    return 0;