[Home] [Help]
361: evt.Course_Start_Date,
362: evt.Course_End_Date,
363: tdb.delegate_contact_id
364: From
365: ota_Delegate_bookings tdb,
366: ota_Events_vl evt, --MLS change _vl added
367: ota_activity_versions_tl tav -- MLS change _tl added
368: Where
369: evt.event_id = tdb.event_id and
689: CURSOR C_enrollment
690: IS
691: Select null
692: FROM
693: OTA_DELEGATE_BOOKINGS
694: WHERE
695: Line_id = p_line_id;
696:
697:
985: -- clash with the event being booked
986: --
987: cursor c_other_person_bookings is
988: select bst.type
989: from ota_delegate_bookings db,
990: ota_booking_status_types bst,
991: ota_events ev,
992: ota_events evt
993: where db.delegate_person_id = p_delegate_person_id
1012: order by bst.type;
1013: --
1014: cursor c_other_contact_bookings is
1015: select bst.type
1016: from ota_delegate_bookings db,
1017: ota_booking_status_types bst,
1018: ota_events ev,
1019: ota_events evt
1020: where db.delegate_contact_id = p_delegate_contact_id
1410: BEGIN
1411: hr_utility.set_location('Entering :'||l_proc,5);
1412: SELECT COUNT(booking_id)
1413: INTO l_num_waitlisted
1414: FROM ota_delegate_bookings tdb
1415: WHERE tdb.event_id = p_event_id
1416: AND tdb.booking_status_type_id IN (SELECT bst.booking_status_type_id
1417: FROM ota_booking_status_types bst
1418: WHERE bst.type = 'W');
1453: l_count number := 1;
1454:
1455: CURSOR c_date_waitlist is
1456: SELECT tdb.booking_id
1457: FROM ota_delegate_bookings tdb,
1458: ota_booking_status_types bst
1459: WHERE tdb.booking_status_type_id = bst.booking_status_type_id
1460: AND bst.type = 'W'
1461: AND tdb.event_id = p_event_id
1462: ORDER BY tdb.date_booking_placed;
1463:
1464: CURSOR c_priority_waitlist is
1465: SELECT tdb.booking_id
1466: FROM ota_delegate_bookings tdb,
1467: ota_booking_status_types bst
1468: WHERE tdb.booking_status_type_id = bst.booking_status_type_id
1469: AND bst.type = 'W'
1470: AND tdb.event_id = p_event_id
2137:
2138: end get_question_bank_count ;
2139:
2140: -- Author: sbhullar
2141: FUNCTION get_enrollment_status(p_delegate_person_id IN ota_delegate_bookings.delegate_person_id%TYPE,
2142: p_delegate_contact_id IN NUMBER,
2143: p_event_id IN ota_events.event_id%TYPE,
2144: p_code IN number)
2145: RETURN VARCHAR2 IS
2146:
2147: CURSOR enroll_status IS
2148: SELECT DECODE(BST.type,'C','Y',BST.type) status, BST.name
2149: FROM ota_booking_status_types_vl BST,
2150: ota_delegate_bookings ODB
2151: WHERE ODB.event_id = p_event_id
2152: AND (p_delegate_person_id IS NOT NULL AND ODB.delegate_person_id = p_delegate_person_id
2153: OR p_delegate_contact_id IS NOT NULL and ODB.delegate_contact_id = p_delegate_contact_id)
2154: AND ODB.booking_status_type_id = BST.booking_status_type_id
2253:
2254: END get_person_fullname;
2255:
2256: FUNCTION get_learner_name(p_person_id IN per_all_people_f.person_id%TYPE,
2257: p_customer_id IN ota_delegate_bookings.customer_id%TYPE,
2258: p_contact_id IN ota_delegate_bookings.delegate_contact_id%TYPE)
2259: RETURN VARCHAR2 IS
2260:
2261: CURSOR c_person_name IS
2254: END get_person_fullname;
2255:
2256: FUNCTION get_learner_name(p_person_id IN per_all_people_f.person_id%TYPE,
2257: p_customer_id IN ota_delegate_bookings.customer_id%TYPE,
2258: p_contact_id IN ota_delegate_bookings.delegate_contact_id%TYPE)
2259: RETURN VARCHAR2 IS
2260:
2261: CURSOR c_person_name IS
2262: select pap.full_name from per_all_people_f pap
2263: where pap.person_id = p_person_id
2264: and trunc(sysdate) between nvl(pap.effective_start_date, trunc(sysdate))
2265: and nvl(pap.effective_end_date, trunc(sysdate));
2266:
2267: CURSOR c_contact_name(l_customer_id IN ota_delegate_bookings.customer_id%TYPE) IS
2268: SELECT
2269: SUBSTRB( PARTY.PERSON_LAST_NAME,1,50) || ' ' ||
2270: SUBSTRB( PARTY.PERSON_FIRST_NAME,1,40) || ' ' ||
2271: HR_GENERAL.DECODE_AR_LOOKUP('CONTACT_TITLE',nvl(PARTY.PERSON_PRE_NAME_ADJUNCT,PARTY.PERSON_TITLE)) LEARNER_NAME
2343: and role_acct.party_id = rel.object_id
2344: and ACCT_ROLE.cust_account_role_id = p_contact_id;
2345:
2346: l_proc VARCHAR2(72) := g_package|| 'get_customer_id';
2347: l_return ota_delegate_bookings.customer_id%TYPE;
2348:
2349: Begin
2350: hr_utility.set_location(' Step:'|| l_proc, 10);
2351:
2356: return l_return;
2357: hr_utility.set_location(' Step:'|| l_proc, 20);
2358: End get_customer_id;
2359:
2360: FUNCTION get_cust_org_name(p_organization_id IN ota_delegate_bookings.organization_id%TYPE,
2361: p_customer_id IN ota_delegate_bookings.customer_id%TYPE,
2362: p_contact_id IN ota_lp_enrollments.contact_id%TYPE default null)
2363: RETURN VARCHAR2 IS
2364:
2357: hr_utility.set_location(' Step:'|| l_proc, 20);
2358: End get_customer_id;
2359:
2360: FUNCTION get_cust_org_name(p_organization_id IN ota_delegate_bookings.organization_id%TYPE,
2361: p_customer_id IN ota_delegate_bookings.customer_id%TYPE,
2362: p_contact_id IN ota_lp_enrollments.contact_id%TYPE default null)
2363: RETURN VARCHAR2 IS
2364:
2365: CURSOR c_organization_name IS
2365: CURSOR c_organization_name IS
2366: select name from hr_all_organization_units_tl
2367: where language = userenv('LANG') and organization_id = p_organization_id;
2368:
2369: CURSOR c_customer_name(l_customer_id IN ota_delegate_bookings.customer_id%TYPE) IS
2370: select substrb(party.party_name,1,50)
2371: from hz_parties party
2372: ,hz_cust_accounts cust_acct
2373: where
2676: -- Public
2677: -- {End of Comments}
2678: ------------------------------------------------------------------
2679: FUNCTION get_ext_lrnr_party_id
2680: (p_delegate_contact_id IN ota_delegate_bookings.delegate_contact_id%TYPE)
2681: RETURN number IS
2682:
2683: Cursor get_ext_lrn_party_id is
2684: select party.party_id