Friday, March 28, 2014

SQL Query for Max Effective MAX (EFFDT) dated row from JOB table

Maximum Effective dated row in JOB table (MAX (EFFDT) FROM JOB)

Effective date is a key field in most of the tables (Control Tables, Transaction Tables etc…) and it’s used to track the history data in PeopleSoft. In other words, tables which have effective dated transactions provide us the opportunity to go back to history row and check how the data looked like as of a particular past effective date.

However, today the table that we are talking about is PS_JOB and it's apparently one of them. Remember, this is the key table used across almost all the PeopleSoft applications (Core HR, Benefits, Payroll, Performance Management etc…)

Please also see
Simplified Way to Provide a Page Access in PeopleSoft
Understanding Dynamic prompts in PeopleSoft
Implementing parallel processing using Application Engine in PeopleSoft
Adding and Maintaining Person Of Interest in PeopleSoft
PeopleSoft Set Control Field
Peoplesoft Row Level Security Search Records
FTE For Multiple Jobs in PoeopleSoft
PeopleSoft Set Control Field Concept and Tableset Sharing
Understanding Future dated security in PeopleSoft
PeopleSoft 9.1 Person Model
Creating Query Report with PS Query in PeopleSoft
PeopleSoft HRMS Online Training

SQL Query for Max Effective dated row from JOB table

A developer in their day to day work uses this table a lot in various ways. But the most frequent use is getting the Maximum effective dated row and in fact below is the query I have seen being used mostly:

   SELECT * FROM PS_JOB J
WHERE J.EMPLID = 'NZ11SN28'
    AND J.EFFDT = (SELECT MAX(J1.EFFDT)
                    FROM PS_JOB J1
                   WHERE J1.EMPLID = J.EMPLID
                     AND J1.EMPL_RCD = J.EMPL_RCD
                     AND J1.EFFDT <= SYSDATE)
    AND J.EFFSEQ = (SELECT MAX(J2.EFFSEQ)
                    FROM PS_JOB J2
                   WHERE J2.EMPLID = J.EMPLID
                     AND J2.EMPL_RCD = J.EMPL_RCD
                     AND J2.EFFDT = J.EFFDT)

Well, this SQL will work in many cases but not in few. Let’s understand how.

An employee NZ11SN28 has following rows in PS_JOB table:

You can see the max effective dated row is with EFFDT - 06-FEB-14 so, if we just run the SQL we saw earlier, then will see what we get:

So far so good..! The result is correct however, you should also note that in this case employee has only one job record (EMPL_RCD = 0) and that is substantive job record as we know.

But, what if employee has more than one job records, will the same SQL still work? Let’s check

Before we get into that you must understand the Person Model in PeopleSoft and that has been explained here PeopleSoft 9.1 Person Model

Please also see
'Override Position Data' and 'Use Position Data' Option in Job Data Page
Hiring a Person in PeopleSoft
How to find the List of Users Assigned to a Role
How the Various Date Fields on Employment Data Page are Updated
PeopleSoft 9.1 Person Model
How to Resolve a Row Level Security Issue in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft
SQL Query to Find Direct Reports for a Manager in PeopleSoft
Understanding Component Interface in PeopleSoft
How the FTE is Calculated in PeopleSoft
Business Unit, Company and Regulatory Region in PeopleSoft

Joining PS_PER_ORG_INST with PS_JOB in PeopleSoft

Suppose the employee NZ11SN28 gets an additional assignment under the same Employment Instance (PER_ORG = ‘EMP’ and ORG_INSTANCE_ERN = 0) and in the same Company/Regulatory Region hence, HR user creates an additional assignment (For more information on how to create Additional Assignment please visit PeopleSoft 9.1 Person Model).

Now the employee will have following records in PS_JOB table

The requirement is still same, get the Max effective dated row for the substantive job (EMPL_RCD = 0) so let’s again run the same SQL and see what we get:

Oops..!! you certainly expected the row with EFFDT = 6-Feb-14 but not the other one hence I would just imply that this SQL will not work in the cases where an employee has more than one Job record.

However, if the additional assignment is in different company or region then we can still add this criteria in the WHERE clause though still not a good idea but if it’s in the same company or region then it won’t work.

Although we certainly have a solution but I have seen many developers add Max Effective dated criteria on the field EMPL_RCD too hoping to get correct result which is absolutely something you shouldn’t do.

So what should we do now?

Not to worry, we can bring in the table PS_PER_ORG_INST in the scene to get the correct result i.e. we can join PS_JOB with PS_PER_ORG_INST. The reason we are joining PS_PER_ORG_INST is this table tracks the organization instances and hence we can easily identify which job record (EMPL_RCD) in PS_JOB table created for the Organizational Instance (ORG_INSTANCE_ERN) in PS_PER_ORG_INST table. This job record is also called “Substantive Job” record.

Join both the tables and run it as shown below:

As you can see, once we join PS_PER_ORG_INST table with PS_JOB then the SQL looks for the Max effective dated row among those under substantive job record.

So the point is, whether or not we have multiple job records for employees we should always join PS_PER_ORG_INST with PS_JOB to get correct result.

SQL Query to get second highest Max Effective dated (MAX (EFFDT)) row from JOB table

An employee NZ11SN28 has following rows in PS_JOB table:

The second highest max effective dated row is with EFFDT 31-JAN-14 so here is the SQL which will get us this row:

SELECT * FROM PS_JOB J
WHERE J.EMPLID = 'NZ11SN28'
   AND J.EFFDT = (SELECT MAX(J1.EFFDT)
                    FROM PS_JOB J1
                   WHERE J1.EMPLID = J.EMPLID
                     AND J1.EMPL_RCD = J.EMPL_RCD
                     AND J1.EFFDT < (SELECT MAX(J3.EFFDT)
                                       FROM PS_JOB J3
                                      WHERE J3.EMPLID = J.EMPLID
                                        AND J3.EMPL_RCD =    J.EMPL_RCD
                                        AND J3.EFFDT <= SYSDATE)
                  )

   AND J.EFFSEQ = (SELECT MAX(J2.EFFSEQ)
                     FROM PS_JOB J2
                    WHERE J2.EMPLID = J.EMPLID
                      AND J2.EMPL_RCD = J.EMPL_RCD
                      AND J2.EFFDT = SYSDATE)

Lets test this query:

Please also see
Simplified Way to Provide a Page Access in PeopleSoft
PeopleSoft Set Control Field
Adding and Maintaining Person Of Interest in PeopleSoft
Unable to See Future dated transactions in Job Data page in PeopleSoft
Hiring a Person in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft
Understanding Component Interface in PeopleSoft
SQL Query to Find Direct Reports for a Manager in PeopleSoft
How to find the List of Users Assigned to a Role
How the FTE is Calculated in PeopleSoft
Understanding Future dated security in PeopleSoft
How to Resolve a Row Level Security Issue in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft
'Override Position Data' and 'Use Position Data' Option in Job Data Page
How the Various Date Fields on Employment Data Page are Updated
Process Security in PeopleSoft

Want to learn PeopleSoft technical module thoroughly? I have several videos with total duration of over 50 hours.

Following is the link to the YouTube videos Technical
Click here to see course contents

Click here to know how it works

However, if you want to save money by purchasing whole module instead of in parts then visit this page to get more details PeopleSoft Functional and technical online training

Sunday, March 23, 2014

Peoplesoft Row Level Security Search Records

If an organization we are working with is using PeopleSoft as it's ERP solution then depending upon what role we play we do online transactions through various components in PeopleSoft. For example, HR users usually work in Production environment hence use components under Workforce Administration, Organizational Development, Workforce Development, Benefits etc… in order to carry out their daily business.

A Consultant (Technical or Functional) however will mostly works in Non-Production environment hence the components under the menus – Workforce Administration, Set Up HRMS, PeopleTools etc… are the ones where they visits most often to do transactions as required. A consultant at times needs to play various roles in order to expedite certain tasks part of their assignment. For example:

Resolving a production issue by simulating it into Non Prod environment
Validating a test scenario which is part of implementation
Fig-Gap analysis

Etc…

Please also see
Simplified Way to Provide a Page Access in PeopleSoft
Understanding Dynamic prompts in PeopleSoft
Implementing parallel processing using Application Engine in PeopleSoft
Adding and Maintaining Person Of Interest in PeopleSoft
PeopleSoft Set Control Field
Peoplesoft Row Level Security Search Records
SQL Query for Max Effective (MAX (EFFDT) dated row from JOB table
PeopleSoft Set Control Field Concept and Tableset Sharing
Understanding Future dated security in PeopleSoft
PeopleSoft 9.1 Person Model
Creating Query Report with PS Query in PeopleSoft
PeopleSoft HRMS Online Training

If we just forget about the role that one plays and list out few most common components a user deals with to carry out various transactions then the list will contain:

Transactions on Job data

· JOB_DATA (Navigation: Main > Workforce Administration > Job Information > Job Data)

· JOB_DATA_CURRENT (Navigation: Main > Workforce Administration > Job Information > Current Job)

· ADD_PER_ORG_ASGN (Navigation: Main > Workforce Administration > Job Information > Add Additional Assignment)

· ADD_HOST_ASSIGN (Navigation: Main > Workforce Administration > Global Assignments > Track Assignment > Add a Host Assignment)

Etc…

Transactions on Personal data

· PERSONAL_DATA (Navigation: Main > Workforce Administration >Personal Information > Modify a Person)

· DEPEND_BENEF (Navigation: Main > Workforce Administration >Personal Information > Personal Relationships >Dependent Information)

· EMERGENCY_CONTACT (Navigation: Main > Workforce Administration >Personal Information > Personal Relationships > Emergency Contact)

· DISABILITY (Navigation: Main > Workforce Administration >Personal Information > Disabilities)

Etc…

Hold on.. Why am I explaining all this when our topic here is “Row Level Security Search Records in PeopleSoft”?

Well, because all the components mentioned above, use security views as their search record and that is to ensure that a particular user who is doing transactions through these components, can see only those employees that they are allowed to see.I would like to list down those security views along with the components where they are used as search record:

Components for Job data transactions

· Job Data (JOB_DATA) - EMPLMT_SRCH_GBL

· Current Job (JOB_DATA_CURRENT ) - EMPLMT_SRCH_COR

· Add Additional Assignment (ADD_PER_ORG_ASGN ) - PERS_SRCH_GBL

· Add a Host Assignment (ADD_HOST_ASSIGN ) - PERS_SRCH_GBL

Etc…

Components for Personal data transactions

· Modify a Person (PERSONAL_DATA) - PERS_SRCH_ALL

· Dependent Information (DEPEND_BENEF) - PERS_SRCH_GBL

· Emergency Contact (EMERGENCY_CONTACT) - PERS_SRCH_ALL

· Disabilities(DISABILITY) - PERS_SRCH_ALL

Etc…

As you can see, there are various security views created and are used in these components. Based on the component requirement, some of these views search for the employee data rows by EMPLID and EMPL_RCD and others just by the EMPLID.

For example, The view EMPLMT_SRCH_GBL used in Job data component considers EMPL_RCD field while searching the data row when a user hits on the “Search” button in the component search page whereas the view PERS_SRCH_ALL used in Personal Data component doesn’t consider EMPL_RCD filed while searching the data rows.

One easy way to find out the search record for a component, Just open the menu definition under which the component exists, in the Application Designer and locate your component, then right click on it and open the “Menu Item Properties”. Check what the search record for the component is and whether it has been overridden.

So, when the so called “Row Level Security Search Records” come in picture and how they secure the data from a user (HR Admin, Consultant etc…). This is also called Row Level Security in PeopleSoft.

Securing Data rows using Security Search Records (Row Level Security in PeopleSoft)

Let’s take any one of the component mentioned earlier. A new employee “ST01ST20” has been hired in the organization but when HR tries to open their job data through JOB_DATA component:

Oops..! looks like employee doesn’t exist.

Is that what it looks like? No, instead the HR user is not able to see the newly hired employee because they don't have access to them and this restricted access has been enforced with the use of the row level security view EMPLMT_SRCH_GBL as search record in this component.

Please also see
'Override Position Data' and 'Use Position Data' Option in Job Data Page
Hiring a Person in PeopleSoft
How to find the List of Users Assigned to a Role
How the Various Date Fields on Employment Data Page are Updated
Simplified Way to Provide a Page Access in PeopleSoft
How to Resolve a Row Level Security Issue in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft
SQL Query to Find Direct Reports for a Manager in PeopleSoft
Understanding Component Interface in PeopleSoft
How the FTE is Calculated in PeopleSoft
Business Unit, Company and Regulatory Region in PeopleSoft

So let’s find out what exactly happens behind the scene and how the security search view works to ensure the unintended employee data shouldn’t be displayed to HR user.

In the search page, when we enter the EMPLID ‘ST01ST20’ and hit enter:

Hope everything is clear so far and if it is then let’s give data access to the HR user so that they can see the data rows of employee ST01ST20. There are various different ways such access can be given and for now we will just add business unit “AUS01” to the Permission list TEST_PERMSN (Navigation: Main > Set Up HRMS > Security > Core Row Level Security > Security By Permission list).

Note that, this access can also be given through “Security by Dept Tree” component which exists under the same menu. In this method, the HR users are given access to the intended department defined in a Department tree where a particular employee belongs.
After the access is given to HR user let’s see what happens: