[Home] [Help]
PACKAGE BODY: APPS.BSC_DESIGNER_PVT
Source
1 PACKAGE BODY BSC_DESIGNER_PVT AS
2 /* $Header: BSCDSGB.pls 120.14 2007/06/29 08:29:01 ankgoel ship $ */
3
4 g_kpi_metadata_tables t_kpi_metadata_tables;
5 g_num_kpi_metadata_tables NUMBER := 0;
6 g_obj_kpi_metadata_tables t_kpi_metadata_tables;
7 g_num_obj_kpi_metadata_tables NUMBER := 0;
8
9
10 PROCEDURE Init_Kpi_Metadata_Tables_Array IS
11 BEGIN
12
13 /* TABLES TO BE COPIED BASED ON OBJECTIVE AND KPI_MEASURE_ID */
14 g_num_obj_kpi_metadata_tables := 0;
15
16 g_num_obj_kpi_metadata_tables := g_num_obj_kpi_metadata_tables + 1;
17 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).table_name := 'BSC_KPI_ANALYSIS_MEASURES_B';
18 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).table_type := C_KPI_MEAS_TABLE;
19 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).table_column := C_INDICATOR;
20 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
21 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).mls_table := bsc_utility.NO;
22 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).copy_type := C_MASTER_KPI;
23
24 g_num_obj_kpi_metadata_tables := g_num_obj_kpi_metadata_tables + 1;
25 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).table_name := 'BSC_KPI_MEASURE_PROPS';
26 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).table_type := C_KPI_MEAS_TABLE;
27 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).table_column := C_INDICATOR;
28 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
29 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).mls_table := bsc_utility.NO;
30 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).copy_type := C_MASTER_KPI;
31
32 g_num_obj_kpi_metadata_tables := g_num_obj_kpi_metadata_tables + 1;
33 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).table_name := 'BSC_COLOR_TYPE_PROPS';
34 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).table_type := C_KPI_MEAS_TABLE;
35 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).table_column := C_INDICATOR;
36 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
37 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).mls_table := bsc_utility.NO;
38 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).copy_type := C_MASTER_KPI;
39
40 g_num_obj_kpi_metadata_tables := g_num_obj_kpi_metadata_tables + 1;
41 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).table_name := 'BSC_KPI_MEASURE_WEIGHTS';
42 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).table_type := C_KPI_MEAS_TABLE;
43 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).table_column := C_INDICATOR;
44 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
45 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).mls_table := bsc_utility.NO;
46 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).copy_type := C_MASTER_KPI;
47
48 /* No need to copy calculated color data since the Shared Objective and KPIs
49 are put to prototype mode, when duplicated.
50 g_num_obj_kpi_metadata_tables := g_num_obj_kpi_metadata_tables + 1;
51 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).table_name := 'BSC_SYS_KPI_COLORS';
52 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).table_type := C_KPI_MEAS_TABLE;
53 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).table_column := C_INDICATOR;
54 g_obj_kpi_metadata_tables(g_num_obj_kpi_metadata_tables).duplicate_data := bsc_utility.YES;*/
55
56 /* TABLES TO BE COPIED BASED ON OBJECTIVE */
57 g_num_kpi_metadata_tables := 0;
58
59 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
60 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPIS_B';
61 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE;
62 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR;
63 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
64 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
65 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
66
67 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
68 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPIS_TL';
69 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
70 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
71 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
72 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.YES;
73 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
74
75 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
76 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_ANALYSIS_GROUPS';
77 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
78 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
79 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
80 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
81 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
82
83 /*g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
84 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_ANALYSIS_MEASURES_B';
85 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
86 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
87 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;*/
88
89 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
90 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_ANALYSIS_MEASURES_TL';
91 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
92 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
93 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
94 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.YES;
95 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
96
97 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
98 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_ANALYSIS_OPTIONS_B';
99 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
100 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
101 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
102 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
103 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
104
105 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
106 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_ANALYSIS_OPTIONS_TL';
107 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
108 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
109 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
110 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.YES;
111 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
112
113 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
114 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_ANALYSIS_OPT_USER';
115 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
116 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
117 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
118 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
119 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;
120
121 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
122 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_CALCULATIONS';
123 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
124 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
125 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
126 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
127 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
128
129 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
130 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_CALCULATIONS_USER';
131 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
132 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
133 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
134 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
135 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;
136
137 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
138 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_CAUSE_EFFECT_RELS';
139 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
140 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_CAUSE_INDICATOR ;
141 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
142 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
143 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;
144
145 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
146 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_CAUSE_EFFECT_RELS';
147 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
148 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_EFFECT_INDICATOR ;
149 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
150 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
151 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;
152
153 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
154 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_DATA_TABLES';
155 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
156 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
157 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
158 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
159 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;
160
161 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
162 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_DEFAULTS_B';
163 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
164 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
165 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
166 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
167 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
168
169 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
170 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_DEFAULTS_TL';
171 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
172 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
173 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
174 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.YES;
175 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
176
177 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
178 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_DIM_GROUPS';
179 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
180 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
181 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
182 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
183 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_SHARED_KPI;
184
185 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
186 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_DIM_LEVELS_B';
187 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
188 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
189 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
190 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
191 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;
192
193 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
194 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_DIM_LEVELS_TL';
195 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
196 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
197 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
198 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.YES;
199 --g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
200 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;
201
202 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
203 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_DIM_LEVELS_USER';
204 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
205 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
206 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
207 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
208 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;
209
210 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
211 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_DIM_LEVEL_PROPERTIES';
212 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
213 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
214 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
215 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
216 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;
217
218 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
219 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_DIM_SETS_TL';
220 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
221 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
222 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
223 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.YES;
224 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
225
226 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
227 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_GRAPHS';
228 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
229 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
230 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
231 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
232 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;
233
234 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
235 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_MM_CONTROLS';
236 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
237 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
238 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
239 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
240 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
241
242 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
243 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_PERIODICITIES';
244 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
245 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
246 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
247 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
248 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
249
250 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
251 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_PERIODICITIES_USER';
252 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
253 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
254 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
255 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
256 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_SHARED_KPI;
257
258 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
259 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_PROPERTIES';
260 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
261 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
262 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
263 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
264 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
265
266 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
267 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_SERIES_COLORS';
268 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
269 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
270 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
271 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
272 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
273
274 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
275 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_SHELL_CMDS_TL';
276 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
277 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
278 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
279 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.YES;
280 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
281
282 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
283 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_SHELL_CMDS_USER';
284 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
285 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
286 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
287 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
288 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;
289
290 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
291 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_SUBTITLES_TL';
292 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
293 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
294 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
295 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.YES;
296 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
297
298 --Removed this as the simulation tree tables are copied separately
299 /*g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
300 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_TREE_NODES_B';
301 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
302 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
303 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
304 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
305 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
306
307 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
308 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_KPI_TREE_NODES_TL';
309 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
310 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
311 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
312 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.YES;
313 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;*/
314
315 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
316 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_SYS_FILES';
317 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
318 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
319 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
320 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
321 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;
322
323 /* No need to copy calculated color data since the Shared Objective and KPIs
324 are put to prototype mode, when duplicated.
325 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
326 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_SYS_KPI_COLORS';
327 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
328 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
329 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
330
331 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
332 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_SYS_OBJECTIVE_COLORS';
333 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
334 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
335 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;*/
336
337 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
338 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_SYS_LABELS_B';
339 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_SYSTEM_TABLE;
340 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_SOURCE_CODE ;
341 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
342 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
343 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
344
345 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
346 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_SYS_LABELS_TL';
347 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_SYSTEM_TABLE ;
348 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_SOURCE_CODE ;
349 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
350 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.YES;
351 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
352
353 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
354 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_SYS_LINES';
355 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_SYSTEM_TABLE ;
356 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_SOURCE_CODE ;
357 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
358 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
359 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
360
361 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
362 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_SYS_USER_OPTIONS';
363 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_SYSTEM_TABLE ;
364 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_SOURCE_CODE ;
365 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
366 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
367 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_MASTER_KPI;
368
369 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
370 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_TAB_INDICATORS';
371 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
372 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
373 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.NO;
374 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
375 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;
376
377 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
378 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_TAB_VIEW_KPI_TL';
379 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
380 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
381 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
382 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
383 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;
384
385 --Bug #5955966. This table should not be copied while creating a shared indicator
386 /*g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
387 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_USER_KPIGRAPH_PLUGS';
388 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
389 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
390 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
391 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
392 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;*/
393
394 g_num_kpi_metadata_tables := g_num_kpi_metadata_tables + 1;
395 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_name := 'BSC_USER_KPI_ACCESS';
396 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_type := C_KPI_TABLE ;
397 g_kpi_metadata_tables(g_num_kpi_metadata_tables).table_column := C_INDICATOR ;
398 g_kpi_metadata_tables(g_num_kpi_metadata_tables).duplicate_data := bsc_utility.YES;
399 g_kpi_metadata_tables(g_num_kpi_metadata_tables).mls_table := bsc_utility.NO;
400 g_kpi_metadata_tables(g_num_kpi_metadata_tables).copy_type := C_NO_COPY;
401
402 END Init_Kpi_Metadata_Tables_Array;
403 /********************************************************************************************/
404 PROCEDURE Copy_Record_by_Indicator_Table
405 ( p_table_name IN VARCHAR2
406 , p_table_type IN VARCHAR2
407 , p_table_column IN VARCHAR2
408 , p_Src_kpi IN NUMBER
409 , p_Trg_kpi IN NUMBER
410 )IS
411
412 CURSOR c_column IS
413 SELECT column_name
414 FROM all_tab_columns
415 WHERE table_name = p_table_name
416 AND owner = DECODE(USER,BSC_APPS.get_user_schema('APPS'),BSC_APPS.get_user_schema,USER)
417 ORDER BY column_name;
418
419 h_Trg_kpi_neg NUMBER;
420
421 CURSOR c_userwizard IS
422 SELECT ANALYSIS_GROUP_ID
423 ,OPTION_ID
424 ,PARENT_OPTION_ID
425 ,GRANDPARENT_OPTION_ID
426 ,USER_LEVEL0
427 ,USER_LEVEL1
428 ,USER_LEVEL1_DEFAULT
429 ,USER_LEVEL2
430 ,USER_LEVEL2_DEFAULT
431 FROM BSC_KPI_ANALYSIS_OPTIONS_B
432 WHERE INDICATOR = h_Trg_kpi_neg;
433
434 CURSOR c_Dim_level IS
435 SELECT DIM_SET_ID
436 ,DIM_LEVEL_INDEX
437 ,USER_LEVEL0
438 ,USER_LEVEL1
439 ,USER_LEVEL1_DEFAULT
440 ,USER_LEVEL2
441 ,USER_LEVEL2_DEFAULT
442 ,LEVEL_VIEW_NAME
443 FROM BSC_KPI_DIM_LEVELS_B
444 WHERE INDICATOR = h_Trg_kpi_neg;
445
446 CURSOR c_Periodicity IS
447 SELECT PERIODICITY_ID
448 ,USER_LEVEL0
449 ,USER_LEVEL1
450 ,USER_LEVEL1_DEFAULT
451 ,USER_LEVEL2
452 ,USER_LEVEL2_DEFAULT
453 FROM BSC_KPI_PERIODICITIES
454 WHERE INDICATOR = h_Trg_kpi_neg;
455
456 CURSOR c_Calculation IS
457 SELECT CALCULATION_ID
458 ,USER_LEVEL0
459 ,USER_LEVEL1
460 ,USER_LEVEL1_DEFAULT
461 ,USER_LEVEL2
462 ,USER_LEVEL2_DEFAULT
463 FROM BSC_KPI_CALCULATIONS
464 WHERE INDICATOR = h_Trg_kpi_neg;
465
466 CURSOR c_Dim_Level_Properties IS
467 SELECT DIM_SET_ID
468 ,DIM_LEVEL_ID
469 ,USER_LEVEL0
470 ,USER_LEVEL1
471 ,USER_LEVEL1_DEFAULT
472 ,USER_LEVEL2
473 ,USER_LEVEL2_DEFAULT
474 FROM BSC_KPI_DIM_LEVEL_PROPERTIES
475 WHERE INDICATOR = h_Trg_kpi_neg;
476
477
478 h_colum VARCHAR2(100);
479 h_key_name VARCHAR2(30);
480 h_condition VARCHAR2(1000);
481 h_sql VARCHAR2(32000);
482 x_arr_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
483 x_num_columns NUMBER;
484 i NUMBER;
485 h_ag NUMBER;
486 h_aO NUMBER;
487 h_aOP NUMBER;
488 h_aOG NUMBER;
489 h_usl NUMBER;
490 h_count NUMBER := 0;
491 BEGIN
492 -- Initialize BSC/APPS global variables
493 BSC_APPS.Init_Bsc_Apps;
494
495 h_key_name := 'INDICATOR';
496 IF( p_table_column = C_SOURCE_CODE )THEN
497 h_key_name := 'SOURCE_CODE';
498 h_condition := 'SOURCE_TYPE = 2 AND ' || h_key_name || '=' || p_Trg_kpi;
499 ELSIF (p_table_column = C_INDICATOR) THEN
500 h_condition := 'INDICATOR =' || p_Trg_kpi;
501 ELSE
502 h_condition := p_table_column ||' = ' || p_Trg_kpi;
503 END IF;
504
505 --Bug 2258410 don't override the user wizard preferences
506 --Move the record to negative . to later restore the values
507 h_Trg_kpi_neg := p_Trg_kpi * (-1);
508
509 h_sql := 'DELETE ' || p_table_name || ' WHERE ' || h_condition;
510 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
511
512 x_num_columns :=0;
513 OPEN c_column;
514 FETCH c_column INTO h_colum;
515 WHILE c_column%FOUND LOOP
516 x_num_columns := x_num_columns + 1;
517 x_arr_columns(x_num_columns) := h_colum;
518 FETCH c_column INTO h_colum;
519 END LOOP;
520 CLOSE c_column;
521
522 IF x_num_columns > 0 THEN
523 h_condition:= h_key_name || '=' || p_Src_kpi;
524 IF h_key_name = 'SOURCE_CODE' THEN
525 h_condition:= h_condition || ' AND SOURCE_TYPE=2';
526 END IF;
527 h_sql:= 'INSERT INTO ( SELECT ';
528 FOR i IN 1..x_num_columns LOOP
529 IF i <> 1 THEN
530 h_sql:= h_sql || ',';
531 END IF;
532 h_sql:= h_sql || x_arr_columns(i);
533 END LOOP;
534 h_sql:= h_sql || ' FROM ' || p_table_name;
535 h_sql:= h_sql || ' )';
536 h_sql:= h_sql || ' SELECT ';
537 FOR i IN 1..x_num_columns LOOP
538 IF i <> 1 THEN
539 h_sql:= h_sql || ',';
540 END IF;
541 IF UPPER(x_arr_columns(i)) = h_key_name THEN
542 h_sql:= h_sql || p_Trg_kpi || ' AS ' || x_arr_columns(i);
543 ELSE
544 h_sql:= h_sql || x_arr_columns(i) || ' AS ' || x_arr_columns(i);
545 END IF;
546 END LOOP;
547 h_sql:= h_sql || ' FROM ' || p_table_name;
548 h_sql:= h_sql || ' WHERE ' || h_condition;
549 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
550 --DBMS_OUTPUT.PUT_LINE(' Insertred into table :-' || p_table_name);
551 --BUG 1224991
552 IF UPPER(p_table_name) = 'BSC_KPI_DEFAULTS_B' Or UPPER(p_table_name) = 'BSC_KPI_DEFAULTS_TL' THEN
553 h_sql:= 'UPDATE ' || p_table_name;
554 h_sql:= h_sql || ' SET TAB_ID = (SELECT TAB_ID FROM BSC_TAB_INDICATORS WHERE INDICATOR =:1)'; --|| x_Trg_kpi || ')';
555 h_sql:= h_sql || ' WHERE INDICATOR = :2'; --|| x_Trg_kpi;
556 Execute Immediate h_sql USING p_Trg_kpi, p_Trg_kpi;
557 END IF;
558
559 --Bug 2258410 don't override the user wizard preferences
560 IF p_table_name = 'BSC_KPI_ANALYSIS_OPTIONS_B' THEN
561 -- Take the Values from the temporal (negative) records
562 FOR CD IN c_userwizard LOOP
563 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
564 SET USER_LEVEL0 = CD.USER_LEVEL0
565 ,USER_LEVEL1 = CD.USER_LEVEL1
566 ,USER_LEVEL1_DEFAULT = CD.USER_LEVEL1_DEFAULT
567 ,USER_LEVEL2 = CD.USER_LEVEL2
568 ,USER_LEVEL2_DEFAULT = CD.USER_LEVEL2_DEFAULT
569 WHERE INDICATOR = p_Trg_kpi
570 AND ANALYSIS_GROUP_ID = CD.ANALYSIS_GROUP_ID
571 AND OPTION_ID = CD.OPTION_ID
572 AND PARENT_OPTION_ID = CD.PARENT_OPTION_ID
573 AND GRANDPARENT_OPTION_ID = CD.GRANDPARENT_OPTION_ID;
574 END LOOP;
575 END IF;
576 IF p_table_name = 'BSC_KPI_DIM_LEVELS_B' THEN
577 --DBMS_OUTPUT.PUT_LINE(' UPDATEIN BSC_KPI_DIM_LEVELS_B:-');
578 FOR CD IN c_Dim_level LOOP
579 UPDATE BSC_KPI_DIM_LEVELS_B
580 SET USER_LEVEL0 = CD.USER_LEVEL0
581 ,USER_LEVEL1 = CD.USER_LEVEL1
582 ,USER_LEVEL1_DEFAULT = CD.USER_LEVEL1_DEFAULT
583 ,USER_LEVEL2 = CD.USER_LEVEL2
584 ,USER_LEVEL2_DEFAULT = CD.USER_LEVEL2_DEFAULT
585 ,LEVEL_VIEW_NAME = CD.LEVEL_VIEW_NAME
586 WHERE INDICATOR = p_Trg_kpi
587 AND DIM_SET_ID = CD.DIM_SET_ID
588 AND DIM_LEVEL_INDEX = CD.DIM_LEVEL_INDEX;
589 END LOOP;
590 END IF;
591
592 IF p_table_name = 'BSC_KPI_PERIODICITIES' THEN
593 --DBMS_OUTPUT.PUT_LINE(' UPDATEIN BSC_KPI_PERIODICITIES:-');
594 FOR CD IN c_Periodicity LOOP
595 UPDATE BSC_KPI_PERIODICITIES
596 SET USER_LEVEL0 = CD.USER_LEVEL0
597 ,USER_LEVEL1 = CD.USER_LEVEL1
598 ,USER_LEVEL1_DEFAULT = CD.USER_LEVEL1_DEFAULT
599 ,USER_LEVEL2 = CD.USER_LEVEL2
600 ,USER_LEVEL2_DEFAULT = CD.USER_LEVEL2_DEFAULT
601 WHERE INDICATOR = p_Trg_kpi
602 AND PERIODICITY_ID = CD.PERIODICITY_ID;
603 END LOOP;
604 END IF;
605
606 IF p_table_name = 'BSC_KPI_CALCULATIONS' THEN
607 --DBMS_OUTPUT.PUT_LINE(' UPDATEIN BSC_KPI_CALCULATIONS:-');
608 FOR CD IN c_Calculation LOOP
609 UPDATE BSC_KPI_CALCULATIONS
610 SET USER_LEVEL0 = CD.USER_LEVEL0
611 ,USER_LEVEL1 = CD.USER_LEVEL1
612 ,USER_LEVEL1_DEFAULT = CD.USER_LEVEL1_DEFAULT
613 ,USER_LEVEL2 = CD.USER_LEVEL2
614 ,USER_LEVEL2_DEFAULT = CD.USER_LEVEL2_DEFAULT
615 WHERE INDICATOR = p_Trg_kpi
616 AND CALCULATION_ID = CD.CALCULATION_ID;
617 END LOOP;
618 END IF;
619
620 IF p_table_name = 'BSC_KPI_DIM_LEVEL_PROPERTIES' THEN
621 --DBMS_OUTPUT.PUT_LINE(' UPDATEIN BSC_KPI_DIM_LEVEL_PROPERTIES:-');
622
623 FOR CD IN c_Dim_Level_Properties LOOP
624 UPDATE BSC_KPI_DIM_LEVEL_PROPERTIES
625 SET USER_LEVEL0 = CD.USER_LEVEL0
626 ,USER_LEVEL1 = CD.USER_LEVEL1
627 ,USER_LEVEL1_DEFAULT = CD.USER_LEVEL1_DEFAULT
628 ,USER_LEVEL2 = CD.USER_LEVEL2
629 ,USER_LEVEL2_DEFAULT = CD.USER_LEVEL2_DEFAULT
630 WHERE INDICATOR = p_Trg_kpi
631 AND DIM_SET_ID = CD.DIM_SET_ID
632 AND DIM_LEVEL_ID = CD.DIM_LEVEL_ID;
633 END LOOP;
634 END IF;
635
636 END IF;
637
638
639 EXCEPTION
640 WHEN OTHERS THEN
641 IF(c_userwizard%ISOPEN) THEN
642 CLOSE c_userwizard;
643 END IF;
644 IF(c_column%ISOPEN) THEN
645 CLOSE c_column;
646 END IF;
647 --DBMS_OUTPUT.PUT_LINE('error occured in s' || substr(SQLERRM,1,255));
648 BSC_MESSAGE.Add(x_message => SQLERRM,
649 x_source => 'Copy_Record_by_Indicator_Table');
650 RAISE;
651 END Copy_Record_by_Indicator_Table;
652
653
654
655
656 PROCEDURE Init_variables(x_indicator IN NUMBER) IS
657
658 CURSOR c_kpi IS
659 SELECT INDICATOR_TYPE,CONFIG_TYPE
660 FROM BSC_KPIS_B
661 WHERE INDICATOR = x_indicator;
662
663 h_msg VARCHAR2(1000);
664
665 BEGIN
666 --Need to initiliaze in order to Do_DDL works fine
667 BSC_APPS.Init_Bsc_Apps;
668
669 IF l_indicator = x_indicator THEN
670 --h_msg := 'Loaded';
671 --DBMS_OUTPUT.PUT_LINE(h_msg);
672 RETURN;
673 END IF;
674
675 l_indicator := x_indicator;
676 --h_msg := 'Initialzie' || l_indicator;
677 --DBMS_OUTPUT.PUT_LINE(h_msg);
678
679 --Get indicator configuration and type
680 OPEN c_kpi;
681 FETCH c_kpi INTO l_ind_type,l_ind_config;
682 CLOSE c_kpi;
683
684 --Init current user
685 l_current_user := 0;
686
687 SELECT LANGUAGE_CODE INTO
688 l_base_lang
689 FROM FND_LANGUAGES
690 WHERE INSTALLED_FLAG IN ('B');
691 --h_msg := 'Testing Indicator:' || l_indicator || '/Type:' || l_ind_type || '/Config:' || l_ind_config || '/user:' || l_current_user || '/Lan:' || l_base_lang;
692 --DBMS_OUTPUT.PUT_LINE(h_msg);
693
694 EXCEPTION
695 WHEN OTHERS THEN
696 --DBMS_OUTPUT.PUT_LINE('Init' || SQLERRM);
697 BSC_MESSAGE.Add(x_message => SQLERRM,
698 x_source => 'Init_variables');
699 END Init_variables;
700
701 /*===========================================================================+
702 | PROCEDURE
703 | Deflt_RefreshInvalidKpis
704 |
705 | PURPOSE
706 | It refresh the information in KPI_DEFAULTS table for all
707 | the invalid kpis.
708 | PARAMETERS
709 |
710 | HISTORY
711 | 12-NOV-2001 Henry Camacho Created
712 +---------------------------------------------------------------------------*/
713 PROCEDURE Deflt_RefreshInvalidKpis IS
714
715 CURSOR c_kpi_invalid IS
716 SELECT INDICATOR FROM BSC_KPIS_B
717 WHERE PROTOTYPE_FLAG<>2 AND
718 INDICATOR NOT IN (SELECT INDICATOR FROM BSC_KPI_DEFAULTS_VL);
719
720 h_msg VARCHAR2(1000);
721 h_indicator NUMBER;
722
723 BEGIN
724 --Clean invalid record in
725 DELETE BSC_KPI_DEFAULTS_B
726 WHERE (TAB_ID,INDICATOR) IN
727 (SELECT TAB_ID,INDICATOR
728 FROM BSC_KPI_DEFAULTS_B
729 WHERE (TAB_ID,INDICATOR) NOT IN
730 (SELECT TAB_ID,INDICATOR FROM BSC_TAB_INDICATORS));
731
732 --Need to initiliaze in order to Do_DDL works fine
733 BSC_APPS.Init_Bsc_Apps;
734 --Get indicator configuration and type
735 OPEN c_kpi_invalid;
736 FETCH c_kpi_invalid INTO h_indicator;
737 WHILE c_kpi_invalid%FOUND LOOP
738 -- Refresh the data for this kpi
739 Deflt_RefreshKpi(h_indicator);
740 FETCH c_kpi_invalid INTO h_indicator;
741 END LOOP;
742 CLOSE c_kpi_invalid ;
743 COMMIT;
744
745 EXCEPTION
746 WHEN OTHERS THEN
747 --DBMS_OUTPUT.PUT_LINE('a' || SQLERRM);
748 BSC_MESSAGE.Add(x_message => SQLERRM,
749 x_source => 'Deflt_RefreshInvalidKpis');
750 END Deflt_RefreshInvalidKpis;
751 /*===========================================================================+
752 | PROCEDURE
753 | Deflt_RefreshKpi
754 |
755 | PURPOSE
756 | It refresh the information in KPI_DEFAULTS table
757 | PARAMETERS
758 |
759 | HISTORY
760 | 12-NOV-2001 Henry Camacho Created
761 +---------------------------------------------------------------------------*/
762 PROCEDURE Deflt_RefreshKpi(x_indicator IN NUMBER) IS
763
764 CURSOR c_kpi IS
765 SELECT INDICATOR_TYPE,CONFIG_TYPE
766 FROM BSC_KPIS_B
767 WHERE INDICATOR = x_indicator;
768
769 h_msg VARCHAR2(1000);
770 h_exist NUMBER(1);
771
772 BEGIN
773
774 --Initiliaze the inetrnal variable by kpis
775 Init_variables(x_indicator);
776 --h_msg := 'Initialzie' || l_indicator;
777 --DBMS_OUTPUT.PUT_LINE(h_msg);
778 h_exist := 1;
779 --Get indicator configuration and type
780 OPEN c_kpi;
781 FETCH c_kpi INTO l_ind_type,l_ind_config;
782 IF NOT(c_kpi%FOUND) THEN
783 h_exist := 0;
784 END IF;
785 CLOSE c_kpi;
786 --DBMS_OUTPUT.PUT_LINE('exist:' || h_exist);
787 -- Execute all the steps only if the kpi exist
788 IF h_exist = 1 THEN
789 --h_msg := 'Testing Indicator:' || l_indicator || '/Type:' || l_ind_type || '/Config:' || l_ind_config || '/user:' || l_current_user || '/Lan:' || l_base_lang;
790 --DBMS_OUTPUT.PUT_LINE(h_msg);
791
792 --Reset Values
793 Deflt_Clear(x_indicator);
794 Deflt_Update_AOPTS(x_indicator);
795 Deflt_Update_SN_FM_CM(x_indicator);
796 Deflt_Update_DIM_SET(x_indicator);
797 Deflt_Update_DIM_VALUES(x_indicator);
798 Deflt_Update_DIM_NAMES(x_indicator);
799 Deflt_Update_PERIOD_NAME(x_indicator);
800 --COMMIT;
801 END IF;
802 EXCEPTION
803 WHEN OTHERS THEN
804 --DBMS_OUTPUT.PUT_LINE('c' || SQLERRM);
805 BSC_MESSAGE.Add(x_message => SQLERRM,
806 x_source => 'Deflt_RefreshKpi');
807 END Deflt_RefreshKpi;
808
809 /*===========================================================================+
810 | PROCEDURE
811 | Deflt_Clear
812 |
813 | PURPOSE
814 | Delete the previous and insert a clean records
815 | PARAMETERS
816 |
817 | HISTORY
818 | 12-NOV-2001 Henry Camacho Created
819 +---------------------------------------------------------------------------*/
820 PROCEDURE Deflt_Clear(x_indicator IN NUMBER) IS
821
822 CURSOR c_tab_kpi IS
823 SELECT TAB_ID
824 FROM BSC_TAB_INDICATORS
825 WHERE INDICATOR = l_indicator;
826
827 h_msg VARCHAR2(1000);
828 h_sql VARCHAR2(2000);
829 h_tab_id NUMBER;
830 h_tmp VARCHAR2(255); /* Bug Fix #2691601 changine size from 80 to 255 */
831 BEGIN
832
833 --Initiliaze the inetrnal variable by kpis
834 Init_variables(x_indicator);
835 --h_msg := 'Clear Initialzie' || l_indicator;
836 --DBMS_OUTPUT.PUT_LINE(h_msg);
837
838 OPEN c_tab_kpi;
839 FETCH c_tab_kpi INTO h_tab_id;
840 IF NOT(c_tab_kpi%FOUND) THEN
841 h_tab_id := -1;
842 END IF;
843 CLOSE c_tab_kpi;
844
845 --h_msg := 'Reset_BscKpiDefaults Tab:' || h_tab_id;
846 --DBMS_OUTPUT.PUT_LINE(h_msg);
847 --Delete Records
848 h_sql :='DELETE BSC_KPI_DEFAULTS_B WHERE INDICATOR= :1'; --|| l_indicator;
849 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
850 Execute Immediate h_sql USING l_indicator;
851 --DBMS_OUTPUT.PUT_LINE(h_sql);
852
853 h_sql :='DELETE BSC_KPI_DEFAULTS_TL WHERE INDICATOR= :1';-- || l_indicator;
854 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
855 EXECUTE IMMEDIATE h_sql USING l_indicator;
856 --DBMS_OUTPUT.PUT_LINE(h_sql);
857
858 -- Insert Defaults BSC_KPI_DEFAULTS_B
859 h_sql :='INSERT INTO BSC_KPI_DEFAULTS_B (TAB_ID,INDICATOR,' ||
860 'LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)' ||
861 'VALUES ('|| h_tab_id ||',' || l_indicator ||',SYSDATE,' || l_current_user ||',SYSDATE,' || l_current_user || ')';
862 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
863 --DBMS_OUTPUT.PUT_LINE(h_sql);
864
865 -- Insert Defaults BSC_KPI_DEFAULTS_TL
866 h_sql :='INSERT INTO BSC_KPI_DEFAULTS_TL (TAB_ID,INDICATOR,LANGUAGE,SOURCE_LANG)' ||
867 ' SELECT '|| h_tab_id ||' TAB_ID,' || l_indicator || ' INDICATOR,' ||
868 ' LANGUAGE_CODE,' || '''' || l_base_lang || '''' ||
869 ' FROM FND_LANGUAGES ' ||
870 ' WHERE INSTALLED_FLAG IN (''B'',''I'')';
871 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
872 --DBMS_OUTPUT.PUT_LINE(h_sql);
873 EXCEPTION
874 WHEN OTHERS THEN
875 --DBMS_OUTPUT.PUT_LINE('d' || SQLERRM);
876 BSC_MESSAGE.Add(x_message => SQLERRM,
877 x_source => 'Deflt_Clear');
878 END Deflt_Clear;
879
880 /*===========================================================================+
881 | PROCEDURE
882 |
883 |
884 | PURPOSE
885 | To update the ANALYSIS OPTIONS COLUMNS
886 | PARAMETERS
887 |
888 | HISTORY
889 | 12-NOV-2001 Henry Camacho Created
890 | 09-JAN-2003 Malcoln Leung bug fix #2740431
891 +---------------------------------------------------------------------------*/
892 PROCEDURE Deflt_Update_AOPTS(x_indicator IN NUMBER) IS
893
894
895 s_node NUMBER;
896 h_a_grp NUMBER;
897 h_a_opt NUMBER;
898 h_a_parent_opt NUMBER;
899 h_a_grandparent_opt NUMBER;
900
901 CURSOR c_kpi_tree IS
902 SELECT LANGUAGE,SOURCE_LANG,NAME
903 FROM BSC_KPI_TREE_NODES_TL
904 WHERE INDICATOR = l_indicator AND NODE_ID = s_node;
905
906 CURSOR c_color_AO_DEFAULT IS
907 SELECT A0_DEFAULT,A1_DEFAULT,A2_DEFAULT
908 FROM BSC_DB_COLOR_AO_DEFAULTS_V
909 WHERE INDICATOR = l_indicator;
910
911 CURSOR c_kpi_analysis IS
912 SELECT LANGUAGE,SOURCE_LANG,NAME
913 FROM BSC_KPI_ANALYSIS_OPTIONS_TL
914 WHERE INDICATOR = l_indicator AND
915 ANALYSIS_GROUP_ID = h_a_grp AND
916 --OPTION_ID = h_a_opt; //bug#2740431
917 OPTION_ID = h_a_opt AND
918 PARENT_OPTION_ID = h_a_parent_opt AND
919 GRANDPARENT_OPTION_ID =h_a_grandparent_opt;
920
921 --bug#2740431, check dependency between analysis groups
922 CURSOR c_kpi_group_dependency IS
923 SELECT ANALYSIS_GROUP_ID,DEPENDENCY_FLAG
924 FROM BSC_KPI_ANALYSIS_GROUPS
925 WHERE INDICATOR = l_indicator;
926
927
928 h_msg VARCHAR2(1000);
929 h_sql VARCHAR2(2000);
930 --
931 h_name BSC_KPI_ANALYSIS_OPTIONS_TL.NAME%TYPE;
932 h_lang VARCHAR2(4);
933 h_source_lang VARCHAR2(4);
934 h_tmp VARCHAR2(255); /* Bug Fix #2691601 changine size from 80 to 255 */
935 --
936 h_a0 NUMBER;
937 h_a1 NUMBER;
938 h_a2 NUMBER;
939
940 a_temp NUMBER;
941 dep_temp NUMBER;
942 dep_a0 NUMBER;
943 dep_a1 NUMBER;
944 dep_a2 NUMBER;
945
946
947
948
949 BEGIN
950 --Initiliaze the inetrnal variable by kpis
951 Init_variables(x_indicator);
952 --h_msg := 'Update aoptsInitialzie' || l_indicator;
953 --DBMS_OUTPUT.PUT_LINE(h_msg);
954
955 -- Set To Null----------------------------
956 h_sql := 'UPDATE BSC_KPI_DEFAULTS_TL SET ANALYSIS_OPTION0_NAME =NULL,' ||
957 ' ANALYSIS_OPTION1_NAME=NULL,ANALYSIS_OPTION2_NAME=NULL '||
958 ' WHERE INDICATOR = :1'; -- || l_indicator;
959 --DBMS_OUTPUT.PUT_LINE(h_sql);
960 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); -- bug 3111300
961 Execute Immediate h_sql USING l_indicator;
962
963
964 --Simulation Tree, Retrieve the default node captions----------------------------
965 IF l_ind_type = 1 AND l_ind_config = 7 THEN
966 --Init s_node
967 s_node := 0;
968 SELECT PROPERTY_VALUE
969 INTO s_node
970 FROM BSC_KPI_PROPERTIES WHERE PROPERTY_CODE='S_NODE_ID'
971 AND INDICATOR=l_indicator;
972 --h_msg := ' s_node:' || s_node;
973 --DBMS_OUTPUT.PUT_LINE(h_msg);
974
975 --Analysis 0----------------------------
976 OPEN c_kpi_tree ;
977 FETCH c_kpi_tree INTO h_lang,h_source_lang,h_name;
978 WHILE c_kpi_tree%FOUND LOOP
979 --08/30/02
980 h_tmp := REPLACE(h_name,'''','''''');
981 h_sql := 'UPDATE BSC_KPI_DEFAULTS_TL SET ' ||
982 'SOURCE_LANG = :1' || --'''' || h_source_lang || '''' ||
983 ',ANALYSIS_OPTION0_NAME= :2' || --'''' || h_tmp || '''' ||
984 ' WHERE INDICATOR= :3' || --l_indicator ||
985 ' AND LANGUAGE= :4'; --|| '''' || h_lang || '''';
986 --DBMS_OUTPUT.PUT_LINE('A' || h_sql);
987 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); -- bug 3111300
988 Execute Immediate h_sql USING h_source_lang, h_tmp, l_indicator, h_lang;
989
990
991 --BUG 2610065 FETCH c_kpi_tree INTO h_name,h_lang,h_source_lang;
992 FETCH c_kpi_tree INTO h_lang,h_source_lang,h_name;
993 END LOOP;
994 CLOSE c_kpi_tree;
995 -- series name is null for a tree
996 h_sql := 'UPDATE BSC_KPI_DEFAULTS_TL SET SERIES_NAME=NULL' ||
997 ' WHERE INDICATOR=:1'; --|| l_indicator;
998 --DBMS_OUTPUT.PUT_LINE(h_sql);
999 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); -- bug 3111300
1000 Execute Immediate h_sql USING l_indicator;
1001 ELSE
1002 -- Get the default
1003 OPEN c_color_AO_DEFAULT;
1004 FETCH c_color_AO_DEFAULT INTO h_a0,h_a1,h_a2;
1005 WHILE c_color_AO_DEFAULT%FOUND LOOP
1006 --h_msg := ' a0:' || h_a0 || '/a1:' || h_a1 || '/a2:' || h_a2;
1007
1008 OPEN c_kpi_group_dependency;
1009 FETCH c_kpi_group_dependency INTO a_temp, dep_temp;
1010 WHILE c_kpi_group_dependency%FOUND LOOP
1011 IF a_temp = 0 THEN
1012 dep_a0 := dep_temp;
1013 ELSIF a_temp =1 THEN
1014 dep_a1 := dep_temp;
1015 ELSIF a_temp =2 THEN
1016 dep_a2 := dep_temp;
1017 END IF;
1018 FETCH c_kpi_group_dependency INTO a_temp, dep_temp;
1019 END LOOP;
1020 CLOSE c_kpi_group_dependency;
1021 --h_msg:=' dep_a0:' || dep_a0 || '/dep_a1:' || dep_a1 || '/dep_a2:' || dep_a2;
1022
1023
1024 --DBMS_OUTPUT.PUT_LINE(h_msg);
1025 ---A0----------------------------
1026 h_a_grp := 0;
1027 h_a_opt := h_a0;
1028 h_a_parent_opt :=0;
1029 h_a_grandparent_opt :=0;
1030 OPEN c_kpi_analysis;
1031 FETCH c_kpi_analysis INTO h_lang,h_source_lang,h_name;
1032 WHILE c_kpi_analysis%FOUND LOOP
1033 --08/30/02
1034 h_tmp := REPLACE(h_name,'''','''''');
1035 h_sql := 'UPDATE BSC_KPI_DEFAULTS_TL SET ' ||
1036 'SOURCE_LANG = :1' || --'''' || h_source_lang || '''' ||
1037 ',ANALYSIS_OPTION0_NAME= :2' || --'''' || h_tmp || '''' ||
1038 ' WHERE INDICATOR= :3' || --l_indicator ||
1039 ' AND LANGUAGE= :4'; --|| '''' || h_lang || '''';
1040 --DBMS_OUTPUT.PUT_LINE(h_sql);
1041 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); -- bug 3111300
1042 Execute Immediate h_sql USING h_source_lang, h_tmp, l_indicator, h_lang;
1043
1044 FETCH c_kpi_analysis INTO h_lang,h_source_lang,h_name;
1045 END LOOP;
1046 CLOSE c_kpi_analysis;
1047 ---A1----------------------------
1048 h_a_grp := 1;
1049 h_a_opt := h_a1;
1050 -- if a1 is depends on a0, then we need to set grand/parents ID for query
1051 -- else, independent group, grand/parents ID is 0
1052 IF dep_a1 =1 THEN
1053 h_a_parent_opt := h_a0;
1054 h_a_grandparent_opt :=0;
1055 END IF;
1056 OPEN c_kpi_analysis;
1057 FETCH c_kpi_analysis INTO h_lang,h_source_lang,h_name;
1058 WHILE c_kpi_analysis%FOUND LOOP
1059 --08/30/02
1060 h_tmp := REPLACE(h_name,'''','''''');
1061 h_sql := 'UPDATE BSC_KPI_DEFAULTS_TL SET ' ||
1062 'SOURCE_LANG = :1' || --'''' || h_source_lang || '''' ||
1063 ',ANALYSIS_OPTION0_NAME= :2' || --'''' || h_tmp || '''' ||
1064 ' WHERE INDICATOR= :3' || --l_indicator ||
1065 ' AND LANGUAGE= :4'; --|| '''' || h_lang || '''';
1066 --DBMS_OUTPUT.PUT_LINE(h_sql);
1067 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
1068 Execute Immediate h_sql USING h_source_lang, h_tmp, l_indicator, h_lang;
1069
1070 FETCH c_kpi_analysis INTO h_lang,h_source_lang,h_name;
1071 END LOOP;
1072 CLOSE c_kpi_analysis;
1073 ---A2----------------------------
1074 h_a_grp := 2;
1075 h_a_opt := h_a2;
1076 IF dep_a2 =1 THEN
1077 h_a_parent_opt := h_a1;
1078 h_a_grandparent_opt := h_a0;
1079 END IF;
1080 OPEN c_kpi_analysis;
1081 FETCH c_kpi_analysis INTO h_lang,h_source_lang,h_name;
1082 WHILE c_kpi_analysis%FOUND LOOP
1083 --08/30/02
1084 h_tmp := REPLACE(h_name,'''','''''');
1085 h_sql := 'UPDATE BSC_KPI_DEFAULTS_TL SET ' ||
1086 'SOURCE_LANG = :1' || --'''' || h_source_lang || '''' ||
1087 ',ANALYSIS_OPTION0_NAME= :2' || --'''' || h_tmp || '''' ||
1088 ' WHERE INDICATOR= :3' || --l_indicator ||
1089 ' AND LANGUAGE= :4'; --|| '''' || h_lang || '''';
1090 --DBMS_OUTPUT.PUT_LINE(h_sql);
1091 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); -- bug 3111300
1092 Execute Immediate h_sql USING h_source_lang, h_tmp, l_indicator, h_lang;
1093
1094 FETCH c_kpi_analysis INTO h_lang,h_source_lang,h_name;
1095 END LOOP;
1096 CLOSE c_kpi_analysis;
1097
1098 FETCH c_color_AO_DEFAULT INTO h_a0,h_a1,h_a2;
1099 END LOOP;
1100 CLOSE c_color_AO_DEFAULT;
1101 END IF;
1102 --DBMS_OUTPUT.PUT_LINE('end of Deflt_Update_AOPTS');
1103 EXCEPTION
1104 WHEN OTHERS THEN
1105 --DBMS_OUTPUT.PUT_LINE('e' || SQLERRM);
1106 BSC_MESSAGE.Add(x_message => SQLERRM,
1107 x_source => 'Deflt_Update_AOPTS');
1108 END Deflt_Update_AOPTS;
1109
1110 /*===========================================================================+
1111 | PROCEDURE
1112 |
1113 |
1114 | PURPOSE
1115 | To update the SERIES_NAME, FORMAT_MASK,COLOR_METHOD,
1116 | MEASURE_SOURCE 06/06/02
1117 | PARAMETERS
1118 |
1119 | HISTORY
1120 | 12-NOV-2001 Henry Camacho Created
1121 +---------------------------------------------------------------------------*/
1122
1123 PROCEDURE Deflt_Update_SN_FM_CM(x_indicator IN NUMBER) IS
1124
1125
1126 h_msg VARCHAR2(1000);
1127 h_sql VARCHAR2(2000);
1128 s_node NUMBER;
1129 --
1130 h_color_method NUMBER;
1131 h_format_mask VARCHAR2(20);
1132 --
1133 h_a0 NUMBER;
1134 h_a1 NUMBER;
1135 h_a2 NUMBER;
1136
1137 --
1138 h_name BSC_KPI_ANALYSIS_MEASURES_TL.NAME%TYPE;
1139 h_lang VARCHAR2(4);
1140 h_source_lang VARCHAR2(4);
1141 --08/30/02
1142 h_tmp VARCHAR2(255); /* Bug Fix #2691601 changine size from 80 to 255 */
1143 --
1144
1145 CURSOR c_kpi_tree IS
1146 SELECT F.FORMAT,COLOR_METHOD
1147 FROM BSC_KPI_TREE_NODES_B B,
1148 BSC_SYS_FORMATS F
1149 WHERE INDICATOR = l_indicator AND NODE_ID = s_node
1150 AND F.FORMAT_ID = B.FORMAT_ID;
1151 CURSOR c_color_AO_DEFAULT IS
1152 SELECT A0_DEFAULT,A1_DEFAULT,A2_DEFAULT
1153 FROM BSC_DB_COLOR_AO_DEFAULTS_V
1154 WHERE INDICATOR = l_indicator;
1155
1156 CURSOR c_analisys_measure IS
1157 SELECT B.SERIES_ID,F.FORMAT,DS.COLOR_METHOD
1158 FROM BSC_KPI_ANALYSIS_MEASURES_B B,BSC_SYS_FORMATS F,BSC_SYS_DATASETS_B DS
1159 WHERE INDICATOR = l_indicator
1160 AND B.ANALYSIS_OPTION0= h_a0
1161 AND B.ANALYSIS_OPTION1= h_a1
1162 AND B.ANALYSIS_OPTION2= h_a2
1163 AND F.FORMAT_ID = DS.FORMAT_ID
1164 AND B.DATASET_ID = DS.DATASET_ID
1165 AND B.DEFAULT_VALUE = 1;
1166
1167 h_serie_id NUMBER;
1168 CURSOR c_analisys_measure_tl IS
1169 SELECT LANGUAGE,SOURCE_LANG,NAME
1170 FROM BSC_KPI_ANALYSIS_MEASURES_TL
1171 WHERE INDICATOR = l_indicator
1172 AND ANALYSIS_OPTION0= h_a0
1173 AND ANALYSIS_OPTION1= h_a1
1174 AND ANALYSIS_OPTION2= h_a2
1175 AND SERIES_ID = h_serie_id;
1176 --06/06/02
1177 h_kpi_measure_source VARCHAR2(10);
1178 BEGIN
1179 --Initiliaze the inetrnal variable by kpis
1180 Init_variables(x_indicator);
1181 --h_msg := 'Upadte FM Initialzie' || l_indicator;
1182 --DBMS_OUTPUT.PUT_LINE(h_msg);
1183
1184 -- Set To Null-----------------------
1185 h_sql := 'UPDATE BSC_KPI_DEFAULTS_B SET ' ||
1186 ' FORMAT_MASK =NULL,' ||
1187 ' COLOR_METHOD=NULL, ' ||
1188 ' MEASURE_SOURCE = ''BSC''' ||
1189 ' WHERE INDICATOR =:1'; -- || l_indicator;
1190 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
1191 Execute Immediate h_sql USING l_indicator;
1192 --DBMS_OUTPUT.PUT_LINE(h_sql);
1193
1194 h_sql := 'UPDATE BSC_KPI_DEFAULTS_TL SET ' ||
1195 ' SERIES_NAME= NULL' ||
1196 ' WHERE INDICATOR =:1'; --|| l_indicator;
1197 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
1198 Execute Immediate h_sql USING l_indicator;
1199 --DBMS_OUTPUT.PUT_LINE(h_sql);
1200
1201 --Simulation Tree, Retrieve the default node captions----------------------------
1202 IF l_ind_type = 1 AND l_ind_config = 7 THEN
1203 --Init s_node
1204 s_node := 0;
1205 SELECT PROPERTY_VALUE
1206 INTO s_node
1207 FROM BSC_KPI_PROPERTIES WHERE PROPERTY_CODE='S_NODE_ID'
1208 AND INDICATOR = l_indicator;
1209 --h_msg := ' s_node:' || s_node;
1210 --DBMS_OUTPUT.PUT_LINE(h_msg);
1211
1212 --FORMAT AND METHOD----------------------------
1213 OPEN c_kpi_tree ;
1214 FETCH c_kpi_tree INTO h_format_mask,h_color_method;
1215 IF c_kpi_tree%FOUND THEN
1216 h_sql := 'UPDATE BSC_KPI_DEFAULTS_B SET ' ||
1217 ' FORMAT_MASK = :1' || --'''' || h_format_mask || '''' ||
1218 ',COLOR_METHOD=:2' || --h_color_method ||
1219 ' WHERE INDICATOR=:3'; --|| l_indicator;
1220 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
1221 Execute Immediate h_sql USING h_format_mask, h_color_method, l_indicator;
1222 --DBMS_OUTPUT.PUT_LINE(h_sql);
1223 END IF;
1224 CLOSE c_kpi_tree;
1225 -- series name is null for a tree
1226 h_sql := 'UPDATE BSC_KPI_DEFAULTS_TL SET SERIES_NAME=NULL' ||
1227 ' WHERE INDICATOR=:1'; -- || l_indicator;
1228 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
1229 Execute Immediate h_sql USING l_indicator;
1230 --DBMS_OUTPUT.PUT_LINE(h_sql);
1231 ELSE
1232 --Get defaults
1233 OPEN c_color_AO_DEFAULT;
1234 FETCH c_color_AO_DEFAULT INTO h_a0,h_a1,h_a2;
1235 IF c_color_AO_DEFAULT%FOUND THEN
1236 --h_msg := ' a0:' || h_a0 || '/a1:' || h_a1 || '/a2:' || h_a2;
1237 --DBMS_OUTPUT.PUT_LINE(h_msg);
1238 ---Get The captions-------------------------
1239 OPEN c_analisys_measure;
1240 FETCH c_analisys_measure INTO h_serie_id,h_format_mask,h_color_method;
1241 IF c_analisys_measure%FOUND THEN
1242 h_sql := 'UPDATE BSC_KPI_DEFAULTS_B SET ' ||
1243 ' FORMAT_MASK = :1' || --'''' || h_format_mask || '''' ||
1244 ',COLOR_METHOD=:2' || --h_color_method ||
1245 ' WHERE INDICATOR=:3'; --|| l_indicator;
1246 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
1247 Execute Immediate h_sql USING h_format_mask, h_color_method, l_indicator;
1248 --DBMS_OUTPUT.PUT_LINE(h_sql);
1249 END IF;
1250 CLOSE c_analisys_measure;
1251 -- Serie Name
1252 OPEN c_analisys_measure_tl;
1253 FETCH c_analisys_measure_tl INTO h_lang,h_source_lang,h_name;
1254 WHILE c_analisys_measure_tl%FOUND LOOP
1255 --08/30/02
1256 h_tmp := REPLACE(h_name,'''','''''');
1257 h_sql := 'UPDATE BSC_KPI_DEFAULTS_TL SET ' ||
1258 'SOURCE_LANG = :1' || --'''' || h_source_lang || '''' ||
1259 ',SERIES_NAME=:2' || --'''' || h_tmp || '''' ||
1260 ' WHERE INDICATOR=:3' || --l_indicator ||
1261 ' AND LANGUAGE=:4'; --|| '''' || h_lang || '''';
1262 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
1263 Execute Immediate h_sql USING h_source_lang, h_tmp, l_indicator, h_lang;
1264 --DBMS_OUTPUT.PUT_LINE(h_sql);
1265
1266
1267 FETCH c_analisys_measure_tl INTO h_lang,h_source_lang,h_name;
1268 END LOOP;
1269 CLOSE c_analisys_measure_tl;
1270
1271 END IF;
1272 CLOSE c_color_AO_DEFAULT;
1273
1274 -- MEASURE_SOURCE 06/06/02
1275 -- Changed for Bug#3753735
1276 h_kpi_measure_source :='BSC';
1277 SELECT NVL(SOURCE,'BSC')
1278 INTO h_Kpi_Measure_Source
1279 FROM BSC_SYS_DATASETS_B A,
1280 (SELECT DATASET_ID
1281 FROM BSC_KPI_ANALYSIS_MEASURES_B MS,
1282 BSC_DB_COLOR_AO_DEFAULTS_V DF
1283 WHERE ANALYSIS_OPTION0 = DF.A0_DEFAULT
1284 AND ANALYSIS_OPTION1 = DF.A1_DEFAULT
1285 AND ANALYSIS_OPTION2= DF.A2_DEFAULT
1286 AND DEFAULT_VALUE = 1
1287 AND MS.INDICATOR = DF.INDICATOR
1288 AND MS.INDICATOR= x_indicator) B
1289 WHERE A.DATASET_ID = B.DATASET_ID;
1290
1291 h_sql := 'UPDATE BSC_KPI_DEFAULTS_B SET ' ||
1292 ' MEASURE_SOURCE = :1' || --'''' || h_kpi_measure_source || '''' ||
1293 ' WHERE INDICATOR=:2'; --|| l_indicator;
1294
1295 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
1296 Execute Immediate h_sql USING h_kpi_measure_source, l_indicator;
1297
1298
1299 END IF;
1300
1301 EXCEPTION
1302 WHEN OTHERS THEN
1303 --DBMS_OUTPUT.PUT_LINE('d' || SQLERRM);
1304 BSC_MESSAGE.Add(x_message => SQLERRM,
1305 x_source => 'Deflt_Update_SN_FM_CM');
1306 END Deflt_Update_SN_FM_CM;
1307
1308
1309 /*===========================================================================+
1310 | PROCEDURE
1311 |
1312 |
1313 | PURPOSE
1314 | To update the DIM_SET
1315 | PARAMETERS
1316 |
1317 | HISTORY
1318 | 13-NOV-2001 Henry Camacho Created
1319 +---------------------------------------------------------------------------*/
1320
1321 PROCEDURE Deflt_Update_DIM_SET(x_indicator IN NUMBER) IS
1322
1323 h_msg VARCHAR2(1000);
1324 h_sql VARCHAR2(2000);
1325 BEGIN
1326 --Initiliaze the inetrnal variable by kpis
1327 Init_variables(x_indicator);
1328 --h_msg := 'Upadte DIM SET Initialzie' || l_indicator;
1329 --DBMS_OUTPUT.PUT_LINE(h_msg);
1330
1331
1332 -- Update DIM SET
1333 h_sql := 'UPDATE BSC_KPI_DEFAULTS_B KD SET ' ||
1334 ' DIM_SET_ID = (SELECT DIM_SET_ID ' ||
1335 ' FROM BSC_DB_COLOR_KPI_DEFAULTS_V DB ' ||
1336 ' WHERE KD.INDICATOR = DB.INDICATOR (+)) ' ||
1337 ' WHERE KD.INDICATOR =:1'; --|| l_indicator;
1338 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1339 Execute Immediate h_sql USING l_indicator;
1340
1341 EXCEPTION
1342 WHEN OTHERS THEN
1343 --DBMS_OUTPUT.PUT_LINE('g' || SQLERRM);
1344 BSC_MESSAGE.Add(x_message => SQLERRM,
1345 x_source => 'Deflt_Update_DIM_SET');
1346 END Deflt_Update_DIM_SET;
1347
1348 /*===========================================================================+
1349 | PROCEDURE
1350 |
1351 |
1352 | PURPOSE
1353 | To update the Dimension values
1354 | PARAMETERS
1355 |
1356 | HISTORY
1357 | 13-NOV-2001 Henry Camacho Created
1358 +---------------------------------------------------------------------------*/
1359
1360 PROCEDURE Deflt_Update_DIM_VALUES(x_indicator IN NUMBER) IS
1361
1362 h_msg VARCHAR2(1000);
1363 h_sql VARCHAR2(32000);
1364 BEGIN
1365
1366 --Initiliaze the inetrnal variable by kpis
1367 Init_variables(x_indicator);
1368 --h_msg := 'Update DIM VALUES Initialzie' || l_indicator;
1369 --DBMS_OUTPUT.PUT_LINE(h_msg);
1370
1371 -- Update dimension values
1372
1373 h_sql := 'UPDATE BSC_KPI_DEFAULTS_B KD SET ' ||
1374 ' DIM_LEVEL1_VALUE = (SELECT DECODE(DEFAULT_KEY_VALUE, NULL, DECODE(DEFAULT_VALUE,''C'',-1,0), DEFAULT_KEY_VALUE) VALUE ' ||
1375 ' FROM BSC_KPI_DIM_LEVELS_B KDL ' ||
1376 ' WHERE KDL.DIM_LEVEL_INDEX = 0 ' ||
1377 ' AND KDL.INDICATOR = KD.INDICATOR ' ||
1378 ' AND KDL.DIM_SET_ID = KD.DIM_SET_ID),' ||
1379 ' DIM_LEVEL2_VALUE = (SELECT DECODE(DEFAULT_KEY_VALUE, NULL, DECODE(DEFAULT_VALUE,''C'',-1,0), DEFAULT_KEY_VALUE) VALUE ' ||
1380 ' FROM BSC_KPI_DIM_LEVELS_B KDL ' ||
1381 ' where KDL.DIM_LEVEL_INDEX = 1 ' ||
1382 ' AND KDL.INDICATOR = KD.INDICATOR ' ||
1383 ' AND KDL.DIM_SET_ID = KD.DIM_SET_ID), ' ||
1384 ' DIM_LEVEL3_VALUE = (SELECT DECODE(DEFAULT_KEY_VALUE, NULL, DECODE(DEFAULT_VALUE,''C'',-1,0), DEFAULT_KEY_VALUE) VALUE ' ||
1385 ' FROM BSC_KPI_DIM_LEVELS_B KDL ' ||
1386 ' WHERE KDL.DIM_LEVEL_INDEX = 2 ' ||
1387 ' AND KDL.INDICATOR = KD.INDICATOR ' ||
1388 ' AND KDL.DIM_SET_ID = KD.DIM_SET_ID), ' ||
1389 ' DIM_LEVEL4_VALUE = (SELECT DECODE(DEFAULT_KEY_VALUE, NULL, DECODE(DEFAULT_VALUE,''C'',-1,0), DEFAULT_KEY_VALUE) VALUE ' ||
1390 ' FROM BSC_KPI_DIM_LEVELS_B KDL ' ||
1391 ' WHERE KDL.DIM_LEVEL_INDEX = 3 ' ||
1392 ' AND KDL.INDICATOR = KD.INDICATOR ' ||
1393 ' AND KDL.DIM_SET_ID = KD.DIM_SET_ID), ' ||
1394 ' DIM_LEVEL5_VALUE = (SELECT DECODE(DEFAULT_KEY_VALUE, NULL, DECODE(DEFAULT_VALUE,''C'',-1,0), DEFAULT_KEY_VALUE) VALUE ' ||
1395 ' FROM BSC_KPI_DIM_LEVELS_B KDL ' ||
1396 ' WHERE KDL.DIM_LEVEL_INDEX = 4 ' ||
1397 ' AND KDL.INDICATOR = KD.INDICATOR ' ||
1398 ' AND KDL.DIM_SET_ID = KD.DIM_SET_ID), ' ||
1399 ' DIM_LEVEL6_VALUE = (SELECT DECODE(DEFAULT_KEY_VALUE, NULL, DECODE(DEFAULT_VALUE,''C'',-1,0), DEFAULT_KEY_VALUE) VALUE ' ||
1400 ' FROM BSC_KPI_DIM_LEVELS_B KDL ' ||
1401 ' WHERE KDL.DIM_LEVEL_INDEX = 5 ' ||
1402 ' AND KDL.INDICATOR = KD.INDICATOR ' ||
1403 ' AND KDL.DIM_SET_ID = KD.DIM_SET_ID), ' ||
1404 ' DIM_LEVEL7_VALUE = (SELECT DECODE(DEFAULT_KEY_VALUE, NULL, DECODE(DEFAULT_VALUE,''C'',-1,0), DEFAULT_KEY_VALUE) VALUE ' ||
1405 ' FROM BSC_KPI_DIM_LEVELS_B KDL ' ||
1406 ' WHERE KDL.DIM_LEVEL_INDEX = 6 ' ||
1407 ' AND KDL.INDICATOR = KD.INDICATOR ' ||
1408 ' AND KDL.DIM_SET_ID = KD.DIM_SET_ID), ' ||
1409 ' DIM_LEVEL8_VALUE = (SELECT DECODE(DEFAULT_KEY_VALUE, NULL, DECODE(DEFAULT_VALUE,''C'',-1,0), DEFAULT_KEY_VALUE) VALUE ' ||
1410 ' FROM BSC_KPI_DIM_LEVELS_B KDL ' ||
1411 ' WHERE KDL.DIM_LEVEL_INDEX = 7 ' ||
1412 ' AND KDL.INDICATOR = KD.INDICATOR ' ||
1413 ' AND KDL.DIM_SET_ID = KD.DIM_SET_ID) ' ||
1414 ' WHERE KD.INDICATOR =:1'; --|| l_indicator;
1415 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300 ,part2
1416 Execute Immediate h_sql USING l_indicator;
1417 EXCEPTION
1418 WHEN OTHERS THEN
1419 --DBMS_OUTPUT.PUT_LINE('h' || SQLERRM);
1420 BSC_MESSAGE.Add(x_message => SQLERRM,
1421 x_source => 'Deflt_Update_DIM_VALUES');
1422 END Deflt_Update_DIM_VALUES;
1423 /*===========================================================================+
1424 | PROCEDURE
1425 |
1426 |
1427 | PURPOSE
1428 | To update the Dimension names
1429 | PARAMETERS
1430 |
1431 | HISTORY
1432 | 13-NOV-2001 Henry Camacho Created
1433 +---------------------------------------------------------------------------*/
1434
1435 PROCEDURE Deflt_Update_DIM_NAMES(x_indicator IN NUMBER) IS
1436
1437 h_msg VARCHAR2(1000);
1438 h_sql VARCHAR2(32000);
1439 h_i NUMBER(1);
1440 h_drill NUMBER;
1441 --08/30/02
1442 h_tmp VARCHAR2(270); /* Bug Fix #2691601 changine size from 80 to 255 */
1443 h_tmp1 VARCHAR2(270); /* Bug Fix #2691601 changine size from 80 to 255 */
1444 CURSOR c_kpi_dim IS
1445 SELECT VL.DIM_SET_ID,VL.DIM_LEVEL_INDEX, VL.LEVEL_VIEW_NAME,VL.VALUE_ORDER_BY
1446 FROM BSC_KPI_DIM_LEVELS_VL VL,
1447 BSC_KPI_DEFAULTS_B DF
1448 WHERE VL.INDICATOR =l_indicator
1449 AND VL.INDICATOR = DF.INDICATOR
1450 AND VL.DIM_SET_ID = DF.DIM_SET_ID
1451 AND VL.DEFAULT_VALUE='T'
1452 AND VL.TOTAL_DISP_NAME IS NULL
1453 AND VL.PARENT_LEVEL_INDEX IS NULL
1454 AND VL.DIM_LEVEL_INDEX <8;
1455
1456 CURSOR c_dimnames IS
1457 SELECT B.DIM_SET_ID,TL.LANGUAGE,
1458 DECODE(B.STATUS,2,TL.NAME,NULL) AS NAME,
1459 DECODE(DEFAULT_KEY_VALUE,NULL,
1460 DECODE(DEFAULT_VALUE,
1461 'C',COMP_DISP_NAME,TOTAL_DISP_NAME),NULL) TEXT
1462 FROM BSC_KPI_DIM_LEVELS_B B,
1463 BSC_KPI_DIM_LEVELS_TL TL,
1464 BSC_KPI_DEFAULTS_B DF
1465 WHERE B.INDICATOR = l_indicator AND
1466 B.INDICATOR = TL.INDICATOR AND
1467 B.INDICATOR = DF.INDICATOR AND
1468 B.DIM_SET_ID =DF.DIM_SET_ID AND
1469 B.DIM_SET_ID =TL.DIM_SET_ID AND
1470 B.DIM_LEVEL_INDEX = TL.DIM_LEVEL_INDEX AND
1471 B.DIM_LEVEL_INDEX = h_drill;
1472 --Set text for key item
1473 CURSOR c_key_item IS
1474 SELECT DIM_SET_ID,DIM_LEVEL_INDEX,LEVEL_TABLE_NAME,DEFAULT_KEY_VALUE
1475 FROM BSC_KPI_DIM_LEVELS_VL
1476 WHERE INDICATOR=l_indicator
1477 AND DEFAULT_KEY_VALUE>0
1478 AND DIM_LEVEL_INDEX<8;
1479
1480 l_LEVEL_TABLE_NAME VARCHAR2(30);
1481 l_DEFAULT_KEY_VALUE NUMBER;
1482
1483 h_dim_set_id NUMBER(3);
1484 h_dim_level_index NUMBER(3);
1485 h_level_view_name VARCHAR2(30);
1486 h_value_order_by NUMBER(5);
1487 h_dim_txt VARCHAR2(100);
1488 h_lang VARCHAR2(4);
1489 h_name BSC_KPI_DIM_LEVELS_TL.NAME%TYPE;
1490 h_text VARCHAR2(240);
1491 BEGIN
1492
1493 --Initiliaze the inetrnal variable by kpis
1494 Init_variables(x_indicator);
1495 --h_msg := 'Update DIM NAME Initialzie' || l_indicator;
1496 --Reset
1497 h_sql := 'UPDATE BSC_KPI_DEFAULTS_TL SET DIM_LEVEL1_NAME=NULL,'||
1498 ' DIM_LEVEL2_NAME=NULL,DIM_LEVEL3_NAME=NULL,'||
1499 ' DIM_LEVEL4_NAME=NULL,DIM_LEVEL5_NAME=NULL,'||
1500 ' DIM_LEVEL6_NAME=NULL,DIM_LEVEL7_NAME=NULL,'||
1501 ' DIM_LEVEL8_NAME=NULL ' ||
1502 ' WHERE INDICATOR =:1'; --|| l_indicator;
1503 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
1504 Execute Immediate h_sql USING l_indicator;
1505
1506 FOR h_i IN 0..7 LOOP
1507 --New
1508 h_drill := h_i;
1509 --DBMS_OUTPUT.PUT_LINE('h_i:' || h_i);
1510 OPEN c_dimnames;
1511 FETCH c_dimnames INTO h_dim_set_id,h_lang,h_name,h_text;
1512 WHILE c_dimnames%FOUND LOOP
1513 -- bug 2479254
1514 h_sql := 'UPDATE BSC_KPI_DEFAULTS_TL SET ' ||
1515 ' DIM_LEVEL' || TO_CHAR(h_i + 1) || '_NAME= ';
1516 IF h_name IS NULL THEN
1517 h_sql := h_sql || 'NULL';
1518 ELSE
1519 --08/30/02
1520 h_tmp := REPLACE(h_name,'''','''''');
1521 h_sql := h_sql || ' ''' || h_tmp || '''';
1522 END IF;
1523
1524 h_sql := h_sql || ',DIM_LEVEL' || TO_CHAR(h_i + 1) || '_TEXT=';
1525 IF h_text IS NULL THEN
1526 h_sql := h_sql || 'NULL';
1527 ELSE
1528 --08/30/02
1529 h_tmp1 := REPLACE(h_text,'''','''''');
1530 h_sql := h_sql || ' ''' || h_tmp1 || '''';
1531 END IF;
1532 h_sql := h_sql ||' WHERE INDICATOR = :1' || --l_indicator ||
1533 ' AND LANGUAGE = :2'; --|| ' ''' || h_lang || '''';
1534
1535 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
1536 Execute Immediate h_sql USING l_indicator, h_lang;
1537
1538 --DBMS_OUTPUT.PUT_LINE(h_sql);
1539 FETCH c_dimnames INTO h_dim_set_id,h_lang,h_name,h_text;
1540 END LOOP;
1541 CLOSE c_dimnames;
1542 END LOOP;
1543
1544 --1759829 Update the DIM_LEVELS_#_TEXT when the drill doesn't have all and it should be select the first item
1545 OPEN c_kpi_dim;
1546 FETCH c_kpi_dim INTO h_dim_set_id,h_dim_level_index,h_level_view_name,h_value_order_by;
1547 WHILE c_kpi_dim%FOUND LOOP
1548 --Get The Value
1549 h_dim_txt := getItemfromMasterTable(h_level_view_name, h_value_order_by);
1550 --Assigned
1551 h_sql := 'UPDATE BSC_KPI_DEFAULTS_TL SET DIM_LEVEL' || (h_dim_level_index + 1) || '_TEXT= :1' || --'''' || h_dim_txt || '''' ||
1552 ' WHERE INDICATOR =:2'; --|| l_indicator;
1553
1554 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
1555 Execute Immediate h_sql USING h_dim_txt, l_indicator;
1556 --DBMS_OUTPUT.PUT_LINE(h_sql);
1557
1558 FETCH c_kpi_dim INTO h_dim_set_id,h_dim_level_index,h_level_view_name,h_value_order_by;
1559 END LOOP;
1560 CLOSE c_kpi_dim;
1561 -- bug 2424070
1562 OPEN c_key_item;
1563 FETCH c_key_item INTO h_dim_set_id,h_dim_level_index,l_LEVEL_TABLE_NAME,l_DEFAULT_KEY_VALUE;
1564 WHILE c_key_item%FOUND LOOP
1565 h_sql := 'UPDATE BSC_KPI_DEFAULTS_TL DTL SET DIM_LEVEL' || (h_dim_level_index + 1) || '_TEXT=( ' ||
1566 ' SELECT NAME FROM ' || l_LEVEL_TABLE_NAME ||
1567 ' WHERE CODE = :1' || --l_DEFAULT_KEY_VALUE ||
1568 ' AND '||
1569 ' DTL.LANGUAGE =LANGUAGE) WHERE INDICATOR = :2'; --|| l_indicator;
1570
1571 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
1572 Execute Immediate h_sql USING l_DEFAULT_KEY_VALUE, l_indicator;
1573
1574
1575 FETCH c_key_item INTO h_dim_set_id,h_dim_level_index,l_LEVEL_TABLE_NAME,l_DEFAULT_KEY_VALUE;
1576 END LOOP;
1577 CLOSE c_key_item;
1578
1579 EXCEPTION
1580 WHEN OTHERS THEN
1581 --DBMS_OUTPUT.PUT_LINE('j' || SQLERRM);
1582 BSC_MESSAGE.Add(x_message => SQLERRM,
1583 x_source => 'Deflt_Update_DIM_NAMES');
1584 END Deflt_Update_DIM_NAMES;
1585 /*===========================================================================+
1586 | FUNCTION
1587 |
1588 |
1589 | PURPOSE
1590 | To get the first Item from the Master Table
1591 | PARAMETERS
1592 |
1593 | HISTORY
1594 | 13-NOV-2001 Henry Camacho Created
1595 +---------------------------------------------------------------------------*/
1596 FUNCTION getItemfromMasterTable(MASTER IN VARCHAR2, ORDER_BY IN NUMBER)
1597 RETURN VARCHAR2 IS
1598
1599 h_sql VARCHAR2(2000);
1600 h_msg VARCHAR2(1000);
1601 h_cursor INTEGER;
1602 h_name VARCHAR2(255);
1603 h_ret INTEGER;
1604 BEGIN
1605 h_name := ' ';
1606
1607 IF (master = 'BSC_D_HRI_PER_USRDR_H_V') THEN
1608 return ' ';
1609 /*h_sql := 'SELECT NAME FROM ' || master ||
1610 ' WHERE CODE = FND_GLOBAL.EMPLOYEE_ID';*/
1611 ELSE
1612 h_sql := 'SELECT NAME FROM ' || master ||
1613 ' WHERE CODE <> 0 ';
1614 END IF;
1615
1616 IF ORDER_BY = 0 THEN
1617 h_sql := h_sql || ' ORDER BY NAME';
1618 END IF;
1619 IF ORDER_BY = 1 THEN
1620 h_sql := h_sql || ' ORDER BY CODE';
1621 END IF;
1622 IF ORDER_BY = 2 THEN
1623 h_sql := h_sql || ' ORDER BY USER_CODE';
1624 END IF;
1625 --DBMS_OUTPUT.PUT_LINE(h_sql);
1626
1627 h_cursor := DBMS_SQL.OPEN_CURSOR;
1628 DBMS_SQL.PARSE(h_cursor, h_sql, DBMS_SQL.NATIVE);
1629 DBMS_SQL.DEFINE_COLUMN(h_cursor,1,h_name,100);
1630 h_ret := DBMS_SQL.EXECUTE(h_cursor);
1631 IF DBMS_SQL.FETCH_ROWS(h_cursor) > 0 THEN
1632 DBMS_SQL.COLUMN_VALUE(h_cursor, 1, h_name);
1633 ELSE
1634 h_name := ' ';
1635 END IF;
1636 DBMS_SQL.CLOSE_CURSOR(h_cursor);
1637
1638 RETURN h_name;
1639
1640 EXCEPTION
1641 WHEN OTHERS THEN
1642 --DBMS_OUTPUT.PUT_LINE('k' || SQLERRM);
1643 BSC_MESSAGE.Add(x_message => SQLERRM,
1644 x_source => 'getItemfromMasterTable');
1645 END getItemfromMasterTable;
1646 /*===========================================================================+
1647 | PROCEDURE
1648 |
1649 |
1650 | PURPOSE
1651 | update period_name
1652 | PARAMETERS
1653 |
1654 | HISTORY
1655 | 14-NOV-2001 Henry Camacho Created
1656 +---------------------------------------------------------------------------*/
1657
1658 PROCEDURE Deflt_Update_PERIOD_NAME(x_indicator IN NUMBER) IS
1659
1660 h_msg VARCHAR2(1000);
1661 h_sql VARCHAR2(32000);
1662 h_year NUMBER;
1663 h_cur_period NUMBER;
1664 h_periodicity NUMBER;
1665
1666 CURSOR c_cur_year IS
1667 SELECT FISCAL_YEAR FROM
1668 BSC_KPIS_VL
1669 WHERE INDICATOR= l_indicator;
1670 CURSOR c_periodicity IS
1671 SELECT P.PERIODICITY_ID,P.CURRENT_PERIOD
1672 FROM BSC_KPIS_B B, BSC_KPI_PERIODICITIES P
1673 WHERE P.INDICATOR= l_indicator
1674 AND P.INDICATOR= B.INDICATOR AND
1675 P.PERIODICITY_ID = B.PERIODICITY_ID;
1676
1677 h_str VARCHAR2(90);
1678 BEGIN
1679
1680 --Initiliaze the inetrnal variable by kpis
1681 Init_variables(x_indicator);
1682 --h_msg := 'Upadte Period Name Initialzie' || l_indicator;
1683 --DBMS_OUTPUT.PUT_LINE(h_msg);
1684
1685 --Current Year
1686 h_year := 2001;
1687 OPEN c_cur_year;
1688 FETCH c_cur_year INTO h_year;
1689 CLOSE c_cur_year;
1690 --DBMS_OUTPUT.PUT_LINE('Year:' || h_year);
1691
1692 -- get periodicity and period
1693 h_periodicity := 5;
1694 h_cur_period := 1;
1695 OPEN c_periodicity;
1696 FETCH c_periodicity INTO h_periodicity,h_cur_period;
1697 CLOSE c_periodicity;
1698 --DBMS_OUTPUT.PUT_LINE('Periodicity:' || h_periodicity || '/Period:' || h_cur_period);
1699
1700 --get Label
1701 IF h_periodicity = 1 THEN
1702 h_str := h_periodicity || '-' || h_year;
1703 --DBMS_OUTPUT.PUT_LINE('h_str:' || h_str);
1704
1705 h_sql := 'UPDATE BSC_KPI_DEFAULTS_TL KD SET '||
1706 ' PERIOD_NAME =:1' || --'''' || h_str || '''' ||
1707 ' WHERE KD.INDICATOR =:2'; --|| l_indicator;
1708
1709 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
1710 Execute Immediate h_sql USING h_str, l_indicator;
1711
1712 --DBMS_OUTPUT.PUT_LINE(h_sql);
1713 ELSE
1714 h_str := 'CONCAT(CONCAT(''' || h_periodicity || ''',''-''),P.NAME)';
1715 --DBMS_OUTPUT.PUT_LINE('h_str:' || h_str);
1716 h_sql := ' UPDATE BSC_KPI_DEFAULTS_TL KD SET '||
1717 ' PERIOD_NAME = ( '||
1718 ' SELECT ' || h_str ||
1719 ' FROM BSC_SYS_PERIODS_TL P '||
1720 ' WHERE ' ||
1721 ' P.YEAR= :1' || --h_year ||
1722 ' AND P.LANGUAGE = KD.LANGUAGE '||
1723 ' AND P.PERIODICITY_ID = :2' || --h_periodicity ||
1724 ' AND P.PERIOD_ID = :3' || ')' || --h_cur_period || ')' ||
1725 ' WHERE KD.INDICATOR = :4'; --|| l_indicator;
1726
1727 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300
1728 Execute Immediate h_sql USING h_year , h_periodicity, h_cur_period, l_indicator;
1729 --DBMS_OUTPUT.PUT_LINE(h_sql);
1730 END IF;
1731
1732 EXCEPTION
1733 WHEN OTHERS THEN
1734 --DBMS_OUTPUT.PUT_LINE('m' || SQLERRM);
1735 BSC_MESSAGE.Add(x_message => SQLERRM,
1736 x_source => 'Deflt_Update_PERIOD');
1737 END Deflt_Update_PERIOD_NAME;
1738 /*===========================================================================+
1739 | PROCEDURE
1740 | PURPOSE
1741 | Replicate the metadata records for a particular Table
1742 | PARAMETERS
1743 |
1744 | HISTORY
1745 | 04-FEB-2002 Henry Camacho Created
1746 +---------------------------------------------------------------------------*/
1747
1748 PROCEDURE Duplicate_Record_by_Indicator(x_table_name IN VARCHAR2, x_Src_kpi IN NUMBER, x_Trg_kpi IN NUMBER ) IS
1749
1750 CURSOR c_column IS
1751 SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS
1752 WHERE TABLE_NAME = x_table_name
1753 AND OWNER = DECODE(USER,BSC_APPS.get_user_schema('APPS'),BSC_APPS.get_user_schema,USER)
1754 ORDER BY COLUMN_NAME;
1755
1756 h_Trg_kpi_neg NUMBER;
1757 CURSOR c_userwizard IS
1758 SELECT
1759 TRG.ANALYSIS_GROUP_ID,
1760 TRG.OPTION_ID,
1761 TRG.PARENT_OPTION_ID,
1762 TRG.GRANDPARENT_OPTION_ID,
1763 TRG.USER_LEVEL1
1764 FROM BSC_KPI_ANALYSIS_OPTIONS_VL SRC,
1765 BSC_KPI_ANALYSIS_OPTIONS_VL TRG
1766 WHERE
1767 SRC.ANALYSIS_GROUP_ID= TRG.ANALYSIS_GROUP_ID AND
1768 SRC.PARENT_OPTION_ID= TRG.PARENT_OPTION_ID AND
1769 SRC.GRANDPARENT_OPTION_ID= TRG.GRANDPARENT_OPTION_ID AND
1770 SRC.NAME = TRG.NAME AND
1771 SRC.USER_LEVEL1 <> 1 AND
1772 SRC.INDICATOR = x_Src_kpi AND
1773 TRG.INDICATOR = h_Trg_kpi_neg;
1774 h_colum VARCHAR2(100);
1775 h_key_name VARCHAR2(30);
1776 h_condition VARCHAR2(1000);
1777 h_sql VARCHAR2(32000);
1778 x_arr_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
1779 x_num_columns NUMBER;
1780 i NUMBER;
1781 h_ag NUMBER;
1782 h_aO NUMBER;
1783 h_aOP NUMBER;
1784 h_aOG NUMBER;
1785 h_usl NUMBER;
1786 BEGIN
1787 -- Initialize BSC/APPS global variables
1788 BSC_APPS.Init_Bsc_Apps;
1789
1790 h_key_name := 'INDICATOR';
1791 IF SUBSTR(x_table_name, 1, 7) = 'BSC_SYS' THEN
1792 h_key_name := 'SOURCE_CODE';
1793 -- Delete the Target Records
1794 h_condition := 'SOURCE_TYPE=2 AND ' || h_key_name || '=' || x_Trg_kpi;
1795 ELSE
1796 h_condition := 'INDICATOR=' || x_Trg_kpi;
1797 END IF;
1798
1799 --Bug 2258410 don't override the user wizard preferences
1800 --Move the record to negative . to later restore the values
1801 h_Trg_kpi_neg := x_Trg_kpi * -1;
1802
1803 h_sql := 'DELETE ' || x_table_name || ' WHERE ' || h_condition;
1804 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1805
1806 --Open COLUMNS by table cursor
1807 x_num_columns :=0;
1808 OPEN c_column;
1809 FETCH c_column INTO h_colum;
1810 WHILE c_column%FOUND LOOP
1811 x_num_columns := x_num_columns + 1;
1812 x_arr_columns(x_num_columns) := h_colum;
1813 FETCH c_column INTO h_colum;
1814 END LOOP;
1815 CLOSE c_column;
1816
1817 IF x_num_columns > 0 THEN
1818 --Create the sql to insert
1819 h_condition:= h_key_name || '=' || x_Src_kpi;
1820 IF h_key_name = 'SOURCE_CODE' THEN
1821 h_condition:= h_condition || ' AND SOURCE_TYPE=2';
1822 END IF;
1823 h_sql:= 'INSERT INTO ( SELECT ';
1824 FOR i IN 1..x_num_columns LOOP
1825 IF i <> 1 THEN
1826 h_sql:= h_sql || ',';
1827 END IF;
1828 h_sql:= h_sql || x_arr_columns(i);
1829 END LOOP;
1830 h_sql:= h_sql || ' FROM ' || x_table_name;
1831 h_sql:= h_sql || ' )';
1832 h_sql:= h_sql || ' SELECT ';
1833 FOR i IN 1..x_num_columns LOOP
1834 IF i <> 1 THEN
1835 h_sql:= h_sql || ',';
1836 END IF;
1837 --Replace
1838 IF UPPER(x_arr_columns(i)) = h_key_name THEN
1839 h_sql:= h_sql || x_Trg_kpi || ' AS ' || x_arr_columns(i);
1840 ELSE
1841 h_sql:= h_sql || x_arr_columns(i) || ' AS ' || x_arr_columns(i);
1842 END IF;
1843 END LOOP;
1844 h_sql:= h_sql || ' FROM ' || x_table_name;
1845 h_sql:= h_sql || ' WHERE ' || h_condition;
1846 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1847 --BUG 1224991
1848 IF UPPER(x_table_name) = 'BSC_KPI_DEFAULTS_B' Or UPPER(x_table_name) = 'BSC_KPI_DEFAULTS_TL' THEN
1849 h_sql:= 'UPDATE ' || x_table_name;
1850 h_sql:= h_sql || ' SET TAB_ID = (SELECT TAB_ID FROM BSC_TAB_INDICATORS WHERE INDICATOR =:1)'; --|| x_Trg_kpi || ')';
1851 h_sql:= h_sql || ' WHERE INDICATOR = :2'; --|| x_Trg_kpi;
1852 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300 ,part2
1853 Execute Immediate h_sql USING x_Trg_kpi, x_Trg_kpi;
1854 END IF;
1855
1856 --Bug 2258410 don't override the user wizard preferences
1857 IF x_table_name = 'BSC_KPI_ANALYSIS_OPTIONS_B' THEN
1858 -- Take the Values from the temporal (negative) records
1859 OPEN c_userwizard;
1860 FETCH c_userwizard INTO h_ag,h_aO,h_aOP,h_aOG,h_usl;
1861 WHILE c_userwizard%FOUND LOOP
1862 --Update the USER_LEVEL1 to this value
1863 h_sql := 'UPDATE ' || x_table_name;
1864 h_sql := h_sql || ' SET USER_LEVEL1 = :1'; --|| h_usl;
1865 h_sql := h_sql || ' WHERE INDICATOR = :2'; --|| x_Trg_kpi ;
1866 h_sql := h_sql || ' AND ANALYSIS_GROUP_ID = :3'; --|| h_ag ;
1867 h_sql := h_sql || ' AND OPTION_ID = :4'; --|| h_aO ;
1868 h_sql := h_sql || ' AND PARENT_OPTION_ID = :5'; --|| h_aOP ;
1869 h_sql := h_sql || ' AND GRANDPARENT_OPTION_ID = :6'; --|| h_aOG ;
1870 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); -bug 3111300 ,part2
1871 Execute Immediate h_sql USING h_usl, x_Trg_kpi, h_ag, h_aO, h_aOP, h_aOG;
1872
1873 FETCH c_userwizard INTO h_ag,h_aO,h_aOP,h_aOG,h_usl;
1874 END LOOP;
1875 CLOSE c_userwizard;
1876 END IF;
1877 END IF;
1878
1879 EXCEPTION
1880 WHEN OTHERS THEN
1881 --DBMS_OUTPUT.PUT_LINE('n' || SQLERRM);
1882 BSC_MESSAGE.Add(x_message => SQLERRM,
1883 x_source => 'Duplicate_Record_by_Indicator');
1884 RAISE;
1885 END Duplicate_Record_by_Indicator;
1886
1887
1888 PROCEDURE insert_kpi_meas_data (
1889 p_table_name IN VARCHAR2
1890 , p_src_kpi IN NUMBER
1891 , p_trg_kpi IN NUMBER
1892 , p_src_kpi_measure_id IN NUMBER
1893 , p_trg_kpi_measure_id IN NUMBER
1894 )
1895 IS
1896 CURSOR c_column IS
1897 SELECT column_name
1898 FROM all_tab_columns
1899 WHERE table_name = p_table_name
1900 AND owner = DECODE(USER, BSC_APPS.get_user_schema('APPS'), BSC_APPS.get_user_schema, USER)
1901 AND UPPER(column_name) <> 'KPI_MEASURE_ID'
1902 AND UPPER(column_name) <> 'INDICATOR'
1903 ORDER BY column_name;
1904
1905 h_sql VARCHAR2(32000);
1906 h_column VARCHAR2(100);
1907 x_arr_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
1908 x_num_columns NUMBER;
1909
1910 BEGIN
1911 -- Initialize BSC/APPS global variables
1912 BSC_APPS.Init_Bsc_Apps;
1913
1914 --Open COLUMNS by table cursor
1915 x_num_columns := 0;
1916 IF c_column%ISOPEN THEN
1917 CLOSE c_column;
1918 END IF;
1919 OPEN c_column;
1920 FETCH c_column INTO h_column;
1921 WHILE c_column%FOUND LOOP
1922 x_num_columns := x_num_columns + 1;
1923 x_arr_columns(x_num_columns) := h_column;
1924 FETCH c_column INTO h_column;
1925 END LOOP;
1926 CLOSE c_column;
1927
1928 IF x_num_columns > 0 THEN
1929
1930 --Create the sql to insert
1931 h_sql:= 'INSERT INTO ' || p_table_name || ' ( indicator, kpi_measure_id, ';
1932 FOR i IN 1..x_num_columns LOOP
1933 IF i <> 1 THEN
1934 h_sql:= h_sql || ',';
1935 END IF;
1936 h_sql:= h_sql || x_arr_columns(i);
1937 END LOOP;
1938 h_sql:= h_sql || ' ) ';
1939 h_sql:= h_sql || ' SELECT ' || p_trg_kpi || ' AS indicator, ' ;
1940 IF p_trg_kpi_measure_id IS NULL THEN
1941 h_sql:= h_sql || ' NULL AS kpi_measure_id, ';
1942 ELSE
1943 h_sql:= h_sql || p_trg_kpi_measure_id || ' AS kpi_measure_id, ';
1944 END IF;
1945 FOR i IN 1..x_num_columns LOOP
1946 IF i <> 1 THEN
1947 h_sql:= h_sql || ',';
1948 END IF;
1949 h_sql:= h_sql || x_arr_columns(i) || ' AS ' || x_arr_columns(i);
1950 END LOOP;
1951
1952 h_sql:= h_sql || ' FROM ' || BSC_DESIGNER_PVT.Format_DbLink_String(p_table_name);
1953 h_sql:= h_sql || ' WHERE indicator = ' || p_src_kpi ;
1954 IF p_src_kpi_measure_id IS NULL THEN
1955 h_sql:= h_sql || ' AND kpi_measure_id IS NULL';
1956 ELSE
1957 h_sql:= h_sql || ' AND kpi_measure_id = ' || p_src_kpi_measure_id;
1958 END IF;
1959 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1960
1961 END IF;
1962
1963 EXCEPTION
1964 WHEN OTHERS THEN
1965 IF c_column%ISOPEN THEN
1966 CLOSE c_column;
1967 END IF;
1968 BSC_MESSAGE.Add( x_message => SQLERRM
1969 , x_source => 'BSC_DESIGNER_PVT.insert_kpi_meas_data'
1970 );
1971 RAISE;
1972 END insert_kpi_meas_data;
1973
1974
1975
1976 PROCEDURE Copy_Thresholds (
1977 p_src_kpi IN NUMBER
1978 , p_trg_kpi IN NUMBER
1979 , p_src_kpi_measure_id IN NUMBER := NULL
1980 , p_trg_kpi_measure_id IN NUMBER := NULL
1981 )
1982 IS
1983 l_sql VARCHAR2(32000);
1984 l_src_color_range_id NUMBER;
1985 l_trg_color_range_id NUMBER;
1986 l_src_property_value NUMBER;
1987 TYPE c_cur_type IS REF CURSOR;
1988 c_color_range c_cur_type;
1989 BEGIN
1990 l_sql := BSC_DESIGNER_PVT.Format_DbLink_String('SELECT color_range_id, property_value FROM bsc_color_type_props');
1991 l_sql := l_sql || 'WHERE indicator = :1';
1992
1993 IF p_src_kpi_measure_id IS NULL THEN
1994 l_sql := l_sql || ' AND kpi_measure_id IS NULL';
1995 OPEN c_color_range FOR l_sql USING p_src_kpi;
1996 ELSE
1997 l_sql := l_sql || ' AND kpi_measure_id = :2';
1998 OPEN c_color_range FOR l_sql USING p_src_kpi, p_src_kpi_measure_id;
1999 END IF;
2000 FETCH c_color_range INTO l_src_color_range_id, l_src_property_value;
2001 WHILE c_color_range%FOUND LOOP
2002
2003 SELECT bsc_color_range_id_s.NEXTVAL INTO l_trg_color_range_id from dual;
2004
2005 IF p_trg_kpi_measure_id IS NOT NULL THEN
2006 UPDATE bsc_color_type_props
2007 SET color_range_id = l_trg_color_range_id
2008 WHERE indicator = p_trg_kpi
2009 AND kpi_measure_id = p_trg_kpi_measure_id
2010 AND NVL(property_value, -1) = DECODE(l_src_property_value, NULL, -1, l_src_property_value);
2011 ELSE
2012 UPDATE bsc_color_type_props
2013 SET color_range_id = l_trg_color_range_id
2014 WHERE indicator = p_trg_kpi
2015 AND kpi_measure_id IS NULL;
2016 END IF;
2017
2018
2019 l_sql := 'INSERT INTO bsc_color_ranges (color_range_id, color_range_sequence, low, high, color_id)';
2020 l_sql := l_sql || 'SELECT '|| l_trg_color_range_id || ' AS color_range_id, color_range_sequence';
2021 l_sql := l_sql || BSC_DESIGNER_PVT.Format_DbLink_String(', low , high , color_id FROM bsc_color_ranges');
2022 l_sql := l_sql || 'WHERE color_range_id = :1';
2023
2024 EXECUTE IMMEDIATE l_sql USING l_src_color_range_id;
2025
2026 FETCH c_color_range INTO l_src_color_range_id, l_src_property_value;
2027 END LOOP;
2028
2029 EXCEPTION
2030 WHEN OTHERS THEN
2031 IF c_color_range%ISOPEN THEN
2032 CLOSE c_color_range;
2033 END IF;
2034 BSC_MESSAGE.Add( x_message => SQLERRM
2035 , x_source => 'BSC_DESIGNER_PVT.Copy_Thresholds'
2036 );
2037 RAISE;
2038 END Copy_Thresholds;
2039
2040
2041 PROCEDURE Copy_Records_by_Obj_Kpi_Meas (
2042 p_src_kpi IN NUMBER
2043 , p_trg_kpi IN NUMBER
2044 )
2045 IS
2046 l_trg_kpi_measure_id NUMBER;
2047 h_sql VARCHAR2(32000);
2048 l_ao_comb_sql VARCHAR2(32000);
2049 TYPE c_cur_type IS REF CURSOR;
2050 c_src_ao_comb c_cur_type;
2051 l_src_kpi_measure_id NUMBER;
2052 l_Count NUMBER := 0;
2053 BEGIN
2054 -- Initialize BSC/APPS global variables
2055 BSC_APPS.Init_Bsc_Apps;
2056 Init_Kpi_Metadata_Tables_Array();
2057
2058 -- Delete all the existing rows for the Target Objective (if any)
2059 FOR i IN 1 .. g_num_obj_kpi_metadata_tables LOOP
2060 IF (g_obj_kpi_metadata_tables(i).duplicate_data = BSC_UTILITY.YES) THEN
2061 h_sql := 'DELETE ' || g_obj_kpi_metadata_tables(i).table_name ||
2062 ' WHERE indicator = ' || p_trg_kpi;
2063 BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2064
2065 END IF;
2066 END LOOP;
2067
2068 l_ao_comb_sql := BSC_DESIGNER_PVT.Format_DbLink_String('SELECT distinct kpi_measure_id FROM bsc_kpi_analysis_measures_b');
2069 l_ao_comb_sql := l_ao_comb_sql || 'WHERE indicator = :1';
2070 OPEN c_src_ao_comb FOR l_ao_comb_sql USING p_src_kpi;
2071 LOOP
2072 FETCH c_src_ao_comb INTO l_src_kpi_measure_id;
2073 EXIT WHEN c_src_ao_comb%NOTFOUND;
2074
2075 SELECT bsc_kpi_measure_s.NEXTVAL INTO l_trg_kpi_measure_id from dual;
2076
2077 FOR i IN 1 .. g_num_obj_kpi_metadata_tables LOOP
2078 IF (g_obj_kpi_metadata_tables(i).duplicate_data = BSC_UTILITY.YES) THEN
2079 insert_kpi_meas_data (
2080 p_table_name => g_obj_kpi_metadata_tables(i).table_name
2081 , p_src_kpi => p_src_kpi
2082 , p_trg_kpi => p_trg_kpi
2083 , p_src_kpi_measure_id => l_src_kpi_measure_id
2084 , p_trg_kpi_measure_id => l_trg_kpi_measure_id
2085 );
2086 END IF;
2087 END LOOP;
2088
2089 -- Copy color thresholds/ranges
2090 Copy_Thresholds (
2091 p_src_kpi => p_src_kpi
2092 , p_trg_kpi => p_trg_kpi
2093 , p_src_kpi_measure_id => l_src_kpi_measure_id
2094 , p_trg_kpi_measure_id => l_trg_kpi_measure_id
2095 );
2096 END LOOP;
2097
2098 -- Check whether thresholds are specified at objective level.
2099 insert_kpi_meas_data (
2100 p_table_name => 'BSC_COLOR_TYPE_PROPS'
2101 , p_src_kpi => p_src_kpi
2102 , p_trg_kpi => p_trg_kpi
2103 , p_src_kpi_measure_id => NULL
2104 , p_trg_kpi_measure_id => NULL
2105 );
2106 SELECT COUNT(1) INTO l_Count
2107 FROM bsc_color_type_props
2108 WHERE indicator = p_trg_kpi AND kpi_measure_id IS NULL;
2109
2110 IF l_Count = 1 THEN
2111 Copy_Thresholds (
2112 p_src_kpi => p_src_kpi
2113 , p_trg_kpi => p_trg_kpi
2114 );
2115 END IF;
2116
2117 -- Update Prototype flag for all KPIs for the Shared objective to re-calculate color (non-production)
2118 UPDATE bsc_kpi_analysis_measures_b
2119 SET prototype_flag = 7
2120 WHERE indicator = p_trg_kpi;
2121
2122 EXCEPTION
2123 WHEN OTHERS THEN
2124 BSC_MESSAGE.Add( x_message => SQLERRM
2125 , x_source => 'BSC_DESIGNER_PVT.Copy_Records_by_Obj_Kpi_Meas'
2126 );
2127 RAISE;
2128 END Copy_Records_by_Obj_Kpi_Meas;
2129
2130 /*===========================================================================+
2131 | DESCRIPTION:
2132 | This fucntion made a copy of all the metadata table for kpi from one kpi to other kpi.,
2133 | 1- Fisrt get all the KPI metadata tables
2134 | 2- Duplicate record for all of them
2135 |
2136 | NOTE : This function allways expect the INDICATOR columns as part of the table.
2137 | - BSC_KPIS_B is a special case
2138 |
2139 | HISTORY
2140 | 05-FEB-2002 Henry Camacho Created
2141 +---------------------------------------------------------------------------*/
2142 PROCEDURE Duplicate_KPI_Metadata (
2143 x_Src_kpi IN NUMBER
2144 , x_Trg_kpi IN NUMBER
2145 , x_Shared_apply IN NUMBER
2146 , x_Shared_tables IN VARCHAR2
2147 ) IS
2148
2149 l_kpi_metadata_tables t_kpi_metadata_tables;
2150 l_num_kpi_metadata_tables NUMBER := 0;
2151 h_table_name VARCHAR2(30);
2152 h_condition VARCHAR2(1000);
2153 h_sql VARCHAR2(32000);
2154 x_arr BSC_UPDATE_UTIL.t_array_of_varchar2;
2155 x_arrayShared_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2156 x_num NUMBER;
2157 i NUMBER;
2158 h_CSF_id NUMBER;
2159 h_Ind_group_id NUMBER;
2160 h_Disp_Order NUMBER;
2161 h_Source_Flag NUMBER;
2162 h_Source_Indicator NUMBER;
2163 h_SRC_Flag NUMBER;
2164
2165 h_aopt_table NUMBER;
2166 h_Trg_kpi_neg NUMBER;
2167
2168 h_TRG_Flag NUMBER;
2169 BEGIN
2170 -- Initialize BSC/APPS global variables
2171 BSC_APPS.Init_Bsc_Apps;
2172 Init_Kpi_Metadata_Tables_Array();
2173
2174 SELECT NVL(CSF_ID,1)
2175 INTO h_CSF_id
2176 FROM BSC_KPIS_B WHERE INDICATOR = x_Trg_kpi;
2177 SELECT NVL(IND_GROUP_ID,1)
2178 INTO h_Ind_group_id
2179 FROM BSC_KPIS_B WHERE INDICATOR = x_Trg_kpi;
2180 SELECT NVL(DISP_ORDER,1)
2181 INTO h_Disp_Order
2182 FROM BSC_KPIS_B WHERE INDICATOR = x_Trg_kpi;
2183 SELECT NVL(SHARE_FLAG,0)
2184 INTO h_Source_Flag
2185 FROM BSC_KPIS_B WHERE INDICATOR = x_Trg_kpi;
2186 SELECT SOURCE_INDICATOR
2187 INTO h_Source_Indicator
2188 FROM BSC_KPIS_B WHERE INDICATOR = x_Trg_kpi;
2189 --Get Prototype Flag 04/26/02
2190 -- Get the status
2191 SELECT NVL(PROTOTYPE_FLAG,1)
2192 INTO h_TRG_Flag
2193 FROM BSC_KPIS_B WHERE INDICATOR = x_Trg_kpi;
2194
2195 --Fisrt Duplicate the data to BSC_KPIS_B
2196 Duplicate_Record_by_Indicator('BSC_KPIS_B', x_Src_kpi, x_Trg_kpi);
2197
2198 --Restore the properties
2199 BscKpisB_Update(x_Trg_kpi, 'CSF_ID', h_CSF_id);
2200 BscKpisB_Update(x_Trg_kpi, 'IND_GROUP_ID', h_Ind_group_id);
2201 BscKpisB_Update(x_Trg_kpi, 'DISP_ORDER', h_Disp_Order);
2202 BscKpisB_Update(x_Trg_kpi, 'SHARE_FLAG', h_Source_Flag);
2203 BscKpisB_Update(x_Trg_kpi, 'SOURCE_INDICATOR', h_Source_Indicator);
2204
2205 --UPDATE SHARE_FLAG
2206 h_condition := 'WHERE INDICATOR = :1 '; --|| x_Trg_kpi;
2207 h_sql := 'UPDATE BSC_KPIS_B SET SHARE_FLAG =3 ' || h_condition;
2208 Execute Immediate h_sql USING x_Trg_kpi;
2209
2210 h_condition := 'WHERE INDICATOR = :2 '; --|| x_Trg_kpi;
2211 h_sql := 'UPDATE BSC_KPIS_B SET SOURCE_INDICATOR =:1 ' --|| x_Src_kpi
2212 || h_condition;
2213 Execute Immediate h_sql USING x_Src_kpi, x_Trg_kpi;
2214
2215 -- Get the status
2216 SELECT NVL(PROTOTYPE_FLAG,1)
2217 INTO h_SRC_Flag
2218 FROM BSC_KPIS_B WHERE INDICATOR = x_Src_kpi;
2219
2220 ---04/26/02 Validate the prototype flag
2221 IF h_SRC_Flag <> h_TRG_Flag THEN
2222 -- Critical status are propage to the child
2223 -- Bug #2652366 fix (added h_SRC_Flag = 7 condition)
2224 IF h_SRC_Flag = 1 OR h_SRC_Flag = 3 OR h_SRC_Flag = 7 THEN
2225 BscKpisB_Update(x_Trg_kpi, 'PROTOTYPE_FLAG', h_SRC_Flag);
2226 ELSE
2227 BscKpisB_Update(x_Trg_kpi, 'PROTOTYPE_FLAG', h_TRG_Flag);
2228 END IF;
2229 END IF;
2230
2231
2232 x_num := 0;
2233 IF x_Shared_apply = 0 THEN
2234 FOR i IN 1..g_num_kpi_metadata_tables LOOP
2235 IF(g_kpi_metadata_tables(i).duplicate_data = bsc_utility.YES) THEN
2236 l_num_kpi_metadata_tables := l_num_kpi_metadata_tables + 1;
2237 l_kpi_metadata_tables(l_num_kpi_metadata_tables).table_name := g_kpi_metadata_tables(i).table_name;
2238 l_kpi_metadata_tables(l_num_kpi_metadata_tables).table_type := g_kpi_metadata_tables(i).table_type;
2239 l_kpi_metadata_tables(l_num_kpi_metadata_tables).table_column := g_kpi_metadata_tables(i).table_column;
2240 END IF;
2241 END LOOP;
2242
2243 ELSE
2244 x_num := Decompose_Varchar_List(x_Shared_tables,x_arrayShared_tables ,',');
2245 FOR i IN 1..x_num LOOP
2246 FOR j IN 1..g_num_kpi_metadata_tables LOOP
2247 IF(g_kpi_metadata_tables(j).table_name = x_arrayShared_tables(i)) THEN
2248 l_num_kpi_metadata_tables := l_num_kpi_metadata_tables + 1;
2249 l_kpi_metadata_tables(l_num_kpi_metadata_tables).table_name := g_kpi_metadata_tables(j).table_name;
2250 l_kpi_metadata_tables(l_num_kpi_metadata_tables).table_type := g_kpi_metadata_tables(j).table_type;
2251 l_kpi_metadata_tables(l_num_kpi_metadata_tables).table_column := g_kpi_metadata_tables(j).table_column;
2252 END IF;
2253 END LOOP;
2254 END LOOP;
2255 END IF;
2256
2257 h_Trg_kpi_neg := -1 * x_Trg_kpi;
2258 FOR i IN 1..l_num_kpi_metadata_tables LOOP
2259 IF(l_kpi_metadata_tables(i).table_name = 'BSC_KPI_ANALYSIS_OPTIONS_B') THEN
2260 UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
2261 SET INDICATOR = h_Trg_kpi_neg
2262 WHERE INDICATOR = x_Trg_kpi;
2263 END IF;
2264 IF(l_kpi_metadata_tables(i).table_name = 'BSC_KPI_PERIODICITIES') THEN
2265 UPDATE BSC_KPI_PERIODICITIES
2266 SET INDICATOR = h_Trg_kpi_neg
2267 WHERE INDICATOR = x_Trg_kpi;
2268 END IF;
2269 IF(l_kpi_metadata_tables(i).table_name = 'BSC_KPI_CALCULATIONS') THEN
2270 UPDATE BSC_KPI_CALCULATIONS
2271 SET INDICATOR = h_Trg_kpi_neg
2272 WHERE INDICATOR = x_Trg_kpi;
2273 END IF;
2274 IF(l_kpi_metadata_tables(i).table_name = 'BSC_KPI_DIM_LEVELS_B') THEN
2275 UPDATE BSC_KPI_DIM_LEVELS_B
2276 SET INDICATOR = h_Trg_kpi_neg
2277 WHERE INDICATOR = x_Trg_kpi;
2278 END IF;
2279 IF(l_kpi_metadata_tables(i).table_name = 'BSC_KPI_DIM_LEVEL_PROPERTIES') THEN
2280 UPDATE BSC_KPI_DIM_LEVEL_PROPERTIES
2281 SET INDICATOR = h_Trg_kpi_neg
2282 WHERE INDICATOR = x_Trg_kpi;
2283 END IF;
2284
2285 END LOOP;
2286
2287 FOR i IN 1..l_num_kpi_metadata_tables LOOP
2288 IF ((l_kpi_metadata_tables(i).table_name <> 'BSC_KPIS_B' )
2289 AND (l_kpi_metadata_tables(i).table_name <> 'BSC_KPIS_TL' )
2290 AND (l_kpi_metadata_tables(i).table_name <> 'BSC_KPI_CAUSE_EFFECT_RELS' )
2291 AND (l_kpi_metadata_tables(i).table_name <> 'BSC_KPI_DATA_TABLES') )THEN
2292 Copy_Record_by_Indicator_Table(l_kpi_metadata_tables(i).table_name,l_kpi_metadata_tables(i).table_type,l_kpi_metadata_tables(i).table_column, x_Src_kpi, x_Trg_kpi);
2293 ELSE
2294 IF l_kpi_metadata_tables(i).table_name = 'BSC_KPI_DATA_TABLES' THEN
2295 IF h_SRC_Flag <> 0 THEN --gActionFlag.Normal =0
2296 Copy_Record_by_Indicator_Table(l_kpi_metadata_tables(i).table_name,l_kpi_metadata_tables(i).table_type,l_kpi_metadata_tables(i).table_column, x_Src_kpi, x_Trg_kpi);
2297 END IF;
2298 END IF;
2299 END IF;
2300 END LOOP;
2301
2302 -- Copy records per KPI Measure
2303 Copy_Records_by_Obj_Kpi_Meas(x_src_kpi, x_trg_kpi);
2304
2305 --DBMS_OUTPUT.PUT_LINE(' DELETEING BSC_KPI_ANALYSIS_OPTIONS_B:-' );
2306 h_sql := 'DELETE BSC_KPI_ANALYSIS_OPTIONS_B WHERE INDICATOR=:1'; --|| h_Trg_kpi_neg;
2307 Execute Immediate h_sql USING h_Trg_kpi_neg;
2308 h_sql := 'DELETE BSC_KPI_ANALYSIS_OPTIONS_TL WHERE INDICATOR=:1'; --|| h_Trg_kpi_neg;
2309 Execute Immediate h_sql USING h_Trg_kpi_neg;
2310 h_sql := 'DELETE BSC_KPI_PERIODICITIES WHERE INDICATOR=:1'; --|| h_Trg_kpi_neg;
2311 Execute Immediate h_sql USING h_Trg_kpi_neg;
2312 h_sql := 'DELETE BSC_KPI_CALCULATIONS WHERE INDICATOR=:1'; --|| h_Trg_kpi_neg;
2313 Execute Immediate h_sql USING h_Trg_kpi_neg;
2314 h_sql := 'DELETE BSC_KPI_DIM_LEVELS_B WHERE INDICATOR=:1'; --|| h_Trg_kpi_neg;
2315 Execute Immediate h_sql USING h_Trg_kpi_neg;
2316 h_sql := 'DELETE BSC_KPI_DIM_LEVEL_PROPERTIES WHERE INDICATOR=:1'; --|| h_Trg_kpi_neg;
2317 Execute Immediate h_sql USING h_Trg_kpi_neg;
2318
2319 --UPDATE SHARE_FLAG
2320 h_condition := 'WHERE INDICATOR = :1'; --|| x_Trg_kpi;
2321 h_sql := 'UPDATE BSC_KPIS_B SET SHARE_FLAG =2 ' || h_condition;
2322 Execute Immediate h_sql USING x_Trg_kpi;
2323
2324
2325 EXCEPTION
2326 WHEN OTHERS THEN
2327 h_sql := 'DELETE BSC_KPI_ANALYSIS_OPTIONS_B WHERE INDICATOR=:1'; --|| h_Trg_kpi_neg;
2328 Execute Immediate h_sql USING h_Trg_kpi_neg;
2329 h_sql := 'DELETE BSC_KPI_ANALYSIS_OPTIONS_TL WHERE INDICATOR=:1'; --|| h_Trg_kpi_neg;
2330 Execute Immediate h_sql USING h_Trg_kpi_neg;
2331 h_sql := 'DELETE BSC_KPI_PERIODICITIES WHERE INDICATOR=:1'; --|| h_Trg_kpi_neg;
2332 Execute Immediate h_sql USING h_Trg_kpi_neg;
2333 h_sql := 'DELETE BSC_KPI_CALCULATIONS WHERE INDICATOR=:1'; --|| h_Trg_kpi_neg;
2334 Execute Immediate h_sql USING h_Trg_kpi_neg;
2335 h_sql := 'DELETE BSC_KPI_DIM_LEVELS_B WHERE INDICATOR=:1'; --|| h_Trg_kpi_neg;
2336 Execute Immediate h_sql USING h_Trg_kpi_neg;
2337 h_sql := 'DELETE BSC_KPI_DIM_LEVEL_PROPERTIES WHERE INDICATOR=:1'; --|| h_Trg_kpi_neg;
2338 Execute Immediate h_sql USING h_Trg_kpi_neg;
2339 BSC_MESSAGE.Add(x_message => SQLERRM,
2340 x_source => 'Duplicate_KPI_Metadata');
2341 RAISE;
2342 END Duplicate_KPI_Metadata;
2343
2344
2345
2346 PROCEDURE BscKpisB_Update(x_Ind IN NUMBER, x_Field IN VARCHAR, x_Val IN VARCHAR) IS
2347 /*===========================================================================+
2348 | DESCRIPTION:
2349 | Updates a record in the BSC_KPIS_B table.
2350 | PARAMETERS:
2351 | Ind Indicator Code
2352 | VARIABLE Variable name
2353 | Valor Value
2354 | OUTPUT:
2355 | AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2356 | henry camacho 03/17/99
2357 | hcc. 12/01/99 Data Model 4.0
2358 +---------------------------------------------------------------------------*/
2359 h_sql VARCHAR2(32000);
2360 BEGIN
2361 --Validation
2362 h_sql := 'UPDATE BSC_KPIS_B SET ' || x_Field || ' = :1 '
2363 || ' WHERE INDICATOR=:2'; --|| x_Ind;
2364 --BSC_UPDATE_UTIL.Execute_Immediate(h_sql); --bug 3111300 ,part2
2365 Execute Immediate h_sql USING x_Val, x_Ind;
2366 --DBMS_OUTPUT.PUT_LINE('h_sql :' || h_sql );
2367
2368 EXCEPTION
2369 WHEN OTHERS THEN
2370 --DBMS_OUTPUT.PUT_LINE('p' || SQLERRM);
2371 BSC_MESSAGE.Add(x_message => SQLERRM,
2372 x_source => 'BscKpisB_Update');
2373 RAISE;
2374 END BscKpisB_Update;
2375
2376 /*===========================================================================+
2377 | FUNCTION Decompose_Varchar_List
2378 +============================================================================*/
2379 FUNCTION Decompose_Varchar_List(
2380 x_string IN VARCHAR2,
2381 x_array IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2382 x_separator IN VARCHAR2
2383 ) RETURN NUMBER IS
2384
2385 h_num_items NUMBER := 0;
2386
2387 h_sub_string VARCHAR2(32700);
2388 h_position NUMBER;
2389
2390 BEGIN
2391 IF x_string IS NOT NULL THEN
2392 h_sub_string := x_string;
2393 h_position := INSTR(h_sub_string, x_separator);
2394
2395 WHILE h_position <> 0 LOOP
2396 h_num_items := h_num_items + 1;
2397 x_array(h_num_items) := RTRIM(LTRIM(SUBSTR(h_sub_string, 1, h_position - 1)));
2398 h_sub_string := SUBSTR(h_sub_string, h_position + 1);
2399 h_position := INSTR(h_sub_string, x_separator);
2400 END LOOP;
2401
2402 h_num_items := h_num_items + 1;
2403 x_array(h_num_items) := RTRIM(LTRIM(h_sub_string));
2404 END IF;
2405 RETURN h_num_items;
2406 END Decompose_Varchar_List;
2407
2408 /*===========================================================================+
2409 | FUNCTION Commdim_DimSetDefaulisPMFbyTab
2410 | DESCRIPTION:
2411 | Validate if the Default dimension set is or is not PMF. by Tab
2412 |
2413 | USAGE :
2414 | It is to effort a PFM rule, If there is PMF dim set as default-disable the list Button
2415 | PARAMETERS:
2416 | Tab Id : Tab Id
2417 | RETURN 'T'rue or 'F'alse,
2418 | OUTPUT:
2419 | AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
2420 | henry camacho 02/07/02
2421 +---------------------------------------------------------------------------*/
2422 FUNCTION Commdim_DefltDSetisPMFbyTab(
2423 x_Tab_id IN NUMBER
2424 ) RETURN VARCHAR2 IS
2425
2426 CURSOR c_dimset IS
2427 SELECT DFT.INDICATOR
2428 FROM BSC_DB_COLOR_KPI_V DFT,
2429 (SELECT DISTINCT INDICATOR,DIM_SET_ID,NVL(LEVEL_SOURCE,'BSC') SOURCE
2430 FROM BSC_KPI_DIM_LEVELS_VL) DIM
2431 WHERE DFT.INDICATOR = DIM .INDICATOR
2432 AND DFT.DIM_SET_ID = DIM .DIM_SET_ID
2433 AND DFT.TAB_ID = x_Tab_id
2434 AND DIM.SOURCE ='PMF';
2435 h_val VARCHAR2(1);
2436 h_kpi NUMBER;
2437
2438 BEGIN
2439 h_val :='F';
2440 OPEN c_dimset;
2441 FETCH c_dimset INTO h_kpi;
2442 IF c_dimset%FOUND THEN
2443 h_val :='T';
2444 END IF;
2445 CLOSE c_dimset;
2446 RETURN h_val;
2447 END Commdim_DefltDSetisPMFbyTab;
2448
2449
2450 -- Color By KPI: Mark KPI Prototype Flag
2451 PROCEDURE Update_Kpi_Prototype_Flag (
2452 p_objective_id IN NUMBER
2453 , p_kpi_measure_id IN NUMBER := NULL
2454 , p_flag IN NUMBER
2455 ) IS
2456
2457 l_anal_measure_rec BSC_ANALYSIS_OPTION_PUB.bsc_option_rec_type;
2458
2459 BEGIN
2460
2461 IF p_objective_id IS NOT NULL AND p_kpi_measure_id IS NOT NULL THEN
2462 UPDATE bsc_kpi_analysis_measures_b
2463 SET prototype_flag = p_flag
2464 WHERE indicator = p_objective_id
2465 AND kpi_measure_id = p_kpi_measure_id;
2466 ELSIF p_objective_id IS NOT NULL THEN
2467 UPDATE bsc_kpi_analysis_measures_b
2468 SET prototype_flag = p_flag
2469 WHERE indicator = p_objective_id;
2470 END IF;
2471
2472 EXCEPTION
2473 WHEN OTHERS THEN
2474 BSC_MESSAGE.Add(x_message => SQLERRM,
2475 x_source => 'BSC_DESIGNER_PVT.Update_Kpi_Prototype_Flag');
2476 END Update_Kpi_Prototype_Flag;
2477
2478
2479 /*===========================================================================+
2480 | PROCEDURE
2481 | ActionFlag_Change
2482 |
2483 | PURPOSE
2484 | It control the incremental change flags.
2485 | PARAMETERS
2486 | x_indicator
2487 | x_newflag : New action flag
2488 | HISTORY
2489 | 19-APR-2002 Henry Camacho Created
2490 +---------------------------------------------------------------------------*/
2491 PROCEDURE ActionFlag_Change (
2492 x_indicator IN NUMBER
2493 , x_newflag IN NUMBER
2494 ) IS
2495 BEGIN
2496 ActionFlag_Change_Cascade (
2497 p_indicator => x_indicator
2498 , p_newflag => x_newflag
2499 , p_cascade_color => TRUE
2500 );
2501 EXCEPTION
2502 WHEN OTHERS THEN
2503 BSC_MESSAGE.Add(x_message => SQLERRM,
2504 x_source => 'ActionFlag_Change_Cascade');
2505 END ActionFlag_Change;
2506
2507
2508 /* If p_cascade_color is TRUE, then the color_change flag (7) will be cascaded to KPI
2509 * Prototype flag too.
2510 * p_cascade_color will be FALSE for :
2511 * 1. Color rollup type change for the Objective
2512 * 2. Change in the WA properties for the Objective (weights, color method & color thresholds etc.)
2513 * 3. Change of the default KPI for the Objective
2514 * 4. Change in the numeric equivalent of the system level colors for WA rollup
2515 */
2516 PROCEDURE ActionFlag_Change_Cascade (
2517 p_indicator IN NUMBER
2518 , p_newflag IN NUMBER
2519 , p_cascade_color IN BOOLEAN
2520 ) IS
2521
2522 h_stage NUMBER(1);
2523 h_structure NUMBER(1);
2524 h_currentFlag NUMBER(1);
2525 h_newflag NUMBER(1);
2526 h_tmp NUMBER(1);
2527
2528 -- Time stamp
2529 l_commit varchar2(10);
2530 my_kpi_Record BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
2531 l_return_status varchar2(100);
2532 l_msg_data varchar2(1000);
2533 l_msg_count number;
2534 l_count_kpis number;
2535
2536 BEGIN
2537
2538 -- Current stage
2539 h_stage := 1;
2540 SELECT PROPERTY_VALUE
2541 INTO h_stage
2542 FROM BSC_SYS_INIT
2543 WHERE PROPERTY_CODE = 'SYSTEM_STAGE';
2544
2545 --DBMS_OUTPUT.PUT_LINE('1. System Stage = ' || h_stage);
2546
2547 IF h_stage = 1 THEN
2548 -- In stage 1 flag to prototype
2549 h_structure := G_ActionFlag.Prototype;
2550 ELSE
2551 --In stage 2 flag structure change
2552 h_structure := G_ActionFlag.GAA_Structure;
2553 END IF;
2554
2555 --DBMS_OUTPUT.PUT_LINE('2. h_structure := ' || h_structure);
2556
2557 -- Get the current indicator status
2558 h_currentFlag := 1;
2559 SELECT PROTOTYPE_FLAG
2560 INTO h_currentFlag
2561 FROM BSC_KPIS_B
2562 WHERE INDICATOR = p_indicator;
2563
2564 --DBMS_OUTPUT.PUT_LINE('3. Current KPI Deflt values (' || p_indicator || ') = ' || h_currentFlag);
2565
2566 --Refresh The Kpi Defaults
2567 Deflt_RefreshKpi(p_indicator);
2568
2569 --DBMS_OUTPUT.PUT_LINE('4. Refreshed the KPI Defaults');
2570
2571 -- Update the kpi timestamp
2572 my_kpi_Record.Bsc_Kpi_Id := p_indicator;
2573 l_commit := FND_API.G_FALSE;
2574 BSC_KPI_PUB.Update_Kpi_Time_Stamp( l_commit,my_kpi_Record,l_return_status,l_msg_count,l_msg_data);
2575
2576 -- Not change in the status
2577 IF p_newflag = h_currentFlag THEN
2578 --DBMS_OUTPUT.PUT_LINE('exit p_newflag = h_currentFlag');
2579 RETURN;
2580 END IF;
2581
2582 -- We can't change this flag (Structure changes)
2583 IF h_currentFlag = h_structure THEN
2584 --DBMS_OUTPUT.PUT_LINE('exit h_currentFlag = h_structure ');
2585 RETURN;
2586 END IF;
2587
2588 -- For Indicator deleted, don't change
2589 IF h_currentFlag = G_ActionFlag.Delete_kpi THEN
2590 --DBMS_OUTPUT.PUT_LINE('exit h_currentFlag = G_ActionFlag.Delete_kpi ');
2591 RETURN;
2592 END IF;
2593 -- Don't change in the flag values
2594
2595 --DBMS_OUTPUT.PUT_LINE('5a. p_newflag = ' || p_newflag || ' , h_currentFlag = ' || h_currentFlag);
2596 IF p_newflag <> h_currentFlag THEN
2597 -- Normal Indicator
2598 --DBMS_OUTPUT.PUT_LINE('5. Normal Indicator');
2599 h_newflag := p_newflag;
2600 IF p_newflag = G_ActionFlag.Normal THEN
2601 --DBMS_OUTPUT.PUT_LINE('6. p_newflag = G_ActionFlag.Normal ');
2602 RETURN;
2603 ELSIF p_newflag = G_ActionFlag.Delete_kpi THEN
2604 --DBMS_OUTPUT.PUT_LINE('7. p_newflag = G_ActionFlag.Delete_kpi ');
2605 RETURN;
2606 -- The mayor Hierarchy is GAA_STRUCTURE (3)
2607 ELSIF p_newflag = G_ActionFlag.GAA_Structure THEN
2608 --DBMS_OUTPUT.PUT_LINE('8. p_newflag = G_ActionFlag.GAA_Structure');
2609 h_newflag := h_structure;
2610 -- Prototype Hierarchy
2611 ELSIF p_newflag = G_ActionFlag.Prototype THEN
2612 --DBMS_OUTPUT.PUT_LINE('9. p_newflag = G_ActionFlag.Prototype');
2613 h_newflag := h_structure;
2614 -- Reconfigurate the update process
2615 ELSIF p_newflag = G_ActionFlag.GAA_Update THEN
2616 -- CHANGE FOR Normal,GAA_color,Update_Update,Update_color
2617 IF h_currentFlag = G_ActionFlag.Normal OR
2618 h_currentFlag = G_ActionFlag.GAA_Color OR
2619 h_currentFlag = G_ActionFlag.Update_Update OR
2620 h_currentFlag = G_ActionFlag.Update_color THEN
2621 h_newflag := G_ActionFlag.GAA_Update;
2622 ELSE
2623 -- G_ActionFlag.Delete,G_ActionFlag.GAA_Structure G_ActionFlag.Prototype
2624 h_newflag := h_currentFlag;
2625 END IF;
2626 -- Reconfigurate color process
2627 ELSIF p_newflag = G_ActionFlag.GAA_Color THEN
2628 -- CHANGE FOR NORMAL, UPDATE_COLOR
2629 IF h_currentFlag = G_ActionFlag.Normal OR h_currentFlag = G_ActionFlag.Update_color THEN
2630 h_newflag := G_ActionFlag.GAA_Color;
2631 ELSE
2632 -- Dont' change for Prototype,delte, structure
2633 h_newflag := h_currentFlag;
2634 END IF;
2635 -- ELSIF p_newflag = G_ActionFlag.Update_Update THEN -- Re-Color process
2636 -- ELSIF p_newflag = G_ActionFlag.Update_color THEN -- Color Update process
2637 END IF;
2638 END IF;
2639
2640 -- Changes or not
2641 IF h_currentFlag <> h_newflag THEN
2642 -- Refresh the tab panel
2643 --- ======>ON HOLD gRefresh_type.tab = True
2644
2645 -- Call BscKpisB_Update(indicator, "PROTOTYPE_FLAG", h_newflag)
2646 UPDATE BSC_KPIS_B SET PROTOTYPE_FLAG =h_newflag
2647 WHERE INDICATOR = p_indicator;
2648
2649 -- RECORD IN MIND_TABLES_NEW
2650 -- Recreate the Information for MIND_TABLES_NEW
2651 -- BUG 2629725 this IF was comment out NOCOPY
2652 IF h_newflag = h_structure THEN -- Structure Change . to Prototype
2653 -- Insert at least one record in the KPI_DATA_TABLES
2654 SELECT COUNT(INDICATOR)
2655 INTO l_count_kpis
2656 FROM BSC_KPI_DATA_TABLES
2657 WHERE INDICATOR = p_indicator
2658 AND TABLE_NAME IS NOT NULL;
2659
2660 IF(l_count_kpis = 0) THEN
2661 DELETE BSC_KPI_DATA_TABLES WHERE DIM_SET_ID =0 AND INDICATOR = p_indicator;
2662 INSERT INTO BSC_KPI_DATA_TABLES
2663 (INDICATOR,PERIODICITY_ID,DIM_SET_ID,LEVEL_COMB,TABLE_NAME,FILTER_CONDITION)
2664 (SELECT INDICATOR INDICATOR,PERIODICITY_ID PERIODICITY_ID,0 DIM_SET_ID,'?' LEVEL_COMB,
2665 NULL TABLE_NAME,NULL FILTER_CONDITION FROM BSC_KPI_PERIODICITIES
2666 WHERE INDICATOR = p_indicator);
2667 END IF;
2668
2669 END IF;
2670
2671 -- Change from Production to Prototype
2672 IF h_currentFlag = G_ActionFlag.Normal AND h_newflag = h_structure THEN
2673 h_tmp := 1;
2674 --- ======>ON HOLD msg_tmp = Get_FEM_MESSAGES("BSC_MUSERS_KPI_TO_PUBLISH")
2675 --- ======>ON HOLD msg_tmp.Text = ReplaceToken(msg_tmp.Text, "INDICATOR_NAME", Get_IndicatorName(indicator))
2676 --- ======>ON HOLD tmp = MsgBox(msg_tmp.Text, vbYesNo, gKpiDesignerTitle)
2677 --YES
2678 IF h_tmp = 1 THEN
2679 -- Call BscKpisB_Update(indicator, "PUBLISH_FLAG", 1)
2680 UPDATE BSC_KPIS_B SET PUBLISH_FLAG = 1
2681 WHERE INDICATOR = p_indicator;
2682 ELSE
2683 -- Call BscKpisB_Update(indicator, "PUBLISH_FLAG", 0)
2684 UPDATE BSC_KPIS_B SET PUBLISH_FLAG = 0
2685 WHERE INDICATOR = p_indicator;
2686 END IF;
2687 END IF;
2688 END IF;
2689
2690 IF p_cascade_color THEN
2691 IF p_newflag <> G_ActionFlag.Normal THEN
2692 --IF p_newflag <> 0 THEN
2693 -- Update KPI level prototype_flag to 7
2694 Update_Kpi_Prototype_Flag( p_objective_id => p_indicator
2695 , p_flag => C_COLOR_CHANGE
2696 );
2697 END IF;
2698 ELSE
2699 IF p_newflag <> G_ActionFlag.Normal AND p_newflag <> G_ActionFlag.Update_color THEN
2700 --IF p_newflag <> 0 AND p_newflag <> 7 THEN
2701 -- Update KPI level prototype_flag to 7
2702 Update_Kpi_Prototype_Flag( p_objective_id => p_indicator
2703 , p_flag => C_COLOR_CHANGE
2704 );
2705 END IF;
2706 END IF;
2707
2708 --DBMS_OUTPUT.PUT_LINE('p_indicator' || p_indicator);
2709 --DBMS_OUTPUT.PUT_LINE('h_stage' || h_stage);
2710 --DBMS_OUTPUT.PUT_LINE('h_structure' || h_structure);
2711 --DBMS_OUTPUT.PUT_LINE('h_currentFlag '|| h_currentFlag);
2712 --DBMS_OUTPUT.PUT_LINE('p_newflag '|| p_newflag);
2713 --DBMS_OUTPUT.PUT_LINE('h_newflag '|| h_newflag);
2714
2715 EXCEPTION
2716 WHEN OTHERS THEN
2717 --DBMS_OUTPUT.PUT_LINE('q' || SQLERRM);
2718 BSC_MESSAGE.Add(x_message => SQLERRM,
2719 x_source => 'ActionFlag_Change_Cascade');
2720 END ActionFlag_Change_Cascade;
2721
2722
2723 /*********************************************************************************
2724 INCREMENTAL CHANGES FOR DIMENSION OBJECTS
2725 *********************************************************************************/
2726
2727
2728 /*===========================================================================+
2729 | PROCEDURE
2730 | Dim_Object_Change
2731 |
2732 | PURPOSE
2733 | This procedure takes care of Incremental Changes, when the Dimension
2734 | object is modified
2735 | SEUDO CODE
2736 | -Search for all the KPIs that are using this Dimension Object as Default
2737 | -Change the Action Flag
2738 | PARAMETERS
2739 | x_dim_level_id
2740 | HISTORY
2741 | 15-MAY-2003 Aditya Rao Created
2742 +---------------------------------------------------------------------------*/
2743 PROCEDURE Dim_Object_Change(x_dim_level_id IN NUMBER) IS
2744
2745 CURSOR c_kpi_dim_level IS
2746
2747 SELECT distinct(INDICATOR)
2748 FROM BSC_KPI_DIM_LEVEL_PROPERTIES
2749 WHERE DIM_LEVEL_ID = x_dim_level_id;
2750
2751 h_kpi NUMBER;
2752
2753 BEGIN
2754 -- testing
2755 --DBMS_OUTPUT.PUT_LINE('x_dataset_id' || x_dataset_id);
2756
2757 --Search for all the Kpi that are affectec
2758 OPEN c_kpi_dim_level;
2759
2760 FETCH c_kpi_dim_level INTO h_kpi;
2761 WHILE c_kpi_dim_level%FOUND LOOP
2762 --Testing
2763 --DBMS_OUTPUT.PUT_LINE('-----------------------( '|| h_kpi || ' )------------------------');
2764
2765 --Change the Action Flag
2766
2767 ActionFlag_Change(h_kpi, G_ActionFlag.GAA_Structure);
2768
2769 FETCH c_kpi_dim_level INTO h_kpi;
2770 END LOOP;
2771 CLOSE c_kpi_dim_level;
2772
2773
2774 EXCEPTION
2775 WHEN OTHERS THEN
2776 --DBMS_OUTPUT.PUT_LINE('s' || SQLERRM);
2777 BSC_MESSAGE.Add(x_message => SQLERRM,
2778 x_source => 'Dim_Object_Change');
2779 END Dim_Object_Change;
2780
2781
2782
2783 /*********************************************************************************
2784 INCREMENTAL CHANGES FOR DIMENSIONS (GROUPS)
2785 *********************************************************************************/
2786
2787
2788 /*===========================================================================+
2789 | PROCEDURE
2790 | Dimension_Change
2791 |
2792 | PURPOSE
2793 | This procedure takes care of Incremental Changes, when the Dimension
2794 | (Group) is modified for the following conditions
2795 |
2796 | - Delete Dimension Group
2797 | - Add or Delete Dimension in a Dimension Group
2798 | - Edit Dimension Properties inside a Dimension Group
2799 |
2800 | PSEUDO CODE
2801 |
2802 | -Search for all the KPIs that are using this Dimension (Group)
2803 | -Change the Action Flag
2804 |
2805 | PARAMETERS
2806 | x_dim_level_id
2807 | HISTORY
2808 | 15-MAY-2003 Aditya Rao Created
2809 +---------------------------------------------------------------------------*/
2810 PROCEDURE Dimension_Change(x_dim_group_id IN NUMBER, x_flag IN NUMBER) IS
2811
2812 CURSOR c_kpi_dim_group_id IS
2813
2814 SELECT DISTINCT(INDICATOR)
2815 FROM BSC_KPI_DIM_GROUPS
2816 WHERE DIM_GROUP_ID = x_dim_group_id;
2817
2818 h_kpi NUMBER;
2819
2820 BEGIN
2821 -- testing
2822 --DBMS_OUTPUT.PUT_LINE('Inside dimension_change ');
2823
2824 --Search for all the Kpi that are affectec
2825 OPEN c_kpi_dim_group_id;
2826
2827 -- Fetch the Indicators that house the Group into h_kpi
2828 FETCH c_kpi_dim_group_id INTO h_kpi;
2829
2830 WHILE c_kpi_dim_group_id%FOUND LOOP
2831 --Testing
2832 --DBMS_OUTPUT.PUT_LINE('h_kpi' || h_kpi);
2833
2834 --Change the Action Flag for the KPI
2835 ActionFlag_Change(h_kpi, x_flag);
2836
2837
2838 FETCH c_kpi_dim_group_id INTO h_kpi;
2839 END LOOP;
2840 CLOSE c_kpi_dim_group_id;
2841
2842 EXCEPTION
2843 WHEN OTHERS THEN
2844 --DBMS_OUTPUT.PUT_LINE('s' || SQLERRM);
2845 BSC_MESSAGE.Add(x_message => SQLERRM,
2846 x_source => 'Dimension_Change');
2847 END Dimension_Change;
2848
2849
2850 /*===========================================================================+
2851 | PROCEDURE
2852 | Dimension_Change (takes a short_name)
2853 |
2854 | PURPOSE
2855 | This procedure takes care of Incremental Changes, when the Dimension
2856 | (Group) is modified for the following conditions
2857 |
2858 | - Delete Dimension Group
2859 | - Add or Delete Dimension in a Dimension Group
2860 | - Edit Dimension Properties inside a Dimension Group
2861 |
2862 | PSEUDO CODE
2863 |
2864 | -Search for all the KPIs that are using this Dimension (Group)
2865 | -Change the Action Flag
2866 |
2867 | PARAMETERS
2868 | x_grp_short_name
2869 | HISTORY
2870 | 15-MAY-2003 Aditya Rao Created
2871 +---------------------------------------------------------------------------*/
2872 PROCEDURE Dimension_Change(x_grp_short_name IN VARCHAR2, x_flag IN NUMBER) IS
2873
2874 CURSOR c_group_id IS
2875
2876 SELECT DIM_GROUP_ID
2877 FROM BSC_SYS_DIM_GROUPS_VL
2878 WHERE SHORT_NAME = x_grp_short_name;
2879
2880 h_grp_id NUMBER;
2881
2882 BEGIN
2883 -- testing
2884 --DBMS_OUTPUT.PUT_LINE('Inside dimension_change ');
2885
2886 --Search for all the Kpi that are affectec
2887 OPEN c_group_id;
2888
2889 -- Fetch the Indicators that house the Group into h_kpi
2890 FETCH c_group_id INTO h_grp_id;
2891
2892 -- --DBMS_OUTPUT.PUT_LINE('h_kpi' || h_kpi);
2893
2894 BSC_DESIGNER_PVT.Dimension_Change(h_grp_id, x_flag);
2895
2896 CLOSE c_group_id;
2897
2898 EXCEPTION
2899 WHEN OTHERS THEN
2900 --DBMS_OUTPUT.PUT_LINE('s' || SQLERRM);
2901 BSC_MESSAGE.Add(x_message => SQLERRM,
2902 x_source => 'Dimension_Change');
2903 END Dimension_Change;
2904
2905 /*-------------------------------------------------------
2906 This is wrapper for query fnd_profile.get from VB
2907 ---------------------------------------------------------*/
2908 FUNCTION FND_PROFILE_GET (name VARCHAR2)
2909 RETURN VARCHAR2 is
2910 val VARCHAR2(32767);
2911 BEGIN
2912 -- Now call the stored program
2913 fnd_profile.get(name,val);
2914 --DBMS_OUTPUT.PUT_LINE(SubStr('val = '||val,1,255));
2915 RETURN nvl(val, '');
2916 EXCEPTION
2917 WHEN OTHERS THEN
2918 RETURN '';
2919 END FND_PROFILE_GET;
2920
2921 /************************************************************************************
2922 -- API name : Copy_Objective_Recors
2923 -- Type : Private
2924 -- Function :
2925 -- This API is used to copy records of any table that satisfies the clause
2926 -- "p_column_name = p_Source_Value'
2927 -- It will copy all the records but replaces the p_Source_Value with
2928 -- p_Target_value
2929 -- Also for columns like creation_date and last_update_date the system date
2930 -- will be inserted.
2931 ************************************************************************************/
2932
2933 PROCEDURE Copy_Objective_Record (
2934 p_commit IN VARCHAR2 := FND_API.G_FALSE
2935 , p_DbLink_Name IN VARCHAR2
2936 , p_Table_Name IN VARCHAR2
2937 , p_Table_column IN VARCHAR2
2938 , p_Source_Value IN NUMBER
2939 , p_Target_Value IN NUMBER
2940 , x_return_status OUT NOCOPY VARCHAR2
2941 , x_msg_count OUT NOCOPY NUMBER
2942 , x_msg_data OUT NOCOPY VARCHAR2
2943 ) IS
2944
2945 CURSOR c_column IS
2946 SELECT
2947 column_name
2948 FROM
2949 all_tab_columns
2950 WHERE
2951 table_name = p_table_name
2952 AND
2953 owner = DECODE(USER,BSC_APPS.get_user_schema('APPS'),BSC_APPS.get_user_schema,USER)
2954 ORDER BY column_name;
2955
2956 l_colum VARCHAR2(100);
2957 l_key_name VARCHAR2(30);
2958 l_condition VARCHAR2(1000);
2959 l_sql VARCHAR2(32000);
2960 l_arr_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
2961 l_num_columns NUMBER;
2962 i NUMBER;
2963 BEGIN
2964 -- Initialize BSC/APPS global variables
2965 BSC_APPS.Init_Bsc_Apps;
2966 SAVEPOINT BscCopyObjRecordPub;
2967
2968 l_key_name := p_Table_column;
2969 IF( p_table_column = C_SOURCE_CODE )THEN
2970 l_key_name := 'SOURCE_CODE';
2971 END IF;
2972
2973 l_num_columns :=0;
2974 OPEN c_column;
2975 FETCH c_column INTO l_colum;
2976 WHILE c_column%FOUND LOOP
2977 l_num_columns := l_num_columns + 1;
2978 l_arr_columns(l_num_columns) := l_colum;
2979 FETCH c_column INTO l_colum;
2980 END LOOP;
2981 CLOSE c_column;
2982
2983
2984 IF l_num_columns > 0 THEN
2985 l_condition:= l_key_name || '=' || p_Source_Value;
2986 IF l_key_name = 'SOURCE_CODE' THEN
2987 l_condition:= l_condition || ' AND SOURCE_TYPE=2';
2988 END IF;
2989 l_sql:= 'INSERT INTO ( SELECT ';
2990 FOR i IN 1..l_num_columns LOOP
2991 IF i <> 1 THEN
2992 l_sql:= l_sql || ',';
2993 END IF;
2994 l_sql:= l_sql || l_arr_columns(i);
2995 END LOOP;
2996 l_sql:= l_sql || ' FROM ' || p_table_name;
2997 l_sql:= l_sql || ' )';
2998 l_sql:= l_sql || ' SELECT ';
2999 FOR i IN 1..l_num_columns LOOP
3000 IF i <> 1 THEN
3001 l_sql:= l_sql || ',';
3002 END IF;
3003 IF UPPER(l_arr_columns(i)) = l_key_name THEN
3004 l_sql:= l_sql || p_Target_Value || ' AS ' || l_arr_columns(i);
3005 ELSE
3006 l_sql:= l_sql || l_arr_columns(i) || ' AS ' || l_arr_columns(i);
3007 END IF;
3008 END LOOP;
3009 l_sql:= l_sql || ' FROM ' || Format_DbLink_String(p_table_name);
3010 l_sql:= l_sql || ' WHERE ' || l_condition;
3011
3012 EXECUTE IMMEDIATE l_sql;
3013 END IF;
3014
3015 IF (p_commit = FND_API.G_TRUE) THEN
3016 COMMIT;
3017 END IF;
3018
3019 EXCEPTION
3020 WHEN FND_API.G_EXC_ERROR THEN
3021 ROLLBACK TO BscCopyObjRecordPub;
3022 IF (x_msg_data IS NULL) THEN
3023 FND_MSG_PUB.Count_And_Get
3024 ( p_encoded => FND_API.G_FALSE
3025 , p_count => x_msg_count
3026 , p_data => x_msg_data
3027 );
3028 END IF;
3029 x_return_status := FND_API.G_RET_STS_ERROR;
3030 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3031 ROLLBACK TO BscCopyObjRecordPub;
3032 IF (x_msg_data IS NULL) THEN
3033 FND_MSG_PUB.Count_And_Get
3034 ( p_encoded => FND_API.G_FALSE
3035 , p_count => x_msg_count
3036 , p_data => x_msg_data
3037 );
3038 END IF;
3039 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3040 WHEN NO_DATA_FOUND THEN
3041 ROLLBACK TO BscCopyObjRecordPub;
3042 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3043 IF (x_msg_data IS NOT NULL) THEN
3044 x_msg_data := x_msg_data || p_table_name || ' ->BSC_DESIGNER_PVT.Copy_Objective_Record ';
3045 ELSE
3046 x_msg_data := SQLERRM || p_table_name || ' at BSC_DESIGNER_PVT.Copy_Objective_Record ';
3047 END IF;
3048 WHEN OTHERS THEN
3049 ROLLBACK TO BscCopyObjRecordPub;
3050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3051 IF (x_msg_data IS NOT NULL) THEN
3052 x_msg_data := x_msg_data ||p_table_name || ' -> BSC_DESIGNER_PVT.Copy_Objective_Record ';
3053 ELSE
3054 x_msg_data := SQLERRM || p_table_name || ' at BSC_DESIGNER_PVT.Copy_Objective_Record ';
3055 END IF;
3056 END Copy_Objective_Record;
3057
3058 /************************************************************************************
3059 -- API name : Item_Belong_To_Array_Varchar2
3060 -- Type : Private
3061 -- Function :
3062 ***********************************************************************************/
3063 FUNCTION Item_Belong_To_Array_Varchar2(
3064 x_item IN VARCHAR2,
3065 x_array IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3066 x_num_items IN NUMBER
3067 ) RETURN BOOLEAN IS
3068
3069 h_i NUMBER;
3070
3071 BEGIN
3072 FOR h_i IN 1 .. x_num_items LOOP
3073 IF UPPER(x_array(h_i)) = UPPER(x_item) THEN
3074 RETURN TRUE;
3075 END IF;
3076 END LOOP;
3077
3078 RETURN FALSE;
3079
3080 END Item_Belong_To_Array_Varchar2;
3081
3082 /************************************************************************************
3083 -- API name : Process_TL_Table
3084 -- Type : Public
3085 -- Function :
3086 -- This API checks whether the source and target language entries are in sync
3087 -- 1. Copies all the entries from source
3088 -- 2. Deletes off any entries that are not installed in target environment
3089 -- 3. Creates entries for languages that are installed only in target by
3090 -- copying from the base language entry
3091 ************************************************************************************/
3092
3093 PROCEDURE Process_TL_Table (
3094 p_commit IN VARCHAR2 := FND_API.G_FALSE
3095 , p_DbLink_Name IN VARCHAR2
3096 , p_Table_Name IN VARCHAR2
3097 , p_Table_column IN VARCHAR2
3098 , p_Target_Value IN NUMBER
3099 , p_Target_Value_Char IN VARCHAR2
3100 , x_return_status OUT NOCOPY VARCHAR2
3101 , x_msg_count OUT NOCOPY NUMBER
3102 , x_msg_data OUT NOCOPY VARCHAR2
3103 ) IS
3104
3105 TYPE t_cursor IS REF CURSOR;
3106 c_cursor t_cursor;
3107
3108 l_src_languages BSC_UPDATE_UTIL.t_array_of_varchar2;
3109 l_num_src_languages NUMBER := 0;
3110 l_trg_languages BSC_UPDATE_UTIL.t_array_of_varchar2;
3111 l_num_trg_languages NUMBER := 0;
3112
3113 l_src_base_language VARCHAR2(4);
3114 l_owner all_tab_columns.owner%TYPE;
3115
3116 CURSOR c_columns_apps IS
3117 SELECT
3118 column_name
3119 FROM
3120 all_tab_columns
3121 WHERE
3122 table_name = p_table_name
3123 AND
3124 owner = l_owner
3125 ORDER BY column_name;
3126
3127 l_lang_code VARCHAR2(4);
3128 l_installed_flag VARCHAR2(1);
3129 l_colum VARCHAR2(100);
3130 l_key_name VARCHAR2(30);
3131 l_condition VARCHAR2(1000);
3132 l_sql VARCHAR2(32000);
3133 l_insert VARCHAR2(32000);
3134 l_select VARCHAR2(32000);
3135 l_arr_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3136 l_num_columns NUMBER;
3137 i NUMBER;
3138 l_Count NUMBER := 0;
3139 BEGIN
3140 -- Initialize BSC/APPS global variables
3141 BSC_APPS.Init_Bsc_Apps;
3142 SAVEPOINT BscProcessTLTableInfo;
3143
3144 IF(INSTR(p_table_name,'BSC') = 1)THEN
3145 SELECT DECODE(USER,BSC_APPS.get_user_schema('APPS'),BSC_APPS.get_user_schema('BSC'),USER)
3146 INTO l_owner FROM DUAL;
3147 ELSIF(INSTR(p_table_name,'BIS') = 1)THEN
3148 SELECT DECODE(USER,BSC_APPS.get_user_schema('APPS'),BSC_APPS.get_user_schema('BIS'),USER)
3149 INTO l_owner FROM DUAL;
3150 ELSIF(INSTR(p_table_name,'FND') = 1) THEN
3151 SELECT DECODE(USER,BSC_APPS.get_user_schema('APPS'),BSC_APPS.get_user_schema('FND'),USER)
3152 INTO l_owner FROM DUAL;
3153 ELSE
3154 SELECT DECODE(USER,BSC_APPS.get_user_schema('APPS'),BSC_APPS.get_user_schema('AK'),USER)
3155 INTO l_owner FROM DUAL;
3156 END IF;
3157
3158 -- Get supported languages in source system
3159 l_sql := 'SELECT DISTINCT language_code, installed_flag'||
3160 ' FROM fnd_languages';
3161 IF p_DbLink_Name IS NOT NULL THEN
3162 l_sql := l_sql || '@'||p_DbLink_Name;
3163 END IF;
3164 l_sql := l_sql ||' WHERE installed_flag IN (:1, :2)';
3165 OPEN c_cursor FOR l_sql USING 'B', 'I';
3166 l_num_src_languages := 0;
3167 LOOP
3168 FETCH c_cursor INTO l_lang_code, l_installed_flag;
3169 EXIT WHEN c_cursor%NOTFOUND;
3170
3171 l_num_src_languages := l_num_src_languages + 1;
3172 l_src_languages(l_num_src_languages) := l_lang_code;
3173
3174 IF l_installed_flag = 'B' THEN
3175 l_src_base_language := l_lang_code;
3176 END IF;
3177 END LOOP;
3178 CLOSE c_cursor;
3179
3180 -- Get supported languages in target system
3181 l_sql := 'SELECT DISTINCT language_code'||
3182 ' FROM fnd_languages'||
3183 ' WHERE installed_flag IN (:1, :2)';
3184 OPEN c_cursor FOR l_sql USING 'B', 'I';
3185 l_num_trg_languages := 0;
3186 LOOP
3187 FETCH c_cursor INTO l_lang_code;
3188 EXIT WHEN c_cursor%NOTFOUND;
3189
3190 l_num_trg_languages := l_num_trg_languages + 1;
3191 l_trg_languages(l_num_trg_languages) := l_lang_code;
3192 END LOOP;
3193 CLOSE c_cursor;
3194
3195 l_key_name := p_Table_column;
3196 IF( p_table_column = C_SOURCE_CODE )THEN
3197 l_key_name := 'SOURCE_CODE';
3198 END IF;
3199 IF p_Target_Value_Char IS NOT NULL THEN
3200 l_condition:= l_key_name || '=''' || p_Target_Value_Char || '''';
3201 ELSE
3202 l_condition:= l_key_name || '=' || p_Target_Value;
3203 END IF;
3204 IF l_key_name = 'SOURCE_CODE' THEN
3205 l_condition:= l_condition || ' AND SOURCE_TYPE = 2';
3206 END IF;
3207 FOR i IN 1..l_num_trg_languages LOOP
3208 l_sql := 'SELECT COUNT(1) FROM ' || p_Table_Name || ' WHERE '|| l_condition ;
3209 l_sql := l_sql || ' AND language = :1';
3210 l_Count := 0;
3211 OPEN c_cursor FOR l_sql USING l_trg_languages(i);
3212 FETCH c_cursor INTO l_Count;
3213 CLOSE c_cursor;
3214
3215 IF l_Count = 0 AND NOT Item_Belong_To_Array_Varchar2(l_trg_languages(i),
3216 l_src_languages,
3217 l_num_src_languages) THEN
3218 l_insert := NULL;
3219 l_select := NULL;
3220 OPEN c_columns_apps;
3221 FETCH c_columns_apps INTO l_colum;
3222 WHILE c_columns_apps%FOUND LOOP
3223 IF l_insert IS NOT NULL THEN
3224 l_insert := l_insert||', ';
3225 l_select := l_select||', ';
3226 END IF;
3227
3228 l_insert := l_insert||l_colum;
3229
3230 IF UPPER(l_colum) = 'LANGUAGE' THEN
3231 l_select := l_select||''''||l_trg_languages(i)||'''';
3232 ELSE
3233 l_select := l_select||l_colum;
3234 END IF;
3235
3236 FETCH c_columns_apps INTO l_colum;
3237 END LOOP;
3238 CLOSE c_columns_apps;
3239
3240 l_sql := 'INSERT INTO '||p_Table_Name||' ('||l_insert||')'||
3241 ' SELECT '||l_select||
3242 ' FROM '||p_Table_Name||
3243 ' WHERE LANGUAGE = :1 AND ' || l_condition;
3244 EXECUTE IMMEDIATE l_sql USING l_src_base_language;
3245 END IF;
3246 END LOOP;
3247
3248 FOR i IN 1..l_num_src_languages LOOP
3249 IF NOT Item_Belong_To_Array_Varchar2(l_src_languages(i),
3250 l_trg_languages,
3251 l_num_trg_languages) THEN
3252 l_sql := 'DELETE FROM '||p_Table_Name||
3253 ' WHERE LANGUAGE = :1 AND ' || l_condition;
3254 EXECUTE IMMEDIATE l_sql USING l_src_languages(i);
3255 END IF;
3256 END LOOP;
3257
3258
3259 IF (p_commit = FND_API.G_TRUE) THEN
3260 COMMIT;
3261 END IF;
3262
3263 EXCEPTION
3264 WHEN FND_API.G_EXC_ERROR THEN
3265 ROLLBACK TO BscProcessTLTableInfo;
3266 IF (x_msg_data IS NULL) THEN
3267 FND_MSG_PUB.Count_And_Get
3268 ( p_encoded => FND_API.G_FALSE
3269 , p_count => x_msg_count
3270 , p_data => x_msg_data
3271 );
3272 END IF;
3273 x_return_status := FND_API.G_RET_STS_ERROR;
3274 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3275 ROLLBACK TO BscProcessTLTableInfo;
3276 IF (x_msg_data IS NULL) THEN
3277 FND_MSG_PUB.Count_And_Get
3278 ( p_encoded => FND_API.G_FALSE
3279 , p_count => x_msg_count
3280 , p_data => x_msg_data
3281 );
3282 END IF;
3283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3284 WHEN NO_DATA_FOUND THEN
3285 ROLLBACK TO BscProcessTLTableInfo;
3286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3287 IF (x_msg_data IS NOT NULL) THEN
3288 x_msg_data := x_msg_data || p_table_name || ' ->BSC_DESIGNER_PVT.Process_TL_Table ';
3289 ELSE
3290 x_msg_data := SQLERRM || p_table_name || ' at BSC_DESIGNER_PVT.Process_TL_Table ';
3291 END IF;
3292 WHEN OTHERS THEN
3293 ROLLBACK TO BscProcessTLTableInfo;
3294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3295 IF (x_msg_data IS NOT NULL) THEN
3296 x_msg_data := x_msg_data ||p_table_name || ' -> BSC_DESIGNER_PVT.Process_TL_Table ';
3297 ELSE
3298 x_msg_data := SQLERRM || p_table_name || ' at BSC_DESIGNER_PVT.Process_TL_Table ';
3299 END IF;
3300 END Process_TL_Table;
3301
3302
3303 /************************************************************************************
3304 -- API name : Copy_Kpi_Metadata
3305 -- Type : Public
3306 -- Function :
3307 -- This API is used to copy entire bsc side metadata of an indicator via dblink
3308 -- or within the system
3309 ************************************************************************************/
3310 PROCEDURE Copy_Kpi_Metadata (
3311 p_commit IN VARCHAR2 := FND_API.G_FALSE
3312 , p_DbLink_Name IN VARCHAR2
3313 , p_Source_Indicator IN NUMBER
3314 , x_Target_Indicator OUT NOCOPY NUMBER
3315 , x_return_status OUT NOCOPY VARCHAR2
3316 , x_msg_count OUT NOCOPY NUMBER
3317 , x_msg_data OUT NOCOPY VARCHAR2
3318 ) IS
3319 l_Copy_Type NUMBER := 5;
3320 l_Where_Clause VARCHAR2(2000);
3321 l_Record_Count NUMBER;
3322 l_Target_Value NUMBER;
3323 l_Sql VARCHAR2(2000);
3324 TYPE t_cursor IS REF CURSOR;
3325 l_cursor t_cursor;
3326 BEGIN
3327
3328 FND_MSG_PUB.Initialize;
3329 x_return_status := FND_API.G_RET_STS_SUCCESS;
3330
3331 SAVEPOINT BscCopyKpiMetadataPub;
3332
3333 BSC_APPS.Init_Bsc_Apps;
3334 Init_Kpi_Metadata_Tables_Array();
3335
3336 l_sql := BSC_DESIGNER_PVT.Format_DbLink_String('SELECT DECODE(share_flag, 2, 3, 5) FROM bsc_kpis_vl');
3337 l_sql := l_sql || 'WHERE indicator = :1';
3338 OPEN l_cursor FOR l_sql USING p_Source_Indicator;
3339 FETCH l_cursor INTO l_Copy_Type;
3340 CLOSE l_cursor;
3341
3342 SELECT
3343 BSC_INDICATOR_ID_S.NEXTVAL
3344 INTO
3345 l_Target_Value
3346 FROM DUAL;
3347
3348 IF l_Target_Value IS NOT NULL THEN
3349 FOR i IN 1..g_num_kpi_metadata_tables LOOP
3350 IF g_kpi_metadata_tables(i).copy_type >= l_Copy_Type THEN
3351 Copy_Objective_Record (
3352 p_commit => FND_API.G_FALSE
3353 ,p_DbLink_Name => p_DbLink_Name
3354 ,p_Table_Name => g_kpi_metadata_tables(i).table_name
3355 ,p_Table_column => g_kpi_metadata_tables(i).table_column
3356 ,p_Source_Value => p_Source_Indicator
3357 ,p_Target_Value => l_Target_Value
3358 ,x_return_status => x_return_status
3359 ,x_msg_count => x_msg_count
3360 ,x_msg_data => x_msg_data
3361 );
3362 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3364 END IF;
3365
3366 IF g_kpi_metadata_tables(i).mls_table IS NOT NULL AND
3367 g_kpi_metadata_tables(i).mls_table = bsc_utility.YES THEN
3368 Process_TL_Table(
3369 p_commit => FND_API.G_FALSE
3370 ,p_DbLink_Name => p_DbLink_Name
3371 ,p_Table_Name => g_kpi_metadata_tables(i).table_name
3372 ,p_Table_column => g_kpi_metadata_tables(i).table_column
3373 ,p_Target_Value => l_Target_Value
3374 ,p_Target_Value_Char => NULL
3375 ,x_return_status => x_return_status
3376 ,x_msg_count => x_msg_count
3377 ,x_msg_data => x_msg_data
3378 );
3379 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3381 END IF;
3382 END IF;
3383 END IF;
3384 END LOOP;
3385
3386 Copy_Records_by_Obj_Kpi_Meas(
3387 p_src_kpi => p_Source_Indicator
3388 ,p_trg_kpi => l_Target_Value
3389 );
3390 x_Target_Indicator := l_Target_Value;
3391 END IF;
3392
3393
3394 IF (p_commit = FND_API.G_TRUE) THEN
3395 COMMIT;
3396 END IF;
3397
3398 EXCEPTION
3399 WHEN FND_API.G_EXC_ERROR THEN
3400 ROLLBACK TO BscCopyKpiMetadataPub;
3401 IF (x_msg_data IS NULL) THEN
3402 FND_MSG_PUB.Count_And_Get
3403 ( p_encoded => FND_API.G_FALSE
3404 , p_count => x_msg_count
3405 , p_data => x_msg_data
3406 );
3407 END IF;
3408 x_return_status := FND_API.G_RET_STS_ERROR;
3409 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3410 ROLLBACK TO BscCopyKpiMetadataPub;
3411 IF (x_msg_data IS NULL) THEN
3412 FND_MSG_PUB.Count_And_Get
3413 ( p_encoded => FND_API.G_FALSE
3414 , p_count => x_msg_count
3415 , p_data => x_msg_data
3416 );
3417 END IF;
3418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3419 WHEN NO_DATA_FOUND THEN
3420 ROLLBACK TO BscCopyKpiMetadataPub;
3421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3422 IF (x_msg_data IS NOT NULL) THEN
3423 x_msg_data := x_msg_data || ' ->BSC_DESIGNER_PVT.Copy_Kpi_Metadata ';
3424 ELSE
3425 x_msg_data := SQLERRM || 'BSC_DESIGNER_PVT.Copy_Kpi_Metadata ';
3426 END IF;
3427 WHEN OTHERS THEN
3428 ROLLBACK TO BscCopyKpiMetadataPub;
3429 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3430 IF (x_msg_data IS NOT NULL) THEN
3431 x_msg_data := x_msg_data || ' ->BSC_DESIGNER_PVT.Copy_Kpi_Metadata ';
3432 ELSE
3433 x_msg_data := SQLERRM || 'BSC_DESIGNER_PVT.Copy_Kpi_Metadata ';
3434 END IF;
3435 END Copy_Kpi_Metadata;
3436
3437 /************************************************************************************
3438 -- API name : Format_DbLink_String
3439 -- Type : Public
3440 -- Function :
3441 ************************************************************************************/
3442 FUNCTION Format_DbLink_String (
3443 p_Sql IN VARCHAR2
3444 ) RETURN VARCHAR2 IS
3445
3446 BEGIN
3447 IF g_DbLink_Name IS NOT NULL THEN
3448 RETURN p_Sql || '@'|| g_DbLink_Name || ' ';
3449 END IF;
3450 RETURN p_Sql || ' ';
3451 EXCEPTION
3452 WHEN OTHERS THEN
3453 RETURN p_Sql|| ' ';
3454 END Format_DbLink_String;
3455
3456
3457 END BSC_DESIGNER_PVT;