Who's Online
0 registered (), 9 Guests and 14 Spiders online.
Key: Admin, Global Mod, Mod
Newest Members
Bobin, DJP, Windswept, Shehzad, Hugo Santos
1163 Registered Users
Recent Posts
Canadian HR & Payroll
by Vigneswar Battu
01:52 PM
Pension Auto Enrolment
by Kate
10:43 AM
2016/17 Tax Year Legislative Patch and Apprentices
by CT
06:51 PM
Dynamic Triggers in R12.2
by pat
01:25 PM
Balance Dimension _ASG_TD_YTD
by Gus
12:46 PM
PAYE and NI payments to HMRC
by paulgos
10/02/16 11:28 AM
Output Default Options on Concurrent Programs
by Vigneswar Battu
10/02/16 10:32 AM
NI included in Retro Enhanced
by CT
09/02/16 03:28 PM
System Extract / Generic Payroll Interface Toolkit
by PeterP
08/02/16 01:55 PM
Retro Pay with Overlap
by paulgos
08/02/16 11:14 AM
Top Posters (30 Days)
delboy 18
Vigneswar Battu 17
paulgos 14
CT 14
pat 7
bcooper 4
pxlewis 3
Sandy 3
BRIAN_D 2
Kate 2
(Views)Popular Topics
Pension Auto Enrolment 3,147,067
PAYE RTI 270,500
New PCSPS regime for 2015 - Alpha, Compendia etc 66,118
Family Pack K issues thread 58,818
CREATE_GRADE api returns:PLS-00306: wrong number o 48,824
Enhanced Retro & Release 12 46,978
Still trying to locate... 41,573
Creating hr jobs ORA-20001: HR_289477_JOB_GROUP_ID 39,634
Adding a taskflow button to a form 39,042
HR_PF.K RUP4 37,264
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   sleepy
The Grand Daddy
****

Registered: 11/03/05
Posts: 1990
Loc: People's Republic of Portsea 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


*****

Registered: 11/03/05
Posts: 1237
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   sleepy
The Grand Daddy
****

Registered: 11/03/05
Posts: 1990
Loc: People's Republic of Portsea 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
1,163 Registered Members
48 Forums
2,057 Topics
11,196 Posts

Most users ever online: 68 @ 15/08/12 04:43 AM
Today's Birthdays
No Birthdays
Recent vacancies
Top Posters
CT 1990
bcooper 1237
delboy 1107
SBi 393
paulgos 387
Geoff Dixon 382
Gus 252
Vigneswar Battu 237
pat 227
vkumar 223
February
S M T W T F S
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