Meeting Topic: Queries
Meeting Date: June 13, 2017
Meeting Facilitator(s): Brian Lanier
WebEx Recording: Due to the personal information available within this Listen & Learn remediation session, the recording is being hosted in the Community of Practice Canvas course. This is in keeping with our FERPA commitment. All SSN/Bank Data are redacted. Please contact Jo Munroe for viewing access.
Key HCM Module Involved
Remediation Ticket Information
Topic: Queries requested by Spokane
Brian: when we went back looking through remediation items, we found a list of queries and reports from Spokane and we want to go over this list together and see if we’re on the same page
First one I see from Spokane, is to write query for specific values for PTF eligibility. Can you give more information on this?
Melody: We want it for leave too.
Brian: So if we get a box for leave that will work?
Melody: Yes, so long as we can get the information out.
Brian: So if we could give you a query that pulls the data from that box. Like all contracts that are Benefit Eligible.
Melody: What else do you want from that query (internal question)?
(Did not provide any other fields.)
Brian: Tacoma, so you want a Benefit and Leave box. And then once they are made you’ll get a query.
Next one is tenure tracking…
Melody: We have faculty on tenure, and we’re requesting additional training on the tenure tracking since it has no effective dating and they have an issue with that. This is also looked at to pull IPEDS and since there’s no effective row dating you have to be careful to pull the IPEDS information.
This last year it was saying an adjunct was on tenure track. They used to be an adjunct but they had gotten onto the tenure track.
Brian: We’ll try to look at this and see if we can provide a solution.
Melody: We also have bad data inside Tenure data which needs deletion.
Brian: So we need to clean it up and to make sure it functions correctly.
Tacoma, what are you doing with this item at this time?
AP: I haven’t run into a big problem with this.
Brian: Are you tracking it in PeopleSoft?
AP: I do it under Faculty Events in workplace development. It’s mostly manual input.
Melody: So what do you do when they retire and return as an adjunct?
AP: That is something I haven’t run into yet. I like to keep the information, but it hasn’t rolled up into things yet.
Melody: It will impact your IPEDS data.
AP: I’m not saying it is an issue, I’m just saying I haven’t run into an issue.
Jameeka: If you change the track start date you can control the date in the system.
Melody: How do I retrieve history from this. We need to be able to see more detail.
Jameeka: I think you’re right we need to have a separate session for that. We’ll address it at that time.
Brian: Ok next one is document PTF benefits, and I see that as the E-031 fix.
Melody: I think you’re right.
Brian: So next one is copying forward one term to the next is causing issues?
Melody: Yes, but we have been having Sam copy things forward so I can’t speak to if this a new or existing error.
Brian: We’ll check with Sam.
The next one is college service dates. So could you all go into more detail?
Melody: We want seniority date and RIFF date for layoff. We also want to determine longevity. We can’t figure out longevity for an individual.
Debbie: The seniority date in the labor screen. We know the data needs to be cleaned up. There’s no query for longevity.
Melody: We need to be able to manage our RIFF list. We have a lot of other needs related to this information.
Brian: I do remember this screen, and we don’t have this built into PeopleSoft. We’ll look into it and get back to you.
Debbie: I’ve tried that report, no data. We also get verifications of employment we have to complete, 10-20 in a week, and we don’t have anywhere to go look at their part time dates and their full time dates.
Melody: So we’ll probably have to clean up our data.
Brian: Melody is right. Tacoma, are you having the same problem?
Janet: Yes, we’re having similar issues.
Brian: So next is need FTE values for hourly and adjunct employees.
Melody: Every single person with adjunct and hourly all came over at conversion with 40 hours/1 FTE.
We talked with Carmen and they were taking the FTE in HCM and the classes in CS and dividing it with the HCM FTE. We said that the adjunct should come from FWL and that the rest are in HCM.
We want a query for employees as hours assigned and FTE.
Brian: So query by empl class and job code that will pull hours per week and FTE.
Next one was the Job Data SQL issue.
Melody: We resolved that.
Brian: History one…. Reports to…
Melody: So because we’re not using reports to function (because it caused a problem in absence management). We have such a volume of turnover any time someone leaves that’s a supervisor then we wouldn’t need a manual solution for each manual change. This isn’t efficient.
Jameeka: So, with the reports to we would need people on full position management. Everyone would need a position number. All supervisors probably have a position number, but those reporting to them don’t all necessarily have it. Need to do some testing to see if there’s an easier way. Will probably have questions as she goes along.
Brian: Would reports to be something you’d be interested in…. everyone having a position number?
Melody: In legacy all PTH and all CLA, EXP, FAC had a number. We just weren’t sure what to do with PTF at the time. We’re interested in more position numbers.
Brian: So you’re interested. Tacoma are you interested?
AP: I want to say yes, but I need to know a little more about position management and it’s use.
Brian: We can get you more information. But yes, reports to is attached to position management. We would need all colleges on board.
Melody: We’re already there partway. When you’re in position control and look at position management, all it shows what’s tied to job data. Is there a report that will give the history of the position control?
Josie: History isn’t stored in position management, you can pull that from Job Data by position number.
Melody: We want the full chart string in Job Data as well.
Josie: Chart string is difficult to pull as one report. We can look at improving the efficiency of what you pull now, but it will probably always be multiple reports.
Melody: We have to manually go in and override the automatic salary. The autostep increase isn’t working right. Comp rate doesn’t look at % of FT.
Brian: Yes, we’re aware of this. We can’t really change delivered, but we might be able to look at what other way we could help you. Or give you a query to identify who you’re concerned about. We could give you a query with employees less than 1 FTE? We would probably have to make a custom process.
Melody: We would like a custom process.
Brian: Can a better query help you in the time being?
Melody: Debbie already has a query she runs.
Brian: I will add it to the list to see what we can do. We have been aware this is a problem since go live.
Debbie: I did respond with some print screen since I ran the progression report today and it clearly says on Job Data what is going wrong with the step increase.
Brian: Tacoma, are you having the same issue? How are you dealing with it?
Mark: Yes, when we run the process we have an analyst go over the information and then manually fix it. We don’t have many non 100% FTE people.
Brian: This is about conversion using wrong increment month.
Debbie: So they brought in legacy screen 20 pay history screen, but it took the last increment and made it the employees increment month. The increment month wasn’t right for CLA, and we changed them individually. We cleaned it up, but it’s a warning for the future.
Jameeka: Thank you.
Brian: Next one is probation action reasons.
Jameeka: So there have been cases when there’s a probation start/end date in Job Data, and if you add a new effective dated row the system runs a batch process and if you don’t address that date in Job Data it will still pick up that date and enter a termination line etc. whatever goes with the probation line.
Debbie: We noticed this suddenly in Job Data for people who had passed their probationary period. So when I put someone’s probationary end date on the CTC_DATA screen, if that date is happening 6/19 then something needs to be extended….
Melody: I’d like a time devoted to Action/Reason Job Data discussion. We need to know what impacts what. We want to go through them all and talk about when they should be used and when they shouldn’t.
Debbie: When we do hire someone and put them in probationary period, we need to know it won’t be an issue in that screen. If we use project status will that impact them? We need reports pulling people who are in various different statuses. (Query for probationary periods, etc. probably needed)
Jameeka: Tacoma needs to spark my memory, I remember the automatic end being an issue. So far as the actions/reasons we haven’t talked about action/reason system impact. If you can look at the list that’s out there already, if you identify a few you have questions on we can go case by case.
Melody: I won’t get to that any time soon. There’s too much going on here.
Brian: The citizenship page/Visa information. Have you had a chance to look at the QRG and run the query?
Debbie: I have not yet run the query.
Brian: Next one was Visa expiration date, did you have a chance to look at that?
Jameeka: That is the same query.
Brian: Next one is about labor and contract administration. We’re learning as we go and we’ll have something to show you after we learn it a little better.
Next one is a report for veterans. Debbie can you tell us more about what you’re looking for?
Melody: I think we need to check this again, I think this is older.
Debbie: I remember there being a lot of information on there.
Melody: We addressed this locally already. They can pick codes in in self service that we don’t report on. We want fewer options. Ethnicity, leave codes, vet statusall in the same boat.
Brian: We share ethnicity and vetran tables with CS and Campus Solutions wants all the codes.
Melody: Can someone talk to them as ask them if they really need it all.
Brian: We can talk to CS and see what they say. We have talked to them once before about this, and they already said they wanted all the ethnicity codes.