Title: I Have A Good One!
Description: Tracking Emp Status changes
Mil0n023 - November 30, 2006 05:12 PM (GMT)
How can I build a report that will show an Employee's EMPLOYEE.EMP_STATUS history incorporating the corresponding Pay Periods?
So thats: I want to see all the employee EMP_STATUS history with Pay Periods (dates)
Is EMP_STATUS referenced in the HRHISTORY, PERACTHIST? I can't seem to find it - field # ?? Not sure what that is - or if it's possible.
FYI: we do have History for Status tracked - HR10 History is set up.
Anyone, Anyone?? :blink: :blink:
mnye - November 30, 2006 05:39 PM (GMT)
Ive never done this but taking a quick look in th EMSTATUS table, there is an Active flag field and a date stamp. You may want to play around with that. It only links back to Employee, but you could do a join to your PRTIME table using ranges on the pay period end data and date stamp.
hth,
matt
mnye - November 30, 2006 05:49 PM (GMT)
Clarification
| QUOTE |
| It only links back to Employee, but you could do a join to your PRTIME table |
It only links back to the EMPLOYEE and PRSYSTEM files from the OLE DB Query Builder but you could link back to PRTIME table using SQL.
Im assuming you are on 8.x apps?
Mil0n023 - November 30, 2006 05:52 PM (GMT)
yeah 8.03
Is there a field number for EMP Status? I can maybe use the HRHISTORY?
cjmart - November 30, 2006 05:54 PM (GMT)
You'll definitely need to reference emp status changes in HRHISTORY. The field number is 20.
mnye - November 30, 2006 05:54 PM (GMT)
what do you mean by field number?
cjmart - November 30, 2006 05:57 PM (GMT)
You have to reference the field number to determine what the HRHISTORY changes are for. PADICT/PADICT2 give a description of what each field number stands for.
cjmart - November 30, 2006 05:58 PM (GMT)
Forget to mention that field number is FLD_NBR in HRHISTORY
cjmart - November 30, 2006 06:10 PM (GMT)
Sample oracle sql query that will give you the emp name, historical status, description of historical status (be aware that this is only giving the current status description for the historical status code), status effective date, etc...
| CODE |
select e.employee, e.last_name, e.first_name, h.a_value as status_hist, s.description as status_descr_hist, h.beg_date
from law_bld.hrhistory h,
law_bld.employee e,
law_bld.emstatus s
where h.company = e.company
and h.employee = e.employee
and h.company = s.company
and h.a_value = s.emp_status
and h.fld_nbr = 20
order by h.employee, h.beg_date desc |
shanesmj - December 11, 2006 06:45 PM (GMT)
Is there a document that will tell me all of the FLD_NBRs for the table????
Shane
cjmart - December 11, 2006 07:04 PM (GMT)
The only doc I can find (Article ID: 74729) instructs you to dump/review PADICT. Take a look at this table (and PADICT2). It's pretty easy to identify the field number for a specific field by doing this.
SAH - December 11, 2006 08:49 PM (GMT)
Here's a zip file of employee fields (version 8.1). Have fun! If you have any issues opening it, feel free to e-mail me.
shanesmj - December 14, 2006 08:22 AM (GMT)
Here is a question....
What is the field number for the Supervisor employee... Or in other words the employee that is assigned as the supervisor on HR07.....
Thanks
Shane
SAH - December 14, 2006 10:37 AM (GMT)
We're version 8.1 - I show 721