User login
Sponsored links
Recent blog posts
- How to resubmit on Integration Broker message with DONE status
- Message cannot be changed. Message referenced in runtime tables (Doc ID 660969.1)
- PeopleSoft Object Type List
- PeopleSoft Data Integrity Error (124,85)
- Unwanted Save Warnings in Display Only Pages
- Breadcrumb SQL to find the Navigation path for the component in the PIA
- Application Designer Index naming convention
- How to create Trigger in DB2
- Emplid increment automatically
- How can we romove multiple duplicate rows of data printing under each Print command in SQR Program?
Breadcrumb SQL to find the Navigation path for the component in the PIA
Thu, 11/17/2011 - 5:53am — Karthikeyan
Breadcrumb SQL:
WITH parent (breadcrumb, portal_label, portal_objname, portal_prntobjname, portal_reftype, component) AS
(SELECT varchar(rtrim(portal_label), 1000)
, portal_label
, portal_objname
, portal_prntobjname
, portal_reftype
, RIGHT(portal_urltext, length(portal_urltext) - locate('.', portal_urltext))
FROM psprsmdefn
WHERE portal_name = 'EMPLOYEE'
AND portal_reftype = 'C'
UNION ALL
SELECT varchar(rtrim(c.portal_label), 1000) || ' > ' || p.breadcrumb
, c.portal_label
, c.portal_objname
, c.portal_prntobjname
, c.portal_reftype
, p.component
FROM parent p
, psprsmdefn c
WHERE c.portal_reftype = 'F'
AND c.portal_name = 'EMPLOYEE'
AND c.portal_objname = p.portal_prntobjname)
SELECT breadcrumb, component FROM parent WHERE portal_prntobjname = 'PORTAL_ROOT_OBJECT'
AND char(component)='Component Name.GBL'
ORDER BY 1;
(SELECT varchar(rtrim(portal_label), 1000)
, portal_label
, portal_objname
, portal_prntobjname
, portal_reftype
, RIGHT(portal_urltext, length(portal_urltext) - locate('.', portal_urltext))
FROM psprsmdefn
WHERE portal_name = 'EMPLOYEE'
AND portal_reftype = 'C'
UNION ALL
SELECT varchar(rtrim(c.portal_label), 1000) || ' > ' || p.breadcrumb
, c.portal_label
, c.portal_objname
, c.portal_prntobjname
, c.portal_reftype
, p.component
FROM parent p
, psprsmdefn c
WHERE c.portal_reftype = 'F'
AND c.portal_name = 'EMPLOYEE'
AND c.portal_objname = p.portal_prntobjname)
SELECT breadcrumb, component FROM parent WHERE portal_prntobjname = 'PORTAL_ROOT_OBJECT'
AND char(component)='Component Name.GBL'
ORDER BY 1;
Bookmark/Search this post with
- Karthikeyan's blog
- Login to post comments
- 28408 reads
Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!
Unanswered Forum Posts
| Title | Under | Posted on |
|---|---|---|
| Component interface Error: no rows exist for the specified keys | PeopleSoft Technical | 03/15/2019 - 3:54am |
| ADD 24 months starting from current month.(peoplesoft) | PeopleSoft Functional | 07/29/2018 - 8:44pm |
| TRC values dropdown | PeopleSoft Technical | 04/04/2018 - 12:54am |
| how to find missing sequence in GRID and print the mising sequence number while saving through peoplecode | PeopleSoft Technical | 09/11/2017 - 4:49am |
Stay updated
CompShack is a user driven Dev2Dev IT community. Developers can ask questions, help answer questions, or just browse code and articles contributed by users. No registration required.
You can register if you want to contribute code samples, share your knowledge at your own blog, and become adored in the community!
About Karthikeyan
Job title
Associate Software Engineer
Poll
New forum topics
- Component interface Error: no rows exist for the specified keys
- ADD 24 months starting from current month.(peoplesoft)
- TRC values dropdown
- OHS is rendering XML instead of the view or data
- Search Results
- PS Query - Need Dependency and Prunable examples
- PS HCM AWE - Transaction routing to Requestor for approval, when he is the Delegatee by his 1 up Manager
- how to find missing sequence in GRID and print the mising sequence number while saving through peoplecode
Recent comments
- Re: PeopleCode Check Number and Check Letter Functions
3 years 17 weeks ago - Re: nVision Tabular Report through PIA with prompts
3 years 22 weeks ago - Re: PeopleSoft PeopleTools Meta-Tables
3 years 22 weeks ago - Re: PS Query - Need Dependency and Prunable examples
3 years 24 weeks ago - Re: Search Results
3 years 24 weeks ago - Re: How to schedule appengine using peoplecode
3 years 24 weeks ago - Re: Mark worked in Worklist page
3 years 27 weeks ago - Re: Calculate the sum for a grid field with out using row ...
3 years 28 weeks ago - Re: Invalid SNAC client version
3 years 31 weeks ago - Re: Trace Application Engine Processes Using Process Definitions
3 years 37 weeks ago
Comments
SELECT replace(navigation,'',' > ') "PIA Navigation"
, url
FROM (SELECT SYS_CONNECT_BY_PATH (a.portal_label, '>>') navigation
, '/EMPLOYEE/ERP/c/' || a.portal_uri_seg1 || '.' || a.portal_uri_seg2 || '.' || a.portal_uri_seg3 url
FROM (SELECT DISTINCT a.portal_name
, a.portal_label
, a.portal_objname
, a.portal_prntobjname
, a.portal_uri_seg1
, a.portal_uri_seg2
, a.portal_uri_seg3
, a.portal_reftype
FROM SYSADM.psprsmdefn a
WHERE portal_name = 'EMPLOYEE'
AND portal_objname <> portal_prntobjname
AND NOT EXISTS (SELECT 'x'
FROM SYSADM.psprsmsysattrvl
WHERE portal_name = a.portal_name
AND portal_reftype = a.portal_reftype
AND portal_objname = a.portal_objname
AND portal_attr_nam = 'your component name'
AND a.portal_objname NOT IN ('CO_NAVIGATION_COLLECTIONS' ,'PORTAL_BASE_DATA'))) a
WHERE portal_uri_seg2 = 'your component name'
START WITH a.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
CONNECT BY PRIOR a.portal_objname = a.portal_prntobjname)
WHERE navigation NOT LIKE '%Navigation Collections%'
This is the best and easy way to find the navigation.!!!!!!!!!!!!!!!!!!!!!!!
The query posted in comments section worked like a charm! So easy. Thanks!!!