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'

Peoplecode to Read and Write a File - Send email within Peoplecode using SendMail() function

Here is some sample code to read and write a file. You will have to design your code to handle your environment paths...

/*** Read a file ***/

Local File &ASU_ADDR_WRK;
Local Rowset &rsInput_Rowset;
Local Record &Rec1;
Local SQL &SQL1;
&Rec1 = CreateRecord(Record.ASU_ADDR_WRK);
&SQL1 = CreateSQL("%Insert(:1)");
If ASU_ADDR_AET.PROCESS_INSTANCE > 0 Then
&pshome = GetEnv("PS_HOME");
&InboundDirectory = &pshome "/datafiles/interfacein/";
&Filename = &InboundDirectory “asu_addr.csv";
End-If;
If FileExists(&Filename, %FilePath_Absolute) Then
&ASU_ADDR_WRK = GetFile(&Filename, "R", "A", %FilePath_Absolute);
End-If;
&ASU_ADDR_WRK.SetFileLayout(FileLayout.ASU_ADDR_WRK);
&rsInput_Rowset = CreateRowset(Record.ASU_ADDR_WRK);
&rsInput_Rowset = &ASU_ADDR_WRK.ReadRowset();
While &rsInput_Rowset <> Null
&rsInput_Rowset.GetRow(1).ASU_ADDR_WRK.CopyFieldsTo(&Rec1);
&SQL1.Execute(&Rec1);
&rsInput_Rowset = &ASU_ADDR_WRK.ReadRowset();
End-While;
&ASU_ADDR_WRK.Close();


/*** Write to a file ***/

Local File &ASU_ADDR_DUP;
Local Record &Rec3;
Local SQL &SQL3;
&Rec3 = CreateRecord(Record.ASU_ADDR_DUP);
&SQL3 = CreateSQL("%SelectAll (:1) ORDER BY EMPLID, ADDRESS_TYPE", &Rec3);
If ASU_ADDR_AET.PROCESS_INSTANCE > 0 Then
&pshome = GetEnv("PS_HOME");
&OutboundDirectory = &pshome "/ datafiles/interfaceout/"; ";
&FileName2 = &OutboundDirectory “asu_addr_dup.csv";
End-If;
&ASU_ADDR_DUP = GetFile(&FileName2, "W", "A", %FilePath_Absolute);
If &ASU_ADDR_DUP.IsOpen Then
If &ASU_ADDR_DUP.SetFileLayout(FileLayout.ASU_ADDR_DUP) Then;
While &SQL3.Fetch(&Rec3)
&ASU_ADDR_DUP.WriteRecord(&Rec3);
End-While;
End-If;
End-If;
&ASU_ADDR_DUP.Close();


/*** Send and email ***/

&MAIL_FLAGS = 0;
&MAIL_TO = “Jeromy.McMahon@asu.edu";
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = “ASU Application Engine Training Class";
&MAIL_TEXT = "See attachment for the file created in AE class";
&pshome = GetEnv("PS_HOME");
&pshome "/datafiles/interfaceout/";
&MAIL_FILES = &pshome "asu_addr_dup.csv";
&MAIL_TITLES = “asu_addr_dup.csv";
&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES);

Find Employees who are terminated and not locked out of the system

Here is a rough query that will display all the employees who have a max termed row and are not locked out of the system.

SELECT DISTINCT A.OPRID, A.EMPLID, C.ALTER_EMPLID, A.ACCTLOCK, B.NAME, A.LASTUPDDTTM, A.LASTPSWDCHANGE FROM PSOPRDEFN A, PS_NAMES B, PS_PERS_DATA_EFFDT C, PS_JOB JOBWHERE A.ACCTLOCK <> 1 AND A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND JOB.EMPLID = A.EMPLID AND JOB.EFFDT = (SELECT MAX(JOB1.EFFDT) FROM PS_JOB JOB1 WHERE JOB1.EMPLID = JOB.EMPLID AND JOB1.EFFSEQ = JOB.EFFSEQ AND JOB1.EFFDT <= SYSDATE) AND JOB.EFFSEQ = (SELECT MAX(JOB2.EFFSEQ) FROM PS_JOB JOB2 WHERE JOB2.EMPLID = JOB.EMPLID AND JOB2.EFFDT = JOB.EFFDT) AND JOB.ACTION = 'TER'

Updating the PS_PAY_CALENDAR

Here is some sample code that will set the update flags for PS_PAY_CALENDAR.

update ps_pay_calendar
set PAY_SHEETS_RUN = 'Y', PAY_PRECALC_RUN = 'Y', PAY_CALC_RUN = 'Y', PAY_CONFIRM_START = 'Y', PAY_CONFIRM_RUN = 'Y', SINGLE_CHECK = 'Y', PAY_OFF_CYCLE_CLS = 'Y'
where run_id = '20070805' -- You will have to replace with your specific run_id

update ps_pay_calendar
set PAY_CONFIRM_START = 'Y', PAY_CONFIRM_RUN = 'Y'
where run_id = '20070729'

DBA Extents Segments - Sample SQL

select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocksfrom sys.v_$session s, sys.v_$sort_usage u where s.saddr = u.session_addr


SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name


select s.osuser, s.process, s.username, s.serial#, sum(u.blocks)*vp.value/1024 sort_sizefrom sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vpwhere s.saddr = u.session_addr and vp.name = 'db_block_size' --and s.osuser like '&1'group by s.osuser, s.process, s.username, s.serial#, vp.value

-- Free Table Space

SELECT /* + RULE */ df.tablespace_name tspace,df.bytes/(1024*1024) tot_ts_size,sum(fs.bytes)/(1024*1024) free_ts_size,nvl(round(sum(fs.bytes)*100/df.bytes),1) ts_pct,round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1 FROM dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) dfWHERE fs.tablespace_name(+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytesunion all

SELECT /* + RULE */ df.tablespace_name tspace, fs.bytes/(1024*1024) tot_ts_size, sum(df.bytes_free)/(1024*1024) free_ts_size, nvl(round((sum(fs.bytes)- df.bytes_used) *100/fs.bytes),1) ts_pct, round((sum(fs.BYTES) - df.BYTES_free )*100/fs.bytes) ts_pct1FROM dba_temp_files fs , (select tablespace_name, bytes_free, bytes_used from V$temp_space_header group by tablespace_name, bytes_free, bytes_used ) dfWHERE fs.tablespace_name(+) = df.tablespace_nameGROUP BY df.tablespace_name, fs.bytes, df.bytes_free,df.BYTES_usedorder by 4 desc

--Table spaces

SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_freeFROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total

--Rows for each statement using sort segment space:

SELECT S.sid ',' S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, T.sqladdr address, Q.hash_value, Q.sql_textFROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+)AND T.tablespace = TBS.tablespace_name ORDER BY S.sid

Peoplesoft perfomance

Monitoring Tools - Oracle Database


Written by David Vandiver
(Topic formerly known as "Active Sessions with SQL Statements")
This is a collection of SQL statements that you can run to see activity and performance on your PeopleSoft system.
You will need read permission to the following tables:
v$session
psoprdefn
v$sqlarea
v$sql_cursor
v$sql_bind_data
v%sql

The first SQL gives you active sessions, including the operator's name from the PSOPRDEFN table. There is also a "Kill Statement" built as one of the columns. This allows you to copy and paste the "Kill SQL" column and send it to the dba quickly.
-- Sessions and PSOPRDEFN

select nvl(b.oprdefndesc,'Blank'), a.CLIENT_INFO,a.OSUSER, a.machine, a.PROGRAM,a.LOCKWAIT, a.LOGON_TIME,'ALTER SYSTEM KILL SESSION ''' a.SID ',' a.SERIAL# ''';' "Kill SQL"from v$session a, psoprdefn bwhere status = 'ACTIVE'and a.USERNAME is not nulland substr(a.client_info, 1, 7) = b.oprid (+);


The last three are still being tweaked, but should work.

-- For statistics use:

SELECT * FROM (SELECT hash_value , sum(disk_reads) , sum(buffer_gets) ,sum(rows_processed), sum(buffer_gets)/greatest(sum(rows_processed),1) , sum(executions) , sum(buffer_gets)/greatest(sum(executions), 1) from V$SQL where command_type in (2,3,6,7) group by hash_value order by 5 desc) WHERE rownum <= 10;

--To get the SQL text for the above statistics use the following:

select t.SQL_TEXT from v$sqlarea t where t.HASH_VALUE in (SELECT hash_value FROM (SELECT hash_value , sum(disk_reads) , sum(buffer_gets) ,sum(rows_processed), sum(buffer_gets)/greatest(sum(rows_processed),1) , sum(executions) , sum(buffer_gets)/greatest(sum(executions), 1) from V$SQL where command_type in (2,3,6,7) group by hash_value order by 5 desc) WHERE rownum <= 10);

--To find the top ten run sql’s:

SELECT * FROM (SELECT hash_value,address,substr(sql_text,1,40) sql, buffer_gets, executions, buffer_gets/executions "Gets/Exec", sql_text "Full SQL Text" FROM V$SQLAREA WHERE buffer_gets > 100000 AND executions > 10 ORDER BY buffer_gets DESC)WHERE rownum <= 10;

Hidden Folders and Content References

I wanted to post this on my blog as well. This was orginally posted by peoplesoftexperts.blogspot.com

• If you do not see a folder or content Reference (Menu Item) in Left hand side navigation, (Applicable to 8.4x ) then you must check to see if the folder or content reference is not marked as hidden. Other reasons could be security. Here is a SQL to find out all the objects that are hidden.
  To find all the folders which are hidden from Portal Navigation.
  select * from PSPRSMSYSATTRVL where portal_name = 'EMPLOYEE' and PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'F'
• 
  select a.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_labelfrom PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c where a.portal_name = 'EMPLOYEE' and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and b.portal_Reftype = 'F' and a.portal_name = b.portal_name and a.portal_objname = b.portal_objnameand b.portal_name = c.portal_nameand b.portal_prntobjname = c.portal_objname
  
• To find all the content references which are hidden from Portal Navigation.
  select * from PSPRSMSYSATTRVL where portal_name = 'EMPLOYEE' and PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'C'
  

select a.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_labelfrom PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c where a.portal_name = 'EMPLOYEE' and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and b.portal_Reftype = 'C' and a.portal_name = b.portal_name and a.portal_objname = b.portal_objnameand b.portal_name = c.portal_nameand b.portal_prntobjname = c.portal_objname

Replace EMPLOYEE with Your portal name.
Some of the Other names are.
CUSTOMER
DEMOSITE
EMPLOYEE
MOBILE
PORTAL
PS_SITETEMPLATE
SUPPLIER

Portal Content Reference/folder Attributes are stored in
PSPRSMSYSATTR
PSPRSMSYSATTRVL