USP_SAVE_BENEFITLIABILITYBACKOUT

CREATE procedure dbo.USP_SAVE_BENEFITLIABILITYBACKOUT
(
    @PAYMENTID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null
)
as
begin

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

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

    declare @BACKOUTS table
    (
        ID uniqueidentifier,
        LIABILITYID uniqueidentifier,
        POSTDATE date
    )

    insert into @BACKOUTS
        (ID, LIABILITYID, POSTDATE)
    select
        newid(), BENEFITLIABILITYLI.ID, isnull(BA.POSTDATE, PAYMENTLI.POSTDATE)
    from
        dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTLI
        cross apply (select distinct PAYMENTID, PLEDGEID from dbo.INSTALLMENTSPLITPAYMENT ISP where ISP.PAYMENTID = PAYMENTLI.ID) as ISP
        inner join dbo.FINANCIALTRANSACTION PLEDGE on PLEDGE.ID = ISP.PLEDGEID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM BENEFITLIABILITYLI on BENEFITLIABILITYLI.FINANCIALTRANSACTIONID = PLEDGE.ID
        inner join dbo.REVENUEBENEFIT_EXT RBX ON RBX.ID = BENEFITLIABILITYLI.ID
        left join dbo.BENEFITADJUSTMENT BA on PAYMENTLI.FINANCIALTRANSACTIONID = BA.REVENUEID and BA.POSTSTATUSCODE <> 0
    where
        PAYMENTLI.FINANCIALTRANSACTIONID = @PAYMENTID
        and dbo.UFN_PLEDGE_GETBALANCE(PLEDGE.ID) <= 0
        and BENEFITLIABILITYLI.DELETEDON is null
        and BENEFITLIABILITYLI.TYPECODE <> 1
        and RBX.BENEFITTYPECODE = 2
        and RBX.SENDBENEFIT = 1

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

    insert into dbo.REVENUEBENEFIT_EXT
        (ID, BENEFITID, SENDBENEFIT, PERCENTAPPLICABLEAMOUNT, VALUEPERCENT, REVENUESPLITID,
        BENEFITTYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select
        BO.ID, RBX.BENEFITID, RBX.SENDBENEFIT, RBX.PERCENTAPPLICABLEAMOUNT, RBX.VALUEPERCENT, RBX.REVENUESPLITID,
        3, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
    from
        @BACKOUTS BO
        inner join dbo.REVENUEBENEFIT_EXT RBX on RBX.ID = BO.LIABILITYID

    declare @LINEITEMS UDT_GENERICID
    insert into @LINEITEMS
    select ID from @BACKOUTS

    exec USP_SAVE_BENEFITGLDISTRIBUTION_LINEITEMS @LINEITEMS, @CHANGEAGENTID, @CHANGEDATE

    --never create backouts for these line items again

    update RBX set
        SENDBENEFIT = 0
    from
        @BACKOUTS BO
        inner join dbo.FINANCIALTRANSACTIONLINEITEM BACKOUTLI on BACKOUTLI.ID = BO.ID
        inner join dbo.FINANCIALTRANSACTION FT on FT.ID = BACKOUTLI.FINANCIALTRANSACTIONID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM ALLLI on ALLLI.FINANCIALTRANSACTIONID = FT.ID
        inner join dbo.REVENUEBENEFIT_EXT RBX on RBX.ID = ALLLI.ID

end