[Home] [Help]
PACKAGE BODY: APPS.OTA_ADD_TRAINING_SS
Source
1 PACKAGE BODY ota_add_training_ss AS
2 /* $Header: otaddwrs.pkb 120.3 2011/10/14 10:45:20 jaysridh ship $ */
3
4 g_package varchar2(33) := ' ota_add_training_ss'; -- Global package name
5 g_called_from varchar2(1) := 'S' ; -- Global variable to differentiate whether calling from review or submit
6
7
8 /*
9 ||===========================================================================
10 || PROCEDURE: save_add_training
11 ||---------------------------------------------------------------------------
12 ||
13 || Description:
14 || This procedure will save additional training details in Transaction table
15 ||
16 ||
17 || Pre Conditions:
18 ||
19 || In Arguments:
20 ||
21 ||
22 ||
23 || out nocopy Arguments:
24 ||
25 || In out nocopy Arguments:
26 ||
27 || Post Success:
28 ||
29 ||
30 || Post Failure:
31 || Raises an exception
32 ||
33 || Access Status:
34 || Public.
35 ||
36 ||===========================================================================
37 */
38 PROCEDURE save_add_training(
39 p_login_person_id IN NUMBER
40 , p_item_type IN VARCHAR2
41 , p_item_key IN VARCHAR2
42 , p_activity_id IN NUMBER
43 , p_save_mode IN VARCHAR2
44 , p_error_message OUT NOCOPY VARCHAR2
45 , p_title IN VARCHAR2
46 , p_supplier IN VARCHAR2
47 , p_eq_ota_activity IN VARCHAR2
48 , p_location IN VARCHAR2
49 , p_trntype IN VARCHAR2
50 , p_duration IN VARCHAR2
51 , p_duration_unit IN VARCHAR2
52 , p_status IN VARCHAR2
53 , p_completion_date IN DATE
54 , p_award IN VARCHAR2
55 , p_score IN VARCHAR2
56 , p_internal_contact_person IN VARCHAR2 --This contains contact id
57 , p_historyId IN VARCHAR2
58 , p_nth_information_category IN VARCHAR2
59 , p_nth_information1 IN VARCHAR2
60 , p_nth_information2 IN VARCHAR2
61 , p_nth_information3 IN VARCHAR2
62 , p_nth_information4 IN VARCHAR2
63 , p_nth_information5 IN VARCHAR2
64 , p_nth_information6 IN VARCHAR2
65 , p_nth_information7 IN VARCHAR2
66 , p_nth_information8 IN VARCHAR2
67 , p_nth_information9 IN VARCHAR2
68 , p_nth_information10 IN VARCHAR2
69 , p_nth_information11 IN VARCHAR2
70 , p_nth_information12 IN VARCHAR2
71 , p_nth_information13 IN VARCHAR2
72 , p_nth_information14 IN VARCHAR2
73 , p_nth_information15 IN VARCHAR2
74 , p_nth_information16 IN VARCHAR2
75 , p_nth_information17 IN VARCHAR2
76 , p_nth_information18 IN VARCHAR2
77 , p_nth_information19 IN VARCHAR2
78 , p_nth_information20 IN VARCHAR2
79 , p_contact_name IN VARCHAR2
80 , p_activity_name IN VARCHAR2
81 , p_obj_ver_no IN VARCHAR2
82 , p_business_grp_id IN VARCHAR2
83 , p_person_id IN NUMBER
84 , p_from IN VARCHAR2
85 , p_oafunc IN VARCHAR2
86 , p_processname IN VARCHAR2
87 , p_calledfrom IN VARCHAR2
88 , p_frommenu IN VARCHAR2
89 , p_org_id IN VARCHAR2
90 , p_transaction_mode IN VARCHAR2
91 , p_check_changes_result OUT NOCOPY VARCHAR2
92 , p_Status_Meaning IN VARCHAR2
93 , p_Type_Meaning IN VARCHAR2
94 ) AS
95
96 l_transaction_id NUMBER ;
97 l_transaction_step_id NUMBER ;
98 l_trans_obj_vers_num NUMBER ;
99 l_count INTEGER ;
100 l_transaction_table hr_transaction_ss.transaction_table;
101 l_review_item_name VARCHAR2(50);
102 l_message_number VARCHAR2(10);
103 l_result VARCHAR2(100) ;
104 l_old_transaction_step_id NUMBER;
105 l_old_object_version_number NUMBER;
106
107 l_check_changes_result NUMBER := 100;
108 --
109 BEGIN
110 --
111 -- First, check if transaction id exists or not
112 l_transaction_id := hr_transaction_ss.get_transaction_id
113 (p_item_type => p_item_type
114 ,p_item_key => p_item_key);
115 --
116 IF l_transaction_id is null THEN
117 -- Start a Transaction
118 hr_transaction_ss.start_transaction
119 (itemtype => p_item_type
120 ,itemkey => p_item_key
121 ,actid => p_activity_id
122 ,funmode => 'RUN'
123 ,p_login_person_id => p_login_person_id
124 ,result => l_result);
125 l_transaction_id := hr_transaction_ss.get_transaction_id
126 (p_item_type => p_item_type
127 ,p_item_key => p_item_key);
128 END IF;
129 --
130
131 -- Delete transaction step if exist
132 --
133 IF ( hr_transaction_api.transaction_step_exist
134 (p_item_type => p_item_type
135 ,p_item_key => p_item_key
136 ,p_activity_id => p_activity_id) ) THEN
137
138 hr_transaction_api.get_transaction_step_info
139 (p_item_type => p_item_type
140 ,p_item_key => p_item_key
141 ,p_activity_id => p_activity_id
142 ,p_transaction_step_id => l_old_transaction_step_id
143 ,p_object_version_number => l_old_object_version_number);
144
145 hr_transaction_api.delete_transaction_step
146 (p_validate => FALSE
147 ,p_transaction_step_id => l_old_transaction_step_id
148 ,p_person_id => p_login_person_id
149 ,p_object_version_number => l_old_object_version_number);
150 END IF;
151
152 --If transaction mode is 'UPDATE' then check if any changes are made or not
153 If (p_transaction_mode = 'UPDATE') Then
154 check_changes
155 (p_historyId
156 ,to_number(p_internal_contact_person)
157 ,p_title
158 ,p_supplier
159 ,p_trntype
160 ,p_location
161 ,p_completion_date
162 ,p_award
163 ,p_score
164 ,to_number(p_duration)
165 ,p_duration_unit
166 ,to_number(p_eq_ota_activity)
167 ,p_status
168 ,p_nth_information_category
169 ,p_nth_information1
170 ,p_nth_information2
171 ,p_nth_information3
172 ,p_nth_information4
173 ,p_nth_information5
174 ,p_nth_information6
175 ,p_nth_information7
176 ,p_nth_information8
177 ,p_nth_information9
178 ,p_nth_information10
179 ,p_nth_information11
180 ,p_nth_information12
181 ,p_nth_information13
182 ,p_nth_information14
183 ,p_nth_information15
184 ,p_nth_information16
185 ,p_nth_information17
186 ,p_nth_information18
187 ,p_nth_information19
188 ,p_nth_information20
189 ,l_check_changes_result);
190
191 p_check_changes_result := l_check_changes_result;
192 If (l_check_changes_result = 0) Then --Return if no changes are made
193 Return;
194 End If;
195 End If;
196
197 p_check_changes_result := l_check_changes_result;
198
199 --
200 -- Create a transaction step
201 --
202 hr_transaction_api.create_transaction_step
203 (p_validate => FALSE
204 ,p_creator_person_id => p_login_person_id
205 ,p_transaction_id => l_transaction_id
206 ,p_api_name => g_package || '.PROCESS_API'
207 ,p_item_type => p_item_type
208 ,p_item_key => p_item_key
209 ,p_activity_id => p_activity_id
210 ,p_transaction_step_id => l_transaction_step_id
211 ,p_object_version_number => l_trans_obj_vers_num);
212 --
213 l_count := 1;
214 l_transaction_table(l_count).param_name := 'P_TITLE';
215 l_transaction_table(l_count).param_value := p_title;
216 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
217
218 l_count := l_count + 1;
219 l_transaction_table(l_count).param_name := 'P_SUPPLIER';
220 l_transaction_table(l_count).param_value := p_supplier;
221 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
222
223 l_count := l_count + 1;
224 l_transaction_table(l_count).param_name := 'P_EQ_OTA_ACTIVITY';
225 l_transaction_table(l_count).param_value := p_eq_ota_activity;
226 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
227
228 l_count := l_count + 1;
229 l_transaction_table(l_count).param_name := 'P_LOCATION';
230 l_transaction_table(l_count).param_value := p_location;
231 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
232
233 l_count := l_count + 1;
234 l_transaction_table(l_count).param_name := 'P_TRNTYPE';
235 l_transaction_table(l_count).param_value := p_trntype;
236 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
237
238 l_count := l_count + 1;
239 l_transaction_table(l_count).param_name := 'P_DURATION';
240 l_transaction_table(l_count).param_value := p_duration;
241 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
242
243 l_count := l_count + 1;
244 l_transaction_table(l_count).param_name := 'P_DURATION_UNIT';
245 l_transaction_table(l_count).param_value := p_duration_unit;
246 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
247
248 l_count := l_count + 1;
249 l_transaction_table(l_count).param_name := 'P_STATUS';
250 l_transaction_table(l_count).param_value := p_status;
251 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
252
253 l_count := l_count + 1;
254 l_transaction_table(l_count).param_name := 'P_COMPLETION_DATE';
255 l_transaction_table(l_count).param_value := to_char(p_completion_date, hr_transaction_ss.g_date_format);
256 l_transaction_table(l_count).param_data_type := 'DATE';
257
258 l_count := l_count + 1;
259 l_transaction_table(l_count).param_name := 'P_AWARD';
260 l_transaction_table(l_count).param_value := p_award;
261 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
262
263 l_count := l_count + 1;
264 l_transaction_table(l_count).param_name := 'P_SCORE';
265 l_transaction_table(l_count).param_value := p_score;
266 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
267
268 l_count := l_count + 1;
269 l_transaction_table(l_count).param_name := 'P_INTERNAL_CONTACT_PERSON';
270 l_transaction_table(l_count).param_value := p_internal_contact_person;
271 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
272
273 l_count := l_count + 1;
274 l_transaction_table(l_count).param_name := 'P_HISTORYID';
275 l_transaction_table(l_count).param_value := p_historyId;
276 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
277
278 l_count := l_count + 1;
279 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION_CATEGORY';
280 l_transaction_table(l_count).param_value := p_nth_information_category;
281 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
282
283 l_count := l_count + 1;
284 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION1';
285 l_transaction_table(l_count).param_value := p_nth_information1;
286 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
287
288 l_count := l_count + 1;
289 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION2';
290 l_transaction_table(l_count).param_value := p_nth_information2;
291 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
292
293 l_count := l_count + 1;
294 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION3';
295 l_transaction_table(l_count).param_value := p_nth_information3;
296 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
297
298 l_count := l_count + 1;
299 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION4';
300 l_transaction_table(l_count).param_value := p_nth_information4;
301 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
302
303 l_count := l_count + 1;
304 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION5';
305 l_transaction_table(l_count).param_value := p_nth_information5;
306 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
307
308 l_count := l_count + 1;
309 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION6';
310 l_transaction_table(l_count).param_value := p_nth_information6;
311 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
312
313 l_count := l_count + 1;
314 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION7';
315 l_transaction_table(l_count).param_value := p_nth_information7;
316 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
317
318 l_count := l_count + 1;
319 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION8';
320 l_transaction_table(l_count).param_value := p_nth_information8;
321 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
322
323 l_count := l_count + 1;
324 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION9';
325 l_transaction_table(l_count).param_value := p_nth_information9;
326 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
327
328 l_count := l_count + 1;
329 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION10';
330 l_transaction_table(l_count).param_value := p_nth_information10;
331 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
332
333 l_count := l_count + 1;
334 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION11';
335 l_transaction_table(l_count).param_value := p_nth_information11;
336 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
337
338 l_count := l_count + 1;
339 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION12';
340 l_transaction_table(l_count).param_value := p_nth_information12;
341 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
342
343 l_count := l_count + 1;
344 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION13';
345 l_transaction_table(l_count).param_value := p_nth_information13;
346 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
347
348 l_count := l_count + 1;
349 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION14';
350 l_transaction_table(l_count).param_value := p_nth_information14;
351 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
352
353 l_count := l_count + 1;
354 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION15';
355 l_transaction_table(l_count).param_value := p_nth_information15;
356 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
357
358 l_count := l_count + 1;
359 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION16';
360 l_transaction_table(l_count).param_value := p_nth_information16;
361 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
362
363 l_count := l_count + 1;
364 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION17';
365 l_transaction_table(l_count).param_value := p_nth_information17;
366 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
367
368 l_count := l_count + 1;
369 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION18';
370 l_transaction_table(l_count).param_value := p_nth_information18;
371 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
372
373 l_count := l_count + 1;
374 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION19';
375 l_transaction_table(l_count).param_value := p_nth_information19;
376 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
377
378 l_count := l_count + 1;
379 l_transaction_table(l_count).param_name := 'P_NTH_INFORMATION20';
380 l_transaction_table(l_count).param_value := p_nth_information20;
381 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
382
383 l_count := l_count + 1;
384 l_transaction_table(l_count).param_name := 'P_CONTACT_NAME';
385 l_transaction_table(l_count).param_value := p_contact_name;
386 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
387
388 l_count := l_count + 1;
389 l_transaction_table(l_count).param_name := 'P_ACTIVITY_NAME';
390 l_transaction_table(l_count).param_value := p_activity_name;
391 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
392
393 l_count := l_count + 1;
394 l_transaction_table(l_count).param_name := 'P_OBJ_VER_NO';
395 l_transaction_table(l_count).param_value := p_obj_ver_no;
396 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
397
398 l_count := l_count + 1;
399 l_transaction_table(l_count).param_name := 'P_BUSINESS_GRP_ID';
400 l_transaction_table(l_count).param_value := p_business_grp_id;
401 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
402
403 l_count := l_count + 1;
404 l_transaction_table(l_count).param_name := 'P_PERSON_ID';
405 l_transaction_table(l_count).param_value := p_login_person_id;
406 l_transaction_table(l_count).param_data_type := 'NUMBER';
407
408
409 l_count := l_count + 1;
410 l_transaction_table(l_count).param_name := 'P_DELEGATE_PERSON_ID';
411 l_transaction_table(l_count).param_value := p_person_id;
412 l_transaction_table(l_count).param_data_type := 'NUMBER';
413
414 l_count := l_count + 1;
415 l_transaction_table(l_count).param_name := 'P_ORG_ID';
416 l_transaction_table(l_count).param_value := p_org_id;
417 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
418
419 l_review_item_name := wf_engine.GetActivityAttrText(itemtype => p_item_type,
420 itemkey => p_item_key,
421 actid => p_activity_id,
422 aname => gv_wf_review_region_item);
423
424 l_count := l_count + 1;
425 l_transaction_table(l_count).param_name := 'P_REVIEW_PROC_CALL';
426 l_transaction_table(l_count).param_value := l_review_item_name;
427 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
428
429 l_count := l_count + 1;
430 l_transaction_table(l_count).param_name := 'P_REVIEW_ACTID';
431 l_transaction_table(l_count).param_value := p_activity_id;
432 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
433
434 l_count := l_count + 1;
435 l_transaction_table(l_count).param_name := 'P_FROM';
436 l_transaction_table(l_count).param_value := p_from;
437 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
438
439 l_count := l_count + 1;
440 l_transaction_table(l_count).param_name := 'P_TRANSACTION_MODE';
441 l_transaction_table(l_count).param_value := p_transaction_mode;
442 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
443
444 l_count := l_count + 1;
445 l_transaction_table(l_count).param_name := 'P_OAFUNC';
446 l_transaction_table(l_count).param_value := p_oafunc;
447 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
448
449 l_count := l_count + 1;
450 l_transaction_table(l_count).param_name := 'P_PROCESSNAME';
451 l_transaction_table(l_count).param_value := p_processname;
452 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
453
454 l_count := l_count + 1;
455 l_transaction_table(l_count).param_name := 'P_CALLEDFROM';
459 l_count := l_count + 1;
456 l_transaction_table(l_count).param_value := p_calledfrom;
457 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
458
460 l_transaction_table(l_count).param_name := 'P_FROMMENU';
461 l_transaction_table(l_count).param_value := p_frommenu;
462 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
463
464 l_count := l_count + 1;
465 l_transaction_table(l_count).param_name := 'P_STATUS_MEANING';
466 l_transaction_table(l_count).param_value := p_Status_Meaning;
467 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
468
469 l_count := l_count + 1;
470 l_transaction_table(l_count).param_name := 'P_TYPE_MEANING';
471 l_transaction_table(l_count).param_value := p_Type_Meaning;
472 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
473
474 hr_approval_wf.create_item_attrib_if_notexist
475 (p_item_type => p_item_type
476 ,p_item_key => p_item_key
477 ,p_name => 'OTA_TRANSACTION_STEP_ID');
478
479 WF_ENGINE.setitemattrnumber(p_item_type,
480 p_item_key,
481 'OTA_TRANSACTION_STEP_ID',
482 l_transaction_step_id);
483 -- bug 4146681
484 If p_from='REVIEW' Then
485 WF_ENGINE.setitemattrtext(p_item_type,
486 p_item_key,
487 'HR_RESTRICT_EDIT_ATTR',
488 'Y');
489 end if;
490 --bug 4146681
491
492 hr_transaction_ss.save_transaction_step
493 (p_item_type => p_item_type
494 ,p_item_key => p_item_key
495 ,p_actid => p_activity_id
496 ,p_login_person_id => p_login_person_id
497 ,p_transaction_step_id => l_transaction_step_id
498 ,p_api_name => g_package || '.PROCESS_API'
499 ,p_transaction_data => l_transaction_table);
500
501 EXCEPTION
502 WHEN hr_utility.hr_error THEN
503 -- -------------------------------------------
504 -- an application error has been raised so we must
505 -- redisplay the web form to display the error
506 -- --------------------------------------------
507 hr_message.provide_error;
508 l_message_number := hr_message.last_message_number;
509 IF l_message_number = 'APP-7165' OR
510 l_message_number = 'APP-7155' THEN
511 --populate the p_error_message OUT variable
512 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
513 p_error_message => p_error_message,
514 p_attr_name => 'Page',
515 p_app_short_name => 'PER',
516 p_message_name => 'HR_UPDATE_NOT_ALLOWED');
517 ELSE
518 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
519 p_error_message => p_error_message);
520 END IF;
521 WHEN OTHERS THEN
522 p_error_message := 'save add trng'||hr_java_conv_util_ss.get_formatted_error_message(
523 p_error_message => p_error_message);
524
525 END save_add_training;
526
527
528 -- ---------------------------------------------------------------------------
529 -- ---------------------- < get_add_trg_data_from_tt> -------------------------
530 -- ---------------------------------------------------------------------------
531 -- Purpose: This procedure will get transaction data which are saved earlier
532 -- IN the current transaction. This is invoked when a user click BACK
533 -- button to go back from the Review page to Update page to correct
534 -- typos or make further changes or vice-versa. Hence, we need to use
535 -- the item_type item_key passed IN to retrieve the transaction record.
536 -- ---------------------------------------------------------------------------
537 PROCEDURE get_add_trg_data_from_tt (p_item_type IN VARCHAR2
538 ,p_item_key IN VARCHAR2
539 ,p_activity_id IN VARCHAR2
540 ,p_trans_rec_count OUT NOCOPY NUMBER
541 ,p_person_id OUT NOCOPY NUMBER
542 ,p_add_trg_data OUT NOCOPY VARCHAR2)
543 IS
544 l_trans_rec_count INTEGER DEFAULT 0;
545 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
546 l_trans_obj_vers_nums hr_util_web.g_varchar2_tab_type;
547 ln_index NUMBER DEFAULT 0;
548 l_trans_step_rows NUMBER ;
549 l_add_trg_data VARCHAR2(4000);
550
551 BEGIN
552 hr_transaction_api.get_transaction_step_info
553 (p_item_type => p_item_type
554 ,p_item_key => p_item_key
555 ,p_activity_id => p_activity_id
556 ,p_transaction_step_id => l_trans_step_ids
557 ,p_object_version_number => l_trans_obj_vers_nums
558 ,p_rows => l_trans_step_rows);
559
560 get_add_trg_data_from_tt
561 (p_transaction_step_id => l_trans_step_ids(ln_index)
562 ,p_add_trg_data => l_add_trg_data);
563
564 p_add_trg_data := l_add_trg_data;
565
566 EXCEPTION
567 WHEN OTHERS THEN
568 RAISE;
569 END get_add_trg_data_from_tt;
570
571
572 -- ---------------------------------------------------------------------------
573 -- ---------------------- < get_add_trg_data_from_tt> ---------------------
574 -- ---------------------------------------------------------------------------
575 -- Purpose: This procedure will get transaction data which are pending for
576 -- approval in workflow for a transaction step id.
577 -- This is a overloaded version
578 -- ---------------------------------------------------------------------------
579 PROCEDURE get_add_trg_data_from_tt (p_transaction_step_id IN NUMBER
580 ,p_add_trg_data OUT NOCOPY VARCHAR2
581 )IS
582 l_object_version_number per_phones.object_version_number%type;
583 l_title ota_notrng_histories.trng_title%TYPE;
584 l_supplier ota_notrng_histories.provider%TYPE;
585 l_eq_ota_activity VARCHAR2(80);
586 l_location ota_notrng_histories.centre%TYPE;
587 l_trntype ota_notrng_histories.type%TYPE;
588 l_duration VARCHAR2(50);
589 l_duration_unit ota_notrng_histories.duration_units%TYPE;
590 l_status ota_notrng_histories.status%TYPE;
591 l_completion_date DATE;
592 l_award ota_notrng_histories.award%TYPE;
593 l_score ota_notrng_histories.rating%TYPE;
594 l_internal_contact_person VARCHAR2(150);
595 l_historyid VARCHAR2(50);
596 l_nth_information_category ota_notrng_histories.NTH_INFORMATION_CATEGORY%TYPE;
597 l_nth_information1 ota_notrng_histories.NTH_INFORMATION1%TYPE;
598 l_nth_information2 ota_notrng_histories.NTH_INFORMATION2%TYPE;
599 l_nth_information3 ota_notrng_histories.NTH_INFORMATION3%TYPE;
600 l_nth_information4 ota_notrng_histories.NTH_INFORMATION4%TYPE;
601 l_nth_information5 ota_notrng_histories.NTH_INFORMATION5%TYPE;
602 l_nth_information6 ota_notrng_histories.NTH_INFORMATION6%TYPE;
603 l_nth_information7 ota_notrng_histories.NTH_INFORMATION7%TYPE;
604 l_nth_information8 ota_notrng_histories.NTH_INFORMATION8%TYPE;
605 l_nth_information9 ota_notrng_histories.NTH_INFORMATION9%TYPE;
606 l_nth_information10 ota_notrng_histories.NTH_INFORMATION10%TYPE;
607 l_nth_information11 ota_notrng_histories.NTH_INFORMATION11%TYPE;
608 l_nth_information12 ota_notrng_histories.NTH_INFORMATION12%TYPE;
609 l_nth_information13 ota_notrng_histories.NTH_INFORMATION13%TYPE;
610 l_nth_information14 ota_notrng_histories.NTH_INFORMATION14%TYPE;
611 l_nth_information15 ota_notrng_histories.NTH_INFORMATION15%TYPE;
612 l_nth_information16 ota_notrng_histories.NTH_INFORMATION16%TYPE;
613 l_nth_information17 ota_notrng_histories.NTH_INFORMATION17%TYPE;
614 l_nth_information18 ota_notrng_histories.NTH_INFORMATION18%TYPE;
615 l_nth_information19 ota_notrng_histories.NTH_INFORMATION19%TYPE;
616 l_nth_information20 ota_notrng_histories.NTH_INFORMATION20%TYPE;
617 l_contact_name VARCHAR2(80);
618 l_activity_name ota_activity_versions.version_name%TYPE;
619 l_obj_ver_no VARCHAR2(80);
620 l_business_grp_id VARCHAR2(80);
621 l_person_id per_all_people_f.person_id%TYPE;
622 l_var_person_id VARCHAR2(80);
623 l_from VARCHAR2(100);
624 l_org_id VARCHAR2(800);
625 l_org_name hr_all_organization_units.name%TYPE;
626 l_oafunc VARCHAR2(1000);
627 l_processname VARCHAR2(1000);
628 l_calledfrom VARCHAR2(1000);
629 l_frommenu VARCHAR2(1000);
630 l_transaction_mode VARCHAR2(10);
631 l_str_completion_date varchar2(20);
632 l_status_Meaning Varchar2(80);
633 l_Type_Meaning Varchar2(80);
634
635 l_temp varchar2(150);
636 BEGIN
637
638 l_historyid := hr_transaction_api.get_varchar2_value
639 (p_transaction_step_id => p_transaction_step_id
640 ,p_name => 'P_HISTORYID');
641
642 l_title := hr_transaction_api.get_varchar2_value
643 (p_transaction_step_id => p_transaction_step_id
644 ,p_name => 'P_TITLE');
645
646 l_supplier := hr_transaction_api.get_varchar2_value
647 (p_transaction_step_id => p_transaction_step_id
648 ,p_name => 'P_SUPPLIER');
649
650 l_eq_ota_activity := hr_transaction_api.get_varchar2_value
651 (p_transaction_step_id => p_transaction_step_id
652 ,p_name => 'P_EQ_OTA_ACTIVITY');
653
654 l_location := hr_transaction_api.get_varchar2_value
655 (p_transaction_step_id => p_transaction_step_id
656 ,p_name => 'P_LOCATION');
657
658 l_trntype := hr_transaction_api.get_varchar2_value
659 (p_transaction_step_id => p_transaction_step_id
660 ,p_name => 'P_TRNTYPE');
661
662 l_duration := hr_transaction_api.get_varchar2_value
663 (p_transaction_step_id => p_transaction_step_id
664 ,p_name => 'P_DURATION');
665
666 l_duration_unit := hr_transaction_api.get_varchar2_value
667 (p_transaction_step_id => p_transaction_step_id
668 ,p_name => 'P_DURATION_UNIT');
669
670 l_status := hr_transaction_api.get_varchar2_value
671 (p_transaction_step_id => p_transaction_step_id
672 ,p_name => 'P_STATUS');
673
674 l_completion_date := hr_transaction_api.get_date_value
675 (p_transaction_step_id => p_transaction_step_id
676 ,p_name => 'P_COMPLETION_DATE');
677
678 l_award := hr_transaction_api.get_varchar2_value
679 (p_transaction_step_id => p_transaction_step_id
680 ,p_name => 'P_AWARD');
681
682 l_score := hr_transaction_api.get_varchar2_value
683 (p_transaction_step_id => p_transaction_step_id
684 ,p_name => 'P_SCORE');
685
686 l_internal_contact_person := hr_transaction_api.get_varchar2_value
687 (p_transaction_step_id => p_transaction_step_id
688 ,p_name => 'P_INTERNAL_CONTACT_PERSON');
689
690 l_nth_information_category := hr_transaction_api.get_varchar2_value
691 (p_transaction_step_id => p_transaction_step_id
692 ,p_name => 'P_NTH_INFORMATION_CATEGORY');
693
694 l_nth_information1 := hr_transaction_api.get_varchar2_value
695 (p_transaction_step_id => p_transaction_step_id
696 ,p_name => 'P_NTH_INFORMATION1');
697
698 l_nth_information2 := hr_transaction_api.get_varchar2_value
699 (p_transaction_step_id => p_transaction_step_id
700 ,p_name => 'P_NTH_INFORMATION2');
701
702 l_nth_information3 := hr_transaction_api.get_varchar2_value
703 (p_transaction_step_id => p_transaction_step_id
704 ,p_name => 'P_NTH_INFORMATION3');
705
706 l_nth_information4 := hr_transaction_api.get_varchar2_value
707 (p_transaction_step_id => p_transaction_step_id
708 ,p_name => 'P_NTH_INFORMATION4');
709
710 l_nth_information5 := hr_transaction_api.get_varchar2_value
711 (p_transaction_step_id => p_transaction_step_id
712 ,p_name => 'P_NTH_INFORMATION5');
713
714 l_nth_information6 := hr_transaction_api.get_varchar2_value
715 (p_transaction_step_id => p_transaction_step_id
716 ,p_name => 'P_NTH_INFORMATION6');
717
718 l_nth_information7 := hr_transaction_api.get_varchar2_value
719 (p_transaction_step_id => p_transaction_step_id
720 ,p_name => 'P_NTH_INFORMATION7');
721
722 l_nth_information8 := hr_transaction_api.get_varchar2_value
723 (p_transaction_step_id => p_transaction_step_id
724 ,p_name => 'P_NTH_INFORMATION8');
725
726 l_nth_information9 := hr_transaction_api.get_varchar2_value
727 (p_transaction_step_id => p_transaction_step_id
728 ,p_name => 'P_NTH_INFORMATION9');
729
730 l_nth_information10 := hr_transaction_api.get_varchar2_value
731 (p_transaction_step_id => p_transaction_step_id
732 ,p_name => 'P_NTH_INFORMATION10');
733
734 l_nth_information11 := hr_transaction_api.get_varchar2_value
735 (p_transaction_step_id => p_transaction_step_id
736 ,p_name => 'P_NTH_INFORMATION11');
737
738 l_nth_information12 := hr_transaction_api.get_varchar2_value
739 (p_transaction_step_id => p_transaction_step_id
740 ,p_name => 'P_NTH_INFORMATION12');
741
742 l_nth_information13 := hr_transaction_api.get_varchar2_value
743 (p_transaction_step_id => p_transaction_step_id
744 ,p_name => 'P_NTH_INFORMATION13');
745
746 l_nth_information14 := hr_transaction_api.get_varchar2_value
747 (p_transaction_step_id => p_transaction_step_id
748 ,p_name => 'P_NTH_INFORMATION14');
749
750 l_nth_information15 := hr_transaction_api.get_varchar2_value
751 (p_transaction_step_id => p_transaction_step_id
752 ,p_name => 'P_NTH_INFORMATION15');
753
754 l_nth_information16 := hr_transaction_api.get_varchar2_value
755 (p_transaction_step_id => p_transaction_step_id
756 ,p_name => 'P_NTH_INFORMATION16');
757
758 l_nth_information17 := hr_transaction_api.get_varchar2_value
759 (p_transaction_step_id => p_transaction_step_id
760 ,p_name => 'P_NTH_INFORMATION17');
761
762 l_nth_information18 := hr_transaction_api.get_varchar2_value
763 (p_transaction_step_id => p_transaction_step_id
764 ,p_name => 'P_NTH_INFORMATION18');
765
766 l_nth_information19 := hr_transaction_api.get_varchar2_value
767 (p_transaction_step_id => p_transaction_step_id
768 ,p_name => 'P_NTH_INFORMATION19');
769
770 l_nth_information20 := hr_transaction_api.get_varchar2_value
771 (p_transaction_step_id => p_transaction_step_id
772 ,p_name => 'P_NTH_INFORMATION20');
773
774 l_contact_name := hr_transaction_api.get_varchar2_value
775 (p_transaction_step_id => p_transaction_step_id
776 ,p_name => 'P_CONTACT_NAME');
777
778 l_activity_name := hr_transaction_api.get_varchar2_value
779 (p_transaction_step_id => p_transaction_step_id
780 ,p_name => 'P_ACTIVITY_NAME');
781
782 l_obj_ver_no := hr_transaction_api.get_varchar2_value
783 (p_transaction_step_id => p_transaction_step_id
784 ,p_name => 'P_OBJ_VER_NO');
785
786 l_business_grp_id := hr_transaction_api.get_varchar2_value
787 (p_transaction_step_id => p_transaction_step_id
788 ,p_name => 'P_BUSINESS_GRP_ID');
789
790 l_person_id := hr_transaction_api.get_number_value
791 (p_transaction_step_id => p_transaction_step_id
792 ,p_name => 'P_DELEGATE_PERSON_ID');
793
794 l_from := hr_transaction_api.get_varchar2_value
795 (p_transaction_step_id => p_transaction_step_id
796 ,p_name => 'P_FROM');
797
798 l_transaction_mode := hr_transaction_api.get_varchar2_value
799 (p_transaction_step_id => p_transaction_step_id
800 ,p_name => 'P_TRANSACTION_MODE');
801
802 l_oafunc := hr_transaction_api.get_varchar2_value
803 (p_transaction_step_id => p_transaction_step_id
804 ,p_name => 'P_OAFUNC');
805
806 l_processname := hr_transaction_api.get_varchar2_value
807 (p_transaction_step_id => p_transaction_step_id
808 ,p_name => 'P_PROCESSNAME');
809
810 l_calledfrom := hr_transaction_api.get_varchar2_value
811 (p_transaction_step_id => p_transaction_step_id
812 ,p_name => 'P_CALLEDFROM');
813
814 l_frommenu := hr_transaction_api.get_varchar2_value
815 (p_transaction_step_id => p_transaction_step_id
816 ,p_name => 'P_FROMMENU');
817
818 l_status_Meaning := hr_transaction_api.get_varchar2_value
819 (p_transaction_step_id => p_transaction_step_id
820 ,p_name => 'P_STATUS_MEANING');
821
822 l_Type_Meaning := hr_transaction_api.get_varchar2_value
823 (p_transaction_step_id => p_transaction_step_id
824 ,p_name => 'P_TYPE_MEANING');
825
826 l_str_completion_date := to_char(l_completion_date, fnd_profile.value('ICX_DATE_FORMAT_MASK'));
827
828
829 -- Now string all the retreived items into p_add_trg_data
830 --
831 p_add_trg_data := nvl(l_historyid,'null')
832 ||'^'||nvl(l_title,'null')
833 ||'^'||nvl(l_supplier,'null')
834 ||'^'||nvl(l_eq_ota_activity,'null')
835 ||'^'||nvl(l_location,'null')
836 ||'^'||nvl(l_trntype,'null')
837 ||'^'||nvl(l_duration,'null')
838 ||'^'||nvl(l_duration_unit,'null')
839 ||'^'||nvl(l_status,'null')
840 ||'^'||nvl(l_str_completion_date,'null')
841 ||'^'||nvl(l_award,'null')
842 ||'^'||nvl(l_score,'null')
843 ||'^'||nvl(l_internal_contact_person,'null')
844 ||'^'||nvl(l_nth_information_category,'null')
845 ||'^'||nvl(l_nth_information1,'null')
846 ||'^'||nvl(l_nth_information2,'null')
847 ||'^'||nvl(l_nth_information3,'null')
848 ||'^'||nvl(l_nth_information4,'null')
849 ||'^'||nvl(l_nth_information5,'null')
850 ||'^'||nvl(l_nth_information6,'null')
851 ||'^'||nvl(l_nth_information7,'null')
852 ||'^'||nvl(l_nth_information8,'null')
853 ||'^'||nvl(l_nth_information9,'null')
854 ||'^'||nvl(l_nth_information10,'null')
855 ||'^'||nvl(l_nth_information11,'null')
856 ||'^'||nvl(l_nth_information12,'null')
857 ||'^'||nvl(l_nth_information13,'null')
858 ||'^'||nvl(l_nth_information14,'null')
859 ||'^'||nvl(l_nth_information15,'null')
860 ||'^'||nvl(l_nth_information16,'null')
861 ||'^'||nvl(l_nth_information17,'null')
862 ||'^'||nvl(l_nth_information18,'null')
863 ||'^'||nvl(l_nth_information19,'null')
864 ||'^'||nvl(l_nth_information20,'null')
865 ||'^'||nvl(l_contact_name,'null')
866 ||'^'||nvl(l_activity_name,'null')
867 ||'^'||nvl(l_obj_ver_no,'null')
868 ||'^'||nvl(l_business_grp_id,'null')
869 ||'^'||nvl(l_person_id,0)
870 ||'^'||nvl(l_from,'null')
871 ||'^'||nvl(l_oafunc,'null')
872 ||'^'||nvl(l_processname,'null')
873 ||'^'||nvl(l_calledfrom,'null')
874 ||'^'||nvl(l_frommenu,'null')
875 ||'^'||nvl(l_transaction_mode,'null')
876 ||'^'||nvl(l_status_Meaning,'null')
877 ||'^'||nvl(l_Type_Meaning,'null');
878
879 EXCEPTION
880 WHEN OTHERS THEN
881 RAISE;
882 END get_add_trg_data_from_tt;
883
884 PROCEDURE get_pending_transaction_data
885 (p_processname IN VARCHAR2,
886 p_item_type IN VARCHAR2,
887 p_person_id IN NUMBER,
888 p_exclude_historyid OUT NOCOPY VARCHAR2,
889 p_transaction_step_ids OUT NOCOPY VARCHAR2) IS
890
891 l_token varchar2(3) := null;
892 l_token2 varchar2(3) := null;
893 l_exclude_historyid VARCHAR2(2000):= null;
894 l_temp_historyid VARCHAR2(2000):= null;
895 l_transaction_step_ids VARCHAR2(2000):= null;
896
897
898 Cursor cur_get_pending_trn_step_id IS
899 Select
900 hrtrns.transaction_step_id
901 From
902 wf_item_activity_statuses process
903 ,wf_item_attribute_values attribute2
904 ,wf_process_activities activity
905 ,hr_api_transaction_steps hrtrns
906 Where
907 activity.activity_name = p_processname
908 and activity.process_item_type = p_item_type
909 and activity.activity_item_type = p_item_type
910 and activity.instance_id = process.process_activity
911 and process.activity_status = 'ACTIVE'
912 and process.item_type = p_item_type
913 and hrtrns.update_person_id = p_person_id
914 and process.item_key = attribute2.item_key
915 and attribute2.item_type = process.item_type
916 and attribute2.name = 'TRAN_SUBMIT'
917 and attribute2.text_value = 'Y'
918 and process.item_key = hrtrns.item_key
919 and trim(upper(hrtrns.api_name)) = trim(upper(g_package ||'.PROCESS_API'))
920 and hrtrns.item_type = p_item_type;
921
922
923
924
925
926
927 BEGIN
928
929 FOR c in cur_get_pending_trn_step_id --Bug 3590613
930 LOOP
931
932 l_temp_historyid :=
933 hr_transaction_api.get_varchar2_value
934 (p_transaction_step_id => c.transaction_step_id
935 ,p_name => 'P_HISTORYID');
936
937 IF l_temp_historyid is not null then
938 l_exclude_historyid := l_exclude_historyid || l_token || l_temp_historyid;
939 l_token:= ',';
940 END IF;
941
942 l_transaction_step_ids := l_transaction_step_ids || l_token2 ||c.transaction_step_id;
943 l_token2 := ',';
944 END LOOP;
945
946 p_transaction_step_ids := l_transaction_step_ids;
947 p_exclude_historyid := l_exclude_historyid;
948
949
950 EXCEPTION
951 WHEN OTHERS THEN
952
953 RAISE;
954 END;
955
956
957
958
959
960 /*PROCEDURE process_api
961 (p_validate IN BOOLEAN ,p_transaction_step_id IN NUMBER) IS */
962 PROCEDURE process_api
963 (p_validate IN BOOLEAN ,p_transaction_step_id IN NUMBER
964 ,p_effective_date in varchar2) IS
965 l_transaction_mode VARCHAR2(10);
966 l_from VARCHAR2(20);
967 l_tran_submitted VARCHAR2(1);
968
969 l_item_type HR_API_TRANSACTION_STEPS.ITEM_TYPE%TYPE;
970 l_item_key HR_API_TRANSACTION_STEPS.ITEM_KEY%TYPE;
971 l_activity_id HR_API_TRANSACTION_STEPS.ACTIVITY_ID%TYPE;
972
973
974
975 BEGIN
976 l_transaction_mode := hr_transaction_api.get_varchar2_value
977 (p_transaction_step_id => p_transaction_step_id
978 ,p_name => 'P_TRANSACTION_MODE');
979 l_from := hr_transaction_api.get_varchar2_value
980 (p_transaction_step_id => p_transaction_step_id
981 ,p_name => 'P_FROM');
982
983 hr_transaction_api.get_transaction_step_info
984 (p_transaction_step_id => p_transaction_step_id
985 ,p_item_type => l_item_type
986 ,p_item_key => l_item_key
987 ,p_activity_id => l_activity_id);
988
989
990 l_tran_submitted := wf_engine.GetItemAttrtext(itemtype => l_item_type
991 ,itemkey => l_item_key
992 ,aname => 'TRAN_SUBMIT');
993
994 If (l_from = 'REVIEW') Then
995 g_called_from := 'R' ;
996 End if;
997
998 If (l_tran_submitted <> 'Y') then
999 savepoint validate_add_training;
1000 If (l_transaction_mode = 'INSERT') Then
1001 create_add_training_tt(p_validate => true, p_transaction_step_id => p_transaction_step_id);
1002 ElsIf (l_transaction_mode = 'UPDATE') Then
1003 update_add_training_tt(p_validate => true, p_transaction_step_id => p_transaction_step_id);
1004 End If;
1005 rollback to validate_add_training;
1006 Else
1007 If (l_transaction_mode = 'INSERT') Then
1008 create_add_training_tt(p_validate => p_validate, p_transaction_step_id => p_transaction_step_id);
1009 ElsIf (l_transaction_mode = 'UPDATE') Then
1010 update_add_training_tt(p_validate => p_validate, p_transaction_step_id => p_transaction_step_id);
1011 End If;
1012 End If;
1013
1014
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017
1018 RAISE;
1019 END process_api;
1020
1021
1022 -- ---------------------------------------------------------------------------
1023 -- ---------------------- < create_add_training_tt > ---------------------
1024 -- ---------------------------------------------------------------------------
1025 -- Purpose: This procedure will get transaction data which are pending for
1026 -- approval in workflow for a transaction step id and creates
1027 -- a additional training record.
1028 -- ---------------------------------------------------------------------------
1029
1030 procedure create_add_training_tt
1031 (p_validate IN BOOLEAN, p_transaction_step_id IN NUMBER)
1032 is
1033 l_title ota_notrng_histories.trng_title%TYPE;
1034 l_supplier ota_notrng_histories.provider%TYPE;
1035 l_eq_ota_activity VARCHAR2(800);
1036 l_location ota_notrng_histories.centre%TYPE;
1037 l_trntype ota_notrng_histories.type%TYPE;
1038 l_duration VARCHAR2(500);
1039 l_duration_unit ota_notrng_histories.duration_units%TYPE;
1040 l_status ota_notrng_histories.status%TYPE;
1041 l_completion_date DATE;
1042 l_award ota_notrng_histories.award%TYPE;
1043 l_score ota_notrng_histories.rating%TYPE;
1044 l_internal_contact_person VARCHAR2(500);
1045 l_historyid VARCHAR2(500);
1046 l_nth_information_category ota_notrng_histories.NTH_INFORMATION_CATEGORY%TYPE;
1047 l_nth_information1 ota_notrng_histories.NTH_INFORMATION1%TYPE;
1048 l_nth_information2 ota_notrng_histories.NTH_INFORMATION2%TYPE;
1049 l_nth_information3 ota_notrng_histories.NTH_INFORMATION3%TYPE;
1050 l_nth_information4 ota_notrng_histories.NTH_INFORMATION4%TYPE;
1051 l_nth_information5 ota_notrng_histories.NTH_INFORMATION5%TYPE;
1052 l_nth_information6 ota_notrng_histories.NTH_INFORMATION6%TYPE;
1053 l_nth_information7 ota_notrng_histories.NTH_INFORMATION7%TYPE;
1054 l_nth_information8 ota_notrng_histories.NTH_INFORMATION8%TYPE;
1055 l_nth_information9 ota_notrng_histories.NTH_INFORMATION9%TYPE;
1056 l_nth_information10 ota_notrng_histories.NTH_INFORMATION10%TYPE;
1057 l_nth_information11 ota_notrng_histories.NTH_INFORMATION11%TYPE;
1058 l_nth_information12 ota_notrng_histories.NTH_INFORMATION12%TYPE;
1059 l_nth_information13 ota_notrng_histories.NTH_INFORMATION13%TYPE;
1060 l_nth_information14 ota_notrng_histories.NTH_INFORMATION14%TYPE;
1061 l_nth_information15 ota_notrng_histories.NTH_INFORMATION15%TYPE;
1062 l_nth_information16 ota_notrng_histories.NTH_INFORMATION16%TYPE;
1063 l_nth_information17 ota_notrng_histories.NTH_INFORMATION17%TYPE;
1064 l_nth_information18 ota_notrng_histories.NTH_INFORMATION18%TYPE;
1065 l_nth_information19 ota_notrng_histories.NTH_INFORMATION19%TYPE;
1066 l_nth_information20 ota_notrng_histories.NTH_INFORMATION20%TYPE;
1067 l_contact_name VARCHAR2(800);
1068 l_activity_name ota_activity_versions.version_name%TYPE;
1069 l_obj_ver_no VARCHAR2(800);
1070 l_business_grp_id VARCHAR2(800);
1071 l_person_id per_all_people_f.person_id%TYPE;
1072 l_org_id VARCHAR2(800);
1073 l_transaction_mode VARCHAR2(10);
1074 l_some_warning NUMBER := 0;
1075 l_from VARCHAR2(100);
1076 l_message VARCHAR2(1000) := NULL;
1077
1078 l_item_type HR_API_TRANSACTION_STEPS.ITEM_TYPE%TYPE;
1079 l_item_key HR_API_TRANSACTION_STEPS.ITEM_KEY%TYPE;
1080 l_activity_id HR_API_TRANSACTION_STEPS.ACTIVITY_ID%TYPE;
1081 BEGIN
1082
1083
1084 hr_transaction_api.get_transaction_step_info
1085 (p_transaction_step_id => p_transaction_step_id
1086 ,p_item_type => l_item_type
1087 ,p_item_key => l_item_key
1088 ,p_activity_id => l_activity_id);
1089
1090 SAVEPOINT create_tt;
1091 l_historyid := hr_transaction_api.get_varchar2_value
1092 (p_transaction_step_id => p_transaction_step_id
1093 ,p_name => 'P_HISTORYID');
1094 l_title := hr_transaction_api.get_varchar2_value
1095 (p_transaction_step_id => p_transaction_step_id
1096 ,p_name => 'P_TITLE');
1097 l_supplier := hr_transaction_api.get_varchar2_value
1098 (p_transaction_step_id => p_transaction_step_id
1099 ,p_name => 'P_SUPPLIER');
1103 l_location := hr_transaction_api.get_varchar2_value
1100 l_eq_ota_activity := hr_transaction_api.get_varchar2_value
1101 (p_transaction_step_id => p_transaction_step_id
1102 ,p_name => 'P_EQ_OTA_ACTIVITY');
1104 (p_transaction_step_id => p_transaction_step_id
1105 ,p_name => 'P_LOCATION');
1106 l_trntype := hr_transaction_api.get_varchar2_value
1107 (p_transaction_step_id => p_transaction_step_id
1108 ,p_name => 'P_TRNTYPE');
1109 l_duration := hr_transaction_api.get_varchar2_value
1110 (p_transaction_step_id => p_transaction_step_id
1111 ,p_name => 'P_DURATION');
1112 l_duration_unit := hr_transaction_api.get_varchar2_value
1113 (p_transaction_step_id => p_transaction_step_id
1114 ,p_name => 'P_DURATION_UNIT');
1115 l_status := hr_transaction_api.get_varchar2_value
1116 (p_transaction_step_id => p_transaction_step_id
1117 ,p_name => 'P_STATUS');
1118 l_completion_date := hr_transaction_api.get_date_value
1119 (p_transaction_step_id => p_transaction_step_id
1120 ,p_name => 'P_COMPLETION_DATE');
1121 l_award := hr_transaction_api.get_varchar2_value
1122 (p_transaction_step_id => p_transaction_step_id
1123 ,p_name => 'P_AWARD');
1124 l_score := hr_transaction_api.get_varchar2_value
1125 (p_transaction_step_id => p_transaction_step_id
1126 ,p_name => 'P_SCORE');
1127 l_internal_contact_person := hr_transaction_api.get_varchar2_value
1128 (p_transaction_step_id => p_transaction_step_id
1129 ,p_name => 'P_INTERNAL_CONTACT_PERSON');
1130 l_nth_information_category := hr_transaction_api.get_varchar2_value
1131 (p_transaction_step_id => p_transaction_step_id
1132 ,p_name => 'P_NTH_INFORMATION_CATEGORY');
1133 l_nth_information1 := hr_transaction_api.get_varchar2_value
1134 (p_transaction_step_id => p_transaction_step_id
1135 ,p_name => 'P_NTH_INFORMATION1');
1136 l_nth_information2 := hr_transaction_api.get_varchar2_value
1137 (p_transaction_step_id => p_transaction_step_id
1138 ,p_name => 'P_NTH_INFORMATION2');
1139 l_nth_information3 := hr_transaction_api.get_varchar2_value
1140 (p_transaction_step_id => p_transaction_step_id
1141 ,p_name => 'P_NTH_INFORMATION3');
1142 l_nth_information4 := hr_transaction_api.get_varchar2_value
1143 (p_transaction_step_id => p_transaction_step_id
1144 ,p_name => 'P_NTH_INFORMATION4');
1145 l_nth_information5 := hr_transaction_api.get_varchar2_value
1146 (p_transaction_step_id => p_transaction_step_id
1147 ,p_name => 'P_NTH_INFORMATION5');
1148 l_nth_information6 := hr_transaction_api.get_varchar2_value
1149 (p_transaction_step_id => p_transaction_step_id
1150 ,p_name => 'P_NTH_INFORMATION6');
1151 l_nth_information7 := hr_transaction_api.get_varchar2_value
1152 (p_transaction_step_id => p_transaction_step_id
1153 ,p_name => 'P_NTH_INFORMATION7');
1154 l_nth_information8 := hr_transaction_api.get_varchar2_value
1155 (p_transaction_step_id => p_transaction_step_id
1156 ,p_name => 'P_NTH_INFORMATION8');
1157 l_nth_information9 := hr_transaction_api.get_varchar2_value
1158 (p_transaction_step_id => p_transaction_step_id
1159 ,p_name => 'P_NTH_INFORMATION9');
1160 l_nth_information10 := hr_transaction_api.get_varchar2_value
1161 (p_transaction_step_id => p_transaction_step_id
1162 ,p_name => 'P_NTH_INFORMATION10');
1163 l_nth_information11 := hr_transaction_api.get_varchar2_value
1164 (p_transaction_step_id => p_transaction_step_id
1165 ,p_name => 'P_NTH_INFORMATION11');
1166 l_nth_information12 := hr_transaction_api.get_varchar2_value
1167 (p_transaction_step_id => p_transaction_step_id
1168 ,p_name => 'P_NTH_INFORMATION12');
1169 l_nth_information13 := hr_transaction_api.get_varchar2_value
1170 (p_transaction_step_id => p_transaction_step_id
1171 ,p_name => 'P_NTH_INFORMATION13');
1172 l_nth_information14 := hr_transaction_api.get_varchar2_value
1173 (p_transaction_step_id => p_transaction_step_id
1174 ,p_name => 'P_NTH_INFORMATION14');
1175 l_nth_information15 := hr_transaction_api.get_varchar2_value
1176 (p_transaction_step_id => p_transaction_step_id
1177 ,p_name => 'P_NTH_INFORMATION15');
1178 l_nth_information16 := hr_transaction_api.get_varchar2_value
1179 (p_transaction_step_id => p_transaction_step_id
1180 ,p_name => 'P_NTH_INFORMATION16');
1181 l_nth_information17 := hr_transaction_api.get_varchar2_value
1182 (p_transaction_step_id => p_transaction_step_id
1183 ,p_name => 'P_NTH_INFORMATION17');
1184 l_nth_information18 := hr_transaction_api.get_varchar2_value
1185 (p_transaction_step_id => p_transaction_step_id
1186 ,p_name => 'P_NTH_INFORMATION18');
1187 l_nth_information19 := hr_transaction_api.get_varchar2_value
1188 (p_transaction_step_id => p_transaction_step_id
1189 ,p_name => 'P_NTH_INFORMATION19');
1190 l_nth_information20 := hr_transaction_api.get_varchar2_value
1191 (p_transaction_step_id => p_transaction_step_id
1192 ,p_name => 'P_NTH_INFORMATION20');
1193 l_contact_name := hr_transaction_api.get_varchar2_value
1194 (p_transaction_step_id => p_transaction_step_id
1195 ,p_name => 'P_CONTACT_NAME');
1196 l_activity_name := hr_transaction_api.get_varchar2_value
1200 (p_transaction_step_id => p_transaction_step_id
1197 (p_transaction_step_id => p_transaction_step_id
1198 ,p_name => 'P_ACTIVITY_NAME');
1199 l_obj_ver_no := hr_transaction_api.get_varchar2_value
1201 ,p_name => 'P_OBJ_VER_NO');
1202 l_business_grp_id := hr_transaction_api.get_varchar2_value
1203 (p_transaction_step_id => p_transaction_step_id
1204 ,p_name => 'P_BUSINESS_GRP_ID');
1205 l_person_id := hr_transaction_api.get_number_value
1206 (p_transaction_step_id => p_transaction_step_id
1207 ,p_name => 'P_DELEGATE_PERSON_ID');
1208
1209 l_org_id := hr_transaction_api.get_varchar2_value
1210 (p_transaction_step_id => p_transaction_step_id
1211 ,p_name => 'P_ORG_ID');
1212
1213 create_add_training
1214 (p_effective_date => SYSDATE
1215 ,p_nota_history_id => l_historyid
1216 ,p_person_id => l_person_id
1217 ,p_contact_id => l_internal_contact_person
1218 ,p_trng_title => l_title
1219 ,p_provider => l_supplier
1220 ,p_type => l_trntype
1221 ,p_centre => l_location
1222 ,p_completion_date => l_completion_date
1223 ,p_award => l_award
1224 ,p_rating => l_score
1225 ,p_duration => l_duration
1226 ,p_duration_units => l_duration_unit
1227 ,p_activity_version_id => l_eq_ota_activity
1228 ,p_status => l_status
1229 ,p_verified_by_id => NULL
1230 ,p_nth_information_category => l_nth_information_category
1231 ,p_nth_information1 => l_nth_information1
1232 ,p_nth_information2 => l_nth_information2
1233 ,p_nth_information3 => l_nth_information3
1234 ,p_nth_information4 => l_nth_information4
1235 ,p_nth_information5 => l_nth_information5
1236 ,p_nth_information6 => l_nth_information6
1237 ,p_nth_information7 => l_nth_information7
1238 ,p_nth_information8 => l_nth_information8
1239 ,p_nth_information9 => l_nth_information9
1240 ,p_nth_information10 => l_nth_information10
1241 ,p_nth_information11 => l_nth_information11
1242 ,p_nth_information12 => l_nth_information12
1243 ,p_nth_information13 => l_nth_information13
1244 ,p_nth_information15 => l_nth_information15
1245 ,p_nth_information16 => l_nth_information16
1246 ,p_nth_information17 => l_nth_information17
1247 ,p_nth_information18 => l_nth_information18
1248 ,p_nth_information19 => l_nth_information19
1249 ,p_nth_information20 => l_nth_information20
1250 ,p_org_id => NULL
1251 ,p_object_version_number => l_obj_ver_no
1252 ,p_business_group_id => l_business_grp_id
1253 ,p_nth_information14 => l_nth_information14
1254 ,p_customer_id => NULL
1255 ,p_organization_id => l_org_id
1256 ,p_some_warning => l_some_warning
1257 ,p_message => l_message
1258 ,p_item_type => l_item_type
1259 ,p_item_key => l_item_key
1260 );
1261 EXCEPTION
1262 WHEN OTHERS THEN
1263 rollback to create_tt;
1264 RAISE;
1265 END create_add_training_tt;
1266
1267
1268 -- ---------------------------------------------------------------------------
1269 -- ---------------------- < update_add_training_tt > ---------------------
1270 -- ---------------------------------------------------------------------------
1271 -- Purpose: This procedure will get transaction data which are pending for
1272 -- approval in workflow for a transaction step id and updates
1273 -- corresponding additional training record.
1274 -- ---------------------------------------------------------------------------
1275 procedure update_add_training_tt
1276 (p_validate IN BOOLEAN, p_transaction_step_id IN NUMBER)
1277 IS
1278 l_title ota_notrng_histories.trng_title%TYPE;
1279 l_supplier ota_notrng_histories.provider%TYPE;
1280 l_eq_ota_activity VARCHAR2(800);
1281 l_location ota_notrng_histories.centre%TYPE;
1282 l_trntype ota_notrng_histories.type%TYPE;
1283 l_duration VARCHAR2(500);
1284 l_duration_unit ota_notrng_histories.duration_units%TYPE;
1285 l_status ota_notrng_histories.status%TYPE;
1286 l_completion_date DATE;
1287 l_award ota_notrng_histories.award%TYPE;
1288 l_score ota_notrng_histories.rating%TYPE;
1289 l_internal_contact_person VARCHAR2(500);
1290 l_historyid VARCHAR2(500);
1291 l_nth_information_category ota_notrng_histories.NTH_INFORMATION_CATEGORY%TYPE;
1292 l_nth_information1 ota_notrng_histories.NTH_INFORMATION1%TYPE;
1293 l_nth_information2 ota_notrng_histories.NTH_INFORMATION2%TYPE;
1294 l_nth_information3 ota_notrng_histories.NTH_INFORMATION3%TYPE;
1295 l_nth_information4 ota_notrng_histories.NTH_INFORMATION4%TYPE;
1296 l_nth_information5 ota_notrng_histories.NTH_INFORMATION5%TYPE;
1297 l_nth_information6 ota_notrng_histories.NTH_INFORMATION6%TYPE;
1298 l_nth_information7 ota_notrng_histories.NTH_INFORMATION7%TYPE;
1299 l_nth_information8 ota_notrng_histories.NTH_INFORMATION8%TYPE;
1300 l_nth_information9 ota_notrng_histories.NTH_INFORMATION9%TYPE;
1301 l_nth_information10 ota_notrng_histories.NTH_INFORMATION10%TYPE;
1302 l_nth_information11 ota_notrng_histories.NTH_INFORMATION11%TYPE;
1303 l_nth_information12 ota_notrng_histories.NTH_INFORMATION12%TYPE;
1304 l_nth_information13 ota_notrng_histories.NTH_INFORMATION13%TYPE;
1305 l_nth_information14 ota_notrng_histories.NTH_INFORMATION14%TYPE;
1306 l_nth_information15 ota_notrng_histories.NTH_INFORMATION15%TYPE;
1307 l_nth_information16 ota_notrng_histories.NTH_INFORMATION16%TYPE;
1308 l_nth_information17 ota_notrng_histories.NTH_INFORMATION17%TYPE;
1309 l_nth_information18 ota_notrng_histories.NTH_INFORMATION18%TYPE;
1310 l_nth_information19 ota_notrng_histories.NTH_INFORMATION19%TYPE;
1311 l_nth_information20 ota_notrng_histories.NTH_INFORMATION20%TYPE;
1312 l_contact_name VARCHAR2(800);
1313 l_activity_name ota_activity_versions.version_name%TYPE;
1314 l_old_obj_ver_no VARCHAR2(800);
1315 l_new_obj_ver_no VARCHAR2(800) := NULL;
1316 l_business_grp_id VARCHAR2(800);
1317 l_person_id per_all_people_f.person_id%TYPE;
1318 l_org_id VARCHAR2(800);
1319 l_transaction_mode VARCHAR2(10);
1320 l_some_warning NUMBER := 0;
1321 l_from VARCHAR2(100);
1322 l_message VARCHAR2(1000) := NULL;
1323
1324 l_item_type HR_API_TRANSACTION_STEPS.ITEM_TYPE%TYPE;
1325 l_item_key HR_API_TRANSACTION_STEPS.ITEM_KEY%TYPE;
1326 l_activity_id HR_API_TRANSACTION_STEPS.ACTIVITY_ID%TYPE;
1327 l_approval_reg_flag wf_lookups.lookup_code%type;
1328
1329 BEGIN
1330
1331
1332 hr_transaction_api.get_transaction_step_info
1333 (p_transaction_step_id => p_transaction_step_id
1334 ,p_item_type => l_item_type
1335 ,p_item_key => l_item_key
1336 ,p_activity_id => l_activity_id);
1337
1338 SAVEPOINT update_tt;
1339 l_historyid := hr_transaction_api.get_varchar2_value
1340 (p_transaction_step_id => p_transaction_step_id
1341 ,p_name => 'P_HISTORYID');
1342 l_title := hr_transaction_api.get_varchar2_value
1343 (p_transaction_step_id => p_transaction_step_id
1344 ,p_name => 'P_TITLE');
1345 l_supplier := hr_transaction_api.get_varchar2_value
1346 (p_transaction_step_id => p_transaction_step_id
1347 ,p_name => 'P_SUPPLIER');
1348 l_eq_ota_activity := hr_transaction_api.get_varchar2_value
1349 (p_transaction_step_id => p_transaction_step_id
1350 ,p_name => 'P_EQ_OTA_ACTIVITY');
1351 l_location := hr_transaction_api.get_varchar2_value
1352 (p_transaction_step_id => p_transaction_step_id
1353 ,p_name => 'P_LOCATION');
1354 l_trntype := hr_transaction_api.get_varchar2_value
1355 (p_transaction_step_id => p_transaction_step_id
1356 ,p_name => 'P_TRNTYPE');
1357 l_duration := hr_transaction_api.get_varchar2_value
1358 (p_transaction_step_id => p_transaction_step_id
1359 ,p_name => 'P_DURATION');
1360 l_duration_unit := hr_transaction_api.get_varchar2_value
1361 (p_transaction_step_id => p_transaction_step_id
1362 ,p_name => 'P_DURATION_UNIT');
1363 l_status := hr_transaction_api.get_varchar2_value
1364 (p_transaction_step_id => p_transaction_step_id
1365 ,p_name => 'P_STATUS');
1366 l_completion_date := hr_transaction_api.get_Date_value
1367 (p_transaction_step_id => p_transaction_step_id
1368 ,p_name => 'P_COMPLETION_DATE');
1369 l_award := hr_transaction_api.get_varchar2_value
1370 (p_transaction_step_id => p_transaction_step_id
1371 ,p_name => 'P_AWARD');
1372 l_score := hr_transaction_api.get_varchar2_value
1373 (p_transaction_step_id => p_transaction_step_id
1374 ,p_name => 'P_SCORE');
1375 l_internal_contact_person := hr_transaction_api.get_varchar2_value
1376 (p_transaction_step_id => p_transaction_step_id
1377 ,p_name => 'P_INTERNAL_CONTACT_PERSON');
1378 l_nth_information_category := hr_transaction_api.get_varchar2_value
1379 (p_transaction_step_id => p_transaction_step_id
1380 ,p_name => 'P_NTH_INFORMATION_CATEGORY');
1381 l_nth_information1 := hr_transaction_api.get_varchar2_value
1382 (p_transaction_step_id => p_transaction_step_id
1383 ,p_name => 'P_NTH_INFORMATION1');
1384 l_nth_information2 := hr_transaction_api.get_varchar2_value
1385 (p_transaction_step_id => p_transaction_step_id
1386 ,p_name => 'P_NTH_INFORMATION2');
1387 l_nth_information3 := hr_transaction_api.get_varchar2_value
1388 (p_transaction_step_id => p_transaction_step_id
1389 ,p_name => 'P_NTH_INFORMATION3');
1390 l_nth_information4 := hr_transaction_api.get_varchar2_value
1391 (p_transaction_step_id => p_transaction_step_id
1392 ,p_name => 'P_NTH_INFORMATION4');
1393 l_nth_information5 := hr_transaction_api.get_varchar2_value
1394 (p_transaction_step_id => p_transaction_step_id
1395 ,p_name => 'P_NTH_INFORMATION5');
1396 l_nth_information6 := hr_transaction_api.get_varchar2_value
1397 (p_transaction_step_id => p_transaction_step_id
1398 ,p_name => 'P_NTH_INFORMATION6');
1399 l_nth_information7 := hr_transaction_api.get_varchar2_value
1400 (p_transaction_step_id => p_transaction_step_id
1401 ,p_name => 'P_NTH_INFORMATION7');
1402 l_nth_information8 := hr_transaction_api.get_varchar2_value
1403 (p_transaction_step_id => p_transaction_step_id
1404 ,p_name => 'P_NTH_INFORMATION8');
1405 l_nth_information9 := hr_transaction_api.get_varchar2_value
1406 (p_transaction_step_id => p_transaction_step_id
1407 ,p_name => 'P_NTH_INFORMATION9');
1408 l_nth_information10 := hr_transaction_api.get_varchar2_value
1409 (p_transaction_step_id => p_transaction_step_id
1410 ,p_name => 'P_NTH_INFORMATION10');
1411 l_nth_information11 := hr_transaction_api.get_varchar2_value
1412 (p_transaction_step_id => p_transaction_step_id
1413 ,p_name => 'P_NTH_INFORMATION11');
1414 l_nth_information12 := hr_transaction_api.get_varchar2_value
1415 (p_transaction_step_id => p_transaction_step_id
1416 ,p_name => 'P_NTH_INFORMATION12');
1417 l_nth_information13 := hr_transaction_api.get_varchar2_value
1418 (p_transaction_step_id => p_transaction_step_id
1419 ,p_name => 'P_NTH_INFORMATION13');
1420 l_nth_information14 := hr_transaction_api.get_varchar2_value
1421 (p_transaction_step_id => p_transaction_step_id
1422 ,p_name => 'P_NTH_INFORMATION14');
1423 l_nth_information15 := hr_transaction_api.get_varchar2_value
1424 (p_transaction_step_id => p_transaction_step_id
1425 ,p_name => 'P_NTH_INFORMATION15');
1426 l_nth_information16 := hr_transaction_api.get_varchar2_value
1427 (p_transaction_step_id => p_transaction_step_id
1428 ,p_name => 'P_NTH_INFORMATION16');
1429 l_nth_information17 := hr_transaction_api.get_varchar2_value
1430 (p_transaction_step_id => p_transaction_step_id
1431 ,p_name => 'P_NTH_INFORMATION17');
1432 l_nth_information18 := hr_transaction_api.get_varchar2_value
1433 (p_transaction_step_id => p_transaction_step_id
1434 ,p_name => 'P_NTH_INFORMATION18');
1435 l_nth_information19 := hr_transaction_api.get_varchar2_value
1436 (p_transaction_step_id => p_transaction_step_id
1437 ,p_name => 'P_NTH_INFORMATION19');
1438 l_nth_information20 := hr_transaction_api.get_varchar2_value
1439 (p_transaction_step_id => p_transaction_step_id
1440 ,p_name => 'P_NTH_INFORMATION20');
1441 l_contact_name := hr_transaction_api.get_varchar2_value
1442 (p_transaction_step_id => p_transaction_step_id
1443 ,p_name => 'P_CONTACT_NAME');
1444 l_activity_name := hr_transaction_api.get_varchar2_value
1445 (p_transaction_step_id => p_transaction_step_id
1446 ,p_name => 'P_ACTIVITY_NAME');
1447 l_old_obj_ver_no := hr_transaction_api.get_varchar2_value
1448 (p_transaction_step_id => p_transaction_step_id
1449 ,p_name => 'P_OBJ_VER_NO');
1450 l_business_grp_id := hr_transaction_api.get_varchar2_value
1451 (p_transaction_step_id => p_transaction_step_id
1452 ,p_name => 'P_BUSINESS_GRP_ID');
1453 l_person_id := hr_transaction_api.get_number_value
1454 (p_transaction_step_id => p_transaction_step_id
1455 ,p_name => 'P_DELEGATE_PERSON_ID');
1456
1457 l_org_id := hr_transaction_api.get_varchar2_value
1458 (p_transaction_step_id => p_transaction_step_id
1459 ,p_name => 'P_ORG_ID');
1460
1461
1462 l_approval_reg_flag := wf_engine.GetActivityAttrText(itemtype => l_item_type
1463 ,itemkey => l_item_key
1464 ,actid => l_activity_id
1465 ,aname => 'HR_APPROVAL_REQ_FLAG');
1466
1467 --Bug#2508100 hdshah Below if condition corrected.
1468 -- if l_approval_reg_flag <> 'NO' then
1469 if (l_approval_reg_flag <> 'NO' and p_validate) then
1470 chk_pending_approval
1471 (p_nota_history_id => l_historyid
1472 ,p_person_id => l_person_id) ;
1473 end if;
1474
1475
1476 update_add_training
1477 (p_effective_date => SYSDATE
1478 ,p_nota_history_id => l_historyid
1479 ,p_person_id => l_person_id
1480 ,p_contact_id => l_internal_contact_person
1481 ,p_trng_title => l_title
1482 ,p_provider => l_supplier
1483 ,p_type => l_trntype
1484 ,p_centre => l_location
1485 ,p_completion_date => l_completion_date
1486 ,p_award => l_award
1487 ,p_rating => l_score
1488 ,p_duration => l_duration
1489 ,p_duration_units => l_duration_unit
1490 ,p_activity_version_id => l_eq_ota_activity
1491 ,p_status => l_status
1492 ,p_verified_by_id => NULL
1493 ,p_nth_information_category => l_nth_information_category
1494 ,p_nth_information1 => l_nth_information1
1495 ,p_nth_information2 => l_nth_information2
1496 ,p_nth_information3 => l_nth_information3
1497 ,p_nth_information4 => l_nth_information4
1498 ,p_nth_information5 => l_nth_information5
1499 ,p_nth_information6 => l_nth_information6
1500 ,p_nth_information7 => l_nth_information7
1501 ,p_nth_information8 => l_nth_information8
1502 ,p_nth_information9 => l_nth_information9
1503 ,p_nth_information10 => l_nth_information10
1504 ,p_nth_information11 => l_nth_information11
1505 ,p_nth_information12 => l_nth_information12
1506 ,p_nth_information13 => l_nth_information13
1507 ,p_nth_information15 => l_nth_information15
1508 ,p_nth_information16 => l_nth_information16
1509 ,p_nth_information17 => l_nth_information17
1510 ,p_nth_information18 => l_nth_information18
1511 ,p_nth_information19 => l_nth_information19
1512 ,p_nth_information20 => l_nth_information20
1513 ,p_org_id => NULL
1514 ,p_old_object_version_number => l_old_obj_ver_no
1515 ,p_new_object_version_number => l_new_obj_ver_no
1516 ,p_business_group_id => l_business_grp_id
1517 ,p_nth_information14 => l_nth_information14
1518 ,p_customer_id => NULL
1519 ,p_organization_id => l_org_id
1520 ,p_some_warning => l_some_warning
1521 ,p_message => l_message
1522 ,p_item_type => l_item_type
1523 ,p_item_key => l_item_key
1524 );
1525 EXCEPTION
1526 WHEN OTHERS THEN
1527 rollback to update_tt;
1528 RAISE;
1529 END update_add_training_tt;
1530
1531
1532 -- ----------------------------------------------------------------------------
1533 -- |-----------------------------< delete_add_training >--------------------------|
1534 -- ----------------------------------------------------------------------------
1535 -- {Start Of Comments}
1536 --
1537 -- Description:
1538 -- This package is used by self service application to delete additional training records.
1539 --
1540 -- Prerequisites:
1541 --
1542 -- In Parameters:
1543 --
1544 -- Post Success:
1545 -- Additional Training data will be deleted.
1546 --
1547 -- Post Failure:
1548 -- Status will be passed to the caller and the caller will raise a notification.
1549 --
1550 -- Developer Implementation Notes:
1551 -- The attrbute in parameters should be modified as to the business process
1552 -- requirements.
1553 --
1554 -- Access Status:
1555 -- Internal Development Use Only.
1556 --
1557 -- {End Of Comments}
1558 -- ----------------------------------------------------------------------------
1559 PROCEDURE delete_add_training
1560 ( p_nota_history_id IN OTA_NOTRNG_HISTORIES.NOTA_HISTORY_ID%TYPE
1561 , p_trng_title IN VARCHAR2
1562 , p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE
1563 , p_item_key IN WF_ITEMS.ITEM_TYPE%TYPE
1564 , p_message OUT NOCOPY VARCHAR2
1565 ) IS
1566
1567 CURSOR c_get_obj_ver_no IS
1568 SELECT OBJECT_VERSION_NUMBER
1569 FROM OTA_NOTRNG_HISTORIES
1570 WHERE NOTA_HISTORY_ID = p_nota_history_id;
1571
1572 l_obj_ver_no OTA_NOTRNG_HISTORIES.OBJECT_VERSION_NUMBER%TYPE;
1573 l_proc VARCHAR2(72) := 'Delete_Add_Training';
1574
1575 BEGIN
1576
1577 OPEN c_get_obj_ver_no;
1578 FETCH c_get_obj_ver_no INTO l_obj_ver_no;
1579 CLOSE c_get_obj_ver_no;
1580
1581 ota_nhs_del.del( p_nota_history_id, l_obj_ver_no );
1582
1583 p_message := 'S';
1584
1585 --set workflow attributes for notification
1586 /*WF_ENGINE.setitemattrtext(p_item_type,
1587 p_item_key,
1588 'PROCESS_DISPLAY_NAME',
1589 'External Training '); */
1590
1591 WF_ENGINE.setitemattrtext(p_item_type,
1592 p_item_key,
1593 'OTA_EVENT_TITLE',
1594 p_trng_title);
1595
1596 EXCEPTION
1597 --
1598 WHEN OTHERS THEN
1599 --
1600 -- p_message := SQLCODE||': '||SUBSTR(SQLERRM, 1, 950);
1601 p_message := fnd_message.get();
1602 hr_utility.set_location('Leaving:'||g_package||l_proc, 40);
1603 --
1604 END delete_add_training;
1605
1606 -- ----------------------------------------------------------------------------
1607 -- |-----------------------------< create_add_training >--------------------------|
1608 -- ----------------------------------------------------------------------------
1609 -- {Start Of Comments}
1610 --
1611 -- Description:
1612 -- This package is used by self service application to create additional training records.
1613 --
1614 -- Prerequisites:
1615 --
1616 -- In Parameters:
1617 --
1618 -- Post Success:
1619 -- Additional Training data will be created.
1620 --
1621 -- Post Failure:
1622 -- Status will be passed to the caller and the caller will raise a notification.
1623 --
1624 -- Developer Implementation Notes:
1625 -- The attrbute in parameters should be modified as to the business process
1626 -- requirements.
1627 --
1628 -- Access Status:
1629 -- Internal Development Use Only.
1630 --
1631 -- {End Of Comments}
1632 -- ----------------------------------------------------------------------------
1633
1634 PROCEDURE create_add_training
1635 (p_effective_date IN DATE
1636 ,p_nota_history_id OUT NOCOPY NUMBER
1637 ,p_person_id IN NUMBER
1638 ,p_contact_id IN NUMBER
1639 ,p_trng_title IN VARCHAR2
1640 ,p_provider IN VARCHAR2
1641 ,p_type IN VARCHAR2
1642 ,p_centre IN VARCHAR2
1643 ,p_completion_date IN DATE
1644 ,p_award IN VARCHAR2
1645 ,p_rating IN VARCHAR2
1646 ,p_duration IN NUMBER
1647 ,p_duration_units IN VARCHAR2
1648 ,p_activity_version_id IN NUMBER
1649 ,p_status IN VARCHAR2
1650 ,p_verified_by_id IN NUMBER
1651 ,p_nth_information_category IN VARCHAR2
1652 ,p_nth_information1 IN VARCHAR2
1653 ,p_nth_information2 IN VARCHAR2
1654 ,p_nth_information3 IN VARCHAR2
1655 ,p_nth_information4 IN VARCHAR2
1656 ,p_nth_information5 IN VARCHAR2
1657 ,p_nth_information6 IN VARCHAR2
1658 ,p_nth_information7 IN VARCHAR2
1659 ,p_nth_information8 IN VARCHAR2
1660 ,p_nth_information9 IN VARCHAR2
1661 ,p_nth_information10 IN VARCHAR2
1662 ,p_nth_information11 IN VARCHAR2
1663 ,p_nth_information12 IN VARCHAR2
1664 ,p_nth_information13 IN VARCHAR2
1665 ,p_nth_information15 IN VARCHAR2
1666 ,p_nth_information16 IN VARCHAR2
1667 ,p_nth_information17 IN VARCHAR2
1668 ,p_nth_information18 IN VARCHAR2
1669 ,p_nth_information19 IN VARCHAR2
1670 ,p_nth_information20 IN VARCHAR2
1671 ,p_org_id IN NUMBER
1672 ,p_object_version_number OUT NOCOPY NUMBER
1673 ,p_business_group_id IN NUMBER
1674 ,p_nth_information14 IN VARCHAR2
1675 ,p_customer_id IN NUMBER
1676 ,p_organization_id IN NUMBER
1677 ,p_some_warning OUT NOCOPY NUMBER
1678 ,p_message OUT NOCOPY VARCHAR2
1679 ,p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE
1680 ,p_item_key IN WF_ITEMS.ITEM_TYPE%TYPE
1681
1682 ) IS
1683 --
1684 -- Declare cursors and local variables
1685 --
1686 l_some_warning BOOLEAN;
1687 l_proc VARCHAR2(72) := 'Create_Add_Training';
1688 l_learner_name per_all_people_f.full_name%TYPE;
1689
1690 BEGIN
1691
1692
1693 OTA_NHS_API.create_non_ota_histories
1694 (p_validate => false
1695 ,p_effective_date => p_effective_date
1696 ,p_nota_history_id => p_nota_history_id
1697 ,p_person_id => p_person_id
1698 ,p_contact_id => p_contact_id
1699 ,p_trng_title => p_trng_title
1700 ,p_provider => p_provider
1701 ,p_type => p_type
1702 ,p_centre => p_centre
1703 ,p_completion_date => p_completion_date
1704 ,p_award => p_award
1705 ,p_rating => p_rating
1706 ,p_duration => p_duration
1707 ,p_duration_units => p_duration_units
1708 ,p_activity_version_id => p_activity_version_id
1709 ,p_status => p_status
1710 ,p_verified_by_id => p_verified_by_id
1711 ,p_nth_information_category => p_nth_information_category
1712 ,p_nth_information1 => p_nth_information1
1713 ,p_nth_information2 => p_nth_information2
1714 ,p_nth_information3 => p_nth_information3
1715 ,p_nth_information4 => p_nth_information4
1716 ,p_nth_information5 => p_nth_information5
1717 ,p_nth_information6 => p_nth_information6
1718 ,p_nth_information7 => p_nth_information7
1719 ,p_nth_information8 => p_nth_information8
1720 ,p_nth_information9 => p_nth_information9
1721 ,p_nth_information10 => p_nth_information10
1722 ,p_nth_information11 => p_nth_information11
1723 ,p_nth_information12 => p_nth_information12
1724 ,p_nth_information13 => p_nth_information13
1725 ,p_nth_information15 => p_nth_information15
1726 ,p_nth_information16 => p_nth_information16
1727 ,p_nth_information17 => p_nth_information17
1728 ,p_nth_information18 => p_nth_information18
1729 ,p_nth_information19 => p_nth_information19
1730 ,p_nth_information20 => p_nth_information20
1731 ,p_org_id => p_org_id
1732 ,p_object_version_number => p_object_version_number
1733 ,p_business_group_id => p_business_group_id
1734 ,p_nth_information14 => p_nth_information14
1735 ,p_customer_id => p_customer_id
1736 ,p_organization_id => p_organization_id
1737 ,p_some_warning => l_some_warning
1738 );
1739
1740 IF ( l_some_warning ) THEN
1741 p_some_warning := 1;
1742 ELSE
1743 p_some_warning := 0;
1744 END IF;
1745 p_message := 'S';
1746 l_learner_name := get_internal_contact_name(p_person_id);
1747 --set workflow attributes for notification
1748
1749 hr_approval_wf.create_item_attrib_if_notexist
1750 (p_item_type => p_item_type
1751 ,p_item_key => p_item_key
1752 ,p_name => 'OTA_TRANSACTION_MODE');
1753
1754 WF_ENGINE.setitemattrtext(p_item_type,
1755 p_item_key,
1756 'OTA_TRANSACTION_MODE',
1757 'INSERT');
1758
1759 /* WF_ENGINE.setitemattrtext(p_item_type,
1760 p_item_key,
1761 'PROCESS_DISPLAY_NAME',
1762 'External Training '); */
1763
1764 --bug 3593080
1765 WF_ENGINE.setitemattrtext(p_item_type,
1766 p_item_key,
1767 'CURRENT_PERSON_DISPLAY_NAME',
1768 l_learner_name);
1769 --bug 3593080
1770
1771 WF_ENGINE.setitemattrtext(p_item_type,
1772 p_item_key,
1773 'OTA_EVENT_TITLE',
1774 p_trng_title);
1775
1776 WF_ENGINE.setitemattrtext(p_item_type,
1777 p_item_key,
1778 'OTA_COURSE_END_DATE',
1779 p_completion_date);
1780
1781 EXCEPTION
1782 --
1783 WHEN OTHERS THEN
1784 --
1785 p_some_warning := -1;
1786 hr_utility.set_location('Leaving:'||g_package||l_proc, 40);
1787 -- p_message := SQLCODE||': '||SUBSTR(SQLERRM, 1, 950);
1788
1789
1790 If g_called_from = 'R' Then
1791 g_called_From := 'S' ;
1792 RAISE ;
1793 Else
1794 p_message := fnd_message.get();
1795
1796 End if;
1797
1798 If p_message is NULL then
1799
1800 p_message := substr(SQLERRM,11,(length(SQLERRM)-10));
1801
1802 End If;
1803 --
1804 END create_add_training;
1805
1806 -- ----------------------------------------------------------------------------
1807 -- |-----------------------------< update_add_training >--------------------------|
1808 -- ----------------------------------------------------------------------------
1809 -- {Start Of Comments}
1810 --
1811 -- Description:
1812 -- This package is used by self service application to update additional training records.
1813 --
1814 -- Prerequisites:
1815 --
1816 -- In Parameters:
1817 --
1818 -- Post Success:
1819 -- Additional Training data will be updated.
1820 --
1821 -- Post Failure:
1822 -- Status will be passed to the caller and the caller will raise a notification.
1823 --
1824 -- Developer Implementation Notes:
1825 -- The attrbute in parameters should be modified as to the business process
1826 -- requirements.
1827 --
1828 -- Access Status:
1829 -- Internal Development Use Only.
1830 --
1831 -- {End Of Comments}
1832 -- ----------------------------------------------------------------------------
1833
1834 Procedure update_add_training
1835 (p_effective_date IN DATE
1836 ,p_nota_history_id IN NUMBER
1837 ,p_person_id IN NUMBER
1838 ,p_contact_id IN NUMBER
1839 ,p_trng_title IN VARCHAR2
1840 ,p_provider IN VARCHAR2
1841 ,p_type IN VARCHAR2
1845 ,p_rating IN VARCHAR2
1842 ,p_centre IN VARCHAR2
1843 ,p_completion_date IN DATE
1844 ,p_award IN VARCHAR2
1846 ,p_duration IN NUMBER
1847 ,p_duration_units IN VARCHAR2
1848 ,p_activity_version_id IN NUMBER
1849 ,p_status IN VARCHAR2
1850 ,p_verified_by_id IN NUMBER
1851 ,p_nth_information_category IN VARCHAR2
1852 ,p_nth_information1 IN VARCHAR2
1853 ,p_nth_information2 IN VARCHAR2
1854 ,p_nth_information3 IN VARCHAR2
1855 ,p_nth_information4 IN VARCHAR2
1856 ,p_nth_information5 IN VARCHAR2
1857 ,p_nth_information6 IN VARCHAR2
1858 ,p_nth_information7 IN VARCHAR2
1859 ,p_nth_information8 IN VARCHAR2
1860 ,p_nth_information9 IN VARCHAR2
1861 ,p_nth_information10 IN VARCHAR2
1862 ,p_nth_information11 IN VARCHAR2
1863 ,p_nth_information12 IN VARCHAR2
1864 ,p_nth_information13 IN VARCHAR2
1865 ,p_nth_information14 IN VARCHAR2
1866 ,p_nth_information15 IN VARCHAR2
1867 ,p_nth_information16 IN VARCHAR2
1868 ,p_nth_information17 IN VARCHAR2
1869 ,p_nth_information18 IN VARCHAR2
1870 ,p_nth_information19 IN VARCHAR2
1871 ,p_nth_information20 IN VARCHAR2
1872 ,p_org_id IN NUMBER
1873 ,p_old_object_version_number IN NUMBER
1874 ,p_business_group_id IN NUMBER
1875 ,p_customer_id IN NUMBER
1876 ,p_organization_id IN NUMBER
1877 ,p_some_warning OUT NOCOPY NUMBER
1878 ,p_message OUT NOCOPY VARCHAR2
1879 ,p_new_object_version_number OUT NOCOPY NUMBER
1880 ,p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE
1881 ,p_item_key IN WF_ITEMS.ITEM_TYPE%TYPE
1882 ) IS
1883 --
1884 -- Declare cursors and local variables
1885 --
1886 l_some_warning BOOLEAN;
1887 l_new_object_version_number NUMBER;
1888 l_proc VARCHAR2(72) := 'Update_Add_Training';
1889 l_learner_name per_all_people_f.full_name%TYPE;
1890 BEGIN
1891 hr_utility.set_location('Entering:'||g_package||l_proc, 40);
1892
1893 l_new_object_version_number := p_old_object_version_number;
1894
1895 OTA_NHS_API.update_non_ota_histories
1896 (p_validate => false
1897 ,p_effective_date => p_effective_date
1898 ,p_nota_history_id => p_nota_history_id
1899 ,p_person_id => p_person_id
1900 ,p_contact_id => p_contact_id
1901 ,p_trng_title => p_trng_title
1902 ,p_provider => p_provider
1903 ,p_type => p_type
1904 ,p_centre => p_centre
1905 ,p_completion_date => p_completion_date
1906 ,p_award => p_award
1907 ,p_rating => p_rating
1908 ,p_duration => p_duration
1909 ,p_duration_units => p_duration_units
1910 ,p_activity_version_id => p_activity_version_id
1911 ,p_status => p_status
1912 ,p_verified_by_id => p_verified_by_id
1913 ,p_nth_information_category => p_nth_information_category
1914 ,p_nth_information1 => p_nth_information1
1915 ,p_nth_information2 => p_nth_information2
1916 ,p_nth_information3 => p_nth_information3
1917 ,p_nth_information4 => p_nth_information4
1918 ,p_nth_information5 => p_nth_information5
1919 ,p_nth_information6 => p_nth_information6
1920 ,p_nth_information7 => p_nth_information7
1921 ,p_nth_information8 => p_nth_information8
1922 ,p_nth_information9 => p_nth_information9
1923 ,p_nth_information10 => p_nth_information10
1924 ,p_nth_information11 => p_nth_information11
1925 ,p_nth_information12 => p_nth_information12
1926 ,p_nth_information13 => p_nth_information13
1927 ,p_nth_information14 => p_nth_information14
1928 ,p_nth_information15 => p_nth_information15
1932 ,p_nth_information19 => p_nth_information19
1929 ,p_nth_information16 => p_nth_information16
1930 ,p_nth_information17 => p_nth_information17
1931 ,p_nth_information18 => p_nth_information18
1933 ,p_nth_information20 => p_nth_information20
1934 ,p_org_id => p_org_id
1935 ,p_object_version_number => l_new_object_version_number
1936 ,p_business_group_id => p_business_group_id
1937 ,p_customer_id => p_customer_id
1938 -- ,p_organization_id => p_organization_id
1939 ,p_some_warning => l_some_warning
1940 );
1941
1942 IF ( l_some_warning ) THEN
1943 p_some_warning := 1;
1944 ELSE
1945 p_some_warning := 0;
1946 END IF;
1947
1948 p_message := 'S';
1949 l_learner_name := get_internal_contact_name(p_person_id);
1950
1951 --set workflow attributes for notification
1952
1953 hr_approval_wf.create_item_attrib_if_notexist
1954 (p_item_type => p_item_type
1955 ,p_item_key => p_item_key
1956 ,p_name => 'OTA_TRANSACTION_MODE');
1957
1958 WF_ENGINE.setitemattrtext(p_item_type,
1959 p_item_key,
1960 'OTA_TRANSACTION_MODE',
1961 'UPDATE');
1962
1963 /*WF_ENGINE.setitemattrtext(p_item_type,
1964 p_item_key,
1965 'PROCESS_DISPLAY_NAME',
1966 'External Training '); */
1967
1968 --bug 3593080
1969 WF_ENGINE.setitemattrtext(p_item_type,
1970 p_item_key,
1971 'CURRENT_PERSON_DISPLAY_NAME',
1972 l_learner_name);
1973 --bug 3593080
1974 WF_ENGINE.setitemattrtext(p_item_type,
1975 p_item_key,
1976 'OTA_EVENT_TITLE',
1977 p_trng_title);
1978
1979 WF_ENGINE.setitemattrtext(p_item_type,
1980 p_item_key,
1981 'OTA_COURSE_END_DATE',
1982 p_completion_date);
1983
1984 p_new_object_version_number := l_new_object_version_number;
1985 hr_utility.set_location('Leaving:'||g_package||l_proc, 40);
1986 EXCEPTION
1987 --
1988 WHEN OTHERS THEN
1989 --
1990 p_some_warning := -1;
1991 hr_utility.set_location('WHEN OTHERS Exception in :'||g_package||l_proc, 50);
1992 -- p_message := SQLERRM;
1993
1994
1995 If g_called_from = 'R' Then
1996 g_called_From := 'S' ;
1997 RAISE ;
1998 Else
1999 p_message := fnd_message.get();
2000
2001 End If;
2002
2003 If p_message is NULL then
2004 p_message := substr(SQLERRM,11,(length(SQLERRM)-10));
2005
2006 End If;
2007 --
2008 END update_add_training;
2009
2010 -- ----------------------------------------------------------------------------
2011 -- |-----------------------------<additional_training_notify>--------------------------|
2012 -- ----------------------------------------------------------------------------
2013 -- {Start Of Comments}
2014 --
2015 -- Description:
2016 -- This procedure is used by self service application to identify which notification (insert or update)
2017 -- to send on commiting a transaction IN the table.
2021 --
2018 -- Prerequisites:
2019 --
2020 -- In Parameters:
2022 -- Post Success:
2023 --
2024 --
2025 -- Post Failure:
2026 --
2027 --
2028 -- Developer Implementation Notes:
2029 --
2030 --
2031 -- Access Status:
2032 -- Internal Development Use Only.
2033 --
2034 -- {End Of Comments}
2035 -- ----------------------------------------------------------------------------
2036 PROCEDURE additional_training_notify (itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
2037 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
2038 actid IN NUMBER,
2039 funcmode IN VARCHAR2,
2040 resultout OUT NOCOPY VARCHAR2)
2041 IS
2042
2043 l_transaction_mode VARCHAR2(10);
2044
2045 BEGIN
2046
2047 l_transaction_mode := wf_engine.GetItemAttrText(itemtype => itemtype
2048 ,itemkey => itemkey
2049 ,aname => 'OTA_TRANSACTION_MODE');
2050
2051 IF (funcmode='RUN') THEN
2052 IF l_transaction_mode='INSERT' THEN
2053 resultout:='COMPLETE:INSERT';
2054 ELSE
2055 resultout:='COMPLETE:UPDATE';
2056 END IF;
2057 END IF;
2058
2059 RETURN;
2060 END additional_training_notify;
2061
2062 procedure validate_add_training
2063 (p_item_type in varchar2,
2064 p_item_key in varchar2,
2065 p_message out nocopy varchar2) is
2066
2067 l_transaction_step_id number;
2068
2069 Begin
2070
2071 l_transaction_step_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type
2072 ,itemkey => p_item_key
2073 ,aname => 'OTA_TRANSACTION_STEP_ID');
2074 process_api(true,l_transaction_step_id);
2075
2076 p_message := 'S' ;
2077 EXCEPTION
2078 When OTHERS Then
2079
2080 p_message := fnd_message.get();
2081
2082 If p_message is NULL then
2083
2084 p_message := substr(SQLERRM,11,(length(SQLERRM)-10));
2085
2086 End If;
2087
2088
2089 End validate_add_training;
2090
2091 -- ----------------------------------------------------------------------------
2092 -- |-----------------------------< get_internal_contact_name >--------------------------|
2093 -- ----------------------------------------------------------------------------
2094 -- {Start Of Comments}
2095 --
2096 -- Description:
2097 -- This function is used by self service application to get the contact person name.
2098 -- Prerequisites:
2099 --
2100 -- In Parameters:
2101 --
2102 -- Post Success:
2103 --
2104 --
2105 -- Post Failure:
2106 --
2107 --
2108 -- Developer Implementation Notes:
2109 --
2110 --
2111 -- Access Status:
2112 -- Internal Development Use Only.
2113 --
2114 -- {End Of Comments}
2115 -- ----------------------------------------------------------------------------
2116 Function get_internal_contact_name
2117 ( Person_id IN per_all_people_f.person_id%TYPE) RETURN per_all_people_f.full_name%TYPE
2118 IS
2119
2120 CURSOR c_get_person_name(p_person_id per_all_people_f.person_id%TYPE) IS
2121 SELECT full_name
2122 FROM per_all_people_f
2123 WHERE person_id = p_person_id
2124 AND sysdate BETWEEN effective_start_date and effective_end_date; --Bug 5464327: date tracking of per_all_people_f is considered.
2125
2126 l_person_full_name per_all_people_f.full_name%TYPE;
2127 l_proc VARCHAR2(72) := 'get_internal_contact_name';
2128 BEGIN
2129
2130 hr_utility.set_location('Entering:'||g_package||l_proc, 10);
2131 OPEN c_get_person_name(person_id);
2132 FETCH c_get_person_name INTO l_person_full_name;
2133 CLOSE c_get_person_name;
2134 hr_utility.set_location('Leaving:'||g_package||l_proc, 20);
2135 RETURN l_person_full_name;
2136
2137 EXCEPTION
2138 WHEN others THEN
2139 hr_utility.set_location('Leaving:'||g_package||l_proc, 30);
2140 RETURN null;
2141
2142 END get_internal_contact_name;
2143
2144
2145 -- ----------------------------------------------------------------------------
2146 -- |-----------------------------< get_learner_name >--------------------------|
2147 -- ----------------------------------------------------------------------------
2148 -- {Start Of Comments}
2149 -- Description:
2150 -- This function is used by self service application to get the contact person name.
2151 -- The above implementation assumes that the contact would be found in the
2152 -- per_all_poople_f table. It is ignoring the possiblity of the contact being
2153 -- that of a customer. This function considers that.
2154 -- In Parameters:
2155 -- Contact_Id - person_id/contact id of employee/customer
2156 -- Organization_Id - this parameter is used to decide if the per_all_people_f
2157 -- or the ra_contacts needs to be queried. If this is null, then the incoming
2158 -- person_id belongs to a Customers' contact and ra_contacts is queried.
2159 -- {End Of Comments}
2160 -- ----------------------------------------------------------------------------
2161 Function get_learner_name
2162 ( Person_id IN ota_notrng_histories.contact_id%TYPE
2163 ,Organization_id IN ota_notrng_histories.organization_id%TYPE ) RETURN VARCHAR2
2164 IS
2165
2166 CURSOR c_get_person_name(p_person_id per_all_people_f.person_id%TYPE) IS
2167 SELECT full_name
2168 FROM per_all_people_f
2169 WHERE person_id = p_person_id
2173 SELECT
2170 AND sysdate BETWEEN effective_start_date and effective_end_date;
2171
2172 CURSOR c_get_contact_name(p_person_id hz_cust_account_roles.cust_account_role_id%TYPE) IS
2174 substrb( PARTY.person_last_name,1,50) || ',' ||
2175 substrb( PARTY.person_first_name,1,40) || ' ' ||
2176 party.person_pre_name_adjunct
2177 from HZ_CUST_ACCOUNT_ROLES acct_role,
2178 HZ_PARTIES party,
2179 HZ_RELATIONSHIPS rel,
2180 HZ_ORG_CONTACTS org_cont,
2181 HZ_PARTIES rel_party,
2182 HZ_CUST_ACCOUNTS role_acct
2183 where acct_role.party_id = rel.party_id
2184 and acct_role.role_type = 'CONTACT'
2185 and org_cont.party_relationship_id = rel.relationship_id
2186 and rel.subject_id = party.party_id
2187 and rel.party_id = rel_party.party_id
2188 and rel.subject_table_name = 'HZ_PARTIES'
2189 and rel.object_table_name = 'HZ_PARTIES'
2190 and acct_role.cust_account_id = role_acct.cust_account_id
2191 and role_acct.party_id = rel.object_id
2192 and acct_role.cust_account_role_id = p_person_id;
2193
2194 l_person_full_name VARCHAR2(500);
2195 l_proc VARCHAR2(72) := 'get_learner_name';
2196 BEGIN
2197
2198 hr_utility.set_location('Entering:'||g_package||l_proc, 10);
2199
2200 IF organization_id IS NULL THEN
2201 -- ra_contacts needs to be queried since org id is null
2202 OPEN c_get_contact_name(person_id);
2203 FETCH c_get_contact_name INTO l_person_full_name;
2204 CLOSE c_get_contact_name;
2205 ELSE
2206 OPEN c_get_person_name(person_id);
2207 FETCH c_get_person_name INTO l_person_full_name;
2208 CLOSE c_get_person_name;
2209 END IF;
2210 hr_utility.set_location('Leaving:'||g_package||l_proc, 20);
2211 RETURN l_person_full_name;
2212
2213 EXCEPTION
2214 WHEN others THEN
2215 hr_utility.set_location('Leaving:'||g_package||l_proc, 30);
2216 RETURN null;
2217 END get_learner_name;
2218
2219 -- ----------------------------------------------------------------------------
2220 -- |-----------------------------< get_custorg_name >--------------------------|
2221 -- ----------------------------------------------------------------------------
2222 -- {Start Of Comments}
2223 -- Description:
2224 -- This function is used by self service application to get the name of the
2225 -- customer or the organization depending on the not null id.
2226 -- In Parameters:
2227 -- Customer_Id - customer_id of the Customer
2228 -- {End Of Comments}
2229 -- ----------------------------------------------------------------------------
2230 FUNCTION get_custorg_name(p_customer_id OTA_NOTRNG_HISTORIES.CUSTOMER_ID%TYPE,
2231 p_organization_id OTA_NOTRNG_HISTORIES.ORGANIZATION_ID%TYPE)
2232 RETURN VARCHAR2 IS
2233
2234 CURSOR c_get_customer_name IS
2235 SELECT
2236 substrb(PARTY.party_name,1,50)
2237 FROM
2238 HZ_PARTIES party,
2239 HZ_CUST_ACCOUNTS cust_acct
2240 WHERE
2241 CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
2242 AND CUST_ACCT.CUST_ACCOUNT_ID = p_customer_id;
2243
2244 l_value VARCHAR2(500);
2245 l_proc VARCHAR2(50) := 'get_customer_name';
2246
2247 BEGIN
2248 HR_UTILITY.SET_LOCATION('Entering:'||g_package||l_proc, 10);
2249
2250 IF p_organization_id IS NOT NULL THEN
2251 SELECT OTA_GENERAL.get_org_name(p_organization_id) INTO l_value FROM DUAL;
2252 ELSE
2253 OPEN c_get_customer_name;
2254 FETCH c_get_customer_name INTO l_value;
2255 CLOSE c_get_customer_name;
2256 END IF;
2257
2258 HR_UTILITY.SET_LOCATION('Leaving'||g_package||l_proc, 30);
2259 RETURN l_value;
2260 EXCEPTION
2261 WHEN others THEN
2262 HR_UTILITY.SET_LOCATION('Leaving:'||g_package||l_proc, 30);
2263 RETURN null;
2264 END get_custorg_name;
2265
2266 -- ----------------------------------------------------------------------------
2267 -- |-----------------------------< check_changes >--------------------------|
2268 -- ----------------------------------------------------------------------------
2269 -- {Start Of Comments}
2270 --
2271 -- Description:
2272 -- This procedure is used by self service application to find out whether in 'update'
2273 -- mode any changes are made or not by comparing it with data from database.
2274 -- Prerequisites:
2275 --
2276 -- In Parameters:
2277 --
2278 -- Post Success:
2279 --
2280 --
2281 -- Post Failure:
2282 --
2283 --
2284 -- Developer Implementation Notes:
2285 --
2286 --
2287 -- Access Status:
2288 -- Internal Development Use Only.
2289 --
2290 -- {End Of Comments}
2291 -- ----------------------------------------------------------------------------
2292 Procedure check_changes
2293 (p_nota_history_id in NUMBER
2294 ,p_contact_id in NUMBER
2295 ,p_trng_title in VARCHAR2
2296 ,p_provider in VARCHAR2
2297 ,p_type in VARCHAR2
2298 ,p_centre in VARCHAR2
2299 ,p_completion_date in date
2300 ,p_award in VARCHAR2
2301 ,p_rating in VARCHAR2
2302 ,p_duration in NUMBER
2303 ,p_duration_units in VARCHAR2
2304 ,p_activity_version_id in NUMBER
2305 ,p_status in VARCHAR2
2306 ,p_nth_information_category in VARCHAR2
2307 ,p_nth_information1 in VARCHAR2
2308 ,p_nth_information2 in VARCHAR2
2309 ,p_nth_information3 in VARCHAR2
2310 ,p_nth_information4 in VARCHAR2
2314 ,p_nth_information8 in VARCHAR2
2311 ,p_nth_information5 in VARCHAR2
2312 ,p_nth_information6 in VARCHAR2
2313 ,p_nth_information7 in VARCHAR2
2315 ,p_nth_information9 in VARCHAR2
2316 ,p_nth_information10 in VARCHAR2
2317 ,p_nth_information11 in VARCHAR2
2318 ,p_nth_information12 in VARCHAR2
2319 ,p_nth_information13 in VARCHAR2
2320 ,p_nth_information14 in VARCHAR2
2321 ,p_nth_information15 in VARCHAR2
2322 ,p_nth_information16 in VARCHAR2
2323 ,p_nth_information17 in VARCHAR2
2324 ,p_nth_information18 in VARCHAR2
2325 ,p_nth_information19 in VARCHAR2
2326 ,p_nth_information20 in VARCHAR2
2327 ,p_result out nocopy NUMBER
2328 ) Is
2329
2330 CURSOR c_get_training_data(p_history_id ota_notrng_histories.nota_history_id%TYPE) IS
2331 Select
2332 ONH.Trng_Title,
2333 ONH.Provider,
2334 ONH.activity_version_id,
2335 ONH.centre,
2336 ONH.type,
2337 ONH.duration,
2338 ONH.duration_units,
2339 ONH.status,
2340 ONH.completion_date,
2341 ONH.award,
2342 ONH.rating,
2343 ONH.contact_id,
2344 ONH.nth_information_category,
2345 ONH.nth_information1,
2346 ONH.nth_information2,
2347 ONH.nth_information3,
2348 ONH.nth_information4,
2349 ONH.nth_information5,
2350 ONH.nth_information6,
2351 ONH.nth_information7,
2352 ONH.nth_information8,
2353 ONH.nth_information9,
2354 ONH.nth_information10,
2355 ONH.nth_information11,
2356 ONH.nth_information12,
2357 ONH.nth_information13,
2358 ONH.nth_information14,
2359 ONH.nth_information15,
2360 ONH.nth_information16,
2361 ONH.nth_information17,
2362 ONH.nth_information18,
2363 ONH.nth_information19,
2364 ONH.nth_information20
2365 From
2366 OTA_NOTRNG_HISTORIES ONH
2367 where
2368 ONH.nota_history_id = p_history_id;
2369
2370 l_proc VARCHAR2(72) := 'check_changes';
2371 l_changed_flag BOOLEAN := false;
2372 l_null_value VARCHAR2(5) := '^%&*!';
2373 l_null_value_number NUMBER := -1000;
2374 l_null_value_date Date := sysdate - 1000;
2375 BEGIN
2376
2377 hr_utility.set_location('Entering:'||g_package||l_proc, 10);
2378
2379 FOR trg_rec IN c_get_training_data(p_nota_history_id) LOOP
2380
2381 If (Nvl(trim(trg_rec.Trng_Title),l_null_value) = Nvl(trim(p_trng_title),l_null_value)) Then
2382 Null;
2383 Else
2384 l_changed_flag := true;
2385 exit;
2386 End If;
2387
2388 If (Nvl(trim(trg_rec.Provider),l_null_value) = Nvl(trim(p_provider),l_null_value)) Then
2389 Null;
2390 Else
2391 l_changed_flag := true;
2392 exit;
2393 End If;
2394
2395 If (Nvl(trg_rec.activity_version_id,l_null_value_number) = Nvl(p_activity_version_id,l_null_value_number)) Then
2396 Null;
2397 Else
2398 l_changed_flag := true;
2399 exit;
2400 End If;
2401
2402 If (Nvl(trim(trg_rec.centre),l_null_value) = Nvl(trim(p_centre),l_null_value)) Then
2403 Null;
2404 Else
2405 l_changed_flag := true;
2406 exit;
2407 End If;
2408
2409 If (Nvl(trim(trg_rec.type),l_null_value) = Nvl(trim(p_type),l_null_value)) Then
2410 Null;
2411 Else
2412 l_changed_flag := true;
2413 exit;
2414 End If;
2415
2416 If (Nvl(trg_rec.duration,l_null_value_number) = Nvl(p_duration,l_null_value_number)) Then
2417 Null;
2418 Else
2419 l_changed_flag := true;
2420 exit;
2421 End If;
2422
2423 If (Nvl(trim(trg_rec.duration_units),l_null_value) = Nvl(trim(p_duration_units),l_null_value)) Then
2424 Null;
2425 Else
2426 l_changed_flag := true;
2427 exit;
2428 End If;
2429
2430 If (Nvl(trim(trg_rec.status),l_null_value) = Nvl(trim(p_status),l_null_value)) Then
2431 Null;
2432 Else
2433 l_changed_flag := true;
2434 exit;
2435 End If;
2436
2437 If (Nvl(trg_rec.completion_date,l_null_value_date) = Nvl(p_completion_date,l_null_value_date)) Then
2438 Null;
2439 Else
2440 l_changed_flag := true;
2441 exit;
2442 End If;
2443
2444 If (Nvl(trim(trg_rec.award),l_null_value) = Nvl(trim(p_award),l_null_value)) Then
2445 Null;
2446 Else
2447 l_changed_flag := true;
2448 exit;
2449 End If;
2450
2451 If (Nvl(trim(trg_rec.rating),l_null_value) = Nvl(trim(p_rating),l_null_value)) Then
2452 Null;
2453 Else
2454 l_changed_flag := true;
2455 exit;
2456 End If;
2457
2458 If (Nvl(trg_rec.contact_id,l_null_value_number) = Nvl(p_contact_id,l_null_value_number)) Then
2459 Null;
2460 Else
2461 l_changed_flag := true;
2462 exit;
2463 End If;
2464
2465 If (Nvl(trim(trg_rec.nth_information_category),l_null_value) = Nvl(trim(p_nth_information_category),l_null_value)) Then
2466 If (Nvl(trim(trg_rec.nth_information1),l_null_value) = Nvl(trim(p_nth_information1),l_null_value)) Then
2467 Null;
2468 Else
2469 l_changed_flag := true;
2470 exit;
2471 End If;
2472
2473 If (Nvl(trim(trg_rec.nth_information2),l_null_value) = Nvl(trim(p_nth_information2),l_null_value)) Then
2474 Null;
2475 Else
2476 l_changed_flag := true;
2477 exit;
2478 End If;
2479
2480 If (Nvl(trim(trg_rec.nth_information3),l_null_value) = Nvl(trim(p_nth_information3),l_null_value)) Then
2481 Null;
2482 Else
2483 l_changed_flag := true;
2484 exit;
2485 End If;
2486
2487 If (Nvl(trim(trg_rec.nth_information4),l_null_value) = Nvl(trim(p_nth_information4),l_null_value)) Then
2488 Null;
2489 Else
2490 l_changed_flag := true;
2491 exit;
2492 End If;
2493
2494 If (Nvl(trim(trg_rec.nth_information5),l_null_value) = Nvl(trim(p_nth_information5),l_null_value)) Then
2495 Null;
2496 Else
2497 l_changed_flag := true;
2498 exit;
2499 End If;
2500
2501 If (Nvl(trim(trg_rec.nth_information6),l_null_value) = Nvl(trim(p_nth_information6),l_null_value)) Then
2502 Null;
2503 Else
2504 l_changed_flag := true;
2505 exit;
2506 End If;
2507
2508 If (Nvl(trim(trg_rec.nth_information7),l_null_value) = Nvl(trim(p_nth_information7),l_null_value)) Then
2509 Null;
2510 Else
2511 l_changed_flag := true;
2512 exit;
2513 End If;
2514
2515 If (Nvl(trim(trg_rec.nth_information8),l_null_value) = Nvl(trim(p_nth_information8),l_null_value)) Then
2516 Null;
2517 Else
2518 l_changed_flag := true;
2519 exit;
2520 End If;
2521
2522 If (Nvl(trim(trg_rec.nth_information9),l_null_value) = Nvl(trim(p_nth_information9),l_null_value)) Then
2523 Null;
2524 Else
2525 l_changed_flag := true;
2526 exit;
2527 End If;
2528
2529 If (Nvl(trim(trg_rec.nth_information10),l_null_value) = Nvl(trim(p_nth_information10),l_null_value)) Then
2530 Null;
2531 Else
2532 l_changed_flag := true;
2533 exit;
2534 End If;
2535
2536 If (Nvl(trim(trg_rec.nth_information11),l_null_value) = Nvl(trim(p_nth_information11),l_null_value)) Then
2537 Null;
2538 Else
2539 l_changed_flag := true;
2540 exit;
2541 End If;
2542
2543 If (Nvl(trim(trg_rec.nth_information12),l_null_value) = Nvl(trim(p_nth_information12),l_null_value)) Then
2544 Null;
2545 Else
2546 l_changed_flag := true;
2547 exit;
2548 End If;
2549
2550 If (Nvl(trim(trg_rec.nth_information13),l_null_value) = Nvl(trim(p_nth_information13),l_null_value)) Then
2551 Null;
2552 Else
2553 l_changed_flag := true;
2554 exit;
2555 End If;
2556
2557 If (Nvl(trim(trg_rec.nth_information14),l_null_value) = Nvl(trim(p_nth_information14),l_null_value)) Then
2558 Null;
2559 Else
2560 l_changed_flag := true;
2561 exit;
2562 End If;
2563
2564 If (Nvl(trim(trg_rec.nth_information15),l_null_value) = Nvl(trim(p_nth_information15),l_null_value)) Then
2565 Null;
2566 Else
2567 l_changed_flag := true;
2568 exit;
2569 End If;
2570
2571 If (Nvl(trim(trg_rec.nth_information16),l_null_value) = Nvl(trim(p_nth_information16),l_null_value)) Then
2572 Null;
2573 Else
2574 l_changed_flag := true;
2575 exit;
2576 End If;
2577
2578 If (Nvl(trim(trg_rec.nth_information17),l_null_value) = Nvl(trim(p_nth_information17),l_null_value)) Then
2579 Null;
2580 Else
2581 l_changed_flag := true;
2582 exit;
2583 End If;
2584
2585 If (Nvl(trim(trg_rec.nth_information18),l_null_value) = Nvl(trim(p_nth_information18),l_null_value)) Then
2586 Null;
2587 Else
2588 l_changed_flag := true;
2589 exit;
2590 End If;
2591
2592 If (Nvl(trim(trg_rec.nth_information19),l_null_value) = Nvl(trim(p_nth_information19),l_null_value)) Then
2593 Null;
2594 Else
2595 l_changed_flag := true;
2596 exit;
2597 End If;
2598
2599 If (Nvl(trim(trg_rec.nth_information20),l_null_value) = Nvl(trim(p_nth_information20),l_null_value)) Then
2600 Null;
2601 Else
2602 l_changed_flag := true;
2603 exit;
2604 End If;
2605
2606 Else
2607 l_changed_flag := true;
2608 exit;
2609 End If;
2610 END LOOP;
2611
2612 If (l_changed_flag) Then
2613 p_result := 1;
2614 Else
2615 p_result := 0;
2616 End If;
2617
2618 hr_utility.set_location('Leaving:'||g_package||l_proc, 20);
2619 END check_changes;
2620
2621
2622
2623
2624 Procedure chk_pending_approval
2625 (p_nota_history_id in VARCHAR2
2626 ,p_person_id in number ) is
2627
2628 --
2629 l_proc varchar2(72) := g_package||'chk_pending_approval';
2630 l_temp_historyid varchar2(100) := null;
2631
2632
2633
2634 Cursor cur_get_pending_trn_step_id IS
2635 Select
2636 hrtrns.transaction_step_id
2637 From
2638 wf_item_activity_statuses process
2639 ,wf_item_attribute_values attribute2
2640 ,wf_process_activities activity
2641 ,hr_api_transaction_steps hrtrns
2642 Where
2643 activity.activity_name = 'OTA_ADDTRNG_JSP_PRC'
2644 and activity.process_item_type = 'HRSSA'
2645 and activity.activity_item_type = 'HRSSA'
2646 and activity.instance_id = process.process_activity
2647 and process.activity_status = 'ACTIVE'
2648 and process.item_type = 'HRSSA'
2649 and hrtrns.update_person_id = p_person_id
2650 and process.item_key = attribute2.item_key
2651 and attribute2.item_type = process.item_type
2652 and attribute2.name = 'TRAN_SUBMIT'
2653 and attribute2.text_value = 'Y'
2654 and process.item_key = hrtrns.item_key
2655 and trim(upper(hrtrns.api_name)) = trim(upper(g_package||'.PROCESS_API'))
2656 and hrtrns.item_type = 'HRSSA';
2657
2658
2659
2660 BEGIN
2661
2662 hr_utility.set_location('Entering:'||l_proc, 5);
2663
2664 FOR c in cur_get_pending_trn_step_id --bug 3590613
2665 LOOP
2666
2667 l_temp_historyid :=
2668 hr_transaction_api.get_varchar2_value
2669 (p_transaction_step_id => c.transaction_step_id
2670 ,p_name => 'P_HISTORYID');
2671
2672 if (l_temp_historyid is not null and l_temp_historyid = p_nota_history_id) then
2673
2674 exit;
2675
2676 null;
2677
2678 end if;
2679
2680
2681 End LOOP;
2682
2683 if (l_temp_historyid is not null and l_temp_historyid = p_nota_history_id) then
2684
2685
2686 fnd_message.set_name('OTA','OTA_13967_ADD_TRNG_PA_SS');
2687 fnd_message.raise_error;
2688
2689 end if;
2690
2691 hr_utility.set_location('Entering:'||l_proc, 30);
2692
2693
2694 end chk_pending_approval;
2695
2696
2697
2698
2699
2700
2701
2702
2703 END ota_add_training_ss;