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