USP_REVENUE_GETBENEFITS_3_ADDFROMXML

            CREATE procedure dbo.USP_REVENUE_GETBENEFITS_3_ADDFROMXML
            (
                @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 (
               [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),
               [TOTALVALUE] money)

            insert into @TempTbl select 
                [BASECURRENCYID],
                [BASEEXCHANGERATEID],
                [BENEFITID],
                [DETAILS],
                [ID],
                [ORGANIZATIONEXCHANGERATEID],
                [ORGANIZATIONTOTALVALUE],
                [PERCENTAPPLICABLEAMOUNT],
                [QUANTITY],
                [REVENUESPLITID],
                [SEQUENCE],
                [TRANSACTIONCURRENCYID],
                [TRANSACTIONTOTALVALUE],
                [UNITVALUE],
                [VALUEPERCENT],
                isnull(cast(isnull((QUANTITY * UNITVALUE), 0) + isnull((PERCENTAPPLICABLEAMOUNT * VALUEPERCENT/100), 0) as money),0) as TOTALVALUE 
            from dbo.UFN_REVENUE_GETBENEFITS_3_FROMITEMLISTXML(@XML)

            update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

            if @@Error <> 0
                return 1;

            declare @REVENUEBASECURRENCYID uniqueidentifier = null;
            select @REVENUEBASECURRENCYID = REVENUE.BASECURRENCYID from dbo.REVENUE where ID = @REVENUEID;

            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 @ORGANIZATIONCURRENCYID as uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();                                    

            insert into dbo.FINANCIALTRANSACTIONLINEITEM
            (ID, FINANCIALTRANSACTIONID, TRANSACTIONAMOUNT, QUANTITY, UNITVALUE, VISIBLE, 
             ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, DESCRIPTION, SEQUENCE,
             TYPECODE, POSTDATE, POSTSTATUSCODE, BASEAMOUNT,ORGAMOUNT)
            select 
              temp.ID, 
              @REVENUEID, 
              case when 
                  temp.ORGANIZATIONEXCHANGERATEID is null
                  and (temp.BASECURRENCYID is null or temp.BASECURRENCYID = @ORGANIZATIONCURRENCYID)
                  and temp.BASEEXCHANGERATEID is null
                  and (temp.TRANSACTIONCURRENCYID is null or temp.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID)
                  and (
                    temp.ORGANIZATIONTOTALVALUE <> temp.TOTALVALUE
                    or temp.TRANSACTIONTOTALVALUE <> temp.TOTALVALUE
                    or temp.TOTALVALUE = 0
                                    or temp.TRANSACTIONTOTALVALUE is null
                  )
                then isnull(cast(isnull((QUANTITY * UNITVALUE), 0) + isnull((PERCENTAPPLICABLEAMOUNT * VALUEPERCENT/100), 0) as money),0)
                else isnull(temp.TRANSACTIONTOTALVALUE,0)
              end, 
              temp.QUANTITY,
              temp.UNITVALUE, 
              0, 
              @CHANGEAGENTID, 
              @CHANGEAGENTID, 
              @CHANGEDATE,@CHANGEDATE, 
              temp.DETAILS, 
              temp.SEQUENCE, 
              3, 
              FT.PostDate, 
              case FT.PostStatusCode when 3 then 3 else 1 end,      
              isnull(cast(isnull((temp.QUANTITY * temp.UNITVALUE), 0) + isnull((temp.PERCENTAPPLICABLEAMOUNT * temp.VALUEPERCENT/100), 0) as money),0) as TOTALVALUE, 
              case when 
                  temp.ORGANIZATIONEXCHANGERATEID is null
                  and (temp.BASECURRENCYID is null or temp.BASECURRENCYID = @ORGANIZATIONCURRENCYID)
                  and temp.BASEEXCHANGERATEID is null
                  and (temp.TRANSACTIONCURRENCYID is null or temp.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID)
                  and (
                    temp.ORGANIZATIONTOTALVALUE <> temp.TOTALVALUE
                    or temp.TRANSACTIONTOTALVALUE <> temp.TOTALVALUE
                    or temp.TOTALVALUE = 0
                                    or temp.ORGANIZATIONTOTALVALUE is null
                  )
                then isnull(cast(isnull((QUANTITY * UNITVALUE), 0) + isnull((PERCENTAPPLICABLEAMOUNT * VALUEPERCENT/100), 0) as money),0)
                else isnull(temp.ORGANIZATIONTOTALVALUE,0)
              end 
            from @TempTbl as temp inner join FINANCIALTRANSACTION as FT on @REVENUEID = FT.ID;

            insert into dbo.REVENUEBENEFIT_EXT(
              ID
            , BENEFITID
            , SENDBENEFIT
            , PERCENTAPPLICABLEAMOUNT
            , VALUEPERCENT
            , REVENUESPLITID
            , ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            select 
              ID
            , BENEFITID
            , 0 --SENDBENEFIT

            , PERCENTAPPLICABLEAMOUNT
            , VALUEPERCENT
            , REVENUESPLITID
            , @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE 
            from @TempTbl as temp


          if @@Error <> 0
            return 2;

            return 0;