[Home] [Help]
PACKAGE BODY: APPS.GMO_OPER_CERT_PUB
Source
1 PACKAGE BODY gmo_oper_cert_pub AS
2 /* $Header: GMOOPCTB.pls 120.1 2007/06/21 06:11:48 rvsingh noship $ */
3 g_debug VARCHAR2 (5) := NVL(fnd_profile.VALUE ('AFLOG_LEVEL'),-1);
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GMO_OPER_CERT';
5
6
7
8 FUNCTION check_certification(
9 p_user_id IN NUMBER
10 ,p_org_id IN NUMBER
11 ,p_object_id IN NUMBER DEFAULT NULL
12 ,p_object_type IN NUMBER DEFAULT NULL
13 ,p_eff_date IN DATE
14 ,x_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER
15
16 IS
17
18 TYPE l_cert_comp_tbl_typ IS TABLE OF gmo_opert_cert_detail%ROWTYPE
19 INDEX BY BINARY_INTEGER;
20
21 l_api_name CONSTANT VARCHAR2 (30) := 'check_certification';
22 l_return_status VARCHAR2 (1);
23 l_employ_id NUMBER ;
24 l_user_id NUMBER ;
25 l_opert_cert_hdr gmo_opert_cert_header%ROWTYPE;
26 l_cert_comp_tbl l_cert_comp_tbl_typ;
27 l_org_id NUMBER ;
28 l_object_id NUMBER ;
29 l_object_type NUMBER ;
30 l_competence_lines NUMBER ;
31 l_business_group_id NUMBER ;
32 l_cert_count NUMBER ;
33 l_comp_count NUMBER ;
34 l_eff_date DATE;
35
36 CURSOR cur_get_employee (v_user_id IN NUMBER) IS
37 SELECT employee_id
38 FROM fnd_user
39 WHERE user_id = v_user_id ;
40 CURSOR cur_get_oper_cert (v_org_id IN NUMBER,
41 v_object_id IN NUMBER,
42 v_object_type IN NUMBER ) IS
43 SELECT *
44 FROM gmo_opert_cert_header
45 WHERE organization_id = v_org_id
46 AND object_id = v_object_id
47 AND object_type = v_object_type;
48
49 CURSOR cur_get_compet_dtl (v_header_id IN NUMBER
50 ) IS
51 SELECT *
52 FROM gmo_opert_cert_detail
53 WHERE header_id = v_header_id ;
54
55 CURSOR cur_get_org (v_org_id number) IS
56
57 SELECT HOU.BUSINESS_GROUP_ID
58 -- DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION1), TO_NUMBER(NULL)) SET_OF_BOOKS_ID,
59 -- DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION3), TO_NUMBER(NULL)) OPERATING_UNIT,
60 -- DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION2), null) LEGAL_ENTITY
61 FROM HR_ORGANIZATION_UNITS HOU, HR_ORGANIZATION_INFORMATION HOI2
62 WHERE HOU.organization_id = V_org_id
63 AND HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
64 AND ( HOI2.ORG_INFORMATION_CONTEXT || '') ='Accounting Information';
65
66
67 invalid_user EXCEPTION;
68 NO_CERTIFICATE_REQ EXCEPTION;
69 NO_COMPETENCE_DEFINED EXCEPTION;
70 NO_COMPETENCE EXCEPTION;
71 NO_CERTIFICATE EXCEPTION;
72 INVALID_BUSINESS_GROUP_ID EXCEPTION;
73 BEGIN
74
75
76 IF g_debug <= gme_debug.g_log_procedure THEN
77 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
78 || l_api_name);
79 END IF;
80 x_return_status := fnd_api.g_ret_sts_success;
81 l_user_id := p_user_id ;
82 l_org_id := p_org_id ;
83 l_object_id := p_object_id ;
84 l_object_type := p_object_type;
85 l_eff_date := nvl(p_eff_date, sysdate) ;
86 -- need to add check for gmo operator certificate profile
87 -- return if not set
88 -- dbms_output.put_line('l_object_type '||l_object_type);
89 -- dbms_output.put_line('l_object_id '||l_object_id);
90 /* IF l_user_id = 1012129 THEN
91 return 1 ;
92 ENd If; */
93 -- get the employee data for the user_id.
94 OPEN cur_get_employee (l_user_id);
95 FETCH cur_get_employee
96 INTO l_employ_id;
97 CLOSE cur_get_employee ;
98 --dbms_output.put_line('empl '||l_employ_id);
99 -- get the buiness_group_id for the org_id.
100 OPEN cur_get_org (l_org_id);
101 FETCH cur_get_org
102 INTO l_business_group_id;
103 IF (cur_get_org%NOTFOUND) THEN
104 CLOSE cur_get_org;
105 RAISE invalid_business_group_id;
106 END IF;
107 CLOSE cur_get_org;
108 --dbms_output.put_line('hr unit '||l_business_group_id);
109 -- check for whether certification is required for this object
110 OPEN cur_get_oper_cert (l_org_id, l_object_id, l_object_type);
111 FETCH cur_get_oper_cert INTO l_opert_cert_hdr ;
112 IF (cur_get_oper_cert%NOTFOUND) THEN
113 CLOSE cur_get_oper_cert;
114 --dbms_output.put_line('no req');
115 RETURN 1;
116
117 END IF;
118 CLOSE cur_get_oper_cert;
119 --dbms_output.put_line('cert req '||l_opert_cert_hdr.header_id);
120 -- check for override allowed or not
121
122
123
124
125 -- now based on the HRMS make a call to the api and chek for competency.
126 --dbms_output.put_line('for cert');
127 SELECT COUNT(*) into l_cert_count
128 FROM gmo_opert_cert_detail
129 WHERE header_id = l_opert_cert_hdr.header_id
130 AND qualification_type = 1 -- 1 is certification
131 AND FROM_DATE <= l_eff_date
132 AND nvl(TO_DATE, sysdate) >= l_eff_date
133 AND qualification_id NOT IN (SELECT certification_id
134 FROM OTA_CERT_ENROLLMENTS oce
135 WHERE oce.certification_status_code ='CERTIFIED'
136 AND oce.completion_date <= SYSDATE
137 AND oce.business_group_id = l_business_group_id
138 AND oce.person_id = l_employ_id );
139 IF l_cert_count > 0 THEN
140 --dbms_output.put_line('no cert found');
141 RAISE no_certificate;
142 END IF;
143
144 SELECT COUNT(*) into l_comp_count
145 FROM gmo_opert_cert_detail ocd
146 WHERE ocd.header_id = l_opert_cert_hdr.header_id
147 AND ocd.qualification_type = 2 -- 2 is competence
148 AND FROM_DATE <= l_eff_date
149 AND nvl(TO_DATE, sysdate) >= l_eff_date
150 AND ocd.qualification_id NOT IN (SELECT competence_id
151 FROM per_competence_elements pce
152 WHERE nvl(pce.effective_date_to, sysdate) >= SYSDATE
153 AND pce.effective_date_from <= SYSDATE
154 AND pce.PROFICIENCY_LEVEL_ID >= ocd.PROFICIENCY_LEVEL_ID
155 AND pce.business_group_id = l_business_group_id
156 AND pce.person_id = l_employ_id );
157
158
159 IF l_comp_count > 0 THEN
160
161 RAISE no_competence;
162 END IF;
163
164 RETURN 1;
165
166 EXCEPTION
167 WHEN invalid_user THEN
168
169 -- message that user is not defined in hrms
170 fnd_message.set_name ('INV', 'INV_INT_USRCODE');
171 fnd_msg_pub.ADD;
172 x_return_status := fnd_api.g_ret_sts_error ;
173 RETURN 0;
174
175 WHEN NO_CERTIFICATE_REQ THEN
176
177 -- this object don't require any certification
178 RETURN 1;
179 /*WHEN NO_COMPETENCE_DEFINED THEN
180 -- this object has no competence for certification
181 RETURN 0;
182 x_return_status := fnd_api.g_ret_sts_error ;*/
183 WHEN NO_COMPETENCE or no_certificate THEN
184 IF l_opert_cert_hdr.override = 1 THEN
185 RETURN 0;
186 ELSE
187 x_return_status := 'O' ;
188 RETURN -1;
189 END IF;
190
191 WHEN OTHERS THEN
192 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
193 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
194 x_return_status := fnd_api.g_ret_sts_unexp_error;
195 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
196 RETURN 0;
197 END check_certification;
198
199 PROCEDURE required_certification(
200 p_user_id IN NUMBER
201 ,p_org_id IN NUMBER
202 ,p_header_id IN NUMBER
203 ,p_operator_certificate_id IN NUMBER
204 ,p_eff_date IN DATE
205 ,x_return_status OUT NOCOPY VARCHAR2)
206
207 IS
208
209 TYPE l_cert_comp_tbl_typ IS TABLE OF gmo_opert_cert_detail%ROWTYPE
210 INDEX BY BINARY_INTEGER;
211
212 l_api_name CONSTANT VARCHAR2 (30) := 'required_certification';
213 l_return_status VARCHAR2 (1);
214 l_employ_id NUMBER ;
215 l_user_id NUMBER ;
216 l_opert_cert_hdr gmo_opert_cert_header%ROWTYPE;
217 l_cert_comp_tbl l_cert_comp_tbl_typ;
218 l_org_id NUMBER ;
219 l_object_id NUMBER ;
220 l_object_type NUMBER ;
221 l_competence_lines NUMBER ;
222 l_business_group_id NUMBER ;
223 l_header_id NUMBER ;
224 l_operator_certificate_id NUMBER ;
225 l_trans_detail_id NUMBER;
226 i NUMBER;
227 l_eff_date DATE;
228
229 CURSOR cur_get_employee (v_user_id IN NUMBER) IS
230 SELECT employee_id
231 FROM fnd_user
232 WHERE user_id = v_user_id ;
233
234 CURSOR cur_get_org (v_org_id number) IS
235
236 SELECT HOU.BUSINESS_GROUP_ID
237 -- DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION1), TO_NUMBER(NULL)) SET_OF_BOOKS_ID,
238 -- DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION3), TO_NUMBER(NULL)) OPERATING_UNIT,
239 -- DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION2), null) LEGAL_ENTITY
240 FROM HR_ORGANIZATION_UNITS HOU, HR_ORGANIZATION_INFORMATION HOI2
241 WHERE HOU.organization_id = V_org_id
242 AND HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
243 AND ( HOI2.ORG_INFORMATION_CONTEXT || '') ='Accounting Information';
244
245 CURSOR get_req_competence (v_header_id NUMBER,v_employ_id NUMBER, v_business_id NUMBER, v_date DATE) IS
246
247 SELECT ocd.Qualification_id , ocd.Qualification_type, ocd.PROFICIENCY_LEVEL_ID
248 FROM gmo_opert_cert_detail ocd
249 WHERE header_id = v_header_id
250 AND qualification_type = 1 -- 1 is certification
251 AND FROM_DATE <= v_date
252 AND nvl(TO_DATE,sysdate) >= v_date
253 AND qualification_id NOT IN (SELECT certification_id
254 FROM OTA_CERT_ENROLLMENTS oce
255 WHERE oce.certification_status_code ='CERTIFIED'
256 AND oce.completion_date <= SYSDATE
257 AND oce.business_group_id = l_business_group_id
258 AND oce.person_id = l_employ_id )
259
260
261 UNION
262 SELECT ocd.Qualification_id , ocd.Qualification_type, ocd.PROFICIENCY_LEVEL_ID
263 FROM gmo_opert_cert_detail ocd
264 WHERE ocd.header_id = v_header_id
265 AND ocd.qualification_type = 2 -- 2 is competence
266 AND FROM_DATE <= v_date
267 AND nvl(TO_DATE,sysdate) >= v_date
268 AND ocd.qualification_id NOT IN (SELECT competence_id
269 FROM per_competence_elements pce
270 WHERE nvl(pce.effective_date_to, sysdate) >= SYSDATE
271 AND pce.effective_date_from <= SYSDATE
272 AND pce.PROFICIENCY_LEVEL_ID >= ocd.PROFICIENCY_LEVEL_ID
273 AND pce.business_group_id = v_business_id
274 AND pce.person_id = l_employ_id );
275
276
277 invalid_user EXCEPTION;
278 NO_CERTIFICATE_REQ EXCEPTION;
279 NO_COMPETENCE_DEFINED EXCEPTION;
280 NO_COMPETENCE EXCEPTION;
281 NO_CERTIFICATE EXCEPTION;
282 INVALID_BUSINESS_GROUP_ID EXCEPTION;
283
284 BEGIN
285
286
287 IF g_debug <= gme_debug.g_log_procedure THEN
288 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
289 || l_api_name);
290 END IF;
291 x_return_status := fnd_api.g_ret_sts_success;
292
293 l_user_id := p_user_id ;
294 l_org_id := p_org_id ;
295 l_header_id := p_header_id ;
296 l_operator_certificate_id := p_operator_certificate_id ;
297 l_eff_date := nvl(p_eff_date,sysdate);
298 -- get the employee data for the user_id.
299 OPEN cur_get_employee (l_user_id);
300 FETCH cur_get_employee
301 INTO l_employ_id;
302
303 Close cur_get_employee ;
304 -- get the buiness_group_id for the org_id.
305 OPEN cur_get_org (l_org_id);
306 FETCH cur_get_org
307 INTO l_business_group_id;
308 IF (cur_get_org%NOTFOUND) THEN
309 CLOSE cur_get_org;
310 RAISE invalid_business_group_id;
311 END IF;
312 CLOSE cur_get_org;
313
314 FOR get_rec IN get_req_competence (l_header_id,l_employ_id,l_business_group_id, l_eff_date) LOOP
315 i := i+1 ;
316 gmo_cert_trans_detail_dbl.insert_row( l_trans_detail_id,l_operator_certificate_id,l_header_id,
317 get_rec.qualification_id,
318 get_rec.qualification_type,
319 get_rec.PROFICIENCY_LEVEL_ID, x_return_status);
320 --dbms_output.put_line('l_trans_detail_id '||l_trans_detail_id);
321 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
322 RAISE fnd_api.g_exc_error;
323 END IF;
324 END LOOP;
325
326 EXCEPTION
327 WHEN FND_API.g_exc_error THEN
328
329 x_return_status := fnd_api.g_ret_sts_error;
330
331 WHEN OTHERS THEN
332 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
333 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
334 x_return_status := fnd_api.g_ret_sts_unexp_error;
335
336 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
337
338
339 END required_certification;
340
341
342 Procedure Update_erecord(
343 p_ERECORD_ID IN NUMBER
344 ,p_Operator_certificate_id IN NUMBER
345 ,p_EVENT_KEY IN VARCHAR2
346 ,p_EVENT_NAME IN VARCHAR2
347 ,x_return_status OUT NOCOPY VARCHAR2) IS
348
349
350 l_Operator_certificate_id NUMBER ;
351 l_EVENT_KEY VARCHAR2 (30);
352 l_EVENT_NAME VARCHAR2 (240);
353
354
355 BEGIN
356 -- Initially let us assign the return status to success
357 x_return_status := fnd_api.g_ret_sts_success;
358
359 l_Operator_certificate_id := p_Operator_certificate_id ;
360 l_EVENT_KEY := p_EVENT_KEY ;
361 l_EVENT_NAME := p_EVENT_NAME ;
362
363 IF l_operator_certificate_id IS NOT NULL THEN
364
365 update GMO_OPERATOR_CERT_TRANS
366 set erecord_id = p_erecord_id
367 where operator_certificate_id = l_operator_certificate_id
368 AND erecord_id IS NULL ;
369
370 END IF;
371
372 EXCEPTION
373 WHEN FND_API.g_exc_error THEN
374
375 x_return_status := fnd_api.g_ret_sts_error;
376 WHEN OTHERS THEN
377 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
378 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
379 x_return_status := fnd_api.g_ret_sts_unexp_error;
380
381
382 END Update_erecord;
383
384
385 FUNCTION check_certification(
386 p_user_id IN NUMBER
387 ,p_org_id IN NUMBER
388 ,p_object_id IN NUMBER DEFAULT NULL
389 ,p_object_type IN NUMBER DEFAULT NULL
390 ,p_eff_date IN DATE) RETURN NUMBER
391 IS
392 l_cert_status number;
393 l_return_status VARCHAR2(250);
394 BEGIN
395 l_cert_status := check_certification(p_user_id,p_org_id,p_object_id,p_object_type,p_eff_date,l_return_status);
396 RETURN l_cert_status;
397 END check_certification;
398
399 procedure update_cert_record(p_Operator_certificate_id IN NUMBER
400 ,p_EVENT_KEY IN VARCHAR2
401 ,p_EVENT_NAME IN VARCHAR2
402 ,p_ERECORD_ID IN NUMBER
403 ,p_user_key_label_token IN VARCHAR2
404 ,p_user_key_value IN VARCHAR2
405 ,p_transaction_id IN VARCHAR2
406 ,x_return_status OUT NOCOPY VARCHAR2) IS
407 CURSOR CUR_GET_CERT_RECORD IS
408 SELECT EVENT_NAME,EVENT_KEY,USER_KEY_LABEL_TOKEN,USER_KEY_VALUE,ERECORD_ID,
409 Operator_certificate_id,TRANSACTION_ID
410 FROM GMO_OPERATOR_CERT_TRANS
411 WHERE operator_certificate_id = p_Operator_certificate_id;
412 BEGIN
413 x_return_status := fnd_api.g_ret_sts_success;
414 FOR get_rec IN CUR_GET_CERT_RECORD LOOP
415 update GMO_OPERATOR_CERT_TRANS
416 set EVENT_NAME = nvl(p_EVENT_NAME,get_rec.EVENT_NAME)
417 , EVENT_KEY = nvl(p_EVENT_KEY,get_rec.EVENT_KEY)
418 , USER_KEY_LABEL_TOKEN = nvl(p_user_key_label_token,get_rec.USER_KEY_LABEL_TOKEN)
419 , USER_KEY_VALUE = nvl(p_user_key_value,get_rec.USER_KEY_VALUE)
420 , ERECORD_ID = nvl(p_ERECORD_ID,get_rec.ERECORD_ID)
421 , TRANSACTION_ID = nvl(p_transaction_id,get_rec.TRANSACTION_ID)
422 , STATUS = 'S'
423 where operator_certificate_id = get_rec.operator_certificate_id ;
424
425 END LOOP;
426 EXCEPTION
427 WHEN FND_API.g_exc_error THEN
428 x_return_status := fnd_api.g_ret_sts_error;
429 WHEN OTHERS THEN
430 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
431 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
432 x_return_status := fnd_api.g_ret_sts_unexp_error;
433 END update_cert_record;
434
435
436 PROCEDURE cert_details (
437 p_operator_CERTIFICATE_ID IN OUT NOCOPY NUMBER
438 ,p_HEADER_ID IN NUMBER
439 ,p_TRANSACTION_ID IN VARCHAR2
440 ,p_USER_ID IN NUMBER
441 ,p_comments IN VARCHAR2
442 ,p_OVERRIDER_ID IN NUMBER
443 ,p_User_key_label_product IN VARCHAR2
444 ,p_User_key_label_token IN VARCHAR2
445 ,p_User_key_value IN VARCHAR2
446 ,p_Erecord_id IN NUMBER
447 ,p_Trans_object_id IN NUMBER
448 ,p_STATUS IN VARCHAR2
449 ,p_event_name IN VARCHAR2
450 ,p_event_key IN VARCHAR2
451 ,p_eff_date IN DATE
452 ,p_CREATION_DATE IN DATE
453 ,p_CREATED_BY IN NUMBER
454 ,p_LAST_UPDATE_DATE IN DATE
455 ,p_LAST_UPDATED_BY IN NUMBER
456 ,p_LAST_UPDATE_LOGIN IN NUMBER
457 ,x_return_Status OUT NOCOPY VARCHAR2 ) IS
458
459
460 l_return_status VARCHAR2(1) ;
461 l_org_id NUMBER;
462 l_header_id NUMBER;
463 l_erecord_id NUMBER;
464 l_operator_certificate_id NUMBER;
465 l_TRANSACTION_ID VARCHAR2(240);
466 l_USER_ID NUMBER ;
467 l_comments VARCHAR2(240);
468 l_OVERRIDER_ID NUMBER ;
469 l_User_key_label_product VARCHAR2(30);
470 l_User_key_label_token VARCHAR2(240);
471 l_User_key_value VARCHAR2(240);
472 l_Trans_object_id NUMBER;
473 l_STATUS VARCHAR2(1);
474 l_event_name VARCHAR2(240);
475 l_event_key VARCHAR2(30);
476 l_eff_date DATE;
477 l_CREATION_DATE DATE;
478 l_CREATED_BY NUMBER;
479 l_LAST_UPDATE_DATE DATE;
480 l_LAST_UPDATED_BY NUMBER;
481 l_LAST_UPDATE_LOGIN NUMBER;
482
483 BEGIN
484 -- Initially let us assign the return status to success
485 x_return_status := fnd_api.g_ret_sts_success;
486
487 l_HEADER_ID := p_HEADER_ID ;
488 l_TRANSACTION_ID := p_TRANSACTION_ID ;
489 l_USER_ID := p_USER_ID ;
490 l_comments := p_comments ;
491 l_OVERRIDER_ID := p_OVERRIDER_ID ;
492 l_User_key_label_product := p_User_key_label_product ;
493 l_User_key_label_token := p_User_key_label_token ;
494 l_User_key_value := p_User_key_value ;
495 l_Trans_object_id := p_Trans_object_id ;
496 l_STATUS := p_STATUS ;
497 l_event_name := p_event_name ;
498 l_event_key := p_event_key ;
499 l_eff_date := nvl(p_eff_date,sysdate) ;
500 l_CREATION_DATE := p_CREATION_DATE ;
501 l_CREATED_BY := p_CREATED_BY ;
502 l_LAST_UPDATE_DATE := p_LAST_UPDATE_DATE ;
503 l_LAST_UPDATED_BY := p_LAST_UPDATED_BY ;
504 l_LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN ;
505
506
507
508
509
510 gmo_oper_cert_trans_dbl.INSERT_ROW( p_operator_certificate_id => l_operator_certificate_id
511 ,p_header_id => l_header_id
512 ,p_transaction_id => l_transaction_id
513 ,p_user_id => l_user_id
514 ,p_comments => l_comments
515 ,p_overrider_id => l_overrider_id
516 ,p_user_key_label_product => l_user_key_label_product
517 ,p_user_key_label_token => l_user_key_label_token
518 ,p_user_key_value =>l_user_key_value
519 ,p_erecord_id => NULL
520 ,p_trans_object_id => l_trans_object_id
521 ,p_status => l_status
522 ,p_event_name => l_event_name
523 ,p_event_key => l_event_key
524 ,p_creation_date => l_creation_date
525 ,p_created_by => l_created_by
526 ,p_last_update_date => l_last_update_date
527 ,p_last_updated_by => l_last_updated_by
528 ,P_LAST_UPDATE_LOGIN => l_LAST_UPDATE_LOGIN
529 ,x_return_Status => l_return_status);
530 -- check for return status
531 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
532 RAISE fnd_api.g_exc_error;
533 END IF;
534 -- dbms_output.put_line('l_op_cert_id '||l_operator_certificate_id);
535 SELECT organization_id into l_org_id
536 FROM gmo_opert_cert_header
537 Where header_id = l_header_id ;
538
539 IF l_operator_certificate_id IS NOT NULL THEN
540 GMO_OPER_CERT_PUB.required_Certification(p_user_id =>l_user_id
541 ,p_org_id => l_org_id
542 ,p_header_id => l_header_id
543 ,p_operator_certificate_id =>l_operator_certificate_id
544 ,p_eff_date => l_eff_date
545 ,x_return_status => l_return_status);
546 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
547 RAISE fnd_api.g_exc_error;
548 END IF;
549 gmo_opert_cert_gtmp_dbl.INSERT_ROW (
550 p_ERECORD_ID => l_erecord_id
551 ,p_operator_certificate_id => l_operator_certificate_id
552 ,p_EVENT_KEY => l_event_key
553 ,p_EVENT_NAME => l_event_name
554 ,x_return_status => l_return_status ) ;
555
556 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
557 RAISE fnd_api.g_exc_error;
558 END IF;
559 END IF;
560
561
562 p_operator_certificate_id := l_operator_certificate_id;
563 EXCEPTION
564 WHEN FND_API.g_exc_error THEN
565 x_return_status := fnd_api.g_ret_sts_error;
566 WHEN OTHERS THEN
567 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
568 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
569 x_return_status := fnd_api.g_ret_sts_unexp_error;
570
571 END cert_details;
572
573 END gmo_oper_cert_pub;