Friday, November 16, 2012

A T-SQL Script That Lists Navigations For Component

How to find navigation paths for a PeopleSoft component has been a question frequently asked and there are already quite a few posts on Internet discussing about this. For example, you can visit here or here for some useful SQL scripts that can do this. However, these SQLs are written for Oracle and can't be used for MS SQL Server because they make use of an Oracle specific function SYS_CONNECT_BY_PATH() which is not implemented in MSSQL.


Recently I created a script using T-SQL, as requested by my colleagues who are working on MSSQL 2008. This script supports two search mode:

  • Search by Component Name. Assigning a component name to @COMP_NAME, the script will list out all navigation paths for that component. For example
     declare @COMP_NAME as nvarchar(18) = 'USERMAINT';

          Output will be

      Component: USERMAINT
      Menu Path: PeopleTools > Security > User Profiles > User Profiles

  • Search by Component Label. Assigning a component navigation label to@COMP_LABEL, the script will list our all components with the particular lable. For example
           declare @COMP_LABEL as nvarchar(30) = 'User Profiles';

           Output will be

      Component: USERMAINT 
      Menu Path: PeopleTools > Security > User Profiles > User Profiles
      Component: DSUSRPROF2 
      Menu Path: Enterprise Components > Directory Interface > Mappings > User Profiles          

           If 'Y' is set for @IGNORE_CASE you can even search for label without worrying about case sensitivity.

           The script is as follows:


/******************************************************************************
 Filename   : FIND_MENUPATHS.SQL
 Version    : 1.0
 Description: This script query PeopleSoft table PSPRSMDEFN to find out menu
              paths for a component
 Author     : devwfb@gmail.com
 Date       : 08 NOV 2012
 ******************************************************************************/

--
-- Parameters
--
--=============================================================================
-- QUERY MODE 1 - By Component
declare @COMP_NAME as nvarchar(18) = '';
--=============================================================================
-- QUERY MODE 2 - By Label
declare @COMP_LABEL as nvarchar(30) = '';
declare @IGNORE_CASE as nvarchar(1) = 'Y';

--
-- Constants & Variables
--
declare @PORTAL_NAME as nvarchar(30) = 'EMPLOYEE';
declare @SEP as nvarchar(3) = '>';
declare @component_name as nvarchar(30);
declare @prev_component as nvarchar(30) = '';
declare @portal_label as nvarchar(30);
declare @menu_path as nvarchar(max);
declare @parent_obj as nvarchar(30);

--
-- Verification for parameters
--
set @COMP_NAME = ltrim(rtrim(@COMP_NAME));
set @COMP_LABEL = ltrim(rtrim(@COMP_LABEL));
if @COMP_NAME = '' and @COMP_LABEL = '' 
begin
 print 'Alert: Please provide parameter Component Name or Component Label';
 return
end
else if @COMP_NAME <> '' and @COMP_LABEL <> '' 
begin
 print 'Alert: Component Name and Component Label are mutually exclusive. Please provide only one parameter';
 return
end

--
-- Search menu paths
--
if @COMP_NAME <> ''
 -- Mode 1
 declare cur_navi cursor for
  select PORTAL_PRNTOBJNAME, 
      PORTAL_LABEL,
      PORTAL_URI_SEG2
    from PSPRSMDEFN
   where PORTAL_NAME = @PORTAL_NAME
     and PORTAL_CREF_URLT = 'UPGE'
     and (PORTAL_URI_SEG2 = @COMP_NAME or PORTAL_URI_SEG2 like @COMP_NAME);
else if @IGNORE_CASE = 'Y'
 declare cur_navi cursor for
  select PORTAL_PRNTOBJNAME, 
      PORTAL_LABEL,
      PORTAL_URI_SEG2
    from PSPRSMDEFN
   where PORTAL_NAME = @PORTAL_NAME
     and PORTAL_CREF_URLT = 'UPGE'
     and (upper(PORTAL_LABEL) = upper(@COMP_LABEL) or upper(PORTAL_LABEL) like upper(@COMP_LABEL));
else
 declare cur_navi cursor for
  select PORTAL_PRNTOBJNAME, 
      PORTAL_LABEL,
      PORTAL_URI_SEG2
    from PSPRSMDEFN
   where PORTAL_NAME = @PORTAL_NAME
     and PORTAL_CREF_URLT = 'UPGE'
     and (PORTAL_LABEL = @COMP_LABEL or PORTAL_LABEL like @COMP_LABEL);
open cur_navi
fetch next from cur_navi into @parent_obj, @portal_label, @component_name
if @@fetch_status <> 0
begin
 close cur_navi
 deallocate cur_navi
 print 'No menu paths found matching given parameter.'
 return
end
while @@fetch_status = 0
begin
 set @menu_path = @portal_label;
 while 1=1
 begin
  select @parent_obj = PORTAL_PRNTOBJNAME, 
      @portal_label = PORTAL_LABEL
    from PSPRSMDEFN
   where PORTAL_NAME = @PORTAL_NAME
     and PORTAL_OBJNAME <> PORTAL_PRNTOBJNAME
     and PORTAL_OBJNAME = @parent_obj;
  if @parent_obj is null or @portal_label = 'Root' break;
  set @menu_path = @portal_label + ' ' + @SEP + ' ' + @menu_path
 end 
 if @component_name <> @prev_component 
 begin 
  print ''
  print 'Component: ' + @component_name;
  set @prev_component = @component_name;
 end
 print 'Menu Path: ' + @menu_path;
 fetch next from cur_navi into @parent_obj, @portal_label, @component_name
end
close cur_navi
deallocate cur_navi

78 comments:

Anonymous said...

qbpv save [url=http://www.louisvuittonoutletonlineshops.com]Replica Louis Vuitton[/url] zrni
xnpg [url=http://www.sacguccisolde.com/]Sac Gucci Pas cher[/url] mnpd =http://www.sacguccisolde.com
[url=http://www.saclongchampsolde.eu/]longchamp Soldes[/url]jdunhttp://www.saclongchampsolde.eu
mgxg [url=http://www.gafasdesolraybanbaratas.com/]Ray Ban Aviator[/url] http://www.gafasdesolraybanbaratas.com
tunw [url=http://www.saclouisvuittonsolde.com/]louis vuitton solde[/url] cdrf http://www.saclouisvuittonsolde.com
yifz [url=http://www.lunettesdesoleilraybans.com/]Ray Ban Wayfarer Pas Cher[/url] kogt http://www.lunettesdesoleilraybans.com
tjwq [url=http://www.raybansforsales.com/]Ray Ban 2140[/url] bjlq http://www.raybansforsales.com
fqwc [url=http://www.montblancpensonlinesale.com/]mont blanc pens[/url] rugz http://www.montblancpensonlinesale.com
dxlr [url=http://www.jordansolde.eu/]Air Jordan[/url] wmjb http://www.jordansolde.eu
ojbi [url=http://www.poloralphlaurensolde.eu]Doudoune Ralph Lauren[/url] lsvvhttp://www.poloralphlaurensolde.eu
http://www.poloralphlaurenonforsale.com kuea [url=http://www.poloralphlaurenonforsale.com/]Ralph Lauren UK[/url]

Anonymous said...

The metabolic type diet is an excellent eating program that actually ought to be considered more of your lifestyle change when compared to a typical diet. Along with your three major benefits, you will feel and appear healthier, have tons of their time, become more self confident, more productive and discover a happier, joyous and fulfilling life phen375 if you wish to accomplish fat loss success produce a commitment to workout no less than 3-4 times per week. Another significant advantage of Top Secret Fat Loss Secret is who's in addition has helped eliminate diabetes as well as other obesity-related medical conditions in numerous of the who used it. Lunch can be a side salad, a side of vegetables, fish or chicken, or perhaps a larger salad with tuna, or a yogurt and fruit http://www.phen375factsheet.com i promise in only 1 moment i'll share a couple of suggestions about just how you can do that rapid weight-loss. In fact, if you can, throw your microwave or lock up for 6 months [url=http://www.phen375factsheet.com/]phen375 review[/url] matthew mc - conaughey transforms to portray aids hero.

Anonymous said...

http://austinstevens.com/portal/?q=node/60065

Anonymous said...

best online payday loans http://legitpaydayloansonline3.com/ Fundpopog Payday Loans Online Stype [url=http://legitpaydayloansonline2.com/]Payday loans online[/url] payday loans online Hence,, having a help of cash effect so as to cope up you for fast in their back account within hours!!!Paying on time is you need something which shop experience deposit will cost more than a payday loan!

Anonymous said...

Legit Payday Loans Online http://www.legitpaydayloansonline1.com/ Fundpopog Payday Loans Online Stype [url=http://www.legitpaydayloansonline1.com/]payday loans online[/url] Payday loans online Pay much more than what the used worker, have several pledging of any collateral against money with lender.

Anonymous said...

payday loans online http://www.legitpaydayloansonline1.com/ Fundpopog [url=http://www.legitpaydayloansonline3.com/]payday loans online[/url] Stype Payday loans online payday loans online While extensions are allowed in many bill, you might be able automotive cases are 3.By taking proper care of your property, you payday many lender's office which might be inconvenient.

Anonymous said...

buy tramadol online tramadol hcl non-narcotic - tramadol 500mg

Anonymous said...

buy tramadol online tramadol withdrawal vomiting - drug study tramadol generic name

Anonymous said...

Hello. And Bye. Thank you very much.

Anonymous said...

buying xanax generic xanax mylan a4 - buy xanax online cod

Anonymous said...

buy tramadol online tramadol addiction buprenorphine - tramadol high feel like

Anonymous said...

buy tramadol online tramadol for dogs bluelight - tramadol and addiction

Anonymous said...

buy carisoprodol buy carisoprodol cheap - carisoprodol bluelight

Anonymous said...

buy tramadol online buy-tramadol - tramadol hcl narcotic

Anonymous said...

carisoprodol 350 mg soma carisoprodol schedule - carisoprodol dosage 700mg

Anonymous said...

buy tramadol tramadol hcl withdrawal symptoms - buy tramadol valium online

Anonymous said...

buy cialis online buy cialis online with paypal - cialis 4rx

Anonymous said...

cheap xanax vicodin xanax and alcohol - xanax no prescription review

Anonymous said...

xanax online xanax overdose statistics - xanax children

Anonymous said...

http://landvoicelearning.com/#51438 buy tramadol london - treatment of tramadol overdose

Anonymous said...

tramadol 100 mg buy tramadol online us pharmacy - tramadol 37.5 get high

Anonymous said...

http://landvoicelearning.com/#74967 tramadol for dogs how often - where to buy tramadol online usa

Anonymous said...

learn how to buy tramdadol tramadol hcl 50 mg addiction - tramadol withdrawal long

Anonymous said...

buy tramadol 180 tramadol 50 mg for back pain - 3 tramadol high

Anonymous said...

cheap tramadol online tramadol ultram 50 mg tablet - tramadol 50 mg vs advil

Anonymous said...

buy tramadol tramadol 50 mg ultram - tramadol 50 mg 377

Anonymous said...

learn how to buy tramdadol tramadol hydrochloride 50mg overdose - can you buy tramadol in spain

Anonymous said...

buy tramadol online tramadol addiction treatment home - what is better tramadol or ultram

Anonymous said...

buy tramadol buy tramadol with paypal - tramadol 50 mg is equal to

Anonymous said...

buy tramadol dangers tramadol addiction - buy tramadol dogs online

Anonymous said...

http://reidmoody.com/#96318 ativan high blood pressure - lorazepam 1mg nhs

Anonymous said...

tramadol online buy tramadol with a mastercard - buy tramadol hydrochloride online

Anonymous said...

buy cheap ativan ativan dosage weight - ativan 4 mg daily

Anonymous said...

I'm getting a new computer but don't want to lose
my Firefox bookmarks. Can there be an easy way in order
to save a record of all the URLs inside my Bookmarks after which quickly upload those
to Firefox on my new computer?.

my web site: transvaginal mesh lawsuit
Also see my website: transvaginal mesh

Anonymous said...

oZzy moved away ever before, I miss his cock, that i'm always hungry for sex.
FUCK MY PUSSY!

My site :: hcg injections
Also see my page - hcg injections

Anonymous said...

http://ranchodelastortugas.com/#61301 xanax 1 mg and weed - xanax online fedex

Anonymous said...

buy tramadol online tramadol high duration - tramadol for dogs nausea

Anonymous said...

http://staam.org/#50589 nebenwirkungen von tramadol 100mg - buy tramadol with paypal

Anonymous said...

xanax online online eczane xanax - how much xanax and alcohol is safe

Anonymous said...

Hurrah! In the end I got a weblog from where I be capable of really get useful data concerning my study and knowledge.


Here is my homepage; cheap mac makeup

Anonymous said...

Hurrah! In the end I got a weblog from where I be capable of
really get useful data concerning my study and knowledge.

Here is my blog - cheap mac makeup

Anonymous said...

You actually make it appear so easy along
with your presentation but I in finding this topic to be
really something which I feel I would never understand.
It kind of feels too complex and extremely broad
for me. I'm taking a look ahead for your next post, I'll attempt to
get the cling of it!

Visit my page Cheap Ray Ban Sunglasses

Anonymous said...

Hi there, just became aware of your blog through Google, and found that it is truly informative.

I'm going to watch out for brussels. I will appreciate if you continue this in future. A lot of people will be benefited from your writing. Cheers!

Also visit my blog http://buymaxoderm.eklablog.com

Anonymous said...

Have you ever thought about adding a little bit more than just your
articles? I mean, what you say is valuable and all.
However think of if you added some great pictures
or videos to give your posts more, "pop"! Your content is excellent but with pics and video clips, this
blog could certainly be one of the most beneficial in its niche.
Superb blog!

Also visit my blog: http://www.infom.cc/Index.php?do=/profile-37109/info

Anonymous said...

In fact when someone doesn't know afterward its up to other users that they will assist, so here it happens.

My website: dental Implant Costs

Anonymous said...

I am sure this article has touched all the internet viewers, its really really fastidious paragraph on building up new weblog.


Also visit my homepage ミュウミュウ店舗

Anonymous said...

Wonderful blog! I found it while searching on Yahoo News.
Do you have any tips on how to get listed in Yahoo News?
I've been trying for a while but I never seem to get there! Cheers

my blog post - http://www.miumiuoutletshopx.com/

Anonymous said...

Do you mind if I quote a few of your posts as long as I provide credit and sources back to your webpage?
My blog site is in the very same niche as yours and my visitors would really benefit
from some of the information you provide here. Please let me know if
this alright with you. Cheers!

my weblog: http://somalinow.net/

Anonymous said...

Why users still use to read news papers when in this technological world all is available on net?



my weblog クロエ店舗

Anonymous said...

Hello! This is my 1st comment here so I just wanted to give a quick shout out
and say I really enjoy reading through your blog posts.
Can you recommend any other blogs/websites/forums
that deal with the same topics? Many thanks!


Also visit my page - miu miu 店舗

Anonymous said...

Hi, after reading this awesome article i am also cheerful to share my knowledge here with friends.


Have a look at my web-site クロエ店舗

Anonymous said...

Hi to all, how is everything, I think every one is getting more from this website, and your views are pleasant in support of new
users.

my web site :: ミュウミュウ財布

Anonymous said...

Wonderful post however I was wondering if you could write a litte more on this subject?
I'd be very grateful if you could elaborate a little bit further. Thanks!

Also visit my blog online real estate directories

Anonymous said...

What's up to every one, the contents existing at this web page are truly amazing for people knowledge, well, keep up the good work fellows.

my website; academic term papers

Anonymous said...

I will right away grab your rss as I can't find your e-mail subscription link or e-newsletter service. Do you have any? Please permit me understand so that I could subscribe. Thanks.

My site: writing a paper high

Anonymous said...

The article offers provеn benefiсіal tο myself.
It’ѕ really uѕеful and you're simply naturally quite experienced in this region. You have got opened our eyes to be able to numerous opinion of this subject matter along with interesting and reliable articles.

Have a look at my homepage; buy viagra online

Anonymous said...

Spot on with this write-up, I absolutely feel this web site needs
a great deal more attention. I'll probably be returning to read through more, thanks for the info!

My homepage :: african mango pure

Anonymous said...

This blog was... how do you say it? Relevant!! Finally I
have found something that helped me. Appreciate it!

Also visit my site :: alteril review

Anonymous said...

This is really interesting, You're a very skilled blogger. I've joined your rss feed and look forward to seeking more of
your excellent post. Also, I have shared your website in my social
networks!

Also visit my web page :: tushermanastebuscan.blogspot.com

Anonymous said...

Superb, what a blog it is! This website provides helpful data to us, keep it
up.

My website クリスチャンルブタン

Anonymous said...

Hey There. I found your blog the usage of msn. That is a very well written article.
I'll make sure to bookmark it and come back to read extra of your useful info. Thanks for the post. I will certainly return.

my site :: コーチバッグ

Anonymous said...

I really like your blog.. very nice colors & theme. Did you
make this website yourself or did you hire someone to do it
for you? Plz answer back as I'm looking to design my own blog and would like to find out where u got this from. thanks

my weblog - college admission essay

Anonymous said...

Great beat ! I wish to apprentice while you amend your site, how could i subscribe for a blog web site?
The account helped me a acceptable deal. I were a little bit acquainted of this your broadcast offered
brilliant clear idea

Also visit my blog post: クロエ店舗

Anonymous said...

Oh my goodness! Amazing article dude! Many thanks, However
I am going through problems with your RSS. I don't understand the reason why I am unable to subscribe to it. Is there anyone else having identical RSS issues? Anyone that knows the answer will you kindly respond? Thanx!!

Have a look at my page; buy Genf20 Plus

Anonymous said...

You have made some decent points there. I checked
on the web to find out more about the issue and found most people will go along with your views on this website.


Also visit my web blog ... research paper on volcanoes

Anonymous said...

Useful information. Fortunate me I discovered your website by accident, and I'm surprised why this twist of fate did not took place in advance! I bookmarked it.

my website ... buy breast actives

Anonymous said...

This site was... how do I say it? Relevant!! Finally I've found something which helped me. Kudos!

my web page: www.Cm168.info

Anonymous said...

It's going to be finish of mine day, but before ending I am reading this great piece of writing to increase my experience.

Here is my blog post :: film discussion materials education

Anonymous said...

This site truly has all of the information and facts I wanted about this subject and didn't know who to ask.

Look into my web page - buy smoke deter

Anonymous said...

I have read so many posts on the topic of the blogger lovers
but this post is really a good article, keep it up.


Feel free to surf to my blog post ... virility ex price

Anonymous said...

Really no matter if someone doesn't know after that its up to other visitors that they will assist, so here it occurs.

My homepage ... aciphex

Anonymous said...

Remarkable! Its actually amazing post, I have got much clear idea concerning from this article.


my blog meratol

Anonymous said...

Thank you for sharing your info. I really appreciate
your efforts and I am waiting for your further post thank you once again.


my page; buy proactol

Anonymous said...

Thanks for any other informative website. Where else may
just I am getting that type of information written in such an
ideal approach? I have a mission that I am just now working on, and I've been at the glance out for such information.

Look at my web site; what causes ed

Anonymous said...

It's appropriate time to make a few plans for the longer term and it is time to be happy. I've learn this submit
and if I may I want to suggest you some attention-grabbing
things or advice. Perhaps you could write next articles referring to this article.

I want to learn even more issues approximately it!


Also visit my webpage :: http://buyidollips.beep.com

Anonymous said...

What a stuff of un-ambiguity and preserveness of valuable know-how about
unpredicted emotions.

my website ... real estate agent directories

Anonymous said...

Good day! Do you know if they make any plugins to safeguard
against hackers? I'm kinda paranoid about losing everything I've
worked hard on. Any tips?

Feel free to surf to my web site :: idol lip

Anonymous said...

I really like what you guys tend to be up too. This sort of clever work and coverage!

Keep up the awesome works guys I've you guys
to my own blogroll.

My web blog ... car finance loan re