[Home] [Help]
PACKAGE BODY: APPS.BIS_PMF_MIGRATION_PUB
Source
1 PACKAGE BODY BIS_PMF_MIGRATION_PUB AS
2 /* $Header: BISPMIGB.pls 120.1 2005/10/21 07:08:36 ppandey 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 | |
21 REM | 21-MAR-05 ankagarw bug#4235732 - changing count(*) to count(1) |
22 REM | 19-OCT-2005 ppandey Enh 4618419- SQL Literal Fix |
23 REM +=======================================================================+
24 */
25 --
26 --
27 FUNCTION GET_DIMENSION_ID
28 (p_dimension_level_id IN NUMBER
29 )
30 RETURN NUMBER
31 IS
32 CURSOR c_dim IS
33 SELECT dimension_id
34 FROM bis_levels
35 WHERE level_id = p_dimension_level_id;
36 l_dim_id NUMBER;
37 BEGIN
38 OPEN c_dim;
39 FETCH c_dim INTO l_dim_id;
40 CLOSE c_dim;
41 RETURN l_dim_id;
42 EXCEPTION
43 WHEN OTHERS THEN
44 null;
45 END GET_DIMENSION_ID;
46 --
47 PROCEDURE RESEQUENCE_DIMENSION_LEVELS(
48 p_target_level_rec IN BIS_PMF_MIGRATION_PUB.target_level_rec
49 ,x_resequenced_dimensions OUT NOCOPY BIS_PMF_MIGRATION_PUB.resequenced_dimensions_array
50 ,x_dim_count OUT NOCOPY NUMBER
51 ,x_return_status OUT NOCOPY NUMBER
52 )
53 IS
54 CURSOR c_dimcount(p_measure_id IN NUMBER) IS
55 SELECT count(1) FROM
56 bis_indicator_dimensions
57 WHERE indicator_id = p_measure_id;
58 l_reseuenced_dimensions_array BIS_PMF_MIGRATION_PUB.resequenced_dimensions_array;
59 l_dimcount NUMBER;
60 BEGIN
61 OPEN c_dimcount(p_target_level_rec.measure_id);
62 FETCH c_dimcount INTO l_dimcount;
63 IF (c_dimcount%NOTFOUND) THEN
64 x_return_status := -1;
65 END IF;
66 CLOSE c_dimcount;
67 x_dim_count := l_dimcount;
68 -- Since the org/time are always the first two subtract two from the count
69 --If somebody for some reason runs the migration script again , do not subtract two
70 IF (p_target_level_rec.org_levelid IS NOT NULL and p_target_level_rec.time_levelid IS NOT NULL) THEN
71 l_dimcount := l_dimcount-2;
72 END IF;
73 IF (l_dimcount > 0) THEN
74 FOR l_count IN 1..l_dimcount LOOP
75 x_resequenced_dimensions(l_count).dim_level_col := 'dimension'||l_count||'_level_id';
76 IF (l_count = 1) THEN
77 x_resequenced_dimensions(1).dim_level_col_val := p_target_level_rec.dimension1_levelid;
78 x_resequenced_dimensions(1).dim_id := bis_pmf_migration_pub.get_dimension_id (
79 p_target_level_rec.dimension1_levelid);
80 x_Resequenced_dimensions(1).seq_no := l_count;
81 END IF;
82 IF (l_count=2) THEN
83 x_resequenced_dimensions(2).dim_level_col_val := p_target_level_rec.dimension2_levelid;
84 x_resequenced_dimensions(2).dim_id := bis_pmf_migration_pub.get_dimension_id (
85 p_target_level_rec.dimension2_levelid);
86 x_Resequenced_dimensions(2).seq_no := l_count;
87 END IF;
88 IF (l_count=3) THEN
89 x_resequenced_dimensions(3).dim_level_col_val := p_target_level_rec.dimension3_levelid;
90 x_resequenced_dimensions(3).dim_id := bis_pmf_migration_pub.get_dimension_id (
91 p_target_level_rec.dimension3_levelid);
92 x_Resequenced_dimensions(3).seq_no := l_count;
93 END IF;
94 IF (l_count=4) THEN
95 x_resequenced_dimensions(4).dim_level_col_val := p_target_level_rec.dimension4_levelid;
96 x_resequenced_dimensions(4).dim_id := bis_pmf_migration_pub.get_dimension_id (
97 p_target_level_rec.dimension4_levelid);
98 x_Resequenced_dimensions(4).seq_no := l_count;
99 END IF;
100 IF (l_count=5) THEN
101 x_resequenced_dimensions(5).dim_level_col_val := p_target_level_rec.dimension5_levelid;
102 x_resequenced_dimensions(5).dim_id := bis_pmf_migration_pub.get_dimension_id (
103 p_target_level_rec.dimension5_levelid);
104 x_Resequenced_dimensions(5).seq_no := l_count;
105 END IF;
106 END LOOP;
107 END IF;
108 l_dimcount := l_dimcount + 1;
109 IF (l_dimcount <= 7) THEN
110 x_resequenced_dimensions(l_dimcount).dim_level_col := 'dimension'||l_dimcount||'_level_id';
111 x_resequenced_dimensions(l_dimcount).dim_level_col_val := p_target_level_rec.org_levelid;
112 x_resequenced_dimensions(l_dimcount).dim_id := bis_pmf_migration_pub.get_dimension_id (
113 p_target_level_rec.org_levelid);
114 x_Resequenced_dimensions(l_dimcount).seq_no := l_dimcount;
115 l_dimcount := l_dimcount + 1;
116 x_resequenced_dimensions(l_dimcount).dim_level_col := 'dimension'||l_dimcount||'_level_id';
117 x_resequenced_dimensions(l_dimcount).dim_level_col_val := p_target_level_rec.time_levelid;
118 x_resequenced_dimensions(l_dimcount).dim_id := bis_pmf_migration_pub.get_dimension_id (
119 p_target_level_rec.time_levelid);
120 x_Resequenced_dimensions(l_dimcount).seq_no := l_dimcount;
121 l_dimcount := l_dimcount +1;
122 FOR l_count IN l_dimcount..7 LOOP
123 x_resequenced_dimensions(l_dimcount).dim_level_col := 'dimension'||l_dimcount||'_level_id';
124 x_resequenced_dimensions(l_dimcount).dim_level_col_val := NULL;
125 l_dimcount := l_dimcount+1;
126 END LOOP;
127 END IF;
128 x_return_status := 0;
129 EXCEPTION
130 WHEN OTHERS THEN
131 x_return_status := -1;
132 END RESEQUENCE_DIMENSION_LEVELS;
133 --
134 PROCEDURE RESEQUENCE_TARGET_LEVEL_VALUES(
135 p_target_rec IN BIS_PMF_MIGRATION_PUB.target_rec
136 ,p_dim_count IN NUMBER
137 ,x_reseq_target_values OUT NOCOPY BIS_PMF_MIGRATION_PUB.reseq_target_values_arr
138 ,x_return_Status OUT NOCOPY NUMBER
139 )
140 IS
141 l_dim_levelcount NUMBER ;
142 BEGIN
143 l_dim_levelcount := p_dim_count;
144 IF(p_target_rec.org_level_value IS NOT NULL AND p_target_Rec.time_level_value IS NOT NULL) THEN
145 l_dim_levelcount := l_dim_levelcount - 2;
146 END IF;
147 IF (l_dim_levelcount > 0) THEN
148 FOR l_count IN 1..l_dim_levelcount LOOP
149 x_reseq_target_values(l_count).target_level := 'dimension'||l_count||'_level_value';
150 IF (l_count = 1) THEN
151 IF (p_target_rec.dimension1_level_value IS NOT NULL) THEN
152 x_reseq_target_values(1).target_level_value :=
153 ''''||p_target_rec.dimension1_level_value||'''';
154 ELSE
155 x_reseq_target_values(1).target_level_value := p_target_rec.dimension1_level_Value;
156 END IF;
157 END IF;
158 IF (l_count = 2) THEN
159 IF (p_target_rec.dimension2_level_value IS NOT NULL) THEN
160 x_reseq_target_values(2).target_level_value :=
161 ''''||p_target_rec.dimension2_level_value||'''';
162 ELSE
163 x_reseq_target_values(2).target_level_value := p_target_rec.dimension2_level_Value;
164 END IF;
165 END IF;
166 IF (l_count = 3) THEN
167 IF (p_target_rec.dimension3_level_value IS NOT NULL) THEN
168 x_reseq_target_values(3).target_level_value :=
169 ''''||p_target_rec.dimension3_level_value||'''';
170 ELSE
171 x_reseq_target_values(3).target_level_value := p_target_rec.dimension3_level_Value;
172 END IF;
173 END IF;
174 IF (l_count = 4) THEN
175 IF (p_target_rec.dimension4_level_value IS NOT NULL) THEN
176 x_reseq_target_values(4).target_level_value :=
177 ''''||p_target_rec.dimension4_level_value||'''';
178 ELSE
179 x_reseq_target_values(4).target_level_value := p_target_rec.dimension4_level_Value;
180 END IF;
181 END IF;
182 IF (l_count = 5) THEN
183 IF (p_target_rec.dimension5_level_value IS NOT NULL) THEN
184 x_reseq_target_values(5).target_level_value :=
185 ''''||p_target_rec.dimension5_level_value||'''';
186 ELSE
187 x_reseq_target_values(5).target_level_value := p_target_rec.dimension5_level_Value;
188 END IF;
189 END IF;
190 END LOOP;
191 END IF;
192 l_dim_levelcount := l_dim_levelcount + 1;
193 IF (l_dim_levelcount <= 7) THEN
194 x_reseq_target_values(l_dim_levelcount).target_level :=
195 'dimension'||l_dim_levelcount||'_level_value';
196 IF (p_target_rec.org_level_value IS NOT NULL) THEN
197 x_reseq_target_values(l_dim_levelcount).target_level_value :=
198 ''''||p_target_rec.org_level_value||'''';
199 ELSE
200 x_reseq_target_values(l_dim_levelcount).target_level_value := p_target_rec.org_level_value;
201 END IF;
202 l_dim_levelcount := l_dim_levelcount + 1;
203 x_reseq_target_values(l_dim_levelcount).target_level :=
204 'dimension'||l_dim_levelcount||'_level_value';
205 IF (p_target_rec.time_level_value IS NOT NULL) THEN
206 x_reseq_target_values(l_dim_levelcount).target_level_value :=
207 ''''||p_target_rec.time_level_value||'''';
208 ELSE
209 x_reseq_target_values(l_dim_levelcount).target_level_value := p_target_rec.time_level_value;
210 END IF;
211 l_dim_levelcount := l_dim_levelcount +1;
212 FOR l_count IN l_dim_levelcount..7 LOOP
213 x_reseq_target_values(l_dim_levelcount).target_level :=
214 'dimension'||l_dim_levelcount||'_level_value';
215 x_reseq_target_values(l_Dim_levelcount).target_level_value := NULL;
216 l_dim_levelcount := l_dim_levelcount+1;
217 END LOOP;
218 END IF;
219 x_return_status := 0;
220 EXCEPTION
221 WHEN OTHERS THEN
222 x_return_status := -1;
223 END;
224
225 PROCEDURE MIGRATE_PERFORMANCE_MEASURES
226 (
227 p_measure_short_name IN VARCHAR2 default NULL,
228 x_return_status OUT NOCOPY VARCHAR2
229 )
230 IS
231 CURSOR c_targetlvls IS
232 SELECT target_level_id, indicator_id, short_name
233 ,time_level_id, org_level_id, dimension1_level_id
234 ,dimension2_level_id, dimension3_level_id, dimension4_level_id
235 ,dimension5_level_id
236 FROM bis_target_levels;
237 CURSOR c_targetlvl_values(p_target_level_id IN NUMBER) IS
238 SELECT target_id, org_level_value, time_level_value
239 ,dimension1_level_value, dimension2_level_value, dimension3_level_value
240 ,dimension4_level_value, dimension5_level_value
241 FROM bis_target_values
242 WHERE target_level_id = p_target_level_id;
243 l_target_level_rec BIS_PMF_MIGRATION_PUB.target_level_rec;
244 l_reseq_dims BIS_PMF_MIGRATION_PUB.resequenced_dimensions_Array;
245 l_target_rec BIS_PMF_MIGRATION_PUB.target_rec;
246 l_reseq_target_values BIS_PMF_MIGRATION_PUB.reseq_target_values_arr;
247 l_return_status NUMBER;
248 l_sqlstmt VARCHAR2(32000);
249 l_sqlstmt1 VARCHAR2(32000);
250 l_count NUMBER := 1;
251 l_dummy_value NUMBER := 990;
252 l_dimlevel_count NUMBER;
253 l_dim_count NUMBER;
254 l_dim_levelvalue_count NUMBER;
255 BEGIN
256 --Loop thru the cursor and resequence the dimension levels
257 FOR c_rec IN c_targetlvls LOOP
258 l_target_level_rec.measure_id := c_rec.indicator_id;
259 l_target_level_rec.target_level_id := c_rec.target_level_id;
260 l_target_level_rec.org_levelid := c_rec.org_level_id;
261 l_target_level_rec.time_levelid := c_Rec.time_level_id;
262 l_target_level_rec.dimension1_levelid := c_rec.dimension1_level_id;
263 l_target_level_rec.dimension2_levelid := c_rec.dimension2_level_id;
264 l_Target_level_Rec.dimension3_levelid := c_rec.dimension3_level_id;
265 l_target_level_rec.dimension4_levelid := c_rec.dimension4_level_id;
266 l_target_level_rec.dimension5_levelid := c_rec.dimension5_level_id;
267 l_dimlevel_count := 0;
268 IF (c_rec.org_level_id IS NOT NULL) THEN
269 l_dimlevel_count := l_dimlevel_count+1;
270 END IF;
271 IF (c_rec.time_level_id IS NOT NULL) THEN
272 l_dimlevel_count := l_dimlevel_count+1;
273 END IF;
274 IF (c_rec.dimension1_level_id IS NOT NULL) THEN
275 l_dimlevel_count := l_dimlevel_count+1;
276 END IF;
277 IF (c_rec.dimension2_level_id IS NOT NULL) THEN
278 l_dimlevel_count := l_dimlevel_count+1;
279 END IF;
280 IF (c_rec.dimension3_level_id IS NOT NULL) THEN
281 l_dimlevel_count := l_dimlevel_count+1;
282 END IF;
283 IF (c_rec.dimension4_level_id IS NOT NULL) THEN
284 l_dimlevel_count := l_dimlevel_count+1;
285 END IF;
286 IF (c_rec.dimension5_level_id IS NOT NULL) THEN
287 l_dimlevel_count := l_dimlevel_count+1;
288 END IF;
289 BIS_PMF_MIGRATION_PUB.resequence_dimension_levels
290 (p_target_level_rec => l_target_level_rec
291 ,x_resequenced_dimensions => l_reseq_dims
292 ,x_dim_count => l_dim_count
293 ,x_return_status => l_return_status
294 );
295 l_dummy_value := 990;
296 IF (l_return_status = 0 AND l_reseq_dims.COUNT>0) THEN
297 --First update the dimension sequences
298 FOR l_count IN 1..l_reseq_dims.COUNT LOOP
299 IF (l_reseq_dims(l_count).dim_id IS NULL) THEN
300 EXIT;
301 ELSE
302 UPDATE bis_indicator_dimensions
303 SET sequence_no = l_dummy_value
304 WHERE dimension_id=l_Reseq_dims(l_count).dim_id AND
305 indicator_id=c_rec.indicator_id;
306 END IF;
307 l_dummy_value := l_dummy_value + 1;
308 END LOOP;
309 FOR l_count IN 1..l_reseq_dims.COUNT LOOP
310 IF (l_reseq_dims(l_count).dim_id IS NULL) THEN
311 EXIT;
312 ELSE
313 UPDATE bis_indicator_dimensions
314 SET sequence_no= l_reseq_dims(l_count).seq_no
315 WHERE dimension_id = l_reseq_dims(l_count).dim_id AND
316 indicator_id = c_Rec.indicator_id;
317 END IF;
318 END LOOP;
319 IF (l_dim_count = l_dimlevel_count) THEN
320 l_sqlstmt := 'UPDATE bis_target_levels SET ' ||
321 l_reseq_dims(1).dim_level_col ||' = :1' ||
322 ' , '||l_reseq_dims(2).dim_level_col ||' = :2'||
323 ' , '||l_reseq_dims(3).dim_level_col ||' = :3'||
324 ' , '||l_reseq_dims(4).dim_level_col ||' = :4'||
325 ' , '||l_reseq_dims(5).dim_level_col ||' = :5'||
326 ' , '||l_reseq_dims(6).dim_level_col ||' = :6'||
327 ' , '||l_reseq_dims(7).dim_level_col ||' = :7'||
328 ' WHERE target_level_id =:8';
329 EXECUTE IMMEDIATE l_sqlstmt USING to_char(l_reseq_dims(1).dim_level_col_val), to_char(l_reseq_dims(2).dim_level_col_val),
330 to_char(l_reseq_dims(3).dim_level_col_val), to_char(l_reseq_dims(4).dim_level_col_val), to_char(l_reseq_dims(5).dim_level_col_val),
331 to_char(l_reseq_dims(6).dim_level_col_val), to_char(l_reseq_dims(7).dim_level_col_val),
332 c_rec.target_level_id;
333 l_count := 0;
334 FOR c_targetrec IN c_targetlvl_values(c_rec.target_level_id) LOOP
335 l_count := l_count+1;
336 l_target_rec.org_level_value := c_targetrec.org_level_value;
337 l_target_rec.time_level_value := c_targetrec.time_level_value;
338 l_target_rec.dimension1_level_value := c_targetrec.dimension1_level_value;
339 l_target_rec.dimension2_level_value := c_targetrec.dimension2_level_value;
340 l_target_rec.dimension3_level_value := c_targetrec.dimension3_level_value;
341 l_target_rec.dimension4_level_value := c_targetrec.dimension4_level_value;
342 l_Target_rec.dimension5_level_value := c_targetrec.dimension5_level_value;
343 BIS_PMF_MIGRATION_PUB.resequence_target_level_values(
344 p_target_rec => l_target_rec
345 ,p_dim_count => l_dim_count
346 ,x_reseq_target_values => l_reseq_target_values
347 ,x_return_Status => l_return_status
348 );
349 IF ((l_return_status = 0) AND (l_reseq_target_values.COUNT > 0)) THEN
350 l_sqlstmt1 := 'UPDATE bis_target_values SET '||
351 l_reseq_target_values(1).target_level ||' = :1'||
352 ' ,'|| l_Reseq_target_values(2).target_level ||' = :2' ||
353 ' ,' || l_Reseq_target_values(3).target_level||' = :3'||
354 ' ,' || l_Reseq_target_values(4).target_level||' = :4' ||
355 ' ,' || l_Reseq_target_values(5).target_level||' = :5' ||
356 ' ,' || l_Reseq_target_values(6).target_level||' = :6' ||
357 ' ,' || l_Reseq_target_values(7).target_level||' = :7' ||
358 ' WHERE target_level_id = :8 AND target_id = :9';
359 EXECUTE IMMEDIATE l_sqlstmt1 USING l_reseq_target_values(1).target_level_value, l_reseq_target_values(2).target_level_value,
360 l_reseq_target_values(3).target_level_value, l_reseq_target_values(4).target_level_value, l_reseq_target_values(5).target_level_value,
361 l_reseq_target_values(6).target_level_value, l_reseq_target_values(7).target_level_value, c_rec.target_level_id, c_targetrec.target_id;
362 END IF;
363 END LOOP;
364 END IF;
365 l_count := l_count+1;
366 END IF;
367 END LOOP;
368 COMMIT;
369 x_return_Status := 0;
370 EXCEPTION
371 WHEN OTHERS THEN
372 ROLLBACK;
373 x_return_Status := SQLCODE;
374 END MIGRATE_PERFORMANCE_MEASURES;
375
376 END BIS_PMF_MIGRATION_PUB;
377 -- SHOW ERRORS