PeopleSoft 9.0

Data permission security can be administered trough tree-based or role-based security. Both methods involve assigning data access to user permission lists.

To assign a query to a group, the record GB_QRY_LINK_VW, must be part of the query. It GB_QRY_LINK_VW is not a record in the query, the query will not be accessible from the drop down list when creating a group.

A “wedge” row is a row of data that is wedged between two existing effective dated rows. For example:

First or a Historical row: CY070735 (emplid), 1/1/1980 (effdt)
Historical row: CY070735 (emplid), 4/1/2003 (effdt)
Wedge row: CY070735 (emplid), 4/1/2005) (effdt)
Current row: CY070735 (emplid), 9/14/2009 (effdt)

The Load Group Increases job, JOB006 will insert “wedge” rows.

If the Update Future Rows check box is on, the salary increase inserted on the wedge row will be carried forward to the future dated rows.

I was getting buffer errors when trying to access action reasons on-line. If I clicked the included history button, the window would open up. The problem turned out to be mismatched rows in child records.

The parent table is ps_actn_reason_tbl. When an action reason is created on-line the following child tables are updated in addition to ps_actn_reason_tbl;

ps_act_rsn_tbl_na
ps_act_rsn_tbl_aus
ps_act_rsn_tbl_ger
ps_act_rsn_tbl_mex
ps_actn_reason_bra
ps_act_rsn_tbl_esp
ps_actn_rsn_comm

If you are having buffer issues, pay particular attention to reason_bra and rsn_tbl_esp, in bold above. Check the database to make sure each value in the parent table has matching records in reason_bra and/or rsn_tbl_esp.

I was able to resolve the buffer issue by entering rows in the tables above if they were missing or mismatched. Also, since we are not using Brazil and Spain functionality, deleting the rows in those two tables got rid of the buffer issues as well.

Below are the Security Join Table process that should be run every night and a brief description of each.

PeopleSoft HRMS core row level security has four refresh processes. Use the refresh processes to keep your security data up to date so that the system is enforcing data permission using the most current information.

Note: The refresh processes are designed to refresh each row included in the process definition in sequence, causing the system to take an exceptionally long time to run the process when there are a large number of rows. To improve performance, we recommend clearing the Refresh All Rows check boxes on the run control pages and creating more defined run controls to run concurrently. (For example, create a run control for each permission list and run them simultaneously, rather than refreshing all permission lists under a single run control). You can save the run controls and use them as often as necessary.

Nightly Refresh SJT

Run the Nightly Refresh SJT process nightly to refresh the transaction security join tables. The nightly refresh process:

Updates the transaction security join tables with any changes to transaction security data that bypassed the SavePostChange PeopleCode.

The system automatically updates the transaction security join tables when you make and save a change on the transaction components, either by manual entry or a mass update that triggers the component interface. If you bypass the PeopleCode, you will need to capture the changes using a refresh process.

Updates the security join table with future-dated security rows that have become current (when the current calendar date matches up with the effective date of the transaction record) because SavePostChange PeopleCode is not triggered when a future-dated row becomes current..

If you are using future-dated security rows deletes the old security row and makes the future-flagged row the current row.

Run this process nightly for every security set you are using have just become effective.

Set up this process to run every night shortly after midnight using a recurring schedule and leaving the As Of Date field empty. By running the process shortly after midnight, you capture the formerly future-dated rows that

As Of Date - Leave the as of date blank when you schedule this run control ID to run on a recurring basis. The system will use the current, system date each time it runs.

Transaction Sec Join Table - Select the transaction security join table to update.

Include yesterday's changes? - Select to include the previous day's changes. The program searches the system for any changes to the transaction records on the previous day and updates the transaction security join tables with those changes. This ensures that any changes that were made to the data outside of components or component interfaces are captured.

If you do not select this check box, the process will only update the transaction security join tables with the changes made on the As Of Date.

Note: It is recommended that you select this option every time you run this process to guarantee that you are updating the transaction security join tables with the latest information. Only clear the check box if you are experiencing performance issues and you are certain that the records are not being updated outside of the regular user interface or component interfaces.


Refresh Row Security Operator

Run the Refresh SJT_CLASS_ALL process to refresh SJT_CLASS_ALL.

You will need to refresh SJT_CLASS_ALL using this process when you:

Modify a security access type.

Modifications include selecting to use future-dated security rows or changing the job data security options.

Create or modify a department security tree.
Create or modify a row security permission list on the Security by Dept Tree component.

Modifications include adding or removing data permission and refreshing the effective dates of trees.

Refresh SJT_OPR_CLS

Run the Refresh SJT_OPR_CLS process to refresh SJT_OPR_CLS.

You will need to refresh SJT_OPR_CLS whenever you create or change the relationship between a user profile and a permission list with data permission. Run the process when you:

Clone a user profile that has data permission.
Add a row security permission list that has data permission to, or delete one from, a user on the User Profile - General page.
Add a role with permission lists with data permission to, or delete one from, a user.
Add a permission list with data permission to, or delete one from, a user-assigned role

To trouble shoot a SavePostChange error look at the PeopleCode on the corresponding component. For example, to troubleshoot the following error PERSONAL_DATA.GBL.SavePostChange, open AppDesigner, open the Component, PERSONAL_DATA. Click on the View menu item and select View PeopleCode. Once the PeopleCode opens up select SavePostChange from the drop down list directly to the right of the component drop down list

Along with SavePostChange, PostBuild, PreBuild, and SavePreChange are events triggered from the component.

The last increase date resides on the ps_per_org_asgn table. It can be found on the ps_employment view and viewed on-line at Workforce Administration/Job Information/Pay Rate Change.

I ran into issues updating translate values. While I was logged in as the PS superuser I had access to 95% or the translate values. However, I did not have access to values such as pay_system_flg and position_status. To remedy this problem the security on the primary permission list of user PS needed to be updated.

Access Application Designer
Go/Definition Security
File/Open/Permission List

Select your permission list.

On the left side under "Excluded Group ID", highlight All Definitions and move it to the the left side of the screen.

Click save.

You should now have access to ALL translate values.

If you are having problem getting outer joins to work, it may be due to nested effective date logic. Here is a link to a blog post that helped me.

I have also had success using a UNION.

I basically UNION the first query to a second, not exists query, to return the full record set.

For example:
I want to find the value for mail drop on the position_data table. The customer stored this data historically on a custom field, cust_mail_stop on the department table. When they decided to go with full position management, they wanted to get rid of the custom field but update position_data.mail_drop with the correct value from the department table. All history rows needed to be updated. I joined position_data.deptid to dept_tbl.deptid. Also, I had to make sure I got the appropriate effective dated row. I did this by making the dept_tbl.effdt <= position_data.effdt.

The total number of rows in position_data = 35,000
Total number of rows returned by first query = 27,000
Total number of rows returned by second, not exists query = 8,000

Query 1 - Returned 27,000 rows

SELECT a.POSITION_NBR, a.EFFDT, a.deptid, a.mail_drop, a.qvc_mail_stop AS "Pos MP", b.cust_mail_stop
FROM psoft.ps_position_data a, psoft.ps_dept_tbl b
WHERE a.deptid(+) = b.deptid
AND b.effdt = (SELECT MAX(b2.effdt)
FROM psoft.ps_dept_tbl b2
WHERE b2.setid = b.setid
AND b2.deptid = b.deptid
AND b2.effdt <= a.effdt)

The second query is what I used to find the difference between the number of rows in position_data that didn't exist in Query 1.

Query 2 - Returned 8,000 rows

SELECT p.POSITION_NBR, p.EFFDT, p.deptid, p.mail_drop, p.cust_mail_stop AS "Pos MD", ' '
FROM psoft.ps_position_data p
WHERE NOT EXISTS (
SELECT a.POSITION_NBR, a.EFFDT, a.deptid, b.cust_mail_stop
FROM psoft.ps_position_data a, psoft.ps_dept_tbl b
WHERE a.deptid(+) = b.deptid
AND b.effdt = (SELECT MAX(b2.effdt)
FROM psoft.ps_dept_tbl b2
WHERE b2.setid = b.setid
AND b2.deptid = b.deptid
AND b2.effdt <= a.effdt)
AND a.position_nbr = p.position_nbr)

I then added a union statement between Query 1 and Query 2. This combined query returned all 35,000 rows, thus accomplishing what I could not accomplish because outer joins don't work well with effective dated subqueries.

The combined query is below.

SELECT a.POSITION_NBR, a.EFFDT, a.deptid, a.mail_drop, a.qvc_mail_stop AS "Pos MP", b.cust_mail_stop
FROM psoft.ps_position_data a, psoft.ps_dept_tbl b
WHERE a.deptid(+) = b.deptid
AND b.effdt = (SELECT MAX(b2.effdt)
FROM psoft.ps_dept_tbl b2
WHERE b2.setid = b.setid
AND b2.deptid = b.deptid
AND b2.effdt <= a.effdt)
UNION
SELECT p.POSITION_NBR, p.EFFDT, p.deptid, p.mail_drop, p.cust_mail_stop AS "Pos MD", ' '
FROM psoft.ps_position_data p
WHERE NOT EXISTS (
SELECT a.POSITION_NBR, a.EFFDT, a.deptid, b.cust_mail_stop
FROM psoft.ps_position_data a, psoft.ps_dept_tbl b
WHERE a.deptid(+) = b.deptid
AND b.effdt = (SELECT MAX(b2.effdt)
FROM psoft.ps_dept_tbl b2
WHERE b2.setid = b.setid
AND b2.deptid = b.deptid
AND b2.effdt <= a.effdt)
AND a.position_nbr = p.position_nbr)

I can never remember this navigation.

To set up defaults for permission lists follow this navigation:

Set Up HRMS/Foundation Tables/Organization/Org Defaults by Permission Lst.

To Register the program:

Navigation:
PeopleTools/Process Scheduler/Processes/

Add new value
System Process Requests
On Process Definition Options Tab, Process Security

Set component to PRCSMULTI
Set process groups to HRALL

To run the process:

Navigation:
PeopleTools/Process Scheduler/System Process Requests

Create Run Control
Click Run
Select process is list
Click Ok

If a view is created with Application Designer, it must be added to the query tree to allow it to be referenced with the query tool.

Navigation:
PeopleTools/Security/Query Sercurity/Query Access Manager

Click search. Select the appropriate tree. Add the new view.