The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
from ota_suppliable_resources
where supplied_resource_id = p_supplied_resource_id
and resource_type = p_type;
select event_status
from ota_events
where event_id = l_event_id;
select event_type,parent_event_id
from ota_events
where event_id = l_event_id;
select 1
from ota_suppliable_resources sr,
ota_events e,
ota_offerings off
where sr.supplied_resource_id = p_supplied_resource_id
and e.event_id = l_event_id
and off.offering_id = e.parent_offering_id --bug 3494404
and sr.delegates_per_unit >=
nvl(e.maximum_attendees,off.maximum_attendees);
select 1
from hr_lookups
where lookup_type = 'TRAINER_PARTICIPATION'
and lookup_code = p_role_to_play;
select 1
from ota_events e
where business_group_id = p_business_group_id
and exists
(select 1
from ota_suppliable_resources sr,
ota_resource_bookings rb
where rb.event_id = e.event_id
and sr.business_group_id = p_business_group_id
and sr.supplied_resource_id = rb.supplied_resource_id
and sr.currency_code <> e.currency_code);
select e.business_group_id
from ota_events e
where e.event_id = p_event_id;
select sr.cost
from ota_suppliable_resources sr,
ota_events e,
ota_resource_bookings rb
where rb.event_id = p_event_id
and rb.status = 'C'
and sr.supplied_resource_id = rb.supplied_resource_id
and e.event_id = p_event_id;
select 1
from ota_suppliable_resources sr,
hr_lookups l
where sr.supplied_resource_id = p_supplied_resource_id
and sr.resource_type = l.lookup_code
and l.lookup_type in ('VENUE','TRAINER');
select resource_booking_id
from ota_resource_bookings
where supplied_resource_id = p_supplied_resource_id
and event_id = p_event_id;
select 1
from ota_resource_bookings
where event_id = p_event_id;
select course_start_date,course_end_date,event_type,timezone,course_start_time,course_end_time
from ota_events
where event_id = p_event_id;
select start_date_active,end_date_active
from ota_forums_b
where forum_id = p_forum_id;
select start_date_active,end_date_active,timezone_code
from ota_chats_b
where chat_id = p_chat_id;
select 1
from dual
where decode(crs_start_date, null, ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'23:59')), 'dd-mm-yyyy HH24:MI'))
>= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'23:59'), p_timezone_code,crs_timezone)
--to_date(to_char(to_char(crs_start_date,'dd-mm-yyyy')||' '||nvl(crs_start_time,'00:00')), 'dd-mm-yyyy HH24:MI') <= ota_timezone_util.convert_date(p_req_from, nvl(p_req_time_from,'00:00'), p_timezone_code,crs_timezone)
and decode(crs_end_date, null, ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone), to_date(to_char(to_char(crs_end_date,'dd-mm-yyyy')||' '||nvl(crs_end_time,'23:59')), 'dd-mm-yyyy HH24:MI'))
>= ota_timezone_util.convert_date(p_req_to, nvl(p_req_time_to,'23:59'), p_timezone_code,crs_timezone);
select 1
from ota_suppliable_resources
where supplied_resource_id = p_supplied_resource_id
and start_date <= ota_timezone_util.convert_date(p_req_from, p_req_start_time, p_timezone_code, ota_timezone_util.get_server_timezone_code)
and decode(end_date, null, ota_timezone_util.convert_date(p_req_to, p_req_end_time, p_timezone_code, ota_timezone_util.get_server_timezone_code), end_date)
>= ota_timezone_util.convert_date(p_req_to, p_req_end_time, p_timezone_code, ota_timezone_util.get_server_timezone_code);
select 1
from ota_suppliable_resources
where supplied_resource_id = p_supplied_resource_id
and start_date <= ota_timezone_util.convert_date(p_req_from, null, p_timezone_code, ota_timezone_util.get_server_timezone_code)
and decode(end_date, null, ota_timezone_util.convert_date(p_req_to, null, p_timezone_code, ota_timezone_util.get_server_timezone_code), end_date)
>= ota_timezone_util.convert_date(p_req_to, null, p_timezone_code, ota_timezone_util.get_server_timezone_code);
select 1
from ota_suppliable_resources
where supplied_resource_id = p_supplied_resource_id
and start_date <= p_req_from
and nvl(end_date,nvl(p_req_to,hr_api.g_eot)) >= nvl(p_req_to,hr_api.g_eot);
select 1
from ota_events e,
ota_suppliable_resources sr
where sr.supplied_resource_id = p_supplied_resource_id
and e.event_id = p_event_id
and sr.business_group_id = e.business_group_id;
procedure check_update_tra(p_resource_booking_id in number,
p_req_date_from in date,
p_req_date_to in date) is
--
l_proc varchar2(72) := g_package||'check_update_tra';
select 1
from ota_resource_allocations
where (trainer_resource_booking_id = p_resource_booking_id
OR equipment_resource_booking_id = p_resource_booking_id)
and (start_date < p_req_date_from OR end_date > p_req_date_to);
end check_update_tra;
select 1
from ota_resource_allocations
where trainer_resource_booking_id = p_resource_booking_id;
select 1
from ota_resource_allocations
where equipment_resource_booking_id = p_resource_booking_id;
select 1
from hr_lookups l
where lookup_type = 'RESOURCE_BOOKING_STATUS'
and lookup_code = p_status;
select 1
from ota_resource_bookings rb,
ota_events e,
ota_offerings off
where e.event_id = rb.event_id
and off.offering_id = e.parent_offering_id --bug 3494404
and ((rb.required_date_from between
p_req_from and p_req_to)
or (rb.required_date_to between
p_req_from and p_req_to))
and rb.primary_venue_flag = 'Y'
and rb.event_id = p_event_id
and rb.resource_booking_id <> nvl(p_resource_booking_id, -1);
select resource_type
from ota_suppliable_resources
where supplied_resource_id = p_supplied_resource_id;
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 (p_resource_booking_id is null
or (p_resource_booking_id is not null
and p_resource_booking_id <> trb.resource_booking_id));
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 (p_resource_booking_id is null
or (p_resource_booking_id is not null
and p_resource_booking_id <> trb.resource_booking_id));
select resource_type
from ota_suppliable_resources
where supplied_resource_id = p_supplied_resource_id;
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.resource_booking_id = p_target_resource_booking_id;
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.resource_booking_id = p_target_resource_booking_id;
select resource_type
from ota_suppliable_resources
where supplied_resource_id = p_supplied_resource_id;
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)));
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)));
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)));
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)));
select resource_type
from ota_suppliable_resources
where supplied_resource_id = p_supplied_resource_id;
select sign(sr.stock - p_quantity)
from ota_suppliable_resources sr
where sr.supplied_resource_id = p_supplied_resource_id
and sr.consumable_flag = 'Y';
select tsr.stock - p_quantity
from ota_suppliable_resources tsr
where tsr.supplied_resource_id = p_supplied_resource_id
and tsr.consumable_flag = 'Y';
update ota_suppliable_resources
set stock = l_new_stock
where supplied_resource_id = p_supplied_resource_id;
select 1
from ota_finance_lines
where resource_booking_id = p_resource_booking_id;
select 1
from ota_events
where event_id = p_event_id
and event_type in
('SCHEDULED','SESSION','PROGRAMME MEMBER','DEVELOPMENT','SELFPACED');
function check_update_quant_del(p_resource_booking_id in number,
p_quantity in number,
p_del_per_unit in number)
return boolean is
--
l_proc varchar2(72) := g_package||'check_update_quant_del';
select count(*)
from ota_resource_allocations
where equipment_resource_booking_id = p_resource_booking_id;
end check_update_quant_del;
Select
rud.supplied_resource_id,
rud.quantity
From
ota_resource_usages rud
Where rud.offering_id = (select evt.parent_offering_id from ota_events evt -- bug 3494404
where evt.event_id = p_event_id) -- bug 3494404
and p_event_start_date between
nvl(rud.start_date, hr_api.g_sot) and nvl(rud.end_date, hr_api.g_eot)
and rud.supplied_resource_id is not null
and rud.required_flag = 'Y'
and not exists
(Select null
From ota_resource_bookings trb
Where
trb.supplied_resource_id = rud.supplied_resource_id
and trb.event_id = p_event_id);
select timezone
from ota_events
where event_id=p_event_id;
select evt.title,
evt.course_start_date,
evt.course_end_date,
evt.course_start_time,
evt.course_end_time,
evt.currency_code,
fnd.name
from ota_events_vl evt, -- MLS change _vl added
fnd_currencies_vl fnd
where evt.event_id = p_event_id
and fnd.currency_code = evt.currency_code;
SELECT resource_type, trainer_id
FROM ota_suppliable_resources
WHERE supplied_resource_id = p_supplied_resource_id ;
SELECT pce.competence_id ,
nvl(pce.proficiency_level_id,0) proficiency_level_id ,
nvl(rat.step_value,0) step_value
FROM per_competence_elements pce, per_rating_levels rat
WHERE nvl(pce.effective_date_from, p_required_date_from ) <= p_required_date_from --bug 3332972
AND nvl(pce.effective_date_to,p_required_date_to) >= p_required_date_to --bug 3332972
AND rat.rating_level_id = pce.proficiency_level_id
AND pce.type = 'OTA_OFFERING' --bug 3494404
AND pce.Object_id in (
SELECT parent_offering_id --bug 3494404
FROM ota_events
WHERE event_id = p_event_id);
SELECT ev.event_type,ev.parent_event_id ,off.language_code
FROM
OTA_EVENTS ev,
OTA_OFFERINGS_VL off
WHERE ev.EVENT_ID = p_event_id
AND ev.parent_offering_id = off.offering_id;
select off.language_code
From OTA_EVENTS ev,
OTA_OFFERINGS_VL off
WHERE EVENT_ID = p_event_id
AND ev.parent_offering_id = off.offering_id;
Select ocl.competence_id,
nvl(ocl.min_proficiency_level_id,0) min_proficiency_level_id,
nvl(rat.step_value,0) step_value
From ota_competence_languages ocl,
per_rating_levels rat
Where
ocl.language_code = p_language_code and
ocl.business_group_id = ota_general.get_business_group_id and
nvl(rat.rating_level_id,0) = nvl(ocl.min_proficiency_level_id,0);
Select ocl.competence_id
From ota_competence_languages ocl
Where
ocl.language_code = p_language_code and
ocl.business_group_id = ota_general.get_business_group_id;
l_competence := ' select pce.person_id from per_competence_elements pce ,' ||
' per_rating_levels rat '||
' where ' ||
' pce.competence_id = '||b.competence_id ||
' and rat.rating_level_id = pce.proficiency_level_id ' ||
' and pce.person_id = :person_id ' ||
' and pce.effective_date_from <= '''||p_required_date_from||
''' and NVL(pce.effective_date_to,'''||p_end_of_time||''') >= '''||p_required_date_to||
''' and nvl(rat.step_value,0) >= '|| b.step_value;
' select pce.person_id from per_competence_elements pce , '||
' per_rating_levels rat ' ||
' where' ||
' pce.competence_id = '||b.competence_id ||
' and pce.effective_date_from <= '''||p_required_date_from||
''' and NVL(pce.effective_date_to,'''||p_end_of_time||''') >= '''||p_required_date_to||
''' and rat.rating_level_id = pce.proficiency_level_id ' ||
' and nvl(rat.step_value,0) >= '|| b.step_value || ')';
l_competence := ' select pce.person_id from per_competence_elements pce, '||
' per_rating_levels rat ' ||
' where' ||
' pce.competence_id = '||lang_comp.competence_id ||
' and pce.effective_date_from <= '''||p_required_date_from||
''' and NVL(pce.effective_date_to,'''||p_end_of_time||''') >= '''||p_required_date_to||
''' and rat.rating_level_id = pce.proficiency_level_id ' ||
' and pce.person_id = :person_id ' ||
' and nvl(rat.step_value,0) >= '|| lang_comp.step_value ;
' select person_id from per_competence_elements pce , '||
' per_rating_levels rat ' ||
' where' ||
' pce.competence_id = '||lang_comp.competence_id ||
' and pce.effective_date_from <= '''||p_required_date_from||
''' and NVL(pce.effective_date_to,'''||p_end_of_time||''') >= '''||p_required_date_to||
''' and rat.rating_level_id = pce.proficiency_level_id ' ||
' and nvl(rat.step_value,0) >= '|| lang_comp.step_value || ')';
l_competence := ' select pce.person_id from per_competence_elements pce ' ||
' where ' ||
' pce.competence_id = '||e.competence_id ||
' and pce.effective_date_from <= '''||p_required_date_from||
''' and NVL(pce.effective_date_to,'''||p_end_of_time||''') >= '''||p_required_date_to||
''' and pce.person_id = :person_id ' ;
' select pce.person_id from per_competence_elements pce '||
' where' ||
' pce.effective_date_from <= '''||p_required_date_from||
''' and NVL(pce.effective_date_to,'''||p_end_of_time||''') >= '''||p_required_date_to||
''' and pce.competence_id = '||e.competence_id || ')';