1 PACKAGE BODY BIS_PMF_MIGRATION_PVT AS
2 /* $Header: BISVMIGB.pls 120.2 2005/12/15 03:23:41 ankgoel noship $ */
3 --
4 /*
5 REM +=======================================================================+
6 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
7 REM | All rights reserved. |
8 REM +=======================================================================+
9 REM | FILENAME |
10 REM | BISPMIGS.pls |
11 REM | |
12 REM | DESCRIPTION |
13 REM | Package Body for Migration of PMF data .
14 REM | Please refer to the datamodel for the changes.
15 REM | |
16 REM | NOTES |
17 REM | |
18 REM | HISTORY |
19 REM | 21-July-2000 amkulkar Creation |
20 REM | 19-apr-01 rmohanty added fix for 1743506
21 REM | 21-Jun-2001 meastmon fix migration for bis_user_ind_selections |
22 REM | 11-FEB-2003 rchandra added a check to see if the target level going |
23 REM | to be migrated already exists in which case it |
24 REM | will not be migrated , in NEEDS_MIGRATION api |
25 REM | for bug 2790164 |
26 REM | Also the migration script will only process rows|
27 REM | from old data model. Cursor definition |
28 REM | c_targetlvls in MIGRATE_PERFORMANCE_MEASURES API|
29 REM | accordingly changed |
30 REM | 03-JUN-2004 ankgoel Modified for bug#3583357. Added procedures for |
31 REM | re-sequencing dimensions in |
32 REM | bis_indicator_dimensions using the dim level |
33 REM | order in bis_target_levels. |
34 REM | 21-MAR-2005 ankagarw bug#4235732 - changing count(*) to count(1) |
35 REM | 19-OCT-2005 ppandey Enh 4618419- SQL Literal Fix |
36 REM | 15-DEC-2005 ankgoel Bug 4879417- Execute dynamic SQL with a NULL check |
37 REM +=======================================================================+
38 */
39 --
40 --
41 FUNCTION GET_DIMENSION_ID
42 (p_dimension_level_id IN NUMBER
43 )
44 RETURN NUMBER
45 IS
46 CURSOR c_dim IS
47 SELECT dimension_id
48 FROM bis_levels
49 WHERE level_id = p_dimension_level_id;
50 l_dim_id NUMBER;
51 BEGIN
52 OPEN c_dim;
53 FETCH c_dim INTO l_dim_id;
54 CLOSE c_dim;
55 RETURN l_dim_id;
56 EXCEPTION
57 WHEN OTHERS THEN
58 null;
59 END GET_DIMENSION_ID;
60 --
61
62 FUNCTION need_resequence (
63 p_indicator_id IN NUMBER
64 ,p_dim_level_id IN NUMBER
65 ,p_sequence_no IN NUMBER
66 )
67 RETURN BOOLEAN
68 IS
69 CURSOR c_dim_dim_levels(p_dim_level_id NUMBER, p_indicator_id NUMBER) IS
70 SELECT sequence_no
71 FROM bis_indicator_dimensions BIS_IND,
72 bis_levels BIS_LVL
73 WHERE BIS_IND.dimension_id = BIS_LVL.dimension_id
74 AND BIS_IND.indicator_id = p_indicator_id
75 AND BIS_LVL.level_id = p_dim_level_id;
76
77 l_reseq_flag BOOLEAN := FALSE;
78 l_dim_level_sequence NUMBER;
79 l_ind_dim_sequence NUMBER;
80 BEGIN
81 IF c_dim_dim_levels%ISOPEN THEN
82 CLOSE c_dim_dim_levels;
83 END IF;
84 l_dim_level_sequence := p_sequence_no;
85 OPEN c_dim_dim_levels(p_dim_level_id, p_indicator_id);
86 FETCH c_dim_dim_levels INTO l_ind_dim_sequence;
87 IF ((c_dim_dim_levels%FOUND) AND (l_dim_level_sequence <> l_ind_dim_sequence)) THEN
88 l_reseq_flag := TRUE;
89 END IF;
90 CLOSE c_dim_dim_levels;
91
92 RETURN l_reseq_flag;
93
94 EXCEPTION
95 WHEN OTHERS THEN
96 IF c_dim_dim_levels%ISOPEN THEN
97 CLOSE c_dim_dim_levels;
98 END IF;
99 RETURN TRUE;
100 END need_resequence;
101 --
102
103 PROCEDURE update_bis_indicators (
104 p_indicator_id IN NUMBER
105 ,p_dim_level_id IN NUMBER
106 ,p_sequence_no IN NUMBER
107 )
108 IS
109 BEGIN
110 IF(p_dim_level_id IS NOT NULL) THEN
111 UPDATE bis_indicator_dimensions
112 SET sequence_no = p_sequence_no
113 ,last_update_date = SYSDATE
114 WHERE indicator_id = p_indicator_id
115 AND dimension_id = get_dimension_id(p_dim_level_id);
116 END IF;
117 EXCEPTION
118 WHEN OTHERS THEN
119 ROLLBACK;
120 END update_bis_indicators;
121
122 PROCEDURE resequence_ind_dimensions (
123 p_indicator_id IN NUMBER
124 )
125 IS
126 /* It is assumed that all target levels for a measure are consistent with each other.
127 Only the first set of target level data is taken up.
128 */
129 CURSOR c_target_level(p_ind_id NUMBER) IS
130 SELECT dimension1_level_id, dimension2_level_id,
131 dimension3_level_id, dimension4_level_id,
132 dimension5_level_id, dimension6_level_id,
133 dimension7_level_id
134 FROM bis_target_levels
135 WHERE indicator_id = p_ind_id
136 AND org_level_id IS NOT NULL
137 AND time_level_id IS NOT NULL
138 AND rownum < 2;
139
140 l_flag_resequence BOOLEAN := FALSE;
141 l_target_level_rec BIS_PMF_MIGRATION_PVT.target_level_rec;
142 c_tl_rec c_target_level%ROWTYPE;
143
144 BEGIN
145
146 IF c_target_level%ISOPEN THEN
147 CLOSE c_target_level;
148 END IF;
149
150 OPEN c_target_level(p_indicator_id);
151 FETCH c_target_level INTO c_tl_rec;
152 IF c_target_level%FOUND THEN
153
154 IF (c_tl_rec.dimension1_level_id IS NOT NULL) THEN
155 l_flag_resequence := need_resequence(p_indicator_id, c_tl_rec.dimension1_level_id, 1);
156 END IF;
157 IF ((c_tl_rec.dimension2_level_id IS NOT NULL) AND (l_flag_resequence = FALSE)) THEN
158 l_flag_resequence := need_resequence(p_indicator_id, c_tl_rec.dimension2_level_id, 2);
159 END IF;
160 IF ((c_tl_rec.dimension3_level_id IS NOT NULL) AND (l_flag_resequence = FALSE)) THEN
161 l_flag_resequence := need_resequence(p_indicator_id, c_tl_rec.dimension3_level_id, 3);
162 END IF;
163 IF ((c_tl_rec.dimension4_level_id IS NOT NULL) AND (l_flag_resequence = FALSE)) THEN
164 l_flag_resequence := need_resequence(p_indicator_id, c_tl_rec.dimension4_level_id, 4);
165 END IF;
166 IF ((c_tl_rec.dimension5_level_id IS NOT NULL) AND (l_flag_resequence = FALSE)) THEN
167 l_flag_resequence := need_resequence(p_indicator_id, c_tl_rec.dimension5_level_id, 5);
168 END IF;
169 IF ((c_tl_rec.dimension6_level_id IS NOT NULL) AND (l_flag_resequence = FALSE)) THEN
170 l_flag_resequence := need_resequence(p_indicator_id, c_tl_rec.dimension6_level_id, 6);
171 END IF;
172 IF ((c_tl_rec.dimension7_level_id IS NOT NULL) AND (l_flag_resequence = FALSE)) THEN
173 l_flag_resequence := need_resequence(p_indicator_id, c_tl_rec.dimension7_level_id, 7);
174 END IF;
175
176 IF (l_flag_resequence = TRUE) THEN
177
178 update_bis_indicators(p_indicator_id, c_tl_rec.dimension1_level_id, 1);
179 update_bis_indicators(p_indicator_id, c_tl_rec.dimension2_level_id, 2);
180 update_bis_indicators(p_indicator_id, c_tl_rec.dimension3_level_id, 3);
181 update_bis_indicators(p_indicator_id, c_tl_rec.dimension4_level_id, 4);
182 update_bis_indicators(p_indicator_id, c_tl_rec.dimension5_level_id, 5);
183 update_bis_indicators(p_indicator_id, c_tl_rec.dimension6_level_id, 6);
184 update_bis_indicators(p_indicator_id, c_tl_rec.dimension7_level_id, 7);
185
186 END IF;
187 END IF;
188 CLOSE c_target_level;
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 IF c_target_level%ISOPEN THEN
193 CLOSE c_target_level;
194 END IF;
195 ROLLBACK;
196 END resequence_ind_dimensions;
197 --
198
199
200 PROCEDURE migrate_indicator_dimensions(
201 x_return_status OUT NOCOPY VARCHAR2
202 ,x_return_code OUT NOCOPY NUMBER
203 )
204 IS
205 /* Cursor picks up only the indicator_id which have ORG and TIME level id as NOT NULL.
206 This is assumed for old measures.
207 */
208 CURSOR c_ind_id IS
209 SELECT indicator_id
210 FROM bis_target_levels
211 WHERE org_level_id IS NOT NULL
212 AND time_level_id IS NOT NULL
213 GROUP BY indicator_id;
214
215 BEGIN
216 FOR c_ind_rec IN c_ind_id LOOP
217 resequence_ind_dimensions(c_ind_rec.indicator_id);
218 END LOOP;
219
220 x_return_Status := 'Success';
221 x_return_code := 0;
222 EXCEPTION
223 WHEN OTHERS THEN
224 ROLLBACK;
225 x_return_Status := SQLERRM;
226 x_return_code := SQLCODE;
227 END migrate_indicator_dimensions;
228 --
229
230 PROCEDURE RESEQUENCE_DIMENSION_LEVELS(
231 p_target_level_rec IN BIS_PMF_MIGRATION_PVT.target_level_rec
232 ,x_resequenced_dimensions OUT NOCOPY BIS_PMF_MIGRATION_PVT.resequenced_dimensions_array
233 ,x_dim_count OUT NOCOPY NUMBER
234 ,x_return_status OUT NOCOPY NUMBER
235 )
236 IS
237 CURSOR c_dimcount(p_measure_id IN NUMBER) IS
238 SELECT count(1) FROM
239 bis_indicator_dimensions
240 WHERE indicator_id = p_measure_id;
241 l_reseuenced_dimensions_array BIS_PMF_MIGRATION_PVT.resequenced_dimensions_array;
242 l_dimcount NUMBER;
243 BEGIN
244 OPEN c_dimcount(p_target_level_rec.measure_id);
245 FETCH c_dimcount INTO l_dimcount;
246 IF (c_dimcount%NOTFOUND) THEN
247 x_return_status := -1;
248 END IF;
249 CLOSE c_dimcount;
250 x_dim_count := l_dimcount;
251 -- Since the org/time are always the first two subtract two from the count
252 --If somebody for some reason runs the migration script again , do not subtract two
253 IF (p_target_level_rec.org_levelid IS NOT NULL and p_target_level_rec.time_levelid IS NOT NULL) THEN
254 l_dimcount := l_dimcount-2;
255 END IF;
256 IF (l_dimcount > 0) THEN
257 FOR l_count IN 1..l_dimcount LOOP
258 x_resequenced_dimensions(l_count).dim_level_col := 'dimension'||l_count||'_level_id';
259 IF (l_count = 1) THEN
260 x_resequenced_dimensions(1).dim_level_col_val := p_target_level_rec.dimension1_levelid;
261 x_resequenced_dimensions(1).dim_id := bis_pmf_migration_pvt.get_dimension_id (
262 p_target_level_rec.dimension1_levelid);
263 x_Resequenced_dimensions(1).seq_no := l_count;
264 END IF;
265 IF (l_count=2) THEN
266 x_resequenced_dimensions(2).dim_level_col_val := p_target_level_rec.dimension2_levelid;
267 x_resequenced_dimensions(2).dim_id := bis_pmf_migration_pvt.get_dimension_id (
268 p_target_level_rec.dimension2_levelid);
269 x_Resequenced_dimensions(2).seq_no := l_count;
270 END IF;
271 IF (l_count=3) THEN
272 x_resequenced_dimensions(3).dim_level_col_val := p_target_level_rec.dimension3_levelid;
273 x_resequenced_dimensions(3).dim_id := bis_pmf_migration_pvt.get_dimension_id (
274 p_target_level_rec.dimension3_levelid);
275 x_Resequenced_dimensions(3).seq_no := l_count;
276 END IF;
277 IF (l_count=4) THEN
278 x_resequenced_dimensions(4).dim_level_col_val := p_target_level_rec.dimension4_levelid;
279 x_resequenced_dimensions(4).dim_id := bis_pmf_migration_pvt.get_dimension_id (
280 p_target_level_rec.dimension4_levelid);
281 x_Resequenced_dimensions(4).seq_no := l_count;
282 END IF;
283 IF (l_count=5) THEN
284 x_resequenced_dimensions(5).dim_level_col_val := p_target_level_rec.dimension5_levelid;
285 x_resequenced_dimensions(5).dim_id := bis_pmf_migration_pvt.get_dimension_id (
286 p_target_level_rec.dimension5_levelid);
287 x_Resequenced_dimensions(5).seq_no := l_count;
288 END IF;
289 END LOOP;
290 END IF;
291 l_dimcount := l_dimcount + 1;
292 IF (l_dimcount <= 7) THEN
293 x_resequenced_dimensions(l_dimcount).dim_level_col := 'dimension'||l_dimcount||'_level_id';
294 x_resequenced_dimensions(l_dimcount).dim_level_col_val := p_target_level_rec.org_levelid;
295 x_resequenced_dimensions(l_dimcount).dim_id := bis_pmf_migration_pvt.get_dimension_id (
296 p_target_level_rec.org_levelid);
297 x_Resequenced_dimensions(l_dimcount).seq_no := l_dimcount;
298 l_dimcount := l_dimcount + 1;
299 x_resequenced_dimensions(l_dimcount).dim_level_col := 'dimension'||l_dimcount||'_level_id';
300 x_resequenced_dimensions(l_dimcount).dim_level_col_val := p_target_level_rec.time_levelid;
301 x_resequenced_dimensions(l_dimcount).dim_id := bis_pmf_migration_pvt.get_dimension_id (
302 p_target_level_rec.time_levelid);
303 x_Resequenced_dimensions(l_dimcount).seq_no := l_dimcount;
304 l_dimcount := l_dimcount +1;
305 FOR l_count IN l_dimcount..7 LOOP
306 x_resequenced_dimensions(l_dimcount).dim_level_col := 'dimension'||l_dimcount||'_level_id';
307 x_resequenced_dimensions(l_dimcount).dim_level_col_val := NULL;
308 l_dimcount := l_dimcount+1;
309 END LOOP;
310 END IF;
311 x_return_status := 0;
312 EXCEPTION
313 WHEN OTHERS THEN
314 x_return_status := -1;
315 END RESEQUENCE_DIMENSION_LEVELS;
316 --
317 PROCEDURE RESEQUENCE_DIM_LEVEL_VALUES(
318 p_dimvalues_rec IN BIS_PMF_MIGRATION_PVT.dim_values_rec
319 ,p_dim_count IN NUMBER
320 ,x_reseq_dim_values OUT NOCOPY BIS_PMF_MIGRATION_PVT.reseq_dim_values_arr
321 ,x_return_Status OUT NOCOPY NUMBER
322 )
323 IS
324 l_dim_levelcount NUMBER ;
325 BEGIN
326 l_dim_levelcount := p_dim_count;
327 IF(p_dimvalues_rec.org_level_value IS NOT NULL AND p_dimvalues_rec.time_level_value IS NOT NULL) THEN
328 l_dim_levelcount := l_dim_levelcount - 2;
329 END IF;
330 IF (l_dim_levelcount > 0) THEN
331 FOR l_count IN 1..l_dim_levelcount LOOP
332 x_reseq_dim_values(l_count).dim_level_name := 'dimension'||l_count||'_level_value';
333 IF (l_count = 1) THEN
334 IF (p_dimvalues_rec.dimension1_level_value IS NOT NULL) THEN
335 x_reseq_dim_values(1).dim_level_value :=
336 ''''||p_dimvalues_rec.dimension1_level_value||'''';
337 ELSE
338 x_reseq_dim_values(1).dim_level_value := p_dimvalues_rec.dimension1_level_Value;
339 END IF;
340 END IF;
341 IF (l_count = 2) THEN
342 IF (p_dimvalues_rec.dimension2_level_value IS NOT NULL) THEN
343 x_reseq_dim_values(2).dim_level_value :=
344 ''''||p_dimvalues_rec.dimension2_level_value||'''';
345 ELSE
346 x_reseq_dim_values(2).dim_level_value:= p_dimvalues_rec.dimension2_level_Value;
347 END IF;
348 END IF;
349 IF (l_count = 3) THEN
353 ELSE
350 IF (p_dimvalues_rec.dimension3_level_value IS NOT NULL) THEN
351 x_reseq_dim_values(3).dim_level_value:=
352 ''''||p_dimvalues_rec.dimension3_level_value||'''';
354 x_reseq_dim_values(3).dim_level_value := p_dimvalues_rec.dimension3_level_Value;
355 END IF;
356 END IF;
357 IF (l_count = 4) THEN
358 IF (p_dimvalues_rec.dimension4_level_value IS NOT NULL) THEN
359 x_reseq_dim_values(4).dim_level_value :=
360 ''''||p_dimvalues_rec.dimension4_level_value||'''';
361 ELSE
362 x_reseq_dim_values(4).dim_level_value := p_dimvalues_rec.dimension4_level_Value;
363 END IF;
364 END IF;
365 IF (l_count = 5) THEN
366 IF (p_dimvalues_rec.dimension5_level_value IS NOT NULL) THEN
367 x_reseq_dim_values(5).dim_level_value :=
368 ''''||p_dimvalues_rec.dimension5_level_value||'''';
369 ELSE
370 x_reseq_dim_values(5).dim_level_value := p_dimvalues_rec.dimension5_level_Value;
371 END IF;
372 END IF;
373 END LOOP;
374 END IF;
375 l_dim_levelcount := l_dim_levelcount + 1;
376 IF (l_dim_levelcount <= 7) THEN
377 x_reseq_dim_values(l_dim_levelcount).dim_level_name :=
378 'dimension'||l_dim_levelcount||'_level_value';
379 IF (p_dimvalues_rec.org_level_value IS NOT NULL) THEN
380 x_reseq_dim_values(l_dim_levelcount).dim_level_value :=
381 ''''||p_dimvalues_rec.org_level_value||'''';
382 ELSE
383 x_reseq_dim_values(l_dim_levelcount).dim_level_value := p_dimvalues_rec.org_level_value;
384 END IF;
385 l_dim_levelcount := l_dim_levelcount + 1;
386 x_reseq_dim_values(l_dim_levelcount).dim_level_name :=
387 'dimension'||l_dim_levelcount||'_level_value';
388 IF (p_dimvalues_rec.time_level_value IS NOT NULL) THEN
389 x_reseq_dim_values(l_dim_levelcount).dim_level_value :=
390 ''''||p_dimvalues_rec.time_level_value||'''';
391 ELSE
392 x_reseq_dim_values(l_dim_levelcount).dim_level_value := p_dimvalues_rec.time_level_value;
393 END IF;
394 l_dim_levelcount := l_dim_levelcount +1;
395 FOR l_count IN l_dim_levelcount..7 LOOP
396 x_reseq_dim_values(l_dim_levelcount).dim_level_name :=
397 'dimension'||l_dim_levelcount||'_level_value';
398 x_reseq_dim_values(l_Dim_levelcount).dim_level_value := NULL;
399 l_dim_levelcount := l_dim_levelcount+1;
400 END LOOP;
401 END IF;
402 x_return_status := 0;
403 EXCEPTION
404 WHEN OTHERS THEN
405 x_return_status := -1;
406 END;
407 --
408 PROCEDURE RESEQUENCE_IND_LEVEL_VALUES(
409 p_dimvalues_rec IN BIS_PMF_MIGRATION_PVT.dim_values_Rec
410 ,p_dim_count IN NUMBER
411 ,x_reseq_dim_values OUT NOCOPY BIS_PMF_MIGRATION_PVT.reseq_dim_values_arr
412 ,x_return_status OUT NOCOPY NUMBER
413 )
414 IS
415 l_dim_levelcount NUMBER ;
416 BEGIN
417 l_dim_levelcount := p_dim_count;
418 IF(p_dimvalues_rec.org_level_value IS NOT NULL ) THEN
419 l_dim_levelcount := l_dim_levelcount - 1;
420 END IF;
421 IF (l_dim_levelcount > 0) THEN
422 FOR l_count IN 1..l_dim_levelcount LOOP
423 x_reseq_dim_values(l_count).dim_level_name := 'dimension'||l_count||'_level_value';
424 IF (l_count = 1) THEN
425 IF (p_dimvalues_rec.dimension1_level_value IS NOT NULL) THEN
426 x_reseq_dim_values(1).dim_level_value :=
427 ''''||p_dimvalues_rec.dimension1_level_value||'''';
428 ELSE
429 x_reseq_dim_values(1).dim_level_value := p_dimvalues_rec.dimension1_level_Value;
430 END IF;
431 END IF;
432 IF (l_count = 2) THEN
433 IF (p_dimvalues_rec.dimension2_level_value IS NOT NULL) THEN
434 x_reseq_dim_values(2).dim_level_value :=
435 ''''||p_dimvalues_rec.dimension2_level_value||'''';
436 ELSE
437 x_reseq_dim_values(2).dim_level_value:= p_dimvalues_rec.dimension2_level_Value;
438 END IF;
439 END IF;
440 IF (l_count = 3) THEN
441 IF (p_dimvalues_rec.dimension3_level_value IS NOT NULL) THEN
442 x_reseq_dim_values(3).dim_level_value:=
443 ''''||p_dimvalues_rec.dimension3_level_value||'''';
444 ELSE
445 x_reseq_dim_values(3).dim_level_value := p_dimvalues_rec.dimension3_level_Value;
446 END IF;
447 END IF;
448 IF (l_count = 4) THEN
449 IF (p_dimvalues_rec.dimension4_level_value IS NOT NULL) THEN
450 x_reseq_dim_values(4).dim_level_value :=
451 ''''||p_dimvalues_rec.dimension4_level_value||'''';
452 ELSE
453 x_reseq_dim_values(4).dim_level_value := p_dimvalues_rec.dimension4_level_Value;
454 END IF;
455 END IF;
456 IF (l_count = 5) THEN
457 IF (p_dimvalues_rec.dimension5_level_value IS NOT NULL) THEN
458 x_reseq_dim_values(5).dim_level_value :=
459 ''''||p_dimvalues_rec.dimension5_level_value||'''';
460 ELSE
461 x_reseq_dim_values(5).dim_level_value := p_dimvalues_rec.dimension5_level_Value;
462 END IF;
463 END IF;
464 END LOOP;
465 END IF;
466 l_dim_levelcount := l_dim_levelcount + 1;
467 IF (l_dim_levelcount <= 6) THEN
468 x_reseq_dim_values(l_dim_levelcount).dim_level_name :=
469 'dimension'||l_dim_levelcount||'_level_value';
470 IF (p_dimvalues_rec.org_level_value IS NOT NULL) THEN
471 x_reseq_dim_values(l_dim_levelcount).dim_level_value :=
472 ''''||p_dimvalues_rec.org_level_value||'''';
473 ELSE
474 x_reseq_dim_values(l_dim_levelcount).dim_level_value := p_dimvalues_rec.org_level_value;
475 END IF;
476 l_dim_levelcount := l_dim_levelcount + 1;
480 x_reseq_dim_values(l_Dim_levelcount).dim_level_value := NULL;
477 FOR l_count IN l_dim_levelcount..7 LOOP
478 x_reseq_dim_values(l_dim_levelcount).dim_level_name :=
479 'dimension'||l_dim_levelcount||'_level_value';
481 l_dim_levelcount := l_dim_levelcount+1;
482 END LOOP;
483 END IF;
484 x_return_status := 0;
485 EXCEPTION
486 WHEN OTHERS THEN
487 x_return_status := -1;
488 END;
489
490
491 PROCEDURE MIGRATE_PERFORMANCE_MEASURES
492 (
493 x_return_status OUT NOCOPY VARCHAR2
494 ,x_return_code OUT NOCOPY NUMBER
495 )
496 IS
497 CURSOR c_targetlvls IS
498 SELECT target_level_id, indicator_id, short_name
499 ,time_level_id, org_level_id, dimension1_level_id
500 ,dimension2_level_id, dimension3_level_id, dimension4_level_id
501 ,dimension5_level_id, dimension6_level_id, dimension7_level_id
502 FROM bis_target_levels
503 WHERE (NVL(dimension1_level_id,-9999) <> time_level_id AND NVL(dimension1_level_id,-9999) <> org_level_id)
504 AND (NVL(dimension2_level_id,-9999) <> time_level_id AND NVL(dimension2_level_id,-9999) <> org_level_id)
505 AND (NVL(dimension3_level_id,-9999) <> time_level_id AND NVL(dimension3_level_id,-9999) <> org_level_id)
506 AND (NVL(dimension4_level_id,-9999) <> time_level_id AND NVL(dimension4_level_id,-9999) <> org_level_id)
507 AND (NVL(dimension5_level_id,-9999) <> time_level_id AND NVL(dimension5_level_id,-9999) <> org_level_id)
508 AND (NVL(dimension6_level_id,-9999) <> time_level_id AND NVL(dimension6_level_id,-9999) <> org_level_id)
509 AND (NVL(dimension7_level_id,-9999) <> time_level_id AND NVL(dimension7_level_id,-9999) <> org_level_id)
510 ; -- only get those rows from the old datamodel
511 CURSOR c_targetlvl_values(p_target_level_id IN NUMBER) IS
512 SELECT target_id, org_level_value, time_level_value
513 ,dimension1_level_value, dimension2_level_value, dimension3_level_value
514 ,dimension4_level_value, dimension5_level_value
515 FROM bis_target_values
516 WHERE target_level_id = p_target_level_id;
517 CURSOR c_actual_values(p_target_level_id IN NUMBER) IS
518 SELECT actual_id, target_level_id, org_level_value, time_level_value,
519 dimension1_level_value, dimension2_level_value
520 ,dimension3_level_value, dimension4_level_value
521 ,dimension5_level_value
522 FROM bis_Actual_values
523 WHERE target_level_id = p_target_level_id;
524 CURSOR c_usrind_values(p_target_level_id IN NUMBER) IS
525 SELECT ind_selection_id, target_level_id, org_level_value
526 ,dimension1_level_value, dimension2_level_value
527 ,dimension3_level_value, dimension4_level_value
528 ,dimension5_level_value
529 FROM bis_user_ind_selections
530 WHERE target_level_id = p_target_level_id;
531 l_target_level_rec BIS_PMF_MIGRATION_PVT.target_level_rec;
532 l_reseq_dims BIS_PMF_MIGRATION_PVT.resequenced_dimensions_Array;
533 l_target_rec BIS_PMF_MIGRATION_PVT.dim_Values_rec;
534 l_reseq_target_values BIS_PMF_MIGRATION_PVT.reseq_dim_values_arr;
535 l_Actual_rec BIS_PMF_MIGRATION_PVT.dim_values_rec;
536 l_reseq_actual_values BIS_PMF_MIGRATION_PVT.reseq_dim_values_arr;
537 l_userind_rec BIS_PMF_MIGRATION_PVT.dim_values_rec;
538 l_reseq_userind_Values BIS_PMF_MIGRATION_PVT.reseq_dim_values_arr;
539 l_return_status NUMBER;
540 l_sqlstmt VARCHAR2(32000);
541 l_sqlstmt1 VARCHAR2(32000);
542 l_sqlstmt2 VARCHAR2(32000);
543 l_sqlstmt3 VARCHAR2(32000);
544 l_count NUMBER := 1;
545 l_dummy_value NUMBER := 990;
546 l_dimlevel_count NUMBER;
547 l_dim_count NUMBER;
548 l_dim_levelvalue_count NUMBER;
549 l_indicator_id BIS_INDICATORS.INDICATOR_ID%TYPE;
550 l_error_tbl BIS_UTILITIES_PUB.ERROR_TBL_TYPE;
551 l_sequence_no NUMBER;
552 l_dim_level_id NUMBER;
553 l_dim_level_short_name VARCHAR2(32000);
554 l_dim_level_name VARCHAR2(32000);
555 l_ret_status VARCHAR2(32000);
556 l_userind_org_name VARCHAR2(32000);
557 l_userind_org_value VARCHAR2(32000);
558 l_target_level_rec_new BIS_TARget_LEVEL_PUB.TARGET_LEVEL_REC_TYPE;
559 -- meastmon 06/21/2001
560 l_userind_current_org_value VARCHAR2(32000);
561 BEGIN
562 --Loop thru the cursor and resequence the dimension levels
563 FOR c_rec IN c_targetlvls LOOP
564 l_target_level_rec.measure_id := c_rec.indicator_id;
565 l_target_level_rec.target_level_id := c_rec.target_level_id;
566 l_target_level_rec.org_levelid := c_rec.org_level_id;
567 l_target_level_rec.time_levelid := c_Rec.time_level_id;
568 l_target_level_rec.dimension1_levelid := c_rec.dimension1_level_id;
569 l_target_level_rec.dimension2_levelid := c_rec.dimension2_level_id;
570 l_Target_level_Rec.dimension3_levelid := c_rec.dimension3_level_id;
571 l_target_level_rec.dimension4_levelid := c_rec.dimension4_level_id;
572 l_target_level_rec.dimension5_levelid := c_rec.dimension5_level_id;
573 l_dimlevel_count := 0;
574 IF (c_rec.org_level_id IS NOT NULL) THEN
575 l_dimlevel_count := l_dimlevel_count+1;
576 END IF;
577 IF (c_rec.time_level_id IS NOT NULL) THEN
578 l_dimlevel_count := l_dimlevel_count+1;
579 END IF;
580 IF (c_rec.dimension1_level_id IS NOT NULL) THEN
581 l_dimlevel_count := l_dimlevel_count+1;
582 END IF;
583 IF (c_rec.dimension2_level_id IS NOT NULL) THEN
587 l_dimlevel_count := l_dimlevel_count+1;
584 l_dimlevel_count := l_dimlevel_count+1;
585 END IF;
586 IF (c_rec.dimension3_level_id IS NOT NULL) THEN
588 END IF;
589 IF (c_rec.dimension4_level_id IS NOT NULL) THEN
590 l_dimlevel_count := l_dimlevel_count+1;
591 END IF;
592 IF (c_rec.dimension5_level_id IS NOT NULL) THEN
593 l_dimlevel_count := l_dimlevel_count+1;
594 END IF;
595 --Check if the org and time levels are null. If they are then there is no
596 --need to resequence anything. This means the records are not really from an
597 -- older version of BIS. Also ignore any targets, actuals , user indicator dimensions
598 -- for this record since if they have org and time level values the whole thing is
599 --messed up anyway.
600 IF ((c_rec.org_level_id IS NOT NULL) OR (c_rec.time_level_id IS NOT NULL)) THEN
601 l_target_level_rec_new.dimension1_level_id := c_rec.dimension1_level_id;
602 l_target_level_rec_new.dimension2_level_id := c_rec.dimension2_level_id;
603 l_target_level_rec_new.dimension3_level_id := c_rec.dimension3_level_id;
604 l_target_level_rec_new.dimension4_level_id := c_rec.dimension4_level_id;
605 l_target_level_rec_new.dimension5_level_id := c_rec.dimension5_level_id;
606 l_target_level_rec_new.dimension6_level_id := c_rec.dimension6_level_id;
607 l_target_level_rec_new.dimension7_level_id := c_rec.dimension7_level_id;
608 l_target_level_rec_new.org_level_id := c_rec.org_level_id;
609 l_target_level_rec_new.time_level_id := c_rec.time_level_id;
610 l_target_level_rec_new.measure_id := c_rec.indicator_id;
611
612 IF (BIS_PMF_MIGRATION_PVT.NEEDS_MIGRATION(l_target_level_rec_new)) THEN
613 BIS_PMF_MIGRATION_PVT.resequence_dimension_levels
614 (p_target_level_rec => l_target_level_rec
615 ,x_resequenced_dimensions => l_reseq_dims
616 ,x_dim_count => l_dim_count
617 ,x_return_status => l_return_status
618 );
619 -- Check if the org_level_id and time_level_id are already part of one of
620 -- the seven dimension levels. We really need to go back to the dimensions
621 -- for these levels in order to verify that. This is to allow the re-re-running
622 -- of this particular procedure
623 IF (l_return_status = 0 AND l_reseq_dims.COUNT>0) THEN
624 IF (l_dim_count = l_dimlevel_count) THEN
625 --First update the dimension sequences
626 l_Dummy_value := 990;
627 FOR l_count IN 1..l_reseq_dims.COUNT LOOP
628 IF c_rec.indicator_id = 1001 THEN
629 NULL;--
630 END If;
631 END LOOP;
632 FOR l_count IN 1..l_dim_count LOOP
633 IF (l_reseq_dims(l_count).dim_id IS NULL) THEN
634 --EXIT;
635 GOTO skip_record;
636 ELSE
637 --if (c_rec.indicator_id < 995) THEN
638 UPDATE bis_indicator_dimensions
639 SET sequence_no = l_dummy_value
640 WHERE dimension_id=l_Reseq_dims(l_count).dim_id AND
641 indicator_id=c_rec.indicator_id;
642 --END IF;
643 END IF;
644 l_dummy_value := l_dummy_value + 1;
645 END LOOP;
646 FOR l_count IN 1..l_dim_count LOOP
647 IF (l_reseq_dims(l_count).dim_id IS NULL) THEN
648 EXIT;
649 ELSE
650 --if (c_rec.indicator_id > 995) THEN
651 l_indicator_id := c_rec.indicator_id;
652 UPDATE bis_indicator_dimensions
653 SET sequence_no= l_reseq_dims(l_count).seq_no
654 WHERE dimension_id = l_reseq_dims(l_count).dim_id AND
655 indicator_id = c_Rec.indicator_id;
656 --END IF;
657 END IF;
658 END LOOP;
659 END IF;
660 IF (l_dim_count = l_dimlevel_count) THEN
661 l_sqlstmt := 'UPDATE bis_target_levels SET ' ||
662 l_reseq_dims(1).dim_level_col ||' = :1 ' ||
663 ' , '||l_reseq_dims(2).dim_level_col ||' = :2 '||
664 ' , '||l_reseq_dims(3).dim_level_col ||' = :3 '||
665 ' , '||l_reseq_dims(4).dim_level_col ||' = :4 '||
666 ' , '||l_reseq_dims(5).dim_level_col ||' = :5 '||
667 ' , '||l_reseq_dims(6).dim_level_col ||' = :6 '||
668 ' , '||l_reseq_dims(7).dim_level_col ||' = :7 '||
669 ' WHERE target_level_id =:8';
670 EXECUTE IMMEDIATE l_sqlstmt USING to_char(l_reseq_dims(1).dim_level_col_val),
671 to_char(l_reseq_dims(2).dim_level_col_val), to_char(l_reseq_dims(3).dim_level_col_val),
675 l_count := 0;
672 to_char(l_reseq_dims(4).dim_level_col_val), to_char(l_reseq_dims(5).dim_level_col_val),
673 to_char(l_reseq_dims(6).dim_level_col_val), to_char(l_reseq_dims(7).dim_level_col_val),
674 c_rec.target_level_id;
676 /*Migrate the targets*/
677 FOR c_targetrec IN c_targetlvl_values(c_rec.target_level_id) LOOP
678 l_count := l_count+1;
679 l_target_rec.org_level_value := c_targetrec.org_level_value;
680 l_target_rec.time_level_value := c_targetrec.time_level_value;
681 l_target_rec.dimension1_level_value := c_targetrec.dimension1_level_value;
682 l_target_rec.dimension2_level_value := c_targetrec.dimension2_level_value;
683 l_target_rec.dimension3_level_value := c_targetrec.dimension3_level_value;
684 l_target_rec.dimension4_level_value := c_targetrec.dimension4_level_value;
685 l_Target_rec.dimension5_level_value := c_targetrec.dimension5_level_value;
686 BIS_PMF_MIGRATION_PVT.resequence_dim_level_values(
687 p_dimvalues_rec => l_target_rec
688 ,p_dim_count => l_dim_count
689 ,x_reseq_dim_values => l_reseq_target_values
690 ,x_return_Status => l_return_status
691 );
692 IF ((l_return_status = 0) AND (l_reseq_target_values.COUNT > 0)) THEN
693 l_sqlstmt1 := 'UPDATE bis_target_values SET '||
694 l_reseq_target_values(1).dim_level_name ||' = :1 '||
695 ' ,'|| l_Reseq_target_values(2).dim_level_name ||' = :2 ' ||
696 ' ,' || l_Reseq_target_values(3).dim_level_name ||' = :3 '||
697 ' ,' || l_Reseq_target_values(4).dim_level_name||' = :4 ' ||
698 ' ,' || l_Reseq_target_values(5).dim_level_name||' = :5 ' ||
699 ' ,' || l_Reseq_target_values(6).dim_level_name||' = :6 ' ||
700 ' ,' || l_Reseq_target_values(7).dim_level_name||' = :7 ' ||
701 ' WHERE target_level_id = :8 AND target_id = :9';
702 EXECUTE IMMEDIATE l_sqlstmt1 USING l_reseq_target_values(1).dim_level_value,
703 l_reseq_target_values(2).dim_level_value, l_reseq_target_values(3).dim_level_value,
704 l_reseq_target_values(4).dim_level_value, l_reseq_target_values(5).dim_level_value,
705 l_reseq_target_values(6).dim_level_value, l_reseq_target_values(7).dim_level_value,
706 c_rec.target_level_id, c_targetrec.target_id;
707 END IF;
708 END LOOP;
709 --Migrate the Actual Values
710 FOR c_actual_rec IN c_actual_values(c_rec.target_level_id) LOOP
711 l_actual_rec.target_level_id := c_actual_rec.target_level_id;
712 l_actual_rec.org_level_value := c_actual_rec.org_level_value;
713 l_actual_rec.time_level_value := c_actual_rec.time_level_value;
714 l_actual_rec.dimension1_level_value := c_actual_rec.dimension1_level_value;
715 l_actual_rec.dimension2_level_Value := c_actual_rec.dimension2_level_value;
716 l_actual_rec.dimension3_level_value := c_actual_rec.dimension3_level_value;
717 l_actual_rec.dimension4_level_value := c_actual_rec.dimension4_level_value;
718 l_actual_rec.dimension5_level_value := c_actual_rec.dimension5_level_value;
719 BIS_PMF_MIGRATION_PVT.resequence_dim_level_values(
720 p_dimvalues_rec => l_actual_rec
721 ,p_dim_count => l_dim_count
722 ,x_reseq_dim_values => l_reseq_actual_values
723 ,x_return_Status => l_return_status
724 );
725 IF ((l_return_status = 0) AND (l_reseq_actual_values.COUNT > 0)) THEN
726 l_sqlstmt2 := 'UPDATE bis_actual_values SET '||
727 l_reseq_actual_values(1).dim_level_name ||' = :1 '||
728 ' ,'|| l_Reseq_actual_values(2).dim_level_name ||' = :2 ' ||
729 ' ,' || l_Reseq_actual_values(3).dim_level_name ||' = :3 '||
730 ' ,' || l_Reseq_actual_values(4).dim_level_name||' = :4 ' ||
731 ' ,' || l_Reseq_actual_values(5).dim_level_name||' = :5 ' ||
732 ' ,' || l_Reseq_actual_values(6).dim_level_name||' = :6 ' ||
733 ' ,' || l_Reseq_actual_values(7).dim_level_name||' = :7 ' ||
734 ' WHERE target_level_id = :8 AND actual_id = :9 ';
735 EXECUTE IMMEDIATE l_sqlstmt2 USING l_reseq_actual_values(1).dim_level_value,
736 l_reseq_actual_values(2).dim_level_value, l_reseq_actual_values(3).dim_level_value,
740 END IF;
737 l_reseq_actual_values(4).dim_level_value, l_reseq_actual_values(5).dim_level_value,
738 l_reseq_actual_values(6).dim_level_value, l_reseq_actual_values(7).dim_level_value,
739 c_rec.target_level_id, c_actual_Rec.actual_id;
741 END LOOP;
742 END IF;
743
744 FOR c_userind_rec IN c_usrind_values(c_rec.target_level_id) LOOP
745 l_userind_rec.target_level_id := c_userind_rec.target_level_id;
746 l_userind_rec.org_level_value := c_userind_rec.org_level_value;
747 l_userind_rec.time_level_value := NULL;
748 l_userind_rec.dimension1_level_value := c_userind_rec.dimension1_level_value;
749 l_userind_rec.dimension2_level_Value := c_userind_rec.dimension2_level_value;
750 l_userind_rec.dimension3_level_value := c_userind_rec.dimension3_level_value;
751 l_userind_rec.dimension4_level_value := c_userind_rec.dimension4_level_value;
752 l_userind_rec.dimension5_level_value := c_userind_rec.dimension5_level_value;
753 /*BIS_PMF_MIGRATION_PVT.resequence_ind_level_values(
754 p_dimvalues_rec => l_userind_rec
755 ,p_dim_count => l_dim_count
756 ,x_reseq_dim_values => l_reseq_userind_values
757 ,x_return_Status => l_return_status
758 ); */
759 -- For user indicator selections find out NOCOPY what is the org level and just update
760 -- that particular dimension. The above logic tries to move the org level value to
761 -- the end which is wrong
762 BIS_PMF_DEFINER_WRAPPER_PVT.GET_ORG_LEVEL_ID
763 (p_performance_measure_id => c_rec.indicator_id
764 ,p_target_level_id => c_rec.target_level_id
765 ,p_perf_measure_short_name => null
766 ,p_target_level_short_name => null
767 ,x_sequence_no => l_sequence_no
768 ,x_dim_level_id => l_dim_level_id
769 ,x_dim_level_short_name => l_dim_level_short_name
770 ,x_dim_level_name => l_dim_level_name
771 ,x_return_status => l_ret_status
772 ,x_error_tbl =>l_error_tbl
773 );
774 l_userind_org_name := '';
775 l_userind_org_value := '';
776
777 --meastmon 06/21/20001
778 --save the current value for organization level
779 --It will be used to decide whether we have to update the record or not.
780 l_userind_current_org_value := NULL;
781
782 IF (l_sequence_no = 1) THEN
783 l_userind_org_name := 'dimension1_level_value';
784 l_userind_org_value := ''''||c_userind_rec.org_level_value||'''';
785 l_userind_current_org_value := c_userind_rec.dimension1_level_value;
786 END IF;
787 IF (l_sequence_no = 2) THEN
788 l_userind_org_name := 'dimension2_level_value';
789 l_userind_org_value := ''''||c_userind_rec.org_level_Value||'''';
790 l_userind_current_org_value := c_userind_rec.dimension2_level_value;
791 END IF;
792 IF (l_sequence_no = 3) THEN
793 l_userind_org_name := 'dimension3_level_value';
794 l_userind_org_value := ''''||c_userind_rec.org_level_value||'''';
795 l_userind_current_org_value := c_userind_rec.dimension3_level_value;
796 END IF;
797 IF (l_sequence_no = 4) THEN
798 l_userind_org_name := 'dimension4_level_value';
799 l_userind_org_value := ''''||c_userind_rec.org_level_value||'''';
800 l_userind_current_org_value := c_userind_rec.dimension4_level_value;
801 END IF;
802 IF (l_sequence_no = 5) THEN
803 l_userind_org_name := 'dimension5_level_value';
804 l_userind_org_value := ''''||c_userind_rec.org_level_value||'''';
805 l_userind_current_org_value := c_userind_rec.dimension5_level_value;
806 END IF;
807 IF (l_sequence_no = 6) THEN
808 l_userind_org_name := 'dimension6_level_value';
809 l_userind_org_value := ''''||c_userind_rec.org_level_value||'''';
810 END IF;
811 IF (l_sequence_no = 7) THEN
812 l_userind_org_name := 'dimension7_level_value';
813 l_userind_org_value := ''''||c_userind_rec.org_level_value||'''';
814 END IF;
815 --IF ((l_return_status = 0) AND (l_reseq_userind_values.COUNT > 0)) THEN
816 /*l_sqlstmt3 := 'UPDATE bis_user_ind_selections SET '||
817 l_reseq_userind_values(1).dim_level_name ||' = '||
818 NVL(l_reseq_userind_values(1).dim_level_value, 'NULL') ||
819 ' ,'|| l_reseq_userind_values(2).dim_level_name ||' = ' ||
820 NVL(l_reseq_userind_values(2).dim_level_value, 'NULL') ||
821 ' ,' || l_reseq_userind_values(3).dim_level_name ||' = '||
822 NVL(l_reseq_userind_values(3).dim_level_value,'NULL') ||
823 ' ,' || l_reseq_userind_values(4).dim_level_name||' = ' ||
824 NVL(l_reseq_userind_values(4).dim_level_value,'NULL') ||
825 ' ,' || l_reseq_userind_values(5).dim_level_name||' = ' ||
826 NVL(l_reseq_userind_values(5).dim_level_value,'NULL') ||
827 ' ,' || l_reseq_userind_values(6).dim_level_name||' = ' ||
828 NVL(l_reseq_userind_values(6).dim_level_value,'NULL') ||
829 ' ,' || l_reseq_userind_values(7).dim_level_name||' = ' ||
830 NVL(l_reseq_userind_values(7).dim_level_value,'NULL') ||
831 ' WHERE target_level_id = :1 ';*/
832 --meastmon 06/21/2001
833 --If it is null --> Update the record
834 --Else --> dont touch it (It could be a new user selection or the migration was done before)
835 IF (l_userind_current_org_value IS NULL AND l_userind_org_name IS NOT NULL) THEN
836 l_sqlstmt3 := 'UPDATE bis_user_ind_selections SET '||
837 l_userind_org_name ||'= :1 ' ||
838 ' WHERE ind_selection_id = :2 AND target_level_id = :3';
839 EXECUTE IMMEDIATE l_sqlstmt3 USING l_userind_org_value, c_userind_rec.ind_selection_id, c_rec.target_level_id;
840 END IF;
841 END LOOP;
842 --END IF;
843 END IF;
844 END IF;
845 END IF;
846 <<skip_record>>
847 null;
848 END LOOP;
849 COMMIT;
850 --rollback;
851
852 x_return_Status := 'Success';
853 x_return_code := 0;
854 EXCEPTION
855 WHEN OTHERS THEN
856 ROLLBACK;
857 x_return_Status := SQLERRM;
858 x_return_code := SQLCODE;
859 END MIGRATE_PERFORMANCE_MEASURES;
860
861
862
863 FUNCTION NEEDS_MIGRATION
864 (p_target_level_rec IN BIS_TARGET_LEvel_PUB.TARGET_LEVEL_REC_TYPE
865 )
866 RETURN BOOLEAN
867 IS
868 l_org_time_exists BOOLEAN;
869 l_org_id NUMBER;
870 l_time_id NUMBER;
871 l_dim1_id NUMBER;
875 l_dim5_id NUMBER;
872 l_dim2_id NUMBER;
873 l_dim3_id NUMBER;
874 l_dim4_id NUMBER;
876 l_dim6_id NUMBER;
877 l_dim7_id NUMBER;
878
879 CURSOR c_target_lvl_exists(cp_tl_rec IN BIS_TARGET_LEvel_PUB.TARGET_LEVEL_REC_TYPE) IS
880 SELECT COUNT(1) FROM bis_target_levels
881 WHERE indicator_id = cp_tl_rec.measure_id
882 AND ( cp_tl_rec.dimension1_level_id IS NULL OR
883 dimension1_level_id = cp_tl_rec.dimension1_level_id
884 )
885 AND ( cp_tl_rec.dimension2_level_id IS NULL OR
886 dimension2_level_id = cp_tl_rec.dimension2_level_id
887 )
888 AND ( cp_tl_rec.dimension3_level_id IS NULL OR
889 dimension3_level_id = cp_tl_rec.dimension3_level_id
890 )
891 AND ( cp_tl_rec.dimension4_level_id IS NULL OR
892 dimension4_level_id = cp_tl_rec.dimension4_level_id
893 )
894 AND ( cp_tl_rec.dimension5_level_id IS NULL OR
895 dimension5_level_id = cp_tl_rec.dimension5_level_id
896 )
897 AND ( cp_tl_rec.dimension6_level_id IS NULL OR
898 dimension6_level_id = cp_tl_rec.dimension6_level_id
899 )
900 AND ( cp_tl_rec.dimension7_level_id IS NULL OR
901 dimension7_level_id = cp_tl_rec.dimension7_level_id
902 );
903
904 l_tl_count NUMBER := 0;
905 l_mig_target_level_rec BIS_PMF_MIGRATION_PVT.target_level_rec;
906 l_target_level_rec BIS_TARGET_LEVEL_PUB.TARGET_LEVEL_REC_TYPE;
907 l_reseq_dims BIS_PMF_MIGRATION_PVT.resequenced_dimensions_Array;
908 l_return_status NUMBER;
909 l_dim_count NUMBER;
910
911 BEGIN
912
913 -- Get the resequenced dimension levels which will be used to update
914 -- the record to be migrated
915
916 l_mig_target_level_rec.measure_id := p_target_level_rec.measure_id;
917 l_mig_target_level_rec.target_level_id := p_target_level_rec.target_level_id;
918 l_mig_target_level_rec.org_levelid := p_target_level_rec.org_level_id;
919 l_mig_target_level_rec.time_levelid := p_target_level_rec.time_level_id;
920 l_mig_target_level_rec.dimension1_levelid := p_target_level_rec.dimension1_level_id;
921 l_mig_target_level_rec.dimension2_levelid := p_target_level_rec.dimension2_level_id;
922 l_mig_target_level_Rec.dimension3_levelid := p_target_level_rec.dimension3_level_id;
923 l_mig_target_level_rec.dimension4_levelid := p_target_level_rec.dimension4_level_id;
924 l_mig_target_level_rec.dimension5_levelid := p_target_level_rec.dimension5_level_id;
925
926 BIS_PMF_MIGRATION_PVT.resequence_dimension_levels
927 (p_target_level_rec => l_mig_target_level_rec
928 ,x_resequenced_dimensions => l_reseq_dims
929 ,x_dim_count => l_dim_count
930 ,x_return_status => l_return_status
931 );
932
933 -- Create a target level record using the dimension 1 to 7 level ids
934
935 l_target_level_rec.measure_id := p_target_level_rec.measure_id;
936 l_target_level_rec.dimension1_level_id := l_reseq_dims(1).dim_level_col_val;
937 l_target_level_rec.dimension2_level_id := l_reseq_dims(2).dim_level_col_val;
938 l_target_level_rec.dimension3_level_id := l_reseq_dims(3).dim_level_col_val;
939 l_target_level_rec.dimension4_level_id := l_reseq_dims(4).dim_level_col_val;
940 l_target_level_rec.dimension5_level_id := l_reseq_dims(5).dim_level_col_val;
941 l_target_level_rec.dimension6_level_id := l_reseq_dims(6).dim_level_col_val;
942 l_target_level_rec.dimension7_level_id := l_reseq_dims(7).dim_level_col_val;
943
944 -- If the target level combination already exists then the target level should
945 -- not be migrated
946
947 IF (c_target_lvl_exists%ISOPEN) THEN
948 CLOSE c_target_lvl_exists;
949 END IF;
950
951 OPEN c_target_lvl_exists(cp_tl_rec => l_target_level_rec);
952 FETCH c_target_lvl_exists INTO l_tl_count;
953 CLOSE c_target_lvl_exists;
954
955 IF (l_tl_count <> 0) THEN
956 RETURN FALSE;
957 END IF;
958
959 -- IF either org or time exists then return true here. As it should never
960 -- happen that, only org got migrated and not time
961 l_org_id := BIS_PMF_MIGRATION_PVT.GET_DIMENSION_ID(p_target_level_rec.org_level_id);
962 l_time_id := BIS_PMF_MIGRATION_PVT.GET_DIMENSION_ID(p_target_level_rec.time_level_id);
963 l_dim1_id := BIS_PMF_MIGRATION_PVT.GET_DIMENSION_ID(p_target_level_rec.dimension1_level_id);
964 l_dim2_id := BIS_PMF_MIGRATION_PVT.GET_DIMENSION_ID(p_target_level_rec.dimension2_level_id);
965 l_dim3_id := BIS_PMF_MIGRATION_PVT.GET_DIMENSION_ID(p_target_level_rec.dimension3_level_id);
966 l_dim4_id := BIS_PMF_MIGRATION_PVT.GET_DIMENSION_ID(p_target_level_rec.dimension4_level_id);
967 l_dim5_id := BIS_PMF_MIGRATION_PVT.GET_DIMENSION_ID(p_target_level_rec.dimension5_level_id);
968 l_dim6_id := BIS_PMF_MIGRATION_PVT.GET_DIMENSION_ID(p_target_level_rec.dimension6_level_id);
969 l_dim7_id := BIS_PMF_MIGRATION_PVT.GET_DIMENSION_ID(p_target_level_rec.dimension7_level_id);
970
971 l_org_time_exists := TRUE;
972 IF (l_org_id = l_dim1_id)
973 THEN
974 l_org_time_exists := FALSE;
975 GOTO returnfromproc;
976 END IF;
977 IF (l_org_id = l_dim2_id)
978 THEN
979 l_org_time_exists := FALSE;
980 GOTO returnfromproc;
981 END IF;
982 IF (l_org_id = l_dim3_id)
983 THEN
984 l_org_time_exists := FALSE;
985 GOTO returnfromproc;
986 END IF;
987 IF (l_org_id = l_dim4_id)
988 THEN
989 l_org_time_exists := FALSE;
990 GOTO returnfromproc;
991 END IF;
992 IF (l_org_id = l_dim5_id)
993 THEN
994 l_org_time_exists := FALSE;
995 GOTO returnfromproc;
996 END IF;
997 IF (l_org_id = l_dim6_id)
998 THEN
999 l_org_time_exists := FALSE;
1000 GOTO returnfromproc;
1001 END IF;
1002 IF (l_org_id = l_dim7_id)
1003 THEN
1004 l_org_time_exists := FALSE;
1005 GOTO returnfromproc;
1006 END IF;
1007 IF (l_time_id = l_dim1_id)
1008 THEN
1009 l_org_time_exists := FALSE;
1010 GOTO returnfromproc;
1011 END IF;
1012 IF (l_time_id = l_dim2_id)
1013 THEN
1014 l_org_time_exists := FALSE;
1015 GOTO returnfromproc;
1016 END IF;
1017 IF (l_time_id = l_dim3_id)
1018 THEN
1019 l_org_time_exists := FALSE;
1020 GOTO returnfromproc;
1021 END IF;
1022 IF (l_time_id = l_dim4_id)
1023 THEN
1024 l_org_time_exists := FALSE;
1025 GOTO returnfromproc;
1026 END IF;
1027 IF (l_time_id = l_dim5_id)
1028 THEN
1029 l_org_time_exists := FALSE;
1030 GOTO returnfromproc;
1031 END IF;
1032 IF (l_time_id = l_dim6_id)
1033 THEN
1034 l_org_time_exists := FALSE;
1035 GOTO returnfromproc;
1036 END IF;
1037 IF (l_time_id = l_dim7_id)
1038 THEN
1039 l_org_time_exists := FALSE;
1040 GOTO returnfromproc;
1041 END IF;
1042 <<returnfromproc>>
1043 RETURN l_org_time_exists;
1044 EXCEPTION
1045 WHEN OTHERS THEN
1046 IF (c_target_lvl_exists%ISOPEN) THEN
1047 CLOSE c_target_lvl_exists;
1048 END IF;
1049 RETURN FALSE;
1050 END;
1051 END BIS_PMF_MIGRATION_PVT;
1052 --SHOW ERRORS
[Home] [Help]