1 package body OTA_VIEWS_PKG as
2 /* $Header: otaonvew.pkb 120.0 2005/05/29 06:57:47 appldev noship $ */
3
4 --
5 -- Function to find the number of available places in a Training Course
6 --
7 function OTA_GET_PLACES_AVAILABLE(p_event_id number)
8 return number
9 as
10 l_places_booked number;
11 l_max_places number;
12 l_max_internal_places number;
13 l_internal_places_booked number;
14 l_no_of_int_places_remaining number;
15 l_no_of_places_remaining number;
16 l_place_wait number;
17 l_event_status varchar2(1);
18 BEGIN
19 select nvl(sum(db.number_of_places),0)
20 into l_places_booked
21 from ota_delegate_bookings db
22 , ota_booking_status_types bst
23 where bst.booking_status_type_id = db.booking_status_type_id
24 and ota_tdb_bus.event_place_needed(bst.booking_status_type_id) = 1
25 and db.event_id = p_event_id ;
26
27 select nvl(sum(db.number_of_places),0)
28 into l_place_wait
29 from ota_delegate_bookings db
30 , ota_booking_status_types bst
31 where bst.booking_status_type_id = db.booking_status_type_id
32 and bst.type = 'W'
33 and db.event_id = p_event_id ;
34
35
36 select nvl(sum(db.number_of_places),0)
37 into l_internal_places_booked
38 from ota_delegate_bookings db
39 , ota_booking_status_types bst
40 where bst.booking_status_type_id = db.booking_status_type_id
41 and ota_tdb_bus.event_place_needed(bst.booking_status_type_id) = 1
42 and db.internal_booking_flag='Y'
43 and db.event_id = p_event_id ;
44
45 select nvl(maximum_attendees,0)
46 into l_max_places
47 from ota_events
48 where event_id = p_event_id ;
49
50 select nvl(maximum_internal_attendees, -1)
51 into l_max_internal_places
52 from ota_events
53 where event_id = p_event_id;
54
55 select event_status
56 into l_event_status
57 from ota_events
58 where event_id = p_event_id;
59
60
61 l_no_of_places_remaining :=
62 l_max_places - l_places_booked;
63
64 if l_max_internal_places <> -1 THEN
65 -- There is an internal limitation
66
67 l_no_of_int_places_remaining :=
68 l_max_internal_places - l_internal_places_booked;
69
70 if l_no_of_int_places_remaining < l_no_of_places_remaining then
71 -- There are less internal places available then the amount
72 -- of places which could have been filled so use the lower of
73 -- the two figures
74 l_no_of_places_remaining := l_no_of_int_places_remaining;
75 end if;
76 if l_no_of_places_remaining < 0 then
77 l_no_of_places_remaining := 0 ;
78 end if;
79 else
80 l_no_of_places_remaining :=
81 l_max_places - l_places_booked - l_place_wait;
82 if l_no_of_places_remaining < 0 then
83 l_no_of_places_remaining := 0 ;
84 end if;
85 end if;
86
87 if l_event_status = 'F' then
88 l_no_of_places_remaining := 0 ;
89 end if;
90 return (l_no_of_places_remaining) ;
91 END OTA_GET_PLACES_AVAILABLE;
92 --
93 END OTA_VIEWS_PKG ;
[Home] [Help]