UFN_DESIGNATION_REVENUERECEIVED

            CREATE function dbo.UFN_DESIGNATION_REVENUERECEIVED
            (@STARTDATE datetime, @ENDDATE datetime)
            returns @REVENUEINFO table
            (
                DESIGNATIONID uniqueidentifier NOT NULL PRIMARY KEY CLUSTERED,
                TOTALRECEIVED money NOT NULL
            )
            as
            begin
                select @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),
                        @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

                insert @REVENUEINFO
                select D.ID,
                    sum(RS.AMOUNT)
                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.REVENUESPLIT RS on RS.DESIGNATIONID = D2.ID
                    inner join dbo.REVENUE R on RS.REVENUEID = R.ID
                where
                    (R.DATE >= @STARTDATE or @STARTDATE is null) and
                    (R.DATE <= @ENDDATE or @ENDDATE is null) and
                    R.TRANSACTIONTYPECODE = 0 and
                    (RS.APPLICATIONCODE in (0,3,6,7,10,13) or (RS.APPLICATIONCODE = 1 and RS.TYPECODE = 0)) and
                    (RS.APPLICATIONCODE <> 10 or RS.TYPECODE = 0)
                group by D.ID

            return

            end