UFN_REMINDER_GETLETTERCHANNELINSTANCES

Returns the details about each of the communications for a giving reminder.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode	Description
@REMINDEROPTIONID	uniqueidentifier	IN

Definition

Copy



create function dbo.UFN_REMINDER_GETLETTERCHANNELINSTANCES(
@REMINDEROPTIONID 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
        dbo.MKTSEGMENTATIONSEGMENT
    inner join
        dbo.COMMUNICATIONLETTER on COMMUNICATIONLETTER.SEGMENTATIONID = MKTSEGMENTATIONSEGMENT.SEGMENTATIONID
    inner join
        dbo.MKTPACKAGE on (MKTPACKAGE.ID = COMMUNICATIONLETTER.EMAILPACKAGEID or MKTPACKAGE.ID = COMMUNICATIONLETTER.MAILPACKAGEID) and  MKTSEGMENTATIONSEGMENT.PACKAGEID = MKTPACKAGE.ID
    left join
        dbo.MKTSEGMENTATIONSEGMENTCACHEINFO on MKTSEGMENTATIONSEGMENTCACHEINFO.SEGMENTID = MKTSEGMENTATIONSEGMENT.ID
    left join
        dbo.MKTSEGMENTATIONSEGMENTACTIVE on MKTSEGMENTATIONSEGMENTACTIVE.SEGMENTID = MKTSEGMENTATIONSEGMENT.ID
    where
        MKTSEGMENTATIONSEGMENT.SEGMENTATIONID = @REMINDEROPTIONID
)