[Home] [Help]
PACKAGE BODY: APPS.FEM_TABLE_REGISTRATION_PKG
Source
1 PACKAGE BODY fem_table_registration_pkg AS
2 /* $Header: FEMTABREGB.pls 120.6.12000000.3 2007/09/24 09:32:50 asadadek ship $ */
3
4 G_PLSQL_COMPILATION_ERROR exception;
5 pragma exception_init(G_PLSQL_COMPILATION_ERROR,-942);
6
7 PROCEDURE synchronize(p_api_version IN NUMBER,
8 p_init_msg_list IN VARCHAR2,
9 p_commit IN VARCHAR2,
10 p_encoded IN VARCHAR2,
11 p_table_name IN VARCHAR2,
12 p_synchronize_flag OUT NOCOPY VARCHAR2,
13 x_msg_count OUT NOCOPY NUMBER,
14 x_msg_data OUT NOCOPY VARCHAR2,
15 x_return_status OUT NOCOPY VARCHAR2)
16 IS
17
18 l_api_version NUMBER;
19 l_init_msg_list VARCHAR2(1);
20 l_commit VARCHAR2(1);
21 l_encoded VARCHAR2(1);
22 l_owner VARCHAR2(30);
23 dummy NUMBER;
24
25 l_delete_error EXCEPTION;
26 l_owner_error EXCEPTION;
27 l_insert_error EXCEPTION;
28
29 TYPE col_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
30 TYPE col_prop_code_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
31
32 l_column_tab col_tab;
33 l_col_prop_code_tab col_prop_code_tab;
34
35 i NUMBER;
36
37 l_user_id NUMBER;
38 l_login_id NUMBER;
39
40 l_pk_col NUMBER :=0;
41 l_pk_msg_count NUMBER;
42 l_pk_msg_data VARCHAR2(240);
43 l_pk_return_status VARCHAR2(1);
44
45 l_schema_return_status VARCHAR2(1);
46 l_schema_msg_count NUMBER(20);
47 l_schema_msg_data VARCHAR2(240);
48 l_schema_tab_name VARCHAR2(240);
49
50 l_object_type VARCHAR2(10);
51 l_apps VARCHAR2(30):=USER;
52
53 BEGIN
54
55 x_return_status := c_success;
56 p_synchronize_flag := 'N';
57
58 l_user_id := Fnd_Global.User_Id;
59 l_login_id := Fnd_Global.Login_Id;
60
61 fem_engines_pkg.tech_message (p_severity => g_log_level_2
62 ,p_module => g_block||'.synchronize'
63 ,p_msg_text => 'BEGIN');
64
65 l_api_version := NVL(p_api_version, c_api_version);
66 l_init_msg_list := NVL(p_init_msg_list, c_false);
67 l_commit := NVL(p_commit, c_false);
68 l_encoded := NVL(p_encoded, c_true);
69 dummy := 0;
70
71 BEGIN
72
73 l_object_type := get_object_type(p_table_name);
74
75 IF l_object_type = 'FEM_TABLE' THEN
76
77 fem_database_util_pkg.get_table_owner
78 (x_return_status => l_schema_return_status,
79 x_msg_count => l_schema_msg_count,
80 x_msg_data => l_schema_msg_data,
81 p_syn_name => p_table_name,
82 x_tab_name => l_schema_tab_name,
83 x_tab_owner => l_owner
84 );
85
86 ELSE
87
88 l_owner := l_apps;--For veiws owner will be APPS
89
90 END IF;
91
92 EXCEPTION
93 WHEN OTHERS THEN
94 RAISE l_owner_error;
95 END;
96
97 fem_engines_pkg.tech_message (p_severity => g_log_level_1
98 ,p_module => g_block||'.synchronize'
99 ,p_msg_text => 'After fetching the owner, owner = ' || l_owner);
100 BEGIN
101 SELECT 1
102 INTO dummy
103 FROM fem_tab_columns_b ftc
104 WHERE ftc.column_name NOT IN
105 (SELECT column_name
106 FROM dba_tab_columns dtc
107 WHERE dtc.owner = l_owner
108 AND dtc.table_name=p_table_name)
109 AND ftc.table_name=p_table_name
110 AND ROWNUM = 1;
111
112 IF dummy = 1 THEN
113 /* Changes have occurred so must flag the table status to "Incomplete" */
114 p_synchronize_flag := 'Y';
115
116 DELETE FROM fem_tab_columns_b
117 WHERE column_name NOT IN ( SELECT column_name
118 FROM dba_tab_columns
119 WHERE table_name = p_table_name
120 AND owner = l_owner )
121 AND table_name = p_table_name
122 RETURNING column_name BULK COLLECT INTO l_column_tab;
123
124 FORALL i IN l_column_tab.FIRST..l_column_tab.LAST
125 DELETE FROM fem_tab_columns_tl
126 WHERE table_name = p_table_name
127 AND column_name = l_column_tab(i);
128
129 FOR i IN l_column_tab.FIRST..l_column_tab.LAST LOOP
130 DELETE FROM fem_tab_column_prop
131 WHERE table_name =p_table_name
132 AND column_name = l_column_tab(i)
133 RETURNING column_property_code BULK COLLECT INTO l_col_prop_code_tab ;
134 END LOOP;
135
136 IF l_col_prop_code_tab.exists(1) THEN
137 FOR j IN l_col_prop_code_tab.FIRST .. l_col_prop_code_tab.LAST
138 LOOP
139 IF 'PROCESSING_KEY' = l_col_prop_code_tab(j) AND l_pk_col = 0 THEN
140 DELETE FROM fem_tab_column_prop
141 WHERE table_name = p_table_name
142 AND column_property_code = 'PROCESSING_KEY';
143
144 UPDATE fem_tables_b
145 SET proc_key_index_name = NULL
146 WHERE table_name = p_table_name;
147
148 raise_proc_key_update_event(p_table_name,
149 l_pk_msg_count,
150 l_pk_msg_data,
151 l_pk_return_status);
152 l_pk_col := 1;
153
154 END IF; -- 'PROCESSING_KEY'
155
156 END LOOP;
157
158 END IF; -- l_col_prop_code_tab.exists(1)
159
160 END IF; -- dummy = 1
161
162 EXCEPTION
163 WHEN NO_DATA_FOUND THEN
164 NULL;
165
166 WHEN OTHERS THEN
167 RAISE l_delete_error;
168 END;
169
170 fem_engines_pkg.tech_message (p_severity => g_log_level_1
171 ,p_module => g_block||'.synchronize'
172 ,p_msg_text => 'After deleting columns in fem_tab_columns but not in dba_tab_columns');
173
174 BEGIN
175
176 INSERT INTO fem_tab_columns_vl
177 (table_name,
178 column_name,
179 display_name,
180 description,
181 fem_data_type_code,
182 dimension_id,
183 uom_column_name,
184 enabled_flag,
185 creation_date,
186 created_by,
187 last_update_date,
188 last_updated_by,
189 last_update_login,
190 object_version_number
191 )
192 SELECT
193 p_table_name,
194 dtc.column_name,
195 NVL(display_name, dtc.column_name) display_name,
196 NVL(description,dtc.column_name) description,
197 nvl(fcr.fem_data_type_code,dtc.data_type) as cpm_datatype,
198 DECODE(fcr.restricted_flag,'N',TO_NUMBER(NULL),fcr.dimension_id) dimension_id,
199 DECODE(fcr.restricted_flag,'N',TO_NUMBER(NULL),fcr.uom_column_name) uom_column_name,
200 DECODE(dtc.nullable,'N','Y','Y','N') enabled_flag,
201 SYSDATE,
202 l_user_id,
203 SYSDATE,
204 l_user_id,
205 l_login_id,
206 1
207 FROM dba_tab_columns dtc,
208 fem_column_requiremnt_vl fcr
209 WHERE dtc.table_name = p_table_name
210 AND dtc.owner = l_owner
211 AND dtc.column_name = fcr.column_name
212 AND NOT EXISTS ( SELECT 1
213 FROM fem_tab_columns_b
214 WHERE table_name = p_table_name
215 AND column_name = fcr.column_name );
216
217 IF p_synchronize_flag <> 'Y' THEN
218 IF SQL%ROWCOUNT > 0 THEN
219 p_synchronize_flag := 'Y';
220 END IF;
221 END IF;
222
223 fem_engines_pkg.tech_message (p_severity => g_log_level_1
224 ,p_module => g_block||'.synchronize'
225 ,p_msg_text => 'Populating columns with data in tab columns');
226
227 INSERT INTO fem_tab_columns_vl
228 (table_name,
229 column_name,
230 display_name,
231 description,
232 fem_data_type_code,
233 dimension_id,
234 uom_column_name,
235 enabled_flag,
236 creation_date,
237 created_by,
238 last_update_date,
239 last_updated_by,
240 last_update_login,
241 object_version_number
242 )
243 SELECT
244 p_table_name,
245 dump.column_name,
246 nvl(display_name,dump.column_name) display_name,
247 nvl(description,dump.column_name) description,
248 nvl(ftc.fem_data_type_code,dump.data_type) as cpm_datatype,
249 ftc.dimension_id,
250 ftc.uom_column_name,
251 DECODE(dump.nullable,'N','Y','Y','N') enabled_flag,
252 SYSDATE,
253 l_user_id,
254 SYSDATE,
255 l_user_id,
256 l_login_id,
257 1
258 FROM fem_tab_columns_vl ftc,
259 (
260 SELECT dtc.column_name, dtc.table_name, dtc.nullable, (SELECT table_name tname
261 FROM fem_tab_columns_b
262 WHERE column_name = dtc.column_name AND rownum = 1) tname,
263 data_type
264 FROM dba_tab_columns dtc
265 WHERE dtc.table_name = p_table_name
266 AND dtc.owner = l_owner
267 AND NOT EXISTS ( SELECT 1
268 FROM fem_tab_columns_b fcr
269 WHERE fcr.column_name = dtc.column_name
270 AND fcr.table_name = p_table_name )) dump
271 WHERE ftc.column_name = dump.column_name
272 AND ftc.table_name = dump.tname;
273
274 IF p_synchronize_flag <> 'Y' THEN
275 IF SQL%ROWCOUNT > 0 THEN
276 p_synchronize_flag := 'Y';
277 END IF;
278 END IF;
279
280 fem_engines_pkg.tech_message (p_severity => g_log_level_1
281 ,p_module => g_block||'.synchronize'
282 ,p_msg_text => 'Populating columns with data in dba tab columns');
283
284 INSERT INTO fem_tab_columns_vl
285 (table_name,
286 column_name,
287 display_name,
288 description,
289 fem_data_type_code,
290 dimension_id,
291 uom_column_name,
292 enabled_flag,
293 creation_date,
294 created_by,
295 last_update_date,
296 last_updated_by,
297 last_update_login,
298 object_version_number
299 )
300 SELECT
301 table_name,
302 column_name,
303 column_name,
304 column_name,
305 data_type,
306 TO_NUMBER(NULL),
307 NULL,
308 DECODE(atc.nullable,'N','Y','Y','N') enabled_flag,
309 SYSDATE,
310 l_user_id,
311 SYSDATE,
312 l_user_id,
313 l_login_id,
314 1
315 FROM dba_tab_columns atc
316 WHERE table_name = p_table_name
317 AND atc.owner = l_owner
318 AND NOT EXISTS ( SELECT 1
319 FROM fem_tab_columns_b ftc
320 WHERE column_name = atc.column_name
321 AND table_name = p_table_name );
322
323 IF p_synchronize_flag <> 'Y' THEN
324 IF SQL%ROWCOUNT > 0 THEN
325 p_synchronize_flag := 'Y';
326 END IF;
327 END IF;
328 fem_engines_pkg.tech_message (p_severity => g_log_level_1
329 ,p_module => g_block||'.synchronize'
330 ,p_msg_text => 'After populating the VL');
331
332 EXCEPTION
333 WHEN OTHERS THEN
334 RAISE l_insert_error;
335 END;
336
337 fem_engines_pkg.tech_message (p_severity => g_log_level_2
338 ,p_module => g_block||'.synchronize'
339 ,p_msg_text => 'END');
340
341
342 EXCEPTION
343
344 WHEN l_owner_error THEN
345 x_return_status := c_error;
346
347 fem_engines_pkg.tech_message (p_severity => g_log_level_5
348 ,p_module => g_block||'.synchronize'
349 ,p_msg_text => 'synchronize: Trying to get owner info. for' || p_table_name);
350
351 fem_engines_pkg.tech_message (p_severity => g_log_level_5
352 ,p_module => g_block||'.synchronize'
353 ,p_msg_text => 'synchronize: error = ' || SQLERRM);
354
355 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
356 p_count => x_msg_count,
357 p_data => x_msg_data);
358
359 WHEN l_delete_error THEN
360 x_return_status := c_error;
361
362 fem_engines_pkg.tech_message (p_severity => g_log_level_5
363 ,p_module => g_block||'.synchronize'
364 ,p_msg_text => 'synchronize: Trying to delete from fem_tab_columns - ' || p_table_name);
365
366 fem_engines_pkg.tech_message (p_severity => g_log_level_5
367 ,p_module => g_block||'.synchronize'
368 ,p_msg_text => 'synchronize: error = ' || SQLERRM);
369
370 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
371 p_count => x_msg_count,
372 p_data => x_msg_data);
373
374 WHEN l_insert_error THEN
375 x_return_status := c_error;
376
377 fem_engines_pkg.tech_message (p_severity => g_log_level_5
378 ,p_module => g_block||'.synchronize'
379 ,p_msg_text => 'synchronize: Trying to insert for' || p_table_name);
380
381 fem_engines_pkg.tech_message (p_severity => g_log_level_5
382 ,p_module => g_block||'.synchronize'
383 ,p_msg_text => 'synchronize: error = ' || SQLERRM);
384
385 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
386 p_count => x_msg_count,
390 WHEN OTHERS THEN
387 p_data => x_msg_data);
388
389
391 x_return_status := c_error;
392
393 fem_engines_pkg.tech_message (p_severity => g_log_level_5
394 ,p_module => g_block||'.init'
395 ,p_msg_text => 'synchronize: General_Exception');
396
397 fem_engines_pkg.tech_message (p_severity => g_log_level_5
398 ,p_module => g_block||'.synchronize'
399 ,p_msg_text => 'synchronize: error = ' || SQLERRM);
400
401 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
402 p_count => x_msg_count,
403 p_data => x_msg_data);
404
405
406 END synchronize;
407
408 PROCEDURE unregister(p_api_version IN NUMBER,
409 p_init_msg_list IN VARCHAR2,
410 p_commit IN VARCHAR2,
411 p_encoded IN VARCHAR2,
412 p_table_name IN VARCHAR2,
413 x_msg_count OUT NOCOPY NUMBER,
414 x_msg_data OUT NOCOPY VARCHAR2,
415 x_return_status OUT NOCOPY VARCHAR2)
416 IS
417
418 l_api_version NUMBER;
419 l_init_msg_list VARCHAR2(1);
420 l_commit VARCHAR2(1);
421 l_encoded VARCHAR2(1);
422
423 l_di_view_name VARCHAR2(30);
424
425 BEGIN
426
427 x_return_status := c_success;
428
429 fem_engines_pkg.tech_message (p_severity => g_log_level_2
430 ,p_module => g_block||'.unregister'
431 ,p_msg_text => 'BEGIN');
432
433 l_api_version := NVL(p_api_version, c_api_version);
434 l_init_msg_list := NVL(p_init_msg_list, c_false);
435 l_commit := NVL(p_commit, c_false);
436 l_encoded := NVL(p_encoded, c_true);
437
438 -- To get di view name for tables and delete the system generated view for p_table_name
439 BEGIN
440
441 SELECT di_view_name
442 INTO l_di_view_name
443 FROM fem_tables_vl
444 WHERE table_name = p_table_name;
445
446 EXCEPTION
447
448 WHEN OTHERS THEN
449
450 l_di_view_name := null;
451
452 END;
453
454 DELETE FROM fem_tab_columns_vl
455 WHERE table_name = p_table_name;
456
457 DELETE FROM fem_tables_vl
458 WHERE table_name = p_table_name;
459
460 DELETE FROM fem_tab_column_prop
461 WHERE table_name = p_table_name;
462
463 DELETE FROM fem_table_class_assignmt
464 WHERE table_name = p_table_name;
465
466 IF l_di_view_name is NOT NULL THEN
467
468 BEGIN
469
470 DELETE FROM FEM_SVIEW_COLUMNS WHERE view_name = l_di_view_name;
471
472 EXECUTE IMMEDIATE 'DROP VIEW '||l_di_view_name;
473
474 EXCEPTION
475
476 WHEN G_PLSQL_COMPILATION_ERROR THEN
477
478 NULL;
479
480 END;
481
482 END IF;
483
484 fem_engines_pkg.tech_message (p_severity => g_log_level_2
485 ,p_module => g_block||'.unregister'
486 ,p_msg_text => 'END');
487
488 EXCEPTION
489 WHEN OTHERS THEN
490 x_return_status := c_error;
491
492 fem_engines_pkg.tech_message (p_severity => g_log_level_5
493 ,p_module => g_block||'.init'
494 ,p_msg_text => 'unregister: General_Exception');
495
496 fem_engines_pkg.tech_message (p_severity => g_log_level_5
497 ,p_module => g_block||'.synchronize'
498 ,p_msg_text => 'unregsiter: error = ' || SQLERRM);
499
500 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
501 p_count => x_msg_count,
502 p_data => x_msg_data);
503
504
505 END unregister;
506
507 PROCEDURE init(p_api_version IN NUMBER,
508 p_init_msg_list IN VARCHAR2,
509 p_commit IN VARCHAR2,
510 p_encoded IN VARCHAR2,
511 x_msg_count OUT NOCOPY NUMBER,
512 x_msg_data OUT NOCOPY VARCHAR2,
513 x_return_status OUT NOCOPY VARCHAR2)
514
515 IS
516
517 l_cursor INTEGER;
518 l_rows_processed INTEGER;
519
520 l_api_version NUMBER;
521 l_init_msg_list VARCHAR2(1);
522 l_commit VARCHAR2(1);
523 l_encoded VARCHAR2(1);
524
525
526 BEGIN
527
528 x_return_status := c_success;
529
530 fem_engines_pkg.tech_message (p_severity => g_log_level_2
531 ,p_module => g_block||'.init'
535 l_init_msg_list := NVL(p_init_msg_list, c_false);
532 ,p_msg_text => 'BEGIN');
533
534 l_api_version := NVL(p_api_version, c_api_version);
536 l_commit := NVL(p_commit, c_false);
537 l_encoded := NVL(p_encoded, c_true);
538
539 /*
540 l_cursor := dbms_sql.open_cursor;
541 dbms_sql.parse(l_cursor, 'ALTER SESSION ENABLE PARALLEL DML', dbms_sql.native);
542 l_rows_processed := dbms_sql.execute(l_cursor);
543 dbms_sql.close_cursor(l_cursor);
544 */
545
546 fem_engines_pkg.tech_message (p_severity => g_log_level_2
547 ,p_module => g_block||'.init'
548 ,p_msg_text => 'END');
549
550
551 EXCEPTION
552 WHEN OTHERS THEN
553 x_return_status := c_error;
554
555 fem_engines_pkg.tech_message (p_severity => g_log_level_5
556 ,p_module => g_block||'.init'
557 ,p_msg_text => 'init: General_Exception');
558
559 fem_engines_pkg.tech_message (p_severity => g_log_level_5
560 ,p_module => g_block||'.synchronize'
561 ,p_msg_text => 'init: error = ' || SQLERRM);
562
563 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
564 p_count => x_msg_count,
565 p_data => x_msg_data);
566
567 END init;
568
569 PROCEDURE validateClass(p_api_version IN NUMBER,
570 p_init_msg_list IN VARCHAR2,
571 p_commit IN VARCHAR2,
572 p_encoded IN VARCHAR2,
573 p_table_name IN VARCHAR2,
574 x_msg_count OUT NOCOPY NUMBER,
575 x_msg_data OUT NOCOPY VARCHAR2,
576 x_return_status OUT NOCOPY VARCHAR2)
577 IS
578 l_api_version NUMBER;
579 l_init_msg_list VARCHAR2(1);
580 l_commit VARCHAR2(1);
581 l_encoded VARCHAR2(1);
582
583
584 BEGIN
585 x_return_status := c_success;
586 fem_engines_pkg.tech_message (p_severity => g_log_level_2
587 ,p_module => g_block||'.validate'
588 ,p_msg_text => 'BEGIN');
589
590 l_api_version := NVL(p_api_version, c_api_version);
591 l_init_msg_list := NVL(p_init_msg_list, c_false);
592 l_commit := NVL(p_commit, c_false);
593 l_encoded := NVL(p_encoded, c_true);
594
595 DELETE FROM fem_table_class_assignmt ftca
596 WHERE EXISTS ( SELECT table_classification_code
597 FROM fem_tab_class_errors_gt ftce
598 WHERE ftce.table_classification_code = ftca.table_classification_code
599 AND table_name = p_table_name
600 AND ROWNUM = 1 )
601 AND table_name = p_table_name;
602
603 fem_engines_pkg.tech_message (p_severity => g_log_level_2
604 ,p_module => g_block||'.validate'
605 ,p_msg_text => 'END');
606 EXCEPTION
607 WHEN OTHERS THEN
608 x_return_status := c_error;
609
610 fem_engines_pkg.tech_message (p_severity => g_log_level_5
611 ,p_module => g_block||'.init'
612 ,p_msg_text => 'validate: General_Exception');
613
614 fem_engines_pkg.tech_message (p_severity => g_log_level_5
615 ,p_module => g_block||'.synchronize'
616 ,p_msg_text => 'validate: error = ' || SQLERRM);
617
618 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
619 p_count => x_msg_count,
620 p_data => x_msg_data);
621 END validateClass;
622
623 PROCEDURE populate_tab_col_gt(p_api_version IN NUMBER default c_api_version,
624 p_init_msg_list IN VARCHAR2 default c_false,
625 p_commit IN VARCHAR2 default c_false,
626 p_encoded IN VARCHAR2 default c_true,
627 p_mode IN VARCHAR2,
628 p_owner IN VARCHAR2,
629 p_table_name IN VARCHAR2,
630 x_msg_count OUT NOCOPY NUMBER,
631 x_msg_data OUT NOCOPY VARCHAR2,
632 x_return_status OUT NOCOPY VARCHAR2)
633
634 IS
635
636 l_api_version NUMBER;
637 l_init_msg_list VARCHAR2(1);
638 l_commit VARCHAR2(1);
639 l_encoded VARCHAR2(1);
640 l_owner VARCHAR2(30):=p_owner;
641
642 l_schema_return_status VARCHAR2(1);
643 l_schema_msg_count NUMBER(20);
644 l_schema_msg_data VARCHAR2(240);
645 l_schema_tab_name VARCHAR2(240);
649 BEGIN
646
647 l_owner_error EXCEPTION;
648
650
651 x_return_status := c_success;
652
653 fem_engines_pkg.tech_message (p_severity => g_log_level_2
654 ,p_module => g_block||'.populate_tab_col_gt'
655 ,p_msg_text => 'BEGIN');
656
657 l_api_version := NVL(p_api_version, c_api_version);
658 l_init_msg_list := NVL(p_init_msg_list, c_false);
659 l_commit := NVL(p_commit, c_false);
660 l_encoded := NVL(p_encoded, c_true);
661
662 fem_engines_pkg.tech_message (p_severity => g_log_level_1
663 ,p_module => g_block||'.populate_tab_col_gt'
664 ,p_msg_text => 'Populating columns with data in requiremnt');
665
666 IF p_mode = 'CREATE' THEN
667
668 INSERT INTO fem_tab_columns_gt
669 (table_name,
670 column_name,
671 display_name,
672 description,
673 data_type,
674 data_length,
675 data_precision,
676 cpm_datatype,
677 dimension_id,
678 dimension_name,
679 uom_column_name,
680 uom_col_display_name,
681 selected,
682 disable_flag,
683 cpm_switcher,
684 dim_switcher,
685 uom_switcher,
686 enabled_flag,
687 restricted_flag,
688 update_flag ,
689 object_version_number
690 )
691 SELECT
692 dtc.table_name,
693 dtc.column_name,
694 NVL(display_name, dtc.column_name) display_name,
695 NVL(description,dtc.column_name) description,
696 dtc.data_type,
697 dtc.data_length,
698 dtc.data_precision,
699 nvl(fcr.fem_data_type_code,dtc.data_type) as cpm_datatype,
700 fcr.dimension_id,
701 (SELECT fd.dimension_name FROM fem_dimensions_tl fd
702 WHERE fd.dimension_id = fcr.dimension_id
703 AND fd.language = USERENV('LANG')
704 AND rownum = 1) as Dimension_name,
705 fcr.uom_column_name,
706 DECODE(uom_column_name,NULL, NULL, (SELECT display_name
707 FROM fem_tab_columns_tl
708 WHERE column_name = uom_column_name
709 AND language = USERENV('LANG')
710 AND rownum = 1)) as uom_col_display_name,
711 'Y' selected,
712 DECODE(dtc.nullable,'N','Y','Y','N') disable_flag,
713 DECODE(fcr.restricted_flag,'Y','CpmDisabled','CpmDataType') cpm_switcher,
714 DECODE(fcr.restricted_flag, 'Y',
715 DECODE(DECODE(fcr.restricted_flag,'N',NULL,fcr.dimension_id),NULL,
716 DECODE(fcr.fem_data_type_code,'DIMENSION','ronlyDimswitch','disableDimLov' ),
717 'ronlyDimswitch'),
718 DECODE(DECODE(fcr.restricted_flag,'N',NULL,fcr.dimension_id),NULL,
719 DECODE(fcr.fem_data_type_code,'DIMENSION','enableDimLov','disableDimLov' ),
720 'enableDimLov')) dim_switcher,
721 DECODE(fcr.restricted_flag, 'Y',
722 DECODE(uom_column_name,NULL,
723 DECODE(fcr.fem_data_type_code,'TERM', 'ronlyUomswitch',
724 'STATISTIC', 'ronlyUomswitch',
725 'FREQ', 'ronlyUomswitch', 'disableUomLov' ),
726 'ronlyUomswitch'),
727 DECODE(uom_column_name,NULL,
728 DECODE(fcr.fem_data_type_code,'TERM', 'enableUomLov',
729 'STATISTIC', 'enableUomLov',
730 'FREQ', 'enableUomLov', 'disableUomLov' ),
731 'enableUomLov')) uom_Switcher,
732 'Y' enabled_flag,
733 NVL(restricted_flag,'N') restricted_flag,
734 'N' update_flag,
735 0 object_version_number
736 FROM dba_tab_columns dtc,
737 fem_column_requiremnt_vl fcr
738 WHERE dtc.table_name = p_table_name
739 AND dtc.owner = p_owner
740 AND dtc.column_name = fcr.column_name;
741
742 fem_engines_pkg.tech_message (p_severity => g_log_level_1
743 ,p_module => g_block||'.populate_tab_col_gt'
744 ,p_msg_text => 'Populating columns with data in tab columns');
745
746 INSERT INTO fem_tab_columns_gt
747 (table_name,
748 column_name,
749 display_name,
750 description,
751 data_type,
752 data_length,
753 data_precision,
754 cpm_datatype,
755 dimension_id,
756 dimension_name,
757 uom_column_name,
758 uom_col_display_name,
759 selected,
760 disable_flag,
761 cpm_switcher,
765 restricted_flag,
762 dim_switcher,
763 uom_switcher,
764 enabled_flag,
766 update_flag,
767 object_version_number
768 )
769 SELECT
770 dump.table_name,
771 dump.column_name,
772 nvl(display_name,dump.column_name) display_name,
773 nvl(description,dump.column_name) description,
774 dump.data_type,
775 dump.data_length,
776 dump.data_precision,
777 nvl(ftc.fem_data_type_code,dump.data_type) as cpm_datatype,
778 ftc.dimension_id,
779 DECODE(ftc.dimension_id, NULL, NULL, (SELECT fd.dimension_name
780 FROM fem_dimensions_tl fd
781 WHERE fd.dimension_id = ftc.dimension_id
782 AND fd.language = USERENV('LANG')
783 AND rownum = 1)) as Dimension_name,
784 ftc.uom_column_name,
785 DECODE(uom_column_name,NULL, NULL, (SELECT display_name
786 FROM fem_tab_columns_tl
787 WHERE column_name = ftc.uom_column_name
788 AND language = USERENV('LANG')
789 AND rownum = 1)) as uom_col_display_name,
790 'Y' selected,
791 DECODE(dump.nullable,'N','Y','Y','N') disable_flag,
792 'CpmDataType' cpm_switcher,
793 DECODE(ftc.dimension_id,NULL,
794 DECODE(ftc.fem_data_type_code,'DIMENSION','enableDimLov','disableDimLov' ),
795 'enableDimLov') dim_switcher,
796 DECODE(ftc.uom_column_name,NULL,
797 DECODE(ftc.fem_data_type_code,'TERM', 'enableUomLov',
798 'STATISTIC', 'enableUomLov',
799 'FREQ', 'enableUomLov', 'disableUomLov' ),
800 'enableUomLov') uom_Switcher,
801 'Y' enabled_flag,
802 'N' restricted_flag,
803 'N' update_flag,
804 0 object_version_number
805 FROM fem_tab_columns_vl ftc,
806 (
807 SELECT dtc.column_name, dtc.table_name, (SELECT table_name tname
808 FROM fem_tab_columns_b
809 WHERE column_name = dtc.column_name AND rownum = 1) tname,
810 data_type, nullable, data_length, data_precision
811 FROM dba_tab_columns dtc
812 WHERE dtc.table_name = p_table_name
813 AND dtc.owner = p_owner
814 AND NOT EXISTS ( SELECT 1
815 FROM fem_tab_columns_gt fcr
816 WHERE fcr.column_name = dtc.column_name )) dump
820 fem_engines_pkg.tech_message (p_severity => g_log_level_1
817 WHERE ftc.column_name = dump.column_name
818 AND ftc.table_name = dump.tname;
819
821 ,p_module => g_block||'.populate_tab_col_gt'
822 ,p_msg_text => 'Populating columns with data in dba tab columns');
823
824 INSERT INTO fem_tab_columns_gt
825 (table_name,
826 column_name,
827 display_name,
828 description,
829 data_type,
830 data_length,
831 data_precision,
832 cpm_datatype,
833 dimension_id,
834 dimension_name,
835 uom_column_name,
836 uom_col_display_name,
837 selected,
838 disable_flag,
839 cpm_switcher,
840 dim_switcher,
841 uom_switcher,
842 enabled_flag,
843 restricted_flag,
844 update_flag,
845 object_version_number
846 )
847 SELECT
848 table_name,
849 column_name,
850 column_name,
851 column_name,
852 data_type,
853 data_length,
854 data_precision,
855 data_type,
856 TO_NUMBER(NULL),
857 NULL,
858 NULL,
859 NULL,
860 'Y',
861 DECODE(nullable,'N','Y','Y','N'),
862 'CpmDataType',
863 'disableDimLov',
864 'disableUomLov',
865 'Y',
866 'N',
867 'N' update_flag,
868 0 object_version_number
869 FROM dba_tab_columns atc
870 WHERE table_name = p_table_name
871 AND atc.owner = p_owner
872 AND NOT EXISTS ( SELECT 1
873 FROM fem_tab_columns_gt ftc
874 WHERE column_name = atc.column_name );
875
876 ELSIF p_mode = 'UPDATE' THEN
877
878 BEGIN
879
880 IF l_owner is NULL THEN
881
882 fem_database_util_pkg.get_table_owner
883 (x_return_status => l_schema_return_status,
884 x_msg_count => l_schema_msg_count,
885 x_msg_data => l_schema_msg_data,
886 p_syn_name => p_table_name,
887 x_tab_name => l_schema_tab_name,
888 x_tab_owner => l_owner
889 );
890
891 END IF;
892
893
894 EXCEPTION
895 WHEN OTHERS THEN
896 RAISE l_owner_error;
897 END;
898
899 fem_engines_pkg.tech_message (p_severity => g_log_level_1
900 ,p_module => g_block||'.synchronize'
901 ,p_msg_text => 'After fetching the owner, owner = ' || l_owner);
902
903 fem_engines_pkg.tech_message (p_severity => g_log_level_1
904 ,p_module => g_block||'.populate_tab_col_gt'
905 ,p_msg_text => 'Update mode: Populating columns');
906
907 INSERT INTO fem_tab_columns_gt
908 (table_name,
909 column_name,
910 display_name,
911 description,
912 data_type,
913 data_length,
914 data_precision,
915 cpm_datatype,
916 dimension_id,
917 dimension_name,
918 uom_column_name,
919 uom_col_display_name,
920 selected,
921 disable_flag,
922 cpm_switcher,
923 dim_switcher,
924 uom_switcher,
925 enabled_flag,
926 restricted_flag,
927 update_flag,
928 object_version_number
929 )
930 SELECT
931 dtc.table_name,
932 dtc.column_name,
933 dtc.display_name,
934 dtc.description,
935 dt.data_type,
936 dt.data_length,
937 dt.data_precision,
938 dtc.fem_data_type_code,
939 dtc.dimension_id,
940 DECODE(dtc.dimension_id, NULL, NULL, (SELECT fd.dimension_name
941 FROM fem_dimensions_tl fd
942 WHERE fd.dimension_id = dtc.dimension_id
943 AND fd.language = USERENV('LANG')
944 AND rownum = 1)),
945 dtc.uom_column_name,
946 DECODE(dtc.uom_column_name,NULL, NULL, (SELECT display_name
947 FROM fem_tab_columns_tl
948 WHERE column_name = dtc.uom_column_name
949 AND language = USERENV('LANG')
950 AND rownum = 1)),
951 dtc.enabled_flag selected,
952 DECODE(dt.nullable,'N','Y','Y','N') disable_flag,
953 DECODE(fcr.restricted_flag,'Y','CpmDisabled','CpmDataType') cpm_switcher,
957 'ronlyDimswitch'),
954 DECODE(fcr.restricted_flag, 'Y',
955 DECODE(dtc.dimension_id,NULL,
956 DECODE(dtc.fem_data_type_code,'DIMENSION','ronlyDimswitch','disableDimLov' ),
958 DECODE(dtc.dimension_id,NULL,
959 DECODE(dtc.fem_data_type_code,'DIMENSION','enableDimLov','disableDimLov' ),
960 'enableDimLov')) dim_switcher,
961 DECODE(fcr.restricted_flag, 'Y',
962 DECODE(dtc.uom_column_name,NULL,
963 DECODE(dtc.fem_data_type_code,'TERM', 'ronlyUomswitch',
964 'STATISTIC', 'ronlyUomswitch',
965 'FREQ', 'ronlyUomswitch', 'disableUomLov' ),
966 'ronlyUomswitch'),
967 DECODE(dtc.uom_column_name,NULL,
968 DECODE(dtc.fem_data_type_code,'TERM', 'enableUomLov',
969 'STATISTIC', 'enableUomLov',
970 'FREQ', 'enableUomLov', 'disableUomLov' ),
971 'enableUomLov')) uom_Switcher,
972 dtc.enabled_flag,
973 NVL(restricted_flag,'N') restricted_flag,
974 'Y' update_flag,
975 dtc.object_version_number
976 FROM fem_tab_columns_vl dtc,
977 fem_column_requiremnt_vl fcr,
978 dba_tab_columns dt
979 WHERE dtc.table_name = p_table_name
980 AND dtc.table_name = dt.table_name
981 AND dt.column_name = dtc.column_name
982 AND dtc.column_name = fcr.column_name
983 AND dt.owner = l_owner;
984
985 INSERT INTO fem_tab_columns_gt
986 (table_name,
987 column_name,
988 display_name,
989 description,
990 data_type,
991 data_length,
992 data_precision,
993 cpm_datatype,
994 dimension_id,
995 dimension_name,
996 uom_column_name,
997 uom_col_display_name,
998 selected,
999 disable_flag,
1000 cpm_switcher,
1001 dim_switcher,
1002 uom_switcher,
1003 enabled_flag,
1004 restricted_flag,
1005 update_flag,
1006 object_version_number
1007 )
1008 SELECT
1009 dtc.table_name,
1010 dtc.column_name,
1011 dtc.display_name,
1012 dtc.description,
1013 dt.data_type,
1014 dt.data_length,
1015 dt.data_precision,
1016 dtc.fem_data_type_code,
1017 dtc.dimension_id,
1018 DECODE(dtc.dimension_id, NULL, NULL, (SELECT fd.dimension_name
1019 FROM fem_dimensions_tl fd
1020 WHERE fd.dimension_id = dtc.dimension_id
1021 AND fd.language = USERENV('LANG')
1022 AND rownum = 1)),
1023 dtc.uom_column_name,
1024 DECODE(dtc.uom_column_name,NULL, NULL, (SELECT display_name
1025 FROM fem_tab_columns_tl
1026 WHERE column_name = dtc.uom_column_name
1027 AND language = USERENV('LANG')
1028 AND rownum = 1)),
1029 dtc.enabled_flag selected,
1030 DECODE(dt.nullable,'N','Y','Y','N') disable_flag,
1031 'CpmDataType' cpm_switcher,
1032 DECODE(dtc.dimension_id,NULL,
1033 DECODE(dtc.fem_data_type_code,'DIMENSION','enableDimLov','disableDimLov' ),
1034 'enableDimLov') dim_switcher,
1035 DECODE(dtc.uom_column_name,NULL,
1036 DECODE(dtc.fem_data_type_code,'TERM', 'enableUomLov',
1037 'STATISTIC', 'enableUomLov',
1038 'FREQ', 'enableUomLov', 'disableUomLov' ),
1039 'enableUomLov') uom_Switcher,
1040 dtc.enabled_flag,
1041 'N' restricted_flag,
1042 'Y' update_flag,
1043 dtc.object_version_number
1044 FROM fem_tab_columns_vl dtc,
1045 dba_tab_columns dt
1046 WHERE dt.table_name = p_table_name
1047 AND dt.table_name = dtc.table_name
1048 AND dt.column_name = dtc.column_name
1049 AND dt.owner = l_owner
1050 AND NOT EXISTS ( SELECT 1
1051 FROM fem_tab_columns_gt
1052 WHERE column_name = dtc.column_name );
1053
1054 END IF;
1055
1056 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1057 ,p_module => g_block||'.populate_tab_col_gt'
1058 ,p_msg_text => 'END');
1059 EXCEPTION
1060 WHEN l_owner_error THEN
1061 x_return_status := c_error;
1062
1063 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1064 ,p_module => g_block||'.synchronize'
1068 ,p_module => g_block||'.synchronize'
1065 ,p_msg_text => 'populate_tab_col_gt: Trying to get owner info. for' || p_table_name);
1066
1067 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1069 ,p_msg_text => 'populate_tab_col_gt: error = ' || SQLERRM);
1070
1071 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
1072 p_count => x_msg_count,
1073 p_data => x_msg_data);
1074
1075 WHEN OTHERS THEN
1076 x_return_status := c_error;
1077
1078 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1079 ,p_module => g_block||'.populate_tab_col_gt'
1080 ,p_msg_text => 'populate_tab_col_gt: General_Exception');
1081
1082 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1083 ,p_module => g_block||'.synchronize'
1084 ,p_msg_text => 'populate_tab_col_gt: error = ' || SQLERRM);
1085
1086 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
1087 p_count => x_msg_count,
1088 p_data => x_msg_data);
1089
1090 END populate_tab_col_gt;
1091
1092 PROCEDURE populate_tab_col_vl(p_api_version IN NUMBER default c_api_version,
1093 p_init_msg_list IN VARCHAR2 default c_false,
1094 p_commit IN VARCHAR2 default c_false,
1095 p_encoded IN VARCHAR2 default c_true,
1096 p_table_name IN VARCHAR2,
1097 p_skip_validation IN VARCHAR2,
1098 p_mode IN VARCHAR2,
1099 x_msg_count OUT NOCOPY NUMBER,
1100 x_msg_data OUT NOCOPY VARCHAR2,
1101 x_return_status OUT NOCOPY VARCHAR2)
1102 IS
1103
1104 l_api_version NUMBER;
1105 l_init_msg_list VARCHAR2(1);
1106 l_commit VARCHAR2(1);
1107 l_encoded VARCHAR2(1);
1108 l_src_lang VARCHAR2(100);
1109
1110 l_user_id NUMBER;
1111 l_login_id NUMBER;
1112
1113 TYPE col_tab IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
1114
1115 l_column_tab col_tab;
1116 l_display_tab col_tab;
1117 l_no_dupes BOOLEAN;
1118 l_display_name VARCHAR2(150);
1119 l_concat_val VARCHAR2(2000);
1120 l_init BOOLEAN;
1121 i NUMBER;
1122 l_count NUMBER;
1123 l_index_name VARCHAR2(30);
1124
1125 l_disp_ui_error EXCEPTION;
1126 l_ovm_error EXCEPTION;
1127 l_pk_error EXCEPTION;
1128
1129 BEGIN
1130
1131 x_return_status := c_success;
1132
1133 l_src_lang := USERENV('LANG');
1134 l_user_id := Fnd_Global.User_Id;
1135 l_login_id := Fnd_Global.Login_Id;
1136
1137 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1138 ,p_module => g_block||'.populate_tab_col_vl'
1139 ,p_msg_text => 'BEGIN');
1140
1141 l_api_version := NVL(p_api_version, c_api_version);
1142 l_init_msg_list := NVL(p_init_msg_list, c_false);
1143 l_commit := NVL(p_commit, c_false);
1144 l_encoded := NVL(p_encoded, c_true);
1145
1146 IF p_skip_validation = 'N' THEN
1147
1148 fnd_msg_pub.initialize;
1149
1150 SELECT COUNT(*)
1151 INTO l_count
1152 FROM fem_tab_columns_b ftc,
1153 fem_tab_columns_gt ftcg
1154 WHERE ftc.table_name = p_table_name
1155 AND ftcg.table_name = ftc.table_name
1156 AND ftc.object_version_number <> ftcg.object_version_number
1157 AND ftc.column_name = ftcg.column_name;
1158
1159 IF l_count <> 0 THEN
1160 fnd_message.set_name('FEM', 'FEM_TR_OBJ_COL_STALE_DATA_ERR');
1161 fnd_message.set_token('TABLE', p_table_name);
1162 fnd_msg_pub.add;
1163
1164 RAISE l_ovm_error;
1165 END IF;
1166
1167 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1168 ,p_module => g_block||'.populate_tab_col_vl'
1169 ,p_msg_text => 'Starting validations');
1170
1171 l_init := TRUE;
1172
1173 -- Get all the columns where display_name IS NULL
1174
1175 SELECT column_name
1176 BULK COLLECT INTO l_column_tab
1177 FROM fem_tab_columns_gt
1178 WHERE display_name IS NULL;
1179
1180 IF l_column_tab.COUNT > 0 THEN
1181 l_init := FALSE;
1182 END IF;
1183
1184 IF NOT l_init THEN
1185
1186 FOR i IN l_column_tab.FIRST..l_column_tab.LAST LOOP
1187 l_concat_val := l_concat_val || ',' || l_column_tab(i);
1188 END LOOP;
1189
1190 l_concat_val := LTRIM(l_concat_val,',');
1191
1192 IF l_concat_val IS NOT NULL THEN
1196
1193 fnd_message.set_name('FEM', 'FEM_TR_OBJ_COL_DNAME_NULL_ERR');
1194 fnd_message.set_token('DISPNAME_NULL_TOK', l_concat_val);
1195 fnd_msg_pub.add;
1197 RAISE l_disp_ui_error;
1198 END IF;
1199
1200 END IF;
1201
1202 l_init := TRUE;
1203
1204 -- Get all the columns where description IS NULL
1205
1206 SELECT column_name
1207 BULK COLLECT INTO l_column_tab
1208 FROM fem_tab_columns_gt
1209 WHERE description IS NULL;
1210
1211 IF l_column_tab.COUNT > 0 THEN
1212 l_init := FALSE;
1213 END IF;
1214
1215 IF NOT l_init THEN
1216
1217 FOR i IN l_column_tab.FIRST..l_column_tab.LAST LOOP
1218 l_concat_val := l_concat_val || ',' || l_column_tab(i);
1219 END LOOP;
1220
1221 l_concat_val := LTRIM(l_concat_val,',');
1222
1223 IF l_concat_val IS NOT NULL THEN
1224 fnd_message.set_name('FEM', 'FEM_TR_OBJ_COL_DESC_NULL_ERR');
1225 fnd_message.set_token('DESC_NULL_TOK', l_concat_val);
1226 fnd_msg_pub.add;
1227
1228 RAISE l_disp_ui_error;
1229 END IF;
1230
1231 END IF;
1232
1233 l_no_dupes := TRUE;
1234 l_init := FALSE;
1235
1236 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1237 ,p_module => g_block||'.populate_tab_col_vl'
1238 ,p_msg_text => 'Completed NULL display names and descriptions check');
1239
1240 -- Get all the duplicate display name
1241
1242 SELECT a.column_name,a.display_name
1243 BULK COLLECT INTO l_column_tab, l_display_tab
1244 FROM fem_tab_columns_gt a,fem_tab_columns_gt b
1245 WHERE UPPER(a.display_name)=UPPER(b.display_name)
1246 AND a.column_name <> b.column_name;
1247
1248 IF l_column_tab.COUNT > 0 THEN
1249 l_no_dupes := FALSE;
1250 END IF;
1251
1252 IF NOT l_no_dupes THEN
1253
1254 FOR i IN l_column_tab.FIRST..l_column_tab.LAST LOOP
1255
1256 IF l_init = FALSE THEN
1257 l_init := TRUE;
1258 l_display_name := l_display_tab(i);
1259 l_concat_val := '(';
1260 END IF;
1261
1262 IF l_display_tab(i) = l_display_name THEN
1263 l_concat_val := l_concat_val || l_column_tab(i) || ',' ;
1264 ELSE
1265 l_display_name := l_display_tab(i);
1266 l_concat_val := RTRIM(l_concat_val,',') || ')' || ',(' || l_column_tab(i) || ',' ;
1267 END IF;
1268
1269 END LOOP;
1270
1271 l_concat_val := RTRIM(l_concat_val,',') || ')';
1272
1273 END IF;
1274
1275 IF l_concat_val IS NOT NULL THEN
1276 fnd_message.set_name('FEM', 'FEM_TR_OBJ_COL_DNAME_DUP_ERR');
1277 fnd_message.set_token('DISPNAME_DUP_TOK', l_concat_val);
1278 fnd_msg_pub.add;
1279
1280 RAISE l_disp_ui_error;
1281 END IF;
1282
1283 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1284 ,p_module => g_block||'.populate_tab_col_vl'
1285 ,p_msg_text => 'Completed duplicate display names check');
1286
1287 l_init := TRUE;
1288
1289 -- Get all the columns where fem_data_type_code = 'DIMENSION' AND dimension_id IS NULL
1290
1291 SELECT column_name
1292 BULK COLLECT INTO l_column_tab
1293 FROM fem_tab_columns_gt
1294 WHERE cpm_datatype = 'DIMENSION'
1295 AND dimension_id IS NULL
1296 AND SUBSTR(COLUMN_NAME,1,8) <> 'USER_DIM';
1297
1298 IF l_column_tab.COUNT > 0 THEN
1299 l_init := FALSE;
1300 END IF;
1301
1302 IF NOT l_init THEN
1303
1304 FOR i IN l_column_tab.FIRST..l_column_tab.LAST LOOP
1305 l_concat_val := l_concat_val || ',' || l_column_tab(i);
1306 END LOOP;
1307
1308 l_concat_val := LTRIM(l_concat_val,',');
1309
1310 IF l_concat_val IS NOT NULL THEN
1311 fnd_message.set_name('FEM', 'FEM_TR_OBJ_COL_DIMID_NULL_ERR');
1312 fnd_message.set_token('DIM_NULL_TOK', l_concat_val);
1313 fnd_msg_pub.add;
1314
1315 RAISE l_disp_ui_error;
1316 END IF;
1317
1318 END IF;
1319
1320 l_init := TRUE;
1321
1322 -- Get all the columns where fem_data_type_code = ('TERM','FREQ','STATISTIC') AND uom_column_name IS NULL
1323
1324 SELECT column_name
1325 BULK COLLECT INTO l_column_tab
1326 FROM fem_tab_columns_gt
1327 WHERE cpm_datatype IN ('TERM','FREQ','STATISTIC')
1328 AND uom_column_name IS NULL;
1329
1330 IF l_column_tab.COUNT > 0 THEN
1331 l_init := FALSE;
1332 END IF;
1333
1334 IF NOT l_init THEN
1335
1336 FOR i IN l_column_tab.FIRST..l_column_tab.LAST LOOP
1340 l_concat_val := LTRIM(l_concat_val,',');
1337 l_concat_val := l_concat_val || ',' || l_column_tab(i);
1338 END LOOP;
1339
1341
1342 IF l_concat_val IS NOT NULL THEN
1343 fnd_message.set_name('FEM', 'FEM_TR_OBJ_COL_UOM_NULL_ERR');
1344 fnd_message.set_token('UOM_NULL_TOK', l_concat_val);
1345 fnd_msg_pub.add;
1346
1347 RAISE l_disp_ui_error;
1348 END IF;
1349
1350 END IF;
1351
1352 l_init := TRUE;
1353
1354 -- Get all the columns where fem_data_type_code <> ('TERM','FREQ','STATISTIC') AND uom_column_name IS NOT NULL
1355
1356 SELECT column_name
1357 BULK COLLECT INTO l_column_tab
1358 FROM fem_tab_columns_gt
1359 WHERE cpm_datatype NOT IN ('TERM','FREQ','STATISTIC')
1360 AND uom_column_name IS NOT NULL;
1361
1362 IF l_column_tab.COUNT > 0 THEN
1363 l_init := FALSE;
1364 END IF;
1365
1366 IF NOT l_init THEN
1367
1368 FOR i IN l_column_tab.FIRST..l_column_tab.LAST LOOP
1369 l_concat_val := l_concat_val || ',' || l_column_tab(i);
1370 END LOOP;
1371
1372 l_concat_val := LTRIM(l_concat_val,',');
1373
1374 IF l_concat_val IS NOT NULL THEN
1375 fnd_message.set_name('FEM', 'FEM_TR_OBJ_COL_INV_UOM_USG');
1376 fnd_message.set_token('INV_UOM_USG', l_concat_val);
1377 fnd_msg_pub.add;
1378
1379 RAISE l_disp_ui_error;
1380 END IF;
1381
1382 END IF;
1383
1384 l_init := TRUE;
1385
1386 -- Get all the columns where fem_data_type_code = ('TERM','FREQ','STATISTIC') AND uom_column_name IS NOT NULL
1387 -- and maps to one of the columns being registered.
1388
1389 SELECT column_name
1390 BULK COLLECT INTO l_column_tab
1391 FROM fem_tab_columns_gt a
1392 WHERE cpm_datatype IN ('TERM','FREQ','STATISTIC')
1393 AND uom_column_name IS NOT NULL
1394 AND NOT EXISTS ( SELECT column_name
1395 FROM fem_tab_columns_gt b
1396 WHERE a.uom_column_name = b.column_name );
1397
1398 IF l_column_tab.COUNT > 0 THEN
1399 l_init := FALSE;
1400 END IF;
1401
1402 IF NOT l_init THEN
1403
1404 FOR i IN l_column_tab.FIRST..l_column_tab.LAST LOOP
1405 l_concat_val := l_concat_val || ',' || l_column_tab(i);
1406 END LOOP;
1407
1408 l_concat_val := LTRIM(l_concat_val,',');
1409
1410 IF l_concat_val IS NOT NULL THEN
1411 fnd_message.set_name('FEM', 'FEM_TR_OBJ_COL_UOM_DISABLED');
1412 fnd_message.set_token('UOM_DISABLED_TOK', l_concat_val);
1413 fnd_msg_pub.add;
1414
1415 RAISE l_disp_ui_error;
1416 END IF;
1417
1418 END IF;
1419
1420 l_init := TRUE;
1421
1422 -- Get all the columns where fem_data_type_code = ('TERM','FREQ','STATISTIC') AND uom_column_name IS NOT NULL
1423 -- and maps to one of the columns being registered, that column should be of dimension type
1424
1425 SELECT column_name
1426 BULK COLLECT INTO l_column_tab
1427 FROM fem_tab_columns_gt a
1428 WHERE cpm_datatype IN ('TERM','FREQ','STATISTIC')
1429 AND uom_column_name IS NOT NULL
1430 AND NOT EXISTS ( SELECT column_name
1431 FROM fem_tab_columns_gt b
1432 WHERE a.uom_column_name = b.column_name
1433 AND b.cpm_datatype = 'DIMENSION' );
1434
1435 IF l_column_tab.COUNT > 0 THEN
1436 l_init := FALSE;
1437 END IF;
1438
1439 IF NOT l_init THEN
1440
1441 FOR i IN l_column_tab.FIRST..l_column_tab.LAST LOOP
1442 l_concat_val := l_concat_val || ',' || l_column_tab(i);
1443 END LOOP;
1444
1445 l_concat_val := LTRIM(l_concat_val,',');
1446
1447 IF l_concat_val IS NOT NULL THEN
1448 fnd_message.set_name('FEM', 'FEM_TR_OBJ_COL_UOM_FEMDT_ERR');
1449 fnd_message.set_token('UOM_FEM_DT_TOK', l_concat_val);
1450 fnd_msg_pub.add;
1451
1452 RAISE l_disp_ui_error;
1453 END IF;
1454
1455 END IF;
1456
1457 l_init := TRUE;
1458
1459 -- Get all the columns where fem_data_type_code = ('TERM','FREQ','STATISTIC') AND uom_column_name IS NOT NULL
1460 -- and maps to one of the columns being registered, that column should be of dimension type, it should be unique
1461
1462
1463 SELECT column_name
1464 BULK COLLECT INTO l_column_tab
1465 FROM fem_tab_columns_gt a
1466 WHERE cpm_datatype IN ('TERM','FREQ','STATISTIC')
1467 AND uom_column_name IS NOT NULL
1468 AND uom_column_name in (SELECT uom_column_name
1469 FROM fem_tab_columns_gt b
1470 GROUP BY uom_column_name
1471 HAVING COUNT(uom_column_name)>1);
1472
1473 IF l_column_tab.COUNT > 0 THEN
1477 IF NOT l_init THEN
1474 l_init := FALSE;
1475 END IF;
1476
1478
1479 FOR i IN l_column_tab.FIRST..l_column_tab.LAST LOOP
1480 l_concat_val := l_concat_val || ',' || l_column_tab(i);
1481 END LOOP;
1482
1483 l_concat_val := LTRIM(l_concat_val,',');
1484
1485 IF l_concat_val IS NOT NULL THEN
1486 fnd_message.set_name('FEM', 'FEM_TR_OBJ_COL_UOM_DUP_ERR');
1487 fnd_message.set_token('UOM_DUP_TOK', l_concat_val);
1488 fnd_msg_pub.add;
1489
1490 RAISE l_disp_ui_error;
1491 END IF;
1492
1493 END IF;
1494
1495 IF p_mode = 'UPDATE' THEN
1496 SELECT proc_key_index_name
1497 INTO l_index_name
1498 FROM fem_tables_b ftb
1499 WHERE table_name = p_table_name;
1500
1501 SELECT ftcb.column_name
1502 BULK COLLECT INTO l_column_tab
1503 FROM fem_tab_columns_gt ftcb,
1504 fem_tab_column_prop ftcp
1505 WHERE ftcb.table_name = p_table_name
1506 AND ftcb.table_name = ftcp.table_name
1507 AND ftcp.column_property_code = 'PROCESSING_KEY'
1508 AND ftcp.column_name = ftcb.column_name
1509 AND ftcb.enabled_flag = 'N';
1510
1511 IF l_column_tab.COUNT > 0 THEN
1512 l_init := FALSE;
1513 END IF;
1514
1515 IF NOT l_init THEN
1516
1517 FOR i IN l_column_tab.FIRST..l_column_tab.LAST LOOP
1518 l_concat_val := l_concat_val || ',' || l_column_tab(i);
1519 END LOOP;
1520
1521 l_concat_val := LTRIM(l_concat_val,',');
1522
1523 IF l_concat_val IS NOT NULL THEN
1524 fnd_message.set_name('FEM', 'FEM_TR_PK_COLS_DISABLED_ERR');
1525 fnd_message.set_token('COLUMNS', l_concat_val);
1526 fnd_message.set_token('PROCKEY', l_index_name);
1527 fnd_msg_pub.add;
1528
1529 RAISE l_pk_error;
1530 END IF;
1531
1532 END IF;
1533 END IF;
1534
1535 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1536 ,p_module => g_block||'.populate_tab_col_vl'
1537 ,p_msg_text => 'Completed dimension_id and uom_column_name checks');
1538
1539 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1540 ,p_module => g_block||'.populate_tab_col_vl'
1541 ,p_msg_text => 'Completed validations');
1542 END IF;
1543
1544 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1545 ,p_module => g_block||'.populate_tab_col_vl'
1546 ,p_msg_text => 'Before insert into _B');
1547
1548 SELECT COUNT(*)
1549 INTO l_count
1550 FROM fem_tab_columns_b ftc,
1551 fem_tab_columns_gt ftcg
1552 WHERE ftc.table_name = p_table_name
1553 AND ftcg.table_name = ftc.table_name
1554 AND ftc.object_version_number <> ftcg.object_version_number
1555 AND ftc.column_name = ftcg.column_name;
1556
1557 IF l_count <> 0 THEN
1558 fnd_message.set_name('FEM', 'FEM_TR_OBJ_COL_STALE_DATA_ERR');
1559 fnd_message.set_token('TABLE', p_table_name);
1560 fnd_msg_pub.add;
1561
1562 RAISE l_ovm_error;
1563 END IF;
1564
1565 MERGE INTO fem_tab_columns_b ftc
1566 USING fem_tab_columns_gt ftcg
1567 ON ( ftc.column_name = ftcg.column_name
1568 AND ftc.table_name = p_table_name )
1569 WHEN MATCHED THEN UPDATE
1570 SET
1571 ftc.enabled_flag = ftcg.enabled_flag,
1572 ftc.fem_data_type_code = NVL(ftcg.cpm_datatype,'UNDEFINED'),
1573 ftc.dimension_id = ftcg.dimension_id,
1574 ftc.uom_column_name = ftcg.uom_column_name,
1575 ftc.last_updated_by = l_user_id,
1576 ftc.last_update_date = SYSDATE,
1577 ftc.last_update_login = l_login_id,
1578 ftc.object_version_number = NVL(ftc.object_version_number,0) + 1
1579 WHEN NOT MATCHED THEN
1580 INSERT
1581 (
1582 enabled_flag,
1583 interface_column_name,
1584 table_name,
1585 column_name,
1586 fem_data_type_code,
1587 dimension_id,
1588 uom_column_name,
1589 creation_date,
1590 created_by,
1591 last_update_date,
1592 last_updated_by,
1593 last_update_login,
1594 object_version_number
1595 )
1596 VALUES( ftcg.enabled_flag,
1597 NULL,
1598 ftcg.table_name,
1599 ftcg.column_name,
1600 NVL(ftcg.cpm_datatype,'UNDEFINED'),
1601 ftcg.dimension_id,
1602 ftcg.uom_column_name,
1603 SYSDATE,
1604 l_user_id,
1605 SYSDATE,
1606 l_user_id,
1607 l_login_id,
1608 1
1609 );
1610
1611 fem_engines_pkg.tech_message (p_severity => g_log_level_1
1615 MERGE INTO fem_tab_columns_tl ftc
1612 ,p_module => g_block||'.populate_tab_col_vl'
1613 ,p_msg_text => 'Before insert into _TL');
1614
1616 USING (SELECT tcgt.*,fndl.language_code
1617 FROM fem_tab_columns_gt tcgt,
1618 fnd_languages fndl
1619 WHERE fndl.installed_flag IN ('I','B')) ftcg
1620 ON ( ftc.column_name = ftcg.column_name
1621 AND ftc.table_name = ftcg.table_name
1622 AND ftc.language = ftcg.language_code
1623 AND ftcg.table_name = p_table_name )
1624 WHEN MATCHED THEN UPDATE
1625 SET
1626 ftc.display_name = DECODE(USERENV('LANG'),
1627 ftc.language,ftcg.display_name,
1628 ftc.source_lang,ftcg.display_name,
1629 ftc.display_name),
1630 ftc.description = DECODE(USERENV('LANG'),
1631 ftc.language,ftcg.description,
1632 ftc.source_lang,ftcg.description,
1633 ftc.description),
1634 ftc.last_updated_by = l_user_id,
1635 ftc.last_update_date = SYSDATE,
1636 ftc.last_update_login = l_login_id,
1637 ftc.source_lang = DECODE(USERENV('LANG'),
1638 ftc.language,ftcg.language_code,
1639 ftc.source_lang)
1640 WHEN NOT MATCHED THEN
1641 INSERT
1642 (
1643 language,
1644 table_name,
1645 column_name,
1646 source_lang,
1647 display_name,
1648 description,
1649 creation_date,
1650 created_by,
1651 last_update_date,
1652 last_updated_by,
1653 last_update_login
1654 )
1655 VALUES(
1656 ftcg.language_code,
1657 ftcg.table_name,
1658 ftcg.column_name,
1659 USERENV('LANG'),
1660 ftcg.display_name,
1661 ftcg.description,
1662 SYSDATE,
1663 l_user_id,
1664 SYSDATE,
1665 l_user_id,
1666 l_login_id
1667 );
1668
1669 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1670 ,p_module => g_block||'.populate_tab_col_vl'
1671 ,p_msg_text => 'END');
1672
1673 EXCEPTION
1674
1675 WHEN l_disp_ui_error THEN
1676 x_return_status := c_error;
1677
1678 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1679 ,p_module => g_block||'.populate_tab_col_vl'
1680 ,p_msg_text => 'populate_tab_col_vl: UI validation failed for ' || p_table_name);
1681
1682 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1683 ,p_module => g_block||'.synchronize'
1684 ,p_msg_text => 'populate_tab_col_vl: error = ' || SQLERRM);
1685
1686 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
1687 p_count => x_msg_count,
1688 p_data => x_msg_data);
1689
1690 WHEN l_pk_error THEN
1691 x_return_status := c_error;
1692
1693 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1694 ,p_module => g_block||'.populate_tab_col_vl'
1695 ,p_msg_text => 'populate_tab_col_vl: Columns disabled form part of Processing Key' || p_table_name);
1696
1697 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1698 ,p_module => g_block||'.synchronize'
1699 ,p_msg_text => 'populate_tab_col_vl: error = ' || SQLERRM);
1700
1701 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
1702 p_count => x_msg_count,
1703 p_data => x_msg_data);
1704
1705
1706 WHEN l_ovm_error THEN
1707 x_return_status := c_error;
1708
1709 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1710 ,p_module => g_block||'.populate_tab_col_vl'
1711 ,p_msg_text => 'populate_tab_col_vl: Stale data error for ' || p_table_name);
1712
1713 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1714 ,p_module => g_block||'.synchronize'
1715 ,p_msg_text => 'populate_tab_col_vl: error = ' || SQLERRM);
1716
1717 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
1718 p_count => x_msg_count,
1719 p_data => x_msg_data);
1720
1721 WHEN OTHERS THEN
1722 x_return_status := c_error;
1723
1724 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1725 ,p_module => g_block||'.populate_tab_col_vl'
1726 ,p_msg_text => 'populate_tab_col_vl: General_Exception');
1727
1728 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1732 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
1729 ,p_module => g_block||'.synchronize'
1730 ,p_msg_text => 'populate_tab_col_vl: error = ' || SQLERRM);
1731
1733 p_count => x_msg_count,
1734 p_data => x_msg_data);
1735
1736 END populate_tab_col_vl;
1737
1738 PROCEDURE dump_gt(p_api_version IN NUMBER default c_api_version,
1739 p_init_msg_list IN VARCHAR2 default c_false,
1740 p_commit IN VARCHAR2 default c_false,
1741 p_encoded IN VARCHAR2 default c_true,
1742 x_msg_count OUT NOCOPY NUMBER,
1743 x_msg_data OUT NOCOPY VARCHAR2,
1744 x_return_status OUT NOCOPY VARCHAR2)
1745 IS
1746
1747 BEGIN
1748
1749 x_return_status := c_success;
1750
1751 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1752 ,p_module => g_block||'.dump_gt'
1753 ,p_msg_text => 'BEGIN');
1754
1755 DELETE FROM fem_tab_columns_gt;
1756
1757 fem_engines_pkg.tech_message (p_severity => g_log_level_2
1758 ,p_module => g_block||'.dump_gt'
1759 ,p_msg_text => 'END');
1760
1761
1762 EXCEPTION
1763 WHEN OTHERS THEN
1764 x_return_status := c_error;
1765
1766 fem_engines_pkg.tech_message (p_severity => g_log_level_5
1767 ,p_module => g_block||'.dump_gt'
1768 ,p_msg_text => 'dump_gt: General_Exception');
1769
1770 fnd_msg_pub.count_and_get(p_encoded => p_encoded,
1771 p_count => x_msg_count,
1772 p_data => x_msg_data);
1773
1774 END dump_gt;
1775
1776 FUNCTION is_table_registered(p_table_name IN VARCHAR2) RETURN VARCHAR2
1777 IS
1778 l_valid_flag VARCHAR2(1);
1779 BEGIN
1780 l_valid_flag := 'N';
1781
1782 SELECT enabled_flag INTO l_valid_flag
1783 FROM fem_tables_b
1784 WHERE table_name=p_table_name;
1785
1786 RETURN l_valid_flag;
1787
1788 EXCEPTION
1789 WHEN NO_DATA_FOUND THEN
1790 fem_engines_pkg.user_message(p_app_name =>'FEM',
1791 p_msg_name =>'FEM_TAB_NOT_REG_ERR',
1792 p_token1=>'TABLE_NAME',
1793 p_value1=>p_table_name);
1794 RETURN l_valid_flag;
1795 END;
1796
1797 FUNCTION is_table_column_registered(p_table_name IN VARCHAR2,
1798 p_column_name IN VARCHAR2) RETURN VARCHAR2
1799 IS
1800 l_valid_flag VARCHAR2(1);
1801 BEGIN
1802
1803 l_valid_flag := 'N';
1804
1805 SELECT enabled_flag INTO l_valid_flag
1806 FROM fem_tab_columns_b
1807 WHERE table_name=p_table_name
1808 AND column_name = p_column_name;
1809
1810 RETURN l_valid_flag;
1811
1812 EXCEPTION
1813 WHEN NO_DATA_FOUND THEN
1814 fem_engines_pkg.user_message(p_app_name =>'FEM',
1815 p_msg_name =>'FEM_TAB_COL_NOT_REG_ERR',
1816 p_token1=>'TABLE_NAME',
1817 p_value1=>p_table_name,
1818 p_token2=>'COLUMN_NAME',
1819 p_value2=>p_column_name);
1820 RETURN l_valid_flag;
1821 END;
1822
1823 FUNCTION is_table_class_code_valid(p_table_name VARCHAR2,
1824 p_table_class_code VARCHAR2) RETURN VARCHAR2
1825 IS
1826 l_valid_flag VARCHAR2(1);
1827 BEGIN
1828 l_valid_flag := 'N';
1829 IF is_table_registered(p_table_name) <> 'Y' THEN
1830 RETURN 'N';
1831 END IF;
1832 SELECT DECODE(count(*),0,'N','Y') INTO l_valid_flag
1833 FROM fem_table_class_assignmt
1834 WHERE table_classification_code = p_table_class_code
1835 AND table_name = p_table_name
1836 AND enabled_flag='Y';
1837
1838 RETURN l_valid_flag;
1839
1840 EXCEPTION
1841 WHEN NO_DATA_FOUND THEN
1842 fem_engines_pkg.user_message(p_app_name =>'FEM',
1843 p_msg_name =>'FEM_TAB_NOT_CLASS_ERR',
1844 p_token1=>'TABLE_NAME',
1845 p_value1=>p_table_name,
1846 p_token2=>'CLASSIFICATIONS',
1847 p_value2=>p_table_class_code);
1848 RETURN l_valid_flag;
1849 END;
1850
1851 FUNCTION is_table_class_list_valid(p_table_name VARCHAR2,
1852 p_table_class_lookup_type VARCHAR2) RETURN VARCHAR2
1853 IS
1854 l_valid_flag VARCHAR2(1);
1855 l_concat_classif VARCHAR2(2000);
1856 l_classif VARCHAR2(100);
1857
1858 CURSOR classifs(c_table_class_lookup_type VARCHAR2) IS
1859 SELECT lookup_code
1860 FROM fnd_lookup_values
1861 WHERE lookup_type=c_table_class_lookup_type
1862 AND language=userenv('LANG') ;
1863 BEGIN
1864 l_valid_flag := 'N';
1865
1869 l_concat_classif := '';
1866 IF is_table_registered(p_table_name) <> 'Y' THEN
1867 RETURN 'N';
1868 END IF;
1870 OPEN classifs(p_table_class_lookup_type);
1871 LOOP
1872 FETCH classifs INTO l_classif;
1873 EXIT WHEN classifs%NOTFOUND;
1874 l_concat_classif := l_concat_classif || ',' || l_classif ;
1875 END LOOP;
1876 CLOSE classifs;
1877 l_concat_classif := LTRIM(l_concat_classif,',');
1878 SELECT DECODE(count(*),0,'N','Y') INTO l_valid_flag
1879 FROM fem_table_class_assignmt
1880 WHERE table_classification_code IN (SELECT lookup_code
1881 FROM fnd_lookup_values
1882 WHERE lookup_type=p_table_class_lookup_type
1883 AND language=userenv('LANG'))
1884 AND table_name = p_table_name
1885 AND enabled_flag='Y';
1886
1887
1888 RETURN l_valid_flag;
1889
1890 EXCEPTION
1891 WHEN NO_DATA_FOUND THEN
1892 fem_engines_pkg.user_message(p_app_name =>'FEM',
1893 p_msg_name =>'FEM_TAB_NOT_CLASS_ERR',
1894 p_token1=>'TABLE_NAME',
1895 p_value1=>p_table_name,
1896 p_token2=>'CLASSIFICATIONS',
1897 p_value2=>l_concat_classif); RETURN l_valid_flag;
1898 END;
1899
1900
1901 FUNCTION get_schema_name(p_app_id IN NUMBER)
1902 RETURN VARCHAR2 IS
1903
1904 l_status VARCHAR2(100);
1905 l_industry VARCHAR2(100);
1906 l_schema VARCHAR2(10);
1907 l_app_short_name VARCHAR2(50);
1908 l_ret_status BOOLEAN;
1909
1910 BEGIN
1911
1912 SELECT application_short_name
1913 INTO l_app_short_name
1914 FROM fnd_application
1915 WHERE application_id = p_app_id;
1916
1917 l_ret_status := fnd_installation.get_app_info(l_app_short_name,l_status,l_industry,l_schema);
1918
1919 RETURN l_schema;
1920
1921 EXCEPTION
1922 WHEN OTHERS THEN
1923 RAISE_APPLICATION_ERROR(-20101,'No valid schema exists');
1924
1925 END;
1926
1927 FUNCTION get_schema_name(p_app_short_name IN VARCHAR2)
1928 RETURN VARCHAR2 IS
1929
1930 l_status VARCHAR2(100);
1931 l_industry VARCHAR2(100);
1932 l_schema VARCHAR2(10);
1933
1934 l_ret_status BOOLEAN;
1935
1936 BEGIN
1937
1938 l_ret_status := fnd_installation.get_app_info(p_app_short_name,l_status,l_industry,l_schema);
1939
1940 RETURN l_schema;
1941
1942 EXCEPTION
1943 WHEN OTHERS THEN
1944 RAISE_APPLICATION_ERROR(-20101,'No valid schema exists');
1945
1946 END;
1947
1948 PROCEDURE raise_proc_key_update_event(p_table_name IN VARCHAR2,
1949 x_msg_count OUT NOCOPY NUMBER,
1950 x_msg_data OUT NOCOPY VARCHAR2,
1951 x_return_status OUT NOCOPY VARCHAR2
1952 )
1953
1954 IS
1955 l_event_name VARCHAR2(240) := 'oracle.apps.fem.admin.prockey.updated';
1956 l_event_key VARCHAR2(240);
1957
1958 l_parameter_list wf_parameter_list_t;
1959 l_event wf_event_t;
1960
1961 l_api_name CONSTANT VARCHAR2(30) := 'raise_proc_key_update_event';
1962 l_api_version CONSTANT NUMBER := 1.0;
1963
1964 BEGIN
1965
1966 FEM_ENGINES_PKG.Tech_Message (
1967 p_severity => fnd_log.level_procedure
1968 ,p_module => g_block||'.'||l_api_name
1969 ,p_msg_text => 'Begining Function');
1970
1971 x_return_status := c_success;
1972
1973 l_event_key := p_table_name ||'_'|| sysdate;
1974
1975 wf_event_t.initialize(l_event);
1976
1977 l_event.AddParameterToList(G_TABLE_NAME, p_table_name);
1978
1979 l_parameter_list := l_event.getParameterList();
1980
1981 wf_event.raise(
1982 p_event_name => l_event_name
1983 ,p_event_key => l_event_key
1984 ,p_parameters => l_parameter_list);
1985
1986 l_parameter_list.delete;
1987
1988 EXCEPTION
1989
1990 WHEN OTHERS THEN
1991 x_return_status := c_error;
1992
1993 fem_engines_pkg.tech_message (
1994 p_severity => fnd_log.level_unexpected
1995 ,p_module => g_block||'.'||l_api_name
1996 ,p_msg_text => SQLERRM
1997 );
1998
1999 fnd_msg_pub.add_exc_msg(g_block, l_api_name);
2000
2001 RAISE fnd_api.g_exc_unexpected_error;
2002 END;
2003
2004 /*============================================================================+
2005 | PROCEDURE
2006 | get_tab_list
2007 |
2008 | DESCRIPTION
2009 | This Procedure retrieves the underlying base tables and their unique Indexes
2010 | for a view.This proc can not be used for Views containing UNIONS and DB Links.
2011 | After resolving the base tables and uniques indexes, it populates two GTs;
2012 | one containing the information of base tables and their owners and other
2013 | containing information for their unique indexes.
2014 |
2015 | SCOPE - PUBLIC
2016 +============================================================================*/
2017
2018 PROCEDURE get_tab_list(
2019 p_view_name IN VARCHAR2
2023 )
2020 ,x_msg_count OUT NOCOPY NUMBER
2021 ,x_msg_data OUT NOCOPY VARCHAR2
2022 ,x_return_status OUT NOCOPY VARCHAR2
2024
2025 IS
2026
2027 TYPE char_table IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
2028
2029 g_tab_list_tab char_table;
2030 g_owner_list_tab char_table;
2031
2032 l_str CLOB;
2033
2034 l_from NUMBER;
2035 l_where NUMBER;
2036 l_to NUMBER;
2037
2038 i NUMBER := 1;
2039 j NUMBER := 1;
2040
2041 l_tab VARCHAR2(100);
2042
2043 l_where_flag BOOLEAN := FALSE;
2044 l_db_link BOOLEAN := FALSE;
2045 l_union_flag BOOLEAN := FALSE;
2046
2047 BEGIN
2048
2049
2050 SELECT dbms_metadata.get_ddl('VIEW',p_view_name)
2051 INTO l_str
2052 FROM dual;
2053
2054
2055 l_str := UPPER(l_str);
2056
2057 IF INSTR(l_str,'UNION ') > 0 THEN
2058
2059 --x_ret_val := 'U';
2060 l_union_flag := TRUE;
2061 END IF;
2062
2063 IF INSTR(l_str,'@') > 0 THEN
2064
2065 --x_ret_val := 'D';
2066 l_db_link := TRUE;
2067 END IF;
2068
2069 l_where := INSTR(l_str,'WHERE ') ;
2070
2071 IF l_where > 0 THEN
2072
2073 l_where_flag := TRUE;
2074 END IF;
2075
2076 l_str := SUBSTR(l_str, INSTR(l_str,'FROM ')+5, LENGTH(l_str));
2077
2078 IF NOT l_union_flag AND NOT l_db_link THEN
2079
2080 IF l_where_flag THEN
2081 l_str := SUBSTR(l_str, 1, INSTR(l_str, 'WHERE ')-1);
2082 END IF;
2083
2084 l_str := LTRIM(RTRIM(l_str));
2085
2086 l_str := l_str || ', ';
2087
2088 LOOP
2089 l_tab := SUBSTR(l_str, j, INSTR(l_str,',')-1);
2090 j := j + LENGTH(l_tab) + 2; -- to jump ahead of ','
2091 l_tab := LTRIM(RTRIM(l_tab));
2092
2093 IF INSTR(l_tab, ' ') > 0 THEN
2094 l_tab := SUBSTR(l_tab, 1, INSTR(l_tab,' ')-1);
2095 END IF;
2096
2097 g_owner_list_tab(i) := NULL;
2098 IF INSTR(l_tab,'.') > 0 THEN
2099 l_tab := SUBSTR(l_tab, INSTR(l_tab,'.')+1, LENGTH(l_tab));
2100 g_owner_list_tab(i) := SUBSTR(l_tab, 1, INSTR(l_tab, '.') - 1);
2101 END IF;
2102 g_tab_list_tab(i) := l_tab;
2103 i := i +1;
2104 EXIT WHEN j > LENGTH(l_str);
2105 END LOOP;
2106
2107 END IF;
2108
2109
2110 IF g_tab_list_tab.EXISTS(1) AND NOT l_db_link AND NOT l_union_flag THEN
2111
2112 DELETE fem_tab_info_gt;
2113
2114 DELETE fem_tab_indx_info_gt;
2115
2116
2117 FORALL k IN 1..g_tab_list_tab.COUNT
2118 INSERT INTO fem_tab_info_gt
2119 (table_name, owner, db_link)
2120 SELECT table_name,
2121 table_owner,
2122 NULL
2123 FROM user_synonyms
2124 WHERE synonym_name = g_tab_list_tab(k)
2125 UNION
2126 SELECT g_tab_list_tab(k),
2127 g_owner_list_tab(k),
2128 NULL
2129 FROM dual;
2130
2131
2132 INSERT INTO fem_tab_indx_info_gt(table_name,index_name,column_name,column_position)
2133 SELECT aic.table_name,
2134 aic.index_name,
2135 aic.column_name,
2136 aic.column_position
2137 FROM all_ind_columns aic,
2138 all_indexes ai,
2139 all_updatable_columns uuc,
2140 fem_tab_info_gt ftig
2141 WHERE ai.index_name = aic.index_name
2142 AND ai.table_name = aic.table_name
2143 AND ai.uniqueness = 'UNIQUE'
2144 AND ai.index_type = 'NORMAL'
2145 AND ftig.table_name = aic.table_name
2146 AND aic.table_name = uuc.table_name
2147 AND uuc.table_name = ai.table_name
2148 AND uuc.column_name = aic.column_name
2149 AND ftig.owner = aic.index_owner
2150 AND aic.index_owner = uuc.owner
2151 AND uuc.owner = ai.owner
2152 AND uuc.updatable = 'YES'
2153 ORDER BY index_name, column_position;
2154 END IF;
2155
2156
2157 END get_tab_list;
2158
2159 /*============================================================================+
2160 | PROCEDURE
2161 | get_Object_Type
2162 |
2163 | DESCRIPTION
2164 | This function returns 'FEM_TABLE'/'FEM_VIEW' depending on the passed object
2165 | type is a data base table/View
2166 |
2167 | SCOPE - PUBLIC
2168 +============================================================================*/
2169
2170 FUNCTION get_Object_Type(
2171 p_object_name IN VARCHAR2)
2172 RETURN VARCHAR2
2173
2174 IS
2175 l_obj_type VARCHAR2(19):= 'TABLE';
2176 l_apps VARCHAR2(30):=USER;
2177
2178 BEGIN
2179
2180 SELECT decode(object_type,'TABLE','FEM_TABLE','VIEW','FEM_VIEW','SYNONYM','FEM_TABLE')
2181 INTO l_obj_type
2182 FROM all_objects
2183 WHERE owner=l_apps
2184 AND OBJECT_NAME = p_object_name;
2185
2186 RETURN l_obj_type;
2187
2188 END get_Object_Type;
2189
2190 /*============================================================================+
2194 | DESCRIPTION
2191 | PROCEDURE
2192 | get_Fem_Object_Type
2193 |
2195 | This function returns 'FEM_VIEW'/'FEM_TABLE' depending on the passed object
2196 | type has a DI Read Only classificationassigned or not.
2197 |
2198 | SCOPE - PUBLIC
2199 +============================================================================*/
2200
2201
2202 FUNCTION get_Fem_Object_Type(
2203 p_object_name IN VARCHAR2)
2204 RETURN VARCHAR2
2205
2206 IS
2207 l_obj_type VARCHAR2(19):= 'FEM_TABLE';
2208 i NUMBER :=0;
2209
2210 BEGIN
2211
2212 SELECT count(*)
2213 INTO i
2214 FROM fem_table_class_assignmt
2215 WHERE table_name = p_object_name
2216 AND TABLE_CLASSIFICATION_CODE = 'DI_READ_ONLY';
2217
2218 IF (i = 1) THEN
2219 l_obj_type := 'FEM_VIEW';
2220
2221 END IF;
2222
2223 RETURN l_obj_type;
2224
2225 END get_Fem_Object_Type;
2226
2227 /*============================================================================+
2228 | FUNCTION
2229 | get_di_view_details
2230 |
2231 | DESCRIPTION
2232 | This proc returns the DI View Name and its status passing the table name
2233 |
2234 | SCOPE - PUBLIC
2235 +============================================================================*/
2236
2237
2238 FUNCTION get_di_view_details(p_table_name IN VARCHAR2) RETURN VARCHAR2
2239 AS
2240 x_di_view_name varchar2(30);
2241 BEGIN
2242 SELECT di_view_name INTO x_di_view_name FROM fem_tables_b
2243 WHERE table_name = p_table_name
2244 AND EXISTS (SELECT 1 FROM user_objects WHERE object_name = di_view_name
2245 AND status = 'VALID');
2246
2247 RETURN x_di_view_name;
2248
2249 END get_di_view_details;
2250
2251 /*============================================================================+
2252 | PROCEDURE
2253 | GenerateSysView
2254 |
2255 | DESCRIPTION
2256 | This proc generates the View for a table which is used in DI for showing
2257 | IDs/Codes/Names for dimension members.The view is based on
2258 | > All not null dimension columns
2259 | > All dimension columns which are part of processing key.
2260 | > All balance type columns
2261 |
2262 | SCOPE - PUBLIC
2263 +============================================================================*/
2264
2265
2266 PROCEDURE GenerateSysView (errbuf OUT NOCOPY VARCHAR2
2267 ,retcode OUT NOCOPY VARCHAR2
2268 ,p_tab_name IN VARCHAR
2269 ,p_view_name IN VARCHAR)
2270 AS
2271 TYPE attr_list_rec IS RECORD
2272 (
2273 attribute_tab_name VARCHAR2(30),
2274 attribute_tab_count NUMBER,
2275 table_alias VARCHAR2(30)
2276 );
2277
2278 TYPE attr_list_arr IS TABLE OF attr_list_rec INDEX BY BINARY_INTEGER;
2279
2280 TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2281 TYPE char_table IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
2282
2283 gs_col_name_tab char_table;
2284 gs_disp_name_tab char_table;
2285 gs_null_flag_tab char_table;
2286 g_mem_dc_code_tab char_table;
2287
2288 g_view_col_tab char_table;
2289
2290 g_dim_tab number_table;
2291 gs_dim_id_tab number_table;
2292
2293 l_tab_alias VARCHAR2(30);
2294 l_vl_tab_alias VARCHAR2(30);
2295
2296 j NUMBER := 1;
2297
2298 select_list LONG;
2299 from_clause LONG;
2300 where_clause LONG;
2301
2302
2303 attr_list_tbl attr_list_arr;
2304
2305 c_api_version CONSTANT NUMBER := 1.0;
2306
2307 f1 utl_file.file_type;
2308
2309 v_amount NUMBER DEFAULT 32000;
2310 v_offset NUMBER(38) DEFAULT 1;
2311 v_chunksize INTEGER;
2312
2313 l_oj VARCHAR2(10);
2314 l_owner VARCHAR2(30);
2315 l_api_name VARCHAR2(40):= 'GenerateSysView';
2316 l_prg_msg VARCHAR2(2000);
2317 l_view_name VARCHAR2(30);
2318
2319 l_tmp_string_dc VARCHAR2(30);
2320 l_tmp_string_dn VARCHAR2(30);
2321 l_tmp_string_id VARCHAR2(30);
2322
2323 FUNCTION get_alias(p_tab_name IN VARCHAR2,
2324 p_alias IN VARCHAR2 )
2325
2326 RETURN VARCHAR2 IS
2327
2328 l_alias VARCHAR2(10);
2329 l_tab_name VARCHAR2(30);
2330
2331 BEGIN
2332
2333
2334
2335 l_alias := p_alias || SUBSTR(p_tab_name,1,1);
2336
2337 IF INSTR(p_tab_name,'_') > 0 THEN
2338 l_tab_name := SUBSTR(p_tab_name,INSTR(p_tab_name,'_')+1,LENGTH(p_tab_name));
2339 l_alias := get_alias(l_tab_name,l_alias);
2340 END IF;
2341
2342
2343
2344
2345 RETURN l_alias;
2346
2347 END get_alias;
2348
2349 ------------------------------------------------
2350 -- end get_alias returns an alias for a table --
2354 -- get_alias returns number of times a table
2351 ------------------------------------------------
2352
2353 --------------------------------------------
2355 -- has been repeated in FROM clause
2356 -- concatenated with table alias
2357 -- Output: FCL2
2358 --------------------------------------------
2359
2360 PROCEDURE get_alias(p_attr_detail_rec IN OUT NOCOPY attr_list_arr,
2361 p_tab_name IN VARCHAR2,
2362 p_alias OUT NOCOPY VARCHAR2)
2363 IS
2364
2365 i NUMBER;
2366 l_count NUMBER;
2367 l_where NUMBER;
2368
2369 BEGIN
2370
2371 i := 0;
2372 l_count := 1;
2373 l_where := 1;
2374
2375 IF p_attr_detail_rec.EXISTS(1) THEN
2376 FOR i IN p_attr_detail_rec.FIRST .. p_attr_detail_rec.LAST LOOP
2377 IF p_attr_detail_rec(i).attribute_tab_name = p_tab_name THEN
2378 l_count := p_attr_detail_rec(i).attribute_tab_count + 1;
2379 l_where := i;
2380 EXIT;
2381 ELSE
2382 l_where := l_where + 1;
2383 END IF;
2384 END LOOP;
2385 END IF;
2386
2387 p_attr_detail_rec(l_where).attribute_tab_name := p_tab_name;
2388
2389 p_attr_detail_rec(l_where).attribute_tab_count := l_count;
2390
2391 p_alias := get_alias(p_attr_detail_rec(l_where).attribute_tab_name,'') || TO_CHAR(l_count);
2392
2393
2394
2395 END get_alias;
2396
2397 FUNCTION check_dim_rec(p_dimension_id IN NUMBER)
2398 RETURN BOOLEAN IS
2399
2400 exist_status BOOLEAN := FALSE;
2401
2402 BEGIN
2403
2404 IF g_dim_tab.EXISTS(1) THEN
2405 FOR i IN 1..g_dim_tab.COUNT LOOP
2406 IF g_dim_tab(i) = p_dimension_id THEN
2407 exist_status := TRUE;
2408 END IF;
2409 END LOOP;
2410
2411 END IF;
2412
2413 IF NOT exist_status THEN
2414 g_dim_tab(g_dim_tab.COUNT + 1) := p_dimension_id;
2415 END IF;
2416
2417 RETURN exist_status;
2418
2419 END check_dim_rec;
2420
2421 FUNCTION check_mem_dc_rec(p_mem_dc_code IN VARCHAR2)
2422 RETURN BOOLEAN IS
2423
2424 exist_status BOOLEAN := FALSE;
2425
2426 BEGIN
2427
2428 IF g_mem_dc_code_tab.EXISTS(1) THEN
2429 FOR i IN 1..g_mem_dc_code_tab.COUNT LOOP
2430 IF g_mem_dc_code_tab(i) = p_mem_dc_code THEN
2431 exist_status := TRUE;
2432 END IF;
2433 END LOOP;
2434
2435 END IF;
2436
2437 IF NOT exist_status THEN
2438 g_mem_dc_code_tab(g_mem_dc_code_tab.COUNT + 1) := p_mem_dc_code;
2439 END IF;
2440
2441 RETURN exist_status;
2442
2443 END check_mem_dc_rec;
2444
2445 FUNCTION check_view_column(p_view_col IN VARCHAR2)
2446 RETURN BOOLEAN IS
2447
2448 exist_status BOOLEAN := FALSE;
2449
2450 BEGIN
2451
2452 IF g_view_col_tab.EXISTS(1) THEN
2453 FOR i IN 1..g_view_col_tab.COUNT LOOP
2454 IF g_view_col_tab(i) = p_view_col THEN
2455 exist_status := TRUE;
2456 END IF;
2457 END LOOP;
2458
2459 END IF;
2460
2461 IF NOT exist_status THEN
2462 g_view_col_tab(g_view_col_tab.COUNT + 1) := p_view_col;
2463 END IF;
2464
2465 RETURN exist_status;
2466
2467 END check_view_column;
2468
2469 FUNCTION get_view_col_alias(p_view_col IN VARCHAR2,p_col_suffix IN VARCHAR2)
2470 RETURN VARCHAR2 IS
2471
2472 l_view_column VARCHAR2(30):=p_view_col;
2473 i NUMBER:=1;
2474
2475 BEGIN
2476
2477 While(check_view_column(l_view_column)) LOOP
2478
2479 l_view_column:=SUBSTR(l_view_column,1,24)||'_'||p_col_suffix||i;
2480 i:=i+1;
2481
2482 END LOOP;
2483
2484 RETURN l_view_column;
2485
2486 END get_view_col_alias;
2487
2488
2489 BEGIN
2490
2491 UPDATE fem_tables_b set di_view_name = NULL where table_name=p_tab_name;
2492
2493 COMMIT; --Set di view name to null in begining
2494
2495
2496 FEM_ENGINES_PKG.TECH_Message (
2497 p_severity => g_log_level_1
2498 ,p_module => G_BLOCK||'.'||l_api_name
2499 ,p_msg_text => 'BEGIN'
2500 );
2501
2502 l_tab_alias := get_alias(p_tab_name,'');
2503 from_clause := p_tab_name || ' ' || l_tab_alias;
2504
2505 SELECT table_owner
2506 INTO l_owner
2507 FROM user_synonyms
2508 WHERE synonym_name = p_tab_name;
2509
2510 l_view_name:=p_view_name;
2511
2512 IF l_view_name is NULL THEN
2513
2514 l_view_name:=SUBSTR(p_tab_name,1,26)||'_TRV';
2515
2516 END IF;
2517
2518 FEM_ENGINES_PKG.USER_Message (
2519 p_msg_text => 'Table Name::'||p_tab_name||'::::'||'View Name ::'||l_view_name
2520 );
2521
2522 SELECT ftcv.column_name,
2523 NVL(ftcv.dimension_id, -1),
2524 nullable,
2525 display_name
2526 BULK COLLECT INTO gs_col_name_tab,
2530 FROM fem_tab_columns_vl ftcv,
2527 gs_dim_id_tab,
2528 gs_null_flag_tab,
2529 gs_disp_name_tab
2531 dba_tab_columns dtc
2532 WHERE ftcv.table_name = dtc.table_name
2533 AND ftcv.column_name = dtc.column_name
2534 AND dtc.owner = l_owner
2535 AND ftcv.enabled_flag='Y'
2536 AND (EXISTS
2537 (
2538 SELECT column_name
2539 FROM fem_tab_column_prop
2540 WHERE column_property_code='PROCESSING_KEY'
2541 AND table_name = ftcv.table_name
2542 AND column_name = ftcv.column_name)
2543 OR
2544 (dtc.nullable='N' and ftcv.fem_data_type_code = 'DIMENSION')
2545 OR
2546 (ftcv.fem_data_type_code = 'BALANCE'))
2547 AND dtc.table_name = p_tab_name
2548 ORDER BY NVL(ftcv.dimension_id, -1) asc;
2549
2550
2551 DELETE FROM FEM_SVIEW_COLUMNS WHERE view_name=l_view_name;
2552
2553 --First loop through all columns and prepare select list and populate fem_sview_columns
2554 FOR i IN 1..gs_col_name_tab.COUNT LOOP
2555
2556
2557 l_tmp_string_id := get_view_col_alias(gs_col_name_tab(i),'ID');
2558
2559 select_list := select_list || ',' || l_tab_alias || '.' ||gs_col_name_tab(i)||' '|| l_tmp_string_id;
2560
2561 insert into fem_sview_columns (view_name,tbl_column_name,dimension_id,disp_code_column, disp_name_column) values(l_view_name,l_tmp_string_id,null,l_tmp_string_id,l_tmp_string_id);
2562
2563 END LOOP;
2564
2565 --Again loop and update fem_sview_columns..this is to avoid duplicate columns name in View.
2566
2567 FOR i IN 1..gs_col_name_tab.COUNT LOOP
2568
2569 IF gs_dim_id_tab(i) <> -1 OR (gs_col_name_tab(i) NOT IN ('LAST_UPDATED_BY_OBJECT_ID',
2570 'LAST_UPDATED_BY_REQUEST_ID',
2571 'CREATED_BY_OBJECT_ID',
2572 'CREATED_BY_REQUEST_ID')
2573 AND gs_dim_id_tab(i) > 0 )
2574 THEN
2575
2576 FOR metadata_rec IN (SELECT member_display_code_col, member_name_col,
2577 member_vl_object_name, member_col, value_set_required_flag
2578 FROM fem_xdim_dimensions
2579 WHERE dimension_id = gs_dim_id_tab(i))
2580 LOOP
2581
2582 j := j + 1;
2583
2584 get_alias(attr_list_tbl, metadata_rec.member_vl_object_name, l_vl_tab_alias);
2585
2586 l_vl_tab_alias := l_vl_tab_alias || j;
2587
2588 --l_tmp_string_id := get_view_col_alias(gs_col_name_tab(i),'ID');
2589
2590 --select_list := select_list || ',' || l_tab_alias || '.' || l_tmp_string_id;
2591
2592 IF gs_null_flag_tab(i) = 'Y' THEN
2593 l_oj := '(+)';
2594 ELSE
2595 l_oj := '';
2596 END IF;
2597
2598 l_tmp_string_dc := get_view_col_alias(metadata_rec.member_display_code_col,'DC');
2599
2600 select_list := select_list || ',' || l_vl_tab_alias || '.' || metadata_rec.member_display_code_col ||' '||l_tmp_string_dc;
2601
2602 l_tmp_string_dn := get_view_col_alias(metadata_rec.member_name_col,'DN');
2603
2604 select_list := select_list || ',' || l_vl_tab_alias || '.' || metadata_rec.member_name_col ||' '||l_tmp_string_dn ;
2605
2606
2607 UPDATE fem_sview_columns SET dimension_id = gs_dim_id_tab(i), disp_code_column = l_tmp_string_dc, disp_name_column = l_tmp_string_dn
2608 WHERE view_name = l_view_name
2609 AND tbl_column_name = gs_col_name_tab(i);
2610
2611
2612
2613 /*IF check_dim_rec(gs_dim_id_tab(i)) OR (check_mem_dc_rec(metadata_rec.member_display_code_col)) OR (gs_col_name_tab(i) = metadata_rec.member_display_code_col) THEN
2614 select_list := select_list || ',' || l_vl_tab_alias || '.' || metadata_rec.member_display_code_col || ' ' || SUBSTR(gs_col_name_tab(i),1,27) || '_DC';
2615 select_list := select_list || ',' || l_vl_tab_alias || '.' || metadata_rec.member_name_col || ' ' || SUBSTR(gs_col_name_tab(i),1,27) || '_DN';
2616
2617 insert into fem_sview_columns (view_name,tbl_column_name, dimension_id, disp_code_column, disp_name_column)
2618 values(l_view_name,gs_col_name_tab(i),gs_dim_id_tab(i),
2619 SUBSTR(gs_col_name_tab(i),1,27) || '_DC',SUBSTR(gs_col_name_tab(i),1,27) || '_DN');
2620
2621 ELSE
2622 select_list := select_list || ',' || l_vl_tab_alias || '.' || metadata_rec.member_display_code_col;
2623 select_list := select_list || ',' || l_vl_tab_alias || '.' || metadata_rec.member_name_col ;
2624
2625
2626 insert into fem_sview_columns (view_name,tbl_column_name, dimension_id, disp_code_column, disp_name_column)
2627 values(l_view_name,gs_col_name_tab(i),gs_dim_id_tab(i),
2628 metadata_rec.member_display_code_col,metadata_rec.member_name_col);
2629 END IF;*/
2630
2631 from_clause := from_clause || ',' || metadata_rec.member_vl_object_name || ' ' || l_vl_tab_alias;
2632
2633 where_clause := where_clause || ' AND ' || l_vl_tab_alias || '.' || metadata_rec.member_col || l_oj || ' = ' || l_tab_alias || '.' || gs_col_name_tab(i);
2634
2635 END LOOP;
2636
2637 END IF;
2638
2639 END LOOP;
2640
2641
2642 IF select_list is NOT NULL THEN
2643 select_list := ' SELECT ' || RTRIM(LTRIM(select_list,','),',');
2644
2645 FEM_ENGINES_PKG.USER_Message (
2646 p_msg_text => 'Preparing Select Clause::'||select_list
2647 );
2648
2649 END IF;
2650
2651 IF from_clause is NOT NULL THEN
2652 from_clause := ' FROM ' || LTRIM(from_clause,',');
2653
2654 FEM_ENGINES_PKG.USER_Message (
2655 p_msg_text => 'Preparing From Clause::'||from_clause
2656 );
2657
2658 END IF;
2659
2660 IF where_clause is NOT NULL THEN
2661 where_clause := ' WHERE ' || LTRIM(where_clause,' AND ');
2662
2663 FEM_ENGINES_PKG.user_Message (
2664 p_msg_text => 'Preparing Where Clause::'||where_clause
2665 );
2666 END IF;
2667
2668 FEM_ENGINES_PKG.Tech_Message (
2669 p_severity => g_log_level_2
2670 ,p_module => G_BLOCK||'.'||l_api_name
2671 ,p_msg_text => 'Select, From and Where clauses are prepared'
2672 );
2673
2674
2675 IF select_list is NOT NULL THEN
2676 EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || l_view_name||' AS '|| select_list || from_clause || where_clause;
2677
2678
2679 FEM_ENGINES_PKG.Tech_Message (
2680 p_severity => G_LOG_LEVEL_2
2681 ,p_module => G_BLOCK||'.'||l_api_name
2682 ,p_msg_text => 'View has been created successfully'||l_view_name
2683 );
2684
2685 FEM_ENGINES_PKG.User_Message (
2686 p_app_name => G_FEM
2687 ,p_msg_name => 'FEM_SYS_VIEW_SUCCESS'
2688 ,p_token1 => 'VIEW_NAME'
2689 ,p_value1 => l_view_name
2690 ,p_token2 => 'TABLE_NAME'
2691 ,p_value2 => p_tab_name );
2692
2693 FEM_ENGINES_PKG.Tech_Message (
2694 p_severity => G_LOG_LEVEL_1
2695 ,p_module => G_BLOCK||'.'||l_api_name
2696 ,p_msg_text => 'END'
2697 );
2698
2699 UPDATE fem_tables_b set di_view_name = l_view_name where table_name=p_tab_name;
2700
2701 retcode:=0; --Set the status to success
2702 COMMIT;
2703
2704 ELSE
2705
2706 FEM_ENGINES_PKG.User_Message (
2707 p_app_name => G_FEM
2708 ,p_msg_name => 'FEM_SYS_VIEW_CREATION_FAIL'
2709 ,p_token1 => 'TABLE_NAME'
2710 ,p_value1 => p_tab_name);
2711
2712 ROLLBACK;
2713
2714 retcode:=2;--Set the status to Error
2715
2716 END IF;
2717
2718 EXCEPTION
2719
2720 WHEN others THEN
2721 l_prg_msg:=SQLERRM;
2722
2723 FEM_ENGINES_PKG.User_Message (
2724 p_app_name => G_FEM
2725 ,p_msg_name => 'FEM_SYS_VIEW_ERROR'
2726 ,p_token1 => 'TABLE_NAME'
2727 ,p_value1 => p_tab_name
2728 ,p_token2 => 'ERR_MSG'
2729 ,p_value2 => l_prg_msg );
2730
2731 FEM_ENGINES_PKG.Tech_Message (
2732 p_severity => G_LOG_LEVEL_1
2733 ,p_module => G_BLOCK||'.'||l_api_name
2734 ,p_msg_text => 'View Creation failed with unexpected exception'||l_prg_msg
2735 );
2736
2737 ROLLBACK;
2738 retcode:=2;--Set the status to Error
2739
2740 END;
2741
2742 /*============================================================================+
2743 | PROCEDURE
2744 | GenerateAllViews
2745 |
2746 | DESCRIPTION
2747 | This proc is used for generating Sys Views for all tables for which DI View
2748 | is not generated. This will be used for existing customers.
2749 |
2750 |
2751 | SCOPE - PUBLIC
2752 +============================================================================*/
2753
2754 PROCEDURE GenerateAllViews(errbuf OUT NOCOPY VARCHAR2
2755 ,retcode OUT NOCOPY VARCHAR2)
2756
2757 AS
2758
2759 l_retcode NUMBER:=0;
2760 x_retcode NUMBER:=0;
2761
2762 l_di_view_name VARCHAR2(30);
2763 counter NUMBER:=0;
2764
2765 CURSOR all_reg_tables IS
2766 SELECT table_name
2767 FROM fem_tables_vl ftc
2768 WHERE enabled_flag='Y'
2769 AND di_view_name is null
2770 AND EXISTS(
2771 select 1
2772 FROM user_synonyms
2773 where synonym_name = ftc.table_name);
2774
2775
2776 BEGIN
2777
2778 retcode:=0;
2779 FOR table_rec IN all_reg_tables LOOP
2780
2781 l_di_view_name := SUBSTR(table_rec.table_name,1,26)||'_TRV';
2782
2783 GenerateSysView(errbuf =>errbuf
2784 ,retcode =>l_retcode
2785 ,p_tab_name =>table_rec.table_name
2786 ,p_view_name =>l_di_view_name);
2787
2788 x_retcode:=x_retcode+l_retcode;
2789 counter:=counter+1;
2790
2791 END LOOP;
2792 /*
2793 IF all view creation fails then error
2794 IF all view creation success then success
2795 IF atleast one view creation is success then warning
2796 */
2797 IF x_retcode<>0 AND x_retcode/2=counter THEN
2798 retcode:=2;
2799 END IF;
2800 IF x_retcode<>0 AND x_retcode/2<counter THEN
2801 retcode:=1;
2802 END IF;
2803
2804 END;
2805
2806
2807 END fem_table_registration_pkg;