APPS.OTA_INITIALIZATION_WF SQL Statements

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

Line: 68

SELECT ORDER_NUMBER ,
    HEADER_ID
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID IN(
SELECT HEADER_ID
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID = p_line_id);

Line: 79

SELECT TITLE,
    owner_id
FROM   OTA_EVENTS_VL --MLS change _VL added
WHERE  event_id = p_event_id;

Line: 87

SELECT user_name
FROM
   fnd_user
WHERE
   employee_id = l_owner_id
   AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD'));      --Bug 5676892

Line: 180

SELECT
   order_number ,
   header_id
FROM
  OE_ORDER_HEADERS_ALL
WHERE
   HEADER_ID IN(
      SELECT
         HEADER_ID
      FROM
         OE_ORDER_LINES_ALL
      WHERE
         LINE_ID = p_line_id);

Line: 197

SELECT
   USER_NAME
FROM
     FND_USER
WHERE
   employee_id = p_owner_id
    AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD'));      --Bug 5676892

Line: 207

Select
    line_number
from
    oe_order_lines_all
where
    line_id = p_line_id;

Line: 235

   SELECT to_char(sysdate, 'DD-MON-YYYY:HH24:MI:SS')
     INTO l_wf_date
     FROM dual;

Line: 360

SELECT
   order_number ,
   header_id
FROM
  OE_ORDER_HEADERS_ALL
WHERE
   HEADER_ID IN(
      SELECT
         HEADER_ID
      FROM
         OE_ORDER_LINES_ALL
      WHERE
         LINE_ID = p_line_id);

Line: 375

Select
    line_number
from
    oe_order_lines_all
where
    line_id = p_line_id;

Line: 532

SELECT
 USER_NAME
FROM
 FND_USER
WHERE
Employee_id = p_owner_id
AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD'));      --Bug 5676892

Line: 580

select parent_event_id,event_type from
ota_events where event_id =p_eventid;

Line: 586

select
--added after show n tell
oev.activity_version_id,oev.actual_cost, oev.budget_currency_code,
oev.parent_offering_id,ota_timezone_util.get_timezone_name(oev.timezone) timezone
from ota_events_tl evt, ota_events oev
where evt.event_id =oev.event_id
and oev.event_id = l_event_id
and evt.language=USERENV('LANG');

Line: 596

select ofe.language_id, ocu.category
from ota_offerings ofe, ota_category_usages_tl ocu
where ofe.delivery_mode_id = ocu.category_usage_id
and ocu.language=USERENV('LANG')
and ofe.offering_id = l_off_id;

Line: 709

select osr.trainer_id
from  ota_suppliable_resources osr
where
osr.supplied_resource_id=p_sup_res_id;

Line: 716

select evt.title,
oev.course_start_date,oev.course_end_date,oev.course_start_time, oev.course_end_time,
oev.location_id,oev.event_type,ota_timezone_util.get_timezone_name(oev.timezone)
from ota_events_tl evt, ota_events oev
where evt.event_id =oev.event_id
and oev.event_id = p_eventid
and evt.language= USERENV('LANG');

Line: 726

select distinct(osr.trainer_id) trainer_id ,orb.required_date_from,orb.required_date_to,
orb.required_start_time,orb.required_end_time,orb.status,ota_timezone_util.get_timezone_name(orb.timezone_code) timezone,
orb.resource_booking_id resource_booking_id
from ota_resource_bookings orb,ota_suppliable_resources osr
where orb.supplied_resource_id = osr.supplied_resource_id
and osr.resource_type ='T'
and orb.event_id = p_eventid
and (p_res_book_id is null or orb.resource_booking_id=p_res_book_id);

Line: 737

select osr.trainer_id trainer_id ,orb.required_date_from,orb.required_date_to,
orb.required_start_time,orb.required_end_time,orb.status,ota_timezone_util.get_timezone_name(orb.timezone_code) timezone,
orb.resource_booking_id resource_booking_id
from ota_resource_bookings orb,ota_suppliable_resources osr
where orb.supplied_resource_id = osr.supplied_resource_id
and osr.supplied_resource_id = p_sup_res_id
and osr.resource_type ='T'
and orb.event_id = p_eventid;

Line: 764

  select hr_workflow_item_key_s.nextval
  into   l_item_key
  from   sys.dual;

Line: 820

     select hr_workflow_item_key_s.nextval
      into   l_item_key
      from   sys.dual;

Line: 868

  select hr_workflow_item_key_s.nextval
  into   l_item_key
  from   sys.dual;

Line: 968

select evt.title,oev.course_start_date,oev.course_end_date,
oev.course_start_time, oev.course_end_time,
oev.location_id
from ota_events_tl evt, ota_events oev
where evt.event_id =oev.event_id
and oev.event_id = p_eventid
and evt.language=USERENV('LANG');

Line: 979

select odb.booking_id , odb.delegate_person_id
from ota_delegate_bookings odb, ota_booking_status_types bst
where (p_person_id is null or
odb.delegate_person_id = p_person_id)
and odb.event_id =p_eventid
and odb.booking_status_type_id = bst.booking_status_type_id
and bst.type in ('P','W','R');

Line: 990

select odb.booking_id , odb.delegate_person_id
from ota_delegate_bookings odb, ota_booking_status_types bst
where (p_person_id is null or
odb.delegate_person_id = p_person_id)
and odb.event_id =p_eventid
and odb.booking_status_type_id = bst.booking_status_type_id;

Line: 1009

  select hr_workflow_item_key_s.nextval
  into   l_item_key
  from   sys.dual;

Line: 1055

  select hr_workflow_item_key_s.nextval
  into   l_item_key
  from   sys.dual;

Line: 1115

select event_id
from  ota_events
where (course_start_date = trunc(sysdate) )
and event_type in ('SCHEDULED' , 'SELFPACED')
and event_status <> 'A';

Line: 1125

select odb.delegate_person_id
from ota_delegate_bookings odb,ota_booking_status_types bst
where odb.event_id = crs_event_id
and odb.booking_status_type_id = bst.booking_status_type_id
and bst.type = 'W';

Line: 1134

select supplied_resource_id
from ota_suppliable_resources
where resource_type ='T';

Line: 1139

select orb.event_id event_id
from  ota_resource_bookings orb
where orb.supplied_resource_id = l_supp_res_id
and orb.required_date_from = (trunc(sysdate)+l_notify_days_before)
and orb.status= 'C'
and orb.event_id is not null;

Line: 1148

select distinct(orb.event_id) event_id, orb.supplied_resource_id
from  ota_resource_bookings orb,ota_suppliable_resources osr
where orb.supplied_resource_id = osr.supplied_resource_id
and osr.resource_type ='T'
and orb.required_date_from = (trunc(sysdate) + l_notify_days_before)
and orb.status= 'C'
and orb.event_id is not null;*/

Line: 1222

select lpt.name, lpe.person_id, lp.start_date_active
from ota_learning_paths lp , ota_learning_paths_tl lpt, ota_lp_enrollments lpe
where lpt.learning_path_id = lp.learning_path_id
and lpt.Language= USERENV('LANG')
and lp.Learning_path_id = lpe.Learning_path_id
and lpe.lp_enrollment_id = p_lp_enrollment_id;

Line: 1244

  select hr_workflow_item_key_s.nextval
  into   l_item_key
  from   sys.dual;

Line: 1333

Select user_id ,user_name
from
fnd_user
where employee_id=p_person_id
AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD'));      --Bug 5676892

Line: 1341

SELECT
 EMPLOYEE_ID
FROM
 FND_USER
WHERE
 user_id = l_creator_user_id ;

Line: 1350

    SELECT
	       pp.full_name
    FROM    per_people_f        pp
    WHERE   pp.person_id         = crs_person_id
    AND     trunc(sysdate) BETWEEN pp.effective_start_date AND pp.effective_end_date;

Line: 1357

  SELECT asg.supervisor_id, per.full_name
    FROM per_all_assignments_f asg,
         per_all_people_f per
   WHERE asg.person_id = p_person_id
     AND per.person_id = asg.supervisor_id
     AND asg.primary_flag = 'Y'
     AND trunc(sysdate)
 BETWEEN asg.effective_start_date AND asg.effective_end_date
     AND trunc(sysdate)
 BETWEEN per.effective_start_date AND per.effective_end_date
  AND asg.assignment_type in ('E', 'A', 'C');  --Bug#8614003

Line: 1371

 SELECT user_name
   FROM fnd_user
  WHERE employee_id= l_supervisor_id
  AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD'));      --Bug 5676892

Line: 1497

 select olb.name,oa.raw_score,oa.time
-- ,oa.event_id
        from ota_learning_objects olb, ota_attempts oa
        where olb.learning_object_id = oa.learning_object_id
     --   and oa.attempt_id = op.attempt_id
        and oa.attempt_id =p_attempt_id;

Line: 1534

  select hr_workflow_item_key_s.nextval
  into   l_item_key
  from   sys.dual;

Line: 1585

Select cet.name
      ,cet.end_date_comments
      ,ceb.end_date_active
From ota_certifications_b ceb
    ,ota_certifications_tl cet
Where
    ceb.certification_id = cet.certification_id
    and cet.language = userenv('LANG')
    and ceb.certification_id = p_certification_id;

Line: 1613

  select hr_workflow_item_key_s.nextval
  into   l_item_key
  from   sys.dual;

Line: 1654

Select ceb.certification_id
       ,prd.cert_prd_enrollment_id
       ,enr.person_id
From ota_certifications_b ceb
    ,ota_cert_enrollments enr
    ,ota_cert_prd_enrollments prd
Where
    ceb.certification_id = enr.certification_id
    and enr.cert_enrollment_id = prd.cert_enrollment_id
    and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
        nvl(trunc(ceb.end_date_active), trunc(sysdate))
    and trunc(sysdate) between nvl(trunc(prd.cert_period_start_date), trunc(sysdate)) and
        nvl(trunc(prd.cert_period_end_date), trunc(sysdate))
    and prd.period_status_code not in('COMPLETED', 'CANCELLED')
    and enr.certification_status_code not in ('CERTIFIED', 'CANCELLED')
    and enr.person_id is not null
    and ceb.notify_days_before_expire is not null
    and (trunc(sysdate) + ceb.notify_days_before_expire) = trunc(prd.cert_period_end_date);

Line: 1675

Select ceb.certification_id
       ,prd.cert_prd_enrollment_id
       ,enr.person_id
From ota_certifications_b ceb
    ,ota_cert_enrollments enr
    ,ota_cert_prd_enrollments prd
Where
    ceb.certification_id = enr.certification_id
    and enr.cert_enrollment_id = prd.cert_enrollment_id
    and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
        nvl(trunc(ceb.end_date_active), trunc(sysdate))
    and prd.period_status_code not in('COMPLETED', 'CANCELLED')
    and enr.certification_status_code not in ('CERTIFIED', 'CANCELLED')
    and trunc(prd.cert_period_end_date) + 1 = trunc(sysdate)
    and enr.person_id is not null;

Line: 1693

Select ceb.certification_id
       ,prd.cert_prd_enrollment_id
       ,enr.person_id
From ota_certifications_b ceb
    ,ota_cert_enrollments enr
    ,ota_cert_prd_enrollments prd
Where
    ceb.certification_id = enr.certification_id
    and enr.cert_enrollment_id = prd.cert_enrollment_id
    and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
        nvl(trunc(ceb.end_date_active), trunc(sysdate))
    and trunc(sysdate) between nvl(trunc(prd.cert_period_start_date), trunc(sysdate)) and
        nvl(trunc(prd.cert_period_end_date), trunc(sysdate))
    and prd.period_status_code = 'COMPLETED'
    and trunc(prd.completion_date) = trunc(sysdate)
    and enr.person_id is not null;

Line: 1712

Select ceb.certification_id
       ,prd.cert_prd_enrollment_id
       ,enr.person_id
From ota_certifications_b ceb
    ,ota_cert_enrollments enr
    ,ota_cert_prd_enrollments prd
Where
    ceb.certification_id = enr.certification_id
    and enr.cert_enrollment_id = prd.cert_enrollment_id
    and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
        nvl(trunc(ceb.end_date_active), trunc(sysdate))
    and trunc(enr.earliest_enroll_date) = trunc(sysdate)
    and enr.person_id is not null;

Line: 1728

Select ceb.certification_id
       ,enr.person_id
From ota_certifications_b ceb
    ,ota_cert_enrollments enr
Where
    ceb.certification_id = enr.certification_id
    and enr.person_id is not null
    and (trunc(sysdate) + nvl(ceb.notify_days_before_expire, 0)) = trunc(ceb.end_date_active);

Line: 1816

select oft.name,fth.subject,ofm.person_id,ofm.contact_id,
ofm.creation_date,ofm.message_body
from ota_forum_messages ofm ,ota_forum_threads fth ,ota_forums_tl oft
where oft.forum_id = ofm.forum_id
and ofm.forum_thread_id = fth.forum_thread_id
and ofm.forum_message_id = p_Forum_message_id
and oft.language= USERENV('LANG');

Line: 1826

select person_id
from ota_frm_notif_subscribers
where forum_id = p_forum_id
and person_id is not null;

Line: 1861

  select hr_workflow_item_key_s.nextval
  into   l_item_key
  from   sys.dual;

Line: 1937

SELECT bst.Type, tdb.delegate_person_id, tdb.contact_id, tdb.event_id
FROM   OTA_DELEGATE_BOOKINGS tdb,
       OTA_BOOKING_STATUS_TYPES bst
WHERE  tdb.booking_id = p_booking_id
AND    bst.booking_status_type_id = tdb.booking_status_type_id;

Line: 1950

select hr_workflow_item_key_s.nextval into l_item_key from sys.dual;

Line: 1997

Select OCU.synchronous_flag, OCU.online_flag
From ota_events OEV,
     ota_offerings OFR,
     ota_category_usages OCU
Where OFR.offering_id = OEV.parent_offering_id
  And OCU.category_usage_id = OFR.delivery_mode_id
  And OEV.event_id = p_event_id;

Line: 2031

  SELECT title FROM ota_events_tl
  WHERE to_char(event_id) = document_id AND language = USERENV('LANG');

Line: 2076

select oev.title,oev.course_start_date,oev.course_end_date,
oev.course_start_time, oev.course_end_time,
oev.location_id,oev.training_center_id
from ota_events_tl evt, ota_events oev
where evt.event_id =oev.event_id
and oev.event_id = p_eventid
and evt.language=USERENV('LANG');

Line: 2087

select odb.booking_id , odb.delegate_person_id, odb.delegate_contact_id
from ota_delegate_bookings odb, ota_booking_status_types bst
where odb.event_id =p_eventid
and odb.booking_status_type_id = bst.booking_status_type_id
and bst.type in ('P','W','R');

Line: 2101

if (p_event_fired = 'oracle.apps.ota.api.event_api.update_location' or
    p_event_fired = 'oracle.apps.ota.api.event_api.update_training_center' or
    p_event_fired = 'oracle.apps.ota.api.event_api.update_trng_cntr_and_location')then



for rec in get_booking_info
Loop

-- Get the next item key from the sequence
  select hr_workflow_item_key_s.nextval
  into   l_item_key
  from   sys.dual;

Line: 2212

elsif p_event_fired = 'oracle.apps.ota.api.event_api.update_class_schedule' then

for rec in get_booking_info
Loop

-- Get the next item key from the sequence
  select hr_workflow_item_key_s.nextval
  into   l_item_key
  from   sys.dual;