UFN_APPEALMAILING_GETLETTERCHANNELINSTANCES

Returns the details about each of the communications for a given appeal mailing.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@APPEALMAILINGID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_APPEALMAILING_GETLETTERCHANNELINSTANCES
(
    @APPEALMAILINGID uniqueidentifier
)
returns table
as
return 
(
    select
        COMMUNICATIONLETTER.SEQUENCE [SEQUENCE],
        COMMUNICATIONLETTER.NAME [LETTER],
        case 
            when MKTSEGMENTATIONSEGMENTCACHEINFO.OFFERCOUNT is null then MKTSEGMENTATIONSEGMENTACTIVE.QUANTITY
            else MKTSEGMENTATIONSEGMENTCACHEINFO.OFFERCOUNT
        end as [COUNT],
        MKTPACKAGE.ID [MKTPACKAGEID],
        MKTPACKAGE.CHANNEL [CHANNEL],
        MKTPACKAGE.UNITCOST [COSTPERPIECE]
    from
        MKTSEGMENTATIONSEGMENT
    inner join
        COMMUNICATIONLETTER on COMMUNICATIONLETTER.SEGMENTATIONID = MKTSEGMENTATIONSEGMENT.SEGMENTATIONID
    inner join
        MKTPACKAGE on (MKTPACKAGE.ID = COMMUNICATIONLETTER.EMAILPACKAGEID or MKTPACKAGE.ID = COMMUNICATIONLETTER.MAILPACKAGEID) and  MKTSEGMENTATIONSEGMENT.PACKAGEID = MKTPACKAGE.ID
    left join
        MKTSEGMENTATIONSEGMENTCACHEINFO on MKTSEGMENTATIONSEGMENTCACHEINFO.SEGMENTID = MKTSEGMENTATIONSEGMENT.ID
    left join
        MKTSEGMENTATIONSEGMENTACTIVE on MKTSEGMENTATIONSEGMENTACTIVE.SEGMENTID = MKTSEGMENTATIONSEGMENT.ID
    where
        MKTSEGMENTATIONSEGMENT.SEGMENTATIONID = @APPEALMAILINGID
)