Update Trace definitions for better performance in PRD

-- Statement to select current date/time

SELECT to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') from dual;

-- Statement to select current DB

SELECT name from v$database;


--Select all process definitions that have a -TRACE flag
select * from PS_PRCSDEFN
WHERE PARMLIST LIKE '-TRACE%';

--Remove Trace Flags on Process Definitions


update ps_prcsdefn
set parmlist = ' ',parmlisttype = '0'
WHERE PARMLIST LIKE '-TRACE%';

commit;

Audits for Projects and Records - FInd null or blank descrs

Here is some sample SQL to find blank descrs within projects and records...etc. There should be some useful names to custom objects.

select
recname,
recdescr,
descrlong
from psrecdefn
where recname LIKE '%ASU%' -- This would be your custom naming convention
and recdescr in (' ','')


select
projectname,
projectdescr,
lastupddttm,
lastupdoprid,
descrlong
from PSPROJECTDEFN
where projectname like '%ASU%' --This would be your custom naming convention
and projectdescr in (' ', '')

Sample Pell and FA Disbursements query

Here is a good sample for FA disbursements: (Pell, Perkins...etc)

SELECT
AA.NATIONAL_ID,
',',
A.EMPLID,
',',
S.NAME,
',',
F.CAMPUS_FA,
',',
D.FEDERAL_ID,
',',
B.DISBURSED_BALANCE,
',',
SUM(B.DISBURSED_BALANCE)
FROM PS_PERS_NID AA,
PS_STDNT_AWARDS A,
PS_STDNT_AWRD_DISB B,
PS_DISB_ID_TBL C,
PS_ITEM_TYPE_FA D,
PS_STDNT_AWRD_ACTV E,
PS_STDNT_FA_TERM F,
PS_NAMES S
WHERE
A.EMPLID = AA.EMPLID
AND A.EMPLID = S.EMPLID
AND B.DISBURSED_BALANCE > 0
AND B.EMPLID = A.EMPLID
AND B.INSTITUTION = A.INSTITUTION
AND B.ITEM_TYPE = A.ITEM_TYPE
AND B.ACAD_CAREER = A.ACAD_CAREER
AND C.INSTITUTION = B.INSTITUTION
AND C.AID_YEAR = B.AID_YEAR
AND C.ACAD_CAREER = B.ACAD_CAREER
AND C.DISBURSEMENT_PLAN = A.DISBURSEMENT_PLAN
AND C.DISBURSEMENT_ID = B.DISBURSEMENT_ID
AND D.SETID = A.SETID
AND D.ITEM_TYPE = A.ITEM_TYPE
AND D.AID_YEAR = A.AID_YEAR
AND D.EFFDT =
(SELECT MAX(D1.EFFDT)
FROM PS_ITEM_TYPE_FA D1
WHERE D1.SETID = D.SETID
AND D1.ITEM_TYPE = D.ITEM_TYPE
AND D1.AID_YEAR = D.AID_YEAR
AND D1.EFFDT <= SYSDATE
)
AND D.DISBURSE_METHOD = 'A'
AND D.EFF_STATUS = 'A'
AND D.FA_SOURCE = 'F'
AND D.AGGREGATE_AREA IN ('FSEOG','PELL','PERKINS')
AND E.EMPLID = B.EMPLID
AND E.INSTITUTION = B.INSTITUTION
AND E.AID_YEAR = B.AID_YEAR
AND E.ITEM_TYPE = B.ITEM_TYPE
AND E.ACAD_CAREER = B.ACAD_CAREER
AND E.DISBURSEMENT_ID = B.DISBURSEMENT_ID
AND E.AWARD_DISB_ACTION = 'P'
AND E.DISB_AMOUNT > 0
AND E.ACTION_DTTM =
(SELECT MAX(E1.ACTION_DTTM)
FROM PS_STDNT_AWRD_ACTV E1
WHERE E1.EMPLID = E.EMPLID
AND E1.INSTITUTION = E.INSTITUTION
AND E1.AID_YEAR = E.AID_YEAR
AND E1.ITEM_TYPE = E.ITEM_TYPE
AND E1.ACAD_CAREER = E.ACAD_CAREER
AND E1.DISBURSEMENT_ID = E.DISBURSEMENT_ID
AND TRUNC(E1.ACTION_DTTM) <= SYSDATE
)
AND TRUNC(E.ACTION_DTTM) BETWEEN TO_DATE('01-JAN-2007','DD-MON-YYYY') AND TO_DATE('10-JAN-2008','DD-MON-YYYY')
AND F.EMPLID = A.EMPLID
AND F.INSTITUTION = A.INSTITUTION
AND F.STRM = C.STRM
AND F.AID_YEAR = A.AID_YEAR
AND F.EFFDT =
(SELECT MAX(F1.EFFDT)
FROM PS_STDNT_FA_TERM F1
WHERE F1.EMPLID = F.EMPLID
AND F1.INSTITUTION = F.INSTITUTION
AND F1.AID_YEAR = F.AID_YEAR
AND F1.STRM = F.STRM
AND F1.EFFDT <= SYSDATE
)
AND F.EFFSEQ =
(SELECT MAX(C1.EFFSEQ)
FROM PS_STDNT_FA_TERM C1
WHERE C1.EMPLID = F.EMPLID
AND C1.INSTITUTION = F.INSTITUTION
AND C1.AID_YEAR = F.AID_YEAR
AND C1.STRM = F.STRM
AND C1.EFFDT = F.EFFDT)
AND ROWNUM < 100
GROUP BY AA.NATIONAL_ID, A.EMPLID, S.NAME, F.CAMPUS_FA, D.FEDERAL_ID, B.DISBURSED_BALANCE
ORDER BY AA.NATIONAL_ID, A.EMPLID, S.NAME, D.FEDERAL_ID

Decode Peoplecode - decodepc.java

decodePC.sqr as written by David L. Price has been rewritten to decodePC.java and expanded. It's general purpose is decoding PeopleCode stored as BLOB. The PeopleSoft database is accessed via a JDBC connection therefore there is no need to use Application Designer to look at PeopleCode, Application Engine programs, SQL, and XSLT. decodePC.java can read Application Engine programs and Application Packages and output them to a text file so you can get on with solving business problems.

Example of compiling decodePC.java with Oracle JDBC on a Unix box from a command prompt in the directory where decodePC.java exists:

javac -classpath /directorypath/ojdbc14.jar ./decodePC.java

Example of running decodePC.class with Oracle JDBC on a Unix box from a command prompt in the directory where decodePC.class exists:

java -classpath .:/directorypath/ojdbc14.jar decodePC

Download the code here from the site!

Oracle Disk Reads Script / Oracle Tuning Efforts

Here are some good scripts for Oracle tuning.

REM This SQL script will produce a current listing of
REM suspect SQL statements based on a specified
REM level of disk reads against the database instance.

SELECT DISK_READS, SQL_TEXT
FROM V$SQLAREA
WHERE DISK_READS >= &DISK_READS
ORDER BY DISK_READS DESC
/

REM This SQL script will display all current terminal
REM sessions attached to a specific database instance.

spool c:\temp\session.lis

SELECT a.sid sessionno,
substr(a.username,1,10) user_name,
substr(a.schemaname,1,10) schema,
substr(a.osuser,1,15) client_user,
substr(a.client_info,1,25) dbms_client_info,
substr(a.machine,1,15) wk_station,
substr(a.program,1,31) program_name,
a.status,
to_char(a.logon_time,'DD-MON-YYYY HH24:MM:SS') logontime,
b.name command_action
FROM v$session a, sys.audit_actions b
WHERE a.command = b.action
/

spool off


REM This SQL script will produce a current listing of
REM column statistics for a specific table.

col num_distinct format 999,999,999
col num_nulls format 999,999,999


SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY
FROM DBA_TAB_COL_STATISTICS
WHERE TABLE_NAME = upper('&TABLE_NAME')
ORDER BY COLUMN_NAME
/


REM This SQL script will change the SQL prompt to
REM refer to the instance being used at the time.
REM
REM This will eliminate the need to execute the
REM statement:
REM
REM SELECT name from v$database;


set termout off

column x new_value y

SELECT rtrim(instance, chr(0)) x
FROM v$thread
/

set sqlprompt '&Y>'
set termout on

REM
REM Encountering: SP2-0110: Cannot create save file "afiedt.buf"
REM
REM The user may not have permission to edit in that
REM specific directory.
REM

set editfile C:\TEMP\AFIEDT.BUF

REM
REM Set line and page size lengths
REM

set linesize 100
set pagesize 80

Peoplesoft Tools Tables

PSST0101 blog site had started listing out the tools tables. I wanted to post this listing on my blog as well. I have also provided the link to the orginal blog.

Enjoy

Projects

PSPROJECTDEFN — Project header table
PSPROJECTITEM — Definitions in the project
Fields

PSDBFIELD — Fields in the system
PSXLATITEM — Translate Values
Records

PSRECDEFN — Record header table
PSRECFIELD — Fields in the record (subrecords not expanded)
PSRECFIELDALL — Fields in the record (subrecords expanded)
PSKEYDEFN — Indexes
Pages

(Note: Pages still have the name panels in the PeopleTools table names)

PSPNLDEFN — Page header table
PSPNLFIELD — Page controls
PSPNLHTMLAREA — Static HTML Areas on Pages
Components

(Note: Components still have the name panel group in the PeopleTools table names)

PSPNLGRPDEFN — Component header table
PSPNLGROUP — Pages in the components
Menus

PSMENUDEFN — Menu header table
PSMENUITEM — Items (components) on the menu
Security

PSCLASSDEFN — Permission List header table
PSAUTHITEM — Menu items granted security by permission lists
PSROLEDEFN — Role header table
PSROLECLASS — Permission Lists in roles
PSOPERDEFN — User ID header table
PSROLEUSER — Roles granted to users
Portal

PSPRSMDEFN — Content References and Folders
Change Control

PSCHGCTLHIST — shows history of locked definitions with project name, incident, and description
PSCHGCTLLOCK — shows definitions that are currently locked
Application Engine


PSAEAPPLDEFN — header record; 1 row per app engine
PSAEAPPLSTATE — state records assigned to app engines
PSAEAPPLTEMPTBL — temp tables assigned to app engines
PSAESECTDEFN — sections
PSAESTEPDEFN — steps
PSAESTEPMSGDEFN
PSAESTMTDEFN — actions
HTML Definitions


PSCONTDEFN — header record; last update time, etc.
PSCONTENT — stores actual text in the HTML definition

Excellent Backup tool for source code

Here is a great site and software tool to backup source code. You can specify only certain file extensions *.sqr, *.sql, *.js etc. With this backup tool, you can also schedule backups to copy only the changes to the network share or jump drive.

This site is developed by a colleague of mine and his software is top shelf.
http://www.glaciermicrosystems.com/products/products.html

PS Queries / a user can not see / Security

This query was developed to capture a listing of queries that a user does not have access to.

SELECT DISTINCT A.OPRID, A.QRYNAME, A.DESCR, B.RECNAME
FROM PSQRYDEFN A, PSQRYRECORD B
WHERE A.OPRID = B.OPRID AND
A.QRYNAME = B.QRYNAME AND
A.QRYTYPE = 1
and not exists (select 'x' from PSROLECLASS D , PSROLEUSER E , PSQRYACCLSTRECS F
WHERE D.ROLENAME = E.ROLENAME AND
F.CLASSID = D.CLASSID AND
F.VERSION = ( SELECT VERSION FROM PSVERSION D WHERE D.OBJECTTYPENAME = 'QAL') AND
E.ROLEUSER = A.OPRID and
f.recname = b.recname)
and a.oprid = 'Place OPRID here'

PS Listing of Payroll / HR tables

Here is a great sample SQL for capturing PS Payroll and HR tables within PS. This SQL was provided by my colleague Roger Davies.

select a.RECNAME, decode(a.SQLTABLENAME, ' ', 'PS_' || a.recname, a.sqltablename) table_name, a.rectype, a.OBJECTOWNERID, xlat.xlatlongname, num_rows
from psrecdefn a, psxlatitem xlat, all_tables b
where a.objectownerid in ('AWFA', 'FGL', 'HBA', 'HBN', 'HEB', 'HER', 'HFSA', 'HHP', 'HHR', 'HMCF', 'HPY', 'HRAM', 'HRAT', 'HSP', 'HTC', 'HTL', 'HTLA', 'HTLI', 'HTLR', 'HTLX', 'HRAM','HRAT','HSP' )
and xlat.fieldname = 'OBJECTOWNERID'
and xlat.fieldvalue = a.OBJECTOWNERID
and xlat.effdt = (select max(xlat1.effdt) from psxlatitem xlat1
where xlat1.fieldname = xlat.fieldname
and xlat1.fieldvalue = xlat.fieldvalue)
and b.table_name = decode(a.SQLTABLENAME, ' ', 'PS_' || a.recname, a.sqltablename)
and b.NUM_ROWS > 0

How to Trace your APP Engine

AE Process Definition - Override options tab

-TRACE 7 -TOOLSTRACEPC 3596 -TOOLSTRACESQL 131 (review peoplebooks on which tracing values you need to place)

PSACCESLOG - Review users who login

Here are some queries to detail who has logged into the Peoplesoft system (Web Tier)


-- Unique IP Address Count, per hour, for the last 24 hours
-- Change the "1" to be "7" for seven days of activity
select DTTM, count(*)
from (select distinct a.logipaddress "IP",
to_char(a.logindttm,'MM/DD/YYYY HH24') "DTTM"
from psaccesslog a
where a.logindttm > sysdate - 1)
group by DTTM;


-- Find the IP Activity for a individual, for the last 24 hours
-- Change the "1" to be "7" for seven days of activity
select a.*, b.oprdefndesc from psaccesslog a, psoprdefn b
where a.logindttm > sysdate - 7 -- This indicates today and the past seven days...
and a.oprid = b.oprid
and b.oprdefndesc like '%Smith%'
ORDER BY A.LOGINDTTM DESC;

How to Default sysdate (Current Date) for Crystal Report and PeopleTools Query prompt automatically

I found a great blog entry about defaulting query prompts from Keton K. I wanted to post on here as well.

If you have a Query or Crystal Report, that has a date prompt and you want to schedule the report daily, so that date value is defaulted to sysdate (Current date), you can do this as follows.
Open the Query in PeopleTools Query tool.
Go to Criteria tab. Right Click on Expression2 Column and Select Expression or Expr-Expr if you are using between operator.

Type the following in Edit Expression.

current date
decode(:1,TO_DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate),:1)

current date - 1
decode(:1,TO_DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate)-1,:1)

current date + 1
decode(:1,TO_DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate)+1,:1)

replace :1 with the actual prompt value. You must first create this prompt.
Now you need to pass 01/01/1900 as an input parameter if you want to run the query for current date. Passing any other values will make the query run for that date.
This way you can achieve both i.e. run the query for a user selected date or run a query for current date which can be used to schedule. Note : you can use any date as a replacement for sysdate and not just 01/01/1900.
Also you can default it to any day relative to current date for e.g. trunc(sysdate) - 1 or trunc(sysdate) + 1 etc.
If you want to know what parameter user has passed in your report, you can add the following expression as field in your query.
Create an expression of type Date in left hand side Under Expressions.

Current Date
decode(:1,'1900-01-01',to_char(trunc(sysdate),'YYYY-MM-DD') ,:1)

Current Date - 1
decode(:1,'1900-01-01',to_char((trunc(sysdate)-1),'YYYY-MM-DD') ,:1)

Current Date + 1
decode(:1,'1900-01-01',to_char((trunc(sysdate)+1),'YYYY-MM-DD') ,:1)

Objects within Peoplesoft Projects

This SQL was given to me by a great colleague - Jan Jerabek. This SQL will pull all the objects out of your project for cut and paste efforts :-)

select decode(OBJECTTYPE, '0','Record', '1','Index', '2','Field', '3','Field Format', '4','Translate Value', '5','Pages', '6','Menus', '7','Components', '8','PeopleCode Record PeopleCode', '9','PeopleCode Menu PeopleCode', '10','Query', '11','Tree Structures', '12','Trees', '13','Access group', '14','Color', '15','Style', '16','Not used', '17','Business process', '18','Activity', '19','Role', '20','Process Definition', '21','Server Definition', '22','Process Type Definition', '23','Job Definitions', '24','Recurrence Definition', '25','Message Catalog Entries', '26','Dimension', '27','Cube Definitions', '28','Cube Instance Definitions', '29','Business Interlink', '30','SQL', '31','File Layout Definition', '32','Component Interfaces', '33','Application Engine program', '34','Application Engine section', '35','Message Node', '36','Message Channel', '37','Message', '38','Approval rule set', '39','PeopleCode Message PeopleCode', '40','PeopleCode Subscription PeopleCode', '41','Not Used', '42','PeopleCode Component Interface PeopleCode', '43','PeopleCode Application Engine PeopleCode', '44','PeopleCode Page PeopleCode', '45','PeopleCode Page Field PeopleCode', '46','PeopleCode Component PeopleCode', '47','PeopleCode Component Record PeopleCode', '48','PeopleCode Component Rec Fld PeopleCode', '49','Image', '50','Style sheet', '51','HTML', '52','Not used', '53','Permission List', '54','Portal Registry Definitions', '55','Portal Registry Structures', '56', 'URL Definitions', '57', 'Application Packages', '58', 'PeopleCode Application Package Peoplecode', '59', 'Portal Registry User Homepage', '60', 'Problem Type', '61', 'Archive Templates', '62', 'XSLT', '63', 'Portal Registry User Favorite', '64', 'Mobile Page', '65', 'Relationships', '66', 'PeopleCode Component Interface Property Peoplecode', '67', 'Optimization Models', '68', 'File References', '69', 'File Type Codes', '70', 'Archive Object Definitions', '71', 'Archive Templates (Type 2)', '72', 'Diagnostic Plug In', '73', 'Analytic Model','UNKNOWN','80','Service Operations' ,'81' ,'Service Operation Handlers','82','Service Operation Versions','83','Service Operation Routings',
'84','IB Queues','85','XMLP Template Definition','86',
'XMLP Report Definition' ,'87','XMLP File Definition','88',
'XMLP Data Source Definition') OBJECTTYPE, '', OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4 from PSPROJECTITEM where PROJECTNAME = 'Project name goes here' order by objecttype, OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3

Years of Service / Salary Query - Reports_to

Here is a sql/report that I needed to write to track years of service and salary info. (Set the values in red)


select distinct a.reports_to, a.emplid, b.name, (a.comprate * 26), a.grade, c.min_rt_annual, c.mid_rt_annual, c.max_rt_annual, a.sal_admin_plan ,E.HIRE_DT,
TO_CHAR(((SYSDATE - P.BIRTHDATE) / 365), 99) AGE
,TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) TOTAL_YEARS_WORKED
,TO_CHAR((SYSDATE - E.HIRE_DT),999999) TOTAL_DAYS_WORKED
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 5 THEN '5 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - 1825) * -1), 99999) END) TOTAL_DAYS_UNTIL_5_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 10 THEN '10 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 2)) * -1), 999999) END) TOTAL_DAYS_UNTIL_10_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 15 THEN '15 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 3)) * -1), 999999) END) TOTAL_DAYS_UNTIL_15_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 20 THEN '20 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 4)) * -1), 999999) END) TOTAL_DAYS_UNTIL_20_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 25 THEN '25 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 5)) * -1), 999999) END) TOTAL_DAYS_UNTIL_25_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 30 THEN '30 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 6)) * -1), 999999) END) TOTAL_DAYS_UNTIL_30_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 35 THEN '35 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 7)) * -1), 999999) END) TOTAL_DAYS_UNTIL_35_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 40 THEN '40 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 8)) * -1), 999999) END) TOTAL_DAYS_UNTIL_40_YRS_SERV
from ps_job a, ps_names b, ps_sal_grade_tbl c, ps_employment e, ps_person p
where a.reports_to in ('&reports_to')
and a.emplid = b.emplid
and a.emplid = e.emplid
and a.emplid = p.emplid
and b.name_type = 'PRI'
and b.effdt = (select max(b1.effdt) from ps_names b1
where b1.emplid = b.emplid
and b1.name_type = 'PRI'
and b1.effdt <= sysdate)
and c.setid = '&setid'
and c.grade = a.grade
and c.sal_admin_plan = a.sal_admin_plan
and c.effdt = (select max(c1.effdt) from ps_sal_grade_tbl c1
where c1.setid = c.setid
and c1.grade = c.grade
and c1.sal_admin_plan = c.sal_admin_plan
and c1.effdt <= sysdate)
and a.effdt = (select max(a1.effdt) from ps_job a1
where a1.emplid = a.emplid
and a1.empl_rcd = a.empl_rcd
and a1.effseq = a.effseq
and a1.effdt <= sysdate)
and a.effseq = (select max(a2.effseq ) from ps_job a2
where a2.emplid = a.emplid
and a2.empl_rcd = a.empl_rcd
and a2.effdt = a.effdt )
order by a.reports_to, total_years_worked desc, b.name

Finding Menu Path

Here are some good SQL's to have in your library.

When a process name is known:

SELECT DISTINCT
PRCS.PRCSTYPE,
PRCS.PRCSNAME,
PRCS.DESCR,
PAGE.PNLGRPNAME as Component,
'Home > ' || RTRIM(M.MENUGROUP) || ' > ' || RTRIM(M.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as Location
FROM PSMENUDEFN M,
PSMENUITEM ITEM,
PS_PRCSDEFNPNL PAGE,
PS_PRCSDEFN PRCS
WHERE M.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = PAGE.PNLGRPNAME
AND PAGE.PRCSTYPE = PRCS.PRCSTYPE
AND PAGE.PRCSNAME = PRCS.PRCSNAME
AND PRCS.PRCSNAME = 'PY_PULL_COST'


When a component name is known:

SELECT DISTINCT PRCS.PRCSTYPE , PRCS.PRCSNAME , PRCS.DESCR ,
PAGE.PNLGRPNAME as Component , 'Home > ' || RTRIM(MENU.MENUGROUP) || ' > ' ||
RTRIM(MENU.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PS_PRCSDEFNPNL PAGE , PS_PRCSDEFN PRCS
WHERE MENU.MENUNAME = ITEM.MENUNAMEAND ITEM.PNLGRPNAME = PAGE.PNLGRPNAME
AND PAGE.PRCSTYPE = PRCS.PRCSTYPE
AND PAGE.PRCSNAME = PRCS.PRCSNAME
AND PAGE.PNLGRPNAME = 'component-name'

When a record name is known:

SELECT DISTINCT PFLD.RECNAME ,
PFLD.PNLNAME as Page , 'Home > ' || RTRIM(MENU.MENUGROUP) || ' > ' ||
RTRIM(MENU.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP COMP , PSPNLFIELD PFLD
WHERE MENU.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = PFLD.PNLNAME
AND PFLD.RECNAME = 'record-name'

When a page name is known:

SELECT DISTINCT COMP.PNLNAME as Page , 'Home > ' ||
RTRIM(MENU.MENUGROUP) || ' > ' || RTRIM(MENU.MENULABEL) || ' > ' ||
RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP COMP
WHERE MENU.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = 'page-name'