UFN_FUNDRAISER_PROSPECTPLANS

Gets a table of prospect plans for a given fundraiser.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode	Description
@FUNDRAISERID	uniqueidentifier	IN
Definition

Copy


            create function dbo.UFN_FUNDRAISER_PROSPECTPLANS(@FUNDRAISERID uniqueidentifier) 
            returns table
            as return
                select 0 SEQUENCE, 'Primary manager, Secondary manager, Secondary solicitor' FUNDRAISERROLE, ID from dbo.PROSPECTPLAN where (                                       PRIMARYMANAGERFUNDRAISERID =@FUNDRAISERID) and (                               SECONDARYMANAGERFUNDRAISERID =@FUNDRAISERID) and     exists (select 1 from dbo.SECONDARYFUNDRAISER where PROSPECTPLANID=PROSPECTPLAN.ID and FUNDRAISERID=@FUNDRAISERID)
                union all
                select 1 SEQUENCE, 'Primary manager, Secondary manager'                      FUNDRAISERROLE, ID from dbo.PROSPECTPLAN where (                                       PRIMARYMANAGERFUNDRAISERID =@FUNDRAISERID) and (                               SECONDARYMANAGERFUNDRAISERID =@FUNDRAISERID) and not exists (select 1 from dbo.SECONDARYFUNDRAISER where PROSPECTPLANID=PROSPECTPLAN.ID and FUNDRAISERID=@FUNDRAISERID)
                union all
                select 2 SEQUENCE, 'Primary manager, Secondary solicitor'                    FUNDRAISERROLE, ID from dbo.PROSPECTPLAN where (                                       PRIMARYMANAGERFUNDRAISERID =@FUNDRAISERID) and (SECONDARYMANAGERFUNDRAISERID is null or SECONDARYMANAGERFUNDRAISERID!=@FUNDRAISERID) and     exists (select 1 from dbo.SECONDARYFUNDRAISER where PROSPECTPLANID=PROSPECTPLAN.ID and FUNDRAISERID=@FUNDRAISERID)
                union all
                select 3 SEQUENCE, 'Secondary manager, Secondary solicitor'                   FUNDRAISERROLE, ID from dbo.PROSPECTPLAN where (PRIMARYMANAGERFUNDRAISERID is null or PRIMARYMANAGERFUNDRAISERID!=@FUNDRAISERID) and (                               SECONDARYMANAGERFUNDRAISERID =@FUNDRAISERID) and     exists (select 1 from dbo.SECONDARYFUNDRAISER where PROSPECTPLANID=PROSPECTPLAN.ID and FUNDRAISERID=@FUNDRAISERID)
                union all
                select 4 SEQUENCE, 'Primary manager'                                         FUNDRAISERROLE, ID from dbo.PROSPECTPLAN where (                                       PRIMARYMANAGERFUNDRAISERID =@FUNDRAISERID) and (SECONDARYMANAGERFUNDRAISERID is null or SECONDARYMANAGERFUNDRAISERID!=@FUNDRAISERID) and not exists (select 1 from dbo.SECONDARYFUNDRAISER where PROSPECTPLANID=PROSPECTPLAN.ID and FUNDRAISERID=@FUNDRAISERID)
                union all
                select 5 SEQUENCE, 'Secondary manager'                                        FUNDRAISERROLE, ID from dbo.PROSPECTPLAN where (PRIMARYMANAGERFUNDRAISERID is null or PRIMARYMANAGERFUNDRAISERID!=@FUNDRAISERID) and (                               SECONDARYMANAGERFUNDRAISERID =@FUNDRAISERID) and not exists (select 1 from dbo.SECONDARYFUNDRAISER where PROSPECTPLANID=PROSPECTPLAN.ID and FUNDRAISERID=@FUNDRAISERID)
                union all
                select 6 SEQUENCE, 'Secondary solicitor'                                      FUNDRAISERROLE, ID from dbo.PROSPECTPLAN where (PRIMARYMANAGERFUNDRAISERID is null or PRIMARYMANAGERFUNDRAISERID!=@FUNDRAISERID) and (SECONDARYMANAGERFUNDRAISERID is null or SECONDARYMANAGERFUNDRAISERID!=@FUNDRAISERID) and     exists (select 1 from dbo.SECONDARYFUNDRAISER where PROSPECTPLANID=PROSPECTPLAN.ID and FUNDRAISERID=@FUNDRAISERID)