USP_RESERVATION_ADJUSTCOMPLETEDORDER

CREATE procedure dbo.USP_RESERVATION_ADJUSTCOMPLETEDORDER
(
    @ORDERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTDATE datetime
)
as
begin
    declare @ORDERADJUSTMENTID uniqueidentifier;
    declare @PAYMENTS table (ID uniqueidentifier, POSTSTATUSCODE tinyint, PAYMENTTIME datetime, PAYMENTDEPOSITID uniqueidentifier);
    declare @ORDERLINEITEMS table (ID uniqueidentifier, POSTSTATUSCODE tinyint, REVENUEID uniqueidentifier, CONSTITUENTID uniqueidentifier);
    declare @ORDERREVENUEID uniqueidentifier;
    declare @TRANSACTIONDATE datetime;
    declare @TRANSACTIONPOSTDATE date;
    declare @CONSTITUENTID uniqueidentifier;
    declare @ALLOWGLDISTRIBUTIONS bit;
    declare @ORDERPOSTSTATUSCODE tinyint;

    -- Set order line items' post dates to be the order's arrival date until the order is posted; thereafter, use current date.

    select
        @TRANSACTIONDATE = SALESORDER.TRANSACTIONDATE,
        @ORDERPOSTSTATUSCODE = coalesce(FINANCIALTRANSACTION.POSTSTATUSCODE, 1),
        @ORDERREVENUEID = SALESORDER.REVENUEID,
        @CONSTITUENTID = SALESORDER.CONSTITUENTID
    from dbo.SALESORDER
        left join dbo.FINANCIALTRANSACTION on SALESORDER.REVENUEID = FINANCIALTRANSACTION.ID
    where SALESORDER.ID = @ORDERID;

    if @ORDERPOSTSTATUSCODE = 2
    set @TRANSACTIONDATE = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());

    set @TRANSACTIONPOSTDATE = cast(@TRANSACTIONDATE as date);

    declare @RESERVATIONCOMPLETETIME datetime = (
        select STATUSDATE
        from dbo.RESERVATIONSTATUSHISTORY
        where RESERVATIONID = @ORDERID
            and STATUSCODE = 1        -- Completion date

    )

    insert into @PAYMENTS
    select
        FINANCIALTRANSACTION.ID,
        FINANCIALTRANSACTION.POSTSTATUSCODE,
        cast(SALESORDERPAYMENT.PAYMENTDATEWITHTIMEOFFSET as datetime),
        BANKACCOUNTDEPOSITPAYMENT.DEPOSITID
    from
        dbo.FINANCIALTRANSACTION
    inner join
        dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.PAYMENTID = FINANCIALTRANSACTION.ID
    left join
        dbo.BANKACCOUNTDEPOSITPAYMENT on BANKACCOUNTDEPOSITPAYMENT.ID = FINANCIALTRANSACTION.ID
    where
        SALESORDERPAYMENT.SALESORDERID = @ORDERID
    order by DATE;

    insert into @ORDERLINEITEMS
    select
        FINANCIALTRANSACTIONLINEITEM.ID,
        FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE,
        SALESORDER.REVENUEID,
        SALESORDER.CONSTITUENTID
    from 
        dbo.FINANCIALTRANSACTIONLINEITEM
    right join 
        dbo.SALESORDER on SALESORDER.REVENUEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
    where 
        SALESORDER.ID = @ORDERID
        and DELETEDON is null;

    select
        @ALLOWGLDISTRIBUTIONS = PD.ALLOWGLDISTRIBUTIONS
    from
        dbo.UFN_PDACCOUNTSYSTEM_DEFAULTORSYSTEM() as PD

    -- Make sure the amounts are in sync.

    update dbo.FINANCIALTRANSACTION
    set 
        TRANSACTIONAMOUNT = SALESORDER.AMOUNT,
        BASEAMOUNT = SALESORDER.AMOUNT,
        ORGAMOUNT = SALESORDER.AMOUNT,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    from
        dbo.FINANCIALTRANSACTION
    inner join
        dbo.SALESORDER on SALESORDER.REVENUEID = FINANCIALTRANSACTION.ID
    where
        SALESORDER.ID = @ORDERID

    declare @ISCOMPLETEDORDEREDITWITHNOADJUSTMENT bit = 0

    -- update all source line item ids that point to order line items to null so we can just do a straight delete from the FTLI table...

    update dbo.FINANCIALTRANSACTIONLINEITEM
    set SOURCELINEITEMID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE            
    where
        SOURCELINEITEMID in
            (
                select 
                    ID
                from @ORDERLINEITEMS
            );

    -- Order Revenue

    if exists
        (
            select 
                1
            from 
                @ORDERLINEITEMS
            where
                POSTSTATUSCODE = 2 -- posted

        )
    begin

        -- Delete unposted overage. USP_SAVE_ADJUSTMENT will mark it as Posted if we don't (not what we want).

        delete FTLI
        from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
            inner join @ORDERLINEITEMS OLI on FTLI.ID = OLI.ID
            inner join dbo.REVENUESPLIT_EXT EXT on FTLI.ID = EXT.ID
        where FTLI.POSTSTATUSCODE = 1
            and EXT.TYPECODE = 20;

        -- Find the Discount line items to reverse separately, because USP_SAVE_ADJUSTMENT does not handle them at this time.

        declare @DISCOUNTLINEITEMIDS UDT_GENERICID;

        insert into @DISCOUNTLINEITEMIDS
        select LI.ID
        from dbo.FINANCIALTRANSACTIONLINEITEM LI
        where LI.FINANCIALTRANSACTIONID = @ORDERREVENUEID
            and LI.TYPECODE = 5
            and LI.POSTSTATUSCODE = 2
            and LI.DELETEDON is null;

        exec dbo.USP_SAVE_ADJUSTMENT @ORDERREVENUEID, @ORDERADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @TRANSACTIONPOSTDATE, 'Group Sales Order Edit';

        -- Discounts only

        exec dbo.USP_SAVE_REVERSAL_LINEITEM @DISCOUNTLINEITEMIDS, @ORDERADJUSTMENTID, @CHANGEAGENTID, @CURRENTDATE, @TRANSACTIONPOSTDATE, 1
        exec dbo.USP_SAVE_HISTORICAL_LINEITEM @DISCOUNTLINEITEMIDS, @ORDERADJUSTMENTID, @CHANGEAGENTID, @CURRENTDATE, @TRANSACTIONPOSTDATE, 1

        update dbo.FINANCIALTRANSACTIONLINEITEM
            set DELETEDON = @CURRENTDATE,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
        where
            ID in
            (
                select ID from @ORDERLINEITEMS
            )

    end
    else
    begin
        -- Get the adjustment ID if one already exists.

        -- Doesn't matter if we take it from the Standard or Reversal item, as long as it's not already marked as deleted.

        set @ORDERADJUSTMENTID = (
            select top 1 FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
            from dbo.FINANCIALTRANSACTIONLINEITEM
            where FINANCIALTRANSACTIONID = @ORDERREVENUEID
                and POSTSTATUSCODE = 1
                and DELETEDON is null
        );

        declare @contextCache varbinary(128);
        declare @e int;

        set @contextCache = CONTEXT_INFO();

        /* set CONTEXT_INFO to @CHANGEAGENTID */
        if not @CHANGEAGENTID is null
            set CONTEXT_INFO @CHANGEAGENTID;

        -- Mark adjustment line items we didn't create as deleted to avoid hitting same table reference constraint when actually deleting below

        update ORDERLI
        set DELETEDON = @CURRENTDATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from dbo.FINANCIALTRANSACTIONLINEITEM ORDERLI
            inner join dbo.FINANCIALTRANSACTIONLINEITEM ADJUSTMENTLI on ADJUSTMENTLI.REVERSEDLINEITEMID = ORDERLI.ID
        where
            ORDERLI.FINANCIALTRANSACTIONID = @ORDERREVENUEID
            and ORDERLI.POSTSTATUSCODE <> 2
            and ORDERLI.TYPECODE <> 1        -- Not reversal

            and ORDERLI.DELETEDON is null;

        delete from dbo.FINANCIALTRANSACTIONLINEITEM
        where
            FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ORDERREVENUEID
            and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE <> 2
            and TYPECODE <> 1        -- Not reversal

            and DELETEDON is null;

        select @e=@@error;

        /* reset CONTEXT_INFO to previous value  */
        if not @contextCache is null
            set CONTEXT_INFO @contextCache;

        if @e <> 0
            return 2;

        if @ORDERADJUSTMENTID is null
            set @ISCOMPLETEDORDEREDITWITHNOADJUSTMENT = 1;
    end

    -- Add order revenue and discount credits

    if @ORDERREVENUEID is not null
        exec dbo.USP_SALESORDER_ADDREVENUE @ORDERID, @ORDERREVENUEID, @TRANSACTIONDATE, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE, @ISCOMPLETEDORDEREDITWITHNOADJUSTMENT, @ORDERADJUSTMENTID;
    else
     begin
        if dbo.UFN_SALESORDER_TOTAL(@ORDERID)= convert(money, 0.00)
        begin
         exec dbo.USP_SALESORDER_GENERATETICKETS @ORDERID, @CHANGEAGENTID, @CURRENTDATE, 1;
         return;
        end
        else
        begin
         set @ORDERREVENUEID = NEWID();
         exec dbo.USP_SALESORDER_ADDREVENUE @ORDERID, @ORDERREVENUEID, @TRANSACTIONDATE, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;
        end
     end

    exec dbo.USP_SALESORDER_ADDDISCOUNTCREDITS @ORDERID, @CHANGEAGENTID, @CURRENTDATE, @TRANSACTIONDATE, @ORDERADJUSTMENTID;


    -- Now handle payments.


    -- First delete FTLIs for all payments made before checkin; they stay fully UR, but we still need to redistribute the UR.

    delete FTLI
    from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
        inner join @PAYMENTS PAYMENTS on FTLI.FINANCIALTRANSACTIONID = PAYMENTS.ID
        left join dbo.REVENUESPLIT_EXT EXT on FTLI.ID = EXT.ID
    where PAYMENTS.PAYMENTTIME < @RESERVATIONCOMPLETETIME
        and FTLI.POSTSTATUSCODE = 3
        and coalesce(EXT.TYPECODE, 0) <> 19;

    -- This needs to be called once for all prepayments before it is called for any postpayment,

    -- because it seems that the order is drawing its GL from UR even when a postpayment is directly applied.

    exec dbo.USP_SALESORDER_ADDPAYMENTREVENUE @ORDERID, @ORDERREVENUEID, @CHANGEAGENTID, @CURRENTDATE;

    -- Open up cursor and loop through payments so we can update the payments that are posted and not posted.    

    declare @PAYMENTID uniqueidentifier;
    declare @PAYMENTPOSTSTATUS tinyint;
    declare @PAYMENTDEPOSITID uniqueidentifier;

    -- Now recreate GL for all prepayments.

    if @ALLOWGLDISTRIBUTIONS = 1 begin
        declare PREPAYMENTS_CURSOR cursor local fast_forward for 
        select ID, PAYMENTDEPOSITID
        from @PAYMENTS
        where PAYMENTTIME < @RESERVATIONCOMPLETETIME;

        open PREPAYMENTS_CURSOR
        fetch next from PREPAYMENTS_CURSOR
        into @PAYMENTID, @PAYMENTDEPOSITID

        while @@FETCH_STATUS = 0 begin
            exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION    @PAYMENTID, @CHANGEAGENTID, @CURRENTDATE;

            -- Fix debit GL distributions (need to be Bank rather than Cash if the payment has been deposited)

            if @PAYMENTDEPOSITID is not null
                exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @PAYMENTID, @PAYMENTDEPOSITID;

            fetch next from PREPAYMENTS_CURSOR
            into @PAYMENTID, @PAYMENTDEPOSITID;
        end

        close PREPAYMENTS_CURSOR;
        deallocate PREPAYMENTS_CURSOR;
    end;

    declare POSTPAYMENTS_CURSOR cursor local fast_forward for 
    select ID, POSTSTATUSCODE, PAYMENTDEPOSITID
    from @PAYMENTS
    where PAYMENTTIME >= @RESERVATIONCOMPLETETIME;

    open POSTPAYMENTS_CURSOR
    fetch next from POSTPAYMENTS_CURSOR
    into @PAYMENTID, @PAYMENTPOSTSTATUS, @PAYMENTDEPOSITID

    while @@FETCH_STATUS = 0 begin

        declare @PAYMENTADJUSTMENTID uniqueidentifier = null;

        -- Since the payment was made after checkin, adjust if necessary and redistribute.

        if @PAYMENTPOSTSTATUS = 2
        begin
            -- Get the adjustment ID if one already exists. If not, we haven't adjusted yet.

            set @PAYMENTADJUSTMENTID = (
                select top 1 FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
                from dbo.FINANCIALTRANSACTIONLINEITEM
                where FINANCIALTRANSACTIONID = @PAYMENTID
                and POSTSTATUSCODE = 1
            )

            -- If the payment is posted and the order revenue is not, then their transaction dates will differ.

            declare @CURRENTDATEWITHOUTTIME date = cast(@CURRENTDATE as date);
            if @PAYMENTADJUSTMENTID is null
                exec dbo.USP_SAVE_ADJUSTMENT @PAYMENTID, @PAYMENTADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @CURRENTDATEWITHOUTTIME, 'Group Sales Payment Edit';
        end

        -- Delete all not-posted line items except for UR.

        declare @UNEARNEDLINEITEMID uniqueidentifier = (
            select FTLI.ID
            from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FTLI.ID
            where FTLI.FINANCIALTRANSACTIONID = @PAYMENTID
                and REVENUESPLIT_EXT.TYPECODE = 19 -- unearned revenue

                and FTLI.POSTSTATUSCODE <> 2
        );

        update dbo.FINANCIALTRANSACTIONLINEITEM
        set SOURCELINEITEMID = null,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where SOURCELINEITEMID in (
            select ID from dbo.FINANCIALTRANSACTIONLINEITEM
            where FINANCIALTRANSACTIONID = @PAYMENTID
                and POSTSTATUSCODE <> 2
                and (ID <> @UNEARNEDLINEITEMID or @UNEARNEDLINEITEMID is null)
        )

        -- Store types of reversed items so we can restore the links later (only matters when posted)

        declare @ADJUSTMENTLINEITEMS table(ID uniqueidentifier, TYPECODE tinyint)
        insert into @ADJUSTMENTLINEITEMS
        select FTLI.ID,
            EXT.TYPECODE
        from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
            inner join dbo.REVENUESPLIT_EXT EXT on FTLI.REVERSEDLINEITEMID = EXT.ID
        where FTLI.FINANCIALTRANSACTIONID = @PAYMENTID;

        update dbo.FINANCIALTRANSACTIONLINEITEM
        set REVERSEDLINEITEMID = null,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where REVERSEDLINEITEMID in (
            select ID from dbo.FINANCIALTRANSACTIONLINEITEM
            where FINANCIALTRANSACTIONID = @PAYMENTID
                and POSTSTATUSCODE <> 2
        );

        declare @contextCache2 varbinary(128);
        declare @e2 int;

        set @contextCache2 = CONTEXT_INFO();

        /* set CONTEXT_INFO to @CHANGEAGENTID */
        if not @CHANGEAGENTID is null
            set CONTEXT_INFO @CHANGEAGENTID;

        -- Make sure all GL for the payment is also deleted, including UR, or else none of it will be be re-created.

        delete JE from dbo.JOURNALENTRY JE
        inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
        where FTLI.FINANCIALTRANSACTIONID = @PAYMENTID
            and FTLI.DELETEDON is null
            and FTLI.POSTSTATUSCODE <> 2
            and FTLI.TYPECODE <> 1    -- not deleted, not a reversal


        delete from dbo.FINANCIALTRANSACTIONLINEITEM
        where FINANCIALTRANSACTIONID = @PAYMENTID
            and DELETEDON is null
            and POSTSTATUSCODE <> 2
            and TYPECODE <> 1    -- Not reversal

            and (ID <> @UNEARNEDLINEITEMID or @UNEARNEDLINEITEMID is null)

        select @e=@@error;

        /* reset CONTEXT_INFO to previous value  */
        if not @contextCache2 is null
            set CONTEXT_INFO @contextCache2;

        if @e2 <> 0
            return 2;

        -- Since the payment was made after checkin, we need to redistribute.

        exec dbo.USP_SALESORDER_ADDPAYMENTREVENUE @ORDERID, @ORDERREVENUEID, @CHANGEAGENTID, @CURRENTDATE, @PAYMENTID;

        -- Fix debit GL distributions (need to be Bank rather than Cash if the payment has been deposited)

        if @PAYMENTDEPOSITID is not null
            exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @PAYMENTID, @PAYMENTDEPOSITID;

        -- Now relink the reversed line items.

        if @PAYMENTPOSTSTATUS = 2
        begin
            --Set the correct adjustment and reversedlineitem ID's on the new line items for UI grouping.

            update dbo.FINANCIALTRANSACTIONLINEITEM
            set FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @PAYMENTADJUSTMENTID
            where FINANCIALTRANSACTIONID = @PAYMENTID
                and POSTSTATUSCODE = 1
                and FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is null;

            with NEWLINEITEMS_CTE as
            (
                select FTLI.ID, EXT.TYPECODE
                from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
                    inner join dbo.REVENUESPLIT_EXT EXT on FTLI.ID = EXT.ID
                where FTLI.FINANCIALTRANSACTIONID = @PAYMENTID
                    and FTLI.POSTSTATUSCODE = 1
                    and FTLI.TYPECODE <> 1
            )
            update dbo.FINANCIALTRANSACTIONLINEITEM
            set REVERSEDLINEITEMID = NEWLINEITEMS_CTE.ID
            from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
                inner join @ADJUSTMENTLINEITEMS ADJ on FTLI.ID = ADJ.ID
                inner join NEWLINEITEMS_CTE on NEWLINEITEMS_CTE.TYPECODE = ADJ.TYPECODE
            where REVERSEDLINEITEMID is null;
        end

        fetch next from POSTPAYMENTS_CURSOR
        into @PAYMENTID, @PAYMENTPOSTSTATUS, @PAYMENTDEPOSITID;
    end;

    close POSTPAYMENTS_CURSOR;
    deallocate POSTPAYMENTS_CURSOR;

    exec dbo.USP_SALESORDER_GENERATETICKETS @ORDERID, @CHANGEAGENTID, @CURRENTDATE, 1

end