APPS.OTA_REPROCESS_CLASSIC_UPGRADE SQL Statements

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

Line: 25

    select nvl(max(upgrade_id),1)
    into   l_upgrade_id
    from   ota_upgrade_log ;

Line: 66

     select process_date
     from ota_upgrade_log
     where upgrade_id = upg_id;

Line: 83

     select 1
     from ota_upgrade_log
     where upgrade_id = upg_id
     and   log_type = 'E';

Line: 120

             p_param4          => p_upg_name,                      -- Update name
             p_param5          => '200',                      -- batchsize
             p_param6          => CONC_UPGRADE_ID,   -- Concurrent upgrade id;

Line: 145

      select 1
      From Fnd_Lookup_values
      Where Lookup_type = 'FREQUENCY'
      and created_by not in (1,2)
      and (Lookup_code,language)
      not in (Select Lookup_code,language from Fnd_lookup_values
      Where Lookup_type = 'OTA_DURATION_UNITS')
      and rownum = 1 ;

Line: 171

      select 1
      from ota_category_usages ocu
      where ocu.category = (SELECT lkp.meaning
                  FROM  hr_lookups lkp
                  WHERE lkp.lookup_code = ocu.category
               	  AND lkp.lookup_type = 'ACTIVITY_CATEGORY')
            or not exists  (select 1
                       from ota_category_usages_tl oct
                       where oct.category_usage_id = ocu.category_usage_id)
      and rownum  = 1 ;

Line: 199

      select 1
      FROM ota_activity_definitions tad
      WHERE tad.category_usage_id is null
      AND   rownum = 1 ;

Line: 221

      select 1
      from     ota_category_usages ocu
      where    ocu.type = 'C'
      and      not exists (select category_usage_id
                                         from ota_activity_definitions tad
                                         where tad.category_usage_id is not null
                                         and tad.category_usage_id = ocu.category_usage_id)
      and rownum = 1 ;

Line: 248

      select 1
      FROM ota_activity_versions  tav
      WHERE
            not exists (select 1 from ota_offerings off where off.activity_version_id = tav.activity_version_id )
      and   ( exists    (select 1 from ota_events evt where evt.activity_version_id = tav.activity_version_id )
      or    ( exists    (select 1 from ota_resource_usages rud where rud.activity_version_id = tav.activity_version_id)
            and not exists (select 1 from ota_resource_usages rud1 where rud1.activity_version_id= tav.activity_version_id
                             and rud1.offering_id is null))
      or    ( exists     ( select 1 from per_competence_elements where object_id = tav.activity_version_id and type = 'TRAINER')
      and not exists (select 1 from per_competence_elements where object_id = tav.activity_version_id and type = 'OTA_OFFERING')))
      and rownum =1;

Line: 261

      select 1
      from   ota_events
      where  parent_offering_id is null
      and rownum = 1 ;

Line: 292

      select 1
      from ota_event_associations
      where nvl(self_enrollment_flag,'Y') <> 'N'
      and   (           customer_id     is not null
           or        job_id          is not null
           or        organization_id is not null
           or        position_id     is not null)
     and rownum = 1;

Line: 318

      select 1
      from   ota_events
      where line_id is not null
      and   (    nvl(book_independent_flag,'Y') <> 'N'
              or nvl(Maximum_internal_attendees,0) <> 0)
      and rownum = 1 ;

Line: 345

      select evt.event_id,evt.activity_version_id evt_act_Ver_id, off.activity_version_id,evt.parent_offering_id
      from   ota_events evt, ota_offerings off
      where  evt.parent_offering_id = off.offering_id
      and    evt.activity_version_id <> off.activity_version_id ;

Line: 351

      select evt.event_id,evt.activity_version_id evt_act_Ver_id, off.activity_version_id,evt.parent_offering_id,evt.language_id evt_lang, off.language_id off_lang
      from   ota_events evt, ota_offerings off
      where  evt.parent_offering_id = off.offering_id
      and    evt.language_id <> off.language_id ;

Line: 363

	   Update ota_events
	   set parent_offering_id = Null
	   Where  event_id = l_off_evt_link.event_id
	   and    parent_offering_id = l_off_evt_link.parent_offering_id ;

Line: 372

	   Update ota_events
	   set parent_offering_id = Null
	   Where  event_id = l_off_evt_lang.event_id
	   and    parent_offering_id = l_off_evt_lang.parent_offering_id ;

Line: 397

      INSERT INTO OTA_UPGRADE_LOG (
                           UPGRADE_ID,
                           TABLE_NAME,
                           SOURCE_PRIMARY_KEY,
                           OBJECT_VALUE,
                           BUSINESS_GROUP_ID,
                           PROCESS_DATE,
                           MESSAGE_TEXT,
                           TARGET_PRIMARY_KEY,
			   LOG_TYPE,
			   UPGRADE_NAME)
			   VALUES
			   (CONC_UPGRADE_ID,
                             'DUMMY',
			    '-1',
			    null,
			    null,
			    sysdate,
			    'Starting Reprocess OTA Classic Data Upgrade',
			    null,
			    'N',
			    'OTCLSUPG');

Line: 487

         update ota_offerings
         set learning_object_id = null
         where learning_object_id = -1;