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