UFN_DATALIST_COMMUNICATIONLETTERS

create function dbo.UFN_DATALIST_COMMUNICATIONLETTERS
(
    @SEGMENTATIONID uniqueidentifier,
    @CHANNELCODE tinyint = null
)
returns @RESULTS table (
    SEGMENTATIONID uniqueidentifier,
    COMMUNICATIONLETTERID uniqueidentifier,
    SEQUENCE int,
    NAME nvarchar(100),
    CHANNEL nvarchar(50),
    MAILCONTENTDEFINED bit,
    EMAILCONTENTDEFINED bit,
    MOVEUP_ENABLED bit,
    MOVEDOWN_ENABLED bit,
    REMOVE_ENABLED bit
)
as 
begin
    declare @MAXSEQUENCE int;

    select 
        @MAXSEQUENCE = max(SEQUENCE)
    from dbo.COMMUNICATIONLETTER
    where SEGMENTATIONID = @SEGMENTATIONID;

    insert into @RESULTS (SEGMENTATIONID, COMMUNICATIONLETTERID, SEQUENCE, NAME, CHANNEL, MAILCONTENTDEFINED, EMAILCONTENTDEFINED, MOVEUP_ENABLED, MOVEDOWN_ENABLED, REMOVE_ENABLED)
        select    
            @SEGMENTATIONID as SEGMENTATIONID,
            COMMUNICATIONLETTER.ID,
            COMMUNICATIONLETTER.SEQUENCE,
            COMMUNICATIONLETTER.NAME,
            COMMUNICATIONLETTER.CHANNEL,
            case when len(COMMUNICATIONLETTER.MAILCONTENTHTML) > 0 then 1 else 0 end as MAILCONTENTDEFINED,
            case when len(COMMUNICATIONLETTER.EMAILCONTENTHTML) > 0 then 1 else 0 end as EMAILCONTENTDEFINED,
            case 
                when COMMUNICATIONLETTER.SEQUENCE > 1 then 1                
                else 0
            end as MOVEUP_ENABLED,
            case 
                when COMMUNICATIONLETTER.SEQUENCE < @MAXSEQUENCE then 1                
                else 0
            end as MOVEDOWN_ENABLED,
            case
                when @MAXSEQUENCE > 1 then 1
                else 0
            end as REMOVE_ENABLED
        from dbo.COMMUNICATIONLETTER
        left outer join dbo.MKTASKLADDER
            on COMMUNICATIONLETTER.MKTASKLADDERID = MKTASKLADDER.ID
        where SEGMENTATIONID = @SEGMENTATIONID
            and (@CHANNELCODE is null or COMMUNICATIONLETTER.CHANNELCODE = @CHANNELCODE)
        order by SEQUENCE asc;

    return;
end