APPS.OTA_TRB_API_PROCEDURES SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 29

select 1
from ota_suppliable_resources
where supplied_resource_id = p_supplied_resource_id
and resource_type = p_type;

Line: 80

select event_status
from ota_events
where event_id = l_event_id;

Line: 87

select event_type,parent_event_id
from ota_events
where event_id = l_event_id;

Line: 94

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);

Line: 151

select 1
from hr_lookups
where lookup_type = 'TRAINER_PARTICIPATION'
and lookup_code = p_role_to_play;

Line: 214

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);

Line: 230

select e.business_group_id
from ota_events e
where e.event_id = p_event_id;

Line: 238

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;

Line: 333

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');

Line: 372

select resource_booking_id
from ota_resource_bookings
where supplied_resource_id = p_supplied_resource_id
and event_id = p_event_id;

Line: 411

select 1
from ota_resource_bookings
where event_id = p_event_id;

Line: 454

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;

Line: 459

select start_date_active,end_date_active
from ota_forums_b
where forum_id = p_forum_id;

Line: 464

select start_date_active,end_date_active,timezone_code
from ota_chats_b
where chat_id = p_chat_id;

Line: 472

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);

Line: 552

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);

Line: 562

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);

Line: 571

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);

Line: 616

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;

Line: 668

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';

Line: 678

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);

Line: 697

end check_update_tra;

Line: 715

select 1
from ota_resource_allocations
where trainer_resource_booking_id = p_resource_booking_id;

Line: 750

select 1
from ota_resource_allocations
where equipment_resource_booking_id = p_resource_booking_id;

Line: 784

select 1
from hr_lookups l
where lookup_type = 'RESOURCE_BOOKING_STATUS'
and lookup_code = p_status;

Line: 846

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);

Line: 906

  select resource_type
  from   ota_suppliable_resources
  where  supplied_resource_id = p_supplied_resource_id;

Line: 913

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));

Line: 935

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));

Line: 1106

  select resource_type
  from   ota_suppliable_resources
  where  supplied_resource_id = p_supplied_resource_id;

Line: 1113

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;

Line: 1133

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;

Line: 1324

  select resource_type
  from   ota_suppliable_resources
  where  supplied_resource_id = p_supplied_resource_id;

Line: 1331

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)));

Line: 1355

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)));

Line: 1380

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)));

Line: 1399

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)));

Line: 1634

  select resource_type
  from   ota_suppliable_resources
  where  supplied_resource_id = p_supplied_resource_id;

Line: 1684

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';

Line: 1722

select tsr.stock - p_quantity
from ota_suppliable_resources tsr
where tsr.supplied_resource_id = p_supplied_resource_id
and tsr.consumable_flag = 'Y';

Line: 1736

update ota_suppliable_resources
set stock = l_new_stock
where supplied_resource_id = p_supplied_resource_id;

Line: 1758

select 1
from ota_finance_lines
where resource_booking_id = p_resource_booking_id;

Line: 1792

select 1
from ota_events
where event_id = p_event_id
and event_type in
  ('SCHEDULED','SESSION','PROGRAMME MEMBER','DEVELOPMENT','SELFPACED');

Line: 1827

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';

Line: 1840

select count(*)
from ota_resource_allocations
where equipment_resource_booking_id = p_resource_booking_id;

Line: 1861

end check_update_quant_del;

Line: 1884

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);

Line: 1904

select timezone
from ota_events
where event_id=p_event_id;

Line: 1971

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;

Line: 2063

SELECT resource_type, trainer_id
FROM ota_suppliable_resources
WHERE supplied_resource_id = p_supplied_resource_id ;

Line: 2070

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);

Line: 2086

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;

Line: 2096

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;

Line: 2104

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);

Line: 2117

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;

Line: 2160

                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;

Line: 2174

                                ' 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 || ')';

Line: 2193

                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 ;

Line: 2208

                                ' 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 || ')';

Line: 2230

                  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 ' ;

Line: 2242

                                ' 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 || ')';