USP_DATALIST_CONTEXTLESSAPPEALMAILING

Returns a list of all appeal mailings in the system.
Parameters

Parameter	Parameter Type	Mode	Description
@ACTIVESTATUS	bit	IN	Status
@MAILDATEFROM	datetime	IN	From
@MAILDATETO	datetime	IN	To
@SHOWUNSCHEDULED	bit	IN	Include mailings with no date
Definition

Copy


CREATE procedure dbo.USP_DATALIST_CONTEXTLESSAPPEALMAILING
(
@ACTIVESTATUS bit = null,
@MAILDATEFROM datetime = null,
@MAILDATETO datetime = null,
@SHOWUNSCHEDULED bit = 1
)
as
    set nocount on;

    select
        [MKTSEGMENTATION].[ID],
        [MKTSEGMENTATIONACTIVATEPROCESS].[ID] as [MKTSEGMENTATIONACTIVATEPROCESSID],
        [MKTSEGMENTATION].[NAME],
        [MKTSEGMENTATION].[DESCRIPTION],
        [MKTSEGMENTATION].[MAILDATE],
        [MKTSEGMENTATION].[ACTIVE],
        [APPEAL].[NAME] [APPEALNAME],
        [MKTPACKAGE].[NAME] [PACKAGE],
        [APPEALMAILINGSELECTION].[NAME] [SELECTION],
        [APPEAL].[BASECURRENCYID]
    from dbo.APPEALMAILING
    inner join dbo.MKTSEGMENTATION 
        on MKTSEGMENTATION.[ID] = APPEALMAILING.[ID]
    left join dbo.MKTSEGMENTATIONACTIVATEPROCESS 
        on MKTSEGMENTATIONACTIVATEPROCESS.[SEGMENTATIONID] = MKTSEGMENTATION.[ID]
    left join dbo.APPEAL 
        on APPEAL.ID = APPEALMAILING.APPEALID
    left join dbo.[MKTSEGMENTATIONSEGMENT] 
        on [APPEALMAILING].[MKTSEGMENTATIONSEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
    left join dbo.[MKTPACKAGE] 
        on [MKTSEGMENTATIONSEGMENT].[PACKAGEID] = [MKTPACKAGE].[ID]
    left join dbo.[MKTSEGMENT] 
        on [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID]
    left outer join dbo.[MKTSEGMENTSELECTION] 
        on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    left outer join dbo.[IDSETREGISTER] as [APPEALMAILINGSELECTION] 
        on [APPEALMAILINGSELECTION].[ID] = [MKTSEGMENTSELECTION].[SELECTIONID]
    where (@ACTIVESTATUS is null or @ACTIVESTATUS = [MKTSEGMENTATION].[ACTIVE])
        and (@MAILDATEFROM is null or ([MKTSEGMENTATION].[MAILDATE] is not null and [MKTSEGMENTATION].[MAILDATE] >= @MAILDATEFROM) or (@SHOWUNSCHEDULED = 1 and [MKTSEGMENTATION].[MAILDATE] is null))
        and (@MAILDATETO is null or ([MKTSEGMENTATION].[MAILDATE] is not null and [MKTSEGMENTATION].[MAILDATE] <= @MAILDATETO) or (@SHOWUNSCHEDULED = 1 and [MKTSEGMENTATION].[MAILDATE] is null))
        and ((@SHOWUNSCHEDULED is null) or (@SHOWUNSCHEDULED = 1) or (@SHOWUNSCHEDULED = 0 and [MKTSEGMENTATION].[MAILDATE] is not null))
    order by
        [MKTSEGMENTATION].[MAILDATE] desc, [MKTSEGMENTATION].[NAME];

    return 0;