USP_REVENUETRANSACTION_GETSTOCKDETAILGLDISTRIBUTION_CUSTOMUPDATEFROMXML

            CREATE PROCEDURE dbo.USP_REVENUETRANSACTION_GETSTOCKDETAILGLDISTRIBUTION_CUSTOMUPDATEFROMXML
            (
            @REVENUEID 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,
               [CREDITACCOUNT] nvarchar(100),
               [DEBITACCOUNT] nvarchar(100),
               [GLPAYMENTMETHODREVENUETYPEMAPPINGID] uniqueidentifier,
               [ID] uniqueidentifier,
               [STOCKDETAILID] uniqueidentifier,
               [PROJECT] nvarchar(100),
               [REFERENCE] nvarchar(100))

            insert into @TempTbl select 
                [AMOUNT],
                [CREDITACCOUNT],
                [DEBITACCOUNT],
                [GLPAYMENTMETHODREVENUETYPEMAPPINGID],
                [ID],
                [STOCKDETAILID],
                [PROJECT],
                [REFERENCE] 
            from dbo.UFN_REVENUETRANSACTION_GETSTOCKDETAILGLDISTRIBUTION_FROMITEMLISTXML(@XML)

            declare @PAYMENTMETHODID uniqueidentifier
            select @PAYMENTMETHODID = ID
            from dbo.REVENUEPAYMENTMETHOD
            where REVENUEID = @REVENUEID

            update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');
            update @TempTbl set STOCKDETAILID = @PAYMENTMETHODID where (STOCKDETAILID is null) or (STOCKDETAILID = '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.[STOCKDETAILGLDISTRIBUTION] where [STOCKDETAILGLDISTRIBUTION].ID in 
                (select ID from dbo.UFN_REVENUETRANSACTION_GETSTOCKDETAILGLDISTRIBUTION
                (
                    @REVENUEID
                )
                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

            update [STOCKDETAILGLDISTRIBUTION]
                    set [STOCKDETAILGLDISTRIBUTION].[AMOUNT]=temp.[AMOUNT],
                    [STOCKDETAILGLDISTRIBUTION].[CREDITACCOUNT]=temp.[CREDITACCOUNT],
                    [STOCKDETAILGLDISTRIBUTION].[DEBITACCOUNT]=temp.[DEBITACCOUNT],
                    [STOCKDETAILGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID]=temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID],
                    [STOCKDETAILGLDISTRIBUTION].[ID]=temp.[ID],
                    [STOCKDETAILGLDISTRIBUTION].[STOCKDETAILID]=temp.[STOCKDETAILID],
                    [STOCKDETAILGLDISTRIBUTION].[PROJECT]=temp.[PROJECT],
                    [STOCKDETAILGLDISTRIBUTION].[REFERENCE]=temp.[REFERENCE],
                    [STOCKDETAILGLDISTRIBUTION].CHANGEDBYID = @CHANGEAGENTID,
                    [STOCKDETAILGLDISTRIBUTION].DATECHANGED = @CHANGEDATE

                from dbo.[STOCKDETAILGLDISTRIBUTION] inner join @TempTbl as [temp] on [STOCKDETAILGLDISTRIBUTION].ID = [temp].ID
                where ([STOCKDETAILGLDISTRIBUTION].[AMOUNT]<>temp.[AMOUNT]) or 
                    ([STOCKDETAILGLDISTRIBUTION].[AMOUNT] is null and temp.[AMOUNT] is not null) or 
                    ([STOCKDETAILGLDISTRIBUTION].[AMOUNT] is not null and temp.[AMOUNT] is null) or 
                    ([STOCKDETAILGLDISTRIBUTION].[CREDITACCOUNT]<>temp.[CREDITACCOUNT]) or 
                    ([STOCKDETAILGLDISTRIBUTION].[CREDITACCOUNT] is null and temp.[CREDITACCOUNT] is not null) or 
                    ([STOCKDETAILGLDISTRIBUTION].[CREDITACCOUNT] is not null and temp.[CREDITACCOUNT] is null) or 
                    ([STOCKDETAILGLDISTRIBUTION].[DEBITACCOUNT]<>temp.[DEBITACCOUNT]) or 
                    ([STOCKDETAILGLDISTRIBUTION].[DEBITACCOUNT] is null and temp.[DEBITACCOUNT] is not null) or 
                    ([STOCKDETAILGLDISTRIBUTION].[DEBITACCOUNT] is not null and temp.[DEBITACCOUNT] is null) or 
                    ([STOCKDETAILGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID]<>temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID]) or 
                    ([STOCKDETAILGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is null and temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is not null) or 
                    ([STOCKDETAILGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is not null and temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is null) or 
                    ([STOCKDETAILGLDISTRIBUTION].[ID]<>temp.[ID]) or 
                    ([STOCKDETAILGLDISTRIBUTION].[ID] is null and temp.[ID] is not null) or 
                    ([STOCKDETAILGLDISTRIBUTION].[ID] is not null and temp.[ID] is null) or 
                    ([STOCKDETAILGLDISTRIBUTION].[STOCKDETAILID]<>temp.[STOCKDETAILID]) or 
                    ([STOCKDETAILGLDISTRIBUTION].[STOCKDETAILID] is null and temp.[STOCKDETAILID] is not null) or 
                    ([STOCKDETAILGLDISTRIBUTION].[STOCKDETAILID] is not null and temp.[STOCKDETAILID] is null) or 
                    ([STOCKDETAILGLDISTRIBUTION].[PROJECT]<>temp.[PROJECT]) or 
                    ([STOCKDETAILGLDISTRIBUTION].[PROJECT] is null and temp.[PROJECT] is not null) or 
                    ([STOCKDETAILGLDISTRIBUTION].[PROJECT] is not null and temp.[PROJECT] is null) or 
                    ([STOCKDETAILGLDISTRIBUTION].[REFERENCE]<>temp.[REFERENCE]) or 
                    ([STOCKDETAILGLDISTRIBUTION].[REFERENCE] is null and temp.[REFERENCE] is not null) or 
                    ([STOCKDETAILGLDISTRIBUTION].[REFERENCE] is not null and temp.[REFERENCE] is null)

            if @@Error <> 0
                return 3;    

            -- insert new items

            insert into [STOCKDETAILGLDISTRIBUTION] 
                ([STOCKDETAILID], 
                [AMOUNT],
                [CREDITACCOUNT],
                [DEBITACCOUNT],
                [GLPAYMENTMETHODREVENUETYPEMAPPINGID],
                [ID],
                [PROJECT],
                [REFERENCE],                
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
            select     [STOCKDETAILID], 
                [AMOUNT],
                [CREDITACCOUNT],
                [DEBITACCOUNT],
                [GLPAYMENTMETHODREVENUETYPEMAPPINGID],
                [ID],
                [PROJECT],
                [REFERENCE], 
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CHANGEDATE, 
                @CHANGEDATE
            from @TempTbl as [temp]
            where not exists (select ID from dbo.[STOCKDETAILGLDISTRIBUTION] as data where data.ID = [temp].ID)

            if @@Error <> 0
                return 4;

            return 0;