APPS.OTA_TDB_BUS SQL Statements

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

Line: 65

Procedure chk_non_updateable_args
  (
   p_rec in ota_tdb_shd.g_rec_type
  ) IS
--
  l_proc     varchar2(72) := g_package || 'chk_non_updateable_args';

Line: 110

End chk_non_updateable_args;

Line: 150

    select pbg.security_group_id,
           pbg.legislation_code
      from per_business_groups_perf pbg
         , ota_delegate_bookings tdb
     where tdb.booking_id = p_booking_id
       and pbg.business_group_id = tdb.business_group_id;

Line: 245

    select pbg.legislation_code
      from per_business_groups_perf pbg
        , ota_delegate_bookings tdb
     where tdb.booking_id = p_booking_id
       and pbg.business_group_id = tdb.business_group_id;

Line: 392

    select type
    from ota_booking_status_types
    where booking_status_type_id = p_booking_status_type_id;

Line: 450

select 1
from per_all_people_f
where person_id = p_person_id
and p_date between effective_start_date and effective_end_date;

Line: 460

select 1

     from      HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
               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_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_person_id;

Line: 589

    select legislation_code
    from   per_business_groups
    where  business_group_id=p_business_group_id;

Line: 632

  SELECT  paf.ROWID
  FROM    per_all_assignments_f paf, ota_events evt
  WHERE    paf.assignment_id = p_assignment_id
  AND      evt.event_id = p_event_id
  AND      (
                  evt.event_status = 'P'
              AND evt.enrolment_start_date BETWEEN paf.effective_start_date
                      AND paf.effective_end_date
            OR (
                    evt.event_type = 'PROGRAMME'
                AND p_date_booking_placed BETWEEN paf.effective_start_date
                        AND paf.effective_end_date)
            OR (
          -- Modified for Bug#3596070
                p_date_booking_placed --evt.course_start_date
          BETWEEN paf.effective_start_date AND paf.effective_end_date) );

Line: 653

  SELECT  paf.ROWID
  FROM    per_all_assignments_f paf, ota_events evt ,
          per_all_people_f ppf,
          per_person_type_usages_f ptu,
          per_person_types ppt
  WHERE    paf.person_id = l_person_id
  AND      ppf.person_id = l_person_id
  AND      ptu.person_id = ppf.person_id
  AND      evt.event_id = p_event_id
  AND      (
                  evt.event_status = 'P'
              AND evt.enrolment_start_date BETWEEN paf.effective_start_date
                      AND paf.effective_end_date
            OR (
                    evt.event_type = 'PROGRAMME'
                AND p_date_booking_placed BETWEEN paf.effective_start_date
                        AND paf.effective_end_date)
            OR (
         -- Modified for Bug#3596070
                p_date_booking_placed --evt.course_start_date
      BETWEEN paf.effective_start_date AND paf.effective_end_date) )
  AND      (
                 ( evt.event_status = 'P'
              AND evt.enrolment_start_date BETWEEN ppf.effective_start_date
                      AND ppf.effective_end_date
              AND evt.enrolment_start_date BETWEEN ptu.effective_start_date
                        AND ptu.effective_end_date)
            OR (
                    evt.event_type = 'PROGRAMME'
                AND p_date_booking_placed BETWEEN ppf.effective_start_date
                        AND ppf.effective_end_date
                AND p_date_booking_placed BETWEEN ptu.effective_start_date
                        AND ptu.effective_end_date)
            OR (
        -- Modified for Bug#3596070
                p_date_booking_placed   --evt.course_start_date
           BETWEEN ppf.effective_start_date  AND ppf.effective_end_date
                 AND
          -- Modified for Bug#3596070
                p_date_booking_placed   --evt.course_start_date
              BETWEEN ptu.effective_start_date AND ptu.effective_end_date ) )
  AND   ppt.business_group_id = ppf.business_group_id
  AND ppf.business_group_id = paf.business_group_id
  AND   ptu.person_type_id = ppt.person_type_id
  AND   ppt.system_person_type in ('EMP','CWK','APL') ; -- Added 'APL' for 3885568

Line: 704

  SELECT  paf.ROWID
  FROM    per_all_assignments_f paf
  WHERE    paf.assignment_id = p_assignment_id
  AND      p_date_booking_placed BETWEEN paf.effective_start_date
              AND paf.effective_end_date;

Line: 714

  SELECT  paf.ROWID
  FROM    per_all_assignments_f paf ,
             per_all_people_f ppf,
          per_person_type_usages_f ptu,
          per_person_types ppt
  WHERE    paf.person_id = l_person_id
  AND      ppf.person_id = l_person_id
  AND      ptu.person_id = ppf.person_id
  AND      p_date_booking_placed BETWEEN paf.effective_start_date
              AND paf.effective_end_date
  AND      p_date_booking_placed BETWEEN ppf.effective_start_date
              AND ppf.effective_end_date
  AND   p_date_booking_placed BETWEEN ptu.effective_start_date
                AND ptu.effective_end_date
  AND   ppt.business_group_id = ppf.business_group_id
  AND ppf.business_group_id = paf.business_group_id
  AND   ptu.person_type_id = ppt.person_type_id
  AND   ppt.system_person_type in ('EMP','CWK','APL')    ;  -- Added 'APL' for 3885568

Line: 736

select person_id ,rowid
from per_all_assignments_f
where assignment_id = p_assignment_id ;

Line: 952

    select maximum_internal_attendees
    from   ota_events
    where  event_id = p_event_id;

Line: 959

    select sum(a.number_of_places)
    from   ota_delegate_bookings a,
           ota_booking_status_types b
    where  a.event_id = p_event_id
    and    a.booking_status_type_id = b.booking_status_type_id
    and    b.type in ('P','A','E') --6683076.Added new enrollment status.
    and    a.internal_booking_flag = 'Y'
    and    a.booking_id <> nvl(p_booking_id, hr_api.g_number);

Line: 1152

    insert into ota_booking_status_histories
                  (booking_id,
                   booking_status_type_id,
                   start_date,
                   changed_by,
                   comments,
                   object_version_number)
           values (p_booking_id,
                   p_previous_status_type_id,
                   p_date_booking_placed,
                   p_created_by,
                   'Enrolled',
                   1);

Line: 1170

  insert into ota_booking_status_histories
                (booking_id,
                 booking_status_type_id,
                 start_date,
                 changed_by,
                 comments,
                 object_version_number)
         values
                (p_booking_id,
                 p_booking_status_type_id,
                 l_date_changed,
                 p_administrator,
                 p_status_change_comments,
                 1);

Line: 1204

    select 'X'
    from ota_resource_allocations
    where booking_id = p_booking_id;

Line: 1244

select  nvl(sum(booking_id),0)
from    ota_finance_lines tfl
where   tfl.booking_id = p_booking_id;

Line: 1293

    select 'Y'
    from OTA_TRAINING_PLAN_COSTS
    where booking_id = p_booking_id;

Line: 1332

    select 'X'
    from ota_booking_status_types
    where booking_status_type_id = p_booking_status_type_id
      and business_group_id = p_business_group_id;

Line: 1374

    select event_type
    from ota_events
    where event_id = p_event_id;

Line: 1446

    select booking_id
    from   ota_delegate_bookings
    where  event_id = p_event_id
    and    delegate_contact_id = p_person_id;

Line: 1452

    select booking_id
    from   ota_delegate_bookings
    where  event_id = p_event_id
    and    delegate_person_id = p_person_id;

Line: 1459

    select bst.type
    from   ota_delegate_bookings tdb,
           ota_booking_status_types bst
    where tdb.booking_id = l_result
    and   bst.booking_status_type_id = tdb.booking_status_type_id;

Line: 1468

    select bst.type
    from   ota_delegate_bookings tdb,
           ota_booking_status_types bst
    where tdb.booking_id = l_result
    and   bst.booking_status_type_id = tdb.booking_status_type_id;

Line: 1577

select  nvl(sum(booking_id),0)
from    ota_finance_lines tfl
where   tfl.booking_id = p_booking_id
and     tfl.cancelled_flag = p_cancelled_flag;

Line: 1662

  SELECT 1
  FROM ota_events e
  WHERE
    -- Added for bug#5169098
    ota_timezone_util.convert_date(trunc(sysdate), to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, e.timezone)
      >= ota_timezone_util.convert_date(nvl(e.course_start_date,to_date('0001/01/01','YYYY/MM/DD')), course_start_time, e.timezone, e.timezone)
   --(sysdate >= nvl( e.course_start_date, sysdate))
    AND e.event_id = p_event_id;

Line: 1675

  SELECT name
  FROM ota_booking_status_types bst
  WHERE bst.booking_status_type_id = p_booking_status_type_id
  AND bst.type = 'A';

Line: 1752

    select count(event_association_id)
    from ota_event_associations
    where event_id = p_event_id;

Line: 1758

    select course_start_date
    from   ota_events
    where  event_id = p_event_id;

Line: 1764

    select 'X'
    from ota_events b
    where b.event_id = p_event_id
    and public_event_flag = 'N';

Line: 1770

    select 'X'
    from   ota_event_associations
    where  (event_id = p_event_id
    and    ((p_customer_id is not null and customer_id = p_customer_id)
            or (l_party_id is not null and party_id =l_party_id)));  /* bug 3463908 */

Line: 1777

    or not exists (select null
                   from   ota_event_associations evt
                   where  evt.event_id = p_event_id);*/

Line: 1783

    select organization_id, job_id, position_id
    from   per_assignments_f
    where  assignment_id = p_delegate_assignment_id
    and    NVL(l_start_date,TRUNC(sysdate))
           between effective_start_date
           and effective_end_date;

Line: 1794

    select 'Y'
    from   ota_event_associations
    where  (event_id = p_event_id
    and     nvl(organization_id,-1) = decode(organization_id,null,-1,nvl(l_organization_id,-1))
    and     nvl(position_id,-1) = decode(position_id,null,-1,nvl(l_position_id,-1))
    and     nvl(job_id,-1) = decode(job_id,null,-1,nvl(l_job_id,-1)))
    or not exists (select null
                   from   ota_event_associations evt
                   where  evt.event_id = p_event_id);

Line: 1805

    select price_basis
    from ota_events
    where event_id = p_event_id;

Line: 1812

  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: 1832

   Select public_event_flag, maximum_internal_attendees, course_start_date, parent_offering_id
   From ota_events
   Where event_id = p_event_id;

Line: 1920

       Select public_event_flag,  maximum_internal_attendees, course_start_date, parent_offering_id
		into  l_public_event_flag, l_max_internal, l_event_start_date, l_parent_offering_id
       From ota_events
       Where event_id = p_event_id;

Line: 2027

    select nvl(sum(db.number_of_places),0)
    from ota_delegate_bookings    db,
         ota_booking_status_types bst
    where bst.booking_status_type_id = nvl(p_booking_status_type_id,
                                          bst.booking_status_type_id)
      and bst.type = nvl(p_status_type, bst.type)
      and (p_usage_type is null or
           ota_tdb_bus.event_place_needed(bst.booking_status_type_id) = 1)
      and bst.booking_status_type_id = db.booking_status_type_id
      and (p_booking_id is null or
           p_booking_id is not null and db.booking_id <> p_booking_id)
      and db.internal_booking_flag = decode(p_all_or_internal,
                                            'INTERNAL','Y',
                                            db.internal_booking_flag)
      and db.event_id = p_event_id;

Line: 2119

    select 'X'
    from   ota_delegate_bookings
    where  customer_id = p_customer_id
    and    event_id in (select a.program_event_id
                        from   ota_program_memberships a
                        where  a.event_id = p_event_id)
    and    (
            (p_booking_id is not null and booking_id <> p_booking_id
             )
            or
             p_booking_id is null
           )
    and    (delegate_contact_id = p_delegate_contact_id
            or
            (delegate_contact_id is null and p_delegate_contact_id is null
            )
           );

Line: 2138

    select 'X'
    from   ota_delegate_bookings
    where  event_id in (select a.program_event_id
                        from   ota_program_memberships a
                        where  a.event_id = p_event_id)
    and    (
            (p_booking_id is not null and booking_id <> p_booking_id
             )
            or
             p_booking_id is null
           )
    and    (delegate_person_id = p_delegate_person_id
            or
            (delegate_person_id is null and p_delegate_person_id is null
            )
           );

Line: 2158

    select null
    from   ota_program_memberships a
    where  a.event_id = p_event_id;

Line: 2252

    select type
    from   ota_booking_status_types
    where  booking_status_type_id = p_booking_status_type_id;

Line: 2387

  select enrolment_start_date
  from ota_events
  where event_id = nvl(g_event_rec.parent_event_id, -1);

Line: 2441

  select enrolment_start_date,
         enrolment_end_date,
	 timezone
  from ota_events
  where event_id = nvl(g_event_rec.parent_event_id, -1);

Line: 2512

  select enrolment_start_date,
         enrolment_end_date,
	 timezone,
	 event_type
  from ota_events
  where event_id = p_event_id;

Line: 2654

    select per_letter_requests_s.nextval
    from   dual;

Line: 2668

    insert into per_letter_requests
                   (letter_request_id
                   ,business_group_id
                   ,letter_type_id
                   ,date_from
                   ,request_status
                   ,auto_or_manual
                   ,event_id)
    select l_request_id
    ,      a.business_group_id
    ,      p_letter_type_id
    ,      sysdate
    ,      'PENDING'
    ,      'AUTO'
    ,      p_event_id
    from   per_letter_types a
    where  a.letter_type_id = p_letter_type_id;

Line: 2695

    select letter_request_id
    from   per_letter_requests
    where  letter_type_id = p_letter_type_id
      and  request_status = 'PENDING';

Line: 2701

    select per_letter_requests_s.nextval
    from   dual;

Line: 2719

    insert into per_letter_requests
                   (letter_request_id
                   ,business_group_id
                   ,letter_type_id
                   ,date_from
                   ,request_status
                   ,auto_or_manual)
    select l_request_id
    ,      a.business_group_id
    ,      p_letter_type_id
    ,      sysdate
    ,      'PENDING'
    ,      'AUTO'
    from   per_letter_types a
    where  a.letter_type_id = p_letter_type_id;

Line: 2768

      select b.letter_type_id,
             c.letter_request_id
       from  per_letter_gen_statuses a,
             per_letter_types b,
             per_letter_requests c
      where  a.assignment_status_type_id = p_booking_status_type_id
        and  b.letter_type_id = a.letter_type_id
        and  b.generation_status_type = 'OTA_BOOKING'
        and  c.letter_type_id = b.letter_type_id
        and  c.event_id= p_event_id
        and  c.request_status = 'PENDING'
        and  c.auto_or_manual = 'AUTO'
        and  a.enabled_flag='Y'; ---***added for bug#2791524;

Line: 2783

      select b.letter_type_id,
             c.letter_request_id
       from  per_letter_gen_statuses a,
             per_letter_types b,
             per_letter_requests c
      where  a.assignment_status_type_id = p_booking_status_type_id
        and  b.letter_type_id = a.letter_type_id
        and  b.generation_status_type = 'OTA_BOOKING'
        and  c.letter_type_id = b.letter_type_id
        AND  c.event_id IS null
        and  c.request_status = 'PENDING'
        and  c.auto_or_manual = 'AUTO'
        and  a.enabled_flag='Y'; ---***added for bug#2791524;

Line: 2798

      select a.letter_type_id
      from   per_letter_gen_statuses a,
             per_letter_types b
      where  a.assignment_status_type_id = cp_status_id
      and    b.letter_type_id = a.letter_type_id
      and    b.generation_status_type = 'OTA_BOOKING'
      and    a.enabled_flag='Y'; ---***added for bug#2791524;

Line: 2807

      select null
      from   per_letter_request_lines
      where  ota_booking_id = p_booking_id
      and    ota_booking_status_type_id = p_booking_status_type_id
      and    letter_request_id = l_letter_request_id;

Line: 2815

     select business_group_id
     from ota_events
     where event_id = p_event_id;

Line: 2878

               insert into per_letter_request_lines
                (letter_request_line_id,
                 business_group_id,
                 letter_request_id,
                 person_id,
                 ota_booking_id,
                 ota_booking_status_type_id,
                 date_from
                )
               values
                (per_letter_request_lines_s.nextval,
                 l_business_group_id,
                 l_letter_req_id,
                 p_delegate_person_id,
                 p_booking_id,
                 p_booking_status_type_id,
                 trunc(sysdate)
                );

Line: 2913

      select a.letter_type_id
      ,      a.business_group_id
      from   per_letter_gen_statuses a
      ,      per_letter_types b
      where  a.assignment_status_type_id = cp_status_id
      and    b.letter_type_id = a.letter_type_id
      and    b.generation_status_type = 'OTA_BOOKING';

Line: 2922

      select letter_request_id
      from   per_letter_requests
      where  letter_type_id = p_letter_type_id
      and    request_status = 'PENDING'
      and    auto_or_manual = 'AUTO';

Line: 2929

      select null
      from   per_letter_request_lines
      where  ota_booking_id = p_booking_id
      and    ota_booking_status_type_id = p_booking_status_type_id
      and    letter_request_id = l_letter_request_id;

Line: 2962

            insert into per_letter_request_lines
                (letter_request_line_id,
                 business_group_id,
                 letter_request_id,
                 ota_booking_id,
                 ota_booking_status_type_id,
                 date_from
                )
            values
                (per_letter_request_lines_s.nextval,
                 r_letters.business_group_id,
                 l_letter_request_id,
                 p_booking_id,
                 p_booking_status_type_id,
                 trunc(sysdate)
                );

Line: 3040

  select 1
  from  ota_delegate_bookings tdb,
        ota_program_memberships pmm
  where tdb.event_id = pmm.event_id
  and   pmm.program_event_id = l_event_id
  and   tdb.delegate_person_id = l_delegate_person_id;

Line: 3048

  select 1
  from  ota_delegate_bookings tdb,
        ota_program_memberships pmm
  where tdb.event_id = pmm.event_id
  and   pmm.program_event_id = l_event_id
  and   tdb.delegate_contact_id = l_delegate_contact_id;

Line: 3117

     select null
     from oe_order_lines_all
     where line_id = p_line_id;

Line: 3206

Select LINE_ID
FROM OTA_EVENTS
WHERE EVENT_ID = p_event_id
AND   LINE_ID IS NOT NULL ;

Line: 3372

   SELECT organization_id
   FROM ota_events
   WHERE event_id = p_event_id
     AND nvl(secure_event_flag,'N') = 'Y';

Line: 3408

   SELECT oft.learning_object_id
         ,ctu.online_flag
         ,evt.offering_id
   FROM  ota_offerings oft
       , ota_events evt
       , ota_category_usages ctu
   WHERE oft.offering_id = evt.parent_offering_id
       AND ctu.category_usage_id = oft.delivery_mode_id
       AND evt.event_id = p_class_id;

Line: 3429

    SELECT lesson_status
    FROM ota_performances
    WHERE learning_object_id = p_lo_id
     AND user_id = p_user_id
     AND user_type = p_user_type
     AND lesson_status IN ('P', 'C');

Line: 3646

Procedure insert_validate(
                    p_rec in ota_tdb_shd.g_rec_type,
                          p_enrollment_type in varchar2
                          ) is
--
  l_proc  varchar2(72) := g_package||'insert_validate';

Line: 3911

End insert_validate;

Line: 3919

Procedure update_validate(
                          p_rec in ota_tdb_shd.g_rec_type,
                          p_enrollment_type in varchar2
                          ) is
--
  l_proc  varchar2(72) := g_package||'update_validate';

Line: 4017

SELECT nvl(avt.inventory_item_id,1)
FROM ota_events evt, ota_activity_versions avt
WHERE  evt.activity_version_id = avt.activity_version_id
AND evt.event_id = p_event_id ;

Line: 4030

   chk_non_updateable_args
    (
     p_rec              => p_rec
    );

Line: 4463

End update_validate;

Line: 4471

Procedure delete_validate(p_rec in ota_tdb_shd.g_rec_type) is
--
  l_proc  varchar2(72) := g_package||'delete_validate';

Line: 4503

End delete_validate;