# PeopleSoft Navigation Path SQL Generator (PSPRSMDEFN)

> Source: https://netray.co/tools/peoplesoft-portal-navigation-path-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate SQL that walks the portal registry from a component up to the root to print its navigation path, plus queries showing which roles and users can reach it.

## Category

developer

## Erp

Oracle PeopleSoft

## Audience

- PeopleSoft developer
- PeopleSoft security administrator
- PeopleSoft analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Portal name

**Key:** portal

**Type:** text

**Default:** EMPLOYEE

#### Component name (PORTAL_URI_SEG2)

**Key:** component

**Type:** text

**Default:** JOB_DATA

#### Menu name (PORTAL_URI_SEG1)

**Key:** menu

**Type:** text

**Default:** ADMINISTER_WORKFORCE_(GBL)

**Help:** Read it from query 1 output or the component URL.

#### Content reference name (optional)

**Key:** cref

**Type:** text

**Default:** HC_JOB_DATA_GBL

#### User ID for access check

**Key:** oprid

**Type:** text

**Default:** PS

**Blocks:**

#### 1. Oracle: navigation path for a component

**Body:** -- Walk from each content reference for the component up to the root folder
SELECT CREF_NAME,
       MENU_NAME,
       LISTAGG(PORTAL_LABEL, ' > ') WITHIN GROUP (ORDER BY LVL DESC) AS NAV_PATH
FROM (
  SELECT PORTAL_LABEL,
         LEVEL AS LVL,
         CONNECT_BY_ROOT PORTAL_OBJNAME AS CREF_NAME,
         CONNECT_BY_ROOT PORTAL_URI_SEG1 AS MENU_NAME
  FROM PSPRSMDEFN
  START WITH PORTAL_NAME = '{{portal}}'
         AND PORTAL_REFTYPE = 'C'
         AND PORTAL_URI_SEG2 = '{{component}}'
  CONNECT BY PORTAL_OBJNAME = PRIOR PORTAL_PRNTOBJNAME
         AND PORTAL_NAME = PRIOR PORTAL_NAME
)
GROUP BY CREF_NAME, MENU_NAME
ORDER BY CREF_NAME;

#### 2. Oracle: navigation path for a content reference name

**Body:** SELECT LISTAGG(PORTAL_LABEL, ' > ') WITHIN GROUP (ORDER BY LVL DESC) AS NAV_PATH
FROM (
  SELECT PORTAL_LABEL, LEVEL AS LVL
  FROM PSPRSMDEFN
  START WITH PORTAL_NAME = '{{portal}}'
         AND PORTAL_OBJNAME = '{{cref}}'
  CONNECT BY PORTAL_OBJNAME = PRIOR PORTAL_PRNTOBJNAME
         AND PORTAL_NAME = PRIOR PORTAL_NAME
);

#### 3. SQL Server: same path with a recursive CTE

**Body:** WITH NAV (CREF_NAME, PORTAL_NAME, PORTAL_OBJNAME, PORTAL_PRNTOBJNAME, NAV_PATH) AS (
  SELECT PORTAL_OBJNAME, PORTAL_NAME, PORTAL_OBJNAME, PORTAL_PRNTOBJNAME,
         CAST(PORTAL_LABEL AS NVARCHAR(4000))
  FROM PSPRSMDEFN
  WHERE PORTAL_NAME = '{{portal}}'
    AND PORTAL_REFTYPE = 'C'
    AND PORTAL_URI_SEG2 = '{{component}}'
  UNION ALL
  SELECT N.CREF_NAME, P.PORTAL_NAME, P.PORTAL_OBJNAME, P.PORTAL_PRNTOBJNAME,
         CAST(P.PORTAL_LABEL + N' > ' + N.NAV_PATH AS NVARCHAR(4000))
  FROM PSPRSMDEFN P
  JOIN NAV N
    ON P.PORTAL_OBJNAME = N.PORTAL_PRNTOBJNAME
   AND P.PORTAL_NAME = N.PORTAL_NAME
)
SELECT CREF_NAME, NAV_PATH
FROM NAV
WHERE PORTAL_OBJNAME = 'PORTAL_ROOT_OBJECT'
ORDER BY CREF_NAME;

#### 4. Roles and permission lists that can open the component

**Body:** SELECT DISTINCT RC.ROLENAME,
       A.CLASSID,
       A.MENUNAME,
       A.BARITEMNAME AS COMPONENT,
       A.PNLITEMNAME AS PAGE,
       A.DISPLAYONLY,
       A.AUTHORIZEDACTIONS
FROM PSAUTHITEM A
JOIN PSROLECLASS RC
  ON RC.CLASSID = A.CLASSID
WHERE A.MENUNAME = '{{menu}}'
  AND A.BARITEMNAME = '{{component}}'
ORDER BY RC.ROLENAME, A.CLASSID, A.PNLITEMNAME;

#### 5. Does a user have access, and through which role?

**Body:** SELECT DISTINCT RU.ROLEUSER,
       RU.ROLENAME,
       A.CLASSID,
       A.PNLITEMNAME AS PAGE,
       A.DISPLAYONLY
FROM PSAUTHITEM A
JOIN PSROLECLASS RC
  ON RC.CLASSID = A.CLASSID
JOIN PSROLEUSER RU
  ON RU.ROLENAME = RC.ROLENAME
WHERE A.MENUNAME = '{{menu}}'
  AND A.BARITEMNAME = '{{component}}'
  AND RU.ROLEUSER = '{{oprid}}'
ORDER BY RU.ROLENAME, A.CLASSID;

**Notes:** Verify for your release: the root folder object name PORTAL_ROOT_OBJECT, that content references for components have PORTAL_REFTYPE 'C' and folders 'F', and the portal name your users sign in to (EMPLOYEE in most HCM and FSCM databases). LISTAGG needs Oracle 11g Release 2 or later. Labels are the base language; translated labels are held in a related language table. Run as a read-only user.

## Content

**Intro:** This generator writes SQL that prints the navigation path of a PeopleSoft component from the portal registry table PSPRSMDEFN, for Oracle and SQL Server, and shows which roles and users can open it. It applies to PeopleTools 8.4x through 8.6x.

**Sections:**

#### How to use it

**Body:** Enter the portal name, the component name, and optionally the menu, a content reference name and a user ID. Query 1 returns one row per content reference that points to the component, with its full path such as Root > Workforce Administration > Job Information > Job Data. Query 2 does the same for a known content reference. Query 3 is the SQL Server version. Queries 4 and 5 answer the follow-up question: which roles can open it, and does this user have it.

**Bullets:**

- Use the menu name from query 1 in queries 4 and 5
- Expect more than one path when a component is registered twice
- Add the market to filters if you use non-GBL markets

#### Where this lives in PeopleSoft

**Body:** Navigation is the portal registry. Each folder and content reference is a row in PSPRSMDEFN for a portal name. PORTAL_OBJNAME is the object name, PORTAL_PRNTOBJNAME its parent folder, PORTAL_LABEL the label users see and PORTAL_REFTYPE whether it is a folder or content reference. For component content references, PORTAL_URI_SEG1 holds the menu, PORTAL_URI_SEG2 the component and PORTAL_URI_SEG3 the market. The top folder is PORTAL_ROOT_OBJECT, labelled Root. The same structure is maintained online under PeopleTools, Portal, Structure and Content.

#### Worked example: where is JOB_DATA and who can open it

**Body:** With portal EMPLOYEE and component JOB_DATA, query 1 returns the content reference HC_JOB_DATA_GBL with menu ADMINISTER_WORKFORCE_(GBL) and the path Root > Workforce Administration > Job Information > Job Data. Query 4 then lists the roles whose permission lists grant the component on that menu, page by page, with DISPLAYONLY showing read-only access. Query 5 confirms whether user PS has it and through which role. Labels and object names can differ in your database, so treat these values as a pattern.

#### Gotchas

**Body:** Walking upward builds the path leaf first; the LISTAGG ordered by level descending turns it into root first. SYS_CONNECT_BY_PATH on the same upward walk gives the reverse order. If a path is missing, a folder in the chain may be in another portal or deleted, breaking the walk before the root. Content references can be hidden from navigation yet still exist, so a path does not prove the menu is visible. Security is granted on the menu and component in PSAUTHITEM, not on the portal row.

**Bullets:**

- No result: check the portal name and component spelling
- Several results: the component is registered in more than one folder
- Visibility also depends on content reference security and hide flags

**Cta:** If you want users to ask where a page lives and whether they have access in plain English, answered from your own PeopleSoft database on your servers, we can set that up.

**Faq:**

#### How do I get the navigation path of a PeopleSoft component in SQL?

**Answer:** Start from the PSPRSMDEFN row where PORTAL_URI_SEG2 is the component and PORTAL_NAME is your portal, then walk up through PORTAL_PRNTOBJNAME to PORTAL_ROOT_OBJECT. In Oracle use CONNECT BY with LISTAGG ordered by level; in SQL Server use a recursive common table expression concatenating labels.

#### Which table stores PeopleSoft content references?

**Answer:** PSPRSMDEFN, the portal registry, stores folders and content references for each portal. PORTAL_REFTYPE distinguishes folders from content references, PORTAL_LABEL holds the label and PORTAL_URI_SEG1, SEG2 and SEG3 hold the menu, component and market for component content references.

#### How do I find which roles can access a component?

**Answer:** Permission lists grant access in PSAUTHITEM by menu (MENUNAME) and component (BARITEMNAME), with one row per page. Join PSAUTHITEM to PSROLECLASS on CLASSID to get roles, and to PSROLEUSER on ROLENAME to get users. DISPLAYONLY and AUTHORIZEDACTIONS show the level of access.

#### Why does the query return more than one path?

**Answer:** A component can be registered as several content references, for example in different folders, in a WorkCenter, or in a custom folder added by your team. Each one is a separate PSPRSMDEFN row with its own path. Check which ones are hidden from navigation before telling users where to go.

## Search terms

peopletools how to get navigation path in sql, peoplesoft navigation path sql, peoplecode content reference table, how to query in peopletools, psprsmdefn navigation query, peoplesoft component navigation, peoplesoft find menu path for component, peoplesoft who has access to component sql

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json