Peoplesoft Security Tables

PeopleSoft security tables

When conducting an audit of PeopleSoft, eventually you will need to run queries against the PeopleSoft security tables. Listed below are the PeopleSoft delivered security table names, and a brief description of the table contents.

PSROLEUSER

User Roles

PSOPRDEFN PSAUTHITEM PSAUTHSIGNON PSAUTHPRCS PSACCESSPRFL PSOPRCLS

Operator ID and password information. Passwords stored are encrypted. Updated by Security Administrator. Menus, panels and items an Operator is authorized to access. Updated by Security Administrator. Records sign-on times for all Operators. Updated by Security Administrator. Records process groups for all Operators. Updated by Security Administrator. Security table used to allow a super-user to create an access profile that would contain an Access ID and Access Password. Access ID and Passwords are used to connect PeopleSoft to the underlying database. (only in version 7 and above) Maps one Operator to multiple Operator Classes. (only in version 7 and above)

SQL's used in security access SELECT * FROM PSROLEUSER A WHERE A.ROLEUSER = 'A58431'; SELECT * FROM PSROLEUSER A WHERE A.ROLEUSER = 'A56250'; SELECT * FROM PS_ROLEXLATOPR WHERE ROLEUSER = 'A56250'; SELECT * FROM PSOPRDEFN PSOPRDEFN, PSROLEUSER PSROLEUSER WHERE PSOPRDEFN.OPRID=AND PSROLEUSER.ROLENAME LIKE 'WF%' Please see below for more SQLs related to Security

FinSupply->Security ->PayCycle by UserId SECURITY SQLs SQL to find out the users who have access to the page.

Select oprid,oprdefndesc from PSOPRDEFN where oprid in ( SELECT C.ROLEUSER FROM PSCLASSDEFN A, PSROLECLASS B, PSROLEUSER C WHERE A.CLASSID = B.CLASSID AND B.ROLENAME = C.ROLENAME AND A.CLASSID in ( sELECT classid FROM PSAUTHITEM WHERE MENUNAME='PROCESS_SCHEDULER' AND PNLITEMNAME = 'PRCSDEFNCNTDIST'));

--select * from PSAUTHITEM where classid = 'HPOC_DEV_%READ%' and menuname='DEFINE_GENERAL_OPTIONS'; --select * from PSAUTHITEM where classid = 'TESTALL' AND PNLITEMNAME='OPR_DEF_TABLE_PM1'; -- UPDATE PSAUTHITEM SET DISPLAYONLY=0 where classid = 'TESTALL' and menuname='PV_MAIN_MENU'; -- to find the pages assciated with the role: SELECT B.MENUNAME, B.BARNAME, B.BARITEMNAME, B.PNLITEMNAME, C.PAGEACCESSDESCR, B.DISPLAYONLY FROM PSROLECLASS A, PSAUTHITEM B, PSPGEACCESSDESC C WHERE A.CLASSID = B.CLASSID AND A.ROLENAME = 'h_epro_config' AND B.AUTHORIZEDACTIONS = C.AUTHORIZEDACTIONS ORDER BY 2; -- To find number behinf the update dispaly SELECT * FROM PSPGEACCESSDESC; -- To find the permission list attached to role : SELECT DISTINCT A.CLASSID FROM PSROLECLASS A WHERE A.ROLENAME='h_epro_config' ORDER BY 1; -- SELECT * FROM PSAUTHITEM WHERE classid='HPOC_DEVELOPERS' and MENUNAME='PV_MAIN_MENU'; AND PNLITEMNAME = 'Requisitions'; -- SELECT * FROM PSAUTHITEM WHERE classid LIKE 'HPOC_DEV_%READ%' and MENUNAME='PV_MAIN_MENU'; -- select * from PSAUTHITEM where classid LIKE 'HPOC_DEV_%READ%' AND DISPLAYONLY=1 AND AUTHORIZEDACTIONS>2; -- UPDATE PSAUTHITEM SET DISPLAYONLY=1 where classid LIKE 'HPOC_DEV_%READ%'; -- UPDATE PSAUTHITEM SET DISPLAYONLY=1 where classid LIKE 'HPOC_DEV_%READ%' -- making the page display only UPDATE PSAUTHITEM SET DISPLAYONLY=1 where classid = 'HPOC_DEVELOPERS' AND MENUNAME='PV_MAIN_MENU' AND PNLITEMNAME = 'PV_ADM_VNDR'; IN ('PV_ADM_SETUP_BU ', 'PV_ADM_SETUP_CODES', 'PV_ADM_ACCOUNTING ', 'PV_ADM_PROCUREOPTS', 'PV_ADM_VNDR ', 'PV_ADM_DEFINE_ITEM', 'PV_ADM_PROCU_USERS', 'PV_ADM_CATALOGS ', 'PV_ADM_MAIN2 ', 'PV_APP_MAN_PG ', 'PV_REQ_STATUS') ; COMMIT; -- SELECT * FROM PSAUTHITEM WHERE classid ='HPOC_DEVELOPERS' AND MENUNAME='PV_MAIN_MENU' AND PNLITEMNAME = 'PV_REQ_FORM' ;

-- SELECT * FROM PSAUTHITEM WHERE classid ='HPOC_DEV_REQ_VW_READ' AND MENUNAME='PV_MAIN_MENU' AND PNLITEMNAME = 'PV_REQ_ENTRY' ; -- UPDATE PSAUTHITEM SET DISPLAYONLY=0 WHERE classid ='HPOC_DEV_REQ_VW_READ' AND MENUNAME='PV_MAIN_MENU' AND PNLITEMNAME = 'PV_REQ_ENTRY' ; -- WHERE classid ='HPOC_DEV_REQ_VW_READ' AND MENUNAME='PV_MAIN_MENU' AND PNLITEMNAME LIKE 'PV_REQ_STATUS' ; -- UPDATE PSAUTHITEM SET AUTHORIZEDACTIONS=2 WHERE classid ='HPOC_DEVELOPERS' AND MENUNAME='PV_MAIN_MENU' AND PNLITEMNAME = 'PV_REQ_FORM' ; -- SELECT * FROM PSAUTHITEM WHERE classid ='HPOC_DEV_CONFIG' AND MENUNAME='PV_MAIN_MENU' AND PNLITEMNAME NOT IN ( 'PV_OPR_LINKS ', 'OVERALL_PREFERENCES ', 'PURCHASING ', 'PV_REQ_FORM ', 'PV_ACTIONS ', 'PV_ACTIONS_BU '); -- UPDATE PSAUTHITEM SET DISPLAYONLY=0 WHERE classid IN ('HPOC_DEV_CONFIG','HPOC_DEVELOPERS') AND MENUNAME='PV_MAIN_MENU' AND PNLITEMNAME IN ('PV_ADM_SETUP_BU ', 'PV_ADM_SETUP_CODES', 'PV_ADM_ACCOUNTING ', 'PV_ADM_PROCUREOPTS', 'PV_ADM_VNDR ', 'PV_ADM_DEFINE_ITEM', 'PV_ADM_PROCU_USERS', 'PV_ADM_CATALOGS ', 'PV_ADM_MAIN2 ', 'PV_APP_MAN_PG ', 'PV_REQ_STATUS', 'PV_ADM_SETUP_GUIDE') ; WHERE classid ='HPOC_DEV_CONFIG' AND MENUNAME='PV_MAIN_MENU' SELECT * FROM PSAUTHITEM WHERE classid IN ('HPOC_DEV_CONFIG' ,'HPOC_DEVELOPERS') AND PNLITEMNAME LIKE 'OPR_DEF_TABLE%'; MENUNAME='PV_MAIN_MENU' AND PNLITEMNAME LIKE 'OPR_DEF_TABLE%'; SELECT * FROM PSAUTHITEM WHERE MENUNAME='PV_MAIN_MENU' AND PNLITEMNAME = 'PV_REQ_FORM' ; -- SELECT * FROM PSAUTHITEM WHERE classid ='HPOC_DEV_CONFIG' AND MENUNAME='PV_MAIN_MENU' -UPDATE PSAUTHITEM SET DISPLAYONLY=1 WHERE classid IN ('HPOC_DEV_CONFIG' ,'HPOC_DEVELOPERS') AND MENUNAME='PV_MAIN_MENU' AND PNLITEMNAME IN ('PV_PO_DIS_BATCH','PV_IN_RUN_PUB_MSG','PV_OPR_LINKS' );

To get the process/job name and other details from process instance. SELECT * FROM PS_PMN_PRCSLIST WHERE PRCSINSTANCE = 1018493; Query with Effective date SELECT * FROM PS_DEPT_TBL E WHERE E.DEPTID = $B-DEPTID AND E.EFF_STATUS = 'A' AND E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_DEPT_TBL E_ED WHERE E.SETID = E_ED.SETID AND E.DEPTID = E_ED.DEPTID AND E_ED.EFFDT <= CURRENT DATE)

Prompt table suggestions

Page: Run Control Parameters:

Prompt Field Business Unit

Prompt Table SP_BU_PM_NONVW

Operating Unit

OPER_UNIT_NS_VW

Department From

DEPT_NS_VW

Vendor

VENDOR_PO_VW

Date

Date

Item

INV_ITEM_VW

Item Category

ITM_CAT_TBL

Combo check Rules table PS-COMBO_DATA_TBL

! Following code will help you to check the combo edit in ur sqr for your issue on Manual accruals. This is simple logic. begin-procedure Check-Combo begin-SELECT count(*) &count FROM ps_combo_data_tbl c WHERE c.setid = $Setid

!SetID here could be SHARE and can be hardcooded

and c.deptid = $deptid and c.product = $product and c.effdt_from <= sysdate and c.effdt_to >= sysdate end-SELECT if &count = 1 let $found = 'Y' ! WRITE YOUR CODE TO CHANGE THE STATUS OF THE ROW TO CORRECT else ! WRITE YOUR CODE TO CHANGE THE STATUS OF THE ROW ERROR ! SO THAT IT CAN BE CORRECTED ONLINE. end-if end-procedure Check-Combo

How to Schedule a job from People Code (Education Cess will run 2 minutes after voucher post when the user selects Voucher Post(FSTREAMLINE) from the Online page not by Navigation) /*** Begin Hits issue 0016827 -- Hewitt - Kiran -- 12/27/05 ***/ If %Component = Component.VCHR_EXPRESS Then If %Page = Page.VCHR_EXPRESS1 Then Local ProcessRequest &RQST;

&RQST = CreateProcessRequest();

&RQST.ProcessType = "SQR Process"; &RQST.ProcessName = "HEDCESS1"; &RQST.RunControlID = "EDUCESSRUN123^" | VOUCHER.BUSINESS_UNIT;

&RQST.RunDateTime = AddToDateTime(%Datetime, 0, 0, 0, 0, 2, 0); &RQST.TimeZone = %ServerTimeZone;

&RQST.Schedule(); &PRCSSTATUS = &RQST.Status; If &RQST.Status = 0 Then &Inst = &RQST.ProcessInstance; End-If; End-If; End-If; /*** End Hits issue 0016827 -- Hewitt - Kiran -- 12/27/05 ***/

Following SQL statement can be used to update pay_run_ap_status after Pay-Selection update ps_pycycl_stat set pay_run_ap_status = 'N' where pay_cycle = 'HAS01D' AND pay_run_ap_status = 'V';

Mismatch Errors in Requisition creation(Search Catalog) Ideally, Canadian BU should point to CNSHR and all others should point to SHARE setid for items. Dynamic view Vs SQL View

Dynamic view is different from a SQL view because you need not build the view. In dynamic view we cannot have EFFDT in a condition when it is a key field in the record that source the view. How to implement dynamic prompts

If BUS_UNIT_TBL_PM.PO_ITM_VNDR_OPT = "1" Then DERIVED.EDIT_ITEM_ID = Record.H_ITM_PURCH_VW; Else DERIVED.EDIT_ITEM_ID = Record.VENDOR_ITM_VW1; End-If;

Where DERIVED is a Record name, can be used as a prompt table say %EDIT_ITEM_ID If Prompt Table is some thing like %EDITTABLE30 means it is a dynamic prompt whose prompt values are populated by PeopleCode. REQ_CNTRCT_SRH1, REQ_CNTRCT_SRCH

How to attach PyCycl to a user?

Send Mail to logged in Operator Id If $Prcs_Process_Instance <> '' extract $Curr_Emplid from $Prcs_Oprid 1 5 End-If begin-select on-error = ERROR-HANDLER H_VM_ID &Vm_Id let $Mail_List=rtrim(&Vm_Id,' ') from PS_H_ALLASSC_TBL where rtrim(EMPLID) = $Curr_Emplid end-select evaluate $Dbname when = 'FNPRD84' let $Mail_List = $Mail_List || '@hewitt.com' let $Shell = 'mailx -s ' || $Subject || $Mail_List call system using $Shell #Unix_Status break when = 'FN84DEV' let $Mail_List = $Mail_List || '@hewitt.com' let $Shell = 'mailx -s ' || $Subject || $Mail_List call system using $Shell #Unix_Status break when = 'FN84QA' let $Mail_List = $Mail_List || '@hewitt.com' let $Shell = 'mailx -s ' || $Subject || $Mail_List call system using $Shell #Unix_Status break when = 'FN84SPRT' let $Mail_List = $Mail_List || '@hewitt.com' let $Shell = 'mailx -s ' || $Subject || $Mail_List call system using $Shell #Unix_Status end-evaluate

|| ' < ' || $ROutPath

|| ' < ' || $ROutPath

|| ' < ' || $ROutPath

|| ' < ' || $ROutPath

Item vendor table

SELECT U.* from PS_ITM_VNDR_UOM_PR U where INV_ITEM_ID LIKE '%29265'; PS_PO_ITM_STG PS_PO_ITM_STG_VND PS_PO_ITM_STG_UD

Subpage or Secondary page ? Sub and secondary have totally different contexts. A sub page is utilized where you wanna display/ capture similar information for various entities, for example, capturing an address, for a company or for a person, would need similar information like street address, state county, country etc.. In those situations a sub page would be used, to design once and reuse at multiple places. A Secondary page is used to display/ capture secondary information about an entity. A secondary page could use various sub pages, but the reverse is not true. For your case, I don't see any usage of either of the pages as you said its a custom component used to display multiple fields information on several pages, I would go for a subpage having key information displayed on the top of each page and then show relevant information on subsequent pages.