USP_SAVE_HISTORICAL_LINEITEM

CREATE procedure dbo.USP_SAVE_HISTORICAL_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

/*
Reversals MUST be created before historical copies are created.
Creating historical copies disassociates journal entries from their original line item.
*/

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 @ORIGINALLINEITEMS table (ORIGINALID uniqueidentifier, HISTORICALCOPYID uniqueidentifier)

insert into @ORIGINALLINEITEMS (ORIGINALID, HISTORICALCOPYID)
select
    ID,
    NEWID()
from @LINEITEMS


--create historical copies

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
    SLI.HISTORICALCOPYID, LI.FINANCIALTRANSACTIONID, LI.TRANSACTIONAMOUNT, LI.VISIBLE, LI.DESCRIPTION, LI.SEQUENCE, LI.TYPECODE,
    LI.POSTDATE, LI.POSTSTATUSCODE, LI.ID, LI.SOURCELINEITEMID, LI.TARGETLINEITEMID, @CHANGEDATE,
    LI.BASEAMOUNT, LI.ORGAMOUNT, LI.QUANTITY, LI.UNITVALUE, LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID, LI.BATCHID,
    LI.ADDEDBYID, LI.CHANGEDBYID, LI.DATEADDED, LI.DATECHANGED
from
    @ORIGINALLINEITEMS SLI
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = SLI.ORIGINALID

--historical copy should use existing journal entries

update dbo.JOURNALENTRY set
    FINANCIALTRANSACTIONLINEITEMID = SLI.HISTORICALCOPYID
from
    @ORIGINALLINEITEMS SLI
where
    FINANCIALTRANSACTIONLINEITEMID = SLI.ORIGINALID

update JEX set
    OUTDATED = 1
from
    @ORIGINALLINEITEMS SLI
    inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = SLI.HISTORICALCOPYID
    inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID

update LI set
    POSTSTATUSCODE = @POSTSTATUSCODE,
    POSTDATE = @POSTDATE,
    FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID,
    DATECHANGED = @CHANGEDATE,
    CHANGEDBYID = @CHANGEAGENTID
from
    @ORIGINALLINEITEMS SLI
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = SLI.ORIGINALID

end