Who's Online
0 registered (), 8 Guests and 5 Spiders online.
Key: Admin, Global Mod, Mod
Recent Posts
Learning Management Overall Process in any ERP Sys
by tech_girl123
26/08/14 02:09 PM
BACS limit
by Vigneswar Battu
22/08/14 01:06 PM
Process Flow Diagrams For Oracle Learning Mgmt
by tech_girl123
22/08/14 08:38 AM
UK tax-payers (not just contractors): Please read
by CT
22/08/14 08:12 AM
Tables related to Insurance Provider details etc
by CT
18/08/14 02:16 PM
RTI Earlier Year Updates
by delboy
13/08/14 02:49 PM
Available from Sept-2014
by SBi
09/08/14 05:00 PM
Security based views in Self Service
by MAL
07/08/14 06:08 PM
Rollup Patching for 12.1.3 and 12.0.6
by delboy
06/08/14 05:26 AM
OAB Set-up Documents
by Vigneswar Battu
05/08/14 04:18 PM
Top Posters (30 Days)
delboy 7
CT 4
Vigneswar Battu 3
tech_girl123 2
DonnaOracleHCM 1
Phani 1
MAL 1
Marina 1
SBi 1
BRIAN_D 1
(Views)Popular Topics
Pension Auto Enrolment 1529183
PAYE RTI 129581
Family Pack K issues thread 44872
CREATE_GRADE api returns:PLS-00306: wrong number o 34089
Enhanced Retro & Release 12 30214
Still trying to locate... 30119
Creating hr jobs ORA-20001: HR_289477_JOB_GROUP_ID 26590
Adding a taskflow button to a form 25399
HR_PF.K RUP4 24623
Review of my Release 12 laptop 22141
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 Offline
The Grand Daddy
****

Registered: 11/03/05
Posts: 1704
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: 1194
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 Offline
The Grand Daddy
****

Registered: 11/03/05
Posts: 1704
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
1078 Members
48 Forums
1910 Topics
10171 Posts

Max Online: 68 @ 15/08/12 05:43 AM
Today's Birthdays
No Birthdays
Recent vacancies
Available from Sept-2014
by SBi
09/08/14 05:00 PM
Top Posters
CT 1704
bcooper 1194
delboy 949
Geoff Dixon 382
SBi 369
paulgos 350
Gus 245
vkumar 223
kp_rapolu 213
cbrookes 197
August
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