USP_GLTRANSACTION_ADDWRITEOFFREVERSALS

CREATE procedure dbo.USP_GLTRANSACTION_ADDWRITEOFFREVERSALS (
  @WRITEOFFID uniqueidentifier
  ,@CHANGEAGENTID uniqueidentifier = null
  ,@CHANGEDATE datetime = null
  ,@POSTDATE datetime = null
  )
as
set nocount on;

if @CHANGEAGENTID is null
  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

if @CHANGEDATE is null
  set @CHANGEDATE = getdate();

declare @JOURNAL nvarchar(50);

set @JOURNAL = 'Blackbaud Enterprise';

--Get the postdate for REVERSAL from the last adjustment posted

if @POSTDATE is null
  select top 1 @POSTDATE = POSTDATE
  from dbo.WRITEOFFADJUSTMENT
  where WRITEOFFID = @WRITEOFFID
    and POSTSTATUSCODE = 0
  order by DATEADDED desc

--If there are no posted adjustments then get the postdate for REVERSAL from the REVENUE record

if @POSTDATE is null
  select @POSTDATE = POSTDATE
  from dbo.WRITEOFF
  where ID = @WRITEOFFID;

declare @REVERSALS table (
  ORIGINALTRANSACTIONID uniqueidentifier
  ,REVERSALTRANSACTIONID uniqueidentifier
  ,ACCOUNT nvarchar(100)
  ,AMOUNT money
  ,PROJECT nvarchar(100)
  ,REFERENCE nvarchar(255)
  ,TRANSACTIONTYPECODE tinyint
  ,BASECURRENCYID uniqueidentifier
  ,TRANSACTIONAMOUNT money
  ,TRANSACTIONCURRENCYID uniqueidentifier
  ,BASEEXCHANGERATEID uniqueidentifier
  ,ORGANIZATIONAMOUNT money
  ,ORGANIZATIONEXCHANGERATEID uniqueidentifier
  ,GLACCOUNTID uniqueidentifier
  ,FTLIID uniqueidentifier
  ,COPYFTLIID uniqueidentifier
  ,REVERSEDFTLIID uniqueidentifier
  ,SEQUENCE int
  ,DELETED tinyint
  );

insert into @REVERSALS (
  ORIGINALTRANSACTIONID
  ,REVERSALTRANSACTIONID
  ,ACCOUNT
  ,TRANSACTIONAMOUNT
  ,AMOUNT
  ,ORGANIZATIONAMOUNT
  ,PROJECT
  ,REFERENCE
  ,TRANSACTIONTYPECODE
  ,BASECURRENCYID
  ,TRANSACTIONCURRENCYID
  ,BASEEXCHANGERATEID
  ,ORGANIZATIONEXCHANGERATEID
  ,GLACCOUNTID
  ,SEQUENCE
  ,DELETED
  )
select JOURNALENTRY.ID
  ,newid()
  ,JOURNALENTRY_EXT.ACCOUNT
  ,JOURNALENTRY.TRANSACTIONAMOUNT
  ,JOURNALENTRY.BASEAMOUNT
  ,JOURNALENTRY.ORGAMOUNT
  ,JOURNALENTRY_EXT.PROJECT
  ,JOURNALENTRY.COMMENT
  ,JOURNALENTRY.TRANSACTIONTYPECODE
  ,V.BASECURRENCYID
  ,JOURNALENTRY.TRANSACTIONCURRENCYID
  ,JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID
  ,JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID
  ,JOURNALENTRY.GLACCOUNTID
  ,row_number() over (
    order by JOURNALENTRY.DATEADDED
    )
  ,case 
    when FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
      then 0
    else 1
    end
from dbo.JOURNALENTRY
inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
  and JOURNALENTRY_EXT.TABLENAMECODE = 12
where FINANCIALTRANSACTION.ID = @WRITEOFFID
  and OUTDATED = 0;

declare @WRITEOFFADJUSTMENTID uniqueidentifier
declare @WRITEOFFADJUSTMENTPOSTSTATUSCODE tinyint

select top 1 @WRITEOFFADJUSTMENTPOSTSTATUSCODE = A.POSTSTATUSCODE
  ,@WRITEOFFADJUSTMENTID = LIA.ID
from dbo.WRITEOFFADJUSTMENT A
inner join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT LIA on LIA.ID = A.ID
where WRITEOFFID = @WRITEOFFID
order by A.DATEADDED desc;

if @WRITEOFFADJUSTMENTPOSTSTATUSCODE <> 2
  set @WRITEOFFADJUSTMENTPOSTSTATUSCODE = 1

declare @REVERSEDFTLIID as table (
  NEWFTLIID uniqueidentifier
  ,REVERSEDFTLIID uniqueidentifier
  ,COPYFTLIID uniqueidentifier
  );
declare @FTID uniqueidentifier;
declare @ISADJUSTMENT bit = 0;
declare @DELETED bit = 0;

if exists (
    select 1
    from @REVERSALS
    where DELETED = 1
    )
  set @DELETED = 1

insert into @REVERSEDFTLIID (
  NEWFTLIID
  ,REVERSEDFTLIID
  ,COPYFTLIID
  )
select NEWID()
  ,T2.FINANCIALTRANSACTIONLINEITEMID
  ,NEWID()
from @REVERSALS T1
inner join dbo.JOURNALENTRY T2 on T1.ORIGINALTRANSACTIONID = T2.ID
group by T2.FINANCIALTRANSACTIONLINEITEMID

update @REVERSALS
set FTLIID = t3.NEWFTLIID
  ,COPYFTLIID = T3.COPYFTLIID
  ,REVERSEDFTLIID = T3.REVERSEDFTLIID
from @REVERSALS t1
inner join dbo.JOURNALENTRY t2 on t1.ORIGINALTRANSACTIONID = t2.ID
inner join @REVERSEDFTLIID t3 on t2.FINANCIALTRANSACTIONLINEITEMID = t3.REVERSEDFTLIID

select top 1 @FTID = FTLI.FINANCIALTRANSACTIONID
from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
inner join @REVERSEDFTLIID T1 on FTLI.ID = T1.REVERSEDFTLIID

if exists (
    select 1
    from dbo.FINANCIALTRANSACTION FT
    inner join dbo.WRITEOFFADJUSTMENT T1 on T1.WRITEOFFID = FT.ID
      and T1.POSTSTATUSCODE != 0
    where FT.ID = @FTID
    )
  set @ISADJUSTMENT = 1

if @ISADJUSTMENT = 1
  and @DELETED = 0
begin
  insert into dbo.FINANCIALTRANSACTIONLINEITEM (
    ID
    ,REVERSEDLINEITEMID
    ,FINANCIALTRANSACTIONID
    ,TRANSACTIONAMOUNT
    ,BASEAMOUNT
    ,ORGAMOUNT
    ,VISIBLE
    ,DESCRIPTION
    ,SEQUENCE
    ,TYPECODE
    ,POSTDATE
    ,POSTSTATUSCODE
    ,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
    ,BATCHID
    ,ADDEDBYID
    ,CHANGEDBYID
    ,DATEADDED
    ,DATECHANGED
    ,SOURCELINEITEMID
    )
  select distinct T1.COPYFTLIID
    ,T1.REVERSEDFTLIID
    ,T3.FINANCIALTRANSACTIONID
    ,T3.TRANSACTIONAMOUNT
    ,T3.BASEAMOUNT
    ,T3.ORGAMOUNT
    ,1
    ,T3.DESCRIPTION
    ,T3.SEQUENCE
    ,T3.TYPECODE
    ,T3.POSTDATE
    ,2
    ,T3.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
    ,T3.BATCHID
    ,@CHANGEAGENTID
    ,@CHANGEAGENTID
    ,@CHANGEDATE
    ,@CHANGEDATE
    ,T3.SOURCELINEITEMID
  from @REVERSALS T1
  inner join dbo.JOURNALENTRY T2 on T1.ORIGINALTRANSACTIONID = T2.ID
  inner join dbo.FINANCIALTRANSACTIONLINEITEM T3 on T2.FINANCIALTRANSACTIONLINEITEMID = T3.ID

  insert into dbo.REVENUESPLIT_EXT (
    ID
    ,DESIGNATIONID
    ,APPLICATIONCODE
    ,TYPECODE
    ,ADDEDBYID
    ,CHANGEDBYID
    ,DATEADDED
    ,DATECHANGED
    )
  select distinct T2.COPYFTLIID
    ,T1.DESIGNATIONID
    ,T1.APPLICATIONCODE
    ,T1.TYPECODE
    ,T1.ADDEDBYID
    ,T1.CHANGEDBYID
    ,T1.DATEADDED
    ,T1.DATECHANGED
  from dbo.REVENUESPLIT_EXT T1
  inner join @REVERSALS T2 on T1.ID = T2.REVERSEDFTLIID
end

insert into dbo.FINANCIALTRANSACTIONLINEITEM (
  ID
  ,REVERSEDLINEITEMID
  ,FINANCIALTRANSACTIONID
  ,TRANSACTIONAMOUNT
  ,BASEAMOUNT
  ,ORGAMOUNT
  ,VISIBLE
  ,DESCRIPTION
  ,SEQUENCE
  ,TYPECODE
  ,POSTDATE
  ,POSTSTATUSCODE
  ,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
  ,ADDEDBYID
  ,CHANGEDBYID
  ,DATEADDED
  ,DATECHANGED
  )
select distinct T1.FTLIID
  ,T1.REVERSEDFTLIID
  ,T3.FINANCIALTRANSACTIONID
  ,T3.TRANSACTIONAMOUNT
  ,T3.BASEAMOUNT
  ,T3.ORGAMOUNT
  ,1
  ,T3.DESCRIPTION
  ,T3.SEQUENCE
  ,1
  ,@POSTDATE
  ,1
  ,@WRITEOFFADJUSTMENTID
  ,T3.ADDEDBYID
  ,T3.CHANGEDBYID
  ,@CHANGEDATE
  ,@CHANGEDATE
from @REVERSALS T1
inner join dbo.JOURNALENTRY T2 on T1.ORIGINALTRANSACTIONID = T2.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM T3 on T2.FINANCIALTRANSACTIONLINEITEMID = T3.ID

if @ISADJUSTMENT = 1
  and @DELETED = 0
  update FTLI
  set POSTSTATUSCODE = 1
    ,POSTDATE = @POSTDATE
    ,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @WRITEOFFADJUSTMENTID
    ,BATCHID = null
  from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
  inner join @REVERSEDFTLIID R on FTLI.ID = R.REVERSEDFTLIID
  inner join @REVERSALS INS on R.REVERSEDFTLIID = INS.REVERSEDFTLIID

if @ISADJUSTMENT = 1
  and @DELETED = 1
  update FTLI
  set FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @WRITEOFFADJUSTMENTID
    ,BATCHID = null
  from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
  inner join @REVERSEDFTLIID R on FTLI.ID = R.REVERSEDFTLIID

if @DELETED = 0
  update FTLI
  set DELETEDON = getdate()
  from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
  inner join @REVERSEDFTLIID R on FTLI.ID = R.COPYFTLIID

if @ISADJUSTMENT = 1
  and @DELETED = 0
  update JOURNALENTRY
  set FINANCIALTRANSACTIONLINEITEMID = T1.COPYFTLIID
  from @REVERSALS T1
  where JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = T1.REVERSEDFTLIID

insert into dbo.JOURNALENTRY (
  ID
  ,FINANCIALTRANSACTIONLINEITEMID
  ,TRANSACTIONTYPECODE
  ,SUBLEDGERTYPECODE
  ,TRANSACTIONAMOUNT
  ,BASEAMOUNT
  ,ORGAMOUNT
  ,COMMENT
  ,POSTDATE
  ,GLACCOUNTID
  ,SEQUENCE
  ,TYPECODE
  ,TRANSACTIONCURRENCYID
  ,ADDEDBYID
  ,CHANGEDBYID
  ,DATEADDED
  ,DATECHANGED
  )
select source.REVERSALTRANSACTIONID
  ,source.FTLIID
  ,case source.TRANSACTIONTYPECODE
    when 0
      then 1
    else 0
    end --check this

  ,case source.TRANSACTIONTYPECODE
    when 0
      then 1
    else 0
    end
  ,source.TRANSACTIONAMOUNT
  ,source.AMOUNT
  ,source.ORGANIZATIONAMOUNT
  ,source.REFERENCE
  ,@POSTDATE
  ,source.GLACCOUNTID
  ,source.SEQUENCE
  ,case 
    when source.TRANSACTIONCURRENCYID is null
      then 1
    else 0
    end
  ,source.TRANSACTIONCURRENCYID
  ,@CHANGEAGENTID
  ,@CHANGEAGENTID
  ,getdate()
  ,getdate()
from @REVERSALS as source

insert into dbo.JOURNALENTRY_EXT (
  ID
  ,PROJECT
  ,ACCOUNT
  ,JOURNAL
  ,REVERSEDGLTRANSACTIONID
  ,TABLENAMECODE
  ,PRECALCORGANIZATIONEXCHANGERATEID
  ,PRECALCBASEEXCHANGERATEID
  ,ADDEDBYID
  ,CHANGEDBYID
  ,DATEADDED
  ,DATECHANGED
  ,OUTDATED
  )
select source.REVERSALTRANSACTIONID
  ,source.PROJECT
  ,source.ACCOUNT
  ,@JOURNAL
  ,source.ORIGINALTRANSACTIONID
  ,0 -- gltransaction table

  ,source.ORGANIZATIONEXCHANGERATEID
  ,source.BASEEXCHANGERATEID
  ,@CHANGEAGENTID
  ,@CHANGEAGENTID
  ,getdate()
  ,getdate()
  ,1
from @REVERSALS as source

--Set the reversed write-off GL distributions as out of date

update dbo.JOURNALENTRY_EXT
set REVERSEDATE = @CHANGEDATE
  ,OUTDATED = 1
  ,CHANGEDBYID = @CHANGEAGENTID
  ,DATECHANGED = @CHANGEDATE
from @REVERSALS as REV
inner join dbo.JOURNALENTRY_EXT on REV.ORIGINALTRANSACTIONID = JOURNALENTRY_EXT.ID