USP_DATALIST_APPEALMAILINGTASK

Returns a list of tasks for a given appeal mailing.

Parameters

Parameter	Parameter Type	Mode	Description
@MAILINGID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@DATEFILTER	tinyint	IN	Tasks
@INCLUDECOMPLETED	bit	IN	Include completed

Definition

Copy



CREATE procedure dbo.USP_DATALIST_APPEALMAILINGTASK
(
    @MAILINGID uniqueidentifier,
    @DATEFILTER tinyint = 0,
    @INCLUDECOMPLETED bit = 0
)
as
    set nocount on;

    declare @DATE datetime;
    set @DATE = getDate();

    declare @ENDDATE datetime;

    if @DATEFILTER is null    -- all

        set @ENDDATE = null

    if @DATEFILTER = 1        -- this week

        set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@DATE, 1)

    if @DATEFILTER = 2        -- this month

        set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@DATE, 1)

    select
        APPEALMAILINGTASK.ID,
        case
            when APPEALMAILINGTASK.STATUSCODE = 0 and datediff(day, @DATE, dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE)) < 0 then
                1
            else
                0
        end [OVERDUE],
        case
            when APPEALMAILINGTASK.STATUSCODE = 0 and datediff(day, @DATE, dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE)) < 0 then 
                'RES:warning'
            when APPEALMAILINGTASK.STATUSCODE = 1 then 
                'RES:checkmark'
            else 
                'RES:lv_spacer'
        end [IMAGE],                        
        APPEALMAILINGTASK.SUBJECT,
        case when APPEALMAILINGTASK.DATEDUE = '00000000' then null else APPEALMAILINGTASK.DATEDUE end,
        APPEALMAILINGTASK.STATUSCODE,
        NF.NAME
    from
        dbo.APPEALMAILINGTASK
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(APPEALMAILINGTASK.OWNERID) NF
    where
        APPEALMAILINGTASK.SEGMENTATIONID = @MAILINGID
        and ( (@ENDDATE is null) or (dbo.UFN_DATE_LATESTFROMFUZZYDATE(APPEALMAILINGTASK.DATEDUE) <= @ENDDATE) )
        and ( (APPEALMAILINGTASK.STATUSCODE = 0) or (@INCLUDECOMPLETED = 1) )
    order by
        APPEALMAILINGTASK.DATEDUE, APPEALMAILINGTASK.SUBJECT;