[Home] [Help]
PACKAGE BODY: APPS.BSC_APPS
Source
1 PACKAGE BODY BSC_APPS AS
2 /* $Header: BSCAPPSB.pls 120.4 2006/04/18 16:40:35 arsantha noship $ */
3 /*===========================================================================+
4 | Copyright (c) 1999 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved |
7 |============================================================================|
8 |
9 | Name: BSCAPPSB.pls
10 |
11 | Description: This package contains procedures to perform APPS
12 | related calls.
13 |
14 | Example:
15 |
16 | Security:
17 |
18 | History: Created By: Mauricio Eastmond Date: 04-JAN-00
19 |
20 | Srini Jandyala 27-Feb-02 Added Get_Lookup_Value() procedure for HTML UI
21 | 27-03-2003 Adeulgao fixed bug#2865694
22 | used alias for view "v$parameter" |
23 | 20-Aug-03 Adeulgao fixed bug#3008243 added 2 functions |
24 | (overloaded) get_user_schema to get the schema name |
25 | 22-Aug-03 Adeulgao modified the query to fetch oracle schema name|
26 | 27-Aug-03 Aditya Removed Hardcoded literals. |
27 | 15-Sep-03 wleung modified index query in Init_Big_In_Cond_Table. |
28 | 15-DEC-2003 Aditya Rao removed Dynamic SQLs for Bug #3236356 |
29 | |
30 | 19-APR-2004 PAJOHRI Bug #3541933, added a overloaded function |
31 | Do_DDL_AT |
32 | 18-jul-2005 ashankar Bug#4214158 changed the value of the constant |
33 | c_version to 5.3.0 |
34 | 19-AUG-2005 KYADAMAK BUG#4559027 implemented caching for schema names|
35 | 18-APR-2006 ARSANTHA BUG 5162628 wrt tablespace for old mode |
36 +============================================================================*/
37
38 --
39 -- Package constants
40 --
41 -- Formats
42 c_fto_long_date_time CONSTANT VARCHAR2(30) := 'Month DD, YYYY HH24:MI:SS';
43 c_version CONSTANT VARCHAR2(5) := '5.3.0';
44
45 --
46 -- Package variables
47 --
48 g_log_file_dir VARCHAR2(60) := NULL;
49 g_log_file_name VARCHAR2(2000) := NULL;
50
51 -- bsc and apps user schema names
52 g_bsc_user_schema CONSTANT VARCHAR2(100) := 'BSC';
53 g_apps_user_schema CONSTANT VARCHAR2(100) := 'APPS';
54
55 --This is for cachin User schema names
56 TYPE user_schema_table IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(30);
57 user_schema_tbl user_schema_table;
58
59
60
61 /*===========================================================================+
62 | PROCEDURE Add_Value_Big_In_Cond (Number)
63 +============================================================================*/
64
65 PROCEDURE Add_Value_Big_In_Cond(
66 x_variable_id IN NUMBER,
67 x_value IN NUMBER
68 ) IS
69
70 h_sql VARCHAR2(32700);
71
72 BEGIN
73
74 h_sql := 'INSERT INTO BSC_TMP_BIG_IN_COND (SESSION_ID, VARIABLE_ID, VALUE_N, VALUE_V)'||
75 ' VALUES (USERENV(''SESSIONID''), :1, :2, NULL)';
76 EXECUTE IMMEDIATE h_sql USING x_variable_id, x_value;
77
78 END Add_Value_Big_In_Cond;
79 /*===========================================================================+
80 | FUNCTION to parse Lookup Types, Codes, Names
81 +============================================================================*/
82 --==============================================================
83 FUNCTION Is_More
84 ( x_Lookup_Types IN OUT NOCOPY VARCHAR2
85 , x_Lookup_Codes IN OUT NOCOPY VARCHAR2
86 , x_Token_Names IN OUT NOCOPY VARCHAR2
87 , x_Lookup_Type OUT NOCOPY VARCHAR2
88 , x_Lookup_Code OUT NOCOPY VARCHAR2
89 , x_Token_Name OUT NOCOPY VARCHAR2
90 ) RETURN BOOLEAN IS
91 l_Lookup_Ids NUMBER;
92 l_Lookup_Codes NUMBER;
93 l_Lookup_Names NUMBER;
94 BEGIN
95 IF (x_Lookup_Types IS NOT NULL) THEN
96 l_Lookup_Ids := INSTR(x_Lookup_Types, ',');
97 l_Lookup_Codes := INSTR(x_Lookup_Codes, ',');
98 l_Lookup_Names := INSTR(x_Token_Names, ',');
99
100 IF (l_Lookup_Ids > 0) THEN
101 x_Lookup_Type := TRIM(SUBSTR(x_Lookup_Types, 1, l_Lookup_Ids - 1));
102 x_Lookup_Code := TRIM(SUBSTR(x_Lookup_Codes, 1, l_Lookup_Codes - 1));
103 x_Token_Name := TRIM(SUBSTR(x_Token_Names, 1, l_Lookup_Names - 1));
104
105 IF (UPPER(x_Token_Name) = 'NULL') THEN
106 x_Token_Name := NULL;
107 END IF;
108
109 x_Lookup_Types := TRIM(SUBSTR(x_Lookup_Types, l_Lookup_Ids + 1));
110 x_Lookup_Codes := TRIM(SUBSTR(x_Lookup_Codes, l_Lookup_Codes + 1));
111 x_Token_Names := TRIM(SUBSTR(x_Token_Names, l_Lookup_Names + 1));
112 ELSE
113 x_Lookup_Type := TRIM(x_Lookup_Types);
114 x_Lookup_Code := TRIM(x_Lookup_Codes);
115 x_Token_Name := TRIM(x_Token_Names);
116
117 IF (UPPER(x_Token_Name) = 'NULL') THEN
118 x_Token_Name := NULL;
119 END IF;
120
121 x_Lookup_Types := NULL;
122 x_Token_Names := NULL;
123 x_Lookup_Codes := NULL;
124 END IF;
125 RETURN TRUE;
126 ELSE
127 RETURN FALSE;
128 END IF;
129 END Is_More;
130 /*===========================================================================+
131 | PROCEDURE Add_Value_Big_In_Cond (Varchar2)
132 +============================================================================*/
133 PROCEDURE Add_Value_Big_In_Cond(
134 x_variable_id IN NUMBER,
135 x_value IN VARCHAR2
136 ) IS
137
138 h_sql VARCHAR2(32700);
139
140 BEGIN
141
142 h_sql := 'INSERT INTO BSC_TMP_BIG_IN_COND (SESSION_ID, VARIABLE_ID, VALUE_N, VALUE_V)'||
143 ' VALUES (USERENV(''SESSIONID''), :1, NULL, :2)';
144 EXECUTE IMMEDIATE h_sql USING x_variable_id, x_value;
145
146 END Add_Value_Big_In_Cond;
147
148
149 /*===========================================================================+
150 | PROCEDURE Apps_Initilize_VB
151 +============================================================================*/
152 PROCEDURE Apps_Initialize_VB(
153 x_user_id IN NUMBER,
154 x_resp_id IN NUMBER
155 ) IS
156
157 h_appl_id NUMBER;
158
159 BEGIN
160
161 SELECT application_id INTO h_appl_id
162 FROM fnd_responsibility
163 WHERE responsibility_id = x_resp_id;
164
165 FND_GLOBAL.Apps_Initialize(user_id => x_user_id,
166 resp_id => x_resp_id,
167 resp_appl_id => h_appl_id);
168 COMMIT;
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 BSC_MESSAGE.Add(x_message => SQLERRM,
173 x_source => 'BSC_APPS.Apps_Initialize_VB',
174 x_mode => 'I');
175 COMMIT;
176
177 END Apps_Initialize_VB;
178
179
180 /*===========================================================================+
181 | FUNCTION CheckError
182 +============================================================================*/
183 FUNCTION CheckError(
184 x_calling_function IN VARCHAR2
185 ) RETURN BOOLEAN IS
186
187 h_count NUMBER;
188
189 BEGIN
190
191 SELECT count(*)
192 INTO h_count
193 FROM bsc_message_logs
194 WHERE type = 0
195 AND UPPER(source) = UPPER(x_calling_function)
196 AND last_update_login = USERENV('SESSIONID');
197
198 IF h_count > 0 THEN
199 RETURN TRUE;
200 END IF;
201
202 RETURN FALSE;
203
204 END CheckError;
205
206
207 /*===========================================================================+
208 | PROCEDURE Do_DDL
209 +============================================================================*/
210 PROCEDURE Do_DDL(
211 x_statement IN VARCHAR2,
212 x_statement_type IN INTEGER := 0,
213 x_object_name IN VARCHAR2 := NULL
214 ) IS
215
216 BEGIN
217
218 IF apps_env THEN
219 AD_DDL.Do_DDL(fnd_apps_schema,
220 bsc_apps_short_name,
221 x_statement_type,
222 x_statement,
223 x_object_name);
224 ELSE
225 Execute_DDL(x_statement);
226 END IF;
227
228 END Do_DDL;
229
230
231 /*===========================================================================+
232 | PROCEDURE Do_DDL_AT
233 +============================================================================*/
234 PROCEDURE Do_DDL_AT(
235 x_statement IN VARCHAR2,
236 x_statement_type IN INTEGER := 0,
237 x_object_name IN VARCHAR2 := NULL,
238 x_fnd_apps_schema IN VARCHAR2,
239 x_bsc_apps_short_name IN VARCHAR2
240 ) IS
241 PRAGMA AUTONOMOUS_TRANSACTION;
242 BEGIN
243
244 AD_DDL.Do_DDL(x_fnd_apps_schema,
245 x_bsc_apps_short_name,
246 x_statement_type,
247 x_statement,
248 x_object_name);
249
250 END Do_DDL_AT;
251
252
253 /*===========================================================================+
254 | PROCEDURE Do_DDL_VB
255 +============================================================================*/
256 PROCEDURE Do_DDL_VB(
257 x_statement IN VARCHAR2,
258 x_statement_type IN INTEGER := 0,
259 x_object_name IN VARCHAR2 := NULL
260 ) IS
261
262 BEGIN
263 Do_DDL(x_statement, x_statement_type, x_object_name);
264
265 EXCEPTION
266 WHEN OTHERS THEN
267 BSC_MESSAGE.Add(x_message => SQLERRM,
268 x_source => 'BSC_APPS.Do_DDL_VB',
269 x_mode => 'I');
270 COMMIT;
271
272 END Do_DDL_VB;
273
274
275 /*===========================================================================+
276 | FUNCTION Get_Lookup_Value
277 +============================================================================*/
278 FUNCTION Get_Lookup_Value(
279 x_lookup_type IN VARCHAR2,
280 x_lookup_code IN VARCHAR2
281 ) RETURN VARCHAR2 IS
282
283 CURSOR c_lookup_value IS
284 SELECT
285 meaning
286 FROM
287 bsc_lookups
288 WHERE
289 lookup_type = x_lookup_type AND
290 lookup_code = x_lookup_code;
291
292 h_lookup_value VARCHAR2(4000);
293
294 BEGIN
295 OPEN c_lookup_value;
296 FETCH c_lookup_value INTO h_lookup_value;
297 IF c_lookup_value%NOTFOUND THEN
298 h_lookup_value := NULL;
299 END IF;
300 CLOSE c_lookup_value;
301
302 RETURN h_lookup_value;
303
304 END Get_Lookup_Value;
305
306 /*===========================================================================+
307 | PROCEDURE Get_Lookup_Value
308 +============================================================================*/
309 PROCEDURE Get_Lookup_Value(
310 x_lookup_type in varchar2,
311 x_lookup_code in varchar2,
312 x_meaning out NOCOPY varchar2
313
314 ) IS
315
316 BEGIN
317
318 x_meaning := Get_Lookup_Value(x_lookup_type, x_lookup_code);
319
320 END Get_Lookup_Value;
321
322
323 /*===========================================================================+
324 | FUNCTION Get_Message
325 +============================================================================*/
326 FUNCTION Get_Message(
327 x_message_name IN VARCHAR2
328 ) RETURN VARCHAR2 IS
329
330 CURSOR c_message IS
331 SELECT
332 message_text
333 FROM
334 bsc_messages
335 WHERE
336 message_name = x_message_name;
337
338 h_message VARCHAR2(4000);
339
340 BEGIN
341 OPEN c_message;
342 FETCH c_message INTO h_message;
343 IF c_message%NOTFOUND THEN
344 h_message := NULL;
345 END IF;
346 CLOSE c_message;
347
348 RETURN h_message;
349
350 END Get_Message;
351
352
353 /*===========================================================================+
354 | FUNCTION Get_New_Big_In_Cond_Number
355 +============================================================================*/
356 FUNCTION Get_New_Big_In_Cond_Number(
357 x_variable_id IN NUMBER,
358 x_column_name IN VARCHAR2
359 ) RETURN VARCHAR2 IS
360
361 h_sql VARCHAR2(32700);
362 h_cond VARCHAR2(2000);
363
364 BEGIN
365
366 h_sql := 'DELETE FROM BSC_TMP_BIG_IN_COND'||
367 ' WHERE SESSION_ID = USERENV(''SESSIONID'')'||
368 ' AND VARIABLE_ID = :1';
369 EXECUTE IMMEDIATE h_sql USING x_variable_id;
370
371 h_cond := x_column_name||' IN ('||
372 'SELECT VALUE_N FROM BSC_TMP_BIG_IN_COND'||
373 ' WHERE SESSION_ID = USERENV(''SESSIONID'')'||
374 ' AND VARIABLE_ID = '||x_variable_id||
375 ')';
376
377 return h_cond;
378
379 END Get_New_Big_In_Cond_Number;
380
381
382 /*===========================================================================+
383 | FUNCTION Get_New_Big_In_Cond_Varchar2
384 +============================================================================*/
385 FUNCTION Get_New_Big_In_Cond_Varchar2(
386 x_variable_id IN NUMBER,
387 x_column_name IN VARCHAR2
388 ) RETURN VARCHAR2 IS
389
390 h_sql VARCHAR2(32700);
394
391 h_cond VARCHAR2(2000);
392
393 BEGIN
395 h_sql := 'DELETE FROM BSC_TMP_BIG_IN_COND'||
396 ' WHERE SESSION_ID = USERENV(''SESSIONID'')'||
397 ' AND VARIABLE_ID = :1';
398 /*Execute_Immediate(h_sql);*/
399 EXECUTE IMMEDIATE h_sql USING x_variable_id;
400
401 h_cond := 'UPPER('||x_column_name||') IN ('||
402 'SELECT UPPER(VALUE_V) FROM BSC_TMP_BIG_IN_COND'||
403 ' WHERE SESSION_ID = USERENV(''SESSIONID'')'||
404 ' AND VARIABLE_ID = '||x_variable_id||
405 ')';
406
407 return h_cond;
408
409 END Get_New_Big_In_Cond_Varchar2;
410
411
412 -- Add this new function. It does not use UPPER
413 /*===========================================================================+
414 | FUNCTION Get_New_Big_In_Cond_Varchar2NU
415 +============================================================================*/
416 FUNCTION Get_New_Big_In_Cond_Varchar2NU(
417 x_variable_id IN NUMBER,
418 x_column_name IN VARCHAR2
419 ) RETURN VARCHAR2 IS
420
421 h_sql VARCHAR2(32700);
422 h_cond VARCHAR2(2000);
423
424 BEGIN
425
426 h_sql := 'DELETE FROM BSC_TMP_BIG_IN_COND'||
427 ' WHERE SESSION_ID = USERENV(''SESSIONID'')'||
428 ' AND VARIABLE_ID = :1';
429 /*Execute_Immediate(h_sql);*/
430 EXECUTE IMMEDIATE h_sql USING x_variable_id;
431
432 h_cond := x_column_name||' IN ('||
433 'SELECT VALUE_V FROM BSC_TMP_BIG_IN_COND'||
434 ' WHERE SESSION_ID = USERENV(''SESSIONID'')'||
435 ' AND VARIABLE_ID = '||x_variable_id||
436 ')';
437
438 return h_cond;
439
440 END Get_New_Big_In_Cond_Varchar2NU;
441
442
443 /*===========================================================================+
444 | FUNCTION Get_Property_Value
445 +============================================================================*/
446 FUNCTION Get_Property_Value(
447 p_property_list IN VARCHAR2,
448 p_property_name IN VARCHAR2
449 ) RETURN VARCHAR2 IS
450
451 l_property_value VARCHAR2(200) := NULL;
452 l_property_name VARCHAR2(200);
453 l_property_list VARCHAR2(32000);
454 l_i NUMBER;
455 l_j NUMBER;
456
457 BEGIN
458
459 IF p_property_list IS NOT NULL THEN
460 l_property_list := '&'||p_property_list||'&';
461 l_property_name := '&'||p_property_name||'=';
462 l_i := INSTR(l_property_list, l_property_name);
463 IF l_i > 0 THEN
464 l_j := l_i + LENGTH(l_property_name);
465 l_i := INSTR(l_property_list, '&', l_j);
466 l_property_value := RTRIM(LTRIM(SUBSTR(l_property_list, l_j, l_i-l_j)));
467 END IF;
468 END IF;
469
470 RETURN l_property_value;
471
472 END Get_Property_Value;
473
474
475 /*===========================================================================+
476 | FUNCTION Set_Property_Value
477 +============================================================================*/
478 FUNCTION Set_Property_Value(
479 p_property_list IN VARCHAR2,
480 p_property_name IN VARCHAR2,
481 p_property_value IN VARCHAR2
482 ) RETURN VARCHAR2 IS
483
484 l_property_value VARCHAR2(200) := NULL;
485 l_property_name VARCHAR2(200);
486 l_property_list VARCHAR2(32000);
487 l_new_property_list VARCHAR2(32000);
488 l_i NUMBER;
489 l_j NUMBER;
490
491 BEGIN
492
493 IF p_property_list IS NULL THEN
494 l_new_property_list := p_property_name||'='||p_property_value;
495 ELSE
496 l_property_list := '&'||p_property_list||'&';
497 l_property_name := '&'||p_property_name||'=';
498 l_i := INSTR(l_property_list, l_property_name);
499 IF l_i > 0 THEN
500 -- property already exists
501 -- replace the value
502 l_j := l_i + LENGTH(l_property_name);
503 l_j := INSTR(l_property_list, '&', l_j);
504
505 l_new_property_list := SUBSTR(l_property_list, 1, l_i)||
506 p_property_name||'='||p_property_value||
507 SUBSTR(l_property_list, l_j);
508 l_new_property_list := LTRIM(RTRIM(l_new_property_list, '&'),'&');
509 ELSE
510 -- property does not exists
511 -- add it
512 l_new_property_list := p_property_list||'&'||p_property_name||'='||p_property_value;
513 END IF;
514 END IF;
515
516 RETURN l_new_property_list;
517
518 END Set_Property_Value;
519
520
521 /*===========================================================================+
522 | PROCEDURE Get_Request_Status_VB
523 +============================================================================*/
524 PROCEDURE Get_Request_Status_VB(
525 x_request_id IN NUMBER
526 ) IS
527
528 h_phase VARCHAR2(2000) := NULL;
529 h_status VARCHAR2(2000) := NULL;
530 h_dev_phase VARCHAR2(2000) := NULL;
531 h_dev_status VARCHAR2(2000) := NULL;
532 h_message VARCHAR2(2000) := NULL;
533 h_request_id NUMBER;
537 h_request_id := x_request_id;
534 h_res BOOLEAN;
535
536 BEGIN
538
539 IF FND_CONCURRENT.Get_Request_Status(h_request_id,
540 '', '',
541 h_phase,
542 h_status,
543 h_dev_phase,
544 h_dev_status,
545 h_message) THEN
546 -- Insert the information in BSC_MESSAGE_LOGS table
547 -- type 30 h_phase, type 31 h_status
548 -- type 32 h_dev_phase, type 33 h_dev_status, type 34 h_message
549 IF h_phase IS NOT NULL THEN
550 BSC_MESSAGE.Add(x_message => h_phase,
551 x_source => 'BSC_APPS.Get_Request_Status_VB',
552 x_type => 30,
553 x_mode => 'I');
554 END IF;
555
556 IF h_status IS NOT NULL THEN
557 BSC_MESSAGE.Add(x_message => h_status,
558 x_source => 'BSC_APPS.Get_Request_Status_VB',
559 x_type => 31,
560 x_mode => 'I');
561 END IF;
562
563 IF h_dev_phase IS NOT NULL THEN
564 BSC_MESSAGE.Add(x_message => h_dev_phase,
565 x_source => 'BSC_APPS.Get_Request_Status_VB',
566 x_type => 32,
567 x_mode => 'I');
568 END IF;
569
570 IF h_dev_status IS NOT NULL THEN
571 BSC_MESSAGE.Add(x_message => h_dev_status,
572 x_source => 'BSC_APPS.Get_Request_Status_VB',
573 x_type => 33,
574 x_mode => 'I');
575 END IF;
576
577 IF h_message IS NOT NULL THEN
578 BSC_MESSAGE.Add(x_message => h_message,
579 x_source => 'BSC_APPS.Get_Request_Status_VB',
580 x_type => 34,
581 x_mode => 'I');
582 END IF;
583
584 COMMIT;
585
586 END IF;
587
588 EXCEPTION
589 WHEN OTHERS THEN
590 BSC_MESSAGE.Add(x_message => SQLERRM,
591 x_source => 'BSC_APPS.Get_Request_Status_VB',
592 x_mode => 'I');
593 COMMIT;
594 END Get_Request_Status_VB;
595
596
597 /*===========================================================================+
598 | FUNCTION Get_Storage_Clause
599 +============================================================================*/
600 FUNCTION Get_Storage_Clause RETURN VARCHAR2 IS
601 BEGIN
602 RETURN bsc_storage_clause;
603 END Get_Storage_Clause;
604
605
606 /*===========================================================================+
607 | FUNCTION Get_Tablespace_Clause_Tbl
608 +============================================================================*/
609 FUNCTION Get_Tablespace_Clause_Tbl RETURN VARCHAR2 IS
610 BEGIN
611 RETURN bsc_tablespace_clause_tbl;
612 END Get_Tablespace_Clause_Tbl;
613
614
615 /*===========================================================================+
616 | FUNCTION Get_Tablespace_Clause_Idx
617 +============================================================================*/
618 FUNCTION Get_Tablespace_Clause_Idx RETURN VARCHAR2 IS
619 BEGIN
620 RETURN bsc_tablespace_clause_idx;
621 END Get_Tablespace_Clause_Idx;
622
623
624 /*===========================================================================+
625 | FUNCTION Get_Tablespace_Name
626 +============================================================================*/
627 FUNCTION Get_Tablespace_Name (
628 x_tablespace_type IN VARCHAR2
629 ) RETURN VARCHAR2 IS
630 h_ts_exists VARCHAR2(10) := NULL;
631 h_tbs_name VARCHAR2(80) := NULL;
632
633 l_new_tbs_mode VARCHAR2(10) := NULL;
634 l_tablespace_type VARCHAR2(100);
635 BEGIN
636 -- This is the classification:
637 -- Dimension Tables TRANSACTION_TABLES
638 -- Dimension Tables Indexes TRANSACTION_INDEXES
639 -- Input Tables INTERFACE
640 -- Input Tables Indexes INTERFACE
641 -- Base Tables SUMMARY
642 -- Base Tables Indexes SUMMARY
643 -- Summary Tables SUMMARY
644 -- Summary Tables Indexes SUMMARY
645 -- Other Tables TRANSACTION_TABLES
646 -- Other Indexes TRANSACTION_INDEXES
647
648
649 -- P1 5162628 - check tablespace mode
650 -- If its old mode,
651 -- Summary Tables must be created in the BSCD schema
652 -- Got an OK from Pavel that its ok to create MVs in BSCD schema for old
653 l_tablespace_type := x_tablespace_type;
654
655 AD_TSPACE_UTIL.is_new_ts_mode(l_new_tbs_mode);
656 IF (l_new_tbs_mode = 'Y') THEN -- NEW MODE, no changes
657 null;
658 ELSE -- OLD MODE
659 --See if we need to switch summary tables to Transaction table space
660 IF (l_tablespace_type = base_table_tbs_type OR
661 l_tablespace_type = base_index_tbs_type OR
662 l_tablespace_type = summary_table_tbs_type OR
663 l_tablespace_type = summary_index_tbs_type )
664 THEN
665 -- Switch summary tables to transaction tables
669 IF l_tablespace_type = dimension_table_tbs_type THEN
666 l_tablespace_type := other_table_tbs_type;
667 END IF;
668 END IF;
670 IF dimension_table_tbs_name IS NULL THEN
671 AD_TSPACE_UTIL.get_tablespace_name(
672 x_product_short_name => bsc_apps_short_name,
673 x_tablespace_type => 'TRANSACTION_TABLES',
674 x_validate_ts_exists => 'N',
675 x_ts_exists => h_ts_exists,
676 x_tablespace => dimension_table_tbs_name);
677 END IF;
678 RETURN dimension_table_tbs_name;
679
680 ELSIF l_tablespace_type = dimension_index_tbs_type THEN
681 IF dimension_index_tbs_name IS NULL THEN
682 AD_TSPACE_UTIL.get_tablespace_name(
683 x_product_short_name => bsc_apps_short_name,
684 x_tablespace_type => 'TRANSACTION_INDEXES',
685 x_validate_ts_exists => 'N',
686 x_ts_exists => h_ts_exists,
687 x_tablespace => dimension_index_tbs_name);
688 END IF;
689 RETURN dimension_index_tbs_name;
690
691 ELSIF l_tablespace_type = input_table_tbs_type THEN
692 IF input_table_tbs_name IS NULL THEN
693 AD_TSPACE_UTIL.get_tablespace_name(
694 x_product_short_name => bsc_apps_short_name,
695 x_tablespace_type => 'INTERFACE',
696 x_validate_ts_exists => 'N',
697 x_ts_exists => h_ts_exists,
698 x_tablespace => input_table_tbs_name);
699 END IF;
700 RETURN input_table_tbs_name;
701
702 ELSIF l_tablespace_type = input_index_tbs_type THEN
703 IF input_index_tbs_name IS NULL THEN
704 AD_TSPACE_UTIL.get_tablespace_name(
705 x_product_short_name => bsc_apps_short_name,
706 x_tablespace_type => 'INTERFACE',
707 x_validate_ts_exists => 'N',
708 x_ts_exists => h_ts_exists,
709 x_tablespace => input_index_tbs_name);
710 END IF;
711 RETURN input_index_tbs_name;
712
713 ELSIF l_tablespace_type = base_table_tbs_type THEN
714 IF base_table_tbs_name IS NULL THEN
715 AD_TSPACE_UTIL.get_tablespace_name(
716 x_product_short_name => bsc_apps_short_name,
717 x_tablespace_type => 'SUMMARY',
718 x_validate_ts_exists => 'N',
719 x_ts_exists => h_ts_exists,
720 x_tablespace => base_table_tbs_name);
721 END IF;
722 RETURN base_table_tbs_name;
723
724 ELSIF l_tablespace_type = base_index_tbs_type THEN
725 IF base_index_tbs_name IS NULL THEN
726 AD_TSPACE_UTIL.get_tablespace_name(
727 x_product_short_name => bsc_apps_short_name,
728 x_tablespace_type => 'SUMMARY',
729 x_validate_ts_exists => 'N',
730 x_ts_exists => h_ts_exists,
731 x_tablespace => base_index_tbs_name);
732 END IF;
733 RETURN base_index_tbs_name;
734
735 ELSIF l_tablespace_type = summary_table_tbs_type THEN
736 IF summary_table_tbs_name IS NULL THEN
737 AD_TSPACE_UTIL.get_tablespace_name(
738 x_product_short_name => bsc_apps_short_name,
739 x_tablespace_type => 'SUMMARY',
740 x_validate_ts_exists => 'N',
741 x_ts_exists => h_ts_exists,
742 x_tablespace => summary_table_tbs_name);
743 END IF;
744 RETURN summary_table_tbs_name;
745
746 ELSIF l_tablespace_type = summary_index_tbs_type THEN
747 IF summary_index_tbs_name IS NULL THEN
748 AD_TSPACE_UTIL.get_tablespace_name(
749 x_product_short_name => bsc_apps_short_name,
750 x_tablespace_type => 'SUMMARY',
751 x_validate_ts_exists => 'N',
752 x_ts_exists => h_ts_exists,
753 x_tablespace => summary_index_tbs_name);
754 END IF;
755 RETURN summary_index_tbs_name;
756
757 ELSIF l_tablespace_type = other_table_tbs_type THEN
758 IF other_table_tbs_name IS NULL THEN
759 AD_TSPACE_UTIL.get_tablespace_name(
760 x_product_short_name => bsc_apps_short_name,
761 x_tablespace_type => 'TRANSACTION_TABLES',
762 x_validate_ts_exists => 'N',
763 x_ts_exists => h_ts_exists,
764 x_tablespace => other_table_tbs_name);
765 END IF;
766 RETURN other_table_tbs_name;
767
768 ELSIF l_tablespace_type = other_index_tbs_type THEN
769 IF other_index_tbs_name IS NULL THEN
770 AD_TSPACE_UTIL.get_tablespace_name(
771 x_product_short_name => bsc_apps_short_name,
772 x_tablespace_type => 'TRANSACTION_INDEXES',
773 x_validate_ts_exists => 'N',
774 x_ts_exists => h_ts_exists,
775 x_tablespace => other_index_tbs_name);
776 END IF;
777 RETURN other_index_tbs_name;
778
779 ELSE
780 -- use TRANSACTION_TABLES
781 AD_TSPACE_UTIL.get_tablespace_name(
782 x_product_short_name => bsc_apps_short_name,
783 x_tablespace_type => 'TRANSACTION_TABLES',
787 RETURN h_tbs_name;
784 x_validate_ts_exists => 'N',
785 x_ts_exists => h_ts_exists,
786 x_tablespace => h_tbs_name);
788 END IF;
789
790 END Get_Tablespace_Name;
791
792
793 /*===========================================================================+
794 | PROCEDURE Execute_DDL
795 +============================================================================*/
796 PROCEDURE Execute_DDL(
797 x_statement IN VARCHAR2
798 ) IS
799
800 --h_cursor INTEGER;
801 --h_ret INTEGER;
802
803 BEGIN
804 --h_cursor := DBMS_SQL.OPEN_CURSOR;
805 --DBMS_SQL.PARSE(h_cursor, x_statement, DBMS_SQL.NATIVE);
806 --h_ret := DBMS_SQL.EXECUTE(h_cursor);
807 --DBMS_SQL.CLOSE_CURSOR(h_cursor);
808
809 EXECUTE IMMEDIATE x_statement;
810
811 END Execute_DDL;
812
813
814 /*===========================================================================+
815 | PROCEDURE Execute_DDL_Stmts_AT
816 +============================================================================*/
817 PROCEDURE Execute_DDL_Stmts_AT(
818 x_array_ddl_stmts IN t_array_ddl_stmts,
819 x_num_ddl_stmts IN NUMBER,
820 x_fnd_apps_schema IN VARCHAR2,
821 x_bsc_apps_short_name IN VARCHAR2
822 ) IS
823
824 h_i NUMBER;
825
826 BEGIN
827 FOR h_i IN 1..x_num_ddl_stmts LOOP
828 Do_DDL_AT(x_array_ddl_stmts(h_i).sql_stmt,
829 x_array_ddl_stmts(h_i).stmt_type,
830 x_array_ddl_stmts(h_i).object_name,
831 x_fnd_apps_schema,
832 x_bsc_apps_short_name);
833 END LOOP;
834 END Execute_DDL_Stmts_AT;
835
836
837 /*===========================================================================+
838 | PROCEDURE Execute_Immediate
839 +============================================================================*/
840 PROCEDURE Execute_Immediate(
841 x_sql IN VARCHAR2
842 ) IS
843 --h_cursor INTEGER;
844 --h_ret INTEGER;
845
846 BEGIN
847 --h_cursor := DBMS_SQL.OPEN_CURSOR;
848 --DBMS_SQL.PARSE(h_cursor, x_sql, DBMS_SQL.NATIVE);
849 --h_ret := DBMS_SQL.EXECUTE(h_cursor);
850 --DBMS_SQL.CLOSE_CURSOR(h_cursor);
851
852 EXECUTE IMMEDIATE x_sql;
853
854 END Execute_Immediate;
855
856
857
858 /*===========================================================================+
859 | PROCEDURE Init_Big_In_Cond_Table
860 +============================================================================*/
861 PROCEDURE Init_Big_In_Cond_Table IS
862
863 h_sql VARCHAR2(32700) := NULL;
864
865 BEGIN
866 IF NOT Table_Exists('BSC_TMP_BIG_IN_COND') THEN
867 h_sql := 'CREATE TABLE BSC_TMP_BIG_IN_COND ('||
868 'SESSION_ID NUMBER, VARIABLE_ID NUMBER, VALUE_N NUMBER, VALUE_V VARCHAR2(2000)'||
869 ') TABLESPACE '||Get_Tablespace_Name(other_table_tbs_type)||' '||bsc_storage_clause;
870 Do_DDL(h_sql, AD_DDL.CREATE_TABLE, 'BSC_TMP_BIG_IN_COND');
871 END IF;
872
873 IF NOT Index_Exists('BSC_TMP_BIG_IN_COND_N1') THEN
874 h_sql := 'CREATE INDEX BSC_TMP_BIG_IN_COND_N1'||
875 ' ON BSC_TMP_BIG_IN_COND (SESSION_ID, VARIABLE_ID) '||
876 ' TABLESPACE '||Get_Tablespace_Name(other_index_tbs_type)||' '||bsc_storage_clause;
877 Do_DDL(h_sql, AD_DDL.CREATE_INDEX, 'BSC_TMP_BIG_IN_COND_N1');
878 END IF;
879
880 h_sql := 'DELETE FROM BSC_TMP_BIG_IN_COND '||
881 'WHERE SESSION_ID = USERENV(''SESSIONID'')';
882 Execute_Immediate(h_sql);
883
884 END Init_Big_In_Cond_Table;
885
886
887 /*===========================================================================+
888 | PROCEDURE Init_Bsc_Apps
889 +============================================================================*/
890 PROCEDURE Init_Bsc_Apps IS
891
892 CURSOR c_apps_table IS
893 SELECT
894 object_name
895 FROM
896 user_objects
897 WHERE
898 object_name = 'FND_USER';
899
900 h_table VARCHAR2(30);
901 h_status VARCHAR2(2000);
902 h_industry VARCHAR2(2000);
903 h_x BOOLEAN;
904
905 h_nextext_propcode VARCHAR2(30) := 'NEXT_EXTENT';
906 h_next_extent VARCHAR2(90);
907
908 TYPE cursorType IS REF CURSOR;
909 cv cursorType;
910
911 h_sql VARCHAR2(2000);
912 h_tbs_tbl VARCHAR2(30);
913 h_tbs_idx VARCHAR2(30);
914
915 h_sum_level VARCHAR2(100);
916 h_sum_level_prop_code VARCHAR2(20) := 'ADV_SUM_LEVEL';
917
918 BEGIN
919 -- Init apps_env global variable
920 OPEN c_apps_table;
921 FETCH c_apps_table INTO h_table;
922 IF c_apps_table%FOUND THEN
923 apps_env := TRUE;
924 ELSE
925 apps_env := FALSE;
926 END IF;
927 CLOSE c_apps_table;
928
929 --Init bsc_apps_short_name and bsc_apps_schema (Just for APPS environment)
930 IF apps_env THEN
931 bsc_appl_id := 271;
932 bsc_apps_short_name := 'BSC';
933 h_x := FND_INSTALLATION.Get_App_Info(bsc_apps_short_name, h_status, h_industry, bsc_apps_schema);
934
935 fnd_apps_short_name := 'FND';
939 -- Init bsc_storage_clause
936 h_x := FND_INSTALLATION.Get_App_Info(fnd_apps_short_name, h_status, h_industry, fnd_apps_schema);
937 END IF;
938
940 h_sql := 'SELECT property_value FROM bsc_sys_init'||
941 ' WHERE property_code = :1';
942 OPEN cv FOR h_sql USING h_nextext_propcode;
943 FETCH cv INTO h_next_extent;
944 IF cv%NOTFOUND THEN
945 h_next_extent := '1M';
946 END IF;
947 CLOSE cv;
948
949 bsc_storage_clause := 'STORAGE (INITIAL 4K NEXT '||h_next_extent||
950 ' MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0'||
951 ' FREELIST GROUPS 4 FREELISTS 4)'||
952 ' PCTFREE 10 INITRANS 11 MAXTRANS 255';
953
954 -- Init tablespace clause for tables and indexes
955 IF apps_env THEN
956 h_sql := 'SELECT tablespace, index_tablespace'||
957 ' FROM fnd_product_installations'||
958 ' WHERE application_id = 271';
959
960 OPEN cv FOR h_sql;
961 FETCH cv INTO h_tbs_tbl, h_tbs_idx;
962 IF cv%FOUND THEN
963 bsc_tablespace_clause_tbl := 'TABLESPACE '||h_tbs_tbl;
964 bsc_tablespace_clause_idx := 'TABLESPACE '||h_tbs_idx;
965 END IF;
966 CLOSE cv;
967 END IF;
968
969 -- Init apps_user_id
970 IF apps_env THEN
971 h_sql := 'SELECT fnd_global.user_id FROM DUAL';
972 OPEN cv FOR h_sql;
973 FETCH cv INTO apps_user_id;
974 IF cv%NOTFOUND THEN
975 apps_user_id := -1;
976 END IF;
977 CLOSE cv;
978 ELSE
979 -- personal version
980 h_sql := 'SELECT user_id FROM bsc_apps_users WHERE user_name = ''BSCADMIN''';
981 OPEN cv FOR h_sql;
982 FETCH cv INTO apps_user_id;
983 IF cv%NOTFOUND THEN
984 apps_user_id := -1;
985 END IF;
986 CLOSE cv;
987 END IF;
988
989 -- Init bsc_mv global variable
990 -- Read sum level from bsc_sys_init
991 h_sql := 'SELECT property_value'||
992 ' FROM bsc_sys_init'||
993 ' WHERE property_code = :1';
994 OPEN cv FOR h_sql USING h_sum_level_prop_code;
995 FETCH cv INTO h_sum_level;
996 IF cv%NOTFOUND THEN
997 bsc_mv := FALSE;
998 ELSE
999 IF h_sum_level IS NULL THEN
1000 bsc_mv := FALSE;
1001 ELSE
1002 bsc_mv := TRUE;
1003 END IF;
1004 END IF;
1005 CLOSE cv;
1006
1007 END Init_Bsc_Apps;
1008
1009
1010 /*===========================================================================+
1011 | FUNCTION Init_Log_File
1012 +============================================================================*/
1013 FUNCTION Init_Log_File (
1014 x_log_file_name IN VARCHAR2,
1015 x_error_msg OUT NOCOPY VARCHAR2
1016 ) RETURN BOOLEAN IS
1017
1018 e_unexpected_error EXCEPTION;
1019 e_no_log_file_dir EXCEPTION;
1020
1021 h_log_file_dir VARCHAR2(60);
1022 h_log_file_handle UTL_FILE.FILE_TYPE;
1023
1024 CURSOR c_utl_file_dir IS
1025 SELECT
1026 VP.value
1027 FROM
1028 v$parameter VP
1029 WHERE
1030 UPPER(VP.name) = 'UTL_FILE_DIR';
1031
1032 h_utl_file_dir VARCHAR2(2000);
1033
1034 BEGIN
1035
1036 IF apps_env THEN
1037 -- APPS environment (concurrent program)
1038
1039 FND_FILE.Put_Line(FND_FILE.LOG, '+---------------------------------------------------------------------------+');
1040 FND_FILE.Put_Line(FND_FILE.LOG, 'Oracle Balanced Scorecard: Version : '||c_version);
1041 FND_FILE.Put_Line(FND_FILE.LOG, '');
1042 FND_FILE.Put_Line(FND_FILE.LOG, 'Copyright (c) Oracle Corporation 1999. All rights reserved.');
1043 FND_FILE.Put_Line(FND_FILE.LOG, '+---------------------------------------------------------------------------+');
1044 FND_FILE.Put_Line(FND_FILE.LOG, Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
1045 Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
1046 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time));
1047
1048 FND_FILE.Put_Line(FND_FILE.OUTPUT, '+---------------------------------------------------------------------------+');
1049 FND_FILE.Put_Line(FND_FILE.OUTPUT, 'Oracle Balanced Scorecard: Version : '||c_version);
1050 FND_FILE.Put_Line(FND_FILE.OUTPUT, '');
1051 FND_FILE.Put_Line(FND_FILE.OUTPUT, 'Copyright (c) Oracle Corporation 1999. All rights reserved.');
1052 FND_FILE.Put_Line(FND_FILE.OUTPUT, '+---------------------------------------------------------------------------+');
1053 FND_FILE.Put_Line(FND_FILE.OUTPUT, Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
1054 Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
1055 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time));
1056
1057 ELSE
1058 -- Personal environment
1059
1060 OPEN c_utl_file_dir;
1061 FETCH c_utl_file_dir INTO h_utl_file_dir;
1062 IF c_utl_file_dir%NOTFOUND THEN
1063 h_log_file_dir := NULL;
1064 ELSE
1065 IF h_utl_file_dir IS NULL THEN
1069 h_log_file_dir := SUBSTR(h_utl_file_dir, 1, INSTR(h_utl_file_dir, ',') - 1);
1066 h_log_file_dir := NULL;
1067 ELSE
1068 IF INSTR(h_utl_file_dir, ',') > 0 THEN
1070 ELSE
1071 h_log_file_dir := h_utl_file_dir;
1072 END IF;
1073 END IF;
1074 END IF;
1075 CLOSE c_utl_file_dir;
1076
1077 IF h_log_file_dir IS NULL THEN
1078 RAISE e_no_log_file_dir;
1079 END IF;
1080
1081 h_log_file_handle := UTL_FILE.FOPEN(h_log_file_dir, x_log_file_name, 'a');
1082 UTL_FILE.PUT_LINE(h_log_file_handle, '+---------------------------------------------------------------------------+');
1083 UTL_FILE.PUT_LINE(h_log_file_handle,'Oracle Balanced Scorecard: Version : '||c_version);
1084 UTL_FILE.PUT_LINE(h_log_file_handle, '');
1085 UTL_FILE.PUT_LINE(h_log_file_handle, 'Copyright (c) Oracle Corporation 1999. All rights reserved.');
1086 UTL_FILE.PUT_LINE(h_log_file_handle, '+---------------------------------------------------------------------------+');
1087 UTL_FILE.PUT_LINE(h_log_file_handle, Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
1088 Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
1089 ' '||TO_CHAR(SYSDATE, c_fto_long_date_time));
1090 UTL_FILE.FCLOSE(h_log_file_handle);
1091
1092 g_log_file_name := x_log_file_name;
1093 g_log_file_dir := h_log_file_dir;
1094
1095 END IF;
1096
1097 RETURN TRUE;
1098
1099 EXCEPTION
1100 WHEN e_unexpected_error THEN
1101 x_error_msg := Get_Message('BSC_LOGFILE_CREATION_FAILED');
1102 RETURN FALSE;
1103
1104 WHEN e_no_log_file_dir THEN
1105 x_error_msg := Get_Message('BSC_LOGFILE_DIR_NOT_SPECIFIED');
1106 RETURN FALSE;
1107
1108 WHEN UTL_FILE.INVALID_PATH THEN
1109 x_error_msg := Get_Message('BSC_LOGFILE_PATH_FAILED');
1110 RETURN FALSE;
1111
1112 WHEN UTL_FILE.INVALID_MODE THEN
1113 x_error_msg := Get_Message('BSC_LOGFILE_MODE_FAILED');
1114 RETURN FALSE;
1115
1116 WHEN UTL_FILE.INVALID_OPERATION THEN
1117 x_error_msg := Get_Message('BSC_LOGFILE_OPERATION_FAILED');
1118 RETURN FALSE;
1119
1120 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1121 x_error_msg := Get_Message('BSC_LOGFILE_HANDLE_FAILED');
1122 RETURN FALSE;
1123
1124 WHEN UTL_FILE.WRITE_ERROR THEN
1125 x_error_msg := Get_Message('BSC_WRITE_LOGFILE_FAILED');
1126 RETURN FALSE;
1127
1128 WHEN OTHERS THEN
1129 x_error_msg := SQLERRM;
1130 RETURN FALSE;
1131
1132 END Init_Log_File;
1133
1134
1135 /*===========================================================================+
1136 | FUNCTION Log_File_Dir
1137 +============================================================================*/
1138 FUNCTION Log_File_Dir RETURN VARCHAR2 IS
1139 BEGIN
1140 RETURN g_log_file_dir;
1141
1142 END Log_File_Dir;
1143
1144
1145 /*===========================================================================+
1146 | FUNCTION Log_File_Name
1147 +============================================================================*/
1148 FUNCTION Log_File_Name RETURN VARCHAR2 IS
1149 BEGIN
1150 RETURN g_log_file_name;
1151
1152 END Log_File_Name;
1153
1154
1155 /*===========================================================================+
1156 | FUNCTION Object_Exists
1157 +============================================================================*/
1158 FUNCTION Object_Exists(
1159 x_object IN VARCHAR2
1160 ) RETURN BOOLEAN IS
1161
1162 CURSOR get_obj IS
1163 SELECT object_name FROM user_objects
1164 WHERE object_name = upper(x_object);
1165
1166 h_object VARCHAR2(130);
1167
1168 BEGIN
1169 -- This function looks into USER_OBJECTS. for APPS and Personal.
1170 OPEN get_obj;
1171 FETCH get_obj INTO h_object;
1172 IF get_obj%NOTFOUND THEN
1173 CLOSE get_obj;
1174 RETURN FALSE;
1175 END IF;
1176 CLOSE get_obj;
1177
1178 RETURN TRUE;
1179 END Object_Exists;
1180
1181
1182 /*===========================================================================+
1183 |
1184 | Name: Replace_Token
1185 |
1186 | Description: This function returns the message replacin the given token.
1187 |
1188 | Notes:
1189 |
1190 +============================================================================*/
1191 FUNCTION Replace_Token(
1192 x_message IN VARCHAR2,
1193 x_token_name IN VARCHAR2,
1194 x_token_value IN VARCHAR2
1195 ) RETURN VARCHAR2 IS
1196
1197 h_message VARCHAR2(4000);
1198
1199 BEGIN
1200 h_message := REPLACE(x_message, '&'||x_token_name, x_token_value);
1201 RETURN h_message;
1202 END Replace_Token;
1203
1204
1205 /*===========================================================================+
1206 | PROCEDURE Submit_Request_VB
1207 +============================================================================*/
1208 PROCEDURE Submit_Request_VB(
1209 x_program IN VARCHAR2,
1213 x_argument3 IN VARCHAR2 DEFAULT NULL,
1210 x_start_time IN VARCHAR2 DEFAULT NULL,
1211 x_argument1 IN VARCHAR2 DEFAULT NULL,
1212 x_argument2 IN VARCHAR2 DEFAULT NULL,
1214 x_argument4 IN VARCHAR2 DEFAULT NULL,
1215 x_argument5 IN VARCHAR2 DEFAULT NULL,
1216 x_argument6 IN VARCHAR2 DEFAULT NULL,
1217 x_argument7 IN VARCHAR2 DEFAULT NULL,
1218 x_argument8 IN VARCHAR2 DEFAULT NULL,
1219 x_argument9 IN VARCHAR2 DEFAULT NULL,
1220 x_argument10 IN VARCHAR2 DEFAULT NULL,
1221 x_argument11 IN VARCHAR2 DEFAULT NULL,
1222 x_argument12 IN VARCHAR2 DEFAULT NULL,
1223 x_argument13 IN VARCHAR2 DEFAULT NULL,
1224 x_argument14 IN VARCHAR2 DEFAULT NULL,
1225 x_argument15 IN VARCHAR2 DEFAULT NULL,
1226 x_argument16 IN VARCHAR2 DEFAULT NULL,
1227 x_argument17 IN VARCHAR2 DEFAULT NULL,
1228 x_argument18 IN VARCHAR2 DEFAULT NULL,
1229 x_argument19 IN VARCHAR2 DEFAULT NULL,
1230 x_argument20 IN VARCHAR2 DEFAULT NULL
1231 ) IS
1232
1233 e_request_error EXCEPTION;
1234 h_request_id NUMBER;
1235
1236 BEGIN
1237
1238 h_request_id := 0;
1239
1240 IF UPPER(x_program) = 'BSCLOADER' THEN
1241 h_request_id := FND_REQUEST.Submit_Request(application => bsc_apps_short_name,
1242 program => x_program,
1243 description => NULL,
1244 start_time => x_start_time,
1245 sub_request => FALSE,
1246 argument1 => x_argument1,
1247 argument2 => x_argument2,
1248 argument3 => x_argument3);
1249
1250 ELSIF UPPER(x_program) = 'BSC_MIGRATION_PROC' THEN
1251 h_request_id := FND_REQUEST.Submit_Request(application => bsc_apps_short_name,
1252 program => x_program,
1253 description => NULL,
1254 start_time => x_start_time,
1255 sub_request => FALSE,
1256 argument1 => x_argument1,
1257 argument2 => x_argument2,
1258 argument3 => x_argument3,
1259 argument4 => x_argument4,
1260 argument5 => x_argument5);
1261 END IF;
1262
1263 IF h_request_id = 0 THEN
1264 RAISE e_request_error;
1265 END IF;
1266
1267 -- Insert the request_id in BSC_MESSAGE_LOGS table (type = 3, information) to VB program
1268 -- be able to get it.
1269
1270 BSC_MESSAGE.Add(x_message => h_request_id,
1271 x_source => 'BSC_APPS.Submit_Request_VB',
1272 x_type => 3,
1273 x_mode => 'I');
1274 COMMIT;
1275
1276 EXCEPTION
1277 WHEN e_request_error THEN
1278 BSC_MESSAGE.Add(x_message => Get_Message('BSC_SUBMMITREQ_FAILED'),
1279 x_source => 'BSC_APPS.Submit_Request_VB',
1280 x_mode => 'I');
1281 COMMIT;
1282
1283 WHEN OTHERS THEN
1284 BSC_MESSAGE.Add(x_message => SQLERRM,
1285 x_source => 'BSC_APPS.Submit_Request_VB',
1286 x_mode => 'I');
1287 COMMIT;
1288
1289 END Submit_Request_VB;
1290
1291
1292 /*===========================================================================+
1293 | FUNCTION Index_Exists |
1294 +============================================================================*/
1295 FUNCTION Index_Exists(
1296 x_index IN VARCHAR2
1297 ) RETURN BOOLEAN IS
1298
1299 CURSOR get_index IS
1300 SELECT index_name FROM USER_INDEXES
1301 WHERE index_name = upper(x_index);
1302
1303 CURSOR get_index_apps IS
1304 SELECT index_name FROM ALL_INDEXES
1305 WHERE index_name = upper(x_index)
1306 AND owner = upper(bsc_apps_schema);
1307
1308 h_idx VARCHAR2(30);
1309
1310 BEGIN
1311 IF NOT apps_env THEN
1312 -- Personal
1313 OPEN get_index;
1314 FETCH get_index INTO h_idx;
1315 IF get_index%NOTFOUND THEN
1316 CLOSE get_index;
1317 RETURN FALSE;
1318 END IF;
1319 CLOSE get_index;
1320 ELSE
1321 -- APPS
1322 OPEN get_index_apps;
1323 FETCH get_index_apps INTO h_idx;
1324 IF get_index_apps%NOTFOUND THEN
1325 CLOSE get_index_apps;
1326 RETURN FALSE;
1327 END IF;
1328 CLOSE get_index_apps;
1329 END IF;
1330
1331 RETURN TRUE;
1332 END Index_Exists;
1333
1334
1335 /*===========================================================================+
1336 | FUNCTION Table_Exists |
1337 +============================================================================*/
1338 FUNCTION Table_Exists(
1339 x_table IN VARCHAR2
1340 ) RETURN BOOLEAN IS
1341
1342 CURSOR get_table IS
1343 SELECT table_name FROM USER_TABLES
1344 WHERE table_name = upper(x_table);
1345
1346 CURSOR get_table_apps IS
1350
1347 SELECT table_name FROM ALL_TABLES
1348 WHERE table_name = upper(x_table)
1349 AND owner = upper(bsc_apps_schema);
1351 h_tbl VARCHAR2(30);
1352
1353 BEGIN
1354 IF NOT apps_env THEN
1355 -- Personal
1356 OPEN get_table;
1357 FETCH get_table INTO h_tbl;
1358 IF get_table%NOTFOUND THEN
1359 CLOSE get_table;
1360 RETURN FALSE;
1361 END IF;
1362 CLOSE get_table;
1363 ELSE
1364 -- APPS
1365 OPEN get_table_apps;
1366 FETCH get_table_apps INTO h_tbl;
1367 IF get_table_apps%NOTFOUND THEN
1368 CLOSE get_table_apps;
1369 RETURN FALSE;
1370 END IF;
1371 CLOSE get_table_apps;
1372 END IF;
1373
1374 RETURN TRUE;
1375 END Table_Exists;
1376
1377
1378 /*===========================================================================+
1379 | FUNCTION View_Exists |
1380 +============================================================================*/
1381 FUNCTION View_Exists(
1382 x_view IN VARCHAR2
1383 ) RETURN BOOLEAN IS
1384
1385 CURSOR get_view IS
1386 SELECT view_name FROM user_views
1387 WHERE view_name = upper(x_view);
1388
1389 h_view VARCHAR2(30);
1390
1391 BEGIN
1392 -- In both, APPS and Personal, the view are installed in the current
1393 -- schema. So we dont need to user all_views table for APPS.
1394
1395 OPEN get_view;
1396 FETCH get_view INTO h_view;
1397 IF get_view%NOTFOUND THEN
1398 CLOSE get_view;
1399 RETURN FALSE;
1400 END IF;
1401 CLOSE get_view;
1402
1403 RETURN TRUE;
1404 END View_Exists;
1405
1406
1407 /*===========================================================================+
1408 | PROCEDURE Wait_For_Request_VB
1409 +============================================================================*/
1410 PROCEDURE Wait_For_Request_VB(
1411 x_request_id IN NUMBER,
1412 x_interval IN NUMBER,
1413 x_max_wait IN NUMBER
1414 ) IS
1415
1416 e_wait_error EXCEPTION;
1417
1418 h_phase VARCHAR2(32000) := NULL;
1419 h_status VARCHAR2(2000) := NULL;
1420 h_dev_phase VARCHAR2(2000) := NULL;
1421 h_dev_status VARCHAR2(2000) := NULL;
1422 h_message VARCHAR2(2000) := NULL;
1423
1424 BEGIN
1425 IF NOT FND_CONCURRENT.Wait_For_Request(x_request_id,
1426 x_interval,
1427 x_max_wait,
1428 h_phase,
1429 h_status,
1430 h_dev_phase,
1431 h_dev_status,
1432 h_message) THEN
1433 RAISE e_wait_error;
1434 END IF;
1435
1436 COMMIT;
1437
1438 EXCEPTION
1439 WHEN e_wait_error THEN
1440 BSC_MESSAGE.Add(x_message => Get_Message('BSC_WAITREQ_FAILED'),
1441 x_source => 'BSC_APPS.Wait_For_Request_VB',
1442 x_mode => 'I');
1443 COMMIT;
1444
1445 WHEN OTHERS THEN
1446 BSC_MESSAGE.Add(x_message => SQLERRM,
1447 x_source => 'BSC_APPS.Wait_For_Request_VB',
1448 x_mode => 'I');
1449 COMMIT;
1450 END Wait_For_Request_VB;
1451
1452
1453
1454 /*===========================================================================+
1455 | PROCEDURE Write_Errors_To_Log
1456 +============================================================================*/
1457 PROCEDURE Write_Errors_To_Log IS
1458 CURSOR c_messages IS
1459 SELECT
1460 message
1461 FROM
1462 bsc_message_logs
1463 WHERE
1464 last_update_login = USERENV('SESSIONID')
1465 ORDER BY
1466 last_update_date;
1467
1468 h_message bsc_message_logs.message%TYPE;
1469
1470 BEGIN
1471 OPEN c_messages;
1472 FETCH c_messages INTO h_message;
1473
1474 WHILE c_messages%FOUND LOOP
1475 Write_Line_Log(h_message, LOG_FILE);
1476 FETCH c_messages INTO h_message;
1477 END LOOP;
1478
1479 CLOSE c_messages;
1480
1481 END Write_Errors_To_Log;
1482
1483
1484 /*===========================================================================+
1485 | PROCEDURE Write_Line_Log
1486 +============================================================================*/
1487 PROCEDURE Write_Line_Log (
1488 x_line IN VARCHAR2,
1489 x_which IN NUMBER
1490 ) IS
1491
1492 h_log_file_handle UTL_FILE.FILE_TYPE;
1493 h_which NUMBER;
1494
1495 h_line VARCHAR2(32700);
1496
1497 BEGIN
1498 h_line := TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS')||' '||x_line;
1499
1500 IF apps_env THEN
1501 -- APPS environment (concurrent program)
1502
1503 -- Due to some issue, when there is an error in the program
1504 -- the output file is not saved, i am going to write out NOCOPY put to log
1505 -- file also.
1506
1507 IF x_which = LOG_FILE THEN
1508 FND_FILE.Put_Line(FND_FILE.LOG, x_line);
1509 ELSE
1510 FND_FILE.Put_Line(FND_FILE.OUTPUT, x_line);
1511 FND_FILE.Put_Line(FND_FILE.LOG, x_line);
1512 END IF;
1513
1514 ELSE
1515 -- Personal environment
1516 IF g_log_file_name IS NOT NULL THEN
1517 h_log_file_handle := UTL_FILE.FOPEN(g_log_file_dir, g_log_file_name, 'a');
1518
1519 UTL_FILE.PUT_LINE(h_log_file_handle, x_line);
1520 UTL_FILE.FCLOSE(h_log_file_handle);
1521 END IF;
1522 END IF;
1523
1524 END Write_Line_Log;
1525 /*===========================================================================+
1526 | Name: Is_Bsc_User_Valid
1527 |
1528 | Description: This function return 1, if the user_id is a Valid BSc user.
1529 | It means that has BSC responsibility and the resp. is between
1530 | start and end date
1531 | Notes:
1532 | Rewrote the API using a simple Static SQL for the API for
1533 | Performance Bug #3236356
1534 +============================================================================*/
1535 FUNCTION Is_Bsc_User_Valid (
1536 x_User_Id IN NUMBER
1537 ) RETURN NUMBER IS
1538 l_count NUMBER;
1539 l_is_Valid_User NUMBER;
1540 BEGIN
1541
1542 l_is_Valid_User := 0;
1543 l_count := 0;
1544
1545 SELECT COUNT(0)
1546 INTO l_count
1547 FROM FND_USER fu, FND_RESPONSIBILITY fr,FND_USER_RESP_GROUPS fur
1548 WHERE fu.User_Id = x_User_Id
1549 AND fu.User_Id = fur.User_Id
1550 AND fr.Responsibility_Id = fur.Responsibility_Id
1551 AND fr.Application_Id = fur.Responsibility_Application_Id
1552 AND fr.Application_Id = 271
1553 AND SYSDATE BETWEEN fu.Start_Date AND NVL(fu.End_Date, SYSDATE)
1554 AND SYSDATE BETWEEN fr.Start_Date AND NVL(fr.End_Date, SYSDATE);
1555
1556 IF l_count > 0 THEN
1557 l_is_Valid_User := 1;
1558 END IF;
1559
1560 RETURN l_is_Valid_User;
1561
1562 EXCEPTION
1563 WHEN OTHERS THEN
1564
1565 l_is_Valid_User := 0;
1566 RETURN l_is_Valid_User;
1567
1568 END Is_Bsc_User_Valid;
1569
1570 /*===========================================================================+
1571 | Name: Is_Bsc_Design_User_Valid
1572 |
1573 | Description: This function return 1, if the user_id is a Valid BSC design user.
1574 | It means that user has BSC Manager and Performnace Management Designer
1575 | and the resp. is between start and end date
1576 | Notes:
1577 |
1578 +============================================================================*/
1579 FUNCTION Is_Bsc_Design_User_Valid (
1580 x_User_Id IN NUMBER
1581 ) RETURN NUMBER IS
1582 l_count NUMBER;
1583 l_is_Valid_Design_User NUMBER;
1584 BEGIN
1585
1586 l_is_Valid_Design_User := 0;
1587 l_count := 0;
1588
1589 SELECT COUNT(0)
1590 INTO l_count
1591 FROM FND_USER fu, FND_RESPONSIBILITY fr,FND_USER_RESP_GROUPS fur
1592 WHERE fu.User_Id = x_User_Id
1593 AND fu.User_Id = fur.User_Id
1594 AND (fr.responsibility_key = 'BSC_DESIGNER' OR fr.responsibility_key = 'BSC_Manager')
1595 AND fr.Responsibility_Id = fur.Responsibility_Id
1596 AND fr.Application_Id = fur.Responsibility_Application_Id
1597 AND fr.Application_Id = 271
1598 AND SYSDATE BETWEEN fu.Start_Date AND NVL(fu.End_Date, SYSDATE)
1599 AND SYSDATE BETWEEN fr.Start_Date AND NVL(fr.End_Date, SYSDATE)
1603 l_is_Valid_Design_User := 1;
1600 AND SYSDATE BETWEEN fur.Start_Date AND NVL(fur.End_Date, SYSDATE);
1601
1602 IF l_count > 0 THEN
1604 END IF;
1605
1606 RETURN l_is_Valid_Design_User;
1607
1608 EXCEPTION
1609 WHEN OTHERS THEN
1610
1611 l_is_Valid_Design_User := 0;
1612 RETURN l_is_Valid_Design_User;
1613
1614 END Is_Bsc_Design_User_Valid;
1615
1616 /*===========================================================================+
1617 | Name: GET_USER_FULL_NAME
1618 |
1619 | Description: This fucntion return teh full name of the user from
1620 | per_all_people_f table
1621 | Notes: Revamped API to call static cursor for Bug 3236356
1622 +============================================================================*/
1623 FUNCTION Get_User_Full_Name (
1624 x_User_Id in NUMBER
1625 ) RETURN VARCHAR2 is
1626 l_name VARCHAR2(240) := '';
1627
1628 CURSOR c_Full_Name IS
1629 SELECT P.FULL_NAME
1630 FROM PER_ALL_PEOPLE_F P, FND_USER F
1631 WHERE P.PERSON_ID = F.EMPLOYEE_ID
1632 AND F.USER_ID = x_User_Id;
1633
1634 BEGIN
1635
1636 -- bug 2558075 get full name using empploye id
1637
1638 OPEN c_Full_Name;
1639 FETCH c_Full_Name INTO l_name;
1640 IF c_Full_Name%NOTFOUND THEN
1641 l_name := '';
1642 END IF;
1643 CLOSE c_Full_Name;
1644
1645
1646 RETURN l_name;
1647
1648 EXCEPTION
1649 WHEN OTHERS THEN
1650
1651 IF c_Full_Name%ISOPEN THEN
1652 CLOSE c_Full_Name;
1653 END IF;
1654
1655 RETURN l_name;
1656
1657 END Get_User_Full_Name;
1658
1659
1660 /*===========================================================================+
1661 | Name: get_user_schema
1662 |
1663 | Description: The fuction return the BSC schema name
1664 |
1665 | Notes:
1666 +============================================================================*/
1667
1668 FUNCTION get_user_schema
1669 RETURN VARCHAR2 IS
1670
1671 BEGIN
1672 -- BSC SCHEMA NAME
1673 RETURN get_user_schema (g_bsc_user_schema);
1674
1675 END get_user_schema;
1676
1677
1678 /*===========================================================================+
1679 | Name: get_user_schema (over loaded)
1680 |
1681 | Description: The function return the schema name for the application
1682 | short name passed
1683 | Notes:
1684 +============================================================================*/
1685
1686 FUNCTION get_user_schema ( p_app_short_name IN varchar2 )
1687 RETURN VARCHAR2 IS
1688
1689 l_status varchar2(1);
1690 l_industry varchar2(1);
1691 l_oracle_schema varchar2(30);
1692 l_return boolean;
1693 l_app_short_name VARCHAR2(30);
1694
1695 CURSOR c_get_apps_schema is
1696 SELECT oracle_username
1697 FROM fnd_oracle_userid
1698 WHERE oracle_id between 900 and 999;
1699
1700 /* In 11i on any env this query will always return
1701 one row */
1702
1703 BEGIN
1704 l_return := FALSE;
1705
1706 BEGIN
1707 l_oracle_schema := user_schema_tbl(UPPER(p_app_short_name)); --First check if the value is there on cache
1708 l_return := TRUE;
1709 EXCEPTION
1710 WHEN NO_DATA_FOUND THEN
1711 l_oracle_schema := NULL;
1712 END;
1713
1714 --dbms_output.put_line(' GOT l_oracle_schema FROM CACHE:-' || l_oracle_schema);
1715
1716 IF(l_oracle_schema IS NULL) THEN
1717 l_app_short_name := UPPER(p_app_short_name);
1718 IF ( l_app_short_name = g_apps_user_schema ) THEN
1719 IF ( c_get_apps_schema%ISOPEN ) THEN
1720 CLOSE c_get_apps_schema;
1721 END IF;
1722
1723 OPEN c_get_apps_schema;
1724 FETCH c_get_apps_schema into l_oracle_schema;
1725 CLOSE c_get_apps_schema;
1726 l_return := TRUE;
1727 ELSE
1728 l_return := FND_INSTALLATION.get_app_info
1729 ( application_short_name => l_app_short_name
1730 , status => l_status
1731 , industry => l_industry
1732 , oracle_schema => l_oracle_schema
1733 );
1734 END IF;
1735 END IF;
1736
1737 IF l_return THEN
1738 user_schema_tbl(UPPER(p_app_short_name)) := l_oracle_schema; --Do lazy cache
1739 RETURN l_oracle_schema;
1740 ELSE
1741 RAISE FND_API.G_EXC_ERROR;
1742 END IF;
1743
1744 EXCEPTION
1745 WHEN OTHERS THEN
1746 RAISE;
1747
1748 END get_user_schema;
1749
1750 /*===========================================================================+
1751 | PROCEDURE Do_DDL_AT, Use to execute multiple statements under single
1752 | Autonomous Transaction.
1753 | Fixed Bug #3541933
1754 +============================================================================*/
1755 PROCEDURE Do_DDL_AT(
1756 x_Statements_Tbl IN BSC_APPS.Autonomous_Statements_Tbl_Type
1757 ) IS
1758 PRAGMA AUTONOMOUS_TRANSACTION;
1759 BEGIN
1760 FOR i IN 0..(x_Statements_Tbl.COUNT-1) LOOP
1761 IF ((x_Statements_Tbl(i).x_statement_type IS NULL) OR (x_Statements_Tbl(i).x_Object_Name IS NULL)) THEN
1762 EXECUTE IMMEDIATE x_Statements_Tbl(i).x_statement;
1763 ELSE
1764 AD_DDL.Do_DDL
1765 ( Applsys_Schema => NVL(x_Statements_Tbl(i).x_Fnd_Apps_Schema, BSC_APPS.Fnd_Apps_Schema)
1769 , Object_Name => x_Statements_Tbl(i).x_Object_Name
1766 , Application_Short_Name => NVL(x_Statements_Tbl(i).x_Bsc_Apps_Short_Name, BSC_APPS.Bsc_Apps_Short_Name)
1767 , Statement_Type => x_Statements_Tbl(i).x_Statement_Type
1768 , Statement => x_Statements_Tbl(i).x_Statement
1770 );
1771 END IF;
1772 END LOOP;
1773 END Do_DDL_AT;
1774
1775 /*===========================================================================+
1776 | FUNCTION Get_Bsc_Message
1777 +============================================================================*/
1778 FUNCTION Get_Bsc_Message
1779 ( p_Message_Name IN VARCHAR2,
1780 p_Token_Names IN VARCHAR2,
1781 p_Lookup_Codes IN VARCHAR2,
1782 p_Lookup_Types IN VARCHAR2
1783 ) RETURN VARCHAR2
1784 IS
1785 l_msg_count NUMBER;
1786 l_msg_data VARCHAR2(2000);
1787
1788 l_Lookup_Types VARCHAR2(2000);
1789 l_Lookup_Codes VARCHAR2(2000);
1790 l_Token_Names VARCHAR2(2000);
1791
1792 l_Lookup_Type VARCHAR2(100);
1793 l_Lookup_Code VARCHAR2(100);
1794 l_Token_Name VARCHAR2(100);
1795 BEGIN
1796 FND_MSG_PUB.Initialize;
1797 FND_MESSAGE.SET_NAME('BSC', p_Message_Name);
1798
1799 l_Lookup_Types := TRIM(p_Lookup_Types);
1800 l_Lookup_Codes := TRIM(p_Lookup_Codes);
1801 l_Token_Names := TRIM(p_Token_Names);
1802 IF (l_Token_Names IS NOT NULL) THEN
1803 WHILE (is_more( x_Lookup_Types => l_Lookup_Types
1804 , x_Lookup_Codes => l_Lookup_Codes
1805 , x_Token_Names => l_Token_Names
1806 , x_Lookup_Type => l_Lookup_Type
1807 , x_Lookup_Code => l_Lookup_Code
1808 , x_Token_Name => l_Token_Name
1809 )) LOOP
1810 IF ((l_Lookup_Type IS NULL) OR (UPPER(l_Lookup_Type) = 'NULL')) THEN
1811 FND_MESSAGE.SET_TOKEN(l_Token_Name, l_Lookup_Code);
1812 ELSE
1813 FND_MESSAGE.SET_TOKEN(l_Token_Name, BSC_APPS.Get_Lookup_Value(l_Lookup_Type, l_Lookup_Code), TRUE);
1814 END IF;
1815 END LOOP;
1816 END IF;
1817
1818 FND_MSG_PUB.ADD;
1819 --fetch the message
1820 FND_MSG_PUB.Count_And_Get
1821 ( p_encoded => FND_API.G_FALSE
1822 , p_count => l_msg_count
1823 , p_data => l_msg_data
1824 );
1825 RETURN l_msg_data;
1826 END Get_Bsc_Message;
1827
1828 END BSC_APPS;