UFN_REVENUE_GETSPLITS_LISTINCURRENCY

            CREATE function dbo.UFN_REVENUE_GETSPLITS_LISTINCURRENCY
            (
                @REVENUEID uniqueidentifier,
                @CURRENCYID uniqueidentifier = null
            )
            returns table            
            as            
            return 
                (
                    select
                        LI.ID,
                        dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(LI.ID, @CURRENCYID) - sum(coalesce(WO.AMOUNT,0)) AMOUNT,
                        dbo.UFN_DESIGNATION_BUILDNAME(RSE.DESIGNATIONID) PURPOSE
                    from dbo.FINANCIALTRANSACTIONLINEITEM LI
                    inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = LI.ID
                    left join 
                        (select
                            INS.REVENUESPLITID,
                            case when dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(FT.PARENTID, @CURRENCYID) > 0 then cast(sum(dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTINCURRENCY(INSTALLMENTSPLITWRITEOFF.ID, @CURRENCYID)) * (cast(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(INS.REVENUESPLITID, @CURRENCYID) as decimal(30, 4))/ cast(dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(FT.PARENTID, @CURRENCYID) as decimal(30, 4))) as money) else null end AS [AMOUNT]
                        from dbo.WRITEOFF_EXT WRITEOFF
                        inner join dbo.FINANCIALTRANSACTION FT on FT.ID = WRITEOFF.ID
                        inner join dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
                        inner join dbo.INSTALLMENTSPLIT INS on INS.ID = INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID
                        where FT.TYPECODE = 20 and FT.DELETEDON is null
                        group by INS.REVENUESPLITID, FT.PARENTID) as WO on RSE.ID = WO.REVENUESPLITID
                    where LI.FINANCIALTRANSACTIONID = @REVENUEID and LI.TYPECODE != 1 and LI.DELETEDON is null
                    group by
                        LI.ID, RSE.DESIGNATIONID
                );