[Home] [Help]
PACKAGE BODY: APPS.PER_BOOKINGS_SWI
Source
1 PACKAGE BODY PER_BOOKINGS_SWI As
2 /* $Header: pebkgswi.pkb 120.6 2010/08/19 12:01:51 kgowripe ship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'per_bookings_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< create_per_booking >---------------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE create_per_bookings
12 (p_Rowid IN OUT NOCOPY VARCHAR2
13 ,p_Booking_Id IN OUT NOCOPY NUMBER
14 ,p_Business_Group_Id NUMBER
15 ,p_Person_Id NUMBER
16 ,p_Event_Id NUMBER
17 ,p_Message VARCHAR2 default null
18 ,p_Token VARCHAR2 default null
19 ,p_Comments VARCHAR2 default null
20 ,p_Attribute_Category VARCHAR2 default null
21 ,p_Attribute1 VARCHAR2 default null
22 ,p_Attribute2 VARCHAR2 default null
23 ,p_Attribute3 VARCHAR2 default null
24 ,p_Attribute4 VARCHAR2 default null
25 ,p_Attribute5 VARCHAR2 default null
26 ,p_Attribute6 VARCHAR2 default null
27 ,p_Attribute7 VARCHAR2 default null
28 ,p_Attribute8 VARCHAR2 default null
29 ,p_Attribute9 VARCHAR2 default null
30 ,p_Attribute10 VARCHAR2 default null
31 ,p_Attribute11 VARCHAR2 default null
32 ,p_Attribute12 VARCHAR2 default null
33 ,p_Attribute13 VARCHAR2 default null
34 ,p_Attribute14 VARCHAR2 default null
35 ,p_Attribute15 VARCHAR2 default null
36 ,p_Attribute16 VARCHAR2 default null
37 ,p_Attribute17 VARCHAR2 default null
38 ,p_Attribute18 VARCHAR2 default null
39 ,p_Attribute19 VARCHAR2 default null
40 ,p_Attribute20 VARCHAR2 default null
41 ,p_Primary_Interviewer_Flag VARCHAR2 default null
42 ,p_return_status out nocopy varchar2
43 ) is
44 --
45 -- Variables for API Boolean parameters
46 l_validate boolean;
47 --
48 -- Variables for IN/OUT parameters
49 --
50 -- Other variables
51 l_interview_details_id number;
52 l_proc varchar2(72) := g_package ||'create_per_bookings';
53 Begin
54 hr_utility.set_location(' Entering:' || l_proc,10);
55 --
56 -- Issue a savepoint
57 --
58 savepoint create_per_bookings_swi;
59 --
60 -- Initialise Multiple Message Detection
61 --
62 hr_multi_message.enable_message_list;
63 --
64 -- Remember IN OUT parameter IN values
65 --
66 -- Call API
67 --
68 per_bookings_pkg.Insert_Row(X_Rowid => p_Rowid
69 ,X_Booking_Id => p_Booking_Id
70 ,X_Business_Group_Id => p_Business_Group_Id
71 ,X_Person_Id => p_Person_Id
72 ,X_Event_Id => p_Event_Id
73 ,X_Message => p_Message
74 ,X_Token => p_Token
75 ,X_Comments => p_Comments
76 ,X_Attribute_Category => p_Attribute_Category
77 ,X_Attribute1 => p_Attribute1
78 ,X_Attribute2 => p_Attribute2
79 ,X_Attribute3 => p_Attribute3
80 ,X_Attribute4 => p_Attribute4
81 ,X_Attribute5 => p_Attribute5
82 ,X_Attribute6 => p_Attribute6
83 ,X_Attribute7 => p_Attribute7
84 ,X_Attribute8 => p_Attribute8
85 ,X_Attribute9 => p_Attribute9
86 ,X_Attribute10 => p_Attribute10
87 ,X_Attribute11 => p_Attribute11
88 ,X_Attribute12 => p_Attribute12
89 ,X_Attribute13 => p_Attribute13
90 ,X_Attribute14 => p_Attribute14
91 ,X_Attribute15 => p_Attribute15
92 ,X_Attribute16 => p_Attribute16
93 ,X_Attribute17 => p_Attribute17
94 ,X_Attribute18 => p_Attribute18
95 ,X_Attribute19 => p_Attribute19
96 ,X_Attribute20 => p_Attribute20
97 ,X_Primary_Interviewer_Flag => p_Primary_Interviewer_Flag
98 );
99 p_return_status := hr_multi_message.get_return_status_disable;
100 hr_utility.set_location(' Leaving:' || l_proc, 20);
101 --
102 exception
103 when hr_multi_message.error_message_exist then
104 --
105 -- Catch the Multiple Message List exception which
106 -- indicates API processing has been aborted because
107 -- at least one message exists in the list.
108 --
109 rollback to create_per_bookings_swi;
110 --
111 -- Reset IN OUT parameters and set OUT parameters
112 --
113 p_return_status := hr_multi_message.get_return_status_disable;
114 hr_utility.set_location(' Leaving:' || l_proc, 30);
115 when others then
116 --
117 -- When Multiple Message Detection is enabled catch
118 -- any Application specific or other unexpected
119 -- exceptions. Adding appropriate details to the
120 -- Multiple Message List. Otherwise re-raise the
121 -- error.
122 --
123 rollback to create_per_bookings_swi;
124 if hr_multi_message.unexpected_error_add(l_proc) then
125 hr_utility.set_location(' Leaving:' || l_proc,40);
126 raise;
127 end if;
128 --
129 -- Reset IN OUT and set OUT parameters
130 --
131 p_return_status := hr_multi_message.get_return_status_disable;
132 hr_utility.set_location(' Leaving:' || l_proc,50);
133 end create_per_bookings;
134 --
135 --
136 -- ----------------------------------------------------------------------------
137 -- |-------------------------< update_per_bookings ---------------------------|
138 -- ----------------------------------------------------------------------------
139 PROCEDURE update_per_bookings
140 (p_Rowid IN OUT NOCOPY VARCHAR2
141 ,p_Booking_Id IN OUT NOCOPY NUMBER
142 ,p_Business_Group_Id NUMBER
143 ,p_Person_Id NUMBER
144 ,p_Event_Id NUMBER
145 ,p_Message VARCHAR2 default hr_api.g_varchar2
146 ,p_Token VARCHAR2 default hr_api.g_varchar2
147 ,p_Comments VARCHAR2 default hr_api.g_varchar2
148 ,p_Attribute_Category VARCHAR2 default hr_api.g_varchar2
149 ,p_Attribute1 VARCHAR2 default hr_api.g_varchar2
150 ,p_Attribute2 VARCHAR2 default hr_api.g_varchar2
151 ,p_Attribute3 VARCHAR2 default hr_api.g_varchar2
152 ,p_Attribute4 VARCHAR2 default hr_api.g_varchar2
153 ,p_Attribute5 VARCHAR2 default hr_api.g_varchar2
154 ,p_Attribute6 VARCHAR2 default hr_api.g_varchar2
155 ,p_Attribute7 VARCHAR2 default hr_api.g_varchar2
156 ,p_Attribute8 VARCHAR2 default hr_api.g_varchar2
157 ,p_Attribute9 VARCHAR2 default hr_api.g_varchar2
158 ,p_Attribute10 VARCHAR2 default hr_api.g_varchar2
159 ,p_Attribute11 VARCHAR2 default hr_api.g_varchar2
160 ,p_Attribute12 VARCHAR2 default hr_api.g_varchar2
161 ,p_Attribute13 VARCHAR2 default hr_api.g_varchar2
162 ,p_Attribute14 VARCHAR2 default hr_api.g_varchar2
163 ,p_Attribute15 VARCHAR2 default hr_api.g_varchar2
164 ,p_Attribute16 VARCHAR2 default hr_api.g_varchar2
165 ,p_Attribute17 VARCHAR2 default hr_api.g_varchar2
166 ,p_Attribute18 VARCHAR2 default hr_api.g_varchar2
167 ,p_Attribute19 VARCHAR2 default hr_api.g_varchar2
168 ,p_Attribute20 VARCHAR2 default hr_api.g_varchar2
169 ,p_Primary_Interviewer_Flag VARCHAR2 default null
170 ,p_return_status out nocopy varchar2
171 ) is
172 --
173 -- Variables for API Boolean parameters
174 l_validate boolean;
175 --
176 -- Variables for IN/OUT parameters
177 --
178 -- Other variables
179 l_interview_details_id number;
180 l_proc varchar2(72) := g_package ||'update_per_bookings';
181 --
182 cursor csr_rowid is
183 select rowid ROW_ID, b.*
184 from per_bookings b
185 where booking_id = p_Booking_Id;
186 l_oldrec csr_rowid%ROWTYPE;
187 p_rec per_bookings%ROWTYPE;
188 Begin
189 hr_utility.set_location(' Entering:' || l_proc,10);
190 --
191 -- Issue a savepoint
192 --
193 savepoint update_per_bookings_swi;
194 --
195 -- Initialise Multiple Message Detection
196 --
197 hr_multi_message.enable_message_list;
198 --
199 open csr_rowid;
200 fetch csr_rowid into l_oldrec;
201 if csr_rowid%found then
202 close csr_rowid;
203 else
204 close csr_rowid;
205 -- booking_id is invalid, raise error
206 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
207 fnd_message.raise_error;
208 end if;
209 p_rowid := l_oldrec.row_id;
210 IF p_comments = hr_api.g_varchar2 THEN
211 p_rec.comments := l_oldrec.comments;
212 ELSE
213 p_rec.comments := p_comments;
214 END IF;
215 IF p_Primary_Interviewer_Flag = hr_api.g_varchar2 THEN
216 p_rec.Primary_Interviewer_Flag := l_oldrec.Primary_Interviewer_Flag;
217 ELSE
218 p_rec.Primary_Interviewer_Flag := p_Primary_Interviewer_Flag;
219 END IF;
220 IF p_Attribute_Category = hr_api.g_varchar2 THEN
221 p_rec.Attribute_Category := l_oldrec.Attribute_Category;
222 ELSE
223 p_rec.Attribute_Category := p_attribute_category;
224 END IF;
225 IF p_Attribute1 = hr_api.g_varchar2 THEN
226 p_rec.Attribute1 := l_oldrec.Attribute1;
227 ELSE
228 p_rec.Attribute1 := p_attribute1;
229 END IF;
230 IF p_Attribute2 = hr_api.g_varchar2 THEN
231 p_rec.Attribute2 := l_oldrec.Attribute2;
232 ELSE
233 p_rec.Attribute2 := p_attribute2;
234 END IF;
235 IF p_Attribute3 = hr_api.g_varchar2 THEN
236 p_rec.Attribute3 := l_oldrec.Attribute3;
237 ELSE
238 p_rec.Attribute3 := p_attribute3;
239 END IF;
240 IF p_Attribute4 = hr_api.g_varchar2 THEN
241 p_rec.Attribute4 := l_oldrec.Attribute4;
242 ELSE
243 p_rec.Attribute4 := p_attribute4;
244 END IF;
245 IF p_Attribute5 = hr_api.g_varchar2 THEN
246 p_rec.Attribute5 := l_oldrec.Attribute5;
247 ELSE
248 p_rec.Attribute5 := p_attribute5;
249 END IF;
250 IF p_Attribute6 = hr_api.g_varchar2 THEN
251 p_rec.Attribute6 := l_oldrec.Attribute6;
252 ELSE
253 p_rec.Attribute6 := p_attribute6;
254 END IF;
255 IF p_Attribute7 = hr_api.g_varchar2 THEN
256 p_rec.Attribute7 := l_oldrec.Attribute7;
257 ELSE
258 p_rec.Attribute7 := p_attribute7;
259 END IF;
260 IF p_Attribute8 = hr_api.g_varchar2 THEN
261 p_rec.Attribute8 := l_oldrec.Attribute8;
262 ELSE
263 p_rec.Attribute8 := p_attribute8;
264 END IF;
265 IF p_Attribute9 = hr_api.g_varchar2 THEN
266 p_rec.Attribute9 := l_oldrec.Attribute9;
267 ELSE
268 p_rec.Attribute9 := p_attribute9;
269 END IF;
270 IF p_Attribute10 = hr_api.g_varchar2 THEN
271 p_rec.Attribute10 := l_oldrec.Attribute10;
272 ELSE
273 p_rec.Attribute10 := p_attribute10;
274 END IF;
275 IF p_Attribute11 = hr_api.g_varchar2 THEN
276 p_rec.Attribute11 := l_oldrec.Attribute11;
277 ELSE
278 p_rec.Attribute11 := p_attribute11;
279 END IF;
280 IF p_Attribute12 = hr_api.g_varchar2 THEN
281 p_rec.Attribute12 := l_oldrec.Attribute12;
282 ELSE
283 p_rec.Attribute12 := p_attribute12;
284 END IF;
285 IF p_Attribute13 = hr_api.g_varchar2 THEN
286 p_rec.Attribute13 := l_oldrec.Attribute13;
287 ELSE
288 p_rec.Attribute13 := p_attribute13;
289 END IF;
290 IF p_Attribute14 = hr_api.g_varchar2 THEN
291 p_rec.Attribute14 := l_oldrec.Attribute14;
292 ELSE
293 p_rec.Attribute14 := p_attribute14;
294 END IF;
295 IF p_Attribute15 = hr_api.g_varchar2 THEN
296 p_rec.Attribute15 := l_oldrec.Attribute15;
297 ELSE
298 p_rec.Attribute15 := p_attribute15;
299 END IF;
300 IF p_Attribute16 = hr_api.g_varchar2 THEN
301 p_rec.Attribute16 := l_oldrec.Attribute16;
302 ELSE
303 p_rec.Attribute16 := p_attribute16;
304 END IF;
305 IF p_Attribute17 = hr_api.g_varchar2 THEN
306 p_rec.Attribute17 := l_oldrec.Attribute17;
307 ELSE
308 p_rec.Attribute17 := p_attribute17;
309 END IF;
310 IF p_Attribute18 = hr_api.g_varchar2 THEN
311 p_rec.Attribute18 := l_oldrec.Attribute18;
312 ELSE
313 p_rec.Attribute18 := p_attribute18;
314 END IF;
315 IF p_Attribute19 = hr_api.g_varchar2 THEN
316 p_rec.Attribute19 := l_oldrec.Attribute19;
317 ELSE
318 p_rec.Attribute19 := p_attribute19;
319 END IF;
320 IF p_Attribute20 = hr_api.g_varchar2 THEN
321 p_rec.Attribute20 := l_oldrec.Attribute20;
322 ELSE
323 p_rec.Attribute20 := p_attribute20;
324 END IF;
325 --
326 -- Call API
327 --
328 per_bookings_pkg.Update_Row(X_Rowid => p_Rowid
329 ,X_Booking_Id => p_Booking_Id
330 ,X_Business_Group_Id => p_Business_Group_Id
331 ,X_Person_Id => p_Person_Id
332 ,X_Event_Id => p_Event_Id
333 ,X_Message => p_Message
334 ,X_Token => p_Token
335 ,X_Comments => p_rec.Comments
336 ,X_Attribute_Category => p_rec.Attribute_Category
337 ,X_Attribute1 => p_rec.Attribute1
338 ,X_Attribute2 => p_rec.Attribute2
339 ,X_Attribute3 => p_rec.Attribute3
340 ,X_Attribute4 => p_rec.Attribute4
341 ,X_Attribute5 => p_rec.Attribute5
342 ,X_Attribute6 => p_rec.Attribute6
343 ,X_Attribute7 => p_rec.Attribute7
344 ,X_Attribute8 => p_rec.Attribute8
345 ,X_Attribute9 => p_rec.Attribute9
346 ,X_Attribute10 => p_rec.Attribute10
347 ,X_Attribute11 => p_rec.Attribute11
348 ,X_Attribute12 => p_rec.Attribute12
349 ,X_Attribute13 => p_rec.Attribute13
350 ,X_Attribute14 => p_rec.Attribute14
351 ,X_Attribute15 => p_rec.Attribute15
352 ,X_Attribute16 => p_rec.Attribute16
353 ,X_Attribute17 => p_rec.Attribute17
354 ,X_Attribute18 => p_rec.Attribute18
355 ,X_Attribute19 => p_rec.Attribute19
356 ,X_Attribute20 => p_rec.Attribute20
357 ,X_Primary_Interviewer_Flag => p_rec.Primary_Interviewer_Flag
358 );
359 p_return_status := hr_multi_message.get_return_status_disable;
360 hr_utility.set_location(' Leaving:' || l_proc, 20);
361 --
362 exception
363 when hr_multi_message.error_message_exist then
364 --
365 -- Catch the Multiple Message List exception which
366 -- indicates API processing has been aborted because
367 -- at least one message exists in the list.
368 --
369 rollback to update_per_bookings_swi;
370 --
371 -- Reset IN OUT parameters and set OUT parameters
372 --
373 p_return_status := hr_multi_message.get_return_status_disable;
374 hr_utility.set_location(' Leaving:' || l_proc, 30);
375 when others then
376 --
377 -- When Multiple Message Detection is enabled catch
378 -- any Application specific or other unexpected
379 -- exceptions. Adding appropriate details to the
380 -- Multiple Message List. Otherwise re-raise the
381 -- error.
382 --
383 rollback to update_per_bookings_swi;
384 if hr_multi_message.unexpected_error_add(l_proc) then
385 hr_utility.set_location(' Leaving:' || l_proc,40);
386 raise;
387 end if;
388 --
389 -- Reset IN OUT and set OUT parameters
390 --
391 p_return_status := hr_multi_message.get_return_status_disable;
392 hr_utility.set_location(' Leaving:' || l_proc,50);
393 end update_per_bookings;
394 -- ----------------------------------------------------------------------------
395 PROCEDURE delete_per_bookings
396 (p_Booking_Id IN NUMBER
397 ,p_return_status out nocopy varchar2
398 ) is
399 l_Rowid VARCHAR2(30);
400 l_proc varchar2(72) := g_package ||'delete_per_bookings';
401 cursor csr_rowid is
402 select rowid
403 from per_bookings
404 where booking_id = p_Booking_Id;
405 begin
406 hr_utility.set_location(' Entering:' || l_proc,10);
407 --
408 -- Issue a savepoint
409 --
410 savepoint delete_per_bookings_swi;
411 --
412 -- Initialise Multiple Message Detection
413 --
414 hr_multi_message.enable_message_list;
415 --
416 open csr_rowid;
417 fetch csr_rowid into l_Rowid;
418 if csr_rowid%found then
419 close csr_rowid;
420 else
421 close csr_rowid;
422 -- booking_id is invalid, raise error
423 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
424 fnd_message.raise_error;
425 end if;
426 --
427 -- Call API
428 --
429 per_bookings_pkg.Delete_Row(X_Rowid => l_Rowid);
430 --
431 p_return_status := hr_multi_message.get_return_status_disable;
432 hr_utility.set_location(' Leaving:' || l_proc, 20);
433 ---
434 exception
435 when hr_multi_message.error_message_exist then
436 --
437 -- Catch the Multiple Message List exception which
438 -- indicates API processing has been aborted because
439 -- at least one message exists in the list.
440 --
441 rollback to delete_per_bookings_swi;
442 --
443 -- Reset IN OUT parameters and set OUT parameters
444 --
445 p_return_status := hr_multi_message.get_return_status_disable;
446 hr_utility.set_location(' Leaving:' || l_proc, 30);
447 when others then
448 --
449 -- When Multiple Message Detection is enabled catch
450 -- any Application specific or other unexpected
451 -- exceptions. Adding appropriate details to the
452 -- Multiple Message List. Otherwise re-raise the
453 -- error.
454 --
455 rollback to delete_per_bookings_swi;
456 if hr_multi_message.unexpected_error_add(l_proc) then
457 hr_utility.set_location(' Leaving:' || l_proc,40);
458 raise;
459 end if;
460 --
461 -- Reset IN OUT and set OUT parameters
462 --
463 p_return_status := hr_multi_message.get_return_status_disable;
464 hr_utility.set_location(' Leaving:' || l_proc,50);
465 end delete_per_bookings;
466
467 --
468 end PER_BOOKINGS_SWI;