[Home] [Help]
PACKAGE BODY: APPS.BIS_UTILITIES_PUB
Source
1 PACKAGE BODY BIS_UTILITIES_PUB AS
2 /* $Header: BISPUTLB.pls 120.0 2005/06/01 18:06:50 appldev noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_UTILITIES_PUB';
6
7
8 g_db_nls_lang varchar2(200) := userenv('LANGUAGE');
9 g_db_charset varchar2(200) := substr(g_db_nls_lang,
10 instr(g_db_nls_lang, '.')+1);
11
12 Tlist TimeLvlList := TimeLvlList('MONTH','QUARTER','YEAR','EDW_TIME_CAL_PERIOD','EDW_TIME_CAL_QTR','EDW_TIME_CAL_YEAR');
13
14 Olist TimeLvlList := TimeLvlList('INV ORGANIZATION','LEGAL ENTITY','OPERATING UNIT','HR ORGANIZATION','OPM COMPANY','ORGANIZATION','SET OF BOOKS','BUSINESS GROUP'
15 ,'HRI_ORG_HRCY_BX','HRI_ORG_HRCYVRSN_BX','HRI_ORG_HR_HX','HRI_ORG_INHV_H','HRI_ORG_SSUP_H','HRI_ORG_BGR_HX','HRI_ORG_HR_H','HRI_ORG_SRHL');
16
17 Procedure Retrieve_User
18 ( p_user_id IN NUMBER Default G_NULL_NUM
19 , p_user_name IN VARCHAR2 Default G_NULL_CHAR
20 , x_user_id OUT NOCOPY NUMBER
21 , x_user_name OUT NOCOPY VARCHAR2
22 , x_return_status OUT NOCOPY VARCHAR2
23 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
24 )
25 IS
26 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
27 BEGIN
28 x_return_status := FND_API.G_RET_STS_SUCCESS;
29
30 IF BIS_UTILITIES_PUB.Value_Not_Missing(p_user_id) = FND_API.G_TRUE THEN
31 select user_id , user_name
32 into x_user_id, x_user_name
33 from fnd_user
34 where user_id = p_user_id;
35 ELSIF BIS_UTILITIES_PUB.Value_Not_Missing(p_user_name) = FND_API.G_TRUE THEN
36 select user_id , user_name
37 into x_user_id, x_user_name
38 from fnd_user
39 where user_name = p_user_name;
40 ELSE
41 null;
42 END IF;
43
44 EXCEPTION
45 WHEN NO_DATA_FOUND THEN
46 x_return_status := FND_API.G_RET_STS_ERROR ;
47 when FND_API.G_EXC_ERROR then
48 x_return_status := FND_API.G_RET_STS_ERROR ;
49 when FND_API.G_EXC_UNEXPECTED_ERROR then
50 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
51 when others then
52 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
53 l_error_tbl := x_error_Tbl;
54 BIS_UTILITIES_PVT.Add_Error_Message
55 ( p_error_msg_id => SQLCODE
56 , p_error_description => SQLERRM
57 , p_error_proc_name => G_PKG_NAME||'.Retrieve_User'
58 , p_error_table => l_error_tbl
59 , x_error_table => x_error_tbl
60 );
61
62 END Retrieve_User;
63
64
65 Procedure Retrieve_Organization
66 ( p_organization_id IN NUMBER Default G_NULL_NUM
67 , p_organization_name IN VARCHAR2 Default G_NULL_CHAR
68 , x_organization_id OUT NOCOPY NUMBER
69 , x_organization_name OUT NOCOPY VARCHAR2
70 , x_return_status OUT NOCOPY VARCHAR2
71 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
72 )
73 IS
74 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
75
76 BEGIN
77 x_return_status := FND_API.G_RET_STS_SUCCESS;
78
79 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_organization_id) = FND_API.G_TRUE)
80 THEN
81 select organization_id , name
82 into x_organization_id, x_organization_name
83 from hr_all_organization_units
84 where organization_id = p_organization_id;
85 ELSIF BIS_UTILITIES_PUB.Value_Not_Missing(p_organization_name) = FND_API.G_TRUE THEN
86 select organization_id , name
87 into x_organization_id, x_organization_name
88 from hr_all_organization_units
89 where name = p_organization_name;
90 ELSE
91 null;
92 END IF;
93
94 EXCEPTION
95 WHEN NO_DATA_FOUND THEN
96 x_return_status := FND_API.G_RET_STS_ERROR ;
97 when FND_API.G_EXC_ERROR then
98 x_return_status := FND_API.G_RET_STS_ERROR ;
99 when FND_API.G_EXC_UNEXPECTED_ERROR then
100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
101 when others then
102 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
103 l_error_tbl := x_error_Tbl;
104 BIS_UTILITIES_PVT.Add_Error_Message
105 ( p_error_msg_id => SQLCODE
106 , p_error_description => SQLERRM
107 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Organization'
108 , p_error_table => l_error_tbl
109 , x_error_table => x_error_tbl
110 );
111
112 END Retrieve_Organization;
113
114 -- The following where_clause functions are used for ICX pop up LOVs
115 -- to restrict the values returned in the list
116
117 -- Maintained for backwards compatibility (Rel 1.2)
118 -- Originally only Organization required special where_clauses
119 --
120 Procedure Retrieve_Where_Clause
121 ( p_user_id IN NUMBER Default G_NULL_NUM
122 , p_user_name IN VARCHAR2 Default G_NULL_CHAR
123 , p_region_code 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 IS
129 BEGIN
130
131 Retrieve_Where_Clause
132 ( p_user_id => p_user_id
133 , p_user_name => p_user_name
134 , p_region_code => p_region_code
135 , p_dimension_short_name => 'ORGANIZATION'
136 , x_where_clause => x_where_clause
137 , x_return_status => x_return_status
138 , x_error_tbl => x_error_tbl
139 );
140
141 END Retrieve_Where_Clause;
142
143 -- Not sure if this belongs in utilities pkg or in dimension values pkg
144 --
145 -- See also BIS_DIM_LVL_LOV_REG_PVT.Lookup_DimLvl_Dependency
146 -- for dimension to dimension dependency
147 --
148 Procedure Retrieve_Where_Clause
149 ( p_user_id IN NUMBER Default G_NULL_NUM
150 , p_user_name IN VARCHAR2 Default G_NULL_CHAR
151 , p_organization_id IN VARCHAR2 Default G_NULL_CHAR
152 , p_organization_type IN VARCHAR2 Default G_NULL_CHAR
153 , p_region_code IN VARCHAR2
154 , p_dimension_short_name IN VARCHAR2
155 , x_where_clause OUT NOCOPY VARCHAR2
156 , x_return_status OUT NOCOPY VARCHAR2
157 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
158 )
159 IS
160
161 l_user_id NUMBER;
162 l_dim_level_view BISBV_DIMENSION_LEVELS.LEVEL_VALUES_VIEW_NAME%TYPE;
163 l_database_object VARCHAR2(30);
164 l_dim_level_short_name BISBV_DIMENSION_LEVELS.DIMENSION_LEVEL_SHORT_NAME%TYPE;
165 counter NUMBER := 0;
166 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
167
168 CURSOR c_views IS
169 SELECT level_values_view_name, dimension_level_short_name
170 FROM bisfv_dimension_levels
171 WHERE UPPER(level_values_view_name) = UPPER(l_database_object);
172
173 BEGIN
174
175 SELECT DATABASE_OBJECT_NAME
176 INTO l_database_object
177 FROM ak_regions
178 WHERE REGION_CODE = UPPER(p_region_code);
179
180
181 FOR c_views_data in c_views LOOP
182 l_dim_level_view:= c_views_data.level_values_view_name;
183 l_dim_level_short_name:= c_views_data.dimension_level_short_name;
184 counter:= counter + 1;
185 END LOOP;
186
187 IF counter > 1
188 THEN
189 SELECT level_values_view_name, dimension_level_short_name
190 INTO l_dim_level_view, l_dim_level_short_name
191 FROM bisfv_dimension_levels
192 WHERE UPPER(level_values_view_name) = UPPER(l_database_object) and dimension_short_name = p_dimension_short_name;
193 END IF;
194
195
196 IF BIS_UTILITIES_PUB.Value_Missing(p_user_id) = FND_API.G_TRUE
197 THEN
198 SELECT user_id
199 INTO l_user_id
200 FROM fnd_user
201 WHERE user_name = p_user_name;
202 ELSE
203 l_user_id := p_user_id;
204 END IF;
205
206 IF p_dimension_short_name = BIS_UTILITIES_PVT.GET_ORG_DIMENSION_NAME(p_DimLevelId => NULL
207 ,p_DimLevelName => l_dim_level_short_name) THEN --'ORGANIZATION'
208 Retrieve_Org_Where_Clause
209 ( p_database_object => l_database_object
210 , p_user_id => l_user_id
211 , p_dim_level_short_name => l_dim_level_short_name
212 , x_where_clause => x_where_clause
213 , x_return_status => x_return_status
214 , x_error_tbl => x_error_tbl
215 );
216
217 ELSIF p_dimension_short_name = BIS_UTILITIES_PVT.GET_TIME_DIMENSION_NAME(p_DimLevelId => NULL
218 ,p_DimLevelName => l_dim_level_short_name) THEN
219 IF ( BIS_UTILITIES_PUB.Value_Missing(p_organization_id) = FND_API.G_TRUE ) THEN -- 2694965
220 RAISE FND_API.G_EXC_ERROR;
221 END IF;
222 Retrieve_Time_Where_Clause
223 ( p_database_object => l_database_object
224 , p_dim_level_short_name => l_dim_level_short_name
225 , p_organization_id => p_organization_id
226 , p_organization_type => p_organization_type
227 , x_where_clause => x_where_clause
228 , x_return_status => x_return_status
229 , x_error_tbl => x_error_tbl
230 );
231 ELSE
232 Retrieve_DimX_Where_Clause
233 ( p_database_object => l_database_object
234 , p_user_id => l_user_id
235 , p_organization_id => p_organization_id
236 , p_dim_level_short_name => l_dim_level_short_name
237 , p_organization_type => p_organization_type
238 , x_where_clause => x_where_clause
239 , x_return_status => x_return_status
240 , x_error_tbl => x_error_tbl
241 );
242 END IF;
243
244
245 EXCEPTION
246 WHEN NO_DATA_FOUND THEN
247 x_return_status := FND_API.G_RET_STS_ERROR ;
248 when FND_API.G_EXC_ERROR then
249 x_return_status := FND_API.G_RET_STS_ERROR ;
250 when FND_API.G_EXC_UNEXPECTED_ERROR then
251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
252 when others then
253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
254 l_error_tbl := x_error_Tbl;
255 BIS_UTILITIES_PVT.Add_Error_Message
256 ( p_error_msg_id => SQLCODE
257 , p_error_description => SQLERRM
258 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Where_Clause'
259 , p_error_table => l_error_tbl
260 , x_error_table => x_error_tbl
261 );
262
263 END Retrieve_Where_Clause;
264
265 Procedure Retrieve_Org_Where_Clause
266 ( p_user_id IN NUMBER
267 , p_dimension_level_short_name IN VARCHAR2
268 , x_where_clause OUT NOCOPY VARCHAR2
269 )
270 IS
271
272 l_Responsibility_tbl BIS_Responsibility_PVT.Responsibility_Tbl_Type;
273 l_where_clause VARCHAR2(32000);
274 l_comma VARCHAR2(2) := ',';
275 l_return_status VARCHAR2(100);
276 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
277
278 BEGIN
279
280 FND_MSG_PUB.initialize;
281
282
283 IF(p_dimension_level_short_name <> 'TOTAL_ORGANIZATION')
284 AND (SUBSTR(p_dimension_level_short_name, 1, 3) <> 'OPM')
285 THEN
286
287 BIS_RESPONSIBILITY_PVT.Retrieve_User_Responsibilities
288 ( p_api_version => 1.0
289 , p_user_id => p_user_id
290 , p_Responsibility_version => NULL
291 , x_Responsibility_Tbl => l_Responsibility_tbl
292 , x_return_status => l_return_status
293 , x_error_tbl => l_error_tbl
294 );
295
296 l_where_clause := '" responsibility_id IN ( ';
297
298 FOR i IN 1..l_Responsibility_tbl.COUNT LOOP
299 IF i = l_Responsibility_tbl.LAST THEN
300 l_where_clause := l_where_clause
301 ||l_Responsibility_tbl(i).Responsibility_ID;
302 ELSE
303 l_where_clause := l_where_clause
304 ||l_Responsibility_tbl(i).Responsibility_ID||l_comma;
305 END IF;
306 END LOOP;
307
308 l_where_clause := l_where_clause||' )"';
309 ELSE
310 l_where_clause := '""';
311 END IF;
312
313 x_where_clause := l_where_clause;
314
315 END Retrieve_Org_Where_Clause;
316
317 -- Organization is related to user
318 --
319 Procedure Retrieve_Org_Where_Clause
320 ( p_database_object IN VARCHAR2
321 , p_user_id IN NUMBER
322 , p_dim_level_short_name IN VARCHAR2
323 , x_where_clause OUT NOCOPY VARCHAR2
324 , x_return_status OUT NOCOPY VARCHAR2
325 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
326 )
327 IS
328
329 l_Responsibility_tbl BIS_Responsibility_PVT.Responsibility_Tbl_Type;
330 l_where_clause VARCHAR2(32000);
331 l_comma VARCHAR2(2) := ',';
332 l_database_object VARCHAR2(30);
333 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
334
335 BEGIN
336
337 FND_MSG_PUB.initialize;
338
339 IF(p_dim_level_short_name <> 'TOTAL_ORGANIZATION')
340 AND (SUBSTR(p_dim_level_short_name, 1, 3) <> 'OPM')
341 THEN
342
343 BIS_RESPONSIBILITY_PVT.Retrieve_User_Responsibilities
344 ( p_api_version => 1.0
345 , p_user_id => p_user_id
346 , p_Responsibility_version => NULL
347 , x_Responsibility_Tbl => l_Responsibility_tbl
348 , x_return_status => x_return_status
349 , x_error_tbl => x_error_tbl
350 );
351
352 l_where_clause := ' 1 = 1 '
353 ||' INTERSECT SELECT DISTINCT VALUE, ID '
354 ||' FROM '||p_database_object
355 ||' WHERE responsibility_id IN ( ';
356
357 FOR i IN 1..l_Responsibility_tbl.COUNT LOOP
358 IF i = l_Responsibility_tbl.LAST THEN
359 l_where_clause := l_where_clause
360 ||l_Responsibility_tbl(i).Responsibility_ID;
361 ELSE
362 l_where_clause := l_where_clause
363 ||l_Responsibility_tbl(i).Responsibility_ID||l_comma;
364 END IF;
365 END LOOP;
366
367 l_where_clause := l_where_clause||' )';
368 ELSE
369 l_where_clause := '';
370 END IF;
371
372 l_where_clause := BIS_UTILITIES_PUB.encode(l_where_clause);
373
374 x_where_clause := l_where_clause;
375
376 EXCEPTION
377 WHEN NO_DATA_FOUND THEN
378 x_return_status := FND_API.G_RET_STS_ERROR ;
379 when FND_API.G_EXC_ERROR then
380 x_return_status := FND_API.G_RET_STS_ERROR ;
381 when FND_API.G_EXC_UNEXPECTED_ERROR then
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
383 when others then
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
385 l_error_tbl := x_error_Tbl;
386 BIS_UTILITIES_PVT.Add_Error_Message
387 ( p_error_msg_id => SQLCODE
388 , p_error_description => SQLERRM
389 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Org_Where_Clause'
390 , p_error_table => l_error_tbl
391 , x_error_table => x_error_tbl
392 );
393
394 END Retrieve_Org_Where_Clause;
395
396 -- Time is related to organiztion
397 --
398 Procedure Retrieve_Time_Where_Clause
399 ( p_time_dim_level_short_name IN VARCHAR2
400 , p_org_dim_level_short_name IN VARCHAR2
401 , p_org_form_name IN VARCHAR2
402 , p_ak_org_id_var IN VARCHAR2
403 , x_where_clause OUT NOCOPY VARCHAR2
404 )
405 IS
406 l_where_clause VARCHAR2(32000);
407 BEGIN
408
409 FND_MSG_PUB.initialize;
410
411 IF(p_time_dim_level_short_name <> 'TOTAL_TIME')
412 AND (is_time_dependent_on_org(p_time_lvl_short_name => p_time_dim_level_short_name)
413 = BIS_UTILITIES_PUB.G_TIME_IS_DEPEN_ON_ORG) THEN --2684911
414 l_where_clause :=
415 '"organization_id="+'||'"''"+'||'document.'||p_org_form_name||'.'
416 ||p_ak_org_id_var||'.value+'||'"''"'||'+ "%20and%20organization_type="+'
417 ||'"'''||BIS_UTILITIES_PUB.encode(p_org_dim_level_short_name)||''''||'"';
418 ELSE
419 l_where_clause := '""';
420 END IF;
421
422 x_where_clause := l_where_clause;
423
424 END Retrieve_Time_Where_Clause;
425
426 -- Time is related to organiztion
427 -- New procedure to take in org_id directly
428 Procedure Retrieve_Time_Where_Clause
429 ( p_time_dim_level_short_name IN VARCHAR2
430 , p_org_dim_level_short_name IN VARCHAR2
431 , p_org_id IN VARCHAR2
432 , x_where_clause OUT NOCOPY VARCHAR2
433 )
434 IS
435 l_where_clause VARCHAR2(32000);
436 BEGIN
437
438 FND_MSG_PUB.initialize;
439
440 IF(p_time_dim_level_short_name <> 'TOTAL_TIME')
441 AND (is_time_dependent_on_org(p_time_lvl_short_name => p_time_dim_level_short_name)
442 = BIS_UTILITIES_PUB.G_TIME_IS_DEPEN_ON_ORG) THEN --2684911
443 l_where_clause :=
444 'organization_id = '|| '''' || p_org_id || '''' || ' and organization_type = '
445 || '''' || p_org_dim_level_short_name || '''';
446 ELSE
447 l_where_clause := '""';
448 END IF;
449
450 x_where_clause := l_where_clause;
451
452 END Retrieve_Time_Where_Clause;
453
454 Procedure Retrieve_Time_Where_Clause
455 ( p_database_object IN VARCHAR2
456 , p_dim_level_short_name IN VARCHAR2
457 , p_organization_id IN VARCHAR2
458 , p_organization_type IN VARCHAR2 Default G_NULL_CHAR
459 , x_where_clause OUT NOCOPY VARCHAR2
460 , x_return_status OUT NOCOPY VARCHAR2
461 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
462 )
463 IS
464
465 l_Responsibility_tbl BIS_Responsibility_PVT.Responsibility_Tbl_Type;
466 l_where_clause VARCHAR2(32000);
467 l_database_object VARCHAR2(30);
468 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
469
470 BEGIN
471
472 FND_MSG_PUB.initialize;
473
474 IF(p_dim_level_short_name <> 'TOTAL_TIME') THEN
475
476 IF (is_time_dependent_on_org(p_time_lvl_short_name => p_dim_level_short_name)
477 = BIS_UTILITIES_PUB.G_TIME_IS_DEPEN_ON_ORG) THEN --2684911
478 --
479 -- NULL is included because of some kludge in the AK definition
480 l_where_clause := ' 1 = 1 '
481 ||' INTERSECT SELECT DISTINCT VALUE, ID'
482 || ' FROM '
483 || p_database_object
484 || ' WHERE '
485 || ' ORGANIZATION_ID = '
486 || p_organization_ID
487 || ' AND NVL(ORGANIZATION_TYPE, ''%'') LIKE '
488 || ''''
489 || p_organization_type
490 || '''';
491 ELSE
492 --
493 -- NULL is included because of some kludge in the AK definition
494 l_where_clause := ' 1 = 1 '
495 ||' INTERSECT SELECT DISTINCT VALUE, ID'
496 || ' FROM '
497 || p_database_object;
498 END IF;
499 END IF;
500
501 l_where_clause := BIS_UTILITIES_PUB.encode(l_where_clause);
502 x_where_clause := l_where_clause;
503
504 EXCEPTION
505 WHEN NO_DATA_FOUND THEN
506 x_return_status := FND_API.G_RET_STS_ERROR ;
507 when FND_API.G_EXC_ERROR then
508 x_return_status := FND_API.G_RET_STS_ERROR ;
509 when FND_API.G_EXC_UNEXPECTED_ERROR then
510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
511 when others then
512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
513 l_error_tbl := x_error_Tbl;
514 BIS_UTILITIES_PVT.Add_Error_Message
515 ( p_error_msg_id => SQLCODE
516 , p_error_description => SQLERRM
517 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Time_Where_Clause'
518 , p_error_table => l_error_tbl
519 , x_error_table => x_error_tbl
520 );
521
522 END Retrieve_Time_Where_Clause;
523
524 -- no other relationship but included for future
525 --
526 Procedure Retrieve_DimX_Where_Clause
527 ( p_database_object IN VARCHAR2
528 , p_user_id IN NUMBER Default G_NULL_NUM
529 , p_organization_id IN VARCHAR2 Default G_NULL_CHAR
530 , p_organization_type IN VARCHAR2 Default G_NULL_CHAR
531 , p_dim_level_short_name IN VARCHAR2
532 , x_where_clause OUT NOCOPY VARCHAR2
533 , x_return_status OUT NOCOPY VARCHAR2
534 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
535 )
536 IS
537
538 l_Responsibility_tbl BIS_Responsibility_PVT.Responsibility_Tbl_Type;
539 l_where_clause VARCHAR2(32000);
540 l_database_object VARCHAR2(30);
541 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
542
543 BEGIN
544
545 -- In the future if a where clause is really added for this
546 -- please do encoding as below for Netscape issues
547 -- l_where_clause := wfa_html.conv_special_url_chars(l_where_clause);
548
549 x_where_clause := l_where_clause;
550
551 EXCEPTION
552 WHEN NO_DATA_FOUND THEN
553 x_return_status := FND_API.G_RET_STS_ERROR ;
554 when FND_API.G_EXC_ERROR then
555 x_return_status := FND_API.G_RET_STS_ERROR ;
556 when FND_API.G_EXC_UNEXPECTED_ERROR then
557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
558 when others then
559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
560 l_error_tbl := x_error_Tbl;
561 BIS_UTILITIES_PVT.Add_Error_Message
562 ( p_error_msg_id => SQLCODE
563 , p_error_description => SQLERRM
564 , p_error_proc_name => G_PKG_NAME||'.Retrieve_DimX_Where_Clause'
565 , p_error_table => l_error_tbl
566 , x_error_table => x_error_tbl
567 );
568
569 END Retrieve_DimX_Where_Clause;
570 --
571 --
572 Procedure Retrieve_DimX_Where_Clause
573 ( p_dimension_level_short_name IN VARCHAR2
574 , p_depend_dimension_short_name IN VARCHAR2
575 , p_depend_dim_column_name IN VARCHAR2
576 , p_depend_form_name IN VARCHAR2
577 , p_ak_depend_id_var IN VARCHAR2
578 , x_where_clause OUT NOCOPY VARCHAR2
579 )
580 IS
581 l_database_object VARCHAR2(32000);
582 l_where_clause VARCHAR2(32000);
583
584 BEGIN
585
586 IF (SUBSTR(p_dimension_level_short_name, 1, 5) <> 'TOTAL')
587 THEN
588 l_where_clause :=
589 '"'||p_depend_dim_column_name||'="+'||'"''"+'
590 ||'document.'||p_depend_form_name||'.'||p_ak_depend_id_var||'.value'
591 ||' +'||'"''"';
592
593 ELSE
594 l_where_clause := '""';
595 END IF;
596
597 x_where_clause := l_where_clause;
598
599 END Retrieve_DimX_Where_Clause;
600
601 -- Function used in BIS_TARGETS view to resolve names of roles
602 -- from the workflow roles view
603 FUNCTION RESOLVE_ROLE_NAME(
604 p_value IN VARCHAR2 )
605 RETURN varchar2
606 IS
607 l_name varchar2(100);
608 BEGIN
609
610 IF BIS_UTILITIES_PUB.Value_Not_Missing(p_value) = FND_API.G_TRUE THEN
611 l_name := WF_DIRECTORY.GetRoleDisplayName(p_value);
612 ELSE
613 l_name := NULL;
614 END IF;
615 return(l_name);
616
617 END RESOLVE_ROLE_NAME;
618
619 -- Function used in BIS_TARGETS view to resolve names of
620 -- workflow functions attached to a target level
621
622 FUNCTION RESOLVE_FUNCTION_NAME(
623 p_value IN VARCHAR2 )
624 RETURN varchar2
625 IS
626 l_name FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE;
627 BEGIN
628
629 IF BIS_UTILITIES_PUB.Value_Not_Missing(p_value) = FND_API.G_TRUE THEN
630 select FUNCTION_NAME
631 into l_name
632 from fnd_form_functions
633 where function_id = p_value;
634 ELSE
635 l_name := NULL;
636 END IF;
637 return(l_name);
638 END RESOLVE_FUNCTION_NAME;
639
640 -- Function used in BIS_TARGETS view to resolve translated full names of
641 -- workflow functions attached to a target level
642
643 FUNCTION RESOLVE_FULL_FUNCTION_NAME(
644 p_value IN VARCHAR2 )
645 RETURN varchar2
646 IS
647 l_name varchar2(100);
648 BEGIN
649
650 IF BIS_UTILITIES_PUB.Value_Not_Missing(p_value) = FND_API.G_TRUE THEN
651 select USER_FUNCTION_NAME
652 into l_name
653 from fnd_form_functions_tl
654 where function_id = p_value
655 and
656 language = nvl(userenv('LANG'),'US');
657 ELSE
658 l_name := NULL;
659 END IF;
660 return( l_name );
661
662 END RESOLVE_FULL_FUNCTION_NAME;
663
664
665
666 -- Function used in BIS_TARGET_LEVELS view
667 -- to resolve translated full names of
668 -- workflow activities attached to a target level
669
670 FUNCTION RESOLVE_FULL_ACTIVITY_NAME(
671 p_name IN VARCHAR2
672 , p_type IN VARCHAR2
673 )
674 RETURN varchar2
675 IS
676 l_version number;
677 l_name varchar2(100);
678 BEGIN
679
680 IF BIS_UTILITIES_PUB.Value_Missing(p_name) = FND_API.G_TRUE
681 OR BIS_UTILITIES_PUB.Value_Missing(p_type) = FND_API.G_TRUE
682 THEN
683 l_name := NULL;
684 ELSE
685 select version
686 into l_version
687 from wf_activities
688 where name =p_name
689 and item_type = p_type
690 and nvl(begin_date, sysdate) <= sysdate
691 and nvl(end_date, sysdate) >= sysdate
692 and type = 'PROCESS';
693
694 select display_name
695 into l_name
696 from wf_activities_tl
697 where version = l_version
698 and name = p_name
699 and item_type = p_type
700 and language = nvl(userenv('LANG'),'US');
701 END IF;
702 return(l_name );
703 END RESOLVE_FULL_ACTIVITY_NAME;
704
705
706 -- First segment is segment #1
707 FUNCTION Retrieve_Segment
708 ( p_string IN VARCHAR2
709 , p_delimitor IN VARCHAR2 Default BIS_UTILITIES_PUB.G_VALUE_SEPARATOR
710 , p_segment_num IN NUMBER Default 1
711 ) RETURN VARCHAR2
712 IS
713 l_string VARCHAR2(32000);
714
715 BEGIN
716 l_string := p_string || p_delimitor;
717
718 IF p_segment_num = 1 THEN
719 RETURN SUBSTR(l_string, 1, INSTR(l_string, p_delimitor, 1, 1)-1);
720 ELSE
721 RETURN SUBSTR(l_string, INSTR(l_string, p_delimitor, 1, p_segment_num-1)+1,
722 INSTR(l_string, p_delimitor, 1, p_segment_num) -
723 INSTR(l_string, p_delimitor, 1, p_segment_num-1) - 1 );
724
725 END IF;
726
727 EXCEPTION
728 WHEN OTHERS THEN
729 RAISE;
730
731 END Retrieve_Segment;
732
733 FUNCTION Value_Not_Missing(
734 p_value IN VARCHAR2 )
735 RETURN VARCHAR2
736 IS
737 BEGIN
738 return BIS_UTILITIES_PVT.Value_Not_Missing(p_value);
739 END Value_Not_Missing;
740
741 FUNCTION Value_Not_Missing(
742 p_value IN NUMBER )
743 RETURN VARCHAR2
744 IS
745 BEGIN
746 return BIS_UTILITIES_PVT.Value_Not_Missing(p_value);
747 END Value_Not_Missing;
748
749 FUNCTION Value_Not_Missing(
750 p_value IN DATE )
751 RETURN VARCHAR2
752 IS
753 BEGIN
754 return BIS_UTILITIES_PVT.Value_Not_Missing(p_value);
755 END Value_Not_Missing;
756
757 FUNCTION Value_Missing(
758 p_value IN VARCHAR2 )
759 RETURN VARCHAR2
760 IS
761 BEGIN
762 return BIS_UTILITIES_PVT.Value_Missing(p_value);
763 END Value_Missing;
764
765 FUNCTION Value_Missing(
766 p_value IN NUMBER )
767 RETURN VARCHAR2
768 IS
769 BEGIN
770 return BIS_UTILITIES_PVT.Value_Missing(p_value);
771 END Value_Missing;
772
773 FUNCTION Value_Missing(
774 p_value IN DATE )
775 RETURN VARCHAR2
776 IS
777 BEGIN
778 return BIS_UTILITIES_PVT.Value_Missing(p_value);
779 END Value_Missing;
780
781 FUNCTION Value_Not_NULL(
782 p_value IN VARCHAR2 )
783 RETURN VARCHAR2
784 IS
785 BEGIN
786 return BIS_UTILITIES_PVT.Value_Not_NULL(p_value);
787 END Value_Not_NULL;
788
789 FUNCTION Value_Not_NULL(
790 p_value IN NUMBER )
791 RETURN VARCHAR2
792 IS
793 BEGIN
794 return BIS_UTILITIES_PVT.Value_Not_NULL(p_value);
795 END Value_Not_NULL;
796
797 FUNCTION Value_Not_NULL(
798 p_value IN DATE )
799 RETURN VARCHAR2
800 IS
801 BEGIN
802 return BIS_UTILITIES_PVT.Value_Not_NULL(p_value);
803 END Value_Not_NULL;
804
805 FUNCTION Value_NULL(
806 p_value IN VARCHAR2 )
807 RETURN VARCHAR2
808 IS
809 BEGIN
810 return BIS_UTILITIES_PVT.Value_NULL(p_value);
811 END Value_NULL;
812
813 FUNCTION Value_NULL(
814 p_value IN NUMBER )
815 RETURN VARCHAR2
816 IS
817 BEGIN
818 return BIS_UTILITIES_PVT.Value_NULL(p_value);
819 END Value_NULL;
820
821 FUNCTION Value_NULL(
822 p_value IN DATE )
823 RETURN VARCHAR2
824 IS
825 BEGIN
826 return BIS_UTILITIES_PVT.Value_NULL(p_value);
827 END Value_NULL;
828
829 PROCEDURE Build_HTML_Banner
830 ( p_title IN VARCHAR2
831 , x_banner_string OUT NOCOPY VARCHAR2
832 , x_return_status OUT NOCOPY VARCHAR2
833 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
834 )
835 IS
836 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
837 BEGIN
838
839 BIS_HTML_UTILITIES_PVT.Build_HTML_Banner( ' '
840 , p_title
841 , ' '
842 , x_banner_string
843 );
844
845
846 EXCEPTION
847
848 when FND_API.G_EXC_ERROR then
849 x_return_status := FND_API.G_RET_STS_ERROR ;
850 when FND_API.G_EXC_UNEXPECTED_ERROR then
851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
852 when others then
853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
854 l_error_tbl := x_error_Tbl;
855 BIS_UTILITIES_PVT.Add_Error_Message
856 ( p_error_msg_id => SQLCODE
857 , p_error_description => SQLERRM
858 , p_error_proc_name => G_PKG_NAME||'.Build_HTML_Banner'
859 , p_error_table => l_error_tbl
860 , x_error_table => x_error_tbl
861 );
862
863 END Build_HTML_Banner;
864
865
866 PROCEDURE Build_HTML_Banner
867 ( p_title IN VARCHAR2
868 , x_banner_string OUT NOCOPY VARCHAR2
869 , x_return_status OUT NOCOPY VARCHAR2
870 , icon_show IN BOOLEAN
871 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
872 )
873 IS
874 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
875 BEGIN
876
877 BIS_HTML_UTILITIES_PVT.Build_HTML_Banner( ' '
878 , p_title
879 , ' '
880 , icon_show
881 , x_banner_string
882 );
883
884
885 EXCEPTION
886
887 when FND_API.G_EXC_ERROR then
888 x_return_status := FND_API.G_RET_STS_ERROR ;
889 when FND_API.G_EXC_UNEXPECTED_ERROR then
890 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
891 when others then
892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
893 l_error_tbl := x_error_Tbl;
894 BIS_UTILITIES_PVT.Add_Error_Message
895 ( p_error_msg_id => SQLCODE
896 , p_error_description => SQLERRM
897 , p_error_proc_name => G_PKG_NAME||'.Build_HTML_Banner'
898 , p_error_table => l_error_tbl
899 , x_error_table => x_error_tbl
900 );
901
902 END Build_HTML_Banner;
903
904
905 FUNCTION Get_Images_Server
906 RETURN VARCHAR2
907 IS
908 BEGIN
909 Return BIS_REPORT_UTIL_PVT.Get_Images_Server;
910 END Get_Images_Server;
911
912 FUNCTION Get_NLS_Language
913 RETURN VARCHAR2
914 IS
915 BEGIN
916 Return BIS_REPORT_UTIL_PVT.Get_NLs_Language;
917 END Get_NLS_Language;
918
919 FUNCTION Get_Report_Title (Function_Code IN VARCHAR2)
920 RETURN VARCHAR2
921 IS
922 BEGIN
923 Return BIS_REPORT_UTIL_PVT.Get_Report_Title(Function_Code);
924 END Get_Report_Title;
925
926
927 PROCEDURE Build_Report_Header
928 (p_javascript IN VARCHAR2)
929 IS
930 BEGIN
931 BIS_REPORT_UTIL_PVT.Build_Report_Header(p_javascript);
932 END Build_Report_Header;
933
934 PROCEDURE Build_More_Info_Directory
935 ( Rdf_Filename IN VARCHAR2,
936 NLS_Language_Code IN VARCHAR2,
937 Help_Directory OUT NOCOPY VARCHAR2
938 )
939 IS
940 BEGIN
941 BIS_REPORT_UTIL_PVT.Build_More_Info_Directory(Rdf_Filename,
942 NLS_Language_Code,
943 Help_Directory);
944 END Build_More_Info_Directory;
945
946 PROCEDURE Get_Translated_Icon_Text
947 ( Icon_Code IN VARCHAR2,
948 Icon_Meaning OUT NOCOPY VARCHAR2,
949 Icon_Description OUT NOCOPY VARCHAR2
950 )
951 IS
952 BEGIN
953 BIS_REPORT_UTIL_PVT.Get_Translated_Icon_Text(Icon_code,
954 Icon_Meaning,
955 Icon_Description);
956 END Get_Translated_Icon_Text;
957
958 PROCEDURE Get_Image_File_Structure
959 ( Icx_Report_Images IN VARCHAR2,
960 NLS_Language_Code IN VARCHAR2,
961 Report_Image OUT NOCOPY VARCHAR2
962 )
963 IS
964 BEGIN
965 BIS_REPORT_UTIL_PVT.Get_Image_File_Structure(Icx_Report_Images,
966 NLS_Language_Code,
967 Report_Image);
968 END Get_Image_File_Structure;
969
970 PROCEDURE Build_HTML_Banner_Reports
971 ( Icx_Report_Images IN VARCHAR2,
972 More_Info_Directory IN VARCHAR2,
973 NLS_Language_Code IN VARCHAR2,
974 Report_Name IN VARCHAR2,
975 Report_Link IN VARCHAR2,
976 Related_Reports_Exist IN BOOLEAN,
977 Parameter_Page IN BOOLEAN,
978 Parameter_Page_Link IN VARCHAR2,
979 p_Body_Attribs IN VARCHAR2,
980 HTML_Banner OUT NOCOPY VARCHAR2
981 )
982 IS
983 BEGIN
984 BIS_REPORT_UTIL_PVT.Build_HTML_Banner (Icx_Report_Images,
985 More_Info_Directory,
986 NLS_Language_Code,
987 Report_Name,
988 Report_Link,
989 Related_Reports_Exist,
990 Parameter_Page,
991 Parameter_Page_Link,
992 p_Body_Attribs,
993 HTML_Banner
994 );
995 END Build_HTML_Banner_Reports;
996
997 PROCEDURE Build_Report_Title
998 ( p_Function_Code IN VARCHAR2,
999 p_Rdf_Filename IN VARCHAR2,
1000 p_Body_Attribs IN VARCHAR2
1001 )
1002 IS
1003 BEGIN
1004 BIS_REPORT_UTIL_PVT.Build_Report_Title (p_Function_Code,
1005 p_Rdf_Filename,
1006 p_Body_Attribs);
1007 END Build_Report_Title;
1008
1009
1010 PROCEDURE Build_Parameter_Form
1011 ( p_Form_Action IN VARCHAR2,
1012 p_Report_Param_Table IN BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type
1013 )
1014 IS
1015 BEGIN
1016 BIS_REPORT_UTIL_PVT.Build_Parameter_Form (p_Form_Action,
1017 p_Report_Param_Table
1018 );
1019 END Build_Parameter_Form;
1020
1021 PROCEDURE Get_After_Form_HTML
1022 ( icx_report_images IN VARCHAR2,
1023 nls_language_code IN VARCHAR2,
1024 report_name IN VARCHAR2
1025 )
1026 IS
1027 BEGIN
1028 BIS_REPORT_UTIL_PVT.Get_After_Form_HTML(Icx_Report_Images,
1029 NLS_Language_Code,
1030 Report_Name);
1031 END Get_After_Form_HTML;
1032
1033
1034 function encode (p_url in varchar2,
1035 p_charset in varchar2 default null)
1036 return varchar2
1037 is
1038 c_unreserved constant varchar2(72) :=
1039 '-_.!~*''()ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
1040 l_client_nls_lang varchar2(200);
1041 l_client_charset varchar2(200);
1042 l_db_charset varchar2(200);
1043 l_tmp varchar2(32767) := '';
1044 l_onechar varchar2(4);
1045 l_str varchar2(48);
1046 l_byte_len integer;
1047 l_do_convert boolean := null;
1048 i integer;
1049 begin
1050 if p_url is NULL then
1051 return NULL;
1052 end if;
1053 if p_charset is NULL
1054 then
1055 l_client_charset := g_db_charset;
1056 l_client_nls_lang := g_db_nls_lang;
1057 else
1058 i := instr(p_charset, '.');
1059 if i <> 0 then
1060 l_client_charset := substr(p_charset, i+1);
1061 l_client_nls_lang := p_charset;
1062 else
1063 l_client_charset := p_charset;
1064 l_client_nls_lang := 'AMERICAN_AMERICA.' || p_charset;
1065 end if;
1066 end if; -- this IF is Fix for 2380993
1067
1068 /* check if code conversion is required or not */
1069 if l_client_nls_lang = NULL then
1070 l_do_convert := false;
1071 elsif l_client_charset = g_db_charset then
1072 l_do_convert := false;
1073 else
1074 l_do_convert := true;
1075 end if;
1076
1077 for i in 1 .. length(p_url) loop
1078 l_onechar := substr(p_url,i,1);
1079
1080 if instr(c_unreserved, l_onechar) > 0 then
1081 /* if this character is excluded from encoding */
1082 l_tmp := l_tmp || l_onechar;
1083 elsif l_onechar = ' ' then
1084 /* spaces are encoded using the plus "+" sign */
1085 l_tmp := l_tmp || '+';
1086 else
1087 if (l_do_convert) then
1088 /*
1089 * This code to be called ONLY in case when client and server
1090 * charsets are different. The performance of this code is
1091 * significantly slower than "else" portion of this statement.
1092 * But in this case it is guarenteed to be working in
1093 * any configuration where the byte-length of the charset
1094 * is different between client and server (e.g. UTF-8 to SJIS).
1095 */
1096
1097 /*
1098 * utl_raw.convert only takes a qualified NLS_LANG value in
1099 * <langauge>_<territory>.<charset> format for target and
1100 * source charset parameters. Need to use l_client_nls_lang
1101 * and g_db_nls_lang here.
1102 */
1103 l_str := utl_raw.convert(utl_raw.cast_to_raw(l_onechar),
1104 l_client_nls_lang,
1105 g_db_nls_lang);
1106 l_byte_len := length(l_str);
1107 if l_byte_len = 2 then
1108 l_tmp := l_tmp
1109 || '%' || l_str;
1110 elsif l_byte_len = 4 then
1111 l_tmp := l_tmp
1112 || '%' || substr(l_str,1,2)
1113 || '%' || substr(l_str,3,2);
1114 elsif l_byte_len = 6 then
1115 l_tmp := l_tmp
1116 || '%' || substr(l_str,1,2)
1117 || '%' || substr(l_str,3,2)
1118 || '%' || substr(l_str,5,2);
1119 elsif l_byte_len = 8 then
1120 l_tmp := l_tmp
1121 || '%' || substr(l_str,1,2)
1122 || '%' || substr(l_str,3,2)
1123 || '%' || substr(l_str,5,2)
1124 || '%' || substr(l_str,7,2);
1125 else /* maximum precision exceeded */
1126 raise PROGRAM_ERROR;
1127 end if;
1128 else
1129 /*
1130 * This is the "simple" encoding when no charset translation
1131 * is needed, so it is relatively fast.
1132 */
1133 l_byte_len := lengthb(l_onechar);
1134 if l_byte_len = 1 then
1135 l_tmp := l_tmp || '%' ||
1136 substr(to_char(ascii(l_onechar),'FM0X'),1,2);
1137 elsif l_byte_len = 2 then
1138 l_str := to_char(ascii(l_onechar),'FM0XXX');
1139 l_tmp := l_tmp
1140 || '%' || substr(l_str,1,2)
1141 || '%' || substr(l_str,3,2);
1142 elsif l_byte_len = 3 then
1143 l_str := to_char(ascii(l_onechar),'FM0XXXXX');
1144 l_tmp := l_tmp
1145 || '%' || substr(l_str,1,2)
1146 || '%' || substr(l_str,3,2)
1147 || '%' || substr(l_str,5,2);
1148 elsif l_byte_len = 4 then
1149 l_str := to_char(ascii(l_onechar),'FM0XXXXXXX');
1150 l_tmp := l_tmp
1151 || '%' || substr(l_str,1,2)
1152 || '%' || substr(l_str,3,2)
1153 || '%' || substr(l_str,5,2)
1154 || '%' || substr(l_str,7,2);
1155 else /* maximum precision exceeded */
1156 raise PROGRAM_ERROR;
1157 end if;
1158 end if;
1159 end if;
1160 end loop;
1161 return l_tmp;
1162 end encode;
1163
1164 function decode (p_url in varchar2,
1165 p_charset in varchar2 default null)
1166 return varchar2
1167 is
1168 l_client_nls_lang varchar2(200);
1169 l_raw raw(32767);
1170 l_char varchar2(4);
1171 l_hex varchar2(8);
1172 l_len integer;
1173 i integer := 1;
1174 begin
1175 /*
1176 * Set a source charset for code conversion.
1177 * utl_raw.convert() only accepts <lang>_<territory>.<charset> format
1178 * to specify source and destination charset and need to add a dummy
1179 * 'AMERICAN_AMERICA' string if a give charset dose not have <lang>_
1180 * <territory> information.
1181 */
1182 if instr(p_charset, '.') = 0 then
1183 l_client_nls_lang := 'AMERICAN_AMERICA.' || p_charset;
1184 else
1185 l_client_nls_lang := p_charset;
1186 end if;
1187
1188 l_len := length(p_url);
1189
1190 while i <= l_len
1191 loop
1192 l_char := substr(p_url, i, 1);
1193 if l_char = '+' then
1194 /* convert to a hex number of space characters */
1195 l_hex := '20';
1196 i := i + 1;
1197 elsif l_char = '%' then
1198 /* process hex encoded characters. just remove a % character */
1199 l_hex := substr(p_url, i+1, 2);
1200 i := i + 3;
1201 else
1202 /* convert to hex numbers for all other characters */
1203 l_hex := to_char(ascii(l_char), 'FM0X');
1204 i := i + 1;
1205 end if;
1206 /* convert a hex number to a raw datatype */
1207 l_raw := l_raw || hextoraw(l_hex);
1208 end loop;
1209
1210 /*
1211 * convert a raw data from the source charset to the database charset,
1212 * then cast it to a varchar2 string.
1213 */
1214 return utl_raw.cast_to_varchar2(
1215 utl_raw.convert(l_raw, g_db_nls_lang, l_client_nls_lang));
1216 end decode;
1217
1218
1219 Procedure Retrieve_Org_Where_Clause
1220 ( p_user_id IN NUMBER
1221 , p_dimension_level_short_name IN VARCHAR2
1222 , x_where_clause OUT NOCOPY VARCHAR2
1223 , x_return_status OUT NOCOPY VARCHAR2
1224 , x_msg_count OUT NOCOPY VARCHAR2
1225 , x_msg_data OUT NOCOPY VARCHAR2
1226 )
1227 IS
1228
1229 l_Responsibility_tbl BIS_Responsibility_PVT.Responsibility_Tbl_Type;
1230 l_where_clause VARCHAR2(32000);
1231 l_comma VARCHAR2(2) := ',';
1232 l_return_status VARCHAR2(100);
1233 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1234 x_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1235 BEGIN
1236
1237 FND_MSG_PUB.initialize;
1238
1239 IF(p_dimension_level_short_name <> 'TOTAL_ORGANIZATION')
1240 AND (SUBSTR(p_dimension_level_short_name, 1, 3) <> 'OPM')
1241 THEN
1242
1243 BIS_RESPONSIBILITY_PVT.Retrieve_User_Responsibilities
1244 ( p_api_version => 1.0
1245 , p_user_id => p_user_id
1246 , p_Responsibility_version => NULL
1247 , x_Responsibility_Tbl => l_Responsibility_tbl
1248 , x_return_status => l_return_status
1249 , x_error_tbl => l_error_tbl
1250 );
1251
1252 l_where_clause := '" responsibility_id IN ( ';
1253
1254 FOR i IN 1..l_Responsibility_tbl.COUNT LOOP
1255 IF i = l_Responsibility_tbl.LAST THEN
1256 l_where_clause := l_where_clause
1257 ||l_Responsibility_tbl(i).Responsibility_ID;
1258 ELSE
1259 l_where_clause := l_where_clause
1260 ||l_Responsibility_tbl(i).Responsibility_ID||l_comma;
1261 END IF;
1262 END LOOP;
1263
1264 l_where_clause := l_where_clause||' )"';
1265 ELSE
1266 l_where_clause := '""';
1267 END IF;
1268
1269 x_where_clause := l_where_clause;
1270
1271 x_return_status := 'S';
1272 EXCEPTION
1273 WHEN FND_API.G_EXC_ERROR THEN
1274 x_return_status := FND_API.G_RET_STS_ERROR;
1275 l_error_tbl := x_error_tbl;
1276 BIS_UTILITIES_PVT.Add_Error_Message
1277 ( p_error_table => l_error_Tbl
1278 , p_error_msg_id => SQLCODE
1279 , p_error_description => SQLERRM
1280 , x_error_table => x_error_Tbl
1281 );
1282 FND_MSG_PUB.Count_And_Get
1283 ( p_count => x_msg_count,
1284 p_data => x_msg_data
1285 );
1286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1288 l_error_tbl := x_error_tbl;
1289 BIS_UTILITIES_PVT.Add_Error_Message
1290 ( p_error_table => l_error_Tbl
1291 , p_error_msg_id => SQLCODE
1292 , p_error_description => SQLERRM
1293 , x_error_table => x_error_Tbl
1294 );
1295 FND_MSG_PUB.Count_And_Get
1296 ( p_count => x_msg_count,
1297 p_data => x_msg_data
1298 );
1299 WHEN OTHERS THEN
1300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1301 l_error_tbl := x_error_tbl;
1302 BIS_UTILITIES_PVT.Add_Error_Message
1303 ( p_error_table => l_error_Tbl
1304 , p_error_msg_id => SQLCODE
1305 , p_error_description => SQLERRM
1306 , x_error_table => x_error_Tbl
1307 );
1308 FND_MSG_PUB.Count_And_Get
1309 ( p_count => x_msg_count,
1310 p_data => x_msg_data
1311 );
1312
1313 END Retrieve_Org_Where_Clause;
1314
1315 -- Time is related to organiztion
1316 -- New procedure to take in org_id directly
1317 Procedure Retrieve_Time_Where_Clause
1318 ( p_time_dim_level_short_name IN VARCHAR2
1319 , p_org_dim_level_short_name IN VARCHAR2
1320 , p_org_id IN VARCHAR2
1321 , x_where_clause OUT NOCOPY VARCHAR2
1322 , x_return_status OUT NOCOPY VARCHAR2
1323 , x_msg_count OUT NOCOPY VARCHAR2
1324 , x_msg_data OUT NOCOPY VARCHAR2
1325 )
1326 IS
1327 l_where_clause VARCHAR2(32000);
1328 x_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1329 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1330 BEGIN
1331
1332 FND_MSG_PUB.initialize;
1333
1334 IF(p_time_dim_level_short_name <> 'TOTAL_TIME')
1335 AND (is_time_dependent_on_org(p_time_lvl_short_name => p_time_dim_level_short_name)
1336 = BIS_UTILITIES_PUB.G_TIME_IS_DEPEN_ON_ORG) THEN --2684911
1337 l_where_clause :=
1338 'organization_id = '|| '''' || p_org_id || '''' || ' and organization_type = '
1339 || '''' || p_org_dim_level_short_name || '''';
1340 ELSE
1341 l_where_clause := '""';
1342 END IF;
1343
1344 x_where_clause := l_where_clause;
1345 x_return_status := 'S';
1346 EXCEPTION
1347 WHEN FND_API.G_EXC_ERROR THEN
1348 x_return_status := FND_API.G_RET_STS_ERROR;
1349 l_error_tbl := x_error_tbl;
1350 BIS_UTILITIES_PVT.Add_Error_Message
1351 ( p_error_table => l_error_Tbl
1352 , p_error_msg_id => SQLCODE
1353 , p_error_description => SQLERRM
1354 , x_error_table => x_error_Tbl
1355 );
1356 FND_MSG_PUB.Count_And_Get
1357 ( p_count => x_msg_count,
1358 p_data => x_msg_data
1359 );
1360 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1362 l_error_tbl := x_error_tbl;
1363 BIS_UTILITIES_PVT.Add_Error_Message
1364 ( p_error_table => l_error_Tbl
1365 , p_error_msg_id => SQLCODE
1366 , p_error_description => SQLERRM
1367 , x_error_table => x_error_Tbl
1368 );
1369 FND_MSG_PUB.Count_And_Get
1370 ( p_count => x_msg_count,
1371 p_data => x_msg_data
1372 );
1373 WHEN OTHERS THEN
1374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1375 l_error_tbl := x_error_tbl;
1376 BIS_UTILITIES_PVT.Add_Error_Message
1377 ( p_error_table => l_error_Tbl
1378 , p_error_msg_id => SQLCODE
1379 , p_error_description => SQLERRM
1380 , x_error_table => x_error_Tbl
1381 );
1382 FND_MSG_PUB.Count_And_Get
1383 ( p_count => x_msg_count,
1384 p_data => x_msg_data
1385 );
1386 END Retrieve_Time_Where_Clause;
1387
1388 FUNCTION is_time_dependent_on_org
1389 ( p_time_lvl_short_name IN VARCHAR2)
1390 RETURN NUMBER IS
1391 BEGIN
1392
1393 FOR i IN 1..Tlist.count LOOP
1394 IF Tlist(i) = p_time_lvl_short_name THEN
1395 RETURN 1;
1396 END IF;
1397 END LOOP;
1398 RETURN 0;
1399 EXCEPTION
1400 WHEN OTHERS THEN
1401 RETURN 0;
1402 END is_time_dependent_on_org;
1403
1404
1405 FUNCTION is_org_dependent_on_resp ( p_org_lvl_short_name IN VARCHAR2) RETURN NUMBER IS
1406 BEGIN
1407 FOR i IN 1..Olist.count LOOP
1408 IF Olist(i) = p_org_lvl_short_name THEN
1409 return 1;
1410 END IF;
1411 END LOOP;
1412 RETURN 0;
1413 EXCEPTION
1414 WHEN OTHERS THEN
1415 RETURN 0;
1416 END is_org_dependent_on_resp;
1417
1418 PROCEDURE get_time_where_clause(
1419 p_dim_level_short_name IN VARCHAR2
1420 ,p_parent_level_short_name IN VARCHAR2
1421 ,p_parent_level_id IN VARCHAR2
1422 ,p_source IN VARCHAR2
1423 ,x_where_clause OUT NOCOPY VARCHAR2
1424 ,x_return_status OUT NOCOPY VARCHAR2
1425 ,x_err_count OUT NOCOPY NUMBER
1426 ,x_errorMessage OUT NOCOPY VARCHAR2
1427 ) IS
1428
1429 l_where_clause VARCHAR2(32000);
1430 l_first_quote_pos NUMBER(10);
1431 BEGIN
1432 IF is_time_dependent_on_org(p_dim_level_short_name) <> 1 THEN
1433 x_where_clause := NULL;
1434 x_return_status := 'S';
1435 x_err_count := 0;
1436 x_errorMessage := '';
1437 ELSE
1438 -- the old(non-dbc) time dimension levels now, this also includes EDW_TIME_CAL_PERIOD
1439 IF ( (p_parent_level_short_name IS NULL) OR (p_parent_level_id IS NULL)) THEN
1440 RAISE e_invalid_parent;
1441 ELSE
1442 BIS_UTILITIES_PUB.Retrieve_Time_Where_Clause(
1443 P_TIME_DIM_LEVEL_SHORT_NAME => p_dim_level_short_name
1444 ,P_ORG_DIM_LEVEL_SHORT_NAME => p_parent_level_short_name
1445 ,P_ORG_ID => p_parent_level_id
1446 ,X_WHERE_CLAUSE => l_where_clause
1447 ,X_RETURN_STATUS => x_return_status
1448 ,X_MSG_COUNT => x_err_count
1449 ,X_MSG_DATA => x_errorMessage
1450 );
1451
1452 -- Strip off the ""
1453 IF ( (l_where_clause IS NOT NULL) AND
1454 (l_where_clause <> '""')) THEN
1455 l_where_clause := TRIM(l_where_clause);
1456 l_first_quote_pos := instr(l_where_clause, '"');
1457 IF (l_first_quote_pos <> 0) THEN -- there is "" exists
1458 l_where_clause := SUBSTR(l_where_clause, (l_first_quote_pos+1));
1459 l_where_clause := SUBSTR(l_where_clause,1,(LENGTH(l_where_clause)-1));
1460 END IF;
1461 x_where_clause := l_where_clause ;
1462 ELSE -- NULL or "" now
1463 x_where_clause := NULL;
1464 END IF;
1465 END IF;
1466 END IF;
1467 RETURN;
1468 EXCEPTION
1469 WHEN e_invalid_parent THEN
1470 null;
1471 WHEN OTHERS THEN
1472 null;
1473 END get_time_where_clause;
1474
1475 PROCEDURE get_org_where_clause(
1476 p_usr_id IN NUMBER
1477 ,p_dim_level_short_name IN VARCHAR2
1478 ,x_where_clause OUT NOCOPY VARCHAR2
1479 ,x_return_status OUT NOCOPY VARCHAR2
1480 ,x_err_count OUT NOCOPY NUMBER
1481 ,x_errorMessage OUT NOCOPY VARCHAR2
1482 ) IS
1483 l_where_clause VARCHAR2(32000);
1484 l_first_quote_pos NUMBER(10);
1485 BEGIN
1486 IF is_org_dependent_on_resp(p_dim_level_short_name) <> 1 THEN
1487 x_where_clause := NULL;
1488 x_return_status := 'S';
1489 x_err_count := 0;
1490 x_errorMessage := '';
1491 ELSE
1492 -- the old(non-dbc) time dimension levels now, this also includes EDW_TIME_CAL_PERIOD
1493 IF p_usr_id IS NULL THEN
1494 RAISE e_invalid_user;
1495 ELSE
1496 BIS_UTILITIES_PUB.Retrieve_Org_Where_Clause(
1497 p_user_id => p_usr_id
1498 , p_dimension_level_short_name => p_dim_level_short_name
1499 , x_where_clause => l_where_clause
1500 );
1501 -- Strip off the ""
1502 IF ( (l_where_clause IS NOT NULL) AND
1503 (l_where_clause <> '""')) THEN
1504 l_where_clause := TRIM(l_where_clause);
1505 l_first_quote_pos := instr(l_where_clause, '"');
1506 l_where_clause := SUBSTR(l_where_clause, (l_first_quote_pos+1));
1507
1508 l_where_clause := SUBSTR(l_where_clause,1,(LENGTH(l_where_clause)-1));
1509 x_where_clause := l_where_clause ;
1510 ELSE
1511 x_where_clause := NULL;
1512 END IF;
1513 END IF;
1514 END IF;
1515 RETURN;
1516 EXCEPTION
1517 WHEN e_invalid_user THEN
1518 null;
1519 WHEN OTHERS THEN
1520 null;
1521 END get_org_where_clause;
1522
1523
1524 -- return Edw time levels from the Tlist
1525
1526 FUNCTION get_edw_org_dep_time_levels RETURN VARCHAR2 IS
1527 l_edw_org_dep_time_levels VARCHAR2(32000) := null;
1528 BEGIN
1529 FOR i IN 1..Tlist.count LOOP
1530 IF ( SUBSTR(Tlist(i),1,3) = 'EDW' ) THEN
1531 IF ( l_edw_org_dep_time_levels IS NOT NULL) THEN
1532 l_edw_org_dep_time_levels := l_edw_org_dep_time_levels ||','|| Tlist(i);
1533 ELSE
1534 l_edw_org_dep_time_levels := Tlist(i);
1535 END IF;
1536 END IF;
1537 END LOOP;
1538 RETURN l_edw_org_dep_time_levels ;
1539 EXCEPTION
1540 WHEN OTHERS THEN
1541 RETURN null;
1542 END get_edw_org_dep_time_levels ;
1543
1544
1545
1546
1547 --
1548 -- The following api is called (once per program) to check value of
1549 -- the profile option 'BIS_PMF_DEBUG'. This profile will be set
1550 -- from Jintiator Forms.
1551 -- Will code SET_debug_mode_profile if needed.
1552 --
1553 PROCEDURE get_debug_mode_profile -- 2694978
1554 ( x_is_debug_mode OUT NOCOPY BOOLEAN
1555 , x_return_status OUT NOCOPY VARCHAR2
1556 , x_return_msg OUT NOCOPY VARCHAR2
1557 ) IS
1558 BEGIN
1559
1560 x_return_status := FND_API.G_RET_STS_SUCCESS;
1561 x_return_msg := NULL;
1562 bis_utilities_pvt.get_debug_mode_profile(
1563 x_is_debug_mode => x_is_debug_mode
1564 , x_return_status => x_return_status
1565 , x_return_msg => x_return_msg
1566 );
1567
1568 EXCEPTION
1569 WHEN OTHERS THEN
1570 x_return_status := FND_API.G_RET_STS_ERROR;
1571 x_return_msg := 'Error in setting debug log flag in BIS_UTILITIES_PUB.get_debug_mode_profile: '|| SQLERRM;
1572 x_is_debug_mode := FALSE;
1573 END get_debug_mode_profile ;
1574
1575
1576 --
1577 -- The following api is called (once per program) to set the value of
1578 -- debug flag value.
1579 --
1580 PROCEDURE set_debug_log_flag ( -- 2694978
1581 p_is_true IN BOOLEAN
1582 , x_return_status OUT NOCOPY VARCHAR2
1583 , x_return_msg OUT NOCOPY VARCHAR2
1584 )
1585 IS
1586 l_return_status VARCHAR2(1000) := FND_API.G_RET_STS_SUCCESS;
1587 l_return_msg VARCHAR2(10000) := NULL;
1588 BEGIN
1589
1590 x_return_status := FND_API.G_RET_STS_SUCCESS;
1591 x_return_msg := NULL;
1592
1593 bis_utilities_pvt.set_debug_log_flag(
1594 p_is_true => p_is_true
1595 , x_return_status => x_return_status
1596 , x_return_msg => x_return_msg );
1597
1598
1599 EXCEPTION
1600 WHEN OTHERS THEN
1601 x_return_status := FND_API.G_RET_STS_ERROR;
1602 x_return_msg := 'Error in setting debug log flag in BIS_UTILITIES_PUB.set_debug_log_flag: '|| SQLERRM;
1603 END set_debug_log_flag;
1604
1605
1606
1607 FUNCTION is_debug_on -- x in varchar2) -- 2694978
1608 RETURN BOOLEAN
1609 IS
1610 BEGIN
1611
1612 RETURN bis_utilities_pvt.is_debug_on();
1613
1614 EXCEPTION
1615 WHEN OTHERS THEN
1616 RETURN FALSE;
1617 END is_debug_on;
1618
1619
1620
1621 PROCEDURE open_debug_log ( -- 2694978
1622 p_file_name IN VARCHAR2,
1623 p_dir_name IN VARCHAR2,
1624 x_return_status OUT NOCOPY VARCHAR2,
1625 x_return_msg OUT NOCOPY VARCHAR2)
1626 IS
1627 BEGIN
1628 x_return_status := FND_API.G_RET_STS_SUCCESS;
1629 x_return_msg := NULL;
1630
1631 bis_utilities_pvt.open_debug_log ( -- 2694978
1632 p_file_name => p_file_name ,
1633 p_dir_name => p_dir_name,
1634 x_return_status => x_return_status,
1635 x_return_msg => x_return_msg);
1636
1637 EXCEPTION
1638 WHEN OTHERS THEN
1639 x_return_status := FND_API.G_RET_STS_ERROR;
1640 x_return_msg := 'Error in setting debug log flag in BIS_UTILITIES_PUB.open_debug_log: '|| SQLERRM;
1641 END open_debug_log;
1642
1643
1644
1645 PROCEDURE close_debug_log ( -- 2694978
1646 p_file_name IN VARCHAR2,
1647 p_dir_name IN VARCHAR2,
1648 x_return_status OUT NOCOPY VARCHAR2,
1649 x_return_msg OUT NOCOPY VARCHAR2)
1650 IS
1651 BEGIN
1652 x_return_status := FND_API.G_RET_STS_SUCCESS;
1653 x_return_msg := NULL;
1654
1655 bis_utilities_pvt.close_debug_log(
1656 p_file_name => p_file_name
1657 , p_dir_name => p_dir_name
1658 , x_return_status => x_return_status
1659 , x_return_msg => x_return_msg);
1660
1661 EXCEPTION
1662 WHEN OTHERS THEN
1663 x_return_status := FND_API.G_RET_STS_ERROR;
1664 x_return_msg := 'Error in setting debug log flag in BIS_UTILITIES_PUB.close_debug_log: '|| SQLERRM;
1665 END close_debug_log;
1666
1667
1668
1669 PROCEDURE put(p_text IN VARCHAR2) -- 2694978
1670 IS
1671 BEGIN
1672 bis_utilities_pvt.put(p_text => p_text);
1673 EXCEPTION
1674 WHEN OTHERS THEN
1675 NULL;
1676 END put;
1677
1678
1679 PROCEDURE put_line(p_text IN VARCHAR2) -- 2694978
1680 IS
1681 BEGIN
1682 bis_utilities_pvt.put_line(p_text => p_text);
1683 EXCEPTION
1684 WHEN OTHERS THEN
1685 NULL;
1686 END put_line;
1687
1688 -- get the oracle database version.
1689 -- bug#3374352
1690 FUNCTION Get_DB_Version RETURN NUMBER IS
1691 l_instance_role VARCHAR2(18);
1692 l_db_ver NUMBER;
1693 BEGIN
1694 l_instance_role := 'PRIMARY_INSTANCE';
1695
1696 -- Get the db version before the first '.'
1697 SELECT TO_NUMBER(substr(trim(VI.version), 1, instr(trim(VI.version), '.', 1, 1) - 1)) INTO l_db_ver
1698 FROM v$instance VI
1699 WHERE VI.instance_role = l_instance_role;
1700
1701 RETURN l_db_ver;
1702
1703 END Get_DB_Version;
1704
1705
1706 /******************************************************************************
1707 * Function Enable_Auto_Generated() returns
1708 *
1709 * 1) "T" if profile "XXX" is set to "Yes"
1710 *
1711 * 2) "F" if profile "XXX" is set to "No" or is NULL
1712 *
1713 * Added for Bug#3767188
1714 * Modified for Bug#3788314
1715 *
1716 ******************************************************************************/
1717
1718 FUNCTION Enable_Auto_Generated
1719 RETURN VARCHAR2 IS
1720 l_Profile_Value VARCHAR2(255);
1721 l_Return_Value VARCHAR2(1);
1722 BEGIN
1723 l_Return_Value := 'F';
1724
1725 l_Profile_Value := FND_PROFILE.VALUE_SPECIFIC(
1726 NAME => BIS_UTILITIES_PUB.G_ENABLE_AUTOGEN_PROFILE_NAME
1727 , USER_ID => NULL
1728 , RESPONSIBILITY_ID => FND_GLOBAL.RESP_ID
1729 , APPLICATION_ID => NULL
1730 , ORG_ID => NULL
1731 , SERVER_ID => NULL
1732 );
1733
1734 l_Profile_Value := UPPER(NVL(l_Profile_Value, 'N'));
1735
1736 IF (l_Profile_Value = 'Y') THEN
1737 l_Return_Value := 'T';
1738 ELSE
1739 l_Return_Value := 'F';
1740 END IF;
1741
1742 RETURN l_Return_Value;
1743 EXCEPTION
1744 WHEN OTHERS THEN
1745 l_Return_Value := 'F';
1746
1747 RETURN l_Return_Value;
1748 END Enable_Auto_Generated;
1749
1750 /******************************************************************************
1751 * Function Is_Func_Enabled
1752 *
1753 * 1) "T" if the user (fnd_global.user_id) has access to the function p_Function_Name.
1754 * 2) "F" if the user does not have access to the function p_function_name
1755 *
1756 *
1757 ******************************************************************************/
1758
1759 FUNCTION Is_Func_Enabled (
1760 p_Function_Name IN VARCHAR2
1761 ) RETURN VARCHAR2 IS
1762
1763 l_Function_Name FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE;
1764 l_Function_Id FND_FORM_FUNCTIONS.FUNCTION_ID%TYPE;
1765 l_Menu_Id FND_MENUS.MENU_ID%TYPE;
1766 l_User_Id FND_USER.USER_ID%TYPE;
1767
1768 l_Has_User_Access BOOLEAN;
1769 l_Return_Status VARCHAR2(1);
1770
1771
1772 CURSOR c_Resp_Menus IS
1773 SELECT R.MENU_ID
1774 FROM FND_RESPONSIBILITY_VL R,
1775 FND_USER_RESP_GROUPS U
1776 WHERE U.USER_ID = FND_GLOBAL.USER_ID
1777 AND R.VERSION = 'W'
1778 AND U.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
1779 AND U.START_DATE <= SYSDATE
1780 AND (U.END_DATE IS NULL OR U.END_DATE >= SYSDATE)
1781 AND R.START_DATE <= SYSDATE
1782 AND (R.END_DATE IS NULL OR R.END_DATE >= SYSDATE);
1783
1784 BEGIN
1785 l_Has_User_Access := FALSE;
1786 l_Return_Status := 'F';
1787 l_Function_Name := p_Function_Name;
1788
1789 -- Internally manage the no-data-found issue. If Measure definer function is not available
1790 BEGIN
1791
1792 SELECT FUNCTION_ID
1793 INTO l_Function_Id
1794 FROM FND_FORM_FUNCTIONS
1795 WHERE FUNCTION_NAME = l_Function_Name;
1796
1797 EXCEPTION
1798 WHEN OTHERS THEN
1799 l_Function_Id := NULL;
1800 END;
1801
1802 -- Loop thro the responsibility menus
1803 IF (l_Function_Id IS NOT NULL) THEN
1804 FOR cRespMenu IN c_Resp_Menus LOOP
1805 IF (FND_FUNCTION.IS_FUNCTION_ON_MENU(cRespMenu.MENU_ID, l_Function_Id)) THEN
1806 l_Has_User_Access := TRUE;
1807 EXIT;
1808 END IF;
1809 END LOOP;
1810 END IF;
1811
1812 IF (l_Has_User_Access = TRUE) THEN
1813 l_Return_Status := 'T';
1814 ELSE
1815 l_Return_Status := 'F';
1816 END IF;
1817
1818 RETURN l_Return_Status;
1819
1820 EXCEPTION
1821 WHEN OTHERS THEN
1822 l_Return_Status := 'F';
1823
1824 RETURN l_Return_Status;
1825 END Is_Func_Enabled;
1826
1827 /******************************************************************************
1828 * FUNCTION Enable_Custom_Kpi()
1829 *
1830 * 1) "T" if the function Enable_Auto_Generated returns "T" (true) and
1831 * the current accessing user has access to the following FND form functions
1832 * - BSC_PMD_MD_SELECTMEASURE_PGE
1833 * - BSC_BID_SELECTMEASURE_PGE
1834 *
1835 * 2) "F" if any of the above conditions fail
1836 *
1837 ******************************************************************************/
1838
1839 FUNCTION Enable_Custom_Kpi
1840 RETURN VARCHAR2 IS
1841 l_Return_Value VARCHAR2(1);
1842 BEGIN
1843 l_Return_Value := 'F';
1844
1845 IF ((BIS_UTILITIES_PUB.Enable_Auto_Generated = 'T') AND
1846 ((BIS_UTILITIES_PUB.Is_Func_Enabled(BIS_UTILITIES_PUB.G_BIA_MEAS_DEFINER_FUNCTION) = 'T') OR
1847 (BIS_UTILITIES_PUB.Is_Func_Enabled(BIS_UTILITIES_PUB.G_MEAS_DEFINER_FORM_FUNCTION)= 'T'))) THEN
1848 l_Return_Value := 'T';
1849 ELSE
1850 l_Return_Value := 'F';
1851 END IF;
1852
1853 RETURN l_Return_Value;
1854 EXCEPTION
1855 WHEN OTHERS THEN
1856 l_Return_Value := 'F';
1857
1858 RETURN l_Return_Value;
1859 END Enable_Custom_Kpi;
1860
1861 FUNCTION Enable_Generated_Source_Report
1862 RETURN VARCHAR2 IS
1863 l_Return_Value VARCHAR2(1);
1864 l_property_value bsc_sys_init.property_value%TYPE:=NULL ;
1865 BEGIN
1866
1867 -- check bsc_sys_init for flag
1868 BEGIN
1869 SELECT property_value
1870 INTO l_property_value
1871 FROM bsc_sys_init
1872 WHERE property_code = G_ENABLE_GEN_SOURCE_REPORT;
1873 EXCEPTION
1874 WHEN NO_DATA_FOUND THEN
1875 l_property_value := NULL;
1876 END;
1877
1878 IF (l_property_value IS NULL) THEN
1879 l_Return_Value := 'F';
1880 ELSE
1881 l_Return_Value := 'T';
1882 END IF;
1883
1884 RETURN l_Return_Value;
1885 EXCEPTION
1886 WHEN OTHERS THEN
1887 l_Return_Value := 'F';
1888
1889 RETURN l_Return_Value;
1890 END Enable_Generated_Source_Report;
1891
1892 /******************************************************************************/
1893 FUNCTION Get_Owner_Id(p_name IN VARCHAR2) RETURN NUMBER IS
1894 BEGIN
1895 IF p_name = BIS_UTILITIES_PUB.G_CUSTOM_OWNER THEN
1896 RETURN FND_GLOBAL.USER_ID;
1897 ELSE
1898 RETURN FND_LOAD_UTIL.OWNER_ID(p_name);
1899 END IF;
1900 EXCEPTION
1901 WHEN OTHERS THEN
1902 RETURN FND_GLOBAL.USER_ID;
1903 END Get_Owner_Id;
1904
1905
1906 /******************************************************************************/
1907 FUNCTION Get_Owner_Name(p_id IN NUMBER)
1908 RETURN VARCHAR2 IS
1909 BEGIN
1910 IF ((p_id = 0) OR (p_id = 1)) THEN
1911 RETURN 'SEED';
1912 ELSE
1913 RETURN FND_LOAD_UTIL.OWNER_NAME(p_id);
1914 END IF;
1915
1916 EXCEPTION
1917 WHEN OTHERS THEN
1918 RETURN FND_LOAD_UTIL.OWNER_NAME(-1);
1919 END Get_Owner_Name;
1920
1921 END BIS_UTILITIES_PUB;