1 PACKAGE BODY bsc_security AS
2 /* $Header: BSCSSECB.pls 120.3 2006/01/24 16:10:11 calaw noship $ */
3
4 -- Global Variables
5 --
6 -- User_Info_Type
7 --
8 Type User_Info_Rec_Type Is Record (
9 sid NUMBER,
10 user_id NUMBER(15),
11 user_name VARCHAR2(100),
12 user_pwd VARCHAR2(100),
13 user_type NUMBER(1),
14 obsc_un VARCHAR2(100),
15 obsc_pwd VARCHAR2(100),
16 ubsc_un VARCHAR2(100),
17 ubsc_pwd VARCHAR2(100)
18 );
19
20 c_src_db_link CONSTANT VARCHAR2(15) := 'BSC_SRC_DBLINK';
21
22
23 -- Public Functions and Procedures
24 --
25 -- Return the Bsc_user Table name, It is to keep the backward compatibility
26 -- It is called from OBSCPUB scheme, The tables are synonyms.
27 FUNCTION user_table_name RETURN VARCHAR IS
28 h_table_name varchar2(30);
29 CURSOR c_tables IS
30 SELECT OBJECT_NAME
31 FROM USER_OBJECTS
32 WHERE OBJECT_NAME='BSC_USER';
33 BEGIN
34 OPEN c_tables;
35 FETCH c_tables INTO h_table_name;
36 -- TEMPORAL
37 IF c_tables%FOUND THEN
38 h_table_name :='BSC_USER';
39 ELSE
40 h_table_name :='BSC_USERS';
41 END IF;
42 CLOSE c_tables;
43 RETURN h_table_name;
44 END user_table_name;
45
46
47 --
48 -- Name
49 -- get_user_info
50 -- Purpose
51 -- Gets user_pwd, user_type and scheme un/pwd for OBSC and UBSC users.
52 --
53 -- Arguments
54 -- x_user_name
55 -- x_debug_flag
56 -- x_calling_fn
57 --
58 -- Overloaded procedure for VB code, since VB cannot read from PL/SQL
59 -- parameters.
60
61 PROCEDURE get_user_info(
62 x_sid IN VARCHAR2,
63 x_user_name IN VARCHAR2,
64 x_debug_flag IN VARCHAR2 := 'NO',
65 x_calling_fn IN VARCHAR2) IS
66
67 h_current_fn VARCHAR2(128) := 'bsc_security.get_user_info';
68
69 user_info User_Info_Rec_Type;
70 h_message VARCHAR2(200);
71 h_st_dt_chk NUMBER(10);
72 h_end_dt_chk NUMBER(10);
73
74 h_undefined_user EXCEPTION;
75 h_login_expired EXCEPTION;
76 h_undefined_obsc_user EXCEPTION;
77 h_undefined_ubsc_user EXCEPTION;
78
79 l_sql_stmt VARCHAR2(1024);
80 l_curr INTEGER;
81 l_val INTEGER;
82 BEGIN
83
84 -- Enable dbms output
85
86 IF (x_debug_flag = 'YES') THEN
87 bsc_utility.enable_debug;
88 END IF;
89
90 user_info.sid := TO_NUMBER(x_sid);
91 user_info.user_name := UPPER(x_user_name);
92
93 --
94 -- Verify if the login user_name is correct
95 --
96
97 BEGIN
98 l_sql_stmt :='SELECT encrypted_user_password,';
99 l_sql_stmt :=l_sql_stmt||'user_id, ';
100 l_sql_stmt :=l_sql_stmt||'user_type, ';
101 l_sql_stmt :=l_sql_stmt||' sysdate - start_date,';
102 l_sql_stmt :=l_sql_stmt||' sysdate - end_date ';
103 l_sql_stmt :=l_sql_stmt||' FROM '||user_table_name;
104 l_sql_stmt :=l_sql_stmt||' WHERE ';
105 l_sql_stmt :=l_sql_stmt||' user_name = :1'; -- fix for literals bug#3075851
106 l_sql_stmt :=l_sql_stmt||' AND user_type <> :2'; -- "
107
108
109 l_curr := dbms_sql.open_cursor;
110 dbms_sql.parse(l_curr, l_sql_stmt, dbms_sql.native);
111 dbms_sql.define_column(l_curr,1,user_info.user_pwd,100);
112 dbms_sql.define_column(l_curr,2,user_info.user_id);
113 dbms_sql.define_column(l_curr,3,user_info.user_type);
114 dbms_sql.define_column(l_curr,4,h_st_dt_chk);
115 dbms_sql.define_column(l_curr,5,h_end_dt_chk);
116 dbms_sql.bind_variable(l_curr,':1',user_info.user_name); -- fix for literals bug#3075851
117 dbms_sql.bind_variable(l_curr,':2',BSC_SECURITY.DB_USER_TYPE); -- "
118
119 l_val := dbms_sql.execute(l_curr);
120 IF dbms_sql.fetch_rows(l_curr)> 0 THEN
121 dbms_sql.column_value(l_curr,1,user_info.user_pwd);
122 dbms_sql.column_value(l_curr,2,user_info.user_id);
123 dbms_sql.column_value(l_curr,3,user_info.user_type);
124 dbms_sql.column_value(l_curr,4,h_st_dt_chk);
125 dbms_sql.column_value(l_curr,5,h_end_dt_chk);
126 END IF;
127 dbms_sql.close_cursor(l_curr);
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 h_message := bsc_apps.get_message('BSC_INV_USER_NAME');
131 RAISE h_undefined_user;
132 END;
133
134 IF (h_st_dt_chk < 0 OR h_end_dt_chk > 0) THEN
135 h_message := bsc_apps.get_message('BSC_LOGIN_EXPIRED');
136 RAISE h_login_expired;
137
138 END IF;
139
140 -- If valid user_name, get required info for OBSC application connection.
141 -- Get OBSC username and encrypted password
142
143 BEGIN
144
145 l_sql_stmt :='SELECT ';
146 l_sql_stmt :=l_sql_stmt||'user_name,';
147 l_sql_stmt :=l_sql_stmt||'encrypted_user_password';
148 l_sql_stmt :=l_sql_stmt||' FROM '||user_table_name;
149 l_sql_stmt :=l_sql_stmt||' WHERE ';
150 l_sql_stmt :=l_sql_stmt||' user_name LIKE ''OBSC%''';
151 l_sql_stmt :=l_sql_stmt||' AND user_type = :1'; --||DB_USER_TYPE;
152 l_curr := dbms_sql.open_cursor;
153 dbms_sql.parse(l_curr, l_sql_stmt, dbms_sql.native);
154 dbms_sql.define_column(l_curr,1,user_info.obsc_un,100);
155 dbms_sql.define_column(l_curr,2,user_info.obsc_pwd,100);
156
157 dbms_sql.bind_variable(l_curr,':1',DB_USER_TYPE); -- literals fix
158 l_val := dbms_sql.execute(l_curr);
159
160 IF dbms_sql.fetch_rows(l_curr)> 0 THEN
161 dbms_sql.column_value(l_curr,1,user_info.obsc_un);
162 dbms_sql.column_value(l_curr,2,user_info.obsc_pwd);
163 ELSE
164 user_info.obsc_un :=' ';
165 user_info.obsc_pwd :=' ';
166 END IF;
167 dbms_sql.close_cursor(l_curr);
168 EXCEPTION
169 WHEN NO_DATA_FOUND THEN
170 h_message := bsc_apps.get_message('BSC_INV_OBSC');
171 RAISE h_undefined_obsc_user;
172 END;
173
174 -- Get UBSC username and encrypted password
175
176 BEGIN
177
178 l_sql_stmt := 'SELECT ';
179 l_sql_stmt :=l_sql_stmt||'user_name,';
180 l_sql_stmt :=l_sql_stmt||'encrypted_user_password';
181 l_sql_stmt :=l_sql_stmt||' FROM '||user_table_name;
182 l_sql_stmt :=l_sql_stmt||' WHERE ';
183 l_sql_stmt :=l_sql_stmt||' user_name LIKE ''UBSC%''';
184 l_sql_stmt :=l_sql_stmt||' AND user_type = :1'; --||DB_USER_TYPE;
185
186 l_curr := dbms_sql.open_cursor;
187 dbms_sql.parse(l_curr, l_sql_stmt, dbms_sql.native);
188 dbms_sql.define_column(l_curr,1,user_info.ubsc_un,100);
189 dbms_sql.define_column(l_curr,2,user_info.ubsc_pwd,100);
190
191 dbms_sql.bind_variable(l_curr,':1',DB_USER_TYPE); -- literals fix
192 l_val := dbms_sql.execute(l_curr);
193 IF dbms_sql.fetch_rows(l_curr)> 0 THEN
194 dbms_sql.column_value(l_curr,1,user_info.ubsc_un);
195 dbms_sql.column_value(l_curr,2,user_info.ubsc_pwd);
196 ELSE
197 user_info.ubsc_un :=' ';
198 user_info.ubsc_pwd :=' ';
199 END IF;
200 dbms_sql.close_cursor(l_curr);
201 EXCEPTION
202 WHEN NO_DATA_FOUND THEN
203 h_message := bsc_apps.get_message('BSC_INV_UBSC');
204 RAISE h_undefined_ubsc_user;
205 END;
206 INSERT INTO bsc_user_info(
207 SID,
208 USER_ID,
209 USER_NAME,
210 USER_PWD,
211 USER_TYPE,
212 OBSC_UN,
213 OBSC_PWD,
214 UBSC_UN,
215 UBSC_PWD
216 )
217 (
218 SELECT
219 user_info.sid,
220 user_info.user_id,
221 user_info.user_name,
222 user_info.user_pwd,
223 user_info.user_type,
224 user_info.obsc_un,
225 user_info.obsc_pwd,
226 user_info.ubsc_un,
227 user_info.ubsc_pwd
228 FROM
229 dual
230 );
231 EXCEPTION
232 WHEN h_undefined_user THEN
233 bsc_message.add('ERROR-', x_calling_fn, APP_ERR_MSG, 'I');
234 bsc_message.add(h_message, h_current_fn, APP_ERR_MSG, 'I');
235
236 WHEN h_login_expired THEN
237 bsc_message.add('ERROR-', x_calling_fn, APP_ERR_MSG, 'I');
238 bsc_message.add(h_message, h_current_fn, APP_ERR_MSG, 'I');
239
240 WHEN h_undefined_obsc_user THEN
241 bsc_message.add('ERROR-', x_calling_fn, APP_ERR_MSG, 'I');
242 bsc_message.add(h_message, h_current_fn, APP_ERR_MSG, 'I');
243
244 WHEN h_undefined_ubsc_user THEN
245 bsc_message.add('ERROR-', x_calling_fn, APP_ERR_MSG, 'I');
246 bsc_message.add(h_message, h_current_fn, APP_ERR_MSG, 'I');
247
248 WHEN OTHERS THEN
249 bsc_message.add('ERROR-', x_calling_fn, DB_ERR_MSG, 'I');
250 bsc_message.add(SQLERRM, h_current_fn, DB_ERR_MSG, 'I');
251
252 END get_user_info;
253
254 --
255 -- Name
256 -- get_user_info
257 -- Purpose
258 -- Gets user_pwd, user_type and scheme un/pwd for OBSC and UBSC users.
259 --
260 -- Arguments
261 -- x_user_name
262 -- x_user_pwd
263 -- x_user_id
264 -- x_user_type
265 -- x_obsc_un
266 -- x_obsc_pwd
267 -- x_ubsc_un
268 -- x_ubsc_pwd
269 -- x_debug_flag
270 -- x_status
271 -- x_calling_fn
272 --
273
274 PROCEDURE get_user_info(
275 x_user_name IN VARCHAR2,
276 x_user_pwd IN OUT NOCOPY VARCHAR2,
277 x_user_id IN OUT NOCOPY NUMBER,
278 x_user_type IN OUT NOCOPY NUMBER,
279 x_obsc_un IN OUT NOCOPY VARCHAR2,
280 x_obsc_pwd IN OUT NOCOPY VARCHAR2,
281 x_ubsc_un IN OUT NOCOPY VARCHAR2,
282 x_ubsc_pwd IN OUT NOCOPY VARCHAR2,
283 x_debug_flag IN VARCHAR2 := 'NO',
284 x_status IN OUT NOCOPY BOOLEAN,
285 x_calling_fn IN VARCHAR2) IS
286
287 h_current_fn VARCHAR2(128) := 'bsc_security.get_user_info';
288
289 h_count NUMBER;
290 h_session_id NUMBER;
291 l_sql_stmt VARCHAR2(1024);
292 l_curr INTEGER;
293 l_val INTEGER;
294
295
296 BEGIN
297
298 select USERENV('SESSIONID')
299 into h_session_id
300 from dual;
301
302 get_user_info(h_session_id,
303 x_user_name,
304 x_debug_flag,
305 h_current_fn);
306
307 SELECT COUNT(*)
308 INTO h_count
309 FROM bsc_message_logs
310 WHERE
311 type IN (DB_ERR_MSG, APP_ERR_MSG)
312 AND source = h_current_fn
313 AND Last_Update_Login = h_session_id;
314
315 IF (h_count > 0) THEN
316 x_status := FALSE;
317 ELSE
318 SELECT
319 user_id,
320 user_pwd,
321 user_type,
322 obsc_un,
323 obsc_pwd,
324 ubsc_un,
325 ubsc_pwd
326 INTO
327 x_user_id,
328 x_user_pwd,
329 x_user_type,
330 x_obsc_un,
331 x_obsc_pwd,
332 x_ubsc_un,
333 x_ubsc_pwd
334 FROM
335 bsc_user_info
336 WHERE
337 sid = h_session_id;
338
339 x_status := TRUE;
340
341 END IF;
342
343 EXCEPTION
344 WHEN OTHERS THEN
345 x_status := FALSE;
346 bsc_message.add('ERROR-', x_calling_fn, DB_ERR_MSG, 'I');
347 bsc_message.add(SQLERRM, h_current_fn, DB_ERR_MSG, 'I');
348
349 END get_user_info;
350
351
352 --
353 -- Name
354 -- Check_System_Lock
355 -- Purpose
356 -- Enforce system locking for all OBSC models
357 --
358 -- Parameter:
359 -- x_program_id - program identifier, has the following value
360 -- a. Loader = -100
361 -- b. Metadata Optimizer = -200
362 -- c. Security Wizard = -300
363 -- d. KPI Designer = -400
364 -- e. BSC Builder = -500
365 -- f. iViewer or VB Viewer (in user mode) = -600
366 -- g. Migration Backend Target = -800 added amitgupt 3013894
367 -- h. Migration UI = -801 added amitgupt 3013894
368 -- i. Migration Backend Source = -802 added calaw 4648979
372 -- Other OBSC clients will the first 3 parms.
369 -- x_debug_flag - debug flag
370 -- x_user_id - Session Management, passed by OA Fwk for user name
371 -- x_icx_session_id - Session Management, passed by OA Fwk from IBuilder only
373
374 Procedure Check_System_Lock(
375 x_program_id IN Number,
376 x_debug_flag IN Varchar2 := 'NO',
377 x_user_id IN Number :=NULL,
378 x_icx_session_id IN Number :=NULL
379 ) Is
380
381 l_calling_fn Varchar2(80);
382 l_message Varchar2(2000);
383 l_lock_msg Varchar2(2000) := NULL;
384 l_obsc_count Number := 0;
385
386 BSC_Lock_Error Exception;
387
388 h_session_id NUMBER;
389 h_user_id NUMBER;
390
391 h_time_out NUMBER := 1200; -- (1200segs = 20 minutes)
392
393 CURSOR c_ksessions IS
394 Select
395 s.audsid,
396 s.sid,
397 s.serial#
398 From
399 v$session s,
400 v$session_wait w,
401 bsc_current_sessions c
402 Where
403 s.audsid = c.session_id And
404 s.sid = w.sid And
405 c.program_id = -600 And
406 w.seconds_in_wait > h_time_out;
407
408 h_ksession c_ksessions%ROWTYPE;
409
410 h_sql VARCHAR2(2000);
411 h_cursor INTEGER;
412 h_ret INTEGER;
413 h_program_id NUMBER;
414 h_username VARCHAR2(100);
415 h_machine VARCHAR2(100);
416 h_machine2 VARCHAR2(100);
417 h_terminal VARCHAR2(100);
418
419 TYPE t_array_of_varchar2 IS TABLE OF VARCHAR2(500)
420 INDEX BY BINARY_INTEGER;
421
422 h_components t_array_of_varchar2;
423
424 l_count NUMBER;
425
426 Begin
427 -- Initialize BSC/APPS global variables
428 BSC_APPS.Init_Bsc_Apps;
429
430 BSC_MESSAGE.Init(X_Debug_Flag => x_debug_flag);
431
432 l_calling_fn := 'BSC_SECURITY.CHECK_SYSTEM_LOCK';
433 l_lock_msg := bsc_apps.get_message('BSC_SEC_LOCKED_SYSTEM');
434 h_components(-100) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'BSC_LOADER'); -- Loader UI
435 h_components(-101) := h_components(-100); -- Loader concurrent program
436 h_components(-200) := bsc_apps.get_lookup_value('BSC_UI_COMMON', 'METADATA_OPTIMIZER');
437 h_components(-201) := h_components(-200); --Generate documentation
438 h_components(-202) := h_components(-200); --Rename input tables
439 h_components(-300) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'BSC_ADMINISTRATOR');
440 h_components(-400) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'KPI_DESIGNER');
441 h_components(-500) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'BSC_BUILDER');
442 h_components(-600) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'OBSC_VIEWER');
443 h_components(-700) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'UPGRADE');
444 h_components(-800) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'SYSTEM_MIGRATION'); -- Migration Backend Target
445 h_components(-801) := h_components(-800); -- Migration UI
446 h_components(-802) := h_components(-800); -- Migration Backend Source
447
448 -- Ref: bug#3482442 In corner cases this query can return more than one
449 -- row and it will fail. AUDSID is not PK. After meeting with
450 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
451 h_session_id := USERENV('SESSIONID');
452 h_user_id := BSC_APPS.fnd_global_user_id;
453
454 -- Clean BSC_MESSAGE_LOGS for the current session
455 DELETE bsc_message_logs
456 WHERE last_update_login = h_session_id;
457 commit;
458
459 BSC_SECURITY.Refresh_System_Lock(x_program_id);
460 -- Clean BSC_CURRENT_SESSIONS to leave only current sessions
461 --Delete bsc_current_sessions
462 --Where session_id Not In (Select vs.audsid From V$Session vs);
463 --commit;
464 --
465 -- Clean BSC_CURRENT_SESSIONS for ibuilder when users click "logout" button
466 -- Added for BIS Application ID (191), so that loader session is not removed.
467 -- Fix locking issue with icx sessions:
468 -- We have found that we can call the api FND_SESSION_MANAGEMENT.Check_Session,
469 -- passing the icx session id and it will return any 'VALID', 'INVALID' or 'EXPIRED'.
470 -- This function does the validations to see if the icx session is valid or not and
471 -- also if it has exprired. This is based on the values of TIME_OUT, TIME_LIMIT, GUEST,
472 -- DISABLED, etc for the icx session.
473 -- The value for TIME_LIMIT and TIME_OUT are from profiles at user level.
474 -- TIME_LIMIT is always populated with a default value of 4 (4 hours).
475 -- We need use the same logic FND is using to throw time out exception to the OA pages.
476 -- If we do something different, we would need to modify all our OA pages to implement a new
477 -- logic which has a big impact.
478 --
479 -- The icx session id is not re-used by FND, so we can take off the second condition:
480 -- or (responsibility_application_id not in (271, 191))
481 --
482 --Delete bsc_current_sessions
483 --Where icx_session_id In (
484 -- Select session_id
485 -- From icx_sessions
486 -- Where (fnd_session_management.check_session(session_id, null, null, 'N') <> 'VALID')
487 -- );
488 --commit;
489 --
490 --metadata optimizer , loader are conc requests.if the conc request is not running anymore
491 --we can remove it from bsc_current_session
495 --(select oracle_session_id from fnd_concurrent_requests f ,bsc_current_sessions b where b.session_id = f.oracle_session_id and phase_code='C');
492 --phase code of C means complete. the other phases are pending, running
493 --Delete bsc_current_sessions
494 --Where session_id in --(select nvl(oracle_session_id,-1) from fnd_concurrent_requests where phase_code='C'); --bug 3396460, optimized query
496 --
497 -- Kill IViewer Sessions that have been INACTIVE more than 20 minutes
498 -- This is implemented only in APPS mode.
499 -- Note: Only Architect can do it.
500 --
501 -- Bug#2145306: Since the IViewer sessions are returned to the pool after
502 -- user exists and another application can use the same session, we cannot
503 -- kill the session for any reason. What we can do is to delete the record
504 -- from BSC_CURRENT_SESSIONS table.
505 --
506 --IF x_program_id <> -600 THEN
507 -- IF BSC_APPS.APPS_ENV THEN
508 -- OPEN c_ksessions;
509 -- FETCH c_ksessions INTO h_ksession;
510 -- WHILE c_ksessions%FOUND LOOP
511 -- DELETE BSC_CURRENT_SESSIONS
512 -- WHERE SESSION_ID = h_ksession.audsid;
513 -- FETCH c_ksessions INTO h_ksession;
514 -- END LOOP;
515 -- CLOSE c_ksessions;
516 -- END IF;
517 --END IF;
518 --
519 -- Delete KILLED sessions from bsc_current_sesions
520 -- Even tough we do not kill session, we can try this.
521 --Delete bsc_current_sessions
522 --Where session_id In (Select vs.audsid From V$Session vs Where vs.status = 'KILLED');
523 --commit;
524
525 -- Make the query to validate if the application can run or not.
526 IF x_program_id = -600 then
527 -- iViewer.
528 -- Several instances of iViewer can run at the same time.
529 -- iViewer can run at the same time with Designer, Builder, Security, Loader and
530 -- Metadata Optimizer.(Bug 3731337)
531 -- iViewer cannot run at the same time with Migration, Upgrade
532
533 -- fixed the following SQLS for the literals bug
534
535 h_sql := 'SELECT c.program_id, u.user_name, s.machine, s.terminal'||
536 ' FROM bsc_current_sessions c, v$session s, bsc_apps_users_v u'||
537 ' WHERE c.session_id = s.audsid'||
538 ' AND c.program_id in (-700, -800)'||
539 ' AND c.session_id <> :1 '||
540 ' AND c.user_id = u.user_id (+)';
541
542 ELSIF x_program_id = -300 THEN
543 -- Security
544 -- Several instances can run at the same time.
545 -- It can run at the same time with iViewer, Metadata Optimizer(Generate Documentation or Rename input tables)
546 -- They cannot run at the same time with Loader or Metadata(Configure Indicators), Upgrade or Migration.
547 h_sql := 'SELECT c.program_id, u.user_name, s.machine, s.terminal'||
548 ' FROM bsc_current_sessions c, v$session s, bsc_apps_users_v u'||
549 ' WHERE c.session_id = s.audsid'||
550 ' AND c.program_id in (-100, -101, -200, -700, -800, -802)'||
551 ' AND c.session_id <> :1 '||
552 ' AND c.user_id = u.user_id (+)';
553
554 ELSIF x_program_id IN (-400, -500) THEN
555 -- Builder or Designer
556 -- Several instances can run at the same time.
557 -- They can run at the same time with iViewer, Security and Metadata Optimizer (Rename input tables)
558 -- They cannot run at the same time with Loader, Metadata(Configure Indicators),
559 -- Metadata Optmizer(Generate documention), Upgrade or Migration.
560 h_sql := 'SELECT c.program_id, u.user_name, s.machine, s.terminal'||
561 ' FROM bsc_current_sessions c, v$session s, bsc_apps_users_v u'||
562 ' WHERE c.session_id = s.audsid'||
563 ' AND c.program_id in (-100, -101, -200, -201, -700, -800, -802)'||
564 ' AND c.session_id <> :1 '||
565 ' AND c.user_id = u.user_id (+)';
566
567 ELSIF x_program_id = -100 THEN
568 -- Loader UI
569 -- Only one instance at the same time.
570 -- It cannot run at the same time with any other tool but IViewer and Metadata Optmizer(Generate documentation).
571 h_sql := 'SELECT c.program_id, u.user_name, s.machine, s.terminal'||
572 ' FROM bsc_current_sessions c, v$session s, bsc_apps_users_v u'||
573 ' WHERE c.session_id = s.audsid'||
574 ' AND c.program_id in (-100, -101, -200, -202, -300, -400, -500, -700, -800, -802)'||
575 ' AND c.session_id <> :1'||
576 ' AND c.user_id = u.user_id (+)';
577
578 ELSIF x_program_id = -101 THEN
579 -- Loader (Concurrent program)
580 -- Only one instance at the same time.
581 -- It cannot run at the same time with any other tool but IViewer and Metadata Optmizer(Generate documentation).
582 -- It cannot run with other Loader Concurrent program but It can run with a Loader UI.
583 h_sql := 'SELECT c.program_id, u.user_name, s.machine, s.terminal'||
584 ' FROM bsc_current_sessions c, v$session s, bsc_apps_users_v u'||
585 ' WHERE c.session_id = s.audsid'||
586 ' AND c.program_id in (-101, -200, -202, -300, -400, -500, -700, -800, -802)'||
587 ' AND c.session_id <> :1'||
588 ' AND c.user_id = u.user_id (+)';
589
590
591 ELSIF x_program_id = -200 THEN
592 -- Metadata(Configure indicators)
593 -- Only one instance at the same time.
594 -- It can run at the same time with: IViewer (Bug 3731337)
598 ' WHERE c.session_id = s.audsid'||
595 -- It cannot run at the same time with any other tool.
596 h_sql := 'SELECT c.program_id, u.user_name, s.machine, s.terminal'||
597 ' FROM bsc_current_sessions c, v$session s, bsc_apps_users_v u'||
599 ' AND c.program_id in (-100, -101, -200, -201, -202, -300, -400, -500, -700, -800, -802)'||
600 ' AND c.session_id <> :1 '||
601 ' AND c.user_id = u.user_id (+)';
602
603
604 ELSIF x_program_id = -201 THEN
605 -- Metadata Optimizer - Generate Documention
606 -- Several instances can run at the same time.
607 -- It can run at the same time with: Loader, Security and Viewer
608 -- It cannot run at the same time with: Metadata Optimizer(Configure Indicators or Rename input tables),
609 -- Builder, Designer, Upgrade or Migration
610 h_sql := 'SELECT c.program_id, u.user_name, s.machine, s.terminal'||
611 ' FROM bsc_current_sessions c, v$session s, bsc_apps_users_v u'||
612 ' WHERE c.session_id = s.audsid'||
613 ' AND c.program_id in (-200, -202, -400, -500, -700, -800, -802)'||
614 ' AND c.session_id <> :1 '||
615 ' AND c.user_id = u.user_id (+)';
616
617 ELSIF x_program_id = -202 THEN
618 -- Metadata Optimizer - Rename Input Tables
619 -- It can run at the same time with: Security, Designer, Builder and Viewer
620 -- It cannot run at the same time with: Metadata Optimizer(Configure Indicators or Rename input tables
621 -- or Generate Documentation), Loader, Upgrade or Migration
622 h_sql := 'SELECT c.program_id, u.user_name, s.machine, s.terminal'||
623 ' FROM bsc_current_sessions c, v$session s, bsc_apps_users_v u'||
624 ' WHERE c.session_id = s.audsid'||
625 ' AND c.program_id in (-100, -101, -200, -201, -202, -700, -800, -802)'||
626 ' AND c.session_id <> :1 '||
627 ' AND c.user_id = u.user_id (+)';
628
629 ELSIF x_program_id = -801 THEN
630 -- Migration ui
631 -- It cannot run if there is any other user in migration UI or migration CON req is running
632 h_sql := 'SELECT c.program_id, u.user_name, s.machine, s.terminal'||
633 ' FROM bsc_current_sessions c, v$session s, bsc_apps_users_v u'||
634 ' WHERE c.session_id = s.audsid'||
635 ' AND c.program_id in (-700, -800, -801, -802)'||
636 ' AND c.session_id <> :1 '||
637 ' AND c.user_id = u.user_id (+)';
638
639 ELSIF x_program_id = -802 THEN
640 -- Migration backend (source)
641 -- iViewer can run with Migration backend (source)
642 h_sql := 'SELECT c.program_id, u.user_name, s.machine, s.terminal'||
643 ' FROM bsc_current_sessions c, v$session s, bsc_apps_users_v u'||
644 ' WHERE c.session_id = s.audsid'||
645 ' AND c.program_id in (-100, -101, -200, -201, -202, -300, -400, -500, -700, -800, -801)'||
646 ' AND c.session_id <> :1 '||
647 ' AND c.user_id = u.user_id (+)';
648
649 ELSE
650 -- Upgrade or Migration
651 -- Only one instance at the same time.
652 -- It cannot run at the same time with any other tool.
653 -- code -801 added for migration UI. migration process can not run if any user is there in UI
654 h_sql := 'SELECT c.program_id, u.user_name, s.machine, s.terminal'||
655 ' FROM bsc_current_sessions c, v$session s, bsc_apps_users_v u'||
656 ' WHERE c.session_id = s.audsid'||
657 ' AND c.program_id in (-100, -101, -200, -201, -202, -300, -400, -500, -600, -700, -800, -801, -802)'||
658 ' AND c.session_id <> :1 '||
659 ' AND c.user_id = u.user_id (+)';
660 END IF;
661
662 h_cursor := DBMS_SQL.OPEN_CURSOR;
663 DBMS_SQL.PARSE(h_cursor, h_sql, DBMS_SQL.NATIVE);
664 DBMS_SQL.DEFINE_COLUMN(h_cursor, 1, h_program_id);
665 DBMS_SQL.DEFINE_COLUMN(h_cursor, 2, h_username, 100);
666 DBMS_SQL.DEFINE_COLUMN(h_cursor, 3, h_machine, 100);
667 DBMS_SQL.DEFINE_COLUMN(h_cursor, 4, h_terminal, 100);
668
669 DBMS_SQL.BIND_VARIABLE(h_cursor,':1', h_session_id); -- fix for literals bug#3075851
670 h_ret := DBMS_SQL.EXECUTE(h_cursor);
671
672 IF DBMS_SQL.FETCH_ROWS(h_cursor) > 0 THEN
673 DBMS_SQL.COLUMN_VALUE(h_cursor, 1, h_program_id);
674 DBMS_SQL.COLUMN_VALUE(h_cursor, 2, h_username);
675 DBMS_SQL.COLUMN_VALUE(h_cursor, 3, h_machine);
676 DBMS_SQL.COLUMN_VALUE(h_cursor, 4, h_terminal);
677
678 l_message := l_lock_msg;
679 l_message := bsc_apps.replace_token(l_message, 'COMPONENT', h_components(h_program_id));
680 l_message := bsc_apps.replace_token(l_message, 'USERNAME', h_username);
681
682 --------------------------------------------------------------
683 -- Jui Wang Apr/05/2001
684 -- Delete the invisible char, trailing CHR(0), from h_machine
685 -- Also close the cursor before raise BSC_Lock_Error
686 --------------------------------------------------------------
687 h_machine2 := REPLACE(h_machine, CHR(0));
688 l_message := bsc_apps.replace_token(l_message, 'MACHINE', h_machine2);
689 l_message := bsc_apps.replace_token(l_message, 'TERMINAL', h_terminal);
690 DBMS_SQL.CLOSE_CURSOR(h_cursor);
691 raise BSC_Lock_Error;
692 END IF;
693 DBMS_SQL.CLOSE_CURSOR(h_cursor);
694
698
695 -- register the process in bsc_current_sessions
696 -- 08/29/02 COMENT OUT NOCOPY USER_ID until this column is approved; Approved USER_ID column on 09/09/02
697 -- 03/18/03 Approved ICX_SESSOIN_ID column on 03/18/03, fix bug#2728234
699
700 -- added for Enh#2983050
701 select count(session_id)
702 into l_count
703 from bsc_current_sessions
704 where (session_id = h_session_id) and (icx_session_id = x_icx_session_id) and (program_id = x_program_id);
705
706 if (l_count = 0) then -- Entry for the same session and program exists
707 Insert Into bsc_current_sessions
708 (SESSION_ID,PROGRAM_ID,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,USER_ID, ICX_SESSION_ID)
709 Values (h_session_id, x_program_id, h_user_id, SYSDATE, h_user_id, SYSDATE, h_session_id,x_user_id, x_icx_session_id);
710 end if;
711
712 COMMIT;
713
714 Exception
715 WHEN BSC_Lock_Error THEN
716 BSC_MESSAGE.Add(
717 X_Message => l_message,
718 X_Source => l_calling_fn,
719 X_Mode => 'I');
720
721 WHEN Others THEN
722 BSC_MESSAGE.Add(
723 X_Message => SQLERRM,
724 X_Source => l_calling_fn,
725 X_Mode => 'I');
726
727 End Check_System_Lock;
728
729
730 --
731 -- Name
732 -- Refresh_System_Lock
733 -- Purpose
734 -- Cleanup BSC_CURRENT_SESSIONS table before acquiring locks
735 -- Called by BSC_SECURITY.CHECK_SYSTEM_LOCK and BSC_LOCKS_PUB.GET_SYSTEM_LOCK
736 -- 1) Delete all orphan the sessions
737 -- 2) Delete all the session not being reused by FND
738 -- 3) Delete all sessions, which have their concurrent programs in invalid or hang status
739 -- 4) Kill IViewer Sessions that have been INACTIVE more than 20 minutes
740 -- 5) Delete all the Killed Sessions
741
742 Procedure Refresh_System_Lock(
743 p_program_id IN Number
744 ) IS
745 CURSOR c_sessions IS
746 SELECT session_id
747 FROM bsc_current_sessions
748 WHERE program_id IN (-100,-101,-200,-201,-202,-800,-802);
749
750 l_session_ids VARCHAR2(8000);
751 l_sql VARCHAR2(8000);
752
753 BEGIN
754 --Delete all orphan the sessions
755 DELETE BSC_CURRENT_SESSIONS
756 WHERE SESSION_ID NOT IN
757 (SELECT VS.AUDSID
758 FROM V$SESSION VS);
759
760 --Delete all the session not being reused by FND
761 DELETE BSC_CURRENT_SESSIONS
762 WHERE ICX_SESSION_ID IN (
763 SELECT SESSION_ID
764 FROM ICX_SESSIONS
765 WHERE (FND_SESSION_MANAGEMENT.CHECK_SESSION(SESSION_ID,NULL,NULL,'N') <> 'VALID'));
766
767 --Delete all sessions, which have their concurrent programs in invalid or hang status
768 FOR cd IN c_sessions LOOP
769 IF(l_session_ids IS NULL ) THEN
770 l_session_ids := cd.session_id;
771 ELSE
772 l_session_ids := l_session_ids ||','||cd.session_id;
773 END IF;
774 END LOOP;
775 IF(l_session_ids IS NOT NULL) THEN
776 l_sql := ' DELETE bsc_current_sessions'||
777 ' WHERE session_id IN ('||
778 ' SELECT oracle_session_id '||
779 ' FROM fnd_concurrent_requests '||
780 ' WHERE program_application_id = 271 '||
781 ' AND oracle_session_id IN ('||l_session_ids ||' )'||
782 ' AND phase_code=''C'')';
783 EXECUTE IMMEDIATE l_sql ;
784 END IF;
785 --DELETE BSC_CURRENT_SESSIONS
786 --WHERE SESSION_ID IN (
787 -- SELECT NVL(ORACLE_SESSION_ID, -1)
788 -- FROM FND_CONCURRENT_REQUESTS
789 -- WHERE PHASE_CODE = 'C');
790
791 -- Kill IViewer Sessions that have been INACTIVE more than 20 minutes
792 IF p_program_id <> -600 THEN
793 IF BSC_APPS.APPS_ENV THEN
794 DELETE BSC_CURRENT_SESSIONS
795 WHERE PROGRAM_ID = -600
796 AND SESSION_ID IN (
797 SELECT s.audsid
798 FROM v$session s, v$session_wait w
799 WHERE s.sid = w.sid
800 AND w.seconds_in_wait > 1200);
801 END IF;
802 END IF;
803
804 --Delete all the Killed Sessions
805 DELETE BSC_CURRENT_SESSIONS
806 WHERE SESSION_ID IN (
807 SELECT VS.AUDSID
808 FROM V$SESSION VS
809 WHERE VS.STATUS = 'KILLED');
810 COMMIT;
811 END Refresh_System_Lock;
812
813
814 --
815 -- Name
816 -- Check_Source_System_Lock
817 -- Purpose
818 -- Enforce system locking for all OBSC models in the source system
819 -- This is issued by Migration (-800)
820 --
821 -- Parameter:
822 -- x_debug_flag - debug flag
823
824 Procedure Check_Source_System_Lock(
825 x_debug_flag IN Varchar2 := 'NO'
826 ) Is
827
828 l_calling_fn Varchar2(80);
829 l_message Varchar2(2000);
830 l_lock_msg Varchar2(2000) := NULL;
831 l_obsc_count Number := 0;
832
833 BSC_Lock_Error Exception;
837 h_src_session_id NUMBER;
834
835 h_session_id NUMBER;
836 h_user_id NUMBER;
838 h_src_user_id NUMBER;
839
840 h_sql VARCHAR2(2000);
841 h_cursor INTEGER;
842 h_ret INTEGER;
843 h_program_id NUMBER;
844 h_username VARCHAR2(100);
845 h_machine VARCHAR2(100);
846 h_terminal VARCHAR2(100);
847
848 TYPE t_array_of_varchar2 IS TABLE OF VARCHAR2(500)
849 INDEX BY BINARY_INTEGER;
850
851 h_components t_array_of_varchar2;
852
853 Begin
854 -- Initialize BSC/APPS global variables
855 BSC_APPS.Init_Bsc_Apps;
856
857 BSC_MESSAGE.Init(X_Debug_Flag => X_Debug_Flag);
858
859 l_calling_fn := 'BSC_SECURITY.CHECK_SOURCE_SYSTEM_LOCK';
860 l_lock_msg := bsc_apps.get_message('BSC_SEC_LOCKED_SRC_SYSTEM');
861 h_components(-100) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'BSC_LOADER'); -- Loader UI
862 h_components(-101) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'BSC_LOADER'); -- Loader Concurrent program
863 h_components(-200) := bsc_apps.get_lookup_value('BSC_UI_COMMON', 'METADATA_OPTIMIZER');
864 h_components(-201) := bsc_apps.get_lookup_value('BSC_UI_COMMON', 'METADATA_OPTIMIZER'); -- Generate documentation
865 h_components(-202) := bsc_apps.get_lookup_value('BSC_UI_COMMON', 'METADATA_OPTIMIZER'); -- Rename input tables
866 h_components(-300) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'BSC_ADMINISTRATOR');
867 h_components(-400) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'KPI_DESIGNER');
868 h_components(-500) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'BSC_BUILDER');
869 h_components(-600) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'OBSC_VIEWER');
870 h_components(-700) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'UPGRADE');
871 h_components(-800) := bsc_apps.get_lookup_value('BSC_UI_SETUP', 'SYSTEM_MIGRATION');
872
873 -- Get the session id in the source system
874 /* changed to fixed bug 2669465
875 h_sql := 'select s.user#, s.audsid'||
876 ' from v$session@'||c_src_db_link||' s'||
877 ' where s.process = ('||
878 ' select vs.process'||
879 ' from v$session vs'||
880 ' where vs.audsid = userenv(''SESSIONID'')'||
881 ' )'; */
882 h_sql := 'select s.user#, s.audsid from bsc_session_v@'||c_src_db_link||' s';
883
884 h_cursor := DBMS_SQL.OPEN_CURSOR;
885 DBMS_SQL.PARSE(h_cursor, h_sql, DBMS_SQL.NATIVE);
886 DBMS_SQL.DEFINE_COLUMN(h_cursor, 1, h_src_user_id);
887 DBMS_SQL.DEFINE_COLUMN(h_cursor, 2, h_src_session_id);
888 h_ret := DBMS_SQL.EXECUTE(h_cursor);
889
890 IF DBMS_SQL.FETCH_ROWS(h_cursor) > 0 THEN
891 DBMS_SQL.COLUMN_VALUE(h_cursor, 1, h_src_user_id);
892 DBMS_SQL.COLUMN_VALUE(h_cursor, 2, h_src_session_id);
893 END IF;
894 DBMS_SQL.CLOSE_CURSOR(h_cursor);
895
896
897 -- Get the session id in the current(target) system
898 -- Ref: bug#3482442 In corner cases this query can return more than one
899 -- row and it will fail. AUDSID is not PK. After meeting with
900 -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
901 h_session_id := USERENV('SESSIONID');
902 h_user_id := BSC_APPS.fnd_global_user_id;
903
904 -- Clean BSC_MESSAGE_LOGS for the current session
905 DELETE bsc_message_logs
906 WHERE last_update_login = h_session_id;
907 commit;
908
909 -- Clean BSC_CURRENT_SESSIONS in the source system to leave only current sessions
910 h_sql := 'Delete bsc_current_sessions@'||c_src_db_link||
911 ' Where session_id Not In (Select audsid From V$Session@'||c_src_db_link||')';
912 BSC_APPS.Execute_Immediate(h_sql);
913
914 commit;
915
916 -- Delete KILLED sessions from bsc_current_sessions
917 h_sql := 'Delete bsc_current_sessions@'||c_src_db_link||
918 ' Where session_id In (Select audsid From V$Session@'||c_src_db_link||
919 ' Where status = ''KILLED'')';
920 BSC_APPS.Execute_Immediate(h_sql);
921
922 commit;
923
924 -- Make the query to validate if the application can run or not.
925 -- This procedure is called by MIgration (-800)
926 -- Only one instance at the same time.
927 -- It cannot run at the same time with any other tool.
928 h_sql := 'SELECT c.program_id, s.username, s.machine, s.terminal'||
929 ' FROM bsc_current_sessions@'||c_src_db_link||' c, v$session@'||c_src_db_link||' s'||
930 ' WHERE c.session_id = s.audsid'||
931 ' AND c.program_id in (-100, -101, -200, -201, -202, -300, -400, -500, -600, -700, -800)'||
932 ' AND c.session_id <> :1 ';
933
934 h_cursor := DBMS_SQL.OPEN_CURSOR;
935 DBMS_SQL.PARSE(h_cursor, h_sql, DBMS_SQL.NATIVE);
936 DBMS_SQL.DEFINE_COLUMN(h_cursor, 1, h_program_id);
937 DBMS_SQL.DEFINE_COLUMN(h_cursor, 2, h_username, 100);
938 DBMS_SQL.DEFINE_COLUMN(h_cursor, 3, h_machine, 100);
942 h_ret := DBMS_SQL.EXECUTE(h_cursor);
939 DBMS_SQL.DEFINE_COLUMN(h_cursor, 4, h_terminal, 100);
940
941 DBMS_SQL.BIND_VARIABLE(h_cursor,':1', h_src_session_id); -- fixed for literals bug
943
944 IF DBMS_SQL.FETCH_ROWS(h_cursor) > 0 THEN
945 DBMS_SQL.COLUMN_VALUE(h_cursor, 1, h_program_id);
946 DBMS_SQL.COLUMN_VALUE(h_cursor, 2, h_username);
947 DBMS_SQL.COLUMN_VALUE(h_cursor, 3, h_machine);
948 DBMS_SQL.COLUMN_VALUE(h_cursor, 4, h_terminal);
949
950 l_message := l_lock_msg;
951 l_message := bsc_apps.replace_token(l_message, 'COMPONENT', h_components(h_program_id));
952 l_message := bsc_apps.replace_token(l_message, 'USERNAME', h_username);
953 l_message := bsc_apps.replace_token(l_message, 'MACHINE', h_machine);
954 l_message := bsc_apps.replace_token(l_message, 'TERMINAL', h_terminal);
955
956 raise BSC_Lock_Error;
957 END IF;
958 DBMS_SQL.CLOSE_CURSOR(h_cursor);
959
960 -- register the process in bsc_current_sessions
961 h_sql := 'Insert Into bsc_current_sessions@'||c_src_db_link||
962 ' (SESSION_ID,PROGRAM_ID,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)'||
963 ' Values ('||h_src_session_id||', -800, '||h_src_user_id||', SYSDATE, '||
964 h_src_user_id||', SYSDATE, '||h_src_session_id||')';
965 BSC_APPS.Execute_Immediate(h_sql);
966
967 COMMIT;
968
969 Exception
970 WHEN BSC_Lock_Error THEN
971 BSC_MESSAGE.Add(
972 X_Message => l_message,
973 X_Source => l_calling_fn,
974 X_Mode => 'I');
975
976 WHEN Others THEN
977 BSC_MESSAGE.Add(
978 X_Message => SQLERRM,
979 X_Source => l_calling_fn,
980 X_Mode => 'I');
981
982 End Check_Source_System_Lock;
983
984
985 /*
986 ADRAO 18-JUL-03 Modified for Global Button Enhancement.
987
988 Added Exception Block.
989 */
990
991
992 PROCEDURE Delete_Bsc_Session IS
993
994 l_calling_fn Varchar2(80);
995 l_message Varchar2(2000);
996
997 BSC_Lock_Error Exception;
998
999 BEGIN
1000 l_calling_fn := 'BSC_SECURITY.DELETE_BSC_SESSION';
1001
1002 DELETE BSC_CURRENT_SESSIONS
1003 WHERE SESSION_ID = USERENV('SESSIONID');
1004 COMMIT;
1005
1006 Exception
1007 WHEN BSC_Lock_Error THEN
1008 BSC_MESSAGE.Add(
1009 X_Message => l_message,
1010 X_Source => l_calling_fn,
1011 X_Mode => 'I');
1012
1013 WHEN Others THEN
1014 BSC_MESSAGE.Add(
1015 X_Message => SQLERRM,
1016 X_Source => l_calling_fn,
1017 X_Mode => 'I');
1018
1019 END Delete_Bsc_Session;
1020
1021
1025 Added Exception Block.
1022 /*
1023 ADRAO 18-JUL-03 Added for Global Button Enhancement.
1024
1026 */
1027
1028
1029 PROCEDURE Delete_Bsc_Session_ICX(
1030 p_icx_session_id IN NUMBER
1031 ) IS
1032
1033 BSC_Lock_Error Exception;
1034
1035 l_calling_fn Varchar2(80);
1036 l_message Varchar2(2000);
1037
1038 BEGIN
1039
1040 l_calling_fn := 'BSC_SECURITY.DELETE_BSC_SESSION_ICX';
1041
1042 IF (p_icx_session_id IS NOT NULL) THEN
1043 DELETE BSC_CURRENT_SESSIONS
1044 WHERE ICX_SESSION_ID = p_icx_session_id;
1045
1046 COMMIT;
1047 END IF;
1048
1049 EXCEPTION
1050 WHEN BSC_Lock_Error THEN
1051 BSC_MESSAGE.Add(
1052 X_Message => l_message,
1053 X_Source => l_calling_fn,
1054 X_Mode => 'I');
1055
1056 WHEN Others THEN
1057 BSC_MESSAGE.Add(
1058 X_Message => SQLERRM,
1059 X_Source => l_calling_fn,
1060 X_Mode => 'I');
1061
1062 END Delete_Bsc_Session_ICX;
1063
1064 --
1065 -- Name
1066 -- user_has_lock
1067 -- Purpose
1068 -- Return Y if user holds locks
1069 -- else return N
1070 -- Parameter:
1071 -- x_SID - Sessuib ID that user currently belongs
1072
1073 FUNCTION user_has_lock (
1074 X_SID in NUMBER
1075 ) RETURN VARCHAR2 is
1076
1077 l_count number;
1078 yes_no varchar2(1);
1079 h_sql VARCHAR2(2000);
1080 TYPE t_cursor IS REF CURSOR;
1081 h_cursor t_cursor;
1082
1083 begin
1084
1085 return user_has_lock(X_SID,BSC_APPS.get_user_schema);
1086 /*yes_no :='N';
1087 -- bug fix 3008243
1088
1089 h_sql :='SELECT count(*) FROM dba_objects A, v$locked_object B'||
1090 ' WHERE A.OBJECT_ID = B.OBJECT_ID AND A.OWNER = BSC_APPS.get_user_schema AND OBJECT_NAME LIKE ''BSC%'' AND SESSION_ID = :1';
1091
1092 OPEN h_cursor FOR h_sql USING X_SID;
1093 FETCH h_cursor INTO l_count;
1094 IF h_cursor%NOTFOUND THEN
1095 yes_no := 'N';
1096 l_count := 0;
1097 END IF;
1098 CLOSE h_cursor;
1099
1100 if l_count > 0 then
1101 yes_no :='Y';
1102 end if;
1103
1104 RETURN yes_no;*/
1105 end user_has_lock;
1106 --
1107 -- Name
1108 -- user_has_lock
1109 -- Purpose
1110 -- Return Y if user holds locks
1111 -- else return N
1112 -- Parameter:
1113 -- x_SID - Sessuib ID that user currently belongs
1114 -- x_Schema - BSC schema name performance improvement
1115
1116 FUNCTION user_has_lock (
1117 X_SID in NUMBER,
1118 X_SCHEMA IN VARCHAR2
1119 ) RETURN VARCHAR2 is
1120
1121 l_count number;
1122 yes_no varchar2(1);
1123 h_sql VARCHAR2(2000);
1124 TYPE t_cursor IS REF CURSOR;
1125 h_cursor t_cursor;
1126
1127 begin
1128
1129 yes_no :='N';
1130 -- bug fix 3008243
1131
1132 h_sql :='SELECT count(B.OBJECT_ID) FROM all_objects A, v$locked_object B'||
1133 ' WHERE B.OBJECT_ID = A.OBJECT_ID AND A.OWNER = :1 AND OBJECT_NAME LIKE ''BSC%'' AND SESSION_ID = :2';
1134
1135 OPEN h_cursor FOR h_sql USING X_SCHEMA, X_SID;
1136 FETCH h_cursor INTO l_count;
1137 IF h_cursor%NOTFOUND THEN
1138 yes_no := 'N';
1139 l_count := 0;
1140 END IF;
1141 CLOSE h_cursor;
1142
1143 if l_count > 0 then
1144 yes_no :='Y';
1145 end if;
1146
1147 RETURN yes_no;
1148 end user_has_lock;
1149
1150 --
1151 -- Name
1152 -- can_meta_run
1153 -- Purpose
1154 -- Return Y if no user holds any locks and Meta Optimizer can start running
1155 -- else return N
1156 --
1157
1158 FUNCTION can_meta_run
1159 RETURN VARCHAR2 is
1160
1161 l_count number;
1162 yes_no varchar2(1);
1163 h_sql VARCHAR2(2000);
1164 TYPE t_cursor IS REF CURSOR;
1165 h_cursor t_cursor;
1166
1167 begin
1168
1169 yes_no :='Y';
1170 -- bug fix 3008243
1171
1172 h_sql :='SELECT count(*) FROM dba_objects A, v$locked_object B'||
1173 ' WHERE A.OBJECT_ID = B.OBJECT_ID AND A.OWNER = BSC_APPS.get_user_schema AND OBJECT_NAME LIKE ''BSC%''';
1174
1175 OPEN h_cursor FOR h_sql;
1176 FETCH h_cursor INTO l_count;
1177 IF h_cursor%NOTFOUND THEN
1178 yes_no := 'Y';
1179 l_count := 0;
1180 END IF;
1181 CLOSE h_cursor;
1182
1183 if l_count > 0 then
1184 yes_no :='N';
1185 end if;
1186
1187 RETURN yes_no;
1188 end can_meta_run;
1189
1190 --
1191 -- Name
1192 -- is_meta_inside
1193 -- Purpose
1194 -- Return Y if Meta Optimizer is inside the system
1195 -- else return N
1196
1197 FUNCTION is_meta_inside
1198 RETURN VARCHAR2 is
1199
1200 l_count number;
1201 yes_no varchar2(1);
1202 h_sql VARCHAR2(2000);
1203 TYPE t_cursor IS REF CURSOR;
1204 h_cursor t_cursor;
1205
1206 begin
1207
1208 h_sql :='SELECT COUNT(*) FROM BSC_CURRENT_SESSIONS C, V$SESSION S, V$SESSION_WAIT W, BSC_APPS_USERS_V U ';
1209 h_sql := h_sql || 'WHERE C.SESSION_ID = S.AUDSID AND S.SID = W.SID AND S.STATUS <> ''KILLED'' AND U.USER_ID = C.USER_ID AND C.PROGRAM_ID = -200';
1210
1211 yes_no :='N';
1212 -- bug fix 3008243
1213
1214 h_sql :='SELECT count(*) FROM dba_objects A, v$locked_object B'||
1215 ' WHERE A.OBJECT_ID = B.OBJECT_ID AND A.OWNER = BSC_APPS.get_user_schema AND OBJECT_NAME LIKE ''BSC%''';
1216
1217 OPEN h_cursor FOR h_sql;
1218 FETCH h_cursor INTO l_count;
1219 IF h_cursor%NOTFOUND THEN
1220 yes_no := 'N';
1221 l_count := 0;
1222 END IF;
1223 CLOSE h_cursor;
1224
1225 if l_count > 0 then
1226 yes_no :='Y';
1227 end if;
1228
1229 RETURN yes_no;
1230 end is_meta_inside;
1231
1232
1233 END bsc_security;
[Home] [Help]