USP_DATAFORMTEMPLATE_EDITLOAD_MOBILE_STEP

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@PROSPECTPLANID	uniqueidentifier	INOUT
@EXPECTEDDATE	datetime	INOUT
@ACTUALDATE	datetime	INOUT
@OWNERID	uniqueidentifier	INOUT
@INTERACTIONTYPECODEID	uniqueidentifier	INOUT
@OBJECTIVE	nvarchar(100)	INOUT
@STATUSCODE	tinyint	INOUT
@COMMENT	nvarchar(max)	INOUT
@PROSPECTPLANSTATUSCODEID	uniqueidentifier	INOUT
@PROSPECTNAME	nvarchar(700)	INOUT
@ADDITIONALFUNDRAISERS	xml	INOUT
@PARTICIPANTS	xml	INOUT
@INTERACTIONCATEGORYID	uniqueidentifier	INOUT
@INTERACTIONSUBCATEGORYID	uniqueidentifier	INOUT
@EXPECTEDSTARTTIME	UDT_HOURMINUTE	INOUT
@EXPECTEDENDTIME	UDT_HOURMINUTE	INOUT
@TIMEZONEENTRYID	uniqueidentifier	INOUT
@ISALLDAYEVENT	bit	INOUT
@ACTUALSTARTTIME	UDT_HOURMINUTE	INOUT
@ACTUALENDTIME	UDT_HOURMINUTE	INOUT
@PLANISACTIVE	bit	INOUT
@PROSPECTID	uniqueidentifier	INOUT
@LOCATION	nvarchar(300)	INOUT
@LOCATIONID	uniqueidentifier	INOUT
@OTHERLOCATION	nvarchar(300)	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MOBILE_STEP
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
  @PROSPECTPLANID uniqueidentifier = null output,
  @EXPECTEDDATE datetime = null output,
  @ACTUALDATE datetime = null output,
  @OWNERID uniqueidentifier = null output,
  @INTERACTIONTYPECODEID uniqueidentifier = null output,
  @OBJECTIVE nvarchar(100) = null output,
  @STATUSCODE tinyint = null output,
  @COMMENT nvarchar(max) = null output,
  @PROSPECTPLANSTATUSCODEID uniqueidentifier = null output,
  @PROSPECTNAME nvarchar(700) = null output,
  @ADDITIONALFUNDRAISERS xml = null output,
  @PARTICIPANTS xml = null output,
  @INTERACTIONCATEGORYID uniqueidentifier = null output,
  @INTERACTIONSUBCATEGORYID uniqueidentifier = null output,
  @EXPECTEDSTARTTIME dbo.UDT_HOURMINUTE = null output,
  @EXPECTEDENDTIME dbo.UDT_HOURMINUTE = null output,
  @TIMEZONEENTRYID uniqueidentifier = null output,
  @ISALLDAYEVENT bit = null output,
  @ACTUALSTARTTIME dbo.UDT_HOURMINUTE = null output,
  @ACTUALENDTIME dbo.UDT_HOURMINUTE = null output,
  @PLANISACTIVE bit = null output,
  @PROSPECTID uniqueidentifier = null output,
  @LOCATION nvarchar(300) = null output,
  @LOCATIONID uniqueidentifier = null output,
  @OTHERLOCATION nvarchar(300) = null output
) as begin
  set nocount on;

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

  select
    @DATALOADED = 1,
    @TSLONG = I.TSLONG,
    @PROSPECTPLANID = I.PROSPECTPLANID,
    @EXPECTEDDATE = I.EXPECTEDDATE,
    @ACTUALDATE = I.ACTUALDATE,
    @OWNERID = I.FUNDRAISERID,
    @INTERACTIONTYPECODEID = I.INTERACTIONTYPECODEID,
    @OBJECTIVE = I.OBJECTIVE,
    @STATUSCODE = I.STATUSCODE,
    @COMMENT = I.COMMENT,
    @PROSPECTPLANSTATUSCODEID = I.PROSPECTPLANSTATUSCODEID,
    @PROSPECTNAME = NF.NAME,
    @PARTICIPANTS = dbo.UFN_INTERACTION_PARTICIPANTS_TOITEMLISTXML(I.ID),
    @INTERACTIONCATEGORYID = INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID,
    @INTERACTIONSUBCATEGORYID = INTERACTIONSUBCATEGORY.ID,
    @EXPECTEDSTARTTIME = I.EXPECTEDSTARTTIME,
    @EXPECTEDENDTIME = I.EXPECTEDENDTIME,
    @ISALLDAYEVENT = I.ISALLDAYEVENT, 
    @TIMEZONEENTRYID = I.TIMEZONEENTRYID,
    @ACTUALSTARTTIME = I.ACTUALSTARTTIME,
    @ACTUALENDTIME = I.ACTUALENDTIME,
    @PLANISACTIVE = PP.ISACTIVE,
    @PROSPECTID = PP.PROSPECTID,
    @LOCATION = I.LOCATION
  from dbo.INTERACTION I
    inner join dbo.PROSPECTPLAN PP on I.PROSPECTPLANID = PP.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PROSPECTID) NF
    left outer join dbo.INTERACTIONSUBCATEGORY on I.INTERACTIONSUBCATEGORYID = INTERACTIONSUBCATEGORY.ID
  where I.ID = @ID;

  if @DATALOADED = 1 begin
    set @ADDITIONALFUNDRAISERS = (
      select
        FUNDRAISERS.ID,
        FUNDRAISERS.FUNDRAISERID,
        CONSTITUENT.FIRSTNAME,
        CONSTITUENT.KEYNAME
      from
        dbo.UFN_INTERACTION_ADDITIONALFUNDRAISERS(@ID) as FUNDRAISERS
      inner join
        dbo.CONSTITUENT on CONSTITUENT.ID = FUNDRAISERS.FUNDRAISERID
      order by
        FUNDRAISERS.FUNDRAISERID
      for xml raw('ITEM'), type, elements, root('ADDITIONALFUNDRAISERS'), binary base64
    );
  end

  return 0;
end