USP_DATALIST_PROSPECTTEAM

            CREATE procedure dbo.USP_DATALIST_PROSPECTTEAM
            (
                @PROSPECTID uniqueidentifier,
                @VIEWINACTIVE bit = 0
            )  
            as 
            begin
                set nocount on;

                declare @now DateTime = GetDate()
                declare @minCompareResult int = case @viewinactive when 0 then 0 else 1 end

select
    PROSPECTTEAM.ID,
    PROSPECTTEAM.MEMBERID,
    PROSPECTTEAM.NAME,
    PROSPECTTEAM.ROLEDESCRIPTION,
    PROSPECTTEAM.DATEFROM,
    PROSPECTTEAM.DATETO,
    PROSPECTTEAM.ORGANIZATIONALSITE,
    PROSPECTTEAM.TIMEFRAME,
    PROSPECTTEAM.ISFUNDRAISER,
    PROSPECTTEAM.ISUSERDEFINED,
    PROSPECTTEAM.ROLETIMEFRAME,
    PROSPECTTEAM.PLANNAME
from
(
    select 
        ID,
        MEMBERID,
        NAME,
        ROLEDESCRIPTION,
        DATEFROM,
        DATETO,
        ORGANIZATIONALSITE,
        TIMEFRAME,
        1 as ISFUNDRAISER,
        1 as ISUSERDEFINED,
        ROLEDESCRIPTION + ' - ' + TIMEFRAME as ROLETIMEFRAME,
        null as PLANNAME
    from 
        dbo.UFN_PROSPECTTEAM_SELECT(@PROSPECTID, @VIEWINACTIVE)

    union all

    --add primary plan managers as current unless plan is historical (historical managers will be added separately)

    select
        PROSPECTPLAN.ID, 
        PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID as MEMBERID, 
        PM_NF.NAME, 
        'Primary plan manager' as ROLEDESCRIPTION, 
        PROSPECTPLAN.PRIMARYMANAGERSTARTDATE as DATEFROM, 
        PROSPECTPLAN.PRIMARYMANAGERENDDATE as DATETO, 
        dbo.UFN_FUNDRAISER_GETDEFAULTSITENAME(PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID) as ORGANIZATIONALSITE,                
        case when PROSPECTPLAN.ISACTIVE = 1 then 'Current' else 'Prior' end as TIMEFRAME,
        1 as ISFUNDRAISER,
        0 as ISUSERDEFINED,
        'Primary plan manager - ' + case when PROSPECTPLAN.ISACTIVE = 1 then 'Current' else 'Prior' end as ROLETIMEFRAME,
        PROSPECTPLAN.NAME as PLANNAME
    from
        dbo.PROSPECTPLAN
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID) PM_NF
    where 
        PROSPECTPLAN.PROSPECTID = @PROSPECTID and
        PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID is not null and
        ((@VIEWINACTIVE = 0 and PROSPECTPLAN.ISACTIVE = 1) or (@VIEWINACTIVE = 1))

    union all

    --add secondary plan managers as current unless plan is historical (historical managers will be added separately)

    select
        PROSPECTPLAN.ID, 
        PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID as MEMBERID, 
        PM_NF.NAME, 
        'Secondary plan manager' as ROLEDESCRIPTION, 
        PROSPECTPLAN.SECONDARYMANAGERSTARTDATE as DATEFROM, 
        PROSPECTPLAN.SECONDARYMANAGERENDDATE as DATETO, 
        dbo.UFN_FUNDRAISER_GETDEFAULTSITENAME(PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID) as ORGANIZATIONALSITE,                
        case when PROSPECTPLAN.ISACTIVE = 1 then 'Current' else 'Prior' end as TIMEFRAME,
        1 as ISFUNDRAISER,
        0 as ISUSERDEFINED,
        'Secondary plan manager - ' + case when PROSPECTPLAN.ISACTIVE = 1 then 'Current' else 'Prior' end as ROLETIMEFRAME,
        PROSPECTPLAN.NAME as PLANNAME
    from
        dbo.PROSPECTPLAN
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID) PM_NF
    where 
        PROSPECTPLAN.PROSPECTID = @PROSPECTID and
        PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID is not null and
        ((@VIEWINACTIVE = 0 and PROSPECTPLAN.ISACTIVE = 1) or (@VIEWINACTIVE = 1))

    union all

    --add historical plan managers

    select
        PROSPECTPLANMANAGERHISTORY.ID, 
        PROSPECTPLANMANAGERHISTORY.FUNDRAISERID as MEMBERID, 
        NF.NAME, 
        case when PROSPECTPLANMANAGERHISTORY.ISPRIMARYMANAGER = 1 then 'Primary plan manager' else 'Secondary plan manager' end as ROLEDESCRIPTION, 
        PROSPECTPLANMANAGERHISTORY.DATEFROM as DATEFROM, 
        PROSPECTPLANMANAGERHISTORY.DATETO as DATETO, 
        dbo.UFN_FUNDRAISER_GETDEFAULTSITENAME(PROSPECTPLANMANAGERHISTORY.FUNDRAISERID) as ORGANIZATIONALSITE,                
        'Prior' as TIMEFRAME,
        1 as ISFUNDRAISER,
        0 as ISUSERDEFINED,
        case when PROSPECTPLANMANAGERHISTORY.ISPRIMARYMANAGER = 1 then 'Primary plan manager' else 'Secondary plan manager' end + ' - Prior' as ROLETIMEFRAME,
        PROSPECTPLAN.NAME as PLANNAME
    from
        dbo.PROSPECTPLANMANAGERHISTORY
    inner join dbo.PROSPECTPLAN on PROSPECTPLANMANAGERHISTORY.PROSPECTPLANID = PROSPECTPLAN.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLANMANAGERHISTORY.FUNDRAISERID) NF
    where 
        @VIEWINACTIVE = 1 and
        PROSPECTPLAN.PROSPECTID = @PROSPECTID

    union all

    --add secondary solicitors

    select
        SECONDARYFUNDRAISER.ID, 
        SECONDARYFUNDRAISER.FUNDRAISERID as MEMBERID, 
        NF.NAME, 
        case when SOLICITORROLECODE.ID is null then 'Secondary solicitor' else SOLICITORROLECODE.DESCRIPTION end as ROLEDESCRIPTION, 
        SECONDARYFUNDRAISER.DATEFROM as DATEFROM, 
        SECONDARYFUNDRAISER.DATETO as DATETO, 
        dbo.UFN_FUNDRAISER_GETDEFAULTSITENAME(SECONDARYFUNDRAISER.FUNDRAISERID) as ORGANIZATIONALSITE,                
        case when PROSPECTPLAN.ISACTIVE = 1 then SECONDARYFUNDRAISER.STATUS else 'Prior' end as TIMEFRAME,
        1 as ISFUNDRAISER,
        0 as ISUSERDEFINED,
        'Secondary solicitor - ' + case when PROSPECTPLAN.ISACTIVE = 1 then SECONDARYFUNDRAISER.STATUS else 'Prior' end as ROLETIMEFRAME,
        PROSPECTPLAN.NAME as PLANNAME
    from
        dbo.SECONDARYFUNDRAISER
    inner join dbo.PROSPECTPLAN on SECONDARYFUNDRAISER.PROSPECTPLANID = PROSPECTPLAN.ID
    left join dbo.SOLICITORROLECODE on SECONDARYFUNDRAISER.SOLICITORROLECODEID = SOLICITORROLECODE.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SECONDARYFUNDRAISER.FUNDRAISERID) NF
    where 
        ((@VIEWINACTIVE = 0 and SECONDARYFUNDRAISER.STATUS <> 'Prior' and PROSPECTPLAN.ISACTIVE = 1) or (@VIEWINACTIVE = 1)) and
        PROSPECTPLAN.PROSPECTID = @PROSPECTID

    union all

    --add stewardship plan managers as Current (historical managers will be added separately)

    select
        STEWARDSHIPPLAN.ID, 
        STEWARDSHIPPLAN.MANAGERID as MEMBERID, 
        NF.NAME, 
        'Stewardship plan manager' as ROLEDESCRIPTION, 
        STEWARDSHIPPLAN.MANAGERSTARTDATE as DATEFROM, 
        STEWARDSHIPPLAN.MANAGERENDDATE as DATETO, 
        dbo.UFN_FUNDRAISER_GETDEFAULTSITENAME(STEWARDSHIPPLAN.MANAGERID) as ORGANIZATIONALSITE,                
        'Current' as TIMEFRAME,
        0 as ISFUNDRAISER,
        0 as ISUSERDEFINED,
        'Stewardship plan manager - Current' as ROLETIMEFRAME,
        STEWARDSHIPPLAN.NAME as PLANNAME
    from
        dbo.STEWARDSHIPPLAN
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLAN.MANAGERID) NF
    where 
        STEWARDSHIPPLAN.CONSTITUENTID = @PROSPECTID and
        STEWARDSHIPPLAN.MANAGERID is not null and
        ((@VIEWINACTIVE = 0 and STEWARDSHIPPLAN.ISACTIVE = 1) or (@VIEWINACTIVE = 1))

    union all

    --add historical stewardship plan managers

    select
        STEWARDSHIPPLANMANAGERHISTORY.ID, 
        STEWARDSHIPPLANMANAGERHISTORY.MANAGERID as MEMBERID, 
        NF.NAME, 
        'Stewardship plan manager' as ROLEDESCRIPTION, 
        STEWARDSHIPPLANMANAGERHISTORY.STARTDATE as DATEFROM, 
        STEWARDSHIPPLANMANAGERHISTORY.ENDDATE as DATETO, 
        dbo.UFN_FUNDRAISER_GETDEFAULTSITENAME(STEWARDSHIPPLANMANAGERHISTORY.MANAGERID) as ORGANIZATIONALSITE,                
        'Prior' as TIMEFRAME,
        0 as ISFUNDRAISER,
        0 as ISUSERDEFINED,
        'Stewardship plan manager - Prior' as ROLETIMEFRAME,
        STEWARDSHIPPLAN.NAME as PLANNAME
    from
        dbo.STEWARDSHIPPLANMANAGERHISTORY
    inner join dbo.STEWARDSHIPPLAN on STEWARDSHIPPLANMANAGERHISTORY.STEWARDSHIPPLANID = STEWARDSHIPPLAN.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLANMANAGERHISTORY.MANAGERID) NF
    where 
        @VIEWINACTIVE = 1 and
        STEWARDSHIPPLAN.CONSTITUENTID = @PROSPECTID

    union all

    --add stewards

    select
        STEWARDSHIPPLANSTEWARD.ID, 
        STEWARDSHIPPLANSTEWARD.CONSTITUENTID as MEMBERID, 
        NF.NAME, 
        case when STEWARDSHIPSTEWARDROLECODE.ID is null then 'Steward' else STEWARDSHIPSTEWARDROLECODE.DESCRIPTION end as ROLEDESCRIPTION, 
        STEWARDSHIPPLANSTEWARD.STARTDATE as DATEFROM, 
        STEWARDSHIPPLANSTEWARD.ENDDATE as DATETO, 
        dbo.UFN_FUNDRAISER_GETDEFAULTSITENAME(STEWARDSHIPPLANSTEWARD.CONSTITUENTID) as ORGANIZATIONALSITE,                
        case dbo.UFN_DATE_COMPARETODATERANGE(@now, STEWARDSHIPPLANSTEWARD.STARTDATE, STEWARDSHIPPLANSTEWARD.ENDDATE) when -1 then 'Future' when 0 then 'Current' when 1 then 'Prior' else '' end as TIMEFRAME,
        0 as ISFUNDRAISER,
        0 as ISUSERDEFINED,
        'Steward - ' + case dbo.UFN_DATE_COMPARETODATERANGE(@now, STEWARDSHIPPLANSTEWARD.STARTDATE, STEWARDSHIPPLANSTEWARD.ENDDATE) when -1 then 'Future' when 0 then 'Current' when 1 then 'Prior' else '' end as ROLETIMEFRAME,
        STEWARDSHIPPLAN.NAME as PLANNAME
    from
        dbo.STEWARDSHIPPLANSTEWARD
    inner join dbo.STEWARDSHIPPLAN on STEWARDSHIPPLANSTEWARD.PLANID = STEWARDSHIPPLAN.ID
    left join dbo.STEWARDSHIPSTEWARDROLECODE on STEWARDSHIPPLANSTEWARD.ROLECODEID = STEWARDSHIPSTEWARDROLECODE.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLANSTEWARD.CONSTITUENTID) NF
    where 
        ((@VIEWINACTIVE = 0 and STEWARDSHIPPLAN.ISACTIVE = 1 and dbo.UFN_DATE_COMPARETODATERANGE(@now, STEWARDSHIPPLANSTEWARD.STARTDATE, STEWARDSHIPPLANSTEWARD.ENDDATE)<=0) or (@VIEWINACTIVE = 1)) and
        STEWARDSHIPPLAN.CONSTITUENTID = @PROSPECTID
) PROSPECTTEAM
    left join dbo.CONSTITUENT on PROSPECTTEAM.MEMBERID = CONSTITUENT.ID
order by PROSPECTTEAM.TIMEFRAME, PROSPECTTEAM.PLANNAME, PROSPECTTEAM.ROLEDESCRIPTION, CONSTITUENT.KEYNAME
            end