USP_SALESORDER_CREATETOPDOWNDISTRIBUTIONS

CREATE procedure dbo.USP_SALESORDER_CREATETOPDOWNDISTRIBUTIONS
(
    @REVENUEID uniqueidentifier,
    @SALESORDERID uniqueidentifier,
    @PDACCOUNTSYSTEMID uniqueidentifier,
    @ALLOWGLDISTRIBUTIONS bit,
    @CURRENTDATE datetime,
    @CHANGEAGENTID uniqueidentifier,
    @POSTPAYMENTID uniqueidentifier = null, -- Only happens for payments for completed group sales orders

    @POSTPAYMENTAMOUNT money = null -- ditto with above 

)
as
    set nocount on;

    declare @POSTSTATUSCODE tinyint = 3;  -- Do not post

    declare @POSTDATE date = null;
    declare @ISVISIBLE bit = 0;

    declare @SALESORDERLINEITEMS table 
    (
        ORDERLINEITEMID uniqueidentifier, 
        ISPAID bit, 
        ITEMAMOUNTREMAINING money, 
        TYPECODE tinyint, 
        FINANCIALTRANSACTIONID uniqueidentifier
    )
    insert into @SALESORDERLINEITEMS
    select
        FINANCIALTRANSACTIONLINEITEM.ID as ORDERLINEITEMID,
        0 as ISPAID,
        FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
        REVENUESPLIT_EXT.TYPECODE,
        FINANCIALTRANSACTIONID
    from
        dbo.FINANCIALTRANSACTIONLINEITEM
    inner join 
        dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
    where
        FINANCIALTRANSACTIONID = @REVENUEID
        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
        and REVENUESPLIT_EXT.TYPECODE in (1,2,5,6,7,10,11,14,16)    --1: event registration, 2: membership, 5: ticket, 6: fee, 7: tax, 10: supply resources, 11: staff resources, 14: facility, 16: merchandise    

    order by 
        REVENUESPLIT_EXT.TYPECODE desc;

    with ITEMDISCOUNT_CTE as
    (
        select
            ORDERLINEITEMID as ORDERLINEITEMID,
            ORDERLINEITEM.ITEMAMOUNTREMAINING - sum(isnull(ITEMDISCOUNT.BASEAMOUNT, 0)) REMAINING,
            ORDERLINEITEM.ITEMAMOUNTREMAINING TOTAL
        from
            @SALESORDERLINEITEMS ORDERLINEITEM
        left join
            dbo.FINANCIALTRANSACTIONLINEITEM ITEMDISCOUNT on ITEMDISCOUNT.SOURCELINEITEMID = ORDERLINEITEMID
        where
            ITEMDISCOUNT.TYPECODE = 5 --ONLY discounts

        group by 
            ORDERLINEITEMID, ITEMAMOUNTREMAINING
    )
    update @SALESORDERLINEITEMS
    set 
        ISPAID = case when (REMAINING <=0) then 1 else 0 end,
        ITEMAMOUNTREMAINING = REMAINING
    from 
        @SALESORDERLINEITEMS ORDERLINEITEMS
    inner join 
        ITEMDISCOUNT_CTE DISCOUNTS on DISCOUNTS.ORDERLINEITEMID = ORDERLINEITEMS.ORDERLINEITEMID;

    -- Adjust amounts remaining based on other (pre/post)payments

    with OTHERPAYMENT_CTE as
    (
        select
            ORDERLINEITEMID as ORDERLINEITEMID,
            ORDERLINEITEM.ITEMAMOUNTREMAINING - APPLIEDPAYMENTS.AMOUNT as REMAINING
        from
            @SALESORDERLINEITEMS ORDERLINEITEM
        outer apply (
            select
                isnull(sum(PAYMENTLI.BASEAMOUNT), 0) as AMOUNT
            from
                dbo.FINANCIALTRANSACTIONLINEITEM AS PAYMENTLI
            where
                PAYMENTLI.TYPECODE <> 5  -- Discount

                and PAYMENTLI.DELETEDON is null
                -- Editing GL on the GL Distribution tab can cause adjustments

                -- that point these records to the adjustment history line items.

                -- WARNING: this only works if the adjustment doesn't change the line item amounts

                -- which the user currently can't do.

                and (
                    PAYMENTLI.SOURCELINEITEMID = ORDERLINEITEM.ORDERLINEITEMID
                    or PAYMENTLI.SOURCELINEITEMID in (
                        select ADJUSTMENTHISTORYLI.ID
                        from dbo.FINANCIALTRANSACTIONLINEITEM as ADJUSTMENTHISTORYLI
                        where ADJUSTMENTHISTORYLI.REVERSEDLINEITEMID = ORDERLINEITEM.ORDERLINEITEMID
                    )
                )
        ) as APPLIEDPAYMENTS
    )
    update @SALESORDERLINEITEMS
    set 
        ISPAID = case when (REMAINING <=0) then 1 else 0 end,
        ITEMAMOUNTREMAINING = REMAINING
    from 
        @SALESORDERLINEITEMS ORDERLINEITEMS
    inner join 
        OTHERPAYMENT_CTE OTHERPAYMENT on OTHERPAYMENT.ORDERLINEITEMID = ORDERLINEITEMS.ORDERLINEITEMID;


    declare @TRANSACTIONDATE date = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());

    declare @UNEARNEDLINEITEMID uniqueidentifier;
    declare @UNEARNEDAMOUNT money;

    if @POSTPAYMENTID is not null
    begin
        if @ALLOWGLDISTRIBUTIONS = 1 begin
            set @POSTSTATUSCODE = 1;  -- Not posted

            set @POSTDATE = @TRANSACTIONDATE;
        end

        select
            @UNEARNEDLINEITEMID = FTLI.ID,
            @UNEARNEDAMOUNT = FTLI.TRANSACTIONAMOUNT
        from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
        inner join dbo.REVENUESPLIT_EXT EXT on FTLI.ID = EXT.ID
        where FTLI.FINANCIALTRANSACTIONID = @POSTPAYMENTID
            and EXT.TYPECODE = 19
            and FTLI.POSTSTATUSCODE <> 2
            and DELETEDON is null

        set @ISVISIBLE = 1;

        declare @TOTALREMAINING money;
        select @TOTALREMAINING = coalesce(sum(ITEMAMOUNTREMAINING), 0)
        from @SALESORDERLINEITEMS;

        if @UNEARNEDLINEITEMID is not null
        begin
            if @POSTPAYMENTAMOUNT - @UNEARNEDAMOUNT > @TOTALREMAINING
            begin
                --Increase the value of the UR line item.

                set @UNEARNEDAMOUNT = @POSTPAYMENTAMOUNT - @TOTALREMAINING;

                update dbo.FINANCIALTRANSACTIONLINEITEM
                set TRANSACTIONAMOUNT = @UNEARNEDAMOUNT,
                    BASEAMOUNT = @UNEARNEDAMOUNT,
                    ORGAMOUNT = @UNEARNEDAMOUNT,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @UNEARNEDLINEITEMID;
            end
            else if @POSTPAYMENTAMOUNT - @UNEARNEDAMOUNT < @TOTALREMAINING
            begin
                --Decrease the value of the UR line item, if possible.

                --The smallest our UR can be is max(paymentamount - totalremaining, refundedamount, 0)


                --TODO: Revisit. I'm seeing refunds linked to the wrong unearned revenue LI.

                declare @REFUNDEDAMOUNT money = 0;
                select @REFUNDEDAMOUNT = coalesce((
                    select sum(REFUNDLI.TRANSACTIONAMOUNT)
                    from dbo.FINANCIALTRANSACTION FT
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM REFUNDLI on FT.ID = REFUNDLI.FINANCIALTRANSACTIONID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTLI on REFUNDLI.SOURCELINEITEMID = PAYMENTLI.ID
                    where FT.TYPECODE = 23
                        and PAYMENTLI.FINANCIALTRANSACTIONID = @POSTPAYMENTID
                ), 0);

                declare @TARGETUNEARNEDMOUNT money = @POSTPAYMENTAMOUNT - @TOTALREMAINING;

                if @TARGETUNEARNEDMOUNT < @REFUNDEDAMOUNT
                    set @TARGETUNEARNEDMOUNT = @REFUNDEDAMOUNT;

                if @TARGETUNEARNEDMOUNT < @UNEARNEDAMOUNT
                begin
                    if @TARGETUNEARNEDMOUNT = 0
                    begin
                        update dbo.FINANCIALTRANSACTIONLINEITEM
                        set REVERSEDLINEITEMID = null,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where REVERSEDLINEITEMID = @UNEARNEDLINEITEMID;

                        delete from dbo.FINANCIALTRANSACTIONLINEITEM
                        where ID = @UNEARNEDLINEITEMID;

                        set @UNEARNEDLINEITEMID = null;
                    end
                    else begin
                        update dbo.FINANCIALTRANSACTIONLINEITEM
                        set TRANSACTIONAMOUNT = @TARGETUNEARNEDMOUNT,
                            BASEAMOUNT = @TARGETUNEARNEDMOUNT,
                            ORGAMOUNT = @TARGETUNEARNEDMOUNT,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID = @UNEARNEDLINEITEMID;
                    end
                end
            end
        end
        else if @UNEARNEDLINEITEMID is null and @POSTPAYMENTAMOUNT > @TOTALREMAINING
        begin
            set @UNEARNEDLINEITEMID = newid();
            set @UNEARNEDAMOUNT = @POSTPAYMENTAMOUNT - @TOTALREMAINING;

            insert into dbo.FINANCIALTRANSACTIONLINEITEM 
            (
                ID,
                FINANCIALTRANSACTIONID,
                TYPECODE,
                BASEAMOUNT,
                TRANSACTIONAMOUNT,
                ORGAMOUNT,
                POSTDATE,
                POSTSTATUSCODE,
                SOURCELINEITEMID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            ) 
            values 
            (
                @UNEARNEDLINEITEMID,
                @POSTPAYMENTID,
                0,  -- Standard

                @UNEARNEDAMOUNT,
                @UNEARNEDAMOUNT,
                @UNEARNEDAMOUNT,
                @POSTDATE,
                @POSTSTATUSCODE,
                null,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            );

            insert into dbo.REVENUESPLIT_EXT
            (
                ID,                     
                APPLICATIONCODE,       
                TYPECODE,              
                DESIGNATIONID, 
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED                
            )
            select
                @UNEARNEDLINEITEMID,
                10,  -- Order

                19,  -- Unearned revenue

                null,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE;
        end
    end

    declare @PAYMENTID uniqueidentifier;
    declare @PAYMENTAMOUNT money;
    declare @PAYMENTRUNNINGAMOUNT money;

    --If we have no @POSTPAYMENTID, we only want our cursor to only include payments made before checkin.

    declare @RESERVATIONCOMPLETETIME datetimeoffset = coalesce((
        select STATUSDATEWITHOFFSET
        from dbo.RESERVATIONSTATUSHISTORY
        where RESERVATIONID = @SALESORDERID
            and STATUSCODE = 1        -- Completion date

    ), sysdatetimeoffset())

    declare PAYMENTS_CURSOR cursor local static for
    select
        SALESORDERPAYMENT.PAYMENTID,
        SALESORDERPAYMENT.AMOUNT
            - (
                select isnull(sum(CREDITPAYMENT.AMOUNT), 0)
                from dbo.CREDITPAYMENT
                where CREDITPAYMENT.REVENUEID = SALESORDERPAYMENT.PAYMENTID
            ) as AMOUNT
    from 
        dbo.SALESORDERPAYMENT with (nolock)
    where
        SALESORDERPAYMENT.PAYMENTID = @POSTPAYMENTID  -- For after check in payments, only bring back that payment to distribute

        or (  -- This should grab payments that need to be distributed at check in

            @POSTPAYMENTID is null
            and SALESORDERPAYMENT.SALESORDERID = @SALESORDERID
            and SALESORDERPAYMENT.PAYMENTDATEWITHTIMEOFFSET < @RESERVATIONCOMPLETETIME
        )
    order by SALESORDERPAYMENT.DATEADDED asc;

    open PAYMENTS_CURSOR
    fetch next from PAYMENTS_CURSOR into @PAYMENTID, @PAYMENTAMOUNT
    while @@FETCH_STATUS = 0
    begin
        if @PAYMENTAMOUNT > 0
        begin
            set @PAYMENTRUNNINGAMOUNT = @PAYMENTAMOUNT

            declare ITEMS_CURSOR cursor local static for
            select
                ORDERLINEITEMID, 
                ITEMAMOUNTREMAINING,
                TYPECODE,
                FINANCIALTRANSACTIONID
            from 
                @SALESORDERLINEITEMS
            where 
                ISPAID <> 1;

            declare @ORDERLINEITEMID uniqueidentifier;
            declare @ITEMAMOUNTREMAINING money;
            declare @AMOUNTPAID money;
            declare @TYPECODE tinyint;
            declare @FINANCIALTRANSACTIONID uniqueidentifier;

            open ITEMS_CURSOR
            fetch next from ITEMS_CURSOR into @ORDERLINEITEMID, @ITEMAMOUNTREMAINING, @TYPECODE, @FINANCIALTRANSACTIONID

            while @@fetch_status = 0
            begin
                if @PAYMENTRUNNINGAMOUNT >= @ITEMAMOUNTREMAINING
                begin
                    update @SALESORDERLINEITEMS
                        set    ISPAID = 1,
                            ITEMAMOUNTREMAINING = 0
                    where 
                        ORDERLINEITEMID = @ORDERLINEITEMID

                    set @PAYMENTRUNNINGAMOUNT = @PAYMENTRUNNINGAMOUNT - @ITEMAMOUNTREMAINING        
                    set @AMOUNTPAID = @ITEMAMOUNTREMAINING
                end
                else
                begin
                    update @SALESORDERLINEITEMS
                        set ITEMAMOUNTREMAINING = ITEMAMOUNTREMAINING - @PAYMENTRUNNINGAMOUNT
                    where
                        ORDERLINEITEMID = @ORDERLINEITEMID

                    set @AMOUNTPAID = @PAYMENTRUNNINGAMOUNT
                    set @PAYMENTRUNNINGAMOUNT = 0
                end

                declare @FTLIID as uniqueidentifier = newid();
                insert into dbo.FINANCIALTRANSACTIONLINEITEM 
                (
                    ID,
                    FINANCIALTRANSACTIONID,
                    TYPECODE,
                    BASEAMOUNT,
                    TRANSACTIONAMOUNT,
                    ORGAMOUNT,
                    POSTDATE,
                    POSTSTATUSCODE,
                    SOURCELINEITEMID,
                    VISIBLE,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                ) 
                values 
                (
                    @FTLIID,
                    @PAYMENTID,
                    0,
                    @AMOUNTPAID,
                    @AMOUNTPAID,
                    @AMOUNTPAID,
                    @POSTDATE,
                    @POSTSTATUSCODE,
                    @ORDERLINEITEMID,
                    @ISVISIBLE,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                );

                insert into dbo.REVENUESPLIT_EXT
                (
                    ID,                     
                    APPLICATIONCODE,       
                    TYPECODE,              
                    DESIGNATIONID, 
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED                
                )
                select
                    @FTLIID,
                    10,
                    @TYPECODE,
                    null,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE

                insert into dbo.REVENUESPLITORDER
                (
                    ID, 
                    PROGRAMID, 
                    EVENTID, 
                    FEEID, 
                    TAXID, 
                    RESOURCEID, 
                    VOLUNTEERTYPEID, 
                    EVENTLOCATIONID, 
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED
                )
                select 
                    @FTLIID, 
                    PROGRAMID,
                    EVENTID,
                    FEEID,
                    TAXID,
                    RESOURCEID,
                    VOLUNTEERTYPEID,
                    EVENTLOCATIONID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from dbo.REVENUESPLITORDER
                where ID = @FINANCIALTRANSACTIONID;

                if @PAYMENTRUNNINGAMOUNT <=0 
                    break;

                fetch next from ITEMS_CURSOR into @ORDERLINEITEMID, @ITEMAMOUNTREMAINING, @TYPECODE, @FINANCIALTRANSACTIONID;
            end;

            close ITEMS_CURSOR;
            deallocate ITEMS_CURSOR;

            exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE 
                @ID = @PAYMENTID, 
                @PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID, 
                @CHANGEDATE = @TRANSACTIONDATE, 
                @CHANGEAGENTID = @CHANGEAGENTID;
        end

        fetch next from PAYMENTS_CURSOR into @PAYMENTID, @PAYMENTAMOUNT
    end

    close PAYMENTS_CURSOR
    deallocate PAYMENTS_CURSOR

    -- Only do the following for payments made before check in

    if @POSTPAYMENTID is null begin
        declare @ARDEBITS table (ID uniqueidentifier not null, LINEITEMID uniqueidentifier not null)
        insert into @ARDEBITS
            (ID, LINEITEMID)
        select ARDEBITS.ID, LI.ID
        from @SALESORDERLINEITEMS SOLI
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = SOLI.ORDERLINEITEMID
            inner join dbo.JOURNALENTRY ARDEBITS on ARDEBITS.FINANCIALTRANSACTIONLINEITEMID = LI.ID
        where ARDEBITS.TRANSACTIONTYPECODE = 0

        declare @LIABILITYDEBITS table 
        (
            ID uniqueidentifier,
            ARDEBITID uniqueidentifier,
            TRANSACTIONAMOUNT money,
            BASEAMOUNT money,
            ORGAMOUNT money,
            GLACCOUNTID uniqueidentifier,
            TYPEMAPPINGID uniqueidentifier
        )
        insert into @LIABILITYDEBITS
            (ID,ARDEBITID,TRANSACTIONAMOUNT,BASEAMOUNT,ORGAMOUNT,GLACCOUNTID,TYPEMAPPINGID)
        select
            NEWID(),ARD.ID,APPLICATIONLINEITEM.TRANSACTIONAMOUNT,APPLICATIONLINEITEM.BASEAMOUNT,APPLICATIONLINEITEM.ORGAMOUNT,
            PAYMENTCREDIT.GLACCOUNTID,PAYMENTCREDIT_EXT.GLPAYMENTMETHODREVENUETYPEMAPPINGID
        from @ARDEBITS ARD
            inner join dbo.FINANCIALTRANSACTIONLINEITEM APPLICATIONLINEITEM on APPLICATIONLINEITEM.SOURCELINEITEMID = ARD.LINEITEMID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTLINEITEM on PAYMENTLINEITEM.FINANCIALTRANSACTIONID = APPLICATIONLINEITEM.FINANCIALTRANSACTIONID
            inner join dbo.REVENUESPLIT_EXT RSX on RSX.ID = PAYMENTLINEITEM.ID
            inner join dbo.JOURNALENTRY PAYMENTCREDIT on PAYMENTCREDIT.FINANCIALTRANSACTIONLINEITEMID = PAYMENTLINEITEM.ID
            inner join dbo.JOURNALENTRY_EXT PAYMENTCREDIT_EXT on PAYMENTCREDIT.ID = PAYMENTCREDIT_EXT.ID
        where
            RSX.TYPECODE = 19
            and PAYMENTLINEITEM.DELETEDON is null
            and PAYMENTLINEITEM.TYPECODE = 0  -- Standard

            and PAYMENTLINEITEM.POSTSTATUSCODE <> 3 --ignore existing do no post credits

            and PAYMENTCREDIT.TRANSACTIONTYPECODE = 1

        --insert liability debits for order line items

        insert into dbo.JOURNALENTRY
            (ID, FINANCIALTRANSACTIONLINEITEMID, TRANSACTIONTYPECODE, SUBLEDGERTYPECODE,
            TRANSACTIONAMOUNT, BASEAMOUNT, ORGAMOUNT, COMMENT, POSTDATE,
            FINANCIALBATCHID, GLACCOUNTID, TRANSACTIONCURRENCYID, TYPECODE, 
            DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
        select
            LD.ID, ARD.FINANCIALTRANSACTIONLINEITEMID, 0, 0,
            LD.TRANSACTIONAMOUNT, LD.BASEAMOUNT, LD.ORGAMOUNT, ARD.COMMENT, ARD.POSTDATE,
            ARD.FINANCIALBATCHID, LD.GLACCOUNTID, ARD.TRANSACTIONCURRENCYID, ARD.TYPECODE, 
            @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID
        from @LIABILITYDEBITS LD
            inner join dbo.JOURNALENTRY ARD on ARD.ID = LD.ARDEBITID

        insert into dbo.JOURNALENTRY_EXT
            (ID, PROJECT, JOURNAL, TABLENAMECODE, BATCHID, REVERSEDGLTRANSACTIONID, REVERSEDATE, 
            DISTRIBUTIONTABLEID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, OUTDATED, 
            PAYMENTMETHODCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, 
            LOGICALREVENUEID, ACCOUNT, PRECALCPOSTSTATUSCODE, BENEFITTYPECODE)
        select
            LD.ID, ODX.PROJECT, ODX.JOURNAL, ODX.TABLENAMECODE, ODX.BATCHID, ODX.REVERSEDGLTRANSACTIONID, ODX.REVERSEDATE, 
            ODX.DISTRIBUTIONTABLEID, ODX.REVENUEID, LD.TYPEMAPPINGID, ODX.OUTDATED, 
            ODX.PAYMENTMETHODCODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, 
            ODX.LOGICALREVENUEID, GL.ACCOUNTNUMBER, ODX.PRECALCPOSTSTATUSCODE, ODX.BENEFITTYPECODE
        from @LIABILITYDEBITS LD
            inner join @ARDEBITS ARD on ARD.ID = LD.ARDEBITID
            inner join dbo.JOURNALENTRY_EXT ODX on ODX.ID = ARD.ID
            inner join dbo.GLACCOUNT GL on GL.ID = LD.GLACCOUNTID

        --delete AR debits for fully paid order line items

        --update AR balance for underpaid order line items

        merge dbo.JOURNALENTRY as target
        using (
            select
                ARD.ID,
                REVENUECREDIT.TRANSACTIONAMOUNT - sum(LIABILITYDEBIT.TRANSACTIONAMOUNT) as TRANSACTIONAMOUNT,
                REVENUECREDIT.BASEAMOUNT - sum(LIABILITYDEBIT.BASEAMOUNT) as BASEAMOUNT,
                REVENUECREDIT.ORGAMOUNT - sum(LIABILITYDEBIT.ORGAMOUNT) as ORGAMOUNT
            from 
                @ARDEBITS ARD
                inner join dbo.FINANCIALTRANSACTIONLINEITEM ORDERLINEITEM on ORDERLINEITEM.ID = ARD.LINEITEMID
                inner join dbo.JOURNALENTRY REVENUECREDIT on REVENUECREDIT.FINANCIALTRANSACTIONLINEITEMID = ORDERLINEITEM.ID
                inner join @LIABILITYDEBITS LIABILITYDEBIT on LIABILITYDEBIT.ARDEBITID = ARD.ID
            where
                REVENUECREDIT.TRANSACTIONTYPECODE = 1
            group by ARD.ID, REVENUECREDIT.TRANSACTIONAMOUNT, REVENUECREDIT.BASEAMOUNT, REVENUECREDIT.ORGAMOUNT
            ) as source (ID, TRANSACTIONAMOUNT, BASEAMOUNT, ORGAMOUNT)
        on (target.ID = source.ID)
        when matched and source.BASEAMOUNT <= 0
            then delete
        when matched
            then update set 
                target.TRANSACTIONAMOUNT = source.TRANSACTIONAMOUNT,
                target.BASEAMOUNT = source.BASEAMOUNT,
                target.ORGAMOUNT = source.ORGAMOUNT,
                target.DATECHANGED = @CURRENTDATE,
                target.CHANGEDBYID = @CHANGEAGENTID;
    end
    else if @ALLOWGLDISTRIBUTIONS = 1 begin
        exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @POSTPAYMENTID, @CHANGEAGENTID, @CURRENTDATE
    end

    return 0;