USP_DATAFORMTEMPLATE_EDIT_DISBURSEMENT_TRANSACTION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_DISBURSEMENT_TRANSACTION (
    @ID uniqueidentifier
    ,@CHANGEAGENTID uniqueidentifier = null
    ,@DISBURSEMENT xml
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    declare @TempTbl table(
        [ID] uniqueidentifier
        ,[INCLUDE] bit
        ,[AMOUNTTOPAY] money
    );

    begin try
        -- handle updating the data


    insert into @TempTbl select
            [ID]
            ,[INCLUDE]
            ,[AMOUNTTOPAY]
        from dbo.UFN_DISBURSEMENTTRANSACTIONS_FROMITEMLISTXML(@DISBURSEMENT);

    -- We only update the Amount when we are included.

    update dbo.FINANCIALTRANSACTIONAPPLICATION set
      AMOUNT = case 
                 when temp.INCLUDE = 1 then temp.AMOUNTTOPAY
                 else 0 
               end
            -- standard stuff

            , DATECHANGED = @CURRENTDATE 
            , CHANGEDBYID = @CHANGEAGENTID 
    from dbo.FINANCIALTRANSACTIONAPPLICATION FTA
      inner join @TempTbl as temp on FTA.ID = temp.ID
    where 
        (FTA.ID <> temp.ID) or
        (FTA.ID is null and temp.ID is not null) or
        (FTA.ID is not null and temp.ID is null) or
        (FTA.AMOUNT <> temp.AMOUNTTOPAY and temp.INCLUDE = 1) or
        (FTA.AMOUNT is null and temp.AMOUNTTOPAY is not null and temp.INCLUDE = 1) or
        (FTA.AMOUNT is not null and temp.AMOUNTTOPAY is null and temp.INCLUDE = 1) or 
        (FTA.AMOUNT <> 0 and temp.INCLUDE = 0)

        -- Adjust Discount amounts.

        update dbo.FINANCIALTRANSACTIONAPPLICATION set
            AMOUNT = case 
                        when (DERIVED.[DIFFERENCE]) = DERIVED.DISCOUNTAMOUNT 
                            then DERIVED.DISCOUNTAMOUNT
                        else 0 
                    end
            -- standard stuff

            , DATECHANGED = @CURRENTDATE 
            , CHANGEDBYID = @CHANGEAGENTID 
        from dbo.FINANCIALTRANSACTIONAPPLICATION as FTA
            inner join (
            select 
                FTD.ID
                ,I.DISCOUNTAMOUNT
                ,FTS.AMOUNT [SCHEDULEDAMOUNT]
                ,FTA.AMOUNT [APPLIEDAMOUNT]
                ,FTS.AMOUNT - FTA.AMOUNT [DIFFERENCE]
            from dbo.FINANCIALTRANSACTIONAPPLICATION as FTA
                inner join @TempTbl as temp on FTA.ID = temp.ID
                inner join dbo.FINANCIALTRANSACTIONSCHEDULE as FTS on FTA.FINANCIALTRANSACTIONSCHEDULEID = FTS.ID
                inner join dbo.FINANCIALTRANSACTION as FTPAID on FTS.FINANCIALTRANSACTIONID = FTPAID.ID
                inner join dbo.INVOICE as I on I.ID = FTPAID.ID
                inner join dbo.FINANCIALTRANSACTIONAPPLICATION as FTD on FTS.ID = FTD.FINANCIALTRANSACTIONSCHEDULEID and FTD.TYPECODE = 1
            where
                I.DISCOUNTAMOUNT > 0 -- has a discount

            ) as DERIVED on FTA.ID = DERIVED.ID
        where 
            (FTA.AMOUNT <> 0 and DERIVED.[DIFFERENCE] <> DERIVED.DISCOUNTAMOUNT) -- where discount isnt 0 and the difference isnt the discount

            or (FTA.AMOUNT = 0 and DERIVED.[DIFFERENCE] = DERIVED.DISCOUNTAMOUNT) -- or the amount isnt the discount amount and the difference is the discount


        -- Adjust DPD

        exec dbo.USP_DISBURSEMENTPROCESS_UPDATE_DISBURSEMENTPROCESSDISBURSEMENT_AMOUNT 
            @DISBURSEMENTPROCESSDISBURSEMENTID = @ID
            , @CHANGEAGENTID = @CHANGEAGENTID
            , @DATECHANGED = @CURRENTDATE;

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;