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