USP_DATAFORMTEMPLATE_VIEW_COMMUNICATIONLETTER

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_COMMUNICATIONLETTER
(
    @COMMUNICATIONLETTERID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier
)
as
begin
    set nocount on;

    select
        COMMUNICATIONLETTER.SEQUENCE,
        COMMUNICATIONLETTER.NAME,
        COMMUNICATIONLETTER.CHANNELCODE,
        COMMUNICATIONLETTER.CHANNELPREFERENCECODE,
        COMMUNICATIONLETTER.MKTASKLADDERID,
        COMMUNICATIONLETTER.INCLUDEINACTIVE,
        coalesce(MAILPACKAGE.EXPORTDEFINITIONID, dbo.UFN_COMMUNICATIONLETTER_GETDEFAULTEXPORTDEFINITIONID(COMMUNICATIONLETTER.COMMUNICATIONTYPECODE, 0)) as MAILEXPORTDEFINITIONID,
        COMMUNICATIONLETTER.MAILCONTENTHTML,
        coalesce(EMAILPACKAGE.EXPORTDEFINITIONID, dbo.UFN_COMMUNICATIONLETTER_GETDEFAULTEXPORTDEFINITIONID(COMMUNICATIONLETTER.COMMUNICATIONTYPECODE, 1)) as EMAILEXPORTDEFINITIONID,
        COMMUNICATIONLETTER.EMAILCONTENTHTML,
        COMMUNICATIONLETTER.EMAILSUBJECT,
        COMMUNICATIONLETTER.EMAILFROMADDRESS,
        COMMUNICATIONLETTER.EMAILREPLYTOADDRESS,
        COMMUNICATIONLETTER.EMAILFROMDISPLAYNAME,            
        COMMUNICATIONLETTER.PAPERSIZECODE,
        COMMUNICATIONLETTER.MARGINTOP,
        COMMUNICATIONLETTER.MARGINBOTTOM,
        COMMUNICATIONLETTER.MARGINLEFT,
        COMMUNICATIONLETTER.MARGINRIGHT,            
        COMMUNICATIONLETTER.MAILPACKAGEID,
        COMMUNICATIONLETTER.MAILSEGMENTID,
        COMMUNICATIONLETTER.EMAILPACKAGEID,
        COMMUNICATIONLETTER.EMAILSEGMENTID,            
        dbo.UFN_COMMUNICATIONLETTER_GETINCLUDEDSELECTIONS_TOITEMLISTXML(COMMUNICATIONLETTER.ID) as SELECTIONSXML,
        dbo.UFN_COMMUNICATIONLETTER_GETEXCLUDEDSELECTIONS_TOITEMLISTXML(COMMUNICATIONLETTER.ID) as EXCLUDEDSELECTIONSXML,
        dbo.UFN_COMMUNICATIONLETTER_GETEXCLUDEDSOLICITCODES_TOITEMLISTXML(COMMUNICATIONLETTER.ID) as EXCLUDEDSOLICITCODESXML,
        dbo.[UFN_MKTSEGMENTATION_GETREQUIREDANDDEFAULTSOLICITCODEEXCLUSIONS_TOITEMLISTXML](@CURRENTAPPUSERID) as REQUIREDANDEFAULTEXCLUSIONS,
        COMMUNICATIONLETTERACTIVITYEXCLUSIONS.EXCLUDEBASEDONRECENTCOMMUNICATION,
        COMMUNICATIONLETTERACTIVITYEXCLUSIONS.NUMRECENTCOMMUNICATIONPERIODS,
        COMMUNICATIONLETTERACTIVITYEXCLUSIONS.RECENTCOMMUNICATIONPERIODTYPECODE,
        COMMUNICATIONLETTERACTIVITYEXCLUSIONS.EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR,
        COMMUNICATIONLETTERACTIVITYEXCLUSIONS.NUMTOTALCOMMUNICATIONSINPASTYEAR,
        COMMUNICATIONLETTERACTIVITYEXCLUSIONS.EXCLUDEBASEDONRECENTGIVING,
        COMMUNICATIONLETTERACTIVITYEXCLUSIONS.NUMRECENTGIVINGPERIODS,
        COMMUNICATIONLETTERACTIVITYEXCLUSIONS.RECENTGIVINGPERIODTYPECODE,
        COMMUNICATIONLETTERACTIVITYEXCLUSIONS.EXCLUDEBASEDONTOTALGIVINGINPASTYEAR,
        COMMUNICATIONLETTERACTIVITYEXCLUSIONS.TOTALREVENUEAMOUNTINPASTYEAR,           
        dbo.UFN_COMMUNICATIONLETTERACTIVITYEXCLUSION_GETCOMMUNICATIONTYPES_TOITEMLISTXML(COMMUNICATIONLETTERACTIVITYEXCLUSIONS.ID) as COMMUNICATIONTYPESXML,
        COMMUNICATIONLETTER.COMMUNICATIONTYPECODE,
        COMMUNICATIONLETTER.OUTPUTTYPECODE
    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 COMMUNICATIONLETTER.ID = @COMMUNICATIONLETTERID;

    return 0;
end