[Home] [Help]
PACKAGE BODY: APPS.OKC_UDA_TEMPLATES_UTIL
Source
1 PACKAGE BODY okc_uda_templates_util AS
2 /* $Header: OKCVUDATUTLB.pls 120.0.12020000.5 2013/05/24 00:59:42 kkolukul noship $ */
3
4 l_pkg_name CONSTANT varchar2(50) := 'OKC_UDA_TEMPLATES_UTIL';
5
6 PROCEDURE debug_msg(p_msg IN VARCHAR2) IS
7 BEGIN
8
9 fnd_file.put_line (fnd_file.log, 'OKC_UDA_TEMPLATES_UTIL : ' || p_msg);
10
11 END debug_msg;
12
13
14 PROCEDURE UPLOAD_ACTIONS
15 (
16 p_attr_grp_type VARCHAR2,
17 p_action_name VARCHAR2,
18 p_classification_code VARCHAR2,
19 p_attr_grp_internal_name VARCHAR2,
20 p_object_name VARCHAR2,
21 p_sequence NUMBER,
22 p_function_name VARCHAR2,
23 p_description VARCHAR2,
24 p_owner VARCHAR2
25 )
26 IS
27 l_object_id NUMBER;
28 l_function_id NUMBER;
29 l_attr_grp_id NUMBER;
30 l_action_id NUMBER;
31 l_entity_code VARCHAR2(100);
32
33 BEGIN
34
35 BEGIN
36
37 SELECT OBJECT_ID
38 INTO l_object_id
39 FROM FND_OBJECTS
40 WHERE OBJ_NAME = p_object_name;
41
42 BEGIN
43 SELECT FUNCTION_ID
44 INTO l_function_id
45 FROM EGO_FUNCTIONS_B
46 WHERE INTERNAL_NAME = p_function_name;
47 EXCEPTION
48 WHEN No_Data_Found THEN
49 l_function_id := NULL;
50
51 debug_msg ('UPLOAD_ACTIONS : Function '|| p_function_name || ' was not found. Skipping the Action.' );
52 END;
53
54 IF l_function_id IS NOT NULL THEN
55
56 BEGIN
57 SELECT ATTR_GROUP_ID
58 INTO l_attr_grp_id
59 FROM EGO_FND_DSC_FLX_CTX_EXT
60 WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_internal_name
61 AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type;
62 EXCEPTION
63 WHEN No_Data_Found THEN
64 l_attr_grp_id := NULL;
65 -- show a message about which attribute group was not found and do nothing
66 debug_msg ('UPLOAD_ACTIONS : attribute group '|| p_attr_grp_internal_name || ' was not found. Skipping the Action.' );
67 END;
68
69 IF l_attr_grp_id IS NOT NULL THEN
70
71 BEGIN
72 SELECT ACTION_ID
73 INTO l_action_id
74 FROM EGO_ACTIONS_B
75 WHERE ACTION_NAME = p_action_name
76 AND ATTR_GROUP_ID = l_attr_grp_id
77 AND CLASSIFICATION_CODE = p_classification_code || '';
78
79 UPDATE EGO_ACTIONS_B
80 SET SEQUENCE = p_sequence
81 WHERE ACTION_ID = l_action_id;
82
83 BEGIN
84
85 SELECT ACTION_ID
86 INTO l_action_id
87 FROM EGO_ACTIONS_TL
88 WHERE ACTION_ID = l_action_id
89 AND LANGUAGE = USERENV('LANG');
90
91 UPDATE EGO_ACTIONS_TL
92 SET DESCRIPTION = p_description
93 WHERE ACTION_ID = l_action_id
94 AND LANGUAGE = USERENV('LANG');
95 EXCEPTION
96 WHEN NO_DATA_FOUND THEN
97
98 INSERT INTO EGO_ACTIONS_TL
99 (
100 ACTION_ID,
101 DESCRIPTION,
102 LANGUAGE,
103 SOURCE_LANG,
104 CREATED_BY,
105 CREATION_DATE,
106 LAST_UPDATED_BY,
107 LAST_UPDATE_DATE,
108 LAST_UPDATE_LOGIN
109 )
110 SELECT
111 l_action_id,
112 p_description,
113 L.LANGUAGE_CODE,
114 USERENV('LANG'),
115 DECODE(p_owner, 'SEED', 1, 0),
116 SYSDATE,
117 DECODE(p_owner, 'SEED', 1, 0),
118 SYSDATE,
119 0
120 FROM FND_LANGUAGES L
121 WHERE L.INSTALLED_FLAG in ('I', 'B')
122 AND NOT EXISTS
123 ( SELECT NULL
124 FROM EGO_ACTIONS_TL T
125 WHERE T.ACTION_ID =l_action_id
126 AND T.LANGUAGE = L.LANGUAGE_CODE
127 );
128 END;
129
130
131 EXCEPTION
132 WHEN NO_DATA_FOUND THEN
133
134 SELECT EGO_ACTIONS_S.nextval
135 INTO l_action_id
136 FROM DUAL
137 WHERE ROWNUM = 1;
138
139 INSERT INTO EGO_ACTIONS_B
140 (
141 ACTION_ID,
142 OBJECT_ID,
143 CLASSIFICATION_CODE,
144 ATTR_GROUP_ID,
145 SEQUENCE,
146 ACTION_NAME,
147 FUNCTION_ID,
148 CREATED_BY,
149 CREATION_DATE,
150 LAST_UPDATED_BY,
151 LAST_UPDATE_DATE,
152 LAST_UPDATE_LOGIN
153 )
154 VALUES
155 (
156 l_action_id,
157 l_object_id,
158 p_classification_code || '',
159 l_attr_grp_id,
160 p_sequence,
161 p_action_name,
162 l_function_id,
163 DECODE(p_owner, 'SEED', 1, 0),
164 SYSDATE,
165 DECODE(p_owner, 'SEED', 1, 0),
166 SYSDATE,
167 0
168 );
169
170 INSERT INTO EGO_ACTIONS_TL
171 (
172 ACTION_ID,
173 DESCRIPTION,
174 LANGUAGE,
175 SOURCE_LANG,
176 CREATED_BY,
177 CREATION_DATE,
178 LAST_UPDATED_BY,
179 LAST_UPDATE_DATE,
180 LAST_UPDATE_LOGIN
181 )
182 SELECT
183 l_action_id,
184 p_description,
185 L.LANGUAGE_CODE,
186 USERENV('LANG'),
187 DECODE(p_owner, 'SEED', 1, 0),
188 SYSDATE,
189 DECODE(p_owner, 'SEED', 1, 0),
190 SYSDATE,
191 0
192 FROM FND_LANGUAGES L
193 WHERE L.INSTALLED_FLAG in ('I', 'B')
194 AND NOT EXISTS
195 ( SELECT NULL
196 FROM EGO_ACTIONS_TL T
197 WHERE T.ACTION_ID =l_action_id
198 AND T.LANGUAGE = L.LANGUAGE_CODE
199 );
200
201 WHEN TOO_MANY_ROWS THEN
202 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
203 END;
204 END IF; -- AG found
205 END IF; -- function found
206 END;
207
208 EXCEPTION
209 WHEN OTHERS THEN
210 debug_msg('UPLOAD_ACTIONS: in exception ' ||SQLERRM);
211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
212
213 END UPLOAD_ACTIONS;
214
215 PROCEDURE UPLOAD_ACTIONS_NLS
216 (
217 p_attr_grp_type VARCHAR2,
218 p_action_name VARCHAR2,
219 p_classification_code VARCHAR2,
220 p_attr_grp_internal_name VARCHAR2,
221 p_object_name VARCHAR2,
222 p_sequence NUMBER,
223 p_function_name VARCHAR2,
224 p_description VARCHAR2,
225 p_owner VARCHAR2
226 )
227 IS
228 l_object_id NUMBER;
229 l_function_id NUMBER;
230 l_attr_grp_id NUMBER;
231 l_action_id NUMBER;
232 l_entity_code VARCHAR2(100);
233
234 BEGIN
235 BEGIN
236
237 debug_msg ('UPLOAD_ACTIONS_NLS : p_classification_code ' || p_classification_code || ' p_object_name ' ||p_object_name || ' p_function_name ' || p_function_name);
238
239 SELECT OBJECT_ID
240 INTO l_object_id
241 FROM FND_OBJECTS
242 WHERE OBJ_NAME = p_object_name;
243
244 BEGIN
245 SELECT FUNCTION_ID
246 INTO l_function_id
247 FROM EGO_FUNCTIONS_B
248 WHERE INTERNAL_NAME = p_function_name;
249 EXCEPTION
250 WHEN No_Data_Found THEN
251 l_function_id := NULL;
252 -- show a message about which attribute group was not found and do nothing
253 debug_msg ('UPLOAD_ACTIONS_NLS : Function '|| p_function_name || ' was not found. Skipping the Action.' );
254 END;
255
256 IF l_function_id IS NOT NULL THEN
257
258 BEGIN
259 SELECT ATTR_GROUP_ID
260 INTO l_attr_grp_id
261 FROM EGO_FND_DSC_FLX_CTX_EXT
262 WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_internal_name
263 AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type;
264 EXCEPTION
265 WHEN No_Data_Found THEN
266 l_attr_grp_id := NULL;
267 -- show a message about which attribute group was not found and do nothing
268 debug_msg ('UPLOAD_ACTIONS_NLS : attribute group '|| p_attr_grp_internal_name || ' was not found. Skipping the Action.' );
269 END;
270
271 IF l_attr_grp_id IS NOT NULL THEN
272
273 SELECT ACTION_ID
274 INTO l_action_id
275 FROM EGO_ACTIONS_B
276 WHERE ACTION_NAME = p_action_name
277 AND ATTR_GROUP_ID = l_attr_grp_id
278 AND CLASSIFICATION_CODE = p_classification_code || '';
279
280
281 UPDATE EGO_ACTIONS_TL
282 SET DESCRIPTION = p_description,
283 SOURCE_LANG = USERENV('LANG'),
284 LAST_UPDATE_DATE = SYSDATE,
285 LAST_UPDATED_BY = DECODE(p_owner, 'SEED', 1, 0),
286 LAST_UPDATE_LOGIN = 0
287 WHERE ACTION_ID = l_action_id
288 AND LANGUAGE = USERENV('LANG');
289
290 END IF; -- AG found
291 END IF; -- function found
292 END;
293
294 EXCEPTION
295 WHEN OTHERS THEN
296 debug_msg('UPLOAD_ACTIONS_NLS: in exception ' ||SQLERRM);
297 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
298
299
300 END UPLOAD_ACTIONS_NLS ;
301
302 PROCEDURE UPLOAD_ACTION_DL
303 (
304 p_attr_grp_type VARCHAR2,
305 p_action_name VARCHAR2,
306 p_classification_code VARCHAR2,
307 p_attr_grp_internal_name VARCHAR2,
308 p_data_level VARCHAR2,
309 p_visibility_flag VARCHAR2,
310 p_owner VARCHAR2
311 )
312 IS
313
314 l_data_level_id NUMBER;
315 l_attr_grp_id NUMBER;
316 l_action_id NUMBER;
317 l_visibility_flag VARCHAR2(1);
318 l_entity_code VARCHAR2(100);
319
320 BEGIN
321 BEGIN
322
323 SELECT DATA_LEVEL_ID
324 INTO l_data_level_id
325 FROM EGO_DATA_LEVEL_B
326 WHERE DATA_LEVEL_NAME = p_data_level;
327
328 BEGIN
329
330 SELECT ATTR_GROUP_ID
331 INTO l_attr_grp_id
332 FROM EGO_FND_DSC_FLX_CTX_EXT
333 WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_internal_name
334 AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type;
335
336 EXCEPTION
337 WHEN No_Data_Found THEN
338 l_attr_grp_id := NULL;
339 -- show a message about which attribute group was not found and do nothing
340 debug_msg ('UPLOAD_ACTION_DL : attribute group '|| p_attr_grp_internal_name || ' was not found. Skipping the ActionDL.' );
341 END;
342
343 IF l_attr_grp_id IS NOT NULL THEN
344
345 BEGIN
346 SELECT ACTION_ID
347 INTO l_action_id
348 FROM EGO_ACTIONS_B
349 WHERE ACTION_NAME = p_action_name
350 AND ATTR_GROUP_ID = l_attr_grp_id
351 AND CLASSIFICATION_CODE = p_classification_code || ''
352 AND ROWNUM = 1;
353 EXCEPTION
354 WHEN No_Data_Found THEN
355 l_action_id := NULL;
356 -- show a message about which attribute group was not found and do nothing
357 debug_msg ('UPLOAD_ACTION_DL : Action '|| p_action_name || ' was not found. Skipping the ActionDL.' );
358 END;
359
360 IF l_action_id IS NOT NULL THEN
361
362
363 BEGIN
364
365 SELECT VISIBILITY_FLAG
366 INTO l_visibility_flag
367 FROM EGO_ACTIONS_DL
368 WHERE ACTION_ID = l_action_id
369 AND DATA_LEVEL_ID = l_data_level_id;
370
371 UPDATE EGO_ACTIONS_DL
372 SET VISIBILITY_FLAG = p_visibility_flag
373 WHERE ACTION_ID = l_action_id
374 AND DATA_LEVEL_ID = l_data_level_id;
375
376 EXCEPTION
377 WHEN NO_DATA_FOUND THEN
378
379 INSERT INTO EGO_ACTIONS_DL
380 (
381 ACTION_ID,
382 DATA_LEVEL_ID,
383 VISIBILITY_FLAG,
384 CREATED_BY,
385 CREATION_DATE,
386 LAST_UPDATED_BY,
387 LAST_UPDATE_DATE,
388 LAST_UPDATE_LOGIN
389 )
390 VALUES
391 (
392 l_action_id,
393 l_data_level_id,
394 p_visibility_flag,
395 DECODE(p_owner, 'SEED', 1, 0),
396 SYSDATE,
397 DECODE(p_owner, 'SEED', 1, 0),
398 SYSDATE,
399 0
400 );
401
402 WHEN TOO_MANY_ROWS THEN
403 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
404 END;
405 END IF; -- Action found
406 END IF; -- AG found
407 END;
408 EXCEPTION
409 WHEN OTHERS THEN
410 debug_msg('UPLOAD_ACTION_DL: in exception ' ||SQLERRM);
411 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
412
413 END UPLOAD_ACTION_DL;
414
415 PROCEDURE UPLOAD_ACTION_DISPLAYS
416 (
417 p_attr_grp_type VARCHAR2,
418 p_action_name VARCHAR2,
419 p_function_name VARCHAR2,
420 p_object_name VARCHAR2,
421 p_attr_grp_internal_name VARCHAR2,
422 p_classification_code VARCHAR2,
423 p_exec_method VARCHAR2,
424 p_exec_trigger VARCHAR2,
425 p_display_style VARCHAR2,
426 p_app_short_name VARCHAR2,
427 p_prompt_message_name VARCHAR2,
428 p_visibility_flag VARCHAR2,
429 p_owner VARCHAR2
430 )
431 IS
432
433 l_app_id NUMBER;
434 l_data_level VARCHAR2(30);
435 l_attr_grp_id NUMBER;
436 l_action_id NUMBER;
437 l_update_login NUMBER;
438 l_entity_code VARCHAR2(100);
439
440 BEGIN
441 BEGIN
442
443 SELECT APPLICATION_ID
444 INTO l_app_id
445 FROM FND_APPLICATION
446 WHERE APPLICATION_SHORT_NAME = p_app_short_name;
447
448 BEGIN
449 SELECT ATTR_GROUP_ID
450 INTO l_attr_grp_id
451 FROM EGO_FND_DSC_FLX_CTX_EXT
452 WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_internal_name
453 AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type;
454 EXCEPTION
455 WHEN No_Data_Found THEN
456 l_attr_grp_id := NULL;
457 -- show a message about which attribute group was not found and do nothing
458 debug_msg ('UPLOAD_ACTION_DISPLAYS : attribute group '|| p_attr_grp_internal_name || ' was not found. Skipping the ActionDisplays.' );
459 END;
460
461 IF l_attr_grp_id IS NOT NULL THEN
462
463 BEGIN
464
465 SELECT ACTION_ID
466 INTO l_action_id
467 FROM EGO_ACTIONS_B
468 WHERE ACTION_NAME = p_action_name
469 AND ATTR_GROUP_ID = l_attr_grp_id
470 AND CLASSIFICATION_CODE = p_classification_code || ''
471 AND ROWNUM = 1;
472
473 EXCEPTION
474 WHEN No_Data_Found THEN
475 l_action_id := NULL;
476 -- show a message about which attribute group was not found and do nothing
477 debug_msg ('UPLOAD_ACTION_DISPLAYS : Action '|| p_action_name || ' was not found. Skipping the ActionDisplays.' );
478 END;
479
480 IF l_action_id IS NOT NULL THEN
481
482 BEGIN
483
484 SELECT LAST_UPDATE_LOGIN
485 INTO l_update_login
486 FROM EGO_ACTION_DISPLAYS_B
487 WHERE ACTION_ID = l_action_id;
488
489 UPDATE EGO_ACTION_DISPLAYS_B
490 SET EXECUTION_METHOD = p_exec_method,
491 EXECUTION_TRIGGER = p_exec_trigger,
492 DISPLAY_STYLE = p_display_style,
493 PROMPT_APPLICATION_ID = l_app_id,
494 PROMPT_MESSAGE_NAME = p_prompt_message_name,
495 VISIBILITY_FLAG = p_visibility_flag
496 WHERE ACTION_ID = l_action_id;
497
498 EXCEPTION
499 WHEN NO_DATA_FOUND THEN
500
501 INSERT INTO EGO_ACTION_DISPLAYS_B
502 (
503 ACTION_ID,
504 EXECUTION_METHOD,
505 EXECUTION_TRIGGER,
506 DISPLAY_STYLE,
507 PROMPT_APPLICATION_ID,
508 PROMPT_MESSAGE_NAME,
509 VISIBILITY_FLAG,
510 CREATED_BY,
511 CREATION_DATE,
512 LAST_UPDATED_BY,
513 LAST_UPDATE_DATE,
514 LAST_UPDATE_LOGIN
515 )
516 VALUES
517 (
518 l_action_id,
519 p_exec_method,
520 p_exec_trigger,
521 p_display_style,
522 l_app_id,
523 p_prompt_message_name,
524 p_visibility_flag,
525 DECODE(p_owner, 'SEED', 1, 0),
526 SYSDATE,
527 DECODE(p_owner, 'SEED', 1, 0),
528 SYSDATE,
529 0
530 );
531
532 WHEN TOO_MANY_ROWS THEN
533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
534 END;
535 END IF; -- Action Found
536 END IF; -- AG found
537 END;
538 EXCEPTION
539 WHEN OTHERS THEN
540 debug_msg('UPLOAD_ACTION_DISPLAYS: in exception ' ||SQLERRM);
541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
542 END UPLOAD_ACTION_DISPLAYS;
543
544 PROCEDURE UPLOAD_FUNCTION_MAPPINGS
545 (
546 p_attr_grp_type VARCHAR2,
547 p_action_name VARCHAR2,
548 p_function_name VARCHAR2,
549 p_object_name VARCHAR2,
550 p_attr_grp_internal_name VARCHAR2,
551 p_classification_code VARCHAR2,
552 p_map_par_int_name VARCHAR2,
553 p_map_app_short_name VARCHAR2,
554 p_map_obj_type VARCHAR2,
555 p_map_to_grp_type VARCHAR2,
556 p_map_ext_attrs VARCHAR2,
557 p_map_context VARCHAR2,
558 p_map_attr VARCHAR2,
559 p_owner VARCHAR2
560 )
561 IS
562
563 l_function_id NUMBER;
564 l_data_level NUMBER;
565 l_attr_grp_id NUMBER;
566 l_action_id NUMBER;
567 l_param_id NUMBER;
568 l_app_id NUMBER;
569 l_pk2 VARCHAR2(50);
570 l_pk3 VARCHAR2(50);
571 l_map_attr VARCHAR2(100);
572 l_entity_code VARCHAR2(100);
573
574 BEGIN
575
576 BEGIN
577
578 BEGIN
579
580 SELECT FUNCTION_ID
581 INTO l_function_id
582 FROM EGO_FUNCTIONS_B
583 WHERE INTERNAL_NAME = p_function_name;
584
585 EXCEPTION
586 WHEN No_Data_Found THEN
587 l_function_id := NULL;
588 -- show a message about which attribute group was not found and do nothing
589 debug_msg ('LOAD_FUNCTION_MAPPINGS : Function '|| p_function_name || ' was not found. Skipping the Mappings.' );
590 END;
591
592 IF l_function_id IS NOT NULL THEN
593
594 BEGIN
595 SELECT ATTR_GROUP_ID
596 INTO l_attr_grp_id
597 FROM EGO_FND_DSC_FLX_CTX_EXT
598 WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_internal_name
599 AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type;
600 EXCEPTION
601 WHEN No_Data_Found THEN
602 l_attr_grp_id := NULL;
603 -- show a message about which attribute group was not found and do nothing
604 debug_msg ('UPLOAD_FUNCTION_MAPPINGS : attribute group '|| p_attr_grp_internal_name || ' was not found. Skipping the Mappings.' );
605 END;
606
607 IF l_attr_grp_id IS NOT NULL THEN
608
609 SELECT ACTION_ID
610 INTO l_action_id
611 FROM EGO_ACTIONS_B
612 WHERE ACTION_NAME = p_action_name
613 AND ATTR_GROUP_ID = l_attr_grp_id
614 AND CLASSIFICATION_CODE = p_classification_code || ''
615 AND ROWNUM = 1;
616
617 BEGIN
618 SELECT FUNC_PARAM_ID
619 INTO l_param_id
620 FROM EGO_FUNC_PARAMS_B
621 WHERE INTERNAL_NAME = p_map_par_int_name
622 AND FUNCTION_ID = l_function_id;
623 EXCEPTION
624 WHEN No_Data_Found THEN
625 l_param_id := NULL;
626 -- show a message about which attribute group was not found and do nothing
627 debug_msg ('UPLOAD_FUNCTION_MAPPINGS : Parameter '|| p_map_par_int_name || ' was not found. Skipping the Mappings.' );
628 END;
629
630 IF l_param_id IS NOT NULL THEN
631
632 SELECT APPLICATION_ID
633 INTO l_app_id
634 FROM FND_APPLICATION
635 WHERE APPLICATION_SHORT_NAME = p_map_app_short_name;
636
637 BEGIN
638
639 SELECT MAPPED_TO_GROUP_PK2, MAPPED_TO_GROUP_PK3, MAPPED_ATTRIBUTE
640 INTO l_pk2, l_pk3, l_map_attr
641 FROM EGO_MAPPINGS_B
642 WHERE FUNCTION_ID = l_function_id
643 AND MAPPED_OBJ_PK1_VAL = l_action_id
644 AND FUNC_PARAM_ID = l_param_id
645 AND MAPPED_OBJ_TYPE = p_map_obj_type
646 AND MAPPED_TO_GROUP_TYPE = p_map_to_grp_type
647 AND NVL(MAPPED_TO_GROUP_PK2, '-1') = NVL(p_map_ext_attrs, '-1')
648 AND NVL(MAPPED_TO_GROUP_PK3, '-1') = NVL(p_map_context, '-1')
649 AND MAPPED_ATTRIBUTE = p_map_attr;
650
651 EXCEPTION
652 WHEN NO_DATA_FOUND THEN
653
654 INSERT INTO EGO_MAPPINGS_B
655 (
656 FUNCTION_ID,
657 MAPPED_OBJ_TYPE,
658 MAPPED_OBJ_PK1_VAL,
659 FUNC_PARAM_ID,
660 MAPPED_TO_GROUP_TYPE,
661 MAPPED_TO_GROUP_PK1,
662 MAPPED_TO_GROUP_PK2,
663 MAPPED_TO_GROUP_PK3,
664 MAPPED_ATTRIBUTE,
665 CREATED_BY,
666 CREATION_DATE,
667 LAST_UPDATED_BY,
668 LAST_UPDATE_DATE,
669 LAST_UPDATE_LOGIN
670 )
671 VALUES
672 (
673 l_function_id,
674 p_map_obj_type,
675 l_action_id,
676 l_param_id,
677 p_map_to_grp_type,
678 l_app_id,
679 p_map_ext_attrs,
680 p_map_context,
681 p_map_attr,
682 DECODE(p_owner, 'SEED', 1, 0),
683 SYSDATE,
684 DECODE(p_owner, 'SEED', 1, 0),
685 SYSDATE,
686 0
687 );
688
689 WHEN TOO_MANY_ROWS THEN
690 NULL;
691 END;
692 END IF;
693 END IF; -- parameter found
694 END IF; -- function found
695 END;
696 EXCEPTION
697 WHEN OTHERS THEN
698 debug_msg('UPLOAD_FUNCTION_MAPPINGS: in exception ' ||SQLERRM);
699 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
700 END UPLOAD_FUNCTION_MAPPINGS;
701
702 PROCEDURE copy_associations(p_api_version IN NUMBER,
703 p_init_msg_list IN VARCHAR2,
704 p_commit IN VARCHAR2,
705 p_new_template_code IN VARCHAR2,
706 p_src_template_code IN NUMBER,
707 p_object_id IN NUMBER,
708 x_return_status OUT NOCOPY VARCHAR2,
709 x_msg_count OUT NOCOPY NUMBER,
710 x_msg_data OUT NOCOPY VARCHAR2)
711 IS
712
713 CURSOR c_get_assoc_csr IS
714 SELECT data_level, attr_group_id, enabled_flag, view_privilege_id, edit_privilege_id
715 FROM EGO_OBJ_AG_ASSOCS_B assoc
716 WHERE assoc.OBJECT_ID = p_object_id
717 AND assoc.CLASSIFICATION_CODE = p_src_template_code;
718
719 CURSOR c_gen_association_id IS
720 SELECT EGO_ASSOCS_S.NEXTVAL
721 FROM dual;
722
723 l_association_id NUMBER;
724 l_api_name VARCHAR2(50);
725 l_errorcode NUMBER;
726
727 BEGIN
728 l_api_name := 'copy_associations';
729
730 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
731 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
732 '100: Entered OKC_UDA_TEMPLATES_UTIL.copy_associations');
733 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
734 '101: p_new_template_code is: ' || p_new_template_code);
735 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
736 '102: p_src_template_code is: ' || p_src_template_code);
737 END IF;
738 -- Initialize message list if p_init_msg_list is set to TRUE.
739 IF FND_API.to_Boolean( p_init_msg_list ) THEN
740 FND_MSG_PUB.initialize;
741 END IF;
742
743 x_return_status := FND_API.G_RET_STS_SUCCESS;
744
745 FOR c_get_assoc_csr_rec IN c_get_assoc_csr LOOP
746
747 OPEN c_gen_association_id;
748 FETCH c_gen_association_id INTO l_association_id;
749 CLOSE c_gen_association_id;
750
751 EGO_EXT_FWK_PUB.Create_Association (
752 p_api_version => p_api_version ,
753 p_association_id => l_association_id ,
754 p_object_id => p_object_id ,
755 p_classification_code => p_new_template_code,
756 p_data_level => c_get_assoc_csr_rec.data_level ,
757 p_attr_group_id => c_get_assoc_csr_rec.attr_group_id,
758 p_enabled_flag => c_get_assoc_csr_rec.enabled_flag ,
759 p_view_privilege_id => c_get_assoc_csr_rec.view_privilege_id,
760 p_edit_privilege_id => c_get_assoc_csr_rec.edit_privilege_id ,
761 p_init_msg_list => p_init_msg_list ,
762 p_commit => p_commit ,
763 x_association_id => l_association_id,
764 x_return_status => x_return_status ,
765 x_errorcode => l_errorcode ,
766 x_msg_count => x_msg_count ,
767 x_msg_data => x_msg_data);
768
769 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
770 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
771 'EGO_EXT_FWK_PUB.Create_Association return status is: '
772 || x_return_status);
773 END IF;
774 -------------------------------------------------------
775 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
776 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
777 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
778 RAISE OKC_API.G_EXCEPTION_ERROR;
779 END IF;
780 --------------------------------------------------------
781
782
783 -- copy actions
784
785 copy_uda_actions(p_api_version => 1.0,
786 p_init_msg_list => FND_API.G_FALSE,
787 p_commit => FND_API.G_FALSE,
788 p_attr_group_id => c_get_assoc_csr_rec.attr_group_id,
789 p_new_template_code => p_new_template_code,
790 p_src_template_code => p_src_template_code,
791 p_object_id => p_object_id,
792 x_return_status => x_return_status,
793 x_msg_count => x_msg_count,
794 x_msg_data => x_msg_data);
795
796 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
797 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
798 'copy_uda_actions return status is: '
799 || x_return_status);
800 END IF;
801 -------------------------------------------------------
802 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
803 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
804 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
805 RAISE OKC_API.G_EXCEPTION_ERROR;
806 END IF;
807 --------------------------------------------------------
808
809
810 END LOOP;
811
812 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
813 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
814 'Leaving OKC_UDA_TEMPLATES_UTIL.'||l_api_name);
815 END IF;
816
817
818 EXCEPTION
819 WHEN FND_API.G_EXC_ERROR THEN
820 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
821 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
822 g_module || l_api_name || '.exception',
823 'Leaving copy_associations: FND_API.G_EXC_ERROR Exception');
824 END IF;
825 x_return_status := FND_API.G_RET_STS_ERROR;
826 FND_MSG_PUB.Count_And_Get(
827 p_count => x_msg_count,
828 p_data => x_msg_data
829 );
830 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
831 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
832 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
833 g_module || l_api_name || '.exception',
834 'Leaving copy_associations: FND_API.G_EXC_UNEXPECTED_ERROR Exception');
835 END IF;
836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
837 FND_MSG_PUB.Count_And_Get(
838 p_count => x_msg_count,
839 p_data => x_msg_data
840 );
841 WHEN OTHERS THEN
842 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
843 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
844 g_module || l_api_name || '.exception',
845 'Leaving copy_associations because of EXCEPTION: ' || sqlerrm);
846 END IF;
847 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
848 p_msg_name => G_UNEXPECTED_ERROR,
849 p_token1 => G_SQLCODE_TOKEN,
850 p_token1_value => sqlcode,
851 p_token2 => G_SQLERRM_TOKEN,
852 p_token2_value => sqlerrm);
853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
854 FND_MSG_PUB.Count_And_Get(
855 p_count => x_msg_count,
856 p_data => x_msg_data
857 );
858
859 END copy_associations;
860
861 PROCEDURE copy_uda_actions(p_api_version IN NUMBER,
862 p_init_msg_list IN VARCHAR2,
863 p_commit IN VARCHAR2,
864 p_attr_group_id IN VARCHAR2,
865 p_new_template_code IN VARCHAR2,
866 p_src_template_code IN NUMBER,
867 p_object_id IN NUMBER,
868 x_return_status OUT NOCOPY VARCHAR2,
869 x_msg_count OUT NOCOPY NUMBER,
870 x_msg_data OUT NOCOPY VARCHAR2)
871 IS
872
873 l_api_name VARCHAR2(50);
874
875 l_action_id NUMBER;
876 l_return_status VARCHAR2(2);
877 l_msg_count NUMBER;
878 l_msg_data VARCHAR2(3000);
879 l_errorcode NUMBER;
880 i NUMBER := 0;
881
882 CURSOR c_src_action_ids(p_attr_group_id NUMBER, p_src_template_code VARCHAR2, p_object_id NUMBER) IS
883 SELECT action_id FROM ego_actions_b
884 WHERE ATTR_GROUP_ID = p_attr_group_id
885 AND classification_code = p_src_template_code
886 AND OBJECT_ID = p_object_id;
887
888 CURSOR c_src_record(p_action_id NUMBER) IS
889 SELECT b.OBJECT_ID, b.CLASSIFICATION_CODE, b.SEQUENCE, b.ACTION_NAME, b.FUNCTION_ID,b.SECURITY_PRIVILEGE_ID, b.ENABLE_KEY_ATTRIBUTES, tl.DESCRIPTION
890 FROM EGO_ACTIONS_tl tl, EGO_ACTIONS_B b
891 WHERE b.action_id = p_action_id AND b.action_id = tl.action_id AND tl.LANGUAGE = userenv('LANG');
892
893 CURSOR c_src_action_display(p_action_id NUMBER) IS
894 SELECT ACTION_ID, EXECUTION_METHOD, DISPLAY_STYLE, PROMPT_APPLICATION_ID, PROMPT_MESSAGE_NAME, VISIBILITY_FLAG, PROMPT_FUNCTION_ID, VISIBILITY_FUNC_ID
895 FROM EGO_ACTION_DISPLAYS_B
896 WHERE ACTION_ID = p_action_id;
897
898 CURSOR c_src_func_mappings(p_function_id NUMBER, p_action_id NUMBER) IS
899 SELECT FUNCTION_ID, MAPPED_OBJ_TYPE, MAPPED_OBJ_PK1_VAL, FUNC_PARAM_ID, MAPPED_TO_GROUP_TYPE, MAPPED_TO_GROUP_PK1, MAPPED_TO_GROUP_PK2,
900 MAPPED_TO_GROUP_PK3, MAPPED_ATTRIBUTE, MAPPED_UOM_PARAMETER, VALUE_UOM_SOURCE, FIXED_UOM
901 FROM EGO_MAPPINGS_B
902 WHERE function_id = p_function_id
903 AND MAPPED_OBJ_PK1_VAL = p_action_id;
904
905 BEGIN
906
907
908
909 l_api_name := 'copy_uda_actions';
910
911 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
912 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
913 '100: Entered OKC_UDA_TEMPLATES_UTIL.copy_uda_actions');
914 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
915 '101: p_new_template_code is: ' || p_new_template_code);
916 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
917 '102: p_src_template_code is: ' || p_src_template_code);
918 END IF;
919 -- Initialize message list if p_init_msg_list is set to TRUE.
920 IF FND_API.to_Boolean( p_init_msg_list ) THEN
921 FND_MSG_PUB.initialize;
922 END IF;
923
924 x_return_status := FND_API.G_RET_STS_SUCCESS;
925
926 FOR c_src_action_ids_rec IN c_src_action_ids(p_attr_group_id, p_src_template_code, p_object_id) LOOP
927
928 FOR c_src_record_rec IN c_src_record(c_src_action_ids_rec.action_id) LOOP
929 i := i+1;
930
931 EGO_EXT_FWK_PUB.Create_Action (
932 p_api_version => 1.0 ,
933 p_object_id => c_src_record_rec.OBJECT_ID ,
934 p_classification_code => p_new_template_code ,
935 p_attr_group_id => p_attr_group_id ,
936 p_sequence => c_src_record_rec.SEQUENCE,
937 p_action_name => c_src_record_rec.ACTION_NAME ,
938 p_description => c_src_record_rec.DESCRIPTION ,
939 p_function_id => c_src_record_rec.FUNCTION_ID , -- ignored for now
940 p_enable_key_attrs => c_src_record_rec.ENABLE_KEY_ATTRIBUTES , -- ignored for now
941 p_security_privilege_id => c_src_record_rec.SECURITY_PRIVILEGE_ID ,
942 p_init_msg_list => fnd_api.g_FALSE ,
943 p_commit => fnd_api.g_FALSE ,
944 x_action_id => l_action_id ,
945 x_return_status => l_return_status ,
946 x_errorcode => l_errorcode,
947 x_msg_count => l_msg_count ,
948 x_msg_data => l_msg_data);
949
950 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
951 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
952 'EGO_EXT_FWK_PUB.Create_Action return status is: '
953 || x_return_status);
954 END IF;
955 -------------------------------------------------------
956 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
957 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
958 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
959 RAISE OKC_API.G_EXCEPTION_ERROR;
960 END IF;
961 --------------------------------------------------------
962
963
964
965 /*Once the action is created we have to create the action display data*/
966 IF (l_return_status = 'S') THEN
967 FOR c_src_action_display_rec IN c_src_action_display(c_src_action_ids_rec.action_id) LOOP
968
969 EGO_EXT_FWK_PUB.Create_Action_Display (
970 p_api_version => 1.0
971 ,p_action_id => l_action_id
972 ,p_exec_code => c_src_action_display_rec.EXECUTION_METHOD
973 ,p_display_style => c_src_action_display_rec.DISPLAY_STYLE
974 ,p_prompt_application_id => c_src_action_display_rec.PROMPT_APPLICATION_ID
975 ,p_prompt_message_name => c_src_action_display_rec.PROMPT_MESSAGE_NAME
976 ,p_visibility_flag => c_src_action_display_rec.VISIBILITY_FLAG
977 ,p_prompt_function_id => c_src_action_display_rec.PROMPT_FUNCTION_ID
978 ,p_visibility_func_id => c_src_action_display_rec.VISIBILITY_FUNC_ID
979 ,p_init_msg_list => fnd_api.g_FALSE
980 ,p_commit => fnd_api.g_FALSE
981 ,x_return_status => l_return_status
982 ,x_errorcode => l_errorcode
983 ,x_msg_count => l_msg_count
984 ,x_msg_data => l_msg_data );
985
986 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
987 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
988 'EGO_EXT_FWK_PUB.Create_Action_Display return status is: '
989 || x_return_status);
990 END IF;
991 -------------------------------------------------------
992 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
993 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
994 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
995 RAISE OKC_API.G_EXCEPTION_ERROR;
996 END IF;
997 --------------------------------------------------------
998
999
1000
1001 END LOOP;
1002 END IF;
1003 /* The actions and action displays are copied. Now copy the function parameter mappings*/
1004
1005 IF l_return_status = 'S' THEN
1006 FOR c_src_func_mappings_rec IN c_src_func_mappings(c_src_record_rec.FUNCTION_ID, c_src_action_ids_rec.action_id) LOOP
1007
1008 EGO_EXT_FWK_PUB.Create_Mapping (
1009 p_api_version => 1.0
1010 ,p_function_id => c_src_record_rec.FUNCTION_ID
1011 ,p_mapped_obj_type => c_src_func_mappings_rec.MAPPED_OBJ_TYPE
1012 ,p_mapped_obj_pk1_value => l_action_id
1013 ,p_func_param_id => c_src_func_mappings_rec.FUNC_PARAM_ID
1014 ,p_mapping_group_type => c_src_func_mappings_rec.MAPPED_TO_GROUP_TYPE
1015 ,p_mapping_group_pk1 => c_src_func_mappings_rec.MAPPED_TO_GROUP_PK1
1016 ,p_mapping_group_pk2 => c_src_func_mappings_rec.MAPPED_TO_GROUP_PK2
1017 ,p_mapping_group_pk3 => c_src_func_mappings_rec.MAPPED_TO_GROUP_PK3
1018 ,p_mapping_value => c_src_func_mappings_rec.MAPPED_ATTRIBUTE
1019 ,p_mapped_uom_parameter => c_src_func_mappings_rec.MAPPED_UOM_PARAMETER
1020 ,p_value_uom_source => c_src_func_mappings_rec.VALUE_UOM_SOURCE
1021 ,p_fixed_uom => c_src_func_mappings_rec.FIXED_UOM
1022 ,p_init_msg_list => fnd_api.g_FALSE
1023 ,p_commit => fnd_api.g_FALSE
1024 ,x_return_status => l_return_status
1025 ,x_errorcode => l_errorcode
1026 ,x_msg_count => l_msg_count
1027 ,x_msg_data => l_msg_data);
1028
1029 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1030 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1031 'EGO_EXT_FWK_PUB.Create_Mapping return status is: '
1032 || x_return_status);
1033 END IF;
1034 -------------------------------------------------------
1035 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1036 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1037 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1038 RAISE OKC_API.G_EXCEPTION_ERROR;
1039 END IF;
1040 --------------------------------------------------------
1041
1042 END LOOP;
1043 END IF;
1044 IF l_return_status <> 'S' THEN
1045 x_return_status := l_return_status;
1046 END IF;
1047 x_msg_count := l_msg_count;
1048 x_msg_data := l_msg_data;
1049 END LOOP;
1050 END LOOP;
1051
1052 IF (i = 0) THEN
1053 x_return_status := 'S';
1054 END IF;
1055
1056 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1057 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1058 'Leaving OKC_UDA_TEMPLATES_UTIL.'||l_api_name);
1059 END IF;
1060
1061
1062 EXCEPTION
1063 WHEN FND_API.G_EXC_ERROR THEN
1064 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1065 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1066 g_module || l_api_name || '.exception',
1067 'Leaving copy_uda_actions: FND_API.G_EXC_ERROR Exception');
1068 END IF;
1069 x_return_status := FND_API.G_RET_STS_ERROR;
1070 FND_MSG_PUB.Count_And_Get(
1071 p_count => x_msg_count,
1072 p_data => x_msg_data
1073 );
1074 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1075 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1076 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1077 g_module || l_api_name || '.exception',
1078 'Leaving copy_uda_actions: FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1079 END IF;
1080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1081 FND_MSG_PUB.Count_And_Get(
1082 p_count => x_msg_count,
1083 p_data => x_msg_data
1084 );
1085 WHEN OTHERS THEN
1086 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1087 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1088 g_module || l_api_name || '.exception',
1089 'Leaving copy_uda_actions because of EXCEPTION: ' || sqlerrm);
1090 END IF;
1091 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1092 p_msg_name => G_UNEXPECTED_ERROR,
1093 p_token1 => G_SQLCODE_TOKEN,
1094 p_token1_value => sqlcode,
1095 p_token2 => G_SQLERRM_TOKEN,
1096 p_token2_value => sqlerrm);
1097 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1098 FND_MSG_PUB.Count_And_Get(
1099 p_count => x_msg_count,
1100 p_data => x_msg_data
1101 );
1102
1103
1104 END copy_uda_actions;
1105
1106 PROCEDURE copy_pages(p_api_version IN NUMBER,
1107 p_init_msg_list IN VARCHAR2,
1108 p_commit IN VARCHAR2,
1109 p_new_template_code IN VARCHAR2,
1110 p_src_template_code IN NUMBER,
1111 p_object_id IN NUMBER,
1112 x_return_status OUT NOCOPY VARCHAR2,
1113 x_msg_count OUT NOCOPY NUMBER,
1114 x_msg_data OUT NOCOPY VARCHAR2)
1115 IS
1116
1117 l_api_name VARCHAR2(30);
1118 l_create_page_id NUMBER;
1119 l_association_id NUMBER;
1120 l_errorcode NUMBER;
1121
1122
1123 CURSOR c_get_pages_csr IS
1124 SELECT page_id, DATA_LEVEL_INT_NAME, internal_name,
1125 display_name, SEQUENCE, description
1126 FROM EGO_PAGES_V pages
1127 WHERE pages.CLASSIFICATION_CODE = p_src_template_code
1128 AND pages.object_id = p_object_id;
1129
1130 CURSOR c_get_page_entries_csr(p_page_id NUMBER) IS
1131 SELECT entries_view.association_id , entries_view.SEQUENCE, assoc.attr_group_id
1132 FROM EGO_PAGE_ENTRIES_V entries_view, EGO_OBJ_AG_ASSOCS_B assoc
1133 WHERE entries_view.OBJECT_ID = p_object_id
1134 AND entries_view.CLASSIFICATION_CODE = p_src_template_code
1135 AND assoc.association_id = entries_view.association_id
1136 AND entries_view. page_id = p_page_id;
1137
1138 BEGIN
1139
1140 l_api_name := 'copy_pages';
1141
1142 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1143 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1144 '100: Entered OKC_UDA_TEMPLATES_UTIL.copy_pages');
1145 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1146 '101: p_new_template_code is: ' || p_new_template_code);
1147 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1148 '102: p_src_template_code is: ' || p_src_template_code);
1149 END IF;
1150 -- Initialize message list if p_init_msg_list is set to TRUE.
1151 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1152 FND_MSG_PUB.initialize;
1153 END IF;
1154
1155 x_return_status := FND_API.G_RET_STS_SUCCESS;
1156
1157 FOR c_get_pages_csr_rec IN c_get_pages_csr LOOP
1158
1159 EGO_EXT_FWK_PUB.Create_Page
1160 (
1161 p_api_version => 1.0
1162 ,p_object_id => p_object_id
1163 ,p_classification_code => p_new_template_code
1164 ,p_data_level => c_get_pages_csr_rec.DATA_LEVEL_INT_NAME
1165 ,p_internal_name => c_get_pages_csr_rec.internal_name
1166 ,p_display_name => c_get_pages_csr_rec.display_name
1167 ,p_description => c_get_pages_csr_rec.description
1168 ,p_sequence => c_get_pages_csr_rec.sequence
1169 ,p_init_msg_list => fnd_api.g_FALSE
1170 ,p_commit => fnd_api.g_FALSE
1171 ,x_page_id => l_create_page_id
1172 ,x_return_status => x_return_status
1173 ,x_errorcode => l_errorcode
1174 ,x_msg_count => x_msg_count
1175 ,x_msg_data => x_msg_data
1176 );
1177
1178 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1179 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1180 'EGO_EXT_FWK_PUB.Create_Page return status is: '
1181 || x_return_status);
1182 END IF;
1183 -------------------------------------------------------
1184 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1185 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1186 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1187 RAISE OKC_API.G_EXCEPTION_ERROR;
1188 END IF;
1189 --------------------------------------------------------
1190
1191 FOR c_get_page_entries_csr_rec IN c_get_page_entries_csr(c_get_pages_csr_rec.page_id) LOOP
1192
1193 BEGIN
1194 SELECT association_id INTO l_association_id
1195 FROM EGO_OBJ_AG_ASSOCS_B assoc
1196 WHERE assoc.OBJECT_ID = p_object_id
1197 AND assoc.CLASSIFICATION_CODE = p_new_template_code
1198 AND assoc.ATTR_GROUP_ID = c_get_page_entries_csr_rec.attr_group_id;
1199
1200 EXCEPTION
1201 WHEN No_Data_Found THEN
1202 RAISE FND_API.G_EXC_ERROR;
1203 END;
1204
1205 EGO_EXT_FWK_PUB.Create_Page_Entry
1206 (
1207 p_api_version => 1.0
1208 ,p_page_id => l_create_page_id
1209 ,p_association_id => l_association_id
1210 ,p_sequence => c_get_page_entries_csr_rec.sequence
1211 ,p_classification_code => p_new_template_code
1212 ,p_init_msg_list => fnd_api.g_FALSE
1213 ,p_commit => fnd_api.g_FALSE
1214 ,x_return_status => x_return_status
1215 ,x_errorcode => l_errorcode
1216 ,x_msg_count => x_msg_count
1217 ,x_msg_data => x_msg_data
1218 );
1219
1220 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1221 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1222 'EGO_EXT_FWK_PUB.Create_Page_Entry return status is: '
1223 || x_return_status);
1224 END IF;
1225 -------------------------------------------------------
1226 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1227 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1228 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1229 RAISE OKC_API.G_EXCEPTION_ERROR;
1230 END IF;
1231 --------------------------------------------------------
1232
1233 END LOOP;
1234 END LOOP;
1235
1236
1237 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1238 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1239 'Leaving OKC_UDA_TEMPLATES_UTIL.'||l_api_name);
1240 END IF;
1241
1242
1243 EXCEPTION
1244 WHEN FND_API.G_EXC_ERROR THEN
1245 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1246 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1247 g_module || l_api_name || '.exception',
1248 'Leaving copy_pages: FND_API.G_EXC_ERROR Exception');
1249 END IF;
1250 x_return_status := FND_API.G_RET_STS_ERROR;
1251 FND_MSG_PUB.Count_And_Get(
1252 p_count => x_msg_count,
1253 p_data => x_msg_data
1254 );
1255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1256 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1257 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1258 g_module || l_api_name || '.exception',
1259 'Leaving copy_pages: FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1260 END IF;
1261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1262 FND_MSG_PUB.Count_And_Get(
1263 p_count => x_msg_count,
1264 p_data => x_msg_data
1265 );
1266 WHEN OTHERS THEN
1267 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1268 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1269 g_module || l_api_name || '.exception',
1270 'Leaving copy_pages because of EXCEPTION: ' || sqlerrm);
1271 END IF;
1272 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1273 p_msg_name => G_UNEXPECTED_ERROR,
1274 p_token1 => G_SQLCODE_TOKEN,
1275 p_token1_value => sqlcode,
1276 p_token2 => G_SQLERRM_TOKEN,
1277 p_token2_value => sqlerrm);
1278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1279 FND_MSG_PUB.Count_And_Get(
1280 p_count => x_msg_count,
1281 p_data => x_msg_data
1282 );
1283
1284
1285 END copy_pages;
1286
1287 PROCEDURE copy_template_details(p_api_version IN NUMBER,
1288 p_init_msg_list IN VARCHAR2,
1289 p_commit IN VARCHAR2,
1290 p_src_uda_temp_id IN NUMBER,
1291 p_new_uda_temp_id IN NUMBER,
1292 x_return_status OUT NOCOPY VARCHAR2,
1293 x_msg_count OUT NOCOPY NUMBER,
1294 x_msg_data OUT NOCOPY VARCHAR2
1295 ) IS
1296
1297 l_api_name VARCHAR2(50);
1298 l_new_template_code VARCHAR2(30);
1299 l_src_template_code VARCHAR2(30);
1300
1301 l_object_id NUMBER;
1302 l_user_id NUMBER;
1303
1304 CURSOR c_attr_grp_details IS
1305 SELECT object_id
1306 FROM FND_OBJECTS fo , okc_uda_templates_vl outv
1307 WHERE fo.OBJ_NAME = decode(outv.template_category, 'CONTRACT', 'OKC_REP_CONTRACTS_UDA',
1308 'DELIVERABLE', 'OKC_DELIVERABLE')
1309 and outv.uda_template_id = p_src_uda_temp_id;
1310
1311 BEGIN
1312
1313 l_api_name := 'copy_template_details';
1314
1315 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1316 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1317 '100: Entered OKC_UDA_TEMPLATES_UTIL.copy_template_details');
1318 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1319 '101: p_src_uda_temp_id is: ' || To_Char(p_src_uda_temp_id));
1320 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1321 '102: p_new_uda_temp_id is: ' || To_Char(p_new_uda_temp_id));
1322 END IF;
1323 -- Initialize message list if p_init_msg_list is set to TRUE.
1324 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1325 FND_MSG_PUB.initialize;
1326 END IF;
1327
1328 x_return_status := FND_API.G_RET_STS_SUCCESS;
1329
1330 -- Standard Start of API savepoint
1331 SAVEPOINT copy_temp_details_PVT;
1332
1333 l_user_id := fnd_global.user_id();
1334
1335 --Copy Deliverable Usages
1336 INSERT INTO OKC_UDA_DELIVERABLE_USAGES (DEL_USAGE_ID
1337 ,UDA_TEMPLATE_ID
1338 ,DEL_TYPE_CODE
1339 ,ATTR_GROUP_ID
1340 ,CREATED_BY
1341 ,CREATION_DATE
1342 ,LAST_UPDATED_BY
1343 ,LAST_UPDATE_DATE
1344 ,LAST_UPDATE_LOGIN
1345 ,OBJECT_VERSION_NUMBER
1346 ,SEQUENCE_NUM
1347
1348 ) SELECT OKC_UDA_DEL_USAGES_S1.NEXTVAL
1349 ,p_new_uda_temp_id
1350 ,DEL_TYPE_CODE
1351 ,ATTR_GROUP_ID
1352 ,CREATED_BY
1353 ,sysdate
1354 ,LAST_UPDATED_BY
1355 ,sysdate
1356 ,LAST_UPDATE_LOGIN
1357 ,OBJECT_VERSION_NUMBER
1358 ,SEQUENCE_NUM
1359 FROM OKC_UDA_DELIVERABLE_USAGES
1360 WHERE UDA_TEMPLATE_ID = p_src_uda_temp_id;
1361
1362 OPEN c_attr_grp_details;
1363 FETCH c_attr_grp_details INTO l_object_id;
1364 CLOSE c_attr_grp_details;
1365
1366 IF (l_object_id = NULL ) THEN
1367
1368 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1369 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1370 'l_object_id is null ');
1371 END IF;
1372
1373 RAISE OKC_API.G_EXCEPTION_ERROR;
1374 END IF;
1375
1376 --We have to send uda_template_id as classification_code to ego API's
1377 -- classification_code is varchar2 field.
1378
1379 l_new_template_code := p_new_uda_temp_id || '';
1380 l_src_template_code := p_src_uda_temp_id || '';
1381
1382 --copy associations
1383 --This will inturn copy actions and action mappings
1384
1385 copy_associations(p_api_version => 1.0,
1386 p_init_msg_list => FND_API.G_FALSE,
1387 p_commit => FND_API.G_FALSE,
1388 p_new_template_code => l_new_template_code,
1389 p_src_template_code => l_src_template_code,
1390 p_object_id => l_object_id,
1391 x_return_status => x_return_status,
1392 x_msg_count => x_msg_count,
1393 x_msg_data => x_msg_data);
1394
1395 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1396 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1397 'copy_associations return status is: '
1398 || x_return_status);
1399 END IF;
1400 -------------------------------------------------------
1401 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1402 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1403 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1404 RAISE OKC_API.G_EXCEPTION_ERROR;
1405 END IF;
1406 --------------------------------------------------------
1407
1408 --copy pages
1409
1410 copy_pages (p_api_version => 1.0,
1411 p_init_msg_list => FND_API.G_FALSE,
1412 p_commit => FND_API.G_FALSE,
1413 p_new_template_code => l_new_template_code,
1414 p_src_template_code => l_src_template_code,
1415 p_object_id => l_object_id,
1416 x_return_status => x_return_status,
1417 x_msg_count => x_msg_count,
1418 x_msg_data => x_msg_data);
1419
1420
1421
1422 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1423 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1424 'copy_pages return status is: '
1425 || x_return_status);
1426 END IF;
1427 -------------------------------------------------------
1428 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1429 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1430 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1431 RAISE OKC_API.G_EXCEPTION_ERROR;
1432 END IF;
1433 --------------------------------------------------------
1434
1435
1436 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1437 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1438 'Leaving OKC_UDA_TEMPLATES_UTIL.'||l_api_name);
1439 END IF;
1440
1441 EXCEPTION
1442 WHEN FND_API.G_EXC_ERROR THEN
1443 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1444 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1445 g_module || l_api_name || '.exception',
1446 'Leaving copy_template_details: FND_API.G_EXC_ERROR Exception');
1447 END IF;
1448
1449 ROLLBACK TO copy_temp_details_PVT;
1450
1451 x_return_status := FND_API.G_RET_STS_ERROR;
1452 FND_MSG_PUB.Count_And_Get(
1453 p_count => x_msg_count,
1454 p_data => x_msg_data
1455 );
1456 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1457 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1458 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1459 g_module || l_api_name || '.exception',
1460 'Leaving copy_template_details: FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1461 END IF;
1462 ROLLBACK TO copy_temp_details_PVT;
1463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1464 FND_MSG_PUB.Count_And_Get(
1465 p_count => x_msg_count,
1466 p_data => x_msg_data
1467 );
1468 WHEN OTHERS THEN
1469 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1470 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1471 g_module || l_api_name || '.exception',
1472 'Leaving copy_template_details because of EXCEPTION: ' || sqlerrm);
1473 END IF;
1474 ROLLBACK TO copy_temp_details_PVT;
1475 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1476 p_msg_name => G_UNEXPECTED_ERROR,
1477 p_token1 => G_SQLCODE_TOKEN,
1478 p_token1_value => sqlcode,
1479 p_token2 => G_SQLERRM_TOKEN,
1480 p_token2_value => sqlerrm);
1481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1482 FND_MSG_PUB.Count_And_Get(
1483 p_count => x_msg_count,
1484 p_data => x_msg_data
1485 );
1486
1487 END copy_template_details;
1488
1489 FUNCTION is_template_freezed(p_uda_template_id IN NUMBER )
1490 RETURN VARCHAR2
1491 IS
1492 l_status_flag VARCHAR2(1);
1493 BEGIN
1494
1495 BEGIN
1496
1497 SELECT Decode (Nvl(status_flag, 'N') , 'F', 'Y', 'N')
1498 INTO l_status_flag
1499 FROM okc_uda_templates_all
1500 WHERE uda_template_id = p_uda_template_id;
1501
1502 EXCEPTION
1503 WHEN No_Data_Found THEN
1504 RAISE;
1505 END ;
1506 RETURN l_status_flag;
1507 END is_template_freezed;
1508
1509 PROCEDURE delete_temp_details(p_api_version IN NUMBER,
1510 p_init_msg_list IN VARCHAR2,
1511 p_commit IN VARCHAR2,
1512 p_uda_temp_id IN NUMBER,
1513 x_return_status OUT NOCOPY VARCHAR2,
1514 x_msg_count OUT NOCOPY NUMBER,
1515 x_msg_data OUT NOCOPY VARCHAR2
1516 ) IS
1517
1518 l_api_name VARCHAR2(50);
1519 l_object_id NUMBER;
1520 l_uda_temp_id VARCHAR2(150);
1521 l_errorcode VARCHAR2(30);
1522
1523 CURSOR c_get_object_id IS
1524 SELECT object_id
1525 FROM FND_OBJECTS fo , okc_uda_templates_vl outv
1526 WHERE fo.OBJ_NAME = decode(outv.template_category, 'CONTRACT', 'OKC_REP_CONTRACTS_UDA',
1527 'DELIVERABLE', 'OKC_DELIVERABLE')
1528 and outv.uda_template_id = p_uda_temp_id;
1529
1530
1531 CURSOR c_pages(p_object_id NUMBER , p_uda_temp_id VARCHAR2) IS
1532 SELECT page_id
1533 FROM EGO_PAGES_B epb
1534 WHERE CLASSIFICATION_CODE = p_uda_temp_id
1535 AND OBJECT_ID = p_object_id;
1536
1537 CURSOR association_ids(p_object_id NUMBER, p_uda_temp_id VARCHAR2) IS
1538 select ASSOCIATION_ID from EGO_OBJ_AG_ASSOCS_B
1539 where object_id = p_object_id
1540 and classification_code = p_uda_temp_id;
1541
1542 CURSOR c_get_action_id(p_object_id NUMBER, p_uda_temp_id VARCHAR2) IS
1543 SELECT action_id, function_id FROM EGO_ACTIONS_B
1544 WHERE object_id = p_object_id
1545 AND classification_code = p_uda_temp_id;
1546
1547 BEGIN
1548
1549 l_api_name := 'delete_temp_details';
1550
1551 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1552 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1553 '100: Entered OKC_UDA_TEMPLATES_UTIL.delete_temp_uda_details');
1554 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1555 '101: p_src_uda_temp_id is: ' || To_Char(p_uda_temp_id));
1556 END IF;
1557 -- Initialize message list if p_init_msg_list is set to TRUE.
1558 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1559 FND_MSG_PUB.initialize;
1560 END IF;
1561
1562 x_return_status := FND_API.G_RET_STS_SUCCESS;
1563
1564 -- Standard Start of API savepoint
1565 SAVEPOINT del_temp_details_PVT;
1566
1567 --Delete Deliverable details
1568 DELETE FROM OKC_UDA_DELIVERABLE_USAGES
1569 WHERE uda_template_id = p_uda_temp_id;
1570
1571 --EGO requires template Id in varchar
1572 l_uda_temp_id := p_uda_temp_id || '';
1573
1574 OPEN c_get_object_id;
1575 FETCH c_get_object_id INTO l_object_id;
1576 IF c_get_object_id%NOTFOUND THEN
1577 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1578 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1579 'Invalid template Id: ' );
1580 END IF;
1581
1582 RAISE OKC_API.G_EXCEPTION_ERROR;
1583 END IF;
1584 CLOSE c_get_object_id;
1585
1586 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1587 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1588 '102: l_object_id is: ' || To_Char(l_object_id));
1589 END IF;
1590
1591
1592 --Delete Pages and page Entries
1593 FOR c_pages_rec IN c_pages(l_object_id, l_uda_temp_id) LOOP
1594
1595 EGO_EXT_FWK_PUB.DELETE_PAGE
1596 (
1597 p_api_version => 1.0
1598 ,p_page_id => c_pages_rec.page_id
1599 ,p_init_msg_list => fnd_api.g_FALSE
1600 ,p_commit => fnd_api.g_FALSE
1601 ,x_return_status => x_return_status
1602 ,x_errorcode => l_errorcode
1603 ,x_msg_count => x_msg_count
1604 ,x_msg_data => x_msg_data
1605 );
1606
1607 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1608 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1609 'EGO_EXT_FWK_PUB.DELETE_PAGE return status is: '
1610 || x_return_status);
1611 END IF;
1612 -------------------------------------------------------
1613 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1614 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1615 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1616 RAISE OKC_API.G_EXCEPTION_ERROR;
1617 END IF;
1618 --------------------------------------------------------
1619
1620 END LOOP;
1621
1622 --Delete Associations
1623
1624 FOR association_ids_rec IN association_ids(l_object_id, l_uda_temp_id) LOOP
1625
1626 --deleting assiociations from ego tables.
1627 ego_ext_fwk_pub.Delete_Association(
1628 p_api_version => 1.0, -- IN NUMBER
1629 p_association_id => association_ids_rec.association_id, -- IN NUMBER
1630 p_init_msg_list => fnd_api.g_FALSE, -- IN VARCHAR2 := fnd_api.g_FALSE
1631 p_commit => fnd_api.g_FALSE, -- IN VARCHAR2 := fnd_api.g_FALSE
1632 p_force => fnd_api.g_FALSE, -- IN VARCHAR2 := fnd_api.g_FALSE
1633 x_return_status => x_return_status, -- OUT NOCOPY VARCHAR2
1634 x_errorcode => l_errorcode, -- OUT NOCOPY NUMBER
1635 x_msg_count => x_msg_count, -- OUT NOCOPY NUMBER
1636 x_msg_data => x_msg_data); -- OUT NOCOPY VARCHAR2
1637
1638 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1639 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1640 ' ego_ext_fwk_pub.Delete_Association return status is: '
1641 || x_return_status);
1642 END IF;
1643 -------------------------------------------------------
1644 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1645 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1646 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1647 RAISE OKC_API.G_EXCEPTION_ERROR;
1648 END IF;
1649 --------------------------------------------------------
1650
1651 -- if deleting associations is successful, proceed to delete actions
1652 IF (x_return_status = 'S' ) THEN
1653
1654 FOR c_get_action_id_rec IN c_get_action_id(l_object_id, l_uda_temp_id) LOOP
1655
1656 ego_ext_fwk_pub.Delete_Action (
1657 p_api_version => 1.0
1658 ,p_action_id => c_get_action_id_rec.action_id
1659 ,p_init_msg_list => fnd_api.g_FALSE
1660 ,p_commit => fnd_api.g_FALSE
1661 ,x_return_status => x_return_status
1662 ,x_errorcode => l_errorcode, -- OUT NOCOPY NUMBER
1663 x_msg_count => x_msg_count, -- OUT NOCOPY NUMBER
1664 x_msg_data => x_msg_data);
1665
1666 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1667 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1668 ' ego_ext_fwk_pub.Delete_Action return status is: '
1669 || x_return_status);
1670 END IF;
1671 -------------------------------------------------------
1672 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1673 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1674 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1675 RAISE OKC_API.G_EXCEPTION_ERROR;
1676 END IF;
1677 --------------------------------------------------------
1678
1679 IF (x_return_status = 'S' ) THEN
1680
1681 ego_ext_fwk_pub.Delete_Action_Display (
1682 p_api_version => 1.0
1683 ,p_action_id => c_get_action_id_rec.action_id
1684 ,p_init_msg_list => fnd_api.g_FALSE
1685 ,p_commit => fnd_api.g_FALSE
1686 ,x_return_status => x_return_status
1687 ,x_errorcode => l_errorcode, -- OUT NOCOPY NUMBER
1688 x_msg_count => x_msg_count, -- OUT NOCOPY NUMBER
1689 x_msg_data => x_msg_data);
1690
1691
1692 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1693 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1694 ' ego_ext_fwk_pub.Delete_Action_Display return status is: '
1695 || x_return_status);
1696 END IF;
1697 -------------------------------------------------------
1698 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1699 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1700 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1701 RAISE OKC_API.G_EXCEPTION_ERROR;
1702 END IF;
1703 --------------------------------------------------------
1704
1705
1706 IF (x_return_status = 'S' ) THEN
1707
1708 ego_ext_fwk_pub.Delete_Func_Mapping (
1709 p_api_version => 1.0
1710 ,p_function_id => c_get_action_id_rec.function_id
1711 ,p_mapped_obj_type => 'A' -- hardcoded to 'A'
1712 ,p_mapped_obj_pk1_value => c_get_action_id_rec.action_id
1713 ,p_init_msg_list => fnd_api.g_FALSE
1714 ,p_commit => fnd_api.g_FALSE
1715 ,x_return_status => x_return_status
1716 ,x_errorcode => l_errorcode, -- OUT NOCOPY NUMBER
1717 x_msg_count => x_msg_count, -- OUT NOCOPY NUMBER
1718 x_msg_data => x_msg_data);
1719
1720 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1721 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1722 ' ego_ext_fwk_pub.Delete_Action_Display return status is: '
1723 || x_return_status);
1724 END IF;
1725 -------------------------------------------------------
1726 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1727 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1728 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1729 RAISE OKC_API.G_EXCEPTION_ERROR;
1730 END IF;
1731 --------------------------------------------------------
1732
1733 END IF;
1734 END IF;
1735 END LOOP;
1736 END IF ;
1737 END LOOP;
1738
1739 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1740 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,G_MODULE||l_api_name,
1741 'Leaving OKC_UDA_TEMPLATES_UTIL.'||l_api_name);
1742 END IF;
1743
1744 EXCEPTION
1745 WHEN FND_API.G_EXC_ERROR THEN
1746 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1747 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1748 g_module || l_api_name || '.exception',
1749 'Leaving delete_temp_details: FND_API.G_EXC_ERROR Exception');
1750 END IF;
1751
1752 ROLLBACK TO del_temp_details_PVT;
1753
1754 x_return_status := FND_API.G_RET_STS_ERROR;
1755 FND_MSG_PUB.Count_And_Get(
1756 p_count => x_msg_count,
1757 p_data => x_msg_data
1758 );
1759 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1760 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1761 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1762 g_module || l_api_name || '.exception',
1763 'Leaving delete_temp_details: FND_API.G_EXC_UNEXPECTED_ERROR Exception');
1764 END IF;
1765 ROLLBACK TO del_temp_details_PVT;
1766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1767 FND_MSG_PUB.Count_And_Get(
1768 p_count => x_msg_count,
1769 p_data => x_msg_data
1770 );
1771 WHEN OTHERS THEN
1772 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1773 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION ,
1774 g_module || l_api_name || '.exception',
1775 'Leaving delete_temp_details because of EXCEPTION: ' || sqlerrm);
1776 END IF;
1777 ROLLBACK TO del_temp_details_PVT;
1778 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1779 p_msg_name => G_UNEXPECTED_ERROR,
1780 p_token1 => G_SQLCODE_TOKEN,
1781 p_token1_value => sqlcode,
1782 p_token2 => G_SQLERRM_TOKEN,
1783 p_token2_value => sqlerrm);
1784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1785 FND_MSG_PUB.Count_And_Get(
1786 p_count => x_msg_count,
1787 p_data => x_msg_data
1788 );
1789
1790 END delete_temp_details;
1791
1792
1793 END OKC_UDA_TEMPLATES_UTIL;