Dynamic Roles in Peoplesoft

Why Dynamic roles ?

1) The assignment of roles to User Profiles based on your business rules.
2) These business rules run against system(s) to assign PeopleSoft access.
3) PeopleSoft security changes in an automatically.
4) The dynamic role rule process removes and grants access to User Profiles

PeopleTools > Security > Permissions & Roles >Roles  (Dynamic Member Tab/Page).

There are three ways can use to execute your rules to find the dynamic role users.
o PS/Query
o LDAP Plug-in
o PeopleCode
1) Access is removed or granted based on the User Profile IDs retrieved by the query.
2) Logic to select the dynamic role users will be present in the SQL of the Query.
Note: -
a) Use PSOPRALIAS_VW or PSOPRALIAS record to create the dynamic role queries.
    PSOPRALIAS - Used to store ID values corresponding to various ID types. For example, stores  EMPLID for Employee type, CUST_ID for customer ID. It will be easy to join the tables based on the type of the Userid.
b) Don't use any Bind variables in the role queries. They are not designed for resloving the bind variables.
c) Use Disntict caluse in the SQL to make you always retrive uniques id only. Any duplicate will the the dynamic role asssignement.
 1) Access is removed or granted based on the User Profile IDs pushed in to the system array variable 
 2) This is used when logic is complicated and cannot be retried from the Query.


Peoplesoft AWE Workflow configuration DMS scripts

Peoplesoft AWE Workflow configuration and setup tables. Ver 9.0

User the following scripts to move the AWE configuration / setup data from the one instance to another instance.

Note :- add where clause if you need to move only configuration specific to particular AWE transactions.

Export script

-- "Register Transaction"

-- "Approvals > Workflow Transaction "

-- "Configure Transactions"

-- "Setup Process Definitions"


Peoplesoft HCM Process - Scheduled process.

HR_PERSDATA Application Engine - Updates the PERSONAL_DATA snapshot table with future dated entries as they become effective.

NAME_DISPLAY Application Engine - Refreshes the NAME_DISPLAY and NAME_FORMAL fields in all records with the NAME_GBL_SBR. Use when the criteria used to change these fields is changed.

PER099 Application Engine - Fill EMPLOYEES Table.

PTAF_NEM Application Engine - PTAF_NEM - Approval Framework Escalation App Engine.           AWE Workflow escalation process.

SCRTY_OPRCLS Application Engine - SCRTY_OPRCLS - Updates the SJT_OPR_CLS security Join Table.

SCRTY_SJTDLY Application Engine -  Nightly update for SJT Records -  Update of all effdt rows that have become current as of this data.

HRS_AM Application Engine - Applicant Search Auto-Match -Recruiter Applicant Search auto-match process.

HRS_DUPE_PRC Application Engine - Find Applicant Duplicates - Finds applicant duplicates given certain criteria.

HRS_JOB_AGNT Application Engine - Job Agent - Run the Job Posting Search Agent process.

HRS_JSCH_IDX Application Engine -Verity based Job Posting Index build process.

HRS035 SQR Report - Application Analysis (USA).

PER506 SQR Report - Department Tbl & Departmental - This report lists any departments that you have created in the Department Tree but have not yet added to the departmental security tree, It also lists any changes in departments since the effective date of the current security tree.

PER900 SQR Report - Core HR Data Integrity Audit. - Audit of the CORE HR Tables for Data Relationship Integrity.

PSBARUN - Cobal Job - For the Benefit Administration - scheduled to run twice each night with some custom pre and post processes.


F1 Context Help for Peoplesoft Application Designer

Access Peoplebooks from Applicaiton Desinger.
Context Help for Application designer.

Follow the setps to configure F1 - context help for the application designer.
Press F1 in the application designer to get the help based on the URL configuration below.
It can take to the peoplebooks based on the context.

Navigate to PeopleTools > Utilities > Administration > PeopleTools Options

Under the help option you can set the URL to the People books.

URL needs to be in the following format.

%CONTEXT_ID% is the object name or context ID of the currently displaying page or dialog box.
%LANG_CD% is the three-letter language code for the user's preferred language.

Cntrl+F1 - can be used as alternative help URL to the website on the internet or the company's internal website.


Tools/Utilites & Applications for Peoplesoft Development.

Productivity tools for the Peoplesoft Development.

1) SQL-Formatter :- The Application engine sql statements invloving are not formatted well for   understanding it.

SQL-formatter comes in handy to format the SQL. It helps in Debuggin ,Review & performance tunning of the complex qurey.

http://www.dpriver.com/pp/sqlformat.htm?ref=g_wangz - Online formatter.

http://www.dpriver.com/index.php?ref=g_wangz - Offline SQL Formatter.

Pros :- Works with different databases SQL statements.

Cons:- Doesn't recongnize many Peoplesoft Meta-SQL tags. Just replace them with sql function or static values then it works fine.

2) Pace Trace :- Pace-Trace is effectively a viewer for PeopleSoft SQL trace files. It reads a selected trace file and creates a grid that summarizes all the SQL actions contained in the file.

This quick access to trace file information can be used by everyone from business analysts to developers, speeding report design, custom development, and troubleshooting.

Following Trace files generated in different methods can be

1)Trace an entire session - Trace file generated by the entire session. Enabled on the login trace page.
2)Interactive Tracing - Enabled at PeopleTools > Utilities > Debug > Trace SQL.
3)Application Engine trace.
4)COBAL Tracing.
 3) PS Pad editor :- This editor is very much similar to the Ultra edit and Edit plus which many of us know.

What makes it special is for the following reasons.

1) Peoplecode.INI file which is code highligter for the peoplecode like in the application designer - Why do we need this one for ?? Read the next reason.

2) Export to Clipboard as RTF feature (File > Export > Export to Clipboard as RTF) is allow you to copy the peoplecode from the editor with the code highlighter and paste in the word document or email. code highlighter formatting is retained.

3)SQR.INI file is also available for SQR programming.

Check out this link - http://xtrahot.chili-mango.net/2007/07/prettify-those-code-listings/

for details on how you can use it.
4) Zapgrab 1.0 :- The best , easiest & Free way to capture screens, and paste them into Documents & presentations.

It is very helpful to capture the screens for the Testcase results document , Technical design & Functional design documents.

ZapGrab allows you to capture your screen in two different ways: the whole screen or only a section with a quadrangular form selected by you with the mouse.

Just select the area on the screen and to Cntrl+v.

Cons:- Difficult to capture scrolling window screens.

Link:- http://www.softpedia.com/get/Multimedia/Graphic/Graphic-Capture/Zapgrab.shtml
5) SQR Express :- Developing SQRs Has Never Been So Easy! - In a powerful environment you can edit multiple files with unlimited length, maintain multiple database logins,

navigate in the source-code by clicking procedure-names or inside a treelike program-flow, variables, calls, includes or records.

Cons:- Not a freeware.
Detalied features here.
6) JEdit :- Java based open source editor. Code highligther for SQR available

Pros: - A lot of open source plugins avaliable.
The only free ware text editor with help of plugins can create SQR tree like SQR Express.
Can create own plugins. Create plugins for intellisense.

Cons:- Not the easiest way installing plugins with different versions.



HCM/HRMS Tables for the PS QUERY - Querying HCM/HRMS table in peoplesoft

HCM/HRMS Query reporting

The following are the main tables used to query HR related data

Human Resources Information/ Personal Data:

Employment Data:

EMPLOYMENT- general employment data, such as various service dates, etc.
Department Inforamation
DEPT_TBL --This is one of foundation tables in HR. You will rarely query against it directly; more

likely you will join to it to get the description or short description. Department codes in PeopleSoft are

different from what was used in HRS/Legacy, and department data is effective dated.
Job code information
JOBCODE_TBL --This is also a foundation table. This table holds job codes, descriptions and related

data such as FLSA classification, pay plan, grade and structure information.
Job Data:

JOB-- Job (roughly equates to assignment records in legacy). Contains job records along with other

data. Effective dated someone's job history will be stored in this table. Remember, Empl Records

refer to an employee's job record(s). The existence of multiple Empl Records means that they have

had or are currently holding multiple jobs Empl Records start with 0, and their benefits are tied to

Empl Record #0.
Benefits Data :

BEN_PROG_PARTIC – Stores the Benefit Program to which the employee is assigned.

LEAVE_PLAN – Stores enrollment in leave plans.

HEALTH_BENEFIT – Stores e nrollment in health insurance plans

SAVINGS_PLAN -- Stores enrollment in TDA, ORP and Deferred Compensation

LIFE_ADD_BEN -- Stores enrollment in life insurance plans.

RTRMNT_PLAN – Stores TRS enrollment.

DISABILITY_BEN – Stores enrollment in disability plans.
Tax Data:

FED_TAX_DATA – Contains federal tax data, including nonresident alien data

STATE_TAX_DATA – Although there is no state withholding in Texas, this record is necessary to

maintain state of residence and unemployment jurisdiction.
Payroll Data:

Payroll processing check data (records used by the payroll process to store check history)








Payroll funding distribution data (records that tie funding to payroll actuals):



Other employee level payroll data:

GENL_DEDUCTION - Genearl deduction.

GENL_DED_CD        - General deduction code.

ADDL_PAY_EFFDT -  additional pay effdt.

ADDL_PAY_DATA   - additional pay data.

ADDL_PAY_ERNCD - additional pay earn code.

DIRECT_DEPOSIT  - Direct deposit.

DIR_DEP_DISTRIB - Direct deposit distribution.
Position Data:

POSITION_DATA—identifies position data for each business unit.

Tenure Data:

EG_TENURE  Tenure Data.
Use the Employees table whenever possible. Queries using this table will generally have better

performance for several reasons. This tables has name, Empl ID, empl records, job title, job code,

dept title, dept code, location code, comp rate, comp frequency, position number, standard hours,

etc., data for active employees. Two fields that it does not have are Empl Class and FTE. This table

is not a transactional table, meaning that records are not actively written to it throughout the day.

Rather, it is refreshed nightly on process scheduler. There may be a one-day lag time for new data to

hit the table, but for most reporting this is acceptable.