PACKAGE BODY: APPS.BSC_SYS_DIMTABLES_PKG

Source

1 PACKAGE BODY BSC_SYS_DIMTABLES_PKG as
2 /* $Header: BSCSDIMB.pls 115.2 2003/01/14 22:50:48 meastmon ship $ */
3 
4 
5 PROCEDURE ADD_LANGUAGE
6 IS
7     h_sql VARCHAR2(32000);
8 
9     CURSOR c_dim_tables IS
10         SELECT level_table_name
11         FROM bsc_sys_dim_levels_b
12         WHERE nvl(edw_flag, 0) = 0 AND
13               nvl(source, 'BSC') = 'BSC';
14 
15    h_dim_table VARCHAR2(30);
16 
17    CURSOR c_columns IS
18        SELECT column_name
19        FROM all_tab_columns
20        WHERE table_name = UPPER(h_dim_table) AND
21              owner = UPPER(BSC_APPS.BSC_APPS_SCHEMA);
22 
23    h_column_name VARCHAR2(30);
24    h_lst_insert VARCHAR2(32000);
25    h_lst_select VARCHAR2(32000);
26 
27 BEGIN
28     -- Initialize BSC/APPS global variables
29     BSC_APPS.Init_Bsc_Apps;
30 
31     -- Add language in each dimension table
32     OPEN c_dim_tables;
33     FETCH c_dim_tables INTO h_dim_table;
34     WHILE c_dim_tables%FOUND LOOP
35         h_sql := 'UPDATE '||h_dim_table||' T'||
36                  ' SET NAME = ('||
37                  '   SELECT B.NAME'||
38                  '   FROM '||h_dim_table||' B'||
39                  '   WHERE B.CODE = T.CODE AND'||
40                  '         B.LANGUAGE = T.SOURCE_LANG'||
41                  ' )'||
42                  ' WHERE (T.CODE, T.LANGUAGE) IN ('||
43                  '   SELECT SUBT.CODE, SUBT.LANGUAGE'||
44                  '   FROM '||h_dim_table||' SUBB, '||h_dim_table||' SUBT'||
45                  '   WHERE SUBB.CODE = SUBT.CODE AND'||
46                  '         SUBB.LANGUAGE = SUBT.SOURCE_LANG AND'||
47                  '         SUBB.NAME <> SUBT.NAME'||
48                  '   )';
49         EXECUTE IMMEDIATE h_sql;
50 
51         h_lst_insert := NULL;
52         h_lst_select := NULL;
53 
54         OPEN c_columns;
55         FETCH c_columns INTO h_column_name;
56         WHILE c_columns%FOUND LOOP
57             IF h_lst_insert IS NOT NULL THEN
58                 h_lst_insert := h_lst_insert||', ';
59                 h_lst_select := h_lst_select||', ';
60             END IF;
61 
62             h_lst_insert := h_lst_insert||h_column_name;
63 
64             IF h_column_name = 'LANGUAGE' THEN
65                 h_lst_select := h_lst_select||'L.LANGUAGE_CODE';
66             ELSE
67                 h_lst_select := h_lst_select||'B.'||h_column_name;
68             END IF;
69 
70             FETCH c_columns INTO h_column_name;
71         END LOOP;
72         CLOSE c_columns;
73 
74         h_sql := 'INSERT INTO '||h_dim_table||' ('||h_lst_insert||')'||
75                  ' SELECT '||h_lst_select||
76                  ' FROM '||h_dim_table||' B, FND_LANGUAGES L'||
77                  ' WHERE L.INSTALLED_FLAG IN (''I'', ''B'') AND'||
78                  '       B.LANGUAGE = USERENV(''LANG'') AND'||
79                  '       NOT EXISTS ('||
80                  '         SELECT NULL'||
81                  '         FROM '||h_dim_table||' T'||
82                  '         WHERE T.CODE = B.CODE AND'||
83                  '               T.LANGUAGE = L.LANGUAGE_CODE'||
84                  '       )';
85         EXECUTE IMMEDIATE h_sql;
86 
87         FETCH c_dim_tables INTO h_dim_table;
88     END LOOP;
89     CLOSE c_dim_tables;
90 
91 END ADD_LANGUAGE;
92 
93 
94 END BSC_SYS_DIMTABLES_PKG;