The below SQL lists the complete portal navigation along with the process name. I built this SQL when we had to provide this list to our client to review processes that could be retired. The output is useful for generating an inventory or assisting in KEEP/DROP decisions during upgrade.

Here it is:

SELECT NAVIGATION, A1.PRCSNAME

FROM

(SELECT LPAD(‘–‘,2*(LEVEL-1)) || PORTAL_LABEL “NAVIGATION”, PORTAL_URI_SEG2 FROM (SELECT PORTAL_LABEL, PORTAL_PRNTOBJNAME, PORTAL_OBJNAME, PORTAL_URI_SEG2 FROM PSPRSMDEFN A

WHERE PORTAL_NAME = ‘EMPLOYEE’ ) B

WHERE B.PORTAL_PRNTOBJNAME != ‘ ‘

START WITH (B.PORTAL_URI_SEG2 IN

(SELECT D.PNLGRPNAME

FROM PSMENUITEM A, PSMENUDEFN B, PS_PRCSDEFNPNL C, PSPNLGROUP D

WHERE A.MENUNAME=B.MENUNAME

AND A.PNLGRPNAME = C.PNLGRPNAME

AND A.PNLGRPNAME = D.PNLGRPNAME

AND C.PRCSNAME IN

(SELECT PRCSNAME FROM PS_PRCSDEFN

WHERE LASTUPDOPRID !=

Posted by Nitin Pai
Comments (3)
January 11th, 2008

Comments (3)

santosh suryan - May 24th, 2008

Hi, Can you please tell me how and where to use SetAuthenticationResult function in PeopleSoft. Because I have already used it signonpeoplecode to customize login, but then its working.. Thanks in Advance

Nitin - February 15th, 2008

Apologize for the delay in responding to your question. I was tied up with a high priority deployment. I am not familiar with SQL Server alternative for the above SQL. However, I will try and get you an answer. Thanks Nitin

Jayaprakash Tedla - January 24th, 2008

This SQL doesn't work in SQL Server database, can you give me SQL that is compatible to SQL server.

Comments are closed.