Who's Online
0 registered (), 14 Guests and 4 Spiders online.
Key: Admin, Global Mod, Mod
Recent Posts
Ireland and Australia payrolls
by MarinaH
Today at 12:38 AM
PCSPS Pensions 2015
by CT
20/10/14 01:14 PM
OAB Set-up Documents
by benefits99
17/10/14 06:47 AM
Using Checklists, but Person Changes button grey!
by spiro
16/10/14 05:39 PM
RUP7 Payroll
by delboy
15/10/14 01:08 PM
UK OUG HCM SIG - 23rd October, BVP
by delboy
14/10/14 03:00 PM
UK online Payslip - PDF format
by Vigneswar Battu
09/10/14 07:55 PM
Absence Scheme Error
by CT
07/10/14 10:29 AM
Anyone else run Pension Payroll? RTI issue
by delboy
06/10/14 07:36 AM
Task Flow Problem
by SBi
02/10/14 02:35 PM
Top Posters (30 Days)
delboy 5
pat 4
Vigneswar Battu 3
sheebs 3
CT 3
MarinaH 2
PJS 2
BRIAN_D 2
DanC 1
benefits99 1
(Views)Popular Topics
Pension Auto Enrolment 1689155
PAYE RTI 147617
Family Pack K issues thread 46311
CREATE_GRADE api returns:PLS-00306: wrong number o 36424
Enhanced Retro & Release 12 31888
Still trying to locate... 31201
Creating hr jobs ORA-20001: HR_289477_JOB_GROUP_ID 27913
Adding a taskflow button to a form 27302
HR_PF.K RUP4 26148
Review of my Release 12 laptop 23425
Topic Options
Rate This Topic
#2312 - 03/11/06 03:28 PM Formula to calculate absence duration
rakram2 Offline
enthusiast

Registered: 27/06/06
Posts: 39
Hello

I am using the BG_ABSENCE_DUATION formula (copied from TEMPLATE_ABSENCE_DURATION). However this doesn't exclude weekends when the absence duration is calculated. How would I modify the formula to exclude weekends??

Top
#2313 - 03/11/06 09:05 PM Re: Formula to calculate absence duration [Re: rakram2]
CT Online   confused
The Grand Daddy
****

Registered: 11/03/05
Posts: 1712
Loc: P.R.O.P.I.
I believe there is a function GET_WORKING_DAYS, registered for use with formulas, that does exactly this.
_________________________
L&K
CT

Remember: A dog is for life, not just for Christmas... unless you're in Korea

Top
#2314 - 08/11/06 12:00 PM Re: Formula to calculate absence duration [Re: CT]
bcooper Offline

Guru
*****

Registered: 11/03/05
Posts: 1195
Loc: Earth, Europe, England, here
Ah, but what is a working day?
Surely this is legislation specific. Not everybody works on a Friday, and not everybody has Sunday as a non-working day (for example)
_________________________
My Blog

my random musings courtesy of Twitter

My daily technology digest

Top
#2315 - 08/11/06 02:33 PM Re: Formula to calculate absence duration [Re: bcooper]
CT Online   confused
The Grand Daddy
****

Registered: 11/03/05
Posts: 1712
Loc: P.R.O.P.I.
Firstly I lied, the function is actually called COUNT_WORKING_DAYS, and you can find it in the package HR_CAL_ABS_DUR_PKG .

Secondly, I'm not sure if what you say is right about non-working days in other countries. The basis of this function is the resolution of the function TO_CHAR(<date>,'D') the result of this is a number between 1 and 7, but I *think* that depending on certain (registry?) settings, the value for Sunday is 1 or 7 or even 6! Essentially the function only includes a date in the range as a working day if the number is between 2 and 6 inclusive. I'd like to know if anyone out there gets a value when performing that function for a Sunday, that is not 1.

However, what I have had trouble with in the past is public holidays. I seem to remember having to write a variant of the GET_TABLE_VALUE function that interrogated the user defined table 'BANK_HOLIDAYS', and didn't get upset if it found nothing for the given date/country.

The other thing I guess one might need to think about is the effect of shift patterns.

Food for thought, perhaps.


Edited by CT (08/11/06 02:38 PM)
_________________________
L&K
CT

Remember: A dog is for life, not just for Christmas... unless you're in Korea

Top
#2316 - 12/11/06 11:17 AM Re: Formula to calculate absence duration [Re: CT]
rakram2 Offline
enthusiast

Registered: 27/06/06
Posts: 39
Thanks for your response.

I have amended the BG_ABSENCE_DURATION formula as follows, adding the COUNT_WORKING_DAYS function but it still doesn't exclude weekends??


/*
FORMULA NAME: BG_ABSENCE_DURATION
FORMULA TYPE: Quickpaint
DESCRIPTION: Calculates the Employee's Absence
Duration in days or hours. The profile
'HR: Absence Duration Auto Overwrite'
determines if an existing duration value
can change automatically or not.
--
INPUTS:
- days_or_hours: the units of the absence
- date_start: the absence start date
- date_end: the absence end date
- time_start: the absence start time
- time_end: the absence end time
--
DBI Required:
- asg_start_time : the assignment start time
- asg_end_time: the assignment end time
- asg_pos_start_time: the positon start time
- asg_pos_end_time: the position end time
--
Change History
01 Sep 99 jmoyano Created
10 Oct 01 dcasemor end_day was being set to
asg_start_time. Also allowed
hours to be defaulted if no
UOM is set and hours have been
entered.
*/
/* Main Body of Formula */
INPUTS ARE days_or_hours(text),
date_start (date),
date_end (date),
time_start (text),
time_end (text)
/* default values */
DEFAULT FOR days_or_hours IS 'D'
DEFAULT FOR time_start IS '09:00'
DEFAULT FOR time_end IS '17:00'
DEFAULT FOR date_start IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR date_end IS '4712/12/31 00:00:00' (DATE)
/* database items */
DEFAULT FOR asg_start_time IS '09:00'
DEFAULT FOR asg_end_time IS '17:00'
DEFAULT FOR asg_pos_start_time IS '09:00'
DEFAULT FOR asg_pos_end_time IS '17:00'
/* local variables */
error_or_warning = ' '
invalid_msg = ' '
duration = '0'
number_of_days = 0
number_of_days_count = 0
working_days_count = '0'
first_day_hours = 0
last_day_hours = 0
/* Defaults Section */
/* default values for working day, these are only used if no
working conditions can be found */
begin_day = '09:00'
end_day = '17:00'

IF ((date_start WAS DEFAULTED) or (date_end WAS DEFAULTED)) then
duration = '0'
else
(
number_of_days_count = days_between(date_end,date_start)

working_days_count = COUNT_WORKING_DAYS(to_char(date_start,'DD-MON-YYYY'), to_char(number_of_days_count))

number_of_days = to_number(working_days_count)

/* absence in hours */
IF days_or_hours = 'H'
OR (days_or_hours WAS DEFAULTED
AND time_start WAS NOT DEFAULTED
AND time_end WAS NOT DEFAULTED) THEN
(
/* look for the assignment values*/
If ((asg_start_time WAS NOT DEFAULTED) and
(asg_end_time WAS NOT DEFAULTED)) then
(
begin_day = asg_start_time
end_day = asg_end_time
)
else
(
/* look for the position values */
if ((asg_pos_start_time WAS NOT DEFAULTED) and
(asg_pos_end_time WAS NOT DEFAULTED)) then
(
begin_day = asg_pos_start_time
end_day = asg_pos_end_time
)
)
/* compute hours per day */
hours_per_day = ((to_num(substr(end_day,1,2))*60 +
to_num(substr(end_day,4,2))) -
(to_num(substr(begin_day,1,2))*60 +
to_num(substr(begin_day,4,2)))) / 60
/* absence takes place during the same day */
IF number_of_days = 0 THEN
duration = to_char(((to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2))) -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2)))) / 60)
/* more than one day */
ELSE
(
/* Changes for bug3093970 starts here */
first_day_hours =((to_num(substr(end_day,1,2))*60 +
to_num(substr(end_day,4,2))) -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2))) ) / 60

last_day_hours = ((to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2))) -
(to_num(substr(begin_day,1,2))*60 +
to_num(substr(begin_day,4,2))))/60


if first_day_hours <=0
OR first_day_hours > hours_per_day
OR last_day_hours <= 0
OR last_day_hours > hours_per_day THEN
(
/* Leave timings are out off standard timings*/
/* So use 24 hours rule */
first_day_hours = (24*60 -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2))))/60

last_day_hours = (to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2)))/60
)
duration = to_char(first_day_hours+last_day_hours)
duration = to_char(to_num(duration) +
(DAYS_BETWEEN(date_end,date_start) - 1)* hours_per_day)
)
/* Changes for bug3093970 ends here */
)
/* absence in days */
ELSE
(
duration = to_char(DAYS_BETWEEN(date_end,date_start) + 1)
)
/* use of error messages:
if to_num(duration) = 0 then
(
duration = 'FAILED'
invalid_msg = 'HR_ABSENCE_CANNOT_BE_ZERO'
)
*/
)
return duration, invalid_msg

Top
#2317 - 14/11/06 01:59 PM Re: Formula to calculate absence duration [Re: rakram2]
simonbarrow Offline
enthusiast

Registered: 06/10/05
Posts: 37
Loc: Manchester
Hi there,

I think you might actually be looking for the GET_WORKING_DAYS function, into which you pass the relevant start and end dates.

A good tip is to set two local variables as follows:

start_date = greatest(absence_start, period_start)
end_date = least(absence_end, period_end)

If you then pass these into the GET_WORKING_DAYS function, you will always get the number of working days in the absence, relevant to the current processing period.

Cheers

Simon
_________________________
Simon Barrow

Top
#2318 - 14/11/06 02:40 PM Re: Formula to calculate absence duration [Re: simonbarrow]
rakram2 Offline
enthusiast

Registered: 27/06/06
Posts: 39
Thanks

I have replaced COUNT_WORKING DAYS with GET_WORKING_DAYS as follows, but weekends are still not excluded in the absence duration calculation.

I now have:

/*
FORMULA NAME: BG_ABSENCE_DURATION
FORMULA TYPE: Quickpaint
DESCRIPTION: Calculates the Employee's Absence
Duration in days or hours. The profile
'HR: Absence Duration Auto Overwrite'
determines if an existing duration value
can change automatically or not.
--
INPUTS:
- days_or_hours: the units of the absence
- date_start: the absence start date
- date_end: the absence end date
- time_start: the absence start time
- time_end: the absence end time
--
DBI Required:
- asg_start_time : the assignment start time
- asg_end_time: the assignment end time
- asg_pos_start_time: the positon start time
- asg_pos_end_time: the position end time
--
Change History
01 Sep 99 jmoyano Created
10 Oct 01 dcasemor end_day was being set to
asg_start_time. Also allowed
hours to be defaulted if no
UOM is set and hours have been
entered.
*/
/* Main Body of Formula */
INPUTS ARE days_or_hours(text),
date_start (date),
date_end (date),
time_start (text),
time_end (text)
/* default values */
DEFAULT FOR days_or_hours IS 'D'
DEFAULT FOR time_start IS '09:00'
DEFAULT FOR time_end IS '17:00'
DEFAULT FOR date_start IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR date_end IS '4712/12/31 00:00:00' (DATE)
/* database items */
DEFAULT FOR asg_start_time IS '09:00'
DEFAULT FOR asg_end_time IS '17:00'
DEFAULT FOR asg_pos_start_time IS '09:00'
DEFAULT FOR asg_pos_end_time IS '17:00'
/* local variables */
error_or_warning = ' '
invalid_msg = ' '
duration = '0'
number_of_days = 0
get_working_days_count = 0
first_day_hours = 0
last_day_hours = 0
/* Defaults Section */
/* default values for working day, these are only used if no
working conditions can be found */
begin_day = '09:00'
end_day = '17:00'

IF ((date_start WAS DEFAULTED) or (date_end WAS DEFAULTED)) then
duration = '0'
else
(

get_working_days_count = GET_WORKING_DAYS(date_start, date_end)

number_of_days = get_working_days_count

/* absence in hours */
IF days_or_hours = 'H'
OR (days_or_hours WAS DEFAULTED
AND time_start WAS NOT DEFAULTED
AND time_end WAS NOT DEFAULTED) THEN
(
/* look for the assignment values*/
If ((asg_start_time WAS NOT DEFAULTED) and
(asg_end_time WAS NOT DEFAULTED)) then
(
begin_day = asg_start_time
end_day = asg_end_time
)
else
(
/* look for the position values */
if ((asg_pos_start_time WAS NOT DEFAULTED) and
(asg_pos_end_time WAS NOT DEFAULTED)) then
(
begin_day = asg_pos_start_time
end_day = asg_pos_end_time
)
)
/* compute hours per day */
hours_per_day = ((to_num(substr(end_day,1,2))*60 +
to_num(substr(end_day,4,2))) -
(to_num(substr(begin_day,1,2))*60 +
to_num(substr(begin_day,4,2)))) / 60
/* absence takes place during the same day */
IF number_of_days = 0 THEN
duration = to_char(((to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2))) -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2)))) / 60)
/* more than one day */
ELSE
(
/* Changes for bug3093970 starts here */
first_day_hours =((to_num(substr(end_day,1,2))*60 +
to_num(substr(end_day,4,2))) -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2))) ) / 60

last_day_hours = ((to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2))) -
(to_num(substr(begin_day,1,2))*60 +
to_num(substr(begin_day,4,2))))/60


if first_day_hours <=0
OR first_day_hours > hours_per_day
OR last_day_hours <= 0
OR last_day_hours > hours_per_day THEN
(
/* Leave timings are out off standard timings*/
/* So use 24 hours rule */
first_day_hours = (24*60 -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2))))/60

last_day_hours = (to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2)))/60
)
duration = to_char(first_day_hours+last_day_hours)
duration = to_char(to_num(duration) +
(DAYS_BETWEEN(date_end,date_start) - 1)* hours_per_day)
)
/* Changes for bug3093970 ends here */
)
/* absence in days */
ELSE
(
duration = to_char(DAYS_BETWEEN(date_end,date_start) + 1)
)
/* use of error messages:
if to_num(duration) = 0 then
(
duration = 'FAILED'
invalid_msg = 'HR_ABSENCE_CANNOT_BE_ZERO'
)
*/
)
return duration, invalid_msg

Top
#2319 - 14/11/06 03:14 PM Re: Formula to calculate absence duration [Re: rakram2]
simonbarrow Offline
enthusiast

Registered: 06/10/05
Posts: 37
Loc: Manchester
Hello there

The above code doesn't actually return the result of the get_working_days function, it simply populates get_working_days_count with the outcome, and then carries on using the seeded calcs.

A good start would be to change the final criteria clause:

/* absence in days */
ELSE
(
duration = to_char(DAYS_BETWEEN(date_end,date_start) + 1)
)
/* use of error messages:
if to_num(duration) = 0 then
(
duration = 'FAILED'
invalid_msg = 'HR_ABSENCE_CANNOT_BE_ZERO'
)
*/
)


to the following:


/* absence in days */
ELSE
(
duration = to_char(GET_WORKING_DAYS(date_start,date_end) + 1)
)
/* use of error messages:
if to_num(duration) = 0 then
(
duration = 'FAILED'
invalid_msg = 'HR_ABSENCE_CANNOT_BE_ZERO'
)
*/
)


Note that the order of the input values has changed to what is required in the formula function definition.

You will also need to go through the rest of the criteria and get rid of any that will never be satisfied, and change the rest to use the function..

Absences can be pretty tricky, and often using the formulae created by the wizards can produce a whole lot of code which may not be useful to your particular scenario. If your absence requirement is fairly simple, then I would consider writing the formula yourself- again, this can be a lot less confusing than understanding and editing an exising one..

Cheers

Simon
_________________________
Simon Barrow

Top
#2320 - 15/11/06 03:17 PM Re: Formula to calculate absence duration [Re: simonbarrow]
rakram2 Offline
enthusiast

Registered: 27/06/06
Posts: 39
Hello Again

You will not be surprised when I tell you it still doesn't work! We are using hours rather then days so I now have:

/*
FORMULA NAME: BG_ABSENCE_DURATION
FORMULA TYPE: Quickpaint
DESCRIPTION: Calculates the Employee's Absence
Duration in days or hours. The profile
'HR: Absence Duration Auto Overwrite'
determines if an existing duration value
can change automatically or not.
--
INPUTS:
- days_or_hours: the units of the absence
- date_start: the absence start date
- date_end: the absence end date
- time_start: the absence start time
- time_end: the absence end time
--
DBI Required:
- asg_start_time : the assignment start time
- asg_end_time: the assignment end time
- asg_pos_start_time: the positon start time
- asg_pos_end_time: the position end time
--
Change History
01 Sep 99 jmoyano Created
10 Oct 01 dcasemor end_day was being set to
asg_start_time. Also allowed
hours to be defaulted if no
UOM is set and hours have been
entered.
*/
/* Main Body of Formula */
INPUTS ARE days_or_hours(text),
date_start (date),
date_end (date),
time_start (text),
time_end (text)
/* default values */
DEFAULT FOR days_or_hours IS 'D'
DEFAULT FOR time_start IS '09:00'
DEFAULT FOR time_end IS '17:00'
DEFAULT FOR date_start IS '0001/01/01 00:00:00' (DATE)
DEFAULT FOR date_end IS '4712/12/31 00:00:00' (DATE)
/* database items */
DEFAULT FOR asg_start_time IS '09:00'
DEFAULT FOR asg_end_time IS '17:00'
DEFAULT FOR asg_pos_start_time IS '09:00'
DEFAULT FOR asg_pos_end_time IS '17:00'
/* local variables */
error_or_warning = ' '
invalid_msg = ' '
duration = '0'
number_of_days = 0
get_working_days_count = 0
first_day_hours = 0
last_day_hours = 0
/* Defaults Section */
/* default values for working day, these are only used if no
working conditions can be found */
begin_day = '09:00'
end_day = '17:00'

IF ((date_start WAS DEFAULTED) or (date_end WAS DEFAULTED)) then
duration = '0'
else
(

get_working_days_count = GET_WORKING_DAYS(date_start, date_end)

number_of_days = get_working_days_count

/* absence in hours */
IF days_or_hours = 'H'
OR (days_or_hours WAS DEFAULTED
AND time_start WAS NOT DEFAULTED
AND time_end WAS NOT DEFAULTED) THEN
(
/* look for the assignment values*/
If ((asg_start_time WAS NOT DEFAULTED) and
(asg_end_time WAS NOT DEFAULTED)) then
(
begin_day = asg_start_time
end_day = asg_end_time
)
else
(
/* look for the position values */
if ((asg_pos_start_time WAS NOT DEFAULTED) and
(asg_pos_end_time WAS NOT DEFAULTED)) then
(
begin_day = asg_pos_start_time
end_day = asg_pos_end_time
)
)
/* compute hours per day */
hours_per_day = ((to_num(substr(end_day,1,2))*60 +
to_num(substr(end_day,4,2))) -
(to_num(substr(begin_day,1,2))*60 +
to_num(substr(begin_day,4,2)))) / 60
/* absence takes place during the same day */
IF number_of_days = 0 THEN
duration = to_char(((to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2))) -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2)))) / 60)
/* more than one day */
ELSE
(
/* Changes for bug3093970 starts here */
first_day_hours =((to_num(substr(end_day,1,2))*60 +
to_num(substr(end_day,4,2))) -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2))) ) / 60

last_day_hours = ((to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2))) -
(to_num(substr(begin_day,1,2))*60 +
to_num(substr(begin_day,4,2))))/60


if first_day_hours <=0
OR first_day_hours > hours_per_day
OR last_day_hours <= 0
OR last_day_hours > hours_per_day THEN
(
/* Leave timings are out off standard timings*/
/* So use 24 hours rule */
first_day_hours = (24*60 -
(to_num(substr(time_start,1,2))*60 +
to_num(substr(time_start,4,2))))/60

last_day_hours = (to_num(substr(time_end,1,2))*60 +
to_num(substr(time_end,4,2)))/60
)
duration = to_char(first_day_hours+last_day_hours)
duration = to_char(to_num(duration) +
(GET_WORKING_DAYS(date_start,date_end) - 1)* hours_per_day)
)
/* Changes for bug3093970 ends here */
)
/* absence in days */
ELSE
(
duration = to_char(DAYS_BETWEEN(date_end,date_start) + 1)
)
/* use of error messages:
if to_num(duration) = 0 then
(
duration = 'FAILED'
invalid_msg = 'HR_ABSENCE_CANNOT_BE_ZERO'
)
*/
)
return duration, invalid_msg

Top



Moderator:  CT, delboy 
Forum Stats
1092 Members
48 Forums
1923 Topics
10236 Posts

Max Online: 68 @ 15/08/12 05:43 AM
Today's Birthdays
No Birthdays
Recent vacancies
Top Posters
CT 1712
bcooper 1195
delboy 964
Geoff Dixon 382
SBi 370
paulgos 352
Gus 245
vkumar 223
kp_rapolu 213
cbrookes 197
October
Su M Tu W Th F Sa
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31