APPS.OTA_TAV_API_BUSINESS_RULES SQL Statements

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

Line: 314

  select business_group_id
  from   ota_activity_definitions tad
  where  activity_id = p_activity_id;

Line: 319

select 'Y'
from ota_activity_versions_vl  tav
,    ota_activity_definitions tad
where tav.version_name = p_version_name
and   tav.activity_id  = tad.activity_id
and (p_activity_version_id is null or
    (p_activity_version_id is not null and
     tav.activity_version_id <> p_activity_version_id))
and   tad.business_group_id+0 = v_business_group_id;

Line: 383

    select end_date
      from ota_activity_versions    tav
     where tav.activity_version_id  =  p_sup_act_vers_id;

Line: 446

    select 1
      from ota_activity_versions  tav1
     where tav1.activity_id = p_activity_id
     and exists
         ( select 1
             from ota_activity_versions tav2
            where tav2.activity_id = p_activity_id
            and tav1.activity_version_id <> tav2.activity_version_id
            and tav2.start_date between
                tav1.start_date and nvl(tav1.end_date, hr_general.end_of_time)
         );

Line: 559

    select activity_version_id
      from ota_activity_versions_vl   tav
     where tav.activity_id    =    p_activity_id
       and tav.version_name   =    p_version_name;

Line: 621

    select version_name
      from ota_activity_versions_tl     tav
     where tav.activity_version_id   = p_activity_version_id
        and tav.language = USERENV('LANG');

Line: 686

Procedure check_dates_update_rud
  (
   p_activity_version_id   in    number
  ,p_start_date            in    date
  ,p_end_date              in    date
  ,p_old_start_date        in    date
  ,p_old_end_date          in    date
  ) is
  --
  v_start_date            date;

Line: 697

  v_proc                  varchar2(72) := g_package||'check_dates_update_rud';

Line: 700

    select start_date
         , end_date
      from ota_resource_usages       rud
     where rud.activity_version_id   = p_activity_version_id;

Line: 765

End check_dates_update_rud;

Line: 777

Procedure check_dates_update_ple
  (
   p_activity_version_id   in    number
  ,p_start_date            in    date
  ,p_end_date              in    date
  ) is
  --
  v_start_date            date;

Line: 786

  v_proc                  varchar2(72) := g_package||'check_dates_update_ple';

Line: 789

    select start_date
         , end_date
      from ota_price_list_entries    ple
     where ple.activity_version_id   = p_activity_version_id;

Line: 825

End check_dates_update_ple;

Line: 837

Procedure check_dates_update_tbd
  (
   p_activity_version_id   in    number
  ,p_start_date            in    date
  ,p_end_date              in    date
  ) is
  --
  v_start_date            date;

Line: 846

  v_proc                  varchar2(72) := g_package||'check_dates_update_tbd';

Line: 849

    select start_date
         , end_date
      from ota_booking_deals         tbd
     where tbd.activity_version_id   = p_activity_version_id;

Line: 885

End check_dates_update_tbd;

Line: 900

Procedure check_dates_update_evt
  (
   p_activity_version_id   in    number
  ,p_start_date            in    date
  ,p_end_date              in    date
  ) is
  --
  v_start_date            date;

Line: 909

  v_proc                  varchar2(72) := g_package||'check_dates_update_evt';

Line: 919

    select start_date
         , end_date
      from ota_offerings off -- bug 3534657
     where off.activity_version_id   = p_activity_version_id;

Line: 977

End check_dates_update_evt;

Line: 999

    select
      ctu.start_date_active,
      nvl(ctu.end_date_active, hr_api.g_eot)
    from
      ota_category_usages ctu,
      ota_act_cat_inclusions aci
    where
      ctu.category_usage_id = aci.category_usage_id
      and aci.activity_version_id = p_activity_version_id
      and ctu.type='C'
      and aci.primary_flag='Y';

Line: 1078

    select 'Y'
      from ota_events              evt
     where evt.activity_version_id = p_activity_version_id;

Line: 1126

    select 'Y'
      from ota_offerings             off
     where off.activity_version_id = p_activity_version_id;

Line: 1175

    Select 'Y'
    from OTA_TRAINING_PLAN_MEMBERS
    where activity_version_id = p_activity_version_id;

Line: 1221

    select 'Y'
      from ota_booking_deals         tbd
     where tbd.activity_version_id = p_activity_version_id;

Line: 1269

    select 'Y'
      from ota_price_list_entries  ple
     where ple.activity_version_id = p_activity_version_id;

Line: 1317

    select 'Y'
      from per_competence_elements  pce
     where pce.activity_version_id = p_activity_version_id;

Line: 1367

    select 'Y'
      from ota_learning_path_members  lpm
     where lpm.activity_version_id = p_activity_version_id;

Line: 1417

    select 'Y'
      from ota_activity_versions             tav
     where tav.superseded_by_act_version_id  =  p_activity_version_id;

Line: 1465

    select 'Y'
      from ota_skill_provisions             tsp
     where tsp.activity_version_id  =  p_activity_version_id;

Line: 1573

    select 'Y'
      from fnd_languages       lan
     where lan.language_id  =  p_language_id;

Line: 1669

    select 'Y'
      from ota_activity_versions    tav
     where tav.activity_id        = p_activity_id
     and ((tav.activity_version_id <> p_activity_version_id
     and p_activity_version_id is not null )
     or  p_activity_version_id is null)
     and  v_start_date <= nvl(tav.end_date,
                           greatest(v_default_end_date,tav.start_date))
     and  v_end_date   >= nvl(tav.start_date,hr_api.g_sot);

Line: 1754

    select 'Y'
      from ota_activity_definitions          tad
     where tad.activity_id                =  p_activity_id
       and tad.multiple_con_versions_flag =  'N' ;

Line: 1815

  select tav.activity_version_id
  ,      tav.start_date
  ,      tav.end_date
  ,      tav.object_version_number
  from   ota_activity_versions    tav
  ,      ota_activity_definitions tad
  where  tad.activity_id = p_activity_id
  and    tad.multiple_con_versions_flag = 'N'
  and    tav.activity_id = tad.activity_id
  and    tav.start_date =
    (select max(tav2.start_date)
     from   ota_activity_versions tav2
     where  tav2.activity_version_id <> p_activity_version_id
     and    tav2.activity_id = p_activity_id);

Line: 1892

    select tav.end_date
      from ota_activity_versions          tav
          , ota_activity_definitions       tad
     where tav.activity_id                =   tad.activity_id --p_activity_id
       and tad.activity_id = p_activity_id -- Bug 2808274
       and tad.multiple_con_versions_flag =  'N'
       order by tav.end_date desc;

Line: 1945

    select tav.superseded_by_act_version_id
      from ota_activity_definitions          tad
         , ota_activity_versions             tav
     where tad.activity_id                =  p_activity_id
       and tad.multiple_con_versions_flag =  'N'
       and tav.activity_id                =  tad.activity_id
       and nvl(tav.end_date, hr_api.g_eot) =
           (select max( nvl(tav2.end_date, hr_api.g_eot))
            from   ota_activity_versions tav2
            where  tav2.activity_id                =  p_activity_id);

Line: 1957

    select 'Y'
      from ota_activity_versions       tav
     where tav.activity_id          =  p_activity_id
       and tav.activity_version_id  =  pc_activity_version_id ;

Line: 2026

     select null
     from oe_order_lines_all
     where inventory_item_id = p_inventory_item_id and
           org_id = p_organization_id;

Line: 2032

     select null
     from oe_order_lines_all
     where inventory_item_id = l_old_inventory_item_id and
           org_id = l_old_organization_id;

Line: 2039

	select inventory_item_id,
             organization_id
	from ota_activity_versions
	where activity_version_id = p_activity_version_id;

Line: 2128

     select null
     from mtl_system_items_b
     where inventory_item_id = p_inventory_item_id and
           organization_id = p_organization_id;

Line: 2134

	select inventory_item_id,
		 organization_id
      from
      ota_activity_versions
      where activity_version_id <> p_activity_version_id;

Line: 2141

      select 1 from ota_offerings off,ota_category_usages  cat
      where  cat.type ='DM'
      and    (cat.online_flag = 'Y' or( cat.online_flag = 'N' and cat.synchronous_flag = 'N'))
      and    cat.category_usage_id = off.delivery_mode_id
      and    off.activity_version_id = p_activity_version_id;

Line: 2148

      select 1 from ota_offerings off,
              ota_events  evt,
              ota_delegate_bookings tdb
      where  evt.parent_offering_id = off.offering_id
      and    tdb.event_id = evt.event_id
      and    off.activity_version_id = p_activity_version_id;

Line: 2265

     select null
     from ota_activity_versions
     where rco_id = p_rco_id;

Line: 2331

SELECT lps.learning_path_id
FROM ota_learning_paths_vl lps,
  ota_learning_path_members lpm
WHERE lpm.learning_path_id = lps.learning_path_id
   AND lpm.activity_version_id = p_activity_version_id
   AND (( l_end_date IS NOT NULL AND lps.start_date_active > l_end_date)
           OR (lps.end_date_active IS NOT NULL AND l_start_date > lps.end_date_active));

Line: 2405

    select 'Y'
      from ota_notrng_histories        nth
     where nth.activity_version_id  =  p_activity_version_id;

Line: 2455

    select 'Y'
      from ota_certification_members        ctm
     where ctm.object_id  =  p_activity_version_id
     and ctm.object_type = 'H';

Line: 2515

SELECT ctm.certification_id
FROM ota_certification_members ctm
WHERE ctm.object_id = p_activity_version_id
   AND ctm.object_type='H'
   AND (
        (l_end_date IS NOT NULL AND ctm.start_date_active > l_end_date)
		 OR (l_end_date is not null and ctm.end_date_active is not null AND ctm.end_date_active > l_end_date)
         OR (ctm.start_date_active < l_start_date)
		 OR (ctm.end_date_active IS NOT NULL AND l_start_date > ctm.end_date_active)
	   )
    ;