SQL to retrieve the difference between DATES

Method 1: SELECT t1_id, EXTRACT(Day FROM(mod_date_time – create_date_time) DAY TO SECOND) as Day, EXTRACT(HOUR FROM(mod_date_time – create_date_time) DAY TO SECOND) as Hour, EXTRACT(Minute FROM(mod_date_time – create_date_time) DAY TO SECOND) as Minute, EXTRACT(SECOND FROM(mod_date_time – create_date_time) DAY TO SECOND) as second FROM t1; Method 2: SELECT floor((date1-date2)*24) || ' HOURS ' || mod(floor((date1-date2)*24*60),60) || ' MINUTES ' || mod(floor((date1-date2)*24*60*60),60) || ' SECS ' time_difference FROM dates;

Summary of requested processes by process status

The following query will give you a summary of the requested processes by process status. SELECT RQST.RUNSTATUS, RQST.PRCSTYPE, ( SELECT XLAT.XLATLONGNAME FROM PSXLATITEM XLAT WHERE XLAT.EFFDT = ( SELECT MAX(XLAT_ED.EFFDT) FROM PSXLATITEM XLAT_ED WHERE XLAT_ED.FIELDNAME = XLAT.FIELDNAME AND XLAT_ED.FIELDVALUE = XLAT.FIELDVALUE ) AND XLAT.FIELDNAME = 'RUNSTATUS' AND XLAT.FIELDVALUE = RQST.RUNSTATUS ) AS RUNSTATUS_XLAT, COUNT(RQST.PRCSINSTANCE) AS TOTAL_PROCESSES, MIN(RUNDTTM) AS FIRST_OCCURRED, MAX(RUNDTTM) AS LAST_OCCURRED FROM PSPRCSRQST RQST GROUP BY RQST.RUNSTATUS, RQST.PRCSTYPE ORDER BY RUNSTATUS_XLAT, RQST.PRCSTYPE

Common Process failures

The App Engine program has run to No Cuccess and we dont see any error in the log. But as per the trace file that is being generated we can see that the following errors have occurred: 28 01.00.04 340.792628 CMgrUpdateTransaction::OnPreCommit : trying to commit a failed transaction 29 01.00.04 0.000823 CMgrUpdateTransaction::OnPreCommit : trying to commit a failed transaction 30 01.00.04 0.008131 CMgrUpdateTransaction::OnAbort 31 01.00.04 0.000033 CMgrUpdateTransaction::OnAbort 32 01.00.04 0.000738 CMgrUpdateTransaction::OnPreCommit : trying to commit a failed transaction Cause: The cause of this issue is corruption of the Process Scheduler cache on DB Platform. This is sporadic failure, some App Engine programs will fail and some of them will run successfully. Steps to be taken: Try restarting/ rerunning the processes after deleting the failed instances. If the problem persists, Bounce the Process scheduler server and Clear Cache. SOLUTION INSTRUCTIONS --------------- Implement the following instruction steps: 1. On Process Scheduler Windows box, run psadmin to stop the Process Scheduler. 2. Navigate to PS_HOME\appserv\prcs\DBNAME directory. 3. Delete the CACHE directory. 4. Retest the issue. 5. Migrate the solution as appropriate to other environments.

Query to find the Special Characters in PS_NAMES table

SELECT N.EMPLID,N.NAME_TYPE,N.EFFDT,N.NAME,TRANSLATE(UPPER(N.NAME) ,'`/()-,.ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"''',' ') AS "SPECIAL" FROM PS_NAMES N WHERE N.EFFDT=(SELECT MAX(N1.EFFDT) FROM PS_NAMES N1 WHERE N1.EMPLID=N.EMPLID AND N1.NAME_TYPE='PRI' AND N1.EFFDT<=SYSDATE) AND N.NAME_TYPE='PRI' AND RTRIM(LTRIM((TRANSLATE(UPPER(N.NAME),'`/()-,.ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"''',' '))))<>' ' ORDER BY N.EMPLID

PeopleSoft Security related Queries (Permission List, Roles etc)

Component Permission List Query: This query identifies 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; 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; 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; 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; 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; 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;

PORTAL_CSS process error out

PeopleTools 8.49.27 - Application Engine Server Copyright (c) 1988-2013 PeopleSoft, Inc. All Rights Reserved PSAESRV started service request at 23.37.09 2013-03-25 Not authorized CRef: HC_AWARD_SUMMARY_GBL (95,5032) Not authorized CRef: EO_EO9030R_GBL (95,5032) Not authorized CRef: HC_AV_P_GPROFILE_GBL3 (95,5032) Not authorized CRef: HC_AV_VOL_HIST_GBL2 (95,5032) Not authorized CRef: EOEI_INCR_LOAD_GBL (95,5032) Not authorized CRef: EOEC_MP_ROLE_GBL (95,5032) Not authorized CRef: HC_SS_CC_TODOS_GBL (95,5032) Not authorized CRef: HC_FPMCONVTABL_PNL_GBL5 (95,5032) Not authorized CRef: HC_AV_INVLV_SMRY_E_GBL1 (95,5032) Not authorized CRef: HC_SSR_SSENRL_EXAM_L_GBL3 (95,5032) Not authorized CRef: HC_LN_EDIT_DFLTS_GBL1 (95,5032) Not authorized CRef: HC_RUNCTL_DISBURSE_GBL (95,5032) Not authorized CRef: HC_FA_SS_BATCH_INQ_GBL (95,5032) Not authorized CRef: HC_GPIN_RC_ITCS_GBL (95,5032) Not authorized CRef: HC_GPIN_YE_F6_GBL (95,5032) Not authorized CRef: HC_GPMX_INF_BR_GBL (95,5032) Not authorized CRef: HC_GPMX_MAINT_SUB_EE_GBL (95,5032) Not authorized CRef: HCSS_TRCR_RPT_GBL (95,5032) Not authorized CRef: HC_SFA_ASG_ELIG_GBL (95,5032) Not authorized CRef: HC_SFA_ASG_HS_PROGRAM_GBL (95,5032) Not authorized CRef: HC_SFA_ASG_MRR_EC_GBL (95,5032) Not authorized CRef: HC_GPAR_LAYOUT_GBL (95,5032) Not authorized CRef: HC_HONORS_AWARDS_GBL4 (95,5032) Not authorized CRef: HC_AV_GCLUB_PRG_GBL2 (95,5032) Not authorized CRef: HC_INAS_IM_SMRY_05_GBL (95,5032) Not authorized CRef: HC_SCC_SM_SEARCH_GBL14 (95,5032) Not Authorized. (96,1) Not Authorized. (96,1) First operand of . is NULL, so cannot access member ItemByName. (180,236) PORTAL_CSS.CREFPERM.GBL.default.1900-01-01.Step01.OnExecute Name:CheckParentFolder PCPC:2339 Statement:40 Called from:PORTAL_CSS.CREFPERM.GBL.default.1900-01-01.Step01.OnExecute Statement:202 Process 209291 ABENDED at Step PORTAL_CSS.CREFPERM.Step01 (PeopleCode) -- RC = 24 (108,524) Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s PSAESRV completed service request at 23.37.21 2013-03-25 Solution: As per oracle support,the below is the information provided: please provide roles to the user and run the portal security sync process. To run portal security sync, you need access to every single folder and content reference in the portal. The two specially delivered roles that will give you this is: PeopleSoft Administrator Portal Administrator It is seen that if you don't have a role that gives you access to the entire portal structure then, the portal security sync application engine program (PORTAL_CSS) will run to No Success (abend) with a message like this in the standard output (.stdout) file: First operand of . is NULL, so cannot access member ItemByName. (180,236) PORTAL_CSS.CREFPERM.GBL.default.1900-01-01.Step01.OnExecute Name:CheckParentFolder PCPC:2339 Statement:40 Called from:PORTAL_CSS.CREFPERM.GBL.default.1900-01-01.Step01.OnExecute Statement:202

SQL for finding the navigation of a Component

Query 1: 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 = 'Component Name here' 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 Query 2: SELECT DISTINCT LTRIM (SYS_CONNECT_BY_PATH (PORTAL_LABEL, ' > '), ' > ') PATH FROM ( SELECT * FROM (SELECT * FROM PSPRSMDEFN WHERE PORTAL_NAME = 'EMPLOYEE') CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME START WITH (PORTAL_URI_SEG2 = UPPER ('Component Name here') AND PORTAL_REFTYPE = 'C' AND PORTAL_CREF_USGT = 'TARG') ) WHERE PORTAL_REFTYPE = 'C' CONNECT BY PORTAL_PRNTOBJNAME = PRIOR PORTAL_OBJNAME START WITH (PORTAL_OBJNAME = 'PORTAL_ROOT_OBJECT' AND PORTAL_REFTYPE = 'F') Check if the Component is registered SELECT LPAD('–',2*(LEVEL-1)) || PORTAL_LABEL "NAVIGATION” FROM (SELECT PORTAL_LABEL, PORTAL_PRNTOBJNAME, PORTAL_OBJNAME, PORTAL_URI_SEG2 FROM PSPRSMDEFN A WHERE PORTAL_NAME = ‘EMPLOYEE' ) B WHERE B.PORTAL_PRNTOBJNAME != ' ' START WITH (B.PORTAL_URI_SEG2 IN (SELECT D.PNLGRPNAME FROM PSMENUITEM A, PSMENUDEFN B, PSPNLGROUP D WHERE A.MENUNAME=B.MENUNAME AND A.PNLGRPNAME = D.PNLGRPNAME AND A.PNLGRPNAME LIKE UPPER('Enter Component Name')) ) CONNECT BY PRIOR B.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME