USP_DISBURSEMENTPROCESS_SIGNATURE_UPDATEFROMXML

            create procedure dbo.USP_DISBURSEMENTPROCESS_SIGNATURE_UPDATEFROMXML
            (
                @DISBURSEMENTPROCESSID 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_DISBURSEMENTPROCESS_SIGNATURE1_FROMITEMLISTXML(@TYPECODE, @xml);
                end
            else
                insert into @TempTbl select
                    [ID],
                    [FROMAMOUNT],
                    [BANKACCOUNTAUTHORIZEDSIGNATUREID],
                    @TYPECODE
                from dbo.UFN_DISBURSEMENTPROCESS_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.DISBURSEMENTPROCESSSIGNATURE where DISBURSEMENTPROCESSSIGNATURE.ID in
            (select ID from dbo.DISBURSEMENTPROCESSSIGNATURE where DISBURSEMENTPROCESSSIGNATURE.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID
            and DISBURSEMENTPROCESSSIGNATURE.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.DISBURSEMENTPROCESSSIGNATURE set
                DISBURSEMENTPROCESSSIGNATURE.ID = temp.ID,
                DISBURSEMENTPROCESSSIGNATURE.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID,
                DISBURSEMENTPROCESSSIGNATURE.FROMAMOUNT = temp.FROMAMOUNT,
                DISBURSEMENTPROCESSSIGNATURE.BANKACCOUNTAUTHORIZEDSIGNATUREID = temp.BANKACCOUNTAUTHORIZEDSIGNATUREID,
                DISBURSEMENTPROCESSSIGNATURE.TYPECODE = temp.TYPECODE,
                DISBURSEMENTPROCESSSIGNATURE.CHANGEDBYID = @CHANGEAGENTID,
                DISBURSEMENTPROCESSSIGNATURE.DATECHANGED = @CHANGEDATE
            from dbo.DISBURSEMENTPROCESSSIGNATURE
            inner join @TempTbl as temp on DISBURSEMENTPROCESSSIGNATURE.ID = temp.ID
            where 
                (DISBURSEMENTPROCESSSIGNATURE.ID <> temp.ID) or
                (DISBURSEMENTPROCESSSIGNATURE.ID is null and temp.ID is not null) or
                (DISBURSEMENTPROCESSSIGNATURE.ID is not null and temp.ID is null) or
                (DISBURSEMENTPROCESSSIGNATURE.FROMAMOUNT <> temp.FROMAMOUNT) or
                (DISBURSEMENTPROCESSSIGNATURE.FROMAMOUNT is null and temp.FROMAMOUNT is not null) or
                (DISBURSEMENTPROCESSSIGNATURE.FROMAMOUNT is not null and temp.FROMAMOUNT is null) or
                (DISBURSEMENTPROCESSSIGNATURE.BANKACCOUNTAUTHORIZEDSIGNATUREID <> temp.BANKACCOUNTAUTHORIZEDSIGNATUREID) or
                (DISBURSEMENTPROCESSSIGNATURE.BANKACCOUNTAUTHORIZEDSIGNATUREID is null and temp.BANKACCOUNTAUTHORIZEDSIGNATUREID is not null) or
                (DISBURSEMENTPROCESSSIGNATURE.BANKACCOUNTAUTHORIZEDSIGNATUREID is not null and temp.BANKACCOUNTAUTHORIZEDSIGNATUREID is null)

            if @@Error <> 0
                return 3;

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

            if @@Error <> 0
                return 4;

            return 0;