USP_AGINGBUCKET_UPDATEFROMXML

        create procedure dbo.USP_AGINGBUCKET_UPDATEFROMXML
            (
                @APPLICATIONCODE tinyint,
                @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();

            declare @TempTbl table
                (
                [ID] uniqueidentifier,
                [PERIODNUMBER] int,
                [PERIODSTART] varchar (20),
                [PERIODEND] varchar (20),
                [PERIODDESCRIPTION]  varchar (20)); 

            insert into @TempTbl select
                [ID],
                [PERIODNUMBER],
                case when charindex('.', [PERIODSTART]) > 0 then 0 else [PERIODSTART] end,
                case when [PERIODEND] = 'over' then 32767 else (case when isnumeric([PERIODEND]) <> 1 then 0 else 
                    (case when charindex('.', [PERIODEND]) > 0 then 0 else [PERIODEND] end) end) end,
                [PERIODDESCRIPTION]
            from dbo.UFN_AGINGBUCKET_FROMPERIODLISTXML(@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.AGINGBUCKET where AGINGBUCKET.ID in
            (select ID from dbo.AGINGBUCKET where AGINGBUCKET.APPLICATIONCODE = @APPLICATIONCODE
            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.AGINGBUCKET set
                AGINGBUCKET.ID = temp.ID,
                AGINGBUCKET.APPLICATIONCODE = @APPLICATIONCODE,
                AGINGBUCKET.PERIODNUMBER = temp.[PERIODNUMBER],
                AGINGBUCKET.PERIODSTART = temp.[PERIODSTART],
                AGINGBUCKET.PERIODEND = temp.[PERIODEND],
                AGINGBUCKET.PERIODDESCRIPTION = temp.[PERIODDESCRIPTION],
                AGINGBUCKET.CHANGEDBYID = @CHANGEAGENTID,
                AGINGBUCKET.DATECHANGED = @CHANGEDATE
            from dbo.AGINGBUCKET
            inner join @TempTbl as temp on AGINGBUCKET.ID = temp.ID
            where (AGINGBUCKET.ID <> temp.ID) or
                (AGINGBUCKET.ID is null and temp.ID is not null) or
                (AGINGBUCKET.ID is not null and temp.ID is null) or
                (AGINGBUCKET.PERIODNUMBER <> temp.[PERIODNUMBER]) or
                (AGINGBUCKET.PERIODNUMBER is null and temp.[PERIODNUMBER] is not null) or
                (AGINGBUCKET.PERIODNUMBER is not null and temp.[PERIODNUMBER] is null) or
                (AGINGBUCKET.PERIODSTART <> temp.[PERIODSTART]) or
                (AGINGBUCKET.PERIODSTART is null and temp.[PERIODSTART] is not null) or
                (AGINGBUCKET.PERIODSTART is not null and temp.[PERIODSTART] is null) or
                (AGINGBUCKET.PERIODEND <> temp.[PERIODEND]) or
                (AGINGBUCKET.PERIODEND is null and temp.[PERIODEND] is not null) or
                (AGINGBUCKET.PERIODEND is not null and temp.[PERIODEND] is null) or
                (AGINGBUCKET.PERIODDESCRIPTION <> temp.[PERIODDESCRIPTION]) or
                (AGINGBUCKET.PERIODDESCRIPTION is null and temp.[PERIODDESCRIPTION] is not null) or
                (AGINGBUCKET.PERIODDESCRIPTION is not null and temp.[PERIODDESCRIPTION] is null); 

            if @@Error <> 0
            return 3;

            insert into dbo.AGINGBUCKET(
                ID,
                APPLICATIONCODE,
                PERIODNUMBER,
                PERIODSTART,
                PERIODEND,
                PERIODDESCRIPTION,
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
            select
                ID,
                @APPLICATIONCODE,
                [PERIODNUMBER],
                [PERIODSTART],
                [PERIODEND],
                [PERIODDESCRIPTION],
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CHANGEDATE,
                @CHANGEDATE
            from @TempTbl as temp
            where not exists (select ID from dbo.AGINGBUCKET as data where data.ID = temp.ID);  

            if @@Error <> 0
                return 4;

            return 0;