[Home] [Help]
PACKAGE BODY: APPS.PO_UDA_LOAD_TEMPLATES
Source
1 PACKAGE BODY po_uda_load_templates AS
2 /* $Header: PO_UDA_LOAD_TEMPLATES.plb 120.18.12020000.2 2013/04/17 04:13:23 amalick ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) := PO_LOG.get_package_base('PO_UDA_LOAD_TEMPLATES');
5
6 -- forward declaration
7 PROCEDURE load_msg (p_msg IN varchar2);
8
9 PROCEDURE LOAD_TEMPLATES
10 (
11 p_functional_area VARCHAR2,
12 p_document_level VARCHAR2,
13 p_document_type VARCHAR2,
14 p_document_style VARCHAR2,
15 p_revision NUMBER,
16 p_entity_code VARCHAR2,
17 p_display_name VARCHAR2,
18 p_comments VARCHAR2,
19 p_owner VARCHAR2
20 )
21 IS
22 v_count NUMBER;
23 l_template_id NUMBER;
24 l_revision NUMBER;
25 l_entity_code VARCHAR2(300);
26 l_display_name VARCHAR2(250);
27 l_can_update VARCHAR2(10);
28
29 l_return_status VARCHAR2(3);
30 l_msg_count NUMBER;
31 l_msg_data VARCHAR2(300);
32
33 l_new_template_id NUMBER;
34
35 CURSOR c_get_seeded_usage_id(p_template_id NUMBER) IS
36 SELECT usage_id
37 FROM po_uda_ag_template_usages
38 WHERE template_id = p_template_id
39 AND last_updated_by =0 ;
40
41
42 BEGIN
43
44 load_msg('load_templates: called for p_functional_area '||p_functional_area ||' p_document_level '
45 ||p_document_level ||' p_document_type '||p_document_type ||' p_document_style '||p_document_style
46 ||' p_revision '||p_revision ||' p_entity_code '||p_entity_code || ' p_display_name ' ||p_display_name
47 ||' p_comments ' ||p_comments || ' p_owner '|| p_owner);
48
49
50 SELECT COUNT(*)
51 INTO v_count
52 FROM PO_UDA_AG_TEMPLATES
53 WHERE NVL(FUNCTIONAL_AREA, '-1') = NVL(p_functional_area, '-1')
54 AND NVL(DOCUMENT_LEVEL, '-1') = NVL(p_document_level, '-1')
55 AND NVL(DOCUMENT_TYPE, '-1') = NVL(p_document_type, '-1')
56 AND NVL(DOCUMENT_STYLE_ID, -1) = DECODE(p_document_style, null, -1, 1);
57
58
59 load_msg('load_templates: v_count ' || v_count);
60
61 IF (v_count = 0) THEN
62
63 SELECT PO_UDA_AG_TEMPLATES_S.nextval
64 INTO l_template_id
65 FROM DUAL
66 WHERE ROWNUM = 1;
67
68 load_msg('load_templates: l_template_id '||l_template_id);
69
70 INSERT INTO PO_UDA_AG_TEMPLATES
71 (
72 TEMPLATE_ID,
73 ENTITY_CODE,
74 LAST_UPDATE_DATE,
75 LAST_UPDATED_BY,
76 LAST_UPDATE_LOGIN,
77 CREATION_DATE,
78 CREATED_BY,
79 FUNCTIONAL_AREA,
80 DOCUMENT_LEVEL,
81 DOCUMENT_TYPE,
82 DOCUMENT_STYLE_ID,
83 REVISION
84 )
85 VALUES
86 (
87 l_template_id,
88 p_entity_code,
89 SYSDATE,
90 DECODE(p_owner, 'SEED', 1, 0),
91 0,
92 SYSDATE,
93 DECODE(p_owner, 'SEED', 1, 0),
94 p_functional_area,
95 p_document_level,
96 p_document_type,
97 decode(p_functional_area, 'PURCHASING', 1, null),
98 0
99 );
100
101 load_msg('load_templates: after intering base table');
102
103 INSERT INTO PO_UDA_AG_TEMPLATES_TL
104 (
105 TEMPLATE_ID,
106 DISPLAY_NAME,
107 LANGUAGE,
108 SOURCE_LANG,
109 LAST_UPDATE_DATE,
110 LAST_UPDATED_BY,
111 LAST_UPDATE_LOGIN,
112 CREATION_DATE,
113 CREATED_BY,
114 COMMENTS
115 )
116 SELECT
117 l_template_id,
118 p_display_name,
119 L.LANGUAGE_CODE,
120 USERENV('LANG'),
121 SYSDATE,
122 DECODE(p_owner, 'SEED', 1, 0),
123 0,
124 SYSDATE,
125 DECODE(p_owner, 'SEED', 1, 0),
126 p_comments
127 FROM FND_LANGUAGES L
128 WHERE L.INSTALLED_FLAG in ('I', 'B')
129 AND NOT EXISTS
130 ( SELECT NULL
131 FROM PO_UDA_AG_TEMPLATES_TL T
132 WHERE T.TEMPLATE_ID = l_template_id
133 AND T.LANGUAGE = L.LANGUAGE_CODE);
134
135
136 load_msg('load_templates: after inserting tel table');
137
138 ELSE
139
140 load_msg('load_templates: in update mode');
141
142 SELECT TB.TEMPLATE_ID, TB.REVISION, TB.ENTITY_CODE
143 INTO l_template_id, l_revision, l_entity_code
144 FROM PO_UDA_AG_TEMPLATES TB
145 WHERE NVL(TB.FUNCTIONAL_AREA, '-1') = NVL(p_functional_area, '-1')
146 AND NVL(TB.DOCUMENT_LEVEL, '-1') = NVL(p_document_level, '-1')
147 AND NVL(TB.DOCUMENT_TYPE, '-1') = NVL(p_document_type, '-1')
148 AND NVL(TB.DOCUMENT_STYLE_ID, -1) = decode (p_document_style, null, -1, 1)
149 AND TB.REVISION = (
150 SELECT MAX(T.REVISION)
151 FROM PO_UDA_AG_TEMPLATES T
152 WHERE NVL(T.FUNCTIONAL_AREA, '-1') = NVL(p_functional_area, '-1')
153 AND NVL(T.DOCUMENT_LEVEL, '-1') = NVL(p_document_level, '-1')
154 AND NVL(T.DOCUMENT_TYPE, '-1') = NVL(p_document_type, '-1')
155 AND NVL(T.DOCUMENT_STYLE_ID, -1) = decode (p_document_style, null, -1, 1)
156 );
157
158 INSERT INTO PO_UDA_AG_TEMPLATES_TL
159 (
160 TEMPLATE_ID,
161 DISPLAY_NAME,
162 LANGUAGE,
163 SOURCE_LANG,
164 LAST_UPDATE_DATE,
165 LAST_UPDATED_BY,
166 LAST_UPDATE_LOGIN,
167 CREATION_DATE,
168 CREATED_BY,
169 COMMENTS
170 )
171 SELECT
172 l_template_id,
173 p_display_name,
174 L.LANGUAGE_CODE,
175 USERENV('LANG'),
176 SYSDATE,
177 DECODE(p_owner, 'SEED', 1, 0),
178 0,
179 SYSDATE,
180 DECODE(p_owner, 'SEED', 1, 0),
181 p_comments
182 FROM fnd_languages L
183 where L.INSTALLED_FLAG in ('I', 'B')
184 AND NOT EXISTS
185 ( select NULL
186 from PO_UDA_AG_TEMPLATES_TL T
187 where T.TEMPLATE_ID = l_template_id
188 and T.LANGUAGE = L.LANGUAGE_CODE);
189
190 load_msg('load_templates: update mode : l_template_id ' ||l_template_id ||' l_revision '||l_revision ||' l_entity_code '|| l_entity_code || ' l_display_name ' || l_display_name );
191
192 l_can_update := PO_UDA_TEMPLATES_UTIL.can_update_delete(l_template_id);
193
194 load_msg('load_templates: l_can_update'|| l_can_update);
195
196 IF l_can_update = 'true' THEN
197
198 UPDATE PO_UDA_AG_TEMPLATES_TL
199 SET COMMENTS = p_comments,
200 source_lang = USERENV('LANG')
201 WHERE TEMPLATE_ID = l_template_id
202 AND LANGUAGE = USERENV('LANG');
203
204 load_msg('load_templates: updated comments ');
205
206 UPDATE PO_UDA_AG_TEMPLATES
207 SET COMPILED_FLAG ='N'
208 WHERE TEMPLATE_ID = l_template_id;
209
210 load_msg('load_templates: compile flag reset ');
211
212 ELSE
213
214 PO_UDA_TEMPLATES_UTIL.copy_uda_temp_header
215 (
216 p_api_version => 1.0,
217 p_src_template_id => l_template_id,
218 x_new_template_id => l_new_template_id,
219 x_return_status => l_return_status,
220 x_msg_count => l_msg_count,
221 x_msg_data => l_msg_data
222 );
223
224 load_msg('load_templates: new template id is '|| l_new_template_id);
225
226
227 PO_UDA_TEMPLATES_UTIL.copy_uda_temp_usages
228 (
229 p_api_version => 1.0,
230 p_new_template_id => l_new_template_id,
231 p_src_template_id => l_template_id ,
232 x_return_status => l_return_status,
233 x_msg_count => l_msg_count,
234 x_msg_data => l_msg_data
235 );
236
237 load_msg('load_templates: usages created ');
238
239
240 l_template_id := l_new_template_id;
241
242 END IF;
243
244 load_msg('load_templates: Deleting all seeded usage attribute groups for template '|| l_template_id);
245
246 FOR c_get_seeded_usage_id_rec IN c_get_seeded_usage_id(l_template_id) LOOP
247
248 PO_UDA_TEMPLATES_UTIL.DELETE_USAGE(
249 p_api_version => 1.0,
250 p_usage_id => c_get_seeded_usage_id_rec.usage_id,
251 x_return_status => l_return_status,
252 x_msg_count => l_msg_count,
253 x_msg_data => l_msg_data);
254
255 DELETE FROM po_uda_ag_template_usages WHERE usage_id = c_get_seeded_usage_id_rec.usage_id;
256
257 END LOOP ;
258
259 load_msg('After deleting the seeded usages ');
260
261 END IF;
262
263 g_pkg_template_id := l_template_id;
264 load_msg('load_templates: g_pkg_template_id ' ||g_pkg_template_id);
265 load_msg('load_templates: exiting');
266
267 EXCEPTION
268 WHEN OTHERS THEN
269 load_msg('load_templates: in exception ' ||SQLERRM);
270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
271
272 END LOAD_TEMPLATES;
273
274 PROCEDURE LOAD_TEMPLATES_NLS
275 (
276 p_functional_area VARCHAR2,
277 p_document_level VARCHAR2,
278 p_document_type VARCHAR2,
279 p_document_style VARCHAR2,
280 p_revision NUMBER,
281 p_entity_code VARCHAR2,
282 p_display_name VARCHAR2,
283 p_comments VARCHAR2,
284 p_owner VARCHAR2
285 )
286 IS
287
288 l_template_id NUMBER;
289 l_revision NUMBER;
290 l_entity_code VARCHAR2(300);
291 l_can_update VARCHAR2(10);
292
293
294 BEGIN
295 load_msg('load_templates_nls: called for p_functional_area '||p_functional_area ||' p_document_level '
296 ||p_document_level ||' p_document_type '||p_document_type ||' p_document_style '||p_document_style
297 ||' p_revision '||p_revision ||' p_entity_code '||p_entity_code || ' p_display_name ' ||p_display_name
298 ||' p_comments ' ||p_comments || ' p_owner '|| p_owner);
299
300
301 load_msg('load_templates_nls: in update mode');
302
303 SELECT TB.TEMPLATE_ID, TB.REVISION, TB.ENTITY_CODE
304 INTO l_template_id, l_revision, l_entity_code
305 FROM PO_UDA_AG_TEMPLATES TB
306 WHERE NVL(TB.FUNCTIONAL_AREA, '-1') = NVL(p_functional_area, '-1')
307 AND NVL(TB.DOCUMENT_LEVEL, '-1') = NVL(p_document_level, '-1')
308 AND NVL(TB.DOCUMENT_TYPE, '-1') = NVL(p_document_type, '-1')
309 AND NVL(TB.DOCUMENT_STYLE_ID, -1) = decode (p_document_style, null, -1, 1)
310 AND TB.REVISION = (
311 SELECT MAX(T.REVISION)
312 FROM PO_UDA_AG_TEMPLATES T
313 WHERE NVL(T.FUNCTIONAL_AREA, '-1') = NVL(p_functional_area, '-1')
314 AND NVL(T.DOCUMENT_LEVEL, '-1') = NVL(p_document_level, '-1')
315 AND NVL(T.DOCUMENT_TYPE, '-1') = NVL(p_document_type, '-1')
316 AND NVL(T.DOCUMENT_STYLE_ID, -1) = decode (p_document_style, null, -1, 1)
317 );
318
319 load_msg('load_templates_nls: update mode : l_template_id ' ||l_template_id ||' l_revision '||l_revision ||' l_entity_code '|| l_entity_code );
320
321 l_can_update := PO_UDA_TEMPLATES_UTIL.can_update_delete(l_template_id);
322
323 load_msg('load_templates_nls: l_can_update'|| l_can_update);
324
325 IF l_can_update = 'true' THEN
326
327 UPDATE PO_UDA_AG_TEMPLATES_TL
328 SET DISPLAY_NAME = p_display_name,
329 COMMENTS = p_comments,
330 SOURCE_LANG = USERENV('LANG'),
331 LAST_UPDATE_DATE = SYSDATE,
332 LAST_UPDATED_BY = DECODE(p_owner, 'SEED', 1, 0),
333 LAST_UPDATE_LOGIN = 0
334 WHERE TEMPLATE_ID = l_template_id
335 AND LANGUAGE = USERENV('LANG');
336
337 END IF ;
338
339 g_pkg_template_id := l_template_id;
340
341 load_msg('load_templates_nls: exiting');
342
343 EXCEPTION
344 WHEN OTHERS THEN
345 load_msg('load_templates_nls: in exception ' ||SQLERRM);
346 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
347 END LOAD_TEMPLATES_NLS;
348
349
350 PROCEDURE LOAD_TEMPLATE_USAGES
351 (
352 p_desc_flex_context_code VARCHAR2,
353 p_attribute_category VARCHAR2,
354 p_attribute1 VARCHAR2,
355 p_attribute2 VARCHAR2,
356 p_attribute3 VARCHAR2,
357 p_attribute4 VARCHAR2,
358 p_attribute5 VARCHAR2,
359 p_attribute6 VARCHAR2,
360 p_attribute7 VARCHAR2,
361 p_attribute8 VARCHAR2,
362 p_attribute9 VARCHAR2,
363 p_attribute10 VARCHAR2,
364 p_attribute11 VARCHAR2,
365 p_attribute12 VARCHAR2,
366 p_attribute13 VARCHAR2,
367 p_attribute14 VARCHAR2,
371 p_attribute18 VARCHAR2,
368 p_attribute15 VARCHAR2,
369 p_attribute16 VARCHAR2,
370 p_attribute17 VARCHAR2,
372 p_attribute19 VARCHAR2,
373 p_attribute20 VARCHAR2,
374 p_attribute_group_sequence NUMBER,
375 p_owner VARCHAR2
376 )
377 IS
378 l_entity_code VARCHAR2(255);
379 l_attr_grp_id NUMBER;
380 l_association_id NUMBER;
381 v_count NUMBER;
382
383 BEGIN
384
385
386 SELECT ENTITY_CODE
387 INTO l_entity_code
388 FROM PO_UDA_AG_TEMPLATES
389 WHERE TEMPLATE_ID = g_pkg_template_id;
390
391 BEGIN
392
393 SELECT ATTR_GROUP_ID
394 INTO l_attr_grp_id
395 FROM EGO_FND_DSC_FLX_CTX_EXT ATTR_GRP
396 WHERE ATTR_GRP.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_desc_flex_context_code
397 AND ATTR_GRP.DESCRIPTIVE_FLEXFIELD_NAME = l_entity_code;
398
399 EXCEPTION
400 WHEN No_Data_Found THEN
401 l_attr_grp_id := NULL;
402 -- show a message about which attribute group was not found and do nothing
403 load_msg ('LOAD_TEMPLATE_USAGES : attribute group '||p_desc_flex_context_code || ' was not found. Skipping the usage.' );
404 END;
405
406 IF l_attr_grp_id IS NOT NULL THEN
407 SELECT COUNT(*)
408 INTO v_count
409 FROM PO_UDA_AG_TEMPLATE_USAGES
410 WHERE TEMPLATE_ID = g_pkg_template_id
411 AND ATTRIBUTE_GROUP_ID = l_attr_grp_id
412 AND (p_attribute_category IS NULL OR ATTRIBUTE_CATEGORY = p_attribute_category)
413 AND (p_attribute1 IS NULL OR NVL(ATTRIBUTE1, '') = p_attribute1)
414 AND (p_attribute2 IS NULL OR NVL(ATTRIBUTE2, '') = p_attribute2)
415 AND (p_attribute3 IS NULL OR NVL(ATTRIBUTE3, '') = p_attribute3)
416 AND (p_attribute4 IS NULL OR NVL(ATTRIBUTE4, '') = p_attribute4)
417 AND (p_attribute5 IS NULL OR NVL(ATTRIBUTE5, '') = p_attribute5)
418 AND (p_attribute6 IS NULL OR NVL(ATTRIBUTE6, '') = p_attribute6)
419 AND (p_attribute7 IS NULL OR NVL(ATTRIBUTE7, '') = p_attribute7)
420 AND (p_attribute8 IS NULL OR NVL(ATTRIBUTE8, '') = p_attribute8)
421 AND (p_attribute9 IS NULL OR NVL(ATTRIBUTE9, '') = p_attribute9)
422 AND (p_attribute10 IS NULL OR NVL(ATTRIBUTE10, '') = p_attribute10)
423 AND (p_attribute11 IS NULL OR NVL(ATTRIBUTE11, '') = p_attribute11)
424 AND (p_attribute12 IS NULL OR NVL(ATTRIBUTE12, '') = p_attribute12)
425 AND (p_attribute13 IS NULL OR NVL(ATTRIBUTE13, '') = p_attribute13)
426 AND (p_attribute14 IS NULL OR NVL(ATTRIBUTE14, '') = p_attribute14)
427 AND (p_attribute15 IS NULL OR NVL(ATTRIBUTE15, '') = p_attribute15)
428 AND (p_attribute16 IS NULL OR NVL(ATTRIBUTE16, '') = p_attribute16)
429 AND (p_attribute17 IS NULL OR NVL(ATTRIBUTE17, '') = p_attribute17)
430 AND (p_attribute18 IS NULL OR NVL(ATTRIBUTE18, '') = p_attribute18)
431 AND (p_attribute19 IS NULL OR NVL(ATTRIBUTE19, '') = p_attribute19)
432 AND (p_attribute20 IS NULL OR NVL(ATTRIBUTE20, '') = p_attribute20);
433
434 IF (v_count = 0) THEN
435
436 PO_UDA_DATA_UTIL.CREATE_ASSOCIATION_ID(g_pkg_template_id, l_attr_grp_id, l_association_id);
437
438 INSERT INTO PO_UDA_AG_TEMPLATE_USAGES
439 (
440 TEMPLATE_ID,
441 ATTRIBUTE_GROUP_ID,
442 ATTRIBUTE_CATEGORY,
443 ATTRIBUTE1,
444 ATTRIBUTE2,
445 ATTRIBUTE3,
446 ATTRIBUTE4,
447 ATTRIBUTE5,
448 ATTRIBUTE6,
449 ATTRIBUTE7,
450 ATTRIBUTE8,
451 ATTRIBUTE9,
452 ATTRIBUTE10,
453 ATTRIBUTE11,
454 ATTRIBUTE12,
455 ATTRIBUTE13,
456 ATTRIBUTE14,
457 ATTRIBUTE15,
458 ATTRIBUTE16,
459 ATTRIBUTE17,
460 ATTRIBUTE18,
461 ATTRIBUTE19,
462 ATTRIBUTE20,
463 CREATED_BY,
464 CREATION_DATE,
465 LAST_UPDATE_DATE,
466 LAST_UPDATED_BY,
467 LAST_UPDATE_LOGIN,
468 ASSOCIATION_ID,
469 USAGE_ID,
470 ATTRIBUTE_GROUP_SEQUENCE
471 )
472 VALUES
473 (
474 g_pkg_template_id,
475 l_attr_grp_id,
476 p_attribute_category,
477 p_attribute1,
478 p_attribute2,
479 p_attribute3,
480 p_attribute4,
481 p_attribute5,
482 p_attribute6,
483 p_attribute7,
484 p_attribute8,
485 p_attribute9,
486 p_attribute10,
487 p_attribute11,
488 p_attribute12,
489 p_attribute13,
490 p_attribute14,
491 p_attribute15,
492 p_attribute16,
493 p_attribute17,
494 p_attribute18,
495 p_attribute19,
496 p_attribute20,
497 DECODE(p_owner, 'SEED', 1, 0),
498 SYSDATE,
499 SYSDATE,
500 DECODE(p_owner, 'SEED', 1, 0),
501 0,
502 l_association_id,
503 PO_UDA_AG_TEMPLATE_USAGES_S.nextval,
504 p_attribute_group_sequence
505 );
506
507 ELSE
508
509 UPDATE PO_UDA_AG_TEMPLATE_USAGES
510 SET ATTRIBUTE_GROUP_SEQUENCE = p_attribute_group_sequence
511 WHERE TEMPLATE_ID = g_pkg_template_id
512 AND ATTRIBUTE_GROUP_ID = l_attr_grp_id
513 AND (p_attribute_category IS NULL OR ATTRIBUTE_CATEGORY = p_attribute_category)
514 AND (p_attribute1 IS NULL OR NVL(ATTRIBUTE1, '') = p_attribute1)
515 AND (p_attribute2 IS NULL OR NVL(ATTRIBUTE2, '') = p_attribute2)
516 AND (p_attribute3 IS NULL OR NVL(ATTRIBUTE3, '') = p_attribute3)
517 AND (p_attribute4 IS NULL OR NVL(ATTRIBUTE4, '') = p_attribute4)
518 AND (p_attribute5 IS NULL OR NVL(ATTRIBUTE5, '') = p_attribute5)
519 AND (p_attribute6 IS NULL OR NVL(ATTRIBUTE6, '') = p_attribute6)
520 AND (p_attribute7 IS NULL OR NVL(ATTRIBUTE7, '') = p_attribute7)
521 AND (p_attribute8 IS NULL OR NVL(ATTRIBUTE8, '') = p_attribute8)
522 AND (p_attribute9 IS NULL OR NVL(ATTRIBUTE9, '') = p_attribute9)
523 AND (p_attribute10 IS NULL OR NVL(ATTRIBUTE10, '') = p_attribute10)
524 AND (p_attribute11 IS NULL OR NVL(ATTRIBUTE11, '') = p_attribute11)
525 AND (p_attribute12 IS NULL OR NVL(ATTRIBUTE12, '') = p_attribute12)
526 AND (p_attribute13 IS NULL OR NVL(ATTRIBUTE13, '') = p_attribute13)
527 AND (p_attribute14 IS NULL OR NVL(ATTRIBUTE14, '') = p_attribute14)
528 AND (p_attribute15 IS NULL OR NVL(ATTRIBUTE15, '') = p_attribute15)
529 AND (p_attribute16 IS NULL OR NVL(ATTRIBUTE16, '') = p_attribute16)
530 AND (p_attribute17 IS NULL OR NVL(ATTRIBUTE17, '') = p_attribute17)
531 AND (p_attribute18 IS NULL OR NVL(ATTRIBUTE18, '') = p_attribute18)
532 AND (p_attribute19 IS NULL OR NVL(ATTRIBUTE19, '') = p_attribute19)
533 AND (p_attribute20 IS NULL OR NVL(ATTRIBUTE20, '') = p_attribute20);
534
535 END IF;
536 END IF; -- attribute group found
537
538 EXCEPTION
539 WHEN OTHERS THEN
540 load_msg('LOAD_TEMPLATE_USAGES: in exception ' ||SQLERRM);
541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
542 END LOAD_TEMPLATE_USAGES;
543
544 PROCEDURE LOAD_ACTIONS
545 (
546 p_action_name VARCHAR2,
547 p_classification_code VARCHAR2,
548 p_attr_grp_internal_name VARCHAR2,
549 p_object_name VARCHAR2,
550 p_sequence NUMBER,
551 p_function_name VARCHAR2,
552 p_description VARCHAR2,
553 p_owner VARCHAR2
554 )
555 IS
556 l_object_id NUMBER;
557 l_function_id NUMBER;
558 l_attr_grp_id NUMBER;
559 l_action_id NUMBER;
560 l_entity_code VARCHAR2(100);
561
562 BEGIN
563
564 BEGIN
565
566 SELECT ENTITY_CODE
567 INTO l_entity_code
568 FROM PO_UDA_AG_TEMPLATES
569 WHERE TEMPLATE_ID = g_pkg_template_id;
570
571 SELECT OBJECT_ID
572 INTO l_object_id
573 FROM FND_OBJECTS
574 WHERE OBJ_NAME = p_object_name;
575
576 BEGIN
577 SELECT FUNCTION_ID
578 INTO l_function_id
579 FROM EGO_FUNCTIONS_B
580 WHERE INTERNAL_NAME = p_function_name;
581 EXCEPTION
582 WHEN No_Data_Found THEN
583 l_function_id := NULL;
584 -- show a message about which attribute group was not found and do nothing
585 load_msg ('LOAD_ACTIONS : Function '|| p_function_name || ' was not found. Skipping the Action.' );
586 END;
587
588 IF l_function_id IS NOT NULL THEN
589
590 BEGIN
591 SELECT ATTR_GROUP_ID
592 INTO l_attr_grp_id
593 FROM EGO_FND_DSC_FLX_CTX_EXT
594 WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_internal_name
595 AND DESCRIPTIVE_FLEXFIELD_NAME = l_entity_code;
596 EXCEPTION
597 WHEN No_Data_Found THEN
598 l_attr_grp_id := NULL;
599 -- show a message about which attribute group was not found and do nothing
600 load_msg ('LOAD_ACTIONS : attribute group '|| p_attr_grp_internal_name || ' was not found. Skipping the Action.' );
601 END;
602
603 IF l_attr_grp_id IS NOT NULL THEN
604
605 BEGIN
606 SELECT ACTION_ID
607 INTO l_action_id
608 FROM EGO_ACTIONS_B
609 WHERE ACTION_NAME = p_action_name
610 AND ATTR_GROUP_ID = l_attr_grp_id
611 AND CLASSIFICATION_CODE = g_pkg_template_id || '';
612
613 UPDATE EGO_ACTIONS_B
614 SET SEQUENCE = p_sequence
615 WHERE ACTION_ID = l_action_id;
616
617 BEGIN
618
619 SELECT ACTION_ID
620 INTO l_action_id
621 FROM EGO_ACTIONS_TL
622 WHERE ACTION_ID = l_action_id
623 AND LANGUAGE = USERENV('LANG');
624
625 UPDATE EGO_ACTIONS_TL
626 SET DESCRIPTION = p_description
627 WHERE ACTION_ID = l_action_id
628 AND LANGUAGE = USERENV('LANG');
629 EXCEPTION
630 WHEN NO_DATA_FOUND THEN
631
632 INSERT INTO EGO_ACTIONS_TL
633 (
634 ACTION_ID,
635 DESCRIPTION,
636 LANGUAGE,
637 SOURCE_LANG,
638 CREATED_BY,
639 CREATION_DATE,
640 LAST_UPDATED_BY,
641 LAST_UPDATE_DATE,
642 LAST_UPDATE_LOGIN
643 )
644 SELECT
645 l_action_id,
646 p_description,
647 L.LANGUAGE_CODE,
648 USERENV('LANG'),
649 DECODE(p_owner, 'SEED', 1, 0),
650 SYSDATE,
651 DECODE(p_owner, 'SEED', 1, 0),
652 SYSDATE,
653 0
654 FROM FND_LANGUAGES L
655 WHERE L.INSTALLED_FLAG in ('I', 'B')
656 AND NOT EXISTS
657 ( SELECT NULL
658 FROM EGO_ACTIONS_TL T
659 WHERE T.ACTION_ID =l_action_id
660 AND T.LANGUAGE = L.LANGUAGE_CODE
661 );
662 END;
663
664
665 EXCEPTION
666 WHEN NO_DATA_FOUND THEN
667
668 SELECT EGO_ACTIONS_S.nextval
669 INTO l_action_id
670 FROM DUAL
671 WHERE ROWNUM = 1;
672
673 INSERT INTO EGO_ACTIONS_B
674 (
675 ACTION_ID,
676 OBJECT_ID,
677 CLASSIFICATION_CODE,
678 ATTR_GROUP_ID,
679 SEQUENCE,
680 ACTION_NAME,
681 FUNCTION_ID,
682 CREATED_BY,
683 CREATION_DATE,
684 LAST_UPDATED_BY,
685 LAST_UPDATE_DATE,
686 LAST_UPDATE_LOGIN
687 )
688 VALUES
689 (
690 l_action_id,
691 l_object_id,
692 g_pkg_template_id || '',
693 l_attr_grp_id,
694 p_sequence,
695 p_action_name,
696 l_function_id,
697 DECODE(p_owner, 'SEED', 1, 0),
698 SYSDATE,
699 DECODE(p_owner, 'SEED', 1, 0),
700 SYSDATE,
701 0
702 );
703
704 INSERT INTO EGO_ACTIONS_TL
705 (
706 ACTION_ID,
707 DESCRIPTION,
708 LANGUAGE,
709 SOURCE_LANG,
710 CREATED_BY,
711 CREATION_DATE,
712 LAST_UPDATED_BY,
713 LAST_UPDATE_DATE,
714 LAST_UPDATE_LOGIN
715 )
716 SELECT
717 l_action_id,
718 p_description,
719 L.LANGUAGE_CODE,
720 USERENV('LANG'),
721 DECODE(p_owner, 'SEED', 1, 0),
722 SYSDATE,
723 DECODE(p_owner, 'SEED', 1, 0),
724 SYSDATE,
725 0
726 FROM FND_LANGUAGES L
727 WHERE L.INSTALLED_FLAG in ('I', 'B')
728 AND NOT EXISTS
729 ( SELECT NULL
730 FROM EGO_ACTIONS_TL T
731 WHERE T.ACTION_ID =l_action_id
732 AND T.LANGUAGE = L.LANGUAGE_CODE
733 );
734
735 WHEN TOO_MANY_ROWS THEN
736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
737 END;
738 END IF; -- AG found
739 END IF; -- function found
740 END;
741
742 EXCEPTION
743 WHEN OTHERS THEN
744 load_msg('LOAD_ACTIONS: in exception ' ||SQLERRM);
745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
746
747 END LOAD_ACTIONS;
748
749 PROCEDURE LOAD_ACTIONS_NLS
750 (
751 p_action_name VARCHAR2,
752 p_classification_code VARCHAR2,
753 p_attr_grp_internal_name VARCHAR2,
754 p_object_name VARCHAR2,
755 p_sequence NUMBER,
756 p_function_name VARCHAR2,
757 p_description VARCHAR2,
758 p_owner VARCHAR2
759 )
760 IS
761 l_object_id NUMBER;
762 l_function_id NUMBER;
763 l_attr_grp_id NUMBER;
764 l_action_id NUMBER;
765 l_entity_code VARCHAR2(100);
766
767 BEGIN
768 BEGIN
769
770 load_msg ('LOAD_ACTIONS_NLS : g_pkg_template_id ' || g_pkg_template_id || ' p_object_name ' ||p_object_name || ' p_function_name ' || p_function_name);
771
772 SELECT ENTITY_CODE
773 INTO l_entity_code
774 FROM PO_UDA_AG_TEMPLATES
775 WHERE TEMPLATE_ID = g_pkg_template_id;
776
777 SELECT OBJECT_ID
778 INTO l_object_id
779 FROM FND_OBJECTS
780 WHERE OBJ_NAME = p_object_name;
781
782 BEGIN
783 SELECT FUNCTION_ID
784 INTO l_function_id
785 FROM EGO_FUNCTIONS_B
786 WHERE INTERNAL_NAME = p_function_name;
787 EXCEPTION
788 WHEN No_Data_Found THEN
789 l_function_id := NULL;
790 -- show a message about which attribute group was not found and do nothing
791 load_msg ('LOAD_ACTIONS_NLS : Function '|| p_function_name || ' was not found. Skipping the Action.' );
792 END;
793
794 IF l_function_id IS NOT NULL THEN
795
796 BEGIN
797 SELECT ATTR_GROUP_ID
798 INTO l_attr_grp_id
799 FROM EGO_FND_DSC_FLX_CTX_EXT
800 WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_internal_name
801 AND DESCRIPTIVE_FLEXFIELD_NAME = l_entity_code;
802 EXCEPTION
803 WHEN No_Data_Found THEN
804 l_attr_grp_id := NULL;
805 -- show a message about which attribute group was not found and do nothing
806 load_msg ('LOAD_ACTIONS_NLS : attribute group '|| p_attr_grp_internal_name || ' was not found. Skipping the Action.' );
807 END;
808
809 IF l_attr_grp_id IS NOT NULL THEN
810
811 SELECT ACTION_ID
812 INTO l_action_id
813 FROM EGO_ACTIONS_B
814 WHERE ACTION_NAME = p_action_name
815 AND ATTR_GROUP_ID = l_attr_grp_id
816 AND CLASSIFICATION_CODE = g_pkg_template_id || '';
817
818
819 UPDATE EGO_ACTIONS_TL
820 SET DESCRIPTION = p_description,
821 SOURCE_LANG = USERENV('LANG'),
822 LAST_UPDATE_DATE = SYSDATE,
823 LAST_UPDATED_BY = DECODE(p_owner, 'SEED', 1, 0),
824 LAST_UPDATE_LOGIN = 0
825 WHERE ACTION_ID = l_action_id
826 AND LANGUAGE = USERENV('LANG');
827
828 END IF; -- AG found
829 END IF; -- function found
830 END;
831
832 EXCEPTION
833 WHEN OTHERS THEN
834 load_msg('LOAD_ACTIONS_NLS: in exception ' ||SQLERRM);
835 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
836
837
838 END LOAD_ACTIONS_NLS ;
839
840 PROCEDURE LOAD_ACTION_DL
841 (
842 p_action_name VARCHAR2,
843 p_classification_code VARCHAR2,
844 p_attr_grp_internal_name VARCHAR2,
845 p_data_level VARCHAR2,
846 p_visibility_flag VARCHAR2,
847 p_owner VARCHAR2
848 )
849 IS
850
851 l_data_level_id NUMBER;
852 l_attr_grp_id NUMBER;
853 l_action_id NUMBER;
854 l_visibility_flag VARCHAR2(1);
855 l_entity_code VARCHAR2(100);
856
857 BEGIN
858 BEGIN
859
860 SELECT ENTITY_CODE
861 INTO l_entity_code
862 FROM PO_UDA_AG_TEMPLATES
863 WHERE TEMPLATE_ID = g_pkg_template_id;
864
865 SELECT DATA_LEVEL_ID
866 INTO l_data_level_id
867 FROM EGO_DATA_LEVEL_B
868 WHERE DATA_LEVEL_NAME = p_data_level;
869
870 BEGIN
871
872 SELECT ATTR_GROUP_ID
873 INTO l_attr_grp_id
874 FROM EGO_FND_DSC_FLX_CTX_EXT
878 EXCEPTION
875 WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_internal_name
876 AND DESCRIPTIVE_FLEXFIELD_NAME = l_entity_code;
877
879 WHEN No_Data_Found THEN
880 l_attr_grp_id := NULL;
881 -- show a message about which attribute group was not found and do nothing
882 load_msg ('LOAD_ACTION_DL : attribute group '|| p_attr_grp_internal_name || ' was not found. Skipping the ActionDL.' );
883 END;
884
885 IF l_attr_grp_id IS NOT NULL THEN
886
887 BEGIN
888 SELECT ACTION_ID
889 INTO l_action_id
890 FROM EGO_ACTIONS_B
891 WHERE ACTION_NAME = p_action_name
892 AND ATTR_GROUP_ID = l_attr_grp_id
893 AND CLASSIFICATION_CODE = g_pkg_template_id || ''
894 AND ROWNUM = 1;
895 EXCEPTION
896 WHEN No_Data_Found THEN
897 l_action_id := NULL;
898 -- show a message about which attribute group was not found and do nothing
899 load_msg ('LOAD_ACTION_DL : Action '|| p_action_name || ' was not found. Skipping the ActionDL.' );
900 END;
901
902 IF l_action_id IS NOT NULL THEN
903
904
905 BEGIN
906
907 SELECT VISIBILITY_FLAG
908 INTO l_visibility_flag
909 FROM EGO_ACTIONS_DL
910 WHERE ACTION_ID = l_action_id
911 AND DATA_LEVEL_ID = l_data_level_id;
912
913 UPDATE EGO_ACTIONS_DL
914 SET VISIBILITY_FLAG = p_visibility_flag
915 WHERE ACTION_ID = l_action_id
916 AND DATA_LEVEL_ID = l_data_level_id;
917
918 EXCEPTION
919 WHEN NO_DATA_FOUND THEN
920
921 INSERT INTO EGO_ACTIONS_DL
922 (
923 ACTION_ID,
924 DATA_LEVEL_ID,
925 VISIBILITY_FLAG,
926 CREATED_BY,
927 CREATION_DATE,
928 LAST_UPDATED_BY,
929 LAST_UPDATE_DATE,
930 LAST_UPDATE_LOGIN
931 )
932 VALUES
933 (
934 l_action_id,
935 l_data_level_id,
936 p_visibility_flag,
937 DECODE(p_owner, 'SEED', 1, 0),
938 SYSDATE,
939 DECODE(p_owner, 'SEED', 1, 0),
940 SYSDATE,
941 0
942 );
943
944 WHEN TOO_MANY_ROWS THEN
945 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
946 END;
947 END IF; -- Action found
948 END IF; -- AG found
949 END;
950 EXCEPTION
951 WHEN OTHERS THEN
952 load_msg('LOAD_ACTION_DL: in exception ' ||SQLERRM);
953 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
954
955 END LOAD_ACTION_DL;
956
957 PROCEDURE LOAD_ACTION_DISPLAYS
958 (
959 p_action_name VARCHAR2,
960 p_function_name VARCHAR2,
961 p_object_name VARCHAR2,
962 p_attr_grp_internal_name VARCHAR2,
963 p_classification_code VARCHAR2,
964 p_exec_method VARCHAR2,
965 p_exec_trigger VARCHAR2,
966 p_display_style VARCHAR2,
967 p_app_short_name VARCHAR2,
968 p_prompt_message_name VARCHAR2,
969 p_visibility_flag VARCHAR2,
970 p_owner VARCHAR2
971 )
972 IS
973
974 l_app_id NUMBER;
975 l_data_level VARCHAR2(30);
976 l_attr_grp_id NUMBER;
977 l_action_id NUMBER;
978 l_update_login NUMBER;
979 l_entity_code VARCHAR2(100);
980
981 BEGIN
982 BEGIN
983
984 SELECT ENTITY_CODE
985 INTO l_entity_code
986 FROM PO_UDA_AG_TEMPLATES
987 WHERE TEMPLATE_ID = g_pkg_template_id;
988
989 SELECT APPLICATION_ID
990 INTO l_app_id
991 FROM FND_APPLICATION
992 WHERE APPLICATION_SHORT_NAME = p_app_short_name;
993
994 BEGIN
995 SELECT ATTR_GROUP_ID
996 INTO l_attr_grp_id
997 FROM EGO_FND_DSC_FLX_CTX_EXT
998 WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_internal_name
999 AND DESCRIPTIVE_FLEXFIELD_NAME = l_entity_code;
1000 EXCEPTION
1001 WHEN No_Data_Found THEN
1002 l_attr_grp_id := NULL;
1003 -- show a message about which attribute group was not found and do nothing
1004 load_msg ('LOAD_ACTION_DISPLAYS : attribute group '|| p_attr_grp_internal_name || ' was not found. Skipping the ActionDisplays.' );
1005 END;
1006
1007 IF l_attr_grp_id IS NOT NULL THEN
1008
1009 BEGIN
1010
1011 SELECT ACTION_ID
1012 INTO l_action_id
1013 FROM EGO_ACTIONS_B
1014 WHERE ACTION_NAME = p_action_name
1015 AND ATTR_GROUP_ID = l_attr_grp_id
1016 AND CLASSIFICATION_CODE = g_pkg_template_id || ''
1017 AND ROWNUM = 1;
1018
1019 EXCEPTION
1020 WHEN No_Data_Found THEN
1021 l_action_id := NULL;
1025
1022 -- show a message about which attribute group was not found and do nothing
1023 load_msg ('LOAD_ACTION_DISPLAYS : Action '|| p_action_name || ' was not found. Skipping the ActionDisplays.' );
1024 END;
1026 IF l_action_id IS NOT NULL THEN
1027
1028 BEGIN
1029
1030 SELECT LAST_UPDATE_LOGIN
1031 INTO l_update_login
1032 FROM EGO_ACTION_DISPLAYS_B
1033 WHERE ACTION_ID = l_action_id;
1034
1035 UPDATE EGO_ACTION_DISPLAYS_B
1036 SET EXECUTION_METHOD = p_exec_method,
1037 EXECUTION_TRIGGER = p_exec_trigger,
1038 DISPLAY_STYLE = p_display_style,
1039 PROMPT_APPLICATION_ID = l_app_id,
1040 PROMPT_MESSAGE_NAME = p_prompt_message_name,
1041 VISIBILITY_FLAG = p_visibility_flag
1042 WHERE ACTION_ID = l_action_id;
1043
1044 EXCEPTION
1045 WHEN NO_DATA_FOUND THEN
1046
1047 INSERT INTO EGO_ACTION_DISPLAYS_B
1048 (
1049 ACTION_ID,
1050 EXECUTION_METHOD,
1051 EXECUTION_TRIGGER,
1052 DISPLAY_STYLE,
1053 PROMPT_APPLICATION_ID,
1054 PROMPT_MESSAGE_NAME,
1055 VISIBILITY_FLAG,
1056 CREATED_BY,
1057 CREATION_DATE,
1058 LAST_UPDATED_BY,
1059 LAST_UPDATE_DATE,
1060 LAST_UPDATE_LOGIN
1061 )
1062 VALUES
1063 (
1064 l_action_id,
1065 p_exec_method,
1066 p_exec_trigger,
1067 p_display_style,
1068 l_app_id,
1069 p_prompt_message_name,
1070 p_visibility_flag,
1071 DECODE(p_owner, 'SEED', 1, 0),
1072 SYSDATE,
1073 DECODE(p_owner, 'SEED', 1, 0),
1074 SYSDATE,
1075 0
1076 );
1077
1078 WHEN TOO_MANY_ROWS THEN
1079 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1080 END;
1081 END IF; -- Action Found
1082 END IF; -- AG found
1083 END;
1084 EXCEPTION
1085 WHEN OTHERS THEN
1086 load_msg('LOAD_ACTION_DISPLAYS: in exception ' ||SQLERRM);
1087 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1088 END LOAD_ACTION_DISPLAYS;
1089
1090 PROCEDURE LOAD_FUNCTION_MAPPINGS
1091 (
1092 p_action_name VARCHAR2,
1093 p_function_name VARCHAR2,
1094 p_object_name VARCHAR2,
1095 p_attr_grp_internal_name VARCHAR2,
1096 p_classification_code VARCHAR2,
1097 p_map_par_int_name VARCHAR2,
1098 p_map_app_short_name VARCHAR2,
1099 p_map_obj_type VARCHAR2,
1100 p_map_to_grp_type VARCHAR2,
1101 p_map_ext_attrs VARCHAR2,
1102 p_map_context VARCHAR2,
1103 p_map_attr VARCHAR2,
1104 p_owner VARCHAR2
1105 )
1106 IS
1107
1108 l_function_id NUMBER;
1109 l_data_level NUMBER;
1110 l_attr_grp_id NUMBER;
1111 l_action_id NUMBER;
1112 l_param_id NUMBER;
1113 l_app_id NUMBER;
1114 l_pk2 VARCHAR2(50);
1115 l_pk3 VARCHAR2(50);
1116 l_map_attr VARCHAR2(100);
1117 l_entity_code VARCHAR2(100);
1118
1119 BEGIN
1120
1121 BEGIN
1122
1123 SELECT ENTITY_CODE
1124 INTO l_entity_code
1125 FROM PO_UDA_AG_TEMPLATES
1126 WHERE TEMPLATE_ID = g_pkg_template_id;
1127
1128 BEGIN
1129
1130 SELECT FUNCTION_ID
1131 INTO l_function_id
1132 FROM EGO_FUNCTIONS_B
1133 WHERE INTERNAL_NAME = p_function_name;
1134
1135 EXCEPTION
1136 WHEN No_Data_Found THEN
1137 l_function_id := NULL;
1138 -- show a message about which attribute group was not found and do nothing
1139 load_msg ('LOAD_FUNCTION_MAPPINGS : Function '|| p_function_name || ' was not found. Skipping the Mappings.' );
1140 END;
1141
1142 IF l_function_id IS NOT NULL THEN
1143
1144 BEGIN
1145 SELECT ATTR_GROUP_ID
1146 INTO l_attr_grp_id
1147 FROM EGO_FND_DSC_FLX_CTX_EXT
1148 WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_internal_name
1149 AND DESCRIPTIVE_FLEXFIELD_NAME = l_entity_code;
1150 EXCEPTION
1151 WHEN No_Data_Found THEN
1152 l_attr_grp_id := NULL;
1153 -- show a message about which attribute group was not found and do nothing
1154 load_msg ('LOAD_FUNCTION_MAPPINGS : attribute group '|| p_attr_grp_internal_name || ' was not found. Skipping the Mappings.' );
1155 END;
1156
1157 IF l_attr_grp_id IS NOT NULL THEN
1158 --<Bug 16421784> : Exception handling in case of action not present for the AG is not present
1159 BEGIN
1160 SELECT ACTION_ID
1161 INTO l_action_id
1162 FROM EGO_ACTIONS_B
1163 WHERE ACTION_NAME = p_action_name
1164 AND ATTR_GROUP_ID = l_attr_grp_id
1165 AND CLASSIFICATION_CODE = g_pkg_template_id || ''
1166 AND ROWNUM = 1;
1167
1168 EXCEPTION
1169 WHEN No_Data_Found THEN
1170 l_action_id := NULL;
1171 -- show a message about which action name was not found and do nothing
1172 load_msg ('No Action was found for action name : '|| p_action_name || ' Skipping the Mappings.' );
1173 END;
1174
1175 BEGIN
1176 SELECT FUNC_PARAM_ID
1177 INTO l_param_id
1178 FROM EGO_FUNC_PARAMS_B
1179 WHERE INTERNAL_NAME = p_map_par_int_name
1180 AND FUNCTION_ID = l_function_id;
1181 EXCEPTION
1182 WHEN No_Data_Found THEN
1183 l_param_id := NULL;
1184 -- show a message about which attribute group was not found and do nothing
1185 load_msg ('LOAD_FUNCTION_MAPPINGS : Parameter '|| p_map_par_int_name || ' was not found. Skipping the Mappings.' );
1186 END;
1187
1188 --<Bug 16421784>
1189 IF l_param_id IS NOT NULL AND l_action_id IS NOT NULL THEN
1190
1191 SELECT APPLICATION_ID
1192 INTO l_app_id
1193 FROM FND_APPLICATION
1194 WHERE APPLICATION_SHORT_NAME = p_map_app_short_name;
1195
1196 BEGIN
1197
1198 SELECT MAPPED_TO_GROUP_PK2, MAPPED_TO_GROUP_PK3, MAPPED_ATTRIBUTE
1199 INTO l_pk2, l_pk3, l_map_attr
1200 FROM EGO_MAPPINGS_B
1201 WHERE FUNCTION_ID = l_function_id
1202 AND MAPPED_OBJ_PK1_VAL = l_action_id
1203 AND FUNC_PARAM_ID = l_param_id
1204 AND MAPPED_OBJ_TYPE = p_map_obj_type
1205 AND MAPPED_TO_GROUP_TYPE = p_map_to_grp_type
1206 AND NVL(MAPPED_TO_GROUP_PK2, '-1') = NVL(p_map_ext_attrs, '-1')
1207 AND NVL(MAPPED_TO_GROUP_PK3, '-1') = NVL(p_map_context, '-1')
1208 AND MAPPED_ATTRIBUTE = p_map_attr;
1209
1210 EXCEPTION
1211 WHEN NO_DATA_FOUND THEN
1212
1213 INSERT INTO EGO_MAPPINGS_B
1214 (
1215 FUNCTION_ID,
1216 MAPPED_OBJ_TYPE,
1217 MAPPED_OBJ_PK1_VAL,
1218 FUNC_PARAM_ID,
1219 MAPPED_TO_GROUP_TYPE,
1220 MAPPED_TO_GROUP_PK1,
1221 MAPPED_TO_GROUP_PK2,
1222 MAPPED_TO_GROUP_PK3,
1223 MAPPED_ATTRIBUTE,
1224 CREATED_BY,
1225 CREATION_DATE,
1226 LAST_UPDATED_BY,
1227 LAST_UPDATE_DATE,
1228 LAST_UPDATE_LOGIN
1229 )
1230 VALUES
1231 (
1232 l_function_id,
1233 p_map_obj_type,
1234 l_action_id,
1235 l_param_id,
1236 p_map_to_grp_type,
1237 l_app_id,
1238 p_map_ext_attrs,
1239 p_map_context,
1240 p_map_attr,
1241 DECODE(p_owner, 'SEED', 1, 0),
1242 SYSDATE,
1243 DECODE(p_owner, 'SEED', 1, 0),
1244 SYSDATE,
1245 0
1246 );
1247
1248 WHEN TOO_MANY_ROWS THEN
1249 NULL;
1250 END;
1251 END IF;
1252 END IF; -- parameter found
1253 END IF; -- function found
1254 END;
1255 EXCEPTION
1256 WHEN OTHERS THEN
1257 load_msg('LOAD_FUNCTION_MAPPINGS: in exception ' ||SQLERRM);
1258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1259 END LOAD_FUNCTION_MAPPINGS;
1260
1261
1262 PROCEDURE load_msg(p_msg IN VARCHAR2) IS
1263 BEGIN
1264
1265 fnd_file.put_line (fnd_file.log, 'PO_UDA_LOAD_TEMPLATES : ' || p_msg);
1266
1267 END load_msg;
1268
1269 END PO_UDA_LOAD_TEMPLATES;