USP_PROSPECTNAVIGATIONTREENODES

                CREATE procedure dbo.USP_PROSPECTNAVIGATIONTREENODES
                (
                    @CURRENTAPPUSERID uniqueidentifier,
                    @PROSPECTID uniqueidentifier,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                )
                as
                    set nocount on;

                    with PLAN_CTE as (
                        select
                            PROSPECTPLAN.ID,
                            PROSPECTPLAN.PROSPECTPLANTYPECODEID,
                            PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID                            
                        from dbo.PROSPECTPLAN                    
                        where 
                            PROSPECTPLAN.PROSPECTID = @PROSPECTID 
                            and (
                                select count(*) 
                                from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.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                    
                    )
                    select 
                        null as PLANDATE,
                        'Prospectplans' as DESCRIPTION,
                        1 as HASRESOURCEKEY,
                        'RES:ShowDetails' as IMAGEKEY,
                        '00000000-0000-0000-0000-000000000000' as PAGEID, 
                        '00000000-0000-0000-0000-000000000000' as CONTEXTID,
                        null as PARENTNODEID,
                        '1' as NODEID

                    union all    

                    select 
                        null as PLANDATE,
                        (select DESCRIPTION from dbo.PROSPECTPLANTYPECODE where ID = PLANS.PROSPECTPLANTYPECODEID)
                            + case when PLANS.PRIMARYMANAGERFUNDRAISERID is null then '' else ' (' + (select dbo.UFN_CONSTITUENTSEARCH_DISPLAYNAME (PLANS.PRIMARYMANAGERFUNDRAISERID)) + ')' end
                            as DESCRIPTION, 
                        0 as HASRESOURCEKEY,
                        'RES:ShowDetails' as IMAGEKEY, 
                        '9EA482BE-776F-45c9-990C-A152783814F9' as PAGEID, 
                        cast(PLANS.ID as nvarchar(36)) as CONTEXTID,
                        '1' as PARENTNODEID,
                        cast(PLANS.ID as nvarchar(36)) as NODEID
                    from PLAN_CTE PLANS

                    union all

                    select 
                        null as PLANDATE,
                        'Steps' as DESCRIPTION,
                        1 as HASRESOURCEKEY,
                        'RES:bullet_diamond' as IMAGEKEY, 
                        '00000000-0000-0000-0000-000000000000' as PAGEID, 
                        '00000000-0000-0000-0000-000000000000' as CONTEXTID,
                        cast(PLANS.ID as nvarchar(36)) as PARENTNODEID,
                        '111-' + cast(PLANS.ID as nvarchar(36)) as NODEID
                    from PLAN_CTE PLANS                    

                    union all

                    select 
                        null as PLANDATE,
                        'Opportunities' as DESCRIPTION,
                        1 as HASRESOURCEKEY,
                        'RES:bullet_diamond' as IMAGEKEY, 
                        '00000000-0000-0000-0000-000000000000' as PAGEID, 
                        '00000000-0000-0000-0000-000000000000' as CONTEXTID,
                        cast(PLANS.ID as nvarchar(36)) as PARENTNODEID,
                        '222-' + cast(PLANS.ID as nvarchar(36)) as NODEID
                        from PLAN_CTE PLANS
                        /* AAW 1/11/10: Hiding opportunities unless Enterprise or Advanced Prospect Management or Basic 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:InstalledProduct ID="ab25cd89-7288-4605-b0ea-48961960ec06" />
                            </common:InstalledProductList>'
                        ) = 1

                    union all

                    select 
                        null as PLANDATE,
                        'Plannedgifts' as DESCRIPTION,
                        1 as HASRESOURCEKEY,
                        'RES:ShowDetails' as IMAGEKEY,
                        '00000000-0000-0000-0000-000000000000' as PAGEID, 
                        '00000000-0000-0000-0000-000000000000' as CONTEXTID,
                        cast(PLANS.ID as nvarchar(36)) as PARENTNODEID,
                        '333-' + cast(PLANS.ID as nvarchar(36)) as NODEID
                        from PLAN_CTE PLANS
                        /* 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 
                        I.DATE as PLANDATE,
                        I.OBJECTIVE as DESCRIPTION,
                        0 as HASRESOURCEKEY,
                        'RES:bullet_diamond' as IMAGEKEY, 
                        '66081E86-940D-4be9-ACE6-DE6D4B45097C' as PAGEID, 
                        cast(I.ID as nvarchar(36)) as CONTEXTID,
                        '111-' + cast(PLANS.ID as nvarchar(36)) as PARENTNODEID,
                        null as NODEID
                    from 
                        dbo.INTERACTION as I
                    inner join 
                        PLAN_CTE PLANS
                            on I.PROSPECTPLANID = PLANS.ID
                    --order by 

                    --    I.COMPLETED, I.DATE


                    union all    

                    select 
                        O.ASKDATE as PLANDATE,
                        O.STATUS as DESCRIPTION,
                        0 as HASRESOURCEKEY,
                        'RES:bullet_diamond' as IMAGEKEY, 
                        '8B97A047-E267-4e9f-822D-F74CBF1447E5' as PAGEID, 
                        cast(O.ID as nvarchar(36)) as CONTEXTID,
                        '222-' + cast(PLANS.ID as nvarchar(36)) as PARENTNODEID,
                        null as NODEID
                    from 
                        dbo.OPPORTUNITY as O
                    inner join 
                        PLAN_CTE PLANS
                            on O.PROSPECTPLANID = PLANS.ID

                    union all

                    select 
                        PG.GIFTDATE as PLANDATE,
                        PG.VEHICLE as DESCRIPTION,
                        0 as HASRESOURCEKEY,
                        'RES:ShowDetails' as IMAGEKEY, 
                        'F031A07D-521F-4A83-B31B-B3E0E5AF99E8' as PAGEID, 
                        cast(PG.ID as nvarchar(36)) as CONTEXTID,
                        '333-' + cast(PLANS.ID as nvarchar(36)) as PARENTNODEID,
                        null as NODEID
                    from 
                        dbo.PLANNEDGIFT as PG
                    inner join 
                        PLAN_CTE PLANS
                            on PG.PROSPECTPLANID = PLANS.ID