USP_REVENUE_GETSPLITS_3_CUSTOMUPDATEFROMXML

CREATE procedure dbo.USP_REVENUE_GETSPLITS_3_CUSTOMUPDATEFROMXML (
  @REVENUEID uniqueidentifier
  ,@XML xml
  ,@CHANGEAGENTID uniqueidentifier = null
  ,@CHANGEDATE datetime = null
  ,@POSTDATE 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
  ,[BASECURRENCYID] uniqueidentifier
  ,[BASEEXCHANGERATEID] uniqueidentifier
  ,[CATEGORYCODEID] uniqueidentifier
  ,[DESIGNATIONID] uniqueidentifier
  ,[ID] uniqueidentifier
  ,[ORGANIZATIONAMOUNT] money
  ,[ORGANIZATIONEXCHANGERATEID] uniqueidentifier
  ,[TRANSACTIONAMOUNT] money
  ,[TRANSACTIONCURRENCYID] uniqueidentifier
  ,[TYPECODE] tinyint
  )

insert into @TempTbl
select [AMOUNT]
  ,[APPLICATIONCODE]
  ,[BASECURRENCYID]
  ,[BASEEXCHANGERATEID]
  ,[CATEGORYCODEID]
  ,[DESIGNATIONID]
  ,[ID]
  ,[ORGANIZATIONAMOUNT]
  ,[ORGANIZATIONEXCHANGERATEID]
  ,[TRANSACTIONAMOUNT]
  ,[TRANSACTIONCURRENCYID]
  ,[TYPECODE]
from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@XML)

declare @POSTSTATUSCODE tinyint;

select @POSTSTATUSCODE = case when FT.POSTSTATUSCODE = 2 then 1 else FT.POSTSTATUSCODE end
  ,@POSTDATE = case FT.POSTSTATUSCODE when 3 then FT.[DATE] else isnull(@POSTDATE, FT.POSTDATE) end
from dbo.FINANCIALTRANSACTION FT
where FT.ID = @REVENUEID;

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;

declare @SPLITSTODELETE xml

set @SPLITSTODELETE = (
    select SPLITS.ID
    from (
      select ID
      from dbo.UFN_REVENUE_GETSPLITS_2(@REVENUEID)

      except

      select ID
      from @TempTbl
      ) SPLITS
    for xml raw('ITEM')
      ,type
      ,elements
      ,root('SPLITSTODELETE')
      ,binary BASE64
    );

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

--exec dbo.USP_REVENUESPLIT_DELETESPLITS @SPLITSTODELETE, @CHANGEAGENTID, @CHANGEDATE;

exec dbo.USP_FINANCIALTRANSACTIONLINEITEM_DELETESPLITSANDMARKORPHANED @SPLITSTODELETE
  ,@CHANGEAGENTID
  ,@CHANGEDATE

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

delete
from dbo.[REVENUECATEGORY]
where [REVENUECATEGORY].ID in (
    select ID
    from dbo.UFN_REVENUE_GETSPLITS_2(@REVENUEID)

    except

    select data.ID
    from @TempTbl as data
    where (not data.CATEGORYCODEID is null)
    )

select @e = @@error;

-- reset CONTEXT_INFO to previous value 

if not @contextCache is null
  set CONTEXT_INFO @contextCache;

if @e <> 0
  return 2;

declare @FINANCIALTRANSACTIONLINEITEMADJUSTMENTID uniqueidentifier;

select @FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = LIA.ID
from dbo.ADJUSTMENT A
inner join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT LIA on LIA.ID = A.ID
where A.REVENUEID = @REVENUEID
  and A.POSTSTATUSCODE <> 0

declare @ORGANIZATIONCURRENCYID as uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY ();

update FINANCIALTRANSACTIONLINEITEM
set BASEAMOUNT = temp.AMOUNT
  ,ORGAMOUNT = 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 (
        isnull(temp.ORGANIZATIONAMOUNT, 0) <> isnull(temp.AMOUNT, 0)
        or isnull(temp.TRANSACTIONAMOUNT, 0) <> isnull(temp.AMOUNT, 0)
        or isnull(temp.AMOUNT, 0) = 0
        )
      and (
        FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT = FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT
        and FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT = FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT
        ) then temp.AMOUNT else isnull(temp.ORGANIZATIONAMOUNT, 0) end
  ,TRANSACTIONAMOUNT = 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 (
        isnull(temp.ORGANIZATIONAMOUNT, 0) <> isnull(temp.AMOUNT, 0)
        or isnull(temp.TRANSACTIONAMOUNT, 0) <> isnull(temp.AMOUNT, 0)
        or isnull(temp.AMOUNT, 0) = 0
        )
      and (
        FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT = FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT
        and FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT = FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT
        ) then temp.AMOUNT else isnull(temp.TRANSACTIONAMOUNT, 0) end
  ,CHANGEDBYID = @CHANGEAGENTID
  ,DATECHANGED = @CHANGEDATE
  ,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join @TempTbl as temp on FINANCIALTRANSACTIONLINEITEM.ID = temp.ID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
where ([FINANCIALTRANSACTIONLINEITEM].[BASEAMOUNT] <> temp.[AMOUNT])
  or (
    [FINANCIALTRANSACTIONLINEITEM].[BASEAMOUNT] is null
    and temp.[AMOUNT] is not null
    )
  or (
    [FINANCIALTRANSACTIONLINEITEM].[BASEAMOUNT] 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 ([FINANCIALTRANSACTION].[BASEEXCHANGERATEID] <> temp.[BASEEXCHANGERATEID])
  or (
    [FINANCIALTRANSACTION].[BASEEXCHANGERATEID] is null
    and temp.[BASEEXCHANGERATEID] is not null
    )
  or (
    [FINANCIALTRANSACTION].[BASEEXCHANGERATEID] is not null
    and temp.[BASEEXCHANGERATEID] 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].[ORGAMOUNT] <> temp.[ORGANIZATIONAMOUNT])
  or (
    [FINANCIALTRANSACTIONLINEITEM].[ORGAMOUNT] is null
    and temp.[ORGANIZATIONAMOUNT] is not null
    )
  or (
    [FINANCIALTRANSACTIONLINEITEM].[ORGAMOUNT] is not null
    and temp.[ORGANIZATIONAMOUNT] is null
    )
  or ([FINANCIALTRANSACTION].[ORGEXCHANGERATEID] <> temp.[ORGANIZATIONEXCHANGERATEID])
  or (
    [FINANCIALTRANSACTION].[ORGEXCHANGERATEID] is null
    and temp.[ORGANIZATIONEXCHANGERATEID] is not null
    )
  or (
    [FINANCIALTRANSACTION].[ORGEXCHANGERATEID] is not null
    and temp.[ORGANIZATIONEXCHANGERATEID] is null
    )
  or ([FINANCIALTRANSACTIONLINEITEM].[TRANSACTIONAMOUNT] <> temp.[TRANSACTIONAMOUNT])
  or (
    [FINANCIALTRANSACTIONLINEITEM].[TRANSACTIONAMOUNT] is null
    and temp.[TRANSACTIONAMOUNT] is not null
    )
  or (
    [FINANCIALTRANSACTIONLINEITEM].[TRANSACTIONAMOUNT] is not null
    and temp.[TRANSACTIONAMOUNT] is null
    )
  or ([FINANCIALTRANSACTION].[TRANSACTIONCURRENCYID] <> temp.[TRANSACTIONCURRENCYID])
  or (
    [FINANCIALTRANSACTION].[TRANSACTIONCURRENCYID] is null
    and temp.[TRANSACTIONCURRENCYID] is not null
    )
  or (
    [FINANCIALTRANSACTION].[TRANSACTIONCURRENCYID] is not null
    and temp.[TRANSACTIONCURRENCYID] 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 APPLICATIONCODE = temp.APPLICATIONCODE
  ,DESIGNATIONID = temp.DESIGNATIONID
  ,TYPECODE = temp.TYPECODE
  ,CHANGEDBYID = @CHANGEAGENTID
  ,DATECHANGED = @CHANGEDATE
from dbo.REVENUESPLIT_EXT
inner join @TempTbl as temp on REVENUESPLIT_EXT.ID = temp.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
where ([FINANCIALTRANSACTIONLINEITEM].[BASEAMOUNT] <> temp.[AMOUNT])
  or (
    [FINANCIALTRANSACTIONLINEITEM].[BASEAMOUNT] is null
    and temp.[AMOUNT] is not null
    )
  or (
    [FINANCIALTRANSACTIONLINEITEM].[BASEAMOUNT] 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 ([FINANCIALTRANSACTION].[BASEEXCHANGERATEID] <> temp.[BASEEXCHANGERATEID])
  or (
    [FINANCIALTRANSACTION].[BASEEXCHANGERATEID] is null
    and temp.[BASEEXCHANGERATEID] is not null
    )
  or (
    [FINANCIALTRANSACTION].[BASEEXCHANGERATEID] is not null
    and temp.[BASEEXCHANGERATEID] 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].[ORGAMOUNT] <> temp.[ORGANIZATIONAMOUNT])
  or (
    [FINANCIALTRANSACTIONLINEITEM].[ORGAMOUNT] is null
    and temp.[ORGANIZATIONAMOUNT] is not null
    )
  or (
    [FINANCIALTRANSACTIONLINEITEM].[ORGAMOUNT] is not null
    and temp.[ORGANIZATIONAMOUNT] is null
    )
  or ([FINANCIALTRANSACTION].[ORGEXCHANGERATEID] <> temp.[ORGANIZATIONEXCHANGERATEID])
  or (
    [FINANCIALTRANSACTION].[ORGEXCHANGERATEID] is null
    and temp.[ORGANIZATIONEXCHANGERATEID] is not null
    )
  or (
    [FINANCIALTRANSACTION].[ORGEXCHANGERATEID] is not null
    and temp.[ORGANIZATIONEXCHANGERATEID] is null
    )
  or ([FINANCIALTRANSACTIONLINEITEM].[TRANSACTIONAMOUNT] <> temp.[TRANSACTIONAMOUNT])
  or (
    [FINANCIALTRANSACTIONLINEITEM].[TRANSACTIONAMOUNT] is null
    and temp.[TRANSACTIONAMOUNT] is not null
    )
  or (
    [FINANCIALTRANSACTIONLINEITEM].[TRANSACTIONAMOUNT] is not null
    and temp.[TRANSACTIONAMOUNT] is null
    )
  or ([FINANCIALTRANSACTION].[TRANSACTIONCURRENCYID] <> temp.[TRANSACTIONCURRENCYID])
  or (
    [FINANCIALTRANSACTION].[TRANSACTIONCURRENCYID] is null
    and temp.[TRANSACTIONCURRENCYID] is not null
    )
  or (
    [FINANCIALTRANSACTION].[TRANSACTIONCURRENCYID] is not null
    and temp.[TRANSACTIONCURRENCYID] 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 the revenue category table as well

update [REVENUECATEGORY]
set [REVENUECATEGORY].[GLREVENUECATEGORYMAPPINGID] = temp.[CATEGORYCODEID]
  ,[REVENUECATEGORY].CHANGEDBYID = @CHANGEAGENTID
  ,[REVENUECATEGORY].DATECHANGED = @CHANGEDATE
from dbo.[REVENUECATEGORY]
inner join @TempTbl as [temp] on [REVENUECATEGORY].ID = [temp].ID
where ([REVENUECATEGORY].[GLREVENUECATEGORYMAPPINGID] <> temp.[CATEGORYCODEID])

if @@Error <> 0
  return 3;

-- insert new items

insert into dbo.FINANCIALTRANSACTIONLINEITEM (
  ID
  ,FINANCIALTRANSACTIONID
  ,BASEAMOUNT
  ,ORGAMOUNT
  ,TRANSACTIONAMOUNT
  ,POSTSTATUSCODE
  ,POSTDATE
  ,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
  ,ADDEDBYID
  ,CHANGEDBYID
  ,DATEADDED
  ,DATECHANGED
  )
select ID
  ,@REVENUEID
  ,AMOUNT
  ,case when ORGANIZATIONEXCHANGERATEID is null
      and (
        BASECURRENCYID is null
        or BASECURRENCYID = @ORGANIZATIONCURRENCYID
        )
      and BASEEXCHANGERATEID is null
      and (
        TRANSACTIONCURRENCYID is null
        or TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID
        )
      and (
        isnull(ORGANIZATIONAMOUNT, 0) <> isnull(AMOUNT, 0)
        or isnull(TRANSACTIONAMOUNT, 0) <> isnull(AMOUNT, 0)
        or isnull(AMOUNT, 0) = 0
        ) then isnull(AMOUNT, 0) else coalesce(ORGANIZATIONAMOUNT, AMOUNT, 0) end
  ,case when ORGANIZATIONEXCHANGERATEID is null
      and (
        BASECURRENCYID is null
        or BASECURRENCYID = @ORGANIZATIONCURRENCYID
        )
      and BASEEXCHANGERATEID is null
      and (
        TRANSACTIONCURRENCYID is null
        or TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID
        )
      and (
        isnull(ORGANIZATIONAMOUNT, 0) <> isnull(AMOUNT, 0)
        or isnull(TRANSACTIONAMOUNT, 0) <> isnull(AMOUNT, 0)
        or isnull(AMOUNT, 0) = 0
        ) then isnull(AMOUNT, 0) else coalesce(TRANSACTIONAMOUNT, AMOUNT, 0) end
  ,@POSTSTATUSCODE
  ,@POSTDATE
  ,@FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
  ,@CHANGEAGENTID
  ,@CHANGEAGENTID
  ,@CHANGEDATE
  ,@CHANGEDATE
from @TempTbl as temp
where not exists (
    select 1
    from dbo.FINANCIALTRANSACTIONLINEITEM
    where ID = temp.ID
    )

insert into dbo.REVENUESPLIT_EXT (
  ID
  ,APPLICATIONCODE
  ,DESIGNATIONID
  ,TYPECODE
  ,ADDEDBYID
  ,CHANGEDBYID
  ,DATEADDED
  ,DATECHANGED
  )
select ID
  ,APPLICATIONCODE
  ,DESIGNATIONID
  ,TYPECODE
  ,@CHANGEAGENTID
  ,@CHANGEAGENTID
  ,@CHANGEDATE
  ,@CHANGEDATE
from @TempTbl as temp
where not exists (
    select 1
    from dbo.REVENUESPLIT_EXT
    where ID = temp.ID
    )

-- insert new revenue category items

insert into [REVENUECATEGORY] (
  [ID]
  ,[GLREVENUECATEGORYMAPPINGID]
  ,ADDEDBYID
  ,CHANGEDBYID
  ,DATEADDED
  ,DATECHANGED
  )
select [ID]
  ,[CATEGORYCODEID]
  ,@CHANGEAGENTID
  ,@CHANGEAGENTID
  ,@CHANGEDATE
  ,@CHANGEDATE
from @TempTbl as [temp]
where (
    not exists (
      select ID
      from dbo.[REVENUECATEGORY] as data
      where data.ID = [temp].ID
      )
    )
  and (not [temp].CATEGORYCODEID is null)

if @@Error <> 0
  return 4;

return 0;