USP_DATAFORM_EDITLOAD_FUNDINGREQUESTSTEPSALL

The load procedure used by the edit dataform template "Funding Request Steps All Edit 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.
@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.
@FUNDINGPLAN	nvarchar(154)	INOUT	Prospect
@FUNDINGPLANID	uniqueidentifier	INOUT
@FUNDINGREQUESTNAME	nvarchar(100)	INOUT	Funding request name
@FUNDINGREQUESTTYPECODEID	uniqueidentifier	INOUT	Funding request type
@FUNDINGREQUESTTYPECODE	nvarchar(100)	INOUT	Funding request type
@STEPS	xml	INOUT
@FISCALYEARSTARTDATE	datetime	INOUT	Fiscal year start date
@FISCALYEARENDDATE	datetime	INOUT	Fiscal year end date

Definition

Copy



          CREATE procedure dbo.USP_DATAFORM_EDITLOAD_FUNDINGREQUESTSTEPSALL
          (
            @ID uniqueidentifier,
            @DATALOADED bit = 0 output,
            @TSLONG bigint = 0 output,
            @FUNDINGPLAN nvarchar(154) = null output,
            @FUNDINGPLANID uniqueidentifier = null output,
            @FUNDINGREQUESTNAME nvarchar(100) = null output,
            @FUNDINGREQUESTTYPECODEID uniqueidentifier = null output,
            @FUNDINGREQUESTTYPECODE nvarchar(100) = null output,
            @STEPS xml = null output,
            @FISCALYEARSTARTDATE datetime = null output,
            @FISCALYEARENDDATE datetime = null output
          ) as begin
            set nocount on;

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

            set @STEPS = 
              (select 
                ID, 
                FUNDINGREQUESTOUTLINESTEPID, 
                OBJECTIVE, 
                FUNDRAISERID as OWNERID, 
                FUNDINGREQUESTSTAGECODEID,
                STATUSCODE, 
                EXPECTEDDATE,
                ACTUALDATE, 
                INTERACTIONTYPECODEID, 
                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,BINARY BASE64
                ) ADDITIONALFUNDRAISERS,
                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,BINARY BASE64
                ) PARTICIPANTS,
                COMMENT
              from
                dbo.INTERACTION
              where
                FUNDINGREQUESTID = @ID
              order by EXPECTEDDATE
              for xml raw('ITEM'),type,elements,root('STEPS'),BINARY BASE64)

              select 
                @DATALOADED = 1,
                @TSLONG = FUNDINGREQUEST.TSLONG,
                @FUNDINGPLANID = FUNDINGPLANID,
                @FUNDINGPLAN = dbo.UFN_FUNDINGPLAN_GETNAME(FUNDINGPLANID),
                @FUNDINGREQUESTNAME = dbo.UFN_FUNDINGREQUEST_GETNAME(ID),
                @FUNDINGREQUESTTYPECODE = dbo.UFN_FUNDINGREQUESTTYPECODE_GETDESCRIPTION(FUNDINGREQUESTTYPECODEID),
                @FUNDINGREQUESTTYPECODEID = FUNDINGREQUESTTYPECODEID
              from dbo.FUNDINGREQUEST
              where
                FUNDINGREQUEST.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;