Some Nice to have SQL Scripts in PeopleSoft - Part III

13)      SQL Query to find out the records exposed by a Component Interface

SELECT distinct RECNAME
FROM PSBCITEM
WHERE BCNAME = 'XXXX_CI';

 

14)       SQL Query to find search records in a Component Interface

SELECT SEARCHRECNAME, ADDSRCHRECNAME
FROM PSBCDEFN
WHERE BCNAME = 'XXXX_CI';

 

15)       SQL to find All Records under a specified component

SELECT DISTINCT RECNAME FROM PSRECDEFN
WHERE ( RECNAME IN (SELECT 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 = 'JOB_DATA'))
OR RECNAME IN (SELECT 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 = 'JOB_DATA'))

 

16)       SQL to find the Navigation of a PeopleSoft Process

       SQL to find the navigation to the run control page by Process Name in PeopleSoft.

SELECT E.PRCSTYPE, E.PRCSNAME, F.DESCR, DECODE( H.PORTAL_LABEL,' ','','','','Root','Root ','Root > ' || H.PORTAL_LABEL) || '> ' || G.PORTAL_LABEL || ' > ' || C.PORTAL_LABEL || ' > ' || B.PORTAL_LABEL || ' > ' || A.PORTAL_LABEL || ' > ' || D.PORTAL_LABEL , D.DESCR254, E.PNLGRPNAME,D.PORTAL_NAME,D.PORTAL_REFTYPE,D.PORTAL_OBJNAME,F.PRCSTYPE,F.PRCSNAME
FROM PSPRSMDEFN D, PS_PRCSDEFNPNL E, PS_PRCSDEFN F, PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN G, PSPRSMDEFN H
WHERE E.PNLGRPNAME = D.PORTAL_URI_SEG2
AND E.PRCSTYPE = F.PRCSTYPE
AND E.PRCSNAME = F.PRCSNAME
AND D.PORTAL_NAME = 'EMPLOYEE'
AND D.PORTAL_REFTYPE = 'C'
AND E.PNLGRPNAME > ' '
AND A.PORTAL_NAME = 'EMPLOYEE'
AND A.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME
AND B.PORTAL_NAME = 'EMPLOYEE'
AND B.PORTAL_OBJNAME = A.PORTAL_PRNTOBJNAME
AND C.PORTAL_NAME (+) = 'EMPLOYEE'
AND C.PORTAL_OBJNAME(+) = B.PORTAL_PRNTOBJNAME
AND G.PORTAL_NAME(+) = 'EMPLOYEE'
AND G.PORTAL_OBJNAME(+) = C.PORTAL_PRNTOBJNAME
AND H.PORTAL_NAME(+) = 'EMPLOYEE'
AND H.PORTAL_OBJNAME(+) = G.PORTAL_PRNTOBJNAME
AND E.PRCSNAME ='Process Name'
ORDER BY 1, 2, 4

Some Nice to have SQL Scripts in PeopleSoft - Part II

6)      SQL to get the 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 = 'PTPT1200'
ORDER BY 1,2;

7)      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;

8)      SQL to get the list of query security trees that an oprid has access

SELECT distinct P.TREE_NAME,P.ACCESS_GROUP,P.ACCESSIBLE
FROM PS_SCRTY_ACC_GRP P, PSROLECLASS C, PSROLEUSER R
WHERE R.ROLENAME = C.ROLENAME
AND P.CLASSID = C.CLASSID
AND R.ROLEUSER = 'User ID'
AND P.ACCESSIBLE = 'Y'
ORDER BY 1,2;

9)      SQL to get the Process Scheduler Server status

SELECT S.SERVERNAME, X.XLATSHORTNAME,X.FIELDVALUE,S.MAXCPU,S.PRCSDISKSPACE,S.LASTUPDDTTM
FROM PSSERVERSTAT S, PSXLATITEM X
WHERE X.FIELDNAME = 'SERVERSTATUS' AND X.FIELDVALUE = S.SERVERSTATUS;

 

10)       SQL query to get all the child records for a parent record:

SELECT RECNAME FROM PSRECDEFN
CONNECT BY PRIOR RECNAME=PARENTRECNAME
START WITH PARENTRECNAME = 'PS_JOB';

 

 

11)       SQL to identify which USER, ROLE and PERMISSION LIST has 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 = 'XXXX_CI'

ORDER BY 1,2,3;

 

12)       SQL Query to find out the Component associated with a Component Interface

SELECT BCPGNAME,MARKET,MENUNAME
FROM PSBCDEFN
WHERE BCNAME = 'XXXX_CI';
 

Click here for Part III

 

Some Nice to have SQL Scripts in PeopleSoft - Part I

1)      SQL Query to identify the record's of a component

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 = 'JOB_DATA'
AND E.recname = D.recname
AND E.rectype IN ( 0 )
AND To_char(Bitand(D.fielduse, 16)) <> '16'
ORDER BY 2,1

2)      SQL to find the navigation to a Component

SELECT LEVEL0.PORTAL_LABEL || ' > ' || LEVEL1.PORTAL_LABEL || ' > ' || LEVEL2.PORTAL_LABEL || ' > ' || level3.PORTAL_LABEL PATH_TO_COMPONENT
FROM PSPRSMDEFN level3
, PSPRSMDEFN level2
, PSPRSMDEFN level1
, PSPRSMDEFN LEVEL0
WHERE level3.PORTAL_URI_SEG2 = 'JOB_DATA'
AND level3.PORTAL_PRNTOBJNAME = level2.PORTAL_OBJNAME
AND level2.PORTAL_PRNTOBJNAME = level1.PORTAL_OBJNAME
AND level1.PORTAL_PRNTOBJNAME = LEVEL0.PORTAL_OBJNAME
AND level3.PORTAL_NAME = level2.PORTAL_NAME
AND level2.PORTAL_NAME = level1.PORTAL_NAME
AND level1.PORTAL_NAME = LEVEL0.PORTAL_NAME

3)      SQL to get the list of users connected to the environment in last 30 days, with connections count

 

SELECT TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD'),A.OPRID, sum(1) "Connections"
FROM PSACCESSLOG A
WHERE TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD') >= TO_CHAR(SYSDATE - 30,'YYYY-MM-DD')
GROUP BY TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD'),A.OPRID
ORDER BY TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD') DESC, A.OPRID;

 

4)      SQL to get the Number of users connected to the environment in last 30 days

SELECT TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD'),count(distinct A.OPRID) "Users"
FROM PSACCESSLOG A
WHERE TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD') >= TO_CHAR(SYSDATE - 30,'YYYY-MM-DD')
GROUP BY TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD')
ORDER BY TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD') DESC;

5)      SQL to get the Role's and user id's assigned to a permission list

 

SELECT C.ROLEUSER AS USER_IDS, B.ROLENAME as ROLE
FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C
WHERE A.CLASSID = B.CLASSID AND B.ROLENAME = C.ROLENAME AND A.CLASSID = 'PTPT1200'
ORDER BY 1,2;

Click for Part II

Diffrence between SETID and Business Unit

Business Units

Business Units are logical units created within any organization for
reporting purposes and don't have any predetermined restrictions or
requirements. This flexible structuring device enables you to actually
define a higher level of reporting for your employees and group them
according to functional or administrative tasks or for your business
purposes.

One can define Business Units that reflect the specific functional
needs of your internal human resources departments, or reflect the actual
business structure of your enterprise. Your Business Units may be, for
example, companies, agencies, subsidiaries, divisions, departments, or
branch offices within your organization. Or, you may choose to have a
single Business Unit represent your entire organization. It's up to you and
your unique business needs.

TableSets and SetIDs

Once you've established Business Units, you can define TableSets, or
groups of tables, for your system-wide control tables, so that you can share
the
same code values among multiple Business Units within your enterprise.
Conversely, the TableSet feature also enables you to limit access to
specific data to only those users who need it, while maintaining all of
your data on the same tables. The flexibility to share TableSets among
Business
Units enables you to centralize redundant information while you keep other
information, such as Department and Job Codes, decentralized. You can use
Business Units and TableSets to associate a Business Unit with employees
in your enterprise and to specify how default values for currencies and
country codes will behave throughout the HRMS system, based on either the
user's
Permission List or the Business Unit that the system is referencing during
a particular business process or activity.

Where a Business Unit organizes your company or your organization, SetIDs
help you organize your data within the system. The HRMS system uses tables
(Control Tables or Prompt Tables) that use a high-level key that enables
you to identify and retrieve data from the system. A secondary high-level
key,referred to as a SetID, has also been added on various tables. SetIDs are
simply the labels used to identify a TableSet. Business Unit and SetID
functionality in PeopleSoft also provides you with a higher business
level for reporting purposes and other business data roll-up.

Some more points to Note :

=> All the transaction tables should store the business
unit as a First level Key, so that reports based on business units can
be generated.

=> All the control tables and prompt tables should store setid as the
first level key to share the data