USP_DATAFORMTEMPLATE_VIEW_PROSPECTASSIGNMENTREQUESTDETAIL

The load procedure used by the view dataform template "Prospect Assignment Request Detail 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.
@PROSPECT	nvarchar(154)	INOUT	Prospect
@PROSPECTPLAN	nvarchar(100)	INOUT	Plan
@NEWFUNDRAISER	nvarchar(154)	INOUT	New Fundraiser
@CURRENTFUNDRAISER	nvarchar(154)	INOUT	Current Fundraiser
@TYPE	nvarchar(100)	INOUT	Fundraiser Role
@STARTDATE	datetime	INOUT	Start date
@NEWSOLICITORROLECODE	nvarchar(100)	INOUT	Secondary Role
Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PROSPECTASSIGNMENTREQUESTDETAIL
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @PROSPECT nvarchar(154) = null output,
    @PROSPECTPLAN nvarchar(100) = null output,
    @NEWFUNDRAISER nvarchar(154) = null output,
  @CURRENTFUNDRAISER nvarchar(154) = null output,
  @TYPE nvarchar(100) = null output,
  @STARTDATE datetime = null output,
  @NEWSOLICITORROLECODE nvarchar(100) = null output
)
as begin
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

  declare @ASSIGNMENTTYPECODE int
  select @ASSIGNMENTTYPECODE = TYPECODE from dbo.PROSPECTASSIGNMENTREQUEST where ID = @ID

  if @ASSIGNMENTTYPECODE = 0
        select 
            @DATALOADED = 1,
            @NEWFUNDRAISER = NF_NEWFUNDRAISER.NAME,
            @CURRENTFUNDRAISER = NF_CURRENTFUNDRAISER.NAME,
            @TYPE = PROSPECTASSIGNMENTREQUEST.TYPE,
            @PROSPECT = NF_PROSPECTNAME.NAME,
            @PROSPECTPLAN = null,
            @NEWSOLICITORROLECODE = null,
            @STARTDATE = PROSPECTASSIGNMENTREQUEST.STARTDATE
        from dbo.PROSPECTASSIGNMENTREQUEST
            inner join PROSPECTASSIGNMENTREQUESTPROSPECT on PROSPECTASSIGNMENTREQUEST.ID = PROSPECTASSIGNMENTREQUESTPROSPECT.ID
            inner join dbo.PROSPECT on PROSPECTASSIGNMENTREQUESTPROSPECT.PROSPECTID = PROSPECT.ID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTASSIGNMENTREQUEST.NEWFUNDRAISERID) NF_NEWFUNDRAISER
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.ID) NF_PROSPECTNAME
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.PROSPECTMANAGERFUNDRAISERID) NF_CURRENTFUNDRAISER
        where PROSPECTASSIGNMENTREQUEST.ID = @ID

    else if @ASSIGNMENTTYPECODE = 4 
        select 
            @DATALOADED = 1,
            @NEWFUNDRAISER = NF_NEWFUNDRAISER.NAME,
            @CURRENTFUNDRAISER = NF_CURRENTFUNDRAISER.NAME,
            @TYPE = PROSPECTASSIGNMENTREQUEST.TYPE,
            @PROSPECT = NF_PROSPECTNAME.NAME,
            @PROSPECTPLAN = PROSPECTPLAN.NAME,
            @NEWSOLICITORROLECODE = dbo.UFN_SOLICITORROLECODE_GETDESCRIPTION(PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.SOLICITORROLECODEID),
            @STARTDATE = PROSPECTASSIGNMENTREQUEST.STARTDATE
        from PROSPECTASSIGNMENTREQUEST
            inner join PROSPECTASSIGNMENTREQUESTPROSPECTPLAN on PROSPECTASSIGNMENTREQUEST.ID = PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.ID
            inner join dbo.PROSPECTPLAN on PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.PROSPECTPLANID = PROSPECTPLAN.ID
            inner join dbo.PROSPECT on PROSPECTPLAN.PROSPECTID = PROSPECT.ID
            left outer join dbo.SECONDARYFUNDRAISER on SECONDARYFUNDRAISER.ID = PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.SECONDARYFUNDRAISERID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTASSIGNMENTREQUEST.NEWFUNDRAISERID) NF_NEWFUNDRAISER
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.ID) NF_PROSPECTNAME
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SECONDARYFUNDRAISER.FUNDRAISERID) NF_CURRENTFUNDRAISER
        where PROSPECTASSIGNMENTREQUEST.ID = @ID

    else if @ASSIGNMENTTYPECODE = 2
        select 
            @DATALOADED = 1,
            @NEWFUNDRAISER = NF_NEWFUNDRAISER.NAME,
            @CURRENTFUNDRAISER = NF_CURRENTFUNDRAISER.NAME,
            @TYPE = PROSPECTASSIGNMENTREQUEST.TYPE,
            @PROSPECT = NF_PROSPECTNAME.NAME,
            @PROSPECTPLAN = PROSPECTPLAN.NAME,
            @NEWSOLICITORROLECODE = null,
            @STARTDATE = PROSPECTASSIGNMENTREQUEST.STARTDATE
        from PROSPECTASSIGNMENTREQUEST
            inner join PROSPECTASSIGNMENTREQUESTPROSPECTPLAN on PROSPECTASSIGNMENTREQUEST.ID = PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.ID
            inner join dbo.PROSPECTPLAN on PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.PROSPECTPLANID = PROSPECTPLAN.ID
            inner join dbo.PROSPECT on PROSPECTPLAN.PROSPECTID = PROSPECT.ID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTASSIGNMENTREQUEST.NEWFUNDRAISERID) NF_NEWFUNDRAISER
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.ID) NF_PROSPECTNAME
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID) NF_CURRENTFUNDRAISER
        where PROSPECTASSIGNMENTREQUEST.ID = @ID 

      else
        select 
            @DATALOADED = 1,
            @NEWFUNDRAISER = NF_NEWFUNDRAISER.NAME,
            @CURRENTFUNDRAISER = NF_CURRENTFUNDRAISER.NAME,
            @TYPE = PROSPECTASSIGNMENTREQUEST.TYPE,
            @PROSPECT = NF_PROSPECTNAME.NAME,
            @PROSPECTPLAN = PROSPECTPLAN.NAME,
            @NEWSOLICITORROLECODE = null,
            @STARTDATE = PROSPECTASSIGNMENTREQUEST.STARTDATE
        from PROSPECTASSIGNMENTREQUEST
            inner join PROSPECTASSIGNMENTREQUESTPROSPECTPLAN on PROSPECTASSIGNMENTREQUEST.ID = PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.ID
            inner join dbo.PROSPECTPLAN on PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.PROSPECTPLANID = PROSPECTPLAN.ID
            inner join dbo.PROSPECT on PROSPECTPLAN.PROSPECTID = PROSPECT.ID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTASSIGNMENTREQUEST.NEWFUNDRAISERID) NF_NEWFUNDRAISER
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.ID) NF_PROSPECTNAME
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID) NF_CURRENTFUNDRAISER
        where PROSPECTASSIGNMENTREQUEST.ID = @ID 
  end
    return 0;