UFN_REVENUE_GETORDEREDDESIGNATIONS_TOP2_BULK

CREATE function dbo.UFN_REVENUE_GETORDEREDDESIGNATIONS_TOP2_BULK()
returns table
as
return
    with REVENUESPLIT_CTE as (
        select
            FINANCIALTRANSACTIONLINEITEM.ID,
            FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
            REVENUESPLIT_EXT.DESIGNATIONID,
            FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
            FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT,
            FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT
        from dbo.FINANCIALTRANSACTIONLINEITEM
        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
        where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
    )
    select
        FINANCIALTRANSACTION.ID REVENUEID,
        DESIGNATION1.NAME DESIGNATION1NAME,
        case len(DESIGNATION1.VANITYNAME)
            when 0 
                then dbo.UFN_DESIGNATION_GETNODENAME(DESIGNATION1.ID) 
            else DESIGNATION1.VANITYNAME 
        end as DESIGNATION1PUBLICNAME,
        DESIGNATIONS.SPLIT1AMOUNT DESIGNATION1AMOUNT, 
        DESIGNATION2.NAME DESIGNATION2NAME,
        case len(DESIGNATION2.VANITYNAME)
            when 0 
                then dbo.UFN_DESIGNATION_GETNODENAME(DESIGNATION2.ID) 
            else DESIGNATION2.VANITYNAME 
        end as DESIGNATION2PUBLICNAME,
        DESIGNATIONS.SPLIT2AMOUNT DESIGNATION2AMOUNT,
        DESIGNATIONS.SPLIT1TRANSACTIONAMOUNT DESIGNATION1TRANSACTIONAMOUNT,
        DESIGNATIONS.SPLIT1ORGANIZATIONAMOUNT DESIGNATION1ORGANIZATIONAMOUNT,
        DESIGNATIONS.SPLIT2TRANSACTIONAMOUNT DESIGNATION2TRANSACTIONAMOUNT,
        DESIGNATIONS.SPLIT2ORGANIZATIONAMOUNT DESIGNATION2ORGANIZATIONAMOUNT
    from dbo.FINANCIALTRANSACTION
        cross apply(
            select top 1
                SPLIT1.ID SPLIT1ID, SPLIT1.DESIGNATIONID SPLIT1DESIGNATIONID, SPLIT1.BASEAMOUNT SPLIT1AMOUNT, SPLIT1.TRANSACTIONAMOUNT SPLIT1TRANSACTIONAMOUNT, SPLIT1.ORGAMOUNT SPLIT1ORGANIZATIONAMOUNT,
                SPLIT2.ID SPLIT2ID, SPLIT2.DESIGNATIONID SPLIT2DESIGNATIONID, SPLIT2.BASEAMOUNT SPLIT2AMOUNT, SPLIT2.TRANSACTIONAMOUNT SPLIT2TRANSACTIONAMOUNT, SPLIT2.ORGAMOUNT SPLIT2ORGANIZATIONAMOUNT
            from REVENUESPLIT_CTE as SPLIT1
            left join REVENUESPLIT_CTE as SPLIT2 on SPLIT1.FINANCIALTRANSACTIONID = SPLIT2.FINANCIALTRANSACTIONID and SPLIT1.ID != SPLIT2.ID
            where SPLIT1.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
            order by SPLIT1.BASEAMOUNT desc, SPLIT2.BASEAMOUNT desc
        ) DESIGNATIONS
        inner join dbo.DESIGNATION DESIGNATION1 on DESIGNATION1.ID = DESIGNATIONS.SPLIT1DESIGNATIONID
        left join dbo.DESIGNATION DESIGNATION2 on DESIGNATION2.ID = DESIGNATIONS.SPLIT2DESIGNATIONID
        where (FINANCIALTRANSACTION.TYPECODE < 10 or FINANCIALTRANSACTION.TYPECODE = 15)
        and FINANCIALTRANSACTION.DELETEDON is null