Tuesday, December 04, 2007

Query for Component and/or CREF Navigation

I regulary have to figure out the menu/portal navigation to a component given nothing more than a component or a CREF name (AKA Content Reference or Portal Object Name). For example, looking at a Pagelet definition in the Pagelet Wizard, you may see the cryptic content reference name for a component, but nothing telling you how to navigate to that component and no hint to help you find it in the portal registry. Likewise, a user might give you the menu and component name of a troubled component, but not the navigation to that component. It is situations like this that we, as developers, can realize value from the PeopleTools meta-data. Since the navigation, CREF name, and menu/component relationship is stored in the PeopleTools database, we can query a tools table to determine this navigation. The following Oracle specific SQL displays the navigation to a given CREF name. If you use a different database, and are familiar enough with your database's SQL, please translate this statement and post it as a comment for the benefit of the rest of the community.

WITH PORTAL_REGISTRY AS (
SELECT RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL), ' >> ')), ' >> ') PATH
, LEVEL LVL
FROM PSPRSMDEFN
WHERE PORTAL_NAME = 'EMPLOYEE'
START WITH PORTAL_OBJNAME = :1
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME )
SELECT PATH
FROM PORTAL_REGISTRY
WHERE LVL = (
SELECT MAX(LVL)
FROM PORTAL_REGISTRY )

NOTE: Even though I've written my fair share of SQL, I'm no expert. Please don't grade me on the elegance and efficiency of the previous statement. If you are an SQL expert, and have a better way of writing this recursive SQL statement, then, please, for the benefit of the community, paste that statement into a comment. That said, I'll continue with the topic at hand...

If we pass EP_STANDARD_CF_TMPLT_GBL as bind :1, then the result will be Root >> Set Up Financials/Supply Chain >> Common Definitions >> Design ChartFields >> Configure >> Standard Configuration.

Modifying the above SQL statement a little, we can determine the navigation for a given menu, component, and market (the Ctrl-J data our users usually give us):

WITH PORTAL_REGISTRY AS (
SELECT RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL), ' >> ')), ' >> ') PATH
, LEVEL LVL
FROM PSPRSMDEFN
WHERE PORTAL_NAME = 'EMPLOYEE'
START WITH PORTAL_REFTYPE = 'C'
AND PORTAL_URI_SEG1 = :1
AND PORTAL_URI_SEG2 = :2
AND PORTAL_URI_SEG3 = :3
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME )
SELECT PATH
FROM PORTAL_REGISTRY
WHERE LVL = (
SELECT MAX(LVL)
FROM PORTAL_REGISTRY )

If we pass DESIGN_CHARTFIELDS for bind :1 (menu), STANDARD_CF_TMPLT for bind :2 (component), and GBL for bind :3 (market), then we will get the same result as the previous query, but using the Ctrl-J data rather than the CREF name.

15 comments:

Jim Marion said...

I just found another use for this SQL statement. If you are familiar with the standard PeopleSoft URL, then you know that the piece of data after the /c/ is the menu.component.market. Therefore, using the second SQL statement, you can determine the navigation to a component from the URL.

Why not just hit the psp URL and find out? Hmmm... good question.

Jeromy McMahon said...

Thanks for the posting. I have also posted some SQL's for finding menu paths on my blog.

Thanks
Jeromy McMahon

http://jmcmahon33.blogspot.com

Jason said...

Jim, i'm a functional user who's the kinda guy to give techs the Cntl-J data. Thanks for your post. I've been trying to attack a similar problem to create a starting point for a new security matrix. I want to find all the sub-menus, components and page names recursively for a given menu. i was hoping for something like the PS_Page_Panel utility described in a number of PS query documents that has become my best friend for query development. Thanks for pointing my in the right direction!

Yatish said...

Thanks for the sql..we are on DB2..would you know the format to use this sql in a DB2 database? Please let me know..thanks !
Yatish.

Jim Marion said...

@Yatish, I don't believe DB2 has a CONNECT BY clause. Here is an IBM article that discusses porting the CONNECT BY clause to DB2: http://www.ibm.com/developerworks/db2/library/techarticle/dm-0510rielau/.

ashok said...

Hi Jim,
I was given a task to get the navigation for all the components used in a particular module(GL,AP,etc) in Peoplesoft.My Query is taking a huge time to filter the ouput.Can you help in simplifying this query.

Thanks,
Ashok K.



select distinct NAV.navigation, C.MENUNAME as menu_NAMe, Aa.PORTAL_URI_SEG1 AS MENu_DeScRiptION,
C.PNLGRPNAME AS COMPONent,Aa.PORTAL_URI_SEG2 AS CoMpoNeNt_NAME,D.PnLName AS page, D.ITEMNAME AS PAGE_NAMe,
aa.PORTAL_NAME,
Aa.PORTAL_PRNTOBJNAME AS FOLDER,
Aa.PORTAL_OBJNAME AS CONTENT_REFRENCE,
aa.PORTAL_LABEL,
aa.PORTAL_URI_SEG3 AS MARKET,
AA.portal_reftype,
F.acTIOns , E.PAGEACCESSDESCR
from PSMENUITEM C ,PSPNLGROUP D,
PSPNLGRPDEFN F, PsPgeAcCESSDEsC E,
(SELECT navigation
, url
, MENU_NAME
, COMPONENT_NAME
, portal_objname
, portal_prntobjname
, portal_uri_seg3
,portal_label
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
, a.portal_uri_seg1 MENU_NAME
, a.portal_uri_seg2 COMPONENT_NAME
, a.portal_objname portal_objname
, a.portal_prntobjname portal_prntobjname
, a.portal_uri_seg3 portal_uri_seg3
, a.portal_reftype portal_reftype
,a.portal_label portal_label
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 psprsmdefn a
WHERE portal_name = 'EMPLOYEE'
AND portal_objname <> portal_prntobjname
AND NOT EXISTS (SELECT 'X'
FROM psprsmsysattrvl
WHERE portal_name = a.portal_name
AND portal_reftype = a.portal_reftype
AND portal_objname = a.portal_objname
AND portal_attr_nam = 'PORTAL_HIDE_FROM_NAV'
AND a.portal_objname NOT IN ('CO_NAVIGATION_COLLECTIONS','PORTAL_BASE_DATA'))) a
WHERE portal_uri_seg2 in(select Aa.PORTAL_URI_SEG2 AS CoMpoNeNt_NAME
from PSMENUITEM C ,PSPNLGROUP D,
PSPNLGRPDEFN F, PsPgeAcCESSDEsC E,
(SELECT dISTInct g.PORTAL_NAME,
g.PORTAL_PRNTOBJNAME ,
G.PORTAL_OBJNAME,
g.PORTAL_LABEL,
G.PORTAL_URI_SEG1,
G.PORTAL_URI_SEG2 ,
G.PORTAL_URI_SEG3,
G.portal_reftype
FROM psprsmdefn G
WHERE g.portal_name = 'EMPLOYEE'
AND g.PORTAL_REFTYPE = 'C'
and g.PORTAL_URI_SEG3 ='GBL'
START WITH g.PORTAL_LABEL IN ('General Ledger') CONNECT BY PRIOR g.portal_objname = g.portal_prntobjname
GROUP BY g.PORTAL_NAME,
g.portal_reftype,
g.PORTAL_PRNTOBJNAME,
g.PORTAL_OBJNAME,
g.PORTAL_LABEL,
g.PORTAL_URI_SEG1,
g.PORTAL_URI_SEG2,
g.PORTAL_URI_SEG3
ORDER BY g.portal_reftype DESC) AA
WHERE C.PNLGRPNAME=f.PNLGRPNAME AnD
AA.PORTAL_URI_SEG1=C.MENUNAME
AND Aa.PORTAL_URI_SEG2 = C.PNLGRPNAME
aNd f.ACTIONs = e.authorizedactions
ANd C.PNLGRPNAME=D.PNLGRPNAME)
START WITH a.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
CONNECT BY PRIOR a.portal_objname = a.portal_prntobjname)
WHERE navigation NOT LIKE '%Navigation Collections%') NAV,
(SELECT dISTInct g.PORTAL_NAME,
g.PORTAL_PRNTOBJNAME ,
G.PORTAL_OBJNAME,
g.PORTAL_LABEL,
G.PORTAL_URI_SEG1,
G.PORTAL_URI_SEG2 ,
G.PORTAL_URI_SEG3,
G.portal_reftype
FROM psprsmdefn G
WHERE g.portal_name = 'EMPLOYEE'
AND g.PORTAL_REFTYPE = 'C'
and g.PORTAL_URI_SEG3 ='GBL'
START WITH g.PORTAL_LABEL IN ('General Ledger') CONNECT BY PRIOR g.portal_objname = g.portal_prntobjname
GROUP BY g.PORTAL_NAME,
g.portal_reftype,
g.PORTAL_PRNTOBJNAME,
g.PORTAL_OBJNAME,
g.PORTAL_LABEL,
g.PORTAL_URI_SEG1,
g.PORTAL_URI_SEG2,
g.PORTAL_URI_SEG3
ORDER BY g.portal_reftype DESC) AA
WHERE C.PNLGRPNAME=f.PNLGRPNAME AnD
AA.PORTAL_URI_SEG1=C.MENUNAME
AND Aa.PORTAL_URI_SEG2 = C.PNLGRPNAME
aNd f.ACTIONs = e.authorizedactions
ANd C.PNLGRPNAME=D.PNLGRPNAME

Jim Marion said...

@Ashok! Wow! That is quite the SQL statement. I recommend breaking it up and using explain plan in SQL Developer or SQLNav to find your bottlenecks.

admin said...

Hi Jim - I made a few additions to the query so that the authorized actions could be returned along with the navigation based on passing in a given rolename. I have minor notes here:

http://peoplesoftsecurity.wordpress.com/2011/09/22/query-to-get-navigation-and-authorized-actions-based-on-a-role/

Other users might find this query useful.

The query is:

select
distinct path
, base_component
, base_menu
, case sum(displayonly)
WHEN 0 THEN
CASE sum(authorizedactions)
WHEN 1 THEN ‘ADD’
WHEN 2 THEN ‘UPDATE,DISPLAY’
WHEN 3 THEN ‘ADD,UPDATE, DISPLAY’
WHEN 4 THEN ‘UPDATE/DISPLAY ONLY’
WHEN 5 THEN ‘ADD,UPDATE/DISPLAY ALL’
WHEN 6 THEN ‘UPDATE,DISPLAY,UPDATE/DISPLAY ALL’
WHEN 7 THEN ‘ADD,UPDATE,DISPLAY,UPDATE/DISPLAY ALL’
WHEN 8 THEN ‘CORRECTION’
WHEN 9 THEN ‘ADD,CORRECTION’
WHEN 10 THEN ‘UPDATE,DISPLAY,CORRECTION’
WHEN 11 THEN ‘ADD, UPDATE/DISPLAY ALL, CORRECTION’
WHEN 12 THEN ‘UPDATE/DISPLAY ALL,CORRECTION’
WHEN 13 THEN ‘ADD, UPDATE, DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN 14 THEN ‘UPDATE,DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN 15 THEN ‘ADD,UPDATE,DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
ELSE’UNKNOWN’
END
ELSE ‘DISPLAY ONLY’
END as access_type
from (
with sec_info as
(
SELECT RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(m.PORTAL_LABEL), ‘ > ‘)), ‘ > ‘) PATH,
LEVEL LVL,
connect_by_root m.portal_uri_seg2 as base_component,
connect_by_root m.portal_uri_seg1 as base_menu
FROM sysadm.psprsmdefn m
WHERE m.PORTAL_NAME = ‘EMPLOYEE’
START WITH m.PORTAL_REFTYPE = ‘C’
AND m.PORTAL_URI_SEG2 in
(
select distinct mi.PNLGRPNAME
from sysadm.psroleclass z,
sysadm.psauthitem x,
sysadm.PSMENUITEM mi
where z.rolename = ‘ROLE_NAME’
and z.classid = x.classid
and x.MENUNAME = mi.MENUNAME
and x.BARNAME = mi.BARNAME
and x.BARITEMNAME = mi.ITEMNAME
)
CONNECT BY PRIOR m.PORTAL_PRNTOBJNAME = m.PORTAL_OBJNAME
)
select distinct a.path
, a.base_component
, a.base_menu
, x.authorizedactions
, x.displayonly
, z.classid
from sec_info a,
sysadm.psroleclass z,
sysadm.psauthitem x,
sysadm.psmenuitem mi
where a.lvl = (
select max(b.lvl-1) from sec_info b
where a.base_component = b.base_component
and a.base_menu = b.base_menu
)
and z.rolename = ‘ROLE_NAME’
and z.classid = x.classid
and x.MENUNAME = mi.MENUNAME
and x.BARNAME = mi.BARNAME
and x.BARITEMNAME = mi.ITEMNAME
and x.menuname = a.base_menu
and mi.PNLGRPNAME = a.base_component
group by a.path, a.base_component, a.base_menu, x.authorizedactions, x.displayonly, z.classid
)
group by path, base_component, base_menu
;

Jim Marion said...

@admin, thank you for sharing and helping to build the PeopleSoft/PeopleTools community. I really appreciate it.

M Asim said...

Hi Jim:
I am new in development of PeopleSoft. I have learned about PeopleSoft pages,components,App engine etc, etc. All I done on the existing Portal of company. Now my task is to Build a separate Application independent of existing portal mean on new url with new login users.
Can You guide me something about it?? Thanks :)

Jim Marion said...

@M Asim, you don't want to build additional components, pages, etc inside HCM, financials, CRM, etc? If not, then there are two ways to go about this:

1. Find the application that is closest to your needs and build on that.

2. Create a "tools only" instance that just has peopletools modules.

M Asim said...

@Jim
Thanks for reply.
2. Create a "tools only" instance that just has peopletools modules.

please explain it more. if their is an oracle guide available for it then send me it,s link. Thanks

Jim Marion said...

@M Asim, it is an option when you run through the database installation process. You can get more information from Nicolas's blog posts starting Here.

divya s said...

since start with--connect by command is specific to oracle database. Is there any other way we can implement this through peoplecode?

Jim Marion said...

@divya, see the Common Table Expressions version in this post.