1 PACKAGE BIS_UTILITIES_PUB AS
2 /* $Header: BISPUTLS.pls 120.0 2005/06/01 14:34:56 appldev noship $ */
3
4 -- Global constans
5
6 G_SHORT_NAME_LEN Number := 20;
7 G_ERROR VARCHAR2(1) := 'E';
8 G_WARNING VARCHAR2(1) := 'W';
9 G_VALUE_SEPARATOR VARCHAR2(1) := '+';
10 G_SEED_OWNER VARCHAR2(6) := 'SEED';
11 G_CUSTOM_OWNER VARCHAR2(6) := 'CUSTOM';
12 G_SEED_USER_ID NUMBER := 1;
13 G_CUSTOM_USER_ID NUMBER := 0;
14 G_TIME_IS_DEPEN_ON_ORG CONSTANT NUMBER := 1; --2684911
15 G_IS_DEBUG_ON BOOLEAN := FALSE; -- 2694978
16 G_DEBUG_LOG_PROFILE CONSTANT VARCHAR2(30) := 'BIS_PMF_DEBUG'; -- 2694978
17 G_UTL_FILE_DIR CONSTANT VARCHAR2(30) := 'utl_file_dir'; -- 2694978
18 G_NULL_CHAR CONSTANT VARCHAR2(1) := chr(0);
19 G_NULL_NUM CONSTANT NUMBER := 9.99E125;
20 G_NULL_DATE CONSTANT DATE := TO_DATE('1','j');
21
22 -- Added for Bug#3767188
23 -- modified for Bug#3788314
24 G_MEAS_DEFINER_FORM_FUNCTION CONSTANT VARCHAR2(30) := 'BSC_PMD_MD_SELECTMEASURE_PGE';
25 G_BIA_MEAS_DEFINER_FUNCTION CONSTANT VARCHAR2(30) := 'BSC_BID_SELECTMEASURE_PGE';
26 G_ENABLE_AUTOGEN_PROFILE_NAME CONSTANT VARCHAR2(30) := 'BSC_GENERATED_SUMMARIES';
27 G_ENABLE_GEN_SOURCE_REPORT CONSTANT VARCHAR2(30) := 'GEN_SOURCE_RPD';
28
29
30 -- Data Type: Records and Tables
31 TYPE Error_Rec_Type IS RECORD
32 ( Error_Msg_ID Number
33 , Error_Msg_Name VARCHAR2(30)
34 , Error_Description VARCHAR2(2000)
35 , Error_Proc_Name VARCHAR2(100)
36 , Error_Type VARCHAR2(1) := G_ERROR
37 -- mdamle 08/06/2003
38 , Error_Token1 VARCHAR2(30)
39 , Error_Value1 VARCHAR2(2000)
40 , Error_Token2 VARCHAR2(30)
41 , Error_Value2 VARCHAR2(2000)
42 , Error_Token3 VARCHAR2(30)
43 , Error_Value3 VARCHAR2(2000)
44 );
45
46 TYPE Error_Tbl_Type IS TABLE of Error_Rec_Type
47 INDEX BY BINARY_INTEGER;
48
49 TYPE BIS_VARCHAR_TBL IS
50 table OF VARCHAR2(32000) INDEX BY BINARY_INTEGER;
51
52 TYPE Report_Parameter_Rec_Type IS RECORD
53 ( Label VARCHAR2(32767)
54 ,Value VARCHAR2(32767)
55 ,Action VARCHAR2(32767)
56 );
57
58 TYPE Report_Parameter_Tbl_Type IS TABLE OF
59 Report_Parameter_Rec_Type
60 INDEX BY BINARY_INTEGER;
61
62 TYPE TimeLvlList IS VARRAY(50) of VARCHAR2(100);
63
64 E_INVALID_PARENT EXCEPTION ;
65 E_INVALID_USER EXCEPTION ;
66
67
68 Procedure Retrieve_User
69 ( p_user_id IN NUMBER Default G_NULL_NUM
70 , p_user_name IN VARCHAR2 Default G_NULL_CHAR
71 , x_user_id OUT NOCOPY NUMBER
72 , x_user_name OUT NOCOPY VARCHAR2
73 , x_return_status OUT NOCOPY VARCHAR2
74 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
75 );
76
77 Procedure Retrieve_Organization
78 ( p_organization_id IN NUMBER Default G_NULL_NUM
79 , p_organization_name IN VARCHAR2 Default G_NULL_CHAR
80 , x_organization_id OUT NOCOPY NUMBER
81 , x_organization_name OUT NOCOPY VARCHAR2
82 , x_return_status OUT NOCOPY VARCHAR2
83 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
84 );
85
86 -- The following where_clause functions are used for ICX pop up LOVs
87 -- to restrict the values returned in the list
88
89 -- Maintained for backwards compatibility (Rel 1.2)
90 --
91 Procedure Retrieve_Where_Clause
92 ( p_user_id IN NUMBER Default G_NULL_NUM
93 , p_user_name IN VARCHAR2 Default G_NULL_CHAR
94 , p_region_code IN VARCHAR2
95 , x_where_clause OUT NOCOPY VARCHAR2
96 , x_return_status OUT NOCOPY VARCHAR2
97 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
98 );
99 --
100 -- See also BIS_DIM_LVL_LOV_REG_PVT
101 --
102 Procedure Retrieve_Where_Clause
103 ( p_user_id IN NUMBER Default G_NULL_NUM
104 , p_user_name IN VARCHAR2 Default G_NULL_CHAR
105 , p_organization_id IN VARCHAR2 Default G_NULL_CHAR
106 , p_organization_type IN VARCHAR2 Default G_NULL_CHAR
107 , p_region_code IN VARCHAR2
108 , p_dimension_short_name IN VARCHAR2
109 , x_where_clause OUT NOCOPY VARCHAR2
110 , x_return_status OUT NOCOPY VARCHAR2
111 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
112 );
113 --
114 Procedure Retrieve_Org_Where_Clause
115 ( p_user_id IN NUMBER
116 , p_dimension_level_short_name IN VARCHAR2
117 , x_where_clause OUT NOCOPY VARCHAR2
118 );
119 --
120 Procedure Retrieve_Org_Where_Clause
121 ( p_database_object IN VARCHAR2
122 , p_user_id IN NUMBER
123 , p_dim_level_short_name IN VARCHAR2
124 , x_where_clause OUT NOCOPY VARCHAR2
125 , x_return_status OUT NOCOPY VARCHAR2
126 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
127 );
128
129 Procedure Retrieve_Org_Where_Clause
130 ( p_user_id IN NUMBER
131 , p_dimension_level_short_name IN VARCHAR2
132 , x_where_clause OUT NOCOPY VARCHAR2
133 , x_return_status OUT NOCOPY VARCHAR2
134 , x_msg_count OUT NOCOPY VARCHAR2
135 , x_msg_data OUT NOCOPY VARCHAR2
136 );
137
138 Procedure Retrieve_Time_Where_Clause
139 ( p_time_dim_level_short_name IN VARCHAR2
140 , p_org_dim_level_short_name IN VARCHAR2
141 , p_org_form_name IN VARCHAR2
142 , p_ak_org_id_var IN VARCHAR2
143 , x_where_clause OUT NOCOPY VARCHAR2
144 );
145
146 Procedure Retrieve_Time_Where_Clause
147 ( p_time_dim_level_short_name IN VARCHAR2
148 , p_org_dim_level_short_name IN VARCHAR2
149 , p_org_id IN VARCHAR2
150 , x_where_clause OUT NOCOPY VARCHAR2
151 );
152
153 Procedure Retrieve_Time_Where_Clause
154 ( p_database_object IN VARCHAR2
155 , p_dim_level_short_name IN VARCHAR2
156 , p_organization_id IN VARCHAR2
157 , p_organization_type IN VARCHAR2 Default G_NULL_CHAR
158 , x_where_clause OUT NOCOPY VARCHAR2
159 , x_return_status OUT NOCOPY VARCHAR2
160 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
161 );
162
163 Procedure Retrieve_Time_Where_Clause
164 ( p_time_dim_level_short_name IN VARCHAR2
165 , p_org_dim_level_short_name IN VARCHAR2
166 , p_org_id IN VARCHAR2
167 , x_where_clause OUT NOCOPY VARCHAR2
168 , x_return_status OUT NOCOPY VARCHAR2
169 , x_msg_count OUT NOCOPY VARCHAR2
170 , x_msg_data OUT NOCOPY VARCHAR2
171 );
172
173 Procedure Retrieve_DimX_Where_Clause
174 ( p_database_object IN VARCHAR2
175 , p_user_id IN NUMBER Default G_NULL_NUM
176 , p_organization_id IN VARCHAR2 Default G_NULL_CHAR
177 , p_organization_type IN VARCHAR2 Default G_NULL_CHAR
178 , p_dim_level_short_name IN VARCHAR2
179 , x_where_clause OUT NOCOPY VARCHAR2
180 , x_return_status OUT NOCOPY VARCHAR2
181 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
182 );
183
184 Procedure Retrieve_DimX_Where_Clause
185 ( p_dimension_level_short_name IN VARCHAR2
186 , p_depend_dimension_short_name IN VARCHAR2
187 , p_depend_dim_column_name IN VARCHAR2
188 , p_depend_form_name IN VARCHAR2
189 , p_ak_depend_id_var IN VARCHAR2
190 , x_where_clause OUT NOCOPY VARCHAR2
191 );
192 --
193 -- The following three functions are used in the BIS_TARGETS view
194 -- to resolve names of roles, computing funcitons and reporting functions
195 -- for target levels
196 --
197 FUNCTION RESOLVE_ROLE_NAME(
198 p_value IN VARCHAR2
199 )
200 RETURN VARCHAR2;
201
202 FUNCTION RESOLVE_FUNCTION_NAME(
203 p_value IN VARCHAR2 )
204 RETURN VARCHAR2;
205
206 FUNCTION RESOLVE_FULL_FUNCTION_NAME(
207 p_value IN VARCHAR2 )
208 RETURN VARCHAR2;
209
210 FUNCTION RESOLVE_FULL_ACTIVITY_NAME(
211 p_name IN VARCHAR2
212 , p_type IN VARCHAR2
213 )
214 RETURN varchar2;
215
216
217
218 -- First segment is segment #1
219 FUNCTION Retrieve_Segment
220 ( p_string IN VARCHAR2
221 , p_delimitor IN VARCHAR2 Default BIS_UTILITIES_PUB.G_VALUE_SEPARATOR
222 , p_segment_num IN NUMBER Default 1
223 ) RETURN VARCHAR2;
224
225 -- the following functions return FND_API.G_TRUE/FND_API.G_FALSE
226
227 FUNCTION Value_Missing(
228 p_value IN VARCHAR2 )
229 RETURN VARCHAR2;
230
231 FUNCTION Value_Missing(
232 p_value IN NUMBER )
233 RETURN VARCHAR2;
234
235 FUNCTION Value_Missing(
236 p_value IN DATE )
237 RETURN VARCHAR2;
238
239 FUNCTION Value_Not_Missing(
240 p_value IN VARCHAR2 )
241 RETURN VARCHAR2;
242
243 FUNCTION Value_Not_Missing(
244 p_value IN NUMBER )
245 RETURN VARCHAR2;
246
247 FUNCTION Value_Not_Missing(
248 p_value IN DATE )
249 RETURN VARCHAR2;
250 FUNCTION Value_NULL(
251 p_value IN VARCHAR2 )
252 RETURN VARCHAR2;
253
254 FUNCTION Value_NULL(
255 p_value IN NUMBER )
256 RETURN VARCHAR2;
257
258 FUNCTION Value_NULL(
259 p_value IN DATE )
260 RETURN VARCHAR2;
261
262 FUNCTION Value_Not_NULL(
263 p_value IN VARCHAR2 )
264 RETURN VARCHAR2;
265
266 FUNCTION Value_Not_NULL(
267 p_value IN NUMBER )
268 RETURN VARCHAR2;
269
270 FUNCTION Value_Not_NULL(
271 p_value IN DATE )
272 RETURN VARCHAR2;
273
274
275
276 PROCEDURE Build_HTML_Banner
277 ( p_title IN VARCHAR2
278 , x_banner_string OUT NOCOPY VARCHAR2
279 , x_return_status OUT NOCOPY VARCHAR2
280 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
281 );
282
283 PROCEDURE Build_HTML_Banner
284 ( p_title IN VARCHAR2
285 , x_banner_string OUT NOCOPY VARCHAR2
286 , x_return_status OUT NOCOPY VARCHAR2
287 , icon_show IN BOOLEAN
288 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
289 );
290
291
292 FUNCTION Get_Images_Server
293 RETURN VARCHAR2;
294 FUNCTION Get_NLS_Language
295 RETURN VARCHAR2;
296 FUNCTION Get_Report_Title
297 (Function_Code IN VARCHAR2)
298 RETURN VARCHAR2;
299
300 PROCEDURE Build_Report_Header (p_javascript IN VARCHAR2);
301
302 PROCEDURE Build_More_Info_Directory
303 ( Rdf_Filename IN VARCHAR2
304 ,NLS_Language_Code IN VARCHAR2
305 ,Help_Directory OUT NOCOPY VARCHAR2
306 );
307
308
309
310 PROCEDURE Get_Translated_Icon_Text
311 ( Icon_Code IN VARCHAR2,
312 Icon_Meaning OUT NOCOPY VARCHAR2,
313 Icon_Description OUT NOCOPY VARCHAR2
314 );
315 PROCEDURE Get_Image_File_Structure
316 (Icx_Report_Images IN VARCHAR2,
317 NLS_Language_Code IN VARCHAR2,
318 Report_Image OUT NOCOPY VARCHAR2
319 );
320 PROCEDURE Build_HTML_Banner_Reports
321 (Icx_Report_Images IN VARCHAR2,
322 More_Info_Directory IN VARCHAR2,
323 NLS_Language_Code IN VARCHAR2,
324 Report_Name IN VARCHAR2,
325 Report_Link IN VARCHAR2,
326 Related_Reports_Exist IN BOOLEAN,
327 Parameter_Page IN BOOLEAN,
328 Parameter_Page_Link IN VARCHAR2,
329 p_Body_Attribs IN VARCHAR2,
330 HTML_Banner OUT NOCOPY VARCHAR2
331 );
332
333 PROCEDURE Build_Report_Title
334 (p_Function_Code IN VARCHAR2,
335 p_Rdf_Filename IN VARCHAR2,
336 p_Body_Attribs IN VARCHAR2
337 );
338
339 PROCEDURE Build_Parameter_Form
340 (p_Form_Action IN VARCHAR2,
341 p_Report_Param_Table IN BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
342 );
343 PROCEDURE Get_After_Form_HTML
344 (icx_report_images IN VARCHAR2,
345 nls_language_code IN VARCHAR2,
346 report_name IN VARCHAR2
347 );
348
349
350 /* rchandra 06-NOV-2001 created functions encode and decode functions
351 for bug 2054540 */
352 /*
353 * util_url.encode convert a given string to a specified character set,
354 * then encode the converted string in form-urlencoded format.
355 * If you only need to encode a string in the database character set,
356 * you don't need to specify the second parameter.
357 * If you have to support different character set in a middle tier
358 * than a database character set, you have to specify this middle
359 * tier character set in p_charset.
360 *
361 * See next URL for more details.
362 * http://www.w3.org/TR/html4/interact/forms.html#h-17.13.4.1
363 *
364 * This method is almost identical to the spec rfc2396, except it replaces
365 * a space character with a '+' character. This function will not be
366 * suitable for some applications that requires true URL and URI escape
367 * described in rfc2396.
368 *
369 * Most of web applications only require form-url-encode.
370 *
371 * When you encode a string with this function, you should not pass
372 * a whole URL string like 'http://aaa.yyy.com/abc.html?param=xxx'.
373 * You first encode parameter names and parameter values separately, then
374 * compose complete URL.
375 *
376 * Syntax:
377 * encode (p_url in varchar2,
378 * p_charset in varchar2)
379 * return varchar2;
380 *
381 * Parameters:
382 * p_url url parameter string to be encoded in form-urlencoded format.
383 * p_charset name of oracle charset such as 'WE8ISO8859P1' or 'UTF8'.
384 * It also accepts a full qualified NLS_LANG value such as
385 * 'JAPANESE_JAPAN.JA16SJIS'. <language> and <territtory>
386 * value are ignored and no affect of the result.
387 *
388 * Return:
389 * varchar2 form-urlencoded string.
390 */
391 function encode (p_url in varchar2,
392 p_charset in varchar2 default null)
393 return varchar2;
394
395 /*
396 * util_url.decode decode a specified form-url-encoded string with specified
397 * character set and convert it to varchar2 string.
398 *
399 * Syntax:
400 * decode (p_url in varchar2,
401 * p_charset in varchar2)
402 * return varchar2;
403 *
404 * Parameters:
405 * p_url url parameter string to be decoded.
406 * p_charset nname of oracle charset such as 'WE8ISO8859P1' or 'UTF8'.
407 * It also accepts a full qualified NLS_LANG value such as
408 * 'JAPANESE_JAPAN.JA16SJIS'. <language> and <territtory>
409 * value are ignored and no affect of the result.
410 *
411 * Return:
412 * varchar2 decoded string in varchar2 that character set match
413 * with the database character set.
414 */
415 function decode(p_url in varchar2,
416 p_charset in varchar2 default null)
417 return varchar2;
418
419
420 FUNCTION is_time_dependent_on_org( p_time_lvl_short_name IN VARCHAR2) RETURN NUMBER ;
421
422 FUNCTION is_org_dependent_on_resp ( p_org_lvl_short_name IN VARCHAR2) RETURN NUMBER ;
423
424 PROCEDURE get_time_where_clause(
425 p_dim_level_short_name IN VARCHAR2
426 ,p_parent_level_short_name IN VARCHAR2
427 ,p_parent_level_id IN VARCHAR2
428 ,p_source IN VARCHAR2
429 ,x_where_clause OUT NOCOPY VARCHAR2
430 ,x_return_status OUT NOCOPY VARCHAR2
431 ,x_err_count OUT NOCOPY NUMBER
432 ,x_errorMessage OUT NOCOPY VARCHAR2
433 );
434
435
436 PROCEDURE get_org_where_clause(
437 p_usr_id IN NUMBER
438 ,p_dim_level_short_name IN VARCHAR2
439 ,x_where_clause OUT NOCOPY VARCHAR2
440 ,x_return_status OUT NOCOPY VARCHAR2
441 ,x_err_count OUT NOCOPY NUMBER
442 ,x_errorMessage OUT NOCOPY VARCHAR2
443 ) ;
444
445
446 -- return Edw time levels from the Tlist
447 FUNCTION get_edw_org_dep_time_levels RETURN VARCHAR2;
448
449
450 PROCEDURE get_debug_mode_profile -- 2694978
451 ( x_is_debug_mode OUT NOCOPY BOOLEAN
452 , x_return_status OUT NOCOPY VARCHAR2
453 , x_return_msg OUT NOCOPY VARCHAR2
454 ) ;
455
456 PROCEDURE set_debug_log_flag ( -- 2694978
457 p_is_true IN BOOLEAN
458 , x_return_status OUT NOCOPY VARCHAR2
459 , x_return_msg OUT NOCOPY VARCHAR2
460 ) ;
461
462 FUNCTION is_debug_on RETURN BOOLEAN ; -- 2694978
463
464 PROCEDURE open_debug_log ( -- 2694978
465 p_file_name IN VARCHAR2,
466 p_dir_name IN VARCHAR2,
467 x_return_status OUT NOCOPY VARCHAR2,
468 x_return_msg OUT NOCOPY VARCHAR2);
469
470
471 PROCEDURE close_debug_log ( -- 2694978
472 p_file_name IN VARCHAR2,
473 p_dir_name IN VARCHAR2,
474 x_return_status OUT NOCOPY VARCHAR2,
475 x_return_msg OUT NOCOPY VARCHAR2);
476
477
478 PROCEDURE put(p_text IN VARCHAR2); -- 2694978
479
480
481 PROCEDURE put_line(p_text IN VARCHAR2); -- 2694978
482
483 FUNCTION Get_DB_Version RETURN NUMBER;
484
485
486 -- Added for Bug#3767188
487 FUNCTION Enable_Auto_Generated RETURN VARCHAR2;
488
489 --sawu: lookup given user name for user id
490 FUNCTION Get_Owner_Id(p_name IN VARCHAR2) RETURN NUMBER;
491
492 --vtulasi: Returns user name for given user id
493 FUNCTION Get_Owner_Name(p_id IN NUMBER)
494 RETURN VARCHAR2;
495
496 -- adrao added for bug#3788314
497 FUNCTION Is_Func_Enabled (p_Function_Name IN VARCHAR2) RETURN VARCHAR2;
498
499 -- adrao added for bug#3788314
500 FUNCTION Enable_Custom_Kpi RETURN VARCHAR2;
501
502 -- wleung added for bug#2690720
503 FUNCTION Enable_Generated_Source_Report RETURN VARCHAR2;
504
505 END BIS_UTILITIES_PUB;
[Home] [Help]