USP_BENEFIT_GETGLDISTRIBUTION_CUSTOMUPDATEFROMXML_2

            CREATE procedure dbo.USP_BENEFIT_GETGLDISTRIBUTION_CUSTOMUPDATEFROMXML_2
      (
          @REVENUEID uniqueidentifier,
          @XML xml,
          @POSTDATE datetime,
          @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 (
         [ACCOUNT] nvarchar(100),
         [AMOUNT] money,
         [GLPAYMENTMETHODREVENUETYPEMAPPINGID] uniqueidentifier,
         [ID] uniqueidentifier,
         [PROJECT] nvarchar(100),
         [REFERENCE] nvarchar(100),
         [TRANSACTIONTYPECODE] tinyint,
         [ORGANIZATIONAMOUNT] money,
         [BASECURRENCYID] uniqueidentifier,
         [TRANSACTIONAMOUNT] money,
         [TRANSACTIONCURRENCYID] uniqueidentifier,
         [BASEEXCHANGERATEID] uniqueidentifier,
         [ORGANIZATIONEXCHANGERATEID] uniqueidentifier,
         [REVENUEBENEFITID] uniqueidentifier
         )

      insert into @TempTbl select 
          [ACCOUNT],
          [AMOUNT],
          [GLPAYMENTMETHODREVENUETYPEMAPPINGID],
          [ID],
          [PROJECT],
          [REFERENCE],
          [TRANSACTIONTYPECODE],
          [ORGANIZATIONAMOUNT],
          [BASECURRENCYID],
          [TRANSACTIONAMOUNT],
          [TRANSACTIONCURRENCYID],
          [BASEEXCHANGERATEID],
          [ORGANIZATIONEXCHANGERATEID],
          [REVENUEBENEFITID]
      from dbo.UFN_BENEFIT_GETGLDISTRIBUTION_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.[BENEFITGLDISTRIBUTION] where [BENEFITGLDISTRIBUTION].ID in 
          (select ID from dbo.UFN_BENEFIT_GETGLDISTRIBUTION
          (
              @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 BenefitGLDistribution table

  Merge into dbo.[BENEFITGLDISTRIBUTION] 
  using @TempTbl as [temp] on [BENEFITGLDISTRIBUTION].ID = [temp].ID
  when matched and
     (([BENEFITGLDISTRIBUTION].[ACCOUNT]<>temp.[ACCOUNT]) or 
      ([BENEFITGLDISTRIBUTION].[ACCOUNT] is null and temp.[ACCOUNT] is not null) or 
      ([BENEFITGLDISTRIBUTION].[ACCOUNT] is not null and temp.[ACCOUNT] is null) or 
      ([BENEFITGLDISTRIBUTION].[AMOUNT]<>temp.[AMOUNT]) or 
      ([BENEFITGLDISTRIBUTION].[AMOUNT] is null and temp.[AMOUNT] is not null) or 
      ([BENEFITGLDISTRIBUTION].[AMOUNT] is not null and temp.[AMOUNT] is null) or 
      ([BENEFITGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID]<>temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID]) or 
      ([BENEFITGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is null and temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is not null) or 
      ([BENEFITGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is not null and temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID] is null) or 
      ([BENEFITGLDISTRIBUTION].[ID]<>temp.[ID]) or 
      ([BENEFITGLDISTRIBUTION].[ID] is null and temp.[ID] is not null) or 
      ([BENEFITGLDISTRIBUTION].[ID] is not null and temp.[ID] is null) or 
      ([BENEFITGLDISTRIBUTION].[PROJECT]<>temp.[PROJECT]) or 
      ([BENEFITGLDISTRIBUTION].[PROJECT] is null and temp.[PROJECT] is not null) or 
      ([BENEFITGLDISTRIBUTION].[PROJECT] is not null and temp.[PROJECT] is null) or 
      ([BENEFITGLDISTRIBUTION].[REFERENCE]<>temp.[REFERENCE]) or 
      ([BENEFITGLDISTRIBUTION].[REFERENCE] is null and temp.[REFERENCE] is not null) or 
      ([BENEFITGLDISTRIBUTION].[REFERENCE] is not null and temp.[REFERENCE] is null) or 
      ([BENEFITGLDISTRIBUTION].[TRANSACTIONTYPECODE]<>temp.[TRANSACTIONTYPECODE]) or 
      ([BENEFITGLDISTRIBUTION].[TRANSACTIONTYPECODE] is null and temp.[TRANSACTIONTYPECODE] is not null) or 
      ([BENEFITGLDISTRIBUTION].[TRANSACTIONTYPECODE] is not null and temp.[TRANSACTIONTYPECODE] is null) or
      ([BENEFITGLDISTRIBUTION].[ORGANIZATIONAMOUNT]<>temp.[ORGANIZATIONAMOUNT]) or 
      ([BENEFITGLDISTRIBUTION].[ORGANIZATIONAMOUNT] is null and temp.[ORGANIZATIONAMOUNT] is not null) or 
      ([BENEFITGLDISTRIBUTION].[ORGANIZATIONAMOUNT] is not null and temp.[ORGANIZATIONAMOUNT] is null) or 
      ([BENEFITGLDISTRIBUTION].[BASECURRENCYID]<>temp.[BASECURRENCYID]) or 
      ([BENEFITGLDISTRIBUTION].[BASECURRENCYID] is null and temp.[BASECURRENCYID] is not null) or 
      ([BENEFITGLDISTRIBUTION].[BASECURRENCYID] is not null and temp.[BASECURRENCYID] is null) or 
      ([BENEFITGLDISTRIBUTION].[TRANSACTIONAMOUNT]<>temp.[TRANSACTIONAMOUNT]) or 
      ([BENEFITGLDISTRIBUTION].[TRANSACTIONAMOUNT] is null and temp.[TRANSACTIONAMOUNT] is not null) or 
      ([BENEFITGLDISTRIBUTION].[TRANSACTIONAMOUNT] is not null and temp.[TRANSACTIONAMOUNT] is null) or 
      ([BENEFITGLDISTRIBUTION].[TRANSACTIONCURRENCYID]<>temp.[TRANSACTIONCURRENCYID]) or 
      ([BENEFITGLDISTRIBUTION].[TRANSACTIONCURRENCYID] is null and temp.[TRANSACTIONCURRENCYID] is not null) or 
      ([BENEFITGLDISTRIBUTION].[TRANSACTIONCURRENCYID] is not null and temp.[TRANSACTIONCURRENCYID] is null) or 
      ([BENEFITGLDISTRIBUTION].[BASEEXCHANGERATEID]<>temp.[BASEEXCHANGERATEID]) or 
      ([BENEFITGLDISTRIBUTION].[BASEEXCHANGERATEID] is null and temp.[BASEEXCHANGERATEID] is not null) or 
      ([BENEFITGLDISTRIBUTION].[BASEEXCHANGERATEID] is not null and temp.[BASEEXCHANGERATEID] is null) or 
      ([BENEFITGLDISTRIBUTION].[ORGANIZATIONEXCHANGERATEID]<>temp.[ORGANIZATIONEXCHANGERATEID]) or 
      ([BENEFITGLDISTRIBUTION].[ORGANIZATIONEXCHANGERATEID] is null and temp.[ORGANIZATIONEXCHANGERATEID] is not null) or 
      ([BENEFITGLDISTRIBUTION].[ORGANIZATIONEXCHANGERATEID] is not null and temp.[ORGANIZATIONEXCHANGERATEID] is null) 
      or ([BENEFITGLDISTRIBUTION].[REVENUEBENEFITID] is not null and temp.[REVENUEBENEFITID] is not null) )

    then update 
      set [BENEFITGLDISTRIBUTION].[ACCOUNT]=temp.[ACCOUNT],
      [BENEFITGLDISTRIBUTION].[AMOUNT]=temp.[AMOUNT],
      [BENEFITGLDISTRIBUTION].[GLPAYMENTMETHODREVENUETYPEMAPPINGID]=temp.[GLPAYMENTMETHODREVENUETYPEMAPPINGID],
      [BENEFITGLDISTRIBUTION].[ID]=temp.[ID],
      [BENEFITGLDISTRIBUTION].[PROJECT]=temp.[PROJECT],
      [BENEFITGLDISTRIBUTION].[REFERENCE]=temp.[REFERENCE],
      [BENEFITGLDISTRIBUTION].[TRANSACTIONTYPECODE]=temp.[TRANSACTIONTYPECODE],
      [BENEFITGLDISTRIBUTION].CHANGEDBYID = @CHANGEAGENTID,
      [BENEFITGLDISTRIBUTION].DATECHANGED = @CHANGEDATE,
      --[BENEFITGLDISTRIBUTION].[ORGANIZATIONAMOUNT] = isnull(temp.[ORGANIZATIONAMOUNT],temp.[AMOUNT]),

      [BENEFITGLDISTRIBUTION].[ORGANIZATIONAMOUNT] = temp.[ORGANIZATIONAMOUNT],
      [BENEFITGLDISTRIBUTION].[BASECURRENCYID] = temp.[BASECURRENCYID],
      --[BENEFITGLDISTRIBUTION].[TRANSACTIONAMOUNT] = isnull(temp.[TRANSACTIONAMOUNT],temp.[AMOUNT]),

      [BENEFITGLDISTRIBUTION].[TRANSACTIONAMOUNT] = temp.[TRANSACTIONAMOUNT],                    
      [BENEFITGLDISTRIBUTION].[TRANSACTIONCURRENCYID] = temp.[TRANSACTIONCURRENCYID],
      [BENEFITGLDISTRIBUTION].[BASEEXCHANGERATEID] = temp.[BASEEXCHANGERATEID],
      [BENEFITGLDISTRIBUTION].[ORGANIZATIONEXCHANGERATEID] = temp.[ORGANIZATIONEXCHANGERATEID],
      [BENEFITGLDISTRIBUTION].[REVENUEBENEFITID] = temp.[REVENUEBENEFITID];


      --Update the corresponding rows in the GLTRANSACTION table

      merge into dbo.GLTRANSACTION
      using dbo.BENEFITGLDISTRIBUTION 
          inner join @TempTbl as [temp] on BENEFITGLDISTRIBUTION.ID = [temp].ID
          on BENEFITGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
      when matched 
      then update 
      set GLTRANSACTION.ACCOUNT = BENEFITGLDISTRIBUTION.ACCOUNT,
          GLTRANSACTION.AMOUNT = BENEFITGLDISTRIBUTION.AMOUNT,
          GLTRANSACTION.PROJECT = BENEFITGLDISTRIBUTION.PROJECT,
          GLTRANSACTION.REFERENCE = BENEFITGLDISTRIBUTION.REFERENCE,
          GLTRANSACTION.TRANSACTIONTYPECODE = BENEFITGLDISTRIBUTION.TRANSACTIONTYPECODE,
          GLTRANSACTION.POSTDATE = @POSTDATE,
          GLTRANSACTION.CHANGEDBYID = @CHANGEAGENTID,
          GLTRANSACTION.DATECHANGED = @CHANGEDATE,
          GLTRANSACTION.ORGANIZATIONAMOUNT = BENEFITGLDISTRIBUTION.ORGANIZATIONAMOUNT,
          GLTRANSACTION.BASECURRENCYID = BENEFITGLDISTRIBUTION.BASECURRENCYID,
          GLTRANSACTION.TRANSACTIONAMOUNT = BENEFITGLDISTRIBUTION.TRANSACTIONAMOUNT,
          GLTRANSACTION.TRANSACTIONCURRENCYID = BENEFITGLDISTRIBUTION.TRANSACTIONCURRENCYID,
          GLTRANSACTION.BASEEXCHANGERATEID = BENEFITGLDISTRIBUTION.BASEEXCHANGERATEID,
          GLTRANSACTION.ORGANIZATIONEXCHANGERATEID = BENEFITGLDISTRIBUTION.ORGANIZATIONEXCHANGERATEID,
          GLTRANSACTION.GLACCOUNTID =  dbo.UFN_GLACCOUNT_GETIDFROMACCOUNTANDREVENUE(BENEFITGLDISTRIBUTION.ACCOUNT,@REVENUEID);


      if @@Error <> 0
          return 3;    

      -- insert new items

      declare @DISTRIBUTIONS table(
              GLTRANSACTIONID uniqueidentifier,
              ACCOUNT nvarchar(100),
              AMOUNT money,
              PROJECT nvarchar(100),
              REFERENCE nvarchar(255),
              TRANSACTIONTYPECODE tinyint,
              GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
              REVENUEGLDISTRIBUTIONID uniqueidentifier,
              ORGANIZATIONAMOUNT money,
              BASECURRENCYID uniqueidentifier,
              TRANSACTIONAMOUNT money,
              TRANSACTIONCURRENCYID uniqueidentifier,
              BASEEXCHANGERATEID uniqueidentifier,
              ORGANIZATIONEXCHANGERATEID uniqueidentifier,
              REVENUEBENEFITID  uniqueidentifier
      );

      insert into @DISTRIBUTIONS(GLTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, GLPAYMENTMETHODREVENUETYPEMAPPINGID, REVENUEGLDISTRIBUTIONID,
                                  ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID,REVENUEBENEFITID)
      select 
          newid(), 
          [ACCOUNT],
          [AMOUNT],
          [PROJECT],
          [REFERENCE],
          [TRANSACTIONTYPECODE], 
          [GLPAYMENTMETHODREVENUETYPEMAPPINGID],
          [ID],
          [ORGANIZATIONAMOUNT],
          [BASECURRENCYID],
          [TRANSACTIONAMOUNT],
          [TRANSACTIONCURRENCYID],
          [BASEEXCHANGERATEID],
          [ORGANIZATIONEXCHANGERATEID],
          [REVENUEBENEFITID]
      from @TempTbl as [temp]
      where not exists (select ID from dbo.[BENEFITGLDISTRIBUTION] as data where data.ID = [temp].ID)

      -- Insert rows in GLTRANSACTION table

      insert into dbo.GLTRANSACTION(ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTDATE, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,
                                  ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, GLACCOUNTID)
      select
          GLTRANSACTIONID,
          TRANSACTIONTYPECODE,
          ACCOUNT,
          AMOUNT,
          PROJECT,
          REFERENCE,
          @POSTDATE,
          'Blackbaud Enterprise',
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CHANGEDATE,
          @CHANGEDATE,
          --isnull(ORGANIZATIONAMOUNT,AMOUNT),

          ORGANIZATIONAMOUNT,
          BASECURRENCYID,
          --isnull(TRANSACTIONAMOUNT,AMOUNT),

          TRANSACTIONAMOUNT,
          TRANSACTIONCURRENCYID,
          BASEEXCHANGERATEID,
          ORGANIZATIONEXCHANGERATEID,
          dbo.UFN_GLACCOUNT_GETIDFROMACCOUNTANDREVENUE([ACCOUNT],@REVENUEID)                
      from 
          @DISTRIBUTIONS;



      insert into dbo.BENEFITGLDISTRIBUTION
          (ID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, PROJECT, REFERENCE, AMOUNT, ACCOUNT, TRANSACTIONTYPECODE, GLTRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,
          ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, REVENUEBENEFITID)
      select
          REVENUEGLDISTRIBUTIONID,
          @REVENUEID,
          GLPAYMENTMETHODREVENUETYPEMAPPINGID,
          PROJECT,
          REFERENCE, 
          AMOUNT,
          ACCOUNT,
          TRANSACTIONTYPECODE,
          GLTRANSACTIONID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CHANGEDATE,
          @CHANGEDATE,
          --isnull(ORGANIZATIONAMOUNT,AMOUNT),

          ORGANIZATIONAMOUNT,
          BASECURRENCYID,
          --isnull(TRANSACTIONAMOUNT,AMOUNT),

          TRANSACTIONAMOUNT,
          TRANSACTIONCURRENCYID,
          BASEEXCHANGERATEID,
          ORGANIZATIONEXCHANGERATEID,
          REVENUEBENEFITID
      from 
          @DISTRIBUTIONS;    

      if @@Error <> 0
          return 4;

      return 0;