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