[Home] [Help]
PACKAGE BODY: APPS.OTA_TDB_WAITLIST_API
Source
1 Package Body OTA_TDB_WAITLIST_API as
2 /* $Header: ottdb03t.pkb 120.0 2005/05/29 07:38:15 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' OTA_TDB_WAITLIST_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |--------------------------< AUTO_ENROLL_FROM_WAITLIST >-------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure AUTO_ENROLL_FROM_WAITLIST
13 (p_validate in boolean default false
14 ,p_business_group_id in number
15 ,p_event_id in number default null
16 ) is
17
18 --
19 -- Declare cursors and local variables
20 --
21 type r_rec is record (
22 booking_id number,
23 delegate_person_id number,
24 delegate_contact_id number,
25 number_of_places number,
26 object_version_number number,
27 finance_line_id number,
28 tfl_object_version_number number,
29 date_booking_placed date
30 );
31
32 type t_table is table of r_rec index by binary_integer;
33
34 t_waitlist_table t_table;
35
36 cursor c_date_waitlist is
37 select tdb.booking_id,
38 tdb.delegate_person_id,
39 tdb.delegate_contact_id,
40 tdb.number_of_places,
41 tdb.object_version_number,
42 tfl.finance_line_id,
43 tfl.object_version_number tfl_object_version_number,
44 tdb.date_booking_placed ,
45 tdb.internal_booking_flag
46 from ota_delegate_bookings tdb,
47 ota_booking_status_types bst,
48 ota_finance_lines tfl
49 where tfl.booking_id(+) = tdb.booking_id
50 and tdb.booking_status_type_id = bst.booking_status_type_id
51 and bst.type = 'W'
52 and tdb.event_id = p_event_id
53 order by tdb.date_booking_placed;
54
55 cursor c_priority_waitlist is
56 select tdb.booking_id,
57 tdb.delegate_person_id,
58 tdb.delegate_contact_id,
59 tdb.number_of_places,
60 tdb.object_version_number,
61 tfl.finance_line_id,
62 tfl.object_version_number tfl_object_version_number,
63 tdb.date_booking_placed ,
64 tdb.internal_booking_flag
65 from ota_delegate_bookings tdb,
66 ota_booking_status_types bst,
67 ota_finance_lines tfl
68 where tfl.booking_id(+)= tdb.booking_id
69 and tdb.booking_status_type_id = bst.booking_status_type_id
70 and bst.type = 'W'
71 and tdb.event_id = p_event_id
72 order by tdb.booking_priority,
73 tdb.booking_id;
74
75 l_proc varchar2(72) := g_package||'AUTO_ENROLL_FROM_WAITLIST';
76 l_count number := 0;
77 l_vacancies number;
78 l_status_type_id number;
79 l_dummy boolean;
80
81 e_validation_error exception;
82 pragma exception_init(e_validation_error, -20001);
83
84 begin
85
86 hr_utility.set_location('Entering:'|| l_proc, 10);
87
88 --
89 -- Issue a savepoint
90 --
91 savepoint AUTO_ENROLL_FROM_WAITLIST;
92 hr_utility.set_location(l_proc, 20);
93
94 --
95 -- Call Before Process User Hook
96 --
97 begin
98 OTA_TDB_WAITLIST_BK1.AUTO_ENROLL_FROM_WAITLIST_B
99 (p_business_group_id => p_business_group_id
100 ,p_event_id => p_event_id
101 );
102 exception
103 when hr_api.cannot_find_prog_unit then
104 hr_api.cannot_find_prog_unit_error
105 (p_module_name => 'AUTO_ENROLL_FROM_WAITLIST'
106 ,p_hook_type => 'BP'
107 );
108 end;
109
110 hr_utility.set_location(l_proc, 30);
111 --
112 -- Validation in addition to Row Handlers
113 --
114
115
116
117 hr_utility.set_location(l_proc, 40);
118 --
119 -- Process Logic
120 --
121
122 l_status_type_id := fnd_profile.value('OTA_AUTO_WAITLIST_BOOKING_STATUS');
123
124 if l_status_type_id is null then
125 --
126 --
127 -- As we don't know what status to set the waitlistees to, we can't continue.
128 -- Because of previous checks, this condition should never occur, so we don't take
129 -- any further action here.
130
131 null;
132 --
133 else
134 --
135 --
136 -- Build a representation of the waitlist, correctly ordered, in a table.
137 -- This will save on coding, as we will only require one loop to go
138 -- through the waitlist.
139 --
140
141 if fnd_profile.value('OTA_WAITLIST_SORT_CRITERIA') = 'BP' then
142 --
143 for l_waitlist_entry in c_priority_waitlist loop
144 --
145 t_waitlist_table(l_count).number_of_places := l_waitlist_entry.number_of_places;
146 t_waitlist_table(l_count).booking_id := l_waitlist_entry.booking_id;
147 t_waitlist_table(l_count).delegate_person_id := l_waitlist_entry.delegate_person_id;
148 t_waitlist_table(l_count).delegate_contact_id := l_waitlist_entry.delegate_contact_id;
149 t_waitlist_table(l_count).object_version_number := l_waitlist_entry.object_version_number;
150 t_waitlist_table(l_count).finance_line_id := l_waitlist_entry.finance_line_id;
151 t_waitlist_table(l_count).tfl_object_version_number := l_waitlist_entry.tfl_object_version_number;
152 t_waitlist_table(l_count).date_booking_placed := l_waitlist_entry.date_booking_placed;
153
154
155 l_count := l_count+1;
156 --
157 end loop;
158 --
159 else
160 --
161 for l_waitlist_entry in c_date_waitlist loop
162 --
163 t_waitlist_table(l_count).number_of_places := l_waitlist_entry.number_of_places;
164 t_waitlist_table(l_count).booking_id := l_waitlist_entry.booking_id;
165 t_waitlist_table(l_count).delegate_person_id := l_waitlist_entry.delegate_person_id;
166 t_waitlist_table(l_count).delegate_contact_id := l_waitlist_entry.delegate_contact_id;
167 t_waitlist_table(l_count).object_version_number := l_waitlist_entry.object_version_number;
168 t_waitlist_table(l_count).finance_line_id := l_waitlist_entry.finance_line_id;
169 t_waitlist_table(l_count).tfl_object_version_number := l_waitlist_entry.tfl_object_version_number;
170 t_waitlist_table(l_count).date_booking_placed := l_waitlist_entry.date_booking_placed;
171
172
173 l_count := l_count+1;
174 --
175 end loop;
176 --
177 end if;
178
179 for i in t_waitlist_table.first..t_waitlist_table.last loop
180 --
181 l_vacancies := ota_evt_bus2.get_vacancies(p_event_id);
182
183 if l_vacancies <= 0 then
184 --
185 exit;
186 --
187 end if;
188
189 if t_waitlist_table(i).number_of_places <= l_vacancies or
190 l_vacancies is null then
191 --
192 hr_utility.set_location('UPDATING DETAILS FOR BOOKING #'||to_char(t_waitlist_table(i).booking_id), 41);
193 begin
194 --
195 l_dummy := ota_tdb_bus2.other_bookings_clash(to_char(t_waitlist_table(i).delegate_person_id),
196 to_char(t_waitlist_table(i).delegate_contact_id),
197 p_event_id,
198 l_status_type_id);
199
200 hr_utility.set_location('NEW STATUS = '||to_char(l_status_type_id), 42);
201
202 ota_tdb_api_upd2.update_enrollment(
203 p_booking_id => t_waitlist_table(i).booking_id
204 ,p_object_version_number => t_waitlist_table(i).object_version_number
205 ,p_tfl_object_version_number => t_waitlist_table(i).tfl_object_version_number
206 ,p_event_id => p_event_id
207 ,p_finance_line_id => t_waitlist_table(i).finance_line_id
208 ,p_number_of_places => t_waitlist_table(i).number_of_places
209 ,p_date_status_changed => sysdate -- bug 1890732
210 ,p_booking_status_type_id => l_status_type_id
211 ,p_date_booking_placed => t_waitlist_table(i).date_booking_placed);
212
213
214 --
215 exception
216 when e_validation_error then
217 hr_utility.set_location('ENROLLMENT ERROR BEING HANDLED', 49);
218
219 --
220 -- This exception handler is executed following any application
221 -- error encountered when attempting to enroll a delegate from
222 -- the waitlist. (e.g. a double booking was found)
223 --
224 -- If code is to be added at a later stage to handle notifications
225 -- of these errors, this would be the best place to call the code from.
226 -- At present, there are no notifications, so we take no action,
227 -- other than to proceed onto the next waitlisted booking.
228 --
229
230 NULL;
231 --
232 end;
233 --
234 end if;
235 --
236 end loop;
237 --
238 end if;
239
240
241 hr_utility.set_location(l_proc, 50);
242 --
243 -- Call After Process User Hook
244 --
245 begin
246 OTA_TDB_WAITLIST_BK1.AUTO_ENROLL_FROM_WAITLIST_A
247 (p_business_group_id => p_business_group_id
248 ,p_event_id => p_event_id
249 );
250 exception
251 when hr_api.cannot_find_prog_unit then
252 hr_api.cannot_find_prog_unit_error
253 (p_module_name => 'AUTO_ENROLL_FROM_WAITLIST'
254 ,p_hook_type => 'AP'
255 );
256 end;
257 hr_utility.set_location(l_proc, 60);
258 --
259 -- When in validation only mode raise the Validate_Enabled exception
260 --
261 if p_validate then
262 raise hr_api.validate_enabled;
263 end if;
264 --
265 hr_utility.set_location(' Leaving:'||l_proc, 70);
266 exception
267 when hr_api.validate_enabled then
268 --
269 -- As the Validate_Enabled exception has been raised
270 -- we must rollback to the savepoint
271 --
272 rollback to AUTO_ENROLL_FROM_WAITLIST;
273 --
274 hr_utility.set_location(' Leaving:'||l_proc, 80);
275 when others then
276 --
277 -- A validation or unexpected error has occured
278 --
279 rollback to AUTO_ENROLL_FROM_WAITLIST;
280 hr_utility.set_location(' Leaving:'||l_proc, 90);
281 raise;
282 end AUTO_ENROLL_FROM_WAITLIST;
283 --
284 end OTA_TDB_WAITLIST_API;