Who's Online
0 registered (), 7 Guests and 16 Spiders online.
Key: Admin, Global Mod, Mod
Newest Members
tr_mid, Brian Allen, PaulD, Mr Baig, niyamad
1194 Registered Users
Recent Posts
fnd_message.get is truncating err String in WebADI
by KIRTI_MOHAN
Yesterday at 05:11 PM
Off Payroll Working in Public Sector - Tech Note
by SBi
Yesterday at 09:52 AM
Rollback of "Magnetic Report" not working Properly
by CT
Yesterday at 09:39 AM
Available Soon
by delboy
22/08/16 05:34 PM
Update Assignment API
by delboy
15/08/16 04:40 AM
OSP - Employee moves to new OSP scheme.
by hpatel15
09/08/16 11:31 AM
Creating New segments in Key Flexfields
by delboy
03/08/16 04:36 AM
GB OSP Plans
by hpatel15
27/07/16 11:11 AM
Rename Security Group via Application
by Jay Kavia
26/07/16 11:33 AM
Creating New Segments for Key Flexfields
by delboy
25/07/16 09:14 AM
Top Posters (30 Days)
delboy 13
CT 6
hpatel15 3
bcooper 3
KIRTI_MOHAN 2
SBi 1
pat 1
pxlewis 1
Vigneswar Battu 1
Murali 1
(Views)Popular Topics
Pension Auto Enrolment 3,433,877
PAYE RTI 318,345
New PCSPS regime for 2015 - Alpha, Compendia etc 89,129
Family Pack K issues thread 64,805
CREATE_GRADE api returns:PLS-00306: wrong number o 53,172
Enhanced Retro & Release 12 52,068
Still trying to locate... 45,955
Creating hr jobs ORA-20001: HR_289477_JOB_GROUP_ID 45,227
Adding a taskflow button to a form 43,750
HR_PF.K RUP4 41,205
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   content
The Grand Daddy
****

Registered: 11/03/05
Posts: 2013
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: 1248
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 daily technology digest

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

Registered: 11/03/05
Posts: 2013
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,195 Registered Members
48 Forums
2,107 Topics
11,455 Posts

Most users ever online: 68 @ 15/08/12 04:43 AM
Today's Birthdays
No Birthdays
Recent vacancies
Available Soon
by delboy
22/08/16 05:34 PM
Top Posters
CT 2013
bcooper 1248
delboy 1170
paulgos 412
SBi 401
Geoff Dixon 382
Vigneswar Battu 263
Gus 252
pat 231
vkumar 223
August
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 30 31