PS Query & SQL

Does anyone have/use a record in HCM that is populated with future-dated JOB data, but doesn't auto-join any security records?

At CU, our future-dated JOB data rows cannot be queried from the JOB record. (OK, sometimes that isn't the case, but for newly hired employees it usually is). They only become available on JOB once the current date exceeds the Effective Date of the first row. Therefore, our DBAs have created a view-record that only returns future-dated rows in JOB. When we create queries, we often include JOB on the Top Level and the view-record on Union 1 to capture all of the existing data.

The problem: both JOB and JOB_FUTR_VW auto-join to PSQuery's security record(s). That usually limits returned data to just the Dept that entered the rows. And if an employee is active in more than one Job in a different Depts concurrently (often the case at CU) none of the Depts can see each other's data.

We have another workaround view-record that includes 88 fields from the JOB record but doesn't auto-join to any security records. Great. Everybody can see everything here. But we don't have anything similar for future-dated rows in JOB. We are currently reviewing options for filling this void.

I'm curious to know about your experience. How is your system set up? Do you also have these issues? What solution(s) have you implemented?

------------------------------
Scott Frey
Connected Campus CoP Member - Senior Data Analyst-HR Info Systems
University of Colorado System
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Hi Scott

We are not a Peoplesoft HCM school, and don't use the JOB table, so I can not be of help in that regard.  I do, however, have a possible solution for the auto-join issue.  When you create and save a query, you have the option to save the Query as one of 4 different Query Types.  Usually queries are saved as User queries - this is the default value, and works well for most situations.  

User Query Type choices

If you select Process Type, however, there is an interesting Query Manager result.  Your query will NOT auto-join to the security tables.  You will be able to query all rows, with whatever criteria you determine.  This is quite helpful in queries used by various processes.  

Process Query Type

The one thing you have to remember about using Process type queries is that the Search Page for Query Manager also uses a default value of User type.  This means when you search for your Process queries, you have to use the Advanced Search, and add the Query Type of Process. 

Use Advanced Search

Good Luck! 

------------------------------
Tom Johnson
Senior Director, Student Information and Systems Design
University of Nevada-Las Vegas
thomas.johnson@unlv.edu
"None of us is as smart as all of us"
------------------------------

Hi Scott,

I work on the CS side, but we have access to the JOB table and experience the same issues you describe. If I'm building a query for myself or for someone with access to the Process query type, I do what Tom suggested and create a Process type query.

For queries where the end user only has access to the User query type, I use a custom view record that our central OIT created. It only has 19 fields and excludes any that would be considered sensitive. For our purposes, we usually only need to know whether an employee is active or inactive and what their job code and/or deptID is, so the reduced number of fields in the custom view record works ok for us. It has no baked in security or effective date logic, so it will bring back those future dated new hires.  

Best regards,

Dan

------------------------------
Dan Delaney
Business/Systems Analyst
University of Minnesota
------------------------------
------------------------------