USP_DATALIST_APPEALMAILINGCALENDARITEM

Displays appeal mailing items on the organization's calendar.
Parameters

Parameter	Parameter Type	Mode	Description
@STARTDATE	datetime	IN	Start date
@ENDDATE	datetime	IN	End date
@APPEALID	uniqueidentifier	IN	Appeal
@INCLUDETASKS	bit	IN	Include tasks
Definition

Copy


CREATE procedure dbo.USP_DATALIST_APPEALMAILINGCALENDARITEM
(
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @APPEALID uniqueidentifier = null,
    @INCLUDETASKS bit = null
)
as
    set nocount on;

    select
        MKTSEGMENTATION.ID,
        MKTSEGMENTATION.NAME,
        MKTSEGMENTATION.MAILDATE,
        0, --Mailing

        MKTSEGMENTATION.DESCRIPTION,
        MKTSEGMENTATION.ID as PARENTID
    from
        dbo.MKTSEGMENTATION
    left outer join
        dbo.APPEALMAILING on APPEALMAILING.ID = MKTSEGMENTATION.ID
    where
        ( (APPEALMAILING.ID is not null) )
        and (MKTSEGMENTATION.MAILDATE is not null)
        and
        (
            ( (MKTSEGMENTATION.MAILDATE between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
            or ( (MKTSEGMENTATION.MAILDATE >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
            or ( (MKTSEGMENTATION.MAILDATE <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
        )
        and ( (APPEALMAILING.APPEALID = @APPEALID) or (@APPEALID is null) )
    union
    select
        APPEALMAILINGTASK.ID,
        APPEALMAILINGTASK.SUBJECT,
        dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE),
        1, --Task

        '' DESCRIPTION,
        APPEALMAILINGTASK.SEGMENTATIONID as PARENTID
    from
        dbo.APPEALMAILINGTASK
    inner join
        dbo.MKTSEGMENTATION on MKTSEGMENTATION.ID = APPEALMAILINGTASK.SEGMENTATIONID
    left outer join
        dbo.APPEALMAILING on APPEALMAILING.ID = MKTSEGMENTATION.ID
    where
        (APPEALMAILINGTASK.DATEDUE <> '00000000')
        and
        (
            ( (dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE) between @STARTDATE and @ENDDATE) or (@STARTDATE is null and @ENDDATE is null) )
            or ( (dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE) >= @STARTDATE) and (@ENDDATE is null and @STARTDATE is not null) )
            or ( (dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE) <= @ENDDATE) and (@STARTDATE is null and @ENDDATE is not null) )
        )
        and ( (APPEALMAILING.APPEALID = @APPEALID) or (@APPEALID is null) )
        and @INCLUDETASKS = 1
    order by NAME