[Home] [Help]
PACKAGE BODY: APPS.OTA_LEARNER_ENROLL_REVIEW_SS
Source
1 PACKAGE BODY OTA_LEARNER_ENROLL_REVIEW_SS AS
2 /* $Header: otlnrrev.pkb 120.19 2011/10/11 07:15:53 jaysridh ship $*/
3
4 g_package varchar2(30) := 'OTA_LEARNER_ENROLL_REVIEW_SS';
5
6 -- ---------------------------------------------------------------------------
7 -- |----------------------< get_approval_req >--------------------------|
8 -- ---------------------------------------------------------------------------
9 --
10 PROCEDURE get_approval_req ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
11 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
12 actid IN NUMBER,
13 funcmode IN VARCHAR2,
14 resultout OUT nocopy VARCHAR2 )
15 IS
16
17 l_item_value varchar2(200);
18 l_ntf_url varchar2(4000);
19
20
21
22 BEGIN
23 hr_utility.set_location('ENTERING get_approval_req', 10);
24 IF (funcmode='RUN') THEN
25
26
27
28
29 l_item_value := wf_engine.getItemAttrText(itemtype => itemtype
30 ,itemkey => itemkey
31 , aname => 'HR_RUNTIME_APPROVAL_REQ_FLAG');
32
33
34
35 if l_item_value = 'NO' then
36
37 resultout:='COMPLETE:N';
38
39
40 else
41
42 resultout:='COMPLETE:Y';
43
44
45 end if;
46 hr_utility.trace('l_resultout' || resultout);
47
48 RETURN;
49 END IF; --RUN
50
51 IF (funcmode='CANCEL') THEN
52 resultout:='COMPLETE';
53 RETURN;
54 END IF;
55 Exception
56
57 when others then
58 hr_utility.set_location('ENTERING exception get_approval_req', 10);
59
60
61
62 end get_approval_req;
63
64
65 procedure save_add_enroll_detail(
66 p_login_person_id NUMBER default null
67 , p_item_type in varchar2
68 , p_item_key in varchar2
69 , p_activity_id in number
70 , p_save_mode in varchar2 default null
71 , p_error_message out nocopy varchar2
72 , p_eventid in VARCHAR2
73 , p_activityversionid in VARCHAR2
74 , p_specialInstruction in VARCHAR2
75 , p_keyflexId in VARCHAR2
76 , p_businessGroupId in VARCHAR2
77 , p_assignmentId in VARCHAR2
78 , p_organizationId in VARCHAR2
79 , p_from in VARCHAR2
80 , p_tdb_information_category in varchar2 default null
81 , p_tdb_information1 in varchar2 default null
82 , p_tdb_information2 in varchar2 default null
83 , p_tdb_information3 in varchar2 default null
84 , p_tdb_information4 in varchar2 default null
85 , p_tdb_information5 in varchar2 default null
86 , p_tdb_information6 in varchar2 default null
87 , p_tdb_information7 in varchar2 default null
88 , p_tdb_information8 in varchar2 default null
89 , p_tdb_information9 in varchar2 default null
90 , p_tdb_information10 in varchar2 default null
91 , p_tdb_information11 in varchar2 default null
92 , p_tdb_information12 in varchar2 default null
93 , p_tdb_information13 in varchar2 default null
94 , p_tdb_information14 in varchar2 default null
95 , p_tdb_information15 in varchar2 default null
96 , p_tdb_information16 in varchar2 default null
97 , p_tdb_information17 in varchar2 default null
98 , p_tdb_information18 in varchar2 default null
99 , p_tdb_information19 in varchar2 default null
100 , p_tdb_information20 in varchar2 default null
101 , p_delegate_person_id in NUMBER default null
102 , p_ccselectiontext in varchar2 default null
103 , p_offering_id in VARCHAR2
104 ,p_booking_justification_id IN VARCHAR2 default null
105 )
106 as
107
108 l_transaction_id number default null;
109 l_transaction_step_id number default null;
110 l_trans_obj_vers_num number default null;
111 l_count integer default 0;
112 l_transaction_table hr_transaction_ss.transaction_table;
113 l_review_item_name varchar2(50);
114 l_message_number VARCHAR2(10);
115 l_result varchar2(100) default null;
116 l_old_transaction_step_id number;
117 l_old_object_version_number number;
118
119 begin
120
121 -- First, check if transaction id exists or not
122 l_transaction_id := hr_transaction_ss.get_transaction_id
123 (p_item_type => p_item_type
124 ,p_item_key => p_item_key);
125
126 --
127 IF l_transaction_id is null THEN
128 -- Start a Transaction
129 hr_transaction_ss.start_transaction
130 (itemtype => p_item_type
131 ,itemkey => p_item_key
132 ,actid => p_activity_id
133 ,funmode => 'RUN'
134 ,p_login_person_id => p_login_person_id
135 ,result => l_result);
136
137 l_transaction_id := hr_transaction_ss.get_transaction_id
138 (p_item_type => p_item_type
139 ,p_item_key => p_item_key);
140 END IF;
141
142 --
143 -- Delete transaction step if exist
144 --
145
146 if (hr_transaction_api.transaction_step_exist (p_item_type => p_item_type
147 ,p_item_key => p_item_key
148 ,p_activity_id => p_activity_id) ) then
149
150 hr_transaction_api.get_transaction_step_info(p_item_type => p_item_type
151 ,p_item_key => p_item_key
152 ,p_activity_id => p_activity_id
153 ,p_transaction_step_id => l_old_transaction_step_id
154 ,p_object_version_number => l_old_object_version_number);
155
156
157 hr_transaction_api.delete_transaction_step(p_validate => false
158 ,p_transaction_step_id => l_old_transaction_step_id
159 ,p_person_id => p_login_person_id
160 ,p_object_version_number => l_old_object_version_number);
161
162 end if;
163
164 --
165 -- Create a transaction step
166 --
167 hr_transaction_api.create_transaction_step
168 (p_validate => false
169 ,p_creator_person_id => p_login_person_id
170 ,p_transaction_id => l_transaction_id
171 ,p_api_name => g_package || '.PROCESS_API2'
172 ,p_item_type => p_item_type
173 ,p_item_key => p_item_key
174 ,p_activity_id => p_activity_id
175 ,p_transaction_step_id => l_transaction_step_id
176 ,p_object_version_number => l_trans_obj_vers_num);
177
178
179 l_count := 1;
180 l_transaction_table(l_count).param_name := 'P_EVENTID';
181 l_transaction_table(l_count).param_value := p_eventid;
182 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
183
184 l_count := l_count + 1;
185 l_transaction_table(l_count).param_name := 'P_ACTIVITYVERSIONID';
186 l_transaction_table(l_count).param_value := p_activityversionid;
187 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
188
189
190 l_count := l_count + 1;
191 l_transaction_table(l_count).param_name := 'P_SPECIALINSTRUCTION';
192 l_transaction_table(l_count).param_value := p_specialInstruction;
193 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
194
195 l_count := l_count + 1;
196 l_transaction_table(l_count).param_name := 'P_REVIEW_ACTID';
197 l_transaction_table(l_count).param_value := p_activity_id;
198 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
199
200 l_count := l_count + 1;
201 l_transaction_table(l_count).param_name := 'P_KEYFLEXID';
202 l_transaction_table(l_count).param_value := p_keyflexId;
203 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
204
205
206 l_count := l_count + 1;
207 l_transaction_table(l_count).param_name := 'P_BUSINESSGROUPID';
208 l_transaction_table(l_count).param_value := p_businessGroupId;
209 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
210
211
212 l_count := l_count + 1;
213 l_transaction_table(l_count).param_name := 'P_ASSIGNMENTID';
214 l_transaction_table(l_count).param_value := p_assignmentId;
215 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
216
217
218 l_count := l_count + 1;
219 l_transaction_table(l_count).param_name := 'P_ORGANIZATIONID';
220 l_transaction_table(l_count).param_value := p_organizationId;
221 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
222
223
224 l_count := l_count + 1;
225 l_transaction_table(l_count).param_name := 'P_PERSON_ID';
226 l_transaction_table(l_count).param_value := p_login_person_id;
227 l_transaction_table(l_count).param_data_type := 'NUMBER';
228
229 l_count := l_count + 1;
230 l_transaction_table(l_count).param_name := 'P_FROM';
231 l_transaction_table(l_count).param_value := p_from;
232 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
233
234
235 l_count := l_count + 1;
236 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION_CATEGORY';
237 l_transaction_table(l_count).param_value := p_tdb_information_category;
238 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
239
240
241 l_count := l_count + 1;
242 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION1';
243 l_transaction_table(l_count).param_value := p_tdb_information1;
244 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
245
246 l_count := l_count + 1;
247 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION2';
248 l_transaction_table(l_count).param_value := p_tdb_information2;
249 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
250
251 l_count := l_count + 1;
252 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION3';
253 l_transaction_table(l_count).param_value := p_tdb_information3;
254 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
255
256 l_count := l_count + 1;
257 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION4';
258 l_transaction_table(l_count).param_value := p_tdb_information4;
259 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
260
261 l_count := l_count + 1;
262 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION5';
263 l_transaction_table(l_count).param_value := p_tdb_information5;
264 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
265
266 l_count := l_count + 1;
267 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION6';
268 l_transaction_table(l_count).param_value := p_tdb_information6;
269 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
270
271 l_count := l_count + 1;
272 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION7';
273 l_transaction_table(l_count).param_value := p_tdb_information7;
274 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
275
276 l_count := l_count + 1;
277 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION8';
278 l_transaction_table(l_count).param_value := p_tdb_information8;
279 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
280
281 l_count := l_count + 1;
282 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION9';
283 l_transaction_table(l_count).param_value := p_tdb_information9;
284 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
285
286 l_count := l_count + 1;
287 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION10';
288 l_transaction_table(l_count).param_value := p_tdb_information10;
289 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
290
291 l_count := l_count + 1;
292 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION11';
293 l_transaction_table(l_count).param_value := p_tdb_information11;
294 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
295
296 l_count := l_count + 1;
297 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION12';
298 l_transaction_table(l_count).param_value := p_tdb_information12;
299 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
300
301 l_count := l_count + 1;
302 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION13';
303 l_transaction_table(l_count).param_value := p_tdb_information13;
304 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
305
306 l_count := l_count + 1;
307 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION14';
308 l_transaction_table(l_count).param_value := p_tdb_information14;
309 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
310
311 l_count := l_count + 1;
312 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION15';
313 l_transaction_table(l_count).param_value := p_tdb_information15;
314 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
315
316 l_count := l_count + 1;
317 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION16';
318 l_transaction_table(l_count).param_value := p_tdb_information16;
319 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
320
321 l_count := l_count + 1;
322 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION17';
323 l_transaction_table(l_count).param_value := p_tdb_information17;
324 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
325
326 l_count := l_count + 1;
327 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION18';
328 l_transaction_table(l_count).param_value := p_tdb_information18;
329 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
330
331 l_count := l_count + 1;
332 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION19';
333 l_transaction_table(l_count).param_value := p_tdb_information19;
334 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
335
336 l_count := l_count + 1;
337 l_transaction_table(l_count).param_name := 'P_TDB_INFORMATION20';
338 l_transaction_table(l_count).param_value := p_tdb_information20;
339 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
340
341 l_count := l_count + 1;
342 l_transaction_table(l_count).param_name := 'P_DELEGATE_PERSON_ID';
343 l_transaction_table(l_count).param_value := p_delegate_person_id;
344 l_transaction_table(l_count).param_data_type := 'NUMBER';
345
346 l_count := l_count + 1;
347 l_transaction_table(l_count).param_name := 'P_CCSELECTIONTEXT';
348 l_transaction_table(l_count).param_value := p_ccselectiontext;
349 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
350
351 l_count := l_count + 1;
352 l_transaction_table(l_count).param_name := 'P_OFFERING_ID';
353 l_transaction_table(l_count).param_value := p_offering_id;
354 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
355
356 l_count := l_count + 1;
357 l_transaction_table(l_count).param_name := 'P_BKNG_JUSTIFICATION_ID';
358 l_transaction_table(l_count).param_value := p_booking_justification_id;
359 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
360
361
362 hr_approval_wf.create_item_attrib_if_notexist
363 (p_item_type => p_item_type
364 ,p_item_key => p_item_key
365 ,p_name => 'OTA_TRANSACTION_STEP_ID');
366
367 WF_ENGINE.setitemattrnumber(p_item_type,
368 p_item_key,
369 'OTA_TRANSACTION_STEP_ID',
370 l_transaction_step_id);
371
372
373 If p_from='REVIEW' Then
374 hr_approval_wf.create_item_attrib_if_notexist
375 (p_item_type => p_item_type
376 ,p_item_key => p_item_key
377 ,p_name => 'OTA_EVENT_ID');
378
379 WF_ENGINE.setitemattrnumber(p_item_type,
380 p_item_key,
381 'OTA_EVENT_ID',
382 p_eventid);
383
384 -- bug 4146681
385 WF_ENGINE.setitemattrtext(p_item_type,
386 p_item_key,
387 'HR_RESTRICT_EDIT_ATTR',
388 'Y');
389 --bug 4146681
390
391 End If;
392
393 hr_transaction_ss.save_transaction_step
394 (p_item_type => p_item_type
395 ,p_item_key => p_item_key
396 ,p_actid => p_activity_id
397 ,p_login_person_id => p_login_person_id
398 ,p_transaction_step_id => l_transaction_step_id
399 ,p_api_name => g_package || '.PROCESS_API2'
400 ,p_transaction_data => l_transaction_table);
401
402
403 EXCEPTION
404 WHEN hr_utility.hr_error THEN
405 -- -------------------------------------------
406 -- an application error has been raised so we must
407 -- redisplay the web form to display the error
408 -- --------------------------------------------
409 hr_message.provide_error;
410 l_message_number := hr_message.last_message_number;
411 IF l_message_number = 'APP-7165' OR
412 l_message_number = 'APP-7155' THEN
413 --populate the p_error_message out variable
414 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
415 p_error_message => p_error_message,
416 p_attr_name => 'Page',
417 p_app_short_name => 'PER',
418 p_message_name => 'HR_UPDATE_NOT_ALLOWED');
419 ELSE
420 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
421 p_error_message => p_error_message);
422 END IF;
423 WHEN OTHERS THEN
424 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
425 p_error_message => p_error_message);
426
427 end save_add_enroll_detail;
428
429
430
431
432 PROCEDURE get_add_enr_dtl_data_from_tt
433 (p_item_type in varchar2
434 ,p_item_key in varchar2
435 ,p_activity_id in varchar2
436 ,p_trans_rec_count out nocopy number
437 ,p_person_id out nocopy number
438 ,p_add_enroll_detail_data out nocopy varchar2
439 ) is
440
441
442 l_trans_rec_count integer default 0;
443 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
444 l_trans_obj_vers_nums hr_util_web.g_varchar2_tab_type;
445 ln_index number default 0;
446 l_trans_step_rows NUMBER ;
447 l_add_enroll_detail_data varchar2(4000);
448 l_trans_step_id number;
449
450
451 BEGIN
452
453 hr_transaction_api.get_transaction_step_info
454 (p_item_type => p_item_type
455 ,p_item_key => p_item_key
456 ,p_activity_id => p_activity_id
457 ,p_transaction_step_id => l_trans_step_ids
458 ,p_object_version_number => l_trans_obj_vers_nums
459 ,p_rows => l_trans_step_rows);
460
461
462 get_add_enr_dtl_data_from_tt(
463 p_transaction_step_id => l_trans_step_ids(ln_index)
464 -- p_transaction_step_id => l_trans_step_id
465 ,p_add_enroll_detail_data => l_add_enroll_detail_data);
466
467
468 p_add_enroll_detail_data := l_add_enroll_detail_data;
469
470
471 EXCEPTION
472 WHEN OTHERS THEN
473 RAISE;
474
475 END get_add_enr_dtl_data_from_tt;
476
477
478 procedure get_add_enr_dtl_data_from_tt
479 (p_transaction_step_id in number
480 ,p_add_enroll_detail_data out nocopy varchar2
481 )
482 is
483
484 l_eventid ota_events.event_id%TYPE;
485 l_activityversionid ota_events.ACTIVITY_VERSION_ID%TYPE;
486 -- l_costAlocationKeyflexId pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE;
487 l_specialInstruction ota_delegate_bookings.SPECIAL_BOOKING_INSTRUCTIONS%TYPE;
488 l_keyflexid pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE;
489 l_businessgroupid ota_delegate_bookings.BUSINESS_GROUP_ID%TYPE;
490 l_assignmentid per_all_assignments.ASSIGNMENT_ID%TYPE;
491 l_organizationid ota_delegate_bookings.ORGANIZATION_ID%TYPE;
492 l_tdb_information_category ota_delegate_bookings.TDB_INFORMATION_CATEGORY%TYPE;
493 l_tdb_information1 ota_delegate_bookings.TDB_INFORMATION1%TYPE;
494 l_tdb_information2 ota_delegate_bookings.TDB_INFORMATION2%TYPE;
495 l_tdb_information3 ota_delegate_bookings.TDB_INFORMATION3%TYPE;
496 l_tdb_information4 ota_delegate_bookings.TDB_INFORMATION4%TYPE;
497 l_tdb_information5 ota_delegate_bookings.TDB_INFORMATION5%TYPE;
498 l_tdb_information6 ota_delegate_bookings.TDB_INFORMATION6%TYPE;
499 l_tdb_information7 ota_delegate_bookings.TDB_INFORMATION7%TYPE;
500 l_tdb_information8 ota_delegate_bookings.TDB_INFORMATION8%TYPE;
501 l_tdb_information9 ota_delegate_bookings.TDB_INFORMATION9%TYPE;
502 l_tdb_information10 ota_delegate_bookings.TDB_INFORMATION10%TYPE;
503 l_tdb_information11 ota_delegate_bookings.TDB_INFORMATION11%TYPE;
504 l_tdb_information12 ota_delegate_bookings.TDB_INFORMATION12%TYPE;
505 l_tdb_information13 ota_delegate_bookings.TDB_INFORMATION13%TYPE;
506 l_tdb_information14 ota_delegate_bookings.TDB_INFORMATION14%TYPE;
507 l_tdb_information15 ota_delegate_bookings.TDB_INFORMATION15%TYPE;
508 l_tdb_information16 ota_delegate_bookings.TDB_INFORMATION16%TYPE;
509 l_tdb_information17 ota_delegate_bookings.TDB_INFORMATION17%TYPE;
510 l_tdb_information18 ota_delegate_bookings.TDB_INFORMATION18%TYPE;
511 l_tdb_information19 ota_delegate_bookings.TDB_INFORMATION19%TYPE;
512 l_tdb_information20 ota_delegate_bookings.TDB_INFORMATION20%TYPE;
513 l_offering_id ota_offerings.offering_id%TYPE;
514 l_booking_justification_id ota_bkng_justifications_b.booking_justification_id%TYPE;
515 l_booking_justification ota_bkng_justifications_tl.justification_text%TYPE;
516 begin
517
518
519 l_eventid := hr_transaction_api.get_varchar2_value
520 (p_transaction_step_id => p_transaction_step_id
521 ,p_name => 'P_EVENTID');
522
523 l_activityversionid := hr_transaction_api.get_varchar2_value
524 (p_transaction_step_id => p_transaction_step_id
525 ,p_name => 'P_ACTIVITYVERSIONID');
526
527 l_specialInstruction := hr_transaction_api.get_varchar2_value
528 (p_transaction_step_id => p_transaction_step_id
529 ,p_name => 'P_SPECIALINSTRUCTION');
530
531 l_keyflexid := hr_transaction_api.get_varchar2_value
532 (p_transaction_step_id => p_transaction_step_id
533 ,p_name => 'P_KEYFLEXID');
534
535 l_businessgroupid := hr_transaction_api.get_varchar2_value
536 (p_transaction_step_id => p_transaction_step_id
537 ,p_name => 'P_BUSINESSGROUPID');
538
539 l_assignmentid := hr_transaction_api.get_varchar2_value
540 (p_transaction_step_id => p_transaction_step_id
541 ,p_name => 'P_ASSIGNMENTID');
542
543 l_organizationid := hr_transaction_api.get_varchar2_value
544 (p_transaction_step_id => p_transaction_step_id
545 ,p_name => 'P_ORGANIZATIONID');
546
547 l_tdb_information_category := hr_transaction_api.get_varchar2_value
548 (p_transaction_step_id => p_transaction_step_id
549 ,p_name => 'P_TDB_INFORMATION_CATEGORY');
550
551 l_tdb_information1 := hr_transaction_api.get_varchar2_value
552 (p_transaction_step_id => p_transaction_step_id
553 ,p_name => 'P_TDB_INFORMATION1');
554
555 l_tdb_information2 := hr_transaction_api.get_varchar2_value
556 (p_transaction_step_id => p_transaction_step_id
557 ,p_name => 'P_TDB_INFORMATION2');
558
559 l_tdb_information3 := hr_transaction_api.get_varchar2_value
560 (p_transaction_step_id => p_transaction_step_id
561 ,p_name => 'P_TDB_INFORMATION3');
562
563 l_tdb_information4 := hr_transaction_api.get_varchar2_value
564 (p_transaction_step_id => p_transaction_step_id
565 ,p_name => 'P_TDB_INFORMATION4');
566
567 l_tdb_information5 := hr_transaction_api.get_varchar2_value
568 (p_transaction_step_id => p_transaction_step_id
569 ,p_name => 'P_TDB_INFORMATION5');
570
571 l_tdb_information6 := hr_transaction_api.get_varchar2_value
572 (p_transaction_step_id => p_transaction_step_id
573 ,p_name => 'P_TDB_INFORMATION6');
574
575 l_tdb_information7 := hr_transaction_api.get_varchar2_value
576 (p_transaction_step_id => p_transaction_step_id
577 ,p_name => 'P_TDB_INFORMATION7');
578
579 l_tdb_information8 := hr_transaction_api.get_varchar2_value
580 (p_transaction_step_id => p_transaction_step_id
581 ,p_name => 'P_TDB_INFORMATION8');
582
583 l_tdb_information9 := hr_transaction_api.get_varchar2_value
584 (p_transaction_step_id => p_transaction_step_id
585 ,p_name => 'P_TDB_INFORMATION9');
586
587 l_tdb_information10 := hr_transaction_api.get_varchar2_value
588 (p_transaction_step_id => p_transaction_step_id
589 ,p_name => 'P_TDB_INFORMATION10');
590
591 l_tdb_information11 := hr_transaction_api.get_varchar2_value
592 (p_transaction_step_id => p_transaction_step_id
593 ,p_name => 'P_TDB_INFORMATION11');
594
595 l_tdb_information12 := hr_transaction_api.get_varchar2_value
596 (p_transaction_step_id => p_transaction_step_id
597 ,p_name => 'P_TDB_INFORMATION12');
598
599 l_tdb_information13 := hr_transaction_api.get_varchar2_value
600 (p_transaction_step_id => p_transaction_step_id
601 ,p_name => 'P_TDB_INFORMATION13');
602
603 l_tdb_information14 := hr_transaction_api.get_varchar2_value
604 (p_transaction_step_id => p_transaction_step_id
605 ,p_name => 'P_TDB_INFORMATION14');
606
607 l_tdb_information15 := hr_transaction_api.get_varchar2_value
608 (p_transaction_step_id => p_transaction_step_id
609 ,p_name => 'P_TDB_INFORMATION15');
610
611 l_tdb_information16 := hr_transaction_api.get_varchar2_value
612 (p_transaction_step_id => p_transaction_step_id
613 ,p_name => 'P_TDB_INFORMATION16');
614
615 l_tdb_information17 := hr_transaction_api.get_varchar2_value
616 (p_transaction_step_id => p_transaction_step_id
617 ,p_name => 'P_TDB_INFORMATION17');
618
619 l_tdb_information18 := hr_transaction_api.get_varchar2_value
620 (p_transaction_step_id => p_transaction_step_id
621 ,p_name => 'P_TDB_INFORMATION18');
622
623 l_tdb_information19 := hr_transaction_api.get_varchar2_value
624 (p_transaction_step_id => p_transaction_step_id
625 ,p_name => 'P_TDB_INFORMATION19');
626
627 l_tdb_information20 := hr_transaction_api.get_varchar2_value
628 (p_transaction_step_id => p_transaction_step_id
629 ,p_name => 'P_TDB_INFORMATION20');
630
631 l_offering_id := hr_transaction_api.get_varchar2_value
632 (p_transaction_step_id => p_transaction_step_id
633 ,p_name => 'P_OFFERING_ID');
634
635 l_booking_justification_id := hr_transaction_api.get_varchar2_value
636 (p_transaction_step_id => p_transaction_step_id
637 ,p_name => 'P_BKNG_JUSTIFICATION_ID');
638
639 IF l_booking_justification_id IS NOT NULL THEN
640 OPEN csr_get_booking_justification(l_booking_justification_id);
641 FETCH csr_get_booking_justification INTO l_booking_justification;
642 CLOSE csr_get_booking_justification;
643 END IF;
644
645
646 --
647 -- Now string all the retreived items into p_add_enroll_detail_data
648
649 --
650
651 p_add_enroll_detail_data := nvl(l_eventid,0)
652 ||'^'||nvl(l_activityversionid,0)
653 ||'^'||nvl(l_specialInstruction,'null')
654 ||'^'||nvl(l_keyflexid,0)
655 ||'^'||nvl(l_businessgroupid,0)
656 ||'^'||nvl(l_assignmentid,0)
657 ||'^'||nvl(l_organizationid,0)
658 ||'^'||nvl(l_tdb_information_category,'null')
659 ||'^'||nvl(l_tdb_information1,'null')
660 ||'^'||nvl(l_tdb_information2,'null')
661 ||'^'||nvl(l_tdb_information3,'null')
662 ||'^'||nvl(l_tdb_information4,'null')
663 ||'^'||nvl(l_tdb_information5,'null')
664 ||'^'||nvl(l_tdb_information6,'null')
665 ||'^'||nvl(l_tdb_information7,'null')
666 ||'^'||nvl(l_tdb_information8,'null')
667 ||'^'||nvl(l_tdb_information9,'null')
668 ||'^'||nvl(l_tdb_information10,'null')
669 ||'^'||nvl(l_tdb_information11,'null')
670 ||'^'||nvl(l_tdb_information12,'null')
671 ||'^'||nvl(l_tdb_information13,'null')
672 ||'^'||nvl(l_tdb_information14,'null')
673 ||'^'||nvl(l_tdb_information15,'null')
674 ||'^'||nvl(l_tdb_information16,'null')
675 ||'^'||nvl(l_tdb_information17,'null')
676 ||'^'||nvl(l_tdb_information18,'null')
677 ||'^'||nvl(l_tdb_information19,'null')
678 ||'^'||nvl(l_tdb_information20,'null')
679 ||'^'||nvl(l_offering_id,0)
680 ||'^'||nvl(l_booking_justification,'null');
681
682
683 EXCEPTION
684 WHEN OTHERS THEN
685 RAISE;
686
687 END get_add_enr_dtl_data_from_tt;
688
689
690
691
692
693 PROCEDURE get_review_data_from_tt
694 (p_item_type in varchar2
695 ,p_item_key in varchar2
696 ,p_activity_id in varchar2
697 ,p_person_id out nocopy number
698 ,p_review_data out nocopy varchar2
699 ) is
700
701
702 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
703 l_trans_obj_vers_nums hr_util_web.g_varchar2_tab_type;
704 ln_index number default 0;
705 l_trans_step_rows NUMBER ;
706 l_review_data varchar2(4000);
707 l_trans_step_id number;
708
709
710 BEGIN
711
712 /* hr_transaction_api.get_transaction_step_info
713 (p_item_type => p_item_type
714 ,p_item_key => p_item_key
715 ,p_activity_id => p_activity_id
716 ,p_transaction_step_id => l_trans_step_ids
717 ,p_object_version_number => l_trans_obj_vers_nums
718 ,p_rows => l_trans_step_rows);
719 */
720 --added new
721 l_trans_step_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type
722 ,itemkey => p_item_key
723 ,aname => 'OTA_TRANSACTION_STEP_ID');
724 get_review_data_from_tt(
725 p_transaction_step_id => l_trans_step_id
726 ,p_review_data => l_review_data);
727 /*
728 get_review_data_from_tt(
729 p_transaction_step_id => l_trans_step_ids(ln_index)
730 ,p_review_data => l_review_data);
731 */
732
733 p_review_data := l_review_data;
734
735
736 EXCEPTION
737 WHEN OTHERS THEN
738 RAISE;
739
740 END get_review_data_from_tt;
741
742
743
744 procedure get_review_data_from_tt
745 (p_transaction_step_id in number
746 ,p_review_data out nocopy varchar2
747 )
748 is
749
750 l_eventid ota_events.event_id%TYPE;
751 l_specialInstruction ota_delegate_bookings.SPECIAL_BOOKING_INSTRUCTIONS%TYPE;
752 l_keyflexid pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE;
753 l_ccselectiontext varchar2(2000);
754 l_tdb_information_category ota_delegate_bookings.TDB_INFORMATION_CATEGORY%TYPE;
755 l_tdb_information1 ota_delegate_bookings.TDB_INFORMATION1%TYPE;
756 l_tdb_information2 ota_delegate_bookings.TDB_INFORMATION2%TYPE;
757 l_tdb_information3 ota_delegate_bookings.TDB_INFORMATION3%TYPE;
758 l_tdb_information4 ota_delegate_bookings.TDB_INFORMATION4%TYPE;
759 l_tdb_information5 ota_delegate_bookings.TDB_INFORMATION5%TYPE;
760 l_tdb_information6 ota_delegate_bookings.TDB_INFORMATION6%TYPE;
761 l_tdb_information7 ota_delegate_bookings.TDB_INFORMATION7%TYPE;
762 l_tdb_information8 ota_delegate_bookings.TDB_INFORMATION8%TYPE;
763 l_tdb_information9 ota_delegate_bookings.TDB_INFORMATION9%TYPE;
764 l_tdb_information10 ota_delegate_bookings.TDB_INFORMATION10%TYPE;
765 l_tdb_information11 ota_delegate_bookings.TDB_INFORMATION11%TYPE;
766 l_tdb_information12 ota_delegate_bookings.TDB_INFORMATION12%TYPE;
767 l_tdb_information13 ota_delegate_bookings.TDB_INFORMATION13%TYPE;
768 l_tdb_information14 ota_delegate_bookings.TDB_INFORMATION14%TYPE;
769 l_tdb_information15 ota_delegate_bookings.TDB_INFORMATION15%TYPE;
770 l_tdb_information16 ota_delegate_bookings.TDB_INFORMATION16%TYPE;
771 l_tdb_information17 ota_delegate_bookings.TDB_INFORMATION17%TYPE;
772 l_tdb_information18 ota_delegate_bookings.TDB_INFORMATION18%TYPE;
773 l_tdb_information19 ota_delegate_bookings.TDB_INFORMATION19%TYPE;
774 l_tdb_information20 ota_delegate_bookings.TDB_INFORMATION20%TYPE;
775 l_trnorgnames varchar2(2000);
776 l_offering_id ota_offerings.offering_id%TYPE;
777 l_booking_justification_id ota_bkng_justifications_b.booking_justification_id%TYPE;
778 l_booking_justification ota_bkng_justifications_tl.justification_text%TYPE;
779
780 begin
781
782
783 l_eventid := hr_transaction_api.get_varchar2_value
784 (p_transaction_step_id => p_transaction_step_id
785 ,p_name => 'P_EVENTID');
786
787
788 l_specialInstruction := hr_transaction_api.get_varchar2_value
789 (p_transaction_step_id => p_transaction_step_id
790 ,p_name => 'P_SPECIALINSTRUCTION');
791
792 /* l_keyflexid := hr_transaction_api.get_varchar2_value
793 (p_transaction_step_id => p_transaction_step_id
794 ,p_name => 'P_KEYFLEXID'); */
795
796 l_ccselectiontext := hr_transaction_api.get_varchar2_value
797 (p_transaction_step_id => p_transaction_step_id
798 ,p_name => 'P_CCSELECTIONTEXT');
799
800 l_tdb_information_category := hr_transaction_api.get_varchar2_value
801 (p_transaction_step_id => p_transaction_step_id
802 ,p_name => 'P_TDB_INFORMATION_CATEGORY');
803
804 l_tdb_information1 := hr_transaction_api.get_varchar2_value
805 (p_transaction_step_id => p_transaction_step_id
806 ,p_name => 'P_TDB_INFORMATION1');
807
808 l_tdb_information2 := hr_transaction_api.get_varchar2_value
809 (p_transaction_step_id => p_transaction_step_id
810 ,p_name => 'P_TDB_INFORMATION2');
811
812 l_tdb_information3 := hr_transaction_api.get_varchar2_value
813 (p_transaction_step_id => p_transaction_step_id
814 ,p_name => 'P_TDB_INFORMATION3');
815
816 l_tdb_information4 := hr_transaction_api.get_varchar2_value
817 (p_transaction_step_id => p_transaction_step_id
818 ,p_name => 'P_TDB_INFORMATION4');
819
820 l_tdb_information5 := hr_transaction_api.get_varchar2_value
821 (p_transaction_step_id => p_transaction_step_id
822 ,p_name => 'P_TDB_INFORMATION5');
823
824 l_tdb_information6 := hr_transaction_api.get_varchar2_value
825 (p_transaction_step_id => p_transaction_step_id
826 ,p_name => 'P_TDB_INFORMATION6');
827
828 l_tdb_information7 := hr_transaction_api.get_varchar2_value
829 (p_transaction_step_id => p_transaction_step_id
830 ,p_name => 'P_TDB_INFORMATION7');
831
832 l_tdb_information8 := hr_transaction_api.get_varchar2_value
833 (p_transaction_step_id => p_transaction_step_id
834 ,p_name => 'P_TDB_INFORMATION8');
835
836 l_tdb_information9 := hr_transaction_api.get_varchar2_value
837 (p_transaction_step_id => p_transaction_step_id
838 ,p_name => 'P_TDB_INFORMATION9');
839
840 l_tdb_information10 := hr_transaction_api.get_varchar2_value
841 (p_transaction_step_id => p_transaction_step_id
842 ,p_name => 'P_TDB_INFORMATION10');
843
844 l_tdb_information11 := hr_transaction_api.get_varchar2_value
845 (p_transaction_step_id => p_transaction_step_id
846 ,p_name => 'P_TDB_INFORMATION11');
847
848 l_tdb_information12 := hr_transaction_api.get_varchar2_value
849 (p_transaction_step_id => p_transaction_step_id
850 ,p_name => 'P_TDB_INFORMATION12');
851
852 l_tdb_information13 := hr_transaction_api.get_varchar2_value
853 (p_transaction_step_id => p_transaction_step_id
854 ,p_name => 'P_TDB_INFORMATION13');
855
856 l_tdb_information14 := hr_transaction_api.get_varchar2_value
857 (p_transaction_step_id => p_transaction_step_id
858 ,p_name => 'P_TDB_INFORMATION14');
859
860 l_tdb_information15 := hr_transaction_api.get_varchar2_value
861 (p_transaction_step_id => p_transaction_step_id
862 ,p_name => 'P_TDB_INFORMATION15');
863
864 l_tdb_information16 := hr_transaction_api.get_varchar2_value
865 (p_transaction_step_id => p_transaction_step_id
866 ,p_name => 'P_TDB_INFORMATION16');
867
868 l_tdb_information17 := hr_transaction_api.get_varchar2_value
869 (p_transaction_step_id => p_transaction_step_id
870 ,p_name => 'P_TDB_INFORMATION17');
871
872 l_tdb_information18 := hr_transaction_api.get_varchar2_value
873 (p_transaction_step_id => p_transaction_step_id
874 ,p_name => 'P_TDB_INFORMATION18');
875
876 l_tdb_information19 := hr_transaction_api.get_varchar2_value
877 (p_transaction_step_id => p_transaction_step_id
878 ,p_name => 'P_TDB_INFORMATION19');
879
880 l_tdb_information20 := hr_transaction_api.get_varchar2_value
881 (p_transaction_step_id => p_transaction_step_id
882 ,p_name => 'P_TDB_INFORMATION20');
883
884 l_trnorgnames := hr_transaction_api.get_varchar2_value
885 (p_transaction_step_id => p_transaction_step_id
886 ,p_name => 'P_TRNORGNAMES');
887
888 l_offering_id := hr_transaction_api.get_varchar2_value
889 (p_transaction_step_id => p_transaction_step_id
890 ,p_name => 'P_OFFERING_ID');
891
892 l_booking_justification_id := hr_transaction_api.get_varchar2_value
893 (p_transaction_step_id => p_transaction_step_id
894 ,p_name => 'P_BKNG_JUSTIFICATION_ID');
895
896 IF l_booking_justification_id IS NOT NULL THEN
897 OPEN csr_get_booking_justification(l_booking_justification_id);
898 FETCH csr_get_booking_justification INTO l_booking_justification;
899 CLOSE csr_get_booking_justification;
900 END IF;
901
902 --
903 -- Now string all the retreived items into p_review_data
904
905 --
906
907 --p_review_data := nvl(l_eventid,0)||'^'||nvl(l_specialInstruction,'null')||'^'||nvl(l_keyflexid,0);
908 --Bug#2381073 hdshah initialize with space instead of 0 if l_ccselectiontext is null
909 --p_review_data := nvl(l_eventid,0)||'^'||nvl(l_specialInstruction,'null')||'^'||nvl(l_ccselectiontext,0);
910 p_review_data := nvl(l_eventid,0)
911 ||'^'||nvl(l_specialInstruction,'null')
912 ||'^'||nvl(l_ccselectiontext,' ')
913 ||'^'||nvl(l_tdb_information_category,'null')
914 ||'^'||nvl(l_tdb_information1,'null')
915 ||'^'||nvl(l_tdb_information2,'null')
916 ||'^'||nvl(l_tdb_information3,'null')
917 ||'^'||nvl(l_tdb_information4,'null')
918 ||'^'||nvl(l_tdb_information5,'null')
919 ||'^'||nvl(l_tdb_information6,'null')
920 ||'^'||nvl(l_tdb_information7,'null')
921 ||'^'||nvl(l_tdb_information8,'null')
922 ||'^'||nvl(l_tdb_information9,'null')
923 ||'^'||nvl(l_tdb_information10,'null')
924 ||'^'||nvl(l_tdb_information11,'null')
925 ||'^'||nvl(l_tdb_information12,'null')
926 ||'^'||nvl(l_tdb_information13,'null')
927 ||'^'||nvl(l_tdb_information14,'null')
928 ||'^'||nvl(l_tdb_information15,'null')
929 ||'^'||nvl(l_tdb_information16,'null')
930 ||'^'||nvl(l_tdb_information17,'null')
931 ||'^'||nvl(l_tdb_information18,'null')
932 ||'^'||nvl(l_tdb_information19,'null')
933 ||'^'||nvl(l_tdb_information20,'null')
934 ||'^'||nvl(l_trnorgnames,'null')
935 ||'^'||nvl(l_offering_id,0)
936 ||'^'||nvl(l_booking_justification, 'null');
937
938
939 EXCEPTION
940 WHEN OTHERS THEN
941 RAISE;
942
943 END get_review_data_from_tt;
944
945
946
947
948 PROCEDURE process_api
949 (p_validate IN BOOLEAN,p_transaction_step_id IN NUMBER) IS
950 BEGIN
951
952 -- validation for search page.
953 null;
954
955
956 END process_api;
957
958
959
960
961 PROCEDURE process_api2
962 (p_validate IN BOOLEAN,p_transaction_step_id IN NUMBER
963 ,p_effective_date in varchar2) IS
964
965 l_booking_id OTA_DELEGATE_BOOKINGS.booking_id%type := null;
966 l_booking_status_row OTA_BOOKING_STATUS_TYPES%ROWTYPE;
967 l_delegate_id PER_PEOPLE_F.person_id%TYPE;
968 l_eventid ota_events.event_id%TYPE;
969 l_object_version_number number;
970 l_person_details OTA_LEARNER_ENROLL_SS.csr_person_to_enroll_details%ROWTYPE;
971 l_specialInstruction ota_delegate_bookings.SPECIAL_BOOKING_INSTRUCTIONS%TYPE;
972 l_finance_line_id OTA_FINANCE_LINES.finance_line_id%type:= null;
973 l_item_type HR_API_TRANSACTION_STEPS.ITEM_TYPE%TYPE;
974 l_item_key HR_API_TRANSACTION_STEPS.ITEM_KEY%TYPE;
975 l_activity_id HR_API_TRANSACTION_STEPS.ACTIVITY_ID%TYPE;
976 l_transaction_step_id HR_API_TRANSACTION_STEPS.TRANSACTION_STEP_ID%TYPE;
977 l_transaction_table hr_transaction_ss.transaction_table;
978 l_from varchar2(15);
979 l_cancel_boolean BOOLEAN;
980 l_auto_create_finance VARCHAR2(40);
981 l_price_basis OTA_EVENTS.price_basis%TYPE;
982 l_business_group_id_from PER_ALL_ASSIGNMENTS_F.business_group_id%TYPE;
983 l_business_group_id_to hr_all_organization_units.organization_id%type;
984 l_assignment_id PER_ALL_ASSIGNMENTS_F.assignment_id%TYPE;
985 l_organization_id PER_ALL_ASSIGNMENTS_F.organization_id%TYPE;
986 l_user NUMBER;
987 fapi_finance_header_id OTA_FINANCE_LINES.finance_header_id%TYPE;
988 fapi_object_version_number OTA_FINANCE_LINES.object_version_number%TYPE;
989 fapi_result VARCHAR2(40);
990 fapi_from VARCHAR2(5);
991 fapi_to VARCHAR2(5);
992 l_sponsor_organization_id hr_all_organization_units.organization_id%type;
993 l_event_currency_code ota_events.currency_code%type;
994 l_event_status ota_events.event_status%type;
995 l_cost_allocation_keyflex_id VARCHAR2(1000);
996 l_maximum_internal_attendees NUMBER;
997 l_existing_internal NUMBER;
998 l_maximum_internal_allowed NUMBER;
999 l_automatic_transfer_gl VARCHAR2(40);
1000 result_finance_header_id OTA_FINANCE_LINES.finance_header_id%TYPE;
1001 result_create_finance_line VARCHAR2(5) := 'Y';
1002 result_object_version_number OTA_FINANCE_LINES.object_version_number%TYPE;
1003 l_offering_id ota_events.offering_id%type;
1004 l_date_booking_placed date;
1005 l_current_date date;
1006 l_restricted_assignment_id PER_ASSIGNMENTS_F.assignment_id%type;
1007 l_version_name ota_activity_versions.version_name%type;
1008 l_owner_username fnd_user.user_name%type;
1009 l_owner_id ota_events.owner_id%type;
1010 l_activity_version_id ota_activity_versions.activity_version_id%type;
1011 l_event_title ota_events.title%type;
1012 l_course_start_date ota_events.course_start_date%type;
1013 l_course_start_time ota_events.course_start_time%type;
1014 l_course_end_date ota_events.course_end_date%type;
1015 l_delivery_mode ota_category_usages_tl.category%type;
1016 l_event_location hr_locations_all.location_code%TYPE;
1017
1018 l_notification_text VARCHAR2(1000);
1019 l_business_group_name PER_BUSINESS_GROUPS.name%TYPE := null;
1020
1021 l_business_group_id ota_events.business_group_id%type;
1022 l_standard_price ota_events.standard_price%type;
1023
1024 l_tdb_information_category ota_delegate_bookings.TDB_INFORMATION_CATEGORY%TYPE;
1025 l_tdb_information1 ota_delegate_bookings.TDB_INFORMATION1%TYPE;
1026 l_tdb_information2 ota_delegate_bookings.TDB_INFORMATION2%TYPE;
1027 l_tdb_information3 ota_delegate_bookings.TDB_INFORMATION3%TYPE;
1028 l_tdb_information4 ota_delegate_bookings.TDB_INFORMATION4%TYPE;
1029 l_tdb_information5 ota_delegate_bookings.TDB_INFORMATION5%TYPE;
1030 l_tdb_information6 ota_delegate_bookings.TDB_INFORMATION6%TYPE;
1031 l_tdb_information7 ota_delegate_bookings.TDB_INFORMATION7%TYPE;
1032 l_tdb_information8 ota_delegate_bookings.TDB_INFORMATION8%TYPE;
1033 l_tdb_information9 ota_delegate_bookings.TDB_INFORMATION9%TYPE;
1034 l_tdb_information10 ota_delegate_bookings.TDB_INFORMATION10%TYPE;
1035 l_tdb_information11 ota_delegate_bookings.TDB_INFORMATION11%TYPE;
1036 l_tdb_information12 ota_delegate_bookings.TDB_INFORMATION12%TYPE;
1037 l_tdb_information13 ota_delegate_bookings.TDB_INFORMATION13%TYPE;
1038 l_tdb_information14 ota_delegate_bookings.TDB_INFORMATION14%TYPE;
1039 l_tdb_information15 ota_delegate_bookings.TDB_INFORMATION15%TYPE;
1040 l_tdb_information16 ota_delegate_bookings.TDB_INFORMATION16%TYPE;
1041 l_tdb_information17 ota_delegate_bookings.TDB_INFORMATION17%TYPE;
1042 l_tdb_information18 ota_delegate_bookings.TDB_INFORMATION18%TYPE;
1043 l_tdb_information19 ota_delegate_bookings.TDB_INFORMATION19%TYPE;
1044 l_tdb_information20 ota_delegate_bookings.TDB_INFORMATION20%TYPE;
1045
1046 l_booking_justification_id ota_bkng_justifications_b.booking_justification_id%TYPE;
1047 l_priority_level ota_delegate_bookings.booking_priority%TYPE;
1048
1049 l_timezone fnd_timezones_tl.name%TYPE;
1050 l_course_end_time ota_events.course_start_time%type;
1051 status_not_seeded exception;
1052
1053
1054 CURSOR bg_to (l_event_id ota_events.event_id%TYPE) IS
1055 SELECT hao.business_group_id,
1056 evt.organization_id,
1057 evt.currency_code,
1058 evt.offering_id,
1059 evt.owner_id,
1060 ofr.activity_version_id,
1061 evt.Title,
1062 evt.course_start_date,
1063 evt.course_end_date,
1064 evt.business_group_id bg_id,
1065 evt.course_start_time,
1066 Ctl.Category,
1067 -- Modified for bug 3389890 as usage of inline query in CURSOR is not supported in 8.1.7
1068 -- (Select Category from ota_category_usages_tl where Category_Usage_Id = ofr.Delivery_Mode_Id
1069 -- and Language = userenv('LANG')) Delivery_Mode,
1070 ota_general.get_location_code(ota_utility.get_event_location(evt.event_id)) Location_Name,
1071 ota_timezone_util.get_timezone_name(evt.timezone) timezone,
1072 evt.course_end_time
1073 FROM OTA_EVENTS_VL evt,
1074 OTA_OFFERINGS ofr,
1075 OTA_CATEGORY_USAGES_TL ctl,
1076 HR_ALL_ORGANIZATION_UNITS hao
1077 WHERE evt.event_id = l_eventid
1078 AND evt.parent_offering_id = ofr.offering_id
1079 AND evt.organization_id = hao.organization_id (+)
1080 AND ctl.Category_usage_id = ofr.delivery_mode_id
1081 AND ctl.language = userenv('LANG') ;
1082
1083
1084 --Bug#2221320 hdshah standard price included.
1085 Cursor Get_Event_status is
1086 Select event_status, maximum_internal_attendees,nvl(price_basis,NULL),standard_price
1087 from OTA_EVENTS
1088 WHERE EVENT_ID = l_eventid;
1089
1090 CURSOR get_existing_internal IS
1091 SELECT count(*)
1092 FROM OTA_DELEGATE_BOOKINGS dbt,
1093 OTA_BOOKING_STATUS_TYPES bst
1094 WHERE dbt.event_id = l_eventid
1095 AND dbt.internal_booking_flag = 'Y'
1096 AND dbt.booking_status_type_id = bst.booking_status_type_id
1097 AND bst.type in ('P','A','E');
1098
1099
1100 CURSOR csr_chk_event
1101 (p_event_id IN NUMBER
1102 ,p_person_id IN NUMBER) IS
1103 SELECT ov.booking_id,
1104 ov.date_booking_placed,
1105 ov.object_version_number
1106 FROM ota_booking_status_types os,
1107 ota_delegate_bookings ov
1108 WHERE ov.event_id = p_event_id
1109 AND ov.delegate_person_id = p_person_id
1110 AND os.booking_status_type_id = ov.booking_status_type_id
1111 AND os.type = 'R';
1112
1113 CURSOR csr_activity(p_activity_version_id number )
1114 IS
1115 SELECT version_name
1116 FROM OTA_ACTIVITY_VERSIONS_TL
1117 WHERE activity_version_id = p_activity_version_id
1118 AND language=userenv('LANG');
1119
1120
1121
1122 CURSOR csr_user(p_owner_id in number) IS
1123 SELECT
1124 USER_NAME
1125 FROM
1126 FND_USER
1127 WHERE
1128 Employee_id = p_owner_id
1129 AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
1130
1131
1132 CURSOR csr_get_priority_level(p_booking_justification_id IN VARCHAR2) IS
1133 select priority_level
1134 from ota_bkng_justifications_b
1135 where booking_justification_id = p_booking_justification_id;
1136
1137 BEGIN
1138
1139 l_from := hr_transaction_api.get_varchar2_value
1140 (p_transaction_step_id => p_transaction_step_id
1141 ,p_name => 'P_FROM');
1142
1143 hr_transaction_api.get_transaction_step_info
1144 (p_transaction_step_id => p_transaction_step_id
1145 ,p_item_type => l_item_type
1146 ,p_item_key => l_item_key
1147 ,p_activity_id => l_activity_id);
1148
1149 if l_from = 'REVIEW' then -- Create enrollment
1150
1151 -- establish Savepoint
1152 SAVEPOINT validate_enrollment;
1153
1154 l_eventid := TO_NUMBER(hr_transaction_api.get_varchar2_value
1155 (p_transaction_step_id => p_transaction_step_id
1156 ,p_name => 'P_EVENTID'));
1157
1158
1159 -- hdshah Bug#2213380 read delegate_person_id from p_delegate_person_id instead of p_person_id
1160 l_delegate_id := TO_NUMBER(hr_transaction_api.get_number_Value
1161 (p_transaction_step_id => p_transaction_step_id
1162 -- ,p_name => 'P_PERSON_ID'));
1163 ,p_name => 'P_DELEGATE_PERSON_ID'));
1164
1165
1166 l_specialInstruction := hr_transaction_api.get_varchar2_value
1167 (p_transaction_step_id => p_transaction_step_id
1168 ,p_name => 'P_SPECIALINSTRUCTION');
1169
1170 l_tdb_information_category := hr_transaction_api.get_varchar2_value
1171 (p_transaction_step_id => p_transaction_step_id
1172 ,p_name => 'P_TDB_INFORMATION_CATEGORY');
1173
1174 l_tdb_information1 := hr_transaction_api.get_varchar2_value
1175 (p_transaction_step_id => p_transaction_step_id
1176 ,p_name => 'P_TDB_INFORMATION1');
1177
1178 l_tdb_information2 := hr_transaction_api.get_varchar2_value
1179 (p_transaction_step_id => p_transaction_step_id
1180 ,p_name => 'P_TDB_INFORMATION2');
1181
1182 l_tdb_information3 := hr_transaction_api.get_varchar2_value
1183 (p_transaction_step_id => p_transaction_step_id
1184 ,p_name => 'P_TDB_INFORMATION3');
1185
1186 l_tdb_information4 := hr_transaction_api.get_varchar2_value
1187 (p_transaction_step_id => p_transaction_step_id
1188 ,p_name => 'P_TDB_INFORMATION4');
1189
1190 l_tdb_information5 := hr_transaction_api.get_varchar2_value
1191 (p_transaction_step_id => p_transaction_step_id
1192 ,p_name => 'P_TDB_INFORMATION5');
1193
1194 l_tdb_information6 := hr_transaction_api.get_varchar2_value
1195 (p_transaction_step_id => p_transaction_step_id
1196 ,p_name => 'P_TDB_INFORMATION6');
1197
1198 l_tdb_information7 := hr_transaction_api.get_varchar2_value
1199 (p_transaction_step_id => p_transaction_step_id
1200 ,p_name => 'P_TDB_INFORMATION7');
1201
1202 l_tdb_information8 := hr_transaction_api.get_varchar2_value
1203 (p_transaction_step_id => p_transaction_step_id
1204 ,p_name => 'P_TDB_INFORMATION8');
1205
1206 l_tdb_information9 := hr_transaction_api.get_varchar2_value
1207 (p_transaction_step_id => p_transaction_step_id
1208 ,p_name => 'P_TDB_INFORMATION9');
1209
1210 l_tdb_information10 := hr_transaction_api.get_varchar2_value
1211 (p_transaction_step_id => p_transaction_step_id
1212 ,p_name => 'P_TDB_INFORMATION10');
1213
1214 l_tdb_information11 := hr_transaction_api.get_varchar2_value
1215 (p_transaction_step_id => p_transaction_step_id
1216 ,p_name => 'P_TDB_INFORMATION11');
1217
1218 l_tdb_information12 := hr_transaction_api.get_varchar2_value
1219 (p_transaction_step_id => p_transaction_step_id
1220 ,p_name => 'P_TDB_INFORMATION12');
1221
1222 l_tdb_information13 := hr_transaction_api.get_varchar2_value
1223 (p_transaction_step_id => p_transaction_step_id
1224 ,p_name => 'P_TDB_INFORMATION13');
1225
1226 l_tdb_information14 := hr_transaction_api.get_varchar2_value
1227 (p_transaction_step_id => p_transaction_step_id
1228 ,p_name => 'P_TDB_INFORMATION14');
1229
1230 l_tdb_information15 := hr_transaction_api.get_varchar2_value
1231 (p_transaction_step_id => p_transaction_step_id
1232 ,p_name => 'P_TDB_INFORMATION15');
1233
1234 l_tdb_information16 := hr_transaction_api.get_varchar2_value
1235 (p_transaction_step_id => p_transaction_step_id
1236 ,p_name => 'P_TDB_INFORMATION16');
1237
1238 l_tdb_information17 := hr_transaction_api.get_varchar2_value
1239 (p_transaction_step_id => p_transaction_step_id
1240 ,p_name => 'P_TDB_INFORMATION17');
1241
1242 l_tdb_information18 := hr_transaction_api.get_varchar2_value
1243 (p_transaction_step_id => p_transaction_step_id
1244 ,p_name => 'P_TDB_INFORMATION18');
1245
1246 l_tdb_information19 := hr_transaction_api.get_varchar2_value
1247 (p_transaction_step_id => p_transaction_step_id
1248 ,p_name => 'P_TDB_INFORMATION19');
1249
1250 l_tdb_information20 := hr_transaction_api.get_varchar2_value
1251 (p_transaction_step_id => p_transaction_step_id
1252 ,p_name => 'P_TDB_INFORMATION20');
1253
1254 l_booking_justification_id := hr_transaction_api.get_varchar2_value
1255 (p_transaction_step_id => p_transaction_step_id
1256 ,p_name => 'P_BKNG_JUSTIFICATION_ID');
1257
1258 IF l_booking_justification_id IS NOT NULL THEN
1259 OPEN csr_get_priority_level(l_booking_justification_id);
1260 FETCH csr_get_priority_level INTO l_priority_level;
1261 CLOSE csr_get_priority_level;
1262 END IF;
1263
1264
1265
1266
1267 l_person_details := OTA_LEARNER_ENROLL_SS.Get_Person_To_Enroll_Details(p_person_id => l_delegate_id);
1268
1269 IF l_person_details.full_name is not null then
1270 WF_ENGINE.setitemattrtext(l_item_type,
1271 l_item_key,
1272 'CURRENT_PERSON_DISPLAY_NAME',
1273 l_person_details.full_name);
1274 END IF;
1275
1276 l_restricted_assignment_id := OTA_LEARNER_ENROLL_SS.CHK_DELEGATE_OK_FOR_EVENT(p_delegate_id => l_delegate_id
1277 ,p_event_id => l_eventid);
1278
1279 IF l_restricted_assignment_id IS NULL OR
1280 l_restricted_assignment_id = '-1' THEN
1281 NULL;
1282 ELSE
1283 l_person_details.assignment_id := l_restricted_assignment_id;
1284 END IF;
1285
1286
1287
1288 OPEN bg_to(l_eventid);
1289 FETCH bg_to INTO l_business_group_id_to,
1290 l_sponsor_organization_id,
1291 l_event_currency_code,
1292 l_offering_id,
1293 l_owner_id,
1294 l_activity_version_id,
1295 l_event_title,
1296 l_course_start_date,
1297 l_course_end_date,
1298 l_business_group_id,
1299 l_course_start_time,
1300 l_delivery_mode,
1301 l_event_location,l_timezone,l_course_end_time;
1302
1303 CLOSE bg_to;
1304
1305 For act in csr_activity(l_activity_version_id)
1306 Loop
1307 l_version_name := act.version_name;
1308 End Loop;
1309
1310
1311 if l_owner_id is not null then
1312 For owner in csr_user(l_owner_id)
1313 Loop
1314 l_owner_username := owner.user_name;
1315 End Loop;
1316 end if;
1317
1318
1319 WF_ENGINE.setitemattrtext(l_item_type,
1320 l_item_key,
1321 'OTA_ACTIVITY_VERSION_NAME',
1322 l_version_name);
1323
1324
1325 WF_ENGINE.setitemattrtext(l_item_type,
1326 l_item_key,
1327 'EVENT_OWNER_EMAIL',
1328 l_owner_username);
1329
1330
1331 WF_ENGINE.setitemattrtext(l_item_type,
1332 l_item_key,
1333 'OTA_EVENT_TITLE',
1334 l_eventid); --Enh 5606090: Language support for Event Details.
1335
1336 WF_ENGINE.setitemattrtext(l_item_type,
1337 l_item_key,
1338 'OTA_COURSE_START_DATE',
1339 l_course_start_date);
1340
1341
1342 WF_ENGINE.setitemattrtext(l_item_type,
1343 l_item_key,
1344 'OTA_COURSE_END_DATE',
1345 l_course_end_date);
1346
1347 WF_ENGINE.setitemattrtext(l_item_type,
1348 l_item_key,
1349 'OTA_CLASS_START_TIME',
1350 nvl(l_course_start_time,'00:00'));
1351
1352 WF_ENGINE.setitemattrtext(l_item_type,
1353 l_item_key,
1354 'OTA_DELIVERY_MODE_NAME',
1355 l_delivery_mode);
1356
1357 WF_ENGINE.setitemattrtext(l_item_type,
1358 l_item_key,
1359 'OTA_LOCATION_ADDRESS',
1360 l_event_location);
1361
1362 wf_engine.setItemAttrText(l_item_type,l_item_key,'STATE_LIST',l_timezone);
1363
1364 /* hr_approval_wf.create_item_attrib_if_notexist
1365 (p_item_type => l_item_type
1366 ,p_item_key => l_item_key
1367 ,p_name => 'OTA_CLASS_END_TIME');*/
1368 wf_engine.setItemAttrText(l_item_type,l_item_key,'PQH_EVENT_NAME',nvl(l_course_end_time,'23:59'));
1369
1370 WF_ENGINE.setitemattrnumber(l_item_type,
1371 l_item_key,
1372 'TRANSACTION_ID',
1373 --Bug#4617150
1374 -- hr_transaction_web.get_transaction_id
1375 hr_transaction_ss.get_transaction_id
1376 (p_item_type => l_item_type
1377 ,p_item_key => l_item_key));
1378
1379
1380 WF_ENGINE.setitemattrnumber(l_item_type,
1381 l_item_key,
1382 'FORWARD_FROM_PERSON_ID',
1383 l_delegate_id);
1384
1385
1386
1387 l_cancel_boolean := OTA_LEARNER_ENROLL_SS.Chk_Event_Cancelled_for_Person(p_event_id => l_eventid
1388 ,p_delegate_person_id => l_delegate_id
1389 ,p_delegate_contact_id => null
1390 ,p_booking_id => l_booking_id);
1391
1392 IF (l_cancel_boolean) THEN
1393 -- Call Cancel procedure to cancel the Finance if person Re-enroll
1394 OTA_LEARNER_ENROLL_SS.cancel_finance(l_booking_id);
1395 END IF;
1396
1397
1398 l_booking_status_row := OTA_LEARNER_ENROLL_SS.Get_Booking_Status_for_web(
1399 p_web_booking_status_type => 'REQUESTED'
1400 ,p_business_group_id => l_business_group_id);
1401
1402 IF l_booking_status_row.booking_Status_type_id is null then
1403 fnd_message.set_name ('OTA','OTA_13667_WEB_STATUS_NOT_SEEDE');
1404 RAISE status_not_seeded;
1405 ELSE
1406 WF_ENGINE.setitemattrtext(l_item_type,
1407 l_item_key,
1408 'ENROLL_IN_A_CLASS_STATUS',
1409 l_booking_status_row.name);
1410 END IF ;
1411
1412 WF_ENGINE.setitemattrtext(l_item_type,
1413 l_item_key,
1414 'ENROLL_IN_A_CLASS_STATUS',
1415 l_booking_status_row.name);
1416
1417
1418
1419 select sysdate into l_current_date from dual;
1420
1421 ota_tdb_api_ins2.Create_Enrollment( p_booking_id => l_booking_id
1422 ,p_booking_status_type_id => l_booking_status_row.booking_status_type_id
1423 ,p_delegate_person_id => l_delegate_id
1424 ,p_contact_id => null
1425 ,p_business_group_id => l_business_group_id
1426 ,p_event_id => l_eventid
1427 --,p_date_booking_placed => trunc(l_current_date)
1428 ,p_date_booking_placed => l_current_date
1429 ,p_corespondent => 'S' --l_corespondent
1430 ,p_internal_booking_flag => 'Y'
1431 ,p_person_address_type => 'I'
1432 ,p_number_of_places => 1
1433 ,p_object_version_number => l_object_version_number
1434 ,p_delegate_contact_phone => l_person_details.work_telephone
1435 ,p_delegate_contact_fax => l_person_details.work_fax --Added for bug11778453
1436 ,p_source_of_booking => fnd_profile.value('OTA_DEFAULT_ENROLLMENT_SOURCE') --Bug 5580960: removed hardcoding. Now Source of Booking will be decided by profile value OTA_DEFAULT_ENROLLMENT_SOURCE
1437 ,p_special_booking_instructions => l_specialInstruction
1438 ,p_successful_attendance_flag => 'N'
1439 ,p_finance_line_id => l_finance_line_id
1440 ,p_enrollment_type => 'S'
1441 ,p_validate => FALSE
1442 ,p_organization_id => l_person_details.organization_id
1443 ,p_delegate_assignment_id => l_person_details.assignment_id
1444 ,p_delegate_contact_email => l_person_details.email_address
1445 ,p_tdb_information_category => l_tdb_information_category
1446 ,p_tdb_information1 => l_tdb_information1
1447 ,p_tdb_information2 => l_tdb_information2
1448 ,p_tdb_information3 => l_tdb_information3
1449 ,p_tdb_information4 => l_tdb_information4
1450 ,p_tdb_information5 => l_tdb_information5
1451 ,p_tdb_information6 => l_tdb_information6
1452 ,p_tdb_information7 => l_tdb_information7
1453 ,p_tdb_information8 => l_tdb_information8
1454 ,p_tdb_information9 => l_tdb_information9
1455 ,p_tdb_information10 => l_tdb_information10
1456 ,p_tdb_information11 => l_tdb_information11
1457 ,p_tdb_information12 => l_tdb_information12
1458 ,p_tdb_information13 => l_tdb_information13
1459 ,p_tdb_information14 => l_tdb_information14
1460 ,p_tdb_information15 => l_tdb_information15
1461 ,p_tdb_information16 => l_tdb_information16
1462 ,p_tdb_information17 => l_tdb_information17
1463 ,p_tdb_information18 => l_tdb_information18
1464 ,p_tdb_information19 => l_tdb_information19
1465 ,p_tdb_information20 => l_tdb_information20
1466 ,p_booking_justification_id => l_booking_justification_id
1467 ,p_booking_priority => l_priority_level
1468 ,p_book_from => 'AME');
1469
1470
1471 if (p_validate = true) then
1472 rollback to validate_enrollment;
1473 else
1474
1475 l_auto_create_finance := FND_PROFILE.value('OTA_AUTO_CREATE_FINANCE');
1476 l_automatic_transfer_gl := FND_PROFILE.value('OTA_SSHR_AUTO_GL_TRANSFER');
1477 -- l_user := FND_PROFILE.value('USER_ID');
1478 l_user := fnd_global.user_id; -- Bug 3513140
1479
1480 hr_approval_wf.create_item_attrib_if_notexist
1481 (p_item_type => l_item_type
1482 ,p_item_key => l_item_key
1483 ,p_name => 'OTA_AUTO_CREATE_FINANCE');
1484
1485 WF_ENGINE.setitemattrtext(l_item_type,
1486 l_item_key,
1487 'OTA_AUTO_CREATE_FINANCE',
1488 l_auto_create_finance);
1489
1490 hr_approval_wf.create_item_attrib_if_notexist
1491 (p_item_type => l_item_type
1492 ,p_item_key => l_item_key
1493 ,p_name => 'OTA_SSHR_AUTO_GL_TRANSFER');
1494
1495 WF_ENGINE.setitemattrtext(l_item_type,
1496 l_item_key,
1497 'OTA_SSHR_AUTO_GL_TRANSFER',
1498 l_automatic_transfer_gl);
1499
1500 hr_approval_wf.create_item_attrib_if_notexist
1501 (p_item_type => l_item_type
1502 ,p_item_key => l_item_key
1503 ,p_name => 'OTA_USER_ID');
1504
1505 WF_ENGINE.setitemattrnumber(l_item_type,
1506 l_item_key,
1507 'OTA_USER_ID',
1508 l_user);
1509
1510
1511 WF_ENGINE.setitemattrtext(l_item_type,
1512 l_item_key,
1513 'BOOKING_ID',
1514 l_booking_id);
1515
1516 -- update p_from in transaction table
1517 update hr_api_transaction_values
1518 set varchar2_value = 'APPROVE'
1519 where transaction_step_id = p_transaction_step_id
1520 and name = 'P_FROM';
1521
1522 /*Bug#2258423 hdshah Set wf item attribute for rejection */
1523
1524 hr_approval_wf.create_item_attrib_if_notexist
1525 (p_item_type => l_item_type
1526 ,p_item_key => l_item_key
1527 ,p_name => 'OTA_EVENT_ID');
1528
1529 WF_ENGINE.setitemattrnumber(l_item_type,
1530 l_item_key,
1531 'OTA_EVENT_ID',
1532 l_eventid);
1533
1534 hr_approval_wf.create_item_attrib_if_notexist
1535 (p_item_type => l_item_type
1536 ,p_item_key => l_item_key
1537 ,p_name => 'OTA_DELEGATE_PERSON_ID');
1538
1539
1540 WF_ENGINE.setitemattrnumber(l_item_type,
1541 l_item_key,
1542 'OTA_DELEGATE_PERSON_ID',
1543 l_delegate_id);
1544 /* End Set wf item attribute for rejection */
1545
1546 end if;
1547
1548 ELSIF l_from = 'APPROVE' then -- update enrollment and create finance line if profile is set to YES
1549
1550 l_eventid := TO_NUMBER(hr_transaction_api.get_varchar2_value
1551 (p_transaction_step_id => p_transaction_step_id
1552 ,p_name => 'P_EVENTID'));
1553
1554
1555 l_delegate_id := TO_NUMBER(hr_transaction_api.get_number_Value
1556 (p_transaction_step_id => p_transaction_step_id
1557 ,p_name => 'P_DELEGATE_PERSON_ID'));
1558
1559
1560
1561 l_auto_create_finance := wf_engine.GetItemAttrtext(itemtype => l_item_type
1562 ,itemkey => l_item_key
1563 ,aname => 'OTA_AUTO_CREATE_FINANCE');
1564
1565
1566 l_automatic_transfer_gl := wf_engine.GetItemAttrtext(itemtype => l_item_type
1567 ,itemkey => l_item_key
1568 ,aname => 'OTA_SSHR_AUTO_GL_TRANSFER');
1569
1570 l_user := wf_engine.GetItemAttrNumber(itemtype => l_item_type
1571 ,itemkey => l_item_key
1572 ,aname => 'OTA_USER_ID');
1573
1574 OPEN bg_to(l_eventid);
1575 FETCH bg_to INTO l_business_group_id_to,
1576 l_sponsor_organization_id,
1577 l_event_currency_code,
1578 l_offering_id,
1579 l_owner_id,
1580 l_activity_version_id,
1581 l_event_title,
1582 l_course_start_date,
1583 l_course_end_date,
1584 l_business_group_id,
1585 l_course_start_time,
1586 l_delivery_mode,
1587 l_event_location,l_timezone,l_course_end_time;
1588
1589 CLOSE bg_to;
1590
1591 --Bug#2221320 hdshah l_standard_price included.
1592 OPEN get_event_status;
1593 FETCH get_event_status into l_event_status, l_maximum_internal_attendees,l_price_basis,l_standard_price;
1594 CLOSE get_event_status;
1595
1596 OPEN get_existing_internal;
1597 FETCH get_existing_internal into l_existing_internal;
1598 CLOSE get_existing_internal;
1599
1600 l_maximum_internal_allowed := nvl(l_maximum_internal_attendees,0) - l_existing_internal;
1601
1602
1603 --Update enrollments to Waitlisted status for planned class
1604 IF l_event_status in ('F','P') THEN
1605
1606 l_booking_status_row := OTA_LEARNER_ENROLL_SS.Get_Booking_Status_for_web
1607 (p_web_booking_status_type => 'WAITLISTED'
1608 ,p_business_group_id => l_business_group_id);
1609 /*ELSIF l_event_status in ('P') THEN
1610
1611 l_booking_status_row := OTA_LEARNER_ENROLL_SS.Get_Booking_Status_for_web
1612 (p_web_booking_status_type => 'REQUESTED'
1613 ,p_business_group_id => l_business_group_id);*/
1614
1615 ELSIF l_event_status = 'N' THEN
1616
1617 IF l_maximum_internal_attendees is null then
1618 l_booking_status_row := OTA_LEARNER_ENROLL_SS.Get_Booking_Status_for_web
1619 (p_web_booking_status_type => 'PLACED'
1620 ,p_business_group_id => l_business_group_id);
1621
1622 ELSE
1623
1624 IF l_maximum_internal_allowed > 0 THEN
1625 l_booking_status_row := OTA_LEARNER_ENROLL_SS.Get_Booking_Status_for_web
1626 (p_web_booking_status_type => 'PLACED'
1627 ,p_business_group_id => l_business_group_id);
1628
1629 ELSIF l_maximum_internal_allowed <= 0 THEN
1630 l_booking_status_row := OTA_LEARNER_ENROLL_SS.Get_Booking_Status_for_web
1631 (p_web_booking_status_type => 'WAITLISTED'
1632 ,p_business_group_id => l_business_group_id);
1633
1634 END IF;
1635 END IF;
1636 END IF;
1637
1638 IF l_booking_status_row.booking_Status_type_id is null then
1639 fnd_message.set_name ('OTA','OTA_13667_WEB_STATUS_NOT_SEEDE');
1640 RAISE status_not_seeded;
1641 ELSE
1642 WF_ENGINE.setitemattrtext(l_item_type,
1643 l_item_key,
1644 'ENROLL_IN_A_CLASS_STATUS',
1645 l_booking_status_row.name);
1646 END IF ;
1647
1648 WF_ENGINE.setitemattrtext(l_item_type,
1649 l_item_key,
1650 'ENROLL_IN_A_CLASS_STATUS',
1651 l_booking_status_row.name);
1652
1653 OPEN csr_chk_event(l_eventid, l_delegate_id);
1654 FETCH csr_chk_event INTO l_booking_id,l_date_booking_placed,l_object_version_number;
1655 CLOSE csr_chk_event;
1656
1657 --Changed for the unhelpful error msg issue
1658 IF l_booking_id is null then
1659 hr_utility.set_message(810, 'OTA_467215_ENR_STATUS_CHANGED');
1660 hr_utility.raise_error;
1661 END IF;
1662
1663 select sysdate into l_current_date from dual;
1664
1665 IF l_auto_create_finance = 'Y' and
1666 l_price_basis <> 'N' and
1667 l_event_currency_code is not null THEN
1668
1669 l_cost_allocation_keyflex_id := TO_NUMBER(hr_transaction_api.get_varchar2_value
1670 (p_transaction_step_id => p_transaction_step_id
1671 ,p_name => 'P_KEYFLEXID'));
1672
1673
1674 l_business_group_id_from := TO_NUMBER(hr_transaction_api.get_varchar2_value
1675 (p_transaction_step_id => p_transaction_step_id
1676 ,p_name => 'P_BUSINESSGROUPID'));
1677
1678 l_assignment_id := TO_NUMBER(hr_transaction_api.get_varchar2_value
1679 (p_transaction_step_id => p_transaction_step_id
1680 ,p_name => 'P_ASSIGNMENTID'));
1681
1682 l_organization_id := TO_NUMBER(hr_transaction_api.get_varchar2_value
1683 (p_transaction_step_id => p_transaction_step_id
1684 ,p_name => 'P_ORGANIZATIONID'));
1685
1686 fnd_message.set_name('OTA', 'OTA_443505_COST_CENTER_CHARGED');
1687 l_notification_text := fnd_message.get();
1688
1689 Create_Segment( p_assignment_id => l_assignment_id,
1690 p_business_group_id_from => l_business_group_id_from,
1691 p_business_group_id_to => l_business_group_id_to,
1692 p_organization_id => l_organization_id,
1693 p_sponsor_organization_id => l_sponsor_organization_id,
1694 p_event_id => l_eventid,
1695 p_person_id => l_delegate_id,
1696 p_currency_code => l_event_currency_code,
1697 p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id,
1698 p_user_id => l_user,
1699 p_finance_header_id => fapi_finance_header_id,
1700 p_object_version_number => fapi_object_version_number,
1701 p_result => fapi_result,
1702 p_from_result => fapi_from,
1703 p_to_result => fapi_to,
1704 p_auto_transfer => l_automatic_transfer_gl);
1705
1706 IF fapi_result = 'S' THEN
1707
1708 wf_engine.setItemAttrText (itemtype => l_item_type
1709 ,itemkey => l_item_key
1710 ,aname => 'API_RESULT'
1711 ,avalue => fapi_result);
1712
1713 wf_engine.setItemAttrText (itemtype => l_item_type
1714 ,itemkey => l_item_key
1715 ,aname => 'API_FROM'
1716 ,avalue => fapi_from);
1717
1718 wf_engine.setItemAttrText (itemtype => l_item_type
1719 ,itemkey => l_item_key
1720 ,aname => 'API_TO'
1721 ,avalue => fapi_to);
1722
1723 wf_engine.setItemAttrNumber(itemtype => l_item_type
1724 ,itemkey => l_item_key
1725 ,aname => 'EVENT_ID'
1726 ,avalue => l_eventid);
1727
1728 wf_engine.setItemAttrText (itemtype => l_item_type
1729 ,itemkey => l_item_key
1730 ,aname => 'BUSINESS_GROUP_NAME'
1731 ,avalue => l_business_group_name);
1732
1733 wf_engine.setItemAttrText (itemtype => l_item_type
1734 ,itemkey => l_item_key
1735 ,aname => 'NOTIFICATION_TEXT'
1736 ,avalue => l_notification_text);
1737
1738 result_object_version_number := fapi_object_version_number;
1739 result_finance_header_id := fapi_finance_header_id;
1740 --Bug#2221320 hdshah p_currency_code, p_standard_amount, p_money_amount, p_unitary_amount, p_booking_deal_id,
1741 -- p_booking_deal_type included and p_update_finance_line parameter changed from 'N' to 'Y'.
1742 --Bug#2215026 separate update_enrollment procedure call included for successful finance creation.
1743 ota_tdb_api_upd2.update_enrollment(
1744 p_booking_id => l_booking_id,
1745 p_event_id => l_eventid,
1746 p_object_version_number => l_object_version_number,
1747 p_booking_status_type_id => l_booking_status_row.booking_status_type_id,
1748 p_tfl_object_version_number => result_object_version_number,
1749 -- p_update_finance_line => 'N',
1750 p_update_finance_line => 'Y',
1751 p_currency_code => l_event_currency_code,
1752 p_standard_amount => l_standard_price,
1753 p_money_amount => l_standard_price,
1754 p_unitary_amount => null,
1755 p_booking_deal_id => null,
1756 p_booking_deal_type => 'N',
1757 p_finance_header_id => result_finance_header_id,
1758 p_finance_line_id => l_finance_line_id,
1759 p_date_status_changed => l_current_date,
1760 p_date_booking_placed => l_date_booking_placed);
1761
1762
1763 IF l_automatic_transfer_gl = 'Y' AND l_finance_line_id IS NOT NULL AND l_offering_id is null THEN
1764 UPDATE ota_finance_lines SET transfer_status = 'AT'
1765 WHERE finance_line_id = l_finance_line_id;
1766 END IF;
1767
1768
1769 ELSIF fapi_result = 'E' THEN
1770
1771 l_notification_text := NULL;
1772
1773 wf_engine.setItemAttrText (itemtype => l_item_type
1774 ,itemkey => l_item_key
1775 ,aname => 'API_RESULT'
1776 ,avalue => fapi_result);
1777
1778 wf_engine.setItemAttrText (itemtype => l_item_type
1779 ,itemkey => l_item_key
1780 ,aname => 'API_FROM'
1781 ,avalue => fapi_from);
1782
1783 wf_engine.setItemAttrText (itemtype => l_item_type
1784 ,itemkey => l_item_key
1785 ,aname => 'API_TO'
1786 ,avalue => fapi_to);
1787
1788 wf_engine.setItemAttrNumber(itemtype => l_item_type
1789 ,itemkey => l_item_key
1790 ,aname => 'EVENT_ID'
1791 ,avalue => l_eventid);
1792
1793 wf_engine.setItemAttrText (itemtype => l_item_type
1794 ,itemkey => l_item_key
1795 ,aname => 'BUSINESS_GROUP_NAME'
1796 ,avalue => l_business_group_name);
1797
1798 wf_engine.setItemAttrText (itemtype => l_item_type
1799 ,itemkey => l_item_key
1800 ,aname => 'NOTIFICATION_TEXT'
1801 ,avalue => l_notification_text);
1802
1803 result_object_version_number := l_object_version_number;
1804 result_finance_header_id := NULL;
1805 result_create_finance_line := NULL;
1806
1807 --Bug#2215026 separate update_enrollment procedure call included for unsuccessful finance creation.
1808 ota_tdb_api_upd2.update_enrollment(
1809 p_booking_id => l_booking_id,
1810 p_event_id => l_eventid,
1811 p_object_version_number => l_object_version_number,
1812 p_booking_status_type_id => l_booking_status_row.booking_status_type_id,
1813 p_tfl_object_version_number => result_object_version_number,
1814 p_finance_line_id => l_finance_line_id,
1815 p_date_status_changed => l_current_date,
1816 p_date_booking_placed => l_date_booking_placed);
1817
1818 END IF;
1819 /*Bug#2215026 Two separate update_enrollment procedure calls included for successful and unsuccessful finance creation.
1820 --Bug#2221320 hdshah p_currency_code, p_standard_amount, p_money_amount, p_unitary_amount, p_booking_deal_id, p_booking_deal_type included.
1821 -- p_update_finance_line parameter changed from 'N' to 'Y'.
1822 */
1823 wf_engine.setItemAttrText (itemtype => l_item_type
1824 ,itemkey => l_item_key
1825 ,aname => 'BOOKING_STATUS_TYPE_ID'
1826 ,avalue => l_booking_status_row.booking_status_type_id);
1827
1828 WF_ENGINE.setitemattrtext(l_item_type,
1829 l_item_key,
1830 'BOOKING_ID',
1831 l_booking_id);
1832
1833 ELSE
1834
1835 ota_tdb_api_upd2.update_enrollment(
1836 p_booking_id => l_booking_id,
1837 p_event_id => l_eventid,
1838 p_object_version_number => l_object_version_number,
1839 p_booking_status_type_id => l_booking_status_row.booking_status_type_id,
1840 p_tfl_object_version_number => result_object_version_number,
1841 p_finance_line_id => l_finance_line_id,
1842 p_date_status_changed => l_current_date,
1843 p_date_booking_placed => l_date_booking_placed);
1844
1845 wf_engine.setItemAttrText (itemtype => l_item_type
1846 ,itemkey => l_item_key
1847 ,aname => 'BOOKING_STATUS_TYPE_ID'
1848 ,avalue => l_booking_status_row.booking_status_type_id);
1849
1850 WF_ENGINE.setitemattrtext(l_item_type,
1851 l_item_key,
1852 'BOOKING_ID',
1853 l_booking_id);
1854
1855
1856 END IF;
1857
1858
1859 ELSIF l_from = 'SAVEFORLATER' then
1860
1861 -- from save for later validation
1862 null;
1863
1864 END IF;
1865
1866
1867 EXCEPTION
1868 WHEN status_not_seeded THEN
1869 RAISE;
1870 WHEN OTHERS THEN
1871 RAISE;
1872
1873 END process_api2;
1874
1875
1876
1877
1878 procedure create_enrollment
1879 (itemtype in varchar2,
1880 itemkey in varchar2,
1881 actid in number,
1882 funmode in varchar2,
1883 result out nocopy varchar2 ) is
1884
1885 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
1886 l_trans_obj_vers_nums hr_util_web.g_varchar2_tab_type;
1887 l_trans_step_rows NUMBER ;
1888 l_trans_step_id number;
1889
1890 begin
1891
1892 l_trans_step_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
1893 ,itemkey => itemkey
1894 ,aname => 'OTA_TRANSACTION_STEP_ID');
1895
1896 if ( funmode = 'RUN' ) then
1897
1898 process_api2 (false,l_trans_step_id);
1899 result := 'COMPLETE:SUCCESS';
1900
1901 elsif ( funmode = 'CANCEL' ) then
1902 --
1903 null;
1904 --
1905 --
1906 end if;
1907
1908 end create_enrollment;
1909
1910
1911 procedure cancel_enrollment
1912 (itemtype in varchar2,
1913 itemkey in varchar2,
1914 actid in number,
1915 funmode in varchar2,
1916 result out nocopy varchar2 ) is
1917
1918 l_transaction_step_id number;
1919 l_eventid ota_events.event_id%TYPE;
1920 l_booking_id OTA_DELEGATE_BOOKINGS.booking_id%type := null;
1921 l_business_group_id OTA_DELEGATE_BOOKINGS.business_group_id%type;
1922 l_booking_status_row OTA_BOOKING_STATUS_TYPES%ROWTYPE;
1923 l_delegate_id PER_PEOPLE_F.person_id%TYPE;
1924 l_object_version_number number;
1925 l_date_booking_placed date;
1926 l_current_date date;
1927 result_object_version_number OTA_FINANCE_LINES.object_version_number%TYPE;
1928 l_finance_line_id OTA_FINANCE_LINES.finance_line_id%type:= null;
1929
1930
1931
1932 CURSOR csr_chk_event
1933 (p_event_id IN NUMBER
1934 ,p_person_id IN NUMBER) IS
1935 SELECT ov.booking_id,
1936 ov.date_booking_placed,
1937 ov.object_version_number,
1938 ov.business_group_id
1939 FROM ota_booking_status_types os,
1940 ota_delegate_bookings ov
1941 WHERE ov.event_id = p_event_id
1942 AND ov.delegate_person_id = p_person_id
1943 AND os.booking_status_type_id = ov.booking_status_type_id
1944 AND os.type = 'R';
1945
1946
1947 begin
1948
1949 /* l_transaction_step_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
1950 ,itemkey => itemkey
1951 ,aname => 'OTA_TRANSACTION_STEP_ID');
1952 */
1953
1954
1955 if ( funmode = 'RUN' ) then
1956
1957 /* Bug#2258423 hdshah read event id from workflow instead of transaction table
1958 l_eventid := TO_NUMBER(hr_transaction_api.get_varchar2_value
1959 (p_transaction_step_id => l_transaction_step_id
1960 ,p_name => 'P_EVENTID'));
1961 */
1962
1963 l_eventid := wf_engine.GetItemAttrNumber(itemtype => itemtype
1964 ,itemkey => itemkey
1965 ,aname => 'OTA_EVENT_ID');
1966
1967 /* Bug#2258423 hdshah read delegate person id from workflow instead of transaction table
1968 -- hdshah Bug#2213380 read delegate_person_id from p_delegate_person_id instead of p_person_id
1969 l_delegate_id := TO_NUMBER(hr_transaction_api.get_number_Value
1970 (p_transaction_step_id => l_transaction_step_id
1971 -- ,p_name => 'P_PERSON_ID'));
1972 ,p_name => 'P_DELEGATE_PERSON_ID'));
1973 */
1974
1975 l_delegate_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
1976 ,itemkey => itemkey
1977 ,aname => 'OTA_DELEGATE_PERSON_ID');
1978
1979
1980 OPEN csr_chk_event(l_eventid, l_delegate_id);
1981 FETCH csr_chk_event INTO l_booking_id,l_date_booking_placed,l_object_version_number,l_business_group_id;
1982 CLOSE csr_chk_event;
1983
1984 select sysdate into l_current_date from dual;
1985
1986 l_booking_status_row := OTA_LEARNER_ENROLL_SS.Get_Booking_Status_for_web
1987 (p_web_booking_status_type => 'CANCELLED'
1988 ,p_business_group_id => l_business_group_id);
1989
1990 ota_tdb_api_upd2.update_enrollment(
1991 p_booking_id => l_booking_id,
1992 p_event_id => l_eventid,
1993 p_object_version_number => l_object_version_number,
1994 p_booking_status_type_id => l_booking_status_row.booking_status_type_id,
1995 p_tfl_object_version_number => result_object_version_number,
1996 p_finance_line_id => l_finance_line_id,
1997 p_status_change_comments => null, --Bug 2359495
1998 p_date_status_changed => l_current_date,
1999 p_date_booking_placed => l_date_booking_placed,
2000 p_source_cancel => 'AME');
2001
2002
2003
2004 result := 'COMPLETE:SUCCESS';
2005
2006 elsif ( funmode = 'CANCEL' ) then
2007 --
2008 null;
2009 --
2010 --
2011 end if;
2012
2013 end cancel_enrollment;
2014
2015
2016 procedure validate_enrollment
2017 (p_item_type in varchar2,
2018 p_item_key in varchar2,
2019 p_message out nocopy varchar2) is
2020
2021 l_transaction_step_id number;
2022 begin
2023
2024 l_transaction_step_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type
2025 ,itemkey => p_item_key
2026 ,aname => 'OTA_TRANSACTION_STEP_ID');
2027 process_api2(true,l_transaction_step_id);
2028 p_message := 'S' ;
2029 EXCEPTION
2030 When OTHERS Then
2031 p_message := fnd_message.get();
2032 If p_message is NULL then
2033 p_message := substr(SQLERRM,11,(length(SQLERRM)-10));
2034 End If;
2035
2036 end validate_enrollment;
2037
2038
2039
2040
2041 Procedure create_segment
2042 (p_assignment_id in number
2043 ,p_business_group_id_from in number
2044 ,p_business_group_id_to in number
2045 ,p_organization_id in number
2046 ,p_sponsor_organization_id in number
2047 ,p_event_id in number
2048 ,p_person_id in number
2049 ,p_currency_code in varchar2
2050 ,p_cost_allocation_keyflex_id in number
2051 ,p_user_id in number
2052 ,p_finance_header_id out nocopy number
2053 ,p_object_version_number out nocopy number
2054 ,p_result out nocopy varchar2
2055 ,p_from_result out nocopy varchar2
2056 ,p_to_result out nocopy varchar2
2057 ,p_auto_transfer in varchar2
2058 ) IS
2059
2060 Begin
2061
2062 ota_crt_finance_segment.Create_Segment(p_assignment_id => p_assignment_id,
2063 p_business_group_id_from => p_business_group_id_from,
2064 p_business_group_id_to => p_business_group_id_to,
2065 p_organization_id => p_organization_id,
2066 p_sponsor_organization_id => p_sponsor_organization_id,
2067 p_event_id => p_event_id,
2068 p_person_id => p_person_id,
2069 p_currency_code => p_currency_code,
2070 p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id,
2071 p_user_id => p_user_id,
2072 p_finance_header_id => p_finance_header_id,
2073 p_object_version_number => p_object_version_number,
2074 p_result => p_result,
2075 p_from_result => p_from_result,
2076 p_to_result => p_to_result);
2077
2078 end create_segment;
2079
2080 --
2081 -- -----------------------------------------------------------
2082 -- Cross Charges Notifications (Workflow Notifications)
2083 -- -----------------------------------------------------------
2084 --
2085 Procedure Cross_Charges_Notifications ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
2086 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
2087 actid IN NUMBER,
2088 funcmode IN VARCHAR2,
2089 resultout OUT nocopy VARCHAR2 )
2090 IS
2091
2092 CURSOR user_name(p_event_id OTA_EVENTS.event_id%TYPE) IS
2093 SELECT usr.user_name
2094 FROM OTA_EVENTS evt,
2095 FND_USER USR
2096 WHERE evt.event_id = p_event_id and
2097 usr.employee_id = evt.owner_id
2098 and trunc(sysdate) between usr.start_date and nvl(usr.end_date,to_date('4712/12/31', 'YYYY/MM/DD')) ; --Bug 5676892
2099
2100 l_api_result VARCHAR2(4000);
2101 l_api_from VARCHAR2(4000);
2102 l_api_to VARCHAR2(4000);
2103 l_event_id NUMBER;
2104 l_user_name FND_USER.USER_NAME%TYPE;
2105 BEGIN
2106
2107 l_event_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
2108 ,itemkey => itemkey
2109 ,aname => 'OTA_EVENT_ID');
2110
2111
2112 OPEN user_name(l_event_id);
2113 FETCH user_name INTO l_user_name;
2114 CLOSE user_name;
2115
2116
2117 wf_engine.setItemAttrText (itemtype => itemtype
2118 ,itemkey => itemkey
2119 ,aname => 'EVENT_OWNER_EMAIL'
2120 ,avalue => l_user_name);
2121
2122 l_api_result := wf_engine.GetItemAttrText(itemtype => itemtype
2123 ,itemkey => itemkey
2124 ,aname => 'API_RESULT');
2125
2126 l_api_from := wf_engine.GetItemAttrText(itemtype => itemtype
2127 ,itemkey => itemkey
2128 ,aname => 'API_FROM');
2129
2130 l_api_to := wf_engine.GetItemAttrText(itemtype => itemtype
2131 ,itemkey => itemkey
2132 ,aname => 'API_TO');
2133
2134 IF (funcmode='RUN') THEN
2135 IF l_api_result = 'S' THEN
2136 resultout := 'COMPLETE:SUCCESS';
2137 RETURN;
2138 ELSE
2139 IF l_api_from IS NOT NULL THEN
2140 resultout := 'COMPLETE:FROM_ERROR';
2141 ELSIF l_api_to IS NOT NULL THEN
2142 resultout := 'COMPLETE:ERROR_TO';
2143 ELSE
2144 resultout := 'COMPLETE:SUCCESS';
2145 RETURN;
2146 END IF;
2147 END IF;
2148 END IF;
2149
2150 IF (funcmode='CANCEL') THEN
2151 resultout := 'COMPLETE';
2152 RETURN;
2153 END IF;
2154
2155 END Cross_Charges_Notifications;
2156
2157 Procedure set_addnl_attributes(p_item_type in wf_items.item_type%type,
2158 p_item_key in wf_items.item_key%type,
2159 p_eventid in ota_events.event_id %type
2160 )
2161
2162 is
2163
2164 l_proc varchar2(72) := g_package||'set_addnl_attributes';
2165
2166 l_actual_cost ota_events.actual_cost%type;
2167 l_budget_currency_code ota_events.budget_currency_code%type;
2168 l_act_ver_id ota_events.activity_version_id%type;
2169 l_off_id ota_events.parent_offering_id%type;
2170 l_event_id ota_events.event_id%type;
2171 l_event_type ota_events.event_type%type;
2172 l_object_type varchar2(240);
2173 l_timezone ota_events.timezone%type;
2174
2175
2176
2177 cursor get_addnl_event_info
2178 is
2179 select
2180 --added after show n tell
2181 oev.actual_cost, oev.budget_currency_code,
2182 oev.parent_offering_id,oev.timezone
2183 from ota_events oev
2184 where
2185 oev.event_id = l_event_id;
2186
2187
2188 cursor get_lang_det is
2189 select ofe.language_id, ocu.category
2190 from ota_offerings ofe, ota_category_usages_tl ocu
2191 where ofe.delivery_mode_id = ocu.category_usage_id
2192 and ocu.language=USERENV('LANG')
2193 and ofe.offering_id = l_off_id;
2194
2195
2196 l_lang_description fnd_languages_vl.description%TYPE;
2197 l_curr_name fnd_currencies_vl.name%TYPE;
2198 l_lang_id ota_offerings.language_id%type;
2199 l_delivery_method ota_category_usages.category%type;
2200
2201 begin
2202
2203
2204 open get_addnl_event_info;
2205 fetch get_addnl_event_info into l_actual_cost,
2206 l_budget_currency_code,l_off_id,l_timezone;
2207 close get_addnl_event_info;
2208
2209 open get_lang_det;
2210 fetch get_lang_det into l_lang_id,l_delivery_method;
2211 close get_lang_det;
2212
2213 --l_course_name := ota_general.get_course_name(l_act_ver_id);
2214 l_curr_name := ota_general.fnd_currency_name(l_budget_currency_code);
2215 l_curr_name := l_actual_cost || ' ' || l_curr_name;
2216
2217 l_lang_description := ota_general.fnd_lang_desc(l_lang_id);
2218
2219 --set wf item attributes
2220
2221 --wf_engine.setItemAttrText(p_item_type,p_item_key,'COST',l_curr_name );
2222 wf_engine.setItemAttrText(p_item_type,p_item_key,'STATE_LIST',l_timezone);
2223 --wf_engine.setItemAttrText(p_item_type,p_item_key,'LANGUAGE',l_lang_description );
2224
2225
2226
2227
2228
2229 end set_addnl_attributes;
2230
2231 Procedure Delivery_Mode_Notifications ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
2232 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
2233 actid IN NUMBER,
2234 funcmode IN VARCHAR2,
2235 resultout OUT nocopy VARCHAR2 )
2236 IS
2237
2238 CURSOR user_name(p_event_id OTA_EVENTS.event_id%TYPE) IS
2239 SELECT usr.user_name
2240 FROM OTA_EVENTS evt,
2241 FND_USER USR
2242 WHERE evt.event_id = p_event_id and
2243 usr.employee_id = evt.owner_id
2244 and trunc(sysdate) between usr.start_date and nvl(usr.end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
2245
2246
2247 CURSOR csr_booking_status(p_booking_id ota_delegate_bookings.booking_id%type) IS
2248 SELECT bst.Type
2249 FROM OTA_DELEGATE_BOOKINGS tdb,
2250 OTA_BOOKING_STATUS_TYPES bst
2251 WHERE tdb.booking_id = p_booking_id
2252 AND bst.booking_status_type_id = tdb.booking_status_type_id;
2253
2254 CURSOR delivery_mode(p_event_id OTA_EVENTS.event_id%TYPE) IS
2255 Select OCU.synchronous_flag, OCU.online_flag
2256 From ota_events OEV,
2257 ota_offerings OFR,
2258 ota_category_usages OCU
2259 Where OFR.offering_id = OEV.parent_offering_id
2260 And OCU.category_usage_id = OFR.delivery_mode_id
2261 And OEV.event_id = p_event_id;
2262
2263 l_event_id NUMBER;
2264 l_user_name FND_USER.USER_NAME%TYPE;
2265 l_booking_id ota_delegate_bookings.booking_id%type;
2266 l_notification_text varchar2(2000);
2267 l_status_type ota_booking_status_types.type%type;
2268
2269 l_synchronous_flag ota_category_usages.synchronous_flag%type;
2270 l_online_flag ota_category_usages.online_flag%type;
2271 l_dm_status varchar2(20);
2272 l_approval_req_flag varchar2(100);
2273 l_forward_to_person_id per_people_f.person_id%type;
2274
2275 BEGIN
2276
2277 l_event_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
2278 ,itemkey => itemkey
2279 ,aname => 'OTA_EVENT_ID');
2280
2281 /* set_addnl_attributes(p_item_type => itemtype,
2282 p_item_key => itemkey,
2283 p_eventid => l_event_id
2284 );*/
2285
2286
2287 OPEN user_name(l_event_id);
2288 FETCH user_name INTO l_user_name;
2289 CLOSE user_name;
2290
2291 l_approval_req_flag := wf_engine.GetItemAttrText(itemtype => itemtype
2292 ,itemkey => itemkey
2293 ,aname => 'HR_RUNTIME_APPROVAL_REQ_FLAG');
2294
2295 l_booking_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
2296 ,itemkey => itemkey
2297 ,aname => 'BOOKING_ID');
2298
2299 l_notification_text := wf_engine.GetItemAttrText(itemtype => itemtype
2300 ,itemkey => itemkey
2301 ,aname => 'NOTIFICATION_TEXT');
2302
2303 IF l_booking_id is not null then
2304 For sts in csr_booking_status(l_booking_id)
2305 LOOP
2306 l_status_type := sts.type;
2307 END LOOP;
2308
2309 If l_status_type = 'W' then
2310 fnd_message.set_name('OTA', 'OTA_443496_WAITLIST_NTF_TEXT');
2311
2312 l_notification_text := l_notification_text || fnd_message.get();
2313
2314 wf_engine.setItemAttrText (itemtype => itemtype
2315 ,itemkey => itemkey
2316 ,aname => 'NOTIFICATION_TEXT'
2317 ,avalue => l_notification_text);
2318 End If;
2319
2320 END IF;
2321
2322 wf_engine.setItemAttrText (itemtype => itemtype
2323 ,itemkey => itemkey
2324 ,aname => 'EVENT_OWNER_EMAIL'
2325 ,avalue => l_user_name);
2326
2327 /*bug# 7346984 starts*/
2328 l_forward_to_person_id := wf_engine.GetItemAttrNumber
2329 (itemtype => itemtype
2330 ,itemkey => itemkey
2331 ,aname =>'FORWARD_TO_PERSON_ID');
2332
2333 if(l_forward_to_person_id is null) then
2334 wf_engine.SetItemAttrNumber(itemtype => itemtype
2335 ,itemkey => itemkey
2336 ,aname => 'FORWARD_TO_PERSON_ID'
2337 ,avalue => wf_engine.GetItemAttrNumber(itemtype => itemtype
2338 ,itemkey => itemkey
2339 ,aname => 'CREATOR_PERSON_ID'));
2340
2341 wf_engine.SetItemAttrText(itemtype => itemtype
2342 ,itemkey => itemkey
2343 ,aname => 'FORWARD_TO_USERNAME'
2344 ,avalue => wf_engine.GetItemAttrText(itemtype => itemtype
2345 ,itemkey => itemkey
2346 ,aname => 'CREATOR_PERSON_USERNAME'));
2347
2348 wf_engine.SetItemAttrText(itemtype => itemtype
2349 ,itemkey => itemkey
2350 ,aname => 'FORWARD_TO_DISPLAY_NAME'
2351 ,avalue => wf_engine.GetItemAttrText(itemtype => itemtype
2352 ,itemkey => itemkey
2353 ,aname => 'CREATOR_PERSON_DISPLAY_NAME'));
2354 end if;
2355 /*bug# 7346984 ends*/
2356
2357 OPEN delivery_mode(l_event_id);
2358 FETCH delivery_mode INTO l_synchronous_flag, l_online_flag;
2359 CLOSE delivery_mode;
2360
2361 If upper(l_online_flag) = 'Y' Then
2362 If upper(l_synchronous_flag) = 'Y' Then
2363 if l_approval_req_flag = 'NO' then
2364 l_dm_status := 'COMPLETE:ONSYNN';
2365 else
2366 l_dm_status := 'COMPLETE:ONSYN';
2367 end if;
2368 Else
2369 if l_approval_req_flag = 'NO' then
2370 l_dm_status := 'COMPLETE:ONASYNN';
2371 else
2372 l_dm_status := 'COMPLETE:ONASYN';
2373 end if;
2374 End If;
2375 Else
2376 if l_approval_req_flag = 'NO' then
2377 l_dm_status := 'COMPLETE:OFFLINEN';
2378 else
2379 l_dm_status := 'COMPLETE:OFFLINE';
2380 end if;
2381 End If;
2382
2383 IF (funcmode='RUN') THEN
2384 resultout := l_dm_status;
2385 RETURN;
2386 END IF;
2387
2388 IF (funcmode='CANCEL') THEN
2389 resultout := 'COMPLETE';
2390 RETURN;
2391 END IF;
2392
2393 END Delivery_Mode_Notifications;
2394
2395
2396
2397 --
2398 -- ------------------------------------------------------------------
2399 -- PROCEDURE Approved
2400 -- ------------------------------------------------------------------
2401 --
2402 Procedure Approved ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
2403 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
2404 actid IN NUMBER,
2405 funcmode IN VARCHAR2,
2406 resultout OUT nocopy VARCHAR2 ) IS
2407
2408 l_forward_to_person_id per_people_f.person_id%type;
2409 BEGIN
2410
2411 IF (funcmode='RUN') THEN
2412 wf_engine.setItemAttrText (itemtype => itemtype
2413 ,itemkey => itemkey
2414 ,aname => 'APPROVAL_RESULT'
2415 ,avalue => 'ACCEPTED');
2416 resultout:='COMPLETE';
2417 /*bug# 3445970 starts*/
2418 l_forward_to_person_id := wf_engine.GetItemAttrNumber
2419 (itemtype => itemtype
2420 ,itemkey => itemkey
2421 ,aname =>'FORWARD_TO_PERSON_ID');
2422
2423 if(l_forward_to_person_id is null) then
2424 wf_engine.SetItemAttrNumber(itemtype => itemtype
2425 ,itemkey => itemkey
2426 ,aname => 'FORWARD_TO_PERSON_ID'
2427 ,avalue => wf_engine.GetItemAttrNumber(itemtype => itemtype
2428 ,itemkey => itemkey
2429 ,aname => 'CREATOR_PERSON_ID'));
2430
2431 wf_engine.SetItemAttrText(itemtype => itemtype
2432 ,itemkey => itemkey
2433 ,aname => 'FORWARD_TO_USERNAME'
2434 ,avalue => wf_engine.GetItemAttrText(itemtype => itemtype
2435 ,itemkey => itemkey
2436 ,aname => 'CREATOR_PERSON_USERNAME'));
2437
2438 wf_engine.SetItemAttrText(itemtype => itemtype
2439 ,itemkey => itemkey
2440 ,aname => 'FORWARD_TO_DISPLAY_NAME'
2441 ,avalue => wf_engine.GetItemAttrText(itemtype => itemtype
2442 ,itemkey => itemkey
2443 ,aname => 'CREATOR_PERSON_DISPLAY_NAME'));
2444 end if;
2445 /*bug# 3445970 ends*/
2446 RETURN;
2447 END IF;
2448
2449 IF (funcmode='CANCEL') THEN
2450 resultout:='COMPLETE';
2451 RETURN;
2452 END IF;
2453
2454 END Approved;
2455
2456 Function is_class_pending_for_approval
2457 (p_event_id in varchar2,
2458 p_person_id in number,
2459 p_process_name in varchar2)
2460 RETURN VARCHAR2 IS
2461 --
2462 Cursor cur_get_pending_trn_step_id IS
2463 Select
2464 hrtrns.transaction_step_id
2465 From
2466 wf_item_activity_statuses process
2467 ,wf_item_attribute_values attribute2
2468 ,wf_process_activities activity
2469 ,hr_api_transaction_steps hrtrns
2470 Where
2471 activity.activity_name = p_process_name
2472 and activity.process_item_type = 'HRSSA'
2473 and activity.activity_item_type = 'HRSSA'
2474 and activity.instance_id = process.process_activity
2475 and process.activity_status = 'ACTIVE'
2476 and process.item_type = 'HRSSA'
2477 and process.item_key = attribute2.item_key
2478 and attribute2.item_type = process.item_type
2479 and attribute2.name = 'TRAN_SUBMIT'
2480 and attribute2.text_value = 'Y'
2481 and process.item_key = hrtrns.item_key
2482 and trim(upper(hrtrns.api_name)) = trim(upper(g_package||'.PROCESS_API2'))
2483 and hrtrns.item_type = 'HRSSA'
2484 and nvl(hrtrns.update_person_id, hrtrns.creator_person_id) = p_person_id;
2485
2486 l_proc varchar2(72) := g_package || '.is_class_pending_for_approval';
2487 l_temp_event_id varchar2(100) := null;
2488 l_return_value varchar2(1) := 'N';
2489 BEGIN
2490
2491 hr_utility.set_location('Entering:'||l_proc, 5);
2492
2493 FOR c in cur_get_pending_trn_step_id
2494 LOOP
2495 l_temp_event_id :=
2496 hr_transaction_api.get_varchar2_value
2497 (p_transaction_step_id => c.transaction_step_id
2498 ,p_name => 'P_EVENTID');
2499
2500 If (l_temp_event_id is not null and l_temp_event_id = p_event_id) Then
2501 exit;
2502 null;
2503 End If;
2504 End LOOP;
2505
2506 If (l_temp_event_id is not null and l_temp_event_id = p_event_id) Then
2507 l_return_value := 'Y';
2508 End If;
2509
2510 RETURN l_return_value;
2511 hr_utility.set_location('Entering:'||l_proc, 30);
2512 END is_class_pending_for_approval;
2513
2514
2515 --
2516 -- ------------------------------------------------------------------
2517 -- PROCEDURE ntf_details_to_lrnr
2518 -- ------------------------------------------------------------------
2519 --
2520
2521 PROCEDURE ntf_details_to_lrnr
2522 (itemtype in WF_ITEMS.ITEM_TYPE%TYPE
2523 ,itemkey in WF_ITEMS.ITEM_KEY%TYPE
2524 ,actid in number
2525 ,funcmode in varchar2
2526 ,resultout out nocopy varchar2)
2527 IS
2528
2529 l_details varchar2(30) := null;
2530 l_creator_person_id number :=null;
2531 l_current_person_id number :=null;
2532 BEGIN
2533
2534 l_creator_person_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
2535 ,itemkey => itemkey
2536 ,aname => 'CREATOR_PERSON_ID');
2537
2538 l_current_person_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
2539 ,itemkey => itemkey
2540 ,aname => 'CURRENT_PERSON_ID');
2541 if(l_creator_person_id = l_current_person_id) then
2542 l_details := wf_engine.getitemattrtext(itemtype,
2543 itemkey,
2544 'FYI_NTF_DETAILS',
2545 true);
2546 if l_details = 'Y' then
2547 resultout := 'COMPLETE:'|| 'Y';
2548 else
2549 resultout := 'COMPLETE:'|| 'N';
2550 end if;
2551 else
2552 resultout := 'COMPLETE:'|| 'N';
2553 end if;
2554
2555
2556
2557 EXCEPTION
2558 WHEN OTHERS THEN NULL;
2559 RAISE;
2560 END ntf_details_to_lrnr;
2561
2562 end ota_learner_enroll_review_ss;