1 PACKAGE BODY BSC_UPGRADES AS
2 /* $Header: BSCUPGRB.pls 120.6.12000000.2 2007/06/19 13:33:16 ashankar ship $ */
3 --- Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA
4 --- All rights reserved.
5 ---
6 ---==========================================================================
7 --- FILENAME
8 ---
9 --- BSCPBMSB.pls
10 ---
11 --- DESCRIPTION
12 --- Package body File for Upgrade scripts
13 ---
14 --- NOTES
15 ---
16 --- HISTORY
17 ---
18 --- 26-Jun-2003 mdamle Created
19 --- 22-JUL-2003 Adeulgao modified for bug fix#3047536
20 --- 24-JUL-2003 mdamle Call api to create default calc. here
21 --- 03-SEP-2003 mdamle Do not create db measure col here for PMF
22 --- Do not set measure_col here for PMF
23 --- 13-SEP-2003 mdamle Bug#3134652 - Fix for indicator record not
24 --- being created when migrating from another system.
25 --- 22-SEP-2003 mdamle Bug#3154208 - > 1 dataset pointing to same measure
26 --- 25-SEP-2003 mdamle Bug#3160325 - Sync up measures for all installed languages
27 --- 29-SEP-2003 adrao Bug#3160325 - Sync up measures with source_lang
28 --- 29-SEP-2003 mdamle Bug#3168129 - Change l_pmf_disp_name to bsc_sys_datasets_tl.name%type
29 --- 03-OCT-2003 mdamle Bug#3172902 - Measure ID not being set before synch
30 -- 07-OCT-2003 mdamle Bug#3170184 - For BSC type measure, always use short name in PMF display name
31 -- 04-DEC-2003 kyadamak bug#3284202 - while sync'ing from BSC->PMF generate new short_name
32 -- 22-DEC-2003 meastmon: bug#3333957 - Pass application id 271(Balanced scorecard application Id)
33 -- when creating a BSC dimension level in BIS repository
34 -- 02-MAR-2004 ankgoel bug#3464470 - Forward port fix of bug#3450505
35 -- 31-May-2004 Pradeep Pefromance Bug #3634325 fix
36 -- 09-Sep-2004 ankgoel Bug#3874911 - Rollback & Validation issues in measures sync-up
37 -- 20-Sep-2004 ankgoel Bug#3759819 - Handled measures sync-up for Custom KPIs
38 -- Code modularization and message logging
39 -- 29-Sep-2004 ankgoel Bug#3891748 - Synced-up WHO colums except DATES for measures, dimensions
40 -- and dimension objects
41 -- 05-Oct-2004 ankgoel Bug#3933075 - Moved Upgrade_Role_To_Tabs to BSCUPGNB.pls
42 -- BSCUPGRB.pls will now be used for API calls from bscupmdd.sql only
43 -- 21-DEC-04 vtulasi Modified for bug#4045278 - Addtion of LUD
44 -- 21-FEB-05 ankagarw changed dataset name and description column length for enh# 3862703
45 -- 12-Apr-05 adrao Added APIs Refresh_Measure_Col_Names and Gen_Existing_Measure_Cols
46 -- For Enhancement#4239216
47 -- 19-july-05 kyadamak Modified for the bug#4477575
48 -- 26-Sep-05 ankgoel Bug#4625611 - sync-up dim-dimobject rel from BSC to PMF side
49 --- 10-JAN-06 akoduri Enh#4739401 Hide Dimensions and Dimension Objects
50 --- 24-Jan-06 akoduri Bug#4958055 Dgrp dimension not getting deleted
51 --- while disassociating from objective
52 --- 31-MAR-06 akoduri Bug #5048186 Dropping of BSC Views for obsoleted BIS dimension
53 --- objets (Only those for which the underlying view will be dropped)
54 --- 18-JUN-07 ashankar Bug#6129599 Added the API synch_measures_cds_to_pmf
55 ---===========================================================================
56
57 G_PKG_NAME CONSTANT VARCHAR2(30):='BSC_UPGRADES';
58
59
60 FUNCTION Validate_And_Get_Short_Name
61 (
62 p_Short_Name IN BIS_INDICATORS.short_name%TYPE
63 ) RETURN VARCHAR2;
64 /*******************************************************************************
65 Refresh_Measure_Col_Names API ensures that all the PMF measures that were
66 generated using SHORT_NAME for BSC_SYS_MEASURES.MEASURE_COL is modified to
67 more intelligible names which is ideally derived from the name of the measure.
68
69 These columns that will be generated will ensure that the MEASURE_COL is derived
70 from NAME of the measure uniquely. This API as standalone does not have any
71 impact on the Existing source measure part of the world. This API should be used
72 in combination with Gen_Existing_Measure_Cols to ensure corresponding
73 measure columns are generated for the PMF Measure (Existing SourcE)
74
75 Added as part of Enhancement Bug#4239216
76 ********************************************************************************/
77
78
79 PROCEDURE Refresh_Measure_Col_Names;
80
81
82 /*******************************************************************************
83 This PL/SQL API has been designer to generate new DB Column entries in
84 BSC_DB_MEASURE_COLS_TL table, which will be used by Generate Database to
85 directly run on exisitng type of measures.
86
87 This API should not be called without calling Refresh_Measure_Col_Names,
88 Though this API can run independently, it will generate column that are
89 available directly in BSC_SYS_MEASURES.MEASURE_COL (which ideally may not
90 be intelligible as a TABLE COLUMN). Hence it is *recommened* that
91 the API Refresh_Measure_Col_Names is run before this API is run.
92
93 Added as part of Enhancement Bug#4239216
94 ********************************************************************************/
95
96
97 PROCEDURE Gen_Existing_Measure_Cols;
98
99 PROCEDURE sync_dim_object_mappings
100 ( p_dim_short_name IN VARCHAR2
101 , x_return_status OUT NOCOPY VARCHAR2
102 , x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
103 );
104
105 -- ankgoel: bug#3874911
106 -- Validate and re-generate dataset names untill unique
107 -- This validation is uses the same logic while creating/updating measures
108 -- through UI/ldt. For this, BSC_BIS_MEASURE_PUB.get_Measure_Name is re-used.
109 FUNCTION get_dataset_name(
110 p_dataset_name IN VARCHAR2
111 )
112 RETURN VARCHAR2
113 IS
114 l_dataset_name bsc_sys_datasets_tl.name%TYPE;
115 l_return_name bsc_sys_datasets_tl.name%TYPE;
116 l_flag VARCHAR2(10);
117 BEGIN
118 l_flag := 'F';
119 l_dataset_name := p_dataset_name;
120 l_return_name := p_dataset_name;
121
122 WHILE (l_flag = 'F') LOOP
123 BEGIN
124 -- Validate Measure Name
125 BSC_BIS_MEASURE_PUB.get_Measure_Name( p_dataset_id => NULL
126 , p_ui_flag => 'N'
127 , p_dataset_source => 'PMF'
128 , p_dataset_name => l_dataset_name
129 , x_measure_name => l_return_name);
130 l_flag := 'T';
131 EXCEPTION
132 -- Exception is thrown when the measure name fails during validation
133 -- WHEN FND_API.G_EXC_ERROR THEN
134 WHEN OTHERS THEN
135 l_dataset_name := BSC_UTILITY.get_Next_DispName(l_dataset_name);
136 END;
137 END LOOP;
138
139 RETURN l_return_name;
140 EXCEPTION
141 WHEN OTHERS THEN
142 RETURN p_dataset_name;
143 END get_dataset_name;
144 --
145
146 PROCEDURE delete_bsc_measures_from_pmf
147 IS
148 -- part of bug#3436393: the previous query was not getting all
149 -- the bsc datasets from pmf repository. It was joining with bsc_sys_measures
150 -- but in case of datasets that are formulas between measures
151 -- they were not fetched. We need to join is with bsc_sys_datasets_b
152 CURSOR c_bsc_measures_in_pmf IS
153 SELECT indicator_id, measure_id1, i.short_name
154 FROM bis_indicators i, bsc_sys_datasets_b d
155 WHERE d.dataset_id = i.dataset_id
156 AND d.source = 'BSC';
157
158 CURSOR c_s2e_kpis(p_indicator_id NUMBER) IS
159 SELECT count(1)
160 FROM bis_indicators BIS_IND
161 ,bsc_sys_datasets_b BSC_DTS
162 ,bsc_sys_measures BSC_MEAS
163 WHERE BIS_IND.dataset_id = BSC_DTS.dataset_id
164 AND BSC_DTS.measure_id1 = BSC_MEAS.measure_id
165 AND BIS_IND.indicator_id = p_indicator_id;
166 -- AND BIS_IND.short_name = BSC_MEAS.short_name --kyadamak commented out as this resulted in bad datacorruption 'we should never join by meausre short name'
167
168 TYPE t_array_of_number IS TABLE OF NUMBER
169 INDEX BY BINARY_INTEGER;
170 TYPE t_array_of_varchar2 IS TABLE OF VARCHAR2(100)
171 INDEX BY BINARY_INTEGER;
172
173 l_num_ids NUMBER;
174 l_arr_ids t_array_of_number;
175 l_arr_short_name t_array_of_varchar2;
176 l_max_ds_id_bis NUMBER;
177 l_max_ds_id_bsc NUMBER;
178 l_indicator_id NUMBER;
179 l_count NUMBER;
180 l_bis_measure_rec bis_measure_pub.measure_rec_type;
181 l_return_status VARCHAR2(1);
182 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
183 l_err VARCHAR2(2000);
184 BEGIN
185 l_num_ids := 0;
186
187 SELECT nvl(max(dataset_id),0) INTO l_max_ds_id_bis
188 FROM bis_indicators;
189
190 SELECT nvl(max(dataset_id),0) INTO l_max_ds_id_bsc
191 FROM bsc_sys_datasets_b;
192
193 IF (l_max_ds_id_bis <> l_max_ds_id_bsc) THEN -- dataset ids are out of synch
194 -- remove BSC measures from pmf repository
195 -- Exclude non-corrupt s2e measures.
196 FOR c_meas IN c_bsc_measures_in_pmf LOOP
197 IF (BSC_BIS_CUSTOM_KPI_UTIL_PUB.is_KPI_EndToEnd_Measure(c_meas.short_name) = FND_API.G_TRUE) THEN -- custom KPI
198 OPEN c_s2e_kpis(c_meas.indicator_id);
199 FETCH c_s2e_kpis INTO l_count;
200 IF (l_count = 0) THEN -- custom KPI is corrupt, delete it from PMF side (should not happen ideally)
201 l_num_ids := l_num_ids + 1;
202 l_arr_ids(l_num_ids) := c_meas.indicator_id;
203 l_arr_short_name(l_num_ids) := c_meas.short_name;
204 END IF;
205 CLOSE c_s2e_kpis;
206 ELSE
207 l_num_ids := l_num_ids + 1;
208 l_arr_ids(l_num_ids) := c_meas.indicator_id;
209 l_arr_short_name(l_num_ids) := c_meas.short_name;
210 END IF;
211 END LOOP;
212
213 FOR i IN 1..l_num_ids LOOP
214 BEGIN
215 l_bis_measure_rec.measure_id := l_arr_ids(i);
216
217 BIS_MEASURE_PUB.Delete_Measure(
218 p_api_version => 1.0
219 ,p_commit => FND_API.G_FALSE
220 ,p_Measure_Rec => l_bis_measure_rec
221 ,x_return_status => l_return_status
222 ,x_error_Tbl => l_error_tbl);
223 IF ((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
225 END IF;
226
227 BEGIN
228 BSC_MESSAGE.Add(x_message => 'Deleted measure: ' || l_arr_short_name(i) || ' : Successfully',
229 x_source => 'BSCUPGRB.delete_bsc_measures_from_pmf',
230 x_mode => 'I');
231 EXCEPTION
232 WHEN OTHERS THEN
233 NULL;
234 END;
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 l_err := l_arr_short_name(i) ;
239 IF (l_error_tbl.COUNT > 0) THEN
240 l_err := l_err || ' : ' || l_error_tbl(1).Error_Description;
241 END IF;
242 BSC_MESSAGE.Add(x_message => 'Could not delete measure: ' || l_err,
243 x_source => 'BSCUPGRB.delete_bsc_measures_from_pmf',
244 x_mode => 'I');
245 END;
246 END LOOP;
247
248 COMMIT;
249 END IF;
250 EXCEPTION
251 WHEN OTHERS THEN
252 IF(c_s2e_kpis%ISOPEN) THEN
253 CLOSE c_s2e_kpis;
254 END IF;
255 l_err := SQLERRM;
256 BSC_MESSAGE.Add(x_message => 'Failed: ' || l_err,
257 x_source => 'BSCUPGRB.delete_bsc_measures_from_pmf',
258 x_mode => 'I');
259 END delete_bsc_measures_from_pmf;
260 --
261
262 PROCEDURE sync_measures_pmf_to_bsc
263 IS
264 l_return_status VARCHAR2(1);
265 l_msg_count NUMBER;
266 l_msg_data VARCHAR2(2000);
267 l_Dataset_id NUMBER;
268 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
269
270 TYPE t_array_of_varchar2 IS TABLE OF VARCHAR2(2000)
271 INDEX BY BINARY_INTEGER;
272
273 l_arr_short_name t_array_of_varchar2;
274 l_num NUMBER;
275 i NUMBER;
276 l_err VARCHAR2(2000);
277
278 l_Measure_Col BSC_SYS_MEASURES.MEASURE_COL%TYPE;
279 l_Measure_Group_Id BSC_DB_MEASURE_COLS_TL.MEASURE_GROUP_ID%TYPE;
280 l_Projection_Id BSC_DB_MEASURE_COLS_TL.PROJECTION_ID%TYPE;
281 l_Measure_Type BSC_DB_MEASURE_COLS_TL.MEASURE_TYPE%TYPE;
282 CURSOR bsc_indicators_cursor IS
283 SELECT d.dataset_id, i.name as measure_name, i.description, i.short_name as measure_short_name, i.indicator_id measure_id, i.created_by created_by, i.last_updated_by last_updated_by, i.last_update_login last_update_login
284 FROM bsc_sys_datasets_vl d, bis_indicators_vl i, bsc_sys_measures m
285 WHERE i.short_name = m.short_name (+)
286 and (d.source is null or d.source = 'PMF')
287 and m.measure_id = d.measure_id1 (+)
288 and (i.dataset_id is null or d.dataset_id <> i.dataset_id);
289 BEGIN
290 l_num := 0;
291 -- setup defaults, added for Enhancement#4239216
292 l_Measure_Group_Id := -1; -- default group
293 l_Projection_Id := 0; -- Indicates no projection
294 l_Measure_Type := 1; -- activity type by default
295 -- ankgoel: bug#3874911
296 -- Ideally, in no case should sync-up fail. Still use individual rollback for each measure getting synced-up.
297 FOR icr IN bsc_indicators_cursor LOOP
298 BEGIN
299 SAVEPOINT SP_SYNC_MEASURE;
300 IF (icr.dataset_id is null) then
301 l_Dataset_Rec.Bsc_Source := BSC_BIS_MEASURE_PUB.c_PMF;
302 --l_Dataset_Rec.Bsc_Dataset_Name := icr.measure_name;
303 l_Dataset_Rec.Bsc_Dataset_Name := get_dataset_name(icr.measure_name);
304 l_Dataset_Rec.Bsc_Dataset_Help := icr.description;
305 l_Dataset_Rec.Bsc_Measure_Short_Name := icr.measure_short_name;
306 l_Dataset_Rec.Bsc_Measure_Long_Name := icr.measure_name;
307 l_Dataset_Rec.Bsc_Measure_Operation := BSC_BIS_MEASURE_PUB.c_SUM;
308 -- ankgoel: bug#3891748 - Creation_Date and Last_Update_Date will not be synced-up
309 -- They might be useful in debugging
310 l_Dataset_Rec.Bsc_Measure_Created_By := icr.created_by;
311 l_Dataset_Rec.Bsc_Measure_Last_Update_By := icr.last_updated_by;
312 l_Dataset_Rec.Bsc_Measure_Last_Update_Login := icr.last_update_login;
313 l_Dataset_Rec.Bsc_Dataset_Created_By := icr.created_by;
314 l_Dataset_Rec.Bsc_Dataset_Last_Update_By := icr.last_updated_by;
315 l_Dataset_Rec.Bsc_Dataset_Last_Update_Login := icr.last_update_login;
316
317 -- added for Enhancement#4239216
318 l_Measure_Col := BSC_BIS_MEASURE_PUB.Get_Measure_Col(
319 l_Dataset_Rec.Bsc_Dataset_Name,
320 l_Dataset_Rec.Bsc_Source,
321 NULL,
322 l_Dataset_Rec.Bsc_Measure_Short_Name
323 );
324 l_Dataset_Rec.Bsc_Measure_Col := l_Measure_Col;
325 BSC_DATASETS_PUB.Create_Measures(
326 p_commit => FND_API.G_FALSE
327 ,p_Dataset_Rec => l_Dataset_Rec
328 ,x_Dataset_Id => l_Dataset_Id
329 ,x_return_status => l_return_status
330 ,x_msg_count => l_msg_count
331 ,x_msg_data => l_msg_data);
332 IF ((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
334 END IF;
335
336 l_Dataset_Rec.Bsc_Dataset_Id := l_Dataset_Id;
337 BSC_DATASETS_PUB.Create_Dataset_Calc(FND_API.G_FALSE
338 ,l_Dataset_Rec
339 ,l_return_status
340 ,l_msg_count
341 ,l_msg_data);
342 IF ((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
344 END IF;
345 -- added for Enhancement#4239216
346 BEGIN
347 IF (l_Measure_Col IS NOT NULL) THEN
348 BSC_DB_MEASURE_COLS_PKG.INSERT_ROW (
349 x_Measure_Col => l_Measure_Col
350 , x_Measure_Group_Id => l_Measure_Group_Id
351 , x_Projection_Id => l_Projection_Id
352 , x_Measure_Type => l_Measure_Type
353 , x_Help => l_Measure_Col
354 );
355 END IF;
356 EXCEPTION
357 WHEN OTHERS THEN
358 BSC_MESSAGE.Add (
359 x_message => SQLERRM || ' - ERROR ADDING COL : ' || l_Measure_Col
360 , x_source => 'BSC_DB_MEASURE_COLS_PKG.INSERT_ROW'
361 , x_mode => 'I'
362 );
363 END;
364 UPDATE bis_indicators
365 SET dataset_id = l_Dataset_id
366 WHERE indicator_id = icr.measure_id;
367
368 -- mdamle 09/25/2003 - Sync up measures for all installed languages
369 lang_synch_PMF_To_BSC_measure(
370 p_indicator_id => icr.measure_id
371 , p_Dataset_Rec => l_dataset_rec
372 , x_return_status => l_return_status
373 , x_msg_count => l_msg_count
374 , x_msg_data => l_msg_data);
375
376 ELSE
377 UPDATE bis_indicators
378 SET dataset_id = icr.dataset_id
379 WHERE indicator_id = icr.measure_id;
380 END IF;
381
382 BEGIN
383 BSC_MESSAGE.Add(x_message => 'Synchronized measure: ' || icr.measure_short_name || ' : ' || 'Successfully',
384 x_source => 'BSCUPGRB.sync_measures_pmf_to_bsc',
385 x_mode => 'I');
386 EXCEPTION
387 WHEN OTHERS THEN
388 NULL;
389 END;
390
391 COMMIT;
392 EXCEPTION
393 WHEN OTHERS THEN
394 l_num := l_num + 1;
395 IF (l_msg_data IS NULL) THEN
396 FND_MSG_PUB.Count_And_Get (
397 p_encoded => FND_API.G_FALSE
398 , p_count => l_msg_count
399 , p_data => l_msg_data );
400 END IF;
401 l_arr_short_name(l_num) := icr.measure_short_name || ' : ' || l_msg_data;
402 ROLLBACK TO SP_SYNC_MEASURE;
403 END;
404 END LOOP;
405
406 BEGIN
407 FOR i IN 1..l_num LOOP
408 BSC_MESSAGE.Add(x_message => 'Failed measure: ' || l_arr_short_name(i),
409 x_source => 'BSCUPGRB.sync_measures_pmf_to_bsc',
410 x_mode => 'I');
411 END LOOP;
412 EXCEPTION
413 WHEN OTHERS THEN
414 NULL;
415 END;
416
417 COMMIT;
418 EXCEPTION
419 WHEN OTHERS THEN
420 l_err := SQLERRM;
421 BSC_MESSAGE.Add(x_message => 'Failed: ' || l_err,
422 x_source => 'BSCUPGRB.sync_measures_pmf_to_bsc',
423 x_mode => 'I');
424 END sync_measures_pmf_to_bsc;
425 --
426
427 PROCEDURE sync_measures_bsc_to_pmf_51
428 IS
429 l_return_status varchar2(1);
430 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
431 l_measure_rec BIS_MEASURE_PUB.Measure_rec_type;
432
433 TYPE t_array_of_varchar2 IS TABLE OF VARCHAR2(2000)
434 INDEX BY BINARY_INTEGER;
435
436 l_arr_short_name t_array_of_varchar2;
437 l_num NUMBER;
438 i NUMBER;
439 l_err VARCHAR2(2000);
440 l_Count NUMBER;
441 l_Short_Name BIS_INDICATORS.short_name%TYPE;
442
443 CURSOR bsc_datasets_cursor_1 IS
444 SELECT d.dataset_id, name, m.measure_id, m.created_by created_by, m.last_updated_by last_updated_by, m.last_update_login last_update_login
445 FROM bsc_sys_datasets_vl d, bsc_sys_measures m, bisbv_performance_measures pm
446 WHERE d.measure_id1 = m.measure_id
447 AND m.short_name is null
448 AND d.source = 'BSC'
449 AND d.dataset_id = pm.dataset_id (+)
450 AND pm.dataset_id is null;
451 BEGIN
452 l_num := 0;
453 FOR cr IN bsc_datasets_cursor_1 LOOP
454 BEGIN
455 SAVEPOINT SP_SYNC_MEASURE;
456 l_measure_rec.Measure_Short_Name := BSC_BIS_MEASURE_PUB.c_PMD||cr.dataset_id;
457
458 /**************************************************************************
459 We need to validate that the Short Name we are going to create already exists
460 in PMF data model.If yes then we have to suffix the short_name with A or B
461 We already faced the problem when we did the migration from BSCUPG9 environment
462 to BSCUPG19 environment.
463 For more info on what actaully happened visit
464
465 http://files.oraclecorp.com/content/MySharedFolders/E-BI%20Core%20Status%20Rep
466 orts/Development/Vinod%20Bansal/Sudharsan%20Krishnamurthy%20%28Kris%29/Sathis%
467 20Kumar/Ravi%20shankar/MIGRATIONISSUE/MigrationIssue.doc
468 /**************************************************************************/
469 l_measure_rec.Measure_Short_Name := Validate_And_Get_Short_Name(p_Short_Name => l_measure_rec.Measure_Short_Name);
470
471 -- mdamle 10/07/2003 - Bug#3170184 - For BSC type measure, always use short name in PMF display name
472 l_measure_rec.Measure_name := l_measure_rec.Measure_short_name;
473
474 l_measure_rec.Application_Id := 271;
475 l_measure_rec.Dataset_id := cr.dataset_id;
476 -- ankgoel: bug#3891748 - Creation_Date and Last_Update_Date will not be synced-up
477 -- They might be useful in debugging
478 l_measure_rec.Created_By := cr.created_by;
479 l_measure_rec.Last_Updated_By := cr.last_updated_by;
480 l_measure_rec.Last_Update_Login := cr.last_update_login;
481
482 BIS_MEASURE_PUB.Create_Measure(
483 p_api_version => 1.0
484 ,p_commit => FND_API.G_FALSE
485 ,p_Measure_Rec => l_measure_rec
486 ,p_owner => FND_LOAD_UTIL.OWNER_NAME(cr.created_by)
487 ,x_return_status => l_return_status
488 ,x_error_tbl => l_error_tbl);
489 IF ((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
490 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
491 END IF;
492
493 UPDATE bsc_sys_measures
494 SET short_name = l_measure_rec.Measure_Short_Name
495 WHERE measure_id = cr.measure_id;
496
497 BEGIN
498 BSC_MESSAGE.Add(x_message => 'Synchronized measure: ' || l_measure_rec.Measure_short_name || ' : ' || 'Successfully',
499 x_source => 'BSCUPGRB.sync_measures_bsc_to_pmf_51',
500 x_mode => 'I');
501 EXCEPTION
502 WHEN OTHERS THEN
503 NULL;
504 END;
505
506 COMMIT;
507 EXCEPTION
508 WHEN OTHERS THEN
509 l_num := l_num + 1;
510 l_err := l_measure_rec.Measure_Short_Name;
511 IF (l_error_tbl.COUNT > 0) THEN
512 l_err := l_err || ' : ' || l_error_tbl(1).Error_Description;
513 END IF;
514 l_arr_short_name(l_num) := l_err;
515 ROLLBACK TO SP_SYNC_MEASURE;
516 END;
517 END LOOP;
518
519 BEGIN
520 FOR i IN 1..l_num LOOP
521 BSC_MESSAGE.Add(x_message => 'Failed measure: ' || l_arr_short_name(i),
522 x_source => 'BSCUPGRB.sync_measures_bsc_to_pmf_51',
523 x_mode => 'I');
524 END LOOP;
525 EXCEPTION
526 WHEN OTHERS THEN
527 NULL;
528 END;
529
530 COMMIT;
531 EXCEPTION
532 WHEN OTHERS THEN
533 l_err := SQLERRM;
534 BSC_MESSAGE.Add(x_message => 'Failed: ' || l_err,
535 x_source => 'BSCUPGRB.sync_measures_bsc_to_pmf_51',
536 x_mode => 'I');
537 END sync_measures_bsc_to_pmf_51;
538
539
540
541 PROCEDURE synch_measures_cds_to_pmf
542 IS
543 l_return_status varchar2(1);
544 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
545 l_measure_rec BIS_MEASURE_PUB.Measure_rec_type;
546 l_count NUMBER;
547 l_Kpi_Id NUMBER;
548
549 TYPE t_array_of_varchar2 IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
550
551 l_arr_short_name t_array_of_varchar2;
552 l_num NUMBER;
553 i NUMBER;
554 l_err VARCHAR2(2000);
555
556 CURSOR cds_datasets_cursor IS
557 SELECT d.dataset_id, m.short_name
558 FROM bsc_sys_datasets_vl d, bsc_sys_measures m, bisbv_performance_measures pm
559 WHERE d.measure_id1 = m.measure_id
560 AND m.short_name = pm.measure_short_name (+)
561 AND m.short_name IS NOT NULL
562 AND d.source = 'CDS'
563 AND (pm.dataset_id IS NULL);
564
565 BEGIN
566 --For CDS type of measures we need to check if that measure id
567 -- exits in bis_indicators or not.if the measure exists and its dataset_id
568 -- is set to NULL then we need to populate
569 l_num := 0;
570 FOR cd IN cds_datasets_cursor LOOP
571 BEGIN
572 SAVEPOINT SP_SYNC_CDS_MEASURE;
573 IF(cd.short_name IS NOT NULL)THEN
574
575 UPDATE bis_indicators
576 SET dataset_id =cd.dataset_id
577 WHERE short_name =cd.short_name;
578
579 END IF;
580 EXCEPTION
581 WHEN OTHERS THEN
582 l_num := l_num + 1;
583 l_err := cd.Short_Name;
584 IF (l_error_tbl.COUNT > 0) THEN
585 l_err := l_err || ' : ' || l_error_tbl(1).Error_Description;
586 END IF;
587 l_arr_short_name(l_num) := l_err;
588 ROLLBACK TO SP_SYNC_CDS_MEASURE;
589 END;
590 END LOOP;
591 --LOG THE Measures which were failed
592 BEGIN
593 FOR i IN 1..l_num LOOP
594 BSC_MESSAGE.Add(x_message => 'Failed measure: ' || l_arr_short_name(i),
595 x_source => 'BSCUPGRB.synch_measures_cds_to_pmf',
596 x_mode => 'I');
597 END LOOP;
598 EXCEPTION
599 WHEN OTHERS THEN
600 NULL;
601 END;
602 --commit the changes
603
604 COMMIT;
605 EXCEPTION
606 WHEN OTHERS THEN
607 l_err := SQLERRM;
608 BSC_MESSAGE.Add(x_message => 'Failed: ' || l_err,
609 x_source => 'BSCUPGRB.synch_measures_cds_to_pmf',
610 x_mode => 'I');
611
612 END synch_measures_cds_to_pmf;
613
614
615
616 --
617
618 PROCEDURE sync_measures_bsc_to_pmf
619 IS
620 l_return_status varchar2(1);
621 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
622 l_measure_rec BIS_MEASURE_PUB.Measure_rec_type;
623 l_count NUMBER;
624 l_Kpi_Id NUMBER;
625
626 TYPE t_array_of_varchar2 IS TABLE OF VARCHAR2(2000)
627 INDEX BY BINARY_INTEGER;
628
629 l_arr_short_name t_array_of_varchar2;
630 l_num NUMBER;
631 i NUMBER;
632 l_err VARCHAR2(2000);
633
634 CURSOR bsc_datasets_cursor_2 IS
635 SELECT d.dataset_id, name, m.short_name, m.measure_id, pm.measure_short_name, m.created_by created_by, m.last_updated_by last_updated_by, m.last_update_login last_update_login
636 FROM bsc_sys_datasets_vl d, bsc_sys_measures m, bisbv_performance_measures pm
637 WHERE d.measure_id1 = m.measure_id
638 AND m.short_name = pm.measure_short_name (+)
639 AND m.short_name IS NOT NULL
640 AND d.source = 'BSC'
641 AND (pm.dataset_id IS NULL OR pm.dataset_id <> d.dataset_id);
642 BEGIN
643 l_num := 0;
644 -- If measure already exists, then just update the dataset_id
645 FOR cr IN bsc_datasets_cursor_2 LOOP
646 BEGIN
647 SAVEPOINT SP_SYNC_MEASURE;
648 IF cr.measure_short_name is not null then
649 -- mdamle 09/22/2003 - First check if the dataset already exists in bis_indicators
650 -- When more than 1 dataset points to the same measure, the short_name in measures
651 -- will match with only one of the datasets.
652 -- If these datasets were already synchronized above, then there would be a record
653 -- in bis_indicators for every dataset. Hence, added the check here so that
654 -- update is done only if that dataset id is not already set in bis_indicators
655 SELECT count(1) INTO l_count
656 FROM BISBV_PERFORMANCE_MEASURES
657 WHERE dataset_id = cr.dataset_id;
658
659 IF (l_count = 0) THEN
660 UPDATE BIS_INDICATORS
661 SET dataset_id = cr.dataset_id
662 WHERE short_name = cr.measure_short_name;
663 END IF;
664 ELSE
665 l_measure_rec.Measure_Short_Name := BSC_BIS_MEASURE_PUB.c_PMD||cr.dataset_id;
666 /****************************************************************************
667 One more thing we have to notive here is that short_names for BSC measures
668 will not be same in both the data models.
669 Its possible that the same measure can be attached to many datasets.
670 So in that case the short_name at BSC end will be different and that at PMF end will
671 be different.
672
673 But hold on ,they wil have the same dataset_id.
674 So while making a join between both the tables we have to take into account
675 dataset_id.
676 /****************************************************************************/
677 l_measure_rec.Measure_Short_Name := Validate_And_Get_Short_Name(p_Short_Name => l_measure_rec.Measure_Short_Name);
678
679 -- mdamle 10/07/2003 - Bug#3170184 - For BSC type measure, always use short name in PMF display name
680 l_measure_rec.Measure_name := l_measure_rec.Measure_short_name;
681
682 l_measure_rec.Application_Id := 271;
683 l_measure_rec.dataset_id := cr.dataset_id;
684 -- ankgoel: bug#3891748 - Creation_Date and Last_Update_Date will not be synced-up
685 -- They might ne useful in debugging
686 l_measure_rec.Created_By := cr.created_by;
687 l_measure_rec.Last_Updated_By := cr.last_updated_by;
688 l_measure_rec.Last_Update_Login := cr.last_update_login;
689
690 -- Get the actual_data_source, actual_data_source_type and function name for custom KPIs
691 IF (BSC_BIS_CUSTOM_KPI_UTIL_PUB.is_KPI_EndToEnd_Measure(cr.Measure_Id) = FND_API.G_TRUE) THEN
692 BEGIN
693 BSC_BIS_CUSTOM_KPI_UTIL_PUB.Get_Pmf_Metadata_By_Objective (
694 p_Dataset_Id => cr.dataset_Id
695 , p_Measure_Short_Name => cr.short_name
696 , x_Actual_Source_Type => l_measure_rec.Actual_Data_Source_Type
697 , x_Actual_Source => l_measure_rec.Actual_Data_Source
698 , x_Function_Name => l_measure_rec.Function_Name
699 );
700 -- enable to report
701 l_measure_rec.Enable_Link := 'Y';
702 EXCEPTION
703 WHEN OTHERS THEN
704 BSC_MESSAGE.Add(x_message => 'Custom KPI: ' || cr.Short_Name || ' : ' || 'Failed to get PMF Metadata',
705 x_source => 'BSCUPGRB.sync_measures_bsc_to_pmf',
706 x_mode => 'I');
707 END;
708 END IF;
709
710 BIS_MEASURE_PUB.Create_Measure(
711 p_api_version => 1.0
712 ,p_commit => FND_API.G_FALSE
713 ,p_Measure_Rec => l_measure_rec
714 ,p_owner => FND_LOAD_UTIL.OWNER_NAME(cr.created_by)
715 ,x_return_status => l_return_status
716 ,x_error_tbl => l_error_tbl);
717 IF ((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
719 END IF;
720
721 UPDATE BSC_SYS_MEASURES
722 SET short_name = l_measure_rec.Measure_Short_Name
723 WHERE measure_id = cr.measure_id;
724 END IF;
725
726 BEGIN
727 BSC_MESSAGE.Add(x_message => 'Synchronized measure: ' || cr.Short_Name || ' : ' || 'Successfully',
728 x_source => 'BSCUPGRB.sync_measures_bsc_to_pmf',
729 x_mode => 'I');
730 EXCEPTION
731 WHEN OTHERS THEN
732 NULL;
733 END;
734
735 COMMIT;
736 EXCEPTION
737 WHEN OTHERS THEN
738 l_num := l_num + 1;
739 l_err := cr.Short_Name;
740 IF (l_error_tbl.COUNT > 0) THEN
741 l_err := l_err || ' : ' || l_error_tbl(1).Error_Description;
742 END IF;
743 l_arr_short_name(l_num) := l_err;
744 ROLLBACK TO SP_SYNC_MEASURE;
745 END;
746 END LOOP;
747
748 BEGIN
749 FOR i IN 1..l_num LOOP
750 BSC_MESSAGE.Add(x_message => 'Failed measure: ' || l_arr_short_name(i),
751 x_source => 'BSCUPGRB.sync_measures_bsc_to_pmf',
752 x_mode => 'I');
753 END LOOP;
754 EXCEPTION
755 WHEN OTHERS THEN
756 NULL;
757 END;
758
759 COMMIT;
760 EXCEPTION
761 WHEN OTHERS THEN
762 l_err := SQLERRM;
763 BSC_MESSAGE.Add(x_message => 'Failed: ' || l_err,
764 x_source => 'BSCUPGRB.sync_measures_bsc_to_pmf',
765 x_mode => 'I');
766 END sync_measures_bsc_to_pmf;
767 --
768 /***************************************************************************************************
769
770 if the short name sent already exists in bsc_sys_measures it gets time based unique short_name
771
772 ***************************************************************************************************/
773 FUNCTION getUniqueShtNameSysMeasure(p_Short_Name IN VARCHAR2)
774 RETURN VARCHAR2
775 IS
776 l_count NUMBER;
777 BEGIN
778 SELECT COUNT(1)
779 INTO l_count
780 FROM BSC_SYS_MEASURES
781 WHERE SHORT_NAME = p_Short_Name;
782
783 IF(l_count > 0) THEN
784 RETURN bsc_utility.Get_Unique_Sht_Name_By_Obj_Typ(p_Object_Type => bsc_utility.c_BSC_MEASURE);
785 ELSE
786 RETURN p_Short_Name;
787 END IF;
788 END getUniqueShtNameSysMeasure;
789
790 /***************************************************************************************************/
791
792
793
794 /***************************************************************************************************
795 Added for the bug#4477575
796 This function populates short_name in bsc_sys_measures if it is null.
797 we found that there may be case where short_name can be null after syncup is run as migration
798 is putting null for all bsc_short_names
799 ***************************************************************************************************/
800 PROCEDURE update_short_name_bsc_sys_mes
801 IS
802 CURSOR cBscSysMeasures IS
803 SELECT M.short_name bscShortName
804 ,B.short_name bisShortName
805 ,B.dataset_id
806 ,M.measure_id
807 FROM bsc_sys_measures M
808 ,bsc_sys_datasets_vl V
809 ,bis_indicators_Vl B
810 WHERE M.measure_id = V.measure_id1
811 AND B.dataset_id = V.dataset_id
812 AND V.SOURCE = 'BSC'
813 AND M.short_name IS NULL;
814
815 l_Measure_Short_Name BIS_INDICATORS.SHORT_NAME%TYPE;
816 l_Bsc_Measure_Short_Name BSC_SYS_MEASURES.SHORT_NAME%TYPE;
817 BEGIN
818 SAVEPOINT SP_UPDATE_SHORT_NAME;
819 FOR cBSCM IN cBscSysMeasures LOOP
820 l_Bsc_Measure_Short_Name := getUniqueShtNameSysMeasure(p_Short_Name => cBSCM.bisShortName);
821
822 UPDATE bsc_sys_measures
823 SET short_name = l_Bsc_Measure_Short_Name
824 WHERE measure_id = cBSCM.measure_id;
825
826 END LOOP;
827 COMMIT;
828 EXCEPTION
829 WHEN OTHERS THEN
830 ROLLBACK TO SP_UPDATE_SHORT_NAME;
831 BSC_MESSAGE.Add (
832 x_message => SQLERRM
833 , x_source => 'Update short names in bsc_sys_measures'
834 , x_mode => 'I'
835 );
836 END update_short_name_bsc_sys_mes;
837 /**********************************************************************************************/
838
839 FUNCTION synchronize_measures(
840 x_error_msg OUT NOCOPY VARCHAR2
841 ) return boolean is
842
843 l_return_status varchar2(1);
844 l_msg_count number;
845 l_msg_data varchar2(2000);
846 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
847
848 BEGIN
849
850 -- meastmon bug#3379110 --------------------------------------------------------------------
851 -- If the dataset_ids from BIS_INDICATORS and BSC_SYS_DATASETS_VL are not in synch
852 -- we are going to:
853 -- 1. Remove BSC measures from PMF repository
854 -- 2. Update all the dataset_ids to NULL.
855 -- Then synchronization will refresh the dataset_id in BIS_INDICATORS with the correct value
856 -- from BSC_SYS_DATASETS_VL
857 delete_bsc_measures_from_pmf;
858
859 -- For every PMF record that does not exist in bsc_sys_datasets, create one in bsc_sys_datasets and bsc_sys_measures
860 sync_measures_pmf_to_bsc;
861
862
863 -- For every dataset, create a record in bis_indicator table.
864 -- Short Name is null (for Pre-51 release measures)
865 sync_measures_bsc_to_pmf_51;
866
867
868 -- For every dataset, create a record in bis_indicator table.
869 -- For post-51 release migration from another db - short_name is not null
870 sync_measures_bsc_to_pmf;
871
872 --Modified for the bug#4477575
873 update_short_name_bsc_sys_mes;
874
875 --For CDS type of measures we will check only the dataset_id is populated
876 -- or not. if not then we will update it.It will happen during migration
877 -- process.
878 synch_measures_cds_to_pmf;
879
880 -- mdamle 09/25/2003 - Sync up measures for all installed languages
881 -- This is used to fix language data that was generated before the synchronize routines were added in
882 lang_synch_existing_measures(
883 x_msg_count => l_msg_count
884 , x_msg_data => l_msg_data
885 , x_return_status => l_return_status
886 , x_error_tbl => l_error_tbl);
887
888 -- mdamle 09/25/2003 - BIS messages need to be added to the FND stack
889 --Added for Enhancement#4239216
890 Refresh_Measure_Col_Names;
891 --Added for Enhancement#4239216
892 Gen_Existing_Measure_Cols;
893 BSC_UTILITY.Add_To_Fnd_Msg_Stack(
894 p_error_tbl => l_error_tbl
895 ,x_return_status => l_return_status
896 ,x_msg_count => l_msg_count
897 ,x_msg_data => l_msg_data);
898
899 IF (l_return_status IS NOT NULL AND l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
900 RAISE FND_API.G_EXC_ERROR;
901 END IF;
902
903 COMMIT;
904 RETURN TRUE;
905
906 EXCEPTION
907 WHEN OTHERS THEN
908 ROLLBACK;
909 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
910 ,p_data => l_msg_data);
911 IF (l_msg_data IS NULL) THEN
912 l_msg_data := SQLERRM;
913 END IF;
914 x_error_msg := l_msg_data;
915 RETURN FALSE;
916 END synchronize_measures;
917
918
919 -- mdamle 09/25/2003 - Sync up measures for all installed languages
920 -- ashankar: bug#390429 - Used by bscup.sql in create_template process
921 Procedure lang_synch_BSC_To_PMF_measure(
922 p_dataset_id IN NUMBER := NULL
923 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
924 , x_return_status OUT NOCOPY VARCHAR2
925 , x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type)
926 is
927
928 cursor installed_languages_cursor is
929 select L.language_code
930 from FND_LANGUAGES L
931 where L.INSTALLED_FLAG in ('I', 'B')
932 and language_code <> userenv('LANG');
933
934
935 l_name bsc_sys_datasets_tl.name%type;
936 l_help bsc_sys_datasets_tl.help%type;
937 l_source_lang bsc_sys_datasets_tl.source_lang%type;
938 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
939 l_count number;
940
941 BEGIN
942
943 l_measure_rec := p_measure_rec;
944 for cr in installed_languages_cursor loop
945 select name, help, source_lang into l_name, l_help, l_source_lang
946 from bsc_sys_datasets_tl
947 where dataset_id = p_dataset_id
948 and language = cr.language_code;
949
950 l_measure_rec.measure_name := l_name;
951 l_measure_rec.description := l_help;
952
953 -- mdamle 10/07/2003 - Bug#3170184 - For BSC type measure, always use short name in PMF display name
954 l_measure_rec.Measure_name := l_measure_rec.Measure_short_name;
955
956 BIS_MEASURE_PUB.Translate_Measure_By_lang
957 ( p_api_version => 1.0
958 , p_commit => FND_API.G_FALSE
959 , p_Measure_Rec => l_Measure_Rec
960 , p_lang => cr.language_code
961 , p_source_lang => l_source_lang
962 , x_return_status => x_return_status
963 , x_error_Tbl => x_error_tbl
964 );
965 end loop;
966
967 if installed_languages_cursor%ISOPEN THEN
968 CLOSE installed_languages_cursor;
969 end if;
970
971 EXCEPTION
972 WHEN FND_API.G_EXC_ERROR THEN
973 x_return_status := FND_API.G_RET_STS_ERROR;
974 if installed_languages_cursor%ISOPEN THEN
975 CLOSE installed_languages_cursor;
976 end if;
977 RAISE;
978 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
980 if installed_languages_cursor%ISOPEN THEN
981 CLOSE installed_languages_cursor;
982 end if;
983 RAISE;
984 WHEN NO_DATA_FOUND THEN
985 x_return_status := FND_API.G_RET_STS_ERROR;
986 if installed_languages_cursor%ISOPEN THEN
987 CLOSE installed_languages_cursor;
988 end if;
989 RAISE;
990 WHEN OTHERS THEN
991 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
992 if installed_languages_cursor%ISOPEN THEN
993 CLOSE installed_languages_cursor;
994 end if;
995 RAISE;
996
997 END lang_synch_BSC_To_PMF_measure;
998
999
1000
1001 -- mdamle 09/25/2003 - Sync up measures for all installed languages
1002 Procedure lang_synch_PMF_To_BSC_measure(
1003 p_indicator_id IN NUMBER -- := NULL
1004 , p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
1005 , x_return_status OUT NOCOPY VARCHAR2
1006 , x_msg_count OUT NOCOPY NUMBER
1007 , x_msg_data OUT NOCOPY VARCHAR2)
1008 is
1009
1010 cursor installed_languages_cursor is
1011 select L.language_code
1012 from FND_LANGUAGES L
1013 where L.INSTALLED_FLAG in ('I', 'B')
1014 and language_code <> userenv('LANG');
1015
1016 l_name bis_indicators_tl.name%type;
1017 l_description bis_indicators_tl.description%type;
1018 l_measure_name bis_indicators_tl.name%type;
1019 l_source_lang bis_indicators_tl.source_lang%type;
1020 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
1021 BEGIN
1022
1023 l_dataset_rec := p_dataset_rec;
1024
1025 SELECT name INTO l_measure_name
1026 FROM bis_indicators_tl
1027 WHERE indicator_id = p_indicator_id
1028 AND language = userenv('LANG');
1029
1030 for cr in installed_languages_cursor loop
1031 select name, description, source_lang
1032 into l_name, l_description, l_source_lang
1033 from bis_indicators_tl
1034 where indicator_id = p_indicator_id
1035 and language = cr.language_code;
1036
1037 -- ankgoel: bug#3874911
1038 -- Get the name from BIS end only when it's not changed during validations
1039 IF (l_dataset_rec.Bsc_Dataset_Name = l_measure_name) THEN
1040 l_dataset_rec.Bsc_Dataset_Name := l_name;
1041 END IF;
1042 l_dataset_rec.Bsc_Dataset_help := l_description;
1043
1044 BSC_DATASETS_PUB.Translate_Measure_By_Lang
1045 ( p_commit => FND_API.G_FALSE
1046 , p_Dataset_Rec => l_Dataset_Rec
1047 , p_lang => cr.language_code
1048 , p_source_lang => l_source_lang
1049 , x_return_status => x_return_status
1050 , x_msg_count => x_msg_count
1051 , x_msg_data => x_msg_data);
1052
1053 end loop;
1054
1055 if installed_languages_cursor%ISOPEN THEN
1056 CLOSE installed_languages_cursor;
1057 end if;
1058
1059 EXCEPTION
1060 WHEN FND_API.G_EXC_ERROR THEN
1061 x_return_status := FND_API.G_RET_STS_ERROR;
1062 if installed_languages_cursor%ISOPEN THEN
1063 CLOSE installed_languages_cursor;
1064 end if;
1065 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1066 ,p_data => x_msg_data);
1067 RAISE;
1068 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1070 if installed_languages_cursor%ISOPEN THEN
1071 CLOSE installed_languages_cursor;
1072 end if;
1073 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1074 ,p_data => x_msg_data);
1075 RAISE;
1076 WHEN NO_DATA_FOUND THEN
1077 x_return_status := FND_API.G_RET_STS_ERROR;
1078 if installed_languages_cursor%ISOPEN THEN
1079 CLOSE installed_languages_cursor;
1080 end if;
1081 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1082 ,p_data => x_msg_data);
1083 RAISE;
1084 WHEN OTHERS THEN
1085 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1086 if installed_languages_cursor%ISOPEN THEN
1087 CLOSE installed_languages_cursor;
1088 end if;
1089 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1090 ,p_data => x_msg_data);
1091 RAISE;
1092
1093 END lang_synch_PMF_To_BSC_measure;
1094
1095
1096 -- mdamle 09/25/2003 - Sync up measures for all installed languages
1097 Procedure lang_synch_existing_measures(
1098 x_msg_count OUT NOCOPY NUMBER
1099 , x_msg_data OUT NOCOPY VARCHAR2
1100 , x_return_status OUT NOCOPY VARCHAR2
1101 , x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type)
1102 is
1103
1104 cursor synch_lang_cursor is
1105 select i.indicator_id, itl.language, itl.source_lang, i.short_name, d.source, d.dataset_id,
1106 itl.name indicator_name, dtl.name dataset_name,
1107 itl.description, dtl.help
1108 from bis_indicators i, bis_indicators_tl itl, bsc_sys_datasets_b d, bsc_sys_datasets_tl dtl
1109 where i.indicator_id = itl.indicator_id
1110 and i.dataset_id = dtl.dataset_id
1111 and d.dataset_id = dtl.dataset_id
1112 and itl.language = dtl.language
1113 and itl.name <> dtl.name;
1114
1115 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
1116 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
1117 l_measure_name BIS_INDICATORS_TL.Name%TYPE;
1118 l_count number;
1119 BEGIN
1120
1121 for scr in synch_lang_cursor loop
1122 if scr.source = BSC_BIS_MEASURE_PUB.c_BSC then
1123 -- ankgoel: Since name is same as short name and will not get
1124 -- re-generated unlike PMF to BSC sync-up, no conditions reqd. here
1125 l_measure_rec.measure_id := scr.indicator_id;
1126 l_measure_rec.description := scr.help;
1127
1128 -- mdamle 10/07/2003 - Bug#3170184 - For BSC type measure, always use short name in PMF display name
1129 l_measure_rec.Measure_name := scr.short_name;
1130
1131 BIS_MEASURE_PUB.Translate_Measure_By_lang
1132 ( p_api_version => 1.0
1133 , p_commit => FND_API.G_FALSE
1134 , p_Measure_Rec => l_Measure_Rec
1135 , p_lang => scr.language
1136 , p_source_lang => scr.source_lang
1137 , x_return_status => x_return_status
1138 , x_error_Tbl => x_error_tbl
1139 );
1140 else
1141 SELECT name INTO l_measure_name
1142 FROM bis_indicators_tl
1143 WHERE indicator_id = scr.indicator_id
1144 AND language = userenv('LANG');
1145
1146 l_dataset_rec.bsc_dataset_id := scr.dataset_id;
1147
1148 -- ankgoel: bug#3874911
1149 -- Get the name from BIS end only when it's not changed during validations
1150 IF(l_dataset_rec.Bsc_Dataset_Name = l_measure_name) THEN
1151 l_dataset_rec.bsc_dataset_name := scr.indicator_name;
1152 END IF;
1153
1154 if (scr.description is null) then
1155 l_dataset_rec.bsc_dataset_help := l_dataset_rec.bsc_dataset_name;
1156 else
1157 l_dataset_rec.bsc_dataset_help := scr.description;
1158 end if;
1159
1160 BSC_DATASETS_PUB.Translate_Measure_By_Lang
1161 ( p_commit => FND_API.G_FALSE
1162 , p_Dataset_Rec => l_Dataset_Rec
1163 , p_lang => scr.language
1164 , p_source_lang => scr.source_lang
1165 , x_return_status => x_return_status
1166 , x_msg_count => x_msg_count
1167 , x_msg_data => x_msg_data);
1168
1169 end if;
1170 end loop;
1171
1172 if synch_lang_cursor%ISOPEN THEN
1173 CLOSE synch_lang_cursor;
1174 end if;
1175
1176 EXCEPTION
1177 WHEN FND_API.G_EXC_ERROR THEN
1178 x_return_status := FND_API.G_RET_STS_ERROR;
1179 if synch_lang_cursor%ISOPEN THEN
1180 CLOSE synch_lang_cursor;
1181 end if;
1182 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1183 ,p_data => x_msg_data);
1184 RAISE;
1185 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1187 if synch_lang_cursor%ISOPEN THEN
1188 CLOSE synch_lang_cursor;
1189 end if;
1190 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1191 ,p_data => x_msg_data);
1192 RAISE;
1193 WHEN NO_DATA_FOUND THEN
1194 x_return_status := FND_API.G_RET_STS_ERROR;
1195 if synch_lang_cursor%ISOPEN THEN
1196 CLOSE synch_lang_cursor;
1197 end if;
1198 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1199 ,p_data => x_msg_data);
1200 RAISE;
1201 WHEN OTHERS THEN
1202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1203 if synch_lang_cursor%ISOPEN THEN
1204 CLOSE synch_lang_cursor;
1205 end if;
1206 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1207 ,p_data => x_msg_data);
1208 RAISE;
1209
1210 END lang_synch_existing_measures;
1211
1212 -- Not Used anymore
1213 function getUniqueDisplayName(
1214 p_dataset_name IN VARCHAR2
1215 , p_language IN VARCHAR2) return varchar2
1216 IS
1217 l_pmf_disp_name bsc_sys_datasets_tl.name%type;
1218 l_count number;
1219 begin
1220 l_pmf_disp_name := trim(p_dataset_name);
1221
1222 select count(indicator_id) into l_count
1223 from bis_indicators_tl
1224 where upper(name) = upper(l_pmf_disp_name)
1225 and language = p_language;
1226
1227 while(l_count > 0) loop
1228 l_pmf_disp_name := bsc_utility.get_Next_DispName(l_pmf_disp_name);
1229
1230 select count(indicator_id) into l_count
1231 from bis_indicators_tl
1232 where upper(name) = upper(l_pmf_disp_name)
1233 and language = p_language;
1234 end loop;
1235
1236 return l_pmf_disp_name;
1237
1238 EXCEPTION
1239 when others then return null;
1240
1241 end getUniqueDisplayName;
1242
1243
1244
1245 /*******************************************************************************
1246 FUNCTION TO SYNCHRONZIE DIMENSION OBJECTS BSC & PMF
1247 ********************************************************************************/
1248 FUNCTION Synchronize_Dim_Objects
1249 (
1250 x_error_msg OUT NOCOPY VARCHAR2
1251 ) RETURN BOOLEAN IS
1252 l_return_status VARCHAR2(30);
1253 l_msg_count NUMBER;
1254 l_dimension_id NUMBER;
1255
1256 CURSOR c_bis_dim_objs IS
1257 SELECT Short_Name
1258 , Dimension_Id
1259 , Level_Values_View_Name
1260 , Where_Clause
1261 , Created_By
1262 , Last_Updated_By
1263 , Last_Update_Date
1264 , Last_Update_Login
1265 , Name
1266 , Description
1267 , Source
1268 , Comparison_Label_Code
1269 , Attribute_Code
1270 , Application_Id
1271 FROM BIS_LEVELS_VL;
1272
1273 CURSOR c_bsc_dim_objs IS
1274 SELECT Dim_Level_Id
1275 , Name
1276 , Help
1277 , Total_Disp_Name
1278 , Comp_Disp_Name
1279 , Level_Table_Name
1280 , Table_Type
1281 , Level_Pk_Col
1282 , Abbreviation
1283 , Value_Order_By
1284 , Comp_Order_By
1285 , Custom_Group
1286 , User_Key_Size
1287 , Disp_Key_Size
1288 , Edw_Flag
1289 , Edw_Dim_Id
1290 , Edw_Dim_Level_Id
1291 , Level_View_Name
1292 , Short_Name
1293 , Source
1294 , Created_By
1295 , Last_Updated_By
1296 , Last_Update_Date
1297 , Last_Update_Login
1298 FROM BSC_SYS_DIM_LEVELS_VL;
1299
1300 l_bsc_dim_obj_rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
1301 l_bis_dim_level_rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
1302
1303 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1304
1305 l_flag BOOLEAN;
1306 l_count NUMBER;
1307 l_sql VARCHAR2(32000);
1308
1309 CURSOR c_oltp_level_name IS
1310 SELECT level_values_view_name
1311 FROM BIS_LEVELS
1312 WHERE SOURCE = 'OLTP'
1313 AND level_values_view_name NOT LIKE 'BSC_D_%'
1314 AND level_values_view_name IS NOT NULL;
1315 BEGIN
1316 BSC_APPS.Init_Bsc_Apps;
1317 FND_MSG_PUB.Initialize;
1318 SAVEPOINT BscSyncDimeObjects;
1319
1320 --Modify level_table_names in BSC tables to Upper case
1321 UPDATE BSC_SYS_DIM_LEVELS_B
1322 SET Level_Table_Name = UPPER(Level_Table_Name)
1323 WHERE LEVEL_TABLE_NAME <> UPPER(LEVEL_TABLE_NAME);
1324
1325 UPDATE BSC_KPI_DIM_LEVELS_B
1326 SET Level_Table_Name = UPPER(Level_Table_Name)
1327 WHERE LEVEL_TABLE_NAME <> UPPER(LEVEL_TABLE_NAME);
1328
1329 UPDATE BSC_DB_TABLES_RELS
1330 SET Table_Name = UPPER(Table_Name)
1331 WHERE Table_Name <> UPPER(Table_Name);
1332
1333 UPDATE BSC_SYS_DIM_LEVELS_B
1334 SET SHORT_NAME = 'BSC_DIM_OBJ_'||Dim_Level_Id||'_'||ROWNUM
1335 WHERE short_name IS NULL;
1336
1337 FOR cd IN c_bsc_dim_objs LOOP
1338 SELECT COUNT(1) INTO l_count
1339 FROM BIS_LEVELS
1340 WHERE short_name = cd.Short_Name;
1341 IF (l_count = 0) THEN
1342 SELECT COUNT(1) INTO l_count
1343 FROM BIS_LEVELS_TL
1344 WHERE Name = cd.Name
1345 AND language = userenv('LANG');
1346
1347 IF (l_count = 0) THEN
1348 l_bis_dim_level_rec.Dimension_Level_Name := cd.Name; -- Bug 3172231, should use Dimension_Level_Name instaed of Dimension_Name
1349 ELSE
1350 l_bis_dim_level_rec.Dimension_Level_Name := cd.Short_Name; -- Bug 3172231, should use Dimension_Level_Name instaed of Dimension_Name
1351 END IF;
1352 l_bis_dim_level_rec.Dimension_Name := 'unassigned';
1353 l_bis_dim_level_rec.Dimension_Short_Name := 'DUMMY_NAME';
1354 l_bis_dim_level_rec.Dimension_ID := -1;
1355 l_bis_dim_level_rec.Dimension_Level_Short_Name := cd.short_name;
1356 --l_bis_dim_level_rec.Dimension_Level_Name := cd.Name; -- Bug 3172231
1357 l_bis_dim_level_rec.Description := cd.Help;
1358 l_bis_dim_level_rec.Level_Values_View_Name := cd.level_table_name;
1359 l_bis_dim_level_rec.where_Clause := NULL;
1360 l_bis_dim_level_rec.Source := 'OLTP';
1361 -- 12/22/03 meastmon: Bug#3333957 Pass application id 271(Balanced scorecard application Id)
1362 -- when creating a BSC dimension level
1363 l_bis_dim_level_rec.Application_ID := 271;
1364 -- ankgoel: bug#3891748 - Creation_Date and Last_Update_Date will not be synced-up
1365 -- They might ne useful in debugging
1366 -- But now syncing-up for bug#4045278
1367 l_bis_dim_level_rec.Created_By := cd.Created_By;
1368 l_bis_dim_level_rec.Last_Updated_By := cd.Last_Updated_By;
1369 l_bis_dim_level_rec.Last_Update_Date := cd.Last_Update_Date;
1370 l_bis_dim_level_rec.Last_Update_Login := cd.Last_Update_Login;
1371 -- ankgoel: bug#4625611 - dim object should be enabled by default
1372 l_bis_dim_level_rec.enabled := FND_API.G_TRUE;
1373
1374 BIS_DIMENSION_LEVEL_PUB.Create_Dimension_Level
1375 (
1376 p_api_version => 1.0
1377 , p_commit => FND_API.G_FALSE
1378 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1379 , p_Dimension_Level_Rec => l_bis_dim_level_rec
1380 , x_return_status => l_return_status
1381 , x_error_Tbl => l_error_tbl
1382 );
1383
1384 Lang_Synch_BSC_To_PMF_DimObj
1385 (
1386 p_level_short_name => l_bis_dim_level_rec.Dimension_Level_Short_Name
1387 , x_return_status => l_return_status
1388 , x_error_Tbl => l_error_tbl
1389 );
1390
1391 END IF;
1392 END LOOP;
1393
1394 FOR cd IN c_bis_dim_objs LOOP
1395 SELECT COUNT(1) INTO l_count
1396 FROM BSC_SYS_DIM_LEVELS_B
1397 WHERE short_name = cd.Short_Name;
1398
1399 IF(l_count = 0) THEN
1400 l_bsc_dim_obj_rec.Source := cd.Source;
1401 l_bsc_dim_obj_rec.Bsc_Level_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_SYS_DIM_LEVELS_B', 'DIM_LEVEL_ID');
1402 l_bsc_dim_obj_rec.Bsc_Source := 'PMF';
1403 l_bsc_dim_obj_rec.Bsc_Level_User_Key_Size := 5;
1404 l_bsc_dim_obj_rec.Bsc_Level_Disp_Key_Size := 15;
1405 l_bsc_dim_obj_rec.Bsc_Level_Abbreviation := SUBSTR(REPLACE(cd.Short_Name, ' ', ''), 1, 5);
1406 l_bsc_dim_obj_rec.Bsc_Level_Short_Name := cd.Short_Name;
1407 l_bsc_dim_obj_rec.Bsc_Pk_Col := cd.Short_Name; -- Start to End KPI -- NULL; --fetch from IN-OUT Parameter
1408 l_bsc_dim_obj_rec.Bsc_Level_Name := cd.Level_Values_View_Name;
1409 l_bsc_dim_obj_rec.Bsc_Dim_Comp_Disp_Name := 'COMPARISON';
1410 l_bsc_dim_obj_rec.Bsc_Dim_Level_Long_Name := SUBSTR(cd.Name, 1, 60);
1411 l_bsc_dim_obj_rec.Bsc_Dim_Level_Help := NVL(cd.Description, cd.Name);
1412 l_bsc_dim_obj_rec.Bsc_Dim_Tot_Disp_Name := 'ALL';
1413 l_bsc_dim_obj_rec.Bsc_Level_Comp_Order_By := 0;
1414 l_bsc_dim_obj_rec.Bsc_Level_Custom_Group := 0;
1415 l_bsc_dim_obj_rec.Bsc_Level_Index := 0;
1416 l_bsc_dim_obj_rec.Bsc_Level_Table_Type := -1; --view will not be created at this point
1417 l_bsc_dim_obj_rec.Bsc_Level_Value_Order_By := 0;
1418 l_bsc_dim_obj_rec.Bsc_Created_By := cd.Created_By;
1419 l_bsc_dim_obj_rec.Bsc_Last_Updated_By := cd.Last_Updated_By;
1420 l_bsc_dim_obj_rec.Bsc_Last_Update_Date := cd.Last_Update_Date;
1421 l_bsc_dim_obj_rec.Bsc_Last_Update_Login := cd.Last_Update_Login;
1422
1423 l_flag := BSC_BIS_DIM_OBJ_PUB.Initialize_Pmf_Recs
1424 (
1425 p_Dim_Level_Rec => l_bsc_dim_obj_rec
1426 , x_return_status => l_return_status
1427 , x_msg_count => l_msg_count
1428 , x_msg_data => x_error_msg
1429 );
1430
1431 l_bsc_dim_obj_rec.Bsc_Level_Name := l_bsc_dim_obj_rec.Bsc_Level_View_Name;
1432 BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level
1433 (
1434 p_commit => FND_API.G_FALSE
1435 , p_Dim_Level_Rec => l_bsc_dim_obj_rec
1436 , p_create_tables => FALSE
1437 , x_return_status => l_return_status
1438 , x_msg_count => l_msg_count
1439 , x_msg_data => x_error_msg
1440 );
1441
1442 --
1443 Lang_Synch_PMF_To_BSC_DimObj
1444 (
1445 p_level_short_name => l_bsc_dim_obj_rec.Bsc_Level_Short_Name
1446 , x_return_status => l_return_status
1447 , x_msg_count => l_msg_count
1448 , x_msg_data => x_error_msg
1449 );
1450
1451 END IF;
1452 END LOOP;
1453 COMMIT;
1454 x_error_msg := 'BSC_UPGRADES.Synchronize_Dim_Objects Successfully Completed';
1455 RETURN TRUE;
1456 EXCEPTION
1457 WHEN FND_API.G_EXC_ERROR THEN
1458 IF (c_bis_dim_objs%ISOPEN) THEN
1459 CLOSE c_bis_dim_objs;
1460 END IF;
1461 IF (c_bsc_dim_objs%ISOPEN) THEN
1462 CLOSE c_bsc_dim_objs;
1463 END IF;
1464 ROLLBACK TO BscSyncDimeObjects;
1465 IF (x_error_msg IS NULL) THEN
1466 FND_MSG_PUB.Count_And_Get
1467 ( p_encoded => 'F'
1468 , p_count => l_msg_count
1469 , p_data => x_error_msg
1470 );
1471 END IF;
1472 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_error_msg);
1473 RETURN FALSE;
1474 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1475 IF (c_bis_dim_objs%ISOPEN) THEN
1476 CLOSE c_bis_dim_objs;
1477 END IF;
1478 IF (c_bsc_dim_objs%ISOPEN) THEN
1479 CLOSE c_bsc_dim_objs;
1480 END IF;
1481 ROLLBACK TO BscSyncDimeObjects;
1482 IF (x_error_msg IS NULL) THEN
1483 FND_MSG_PUB.Count_And_Get
1484 ( p_encoded => 'F'
1485 , p_count => l_msg_count
1486 , p_data => x_error_msg
1487 );
1488 END IF;
1489 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_error_msg);
1490 RETURN FALSE;
1491 WHEN NO_DATA_FOUND THEN
1492 IF (c_bis_dim_objs%ISOPEN) THEN
1493 CLOSE c_bis_dim_objs;
1494 END IF;
1495 IF (c_bsc_dim_objs%ISOPEN) THEN
1496 CLOSE c_bsc_dim_objs;
1497 END IF;
1498 ROLLBACK TO BscSyncDimeObjects;
1499 IF (x_error_msg IS NOT NULL) THEN
1500 x_error_msg := x_error_msg||' -> BSC_BIS_DIMENSION_PUB.Synchronize_Dim_Objects ';
1501 ELSE
1502 x_error_msg := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Synchronize_Dim_Objects ';
1503 END IF;
1504 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_error_msg);
1505 RETURN FALSE;
1506 WHEN OTHERS THEN
1507 IF (c_bis_dim_objs%ISOPEN) THEN
1508 CLOSE c_bis_dim_objs;
1509 END IF;
1510 IF (c_bsc_dim_objs%ISOPEN) THEN
1511 CLOSE c_bsc_dim_objs;
1512 END IF;
1513 ROLLBACK TO BscSyncDimeObjects;
1514 IF (x_error_msg IS NOT NULL) THEN
1515 x_error_msg := x_error_msg||' -> BSC_BIS_DIMENSION_PUB.Synchronize_Dim_Objects ';
1516 ELSE
1517 x_error_msg := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Synchronize_Dim_Objects ';
1518 END IF;
1519 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_error_msg);
1520 RETURN FALSE;
1521 END Synchronize_Dim_Objects;
1522
1523
1524
1525
1526 /*******************************************************************************
1527 FUNCTION TO SYNCHRONZIE DIMENSIONS IN BSC & PMF
1528 ********************************************************************************/
1529 FUNCTION Synchronize_Dimensions
1530 (
1531 x_error_msg OUT NOCOPY VARCHAR2
1532 ) RETURN BOOLEAN IS
1533 l_return_status VARCHAR2(30);
1534 l_msg_count NUMBER;
1535 l_dimension_id NUMBER;
1536 l_cn_dim_level_id NUMBER;
1537
1538 CURSOR c_bis_dims IS
1539 SELECT Dimension_Id
1540 , Short_Name
1541 , Created_By
1542 , Last_Updated_By
1543 , Last_Update_Date
1544 , Last_Update_Login
1545 , Name
1546 , Description
1547 , Application_Id
1548 FROM BIS_DIMENSIONS_VL
1549 WHERE DIM_GRP_ID IS NULL;
1550
1551 CURSOR c_bsc_dims IS
1552 SELECT Dim_Group_Id
1553 , Name
1554 , Short_Name
1555 , Created_By
1556 , Last_Updated_By
1557 , Last_Update_Date
1558 , Last_Update_Login
1559 FROM BSC_SYS_DIM_GROUPS_VL;
1560
1561 CURSOR dim_obj_short_name IS
1562 SELECT A.short_name short_name
1563 FROM BIS_LEVELS A
1564 WHERE A.dimension_id = l_dimension_id;
1565
1566
1567 l_bis_dimension_rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
1568 l_bsc_dimension_rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
1569 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1570
1571 l_flag BOOLEAN;
1572 l_count NUMBER;
1573 BEGIN
1574 FND_MSG_PUB.Initialize;
1575 SAVEPOINT BscSyncDimensions;
1576
1577 UPDATE BSC_SYS_DIM_GROUPS_TL
1578 SET SHORT_NAME = 'BSC_DIM_'||Dim_Group_Id
1579 WHERE short_name IS NULL;
1580
1581 FOR cd IN c_bis_dims LOOP
1582 SELECT COUNT(1) INTO l_count
1583 FROM BSC_SYS_DIM_GROUPS_VL
1584 WHERE short_name = cd.Short_Name;
1585
1586 IF (l_count = 0) THEN
1587 IF (cd.Dimension_Id <> -1) THEN
1588 SELECT COUNT(1) INTO l_count
1589 FROM BSC_SYS_DIM_GROUPS_TL
1590 WHERE name = cd.Name
1591 AND language = userenv('LANG');
1592
1593 IF (l_count = 0) THEN
1594 l_bsc_dimension_rec.Bsc_Dim_Level_Group_Name := cd.Name;
1595 ELSE
1596 l_bsc_dimension_rec.Bsc_Dim_Level_Group_Name := cd.Short_Name;
1597 END IF;
1598 l_dimension_id := cd.Dimension_Id;
1599 l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id := NULL;
1600 l_bsc_dimension_rec.Bsc_Dim_Level_Group_short_name := cd.Short_Name;
1601 l_bsc_dimension_rec.Bsc_Dim_Level_Index := 1;
1602 l_bsc_dimension_rec.Bsc_Group_Level_Comp_Flag := -1;
1603 l_bsc_dimension_rec.Bsc_Group_Level_Default_Value := 'T';
1604 l_bsc_dimension_rec.Bsc_Group_Level_Default_Type := 0;
1605 l_bsc_dimension_rec.Bsc_Group_Level_Filter_Col := NULL;
1606 l_bsc_dimension_rec.Bsc_Group_Level_Filter_Value := 0;
1607 l_bsc_dimension_rec.Bsc_Group_Level_No_Items := 0;
1608 l_bsc_dimension_rec.Bsc_Group_Level_Parent_In_Tot := 2;
1609 l_bsc_dimension_rec.Bsc_Group_Level_Total_Flag := -1;
1610 l_bsc_dimension_rec.Bsc_Language := NULL;
1611 l_bsc_dimension_rec.Bsc_Level_Id := NULL;
1612 l_bsc_dimension_rec.Bsc_Source_Language := NULL;
1613 l_bsc_dimension_rec.Bsc_Created_By := cd.Created_By;
1614 l_bsc_dimension_rec.Bsc_Last_Updated_By := cd.Last_Updated_By;
1615 l_bsc_dimension_rec.Bsc_Last_Update_Date := cd.Last_Update_Date;
1616 l_bsc_dimension_rec.Bsc_Last_Update_Login := cd.Last_Update_Login;
1617
1618 BSC_DIMENSION_GROUPS_PUB.Create_Dimension_Group
1619 (
1620 p_commit => FND_API.G_FALSE
1621 , p_Dim_Grp_Rec => l_bsc_dimension_rec
1622 , p_create_Dim_Levels => FALSE
1623 , x_return_status => l_return_status
1624 , x_msg_count => l_msg_count
1625 , x_msg_data => x_error_msg
1626 );
1627
1628
1629 Lang_Synch_PMF_To_BSC_Dim
1630 (
1631 p_dim_short_name => l_bsc_dimension_rec.Bsc_Dim_Level_Group_Short_Name
1632 , x_return_status => l_return_status
1633 , x_msg_count => l_msg_count
1634 , x_msg_data => x_error_msg
1635 );
1636
1637 -- sync-up dimension - dimension objects relationship to BSC side.
1638 FOR dim_cn IN dim_obj_short_name LOOP
1639 -- Added because join to bis_levels is causing FTS.
1640 BEGIN
1641 SELECT dim_level_id
1642 INTO l_cn_dim_level_id
1643 FROM BSC_SYS_DIM_LEVELS_B
1644 WHERE short_name = dim_cn.short_name;
1645 EXCEPTION WHEN OTHERS THEN
1646 NULL;
1647 END;
1648 SELECT COUNT(1) INTO l_count
1649 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
1650 WHERE dim_level_id = l_cn_dim_level_id
1651 AND dim_group_id = (SELECT Dim_Group_Id
1652 FROM BSC_SYS_DIM_GROUPS_VL WHERE SHORT_NAME = cd.Short_Name);
1653
1654 IF (l_count = 0) THEN
1655 BSC_BIS_DIMENSION_PUB.Assign_Dimension_Object
1656 (
1657 p_commit => FND_API.G_FALSE
1658 , p_dim_short_name => cd.Short_Name
1659 , p_dim_obj_short_name => dim_cn.Short_Name
1660 , p_comp_flag => -1
1661 , p_no_items => 0
1662 , p_parent_in_tot => 2
1663 , p_total_flag => -1
1664 , p_default_value => 'T'
1665 , p_time_stamp => NULL -- Granular Locking
1666 , x_return_status => l_return_status
1667 , x_msg_count => l_msg_count
1668 , x_msg_data => x_error_msg
1669 );
1670 END IF;
1671 END LOOP;
1672 END IF;
1673 END IF;
1674
1675
1676 END LOOP;
1677
1678 FOR cd IN c_bsc_dims LOOP
1679 SELECT COUNT(1) INTO l_count
1680 FROM BIS_DIMENSIONS
1681 WHERE short_name = cd.Short_Name;
1682
1683 IF (l_count = 0) THEN
1684 SELECT COUNT(1) INTO l_count
1685 FROM BIS_DIMENSIONS_TL
1686 WHERE Name = cd.Name
1687 AND language = userenv('LANG');
1688
1689 IF (l_count = 0) THEN
1690 l_bis_dimension_rec.Dimension_Name := cd.Name;
1691 ELSE
1692 l_bis_dimension_rec.Dimension_Name := cd.short_name;
1693 END IF;
1694 l_bis_dimension_rec.Dimension_Short_Name := cd.short_name;
1695 l_bis_dimension_rec.Description := cd.Name;
1696 l_bis_dimension_rec.Application_ID := 271;
1697 -- ankgoel: bug#3891748 - Creation_Date and Last_Update_Date will not be synced-up
1698 -- They might ne useful in debugging
1699 -- But now syncing-up for bug#4045278
1700 l_bis_dimension_rec.Created_By := cd.Created_By;
1701 l_bis_dimension_rec.Last_Updated_By := cd.Last_Updated_By;
1702 l_bis_dimension_rec.Last_Update_Date := cd.Last_Update_Date;
1703 l_bis_dimension_rec.Last_Update_Login := cd.Last_Update_Login;
1704
1705 BIS_DIMENSION_PUB.Create_Dimension
1706 ( p_api_version => 1.0
1707 , p_commit => FND_API.G_FALSE
1708 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1709 , p_Dimension_Rec => l_bis_dimension_rec
1710 , x_return_status => l_return_status
1711 , x_error_Tbl => l_error_tbl
1712 );
1713
1714 Lang_Synch_BSC_To_PMF_Dim
1715 ( p_dim_short_name => l_bis_dimension_rec.Dimension_Short_Name
1716 , x_return_status => l_return_status
1717 , x_error_Tbl => l_error_tbl
1718 );
1719
1720 -- sync-up dimension - dimension objects relationship to PMF side.
1721 sync_dim_object_mappings
1722 ( p_dim_short_name => l_bis_dimension_rec.Dimension_Short_Name
1723 , x_return_status => l_return_status
1724 , x_error_Tbl => l_error_tbl
1725 );
1726
1727 END IF;
1728
1729 UPDATE BIS_DIMENSIONS
1730 SET Dim_Grp_ID = cd.Dim_Group_Id
1731 WHERE Short_Name = cd.short_name;
1732
1733 END LOOP;
1734 COMMIT;
1735 x_error_msg := 'BSC_UPGRADES.Synchronize_Dimensions Successfully Completed';
1736 RETURN TRUE;
1737 EXCEPTION
1738 WHEN FND_API.G_EXC_ERROR THEN
1739 IF (c_bsc_dims%ISOPEN) THEN
1740 CLOSE c_bsc_dims;
1741 END IF;
1742 IF (c_bis_dims%ISOPEN) THEN
1743 CLOSE c_bsc_dims;
1744 END IF;
1745 IF (dim_obj_short_name%ISOPEN) THEN
1746 CLOSE dim_obj_short_name;
1747 END IF;
1748 ROLLBACK TO BscSyncDimensions;
1749 IF (x_error_msg IS NULL) THEN
1750 FND_MSG_PUB.Count_And_Get
1751 ( p_encoded => 'F'
1752 , p_count => l_msg_count
1753 , p_data => x_error_msg
1754 );
1755 END IF;
1756 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_error_msg);
1757 RETURN FALSE;
1758 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1759 IF (c_bsc_dims%ISOPEN) THEN
1760 CLOSE c_bsc_dims;
1761 END IF;
1762 IF (c_bis_dims%ISOPEN) THEN
1763 CLOSE c_bsc_dims;
1764 END IF;
1765 IF (dim_obj_short_name%ISOPEN) THEN
1766 CLOSE dim_obj_short_name;
1767 END IF;
1768 ROLLBACK TO BscSyncDimensions;
1769 IF (x_error_msg IS NULL) THEN
1770 FND_MSG_PUB.Count_And_Get
1771 ( p_encoded => 'F'
1772 , p_count => l_msg_count
1773 , p_data => x_error_msg
1774 );
1775 END IF;
1776 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_error_msg);
1777 RETURN FALSE;
1778 WHEN NO_DATA_FOUND THEN
1779 IF (c_bsc_dims%ISOPEN) THEN
1780 CLOSE c_bsc_dims;
1781 END IF;
1782 IF (c_bis_dims%ISOPEN) THEN
1783 CLOSE c_bsc_dims;
1784 END IF;
1785 IF (dim_obj_short_name%ISOPEN) THEN
1786 CLOSE dim_obj_short_name;
1787 END IF;
1788 ROLLBACK TO BscSyncDimensions;
1789 IF (x_error_msg IS NOT NULL) THEN
1790 x_error_msg := x_error_msg||' -> BSC_UPGRADES.Synchronize_Dimensions ';
1791 ELSE
1792 x_error_msg := SQLERRM||' at BSC_UPGRADES.Synchronize_Dimensions ';
1793 END IF;
1794 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_error_msg);
1795 RETURN FALSE;
1796 WHEN OTHERS THEN
1797 IF (c_bsc_dims%ISOPEN) THEN
1798 CLOSE c_bsc_dims;
1799 END IF;
1800 IF (c_bis_dims%ISOPEN) THEN
1801 CLOSE c_bsc_dims;
1802 END IF;
1803 IF (dim_obj_short_name%ISOPEN) THEN
1804 CLOSE dim_obj_short_name;
1805 END IF;
1806 ROLLBACK TO BscSyncDimensions;
1807 IF (x_error_msg IS NOT NULL) THEN
1808 x_error_msg := x_error_msg||' -> BSC_UPGRADES.Synchronize_Dimensions ';
1809 ELSE
1810 x_error_msg := SQLERRM||' at BSC_UPGRADES.Synchronize_Dimensions ';
1811 END IF;
1812 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_error_msg);
1813 RETURN FALSE;
1814 END Synchronize_Dimensions;
1815
1816
1817 /*******************************************************************************
1818 Procedure to sync dimension objects lang data
1819 ********************************************************************************/
1820
1821 PROCEDURE Lang_Synch_BSC_To_PMF_DimObj
1822 (
1823 p_level_short_name IN VARCHAR2
1824 , x_return_status OUT NOCOPY VARCHAR2
1825 , x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1826 )
1827 IS
1828
1829 l_Dim_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
1830 l_level_id NUMBER;
1831 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1832
1833 cursor installed_languages_cursor is
1834 select L.language_code
1835 from FND_LANGUAGES L
1836 where L.INSTALLED_FLAG in ('I', 'B')
1837 and language_code <> userenv('LANG');
1838
1839 BEGIN
1840
1841 SELECT DIM_LEVEL_ID
1842 INTO l_level_id
1843 FROM BSC_SYS_DIM_LEVELS_B
1844 WHERE SHORT_NAME = p_level_short_name;
1845
1846 l_Dim_Level_Rec.Dimension_Short_Name := p_level_short_name;
1847
1848 FOR cd in installed_languages_cursor LOOP
1849 SELECT NAME, HELP, LANGUAGE, SOURCE_LANG
1850 INTO l_Dim_Level_Rec.Dimension_Name,
1851 l_Dim_Level_Rec.Description,
1852 l_Dim_Level_Rec.Language,
1853 l_Dim_Level_Rec.Source_Lang
1854 FROM BSC_SYS_DIM_LEVELS_TL
1855 WHERE DIM_LEVEL_ID = l_level_id
1856 AND LANGUAGE = cd.language_code;
1857
1858 BIS_DIMENSION_LEVEL_PUB.Trans_DimObj_By_Given_Lang
1859 (
1860 p_commit => FND_API.G_FALSE
1861 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1862 , p_Dimension_Level_Rec => l_Dim_Level_Rec
1863 , x_return_status => x_return_status
1864 , x_error_Tbl => x_error_tbl
1865 );
1866 END LOOP;
1867
1868 EXCEPTION
1869
1870 WHEN NO_DATA_FOUND THEN
1871 x_return_status := FND_API.G_RET_STS_ERROR ;
1872 if installed_languages_cursor%ISOPEN THEN
1873 CLOSE installed_languages_cursor;
1874 end if;
1875 when FND_API.G_EXC_ERROR then
1876 x_return_status := FND_API.G_RET_STS_ERROR ;
1877 if installed_languages_cursor%ISOPEN THEN
1878 CLOSE installed_languages_cursor;
1879 end if;
1880 when FND_API.G_EXC_UNEXPECTED_ERROR then
1881 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1882 if installed_languages_cursor%ISOPEN THEN
1883 CLOSE installed_languages_cursor;
1884 end if;
1885 when others then
1886 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1887 l_error_tbl := x_error_Tbl;
1888 if installed_languages_cursor%ISOPEN THEN
1889 CLOSE installed_languages_cursor;
1890 end if;
1891
1892 BIS_UTILITIES_PVT.Add_Error_Message
1893 ( p_error_msg_id => SQLCODE
1894 , p_error_description => SQLERRM
1895 , p_error_proc_name => G_PKG_NAME||'.Lang_Synch_BSC_To_PMF_Dim'
1896 , p_error_table => l_error_tbl
1897 , x_error_table => x_error_tbl
1898 );
1899 END Lang_Synch_BSC_To_PMF_DimObj;
1900
1901
1902
1903 /*******************************************************************************
1904 Procedure to sync dimension objects lang data
1905 ********************************************************************************/
1906
1907 PROCEDURE Lang_Synch_PMF_To_BSC_DimObj
1908 (
1909 p_level_short_name IN VARCHAR2
1910 , x_return_status OUT NOCOPY VARCHAR2
1911 , x_msg_count OUT NOCOPY NUMBER
1912 , x_msg_data OUT NOCOPY VARCHAR2
1913 )
1914 IS
1915
1916 l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
1917 l_level_id NUMBER;
1918
1919 cursor installed_languages_cursor is
1920 select L.language_code
1921 from FND_LANGUAGES L
1922 where L.INSTALLED_FLAG in ('I', 'B')
1923 and language_code <> userenv('LANG');
1924
1925 BEGIN
1926
1927 SELECT LEVEL_ID
1928 INTO l_level_id
1929 FROM BIS_LEVELS
1930 WHERE SHORT_NAME = p_level_short_name;
1931
1932 l_Dim_Level_Rec.Bsc_Level_Short_Name := p_level_short_name;
1933 --As of now LUD is not being updated, but populated for future use
1934 FOR cd in installed_languages_cursor LOOP
1935 SELECT NAME, DESCRIPTION, LANGUAGE, LAST_UPDATE_DATE, SOURCE_LANG
1936 INTO l_Dim_Level_Rec.Bsc_Dim_Level_Long_Name,
1937 l_Dim_Level_Rec.Bsc_Dim_Level_Help,
1938 l_Dim_Level_Rec.Bsc_Language,
1939 l_Dim_Level_Rec.Bsc_Last_Update_Date,
1940 l_Dim_Level_Rec.Bsc_Source_Language
1941 FROM BIS_LEVELS_TL
1942 WHERE LEVEL_ID = l_level_id
1943 AND LANGUAGE = cd.language_code;
1944
1945 BSC_DIMENSION_LEVELS_PUB.Trans_DimObj_By_Given_Lang
1946 (
1947 p_commit => FND_API.G_FALSE
1948 , p_dim_level_rec => l_Dim_Level_Rec
1949 , x_return_status => x_return_status
1950 , x_msg_count => x_msg_count
1951 , x_msg_data => x_msg_data
1952 );
1953
1954 END LOOP;
1955 EXCEPTION
1956 WHEN FND_API.G_EXC_ERROR THEN
1957 x_return_status := FND_API.G_RET_STS_ERROR;
1958 if installed_languages_cursor%ISOPEN THEN
1959 CLOSE installed_languages_cursor;
1960 end if;
1961 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1962 ,p_data => x_msg_data);
1963 RAISE;
1964 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1965 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1966 if installed_languages_cursor%ISOPEN THEN
1967 CLOSE installed_languages_cursor;
1968 end if;
1969 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1970 ,p_data => x_msg_data);
1971 RAISE;
1972 WHEN NO_DATA_FOUND THEN
1973 x_return_status := FND_API.G_RET_STS_ERROR;
1974 if installed_languages_cursor%ISOPEN THEN
1975 CLOSE installed_languages_cursor;
1976 end if;
1977 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1978 ,p_data => x_msg_data);
1979 RAISE;
1980 WHEN OTHERS THEN
1981 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1982 if installed_languages_cursor%ISOPEN THEN
1983 CLOSE installed_languages_cursor;
1984 end if;
1985 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1986 ,p_data => x_msg_data);
1987 RAISE;
1988
1989 END Lang_Synch_PMF_To_BSC_DimObj;
1990
1991
1992 /*******************************************************************************
1993 ********************************************************************************/
1994
1995
1996 PROCEDURE Lang_Synch_BSC_To_PMF_Dim
1997 (
1998 p_dim_short_name IN VARCHAR2
1999 , x_return_status OUT NOCOPY VARCHAR2
2000 , x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2001 )
2002 IS
2003
2004 l_Dim_Grp_Rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
2005 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2006
2007 cursor installed_languages_cursor is
2008 select L.language_code
2009 from FND_LANGUAGES L
2010 where L.INSTALLED_FLAG in ('I', 'B')
2011 and language_code <> userenv('LANG');
2012
2013 BEGIN
2014 FOR cd IN installed_languages_cursor LOOP
2015
2016 SELECT SHORT_NAME, NAME, LANGUAGE, LAST_UPDATE_DATE, SOURCE_LANG
2017 INTO l_Dim_Grp_Rec.Dimension_Short_Name,
2018 l_Dim_Grp_Rec.Dimension_Name,
2019 l_Dim_Grp_Rec.Language,
2020 l_Dim_Grp_Rec.Last_Update_Date,
2021 l_Dim_Grp_Rec.Source_Lang
2022 FROM BSC_SYS_DIM_GROUPS_TL
2023 WHERE SHORT_NAME = p_dim_short_name
2024 AND LANGUAGE = cd.language_code;
2025
2026 BIS_DIMENSION_PUB.Translate_Dim_By_Given_Lang
2027 (
2028 p_commit => FND_API.G_FALSE
2029 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
2030 , p_Dimension_Rec => l_Dim_Grp_Rec
2031 , x_return_status => x_return_status
2032 , x_error_Tbl => x_error_tbl
2033 );
2034
2035 END LOOP;
2036
2037 EXCEPTION
2038 WHEN NO_DATA_FOUND THEN
2039 x_return_status := FND_API.G_RET_STS_ERROR ;
2040 if installed_languages_cursor%ISOPEN THEN
2041 CLOSE installed_languages_cursor;
2042 end if;
2043 when FND_API.G_EXC_ERROR then
2044 x_return_status := FND_API.G_RET_STS_ERROR ;
2045 if installed_languages_cursor%ISOPEN THEN
2046 CLOSE installed_languages_cursor;
2047 end if;
2048 when FND_API.G_EXC_UNEXPECTED_ERROR then
2049 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2050 if installed_languages_cursor%ISOPEN THEN
2051 CLOSE installed_languages_cursor;
2052 end if;
2053 when others then
2054 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2055 l_error_tbl := x_error_Tbl;
2056 if installed_languages_cursor%ISOPEN THEN
2057 CLOSE installed_languages_cursor;
2058 end if;
2059
2060 BIS_UTILITIES_PVT.Add_Error_Message
2061 ( p_error_msg_id => SQLCODE
2062 , p_error_description => SQLERRM
2063 , p_error_proc_name => G_PKG_NAME||'.Lang_Synch_BSC_To_PMF_Dim'
2064 , p_error_table => l_error_tbl
2065 , x_error_table => x_error_tbl
2066 );
2067 END Lang_Synch_BSC_To_PMF_Dim;
2068
2069
2070 /*******************************************************************************
2071 ********************************************************************************/
2072
2073
2074 PROCEDURE Lang_Synch_PMF_To_BSC_Dim
2075 (
2076 p_dim_short_name IN VARCHAR2
2077 ,x_return_status OUT NOCOPY VARCHAR2
2078 ,x_msg_count OUT NOCOPY NUMBER
2079 ,x_msg_data OUT NOCOPY VARCHAR2
2080
2081 )
2082 IS
2083
2084 l_Dim_Grp_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
2085 l_dim_id NUMBER;
2086
2087 cursor installed_languages_cursor is
2088 select L.language_code
2089 from FND_LANGUAGES L
2090 where L.INSTALLED_FLAG in ('I', 'B')
2091 and language_code <> userenv('LANG');
2092
2093
2094 BEGIN
2095
2096 SELECT DIMENSION_ID
2097 INTO l_dim_id
2098 FROM BIS_DIMENSIONS
2099 WHERE SHORT_NAME = p_dim_short_name;
2100
2101 l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Short_Name := p_dim_short_name;
2102
2103 FOR cd IN installed_languages_cursor LOOP
2104 SELECT NAME, LANGUAGE, LAST_UPDATE_DATE, SOURCE_LANG
2105 INTO l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name,
2106 l_Dim_Grp_Rec.Bsc_Language,
2107 l_Dim_Grp_Rec.Bsc_Last_Update_Date,
2108 l_Dim_Grp_Rec.Bsc_Source_Language
2109 FROM BIS_DIMENSIONS_TL
2110 WHERE DIMENSION_ID = l_dim_id
2111 AND LANGUAGE = cd.language_code;
2112
2113 BSC_DIMENSION_GROUPS_PUB.Translate_Dim_By_Given_Lang
2114 (
2115 p_commit => FND_API.G_FALSE
2116 , p_Dim_Grp_Rec => l_Dim_Grp_Rec
2117 , x_return_status => x_return_status
2118 , x_msg_count => x_msg_count
2119 , x_msg_data => x_msg_data
2120 );
2121
2122 END LOOP;
2123 EXCEPTION
2124 WHEN FND_API.G_EXC_ERROR THEN
2125 x_return_status := FND_API.G_RET_STS_ERROR;
2126 if installed_languages_cursor%ISOPEN THEN
2127 CLOSE installed_languages_cursor;
2128 end if;
2129 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2130 ,p_data => x_msg_data);
2131 RAISE;
2132 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2134 if installed_languages_cursor%ISOPEN THEN
2135 CLOSE installed_languages_cursor;
2136 end if;
2137 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2138 ,p_data => x_msg_data);
2139 RAISE;
2140 WHEN NO_DATA_FOUND THEN
2141 x_return_status := FND_API.G_RET_STS_ERROR;
2142 if installed_languages_cursor%ISOPEN THEN
2143 CLOSE installed_languages_cursor;
2144 end if;
2145 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2146 ,p_data => x_msg_data);
2147 RAISE;
2148 WHEN OTHERS THEN
2149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2150 if installed_languages_cursor%ISOPEN THEN
2151 CLOSE installed_languages_cursor;
2152 end if;
2153 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2154 ,p_data => x_msg_data);
2155 RAISE;
2156
2157 END Lang_Synch_PMF_To_BSC_Dim;
2158
2159
2160
2161
2162 /*******************************************************************************
2163 ********************************************************************************/
2164
2165 -- Function to give all tabs and kpis access to Performance Management User
2166
2167
2168 FUNCTION Add_Access_To_Tabs_Kpis
2169 (
2170 x_error_msg OUT NOCOPY VARCHAR2
2171 ) RETURN BOOLEAN IS
2172 l_Bsc_Tab_Entity_Rec BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;
2173 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
2174 l_mgr_resp NUMBER ;
2175 l_pmd_resp NUMBER ;
2176 l_count NUMBER ;
2177 l_valid NUMBER ;
2178 l_msg_count NUMBER;
2179 l_msg_data VARCHAR2(2000);
2180 x_return_status VARCHAR2(5000);
2181 x_msg_count NUMBER;
2182 x_msg_data VARCHAR2(5000);
2183 CURSOR c_get_upgrade_tabs (c_mgr_resp NUMBER) IS
2184 SELECT * FROM BSC_USER_TAB_ACCESS
2185 WHERE RESPONSIBILITY_ID = c_mgr_resp;
2186
2187 CURSOR c_get_upgrade_kpis(c_mgr_resp NUMBER) IS
2188 SELECT * FROM BSC_USER_KPI_ACCESS
2189 WHERE RESPONSIBILITY_ID = c_mgr_resp;
2190 BEGIN
2191 SELECT responsibility_id
2192 INTO l_pmd_resp
2193 FROM FND_RESPONSIBILITY
2194 WHERE responsibility_key = 'BSC_PMD_USER';
2195
2196 SELECT responsibility_id
2197 INTO l_mgr_resp
2198 FROM FND_RESPONSIBILITY
2199 WHERE responsibility_key = 'BSC_Manager';
2200
2201 FOR c_tab_recs IN c_get_upgrade_tabs(l_mgr_resp) LOOP
2202 SELECT COUNT(1)
2203 INTO l_count
2204 FROM BSC_USER_TAB_ACCESS
2205 WHERE TAB_ID = c_tab_recs.TAB_ID
2206 AND RESPONSIBILITY_ID = l_pmd_resp;
2207
2208 SELECT COUNT(1)
2209 INTO l_valid
2210 FROM BSC_TABS_B
2211 WHERE TAB_ID = c_tab_recs.TAB_ID;
2212 IF ( l_count = 0 and l_valid > 0 ) THEN
2213 l_Bsc_Tab_Entity_Rec.Bsc_Responsibility_Id := l_pmd_resp;
2214 l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id := c_tab_recs.TAB_ID;
2215 l_Bsc_Tab_Entity_Rec.Bsc_Created_By := c_tab_recs.CREATED_BY;
2216 l_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By := c_tab_recs.CREATED_BY;
2217 l_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login := c_tab_recs.LAST_UPDATE_LOGIN;
2218 l_Bsc_Tab_Entity_Rec.Bsc_Resp_End_Date := c_tab_recs.END_DATE;
2219 BEGIN
2220 BSC_SCORECARD_PVT.Create_Tab_Access
2221 ( FND_API.G_FALSE
2222 , l_Bsc_Tab_Entity_Rec
2223 , x_return_status
2224 , x_msg_count
2225 , x_msg_data
2226 );
2227 EXCEPTION
2228 WHEN OTHERS THEN
2229 NULL;
2230 END;
2231 END IF;
2232 END LOOP;
2233 FOR c_kpi_recs IN c_get_upgrade_kpis(l_mgr_resp) LOOP
2234 SELECT COUNT(1)
2235 INTO l_count
2236 FROM BSC_USER_KPI_ACCESS
2237 WHERE INDICATOR = c_kpi_recs.INDICATOR
2238 AND RESPONSIBILITY_ID = l_pmd_resp;
2239
2240 SELECT COUNT(1)
2241 INTO l_valid
2242 FROM BSC_KPIS_B
2243 WHERE INDICATOR = c_kpi_recs.INDICATOR;
2244
2245 IF ( l_count = 0 and l_valid > 0 ) THEN
2246 l_Bsc_Kpi_Entity_Rec.Bsc_Responsibility_Id := l_pmd_resp;
2247 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := c_kpi_recs.INDICATOR;
2248 l_Bsc_Kpi_Entity_Rec.Created_By := c_kpi_recs.CREATED_BY;
2249 l_Bsc_Kpi_Entity_Rec.Last_Updated_By := c_kpi_recs.CREATED_BY;
2250 l_Bsc_Kpi_Entity_Rec.Last_Update_Login := c_kpi_recs.LAST_UPDATE_LOGIN;
2251 l_Bsc_Kpi_Entity_Rec.Bsc_Resp_Start_Date := c_kpi_recs.START_DATE;
2252 l_Bsc_Kpi_Entity_Rec.Bsc_Resp_End_Date := c_kpi_recs.END_DATE;
2253 BEGIN
2254 BSC_KPI_PVT.Create_Kpi_User_Access
2255 ( FND_API.G_FALSE
2256 , l_Bsc_Kpi_Entity_Rec
2257 , x_return_status
2258 , x_msg_count
2259 , x_msg_data
2260 );
2261 EXCEPTION
2262 WHEN OTHERS THEN
2263 NULL;
2264 END;
2265 -- dbms_output.put_line('tab_id = '||c_kpi_recs.INDICATOR);
2266 END IF;
2267 END LOOP;
2268 RETURN TRUE;
2269 EXCEPTION
2270 WHEN OTHERS THEN
2271 ROLLBACK;
2272 FND_MSG_PUB.Count_And_Get
2273 ( p_count => l_msg_count
2274 ,p_data => l_msg_data
2275 );
2276 IF (l_msg_data IS NULL) THEN
2277 l_msg_data := SQLERRM;
2278 end if;
2279 x_error_msg := l_msg_data;
2280 RETURN FALSE;
2281 END Add_Access_To_Tabs_Kpis;
2282
2283 /*******************************************************************************
2284 ********************************************************************************/
2285
2286 -- Upgrade for BS 5.1.0 to 5.1.1 to seperate Summization and UI Features(Max Fetching) Profiles
2287 FUNCTION Upgrade_Advanced_Profile(
2288 x_error_msg OUT NOCOPY VARCHAR2
2289 ) RETURN BOOLEAN IS
2290
2291 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2292 l_return_status boolean;
2293 l_summerization_value VARCHAR2(240);
2294 l_ui_features_value VARCHAR2(240);
2295
2296 BEGIN
2297
2298 l_ui_features_value := FND_PROFILE.value('BSC_ADVANCED_UI_FEATURES');
2299
2300 IF l_ui_features_value IS NULL THEN -- first time setup value
2301
2302 l_summerization_value :=FND_PROFILE.value('BSC_ADVANCED_SUMMARIZATION_LEVEL');
2303 IF l_summerization_value IS NULL THEN
2304 l_return_status := FND_PROFILE.save('BSC_ADVANCED_UI_FEATURES','No', 'SITE');
2305 ELSE
2306 l_return_status := FND_PROFILE.save('BSC_ADVANCED_UI_FEATURES','Yes', 'SITE');
2307 END IF;
2308
2309 COMMIT;
2310 END IF;
2311
2312 RETURN TRUE;
2313
2314 EXCEPTION
2315 WHEN OTHERS THEN
2316 ROLLBACK;
2317 x_error_msg := SQLERRM;
2318 RETURN FALSE;
2319 END Upgrade_Advanced_Profile;
2320
2321 /*******************************************************************************
2322 ********************************************************************************/
2323
2324 FUNCTION get_next_entry_sequence
2325 (
2326 p_Menu_Id FND_MENUS.menu_id%TYPE
2327 )RETURN NUMBER
2328 IS
2329 l_count NUMBER;
2330 BEGIN
2331
2332 SELECT NVL(MAX(Entry_Sequence),0)Entry_Sequence
2333 INTO l_count
2334 FROM FND_MENU_ENTRIES
2335 WHERE Menu_Id =p_Menu_Id;
2336
2337 RETURN (l_count + 1);
2338
2339 END get_next_entry_sequence;
2340
2341
2342 -- Upgrade for BS 5.1.0 to 5.1.1 to sync up Lanuchpad from BSC_Manger to other pmd resps.
2343 FUNCTION Add_Access_To_Launchpads
2344 (
2345 p_mgr_resp NUMBER,
2346 p_pmd_resp NUMBER,
2347 x_error_msg OUT NOCOPY VARCHAR2
2348 ) RETURN BOOLEAN IS
2349 l_count NUMBER ;
2350 l_msg_count NUMBER;
2351 l_msg_data VARCHAR2(2000);
2352 x_return_status VARCHAR2(5000);
2353 x_msg_count NUMBER;
2354 x_msg_data VARCHAR2(5000);
2355
2356 l_mgr_menu NUMBER ;
2357 l_pmd_menu NUMBER ;
2358 h_launchpad_id NUMBER ;
2359 h_launchpad_desc VARCHAR2(240);
2360 h_user_id NUMBER ;
2361
2362
2363 CURSOR c_synch_launchpad IS
2364 SELECT A.MENU_ID
2365 , A.DESCRIPTION
2366 , A.CREATED_BY
2367 FROM FND_MENUS_VL A
2368 , FND_MENU_ENTRIES B
2369 WHERE A.MENU_NAME LIKE 'BSC_LAUNCHPAD_%'
2370 AND A.MENU_ID = B.SUB_MENU_ID
2371 AND B.MENU_ID = l_mgr_menu;
2372
2373 BEGIN
2374 --BSC_Manager
2375 SELECT MENU_ID
2376 INTO l_mgr_menu
2377 FROM FND_RESPONSIBILITY_VL
2378 WHERE RESPONSIBILITY_ID = p_mgr_resp;
2379
2380 --BSC_PMD_USER/BSC_DESIGNER (called two time from bscup.sql)
2381 --once for BSC_PMD_USER and once for BSC_DESIGNER
2382 SELECT MENU_ID
2383 INTO l_pmd_menu
2384 FROM FND_RESPONSIBILITY_VL
2385 WHERE RESPONSIBILITY_ID = p_pmd_resp;
2386
2387
2388 FOR cd IN c_synch_launchpad LOOP
2389 SELECT COUNT(0)
2390 INTO l_count
2391 FROM FND_MENU_ENTRIES
2392 WHERE MENU_ID = l_pmd_menu
2393 AND SUB_MENU_ID = cd.menu_id;
2394
2395 IF (l_count = 0) THEN
2396 BSC_LAUNCH_PAD_PVT.INSERT_APP_MENU_ENTRIES_VB
2397 ( X_Menu_Id => l_pmd_menu
2398 , X_Entry_Sequence => get_next_entry_sequence(l_pmd_menu)
2399 , X_Sub_Menu_Id => cd.menu_id
2400 , X_Function_Id => NULL
2401 , X_Grant_Flag =>'Y'
2402 , X_Prompt => NULL
2403 , X_Description => cd.description
2404 , X_User_Id => cd.created_by
2405 );
2406 END IF;
2407 END LOOP;
2408
2409 RETURN TRUE;
2410
2411 EXCEPTION
2412 WHEN OTHERS THEN
2413
2414 IF(c_synch_launchpad%ISOPEN) THEN
2415 CLOSE c_synch_launchpad;
2416 END IF;
2417
2418 ROLLBACK;
2419 FND_MSG_PUB.Count_And_Get
2420 ( p_count => l_msg_count
2421 ,p_data => l_msg_data
2422 );
2423 IF (l_msg_data IS NULL) THEN
2424 l_msg_data := SQLERRM;
2425 end if;
2426 x_error_msg := l_msg_data;
2427 RETURN FALSE;
2428 END Add_Access_To_Launchpads;
2429
2430
2431 /*******************************************************************************
2432 ********************************************************************************/
2433
2434 FUNCTION Upgrade_Bsc_Pmf_dim_Views(
2435 x_error_msg OUT NOCOPY VARCHAR2
2436 ) RETURN BOOLEAN IS
2437 l_return_status VARCHAR2(3000);
2438 l_msg_count NUMBER;
2439 l_msg_data VARCHAR2(3000);
2440 BEGIN
2441 -- ADRAO modified signature for modularization, Bug#3739872
2442 -- NULL indicates process 'all' the dimension object views
2443 BSC_BIS_DIM_OBJ_PUB.Refresh_BSC_PMF_Dim_View
2444 ( p_Short_Name => NULL
2445 , x_return_status => l_return_status
2446 , x_msg_count => l_msg_count
2447 , x_msg_data => l_msg_data
2448 );
2449 COMMIT;
2450 RETURN TRUE;
2451 EXCEPTION
2452 WHEN OTHERS THEN
2453 RETURN FALSE;
2454 END Upgrade_Bsc_Pmf_dim_Views;
2455
2456 /*******************************************************************************
2457 ********************************************************************************/
2458
2459 FUNCTION Remove_Bsc_Pmf_Edw_dim_Views(
2460 x_error_msg OUT NOCOPY VARCHAR2
2461 ) RETURN BOOLEAN IS
2462 l_return_status VARCHAR2(3000);
2463 l_msg_count NUMBER;
2464 l_msg_data VARCHAR2(3000);
2465 BEGIN
2466 BSC_BIS_DIM_OBJ_PUB.Remove_BSC_PMF_EDW_Dim_View
2467 ( x_return_status => l_return_status
2468 , x_msg_count => l_msg_count
2469 , x_msg_data => x_error_msg
2470 );
2471 COMMIT;
2472 RETURN TRUE;
2473 EXCEPTION
2474 WHEN OTHERS THEN
2475 RETURN FALSE;
2476 END Remove_Bsc_Pmf_Edw_dim_Views;
2477
2478 /********************************************************************************************
2479 This procedure identifies the BSC Measures with application_ids -1 and updates them
2480 to 271.
2481 ********************************************************************************************/
2482 FUNCTION update_Bsc_Application_Ids
2483 (
2484 x_error_msg OUT NOCOPY VARCHAR2
2485 )RETURN BOOLEAN IS
2486 l_return_Status BOOLEAN;
2487 CURSOR c_update_appid IS
2488 SELECT bisapp.indicator_id
2489 FROM bis_application_measures bisapp,
2490 bis_indicators bisindic,
2491 bsc_sys_datasets_vl bsc
2492 WHERE bisapp.indicator_id = bisindic.indicator_id
2493 AND bisindic.dataset_id = bsc.dataset_id
2494 AND bsc.source= 'BSC'
2495 AND (bisapp.application_id = -1 OR bisapp.application_id IS NULL);
2496
2497 BEGIN
2498 l_return_Status := TRUE;
2499 FOR cd IN c_update_appid LOOP
2500 UPDATE bis_application_measures
2501 SET application_id = 271
2502 where indicator_id = cd.indicator_id;
2503 END LOOP;
2504
2505 COMMIT;
2506 RETURN l_return_Status;
2507 EXCEPTION
2508 WHEN OTHERS THEN
2509 IF(c_update_appid%ISOPEN) THEN
2510 CLOSE c_update_appid;
2511 END IF;
2512 ROLLBACK;
2513 x_error_msg := SQLERRM;
2514 RETURN FALSE;
2515 END update_Bsc_Application_Ids;
2516
2517
2518
2519 /************************************************************************
2520 Fucntion : Validate_And_Get_Short_Name
2521 Parameters : p_Short_Name --> Measure Short Name
2522 Description :This function will return the unique measure short_name
2523 Author : Ashankar fix for the bug 4054812
2524 /************************************************************************/
2525
2526 FUNCTION Validate_And_Get_Short_Name
2527 (
2528 p_Short_Name IN BIS_INDICATORS.short_name%TYPE
2529 ) RETURN VARCHAR2
2530 IS
2531 l_measure_short_name BIS_INDICATORS.short_name%TYPE;
2532 l_temp_var BIS_INDICATORS.short_name%TYPE;
2533 l_alias VARCHAR2(5);
2534 l_flag BOOLEAN;
2535 l_count NUMBER;
2536
2537 BEGIN
2538 l_flag := TRUE;
2539 l_alias := NULL;
2540 l_temp_var := p_Short_Name;
2541
2542 WHILE (l_flag) LOOP
2543 SELECT count(1)
2544 INTO l_count
2545 FROM BIS_INDICATORS
2546 WHERE UPPER(TRIM(Short_Name)) = UPPER(TRIM(l_temp_var));
2547
2548 IF (l_count = 0) THEN
2549 l_flag := FALSE;
2550 l_measure_short_name := l_temp_var;
2551 END IF;
2552 l_alias := BSC_UTILITY.get_Next_Alias(l_alias);
2553 l_temp_var := l_measure_short_name||l_alias;
2554 END LOOP;
2555
2556 RETURN l_measure_short_name;
2557
2558 EXCEPTION
2559 WHEN OTHERS THEN
2560 RETURN l_measure_short_name;
2561
2562 END Validate_And_Get_Short_Name;
2563
2564
2565 /*******************************************************************************
2566 Refresh_Measure_Col_Names API ensures that all the PMF measures that were
2567 generated using SHORT_NAME for BSC_SYS_MEASURES.MEASURE_COL is modified to
2568 more intelligible names which is ideally derived from the name of the measure.
2569
2570 These columns that will be generated will ensure that the MEASURE_COL is derived
2571 from NAME of the measure uniquely. This API as standalone does not have any
2572 impact on the Existing source measure part of the world. This API should be used
2573 in combination with Gen_Existing_Measure_Cols to ensure corresponding
2574 measure columns are generated for the PMF Measure (Existing SourcE)
2575 Added as part of Enhancement Bug#4239216
2576 ********************************************************************************/
2577 PROCEDURE Refresh_Measure_Col_Names
2578 IS
2579 CURSOR cPMFMeasures IS
2580 SELECT
2581 M.MEASURE_ID,
2582 M.MEASURE_COL,
2583 M.SOURCE,
2584 I.SHORT_NAME,
2585 I.DATASET_ID,
2586 I.NAME
2587 FROM
2588 BSC_SYS_MEASURES M,
2589 BSC_SYS_DATASETS_VL D,
2590 BIS_INDICATORS_VL I
2591 WHERE
2592 M.MEASURE_ID = D.MEASURE_ID1 AND
2593 D.DATASET_ID=I.DATASET_ID AND
2594 M.SOURCE = BSC_BIS_MEASURE_PUB.c_PMF AND
2595 M.MEASURE_COL = I.SHORT_NAME ORDER BY I.DATASET_ID;
2596 /*SELECT
2597 M.MEASURE_ID,
2598 M.MEASURE_COL,
2599 M.SOURCE,
2600 I.SHORT_NAME,
2601 I.DATASET_ID,
2602 I.NAME
2603 FROM
2604 BSC_SYS_MEASURES M,
2605 BIS_INDICATORS_VL I
2606 WHERE
2607 M.SHORT_NAME = I.SHORT_NAME AND
2608 M.SOURCE = BSC_BIS_MEASURE_PUB.c_PMF AND
2609 M.MEASURE_COL = I.SHORT_NAME ORDER BY I.DATASET_ID;*/
2610 l_Measure_Col BSC_SYS_MEASURES.MEASURE_COL%TYPE;
2611 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
2612 l_Return_Status VARCHAR2(1);
2613 l_Msg_Count NUMBER;
2614 l_Msg_Data VARCHAR2(2000);
2615 BEGIN
2616 SAVEPOINT SP_REFRESH_MEAS;
2617 FOR cPMFM IN cPMFMeasures LOOP
2618 l_Measure_Col := BSC_BIS_MEASURE_PUB.Get_Measure_Col(
2619 cPMFM.NAME,
2620 cPMFM.SOURCE,
2621 cPMFM.MEASURE_ID,
2622 cPMFM.SHORT_NAME
2623 );
2624 IF(l_Measure_Col IS NOT NULL) THEN
2625 l_Dataset_Rec.Bsc_Measure_Id := cPMFM.MEASURE_ID;
2626 l_Dataset_Rec.Bsc_Measure_Col := l_Measure_Col;
2627 BSC_DATASETS_PVT.Update_Measures(
2628 p_commit => FND_API.G_FALSE
2629 ,p_Dataset_Rec => l_Dataset_Rec
2630 ,x_return_status => l_Return_Status
2631 ,x_msg_count => l_Msg_Count
2632 ,x_msg_data => l_Msg_Data
2633 );
2634 IF ((l_Return_Status IS NOT NULL) AND (l_Return_Status <> FND_API.G_RET_STS_SUCCESS)) THEN
2635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2636 END IF;
2637 END IF;
2638 END LOOP;
2639 COMMIT;
2640 EXCEPTION
2641 WHEN OTHERS THEN
2642 ROLLBACK TO SP_REFRESH_MEAS;
2643 --x_error_msg := SQLERRM;
2644 IF (l_Msg_Data IS NULL) THEN
2645 l_Msg_Data := SQLERRM;
2646 END IF;
2647 BSC_MESSAGE.Add (
2648 x_message => l_Msg_Data
2649 , x_source => 'Refresh_Measure_Col_Names'
2650 , x_mode => 'I'
2651 );
2652 END Refresh_Measure_Col_Names;
2653 /*******************************************************************************
2654 This PL/SQL API has been designer to generate new DB Column entries in
2655 BSC_DB_MEASURE_COLS_TL table, which will be used by Generate Database to
2656 directly run on exisitng type of measures.
2657 This API should not be called without calling Refresh_Measure_Col_Names,
2658 Though this API can run independently, it will generate column that are
2659 available directly in BSC_SYS_MEASURES.MEASURE_COL (which ideally may not
2660 be intelligible as a TABLE COLUMN). Hence it is *recommened* that
2661 the API Refresh_Measure_Col_Names is run before this API is run.
2662 Added as part of Enhancement Bug#4239216
2663 ********************************************************************************/
2664 PROCEDURE Gen_Existing_Measure_Cols
2665 IS
2666 CURSOR cPMFMeasures IS
2667 SELECT
2668 D.MEASURE_ID1,
2669 M.MEASURE_COL,
2670 D.SOURCE
2671 FROM
2672 BSC_SYS_DATASETS_B D,
2673 BSC_SYS_MEASURES M
2674 WHERE
2675 D.SOURCE = BSC_BIS_MEASURE_PUB.c_PMF AND
2676 M.MEASURE_ID = D.MEASURE_ID1;
2677 l_Measure_Col BSC_SYS_MEASURES.MEASURE_COL%TYPE;
2678 l_Return_Status VARCHAR2(1);
2679 l_Msg_Count NUMBER;
2680 l_Msg_Data VARCHAR2(2000);
2681 l_Count NUMBER;
2682 l_Measure_Group_Id BSC_DB_MEASURE_COLS_TL.MEASURE_GROUP_ID%TYPE;
2683 l_Projection_Id BSC_DB_MEASURE_COLS_TL.PROJECTION_ID%TYPE;
2684 l_Measure_Type BSC_DB_MEASURE_COLS_TL.MEASURE_TYPE%TYPE;
2685 BEGIN
2686 SAVEPOINT SP_GEN_EXISTING;
2687 l_Count := 0; -- BSC_DB_MEASURE_COLS_PKG
2688 -- setup defaults
2689 l_Measure_Group_Id := -1; -- default group
2690 l_Projection_Id := 0; -- Indicates no projection
2691 l_Measure_Type := 1; -- activity type by default
2692 FOR cPMFM IN cPMFMeasures LOOP
2693 SELECT COUNT(1) INTO l_Count
2694 FROM BSC_DB_MEASURE_COLS_VL B
2695 WHERE UPPER(B.MEASURE_COL) = UPPER(cPMFM.MEASURE_COL);
2696 -- need to create a new MEASURE COULIMN
2697 IF(l_Count = 0) THEN
2698 BEGIN
2699 BSC_DB_MEASURE_COLS_PKG.INSERT_ROW (
2700 x_Measure_Col => cPMFM.MEASURE_COL
2701 , x_Measure_Group_Id => l_Measure_Group_Id
2702 , x_Projection_Id => l_Projection_Id
2703 , x_Measure_Type => l_Measure_Type
2704 , x_Help => cPMFM.MEASURE_COL
2705 );
2706 EXCEPTION
2707 WHEN OTHERS THEN
2708 BSC_MESSAGE.Add (
2709 x_message => SQLERRM || ' - ERROR ADDING COL : ' || cPMFM.MEASURE_COL
2710 , x_source => 'BSC_DB_MEASURE_COLS_PKG.INSERT_ROW'
2711 , x_mode => 'I'
2712 );
2713 END;
2714 END IF;
2715 END LOOP;
2716 COMMIT;
2717 EXCEPTION
2718 WHEN OTHERS THEN
2719 ROLLBACK TO SP_GEN_EXISTING;
2720 IF (l_Msg_Data IS NULL) THEN
2721 l_Msg_Data := SQLERRM;
2722 END IF;
2723 BSC_MESSAGE.Add (
2724 x_message => l_Msg_Data
2725 , x_source => 'Gen_Existing_Measure_Cols'
2726 , x_mode => 'I'
2727 );
2728 END Gen_Existing_Measure_Cols;
2729
2730
2731 PROCEDURE sync_dim_object_mappings
2732 ( p_dim_short_name IN VARCHAR2
2733 , x_return_status OUT NOCOPY VARCHAR2
2734 , x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2735 )
2736 IS
2737 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2738
2739 CURSOR c_bsc_rel is
2740 SELECT bsc_lvl.short_name, bis_dim.dimension_id
2741 FROM bsc_sys_dim_groups_vl bsc_dim,
2742 bsc_sys_dim_levels_b bsc_lvl,
2743 bsc_sys_dim_levels_by_group lvl_by_grp,
2744 bis_dimensions bis_dim
2745 WHERE bsc_dim.dim_group_id = lvl_by_grp.dim_group_id
2746 AND bsc_lvl.dim_level_id = lvl_by_grp.dim_level_id
2747 /* AND bsc_dim.dim_group_id = bis_dim.dim_grp_id cannot use since dim_group_id is not updated on BIS side yet */
2748 AND bsc_dim.short_name = bis_dim.short_name /* can assume here since short name are same on both side */
2749 AND bsc_dim.short_name = p_dim_short_name ;
2750
2751 BEGIN
2752
2753 FOR l_bsc_rel_rec IN c_bsc_rel LOOP
2754
2755 UPDATE bis_levels
2756 SET dimension_id = l_bsc_rel_rec.dimension_id
2757 WHERE short_name = l_bsc_rel_rec.short_name
2758 AND dimension_id = -1 ;
2759
2760 END LOOP;
2761
2762 EXCEPTION
2763 WHEN NO_DATA_FOUND THEN
2764 x_return_status := FND_API.G_RET_STS_ERROR ;
2765 WHEN FND_API.G_EXC_ERROR THEN
2766 x_return_status := FND_API.G_RET_STS_ERROR ;
2767 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2769 WHEN OTHERS THEN
2770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2771 l_error_tbl := x_error_Tbl;
2772 BIS_UTILITIES_PVT.Add_Error_Message
2773 ( p_error_msg_id => SQLCODE
2774 , p_error_description => SQLERRM
2775 , p_error_proc_name => G_PKG_NAME||'.sync_dim_object_mappings'
2776 , p_error_table => l_error_tbl
2777 , x_error_table => x_error_tbl
2778 );
2779 END sync_dim_object_mappings;
2780
2781 FUNCTION Update_Dim_Hide_Properties (
2782 x_error_msg OUT NOCOPY VARCHAR2
2783 ) RETURN BOOLEAN
2784 IS
2785 TYPE t_short_names IS TABLE OF BIS_DIMENSIONS.SHORT_NAME%TYPE INDEX BY BINARY_INTEGER;
2786 l_dim_short_names t_short_names;
2787 l_row_count NUMBER := 0;
2788
2789 CURSOR cr_internal_dimensions IS
2790 SELECT
2791 short_name
2792 FROM
2793 bis_dimensions
2794 WHERE
2795 (bsc_utility.Is_Internal_AG_Dim(short_name) IS NOT NULL OR
2796 bsc_utility.Is_Internal_BIS_Import_Dim(short_name) IS NOT NULL OR
2797 bsc_utility.Is_Internal_WKPI_Dim(short_name) IS NOT NULL)
2798 AND bis_util.is_Seeded(created_by,'T','F') = 'F'
2799 AND NVL(hide_in_design,'F') = 'F';
2800 BEGIN
2801 FND_MSG_PUB.Initialize;
2802 SAVEPOINT BisHideInDesignUpdate;
2803 OPEN cr_internal_dimensions;
2804 LOOP
2805 FETCH cr_internal_dimensions
2806 BULK COLLECT INTO l_dim_short_names
2807 LIMIT 100;
2808 EXIT WHEN l_row_count = cr_internal_dimensions%ROWCOUNT;
2809 l_row_count := cr_internal_dimensions%ROWCOUNT;
2810
2811 FORALL i in 1..l_dim_short_names.COUNT
2812 UPDATE BIS_DIMENSIONS
2813 SET HIDE_IN_DESIGN = FND_API.G_TRUE
2814 WHERE SHORT_NAME = l_dim_short_names(i);
2815 END LOOP;
2816
2817 CLOSE cr_internal_dimensions;
2818 x_error_msg := 'BSC_UPGRADES.Update_Dim_Hide_Properties Successfully Completed';
2819 RETURN TRUE;
2820 EXCEPTION
2821 WHEN OTHERS THEN
2822 IF (cr_internal_dimensions%ISOPEN) THEN
2823 CLOSE cr_internal_dimensions;
2824 END IF;
2825 ROLLBACK TO BisHideInDesignUpdate;
2826 IF (x_error_msg IS NOT NULL) THEN
2827 x_error_msg := x_error_msg||' -> BSC_UPGRADES.Update_Dim_Hide_Properties ';
2828 ELSE
2829 x_error_msg := SQLERRM||' at BSC_UPGRADES.Update_Dim_Hide_Properties ';
2830 END IF;
2831 RETURN FALSE;
2832 END Update_Dim_Hide_Properties;
2833
2834 FUNCTION Hide_Unused_Import_Dim(
2835 x_error_msg OUT NOCOPY VARCHAR2
2836 ) RETURN BOOLEAN
2837 IS
2838 TYPE t_short_names IS TABLE OF BSC_SYS_DIM_GROUPS_VL.SHORT_NAME%TYPE INDEX BY BINARY_INTEGER;
2839 l_dim_short_names t_short_names;
2840 l_row_count NUMBER := 0;
2841 l_region_count NUMBER;
2842
2843 l_return_status VARCHAR2(1);
2844 l_msg_count NUMBER;
2845 l_msg_data VARCHAR2(3200);
2846
2847 CURSOR cr_pmf_import_dims IS
2848 SELECT
2849 short_name
2850 FROM
2851 bsc_sys_dim_groups_vl grp
2852 WHERE
2853 bsc_bis_dimension_pub.get_dimension_source(short_name)='PMF' AND
2854 short_name LIKE 'DGRP_%' AND
2855 name = short_name AND
2856 bis_util.is_seeded(created_by,'T','F') = 'F' AND
2857 (SELECT COUNT(1) FROM bsc_kpi_dim_groups WHERE dim_group_id = grp.dim_group_id) = 0;
2858 BEGIN
2859 FND_MSG_PUB.Initialize;
2860 SAVEPOINT BisUpdateImportDim;
2861 OPEN cr_pmf_import_dims;
2862 LOOP
2863 FETCH cr_pmf_import_dims
2864 BULK COLLECT INTO l_dim_short_names
2865 LIMIT 100;
2866 EXIT WHEN l_row_count = cr_pmf_import_dims%ROWCOUNT;
2867 l_row_count := cr_pmf_import_dims%ROWCOUNT;
2868
2869 FOR i in 1..l_dim_short_names.COUNT LOOP
2870 UPDATE BIS_DIMENSIONS
2871 SET HIDE_IN_DESIGN = FND_API.G_TRUE
2872 WHERE SHORT_NAME = l_dim_short_names(i);
2873 END LOOP;
2874 END LOOP;
2875 CLOSE cr_pmf_import_dims;
2876 x_error_msg := 'BSC_UPGRADES.Hide_Unused_Pmf_Import_Dim Successfully Completed';
2877 RETURN TRUE;
2878 EXCEPTION
2879 WHEN OTHERS THEN
2880 IF (cr_pmf_import_dims%ISOPEN) THEN
2881 CLOSE cr_pmf_import_dims;
2882 END IF;
2883 ROLLBACK TO BisUpdateImportDim;
2884 IF (x_error_msg IS NOT NULL) THEN
2885 x_error_msg := x_error_msg||' -> BSC_UPGRADES.Hide_Unused_Import_Dim';
2886 ELSE
2887 x_error_msg := SQLERRM||' at BSC_UPGRADES.Hide_Unused_Import_Dim';
2888 END IF;
2889 RETURN FALSE;
2890 END Hide_Unused_Import_Dim;
2891
2892 PROCEDURE Drop_Update_Dim_Obj_Views(
2893 p_Dim_Obj_Sht_Name IN OUT NOCOPY FND_TABLE_OF_VARCHAR2_30
2894 , x_return_status OUT NOCOPY VARCHAR2
2895 , x_msg_count OUT NOCOPY NUMBER
2896 , x_msg_data OUT NOCOPY VARCHAR2
2897 ) IS
2898 l_sql VARCHAR2(2000);
2899 l_level_view_name bsc_sys_dim_levels_b.level_view_name%TYPE;
2900 l_table_type bsc_sys_dim_levels_b.table_type%TYPE;
2901
2902 CURSOR c_dim_obj(p_sht_name VARCHAR2) IS
2903 SELECT level_view_name,table_type
2904 FROM bsc_sys_dim_levels_b
2905 WHERE short_name = p_sht_name;
2906
2907 BEGIN
2908 FND_MSG_PUB.Initialize;
2909 x_return_status := FND_API.G_RET_STS_SUCCESS;
2910
2911 FOR i in p_Dim_Obj_Sht_Name.FIRST..p_Dim_Obj_Sht_Name.LAST
2912 LOOP
2913 l_level_view_name := NULL;
2914 OPEN c_dim_obj(p_Dim_Obj_Sht_Name(i));
2915 FETCH c_dim_obj INTO l_level_view_name,l_table_type;
2916 CLOSE c_dim_obj;
2917 IF (l_level_view_name IS NOT NULL AND l_table_type = 1) THEN
2918 l_sql := 'DROP VIEW '||l_level_view_name;
2919 BEGIN
2920 BSC_APPS.Do_DDL(l_sql, AD_DDL.DROP_VIEW, l_level_view_name);
2921 EXCEPTION
2922 WHEN OTHERS THEN
2923 NULL;
2924 END;
2925 UPDATE bsc_sys_dim_levels_b
2926 SET table_type = -1
2927 WHERE short_name = p_Dim_Obj_Sht_Name(i);
2928 END IF;
2929 END LOOP;
2930 EXCEPTION
2931 WHEN OTHERS THEN
2932 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2933 IF (x_msg_data IS NOT NULL) THEN
2934 x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Drop_Update_Dim_Obj_Views ';
2935 ELSE
2936 x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Drop_Update_Dim_Obj_Views ';
2937 END IF;
2938 END Drop_Update_Dim_Obj_Views;
2939
2940 end BSC_UPGRADES;
[Home] [Help]