User login
Sponsored links
Recent blog posts
- 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?
- How can we call Dynamic Calling section in Peoplesoft Application Engine program?
- How can we write multiple SQL Commands/Statements under one Action by using one function?
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
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 |
|---|---|---|
| Required Peoplesoft HRMS Functional Training | PeopleSoft Functional | 05/07/2013 - 10:29pm |
| Error while saving Voucher | PeopleSoft Functional | 05/02/2013 - 1:55am |
| santosh asking que | PeopleSoft Technical | 04/30/2013 - 10:22am |
| How can i implement Email functionality to alerts in peoplesoft. | PeopleSoft Technical | 04/30/2013 - 5:01am |
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
How Is Site Load Performance?
Pages Load Fast
38%
Pages Load Somewhat Fast
25%
Pages Load Somewhat Slow
12%
Pages Load Slow
7%
Pages Load Very Slow
17%
Total votes: 465
New forum topics
- Required Peoplesoft HRMS Functional Training
- Error while saving Voucher
- santosh asking que
- How can i implement Email functionality to alerts in peoplesoft.
- component interface
- Insert hire rows in JOB Data without using CI
- field edit issue
- ANy way to limit the # of Grid rows loaded initially WITHOUT modifying the criteria?
Recent comments
- Reply to comment | CompShack.com
2 weeks 5 days ago - Re: Temporary Tables
3 weeks 3 days ago - teacher credit union home page
3 weeks 3 days ago - Re: Create Paychecks/Advice in PDF Form
3 weeks 5 days ago - The third monster is modify.
3 weeks 6 days ago - What a perfect web site.
3 weeks 6 days ago - Re: FTP a File Using PeopleCode
4 weeks 5 days ago - Re: PeopleSoft XMLP, RTF template does not print well as PDF
4 weeks 6 days ago - How did I ended up here?
5 weeks 2 days ago - Re: START WITH and CONNECT BY in Oracle
5 weeks 5 days 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.!!!!!!!!!!!!!!!!!!!!!!!