SQL Query to Retrieve all Records associated with a PeopleSoft Component

SELECT R.RECNAME AS RECORD_NAME,
       ( CASE
           WHEN R.RECTYPE = 0 THEN 'Table'
           WHEN R.RECTYPE = 1 THEN 'View'
           WHEN R.RECTYPE = 2 THEN 'Derived'
           WHEN R.RECTYPE = 3 THEN 'Sub Record'
           WHEN R.RECTYPE = 5 THEN 'Dynamic View'
           WHEN R.RECTYPE = 6 THEN 'Query View'
           WHEN R.RECTYPE = 7 THEN 'Temporary Table'
           ELSE 'Unknown'
         END )   AS RECORD_TYPE
FROM   PSRECDEFN R
WHERE  R.RECNAME IN (SELECT DISTINCT RECNAME
                     FROM   PSPNLFIELD
                     WHERE  PNLNAME IN (SELECT DISTINCT B.PNLNAME
                                        FROM   PSPNLGROUP A,
                                               PSPNLFIELD B
                                        WHERE  ( A.PNLNAME = B.PNLNAME
                                                  OR A.PNLNAME = B.SUBPNLNAME )
                                           AND A.PNLGRPNAME=:1 --Comp Name
                                           AND RECNAME <> ' ')
                     UNION
                     SELECT DISTINCT RECNAME
                     FROM   PSPNLFIELD
                     WHERE  PNLNAME IN (SELECT DISTINCT B.SUBPNLNAME
                                        FROM   PSPNLGROUP A,
                                               PSPNLFIELD B
                                        WHERE  ( A.PNLNAME = B.PNLNAME
                                                  OR A.PNLNAME = B.SUBPNLNAME )
                                           AND A.PNLGRPNAME=:1--Comp Name))
   AND R.RECNAME <> ' '
ORDER  BY R.RECTYPE ;

SELECT DISTINCT p.pnlgrpname,
                cp.market,
                cp.descr,
                pgf.recname,
                r.recdescr,
                r.rectype FROM  pspnlgrpdefn cp,
       pspnlgroup cpg,
       pspnldefn pg,
       pspnlfield pgf,
       psrecdefn r
WHERE cp.pnlgrpname = :1 -- Comp Name       AND cp.market = 'GBL'
       AND cp.pnlgrpname = cpg.pnlgrpname
       AND cp.market = cp.market
       AND pg.pnlname = cpg.pnlname
       AND pgf.pnlname = pg.pnlname
       AND pgf.recname = r.recname;

SQL Queries to find Key, Search Key, Alternate Search Keys of a record and query to get Search Key fields of a CI

SQL query to find key fields in a record:

SELECT KEYPOSN, FIELDNAME,ASCDESC
FROM PSKEYDEFN
WHERE RECNAME = 'POSITION_DATA'
AND INDEXID = '_'
ORDER BY 1;

Alternate SQL query to find key fields in a record:
SELECT FIELDNAME FROM PSRECFIELD
WHERE RECNAME = 'POSITION_DATA'
AND BITAND(USEEDIT,1)=1;

SQL Query to find search keys in a record:
SELECT FIELDNAME FROM PSRECFIELD
WHERE RECNAME = 'POSITION_DATA'
AND BITAND(USEEDIT,2048)=2048;

SQL Query to list alternate search keys in a record:
SELECT FIELDNAME FROM PSRECFIELD
WHERE RECNAME = 'POSITION_DATA'
AND BITAND(USEEDIT,16)=16;

SQL Query to list both the search and alternate search keys of a record:
SELECT FIELDNAME FROM PSRECFIELD
WHERE RECNAME = 'POSITION_DATA'
AND (BITAND(USEEDIT,16)=16
or BITAND(USEEDIT,2048)=2048);

SQL Query to get Search Key Fields of a Component Interface:

SELECT A.FIELDNAME FROM PSRECFIELD A WHERE A.RECNAME IN ((SELECT B.SEARCHRECNAME FROM PSBCDEFN B WHERE B.BCNAME =:1) UNION

(SELECT B.SEARCHRECNAME FROM PSBCDEFN B WHERE B.BCNAME = :1)) AND BITAND(A.USEEDIT,2048)=2048

SQL Query to identify the Level 0 record of a component

The below SQL will give all the records of a component along with its corresponding occurs levels:

SELECT DISTINCT D.recname     TableName,
                D.occurslevel ScrollName
FROM   pspnlgrpdefn A,
       pspnlgroup B,
       pspnldefn C,
       pspnlfield D,
       psrecdefn E
WHERE  A.pnlgrpname = B.pnlgrpname
       AND A.market = B.market
       AND B.pnlname = C.pnlname
       AND C.pnlname = D.pnlname
       AND A.pnlgrpname = 'POSITION_DATA' --Component Name
       AND E.recname = D.recname
       /* Restricts to only tables and eliminates views and derived records */
       AND E.rectype IN ( 0 )
       /*This condition eliminates records in component as Related Fields*/
       AND To_char(Bitand(D.fielduse, 16)) <> '16'
ORDER  BY 2,1

SQL Query to fetch list of all processes\jobs in a recurrence

SELECT process_job_name,
       description,
       recurname
FROM   ps_prcsrecur a,
       (SELECT p.prcsname  AS Process_Job_name,
               p.descr     AS Description,
               p.recurname AS recurname
        FROM   ps_prcsdefn p
        UNION
        SELECT jp.prcsjobname AS Process_Job_name,
               (SELECT j.descr
                FROM   ps_prcsdefn j
                WHERE  j.prcsname = jp.prcsname) AS Description,
               jp.recurname AS recurname
        FROM   ps_prcsjobdefn j,
               ps_prcsjobitem jp
        WHERE  j.prcsjobname = jp.prcsjobname) b
WHERE b.recurname = a.recurname

AND a.recurname = :1

ORDER BY 1

SQL Query to find Records and Fields used in a PeopleSoft Page

1. SQL Query to find all the Records and Fields used in a PeopleSoft page:

SELECT recname, fieldname
FROM   pspnlfield
WHERE  pnlname = PageName;

* Replace PageName with the actual Page name you want to reference

2. SQL Query to find all the Records where a particular PeopleSoft field is used:

SELECT DISTINCT recname, fieldname 
FROM   psrecfield 
WHERE  fieldname = FieldName;

* Replace PageName with the actual Page name you want to reference

3. SQL query to find all the page names where a field is used from a particular record:

SELECT pnlname
FROM   pspnlfield
WHERE  recname =RecordName
AND fieldname = FieldName;

* Replace RecordName and FieldName with the actual record name and field name respectively. You can also remove the and condition if needed.

SQL Query to get Maximum effective dated template in BI Publisher:

SELECT ED.REPORT_DEFN_ID,ED.TMPLDEFN_ID, ED.TEMPLATE_FILEID FROM PSXPRPTTMPLF_VW ED WHERE REPORT_DEFN_ID = <‘Your Report Defn ID‘> AND TMPLDEFN_ID = <‘Your Template ID’> AND EFFDT = (SELECT MAX(ED1.EFFDT) FROM PSXPRPTTMPLF_VW ED1 WHERE ED.REPORT_DEFN_ID= ED1.REPORT_DEFN_ID AND ED.TMPLDEFN_ID=ED1.TMPLDEFN_ID AND ED1.EFFDT <= SYSDATE) AND TMPLFILE_STATUS = ‘A’

SQL Query to get Maximum Effective dated Row:

select a.effdt from PS_JOB a where a.emplid=’010001′
and a.effdt=(Select max(b.effdt) from PS_JOB b where b.emplid=a.emplid and b.empl_rcd=a.empl_rcd and b.effdt<=SYSDATE)
and a.effseq=(select max(c.effseq) from PS_JOB c where c.emplid=a.emplid and c.empl_rcd=a.empl_rcd and c.effdt=a.effdt);

SQL Query to get nth maximum Salary:

select distinct(a.salary) from Table a where <Provide your n-th value here>=(select count(distinct(b.salary)) from table b where a.salary<=b.salary);

SQL Query to Update the table space name for Records in a project:

Update PSRECTBLSPC SET DDLSPACENAME = ‘<your new table space name>’ WHERE DDLSPACENAME = ‘<your old table space name>’ and RECNAME IN (SELECT A.OBJECTVALUE1 FROM PSPROJECTITEM A, PSRECDEFN B Where A.PROJECTNAME = ‘<Your Project name>’ and A.OBJECTTYPE = 0 and A.OBJECTID1 = 1 and A.OBJECTVALUE1 = B.RECNAME and B.RECTYPE =0)

Navigation for Component:

SELECT RTRIM(E.PORTAL_LABEL)
|| ‘ >> ‘ || RTRIM(D.PORTAL_LABEL)
|| ‘ >> ‘ || RTRIM(C.PORTAL_LABEL)
|| ‘ >> ‘ || RTRIM(B.PORTAL_LABEL)
|| ‘ >> ‘ || RTRIM(A.PORTAL_LABEL)
FROM PSPRSMDEFN A LEFT JOIN PSPRSMDEFN B ON B.PORTAL_NAME = A.PORTAL_NAME AND B.PORTAL_OBJNAME = A.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN C ON C.PORTAL_NAME = B.PORTAL_NAME AND C.PORTAL_OBJNAME = B.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN D ON D.PORTAL_NAME = C.PORTAL_NAME AND D.PORTAL_OBJNAME = C.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN E ON E.PORTAL_NAME = D.PORTAL_NAME AND E.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME WHERE A.PORTAL_URI_SEG2 = ‘JOB_DATA’

select distinct rtrim(reverse
         (sys_connect_by_path(reverse
         (portal_label), ' > ')), ' > ') path
  from psprsmdefn
where portal_name = 'EMPLOYEE'
   and portal_prntobjname = 'PORTAL_ROOT_OBJECT'
start with portal_uri_seg2 = :1
connect by prior
         portal_prntobjname = portal_objname

Query to get Childs for Manager:

SELECT EMPLID, sy_manager_id FROM PS_SY_PERSON_DTL
CONNECT BY nocycle PRIOR EMPLID = SY_MANAGER_ID
START WITH EMPLID = ‘<ManagerID>‘;

Query to get data count for PS Tables:

SELECT TABLE_NAME,
TO_NUMBER(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML(‘SELECT COUNT(*) CNT FROM ‘||TABLE_NAME)),’/ROWSET/ROW/CNT’)) AS COUNT
FROM USER_TABLES WHERE TABLE_NAME = ‘PS_JOB’;

Find long running SQL queries

select * from
(
select
     opname,
     start_time,
     target,
     sofar,
     totalwork,
     units,
     elapsed_seconds,
     message
   from
        v$session_longops
order by start_time desc
)
where rownum <=1;

This query for long running SQL is especially useful when operations contain long running loops such as shown in the example below.

DECLARE
l_rindex PLS_INTEGER;
l_slno PLS_INTEGER;
l_totalwork NUMBER;
l_sofar NUMBER;
l_obj PLS_INTEGER;
BEGIN
l_rindex := DBMS_APPLICATION_INFO.set_session_longops_nohint;
l_sofar := 0;
l_totalwork := 10;
WHILE l_sofar < 10 LOOP
-- Do some work
DBMS_LOCK.sleep(5);
l_sofar := l_sofar + 1;
DBMS_APPLICATION_INFO.set_session_longops(
rindex => l_rindex,
slno => l_slno,
op_name => 'BATCH_LOAD',
target => l_obj,
context => 0,
sofar => l_sofar,
totalwork => l_totalwork,
target_desc => 'BATCH_LOAD_TABLE',
units => 'rows');
END LOOP;
END;
/

While the above code is running, the contents of the v$session_longops view can be queried as follows.

COLUMN opname FORMAT A20
COLUMN target_desc FORMAT A20
COLUMN units FORMAT A10

select
   opname,
   target_desc,
   sofar,
   totalwork,
   time_remaining,
   units
from
   v$session_longops

The type of output expected from this v$session_longops query is listed below.

OPNAME TARGET_DESC SOFAR TOTALWORK UNITS
-------------------- -------------------- ---------- ---------- ----
BATCH_LOAD BATCH_LOAD_TABLE 3 10 rows

Querying AWR for historical long running SQL

Here is a SQL query to locate historical SQL (over the past 40 days) that has an execution time greater than 20 wall clock seconds:

set wrap off
col elapsed_time_delta format 9999999999
col plan_hash_value    format 9999999999
col seconds            format 99999
col executions_total   format 99999999

select
   stat.sql_id,
   plan_hash_value,
   rpad(parsing_schema_name,10) "schema",elapsed_time_total/1000000 "seconds",
   elapsed_time_delta,disk_reads_delta,
   stat.executions_total,
   to_char(ss.end_interval_time,'dd-mm-yy hh24:mi:ss') "endtime",
   rpad(sql_text,40) text,ss.snap_id
from
   dba_hist_sqlstat stat,
   dba_hist_sqltext txt,
   dba_hist_snapshot ss

where
   stat.sql_id = txt.sql_id
and
   stat.dbid = txt.dbid
and
   ss.dbid = stat.dbid
and
   ss.instance_number = stat.instance_number
and
   stat.snap_id = ss.snap_id
and
   parsing_schema_name not like 'sys%'
and
   ss.begin_interval_time >= sysdate-40
and
   stat.elapsed_time_total/1000000 > 20
order by
   elapsed_time_total desc;

FIELD PROPERTIES
----

SELECT DISTINCT
FD.FIELDNAME
,CASE FD.FIELDTYPE
WHEN 0 THEN 'Char'
WHEN 1 THEN 'Long Char'
WHEN 2 THEN 'NBR'
WHEN 3 THEN 'Sign'
WHEN 4 THEN 'Date'
WHEN 5 THEN 'Time'
WHEN 6 THEN 'Dttm'
WHEN 7 THEN 'Img'
WHEN 8 THEN 'Vers'
ELSE ' ' END "TYPE"
,FD.LENGTH
,FL.SHORTNAME
,FL.LONGNAME
FROM PSDBFIELD FD,
PSDBFLDLABL FL,
PSPROJECTITEM PI
WHERE FD.FIELDNAME = FL.FIELDNAME
AND PI.OBJECTVALUE1 = FD.FIELDNAME
AND PI.OBJECTTYPE = 2
AND PI.OBJECTID1 = 6
AND FD.FIELDNAME = '' *** give ur fieldname here
ORDER BY FD.FIELDNAME;

TRANSLATE VALUES
----

SELECT DISTINCT
X.FIELDVALUE
,X.EFFDT
,X.EFF_STATUS
,X.XLATSHORTNAME
,X.XLATLONGNAME
FROM PSPROJECTITEM A, PSXLATITEM X
WHERE A.OBJECTTYPE = 4
AND A.OBJECTID1 = 6
AND X.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PSXLATITEM B_ED
WHERE X.FIELDNAME = B_ED.FIELDNAME
AND X.FIELDVALUE = B_ED.FIELDVALUE
AND B_ED.EFFDT <= CURRENT DATE)
AND X.FIELDNAME = ''; *** give ur fieldname here

RECORD FIELD PROPERTIES
----—-

SELECT DISTINCT
PR.RECNAME,
PR.FIELDNUM,
PR.FIELDNAME,
CASE MOD(PR.USEEDIT,2)
WHEN 1 THEN 'Y'
ELSE ' '
END "KEY/REQ",
CASE MOD((PR.USEEDIT/256),2)
WHEN 1 THEN 'R'
ELSE ' ' END "KEY/REQ",
CASE MOD((PR.USEEDIT/2048),2)
WHEN 1 THEN 'YES'
ELSE ' ' END "SEARCH KEY",
CASE MOD((PR.USEEDIT/32),2)
WHEN 1 THEN 'YES'
ELSE ' ' END "LIST BOX ITEM",
CASE MOD((PR.USEEDIT/16),2)
WHEN 1 THEN 'ALT'
ELSE ' '
END "ALT SEARCH KEY"
FROM PSRECFIELD PR,
PSPROJECTITEM A,
PSRECDEFN D
WHERE PR.RECNAME = D.RECNAME
AND A.OBJECTTYPE = 0
AND A.OBJECTID1 = 1
AND PR.RECNAME = '' *** give ur recname here
ORDER BY PR.RECNAME,PR.FIELDNUM;

PAGE FIELD PROPERTIES
-----

SELECT distinct
PF.PNLNAME
,PF.FIELDNUM
,PF.OCCURSLEVEL AS "LVL"
,PF.LBLTEXT AS "LABEL"
,CASE PF.FIELDTYPE
WHEN 1 THEN 'Frame'
WHEN 2 THEN 'Group Box'
WHEN 3 THEN 'Static Image'
WHEN 4 THEN 'Edit Box'
WHEN 5 THEN 'Drop Down List'
WHEN 6 THEN 'Long Edit Box'
WHEN 7 THEN 'Check Box'
WHEN 8 THEN 'Radio Button'
WHEN 9 THEN 'Image'
WHEN 10 THEN 'Scroll Bar'
WHEN 11 THEN 'SubPage'
WHEN 12 THEN 'Push Button/Hyper Link'
WHEN 13 THEN 'Push Button/Hyper Link'
WHEN 14 THEN 'Push Button/Hyper Link'
WHEN 15 THEN 'Push Button/Hyper Link'
WHEN 16 THEN 'Push Button/Hyper Link'
WHEN 17 THEN 'Text'
WHEN 18 THEN 'SecPage'
WHEN 19 THEN 'Grid'
WHEN 20 THEN 'Tree'
WHEN 21 THEN 'Push Button/HyperLink'
WHEN 23 THEN 'Horizontle Rule'
WHEN 24 THEN 'Tab Separator'
WHEN 25 THEN 'HTML Area'
WHEN 26 THEN 'Push Button/Hyper Link'
WHEN 27 THEN 'Scroll Area'
ELSE ' '
END "TYPE"
,PF.FIELDNAME
,PF.RECNAME
FROM PSPNLFIELD PF,
PSPROJECTITEM A,
PSPNLDEFN F
WHERE F.PNLNAME = PF.PNLNAME
AND A.OBJECTTYPE = 5
AND A.OBJECTID1 = 9
AND F.PNLNAME = ''
ORDER BY PF.PNLNAME,PF.FIELDNUM;

COMPONENT PROPERTIES
------

SELECT DISTINCT
MI.PNLGRPNAME AS "COMPONENT"
,MI.ITEMLABEL AS "DESCRIPTION"
,PG.ITEMLABEL AS "ITEM LABEL"
,PG.FOLDERTABLABEL AS "FOLDER TAB LABEL"
,H.SEARCHRECNAME AS "SEARCH RECORD"
,PG.PNLNAME AS "PAGE NAME"
FROM PSMENUITEM MI,
PSPNLGROUP PG,
PSPNLGRPDEFN H,
PSPROJECTITEM A
WHERE MI.PNLGRPNAME = PG.PNLGRPNAME
AND PG.PNLGRPNAME = H.PNLGRPNAME
AND PG.MARKET = H.MARKET
AND A.OBJECTTYPE = 7
AND A.OBJECTID1 = 10
AND PG.PNLGRPNAME = ''

FIELDS FROM A PROJECT

select projectname,'Field',objectvalue1,Objectvalue2,objectvalue3 from hrg74d.psprojectitem where objecttype=2 and objectid1=6 and projectname ='H0091_4'

*FIELD TRANSLATE

select projectname,'Field Translate Values',objectvalue1,Objectvalue2,objectvalue3 from hrg74d.psprojectitem where objecttype=4 and objectid1=6 and projectname ='H0091_4'

RECORDS IN PROJECT

select projectname,'Record',objectvalue1,Objectvalue2,objectvalue3 from hrg74d.psprojectitem where objecttype=0 and objectid1=1 and projectname ='H0091_4'

*RECORD VIEW SQL IN PROJECT

select projectname,'Record View SQL',objectvalue1,Objectvalue2,objectvalue3 from hrg74d.psprojectitem where objecttype=30 and objectid1=65 and projectname ='H0091_4'

***PAGE IN PROJECT

select projectname,'Page',objectvalue1,Objectvalue2,objectvalue3 from hrg74d.psprojectitem where objecttype=5 and objectid1=9 and projectname ='H0091_4'

COMPONENT IN PROJECT

select projectname,'Component',objectvalue1,Objectvalue2,objectvalue3 from hrg74d.psprojectitem where objecttype=7 and objectid1=10 and projectname ='H0091_4'

***MENU IN PROJECT

select projectname,'Menu',objectvalue1,Objectvalue2,objectvalue3 from hrg74d.psprojectitem where objecttype=6 and objectid1=3 and projectname ='H0091_4'

* RECORD FIELD PEOPLECODE

select projectname,'Record Field Peoplecode',objectvalue1,Objectvalue2,objectvalue3 from hrg74d.psprojectitem where objecttype=8 and objectid1=1 and projectname ='H0091_4'

select projectname,'Record Field Peoplecode',objectvalue1,Objectvalue2,objectvalue3 from hrg74d.psprojectitem where objecttype=44 and objectid1=9 and projectname ='H0091_4'

***QUERY

select projectname,'Query',objectvalue1,Objectvalue2,objectvalue3 from hrg74d.psprojectitem where objecttype=10 and objectid1=30 and projectname ='H0091_4'

**COMPONENT INTERFACE

select projectname,'Component Interface',objectvalue1,Objectvalue2,objectvalue3 from hrg74d.psprojectitem where objecttype=32 and objectid1=74 and projectname ='H0091_4'

***MESSAGE CATALOG ENTRIES

select projectname,'Message Catalog Entries',objectvalue1,Objectvalue2,objectvalue3 from hrg74d.psprojectitem where objecttype=25 and objectid1=48 and projectname =H0091_4'

RELATED TO INDEX

select NAME, TBNAME, DBNAME from sysibm.sysindexes where tbname = 'PS_GP_RSLT_ACUM' AND TBCREATOR IN ('HRW08D', 'HRW14D', 'HRW54D')

*DESCRIPTION IN DB2
DESCRIBE SELECT * FROM PSRECDEFN

TO FIND DATABSE OWNER
SELECT * FROM PS.PSDBOWNER WHERE DBNAME = 'E89SAT'

*PAGE ACCESS **

Input : -User ID
-Page Name for which you want to check the access type.

-------------
Notations used in the query output.
-------------
C Correction
U-DA Update Display All
UD Update Display
A Add
-------------

SELECT A.ROLEUSER,C.PNLITEMNAME,A.ROLENAME,B.CLASSID,
CASE WHEN C.AUTHORIZEDACTIONS = 15
THEN 'C|U-DA|UD|A'
ELSE
CASE WHEN C.AUTHORIZEDACTIONS = 14
THEN 'C|U-DA|UD| '
ELSE
CASE WHEN C.AUTHORIZEDACTIONS = 13
THEN 'C|U-DA| |A'
ELSE
CASE WHEN C.AUTHORIZEDACTIONS = 12
THEN 'C|U-DA| | '
ELSE
CASE WHEN C.AUTHORIZEDACTIONS = 11
THEN 'C| |UD|A'
ELSE
CASE WHEN C.AUTHORIZEDACTIONS = 10
THEN 'C| |UD| '
ELSE
CASE WHEN C.AUTHORIZEDACTIONS = 9
THEN 'C| | |A'
ELSE
CASE WHEN C.AUTHORIZEDACTIONS = 8
THEN 'C| | | '
ELSE
CASE WHEN C.AUTHORIZEDACTIONS = 7
THEN '|U-DA|UD|A'
ELSE
CASE WHEN C.AUTHORIZEDACTIONS = 6
THEN ' |U-DA|UD| '
ELSE
CASE WHEN C.AUTHORIZEDACTIONS = 5

THEN ' |U-DA| |A'
ELSE
CASE WHEN C.AUTHORIZEDACTIONS = 4

THEN ' |U-DA| | '

ELSE

CASE WHEN C.AUTHORIZEDACTIONS = 3

THEN ' | |UD|A'

ELSE

CASE WHEN C.AUTHORIZEDACTIONS = 2

THEN ' | |UD| '

ELSE

CASE WHEN C.AUTHORIZEDACTIONS = 1

THEN '
| | |A'

ELSE
CASE
WHEN C.AUTHORIZEDACTIONS = 0
THEN ' | | | '

ELSE

'Invalid AUTHORIZATION Type'

END

END AS ALLOWED

FROM PSROLEUSER A,PSROLECLASS B,PSAUTHITEM C
WHERE C.CLASSID = B.CLASSID
AND B.ROLENAME = A.ROLENAME
AND C.PNLITEMNAME = 'EMPLOYEE_REVIEW_1'
AND A.ROLEUSER = 'RATANK'

/*
TO GET URL FOR A COMPONENT & TO MAP COMPONENT TO PROCESS.
*
SELECT PORTAL_URLTEXT FROM PSPRSMDEFN
WHERE PORTAL_URLTEXT LIKE '%HSBC_RUN_EMPDEVPFL%'

SELECT * FROM HRW07D.PS_PRCSDEFNPNL
WHERE PNLGRPNAME LIKE '%HSBC_RUN_TRNPROF%'
***/

sysibm.syscolumns
sysibm.systable

--------

--------
To get the Key for a particular table
--------

select * from pskeydefn where recname in ='PSPROJDEFNLANG' order by keyposn
select * from pskeydefn where recname in ( 'PSACTIVITYDEFN', 'PSACTIVITYLANG', 'PSAEAPPLDEFN', 'PSAEAPPLLANG', 'PSAESECTDTLDEFN', 'PSAESTEPDEFN', 'PSAESTMTDEFN', 'PSBCDEFN', 'PSBCDEFNLANG', 'PSBUSPITEMLANG', 'PSBUSPROCDEFN', 'PSBUSPROCITEM', 'PSBUSPROCLANG', 'PSCHGCTLHIST', 'PSCHGCTLLOCK', 'PSCHNLDEFN', 'PSCHNLDEFNLANG', 'PSDBFIELD', 'PSDBFIELDLANG', 'PSFLDDEFN', 'PSMENUDEFN', 'PSMENUDEFNLANG', 'PSMSGDEFN', 'PSMSGDEFNLANG', 'PSMSGNODEDEFN', 'PSNODEDEFNLANG', 'PSOPRALIASTYPE', 'PSOPRALSTYPLANG', 'PSPNLDEFN', 'PSPNLGDEFNLANG', 'PSPNLGRPDEFN', 'PSPROJDEFNLANG', 'PSPROJECTDEFN', 'PSQRYDEFN', 'PSQRYDEFNLANG', 'PSRECDEFN', 'PSRECDEFNLANG', 'PSROLEDEFN', 'PSROLEDEFN', 'PSSQLDESCR', 'PSSQLLANG', 'PSVAITEM', 'PSVAITEMLANG') order by keyposn

--------
To get the Database Owner Name
--------
select * from ps.psdbowner where dbname = 'HR89dmo'

--------
To get the table description
--------

DESCRIBE select * from psrecdefn

select TBNAME from syscolumns where name = 'DESCRLONG' AND TBNAME IN(SELECT RECNAME FROM PSRECDEFN WHERE RECTYPE=0)

--------
Gettin all the Fields in a Project
--------
select A.projectname,B.fieldname
from hrg48d.psprojectitem A,
psdbfield B

where A.objectvalue1 = B.fieldname
and A.objecttype = 2
and A.objectid1 = 6
fetch first 100 rows only

--------
Gettin all the Records in Project
--------

select A.projectname,B.recname
from psprojectitem A,hrg48d.psrecdefn B
where A.objectvalue1 = B.recname
and A.objecttype = 0
and A.objectid1 = 1
fetch first 100 rows only

--------
Gettin all the Recname and Refname from PSPCMNAME for rec fld pplcode
--------

select distinct objectvalue1,';',objectvalue2,';',objectvalue3,';',namenum,';',recname,';',refname from hrg30d.pspcmname

--------
Gettin all the Recname and Refname from PSPCMNAME for AppEngine Programs
--------
select objectvalue1,';',objectvalue2,';',objectvalue6,';',namenum,';',recname,';',refname from hrg30d.pspcmname
where objectid1=66
and namenum <> 1

/*----——
New Table

psaestmtdefn
-----*/

/*----——
iMPORTANT Notes
1):- In psrecdefn field value rectype = 0 signify that its a SQL Table.

-----*/

**
Determine Objects in a project.
**

select B.LASTUPDDTTM,A.objecttype,A.objectid1,A.OBJECTVALUE1||A.OBJECTVALUE2||A.OBJECTVALUE3 ||A.OBJECTVALUE4 from hrg54d.psprojectitem A,HRG54D.PSPROJECTDEFN B where A.projectname ='HSBC_TS45001'

AND A.PROJECTNAME = B.PROJECTNAME

***

GET PORTAL TEXT**

select portal_urltext from psprsmdefn where portal_urltext like '%HSBC_RUN_TR%'

*MESSAGE CATALOG TABLE IN 8.3

SELECT * FROM PS_MESSAGE_SET_TBL

DESCRIBE SELECT MESSAGE_TEXT,LANGUAGE_CD FROM HRW28D.PS_MESSAGE_CATALOG WHERE MESSAGE_SET_NBR = 22001 AND MESSAGE_NBR = 4

*MESSAGE CATALOG TABLE IN 8.9

SELECT * FROM PSMSGSETDEFN

SELECT MESSAGE_TEXT,LANGUAGE_CD FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 22001 AND MESSAGE_NBR = 4

SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 22001 AND MESSAGE_NBR = 4

***

REQUIERED FIELD OR NOT***

SELECT FIELDNAME, CASE MOD((USEEDIT/256),2) WHEN 1 THEN 'Y' ELSE 'N' END REQUIRED
FROM HRG70D.PSRECFIELD
WHERE RECNAME = 'HSBC_TE_TIMEENT'

People tools Export and Meta tables Information

— SYSTEM
EXPORT PSOPTIONS;

— RECORDS AND INDEXES
EXPORT PSRECDEFN;
EXPORT PSRECDEFNLANG;
EXPORT PSRECFIELD;
EXPORT PSINDEXDEFN;
EXPORT PSKEYDEFN;
EXPORT PSDDLMODEL;
EXPORT PSDDLDEFPARMS;
EXPORT PSSPCDDLPARM;
EXPORT PSRECDDLPARM;
EXPORT PSIDXDDLPARM;
EXPORT PSRECFIELDDB;
EXPORT PSRECTBLSPC;

— FIELDS
EXPORT PSDBFIELD;
EXPORT PSDBFIELDLANG;
EXPORT PSDBFLDLABL;
EXPORT PSDBFLDLABLLANG;

— FIELD FORMATS
EXPORT PSFMTDEFN;
EXPORT PSFMTITEM;

— TRANSLATES
EXPORT PSXLATDEFN;
EXPORT PSXLATITEM;
EXPORT PSXLATITEMLANG;

— PAGES
EXPORT PSPNLDEFN;
EXPORT PSPNLFIELD;
EXPORT PSPNLTREECTRL;
EXPORT PSPNLHTMLAREA;
EXPORT PSPNLCNTRLDATA;
EXPORT PSPNLBTNDATA;
EXPORT PSPNLDEFNLANG;
EXPORT PSPNLFIELDLANG;
EXPORT PSPNLHTMLLANG;
EXPORT PSPNLBTNLANG;
EXPORT PSPNLACTIVEX;

— MENUS
EXPORT PSMENUDEFN;
EXPORT PSMENUDEFNLANG;
EXPORT PSMENUITEM;
EXPORT PSMENUITEMLANG;
EXPORT PSXFERITEM;

— PANEL GROUPS
EXPORT PSPNLGRPDEFN;
EXPORT PSPNLGDEFNLANG;
EXPORT PSPNLGROUP;
EXPORT PSPNLGROUPLANG;

— PEOPLECODE
EXPORT PSPCMPROG;
EXPORT PSPCMNAME;

— QUERIES
EXPORT PSQRYDEFN;
EXPORT PSQRYDEFNLANG;
EXPORT PSQRYBIND;
EXPORT PSQRYBINDLANG;
EXPORT PSQRYCRITERIA;
EXPORT PSQRYEXPR;
EXPORT PSQRYFIELD;
EXPORT PSQRYFIELDLANG;
EXPORT PSQRYFLAGS;
EXPORT PSQRYLINK;
EXPORT PSQRYRECORD;
EXPORT PSQRYSELECT;

— TREE STRUCTURES
EXPORT PSTREESTRCT;
EXPORT PSTREESTRCTLANG;

— TREES
EXPORT PSTREEDEFN;
EXPORT PSTREEDEFNLANG;
EXPORT PSTREEBRANCH;
EXPORT PSTREELEAF;
EXPORT PSTREELEVEL;
EXPORT PSTREENODE;
EXPORT PSTREEPROMPT;
EXPORT PSTREESELCTL;
EXPORT PSTREESELNUM;
EXPORT PSTREESELECT01;
EXPORT PSTREESELECT02;
EXPORT PSTREESELECT03;
EXPORT PSTREESELECT04;
EXPORT PSTREESELECT05;
EXPORT PSTREESELECT06;
EXPORT PSTREESELECT07;
EXPORT PSTREESELECT08;
EXPORT PSTREESELECT09;
EXPORT PSTREESELECT10;
EXPORT PSTREESELECT11;
EXPORT PSTREESELECT12;
EXPORT PSTREESELECT13;
EXPORT PSTREESELECT14;
EXPORT PSTREESELECT15;
EXPORT PSTREESELECT16;
EXPORT PSTREESELECT17;
EXPORT PSTREESELECT18;
EXPORT PSTREESELECT19;
EXPORT PSTREESELECT20;
EXPORT PSTREESELECT21;
EXPORT PSTREESELECT22;
EXPORT PSTREESELECT23;
EXPORT PSTREESELECT24;
EXPORT PSTREESELECT25;
EXPORT PSTREESELECT26;
EXPORT PSTREESELECT27;
EXPORT PSTREESELECT28;
EXPORT PSTREESELECT29;
EXPORT PSTREESELECT30;

— ACCESS GROUPS
EXPORT ACCESS_GRP_TBL;
EXPORT ACCESS_GRP_LANG;

— COLORS
EXPORT PSCOLORDEFN;

— STYLES
EXPORT PSSTYLEDEFN;
EXPORT PSSTYLEDEFNLANG;

— BUSINESS PROCESSES
EXPORT PSBUSPROCDEFN;
EXPORT PSBUSPROCITEM;
EXPORT PSBUSPROCLANG;
EXPORT PSBUSPROCSEC;
EXPORT PSBUSPRIMG;
EXPORT PSBUSPITEMIMG;
EXPORT PSBUSPITEMLANG;
EXPORT PSBUSPRIMGLANG;

— ACTIVITIES
EXPORT PSACTIVITYDEFN;
EXPORT PSACTIVITYLANG;
EXPORT PSACTIVIMG;
EXPORT PSACTIVIMGLANG;
EXPORT PSSTEPDEFN;
EXPORT PSEVENTDEFN;
EXPORT PSEVENTROUTE;
EXPORT PSMSGAGTDEFN;
EXPORT PSMAPEXPR;
EXPORT PSMAPFIELD;
EXPORT PSMAPLEVEL;
EXPORT PSMAPRECFIELD;
EXPORT PSMAPROLEBIND;
EXPORT PSMAPROLENAME;

— PROCESS DEFINITIONS
EXPORT PRCSDEFN;
EXPORT PRCSDEFNLANG;
EXPORT PRCSDEFNGRP;
EXPORT PRCSDEFNPNL;
EXPORT PRCSDEFNXFER;
EXPORT PRCSDEFNNOTIFY;
EXPORT PRCSDEFNCNTDIST;
EXPORT PRCSDEFNMESSAGE;
EXPORT PRCSDEFNMETA;
EXPORT PS_PRCSDEFNURL;
EXPORT PS_PRCSDEFNURL_LNG;
EXPORT PS_PRCSDEFNURLKEY;

— PROCESS SERVERS
EXPORT PS_SERVERCLASS;
EXPORT PS_SERVERDEFN;
EXPORT PS_SERVERDEFN_LNG;
EXPORT PS_SERVERNOTIFY;
EXPORT PS_SERVERMESSAGE;
EXPORT PS_SERVEROPRTN;
EXPORT PS_SERVERCATEGORY;

— PROCESS TYPES
EXPORT PS_PRCSTYPEDEFN;
EXPORT PS_PRCSTYPEDEFNLNG;
EXPORT PS_PRCSTYPEMETA;

— PROCESS JOBS
EXPORT PRCSJOBDEFN;
EXPORT PRCSJOBDEFNLANG;
EXPORT PRCSJOBGRP;
EXPORT PRCSJOBITEM;
EXPORT PRCSJOBPNL;
EXPORT PS_PRCSJOBNOTIFY;
EXPORT PS_PRCSJOBCNTDIST;
EXPORT PS_PRCSJOBMESSAGE;
EXPORT PS_PRCSSEQUENCE;
EXPORT PS_PRCSOUTPUTLIST;
EXPORT PS_PRCSOUTDESTTYPE;
EXPORT PS_PRCSSYSTEM;
EXPORT PS_PRCSPURGELIST;
EXPORT PS_PRCS_CAT_TBL;
EXPORT PS_PRCS_CAT_LG_TBL;

— PROCESS RECURRENCE
EXPORT PRCSRECUR;
EXPORT PRCSRECURLANG;
EXPORT PRCSRECURDATE;
EXPORT PRCSRECUREXEMPT;

— PROCESS CONTROL
EXPORT PS_SCHDLDEFN;
EXPORT PS_SCHDLDEFNLANG;
EXPORT PS_SCHDLITEM;
EXPORT PS_SCHDLMESSAGE;
EXPORT PS_SCHDLNODEPARM;
EXPORT PS_SCHDLNOTIFY;
EXPORT PS_SCHDLRPTDIST;
EXPORT PS_SCHDLTEXT;

— DIMENSIONS
EXPORT DIMENSION;
EXPORT DIMENSION_LANG;
EXPORT DIM_CTRL_TBL;
EXPORT DIM_DATA_SRC;
EXPORT DIM_INPUT_FLD;
EXPORT DIM_ROLLUP;
EXPORT DIM_ROLLUP_LVL;

— ANALYSIS MODELS
EXPORT ANALYSIS_MODEL;
EXPORT ANL_MOD_DAT_SRC;
EXPORT ANL_MOD_DIM;
EXPORT ANL_MOD_DIM_FLD;

— CUBE TEMPLATES
EXPORT ANALYSIS_DB;
EXPORT ANALYSIS_DB_DIM;
EXPORT ANL_DB_LANG;
EXPORT ANL_DB_QRY_ESS;
EXPORT CUBE_AGG_DEF;
EXPORT CUBE_AGG_DIM;
EXPORT CUBE_FILTER_ITM;

— BUSINESS INTERLINKS
EXPORT PSIODEFN;
EXPORT PSIODEFNLANG;
EXPORT PSIOINPUTS;
EXPORT PSIOOUTPUTS;
EXPORT PSIOSETTINGS;

— SQL
EXPORT PSSQLDEFN;
EXPORT PSSQLDESCR;
EXPORT PSSQLLANG;
EXPORT PSSQLTEXTDEFN;

— FILE LAYOUT DEFINITIONS
EXPORT PSFLDDEFN;
EXPORT PSFLDFIELDDEFN;
EXPORT PSFLDSEGDEFN;

— BUSINESS COMPONENTS
EXPORT PSBCDEFN;
EXPORT PSBCDEFNLANG;
EXPORT PSBCITEM;
EXPORT PSBCITEMLANG;

— APP ENGINE PROGRAMS
EXPORT PSAEAPPLDEFN;
EXPORT PSAEAPPLLANG;
EXPORT PSAEAPPLSTATE;
EXPORT PSAEAPPLTEMPTBL;

— APP ENGINE SECTIONS
EXPORT PSAESECTDEFN;
EXPORT PSAESECTDTLDEFN;
EXPORT PSAESTEPDEFN;
EXPORT PSAESTEPMSGDEFN;
EXPORT PSAESTMTDEFN;
EXPORT PS_AEINSTANCENBR;
EXPORT PS_AELOCKMGR;

— MESSAGE NODES
EXPORT PSMSGNODEDEFN;
EXPORT PSNODEDEFNLANG;
EXPORT PSCONNECTSTRING;
EXPORT PSNODEURITEXT;
EXPORT PSNODECONPROP;
EXPORT PSNODCONPRPLANG;
EXPORT PSNODEPROP;
EXPORT PSNODEPROPLANG;
EXPORT PSNODESDOWN;
EXPORT PSTRUSTNODES;

— MESSAGE CHANNELS
EXPORT PSCHNLDEFN;
EXPORT PSCHNLDEFNLANG;
EXPORT PSCHNLNODE;
EXPORT PSSUBCHNL;

— MESSAGE DEFINITIONS
EXPORT PSMSGDEFN;
EXPORT PSMSGDEFNLANG;
EXPORT PSMSGFLDOVR;
EXPORT PSMSGREC;
EXPORT PSMSGVER;

— APPROVAL RULE SETS
EXPORT APPR_HDR_LNG;
EXPORT APPR_RULE_AMT;
EXPORT APPR_RULE_DETL;
EXPORT APPR_RULE_FIELD;
EXPORT APPR_RULE_HDR;
EXPORT APPR_RULE_LN;
EXPORT APPR_RULE_QTY;
EXPORT APPR_RULE_ROLE;
EXPORT PSVAITEM;
EXPORT PSVAITEMIMG;
EXPORT PSVAITEMLANG;

— IMAGE AND HTMLCATALOG— CONTENT
EXPORT PSCONTDEFN;
EXPORT PSCONTDEFNLANG;
EXPORT PSCONTENT;
EXPORT PSCONTENTLANG;

— STYLE SHEETS
EXPORT PSSTYLECLASS;
EXPORT PSSTYLECLASSFNT;
EXPORT PSSTYLSHEETDEFN;
EXPORT PSSTYLECLSLANG;
EXPORT PSSTYLEFNTLANG;
EXPORT PSSTYLEDFNLANG;

— LANGUAGES
EXPORT PSLANGUAGES;
EXPORT PS_STRINGS_TBL;
EXPORT PS_STRINGS_LNG_TBL;

— SUBSCRIPTIONS
EXPORT PSSUBDEFN;

— RECORD GROUP
EXPORT PS_REC_GROUP_REC;
EXPORT PS_REC_GROUP_TBL;

— CHANGE CONTROL
EXPORT PSCHGCTLHIST;
EXPORT PSCHGCTLLOCK;

— OBJECT CHANGE (RENAMES)
EXPORT PSOBJCHNG;

— APP MESSAGING
EXPORT PSAPMSGARCHPC;
EXPORT PSAPMSGARCHPD;
EXPORT PSAPMSGARCHPH;
EXPORT PSAPMSGARCHSC;
EXPORT PSAPMSGARCHTMP;
EXPORT PSAPMSGPUBATTR;
EXPORT PSAPMSGPUBCERR;
EXPORT PSAPMSGPUBCERRP;
EXPORT PSAPMSGPUBCLOCK;
EXPORT PSAPMSGPUBCON;
EXPORT PSAPMSGPUBCSYNC;
EXPORT PSAPMSGPUBDATA;
EXPORT PSAPMSGPUBERR;
EXPORT PSAPMSGPUBERRP;
EXPORT PSAPMSGPUBHDR;
EXPORT PSAPMSGPUBINST;
EXPORT PSAPMSGPUBLOCK;
EXPORT PSAPMSGPUBSYNC;
EXPORT PSAPMSGSUBCERR;
EXPORT PSAPMSGSUBCERRP;
EXPORT PSAPMSGSUBCLOCK;
EXPORT PSAPMSGSUBCON;
EXPORT PSAPMSGSUBCSYNC;
EXPORT PSAPMSGSUBPRCID;
EXPORT PSAPMSGXTB;

— URL DEFINITIONS
EXPORT PSURLDEFN;
EXPORT PSURLDEFNLANG;

— PORTAL
EXPORT PSPRSMDEFN;
EXPORT PSPRSMDEFNLANG;
EXPORT PSPRSMATTR;
EXPORT PSPRSMATTRLANG;
EXPORT PSPRSMATTRVAL;
EXPORT PSPRSMATTRVALNG;
EXPORT PSPRSMPERM;
EXPORT PSPRSMSYSATTR;
EXPORT PSPRSMSYSATTRVL;
EXPORT PSPRSMHPASGPGLT;
EXPORT PSPRSMSYNC;
EXPORT PSPRDMDEFN;
EXPORT PSPRDMDEFNLANG;
EXPORT PSPRDMCNTPRV;
EXPORT PSPRUHPERSPGLT;

— RELATIONSHIPS
EXPORT PSRELATCONTACT;
EXPORT PSRELATIONLANG;
EXPORT PSRELATIONPROP;
EXPORT PSRELATIONSHIP;
EXPORT PSRELPROPLANG;

— APPLICATION PACKAGES
EXPORT PSPACKAGEDEFN;
EXPORT PSPACKAGELANG;
EXPORT PSAPPCLASSDEFN;

— ARCHIVE TEMPLATES
EXPORT PS_ARCH_PROJ;
EXPORT PS_ARCH_TBL;
EXPORT PS_ARCH_CTRL;
EXPORT PS_ARCH_OTH_CTRL;
EXPORT PS_ARCH_SQL_LNG;

— ARCHIVE OBJECTS
EXPORT PSARCHOBJDEFN;
EXPORT PSARCHOBJLANG;
EXPORT PSARCHOBJREC;

— ARCHIVE TEMPLATES (TYPE 2)
EXPORT PSARCHTEMPLATE;
EXPORT PSARCHTEMPLANG;
EXPORT PSARCHTEMPOBJ;
EXPORT PSARCHTEMPQRY;
EXPORT PSARCHTEMPAE;

— DIAGNOSTIC PLUGINS
EXPORT PSDIAGREG;

— CONNECT STRINGS
EXPORT PSCONN;
EXPORT PSCONNLANG;
EXPORT PSCONNPROP;
EXPORT PSCONNPROPLANG;

— FORMAT DEFINITIONS
EXPORT PSCIREF;
EXPORT PSCIREFLANG;
EXPORT PSCIREFPROPERTY;
EXPORT PSCIREFENUM;
EXPORT PSCISYNCDEFN;

— EVALUATED NODE TRANSACTION
EXPORT PSNODETRX;
EXPORT PSNODTRXCONPROP;

— EVALUATED RELATIONSHIP TRANSACTION
EXPORT PSRELATIONTRX;

— MOBILE PAGE
EXPORT PSMPDEFN;
EXPORT PSMPDEFNLANG;
EXPORT PSMPDEFNDEV;
EXPORT PSMPDEFNDEVLANG;
EXPORT PSMPCONTDEV;
EXPORT PSMPCONTDEVLANG;
EXPORT PSSYSTEMID;
EXPORT PSAUTHMP;

— DICTIONARY
EXPORT PSSCDICTDEFN;

— MESSAGES
EXPORT PSMSGSETDEFN;
EXPORT PSMSGSETLANG;
EXPORT PSMSGCATDEFN;
EXPORT PSMSGCATLANG;

— PROBLEM DEFINITIONS
EXPORT PSOPTPRBTYPE;
EXPORT PSOPTPRBTYPELNG;
EXPORT PSOPTTRN;
EXPORT PSOPTTRNLNG;
EXPORT PSOPTTRNCTLG;
EXPORT PSOPTREC;
EXPORT PSOPTRECLNG;
EXPORT PSOPTFIELD;

— PORTAL REGISTRY USER FAVORITES
EXPORT PSPRUFDEFN;

— PORTAL USER REGISTRY HOMEPAGE
EXPORT PSPRUHDEFN;
EXPORT PSPRUHTAB;
EXPORT PSPRUHTABPGLT;

— SYSTEM PAUSE TIME
EXPORT PSSPTDEFN;
EXPORT PSSPTIMES;

— TOOLBARS
EXPORT PSTOOLBARDEFN;
EXPORT PSTOOLBARITEM;

— MAINTENANCE LOG
EXPORT PS_MAINTENANCE_LOG;

— HOLIDAY DEFINITIONS
EXPORT PSHOLIDAYDEFN;

— USERS
— Note: PSOPRDEFN exported separately, see top of script
EXPORT PSOPRALIAS;
EXPORT PSROLEUSER;
EXPORT PSUSERATTR;
EXPORT PSUSEREMAIL;
EXPORT PSUSERPRSNLOPTN;
EXPORT PS_ROLEXLATOPR;
EXPORT PS_ROLEXLATOPR_LNG;
EXPORT PS_RTE_CNTL_RUSER;

— ROLES
EXPORT PSROLEDEFN;
EXPORT PSROLEDEFNLANG;
EXPORT PSROLECANGRANT;
EXPORT PSROLECLASS;

— PERMISSION LISTS
EXPORT PSCLASSDEFN;
EXPORT PSAUTHBUSCOMP;
EXPORT PSAUTHCHNLMON;
EXPORT PSAUTHCUBE;
EXPORT PSAUTHITEM;
EXPORT PSAUTHOPTN;
EXPORT PSAUTHPRCS;
EXPORT PSAUTHSIGNON;
EXPORT PSPRCSPRFL;
EXPORT PS_MC_OPR_SECURITY;
EXPORT PS_MC_OPRID;
EXPORT PS_SCRTY_ACC_GRP;
EXPORT PS_SCRTY_QUERY;

— DEFINITION SECURITY
EXPORT PSOBJGROUP;
EXPORT PSOPROBJ;

— PERSONALIZATIONS
EXPORT PSUSEROPTNDEFN;
EXPORT PSUSEROPTNLANG;
EXPORT PSOPTNCATGRPLNG;
EXPORT PSOPTNCATGRPTBL;
EXPORT PSOPTNCATTBL;
EXPORT PSOPTNCATLANG;

— SECURITY OPTIONS
EXPORT PSSECOPTIONS;

— SECURITY LINKS
EXPORT PSUSEROTHER;
EXPORT PSUSERSELFOTHER;
EXPORT PSROLEOTHER;
EXPORT PSPERMLISTOTHER;

— USER ID TYPES
EXPORT PSOPRALIASTYPE;
EXPORT PSOPRALIASFIELD;

— DELETE USER BYPASS TABLE
EXPORT PS_BYPASS_TABLE;

— FORGOT EMAIL TEXT
EXPORT PSPSWDEMAIL;
EXPORT PSPSWDEMAILLANG;

— PASSWORD HINTS
EXPORT PSPSWDHINT;
EXPORT PSPSWDHINT_LANG;

— SIGNON PEOPLECODE
EXPORT PSSIGNONPPC;

— DIRECTORY
EXPORT PSDSDIR;
EXPORT PSDSSRVR;
EXPORT DSCONNECTID;
EXPORT PSDSEXT_INSTALL;
EXPORT PSDSSECMAPMAIN;
EXPORT PSDSSECMAPSRVR;
EXPORT DSUSRPRFLMAP;
EXPORT PSDSUSERPRFL;
EXPORT PSDSSECROLERULE;
EXPORT DSSRCH_SBR;
EXPORT DSSRCHATTR;
EXPORT DSSECFILTER;
EXPORT PT_WF_NOT_DSCFG;

— PEOPLETOOLS SYSTEM DATA
EXPORT PSCERTDB;
EXPORT PSCRYPTALGCHAIN;
EXPORT PSCRYPTALGDEFN;
EXPORT PSCRYPTALGPARAM;
EXPORT PSCRYPTCHNDEFN;
EXPORT PSCRYPTDLLDEFN;
EXPORT PSCRYPTPRFL;
EXPORT PSCRYPTPRFLPRM;
EXPORT PSTIMEZONE;
EXPORT PSTIMEZONELANG;
EXPORT PSDSTTIME;
EXPORT PSKEYDB;
EXPORT PSCHARSETS;
EXPORT PS_OLAP_ATTRIB_NAM;
EXPORT PS_VERTICAL_MARKET;
EXPORT PS_APP_DES_OBJECTS;
EXPORT PS_APP_DES_OBJ_PAR;
EXPORT PSPGEACCESSDESC;
EXPORT PSOBJSECDESC;
EXPORT PSOPTPARMTYPE;
EXPORT PSLOCALEDEFN;
EXPORT PSLOCALELANG;
EXPORT PSLOCALEOPTNDFN;
EXPORT PSLOCALEORDER;
EXPORT PSCOLLATIONS;
EXPORT PS_CDM_FILE_EXT;
EXPORT PS_AEONLINEINST;
EXPORT PS_WL_TEMPLATE_GEN;
EXPORT PS_WL_TEMPL_GEN_TK;
EXPORT PSGATEWAY;
EXPORT PSRF_FLIST_TBL;
EXPORT PSRF_FLIST_LANG;
EXPORT PSRF_FVIEW_TBL;
EXPORT PSRF_FVIEW_LANG;
EXPORT PS_DECIMAL_POS_TBL;
EXPORT PS_WL_TEMPL_GNTKLN;
EXPORT PSACTIVEXLIC;
EXPORT PS_APPDES_OBJ_PERM;
EXPORT PSXMLSIGNINST;
EXPORT PS_TS_REC_KEYFLDS;
EXPORT PS_TS_REC_TXTFLDS;
EXPORT PS_TS_RECORDS;
EXPORT PS_MCF_INSTALL;
EXPORT PS_MCFIMNETWORKS;
EXPORT PSMCFRENPERMS;
EXPORT PSMCFSYSTEM;
EXPORT PS_MCFUQTASKCFG;
EXPORT PS_PINGOPTIONS;
EXPORT PSVERITYLOCALE;
EXPORT PS_PTP_TABLE1;
EXPORT PS_PTP_TABLE2;
EXPORT PS_PT_CTI_SWITCH;
EXPORT PS_WF_INSTSTATUS;
EXPORT PSIBWSDLADMIN;
EXPORT PSRENNAMETOPIC;
EXPORT PSTRANSFRM_TST;
EXPORT PSSHADOWSEQ;
EXPORT PSPTUPGDSLOG;

— WEB Data
EXPORT PSWEBPROFBROW;
EXPORT PSWEBPROFCOOK;
EXPORT PSWEBPROFDEF;
EXPORT PSWEBPROFILE;
EXPORT PSWEBPROFPROP;
EXPORT PSWEBPROFNVP;

— XML Service Info
EXPORT PS_XMLSERVICEINFO;

— EDI OBJECTS
EXPORT PS_ECACTIONCDS;
EXPORT PS_ECACTIONS;
EXPORT PS_ECCVTPROFILE;
EXPORT PS_ECENTITYCDS;
EXPORT PS_ECEXTPARTNER;
EXPORT PS_ECEXTTPLINK;
EXPORT PS_ECGENERAL;
EXPORT PS_ECINMAPFILE;
EXPORT PS_ECINMAPFLD;
EXPORT PS_ECINMAPFLDCVT;
EXPORT PS_ECINMAPREC;
EXPORT PS_ECINMAPRECFLD;
EXPORT PS_ECINTLINK;
EXPORT PS_ECINTPARTNER;
EXPORT PS_ECMAPDEFN;
EXPORT PS_ECMAPPROFILE;
EXPORT PS_ECNAMES;
EXPORT PS_ECOUTMAPCVT;
EXPORT PS_ECOUTMAPFLD;
EXPORT PS_ECOUTMAPREC;
EXPORT PS_ECOUTMAPWHERE;
EXPORT PS_ECPRIEVENTCDS;
EXPORT PS_ECPRODFLTS;
EXPORT PS_ECPROMAP;
EXPORT PS_ECPROTRANS;
EXPORT PS_ECSECEVENTCDS;
EXPORT PS_ECTPALIAS;
EXPORT PS_ECTPCVT;
EXPORT PS_ECTPCVT_HDR;
EXPORT PS_ECTPCVT_LN;
EXPORT PS_ECTPPROFILE;
EXPORT PS_ECTRANS;
EXPORT PS_ECTRANSOPTION;
EXPORT PS_ECTRANSOPVAL;

— SDK Data
EXPORT PS_SDK_AMORT_PREF;
EXPORT PS_SDK_BUS_EXP_DTL;
EXPORT PS_SDK_BUS_EXP_PER;
EXPORT PS_SDK_COMPANY_TBL;
EXPORT PS_SDK_COUNTRY_TBL;
EXPORT PS_SDK_CURR_CD_TBL;
EXPORT PS_SDK_DEPT_TBL;
EXPORT PS_SDK_EM_MAILLST;
EXPORT PS_SDK_EM_RCVMSGS;
EXPORT PS_SDK_EM_SNDEMAIL;
EXPORT PS_SDK_FILEUTL_AET;
EXPORT PS_SDK_INSTALL;
EXPORT PS_SDK_INTL_FLG_CD;
EXPORT PS_SDK_JOB;
EXPORT PS_SDK_LOCH_TBL;
EXPORT PS_SDK_PERS_DATA;
EXPORT PS_SDK_POS_DATA;
EXPORT PS_SDK_PSTREENODE;
EXPORT PS_SDK_RP_INPUT;
EXPORT PS_SDK_RP_PO;
EXPORT PS_SDK_RP_POLINE;
EXPORT PS_SDK_RP_QUERYWRK;
EXPORT PS_SDK_RP_RESULT;
EXPORT PS_SDK_RP_SALORDER;
EXPORT PS_SDK_RP_SITE;
EXPORT PS_SDK_RP_SOQRY1;
EXPORT PS_SDK_RP_SOQRY2;
EXPORT PS_SDK_RT_TYPE_TBL;
EXPORT PS_SDK_RUNCNTL;
EXPORT PS_SDK_SCRTY_DEPT;
EXPORT PS_SDK_SM;
EXPORT PS_SDK_SM_CONCATE;
EXPORT PS_SDK_SRCHSPREF;
EXPORT PS_SDK_SRCH_SAVE;
EXPORT PS_SDK_STOCK;
EXPORT PS_SDK_STOCKXCHG;
EXPORT PS_SDK_STOCK_PRF;
EXPORT PS_SDK_UPS_COST;
EXPORT PS_SDK_UPS_CST_RES;
EXPORT PS_SDK_UPS_TIME;

— Performance Monitor Data
EXPORT PSPMCONTEXTDEFN;
EXPORT PSPMEVENTDEFN;
EXPORT PSPMEVENTSET;
EXPORT PSPMMETRICDEFN;
EXPORT PSPMMETRICVALUE;
EXPORT PSPMMONITORGBL;
EXPORT PSPMSYSDEFAULTS;
EXPORT PSPMTABLEMAP;
EXPORT PSPMTRANSDEFN;
EXPORT PSPMTRANSSET;
EXPORT PSPMTRANSSET_LN;

— Mathematical Model
EXPORT PSANALYTICREG;
EXPORT PSOPTCONARRAY;
EXPORT PSOPTCONSEXP;
EXPORT PSOPTINDEX;
EXPORT PSOPTINDEXSET;
EXPORT PSOPTLINEXP;
EXPORT PSOPTMODEL;
EXPORT PSOPTMODELLNG;
EXPORT PSOPTMDLSLVR;
EXPORT PSOPTOBJECTIVE;
EXPORT PSOPTPARARRAY;
EXPORT PSOPTPARAEXPDIM;
EXPORT PSOPTPARSDIM;
EXPORT PSOPTPRED;
EXPORT PSOPTPREDDIM;
EXPORT PSOPTPTMODEL;
EXPORT PSOPTREGION;
EXPORT PSOPTREGIONDIM;
EXPORT PSOPTSETDIM;
EXPORT PSOPTSLICEDIM;
EXPORT PSOPTSLICESET;
EXPORT PSOPTSOLVERPARA;
EXPORT PSOPTVAREXPDIM;
EXPORT PSOPTVARARRAY;
EXPORT PSOPTVARSDIM;

— File Reference
EXPORT PSFILEREDEFN;

— File Reference Type code
EXPORT PSTYPECODEDEFN;

— ACE
EXPORT PSACECUBE;
EXPORT PSACECUBECOLL;
EXPORT PSACECUBECOLMAP;
EXPORT PSACECUBEDIMS;
EXPORT PSACEDIM;
EXPORT PSACEEXPDIMS;
EXPORT PSACEEXPSET;
EXPORT PSACEMDLDEFN;
EXPORT PSACEMDLDEFNLNG;
EXPORT PSACEORGANIZER;
EXPORT PSACEORGMAP;
EXPORT PSACERULE;
EXPORT PSACETREE;
EXPORT PSACEUSRFUNC;
EXPORT PSPNLACEGRDAXIS;
EXPORT PSPNLACEGRDDATA;

— Web Services for Remote Portlets
EXPORT PSPRSMWSRPCONS;
EXPORT PSPRSMWSRPPROD;
EXPORT PSWSRPCPHDEFN;
EXPORT PSWSRPPLTDEFN;
EXPORT PSWSRPPLTGRPID;
EXPORT PSWSRPPLTKW;
EXPORT PSWSRPPLTKWLANG;
EXPORT PSWSRPPLTLANG;
EXPORT PSWSRPPRDDEFN;
EXPORT PSWSRPPRDLANG;
EXPORT PSWSRPPRDPROP;

— Java Portlet User Preferences
EXPORT PSJPUPDEFN;
EXPORT PSJPUPPREF;
EXPORT PSJPUPPREFVAL;

Posted by Kalyan at 2:45 AM No comments:
Friday, September 24, 2010
Roles, Permissions, and Users Assigned to Portal Content Reference or Component

Roles attached to a component (portal_uri_seg2) or content reference (portal_objname)

SELECT DISTINCT c.portal_name, c.portal_objname, b.rolename, e.descr
FROM psroleclass b, psprsmperm c, psprsmdefn d, psroledefn e
WHERE c.portal_objname = d.portal_objname
AND b.classid = c.portal_permname
AND b.rolename = e.rolename
AND (d.portal_uri_seg2 = :1 OR d.portal_objname = :2);

Permissions attached to a component (portal_uri_seg2) or content reference (portal_objname)

SELECT DISTINCT d.portal_name, d.portal_objname, c.portal_permname, e.classdefndesc
FROM psprsmperm c, psprsmdefn d, psclassdefn e
WHERE d.portal_objname = c.portal_objname
AND d.portal_name = c.portal_name
AND c.portal_permname = e.classid
AND (d.portal_uri_seg2 = :1 OR d.portal_objname = :2);

Users that can access a component (portal_uri_seg2) or content reference (portal_objname)

SELECT DISTINCT c.portal_name, c.portal_objname, a.roleuser, e.oprdefndesc
FROM psroleuser a,
psroleclass b,
psprsmperm c,
psprsmdefn d,
psoprdefn e
WHERE c.portal_objname = d.portal_objname
AND a.rolename = b.rolename
AND b.classid = c.portal_permname
AND a.roleuser = e.oprid
AND (d.portal_uri_seg2 = :1 OR d.portal_objname = :2);

Posted by Kalyan at 7:53 PM No comments:
People soft meta tables
PSPROJECTDEFN table stores information about projects created in Application Designer.
Try it out:

SELECT * FROM PSPROJECTDEFN
WHERE PROJECTNAME = 'Your_Project_name';

PSPROJECTITEM table stores objects inserted into your Application Designer project.
Try it out:

SELECT * FROM PSPROJECTITEM
WHERE PROJECTNAME = 'Your_Project_name';

Portal Structure

PSPRSMDEFN is a Portal Structure Definition table. A good example is to use this table to find portal path for a specific component.

PSPRSMPERM: Shows the permission lists that are assigned to a portal registry structure (content reference). The permission list name is under field PORTAL_PERMNAME.

XLAT Tables

XLATTABLE: Stores translate values (PeopleSoft version prior to 8.4).
PSXLATDEFN: Stores all fields that have Xlat values. This table does not store any Xlat values.
PSXLATITEM: Stores fields with their actual translate values (PeopleSoft version 8.4 and above).

Record & Field Tables

PSRECDEFN: Stores informations about tables. One row for each table. Field count and record type are two fields that are stored on this table.

CASE RECTYPE
WHEN 0 THEN 'Table'
WHEN 1 THEN 'View'
WHEN 2 THEN 'Derived'
WHEN 3 THEN 'Sub Record'
WHEN 5 THEN 'Dynamic View'
WHEN 6 THEN 'Query View'
WHEN 7 THEN 'Temporary Table'
ELSE TO_CHAR(RECTYPE)
END CASE

PSRECFIELD: Stores records with all their fields (sub-records are not expanded)

PSRECFIELDALL: Stores records with all their fields (sub-records are expanded)

PSINDEXDEFN: Contains 1 row per index defined for a table.

PSKEYDEFN: Containes 1 row per key field defined for an index.

PSDBFIELD: You got it, stores information about fields.

CASE FIELDTYPE
WHEN 0 THEN 'Character'
WHEN 1 THEN 'Long Character'
WHEN 2 THEN 'Number'
WHEN 3 THEN 'Signed Number'
WHEN 4 THEN 'Date'
WHEN 5 THEN 'Time'
WHEN 6 THEN 'DateTime'
WHEN 8 THEN 'Image'
WHEN 9 THEN 'Image Reference'
ELSE TO_CHAR(FIELDTYPE)
END CASE

PSDBFLDLABL: Stores field label information.

Process Definition Table(s)

PS_PRCSDEFNPNL: Stores the process definition name, process type(sqr report, application engine…), and the component name associated with the process definition.

PS_PRCSDEFN: Process definitions table. The record stores processes that can run within the Process Scheduler. Security information such as components and process groups are also stored on this table.

Message Catalog Tables

PS_MESSAGE_CATALOG: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.

MESSAGE_SET_TBL: Message set description table.

— Example
SELECT * FROM PS_MESSAGE_CATALOG
WHERE LAST_UPDATE_DTTM > TO_DATE('03-DEC-07', 'DD-MON-YY')
AND LAST_UPDATE_DTTM < TO_DATE('05-DEC-07', 'DD-MON-YY')
ORDER BY MESSAGE_SET_NBR, MESSAGE_NBR;
— This will return messages that has been last update/added between 2 specific dates.

Menu Tables

PSMENUDEFN: Store Menu related information. No related component info on this table.

PSMENUITEM: List the menu with all components attached to it.

Component Tables

PSPNLGRPDEFN: Stores component related information only.

PSPNLGROUP: This table will give you information regarding a specific component along with the names of pages attached to it.

Pages

PSPNLDEFN: Stores pages definitions.

PSPNLFIELD: Stores all items used by each page definition.

Security

PSPRSMPERM: Portal Structure Permissions.

PSAUTHITEM: Page Permissions. This table stores the information about the page level access for a permission list.

PSROLECLASS: Role Classes table. A many to many relationship table between Roles and Permission Lists.

PSROLEDEFN: This table stores information about Peoplesoft Role definitions. Users get permissions to PeopleSoft objects through Roles, which are assigned Permission Lists.

PSCLASSDEFN: Permissions List definitions table. Permission list name can be found under Field Name CLASSID.

PSOPRDEFN: Users/Operator definition table. This table stores information about PeopleSoft users. This is the core table for User Profile Manager.

Here is an example query post that uses all of the above security tables!

URL Definitions

PSURLDEFN: Stores URL definitions. Here is the path to create URL definitions in PeopleSoft Root » PeopleTools » Utilities » Administration » URLs

Application Classes

PSAPPCLASSDEFN: Application Class Definitions table. You can use field PACKAGEROOT to search for a specific Application Package.

PeopleSoft Query Tables

PSQRYDEFN: Stores query related info.

PSQRYFIELD: Stores all fields used in a query (both the fields in the Select and Where clause).

PSQRYCRITERIA: Stores criteria query fields. You can get the name of the fields by joining the PSQRYFIELD table.

PSQRYEXPR: Stores query expressions.

PSQRYBIND: Stores query bind variables.

PSQRYRECORD: Stores all records used in all aspects of query creation

PSQRYSELECT: Stores all SELECT requirements by select type. Example would be sub select, join, ect.

PSQRYLINK: Stores the relationships to child queries.

PSQRYEXECLOG: Query run time log table that stores (only 8.4x and higher)

PSQRYSTATS: Query run time statistics table such as count of query execution, and date time of last execution (only in 8.4x and higher).

SQL Objects

PSSQLDEFN: Stores SQL object definitions.

PSSQLDESCR: Stores SQL objects descriptions, and description long.

PSSQLTEXTDEFN: Stores actual SQL text. You can filter by SQLTYPE field to get SQL objects of interest such as Views SQLs and Application Engine SQLs.

— When SQL type is:
0 = Stand alone SQL objects
1 = Application engine SQL
2 = Views SQLs

Application Engines

PSAEAPPLDEFN: Table that stores Application Engine program definitions.

PSAEAPPLSTATE: Stores application engine STATE records and a flag to indicate if the record is the default STATE record.

PSAESECTDEFN: Application engine section information and also stores last user id to update a specific section.

PSAESECTDTLDEFN: AE section along with descriptions and wither the section is active or not.

PSAEAPPLTEMPTBL: If your application engine uses Temp tables it will show on this record.

PSAESTEPDEFN: Steps in application engines are stored in this table.

PSAESTMTDEFN: Stores your application engine actions and along with their types, such as "Do Select" and so on.

PSAESTEPMSGDEFN: Application engine message action definition table.

PeopleCode Tables

PSPCMNAME: PeopleCode Reference table.

PSPCMPROG: Store actual PeopleCode programs (actual code behind PeopleCode events).

Other Useful Tables

PSSTATUS: Stores PeopleSoft information such as PS Tools release version and the UNICODE_ENABLED boolean flag where a value of 1 indicates the DB is to be treated by Tools as a UNICODE DB.

PSCHGCTLLOCK: Description as explained by PeopleSoft "This table contains a a row for every object that is currently locked by any user. When the user requests to lock an object in the Application Designer, first this table is searched to see if the object is locked by another user. If it is not found, a row is inserted into the table. When the user requests to unlock an object, the row in this table is deleted."

PSCHGCTLHIST - shows history of locked definitions with project name, incident, and description

HTML Definitions
PSCONTDEFN - header record; last update time, etc.
PSCONTENT - stores actual text in the HTML definition
File Layout Definitions
PSFLDDEFN - header record; last update time, etc.
PSFLDSEGDEFN - stores the segments for each layout
PSFLDFIELDDEFN - stores the fields for each layout
Workflow
APPR_RULE_DETL - Approval Rule Defn Details
APPR_RULE_FIELD - Approval Rule Defn Route Cntl
APPR_RULE_AMT - Approval Rule Amounts
RTE_CNTL_LN - Route Control Profile Line
RTE_CNTL_RUSER - RoleUser Route Cntl Profiles
RTE_CNTL_TYPE - Route Control Type
RTE_CNTL_HDR - Routing Control Type

PSMAPFIELD: Stores Field mapping of Activity

APP Messaging Tables :

PSCAMA - PeopleSoft Common Application Message Attributes Table

PSMSGDEFN - Message Definitions Table

PSMSGDEFNLANG - Language Table for Message Definitions

PSMSGNODEDEFN Node Definition Table

PSNODEDEFNLANG Language table for Node Definitions

PSCHNLDEFN Message Channel Details Table

PSCHNLDEFNLANG Language Record for Channel Definition Table

How to Find Permission List for a page/Component/User ID in PeopleSoft by SQL

Query to find permission list of each user ID

SELECT a.ROLEUSER, a.ROLENAME, a.DYNAMIC_SW, b.CLASSID
, MENUNAME, BARNAME, BARITEMNAME, PNLITEMNAME, DISPLAYONLY, AUTHORIZEDACTIONS
FROM PSROLEUSER a , PSROLECLASS b , PSAUTHITEM c
WHERE a.ROLEUSER=’1111′ AND
a.ROLENAME = b.ROLENAME
AND b.CLASSID = c.CLASSID

ORDER BY ROLENAME , CLASSID , MENUNAME

SQL for find permission list of page of PeopleSoft

SELECT a.ROLEUSER, a.ROLENAME, a.DYNAMIC_SW, b.CLASSID
, MENUNAME, BARNAME, BARITEMNAME, PNLITEMNAME, DISPLAYONLY, AUTHORIZEDACTIONS
FROM PSROLEUSER a , PSROLECLASS b , PSAUTHITEM c
WHERE a.ROLENAME = b.ROLENAME
AND b.CLASSID = c.CLASSID AND PNLITEMNAME=’JPM_CAT_TYPES’

ORDER BY ROLENAME , CLASSID , MENUNAME

SQL to find permission list of a component of PeopleSoft

SELECT a.ROLEUSER, a.ROLENAME, a.DYNAMIC_SW, b.CLASSID
, MENUNAME, BARNAME, BARITEMNAME, PNLITEMNAME, DISPLAYONLY, AUTHORIZEDACTIONS
FROM PSROLEUSER a , PSROLECLASS b , PSAUTHITEM c
WHERE a.ROLENAME = b.ROLENAME
AND b.CLASSID = c.CLASSID AND BARITEMNAME=’JPM_CAT_TYPES’

ORDER BY ROLENAME , CLASSID , MENUNAME

The SQL below will get you the permission list names:
SELECT DISTINCT AI.CLASSID
FROM PSAUTHITEM AI, PSCLASSDEFN CD, PSPNLGROUP PG, PSMENUITEM MI
WHERE AI.MENUNAME = MI.MENUNAME AND
AI.BARNAME = MI.BARNAME AND
AI.BARITEMNAME = MI.ITEMNAME AND
AI.PNLITEMNAME = PG.ITEMNAME AND
PG.PNLGRPNAME ||'.' || PG.MARKET = '' AND
MI.PNLGRPNAME = PG.PNLGRPNAME AND
MI.MARKET = PG.MARKET AND
CD.CLASSID = AI.CLASSID AND
PG.PNLNAME = '' ORDER BY AI.CLASSID

The following query will return the roles and permission lists that a particular user belongs to.

select

    RU.ROLEUSER as OPRID,

    RU.ROLENAME as ROLE,

    RC.CLASSID as PERMISSION_LIST

from

    PSROLEUSER RU inner join PSROLECLASS RC

    on RU.ROLENAME = RC.ROLENAME

where

    RU.ROLEUSER = 'OPRID'

order by ROLE, PERMISSION_LIST

To get the component and page name we need to join PSMENUITEM (Stores Items (components) on the menu) and PSPNLGROUP ( Stores Pages in the components) tables.

SELECT o.oprid as userid, r.ROLENAME as rolename, c.CLASSID as permission_list, a.MENUNAME as menuname, p.PNLGRPNAME as component_name, pg.PNLNAME as page_name from PSOPRDEFN o
inner join PSROLEUSER r on o.OPRID = r.ROLEUSER inner join PSROLECLASS c on r.ROLENAME = c.ROLENAME inner join PSAUTHITEM a on c.CLASSID = a.CLASSID inner join PSMENUITEM m on a.MENUNAME = m.MENUNAME and a.BARNAME = m.BARNAME and a.BARITEMNAME = m.ITEMNAME inner join PSPNLGROUP p on p.PNLGRPNAME = m.PNLGRPNAME and AND p.ITEMNAME = a.PNLITEMNAME where o.OPRID =:1

SELECT o.oprid as userid, r.ROLENAME as rolename, c.CLASSID as permission_list, a.MENUNAME as menuname, p.PNLGRPNAME as component_name, pg.PNLNAME as page_name,a.DISPLAYONLY, pd.PAGEACCESSDESCR from PSOPRDEFN o
inner join PSROLEUSER r on o.OPRID = r.ROLEUSER inner join PSROLECLASS c on r.ROLENAME = c.ROLENAME inner join PSAUTHITEM a on c.CLASSID = a.CLASSID inner join PSMENUITEM m on a.MENUNAME = m.MENUNAME and a.BARNAME = m.BARNAME and a.BARITEMNAME = m.ITEMNAME inner join PSPNLGROUP p on p.PNLGRPNAME = m.PNLGRPNAME and AND p.ITEMNAME = a.PNLITEMNAME inner join PSPGEACCESSDESC pd on a.AUTHORIZEDACTIONS = pd.AUTHORIZEDACTIONS where o.OPRID =:1

List of queries that a user has access to:

SELECT DISTINCT A.QRYNAME
	FROM PSQRYRECORD A, PSTREENODE B
	WHERE A.RECNAME = B.TREE_NODE
	AND B.TREE_NAME IN (--Get Tree Name from all the user's permission lists
	SELECT DISTINCT TREE_NAME
	FROM PS_SCRTY_ACC_GRP
	WHERE CLASSID IN --Get Permission List for the user
	(SELECT OPRCLASS
	FROM PSOPRCLS
	WHERE OPRID = 'ENTER_USER_ID_HERE')) --Replace with User ID here
	;

SQL to identify which user, role and permission-list access to a particular component interface

SELECT DISTINCT R.ROLEUSER AS USER_IDS, C.ROLENAME as ROLE, P.CLASSID AS PERMISSION_LIST
FROM PSROLEUSER R, PSROLECLASS C, PSAUTHBUSCOMP P
WHERE R.ROLENAME = C.ROLENAME
AND P.CLASSID = C.CLASSID
AND P.BCNAME = 'JOB_DATA_CI'
ORDER BY 1,2,3;

1. Component Permission List Query:
This query identify the permission lists and its description associated with component.

SELECT   menu.menuname, compdfn.pnlgrpname, auth.classid permission_list,

CLASS.classdefndesc permission_desc

FROM psauthitem auth,

psmenudefn menu,

psmenuitem menuitm,

pspnlgroup comp,

pspnlgrpdefn compdfn,

psclassdefn CLASS

WHERE menu.menuname = menuitm.menuname

AND menuitm.pnlgrpname = comp.pnlgrpname

AND compdfn.pnlgrpname = comp.pnlgrpname

AND compdfn.pnlgrpname LIKE UPPER (:component_name)

AND auth.menuname = menu.menuname

AND auth.barname = menuitm.barname

AND auth.baritemname = menuitm.itemname

AND auth.pnlitemname = comp.itemname

AND auth.classid = CLASS.classid

GROUP BY menu.menuname, compdfn.pnlgrpname, auth.classid, CLASS.classdefndesc

ORDER BY menu.menuname, compdfn.pnlgrpname, permission_list;

Find Roles and Permission lists that grant access to a page:

SELECT RLCLS.ROLENAME,
	RLCLS.CLASSID,
	AUTH.MENUNAME,
	AUTH.BARITEMNAME,
	AUTH.PNLITEMNAME,
	CASE AUTH.DISPLAYONLY
	WHEN 0 THEN 'UPDATE_ACCESS'
	WHEN 1 THEN 'DISPLAY ONLY ACCESS'
	END
	ACCESS_TYPE,
	AUTH.DISPLAYONLY
	FROM PSROLECLASS RLCLS,
	PSAUTHITEM AUTH,
	PSMENUITEM MNU,
	PSPNLGROUP PNLGRP
	WHERE RLCLS.CLASSID = AUTH.CLASSID
	AND AUTH.MENUNAME = MNU.MENUNAME
	AND AUTH.BARITEMNAME = MNU.ITEMNAME
	AND AUTH.PNLITEMNAME = PNLGRP.ITEMNAME
	AND MNU.PNLGRPNAME = PNLGRP.PNLGRPNAME
	-- AND AUTH.DISPLAYONLY = 0 -- Update Access, 1 - means display only
	AND RLCLS.ROLENAME IN (SELECT DISTINCT ROLENAME
	FROM PSROLEUSER) --Check if role is assigned to any user
	AND AUTH.MENUNAME = 'WORKFLOW_ADMINISTRATOR' --MENUNAME HERE
	AND MNU.PNLGRPNAME = 'WF_MONITOR_UPDATE' -- COMPONENT NAME HERE
	AND PNLGRP.PNLNAME = 'WF_MONITOR_UPDATE' --PAGE NAME HERE

2. Content Reference accessed by a permission list:

This query identifies Content references accessed by Permission List.

SELECT   a.portal_label AS PORTAL_LINK_NAME, a.portal_objname, a.portal_name, a.portal_reftype

FROM psprsmdefn a, psprsmperm b, psclassdefn c

WHERE a.portal_reftype = 'C'

AND a.portal_cref_usgt = 'TARG'

AND a.portal_name = b.portal_name

AND a.portal_reftype = b.portal_reftype

AND a.portal_objname = b.portal_objname

AND c.classid = b.portal_permname

AND a.portal_uri_seg1 <> ' '

AND a.portal_uri_seg2 <> ' '

AND a.portal_uri_seg3 <> ' '

AND c.classid = :permissionlist

AND a.portal_name = :portalname

ORDER BY portal_label;

FRMT = Frame Template

HPGC = Pagelet

HPGT = Homepage Tab

HTMT = HTML template

LINK = Content Reference Link

3. Page Access By Permission List:

SELECT b.menuname, b.barname, b.baritemname, b.pnlitemname AS pagename,
       c.pageaccessdescr,
       DECODE (b.displayonly, 0, 'No', 1, 'Yes') AS displayonly
FROM psclassdefn a, psauthitem b, pspgeaccessdesc c
WHERE a.classid = b.classid
   AND a.classid = :1
   AND b.baritemname > ' '
   AND b.authorizedactions = c.authorizedactions;

4. PeopleTools Accessed By a Permission List:

SELECT DISTINCT b.menuname

FROM psclassdefn a, psauthitem b

WHERE a.classid = b.classid

AND (   b.menuname = 'CLIENTPROCESS'

OR b.menuname = 'DATA_MOVER'

OR b.menuname = 'IMPORT_MANAGER'

OR b.menuname = 'APPLICATION_DESIGNER'

OR b.menuname = 'OBJECT_SECURITY'

OR b.menuname = 'QUERY'

AND a.classid = :PermissionList;

5. Roles Assigned to a Permission List:

SELECT b.rolename, b.classid AS permission_list

FROM psclassdefn a, psroleclass b

WHERE a.classid = b.classid AND a.classid = :permissionlist;

6. User IDs assigned to a Permission List:

SELECT c.roleuser AS USER_IDs

FROM psclassdefn a, psroleclass b, psroleuser c

WHERE a.classid = b.classid

AND b.rolename = c.rolename

AND a.classid = :permissionlist

GROUP BY c.roleuser;

PeopleSoft Tips & Tricks

Useful SQL Queries in PeopleSoft

SQL Query to find Records and Fields used in a PeopleSoft Page

Find long running SQL queries

No comments:

Post a Comment

Excel to Component Interface Utility

Report Abuse