USP_FINANCIALTRANSACTIONLINEITEMS_REVERSE

CREATE procedure dbo.USP_FINANCIALTRANSACTIONLINEITEMS_REVERSE
      (
        @CHANGEAGENTID uniqueidentifier
        ,@LINEITEMIDS UDT_GENERICID readonly  
        ,@REVERSALPOSTDATE datetime
        ,@REVERSALDATE datetime
        ,@DELETEUNPOSTED bit
      )
as
begin
  set nocount on;

    /*
     *
     * NOTE: Rules about transaction types, line item types, and subledger specific details should be handled in a 
     *          line item or subledger reversal sproc that calls this sproc.
     *
     * Some subledgers will use the ability of reversing unposted items for tracking purposes. We support that.
     *
     */

    begin try
        -- Cannot reverse a deleted line item

        if (exists (select FINANCIALTRANSACTIONLINEITEM.ID
                    from dbo.FINANCIALTRANSACTIONLINEITEM
                        inner join @LINEITEMIDS as LIDS
                            on FINANCIALTRANSACTIONLINEITEM.ID = LIDS.ID
                    where (not FINANCIALTRANSACTIONLINEITEM.DELETEDON is null)))
             raiserror('ERR_LINEITEMREVERSAL_CANNOTREVERSEDELETEDLINEITEM', 13,1);

        -- Cannot reverse a reversal line item

        if (exists (select FINANCIALTRANSACTIONLINEITEM.ID
                    from dbo.FINANCIALTRANSACTIONLINEITEM
                        inner join @LINEITEMIDS as LIDS
                            on FINANCIALTRANSACTIONLINEITEM.ID = LIDS.ID
                    where (FINANCIALTRANSACTIONLINEITEM.TYPECODE = 1)))
             raiserror('ERR_LINEITEMREVERSAL_CANNOTREVERSEREVERSEDLINEITEM', 13,1);

        -- Otherwise create the reversal


        -- Get the change agent

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

        -- Get the current date

      declare @CURRENTDATE datetime;
      set @CURRENTDATE = getdate();

      -- Otherwise delete line items that are not posted instead of reversing

      declare @CONTEXTCACHE varbinary(128) = CONTEXT_INFO();
      set CONTEXT_INFO @CHANGEAGENTID;

      -- We need to change the financial transaction amount before deleting to keep the totals correct

      update dbo.FINANCIALTRANSACTION
          set TRANSACTIONAMOUNT = isnull((select sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT)
                          from dbo.FINANCIALTRANSACTION as FINANCIALTRANSACTION_INNER
                              inner join dbo.FINANCIALTRANSACTIONLINEITEM
                                  on FINANCIALTRANSACTION_INNER.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                              left outer join @LINEITEMIDS as LINEITEMSTOREVERSE
                                  on FINANCIALTRANSACTIONLINEITEM.ID = LINEITEMSTOREVERSE.ID
                          where (FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0) and
                                  ((LINEITEMSTOREVERSE.ID is null) or (FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 2)) and
                                  FINANCIALTRANSACTION_INNER.ID = FINANCIALTRANSACTION.ID),0)
      from dbo.FINANCIALTRANSACTION
      where (FINANCIALTRANSACTION.ID in (select FINANCIALTRANSACTION.ID
                                          from dbo.FINANCIALTRANSACTION
                                              inner join dbo.FINANCIALTRANSACTIONLINEITEM
                                                  on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                                              inner join @LINEITEMIDS as LINEITEMSTOREVERSE
                                                  on FINANCIALTRANSACTIONLINEITEM.ID = LINEITEMSTOREVERSE.ID))
      ;

      if @DELETEUNPOSTED=1 
      begin
        delete from dbo.FINANCIALTRANSACTIONLINEITEM
        from dbo.FINANCIALTRANSACTIONLINEITEM
            inner join @LINEITEMIDS as LINEITEMSTOREVERSE
                on FINANCIALTRANSACTIONLINEITEM.ID = LINEITEMSTOREVERSE.ID
        where FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2
        ;
      end;

      if @CONTEXTCACHE is not null
          set CONTEXT_INFO @CONTEXTCACHE;

      -- Mark the reversed line item as deleted

      update dbo.FINANCIALTRANSACTIONLINEITEM
          set DELETEDON = isnull(@REVERSALDATE,@CURRENTDATE),
              DATECHANGED = @CURRENTDATE,
              CHANGEDBYID = @CHANGEAGENTID
      from dbo.FINANCIALTRANSACTIONLINEITEM
          inner join @LINEITEMIDS as LINEITEMSTOREVERSE
              on FINANCIALTRANSACTIONLINEITEM.ID = LINEITEMSTOREVERSE.ID
      --If the wanted to delete unposted, they are now gone. The ones left must be 2 if they desire to do so.              

      --where (FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 2)

      --;


        declare @REVERSALS table ( ORIGINALID uniqueidentifier, REVERSALID uniqueidentifier);

    -- Create the reversal line items

      insert into dbo.FINANCIALTRANSACTIONLINEITEM (
        ID
        ,FINANCIALTRANSACTIONID
        ,TRANSACTIONAMOUNT
        ,BASEAMOUNT
        ,ORGAMOUNT
        ,VISIBLE
        ,[DESCRIPTION]
        ,SEQUENCE
        ,TYPECODE
        ,DELETEDON
        ,REVERSEDLINEITEMID
        ,POSTDATE
        ,POSTSTATUSCODE
        ,QUANTITY
        ,UNITVALUE
        -- Boilerplate

        ,DATEADDED,DATECHANGED,ADDEDBYID,CHANGEDBYID
      )
      output
        inserted.ID
        ,inserted.REVERSEDLINEITEMID ORIGINALID    
      into @REVERSALS ( 
        REVERSALID
        , ORIGINALID
      )
      select
        NEWID()
        ,FTLI.FINANCIALTRANSACTIONID
        ,FTLI.TRANSACTIONAMOUNT
        ,FTLI.BASEAMOUNT
        ,FTLI.ORGAMOUNT
        ,0 -- Visible

        ,'' -- Description

        ,FTLI.SEQUENCE
        ,1 -- Reversal

        ,NULL -- Deletedon

        ,FTLI.ID
        ,isnull(@REVERSALPOSTDATE, FTLI.POSTDATE) -- PostDate

        ,case when FTLI.POSTSTATUSCODE = 3 then 3 else 1 end -- POSTSTATUSCODE

        ,FTLI.QUANTITY
        ,FTLI.UNITVALUE
        -- Boilerplate

        ,@CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID
      from
        @LINEITEMIDS as LIDS
        inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI 
          on LIDS.ID = FTLI.ID
      ;

      -- Create the reversal distributions here


  declare @JEREVERSALS table ( ORIGINALID uniqueidentifier, REVERSALID uniqueidentifier, REVERSALLINEITEMID uniqueidentifier );

  insert into @JEREVERSALS (
    ORIGINALID
    ,REVERSALID
    ,REVERSALLINEITEMID
  )
  select
    JE.ID
    ,NEWID()
    ,REVS.REVERSALID
  from
    dbo.JOURNALENTRY as JE
    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI
      on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
    inner join @REVERSALS as REVS 
      on REVS.ORIGINALID = FTLI.ID
  ;

  -- Copy and reverse line item distributions

  insert into dbo.JOURNALENTRY (
    ID
    ,FINANCIALTRANSACTIONLINEITEMID
    ,TRANSACTIONTYPECODE
    ,SUBLEDGERTYPECODE
    ,CLASSCODE
    ,SEQUENCE    
    ,TRANSACTIONAMOUNT
    ,BASEAMOUNT
    ,ORGAMOUNT
    ,PERCENTAGE
    ,DATAELEMENT1ID,DATAELEMENT2ID,DATAELEMENT3ID,DATAELEMENT4ID,DATAELEMENT5ID,DATAELEMENT6ID,DATAELEMENT7ID,DATAELEMENT8ID,DATAELEMENT9ID,DATAELEMENT10ID
    ,DATAELEMENT11ID,DATAELEMENT12ID,DATAELEMENT13ID,DATAELEMENT14ID,DATAELEMENT15ID,DATAELEMENT16ID,DATAELEMENT17ID,DATAELEMENT18ID,DATAELEMENT19ID,DATAELEMENT20ID
    ,DATAELEMENT21ID,DATAELEMENT22ID,DATAELEMENT23ID,DATAELEMENT24ID,DATAELEMENT25ID,DATAELEMENT26ID,DATAELEMENT27ID,DATAELEMENT28ID,DATAELEMENT29ID,DATAELEMENT30ID
    ,COMMENT
    ,POSTDATE
    ,GLACCOUNTID
    ,TRANSACTIONCURRENCYID
    ,TYPECODE
    -- Boilerplate

    ,DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID    
  )          
  select
    JER.REVERSALID
    ,JER.REVERSALLINEITEMID
    ,1-JE.TRANSACTIONTYPECODE -- Opposite transaction side

    ,1-JE.SUBLEDGERTYPECODE
    ,JE.CLASSCODE
    ,JE.SEQUENCE
    ,JE.TRANSACTIONAMOUNT
    ,JE.BASEAMOUNT
    ,JE.ORGAMOUNT
    ,JE.PERCENTAGE
    ,DATAELEMENT1ID,DATAELEMENT2ID,DATAELEMENT3ID,DATAELEMENT4ID,DATAELEMENT5ID,DATAELEMENT6ID,DATAELEMENT7ID,DATAELEMENT8ID,DATAELEMENT9ID,DATAELEMENT10ID
    ,DATAELEMENT11ID,DATAELEMENT12ID,DATAELEMENT13ID,DATAELEMENT14ID,DATAELEMENT15ID,DATAELEMENT16ID,DATAELEMENT17ID,DATAELEMENT18ID,DATAELEMENT19ID,DATAELEMENT20ID
    ,DATAELEMENT21ID,DATAELEMENT22ID,DATAELEMENT23ID,DATAELEMENT24ID,DATAELEMENT25ID,DATAELEMENT26ID,DATAELEMENT27ID,DATAELEMENT28ID,DATAELEMENT29ID,DATAELEMENT30ID
    ,COMMENT
    ,ISNULL(@REVERSALPOSTDATE,JE.POSTDATE)
    ,GLACCOUNTID
    ,TRANSACTIONCURRENCYID
    ,JE.TYPECODE
    -- Boilerplate

    ,@CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID
  from
    dbo.JOURNALENTRY as JE
    inner join @JEREVERSALS as JER
      on JE.ID = JER.ORIGINALID
  ;

  -- Is this the price to pay for backwards compatibility?

  insert into dbo.JOURNALENTRY_EXT
  (
    ID
    ,ACCOUNT
    ,JOURNAL
    ,OUTDATED
    ,PRECALCBASEEXCHANGERATEID
    ,PRECALCORGANIZATIONEXCHANGERATEID
    ,PROJECT
    ,REVERSEDGLTRANSACTIONID
    ,TABLENAMECODE
    -- Boilerplate

    ,DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID
  )    
  select
    JER.REVERSALID
    ,JEXT.ACCOUNT
    ,'Blackbaud Enterprise'
    ,0
    ,JEXT.PRECALCBASEEXCHANGERATEID
    ,JEXT.PRECALCORGANIZATIONEXCHANGERATEID
    ,JEXT.PROJECT
    ,JER.ORIGINALID
    ,0
    -- Boilerplate

    ,@CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID
  from @JEREVERSALS as JER
  inner join dbo.JOURNALENTRY_EXT as JEXT
    on JER.ORIGINALID = JEXT.ID
  ;  

  update dbo.JOURNALENTRY_EXT
  set
    REVERSEDATE = @CURRENTDATE
    ,OUTDATED = 1
    -- Boilerplate

    ,DATECHANGED = @CURRENTDATE
    ,CHANGEDBYID = @CHANGEAGENTID
  from dbo.JOURNALENTRY_EXT as JEXT
  inner join @JEREVERSALS as JER
      on JEXT.ID = JER.ORIGINALID
  ;


  select  ORIGINALID
        , REVERSALID from @REVERSALS;

  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
        return 1;
  end catch

  return 0;

end