[Home] [Help]
PACKAGE BODY: APPS.BSC_BIS_LOCKS_PVT
Source
1 PACKAGE BODY BSC_BIS_LOCKS_PVT as
2 /* $Header: BSCVLOCB.pls 120.1 2005/07/12 08:48:34 adrao noship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCPLOCB.pls |
10 | |
11 | Creation Date: |
12 | Mayo 27 , 2003 |
13 | |
14 | Creator: |
15 | William Cano |
16 | |
17 | Description: |
18 | Private Body Version. |
19 | This package is used for Locking all PMD Entities |
20 | |
21 | History: |
22 | 10-JUN-03 Aditya modified procedure SET_TIME_STAMP_KPI |
23 | 18=JUN-03 Aditya added SAVEPOINT and ROLLBACK |
24 | 29-JUN-03 Aditya fixed GET_TIME_STAMP_KPI() procedure |
25 | 29-JUL-03 Aditya fixed for bug #3047483
26 | 20-OCT-03 Kyadamak fixed for the bug #3269334
27 | 11-DEC-03 Pradeep Correct Message for Locked Tab bug #3299614
28 | 20-Dec-03 Sawu Overloaded Set_Time_Stamp_Dataset and Set_Time_Stamp_Datasource
29 | for bug#4045278
30 | 04-JUL-05 Aditya Rao added Calendar and Periodicity Locking APIs |
31 +======================================================================================+
32 */
33
34 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_BIS_LOCKS_PVT';
35 g_db_object varchar2(30) := null;
36
37
38 /*-------------------------------------------------------------------------------------------------------------------
39 Procedure private functions
40 -------------------------------------------------------------------------------------------------------------------*/
41 FUNCTION get_Dataset_Name(
42 p_dataset_id IN NUMBER
43 ) RETURN VARCHAR2 IS
44 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
45 l_sql varchar2(300);
46 temp varchar2(300);
47 Begin
48 l_sql := 'SELECT NAME FROM BSC_SYS_DATASETS_VL WHERE DATASET_ID =:1';
49 open l_cursor for l_sql USING p_dataset_id;
50 fetch l_cursor into temp;
51 close l_cursor;
52 return temp;
53 EXCEPTION
54 WHEN OTHERS THEN
55 return NULL;
56 END get_DataSet_Name;
57
58 FUNCTION get_Datasource_Name(
59 p_datasource_id IN NUMBER
60 ) RETURN VARCHAR2 IS
61 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
62 l_sql varchar2(300);
63 temp varchar2(1000);
64 Begin
65 l_sql := 'SELECT MEASURE_COL FROM BSC_SYS_MEASURES WHERE MEASURE_ID =:1';
66 open l_cursor for l_sql USING p_datasource_id;
67 fetch l_cursor into temp;
68 close l_cursor;
69 return temp;
70 EXCEPTION
71 WHEN OTHERS THEN
72 return NULL;
73 END get_Datasource_Name;
74
75 /*------------------------------------------------------------------------------------------
76 Getting Time Stamp for Dataset
77 -------------------------------------------------------------------------------------------*/
78 Function GET_TIME_STAMP_DATASET (
79 p_dim_set_id IN number
80 ) return varchar2 is
81 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
82 l_sql varchar2(32000);
83 object_id number;
84 temp date;
85 Begin
86 l_sql := '
87 SELECT LAST_UPDATE_DATE
88 FROM BSC_SYS_DATASETS_B
89 WHERE DATASET_ID =:1';
90
91
92 open l_cursor for l_sql USING p_dim_set_id;
93 fetch l_cursor into temp;
94 close l_cursor;
95 return TO_CHAR(temp, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT);
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 return NULL;
100
101 end GET_TIME_STAMP_DATASET;
102 /*------------------------------------------------------------------------------------------
103 Getting Time Stamp for Datasource
104 -------------------------------------------------------------------------------------------*/
105 Function GET_TIME_STAMP_DATASOURCE (
106 p_measure_id IN number
107 ) return varchar2 is
108 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
109 l_sql varchar2(32000);
110 object_id number;
111 temp date;
112 Begin
113 l_sql := '
114 SELECT LAST_UPDATE_DATE
115 FROM BSC_SYS_MEASURES
116 WHERE MEASURE_ID =:1';
117
118 open l_cursor for l_sql USING p_measure_id;
119 fetch l_cursor into temp;
120 close l_cursor;
121 return TO_CHAR(temp, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT);
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 return NULL;
126
127 end GET_TIME_STAMP_DATASOURCE;
128 /*------------------------------------------------------------------------------------------
129 Setting Time Stamp for Data set
130 -------------------------------------------------------------------------------------------*/
131 Procedure SET_TIME_STAMP_DATASET (
132 p_dim_set_id IN number
133 ,x_return_status OUT NOCOPY varchar2
134 ,x_msg_count OUT NOCOPY number
135 ,x_msg_data OUT NOCOPY varchar2
136 ) is
137 l_sql varchar2(32000);
138 begin
139 SAVEPOINT BSCSetTimeDataSetPVT;
140
141 BSC_BIS_LOCKS_PVT.SET_TIME_STAMP_DATASET (
142 p_dim_set_id => p_dim_set_id
143 ,p_lud => sysdate
144 ,x_return_status => x_return_status
145 ,x_msg_count => x_msg_count
146 ,x_msg_data => x_msg_data
147 );
148
149 EXCEPTION
150 WHEN FND_API.G_EXC_ERROR THEN
151 ROLLBACK TO BSCSetTimeDataSetPVT;
152 x_return_status := FND_API.G_RET_STS_ERROR;
153 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
154 ,p_data => x_msg_data);
155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
156 ROLLBACK TO BSCSetTimeDataSetPVT;
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
159 ,p_data => x_msg_data);
160 WHEN OTHERS THEN
161 ROLLBACK TO BSCSetTimeDataSetPVT;
162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
163 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
164 ,p_data => x_msg_data);
165
166 end SET_TIME_STAMP_DATASET;
167
168 /*------------------------------------------------------------------------------------------
169 Bug#4045278: Overloaded for setting Time Stamp for Dataset to take in last_update_date parameter
170 -------------------------------------------------------------------------------------------*/
171 Procedure SET_TIME_STAMP_DATASET (
172 p_dim_set_id IN number
173 ,p_lud IN BSC_SYS_DATASETS_B.LAST_UPDATE_DATE%TYPE
174 ,x_return_status OUT NOCOPY varchar2
175 ,x_msg_count OUT NOCOPY number
176 ,x_msg_data OUT NOCOPY varchar2
177 ) is
178 l_sql varchar2(32000);
179 begin
180 SAVEPOINT BSCSetTimeDataSetPVT;
181
182 l_sql := '
183 UPDATE BSC_SYS_DATASETS_B
184 SET LAST_UPDATE_DATE = :1
185 WHERE DATASET_ID =:2';
186 execute immediate l_sql USING p_lud, p_dim_set_id;
187
188 EXCEPTION
189 WHEN FND_API.G_EXC_ERROR THEN
190 ROLLBACK TO BSCSetTimeDataSetPVT;
191 x_return_status := FND_API.G_RET_STS_ERROR;
192 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
193 ,p_data => x_msg_data);
194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
195 ROLLBACK TO BSCSetTimeDataSetPVT;
196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
197 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
198 ,p_data => x_msg_data);
199 WHEN OTHERS THEN
200 ROLLBACK TO BSCSetTimeDataSetPVT;
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
202 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
203 ,p_data => x_msg_data);
204
205 end SET_TIME_STAMP_DATASET;
206 /*------------------------------------------------------------------------------------------
207 Setting Time Stamp for Datasource
208 -------------------------------------------------------------------------------------------*/
209 Procedure SET_TIME_STAMP_DATASOURCE (
210 p_measure_id IN number
211 ,x_return_status OUT NOCOPY varchar2
212 ,x_msg_count OUT NOCOPY number
213 ,x_msg_data OUT NOCOPY varchar2
214 ) is
215 l_sql varchar2(32000);
216 begin
217
218 SAVEPOINT BSCSetTimeDataSrcPVT;
219
220 BSC_BIS_LOCKS_PVT.SET_TIME_STAMP_DATASOURCE (
221 p_measure_id => p_measure_id
222 ,p_lud => sysdate
223 ,x_return_status => x_return_status
224 ,x_msg_count => x_msg_count
225 ,x_msg_data => x_msg_data
226 );
227
228 EXCEPTION
229 WHEN FND_API.G_EXC_ERROR THEN
230 ROLLBACK TO BSCSetTimeDataSrcPVT;
231 x_return_status := FND_API.G_RET_STS_ERROR;
232 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
233 ,p_data => x_msg_data);
234 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
235 ROLLBACK TO BSCSetTimeDataSrcPVT;
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
238 ,p_data => x_msg_data);
239 WHEN OTHERS THEN
240 ROLLBACK TO BSCSetTimeDataSrcPVT;
241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
242 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
243 ,p_data => x_msg_data);
244
245 end SET_TIME_STAMP_DATASOURCE;
246 /*------------------------------------------------------------------------------------------
247 Bug#4045278: Overloaded for setting Time Stamp for Datasource to take in last_update_date parameter
248 -------------------------------------------------------------------------------------------*/
249 Procedure SET_TIME_STAMP_DATASOURCE (
250 p_measure_id IN number
251 ,p_lud IN BSC_SYS_MEASURES.LAST_UPDATE_DATE%TYPE
252 ,x_return_status OUT NOCOPY varchar2
253 ,x_msg_count OUT NOCOPY number
254 ,x_msg_data OUT NOCOPY varchar2
255 ) is
256 l_sql varchar2(32000);
257 begin
258
259 SAVEPOINT BSCSetTimeDataSrcPVT;
260
261 l_sql := '
262 UPDATE BSC_SYS_MEASURES
263 SET LAST_UPDATE_DATE = :1
264 WHERE MEASURE_ID =:2';
265 execute immediate l_sql USING p_lud, p_measure_id;
266
267 EXCEPTION
268 WHEN FND_API.G_EXC_ERROR THEN
269 ROLLBACK TO BSCSetTimeDataSrcPVT;
270 x_return_status := FND_API.G_RET_STS_ERROR;
271 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
272 ,p_data => x_msg_data);
273 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
274 ROLLBACK TO BSCSetTimeDataSrcPVT;
275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
276 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
277 ,p_data => x_msg_data);
278 WHEN OTHERS THEN
279 ROLLBACK TO BSCSetTimeDataSrcPVT;
280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
281 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
282 ,p_data => x_msg_data);
283
284 end SET_TIME_STAMP_DATASOURCE;
285 /*------------------------------------------------------------------------------------------
286 Procedure to Lock a Datasets
287 Lock the Dataset
288 Lock the measure(s) asociated to the Dataset
289
290 out parameter:
291 x_measure_id1 First Measure associated with the dataset
292 x_measure_id2 Second Measure associated with the dataset
293
294 -------------------------------------------------------------------------------------------------------------------*/
295 Procedure LOCK_DATASET (
296 p_dataset_id IN number
297 ,p_time_stamp IN varchar2/* := null */
298 ,x_measure_id1 OUT NOCOPY number
299 ,x_measure_id2 OUT NOCOPY number
300 ,x_return_status OUT NOCOPY varchar2
301 ,x_msg_count OUT NOCOPY number
302 ,x_msg_data OUT NOCOPY varchar2
303 ) is
304 l_object_id number;
305 l_last_update_date date;
306 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
307 l_sql varchar2(32000);
308
309 l_operation varchar2(20);
310 l_dataset_name varchar2(500);
311 l_meaning varchar2(60); -- Added by ADRAO for Delete Message.
312
313 begin
314 --dbms_output.put_line(' pvt.LOCK_DATASET p_dataset_id = ' || p_dataset_id );
315 /* Lock the Dimension Set */
316
317 SAVEPOINT BSCLockDataSetPVT;
318
319 l_sql := 'SELECT DATASET_ID, LAST_UPDATE_DATE
320 FROM BSC_SYS_DATASETS_B
321 WHERE DATASET_ID =:1
322 FOR UPDATE NOWAIT';
323
324 open l_cursor for l_sql USING p_dataset_id;
325 fetch l_cursor into l_object_id, l_last_update_date;
326 if (l_cursor%notfound) then
327 --dbms_output.put_line(' p_dataset_id = ' || p_dataset_id || ' Deleted by other user ');
328 close l_cursor;
329 FND_MSG_PUB.Initialize;
330 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE'); -- ADRAO changed Measage
331 l_meaning := bsc_apps.get_lookup_value('BSC_UI_COMMON', 'EDW_MEASURE'); -- added by ADRAO
332 FND_MESSAGE.SET_TOKEN('TYPE', l_meaning, TRUE);
333 FND_MSG_PUB.ADD;
334 x_return_status := FND_API.G_RET_STS_ERROR;
335 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
336 ,p_data => x_msg_data);
337 RAISE FND_API.G_EXC_ERROR;
338 end if;
339 if not p_time_stamp is null then
340 if p_time_stamp <> TO_CHAR(l_last_update_date, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT) then
341 --dbms_output.put_line(' p_dataset_id = ' || p_dataset_id || ' updated by other user ');
342 FND_MSG_PUB.Initialize;
343 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_MODIFIED_MEASURE');
344 FND_MESSAGE.SET_TOKEN('MEASURE', get_Dataset_Name(p_dataset_id));
345 FND_MSG_PUB.ADD;
346 x_return_status := FND_API.G_RET_STS_ERROR;
347 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
348 ,p_data => x_msg_data);
349 RAISE FND_API.G_EXC_ERROR;
350 l_last_update_date := l_last_update_date;
351 end if;
352 end if;
353
354 /* Lock the Data Sources associated with the Dataset */
355 l_sql := '
356 SELECT NAME, MEASURE_ID1, OPERATION, MEASURE_ID2
357 FROM BSC_SYS_DATASETS_VL
358 WHERE DATASET_ID =:1';
359 --dbms_output.put_line(' Flag p_dataset_id = ' || p_dataset_id );
360 open l_cursor for l_sql USING p_dataset_id;
361 fetch l_cursor into l_dataset_name, x_measure_id1, l_operation , x_measure_id2;
362 --dbms_output.put_line(' x_measure_id1 = ' || x_measure_id1 );
363 --dbms_output.put_line(' x_measure_id2 = ' || x_measure_id2 );
364 if (l_cursor%found) then
365 if x_measure_id2 is not null then
366 BSC_BIS_LOCKS_PVT.LOCK_DATASOURCE(
367 x_measure_id1
368 ,null
369 ,l_dataset_name
370 ,x_return_status
371 ,x_msg_count
372 ,x_msg_data
373 ) ;
374 end if;
375 if x_measure_id2 is not null and l_operation is not null then
376 BSC_BIS_LOCKS_PVT.LOCK_DATASOURCE(
377 x_measure_id2
378 ,null
379 ,l_dataset_name
380 ,x_return_status
381 ,x_msg_count
382 ,x_msg_data
383 ) ;
384 end if;
385
386 else
387 x_measure_id1 := null;
388 x_measure_id2 := null;
389 end if;
390 close l_cursor;
391 --dbms_output.put_line(' p_dataset_id = ' || p_dataset_id || ' successfuly locked ');
392
393
394 EXCEPTION
395 WHEN FND_API.G_EXC_ERROR THEN
396 ROLLBACK TO BSCLockDataSetPVT;
397 x_return_status := FND_API.G_RET_STS_ERROR;
398 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
399 ,p_data => x_msg_data);
400 raise;
401 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
402 ROLLBACK TO BSCLockDataSetPVT;
403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
404 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
405 ,p_data => x_msg_data);
406 raise;
407 WHEN OTHERS THEN
408 ROLLBACK TO BSCLockDataSetPVT;
409 if (SQLCODE = -00054) then
410 --dbms_output.put_line(' p_dataset_id = ' || p_dataset_id || ' locked by other user ');
411 FND_MSG_PUB.Initialize;
412 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_LOCKED_MEASURE');
413 FND_MESSAGE.SET_TOKEN('MEASURE', get_Dataset_Name(p_dataset_id));
414 FND_MSG_PUB.ADD;
415 x_return_status := FND_API.G_RET_STS_ERROR;
416 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
417 ,p_data => x_msg_data);
418 RAISE FND_API.G_EXC_ERROR;
419 end if;
420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
421 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
422 ,p_data => x_msg_data);
423 raise;
424 end LOCK_DATASET;
425
426 /*------------------------------------------------------------------------------------------
427 Procedure to Lock a Datasource
428 -------------------------------------------------------------------------------------------------------------------*/
429 Procedure LOCK_DATASOURCE(
430 p_measure_id IN number
431 ,p_time_stamp IN varchar2/* := null */
432 ,p_dataset_name IN varchar2/* := null */
433 ,x_return_status OUT NOCOPY varchar2
434 ,x_msg_count OUT NOCOPY number
435 ,x_msg_data OUT NOCOPY varchar2
436 ) is
437 l_object_id number;
438 l_last_update_date date;
439 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
440 l_sql varchar2(32000);
441 begin
442
443 --SQL statement used to lock a dimension:
444
445 SAVEPOINT BSCLockDataSrcPVT;
446
447 l_sql := '
448 SELECT MEASURE_ID, LAST_UPDATE_DATE
449 FROM BSC_SYS_MEASURES
450 WHERE MEASURE_ID =:1
451 FOR UPDATE NOWAIT';
452
453 open l_cursor for l_sql USING p_measure_id;
454 fetch l_cursor into l_object_id, l_last_update_date;
455 if (l_cursor%notfound) then
456 close l_cursor;
457 --dbms_output.put_line(' p_measure_id = ' || p_measure_id || ' deleted by other user ');
458 FND_MSG_PUB.Initialize;
459 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETED_MEASURE');
460 FND_MESSAGE.SET_TOKEN('MEASURE', nvl(p_dataset_name, nvl(get_Datasource_Name(p_measure_id),p_measure_id)));
461 FND_MSG_PUB.ADD;
462 x_return_status := FND_API.G_RET_STS_ERROR;
463 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
464 ,p_data => x_msg_data);
465 RAISE FND_API.G_EXC_ERROR;
466 end if;
467
468 if not p_time_stamp is null then
469 if p_time_stamp <> TO_CHAR(l_last_update_date, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT) then
470 --dbms_output.put_line(' p_measure_id = ' || p_measure_id || ' updated by other user ');
471 FND_MSG_PUB.Initialize;
472 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_MODIFIED_MEASURE');
473 FND_MESSAGE.SET_TOKEN('MEASURE', nvl(p_dataset_name,get_Datasource_Name(p_measure_id)));
474 FND_MSG_PUB.ADD;
475 x_return_status := FND_API.G_RET_STS_ERROR;
476 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
477 ,p_data => x_msg_data);
478 RAISE FND_API.G_EXC_ERROR;
479 l_last_update_date := l_last_update_date;
480 end if;
481 end if;
482 --dbms_output.put_line(' p_measure_id = ' || p_measure_id || ' successfuly locked ');
483
484 EXCEPTION
485 WHEN FND_API.G_EXC_ERROR THEN
486 ROLLBACK TO BSCLockDataSrcPVT;
487 x_return_status := FND_API.G_RET_STS_ERROR;
488 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
489 ,p_data => x_msg_data);
490 raise;
491 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
492 ROLLBACK TO BSCLockDataSrcPVT;
493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
494 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
495 ,p_data => x_msg_data);
496 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
497 WHEN OTHERS THEN
498 ROLLBACK TO BSCLockDataSrcPVT;
499 if (SQLCODE = -00054) then
500 --dbms_output.put_line(' p_measure_id = ' || p_measure_id || ' locked by other user ');
501 FND_MSG_PUB.Initialize;
502 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_LOCKED_MEASURE');
503 FND_MESSAGE.SET_TOKEN('MEASURE', nvl(p_dataset_name, get_Datasource_Name(p_measure_id)));
504 FND_MSG_PUB.ADD;
505 x_return_status := FND_API.G_RET_STS_ERROR;
506 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
507 ,p_data => x_msg_data);
508 RAISE FND_API.G_EXC_ERROR;
509 end if;
510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
511 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
512 ,p_data => x_msg_data);
513 raise;
514 end LOCK_DATASOURCE;
515 /*-------------------------------------------------------------------------------------------------------------------
516 Procedure private functions
517 -------------------------------------------------------------------------------------------------------------------*/
518 FUNCTION get_Dim_Level_Name(
519 p_dim_level_id IN NUMBER
520 ) RETURN VARCHAR2 IS
521 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
522 l_sql varchar2(300);
523 object_id number;
524 temp varchar2(300);
525 Begin
526 l_sql := 'SELECT NAME FROM BSC_SYS_DIM_LEVELS_VL WHERE DIM_LEVEL_ID =:1';
527 open l_cursor for l_sql USING p_dim_level_id;
528 fetch l_cursor into temp;
529 close l_cursor;
530 return temp;
531 EXCEPTION
532 WHEN OTHERS THEN
533 return NULL;
534 END get_Dim_Level_Name;
535 /*-------------------------------------------------------------------------------------------------------------------*/
536 FUNCTION get_Dim_Group_Name(
537 p_dim_group_id IN NUMBER
538 ) RETURN VARCHAR2 IS
539 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
540 l_sql varchar2(300);
541 object_id number;
542 temp varchar2(300);
543 Begin
544 l_sql := 'SELECT NAME FROM BSC_SYS_DIM_GROUPS_VL WHERE DIM_GROUP_ID =:1';
545 open l_cursor for l_sql USING p_dim_group_id;
546 fetch l_cursor into temp;
547 close l_cursor;
548 return temp;
549 EXCEPTION
550 WHEN OTHERS THEN
551 return NULL;
552 END get_Dim_Group_Name;
553 /*-------------------------------------------------------------------------------------------------------------------*/
554 FUNCTION get_Dim_Set_Name(
555 p_kpi_id IN NUMBER
556 ,p_dim_set_id IN NUMBER
557 ) RETURN VARCHAR2 IS
558 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
559 l_sql varchar2(300);
560 object_id number;
561 temp varchar2(300);
562 Begin
563 l_sql := 'SELECT NAME FROM bsc_kpi_dim_sets_vl
564 WHERE INDICATOR =:1 AND DIM_SET_ID =:2';
565 open l_cursor for l_sql USING p_kpi_id, p_dim_set_id;
566 fetch l_cursor into temp;
567 close l_cursor;
568 return temp;
569 EXCEPTION
570 WHEN OTHERS THEN
571 return NULL;
572 END get_Dim_Set_Name;
573 /*-------------------------------------------------------------------------------------------------------------------*/
574
575 FUNCTION get_KPI_Name(
576 p_kpi_id IN NUMBER
577 ) RETURN VARCHAR2 IS
578 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
579 l_sql varchar2(300);
580 object_id number;
581 temp varchar2(300);
582 Begin
583 l_sql := 'SELECT NAME FROM BSC_KPIS_VL WHERE INDICATOR =:1';
584 open l_cursor for l_sql USING p_kpi_id;
585 fetch l_cursor into temp;
586 close l_cursor;
587 return temp;
588 EXCEPTION
589 WHEN OTHERS THEN
590 return NULL;
591 END get_KPI_Name;
592
593 /*************************************************************************
594
595
596 /*************************************************************************/
597
598 FUNCTION get_TabView_Name(
599 p_tab_id IN NUMBER
600 ,p_tab_view_id IN NUMBER
601 ) RETURN VARCHAR2 IS
602 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
603 l_sql varchar2(300);
604 object_id number;
605 temp varchar2(300);
606 Begin
607 l_sql := 'SELECT NAME FROM BSC_TAB_VIEWS_VL WHERE TAB_ID =:1 AND TAB_VIEW_ID=:2';
608 open l_cursor for l_sql USING p_tab_id,p_tab_view_id;
609 fetch l_cursor into temp;
610 close l_cursor;
611 return temp;
612 EXCEPTION
613 WHEN OTHERS THEN
614 return NULL;
615 END get_TabView_Name;
616
617
618 /*-------------------------------------------------------------------------------------------------------------------*/
619 Procedure get_selected_dim_objs(
620 p_dimension_id IN NUMBER
621 ,x_selected_dim_objs OUT NOCOPY t_lock_table
622 ,x_return_status OUT NOCOPY varchar2
623 ,x_msg_count OUT NOCOPY number
624 ,x_msg_data OUT NOCOPY varchar2
625 ) IS
626 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
627 l_sql varchar2(300);
628 l_lock_Rec t_lock_Rec;
629 l_count number;
630
631 Begin
632 l_sql := '
633 SELECT DIM_LEVEL_ID
634 FROM BSC_sys_dim_levels_by_group
635 WHERE DIM_GROUP_ID =:1
636 ORDER BY DIM_LEVEL_INDEX';
637
638 open l_cursor for l_sql USING p_dimension_id;
639 l_count :=1;
640 LOOP
641 fetch l_cursor into l_lock_Rec.obj_key1;
642 exit when l_cursor%NOTFOUND;
643 l_lock_Rec.obj_index := l_count;
644 x_selected_dim_objs(l_lock_Rec.obj_key1) := l_lock_Rec;
645 l_count := l_count + 1;
646 END LOOP;
647 close l_cursor;
648
649 EXCEPTION
650 WHEN OTHERS THEN
651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
652 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
653 ,p_data => x_msg_data);
654 raise;
655 END get_selected_dim_objs;
656
657 /*-------------------------------------------------------------------------------------------------------------------*/
658 Procedure get_selected_dimensions(
659 p_dim_obj_id IN NUMBER
660 ,x_selected_dimensions OUT NOCOPY t_lock_table
661 ,x_return_status OUT NOCOPY varchar2
662 ,x_msg_count OUT NOCOPY number
663 ,x_msg_data OUT NOCOPY varchar2
664 ) IS
665 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
666 l_sql varchar2(300);
667 l_lock_Rec t_lock_Rec;
668 l_count number;
669
670 Begin
671 l_sql := '
672 SELECT DIM_GROUP_ID
673 FROM BSC_sys_dim_levels_by_group
674 WHERE DIM_LEVEL_ID =:1';
675
676 open l_cursor for l_sql USING p_dim_obj_id;
677 l_count :=1;
678 LOOP
679 fetch l_cursor into l_lock_Rec.obj_key1;
680 exit when l_cursor%NOTFOUND;
681 l_lock_Rec.obj_index := l_count;
682 x_selected_dimensions(l_lock_Rec.obj_key1) := l_lock_Rec;
683 l_count := l_count + 1;
684 END LOOP;
685 close l_cursor;
686
687 EXCEPTION
688 WHEN OTHERS THEN
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
690 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
691 ,p_data => x_msg_data);
692 raise;
693 END get_selected_dimensions;
694
695 /*----------------------------------------------------------------------------
696 It compare selected and previous objects and return the
697 impacted object.
698 It used t_lock_Rec.obj_Flag : 'D'= Delected , 'A'= Added
699 object Id is used as t_lock_table index
700 ----------------------------------------------------------------------------*/
701
702 Procedure get_impacted_objects(
703 p_selected_objects IN t_lock_table
704 ,p_previous_objects IN t_lock_table
705 ,x_impacted_objects OUT NOCOPY t_lock_table
706 ,x_return_status OUT NOCOPY varchar2
707 ,x_msg_count OUT NOCOPY number
708 ,x_msg_data OUT NOCOPY varchar2
709 ) IS
710 l_lock_Rec t_lock_Rec;
711 l_count number;
712 object_id number;
713
714 Begin
715
716 /* Find Added Objects */
717 if p_selected_objects.COUNT > 0 then
718 object_id := p_selected_objects.FIRST;
719 LOOP
720 if NOT p_previous_objects.EXISTS(object_id) then
721 l_lock_Rec := p_selected_objects(object_id);
722 l_lock_Rec.obj_Flag := 'A';
723 x_impacted_objects(object_id) := l_lock_Rec;
724 END IF;
725 IF object_id = p_selected_objects.LAST then
726 exit;
727 end if;
728 object_id := p_selected_objects.NEXT(object_id);
729 END LOOP;
730 end if;
731 /* Find deleted objects */
732 if p_previous_objects.COUNT > 0 then
733 object_id := p_previous_objects.FIRST;
734 LOOP
735 if NOT p_selected_objects.EXISTS(object_id) then
736 l_lock_Rec := p_previous_objects(object_id);
737 l_lock_Rec.obj_Flag := 'D';
738 x_impacted_objects(object_id) := l_lock_Rec;
739 END IF;
740 IF object_id = p_previous_objects.LAST then
741 exit;
742 end if;
743 object_id := p_previous_objects.NEXT(object_id);
744 END LOOP;
745 end if;
746
747 EXCEPTION
748 WHEN OTHERS THEN
749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
750 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
751 ,p_data => x_msg_data);
752 raise;
753 END get_impacted_objects;
754 /*----------------------------------------------------------------------------
755 Convert
756 ----------------------------------------------------------------------------*/
757 Procedure convert_table(
758 p_numberTable IN BSC_BIS_LOCKS_PUB.t_numberTable
759 ,x_lock_table OUT NOCOPY t_lock_table
760 ,x_return_status OUT NOCOPY varchar2
761 ,x_msg_count OUT NOCOPY number
762 ,x_msg_data OUT NOCOPY varchar2
763 ) IS
764 l_lock_Rec t_lock_Rec;
765 l_count number;
766 object_id number;
767 l_index number;
768 Begin
769 if p_numberTable.COUNT > 0 then
770 l_count := 1;
771 l_index := p_numberTable.FIRST;
772 LOOP
773 object_id := p_numberTable(l_index);
774 l_lock_Rec.obj_key1 := object_id;
775 l_lock_Rec.obj_index := l_count;
776 x_lock_table(object_id) := l_lock_Rec;
777 IF l_index = p_numberTable.LAST then
778 exit;
779 end if;
780 l_index := p_numberTable.NEXT(l_index);
781 l_count := l_count + 1;
782 END LOOP;
783 end if;
784
785 EXCEPTION
786 WHEN OTHERS THEN
787 -- rollback;
788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
789 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
790 ,p_data => x_msg_data);
791 raise;
792 END convert_table;
793
794
795
796
797
798 /*--------------------------------------------------------------------
799 Retun the KPI Dimension Sets using a list of dimensions (Dim Groups)
800 --------------------------------------------------------------------*/
801
802 Procedure get_kpi_dim_sets_by_dim(
803 p_selected_dimensions IN t_lock_table
804 ,x_selected_dim_sets OUT NOCOPY t_lock_table
805 ,x_return_status OUT NOCOPY varchar2
806 ,x_msg_count OUT NOCOPY number
807 ,x_msg_data OUT NOCOPY varchar2
808 ) IS
809 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
810 l_sql varchar2(32000);
811 l_lock_Rec t_lock_Rec;
812 l_index number;
813 l_count number;
814
815 Begin
816
817 if p_selected_dimensions.COUNT > 0 then
818 /* build the dimanic query */
819 l_sql := '
820 SELECT DISTINCT INDICATOR, DIM_SET_ID
821 FROM bsc_kpi_dim_groups
822 WHERE';
823 l_index := p_selected_dimensions.FIRST;
824 LOOP
825 l_sql := l_sql || ' DIM_GROUP_ID = ' || p_selected_dimensions(l_index).obj_key1;
826 IF l_index <> p_selected_dimensions.LAST then
827 l_sql := l_sql || ' OR ' ;
828 else
829 l_sql := l_sql || ' ORDER BY INDICATOR, DIM_SET_ID' ;
830 exit;
831 end if;
832 l_index := p_selected_dimensions.NEXT(l_index);
833 END LOOP;
834 --dbms_output.put_line('l_sq =l ' || l_sql);
835
836 /* Execute the query */
837 open l_cursor for l_sql;
838 l_count := 1;
839 LOOP
840 fetch l_cursor into l_lock_Rec.obj_key1, l_lock_Rec.obj_key2;
841 exit when l_cursor%NOTFOUND;
842 x_selected_dim_sets(l_count) := l_lock_Rec;
843 l_count := l_count + 1;
844
845 END LOOP;
846 close l_cursor;
847 end if;
848
849 EXCEPTION
850 WHEN OTHERS THEN
851 -- rollback;
852 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
853 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
854 ,p_data => x_msg_data);
855
856 --dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
857
858 END get_kpi_dim_sets_by_dim;
859
860 /*--------------------------------------------------------------------
861 Retun the KPI Dimension Sets using a relationships
862
863 --------------------------------------------------------------------*/
864
865 Procedure get_kpi_dim_sets_by_Rel(
866 p_child_dim_obj IN number
867 ,p_parent_dim_obj IN number
868 ,x_selected_dim_sets OUT NOCOPY t_lock_table
869 ,x_return_status OUT NOCOPY varchar2
870 ,x_msg_count OUT NOCOPY number
871 ,x_msg_data OUT NOCOPY varchar2
872 ) IS
873 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
874 l_sql varchar2(32000);
875 l_lock_Rec t_lock_Rec;
876 l_index number;
877 l_count number;
878
879 Begin
880
881 /* build the dimanic query */
882 l_sql := '
883 Select DISTINCT C.K, C.DS
884 FROM (
885 SELECT K.INDICATOR K, K.DIM_SET_ID DS, S.DIM_LEVEL_ID DL
886 FROM bsc_kpi_dim_levels_b K,
887 bsc_sys_dim_levels_b s
888 WHERE K.LEVEL_TABLE_NAME = S.LEVEL_TABLE_NAME
889 )P,
890 ( SELECT K.INDICATOR K, K.DIM_SET_ID DS, S.DIM_LEVEL_ID DL
891 FROM bsc_kpi_dim_levels_b K,
892 bsc_sys_dim_levels_b s
893 WHERE K.LEVEL_TABLE_NAME = S.LEVEL_TABLE_NAME
894 )C
895 WHERE C.K = P.K
896 AND C.DS = P.DS
897 AND( C.DL =:1 AND P.DL =:2 )';
898
899 /* Execute the query */
900 open l_cursor for l_sql USING p_child_dim_obj, p_parent_dim_obj ;
901 l_count := 1;
902 LOOP
903 fetch l_cursor into l_lock_Rec.obj_key1, l_lock_Rec.obj_key2;
904 exit when l_cursor%NOTFOUND;
905 x_selected_dim_sets(l_count) := l_lock_Rec;
906 l_count := l_count + 1;
907
908 END LOOP;
909 close l_cursor;
910
911 EXCEPTION
912 WHEN OTHERS THEN
913 -- rollback;
914 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
915 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
916 ,p_data => x_msg_data);
917
918 --dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
919
920 END get_kpi_dim_sets_by_Rel;
921
922
923 /*-------------------------------------------------------------------------------------------------------------------
924 Procedure to Lock a Dimension Objects
925 -------------------------------------------------------------------------------------------------------------------*/
926
927 Procedure LOCK_DIM_LEVEL(
928 p_dim_level_id IN number
929 ,p_time_stamp IN varchar2 /*:= null*/
930 ,x_return_status OUT NOCOPY varchar2
931 ,x_msg_count OUT NOCOPY number
932 ,x_msg_data OUT NOCOPY varchar2
933 ) is
934 l_object_id number;
935 l_last_update_date date;
936 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
937 l_sql varchar2(32000);
938 begin
939
940 SAVEPOINT BSCLockDimObjPVT;
941
942 --SQL statement used to lock a dimension:
943 l_sql := '
944 SELECT DIM_LEVEL_ID, LAST_UPDATE_DATE
945 FROM BSC_SYS_DIM_LEVELS_B
946 WHERE DIM_LEVEL_ID =:1
947 FOR UPDATE NOWAIT';
948
949 open l_cursor for l_sql USING p_dim_level_id;
950 fetch l_cursor into l_object_id, l_last_update_date;
951 if (l_cursor%notfound) then
952 close l_cursor;
953 --dbms_output.put_line('Dimension Level Id = ' || p_dim_level_id || ' Deleted by other user ');
954 FND_MSG_PUB.Initialize;
955 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETED_DIM_LEVEL');
956 FND_MESSAGE.SET_TOKEN('DIM_LEVEL', nvl(get_Dim_Level_Name(p_dim_level_id),p_dim_level_id));
957 FND_MSG_PUB.ADD;
958 x_return_status := FND_API.G_RET_STS_ERROR;
959 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
960 ,p_data => x_msg_data);
961 RAISE FND_API.G_EXC_ERROR;
962 end if;
963
964 if not p_time_stamp is null then
965 if p_time_stamp <> TO_CHAR(l_last_update_date, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT) then
966 --dbms_output.put_line('Dimension Level Id = ' || p_dim_level_id || ' updated by other user ');
967 FND_MSG_PUB.Initialize;
968 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_MODIFIED_DIM_LEVEL');
969 FND_MESSAGE.SET_TOKEN('DIM_LEVEL', get_Dim_Level_Name(p_dim_level_id));
970 FND_MSG_PUB.ADD;
971 x_return_status := FND_API.G_RET_STS_ERROR;
972 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
973 ,p_data => x_msg_data);
974 RAISE FND_API.G_EXC_ERROR;
975 l_last_update_date := l_last_update_date;
976 end if;
977 end if;
978 --dbms_output.put_line('Dimension Level Id = ' || p_dim_level_id || ' successfuly locked ');
979
980 EXCEPTION
981 WHEN FND_API.G_EXC_ERROR THEN
982 ROLLBACK TO BSCLockDimObjPVT;
983 x_return_status := FND_API.G_RET_STS_ERROR;
984 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
985 ,p_data => x_msg_data);
986 raise;
987 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
988 ROLLBACK TO BSCLockDimObjPVT;
989 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
990 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
991 ,p_data => x_msg_data);
992 raise;
993 WHEN OTHERS THEN
994 ROLLBACK TO BSCLockDimObjPVT;
995 if (SQLCODE = -00054) then
996 --dbms_output.put_line('Dimension Level Id = ' || p_dim_level_id || ' locked by other user ');
997 FND_MSG_PUB.Initialize;
998 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_LOCKED_DIM_LEVEL');
999 FND_MESSAGE.SET_TOKEN('DIM_LEVEL', get_Dim_Level_Name(p_dim_level_id));
1000 FND_MSG_PUB.ADD;
1001 x_return_status := FND_API.G_RET_STS_ERROR;
1002 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1003 ,p_data => x_msg_data);
1004 RAISE FND_API.G_EXC_ERROR;
1005 end if;
1006 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1007 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1008 ,p_data => x_msg_data);
1009 raise;
1010
1011 end LOCK_DIM_LEVEL;
1012
1013 /*-------------------------------------------------------------------------------------------------------------------
1014 Procedure to Lock a Dimension Group
1015 -------------------------------------------------------------------------------------------------------------------*/
1016
1017 Procedure LOCK_DIM_GROUP (
1018 p_dim_group_id IN number
1019 ,p_time_stamp IN varchar2 /*:= null */
1020 ,x_return_status OUT NOCOPY varchar2
1021 ,x_msg_count OUT NOCOPY number
1022 ,x_msg_data OUT NOCOPY varchar2
1023
1024 ) is
1025 l_object_id number;
1026 l_last_update_date date;
1027 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
1028 l_sql varchar2(32000);
1029
1030 begin
1031
1032 SAVEPOINT BSCLockDimPVT;
1033
1034 --SQL statement used to lock a dimension group:
1035 l_sql := '
1036 SELECT DIM_GROUP_ID
1037 , LAST_UPDATE_DATE
1038 FROM BSC_SYS_DIM_GROUPS_TL
1039 WHERE DIM_GROUP_ID = :1
1040 AND LANGUAGE = USERENV(''LANG'')
1041 ORDER BY LAST_UPDATE_DATE DESC
1042 FOR UPDATE NOWAIT';
1043
1044 open l_cursor for l_sql USING p_dim_group_id;
1045 fetch l_cursor into l_object_id, l_last_update_date;
1046
1047 if (l_cursor%notfound) then
1048 close l_cursor;
1049 --dbms_output.put_line('Dimension Group Id = ' || p_dim_group_id || ' Deleted by other user ');
1050 FND_MSG_PUB.Initialize;
1051 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETED_DIM_GROUP');
1052 FND_MESSAGE.SET_TOKEN('DIM_GROUP', nvl(get_Dim_Group_Name(p_dim_group_id),p_dim_group_id));
1053 FND_MSG_PUB.ADD;
1054 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1055 ,p_data => x_msg_data);
1056 RAISE FND_API.G_EXC_ERROR;
1057 end if;
1058 if not p_time_stamp is null then
1059 if p_time_stamp <> TO_CHAR(l_last_update_date, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT) then
1060 --dbms_output.put_line('Dimension Group Id = ' || p_dim_group_id || ' updated by other user ');
1061 FND_MSG_PUB.Initialize;
1062 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_MODIFIED_DIM_GROUP');
1063 FND_MESSAGE.SET_TOKEN('DIM_GROUP', get_Dim_Group_Name(p_dim_group_id));
1064 FND_MSG_PUB.ADD;
1065 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1066 ,p_data => x_msg_data);
1067 RAISE FND_API.G_EXC_ERROR;
1068 end if;
1069 end if;
1070 --dbms_output.put_line('Dimension Group Id = ' || p_dim_group_id || ' successfuly locked ');
1071
1072 EXCEPTION
1073 WHEN FND_API.G_EXC_ERROR THEN
1074 ROLLBACK TO BSCLockDimPVT;
1075 x_return_status := FND_API.G_RET_STS_ERROR;
1076 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1077 ,p_data => x_msg_data);
1078 raise;
1079 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1080 ROLLBACK TO BSCLockDimPVT;
1081 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1082 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1083 ,p_data => x_msg_data);
1084 raise;
1085 WHEN OTHERS THEN
1086 ROLLBACK TO BSCLockDimPVT;
1087 if (SQLCODE = -00054) then
1088 --dbms_output.put_line('Dimension Group Id = ' || p_dim_group_id || ' locked by other user ');
1089 FND_MSG_PUB.Initialize;
1090 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_LOCKED_DIM_GROUP');
1091 FND_MESSAGE.SET_TOKEN('DIM_GROUP', get_Dim_Group_Name(p_dim_group_id));
1092 FND_MSG_PUB.ADD;
1093 x_return_status := FND_API.G_RET_STS_ERROR;
1094 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1095 ,p_data => x_msg_data);
1096 RAISE FND_API.G_EXC_ERROR;
1097
1098 end if;
1099
1100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1101 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1102 ,p_data => x_msg_data);
1103 raise;
1104
1105 end LOCK_DIM_GROUP;
1106
1107 /*-------------------------------------------------------------------------------------------------------------------
1108 Procedure to Lock a Dimension Set
1109 -------------------------------------------------------------------------------------------------------------------*/
1110
1111 Procedure LOCK_DIM_SET (
1112 p_kpi_Id IN number
1113 ,p_dim_set_id IN number
1114 ,p_time_stamp IN varchar2 /*:= null*/
1115 ,x_return_status OUT NOCOPY varchar2
1116 ,x_msg_count OUT NOCOPY number
1117 ,x_msg_data OUT NOCOPY varchar2
1118 ) is
1119 l_kpi_id number;
1120 l_dim_set_id number;
1121 l_last_update_date date;
1122 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
1123 l_sql varchar2(32000);
1124
1125 Begin
1126
1127 SAVEPOINT BSCLockDimSetPVT;
1128
1129
1130 --SQL statement used to lock a dimension:
1131 l_sql := '
1132 SELECT INDICATOR, DIM_SET_ID, LAST_UPDATE_DATE
1133 FROM bsc_kpi_dim_sets_tl
1134 WHERE INDICATOR =:1
1135 AND DIM_SET_ID =:2
1136 ORDER BY LAST_UPDATE_DATE DESC
1137 FOR UPDATE NOWAIT';
1138
1139 open l_cursor for l_sql USING p_kpi_Id, p_dim_set_id;
1140 fetch l_cursor into l_kpi_id, l_dim_set_id, l_last_update_date;
1141 if (l_cursor%notfound) then
1142 close l_cursor;
1143 --dbms_output.put_line('Kpi = ' || p_kpi_id || ' Dimension Set Id = ' || p_dim_set_id || ' Deleted by other user ');
1144 FND_MSG_PUB.Initialize;
1145 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETED_DIM_SET');
1146 FND_MESSAGE.SET_TOKEN('DIM_SET', nvl(get_Dim_Set_Name(p_kpi_Id, p_dim_set_id), p_dim_set_id )); -- Fixed Bug#3047483
1147 FND_MESSAGE.SET_TOKEN('KPI', nvl(get_KPI_Name(p_kpi_Id),p_kpi_Id)); -- Fixed Bug#3047483
1148 FND_MSG_PUB.ADD;
1149 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1150 ,p_data => x_msg_data);
1151 RAISE FND_API.G_EXC_ERROR;
1152 end if;
1153 if not p_time_stamp is null then
1154 if p_time_stamp <> TO_CHAR(l_last_update_date, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT) then
1155 --dbms_output.put_line('Kpi = ' || p_kpi_id || ' Dimension Set Id = ' || p_dim_set_id || ' updated by other user ');
1156 FND_MSG_PUB.Initialize;
1157 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_MODIFIED_DIM_SET');
1158 FND_MESSAGE.SET_TOKEN('DIM_SET', get_Dim_Set_Name(p_kpi_Id, p_dim_set_id)); -- Fixed Bug#3047483
1159 FND_MESSAGE.SET_TOKEN('KPI', get_KPI_Name(p_kpi_Id)); -- Fixed Bug#3047483
1160 FND_MSG_PUB.ADD;
1161 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1162 ,p_data => x_msg_data);
1163 RAISE FND_API.G_EXC_ERROR;
1164 end if;
1165 end if;
1166 --dbms_output.put_line('Kpi = ' || p_kpi_id || ' Dimension Set Id = ' || p_dim_set_id || ' successfuly locked ');
1167
1168 EXCEPTION
1169 WHEN FND_API.G_EXC_ERROR THEN
1170 ROLLBACK TO BSCLockDimSetPVT;
1171 x_return_status := FND_API.G_RET_STS_ERROR;
1172 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1173 ,p_data => x_msg_data);
1174 raise;
1175 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1176 ROLLBACK TO BSCLockDimSetPVT;
1177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1178 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1179 ,p_data => x_msg_data);
1180 raise;
1181 WHEN OTHERS THEN
1182 ROLLBACK TO BSCLockDimSetPVT;
1183 if (SQLCODE = -00054) then
1184 --dbms_output.put_line('Kpi = ' || p_kpi_id || ' Dimension Set Id = ' || p_dim_set_id || ' locked by other user ');
1185 FND_MSG_PUB.Initialize;
1186 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_LOCKED_DIM_SET');
1187 FND_MESSAGE.SET_TOKEN('DIM_SET', get_Dim_Set_Name(p_dim_set_id, p_kpi_Id));
1188 FND_MESSAGE.SET_TOKEN('KPI', get_KPI_Name(p_kpi_Id));
1189 FND_MSG_PUB.ADD;
1190 x_return_status := FND_API.G_RET_STS_ERROR;
1191 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1192 ,p_data => x_msg_data);
1193 RAISE FND_API.G_EXC_ERROR;
1194 end if;
1195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1196 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1197 ,p_data => x_msg_data);
1198 raise;
1199
1200 end LOCK_DIM_SET;
1201
1202 /*-------------------------------------------------------------------------------------------------------------------
1203 Procedure to Lock a KPI
1204 -------------------------------------------------------------------------------------------------------------------*/
1205
1206 Procedure LOCK_KPI(
1207 p_kpi_Id IN number
1208 ,p_time_stamp IN varchar2 /* := null */
1209 ,p_full_lock_flag IN varchar2 /*:= FND_API.G_FALSE */
1210 ,x_return_status OUT NOCOPY varchar2
1211 ,x_msg_count OUT NOCOPY number
1212 ,x_msg_data OUT NOCOPY varchar2
1213 ) is
1214 l_kpi_id number;
1215 l_dim_set_id number;
1216 l_last_update_date varchar2(50);
1217 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
1218 l_sql varchar2(300);
1219 l_temp varchar2(300);
1220
1221 Begin
1222
1223 SAVEPOINT BSCLockKpiPVT;
1224
1225 --SQL statement used to lock a dimension:
1226 l_sql := '
1227 SELECT PROPERTY_CODE
1228 FROM BSC_KPI_PROPERTIES
1229 WHERE PROPERTY_CODE = ''LOCK_INDICATOR''
1230 AND INDICATOR =:1
1231 FOR UPDATE NOWAIT';
1232
1233 open l_cursor for l_sql USING p_kpi_Id;
1234 fetch l_cursor into l_temp;
1235
1236 if (l_cursor%notfound) then
1237 close l_cursor;
1238 --dbms_output.put_line('Kpi = ' || p_kpi_id || ' Deleted by other user ');
1239 FND_MSG_PUB.Initialize;
1240 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETED_KPI_1');
1241 FND_MESSAGE.SET_TOKEN('KPI', nvl(get_KPI_Name(p_kpi_Id),p_kpi_Id ));
1242 FND_MSG_PUB.ADD;
1243 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1244 ,p_data => x_msg_data);
1245 RAISE FND_API.G_EXC_ERROR;
1246 end if;
1247 close l_cursor;
1248
1249 if not p_time_stamp is null then
1250 l_last_update_date := get_time_stamp_kpi( p_kpi_Id);
1251 if p_time_stamp <> l_last_update_date then
1252 --dbms_output.put_line('Kpi = ' || p_kpi_id || ' updated by other user ');
1253 FND_MSG_PUB.Initialize;
1254 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_MODIFIED_KPI_1');
1255 FND_MESSAGE.SET_TOKEN('KPI', get_KPI_Name(p_kpi_Id));
1256 FND_MSG_PUB.ADD;
1257 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1258 ,p_data => x_msg_data);
1259 RAISE FND_API.G_EXC_ERROR;
1260 end if;
1261 end if;
1262
1263 If p_full_lock_flag = FND_API.G_TRUE then
1264 --SQL statement used to lock ALL the Dimension Sets:
1265 l_sql := '
1266 SELECT INDICATOR, DIM_SET_ID
1267 FROM bsc_kpi_dim_sets_tl
1268 WHERE INDICATOR =:1
1269 FOR UPDATE NOWAIT';
1270 open l_cursor for l_sql USING p_kpi_Id;
1271 close l_cursor;
1272 End if;
1273
1274 --dbms_output.put_line('Kpi = ' || p_kpi_id || ' successfuly locked ');
1275
1276
1277 EXCEPTION
1278 WHEN FND_API.G_EXC_ERROR THEN
1279 ROLLBACK TO BSCLockKpiPVT;
1280 x_return_status := FND_API.G_RET_STS_ERROR;
1281 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1282 ,p_data => x_msg_data);
1283 raise;
1284 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1285 ROLLBACK TO BSCLockKpiPVT;
1286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1287 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1288 ,p_data => x_msg_data);
1289 --dbms_output.put_line('Procedure = LOCK_KPI G_EXC_UNEXPECTED_ERROR ');
1290
1291 raise;
1292 WHEN OTHERS THEN
1293 ROLLBACK TO BSCLockKpiPVT;
1294 if (SQLCODE = -00054) then
1295 --dbms_output.put_line('Kpi = ' || p_kpi_id || ' locked by other user ');
1296 FND_MSG_PUB.Initialize;
1297 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_LOCKED_KPI_1');
1298 FND_MESSAGE.SET_TOKEN('KPI', get_KPI_Name(p_kpi_Id));
1299 FND_MSG_PUB.ADD;
1300 x_return_status := FND_API.G_RET_STS_ERROR;
1301 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1302 ,p_data => x_msg_data);
1303 RAISE FND_API.G_EXC_ERROR;
1304 end if;
1305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1306 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1307 ,p_data => x_msg_data);
1308 --dbms_output.put_line('Procedure = LOCK_KPI OTHERS ');
1309
1310 raise;
1311 End LOCK_KPI;
1312
1313 /*------------------------------------------------------------------------------------------
1314 Getting Time Stamp for Dimension Level
1315 ------------------------------------------------------------------------------------------*/
1316 Function GET_TIME_STAMP_DIM_LEVEL(
1317 p_dim_level_id IN number
1318 ) return varchar2 is
1319 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
1320 l_sql varchar2(32000);
1321 object_id number;
1322 temp date;
1323 Begin
1324 l_sql := '
1325 SELECT LAST_UPDATE_DATE
1326 FROM BSC_SYS_DIM_LEVELS_B
1327 WHERE DIM_LEVEL_ID =:1';
1328
1329 open l_cursor for l_sql USING p_dim_level_id;
1330 fetch l_cursor into temp;
1331 close l_cursor;
1332 return TO_CHAR(temp, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT);
1333
1334 EXCEPTION
1335 WHEN OTHERS THEN
1336 return NULL;
1337
1338 end GET_TIME_STAMP_DIM_LEVEL;
1339
1340 /*------------------------------------------------------------------------------------------
1341 Getting Time Stamp for Dimension Group
1342 -------------------------------------------------------------------------------------------*/
1343 Function GET_TIME_STAMP_DIM_GROUP (
1344 p_dim_group_id IN number
1345 ) return varchar2 is
1346 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
1347 l_sql varchar2(32000);
1348 object_id number;
1349 temp date;
1350 Begin
1351 l_sql := '
1352 SELECT LAST_UPDATE_DATE
1353 FROM BSC_SYS_DIM_GROUPS_VL
1354 WHERE DIM_GROUP_ID = :1';
1355
1356 open l_cursor for l_sql USING p_dim_group_id;
1357 fetch l_cursor into temp;
1358 close l_cursor;
1359 return TO_CHAR(temp, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT);
1360
1361 EXCEPTION
1362 WHEN OTHERS THEN
1363 return NULL;
1364
1365 end GET_TIME_STAMP_DIM_GROUP;
1366 /*------------------------------------------------------------------------------------------
1367 Getting Time Stamp Dimension Set
1368 -------------------------------------------------------------------------------------------*/
1369 Function GET_TIME_STAMP_DIM_SET (
1370 p_kpi_Id IN number
1371 ,p_dim_set_id IN number
1372 ) return varchar2 is
1373 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
1374 l_sql varchar2(32000);
1375 object_id number;
1376 temp date;
1377 Begin
1378 l_sql := '
1379 SELECT MAX(LAST_UPDATE_DATE)
1380 FROM bsc_kpi_dim_sets_tl
1381 WHERE INDICATOR =:1
1382 AND DIM_SET_ID =:2';
1383
1384 open l_cursor for l_sql USING p_kpi_Id, p_dim_set_id;
1385 fetch l_cursor into temp;
1386 close l_cursor;
1387 return TO_CHAR(temp, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT);
1388
1389 EXCEPTION
1390 WHEN OTHERS THEN
1391 return NULL;
1392
1393 end GET_TIME_STAMP_DIM_SET;
1394
1395 /*------------------------------------------------------------------------------------------
1396 Getting Time Stamp for KPIs (Indicators)
1397 -------------------------------------------------------------------------------------------*/
1398 Function GET_TIME_STAMP_KPI (
1399 p_kpi_Id IN number
1400 ) return varchar2 is
1401 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
1402 l_sql varchar2(32000);
1403 object_id number;
1404 temp date;
1405 Begin
1406 -- SELECT LAST_UPDATE_DATE
1407
1408 l_sql := 'SELECT LAST_UPDATE_DATE ' ||
1409 ' FROM BSC_KPIS_B ' ||
1410 ' WHERE INDICATOR =:1' ;
1411 open l_cursor for l_sql USING p_kpi_Id;
1412 fetch l_cursor into temp;
1413 close l_cursor;
1414 return TO_CHAR(temp, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT);
1415
1416 EXCEPTION
1417 WHEN OTHERS THEN
1418 return NULL;
1419 end GET_TIME_STAMP_KPI;
1420
1421 /******************************************************************
1422 Name :- get_tab_time_stamp
1423 Description :- This fucntion will return the time stamp corresponding to
1424 tab id
1425 Input :- p_tab_id
1426 Creator :- ashankar 05-NOV-2003
1427 /******************************************************************/
1428
1429 FUNCTION get_tab_time_stamp(
1430 p_tab_id IN NUMBER
1431 )RETURN VARCHAR2
1432 IS
1433 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
1434 l_sql VARCHAR2(32000);
1435 temp BSC_TABS_B.last_update_date%TYPE;
1436 BEGIN
1437 l_sql := 'SELECT LAST_UPDATE_DATE ' ||
1438 ' FROM BSC_TABS_B ' ||
1439 ' WHERE TAB_ID =:1 ';
1440
1441
1442 OPEN l_cursor FOR l_sql USING p_tab_id;
1443 FETCH l_cursor INTO temp;
1444 CLOSE l_cursor;
1445
1446 RETURN TO_CHAR(temp,BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT);
1447
1448 EXCEPTION
1449 WHEN OTHERS THEN
1450 RETURN NULL;
1451 END get_tab_time_stamp;
1452
1453
1454 /******************************************************************
1455 Name :- get_tabview_time_stamp
1456 Description :- This fucntion will return the time stamp corresponding to
1457 tab view
1458 Input :- p_tab_id
1459 p_tab_view_id
1460 Creator :- ashankar 05-NOV-2003
1461 /******************************************************************/
1462
1463 FUNCTION get_tabview_time_stamp (
1464 p_tab_id IN NUMBER
1465 ,p_tab_view_id IN NUMBER
1466 ) RETURN VARCHAR2
1467 IS
1468 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
1469 l_sql VARCHAR2(32000);
1470 object_id NUMBER;
1471 temp DATE;
1472 Begin
1473
1474 l_sql := 'SELECT LAST_UPDATE_DATE ' ||
1475 ' FROM BSC_TAB_VIEWS_B ' ||
1476 ' WHERE TAB_ID =:1 AND TAB_VIEW_ID =:2' ;
1477
1478 OPEN l_cursor FOR l_sql USING p_tab_id,p_tab_view_id;
1479
1480 FETCH l_cursor INTO temp;
1481 CLOSE l_cursor;
1482
1483 RETURN TO_CHAR(temp, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT);
1484
1485 EXCEPTION
1486 WHEN OTHERS THEN
1487 RETURN NULL;
1488 end get_tabview_time_stamp;
1489
1490
1491 /*------------------------------------------------------------------------------------------
1492 Setting Time Stamp for Dimension Objects
1493 -------------------------------------------------------------------------------------------*/
1494 Procedure SET_TIME_STAMP_DIM_LEVEL (
1495 p_dim_level_id IN number
1496 ,x_return_status OUT NOCOPY varchar2
1497 ,x_msg_count OUT NOCOPY number
1498 ,x_msg_data OUT NOCOPY varchar2
1499 ) is
1500 l_sql varchar2(32000);
1501 begin
1502
1503 SAVEPOINT BSCSetTimeDimObjPVT;
1504
1505 l_sql := '
1506 UPDATE BSC_SYS_DIM_LEVELS_B
1507 SET LAST_UPDATE_DATE = sysdate
1508 WHERE DIM_LEVEL_ID =:1';
1509
1510 execute immediate l_sql USING p_dim_level_id;
1511
1512 EXCEPTION
1513 WHEN FND_API.G_EXC_ERROR THEN
1514 ROLLBACK TO BSCSetTimeDimObjPVT;
1515 x_return_status := FND_API.G_RET_STS_ERROR;
1516 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1517 ,p_data => x_msg_data);
1518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1519 ROLLBACK TO BSCSetTimeDimObjPVT;
1520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1521 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1522 ,p_data => x_msg_data);
1523
1524 WHEN OTHERS THEN
1525 ROLLBACK TO BSCSetTimeDimObjPVT;
1526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1527 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1528 ,p_data => x_msg_data);
1529
1530 end SET_TIME_STAMP_DIM_LEVEL;
1531 /*------------------------------------------------------------------------------------------
1532 Setting Time Stamp for Dimension Group
1533 -------------------------------------------------------------------------------------------*/
1534 Procedure SET_TIME_STAMP_DIM_GROUP (
1535 p_dim_group_id IN number
1536 ,x_return_status OUT NOCOPY varchar2
1537 ,x_msg_count OUT NOCOPY number
1538 ,x_msg_data OUT NOCOPY varchar2
1539 ) is
1540 l_sql varchar2(32000);
1541 begin
1542
1543 SAVEPOINT BSCSetTimeDimPVT;
1544
1545 l_sql := '
1546 UPDATE BSC_SYS_DIM_GROUPS_TL
1547 SET LAST_UPDATE_DATE = SYSDATE
1548 WHERE DIM_GROUP_ID =:1
1549 AND USERENV(''LANG'') IN (LANGUAGE, SOURCE_LANG)';
1550
1551 execute immediate l_sql USING p_dim_group_id;
1552
1553 EXCEPTION
1554 WHEN FND_API.G_EXC_ERROR THEN
1555 ROLLBACK TO BSCSetTimeDimPVT;
1556 x_return_status := FND_API.G_RET_STS_ERROR;
1557 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1558 ,p_data => x_msg_data);
1559 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1560 ROLLBACK TO BSCSetTimeDimPVT;
1561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1562 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1563 ,p_data => x_msg_data);
1564 WHEN OTHERS THEN
1565 ROLLBACK TO BSCSetTimeDimPVT;
1566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1567 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1568 ,p_data => x_msg_data);
1569
1570 end SET_TIME_STAMP_DIM_GROUP;
1571 /*------------------------------------------------------------------------------------------
1572 Setting Time Stamp for Dimension Set
1573 -------------------------------------------------------------------------------------------*/
1574 Procedure SET_TIME_STAMP_DIM_SET (
1575 p_kpi_Id IN number
1576 , p_dim_set_id IN number
1577 ,x_return_status OUT NOCOPY varchar2
1578 ,x_msg_count OUT NOCOPY number
1579 ,x_msg_data OUT NOCOPY varchar2
1580 ) is
1581 l_sql varchar2(32000);
1582 begin
1583
1584 SAVEPOINT BSCSetTimeDimSetPVT;
1585
1586 l_sql := '
1587 UPDATE bsc_kpi_dim_sets_tl
1588 SET LAST_UPDATE_DATE = sysdate
1589 WHERE INDICATOR =:1
1590 AND DIM_SET_ID =:2';
1591 execute immediate l_sql USING p_kpi_Id, p_dim_set_id;
1592
1593 SET_TIME_STAMP_KPI (p_Kpi_Id
1594 ,x_return_status
1595 ,x_msg_count
1596 ,x_msg_data );
1597
1598 EXCEPTION
1599 WHEN FND_API.G_EXC_ERROR THEN
1600 ROLLBACK TO BSCSetTimeDimSetPVT;
1601 x_return_status := FND_API.G_RET_STS_ERROR;
1602 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1603 ,p_data => x_msg_data);
1604 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1605 ROLLBACK TO BSCSetTimeDimSetPVT;
1606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1607 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1608 ,p_data => x_msg_data);
1609 WHEN OTHERS THEN
1610 ROLLBACK TO BSCSetTimeDimSetPVT;
1611 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1612 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1613 ,p_data => x_msg_data);
1614
1615 end SET_TIME_STAMP_DIM_SET;
1616 /*------------------------------------------------------------------------------------------
1617 Setting Time Stamp for KPI
1618 -------------------------------------------------------------------------------------------*/
1619 Procedure SET_TIME_STAMP_KPI (
1620 p_kpi_Id IN number
1621 ,x_return_status OUT NOCOPY varchar2
1622 ,x_msg_count OUT NOCOPY number
1623 ,x_msg_data OUT NOCOPY varchar2
1624 ) is
1625 l_sql varchar2(32000);
1626 begin
1627
1628 SAVEPOINT BSCSetTimeKpiPVT;
1629 -- Modified by Aditya 10-JUN-03
1630 l_sql := ' UPDATE BSC_KPIS_B ' ||
1631 ' SET LAST_UPDATE_DATE = SYSDATE ' ||
1632 ' WHERE INDICATOR =:1';
1633
1634 execute immediate l_sql USING p_kpi_Id;
1635
1636 -- Added Exception block
1637
1638 EXCEPTION
1639 WHEN FND_API.G_EXC_ERROR THEN
1640 ROLLBACK TO BSCSetTimeKpiPVT;
1641 x_return_status := FND_API.G_RET_STS_ERROR;
1642 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1643 ,p_data => x_msg_data);
1644 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1645 ROLLBACK TO BSCSetTimeKpiPVT;
1646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1647 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1648 ,p_data => x_msg_data);
1649 WHEN OTHERS THEN
1650 ROLLBACK TO BSCSetTimeKpiPVT;
1651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1652 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1653 ,p_data => x_msg_data);
1654
1655 end SET_TIME_STAMP_KPI;
1656
1657 /*------------------------------------------------------------------------------------------
1658 Procedure LOCK_CREATE_DIMENSION
1659
1660 This Procedure will make all the necessaries locks to Create a Dimensions (Dimension Group)
1661 according with the PMD UI for 'Performance Measures > Dimensions > Create Dimension'
1662 This procedure will lock all the dimension object that will assign to the new Dimension
1663 <parameters>
1664 p_selected_dim_objets: Array with the Ids corresponding to the Dimesion Objects
1665 that will be assigned to the new dimension.
1666 -------------------------------------------------------------------------------------------*/
1667 Procedure LOCK_CREATE_DIMENSION (
1668 p_selected_dim_objets IN BSC_BIS_LOCKS_PUB.t_numberTable
1669 ,x_return_status OUT NOCOPY varchar2
1670 ,x_msg_count OUT NOCOPY number
1671 ,x_msg_data OUT NOCOPY varchar2
1672 ) is
1673 l_index number;
1674 l_dim_level_id number;
1675 Begin
1676
1677 SAVEPOINT BSCLockCreDimPVT;
1678
1679 if p_selected_dim_objets.COUNT > 0 then
1680 l_index := p_selected_dim_objets.FIRST;
1681 LOOP
1682 l_dim_level_id := p_selected_dim_objets(l_index);
1683 BSC_BIS_LOCKS_PUB.LOCK_DIM_LEVEL(
1684 p_dim_level_id => l_dim_level_id
1685 ,p_time_stamp => null
1686 ,x_return_status => x_return_status
1687 ,x_msg_count => x_msg_count
1688 ,x_msg_data => x_msg_data
1689 );
1690 if l_index = p_selected_dim_objets.LAST then
1691 exit;
1692 end if;
1693 l_index := p_selected_dim_objets.NEXT(l_index);
1694 END LOOP;
1695 end if;
1696
1697 EXCEPTION
1698 WHEN FND_API.G_EXC_ERROR THEN
1699 ROLLBACK TO BSCLockCreDimPVT;
1700 x_return_status := FND_API.G_RET_STS_ERROR;
1701 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1702 ,p_data => x_msg_data);
1703 raise;
1704 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1705 ROLLBACK TO BSCLockCreDimPVT;
1706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1707 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1708 ,p_data => x_msg_data);
1709 raise;
1710 WHEN OTHERS THEN
1711 ROLLBACK TO BSCLockCreDimPVT;
1712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1713 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1714 ,p_data => x_msg_data);
1715 raise;
1716 end LOCK_CREATE_DIMENSION;
1717 /*------------------------------------------------------------------------------------------
1718 Procedure LOCK_UPDATE_DIMENSION
1719 This Procedure will make all the necessaries locks to Update a Dimension (Dimension Group)
1720 according with the PMD UI for 'Performance Measures > Dimensions > Update Dimension'
1721 This procedure will lock the dimension passed in the parameter p_dimension_id,
1722 the dimension objects passed in the parameter p_selected_dim_objets,
1723 and the dimension set (in the kpis) that uses the dimension when it is necessary.
1724 <parameters>
1725 p_dimension_id: Dimension Id (Dimension Group) to update
1726 p_selected_dim_objets: This array has the Ids corresponding to the Dimension Objects
1727 that will have the dimension.
1728 p_time_stamp: Last update of dimension information changed by the user
1729
1730
1731 -------------------------------------------------------------------------------------------*/
1732 Procedure LOCK_UPDATE_DIMENSION (
1733 p_dimension_id IN number
1734 ,p_selected_dim_objets IN BSC_BIS_LOCKS_PUB.t_numberTable
1735 ,p_time_stamp IN varchar2/* := null */
1736 ,x_return_status OUT NOCOPY varchar2
1737 ,x_msg_count OUT NOCOPY number
1738 ,x_msg_data OUT NOCOPY varchar2
1739 ) is
1740 l_index number;
1741 l_dim_level_id number;
1742
1743 l_selected_dim_objets t_lock_table;
1744 l_previous_dim_objets t_lock_table;
1745
1746 l_impacted_dim_sets t_lock_table;
1747 l_impacted_dimentions t_lock_table;
1748
1749 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
1750 l_sql varchar2(300);
1751
1752 l_lock_Rec t_lock_Rec;
1753 l_count number;
1754 l_change_dim_sets_flag boolean;
1755
1756 Begin
1757
1758 SAVEPOINT BSCLockUpdDimPVT;
1759
1760 /*1 Lock the Dimension calling: */
1761 BSC_BIS_LOCKS_PUB.LOCK_DIM_GROUP (
1762 p_dim_group_id => p_dimension_id
1763 ,p_time_stamp => p_time_stamp
1764 ,x_return_status => x_return_status
1765 ,x_msg_count => x_msg_count
1766 ,x_msg_data => x_msg_data
1767 );
1768
1769 /* 0. Just passed selected Dimension Object into l_selected_dim_objets */
1770 if p_selected_dim_objets.COUNT > 0 then
1771 l_count := 1;
1772 l_index := p_selected_dim_objets.FIRST;
1773 LOOP
1774 l_dim_level_id := p_selected_dim_objets(l_index);
1775 l_lock_Rec.obj_key1 := l_dim_level_id;
1776 l_lock_Rec.obj_index := l_count;
1777 l_selected_dim_objets(l_dim_level_id) := l_lock_Rec;
1778 IF l_index = p_selected_dim_objets.LAST then
1779 exit;
1780 end if;
1781 l_index := p_selected_dim_objets.NEXT(l_index);
1782 l_count := l_count + 1;
1783 END LOOP;
1784 end if;
1785
1786 /*2. Query the dimension object that actually have the dimension */
1787 get_selected_dim_objs(
1788 p_dimension_id => p_dimension_id
1789 ,x_selected_dim_objs => l_previous_dim_objets
1790 ,x_return_status => x_return_status
1791 ,x_msg_count => x_msg_count
1792 ,x_msg_data => x_msg_data
1793 );
1794
1795 l_change_dim_sets_flag := false;
1796
1797 /* 3.1 Find the Deleted dimension objects */
1798 if l_previous_dim_objets.COUNT > 0 then
1799 l_index := l_previous_dim_objets.FIRST;
1800 LOOP
1801 l_dim_level_id := l_previous_dim_objets(l_index).obj_key1;
1802 if NOT l_selected_dim_objets.EXISTS(l_dim_level_id) then
1803 /* 4.1 Lock Dimension Objects deleted from de list*/
1804 BSC_BIS_LOCKS_PUB.LOCK_DIM_LEVEL(
1805 p_dim_level_id => l_dim_level_id
1806 ,p_time_stamp => null
1807 ,x_return_status => x_return_status
1808 ,x_msg_count => x_msg_count
1809 ,x_msg_data => x_msg_data
1810 );
1811 l_change_dim_sets_flag := true;
1812 END IF;
1813 IF l_index = l_previous_dim_objets.LAST then
1814 exit;
1815 end if;
1816 l_index := l_previous_dim_objets.NEXT(l_index);
1817 END LOOP;
1818 end if;
1819 /* 3.2 Find the Added dimension objects */
1820 if l_selected_dim_objets.COUNT > 0 then
1821 l_index := l_selected_dim_objets.FIRST;
1822 LOOP
1823 l_dim_level_id := l_selected_dim_objets(l_index).obj_key1;
1824 if NOT l_previous_dim_objets.EXISTS(l_dim_level_id) then
1825 /* 4.2 Lock Dimension Objects Added to de list*/
1826 BSC_BIS_LOCKS_PUB.LOCK_DIM_LEVEL(
1827 p_dim_level_id => l_dim_level_id
1828 ,p_time_stamp => null
1829 ,x_return_status => x_return_status
1830 ,x_msg_count => x_msg_count
1831 ,x_msg_data => x_msg_data
1832 );
1833 l_change_dim_sets_flag := true;
1834 elsif l_change_dim_sets_flag = false then
1835 /* Validate if the dim object order have been changed */
1836 if l_selected_dim_objets(l_index).obj_index <>
1837 l_previous_dim_objets(l_index).obj_index then
1838 l_change_dim_sets_flag := true;
1839 end if;
1840 end if;
1841 If l_index = l_selected_dim_objets.LAST then
1842 exit;
1843 end if;
1844 l_index := l_selected_dim_objets.NEXT(l_index);
1845 END LOOP;
1846 end if;
1847
1848 /* 5.Lock all the KPI Dimension Sets using the Dimension (Dimension Group) */
1849 IF l_change_dim_sets_flag = true then
1850 l_impacted_dimentions(1).obj_key1:= p_dimension_id;
1851 get_kpi_dim_sets_by_dim(
1852 p_selected_dimensions => l_impacted_dimentions
1853 ,x_selected_dim_sets => l_impacted_dim_sets
1854 ,x_return_status => x_return_status
1855 ,x_msg_count => x_msg_count
1856 ,x_msg_data => x_msg_data
1857 );
1858 if not l_impacted_dim_sets is null then
1859 for l_index in 1.. l_impacted_dim_sets.count loop
1860 l_lock_Rec := l_impacted_dim_sets(l_index);
1861 BSC_BIS_LOCKS_PUB.LOCK_DIM_SET (
1862 p_Kpi_Id => l_lock_Rec.obj_key1
1863 ,p_Dim_Set_Id => l_lock_Rec.obj_key2
1864 ,p_time_stamp => null
1865 ,x_return_status => x_return_status
1866 ,x_msg_count => x_msg_count
1867 ,x_msg_data => x_msg_data
1868 );
1869 end loop;
1870 end if;
1871 end if;
1872
1873 EXCEPTION
1874 WHEN FND_API.G_EXC_ERROR THEN
1875 ROLLBACK TO BSCLockUpdDimPVT;
1876 x_return_status := FND_API.G_RET_STS_ERROR;
1877 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1878 ,p_data => x_msg_data);
1879 raise;
1880 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1881 ROLLBACK TO BSCLockUpdDimPVT;
1882 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1883 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1884 ,p_data => x_msg_data);
1885 raise;
1886 WHEN OTHERS THEN
1887 ROLLBACK TO BSCLockUpdDimPVT;
1888 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1889 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1890 ,p_data => x_msg_data);
1891 raise;
1892
1893 end LOCK_UPDATE_DIMENSION;
1894
1895 /*------------------------------------------------------------------------------------------
1896 Procedure LOCK_UPDATE_DIM_OBJ_IN_DIM
1897 This procedure will make all the necessaries locks to Update a Dimension
1898 Object propertis in a dimencion.
1899 (Dimension level properties in a Dimension Group
1900
1901 -------------------------------------------------------------------------------------------*/
1902 Procedure LOCK_UPDATE_DIM_OBJ_IN_DIM(
1903 p_dim_object_id IN number
1904 ,p_dimension_id IN number
1905 ,p_time_stamp IN varchar2
1906 ,x_return_status OUT NOCOPY varchar2
1907 ,x_msg_count OUT NOCOPY number
1908 ,x_msg_data OUT NOCOPY varchar2
1909 )is
1910
1911 l_impacted_dim_sets t_lock_table;
1912 l_impacted_dimentions t_lock_table;
1913 l_lock_Rec t_lock_Rec;
1914 Begin
1915
1916 SAVEPOINT BSCLockUpdDimInObjPVT;
1917
1918
1919 /* Lock Dimension */
1920 BSC_BIS_LOCKS_PUB.LOCK_DIM_GROUP (
1921 p_dim_group_id => p_dimension_id
1922 ,p_time_stamp => p_time_stamp
1923 ,x_return_status => x_return_status
1924 ,x_msg_count => x_msg_count
1925 ,x_msg_data => x_msg_data
1926 );
1927 /* LocKl Dimension Object */
1928 BSC_BIS_LOCKS_PUB.LOCK_DIM_LEVEL(
1929 p_dim_level_id => p_dim_object_id
1930 ,p_time_stamp => null
1931 ,x_return_status => x_return_status
1932 ,x_msg_count => x_msg_count
1933 ,x_msg_data => x_msg_data
1934 );
1935
1936 /* lOCK Kpi Dimension Sets */
1937 l_impacted_dimentions(1).obj_key1:= p_dimension_id;
1938 get_kpi_dim_sets_by_dim(
1939 p_selected_dimensions => l_impacted_dimentions
1940 ,x_selected_dim_sets => l_impacted_dim_sets
1941 ,x_return_status => x_return_status
1942 ,x_msg_count => x_msg_count
1943 ,x_msg_data => x_msg_data
1944 );
1945 for l_index in 1.. l_impacted_dim_sets.count loop
1946 l_lock_Rec := l_impacted_dim_sets(l_index);
1947 BSC_BIS_LOCKS_PUB.LOCK_DIM_SET (
1948 p_Kpi_Id => l_lock_Rec.obj_key1
1949 ,p_Dim_Set_Id => l_lock_Rec.obj_key2
1950 ,p_time_stamp => null
1951 ,x_return_status => x_return_status
1952 ,x_msg_count => x_msg_count
1953 ,x_msg_data => x_msg_data
1954 );
1955 end loop;
1956
1957
1958
1959
1960
1961 EXCEPTION
1962 WHEN FND_API.G_EXC_ERROR THEN
1963 ROLLBACK TO BSCLockUpdDimInObjPVT;
1964 x_return_status := FND_API.G_RET_STS_ERROR;
1965 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1966 ,p_data => x_msg_data);
1967 raise;
1968 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1969 ROLLBACK TO BSCLockUpdDimInObjPVT;
1970 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1971 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1972 ,p_data => x_msg_data);
1973 raise;
1974 WHEN OTHERS THEN
1975 ROLLBACK TO BSCLockUpdDimInObjPVT;
1976 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1977 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1978 ,p_data => x_msg_data);
1979 raise;
1980
1981 End LOCK_UPDATE_DIM_OBJ_IN_DIM;
1982
1983 /*------------------------------------------------------------------------------------------
1984 Procedure LOCK_CREATE_DIMENSION_OBJECT
1985 This procedure will make all the necessaries locks to Create a Dimension Object (Dimension Level)
1986 according with the PMD UI for 'Performance Measures > Dimensions > Dimension Objects >
1987 Create Dimension Object'
1988 <parameters>
1989 p_selected_dimensions: This Array has the Ids corresponding to the Dimensions where
1990 the dimension object will be assigned.
1991 -------------------------------------------------------------------------------------------*/
1992 Procedure LOCK_CREATE_DIMENSION_OBJECT(
1993 p_selected_dimensions IN BSC_BIS_LOCKS_PUB.t_numberTable
1994 ,x_return_status OUT NOCOPY varchar2
1995 ,x_msg_count OUT NOCOPY number
1996 ,x_msg_data OUT NOCOPY varchar2
1997 ) is
1998 l_index number;
1999 l_dim_group_id number;
2000 l_impacted_dimentions t_lock_table;
2001 l_impacted_dim_sets t_lock_table;
2002 l_lock_Rec t_lock_Rec;
2003
2004 Begin
2005
2006
2007 SAVEPOINT BSCLockCreDimObjPVT;
2008
2009 /*1. Lock all the Dimension where the Dimension Object will be assigned*/
2010 if p_selected_dimensions.COUNT > 0 then
2011 l_index := p_selected_dimensions.FIRST;
2012 LOOP
2013 l_dim_group_id := p_selected_dimensions(l_index);
2014 l_impacted_dimentions(l_index).obj_key1 := l_dim_group_id;
2015 BSC_BIS_LOCKS_PUB.LOCK_DIM_GROUP (
2016 p_dim_group_id => l_dim_group_id
2017 ,p_time_stamp => null
2018 ,x_return_status => x_return_status
2019 ,x_msg_count => x_msg_count
2020 ,x_msg_data => x_msg_data
2021 );
2022 if l_index = p_selected_dimensions.LAST then
2023 exit;
2024 end if;
2025 l_index := p_selected_dimensions.NEXT(l_index);
2026 END LOOP;
2027 end if;
2028
2029 IF l_impacted_dimentions.COUNT > 0 then
2030 /* 2. Get all the KPI Dimension Sets using the selected Dimensions
2031 (Dimension Groups) for the new Dimension object*/
2032 get_kpi_dim_sets_by_dim(
2033 p_selected_dimensions => l_impacted_dimentions
2034 ,x_selected_dim_sets => l_impacted_dim_sets
2035 ,x_return_status => x_return_status
2036 ,x_msg_count => x_msg_count
2037 ,x_msg_data => x_msg_data
2038 );
2039 /* Lock affected KPI Dimension Sets */
2040 for l_index in 1.. l_impacted_dim_sets.count loop
2041 l_lock_Rec := l_impacted_dim_sets(l_index);
2042 BSC_BIS_LOCKS_PUB.LOCK_DIM_SET (
2043 p_Kpi_Id => l_lock_Rec.obj_key1
2044 ,p_Dim_Set_Id => l_lock_Rec.obj_key2
2045 ,p_time_stamp => null
2046 ,x_return_status => x_return_status
2047 ,x_msg_count => x_msg_count
2048 ,x_msg_data => x_msg_data
2049 );
2050 end loop;
2051 end if;
2052
2053 EXCEPTION
2054 WHEN FND_API.G_EXC_ERROR THEN
2055 ROLLBACK TO BSCLockCreDimObjPVT;
2056 x_return_status := FND_API.G_RET_STS_ERROR;
2057 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
2058 ,p_data => x_msg_data);
2059 raise;
2060 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2061 ROLLBACK TO BSCLockCreDimObjPVT;
2062 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2063 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
2064 ,p_data => x_msg_data);
2065 raise;
2066 WHEN OTHERS THEN
2067 ROLLBACK TO BSCLockCreDimObjPVT;
2068 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2069 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
2070 ,p_data => x_msg_data);
2071 raise;
2072 End LOCK_CREATE_DIMENSION_OBJECT;
2073
2074 /*------------------------------------------------------------------------------------------
2075 Procedure LOCK_UPDATE_DIMENSION_OBJECT
2076 This procedure will make all the necessaries locks to Update a Dimension Object (Dimension Level)
2077 according with the PMD UI for 'Performance Measures > Dimensions > Dimension Objects >
2078 Update Dimension Object'
2079 <parameters>
2080 p_dim_object_id: Dimension Object Id (Dimension Level) to update
2081 p_selected_dim_objets: This array has the Ids corresponding to the Dimension Objects
2082 that will have the dimension.
2083 p_time_stamp: Last update of dimension object information changed by the user.
2084 It is mandatory in order of checking if the dimension object has been
2085 updated by other user.
2086 -------------------------------------------------------------------------------------------*/
2087 Procedure LOCK_UPDATE_DIMENSION_OBJECT(
2088 p_dim_object_id IN number
2089 ,p_selected_dimensions IN BSC_BIS_LOCKS_PUB.t_numberTable
2090 ,p_time_stamp IN varchar2
2091 ,x_return_status OUT NOCOPY varchar2
2092 ,x_msg_count OUT NOCOPY number
2093 ,x_msg_data OUT NOCOPY varchar2
2094 ) is
2095 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
2096 l_sql varchar2(32000);
2097 l_dim_set_id number;
2098 l_kpi_id number;
2099
2100 l_selected_dimensions t_lock_table;
2101 l_previous_dimensions t_lock_table;
2102 l_impacted_dim_sets t_lock_table;
2103 l_impacted_dimentions t_lock_table;
2104 l_lock_Rec t_lock_Rec;
2105 l_dimension_id number;
2106 l_count number;
2107 l_index number;
2108
2109
2110 Begin
2111
2112 SAVEPOINT BSCLockUpdDimObjPVT;
2113
2114 /* 0. Just passed p_selected Dimension into l_selected Dimension */
2115 if p_selected_dimensions.COUNT > 0 then
2116 l_count := 1;
2117 l_index := p_selected_dimensions.FIRST;
2118 LOOP
2119 l_dimension_id := p_selected_dimensions(l_index);
2120 l_lock_Rec.obj_key1 := l_dimension_id;
2121 l_lock_Rec.obj_index := l_count;
2122 l_selected_dimensions(l_dimension_id) := l_lock_Rec;
2123 IF l_index = p_selected_dimensions.LAST then
2124 exit;
2125 end if;
2126 l_index := p_selected_dimensions.NEXT(l_index);
2127 l_count := l_count + 1;
2128 END LOOP;
2129 end if;
2130
2131 /* 1. Lock the Dimension object that will be updated */
2132 BSC_BIS_LOCKS_PUB.LOCK_DIM_LEVEL(
2133 p_dim_level_id => p_dim_object_id
2134 ,p_time_stamp => p_time_stamp
2135 ,x_return_status => x_return_status
2136 ,x_msg_count => x_msg_count
2137 ,x_msg_data => x_msg_data
2138 );
2139
2140 /* 2. Get the KPI Dimension Set that uses the Dimension object*/
2141 l_sql := '
2142 SELECT INDICATOR, DIM_SET_ID
2143 FROM BSC_sys_dim_levels_by_group DLG,
2144 bsc_kpi_dim_groups KDG
2145 WHERE DLG.DIM_LEVEL_ID =:1
2146 AND KDG.DIM_GROUP_ID = DLG.DIM_GROUP_ID';
2147 open l_cursor for l_sql USING p_dim_object_id ;
2148 LOOP
2149 fetch l_cursor into l_kpi_id, l_dim_set_id;
2150 exit when l_cursor%NOTFOUND;
2151 /* 3. Lock each Dimension Set where the Dimension object is used: */
2152 BSC_BIS_LOCKS_PUB.LOCK_DIM_SET (
2153 p_Kpi_Id => l_Kpi_Id
2154 ,p_Dim_Set_Id => l_Dim_Set_Id
2155 ,p_time_stamp => null
2156 ,x_return_status => x_return_status
2157 ,x_msg_count => x_msg_count
2158 ,x_msg_data => x_msg_data
2159 );
2160 END LOOP;
2161 close l_cursor;
2162
2163 /* Followed instructions are for changes in the Selected Dimensions
2164 for the current Dimension Object */
2165
2166 /* 4. Get previous Selected Dimension : */
2167 get_selected_dimensions(
2168 p_dim_obj_id => p_dim_object_id
2169 ,x_selected_dimensions => l_previous_dimensions
2170 ,x_return_status => x_return_status
2171 ,x_msg_count => x_msg_count
2172 ,x_msg_data => x_msg_data
2173 );
2174 /*5. Compare previous Dimension queried in step 4 with the
2175 Selected Dimension in p_Selected_Dimensions to find Dimension
2176 that was deleted from or added to the Selected Dimension list; This are
2177 Impacted Dimension */
2178
2179 /* 5.1 Find the deleted dimension. */
2180 if l_previous_dimensions.COUNT > 0 then
2181 l_dimension_id := l_previous_dimensions.FIRST;
2182 LOOP
2183 if NOT l_selected_dimensions.EXISTS(l_dimension_id) then
2184 l_lock_Rec := l_previous_dimensions(l_dimension_id);
2185 l_lock_Rec.obj_Flag := 'D';
2186 l_impacted_dimentions(l_dimension_id) := l_lock_Rec;
2187 END IF;
2188 IF l_dimension_id = l_previous_dimensions.LAST then
2189 exit;
2190 end if;
2191 l_dimension_id := l_previous_dimensions.NEXT(l_dimension_id);
2192 END LOOP;
2193 end if;
2194 /* 5.2 Find the Added dimension. */
2195 if l_selected_dimensions.COUNT > 0 then
2196 l_dimension_id := l_selected_dimensions.FIRST;
2197 LOOP
2198 if NOT l_previous_dimensions.EXISTS(l_dimension_id) then
2199 l_lock_Rec := l_selected_dimensions(l_dimension_id);
2200 l_lock_Rec.obj_Flag := 'A';
2201 l_impacted_dimentions(l_dimension_id) := l_lock_Rec;
2202 END IF;
2203 IF l_dimension_id = l_selected_dimensions.LAST then
2204 exit;
2205 end if;
2206 l_dimension_id := l_selected_dimensions.NEXT(l_dimension_id);
2207 END LOOP;
2208 end if;
2209 /* 6. Lock the affected dimensions found in step 5 */
2210 if l_impacted_dimentions.COUNT > 0 then
2211 l_dimension_id := l_impacted_dimentions.FIRST;
2212 LOOP
2213 BSC_BIS_LOCKS_PUB.LOCK_DIM_GROUP (
2214 p_dim_group_id => l_dimension_id
2215 ,p_time_stamp => null
2216 ,x_return_status => x_return_status
2217 ,x_msg_count => x_msg_count
2218 ,x_msg_data => x_msg_data
2219 );
2220 if l_dimension_id = l_impacted_dimentions.LAST then
2221 exit;
2222 end if;
2223 l_dimension_id := l_impacted_dimentions.NEXT(l_dimension_id);
2224 END LOOP;
2225 end if;
2226
2227 /* 7. Get all the KPI Dimension Sets using the Afected Dimensions */
2228 get_kpi_dim_sets_by_dim(
2229 p_selected_dimensions => l_impacted_dimentions
2230 ,x_selected_dim_sets => l_impacted_dim_sets
2231 ,x_return_status => x_return_status
2232 ,x_msg_count => x_msg_count
2233 ,x_msg_data => x_msg_data
2234 );
2235 /* 8. Lock each Dimension Set found in Step 7 calling: */
2236 if not l_impacted_dim_sets is null then
2237 for l_index in 1.. l_impacted_dim_sets.count loop
2238 l_lock_Rec := l_impacted_dim_sets(l_index);
2239 BSC_BIS_LOCKS_PUB.LOCK_DIM_SET (
2240 p_Kpi_Id => l_lock_Rec.obj_key1
2241 ,p_Dim_Set_Id => l_lock_Rec.obj_key2
2242 ,p_time_stamp => null
2243 ,x_return_status => x_return_status
2244 ,x_msg_count => x_msg_count
2245 ,x_msg_data => x_msg_data
2246 );
2247 end loop;
2248 end if;
2249
2250
2251 EXCEPTION
2252 WHEN FND_API.G_EXC_ERROR THEN
2253 ROLLBACK TO BSCLockUpdDimObjPVT;
2254 x_return_status := FND_API.G_RET_STS_ERROR;
2255 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
2256 ,p_data => x_msg_data);
2257 raise;
2258 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2259 ROLLBACK TO BSCLockUpdDimObjPVT;
2260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2261 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
2262 ,p_data => x_msg_data);
2263 raise;
2264 WHEN OTHERS THEN
2265 ROLLBACK TO BSCLockUpdDimObjPVT;
2266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2267 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
2268 ,p_data => x_msg_data);
2269 raise;
2270 end LOCK_UPDATE_DIMENSION_OBJECT;
2271 /*------------------------------------------------------------------------------------------
2272 Procedure LOCK_UPDATE_DIM_OBJ_RELATIONSHIPS
2273 This process Lock all affected object when the relationships for a given dimension
2274 object are updated.
2275 <parameters>
2276 p_dim_object_id: Dimension Object Id (Dimension Level) to update
2277 p_selected_parends: This array has the Ids corresponding to the Parent Dimension Objects
2278 that will have the dimension object (Selected Parent Dimension Objects)
2279 p_selected_childs: This array has the Ids corresponding to the Child Dimension Objects
2280 that will have the dimension object (Selected Child Dimension Objects).
2281 p_time_stamp: Last update of dimension object information changed by the user.
2282 It is mandatory in order of checking if the dimension object has
2283 been updated by other user.
2284 -------------------------------------------------------------------------------------------*/
2285 Procedure LOCK_UPDATE_RELATIONSHIPS(
2286 p_dim_object_id IN number
2287 ,p_selected_parends IN BSC_BIS_LOCKS_PUB.t_numberTable
2288 ,p_selected_childs IN BSC_BIS_LOCKS_PUB.t_numberTable
2289 ,p_time_stamp IN varchar2
2290 ,x_return_status OUT NOCOPY varchar2
2291 ,x_msg_count OUT NOCOPY number
2292 ,x_msg_data OUT NOCOPY varchar2
2293 ) is
2294 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
2295 l_sql varchar2(300);
2296 l_lock_Rec t_lock_Rec;
2297 l_count number;
2298 l_index number;
2299
2300 l_selected_parends t_lock_table;
2301 l_selected_childs t_lock_table;
2302 l_impacted_dim_objects t_lock_table;
2303 l_impacted_dim_sets t_lock_table;
2304 l_previous_parends t_lock_table;
2305 l_previous_childs t_lock_table;
2306 l_dim_object_id number;
2307 l_child_dim_object_id number;
2308 l_parent_dim_object_id number;
2309
2310 Begin
2311
2312 SAVEPOINT BSCLockUpdDimRelsPVT;
2313
2314 /*1. Lock the Dimension object that will be updated: */
2315 BSC_BIS_LOCKS_PUB.LOCK_DIM_LEVEL(
2316 p_dim_level_id => p_dim_object_id
2317 ,p_time_stamp => p_time_stamp
2318 ,x_return_status => x_return_status
2319 ,x_msg_count => x_msg_count
2320 ,x_msg_data => x_msg_data
2321 );
2322
2323 /*2. Get the previous Parent Dimension Object from the database.*/
2324 l_sql := '
2325 SELECT PARENT_DIM_LEVEL_ID
2326 FROM BSC_SYS_DIM_LEVEL_RELS
2327 WHERE DIM_LEVEL_ID =:1';
2328 open l_cursor for l_sql USING p_dim_object_id ;
2329 LOOP
2330 fetch l_cursor into l_dim_object_id;
2331 exit when l_cursor%NOTFOUND;
2332 l_lock_Rec.obj_key1 := l_dim_object_id;
2333 l_previous_parends(l_dim_object_id) := l_lock_Rec ;
2334 END LOOP;
2335 close l_cursor;
2336
2337 /*3. Compare Selected parent in p_Selected_Parents and previous parents
2338 queried in Step 1 to find deleted and added parent in
2339 l_impacted_dim_objects */
2340
2341 convert_table(
2342 p_numberTable => p_selected_parends
2343 ,x_lock_table => l_selected_parends
2344 ,x_return_status => x_return_status
2345 ,x_msg_count => x_msg_count
2346 ,x_msg_data => x_msg_data
2347 );
2348 get_impacted_objects(
2349 p_selected_objects => l_selected_parends
2350 ,p_previous_objects => l_previous_parends
2351 ,x_impacted_objects => l_impacted_dim_objects
2352 ,x_return_status => x_return_status
2353 ,x_msg_count => x_msg_count
2354 ,x_msg_data => x_msg_data
2355 );
2356 if l_impacted_dim_objects.COUNT > 0 then
2357 l_index := l_impacted_dim_objects.FIRST;
2358 LOOP
2359 l_parent_dim_object_id := l_impacted_dim_objects(l_index).obj_key1;
2360 /*4. Lock Deleted and added Parent Dimension object */
2361 BSC_BIS_LOCKS_PUB.LOCK_DIM_LEVEL(
2362 p_dim_level_id => l_parent_dim_object_id
2363 ,p_time_stamp => null
2364 ,x_return_status => x_return_status
2365 ,x_msg_count => x_msg_count
2366 ,x_msg_data => x_msg_data
2367 );
2368 /*5. Get Dimension Sets where the deleted or added Relationship */
2369 get_kpi_dim_sets_by_Rel(
2370 p_child_dim_obj => p_dim_object_id
2371 ,p_parent_dim_obj => l_parent_dim_object_id
2372 ,x_selected_dim_sets => l_impacted_dim_sets
2373 ,x_return_status => x_return_status
2374 ,x_msg_count => x_msg_count
2375 ,x_msg_data => x_msg_data
2376 );
2377 /*6. Lock the Dimension Set got it in step 5 */
2378 for l_index in 1.. l_impacted_dim_sets.count loop
2379 l_lock_Rec := l_impacted_dim_sets(l_index);
2380 BSC_BIS_LOCKS_PUB.LOCK_DIM_SET (
2381 p_Kpi_Id => l_lock_Rec.obj_key1
2382 ,p_Dim_Set_Id => l_lock_Rec.obj_key2
2383 ,p_time_stamp => null
2384 ,x_return_status => x_return_status
2385 ,x_msg_count => x_msg_count
2386 ,x_msg_data => x_msg_data
2387 );
2388 end loop;
2389 if l_index = l_impacted_dim_objects.LAST then
2390 exit;
2391 end if;
2392 l_index := l_impacted_dim_objects.NEXT(l_index);
2393 END LOOP;
2394 end if;
2395
2396
2397 /*7. Get the previous Child Dimension Object from the database. */
2398 l_sql := '
2399 SELECT DIM_LEVEL_ID
2400 FROM BSC_SYS_DIM_LEVEL_RELS
2401 WHERE PARENT_DIM_LEVEL_ID =:1';
2402 open l_cursor for l_sql USING p_dim_object_id ;
2403 LOOP
2404 fetch l_cursor into l_dim_object_id;
2405 exit when l_cursor%NOTFOUND;
2406 l_lock_Rec.obj_key1 := l_dim_object_id;
2407 l_previous_childs(l_dim_object_id) := l_lock_Rec ;
2408 END LOOP;
2409 close l_cursor;
2410
2411 /*8. Compare Selected Child in p_Selected_Chlilds and previous
2412 childs queried in Step 7 to find deleted and added childs. These are
2413 l_impacted_dim_objects */
2414 convert_table(
2415 p_numberTable => p_selected_childs
2416 ,x_lock_table => l_selected_childs
2417 ,x_return_status => x_return_status
2418 ,x_msg_count => x_msg_count
2419 ,x_msg_data => x_msg_data
2420 );
2421 get_impacted_objects(
2422 p_selected_objects => l_selected_childs
2423 ,p_previous_objects => l_previous_childs
2424 ,x_impacted_objects => l_impacted_dim_objects
2425 ,x_return_status => x_return_status
2426 ,x_msg_count => x_msg_count
2427 ,x_msg_data => x_msg_data
2428 );
2429 if l_impacted_dim_objects.COUNT > 0 then
2430 l_index := l_impacted_dim_objects.FIRST;
2431 LOOP
2432 l_child_dim_object_id := l_impacted_dim_objects(l_index).obj_key1;
2433 /*9. Lock deleted and added Child Dimension object*/
2434 BSC_BIS_LOCKS_PUB.LOCK_DIM_LEVEL(
2435 p_dim_level_id => l_child_dim_object_id
2436 ,p_time_stamp => null
2437 ,x_return_status => x_return_status
2438 ,x_msg_count => x_msg_count
2439 ,x_msg_data => x_msg_data
2440 );
2441 /*10. Get Dimension Sets impacted by the Dimension object Relationship */
2442 get_kpi_dim_sets_by_Rel(
2443 p_child_dim_obj => l_child_dim_object_id
2444 ,p_parent_dim_obj => p_dim_object_id
2445 ,x_selected_dim_sets => l_impacted_dim_sets
2446 ,x_return_status => x_return_status
2447 ,x_msg_count => x_msg_count
2448 ,x_msg_data => x_msg_data
2449 );
2450 /*11. Lock the Dimension Sets got it in step 10. */
2451 for l_index in 1.. l_impacted_dim_sets.count loop
2452 l_lock_Rec := l_impacted_dim_sets(l_index);
2453 BSC_BIS_LOCKS_PUB.LOCK_DIM_SET (
2454 p_Kpi_Id => l_lock_Rec.obj_key1
2455 ,p_Dim_Set_Id => l_lock_Rec.obj_key2
2456 ,p_time_stamp => null
2457 ,x_return_status => x_return_status
2458 ,x_msg_count => x_msg_count
2459 ,x_msg_data => x_msg_data
2460 );
2461 end loop;
2462 if l_index = l_impacted_dim_objects.LAST then
2463 exit;
2464 end if;
2465 l_index := l_impacted_dim_objects.NEXT(l_index);
2466 END LOOP;
2467 end if;
2468
2469 EXCEPTION
2470 WHEN FND_API.G_EXC_ERROR THEN
2471 ROLLBACK TO BSCLockUpdDimRelsPVT;
2472 x_return_status := FND_API.G_RET_STS_ERROR;
2473 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
2474 ,p_data => x_msg_data);
2475 raise;
2476 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2477 ROLLBACK TO BSCLockUpdDimRelsPVT;
2478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2479 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
2480 ,p_data => x_msg_data);
2481 raise;
2482 WHEN OTHERS THEN
2483 ROLLBACK TO BSCLockUpdDimRelsPVT;
2484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2485 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
2486 ,p_data => x_msg_data);
2487 raise;
2488 end LOCK_UPDATE_RELATIONSHIPS;
2489 /*------------------------------------------------------------------------------------------
2490 Procedure LOCK_ASSIGN_ DIM_SET
2491 Use this procedure to lock necessary object when a Dimension Set need to be assign
2492 to a specific Analysis Option
2493 <parameters>
2494 p_kpi_Id : Indicator Id
2495 p_dim_set_id : Dimension Set Id
2496 p_time_stamp : Time stamp.
2497
2498 Note: By Now this parmeter will used to make the lock.
2499 Future version will used other parameters
2500
2501 -------------------------------------------------------------------------------------------*/
2502 Procedure LOCK_ASSIGN_DIM_SET (
2503 p_kpi_Id IN number
2504 ,p_option_group0 IN number
2505 ,p_option_group1 IN number
2506 ,p_option_group2 IN number
2507 ,p_serie_id IN number
2508 ,p_dim_set_id IN number
2509 ,p_time_stamp IN varchar2
2510 ,x_return_status OUT NOCOPY varchar2
2511 ,x_msg_count OUT NOCOPY number
2512 ,x_msg_data OUT NOCOPY varchar2
2513 ) is
2514 temp number;
2515
2516 Begin
2517
2518 /* By now, this procedure will lock the KPI instead of
2519 the specific analysis options. This because the analysis option are not
2520 handle by this PMD version yet */
2521
2522 /*1. Lock the Dimension Set */
2523
2524 SAVEPOINT BSCLockAsgnDimSetPVT;
2525
2526 BSC_BIS_LOCKS_PUB.LOCK_DIM_SET (
2527 p_Kpi_Id => p_Kpi_Id
2528 ,p_Dim_Set_Id => p_Dim_Set_Id
2529 ,p_time_stamp => null
2530 ,x_return_status => x_return_status
2531 ,x_msg_count => x_msg_count
2532 ,x_msg_data => x_msg_data
2533 );
2534 /*2. Lock the KPI */
2535
2536 BSC_BIS_LOCKS_PUB.LOCK_KPI(
2537 p_Kpi_Id => p_Kpi_Id
2538 ,p_time_stamp => p_time_stamp
2539 ,p_Full_Lock_Flag => null
2540 ,x_return_status => x_return_status
2541 ,x_msg_count => x_msg_count
2542 ,x_msg_data => x_msg_data
2543 );
2544
2545 EXCEPTION
2546 WHEN FND_API.G_EXC_ERROR THEN
2547 ROLLBACK TO BSCLockAsgnDimSetPVT;
2548 x_return_status := FND_API.G_RET_STS_ERROR;
2549 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
2550 ,p_data => x_msg_data);
2551 raise;
2552 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2553 ROLLBACK TO BSCLockAsgnDimSetPVT;
2554 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2555 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
2556 ,p_data => x_msg_data);
2557 raise;
2558 WHEN OTHERS THEN
2559 ROLLBACK TO BSCLockAsgnDimSetPVT;
2560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2561 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
2562 ,p_data => x_msg_data);
2563 raise;
2564
2565 end LOCK_ASSIGN_DIM_SET;
2566
2567 /************************************************************************
2568 Name :- LOCK_TAB
2569 Description :- This procedure will lock the row corresponding to
2570 tab_id in BSC_TABS_B table.
2571 Input :- p_tab_id
2572
2573 Creator :- ashankar 05-NOV-2003
2574 /************************************************************************/
2575
2576 PROCEDURE LOCK_TAB
2577 (
2578 p_tab_id IN NUMBER
2579 ,p_time_stamp IN VARCHAR2 := NULL
2580 ,x_return_status OUT NOCOPY VARCHAR2
2581 ,x_msg_count OUT NOCOPY NUMBER
2582 ,x_msg_data OUT NOCOPY VARCHAR2
2583 )IS
2584 l_last_update_date VARCHAR2(50);
2585 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
2586 l_sql varchar2(300);
2587 l_temp VARCHAR2(300);
2588
2589 BEGIN
2590
2591 SAVEPOINT bsclocktabpvt;
2592 FND_MSG_PUB.Initialize;
2593 x_return_status := FND_API.G_RET_STS_SUCCESS;
2594
2595 l_sql := ' SELECT TAB_ID
2596 FROM BSC_TABS_B
2597 WHERE TAB_ID = :1
2598 FOR UPDATE NOWAIT ';
2599
2600
2601 OPEN l_cursor FOR l_sql USING p_tab_id;
2602 FETCH l_cursor INTO l_temp;
2603
2604 IF(l_cursor%NOTFOUND) THEN
2605 CLOSE l_cursor;
2606 FND_MSG_PUB.Initialize;
2607 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
2608 FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.GET_LOOKUP_VALUE('BSC_UI_IVIEWER', 'SCORECARD'), TRUE);
2609 FND_MSG_PUB.ADD;
2610 RAISE FND_API.G_EXC_ERROR;
2611 END IF;
2612
2613 IF ( p_time_stamp IS NOT NULL) THEN
2614 l_last_update_date := get_tab_time_stamp
2615 ( p_tab_id => p_tab_id
2616 );
2617
2618 IF (p_time_stamp <> l_last_update_date) THEN
2619 FND_MSG_PUB.Initialize;
2620 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_MODIFIED_SCORECARD');
2621 FND_MSG_PUB.ADD;
2622 RAISE FND_API.G_EXC_ERROR;
2623 END IF;
2624 END IF;
2625
2626 IF (l_cursor%ISOPEN) THEN
2627 CLOSE l_cursor;
2628 END IF;
2629
2630 EXCEPTION
2631 WHEN FND_API.G_EXC_ERROR THEN
2632
2633 IF (l_cursor%ISOPEN) THEN
2634 CLOSE l_cursor;
2635 END IF;
2636
2637 ROLLBACK TO bsclocktabpvt;
2638 x_return_status := FND_API.G_RET_STS_ERROR;
2639 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
2640 ,p_count => x_msg_count
2641 ,p_data => x_msg_data);
2642 RAISE;
2643 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2644
2645 IF (l_cursor%ISOPEN) THEN
2646 CLOSE l_cursor;
2647 END IF;
2648
2649 ROLLBACK TO bsclocktabpvt;
2650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2651 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
2652 ,p_count => x_msg_count
2653 ,p_data => x_msg_data);
2654 RAISE;
2655 WHEN OTHERS THEN
2656 IF (l_cursor%ISOPEN) THEN
2657 CLOSE l_cursor;
2658 END IF;
2659 ROLLBACK TO bsclocktabpvt;
2660 IF (SQLCODE = -00054) THEN
2661 FND_MESSAGE.SET_NAME('BSC','BSC_MUSERS_LOCKED_TAB');
2662 FND_MSG_PUB.ADD;
2663 x_return_status := FND_API.G_RET_STS_ERROR;
2664 RAISE FND_API.G_EXC_ERROR;
2665 END IF;
2666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2667 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
2668 ,p_data => x_msg_data);
2669 RAISE;
2670 ROLLBACK TO bsclocktabpvt;
2671 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2672 FND_MSG_PUB.Count_And_Get(p_encoded => 'F'
2673 ,p_count => x_msg_count
2674 ,p_data => x_msg_data);
2675 RAISE;
2676 END LOCK_TAB;
2677
2678
2679
2680 /************************************************************************
2681 Name :- LOCK_TAB_VIEW_ID
2682 Description :- This procedure will lock the row corresponding to
2683 tab_id and tab_view_id in BSC_TAB_VIEWS_B table.
2684 Input :- p_tab_id
2685 p_tab_view_id
2686 Creator :- ashankar 05-NOV-2003
2687 Note :- This API needs to be modified.
2688 /************************************************************************/
2689
2690 PROCEDURE LOCK_TAB_VIEW_ID
2691 (
2692 p_tab_id IN NUMBER
2693 ,p_tab_view_id IN NUMBER
2694 ,p_time_stamp IN VARCHAR2 := NULL
2695 ,x_return_status OUT NOCOPY VARCHAR2
2696 ,x_msg_count OUT NOCOPY NUMBER
2697 ,x_msg_data OUT NOCOPY VARCHAR2
2698 )IS
2699 l_last_update_date VARCHAR2(50);
2700 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
2701 l_sql VARCHAR2(300);
2702 l_temp VARCHAR2(300);
2703 BEGIN
2704
2705 SAVEPOINT bsclocktabviewpvt;
2706
2707 l_sql := '
2708 SELECT TAB_ID,TAB_VIEW_ID
2709 FROM BSC_TAB_VIEWS_B
2710 WHERE TAB_ID = :1
2711 AND TAB_VIEW_ID = :2
2712 FOR UPDATE NOWAIT ';
2713
2714 OPEN l_cursor FOR l_sql USING p_tab_id,p_tab_view_id;
2715 FETCH l_cursor INTO l_temp;
2716
2717 IF (l_cursor%notfound) THEN
2718 CLOSE l_cursor;
2719 --DBMS_OUTPUT.PUT_LINE('Kpi = ' || p_kpi_id || ' Deleted by other user ');
2720 FND_MSG_PUB.Initialize;
2721 --FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETED_KPI_1');
2722 FND_MESSAGE.SET_NAME('BSC','The current tab view has been deleted by another user');
2723 FND_MSG_PUB.ADD;
2724 FND_MSG_PUB.Count_And_Get(p_encoded => 'F'
2725 ,p_count => x_msg_count
2726 ,p_data => x_msg_data);
2727 RAISE FND_API.G_EXC_ERROR;
2728 END IF;
2729 CLOSE l_cursor;
2730
2731 IF NOT p_time_stamp IS NULL THEN
2732 l_last_update_date := get_tabview_time_stamp(
2733 p_tab_id => p_tab_id
2734 ,p_tab_view_id=> p_tab_view_id
2735 );
2736 IF p_time_stamp <> l_last_update_date THEN
2737 --dbms_output.put_line('Kpi = ' || p_kpi_id || ' updated by other user ');
2738 FND_MSG_PUB.Initialize;
2739 FND_MESSAGE.SET_NAME('BSC','The Current view has been modified by another user');
2740 --FND_MESSAGE.SET_TOKEN('KPI', get_TabView_Name(p_tab_id,p_tab_view_id);
2741 FND_MSG_PUB.ADD;
2742 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
2743 ,p_count => x_msg_count
2744 ,p_data => x_msg_data);
2745 RAISE FND_API.G_EXC_ERROR;
2746 END IF;
2747 END IF;
2748
2749 EXCEPTION
2750 WHEN FND_API.G_EXC_ERROR THEN
2751 ROLLBACK TO bsclocktabviewpvt;
2752 x_return_status := FND_API.G_RET_STS_ERROR;
2753 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
2754 ,p_count => x_msg_count
2755 ,p_data => x_msg_data);
2756 RAISE;
2757 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2758 ROLLBACK TO bsclocktabviewpvt;
2759 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2760 FND_MSG_PUB.Count_And_Get(p_encoded => 'F'
2761 ,p_count => x_msg_count
2762 ,p_data => x_msg_data);
2763 RAISE;
2764 WHEN OTHERS THEN
2765 ROLLBACK TO bsclocktabviewpvt;
2766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2767 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
2768 ,p_count => x_msg_count
2769 ,p_data => x_msg_data);
2770 RAISE;
2771
2772 END LOCK_TAB_VIEW_ID;
2773
2774
2775 PROCEDURE Lock_Calendar (
2776 p_Calendar_Id IN NUMBER
2777 , p_Time_Stamp IN VARCHAR2
2778 , x_Return_Status OUT NOCOPY VARCHAR2
2779 , x_Msg_Count OUT NOCOPY NUMBER
2780 , x_Msg_Data OUT NOCOPY VARCHAR2
2781 ) IS
2782
2783 l_Object_Id NUMBER;
2784 l_Last_Update_Date DATE;
2785 l_Sql VARCHAR2(1024);
2786 l_Cursor BSC_BIS_LOCKS_PUB.t_CURSOR;
2787 l_Meaning BSC_LOOKUPS.MEANING%TYPE;
2788
2789 BEGIN
2790 SAVEPOINT LockCalendarPVT;
2791 FND_MSG_PUB.Initialize;
2792
2793 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
2794
2795 l_Sql := ' SELECT CALENDAR_ID, LAST_UPDATE_DATE '
2796 ||' FROM BSC_SYS_CALENDARS_B '
2797 ||' WHERE CALENDAR_ID = :1 '
2798 ||' FOR UPDATE NOWAIT ';
2799
2800 OPEN l_Cursor FOR l_Sql USING p_Calendar_Id;
2801 FETCH l_Cursor INTO l_Object_Id, l_Last_Update_Date;
2802
2803 IF (l_Cursor%NOTFOUND) THEN
2804 CLOSE l_Cursor;
2805 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
2806 l_Meaning := Bsc_Apps.Get_Lookup_Value('BSC_UI_COMMON', 'CALENDAR');
2807 FND_MESSAGE.SET_TOKEN('TYPE', l_Meaning, TRUE);
2808 FND_MSG_PUB.ADD;
2809 RAISE FND_API.G_EXC_ERROR;
2810 END IF;
2811
2812 IF NOT p_Time_Stamp IS NULL THEN
2813 IF p_Time_Stamp <> TO_CHAR(l_Last_Update_Date, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT) THEN
2814 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_MODIFIED_CALENDAR');
2815 FND_MESSAGE.SET_TOKEN('CALENDAR', Get_Calendar_Name(p_Calendar_Id));
2816 FND_MSG_PUB.ADD;
2817 x_Return_Status := FND_API.G_RET_STS_ERROR;
2818 RAISE FND_API.G_EXC_ERROR;
2819 END IF;
2820 END IF;
2821
2822 IF (l_Cursor%ISOPEN) THEN
2823 CLOSE l_Cursor;
2824 END IF;
2825
2826 EXCEPTION
2827 WHEN FND_API.G_EXC_ERROR THEN
2828 ROLLBACK TO LockCalendarPVT;
2829 IF (l_Cursor%ISOPEN) THEN
2830 CLOSE l_Cursor;
2831 END IF;
2832
2833 IF (x_msg_data IS NULL) THEN
2834 FND_MSG_PUB.Count_And_Get
2835 ( p_encoded => FND_API.G_FALSE
2836 , p_count => x_msg_count
2837 , p_data => x_msg_data
2838 );
2839 END IF;
2840 x_Return_Status := FND_API.G_RET_STS_ERROR;
2841 RAISE;
2842 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2843 ROLLBACK TO LockCalendarPVT;
2844 IF (l_Cursor%ISOPEN) THEN
2845 CLOSE l_Cursor;
2846 END IF;
2847 IF (x_msg_data IS NULL) THEN
2848 FND_MSG_PUB.Count_And_Get
2849 ( p_encoded => FND_API.G_FALSE
2850 , p_count => x_msg_count
2851 , p_data => x_msg_data
2852 );
2853 END IF;
2854 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2855 RAISE;
2856 WHEN NO_DATA_FOUND THEN
2857 ROLLBACK TO LockCalendarPVT;
2858 IF (l_Cursor%ISOPEN) THEN
2859 CLOSE l_Cursor;
2860 END IF;
2861 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2862 IF (x_msg_data IS NOT NULL) THEN
2863 x_msg_data := x_msg_data||' -> BSC_BIS_LOCKS_PVT.Lock_Calendar ';
2864 ELSE
2865 x_msg_data := SQLERRM||' at BSC_BIS_LOCKS_PVT.Lock_Calendar ';
2866 END IF;
2867 RAISE;
2868 WHEN OTHERS THEN
2869 ROLLBACK TO LockCalendarPVT;
2870 IF (l_Cursor%ISOPEN) THEN
2871 CLOSE l_Cursor;
2872 END IF;
2873 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2874 IF (SQLCODE = -00054) THEN
2875 FND_MSG_PUB.Initialize;
2876 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_LOCKED_CALENDAR');
2877 FND_MESSAGE.SET_TOKEN('CALENDAR', Get_Calendar_Name(p_Calendar_Id));
2878 FND_MSG_PUB.ADD;
2879 x_Return_Status := FND_API.G_RET_STS_ERROR;
2880
2881 FND_MSG_PUB.Count_And_Get
2882 ( p_encoded => FND_API.G_FALSE
2883 , p_count => x_msg_count
2884 , p_data => x_msg_data
2885 );
2886 RAISE FND_API.G_EXC_ERROR;
2887 ELSE
2888 IF (x_msg_data IS NOT NULL) THEN
2889 x_msg_data := x_msg_data||' -> BSC_BIS_LOCKS_PVT.Lock_Calendar ';
2890 ELSE
2891 x_msg_data := SQLERRM||' at BSC_BIS_LOCKS_PVT.Lock_Calendar ';
2892 END IF;
2893 END IF;
2894 RAISE;
2895 END Lock_Calendar;
2896
2897 PROCEDURE Lock_Periodicity (
2898 p_Periodicity_Id IN NUMBER
2899 , p_Time_Stamp IN VARCHAR2
2900 , x_Return_Status OUT NOCOPY VARCHAR2
2901 , x_Msg_Count OUT NOCOPY NUMBER
2902 , x_Msg_Data OUT NOCOPY VARCHAR2
2903 ) IS
2904 l_Object_Id NUMBER;
2905 l_Last_Update_Date DATE;
2906 l_Sql VARCHAR2(1024);
2907 l_Cursor BSC_BIS_LOCKS_PUB.t_CURSOR;
2908 l_Meaning BSC_LOOKUPS.MEANING%TYPE;
2909
2910 BEGIN
2911 SAVEPOINT LockPeriodicityPVT;
2912 FND_MSG_PUB.Initialize;
2913
2914 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
2915
2916 l_Sql := ' SELECT PERIODICITY_ID, LAST_UPDATE_DATE '
2917 ||' FROM BSC_SYS_PERIODICITIES_TL '
2918 ||' WHERE PERIODICITY_ID = :1 '
2919 ||' AND LANGUAGE = USERENV(''LANG'') '
2920 ||' FOR UPDATE NOWAIT ';
2921
2922 OPEN l_Cursor FOR l_Sql USING p_Periodicity_Id;
2923 FETCH l_Cursor INTO l_Object_Id, l_Last_Update_Date;
2924 IF (l_Cursor%NOTFOUND) THEN
2925 CLOSE l_Cursor;
2926 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
2927 l_Meaning := Bsc_Apps.Get_Lookup_Value('BSC_UI_COMMON', 'PERIODICITY');
2928 FND_MESSAGE.SET_TOKEN('TYPE', l_Meaning, TRUE);
2929 FND_MSG_PUB.ADD;
2930 RAISE FND_API.G_EXC_ERROR;
2931 END IF;
2932
2933 IF NOT p_Time_Stamp IS NULL THEN
2934 IF p_Time_Stamp <> TO_CHAR(l_Last_Update_Date, BSC_BIS_LOCKS_PUB.C_TIME_STAMP_FORMAT) THEN
2935 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_MODIFIED_PERIODICITY');
2936 FND_MESSAGE.SET_TOKEN('PERIODICITY', Get_Periodicity_Name(p_Periodicity_Id));
2937 FND_MSG_PUB.ADD;
2938 x_Return_Status := FND_API.G_RET_STS_ERROR;
2939 RAISE FND_API.G_EXC_ERROR;
2940 END IF;
2941 END IF;
2942
2943 IF (l_Cursor%ISOPEN) THEN
2944 CLOSE l_Cursor;
2945 END IF;
2946 EXCEPTION
2947 WHEN FND_API.G_EXC_ERROR THEN
2948 ROLLBACK TO LockPeriodicityPVT;
2949 IF (l_Cursor%ISOPEN) THEN
2950 CLOSE l_Cursor;
2951 END IF;
2952
2953 IF (x_msg_data IS NULL) THEN
2954 FND_MSG_PUB.Count_And_Get
2955 ( p_encoded => FND_API.G_FALSE
2956 , p_count => x_msg_count
2957 , p_data => x_msg_data
2958 );
2959 END IF;
2960 x_Return_Status := FND_API.G_RET_STS_ERROR;
2961 RAISE;
2962 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2963 ROLLBACK TO LockPeriodicityPVT;
2964 IF (l_Cursor%ISOPEN) THEN
2965 CLOSE l_Cursor;
2966 END IF;
2967 IF (x_msg_data IS NULL) THEN
2968 FND_MSG_PUB.Count_And_Get
2969 ( p_encoded => FND_API.G_FALSE
2970 , p_count => x_msg_count
2971 , p_data => x_msg_data
2972 );
2973 END IF;
2974 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2975 RAISE;
2976 WHEN NO_DATA_FOUND THEN
2977 ROLLBACK TO LockPeriodicityPVT;
2978 IF (l_Cursor%ISOPEN) THEN
2979 CLOSE l_Cursor;
2980 END IF;
2981 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2982 IF (x_msg_data IS NOT NULL) THEN
2983 x_msg_data := x_msg_data||' -> BSC_BIS_LOCKS_PVT.Lock_Periodicity ';
2984 ELSE
2985 x_msg_data := SQLERRM||' at BSC_BIS_LOCKS_PVT.Lock_Periodicity ';
2986 END IF;
2987 RAISE;
2988 WHEN OTHERS THEN
2989 ROLLBACK TO LockPeriodicityPVT;
2990 IF (l_Cursor%ISOPEN) THEN
2991 CLOSE l_Cursor;
2992 END IF;
2993 IF (l_Cursor%ISOPEN) THEN
2994 CLOSE l_Cursor;
2995 END IF;
2996 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2997 IF (SQLCODE = -00054) THEN
2998 FND_MSG_PUB.Initialize;
2999 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_LOCKED_PERIODICITY');
3000 FND_MESSAGE.SET_TOKEN('PERIODICITY', Get_Periodicity_Name(p_Periodicity_Id));
3001 FND_MSG_PUB.ADD;
3002 x_Return_Status := FND_API.G_RET_STS_ERROR;
3003
3004 FND_MSG_PUB.Count_And_Get
3005 ( p_encoded => FND_API.G_FALSE
3006 , p_count => x_msg_count
3007 , p_data => x_msg_data
3008 );
3009 RAISE FND_API.G_EXC_ERROR;
3010 ELSE
3011 IF (x_msg_data IS NOT NULL) THEN
3012 x_msg_data := x_msg_data||' -> BSC_BIS_LOCKS_PVT.Lock_Periodicity ';
3013 ELSE
3014 x_msg_data := SQLERRM||' at BSC_BIS_LOCKS_PVT.Lock_Periodicity ';
3015 END IF;
3016 END IF;
3017 RAISE;
3018 END Lock_Periodicity;
3019
3020 FUNCTION Get_Calendar_Name (
3021 p_Calendar_Id IN NUMBER
3022 ) RETURN VARCHAR2 IS
3023 l_Calendar_Name BSC_SYS_CALENDARS_VL.NAME%TYPE;
3024 BEGIN
3025
3026 SELECT C.NAME INTO l_Calendar_Name
3027 FROM BSC_SYS_CALENDARS_VL C
3028 WHERE C.CALENDAR_ID = p_Calendar_Id;
3029
3030 RETURN l_Calendar_Name;
3031
3032 EXCEPTION
3033 WHEN OTHERS THEN
3034 RETURN NULL;
3035 END Get_Calendar_Name;
3036
3037
3038 FUNCTION Get_Periodicity_Name (
3039 p_Periodicity_Id IN NUMBER
3040 ) RETURN VARCHAR2 IS
3041 l_Periodicity_Name BSC_SYS_PERIODICITIES_VL.NAME%TYPE;
3042 BEGIN
3043 SELECT P.NAME INTO l_Periodicity_Name
3044 FROM BSC_SYS_PERIODICITIES_VL P
3045 WHERE P.PERIODICITY_ID = p_Periodicity_Id;
3046
3047
3048 RETURN l_Periodicity_Name;
3049 EXCEPTION
3050 WHEN OTHERS THEN
3051 RETURN NULL;
3052 END Get_Periodicity_Name;
3053
3054
3055
3056 End BSC_BIS_LOCKS_PVT;