UFN_PLANOUTLINE_STEPSFORPROSPECTEDIT

Returns steps from a plan outline for use on a specific prospect plan in edit mode.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@PLANOUTLINEID	uniqueidentifier	IN
@PROSPECTPLANID	uniqueidentifier	IN
@BASEDATE	datetime	IN
Definition

Copy


            CREATE function dbo.UFN_PLANOUTLINE_STEPSFORPROSPECTEDIT(@PLANOUTLINEID uniqueidentifier, @PROSPECTPLANID uniqueidentifier, @BASEDATE datetime)
            returns @RET table (
                ID uniqueidentifier,
                PLANOUTLINESTEPID uniqueidentifier,
                OBJECTIVE nvarchar(100),
                OWNERID uniqueidentifier,
                PROSPECTPLANSTATUSCODEID uniqueidentifier,
                STATUSCODE tinyint,
                EXPECTEDDATE datetime,
                ACTUALDATE datetime,
                INTERACTIONTYPECODEID uniqueidentifier,
                CONSTITUENTID uniqueidentifier,
                FUNDRAISERID uniqueidentifier
            )
            with execute as caller
            as begin
                declare @CONSTITUENTID uniqueidentifier;
                declare @PROSPECTMANAGERFUNDRAISERID uniqueidentifier;
                declare @PRIMARYMANAGERFUNDRAISERID uniqueidentifier;
                declare @SECONDARYMANAGERFUNDRAISERID uniqueidentifier;
                declare @SECONDARYFUNDRAISERID uniqueidentifier;

                select top 1
                    @CONSTITUENTID = P.ID,
                    @PROSPECTMANAGERFUNDRAISERID = P.PROSPECTMANAGERFUNDRAISERID,
                    @PRIMARYMANAGERFUNDRAISERID = PP.PRIMARYMANAGERFUNDRAISERID, 
                    @SECONDARYMANAGERFUNDRAISERID = PP.SECONDARYMANAGERFUNDRAISERID,
                    @SECONDARYFUNDRAISERID = SF.FUNDRAISERID 
                from 
                    dbo.PROSPECTPLAN PP
                    left outer join dbo.PROSPECT P on P.ID=PP.PROSPECTID
                    left outer join dbo.SECONDARYFUNDRAISER SF on SF.PROSPECTPLANID=PP.ID
                where 
                    PP.ID=@PROSPECTPLANID

                insert into @RET
                select
                    null,
                    ID,
                    OBJECTIVE, 
                    case FUNDRAISERROLECODE when 0 then @PROSPECTMANAGERFUNDRAISERID when 1 then @PRIMARYMANAGERFUNDRAISERID when 2 then @SECONDARYMANAGERFUNDRAISERID else @SECONDARYFUNDRAISERID end,
                    PROSPECTPLANSTATUSCODEID,
                    0,
                    dateadd(day, DATEOFFSET, dbo.UFN_DATE_GETEARLIESTTIME(@BASEDATE)),
                    null,
                    INTERACTIONTYPECODEID,
                    @CONSTITUENTID,
                    case FUNDRAISERROLECODE when 0 then @PROSPECTMANAGERFUNDRAISERID when 1 then @PRIMARYMANAGERFUNDRAISERID when 2 then @SECONDARYMANAGERFUNDRAISERID else @SECONDARYFUNDRAISERID end
                from 
                    dbo.PLANOUTLINESTEP
                where 
                    PLANOUTLINEID=@PLANOUTLINEID;

                return;
            end