USP_GLTRANSACTION_ADDREVENUEREVERSALS

CREATE procedure dbo.USP_GLTRANSACTION_ADDREVENUEREVERSALS (
  @REVENUEID 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';
declare @BASECURRENCYID uniqueidentifier;
declare @ISADJUSTMENT bit = 0;

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

if @POSTDATE is null
  select top 1 @POSTDATE = POSTDATE
  from dbo.ADJUSTMENT
  where REVENUEID = @REVENUEID
    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 = FT.POSTDATE 
        ,@BASECURRENCYID = CS.BASECURRENCYID
    from dbo.FINANCIALTRANSACTION FT
    inner join dbo.PDACCOUNTSYSTEM PD on FT.PDACCOUNTSYSTEMID = PD.ID
    inner join dbo.CURRENCYSET CS on CS.ID = PD.CURRENCYSETID
    where FT.ID = @REVENUEID;

if @BASECURRENCYID is null
    select @BASECURRENCYID = CS.BASECURRENCYID
    from dbo.FINANCIALTRANSACTION FT
    inner join dbo.PDACCOUNTSYSTEM PD on FT.PDACCOUNTSYSTEMID = PD.ID
    inner join dbo.CURRENCYSET CS on CS.ID = PD.CURRENCYSETID
    where FT.ID = @REVENUEID;

set @POSTDATE = convert(datetime, convert(date, @POSTDATE))

if object_id('tempdb..#TMP_REVENUEREVERSALS') is not null
    drop table #TMP_REVENUEREVERSALS;

create table #TMP_REVENUEREVERSALS(
  ORIGINALTRANSACTIONID uniqueidentifier
  ,REVERSALTRANSACTIONID uniqueidentifier
  ,ACCOUNT nvarchar(100) collate database_default
  ,AMOUNT money
  ,PROJECT nvarchar(100) collate database_default
  ,REFERENCE nvarchar(255) collate database_default
  ,TRANSACTIONTYPECODE tinyint
  ,BASECURRENCYID uniqueidentifier
  ,TRANSACTIONAMOUNT money
  ,TRANSACTIONCURRENCYID uniqueidentifier
  ,BASEEXCHANGERATEID uniqueidentifier
  ,ORGANIZATIONAMOUNT money
  ,ORGANIZATIONEXCHANGERATEID uniqueidentifier
  ,GLACCOUNTID uniqueidentifier
  ,FTLIID uniqueidentifier
  ,COPYFTLIID uniqueidentifier
  ,REVERSEDFTLIID uniqueidentifier
  ,TYPECODE tinyint
  );

insert into #TMP_REVENUEREVERSALS (
  ORIGINALTRANSACTIONID
  ,REVERSALTRANSACTIONID
  ,ACCOUNT
  ,AMOUNT
  ,PROJECT
  ,REFERENCE
  ,TRANSACTIONTYPECODE
  ,BASECURRENCYID
  ,TRANSACTIONAMOUNT
  ,TRANSACTIONCURRENCYID
  ,BASEEXCHANGERATEID
  ,ORGANIZATIONAMOUNT
  ,ORGANIZATIONEXCHANGERATEID
  ,GLACCOUNTID
  ,TYPECODE
  ,REVERSEDFTLIID
  )
select JOURNALENTRY.ID
  ,newid()
  ,JOURNALENTRY_EXT.ACCOUNT
  ,JOURNALENTRY.BASEAMOUNT
  ,JOURNALENTRY_EXT.PROJECT
  ,JOURNALENTRY.COMMENT
  ,JOURNALENTRY.TRANSACTIONTYPECODE
  ,@BASECURRENCYID
  ,JOURNALENTRY.TRANSACTIONAMOUNT
  ,JOURNALENTRY.TRANSACTIONCURRENCYID
  ,JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID
  ,JOURNALENTRY.ORGAMOUNT
  ,JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID
  ,JOURNALENTRY.GLACCOUNTID
  ,JOURNALENTRY.TYPECODE
  ,JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.JOURNALENTRY on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID 
inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
    and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
    and JOURNALENTRY_EXT.TABLENAMECODE = 1;

declare @ADJUSTMENTID uniqueidentifier
declare @ADJUSTMENTPOSTSTATUSCODE tinyint

select top 1 @ADJUSTMENTPOSTSTATUSCODE = A.POSTSTATUSCODE
  ,@ADJUSTMENTID = LIA.ID
from dbo.ADJUSTMENT A
inner join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT LIA on LIA.ID = A.ID
where REVENUEID = @REVENUEID
order by A.DATEADDED desc;

if @ADJUSTMENTPOSTSTATUSCODE <> 2
  set @ADJUSTMENTPOSTSTATUSCODE = 1

if object_id('tempdb..#TMP_REVERSEDFTLIID') is not null
    drop table #TMP_REVERSEDFTLIID;

create table #TMP_REVERSEDFTLIID (
  NEWFTLIID uniqueidentifier
  ,REVERSEDFTLIID uniqueidentifier
  ,COPYFTLIID uniqueidentifier
  );

insert into #TMP_REVERSEDFTLIID (
  NEWFTLIID
  ,REVERSEDFTLIID
  ,COPYFTLIID
  )
select NEWID()
  ,T1.REVERSEDFTLIID
  ,NEWID()
from  #TMP_REVENUEREVERSALS T1
group by T1.REVERSEDFTLIID 

if exists(select top 1 1 from dbo.ADJUSTMENT where REVENUEID = @REVENUEID and POSTSTATUSCODE != 0)
    set @ISADJUSTMENT = 1;

if @ISADJUSTMENT = 1
begin
  insert into dbo.FINANCIALTRANSACTIONLINEITEM (
    ID
    ,REVERSEDLINEITEMID
    ,FINANCIALTRANSACTIONID
    ,TRANSACTIONAMOUNT
    ,BASEAMOUNT
    ,ORGAMOUNT
    ,VISIBLE
    ,DESCRIPTION
    ,SEQUENCE
    ,TYPECODE
    ,POSTDATE
    ,POSTSTATUSCODE
    ,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
    ,BATCHID
    ,ADDEDBYID
    ,CHANGEDBYID
    ,DATEADDED
    ,DATECHANGED
    ,SOURCELINEITEMID
    ,DELETEDON
    )
  select 
    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
    ,GETDATE()
  from #TMP_REVERSEDFTLIID T1
  inner join dbo.FINANCIALTRANSACTIONLINEITEM T3 on T1.REVERSEDFTLIID = T3.ID

  insert into dbo.REVENUESPLIT_EXT (
    ID
    ,DESIGNATIONID
    ,APPLICATIONCODE
    ,TYPECODE
    ,ADDEDBYID
    ,CHANGEDBYID
    ,DATEADDED
    ,DATECHANGED
    )
  select T2.COPYFTLIID
    ,T1.DESIGNATIONID
    ,T1.APPLICATIONCODE
    ,T1.TYPECODE
    ,@CHANGEAGENTID
    ,@CHANGEAGENTID
    ,@CHANGEDATE
    ,@CHANGEDATE
  from #TMP_REVERSEDFTLIID T2
  inner join dbo.REVENUESPLIT_EXT T1 on T1.ID = T2.REVERSEDFTLIID

  update FTLI1 set 
      SOURCELINEITEMID = t1.COPYFTLIID
      ,CHANGEDBYID = @CHANGEAGENTID
      ,DATECHANGED = @CHANGEDATE
    from dbo.FINANCIALTRANSACTIONLINEITEM FTLI1
    inner join #TMP_REVERSEDFTLIID t1 on FTLI1.SOURCELINEITEMID = t1.REVERSEDFTLIID
    inner join FINANCIALTRANSACTIONLINEITEM FTLI2 on FTLI1.SOURCELINEITEMID = FTLI2.ID
    inner join FINANCIALTRANSACTION on FTLI2.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID and FINANCIALTRANSACTION.TYPECODE <> 1

end

insert into dbo.FINANCIALTRANSACTIONLINEITEM (
  ID
  ,REVERSEDLINEITEMID
  ,FINANCIALTRANSACTIONID
  ,TRANSACTIONAMOUNT
  ,BASEAMOUNT
  ,ORGAMOUNT
  ,VISIBLE
  ,DESCRIPTION
  ,SEQUENCE
  ,TYPECODE
  ,POSTDATE
  ,POSTSTATUSCODE
  ,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
  ,ADDEDBYID
  ,CHANGEDBYID
  ,DATEADDED
  ,DATECHANGED
  )
select T1.NEWFTLIID
  ,T1.REVERSEDFTLIID
  ,T3.FINANCIALTRANSACTIONID
  ,T3.TRANSACTIONAMOUNT
  ,T3.BASEAMOUNT
  ,T3.ORGAMOUNT
  ,1
  ,T3.DESCRIPTION
  ,T3.SEQUENCE
  ,1
  ,@POSTDATE
  ,1
  ,@ADJUSTMENTID
  ,T3.ADDEDBYID
  ,T3.CHANGEDBYID
  ,@CHANGEDATE
  ,@CHANGEDATE
from #TMP_REVERSEDFTLIID T1
inner join dbo.FINANCIALTRANSACTIONLINEITEM T3 on T1.REVERSEDFTLIID = T3.ID    

if @ISADJUSTMENT = 1
  update FTLI set 
    POSTSTATUSCODE = case @ADJUSTMENTPOSTSTATUSCODE when 1 then 1 else 3 end
    ,POSTDATE = @POSTDATE
    ,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID
    ,SOURCELINEITEMID = null
    ,BATCHID = null
  from dbo.FINANCIALTRANSACTIONLINEITEM FTLI 
  inner join #TMP_REVERSEDFTLIID R on FTLI.ID = R.REVERSEDFTLIID

if @ISADJUSTMENT = 1
  update JOURNALENTRY set 
    FINANCIALTRANSACTIONLINEITEMID = T1.COPYFTLIID
    ,CHANGEDBYID = @CHANGEAGENTID
    ,DATECHANGED = @CHANGEDATE
  from #TMP_REVERSEDFTLIID 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
  ,IDS.NEWFTLIID
  ,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
  ,1
  ,source.TYPECODE
  ,source.TRANSACTIONCURRENCYID
  ,@CHANGEAGENTID
  ,@CHANGEAGENTID
  ,getdate()
  ,getdate()
from #TMP_REVENUEREVERSALS as source
inner join #TMP_REVERSEDFTLIID IDS on source.REVERSEDFTLIID = IDS.REVERSEDFTLIID

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 #TMP_REVENUEREVERSALS as source

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

drop table #TMP_REVENUEREVERSALS;
drop table #TMP_REVERSEDFTLIID;