USP_REVENUE_GETSPLITS_CUSTOMUPDATEFROMXML

      CREATE procedure dbo.USP_REVENUE_GETSPLITS_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,
           [APPLICATIONCODE] tinyint,
           [DESIGNATIONID] uniqueidentifier,
           [ID] uniqueidentifier,
           [TYPECODE] tinyint,
           [CATEGORYCODEID] uniqueidentifier)

        insert into @TempTbl select 
            [AMOUNT],
            [APPLICATIONCODE],
            [DESIGNATIONID],
            [ID],
            [TYPECODE],
            [CATEGORYCODEID]
        from dbo.UFN_REVENUE_GETSPLITS_FROMITEMLISTXML(@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 any items that no longer exist in the XML table

        delete from dbo.[REVENUESPLIT] where [REVENUESPLIT].ID in 
            (select ID from dbo.UFN_REVENUE_GETSPLITS
            (
                @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 [FINANCIALTRANSACTIONLINEITEM]
                set [FINANCIALTRANSACTIONLINEITEM].[TRANSACTIONAMOUNT]=temp.[AMOUNT],
                [FINANCIALTRANSACTIONLINEITEM].[ID]=temp.[ID],
                [FINANCIALTRANSACTIONLINEITEM].CHANGEDBYID = @CHANGEAGENTID,
                [FINANCIALTRANSACTIONLINEITEM].DATECHANGED = @CHANGEDATE

            from dbo.[FINANCIALTRANSACTIONLINEITEM] 
      inner join @TempTbl as [temp] on [FINANCIALTRANSACTIONLINEITEM].ID = [temp].ID
      inner join dbo.[REVENUESPLIT_EXT] on [REVENUESPLIT_EXT].ID = [FINANCIALTRANSACTIONLINEITEM].ID
            where ([FINANCIALTRANSACTIONLINEITEM].[TRANSACTIONAMOUNT]<>temp.[AMOUNT]) or 
                ([FINANCIALTRANSACTIONLINEITEM].[TRANSACTIONAMOUNT] is null and temp.[AMOUNT] is not null) or 
                ([FINANCIALTRANSACTIONLINEITEM].[TRANSACTIONAMOUNT] is not null and temp.[AMOUNT] is null) or 
                ([REVENUESPLIT_EXT].[APPLICATIONCODE]<>temp.[APPLICATIONCODE]) or 
                ([REVENUESPLIT_EXT].[APPLICATIONCODE] is null and temp.[APPLICATIONCODE] is not null) or 
                ([REVENUESPLIT_EXT].[APPLICATIONCODE] is not null and temp.[APPLICATIONCODE] is null) or 
                ([REVENUESPLIT_EXT].[DESIGNATIONID]<>temp.[DESIGNATIONID]) or 
                ([REVENUESPLIT_EXT].[DESIGNATIONID] is null and temp.[DESIGNATIONID] is not null) or 
                ([REVENUESPLIT_EXT].[DESIGNATIONID] is not null and temp.[DESIGNATIONID] is null) or 
                ([FINANCIALTRANSACTIONLINEITEM].[ID]<>temp.[ID]) or 
                ([FINANCIALTRANSACTIONLINEITEM].[ID] is null and temp.[ID] is not null) or 
                ([FINANCIALTRANSACTIONLINEITEM].[ID] is not null and temp.[ID] is null) or 
                ([REVENUESPLIT_EXT].[TYPECODE]<>temp.[TYPECODE]) or 
                ([REVENUESPLIT_EXT].[TYPECODE] is null and temp.[TYPECODE] is not null) or 
                ([REVENUESPLIT_EXT].[TYPECODE] is not null and temp.[TYPECODE] is null)

        update [REVENUESPLIT_EXT]
                set 
                [REVENUESPLIT_EXT].[APPLICATIONCODE]=temp.[APPLICATIONCODE],
                [REVENUESPLIT_EXT].[DESIGNATIONID]=temp.[DESIGNATIONID],
                [REVENUESPLIT_EXT].[TYPECODE]=temp.[TYPECODE]

            from dbo.[FINANCIALTRANSACTIONLINEITEM] 
      inner join @TempTbl as [temp] on [FINANCIALTRANSACTIONLINEITEM].ID = [temp].ID
      inner join dbo.[REVENUESPLIT_EXT] on [REVENUESPLIT_EXT].ID = [FINANCIALTRANSACTIONLINEITEM].ID
            where ([FINANCIALTRANSACTIONLINEITEM].[TRANSACTIONAMOUNT]<>temp.[AMOUNT]) or 
                ([FINANCIALTRANSACTIONLINEITEM].[TRANSACTIONAMOUNT] is null and temp.[AMOUNT] is not null) or 
                ([FINANCIALTRANSACTIONLINEITEM].[TRANSACTIONAMOUNT] is not null and temp.[AMOUNT] is null) or 
                ([REVENUESPLIT_EXT].[APPLICATIONCODE]<>temp.[APPLICATIONCODE]) or 
                ([REVENUESPLIT_EXT].[APPLICATIONCODE] is null and temp.[APPLICATIONCODE] is not null) or 
                ([REVENUESPLIT_EXT].[APPLICATIONCODE] is not null and temp.[APPLICATIONCODE] is null) or 
                ([REVENUESPLIT_EXT].[DESIGNATIONID]<>temp.[DESIGNATIONID]) or 
                ([REVENUESPLIT_EXT].[DESIGNATIONID] is null and temp.[DESIGNATIONID] is not null) or 
                ([REVENUESPLIT_EXT].[DESIGNATIONID] is not null and temp.[DESIGNATIONID] is null) or 
                ([FINANCIALTRANSACTIONLINEITEM].[ID]<>temp.[ID]) or 
                ([FINANCIALTRANSACTIONLINEITEM].[ID] is null and temp.[ID] is not null) or 
                ([FINANCIALTRANSACTIONLINEITEM].[ID] is not null and temp.[ID] is null) or 
                ([REVENUESPLIT_EXT].[TYPECODE]<>temp.[TYPECODE]) or 
                ([REVENUESPLIT_EXT].[TYPECODE] is null and temp.[TYPECODE] is not null) or 
                ([REVENUESPLIT_EXT].[TYPECODE] is not null and temp.[TYPECODE] is null)




        if @@Error <> 0
            return 3;    

        -- insert new items

        insert into [REVENUESPLIT] 
            ([REVENUEID], 
            [AMOUNT],
            [APPLICATIONCODE],
            [DESIGNATIONID],
            [ID],
            [TYPECODE],                
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED)
        select @REVENUEID, 
            [AMOUNT],
            [APPLICATIONCODE],
            [DESIGNATIONID],
            [ID],
            [TYPECODE], 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CHANGEDATE, 
            @CHANGEDATE
        from @TempTbl as [temp]
        where not exists (select ID from dbo.[REVENUESPLIT] as data where data.ID = [temp].ID)

        if @@Error <> 0
            return 4;

        declare CATEGORYCURSOR cursor local fast_forward for
            select
                ID,
                CATEGORYCODEID
            from @TempTbl

        --TODO WHAT DO WE DO WITH THIS NOW

        declare @SPLITID uniqueidentifier;
        declare @CATEGORYCODEID uniqueidentifier;

        open CATEGORYCURSOR;

        fetch next from CATEGORYCURSOR into @SPLITID, @CATEGORYCODEID;

        while @@FETCH_STATUS = 0 
        begin 
            exec dbo.USP_REVENUECATEGORY_ADDEDIT @SPLITID, @CATEGORYCODEID, @CHANGEAGENTID;

            fetch next from CATEGORYCURSOR into @SPLITID, @CATEGORYCODEID;

        end

        deallocate CATEGORYCURSOR;        


        return 0;