eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OTA_BULK_ENROLL_UTIL SQL Statements

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

Line: 13


   SELECT lpe.path_status_code status_code,
          lkp.meaning Status_meaning,
          decode(lpe.path_status_code,'CANCELLED' , 0,  'ACTIVE',1, 'COMPLETED', 2) path_status_number
   FROM ota_lp_enrollments lpe,
	       hr_lookups lkp
   WHERE lpe.learning_path_id = p_object_id
     AND lpe.person_id = p_learner_id
	 AND lkp.lookup_code  = lpe.path_status_code
	 AND lkp.lookup_type = 'OTA_LEARNING_PATH_STATUS'
     order by path_status_number desc;

Line: 25


   SELECT bst.type status_code,
          btt.name Status,
          bst.booking_status_type_id  status_id,
          decode(bst.type, 'C', 0,'R',1, 'W',2, 'P',3, 'E',4, 'A',5) status_number
   FROM ota_delegate_bookings tdb,
               ota_booking_status_types bst,
			   ota_booking_status_types_tl btt
   WHERE  tdb.delegate_person_id = p_learner_id
        AND  tdb.event_id = p_object_id
		AND tdb.booking_status_type_id = bst.booking_status_type_id
		AND bst.booking_status_type_id = btt.booking_status_type_id
		-- Added for bug#5572552
		AND btt.LANGUAGE = USERENV('LANG')
        order by status_number desc;

Line: 41


SELECT cre.certification_status_code status_code,
          lkp.meaning Status_meaning,
          decode(cre.certification_status_code, 'REJECTED', 0, 'EXPIRED', 1, 'AWAITING_APPROVAL',2,
                 'CERTIFIED' , 3, 'CONCLUDED', 4,'ENROLLED',5) cert_status_number
   FROM ota_cert_enrollments cre,
	       hr_lookups lkp
   WHERE cre.certification_id = p_object_id
     AND cre.person_id = p_learner_id
	 AND lkp.lookup_code  =cre.certification_status_code
	 AND lkp.lookup_type = 'OTA_CERT_ENROLL_STATUS'
     order by cert_status_number desc;

Line: 85


    SELECT btt.name
    FROM ota_booking_status_types_tl btt
    WHERE btt.language = USERENV('LANG')
      AND btt.booking_status_type_id = p_enrollment_status;

Line: 91


   SELECT lkp.meaning
   FROM hr_lookups lkp
   WHERE lkp.lookup_code = p_enrollment_status
      AND lkp.lookup_type = decode(p_object_type,'LP','OTA_LEARNING_PATH_STATUS','CRT','OTA_CERT_ENROLL_STATUS');

Line: 120


SELECT sum(nvl(tdb.number_of_places,1)) enrolled_places
FROM ota_delegate_bookings tdb, ota_booking_status_types bst
WHERE tdb.event_id = p_class_id
  and tdb.internal_booking_flag = 'Y'
  and tdb.booking_status_type_id = bst.booking_status_type_id
  and bst.type IN ('P', 'A','E');

Line: 139


  select MAXIMUM_INTERNAL_ATTENDEES, MAXIMUM_ATTENDEES, event_status
  INTO l_max_internal, l_max_enroll, l_event_status
  FROM ota_events
  WHERE event_id = p_class_id;

Line: 197


FUNCTION get_total_selected_learners(p_bulk_enr_request_id IN NUMBER)
RETURN NUMBER
IS
 CURSOR csr_get_number_of_learners IS
 SELECT count(bulk_enr_request_id)
 FROM ota_bulk_enr_req_members
 WHERE bulk_enr_request_id = p_bulk_enr_request_id;

Line: 212


END get_total_selected_learners;

Line: 220


SELECT count(person_id)
FROM ota_bulk_enr_req_members brm,
     ota_bulk_enr_requests ber,
     ota_events evt
WHERE ber.bulk_enr_request_id = brm.bulk_enr_request_id
AND ber.bulk_enr_request_id = p_bulk_enr_request_id
AND evt.event_id = ber.object_id
AND ber.object_type = 'CL'
AND ota_cpr_utility.is_mand_crs_prereqs_completed(brm.person_id
    , NULL
    , brm.person_id
    , 'E'
    , evt.activity_version_id) = 'N';

Line: 235


 SELECT count(person_id)
FROM ota_bulk_enr_req_members brm,
     ota_bulk_enr_requests ber,
     ota_events evt
WHERE ber.bulk_enr_request_id = brm.bulk_enr_request_id
AND ber.bulk_enr_request_id = p_bulk_enr_request_id
AND evt.event_id = ber.object_id
AND ber.object_type = 'CL'
AND ota_cpr_utility.is_mand_comp_prereqs_completed(brm.person_id, evt.activity_version_id) = 'N';

Line: 262


   ,p_selected_learners OUT NOCOPY NUMBER
   ,p_unfulfil_course_prereqs OUT NOCOPY NUMBER
   ,p_unfulfil_comp_prereqs OUT NOCOPY NUMBER)
IS
l_request_rec csr_get_request_info%ROWTYPE;

Line: 276


       p_selected_learners := 0;

Line: 280


    p_selected_learners := get_total_selected_learners(p_bulk_enr_request_id);

Line: 295


   ,p_selected_learners OUT NOCOPY NUMBER
   ,p_success_enrollments OUT NOCOPY NUMBER
   ,p_errored_enrollments OUT NOCOPY NUMBER
   ,p_unfulfil_course_prereqs OUT NOCOPY NUMBER
   ,p_unfulfil_comp_prereqs OUT NOCOPY NUMBER)
IS

  CURSOR get_success_enrollments IS
  SELECT count(person_id)
  FROM ota_bulk_enr_req_members
  WHERE bulk_enr_request_id = p_bulk_enr_request_id
   AND enrollment_status IS NOT NULL;

Line: 309


  SELECT count(person_id)
  FROM ota_bulk_enr_req_members
  WHERE bulk_enr_request_id = p_bulk_enr_request_id
    AND error_message IS NOT NULL;

Line: 316


   ,p_selected_learners => p_selected_learners
   ,p_unfulfil_course_prereqs => p_unfulfil_course_prereqs
   ,p_unfulfil_comp_prereqs => p_unfulfil_comp_prereqs);

Line: 337


SELECT sum(nvl(tdb.number_of_places,0))
FROM ota_delegate_bookings tdb
   , ota_booking_status_types bst
WHERE tdb.booking_status_type_id = bst.booking_status_type_id
  AND tdb.event_id = p_object_id
  --TBD: needs to be confirmed if Requested(R) and Waitlisted(W) should also be included here
  AND bst.type NOT IN ('C');

Line: 346


SELECT count(lp_enrollment_id)
FROM ota_lp_enrollments
WHERE learning_path_id = p_object_id
  AND path_status_code <> 'CANCELLED';

Line: 352


SELECT count(cert_enrollment_id)
FROM ota_cert_enrollments crt
WHERE certification_id = p_object_id
--TBD: Confirm if this is correct
 AND CERTIFICATION_STATUS_CODE <> 'CANCELLED';

Line: 418


SELECT hao.business_group_id,
       evt.organization_id,
       evt.currency_code,
       evt.course_start_date,
       evt.course_end_date,
       evt.Title,
       evt.owner_id,
       off.activity_version_id,
       evt.offering_id
FROM   OTA_EVENTS_VL 		 evt,
       OTA_OFFERINGS         off,
       HR_ALL_ORGANIZATION_UNITS hao
WHERE  evt.event_id = pp_event_id
AND    off.offering_id = evt.parent_offering_id
AND    evt.organization_id = hao.organization_id (+);

Line: 436


Select event_status, maximum_internal_attendees, maximum_attendees
from   OTA_EVENTS
WHERE  EVENT_ID = TO_NUMBER(p_event_id);

Line: 441


SELECT sum(nvl(dbt.number_of_places, 0))
FROM   OTA_DELEGATE_BOOKINGS dbt,
       OTA_BOOKING_STATUS_TYPES bst
WHERE  dbt.event_id = TO_NUMBER(p_event_id)
AND    dbt.internal_booking_flag = 'Y'
AND    dbt.booking_status_type_id = bst.booking_status_type_id
AND    bst.type in ('P','A','E');

Line: 450


SELECT sum(number_of_places)
FROM   OTA_DELEGATE_BOOKINGS dbt,
       OTA_BOOKING_STATUS_TYPES bst
WHERE  dbt.event_id = TO_NUMBER(p_event_id)
AND    dbt.booking_status_type_id = bst.booking_status_type_id
AND    bst.type in ('P','A','E');

Line: 459


SELECT nvl(price_basis,NULL)
FROM ota_events
where event_id = p_event_id;

Line: 464


SELECT
 USER_NAME
FROM
 FND_USER
WHERE
Employee_id = p_owner_id ;

Line: 473


SELECT version_name
FROM OTA_ACTIVITY_VERSIONS_TL
WHERE activity_version_id = p_activity_version_id
AND language=userenv('LANG');

Line: 479


SELECT bjs.priority_level
FROM ota_bkng_justifications_b BJS
WHERE bjs.booking_justification_id = p_booking_justification_id;

Line: 486


SELECT ppf.work_telephone,
       paf.organization_id,
       ppf.email_address
FROM
  per_all_people_f ppf,
  per_all_assignments_f paf
WHERE
      ppf.person_id = paf.person_id
  AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
  AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
  AND ppf.person_id = p_person_id
  AND paf.assignment_id = p_assignment_id;*/

Line: 502


  SELECT PPH.PHONE_NUMBER work_telephone,
         paf.organization_id,
         ppf.email_address
  FROM
    per_all_people_f ppf,
    per_all_assignments_f paf ,
    per_phones pph
  WHERE
        ppf.person_id = paf.person_id
    AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
    AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
    AND ppf.person_id = p_person_id
    AND paf.assignment_id = p_assignment_id
    AND pph.PARENT_ID(+) = ppf.PERSON_ID
    AND pph.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
    AND pph.PHONE_TYPE(+) = 'W1'
  --AND trunc(sysdate) BETWEEN NVL(PPH.DATE_FROM, SYSDATE) AND NVL(PPH.DATE_TO, SYSDATE);For bug6770085

Line: 522


      SELECT PPH.PHONE_NUMBER work_telephone,
             paf.organization_id,
             ppf.email_address,
             pfax.PHONE_NUMBER fax_number
      FROM
        per_all_people_f ppf,
        per_all_assignments_f paf ,
        per_phones pph,
        per_phones pfax
      WHERE
            ppf.person_id = paf.person_id
        AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
        AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
        AND ppf.person_id = p_person_id
        AND paf.assignment_id = p_assignment_id
        AND pph.PARENT_ID(+) = ppf.PERSON_ID
        AND pph.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
        AND pph.PHONE_TYPE(+) = 'W1'
      --AND trunc(sysdate) BETWEEN NVL(PPH.DATE_FROM, SYSDATE) AND NVL(PPH.DATE_TO, SYSDATE);For bug6770085

Line: 1032


			UPDATE ota_finance_lines SET transfer_status = 'AT'
			WHERE finance_line_id = l_finance_line_id;

Line: 1141


  SELECT name
  FROM ota_learning_paths_tl
  WHERE learning_path_id = p_object_id
    AND language = USERENV('LANG');

Line: 1147


  SELECT name
  FROM ota_certifications_tl
  WHERE certification_id = p_object_id
    AND language = USERENV('LANG');

Line: 1153


  SELECT title
  FROM ota_events_tl
  WHERE event_id = p_object_id
    AND language = USERENV('LANG');

Line: 1183


PROCEDURE delete_bulk_enr_request
		(itemtype   IN WF_ITEMS.ITEM_TYPE%TYPE
		,itemkey    IN WF_ITEMS.ITEM_KEY%TYPE
  		,actid	    IN NUMBER
   	        ,funcmode   IN VARCHAR2
	        ,resultout  OUT nocopy VARCHAR2 ) AS

l_blk_enr_request_id OTA_BULK_ENR_REQUESTS.BULK_ENR_REQUEST_ID%TYPE;

Line: 1198


    DELETE FROM OTA_BULK_ENR_REQ_MEMBERS
    WHERE BULK_ENR_REQUEST_ID = l_blk_enr_request_id;

Line: 1201


    DELETE FROM OTA_BULK_ENR_REQUESTS
    WHERE BULK_ENR_REQUEST_ID = l_blk_enr_request_id;

Line: 1211


END delete_bulk_enr_request;

Line: 1230


SELECT wrpv.display_name displayName
FROM   wf_runnable_processes_v wrpv
WHERE wrpv.item_type = l_item_type
AND wrpv.process_name = l_process;

Line: 1237


SELECT user_name
FROM fnd_user
WHERE employee_id=p_person_id;

Line: 1243


SELECT ppf.full_name
FROM per_all_people_f ppf
WHERE person_id = p_person_id;

Line: 1248


SELECT COUNT(person_id)
FROM ota_bulk_enr_req_members
WHERE bulk_enr_request_id = p_enr_request_id
  and error_message IS NOT NULL;

Line: 1253


CURSOR csr_get_selected_learners IS
SELECT COUNT(person_id)
FROM ota_bulk_enr_req_members
WHERE bulk_enr_request_id = p_enr_request_id;

Line: 1259


SELECT COUNT(person_id)
FROM ota_bulk_enr_req_members
WHERE bulk_enr_request_id = p_enr_request_id
 and enrollment_status IS NOT NULL;

Line: 1268


l_selected_learners NUMBER := 0;

Line: 1288


	select hr_workflow_item_key_s.nextval
	into   l_item_key
	from   sys.dual;

Line: 1314


    OPEN csr_get_selected_learners;

Line: 1315


    FETCH csr_get_selected_learners INTO l_selected_learners;

Line: 1316


    CLOSE csr_get_selected_learners;

Line: 1322


    WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'TOTAL_NUMBER',l_selected_learners);

Line: 1331


	    SELECT user_name INTO l_user_name
	    FROM fnd_user
	    WHERE employee_id=l_person_id
	    AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD'))       --Bug 5676892
	    AND ROWNUM =1 ;

Line: 1419


        UPDATE ota_bulk_enr_req_members
       SET error_message = l_error_message, enrollment_status = NULL
       WHERE person_id = l_req_member_rec.person_id
       AND bulk_enr_request_id = p_enr_request_id;

Line: 1429


       UPDATE ota_bulk_enr_req_members
       SET enrollment_status = l_path_status_code, error_message = NULL
       WHERE person_id = l_req_member_rec.person_id
       AND bulk_enr_request_id = p_enr_request_id;

Line: 1494


        UPDATE ota_bulk_enr_req_members
       SET error_message = l_error_message, enrollment_status = NULL
       WHERE person_id = l_req_member_rec.person_id
       AND bulk_enr_request_id = p_enr_request_id;

Line: 1504


       UPDATE ota_bulk_enr_req_members
       SET enrollment_status = l_certification_status_code, error_message = NULL
       WHERE person_id = l_req_member_rec.person_id
       AND bulk_enr_request_id = p_enr_request_id;

Line: 1526


SELECT btt.booking_status_type_id , btt.name booking_status
FROM ota_delegate_bookings tdb, ota_booking_status_types_tl btt
WHERE booking_id = l_booking_id
 and tdb.booking_status_type_id = btt.booking_status_type_id
 and btt.language = USERENV('LANG');

Line: 1533


SELECT paf.organization_id
FROM per_all_assignments_f paf
WHERE paf.assignment_id = l_assignment_id;

Line: 1538


SELECT pcak.cost_allocation_keyflex_id
FROM per_all_assignments_f assg,
pay_cost_allocations_f pcaf,
pay_cost_allocation_keyflex pcak
WHERE assg.assignment_id = pcaf.assignment_id
AND assg.assignment_id = l_assignment_id
AND assg.Primary_flag = 'Y'
AND pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
AND pcak.enabled_flag = 'Y'
AND sysdate between nvl(pcaf.effective_start_date,sysdate)
and nvl(pcaf.effective_end_date,sysdate+1)
AND trunc(sysdate) between nvl(assg.effective_start_date,trunc(sysdate))
and nvl(assg.effective_end_date,trunc(sysdate+1));

Line: 1607


        UPDATE ota_bulk_enr_req_members
       SET error_message = l_error_message, enrollment_status = NULL
       WHERE person_id = l_req_member_rec.person_id
       AND bulk_enr_request_id = p_enr_request_id;

Line: 1621


       UPDATE ota_bulk_enr_req_members
       SET enrollment_status = l_booking_status_type_id, error_message = NULL
       WHERE person_id = l_req_member_rec.person_id
	   AND assignment_id = l_req_member_rec.assignment_id
       AND bulk_enr_request_id = p_enr_request_id;

Line: 1629


        UPDATE ota_bulk_enr_req_members
       SET error_message = l_error_message, enrollment_status = NULL
       WHERE person_id = l_req_member_rec.person_id
       AND bulk_enr_request_id = p_enr_request_id;

Line: 1650


l_learners_selected NUMBER := 0;

Line: 1657


  SELECT count(person_id)
  INTO l_learners_selected
  FROM ota_bulk_enr_req_members
  WHERE bulk_enr_request_id = p_enr_request_id;

Line: 1666


  IF l_threshold >= l_learners_selected THEN
     -- No concurrent processing required
     p_conc_request_id := -1;

Line: 1695


  UPDATE ota_bulk_enr_requests ber
  SET CONC_PROGRAM_REQUEST_ID = p_conc_request_id
  WHERE bulk_enr_request_id = p_enr_request_id;