USP_DATAFORMTEMPLATE_VIEW_PROSPECTPLANREQUESTDETAIL

The load procedure used by the view dataform template "Prospect Plan 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.
@PROSPECTPLAN	nvarchar(100)	INOUT	Plan name
@PROSPECTPLANTYPE	nvarchar(100)	INOUT	Plan type
@PRIMARYMANAGER	nvarchar(154)	INOUT	Primary manager
@PRIMARYMANAGERSTARTDATE	datetime	INOUT	Start date
@SECONDARYMANAGER	nvarchar(154)	INOUT	Secondary manager
@SECONDARYMANAGERSTARTDATE	datetime	INOUT	Start date
@SECONDARYFUNDRAISERS	xml	INOUT	SECONDARYFUNDRAISERS
@PROSPECTPLAN_PARTICIPANTS	xml	INOUT	PROSPECTPLAN_PARTICIPANTS
@SITES	xml	INOUT	SITES
@NARRATIVE	nvarchar(max)	INOUT	Narrative

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PROSPECTPLANREQUESTDETAIL
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @PROSPECTPLAN nvarchar(100) = null output,
    @PROSPECTPLANTYPE nvarchar(100) = null output,
    @PRIMARYMANAGER nvarchar(154) = null output,
    @PRIMARYMANAGERSTARTDATE datetime = null output,
    @SECONDARYMANAGER nvarchar(154) = null output,
    @SECONDARYMANAGERSTARTDATE datetime = null output,
    @SECONDARYFUNDRAISERS xml = null output,
    @PROSPECTPLAN_PARTICIPANTS xml = null output,
    @SITES xml = null output,
    @NARRATIVE nvarchar(max) = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    declare @ITEMSLISTXML xml

    select 
        @DATALOADED = 1,
        @PROSPECTPLAN = PROSPECTPLANREQUEST.NAME,
        @PROSPECTPLANTYPE = PROSPECTPLANTYPECODE.DESCRIPTION,
        @PRIMARYMANAGER = NF_PRIMARYMANAGER.NAME,
        @PRIMARYMANAGERSTARTDATE = PROSPECTPLANREQUEST.PRIMARYMANAGERSTARTDATE,
        @SECONDARYMANAGER = NF_SECONDARYMANAGER.NAME,
        @SECONDARYMANAGERSTARTDATE = PROSPECTPLANREQUEST.SECONDARYMANAGERSTARTDATE,
        @NARRATIVE = PROSPECTPLANREQUEST.NARRATIVE
    from dbo.PROSPECTPLANREQUEST
        inner join dbo.PROSPECTPLANTYPECODE on PROSPECTPLANREQUEST.PROSPECTPLANTYPECODEID = PROSPECTPLANTYPECODE.ID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLANREQUEST.PRIMARYMANAGERFUNDRAISERID) NF_PRIMARYMANAGER
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLANREQUEST.SECONDARYMANAGERFUNDRAISERID) NF_SECONDARYMANAGER
    where PROSPECTPLANREQUEST.ID = @ID

    select @ITEMSLISTXML = PROSPECTPLAN_PARTICIPANTS from dbo.PROSPECTPLANREQUEST where ID = @ID

    declare @TempParticipants table (
        CONSTITUENTID uniqueidentifier,
        PLANPARTICIPANTROLECODEID uniqueidentifier
    )

    insert into @TempParticipants (
        CONSTITUENTID,
        PLANPARTICIPANTROLECODEID
    )

    select
        T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') AS 'CONSTITUENTID',
        T.c.value('(PLANPARTICIPANTROLECODEID)[1]','uniqueidentifier') AS 'PLANPARTICIPANTROLECODEID'
    FROM @ITEMSLISTXML.nodes('/PROSPECTPLAN_PARTICIPANTS/ITEM') T(c)

    set @PROSPECTPLAN_PARTICIPANTS = (select NF.NAME as CONSTITUENT, PLANPARTICIPANTROLECODE.DESCRIPTION as PLANPARTICIPANTROLECODE
                                   from @TempParticipants TP
                                   left outer join dbo.PLANPARTICIPANTROLECODE on TP.PLANPARTICIPANTROLECODEID = PLANPARTICIPANTROLECODE.ID
                                   cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(TP.CONSTITUENTID) NF
                                   for xml raw('ITEM'),type,elements,root('PROSPECTPLAN_PARTICIPANTS'),BINARY BASE64);

    select @ITEMSLISTXML = SECONDARYFUNDRAISERS from dbo.PROSPECTPLANREQUEST where ID = @ID

    declare @TempSecondary table (
        FUNDRAISERID uniqueidentifier,
        SOLICITORROLECODEID uniqueidentifier
    )

    insert into @TempSecondary (
        FUNDRAISERID,
        SOLICITORROLECODEID
    )
    select
        T.c.value('(FUNDRAISERID)[1]','uniqueidentifier') AS 'FUNDRAISERID',
        T.c.value('(SOLICITORROLECODEID)[1]','uniqueidentifier') AS 'SOLICITORROLECODEID'
    FROM @ITEMSLISTXML.nodes('/SECONDARYFUNDRAISERS/ITEM') T(c)

    set @SECONDARYFUNDRAISERS = (select NF.NAME as FUNDRAISER, SOLICITORROLECODE.DESCRIPTION as SOLICITORROLECODE
                                   from @TempSecondary TS
                                   left outer join dbo.SOLICITORROLECODE on TS.SOLICITORROLECODEID = SOLICITORROLECODE.ID
                                   cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(TS.FUNDRAISERID) NF
                                   for xml raw('ITEM'),type,elements,root('SECONDARYFUNDRAISERS'),BINARY BASE64);

    select @ITEMSLISTXML = SITES from dbo.PROSPECTPLANREQUEST where ID = @ID

    declare @TempSites table (
        SITEID uniqueidentifier
    )

    insert into @TempSites (
        SITEID
    )

    select
        T.c.value('(SITEID)[1]','uniqueidentifier') AS 'SITEID'
    FROM @ITEMSLISTXML.nodes('/SITES/ITEM') T(c)

    set @SITES = (select SITE.NAME as NAME
               from @TempSites TS
               inner join dbo.SITE on TS.SITEID = SITE.ID
               for xml raw('ITEM'),type,elements,root('SITES'),BINARY BASE64);

    return 0;