[Home] [Help]
PACKAGE BODY: APPS.PO_UDA_TEMPLATES_UTIL
Source
1 PACKAGE BODY PO_UDA_TEMPLATES_UTIL AS
2 /* $Header: PO_UDA_TEMPLATES_UTIL.plb 120.22.12020000.5 2013/04/22 23:24:42 rarajar ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) := PO_LOG.get_package_base('PO_UDA_TEMPLATES_UTIL');
5 d_appln_short_name CONSTANT VARCHAR2(20) := 'PO';
6
7 -------------------------------------------------------------------------------
8 --- Start of Comments
9 ---
10 --- Program Name: SYNC_PAGE_ENTRIES
11 --- Parameters: p_template_id
12 ---
13 --- Type: PL/sql
14 ---
15 --- It would Create all pages needed for the template passed
16 ---
17 --- Syncing the pages:
18 --- Delete all pages realted to the passed tempalte
19 --- EGO_EXT_FWK_PUB.DELETE_PAGE
20 --- Create pages for all the usage and context combination
21 --- EGO_EXT_FWK_PUB.Create_Page
22 --- EGO_EXT_FWK_PUB.Create_Page_entries
23 ---
24 --- End of Comments
25 -------------------------------------------------------------------------------
26 PROCEDURE SYNC_PAGE_ENTRIES
27 (
28 p_template_id IN NUMBER
29 ,x_return_status OUT NOCOPY VARCHAR2
30 ,x_errorcode OUT NOCOPY NUMBER
31 ,x_msg_count OUT NOCOPY NUMBER
32 ,x_msg_data OUT NOCOPY VARCHAR2
33 )
34 IS
35 CURSOR c_pages
36 (
37 p_classification_code VARCHAR2
38 )
39 IS
40 SELECT PAGE_ID
41 FROM EGO_PAGES_B
42 WHERE CLASSIFICATION_CODE = p_classification_code;
43
44
45 -----------------------------------------------------------------------------
46 -- Cursor to fetch all usage and context unique combinations entries
47 -----------------------------------------------------------------------------
48 CURSOR cur_temp_usa_page_entries
49 (
50 p_template_id NUMBER,
51 l_cur_usage VARCHAR2,
52 l_cur_context1 VARCHAR2,
53 l_cur_context2 VARCHAR2,
54 l_cur_context3 VARCHAR2,
55 l_cur_context4 VARCHAR2,
56 l_cur_context5 VARCHAR2,
57 l_cur_context6 VARCHAR2,
58 l_cur_context7 VARCHAR2,
59 l_cur_context8 VARCHAR2,
60 l_cur_context9 VARCHAR2,
61 l_cur_context10 VARCHAR2,
62 l_cur_context11 VARCHAR2,
63 l_cur_context12 VARCHAR2,
64 l_cur_context13 VARCHAR2,
65 l_cur_context14 VARCHAR2,
66 l_cur_context15 VARCHAR2,
67 l_cur_context16 VARCHAR2,
68 l_cur_context17 VARCHAR2,
69 l_cur_context18 VARCHAR2,
70 l_cur_context19 VARCHAR2,
71 l_cur_context20 VARCHAR2
72
73 )IS
74 SELECT USAGE_ID, ASSOCIATION_ID, ATTRIBUTE_GROUP_SEQUENCE
75 FROM PO_UDA_AG_TEMPLATE_USAGES
76 WHERE TEMPLATE_ID = p_template_id
77 AND NVL(ATTRIBUTE_CATEGORY, '@@@') = l_cur_usage
78 AND NVL(ATTRIBUTE1, '@@@') = l_cur_context1
79 AND NVL(ATTRIBUTE2, '@@@') = l_cur_context2
80 AND NVL(ATTRIBUTE3, '@@@') = l_cur_context3
81 AND NVL(ATTRIBUTE4, '@@@') = l_cur_context4
82 AND NVL(ATTRIBUTE5, '@@@') = l_cur_context5
83 AND NVL(ATTRIBUTE6, '@@@') = l_cur_context6
84 AND NVL(ATTRIBUTE7, '@@@') = l_cur_context7
85 AND NVL(ATTRIBUTE8, '@@@') = l_cur_context8
86 AND NVL(ATTRIBUTE9, '@@@') = l_cur_context9
87 AND NVL(ATTRIBUTE10, '@@@') = l_cur_context10
88 AND NVL(ATTRIBUTE11, '@@@') = l_cur_context11
89 AND NVL(ATTRIBUTE12, '@@@') = l_cur_context12
90 AND NVL(ATTRIBUTE13, '@@@') = l_cur_context13
91 AND NVL(ATTRIBUTE14, '@@@') = l_cur_context14
92 AND NVL(ATTRIBUTE15, '@@@') = l_cur_context15
93 AND NVL(ATTRIBUTE16, '@@@') = l_cur_context16
94 AND NVL(ATTRIBUTE17, '@@@') = l_cur_context17
95 AND NVL(ATTRIBUTE18, '@@@') = l_cur_context18
96 AND NVL(ATTRIBUTE19, '@@@') = l_cur_context19
97 AND NVL(ATTRIBUTE20, '@@@') = l_cur_context20
98 ORDER BY ATTRIBUTE_CATEGORY, ATTRIBUTE1,
99 ATTRIBUTE2, ATTRIBUTE3,
100 ATTRIBUTE4, ATTRIBUTE5,
101 ATTRIBUTE6, ATTRIBUTE7,
102 ATTRIBUTE8, ATTRIBUTE9,
103 ATTRIBUTE10, ATTRIBUTE11,
104 ATTRIBUTE12, ATTRIBUTE13,
105 ATTRIBUTE14, ATTRIBUTE15,
106 ATTRIBUTE16, ATTRIBUTE17,
107 ATTRIBUTE18, ATTRIBUTE19,
108 ATTRIBUTE20;
109 -----------------------------------------------------------------------------
110 -- Cursor to fetch all usage and context unique combinations for PAGES
111 -----------------------------------------------------------------------------
112 CURSOR cur_template_usages_pages
113 (
114 p_template_id NUMBER
115 )IS
116 SELECT NVL(ATTRIBUTE_CATEGORY, '@@@'), NVL(ATTRIBUTE1, '@@@'),
117 NVL(ATTRIBUTE2, '@@@'), NVL(ATTRIBUTE3, '@@@'),
118 NVL(ATTRIBUTE4, '@@@'), NVL(ATTRIBUTE5, '@@@'),
119 NVL(ATTRIBUTE6, '@@@'), NVL(ATTRIBUTE7, '@@@'),
120 NVL(ATTRIBUTE8, '@@@'), NVL(ATTRIBUTE9, '@@@'),
121 NVL(ATTRIBUTE10, '@@@'), NVL(ATTRIBUTE11, '@@@'),
122 NVL(ATTRIBUTE12, '@@@'), NVL(ATTRIBUTE13, '@@@'),
123 NVL(ATTRIBUTE14, '@@@'), NVL(ATTRIBUTE15, '@@@'),
124 NVL(ATTRIBUTE16, '@@@'), NVL(ATTRIBUTE17, '@@@'),
125 NVL(ATTRIBUTE18, '@@@'), NVL(ATTRIBUTE19, '@@@'),
126 NVL(ATTRIBUTE20, '@@@')
127 FROM PO_UDA_AG_TEMPLATE_USAGES
128 WHERE TEMPLATE_ID = p_template_id
129 GROUP BY ATTRIBUTE_CATEGORY, ATTRIBUTE1,
130 ATTRIBUTE2, ATTRIBUTE3,
131 ATTRIBUTE4, ATTRIBUTE5,
132 ATTRIBUTE6, ATTRIBUTE7,
133 ATTRIBUTE8, ATTRIBUTE9,
134 ATTRIBUTE10, ATTRIBUTE11,
135 ATTRIBUTE12, ATTRIBUTE13,
136 ATTRIBUTE14, ATTRIBUTE15,
137 ATTRIBUTE16, ATTRIBUTE17,
138 ATTRIBUTE18, ATTRIBUTE19,
139 ATTRIBUTE20
140 HAVING COUNT(*) > 1;
141
142 l_page_id NUMBER;
143 l_create_page_id NUMBER;
144
145 l_cur_usage VARCHAR2(50) := '';
146 l_cur_context1 VARCHAR2(50) := '';
147 l_cur_context2 VARCHAR2(50) := '';
148 l_cur_context3 VARCHAR2(50) := '';
149 l_cur_context4 VARCHAR2(50) := '';
150 l_cur_context5 VARCHAR2(50) := '';
151 l_cur_context6 VARCHAR2(50) := '';
152 l_cur_context7 VARCHAR2(50) := '';
153 l_cur_context8 VARCHAR2(50) := '';
154 l_cur_context9 VARCHAR2(50) := '';
155 l_cur_context10 VARCHAR2(50) := '';
156 l_cur_context11 VARCHAR2(50) := '';
157 l_cur_context12 VARCHAR2(50) := '';
158 l_cur_context13 VARCHAR2(50) := '';
159 l_cur_context14 VARCHAR2(50) := '';
160 l_cur_context15 VARCHAR2(50) := '';
161 l_cur_context16 VARCHAR2(50) := '';
162 l_cur_context17 VARCHAR2(50) := '';
163 l_cur_context18 VARCHAR2(50) := '';
164 l_cur_context19 VARCHAR2(50) := '';
165 l_cur_context20 VARCHAR2(50) := '';
166
167 l_internal_name VARCHAR2(150) := '';
168 l_display_name VARCHAR2(150) := '';
169
170 l_object_id NUMBER;
171 l_data_level_name VARCHAR2(100);
172 l_entity_type VARCHAR2(100);
173
174 l_usage_id NUMBER;
175 l_association_id NUMBER;
176 l_sequence NUMBER;
177 l_temp_sequence NUMBER;
178
179 d_api_name CONSTANT VARCHAR2(30) := 'CREATE_PAGE_ENTRIES';
180 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
181 d_progress NUMBER;
182
183 l_count NUMBER;
184
185
186 BEGIN
187 d_progress := 280;
188 OPEN c_pages(p_template_id || '');
189 LOOP
190
191 FETCH c_pages INTO l_page_id;
192 EXIT WHEN c_pages%NOTFOUND;
193
194 d_progress := 290;
195
196 EGO_EXT_FWK_PUB.DELETE_PAGE
197 (
198 p_api_version => 1.0
199 ,p_page_id => l_page_id
200 ,p_init_msg_list => fnd_api.g_FALSE
201 ,p_commit => fnd_api.g_FALSE
202 ,x_return_status => x_return_status
203 ,x_errorcode => x_errorcode
204 ,x_msg_count => x_msg_count
205 ,x_msg_data => x_msg_data
206 );
207
208 d_progress := 300;
209
210 END LOOP;
211 CLOSE c_pages;
212
213 d_progress := 310;
214
215 l_temp_sequence := 0;
216
217
218 OPEN cur_template_usages_pages (p_template_id);
219 LOOP
220
221 FETCH cur_template_usages_pages
222 INTO l_cur_usage, l_cur_context1,
223 l_cur_context2, l_cur_context3,
224 l_cur_context4, l_cur_context5,
225 l_cur_context6, l_cur_context7,
226 l_cur_context8, l_cur_context9,
227 l_cur_context10, l_cur_context11,
228 l_cur_context12, l_cur_context13,
229 l_cur_context14, l_cur_context15,
230 l_cur_context16, l_cur_context17,
231 l_cur_context18, l_cur_context19,
232 l_cur_context20;
233
234 EXIT WHEN cur_template_usages_pages%NOTFOUND;
235
236
237 d_progress := 320;
238
239 l_internal_name := l_cur_usage;
240
241 IF l_cur_context1 <> '' OR l_cur_context1 IS NOT NULL THEN
242 l_internal_name := l_internal_name || '_' || l_cur_context1;
243 END IF;
244 IF l_cur_context2 <> '' OR l_cur_context2 IS NOT NULL THEN
245 l_internal_name := l_internal_name || '_' || l_cur_context2;
246 END IF;
247 IF l_cur_context3 <> '' OR l_cur_context3 IS NOT NULL THEN
248 l_internal_name := l_internal_name || '_' || l_cur_context3;
249 END IF;
250 IF l_cur_context4 <> '' OR l_cur_context4 IS NOT NULL THEN
251 l_internal_name := l_internal_name || '_' || l_cur_context4;
252 END IF;
253 IF l_cur_context5 <> '' OR l_cur_context5 IS NOT NULL THEN
254 l_internal_name := l_internal_name || '_' || l_cur_context5;
255 END IF;
256 IF l_cur_context6 <> '' OR l_cur_context6 IS NOT NULL THEN
257 l_internal_name := l_internal_name || '_' || l_cur_context6;
258 END IF;
259 IF l_cur_context7 <> '' OR l_cur_context7 IS NOT NULL THEN
260 l_internal_name := l_internal_name || '_' || l_cur_context7;
261 END IF;
262 IF l_cur_context8 <> '' OR l_cur_context8 IS NOT NULL THEN
263 l_internal_name := l_internal_name || '_' || l_cur_context8;
264 END IF;
265 IF l_cur_context9 <> '' OR l_cur_context9 IS NOT NULL THEN
266 l_internal_name := l_internal_name || '_' || l_cur_context9;
267 END IF;
268 IF l_cur_context10 <> '' OR l_cur_context10 IS NOT NULL THEN
269 l_internal_name := l_internal_name || '_' || l_cur_context10;
270 END IF;
271 IF l_cur_context11 <> '' OR l_cur_context11 IS NOT NULL THEN
272 l_internal_name := l_internal_name || '_' || l_cur_context11;
273 END IF;
274 IF l_cur_context12 <> '' OR l_cur_context12 IS NOT NULL THEN
275 l_internal_name := l_internal_name || '_' || l_cur_context12;
276 END IF;
277 IF l_cur_context13 <> '' OR l_cur_context13 IS NOT NULL THEN
278 l_internal_name := l_internal_name || '_' || l_cur_context13;
279 END IF;
280 IF l_cur_context14 <> '' OR l_cur_context14 IS NOT NULL THEN
281 l_internal_name := l_internal_name || '_' || l_cur_context14;
282 END IF;
283 IF l_cur_context15 <> '' OR l_cur_context15 IS NOT NULL THEN
284 l_internal_name := l_internal_name || '_' || l_cur_context15;
285 END IF;
286 IF l_cur_context16 <> '' OR l_cur_context16 IS NOT NULL THEN
287 l_internal_name := l_internal_name || '_' || l_cur_context16;
288 END IF;
289 IF l_cur_context17 <> '' OR l_cur_context17 IS NOT NULL THEN
290 l_internal_name := l_internal_name || '_' || l_cur_context17;
291 END IF;
292 IF l_cur_context18 <> '' OR l_cur_context18 IS NOT NULL THEN
293 l_internal_name := l_internal_name || '_' || l_cur_context18;
294 END IF;
295 IF l_cur_context19 <> '' OR l_cur_context19 IS NOT NULL THEN
296 l_internal_name := l_internal_name || '_' || l_cur_context19;
297 END IF;
298 IF l_cur_context20 <> '' OR l_cur_context20 IS NOT NULL THEN
299 l_internal_name := l_internal_name || '_' || l_cur_context20;
300 END IF;
301
302 l_display_name := l_internal_name;
303
304 d_progress := 330;
305
306 SELECT entity_code
307 INTO l_entity_type
308 FROM PO_UDA_AG_TEMPLATES
309 WHERE TEMPLATE_ID = p_template_id;
310
311 d_progress := 335;
312
313 SELECT OBJECT_ID
314 INTO l_object_id
315 FROM FND_OBJECTS
316 WHERE OBJ_NAME = PO_UDA_DATA_UTIL.g_object_dff_tl(l_entity_type).l_object_name;
317
318 d_progress := 340;
319
320 SELECT DATA_LEVEL_NAME
321 INTO l_data_level_name
322 FROM EGO_DATA_LEVEL_B
323 WHERE ATTR_GROUP_TYPE IN
324 (
325 SELECT ENTITY_CODE
326 FROM PO_UDA_AG_TEMPLATES
327 WHERE TEMPLATE_ID = p_template_id
328 )
329 AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
330
331 d_progress := 350;
332
333 l_temp_sequence := l_temp_sequence + 1;
334
335 d_progress := 360;
336
337 fnd_file.put(fnd_file.log, ' l_object_id ' || l_object_id );
338 fnd_file.put(fnd_file.log, ' l_data_level_name ' || l_data_level_name );
339 fnd_file.put(fnd_file.log, ' l_internal_name ' || l_internal_name );
340 fnd_file.put(fnd_file.log, ' l_display_name ' || l_display_name );
341 fnd_file.put(fnd_file.log, ' l_temp_sequence ' || l_temp_sequence );
342
343 EGO_EXT_FWK_PUB.Create_Page
344 (
345 p_api_version => 1.0
346 ,p_object_id => l_object_id
347 ,p_classification_code => p_template_id || ''
348 ,p_data_level => l_data_level_name
349 ,p_internal_name => l_internal_name
350 ,p_display_name => l_display_name
351 ,p_description => l_display_name
352 ,p_sequence => l_temp_sequence
353 ,p_init_msg_list => fnd_api.g_FALSE
354 ,p_commit => fnd_api.g_FALSE
355 ,x_page_id => l_create_page_id
356 ,x_return_status => x_return_status
357 ,x_errorcode => x_errorcode
358 ,x_msg_count => x_msg_count
359 ,x_msg_data => x_msg_data
360 );
361
362 fnd_file.put(fnd_file.log, ' Page created with ID ' || l_create_page_id || ' x_return_status' || x_return_status);
363 d_progress := 370;
364
365
366 OPEN cur_temp_usa_page_entries
367 (
368 p_template_id, l_cur_usage, l_cur_context1, l_cur_context2, l_cur_context3,
369 l_cur_context4, l_cur_context5, l_cur_context6, l_cur_context7, l_cur_context8,
370 l_cur_context9, l_cur_context10, l_cur_context11, l_cur_context12, l_cur_context13,
371 l_cur_context14, l_cur_context15, l_cur_context16, l_cur_context17, l_cur_context18,
372 l_cur_context19, l_cur_context20
373 );
374 LOOP
375
376 FETCH cur_temp_usa_page_entries
377 INTO l_usage_id, l_association_id, l_sequence;
378
379 EXIT WHEN cur_temp_usa_page_entries%NOTFOUND;
380
381
382 fnd_file.put(fnd_file.log, ' l_create_page_id ' || l_create_page_id );
383 fnd_file.put(fnd_file.log, ' l_association_id ' || l_association_id );
384 fnd_file.put(fnd_file.log, ' l_sequence ' || l_sequence );
385 fnd_file.put(fnd_file.log, ' p_template_id ' || p_template_id );
386
387 EGO_EXT_FWK_PUB.Create_Page_Entry
388 (
389 p_api_version => 1.0
390 ,p_page_id => l_create_page_id
391 ,p_association_id => l_association_id
392 ,p_sequence => l_sequence
393 ,p_classification_code => p_template_id || ''
394 ,p_init_msg_list => fnd_api.g_FALSE
395 ,p_commit => fnd_api.g_FALSE
396 ,x_return_status => x_return_status
397 ,x_errorcode => x_errorcode
398 ,x_msg_count => x_msg_count
399 ,x_msg_data => x_msg_data
400 );
401
402 fnd_file.put(fnd_file.log, ' Page entry created with ass ID ' || l_association_id || ' x_return_status' || x_return_status);
403
404
405 d_progress := 380;
406
407 END LOOP;
408 CLOSE cur_temp_usa_page_entries;
409
410 END LOOP;
411 CLOSE cur_template_usages_pages;
412
413 d_progress := 390;
414 x_return_status := 'S';
415 fnd_file.put(fnd_file.log, ' d_progress ' || d_progress || ' x_return_status' || x_return_status);
416
417 EXCEPTION
418 WHEN OTHERS THEN
419 fnd_file.put(fnd_file.log, 'Exception in SYNC_PAGE_ENTRIES after progress number : ' || d_progress);
420 x_return_status := 'U';
421 END SYNC_PAGE_ENTRIES;
422
423 -------------------------------------------------------------------------------
424 --- Start of Comments
425 --- A concurrent program is submitted as soon as the Save or apply is happened.
426 ---
427 --- Concurrent Program Internal Name: COMPILE_TEMPLATES
428 --- Concurrent Program Display Name: Compile Attribute Templates
429 --- Parameters: p_functional_area_code
430 --- p_uda_dummy1
431 --- p_document_type_code
432 --- p_uda_dummy2
433 --- p_document_style_id
434 --- p_level_code
435 --- p_revision
436 --- p_template_id
437 --- Type: PL/sql Concurrent Program
438 ---
439 --- This Concurrent Program firstly checks if the template_id is passed or not
440 --- If it is passed it would compile only the specific template
441 --- Else it would compile all the templates which satisfy the criteria depending on the parameters passed
442 ---
443 --- Compileing a template:
444 --- The Database view is generated using the call
445 --- po_uda_templates_util.generate_template_view
446 --- The appropriate pages are created using the call
447 --- po_uda_templates_util.sync_page_entries
448 --- The compiled flag is changed to ?Y? once the above process is successful
449 ---
450 --- End of Comments
451 -------------------------------------------------------------------------------
452
453 PROCEDURE COMPILE_TEMPLATES
454 (
455 errbuf out NOCOPY varchar2
456 ,retcode out NOCOPY number
457 ,p_functional_area_code in varchar2
458 ,p_uda_dummy1 in varchar2
459 ,p_document_type_code in varchar2
460 ,p_uda_dummy2 in varchar2
461 ,p_document_style_id in number
462 ,p_level_code in varchar2
463 ,p_revision in number
464 ,p_template_id in number
465 )
466 IS
467 d_api_name CONSTANT VARCHAR2(30) := 'COMPILE_TEMPLATES';
468 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
469 d_progress NUMBER;
470
471 conc_status BOOLEAN ;
472 current_error_code VARCHAR2(20) := NULL;
473
474 l_template_view_name VARCHAR2(100);
475 l_return_status VARCHAR2(100);
476 l_msg_count NUMBER;
477 l_errorcode NUMBER;
478 l_msg_data VARCHAR2(100);
479
480 l_template_id NUMBER;
481 l_retcode NUMBER;
482 l_compiled_flag VARCHAR2(100);
483
484
485 -----------------------------------------------------------------------------
486 -- Cursor to fetch all templates with the specicified parameters
487 -- All Non compiled templates satisfying the criteria are fetched
488 -----------------------------------------------------------------------------
489 CURSOR c_templates
490 (
491 p_functional_area_code VARCHAR2
492 ,p_document_type_code VARCHAR2
493 ,p_document_style_id NUMBER
494 ,p_level_code VARCHAR2
495 ,p_revision NUMBER
496 )
497 IS
498 SELECT temp.TEMPLATE_ID
499 FROM PO_UDA_AG_TEMPLATES temp
500 WHERE temp.FUNCTIONAL_AREA = NVL(p_functional_area_code, temp.FUNCTIONAL_AREA)
501 AND temp.DOCUMENT_TYPE = NVL(p_document_type_code, temp.DOCUMENT_TYPE)
502 AND temp.DOCUMENT_STYLE_ID = NVL(p_document_style_id, temp.DOCUMENT_STYLE_ID)
503 AND temp.DOCUMENT_LEVEL = NVL(p_level_code, temp.DOCUMENT_LEVEL)
504 AND temp.REVISION = NVL(p_revision, temp.REVISION)
505 AND COMPILED_FLAG <> 'Y';
506
507 BEGIN
508
509 fnd_file.put(fnd_file.log, 'p_functional_area_code' || p_functional_area_code);
510 fnd_file.put(fnd_file.log, 'p_document_type_code ' || p_document_type_code);
511 fnd_file.put(fnd_file.log, 'p_document_style_id '|| p_document_style_id);
512 fnd_file.put(fnd_file.log, 'p_uda_dummy1 '|| p_uda_dummy1);
513 fnd_file.put(fnd_file.log, 'p_uda_dummy2 '|| p_uda_dummy2);
514 fnd_file.put(fnd_file.log, 'p_level_code '|| p_level_code);
515 fnd_file.put(fnd_file.log, 'p_revision '|| p_revision);
516 fnd_file.put(fnd_file.log, 'p_template_id '|| p_template_id);
517
518 d_progress := 10;
519
520
521
522 IF (PO_LOG.d_proc) THEN
523 PO_LOG.proc_begin(d_module);
524 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
525 END IF;
526
527
528
529 IF p_template_id IS NOT NULL THEN
530
531 -----------------------------------------------------------------------------
532 -- Checking if the template is already compiled
533 -----------------------------------------------------------------------------
534 d_progress := 20;
535
536
537 PO_UDA_TEMPLATES_UTIL.COMPILE_TEMPLATE
538 (
539 p_template_id => p_template_id
540 ,errbuf => errbuf
541 ,retcode => retcode
542 ,x_return_status => l_return_status
543 );
544
545 d_progress := 50;
546
547 IF l_return_status = 'S' THEN
548 retcode := 0;
549 errbuf := 'Compiled the template successfully';
550 d_progress := 60;
551
552 COMMIT;
553
554 ELSE
555 retcode := 1;
556 errbuf := 'Did NOT Compile the template successfully';
557 d_progress := 70;
558
559 ROLLBACK;
560
561 END IF;
562
563 ELSE
564
565 d_progress := 90;
566 OPEN c_templates(p_functional_area_code, p_document_type_code, p_document_style_id, p_level_code, p_revision);
567 LOOP
568 FETCH c_templates INTO l_template_id;
569 EXIT WHEN c_templates%NOTFOUND;
570 d_progress := 100;
571
572 PO_UDA_TEMPLATES_UTIL.COMPILE_TEMPLATE
573 (
574 p_template_id => p_template_id
575 ,errbuf => errbuf
576 ,retcode => retcode
577 ,x_return_status => l_return_status
578 );
579
580 d_progress := 110;
581
582 IF l_return_status = 'S' THEN
583 l_retcode := 0;
584 errbuf := 'Compiled the template successfully';
585 d_progress := 120;
586
587 fnd_file.put(fnd_file.log, 'Before commiting the cahnges for tempalte ' || p_template_id);
588
589 COMMIT;
590
591 ELSE
592 l_retcode := 2 + l_retcode;
593 errbuf := 'Did NOT Compile the template successfully';
594 d_progress := 130;
595
596 ROLLBACK;
597
598 END IF;
599
600
601 d_progress := 140;
602 END LOOP;
603 CLOSE c_templates;
604 d_progress := 150;
605
606
607 IF l_retcode = 0 THEN
608 errbuf := 'Compiled all the templates successfully';
609 d_progress := 160;
610
611 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
612
613 ELSE
614 errbuf := 'One or More templates have failed to compile';
615 d_progress := 170;
616
617 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',Current_Error_Code);
618
619 END IF;
620
621 END IF;
622
623 EXCEPTION
624 WHEN OTHERS THEN
625 fnd_file.put(fnd_file.log, 'Exception in COMPILE_TEMPLATES after progress number : ' || d_progress);
626 errbuf := 'Unexpected error';
627 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
628
629 END COMPILE_TEMPLATES;
630
631 -------------------------------------------------------------------------------
632 --- Start of Comments
633 ---
634 --- Program Name: COMPILE_TEMPLATE
635 --- Parameters: p_template_id
636 ---
637 --- Type: PL/sql
638 ---
639 --- It would compile the specific template
640 ---
641 --- Compileing a template:
642 --- The Database view is generated using the call
643 --- po_uda_templates_util.generate_template_view
644 --- The appropriate pages are created using the call
645 --- po_uda_templates_util.sync_page_entries
646 --- The compiled flag is changed to ?Y? once the above process is successful
647 ---
648 --- End of Comments
649 -------------------------------------------------------------------------------
650
651 PROCEDURE COMPILE_TEMPLATE
652 (
653 p_template_id in number
654 ,errbuf out NOCOPY varchar2
655 ,retcode out NOCOPY number
656 ,x_return_status out NOCOPY varchar2
657 )
658 IS
659 d_api_name CONSTANT VARCHAR2(30) := 'COMPILE_TEMPLATE';
660 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
661 d_progress NUMBER;
662
663 l_template_view_name VARCHAR2(100);
664
665 l_return_status VARCHAR2(100);
666 l_msg_count NUMBER;
667 l_errorcode NUMBER;
668 l_msg_data VARCHAR2(100);
669 l_attr_grp_id NUMBER;
670 l_attr_grp_id_list VARCHAR2(2000) := '@';
671
672 CURSOR c_templates
673 (
674 p_template_id NUMBER
675 )
676 IS
677 SELECT DISTINCT ATTRIBUTE_GROUP_ID
678 FROM PO_UDA_AG_TEMPLATE_USAGES
679 WHERE TEMPLATE_ID = p_template_id;
680
681 l_ERRBUF_ATTRGRP VARCHAR2(200);
682 l_RETCODE_ATTRGRP VARCHAR2(200);
683
684 l_attr_grp_type VARCHAR2(200);
685 l_attr_grp_name VARCHAR2(500);
686
687
688
689
690 BEGIN
691 IF (PO_LOG.d_proc) THEN
692 PO_LOG.proc_begin(d_module);
693 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
694 END IF;
695
696 d_progress := 180;
697
698 SAVEPOINT BEFORE_GENERATE_TEMPALTE_VIEW;
699
700 PO_UDA_TEMPLATES_UTIL.generate_template_view
701 (
702 p_api_version => 1.0
703 ,p_template_id => p_template_id
704 ,x_template_view_name => l_template_view_name
705 ,x_return_status => l_return_status
706 ,x_msg_count => l_msg_count
707 ,x_msg_data => l_msg_data
708 );
709
710 fnd_file.put(fnd_file.log, ' After generate template view with template name' || l_template_view_name || ' Status ' || l_return_status);
711
712 d_progress := 190;
713
714 IF l_template_view_name IS NOT NULL THEN
715 retcode := 0;
716 errbuf := 'View for template id: ' || p_template_id || ' generated as: ' || l_template_view_name;
717 d_progress := 200;
718 ELSE
719 retcode := 3;
720 errbuf := 'View for template id: ' || p_template_id || ' is not created';
721 d_progress := 210;
722 l_return_status := 'U';
723
724 ROLLBACK to BEFORE_GENERATE_TEMPALTE_VIEW;
725
726 END IF;
727
728 d_progress := 220;
729 PO_UDA_TEMPLATES_UTIL.SYNC_PAGE_ENTRIES
730 (
731 p_template_id => p_template_id
732 ,x_return_status => l_return_status
733 ,x_errorcode => l_errorcode
734 ,x_msg_count => l_msg_count
735 ,x_msg_data => l_msg_data
736 );
737 d_progress := 230;
738
739 IF l_return_status = 'S' THEN
740 retcode := 0;
741 errbuf := 'Page entries created successfully';
742 d_progress := 240;
743 ELSE
744 d_progress := 250;
745 retcode := 4;
746 errbuf := 'Page entries created failed';
747 l_return_status := 'U';
748 x_return_status := 'U';
749 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
750 END IF;
751
752 d_progress := 260;
753
754 fnd_file.put(fnd_file.log, 'Before updating the flag');
755
756
757 UPDATE PO_UDA_AG_TEMPLATES
758 SET COMPILED_FLAG = 'Y'
759 WHERE TEMPLATE_ID = p_template_id;
760
761 x_return_status := 'S';
762
763
764 EXCEPTION
765 WHEN OTHERS THEN
766 fnd_file.put(fnd_file.log, 'Exception in COMPILE_TEMPLATE after progress number : ' || d_progress);
767 x_return_status := 'U';
768 END COMPILE_TEMPLATE;
769
770 PROCEDURE is_read_only_dummy_attr_group
771 (
772 p_attribute_group_type IN VARCHAR2
773 ,p_attribute_group_name IN VARCHAR2
774 ,p_source IN VARCHAR2 DEFAULT 'UI'
775 ,x_return_status OUT NOCOPY VARCHAR2
776 )IS
777
778 l_po_header_group_id NUMBER;
779 l_po_line_group_id NUMBER;
780 l_po_distributions_group_id NUMBER;
781 l_shipments_group_id NUMBER;
782 l_req_header_group_id NUMBER;
783 l_req_line_group_id NUMBER;
784 l_req_distribution_group_id NUMBER;
785 l_pon_auc_item_price_group_id NUMBER;
786 l_pon_auc_header_group_id NUMBER;
787 l_bid_header_group_id NUMBER;
788 l_bid_item_proce_group_id NUMBER;
789
790 l_attr_grp_id NUMBER;
791
792
793 BEGIN
794
795 x_return_status := 'F';
796
797 SELECT ATTR_GROUP_ID
798 INTO l_attr_grp_id
799 FROM ego_fnd_dsc_flx_ctx_ext
800 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attribute_group_type
801 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attribute_group_name
802 AND ROWNUM < 2;
803
804 SELECT PO_HEADER_GROUP_ID,
805 PO_LINE_GROUP_ID,
806 PO_DISTRIBUTIONS_GROUP_ID,
807 PO_SHIPMENTS_GROUP_ID,
808 REQ_HEADER_GROUP_ID,
809 REQ_LINE_GROUP_ID,
810 REQ_DISTRIBUTION_GROUP_ID,
811 PON_AUC_ITEM_PRICE_GROUP_ID,
812 PON_AUC_HEADER_GROUP_ID,
813 BID_HEADER_GROUP_ID,
814 BID_ITEM_PRICE_GROUP_ID
815 INTO l_po_header_group_id,
816 l_po_line_group_id,
817 l_po_distributions_group_id,
818 l_shipments_group_id,
819 l_req_header_group_id,
820 l_req_line_group_id,
821 l_req_distribution_group_id,
822 l_pon_auc_item_price_group_id,
823 l_pon_auc_header_group_id,
824 l_bid_header_group_id,
825 l_bid_item_proce_group_id
826 FROM PO_UDA_MAPPINGS
827 WHERE DUMMY_ATTRIBUTE_GROUP_ID = l_attr_grp_id
828 AND ROWNUM < 2;
829
830 IF l_po_header_group_id IS NOT NULL THEN
831 PO_UDA_TEMPLATES_UTIL.is_read_only_attribute_group
832 (
833 p_attribute_group_type => 'PO_HEADER_EXT_ATTRS',
834 p_attribute_group_id => l_po_header_group_id,
835 p_source => p_source,
836 x_return_status => x_return_status
837 );
838 END IF;
839
840 IF l_po_line_group_id IS NOT NULL AND x_return_status NOT IN ('S', 'U') THEN
841 PO_UDA_TEMPLATES_UTIL.is_read_only_attribute_group
842 (
843 p_attribute_group_type => 'PO_LINE_EXT_ATTRS',
844 p_attribute_group_id => l_po_line_group_id,
845 p_source => p_source,
846 x_return_status => x_return_status
847 );
848 END IF;
849
850 IF l_shipments_group_id IS NOT NULL AND x_return_status NOT IN ('S', 'U') THEN
851 PO_UDA_TEMPLATES_UTIL.is_read_only_attribute_group
852 (
853 p_attribute_group_type => 'PO_SHIPMENTS_EXT_ATTRS',
854 p_attribute_group_id => l_shipments_group_id,
855 p_source => p_source,
856 x_return_status => x_return_status
857 );
858 END IF;
859
860 IF l_po_distributions_group_id IS NOT NULL AND x_return_status NOT IN ('S', 'U') THEN
861 PO_UDA_TEMPLATES_UTIL.is_read_only_attribute_group
862 (
863 p_attribute_group_type => 'PO_DISTRIBUTIONS_EXT_ATTRS',
864 p_attribute_group_id => l_po_distributions_group_id,
865 p_source => p_source,
866 x_return_status => x_return_status
867 );
868 END IF;
869
870 IF l_req_header_group_id IS NOT NULL AND x_return_status NOT IN ('S', 'U') THEN
871 PO_UDA_TEMPLATES_UTIL.is_read_only_attribute_group
872 (
873 p_attribute_group_type => 'PO_REQ_HEADER_EXT_ATTRS',
874 p_attribute_group_id => l_req_header_group_id,
875 p_source => p_source,
876 x_return_status => x_return_status
877 );
878 END IF;
879
880 IF l_req_line_group_id IS NOT NULL AND x_return_status NOT IN ('S', 'U') THEN
881 PO_UDA_TEMPLATES_UTIL.is_read_only_attribute_group
882 (
883 p_attribute_group_type => 'PO_REQ_LINE_EXT_ATTRS',
884 p_attribute_group_id => l_req_line_group_id,
885 p_source => p_source,
886 x_return_status => x_return_status
887 );
888 END IF;
889
890 IF l_req_distribution_group_id IS NOT NULL AND x_return_status NOT IN ('S', 'U') THEN
891 PO_UDA_TEMPLATES_UTIL.is_read_only_attribute_group
892 (
893 p_attribute_group_type => 'PO_REQ_DIST_EXT_ATTRS',
894 p_attribute_group_id => l_req_distribution_group_id,
895 p_source => p_source,
896 x_return_status => x_return_status
897 );
898 END IF;
899
900 IF l_bid_item_proce_group_id IS NOT NULL AND x_return_status NOT IN ('S', 'U') THEN
901 PO_UDA_TEMPLATES_UTIL.is_read_only_attribute_group
902 (
903 p_attribute_group_type => 'PON_BID_PRICES_EXT_ATTRS',
904 p_attribute_group_id => l_bid_item_proce_group_id,
905 p_source => p_source,
906 x_return_status => x_return_status
907 );
908 END IF;
909
910 IF l_bid_header_group_id IS NOT NULL AND x_return_status NOT IN ('S', 'U') THEN
911 PO_UDA_TEMPLATES_UTIL.is_read_only_attribute_group
912 (
913 p_attribute_group_type => 'PON_BID_HDRS_EXT_ATTRS',
914 p_attribute_group_id => l_bid_header_group_id,
915 p_source => p_source,
916 x_return_status => x_return_status
917 );
918 END IF;
919
920 IF l_pon_auc_header_group_id IS NOT NULL AND x_return_status NOT IN ('S', 'U') THEN
921 PO_UDA_TEMPLATES_UTIL.is_read_only_attribute_group
922 (
923 p_attribute_group_type => 'PON_AUC_HDRS_EXT_ATTRS',
924 p_attribute_group_id => l_pon_auc_header_group_id,
925 p_source => p_source,
926 x_return_status => x_return_status
927 );
928 END IF;
929
930 IF l_pon_auc_item_price_group_id IS NOT NULL AND x_return_status NOT IN ('S', 'U') THEN
931 PO_UDA_TEMPLATES_UTIL.is_read_only_attribute_group
932 (
933 p_attribute_group_type => 'PON_AUC_PRICES_EXT_ATTRS',
934 p_attribute_group_id => l_pon_auc_item_price_group_id,
935 p_source => p_source,
936 x_return_status => x_return_status
937 );
938 END IF;
939 EXCEPTION
940 WHEN OTHERS THEN
941 x_return_status := 'F';
942 END is_read_only_dummy_attr_group;
943
944 PROCEDURE is_read_only_attribute_group
945 (
946 p_attribute_group_type IN VARCHAR2
947 ,p_attribute_group_id IN NUMBER
948 ,p_source IN VARCHAR2 DEFAULT 'UI'
949 ,x_return_status OUT NOCOPY VARCHAR2
950 )IS
951
952 l_created_by NUMBER;
953 l_count NUMBER;
954
955 BEGIN
956
957 SELECT CREATED_BY
958 INTO l_created_by
959 FROM ego_fnd_dsc_flx_ctx_ext
960 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attribute_group_type
961 AND ATTR_GROUP_ID = p_attribute_group_id
962 AND ROWNUM < 2;
963
964
965 IF (l_created_by in (-1, 0, 1, 2) or ((l_created_by >=120) and (l_created_by <= 129))) AND p_source = 'UI' THEN
966
967 --x_return_status := 'S';
968 x_return_status := 'F'; --Commented above line temporarily and changed
969
970 ELSE
971
972 SELECT count(*)
973 INTO l_count
974 FROM PO_UDA_AG_TEMPLATE_USAGES
975 WHERE ATTRIBUTE_GROUP_ID = p_attribute_group_id;
976
977 IF l_count = 0 THEN
978 x_return_status := 'F';
979 ELSE
980 --x_return_status := 'S';
981 x_return_status := 'F'; --Commented above line temporarily and changed
982 END IF;
983
984 END IF;
985
986 EXCEPTION
987 WHEN OTHERS THEN
988 x_return_status := 'U';
989 END is_read_only_attribute_group;
990
991
992 PROCEDURE is_action_seeded
993 (
994 p_glb_api_version IN NUMBER
995 ,p_glb_application_id IN NUMBER
996 ,p_action_id IN VARCHAR2
997 ,x_is_seeded OUT NOCOPY VARCHAR2
998 ,x_return_status OUT NOCOPY VARCHAR2
999 ,x_errorcode OUT NOCOPY NUMBER
1000 ,x_msg_count OUT NOCOPY NUMBER
1001 ,x_msg_data OUT NOCOPY VARCHAR2
1002 )IS
1003
1004 l_created_by NUMBER;
1005 l_count NUMBER;
1006 l_template_id NUMBER;
1007 l_entity_code VARCHAR2(200);
1008
1009 BEGIN
1010 x_is_seeded := can_update_delete(l_template_id);
1011 x_return_status := 'S';
1012
1013 EXCEPTION
1014 WHEN OTHERS THEN
1015 x_is_seeded := 'false';
1016 x_return_status := 'U';
1017 END is_action_seeded;
1018
1019
1020 PROCEDURE is_function_seeded
1021 (
1022 p_glb_api_version IN NUMBER
1023 ,p_glb_application_id IN NUMBER
1024 ,p_function_id IN VARCHAR2
1025 ,x_is_seeded OUT NOCOPY VARCHAR2
1026 ,x_return_status OUT NOCOPY VARCHAR2
1027 ,x_errorcode OUT NOCOPY NUMBER
1028 ,x_msg_count OUT NOCOPY NUMBER
1029 ,x_msg_data OUT NOCOPY VARCHAR2
1030 )IS
1031
1032 l_created_by NUMBER;
1033 l_count NUMBER;
1034 l_template_id NUMBER;
1035 l_entity_code VARCHAR2(200);
1036
1037 BEGIN
1038
1039 SELECT CREATED_BY, CLASSIFICATION_CODE
1040 INTO l_created_by, l_template_id
1041 FROM EGO_ACTIONS_B
1042 WHERE FUNCTION_ID = p_function_id
1043 AND ROWNUM < 2;
1044
1045
1046 IF (l_created_by in (-1, 0, 1, 2) or ((l_created_by >=120) and (l_created_by <= 129))) THEN
1047
1048 x_return_status := 'S';
1049 x_is_seeded := 'true';
1050
1051 ELSE
1052
1053 SELECT ENTITY_CODE
1054 INTO l_entity_code
1055 FROM PO_UDA_AG_TEMPLATES
1056 WHERE TEMPLATE_ID = l_template_id;
1057
1058 IF l_entity_code = 'PO_HEADER_EXT_ATTRS' THEN
1059 SELECT count(*)
1060 INTO l_count
1061 FROM PO_HEADERS_ALL
1062 WHERE UDA_TEMPLATE_ID = l_template_id;
1063 END IF;
1064 IF l_entity_code = 'PO_LINE_EXT_ATTRS' THEN
1065 SELECT count(*)
1066 INTO l_count
1067 FROM PO_LINES_ALL
1068 WHERE UDA_TEMPLATE_ID = l_template_id;
1069 END IF;
1070 IF l_entity_code = 'PO_SHIPMENTS_EXT_ATTRS' THEN
1071 SELECT count(*)
1072 INTO l_count
1073 FROM PO_LINE_LOCATIONS_ALL
1074 WHERE UDA_TEMPLATE_ID = l_template_id;
1075 END IF;
1076 IF l_entity_code = 'PO_DISTRIBUTIONS_EXT_ATTRS' THEN
1077 SELECT count(*)
1078 INTO l_count
1079 FROM PO_DISTRIBUTIONS_ALL
1080 WHERE UDA_TEMPLATE_ID = l_template_id;
1081 END IF;
1082 IF l_entity_code = 'PO_REQ_HEADER_EXT_ATTRS' THEN
1083 SELECT count(*)
1084 INTO l_count
1085 FROM PO_REQUISITION_HEADERS_ALL
1086 WHERE UDA_TEMPLATE_ID = l_template_id;
1087 END IF;
1088 IF l_entity_code = 'PO_REQ_LINE_EXT_ATTRS' THEN
1089 SELECT count(*)
1090 INTO l_count
1091 FROM PO_REQUISITION_LINES_ALL
1092 WHERE UDA_TEMPLATE_ID = l_template_id;
1093 END IF;
1094 IF l_entity_code = 'PO_REQ_DIST_EXT_ATTRS' THEN
1095 SELECT count(*)
1096 INTO l_count
1097 FROM PO_REQ_DISTRIBUTIONS_ALL
1098 WHERE UDA_TEMPLATE_ID = l_template_id;
1099 END IF;
1100 IF l_entity_code = 'PON_AUC_HDRS_EXT_ATTRS' THEN
1101 SELECT count(*)
1102 INTO l_count
1103 FROM PON_AUCTION_HEADERS_ALL
1104 WHERE UDA_TEMPLATE_ID = l_template_id;
1105 END IF;
1106 IF l_entity_code = 'PON_AUC_PRICES_EXT_ATTRS' THEN
1107 SELECT count(*)
1108 INTO l_count
1109 FROM PON_AUCTION_ITEM_PRICES_ALL
1110 WHERE UDA_TEMPLATE_ID = l_template_id;
1111 END IF;
1112 IF l_entity_code = 'PON_BID_HDRS_EXT_ATTRS' THEN
1113 SELECT count(*)
1114 INTO l_count
1115 FROM PON_BID_HEADERS
1116 WHERE UDA_TEMPLATE_ID = l_template_id;
1117 END IF;
1118 IF l_entity_code = 'PON_BID_PRICES_EXT_ATTRS' THEN
1119 SELECT count(*)
1120 INTO l_count
1121 FROM PON_BID_ITEM_PRICES
1122 WHERE UDA_TEMPLATE_ID = l_template_id;
1123 END IF;
1124
1125 IF l_count = 0 THEN
1126 x_return_status := 'F';
1127 x_is_seeded := 'false';
1128 ELSE
1129 x_return_status := 'S';
1130 x_is_seeded := 'true';
1131 END IF;
1132
1133 END IF;
1134
1135 EXCEPTION
1136 WHEN OTHERS THEN
1137 x_is_seeded := 'false';
1138 x_return_status := 'U';
1139 END is_function_seeded;
1140
1141
1142 FUNCTION can_update_delete(p_template_id NUMBER)
1143 RETURN VARCHAR2 IS
1144
1145 d_api_name CONSTANT VARCHAR2(30) := 'can_delete';
1146 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
1147 d_progress NUMBER;
1148
1149 l_prod_table VARCHAR2(300);
1150 l_template_column VARCHAR2(300);
1151 l_temp_exists VARCHAR2(1) := 'N';
1152 l_stmt VARCHAR2(1000);
1153
1154 TYPE ref_cursor IS REF CURSOR;
1155 c_document_exists ref_cursor;
1156
1157 CURSOR c_prod_table(p_template_id NUMBER) IS
1158 SELECT fo.DATABASE_OBJECT_NAME
1159 FROM EGO_OBJECT_EXT_TABLES_B eb, fnd_objects fo, EGO_FND_DESC_FLEXS_EXT et, po_uda_ag_templates pt
1160 WHERE eb.OBJECT_ID = fo.object_id
1161 AND eb.EXT_TABLE_NAME = et.APPLICATION_TL_TABLE_NAME
1162 AND et.DESCRIPTIVE_FLEXFIELD_NAME = pt.entity_code
1163 AND pt.template_id = p_template_id;
1164
1165 BEGIN
1166
1167 IF (PO_LOG.d_proc) THEN
1168 PO_LOG.proc_begin(d_module);
1169 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
1170 END IF;
1171
1172 OPEN c_prod_table(p_template_id);
1173 FETCH c_prod_table INTO l_prod_table;
1174 CLOSE c_prod_table;
1175
1176 l_template_column := 'UDA_TEMPLATE_ID';
1177
1178 d_progress := 10;
1179
1180 IF PO_LOG.d_stmt THEN
1181 PO_LOG.stmt(d_module, d_progress, 'l_prod_table', l_prod_table);
1182 PO_LOG.stmt(d_module, d_progress, 'l_template_column', l_template_column);
1183 END IF;
1184
1185 l_stmt := 'SELECT ''Y'' from '|| l_prod_table ||
1186 ' WHERE '|| l_template_column||' = '|| p_template_id || ' AND ROWNUM < 2';
1187
1188 OPEN c_document_exists FOR l_stmt;
1189 FETCH c_document_exists into l_temp_exists;
1190 CLOSE c_document_exists;
1191
1192 d_progress := 20;
1193
1194 IF PO_LOG.d_stmt THEN
1195 PO_LOG.stmt(d_module, d_progress, 'l_stmt', l_stmt);
1196 PO_LOG.stmt(d_module, d_progress, 'l_temp_exists', l_temp_exists);
1197 END IF;
1198
1199 IF l_temp_exists ='Y' THEN
1200 RETURN 'false';
1201 ELSE
1202 RETURN 'true';
1203 END IF;
1204
1205 EXCEPTION
1206 WHEN OTHERS THEN
1207 d_progress := 30;
1208 IF PO_LOG.d_stmt THEN
1209 PO_LOG.stmt(d_module, d_progress, 'entered Exception block ');
1210 END IF;
1211 END can_update_delete;
1212
1213
1214 PROCEDURE sync_attribute_group
1215 (
1216 p_glb_api_version IN NUMBER
1217 ,p_glb_application_id IN NUMBER
1218 ,p_attribute_group_type IN VARCHAR2
1219 ,p_attribute_group_name IN VARCHAR2
1220 ,x_return_status OUT NOCOPY VARCHAR2
1221 ,x_errorcode OUT NOCOPY NUMBER
1222 ,x_msg_count OUT NOCOPY NUMBER
1223 ,x_msg_data OUT NOCOPY VARCHAR2
1224 )
1225 IS
1226 l_attr_grp_id NUMBER;
1227
1228 d_api_name CONSTANT VARCHAR2(30) := 'sync_attribute_group with name';
1229 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
1230 d_progress NUMBER;
1231
1232 CURSOR c_to_delete_AG
1233 (
1234 p_internal_name IN VARCHAR2
1235 )
1236 IS
1237 SELECT ATTR_GROUP_ID
1238 FROM ego_fnd_dsc_flx_ctx_ext
1239 WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_internal_name;
1240
1241
1242
1243 BEGIN
1244
1245 d_progress := 10;
1246
1247 IF (PO_LOG.d_proc) THEN
1248 PO_LOG.proc_begin(d_module);
1249 PO_LOG.proc_begin(d_module, 'p_attribute_group_type', p_attribute_group_type);
1250 PO_LOG.proc_begin(d_module, 'p_attribute_group_name', p_attribute_group_name);
1251 END IF;
1252
1253 SELECT ATTR_GROUP_ID
1254 INTO l_attr_grp_id
1255 FROM ego_fnd_dsc_flx_ctx_ext
1256 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attribute_group_type
1257 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attribute_group_name
1258 AND ROWNUM < 2;
1259
1260 d_progress := 20;
1261
1262 IF PO_LOG.d_stmt THEN
1263 PO_LOG.stmt(d_module, d_progress, 'l_attr_grp_id', l_attr_grp_id);
1264 END IF;
1265
1266 -- Fetched the attribute group ID and calling the overloaded sync api
1267 PO_UDA_TEMPLATES_UTIL.sync_attribute_group(
1268 p_glb_api_version,
1269 p_glb_application_id,
1270 l_attr_grp_id,
1271 x_return_status,
1272 x_errorcode,
1273 x_msg_count,
1274 x_msg_data
1275 );
1276
1277 d_progress := 30;
1278
1279 IF PO_LOG.d_stmt THEN
1280 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
1281 END IF;
1282
1283 IF (PO_LOG.d_exc) THEN
1284 PO_LOG.exc(d_module, d_progress, x_return_status );
1285 PO_LOG.proc_end(d_module);
1286 END IF;
1287
1288 EXCEPTION
1289 when no_data_found then
1290 OPEN c_to_delete_AG
1291 (
1292 p_attribute_group_name
1293 );
1294 LOOP
1295 FETCH c_to_delete_AG INTO l_attr_grp_id;
1296 EXIT WHEN c_to_delete_AG%NOTFOUND;
1297
1298 PO_UDA_TEMPLATES_UTIL.delete_real_attr_group(
1299 p_glb_api_version,
1300 l_attr_grp_id
1301 );
1302
1303 END LOOP;
1304 CLOSE c_to_delete_AG;
1305
1306 WHEN OTHERS THEN
1307 d_progress := 40;
1308
1309 IF (PO_LOG.d_exc) THEN
1310 PO_LOG.exc(d_module, d_progress, x_return_status );
1311 PO_LOG.proc_end(d_module);
1312 END IF;
1313 END sync_attribute_group;
1314
1315 PROCEDURE sync_attribute_group
1316 (
1317 p_glb_api_version IN NUMBER
1318 ,p_glb_application_id IN NUMBER
1319 ,p_attribute_group_id IN NUMBER
1320 ,x_return_status OUT NOCOPY VARCHAR2
1321 ,x_errorcode OUT NOCOPY NUMBER
1322 ,x_msg_count OUT NOCOPY NUMBER
1323 ,x_msg_data OUT NOCOPY VARCHAR2
1324 )
1325 IS
1326 l_total_delete NUMBER;
1327 l_true_false VARCHAR2(20);
1328 l_return_status VARCHAR2(100);
1329 l_errorcode NUMBER;
1330 l_msg_count NUMBER;
1331 l_msg_data VARCHAR2(100);
1332
1333 d_api_name CONSTANT VARCHAR2(30) := 'sync_attribute_group with id';
1334 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
1335 d_progress NUMBER;
1336
1337 BEGIN
1338
1339 d_progress := 10;
1340
1341 IF (PO_LOG.d_proc) THEN
1342 PO_LOG.proc_begin(d_module);
1343 PO_LOG.proc_begin(d_module, 'p_attribute_group_id', p_attribute_group_id);
1344 END IF;
1345
1346 -- Query to check if it is a total dummy delete case
1347 SELECT 1
1348 INTO l_total_delete
1349 FROM ego_fnd_dsc_flx_ctx_ext
1350 WHERE ATTR_GROUP_ID = p_attribute_group_id
1351 AND ROWNUM < 2;
1352
1353 IF PO_LOG.d_stmt THEN
1354 PO_LOG.stmt(d_module, d_progress, 'l_total_delete', l_total_delete);
1355 END IF;
1356
1357 d_progress := 20;
1358
1359 -- Non Delete case (Syncing all the reals exisintg)
1360 IF l_total_delete = 1 THEN
1361
1362 d_progress := 30;
1363 l_true_false := sync_real_attribute_groups(p_glb_api_version, p_glb_application_id, p_attribute_group_id, 'PO_HEADER_EXT_ATTRS');
1364 l_true_false := sync_real_attribute_groups(p_glb_api_version, p_glb_application_id, p_attribute_group_id, 'PO_LINE_EXT_ATTRS');
1365 l_true_false := sync_real_attribute_groups(p_glb_api_version, p_glb_application_id, p_attribute_group_id, 'PO_DISTRIBUTIONS_EXT_ATTRS');
1366 l_true_false := sync_real_attribute_groups(p_glb_api_version, p_glb_application_id, p_attribute_group_id, 'PO_SHIPMENTS_EXT_ATTRS');
1367 l_true_false := sync_real_attribute_groups(p_glb_api_version, p_glb_application_id, p_attribute_group_id, 'PO_REQ_HEADER_EXT_ATTRS');
1368 l_true_false := sync_real_attribute_groups(p_glb_api_version, p_glb_application_id, p_attribute_group_id, 'PO_REQ_LINE_EXT_ATTRS');
1369 l_true_false := sync_real_attribute_groups(p_glb_api_version, p_glb_application_id, p_attribute_group_id, 'PO_REQ_DIST_EXT_ATTRS');
1370 l_true_false := sync_real_attribute_groups(p_glb_api_version, p_glb_application_id, p_attribute_group_id, 'PON_AUC_PRICES_EXT_ATTRS');
1371 l_true_false := sync_real_attribute_groups(p_glb_api_version, p_glb_application_id, p_attribute_group_id, 'PON_AUC_HDRS_EXT_ATTRS');
1372 l_true_false := sync_real_attribute_groups(p_glb_api_version, p_glb_application_id, p_attribute_group_id, 'PON_BID_HDRS_EXT_ATTRS');
1373 l_true_false := sync_real_attribute_groups(p_glb_api_version, p_glb_application_id, p_attribute_group_id, 'PON_BID_PRICES_EXT_ATTRS');
1374
1375 d_progress := 40;
1376
1377 IF PO_LOG.d_stmt THEN
1378 PO_LOG.stmt(d_module, d_progress, 'l_true_false', l_true_false);
1379 END IF;
1380
1381 END IF;
1382
1383 d_progress := 50;
1384
1385 IF (PO_LOG.d_exc) THEN
1386 PO_LOG.exc(d_module, d_progress, l_true_false );
1387 PO_LOG.proc_end(d_module);
1388 END IF;
1389
1390 EXCEPTION
1391 WHEN OTHERS THEN -- Total delete case for dummy
1392 d_progress := 60;
1393 IF get_map_table_details(p_attribute_group_id, 'PO_HEADER_EXT_ATTRS') IS NOT NULL THEN
1394 d_progress := 71;
1395 delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PO_HEADER_EXT_ATTRS'));
1396 END IF;
1397 IF get_map_table_details(p_attribute_group_id, 'PO_LINE_EXT_ATTRS') IS NOT NULL THEN
1398 d_progress := 72;
1399 delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PO_LINE_EXT_ATTRS'));
1400 END IF;
1401 IF get_map_table_details(p_attribute_group_id, 'PO_DISTRIBUTIONS_EXT_ATTRS') IS NOT NULL THEN
1402 d_progress := 73;
1403 delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PO_DISTRIBUTIONS_EXT_ATTRS'));
1404 END IF;
1405 IF get_map_table_details(p_attribute_group_id, 'PO_SHIPMENTS_EXT_ATTRS') IS NOT NULL THEN
1406 d_progress := 74;
1407 delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PO_SHIPMENTS_EXT_ATTRS'));
1408 END IF;
1409 IF get_map_table_details(p_attribute_group_id, 'PO_REQ_HEADER_EXT_ATTRS') IS NOT NULL THEN
1410 d_progress := 75;
1411 delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PO_REQ_HEADER_EXT_ATTRS'));
1412 END IF;
1413 IF get_map_table_details(p_attribute_group_id, 'PO_REQ_LINE_EXT_ATTRS') IS NOT NULL THEN
1414 d_progress := 76;
1415 delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PO_REQ_LINE_EXT_ATTRS'));
1416 END IF;
1417 IF get_map_table_details(p_attribute_group_id, 'PO_REQ_DIST_EXT_ATTRS') IS NOT NULL THEN
1418 d_progress := 77;
1419 delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PO_REQ_DIST_EXT_ATTRS'));
1420 END IF;
1421 IF get_map_table_details(p_attribute_group_id, 'PON_AUC_PRICES_EXT_ATTRS') IS NOT NULL THEN
1422 d_progress := 78;
1423 delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PON_AUC_PRICES_EXT_ATTRS'));
1424 END IF;
1425 IF get_map_table_details(p_attribute_group_id, 'PON_AUC_HDRS_EXT_ATTRS') IS NOT NULL THEN
1426 d_progress := 79;
1427 delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PON_AUC_HDRS_EXT_ATTRS'));
1428 END IF;
1429 IF get_map_table_details(p_attribute_group_id, 'PON_BID_HDRS_EXT_ATTRS') IS NOT NULL THEN
1430 d_progress := 80;
1431 delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PON_BID_HEDRS_EXT_ATTRS'));
1432 END IF;
1433 IF get_map_table_details(p_attribute_group_id, 'PON_BID_PRICES_EXT_ATTRS') IS NOT NULL THEN
1434 d_progress := 81;
1435 delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PON_BID_PRICES_EXT_ATTRS'));
1436 END IF;
1437
1438 d_progress := 90;
1439
1440 DELETE FROM PO_UDA_MAPPINGS
1441 WHERE dummy_attribute_group_id = p_attribute_group_id;
1442
1443 d_progress := 100;
1444
1445 IF (PO_LOG.d_exc) THEN
1446 PO_LOG.exc(d_module, d_progress, p_attribute_group_id );
1447 PO_LOG.proc_end(d_module);
1448 END IF;
1449
1450 END sync_attribute_group;
1451
1452 PROCEDURE sync_real_data_levels
1453 (
1454 p_glb_api_version IN NUMBER
1455 ,p_glb_application_id IN NUMBER
1456 ,p_dummy_attr_group_id IN NUMBER
1457 ,p_real_attr_group_id IN NUMBER
1458 ,p_attr_group_type IN VARCHAR2
1459 ,p_data_level_name IN VARCHAR2
1460 ,p_user_data_level_name IN VARCHAR2
1461 ,x_data_level_id OUT NOCOPY NUMBER
1462 ,x_return_status OUT NOCOPY VARCHAR2
1463 ,x_msg_count OUT NOCOPY NUMBER
1464 ,x_msg_data OUT NOCOPY VARCHAR2
1465 )
1466 IS
1467 l_data_level_id NUMBER;
1468 l_errorcode NUMBER;
1469
1470 d_api_name CONSTANT VARCHAR2(30) := 'sync_real_data_levels';
1471 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
1472 d_progress NUMBER;
1473
1474 BEGIN
1475
1476 d_progress := 10;
1477
1478 IF (PO_LOG.d_proc) THEN
1479 PO_LOG.proc_begin(d_module);
1480 PO_LOG.proc_begin(d_module, 'p_dummy_attr_group_id', p_dummy_attr_group_id);
1481 PO_LOG.proc_begin(d_module, 'p_real_attr_group_id', p_real_attr_group_id);
1482 PO_LOG.proc_begin(d_module, 'p_attr_group_type', p_attr_group_type);
1483 PO_LOG.proc_begin(d_module, 'p_data_level_name', p_data_level_name);
1484 PO_LOG.proc_begin(d_module, 'p_user_data_level_name', p_user_data_level_name);
1485 END IF;
1486
1487 SELECT DATA_LEVEL_ID
1488 INTO l_data_level_id
1489 FROM EGO_DATA_LEVEL_B
1490 WHERE ATTR_GROUP_TYPE = p_attr_group_type
1491 AND DATA_LEVEL_NAME = p_data_level_name;
1492
1493 d_progress := 20;
1494
1495 IF PO_LOG.d_stmt THEN
1496 PO_LOG.stmt(d_module, d_progress, 'l_data_level_id', l_data_level_id);
1497 END IF;
1498
1499 -- Assigning base data level to the real attribute group
1500 sync_data_level_attr_group(
1501 p_real_attr_group_id
1502 ,l_data_level_id
1503 ,x_return_status
1504 ,l_errorcode
1505 ,x_msg_count
1506 ,x_msg_data
1507 );
1508
1509 d_progress := 30;
1510
1511 IF PO_LOG.d_stmt THEN
1512 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
1513 END IF;
1514
1515
1516 SELECT DATA_LEVEL_ID
1517 INTO l_data_level_id
1518 FROM EGO_DATA_LEVEL_B
1519 WHERE ATTR_GROUP_TYPE = p_attr_group_type
1520 AND DATA_LEVEL_NAME = p_data_level_name || '_ARCHIVE';
1521
1522 d_progress := 40;
1523
1524 IF PO_LOG.d_stmt THEN
1525 PO_LOG.stmt(d_module, d_progress, 'l_data_level_id', l_data_level_id);
1526 END IF;
1527
1528 -- Assigning archive data level to the real attribute group
1529 sync_data_level_attr_group(
1530 p_real_attr_group_id
1531 ,l_data_level_id
1532 ,x_return_status
1533 ,l_errorcode
1534 ,x_msg_count
1535 ,x_msg_data
1536 );
1537
1538 d_progress := 50;
1539
1540 IF PO_LOG.d_stmt THEN
1541 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
1542 END IF;
1543
1544 -- Following if statement populates PO_UDA_MAPPINGS to refresh the latest dummy data
1545
1546 IF (get_map_table_details(p_dummy_attr_group_id, 'PO_HEADER_EXT_ATTRS') IS NULL
1547 AND get_map_table_details(p_dummy_attr_group_id, 'PO_LINE_EXT_ATTRS') IS NULL
1548 AND get_map_table_details(p_dummy_attr_group_id, 'PO_DISTRIBUTIONS_EXT_ATTRS') IS NULL
1549 AND get_map_table_details(p_dummy_attr_group_id, 'PO_SHIPMENTS_EXT_ATTRS') IS NULL
1550 AND get_map_table_details(p_dummy_attr_group_id, 'PO_REQ_HEADER_EXT_ATTRS') IS NULL
1551 AND get_map_table_details(p_dummy_attr_group_id, 'PO_REQ_LINE_EXT_ATTRS') IS NULL
1552 AND get_map_table_details(p_dummy_attr_group_id, 'PO_REQ_DIST_EXT_ATTRS') IS NULL
1553 AND get_map_table_details(p_dummy_attr_group_id, 'PON_AUC_PRICES_EXT_ATTRS') IS NULL
1554 AND get_map_table_details(p_dummy_attr_group_id, 'PON_AUC_HDRS_EXT_ATTRS') IS NULL
1555 AND get_map_table_details(p_dummy_attr_group_id, 'PON_BID_HDRS_EXT_ATTRS') IS NULL
1556 AND get_map_table_details(p_dummy_attr_group_id, 'PON_BID_PRICES_EXT_ATTRS') IS NULL)
1557 THEN
1558 IF p_attr_group_type = 'PO_HEADER_EXT_ATTRS' THEN
1559 INSERT INTO PO_UDA_MAPPINGS
1560 (
1561 dummy_attribute_group_id
1562 ,PO_HEADER_GROUP_ID
1563 ,created_by
1564 ,creation_date
1565 ,last_updated_by
1566 ,last_update_date
1567 ,last_update_login
1568 )
1569 VALUES
1570 (
1571 p_dummy_attr_group_id
1572 ,p_real_attr_group_id
1573 ,FND_GLOBAL.User_Id
1574 ,SYSDATE
1575 ,FND_GLOBAL.User_Id
1576 ,SYSDATE
1577 ,FND_GLOBAL.Login_Id
1578 );
1579 ELSIF p_attr_group_type = 'PO_LINE_EXT_ATTRS' THEN
1580 INSERT INTO PO_UDA_MAPPINGS
1581 (
1582 dummy_attribute_group_id
1583 ,PO_LINE_GROUP_ID
1584 ,created_by
1585 ,creation_date
1586 ,last_updated_by
1587 ,last_update_date
1588 ,last_update_login
1589 )
1590 VALUES
1591 (
1592 p_dummy_attr_group_id
1593 ,p_real_attr_group_id
1594 ,FND_GLOBAL.User_Id
1595 ,SYSDATE
1596 ,FND_GLOBAL.User_Id
1597 ,SYSDATE
1598 ,FND_GLOBAL.Login_Id
1599 );
1600 ELSIF p_attr_group_type = 'PO_DISTRIBUTIONS_EXT_ATTRS' THEN
1601 INSERT INTO PO_UDA_MAPPINGS
1602 (
1603 dummy_attribute_group_id
1604 ,PO_DISTRIBUTIONS_GROUP_ID
1605 ,created_by
1606 ,creation_date
1607 ,last_updated_by
1608 ,last_update_date
1609 ,last_update_login
1610 )
1611 VALUES
1612 (
1613 p_dummy_attr_group_id
1614 ,p_real_attr_group_id
1615 ,FND_GLOBAL.User_Id
1616 ,SYSDATE
1617 ,FND_GLOBAL.User_Id
1618 ,SYSDATE
1619 ,FND_GLOBAL.Login_Id
1620 );
1621 ELSIF p_attr_group_type = 'PO_SHIPMENTS_EXT_ATTRS' THEN
1622 INSERT INTO PO_UDA_MAPPINGS
1623 (
1624 dummy_attribute_group_id
1625 ,PO_SHIPMENTS_GROUP_ID
1626 ,created_by
1627 ,creation_date
1628 ,last_updated_by
1629 ,last_update_date
1630 ,last_update_login
1631 )
1632 VALUES
1633 (
1634 p_dummy_attr_group_id
1635 ,p_real_attr_group_id
1636 ,FND_GLOBAL.User_Id
1637 ,SYSDATE
1638 ,FND_GLOBAL.User_Id
1639 ,SYSDATE
1640 ,FND_GLOBAL.Login_Id
1641 );
1642 ELSIF p_attr_group_type = 'PO_REQ_HEADER_EXT_ATTRS' THEN
1643 INSERT INTO PO_UDA_MAPPINGS
1644 (
1645 dummy_attribute_group_id
1646 ,REQ_HEADER_GROUP_ID
1647 ,created_by
1648 ,creation_date
1649 ,last_updated_by
1650 ,last_update_date
1651 ,last_update_login
1652 )
1653 VALUES
1654 (
1655 p_dummy_attr_group_id
1656 ,p_real_attr_group_id
1657 ,FND_GLOBAL.User_Id
1658 ,SYSDATE
1659 ,FND_GLOBAL.User_Id
1660 ,SYSDATE
1661 ,FND_GLOBAL.Login_Id
1662 );
1663 ELSIF p_attr_group_type = 'PO_REQ_LINE_EXT_ATTRS' THEN
1664 INSERT INTO PO_UDA_MAPPINGS
1665 (
1666 dummy_attribute_group_id
1667 ,REQ_LINE_GROUP_ID
1668 ,created_by
1669 ,creation_date
1670 ,last_updated_by
1671 ,last_update_date
1672 ,last_update_login
1673 )
1674 VALUES
1675 (
1676 p_dummy_attr_group_id
1677 ,p_real_attr_group_id
1678 ,FND_GLOBAL.User_Id
1679 ,SYSDATE
1680 ,FND_GLOBAL.User_Id
1681 ,SYSDATE
1682 ,FND_GLOBAL.Login_Id
1683 );
1684 ELSIF p_attr_group_type = 'PO_REQ_DIST_EXT_ATTRS' THEN
1685 INSERT INTO PO_UDA_MAPPINGS
1686 (
1687 dummy_attribute_group_id
1688 ,REQ_DISTRIBUTION_GROUP_ID
1689 ,created_by
1690 ,creation_date
1691 ,last_updated_by
1692 ,last_update_date
1693 ,last_update_login
1694 )
1695 VALUES
1696 (
1697 p_dummy_attr_group_id
1698 ,p_real_attr_group_id
1699 ,FND_GLOBAL.User_Id
1700 ,SYSDATE
1701 ,FND_GLOBAL.User_Id
1702 ,SYSDATE
1703 ,FND_GLOBAL.Login_Id
1704 );
1705 ELSIF p_attr_group_type = 'PON_AUC_PRICES_EXT_ATTRS' THEN
1706 INSERT INTO PO_UDA_MAPPINGS
1707 (
1708 dummy_attribute_group_id
1709 ,PON_AUC_ITEM_PRICE_GROUP_ID
1710 ,created_by
1711 ,creation_date
1712 ,last_updated_by
1713 ,last_update_date
1714 ,last_update_login
1715 )
1716 VALUES
1717 (
1718 p_dummy_attr_group_id
1719 ,p_real_attr_group_id
1720 ,FND_GLOBAL.User_Id
1721 ,SYSDATE
1722 ,FND_GLOBAL.User_Id
1723 ,SYSDATE
1724 ,FND_GLOBAL.Login_Id
1725 );
1726 ELSIF p_attr_group_type = 'PON_AUC_HDRS_EXT_ATTRS' THEN
1727 INSERT INTO PO_UDA_MAPPINGS
1728 (
1729 dummy_attribute_group_id
1730 ,PON_AUC_HEADER_GROUP_ID
1731 ,created_by
1732 ,creation_date
1733 ,last_updated_by
1734 ,last_update_date
1735 ,last_update_login
1736 )
1737 VALUES
1738 (
1739 p_dummy_attr_group_id
1740 ,p_real_attr_group_id
1741 ,FND_GLOBAL.User_Id
1742 ,SYSDATE
1743 ,FND_GLOBAL.User_Id
1744 ,SYSDATE
1745 ,FND_GLOBAL.Login_Id
1746 );
1747 ELSIF p_attr_group_type = 'PON_BID_HDRS_EXT_ATTRS' THEN
1748 INSERT INTO PO_UDA_MAPPINGS
1749 (
1750 dummy_attribute_group_id
1751 ,BID_HEADER_GROUP_ID
1752 ,created_by
1753 ,creation_date
1754 ,last_updated_by
1755 ,last_update_date
1756 ,last_update_login
1757 )
1758 VALUES
1759 (
1760 p_dummy_attr_group_id
1761 ,p_real_attr_group_id
1762 ,FND_GLOBAL.User_Id
1763 ,SYSDATE
1764 ,FND_GLOBAL.User_Id
1765 ,SYSDATE
1766 ,FND_GLOBAL.Login_Id
1767 );
1768 ELSIF p_attr_group_type = 'PON_BID_PRICES_EXT_ATTRS' THEN
1769 INSERT INTO PO_UDA_MAPPINGS
1770 (
1771 dummy_attribute_group_id
1772 ,BID_ITEM_PRICE_GROUP_ID
1773 ,created_by
1774 ,creation_date
1775 ,last_updated_by
1776 ,last_update_date
1777 ,last_update_login
1778 )
1779 VALUES
1780 (
1781 p_dummy_attr_group_id
1782 ,p_real_attr_group_id
1783 ,FND_GLOBAL.User_Id
1784 ,SYSDATE
1785 ,FND_GLOBAL.User_Id
1786 ,SYSDATE
1787 ,FND_GLOBAL.Login_Id
1788 );
1789 END IF;
1790 d_progress := 60;
1791
1792 IF PO_LOG.d_stmt THEN
1793 PO_LOG.stmt(d_module, d_progress, 'd_progress', d_progress);
1794 END IF;
1795 ELSE
1796 IF p_attr_group_type = 'PO_HEADER_EXT_ATTRS' THEN
1797 UPDATE PO_UDA_MAPPINGS
1798 SET PO_HEADER_GROUP_ID = p_real_attr_group_id
1799 WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
1800 ELSIF p_attr_group_type = 'PO_LINE_EXT_ATTRS' THEN
1801 UPDATE PO_UDA_MAPPINGS
1802 SET PO_LINE_GROUP_ID = p_real_attr_group_id
1803 WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
1804 ELSIF p_attr_group_type = 'PO_DISTRIBUTIONS_EXT_ATTRS' THEN
1805 UPDATE PO_UDA_MAPPINGS
1806 SET PO_DISTRIBUTIONS_GROUP_ID = p_real_attr_group_id
1807 WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
1808 ELSIF p_attr_group_type = 'PO_SHIPMENTS_EXT_ATTRS' THEN
1809 UPDATE PO_UDA_MAPPINGS
1810 SET PO_SHIPMENTS_GROUP_ID = p_real_attr_group_id
1811 WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
1812 ELSIF p_attr_group_type = 'PO_REQ_HEADER_EXT_ATTRS' THEN
1813 UPDATE PO_UDA_MAPPINGS
1814 SET REQ_HEADER_GROUP_ID = p_real_attr_group_id
1815 WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
1816 ELSIF p_attr_group_type = 'PO_REQ_LINE_EXT_ATTRS' THEN
1817 UPDATE PO_UDA_MAPPINGS
1818 SET REQ_LINE_GROUP_ID = p_real_attr_group_id
1819 WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
1820 ELSIF p_attr_group_type = 'PO_REQ_DIST_EXT_ATTRS' THEN
1821 UPDATE PO_UDA_MAPPINGS
1822 SET REQ_DISTRIBUTION_GROUP_ID = p_real_attr_group_id
1823 WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
1824 ELSIF p_attr_group_type = 'PON_AUC_PRICES_EXT_ATTRS' THEN
1825 UPDATE PO_UDA_MAPPINGS
1826 SET PON_AUC_ITEM_PRICE_GROUP_ID = p_real_attr_group_id
1827 WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
1828 ELSIF p_attr_group_type = 'PON_AUC_HDRS_EXT_ATTRS' THEN
1829 UPDATE PO_UDA_MAPPINGS
1830 SET PON_AUC_HEADER_GROUP_ID = p_real_attr_group_id
1831 WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
1832 ELSIF p_attr_group_type = 'PON_BID_HDRS_EXT_ATTRS' THEN
1833 UPDATE PO_UDA_MAPPINGS
1834 SET BID_HEADER_GROUP_ID = p_real_attr_group_id
1835 WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
1836 ELSIF p_attr_group_type = 'PON_BID_PRICES_EXT_ATTRS' THEN
1837 UPDATE PO_UDA_MAPPINGS
1838 SET BID_ITEM_PRICE_GROUP_ID = p_real_attr_group_id
1839 WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
1840 END IF;
1841 d_progress := 70;
1842
1843 IF PO_LOG.d_stmt THEN
1844 PO_LOG.stmt(d_module, d_progress, 'd_progress', d_progress);
1845 END IF;
1846 END IF;
1847 IF (PO_LOG.d_exc) THEN
1848 PO_LOG.exc(d_module, d_progress, p_real_attr_group_id );
1849 PO_LOG.proc_end(d_module);
1850 END IF;
1851 EXCEPTION
1852 WHEN OTHERS THEN
1853 d_progress := 80;
1854 IF (PO_LOG.d_exc) THEN
1855 PO_LOG.exc(d_module, d_progress, p_real_attr_group_id );
1856 PO_LOG.proc_end(d_module);
1857 END IF;
1858 END sync_real_data_levels;
1859
1860 PROCEDURE sync_data_level_attr_group
1861 (
1862 p_attr_group_id IN NUMBER
1863 ,p_data_level_id IN NUMBER
1864 ,x_return_status OUT NOCOPY VARCHAR2
1865 ,x_errorcode OUT NOCOPY NUMBER
1866 ,x_msg_count OUT NOCOPY NUMBER
1867 ,x_msg_data OUT NOCOPY VARCHAR2
1868 )
1869 IS
1870 d_api_name CONSTANT VARCHAR2(30) := 'sync_data_level_attr_group';
1871 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
1872 d_progress NUMBER;
1873 BEGIN
1874
1875 IF (PO_LOG.d_proc) THEN
1876 PO_LOG.proc_begin(d_module);
1877 PO_LOG.proc_begin(d_module, 'p_attr_group_id', p_attr_group_id);
1878 PO_LOG.proc_begin(d_module, 'p_data_level_id', p_data_level_id);
1879 END IF;
1880
1881
1882 d_progress := 10;
1883 INSERT INTO EGO_ATTR_GROUP_DL
1884 (
1885 attr_group_id
1886 ,data_level_id
1887 ,created_by
1888 ,creation_date
1889 ,last_updated_by
1890 ,last_update_date
1891 ,last_update_login
1892 )
1893 VALUES
1894 (
1895 p_attr_group_id
1896 ,p_data_level_id
1897 ,FND_GLOBAL.User_Id
1898 ,SYSDATE
1899 ,FND_GLOBAL.User_Id
1900 ,SYSDATE
1901 ,FND_GLOBAL.Login_Id
1902 );
1903 d_progress := 20;
1904
1905 IF (PO_LOG.d_exc) THEN
1906 PO_LOG.exc(d_module, d_progress, p_attr_group_id );
1907 PO_LOG.exc(d_module, d_progress, p_data_level_id );
1908 PO_LOG.proc_end(d_module);
1909 END IF;
1910
1911 EXCEPTION
1912 WHEN OTHERS THEN
1913 IF (PO_LOG.d_exc) THEN
1914 PO_LOG.exc(d_module, d_progress, p_attr_group_id );
1915 PO_LOG.exc(d_module, d_progress, p_data_level_id );
1916 PO_LOG.proc_end(d_module);
1917 END IF;
1918 END sync_data_level_attr_group;
1919
1920 FUNCTION sync_real_attribute_groups
1921 (
1922 p_glb_api_version IN NUMBER,
1923 p_glb_application_id IN NUMBER,
1924 p_attribute_group_id IN NUMBER,
1925 p_attribute_group_type IN VARCHAR2
1926 )
1927 RETURN VARCHAR2
1928 IS
1929 l_internal_name VARCHAR2(100);
1930 l_attr_group_id NUMBER;
1931 l_return_status VARCHAR2(100);
1932 l_errorcode NUMBER;
1933 l_msg_count NUMBER;
1934 l_msg_data VARCHAR2(100);
1935 l_real_data_level VARCHAR2(100);
1936 l_real_data_level_name VARCHAR2(100);
1937 l_data_level_id NUMBER;
1938 l_true_false VARCHAR2(20);
1939
1940 l_display_name VARCHAR2(100);
1941 l_attr_group_desc VARCHAR2(100);
1942 l_security_type VARCHAR2(10);
1943 l_multi_row_attrib_group VARCHAR2(10);
1944 l_variant_attrib_group VARCHAR2(10);
1945 l_num_of_cols NUMBER;
1946 l_num_of_rows NUMBER;
1947 l_owning_company_id NUMBER;
1948 l_region_code VARCHAR2(10);
1949 l_view_privilege_id NUMBER;
1950 l_edit_privilege_id NUMBER;
1951 l_business_event_flag VARCHAR2(10);
1952 l_pre_business_event_flag VARCHAR2(10);
1953 l_owner NUMBER;
1954
1955 d_api_name CONSTANT VARCHAR2(30) := 'sync_real_attribute_groups';
1956 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
1957 d_progress NUMBER;
1958
1959 BEGIN
1960
1961 IF (PO_LOG.d_proc) THEN
1962 PO_LOG.proc_begin(d_module);
1963 PO_LOG.proc_begin(d_module, 'p_attribute_group_id', p_attribute_group_id);
1964 PO_LOG.proc_begin(d_module, 'p_attribute_group_type', p_attribute_group_type);
1965 END IF;
1966
1967 d_progress := 10;
1968
1969 -- Condition to check if the data level is present
1970 IF is_data_level_present(p_attribute_group_id, p_attribute_group_type, l_internal_name) = 'true' THEN
1971
1972 d_progress := 20;
1973 IF PO_LOG.d_stmt THEN
1974 PO_LOG.stmt(d_module, d_progress, 'is_data_level_present(p_attribute_group_id, p_attribute_group_type, l_internal_name)', is_data_level_present(p_attribute_group_id, p_attribute_group_type, l_internal_name));
1975 END IF;
1976 -- Condition to check the mapping table for the specific attribute group exits
1977 IF get_map_table_details(p_attribute_group_id, p_attribute_group_type) IS NULL THEN
1978
1979 d_progress := 30;
1980
1981 IF PO_LOG.d_stmt THEN
1982 PO_LOG.stmt(d_module, d_progress, 'get_map_table_details(p_attribute_group_id, p_attribute_group_type)', get_map_table_details(p_attribute_group_id, p_attribute_group_type));
1983 END IF;
1984
1985 -- Fetching the data from dummy to create the real attribute group
1986 SELECT DESCRIPTIVE_FLEX_CONTEXT_NAME,
1987 DESCRIPTION,
1988 SECURITY_TYPE,
1989 MULTI_ROW,
1990 VARIANT,
1991 NUM_OF_COLS,
1992 NUM_OF_ROWS,
1993 OWNING_PARTY_ID,
1994 REGION_CODE,
1995 VIEW_PRIVILEGE_ID,
1996 EDIT_PRIVILEGE_ID,
1997 BUSINESS_EVENT_FLAG,
1998 PRE_BUSINESS_EVENT_FLAG,
1999 FL_CTX_EXT.CREATED_BY
2000 INTO l_display_name,
2001 l_attr_group_desc,
2002 l_security_type,
2003 l_multi_row_attrib_group,
2004 l_variant_attrib_group,
2005 l_num_of_cols,
2006 l_num_of_rows,
2007 l_owning_company_id,
2008 l_region_code,
2009 l_view_privilege_id,
2010 l_edit_privilege_id,
2011 l_business_event_flag,
2012 l_pre_business_event_flag,
2013 l_owner
2014 FROM FND_DESCR_FLEX_CONTEXTS_TL FL_CTX_TL,
2015 EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
2016 WHERE FL_CTX_EXT.APPLICATION_ID = FL_CTX_TL.APPLICATION_ID
2017 AND FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = FL_CTX_TL.DESCRIPTIVE_FLEXFIELD_NAME
2018 AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_CTX_TL.DESCRIPTIVE_FLEX_CONTEXT_CODE
2019 AND FL_CTX_EXT.ATTR_GROUP_ID = p_attribute_group_id
2020 AND FL_CTX_TL.LANGUAGE = userenv('LANG');
2021
2022 d_progress := 40;
2023
2024 IF PO_LOG.d_stmt THEN
2025 PO_LOG.stmt(d_module, d_progress, 'l_display_name', l_display_name);
2026 PO_LOG.stmt(d_module, d_progress, 'l_attr_group_desc', l_attr_group_desc);
2027 PO_LOG.stmt(d_module, d_progress, 'l_security_type', l_security_type);
2028 PO_LOG.stmt(d_module, d_progress, 'l_multi_row_attrib_group', l_multi_row_attrib_group);
2029 PO_LOG.stmt(d_module, d_progress, 'l_variant_attrib_group', l_variant_attrib_group);
2030 PO_LOG.stmt(d_module, d_progress, 'l_num_of_cols', l_num_of_cols);
2031 PO_LOG.stmt(d_module, d_progress, 'l_num_of_rows', l_num_of_rows);
2032 PO_LOG.stmt(d_module, d_progress, 'l_owning_company_id', l_owning_company_id);
2033 PO_LOG.stmt(d_module, d_progress, 'l_region_code', l_region_code);
2034 PO_LOG.stmt(d_module, d_progress, 'l_view_privilege_id', l_view_privilege_id);
2035 PO_LOG.stmt(d_module, d_progress, 'l_edit_privilege_id', l_edit_privilege_id);
2036 PO_LOG.stmt(d_module, d_progress, 'l_business_event_flag', l_business_event_flag);
2037 PO_LOG.stmt(d_module, d_progress, 'l_pre_business_event_flag', l_pre_business_event_flag);
2038 PO_LOG.stmt(d_module, d_progress, 'l_owner', l_owner);
2039 END IF;
2040
2041 -- Public api to create the real attribute group
2042 ego_ext_fwk_pub.create_attribute_group(
2043 p_api_version => p_glb_api_version
2044 ,p_application_id => p_glb_application_id
2045 ,p_attr_group_type => p_attribute_group_type
2046 ,p_internal_name => l_internal_name
2047 ,p_display_name => l_display_name
2048 ,p_attr_group_desc => l_attr_group_desc
2049 ,p_security_type => l_security_type
2050 ,p_multi_row_attrib_group => l_multi_row_attrib_group
2051 ,p_variant_attrib_group => l_variant_attrib_group
2052 ,p_num_of_cols => l_num_of_cols
2053 ,p_num_of_rows => l_num_of_rows
2054 ,p_owning_company_id => l_owning_company_id
2055 ,p_region_code => l_region_code
2056 ,p_view_privilege_id => l_view_privilege_id
2057 ,p_edit_privilege_id => l_edit_privilege_id
2058 ,p_business_event_flag => l_business_event_flag
2059 ,p_pre_business_event_flag => l_pre_business_event_flag
2060 ,p_owner => l_owner
2061 ,p_lud => SYSDATE
2062 ,p_init_msg_list => fnd_api.g_FALSE
2063 ,p_commit => fnd_api.g_FALSE
2064 ,x_attr_group_id => l_attr_group_id
2065 ,x_return_status => l_return_status
2066 ,x_errorcode => l_errorcode
2067 ,x_msg_count => l_msg_count
2068 ,x_msg_data => l_msg_data
2069 );
2070
2071 d_progress := 50;
2072
2073 IF PO_LOG.d_stmt THEN
2074 PO_LOG.stmt(d_module, d_progress, 'l_return_status', l_return_status);
2075 END IF;
2076
2077 -- Getting the datalevels depending on the ext table
2078 IF p_attribute_group_type = 'PO_HEADER_EXT_ATTRS' THEN
2079 l_real_data_level := 'PO_HEADER';
2080 l_real_data_level_name := 'Order Header';
2081 ELSIF p_attribute_group_type = 'PO_LINE_EXT_ATTRS' THEN
2082 l_real_data_level := 'PO_LINE';
2083 l_real_data_level_name := 'Order Line';
2084 ELSIF p_attribute_group_type = 'PO_DISTRIBUTIONS_EXT_ATTRS' THEN
2085 l_real_data_level := 'PO_DISTRIBUTION';
2086 l_real_data_level_name := 'Order Distribution';
2087 ELSIF p_attribute_group_type = 'PO_SHIPMENTS_EXT_ATTRS' THEN
2088 l_real_data_level := 'PO_SHIPMENT';
2089 l_real_data_level_name := 'Order Shipment';
2090 ELSIF p_attribute_group_type = 'PO_REQ_HEADER_EXT_ATTRS' THEN
2091 l_real_data_level := 'REQ_HEADER';
2092 l_real_data_level_name := 'Requisition Header';
2093 ELSIF p_attribute_group_type = 'PO_REQ_LINE_EXT_ATTRS' THEN
2094 l_real_data_level := 'REQ_LINE';
2095 l_real_data_level_name := 'Requisition Line';
2096 ELSIF p_attribute_group_type = 'PO_REQ_DIST_EXT_ATTRS' THEN
2097 l_real_data_level := 'REQ_DISTRIBUTION';
2098 l_real_data_level_name := 'Requisition Distribution';
2099 ELSIF p_attribute_group_type = 'PON_AUC_PRICES_EXT_ATTRS' THEN
2100 l_real_data_level := 'PON_AUC_ITEM_PRICE';
2101 l_real_data_level_name := 'Auction Item Price';
2102 ELSIF p_attribute_group_type = 'PON_AUC_HDRS_EXT_ATTRS' THEN
2103 l_real_data_level := 'PON_AUC_HEADER';
2104 l_real_data_level_name := 'Auction Header';
2105 ELSIF p_attribute_group_type = 'PON_BID_HDRS_EXT_ATTRS' THEN
2106 l_real_data_level := 'BID_HEADER';
2107 l_real_data_level_name := 'Bid Header';
2108 ELSIF p_attribute_group_type = 'PON_BID_PRICES_EXT_ATTRS' THEN
2109 l_real_data_level := 'BID_ITEM_PRICE';
2110 l_real_data_level_name := 'Bid Item Price';
2111 END IF;
2112
2113 d_progress := 60;
2114
2115 IF PO_LOG.d_stmt THEN
2116 PO_LOG.stmt(d_module, d_progress, 'l_real_data_level', l_real_data_level);
2117 PO_LOG.stmt(d_module, d_progress, 'l_real_data_level_name', l_real_data_level_name);
2118 END IF;
2119
2120 -- Syncing or linking the datalevels and the real attribute groups
2121 sync_real_data_levels(
2122 p_glb_api_version => p_glb_api_version
2123 ,p_glb_application_id => p_glb_application_id
2124 ,p_dummy_attr_group_id => p_attribute_group_id
2125 ,p_real_attr_group_id => l_attr_group_id
2126 ,p_attr_group_type => p_attribute_group_type
2127 ,p_data_level_name => l_real_data_level
2128 ,p_user_data_level_name => l_real_data_level_name
2129 ,x_data_level_id => l_data_level_id
2130 ,x_return_status => l_return_status
2131 ,x_msg_count => l_msg_count
2132 ,x_msg_data => l_msg_data
2133 );
2134
2135 d_progress := 70;
2136
2137 IF PO_LOG.d_stmt THEN
2138 PO_LOG.stmt(d_module, d_progress, 'l_return_status', l_return_status);
2139 END IF;
2140
2141 END IF;
2142
2143 d_progress := 80;
2144
2145 -- Updating the real attribute groups depending on the changes on the dummy attribute group
2146
2147
2148 IF get_map_table_details(p_attribute_group_id, 'PO_HEADER_EXT_ATTRS') IS NOT NULL THEN
2149 update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PO_HEADER_EXT_ATTRS'), 'PO_HEADER_EXT_ATTRS');
2150 END IF;
2151 IF get_map_table_details(p_attribute_group_id, 'PO_LINE_EXT_ATTRS') IS NOT NULL THEN
2152 update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PO_LINE_EXT_ATTRS'),'PO_LINE_EXT_ATTRS');
2153 END IF;
2154 IF get_map_table_details(p_attribute_group_id, 'PO_DISTRIBUTIONS_EXT_ATTRS') IS NOT NULL THEN
2155 update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PO_DISTRIBUTIONS_EXT_ATTRS'),'PO_DISTRIBUTIONS_EXT_ATTRS');
2156 END IF;
2157 IF get_map_table_details(p_attribute_group_id, 'PO_SHIPMENTS_EXT_ATTRS') IS NOT NULL THEN
2158 update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PO_SHIPMENTS_EXT_ATTRS'),'PO_SHIPMENTS_EXT_ATTRS');
2159 END IF;
2160 IF get_map_table_details(p_attribute_group_id, 'PO_REQ_HEADER_EXT_ATTRS') IS NOT NULL THEN
2161 update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PO_REQ_HEADER_EXT_ATTRS'),'PO_REQ_HEADER_EXT_ATTRS');
2162 END IF;
2163 IF get_map_table_details(p_attribute_group_id, 'PO_REQ_LINE_EXT_ATTRS') IS NOT NULL THEN
2164 update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PO_REQ_LINE_EXT_ATTRS'),'PO_REQ_LINE_EXT_ATTRS');
2165 END IF;
2166 IF get_map_table_details(p_attribute_group_id, 'PO_REQ_DIST_EXT_ATTRS') IS NOT NULL THEN
2167 update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PO_REQ_DIST_EXT_ATTRS'),'PO_REQ_DIST_EXT_ATTRS');
2168 END IF;
2169 IF get_map_table_details(p_attribute_group_id, 'PON_AUC_PRICES_EXT_ATTRS') IS NOT NULL THEN
2170 update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PON_AUC_PRICES_EXT_ATTRS'),'PON_AUC_PRICES_EXT_ATTRS');
2171 END IF;
2172 IF get_map_table_details(p_attribute_group_id, 'PON_AUC_HDRS_EXT_ATTRS') IS NOT NULL THEN
2173 update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PON_AUC_HDRS_EXT_ATTRS'),'PON_AUC_HDRS_EXT_ATTRS');
2174 END IF;
2175 IF get_map_table_details(p_attribute_group_id, 'PON_BID_HDRS_EXT_ATTRS') IS NOT NULL THEN
2176 update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PON_BID_HDRS_EXT_ATTRS'),'PON_BID_HDRS_EXT_ATTRS');
2177 END IF;
2178 IF get_map_table_details(p_attribute_group_id, 'PON_BID_PRICES_EXT_ATTRS') IS NOT NULL THEN
2179 update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PON_BID_PRICES_EXT_ATTRS'),'PON_BID_PRICES_EXT_ATTRS');
2180 END IF;
2181
2182 d_progress := 90;
2183
2184 IF PO_LOG.d_stmt THEN
2185 PO_LOG.stmt(d_module, d_progress, 'd_progress', d_progress);
2186 END IF;
2187
2188 ELSIF get_map_table_details(p_attribute_group_id, p_attribute_group_type) IS NOT NULL THEN
2189
2190 d_progress := 100;
2191
2192 -- Deleting the real attribute group if the data level is checked out from the dummy
2193 delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, p_attribute_group_type));
2194
2195 DELETE FROM ego_attr_group_dl
2196 WHERE ATTR_GROUP_ID = get_map_table_details(p_attribute_group_id, p_attribute_group_type);
2197
2198 d_progress := 110;
2199
2200 IF PO_LOG.d_stmt THEN
2201 PO_LOG.stmt(d_module, d_progress, 'get_map_table_details(p_attribute_group_id, p_attribute_group_type)', get_map_table_details(p_attribute_group_id, p_attribute_group_type));
2202 END IF;
2203
2204 l_true_false := update_map_table(p_attribute_group_id, p_attribute_group_type);
2205
2206 IF (get_map_table_details(p_attribute_group_id, 'PO_HEADER_EXT_ATTRS') IS NULL
2207 AND get_map_table_details(p_attribute_group_id, 'PO_LINE_EXT_ATTRS') IS NULL
2208 AND get_map_table_details(p_attribute_group_id, 'PO_DISTRIBUTIONS_EXT_ATTRS') IS NULL
2209 AND get_map_table_details(p_attribute_group_id, 'PO_SHIPMENTS_EXT_ATTRS') IS NULL
2210 AND get_map_table_details(p_attribute_group_id, 'PO_REQ_HEADER_EXT_ATTRS') IS NULL
2211 AND get_map_table_details(p_attribute_group_id, 'PO_REQ_LINE_EXT_ATTRS') IS NULL
2212 AND get_map_table_details(p_attribute_group_id, 'PO_REQ_DIST_EXT_ATTRS') IS NULL
2213 AND get_map_table_details(p_attribute_group_id, 'PON_AUC_PRICES_EXT_ATTRS') IS NULL
2214 AND get_map_table_details(p_attribute_group_id, 'PON_AUC_HDRS_EXT_ATTRS') IS NULL
2215 AND get_map_table_details(p_attribute_group_id, 'PON_BID_HDRS_EXT_ATTRS') IS NULL
2216 AND get_map_table_details(p_attribute_group_id, 'PON_BID_PRICES_EXT_ATTRS') IS NULL)
2217 THEN
2218 DELETE FROM PO_UDA_MAPPINGS
2219 WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
2220 -- Deleting the data from the mapping table after the
2221 -- real attribute group is deleted and there are no real attribute groups
2222 -- left with the dummy
2223 d_progress := 120;
2224
2225 IF PO_LOG.d_stmt THEN
2226 PO_LOG.stmt(d_module, d_progress, 'p_attribute_group_id', p_attribute_group_id);
2227 END IF;
2228
2229
2230 END IF;
2231
2232 d_progress := 130;
2233
2234 END IF;
2235
2236 IF (PO_LOG.d_exc) THEN
2237 PO_LOG.exc(d_module, d_progress, 'true' );
2238 PO_LOG.proc_end(d_module);
2239 END IF;
2240
2241 RETURN 'true';
2242 EXCEPTION
2243 WHEN OTHERS THEN
2244 IF (PO_LOG.d_exc) THEN
2245 PO_LOG.exc(d_module, d_progress, 'false' );
2246 PO_LOG.proc_end(d_module);
2247 END IF;
2248 END sync_real_attribute_groups;
2249
2250 PROCEDURE delete_real_attr_group
2251 (
2252 p_glb_api_version IN NUMBER
2253 ,p_real_attribute_group_id IN NUMBER
2254 )
2255 IS
2256 l_return_status VARCHAR2(100);
2257 l_errorcode NUMBER;
2258 l_msg_count NUMBER;
2259 l_msg_data VARCHAR2(100);
2260
2261 d_api_name CONSTANT VARCHAR2(30) := 'delete_real_attr_group';
2262 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
2263 d_progress NUMBER;
2264
2265 BEGIN
2266 d_progress := 10;
2267
2268 IF (PO_LOG.d_proc) THEN
2269 PO_LOG.proc_begin(d_module);
2270 PO_LOG.proc_begin(d_module, 'p_real_attribute_group_id', p_real_attribute_group_id);
2271 END IF;
2272
2273 EGO_EXT_FWK_PUB.Delete_Attribute_Group(
2274 p_api_version => p_glb_api_version
2275 ,p_attr_group_id => p_real_attribute_group_id
2276 ,p_init_msg_list => fnd_api.g_FALSE
2277 ,p_commit => fnd_api.g_FALSE
2278 ,x_return_status => l_return_status
2279 ,x_errorcode => l_errorcode
2280 ,x_msg_count => l_msg_count
2281 ,x_msg_data => l_msg_data
2282 );
2283
2284 d_progress := 20;
2285 IF (PO_LOG.d_exc) THEN
2286 PO_LOG.exc(d_module, d_progress, l_return_status );
2287 PO_LOG.proc_end(d_module);
2288 END IF;
2289 EXCEPTION
2290 WHEN OTHERS THEN
2291 IF (PO_LOG.d_exc) THEN
2292 PO_LOG.exc(d_module, d_progress, l_return_status );
2293 PO_LOG.proc_end(d_module);
2294 END IF;
2295 END delete_real_attr_group;
2296
2297 PROCEDURE update_real_meta_attr_group
2298 (
2299 p_glb_api_version IN NUMBER
2300 ,p_glb_application_id IN NUMBER
2301 ,p_dummy_attribute_group_id IN NUMBER
2302 ,p_real_attribute_group_id IN NUMBER
2303 ,p_real_attribute_group_type IN VARCHAR2
2304 )
2305 IS
2306 l_return_status VARCHAR2(100);
2307 l_errorcode NUMBER;
2308 l_msg_count NUMBER;
2309 l_msg_data VARCHAR2(100);
2310 l_security_type VARCHAR2(10);
2311 l_multi_row VARCHAR2(10);
2312 l_business_event_flag VARCHAR2(10);
2313 l_pre_business_event_flag VARCHAR2(10);
2314 l_num_of_cols NUMBER;
2315 l_num_of_rows NUMBER;
2316 l_variant VARCHAR2(100);
2317 l_owning_party_id NUMBER;
2318 l_region_code VARCHAR2(100);
2319 l_view_privilege_id NUMBER;
2320 l_edit_privilege_id NUMBER;
2321 l_display_name VARCHAR2(100);
2322 l_attr_grp_desc VARCHAR2(100);
2323 l_real_attr_grp_type VARCHAR2(100);
2324 l_internal_name VARCHAR2(100);
2325 l_attribute_name VARCHAR2(100);
2326
2327 l_attr_display_name VARCHAR2(100);
2328 --bug16528620 the tip text in the UI and the corresponding description column are 240 characters long. Modifying the local variable to be same
2329 --l_description VARCHAR2(100);
2330 l_description FND_DESCR_FLEX_COL_USAGE_TL.DESCRIPTION%type;
2331 --bug16528620 end
2332 l_sequence NUMBER;
2333 l_data_type VARCHAR2(100);
2334 l_required VARCHAR2(100);
2335 l_searchable VARCHAR2(100);
2336 l_read_only_flag VARCHAR2(10);
2337 l_column VARCHAR2(100);
2338 l_value_set_id NUMBER;
2339 l_info_1 VARCHAR2(100);
2340 l_default_value VARCHAR2(100);
2341 l_unique_key_flag VARCHAR2(100);
2342 l_enabled VARCHAR2(100);
2343 l_display VARCHAR2(100);
2344 l_uom_class VARCHAR2(100);
2345 l_control_level NUMBER;
2346 l_attribute_code VARCHAR2(100);
2347 l_view_in_hierarchy_code VARCHAR2(100);
2348 l_edit_in_hierarchy_code VARCHAR2(100);
2349 l_customization_level VARCHAR2(100);
2350 l_owner NUMBER;
2351 l_lud DATE;
2352
2353 d_api_name CONSTANT VARCHAR2(30) := 'update_real_meta_attr_group';
2354 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
2355 d_progress NUMBER;
2356
2357 CURSOR c_get_attributes_to_delete
2358 (
2359 p_real_attr_grp_type IN VARCHAR2
2360 ,p_dummy_attr_grp_type IN VARCHAR2
2361 ,p_dummy_internal_name IN VARCHAR2
2362 )
2363 IS
2364 SELECT END_USER_COLUMN_NAME
2365 FROM FND_DESCR_FLEX_COLUMN_USAGES
2366 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_real_attr_grp_type
2367 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_dummy_internal_name
2368 AND END_USER_COLUMN_NAME NOT IN
2369 (
2370 SELECT END_USER_COLUMN_NAME
2371 FROM FND_DESCR_FLEX_COLUMN_USAGES
2372 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_dummy_attr_grp_type
2373 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_dummy_internal_name
2374 );
2375
2376 CURSOR c_get_attributes_to_create
2377 (
2378 p_dummy_attr_grp_type IN VARCHAR2
2379 ,p_dummy_internal_name IN VARCHAR2
2380 )
2381 IS
2382 SELECT END_USER_COLUMN_NAME
2383 FROM FND_DESCR_FLEX_COLUMN_USAGES
2384 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_dummy_attr_grp_type
2385 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_dummy_internal_name;
2386
2387 BEGIN
2388
2389 d_progress := 10;
2390
2391 IF (PO_LOG.d_proc) THEN
2392 PO_LOG.proc_begin(d_module);
2393 PO_LOG.proc_begin(d_module, 'p_dummy_attribute_group_id', p_dummy_attribute_group_id);
2394 PO_LOG.proc_begin(d_module, 'p_real_attribute_group_id', p_real_attribute_group_id);
2395 PO_LOG.proc_begin(d_module, 'p_real_attribute_group_type', p_real_attribute_group_type);
2396 END IF;
2397 -- Fetching the data from Dummy Attribute Group (For Attributes)
2398 SELECT SECURITY_TYPE, MULTI_ROW, BUSINESS_EVENT_FLAG,
2399 PRE_BUSINESS_EVENT_FLAG, NUM_OF_COLS, NUM_OF_ROWS, VARIANT,
2400 OWNING_PARTY_ID, REGION_CODE, VIEW_PRIVILEGE_ID, EDIT_PRIVILEGE_ID
2401 INTO l_security_type, l_multi_row, l_business_event_flag,
2402 l_pre_business_event_flag, l_num_of_cols, l_num_of_rows, l_variant,
2403 l_owning_party_id, l_region_code, l_view_privilege_id, l_edit_privilege_id
2404 FROM ego_fnd_dsc_flx_ctx_ext
2405 WHERE ATTR_GROUP_ID = p_dummy_attribute_group_id;
2406
2407 d_progress := 20;
2408
2409 IF PO_LOG.d_stmt THEN
2410 PO_LOG.stmt(d_module, d_progress, 'l_security_type', l_security_type);
2411 PO_LOG.stmt(d_module, d_progress, 'l_multi_row', l_multi_row);
2412 PO_LOG.stmt(d_module, d_progress, 'l_business_event_flag', l_business_event_flag);
2413 PO_LOG.stmt(d_module, d_progress, 'l_pre_business_event_flag', l_pre_business_event_flag);
2414 PO_LOG.stmt(d_module, d_progress, 'l_num_of_cols', l_num_of_cols);
2415 PO_LOG.stmt(d_module, d_progress, 'l_num_of_rows', l_num_of_rows);
2416 PO_LOG.stmt(d_module, d_progress, 'l_variant', l_variant);
2417 PO_LOG.stmt(d_module, d_progress, 'l_owning_party_id', l_owning_party_id);
2418 PO_LOG.stmt(d_module, d_progress, 'l_region_code', l_region_code);
2419 PO_LOG.stmt(d_module, d_progress, 'l_view_privilege_id', l_view_privilege_id);
2420 PO_LOG.stmt(d_module, d_progress, 'l_edit_privilege_id', l_edit_privilege_id);
2421 END IF;
2422
2423 l_real_attr_grp_type := p_real_attribute_group_type;
2424
2425 -- Fetching the internal name from Dummy Attribute Group
2426 SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE
2427 INTO l_internal_name
2428 FROM ego_fnd_dsc_flx_ctx_ext
2429 WHERE ATTR_GROUP_ID = p_dummy_attribute_group_id;
2430
2431 d_progress := 30;
2432
2433 IF PO_LOG.d_stmt THEN
2434 PO_LOG.stmt(d_module, d_progress, 'l_internal_name', l_internal_name);
2435 END IF;
2436
2437 -- Fetching the display name and desc from Dummy Attribute Group (For Attributes)
2438 SELECT DESCRIPTIVE_FLEX_CONTEXT_NAME, DESCRIPTION
2439 INTO l_display_name, l_attr_grp_desc
2440 FROM FND_DESCR_FLEX_CONTEXTS_TL
2441 WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = l_internal_name
2442 AND APPLICATION_ID = p_glb_application_id
2443 AND DESCRIPTIVE_FLEXFIELD_NAME IN
2444 (
2445 SELECT DESCRIPTIVE_FLEXFIELD_NAME
2446 FROM ego_fnd_dsc_flx_ctx_ext
2447 WHERE ATTR_GROUP_ID = p_dummy_attribute_group_id
2448 )
2449 AND LANGUAGE = USERENV('LANG')
2450 AND USERENV('LANG') in (LANGUAGE , SOURCE_LANG);
2451
2452 d_progress := 40;
2453
2454 IF PO_LOG.d_stmt THEN
2455 PO_LOG.stmt(d_module, d_progress, 'l_display_name', l_internal_name);
2456 PO_LOG.stmt(d_module, d_progress, 'l_attr_grp_desc', l_attr_grp_desc);
2457 END IF;
2458
2459 -- Updating the real attribute group depending on the data from the dummy attribute group
2460 ego_ext_fwk_pub.update_attribute_group(
2461 p_api_version => p_glb_api_version
2462 ,p_attr_group_id => p_real_attribute_group_id
2463 ,p_display_name => l_display_name
2464 ,p_attr_group_desc => l_attr_grp_desc
2465 ,p_security_type => l_security_type
2466 ,p_multi_row_attrib_group => l_multi_row
2467 ,p_variant_attrib_group => l_variant
2468 ,p_num_of_cols => l_num_of_cols
2469 ,p_num_of_rows => l_num_of_rows
2470 ,p_owning_company_id => l_owning_party_id
2471 ,p_region_code => l_region_code
2472 ,p_view_privilege_id => l_view_privilege_id
2473 ,p_edit_privilege_id => l_edit_privilege_id
2474 ,p_business_event_flag => l_business_event_flag
2475 ,p_pre_business_event_flag => l_pre_business_event_flag
2476 ,p_init_msg_list => fnd_api.g_FALSE
2477 ,p_commit => fnd_api.g_FALSE
2478 ,x_return_status => l_return_status
2479 ,x_errorcode => l_errorcode
2480 ,x_msg_count => l_msg_count
2481 ,x_msg_data => l_msg_data
2482 );
2483
2484 d_progress := 50;
2485
2486 IF PO_LOG.d_stmt THEN
2487 PO_LOG.stmt(d_module, d_progress, 'l_return_status', l_return_status);
2488 END IF;
2489
2490 -- Fetching the attributes to delete
2491
2492 OPEN c_get_attributes_to_delete
2493 (
2494 l_real_attr_grp_type
2495 ,'PO_UDA_DUMMY_EXT_ATTRS'
2496 ,l_internal_name
2497 );
2498 LOOP
2499 FETCH c_get_attributes_to_delete INTO l_attribute_name;
2500 EXIT WHEN c_get_attributes_to_delete%NOTFOUND;
2501
2502 -- Deleting all the attributes removed from the Dummy in a loop
2503 ego_ext_fwk_pub.delete_attribute(
2504 p_api_version => p_glb_api_version
2505 ,p_application_id => p_glb_application_id
2506 ,p_attr_group_type => l_real_attr_grp_type
2507 ,p_attr_group_name => l_internal_name
2508 ,p_attr_name => l_attribute_name
2509 ,p_init_msg_list => fnd_api.g_FALSE
2510 ,p_commit => fnd_api.g_FALSE
2511 ,x_return_status => l_return_status
2512 ,x_errorcode => l_errorcode
2513 ,x_msg_count => l_msg_count
2514 ,x_msg_data => l_msg_data
2515 );
2516 END LOOP;
2517 CLOSE c_get_attributes_to_delete;
2518
2519 d_progress := 60;
2520
2521 IF PO_LOG.d_stmt THEN
2522 PO_LOG.stmt(d_module, d_progress, 'l_return_status', l_return_status);
2523 END IF;
2524
2525 -- Fetching the attributes to create
2526 OPEN c_get_attributes_to_create
2527 (
2528 'PO_UDA_DUMMY_EXT_ATTRS'
2529 ,l_internal_name
2530 );
2531 LOOP
2532 FETCH c_get_attributes_to_create INTO l_attribute_name;
2533 EXIT WHEN c_get_attributes_to_create%NOTFOUND;
2534
2535 IF PO_LOG.d_stmt THEN
2536 PO_LOG.stmt(d_module, d_progress, 'l_internal_name', l_internal_name);
2537 END IF;
2538 --Bug 15893437 adding exception
2539 BEGIN
2540 -- Fetching the data from dummy attributes to create real attributes
2541 SELECT TL.FORM_LEFT_PROMPT,
2542 TL.DESCRIPTION,
2543 CL.COLUMN_SEQ_NUM,
2544 EG.DATA_TYPE,
2545 CL.REQUIRED_FLAG,
2546 EG.SEARCH_FLAG,
2547 EG.READ_ONLY_FLAG,
2548 EG.APPLICATION_COLUMN_NAME,
2549 CL.FLEX_VALUE_SET_ID,
2550 EG.INFO_1,
2551 CL.DEFAULT_VALUE,
2552 EG.UNIQUE_KEY_FLAG,
2553 CL.ENABLED_FLAG,
2554 CL.DISPLAY_FLAG,
2555 EG.UOM_CLASS,
2556 EG.CONTROL_LEVEL,
2557 EG.ATTRIBUTE_CODE,
2558 EG.VIEW_IN_HIERARCHY_CODE,
2559 EG.EDIT_IN_HIERARCHY_CODE,
2560 EG.CUSTOMIZATION_LEVEL,
2561 CL.CREATED_BY,
2562 CL.CREATION_DATE
2563 INTO l_attr_display_name,
2564 l_description,
2565 l_sequence,
2566 l_data_type,
2567 l_required,
2568 l_searchable,
2569 l_read_only_flag,
2570 l_column,
2571 l_value_set_id,
2572 l_info_1,
2573 l_default_value,
2574 l_unique_key_flag,
2575 l_enabled,
2576 l_display,
2577 l_uom_class,
2578 l_control_level,
2579 l_attribute_code,
2580 l_view_in_hierarchy_code,
2581 l_edit_in_hierarchy_code,
2582 l_customization_level,
2583 l_owner,
2584 l_lud
2585 FROM FND_DESCR_FLEX_COL_USAGE_TL TL,
2586 FND_DESCR_FLEX_COLUMN_USAGES CL,
2587 EGO_FND_DF_COL_USGS_EXT EG
2588 WHERE CL.APPLICATION_ID = p_glb_application_id
2589 AND EG.APPLICATION_ID = CL.APPLICATION_ID
2590 AND EG.DESCRIPTIVE_FLEXFIELD_NAME = CL.DESCRIPTIVE_FLEXFIELD_NAME
2591 AND EG.DESCRIPTIVE_FLEX_CONTEXT_CODE = CL.DESCRIPTIVE_FLEX_CONTEXT_CODE
2592 AND EG.APPLICATION_COLUMN_NAME = CL.APPLICATION_COLUMN_NAME
2593 AND CL.APPLICATION_ID = TL.APPLICATION_ID
2594 AND CL.DESCRIPTIVE_FLEXFIELD_NAME = TL.DESCRIPTIVE_FLEXFIELD_NAME
2595 AND CL.DESCRIPTIVE_FLEX_CONTEXT_CODE = TL.DESCRIPTIVE_FLEX_CONTEXT_CODE
2596 AND CL.APPLICATION_COLUMN_NAME = TL.APPLICATION_COLUMN_NAME
2597 AND TL.LANGUAGE = USERENV('LANG')
2598 AND CL.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_UDA_DUMMY_EXT_ATTRS'
2599 AND CL.DESCRIPTIVE_FLEX_CONTEXT_CODE = l_internal_name
2600 AND CL.END_USER_COLUMN_NAME = l_attribute_name;
2601
2602 d_progress := 70;
2603
2604 IF PO_LOG.d_stmt THEN
2605 PO_LOG.stmt(d_module, d_progress, 'l_attr_display_name', l_attr_display_name);
2606 PO_LOG.stmt(d_module, d_progress, 'l_description', l_description);
2607 PO_LOG.stmt(d_module, d_progress, 'l_sequence', l_sequence);
2608 PO_LOG.stmt(d_module, d_progress, 'l_data_type', l_data_type);
2609 PO_LOG.stmt(d_module, d_progress, 'l_required', l_required);
2610 PO_LOG.stmt(d_module, d_progress, 'l_searchable', l_searchable);
2611 PO_LOG.stmt(d_module, d_progress, 'l_read_only_flag', l_read_only_flag);
2612 PO_LOG.stmt(d_module, d_progress, 'l_column', l_column);
2613 PO_LOG.stmt(d_module, d_progress, 'l_value_set_id', l_value_set_id);
2614 PO_LOG.stmt(d_module, d_progress, 'l_info_1', l_info_1);
2615 PO_LOG.stmt(d_module, d_progress, 'l_default_value', l_default_value);
2616 PO_LOG.stmt(d_module, d_progress, 'l_unique_key_flag', l_unique_key_flag);
2617 PO_LOG.stmt(d_module, d_progress, 'l_enabled', l_enabled);
2618 PO_LOG.stmt(d_module, d_progress, 'l_display', l_display);
2619 PO_LOG.stmt(d_module, d_progress, 'l_uom_class', l_uom_class);
2620 PO_LOG.stmt(d_module, d_progress, 'l_control_level', l_control_level);
2621 PO_LOG.stmt(d_module, d_progress, 'l_attribute_code', l_attribute_code);
2622 PO_LOG.stmt(d_module, d_progress, 'l_view_in_hierarchy_code', l_view_in_hierarchy_code);
2623 PO_LOG.stmt(d_module, d_progress, 'l_edit_in_hierarchy_code', l_edit_in_hierarchy_code);
2624 PO_LOG.stmt(d_module, d_progress, 'l_customization_level', l_customization_level);
2625 PO_LOG.stmt(d_module, d_progress, 'l_owner', l_owner);
2626 PO_LOG.stmt(d_module, d_progress, 'l_lud', l_lud);
2627 END IF;
2628
2629 --bug15893437, commented the sql%found condition as it is not necessary and is inhibiting the call to ego_ext_fwk_pub.update_attribute or ego_ext_fwk_pub.create_attribute
2630 --IF SQL%FOUND THEN
2631
2632 d_progress := 80;
2633
2634 IF PO_LOG.d_stmt THEN
2635 PO_LOG.stmt(d_module, d_progress, 'FOUND', 'FOUND');
2636 END IF;
2637
2638 IF is_attribute_present(l_real_attr_grp_type, l_internal_name, l_attribute_name) = 'true' THEN
2639
2640 -- If attribute found then updating the attribute
2641
2642
2643 ego_ext_fwk_pub.update_attribute
2644 (
2645 p_api_version => p_glb_api_version
2646 ,p_application_id => p_glb_application_id
2647 ,p_attr_group_type => l_real_attr_grp_type
2648 ,p_attr_group_name => l_internal_name
2649 ,p_internal_name => l_attribute_name
2650 ,p_display_name => l_attr_display_name
2651 ,p_description => l_description
2652 ,p_sequence => l_sequence
2653 ,p_required => l_required
2654 ,p_searchable => l_searchable
2655 ,p_read_only_flag => l_read_only_flag
2656 ,p_column => l_column
2657 ,p_value_set_id => l_value_set_id
2658 ,p_info_1 => l_info_1
2659 ,p_default_value => l_default_value
2660 ,p_unique_key_flag => l_unique_key_flag
2661 ,p_enabled => l_enabled
2662 ,p_display => l_display
2663 ,p_control_level => l_control_level
2664 ,p_attribute_code => l_attribute_code
2665 ,p_view_in_hierarchy_code => l_view_in_hierarchy_code
2666 ,p_edit_in_hierarchy_code => l_edit_in_hierarchy_code
2667 ,p_customization_level => l_customization_level
2668 ,p_owner => l_owner
2669 ,p_lud => l_lud
2670 ,p_init_msg_list => fnd_api.g_FALSE
2671 ,p_commit => fnd_api.g_FALSE
2672 ,p_is_nls_mode => fnd_api.g_FALSE
2673 ,p_uom_class => l_uom_class
2674 ,x_return_status => l_return_status
2675 ,x_errorcode => l_errorcode
2676 ,x_msg_count => l_msg_count
2677 ,x_msg_data => l_msg_data
2678 );
2679
2680 d_progress := 90;
2681
2682 IF PO_LOG.d_stmt THEN
2683 PO_LOG.stmt(d_module, d_progress, 'l_return_status', l_return_status);
2684 END IF;
2685
2686 ELSE
2687
2688 -- Else creating the real attribute (Data from dummy attribute is used to create )
2689 ego_ext_fwk_pub.create_attribute
2690 (
2691 p_api_version => p_glb_api_version
2692 ,p_application_id => p_glb_application_id
2693 ,p_attr_group_type => l_real_attr_grp_type
2694 ,p_attr_group_name => l_internal_name
2695 ,p_internal_name => l_attribute_name
2696 ,p_display_name => l_attr_display_name
2697 ,p_description => l_description
2698 ,p_sequence => l_sequence
2699 ,p_data_type => l_data_type
2700 ,p_required => l_required
2701 ,p_searchable => l_searchable
2702 ,p_read_only_flag => l_read_only_flag
2703 ,p_column => l_column
2704 ,p_is_column_indexed => NULL
2705 ,p_value_set_id => l_value_set_id
2706 ,p_info_1 => l_info_1
2707 ,p_default_value => l_default_value
2708 ,p_unique_key_flag => l_unique_key_flag
2709 ,p_enabled => l_enabled
2710 ,p_display => l_display
2711 ,p_uom_class => l_uom_class
2712 ,p_control_level => l_control_level
2713 ,p_attribute_code => l_attribute_code
2714 ,p_view_in_hierarchy_code => l_view_in_hierarchy_code
2715 ,p_edit_in_hierarchy_code => l_edit_in_hierarchy_code
2716 ,p_customization_level => l_customization_level
2717 ,p_owner => l_owner
2718 ,p_lud => l_lud
2719 ,p_init_msg_list => fnd_api.g_FALSE
2720 ,p_commit => fnd_api.g_FALSE
2721 ,x_return_status => l_return_status
2722 ,x_errorcode => l_errorcode
2723 ,x_msg_count => l_msg_count
2724 ,x_msg_data => l_msg_data
2725 );
2726
2727 d_progress := 100;
2728
2729 IF PO_LOG.d_stmt THEN
2730 PO_LOG.stmt(d_module, d_progress, 'l_return_status', l_return_status);
2731 END IF;
2732
2733 END IF;
2734 --END IF;
2735 --Bug 15893437 adding exception
2736 EXCEPTION
2737 WHEN OTHERS THEN
2738 d_progress := 101;
2739 IF (PO_LOG.d_exc) THEN
2740 PO_LOG.exc(d_module, d_progress, l_return_status );
2741 PO_LOG.proc_end(d_module);
2742 END IF;
2743 END;
2744 END LOOP;
2745 CLOSE c_get_attributes_to_create;
2746 d_progress := 100;
2747
2748 IF (PO_LOG.d_exc) THEN
2749 PO_LOG.exc(d_module, d_progress, l_return_status );
2750 PO_LOG.proc_end(d_module);
2751 END IF;
2752 EXCEPTION
2753 WHEN OTHERS THEN
2754 IF (PO_LOG.d_exc) THEN
2755 PO_LOG.exc(d_module, d_progress, l_return_status );
2756 PO_LOG.proc_end(d_module);
2757 END IF;
2758 END update_real_meta_attr_group;
2759
2760 FUNCTION is_attribute_present(p_real_attr_grp_type IN VARCHAR2, p_real_internal_name IN VARCHAR2, p_attribute_name IN VARCHAR2)
2761 RETURN VARCHAR2
2762 IS
2763 l_present NUMBER;
2764
2765 d_api_name CONSTANT VARCHAR2(30) := 'is_attribute_present';
2766 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
2767 d_progress NUMBER;
2768
2769 BEGIN
2770
2771 d_progress := 10;
2772
2773 IF (PO_LOG.d_proc) THEN
2774 PO_LOG.proc_begin(d_module);
2775 PO_LOG.proc_begin(d_module, 'p_real_attr_grp_type', p_real_attr_grp_type);
2776 PO_LOG.proc_begin(d_module, 'p_real_internal_name', p_real_internal_name);
2777 PO_LOG.proc_begin(d_module, 'p_attribute_name', p_attribute_name);
2778 END IF;
2779
2780 SELECT 1
2781 INTO l_present
2782 FROM FND_DESCR_FLEX_COLUMN_USAGES
2783 WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_real_attr_grp_type
2784 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_real_internal_name
2785 AND END_USER_COLUMN_NAME = p_attribute_name
2786 AND ROWNUM < 2;
2787
2788 IF l_present = 1 THEN
2789 IF (PO_LOG.d_exc) THEN
2790 PO_LOG.exc(d_module, d_progress, 'true' );
2791 PO_LOG.proc_end(d_module);
2792 END IF;
2793 RETURN 'true';
2794 END IF;
2795 EXCEPTION
2796 WHEN OTHERS THEN
2797 IF (PO_LOG.d_exc) THEN
2798 PO_LOG.exc(d_module, d_progress, 'false' );
2799 PO_LOG.proc_end(d_module);
2800 END IF;
2801 RETURN 'false';
2802 END is_attribute_present;
2803
2804 FUNCTION update_map_table( p_attribute_group_id IN NUMBER, p_data_level_type IN VARCHAR2 )
2805 RETURN VARCHAR2
2806 IS
2807 d_api_name CONSTANT VARCHAR2(30) := 'update_map_table';
2808 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
2809 d_progress NUMBER;
2810
2811 BEGIN
2812
2813 d_progress := 10;
2814
2815 IF (PO_LOG.d_proc) THEN
2816 PO_LOG.proc_begin(d_module);
2817 PO_LOG.proc_begin(d_module, 'p_attribute_group_id', p_attribute_group_id);
2818 PO_LOG.proc_begin(d_module, 'p_data_level_type', p_data_level_type);
2819 END IF;
2820
2821 IF p_data_level_type = 'PO_HEADER_EXT_ATTRS' THEN
2822 UPDATE PO_UDA_MAPPINGS
2823 SET PO_HEADER_GROUP_ID = NULL
2824 WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
2825 ELSIF p_data_level_type = 'PO_LINE_EXT_ATTRS' THEN
2826 UPDATE PO_UDA_MAPPINGS
2827 SET PO_LINE_GROUP_ID = NULL
2828 WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
2829 ELSIF p_data_level_type = 'PO_DISTRIBUTIONS_EXT_ATTRS' THEN
2830 UPDATE PO_UDA_MAPPINGS
2831 SET PO_DISTRIBUTIONS_GROUP_ID = NULL
2832 WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
2833 ELSIF p_data_level_type = 'PO_SHIPMENTS_EXT_ATTRS' THEN
2834 UPDATE PO_UDA_MAPPINGS
2835 SET PO_SHIPMENTS_GROUP_ID = NULL
2836 WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
2837 ELSIF p_data_level_type = 'PO_REQ_HEADER_EXT_ATTRS' THEN
2838 UPDATE PO_UDA_MAPPINGS
2839 SET REQ_HEADER_GROUP_ID = NULL
2840 WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
2841 ELSIF p_data_level_type = 'PO_REQ_LINE_EXT_ATTRS' THEN
2842 UPDATE PO_UDA_MAPPINGS
2843 SET REQ_LINE_GROUP_ID = NULL
2844 WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
2845 ELSIF p_data_level_type = 'PO_REQ_DIST_EXT_ATTRS' THEN
2846 UPDATE PO_UDA_MAPPINGS
2847 SET REQ_DISTRIBUTION_GROUP_ID = NULL
2848 WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
2849 ELSIF p_data_level_type = 'PON_AUC_PRICES_EXT_ATTRS' THEN
2850 UPDATE PO_UDA_MAPPINGS
2851 SET PON_AUC_ITEM_PRICE_GROUP_ID = NULL
2852 WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
2853 ELSIF p_data_level_type = 'PON_AUC_HDRS_EXT_ATTRS' THEN
2854 UPDATE PO_UDA_MAPPINGS
2855 SET PON_AUC_HEADER_GROUP_ID = NULL
2856 WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
2857 ELSIF p_data_level_type = 'PON_BID_HDRS_EXT_ATTRS' THEN
2858 UPDATE PO_UDA_MAPPINGS
2859 SET BID_HEADER_GROUP_ID = NULL
2860 WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
2861 ELSIF p_data_level_type = 'PON_BID_PRICES_EXT_ATTRS' THEN
2862 UPDATE PO_UDA_MAPPINGS
2863 SET BID_ITEM_PRICE_GROUP_ID = NULL
2864 WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
2865 END IF;
2866
2867 IF (PO_LOG.d_exc) THEN
2868 PO_LOG.exc(d_module, d_progress, 'true' );
2869 PO_LOG.proc_end(d_module);
2870 END IF;
2871
2872 RETURN 'true';
2873 EXCEPTION
2874 WHEN OTHERS THEN
2875 IF (PO_LOG.d_exc) THEN
2876 PO_LOG.exc(d_module, d_progress, 'false' );
2877 PO_LOG.proc_end(d_module);
2878 END IF;
2879 RETURN 'false';
2880 END update_map_table;
2881
2882 FUNCTION get_map_table_details( p_attribute_group_id IN NUMBER, p_data_level_type IN VARCHAR2 )
2883 RETURN NUMBER
2884 IS
2885 l_po_header_group_id NUMBER;
2886 l_po_line_group_id NUMBER;
2887 l_po_distributions_group_id NUMBER;
2888 l_po_shipments_group_id NUMBER;
2889 l_req_header_group_id NUMBER;
2890 l_req_line_group_id NUMBER;
2891 l_req_distribution_group_id NUMBER;
2892 l_pon_auc_item_price_group_id NUMBER;
2893 l_pon_auc_header_group_id NUMBER;
2894 l_bid_header_group_id NUMBER;
2895 l_bid_item_price_group_id NUMBER;
2896
2897 d_api_name CONSTANT VARCHAR2(30) := 'get_map_table_details';
2898 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
2899 d_progress NUMBER;
2900
2901 BEGIN
2902
2903 d_progress := 10;
2904
2905 IF (PO_LOG.d_proc) THEN
2906 PO_LOG.proc_begin(d_module);
2907 PO_LOG.proc_begin(d_module, 'p_attribute_group_id', p_attribute_group_id);
2908 PO_LOG.proc_begin(d_module, 'p_data_level_type', p_data_level_type);
2909 END IF;
2910
2911 BEGIN
2912
2913 SELECT PO_HEADER_GROUP_ID,
2914 PO_LINE_GROUP_ID,
2915 PO_DISTRIBUTIONS_GROUP_ID,
2916 PO_SHIPMENTS_GROUP_ID,
2917 REQ_HEADER_GROUP_ID,
2918 REQ_LINE_GROUP_ID,
2919 REQ_DISTRIBUTION_GROUP_ID,
2920 PON_AUC_ITEM_PRICE_GROUP_ID,
2921 PON_AUC_HEADER_GROUP_ID,
2922 BID_HEADER_GROUP_ID,
2923 BID_ITEM_PRICE_GROUP_ID
2924 INTO l_po_header_group_id,
2925 l_po_line_group_id,
2926 l_po_distributions_group_id,
2927 l_po_shipments_group_id,
2928 l_req_header_group_id,
2929 l_req_line_group_id,
2930 l_req_distribution_group_id,
2931 l_pon_auc_item_price_group_id,
2932 l_pon_auc_header_group_id,
2933 l_bid_header_group_id,
2934 l_bid_item_price_group_id
2935 FROM PO_UDA_MAPPINGS
2936 WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
2937
2938 d_progress := 20;
2939
2940 EXCEPTION
2941 WHEN NO_DATA_FOUND THEN
2942 d_progress := 30;
2943 IF (PO_LOG.d_exc) THEN
2944 PO_LOG.exc(d_module, d_progress, 'NULL' );
2945 PO_LOG.proc_end(d_module);
2946 END IF;
2947 RETURN NULL;
2948 END;
2949
2950 d_progress := 40;
2951
2952 IF p_data_level_type = 'PO_HEADER_EXT_ATTRS' THEN
2953 RETURN l_po_header_group_id;
2954 ELSIF p_data_level_type = 'PO_LINE_EXT_ATTRS' THEN
2955 RETURN l_po_line_group_id;
2956 ELSIF p_data_level_type = 'PO_DISTRIBUTIONS_EXT_ATTRS' THEN
2957 RETURN l_po_distributions_group_id;
2958 ELSIF p_data_level_type = 'PO_SHIPMENTS_EXT_ATTRS' THEN
2959 RETURN l_po_shipments_group_id;
2960 ELSIF p_data_level_type = 'PO_REQ_HEADER_EXT_ATTRS' THEN
2961 RETURN l_req_header_group_id;
2962 ELSIF p_data_level_type = 'PO_REQ_LINE_EXT_ATTRS' THEN
2963 RETURN l_req_line_group_id;
2964 ELSIF p_data_level_type = 'PO_REQ_DIST_EXT_ATTRS' THEN
2965 RETURN l_req_distribution_group_id;
2966 ELSIF p_data_level_type = 'PON_AUC_PRICES_EXT_ATTRS' THEN
2967 RETURN l_pon_auc_item_price_group_id;
2968 ELSIF p_data_level_type = 'PON_AUC_HDRS_EXT_ATTRS' THEN
2969 RETURN l_pon_auc_header_group_id;
2970 ELSIF p_data_level_type = 'PON_BID_HDRS_EXT_ATTRS' THEN
2971 RETURN l_bid_header_group_id;
2972 ELSIF p_data_level_type = 'PON_BID_PRICES_EXT_ATTRS' THEN
2973 RETURN l_bid_item_price_group_id;
2974 ELSE
2975 RETURN NULL;
2976 END IF;
2977
2978 d_progress := 50;
2979 EXCEPTION
2980 WHEN OTHERS THEN
2981 d_progress := 60;
2982 IF (PO_LOG.d_exc) THEN
2983 PO_LOG.exc(d_module, d_progress, 'EXCEPTION' );
2984 PO_LOG.proc_end(d_module);
2985 END IF;
2986 END get_map_table_details;
2987
2988
2989 FUNCTION is_data_level_present
2990 (
2991 p_attribute_group_id IN NUMBER
2992 ,p_data_level IN VARCHAR2
2993 ,x_internal_name OUT NOCOPY VARCHAR2
2994 )
2995 RETURN VARCHAR2
2996 IS
2997 l_data_level_present NUMBER;
2998
2999 d_api_name CONSTANT VARCHAR2(30) := 'is_data_level_present';
3000 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
3001 d_progress NUMBER;
3002
3003 BEGIN
3004
3005 d_progress := 10;
3006
3007 IF (PO_LOG.d_proc) THEN
3008 PO_LOG.proc_begin(d_module);
3009 PO_LOG.proc_begin(d_module, 'p_attribute_group_id', p_attribute_group_id);
3010 PO_LOG.proc_begin(d_module, 'p_data_level', p_data_level);
3011 END IF;
3012
3013 BEGIN
3014 SELECT 1, dummy_def.DESCRIPTIVE_FLEX_CONTEXT_CODE
3015 INTO l_data_level_present, x_internal_name
3016 FROM ego_fnd_dsc_flx_ctx_ext dummy_def,
3017 ego_data_level_b levels,
3018 ego_attr_group_dl dummy_level
3019 WHERE levels.DATA_LEVEL_ID = dummy_level.DATA_LEVEL_ID
3020 AND dummy_def.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_UDA_DUMMY_EXT_ATTRS'
3021 AND dummy_def.ATTR_GROUP_ID = dummy_level.ATTR_GROUP_ID
3022 AND dummy_def.ATTR_GROUP_ID = p_attribute_group_id
3023 AND levels.DATA_LEVEL_NAME = p_data_level;
3024 EXCEPTION
3025 WHEN NO_DATA_FOUND THEN
3026 d_progress := 20;
3027 IF (PO_LOG.d_exc) THEN
3028 PO_LOG.exc(d_module, d_progress, 'false' );
3029 PO_LOG.proc_end(d_module);
3030 END IF;
3031 RETURN 'false';
3032 END;
3033 d_progress := 30;
3034 IF (PO_LOG.d_exc) THEN
3035 PO_LOG.exc(d_module, d_progress, 'true' );
3036 PO_LOG.proc_end(d_module);
3037 END IF;
3038 RETURN 'true';
3039 EXCEPTION
3040 WHEN OTHERS THEN
3041 d_progress := 40;
3042 IF (PO_LOG.d_exc) THEN
3043 PO_LOG.exc(d_module, d_progress, 'EXCEPTION' );
3044 PO_LOG.proc_end(d_module);
3045 END IF;
3046 END is_data_level_present;
3047
3048 PROCEDURE DELETE_TEMP_USAGES
3049 ( p_api_version IN NUMBER,
3050 p_template_id IN NUMBER,
3051 x_return_status OUT NOCOPY VARCHAR2,
3052 x_msg_count OUT NOCOPY NUMBER,
3053 x_msg_data OUT NOCOPY VARCHAR2
3054 )
3055 IS
3056
3057 d_api_name CONSTANT VARCHAR2(30) := 'DELETE_TEMP_USAGES';
3058 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
3059 d_progress NUMBER;
3060
3061 j NUMBER;
3062 l_return_status VARCHAR2(10);
3063 l_errorcode NUMBER;
3064 l_msg_data VARCHAR2(2000);
3065 l_msg_count NUMBER;
3066
3067 CURSOR association_ids(p_template_id number) IS
3068 SELECT UNIQUE(association_id) FROM po_uda_ag_template_usages
3069 WHERE template_id = p_template_id;
3070
3071 BEGIN
3072
3073 IF (PO_LOG.d_proc) THEN
3074 PO_LOG.proc_begin(d_module);
3075 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
3076 END IF;
3077
3078 j := 0;
3079 d_progress := 10;
3080 FOR association_ids_rec IN association_ids(p_template_id) LOOP
3081
3082 ego_ext_fwk_pub.Delete_Association(
3083 p_api_version => 1.0, -- IN NUMBER
3084 p_association_id => association_ids_rec.association_id, -- IN NUMBER
3085 p_init_msg_list => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
3086 p_commit => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
3087 p_force => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
3088 x_return_status => l_return_status, -- OUT NOCOPY VARCHAR2
3089 x_errorcode => l_errorcode, -- OUT NOCOPY NUMBER
3090 x_msg_count => l_msg_count, -- OUT NOCOPY NUMBER
3091 x_msg_data => l_msg_data); -- OUT NOCOPY VARCHAR2
3092
3093 IF (l_return_status = 'S') THEN
3094
3095 DELETE FROM PO_UDA_AG_TEMPLATE_USAGES
3096 WHERE association_id = association_ids_rec.association_id;
3097
3098 IF PO_LOG.d_stmt THEN
3099 PO_LOG.stmt(d_module, d_progress, 'Deleted association and usages for association_id:', association_ids_rec.association_id);
3100 END IF;
3101
3102 ELSE
3103 x_return_status := l_return_status;
3104 x_msg_count := l_msg_count;
3105 x_msg_data := l_msg_data;
3106
3107 IF PO_LOG.d_stmt THEN
3108 PO_LOG.stmt(d_module, d_progress, 'Status Error when deleting association_id:', association_ids_rec.association_id);
3109 END IF;
3110
3111 RETURN;
3112 END IF;
3113 j := j+1;
3114 END LOOP;
3115 IF j= 0 THEN
3116 l_return_status := 'S';
3117 END IF;
3118
3119 d_progress := 20;
3120
3121 x_return_status := l_return_status;
3122 x_msg_count := l_msg_count;
3123 x_msg_data := l_msg_data;
3124
3125 IF PO_LOG.d_stmt THEN
3126 PO_LOG.stmt(d_module, d_progress, 'l_return_status', l_return_status);
3127 END IF;
3128
3129 EXCEPTION
3130 WHEN OTHERS THEN
3131 d_progress := 30;
3132 x_return_status := 'U';
3133 IF PO_LOG.d_stmt THEN
3134 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
3135 END IF;
3136
3137 END DELETE_TEMP_USAGES;
3138
3139 PROCEDURE DELETE_TEMP_SINGLE_USAGE(p_api_version IN NUMBER,
3140 p_template_id IN NUMBER,
3141 p_usage_name IN VARCHAR2,
3142 x_return_status OUT NOCOPY VARCHAR2,
3143 x_msg_count OUT NOCOPY NUMBER,
3144 x_msg_data OUT NOCOPY VARCHAR2
3145 )
3146 IS
3147
3148 d_api_name CONSTANT VARCHAR2(30) := 'DELETE_TEMP_USAGE';
3149 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
3150 d_progress NUMBER;
3151
3152 CURSOR c_get_usage_id(p_template_id NUMBER , p_usage_name VARCHAR2 ) IS
3153 SELECT USAGE_ID,ATTRIBUTE_CATEGORY
3154 FROM po_uda_ag_template_usages
3155 WHERE template_id = p_template_id
3156 AND ATTRIBUTE_CATEGORY = p_usage_name ;
3157
3158 BEGIN
3159
3160 IF (PO_LOG.d_proc) THEN
3161 PO_LOG.proc_begin(d_module);
3162 PO_LOG.proc_begin(d_module, 'p_usage_name', p_usage_name);
3163 END IF;
3164
3165 FOR c_get_usage_id_rec IN c_get_usage_id(p_template_id,p_usage_name) LOOP
3166
3167
3168 DELETE_USAGE
3169 ( p_api_version => 1.0,
3170 p_usage_id => c_get_usage_id_rec.usage_id,
3171 x_return_status => x_return_status,
3172 x_msg_count => x_msg_count,
3173 x_msg_data => x_msg_data
3174 );
3175
3176 DELETE FROM po_uda_ag_template_usages WHERE usage_id = c_get_usage_id_rec.usage_id;
3177
3178 END LOOP ;
3179
3180 COMMIT ;
3181
3182 IF PO_LOG.d_stmt THEN
3183 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
3184 END IF;
3185
3186 END DELETE_TEMP_SINGLE_USAGE;
3187
3188 PROCEDURE generate_template_view
3189 (
3190 p_api_version IN NUMBER,
3191 p_template_id IN NUMBER,
3192 x_template_view_name OUT NOCOPY VARCHAR2,
3193 x_return_status OUT NOCOPY VARCHAR2,
3194 x_msg_count OUT NOCOPY NUMBER,
3195 x_msg_data OUT NOCOPY VARCHAR2
3196 )
3197 IS
3198
3199 d_api_name CONSTANT VARCHAR2(30) := 'generate_template_view';
3200 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
3201 d_progress NUMBER;
3202
3203 l_view_name VARCHAR2(300);
3204 l_select_columns VARCHAR2(15000);
3205 l_select_query VARCHAR2(15000);
3206 l_create_view VARCHAR2(32000);
3207 l_return_status VARCHAR2(2);
3208 l_msg_count NUMBER;
3209 l_msg_data VARCHAR2(2000);
3210 l_hist_view_name VARCHAR2(300);
3211 l_entity_code VARCHAR2(300);
3212
3213 /* 12.2 Online patching project impact */
3214 l_applsys_schema varchar2(30);
3215 l_status VARCHAR2(1);
3216 l_industry VARCHAR2(10);
3217 l_dummy BOOLEAN;
3218
3219
3220 CURSOR get_entity_code(p_template_id NUMBER) IS
3221 SELECT entity_code
3222 FROM po_uda_ag_templates
3223 WHERE template_id = p_template_id;
3224
3225 BEGIN
3226
3227 IF (PO_LOG.d_proc) THEN
3228 PO_LOG.proc_begin(d_module);
3229 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
3230 END IF;
3231
3232 OPEN get_entity_code( p_template_id);
3233 FETCH get_entity_code INTO l_entity_code;
3234 CLOSE get_entity_code;
3235
3236 d_progress := 10;
3237
3238 -- Gets the name of the view that has to be created.
3239 get_template_view_name(p_api_version => 1.0,
3240 p_template_id => p_template_id,
3241 x_template_view_name => l_view_name,
3242 x_return_status => l_return_status,
3243 x_msg_count => l_msg_count,
3244 x_msg_data => l_msg_data);
3245
3246 -- Creates the query of the view(l_select_query) and the view columns names(l_select_columns)
3247 get_query_for_temp_view(p_api_version => 1.0,
3248 p_template_id => p_template_id,
3249 p_hist_view => 'N',
3250 x_return_status => l_return_status,
3251 x_msg_count => l_msg_count,
3252 x_msg_data => l_msg_data,
3253 x_select_query => l_select_query,
3254 x_user_col_name => l_select_columns);
3255
3256 IF (l_select_columns IS NOT NULL AND l_select_query IS NOT NULL) THEN
3257
3258 l_create_view := 'CREATE OR REPLACE VIEW ' || l_view_name || '('|| l_select_columns ||' ) AS ( '|| l_select_query ||' )' ;
3259
3260 IF PO_LOG.d_stmt THEN
3261 PO_LOG.stmt(d_module, d_progress, 'l_create_view', l_create_view);
3262 END IF;
3263
3264 /* 12.2 Online patching project impact: Use ad_ddl instead of execute immediate */
3265 -- get the APPLSYS schema name
3266 l_dummy := fnd_installation.get_app_info('FND', l_status, l_industry, l_applsys_schema);
3267
3268
3269 ad_ddl.do_ddl (applsys_schema => l_applsys_schema,
3270 application_short_name => 'PO',
3271 statement_type => ad_ddl.create_view,
3272 statement => l_create_view,
3273 object_name => l_view_name);
3274
3275 -- EXECUTE IMMEDIATE (l_create_view);
3276 END IF;
3277
3278 /*update database_view_name in po_ag_templates table*/
3279 UPDATE po_uda_ag_templates SET database_view_name = l_view_name
3280 WHERE template_id = p_template_id;
3281
3282 x_template_view_name := l_view_name;
3283
3284 IF PO_LOG.d_stmt THEN
3285 PO_LOG.stmt(d_module, d_progress, 'l_view_name', l_view_name);
3286 END IF;
3287
3288 d_progress := 20;
3289
3290 /* Generate History view for PO's*/
3291
3292 IF(l_entity_code IN ('PO_HEADER_EXT_ATTRS', 'PO_LINE_EXT_ATTRS', 'PO_SHIPMENTS_EXT_ATTRS', 'PO_DISTRIBUTIONS_EXT_ATTRS') )THEN
3293 -- Gets the name of the view that has to be created.
3294 get_template_hist_view_name(p_api_version => 1.0,
3295 p_template_id => p_template_id,
3296 x_template_view_name => l_hist_view_name,
3297 x_return_status => l_return_status,
3298 x_msg_count => l_msg_count,
3299 x_msg_data => l_msg_data);
3300 -- Creates the query of the view(l_select_query) and the view columns names(l_select_columns)
3301 get_query_for_temp_view(p_api_version => 1.0,
3302 p_template_id => p_template_id,
3303 p_hist_view => 'Y',
3304 x_return_status => l_return_status,
3305 x_msg_count => l_msg_count,
3306 x_msg_data => l_msg_data,
3307 x_select_query => l_select_query,
3308 x_user_col_name => l_select_columns);
3309
3310 IF (l_select_columns IS NOT NULL AND l_select_query IS NOT NULL) THEN
3311
3312 l_create_view := 'CREATE OR REPLACE VIEW ' || l_hist_view_name || '('|| l_select_columns ||' ) AS ( '|| l_select_query ||' )' ;
3313
3314 IF PO_LOG.d_stmt THEN
3315 PO_LOG.stmt(d_module, d_progress, 'l_create_view', l_create_view);
3316 END IF;
3317
3318 /* 12.2 online patching project impact: use ad_ddl instead of execute immediate */
3319 ad_ddl.do_ddl (applsys_schema => l_applsys_schema,
3320 application_short_name => 'PO',
3321 statement_type => ad_ddl.create_view,
3322 statement => l_create_view,
3323 object_name => l_hist_view_name);
3324
3325 -- EXECUTE IMMEDIATE (l_create_view);
3326 END IF;
3327 END IF;
3328
3329 x_return_status := 'S';
3330
3331 IF PO_LOG.d_stmt THEN
3332 PO_LOG.stmt(d_module, d_progress, 'l_return_status', l_return_status);
3333 END IF;
3334
3335 EXCEPTION
3336 WHEN OTHERS THEN
3337 d_progress := 30;
3338 x_return_status := 'U';
3339 IF PO_LOG.d_stmt THEN
3340 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
3341 END IF;
3342
3343
3344 END generate_template_view;
3345
3346 PROCEDURE generate_entity_view
3347 (
3348 p_api_version IN NUMBER,
3349 p_entity_code IN VARCHAR2,
3350 x_template_view_name OUT NOCOPY VARCHAR2,
3351 x_return_status OUT NOCOPY VARCHAR2,
3352 x_msg_count OUT NOCOPY NUMBER,
3353 x_msg_data OUT NOCOPY VARCHAR2
3354 )
3355 IS
3356
3357 d_api_name CONSTANT VARCHAR2(30) := 'generate_entity_view';
3358 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
3359 d_progress NUMBER;
3360
3361 l_view_name VARCHAR2(300);
3362 l_select_columns VARCHAR2(10000);
3363 l_select_query VARCHAR2(22500);
3364 l_create_view VARCHAR2(32000);
3365 l_entity_view_name VARCHAR2(500);
3366 l_return_status VARCHAR2(2);
3367 l_msg_count NUMBER;
3368 l_msg_data VARCHAR2(2000);
3369
3370 /* 12.2 Online patching project impact */
3371 l_applsys_schema varchar2(30);
3372 l_status VARCHAR2(1);
3373 l_industry VARCHAR2(10);
3374 l_dummy BOOLEAN;
3375
3376
3377 BEGIN
3378
3379 IF (PO_LOG.d_proc) THEN
3380 PO_LOG.proc_begin(d_module);
3381 PO_LOG.proc_begin(d_module, 'p_entity_code', p_entity_code);
3382 END IF;
3383
3384 l_entity_view_name := p_entity_code ||'_V';
3385
3386 d_progress := 10;
3387
3388 IF PO_LOG.d_stmt THEN
3389 PO_LOG.stmt(d_module, d_progress, 'p_entity_code', p_entity_code);
3390 END IF;
3391
3392 -- Creates the query of the view(l_select_query) and the view columns names(l_select_columns)
3393 get_query_for_entity_view(p_api_version => 1.0,
3394 p_entity_code => p_entity_code,
3395 x_return_status => l_return_status,
3396 x_msg_count => l_msg_count,
3397 x_msg_data => l_msg_data,
3398 x_select_query => l_select_query,
3399 x_user_col_name => l_select_columns);
3400
3401 IF (l_select_columns IS NOT NULL AND l_select_query IS NOT NULL) THEN
3402 l_create_view := 'CREATE OR REPLACE VIEW ' || l_view_name || '('|| l_select_columns ||' ) AS ( '|| l_select_query ||' )' ;
3403
3404 IF PO_LOG.d_stmt THEN
3405 PO_LOG.stmt(d_module, d_progress, 'l_create_view', l_create_view);
3406 END IF;
3407
3408
3409 /* 12.2 Online patching project impact: Use ad_ddl instead of execute immediate */
3410 -- get the APPLSYS schema name
3411 l_dummy := fnd_installation.get_app_info('FND', l_status, l_industry, l_applsys_schema);
3412
3413 ad_ddl.do_ddl (applsys_schema => l_applsys_schema,
3414 application_short_name => 'PO',
3415 statement_type => ad_ddl.create_view,
3416 statement => l_create_view,
3417 object_name => l_view_name);
3418
3419 -- EXECUTE IMMEDIATE (l_create_view);
3420
3421 END IF;
3422
3423 x_template_view_name := l_entity_view_name;
3424
3425 d_progress := 20;
3426 IF PO_LOG.d_stmt THEN
3427 PO_LOG.stmt(d_module, d_progress, 'l_view_name', l_view_name);
3428 END IF;
3429
3430 EXCEPTION
3431 WHEN OTHERS THEN
3432 d_progress := 30;
3433 x_return_status := 'U';
3434 IF PO_LOG.d_stmt THEN
3435 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
3436 END IF;
3437
3438 END generate_entity_view;
3439
3440 PROCEDURE get_template_view_name
3441 (
3442 p_api_version IN NUMBER,
3443 p_template_id IN NUMBER,
3444 x_template_view_name OUT NOCOPY VARCHAR2,
3445 x_return_status OUT NOCOPY VARCHAR2,
3446 x_msg_count OUT NOCOPY NUMBER,
3447 x_msg_data OUT NOCOPY VARCHAR2
3448 )
3449 IS
3450 d_api_name CONSTANT VARCHAR2(30) := 'get_template_view_name';
3451 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
3452 d_progress NUMBER;
3453
3454 l_view_name VARCHAR2(300);
3455 l_document_level VARCHAR2(25);
3456 l_document_type VARCHAR2(25);
3457 l_document_style_id NUMBER;
3458 l_revision NUMBER;
3459 l_functional_area VARCHAR2(25);
3460
3461 CURSOR c_get_templ_dtls(p_template_id NUMBER) IS
3462 SELECT document_level, document_type, document_style_id, revision, functional_area
3463 FROM po_uda_ag_templates
3464 WHERE template_id = p_template_id;
3465
3466
3467 BEGIN
3468
3469 IF (PO_LOG.d_proc) THEN
3470 PO_LOG.proc_begin(d_module);
3471 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
3472 END IF;
3473
3474 d_progress := 10;
3475
3476 OPEN c_get_templ_dtls(p_template_id);
3477 FETCH c_get_templ_dtls INTO l_document_level, l_document_type, l_document_style_id, l_revision, l_functional_area;
3478 CLOSE c_get_templ_dtls;
3479
3480 IF (l_functional_area = 'PURCHASING') THEN
3481 x_template_view_name := 'PO';
3482 ELSIF(l_functional_area = 'SOLICITATION') THEN
3483 x_template_view_name := 'PON';
3484 ELSIF(l_functional_area = 'REQUISITIONS') THEN
3485 x_template_view_name := 'POREQ';
3486 END IF;
3487
3488 IF(l_document_level = 'DISTRIBUTION') THEN
3489 l_document_level := 'DIST';
3490 ELSIF (l_document_level = 'SHIPMENT') THEN
3491 l_document_level := 'SHPMNT';
3492 END IF;
3493
3494 x_template_view_name := x_template_view_name || l_document_level || l_document_type ||'_';
3495
3496 IF(l_document_style_id IS NOT NULL) THEN
3497 x_template_view_name := x_template_view_name || l_document_style_id || '_';
3498 END IF;
3499
3500 x_template_view_name := x_template_view_name || l_revision;
3501 x_return_status := 'S';
3502
3503 IF PO_LOG.d_stmt THEN
3504 PO_LOG.stmt(d_module, d_progress, 'x_template_view_name', x_template_view_name);
3505 END IF;
3506
3507 EXCEPTION
3508 WHEN OTHERS THEN
3509 d_progress := 30;
3510 x_return_status := 'U';
3511 IF PO_LOG.d_stmt THEN
3512 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
3513 END IF;
3514
3515 END get_template_view_name;
3516
3517 PROCEDURE get_template_view_name
3518 (
3519 p_api_version IN NUMBER,
3520 p_template_code IN VARCHAR2 ,
3521 p_entity_code IN VARCHAR2,
3522 x_template_view_name OUT NOCOPY VARCHAR2,
3523 x_return_status OUT NOCOPY VARCHAR2,
3524 x_msg_count OUT NOCOPY NUMBER,
3525 x_msg_data OUT NOCOPY VARCHAR2
3526 )
3527 IS
3528 d_api_name CONSTANT VARCHAR2(30) := 'get_template_view_name';
3529 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
3530 d_progress NUMBER;
3531
3532 l_view_name VARCHAR2(300);
3533 l_entity_code VARCHAR2(300);
3534
3535 BEGIN
3536
3537 IF (PO_LOG.d_proc) THEN
3538 PO_LOG.proc_begin(d_module);
3539 PO_LOG.proc_begin(d_module, 'p_template_code', p_template_code);
3540 PO_LOG.proc_begin(d_module, 'p_entity_code', p_entity_code);
3541 END IF;
3542
3543 l_entity_code := replace(p_entity_code, 'EXT_ATTRS');
3544
3545 x_template_view_name := l_entity_code||p_template_code;
3546
3547 IF PO_LOG.d_stmt THEN
3548 PO_LOG.stmt(d_module, d_progress, 'x_template_view_name', x_template_view_name);
3549 END IF;
3550
3551 EXCEPTION
3552 WHEN OTHERS THEN
3553 d_progress := 30;
3554 x_return_status := 'U';
3555 IF PO_LOG.d_stmt THEN
3556 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
3557 END IF;
3558 END get_template_view_name;
3559
3560 /*
3561 Here we are constructng the query for the view. If the template is having 2 usages, the sample create view statement will be like
3562 CREATE OR REPLACE VIEW po_header_kv3 ( po_header_id, cotroffice_address, cotroffice_contact, cotroffice_email, cotroffice_location,
3563 cotroffice_phone, paymentoffice_address, paymentoffice_contact, paymentoffice_email, paymentoffice_location, paymentoffice_phone)
3564 AS
3565 ( SELECT tbl.PO_HEADER_ID , pkt0.C_EXT_ATTR2 , pkt0.C_EXT_ATTR3 , pkt0.C_EXT_ATTR5 , pkt0.C_EXT_ATTR1 , pkt0.C_EXT_ATTR4 ,
3566 pkt1.C_EXT_ATTR2 , pkt1.C_EXT_ATTR3 , pkt1.C_EXT_ATTR5 , pkt1.C_EXT_ATTR1 , pkt1.C_EXT_ATTR4
3567 FROM PO_HEADERS_ALL_EXT_VL pkt0 , PO_HEADERS_ALL tbl , PO_HEADERS_ALL_EXT_VL pkt1
3568 WHERE pkt0.attr_group_id (+) = 1734 AND tbl.PO_HEADER_ID = pkt0.PO_HEADER_ID (+) AND pkt0.pk1_value is NULL
3569 AND pkt1.attr_group_id (+) = 1740 AND tbl.PO_HEADER_ID = pkt1.PO_HEADER_ID (+) AND pkt1.pk1_value is NULL )
3570 */
3571 PROCEDURE get_query_for_temp_view
3572 ( p_api_version IN NUMBER,
3573 p_template_id IN NUMBER,
3574 p_hist_view IN VARCHAR2,
3575 x_return_status OUT NOCOPY VARCHAR2,
3576 x_msg_count OUT NOCOPY NUMBER,
3577 x_msg_data OUT NOCOPY VARCHAR2,
3578 x_select_query OUT NOCOPY VARCHAR2,
3579 x_user_col_name OUT NOCOPY VARCHAR2
3580 ) IS
3581
3582 d_api_name CONSTANT VARCHAR2(30) := 'get_query_for_temp_view';
3583 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
3584 d_progress NUMBER;
3585
3586 l_query_select VARCHAR2(8000) := null;
3587 l_query_from VARCHAR2(8000) := null;
3588 l_query_where VARCHAR2(8000) := null;
3589 l_select_query VARCHAR2(32500) := NULL;
3590 l_entity_code VARCHAR2(300);
3591
3592 l_prod_table VARCHAR2(300);
3593 l_prod_ext_table VARCHAR2(300);
3594 l_prod_ext_tl_table VARCHAR2(300);
3595 l_pk_col_name VARCHAR2(300);
3596
3597 l_cols_in_attr_grp NUMBER;
3598 l_table VARCHAR2(200);
3599 l_attr_appl_name VARCHAR2(200);
3600 l_user_col_name VARCHAR2(8000);
3601 l_count NUMBER := 0;
3602 i NUMBER := 0;
3603 j NUMBER ;
3604
3605 CURSOR c_table_name(p_template_id NUMBER) IS
3606 SELECT pt.entity_code, et.APPLICATION_VL_NAME, et.APPLICATION_TL_TABLE_NAME
3607 FROM po_uda_ag_templates pt, EGO_FND_DESC_FLEXS_EXT et
3608 WHERE pt.template_id = p_template_id
3609 AND et.DESCRIPTIVE_FLEXFIELD_NAME = pt.entity_code;
3610
3611 CURSOR c_attr_group_ids(p_template_id NUMBER) IS
3612 SELECT distinct(attribute_group_id)
3613 FROM po_uda_ag_template_usages u,
3614 ego_fnd_dsc_flx_ctx_ext a
3615 WHERE u.template_id = p_template_id
3616 and u.attribute_group_id = a.attr_group_id
3617 and a.multi_row = 'N';
3618
3619 CURSOR l_uda_attr_col_name(p_attr_grp_id NUMBER, p_entity_code VARCHAR2) IS
3620 SELECT efdcue.APPLICATION_COLUMN_NAME AS APPLICATION_COLUMN_NAME
3621 FROM ego_fnd_dsc_flx_ctx_ext efdfce , ego_fnd_df_col_usgs_ext efdcue
3622 WHERE ATTR_GROUP_ID = p_attr_grp_id
3623 AND efdfce.DESCRIPTIVE_FLEX_CONTEXT_CODE = efdcue.DESCRIPTIVE_FLEX_CONTEXT_CODE
3624 AND efdcue.DESCRIPTIVE_FLEXFIELD_NAME <> 'PO_UDA_DUMMY_EXT_ATTRS'
3625 AND efdcue.DESCRIPTIVE_FLEXFIELD_NAME = p_entity_code;
3626
3627 CURSOR c_actual_table_name(l_prod_ext_tl_table VARCHAR2) IS
3628 SELECT fo.DATABASE_OBJECT_NAME, fo.PK1_COLUMN_NAME
3629 FROM EGO_OBJECT_EXT_TABLES_B eb, fnd_objects fo
3630 WHERE eb.OBJECT_ID = fo.object_id
3631 AND eb.EXT_TABLE_NAME = l_prod_ext_tl_table;
3632
3633 BEGIN
3634 IF (PO_LOG.d_proc) THEN
3635 PO_LOG.proc_begin(d_module);
3636 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
3637 PO_LOG.proc_begin(d_module, 'p_hist_view', p_hist_view);
3638 END IF;
3639
3640 OPEN c_table_name(p_template_id);
3641 FETCH c_table_name INTO l_entity_code, l_prod_ext_table, l_prod_ext_tl_table;
3642 CLOSE c_table_name;
3643
3644 OPEN c_actual_table_name(l_prod_ext_tl_table);
3645 FETCH c_actual_table_name INTO l_prod_table, l_pk_col_name;
3646 CLOSE c_actual_table_name;
3647
3648 IF (p_hist_view = 'Y') THEN
3649 l_prod_table := PO_UDA_DATA_UTIL.g_object_dff_tl(l_entity_code).l_arch_object_name;
3650 END IF;
3651
3652 d_progress := 20;
3653
3654 IF PO_LOG.d_stmt THEN
3655 PO_LOG.stmt(d_module, d_progress, 'l_entity_code', l_entity_code);
3656 PO_LOG.stmt(d_module, d_progress, 'l_prod_ext_table',l_prod_ext_table );
3657 PO_LOG.stmt(d_module, d_progress, 'l_prod_ext_tl_table', l_prod_ext_tl_table);
3658 PO_LOG.stmt(d_module, d_progress, 'l_prod_table',l_prod_table );
3659 PO_LOG.stmt(d_module, d_progress, 'l_pk_col_name',l_pk_col_name );
3660 END IF;
3661
3662 /*
3663 l_attr_appl_name will be the column name of uda attribute in the ext table. The actual column for this will be
3664 available from ATTR_DISP_COL_NAME function call. This name will be used as the column name in the view.
3665 This view will be dynamic. We will figure out how many attribute groups will be used in this template
3666 and get all its attributes group by group and then construct the query. Finally the select query and
3667 the columns will be returned back and the view will be created by calling program.
3668 */
3669 for c_attr_group_ids_rec IN c_attr_group_ids(p_template_id)
3670 LOOP
3671 IF i=0 THEN
3672
3673 j:=1;
3674 l_table := ' pkt'||i;
3675 for l_uda_attr_col_name_rec IN l_uda_attr_col_name(c_attr_group_ids_rec.attribute_group_id, l_entity_code)
3676 LOOP
3677
3678 IF j= 1 THEN
3679 l_attr_appl_name := l_uda_attr_col_name_rec.APPLICATION_COLUMN_NAME;
3680 l_user_col_name := l_pk_col_name || ' , ' || ATTR_DISP_COL_NAME(c_attr_group_ids_rec.attribute_group_id,l_attr_appl_name);
3681 l_query_select := 'tbl.'|| l_pk_col_name || ' , ' || l_table||'.'||l_attr_appl_name;
3682
3683 ELSE
3684 l_attr_appl_name := l_uda_attr_col_name_rec.APPLICATION_COLUMN_NAME;
3685 l_user_col_name := l_user_col_name || ' , ' ||ATTR_DISP_COL_NAME(c_attr_group_ids_rec.attribute_group_id,l_attr_appl_name);
3686 l_query_select := l_query_select||' , '||l_table||'.'||l_attr_appl_name;
3687 END IF;
3688 j := j+1;
3689 END LOOP;
3690
3691 l_query_from := l_prod_ext_table || l_table || ' , ' || l_prod_table|| ' tbl' ;
3692 IF (p_hist_view = 'Y') THEN
3693 l_query_where := l_table||'.attr_group_id (+) = ' || c_attr_group_ids_rec.attribute_group_id || ' AND tbl.'|| l_pk_col_name|| ' = ' || l_table || '.'|| l_pk_col_name||' (+) ' || ' AND '|| l_table || '.pk1_value = tbl.revision_num ';
3694 ELSE
3695 l_query_where := l_table||'.attr_group_id (+) = ' || c_attr_group_ids_rec.attribute_group_id || ' AND tbl.'|| l_pk_col_name|| ' = ' || l_table || '.'|| l_pk_col_name||' (+) ' || ' AND '|| l_table || '.pk1_value is NULL ';
3696 END IF;
3697
3698 ELSE
3699 l_table := ' pkt'||i;
3700
3701 FOR l_uda_attr_col_name_rec IN l_uda_attr_col_name(c_attr_group_ids_rec.attribute_group_id, l_entity_code)
3702 LOOP
3703 l_attr_appl_name := l_uda_attr_col_name_rec.APPLICATION_COLUMN_NAME;
3704 l_user_col_name := l_user_col_name || ' , ' ||ATTR_DISP_COL_NAME(c_attr_group_ids_rec.attribute_group_id,l_attr_appl_name);
3705 l_query_select := l_query_select||' , '||l_table||'.'||l_attr_appl_name;
3706 END LOOP;
3707
3708 l_query_from := l_query_from || ' , ' ||l_prod_ext_table || l_table ;
3709 IF (p_hist_view = 'Y') THEN
3710 l_query_where := l_query_where || ' AND '|| l_table||'.attr_group_id (+) = ' ||
3711 c_attr_group_ids_rec.attribute_group_id || ' AND tbl.'|| l_pk_col_name|| ' = ' || l_table || '.'|| l_pk_col_name ||' (+) '|| ' AND '|| l_table || '.pk1_value = tbl.revision_num ';
3712 ELSE
3713 l_query_where := l_query_where || ' AND '|| l_table||'.attr_group_id (+) = ' ||
3714 c_attr_group_ids_rec.attribute_group_id || ' AND tbl.'|| l_pk_col_name|| ' = ' || l_table || '.'|| l_pk_col_name ||' (+) '|| ' AND '|| l_table || '.pk1_value is NULL ';
3715 END IF;
3716
3717 END IF;
3718 i := i+1;
3719 END LOOP;
3720
3721 IF (l_query_select IS NOT NULL AND l_query_from IS NOT NULL AND l_query_where IS NOT NULL) THEN
3722 x_select_query := 'SELECT '|| l_query_select || ' FROM '|| l_query_from ||' WHERE ' || l_query_where;
3723 x_user_col_name := l_user_col_name;
3724 END IF;
3725
3726 d_progress := 30;
3727
3728 IF PO_LOG.d_stmt THEN
3729 PO_LOG.stmt(d_module, d_progress, 'x_select_query', x_select_query);
3730 PO_LOG.stmt(d_module, d_progress, 'x_user_col_name', x_user_col_name);
3731 END IF;
3732
3733 EXCEPTION
3734 WHEN OTHERS THEN
3735 d_progress := 40;
3736 x_return_status := 'U';
3737 IF PO_LOG.d_stmt THEN
3738 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
3739 END IF;
3740 END get_query_for_temp_view;
3741
3742 /*
3743 Here we are constructng the query for the view. If the template is having 2 usages, the sample create view statement will be like
3744 CREATE OR REPLACE VIEW pon_bid_hdrs_ext_attrs_v ( bid_number, "SEQUENCE", descriptionsource, label, attachedfile, displaymode, translatablelabel)
3745 AS
3746 (SELECT tbl.bid_number, pkt0.N_EXT_ATTR1 , pkt0.C_EXT_ATTR1 , pkt0.C_EXT_ATTR2 , pkt0.C_EXT_ATTR3 , pkt0.C_EXT_ATTR4 , pkt0.TL_EXT_ATTR2
3747 FROM PON_BID_HEADERS_EXT_VL pkt0 , PON_BID_HEADERS tbl
3748 WHERE pkt0.attr_group_id (+) = 2 AND tbl.bid_number = pkt0.bid_number )
3749 */
3750 PROCEDURE get_query_for_entity_view(p_api_version IN NUMBER,
3751 p_entity_code IN VARCHAR2,
3752 x_return_status OUT NOCOPY VARCHAR2,
3753 x_msg_count OUT NOCOPY NUMBER,
3754 x_msg_data OUT NOCOPY VARCHAR2,
3755 x_select_query OUT NOCOPY VARCHAR2,
3756 x_user_col_name OUT NOCOPY VARCHAR2)
3757 IS
3758
3759 d_api_name CONSTANT VARCHAR2(30) := 'get_query_for_entity_view';
3760 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
3761 d_progress NUMBER;
3762
3763 l_query_select VARCHAR2(10000) := null;
3764 l_query_from VARCHAR2(6000) := null;
3765 l_query_where VARCHAR2(6000) := null;
3766 l_select_query VARCHAR2(22500) := NULL;
3767
3768 l_prod_table VARCHAR2(300);
3769 l_prod_ext_table VARCHAR2(300);
3770 l_prod_ext_tl_table VARCHAR2(300);
3771 l_pk_col_name VARCHAR2(300);
3772
3773
3774 l_table VARCHAR2(300);
3775 l_attr_appl_name VARCHAR2(500);
3776 l_user_col_name VARCHAR2(10000);
3777 l_count NUMBER := 0;
3778 i NUMBER := 0;
3779 j NUMBER ;
3780
3781 CURSOR c_table_name(p_entity_code VARCHAR2) IS
3782 SELECT et.APPLICATION_VL_NAME, et.APPLICATION_TL_TABLE_NAME
3783 FROM EGO_FND_DESC_FLEXS_EXT et
3784 WHERE et.DESCRIPTIVE_FLEXFIELD_NAME = p_entity_code;
3785
3786 CURSOR c_attr_group_ids(p_entity_code VARCHAR2) IS
3787 SELECT DISTINCT(u.attribute_group_id)
3788 FROM po_uda_ag_template_usages u, po_uda_ag_templates b
3789 WHERE b.entity_code = p_entity_code
3790 AND b.template_id = u.template_id;
3791
3792 CURSOR l_uda_attr_col_name(p_attr_grp_id NUMBER, p_entity_code VARCHAR2) IS
3793 SELECT efdcue.APPLICATION_COLUMN_NAME AS APPLICATION_COLUMN_NAME
3794 FROM ego_fnd_dsc_flx_ctx_ext efdfce , ego_fnd_df_col_usgs_ext efdcue
3795 WHERE ATTR_GROUP_ID = p_attr_grp_id
3796 AND efdfce.DESCRIPTIVE_FLEX_CONTEXT_CODE = efdcue.DESCRIPTIVE_FLEX_CONTEXT_CODE
3797 AND efdcue.DESCRIPTIVE_FLEXFIELD_NAME = p_entity_code
3798 AND efdcue.DESCRIPTIVE_FLEXFIELD_NAME <> 'PO_UDA_DUMMY_EXT_ATTRS';
3799
3800 CURSOR c_actual_table_name(l_prod_ext_tl_table VARCHAR2) IS
3801 SELECT fo.DATABASE_OBJECT_NAME, fo.PK1_COLUMN_NAME
3802 FROM EGO_OBJECT_EXT_TABLES_B eb, fnd_objects fo
3803 WHERE eb.OBJECT_ID = fo.object_id
3804 AND eb.EXT_TABLE_NAME = l_prod_ext_tl_table;
3805
3806
3807 BEGIN
3808 IF (PO_LOG.d_proc) THEN
3809 PO_LOG.proc_begin(d_module);
3810 PO_LOG.proc_begin(d_module, 'p_entity_code', p_entity_code);
3811 END IF;
3812
3813 OPEN c_table_name(p_entity_code);
3814 FETCH c_table_name INTO l_prod_ext_table, l_prod_ext_tl_table;
3815 CLOSE c_table_name;
3816
3817 OPEN c_actual_table_name(l_prod_ext_tl_table);
3818 FETCH c_actual_table_name INTO l_prod_table, l_pk_col_name;
3819 CLOSE c_actual_table_name;
3820
3821 d_progress := 20;
3822
3823 IF PO_LOG.d_stmt THEN
3824 PO_LOG.stmt(d_module, d_progress, 'l_prod_ext_table',l_prod_ext_table );
3825 PO_LOG.stmt(d_module, d_progress, 'l_prod_ext_tl_table', l_prod_ext_tl_table);
3826 PO_LOG.stmt(d_module, d_progress, 'l_prod_table',l_prod_table );
3827 PO_LOG.stmt(d_module, d_progress, 'l_pk_col_name',l_pk_col_name );
3828 END IF;
3829
3830 /*
3831 l_attr_appl_name will be the column name of uda attribute in the ext table. The actual column for this will be
3832 available from ATTR_DISP_COL_NAME function call. This name will be used as the column name in the view.
3833 This view will be dynamic. We will figure out how many attribute groups will be used in this template
3834 and get all its attributes group by group and then construct the query. Finally the select query and
3835 the columns will be returned back and the view will be created by calling program.
3836 */
3837
3838 for c_attr_group_ids_rec IN c_attr_group_ids(p_entity_code)
3839 LOOP
3840 IF i=0 THEN
3841
3842 j:=1;
3843 l_table := ' pkt'||i;
3844 for l_uda_attr_col_name_rec IN l_uda_attr_col_name(c_attr_group_ids_rec.attribute_group_id, p_entity_code)
3845 LOOP
3846 IF j= 1 THEN
3847 l_attr_appl_name := l_uda_attr_col_name_rec.APPLICATION_COLUMN_NAME;
3848 l_user_col_name := l_pk_col_name || ' , ' ||ATTR_DISP_COL_NAME(c_attr_group_ids_rec.attribute_group_id,l_attr_appl_name);
3849 l_query_select := 'tbl.'|| l_pk_col_name || ' , ' ||l_table||'.'||l_attr_appl_name;
3850
3851 ELSE
3852 l_attr_appl_name := l_uda_attr_col_name_rec.APPLICATION_COLUMN_NAME;
3853 l_user_col_name := l_user_col_name || ' , ' ||ATTR_DISP_COL_NAME(c_attr_group_ids_rec.attribute_group_id,l_attr_appl_name);
3854 l_query_select := l_query_select||' , '||l_table||'.'||l_attr_appl_name;
3855
3856 END IF;
3857 j := j+1;
3858 END LOOP;
3859
3860 l_query_from := l_prod_ext_table || l_table || ' , ' || l_prod_table|| ' tbl' ;
3861 l_query_where := l_table||'.attr_group_id (+) = ' || c_attr_group_ids_rec.attribute_group_id || ' AND tbl.'|| l_pk_col_name|| ' = ' || l_table || '.'|| l_pk_col_name ||' (+) '|| ' AND '|| l_table || '.pk1_value is NULL ';
3862
3863 ELSE
3864 l_table := ' pkt'||i;
3865
3866 FOR l_uda_attr_col_name_rec IN l_uda_attr_col_name(c_attr_group_ids_rec.attribute_group_id, p_entity_code)
3867 LOOP
3868 l_attr_appl_name := l_uda_attr_col_name_rec.APPLICATION_COLUMN_NAME;
3869 l_user_col_name := l_user_col_name || ' , ' ||ATTR_DISP_COL_NAME(c_attr_group_ids_rec.attribute_group_id,l_attr_appl_name);
3870
3871 l_query_select := l_query_select||' , '||l_table||'.'||l_attr_appl_name;
3872 END LOOP;
3873
3874 l_query_from := l_query_from || ' , ' ||l_prod_ext_table || l_table ;
3875 l_query_where := l_query_where || ' AND '|| l_table||'.attr_group_id (+) = ' || c_attr_group_ids_rec.attribute_group_id || ' AND tbl.'|| l_pk_col_name|| ' = ' || l_table || '.'|| l_pk_col_name ||' (+) '|| ' AND '|| l_table || '.pk1_value is NULL ';
3876
3877 END IF;
3878 i := i+1;
3879 END LOOP;
3880
3881 IF (l_query_select IS NOT NULL AND l_query_from IS NOT NULL AND l_query_where IS NOT NULL) THEN
3882
3883 x_select_query := 'SELECT '|| l_query_select || ' FROM '|| l_query_from ||' WHERE ' || l_query_where;
3884 x_user_col_name := l_user_col_name;
3885 x_return_status := 'S';
3886
3887 END IF;
3888 d_progress := 30;
3889
3890 IF PO_LOG.d_stmt THEN
3891 PO_LOG.stmt(d_module, d_progress, 'x_select_query', x_select_query);
3892 PO_LOG.stmt(d_module, d_progress, 'x_user_col_name', x_user_col_name);
3893 END IF;
3894
3895 EXCEPTION
3896 WHEN OTHERS THEN
3897 d_progress := 40;
3898 x_return_status := 'U';
3899 IF PO_LOG.d_stmt THEN
3900 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
3901 END IF;
3902
3903 END get_query_for_entity_view;
3904
3905 FUNCTION ATTR_DISP_COL_NAME ( P_ATTR_GRP_ID in NUMBER,
3906 P_APP_COL_NAME IN VARCHAR2
3907 ) RETURN VARCHAR2 IS
3908
3909 d_api_name CONSTANT VARCHAR2(30) := 'ATTR_DISP_COL_NAME';
3910 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
3911 d_progress NUMBER;
3912
3913 l_col_name VARCHAR2(600);
3914 l_app_col_name VARCHAR2(200);
3915 l_attr_grp_internal_name VARCHAR2(300);
3916
3917 CURSOR l_attr_disp_col_name IS
3918 select fdfcu.APPLICATION_COLUMN_NAME AS APPLICATION_COLUMN_NAME, fdfcu.END_USER_COLUMN_NAME AS END_USER_COLUMN_NAME,
3919 efdfce.DESCRIPTIVE_FLEX_CONTEXT_CODE AS AG_internal_name
3920 FROM fnd_descr_flex_column_usages fdfcu,
3921 ego_fnd_df_col_usgs_ext efdcue,
3922 ego_fnd_dsc_flx_ctx_ext efdfce
3923 where efdfce.ATTR_GROUP_ID = P_ATTR_GRP_ID
3924 AND efdfce.DESCRIPTIVE_FLEX_CONTEXT_CODE = fdfcu.DESCRIPTIVE_FLEX_CONTEXT_CODE
3925 AND fdfcu.DESCRIPTIVE_FLEX_CONTEXT_CODE = efdcue.DESCRIPTIVE_FLEX_CONTEXT_CODE
3926 AND fdfcu.APPLICATION_COLUMN_NAME = efdcue.APPLICATION_COLUMN_NAME
3927 AND Upper(fdfcu.APPLICATION_COLUMN_NAME) = Upper(P_APP_COL_NAME);
3928
3929 BEGIN
3930
3931 IF (PO_LOG.d_proc) THEN
3932 PO_LOG.proc_begin(d_module);
3933 PO_LOG.proc_begin(d_module, 'P_APP_COL_NAME', P_APP_COL_NAME);
3934 PO_LOG.proc_begin(d_module, 'P_ATTR_GRP_ID', P_ATTR_GRP_ID);
3935 END IF;
3936
3937 OPEN l_attr_disp_col_name;
3938 FETCH l_attr_disp_col_name INTO l_app_col_name, l_col_name, l_attr_grp_internal_name;
3939 close l_attr_disp_col_name;
3940
3941 d_progress := 20;
3942
3943 IF PO_LOG.d_stmt THEN
3944 PO_LOG.stmt(d_module, d_progress, 'l_app_col_name', l_app_col_name);
3945 PO_LOG.stmt(d_module, d_progress, 'l_col_name', l_col_name);
3946 PO_LOG.stmt(d_module, d_progress, 'l_attr_grp_internal_name', l_attr_grp_internal_name);
3947 END IF;
3948
3949 --l_col_name := substr( l_col_name, 1, 14 );
3950 --l_attr_grp_internal_name := substr( l_attr_grp_internal_name, 1,14 );
3951 l_col_name := l_attr_grp_internal_name||g_delimiter|| l_col_name;
3952
3953 IF PO_LOG.d_stmt THEN
3954 PO_LOG.stmt(d_module, d_progress, 'l_final_col_name', l_col_name);
3955 END IF;
3956
3957 RETURN l_col_name;
3958
3959 EXCEPTION
3960 WHEN OTHERS THEN
3961 d_progress := 30;
3962 IF PO_LOG.d_stmt THEN
3963 PO_LOG.stmt(d_module, d_progress, 'Exception block');
3964 END IF;
3965
3966 END ATTR_DISP_COL_NAME;
3967
3968 PROCEDURE copy_uda_actions(p_api_version IN NUMBER,
3969 p_attr_group_id IN VARCHAR2,
3970 p_new_template_code IN VARCHAR2,
3971 p_src_template_id IN NUMBER,
3972 p_object_id IN NUMBER,
3973 x_return_status OUT NOCOPY VARCHAR2,
3974 x_msg_count OUT NOCOPY NUMBER,
3975 x_msg_data OUT NOCOPY VARCHAR2)
3976 IS
3977
3978 l_action_id NUMBER;
3979 l_return_status VARCHAR2(2);
3980 l_msg_count NUMBER;
3981 l_msg_data VARCHAR2(3000);
3982 l_errorcode VARCHAR2(30);
3983 l_src_temp_code VARCHAR2(11);
3984 i NUMBER := 0;
3985
3986 CURSOR c_src_action_ids(p_attr_group_id NUMBER, p_src_template_code VARCHAR2, p_object_id NUMBER) IS
3987 SELECT action_id FROM ego_actions_b
3988 WHERE ATTR_GROUP_ID = p_attr_group_id
3989 AND classification_code = p_src_template_code
3990 AND OBJECT_ID = p_object_id;
3991
3992 CURSOR c_src_record(p_action_id NUMBER) IS
3993 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
3994 FROM EGO_ACTIONS_tl tl, EGO_ACTIONS_B b
3995 WHERE b.action_id = p_action_id AND b.action_id = tl.action_id AND tl.LANGUAGE = userenv('LANG');
3996
3997 CURSOR c_src_action_display(p_action_id NUMBER) IS
3998 SELECT ACTION_ID, EXECUTION_METHOD, DISPLAY_STYLE, PROMPT_APPLICATION_ID, PROMPT_MESSAGE_NAME, VISIBILITY_FLAG, PROMPT_FUNCTION_ID, VISIBILITY_FUNC_ID
3999 FROM EGO_ACTION_DISPLAYS_B
4000 WHERE ACTION_ID = p_action_id;
4001
4002 CURSOR c_src_func_mappings(p_function_id NUMBER, p_action_id NUMBER) IS
4003 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,
4004 MAPPED_TO_GROUP_PK3, MAPPED_ATTRIBUTE, MAPPED_UOM_PARAMETER, VALUE_UOM_SOURCE, FIXED_UOM
4005 FROM EGO_MAPPINGS_B
4006 WHERE function_id = p_function_id
4007 AND MAPPED_OBJ_PK1_VAL = p_action_id;
4008
4009 BEGIN
4010
4011 x_return_status := 'S';
4012
4013 l_src_temp_code := p_src_template_id || '';
4014
4015 FOR c_src_action_ids_rec IN c_src_action_ids(p_attr_group_id, l_src_temp_code, p_object_id) LOOP
4016
4017 FOR c_src_record_rec IN c_src_record(c_src_action_ids_rec.action_id) LOOP
4018 i := i+1;
4019
4020 EGO_EXT_FWK_PUB.Create_Action (
4021 p_api_version => 1.0 ,
4022 p_object_id => c_src_record_rec.OBJECT_ID ,
4023 p_classification_code => p_new_template_code ,
4024 p_attr_group_id => p_attr_group_id ,
4025 p_sequence => c_src_record_rec.SEQUENCE,
4026 p_action_name => c_src_record_rec.ACTION_NAME ,
4027 p_description => c_src_record_rec.DESCRIPTION ,
4028 p_function_id => c_src_record_rec.FUNCTION_ID , -- ignored for now
4029 p_enable_key_attrs => c_src_record_rec.ENABLE_KEY_ATTRIBUTES , -- ignored for now
4030 p_security_privilege_id => c_src_record_rec.SECURITY_PRIVILEGE_ID ,
4031 p_init_msg_list => fnd_api.g_FALSE ,
4032 p_commit => fnd_api.g_FALSE ,
4033 x_action_id => l_action_id ,
4034 x_return_status => l_return_status ,
4035 x_errorcode => l_errorcode,
4036 x_msg_count => l_msg_count ,
4037 x_msg_data => l_msg_data);
4038
4039 /*Once the action is created we have to create the action display data*/
4040 IF (l_return_status = 'S') THEN
4041 FOR c_src_action_display_rec IN c_src_action_display(c_src_action_ids_rec.action_id) LOOP
4042
4043 EGO_EXT_FWK_PUB.Create_Action_Display (
4044 p_api_version => 1.0
4045 ,p_action_id => l_action_id
4046 ,p_exec_code => c_src_action_display_rec.EXECUTION_METHOD
4047 ,p_display_style => c_src_action_display_rec.DISPLAY_STYLE
4048 ,p_prompt_application_id => c_src_action_display_rec.PROMPT_APPLICATION_ID
4049 ,p_prompt_message_name => c_src_action_display_rec.PROMPT_MESSAGE_NAME
4050 ,p_visibility_flag => c_src_action_display_rec.VISIBILITY_FLAG
4051 ,p_prompt_function_id => c_src_action_display_rec.PROMPT_FUNCTION_ID
4052 ,p_visibility_func_id => c_src_action_display_rec.VISIBILITY_FUNC_ID
4053 ,p_init_msg_list => fnd_api.g_FALSE
4054 ,p_commit => fnd_api.g_FALSE
4055 ,x_return_status => l_return_status
4056 ,x_errorcode => l_errorcode
4057 ,x_msg_count => l_msg_count
4058 ,x_msg_data => l_msg_data );
4059
4060 END LOOP;
4061 END IF;
4062 /* The actions and action displays are copied. Now copy the function parameter mappings*/
4063
4064 IF l_return_status = 'S' THEN
4065 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
4066
4067 EGO_EXT_FWK_PUB.Create_Mapping (
4068 p_api_version => 1.0
4069 ,p_function_id => c_src_record_rec.FUNCTION_ID
4070 ,p_mapped_obj_type => c_src_func_mappings_rec.MAPPED_OBJ_TYPE
4071 ,p_mapped_obj_pk1_value => l_action_id
4072 ,p_func_param_id => c_src_func_mappings_rec.FUNC_PARAM_ID
4073 ,p_mapping_group_type => c_src_func_mappings_rec.MAPPED_TO_GROUP_TYPE
4074 ,p_mapping_group_pk1 => c_src_func_mappings_rec.MAPPED_TO_GROUP_PK1
4075 ,p_mapping_group_pk2 => c_src_func_mappings_rec.MAPPED_TO_GROUP_PK2
4076 ,p_mapping_group_pk3 => c_src_func_mappings_rec.MAPPED_TO_GROUP_PK3
4077 ,p_mapping_value => c_src_func_mappings_rec.MAPPED_ATTRIBUTE
4078 ,p_mapped_uom_parameter => c_src_func_mappings_rec.MAPPED_UOM_PARAMETER
4079 ,p_value_uom_source => c_src_func_mappings_rec.VALUE_UOM_SOURCE
4080 ,p_fixed_uom => c_src_func_mappings_rec.FIXED_UOM
4081 ,p_init_msg_list => fnd_api.g_FALSE
4082 ,p_commit => fnd_api.g_FALSE
4083 ,x_return_status => l_return_status
4084 ,x_errorcode => l_errorcode
4085 ,x_msg_count => l_msg_count
4086 ,x_msg_data => l_msg_data);
4087
4088 END LOOP;
4089 END IF;
4090 IF l_return_status <> 'S' THEN
4091 x_return_status := l_return_status;
4092 END IF;
4093 x_msg_count := l_msg_count;
4094 x_msg_data := l_msg_data;
4095 END LOOP;
4096 END LOOP;
4097
4098 IF (i = 0) THEN
4099 x_return_status := 'S';
4100 END IF;
4101 END copy_uda_actions;
4102
4103 PROCEDURE get_template_hist_view_name
4104 (
4105 p_api_version IN NUMBER,
4106 p_template_id IN NUMBER,
4107 x_template_view_name OUT NOCOPY VARCHAR2,
4108 x_return_status OUT NOCOPY VARCHAR2,
4109 x_msg_count OUT NOCOPY NUMBER,
4110 x_msg_data OUT NOCOPY VARCHAR2
4111 )
4112
4113 IS
4114
4115 d_api_name CONSTANT VARCHAR2(30) := 'get_template_hist_view_name';
4116 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
4117 d_progress NUMBER;
4118
4119 l_view_name VARCHAR2(300);
4120 l_document_level VARCHAR2(25);
4121 l_document_type VARCHAR2(25);
4122 l_document_style_id NUMBER;
4123 l_revision NUMBER;
4124 l_functional_area VARCHAR2(25);
4125
4126 CURSOR c_get_templ_dtls(p_template_id NUMBER) IS
4127 SELECT document_level, document_type, document_style_id, revision, functional_area
4128 FROM po_uda_ag_templates
4129 WHERE template_id = p_template_id;
4130
4131 BEGIN
4132
4133 IF (PO_LOG.d_proc) THEN
4134 PO_LOG.proc_begin(d_module);
4135 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
4136 END IF;
4137
4138 d_progress := 10;
4139
4140 OPEN c_get_templ_dtls(p_template_id);
4141 FETCH c_get_templ_dtls INTO l_document_level, l_document_type, l_document_style_id, l_revision, l_functional_area;
4142 CLOSE c_get_templ_dtls;
4143
4144 IF (l_functional_area = 'PURCHASING') THEN
4145 x_template_view_name := 'PO';
4146 ELSIF(l_functional_area = 'SOLICITATION') THEN
4147 x_template_view_name := 'PON';
4148 ELSIF(l_functional_area = 'REQUISITIONS') THEN
4149 x_template_view_name := 'POREQ';
4150 END IF;
4151
4152 IF(l_document_level = 'DISTRIBUTION') THEN
4153 l_document_level := 'DIST';
4154 ELSIF (l_document_level = 'SHIPMENT') THEN
4155 l_document_level := 'SHPMNT';
4156 END IF;
4157
4158 x_template_view_name := x_template_view_name || l_document_level || l_document_type ||'_';
4159
4160 IF(l_document_style_id IS NOT NULL) THEN
4161 x_template_view_name := x_template_view_name || l_document_style_id || '_';
4162 END IF;
4163
4164 x_template_view_name := x_template_view_name || l_revision || '_H';
4165 x_return_status := 'S';
4166 IF PO_LOG.d_stmt THEN
4167 PO_LOG.stmt(d_module, d_progress, 'x_template_view_name', x_template_view_name);
4168 END IF;
4169
4170 EXCEPTION
4171 WHEN OTHERS THEN
4172 d_progress := 30;
4173 x_return_status := 'U';
4174 IF PO_LOG.d_stmt THEN
4175 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
4176 END IF;
4177
4178 END get_template_hist_view_name;
4179
4180
4181 /* This API is used to decode the value set meaning for the usage Context.
4182 We decode only table type, Validation dependent and Validation independent
4183 type value sets since ego supports only these types.
4184 */
4185
4186 PROCEDURE decode_value_set_meaning
4187 (
4188 p_api_version IN NUMBER,
4189 p_usage_id IN NUMBER,
4190 p_attr_col IN VARCHAR2,
4191 p_attr_value IN VARCHAR2,
4192 x_attr_meaning OUT NOCOPY VARCHAR2,
4193 x_return_status OUT NOCOPY VARCHAR2,
4194 x_msg_count OUT NOCOPY NUMBER,
4195 x_msg_data OUT NOCOPY VARCHAR2)
4196 IS
4197
4198 d_api_name CONSTANT VARCHAR2(30) := 'decode_value_set_meaning';
4199 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
4200 d_progress NUMBER;
4201
4202 l_value_set_id NUMBER;
4203 l_application_table_name VARCHAR2(240);
4204 l_value_column_name VARCHAR2(240);
4205 l_additional_where_clause LONG;
4206 l_id_column_name VARCHAR2(240);
4207 l_value_column_type VARCHAR2(1);
4208 l_id_column_type VARCHAR2(1);
4209 l_query LONG;
4210 l_validation_type VARCHAR2(2);
4211
4212 CURSOR c_get_value_set_id(p_usage_id NUMBER, p_attr_col VARCHAR2) IS
4213 SELECT f.FLEX_VALUE_SET_ID, fv.VALIDATION_TYPE FROM FND_DESCR_FLEX_COL_USAGE_VL f, PO_UDA_AG_TEMPLATE_USAGES t , fnd_flex_value_sets fv
4214 WHERE DESCRIPTIVE_FLEXFIELD_NAME LIKE 'PO_UDA_TEMPLATE_USAGES'
4215 AND f.DESCRIPTIVE_FLEX_CONTEXT_CODE = T.ATTRIBUTE_CATEGORY
4216 AND t. usage_id = p_usage_id AND f.APPLICATION_COLUMN_NAME = p_attr_col
4217 AND f.FLEX_VALUE_SET_ID = fv.FLEX_VALUE_SET_ID;
4218
4219 CURSOR c_query_details(p_value_set_id NUMBER) IS
4220 SELECT value_column_name, value_column_type,
4221 id_column_name, id_column_type,
4222 application_table_name, additional_where_clause
4223 from fnd_flex_validation_tables
4224 where flex_value_set_id = p_value_set_id;
4225
4226 CURSOR c_flex_values(p_value_set_id NUMBER, p_attr_value VARCHAR2) IS
4227 SELECT FLEX_VALUE_MEANING
4228 FROM FND_FLEX_VALUES_VL
4229 WHERE flex_value_set_id = p_value_set_id
4230 AND FLEX_VALUE = p_attr_value;
4231
4232 TYPE ref_cursor IS REF CURSOR;
4233 c_value_meaning ref_cursor;
4234
4235 BEGIN
4236
4237 IF (PO_LOG.d_proc) THEN
4238 PO_LOG.proc_begin(d_module);
4239 PO_LOG.proc_begin(d_module, 'p_usage_id', p_usage_id);
4240 PO_LOG.proc_begin(d_module, 'p_attr_col', p_attr_col);
4241 PO_LOG.proc_begin(d_module, 'p_attr_value', p_attr_value);
4242 END IF;
4243
4244 --cursor fetches the flex value set id for this usage and column combination
4245 OPEN c_get_value_set_id(p_usage_id, p_attr_col);
4246 FETCH c_get_value_set_id INTO l_value_set_id, l_validation_type;
4247 CLOSE c_get_value_set_id;
4248
4249 d_progress := 10;
4250
4251 IF PO_LOG.d_stmt THEN
4252 PO_LOG.stmt(d_module, d_progress, 'l_value_set_id',l_value_set_id );
4253 PO_LOG.stmt(d_module, d_progress, 'l_validation_type', l_validation_type);
4254 END IF;
4255
4256 /*If value set is of table type fetch the query, execute it
4257 and find the meaning. Else if the value set is of type validation
4258 independent or validation dependent fetch the meaning from FND_FLEX_VALUES_VL
4259 If not of these 3 types display error invalid value set.*/
4260 IF (l_validation_type = 'F') THEN
4261 d_progress := 20;
4262
4263 OPEN c_query_details(l_value_set_id);
4264 FETCH c_query_details INTO l_value_column_name, l_value_column_type, l_id_column_name, l_id_column_type, l_application_table_name, l_additional_where_clause;
4265 CLOSE c_query_details;
4266
4267
4268 IF(Upper(SubStr( l_additional_where_clause,1, 5)) = 'WHERE' OR l_additional_where_clause LIKE 'order%') THEN
4269 l_query := 'SELECT '||l_value_column_name ||' from (SELECT ' ||l_value_column_name ||' , '||l_id_column_name ||
4270 ' from ' || l_application_table_name ||' ' || l_additional_where_clause || ' ) where '
4271 || l_id_column_name || ' = ' ||'''' || p_attr_value || '''';
4272 ELSE --Bug 14456753 - handled null case for l_additional_where_clause
4273 IF l_additional_where_clause IS NOT NULL THEN
4274 l_additional_where_clause := ' where '|| l_additional_where_clause;
4275 ELSE
4276 l_additional_where_clause := '';
4277 END IF ;
4278
4279 l_query := 'SELECT value_column ' ||' from (SELECT ' ||l_value_column_name ||' as value_column , '||l_id_column_name ||
4280 ' as id_column from ' || l_application_table_name || l_additional_where_clause || ' ) where id_column '
4281 || ' = ' ||'''' || p_attr_value || '''';
4282 END IF;
4283
4284 IF PO_LOG.d_stmt THEN
4285 PO_LOG.stmt(d_module, d_progress, 'l_query',l_query);
4286 END IF;
4287
4288 d_progress := 20;
4289 OPEN c_value_meaning FOR l_query;
4290 FETCH c_value_meaning into x_attr_meaning;
4291 CLOSE c_value_meaning;
4292
4293 ELSIF (l_validation_type = 'I' OR l_validation_type = 'D') THEN
4294 d_progress := 30;
4295 OPEN c_flex_values(l_value_set_id, p_attr_value);
4296 FETCH c_flex_values INTO x_attr_meaning;
4297 CLOSE c_flex_values;
4298
4299 ELSE
4300 d_progress := 40;
4301 x_attr_meaning := 'INVALID VALUE SET';
4302
4303 END IF;
4304
4305 x_return_status := 'S';
4306
4307 IF PO_LOG.d_stmt THEN
4308 PO_LOG.stmt(d_module, d_progress, 'x_attr_meaning', x_attr_meaning);
4309 END IF;
4310
4311 EXCEPTION
4312 WHEN OTHERS THEN
4313 d_progress := 50;
4314 x_return_status := 'U';
4315 IF PO_LOG.d_stmt THEN
4316 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
4317 END IF;
4318 END decode_value_set_meaning;
4319
4320 FUNCTION get_flex_segment_meaning(
4321 p_api_version IN NUMBER,
4322 p_usage_id IN NUMBER,
4323 p_attr_col IN VARCHAR2,
4324 p_attr_value IN VARCHAR2
4325 ) RETURN VARCHAR2 IS
4326
4327 d_api_name CONSTANT VARCHAR2(30) := 'decode_value_set_meaning';
4328 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
4329 d_progress NUMBER;
4330
4331 l_value_set_id NUMBER;
4332 l_application_table_name VARCHAR2(240);
4333 l_value_column_name VARCHAR2(240);
4334 l_additional_where_clause LONG;
4335 l_id_column_name VARCHAR2(240);
4336 l_value_column_type VARCHAR2(1);
4337 l_id_column_type VARCHAR2(1);
4338 l_query LONG;
4339 l_validation_type VARCHAR2(2);
4340 l_attr_meaning VARCHAR2(100);
4341
4342 CURSOR c_get_value_set_id(p_usage_id NUMBER, p_attr_col VARCHAR2) IS
4343 SELECT f.FLEX_VALUE_SET_ID, fv.VALIDATION_TYPE FROM FND_DESCR_FLEX_COL_USAGE_VL f, PO_UDA_AG_TEMPLATE_USAGES t , fnd_flex_value_sets fv
4344 WHERE DESCRIPTIVE_FLEXFIELD_NAME LIKE 'PO_UDA_TEMPLATE_USAGES'
4345 AND f.DESCRIPTIVE_FLEX_CONTEXT_CODE = T.ATTRIBUTE_CATEGORY
4346 AND t. usage_id = p_usage_id AND f.APPLICATION_COLUMN_NAME = p_attr_col
4347 AND f.FLEX_VALUE_SET_ID = fv.FLEX_VALUE_SET_ID;
4348
4349 CURSOR c_query_details(p_value_set_id NUMBER) IS
4350 SELECT value_column_name, value_column_type,
4351 id_column_name, id_column_type,
4352 application_table_name, additional_where_clause
4353 from fnd_flex_validation_tables
4354 where flex_value_set_id = p_value_set_id;
4355
4356 CURSOR c_flex_values(p_value_set_id NUMBER, p_attr_value VARCHAR2) IS
4357 SELECT FLEX_VALUE_MEANING
4358 FROM FND_FLEX_VALUES_VL
4359 WHERE flex_value_set_id = p_value_set_id
4360 AND FLEX_VALUE = p_attr_value;
4361
4362 TYPE ref_cursor IS REF CURSOR;
4363 c_value_meaning ref_cursor;
4364
4365 BEGIN
4366
4367 IF (PO_LOG.d_proc) THEN
4368 PO_LOG.proc_begin(d_module);
4369 PO_LOG.proc_begin(d_module, 'p_usage_id', p_usage_id);
4370 PO_LOG.proc_begin(d_module, 'p_attr_col', p_attr_col);
4371 PO_LOG.proc_begin(d_module, 'p_attr_value', p_attr_value);
4372 END IF;
4373
4374
4375 IF p_attr_value IS NULL THEN
4376 RETURN '';
4377 END IF ;
4378 --cursor fetches the flex value set id for this usage and column combination
4379 OPEN c_get_value_set_id(p_usage_id, p_attr_col);
4380 FETCH c_get_value_set_id INTO l_value_set_id, l_validation_type;
4381 CLOSE c_get_value_set_id;
4382
4383 d_progress := 10;
4384
4385 IF PO_LOG.d_stmt THEN
4386 PO_LOG.stmt(d_module, d_progress, 'l_value_set_id',l_value_set_id );
4387 PO_LOG.stmt(d_module, d_progress, 'l_validation_type', l_validation_type);
4388 END IF;
4389
4390 /*If value set is of table type fetch the query, execute it
4391 and find the meaning. Else if the value set is of type validation
4392 independent or validation dependent fetch the meaning from FND_FLEX_VALUES_VL
4393 If not of these 3 types display error invalid value set.*/
4394
4395 IF (l_validation_type = 'F') THEN
4396 d_progress := 20;
4397
4398 OPEN c_query_details(l_value_set_id);
4399 FETCH c_query_details INTO l_value_column_name, l_value_column_type, l_id_column_name, l_id_column_type, l_application_table_name, l_additional_where_clause;
4400 CLOSE c_query_details;
4401
4402 IF(Upper(SubStr( l_additional_where_clause,1, 5)) = 'WHERE' OR l_additional_where_clause LIKE 'order%' ) THEN
4403 l_query := 'SELECT '||l_value_column_name ||' from (SELECT ' ||l_value_column_name ||' , '||l_id_column_name ||
4404 ' from ' || l_application_table_name ||' ' || l_additional_where_clause || ' ) where '
4405 || l_id_column_name || ' = ' ||'''' || p_attr_value || '''';
4406 ELSE
4407 l_query := 'SELECT '||l_value_column_name ||' from (SELECT ' ||l_value_column_name ||' , '||l_id_column_name ||
4408 ' from ' || l_application_table_name ||' where ' || l_additional_where_clause || ' ) where '
4409 || l_id_column_name || ' = ' ||'''' || p_attr_value || '''';
4410 END IF;
4411
4412 IF PO_LOG.d_stmt THEN
4413 PO_LOG.stmt(d_module, d_progress, 'l_query',l_query);
4414 END IF;
4415
4416 d_progress := 20;
4417 OPEN c_value_meaning FOR l_query;
4418 FETCH c_value_meaning into l_attr_meaning;
4419 CLOSE c_value_meaning;
4420
4421 ELSIF (l_validation_type = 'I' OR l_validation_type = 'D') THEN
4422 d_progress := 30;
4423 OPEN c_flex_values(l_value_set_id, p_attr_value);
4424 FETCH c_flex_values INTO l_attr_meaning;
4425 CLOSE c_flex_values;
4426
4427 END IF;
4428
4429 IF l_attr_meaning IS NULL THEN
4430 l_attr_meaning := 'Null' ;
4431 END IF ;
4432
4433 IF PO_LOG.d_stmt THEN
4434 PO_LOG.stmt(d_module, d_progress, 'l_attr_meaning', l_attr_meaning);
4435 END IF;
4436
4437 RETURN l_attr_meaning ;
4438
4439 EXCEPTION
4440 WHEN OTHERS THEN
4441 d_progress := 50;
4442 IF PO_LOG.d_stmt THEN
4443 PO_LOG.stmt(d_module, d_progress,SQLERRM );
4444 END IF;
4445 RETURN 'Null' ;
4446
4447
4448 END get_flex_segment_meaning;
4449
4450 FUNCTION get_action_type(p_functional_area VARCHAR2,
4451 p_document_type VARCHAR2,
4452 p_document_style_id NUMBER,
4453 p_document_level VARCHAR2,
4454 p_template_id NUMBER DEFAULT NULL)
4455 RETURN VARCHAR2 IS
4456
4457 d_api_name CONSTANT VARCHAR2(30) := 'get_action_type';
4458 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
4459 d_progress NUMBER;
4460
4461 l_effective_from DATE;
4462 l_template_id NUMBER;
4463 l_max_revision NUMBER ;
4464 l_current_revision NUMBER;
4465 l_action_type VARCHAR2(50);
4466
4467 CURSOR c_get_template_ids(p_functional_area VARCHAR2, p_document_type VARCHAR2,
4468 p_document_style_id NUMBER, p_document_level VARCHAR2) is
4469 SELECT template_id, effective_from, revision FROM po_uda_ag_templates
4470 WHERE functional_area = p_functional_area
4471 AND Nvl(document_type,'null') = Nvl(p_document_type, 'null')
4472 AND Nvl(document_style_id, -1) = Nvl(p_document_style_id,-1)
4473 AND document_level = p_document_level
4474 AND revision = (
4475 SELECT max(revision)
4476 FROM po_uda_ag_templates
4477 WHERE functional_area = p_functional_area
4478 AND Nvl(document_type,'null') = Nvl(p_document_type, 'null')
4479 AND Nvl(document_style_id, -1) = Nvl(p_document_style_id,-1)
4480 AND document_level = p_document_level);
4481
4482 BEGIN
4483 IF (PO_LOG.d_proc) THEN
4484 PO_LOG.proc_begin(d_module);
4485 PO_LOG.proc_begin(d_module, 'p_functional_area', p_functional_area);
4486 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
4487 PO_LOG.proc_begin(d_module, 'p_document_style_id', p_document_style_id);
4488 PO_LOG.proc_begin(d_module, 'p_document_level', p_document_level);
4489
4490 END IF;
4491
4492 g_action_template_id(p_functional_area||p_document_type||p_document_style_id||p_document_level) := NULL;
4493
4494 OPEN c_get_template_ids(p_functional_area, p_document_type, p_document_style_id, p_document_level) ;
4495 FETCH c_get_template_ids INTO l_template_id, l_effective_from,l_max_revision;
4496 CLOSE c_get_template_ids;
4497
4498 d_progress := 10;
4499 IF p_template_id IS NOT NULL THEN
4500 SELECT revision INTO l_current_revision FROM po_uda_ag_templates WHERE template_id = p_template_id;
4501 IF l_current_revision < l_max_revision THEN
4502 RETURN NULL;
4503 END IF;
4504 END IF ;
4505
4506
4507 IF PO_LOG.d_stmt THEN
4508 PO_LOG.stmt(d_module, d_progress, 'l_template_id', l_template_id);
4509 PO_LOG.stmt(d_module, d_progress, 'l_effective_from', l_effective_from);
4510 END IF;
4511
4512 IF l_effective_from IS NULL THEN
4513 l_action_type := 'UPDATE';
4514 ELSIF l_effective_from > SYSDATE THEN
4515 l_action_type := 'UPDATE';
4516 ELSIF (l_effective_from <= SYSDATE AND can_update_delete(l_template_id) = 'true') THEN
4517 l_action_type := 'UPDATE';
4518 ELSE
4519 l_action_type := 'CREATE_REVISION';
4520 END IF;
4521
4522 g_action_template_id(p_functional_area||p_document_type||p_document_style_id||p_document_level) := l_template_id;
4523
4524 d_progress := 20;
4525 IF PO_LOG.d_stmt THEN
4526 PO_LOG.stmt(d_module, d_progress, 'l_action_type', l_action_type);
4527 PO_LOG.stmt(d_module, d_progress, 'l_template_id', l_template_id);
4528 END IF;
4529
4530 RETURN l_action_type;
4531
4532 EXCEPTION
4533 WHEN OTHERS THEN
4534 d_progress := 30;
4535 IF PO_LOG.d_stmt THEN
4536 PO_LOG.stmt(d_module, d_progress, 'entered Exception block ');
4537 END IF;
4538 END get_action_type;
4539
4540 FUNCTION get_view_template_id(p_functional_area VARCHAR2,
4541 p_document_type VARCHAR2,
4542 p_document_style_id NUMBER,
4543 p_document_level VARCHAR2)
4544 RETURN NUMBER IS
4545
4546 d_api_name CONSTANT VARCHAR2(30) := 'get_view_template_id';
4547 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
4548 d_progress NUMBER;
4549 l_view_template_id NUMBER;
4550
4551 CURSOR c_get_template_ids(p_functional_area VARCHAR2, p_document_type VARCHAR2,
4552 p_document_style_id NUMBER, p_document_level VARCHAR2) is
4553 SELECT template_id
4554 FROM po_uda_ag_templates
4555 WHERE functional_area = p_functional_area
4556 AND Nvl(document_type, 'null') = Nvl(p_document_type, 'null')
4557 AND Nvl(document_style_id, -1) = Nvl(p_document_style_id, -1)
4558 AND document_level = p_document_level
4559 AND (SYSDATE BETWEEN effective_from AND nvl(effective_to,sysdate+1) );
4560
4561 BEGIN
4562 IF (PO_LOG.d_proc) THEN
4563 PO_LOG.proc_begin(d_module);
4564 PO_LOG.proc_begin(d_module, 'p_functional_area', p_functional_area);
4565 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
4566 PO_LOG.proc_begin(d_module, 'p_document_style_id', p_document_style_id);
4567 PO_LOG.proc_begin(d_module, 'p_document_level', p_document_level);
4568
4569 END IF;
4570
4571 OPEN c_get_template_ids(p_functional_area, p_document_type, p_document_style_id, p_document_level) ;
4572 FETCH c_get_template_ids INTO l_view_template_id;
4573 CLOSE c_get_template_ids;
4574
4575 IF (l_view_template_id IS NULL) THEN
4576 l_view_template_id := get_action_template_id(p_functional_area, p_document_type, p_document_style_id, p_document_level);
4577 END IF;
4578
4579 d_progress := 20;
4580 IF PO_LOG.d_stmt THEN
4581 PO_LOG.stmt(d_module, d_progress, 'l_view_template_id', l_view_template_id);
4582 END IF;
4583
4584 RETURN l_view_template_id;
4585
4586 EXCEPTION
4587 WHEN OTHERS THEN
4588 d_progress := 30;
4589 IF PO_LOG.d_stmt THEN
4590 PO_LOG.stmt(d_module, d_progress, 'entered Exception block ');
4591 END IF;
4592 END get_view_template_id;
4593
4594 FUNCTION get_action_template_id(p_functional_area VARCHAR2,
4595 p_document_type VARCHAR2,
4596 p_document_style_id NUMBER,
4597 p_document_level VARCHAR2)
4598 RETURN NUMBER IS
4599
4600 d_api_name CONSTANT VARCHAR2(30) := 'get_action_template_id';
4601 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
4602 d_progress NUMBER;
4603
4604 l_action_type VARCHAR2(50);
4605 l_action_template_id number;
4606
4607 BEGIN
4608 IF (PO_LOG.d_proc) THEN
4609 PO_LOG.proc_begin(d_module);
4610 PO_LOG.proc_begin(d_module, 'p_functional_area', p_functional_area);
4611 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
4612 PO_LOG.proc_begin(d_module, 'p_document_style_id', p_document_style_id);
4613 PO_LOG.proc_begin(d_module, 'p_document_level', p_document_level);
4614 END IF;
4615
4616 begin
4617 l_action_template_id := g_action_template_id(p_functional_area||p_document_type||p_document_style_id||p_document_level);
4618 exception
4619 when no_data_found then
4620 l_action_template_id := null;
4621 g_action_template_id(p_functional_area||p_document_type||p_document_style_id||p_document_level) := null;
4622 end;
4623
4624 d_progress := 20;
4625 IF PO_LOG.d_stmt THEN
4626 PO_LOG.stmt(d_module, d_progress, 'l_action_template_id', l_action_template_id);
4627 END IF;
4628
4629 IF(l_action_template_id is NULL) THEN
4630 l_action_type := get_action_type(p_functional_area, p_document_type, p_document_style_id, p_document_level);
4631 l_action_template_id := g_action_template_id(p_functional_area||p_document_type||p_document_style_id||p_document_level);
4632 RETURN l_action_template_id;
4633 ELSE
4634 RETURN l_action_template_id;
4635 END IF;
4636
4637 EXCEPTION
4638 WHEN OTHERS THEN
4639 d_progress := 30;
4640 IF PO_LOG.d_stmt THEN
4641 PO_LOG.stmt(d_module, d_progress, 'entered Exception block ');
4642 END IF;
4643
4644 END get_action_template_id;
4645
4646
4647 /** Before deleting usage, we have to delete the association for that usage
4648 * the actions, the action displays and the function mappings.
4649 */
4650 PROCEDURE DELETE_USAGE
4651 ( p_api_version IN NUMBER,
4652 p_usage_id IN NUMBER,
4653 x_return_status OUT NOCOPY VARCHAR2,
4654 x_msg_count OUT NOCOPY NUMBER,
4655 x_msg_data OUT NOCOPY VARCHAR2
4656 ) IS
4657
4658 d_api_name CONSTANT VARCHAR2(30) := 'DELETE_USAGE';
4659 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
4660 d_progress NUMBER;
4661
4662 l_attribute_group_id NUMBER;
4663 l_association_id NUMBER;
4664 l_object_id NUMBER;
4665 l_template_id NUMBER;
4666 l_action_id NUMBER;
4667 l_function_id NUMBER;
4668 l_usage_ag_count NUMBER ;
4669
4670 l_return_status VARCHAR2(2);
4671 l_msg_count NUMBER;
4672 l_msg_data VARCHAR2(3000);
4673 l_errorcode VARCHAR2(30);
4674
4675 CURSOR c_get_association_id(p_usage_id NUMBER) IS
4676 SELECT ptu.association_id, ptu.attribute_group_id, ptu.template_id, umv.object_id
4677 FROM po_uda_ag_template_usages ptu, uda_setup_metadata_v umv
4678 WHERE ptu.usage_id = p_usage_id
4679 AND ptu.template_id = umv.template_id;
4680
4681 CURSOR c_get_action_id(p_object_id NUMBER, p_template_id NUMBER, p_attribute_group_id NUMBER) IS
4682 SELECT action_id, function_id FROM EGO_ACTIONS_B
4683 WHERE object_id = p_object_id
4684 AND classification_code = p_template_id
4685 AND attr_group_id = p_attribute_group_id;
4686
4687 BEGIN
4688 IF (PO_LOG.d_proc) THEN
4689 PO_LOG.proc_begin(d_module);
4690 PO_LOG.proc_begin(d_module, 'p_usage_id', p_usage_id);
4691 END IF;
4692
4693 OPEN c_get_association_id(p_usage_id);
4694 FETCH c_get_association_id INTO l_association_id, l_attribute_group_id, l_template_id, l_object_id;
4695 CLOSE c_get_association_id;
4696
4697 d_progress := 20;
4698 IF PO_LOG.d_stmt THEN
4699 PO_LOG.stmt(d_module, d_progress, 'l_association_id', l_association_id);
4700 PO_LOG.stmt(d_module, d_progress, 'l_attribute_group_id', l_attribute_group_id);
4701 PO_LOG.stmt(d_module, d_progress, 'l_template_id', l_template_id);
4702 PO_LOG.stmt(d_module, d_progress, 'l_object_id', l_object_id);
4703 END IF;
4704
4705
4706 -- Check if any other usage shares the same attr group information.If so , association and actions are not deleted.
4707
4708 SELECT Count(USAGE_ID)
4709 INTO l_usage_ag_count
4710 FROM po_uda_ag_template_usages
4711 WHERE attribute_group_id = l_attribute_group_id
4712 AND template_id = l_template_id ;
4713
4714 IF (l_usage_ag_count = 1 )THEN --no other usage exists with same attribute group .
4715
4716 IF (l_association_id IS NOT NULL) THEN
4717
4718 --deleting assiociations from ego tables.
4719 Delete_Association(
4720 p_api_version => 1.0, -- IN NUMBER
4721 p_association_id => l_association_id, -- IN NUMBER
4722 p_init_msg_list => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
4723 p_commit => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
4724 p_force => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
4725 x_return_status => l_return_status, -- OUT NOCOPY VARCHAR2
4726 x_errorcode => l_errorcode, -- OUT NOCOPY NUMBER
4727 x_msg_count => l_msg_count, -- OUT NOCOPY NUMBER
4728 x_msg_data => l_msg_data); -- OUT NOCOPY VARCHAR2
4729
4730 -- if deleting associations is successful, proceed to delete actions
4731 IF (l_return_status = 'S' ) THEN
4732
4733 FOR c_get_action_id_rec IN c_get_action_id(l_object_id, l_template_id, l_attribute_group_id) LOOP
4734 d_progress := 30;
4735
4736 IF PO_LOG.d_stmt THEN
4737 PO_LOG.stmt(d_module, d_progress, 'Delete Association succesful. x_return_status: ', x_return_status);
4738 PO_LOG.stmt(d_module, d_progress, 'action_id', c_get_action_id_rec.action_id);
4739 PO_LOG.stmt(d_module, d_progress, 'function_id', c_get_action_id_rec.function_id);
4740 END IF;
4741
4742 ego_ext_fwk_pub.Delete_Action (
4743 p_api_version => 1.0
4744 ,p_action_id => c_get_action_id_rec.action_id
4745 ,p_init_msg_list => 'F'
4746 ,p_commit => 'F'
4747 ,x_return_status => l_return_status
4748 ,x_errorcode => l_errorcode, -- OUT NOCOPY NUMBER
4749 x_msg_count => l_msg_count, -- OUT NOCOPY NUMBER
4750 x_msg_data => l_msg_data);
4751
4752 IF (l_return_status = 'S' ) THEN
4753
4754 d_progress := 40;
4755 IF PO_LOG.d_stmt THEN
4756 PO_LOG.stmt(d_module, d_progress, 'Delete Action successful. x_return_status: ', x_return_status);
4757 END IF;
4758
4759 ego_ext_fwk_pub.Delete_Action_Display (
4760 p_api_version => 1.0
4761 ,p_action_id => c_get_action_id_rec.action_id
4762 ,p_init_msg_list => 'F'
4763 ,p_commit => 'F'
4764 ,x_return_status => l_return_status
4765 ,x_errorcode => l_errorcode, -- OUT NOCOPY NUMBER
4766 x_msg_count => l_msg_count, -- OUT NOCOPY NUMBER
4767 x_msg_data => l_msg_data);
4768
4769 IF (l_return_status = 'S' ) THEN
4770
4771 d_progress := 50;
4772 IF PO_LOG.d_stmt THEN
4773 PO_LOG.stmt(d_module, d_progress, 'Delete_Action_Display successful. x_return_status: ', x_return_status);
4774 END IF;
4775
4776 ego_ext_fwk_pub.Delete_Func_Mapping (
4777 p_api_version => 1.0
4778 ,p_function_id => c_get_action_id_rec.function_id
4779 ,p_mapped_obj_type => 'A' -- hardcoded to 'A'
4780 ,p_mapped_obj_pk1_value => c_get_action_id_rec.action_id
4781 ,p_init_msg_list => 'F'
4782 ,p_commit => 'F'
4783 ,x_return_status => l_return_status
4784 ,x_errorcode => l_errorcode -- OUT NOCOPY NUMBER
4785 ,x_msg_count => l_msg_count -- OUT NOCOPY NUMBER
4786 ,x_msg_data => l_msg_data);
4787 END IF;
4788 END IF;
4789 END LOOP;
4790 END IF ;
4791 END IF;
4792 ELSE
4793 l_return_status := 'S';
4794 END IF;
4795
4796 IF l_return_status <> 'S' THEN
4797 ROLLBACK;
4798 IF PO_LOG.d_stmt THEN
4799 PO_LOG.stmt(d_module, d_progress, 'Delete failed. Rollback happened. x_return_status: ', x_return_status);
4800 END IF;
4801 END IF;
4802
4803 x_return_status := l_return_status;
4804
4805 EXCEPTION
4806 WHEN OTHERS THEN
4807 d_progress := 60;
4808 x_return_status := 'U';
4809 IF PO_LOG.d_stmt THEN
4810 PO_LOG.stmt(d_module, d_progress, 'entered Exception block ');
4811 END IF;
4812 END DELETE_USAGE;
4813
4814 PROCEDURE copy_uda_temp_usages(p_api_version IN NUMBER,
4815 p_new_template_id IN NUMBER,
4816 p_src_template_id IN NUMBER,
4817 x_return_status OUT NOCOPY VARCHAR2,
4818 x_msg_count OUT NOCOPY NUMBER,
4819 x_msg_data OUT NOCOPY VARCHAR2)
4820 IS
4821
4822 d_api_name CONSTANT VARCHAR2(30) := 'DELETE_USAGE';
4823 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
4824 d_progress NUMBER;
4825
4826 l_association_id NUMBER;
4827 l_attribute_group_id NUMBER;
4828 l_object_id NUMBER;
4829 l_classification_code NUMBER;
4830 l_data_level_name VARCHAR2(100);
4831 l_usage_id NUMBER;
4832 l_user_id NUMBER;
4833 l_login_id NUMBER;
4834 l_r_association_id NUMBER;
4835
4836
4837 l_return_status VARCHAR2(2);
4838 l_msg_count NUMBER;
4839 l_msg_data VARCHAR2(3000);
4840 l_errorcode VARCHAR2(30);
4841
4842 CURSOR c_get_usage_ids(p_src_template_id NUMBER) IS
4843 SELECT USAGE_ID, ATTRIBUTE_GROUP_ID, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
4844 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
4845 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18, ATTRIBUTE19, ATTRIBUTE20,
4846 ATTRIBUTE_GROUP_SEQUENCE
4847 FROM po_uda_ag_template_usages
4848 WHERE template_id = p_src_template_id;
4849
4850 CURSOR c_get_temp_details(p_src_template_id NUMBER) IS
4851 SELECT object_id, DATA_LEVEL_NAME
4852 FROM uda_setup_metadata_v
4853 WHERE template_id = p_src_template_id;
4854
4855 CURSOR c_gen_association_id IS
4856 SELECT EGO_ASSOCS_S.NEXTVAL
4857 FROM dual;
4858
4859 CURSOR c_genusage_seq IS
4860 SELECT PO_UDA_AG_TEMPLATE_USAGES_S.NEXTVAL
4861 FROM dual;
4862
4863 BEGIN
4864 IF (PO_LOG.d_proc) THEN
4865 PO_LOG.proc_begin(d_module);
4866 PO_LOG.proc_begin(d_module, 'p_new_template_id', p_new_template_id);
4867 PO_LOG.proc_begin(d_module, 'p_src_template_id', p_src_template_id);
4868 END IF;
4869
4870 l_user_id := Fnd_Global.user_id;
4871 l_login_id := Fnd_Global.login_id;
4872
4873 OPEN c_get_temp_details(p_src_template_id);
4874 FETCH c_get_temp_details INTO l_object_id, l_data_level_name;
4875 CLOSE c_get_temp_details;
4876
4877 FOR c_get_usage_ids_rec IN c_get_usage_ids(p_src_template_id) LOOP
4878
4879 SAVEPOINT start_insert_usages;
4880
4881 /** check if the association for this attribute Group already exists if already exists
4882 do not create associations, action or mappings. Just copy the usage.
4883 Else create all the other and then copy the usage. */
4884 l_association_id := ego_ext_fwk_pub.Get_Association_Id_From_PKs(l_object_id
4885 ,p_new_template_id
4886 ,c_get_usage_ids_rec.attribute_group_id);
4887
4888 IF (l_association_id IS NULL) THEN
4889
4890 OPEN c_gen_association_id;
4891 FETCH c_gen_association_id INTO l_association_id;
4892 CLOSE c_gen_association_id;
4893
4894 Create_Association (
4895 p_api_version => 1.0 ,
4896 p_association_id => l_association_id ,
4897 p_object_id => l_object_id ,
4898 p_classification_code => p_new_template_id,
4899 p_data_level => l_data_level_name ,
4900 p_attr_group_id => c_get_usage_ids_rec.attribute_group_id ,
4901 p_enabled_flag => 'Y' ,
4902 p_view_privilege_id => 0, -- ignored for now
4903 p_edit_privilege_id => 0 , -- ignored for now
4904 p_init_msg_list => fnd_api.g_FALSE ,
4905 p_commit => fnd_api.g_FALSE ,
4906 x_association_id => l_r_association_id,
4907 x_return_status => l_return_status ,
4908 x_errorcode => l_errorcode ,
4909 x_msg_count => l_msg_count ,
4910 x_msg_data => l_msg_data);
4911 END IF;
4912 IF (l_return_status = 'S') THEN
4913 copy_uda_actions(p_api_version => 1.0,
4914 p_attr_group_id => c_get_usage_ids_rec.attribute_group_id,
4915 p_new_template_code => p_new_template_id,
4916 p_src_template_id => p_src_template_id,
4917 p_object_id => l_object_id,
4918 x_return_status => l_return_status ,
4919 x_msg_count => l_msg_count ,
4920 x_msg_data => l_msg_data);
4921 END IF;
4922
4923 IF (l_return_status = 'S') THEN
4924
4925 OPEN c_genusage_seq;
4926 FETCH c_genusage_seq INTO l_usage_id;
4927 CLOSE c_genusage_seq;
4928
4929 INSERT INTO po_uda_ag_template_usages (TEMPLATE_ID,
4930 ATTRIBUTE_GROUP_ID ,
4931 ATTRIBUTE_CATEGORY,
4932 ATTRIBUTE1,
4933 ATTRIBUTE2,
4934 ATTRIBUTE3,
4935 ATTRIBUTE4,
4936 ATTRIBUTE5,
4937 ATTRIBUTE6,
4938 ATTRIBUTE7,
4939 ATTRIBUTE8,
4940 ATTRIBUTE9,
4941 ATTRIBUTE10,
4942 ATTRIBUTE11,
4943 ATTRIBUTE12,
4944 ATTRIBUTE13,
4945 ATTRIBUTE14,
4946 ATTRIBUTE15,
4947 ATTRIBUTE16,
4948 ATTRIBUTE17,
4949 ATTRIBUTE18,
4950 ATTRIBUTE19,
4951 ATTRIBUTE20,
4952 ASSOCIATION_ID,
4953 USAGE_ID,
4954 ATTRIBUTE_GROUP_SEQUENCE,
4955 CREATED_BY,
4956 CREATION_DATE,
4957 LAST_UPDATE_DATE,
4958 LAST_UPDATED_BY,
4959 LAST_UPDATE_LOGIN)
4960 VALUES (p_new_template_id,
4961 c_get_usage_ids_rec.ATTRIBUTE_GROUP_ID,
4962 c_get_usage_ids_rec.ATTRIBUTE_CATEGORY,
4963 c_get_usage_ids_rec.ATTRIBUTE1,
4964 c_get_usage_ids_rec.ATTRIBUTE2,
4965 c_get_usage_ids_rec.ATTRIBUTE3,
4966 c_get_usage_ids_rec.ATTRIBUTE4,
4967 c_get_usage_ids_rec.ATTRIBUTE5,
4968 c_get_usage_ids_rec.ATTRIBUTE6,
4969 c_get_usage_ids_rec.ATTRIBUTE7,
4970 c_get_usage_ids_rec.ATTRIBUTE8,
4971 c_get_usage_ids_rec.ATTRIBUTE9,
4972 c_get_usage_ids_rec.ATTRIBUTE10,
4973 c_get_usage_ids_rec.ATTRIBUTE11,
4974 c_get_usage_ids_rec.ATTRIBUTE12,
4975 c_get_usage_ids_rec.ATTRIBUTE13,
4976 c_get_usage_ids_rec.ATTRIBUTE14,
4977 c_get_usage_ids_rec.ATTRIBUTE15,
4978 c_get_usage_ids_rec.ATTRIBUTE16,
4979 c_get_usage_ids_rec.ATTRIBUTE17,
4980 c_get_usage_ids_rec.ATTRIBUTE18,
4981 c_get_usage_ids_rec.ATTRIBUTE19,
4982 c_get_usage_ids_rec.ATTRIBUTE20,
4983 l_r_association_id,
4984 l_usage_id,
4985 c_get_usage_ids_rec.ATTRIBUTE_GROUP_SEQUENCE,
4986 l_user_id,
4987 sysdate,
4988 sysdate,
4989 l_user_id,
4990 l_login_id);
4991
4992 ELSE
4993 ROLLBACK TO SAVEPOINT start_insert_usages;
4994 END IF;
4995 END LOOP;
4996
4997 x_return_status := l_return_status;
4998
4999 /*Commits all the templates headers, usages, associations, actions, action_displays and function_mappings*/
5000
5001 COMMIT;
5002
5003 EXCEPTION
5004 WHEN OTHERS THEN
5005 d_progress := 60;
5006 x_return_status := 'U';
5007 IF PO_LOG.d_stmt THEN
5008 PO_LOG.stmt(d_module, d_progress, 'entered Exception block ');
5009 END IF;
5010
5011 END copy_uda_temp_usages;
5012
5013 PROCEDURE copy_uda_temp_single_usage(p_api_version IN NUMBER,
5014 p_dest_template_id IN NUMBER,
5015 p_src_template_id IN NUMBER,
5016 p_usage_name IN VARCHAR2 ,
5017 x_return_status OUT NOCOPY VARCHAR2,
5018 x_msg_count OUT NOCOPY NUMBER,
5019 x_msg_data OUT NOCOPY VARCHAR2)
5020 IS
5021
5022 d_api_name CONSTANT VARCHAR2(30) := 'COPY_UDA_TEMP_SINGLE_USAGE';
5023 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
5024 d_progress NUMBER;
5025
5026 l_association_id NUMBER;
5027 l_attribute_group_id NUMBER;
5028 l_object_id NUMBER;
5029 l_classification_code NUMBER;
5030 l_data_level_name VARCHAR2(100);
5031 l_usage_id NUMBER;
5032 l_user_id NUMBER;
5033 l_login_id NUMBER;
5034 l_r_association_id NUMBER;
5035 is_copy_existing_usage BOOLEAN := FALSE ;
5036 l_max_attr_grp_seq_num NUMBER ;
5037
5038
5039 l_return_status VARCHAR2(2);
5040 l_msg_count NUMBER;
5041 l_msg_data VARCHAR2(3000);
5042 l_errorcode VARCHAR2(30);
5043
5044 CURSOR c_get_usage_id(p_src_template_id NUMBER,p_usage_name VARCHAR2 ) IS
5045 SELECT USAGE_ID, ATTRIBUTE_GROUP_ID, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
5046 ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
5047 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18, ATTRIBUTE19, ATTRIBUTE20,
5048 ATTRIBUTE_GROUP_SEQUENCE
5049 FROM po_uda_ag_template_usages
5050 WHERE template_id = p_src_template_id
5051 AND ATTRIBUTE_CATEGORY = p_usage_name ;
5052
5053 CURSOR c_get_temp_details(p_src_template_id NUMBER) IS
5054 SELECT object_id, DATA_LEVEL_NAME
5055 FROM uda_setup_metadata_v
5056 WHERE template_id = p_src_template_id;
5057
5058 CURSOR c_gen_association_id IS
5059 SELECT EGO_ASSOCS_S.NEXTVAL
5060 FROM dual;
5061
5062 CURSOR c_genusage_seq IS
5063 SELECT PO_UDA_AG_TEMPLATE_USAGES_S.NEXTVAL
5064 FROM dual;
5065
5066
5067 BEGIN
5068 IF (PO_LOG.d_proc) THEN
5069 PO_LOG.proc_begin(d_module);
5070 PO_LOG.proc_begin(d_module, 'p_dest_template_id', p_dest_template_id);
5071 PO_LOG.proc_begin(d_module, 'p_src_template_id', p_src_template_id);
5072 END IF;
5073
5074 l_user_id := Fnd_Global.user_id;
5075 l_login_id := Fnd_Global.login_id;
5076
5077 OPEN c_get_temp_details(p_src_template_id);
5078 FETCH c_get_temp_details INTO l_object_id, l_data_level_name;
5079 CLOSE c_get_temp_details;
5080
5081 BEGIN
5082 SELECT Max(ATTRIBUTE_GROUP_SEQUENCE)
5083 INTO l_max_attr_grp_seq_num
5084 FROM po_uda_ag_template_usages
5085 WHERE template_id = p_dest_template_id
5086 AND ATTRIBUTE_CATEGORY = p_usage_name ;
5087 EXCEPTION
5088 WHEN No_Data_Found THEN
5089 IF (PO_LOG.d_exc) THEN
5090 PO_LOG.exc(d_module, d_progress, 'No Sequence number found' );
5091 PO_LOG.proc_end(d_module);
5092 END IF;
5093
5094 END;
5095
5096 FOR c_get_usage_id_rec IN c_get_usage_id(p_src_template_id,p_usage_name ) LOOP
5097
5098 SAVEPOINT start_insert_usages;
5099
5100 /** check if the association for this attribute Group already exists if already exists
5101 do not create associations, action or mappings. Just copy the usage.
5102 Else create all the other and then copy the usage. */
5103 l_association_id := ego_ext_fwk_pub.Get_Association_Id_From_PKs(l_object_id
5104 ,p_dest_template_id
5105 ,c_get_usage_id_rec.attribute_group_id);
5106
5107
5108 IF (l_association_id IS NULL) THEN
5109
5110 OPEN c_gen_association_id;
5111 FETCH c_gen_association_id INTO l_association_id;
5112 CLOSE c_gen_association_id;
5113
5114 Create_Association (
5115 p_api_version => 1.0 ,
5116 p_association_id => l_association_id ,
5117 p_object_id => l_object_id ,
5118 p_classification_code => p_dest_template_id,
5119 p_data_level => l_data_level_name ,
5120 p_attr_group_id => c_get_usage_id_rec.attribute_group_id ,
5121 p_enabled_flag => 'Y' ,
5122 p_view_privilege_id => 0, -- ignored for now
5123 p_edit_privilege_id => 0 , -- ignored for now
5124 p_init_msg_list => fnd_api.g_FALSE ,
5125 p_commit => fnd_api.g_FALSE ,
5126 x_association_id => l_r_association_id,
5127 x_return_status => l_return_status ,
5128 x_errorcode => l_errorcode ,
5129 x_msg_count => l_msg_count ,
5130 x_msg_data => l_msg_data);
5131 ELSE
5132 is_copy_existing_usage := TRUE ;
5133 l_r_association_id := l_association_id;
5134
5135 END IF;
5136
5137
5138 IF (l_return_status = 'S') THEN
5139 copy_uda_actions(p_api_version => 1.0,
5140 p_attr_group_id => c_get_usage_id_rec.attribute_group_id,
5141 p_new_template_code => p_dest_template_id,
5142 p_src_template_id => p_src_template_id,
5143 p_object_id => l_object_id,
5144 x_return_status => l_return_status ,
5145 x_msg_count => l_msg_count ,
5146 x_msg_data => l_msg_data);
5147 END IF;
5148
5149 IF (l_return_status = 'S' OR is_copy_existing_usage ) THEN
5150
5151 OPEN c_genusage_seq;
5152 FETCH c_genusage_seq INTO l_usage_id;
5153 CLOSE c_genusage_seq;
5154
5155 IF(is_copy_existing_usage) THEN
5156 l_max_attr_grp_seq_num := l_max_attr_grp_seq_num + 10 ;
5157 ELSE
5158 l_max_attr_grp_seq_num := c_get_usage_id_rec. ATTRIBUTE_GROUP_SEQUENCE;
5159
5160 END IF ;
5161
5162 INSERT INTO po_uda_ag_template_usages (TEMPLATE_ID,
5163 ATTRIBUTE_GROUP_ID ,
5164 ATTRIBUTE_CATEGORY,
5165 ATTRIBUTE1,
5166 ATTRIBUTE2,
5167 ATTRIBUTE3,
5168 ATTRIBUTE4,
5169 ATTRIBUTE5,
5170 ATTRIBUTE6,
5171 ATTRIBUTE7,
5172 ATTRIBUTE8,
5173 ATTRIBUTE9,
5174 ATTRIBUTE10,
5175 ATTRIBUTE11,
5176 ATTRIBUTE12,
5177 ATTRIBUTE13,
5178 ATTRIBUTE14,
5179 ATTRIBUTE15,
5180 ATTRIBUTE16,
5181 ATTRIBUTE17,
5182 ATTRIBUTE18,
5183 ATTRIBUTE19,
5184 ATTRIBUTE20,
5185 ASSOCIATION_ID,
5186 USAGE_ID,
5187 ATTRIBUTE_GROUP_SEQUENCE,
5188 CREATED_BY,
5189 CREATION_DATE,
5190 LAST_UPDATE_DATE,
5191 LAST_UPDATED_BY,
5192 LAST_UPDATE_LOGIN)
5193 VALUES (p_dest_template_id,
5194 c_get_usage_id_rec.ATTRIBUTE_GROUP_ID,
5195 c_get_usage_id_rec.ATTRIBUTE_CATEGORY,
5196 c_get_usage_id_rec.ATTRIBUTE1,
5197 c_get_usage_id_rec.ATTRIBUTE2,
5198 c_get_usage_id_rec.ATTRIBUTE3,
5199 c_get_usage_id_rec.ATTRIBUTE4,
5200 c_get_usage_id_rec.ATTRIBUTE5,
5201 c_get_usage_id_rec.ATTRIBUTE6,
5202 c_get_usage_id_rec.ATTRIBUTE7,
5203 c_get_usage_id_rec.ATTRIBUTE8,
5204 c_get_usage_id_rec.ATTRIBUTE9,
5205 c_get_usage_id_rec.ATTRIBUTE10,
5206 c_get_usage_id_rec.ATTRIBUTE11,
5207 c_get_usage_id_rec.ATTRIBUTE12,
5208 c_get_usage_id_rec.ATTRIBUTE13,
5209 c_get_usage_id_rec.ATTRIBUTE14,
5210 c_get_usage_id_rec.ATTRIBUTE15,
5211 c_get_usage_id_rec.ATTRIBUTE16,
5212 c_get_usage_id_rec.ATTRIBUTE17,
5213 c_get_usage_id_rec.ATTRIBUTE18,
5214 c_get_usage_id_rec.ATTRIBUTE19,
5215 c_get_usage_id_rec.ATTRIBUTE20,
5216 l_r_association_id,
5217 l_usage_id,
5218 l_max_attr_grp_seq_num,
5219 l_user_id,
5220 sysdate,
5221 sysdate,
5222 l_user_id,
5223 l_login_id);
5224
5225 l_return_status := 'S';
5226
5227 ELSE
5228 ROLLBACK TO SAVEPOINT start_insert_usages;
5229 END IF;
5230 END LOOP;
5231
5232 x_return_status := l_return_status;
5233
5234 /*Commits all the templates headers, usages, associations, actions, action_displays and function_mappings*/
5235
5236 COMMIT;
5237
5238 EXCEPTION
5239 WHEN OTHERS THEN
5240 d_progress := 60;
5241 x_return_status := 'U';
5242 IF PO_LOG.d_stmt THEN
5243 PO_LOG.stmt(d_module, d_progress, 'entered Exception block ');
5244 END IF;
5245
5246 END copy_uda_temp_single_usage;
5247
5248 PROCEDURE copy_uda_temp_header(p_api_version IN NUMBER,
5249 p_src_template_id IN NUMBER,
5250 x_new_template_id OUT NOCOPY NUMBER,
5251 x_return_status OUT NOCOPY VARCHAR2,
5252 x_msg_count OUT NOCOPY NUMBER,
5253 x_msg_data OUT NOCOPY VARCHAR2)
5254 IS
5255
5256 d_api_name CONSTANT VARCHAR2(30) := 'DELETE_USAGE';
5257 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
5258 d_progress NUMBER;
5259
5260 l_return_status VARCHAR2(2);
5261 l_msg_count NUMBER;
5262 l_msg_data VARCHAR2(3000);
5263 l_errorcode VARCHAR2(30);
5264 l_user_id NUMBER;
5265 l_login_id NUMBER;
5266 l_new_template_id NUMBER;
5267
5268 CURSOR c_src_template_details(p_src_template_id NUMBER) IS
5269 SELECT b.template_id, b.ENTITY_CODE, b.FUNCTIONAL_AREA, b.DOCUMENT_LEVEL, b.DOCUMENT_TYPE, b.DOCUMENT_STYLE_ID, b.REVISION,
5270 b.SRC_TEMPLATE_ID
5271 FROM po_uda_ag_templates b
5272 WHERE b.template_id = p_src_template_id;
5273
5274 CURSOR c_src_template_tl_details(p_src_template_id NUMBER) IS
5275 SELECT tl.display_name, tl.comments, tl.LANGUAGE, tl.SOURCE_LANG
5276 FROM po_uda_ag_templates_tl tl
5277 WHERE tl.template_id = p_src_template_id;
5278
5279 CURSOR c_gen_template_seq IS
5280 SELECT PO_UDA_AG_TEMPLATES_S.NEXTVAL FROM dual;
5281
5282 BEGIN
5283 IF (PO_LOG.d_proc) THEN
5284 PO_LOG.proc_begin(d_module);
5285 PO_LOG.proc_begin(d_module, 'p_src_template_id', p_src_template_id);
5286 END IF;
5287
5288 l_user_id := Fnd_Global.user_id;
5289 l_login_id := Fnd_Global.login_id;
5290
5291 OPEN c_gen_template_seq;
5292 FETCH c_gen_template_seq INTO x_new_template_id;
5293 CLOSE c_gen_template_seq;
5294
5295 FOR c_src_template_details_rec IN c_src_template_details(p_src_template_id) LOOP
5296
5297 INSERT INTO po_uda_ag_templates (TEMPLATE_ID,
5298 ENTITY_CODE,
5299 FUNCTIONAL_AREA,
5300 DOCUMENT_LEVEL,
5301 DOCUMENT_TYPE,
5302 DOCUMENT_STYLE_ID,
5303 REVISION,
5304 SRC_TEMPLATE_ID,
5305 CREATED_BY,
5306 CREATION_DATE,
5307 LAST_UPDATE_DATE,
5308 LAST_UPDATED_BY,
5309 LAST_UPDATE_LOGIN)
5310 VALUES (x_new_template_id,
5311 c_src_template_details_rec.ENTITY_CODE,
5312 c_src_template_details_rec.FUNCTIONAL_AREA,
5313 c_src_template_details_rec.DOCUMENT_LEVEL,
5314 c_src_template_details_rec.DOCUMENT_TYPE,
5315 c_src_template_details_rec.DOCUMENT_STYLE_ID,
5316 c_src_template_details_rec.REVISION+1,
5317 c_src_template_details_rec.TEMPLATE_ID,
5318 l_user_id,
5319 sysdate,
5320 sysdate,
5321 l_user_id,
5322 l_login_id);
5323 END LOOP;
5324
5325 FOR c_src_template_tl_details_rec IN c_src_template_tl_details(p_src_template_id) LOOP
5326
5327 INSERT INTO po_uda_ag_templates_tl(template_id,
5328 display_name,
5329 comments,
5330 LANGUAGE,
5331 source_lang,
5332 CREATED_BY,
5333 CREATION_DATE,
5334 LAST_UPDATE_DATE,
5335 LAST_UPDATED_BY,
5336 LAST_UPDATE_LOGIN)
5337 VALUES (x_new_template_id,
5338 c_src_template_tl_details_rec.display_name,
5339 c_src_template_tl_details_rec.comments,
5340 c_src_template_tl_details_rec.LANGUAGE,
5341 c_src_template_tl_details_rec.source_lang,
5342 l_user_id,
5343 sysdate,
5344 sysdate,
5345 l_user_id,
5346 l_login_id);
5347 END LOOP;
5348
5349 EXCEPTION
5350 WHEN OTHERS THEN
5351 d_progress := 60;
5352 x_return_status := 'U';
5353 IF PO_LOG.d_stmt THEN
5354 PO_LOG.stmt(d_module, d_progress, 'entered Exception block ');
5355 END IF;
5356 END copy_uda_temp_header;
5357
5358 PROCEDURE validate_usages(p_api_version IN NUMBER,
5359 p_template_id IN NUMBER,
5360 p_attribute_category IN VARCHAR2,
5361 p_attribute_group_id IN NUMBER,
5362 p_sequence IN NUMBER,
5363 p_concatenated_segments IN VARCHAR2,
5364 x_uctxag_yn OUT NOCOPY VARCHAR2,
5365 x_uctxseq_yn OUT NOCOPY VARCHAR2,
5366 x_return_status OUT NOCOPY VARCHAR2,
5367 x_msg_count OUT NOCOPY NUMBER,
5368 x_msg_data OUT NOCOPY VARCHAR2)
5369 IS
5370
5371 d_api_name CONSTANT VARCHAR2(30) := 'validate_usages';
5372 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
5373 d_progress NUMBER;
5374
5375 l_context VARCHAR2(4000) := '';
5376 l_query_usage_ctx_ag_unique VARCHAR2(8000);
5377 l_query_usage_ctx_seq_unique VARCHAR2(8000);
5378 l_uctxag_yn VARCHAR2(1) := 'Y';
5379 l_uctxseq_yn VARCHAR2(1) := 'Y';
5380
5381 CURSOR c_get_usage_ctx_ag_unique(p_template_id NUMBER, p_attribute_category VARCHAR2, p_attribute_group_id NUMBER, p_concatenated_segments VARCHAR2) IS
5382 SELECT 'N' FROM po_uda_ag_template_usages
5383 WHERE template_id = p_template_id
5384 AND attribute_category = p_attribute_category
5385 AND attribute_group_id = p_attribute_group_id
5386 AND attribute1||'.'|| attribute2||'.'||attribute3||'.'|| attribute4||'.'||attribute5||'.'|| attribute6||'.'||attribute7||'.'|| attribute8||
5387 '.'||attribute9||'.'|| attribute10||'.'||attribute11||'.'|| attribute12||'.'||attribute13||'.'|| attribute14||'.'||attribute15||'.'|| attribute16||
5388 '.'||attribute17||'.'|| attribute18||'.'||attribute19 ||'.'||attribute20 ||'.'= p_concatenated_segments;
5389
5390
5391 CURSOR c_get_usage_ctx_seq_unique(p_template_id NUMBER, p_attribute_category VARCHAR2, p_sequence NUMBER, p_concatenated_segments VARCHAR2) IS
5392 SELECT 'N' FROM po_uda_ag_template_usages
5393 WHERE template_id = p_template_id
5394 AND attribute_category = p_attribute_category
5395 AND attribute_group_sequence = p_sequence
5396 AND attribute1||'.'|| attribute2||'.'||attribute3||'.'|| attribute4||'.'||attribute5||'.'|| attribute6||'.'||attribute7||'.'|| attribute8||
5397 '.'||attribute9||'.'|| attribute10||'.'||attribute11||'.'|| attribute12||'.'||attribute13||'.'|| attribute14||'.'||attribute15||'.'|| attribute16||
5398 '.'||attribute17||'.'|| attribute18||'.'||attribute19 ||'.'||attribute20 ||'.' = p_concatenated_segments;
5399
5400 BEGIN
5401 IF (PO_LOG.d_proc) THEN
5402 PO_LOG.proc_begin(d_module);
5403 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
5404 PO_LOG.proc_begin(d_module, 'p_attribute_category', p_attribute_category);
5405 PO_LOG.proc_begin(d_module, 'p_attribute_group_id', p_attribute_group_id);
5406 PO_LOG.proc_begin(d_module, 'p_sequence', p_sequence);
5407 END IF;
5408
5409 OPEN c_get_usage_ctx_ag_unique(p_template_id, p_attribute_category, p_attribute_group_id, p_concatenated_segments);
5410 FETCH c_get_usage_ctx_ag_unique into l_uctxag_yn;
5411 CLOSE c_get_usage_ctx_ag_unique;
5412
5413 OPEN c_get_usage_ctx_seq_unique (p_template_id, p_attribute_category, p_sequence, p_concatenated_segments);
5414 FETCH c_get_usage_ctx_seq_unique into l_uctxseq_yn;
5415 CLOSE c_get_usage_ctx_seq_unique;
5416
5417 x_uctxseq_yn := l_uctxseq_yn;
5418 x_uctxag_yn := l_uctxag_yn;
5419
5420 EXCEPTION
5421 WHEN OTHERS THEN
5422 d_progress := 60;
5423 x_return_status := 'U';
5424 IF PO_LOG.d_stmt THEN
5425 PO_LOG.stmt(d_module, d_progress, 'entered Exception block ');
5426 END IF;
5427
5428 END validate_usages;
5429
5430 PROCEDURE validate_template(p_api_version IN NUMBER,
5431 p_template_id IN NUMBER,
5432 x_return_status OUT NOCOPY VARCHAR2,
5433 x_msg_count OUT NOCOPY NUMBER,
5434 x_msg_data OUT NOCOPY VARCHAR2
5435 )
5436 IS
5437
5438 d_api_name CONSTANT VARCHAR2(30) := 'validate_template';
5439 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
5440 d_progress NUMBER;
5441
5442 l_count NUMBER := 0 ;
5443 l_attr_group_name VARCHAR2(200) := '';
5444 l_usage_display_name VARCHAR2(200):= '' ;
5445
5446 CURSOR c_get_doc_num_context(p_template_id NUMBER, p_attribute_category VARCHAR2) IS
5447 SELECT attribute_category,context,Count(*) l_count
5448 FROM (SELECT attribute_category,
5449 RTrim(get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE1',attribute1) ||'.'||
5450 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE2',attribute2) ||'.'||
5451 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE3',attribute3) ||'.'||
5452 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE4',attribute4) ||'.'||
5453 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE5',attribute5) ||'.'||
5454 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE6',attribute6) ||'.'||
5455 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE7',attribute7) ||'.'||
5456 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE8',attribute8) ||'.'||
5457 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE9',attribute9) ||'.'||
5458 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE10',attribute10) ||'.'||
5459 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE11',attribute11) ||'.'||
5460 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE12',attribute12) ||'.'||
5461 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE13',attribute13) ||'.'||
5462 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE14',attribute14) ||'.'||
5463 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE15',attribute15) ||'.'||
5464 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE16',attribute16) ||'.'||
5465 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE17',attribute17) ||'.'||
5466 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE18',attribute18) ||'.'||
5467 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE19',attribute19) ||'.'||
5468 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE20',attribute20) ||'.','.') AS CONTEXT
5469 FROM
5470 PO_UDA_AG_TEMPLATE_USAGES
5471 WHERE TEMPLATE_ID= p_template_id
5472 AND attribute_category = p_attribute_category)
5473 GROUP BY attribute_category,context;
5474
5475 CURSOR c_get_usage_ctx_seq_unique(p_template_id NUMBER) IS
5476 SELECT attribute_category,attribute_group_sequence,context,Count(*) l_count
5477 FROM (SELECT attribute_category, ATTRIBUTE_GROUP_SEQUENCE,
5478 RTrim(get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE1',attribute1) ||'.'||
5479 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE2',attribute2) ||'.'||
5480 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE3',attribute3) ||'.'||
5481 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE4',attribute4) ||'.'||
5482 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE5',attribute5) ||'.'||
5483 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE6',attribute6) ||'.'||
5484 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE7',attribute7) ||'.'||
5485 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE8',attribute8) ||'.'||
5486 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE9',attribute9) ||'.'||
5487 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE10',attribute10) ||'.'||
5488 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE11',attribute11) ||'.'||
5489 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE12',attribute12) ||'.'||
5490 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE13',attribute13) ||'.'||
5491 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE14',attribute14) ||'.'||
5492 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE15',attribute15) ||'.'||
5493 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE16',attribute16) ||'.'||
5494 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE17',attribute17) ||'.'||
5495 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE18',attribute18) ||'.'||
5496 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE19',attribute19) ||'.'||
5497 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE20',attribute20) ||'.','.') AS CONTEXT
5498 FROM
5499 PO_UDA_AG_TEMPLATE_USAGES
5500 WHERE TEMPLATE_ID= p_template_id )
5501 GROUP BY attribute_category,attribute_group_sequence,context ;
5502
5503
5504 CURSOR c_get_usage_ctx_ag_unique (p_template_id NUMBER,p_attribute_category1 VARCHAR2,p_attribute_category2 VARCHAR2,p_attribute_category3 VARCHAR2 ) IS
5505 SELECT attribute_category,attribute_group_id,context,Count(*) l_count
5506 FROM (SELECT attribute_category, attribute_group_id,
5507 RTrim(get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE1',attribute1) ||'.'||
5508 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE2',attribute2) ||'.'||
5509 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE3',attribute3) ||'.'||
5510 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE4',attribute4) ||'.'||
5511 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE5',attribute5) ||'.'||
5512 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE6',attribute6) ||'.'||
5513 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE7',attribute7) ||'.'||
5514 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE8',attribute8) ||'.'||
5515 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE9',attribute9) ||'.'||
5516 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE10',attribute10) ||'.'||
5517 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE11',attribute11) ||'.'||
5518 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE12',attribute12) ||'.'||
5519 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE13',attribute13) ||'.'||
5520 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE14',attribute14) ||'.'||
5521 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE15',attribute15) ||'.'||
5522 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE16',attribute16) ||'.'||
5523 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE17',attribute17) ||'.'||
5524 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE18',attribute18) ||'.'||
5525 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE19',attribute19) ||'.'||
5526 get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE20',attribute20) ||'.','.') AS CONTEXT
5527 FROM
5528 PO_UDA_AG_TEMPLATE_USAGES
5529 WHERE TEMPLATE_ID= p_template_id
5530 AND attribute_category NOT IN (p_attribute_category1,p_attribute_category2,p_attribute_category3))
5531 GROUP BY attribute_category,attribute_group_id,context ;
5532
5533 BEGIN
5534 IF (PO_LOG.d_proc) THEN
5535 PO_LOG.proc_begin(d_module);
5536 PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
5537 END IF ;
5538
5539 x_msg_count := 0 ;
5540 x_return_status := 'S' ;
5541 fnd_msg_pub.Initialize;
5542
5543 SELECT Count(*)
5544 INTO l_count
5545 FROM po_uda_ag_template_usages
5546 WHERE ATTRIBUTE_CATEGORY = 'ADDRESS'
5547 AND template_id = p_template_id;
5548
5549 IF(l_count > 1) THEN
5550 fnd_message.set_name(d_appln_short_name,'PO_ADDR_ADD_USAGE_INVALID') ;
5551 fnd_msg_pub.ADD;
5552 x_msg_count := x_msg_count +1 ;
5553 x_return_status := 'E' ;
5554 END IF ;
5555
5556 FOR c_get_doc_num_context_rec IN c_get_doc_num_context(p_template_id, 'DOCUMENT_NUMBERING') LOOP
5557
5558 IF(c_get_doc_num_context_rec.l_count > 1 ) THEN
5559 fnd_message.set_name(d_appln_short_name,'PO_DOC_NUM_ADD_USAGE_INVALID') ;
5560 fnd_message.set_token('CONTEXT',c_get_doc_num_context_rec.context);
5561 fnd_msg_pub.ADD;
5562 x_msg_count := x_msg_count +1 ;
5563 x_return_status := 'E' ;
5564 END IF ;
5565
5566 END LOOP ;
5567
5568
5569 FOR c_get_doc_num_context_rec IN c_get_doc_num_context(p_template_id, 'PRICING') LOOP
5570
5571 IF(c_get_doc_num_context_rec.l_count > 1 ) THEN
5572 fnd_message.set_name(d_appln_short_name,'PO_CP_ADD_USAGE_INVALID') ;
5573 fnd_message.set_token('CONTEXT',c_get_doc_num_context_rec.context);
5574 fnd_msg_pub.ADD;
5575 x_msg_count := x_msg_count +1 ;
5576 x_return_status := 'E' ;
5577 END IF ;
5578
5579 END LOOP ;
5580
5581 FOR c_get_usage_ctx_seq_unique_rec IN c_get_usage_ctx_seq_unique(p_template_id ) LOOP
5582
5583 IF(c_get_usage_ctx_seq_unique_rec.l_count > 1 ) THEN
5584 BEGIN
5585 SELECT descriptive_flex_context_name
5586 INTO l_usage_display_name
5587 FROM fnd_descr_flex_contexts_vl
5588 WHERE descriptive_flexfield_name = 'PO_UDA_TEMPLATE_USAGES'
5589 AND descriptive_flex_context_code = c_get_usage_ctx_seq_unique_rec.attribute_category ;
5590
5591 fnd_message.set_name(d_appln_short_name,'PO_UDA_ADD_USAGE_SEQ_INVALID') ;
5592 fnd_message.set_token('CONTEXT',c_get_usage_ctx_seq_unique_rec.context);
5593 fnd_message.set_token('USAGE',l_usage_display_name);
5594 fnd_message.set_token('SEQUENCE',c_get_usage_ctx_seq_unique_rec.attribute_group_sequence);
5595 fnd_msg_pub.ADD;
5596 x_msg_count := x_msg_count +1 ;
5597 x_return_status := 'E' ;
5598 EXCEPTION WHEN No_Data_Found THEN
5599 IF PO_LOG.d_stmt THEN
5600 PO_LOG.stmt(d_module, d_progress, 'no data found ');
5601 END IF;
5602 END ;
5603
5604 END IF ;
5605
5606 END LOOP ;
5607
5608 FOR c_get_usage_ctx_ag_unique_rec IN c_get_usage_ctx_ag_unique(p_template_id,'DOCUMENT_NUMBERING','ADDRESS','PRICING') LOOP
5609
5610 IF(c_get_usage_ctx_ag_unique_rec.l_count > 1 ) THEN
5611
5612 BEGIN
5613 SELECT attr_group_disp_name
5614 INTO l_attr_group_name
5615 FROM ego_attr_groups_v
5616 WHERE attr_group_id = c_get_usage_ctx_ag_unique_rec. attribute_group_id;
5617
5618 SELECT descriptive_flex_context_name
5619 INTO l_usage_display_name
5620 FROM fnd_descr_flex_contexts_vl
5621 WHERE descriptive_flexfield_name = 'PO_UDA_TEMPLATE_USAGES'
5622 AND descriptive_flex_context_code = c_get_usage_ctx_ag_unique_rec.attribute_category;
5623
5624
5625 fnd_message.set_name(d_appln_short_name,'PO_UDA_ADD_USAGE_AG_INVALID') ;
5626 fnd_message.set_token('CONTEXT',c_get_usage_ctx_ag_unique_rec.context);
5627 fnd_message.set_token('USAGE',l_usage_display_name);
5628 fnd_message.set_token('ATTRIBUTEGROUP',l_attr_group_name);
5629 fnd_msg_pub.ADD;
5630 x_msg_count := x_msg_count +1 ;
5631 x_return_status := 'E' ;
5632
5633 EXCEPTION WHEN No_Data_Found THEN
5634 IF PO_LOG.d_stmt THEN
5635 PO_LOG.stmt(d_module, d_progress, 'no data found ');
5636 END IF;
5637 END ;
5638 END IF ;
5639
5640 END LOOP ;
5641
5642 EXCEPTION
5643 WHEN OTHERS THEN
5644 x_return_status := 'U';
5645 IF PO_LOG.d_stmt THEN
5646 PO_LOG.stmt(d_module, d_progress, 'entered Exception block ');
5647 END IF;
5648
5649 END validate_template;
5650
5651 PROCEDURE Create_Association (
5652 p_api_version IN NUMBER
5653 ,p_association_id IN NUMBER DEFAULT NULL
5654 ,p_object_id IN NUMBER
5655 ,p_classification_code IN VARCHAR2
5656 ,p_data_level IN VARCHAR2
5657 ,p_attr_group_id IN NUMBER
5658 ,p_enabled_flag IN VARCHAR2
5659 ,p_view_privilege_id IN NUMBER --ignored for now
5660 ,p_edit_privilege_id IN NUMBER --ignored for now
5661 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
5662 ,p_commit IN VARCHAR2 := fnd_api.g_FALSE
5663 ,x_association_id OUT NOCOPY NUMBER
5664 ,x_return_status OUT NOCOPY VARCHAR2
5665 ,x_errorcode OUT NOCOPY NUMBER
5666 ,x_msg_count OUT NOCOPY NUMBER
5667 ,x_msg_data OUT NOCOPY VARCHAR2
5668 ) IS
5669
5670 d_api_name CONSTANT VARCHAR2(30) := 'Create_Association';
5671 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
5672 d_progress NUMBER;
5673
5674 l_dummy_associaton_id NUMBER;
5675 l_dummy_attr_grp_id NUMBER ;
5676 l_dummy_object_id NUMBER ;
5677 l_dummy_data_level VARCHAR2(50);
5678 l_real_association_id NUMBER ;
5679 l_dummy_classfication_code VARCHAR2(100);
5680
5681
5682
5683 BEGIN
5684
5685 IF (PO_LOG.d_proc) THEN
5686 PO_LOG.proc_begin(d_module);
5687 PO_LOG.proc_begin(d_module, 'p_api_version', p_api_version);
5688 PO_LOG.proc_begin(d_module, 'p_association_id', p_association_id);
5689 PO_LOG.proc_begin(d_module, 'p_object_id', p_object_id);
5690 PO_LOG.proc_begin(d_module, 'p_classification_code', p_classification_code);
5691 PO_LOG.proc_begin(d_module, 'p_data_level', p_data_level);
5692 PO_LOG.proc_begin(d_module, 'p_attr_group_id', p_attr_group_id);
5693 END IF;
5694
5695 --Create association for the real attribute..no chnage in parameters
5696
5697 EGO_EXT_FWK_PUB.Create_Association (
5698 p_api_version => p_api_version ,
5699 p_association_id => p_association_id ,
5700 p_object_id => p_object_id ,
5701 p_classification_code => p_classification_code,
5702 p_data_level => p_data_level ,
5703 p_attr_group_id => p_attr_group_id,
5704 p_enabled_flag => p_enabled_flag ,
5705 p_view_privilege_id => p_view_privilege_id, -- ignored for now
5706 p_edit_privilege_id => p_edit_privilege_id , -- ignored for now
5707 p_init_msg_list => p_init_msg_list ,
5708 p_commit => p_commit ,
5709 x_association_id => x_association_id,
5710 x_return_status => x_return_status ,
5711 x_errorcode => x_errorcode ,
5712 x_msg_count => x_msg_count ,
5713 x_msg_data => x_msg_data);
5714
5715 l_real_association_id := x_association_id;
5716
5717 IF x_return_status <> 'S' THEN
5718 RETURN ;
5719 END IF ;
5720
5721 --Create association for the dummy attributes.
5722
5723 --Get the dummy attribute group id
5724 SELECT attr_group_id
5725 INTO l_dummy_attr_grp_id
5726 FROM ego_attr_groups_v
5727 WHERE attr_group_type='PO_UDA_DUMMY_EXT_ATTRS'
5728 AND attr_group_name = (SELECT attr_group_name FROM ego_attr_groups_v WHERE attr_group_id = p_attr_group_id);
5729
5730 -- Get the dummy attribute association id
5731 SELECT EGO_ASSOCS_S.NEXTVAL
5732 INTO l_dummy_associaton_id
5733 FROM dual;
5734
5735 --Get the dummy object id
5736 SELECT object_id
5737 INTO l_dummy_object_id
5738 FROM fnd_objects
5739 WHERE obj_name = 'PO_UDA_DUMMY_ALL';
5740
5741 --Get the dummy data level id
5742 SELECT data_level_name
5743 INTO l_dummy_data_level
5744 FROM ego_data_level_b
5745 WHERE attr_group_type='PO_UDA_DUMMY_EXT_ATTRS'
5746 AND data_level_name= (SELECT attr_group_type FROM ego_data_level_b WHERE data_level_name= p_data_level);
5747
5748 --Get the dummy classfication code,Revision -to display in the attribute group page
5749 SELECT ptl.display_name || ', Rev ' || pt.revision
5750 INTO l_dummy_classfication_code
5751 FROM po_uda_ag_templates_tl ptl,po_uda_ag_templates pt
5752 WHERE ptl.template_id = pt.template_id
5753 AND ptl.template_id = p_classification_code
5754 AND language = USERENV('LANG');
5755
5756 --Now make a call to EGO API to create association for dummy attribute.
5757
5758 EGO_EXT_FWK_PUB.Create_Association (
5759 p_api_version => p_api_version ,
5760 p_association_id => l_dummy_associaton_id ,
5761 p_object_id => l_dummy_object_id ,
5762 p_classification_code => l_dummy_classfication_code,
5763 p_data_level => l_dummy_data_level ,
5764 p_attr_group_id => l_dummy_attr_grp_id,
5765 p_enabled_flag => p_enabled_flag ,
5766 p_view_privilege_id => p_view_privilege_id, -- ignored for now
5767 p_edit_privilege_id => p_edit_privilege_id , -- ignored for now
5768 p_init_msg_list => p_init_msg_list ,
5769 p_commit => p_commit ,
5770 x_association_id => x_association_id,
5771 x_return_status => x_return_status ,
5772 x_errorcode => x_errorcode ,
5773 x_msg_count => x_msg_count ,
5774 x_msg_data => x_msg_data);
5775
5776
5777 x_association_id := l_real_association_id ; -- The real association id has to be set to out parameter
5778
5779 EXCEPTION
5780 WHEN OTHERS THEN
5781 x_return_status := 'U';
5782 IF PO_LOG.d_stmt THEN
5783 PO_LOG.stmt(d_module, d_progress, 'Entered Exception block ');
5784 END IF;
5785
5786 END Create_Association;
5787
5788
5789 PROCEDURE Delete_Association (
5790 p_api_version IN NUMBER
5791 ,p_association_id IN NUMBER
5792 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
5793 ,p_commit IN VARCHAR2 := fnd_api.g_FALSE
5794 ,p_force IN VARCHAR2 := fnd_api.g_FALSE
5795 ,x_return_status OUT NOCOPY VARCHAR2
5796 ,x_errorcode OUT NOCOPY NUMBER
5797 ,x_msg_count OUT NOCOPY NUMBER
5798 ,x_msg_data OUT NOCOPY VARCHAR2
5799 ) IS
5800
5801 d_api_name CONSTANT VARCHAR2(30) := 'Delete_Association';
5802 d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
5803 d_progress NUMBER;
5804
5805 l_dummy_associaton_id NUMBER;
5806 l_template_name VARCHAR2(100) ;
5807 l_template_id VARCHAR2(50);
5808 l_dummy_attr_grp_id NUMBER ;
5809 l_real_attr_grp_id NUMBER ;
5810 l_dummy_data_level VARCHAR2(100);
5811 l_real_data_level varchar2(100);
5812
5813 BEGIN
5814
5815 IF (PO_LOG.d_proc) THEN
5816 PO_LOG.proc_begin(d_module);
5817 PO_LOG.proc_begin(d_module, 'p_association_id', p_association_id);
5818 END IF ;
5819
5820 --Get the real attribute group id
5821 SELECT attr_group_id,classification_code
5822 INTO l_real_attr_grp_id,l_template_id
5823 FROM ego_obj_ag_assocs_b
5824 WHERE association_id = p_association_id;
5825
5826 --Get the real data level id
5827 SELECT data_level
5828 INTO l_real_data_level
5829 FROM ego_obj_ag_assocs_b
5830 WHERE classification_code = l_template_id
5831 AND attr_group_id = l_real_attr_grp_id;
5832
5833 --delete association for the real attribute..no chnage in parameters
5834
5835 ego_ext_fwk_pub.Delete_Association(
5836 p_api_version => 1.0, -- IN NUMBER
5837 p_association_id => p_association_id, -- IN NUMBER
5838 p_init_msg_list => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
5839 p_commit => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
5840 p_force => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
5841 x_return_status => x_return_status, -- OUT NOCOPY VARCHAR2
5842 x_errorcode => x_errorcode, -- OUT NOCOPY NUMBER
5843 x_msg_count => x_msg_count, -- OUT NOCOPY NUMBER
5844 x_msg_data => x_msg_data); -- OUT NOCOPY VARCHAR2
5845
5846
5847 IF x_return_status <> 'S' THEN
5848 RETURN ;
5849 END IF ;
5850
5851 --Delete association for the dummy attribute with dummy association id.
5852
5853 --Get the dummy attr grp id
5854 SELECT attr_group_id
5855 INTO l_dummy_attr_grp_id
5856 FROM ego_attr_groups_v
5857 WHERE attr_group_type='PO_UDA_DUMMY_EXT_ATTRS'
5858 AND attr_group_name = (SELECT attr_group_name FROM ego_attr_groups_v WHERE attr_group_id = l_real_attr_grp_id);
5859
5860 --Get the dummy classification code
5861 SELECT ptl.display_name || ', Rev ' || pt.revision
5862 INTO l_template_name
5863 FROM po_uda_ag_templates_tl ptl,po_uda_ag_templates pt
5864 WHERE ptl.template_id = pt.template_id
5865 AND ptl.template_id = l_template_id
5866 AND language = USERENV('LANG');
5867
5868 --Get the dummy data level id
5869 SELECT data_level_name
5870 INTO l_dummy_data_level
5871 FROM ego_data_level_b
5872 WHERE attr_group_type='PO_UDA_DUMMY_EXT_ATTRS'
5873 AND data_level_name= (SELECT attr_group_type FROM ego_data_level_b WHERE data_level_name= l_real_data_level);
5874
5875 --Get the dummy association id.
5876 SELECT association_id
5877 INTO l_dummy_associaton_id
5878 FROM ego_obj_ag_assocs_b
5879 WHERE attr_group_id = l_dummy_attr_grp_id AND classification_code = l_template_name AND data_level= l_dummy_data_level;
5880
5881 ego_ext_fwk_pub.Delete_Association(
5882 p_api_version => 1.0, -- IN NUMBER
5883 p_association_id => l_dummy_associaton_id, -- IN NUMBER
5884 p_init_msg_list => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
5885 p_commit => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
5886 p_force => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
5887 x_return_status => x_return_status, -- OUT NOCOPY VARCHAR2
5888 x_errorcode => x_errorcode, -- OUT NOCOPY NUMBER
5889 x_msg_count => x_msg_count, -- OUT NOCOPY NUMBER
5890 x_msg_data => x_msg_data); -- OUT NOCOPY VARCHAR2
5891
5892 IF (PO_LOG.d_exc) THEN
5893 PO_LOG.exc(d_module, d_progress, x_return_status );
5894 PO_LOG.proc_end(d_module);
5895 END IF;
5896
5897 EXCEPTION
5898 WHEN OTHERS THEN
5899 x_return_status := 'U';
5900 IF PO_LOG.d_stmt THEN
5901 PO_LOG.stmt(d_module, d_progress, 'Entered Exception block ');
5902 END IF;
5903 END Delete_Association;
5904
5905
5906 END PO_UDA_TEMPLATES_UTIL;