UFN_COURSEGRADINGMARKINGCOLUMN_INVALIDTRANSLATIONTABLES

CREATE function dbo.UFN_COURSEGRADINGMARKINGCOLUMN_INVALIDTRANSLATIONTABLES
(
    @COURSEGRADINGMARKINGCOLUMNID uniqueidentifier = null,
    @VALUESALLOWED tinyint = null
)
returns table
as return 
    (    

(select TRANSLATIONTABLE.ID 
from dbo.COURSEGRADINGMARKINGCOLUMN
left outer join dbo.STUDENTMARKINGCOLUMNGRADE 
    on COURSEGRADINGMARKINGCOLUMN.ID = STUDENTMARKINGCOLUMNGRADE.COURSEGRADINGMARKINGCOLUMNID
left outer join dbo.TRANSLATIONTABLEGRADE existing 
    on COURSEGRADINGMARKINGCOLUMN.TRANSLATIONTABLEID = existing.TRANSLATIONTABLEID and STUDENTMARKINGCOLUMNGRADE.TRANSLATIONTABLEGRADEID = existing.ID
left outer join dbo.TRANSLATIONTABLE 
    on existing.TRANSLATIONTABLEID <> TRANSLATIONTABLE.ID
where (@VALUESALLOWED = 1 or @VALUESALLOWED = 0) and (STUDENTMARKINGCOLUMNGRADE.ID is not null) and 
    (STUDENTMARKINGCOLUMNGRADE.GRADEISBLANK = 0) and 
    STUDENTMARKINGCOLUMNGRADE.GRADETYPECODE = 1 and 
    COURSEGRADINGMARKINGCOLUMN.ID = @COURSEGRADINGMARKINGCOLUMNID and
    not exists(
        select * from dbo.TRANSLATIONTABLEGRADE selected 
        where existing.GRADE = selected.GRADE and 
            selected.TRANSLATIONTABLEID = TRANSLATIONTABLE.ID))

union

(select TRANSLATIONTABLE.ID 
from dbo.COURSEGRADINGMARKINGCOLUMN
inner join dbo.STUDENTMARKINGCOLUMNGRADE 
    on COURSEGRADINGMARKINGCOLUMN.ID = STUDENTMARKINGCOLUMNGRADE.COURSEGRADINGMARKINGCOLUMNID
inner join dbo.TRANSLATIONTABLE 
    on COURSEGRADINGMARKINGCOLUMN.TRANSLATIONTABLEID <> TRANSLATIONTABLE.ID and (STUDENTMARKINGCOLUMNGRADE.NUMERICGRADE > TRANSLATIONTABLE.HIGHESTSCOREALLOWED or STUDENTMARKINGCOLUMNGRADE.NUMERICGRADE < TRANSLATIONTABLE.LOWESTSCOREALLOWED)
where (@VALUESALLOWED = 2 or @VALUESALLOWED = 0) and (STUDENTMARKINGCOLUMNGRADE.ID is not null) and
    STUDENTMARKINGCOLUMNGRADE.GRADEISBLANK = 0 and 
    STUDENTMARKINGCOLUMNGRADE.GRADETYPECODE = 2 and 
    COURSEGRADINGMARKINGCOLUMN.ID = @COURSEGRADINGMARKINGCOLUMNID)
)