USP_DATALIST_PROSPECTNAVIGATIONTREE
Returns the navigation tree for the Prospect Page
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @PROSPECTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @SECURITYFEATUREID | uniqueidentifier | IN | Input parameter indicating the ID of the feature to use for site security checking. |
| @SECURITYFEATURETYPE | tinyint | IN | Input parameter indicating the type of the feature to use for site security checking. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_PROSPECTNAVIGATIONTREE
(
@CURRENTAPPUSERID uniqueidentifier,
@PROSPECTID uniqueidentifier,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
)
as
set nocount on;
-- todo: building dates on the server is problematic - need to consider the current client culture
select
'Prospect Plans' as CAPTION,
'RES:ShowDetails' as IMAGEKEY,
'00000000-0000-0000-0000-000000000000' as PAGEID,
'00000000-0000-0000-0000-000000000000' as CONTEXTID,
(
select
(select DESCRIPTION from dbo.PROSPECTPLANTYPECODE where ID = PP.PROSPECTPLANTYPECODEID)
+ case when PRIMARYMANAGERFUNDRAISERID is null then '' else ' (' + (select dbo.UFN_CONSTITUENTSEARCH_DISPLAYNAME (PRIMARYMANAGERFUNDRAISERID)) + ')' end
as CAPTION,
'RES:ShowDetails' as IMAGEKEY,
'9EA482BE-776F-45c9-990C-A152783814F9' as PAGEID,
PP.ID as CONTEXTID,
(
select *
from
(
select
'Steps' as CAPTION,
'RES:bullet_diamond' as IMAGEKEY,
'00000000-0000-0000-0000-000000000000' as PAGEID,
'00000000-0000-0000-0000-000000000000' as CONTEXTID,
(
select
convert(nvarchar, I.DATE, 1) + ' - ' + I.OBJECTIVE as CAPTION,
'RES:bullet_diamond' as IMAGEKEY,
'66081E86-940D-4be9-ACE6-DE6D4B45097C' as PAGEID,
I.ID as CONTEXTID
from
dbo.INTERACTION as I
where
I.PROSPECTPLANID = PP.ID
order by
I.COMPLETED, I.DATE
for xml raw('NODE'), elements, type
) as NODES
union all
select
'Opportunities' as CAPTION,
'RES:bullet_diamond' as IMAGEKEY,
'00000000-0000-0000-0000-000000000000' as PAGEID,
'00000000-0000-0000-0000-000000000000' as CONTEXTID,
(
select
coalesce(convert(nvarchar, O.ASKDATE, 1) + ' - ', '') + O.STATUS as CAPTION,
'RES:bullet_diamond' as IMAGEKEY,
'8B97A047-E267-4e9f-822D-F74CBF1447E5' as PAGEID,
O.ID as CONTEXTID
from
dbo.OPPORTUNITY as O
where
O.PROSPECTPLANID = PP.ID
for xml raw('NODE'), elements, type
) as NODES
/* AAW 1/11/10: Hiding opportunities and planned gifts unless Enterprise or Advanced Prospect Management is unlocked. */
where dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED
(
'<common:InstalledProductList xmlns:common="bb_appfx_commontypes">
<common:InstalledProduct ID="3117d2c8-7f46-42f2-abeb-b654f2f63046" />
<common:InstalledProduct ID="0A07AA22-ED88-42B7-8C55-D80DF4F4CEA2" />
</common:InstalledProductList>'
) = 1
union all
select
'Planned Gifts' as CAPTION,
'RES:ShowDetails' as IMAGEKEY,
'00000000-0000-0000-0000-000000000000' as PAGEID,
'00000000-0000-0000-0000-000000000000' as CONTEXTID,
(
select
coalesce(convert(nvarchar, PG.GIFTDATE, 1) + ' - ', '')
+ PG.VEHICLE
as CAPTION,
'RES:ShowDetails' as IMAGEKEY,
'F031A07D-521F-4A83-B31B-B3E0E5AF99E8' as PAGEID,
PG.ID as CONTEXTID,
null as NODES
from
dbo.PLANNEDGIFT as PG
where
PG.PROSPECTPLANID = PP.ID
for xml raw('NODE'), elements, type
) as NODES
/* AAW 1/11/10: Hiding opportunities and planned gifts unless Enterprise or Advanced Prospect Management is unlocked. */
where dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED
(
'<common:InstalledProductList xmlns:common="bb_appfx_commontypes">
<common:InstalledProduct ID="3117d2c8-7f46-42f2-abeb-b654f2f63046" />
<common:InstalledProduct ID="0A07AA22-ED88-42B7-8C55-D80DF4F4CEA2" />
</common:InstalledProductList>'
) = 1
) T for xml raw('NODE'), elements, type
) as NODES
from
dbo.PROSPECTPLAN as PP
where
PP.PROSPECTID = @PROSPECTID
and (
select count(*)
from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as PROSPECTPLANSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
) > 0
for xml raw('NODE'), root('NODES'), elements, type
) as NODES
order by
1 desc