UFN_RECOGNITIONCREDIT_GETADJUSTEDAMOUNT

create function dbo.UFN_RECOGNITIONCREDIT_GETADJUSTEDAMOUNT(
  @RECOGNITIONCREDITADJUSTMENTCODE tinyint,
  @REVENUEID uniqueidentifier,
  @WRITEOFFTOTALAMOUNT money
)
returns xml
as begin
declare @RECOGNITIONCREDITS xml;
declare @PLEDGEAMOUNTMINUSWRITEOFFS money;

select 
    @PLEDGEAMOUNTMINUSWRITEOFFS = (
        FINANCIALTRANSACTION.TRANSACTIONAMOUNT - coalesce((
                select sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT)
                from dbo.INSTALLMENTSPLITWRITEOFF
                inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                where INSTALLMENTSPLIT.PLEDGEID = @REVENUEID
                ), 0)
        )
from dbo.FINANCIALTRANSACTION
inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FINANCIALTRANSACTION.CONSTITUENTID) NF
left join dbo.CURRENCYEXCHANGERATE on FINANCIALTRANSACTION.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
where FINANCIALTRANSACTION.ID = @REVENUEID
    and FINANCIALTRANSACTION.DELETEDON is null;

set @RECOGNITIONCREDITADJUSTMENTCODE = COALESCE(@RECOGNITIONCREDITADJUSTMENTCODE, 3);

-- RECOGNITIONCREDITADJUSTMENTCODES.ReduceRecognitionCreditsByProportionalAmount 

if (@RECOGNITIONCREDITADJUSTMENTCODE = 0)
begin
    set @RECOGNITIONCREDITS = (
            select REVENUERECOGNITION.ID
                ,FINANCIALTRANSACTIONLINEITEM.ID as REVENUESPLITID
                ,dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT_EXT.DESIGNATIONID) as DESIGNATIONNAME
                ,CONSTITUENT.name as CONSTITUENTNAME
                ,REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONCREDITTYPE
                ,REVENUERECOGNITION.EFFECTIVEDATE
                ,REVENUERECOGNITION.AMOUNT as ORIGINALAMOUNT
                ,(REVENUERECOGNITION.AMOUNT * (@PLEDGEAMOUNTMINUSWRITEOFFS - @WRITEOFFTOTALAMOUNT)) / @PLEDGEAMOUNTMINUSWRITEOFFS as ADJUSTEDAMOUNT
                ,REVENUERECOGNITION.BASECURRENCYID
            from dbo.FINANCIALTRANSACTION
            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            inner join dbo.REVENUERECOGNITION on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
            inner join dbo.CONSTITUENT on REVENUERECOGNITION.CONSTITUENTID = CONSTITUENT.ID
            left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
                and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
            order by DESIGNATIONNAME
                ,REVENUESPLITID
            for xml raw('ITEM')
                ,type
                ,elements
                ,root('RECOGNITIONCREDITS')
                ,binary BASE64
            );
end

-- RECOGNITIONCREDITADJUSTMENTCODES.ReduceRecognitionCreditsByWriteOffAmount 

if (@RECOGNITIONCREDITADJUSTMENTCODE = 1)
begin
    declare @PRORATEDAMOUNTS table (
        ID uniqueidentifier
        ,AMOUNT money
        )
    -- adjust splits for new amount

    declare @CURRENTAMOUNT decimal(30, 5);
    declare @WEIGHT decimal(30, 10);
    declare @tempID uniqueidentifier;
    declare @ORIGINALAMOUNT decimal(30, 5);

  SELECT @ORIGINALAMOUNT=COALESCE(TRANSACTIONAMOUNT,0) FROM dbo.FINANCIALTRANSACTION WHERE ID=@REVENUEID;

    declare @NEWAMOUNT decimal(30, 5) = COALESCE (
        @WRITEOFFTOTALAMOUNT
        ,0
        );

    -- Load return table with current amounts to prorate

    insert into @PRORATEDAMOUNTS (
        AMOUNT
        ,ID
        )
    select FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT [AMOUNT]
          ,FINANCIALTRANSACTIONLINEITEM.ID
                from dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

    -- Loop through and calculate new split amounts

    declare AMOUNTSTOPRORATECURSOR cursor local fast_forward
    for
    select ID
        ,AMOUNT
    from @PRORATEDAMOUNTS;

    open AMOUNTSTOPRORATECURSOR;

    fetch next
    from AMOUNTSTOPRORATECURSOR
    into @tempID
        ,@WEIGHT;

    while @@FETCH_STATUS = 0
    begin
        if @ORIGINALAMOUNT <> 0
            set @CURRENTAMOUNT = (@WEIGHT / @ORIGINALAMOUNT) * @NEWAMOUNT;
        else
            set @CURRENTAMOUNT = 0;

        update @PRORATEDAMOUNTS
        set AMOUNT = @CURRENTAMOUNT
        where ID = @tempID;

        set @NEWAMOUNT = @NEWAMOUNT + @CURRENTAMOUNT;
        set @ORIGINALAMOUNT = @ORIGINALAMOUNT + @WEIGHT;

        fetch next
        from AMOUNTSTOPRORATECURSOR
        into @tempID
            ,@WEIGHT;
    end

    close AMOUNTSTOPRORATECURSOR;

    deallocate AMOUNTSTOPRORATECURSOR;

    set @RECOGNITIONCREDITS = (
            select REVENUERECOGNITION.ID
                ,FINANCIALTRANSACTIONLINEITEM.ID as REVENUESPLITID
                ,dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT_EXT.DESIGNATIONID) as DESIGNATIONNAME
                ,CONSTITUENT.name as CONSTITUENTNAME
                ,REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONCREDITTYPE
                ,REVENUERECOGNITION.EFFECTIVEDATE
                ,REVENUERECOGNITION.AMOUNT as ORIGINALAMOUNT
                ,case when REVENUERECOGNITION.AMOUNT > COALESCE(P.AMOUNT, 0) then REVENUERECOGNITION.AMOUNT - COALESCE(P.AMOUNT, 0) else 0 end as ADJUSTEDAMOUNT
                ,REVENUERECOGNITION.BASECURRENCYID
            from dbo.FINANCIALTRANSACTION
            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            inner join dbo.REVENUERECOGNITION on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
            inner join dbo.CONSTITUENT on REVENUERECOGNITION.CONSTITUENTID = CONSTITUENT.ID
            left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
            left join @PRORATEDAMOUNTS P on FINANCIALTRANSACTIONLINEITEM.ID = P.ID
            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
                and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
            order by DESIGNATIONNAME
                ,REVENUESPLITID
            for xml raw('ITEM')
                ,type
                ,elements
                ,root('RECOGNITIONCREDITS')
                ,binary BASE64
            );
end

-- RECOGNITIONCREDITADJUSTMENTCODES.DoNotAdjustRecognitionCredits 

if (@RECOGNITIONCREDITADJUSTMENTCODE = 3)
begin
    set @RECOGNITIONCREDITS = (
         select ID
                ,REVENUESPLITID
                ,DESIGNATIONNAME
                ,CONSTITUENTNAME
                ,RECOGNITIONCREDITTYPE
                ,EFFECTIVEDATE
                ,ORIGINALAMOUNT
                ,ADJUSTEDAMOUNT
                ,BASECURRENCYID
            from dbo.UFN_PLEDGEWRITEOFFBATCH_GETRECOGNITIONCREDITS(NULL, @REVENUEID) RECOGNITIONCREDITS
            order by DESIGNATIONNAME, REVENUESPLITID, CONSTITUENTNAME
            for xml raw('ITEM')
                ,type
                ,elements
                ,root('RECOGNITIONCREDITS')
                ,binary BASE64
            );
end

    return @RECOGNITIONCREDITS
end