[Home] [Help]
PACKAGE BODY: APPS.OTA_CERT_APPROVAL_SS
Source
1 PACKAGE BODY OTA_CERT_APPROVAL_SS AS
2 /* $Header: otcrtrev.pkb 120.4 2005/10/04 02:31 dbatra noship $*/
3
4 g_package varchar2(30) := 'OTA_CERT_APPROVAL_SS';
5
6 -- ---------------------------------------------------------------------------
7 -- |----------------------< get_approval_req >--------------------------|
8 -- ---------------------------------------------------------------------------
9 --
10 PROCEDURE get_approval_req ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
11 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
12 actid IN NUMBER,
13 funcmode IN VARCHAR2,
14 resultout OUT nocopy VARCHAR2 )
15 IS
16
17 l_item_value varchar2(200);
18 l_ntf_url varchar2(4000);
19
20 l_cert_id number(15);
21
22 Cursor get_cert_details (crs_certification_id number)is
23 select ctl.name, ctb.initial_completion_date
24 from ota_Certifications_tl ctl , ota_Certifications_b ctb
25 where ctl.certification_id = ctb.certification_id
26 and ctl.Language= USERENV('LANG')
27 and ctl.certification_id =crs_certification_id;
28
29 l_certification_name ota_certifications_tl.name%type;
30 l_comp_date varchar2(100);
31
32
33
34 BEGIN
35 hr_utility.set_location('ENTERING get_approval_req', 10);
36 IF (funcmode='RUN') THEN
37
38
39
40
41 l_item_value := wf_engine.getItemAttrText(itemtype => itemtype
42 ,itemkey => itemkey
43 , aname => 'HR_RUNTIME_APPROVAL_REQ_FLAG');
44
45
46
47 if l_item_value = 'NO' then
48
49 /* l_cert_id := wf_engine.getItemAttrNumber(itemtype => itemtype
50 ,itemkey => itemkey
51 , aname => 'BOOKING_ID');
52
53 open get_cert_details(l_cert_id);
54 fetch get_cert_details into l_certification_name,l_comp_date;
55 close get_cert_details;
56
57 wf_engine.setItemAttrText(itemtype,itemkey,'OTA_ACTIVITY_VERSION_NAME',l_certification_name);
58 wf_engine.setItemAttrText(itemtype,itemkey,'OTA_COURSE_START_DATE',l_comp_date);
59
60 */
61
62 resultout:='COMPLETE:N';
63
64
65 else
66
67 resultout:='COMPLETE:Y';
68
69
70 end if;
71 hr_utility.trace('l_resultout' || resultout);
72
73 RETURN;
74 END IF; --RUN
75
76 IF (funcmode='CANCEL') THEN
77 resultout:='COMPLETE';
78 RETURN;
79 END IF;
80 Exception
81
82 when others then
83 hr_utility.set_location('ENTERING exception get_approval_req', 10);
84
85
86
87 end get_approval_req;
88
89
90 procedure save_cert_enroll_detail(
91 p_login_person_id in number,
92 p_item_type in varchar2,
93 p_item_key in varchar2,
94 p_activity_id in number,
95 p_certification_id in varchar2,
96 p_person_id in number,
97 p_certification_status_code in varchar2,
98 p_completion_date in varchar2 default null,
99 p_UNENROLLMENT_DATE in varchar2 default null,
100 p_EXPIRATION_DATE in varchar2 default null,
101 p_EARLIEST_ENROLL_DATE in varchar2 default null,
102 p_IS_HISTORY_FLAG in varchar2,
103 p_business_group_id in varchar2 default null,
104 p_attribute_category in varchar2 default null,
105 p_attribute1 in varchar2 default null,
106 p_attribute2 in varchar2 default null,
107 p_attribute3 in varchar2 default null,
108 p_attribute4 in varchar2 default null,
109 p_attribute5 in varchar2 default null,
110 p_attribute6 in varchar2 default null,
111 p_attribute7 in varchar2 default null,
112 p_attribute8 in varchar2 default null,
113 p_attribute9 in varchar2 default null,
114 p_attribute10 in varchar2 default null,
115 p_attribute11 in varchar2 default null,
116 p_attribute12 in varchar2 default null,
117 p_attribute13 in varchar2 default null,
118 p_attribute14 in varchar2 default null,
119 p_attribute15 in varchar2 default null,
120 p_attribute16 in varchar2 default null,
121 p_attribute17 in varchar2 default null,
122 p_attribute18 in varchar2 default null,
123 p_attribute19 in varchar2 default null,
124 p_attribute20 in varchar2 default null,
125 p_from in varchar2,
126 p_error_message OUT NOCOPY VARCHAR2
127 )
128 as
129
130 l_transaction_id number default null;
131 l_transaction_step_id number default null;
132 l_trans_obj_vers_num number default null;
133 l_count integer default 0;
134 l_transaction_table hr_transaction_ss.transaction_table;
135 l_review_item_name varchar2(50);
136 l_message_number VARCHAR2(10);
137 l_result varchar2(100) default null;
138 l_old_transaction_step_id number;
139 l_old_object_version_number number;
140
141 begin
142
143 -- First, check if transaction id exists or not
144 l_transaction_id := hr_transaction_ss.get_transaction_id
145 (p_item_type => p_item_type
146 ,p_item_key => p_item_key);
147
148 --
149 IF l_transaction_id is null THEN
150 -- Start a Transaction
151 hr_transaction_ss.start_transaction
152 (itemtype => p_item_type
153 ,itemkey => p_item_key
154 ,actid => p_activity_id
155 ,funmode => 'RUN'
156 ,p_login_person_id => p_login_person_id
157 ,result => l_result);
158
159 l_transaction_id := hr_transaction_ss.get_transaction_id
160 (p_item_type => p_item_type
161 ,p_item_key => p_item_key);
162 END IF;
163
164 --
165 -- Delete transaction step if exist
166 --
167
168 if (hr_transaction_api.transaction_step_exist (p_item_type => p_item_type
169 ,p_item_key => p_item_key
170 ,p_activity_id => p_activity_id) ) then
171
172 hr_transaction_api.get_transaction_step_info(p_item_type => p_item_type
173 ,p_item_key => p_item_key
174 ,p_activity_id => p_activity_id
175 ,p_transaction_step_id => l_old_transaction_step_id
176 ,p_object_version_number => l_old_object_version_number);
177
178
179 hr_transaction_api.delete_transaction_step(p_validate => false
180 ,p_transaction_step_id => l_old_transaction_step_id
181 ,p_person_id => p_login_person_id
182 ,p_object_version_number => l_old_object_version_number);
183
184 end if;
185
186 --
187 -- Create a transaction step
188 --
189 hr_transaction_api.create_transaction_step
190 (p_validate => false
191 ,p_creator_person_id => p_login_person_id
192 ,p_transaction_id => l_transaction_id
193 ,p_api_name => g_package || '.PROCESS_API'
194 ,p_item_type => p_item_type
195 ,p_item_key => p_item_key
199
196 ,p_activity_id => p_activity_id
197 ,p_transaction_step_id => l_transaction_step_id
198 ,p_object_version_number => l_trans_obj_vers_num);
200
201 l_count := 1;
202 l_transaction_table(l_count).param_name := 'P_CERTIFICATIONID';
203 l_transaction_table(l_count).param_value := p_certification_id;
204 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
205
206 /* l_count := l_count + 1;
207 l_transaction_table(l_count).param_name := 'P_CERTIFICATIONCODE';
208 l_transaction_table(l_count).param_value := p_certification_status_code;
209 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
210 */
211
212 l_count := l_count + 1;
213 l_transaction_table(l_count).param_name := 'P_HISTORYFLAG';
214 l_transaction_table(l_count).param_value := nvl(p_is_history_flag, 'N');
215 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
216
217 l_count := l_count + 1;
218 l_transaction_table(l_count).param_name := 'P_REVIEW_ACTID';
219 l_transaction_table(l_count).param_value := p_activity_id;
220 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
221
222 l_count := l_count + 1;
223 l_transaction_table(l_count).param_name := 'P_PERSONID';
224 l_transaction_table(l_count).param_value := p_person_Id;
225 l_transaction_table(l_count).param_data_type := 'NUMBER';
226
227
228 l_count := l_count + 1;
229 l_transaction_table(l_count).param_name := 'P_BUSINESSGROUPID';
230 l_transaction_table(l_count).param_value := ota_general.get_business_group_id();
231 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
232
233
234
235 l_count := l_count + 1;
236 l_transaction_table(l_count).param_name := 'P_FROM';
237 l_transaction_table(l_count).param_value := p_from;
238 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
239
240
241 l_count := l_count + 1;
242 l_transaction_table(l_count).param_name := 'P_ATTRIBUTECATEGORY';
243 l_transaction_table(l_count).param_value := p_attribute_category;
244 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
245
246
247 l_count := l_count + 1;
248 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE1';
249 l_transaction_table(l_count).param_value := p_attribute1;
250 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
251
252 l_count := l_count + 1;
253 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE2';
254 l_transaction_table(l_count).param_value := p_attribute2;
255 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
256
257 l_count := l_count + 1;
258 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE3';
259 l_transaction_table(l_count).param_value := p_attribute3;
260 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
261
262 l_count := l_count + 1;
263 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE4';
264 l_transaction_table(l_count).param_value := p_attribute4;
265 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
266
267 l_count := l_count + 1;
268 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE5';
269 l_transaction_table(l_count).param_value := p_attribute5;
270 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
271
272 l_count := l_count + 1;
273 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE6';
274 l_transaction_table(l_count).param_value := p_attribute6;
275 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
276
277 l_count := l_count + 1;
278 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE7';
279 l_transaction_table(l_count).param_value := p_attribute7;
280 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
281
282 l_count := l_count + 1;
283 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE8';
284 l_transaction_table(l_count).param_value := p_attribute8;
285 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
286
287 l_count := l_count + 1;
288 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE9';
289 l_transaction_table(l_count).param_value := p_attribute9;
290 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
291
292 l_count := l_count + 1;
293 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE10';
294 l_transaction_table(l_count).param_value := p_attribute10;
295 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
296
297 l_count := l_count + 1;
298 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE11';
299 l_transaction_table(l_count).param_value := p_attribute11;
300 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
301
302 l_count := l_count + 1;
303 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE12';
304 l_transaction_table(l_count).param_value := p_attribute12;
305 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
306
307 l_count := l_count + 1;
308 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE13';
309 l_transaction_table(l_count).param_value := p_attribute13;
310 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
311
312 l_count := l_count + 1;
313 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE14';
314 l_transaction_table(l_count).param_value := p_attribute14;
315 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
316
317 l_count := l_count + 1;
318 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE15';
319 l_transaction_table(l_count).param_value := p_attribute15;
320 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
321
322 l_count := l_count + 1;
323 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE16';
324 l_transaction_table(l_count).param_value := p_attribute16;
325 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
326
327 l_count := l_count + 1;
328 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE17';
329 l_transaction_table(l_count).param_value := p_attribute17;
330 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
331
332 l_count := l_count + 1;
333 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE18';
334 l_transaction_table(l_count).param_value := p_attribute18;
335 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
336
337 l_count := l_count + 1;
338 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE19';
339 l_transaction_table(l_count).param_value := p_attribute19;
340 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
341
342 l_count := l_count + 1;
343 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE20';
344 l_transaction_table(l_count).param_value := p_attribute20;
345 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
346
347
348 l_count := l_count + 1;
349 l_transaction_table(l_count).param_name := 'P_COMPLETION_DATE';
350 l_transaction_table(l_count).param_value := p_completion_date;
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_UNENROLLMENTDATE';
355 l_transaction_table(l_count).param_value := p_unenrollment_date;
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_EXPIRATIONDATE';
360 l_transaction_table(l_count).param_value := p_expiration_date;
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_EARLIESTENROLLDATE';
365 l_transaction_table(l_count).param_value := p_earliest_enroll_date;
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_LOGINPERSONID';
370 l_transaction_table(l_count).param_value := p_login_person_Id;
371 l_transaction_table(l_count).param_data_type := 'NUMBER';
372
373 hr_approval_wf.create_item_attrib_if_notexist
374 (p_item_type => p_item_type
375 ,p_item_key => p_item_key
376 ,p_name => 'OTA_TRANSACTION_STEP_ID');
377
378 WF_ENGINE.setitemattrnumber(p_item_type,
379 p_item_key,
380 'OTA_TRANSACTION_STEP_ID',
381 l_transaction_step_id);
382
383
384 If p_from='REVIEW' Then
385 hr_approval_wf.create_item_attrib_if_notexist
386 (p_item_type => p_item_type
387 ,p_item_key => p_item_key
388 ,p_name => 'OTA_CERTIFICATION_ID');
389
390 WF_ENGINE.setitemattrnumber(p_item_type,
391 p_item_key,
392 'OTA_CERTIFICATION_ID',
393 p_certification_id);
394
395 -- bug 4146681
396 WF_ENGINE.setitemattrtext(p_item_type,
397 p_item_key,
398 'HR_RESTRICT_EDIT_ATTR',
399 'Y');
400 --bug 4146681
401
402 End If;
403
404 hr_transaction_ss.save_transaction_step
405 (p_item_type => p_item_type
406 ,p_item_key => p_item_key
407 ,p_actid => p_activity_id
408 ,p_login_person_id => p_login_person_id
409 ,p_transaction_step_id => l_transaction_step_id
410 ,p_api_name => g_package || '.PROCESS_API'
411 ,p_transaction_data => l_transaction_table);
412
413
414 EXCEPTION
415 WHEN hr_utility.hr_error THEN
416 -- -------------------------------------------
417 -- an application error has been raised so we must
418 -- redisplay the web form to display the error
419 -- --------------------------------------------
420 hr_message.provide_error;
421 l_message_number := hr_message.last_message_number;
422 IF l_message_number = 'APP-7165' OR
423 l_message_number = 'APP-7155' THEN
424 --populate the p_error_message out variable
425 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
426 p_error_message => p_error_message,
427 p_attr_name => 'Page',
428 p_app_short_name => 'PER',
429 p_message_name => 'HR_UPDATE_NOT_ALLOWED');
430 ELSE
431 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
432 p_error_message => p_error_message);
433 END IF;
434 WHEN OTHERS THEN
435 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
436 p_error_message => p_error_message);
437
438 end save_cert_enroll_detail;
439
440
441
442
443 PROCEDURE get_add_enr_dtl_data_from_tt
444 (p_item_type in varchar2
445 ,p_item_key in varchar2
446 ,p_activity_id in varchar2
447 -- ,p_trans_rec_count out nocopy number
448 -- ,p_person_id out nocopy number
449 ,p_add_enroll_detail_data out nocopy varchar2
450 ) is
451
452
453
454 l_add_enroll_detail_data varchar2(4000);
455 l_trans_step_id number;
456
457
458
459 BEGIN
460
461 l_trans_step_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type
462 ,itemkey => p_item_key
463 ,aname => 'OTA_TRANSACTION_STEP_ID');
464
465
466 get_add_enr_dtl_data_from_tt(
467 p_transaction_step_id => l_trans_step_id
468 -- p_transaction_step_id => l_trans_step_id
469 ,p_add_enroll_detail_data => l_add_enroll_detail_data);
470
471
472 p_add_enroll_detail_data := l_add_enroll_detail_data;
473
474
475 EXCEPTION
476 WHEN OTHERS THEN
477 RAISE;
478
479 END get_add_enr_dtl_data_from_tt;
480
481
482 procedure get_add_enr_dtl_data_from_tt
483 (p_transaction_step_id in number
484 ,p_add_enroll_detail_data out nocopy varchar2
485 )
486 is
487
488
489 l_certification_id ota_cert_enrollments.certification_id%type;
490
491 l_attribute_category ota_cert_enrollments.attribute_category%type;
492 l_attribute1 ota_cert_enrollments.attribute1%type;
493 l_attribute2 ota_cert_enrollments.attribute1%type;
494 l_attribute3 ota_cert_enrollments.attribute1%type;
495 l_attribute4 ota_cert_enrollments.attribute1%type;
496 l_attribute5 ota_cert_enrollments.attribute1%type;
497 l_attribute6 ota_cert_enrollments.attribute1%type;
498 l_attribute7 ota_cert_enrollments.attribute1%type;
499 l_attribute8 ota_cert_enrollments.attribute1%type;
500 l_attribute9 ota_cert_enrollments.attribute1%type;
501 l_attribute10 ota_cert_enrollments.attribute1%type;
502 l_attribute11 ota_cert_enrollments.attribute1%type;
503 l_attribute12 ota_cert_enrollments.attribute1%type;
504 l_attribute13 ota_cert_enrollments.attribute1%type;
505 l_attribute14 ota_cert_enrollments.attribute1%type;
506 l_attribute15 ota_cert_enrollments.attribute1%type;
507 l_attribute16 ota_cert_enrollments.attribute1%type;
508 l_attribute17 ota_cert_enrollments.attribute1%type;
509 l_attribute18 ota_cert_enrollments.attribute1%type;
510 l_attribute19 ota_cert_enrollments.attribute1%type;
511 l_attribute20 ota_cert_enrollments.attribute1%type;
512
513 begin
514
515
516 l_certification_id := hr_transaction_api.get_varchar2_value
517 (p_transaction_step_id => p_transaction_step_id
518 ,p_name => 'P_CERTIFICATIONID');
519
520 l_attribute_category := hr_transaction_api.get_varchar2_value
521 (p_transaction_step_id => p_transaction_step_id
522 ,p_name => 'P_ATTRIBUTECATEGORY');
523 l_attribute1 := hr_transaction_api.get_varchar2_value
524 (p_transaction_step_id => p_transaction_step_id
525 ,p_name => 'P_ATTRIBUTE1');
526 l_attribute2 := hr_transaction_api.get_varchar2_value
527 (p_transaction_step_id => p_transaction_step_id
528 ,p_name => 'P_ATTRIBUTE2');
529 l_attribute3 := hr_transaction_api.get_varchar2_value
530 (p_transaction_step_id => p_transaction_step_id
531 ,p_name => 'P_ATTRIBUTE3');
532 l_attribute4 := hr_transaction_api.get_varchar2_value
533 (p_transaction_step_id => p_transaction_step_id
534 ,p_name => 'P_ATTRIBUTE4');
535 l_attribute5 := hr_transaction_api.get_varchar2_value
536 (p_transaction_step_id => p_transaction_step_id
537 ,p_name => 'P_ATTRIBUTE5');
538 l_attribute6 := hr_transaction_api.get_varchar2_value
539 (p_transaction_step_id => p_transaction_step_id
540 ,p_name => 'P_ATTRIBUTE6');
541 l_attribute7 := hr_transaction_api.get_varchar2_value
542 (p_transaction_step_id => p_transaction_step_id
543 ,p_name => 'P_ATTRIBUTE7');
544 l_attribute8 := hr_transaction_api.get_varchar2_value
545 (p_transaction_step_id => p_transaction_step_id
546 ,p_name => 'P_ATTRIBUTE8');
547 l_attribute9 := hr_transaction_api.get_varchar2_value
548 (p_transaction_step_id => p_transaction_step_id
549 ,p_name => 'P_ATTRIBUTE9');
550 l_attribute10 := hr_transaction_api.get_varchar2_value
551 (p_transaction_step_id => p_transaction_step_id
552 ,p_name => 'P_ATTRIBUTE10');
553 l_attribute11 := hr_transaction_api.get_varchar2_value
554 (p_transaction_step_id => p_transaction_step_id
555 ,p_name => 'P_ATTRIBUTE11');
556 l_attribute12 := hr_transaction_api.get_varchar2_value
557 (p_transaction_step_id => p_transaction_step_id
558 ,p_name => 'P_ATTRIBUTE12');
559 l_attribute13 := hr_transaction_api.get_varchar2_value
560 (p_transaction_step_id => p_transaction_step_id
561 ,p_name => 'P_ATTRIBUTE13');
562 l_attribute14 := hr_transaction_api.get_varchar2_value
563 (p_transaction_step_id => p_transaction_step_id
564 ,p_name => 'P_ATTRIBUTE14');
565 l_attribute15 := hr_transaction_api.get_varchar2_value
566 (p_transaction_step_id => p_transaction_step_id
567 ,p_name => 'P_ATTRIBUTE15');
568 l_attribute16 := hr_transaction_api.get_varchar2_value
569 (p_transaction_step_id => p_transaction_step_id
570 ,p_name => 'P_ATTRIBUTE16');
571 l_attribute17 := hr_transaction_api.get_varchar2_value
572 (p_transaction_step_id => p_transaction_step_id
573 ,p_name => 'P_ATTRIBUTE17');
574 l_attribute18 := hr_transaction_api.get_varchar2_value
575 (p_transaction_step_id => p_transaction_step_id
576 ,p_name => 'P_ATTRIBUTE18');
577 l_attribute19 := hr_transaction_api.get_varchar2_value
578 (p_transaction_step_id => p_transaction_step_id
579 ,p_name => 'P_ATTRIBUTE19');
580 l_attribute20 := hr_transaction_api.get_varchar2_value
581 (p_transaction_step_id => p_transaction_step_id
582 ,p_name => 'P_ATTRIBUTE20');
583
584 --
585 -- Now string all the retreived items into p_add_enroll_detail_data
586
587 --
588
589 p_add_enroll_detail_data := nvl(l_certification_id,0)
590 ||'^'||nvl(l_attribute_category,'null')
591 ||'^'||nvl(l_attribute1,'null')
592 ||'^'||nvl(l_attribute2,'null')
593 ||'^'||nvl(l_attribute3,'null')
594 ||'^'||nvl(l_attribute4,'null')
595 ||'^'||nvl(l_attribute5,'null')
596 ||'^'||nvl(l_attribute6,'null')
597 ||'^'||nvl(l_attribute7,'null')
598 ||'^'||nvl(l_attribute8,'null')
599 ||'^'||nvl(l_attribute9,'null')
600 ||'^'||nvl(l_attribute10,'null')
601 ||'^'||nvl(l_attribute11,'null')
602 ||'^'||nvl(l_attribute12,'null')
603 ||'^'||nvl(l_attribute13,'null')
604 ||'^'||nvl(l_attribute14,'null')
605 ||'^'||nvl(l_attribute15,'null')
606 ||'^'||nvl(l_attribute16,'null')
607 ||'^'||nvl(l_attribute17,'null')
608 ||'^'||nvl(l_attribute18,'null')
609 ||'^'||nvl(l_attribute19,'null')
610 ||'^'||nvl(l_attribute20,'null');
611
612
613 EXCEPTION
614 WHEN OTHERS THEN
615 RAISE;
616
617 END get_add_enr_dtl_data_from_tt;
618
619
620
621
622
623 PROCEDURE get_review_data
624 (p_item_type in varchar2
625 ,p_item_key in varchar2
626 ,p_activity_id in varchar2
627 ,p_review_data out nocopy varchar2
628 ) is
629
630
631
632 l_review_data varchar2(4000);
633 l_trans_step_id number;
634
635
636 BEGIN
637
638
639 --added new
640 l_trans_step_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type
641 ,itemkey => p_item_key
642 ,aname => 'OTA_TRANSACTION_STEP_ID');
643 get_review_data(
644 p_transaction_step_id => l_trans_step_id
645 ,p_review_data => l_review_data);
646
647
648 p_review_data := l_review_data;
649
650
651 EXCEPTION
652 WHEN OTHERS THEN
653 RAISE;
654
655 END get_review_data;
656
657
658
659 procedure get_review_data
660 (p_transaction_step_id in number
661 ,p_review_data out nocopy varchar2
662 )
663 is
664
665 l_name ota_certifications_vl.name%type;
666 l_description ota_certifications_vl.description%type;
667 l_objectives ota_certifications_vl.objectives%type;
668 l_purpose ota_certifications_vl.purpose%type;
669 l_init_comp_date ota_certifications_vl.initial_completion_date%type;
670 l_init_comp_dur varchar2(100);
671 l_renewal_dur varchar2(100);
672 l_notif_days varchar2(100);
673 l_initial_comments ota_certifications_vl.initial_period_comments%type;
674 l_certification_id ota_cert_enrollments.certification_id%type;
675
676 /* cursor get_certification_info(crs_Certification_id number)
677 is
678 Select name,description,objectives,purpose,
679 to_char(initial_completion_date, fnd_profile.value('ICX_DATE_FORMAT_MASK')),
680 initial_completion_duration || initial_compl_duration_units,
681 renewal_duration || renewal_duration_units,
682 notify_days_before_expire,initial_period_comments
683 from ota_certifications_vl
684 where certification_id = crs_certification_id;
685 */
686
687 begin
688
689
690 l_certification_id := hr_transaction_api.get_varchar2_value
691 (p_transaction_step_id => p_transaction_step_id
692 ,p_name => 'P_CERTIFICATIONID');
693
694 /*
695 open get_certification_info(l_certification_id);
696 fetch get_certification_info into l_name,l_description,
697 l_objectives,l_purpose,l_init_comp_date,
698 l_init_comp_dur,l_renewal_dur,l_notif_days,l_initial_comments;
699 close get_certification_info;
700 */
701
702 p_review_data := nvl(l_certification_id,'0') ;
703 /*||'^'||nvl(l_name,'null')
704 ||'^'||nvl(l_description,'null')
705 ||'^'||nvl(l_objectives,'null')
706 ||'^'||nvl(l_purpose,'null')
707 ||'^'||nvl(l_init_comp_date,'null')
708 ||'^'||nvl(l_init_comp_dur,'null')
709 ||'^'||nvl(l_renewal_dur,'null')
710 ||'^'||nvl(l_notif_days,'null')
711 ||'^'||nvl(l_initial_comments,'null');*/
712
713
714
715 EXCEPTION
716 WHEN OTHERS THEN
717 RAISE;
718
719 END get_review_data;
720
721 procedure create_cert_enrollment_tt
722 (p_validate IN BOOLEAN, p_transaction_step_id IN NUMBER)
723 is
724
725 l_item_type HR_API_TRANSACTION_STEPS.ITEM_TYPE%TYPE;
726 l_item_key HR_API_TRANSACTION_STEPS.ITEM_KEY%TYPE;
727 l_activity_id HR_API_TRANSACTION_STEPS.ACTIVITY_ID%TYPE;
728 l_cert_enrollment_id number;
729 l_object_version_number number;
730 l_effective_date date := trunc(sysdate);
731
732 l_certification_id ota_cert_enrollments.certification_id%type;
733 l_certification_status_code ota_cert_enrollments.certification_status_code%type;
734 l_IS_HISTORY_FLAG ota_cert_enrollments.is_history_flag%type;
735 l_person_id ota_cert_enrollments.person_id%type;
736 --l_contact_id
737 l_completion_date varchar2(100);
738 l_business_group_id ota_cert_enrollments.business_group_id%type;
739 l_UNENROLLMENT_DATE varchar2(100);
740 l_EXPIRATION_DATE varchar2(100);
741 l_EARLIEST_ENROLL_DATE varchar2(100);
742 l_attribute_category ota_cert_enrollments.attribute_category%type;
743 l_attribute1 ota_cert_enrollments.attribute1%type;
744 l_attribute2 ota_cert_enrollments.attribute1%type;
745 l_attribute3 ota_cert_enrollments.attribute1%type;
746 l_attribute4 ota_cert_enrollments.attribute1%type;
747 l_attribute5 ota_cert_enrollments.attribute1%type;
748 l_attribute6 ota_cert_enrollments.attribute1%type;
749 l_attribute7 ota_cert_enrollments.attribute1%type;
750 l_attribute8 ota_cert_enrollments.attribute1%type;
751 l_attribute9 ota_cert_enrollments.attribute1%type;
752 l_attribute10 ota_cert_enrollments.attribute1%type;
753 l_attribute11 ota_cert_enrollments.attribute1%type;
754 l_attribute12 ota_cert_enrollments.attribute1%type;
755 l_attribute13 ota_cert_enrollments.attribute1%type;
756 l_attribute14 ota_cert_enrollments.attribute1%type;
757 l_attribute15 ota_cert_enrollments.attribute1%type;
758 l_attribute16 ota_cert_enrollments.attribute1%type;
759 l_attribute17 ota_cert_enrollments.attribute1%type;
760 l_attribute18 ota_cert_enrollments.attribute1%type;
761 l_attribute19 ota_cert_enrollments.attribute1%type;
762 l_attribute20 ota_cert_enrollments.attribute1%type;
763
764 Cursor get_cert_name (crs_certification_id number)is
765 select ctl.name
769 and ctl.certification_id =crs_certification_id;
766 from ota_Certifications_tl ctl , ota_Certifications_b ctb
767 where ctl.certification_id = ctb.certification_id
768 and ctl.Language= USERENV('LANG')
770
771 l_certification_name ota_certifications_tl.name%type;
772
773 l_approval_req_flag varchar2(1);
774
775 begin
776 -- bug 4636199
777 if p_validate then
778 l_approval_req_flag := 'N';
779 else
780 l_approval_req_flag :='A';
781
782 end if;
783
784 l_certification_id := hr_transaction_api.get_varchar2_value
785 (p_transaction_step_id => p_transaction_step_id
786 ,p_name => 'P_CERTIFICATIONID');
787 /*l_certification_status_code := hr_transaction_api.get_varchar2_value
788 (p_transaction_step_id => p_transaction_step_id
789 ,p_name => 'P_CERTIFICATIONCODE');*/
790 l_IS_HISTORY_FLAG := hr_transaction_api.get_varchar2_value
791 (p_transaction_step_id => p_transaction_step_id
792 ,p_name => 'P_HISTORYFLAG');
793 l_person_id := hr_transaction_api.get_Number_value
794 (p_transaction_step_id => p_transaction_step_id
795 ,p_name => 'P_PERSONID');
799 l_attribute_category := hr_transaction_api.get_varchar2_value
796 l_business_group_id := hr_transaction_api.get_varchar2_value
797 (p_transaction_step_id => p_transaction_step_id
798 ,p_name => 'P_BUSINESSGROUPID');
800 (p_transaction_step_id => p_transaction_step_id
801 ,p_name => 'P_ATTRIBUTECATEGORY');
802 l_attribute1 := hr_transaction_api.get_varchar2_value
803 (p_transaction_step_id => p_transaction_step_id
804 ,p_name => 'P_ATTRIBUTE1');
805 l_attribute2 := hr_transaction_api.get_varchar2_value
806 (p_transaction_step_id => p_transaction_step_id
807 ,p_name => 'P_ATTRIBUTE2');
808 l_attribute3 := hr_transaction_api.get_varchar2_value
809 (p_transaction_step_id => p_transaction_step_id
810 ,p_name => 'P_ATTRIBUTE3');
811 l_attribute4 := hr_transaction_api.get_varchar2_value
812 (p_transaction_step_id => p_transaction_step_id
813 ,p_name => 'P_ATTRIBUTE4');
814 l_attribute5 := hr_transaction_api.get_varchar2_value
815 (p_transaction_step_id => p_transaction_step_id
816 ,p_name => 'P_ATTRIBUTE5');
817 l_attribute6 := hr_transaction_api.get_varchar2_value
818 (p_transaction_step_id => p_transaction_step_id
819 ,p_name => 'P_ATTRIBUTE6');
820 l_attribute7 := hr_transaction_api.get_varchar2_value
821 (p_transaction_step_id => p_transaction_step_id
822 ,p_name => 'P_ATTRIBUTE7');
823 l_attribute8 := hr_transaction_api.get_varchar2_value
824 (p_transaction_step_id => p_transaction_step_id
825 ,p_name => 'P_ATTRIBUTE8');
826 l_attribute9 := hr_transaction_api.get_varchar2_value
827 (p_transaction_step_id => p_transaction_step_id
828 ,p_name => 'P_ATTRIBUTE9');
829 l_attribute10 := hr_transaction_api.get_varchar2_value
830 (p_transaction_step_id => p_transaction_step_id
831 ,p_name => 'P_ATTRIBUTE10');
832 l_attribute11 := hr_transaction_api.get_varchar2_value
833 (p_transaction_step_id => p_transaction_step_id
834 ,p_name => 'P_ATTRIBUTE11');
835 l_attribute12 := hr_transaction_api.get_varchar2_value
836 (p_transaction_step_id => p_transaction_step_id
837 ,p_name => 'P_ATTRIBUTE12');
838 l_attribute13 := hr_transaction_api.get_varchar2_value
839 (p_transaction_step_id => p_transaction_step_id
840 ,p_name => 'P_ATTRIBUTE13');
841 l_attribute14 := hr_transaction_api.get_varchar2_value
842 (p_transaction_step_id => p_transaction_step_id
843 ,p_name => 'P_ATTRIBUTE14');
844 l_attribute15 := hr_transaction_api.get_varchar2_value
845 (p_transaction_step_id => p_transaction_step_id
846 ,p_name => 'P_ATTRIBUTE15');
847 l_attribute16 := hr_transaction_api.get_varchar2_value
848 (p_transaction_step_id => p_transaction_step_id
849 ,p_name => 'P_ATTRIBUTE16');
850 l_attribute17 := hr_transaction_api.get_varchar2_value
851 (p_transaction_step_id => p_transaction_step_id
852 ,p_name => 'P_ATTRIBUTE17');
853 l_attribute18 := hr_transaction_api.get_varchar2_value
854 (p_transaction_step_id => p_transaction_step_id
855 ,p_name => 'P_ATTRIBUTE18');
856 l_attribute19 := hr_transaction_api.get_varchar2_value
857 (p_transaction_step_id => p_transaction_step_id
858 ,p_name => 'P_ATTRIBUTE19');
859 l_attribute20 := hr_transaction_api.get_varchar2_value
860 (p_transaction_step_id => p_transaction_step_id
861 ,p_name => 'P_ATTRIBUTE20');
862 l_completion_date := hr_transaction_api.get_varchar2_value
863 (p_transaction_step_id => p_transaction_step_id
864 ,p_name => 'P_COMPLETIONDATE');
865 l_UNENROLLMENT_DATE := hr_transaction_api.get_varchar2_value
866 (p_transaction_step_id => p_transaction_step_id
867 ,p_name => 'P_UNENROLLMENTDATE');
868
869 l_EXPIRATION_DATE := hr_transaction_api.get_varchar2_value
870 (p_transaction_step_id => p_transaction_step_id
871 ,p_name => 'P_EXPIRATIONDATE');
872 l_EARLIEST_ENROLL_DATE := hr_transaction_api.get_varchar2_value
873 (p_transaction_step_id => p_transaction_step_id
874 ,p_name => 'P_EARLIESTENROLLDATE');
875
876 hr_utility.trace ('Before create ' ||'10');
877
878 ota_cert_enrollment_api.subscribe_to_certification
879 (
880 -- p_effective_date => l_effective_date
881 -- p_validate => p_validate,
882 p_certification_id => l_certification_id
883 ,p_certification_status_code => l_certification_status_code
884 ,p_IS_HISTORY_FLAG => l_IS_HISTORY_FLAG
885 ,p_person_id => l_person_id
886 ,p_approval_flag => l_approval_req_flag
887 ,p_completion_date => to_date(l_completion_date,g_date_format)
888 ,p_business_group_id => l_business_group_id
889 ,p_UNENROLLMENT_DATE => to_date(l_UNENROLLMENT_DATE,g_date_format)
890 ,p_EXPIRATION_DATE => to_date(l_EXPIRATION_DATE,g_date_format)
891 ,p_EARLIEST_ENROLL_DATE => to_date(l_EARLIEST_ENROLL_DATE,g_date_format)
892 ,p_attribute_category => l_attribute_category
893 ,p_attribute1 => l_attribute1
894 ,p_attribute2 => l_attribute2
895 ,p_attribute3 => l_attribute3
896 ,p_attribute4 => l_attribute4
897 ,p_attribute5 => l_attribute5
898 ,p_attribute6 => l_attribute6
899 ,p_attribute7 => l_attribute7
900 ,p_attribute8 => l_attribute8
901 ,p_attribute9 => l_attribute9
902 ,p_attribute10 => l_attribute10
903 ,p_attribute11 => l_attribute11
904 ,p_attribute12 => l_attribute12
905 ,p_attribute13 => l_attribute13
906 ,p_attribute14 => l_attribute14
907 ,p_attribute15 => l_attribute15
908 ,p_attribute16 => l_attribute16
909 ,p_attribute17 => l_attribute17
910 ,p_attribute18 => l_attribute18
911 ,p_attribute19 => l_attribute19
912 ,p_attribute20 => l_attribute20
913 ,p_cert_enrollment_id => l_cert_enrollment_id
914 ,p_enroll_from => 'LRNR');
915
916 hr_utility.trace ('AFTER create ' ||'10');
917 --Put certification enrollment id in wf attributes
918 --req during update
919 hr_transaction_api.get_transaction_step_info
920 (p_transaction_step_id => p_transaction_step_id
921 ,p_item_type => l_item_type
922 ,p_item_key => l_item_key
923 ,p_activity_id => l_activity_id);
924
925 wf_engine.setItemAttrNumber(l_item_type, l_item_key, 'EVENT_ID',l_cert_enrollment_id );
926
927 --set certification name
928 --moved to java layer for approval mode off convenience
929 /*
930 open get_cert_name(l_certification_id);
931 fetch get_cert_name into l_certification_name;
932 close get_cert_name;
933
934 wf_engine.setItemAttrText(l_item_type, l_item_key, 'OTA_ACTIVITY_VERSION_NAME',l_certification_name);
935 */
936 hr_utility.trace ('AFTER EVENT_ID ' ||'10');
937 end create_cert_enrollment_tt;
938
939
940
941
942 procedure update_cert_enrollment_tt
943 (p_validate IN BOOLEAN,
944 p_transaction_step_id IN NUMBER,
945 p_trans_mode in varchar2 default null,
946 p_cert_enroll_id in number default null,
947 itemtype in varchar2 default null,
948 itemkey in varchar2 default null)
949 is
950
951 l_cert_enrollment_id number;
952 l_object_version_number number;
953 l_effective_date date := trunc(sysdate);
954 l_certification_status varchar2(100);
955
956 l_certification_id ota_cert_enrollments.certification_id%type;
957 l_item_type HR_API_TRANSACTION_STEPS.ITEM_TYPE%TYPE := itemtype;
958 l_item_key HR_API_TRANSACTION_STEPS.ITEM_KEY%TYPE := itemkey;
959 l_activity_id HR_API_TRANSACTION_STEPS.ACTIVITY_ID%TYPE;
960
964 l_completion_date varchar2(100);
961 l_IS_HISTORY_FLAG ota_cert_enrollments.is_history_flag%type;
962 l_person_id ota_cert_enrollments.person_id%type;
963 l_certification_status_code varchar2(100);
965 l_business_group_id ota_cert_enrollments.business_group_id%type;
966 l_UNENROLLMENT_DATE varchar2(100);
967 l_EXPIRATION_DATE varchar2(100);
968 l_EARLIEST_ENROLL_DATE varchar2(100);
969 l_attribute_category ota_cert_enrollments.attribute_category%type;
970 l_attribute1 ota_cert_enrollments.attribute1%type;
971 l_attribute2 ota_cert_enrollments.attribute1%type;
972 l_attribute3 ota_cert_enrollments.attribute1%type;
973 l_attribute4 ota_cert_enrollments.attribute1%type;
974 l_attribute5 ota_cert_enrollments.attribute1%type;
975 l_attribute6 ota_cert_enrollments.attribute1%type;
976 l_attribute7 ota_cert_enrollments.attribute1%type;
977 l_attribute8 ota_cert_enrollments.attribute1%type;
978 l_attribute9 ota_cert_enrollments.attribute1%type;
979 l_attribute10 ota_cert_enrollments.attribute1%type;
980 l_attribute11 ota_cert_enrollments.attribute1%type;
981 l_attribute12 ota_cert_enrollments.attribute1%type;
982 l_attribute13 ota_cert_enrollments.attribute1%type;
983 l_attribute14 ota_cert_enrollments.attribute1%type;
984 l_attribute15 ota_cert_enrollments.attribute1%type;
985 l_attribute16 ota_cert_enrollments.attribute1%type;
986 l_attribute17 ota_cert_enrollments.attribute1%type;
987 l_attribute18 ota_cert_enrollments.attribute1%type;
988 l_attribute19 ota_cert_enrollments.attribute1%type;
989 l_attribute20 ota_cert_enrollments.attribute1%type;
990
991
992
993
994 Cursor get_cert_info(crs_Cert_enrollment_id number)
995 is
996 select certification_id, object_version_number
997 from ota_cert_enrollments
998 where cert_enrollment_id = crs_cert_enrollment_id;
999
1000 begin
1001
1002 if l_item_key is null then
1003 hr_transaction_api.get_transaction_step_info
1004 (p_transaction_step_id => p_transaction_step_id
1005 ,p_item_type => l_item_type
1006 ,p_item_key => l_item_key
1007 ,p_activity_id => l_activity_id);
1008 end if;
1009
1010 if p_cert_enroll_id is null then
1011 l_Cert_enrollment_id := wf_engine.GetItemAttrNumber(itemtype => l_item_type
1012 ,itemkey => l_item_key
1013 ,aname => 'EVENT_ID');
1014
1015 else
1016 l_Cert_enrollment_id := p_cert_enroll_id;
1017 end if;
1018
1019 open get_cert_info(l_Cert_enrollment_id);
1020 fetch get_cert_info into l_certification_id,l_object_version_number;
1021 close get_cert_info;
1022
1023 if p_trans_mode is not null then
1024 -- implies approver rejected
1025 l_certification_status := 'REJECTED';
1026 else
1027 --if p_cert_enroll_id is null then
1028 -- enrollment is approved
1029 l_certification_status := 'ENROLLED';
1030 /* else
1031 -- reenrolling into a certification
1032 l_certification_status := 'AWAITING_APPROVAL';
1033 end if;*/
1034 end if;
1035
1036 if l_certification_status = 'REJECTED' then
1037 ota_cert_enrollment_api.update_cert_enrollment
1038 (
1039 p_effective_date => l_effective_date
1040 ,p_validate => p_validate
1041 ,p_certification_id => l_certification_id
1042 ,p_certification_status_code => l_certification_status
1043 ,p_cert_enrollment_id => l_cert_enrollment_id
1044 ,p_object_version_number => l_object_version_number
1045 );
1046 else
1047 -- call subscribe to create child objects as well
1048
1049 --really req or not ??
1050
1051 l_IS_HISTORY_FLAG := hr_transaction_api.get_varchar2_value
1052 (p_transaction_step_id => p_transaction_step_id
1053 ,p_name => 'P_HISTORYFLAG');
1054 l_person_id := hr_transaction_api.get_Number_value
1055 (p_transaction_step_id => p_transaction_step_id
1056 ,p_name => 'P_PERSONID');
1057 l_business_group_id := hr_transaction_api.get_varchar2_value
1058 (p_transaction_step_id => p_transaction_step_id
1059 ,p_name => 'P_BUSINESSGROUPID');
1060 l_attribute_category := hr_transaction_api.get_varchar2_value
1061 (p_transaction_step_id => p_transaction_step_id
1062 ,p_name => 'P_ATTRIBUTECATEGORY');
1063 l_attribute1 := hr_transaction_api.get_varchar2_value
1064 (p_transaction_step_id => p_transaction_step_id
1065 ,p_name => 'P_ATTRIBUTE1');
1066 l_attribute2 := hr_transaction_api.get_varchar2_value
1067 (p_transaction_step_id => p_transaction_step_id
1068 ,p_name => 'P_ATTRIBUTE2');
1069 l_attribute3 := hr_transaction_api.get_varchar2_value
1070 (p_transaction_step_id => p_transaction_step_id
1071 ,p_name => 'P_ATTRIBUTE3');
1072 l_attribute4 := hr_transaction_api.get_varchar2_value
1073 (p_transaction_step_id => p_transaction_step_id
1074 ,p_name => 'P_ATTRIBUTE4');
1075 l_attribute5 := hr_transaction_api.get_varchar2_value
1076 (p_transaction_step_id => p_transaction_step_id
1077 ,p_name => 'P_ATTRIBUTE5');
1078 l_attribute6 := hr_transaction_api.get_varchar2_value
1079 (p_transaction_step_id => p_transaction_step_id
1080 ,p_name => 'P_ATTRIBUTE6');
1081 l_attribute7 := hr_transaction_api.get_varchar2_value
1082 (p_transaction_step_id => p_transaction_step_id
1083 ,p_name => 'P_ATTRIBUTE7');
1084 l_attribute8 := hr_transaction_api.get_varchar2_value
1085 (p_transaction_step_id => p_transaction_step_id
1086 ,p_name => 'P_ATTRIBUTE8');
1087 l_attribute9 := hr_transaction_api.get_varchar2_value
1088 (p_transaction_step_id => p_transaction_step_id
1089 ,p_name => 'P_ATTRIBUTE9');
1090 l_attribute10 := hr_transaction_api.get_varchar2_value
1091 (p_transaction_step_id => p_transaction_step_id
1092 ,p_name => 'P_ATTRIBUTE10');
1093 l_attribute11 := hr_transaction_api.get_varchar2_value
1094 (p_transaction_step_id => p_transaction_step_id
1095 ,p_name => 'P_ATTRIBUTE11');
1096 l_attribute12 := hr_transaction_api.get_varchar2_value
1097 (p_transaction_step_id => p_transaction_step_id
1098 ,p_name => 'P_ATTRIBUTE12');
1099 l_attribute13 := hr_transaction_api.get_varchar2_value
1100 (p_transaction_step_id => p_transaction_step_id
1101 ,p_name => 'P_ATTRIBUTE13');
1102 l_attribute14 := hr_transaction_api.get_varchar2_value
1103 (p_transaction_step_id => p_transaction_step_id
1104 ,p_name => 'P_ATTRIBUTE14');
1105 l_attribute15 := hr_transaction_api.get_varchar2_value
1106 (p_transaction_step_id => p_transaction_step_id
1107 ,p_name => 'P_ATTRIBUTE15');
1108 l_attribute16 := hr_transaction_api.get_varchar2_value
1109 (p_transaction_step_id => p_transaction_step_id
1110 ,p_name => 'P_ATTRIBUTE16');
1111 l_attribute17 := hr_transaction_api.get_varchar2_value
1112 (p_transaction_step_id => p_transaction_step_id
1113 ,p_name => 'P_ATTRIBUTE17');
1114 l_attribute18 := hr_transaction_api.get_varchar2_value
1115 (p_transaction_step_id => p_transaction_step_id
1116 ,p_name => 'P_ATTRIBUTE18');
1117 l_attribute19 := hr_transaction_api.get_varchar2_value
1118 (p_transaction_step_id => p_transaction_step_id
1119 ,p_name => 'P_ATTRIBUTE19');
1120 l_attribute20 := hr_transaction_api.get_varchar2_value
1121 (p_transaction_step_id => p_transaction_step_id
1122 ,p_name => 'P_ATTRIBUTE20');
1123 l_completion_date := hr_transaction_api.get_varchar2_value
1124 (p_transaction_step_id => p_transaction_step_id
1125 ,p_name => 'P_COMPLETIONDATE');
1126 l_UNENROLLMENT_DATE := hr_transaction_api.get_varchar2_value
1127 (p_transaction_step_id => p_transaction_step_id
1128 ,p_name => 'P_UNENROLLMENTDATE');
1129
1130 l_EXPIRATION_DATE := hr_transaction_api.get_varchar2_value
1131 (p_transaction_step_id => p_transaction_step_id
1132 ,p_name => 'P_EXPIRATIONDATE');
1133 l_EARLIEST_ENROLL_DATE := hr_transaction_api.get_varchar2_value
1134 (p_transaction_step_id => p_transaction_step_id
1135 ,p_name => 'P_EARLIESTENROLLDATE');
1136
1137
1138
1139
1140 ota_cert_enrollment_api.subscribe_to_certification(
1141 p_validate => p_validate
1142 ,p_certification_id => l_certification_id
1143 ,p_person_id => l_person_id
1144 -- ,p_contact_id => p_contact_id
1145 ,p_business_group_id => l_business_group_id
1146 ,p_approval_flag => 'S'
1147 ,p_completion_date => to_date(l_completion_date,g_date_format)
1148 ,p_UNENROLLMENT_DATE => to_date(l_UNENROLLMENT_DATE,g_date_format)
1149 ,p_EXPIRATION_DATE => to_date(l_EXPIRATION_DATE,g_date_format)
1150 ,p_EARLIEST_ENROLL_DATE => to_date(l_EARLIEST_ENROLL_DATE,g_date_format)
1151 ,p_is_history_flag => l_is_history_flag
1152 ,p_attribute_category => l_attribute_category
1153 ,p_attribute1 => l_attribute1
1154 ,p_attribute2 => l_attribute2
1155 ,p_attribute3 => l_attribute3
1156 ,p_attribute4 => l_attribute4
1157 ,p_attribute5 => l_attribute5
1158 ,p_attribute6 => l_attribute6
1159 ,p_attribute7 => l_attribute7
1160 ,p_attribute8 => l_attribute8
1161 ,p_attribute9 => l_attribute9
1162 ,p_attribute10 => l_attribute10
1163 ,p_attribute11 => l_attribute11
1164 ,p_attribute12 => l_attribute12
1165 ,p_attribute13 => l_attribute13
1166 ,p_attribute14 => l_attribute14
1167 ,p_attribute15 => l_attribute15
1168 ,p_attribute16 => l_attribute16
1169 ,p_attribute17 => l_attribute17
1170 ,p_attribute18 => l_attribute18
1171 ,p_attribute19 => l_attribute19
1172 ,p_attribute20 => l_attribute20
1173 ,p_cert_enrollment_id => l_cert_enrollment_id
1174 ,p_certification_status_code => l_certification_status_code
1175 ,p_enroll_from => 'LRNR');
1176
1177
1178 end if;
1179
1180
1181 end update_cert_enrollment_tt;
1182
1183
1184
1185 PROCEDURE process_api
1186 (p_validate IN BOOLEAN,p_transaction_step_id IN NUMBER
1187 ,p_effective_date in varchar2) IS
1188
1189 l_from VARCHAR2(20);
1190 l_certification_id varchar2(20);
1191 l_person_id number(15);
1192 l_cert_enroll_id number(15) :=0;
1193
1194 /*cursor get_exist_cert_enroll
1195 is
1196 Select cert_enrollment_id
1197 from ota_cert_enrollments
1198 where certification_id = l_certification_id
1199 and person_id = l_person_id
1200 and business_group_id = ota_general.get_business_group_id();*/
1201
1202 begin
1203
1204 l_from := hr_transaction_api.get_varchar2_value
1205 (p_transaction_step_id => p_transaction_step_id
1206 ,p_name => 'P_FROM');
1207
1208
1209 /*l_certification_id := hr_transaction_api.get_varchar2_value
1210 (p_transaction_step_id => p_transaction_step_id
1211 ,p_name => 'P_CERTIFICATIONID');
1212
1213 l_person_id := hr_transaction_api.get_Number_value
1214 (p_transaction_step_id => p_transaction_step_id
1215 ,p_name => 'P_PERSONID'); */
1216
1217 If (l_from = 'REVIEW') Then
1218
1219 -- establish Savepoint
1220 SAVEPOINT validate_enrollment;
1221
1222
1223 create_cert_enrollment_tt(p_validate => p_validate, p_transaction_step_id => p_transaction_step_id);
1224
1225 if (p_validate = true) then
1226 rollback to validate_enrollment;
1227 else
1228 -- update p_from in transaction table
1229 update hr_api_transaction_values
1230 set varchar2_value = 'APPROVE'
1231 where transaction_step_id = p_transaction_step_id
1232 and name = 'P_FROM';
1233 end if;
1234
1235 ELSE -- on approval
1236
1237 update_cert_enrollment_tt(p_validate => p_validate, p_transaction_step_id => p_transaction_step_id);
1238 end if;
1239
1240 EXCEPTION
1241
1242 WHEN OTHERS THEN
1243 RAISE;
1244
1245 END process_api;
1246
1247
1248
1249 procedure create_enrollment
1250 (itemtype in varchar2,
1251 itemkey in varchar2,
1252 actid in number,
1253 funmode in varchar2,
1254 result out nocopy varchar2 ) is
1255
1256 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
1257 l_trans_obj_vers_nums hr_util_web.g_varchar2_tab_type;
1258 l_trans_step_rows NUMBER ;
1259 l_trans_step_id number;
1260
1261 begin
1262
1263 l_trans_step_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
1264 ,itemkey => itemkey
1265 ,aname => 'OTA_TRANSACTION_STEP_ID');
1266
1267 if ( funmode = 'RUN' ) then
1268
1269 process_api (false,l_trans_step_id);
1270 result := 'COMPLETE:SUCCESS';
1271
1272 elsif ( funmode = 'CANCEL' ) then
1273 --
1274 null;
1275 --
1276 --
1277 end if;
1278
1279 end create_enrollment;
1280
1281
1282 procedure cancel_enrollment
1283 (itemtype in varchar2,
1284 itemkey in varchar2,
1285 actid in number,
1286 funmode in varchar2,
1287 result out nocopy varchar2 ) is
1288
1289 l_trans_step_id number;
1290
1291
1292
1293
1294
1295
1296 begin
1297
1298 l_trans_step_id := wf_engine.GetItemAttrNumber(itemtype => itemtype
1299 ,itemkey => itemkey
1300 ,aname => 'OTA_TRANSACTION_STEP_ID');
1301
1302 if ( funmode = 'RUN' ) then
1303
1304 update_cert_enrollment_tt(p_validate => false,
1305 p_transaction_step_id => l_trans_step_id,
1306 p_trans_mode => 'CANCEL',
1307 itemtype => itemtype,
1308 itemkey => itemkey);
1309 result := 'COMPLETE:SUCCESS';
1310
1311 elsif ( funmode = 'CANCEL' ) then
1312 --
1313 null;
1314 --
1315 --
1316 end if;
1317 end cancel_enrollment;
1318
1319
1320 procedure validate_enrollment
1321 (p_item_type in varchar2,
1322 p_item_key in varchar2,
1323 p_message out nocopy varchar2) is
1324
1325 l_transaction_step_id number;
1326 begin
1327
1328 l_transaction_step_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type
1329 ,itemkey => p_item_key
1330 ,aname => 'OTA_TRANSACTION_STEP_ID');
1331 process_api(true,l_transaction_step_id);
1332 p_message := 'S' ;
1333 EXCEPTION
1334 When OTHERS Then
1335 p_message := fnd_message.get();
1336 If p_message is NULL then
1337 p_message := substr(SQLERRM,11,(length(SQLERRM)-10));
1338 End If;
1339
1340 end validate_enrollment;
1341
1342
1343 --
1344 -- ------------------------------------------------------------------
1345 -- PROCEDURE Approved
1346 -- ------------------------------------------------------------------
1347 --
1348 Procedure Approved ( itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
1349 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
1350 actid IN NUMBER,
1351 funcmode IN VARCHAR2,
1352 resultout OUT nocopy VARCHAR2 ) IS
1353
1354 BEGIN
1355
1356 IF (funcmode='RUN') THEN
1357 wf_engine.setItemAttrText (itemtype => itemtype
1358 ,itemkey => itemkey
1359 ,aname => 'APPROVAL_RESULT'
1360 ,avalue => 'ACCEPTED');
1361 resultout:='COMPLETE';
1362 RETURN;
1363 END IF;
1364
1365 IF (funcmode='CANCEL') THEN
1366 resultout:='COMPLETE';
1367 RETURN;
1368 END IF;
1369
1370 END Approved;
1371
1372
1373
1374 end OTA_CERT_APPROVAL_SS;
1375
1376