UFN_COMMUNICATIONLETTER_GETSCHEDULEINFO

CREATE function dbo.UFN_COMMUNICATIONLETTER_GETSCHEDULEINFO
(
    @SEGMENTATIONID uniqueidentifier
)
returns table
as 
return (
    select 
        C.ID
        , C.NAME
        , C.RUNSCHEDULED
        , case when exists (
                select top 1 
                    CL.ID
                from dbo.COMMUNICATIONLETTER CL
                    inner join dbo.COMMUNICATIONLETTERSELECTION CLS on CL.ID = CLS.COMMUNICATIONLETTERID
                where 
                    CL.ID = C.ID
                    and (
                        CL.CHANNELCODE = 0
                        or (
                            len(CL.EMAILSUBJECT) > 0  
                            and len(CL.EMAILFROMDISPLAYNAME) > 0 
                            and len(CL.EMAILFROMADDRESS) > 0 
                            and len(CL.EMAILCONTENTHTML) > 0
                        )
                    )
                )
                then 1
            else 0
        end as ISVALID
        , C.SEQUENCE
    from dbo.COMMUNICATIONLETTER C
    where C.SEGMENTATIONID = @SEGMENTATIONID
);