USP_DATAFORMTEMPLATE_VIEW_CONTACTREPORTNEXTSTEP

The load procedure used by the view dataform template "Contact Report Next Step View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@OBJECTIVE	nvarchar(100)	INOUT	Objective
@STATUS	nvarchar(100)	INOUT	Status
@FUNDRAISER	nvarchar(154)	INOUT	Owner
@EXPECTEDDATE	datetime	INOUT	Expected date
@TOTALSTEPCOUNT	int	INOUT	Total steps in plan
@COMPLETEDSTEPCOUNT	int	INOUT	Steps completed
@PENDINGSTEPCOUNT	int	INOUT	Steps pending
@PLANNEDSTEPCOUNT	int	INOUT	Steps planned
@EXPECTEDSTARTTIME	UDT_HOURMINUTE	INOUT	Expected start time
@EXPECTEDENDTIME	UDT_HOURMINUTE	INOUT	Expected end time
@TIMEZONE	nvarchar(400)	INOUT	Time zone

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONTACTREPORTNEXTSTEP(
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @OBJECTIVE nvarchar(100) = null output,
                    @STATUS nvarchar(100) = null output,
                    @FUNDRAISER nvarchar(154) = null output,
                    @EXPECTEDDATE datetime = null output,
                    @TOTALSTEPCOUNT int = null output,
                    @COMPLETEDSTEPCOUNT int = null output,
                    @PENDINGSTEPCOUNT int = null output,
                    @PLANNEDSTEPCOUNT int = null output,
                    @EXPECTEDSTARTTIME dbo.UDT_HOURMINUTE = null output,
                    @EXPECTEDENDTIME dbo.UDT_HOURMINUTE = null output,
                    @TIMEZONE nvarchar(400) = null output
                ) as begin
                    set nocount on;

                    set @DATALOADED = 0;

                    select
                        @OBJECTIVE = INTERACTION.OBJECTIVE,
                        @STATUS = INTERACTION.STATUS,
                        @FUNDRAISER = NF.NAME,
                        @EXPECTEDDATE = INTERACTION.EXPECTEDDATE,
                        @EXPECTEDSTARTTIME = INTERACTION.EXPECTEDSTARTTIME,
                        @EXPECTEDENDTIME = INTERACTION.EXPECTEDENDTIME,
                        @TIMEZONE = (select DISPLAYNAME from TIMEZONEENTRY where TIMEZONEENTRY.ID = INTERACTION.TIMEZONEENTRYID)
                    from dbo.INTERACTION
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTION.FUNDRAISERID) NF
                    where INTERACTION.ID = dbo.UFN_PROSPECTPLAN_GETNEXTSTEP(@ID)

                    if exists ( select ID from dbo.INTERACTION where PROSPECTPLANID = @ID )
                    begin 
                        set @DATALOADED = 1                         

                        declare @PLANSTEP table (ID uniqueidentifier, STATUSCODE tinyint)
                        insert into @PLANSTEP (ID, STATUSCODE)
                        select ID, STATUSCODE
                        from dbo.INTERACTION
                        where PROSPECTPLANID = @ID;

                        select @TOTALSTEPCOUNT=count(ID)
                        from @PLANSTEP;

                        select @COMPLETEDSTEPCOUNT=count(ID)
                        from @PLANSTEP
                        where STATUSCODE = 2;

                        select @PENDINGSTEPCOUNT=count(ID)
                        from @PLANSTEP
                        where STATUSCODE = 1;

                        select @PLANNEDSTEPCOUNT=count(ID)
                        from @PLANSTEP
                        where STATUSCODE = 0;                        
                    end

                    return 0

                end