UFN_COMMUNICATION_GETLETTERS

CREATE function dbo.UFN_COMMUNICATION_GETLETTERS
(
    @COMMUNICATIONID uniqueidentifier,
    @COMMUNICATIONTYPECODE tinyint
)
returns table as 
    return (
        -- @COMMINICATIONTYPECODE - enum from MKTSegmentation table and CommunicationLetter table

        --        0 - Direct marketing effort

        --        1 - Appeal mailing

        --        2 - Event invitation

        --        3 - Acknowledgement


        select
            COMMUNICATIONLETTER.ID,            
            COMMUNICATIONLETTER.SEQUENCE,
            COMMUNICATIONLETTER.NAME,
            COMMUNICATIONLETTER.CHANNELCODE,
            COMMUNICATIONLETTER.CHANNELPREFERENCECODE,
            COMMUNICATIONLETTER.INCLUDEINACTIVE,
            COMMUNICATIONLETTER.MKTASKLADDERID,
            coalesce(MAILPACKAGE.EXPORTDEFINITIONID, dbo.UFN_COMMUNICATIONLETTER_GETDEFAULTEXPORTDEFINITIONID(@COMMUNICATIONTYPECODE, 0)) as MAILEXPORTDEFINITIONID,
            COMMUNICATIONLETTER.MAILCONTENTHTML,
            coalesce(EMAILPACKAGE.EXPORTDEFINITIONID, dbo.UFN_COMMUNICATIONLETTER_GETDEFAULTEXPORTDEFINITIONID(@COMMUNICATIONTYPECODE, 1)) as EMAILEXPORTDEFINITIONID,
            COMMUNICATIONLETTER.EMAILCONTENTHTML,
            COMMUNICATIONLETTER.EMAILSUBJECT,
            COMMUNICATIONLETTER.EMAILFROMADDRESS,
            COMMUNICATIONLETTER.EMAILFROMDISPLAYNAME,
            COMMUNICATIONLETTER.EMAILREPLYTOADDRESS,
            COMMUNICATIONLETTER.PAPERSIZECODE,
            COMMUNICATIONLETTER.MARGINTOP,
            COMMUNICATIONLETTER.MARGINBOTTOM,
            COMMUNICATIONLETTER.MARGINLEFT,
            COMMUNICATIONLETTER.MARGINRIGHT,            
            COMMUNICATIONLETTER.MAILPACKAGEID,
            COMMUNICATIONLETTER.MAILSEGMENTID,
            COMMUNICATIONLETTER.EMAILPACKAGEID,
            COMMUNICATIONLETTER.EMAILSEGMENTID,            
            cast(dbo.UFN_COMMUNICATIONLETTER_GETINCLUDEDSELECTIONS_TOITEMLISTXML(COMMUNICATIONLETTER.ID) as nvarchar(max)) as SELECTIONSXML,
            cast(dbo.UFN_COMMUNICATIONLETTER_GETEXCLUDEDSELECTIONS_TOITEMLISTXML(COMMUNICATIONLETTER.ID) as nvarchar(max)) as EXCLUDEDSELECTIONSXML,
            cast(dbo.UFN_COMMUNICATIONLETTER_GETEXCLUDEDSOLICITCODES_TOITEMLISTXML(COMMUNICATIONLETTER.ID) as nvarchar(max)) as EXCLUDEDSOLICITCODESXML,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.EXCLUDEBASEDONRECENTCOMMUNICATION,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.NUMRECENTCOMMUNICATIONPERIODS,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.RECENTCOMMUNICATIONPERIODTYPECODE,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.NUMTOTALCOMMUNICATIONSINPASTYEAR,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.EXCLUDEBASEDONRECENTGIVING,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.NUMRECENTGIVINGPERIODS,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.RECENTGIVINGPERIODTYPECODE,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.EXCLUDEBASEDONTOTALGIVINGINPASTYEAR,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.TOTALREVENUEAMOUNTINPASTYEAR,           
            cast(dbo.UFN_COMMUNICATIONLETTERACTIVITYEXCLUSION_GETCOMMUNICATIONTYPES_TOITEMLISTXML(COMMUNICATIONLETTERACTIVITYEXCLUSIONS.ID) as nvarchar(max)) as COMMUNICATIONTYPESXML,
            COMMUNICATIONLETTER.OUTPUTTYPECODE,
            COMMUNICATIONLETTER.RUNNOW,
            COMMUNICATIONLETTER.RUNSCHEDULED
        from dbo.COMMUNICATIONLETTER
        left outer join dbo.APPEALMAILINGSETUP
            on COMMUNICATIONLETTER.SEGMENTATIONID = APPEALMAILINGSETUP.ID
        left outer join dbo.COMMUNICATIONLETTERACTIVITYEXCLUSIONS
            on COMMUNICATIONLETTER.ID = COMMUNICATIONLETTERACTIVITYEXCLUSIONS.COMMUNICATIONLETTERID
        left outer join dbo.MKTPACKAGE MAILPACKAGE
            on COMMUNICATIONLETTER.MAILPACKAGEID = MAILPACKAGE.ID
        left outer join dbo.MKTPACKAGE EMAILPACKAGE
            on COMMUNICATIONLETTER.EMAILPACKAGEID = EMAILPACKAGE.ID

        where SEGMENTATIONID = @COMMUNICATIONID
    )