随笔分类 -  peoplesoft tables&query sql

PeopleSoft Permission List Queries
摘要：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_descFROM psauthitem auth,psmenudefn menu,psmenuitem menuitm,pspnlgroup comp, 阅读全文
posted @ 2013-10-26 17:57 Bryan chen 阅读(523) 评论(0) 推荐(0)
Find Duplicate Objects in Application Desinger Projects
摘要：The sql will identify duplicate objects in different application designer projects to eliminate duplicate work by developers.You will find the SQL very handy during the analysis phase of a peoplesoft upgrade project. You1st need to identify your projects and run compare and report through applicatio 阅读全文
posted @ 2013-10-26 17:53 Bryan chen 阅读(295) 评论(0) 推荐(0)
PeopleTools Catalog Tables
摘要：ProjectsPSPROJECTDEFN — Project header tablePSPROJECTITEM — Definitions in the projectFieldsPSDBFIELD — Fields in the systemPSXLATITEM — Translate ValuesRecordsPSRECDEFN — Record header tablePSRECFIELD — Fields in the record (subrecords not expanded)PSRECFIELDALL — Fields in the record (subrecords e 阅读全文
posted @ 2013-10-26 17:21 Bryan chen 阅读(292) 评论(0) 推荐(0)
Permission Lists Assigned to a User
摘要：SQL that I find useful in many occasions. It will return a list of permissions that are assigned to a specific user.SELECTd.oprid,d.oprdefndesc,c.roleuser,a.rolename,a.classid,b.classdefndescFROMpsroleclass a,psclassdefn b,psroleuser c,psoprdefn dWHEREa.classid=b.classidANDc.rolename=a.rolenameANDd. 阅读全文
posted @ 2013-10-24 14:41 Bryan chen 阅读(173) 评论(0) 推荐(0)
Show Users Assigned to a Specific Role
摘要：In aprevious postI showed you how to know what Roles are assigned to a specific user. But here is how you find out what Users are assigned to a specific Role.SELECTC.OPRID, C.OPRDEFNDESC, A.ROLENAME, A.DESCR FROMPSROLEDEFN A, PSROLEUSER B, PSOPRDEFN C WHEREB.ROLENAME=A.ROLENAME ANDC.OPRID=B.ROLEUSER 阅读全文
posted @ 2013-10-24 14:40 Bryan chen 阅读(176) 评论(0) 推荐(0)
Show Roles Assigned to a Specific User
摘要：Here is a query that I often use to lookup Roles assigned to a specific PeopleSoft user.At run time, replace :1 with OPRID your are looking for OR user name (partial search also works).SELECTC.OPRID,C.OPRDEFNDESC,A.ROLENAME,A.DESCRFROMPSROLEDEFN A,PSROLEUSER B,PSOPRDEFN CWHEREB.ROLENAME=A.ROLENAMEAN 阅读全文
posted @ 2013-10-24 14:38 Bryan chen 阅读(181) 评论(0) 推荐(0)
Query for Component Path within PeopleSoft Portal
摘要：1) Run the below SQL to get the content reference name for your componentSELECTPORTAL_NAME, PORTAL_OBJNAMEASCONTENT_REFERENCE, PORTAL_LABEL, PORTAL_URI_SEG1ASMENU, PORTAL_URI_SEG2ASCOMPONENT, PORTAL_URI_SEG3ASMARKET FROMpsprsmdefn WHEREPORTAL_NAME='EMPLOYEE' ANDPORTAL_URI_SEG2=:1;-- Replace 阅读全文
posted @ 2013-10-24 14:37 Bryan chen 阅读(211) 评论(0) 推荐(0)
Custom PeopleSoft Queries
摘要：The following SQL identifies custom queries created in your system from thePSQRYDEFNPeopleTools table. It includes the query type (and the operator ID who owns the query if it is private).selectQRYNAMEas"Query Name",DESCRas"Query Description",CREATEOPRIDas"Created By",C 阅读全文
posted @ 2013-10-24 13:45 Bryan chen 阅读(241) 评论(0) 推荐(0)
如何找出component的注册路径
摘要：SELECT DISTINCT REVERSE(LTRIM(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL), ' >- '), ' >- ')) "NAVIGATION" FROM PSPRSMDEFN PWHERE PORTAL_OBJNAME = 'PORTAL_ROOT_OBJECT'START WITH PORTAL_URI_SEG2 = 'C_PUNCH_EXPET'/*要找的component的名称*/CONNECT BY PRIOR PORTAL_P 阅读全文
posted @ 2013-10-24 13:38 Bryan chen 阅读(175) 评论(0) 推荐(0)
Delete PeopleSoft Query From the Database
摘要：There could be different reasons why a PeopleSoft developer would like to delete a query from the database. Upgrade clean up would probably be the most common one. Here is a function you can use to get you started.Function DeleteQuery(&sQueryName As string) SQLExec("DELETE FROM PSQRYDEFN WH 阅读全文
posted @ 2013-10-24 13:09 Bryan chen 阅读(200) 评论(0) 推荐(0)
PeopleSoft Object Types Definitions
摘要：PeopleSoft stores object definitions types such as Record, Field and SQL definitions as numbers inPeopleTools meta-tables. Here is a list of what each number means with respect to its definition.Definitions for Record Object TypesThe following are stored on the PSRECDEFN table on RECTYPE field. RECT 阅读全文
posted @ 2013-10-24 13:02 Bryan chen 阅读(242) 评论(0) 推荐(0)