Oracle jgaicc

avril 19, 2011

PeopleSoft » PeopleSoft Useful Scripts

Filed under: Tips & Cdes — Étiquettes : , , , , — jgaicc @ 11:24

Process Scheduler monitoring useful script

Posted on : 20-01-2010 | By : Elisabeta Olteanu | In : PeopleSoft Useful Scripts
Tags: monitoring, process scheduler, PSSERVERSTAT, script
0

SELECT S.SERVERNAME, X.XLATSHORTNAME,X.FIELDVALUE,cast(S.MAXCPU as varchar2(3))||’%’ MAXCPU,cast(S.PRCSDISKSPACE as varchar2(15))||’ MB’ PRCS_DISK_SPACE,TO_CHAR(S.LASTUPDDTTM,’DD-MM-YYYY HH:MI:SS’)LAST_UPDATE_TIME
FROM PSSERVERSTAT S, PSXLATITEM X
WHERE X.FIELDNAME = ‘SERVERSTATUS’
AND X.FIELDVALUE = S.SERVERSTATUS

Read Full Article

Processes running – PSPRCSRQST table

Posted on : 08-12-2009 | By : Elisabeta Olteanu | In : PeopleSoft Useful Scripts
Tags: DISTSTATUS, PSPRCSRQST, RUNSTATUS
0

— Processes running in the environment with run status Processing for PT version<8.40, determined at andSQL SERVER database level

select top 25 PRCSINSTANCE,JOBINSTANCE,PRCSNAME,PRCSJOBNAME,PRCSTYPE,OPRID,XLATLONGNAME RUN_STATUS,DATENAME(DW,BEGINDTTM)as Day,
DATEDIFF(MINUTE,BEGINDTTM, isnull(ENDDTTM,getdate())) as DURATION_MINUTES,RUNDTTM
from  PSPRCSRQST A
INNER JOIN XLATTABLE B  ON B.FIELDNAME = ‘RUNSTATUS’ AND B. LANGUAGE_CD = ‘ENG’ AND
FIELDVALUE =  A.RUNSTATUS
where XLATLONGNAME = ‘Processing’
ORDER BY BEGINDTTM DESC

—Processes running in the environment with run status Processing for PT version>8.40, determined at an ORACLE database level

select A.PRCSINSTANCE , A.JOBINSTANCE, A.PRCSNAME,A.PRCSJOBNAME, A.PRCSTYPE, A.OPRID, B.XLATLONGNAME RUN_STATUS,TO_CHAR(BEGINDTTM,’Day’)as DAY,
round((sysdate – BEGINDTTM)*1440,2) as DURATION_MINUTES , TO_CHAR(A.RUNDTTM,’dd.MON.YYYY HH:MI:SS’) RUNDTTM,P.XLATSHORTNAME DISTRIBUTION_STATUS
from  PSPRCSRQST A
INNER JOIN PSXLATITEM B  ON B.FIELDNAME = ‘RUNSTATUS’ AND
B.FIELDVALUE =  A.RUNSTATUS
INNER JOIN PSXLATITEM P  ON P.FIELDNAME = ‘DISTSTATUS’ AND
P.FIELDVALUE =  A.DISTSTATUS
where B.XLATLONGNAME = ‘Processing’ and rownum<25
ORDER BY BEGINDTTM DESC

Read Full Article

Navigation access in Portal for permission lists/operators in PeopleTools >= 8.40

Posted on : 25-11-2009 | By : Elisabeta Olteanu | In : PeopleSoft Useful Scripts
Tags: Navigation, permission list, Portal Registry, security
0

—Oracle Sql
—The Peoplesoft Navigation menus, PS menus and components a specific user can access
SELECT DISTINCT Dev.PATH MENU_PATH, Dev.portal_objname PORTAL_OBJECT, Dev.PORTAL_URI_SEG2 PS_COMPONENT, Dev.PORTAL_URI_SEG1 PS_MENU, a.CLASSID PERMISSION_LIST,
DECODE(a.DISPLAYONLY,1,’TRUE’,0,’FALSE’) DISPLAYONLY,
(CASE
WHEN a.AUTHORIZEDACTIONS=1 THEN ‘ADD’
WHEN a.AUTHORIZEDACTIONS=2 THEN ‘UPDATE,DISPLAY’
WHEN a.AUTHORIZEDACTIONS=3 THEN ‘ADD,UPDATE, DISPLAY’
WHEN a.AUTHORIZEDACTIONS=4 THEN ‘UPDATE/DISPLAY ONLY’
WHEN a.AUTHORIZEDACTIONS=5 THEN ‘ADD,UPDATE/DISPLAY ALL’
WHEN a.AUTHORIZEDACTIONS=6 THEN ‘UPDATE,DISPLAY,UPDATE/DISPLAY ALL’
WHEN a.AUTHORIZEDACTIONS=7 THEN ‘ADD,UPDATE,DISPLAY,UPDATE/DISPLAY ALL’
WHEN a.AUTHORIZEDACTIONS=8 THEN ‘CORRECTION’
WHEN a.AUTHORIZEDACTIONS=9 THEN ‘ADD,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=10 THEN ‘UPDATE,DISPLAY,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=11 THEN ‘ADD, UPDATE/DISPLAY ALL, CORRECTION’
WHEN a.AUTHORIZEDACTIONS=12 THEN ‘UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=13 THEN ‘ADD, UPDATE, DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=14 THEN ‘UPDATE,DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=15 THEN ‘ADD,UPDATE,DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=128 THEN ‘DATA ENTRY’
ELSE ‘UNKNOWN’
END ) AUTHORIZEDACTIONS
FROM (select LTRIM(SYS_CONNECT_BY_PATH (PORTAL_LABEL, ‘>’),’>’) PATH, portal_objname,PORTAL_URI_SEG1,PORTAL_URI_SEG2 from psprsmdefn where level >1
connect by nocycle prior portal_objname= portal_prntobjname
start with portal_objname= ‘PORTAL_ROOT_OBJECT’ order by level ) Dev
inner join PSMENUITEM b ON Dev.PORTAL_URI_SEG2=b.PNLGRPNAME AND Dev.PORTAL_URI_SEG1 =b.MENUNAME
inner join PSAUTHITEM a ON a.menuname = b.menuname AND a.baritemname = b.itemname
inner join PSOPRCLS p ON a.CLASSID=p.OPRCLASS
where p.OPRID=’YourOPRID’
and rtrim(ltrim(Dev.PORTAL_URI_SEG2)) is NOT NULL and rtrim(ltrim(Dev.PORTAL_URI_SEG1)) is NOT NULL

—The Peoplesoft Navigation menus, PS menus and components a specific permission list gives access to
SELECT DISTINCT Dev.PATH MENU_PATH, Dev.portal_objname PORTAL_OBJECT, Dev.PORTAL_URI_SEG2 PS_COMPONENT, Dev.PORTAL_URI_SEG1 PS_MENU, a.CLASSID PERMISSION_LIST,
DECODE(a.DISPLAYONLY,1,’TRUE’,0,’FALSE’) DISPLAYONLY,
(CASE
WHEN a.AUTHORIZEDACTIONS=1 THEN ‘ADD’
WHEN a.AUTHORIZEDACTIONS=2 THEN ‘UPDATE,DISPLAY’
WHEN a.AUTHORIZEDACTIONS=3 THEN ‘ADD,UPDATE, DISPLAY’
WHEN a.AUTHORIZEDACTIONS=4 THEN ‘UPDATE/DISPLAY ONLY’
WHEN a.AUTHORIZEDACTIONS=5 THEN ‘ADD,UPDATE/DISPLAY ALL’
WHEN a.AUTHORIZEDACTIONS=6 THEN ‘UPDATE,DISPLAY,UPDATE/DISPLAY ALL’
WHEN a.AUTHORIZEDACTIONS=7 THEN ‘ADD,UPDATE,DISPLAY,UPDATE/DISPLAY ALL’
WHEN a.AUTHORIZEDACTIONS=8 THEN ‘CORRECTION’
WHEN a.AUTHORIZEDACTIONS=9 THEN ‘ADD,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=10 THEN ‘UPDATE,DISPLAY,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=11 THEN ‘ADD, UPDATE/DISPLAY ALL, CORRECTION’
WHEN a.AUTHORIZEDACTIONS=12 THEN ‘UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=13 THEN ‘ADD, UPDATE, DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=14 THEN ‘UPDATE,DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=15 THEN ‘ADD,UPDATE,DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=128 THEN ‘DATA ENTRY’
ELSE ‘UNKNOWN’
END ) AUTHORIZEDACTIONS
FROM (select LTRIM(SYS_CONNECT_BY_PATH (PORTAL_LABEL, ‘>’),’>’) PATH, portal_objname,PORTAL_URI_SEG1,PORTAL_URI_SEG2 from psprsmdefn where level >1
connect by nocycle prior portal_objname= portal_prntobjname
start with portal_objname= ‘PORTAL_ROOT_OBJECT’ order by level ) Dev
inner join PSMENUITEM b ON Dev.PORTAL_URI_SEG2=b.PNLGRPNAME AND Dev.PORTAL_URI_SEG1 =b.MENUNAME
inner join PSAUTHITEM a ON a.menuname = b.menuname AND a.baritemname = b.itemname
where a.CLASSID=’YourPermissionList’
and rtrim(ltrim(Dev.PORTAL_URI_SEG2)) is NOT NULL and rtrim(ltrim(Dev.PORTAL_URI_SEG1)) is NOT NULL

Read Full Article

PS_SEC_BU_OPR Script

Posted on : 23-11-2009 | By : Elisabeta Olteanu | In : PeopleSoft Useful Scripts
Tags: business unit security, PeopleTools, PS_SEC_BU_OPR
0

—Oracle Sql
—grant “NewOPRID” user rights to the same BUs as “CloneOPRID” user has access to
INSERT INTO PS_SEC_BU_OPR (OPRID, BUSINESS_UNIT) SELECT ‘NewOPRID’, BUS FROM (SELECT DISTINCT BUSINESS_UNIT BUS FROM PS_SEC_BU_OPR WHERE OPRID = ‘CloneOPRID’)
COMMIT

—grant access to user “YourOPRID” to the rest of the BUs available in the environment
INSERT INTO PS_SEC_BU_OPR (OPRID, BUSINESS_UNIT) (SELECT OPRID,BUSINESS_UNIT FROM (SELECT ‘YourOPRID’ OPRID FROM DUAL), (SELECT BUSINESS_UNIT FROM (select BUSINESS_UNIT FROM PS_BUS_UNIT_TBL_FS) MINUS (SELECT DISTINCT BUSINESS_UNIT BUS FROM PS_SEC_BU_OPR WHERE OPRID = ‘YourOPRID’)))
COMMIT

Read Full Article

PSACCESSLOG scripts

Posted on : 19-11-2009 | By : Elisabeta Olteanu | In : PeopleSoft Useful Scripts
Tags: number of users connected, PeopleTools, PSACCESSLOG
0

—Oracle Sql
—Number of users connected to the environment  per  day /’2009-08-16′

SELECT COUNT(Num) Users_Number  FROM ( SELECT count(A.OPRID) AS Num
FROM PSACCESSLOG A
WHERE TO_CHAR(((A.LOGINDTTM ) + ( 0)),’YYYY-MM-DD’) >=’2009-08-16′
AND TO_CHAR(((A.LOGINDTTM ) + ( -1)),’YYYY-MM-DD’) <’2009-08-17′
GROUP BY OPRID)

—Number of connections  to the environment  per  day / ‘2009-08-16′

SELECT COUNT(A.OPRID) AS Connections_Number
FROM PSACCESSLOG A
WHERE TO_CHAR(((A.LOGINDTTM ) + ( 0)),’YYYY-MM-DD’) >=’2009-08-16′
AND TO_CHAR(((A.LOGINDTTM ) + ( -1)),’YYYY-MM-DD’) <’2009-08-17′

PeopleSoft » PeopleSoft Useful Scripts

Laisser un commentaire »

Aucun commentaire pour l’instant.

RSS feed for comments on this post. TrackBack URI

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

Propulsé par WordPress.com.

%d blogueurs aiment cette page :