USP_DATAFORM_EDITLOAD_PROSPECTPLAN_2

The load procedure used by the edit dataform template "Prospect Plan Edit Form 2"

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.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@PROSPECT	nvarchar(700)	INOUT	Prospect
@PROSPECTID	uniqueidentifier	INOUT
@PROSPECTPLANNAME	nvarchar(100)	INOUT	Plan name
@PROSPECTPLANTYPECODEID	uniqueidentifier	INOUT	Plan type
@PROSPECTPLANTYPECODE	nvarchar(100)	INOUT	Plan type
@STEPS	xml	INOUT	Steps
@FISCALYEARSTARTDATE	datetime	INOUT	Fiscal year start date
@FISCALYEARENDDATE	datetime	INOUT	Fiscal year end date
@SITE	nvarchar(1024)	INOUT	Site

Definition

Copy



          CREATE procedure dbo.USP_DATAFORM_EDITLOAD_PROSPECTPLAN_2
          (
            @ID uniqueidentifier,
            @DATALOADED bit = 0 output,
            @TSLONG bigint = 0 output,
            @PROSPECT nvarchar(700) = null output,
            @PROSPECTID uniqueidentifier = null output,
            @PROSPECTPLANNAME nvarchar(100) = null output,
            @PROSPECTPLANTYPECODEID uniqueidentifier = null output,
            @PROSPECTPLANTYPECODE nvarchar(100) = null output,
            @STEPS xml = null output,
            @FISCALYEARSTARTDATE datetime = null output,
            @FISCALYEARENDDATE datetime = null output,
            @SITE nvarchar(1024) = null output
          ) as begin
            set nocount on;

            set @DATALOADED = 0;
            set @TSLONG = 0;

            set @STEPS = 
              (
                select 
                ID, 
                PLANOUTLINESTEPID, 
                OBJECTIVE, 
                FUNDRAISERID, 
                PROSPECTPLANID,
                PROSPECTPLANSTATUSCODEID,
                STATUSCODE, 
                EXPECTEDDATE,
                ACTUALDATE, 
                INTERACTIONTYPECODEID,
                (select INTERACTIONCATEGORYID from INTERACTIONSUBCATEGORY where ID = INTERACTIONSUBCATEGORYID) as INTERACTIONCATEGORYID,
                INTERACTIONSUBCATEGORYID,
                CONSTITUENTID,
                case 
                  when exists (select ID from INTERACTIONADDITIONALFUNDRAISER where INTERACTIONID = INTERACTION.ID) then 1
                  else 0
                end HASADDITIONALFUNDRAISERS,
                (
                  select
                    ID,
                    FUNDRAISERID
                  from dbo.INTERACTIONADDITIONALFUNDRAISER
                  where INTERACTIONID = INTERACTION.ID
                  order by FUNDRAISERID
                  for xml raw('ITEM'),type,elements,root('ADDITIONALFUNDRAISERS'),BINARY BASE64
                ),
                case
                  when exists (select ID from dbo.INTERACTIONPARTICIPANT where INTERACTIONID = INTERACTION.ID) then 1
                  else 0
                end HASPARTICIPANTS,
                (
                  select
                    ID,
                    CONSTITUENTID
                  from dbo.INTERACTIONPARTICIPANT
                  where INTERACTIONID = INTERACTION.ID
                  order by CONSTITUENTID
                  for xml raw('ITEM'),type,elements,root('PARTICIPANTS'),BINARY BASE64
                ),
                COMMENT,
                EXPECTEDSTARTTIME,
                EXPECTEDENDTIME,
                TIMEZONEENTRYID,
                ISALLDAYEVENT,
                ACTUALSTARTTIME,
                ACTUALENDTIME,
                LOCATION
              from dbo.INTERACTION
              where
                PROSPECTPLANID = @ID
                and COMPLETED = 0
              order by EXPECTEDDATE    
              for xml raw('ITEM'),type,elements,root('STEPS'),BINARY BASE64);

              select 
                @DATALOADED = 1,
                @TSLONG = PROSPECTPLAN.TSLONG,
                @PROSPECTID = PROSPECTPLAN.PROSPECTID,
                @PROSPECT = NF.NAME,
                @PROSPECTPLANNAME = PROSPECTPLAN.NAME,
                @PROSPECTPLANTYPECODE = dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PROSPECTPLAN.PROSPECTPLANTYPECODEID),
                @PROSPECTPLANTYPECODEID = PROSPECTPLAN.PROSPECTPLANTYPECODEID,
                @SITE = dbo.UFN_PROSPECTPLAN_GETSITELIST(PROSPECTPLAN.ID)
              from dbo.PROSPECTPLAN
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.PROSPECTID) NF
              where PROSPECTPLAN.ID = @ID;

              declare @CURRENTDATE as DATETIME
              select @CURRENTDATE = CURRENT_TIMESTAMP

              select @FISCALYEARSTARTDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE, 0)
              select @FISCALYEARENDDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@CURRENTDATE, 0)

              return 0;
            end;