UFN_REVENUE_GETAPPLICATIONDETAILSFORSEARCHXML

            CREATE function dbo.UFN_REVENUE_GETAPPLICATIONDETAILSFORSEARCHXML
(
    @REVENUEID uniqueidentifier
)
returns xml            
as
begin

return (
select *
from 
(
    select
        coalesce(rs.[APPLICATION], N'') [APPLICATION]
        , ftli.TYPECODE
        , ftli.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT
        , c.ISO4217
        , c.DECIMALDIGITS
        , c.CURRENCYSYMBOL
        , c.SYMBOLDISPLAYSETTINGCODE    
    from dbo.FINANCIALTRANSACTIONLINEITEM ftli
        left join dbo.REVENUESPLIT_EXT rs on rs.id = ftli.id
        inner join dbo.FINANCIALTRANSACTION ft on ftli.FINANCIALTRANSACTIONID = ft.ID    
        inner join dbo.CURRENCY c on c.ID = ft.TRANSACTIONCURRENCYID
    where
        ftli.FINANCIALTRANSACTIONID = @REVENUEID
        and ftli.TYPECODE <> 5 -- not Discount

        and ftli.DELETEDON is null
        and (
            rs.APPLICATIONCODE is null
            or rs.APPLICATIONCODE <> 10  -- not Order

            or ftli.VISIBLE = 1  -- Currently, group sales creates line items that aren't visible to the user

        )

    union all

    select 
        ftli.[TYPE] as [APPLICATION]
        , ftli.TYPECODE
        , SUM(ftli.TRANSACTIONAMOUNT) TRANSACTIONAMOUNT
        , c.ISO4217
        , c.DECIMALDIGITS
        , c.CURRENCYSYMBOL
        , c.SYMBOLDISPLAYSETTINGCODE    
    from dbo.FINANCIALTRANSACTIONLINEITEM ftli
        inner join dbo.FINANCIALTRANSACTION ft on ftli.FINANCIALTRANSACTIONID = ft.ID    
        inner join dbo.CURRENCY c on c.ID = ft.TRANSACTIONCURRENCYID
    where 
        ftli.TYPECODE = 5 -- Discount

        and ftli.FINANCIALTRANSACTIONID = @REVENUEID
        and ftli.DELETEDON is null
    group by
        ftli.TYPECODE
        , ftli.[TYPE]
        , ft.TRANSACTIONCURRENCYID
        , c.ISO4217
        , c.DECIMALDIGITS
        , c.CURRENCYSYMBOL
        , c.SYMBOLDISPLAYSETTINGCODE
) as s
order by s.TYPECODE asc, s.TRANSACTIONAMOUNT desc
for xml raw('ITEM'),type,elements,root('APPLICATIONDETAILS'),BINARY BASE64);

end