APPS.OTA_TRNG_PLAN_UTIL_SS SQL Statements

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

Line: 19

Select tpm.training_plan_member_id from
ota_Training_plan_members tpm
where member_status_type_id<>'CANCELLED'
and training_plan_id=p_training_plan_id and rownum=1;

Line: 53

Select tpm.training_plan_member_id from
ota_Training_plan_members tpm
where member_status_type_id<>'CANCELLED'
and member_status_type_id<>'OTA_COMPLETED'
and training_plan_id=p_training_plan_id and rownum=1;

Line: 60

Select tpm.training_plan_member_id from
ota_training_plan_members tpm
where member_status_type_id='OTA_COMPLETED'
and training_plan_id=p_training_plan_id and rownum=1;

Line: 102

SELECT DECODE(bst.type,'C','Z',bst.type) status
  FROM ota_training_plan_members tpm,
       ota_events evt,
       ota_delegate_bookings tdb,
       ota_booking_status_types bst,
       ota_training_plans tps
WHERE tpm.activity_version_id=evt.activity_version_id
   AND evt.event_id = tdb.event_id
   AND bst.booking_status_type_id = tdb.booking_status_type_id
--    and bst.active_flag='Y'
  AND tps.training_plan_id = tpm.training_plan_id
  -- Modified for Bug#3855721
   AND (tps.learning_path_id IS NOT NULL OR (
        evt.course_start_date >= tpm.earliest_start_date
        AND
            (
             evt.course_end_date IS NOT NULL
             AND evt.course_end_date <= tpm.target_completion_date
            )
            OR
            (
               evt.event_type = 'SELFPACED'
             AND tpm.target_completion_date >= evt.course_start_date
             )
         ))
   AND tpm.training_plan_member_id = p_training_plan_member_id
   -- Modified for Bug#3855721
   --AND tdb.delegate_person_id = p_person_id
   AND ((p_person_id IS NOT NULL AND tdb.delegate_person_id = p_person_id)
                   OR (p_contact_id IS NOT NULL AND tdb.delegate_contact_id = p_contact_id))
 ORDER BY status;

Line: 172

    SELECT tps.person_id,tps.contact_id
     INTO l_person_id, l_contact_id
     FROM ota_training_plans tps
    WHERE tps.training_plan_id = p_training_plan_id;

Line: 177

    SELECT employee_id, customer_id
      INTO l_login_person, l_login_customer
      FROM fnd_user
     WHERE user_id = fnd_profile.value('USER_ID');

Line: 214

SELECT tps.Training_plan_id
  FROM
       ota_training_plans tps
 WHERE
   p_earliest_start_date >= tps.start_date
   AND (tps.end_date IS NOT NULL AND p_target_completion_date <= tps.end_date)
   AND tps.plan_source = 'TALENT_MGMT'
   AND tps.person_id = p_person_id
   -- Added for Bug#3493925
   AND tps.plan_status_type_id = 'ACTIVE'
   -- Added for Bug#3108246
   AND tps.business_group_id = p_business_group_id
   AND (tps.additional_member_flag is null or tps.additional_member_flag<>'N');

Line: 261

SELECT tpm.training_plan_member_id
  FROM ota_training_plan_members tpm
 WHERE tpm.activity_version_id = p_activity_version_id
   AND (tpm.earliest_start_date < p_start_date
   or (p_end_date IS NOT NULL AND tpm.target_completion_date > p_end_date))
   and tpm.member_status_type_id <> 'CANCELLED'
   AND ROWNUM=1;

Line: 300

SELECT NULL
  FROM ota_events e,
       ota_activity_versions a,
       ota_delegate_bookings b,
       ota_booking_status_types s,
       ota_training_plan_members tpm
 WHERE e.event_id = b.event_id
   AND tpm.activity_version_id = a.activity_version_id
   AND ((e.course_start_date >= tpm.earliest_start_date
         AND e.course_end_date  <= tpm.target_completion_date )
          OR
        (e.event_type ='SELFPACED'
         AND e.course_start_date< tpm.target_completion_date
         AND e.course_end_date >= tpm.target_completion_date
         ))
    AND e.activity_version_id = a.activity_version_id
    AND b.booking_status_type_id = s.booking_status_type_id
    -- Modified for Bug#3479186
   -- AND b.delegate_person_id = p_person_id
       AND ((p_person_id IS NOT NULL AND b.delegate_person_id = p_person_id)
                   OR (p_contact_id IS NOT NULL AND b.delegate_contact_id = p_contact_id)
		 )
    AND tpm.training_plan_member_id = p_training_plan_member_id;

Line: 365

SELECT DECODE(bst.type,'C','Z',bst.type) status
  FROM ota_events evt,
       ota_delegate_bookings tdb,
       ota_booking_status_types bst
 WHERE evt.event_id = tdb.event_id
   AND bst.booking_status_type_id = tdb.booking_status_type_id
   AND (
        evt.course_start_date >= p_earliest_start_date
        AND
            (
             evt.course_end_date IS NOT NULL
             AND evt.course_end_date <= p_target_completion_date
            )
            OR
            (
               evt.event_type = 'SELFPACED'
             AND p_target_completion_date >= evt.course_start_date
             )
         )
   AND evt.activity_version_id = p_activity_version_id
   --AND tdb.delegate_person_id = p_person_id
    AND ((p_person_id IS NOT NULL AND tdb.delegate_person_id = p_person_id)
                   OR (p_contact_id IS NOT NULL AND tdb.delegate_contact_id = p_contact_id)
		 )
 ORDER BY status;

Line: 392

SELECT DECODE(bst.type,'C','Z',bst.type) status
  FROM ota_events evt,
       ota_delegate_bookings tdb,
       ota_booking_status_types bst
 WHERE evt.event_id = tdb.event_id
   AND bst.booking_status_type_id = tdb.booking_status_type_id
   AND evt.activity_version_id = p_activity_version_id
   -- Modified for Bug#3479186
  -- AND tdb.delegate_person_id = p_person_id
  AND ((p_person_id IS NOT NULL AND tdb.delegate_person_id = p_person_id)
                   OR (p_contact_id IS NOT NULL AND tdb.delegate_contact_id = p_contact_id)
		 )
 ORDER BY status;

Line: 407

SELECT tps.plan_source
FROM ota_training_plans tps
where tps.training_plan_id = p_training_plan_id;

Line: 471

 SELECT tp.person_id, tp.contact_id
 INTO l_person_id , l_contact_id
 FROM ota_training_plans tp
 where tp.training_plan_id = p_training_plan_id;

Line: 516

PROCEDURE modify_tpc_status_on_update(--p_person_id               IN ota_training_plans.person_id%TYPE,
                                      p_earliest_start_date     IN ota_training_plan_members.earliest_start_date%TYPE,
                                      p_target_completion_date  IN ota_training_plan_members.target_completion_date%TYPE,
                                      p_activity_version_id     IN ota_activity_versions.activity_version_id%TYPE,
                                      p_training_plan_id        IN ota_training_plans.training_plan_id%TYPE,
                                      p_member_status_id        OUT nocopy VARCHAR2)
 IS

 l_proc             VARCHAR2(72) :=      g_package|| 'modify_tpc_status_on_update';

Line: 534

  SELECT tp.person_id, tp.contact_id
 INTO l_person_id , l_contact_id
 FROM ota_training_plans tp
 where tp.training_plan_id = p_training_plan_id;

Line: 546

                                                           p_action                 => 'UPDATE' );

Line: 572

END modify_tpc_status_on_update;

Line: 583

SELECT person_id
  FROM ota_training_plans
 WHERE training_plan_id = p_training_plan_id;

Line: 663

    SELECT tps.person_id, tps.contact_id
     INTO l_person_id, l_contact_id
     FROM ota_training_plans tps
    WHERE tps.training_plan_id = p_training_plan_id;

Line: 684

CURSOR csr_tp_update(csr_training_plan_id number)
    IS
    SELECT otp.name,
           otp.object_version_number,
           otp.time_period_id,
           otp.budget_currency
     FROM ota_training_plans otp
     WHERE otp.training_plan_id = csr_training_plan_id;

Line: 703

        OPEN csr_tp_update(p_training_plan_id);

Line: 704

        FETCH csr_tp_update into l_name,l_object_version_number,l_time_period_id,l_budget_currency;

Line: 705

        IF csr_tp_update%FOUND then
           CLOSE csr_tp_update;

Line: 707

           ota_tps_api.update_training_plan
                       (p_effective_date               => sysdate
                       ,p_training_plan_id             => p_training_plan_id
                       ,p_object_version_number        => l_object_version_number
                       ,p_plan_status_type_id          => l_plan_status_type_id
                       ,p_name                         => l_name
                       ,p_time_period_id               => l_time_period_id
                       ,p_budget_currency              => l_budget_currency);

Line: 717

          CLOSE csr_tp_update;