UFN_ADJUSTMENTHISTORY_GETADJUSTEDSTREAMS_FOR_SPECIFIC_LINEITEMS

create function [dbo].[UFN_ADJUSTMENTHISTORY_GETADJUSTEDSTREAMS_FOR_SPECIFIC_LINEITEMS]
(
    @ADJUSTMENTID uniqueidentifier
    ,@LINEITEMS UDT_GENERICID readonly
    ,@CURRENCYCODE tinyint
)
returns nvarchar(max)
as
begin
    declare @RESULT nvarchar(max);
    declare @REVENUEID uniqueidentifier;
    declare @DETAILTYPE nvarchar(25);
    declare @DETAILTYPECODE tinyint;
                    declare @DETAILSPLITTYPECODE tinyint;
    declare @DETAILAPPLICATION nvarchar(30);
    declare @DETAILAPPLICATIONCODE tinyint;
    declare @AMOUNT money;
    declare @REVENUECATEGORY nvarchar(100);
    declare @APPLICATIONSTRING nvarchar(100);

    set @RESULT = N'';
    set @APPLICATIONSTRING = N'';

    select
        @REVENUEID = REVENUEID
    from dbo.ADJUSTMENT 
    where ID = @ADJUSTMENTID;

    if @REVENUEID is null
        return @RESULT;

    declare DETAIL_CURSOR cursor local fast_forward for
        select
            FINANCIALTRANSACTION.TYPE
            ,FINANCIALTRANSACTION.TYPECODE
            ,REVENUESPLIT_EXT.TYPECODE
            ,REVENUESPLIT_EXT.APPLICATION
            ,REVENUESPLIT_EXT.APPLICATIONCODE
            ,case @CURRENCYCODE when 0 then FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT when 2 then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT else FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT end
            ,GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME
        from @LINEITEMS LI
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on LI.ID = FINANCIALTRANSACTIONLINEITEM.ID
        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
        left join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = FINANCIALTRANSACTIONLINEITEM.ID
        left join dbo.GLREVENUECATEGORYMAPPING on REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID = GLREVENUECATEGORYMAPPING.ID
        where FINANCIALTRANSACTION.ID = @REVENUEID
            and FINANCIALTRANSACTION.DELETEDON is null
            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
            and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

    open DETAIL_CURSOR;
        fetch next from DETAIL_CURSOR into @DETAILTYPE, @DETAILTYPECODE, @DETAILSPLITTYPECODE, @DETAILAPPLICATION, @DETAILAPPLICATIONCODE, @AMOUNT, @REVENUECATEGORY;
        while @@FETCH_STATUS = 0
        begin
            select @APPLICATIONSTRING = 
            case
                when @DETAILTYPECODE <> 0 then @DETAILTYPE
                when @DETAILAPPLICATIONCODE = 0 and @DETAILSPLITTYPECODE <> 2 then @DETAILAPPLICATION
                                            when @DETAILAPPLICATIONCODE = 0 and @DETAILSPLITTYPECODE = 2 then ' Recurring membership'
                else @DETAILAPPLICATION + ' Payment'
            end

            if len(@REVENUECATEGORY) = 0 or @REVENUECATEGORY is null
                set @RESULT = @RESULT + @APPLICATIONSTRING + ' (' + cast(@AMOUNT as nvarchar) + ')' + char(13) + char(10);
            else
                set @RESULT = @RESULT + @APPLICATIONSTRING + ' (' + cast(@AMOUNT as nvarchar) + ')' + ' [' + @REVENUECATEGORY + ']' + char(13) + char(10);

            fetch next from DETAIL_CURSOR into @DETAILTYPE, @DETAILTYPECODE, @DETAILSPLITTYPECODE, @DETAILAPPLICATION, @DETAILAPPLICATIONCODE, @AMOUNT, @REVENUECATEGORY;
        end
    deallocate DETAIL_CURSOR;

    return @RESULT;
end