I have tried unsuccessfully to create an employee transfer report in crystal. Does anyone know of a report or an easy way to create a crystal report that would be easily readable? The report I created is very complex and I still don't get the data I want. I only want when an employee transfers depts or jobs. :banghead:
Look for FLD_NBR equal to 15 and 19 in HRHISTORY.
Department changes for Oracle...
| CODE |
select e.employee, e.last_name, e.first_name, h.a_value as dept_hist, d.r_name as dept_descr_hist, h.beg_date as dept_chg_date
from hrhistory h, employee e, deptcode d
where h.company = e.company
and h.employee = e.employee
and h.company = dcompany
and d.a_value = d.department
and h.fld_nbr = 15
order by h.employee, h.beg_date desc |
Look closely at your data...you will need to add an additional filter to restrict records where the change date (beg_date) is not equal to the employee's hire or rehire date (to restrict the records to transfers only).
In trying to differentiate between transfers and new hire/rehire records, it may easier to use the PERSACTHST table (instead of HRHISTORY) if your implemention utilizes Personnel Action Codes for new hires. PERSACTHST also uses FLD_NBR to differentiate the type of change.
If you're trying to identify the previous values (ie old dept nbr/name), I would recommend taking advantage of the procedural language for your rdbms (ie pl/sql, t-sql, etc). I attached some old pl/sql code I once used to do this (and many other things). I scheduled this using dbms_job (scripts also attached) to run overnight after Personnel Actions were processed, which allowed us to let Oracle do the heavy lifting off-hours. The results were inserted into a db table that was used as a data source for reporting. I know there has to be a better way to do this (I was new to PL/SQL at the time), I just haven't had a need to do this kind of reporting since then...
If you prefer to use OLEDB, I believe that there may be a sample dme query in the Smart Notes HR Solutions Pack from Lawson. I know that there is a new hire dme query in there.