function check_double_booking(p_supplied_resource_id in number
,p_required_date_from in date
,p_required_start_time in varchar2
,p_required_date_to in date
,p_required_end_time in varchar2
,p_resource_booking_id in number
,p_book_entire_period_flag in varchar2
,p_timezone in varchar2
,p_last_res_bkng_id in number)return boolean IS
--
l_proc varchar2(72) := g_package||'check_double_booking';
l_exists number;
l_book_entire_period varchar2(1);
l_resource_type varchar2(30);
cursor get_resource_type is
select resource_type
from ota_suppliable_resources
where supplied_resource_id = p_supplied_resource_id;
-- For entire duration flag null or N
cursor double_booking is
select 1
from ota_resource_bookings trb
where trb.supplied_resource_id = p_supplied_resource_id
and (
(p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
)
/*or
(to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
)*/)
and trb.status = 'C'
and (p_resource_booking_id is null
or (p_resource_booking_id is not null
and p_resource_booking_id <> trb.resource_booking_id and trb.resource_booking_id > nvl(p_last_res_bkng_id,0)));
-- Modified to exclude forum and chat related bookings
-- For entire duration flag null or N
cursor trainer_double_booking is
select 1
from ota_resource_bookings trb
where trb.supplied_resource_id = p_supplied_resource_id
and (
(p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
/*or
(to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
*/)
and trb.status = 'C'
and trb.chat_id is null
and trb.forum_id is null
and (p_resource_booking_id is null
or (p_resource_booking_id is not null
and p_resource_booking_id <> trb.resource_booking_id and trb.resource_booking_id > nvl(p_last_res_bkng_id,0))); --bug 5110895
Cursor csr_chk_date_overlap is
select Book_entire_period_flag,required_end_time,required_start_time,
required_date_from,required_date_to,timezone_code
from ota_resource_bookings trb
where trb.supplied_resource_id = p_supplied_resource_id
and p_required_date_from <= trunc (ota_timezone_util.convert_date
(trb.required_date_to, nvl (trb.required_end_time, '23:59'),
trb.timezone_code, p_timezone)) and p_required_date_to >= trunc (ota_timezone_util.convert_date
(trb.required_date_from, nvl (trb.required_start_time, '00:00'),
trb.timezone_code, p_timezone))
and trb.status = 'C'
and (p_resource_booking_id is null
or (p_resource_booking_id is not null
and p_resource_booking_id <> trb.resource_booking_id and trb.resource_booking_id > nvl(p_last_res_bkng_id,0)));
-- Modified to exclude forum and chat related bookings Cursor csr_chk_trainer_date_overlap is select Book_entire_period_flag,required_end_time,required_start_time,
required_date_from,required_date_to,timezone_code
from ota_resource_bookings trb
where trb.supplied_resource_id = p_supplied_resource_id
and (
(p_required_date_from between
trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
and
trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
or
(p_required_date_to between
trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
and
trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
or
((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))
and
(p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))
)
and trb.status = 'C'
and trb.chat_id is null
and trb.forum_id is null
and (p_resource_booking_id is null
or (p_resource_booking_id is not null
and p_resource_booking_id <> trb.resource_booking_id and trb.resource_booking_id > nvl(p_last_res_bkng_id,0)));
--
begin
hr_utility.set_location('Entering:'||l_proc,5);
--
open get_resource_type;
fetch get_resource_type into l_resource_type; close get_resource_type;
if (l_resource_type = 'T') then
open trainer_double_booking;
fetch trainer_double_booking into l_exists;
if trainer_double_booking%found then
--
close trainer_double_booking;
return TRUE;
else
close trainer_double_booking;
for trainer_rec in csr_chk_trainer_date_overlap
loop
/*Fetch csr_chk_trainer_date_overlap into l_book_entire_period;
if csr_chk_trainer_date_overlap%NotFound then
close csr_chk_trainer_date_overlap;
--No date overlap
return FALSE;
else
close csr_chk_trainer_date_overlap;*/
--Date overlap present
-- Check new or existing either one is book enire period Y
if ((p_required_date_from <> p_required_date_to)
and (trainer_rec.required_date_from <> trainer_rec.required_date_to)) then
if trainer_rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
--check time overlap
if (
(p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
or
(p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
) then
return false;
else
return TRUE ;
end if;
end if;
--bug 5152139
elsif(
(p_required_date_from = p_required_date_to and trainer_rec.book_entire_period_flag = 'Y')
or (trainer_rec.required_date_from = trainer_rec.required_date_to and p_book_entire_period_flag = 'Y')
) then
--since first cursor didn't give problem this means new and old record dates cannot be equal
--and time
if(
(p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone)) and
nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
or
(p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)) and
nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)
)
) then
return false;
else
return True;
end if;
--bug 5116223
elsif((p_required_date_from = p_required_date_to or trainer_rec.required_date_from = trainer_rec.required_date_to ) and trainer_rec.timezone_code <> p_timezone)then
if(trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
<> trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))) then
if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)
and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
) then
return true;
end if;
end if;
end if;
end loop;
end if;
else
open double_booking;
fetch double_booking into l_exists;
if double_booking%found then
--
close double_booking;
return TRUE;
else
close double_booking;
-- not sure if still conflict or not depending on book_entire_period_flag of existing or new record
--get date overlap record
for rec in csr_chk_date_overlap
loop
/*Fetch csr_chk_trainer_date_overlap into l_book_entire_period;
if csr_chk_trainer_date_overlap%NotFound then
close csr_chk_trainer_date_overlap;
--No date overlap
return FALSE;
else
close csr_chk_trainer_date_overlap;*/
--Date overlap present
-- Check new or existing either one is book enire period Y
/* if ((p_required_date_from <> p_required_date_to)
and (rec.required_date_from <> rec.required_date_to)) then*/
if rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
--check time overlap
if (
(p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
or
(p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
) then
return false;
else
return TRUE ;
end if;
elsif(
(p_required_date_from = p_required_date_to and rec.book_entire_period_flag = 'Y')
or (rec.required_date_from = rec.required_date_to and p_book_entire_period_flag = 'Y')
) then
--since first cursor didn't give problem this means new and old record dates cannot be equal
--and time
if(
(p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
or
(p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
)
) then
return false;
else
return True;
end if;
--bug 5116223
elsif((p_required_date_from = p_required_date_to or rec.required_date_from = rec.required_date_to) and rec.timezone_code <> p_timezone ) then
if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
<> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
) then
return true;
end if;
end if;
end if;
end loop;
end if;
end if;
--close double_booking;
return FALSE;
--
hr_utility.set_location('Leaving:'||l_proc,10);
end check_double_booking;
,p_required_date_from in date
,p_required_start_time in varchar2
,p_required_date_to in date
,p_required_end_time in varchar2
,p_resource_booking_id in number
,p_book_entire_period_flag in varchar2
,p_timezone in varchar2
,p_last_res_bkng_id in number)return boolean IS
--
l_proc varchar2(72) := g_package||'check_double_booking';
l_exists number;
l_book_entire_period varchar2(1);
l_resource_type varchar2(30);
cursor get_resource_type is
select resource_type
from ota_suppliable_resources
where supplied_resource_id = p_supplied_resource_id;
-- For entire duration flag null or N
cursor double_booking is
select 1
from ota_resource_bookings trb
where trb.supplied_resource_id = p_supplied_resource_id
and (
(p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
)
/*or
(to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)
)*/)
and trb.status = 'C'
and (p_resource_booking_id is null
or (p_resource_booking_id is not null
and p_resource_booking_id <> trb.resource_booking_id and trb.resource_booking_id > nvl(p_last_res_bkng_id,0)));
-- Modified to exclude forum and chat related bookings
-- For entire duration flag null or N
cursor trainer_double_booking is
select 1
from ota_resource_bookings trb
where trb.supplied_resource_id = p_supplied_resource_id
and (
(p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))
and p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
and nvl(p_required_start_time, '00:00') <= ota_timezone_util.convert_dateDT_time(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
and nvl(p_required_end_time, '23:59') >= ota_timezone_util.convert_dateDT_time(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
/*or
(to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)
and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
*/)
and trb.status = 'C'
and trb.chat_id is null
and trb.forum_id is null
and (p_resource_booking_id is null
or (p_resource_booking_id is not null
and p_resource_booking_id <> trb.resource_booking_id and trb.resource_booking_id > nvl(p_last_res_bkng_id,0))); --bug 5110895
Cursor csr_chk_date_overlap is
select Book_entire_period_flag,required_end_time,required_start_time,
required_date_from,required_date_to,timezone_code
from ota_resource_bookings trb
where trb.supplied_resource_id = p_supplied_resource_id
and p_required_date_from <= trunc (ota_timezone_util.convert_date
(trb.required_date_to, nvl (trb.required_end_time, '23:59'),
trb.timezone_code, p_timezone)) and p_required_date_to >= trunc (ota_timezone_util.convert_date
(trb.required_date_from, nvl (trb.required_start_time, '00:00'),
trb.timezone_code, p_timezone))
and trb.status = 'C'
and (p_resource_booking_id is null
or (p_resource_booking_id is not null
and p_resource_booking_id <> trb.resource_booking_id and trb.resource_booking_id > nvl(p_last_res_bkng_id,0)));
-- Modified to exclude forum and chat related bookings Cursor csr_chk_trainer_date_overlap is select Book_entire_period_flag,required_end_time,required_start_time,
required_date_from,required_date_to,timezone_code
from ota_resource_bookings trb
where trb.supplied_resource_id = p_supplied_resource_id
and (
(p_required_date_from between
trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
and
trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
or
(p_required_date_to between
trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone))
and
trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone)))
or
((p_required_date_from <= trunc(ota_timezone_util.convert_date(trb.required_date_from,nvl(trb.required_start_time, '00:00'),trb.timezone_code,p_timezone)))
and
(p_required_date_to >= trunc(ota_timezone_util.convert_date(trb.required_date_to,nvl(trb.required_end_time, '23:59'),trb.timezone_code,p_timezone))))
)
and trb.status = 'C'
and trb.chat_id is null
and trb.forum_id is null
and (p_resource_booking_id is null
or (p_resource_booking_id is not null
and p_resource_booking_id <> trb.resource_booking_id and trb.resource_booking_id > nvl(p_last_res_bkng_id,0)));
--
begin
hr_utility.set_location('Entering:'||l_proc,5);
--
open get_resource_type;
fetch get_resource_type into l_resource_type; close get_resource_type;
if (l_resource_type = 'T') then
open trainer_double_booking;
fetch trainer_double_booking into l_exists;
if trainer_double_booking%found then
--
close trainer_double_booking;
return TRUE;
else
close trainer_double_booking;
for trainer_rec in csr_chk_trainer_date_overlap
loop
/*Fetch csr_chk_trainer_date_overlap into l_book_entire_period;
if csr_chk_trainer_date_overlap%NotFound then
close csr_chk_trainer_date_overlap;
--No date overlap
return FALSE;
else
close csr_chk_trainer_date_overlap;*/
--Date overlap present
-- Check new or existing either one is book enire period Y
if ((p_required_date_from <> p_required_date_to)
and (trainer_rec.required_date_from <> trainer_rec.required_date_to)) then
if trainer_rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
--check time overlap
if (
(p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))
or
(p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
) then
return false;
else
return TRUE ;
end if;
end if;
--bug 5152139
elsif(
(p_required_date_from = p_required_date_to and trainer_rec.book_entire_period_flag = 'Y')
or (trainer_rec.required_date_from = trainer_rec.required_date_to and p_book_entire_period_flag = 'Y')
) then
--since first cursor didn't give problem this means new and old record dates cannot be equal
--and time
if(
(p_required_date_from = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone)) and
nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
or
(p_required_date_to = trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)) and
nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)
)
) then
return false;
else
return True;
end if;
--bug 5116223
elsif((p_required_date_from = p_required_date_to or trainer_rec.required_date_from = trainer_rec.required_date_to ) and trainer_rec.timezone_code <> p_timezone)then
if(trunc(ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
<> trunc(ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone))) then
if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(trainer_rec.required_date_to,nvl(trainer_rec.required_end_time, '23:59'),trainer_rec.timezone_code,p_timezone)
and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(trainer_rec.required_date_from,nvl(trainer_rec.required_start_time, '00:00'),trainer_rec.timezone_code,p_timezone))
) then
return true;
end if;
end if;
end if;
end loop;
end if;
else
open double_booking;
fetch double_booking into l_exists;
if double_booking%found then
--
close double_booking;
return TRUE;
else
close double_booking;
-- not sure if still conflict or not depending on book_entire_period_flag of existing or new record
--get date overlap record
for rec in csr_chk_date_overlap
loop
/*Fetch csr_chk_trainer_date_overlap into l_book_entire_period;
if csr_chk_trainer_date_overlap%NotFound then
close csr_chk_trainer_date_overlap;
--No date overlap
return FALSE;
else
close csr_chk_trainer_date_overlap;*/
--Date overlap present
-- Check new or existing either one is book enire period Y
/* if ((p_required_date_from <> p_required_date_to)
and (rec.required_date_from <> rec.required_date_to)) then*/
if rec.book_entire_period_flag = 'Y' or p_book_entire_period_flag = 'Y' then
--check time overlap
if (
(p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
and nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))
or
(p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
and nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
) then
return false;
else
return TRUE ;
end if;
elsif(
(p_required_date_from = p_required_date_to and rec.book_entire_period_flag = 'Y')
or (rec.required_date_from = rec.required_date_to and p_book_entire_period_flag = 'Y')
) then
--since first cursor didn't give problem this means new and old record dates cannot be equal
--and time
if(
(p_required_date_from = trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone)) and
nvl(p_required_end_time, '23:59') < ota_timezone_util.convert_dateDT_time(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
or
(p_required_date_to = trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)) and
nvl(p_required_start_time, '00:00') > ota_timezone_util.convert_dateDT_time(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
)
) then
return false;
else
return True;
end if;
--bug 5116223
elsif((p_required_date_from = p_required_date_to or rec.required_date_from = rec.required_date_to) and rec.timezone_code <> p_timezone ) then
if(trunc(ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
<> trunc(ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone))) then
if((to_date( to_char(nvl(p_required_date_from,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_start_time, '00:00'),'YYYY/MM/DD HH24:MI') <= ota_timezone_util.convert_date(rec.required_date_to,nvl(rec.required_end_time, '23:59'),rec.timezone_code,p_timezone)
and to_date( to_char(nvl(p_required_date_to,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' '
|| nvl(p_required_end_time, '23:59'),'YYYY/MM/DD HH24:MI') >= ota_timezone_util.convert_date(rec.required_date_from,nvl(rec.required_start_time, '00:00'),rec.timezone_code,p_timezone))
) then
return true;
end if;
end if;
end if;
end loop;
end if;
end if;
--close double_booking;
return FALSE;
--
hr_utility.set_location('Leaving:'||l_proc,10);
end check_double_booking;
No comments:
Post a Comment