Breadcrumb SQL to find the Navigation path for the component in the PIA

Your rating: None Average: 5 (11 votes)

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;

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!

Comments

Have a question? Please ask it on the forum instead.
Guest's picture
Guest
Re: Breadcrumb SQL to find the Navigation path for the ...

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%'

Guest's picture
Guest
Re: Breadcrumb SQL to find the Navigation path for the ...

This is the best and easy way to find the navigation.!!!!!!!!!!!!!!!!!!!!!!!

Guest's picture
Guest
Re: Breadcrumb SQL to find the Navigation path for the ...

The query posted in comments section worked like a charm! So easy. Thanks!!!

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
17 + 2 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.