UFN_SELECTIVECOMMUNICATIONLETTERS_GETLETTEROPTIONS_TOITEMLISTXML

CREATE function dbo.UFN_SELECTIVECOMMUNICATIONLETTERS_GETLETTEROPTIONS_TOITEMLISTXML
(
    @SEGMENTATIONID uniqueidentifier
)
returns xml
as 
begin
    return (
        select
            COMMUNICATIONLETTER.ID,
            COMMUNICATIONLETTER.SEQUENCE,
            COMMUNICATIONLETTER.OUTPUTTYPECODE,
            COMMUNICATIONLETTER.CHANNELCODE,
            COMMUNICATIONLETTER.NAME,
            cast(1 as bit) as RUNNOW,                -- All letters should default to Run now for manual runs

            coalesce(MAILCACHE.OFFERCOUNT, MAILACTIVE.QUANTITY, 0) as MAILCOUNT,
            coalesce(EMAILCACHE.OFFERCOUNT, EMAILACTIVE.QUANTITY, 0) as EMAILCOUNT,
            (
                case when
                    exists (
                        select ID
                        from dbo.COMMUNICATIONLETTERSELECTION
                        where COMMUNICATIONLETTERID = COMMUNICATIONLETTER.ID
                    )
                    then 1                        
                    else 0
                end
            ) as HASSELECTION,
            case
                when COMMUNICATIONLETTER.CHANNELCODE = 0 then 0                                        -- Mail


                when COMMUNICATIONLETTER.CHANNELCODE = 1 or COMMUNICATIONLETTER.CHANNELCODE = 2        -- Email, Mail and Email

                    then 
                        case
                            when len(COMMUNICATIONLETTER.EMAILSUBJECT) > 0  
                                    and len(COMMUNICATIONLETTER.EMAILFROMDISPLAYNAME) > 0 
                                    and len(COMMUNICATIONLETTER.EMAILFROMADDRESS) > 0 
                                    and len(COMMUNICATIONLETTER.EMAILCONTENTHTML) > 0
                                then 0
                            else 1
                        end                                
                else 0
            end as MISSINGREQUIREDEMAILFIELD
        from dbo.COMMUNICATIONLETTER
        left outer join dbo.MKTSEGMENTATIONSEGMENT MAILSEGMENT
            on COMMUNICATIONLETTER.MAILSEGMENTID = MAILSEGMENT.SEGMENTID
        left outer join dbo.MKTSEGMENTATIONSEGMENTCACHEINFO MAILCACHE
            on MAILSEGMENT.ID = MAILCACHE.SEGMENTID
        left outer join dbo.MKTSEGMENTATIONSEGMENTACTIVE MAILACTIVE
            on MAILSEGMENT.ID = MAILACTIVE.SEGMENTID
        left outer join dbo.MKTSEGMENTATIONSEGMENT EMAILSEGMENT
            on COMMUNICATIONLETTER.EMAILSEGMENTID = EMAILSEGMENT.SEGMENTID
        left outer join dbo.MKTSEGMENTATIONSEGMENTCACHEINFO EMAILCACHE
            on EMAILSEGMENT.ID = EMAILCACHE.SEGMENTID
        left outer join dbo.MKTSEGMENTATIONSEGMENTACTIVE EMAILACTIVE
            on EMAILSEGMENT.ID = EMAILACTIVE.SEGMENTID
        where COMMUNICATIONLETTER.SEGMENTATIONID = @SEGMENTATIONID
        order by COMMUNICATIONLETTER.SEQUENCE        
        for xml raw('ITEM'), type,elements,root('LETTEROPTIONS'),BINARY BASE64
    )
end