APPS.OTA_UTILITY SQL Statements

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

Line: 73

select a.resubmit_interval,a.resubmit_end_date
 from fnd_concurrent_requests a, fnd_concurrent_programs b
 where a.concurrent_program_id =  b.concurrent_program_id
 and b.concurrent_program_name = p_name
 and b.application_id = 810
 and a.status_code ='I'
 and a.hold_flag ='N'
 and rownum=1
 order by a.request_id desc;

Line: 114

is select count(resource_booking_id)
from ota_resource_bookings
where event_id = peventid;

Line: 144

SELECT waav.text_value FROM WF_ACTIVITY_ATTR_VALUES WAAV
 WHERE WAAV.PROCESS_ACTIVITY_ID = (select max(instance_id) from
 wf_process_activities wpa where wpa.process_name =
'OTA_COMPETENCE_UPDATE_JSP_PRC'
 and  wpa.activity_name  = 'OTA_COMPETENCE_NOTIFY_APPROVAL'
 and  wpa.instance_label  = 'OTA_COMPETENCE_NOTIFY_APPROVAL'
and wpa.process_version =  wpa.process_version
and wpa.process_item_type = 'HRSSA' )
 AND  WAAV.NAME = 'HR_APPROVAL_REQ_FLAG' ;

Line: 156

 select text_default from wf_activity_attributes where NAME = 'HR_APPROVAL_REQ_FLAG'
 and activity_item_type = 'HRSSA'
 and activity_name  = 'OTA_COMPETENCE_NOTIFY_APPROVAL';

Line: 161

 select oav.competency_update_level from
 ota_activity_versions oav
 --, ota_offerings off
 where oav.activity_version_id = p_obj_id;

Line: 194

l_return_value := get_lookup_meaning('OTA_COMPETENCY_UPDATE_LEVEL',l_lookup_value,810);

Line: 212

is select count(event_id) from
ota_events where
parent_event_id = peventid and
           parent_event_id is not null;

Line: 359

select tav.version_name,
    evt.Title,
    evt.Course_Start_Date,
    evt.Course_End_Date,
       tdb.delegate_contact_id
From
   ota_Delegate_bookings tdb,
   ota_Events_vl evt, --MLS change _vl added
   ota_activity_versions_tl tav -- MLS change _tl added
Where
   evt.event_id = tdb.event_id and
   tdb.line_id = p_line_id and
      evt.activity_version_id = tav.activity_version_id;

Line: 377

Select full_name
From
    ota_customer_contacts_v
Where
    contact_id = l_contact_id;

Line: 385

Select
   tav.version_name,
   evt.title,
      evt.Course_Start_Date,
      evt.course_End_Date,
      evt.Maximum_Attendees
FROM ota_events_vl evt, -- MLS change _vl added
   ota_activity_versions_tl tav  --MLS change _tl added
WHERE evt.line_id = p_line_id and
      evt.activity_version_id = tav.activity_version_id;

Line: 397

select value
from v$parameter
where name ='nls_date_format';

Line: 565

SELECT
   invoicing_rule_id
FROM
   oe_order_lines_all
WHERE
   line_id = p_line_id;

Line: 633

SELECT Type
FROM OTA_BOOKING_STATUS_TYPES
WHERE booking_status_type_id = p_status_type_id;

Line: 695

SELECT name
FROM OTA_BOOKING_STATUS_TYPES_VL
WHERE booking_status_type_id = p_status_type_id;

Line: 755

Select null
FROM
OTA_DELEGATE_BOOKINGS
WHERE
Line_id = p_line_id;

Line: 819

Select null
FROM
OTA_EVENTS
WHERE
Line_id = p_line_id;

Line: 885

        select meaning
        from    hr_lookups
        where   lookup_type     = p_lookup_type
        and     lookup_code     = p_lookup_code
        and     enabled_flag = 'Y';

Line: 945

SELECT
   decode(invoiced_quantity,null,0,invoiced_quantity)
FROM
   oe_order_lines_all
WHERE
   line_id = p_line_id;

Line: 1007

        SELECT null
     FROM wf_item_activity_statuses_v wf
     WHERE activity_name = p_activity
           AND activity_status_code = 'NOTIFIED'
           AND item_type = 'OEOL'
                 AND item_key = to_char(p_line_id);

Line: 1052

    select bst.type
    from ota_delegate_bookings db,
         ota_booking_status_types bst,
         ota_events ev,
         ota_events evt
    where db.delegate_person_id = p_delegate_person_id
      and db.booking_status_type_id = bst.booking_status_type_id
      and bst.type <> g_cancelled_booking
      and db.event_id = ev.event_id
      and evt.event_id = p_event_id
      and ev.event_id <> p_event_id
      and ((
           ev.course_start_date = ev.course_end_date and
           evt.course_start_date = evt.course_end_date and
           ev.course_start_date = evt.course_start_date and
           nvl(evt.course_start_time, '-99:99') <= nvl(ev.course_end_time, '99:99') and
           nvl(evt.course_end_time, '99:99') >= nvl(ev.course_start_time, '-99:99')
          )
      or  (
           (ev.course_start_date <> ev.course_end_date or
           evt.course_start_date <> evt.course_end_date) and
           ev.course_start_date <= evt.course_end_date and
           ev.course_end_date >= evt.course_start_date
          ))
    order by bst.type;

Line: 1079

    select bst.type
    from ota_delegate_bookings db,
         ota_booking_status_types bst,
         ota_events ev,
         ota_events evt
    where db.delegate_contact_id = p_delegate_contact_id
      and db.booking_status_type_id = bst.booking_status_type_id
      and bst.type <> g_cancelled_booking
      and db.event_id = ev.event_id
      and evt.event_id = p_event_id
      and ev.event_id <> p_event_id
      and ev.course_start_date <= evt.course_end_date
      and ev.course_end_date >= evt.course_start_date
      order by bst.type;

Line: 1176

SELECT bg.name
  FROM hr_all_organization_units org,
     hr_all_organization_units bg
 WHERE org.business_group_id = bg.organization_id
   AND org.organization_id = p_organization_id;

Line: 1336

SELECT lookup.meaning   Dm_Name,
       catusg.category  Dm_Code
  FROM ota_act_cat_inclusions actcat,
       ota_category_usages catusg,
       hr_lookups lookup
 WHERE actcat.category_usage_id=  catusg.category_usage_id
   AND actcat.primary_flag='Y'
   AND catusg.category = lookup.lookup_code
   AND lookup.lookup_type ='ACTIVITY_CATEGORY'
   AND catusg.type='DM'
   AND actcat.activity_version_id = p_activity_version_id;

Line: 1349

SELECT lookup.meaning   dm_name,
       catusg.category  dm_code
  FROM ota_act_cat_inclusions actcat,
       ota_category_usages catusg,
       hr_lookups lookup
 WHERE actcat.category_usage_id=  catusg.category_usage_id
   AND catusg.category = lookup.lookup_code
   AND lookup.lookup_type ='ACTIVITY_CATEGORY'
   AND catusg.type='DM'
   AND actcat.activity_version_id = p_activity_version_id;

Line: 1363

SELECT meaning
  FROM hr_lookups
 WHERE lookup_type ='ACTIVITY_CATEGORY'
   AND lookup_code = 'INCLASS';

Line: 1433

select ocu.category
from ota_category_usages_tl ocu , ota_offerings oaf
where oaf.delivery_mode_id = ocu.category_usage_id
and oaf.offering_id = p_offering_id
and ocu.Language = USERENV('LANG');

Line: 1476

   SELECT COUNT(booking_id)
   INTO l_num_waitlisted
   FROM ota_delegate_bookings tdb
   WHERE tdb.event_id = p_event_id
     AND tdb.booking_status_type_id IN (SELECT bst.booking_status_type_id
                                        FROM ota_booking_status_types bst
                                        WHERE bst.type = 'W');

Line: 1520

 SELECT tdb.booking_id
   FROM ota_delegate_bookings tdb,
        ota_booking_status_types bst
  WHERE tdb.booking_status_type_id = bst.booking_status_type_id
    AND bst.type = 'W'
    AND tdb.event_id = p_event_id
  ORDER BY tdb.date_booking_placed;

Line: 1529

 SELECT tdb.booking_id
   FROM ota_delegate_bookings tdb,
        ota_booking_status_types bst
  WHERE tdb.booking_status_type_id = bst.booking_status_type_id
    AND bst.type = 'W'
    AND tdb.event_id = p_event_id
  ORDER BY tdb.booking_priority,
           tdb.booking_id;

Line: 1596

SELECT s.location_id
  FROM ota_suppliable_resources s,
       ota_resource_bookings r
 WHERE r.supplied_resource_id = s.supplied_resource_id
   AND r.event_id = p_event_id
   AND primary_venue_flag = 'Y';

Line: 1605

SELECT e.location_id,
       e.training_center_id
  FROM ota_events e
 WHERE e.event_id = p_event_id;

Line: 1615

SELECT o.location_id
  FROM hr_all_organization_units o
 WHERE o.organization_id = p_training_center_id;

Line: 1682

SELECT employee_id
  FROM fnd_user
 WHERE user_id = fnd_profile.value('USER_ID');

Line: 1748

   SELECT DECODE(per.last_name, NULL, NULL, per.last_name)||
          DECODE(per.title, NULL, DECODE(per.first_name, NULL, NULL, ', '), ', '||per.title)||
     --Modified for Bug#2997820
          --DECODE(per.first_name,NULL,NULL, per.last_name) full_name
          DECODE(per.first_name,NULL,NULL, per.first_name) full_name
   FROM   per_all_people_f per, fnd_user u
   WHERE  per.person_id = u.employee_id
     AND  (per.effective_end_date >= DECODE(p_course_end_date, NULL, TRUNC(SYSDATE), p_course_end_date) AND
           per.effective_start_date <= DECODE(p_course_start_date, NULL, TRUNC(SYSDATE), p_course_start_date))
     AND  u.user_id = p_authorizer_id;

Line: 1759

  SELECT  decode(fnd_profile.value('BEN_DISPLAY_EMPLOYEE_NAME'),'FN',per.full_name,
                  per.first_name||' '|| per.last_name||' '||per.suffix) FULL_NAME
   FROM per_all_people_f per, fnd_user u
   WHERE per.person_id = u.employee_id
   AND u.user_id = p_authorizer_id
   AND trunc(SYSDATE) between per.effective_start_date and per.effective_end_date;

Line: 1935

SELECT lookup.meaning   Dm_Name,
       catusg.category  Dm_Code
  FROM ota_act_cat_inclusions actcat,
       ota_category_usages catusg,
       hr_lookups lookup
 WHERE actcat.category_usage_id=  catusg.category_usage_id
   AND actcat.primary_flag='Y'
   AND catusg.category = lookup.lookup_code
   AND lookup.lookup_type ='ACTIVITY_CATEGORY'
   AND catusg.type='C'
   AND actcat.activity_version_id = p_activity_version_id;

Line: 1948

SELECT lookup.meaning   dm_name,
       catusg.category  dm_code
  FROM ota_act_cat_inclusions actcat,
       ota_category_usages catusg,
       hr_lookups lookup
 WHERE actcat.category_usage_id=  catusg.category_usage_id
   AND catusg.category = lookup.lookup_code
   AND lookup.lookup_type ='ACTIVITY_CATEGORY'
   AND catusg.type='C'
   AND actcat.activity_version_id = p_activity_version_id;

Line: 2020

    SELECT count(*)
    FROM   ota_offerings
    WHERE  learning_object_id = p_learning_object_id;

Line: 2058

    SELECT count(*)
    FROM   ota_offerings
    WHERE  activity_version_id = p_activity_version_id;

Line: 2096

    SELECT rco_id
    FROM   ota_activity_versions
    WHERE  activity_version_id = p_activity_version_id;

Line: 2138

    SELECT count(event_id)
    FROM   ota_events
    WHERE  parent_offering_id = p_offering_id and
           offering_id is not null;

Line: 2144

    SELECT count(event_id)
    FROM   ota_events
    WHERE  parent_offering_id = p_offering_id and
           event_type in ('SELFPACED','SCHEDULED') and
           book_independent_flag = 'N';

Line: 2191

    SELECT count(*)
    FROM   ota_question_banks
    WHERE  folder_id = p_folder_id;

Line: 2212

  SELECT DECODE(BST.type,'C','Y',BST.type) status, BST.name
  FROM   ota_booking_status_types_vl BST,
         ota_delegate_bookings ODB
  WHERE  ODB.event_id = p_event_id
  AND    (p_delegate_person_id IS NOT NULL AND ODB.delegate_person_id = p_delegate_person_id
            OR p_delegate_contact_id IS NOT NULL and ODB.delegate_contact_id = p_delegate_contact_id)
  AND    ODB.booking_status_type_id = BST.booking_status_type_id
  ORDER BY status;

Line: 2257

select
p.full_name person_name
from per_people_f p
where
p.person_id = p_user_id
and sysdate between p.effective_start_date and p.effective_end_date;

Line: 2265

select
p.party_name person_name
from  hz_parties p
where
p.party_id  = p_user_id;

Line: 2296

select
p.full_name person_name
from per_people_f p , fnd_user fus
where p.person_id = fus.employee_id
and
fus.user_id = p_user_id;

Line: 2326

select pap.full_name from per_all_people_f pap
where  pap.person_id = p_person_id
       and trunc(sysdate) between nvl(pap.effective_start_date, trunc(sysdate))
       and nvl(pap.effective_end_date, trunc(sysdate));

Line: 2332

SELECT
    SUBSTRB( PARTY.PERSON_LAST_NAME,1,50) || ' ' ||
    SUBSTRB( PARTY.PERSON_FIRST_NAME,1,40) || ' ' ||
    HR_GENERAL.DECODE_AR_LOOKUP('CONTACT_TITLE',nvl(PARTY.PERSON_PRE_NAME_ADJUNCT,PARTY.PERSON_TITLE)) LEARNER_NAME
FROM
    HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
    HZ_PARTIES PARTY,
    HZ_RELATIONSHIPS REL,
    HZ_CUST_ACCOUNTS ROLE_ACCT
WHERE
    ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ACCT_ROLE.CUST_ACCOUNT_ID = l_customer_id
AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id;

Line: 2392

select ACCT_ROLE.cust_account_id CUSTOMER_ID
from  HZ_CUST_ACCOUNT_ROLES acct_role,
      HZ_PARTIES party,
      HZ_RELATIONSHIPS rel,
      HZ_ORG_CONTACTS org_cont,
      HZ_PARTIES rel_party,
      HZ_CUST_ACCOUNTS role_acct
where acct_role.party_id = rel.party_id
   and acct_role.role_type = 'CONTACT'
   and org_cont.party_relationship_id = rel.relationship_id
   and rel.subject_id = party.party_id
   and rel.party_id = rel_party.party_id
   and rel.subject_table_name = 'HZ_PARTIES'
   and rel.object_table_name = 'HZ_PARTIES'
   and acct_role.cust_account_id = role_acct.cust_account_id
   and role_acct.party_id	= rel.object_id
   and ACCT_ROLE.cust_account_role_id = p_contact_id;

Line: 2430

select name from hr_all_organization_units_tl
where  language = userenv('LANG') and organization_id = p_organization_id;

Line: 2434

select substrb(party.party_name,1,50)
from   hz_parties party
      ,hz_cust_accounts cust_acct
where
      cust_acct.party_id = party.party_id
and   cust_acct.cust_account_id = l_customer_id;

Line: 2488

select nvl(parent_cat_usage_id,-1)
        from ota_category_usages
        where category_usage_id = current_cat_usage_id;

Line: 2525

select nvl(parent_learning_object_id,-1)
        from ota_learning_objects
        where learning_object_id  = current_lo_id;

Line: 2531

select folder_id
        from ota_learning_objects
        where learning_object_id = current_lo_id;

Line: 2537

select nvl(parent_folder_id,-1)
        from ota_lo_folders
        where folder_id = current_folder_id;

Line: 2654

SELECT event_status from ota_events
where
event_id = p_event_id;

Line: 2689

SELECT  ppt.system_person_type
  FROM    per_all_people_f per,
          per_person_type_usages_f ptu,
          per_person_types ppt
  WHERE
         per.person_id = p_person_id
  AND    ptu.person_id = per.person_id
  AND    ppt.business_group_id = per.business_group_id
  AND    ptu.person_type_id = ppt.person_type_id
  AND    trunc(sysdate) between per.effective_start_date AND per.effective_end_date
  AND    trunc(sysdate) between ptu.effective_start_date AND ptu.effective_end_date
  AND ppt.system_person_type <> 'APL'
  AND ppt.system_person_type in ('EMP','CWK');

Line: 2748

select party.party_id
from  HZ_CUST_ACCOUNT_ROLES acct_role,
      HZ_PARTIES party,
      HZ_RELATIONSHIPS rel,
      HZ_ORG_CONTACTS org_cont,
      HZ_PARTIES rel_party,
      HZ_CUST_ACCOUNTS role_acct
where acct_role.party_id = rel.party_id
   and acct_role.role_type = 'CONTACT'
   and org_cont.party_relationship_id = rel.relationship_id
   and rel.subject_id = party.party_id
   and rel.party_id = rel_party.party_id
   and rel.subject_table_name = 'HZ_PARTIES'
   and rel.object_table_name = 'HZ_PARTIES'
   and acct_role.cust_account_id = role_acct.cust_account_id
   and role_acct.party_id	= rel.object_id
   and ACCT_ROLE.cust_account_role_id = p_delegate_contact_id;

Line: 2786

  SELECT null
  FROM ota_events
  WHERE event_id = p_class_id
    --AND trunc(sysdate) between enrolment_start_date and nvl(enrolment_end_date, trunc(sysdate))
    AND ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, timezone)
       BETWEEN to_date(to_char(nvl(enrolment_start_date,trunc(sysdate)),'YYYY/MM/DD') || ' ' || '00:00' , 'YYYY/MM/DD HH24:MI')
       AND to_date(to_char(nvl(enrolment_end_date,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' ' || '23:59', 'YYYY/MM/DD HH24:MI')
    AND event_type IN ('SCHEDULED', 'SELFPACED')
    AND event_status IN ('N', 'P', 'F');

Line: 2814

   SELECT null
   FROM ota_learning_paths
   WHERE learning_path_id = p_learning_path_id
    AND trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate));

Line: 2820

  SELECT null
  from ota_lp_sections lpc,
     ota_learning_path_members lpm
  where lpc.learning_path_id = p_learning_path_id
  and lpc.learning_path_section_id = lpm.learning_path_section_id
  and lpc.completion_type_code in ('M','S');

Line: 2855

   SELECT null
   FROM ota_certifications_b crb
     , ota_certification_members crm
   WHERE crb.certification_id = crm.certification_id
     AND crb.certification_id = p_certification_id
     AND trunc(sysdate) between crb.start_date_active and nvl(crb.end_date_active, trunc(sysdate))
     -- Added for bug#4617609, modified for 4940007
     and ((crb.renewable_flag = 'N' and trunc(sysdate) <= nvl( crb.INITIAL_COMPLETION_DATE ,trunc(sysdate)))
          or crb.renewable_flag = 'Y');

Line: 3322

 SELECT null
 FROM ota_event_associations
 WHERE event_id = p_event_id
  AND customer_id IS NOT NULL;

Line: 3350

  SELECT NULL
  FROM per_all_assignments_f
  WHERE person_id = p_person_id
    AND trunc(sysdate) between effective_start_date and effective_end_date
    AND organization_id = p_sponsor_org_id
    AND assignment_type in ('E', 'C', 'A');

Line: 3376

  SELECT l.lookup_code
    FROM fnd_lookup_values l
  WHERE l.lookup_type = 'ENROLMENT_STATUS_REASON'
   AND l.meaning = p_meaning
   AND l.enabled_flag = 'Y'
   and rownum=1;

Line: 3386

   SELECT bsh.comments
    FROM ota_booking_status_histories bsh
  WHERE bsh.booking_id = p_booking_id
   AND bsh.start_date =
    (SELECT MAX(start_date)
     FROM ota_booking_status_histories
     WHERE booking_id = p_booking_id);

Line: 3395

  SELECT meaning
  FROM hr_lookups
  WHERE lookup_type = 'ENROLMENT_STATUS_REASON'
   AND enabled_flag = 'Y'
   AND lookup_code = p_lookup_code;

Line: 3445

  select name
   from ota_natural_languages_v
   where language_code  = p_language_code;

Line: 3482

SELECT lpe.lp_enrollment_id
FROM ota_lp_enrollments lpe,
     ota_lp_member_enrollments lpme
WHERE lpe.lp_enrollment_id = lpme.lp_enrollment_id AND
      lpe.enrollment_source_code = 'ADMIN' AND
      lpe.person_id = p_person_id AND
      lpme.event_id = p_event_id;

Line: 3491

SELECT lpe.lp_enrollment_id
FROM ota_lp_enrollments lpe,
     ota_lp_member_enrollments lpme
WHERE lpe.lp_enrollment_id = lpme.lp_enrollment_id AND
      lpe.enrollment_source_code = 'ADMIN' AND
      lpe.contact_id = p_contact_id AND
      lpme.event_id = p_event_id;

Line: 3500

SELECT decode(p_manager_flag,'NOT_MANAGER',decode(nvl(p_mandatory_flag,'N'),'Y','UD','N',decode(p_booking_status_type, 'A','UD', 'UE')),
                         'IS_MANAGER',decode(p_booking_status_type, 'A','UD', 'UE')) enroll_image
FROM DUAL;

Line: 3536

select paf.email_address
from ota_delegate_bookings odb,
      ota_booking_status_types obst,
      per_all_people_f paf
where odb.event_id = p_event_id and
odb.booking_status_type_id = obst.booking_status_type_id and
obst.type in ('A', 'P', 'E') and
odb.delegate_person_id = paf.person_id and
trunc(sysdate) between paf.effective_start_date and paf.effective_end_date and
paf.email_address is not null;

Line: 3548

SELECT PARTY.EMAIL_ADDRESS
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
    HZ_PARTIES PARTY,
    HZ_RELATIONSHIPS REL,
    HZ_CUST_ACCOUNTS ROLE_ACCT,
    HZ_ROLE_RESPONSIBILITY ROL ,
    HZ_ORG_CONTACTS ORG_CONT,
    HZ_LOCATIONS LOC,
    HZ_PARTY_SITES PARTY_SITE,
    HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
    ota_delegate_bookings odb,
    ota_booking_status_types obst
WHERE
    ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROL.PRIMARY_FLAG (+) = 'Y'
AND ROL.CUST_ACCOUNT_ROLE_ID (+) = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID(+)
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+)
AND ACCT_SITE.CUST_ACCT_SITE_ID (+) = ACCT_ROLE.CUST_ACCT_SITE_ID
AND ACCT_ROLE.STATUS = 'A'
AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = odb.delegate_contact_id
AND odb.event_id = p_event_id
AND odb.booking_status_type_id = obst.booking_status_type_id
AND obst.type in ('A', 'P', 'E')
AND PARTY.EMAIL_ADDRESS is not null;

Line: 3618

  SELECT null
  FROM ota_events
  WHERE event_id = p_class_id

    AND ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, timezone)
       BETWEEN to_date(to_char(nvl(enrolment_start_date,trunc(sysdate)),'YYYY/MM/DD') || ' ' || '00:00' , 'YYYY/MM/DD HH24:MI')
       AND to_date(to_char(nvl(enrolment_end_date,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' ' || '23:59', 'YYYY/MM/DD HH24:MI')
    AND event_type IN ('SCHEDULED', 'SELFPACED')
    AND event_status IN ('N', 'P', 'F');