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