USP_DISBURSEMENTPROCESS_UPDATE_DISBURSEMENTPROCESSDISBURSEMENT_AMOUNT

CREATE procedure dbo.USP_DISBURSEMENTPROCESS_UPDATE_DISBURSEMENTPROCESSDISBURSEMENT_AMOUNT(
    @DISBURSEMENTPROCESSDISBURSEMENTID uniqueidentifier
    ,@CHANGEAGENTID uniqueidentifier
    ,@DATECHANGED datetime
)
as
begin
    declare @TRANSACTIONS as TABLE ( 
        APPLICATIONID uniqueidentifier
        ,CURRENTAPPLIEDAMOUNT money -- negative for credit memos

        ,APPLIEDAMOUNT money -- may be zeroed for credit memos that are overpaying

        ,SEQUENCE int
    );

    -- No more than one application per schedule for this check

    insert into @TRANSACTIONS (
        APPLICATIONID
        ,CURRENTAPPLIEDAMOUNT -- Current applied amount

        ,APPLIEDAMOUNT
        ,SEQUENCE
    )
    select
        FTA.ID            
        , case FT.TYPECODE when 101 then FTA.AMOUNT else -FTA.AMOUNT end
        , FTA.AMOUNT
        ,  row_number() over (order by FT.TYPECODE asc, FTS.[DUEDATE]) 
    from
        dbo.FINANCIALTRANSACTIONAPPLICATION as FTA            
            inner join dbo.FINANCIALTRANSACTIONSCHEDULE as FTS
                on FTA.FINANCIALTRANSACTIONSCHEDULEID = FTS.ID
            inner join dbo.FINANCIALTRANSACTION as FT
                on FT.ID = FTS.FINANCIALTRANSACTIONID
    where
        FTA.FINANCIALTRANSACTIONID = @DISBURSEMENTPROCESSDISBURSEMENTID
        and FTA.TYPECODE = 0;

    -- updates the applied amount for credit memos so we do not have checks below zero        

    update @TRANSACTIONS
    set APPLIEDAMOUNT = coalesce(
        (select SUM(CURRENTAPPLIEDAMOUNT) 
      from @TRANSACTIONS as T2 
      where T2.SEQUENCE < t.SEQUENCE
        ),0)
    from
        @TRANSACTIONS as t
    where
        CURRENTAPPLIEDAMOUNT < 0; -- Do this for credit memos only


    -- Clamp CMs to the max amounts

    update @TRANSACTIONS
        set APPLIEDAMOUNT = 0
    where 
        APPLIEDAMOUNT < 0;

    update @TRANSACTIONS
        set APPLIEDAMOUNT = -CURRENTAPPLIEDAMOUNT
    where 
        APPLIEDAMOUNT > -CURRENTAPPLIEDAMOUNT;

    -- Update CM Applications accordingly

    update FTA set
        AMOUNT = T.APPLIEDAMOUNT
    from  
        FINANCIALTRANSACTIONAPPLICATION as FTA
            inner join @TRANSACTIONS as T
                on T.APPLICATIONID = FTA.ID
    where T.CURRENTAPPLIEDAMOUNT < 0;

    -- Now update the DPD

    update FT set
            TRANSACTIONAMOUNT = APPLIED.TOTAL - APPLIED.DISCOUNT
            -- standard stuff

            , DATECHANGED = @DATECHANGED 
            , CHANGEDBYID = @CHANGEAGENTID 
        from dbo.FINANCIALTRANSACTION FT
            inner join (
            select 
                FT.ID [DPDID]
                ,SUM(
                    case when I.ID is not null 
                    then FTA.AMOUNT
                    else -FTA.AMOUNT
                    end
                ) [TOTAL]
                ,SUM(
                    case when FTA.TYPECODE = 1
                        then FTA.AMOUNT
                        else 0
                    end
                ) [DISCOUNT]
                from dbo.FINANCIALTRANSACTION FT
                    inner join dbo.FINANCIALTRANSACTIONAPPLICATION FTA on FTA.FINANCIALTRANSACTIONID = FT.ID
                    inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FTA.FINANCIALTRANSACTIONSCHEDULEID = FTS.ID
                    inner join dbo.FINANCIALTRANSACTION FTPAID on FTS.FINANCIALTRANSACTIONID = FTPAID.ID
                    left outer join dbo.INVOICE I on I.ID = FTPAID.ID
                    left outer join dbo.CREDITMEMO CM on CM.ID = FTPAID.ID
                where FT.TYPECODE = 255
                group by FT.ID
            ) as APPLIED on APPLIED.DPDID = FT.ID
        where FT.ID = @DISBURSEMENTPROCESSDISBURSEMENTID;
end