[Home] [Help]
PACKAGE BODY: APPS.BSC_BIS_LOCKS_PUB
Source
1 PACKAGE BODY BSC_BIS_LOCKS_PUB as
2 /* $Header: BSCPLOCB.pls 120.5 2006/04/20 07:26:37 visuri noship $ */
3
4 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_BIS_LOCKS_PUB';
5 g_db_object varchar2(30) := null;
6 /*------------------------------------------------------------------------------------------
7 Getting Time Stamp for Dataset
8 -------------------------------------------------------------------------------------------*/
9 Function GET_TIME_STAMP_DATASET (
10 p_dataset_id IN number
11 ) return varchar2 is
12 Begin
13 return BSC_BIS_LOCKS_PVT.GET_TIME_STAMP_DATASET (p_dataset_id );
14 EXCEPTION
15 WHEN OTHERS THEN
16 return NULL;
17 end GET_TIME_STAMP_DATASET;
18 /*------------------------------------------------------------------------------------------
19 Getting Time Stamp for Datasource
20 -------------------------------------------------------------------------------------------*/
21 Function GET_TIME_STAMP_DATASOURCE (
22 p_measure_id IN number
23 ) return varchar2 is
24 Begin
25 return BSC_BIS_LOCKS_PVT.GET_TIME_STAMP_DATASOURCE (p_measure_id);
26 EXCEPTION
27 WHEN OTHERS THEN
28 return NULL;
29 end GET_TIME_STAMP_DATASOURCE;
30
31 /*------------------------------------------------------------------------------------------
32 Setting Time Stamp for Data set
33 -------------------------------------------------------------------------------------------*/
34 Procedure SET_TIME_STAMP_DATASET (
35 p_dataset_id IN number
36 ,x_return_status OUT NOCOPY varchar2
37 ,x_msg_count OUT NOCOPY number
38 ,x_msg_data OUT NOCOPY varchar2
39 ) is
40
41 begin
42
43 SAVEPOINT BSCSetTimeDataSetPUB;
44
45 /* change time stamp for current dataset */
46 BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DATASET (
47 p_dataset_id
48 ,sysdate
49 ,x_return_status
50 ,x_msg_count
51 ,x_msg_data
52 );
53 EXCEPTION
54 WHEN FND_API.G_EXC_ERROR THEN
55 ROLLBACK TO BSCSetTimeDataSetPUB;
56 x_return_status := FND_API.G_RET_STS_ERROR;
57 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
58 ,p_data => x_msg_data);
59 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
60 ROLLBACK TO BSCSetTimeDataSetPUB;
61 --dbms_output.put_line(' G_EXC_UNEXPECTED_ERROR ' );
62
63 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
64 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
65 ,p_data => x_msg_data);
66 WHEN OTHERS THEN
67 --dbms_output.put_line(' OTHERS ' );
68 ROLLBACK TO BSCSetTimeDataSetPUB;
69 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
70 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
71 ,p_data => x_msg_data);
72
73 end SET_TIME_STAMP_DATASET;
74
75 /*------------------------------------------------------------------------------------------
76 Bug#4045278: Overloaded for Setting Time Stamp for Data set to take in last_update_date parameter
77 -------------------------------------------------------------------------------------------*/
78 Procedure SET_TIME_STAMP_DATASET (
79 p_dataset_id IN number
80 ,p_lud IN BSC_SYS_DATASETS_B.LAST_UPDATE_DATE%TYPE
81 ,x_return_status OUT NOCOPY varchar2
82 ,x_msg_count OUT NOCOPY number
83 ,x_msg_data OUT NOCOPY varchar2
84 ) is
85 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
86 l_sql varchar2(3000);
87 l_operation varchar2(20);
88 l_measure_id1 number;
89 l_measure_id2 number;
90 l_dataset_id number;
91
92 begin
93
94 SAVEPOINT BSCSetTimeDataSetPUB;
95
96 /* change time stamp for current dataset */
97 BSC_BIS_LOCKS_PVT.SET_TIME_STAMP_DATASET (
98 p_dataset_id
99 ,p_lud
100 ,x_return_status
101 ,x_msg_count
102 ,x_msg_data
103 );
104 --dbms_output.put_line(' x_return_status = ' || x_return_status );
105
106 /* change Time stamp of the all Dimension Set using Measures used by currrent
107 dataset */
108 l_sql := '
109 SELECT MEASURE_ID1, OPERATION, MEASURE_ID2
110 FROM BSC_SYS_DATASETS_VL
111 WHERE DATASET_ID =:1';
112 open l_cursor for l_sql USING p_dataset_id;
113 --dbms_output.put_line(' l_sql = ' || l_sql );
114
115 -- mdamle 7/11/03 - Fixed order of fetch
116 fetch l_cursor into l_measure_id1, l_operation, l_measure_id2;
117 --dbms_output.put_line(' l_measure_id1 = ' || l_measure_id1 );
118 --dbms_output.put_line(' l_measure_id2 = ' || l_measure_id2 );
119 if (l_cursor%found) then
120 l_sql := '
121 SELECT DISTINCT DATASET_ID
122 FROM BSC_SYS_DATASETS_B
123 WHERE (MEASURE_ID1 =:1 OR MEASURE_ID1 =:2
124 OR MEASURE_ID2 =:3 OR MEASURE_ID2 =:4)';
125
126 open l_cursor for l_sql USING l_measure_id1, nvl(l_measure_id2,l_measure_id1)
127 ,l_measure_id1, nvl(l_measure_id2,l_measure_id1);
128 --dbms_output.put_line(' l_sql = ' || l_sql );
129 loop
130 fetch l_cursor into l_dataset_id;
131 EXIT WHEN l_cursor%NOTFOUND;
132 --dbms_output.put_line(' l_dataset_id = ' || l_dataset_id );
133 if l_dataset_id <> p_dataset_id then
134 BSC_BIS_LOCKS_PVT.SET_TIME_STAMP_DATASET (
135 l_dataset_id
136 ,p_lud
137 ,x_return_status
138 ,x_msg_count
139 ,x_msg_data
140 );
141 --dbms_output.put_line(' x_return_status = ' || x_return_status );
142 end if;
143 end loop;
144 end if;
145 close l_cursor;
146
147 EXCEPTION
148 WHEN FND_API.G_EXC_ERROR THEN
149 ROLLBACK TO BSCSetTimeDataSetPUB;
150 x_return_status := FND_API.G_RET_STS_ERROR;
151 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
152 ,p_data => x_msg_data);
153 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
154 ROLLBACK TO BSCSetTimeDataSetPUB;
155 --dbms_output.put_line(' G_EXC_UNEXPECTED_ERROR ' );
156
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 --dbms_output.put_line(' OTHERS ' );
162 ROLLBACK TO BSCSetTimeDataSetPUB;
163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
164 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
165 ,p_data => x_msg_data);
166
167 end SET_TIME_STAMP_DATASET;
168
169 /*------------------------------------------------------------------------------------------
170 Setting Time Stamp for Datasource
171 -------------------------------------------------------------------------------------------*/
172 Procedure SET_TIME_STAMP_DATASOURCE (
173 p_measure_id IN number
174 ,x_return_status OUT NOCOPY varchar2
175 ,x_msg_count OUT NOCOPY number
176 ,x_msg_data OUT NOCOPY varchar2
177 ) is
178 begin
179
180 SAVEPOINT BSCSetTimeDataSrcPUB;
181
182
183 BSC_BIS_LOCKS_PUB.SET_TIME_STAMP_DATASOURCE (
184 p_measure_id
185 ,sysdate
186 ,x_return_status
187 ,x_msg_count
188 ,x_msg_data
189 );
190 EXCEPTION
191 WHEN FND_API.G_EXC_ERROR THEN
192 ROLLBACK TO BSCSetTimeDataSrcPUB;
193 x_return_status := FND_API.G_RET_STS_ERROR;
194 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
195 ,p_data => x_msg_data);
196 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
197 ROLLBACK TO BSCSetTimeDataSrcPUB;
198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
199 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
200 ,p_data => x_msg_data);
201 WHEN OTHERS THEN
202 ROLLBACK TO BSCSetTimeDataSrcPUB;
203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
204 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
205 ,p_data => x_msg_data);
206
207 end SET_TIME_STAMP_DATASOURCE;
208
209 /*------------------------------------------------------------------------------------------
210 Bug#4045278: Overloaded for Setting Time Stamp for Datasource to take in last_update_date parameter
211 -------------------------------------------------------------------------------------------*/
212 Procedure SET_TIME_STAMP_DATASOURCE (
213 p_measure_id IN number
214 ,p_lud IN BSC_SYS_MEASURES.LAST_UPDATE_DATE%TYPE
215 ,x_return_status OUT NOCOPY varchar2
216 ,x_msg_count OUT NOCOPY number
217 ,x_msg_data OUT NOCOPY varchar2
218 ) is
219 begin
220
221 SAVEPOINT BSCSetTimeDataSrcPUB;
222
223
224 BSC_BIS_LOCKS_PVT.SET_TIME_STAMP_DATASOURCE (
225 p_measure_id
226 ,p_lud
227 ,x_return_status
228 ,x_msg_count
229 ,x_msg_data
230 );
231 EXCEPTION
232 WHEN FND_API.G_EXC_ERROR THEN
233 ROLLBACK TO BSCSetTimeDataSrcPUB;
234 x_return_status := FND_API.G_RET_STS_ERROR;
235 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
236 ,p_data => x_msg_data);
237 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
238 ROLLBACK TO BSCSetTimeDataSrcPUB;
239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
240 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
241 ,p_data => x_msg_data);
242 WHEN OTHERS THEN
243 ROLLBACK TO BSCSetTimeDataSrcPUB;
244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
245 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
246 ,p_data => x_msg_data);
247
248 end SET_TIME_STAMP_DATASOURCE;
249 /*------------------------------------------------------------------------------------------
250 Procedure to Lock a Datasets
251 -------------------------------------------------------------------------------------------------------------------*/
252 Procedure LOCK_DATASET (
253 p_dataset_id IN number
254 ,p_time_stamp IN varchar2 /*:= null*/
255 ,x_return_status OUT NOCOPY varchar2
256 ,x_msg_count OUT NOCOPY number
257 ,x_msg_data OUT NOCOPY varchar2
258 ) is
259 l_cursor BSC_BIS_LOCKS_PUB.t_cursor;
260 l_sql varchar2(3000);
261 l_measure_id1 number;
262 l_measure_id2 number;
263 l_temp_id1 number;
264 l_temp_id2 number;
265 l_dataset_id number;
266 l_kpi_id number;
267
268 Begin
269 /* Lock the Current Dataset and the Measures assicated with it */
270 --dbms_output.put_line(' p_dataset_id = '|| p_dataset_id);
271
272 SAVEPOINT BSCLockDataSetPUB;
273
274 BSC_BIS_LOCKS_PVT.LOCK_DATASET (
275 p_dataset_id => p_dataset_id
276 ,p_time_stamp => p_time_stamp
277 ,x_measure_id1 => l_measure_id1
278 ,x_measure_id2 => l_measure_id2
279 ,x_return_status => x_return_status
280 ,x_msg_count => x_msg_count
281 ,x_msg_data => x_msg_data
282 ) ;
283 --dbms_output.put_line(' l_measure_id1 = '|| l_measure_id1);
284 --dbms_output.put_line(' l_measure_id2 = '|| l_measure_id2);
285
286 /* Lock the Datasets using Measures associated with curent dataset */
287
288 l_sql := '
289 SELECT DISTINCT DATASET_ID
290 FROM BSC_SYS_DATASETS_B
291 WHERE (MEASURE_ID1 =:1 OR MEASURE_ID1 =:2
292 OR MEASURE_ID2 =:3 OR MEASURE_ID2 =:4)';
293
294 open l_cursor for l_sql USING l_measure_id1, nvl(l_measure_id2,l_measure_id1)
295 ,l_measure_id1, nvl(l_measure_id2,l_measure_id1);
296
297 loop
298 fetch l_cursor into l_dataset_id;
299 EXIT WHEN l_cursor%NOTFOUND;
300 if l_dataset_id <> p_dataset_id then
301 --dbms_output.put_line(' l_dataset_id = '|| l_dataset_id);
302 BSC_BIS_LOCKS_PVT.LOCK_DATASET (
303 p_dataset_id => l_dataset_id
304 ,p_time_stamp => null
305 ,x_measure_id1 => l_temp_id1
306 ,x_measure_id2 => l_temp_id1
307 ,x_return_status => x_return_status
308 ,x_msg_count => x_msg_count
309 ,x_msg_data => x_msg_data
310 ) ;
311 end if;
312 --dbms_output.put_line(' l_dataset_id = '|| l_dataset_id);
313 --dbms_output.put_line(' x_return_status = '|| x_return_status);
314 end loop;
315 close l_cursor;
316
317 /* Lock the KPIs associated with curent dataset */
318 --Performance fix PAJOHRI 28-AUG-2003
319 l_sql := '
320 SELECT DISTINCT KM.INDICATOR
321 FROM bsc_kpi_analysis_measures_b KM,
322 ( SELECT DATASET_ID
323 FROM BSC_SYS_DATASETS_B
324 WHERE MEASURE_ID1 IN (:1,:2)
325 UNION ALL
326 SELECT DATASET_ID
327 FROM BSC_SYS_DATASETS_B
328 where MEASURE_ID2 IN (:3,:4)
329 ) D
330 WHERE KM.DATASET_ID = D.DATASET_ID';
331
332 open l_cursor for l_sql USING l_measure_id1, nvl(l_measure_id2,l_measure_id1)
333 ,l_measure_id1, nvl(l_measure_id2,l_measure_id1);
334 loop
335 fetch l_cursor into l_kpi_id;
336 EXIT WHEN l_cursor%NOTFOUND;
337 if l_dataset_id <> p_dataset_id then
338 --dbms_output.put_line(' l_kpi_id = '|| l_kpi_id);
339 BSC_BIS_LOCKS_PUB.LOCK_KPI(
340 p_Kpi_Id => l_kpi_id
341 ,p_time_stamp => null
342 ,p_Full_Lock_Flag => null
343 ,x_return_status => x_return_status
344 ,x_msg_count => x_msg_count
345 ,x_msg_data => x_msg_data
346 );
347 end if;
348 end loop;
349 close l_cursor;
350
351 EXCEPTION
352 WHEN FND_API.G_EXC_ERROR THEN
353 ROLLBACK TO BSCLockDataSetPUB;
354 x_return_status := FND_API.G_RET_STS_ERROR;
355 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
356 ,p_data => x_msg_data);
357 raise;
358 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
359 ROLLBACK TO BSCLockDataSetPUB;
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
361 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
362 ,p_data => x_msg_data);
363 raise;
364 WHEN OTHERS THEN
365 ROLLBACK TO BSCLockDataSetPUB;
366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
367 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
368 ,p_data => x_msg_data);
369 raise;
370 end LOCK_DATASET;
371
372 /*------------------------------------------------------------------------------------------
373 Procedure to Lock a Datasource
374 -------------------------------------------------------------------------------------------------------------------*/
375 Procedure LOCK_DATASOURCE(
376 p_measure_id IN number
377 ,p_time_stamp IN varchar2 /*:= null*/
378 ,x_return_status OUT NOCOPY varchar2
379 ,x_msg_count OUT NOCOPY number
380 ,x_msg_data OUT NOCOPY varchar2
381 ) is
382
383 Begin
384
385 SAVEPOINT BSCLockDataSrcPUB;
386
387
388 BSC_BIS_LOCKS_PVT.LOCK_DATASOURCE(
389 p_measure_id
390 ,p_time_stamp
391 ,null
392 ,x_return_status
393 ,x_msg_count
394 ,x_msg_data
395 ) ;
396
397 EXCEPTION
398 WHEN FND_API.G_EXC_ERROR THEN
399 ROLLBACK TO BSCLockDataSrcPUB;
400 x_return_status := FND_API.G_RET_STS_ERROR;
401 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
402 ,p_data => x_msg_data);
403 raise;
404 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
405 ROLLBACK TO BSCLockDataSrcPUB;
406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
407 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
408 ,p_data => x_msg_data);
409 raise;
410 WHEN OTHERS THEN
411 ROLLBACK TO BSCLockDataSrcPUB;
412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
413 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
414 ,p_data => x_msg_data);
415 raise;
416 end LOCK_DATASOURCE;
417
418 /*------------------------------------------------------------------------------------------
419 4.2.1. Lock prcedure to Create a new Measure
420 -------------------------------------------------------------------------------------------------------------------*/
421 /*
422 Procedure LOCK_CREATE_MEASURE (
423 p_dataset_id IN number
424 ,x_return_status OUT NOCOPY varchar2
425 ,x_msg_count OUT NOCOPY number
426 ,x_msg_data OUT NOCOPY varchar2
427 ) is
428
429 Begin
430
431 LOCK_DATASET (
432 p_dataset_id => p_dataset_id
433 ,p_time_stamp => p_time_stamp
434 ,x_return_status => x_return_status
435 ,x_msg_count => x_msg_count
436 ,x_msg_data => x_msg_data
437 );
438
439 EXCEPTION
440 WHEN FND_API.G_EXC_ERROR THEN
441 rollback;
442 x_return_status := FND_API.G_RET_STS_ERROR;
443 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
444 ,p_data => x_msg_data);
445 raise;
446 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
447 rollback;
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
450 ,p_data => x_msg_data);
451 raise;
452 WHEN OTHERS THEN
453 rollback;
454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
455 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
456 ,p_data => x_msg_data);
457 raise;
458 end LOCK_CREATE_MEASURE;
459 */
460
461 /*------------------------------------------------------------------------------------------
462 4.2.2. Lock prcedure to Update an existing Measure
463 -------------------------------------------------------------------------------------------------------------------*/
464 Procedure LOCK_UPDATE_MEASURE (
465 p_dataset_id IN number
466 ,p_time_stamp IN varchar2 /*:= null*/
467 ,x_return_status OUT NOCOPY varchar2
468 ,x_msg_count OUT NOCOPY number
469 ,x_msg_data OUT NOCOPY varchar2
470 ) is
471
472 Begin
473
474 LOCK_DATASET (
475 p_dataset_id => p_dataset_id
476 ,p_time_stamp => p_time_stamp
477 ,x_return_status => x_return_status
478 ,x_msg_count => x_msg_count
479 ,x_msg_data => x_msg_data
480 );
481
482 EXCEPTION
483 WHEN FND_API.G_EXC_ERROR THEN
484 --rollback;
485 x_return_status := FND_API.G_RET_STS_ERROR;
486 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
487 ,p_data => x_msg_data);
488 raise;
489 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
490 --rollback;
491 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
492 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
493 ,p_data => x_msg_data);
494 raise;
495 WHEN OTHERS THEN
496 --rollback;
497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
498 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
499 ,p_data => x_msg_data);
500 raise;
501 end LOCK_UPDATE_MEASURE;
502 /*------------------------------------------------------------------------------------------
503 4.2.3. Lock prcedure to Delete an existing Measure
504 -------------------------------------------------------------------------------------------------------------------*/
505 Procedure LOCK_DELETE_MEASURE (
506 p_dataset_id IN number
507 ,p_time_stamp IN varchar2 /*:= null*/
508 ,x_return_status OUT NOCOPY varchar2
509 ,x_msg_count OUT NOCOPY number
510 ,x_msg_data OUT NOCOPY varchar2
511 ) is
512
513 Begin
514
515 LOCK_DATASET (
516 p_dataset_id => p_dataset_id
517 ,p_time_stamp => p_time_stamp
518 ,x_return_status => x_return_status
519 ,x_msg_count => x_msg_count
520 ,x_msg_data => x_msg_data
521 );
522
523 EXCEPTION
524 WHEN FND_API.G_EXC_ERROR THEN
525 --rollback;
526 x_return_status := FND_API.G_RET_STS_ERROR;
527 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
528 ,p_data => x_msg_data);
529 raise;
530 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
531 --rollback;
532 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
533 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
534 ,p_data => x_msg_data);
535 raise;
536 WHEN OTHERS THEN
537 --rollback;
538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
539 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
540 ,p_data => x_msg_data);
541 raise;
542 end LOCK_DELETE_MEASURE;
543 /*------------------------------------------------------------------------------------------
544 4.2.4. Lock prcedure to Assign Dataset to Analysis option combination (KPI)
545 -------------------------------------------------------------------------------------------------------------------*/
546 Procedure LOCK_ASSIGN_MEASURE (
547 p_kpi_Id IN number
548 ,p_dataset_id IN number
549 ,p_time_stamp IN varchar2 /* := null */
550 ,x_return_status OUT NOCOPY varchar2
551 ,x_msg_count OUT NOCOPY number
552 ,x_msg_data OUT NOCOPY varchar2
553 ) is
554 l_temp1 number;
555 l_temp2 number;
556 Begin
557
558
559 SAVEPOINT BSCLockAsgnMeasurePUB;
560 /* Lock the KPI */
561 BSC_BIS_LOCKS_PUB.LOCK_KPI(
562 p_Kpi_Id => p_Kpi_Id
563 ,p_time_stamp => p_time_stamp
564 ,p_Full_Lock_Flag => null
565 ,x_return_status => x_return_status
566 ,x_msg_count => x_msg_count
567 ,x_msg_data => x_msg_data
568 );
569
570 /* Lock the Dataset and the associated Measures */
571 BSC_BIS_LOCKS_PVT.LOCK_DATASET (
572 p_dataset_id => p_dataset_id
573 ,p_time_stamp => null
574 ,x_measure_id1 => l_temp1
575 ,x_measure_id2 => l_temp2
576 ,x_return_status => x_return_status
577 ,x_msg_count => x_msg_count
578 ,x_msg_data => x_msg_data
579 ) ;
580
581
582 EXCEPTION
583 WHEN FND_API.G_EXC_ERROR THEN
584 ROLLBACK TO BSCLockAsgnMeasurePUB;
585 x_return_status := FND_API.G_RET_STS_ERROR;
586 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
587 ,p_data => x_msg_data);
588 raise;
589 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
590 ROLLBACK TO BSCLockAsgnMeasurePUB;
591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
592 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
593 ,p_data => x_msg_data);
594 raise;
595 WHEN OTHERS THEN
596 ROLLBACK TO BSCLockAsgnMeasurePUB;
597 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
598 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
599 ,p_data => x_msg_data);
600 raise;
601 end LOCK_ASSIGN_MEASURE;
602 /*-------------------------------------------------------------------------------------------------------------------
603 Procedure to Lock a Dimension Objects
604 -------------------------------------------------------------------------------------------------------------------*/
605
606 Procedure LOCK_DIM_LEVEL(
607 p_dim_level_id IN number
608 ,p_time_stamp IN varchar2 /*:= null*/
609 ,x_return_status OUT NOCOPY varchar2
610 ,x_msg_count OUT NOCOPY number
611 ,x_msg_data OUT NOCOPY varchar2
612 ) is
613 begin
614
615 SAVEPOINT BSCLockDimObjPUB;
616
617
618 BSC_BIS_LOCKS_PVT.LOCK_DIM_LEVEL(
619 p_dim_level_id
620 ,p_time_stamp
621 ,x_return_status
622 ,x_msg_count
623 ,x_msg_data
624 );
625
626 EXCEPTION
627 WHEN FND_API.G_EXC_ERROR THEN
628 ROLLBACK TO BSCLockDimObjPUB;
629 x_return_status := FND_API.G_RET_STS_ERROR;
630 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
631 ,p_data => x_msg_data);
632 raise;
633 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
634 ROLLBACK TO BSCLockDimObjPUB;
635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
636 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
637 ,p_data => x_msg_data);
638 raise;
639 WHEN OTHERS THEN
640 ROLLBACK TO BSCLockDimObjPUB;
641 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
642 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
643 ,p_data => x_msg_data);
644 raise;
645 end LOCK_DIM_LEVEL;
646
647 /*-------------------------------------------------------------------------------------------------------------------
648 Procedure to Lock a Dimension Group
649 -------------------------------------------------------------------------------------------------------------------*/
650
651 Procedure LOCK_DIM_GROUP (
652 p_dim_group_id IN number
653 ,p_time_stamp IN varchar2 /*:= null */
654 ,x_return_status OUT NOCOPY varchar2
655 ,x_msg_count OUT NOCOPY number
656 ,x_msg_data OUT NOCOPY varchar2
657
658 ) is
659 begin
660
661 SAVEPOINT BSCLockDimPUB;
662
663
664 BSC_BIS_LOCKS_PVT.LOCK_DIM_GROUP (
665 p_dim_group_id
666 ,p_time_stamp
667 ,x_return_status
668 ,x_msg_count
669 ,x_msg_data
670
671 );
672
673 EXCEPTION
674 WHEN FND_API.G_EXC_ERROR THEN
675 ROLLBACK TO BSCLockDimPUB;
676 x_return_status := FND_API.G_RET_STS_ERROR;
677 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
678 ,p_data => x_msg_data);
679 raise;
680 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
681 ROLLBACK TO BSCLockDimPUB;
682 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
683 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
684 ,p_data => x_msg_data);
685 raise;
686 WHEN OTHERS THEN
687 ROLLBACK TO BSCLockDimPUB;
688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
689 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
690 ,p_data => x_msg_data);
691 raise;
692 end LOCK_DIM_GROUP;
693
694 /*-------------------------------------------------------------------------------------------------------------------
695 Procedure to Lock a Dimension Set
696 -------------------------------------------------------------------------------------------------------------------*/
697
698 Procedure LOCK_DIM_SET (
699 p_kpi_Id IN number
700 ,p_dim_set_id IN number
701 ,p_time_stamp IN varchar2 /*:= null*/
702 ,x_return_status OUT NOCOPY varchar2
703 ,x_msg_count OUT NOCOPY number
704 ,x_msg_data OUT NOCOPY varchar2
705 ) is
706
707 Begin
708
709 SAVEPOINT BSCLockDimSetPUB;
710
711
712 BSC_BIS_LOCKS_PVT.LOCK_DIM_SET (
713 p_kpi_Id
714 ,p_dim_set_id
715 ,p_time_stamp
716 ,x_return_status
717 ,x_msg_count
718 ,x_msg_data
719 );
720
721 EXCEPTION
722 WHEN FND_API.G_EXC_ERROR THEN
723 ROLLBACK TO BSCLockDimSetPUB;
724 x_return_status := FND_API.G_RET_STS_ERROR;
725 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
726 ,p_data => x_msg_data);
727 raise;
728 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
729 ROLLBACK TO BSCLockDimSetPUB;
730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
731 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
732 ,p_data => x_msg_data);
733 raise;
734 WHEN OTHERS THEN
735 ROLLBACK TO BSCLockDimSetPUB;
736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
737 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
738 ,p_data => x_msg_data);
739 raise;
740 end LOCK_DIM_SET;
741
742 /*-------------------------------------------------------------------------------------------------------------------
743 Procedure to Lock a KPI
744 -------------------------------------------------------------------------------------------------------------------*/
745 Procedure LOCK_KPI(
746 p_kpi_Id IN number
747 ,p_time_stamp IN varchar2 /* := null */
748 ,p_full_lock_flag IN varchar2 /*:= FND_API.G_FALSE */
749 ,x_return_status OUT NOCOPY varchar2
750 ,x_msg_count OUT NOCOPY number
751 ,x_msg_data OUT NOCOPY varchar2
752 ) is
753
754 Begin
755
756 SAVEPOINT BSCLockKpiPUB;
757
758
759 BSC_BIS_LOCKS_PVT.LOCK_KPI(
760 p_kpi_Id
761 ,p_time_stamp
762 ,p_full_lock_flag
763 ,x_return_status
764 ,x_msg_count
765 ,x_msg_data
766 );
767
768 EXCEPTION
769 WHEN FND_API.G_EXC_ERROR THEN
770 ROLLBACK TO BSCLockKpiPUB;
771 x_return_status := FND_API.G_RET_STS_ERROR;
772 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
773 ,p_data => x_msg_data);
774 raise;
775 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
776 ROLLBACK TO BSCLockKpiPUB;
777 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
778 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
779 ,p_data => x_msg_data);
780 raise;
781 WHEN OTHERS THEN
782 ROLLBACK TO BSCLockKpiPUB;
783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
784 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
785 ,p_data => x_msg_data);
786 raise;
787 End LOCK_KPI;
788 /*------------------------------------------------------------------------------------------
789 Getting Time Stamp for Dimension Level
790 ------------------------------------------------------------------------------------------*/
791 Function GET_TIME_STAMP_DIM_LEVEL(
792 p_dim_level_id IN number
793 ) return varchar2 is
794 Begin
795 return BSC_BIS_LOCKS_PVT.GET_TIME_STAMP_DIM_LEVEL(p_dim_level_id);
796 EXCEPTION
797 WHEN OTHERS THEN
798 return NULL;
799 end GET_TIME_STAMP_DIM_LEVEL;
800
801 /*------------------------------------------------------------------------------------------
802 Getting Time Stamp for Dimension Group
803 -------------------------------------------------------------------------------------------*/
804 Function GET_TIME_STAMP_DIM_GROUP (
805 p_dim_group_id IN number
806 ) return varchar2 is
807 Begin
808 return BSC_BIS_LOCKS_PVT.GET_TIME_STAMP_DIM_GROUP(p_dim_group_id);
809 EXCEPTION
810 WHEN OTHERS THEN
811 return NULL;
812
813 end GET_TIME_STAMP_DIM_GROUP;
814 /*------------------------------------------------------------------------------------------
815 Getting Time Stamp Dimension Set
816 -------------------------------------------------------------------------------------------*/
817 Function GET_TIME_STAMP_DIM_SET (
818 p_kpi_Id IN number
819 ,p_dim_set_id IN number
820 ) return varchar2 is
821 Begin
822 return BSC_BIS_LOCKS_PVT.GET_TIME_STAMP_DIM_SET(p_kpi_Id,p_dim_set_id);
823 EXCEPTION
824 WHEN OTHERS THEN
825 return NULL;
826 end GET_TIME_STAMP_DIM_SET;
827
828 /*------------------------------------------------------------------------------------------
829 Getting Time Stamp for KPIs (Indicators)
830 -------------------------------------------------------------------------------------------*/
831 Function GET_TIME_STAMP_KPI (
832 p_kpi_Id IN number
833 ) return varchar2 is
834 Begin
835 return BSC_BIS_LOCKS_PVT.GET_TIME_STAMP_KPI(p_kpi_Id);
836 EXCEPTION
837 WHEN OTHERS THEN
838 return NULL;
839 end GET_TIME_STAMP_KPI;
840 /*------------------------------------------------------------------------------------------
841 Setting Time Stamp for Dimension Objects
842 -------------------------------------------------------------------------------------------*/
843 Procedure SET_TIME_STAMP_DIM_LEVEL (
844 p_dim_level_id IN number
845 ,x_return_status OUT NOCOPY varchar2
846 ,x_msg_count OUT NOCOPY number
847 ,x_msg_data OUT NOCOPY varchar2
848 ) is
849 begin
850 SAVEPOINT BSCSetTimeDimObjPUB;
851
852
853 BSC_BIS_LOCKS_PVT.SET_TIME_STAMP_DIM_LEVEL (
854 p_dim_level_id
855 ,x_return_status
856 ,x_msg_count
857 ,x_msg_data
858 );
859
860 EXCEPTION
861 WHEN FND_API.G_EXC_ERROR THEN
862 ROLLBACK TO BSCSetTimeDimObjPUB;
863 x_return_status := FND_API.G_RET_STS_ERROR;
864 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
865 ,p_data => x_msg_data);
866 raise;
867 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
868 ROLLBACK TO BSCSetTimeDimObjPUB;
869 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
870 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
871 ,p_data => x_msg_data);
872 raise;
873 WHEN OTHERS THEN
874 ROLLBACK TO BSCSetTimeDimObjPUB;
875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
876 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
877 ,p_data => x_msg_data);
878 raise;
879 end SET_TIME_STAMP_DIM_LEVEL;
880 /*------------------------------------------------------------------------------------------
881 Setting Time Stamp for Dimension Group
882 -------------------------------------------------------------------------------------------*/
883 Procedure SET_TIME_STAMP_DIM_GROUP (
884 p_dim_group_id IN number
885 ,x_return_status OUT NOCOPY varchar2
886 ,x_msg_count OUT NOCOPY number
887 ,x_msg_data OUT NOCOPY varchar2
888 ) is
889 begin
890
891 SAVEPOINT BSCSetTimeDimPUB;
892
893
894 BSC_BIS_LOCKS_PVT.SET_TIME_STAMP_DIM_GROUP (
895 p_dim_group_id
896 ,x_return_status
897 ,x_msg_count
898 ,x_msg_data
899 ) ;
900
901 EXCEPTION
902 WHEN FND_API.G_EXC_ERROR THEN
903 ROLLBACK TO BSCSetTimeDimPUB;
904 x_return_status := FND_API.G_RET_STS_ERROR;
905 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
906 ,p_data => x_msg_data);
907 raise;
908 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
909 ROLLBACK TO BSCSetTimeDimPUB;
910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
911 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
912 ,p_data => x_msg_data);
913 raise;
914 WHEN OTHERS THEN
915 ROLLBACK TO BSCSetTimeDimPUB;
916 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
917 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
918 ,p_data => x_msg_data);
919 raise;
920 end SET_TIME_STAMP_DIM_GROUP;
921 /*------------------------------------------------------------------------------------------
922 Setting Time Stamp for Dimension Set
923 -------------------------------------------------------------------------------------------*/
924 Procedure SET_TIME_STAMP_DIM_SET (
925 p_kpi_Id IN number
926 , p_dim_set_id IN number
927 ,x_return_status OUT NOCOPY varchar2
928 ,x_msg_count OUT NOCOPY number
929 ,x_msg_data OUT NOCOPY varchar2
930 ) is
931 begin
932
933 SAVEPOINT BSCSetTimeDimSetPUB;
934
935
936 BSC_BIS_LOCKS_PVT.SET_TIME_STAMP_DIM_SET (
937 p_kpi_Id
938 , p_dim_set_id
939 ,x_return_status
940 ,x_msg_count
941 ,x_msg_data
942 ) ;
943
944 EXCEPTION
945 WHEN FND_API.G_EXC_ERROR THEN
946 ROLLBACK TO BSCSetTimeDimSetPUB;
947 x_return_status := FND_API.G_RET_STS_ERROR;
948 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
949 ,p_data => x_msg_data);
950 raise;
951 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
952 ROLLBACK TO BSCSetTimeDimSetPUB;
953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
954 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
955 ,p_data => x_msg_data);
956 raise;
957 WHEN OTHERS THEN
958 ROLLBACK TO BSCSetTimeDimSetPUB;
959 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
960 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
961 ,p_data => x_msg_data);
962 raise;
963 end SET_TIME_STAMP_DIM_SET;
964 /*------------------------------------------------------------------------------------------
965 Setting Time Stamp for KPI
966 -------------------------------------------------------------------------------------------*/
967 Procedure SET_TIME_STAMP_KPI (
968 p_kpi_Id IN number
969 ,x_return_status OUT NOCOPY varchar2
970 ,x_msg_count OUT NOCOPY number
971 ,x_msg_data OUT NOCOPY varchar2
972 ) is
973 begin
974
975 SAVEPOINT BSCSetTimeKpiPUB;
976
977
978 BSC_BIS_LOCKS_PVT.SET_TIME_STAMP_KPI (
979 p_kpi_Id
980 ,x_return_status
981 ,x_msg_count
982 ,x_msg_data
983 );
984
985 EXCEPTION
986 WHEN FND_API.G_EXC_ERROR THEN
987 ROLLBACK TO BSCSetTimeKpiPUB;
988 x_return_status := FND_API.G_RET_STS_ERROR;
989 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
990 ,p_data => x_msg_data);
991 raise;
992 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
993 ROLLBACK TO BSCSetTimeKpiPUB;
994 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
995 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
996 ,p_data => x_msg_data);
997 raise;
998 WHEN OTHERS THEN
999 ROLLBACK TO BSCSetTimeKpiPUB;
1000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1001 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1002 ,p_data => x_msg_data);
1003 raise;
1004 end SET_TIME_STAMP_KPI;
1005
1006 /*------------------------------------------------------------------------------------------
1007 Procedure LOCK_CREATE_DIMENSION
1008
1009 This Procedure will make all the necessaries locks to Create a Dimensions (Dimension Group)
1010 according with the PMD UI for 'Performance Measures > Dimensions > Create Dimension'
1011 This procedure will lock all the dimension object that will assign to the new Dimension
1012 <parameters>
1013 p_selected_dim_objets: Array with the Ids corresponding to the Dimesion Objects
1014 that will be assigned to the new dimension.
1015 -------------------------------------------------------------------------------------------*/
1016 Procedure LOCK_CREATE_DIMENSION (
1017 p_selected_dim_objets IN t_numberTable
1018 ,x_return_status OUT NOCOPY varchar2
1019 ,x_msg_count OUT NOCOPY number
1020 ,x_msg_data OUT NOCOPY varchar2
1021 ) is
1022 Begin
1023
1024 SAVEPOINT BSCLockCreDimPUB;
1025
1026
1027 BSC_BIS_LOCKS_PVT.LOCK_CREATE_DIMENSION(
1028 p_selected_dim_objets
1029 ,x_return_status
1030 ,x_msg_count
1031 ,x_msg_data
1032 );
1033
1034 EXCEPTION
1035 WHEN FND_API.G_EXC_ERROR THEN
1036 ROLLBACK TO BSCLockCreDimPUB;
1037 x_return_status := FND_API.G_RET_STS_ERROR;
1038 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1039 ,p_data => x_msg_data);
1040 raise;
1041 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1042 ROLLBACK TO BSCLockCreDimPUB;
1043 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1044 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1045 ,p_data => x_msg_data);
1046 raise;
1047 WHEN OTHERS THEN
1048 ROLLBACK TO BSCLockCreDimPUB;
1049 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1050 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1051 ,p_data => x_msg_data);
1052 raise;
1053
1054 end LOCK_CREATE_DIMENSION;
1055 /*------------------------------------------------------------------------------------------
1056 Procedure LOCK_UPDATE_DIMENSION
1057 This Procedure will make all the necessaries locks to Update a Dimension (Dimension Group)
1058 according with the PMD UI for 'Performance Measures > Dimensions > Update Dimension'
1059 This procedure will lock the dimension passed in the parameter p_dimension_id,
1060 the dimension objects passed in the parameter p_selected_dim_objets,
1061 and the dimension set (in the kpis) that uses the dimension when it is necessary.
1062 <parameters>
1063 p_dimension_id: Dimension Id (Dimension Group) to update
1064 p_selected_dim_objets: This array has the Ids corresponding to the Dimension Objects
1065 that will have the dimension.
1066 p_time_stamp: Last update of dimension information changed by the user
1067
1068
1069 -------------------------------------------------------------------------------------------*/
1070 Procedure LOCK_UPDATE_DIMENSION (
1071 p_dimension_id IN number
1072 ,p_selected_dim_objets IN t_numberTable
1073 ,p_time_stamp IN varchar2 /*:= null*/
1074 ,x_return_status OUT NOCOPY varchar2
1075 ,x_msg_count OUT NOCOPY number
1076 ,x_msg_data OUT NOCOPY varchar2
1077 ) is
1078
1079 Begin
1080
1081 SAVEPOINT BSCLockUpdDimPUB;
1082
1083
1084 BSC_BIS_LOCKS_PVT.LOCK_UPDATE_DIMENSION (
1085 p_dimension_id
1086 ,p_selected_dim_objets
1087 ,p_time_stamp
1088 ,x_return_status
1089 ,x_msg_count
1090 ,x_msg_data
1091 ) ;
1092
1093 EXCEPTION
1094 WHEN FND_API.G_EXC_ERROR THEN
1095 ROLLBACK TO BSCLockUpdDimPUB;
1096 x_return_status := FND_API.G_RET_STS_ERROR;
1097 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1098 ,p_data => x_msg_data);
1099 raise;
1100 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1101 ROLLBACK TO BSCLockUpdDimPUB;
1102 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1103 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1104 ,p_data => x_msg_data);
1105 raise;
1106 WHEN OTHERS THEN
1107 ROLLBACK TO BSCLockUpdDimPUB;
1108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1109 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1110 ,p_data => x_msg_data);
1111 raise;
1112 end LOCK_UPDATE_DIMENSION;
1113
1114 /*------------------------------------------------------------------------------------------
1115 Procedure LOCK_UPDATE_DIM_OBJ_IN_DIM
1116 This procedure will make all the necessaries locks to Update a Dimension
1117 Object propertis in a dimencion.
1118 (Dimension level properties in a Dimension Group
1119
1120 -------------------------------------------------------------------------------------------*/
1121 Procedure LOCK_UPDATE_DIM_OBJ_IN_DIM(
1122 p_dim_object_id IN number
1123 ,p_dimension_id IN number
1124 ,p_time_stamp IN varchar2
1125 ,x_return_status OUT NOCOPY varchar2
1126 ,x_msg_count OUT NOCOPY number
1127 ,x_msg_data OUT NOCOPY varchar2
1128 )is
1129 Begin
1130
1131 SAVEPOINT BSCLockUpdDimInObjPUB;
1132
1133
1134 BSC_BIS_LOCKS_PVT.LOCK_UPDATE_DIM_OBJ_IN_DIM(
1135 p_dim_object_id
1136 ,p_dimension_id
1137 ,p_time_stamp
1138 ,x_return_status
1139 ,x_msg_count
1140 ,x_msg_data
1141 ) ;
1142
1143 EXCEPTION
1144 WHEN FND_API.G_EXC_ERROR THEN
1145 ROLLBACK TO BSCLockUpdDimInObjPUB;
1146 x_return_status := FND_API.G_RET_STS_ERROR;
1147 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1148 ,p_data => x_msg_data);
1149 raise;
1150 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1151 ROLLBACK TO BSCLockUpdDimInObjPUB;
1152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1153 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1154 ,p_data => x_msg_data);
1155 raise;
1156 WHEN OTHERS THEN
1157 ROLLBACK TO BSCLockUpdDimInObjPUB;
1158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1159 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1160 ,p_data => x_msg_data);
1161 raise;
1162 End LOCK_UPDATE_DIM_OBJ_IN_DIM;
1163
1164 /*------------------------------------------------------------------------------------------
1165 Procedure LOCK_CREATE_DIMENSION_OBJECT
1166 This procedure will make all the necessaries locks to Create a Dimension Object (Dimension Level)
1167 according with the PMD UI for 'Performance Measures > Dimensions > Dimension Objects >
1168 Create Dimension Object'
1169 <parameters>
1170 p_selected_dimensions: This Array has the Ids corresponding to the Dimensions where
1171 the dimension object will be assigned.
1172 -------------------------------------------------------------------------------------------*/
1173 Procedure LOCK_CREATE_DIMENSION_OBJECT(
1174 p_selected_dimensions IN t_numberTable
1175 ,x_return_status OUT NOCOPY varchar2
1176 ,x_msg_count OUT NOCOPY number
1177 ,x_msg_data OUT NOCOPY varchar2
1178 ) is
1179
1180 Begin
1181
1182 SAVEPOINT BSCLockCreDimObjPUB;
1183
1184
1185 BSC_BIS_LOCKS_PVT.LOCK_CREATE_DIMENSION_OBJECT(
1186 p_selected_dimensions
1187 ,x_return_status
1188 ,x_msg_count
1189 ,x_msg_data
1190 ) ;
1191 EXCEPTION
1192 WHEN FND_API.G_EXC_ERROR THEN
1193 ROLLBACK TO BSCLockCreDimObjPUB;
1194 x_return_status := FND_API.G_RET_STS_ERROR;
1195 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1196 ,p_data => x_msg_data);
1197 raise;
1198 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1199 ROLLBACK TO BSCLockCreDimObjPUB;
1200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1201 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1202 ,p_data => x_msg_data);
1203 raise;
1204 WHEN OTHERS THEN
1205 ROLLBACK TO BSCLockCreDimObjPUB;
1206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1207 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1208 ,p_data => x_msg_data);
1209 raise;
1210
1211 End LOCK_CREATE_DIMENSION_OBJECT;
1212 /*------------------------------------------------------------------------------------------
1213 Procedure LOCK_UPDATE_DIMENSION_OBJECT
1214 This procedure will make all the necessaries locks to Update a Dimension Object (Dimension Level)
1215 according with the PMD UI for 'Performance Measures > Dimensions > Dimension Objects >
1216 Update Dimension Object'
1217 <parameters>
1218 p_dim_object_id: Dimension Object Id (Dimension Level) to update
1219 p_selected_dim_objets: This array has the Ids corresponding to the Dimension Objects
1220 that will have the dimension.
1221 p_time_stamp: Last update of dimension object information changed by the user.
1222 It is mandatory in order of checking if the dimension object has been
1223 updated by other user.
1224 -------------------------------------------------------------------------------------------*/
1225 Procedure LOCK_UPDATE_DIMENSION_OBJECT(
1226 p_dim_object_id IN number
1227 ,p_selected_dimensions IN t_numberTable
1228 ,p_time_stamp IN varchar2
1229 ,x_return_status OUT NOCOPY varchar2
1230 ,x_msg_count OUT NOCOPY number
1231 ,x_msg_data OUT NOCOPY varchar2
1232 ) is
1233
1234 Begin
1235
1236 SAVEPOINT BSCLockUpdDimObjPUB;
1237
1238
1239 BSC_BIS_LOCKS_PVT.LOCK_UPDATE_DIMENSION_OBJECT(
1240 p_dim_object_id
1241 ,p_selected_dimensions
1242 ,p_time_stamp
1243 ,x_return_status
1244 ,x_msg_count
1245 ,x_msg_data
1246 ) ;
1247 EXCEPTION
1248 WHEN FND_API.G_EXC_ERROR THEN
1249 ROLLBACK TO BSCLockUpdDimObjPUB;
1250 x_return_status := FND_API.G_RET_STS_ERROR;
1251 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1252 ,p_data => x_msg_data);
1253 raise;
1254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1255 ROLLBACK TO BSCLockUpdDimObjPUB;
1256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1257 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1258 ,p_data => x_msg_data);
1259 raise;
1260 WHEN OTHERS THEN
1261 ROLLBACK TO BSCLockUpdDimObjPUB;
1262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1263 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1264 ,p_data => x_msg_data);
1265 raise;
1266
1267 end LOCK_UPDATE_DIMENSION_OBJECT;
1268 /*------------------------------------------------------------------------------------------
1269 Procedure LOCK_UPDATE_DIM_OBJ_RELATIONSHIPS
1270 This process Lock all affected object when the relationships for a given dimension
1271 object are updated.
1272 <parameters>
1273 p_dim_object_id: Dimension Object Id (Dimension Level) to update
1274 p_selected_parends: This array has the Ids corresponding to the Parent Dimension Objects
1275 that will have the dimension object (Selected Parent Dimension Objects)
1276 p_selected_childs: This array has the Ids corresponding to the Child Dimension Objects
1277 that will have the dimension object (Selected Child Dimension Objects).
1278 p_time_stamp: Last update of dimension object information changed by the user.
1279 It is mandatory in order of checking if the dimension object has
1280 been updated by other user.
1281 -------------------------------------------------------------------------------------------*/
1282 Procedure LOCK_UPDATE_RELATIONSHIPS(
1283 p_dim_object_id IN number
1284 ,p_selected_parends IN t_numberTable
1285 ,p_selected_childs IN t_numberTable
1286 ,p_time_stamp IN varchar2
1287 ,x_return_status OUT NOCOPY varchar2
1288 ,x_msg_count OUT NOCOPY number
1289 ,x_msg_data OUT NOCOPY varchar2
1290 ) is
1291 Begin
1292
1293 SAVEPOINT BSCLockUpdDimRelsPUB;
1294
1295
1296 BSC_BIS_LOCKS_PVT.LOCK_UPDATE_RELATIONSHIPS(
1297 p_dim_object_id
1298 ,p_selected_parends
1299 ,p_selected_childs
1300 ,p_time_stamp
1301 ,x_return_status
1302 ,x_msg_count
1303 ,x_msg_data
1304 ) ;
1305 EXCEPTION
1306 WHEN FND_API.G_EXC_ERROR THEN
1307 ROLLBACK TO BSCLockUpdDimRelsPUB;
1308 x_return_status := FND_API.G_RET_STS_ERROR;
1309 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1310 ,p_data => x_msg_data);
1311 raise;
1312 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1313 ROLLBACK TO BSCLockUpdDimRelsPUB;
1314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1315 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1316 ,p_data => x_msg_data);
1317 raise;
1318 WHEN OTHERS THEN
1319 ROLLBACK TO BSCLockUpdDimRelsPUB;
1320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1321 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1322 ,p_data => x_msg_data);
1323 raise;
1324
1325 end LOCK_UPDATE_RELATIONSHIPS;
1326 /*------------------------------------------------------------------------------------------
1327 Procedure LOCK_ASSIGN_ DIM_SET
1328 Use this procedure to lock necessary object when a Dimension Set need to be assign
1329 to a specific Analysis Option
1330 <parameters>
1331 p_kpi_Id : Indicator Id
1332 p_dim_set_id : Dimension Set Id
1333 p_time_stamp : Time stamp.
1334
1335 Note: By Now this parmeter will used to make the lock.
1336 Future version will used other parameters
1337
1338 -------------------------------------------------------------------------------------------*/
1339 Procedure LOCK_ASSIGN_DIM_SET (
1340 p_kpi_Id IN number
1341 ,p_option_group0 IN number
1342 ,p_option_group1 IN number
1343 ,p_option_group2 IN number
1344 ,p_serie_id IN number
1345 ,p_dim_set_id IN number
1346 ,p_time_stamp IN varchar2
1347 ,x_return_status OUT NOCOPY varchar2
1348 ,x_msg_count OUT NOCOPY number
1349 ,x_msg_data OUT NOCOPY varchar2
1350 ) is
1351
1352 Begin
1353
1354 SAVEPOINT BSCLockAsgnDimSetPUB;
1355
1356
1357 BSC_BIS_LOCKS_PVT.LOCK_ASSIGN_DIM_SET (
1358 p_kpi_Id
1359 ,p_option_group0
1360 ,p_option_group1
1361 ,p_option_group2
1362 ,p_serie_id
1363 ,p_dim_set_id
1364 ,p_time_stamp
1365 ,x_return_status
1366 ,x_msg_count
1367 ,x_msg_data
1368 ) ;
1369 EXCEPTION
1370 WHEN FND_API.G_EXC_ERROR THEN
1371 ROLLBACK TO BSCLockAsgnDimSetPUB;
1372 x_return_status := FND_API.G_RET_STS_ERROR;
1373 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1374 ,p_data => x_msg_data);
1375 raise;
1376 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1377 ROLLBACK TO BSCLockAsgnDimSetPUB;
1378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1379 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1380 ,p_data => x_msg_data);
1381 raise;
1382 WHEN OTHERS THEN
1383 ROLLBACK TO BSCLockAsgnDimSetPUB;
1384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1385 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1386 ,p_data => x_msg_data);
1387 raise;
1388 end LOCK_ASSIGN_DIM_SET;
1389
1390 /***********************************************************************
1391 Name :- LOCK_ASSIGN_DIM_SET
1392 Description :- This procedure will lock the Custom View for the tab.
1393 Input :- p_tab_id
1394 p_tab_view_id
1395 p_time_stamp --> corresponds to the time stamp from BSC_TAB_VIEWS_B
1396 Creator :- ashankar 05-NOV-2003
1397 /***********************************************************************/
1398
1399 PROCEDURE LOCK_TAB_VIEW_ID
1400 (
1401 p_tab_id IN NUMBER
1402 ,p_tab_view_id IN NUMBER
1403 ,p_time_stamp IN VARCHAR2 := NULL
1404 ,x_return_status OUT NOCOPY VARCHAR2
1405 ,x_msg_count OUT NOCOPY NUMBER
1406 ,x_msg_data OUT NOCOPY VARCHAR2
1407 )IS
1408 BEGIN
1409
1410 BSC_BIS_LOCKS_PVT.LOCK_TAB_VIEW_ID
1411 (
1412 p_tab_id => p_tab_id
1413 ,p_tab_view_id => p_tab_view_id
1414 ,p_time_stamp => p_time_stamp
1415 ,x_return_status => x_return_status
1416 ,x_msg_count => x_msg_count
1417 ,x_msg_data => x_msg_data
1418
1419 );
1420
1421 EXCEPTION
1422 WHEN FND_API.G_EXC_ERROR THEN
1423 x_return_status := FND_API.G_RET_STS_ERROR;
1424 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1425 ,p_data => x_msg_data);
1426 RAISE;
1427 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1429 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1430 ,p_data => x_msg_data);
1431 RAISE;
1432 WHEN OTHERS THEN
1433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1434 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1435 ,p_data => x_msg_data);
1436 RAISE;
1437
1438 END LOCK_TAB_VIEW_ID;
1439
1440 /***********************************************************************
1441 Name :- LOCK_TAB
1442 Description :- This procedure will lock the tab
1443 Input :- p_tab_id
1444 p_time_stamp --> corresponds to the time stamp from BSC_TABS_B
1445 Creator :- ashankar 10-NOV-2003
1446 /***********************************************************************/
1447
1448 PROCEDURE LOCK_TAB
1449 (
1450 p_tab_id IN NUMBER
1451 ,p_time_stamp IN VARCHAR2 := NULL
1452 ,x_return_status OUT NOCOPY VARCHAR2
1453 ,x_msg_count OUT NOCOPY NUMBER
1454 ,x_msg_data OUT NOCOPY VARCHAR2
1455 )IS
1456 BEGIN
1457 FND_MSG_PUB.Initialize;
1458 x_return_status := FND_API.G_RET_STS_SUCCESS;
1459
1460 BSC_BIS_LOCKS_PVT.LOCK_TAB
1461 (
1462 p_tab_id => p_tab_id
1463 ,p_time_stamp => p_time_stamp
1464 ,x_return_status => x_return_status
1465 ,x_msg_count => x_msg_count
1466 ,x_msg_data => x_msg_data
1467
1468 );
1469
1470 EXCEPTION
1471 WHEN FND_API.G_EXC_ERROR THEN
1472 x_return_status := FND_API.G_RET_STS_ERROR;
1473 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1474 ,p_data => x_msg_data);
1475 RAISE;
1476 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1478 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1479 ,p_data => x_msg_data);
1480 RAISE;
1481 WHEN OTHERS THEN
1482 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1483 FND_MSG_PUB.Count_And_Get(p_encoded => 'F' ,p_count => x_msg_count
1484 ,p_data => x_msg_data);
1485 RAISE;
1486 END LOCK_TAB;
1487
1488 PROCEDURE raise_lock_error (
1489 p_Program_id IN NUMBER
1490 ,p_User_Name IN VARCHAR2
1491 ,p_Machine IN VARCHAR2
1492 ,p_Terminal IN VARCHAR2
1493 ) IS
1494 TYPE t_array_varchar2 IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
1495 h_modules t_array_varchar2;
1496 BEGIN
1497 FND_MSG_PUB.Initialize;
1498 h_modules(-100) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'BSC_LOADER'); -- Loader UI
1499 h_modules(-101) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'BSC_LOADER'); -- Loader concurrent program
1500 h_modules(-200) := bsc_apps.get_lookup_value('BSC_UI_COMMON', 'METADATA_OPTIMIZER');
1501 h_modules(-201) := bsc_apps.get_lookup_value('BSC_UI_COMMON', 'METADATA_OPTIMIZER'); --Generate documentation
1502 h_modules(-700) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'UPGRADE');
1503 h_modules(-800) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'SYSTEM_MIGRATION');
1504 h_modules(-802) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'SYSTEM_MIGRATION');
1505
1506 FND_MESSAGE.SET_NAME('BSC','BSC_SEC_LOCKED_SYSTEM');
1507 FND_MESSAGE.SET_TOKEN('COMPONENT',h_modules(p_Program_id) , TRUE);
1508 FND_MESSAGE.SET_TOKEN('USERNAME' ,p_User_Name , TRUE);
1509 FND_MESSAGE.SET_TOKEN('MACHINE' ,p_Machine , TRUE);
1510 FND_MESSAGE.SET_TOKEN('TERMINAL' ,p_Terminal, TRUE);
1511 FND_MSG_PUB.ADD;
1512 --DBMS_OUTPUT.PUT_LINE('if loop:- ' ||cd.program_id);
1513 RAISE FND_API.G_EXC_ERROR;
1514 END;
1515
1516 /***********************************************************************
1517 Name :- Lock_Designer_Session_AT
1518 Description :- This procecure will check lock against conflicting moudles and return
1519 error if one finds. If doesnt find any it will lock for designer
1520 Input :- p_Entity_Name -> Name of object ie dimension,dimension object,measure,dim obj relation
1521 p_Entity_Type -> source type ie PMF,BSC
1522 p_Action_Type -> action on object ie create,update,delete
1523 Creator :- KRISHNA 19-OCT-2004
1524 /***********************************************************************/
1525
1526 PROCEDURE Lock_Designer_Session_AT (
1527
1528 p_Entity_Name IN VARCHAR2
1529 ,p_Entity_Type IN VARCHAR2
1530 ,p_Action_Type IN VARCHAR2
1531 ,x_Return_Status OUT NOCOPY VARCHAR2
1532 ,x_Msg_Count OUT NOCOPY NUMBER
1533 ,x_Msg_Data OUT NOCOPY VARCHAR2
1534
1535 )IS
1536 PRAGMA AUTONOMOUS_TRANSACTION;
1537 CURSOR c_conflict_session IS
1538 SELECT c.program_id, u.user_name, s.machine, s.terminal
1539 FROM bsc_current_sessions c, v$session s, bsc_apps_users_v u
1540 WHERE c.session_id = s.audsid
1541 AND c.program_id IN (-100, -101, -200, -201, -700, -800, -802)
1542 AND c.session_id <> USERENV('SESSIONID')
1543 AND c.user_id = u.user_id (+);
1544
1545 CURSOR c_sessions IS
1546 SELECT session_id
1547 FROM bsc_current_sessions
1548 WHERE program_id IN (-100,-101,-200,-201,-202,-700,-800, -802);
1549
1550 l_session_ids VARCHAR2(8000);
1551 l_sql VARCHAR2(8000);
1552
1553 BEGIN
1554 FND_MSG_PUB.Initialize;
1555 x_return_status := FND_API.G_RET_STS_SUCCESS;
1556 -- First we need to delete the orphan sessions
1557 --Delete all orphan the sessions
1558 DELETE BSC_CURRENT_SESSIONS
1559 WHERE SESSION_ID NOT IN
1560 (SELECT VS.AUDSID
1561 FROM V$SESSION VS);
1562
1563 --Delete all the session not being reused by FND
1564 DELETE BSC_CURRENT_SESSIONS
1565 WHERE ICX_SESSION_ID IN (
1566 SELECT SESSION_ID
1567 FROM ICX_SESSIONS
1568 WHERE (FND_SESSION_MANAGEMENT.CHECK_SESSION(SESSION_ID,NULL,NULL,'N') <> 'VALID'));
1569
1570 --Delete all the Metadata/Loader sessions, which have their concurrent programs in invalid or hang status
1571 /**************************************
1572 Changed for the POSCO bug 4955767
1573 The logic is the following.
1574 1.First we will check if there are any sessions
1575 in BSC_CURRENT_SESSIONS for those programs for which
1576 concurrent programs are run.
1577 This include loader,Metadata optimizer,system migration and upgrade
1578 2.If there are sessions corresponding to above programs then only
1579 it will enter into the curose loop otherwise it will not.
1580 This will improve the performance.
1581 3.Even if it finds the records then it will delete the records only
1582 for the above programs instead of taking all the programs for which
1583 concurrent programs are not run.
1584 This will again imporve the performance
1585 /**************************************/
1586 FOR cd IN c_sessions LOOP
1587 IF(l_session_ids IS NULL ) THEN
1588 l_session_ids :=cd.session_id;
1589 ELSE
1590 l_session_ids := l_session_ids ||','||cd.session_id;
1591 END IF;
1592 END LOOP;
1593
1594 IF(l_session_ids IS NOT NULL) THEN
1595 l_sql := ' DELETE bsc_current_sessions'||
1596 ' WHERE session_id IN '||
1597 ' ( '||
1598 ' SELECT oracle_session_id '||
1599 ' FROM fnd_concurrent_requests '||
1600 ' WHERE program_application_id =271 '||
1601 ' AND oracle_session_id IN ('||l_session_ids ||' )'||
1602 ' AND phase_code=''C'')';
1603 EXECUTE IMMEDIATE l_sql ;
1604 END IF;
1605
1606 --Delete all the Killed Sessions
1607 DELETE BSC_CURRENT_SESSIONS
1608 WHERE SESSION_ID IN (
1609 SELECT VS.AUDSID
1610 FROM V$SESSION VS
1611 WHERE VS.STATUS = 'KILLED');
1612 --INSERT INTO test_debug_log VALUES ('0','in autonomus',SYSDATE);
1613 COMMIT;
1614 FOR cd IN c_conflict_session LOOP
1615 --DBMS_OUTPUT.PUT_LINE('find program id :- ' ||cd.program_id);
1616 IF(cd.program_id IN (-700,-800, -802) ) THEN
1617 raise_lock_error
1618 ( p_Program_id => cd.program_id
1619 , p_User_Name => cd.user_name
1620 , p_Machine => cd.machine
1621 , p_Terminal => cd.terminal
1622 );
1623
1624 ELSIF (cd.program_id IN (-100, -101, -200, -201) ) THEN
1625 IF(p_Entity_Name = bsc_utility.c_CALENDAR AND p_Action_Type = bsc_utility.c_UPDATE) THEN
1626 raise_lock_error
1627 ( p_Program_id => cd.program_id
1628 , p_User_Name => cd.user_name
1629 , p_Machine => cd.machine
1630 , p_Terminal => cd.terminal
1631 );
1632 ELSIF (p_Entity_Type = bsc_utility.c_BSC) THEN
1633 IF((p_Entity_Name NOT IN (bsc_utility.c_DIMENSION,bsc_utility.c_MEASURE)) OR (p_Action_Type <> bsc_utility.c_CREATE)) THEN
1634 raise_lock_error
1635 ( p_Program_id => cd.program_id
1636 , p_User_Name => cd.user_name
1637 , p_Machine => cd.machine
1638 , p_Terminal => cd.terminal
1639 );
1640 END IF;
1641
1642 END IF;
1643 END IF;
1644
1645 END LOOP;
1646
1647 INSERT INTO BSC_CURRENT_SESSIONS (
1648 SESSION_ID,
1649 PROGRAM_ID,
1650 CREATED_BY,
1651 CREATION_DATE,
1652 LAST_UPDATED_BY,
1653 LAST_UPDATE_DATE,
1654 LAST_UPDATE_LOGIN,
1655 USER_ID,
1656 ICX_SESSION_ID
1657 ) VALUES
1658 (
1659 USERENV('SESSIONID'),
1660 -400,
1661 FND_GLOBAL.USER_ID,
1662 SYSDATE,
1663 FND_GLOBAL.USER_ID,
1664 SYSDATE,
1665 FND_GLOBAL.USER_ID,
1666 FND_GLOBAL.USER_ID,
1667 USERENV('SESSIONID')
1668 );
1669 --DBMS_OUTPUT.PUT_LINE('the session id is :- '||USERENV('SESSIONID'));
1670 COMMIT;
1671 EXCEPTION
1672 WHEN FND_API.G_EXC_ERROR THEN
1673 IF (x_msg_data IS NULL) THEN
1674 FND_MSG_PUB.Count_And_Get
1675 ( p_encoded => FND_API.G_FALSE
1676 , p_count => x_msg_count
1677 , p_data => x_msg_data
1678 );
1679 END IF;
1680 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1681 x_return_status := FND_API.G_RET_STS_ERROR;
1682 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1683 IF (x_msg_data IS NULL) THEN
1684 FND_MSG_PUB.Count_And_Get
1685 ( p_encoded => FND_API.G_FALSE
1686 , p_count => x_msg_count
1687 , p_data => x_msg_data
1688 );
1689 END IF;
1690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1691 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1692 WHEN OTHERS THEN
1693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1694 IF (x_msg_data IS NOT NULL) THEN
1695 x_msg_data := x_msg_data||' -> BSC_BIS_LOCKS_PUB.Lock_Designer_Session_AT ';
1696 ELSE
1697 x_msg_data := SQLERRM||' at BSC_BIS_LOCKS_PUB.Lock_Designer_Session_AT ';
1698 END IF;
1699 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1700
1701 END Lock_Designer_Session_AT;
1702 /***********************************************************************
1703 Name :- Lock_Designer_Session_AT
1704 Description :- This procedure unlock the lock created by Lock_Designer_Session_AT
1705 This should be called at the end of wrapper API and in all exception blocks
1706 Creator :- krishna 19-OCT-2004
1707 /***********************************************************************/
1708
1709 PROCEDURE Unlock_Designer_Session_AT
1710 IS
1711 PRAGMA AUTONOMOUS_TRANSACTION;
1712 BEGIN
1713 DELETE FROM bsc_current_sessions
1714 WHERE session_id = USERENV('SESSIONID')
1715 AND program_id = -400;
1716
1717 COMMIT;
1718 EXCEPTION
1719 WHEN OTHERS THEN --Need to consume the exception here
1720 NULL;
1721 END Unlock_Designer_Session_AT;
1722
1723 /*------------------------------------------------------------------------------------------
1724 *
1725 * Calendar and Periodicities locking public APIs
1726 *
1727 -------------------------------------------------------------------------------------------*/
1728
1729 PROCEDURE Lock_Calendar_And_Periods (
1730 p_Calendar_Id IN NUMBER
1731 , p_Time_Stamp IN VARCHAR2
1732 , x_Return_Status OUT NOCOPY VARCHAR2
1733 , x_Msg_Count OUT NOCOPY NUMBER
1734 , x_Msg_Data OUT NOCOPY VARCHAR2
1735 ) IS
1736 CURSOR c_Periodicity_Ids IS
1737 SELECT PERIODICITY_ID
1738 FROM BSC_SYS_PERIODICITIES
1739 WHERE CALENDAR_ID = p_Calendar_Id;
1740 BEGIN
1741 SAVEPOINT LockUpdateCalendarPUB;
1742 FND_MSG_PUB.Initialize;
1743
1744 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1745
1746 BSC_BIS_LOCKS_PVT.Lock_Calendar (
1747 p_Calendar_Id => p_Calendar_Id
1748 , p_Time_Stamp => p_Time_Stamp
1749 , x_Return_Status => x_Return_Status
1750 , x_Msg_Count => x_Msg_Count
1751 , x_Msg_Data => x_Msg_Data
1752 );
1753 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1754 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1755 END IF;
1756
1757 FOR cPerIds IN c_Periodicity_Ids LOOP
1758 BSC_BIS_LOCKS_PVT.Lock_Periodicity (
1759 p_Periodicity_Id => cPerIds.PERIODICITY_ID
1760 , p_Time_Stamp => NULL
1761 , x_Return_Status => x_Return_Status
1762 , x_Msg_Count => x_Msg_Count
1763 , x_Msg_Data => x_Msg_Data
1764 );
1765 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1766 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1767 END IF;
1768 END LOOP;
1769
1770 EXCEPTION
1771 WHEN FND_API.G_EXC_ERROR THEN
1772 ROLLBACK TO LockUpdateCalendarPUB;
1773 IF (x_msg_data IS NULL) THEN
1774 FND_MSG_PUB.Count_And_Get
1775 ( p_encoded => FND_API.G_FALSE
1776 , p_count => x_msg_count
1777 , p_data => x_msg_data
1778 );
1779 END IF;
1780 x_Return_Status := FND_API.G_RET_STS_ERROR;
1781 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1782 ROLLBACK TO LockUpdateCalendarPUB;
1783 IF (x_msg_data IS NULL) THEN
1784 FND_MSG_PUB.Count_And_Get
1785 ( p_encoded => FND_API.G_FALSE
1786 , p_count => x_msg_count
1787 , p_data => x_msg_data
1788 );
1789 END IF;
1790 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1791 WHEN NO_DATA_FOUND THEN
1792 ROLLBACK TO LockUpdateCalendarPUB;
1793 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1794 IF (x_msg_data IS NOT NULL) THEN
1795 x_msg_data := x_msg_data||' -> BSC_BIS_LOCKS_PUB.Lock_Calendar_And_Periods ';
1796 ELSE
1797 x_msg_data := SQLERRM||' at BSC_BIS_LOCKS_PUB.Lock_Calendar_And_Periods ';
1798 END IF;
1799 WHEN OTHERS THEN
1800 ROLLBACK TO LockUpdateCalendarPUB;
1801 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1802 IF (x_msg_data IS NOT NULL) THEN
1803 x_msg_data := x_msg_data||' -> BSC_BIS_LOCKS_PUB.Lock_Calendar_And_Periods ';
1804 ELSE
1805 x_msg_data := SQLERRM||' at BSC_BIS_LOCKS_PUB.Lock_Calendar_And_Periods ';
1806 END IF;
1807 END Lock_Calendar_And_Periods;
1808
1809
1810 PROCEDURE Lock_Calendar (
1811 p_Calendar_Id IN NUMBER
1812 , p_Time_Stamp IN VARCHAR2
1813 , x_Return_Status OUT NOCOPY VARCHAR2
1814 , x_Msg_Count OUT NOCOPY NUMBER
1815 , x_Msg_Data OUT NOCOPY VARCHAR2
1816 ) IS
1817 BEGIN
1818 SAVEPOINT LockCalendarPUB;
1819 FND_MSG_PUB.Initialize;
1820
1821 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1822
1823 BSC_BIS_LOCKS_PVT.Lock_Calendar (
1824 p_Calendar_Id => p_Calendar_Id
1825 , p_Time_Stamp => p_Time_Stamp
1826 , x_Return_Status => x_Return_Status
1827 , x_Msg_Count => x_Msg_Count
1828 , x_Msg_Data => x_Msg_Data
1829 );
1830 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1832 END IF;
1833
1834 EXCEPTION
1835 WHEN FND_API.G_EXC_ERROR THEN
1836 ROLLBACK TO LockCalendarPUB;
1837 IF (x_msg_data IS NULL) THEN
1838 FND_MSG_PUB.Count_And_Get
1839 ( p_encoded => FND_API.G_FALSE
1840 , p_count => x_msg_count
1841 , p_data => x_msg_data
1842 );
1843 END IF;
1844 x_Return_Status := FND_API.G_RET_STS_ERROR;
1845 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1846 ROLLBACK TO LockCalendarPUB;
1847 IF (x_msg_data IS NULL) THEN
1848 FND_MSG_PUB.Count_And_Get
1849 ( p_encoded => FND_API.G_FALSE
1850 , p_count => x_msg_count
1851 , p_data => x_msg_data
1852 );
1853 END IF;
1854 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1855 WHEN NO_DATA_FOUND THEN
1856 ROLLBACK TO LockCalendarPUB;
1857 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1858 IF (x_msg_data IS NOT NULL) THEN
1859 x_msg_data := x_msg_data||' -> BSC_BIS_LOCKS_PUB.Lock_Calendar ';
1860 ELSE
1861 x_msg_data := SQLERRM||' at BSC_BIS_LOCKS_PUB.Lock_Calendar ';
1862 END IF;
1863 WHEN OTHERS THEN
1864 ROLLBACK TO LockCalendarPUB;
1865 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1866 IF (x_msg_data IS NOT NULL) THEN
1867 x_msg_data := x_msg_data||' -> BSC_BIS_LOCKS_PUB.Lock_Calendar ';
1868 ELSE
1869 x_msg_data := SQLERRM||' at BSC_BIS_LOCKS_PUB.Lock_Calendar ';
1870 END IF;
1871 END Lock_Calendar;
1872
1873
1874 PROCEDURE Lock_Periodicity (
1875 p_Periodicity_Id IN NUMBER
1876 , p_Time_Stamp IN VARCHAR2
1877 , x_Return_Status OUT NOCOPY VARCHAR2
1878 , x_Msg_Count OUT NOCOPY NUMBER
1879 , x_Msg_Data OUT NOCOPY VARCHAR2
1880 ) IS
1881 BEGIN
1882 SAVEPOINT LockPeriodicityPUB;
1883 FND_MSG_PUB.Initialize;
1884
1885 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1886
1887 BSC_BIS_LOCKS_PVT.Lock_Periodicity (
1888 p_Periodicity_Id => p_Periodicity_Id
1889 , p_Time_Stamp => p_Time_Stamp
1890 , x_Return_Status => x_Return_Status
1891 , x_Msg_Count => x_Msg_Count
1892 , x_Msg_Data => x_Msg_Data
1893 );
1894 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1895 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1896 END IF;
1897 EXCEPTION
1898 WHEN FND_API.G_EXC_ERROR THEN
1899 ROLLBACK TO LockPeriodicityPUB;
1900 IF (x_msg_data IS NULL) THEN
1901 FND_MSG_PUB.Count_And_Get
1902 ( p_encoded => FND_API.G_FALSE
1903 , p_count => x_msg_count
1904 , p_data => x_msg_data
1905 );
1906 END IF;
1907 x_Return_Status := FND_API.G_RET_STS_ERROR;
1908 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1909 ROLLBACK TO LockPeriodicityPUB;
1910 IF (x_msg_data IS NULL) THEN
1911 FND_MSG_PUB.Count_And_Get
1912 ( p_encoded => FND_API.G_FALSE
1913 , p_count => x_msg_count
1914 , p_data => x_msg_data
1915 );
1916 END IF;
1917 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1918 WHEN NO_DATA_FOUND THEN
1919 ROLLBACK TO LockPeriodicityPUB;
1920 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1921 IF (x_msg_data IS NOT NULL) THEN
1922 x_msg_data := x_msg_data||' -> BSC_BIS_LOCKS_PUB.Lock_Periodicity ';
1923 ELSE
1924 x_msg_data := SQLERRM||' at BSC_BIS_LOCKS_PUB.Lock_Periodicity ';
1925 END IF;
1926 WHEN OTHERS THEN
1927 ROLLBACK TO LockPeriodicityPUB;
1928 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1929 IF (x_msg_data IS NOT NULL) THEN
1930 x_msg_data := x_msg_data||' -> BSC_BIS_LOCKS_PUB.Lock_Periodicity ';
1931 ELSE
1932 x_msg_data := SQLERRM||' at BSC_BIS_LOCKS_PUB.Lock_Periodicity ';
1933 END IF;
1934 END Lock_Periodicity;
1935
1936 End BSC_BIS_LOCKS_PUB;