[Home] [Help]
382: ,p_cost_centers IN VARCHAR2
383: ,p_assignment_id IN PER_ALL_ASSIGNMENTS_F.assignment_id%TYPE
384: ,p_business_group_id_from IN PER_ALL_ASSIGNMENTS_F.business_group_id%TYPE
385: ,p_organization_id IN PER_ALL_ASSIGNMENTS_F.organization_id%TYPE
386: ,p_person_id IN PER_ALL_PEOPLE_F.person_id%type
387: ,p_delegate_contact_id IN NUMBER
388: ,p_booking_id OUT NOCOPY OTA_DELEGATE_BOOKINGS.Booking_id%type
389: ,p_message_name OUT NOCOPY varchar2
390: ,p_tdb_information_category IN VARCHAR2
486: SELECT ppf.work_telephone,
487: paf.organization_id,
488: ppf.email_address
489: FROM
490: per_all_people_f ppf,
491: per_all_assignments_f paf
492: WHERE
493: ppf.person_id = paf.person_id
494: AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
502: SELECT PPH.PHONE_NUMBER work_telephone,
503: paf.organization_id,
504: ppf.email_address
505: FROM
506: per_all_people_f ppf,
507: per_all_assignments_f paf ,
508: per_phones pph
509: WHERE
510: ppf.person_id = paf.person_id
512: AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
513: AND ppf.person_id = p_person_id
514: AND paf.assignment_id = p_assignment_id
515: AND pph.PARENT_ID(+) = ppf.PERSON_ID
516: AND pph.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
517: AND pph.PHONE_TYPE(+) = 'W1'
518: --AND trunc(sysdate) BETWEEN NVL(PPH.DATE_FROM, SYSDATE) AND NVL(PPH.DATE_TO, SYSDATE);For bug6770085
519: AND trunc(sysdate) BETWEEN NVL(PPH.DATE_FROM(+), SYSDATE) AND NVL(PPH.DATE_TO(+), SYSDATE);*/
520:
523: paf.organization_id,
524: ppf.email_address,
525: pfax.PHONE_NUMBER fax_number
526: FROM
527: per_all_people_f ppf,
528: per_all_assignments_f paf ,
529: per_phones pph,
530: per_phones pfax
531: WHERE
534: AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
535: AND ppf.person_id = p_person_id
536: AND paf.assignment_id = p_assignment_id
537: AND pph.PARENT_ID(+) = ppf.PERSON_ID
538: AND pph.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
539: AND pph.PHONE_TYPE(+) = 'W1'
540: --AND trunc(sysdate) BETWEEN NVL(PPH.DATE_FROM, SYSDATE) AND NVL(PPH.DATE_TO, SYSDATE);For bug6770085
541: AND trunc(sysdate) BETWEEN NVL(PPH.DATE_FROM(+), SYSDATE) AND NVL(PPH.DATE_TO(+), SYSDATE)
542: AND pfax.PARENT_ID(+) = ppf.PERSON_ID
539: AND pph.PHONE_TYPE(+) = 'W1'
540: --AND trunc(sysdate) BETWEEN NVL(PPH.DATE_FROM, SYSDATE) AND NVL(PPH.DATE_TO, SYSDATE);For bug6770085
541: AND trunc(sysdate) BETWEEN NVL(PPH.DATE_FROM(+), SYSDATE) AND NVL(PPH.DATE_TO(+), SYSDATE)
542: AND pfax.PARENT_ID(+) = ppf.PERSON_ID
543: AND pfax.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
544: AND pfax.PHONE_TYPE(+) = 'WF'
545: AND trunc(sysdate) BETWEEN NVL(pfax.DATE_FROM(+), SYSDATE) AND NVL(pfax.DATE_TO(+), SYSDATE);
546:
547:
1223: l_item_type wf_items.item_type%type := 'OTWF';
1224: l_item_key wf_items.item_key%type;
1225:
1226: l_user_name varchar2(80);
1227: l_person_id per_all_people_f.person_id%type;
1228:
1229: l_process_display_name varchar2(240);
1230: l_request_rec csr_get_request_info%ROWTYPE;
1231:
1244:
1245:
1246: CURSOR csr_get_person_name(p_person_id IN number) IS
1247: SELECT ppf.full_name
1248: FROM per_all_people_f ppf
1249: WHERE person_id = p_person_id;
1250:
1251: CURSOR csr_get_error_learners IS
1252: SELECT COUNT(person_id)
1265: WHERE bulk_enr_request_id = p_enr_request_id
1266: and enrollment_status IS NOT NULL;
1267:
1268: l_object_name VARCHAR2(240);
1269: l_person_full_name per_all_people_f.FULL_NAME%TYPE;
1270: l_error_learners NUMBER := 0;
1271: l_success_learners NUMBER := 0;
1272: l_selected_learners NUMBER := 0;
1273:
1378:
1379: l_request_rec csr_get_request_info%ROWTYPE;
1380: l_req_member_rec csr_get_request_members%ROWTYPE;
1381: l_error_message ota_bulk_enr_req_members.error_message%TYPE;
1382: l_person_name per_all_people_f.full_name%TYPE;
1383: l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
1384: l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
1385: l_return_status varchar2(30);
1386:
1456: l_request_rec csr_get_request_info%ROWTYPE;
1457: l_req_member_rec csr_get_request_members%ROWTYPE;
1458: l_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%TYPE;
1459: l_error_message ota_bulk_enr_req_members.error_message%TYPE;
1460: l_person_name per_all_people_f.full_name%TYPE;
1461: l_certification_status_code ota_cert_enrollments.CERTIFICATION_STATUS_CODE%TYPE;
1462:
1463:
1464: BEGIN
1567:
1568: l_assignment_info csr_get_assignment_info%ROWTYPE;
1569: l_cost_center_info csr_get_cost_center_info%ROWTYPE;
1570:
1571: l_person_name per_all_people_f.full_name%TYPE;
1572:
1573: BEGIN
1574: OPEN csr_get_request_info(p_enr_request_id);
1575: FETCH csr_get_request_info INTO l_request_rec;
1737:
1738: l_assignment_info csr_get_assignment_info%ROWTYPE;
1739: l_cost_center_info csr_get_cost_center_info%ROWTYPE;
1740:
1741: l_person_name per_all_people_f.full_name%TYPE;
1742:
1743: l_subscribed_to_lp boolean;
1744: lp_enr_exists_rec csr_check_lp_enr_exists%ROWTYPE;
1745: l_subscribed_now boolean;
2201: l_object_version_number ota_delegate_bookings.object_version_number%TYPE;
2202: l_request_rec csr_get_request_info%ROWTYPE;
2203: l_req_member_rec csr_get_request_members%ROWTYPE;
2204: l_booking_id ota_delegate_bookings.booking_id%TYPE;
2205: l_person_name per_all_people_f.full_name%TYPE;
2206:
2207:
2208: l_class_name varchar2(80);
2209: