USP_WRITEOFF_FIXSPLITS2

CREATE procedure dbo.USP_WRITEOFF_FIXSPLITS2 (
  @WRITEOFFID uniqueidentifier
  ,@PLEDGEID uniqueidentifier
  ,@CHANGEAGENTID uniqueidentifier
  ,@CHANGEDATE datetime
  ,@POSTDATE datetime
  )
as
set nocount on;

declare @contextCache varbinary(128);
declare @AdjustmentID uniqueidentifier

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

set @contextCache = CONTEXT_INFO();

if not @CHANGEAGENTID is null
  set CONTEXT_INFO @CHANGEAGENTID;

select @AdjustmentID = ID
from dbo.WRITEOFFADJUSTMENT
where WRITEOFFID = @WRITEOFFID
  and POSTSTATUSCODE = 1

set @AdjustmentID = isnull(@AdjustmentID, newid())

if not exists (
    select 1
    from dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT
    where ID = @AdjustmentID
    )
  insert into dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT (
    ID
    ,[DATE]
    ,ADDEDBYID
    ,CHANGEDBYID
    ,DATEADDED
    ,DATECHANGED
    )
  values (
    @AdjustmentID
    ,convert([DATE], @CHANGEDATE)
    ,@CHANGEAGENTID
    ,@CHANGEAGENTID
    ,@CHANGEDATE
    ,@CHANGEDATE
    )

-- in the case of adjusted unposted lineitems remove REVERSEDLINEITEMID reference before the delete 

update T1
set REVERSEDLINEITEMID = null
from dbo.FINANCIALTRANSACTIONLINEITEM T1
inner join dbo.FINANCIALTRANSACTIONLINEITEM T2 on T1.REVERSEDLINEITEMID = T2.ID
where T2.FINANCIALTRANSACTIONID = @WRITEOFFID
  and T2.POSTSTATUSCODE != 2

delete
from dbo.WRITEOFFSPLIT
where WRITEOFFID = @WRITEOFFID;

if not @contextCache is null
  set CONTEXT_INFO @contextCache;

declare @TRANSACTIONTYPECODE tinyint = 0;

select @TRANSACTIONTYPECODE = FINANCIALTRANSACTION.TYPECODE
from dbo.FINANCIALTRANSACTION
where FINANCIALTRANSACTION.ID = @PLEDGEID
  and DELETEDON is null;

declare @WriteOffLineItems table (
  ID uniqueidentifier
  ,DESIGNATIONID uniqueidentifier
  ,APPLICATIONCODE tinyint default 0
  ,TYPECODE tinyint default 0
  ,TRANSACTIONAMOUNT money
  ,BASEAMOUNT money
  ,ORGAMOUNT money
  ,SEQUENCE int
  ,POSTDATE date
  ,POSTSTATUSCODE tinyint
  ,SOURCELINEITEMID uniqueidentifier
  )

if @TRANSACTIONTYPECODE = 7
  insert into @WriteOffLineItems (
    ID
    ,DESIGNATIONID
    ,TRANSACTIONAMOUNT
    ,BASEAMOUNT
    ,ORGAMOUNT
    ,SEQUENCE
    ,POSTDATE
    ,POSTSTATUSCODE
    ,SOURCELINEITEMID
    )
  select top 1 newid()
    ,REVENUESPLIT_EXT.DESIGNATIONID
    ,FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
    ,FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT
    ,FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT
    ,(
      select max(SEQUENCE)
      from dbo.FINANCIALTRANSACTIONLINEITEM
      where FINANCIALTRANSACTIONID = @WRITEOFFID
      ) + 1
    ,FINANCIALTRANSACTION.POSTDATE
    ,case (
        select POSTSTATUSCODE
        from dbo.FINANCIALTRANSACTION
        where ID = @WRITEOFFID
        )
      when 3
        then 3
      else 1
      end
    ,FINANCIALTRANSACTIONLINEITEM.ID
  from FINANCIALTRANSACTIONLINEITEM
  inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
  inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
  where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @PLEDGEID
    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
    and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0
else
  insert into @WriteOffLineItems (
    ID
    ,DESIGNATIONID
    ,APPLICATIONCODE
    ,TYPECODE
    ,TRANSACTIONAMOUNT
    ,BASEAMOUNT
    ,ORGAMOUNT
    ,SEQUENCE
    ,POSTDATE
    ,POSTSTATUSCODE
    ,SOURCELINEITEMID
    )
  select newid()
    ,WOSPLIT.DESIGNATIONID
    ,REVENUESPLIT_EXT.APPLICATIONCODE
    ,REVENUESPLIT_EXT.TYPECODE
    ,WOSPLIT.TRANSACTIONAMOUNT
    ,WOSPLIT.AMOUNT
    ,WOSPLIT.ORGANIZATIONAMOUNT
    ,(
      select max(SEQUENCE)
      from dbo.FINANCIALTRANSACTIONLINEITEM
      where FINANCIALTRANSACTIONID = @WRITEOFFID
      ) + row_number() over (
      order by WOSPLIT.AMOUNT
      )
    ,
    case WO.POSTSTATUSCODE
      when 3
        then null
      else isnull(@POSTDATE, WO.POSTDATE)  -- @POSTDATE is adj postdate; there may be no adjustment involved  (i.e not posted)

      end
    ,case WO.POSTSTATUSCODE
      when 3
        then 3
      else 1
      end
    ,WOSPLIT.REVENUESPLITID
  from (
    select 
      ISPLIT.DESIGNATIONID
      ,sum(IWOSPLIT.TRANSACTIONAMOUNT) TRANSACTIONAMOUNT
      ,sum(IWOSPLIT.AMOUNT) AMOUNT
      ,sum(IWOSPLIT.ORGANIZATIONAMOUNT) ORGANIZATIONAMOUNT
      ,ISPLIT.REVENUESPLITID
      ,IWOSPLIT.WRITEOFFID
    from dbo.INSTALLMENTSPLITWRITEOFF IWOSPLIT
    inner join dbo.INSTALLMENTSPLIT ISPLIT on IWOSPLIT.INSTALLMENTSPLITID = ISPLIT.ID
    where IWOSPLIT.WRITEOFFID = @WRITEOFFID
      and ISPLIT.PLEDGEID = @PLEDGEID
    group by ISPLIT.DESIGNATIONID
      ,ISPLIT.REVENUESPLITID
      ,IWOSPLIT.WRITEOFFID
    ) WOSPLIT
    inner join dbo.REVENUESPLIT_EXT on WOSPLIT.REVENUESPLITID = REVENUESPLIT_EXT.ID
    inner join dbo.FINANCIALTRANSACTION WO on WOSPLIT.WRITEOFFID = WO.ID

insert into dbo.FINANCIALTRANSACTIONLINEITEM (
  ID
  ,FINANCIALTRANSACTIONID
  ,TRANSACTIONAMOUNT
  ,BASEAMOUNT
  ,ORGAMOUNT
  ,ADDEDBYID
  ,CHANGEDBYID
  ,DATEADDED
  ,DATECHANGED
  ,SEQUENCE
  ,POSTDATE
  ,POSTSTATUSCODE
  ,DESCRIPTION
  ,SOURCELINEITEMID
  ,FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
  )
select ID
  ,@WRITEOFFID
  ,TRANSACTIONAMOUNT
  ,BASEAMOUNT
  ,ORGAMOUNT
  ,@CHANGEAGENTID
  ,@CHANGEAGENTID
  ,@CHANGEDATE
  ,@CHANGEDATE
  ,isnull(SEQUENCE, 1)
  ,POSTDATE
  ,POSTSTATUSCODE
  ,''
  ,SOURCELINEITEMID
  ,@AdjustmentID
from @WriteOffLineItems

insert into dbo.REVENUESPLIT_EXT (
  ID
  ,DESIGNATIONID
  ,APPLICATIONCODE
  ,TYPECODE
  ,ADDEDBYID
  ,CHANGEDBYID
  ,DATEADDED
  ,DATECHANGED
  )
select ID
  ,DESIGNATIONID
  ,APPLICATIONCODE
  ,TYPECODE
  ,@CHANGEAGENTID
  ,@CHANGEAGENTID
  ,@CHANGEDATE
  ,@CHANGEDATE
from @WriteOffLineItems

return 0;