[Home] [Help]
PACKAGE BODY: APPS.PQP_RIW_ENROLL_WRAPPER
Source
1 package body pqp_riw_enroll_wrapper as
2 /* $Header: pqpriwenwr.pkb 120.0.12010000.3 2009/01/21 13:49:13 sbrahmad noship $ */
3
4 -- =============================================================================
5 -- ~ Package Body Global variables:
6 -- =============================================================================
7 g_package varchar2(33) := 'enrollment_wrapper_api.';
8 procedure InsUpd_Enroll
9 ( p_effective_date in date default null
10 ,p_booking_id in number
11 ,p_booking_status_type_id in number
12 ,p_delegate_person_id in number default null
13 ,p_contact_id in number default null
14 ,p_business_group_id in number
15 ,p_event_id in number
16 ,p_customer_id in number default null
17 ,p_authorizer_person_id in number default null
18 ,p_date_booking_placed in date
19 ,p_corespondent in varchar2 default null
20 ,p_internal_booking_flag in varchar2
21 ,p_number_of_places in number
22 ,p_object_version_number in number
23 ,p_administrator in number default null
24 ,p_booking_priority in varchar2 default null
25 ,p_comments in varchar2 default null
26 ,p_contact_address_id in number default null
27 ,p_delegate_contact_phone in varchar2 default null
28 ,p_delegate_contact_fax in varchar2 default null
29 ,p_third_party_customer_id in number default null
30 ,p_third_party_contact_id in number default null
31 ,p_third_party_address_id in number default null
32 ,p_third_party_contact_phone in varchar2 default null
33 ,p_third_party_contact_fax in varchar2 default null
34 ,p_date_status_changed in date default null
35 ,p_failure_reason in varchar2 default null
36 ,p_attendance_result in varchar2 default null
37 ,p_language_id in number default null
38 ,p_source_of_booking in varchar2 default null
39 ,p_special_booking_instructions in varchar2 default null
40 ,p_successful_attendance_flag in varchar2 default null
41 ,p_tdb_information_category in varchar2 default null
42 ,p_tdb_information1 in varchar2 default null
43 ,p_tdb_information2 in varchar2 default null
44 ,p_tdb_information3 in varchar2 default null
45 ,p_tdb_information4 in varchar2 default null
46 ,p_tdb_information5 in varchar2 default null
47 ,p_tdb_information6 in varchar2 default null
48 ,p_tdb_information7 in varchar2 default null
49 ,p_tdb_information8 in varchar2 default null
50 ,p_tdb_information9 in varchar2 default null
51 ,p_tdb_information10 in varchar2 default null
52 ,p_tdb_information11 in varchar2 default null
53 ,p_tdb_information12 in varchar2 default null
54 ,p_tdb_information13 in varchar2 default null
55 ,p_tdb_information14 in varchar2 default null
56 ,p_tdb_information15 in varchar2 default null
57 ,p_tdb_information16 in varchar2 default null
58 ,p_tdb_information17 in varchar2 default null
59 ,p_tdb_information18 in varchar2 default null
60 ,p_tdb_information19 in varchar2 default null
61 ,p_tdb_information20 in varchar2 default null
62 ,p_create_finance_line in varchar2 default null
63 ,p_finance_header_id in number default null
64 ,p_currency_code in varchar2 default null
65 ,p_standard_amount in number default null
66 ,p_unitary_amount in number default null
67 ,p_money_amount in number default null
68 ,p_booking_deal_id in number default null
69 ,p_booking_deal_type in varchar2 default null
70 ,p_finance_line_id in out nocopy number
71 ,p_enrollment_type in varchar2 default null
72 ,p_validate in number default hr_api.g_false_num
73 ,p_organization_id in number default null
74 ,p_sponsor_person_id in number default null
75 ,p_sponsor_assignment_id in number default null
76 ,p_person_address_id in number default null
77 ,p_delegate_assignment_id in number default null
78 ,p_delegate_contact_id in number default null
79 ,p_delegate_contact_email in varchar2 default null
80 ,p_third_party_email in varchar2 default null
81 ,p_person_address_type in varchar2 default null
82 ,p_line_id in number default null
83 ,p_org_id in number default null
84 ,p_daemon_flag in varchar2 default null
85 ,p_daemon_type in varchar2 default null
86 ,p_old_event_id in number default null
87 ,p_quote_line_id in number default null
88 ,p_interface_source in varchar2 default null
89 ,p_total_training_time in varchar2 default null
90 ,p_content_player_status in varchar2 default null
91 ,p_score in number default null
92 ,p_completed_content in number default null
93 ,p_total_content in number default null
94 ,p_return_status out nocopy varchar2
95 ,p_booking_justification_id in number default null
96 ,p_is_history_flag in varchar2 default 'N'
97 ,p_override_prerequisites in varchar2 default null
98 ,p_override_learner_access in varchar2 default null
99 ,P_CRT_UPD in varchar2 default null
100 ,p_status_change_comments in varchar2 default hr_api.g_varchar2
101 ,p_update_finance_line in varchar2 default hr_api.g_varchar2
102 ,p_tfl_object_version_number in number default null
103 ) is
104
105 --
106 -- Variables for API Boolean parameters
107 l_validate boolean;
108
109 --
110 -- Variables for IN/OUT parameters
111 l_finance_line_id number;
112 --
113 -- Other variables
114 l_proc varchar2(72) := g_package ||'ENROLLMENT_WRAPPER_API';
115 l_booking_id number;
116 l_booking number;
117 l_create_flag number;
118 l_error_msg varchar2(4000);
119 m_validate number :=0; -- No validation
120
121 -- p_status_change_comments varchar2(30) default hr_api.g_varchar2;
122 -- p_update_finance_line varchar2(30) default hr_api.g_varchar2;
123 l_tfl_object_version_number number;
124 l_date_booking_placed ota_delegate_bookings.date_booking_placed%TYPE;
125 l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
126 c_delegate_assignment_id number default null;
127 c_sponsor_assignment_id number default null;
128
129 --$ Get upload mode - "Create and Update" (C) or "Update Only" (U)
130 -- or "View/Download Only" (D)
131 g_crt_upd varchar2 (1);
132
133 --$ Exceptions
134 e_upl_not_allowed exception; -- when mode is 'View Only'
135 e_crt_not_allowed exception; -- when mode is 'Update Only'
136 g_upl_err_msg varchar2(100) := 'Upload NOT allowed.';
137 g_crt_err_msg varchar2(100) := 'Creating NOT allowed.';
138
139 l_bo_id number(9);
140 l_object_version_number number(3);
141 l_obj_ver_num number(3);
142
143 CURSOR booking_csr
144 IS
145 SELECT b.date_booking_placed
146 FROM ota_delegate_bookings b
147 WHERE b.booking_id = p_booking_id;
148
149
150 CURSOR deleg_ass_id
151 IS
152 SELECT asg.assignment_id
153 FROM per_assignments_f asg,per_people_f per,PER_PERSON_TYPES PPT
154 WHERE per.person_id = asg.person_id
155 and ((asg.primary_flag = 'Y' and ppt.system_person_type in ('EMP','CWK','OTHER'))
156 OR (asg.assignment_type = 'A' and ppt.system_person_type ='APL'))
157 and (SYSDATE BETWEEN ASG.EFFECTIVE_START_DATE and ASG.EFFECTIVE_END_DATE)
158 and (SYSDATE BETWEEN per.EFFECTIVE_START_DATE AND per.EFFECTIVE_END_DATE)
159 and ppt.person_type_id = per.person_type_id and ppt.business_group_id = per.business_group_id
160 and per.person_id =p_delegate_person_id;
161
162 CURSOR spon_ass_id
163 IS
164 SELECT asg.assignment_id
165 FROM per_assignments_f asg,per_people_f per,PER_PERSON_TYPES PPT
166 WHERE per.person_id = asg.person_id
167 and ((asg.primary_flag = 'Y' and ppt.system_person_type in ('EMP','CWK','OTHER'))
168 OR (asg.assignment_type = 'A' and ppt.system_person_type ='APL'))
169 and (SYSDATE BETWEEN ASG.EFFECTIVE_START_DATE and ASG.EFFECTIVE_END_DATE)
170 and (SYSDATE BETWEEN per.EFFECTIVE_START_DATE AND per.EFFECTIVE_END_DATE)
171 and ppt.person_type_id = per.person_type_id and ppt.business_group_id = per.business_group_id
172 and per.person_id =p_sponsor_person_id;
173
174 Begin
175
176 -- hr_utility.trace_on(null , 'Enroll_Trace');
177 hr_utility.set_location(' Entering:' || l_proc,10);
178 hr_utility.trace('delegate person id : ' || p_delegate_person_id);
179 hr_utility.trace('P_CRT_UPD : ' || P_CRT_UPD);
180
181 if (P_CRT_UPD is not null) then
182 g_crt_upd := P_CRT_UPD;
183 end if;
184 if (p_delegate_person_id is not null) then
185 OPEN deleg_ass_id;
186 FETCH deleg_ass_id INTO c_delegate_assignment_id;
187 CLOSE deleg_ass_id;
188 end if;
189
190 if (p_delegate_person_id is not null) then
191 OPEN spon_ass_id;
192 FETCH spon_ass_id INTO c_sponsor_assignment_id;
193 CLOSE spon_ass_id;
194 end if;
195
196 hr_utility.trace('c_delegate_assignment_id '|| c_delegate_assignment_id);
197 hr_utility.trace('c_sponsor_assignment_id '|| c_sponsor_assignment_id);
198
199 hr_utility.trace('g_crt_upd : ' || g_crt_upd);
200
201
202
203 --
204 -- Issue a savepoint
205 --
206 l_create_flag :=1; -- Default value for creation.
207 savepoint enrollment_proc;
208
209 l_booking_id := p_booking_id;
210 if l_booking_id is not null then
211 l_create_flag := 2; --update booking
212 else
213 l_create_flag := 1; --create booking
214 end if;
215
216 hr_utility.set_location('The booking_id is : ', l_booking_id);
217
218
219
220 hr_utility.trace('l_create_flag'||l_create_flag);
221
222
223 if (g_crt_upd = 'D') then
224 raise e_upl_not_allowed; -- View only flag is enabled but Trying to Upload
225 end if;
226 if (g_crt_upd = 'U' and l_create_flag = 1) then
227 raise e_crt_not_allowed; -- Update only flag is enabled but Trying to Create
228 end if;
229
230
231 --
232 -- Call API
233 --
234
235 if(l_create_flag =1) then
236 create_delegate_booking
237 (p_effective_date => p_effective_date
238 ,p_booking_id => l_bo_id
239 ,p_booking_status_type_id => p_booking_status_type_id
240 ,p_delegate_person_id => p_delegate_person_id
241 ,p_contact_id => p_contact_id
242 ,p_business_group_id => p_business_group_id
243 ,p_event_id => p_event_id
244 ,p_customer_id => p_customer_id
245 ,p_authorizer_person_id => p_authorizer_person_id
246 ,p_date_booking_placed => p_date_booking_placed
247 ,p_corespondent => p_corespondent
248 ,p_internal_booking_flag => p_internal_booking_flag
249 ,p_number_of_places => p_number_of_places
250 ,p_object_version_number => l_obj_ver_num
251 ,p_administrator => p_administrator
252 ,p_booking_priority => p_booking_priority
253 ,p_comments => p_comments
254 ,p_contact_address_id => p_contact_address_id
255 ,p_delegate_contact_phone => p_delegate_contact_phone
256 ,p_delegate_contact_fax => p_delegate_contact_fax
257 ,p_third_party_customer_id => p_third_party_customer_id
258 ,p_third_party_contact_id => p_third_party_contact_id
259 ,p_third_party_address_id => p_third_party_address_id
260 ,p_third_party_contact_phone => p_third_party_contact_phone
261 ,p_third_party_contact_fax => p_third_party_contact_fax
262 ,p_date_status_changed => p_date_status_changed
263 ,p_failure_reason => p_failure_reason
264 ,p_attendance_result => p_attendance_result
265 ,p_language_id => p_language_id
266 ,p_source_of_booking => p_source_of_booking
267 ,p_special_booking_instructions => p_special_booking_instructions
268 ,p_successful_attendance_flag => p_successful_attendance_flag
269 ,p_tdb_information_category => p_tdb_information_category
270 ,p_tdb_information1 => p_tdb_information1
271 ,p_tdb_information2 => p_tdb_information2
272 ,p_tdb_information3 => p_tdb_information3
273 ,p_tdb_information4 => p_tdb_information4
274 ,p_tdb_information5 => p_tdb_information5
275 ,p_tdb_information6 => p_tdb_information6
276 ,p_tdb_information7 => p_tdb_information7
277 ,p_tdb_information8 => p_tdb_information8
278 ,p_tdb_information9 => p_tdb_information9
279 ,p_tdb_information10 => p_tdb_information10
280 ,p_tdb_information11 => p_tdb_information11
281 ,p_tdb_information12 => p_tdb_information12
282 ,p_tdb_information13 => p_tdb_information13
283 ,p_tdb_information14 => p_tdb_information14
284 ,p_tdb_information15 => p_tdb_information15
285 ,p_tdb_information16 => p_tdb_information16
286 ,p_tdb_information17 => p_tdb_information17
287 ,p_tdb_information18 => p_tdb_information18
288 ,p_tdb_information19 => p_tdb_information19
289 ,p_tdb_information20 => p_tdb_information20
290 ,p_create_finance_line => p_create_finance_line
291 ,p_finance_header_id => p_finance_header_id
292 ,p_currency_code => p_currency_code
293 ,p_standard_amount => p_standard_amount
294 ,p_unitary_amount => p_unitary_amount
295 ,p_money_amount => p_money_amount
296 ,p_booking_deal_id => p_booking_deal_id
297 ,p_booking_deal_type => p_booking_deal_type
298 ,p_finance_line_id => p_finance_line_id
299 ,p_enrollment_type => p_enrollment_type
300 ,p_validate => m_validate
301 ,p_organization_id => p_organization_id
302 ,p_sponsor_person_id => p_sponsor_person_id
303 ,p_sponsor_assignment_id => c_sponsor_assignment_id
304 ,p_person_address_id => p_person_address_id
305 ,p_delegate_assignment_id => c_delegate_assignment_id
306 ,p_delegate_contact_id => p_delegate_contact_id
307 ,p_delegate_contact_email => p_delegate_contact_email
308 ,p_third_party_email => p_third_party_email
309 ,p_person_address_type => p_person_address_type
310 ,p_line_id => p_line_id
311 ,p_org_id => p_org_id
312 ,p_daemon_flag => p_daemon_flag
313 ,p_daemon_type => p_daemon_type
314 ,p_old_event_id => p_old_event_id
315 ,p_quote_line_id => p_quote_line_id
316 ,p_interface_source => p_interface_source
317 ,p_total_training_time => p_total_training_time
318 ,p_content_player_status => p_content_player_status
319 ,p_score => p_score
320 ,p_completed_content => p_completed_content
321 ,p_total_content => p_total_content
322 ,p_return_status => p_return_status
323 ,p_booking_justification_id => p_booking_justification_id
324 ,p_is_history_flag => p_is_history_flag
325 ,p_override_prerequisites => p_override_prerequisites
326 ,p_override_learner_access => p_override_learner_access
327 );
328
329 end if ;
330
331 if(l_create_flag = 2) then
332
336 --p_object_version_number := 1;
333
334 hr_utility.trace('SRK in wrapper_proc booking p_booking_id '|| l_booking);
335
337 hr_utility.trace('SRK in wrapper_proc before update p_object_version_number '|| p_object_version_number);
338
339 select object_version_number into l_object_version_number
340 from ota_delegate_bookings where
341 booking_id = l_booking_id;
342
343 update_delegate_booking
344 (p_effective_date => p_effective_date
345 ,p_booking_id => l_booking_id
346 ,p_booking_status_type_id => p_booking_status_type_id
347 ,p_delegate_person_id => p_delegate_person_id
348 ,p_contact_id => p_contact_id
349 ,p_business_group_id => p_business_group_id
350 ,p_event_id => p_event_id
351 ,p_customer_id => p_customer_id
352 ,p_authorizer_person_id => p_authorizer_person_id
353 ,p_date_booking_placed => p_date_booking_placed
354 ,p_corespondent => p_corespondent
355 ,p_internal_booking_flag => p_internal_booking_flag
356 ,p_number_of_places => p_number_of_places
357 ,p_object_version_number => l_object_version_number
358 ,p_administrator => p_administrator
359 ,p_booking_priority => p_booking_priority
360 ,p_comments => p_comments
361 ,p_contact_address_id => p_contact_address_id
362 ,p_delegate_contact_phone => p_delegate_contact_phone
363 ,p_delegate_contact_fax => p_delegate_contact_fax
364 ,p_third_party_customer_id => p_third_party_customer_id
365 ,p_third_party_contact_id => p_third_party_contact_id
366 ,p_third_party_address_id => p_third_party_address_id
367 ,p_third_party_contact_phone => p_third_party_contact_phone
368 ,p_third_party_contact_fax => p_third_party_contact_fax
369 ,p_date_status_changed => p_date_status_changed
370 ,p_status_change_comments => p_status_change_comments
371 ,p_failure_reason => p_failure_reason
372 ,p_attendance_result => p_attendance_result
373 ,p_language_id => p_language_id
374 ,p_source_of_booking => p_source_of_booking
375 ,p_special_booking_instructions => p_special_booking_instructions
376 ,p_successful_attendance_flag => p_successful_attendance_flag
377 ,p_tdb_information_category => p_tdb_information_category
378 ,p_tdb_information1 => p_tdb_information1
379 ,p_tdb_information2 => p_tdb_information2
380 ,p_tdb_information3 => p_tdb_information3
381 ,p_tdb_information4 => p_tdb_information4
382 ,p_tdb_information5 => p_tdb_information5
383 ,p_tdb_information6 => p_tdb_information6
384 ,p_tdb_information7 => p_tdb_information7
385 ,p_tdb_information8 => p_tdb_information8
386 ,p_tdb_information9 => p_tdb_information9
387 ,p_tdb_information10 => p_tdb_information10
388 ,p_tdb_information11 => p_tdb_information11
389 ,p_tdb_information12 => p_tdb_information12
390 ,p_tdb_information13 => p_tdb_information13
391 ,p_tdb_information14 => p_tdb_information14
392 ,p_tdb_information15 => p_tdb_information15
393 ,p_tdb_information16 => p_tdb_information16
394 ,p_tdb_information17 => p_tdb_information17
395 ,p_tdb_information18 => p_tdb_information18
396 ,p_tdb_information19 => p_tdb_information19
397 ,p_tdb_information20 => p_tdb_information20
398 ,p_update_finance_line => p_update_finance_line
399 ,p_tfl_object_version_number => l_tfl_object_version_number
400 ,p_finance_header_id => p_finance_header_id
401 ,p_finance_line_id => p_finance_line_id
402 ,p_standard_amount => p_standard_amount
403 ,p_unitary_amount => p_unitary_amount
404 ,p_money_amount => p_money_amount
405 ,p_currency_code => p_currency_code
406 ,p_booking_deal_type => p_booking_deal_type
407 ,p_booking_deal_id => p_booking_deal_id
408 ,p_enrollment_type => p_enrollment_type
409 ,p_validate => m_validate
410 ,p_organization_id => p_organization_id
411 ,p_sponsor_person_id => p_sponsor_person_id
412 ,p_sponsor_assignment_id => c_sponsor_assignment_id
413 ,p_person_address_id => p_person_address_id
414 ,p_delegate_assignment_id => c_delegate_assignment_id
415 ,p_delegate_contact_id => p_delegate_contact_id
416 ,p_delegate_contact_email => p_delegate_contact_email
417 ,p_third_party_email => p_third_party_email
418 ,p_person_address_type => p_person_address_type
419 ,p_line_id => p_line_id
420 ,p_org_id => p_org_id
421 ,p_daemon_flag => p_daemon_flag
422 ,p_daemon_type => p_daemon_type
423 ,p_old_event_id => p_old_event_id
424 ,p_quote_line_id => p_quote_line_id
425 ,p_interface_source => p_interface_source
426 ,p_total_training_time => p_total_training_time
427 ,p_content_player_status => p_content_player_status
428 ,p_score => p_score
429 ,p_completed_content => p_completed_content
430 ,p_total_content => p_total_content
431 ,p_return_status => p_return_status
432 ,p_booking_justification_id => p_booking_justification_id
433 ,p_is_history_flag => p_is_history_flag
434 ,p_override_prerequisites => p_override_prerequisites
438
435 ,p_override_learner_access => p_override_learner_access
436 );
437 end if;
439
440 exception
441 -- when hr_multi_message.error_message_exist then
442 --
443 -- Catch the Multiple Message List exception which
444 -- indicates API processing has been aborted because
445 -- at least one message exists in the list.
446 --
447 --rollback to enrollment_proc;
448 --
449 -- Reset IN OUT parameters and set OUT parameters
450 --
451 -- p_object_version_number := null;
452 -- p_finance_line_id := l_finance_line_id;
453 -- p_return_status := hr_multi_message.get_return_status_disable;
454 -- hr_utility.set_location(' Leaving:' || l_proc, 30);
455 -- when others then
456 --
457 -- When Multiple Message Detection is enabled catch
458 -- any Application specific or other unexpected
459 -- exceptions. Adding appropriate details to the
460 -- Multiple Message List. Otherwise re-raise the
461 -- error.
462 --
463 -- rollback to enrollment_proc;
464
465
466 when e_upl_not_allowed then
467 hr_utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
468 hr_utility.set_message_token('GENERIC_TOKEN',g_upl_err_msg);
469 hr_utility.set_location('Leaving: ' || l_proc, 90);
470 hr_utility.raise_error;
471 when e_crt_not_allowed then
472 hr_utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
473 hr_utility.set_message_token('GENERIC_TOKEN',g_crt_err_msg);
474 hr_utility.set_location('Leaving: ' || l_proc, 100);
475 hr_utility.raise_error;
476 when others then
477 --l_error_msg := Substr(SQLERRM,1,2000);
478 hr_utility.set_location('SQLCODE :' || SQLCODE,90);
479 hr_utility.set_location('SQLERRM :' || SQLERRM,90);
480 --hr_utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
481 --hr_utility.set_message_token('GENERIC_TOKEN',substr(l_error_msg,1,500) );
482 hr_utility.set_location('Leaving: ' || l_proc, 110);
483 hr_utility.raise_error;
484 --if hr_multi_message.unexpected_error_add(l_proc) then
485 -- hr_utility.set_location(' Leaving:' || l_proc,40);
486 --raise;
487 --end if;
488 --
489 -- Reset IN OUT and set OUT parameters
490 --
491 -- p_object_version_number := null;
492 -- p_finance_line_id := l_finance_line_id;
493 --p_return_status := hr_multi_message.get_return_status_disable;
494 -- hr_utility.set_location(' Leaving:' || l_proc,50);
495
496 end InsUpd_Enroll;
497
498
499 PROCEDURE create_delegate_booking
500 (p_effective_date in date default null
501 ,p_booking_id in number
502 ,p_booking_status_type_id in number
503 ,p_delegate_person_id in number default null
504 ,p_contact_id in number
505 ,p_business_group_id in number
506 ,p_event_id in number
507 ,p_customer_id in number default null
508 ,p_authorizer_person_id in number default null
509 ,p_date_booking_placed in date
510 ,p_corespondent in varchar2 default null
511 ,p_internal_booking_flag in varchar2
512 ,p_number_of_places in number
513 ,p_object_version_number out nocopy number
514 ,p_administrator in number default null
515 ,p_booking_priority in varchar2 default null
516 ,p_comments in varchar2 default null
517 ,p_contact_address_id in number default null
518 ,p_delegate_contact_phone in varchar2 default null
519 ,p_delegate_contact_fax in varchar2 default null
520 ,p_third_party_customer_id in number default null
521 ,p_third_party_contact_id in number default null
522 ,p_third_party_address_id in number default null
523 ,p_third_party_contact_phone in varchar2 default null
524 ,p_third_party_contact_fax in varchar2 default null
525 ,p_date_status_changed in date default null
526 ,p_failure_reason in varchar2 default null
527 ,p_attendance_result in varchar2 default null
528 ,p_language_id in number default null
529 ,p_source_of_booking in varchar2 default null
530 ,p_special_booking_instructions in varchar2 default null
531 ,p_successful_attendance_flag in varchar2 default null
532 ,p_tdb_information_category in varchar2 default null
533 ,p_tdb_information1 in varchar2 default null
534 ,p_tdb_information2 in varchar2 default null
535 ,p_tdb_information3 in varchar2 default null
536 ,p_tdb_information4 in varchar2 default null
537 ,p_tdb_information5 in varchar2 default null
538 ,p_tdb_information6 in varchar2 default null
539 ,p_tdb_information7 in varchar2 default null
540 ,p_tdb_information8 in varchar2 default null
541 ,p_tdb_information9 in varchar2 default null
542 ,p_tdb_information10 in varchar2 default null
543 ,p_tdb_information11 in varchar2 default null
544 ,p_tdb_information12 in varchar2 default null
545 ,p_tdb_information13 in varchar2 default null
546 ,p_tdb_information14 in varchar2 default null
547 ,p_tdb_information15 in varchar2 default null
548 ,p_tdb_information16 in varchar2 default null
549 ,p_tdb_information17 in varchar2 default null
550 ,p_tdb_information18 in varchar2 default null
551 ,p_tdb_information19 in varchar2 default null
555 ,p_currency_code in varchar2 default null
552 ,p_tdb_information20 in varchar2 default null
553 ,p_create_finance_line in varchar2 default null
554 ,p_finance_header_id in number default null
556 ,p_standard_amount in number default null
557 ,p_unitary_amount in number default null
558 ,p_money_amount in number default null
559 ,p_booking_deal_id in number default null
560 ,p_booking_deal_type in varchar2 default null
561 ,p_finance_line_id in out nocopy number
562 ,p_enrollment_type in varchar2 default null
563 ,p_validate in number default hr_api.g_false_num
564 ,p_organization_id in number default null
565 ,p_sponsor_person_id in number default null
566 ,p_sponsor_assignment_id in number default null
567 ,p_person_address_id in number default null
568 ,p_delegate_assignment_id in number default null
569 ,p_delegate_contact_id in number default null
570 ,p_delegate_contact_email in varchar2 default null
571 ,p_third_party_email in varchar2 default null
572 ,p_person_address_type in varchar2 default null
573 ,p_line_id in number default null
574 ,p_org_id in number default null
575 ,p_daemon_flag in varchar2 default null
576 ,p_daemon_type in varchar2 default null
577 ,p_old_event_id in number default null
578 ,p_quote_line_id in number default null
579 ,p_interface_source in varchar2 default null
580 ,p_total_training_time in varchar2 default null
581 ,p_content_player_status in varchar2 default null
582 ,p_score in number default null
583 ,p_completed_content in number default null
584 ,p_total_content in number default null
585 ,p_return_status out nocopy varchar2
586 ,p_booking_justification_id in number default null
587 ,p_is_history_flag in varchar2 default 'N'
588 ,p_override_prerequisites in varchar2 default null
589 ,p_override_learner_access in varchar2
590 ) is
591 --
592 -- Variables for API Boolean parameters
593 l_validate boolean;
594 --
595 -- Variables for IN/OUT parameters
596 l_finance_line_id number;
597 --
598 -- Other variables
599 l_booking_id number;
600 l_proc varchar2(72) := g_package ||'create_delegate_booking';
601 Begin
602 hr_utility.set_location(' Entering:' || l_proc,10);
603 --
604 -- Issue a savepoint
605 --
606 savepoint create_delegate_booking;
607 --
608 -- Initialise Multiple Message Detection
609 --
610 -- hr_multi_message.enable_message_list;
611 --
612 -- Remember IN OUT parameter IN values
613 --
614 l_finance_line_id := p_finance_line_id;
615 --
616 -- Convert constant values to their corresponding boolean value
617 --
618 l_validate :=
619 hr_api.constant_to_boolean
620 (p_constant_value => p_validate);
621 --
622 -- Register Surrogate ID or user key values
623 --
624 ota_tdb_ins.set_base_key_value
625 (p_booking_id => p_booking_id
626 );
627 --
628 -- Call API
629 --
630 ota_delegate_booking_api.create_delegate_booking
631 (p_effective_date => p_effective_date
632 ,p_booking_id => l_booking_id
633 ,p_booking_status_type_id => p_booking_status_type_id
634 ,p_delegate_person_id => p_delegate_person_id
635 ,p_contact_id => p_contact_id
636 ,p_business_group_id => p_business_group_id
637 ,p_event_id => p_event_id
638 ,p_customer_id => p_customer_id
639 ,p_authorizer_person_id => p_authorizer_person_id
640 ,p_date_booking_placed => p_date_booking_placed
641 ,p_corespondent => p_corespondent
642 ,p_internal_booking_flag => p_internal_booking_flag
643 ,p_number_of_places => p_number_of_places
644 ,p_object_version_number => p_object_version_number
645 ,p_administrator => p_administrator
646 ,p_booking_priority => p_booking_priority
647 ,p_comments => p_comments
648 ,p_contact_address_id => p_contact_address_id
649 ,p_delegate_contact_phone => p_delegate_contact_phone
650 ,p_delegate_contact_fax => p_delegate_contact_fax
651 ,p_third_party_customer_id => p_third_party_customer_id
652 ,p_third_party_contact_id => p_third_party_contact_id
653 ,p_third_party_address_id => p_third_party_address_id
654 ,p_third_party_contact_phone => p_third_party_contact_phone
655 ,p_third_party_contact_fax => p_third_party_contact_fax
656 ,p_date_status_changed => p_date_status_changed
657 ,p_failure_reason => p_failure_reason
658 ,p_attendance_result => p_attendance_result
659 ,p_language_id => p_language_id
660 ,p_source_of_booking => p_source_of_booking
661 ,p_special_booking_instructions => p_special_booking_instructions
662 ,p_successful_attendance_flag => p_successful_attendance_flag
663 ,p_tdb_information_category => p_tdb_information_category
664 ,p_tdb_information1 => p_tdb_information1
665 ,p_tdb_information2 => p_tdb_information2
666 ,p_tdb_information3 => p_tdb_information3
670 ,p_tdb_information7 => p_tdb_information7
667 ,p_tdb_information4 => p_tdb_information4
668 ,p_tdb_information5 => p_tdb_information5
669 ,p_tdb_information6 => p_tdb_information6
671 ,p_tdb_information8 => p_tdb_information8
672 ,p_tdb_information9 => p_tdb_information9
673 ,p_tdb_information10 => p_tdb_information10
674 ,p_tdb_information11 => p_tdb_information11
675 ,p_tdb_information12 => p_tdb_information12
676 ,p_tdb_information13 => p_tdb_information13
677 ,p_tdb_information14 => p_tdb_information14
678 ,p_tdb_information15 => p_tdb_information15
679 ,p_tdb_information16 => p_tdb_information16
680 ,p_tdb_information17 => p_tdb_information17
681 ,p_tdb_information18 => p_tdb_information18
682 ,p_tdb_information19 => p_tdb_information19
683 ,p_tdb_information20 => p_tdb_information20
684 ,p_create_finance_line => p_create_finance_line
685 ,p_finance_header_id => p_finance_header_id
686 ,p_currency_code => p_currency_code
687 ,p_standard_amount => p_standard_amount
688 ,p_unitary_amount => p_unitary_amount
689 ,p_money_amount => p_money_amount
690 ,p_booking_deal_id => p_booking_deal_id
691 ,p_booking_deal_type => p_booking_deal_type
692 ,p_finance_line_id => p_finance_line_id
693 ,p_enrollment_type => p_enrollment_type
694 ,p_validate => l_validate
695 ,p_organization_id => p_organization_id
696 ,p_sponsor_person_id => p_sponsor_person_id
697 ,p_sponsor_assignment_id => p_sponsor_assignment_id
698 ,p_person_address_id => p_person_address_id
699 ,p_delegate_assignment_id => p_delegate_assignment_id
700 ,p_delegate_contact_id => p_delegate_contact_id
701 ,p_delegate_contact_email => p_delegate_contact_email
702 ,p_third_party_email => p_third_party_email
703 ,p_person_address_type => p_person_address_type
704 ,p_line_id => p_line_id
705 ,p_org_id => p_org_id
706 ,p_daemon_flag => p_daemon_flag
707 ,p_daemon_type => p_daemon_type
708 ,p_old_event_id => p_old_event_id
709 ,p_quote_line_id => p_quote_line_id
710 ,p_interface_source => p_interface_source
711 ,p_total_training_time => p_total_training_time
712 ,p_content_player_status => p_content_player_status
713 ,p_score => p_score
714 ,p_completed_content => p_completed_content
715 ,p_total_content => p_total_content
716 ,p_booking_justification_id => p_booking_justification_id
717 ,p_is_history_flag => p_is_history_flag
718 ,p_override_prerequisites => p_override_prerequisites
719 ,p_override_learner_access => p_override_learner_access
720 );
721 --
722 -- Convert API warning boolean parameter values to specific
723 -- messages and add them to Multiple Message List
724 --
725 --
726 -- Convert API non-warning boolean parameter values
727 --
728 --
729 -- Derive the API return status value based on whether
730 -- messages of any type exist in the Multiple Message List.
731 -- Also disable Multiple Message Detection.
732 --
733 p_return_status := hr_multi_message.get_return_status_disable;
734 hr_utility.set_location(' Leaving:' || l_proc,20);
735 --
736 exception
737 when hr_multi_message.error_message_exist then
738 --
739 -- Catch the Multiple Message List exception which
740 -- indicates API processing has been aborted because
741 -- at least one message exists in the list.
742 --
743 rollback to create_delegate_booking;
744 --
745 -- Reset IN OUT parameters and set OUT parameters
746 --
747 p_object_version_number := null;
748 p_finance_line_id := l_finance_line_id;
749 p_return_status := hr_multi_message.get_return_status_disable;
750 hr_utility.set_location(' Leaving:' || l_proc, 30);
751 when others then
752 --
753 -- When Multiple Message Detection is enabled catch
754 -- any Application specific or other unexpected
755 -- exceptions. Adding appropriate details to the
756 -- Multiple Message List. Otherwise re-raise the
757 -- error.
758 --
759 rollback to create_delegate_booking;
760 -- if hr_multi_message.unexpected_error_add(l_proc) then
761 hr_utility.set_location(' Leaving:' || l_proc,40);
762 raise;
763 -- end if;
764 --
765 -- Reset IN OUT and set OUT parameters
766 --
767 p_object_version_number := null;
768 p_finance_line_id := l_finance_line_id;
769 p_return_status := hr_multi_message.get_return_status_disable;
770 hr_utility.set_location(' Leaving:' || l_proc,50);
771 end create_delegate_booking;
772
773
774 PROCEDURE update_delegate_booking
775 (p_effective_date in date default null
776 ,p_booking_id in number
777 ,p_booking_status_type_id in number default hr_api.g_number
778 ,p_delegate_person_id in number default hr_api.g_number
779 ,p_contact_id in number default hr_api.g_number
780 ,p_business_group_id in number default hr_api.g_number
781 ,p_event_id in number default hr_api.g_number
782 ,p_customer_id in number default hr_api.g_number
786 ,p_internal_booking_flag in varchar2 default hr_api.g_varchar2
783 ,p_authorizer_person_id in number default hr_api.g_number
784 ,p_date_booking_placed in date default hr_api.g_date
785 ,p_corespondent in varchar2 default hr_api.g_varchar2
787 ,p_number_of_places in number default hr_api.g_number
788 ,p_object_version_number in out nocopy number
789 ,p_administrator in number default hr_api.g_number
790 ,p_booking_priority in varchar2 default hr_api.g_varchar2
791 ,p_comments in varchar2 default hr_api.g_varchar2
792 ,p_contact_address_id in number default hr_api.g_number
793 ,p_delegate_contact_phone in varchar2 default hr_api.g_varchar2
794 ,p_delegate_contact_fax in varchar2 default hr_api.g_varchar2
795 ,p_third_party_customer_id in number default hr_api.g_number
796 ,p_third_party_contact_id in number default hr_api.g_number
797 ,p_third_party_address_id in number default hr_api.g_number
798 ,p_third_party_contact_phone in varchar2 default hr_api.g_varchar2
799 ,p_third_party_contact_fax in varchar2 default hr_api.g_varchar2
800 ,p_date_status_changed in date default hr_api.g_date
801 ,p_status_change_comments in varchar2 default hr_api.g_varchar2
802 ,p_failure_reason in varchar2 default hr_api.g_varchar2
803 ,p_attendance_result in varchar2 default hr_api.g_varchar2
804 ,p_language_id in number default hr_api.g_number
805 ,p_source_of_booking in varchar2 default hr_api.g_varchar2
806 ,p_special_booking_instructions in varchar2 default hr_api.g_varchar2
807 ,p_successful_attendance_flag in varchar2 default hr_api.g_varchar2
808 ,p_tdb_information_category in varchar2 default hr_api.g_varchar2
809 ,p_tdb_information1 in varchar2 default hr_api.g_varchar2
810 ,p_tdb_information2 in varchar2 default hr_api.g_varchar2
811 ,p_tdb_information3 in varchar2 default hr_api.g_varchar2
812 ,p_tdb_information4 in varchar2 default hr_api.g_varchar2
813 ,p_tdb_information5 in varchar2 default hr_api.g_varchar2
814 ,p_tdb_information6 in varchar2 default hr_api.g_varchar2
815 ,p_tdb_information7 in varchar2 default hr_api.g_varchar2
816 ,p_tdb_information8 in varchar2 default hr_api.g_varchar2
817 ,p_tdb_information9 in varchar2 default hr_api.g_varchar2
818 ,p_tdb_information10 in varchar2 default hr_api.g_varchar2
819 ,p_tdb_information11 in varchar2 default hr_api.g_varchar2
820 ,p_tdb_information12 in varchar2 default hr_api.g_varchar2
821 ,p_tdb_information13 in varchar2 default hr_api.g_varchar2
822 ,p_tdb_information14 in varchar2 default hr_api.g_varchar2
823 ,p_tdb_information15 in varchar2 default hr_api.g_varchar2
824 ,p_tdb_information16 in varchar2 default hr_api.g_varchar2
825 ,p_tdb_information17 in varchar2 default hr_api.g_varchar2
826 ,p_tdb_information18 in varchar2 default hr_api.g_varchar2
827 ,p_tdb_information19 in varchar2 default hr_api.g_varchar2
828 ,p_tdb_information20 in varchar2 default hr_api.g_varchar2
829 ,p_update_finance_line in varchar2 default hr_api.g_varchar2
830 ,p_tfl_object_version_number in out nocopy number
831 ,p_finance_header_id in number default hr_api.g_number
832 ,p_finance_line_id in out nocopy number
833 ,p_standard_amount in number default hr_api.g_number
834 ,p_unitary_amount in number default hr_api.g_number
835 ,p_money_amount in number default hr_api.g_number
836 ,p_currency_code in varchar2 default hr_api.g_varchar2
837 ,p_booking_deal_type in varchar2 default hr_api.g_varchar2
838 ,p_booking_deal_id in number default hr_api.g_number
839 ,p_enrollment_type in varchar2 default hr_api.g_varchar2
840 ,p_validate in number default hr_api.g_false_num
841 ,p_organization_id in number default hr_api.g_number
842 ,p_sponsor_person_id in number default hr_api.g_number
843 ,p_sponsor_assignment_id in number default hr_api.g_number
844 ,p_person_address_id in number default hr_api.g_number
845 ,p_delegate_assignment_id in number default hr_api.g_number
846 ,p_delegate_contact_id in number default hr_api.g_number
847 ,p_delegate_contact_email in varchar2 default hr_api.g_varchar2
848 ,p_third_party_email in varchar2 default hr_api.g_varchar2
849 ,p_person_address_type in varchar2 default hr_api.g_varchar2
850 ,p_line_id in number default hr_api.g_number
851 ,p_org_id in number default hr_api.g_number
852 ,p_daemon_flag in varchar2 default hr_api.g_varchar2
853 ,p_daemon_type in varchar2 default hr_api.g_varchar2
854 ,p_old_event_id in number default hr_api.g_number
855 ,p_quote_line_id in number default hr_api.g_number
856 ,p_interface_source in varchar2 default hr_api.g_varchar2
857 ,p_total_training_time in varchar2 default hr_api.g_varchar2
858 ,p_content_player_status in varchar2 default hr_api.g_varchar2
859 ,p_score in number default hr_api.g_number
860 ,p_completed_content in number default hr_api.g_number
861 ,p_total_content in number default hr_api.g_number
862 ,p_return_status out nocopy varchar2
866 ,p_override_learner_access in varchar2
863 ,p_booking_justification_id in number default hr_api.g_number
864 ,p_is_history_flag in varchar2 default hr_api.g_varchar2
865 ,p_override_prerequisites in varchar2
867 ) is
868 --
869 -- Variables for API Boolean parameters
870 l_validate boolean;
871 --
872 -- Variables for IN/OUT parameters
873 l_object_version_number number;
874 l_tfl_object_version_number number;
875 l_finance_line_id number;
876 --
877 l_date_booking_placed ota_delegate_bookings.date_booking_placed%TYPE;
878 l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
879
880
881
882 CURSOR booking_csr
883 IS
884 SELECT b.date_booking_placed
885 FROM ota_delegate_bookings b
886 WHERE b.booking_id = p_booking_id;
887 --
888
889 -- Other variables
890 l_proc varchar2(72) := g_package ||'update_delegate_booking';
891 Begin
892 hr_utility.set_location(' Entering:' || l_proc,10);
893 --
894 -- Issue a savepoint
895 --
896 savepoint update_delegate_booking;
897 --
898 -- Initialise Multiple Message Detection
899 --
900 -- hr_multi_message.enable_message_list;
901 --
902 -- Remember IN OUT parameter IN values
903 --
904 l_object_version_number := p_object_version_number;
905 l_tfl_object_version_number := p_tfl_object_version_number;
906 l_finance_line_id := p_finance_line_id;
907 --
908 --
909 hr_utility.trace('SRK in update_deleg_booki booking p_booking_id '|| p_booking_id);
910 OPEN booking_csr;
911 FETCH booking_csr INTO l_date_booking_placed;
912 CLOSE booking_csr;
913 l_date_status_changed := trunc(sysdate);
914 --
915 -- Convert constant values to their corresponding boolean value
916 --
917 l_validate :=
918 hr_api.constant_to_boolean
919 (p_constant_value => p_validate);
920 --
921 -- Register Surrogate ID or user key values
922 --
923 --
924 -- Call API
925 --
926 hr_utility.trace('SRK in update_deleg_booki before the api call p_booking_id '|| p_booking_id);
927 ota_delegate_booking_api.update_delegate_booking
928 (p_effective_date => p_effective_date
929 ,p_booking_id => p_booking_id
930 ,p_booking_status_type_id => p_booking_status_type_id
931 ,p_delegate_person_id => p_delegate_person_id
932 ,p_contact_id => p_contact_id
933 ,p_business_group_id => p_business_group_id
934 ,p_event_id => p_event_id
935 ,p_customer_id => p_customer_id
936 ,p_authorizer_person_id => p_authorizer_person_id
937 ,p_date_booking_placed => l_date_booking_placed
938 ,p_corespondent => p_corespondent
939 ,p_internal_booking_flag => p_internal_booking_flag
940 ,p_number_of_places => p_number_of_places
941 ,p_object_version_number => p_object_version_number
942 ,p_administrator => p_administrator
943 ,p_booking_priority => p_booking_priority
944 ,p_comments => p_comments
945 ,p_contact_address_id => p_contact_address_id
946 ,p_delegate_contact_phone => p_delegate_contact_phone
947 ,p_delegate_contact_fax => p_delegate_contact_fax
948 ,p_third_party_customer_id => p_third_party_customer_id
949 ,p_third_party_contact_id => p_third_party_contact_id
950 ,p_third_party_address_id => p_third_party_address_id
951 ,p_third_party_contact_phone => p_third_party_contact_phone
952 ,p_third_party_contact_fax => p_third_party_contact_fax
953 ,p_date_status_changed => l_date_status_changed
954 ,p_status_change_comments => p_status_change_comments
955 ,p_failure_reason => p_failure_reason
956 ,p_attendance_result => p_attendance_result
957 ,p_language_id => p_language_id
958 ,p_source_of_booking => p_source_of_booking
959 ,p_special_booking_instructions => p_special_booking_instructions
960 ,p_successful_attendance_flag => p_successful_attendance_flag
961 ,p_tdb_information_category => p_tdb_information_category
962 ,p_tdb_information1 => p_tdb_information1
963 ,p_tdb_information2 => p_tdb_information2
964 ,p_tdb_information3 => p_tdb_information3
965 ,p_tdb_information4 => p_tdb_information4
966 ,p_tdb_information5 => p_tdb_information5
967 ,p_tdb_information6 => p_tdb_information6
968 ,p_tdb_information7 => p_tdb_information7
969 ,p_tdb_information8 => p_tdb_information8
970 ,p_tdb_information9 => p_tdb_information9
971 ,p_tdb_information10 => p_tdb_information10
972 ,p_tdb_information11 => p_tdb_information11
973 ,p_tdb_information12 => p_tdb_information12
974 ,p_tdb_information13 => p_tdb_information13
975 ,p_tdb_information14 => p_tdb_information14
976 ,p_tdb_information15 => p_tdb_information15
977 ,p_tdb_information16 => p_tdb_information16
978 ,p_tdb_information17 => p_tdb_information17
979 ,p_tdb_information18 => p_tdb_information18
980 ,p_tdb_information19 => p_tdb_information19
981 ,p_tdb_information20 => p_tdb_information20
982 ,p_update_finance_line => p_update_finance_line
983 ,p_tfl_object_version_number => l_tfl_object_version_number
984 ,p_finance_header_id => p_finance_header_id
985 ,p_finance_line_id => p_finance_line_id
986 ,p_standard_amount => p_standard_amount
987 ,p_unitary_amount => p_unitary_amount
988 ,p_money_amount => p_money_amount
989 ,p_currency_code => p_currency_code
990 ,p_booking_deal_type => p_booking_deal_type
991 ,p_booking_deal_id => p_booking_deal_id
992 ,p_enrollment_type => p_enrollment_type
993 ,p_validate => l_validate
994 ,p_organization_id => p_organization_id
995 ,p_sponsor_person_id => p_sponsor_person_id
996 ,p_sponsor_assignment_id => p_sponsor_assignment_id
997 ,p_person_address_id => p_person_address_id
998 ,p_delegate_assignment_id => p_delegate_assignment_id
999 ,p_delegate_contact_id => p_delegate_contact_id
1000 ,p_delegate_contact_email => p_delegate_contact_email
1001 ,p_third_party_email => p_third_party_email
1002 ,p_person_address_type => p_person_address_type
1003 ,p_line_id => p_line_id
1004 ,p_org_id => p_org_id
1005 ,p_daemon_flag => p_daemon_flag
1006 ,p_daemon_type => p_daemon_type
1007 ,p_old_event_id => p_old_event_id
1008 ,p_quote_line_id => p_quote_line_id
1009 ,p_interface_source => p_interface_source
1010 ,p_total_training_time => p_total_training_time
1011 ,p_content_player_status => p_content_player_status
1012 ,p_score => p_score
1013 ,p_completed_content => p_completed_content
1014 ,p_total_content => p_total_content
1015 ,p_booking_justification_id => p_booking_justification_id
1016 ,p_is_history_flag => p_is_history_flag
1017 ,p_override_prerequisites => p_override_prerequisites
1018 ,p_override_learner_access => p_override_learner_access
1019 );
1020 --
1021 -- Convert API warning boolean parameter values to specific
1022 -- messages and add them to Multiple Message List
1023 --
1024 --
1025 -- Convert API non-warning boolean parameter values
1026 --
1027 --
1028 -- Derive the API return status value based on whether
1029 -- messages of any type exist in the Multiple Message List.
1030 -- Also disable Multiple Message Detection.
1031 --
1032 p_return_status := hr_multi_message.get_return_status_disable;
1033 hr_utility.set_location(' Leaving:' || l_proc,20);
1034 --
1035 end update_delegate_booking;
1036
1037
1038
1039
1040 end pqp_riw_enroll_wrapper;