USP_REVENUE_GETBENEFITS_5_UPDATEFROMXML

            create procedure dbo.USP_REVENUE_GETBENEFITS_5_UPDATEFROMXML
            (
                @REVENUEID uniqueidentifier,
                @XML xml,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @ADJUSTMENTID uniqueidentifier = null,
                @ADJUSTMENTPOSTDATE 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
            (
                [BASECURRENCYID] uniqueidentifier,
                [BASEEXCHANGERATEID] uniqueidentifier,
                [BENEFITID] uniqueidentifier,
                [DETAILS] nvarchar(255),
                [ID] uniqueidentifier,
                [ORGANIZATIONEXCHANGERATEID] uniqueidentifier,
                [ORGANIZATIONTOTALVALUE] money,
                [PERCENTAPPLICABLEAMOUNT] money,
                [QUANTITY] int,
                [REVENUESPLITID] uniqueidentifier,
                [SEQUENCE] int,
                [TRANSACTIONCURRENCYID] uniqueidentifier,
                [TRANSACTIONTOTALVALUE] money,
                [UNITVALUE] money,
                [VALUEPERCENT] decimal(20, 4),
                [SENDBENEFITCODE] tinyint,
                [BENEFITTYPECODE] tinyint,
                [SOURCELINEITEMID] uniqueidentifier,
                [IDEXISTS] bit
            );

            --Expenses

            insert into @TempTbl
            select
                temp.[BASECURRENCYID],
                temp.[BASEEXCHANGERATEID],
                temp.[BENEFITID],
                left(temp.[DETAILS], 255),
                case
                    when temp.[ID] is null or temp.[ID] = '00000000-0000-0000-0000-000000000000' then newid()
                    else temp.[ID]
                end,
                temp.[ORGANIZATIONEXCHANGERATEID],
                temp.[ORGANIZATIONTOTALVALUE],
                coalesce(temp.[PERCENTAPPLICABLEAMOUNT], 0.00),
                coalesce(temp.[QUANTITY], 0),
                temp.[REVENUESPLITID],
                temp.[SEQUENCE],
                temp.[TRANSACTIONCURRENCYID],
                temp.[TRANSACTIONTOTALVALUE],
                coalesce(temp.[UNITVALUE], 0.00),
                coalesce(temp.[VALUEPERCENT], 0.00),
                B.[SENDBENEFITCODE],
                1,
                null,
                case
                    when exists (select top 1 ID from dbo.FINANCIALTRANSACTIONLINEITEM where ID = temp.ID or SOURCELINEITEMID = temp.ID) then 1
                    else 0
                end
            from dbo.UFN_REVENUE_GETBENEFITS_3_FROMITEMLISTXML(@XML) temp
                inner join dbo.BENEFIT B on B.ID = temp.BENEFITID;

            --Liabilities

            insert into @TempTbl
            select
                temp.[BASECURRENCYID],
                temp.[BASEEXCHANGERATEID],
                temp.[BENEFITID],
                temp.[DETAILS],
                case
                    when LI.[ID] is null or LI.[ID] = '00000000-0000-0000-0000-000000000000' then newid()
                    else LI.[ID]
                end,
                temp.[ORGANIZATIONEXCHANGERATEID],
                temp.[ORGANIZATIONTOTALVALUE],
                coalesce(temp.[PERCENTAPPLICABLEAMOUNT], 0.00),
                coalesce(temp.[QUANTITY], 0),
                temp.[REVENUESPLITID],
                temp.[SEQUENCE],
                temp.[TRANSACTIONCURRENCYID],
                temp.[TRANSACTIONTOTALVALUE],
                coalesce(temp.[UNITVALUE], 0.00),
                coalesce(temp.[VALUEPERCENT], 0.00),
                temp.[SENDBENEFITCODE],
                2,
                temp.ID,
                case
                    when exists (select top 1 ID from dbo.FINANCIALTRANSACTIONLINEITEM where ID = temp.ID or SOURCELINEITEMID = temp.ID) then 1
                    else 0
                end
            from @TempTbl temp
                left join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.SOURCELINEITEMID = temp.ID
                left join dbo.REVENUEBENEFIT_EXT RBX on RBX.ID = LI.ID
            where
                temp.[SENDBENEFITCODE] = 1
                and (RBX.ID is null or RBX.BENEFITTYPECODE = 2)
                and (select TYPECODE from dbo.FINANCIALTRANSACTION FT where FT.ID = @REVENUEID) = 1
                and LI.DELETEDON is null;

            if @@Error <> 0
                return 1;

            declare @REVENUEBASECURRENCYID uniqueidentifier = null;
            select @REVENUEBASECURRENCYID = REVENUE_EXT.NONPOSTABLEBASECURRENCYID
            from dbo.FINANCIALTRANSACTION inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
            where FINANCIALTRANSACTION.ID = @REVENUEID;

            if @REVENUEBASECURRENCYID is null
            begin
                select @REVENUEBASECURRENCYID = V.BASECURRENCYID
                from dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand)
                where FINANCIALTRANSACTIONID = @REVENUEID;
            end

            declare @ADJUSTMENTPOSTSTATUSCODE tinyint;
            if not @ADJUSTMENTID is null
            begin
             select top 1
                 @ADJUSTMENTPOSTSTATUSCODE = A.POSTSTATUSCODE,
                 @ADJUSTMENTPOSTDATE = A.POSTDATE
             from dbo.BENEFITADJUSTMENT A
             where A.ID = @ADJUSTMENTID
             order by A.DATEADDED desc;
            end


            --danwar check for initial adjustment of payment record and set to match

            declare @INITIALADJUSTMENTID uniqueidentifier
            select top 1
                @INITIALADJUSTMENTID = LIA.ID
            from
                dbo.FINANCIALTRANSACTIONLINEITEM LI
                inner join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT LIA on LIA.ID = LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
            where
                LIA.ADJUSTMENTREASONCODEID is null 
                and LI.FINANCIALTRANSACTIONID = @REVENUEID
                and LI.POSTSTATUSCODE = 2 and LI.TYPECODE = 0;

            if (select count(*)
                from @TempTbl [T]
                inner join dbo.BENEFIT on BENEFIT.ID = [T].BENEFITID
                where BENEFIT.BASECURRENCYID <> @REVENUEBASECURRENCYID
                and BENEFIT.USEPERCENT <> 1) > 0
            begin
                raiserror('BBERR_BENEFIT_BASECURRENCY_NOTEQUAL_REVENUE_BASECURRENCY', 13, 1);
                return 1;
            end

            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;

            declare @LINEITEMSTODELETE table (ID uniqueidentifier)
            insert into @LINEITEMSTODELETE
            select
                LI.ID
            from
                dbo.FINANCIALTRANSACTION FT
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
                inner join dbo.REVENUEBENEFIT_EXT RBX on RBX.ID = LI.ID
                left join dbo.FINANCIALTRANSACTIONLINEITEM REVERSALS on REVERSALS.REVERSEDLINEITEMID = LI.ID
                left join @TempTbl T on T.ID = LI.ID
            where
                T.ID is null
                and LI.POSTSTATUSCODE <> 2
                -- Bug#257538, 257565 & 257622 - We don't require ADJUSTMENTID check in normal condition.

                -- These checks were added for bug#218452/242312/254641.

                -- Earlier version of this SP USP_REVENUE_GETBENEFITS_3_UPDATEFROMXML was not having ADJUSTMENTID parameter.

                -- So if ADJUSTMENTID is not supplied we can safely assume that its not required.

                and (@ADJUSTMENTID is null or LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID <> @ADJUSTMENTID)
                and LI.DELETEDON is null --if they've already been deleted, ignore them

                and FT.ID = @REVENUEID
                and RBX.BENEFITTYPECODE in (1, 2)
                and REVERSALS.ID is null;

            --also delete backouts for liabilities to delete

            insert into @LINEITEMSTODELETE
            select
                LI.ID
            from
                @LINEITEMSTODELETE L
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.SOURCELINEITEMID = L.ID;

            -- delete any items that no longer exist in the XML table

            if exists (select top 1 1 from @LINEITEMSTODELETE)
            begin
                delete from dbo.[FINANCIALTRANSACTIONLINEITEM]
                where [FINANCIALTRANSACTIONLINEITEM].ID in (select ID from @LINEITEMSTODELETE);
            end

            delete from @LINEITEMSTODELETE;

            insert into @LINEITEMSTODELETE
            select
                LI.ID
            from
                dbo.FINANCIALTRANSACTION FT
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
                inner join dbo.REVENUEBENEFIT_EXT RBX on RBX.ID = LI.ID
                left join @TempTbl T on T.ID = LI.ID
            where
                T.ID is null
                and (LI.POSTSTATUSCODE = 2 or LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID)
                and LI.DELETEDON is null --if they've already been deleted, ignore them

                and FT.ID = @REVENUEID
                and RBX.BENEFITTYPECODE in (1, 2);

            --also delete backouts for liabilities to delete

            insert into @LINEITEMSTODELETE
            select
                LI.ID
            from
                @LINEITEMSTODELETE L
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.SOURCELINEITEMID = L.ID
            where
                LI.DELETEDON is null; --if they've already been deleted, ignore them


            -- hide any line items that have been waived or removed but were previously posted

            if exists (select top 1 1 from @LINEITEMSTODELETE)
            begin
                update LI set
                    POSTSTATUSCODE =
                        case
                            when LI.POSTSTATUSCODE = 1 then 3    -- Change 'not posted' to 'do not post'

                            else LI.POSTSTATUSCODE
                        end,
                    FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID,
                    DELETEDON = @CHANGEDATE
                from
                    @LINEITEMSTODELETE L
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = L.ID;
            end

            select @e=@@error;

            -- reset CONTEXT_INFO to previous value 

            if not @contextCache is null
                set CONTEXT_INFO @contextCache;

            if @e <> 0
                return 2;

            if exists( select * from @TempTbl where QUANTITY < 0 )
                raiserror('CK_REVENUEBENEFIT_QUANTITY_NONNEGATIVE', 16, 1);

            if exists( select * from @TempTbl where not (UNITVALUE=0 or VALUEPERCENT=0) )
                raiserror('CK_REVENUEBENEFIT_UNITVALUE_VALUEPERCENT', 16, 1);

            if exists( select * from @TempTbl where (VALUEPERCENT < 0) )
                raiserror('CK_REVENUEBENEFIT_VALUEPERCENT_NONNEGATIVE', 16, 1);

            if exists(select 1 from @TempTbl where UNITVALUE < 0)
                raiserror('CK_REVENUEBENEFIT_VALUE_NONNEGATIVE', 16, 1);

            if exists(select * from @TempTbl where PERCENTAPPLICABLEAMOUNT < 0 )
                raiserror('CK_REVENUEBENEFIT_PERCENTAPPLICABLEAMOUNT_NONNEGATIVE', 16, 1);

            if exists(select * from @TempTbl where TRANSACTIONTOTALVALUE < 0 )
                raiserror('CK_REVENUEBENEFIT_TRANSACTIONTOTALVALUE_NONNEGATIVE', 16, 1);

            if exists(select * from @TempTbl where ORGANIZATIONTOTALVALUE < 0 )
                raiserror('CK_REVENUEBENEFIT_ORGANIZATIONTOTALVALUE_NONNEGATIVE', 16, 1);

            -- Since we're updating existing records, we can ignore those that don't exist

            if exists (select top 1 ID from @TempTbl where IDEXISTS = 1)
            begin
                update LI set
                    DESCRIPTION = temp.DETAILS
                    ,ORGAMOUNT = temp.ORGANIZATIONTOTALVALUE
                    ,QUANTITY = cast(temp.QUANTITY as decimal(20,4))
                    ,SEQUENCE = temp.SEQUENCE
                    ,TRANSACTIONAMOUNT = temp.TRANSACTIONTOTALVALUE
                    ,BASEAMOUNT = cast((temp.QUANTITY * temp.UNITVALUE) + (temp.PERCENTAPPLICABLEAMOUNT * temp.VALUEPERCENT/100) as money)
                    ,UNITVALUE = temp.UNITVALUE
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CHANGEDATE
                    ,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = isnull(@ADJUSTMENTID, @INITIALADJUSTMENTID)
                    ,POSTSTATUSCODE = case when @ADJUSTMENTPOSTSTATUSCODE is null then LI.POSTSTATUSCODE else case @ADJUSTMENTPOSTSTATUSCODE when 2 then 3 else 1 end end
                    ,POSTDATE = isnull(@ADJUSTMENTPOSTDATE, LI.POSTDATE)
                from @TempTbl temp
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on (LI.ID = temp.ID or LI.SOURCELINEITEMID = temp.ID)
                where
                    (LI.POSTSTATUSCODE <> 2) and
                    (([LI].[DESCRIPTION]<>temp.[DETAILS]) or
                    ([LI].[ORGAMOUNT]<>temp.[ORGANIZATIONTOTALVALUE]) or
                    ([LI].[QUANTITY]<>temp.[QUANTITY]) or
                    ([LI].[SEQUENCE]<>temp.[SEQUENCE]) or
                    ([LI].[TRANSACTIONAMOUNT]<>temp.[TRANSACTIONTOTALVALUE]) or
                    ([LI].[UNITVALUE]<>temp.[UNITVALUE]) or
                    (@ADJUSTMENTPOSTSTATUSCODE <> [LI].POSTSTATUSCODE) or
                    (@ADJUSTMENTPOSTDATE <> [LI].POSTDATE) or
                    (@ADJUSTMENTID <> LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID));

                --delete any Journal Entries for DNP line items

                delete from dbo.JOURNALENTRY where FINANCIALTRANSACTIONLINEITEMID in
                    (select
                        LI.ID
                    from
                        @TempTbl temp
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = temp.ID
                    where LI.POSTSTATUSCODE = 3);

                if @ADJUSTMENTID is null
                begin
                    update EXT set
                        BENEFITID = temp.BENEFITID
                        ,PERCENTAPPLICABLEAMOUNT = temp.PERCENTAPPLICABLEAMOUNT
                        ,REVENUESPLITID = temp.REVENUESPLITID
                        ,VALUEPERCENT = temp.VALUEPERCENT
                        ,CHANGEDBYID = @CHANGEAGENTID
                        ,DATECHANGED = @CHANGEDATE
                    from @TempTbl temp
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on (LI.ID = temp.ID or LI.SOURCELINEITEMID = temp.ID)
                    inner join dbo.REVENUEBENEFIT_EXT EXT on temp.ID = EXT.ID
                    where
                            LI.POSTSTATUSCODE <> 2 and
                            (([EXT].[BENEFITID]<>temp.[BENEFITID]) or 
                            ([EXT].[PERCENTAPPLICABLEAMOUNT]<>temp.[PERCENTAPPLICABLEAMOUNT]) or 
                            ([EXT].[REVENUESPLITID]<>temp.[REVENUESPLITID]) or 
                            ([EXT].[VALUEPERCENT]<>temp.[VALUEPERCENT]));
                end
                else
                begin
                    update EXT set
                        BENEFITID = temp.BENEFITID
                        ,PERCENTAPPLICABLEAMOUNT = temp.PERCENTAPPLICABLEAMOUNT
                        ,REVENUESPLITID = temp.REVENUESPLITID
                        ,VALUEPERCENT = temp.VALUEPERCENT
                        ,CHANGEDBYID = @CHANGEAGENTID
                        ,DATECHANGED = @CHANGEDATE
                    from @TempTbl temp
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on (LI.ID = temp.ID or LI.SOURCELINEITEMID = temp.ID)
                        inner join dbo.REVENUEBENEFIT_EXT EXT on temp.ID = EXT.ID
                    where
                        (([EXT].[BENEFITID]<>temp.[BENEFITID]) or
                        ([EXT].[PERCENTAPPLICABLEAMOUNT]<>temp.[PERCENTAPPLICABLEAMOUNT]) or
                        ([EXT].[REVENUESPLITID]<>temp.[REVENUESPLITID]) or
                        ([EXT].[VALUEPERCENT]<>temp.[VALUEPERCENT]));
                end

                if @@Error <> 0
                    return 3;
            end

            -- insert new items

            if exists (select top 1 1 from @TempTbl where IDEXISTS = 0)
            begin
                insert into dbo.FINANCIALTRANSACTIONLINEITEM
                (
                    ID
                    ,FINANCIALTRANSACTIONID
                    ,TRANSACTIONAMOUNT
                    ,BASEAMOUNT
                    ,ORGAMOUNT
                    ,QUANTITY
                    ,UNITVALUE
                    ,VISIBLE
                    ,DESCRIPTION
                    ,SEQUENCE
                    ,TYPECODE
                    ,POSTDATE
                    ,POSTSTATUSCODE
                    ,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
                    ,SOURCELINEITEMID
                    ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                select
                    temp.ID
                    ,@REVENUEID
                    ,isnull(temp.TRANSACTIONTOTALVALUE, 0)
                    ,cast((temp.QUANTITY * temp.UNITVALUE) + (temp.PERCENTAPPLICABLEAMOUNT * temp.VALUEPERCENT/100) as money)                
                    ,isnull(temp.ORGANIZATIONTOTALVALUE, 0)
                    ,isnull(temp.QUANTITY, 0)
                    ,isnull(temp.UNITVALUE, 0)
                    ,0
                    ,isnull(temp.DETAILS,'')
                    ,isnull(temp.SEQUENCE,0)
                    ,3
                    ,isnull(@ADJUSTMENTPOSTDATE, FT.POSTDATE)
                    ,case
                        when @ADJUSTMENTPOSTSTATUSCODE is null then
                            case FT.PostStatusCode
                                when 3 then 3
                                else 1
                            end
                        else
                            case @ADJUSTMENTPOSTSTATUSCODE
                                when 2 then 3
                                else 1
                            end
                    end
                    ,@ADJUSTMENTID
                    ,case temp.BENEFITTYPECODE when 1 then null when 2 then temp.SOURCELINEITEMID end
                    ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                from @TempTbl as [temp]
                inner join dbo.FINANCIALTRANSACTION FT on FT.ID = @REVENUEID
                where temp.IDEXISTS = 0;

                insert into dbo.REVENUEBENEFIT_EXT
                (
                    ID
                    ,BENEFITID
                    ,SENDBENEFIT
                    ,PERCENTAPPLICABLEAMOUNT
                    ,VALUEPERCENT
                    ,REVENUESPLITID
                    ,BENEFITTYPECODE
                    ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                select
                    temp.ID
                    ,temp.BENEFITID
                    ,temp.SENDBENEFITCODE
                    ,isnull(temp.PERCENTAPPLICABLEAMOUNT, 0)
                    ,isnull(temp.VALUEPERCENT, 0)
                    ,temp.REVENUESPLITID
                    ,temp.BENEFITTYPECODE
                    ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                from @TempTbl as [temp]
                    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = @REVENUEID
                where temp.IDEXISTS = 0;

                if @@Error <> 0
                    return 4;
            end
            return 0;