USP_SAVE_REVERSAL_LINEITEM

create procedure dbo.USP_SAVE_REVERSAL_LINEITEM
(
    @LINEITEMS UDT_GENERICID readonly,
    @ADJUSTMENTID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null,
    @POSTDATE datetime = null,
    @POSTSTATUSCODE tinyint = 1 -- FTM codes. 1 - NP, 2 - P, 3 - DNP

)
as
begin

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

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

delete from dbo.FINANCIALTRANSACTIONLINEITEM where ID in
(select
    LI.ID
from
    @LINEITEMS L
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.REVERSEDLINEITEMID = L.ID
where
    LI.POSTSTATUSCODE <> 2
    and LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID
    and LI.TYPECODE = 1)

declare @REVERSALIDS table (ORIGINALID uniqueidentifier, REVERSALID uniqueidentifier)

insert into @REVERSALIDS (ORIGINALID, REVERSALID)
select
    ID,
    NEWID()
from @LINEITEMS

--create reversals

insert into dbo.FINANCIALTRANSACTIONLINEITEM
    (ID, FINANCIALTRANSACTIONID, TRANSACTIONAMOUNT, VISIBLE, DESCRIPTION, SEQUENCE, TYPECODE,
    POSTDATE, POSTSTATUSCODE, REVERSEDLINEITEMID, SOURCELINEITEMID, TARGETLINEITEMID, DELETEDON,
    BASEAMOUNT, ORGAMOUNT, QUANTITY, UNITVALUE, FINANCIALTRANSACTIONLINEITEMADJUSTMENTID, BATCHID,
    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
    R.REVERSALID, LI.FINANCIALTRANSACTIONID, LI.TRANSACTIONAMOUNT, LI.VISIBLE, LI.DESCRIPTION, LI.SEQUENCE, 1,
    @POSTDATE, @POSTSTATUSCODE, LI.ID, null, null, null,
    LI.BASEAMOUNT, LI.ORGAMOUNT, LI.QUANTITY, LI.UNITVALUE, @ADJUSTMENTID, LI.BATCHID,
    @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
from
    @REVERSALIDS R
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on R.ORIGINALID = LI.ID

declare @ORIGINALJE table
    (ORIGINALJEID uniqueidentifier, ORIGINALLIID uniqueidentifier,
    REVERSALJEID uniqueidentifier, REVERSALLIID uniqueidentifier)
insert into @ORIGINALJE (ORIGINALJEID, ORIGINALLIID, REVERSALJEID, REVERSALLIID)
select
    JE.ID, R.ORIGINALID, NEWID(), R.REVERSALID
from
    @REVERSALIDS R
    inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = R.ORIGINALID

insert into dbo.JOURNALENTRY
    (ID, FINANCIALTRANSACTIONLINEITEMID, TRANSACTIONTYPECODE, CLASSCODE, JOURNALCODEID,
    SEQUENCE, TRANSACTIONAMOUNT, PERCENTAGE, SUMMARYID, BATCHID, COMMENT, POSTDATE, FINANCIALBATCHID,
    GLACCOUNTID, SUBLEDGERTYPECODE, BASEAMOUNT, ORGAMOUNT, TRANSACTIONCURRENCYID,
    TYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
    HJE.REVERSALJEID, HJE.REVERSALLIID, case JE.TRANSACTIONTYPECODE when 1 then 0 else 1 end, JE.CLASSCODE, JE.JOURNALCODEID,
    JE.SEQUENCE, JE.TRANSACTIONAMOUNT, JE.PERCENTAGE, JE.SUMMARYID, null, JE.COMMENT, @POSTDATE, JE.FINANCIALBATCHID,
    JE.GLACCOUNTID, case JE.SUBLEDGERTYPECODE when 1 then 0 else 1 end, JE.BASEAMOUNT, JE.ORGAMOUNT, JE.TRANSACTIONCURRENCYID, 
    JE.TYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
from
    @ORIGINALJE HJE
    inner join dbo.JOURNALENTRY JE on JE.ID = HJE.ORIGINALJEID

insert into dbo.JOURNALENTRY_EXT
    (ID, PROJECT, JOURNAL, TABLENAMECODE, BATCHID, REVERSEDGLTRANSACTIONID, REVERSEDATE, DISTRIBUTIONTABLEID,
    REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, OUTDATED, WRITEOFFID, PROPERTYDETAILID, STOCKSALEID,
    PAYMENTMETHODCODE, LOGICALREVENUEID, GIFTINKINDSALEID, CREDITITEMID, DISCOUNTCREDITITEMID,
    REVENUESPLITGIFTFEEID, REVENUESPLITGIFTAIDID, ACCOUNT, PRECALCPOSTSTATUSCODE, REVENUEPURCHASEID,
    PLANNEDGIFTPAYOUTID, PRECALCORGANIZATIONEXCHANGERATEID, PRECALCBASEEXCHANGERATEID, FULLYPAIDSTATUSCODE,
    BENEFITTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
    HJE.REVERSALJEID, JEX.PROJECT, JEX.JOURNAL, JEX.TABLENAMECODE, null, HJE.ORIGINALJEID, null, JEX.DISTRIBUTIONTABLEID,
    JEX.REVENUEID, JEX.GLPAYMENTMETHODREVENUETYPEMAPPINGID, 1, JEX.WRITEOFFID, JEX.PROPERTYDETAILID, JEX.STOCKSALEID,
    JEX.PAYMENTMETHODCODE, JEX.LOGICALREVENUEID, JEX.GIFTINKINDSALEID, JEX.CREDITITEMID, JEX.DISCOUNTCREDITITEMID,
    JEX.REVENUESPLITGIFTFEEID, JEX.REVENUESPLITGIFTAIDID, JEX.ACCOUNT, JEX.PRECALCPOSTSTATUSCODE, JEX.REVENUEPURCHASEID,
    JEX.PLANNEDGIFTPAYOUTID, JEX.PRECALCORGANIZATIONEXCHANGERATEID, JEX.PRECALCBASEEXCHANGERATEID, JEX.FULLYPAIDSTATUSCODE,
    JEX.BENEFITTYPECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
from
    @ORIGINALJE HJE
    inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = HJE.ORIGINALJEID

update JEX set
    REVERSEDATE = @CHANGEDATE
from
    @ORIGINALJE HJE
    inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = HJE.ORIGINALJEID

end