|
United Fire Group went live on Vista 3.2 on October 1, 2006. Prior to this time we were using software from another vendor and we converted everything over. Being a relatively new user, I attended the User Group Conference in Salt Lake City. For anyone who hasn't yet had the opportunity, I highly recommend them. At the conference this past year, PDS offered Sunday workshops. One of the workshops I attended was “Vista Reporting” with Marie Killeen and Dwight White. Due to Marie's amazing ability to traverse the room in a single bound, I decided to stay awake and keep an eye on her. This ended up being a really good decision and I went away from this workshop with ideas in my head.
When I got back from the User Group Conference I started looking at back issues of the newsletters and I found the goldmine. (An archive of past articles is available on the UGA website.) Using the information from the workshop and the information from the article by Marco Padovani in the June 2006 newsletter, I was able to give our employees a report they had been asking for since our conversion to Vista .
I need to give a little background on this. In order to provide our employees with a little “mental stimulation”, we use a unique approach to vacation accrual and carryover.
Our employees accrue vacation on a monthly basis based on their years of service and their weekly scheduled hours. At annual enrollment time, our employees are also allowed to “purchase” an additional amount of vacation time, equal to or less than one week of scheduled hours.
“Purchased” vacation must be used prior to “accrued” vacation time and it must be used in the year it is purchased for. It cannot be carried over. All vacation accrued during the year, plus an amount equal to one week of scheduled hours accrued in the prior year can be carried over into the following year.
It's not too hard to figure out how an employee might get a little confused.
I needed a report for each employee to show them how much vacation time they had remaining and how much vacation time that they would need to use prior to the end of the year to help them avoid losing any vacation time.
This would require writing a Crystal report. To write this report, I decided that I needed to use views. One of the views I used was the PDS view, “emp_leave_accruals”. The other view I would need had to be created as follows:
CREATE VIEW dbo.ufg_vacation_remaining (
context_user_id
,person_id
,company_code
,first_name
,last_name
,hr_is_active
,hr_status_date
,accrued_vacation
,purchased_vacation
,carryover_allowed)
AS
--Value of accrued vacation and purchased vacation could return nulls on new employees --because "ac1.taken" will be null until they actually take some vacation.
SELECT ep.context_user_id
,ep.person_id
,ep.company_code
,ep.first_name
,ep.last_name
,ep.hr_is_active
,ep.hr_status_code
,ISNULL((SELECT ac1.accum_accrued
- ISNULL (ac1.taken,0)
- (SELECT ISNULL(SUM(hours),0)
FROM timept tp1
WHERE tp1.person_id = ac1.person_id
AND tp1.company_code = ac1.company_code
AND tp1.earn_code = '005'
AND tp1.status_code in ('A','S','N'))
- (SELECT ISNULL(SUM(hours),0)
FROM timeth th1
WHERE th1.person_id = ac1.person_id
AND th1.earn_code = '005')
FROM empaccrl ac1
WHERE ac1.accrual_code = 'VACATION'
AND ac1.person_id = ep.person_id
AND ac1.company_code = ep.company_code),0)
,ISNULL((select ac1.accum_accrued
- ISNULL(ac1.taken,0)
- (SELECT ISNULL(SUM(hours),0)
FROM timept tp1
WHERE tp1.person_id = ac1.person_id
AND tp1.company_code = ac1.company_code
AND tp1.earn_code = '009'
AND tp1.status_code in ('A','S','N'))
- (SELECT ISNULL(SUM(hours),0)
FROM timeth th1
WHERE th1.person_id = ac1.person_id
AND th1.earn_code = '009')
FROM empaccrl ac1
WHERE ac1.accrual_code = 'PURVAC'
AND ac1.person_id = ep.person_id
AND ac1.company_code = ep.company_code),0)
,CASE
WHEN (DATEDIFF (yy,seniority_date,('12/31/' + CAST(datepart(yyyy,GETDATE()) AS CHAR(4)))))
> 24
THEN ROUND(((ep.normal_hours/5) * 31),2)
WHEN (DATEDIFF (yy,seniority_date,('12/31/' + CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)))))
BETWEEN 14 AND 25
THEN ROUND(((ep.normal_hours/5) * 26),2)
WHEN (DATEDIFF(yy,seniority_date,('12/31/' + CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)))))
BETWEEN 6 AND 13
THEN ROUND(((ep.normal_hours/5) * 21),2)
WHEN (DATEDIFF (yy,seniority_date,('12/31/' + CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)))))
BETWEEN 4 AND 5
THEN ROUND(((ep.normal_hours/5) * 16),2)
WHEN (datediff (yy,seniority_date,('12/31/' + CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)))))
< 4
THEN ROUND(((ep.normal_hours/5) * 15),2)
ELSE 0
END
FROM emp_person_company ep
--INNER JOIN sec_options ON sec_options.options_id = 54996
WHERE ep.hr_is_active = 'Y'
When I put the crystal report together, each of the numbers shown on the report came from one of these views. The resulting report provides the employee with “up to the minute” numbers. This takes into account all vacation that the employee has entered into the time entry of the Vista system, regardless of whether it is sitting in there as a new entry, submitted, approved, or processed. I added the parameter “LOGGEDINPERSONID” to the crystal report and used the selection criteria of emp_leave_accruals.accrual_code is equal to VACATION and emp_leave_accruals.person_id is equal to {?LOGGEDINPERSONID}
I then went into report maintenance of Vista , and added this report to my “ Payroll Self Service” group and assigned security.
When the employee accesses this report the following is an example of what they see. No more guessing and trying to calculate these numbers on their own.
Zondra Hopkins
Payroll/HRIS Manager
United Fire Group
|
