UFN_DESIGNATION_NEWCOMMITMENTREVENUEINCURRENCY

            CREATE function dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEINCURRENCY
            (
                @STARTDATE datetime, 
                @ENDDATE datetime, 
                @CURRENCYID uniqueidentifier = null,
                @DESIGNATIONID uniqueidentifier = null
            )
            returns @REVENUEINFO table
            (
                DESIGNATIONID uniqueidentifier NOT NULL PRIMARY KEY CLUSTERED,
                TOTALNEWCOMMITMENT money NOT NULL,
                TOTALPAID money NOT NULL,
                TOTALWRITEOFFS money NOT NULL
            )
            AS
            BEGIN
                select 
                    @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),
                    @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                declare @DECIMALDIGITS tinyint;
                declare @ROUNDINGTYPECODE tinyint;

                if @CURRENCYID is null
                    set @CURRENCYID = @ORGANIZATIONCURRENCYID;

                select
                    @DECIMALDIGITS = DECIMALDIGITS,
                    @ROUNDINGTYPECODE = ROUNDINGTYPECODE
                from
                    dbo.UFN_CURRENCY_GETPROPERTIES(@CURRENCYID)

                insert @REVENUEINFO
                select 
                    D.ID,
                    isnull(sum(SPLIT.AMOUNTINCURRENCY),0),
                    (
                        select
                            isnull(sum(PAYSPLIT.AMOUNTINCURRENCY), 0) 
                        from 
                            dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) PAYSPLIT 
                            inner join dbo.FINANCIALTRANSACTION PAY on PAYSPLIT.REVENUEID = PAY.ID
                            inner join dbo.DESIGNATION DP on PAYSPLIT.DESIGNATIONID = DP.ID
                        where (PAY.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and PAY.DELETEDON is null) and
                        PAYSPLIT.ID in (SELECT IP.PAYMENTID
                        from 
                            DBO.INSTALLMENTPAYMENT IP
                            inner join dbo.FINANCIALTRANSACTION PLEDGE on IP.PLEDGEID = PLEDGE.ID
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on PLEDGE.ID = LI.FINANCIALTRANSACTIONID
                            inner join dbo.REVENUESPLIT_EXT PLEDGESPLIT on PLEDGESPLIT.ID = LI.ID
                            inner join dbo.DESIGNATION DPL on PLEDGESPLIT.DESIGNATIONID = DPL.ID
                        where (PLEDGE.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and LI.DELETEDON is null and LI.TYPECODE != 1) and
                            (cast(PLEDGE.DATE as datetime) >= @STARTDATE or @STARTDATE is null) and
                            (cast(PLEDGE.DATE as datetime) <= @ENDDATE or @ENDDATE is null) and 
                            (D.DESIGNATIONLEVEL1ID = DPL.DESIGNATIONLEVEL1ID and
                            (D.DESIGNATIONLEVEL2ID = DPL.DESIGNATIONLEVEL2ID or D.DESIGNATIONLEVEL2ID is null) and
                            (D.DESIGNATIONLEVEL3ID = DPL.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null) and
                            (D.DESIGNATIONLEVEL4ID = DPL.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null) and
                            (D.DESIGNATIONLEVEL5ID = DPL.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null)) and
                            PLEDGESPLIT.APPLICATIONCODE in (2,8)
                        )
                        and
                        (D.DESIGNATIONLEVEL1ID = DP.DESIGNATIONLEVEL1ID
                                and (D.DESIGNATIONLEVEL2ID = DP.DESIGNATIONLEVEL2ID or D.DESIGNATIONLEVEL2ID is null)
                                and (D.DESIGNATIONLEVEL3ID = DP.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null)
                                and (D.DESIGNATIONLEVEL4ID = DP.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null)
                                and (D.DESIGNATIONLEVEL5ID = DP.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null)) and 
                        (cast(PAY.DATE as datetime) >= @STARTDATE) and
                        (cast(PAY.DATE as datetime) <= @ENDDATE)
                    ),
                    (
                        select 
                            isnull(sum(WRITEOFFSPLIT.AMOUNTINCURRENCY), 0) 
                        from 
                            dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) WRITEOFFSPLIT
                            inner join dbo.DESIGNATION WRITEOFFDESIGNATION on WRITEOFFSPLIT.DESIGNATIONID = WRITEOFFDESIGNATION.ID
                            inner join dbo.FINANCIALTRANSACTION WRITEOFF on WRITEOFFSPLIT.WRITEOFFID = WRITEOFF.ID
                            inner join dbo.FINANCIALTRANSACTION WRITEOFFREVENUE on WRITEOFF.PARENTID = WRITEOFFREVENUE.ID
                        where 
                            (WRITEOFF.TYPECODE = 20 and WRITEOFF.DELETEDON is null) and WRITEOFFREVENUE.DELETEDON is null and
                            (cast(WRITEOFFREVENUE.DATE as datetime) >= @STARTDATE or @STARTDATE is null) and
                            (cast(WRITEOFFREVENUE.DATE as datetime) <= @ENDDATE or @ENDDATE is null) and
                            (D.DESIGNATIONLEVEL1ID = WRITEOFFDESIGNATION.DESIGNATIONLEVEL1ID and
                            (D.DESIGNATIONLEVEL2ID = WRITEOFFDESIGNATION.DESIGNATIONLEVEL2ID or D.DESIGNATIONLEVEL2ID is null) and
                            (D.DESIGNATIONLEVEL3ID = WRITEOFFDESIGNATION.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null) and
                            (D.DESIGNATIONLEVEL4ID = WRITEOFFDESIGNATION.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null) and
                            (D.DESIGNATIONLEVEL5ID = WRITEOFFDESIGNATION.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null)) and
                            (WRITEOFFREVENUE.TYPECODE in (1,6))
                )
                from 
                    dbo.DESIGNATION D
                    inner join dbo.DESIGNATION D2 on (D.DESIGNATIONLEVEL1ID = D2.DESIGNATIONLEVEL1ID
                        and (D.DESIGNATIONLEVEL2ID = D2.DESIGNATIONLEVEL2ID or D.DESIGNATIONLEVEL2ID is null)
                        and (D.DESIGNATIONLEVEL3ID = D2.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null)
                        and (D.DESIGNATIONLEVEL4ID = D2.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null)
                        and (D.DESIGNATIONLEVEL5ID = D2.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null))
                    inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) SPLIT on D2.ID = SPLIT.DESIGNATIONID
                where 
                    (cast(SPLIT.DATE as datetime) >= @STARTDATE) and
                    (cast(SPLIT.DATE as datetime) <= @ENDDATE) and
                    (SPLIT.TRANSACTIONTYPECODE in (1,6) and SPLIT.SPLITDELETEDON is null and SPLIT.REVENUEDELETEDON is null) and
                    (@DESIGNATIONID is null or @DESIGNATIONID = D.ID)
                group by D.ID, D.DESIGNATIONLEVEL1ID, D.DESIGNATIONLEVEL2ID, D.DESIGNATIONLEVEL3ID, D.DESIGNATIONLEVEL4ID, D.DESIGNATIONLEVEL5ID
                return
            end