Useful PeopleSoft SQL

 Over the years, I've accumulated some SQL that I use frequently. Here are some

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Find Portal Menu for Component

SELECT A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3,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 LIKE '%COMPONENT_NAME%'

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Find permission list

SELECT A.OPRID

, C.ROLENAME AS ROLE

, D.CLASSID AS PERMISSION

, D.MENUNAME AS MENU

, D.BARITEMNAME AS COMPONENT

, D.PNLITEMNAME AS PAGE

, E.PAGEACCESSDESCR

, D.DISPLAYONLY

FROM PSOPRDEFN A

, PSROLECLASS B

, PSROLEUSER C

, PSAUTHITEM D

, PSPGEACCESSDESC E

WHERE B.ROLENAME = C.ROLENAME

AND A.OPRID = C.ROLEUSER

AND B.CLASSID = D.CLASSID

AND D.AUTHORIZEDACTIONS = E.AUTHORIZEDACTIONS

AND D.MENUNAME = 'CONTRACT_MGMT'

--AND B.CLASSID = 'Class name'

--AND D.BARITEMNAME = 'Bar Name'

--AND A.OPRID = 'UserName'

AND D.PNLITEMNAME LIKE '%ComponentName%'

AND ACCTLOCK = 0

ORDER BY 2

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Find Message Errors by Text

SELECT   *

  FROM   psmsgcatdefn

 WHERE   TO_CHAR (SUBSTR (descrlong, 1, 4000)) LIKE

            '%Message%';

SELECT   *

  FROM   psmsgcatdefn

 WHERE   MESSAGE_TEXT LIKE

            '%Message%'    ;

          

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Find Contract File Attachment

SELECT * FROM PS_PV_ATTACHMENTS WHERE SCM_ATTACH_ID IN

(SELECT SCM_ATTACH_ID FROM PS_CS_DOC_FILES WHERE CS_DOC_ID IN (

SELECT CS_DOC_ID FROM PS_CS_DOC_HDR)

)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Find Journal Attachments

SELECT * FROM PS_JRNL_HEADER WHERE JOURNAL_ID = 'JOURNALID12'

SELECT * FROM PS_JRNL_HEADER_ATT WHERE JOURNAL_ID = 'JOURNALID12'

SELECT JOURNAL_ID, SCM_ATTACH_ID FROM PS_JRNL_HEADER_ATT WHERE JOURNAL_DATE BETWEEN TO_DATE('1/01/2022,'mm/dd/yyyy') and TO_DATE('12/31/2022','mm/dd/yyyy')

SELECT * FROM PS_SCON_ATT

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Find Requisition Attachments

SELECT A.BUSINESS_UNIT 

 , A.REQ_ID 

 , A.LINE_NBR 

 , A.SCM_ATTACH_ID 

 , A.ATT_VERSION 

 , B. ATTACHSYSFILENAME 

 ,B.ATTACHUSERFILE 

 , A.PUBLIC_FLG 

 , A.COMMENT_TYPE 

 , A.SHIPTO_ID 

 , A.COMMENT_ID 

 , A.RANDOM_CMMT_NBR 

  FROM PS_REQ_COMMENTS A 

  , PS_PV_ATTACHMENTS B 

 WHERE A.LINE_NBR > 0 

   AND A.SCM_ATTACH_ID =B.SCM_ATTACH_ID

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Find Purchase Order Attachments

SELECT DISTINCT A.BUSINESS_UNIT 

 , A.PO_ID 

 , A.LINE_NBR 

 , A.SOURCE_FROM 

 , A.SOURCE_BU_SETID 

 , A.SOURCE_ID 

 , A.SOURCE_LINE_NBR 

 , A.SCM_ATTACH_ID 

 , B.ATT_VERSION 

 , B.ATTACHSYSFILENAME 

 , B.ATTACHUSERFILE 

  FROM PS_PO_COMMENTS A 

  , PS_PV_ATTACHMENTS B 

 WHERE A.SCM_ATTACH_ID = B.SCM_ATTACH_ID 

   AND A.ATT_VERSION = B.ATT_VERSION

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Find My Projects

SELECT PROJECTNAME, LASTUPDOPRID, LASTUPDDTTM  FROM PSOWNER.PSPROJECTDEFN WHERE PROJECTNAME LIKE '%%'

AND LASTUPDOPRID = 'USERNAME'