[Home] [Help]
PACKAGE BODY: APPS.BIS_UTIL
Source
1 package body BIS_UTIL as
2 /* $Header: BISUTILB.pls 120.32 2007/12/27 13:36:24 lbodired ship $ */
3
4 --/////////////////Added for Simulation Enhancement /////////////////
5
6 FUNCTION Is_Simulation_Report
7 (
8 p_region_code IN ak_regions.region_code%TYPE
9 )RETURN VARCHAR2 IS
10 l_sim_flag VARCHAR2(10);
11 l_config_type bsc_kpis_b.config_type%TYPE;
12
13 BEGIN
14
15 l_sim_flag := FND_API.G_FALSE;
16 IF(p_region_code IS NOT NULL) THEN
17 SELECT config_type
18 INTO l_config_type
19 FROM bsc_kpis_b
20 WHERE short_name =p_region_code;
21
22 IF(l_config_type = 7) THEN
23 l_sim_flag := FND_API.G_TRUE;
24 END IF;
25 END IF;
26
27 RETURN l_sim_flag;
28
29 EXCEPTION
30 WHEN OTHERS THEN
31 RETURN FND_API.G_FALSE;
32 END Is_Simulation_Report;
33
34
35
36 FUNCTION get_menu_resp_id(
37 p_sub_menu_id IN NUMBER
38 , p_user_id IN NUMBER
39 , p_count IN NUMBER
40 )
41 RETURN NUMBER;
42
43 FUNCTION get_sec_grp_id_for_resp_role (
44 p_role_resp IN VARCHAR2
45 )
46 RETURN NUMBER;
47
48 FUNCTION get_sec_grp_id_for_user_role (
49 p_resp_id IN NUMBER
50 , p_user_id IN NUMBER
51 )
52 RETURN NUMBER;
53
54 Procedure Validate_Short_Name (
55 p_short_name IN VARCHAR2
56 ,x_return_status OUT NOCOPY Varchar2
57 ,x_msg_count OUT NOCOPY NUMBER
58 ,x_msg_data OUT NOCOPY Varchar2 )
59 IS
60 x_string VARCHAR2(30);
61 x_char VARCHAR2(1);
62 BEGIN
63
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65 IF p_short_name IS NULL THEN
66 RETURN;
67 END IF;
68
69 -- user entered short_name, use appropriate length check
70 IF length(p_short_name) > BIS_UTIL.G_SHORT_NAME_LEN THEN
71 FND_MESSAGE.Set_Name('BIS','BIS_SHORT_NAME_LEN');
72 fnd_msg_pub.add;
73 RAISE FND_API.G_EXC_ERROR;
74 END IF;
75
76 -- character checking
77 --
78 x_string := p_short_name;
79 FOR i IN 1..length(x_string) LOOP
80 x_char := SUBSTR(x_string, i, 1);
81 IF (i=1) THEN
82 -- first character should be an alphabet
83 IF (INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ', x_char) < 1) THEN
84 FND_MESSAGE.Set_Name('BIS','BIS_SHORT_NAME_INVALID_CHAR');
85 fnd_msg_pub.add;
86 RAISE FND_API.G_EXC_ERROR;
87 END IF;
88 ELSE
89 -- check all other characters
90 IF (INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890_$#', x_char) < 1) THEN
91 FND_MESSAGE.Set_Name('BIS','BIS_SHORT_NAME_INVALID_CHAR');
92 fnd_msg_pub.add;
93 RAISE FND_API.G_EXC_ERROR;
94 END IF;
95 END IF;
96 END LOOP;
97
98
99 EXCEPTION
100
101 WHEN FND_API.G_EXC_ERROR THEN
102
103 x_return_status := FND_API.G_RET_STS_ERROR;
104 -- Get message count and data
105 FND_MSG_PUB.Count_And_Get
106 ( p_count => x_msg_count
107 , p_data => x_msg_data
108 );
109
110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
111
112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
113
114 -- Get message count and data
115
116 FND_MSG_PUB.Count_And_Get
117 ( p_count => x_msg_count
118 , p_data => x_msg_data
119 );
120
121 WHEN OTHERS THEN
122
123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
124
125 FND_MSG_PUB.Add_Exc_Msg
126 ( G_PKG_NAME
127 , 'Validate_Short_Name'
128 );
129
130 -- Get message count and data
131
132 FND_MSG_PUB.Count_And_Get
133 ( p_count => x_msg_count
134 , p_data => x_msg_data
135 );
136
137 END Validate_Short_Name;
138
139 --
140 -- For 11.5.1 Corrective actions with BIS Reports
141 --
142
143 /* Should really make wf attributes more flexible */
144
145 Procedure Start_Workflow
146 (p_exception_message IN Varchar2
147 ,p_msg_subject IN Varchar2
148 ,p_exception_date IN date
149 ,p_item_type IN Varchar2
150 ,p_wf_process IN Varchar2
151 ,p_notify_resp_name IN Varchar2
152 ,p_BIS_Report_Tbl IN BIS_UTIL.BIS_Report_Tbl_Type
153 ,p_BIS_Cached_Report_Tbl IN BIS_UTIL.BIS_Cached_Report_Tbl_Type
154 ,x_return_status OUT NOCOPY Varchar2
155 )
156 IS
157 l_live_url_tbl BIS_UTIL.Report_URL_Tbl_Type;
158 l_cached_url_tbl BIS_UTIL.Report_URL_Tbl_Type;
159 l_count NUMBER := 0;
160
161 BEGIN
162
163 x_return_status := FND_API.G_RET_STS_SUCCESS;
164
165 FOR i IN 1..p_BIS_Report_Tbl.Count LOOP
166
167 BIS_UTIL.Build_Report_URL
168 ( p_report_type => p_BIS_Report_Tbl(i).Report_Type
169 , p_reportFn_name => p_BIS_Report_Tbl(i).reportFN_Name
170 , p_region_code => p_BIS_Report_Tbl(i).region_code
171 , p_report_resp_id => p_BIS_Report_Tbl(i).report_resp_id
172 , p_report_params => p_BIS_Report_Tbl(i).report_params
173 , x_report_url => l_live_url_tbl(i)
174 , x_return_status => x_return_status
175 );
176
177 END LOOP;
178
179
180 FOR i IN 1..p_BIS_Cached_Report_Tbl.Count LOOP
181
182 BIS_UTIL.Build_Report_URL
183 ( p_report_type => p_BIS_Cached_Report_Tbl(i).Report_Type
184 , p_report_identifier => p_BIS_Cached_Report_Tbl(i).report_identifier
185 , x_report_url => l_cached_url_tbl(i)
186 , x_return_status => x_return_status
187 );
188 END LOOP;
189
190
191 BIS_UTIL.Start_Workflow_Engine
192 ( p_exception_message => p_exception_message
193 , p_msg_subject => p_msg_subject
194 , p_exception_date => p_exception_date
195 , p_item_type => p_item_type
196 , p_wf_process => p_wf_process
197 , p_notify_resp_name => p_notify_resp_name
198 , p_live_report_url_tbl => l_live_url_tbl
199 , p_cached_report_url_tbl => l_cached_url_tbl
200 , x_return_status => x_return_status
201 );
202
203
204 EXCEPTION
205 WHEN OTHERS THEN
206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
207
208 END Start_Workflow;
209
210 -- For 11.5.1 Corrective actions with Oracle Reports
211 --
212 Procedure Start_Workflow
213 (p_exception_message IN Varchar2
214 ,p_msg_subject IN Varchar2
215 ,p_exception_date IN date
216 ,p_item_type IN Varchar2
217 ,p_wf_process IN Varchar2
218 ,p_notify_resp_name IN Varchar2
219 ,p_Oracle_Report_Tbl IN BIS_UTIL.Oracle_Report_Tbl_Type
220 ,x_return_status OUT NOCOPY Varchar2
221 )
222 IS
223 l_url_tbl BIS_UTIL.Report_URL_Tbl_Type;
224
225 BEGIN
226
227 FOR i IN 1..p_Oracle_Report_Tbl.Count LOOP
228 BIS_UTIL.Build_Report_URL
229 ( p_report_type => p_Oracle_Report_Tbl(i).Report_Type
230 , p_report_name => p_Oracle_Report_Tbl(i).report_name
231 , p_report_params => p_Oracle_Report_Tbl(i).report_params
232 , p_report_resp_id => p_Oracle_Report_Tbl(i).report_resp_id
233 , x_report_url => l_url_tbl(i)
234 , x_return_status => x_return_status
235 );
236 END LOOP;
237
238 BIS_UTIL.Start_Workflow_Engine
239 ( p_exception_message => p_exception_message
240 , p_msg_subject => p_msg_subject
241 , p_exception_date => p_exception_date
242 , p_item_type => p_item_type
243 , p_wf_process => p_wf_process
244 , p_notify_resp_name => p_notify_resp_name
245 , p_live_report_url_tbl => l_url_tbl
246 , x_return_status => x_return_status
247 );
248
249 EXCEPTION
250 WHEN OTHERS THEN
251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
252
253 END Start_Workflow;
254
255 -- For 11.5 Corrective actions with Oracle Reports
256 -- Added resp_application_id for bug#3756093
257 -- Added new parameters at last to support old code calling this
258 -- API by numbers
259 Procedure Strt_Wf_Process
260 (p_exception_message Varchar2
261 ,p_msg_subject Varchar2
262 ,p_exception_date date
263 ,p_item_type Varchar2
264 ,p_wf_process Varchar2
265 ,p_notify_resp_name Varchar2
266 ,p_report_name1 Varchar2 default null
267 ,p_report_param1 Varchar2 default null
268 ,p_report_resp1_id number default null
269 ,p_report_name2 Varchar2 default null
270 ,p_report_param2 Varchar2 default null
271 ,p_report_resp2_id number default null
272 ,p_report_name3 Varchar2 default null
273 ,p_report_param3 Varchar2 default null
274 ,p_report_resp3_id number default null
275 ,p_report_name4 Varchar2 default null
276 ,p_report_param4 Varchar2 default null
277 ,p_report_resp4_id number default null
278 ,x_return_status OUT NOCOPY Varchar2
279 ,p_report_app1_id NUMBER default null
280 ,p_report_app2_id NUMBER default null
281 ,p_report_app3_id NUMBER default null
282 ,p_report_app4_id NUMBER default null
283 )
284 IS
285 l_url_tbl BIS_UTIL.Report_URL_Tbl_Type;
286
287 BEGIN
288 x_return_status := FND_API.G_RET_STS_SUCCESS;
289
290 -- 11.5 only excepted up to 4 report URLs
291 --
292 BIS_UTIL.Build_Report_URL
293 ( p_report_type => BIS_UTIL.G_ORACLE_REPORT_TYPE
294 , p_report_name => p_report_name1
295 , p_report_params => p_report_param1
296 , p_report_resp_id => p_report_resp1_id
297 , p_report_app_id => p_report_app1_id
298 , x_report_url => l_url_tbl(1)
299 , x_return_status => x_return_status
300 );
301 BIS_UTIL.Build_Report_URL
302 ( p_report_type => BIS_UTIL.G_ORACLE_REPORT_TYPE
303 , p_report_name => p_report_name2
304 , p_report_params => p_report_param2
305 , p_report_resp_id => p_report_resp2_id
306 , p_report_app_id => p_report_app2_id
307 , x_report_url => l_url_tbl(2)
308 , x_return_status => x_return_status
309 );
310 BIS_UTIL.Build_Report_URL
311 ( p_report_type => BIS_UTIL.G_ORACLE_REPORT_TYPE
312 , p_report_name => p_report_name3
313 , p_report_params => p_report_param3
314 , p_report_resp_id => p_report_resp3_id
315 , p_report_app_id => p_report_app3_id
316 , x_report_url => l_url_tbl(3)
317 , x_return_status => x_return_status
318 );
319 BIS_UTIL.Build_Report_URL
320 ( p_report_type => BIS_UTIL.G_ORACLE_REPORT_TYPE
321 , p_report_name => p_report_name4
322 , p_report_params => p_report_param4
323 , p_report_resp_id => p_report_resp4_id
324 , p_report_app_id => p_report_app4_id
325 , x_report_url => l_url_tbl(4)
326 , x_return_status => x_return_status
327 );
328
329 BIS_UTIL.Start_Workflow_Engine
330 ( p_exception_message => p_exception_message
331 , p_msg_subject => p_msg_subject
332 , p_exception_date => p_exception_date
333 , p_item_type => p_item_type
334 , p_wf_process => p_wf_process
335 , p_notify_resp_name => p_notify_resp_name
336 , p_live_report_url_tbl => l_url_tbl
337 , x_return_status => x_return_status
338 );
339
340 EXCEPTION
341 WHEN OTHERS THEN
342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
343
344 END Strt_WF_Process;
345
346
347 -- 1.2.x corrective actions
348 -- Added resp_application_id for bug#3756093
349 -- Added new parameter at last to support old code calling this
350 -- API by numbers
351 Procedure Strt_Wf_Process
352 (p_exception_message Varchar2
353 ,p_msg_subject Varchar2
354 ,p_exception_date date
355 ,p_wf_process Varchar2
356 ,p_report_name1 Varchar2 default null
357 ,p_report_param1 Varchar2 default null
358 ,p_report_name2 Varchar2 default null
359 ,p_report_param2 Varchar2 default null
360 ,p_report_name3 Varchar2 default null
361 ,p_report_param3 Varchar2 default null
362 ,p_report_name4 Varchar2 default null
363 ,p_report_param4 Varchar2 default null
364 ,p_role Varchar2
365 ,p_responsibility_id number
366 ,x_return_status OUT NOCOPY Varchar2
367 ,p_application_id NUMBER default null)
368 IS
369 l_item_type Varchar2(30) := 'BISKPIWF';
370
371 Begin
372 x_return_status := FND_API.G_RET_STS_SUCCESS;
373
374 Strt_Wf_Process
375 (p_exception_message => p_exception_message
376 ,p_msg_subject => p_msg_subject
377 ,p_exception_date => p_exception_date
378 ,p_item_type => l_item_type
379 ,p_wf_process => p_wf_process
380 ,p_notify_resp_name => p_role
381 ,p_report_name1 => p_report_name1
382 ,p_report_param1 => p_report_param1
383 ,p_report_resp1_id => p_responsibility_id
384 ,p_report_app1_id => p_application_id
385 ,p_report_name2 => p_report_name2
386 ,p_report_param2 => p_report_param2
387 ,p_report_resp2_id => p_responsibility_id
388 ,p_report_app2_id => p_application_id
389 ,p_report_name3 => p_report_name3
390 ,p_report_param3 => p_report_param3
391 ,p_report_resp3_id => p_responsibility_id
392 ,p_report_app3_id => p_application_id
393 ,p_report_name4 => p_report_name4
394 ,p_report_param4 => p_report_param4
395 ,p_report_resp4_id => p_responsibility_id
396 ,p_report_app4_id => p_application_id
397 ,x_return_status => x_return_status
398 );
399
400 EXCEPTION
401 WHEN OTHERS THEN
402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
403
404 END strt_wf_process;
405
406 PROCEDURE Get_Time_Level_Value
407 ( p_Date IN DATE default SYSDATE
408 , p_Target_Level_ID IN NUMBER
409 , p_Organization_ID IN NUMBER
410 , x_Time_Level_Value OUT NOCOPY VARCHAR2
411 , x_Return_Status OUT NOCOPY VARCHAR2
412 )
413 IS
414
415 BEGIN
416
417 BIS_UTIL.Get_Time_Level_Value
418 ( p_Date => p_Date
419 , p_Target_Level_ID => p_Target_Level_id
420 , p_Organization_ID => TO_CHAR(p_Organization_ID)
421 , x_Time_Level_Value => x_Time_Level_value
422 , x_Return_Status => x_return_status
423 );
424
425 EXCEPTION
426
427 WHEN OTHERS THEN
428 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
429
430 END Get_Time_Level_Value;
431
432
433 PROCEDURE Get_Time_Level_Value
434 ( p_Date IN DATE default SYSDATE
435 , p_Target_Level_ID IN NUMBER
436 , p_Organization_ID IN VARCHAR2
437 , x_Time_Level_Value OUT NOCOPY VARCHAR2
438 , x_Return_Status OUT NOCOPY VARCHAR2
439 )
440 IS
441 l_period_set_name VARCHAR2(100) := NULL;
442 l_period_name VARCHAR2(100) := NULL;
443 l_id VARCHAR2(2500) := NULL;
444 l_view_name VARCHAR2(300);
445 l_time_level VARCHAR2(300);
446 l_org_level VARCHAR2(300);
447 l_select_stmt VARCHAR2(32000);
448 l_cursor INTEGER;
449 l_dummy INTEGER;
450 l_num_rows INTEGER;
451 l_Is_OrgRel BOOLEAN;
452
453 BEGIN
454 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
455
456 SELECT lt.short_name
457 , lo.short_name
458 , lt.LEVEL_VALUES_VIEW_NAME
459 INTO l_time_level
460 , l_org_level
461 , l_view_name
462 FROM BIS_LEVELS lt
463 , BIS_LEVELS lo
464 , BIS_TARGET_LEVELS tl
465 WHERE tl.Target_level_id = p_Target_Level_id
466 AND lt.Level_ID = tl.Time_Level_id
467 AND lo.Level_ID = tl.org_Level_id;
468
469 l_cursor := DBMS_SQL.OPEN_CURSOR;
470
471 -- the select statement depends on if the period_name is related to
472 -- the organization's set of books. If the organization is -1 (total_org)
473 -- then the default calendar is used.
474 --
475
476 IF l_time_level <> 'TOTAL_TIME' THEN
477 IF (BIS_UTILITIES_PUB.is_time_dependent_on_org(p_time_lvl_short_name => l_time_level)
478 = BIS_UTILITIES_PUB.G_TIME_IS_DEPEN_ON_ORG) THEN --2684911
479 l_Is_OrgRel := TRUE;
480 l_select_stmt := ' SELECT PERIOD_SET_NAME, PERIOD_NAME, ID '||' '
481 ||'FROM '||l_view_name||' '
482 ||'WHERE ORGANIZATION_ID = :p_Organization_ID '
483 ||'AND NVL(ORGANIZATION_TYPE, ''%'') LIKE :l_org_level '
484 ||' AND '
485 ||' TO_DATE(TO_CHAR(:p_Date,'||''''||'DD-MM-RR'||''''
486 ||'), '||''''||'DD-MM-RR'||''''||')'
487 ||' BETWEEN '
488 ||' TO_DATE(TO_CHAR(START_DATE,'||''''||'DD-MM-RR'||''''
489 ||'), '||''''||'DD-MM-RR'||''''||') and '
490 ||' TO_DATE(TO_CHAR(END_DATE,'||''''||'DD-MM-RR'||''''
491 ||'), '||''''||'DD-MM-RR'||''''||')';
492 ELSE
493 l_Is_OrgRel := FALSE;
494 l_select_stmt := ' SELECT PERIOD_SET_NAME, PERIOD_NAME, ID '||' '
495 ||'FROM '||l_view_name||' '
496 ||'WHERE :p_Date BETWEEN NVL(START_DATE,:p_Date) '
497 ||'AND NVL(END_DATE,:p_Date) ';
498
499 END IF;
500
501 DBMS_SQL.PARSE
502 ( c => l_cursor
503 , statement => l_select_stmt
504 , language_flag => DBMS_SQL.NATIVE
505 );
506
507 IF( l_Is_OrgRel) THEN
508 DBMS_SQL.BIND_VARIABLE
509 ( c => l_cursor
510 , name => ':p_Organization_ID'
511 , value => p_Organization_ID
512 );
513
514 DBMS_SQL.BIND_VARIABLE
515 ( c => l_cursor
516 , name => ':l_org_level'
517 , value => l_org_level
518 );
519
520 END IF;
521
522 DBMS_SQL.BIND_VARIABLE
523 ( c => l_cursor
524 , name => ':p_Date'
525 , value => p_date
526 );
527
528 DBMS_SQL.DEFINE_COLUMN
529 ( c => l_cursor
530 , position => 1
531 , column => l_period_set_name
532 , column_size => 100
533 );
534 DBMS_SQL.DEFINE_COLUMN
535 ( c => l_cursor
536 , position => 2
537 , column => l_period_name
538 , column_size => 100
539 );
540 DBMS_SQL.DEFINE_COLUMN
541 ( c => l_cursor
542 , position => 3
543 , column => l_id
544 , column_size => 2500
545 );
546
547 l_num_rows := DBMS_SQL.EXECUTE_AND_FETCH
548 ( c => l_cursor
549 , exact => TRUE
550 );
551
552 DBMS_SQL.COLUMN_VALUE
553 ( c => l_cursor
554 , position => 1
555 , value => l_period_set_name
556 );
557 DBMS_SQL.COLUMN_VALUE
558 ( c => l_cursor
559 , position => 2
560 , value => l_period_name
561 );
562 DBMS_SQL.COLUMN_VALUE
563 ( c => l_cursor
564 , position => 3
565 , value => l_id
566 );
567
568 DBMS_SQL.CLOSE_CURSOR(l_cursor);
569
570 ELSE
571 l_id := '-1';
572
573 END IF;
574
575 x_Time_Level_Value := l_id;
576
577 EXCEPTION
578 WHEN NO_DATA_FOUND THEN
579 -- No such time period exist.
580 DBMS_SQL.CLOSE_CURSOR(l_cursor);
581
582 x_Time_Level_Value := NULL;
583 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
584
585 WHEN TOO_MANY_ROWS THEN
586
587 -- More than one row, but still get the first record
588 DBMS_SQL.COLUMN_VALUE
589 ( c => l_cursor
590 , position => 1
591 , value => l_period_set_name
592 );
593 DBMS_SQL.COLUMN_VALUE
594 ( c => l_cursor
595 , position => 2
596 , value => l_period_name
597 );
598 x_Time_Level_Value := l_period_set_name||'+'||l_period_name;
599
600 DBMS_SQL.CLOSE_CURSOR(l_cursor);
601 x_Return_Status := FND_API.G_RET_STS_ERROR;
602
603 WHEN OTHERS THEN
604 DBMS_SQL.CLOSE_CURSOR(l_cursor);
605 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
606
607 END Get_Time_Level_Value;
608
609 Procedure Get_EPS
610 ( p_change_in_income in Number
611 ,p_change_in_eps out NOCOPY Number
612 ,p_result out NOCOPY Number
613 ,p_exception_msg OUT NOCOPY Varchar2) IS
614 l_outstanding_shares Number;
615 l_est_tax_rate Number;
616 Begin
617 p_result := 0;
618 p_change_in_eps := null;
619 p_exception_msg := null;
620 l_outstanding_shares := To_Number(FND_PROFILE.Value(
621 'BIS_EPS_SHARES_IN_ISSUE'));
622 l_est_tax_rate := To_Number(FND_PROFILE.Value(
623 'BIS_EPS_EST_TAX_RATE'));
624 if nvl(l_outstanding_shares,0) <= 0 then
625 FND_Message.Set_Name('BIS','BIS_SHARES_IN_ISSUE_UNDEFINED');
626 p_exception_msg := Fnd_Message.Get;
627 p_result := 1;
628 return;
629 end if;
630 if l_est_tax_rate is null then
631 p_result := 2;
632 FND_Message.Set_Name('BIS','BIS_SHORT_NAME_INVALID_CHAR');
633 p_exception_msg := Fnd_Message.Get;
634 return;
635 end if;
636 p_change_in_eps := (p_change_in_income *
637 (1-(l_est_tax_rate/100))) / l_outstanding_shares;
638 exception when others then
639 p_result := 3;
640 p_exception_msg := sqlcode || ':' || sqlerrm;
641 End Get_EPS;
642
643 function EPS_PRECISION_FORMAT_MASK(
644 CURRENCY_CODE IN VARCHAR2,
645 FIELD_LENGTH IN NUMBER
646 )
647 return VARCHAR2
648 IS
649 return_mask VARCHAR2(80);
650 precision NUMBER; /* number of digit to right of decimal */
651 ext_precision NUMBER; /* precision where more precision is needed */
652 min_acct_unit NUMBER; /* minimum value by which amt can vary */
653 bis_precision NUMBER; /* bis precision added to currency precision */
654 BEGIN
655
656 return_mask := NULL;
657 if (field_length > 80) OR (currency_code is NULL) then
658 return return_mask;
659 end if;
660 FND_CURRENCY.get_info(currency_code, precision, ext_precision,
661 min_acct_unit);
662 bis_precision := fnd_profile.value('BIS_EPS_PRECISION');
663 precision := precision + bis_precision;
664 FND_CURRENCY.build_format_mask(return_mask, field_length, precision,
665 min_acct_unit);
666 RETURN return_mask;
667
668 END EPS_PRECISION_FORMAT_MASK;
669
670 -- To start the workflow engine
671 --
672 Procedure Start_Workflow_Engine
673 (p_exception_message IN Varchar2
674 ,p_msg_subject IN Varchar2
675 ,p_exception_date IN date
676 ,p_item_type IN Varchar2
677 ,p_wf_process IN Varchar2
678 ,p_notify_resp_name IN Varchar2
679 ,p_live_report_url_tbl IN BIS_UTIL.Report_URL_Tbl_Type
680 Default G_DEF_Report_URL_Tbl
681 ,p_cached_report_url_tbl IN BIS_UTIL.Report_URL_Tbl_Type
682 Default G_DEF_Report_URL_Tbl
683 ,x_return_status OUT NOCOPY VARCHAR2
684 )
685 IS
686
687 l_wf_item_key NUMBER;
688 l_role_name VARCHAR2(80);
689
690 CURSOR c_role_name IS
691 select name from wf_role_lov_vl
692 where name = p_notify_resp_name;
693
694 BEGIN
695
696 x_return_status := FND_API.G_RET_STS_SUCCESS;
697
698 -- Validate item type and process
699 --
700 IF p_item_type IS NULL
701 OR p_wf_process IS NULL THEN
702 x_return_status := FND_API.G_RET_STS_ERROR;
703 RETURN;
704 END IF;
705
706 -- Validate wf_role
707 --
708 OPEN c_role_name;
709 FETCH c_role_name INTO l_role_name;
710 IF c_role_name%NOTFOUND THEN
711 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
712 RETURN;
713 END IF;
714
715 SELECT bis_excpt_wf_s.nextval
716 INTO l_wf_item_key
717 FROM dual;
718
719 -- create a new workflow process
720 --
721 wf_engine.CreateProcess(itemtype=>p_item_type
722 ,itemkey =>l_wf_item_key
723 ,process =>p_wf_process);
724
725 -- set the workflow attributes
726 --
727 wf_engine.SetItemAttrDate(itemtype=>p_item_type
728 ,itemkey =>l_wf_item_key
729 ,aname=>'L_EXCEPTION_DATE'
730 ,avalue=>p_exception_date);
731 wf_engine.SetItemAttrText(itemtype=>p_item_type
732 ,itemkey =>l_wf_item_key
733 ,aname=>'L_SUBJECT'
734 ,avalue=>P_MSG_SUBJECT);
735 wf_engine.SetItemAttrText(itemtype=>p_item_type
736 ,itemkey =>l_wf_item_key
737 ,aname=>'L_EXCEPTION_MESSAGE'
738 ,avalue=>p_EXCEPTION_MESSAGE);
739 wf_engine.SetItemAttrText(itemtype=>p_item_type
740 ,itemkey =>l_wf_item_key
741 ,aname=>'L_ROLE_NAME'
742 ,avalue=>L_ROLE_NAME);
743
744 FOR i IN 1..p_live_report_url_tbl.Count LOOP
745
746 -- need to modify to account for dynamic number of urls
747 --
748 -- Sets url for live report
749 --
750 IF p_live_report_url_tbl(p_live_report_url_tbl.FIRST) IS NOT NULL THEN
751
752 wf_engine.SetItemAttrText
753 ( itemtype =>p_item_type
754 , itemkey =>l_wf_item_key
755 , aname =>'L_URL'
756 , avalue =>p_live_report_url_tbl(p_live_report_url_tbl.FIRST)
757 );
758
759 END IF;
760
761 END LOOP;
762
763 FOR i IN 1..p_cached_report_url_tbl.Count LOOP
764
765 -- need to modify to account for dynamic number of urls
766 --
767
768 -- Sets url for cached report
769 --
770 IF p_cached_report_url_tbl(p_cached_report_url_tbl.FIRST) IS NOT NULL THEN
771
772 wf_engine.SetItemAttrText
773 ( itemtype =>p_item_type
774 , itemkey =>l_wf_item_key
775 , aname =>'L_URL2'
776 , avalue =>p_cached_report_url_tbl(p_cached_report_url_tbl.FIRST)
777 );
778
779 END IF;
780
781 END LOOP;
782
783 wf_engine.StartProcess(itemtype=>p_item_type
784 ,itemkey => l_wf_item_key);
785
786 commit;
787
788 EXCEPTION
789 WHEN OTHERS THEN
790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
791
792 END Start_Workflow_Engine;
793
794
795 -- For 11.5.1 BIS Reports
796 --
797 /*
798 Example variables from Product Quality report.
799
800 NAME="pFunctionName" VALUE="BIS_PRODUCT_QUALITY"
801 NAME="pRegionCode" VALUE="PRODUCT_QUALITY"
802 NAME="pSessionId" VALUE="134880"
803 NAME="pUserId" VALUE="3259"
804 NAME="pResponsibilityId" VALUE="21524"
805
806 l_bis_report_tbl(1).region_code := 'PRODUCT_QUALITY';
807 l_bis_report_tbl(1).reportFn_name := 'BIS_PRODUCT_QUALITY';
808 l_bis_report_tbl(1).report_resp_id := l_resp_id;
809
810 Final URL should look like: ( <amp> = ampersand )
811 'http://ap804sun.us.oracle.com:778/dev115/plsql/bisviewer.ShowReport?pRegionCode=PRODUCT_QUALITY<amp>pFunctionName=BIS_PRODUCT_QUALITY<amp>pSessionId=134903<amp>pUserId=11788<amp>pResponsibilityId=21524'
812 */
813
814 Procedure Build_Report_URL
815 ( p_report_type IN VARCHAR2 default BIS_UTIL.G_BIS_REPORT_TYPE
816 , p_reportFn_name IN Varchar2
817 , p_region_code IN Varchar2
818 , p_report_resp_id IN VARCHAR2
819 , p_report_params IN VARCHAR2
820 , x_report_url OUT NOCOPY VARCHAR2
821 , x_return_status OUT NOCOPY VARCHAR2
822 )
823 IS
824 l_report_url VARCHAR2(32000);
825 l_report_link VARCHAR2(32000) := NULL;
826
827 BEGIN
828
829 x_return_status := FND_API.G_RET_STS_SUCCESS;
830 l_report_link := FND_PROFILE.value('ICX_REPORT_LINK');
831
832 IF p_report_type = BIS_UTIL.G_BIS_REPORT_TYPE THEN
833
834 IF p_report_params IS NOT NULL OR
835 p_report_params <> FND_API.G_MISS_CHAR THEN
836 l_report_url := l_report_link||
837 'BISVIEWER3.ShowReport?'||
838 'pRegionCode='|| p_region_code ||
839 '&pFunctionName='|| p_reportFN_name ||
840 '&pResponsibilityId='|| p_report_resp_id||
841 '&pParameters='||p_report_params;
842 ELSE
843 l_report_url := l_report_link||
844 'BISVIEWER3.ShowReport?'||
845 'pRegionCode='|| p_region_code ||
846 '&pFunctionName='|| p_reportFN_name ||
847 '&pResponsibilityId='|| p_report_resp_id;
848 END IF;
849
850 END IF;
851
852 x_report_url := l_report_url;
853
854 EXCEPTION
855 WHEN OTHERS THEN
856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
857
858 END Build_Report_URL;
859
860
861 -- For 11.5.1 Cached BIS Reports
862 --
863 /*
864 Report Identifier is the saved report id
865
866 Final URL should look like: ( <amp> = ampersand )
867 'http://ap804sun.us.oracle.com:778/dev115/plsql/BIS_CACHING_PVT.Display_Cache?p_identifier=1234567'
868 */
869
870 Procedure Build_Report_URL
871 ( p_report_type IN VARCHAR2 default BIS_UTIL.G_BIS_CACHE_REPORT_TYPE
872 , p_report_identifier IN VARCHAR2
873 , x_report_url OUT NOCOPY VARCHAR2
874 , x_return_status OUT NOCOPY VARCHAR2
875 )
876 IS
877 l_report_url VARCHAR2(32000);
878
879 BEGIN
880
881 x_return_status := FND_API.G_RET_STS_SUCCESS;
882 IF p_report_type = BIS_UTIL.G_BIS_CACHE_REPORT_TYPE THEN
883
884 l_report_url := 'BIS_CACHING_PVT.Display_Cache?'||
885 'p_identifier='||p_report_identifier;
886
887 END IF;
888
889 x_report_url := l_report_url;
890
891 EXCEPTION
892 WHEN OTHERS THEN
893 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
894
895 END Build_Report_URL;
896
897 -- For 11.5.1 Oracle Reports
898 -- Added resp_application_id for bug#3756093
899 -- Added new parameter at last to support old code calling this
900 -- API by numbers
901 --
902 Procedure Build_Report_URL
903 ( p_report_type IN VARCHAR2 default BIS_UTIL.G_ORACLE_REPORT_TYPE
904 , p_report_name IN Varchar2
905 , p_report_params IN Varchar2
906 , p_report_resp_id IN NUMBER
907 , x_report_url OUT NOCOPY VARCHAR2
908 , x_return_status OUT NOCOPY VARCHAR2
909 , p_report_app_id IN NUMBER DEFAULT NULL
910 )
911 IS
912 l_report_url VARCHAR2(32000) := NULL;
913 l_report_link VARCHAR2(32000) := NULL;
914
915 BEGIN
916
917 x_return_status := FND_API.G_RET_STS_SUCCESS;
918 l_report_link := FND_PROFILE.value('ICX_REPORT_LINK');
919
920 IF p_report_type = BIS_UTIL.G_ORACLE_REPORT_TYPE
921 AND p_report_name IS NOT NULL
922 THEN
923
924 IF p_report_params IS NOT NULL OR
925 p_report_params <> FND_API.G_MISS_CHAR THEN
926
927 l_report_url := l_report_link
928 || 'OracleOASIS.RunReport?report='|| p_report_name
929 || '¶meters=' || p_report_params
930 || '&responsibility_id=' || p_report_resp_id;
931
932 IF (BIS_UTILITIES_PVT.Value_Not_Missing_Not_Null(p_report_app_id) = FND_API.G_TRUE) THEN
933 l_report_url := l_report_url || '&responsibility_application_id=' || p_report_app_id;
934 END IF;
935
936 ELSE
937
938 l_report_url := l_report_link
939 || 'OracleOASIS.RunReport?report='|| p_report_name
940 || '&responsibility_id=' || p_report_resp_id;
941
942 IF (BIS_UTILITIES_PVT.Value_Not_Missing_Not_Null(p_report_app_id) = FND_API.G_TRUE) THEN
943 l_report_url := l_report_url || '&responsibility_application_id=' || p_report_app_id;
944 END IF;
945
946 END IF;
947 END IF;
948
949 x_report_url := l_report_url;
950
951
952 EXCEPTION
953 WHEN OTHERS THEN
954 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
955 END Build_Report_URL;
956
957 -- ankgoel: bug#4015335 - Returns 1 for Internal customers who will be shown all
958 -- the application ids. Returns 0 for External customers.
959 FUNCTION show_application
960 ( p_application_id IN NUMBER
961 , p_created_by IN NUMBER
962 )
963 RETURN NUMBER
964 IS
965 BEGIN
966 -- currently we allow external customers to choose ONLY BSC (271) and
967 -- customer created application. If decision is changed later on to use
968 -- BIS, the number 271 should be changed to 191, or whatever application
969 -- id should be.
970 IF(p_application_id = 271 OR BIS_UTIL.is_internal_customer OR BIS_UTIL.is_Seeded(p_created_by,'Y','N') = 'N') THEN
971 RETURN 1;
972 ELSE
973 RETURN 0;
974 END IF;
975 END show_application;
976
977 FUNCTION show_application
978 ( p_application_id IN NUMBER
979 )
980 RETURN NUMBER
981 IS
982 l_created_by Number;
983 BEGIN
984 select created_by into l_created_by
985 from fnd_application
986 where application_id = p_application_id;
987
988 return show_application(p_application_id, l_created_by);
989 exception
990 when others then return 0;
991 END show_application;
992
993
994
995 FUNCTION is_dev_env_set
996 RETURN BOOLEAN
997 IS
998 BEGIN
999 IF('Y' = FND_PROFILE.value('BIS_PMF_DEV_ENV')) THEN
1000 RETURN TRUE;
1001 ELSE
1002 RETURN FALSE;
1003 END IF;
1004 END is_dev_env_set;
1005
1006 FUNCTION is_internal_customer
1007 RETURN BOOLEAN
1008 IS
1009 BEGIN
1010 IF(BIS_UTIL.is_dev_env_set OR BIS_UTIL.is_Seeded(FND_GLOBAL.USER_ID,'Y','N') = 'Y') THEN
1011 RETURN TRUE;
1012 ELSE
1013 RETURN FALSE;
1014 END IF;
1015 END is_internal_customer;
1016
1017 -- Returns the default application to be set in all the designers
1018 -- It assumes that BSC is licensed.
1019 FUNCTION get_default_application_id
1020 RETURN NUMBER
1021 IS
1022 CURSOR latest_creation_date_cur IS
1023 SELECT application_id
1024 FROM FND_APPLICATION_VL
1025 WHERE created_by NOT IN (1,2)
1026 AND (created_by < 120 OR created_by > 129)
1027 ORDER BY creation_date desc;
1028
1029 l_app_id FND_APPLICATION_VL.application_id%TYPE;
1030 BEGIN
1031 IF(BIS_UTIL.is_internal_customer) THEN
1032 l_app_id := BIS_UTIL.G_BSC_APP_ID;
1033 ELSE
1034 IF(latest_creation_date_cur%ISOPEN) THEN
1035 CLOSE latest_creation_date_cur;
1036 END IF;
1037 OPEN latest_creation_date_cur;
1038 FETCH latest_creation_date_cur INTO l_app_id;
1039 IF(latest_creation_date_cur%NOTFOUND) THEN
1040 l_app_id := BIS_UTIL.G_BSC_APP_ID;
1041 END IF;
1042 CLOSE latest_creation_date_cur;
1043 END IF;
1044
1045 RETURN l_app_id;
1046 EXCEPTION
1047 WHEN OTHERS THEN
1048 IF(latest_creation_date_cur%ISOPEN) THEN
1049 CLOSE latest_creation_date_cur;
1050 END IF;
1051 RETURN BIS_UTIL.G_BSC_APP_ID;
1052 END get_default_application_id;
1053
1054 FUNCTION get_object_type
1055 ( p_function_type IN FND_FORM_FUNCTIONS.type%TYPE
1056 , p_parameters IN FND_FORM_FUNCTIONS.parameters%TYPE
1057 , p_web_html_call IN FND_FORM_FUNCTIONS.web_html_call%TYPE
1058 )
1059 RETURN VARCHAR2
1060 IS
1061 l_object_type VARCHAR2(30);
1062 BEGIN
1063 IF ( (p_function_type = 'WEBPORTLET' OR p_function_type = 'WEBPORTLETX') AND p_web_html_call like 'OA.jsp?akRegionCode=BIS_PM_PORTLET_TABLE_LAYOUT%' AND p_parameters like '%pRequestType=P%') THEN
1064 l_object_type := BIS_UTIL.G_FUNC_PARAMETER_PORTLET;
1065 ELSIF ((p_function_type = 'WEBPORTLET' OR p_function_type = 'WEBPORTLETX') AND p_web_html_call like 'OA.jsp?akRegionCode=BIS_PM_PORTLET_TABLE_LAYOUT%' AND p_parameters like '%pRequestType=T%') THEN
1066 l_object_type := BIS_UTIL.G_FUNC_TABLE_PORTLET;
1067 ELSIF ((p_function_type = 'WEBPORTLET' OR p_function_type = 'WEBPORTLETX') AND p_web_html_call like 'OA.jsp?akRegionCode=BIS_PM_PORTLET_TABLE_LAYOUT%' AND p_parameters like '%pRequestType=G%') THEN
1068 l_object_type := BIS_UTIL.G_FUNC_GRAPH_PORTLET;
1069 ELSIF ((p_function_type = 'WEBPORTLET' OR p_function_type = 'WEBPORTLETX') AND p_web_html_call like 'OA.jsp?akRegionCode=BIS_PM_RELATED_LINK_LAYOUT%') THEN
1070 l_object_type := BIS_UTIL.G_FUNC_RELATED_LINKS_PORTLET;
1071 ELSIF ((p_function_type = 'WEBPORTLET' OR p_function_type = 'WEBPORTLETX') AND p_web_html_call like 'OA.jsp?akRegionCode=BIS_PMF_PORTLET_TABLE_LAYOUT%') THEN
1072 l_object_type := BIS_UTIL.G_FUNC_KPI_LIST;
1073 ELSIF ((p_function_type = 'WEBPORTLET' OR p_function_type = 'WEBPORTLETX') AND p_web_html_call like 'OA.jsp?akRegionCode=BSC_PORTLET_CUSTOM_VIEW%' AND p_parameters like '%pRequestType=C%') THEN
1074 l_object_type := BIS_UTIL.G_FUNC_CUSTOM_VIEW;
1075 ELSIF ((p_function_type = 'WEBPORTLET' OR p_function_type = 'WEBPORTLETX') AND p_parameters like '%pRequestType=URL%') THEN
1076 l_object_type := BIS_UTIL.G_FUNC_URL_PORTLET;
1077 ELSIF ((p_function_type = 'WEBPORTLET' OR p_function_type = 'WEBPORTLETX')) THEN
1078 l_object_type := BIS_UTIL.G_FUNC_GENERIC_OA_PORTLET;
1079 ELSIF (p_function_type = 'JSP' AND p_web_html_call like 'OA.jsp?akRegionCode=BIS_COMPONENT_PAGE%' AND p_parameters IS NOT NULL) THEN
1080 l_object_type := BIS_UTIL.G_FUNC_PAGE;
1081 ELSIF (((lower(p_web_html_call) like 'bisviewm.jsp%' OR p_web_html_call like 'OA.jsp?page=/oracle/apps/bis/report/webui/BISReportPG%') AND p_function_type = 'JSP')
1082 OR (lower(p_web_html_call) like 'bisviewer.showreport%' AND p_function_type = 'WWW')) THEN
1083 l_object_type := BIS_UTIL.G_FUNC_REPORT;
1084 END IF;
1085
1086 RETURN l_object_type;
1087 EXCEPTION
1088 WHEN OTHERS THEN
1089 RETURN l_object_type;
1090 END get_object_type;
1091
1092
1093
1094 -- get Application ID by Short Name
1095 FUNCTION Get_Apps_Id_By_Short_Name (
1096 p_Application_Short_Name IN VARCHAR2
1097 ) RETURN NUMBER IS
1098 l_Apps_Id NUMBER;
1099 BEGIN
1100 SELECT APPLICATION_ID
1101 INTO l_Apps_Id
1102 FROM FND_APPLICATION
1103 WHERE UPPER(APPLICATION_SHORT_NAME) = UPPER(p_Application_Short_Name);
1104
1105 RETURN l_Apps_Id;
1106 EXCEPTION
1107 WHEN OTHERS THEN
1108 RETURN -1;
1109 END Get_Apps_Id_By_Short_Name;
1110
1111 PROCEDURE Get_Update_Date_For_Owner (
1112 p_owner IN VARCHAR2
1113 ,p_last_update_date IN VARCHAR2
1114 ,x_file_last_update_date OUT NOCOPY DATE
1115 ,x_return_status OUT NOCOPY VARCHAR2
1116 ,x_msg_count OUT NOCOPY NUMBER
1117 ,x_msg_data OUT NOCOPY VARCHAR2
1118 )
1119 IS
1120 BEGIN
1121
1122 IF ( (p_owner = 'ORACLE') AND (p_last_update_date IS NULL) ) THEN
1123 RAISE FND_API.G_EXC_ERROR;
1124 END IF;
1125
1126 x_file_last_update_date := NVL(to_date(p_last_update_date, 'YYYY/MM/DD'), SYSDATE);
1127
1128
1129 EXCEPTION
1130
1131 WHEN FND_API.G_EXC_ERROR THEN
1132 IF (x_msg_data IS NOT NULL) THEN
1133 x_msg_data := x_msg_data ||' Upload failed: OWNER=''ORACLE'' and Last_Update_Date is missing.';
1134 ELSE
1135 x_msg_data := 'Upload failed: OWNER=''ORACLE'' and Last_Update_Date is missing.';
1136 END IF;
1137
1138 x_return_status := FND_API.G_RET_STS_ERROR;
1139 RAISE;
1140
1141 WHEN OTHERS THEN
1142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1143 IF (x_msg_data IS NOT NULL) THEN
1144 x_msg_data := x_msg_data ||' -> BIS_UTIL.Get_Update_Date_For_Owner ';
1145 ELSE
1146 x_msg_data := SQLERRM ||' at BIS_UTIL.Get_Update_Date_For_Owner ';
1147 END IF;
1148 RAISE;
1149
1150 END Get_Update_Date_For_Owner;
1151
1152
1153 PROCEDURE Validate_For_Update
1154 (
1155 p_last_update_date IN DATE
1156 ,p_owner IN VARCHAR2
1157 ,p_force_mode IN BOOLEAN
1158 ,p_table_name IN VARCHAR2
1159 ,p_key_value IN VARCHAR2
1160 ,x_ret_code OUT NOCOPY BOOLEAN
1161 ,x_return_status OUT NOCOPY VARCHAR2
1162 ,x_msg_data OUT NOCOPY VARCHAR2
1163 )
1164 IS
1165
1166 l_file_last_updated_by NUMBER;
1167 l_db_last_updated_by NUMBER;
1168 l_db_last_update_date DATE;
1169 l_custom_mode VARCHAR2(5);
1170 l_msg_count NUMBER;
1171
1172 BEGIN
1173
1174 l_file_last_updated_by := fnd_load_util.OWNER_ID(p_owner);
1175 x_ret_code := FALSE;
1176
1177 IF (p_force_mode) THEN
1178 l_custom_mode := 'FORCE';
1179 END IF;
1180
1181 --Using static sql's becoz dynamic sql's has performance impact
1182
1183 IF ( UPPER(p_table_name) = 'BIS_DIMENSIONS') THEN
1184 SELECT last_update_date, last_updated_by INTO l_db_last_update_date, l_db_last_updated_by
1185 FROM bis_dimensions
1186 WHERE short_name = p_key_value;
1187
1188 ELSIF ( UPPER(p_table_name) = 'BIS_DIMENSIONS_TL') THEN
1189 SELECT dim_tl.last_update_date, dim_tl.last_updated_by INTO l_db_last_update_date, l_db_last_updated_by
1190 FROM bis_dimensions_tl dim_tl, bis_dimensions dim
1191 WHERE dim.short_name = p_key_value
1192 AND dim_tl.dimension_id = dim.dimension_id
1193 AND dim_tl.language = USERENV('LANG');
1194
1195 ELSIF ( UPPER(p_table_name) = 'BIS_LEVELS_TL') THEN
1196 SELECT lev_tl.last_update_date, lev_tl.last_updated_by INTO l_db_last_update_date, l_db_last_updated_by
1197 FROM bis_levels_tl lev_tl, bis_levels lev
1198 WHERE lev.short_name = p_key_value
1199 AND lev.level_id = lev_tl.level_id
1200 AND USERENV('LANG') = lev_tl.language;
1201
1202 ELSIF ( UPPER(p_table_name) = 'BIS_LEVELS') THEN
1203 SELECT last_update_date, last_updated_by INTO l_db_last_update_date, l_db_last_updated_by
1204 FROM bis_levels
1205 WHERE short_name = p_key_value;
1206
1207 ELSIF ( UPPER(p_table_name) = 'BSC_SYS_DIM_GROUPS_VL') THEN
1208 SELECT last_update_date, last_updated_by INTO l_db_last_update_date, l_db_last_updated_by
1209 FROM bsc_sys_dim_groups_vl
1210 WHERE short_name = p_key_value;
1211
1212 ELSIF ( UPPER(p_table_name) = 'BSC_SYS_DIM_LEVELS_B') THEN
1213 SELECT last_update_date, last_updated_by INTO l_db_last_update_date, l_db_last_updated_by
1214 FROM bsc_sys_dim_levels_b
1215 WHERE short_name = p_key_value;
1216
1217 END IF;
1218
1219 x_ret_code := fnd_load_util.UPLOAD_TEST( p_file_id => l_file_last_updated_by
1220 ,p_file_lud => p_last_update_date
1221 ,p_db_id => l_db_last_updated_by
1222 ,p_db_lud => l_db_last_update_date
1223 ,p_custom_mode => l_custom_mode);
1224
1225 -- commented becoz FND itself throws this error
1226 --IF(x_ret_code = FALSE) THEN
1227 --FND_MESSAGE.SET_NAME('BIS','BIS_DIM_UPLOAD_TEST_FAILED');
1228 --FND_MESSAGE.SET_TOKEN('SHORT_NAME', p_key_value);
1229 --FND_MSG_PUB.ADD;
1230 --RAISE FND_API.G_EXC_ERROR;
1231 --END IF;
1232
1233 EXCEPTION
1234
1235 --WHEN FND_API.G_EXC_ERROR THEN
1236 --x_return_status := FND_API.G_RET_STS_ERROR ;
1237 --IF(x_msg_data IS NULL) THEN
1238 --FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
1239 --,p_count => l_msg_count
1240 --,p_data => x_msg_data);
1241 --END IF;
1242 --RAISE;
1243
1244 WHEN OTHERS THEN
1245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1246 IF (x_msg_data IS NOT NULL) THEN
1247 x_msg_data := x_msg_data ||' -> BIS_UTIL.Validate_For_Update ';
1248 ELSE
1249 x_msg_data := SQLERRM ||' at BIS_UTIL.Validate_For_Update ';
1250 END IF;
1251 RAISE;
1252
1253 END Validate_For_Update;
1254
1255 FUNCTION get_dim_objects_by_dim
1256 ( p_dimension IN VARCHAR2
1257 , p_allow_all IN VARCHAR2 := FND_API.G_FALSE --Added for bug 5250723
1258 , p_append_short_names IN VARCHAR2 := FND_API.G_TRUE
1259 ) RETURN VARCHAR2
1260 IS
1261 l_dim_object_names VARCHAR2(2000);
1262 l_dim_object_sht_names VARCHAR2(2000);
1263 l_dim_object_name bis_levels_vl.name%TYPE;
1264 l_dim_object_sht_name bis_levels_vl.short_name%TYPE;
1265 TYPE refCursorType IS REF CURSOR ;
1266 c_dim_object_rec refCursorType;
1267
1268 l_dim_objects_sql VARCHAR2(1000) :=
1269 'SELECT bis_lvl.short_name, bis_lvl.name
1270 FROM bis_dimensions bis_dim, bsc_sys_dim_groups_vl bsc_dim, bis_levels_vl bis_lvl, bsc_sys_dim_levels_b bsc_lvl, bsc_sys_dim_levels_by_group lvl_by_grp
1271 WHERE bis_dim.dim_grp_id = bsc_dim.dim_group_id
1272 AND bis_lvl.short_name = bsc_lvl.short_name
1273 AND bsc_dim.dim_group_id = lvl_by_grp.dim_group_id
1274 AND bsc_lvl.dim_level_id = lvl_by_grp.dim_level_id
1275 AND bis_dim.short_name = :1';
1276 l_where_clause VARCHAR2(1000) :=
1277 'AND NVL(bis_lvl.enabled, ''T'') = ''T''
1278 AND bis_lvl.source = ''OLTP''
1279 AND ((bis_dim.short_name <> ''TIME'')
1280 OR ((bis_dim.short_name = ''TIME'') AND (bis_lvl.short_name IN (''FII_TIME_DAY'',''FII_TIME_WEEK'',''FII_TIME_ENT_PERIOD'',''FII_TIME_ENT_QTR'',''FII_TIME_ENT_YEAR''))))';
1281
1282 BEGIN
1283
1284 IF(p_allow_all = FND_API.G_FALSE) THEN
1285 l_dim_objects_sql := l_dim_objects_sql || l_where_clause;
1286 END IF;
1287
1288 OPEN c_dim_object_rec FOR l_dim_objects_sql USING p_dimension;
1289 LOOP
1290 FETCH c_dim_object_rec INTO l_dim_object_sht_name,l_dim_object_name;
1291 EXIT WHEN c_dim_object_rec%NOTFOUND;
1292 IF (l_dim_object_names IS NULL) THEN
1293 l_dim_object_names := l_dim_object_name;
1294 l_dim_object_sht_names := l_dim_object_sht_name;
1295 ELSE
1296 l_dim_object_names := l_dim_object_names || ', ' || l_dim_object_name;
1297 l_dim_object_sht_names := l_dim_object_sht_names || ', ' || l_dim_object_sht_name;
1298 END IF;
1299 END LOOP;
1300 CLOSE c_dim_object_rec;
1301
1302 IF ((l_dim_object_names IS NOT NULL) AND (l_dim_object_sht_names IS NOT NULL)) THEN
1303 IF(p_append_short_names = FND_API.G_TRUE) THEN
1304 RETURN l_dim_object_names || '^' || l_dim_object_sht_names;
1305 ELSE
1306 RETURN l_dim_object_names;
1307 END IF;
1308 ELSE
1309 RETURN NULL;
1310 END IF;
1311 END get_dim_objects_by_dim;
1312
1313 PROCEDURE save_prototype_values
1314 ( p_dim_object IN VARCHAR2
1315 , p_PV_array IN BIS_STRING_ARRAY
1316 )
1317 IS
1318 l_dim_bsc_table VARCHAR2(30);
1319 l_sql_stmt VARCHAR2(2000);
1320 id NUMBER;
1321 value VARCHAR2(1000); -- max allowed size from Designer is '999'
1322 i NUMBER;
1323 BEGIN
1324
1325 SELECT level_table_name INTO l_dim_bsc_table
1326 FROM bsc_sys_dim_levels_b
1327 WHERE short_name = p_dim_object;
1328
1329 l_sql_stmt := ' DELETE FROM ' || l_dim_bsc_table;
1330 EXECUTE IMMEDIATE l_sql_stmt;
1331
1332 i := 1;
1333 WHILE (i < p_PV_array.COUNT) LOOP
1334 id := p_PV_array(i);
1335 value := p_PV_array(i + 1);
1336 l_sql_stmt := ' INSERT INTO '||l_dim_bsc_table||
1337 ' (CODE, USER_CODE, NAME, LANGUAGE, SOURCE_LANG) '||
1338 ' SELECT '||id||' AS CODE, '||
1339 ' '''||TO_CHAR(id)||''' AS USER_CODE, '||
1340 ' '''||value||''' AS NAME, L.LANGUAGE_CODE, '||
1341 ' USERENV(''LANG'') '||
1342 ' FROM FND_LANGUAGES L '||
1343 ' WHERE L.INSTALLED_FLAG IN (''I'', ''B'') '||
1344 ' AND NOT EXISTS '||
1345 ' ( SELECT NULL FROM '||l_dim_bsc_table||
1346 ' T WHERE T.CODE = '||id||' '||
1347 ' AND T.LANGUAGE = L.LANGUAGE_CODE) ';
1348 EXECUTE IMMEDIATE l_sql_stmt;
1349 i := i + 2;
1350 END LOOP;
1351 COMMIT;
1352 EXCEPTION
1353 WHEN OTHERS THEN
1354 NULL;
1355 END save_prototype_values;
1356
1357 FUNCTION get_Pages_Using_ParamPortlet
1358 ( p_Region_Code IN VARCHAR2
1359 , x_Return_Status OUT NOCOPY VARCHAR2
1360 , x_Msg_Count OUT NOCOPY NUMBER
1361 , x_Msg_Data OUT NOCOPY VARCHAR2
1362 )RETURN VARCHAR2
1363 IS
1364 l_form_function FND_FORM_FUNCTIONS.function_name%TYPE;
1365 l_parent_obj_table BIS_RSG_PUB_API_PKG.t_BIA_RSG_Obj_Table;
1366 l_msg VARCHAR2(32000);
1367 l_pages_found BOOLEAN;
1368
1369 CURSOR c_param_portlets IS
1370 SELECT function_name
1371 FROM fnd_form_functions_vl
1372 WHERE parameters LIKE 'pRegionCode=' || p_Region_Code || '&pRequestType=P%';
1373
1374 BEGIN
1375 l_pages_found := FALSE;
1376
1377 FOR CD IN c_param_portlets LOOP
1378
1379 l_parent_obj_table := BIS_RSG_PUB_API_PKG.GetParentObjects
1380 ( p_Dep_Obj_Name => CD.function_name
1381 , p_Dep_Obj_Type => 'PORTLET'
1382 , p_Obj_Type => 'PAGE'
1383 , x_Return_Status => x_Return_Status
1384 , x_Msg_Data => x_Msg_Data
1385 );
1386 IF ((x_Return_Status IS NOT NULL) AND (x_Return_Status <> FND_API.G_RET_STS_SUCCESS)) THEN
1387 FND_MSG_PUB.Initialize;
1388 FND_MESSAGE.SET_NAME('BIS', x_Msg_Data);
1389 FND_MSG_PUB.ADD;
1390 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1391 END IF;
1392 IF(l_parent_obj_table.COUNT > 0) THEN
1393 l_pages_found := TRUE;
1394 FOR i IN 0..l_Parent_Obj_Table.COUNT-1 LOOP
1395 l_msg := l_msg || '<li>'||l_parent_obj_table(i).user_object_name;
1396 END LOOP;
1397 END IF;
1398 END LOOP;
1399
1400 IF(l_pages_found) THEN
1401 l_msg := '<UL>' || l_msg|| '</UL>';
1402 END IF;
1403
1404 RETURN l_msg;
1405
1406 EXCEPTION
1407 WHEN FND_API.G_EXC_ERROR THEN
1408 IF (x_Msg_Data IS NULL) THEN
1409 FND_MSG_PUB.Count_And_Get
1410 ( p_encoded => FND_API.G_FALSE
1411 , p_count => x_Msg_Count
1412 , p_data => x_Msg_Data
1413 );
1414 END IF;
1415 x_Return_Status := FND_API.G_RET_STS_ERROR;
1416 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1417 IF (x_Msg_Data IS NULL) THEN
1418 FND_MSG_PUB.Count_And_Get
1419 ( p_encoded => FND_API.G_FALSE
1420 , p_count => x_Msg_Count
1421 , p_data => x_Msg_Data
1422 );
1423 END IF;
1424 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1425 WHEN OTHERS THEN
1426 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1427 IF (x_Msg_Data IS NOT NULL) THEN
1428 x_Msg_Data := x_msg_data||' -> BIS_UTIL.get_Pages_Using_ParamPortlet';
1429 ELSE
1430 x_Msg_Data := SQLERRM||' at BIS_UTIL.get_Pages_Using_ParamPortlet';
1431 END IF;
1432 END get_Pages_Using_ParamPortlet;
1433
1434 FUNCTION get_value
1435 ( p_id VARCHAR2
1436 , p_view_name VARCHAR2
1437 )
1438 RETURN VARCHAR2
1439 IS
1440
1441 l_sql_stmt VARCHAR2(100);
1442 l_value VARCHAR2(100);
1443
1444 BEGIN
1445
1446 l_sql_stmt := ' SELECT value FROM ' || p_view_name || ' WHERE id = ''' || p_id || ''' AND rownum < 2';
1447 EXECUTE IMMEDIATE l_sql_stmt INTO l_value;
1448
1449 RETURN l_value;
1450
1451 EXCEPTION
1452 WHEN OTHERS THEN
1453 RETURN NULL;
1454 END get_value;
1455
1456 FUNCTION get_default_value
1457 ( p_ids VARCHAR2
1458 , p_view_name VARCHAR2
1459 )
1460 RETURN VARCHAR2
1461 IS
1462
1463 l_ids VARCHAR2(1000);
1464 l_id VARCHAR2(100);
1465 l_values VARCHAR2(1000);
1466 l_value VARCHAR2(100);
1467 l_pos NUMBER;
1468
1469 BEGIN
1470
1471 l_ids := p_ids;
1472
1473 WHILE (l_ids IS NOT NULL) LOOP
1474
1475 l_pos := INSTR(l_ids, ',');
1476
1477 IF (l_pos > 0) THEN
1478
1479 l_id := TRIM(SUBSTR(l_ids, 1, l_pos - 1));
1480
1481 l_value := get_value
1482 ( p_id => l_id
1483 , p_view_name => p_view_name
1484 );
1485
1486 IF (l_values IS NULL) THEN
1487 l_values := l_value;
1488 ELSE
1489 l_values := l_values || '^^' || l_value;
1490 END IF;
1491
1492 l_ids := TRIM(SUBSTR(l_ids, l_pos + 1));
1493
1494 ELSE
1495
1496 l_value := get_value
1497 ( p_id => l_ids
1498 , p_view_name => p_view_name
1499 );
1500
1501 IF (l_values IS NULL) THEN
1502 l_values := l_value;
1503 ELSE
1504 l_values := l_values || '^^' || l_value;
1505 END IF;
1506 l_ids := NULL;
1507
1508 END IF;
1509
1510 END LOOP;
1511
1512 RETURN l_values;
1513
1514 EXCEPTION
1515 WHEN OTHERS THEN
1516 RETURN NULL;
1517 END get_default_value;
1518
1519 PROCEDURE get_default_dim_object_value
1520 ( p_dim_object IN VARCHAR2
1521 , p_dimension IN VARCHAR2
1522 , p_id IN VARCHAR2
1523 , x_id OUT NOCOPY VARCHAR2
1524 , x_value OUT NOCOPY VARCHAR2
1525 )
1526 IS
1527 CURSOR c_view_name IS
1528 SELECT level_values_view_name
1529 FROM bis_levels
1530 WHERE short_name = p_dim_object;
1531 l_view_name_rec c_view_name%ROWTYPE;
1532
1533 CURSOR c_all_enabled IS
1534 SELECT total_flag
1535 FROM bsc_sys_dim_levels_b bsc_lvl, bis_dimensions bis_dim, bsc_sys_dim_levels_by_group lvl_by_grp
1536 WHERE bsc_lvl.dim_level_id = lvl_by_grp.dim_level_id
1537 AND bis_dim.dim_grp_id = lvl_by_grp.dim_group_id
1538 AND bsc_lvl.short_name = p_dim_object
1539 AND bis_dim.short_name = p_dimension;
1540 l_all_enabled_rec c_all_enabled%ROWTYPE;
1541
1542 l_level_values_view_name VARCHAR2(30);
1543 l_sql_stmt VARCHAR2(100);
1544
1545 BEGIN
1546
1547 x_value := NULL;
1548 x_id := p_id;
1549
1550 IF(p_id IS NOT NULL) THEN
1551 FOR l_view_name_rec IN c_view_name LOOP -- Only 1 record should be returned
1552 x_value := get_default_value
1553 ( p_ids => p_id
1554 , p_view_name => l_view_name_rec.level_values_view_name
1555 );
1556 END LOOP;
1557 ELSE
1558 IF(c_all_enabled%ISOPEN) THEN
1559 CLOSE c_all_enabled;
1560 END IF;
1561 OPEN c_all_enabled;
1562 FETCH c_all_enabled INTO l_all_enabled_rec;
1563 IF(c_all_enabled%FOUND AND l_all_enabled_rec.total_flag = -1) THEN
1564 x_id := 'All';
1565 x_value := BIS_UTILITIES_PVT.Get_FND_Message('BIS_ALL');
1566 ELSE
1567 FOR l_view_name_rec IN c_view_name LOOP -- Only 1 record should be returned
1568 l_sql_stmt := ' SELECT id, value FROM ' || l_view_name_rec.level_values_view_name || ' WHERE rownum < 2 ';
1569 EXECUTE IMMEDIATE l_sql_stmt INTO x_id, x_value;
1570 END LOOP;
1571 END IF;
1572 CLOSE c_all_enabled;
1573 END IF;
1574
1575 EXCEPTION
1576 WHEN OTHERS THEN
1577 IF(c_all_enabled%ISOPEN) THEN
1578 CLOSE c_all_enabled;
1579 END IF;
1580 END get_default_dim_object_value;
1581
1582 PROCEDURE get_parent_objects
1583 ( p_dep_object_name IN VARCHAR2
1584 , p_dep_object_type IN VARCHAR2
1585 , p_parent_object_type IN VARCHAR2
1586 , x_parent_objects OUT NOCOPY VARCHAR2
1587 , x_parent_object_owners OUT NOCOPY VARCHAR2
1588 , x_return_status OUT NOCOPY VARCHAR2
1589 , x_msg_count OUT NOCOPY NUMBER
1590 , x_msg_data OUT NOCOPY VARCHAR2
1591 ) IS
1592 l_parent_user_objects VARCHAR(15000) := NULL;
1593 BEGIN
1594 BIS_UTIL.get_parent_objects
1595 ( p_dep_object_name
1596 , p_dep_object_type
1597 , p_parent_object_type
1598 , x_parent_objects
1599 , l_parent_user_objects
1600 , x_parent_object_owners
1601 , x_return_status
1602 , x_msg_count
1603 , x_msg_data
1604 );
1605 END get_parent_objects;
1606
1607
1608 PROCEDURE get_parent_objects
1609 ( p_dep_object_name IN VARCHAR2
1610 , p_dep_object_type IN VARCHAR2
1611 , p_parent_object_type IN VARCHAR2
1612 , x_parent_objects OUT NOCOPY VARCHAR2
1613 , x_parent_user_objects OUT NOCOPY VARCHAR2
1614 , x_parent_object_owners OUT NOCOPY VARCHAR2
1615 , x_return_status OUT NOCOPY VARCHAR2
1616 , x_msg_count OUT NOCOPY NUMBER
1617 , x_msg_data OUT NOCOPY VARCHAR2
1618 )
1619 IS
1620 l_parent_obj_table BIS_RSG_PUB_API_PKG.t_BIA_RSG_Obj_Table;
1621
1622 BEGIN
1623
1624 l_parent_obj_table := BIS_RSG_PUB_API_PKG.GetParentObjects
1625 ( p_Dep_Obj_Name => p_dep_object_name
1626 , p_Dep_Obj_Type => p_dep_object_type
1627 , p_Obj_Type => p_parent_object_type
1628 , x_return_status => x_return_status
1629 , x_msg_data => x_msg_data
1630 );
1631 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1632 FND_MSG_PUB.Initialize;
1633 FND_MESSAGE.SET_NAME('BIS', x_msg_data);
1634 FND_MSG_PUB.ADD;
1635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1636 END IF;
1637
1638 IF(l_parent_obj_table.COUNT > 0) THEN
1639 FOR i IN 0..l_parent_obj_table.COUNT - 1 LOOP
1640 IF(x_parent_objects IS NULL) THEN
1641 x_parent_objects := l_parent_obj_table(i).object_name;
1642 ELSE
1643 x_parent_objects := x_parent_objects || ',' || l_parent_obj_table(i).object_name;
1644 END IF;
1645 IF(x_parent_user_objects IS NULL) THEN
1646 x_parent_user_objects := l_parent_obj_table(i).user_object_name;
1647 ELSE
1648 x_parent_user_objects := x_parent_user_objects || ',' || l_parent_obj_table(i).user_object_name;
1649 END IF;
1650 IF(x_parent_object_owners IS NULL) THEN
1651 x_parent_object_owners := l_parent_obj_table(i).object_owner;
1652 ELSE
1653 x_parent_object_owners := x_parent_object_owners || ',' || l_parent_obj_table(i).object_owner;
1654 END IF;
1655 END LOOP;
1656 END IF;
1657
1658 EXCEPTION
1659 WHEN FND_API.G_EXC_ERROR THEN
1660 IF (x_msg_data IS NULL) THEN
1661 FND_MSG_PUB.Count_And_Get
1662 ( p_encoded => FND_API.G_FALSE
1663 , p_count => x_msg_count
1664 , p_data => x_msg_data
1665 );
1666 END IF;
1667 x_return_status := FND_API.G_RET_STS_ERROR;
1668 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1669 IF (x_msg_data IS NULL) THEN
1670 FND_MSG_PUB.Count_And_Get
1671 ( p_encoded => FND_API.G_FALSE
1672 , p_count => x_msg_count
1673 , p_data => x_msg_data
1674 );
1675 END IF;
1676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1677 WHEN OTHERS THEN
1678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1679 IF (x_msg_data IS NOT NULL) THEN
1680 x_msg_data := x_msg_data || ' -> BIS_UTIL.get_parent_objects';
1681 ELSE
1682 x_msg_data := SQLERRM || ' at BIS_UTIL.get_parent_objects';
1683 END IF;
1684 END get_parent_objects;
1685
1686 /*This function uses PMV Function BIS_PMV_BSC_API_PUB.Get_DimLevel_Viewby to retrieve Dimension Short Name
1687 Values in ATTRIBUTE2 of AK_REGION_ITEMS */
1688
1689 FUNCTION get_dims_for_region(
1690 x_RegionCode IN VARCHAR2
1691 )
1692 RETURN VARCHAR2 IS
1693
1694 l_DimObj_ViewBy_Tbl BIS_PMV_BSC_API_PUB.DimLevel_Viewby_Tbl_Type;
1695 l_return_status VARCHAR2(10);
1696 l_msg_count NUMBER;
1697 l_msg_data VARCHAR2(255);
1698 l_Dim_Value VARCHAR(1000) := NULL;
1699 l_dimension BIS_DIMENSIONS.SHORT_NAME%TYPE;
1700
1701 BEGIN
1702
1703 BIS_PMV_BSC_API_PUB.Get_DimLevel_Viewby(
1704 p_api_version => 1
1705 , p_Region_Code => x_RegionCode
1706 , p_Measure_Short_Name => NULL
1707 , x_DimLevel_Viewby_Tbl => l_DimObj_ViewBy_Tbl
1708 , x_return_status => l_return_status
1709 , x_msg_count => l_msg_count
1710 , x_msg_data => l_msg_data
1711 );
1712
1713 FOR i IN 1..(l_DimObj_ViewBy_Tbl.COUNT) LOOP
1714 IF l_DimObj_ViewBy_Tbl(i).Dim_DimLevel IS NOT NULL THEN
1715 l_dimension := TRIM(SUBSTR(l_DimObj_ViewBy_Tbl(i).Dim_DimLevel, 1, (INSTR(l_DimObj_ViewBy_Tbl(i).Dim_DimLevel, '+') - 1)));
1716 IF ((l_Dim_Value IS NULL) AND (l_dimension IS NOT NULL))THEN
1717 l_Dim_Value := ','||l_dimension||',';
1718 ELSIF (INSTR(l_Dim_Value,','||l_dimension||',')=0 AND (l_dimension IS NOT NULL)) THEN
1719 l_Dim_Value := l_Dim_Value||l_dimension||',';
1720 END IF;
1721 END IF;
1722 END LOOP;
1723
1724 RETURN l_Dim_Value;
1725
1726 END get_dims_for_region;
1727
1728 /* This procedure is to check the dependency of a portlet with the given
1729 form function name
1730 */
1731 PROCEDURE Check_Portlet_Dependency(
1732 p_portlet_func_name IN VARCHAR2
1733 ,p_portlet_type IN VARCHAR2
1734 ,x_parent_obj_exist OUT NOCOPY VARCHAR2
1735 ,x_parent_obj_list OUT NOCOPY VARCHAR2
1736 ,x_return_status OUT NOCOPY VARCHAR2
1737 ,x_msg_count OUT NOCOPY NUMBER
1738 ,x_msg_data OUT NOCOPY VARCHAR2
1739 ) IS
1740
1741 l_par_obj_count INTEGER;
1742 l_par_obj_list VARCHAR2(1000);
1743
1744 CURSOR c_par_objs IS
1745 SELECT att_value
1746 FROM jdr_attributes
1747 WHERE att_name = 'windowTitle' AND att_comp_docid IN
1748 (SELECT UNIQUE a.att_comp_docid
1749 FROM jdr_attributes a, jdr_attributes b
1750 WHERE a.att_comp_docid = b.att_comp_docid AND a.att_comp_seq = b.att_comp_seq
1751 AND a.att_name = 'user:akAttribute3' AND a.att_value = p_portlet_type
1752 AND b.att_name = 'user:akAttribute1' AND b.att_value = p_portlet_func_name);
1753
1754 BEGIN
1755 l_par_obj_count := 0;
1756 x_return_status := FND_API.G_RET_STS_SUCCESS;
1757 x_parent_obj_exist := FND_API.G_FALSE;
1758
1759 FOR cd_par_obj IN c_par_objs LOOP
1760 IF (l_par_obj_count = 0) THEN
1761 l_par_obj_list := cd_par_obj.att_value;
1762 ELSE
1763 l_par_obj_list := l_par_obj_list || ', ' || cd_par_obj.att_value;
1764 END IF;
1765 l_par_obj_count := l_par_obj_count + 1;
1766 END LOOP;
1767
1768 IF (l_par_obj_count > 0) THEN
1769 x_parent_obj_list := l_par_obj_list;
1770 x_parent_obj_exist := FND_API.G_TRUE;
1771 END IF;
1772
1773 EXCEPTION
1774 WHEN FND_API.G_EXC_ERROR THEN
1775 IF (x_msg_data IS NULL) THEN
1776 FND_MSG_PUB.Count_And_Get
1777 ( p_encoded => FND_API.G_FALSE
1778 ,p_count => x_msg_count
1779 ,p_data => x_msg_data
1780 );
1781 END IF;
1782 x_return_status := FND_API.G_RET_STS_ERROR;
1783 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1784 IF (x_msg_data IS NULL) THEN
1785 FND_MSG_PUB.Count_And_Get
1786 ( p_encoded => FND_API.G_FALSE
1787 ,p_count => x_msg_count
1788 ,p_data => x_msg_data
1789 );
1790 END IF;
1791 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1792 WHEN OTHERS THEN
1793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1794 IF (x_msg_data IS NOT NULL) THEN
1795 x_msg_data := x_msg_data||' -> Check_Portlet_Dependency ';
1796 ELSE
1797 x_msg_data := SQLERRM||' at Check_Portlet_Dependency ';
1798 END IF;
1799
1800 END Check_Portlet_Dependency;
1801
1802 /* procedure to check dependency for objects like Graph, Custom View, etc
1803 Return the full list of dependency if p_list_dependency = FND_API.G_TRUE
1804 */
1805
1806 PROCEDURE Check_Object_Dependency(
1807 p_param_search_string IN VARCHAR2
1808 ,p_obj_portlet_type IN VARCHAR2
1809 ,p_list_dependency IN VARCHAR2
1810 ,x_exist_dependency OUT NOCOPY VARCHAR2
1811 ,x_dep_obj_list OUT NOCOPY VARCHAR2
1812 ,x_return_status OUT NOCOPY VARCHAR2
1813 ,x_msg_count OUT NOCOPY NUMBER
1814 ,x_msg_data OUT NOCOPY VARCHAR2
1815 ) IS
1816
1817 l_portlet_name VARCHAR2(200);
1818 l_portlet_func_name VARCHAR2(200);
1819 l_dep_obj_list VARCHAR2(6000);
1820 l_temp_list VARCHAR2(1000);
1821 l_exist_dependency VARCHAR2(10);
1822 l_return_status VARCHAR2(10);
1823 l_msg_count NUMBER;
1824 l_msg_data VARCHAR2(1000);
1825
1826 CURSOR c_portlets IS
1827 SELECT function_name, user_function_name
1828 FROM fnd_form_functions_vl
1829 WHERE parameters like p_param_search_string;
1830
1831 Begin
1832 x_return_status := FND_API.G_RET_STS_SUCCESS;
1833 x_exist_dependency := FND_API.G_FALSE;
1834
1835 FOR cd_portlet IN c_portlets LOOP
1836 l_portlet_name := cd_portlet.user_function_name;
1837 l_portlet_func_name := cd_portlet.function_name;
1838
1839 BIS_UTIL.Check_Portlet_Dependency(p_portlet_func_name => l_portlet_func_name,
1840 p_portlet_type => p_obj_portlet_type,
1841 x_parent_obj_exist => l_exist_dependency,
1842 x_parent_obj_list => l_temp_list,
1843 x_return_status => l_return_status,
1844 x_msg_count => l_msg_count,
1845 x_msg_data => l_msg_data);
1846
1847 IF ((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
1848 FND_MSG_PUB.Initialize;
1849 FND_MESSAGE.SET_NAME('BIS',l_msg_data);
1850 FND_MSG_PUB.ADD;
1851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1852 END IF;
1853
1854 IF (l_exist_dependency = FND_API.G_TRUE) THEN
1855 FND_MESSAGE.SET_NAME('BIS','BIS_DEPENDENCY_PAIR_MSG');
1856 FND_MESSAGE.SET_TOKEN('CHILD_NAME',l_portlet_name);
1857 FND_MESSAGE.SET_TOKEN('PARENT_NAMES',l_temp_list);
1858 --l_dep_obj_message := FND_MESSAGE.GET;
1859 l_dep_obj_list := l_dep_obj_list || '<li>' || FND_MESSAGE.GET;
1860 x_exist_dependency := FND_API.G_TRUE;
1861 END IF;
1862
1863 EXIT WHEN p_list_dependency = FND_API.G_FALSE AND l_exist_dependency = FND_API.G_TRUE;
1864
1865 END LOOP;
1866
1867 x_dep_obj_list := l_dep_obj_list;
1868
1869 EXCEPTION
1870 WHEN FND_API.G_EXC_ERROR THEN
1871 IF (x_msg_data IS NULL) THEN
1872 FND_MSG_PUB.Count_And_Get
1873 ( p_encoded => FND_API.G_FALSE
1874 ,p_count => x_msg_count
1875 ,p_data => x_msg_data
1876 );
1877 END IF;
1878 x_return_status := FND_API.G_RET_STS_ERROR;
1879 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1880 IF (x_msg_data IS NULL) THEN
1881 FND_MSG_PUB.Count_And_Get
1882 ( p_encoded => FND_API.G_FALSE
1883 ,p_count => x_msg_count
1884 ,p_data => x_msg_data
1885 );
1886 END IF;
1887 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1888 WHEN OTHERS THEN
1889 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1890 IF (x_msg_data IS NOT NULL) THEN
1891 x_msg_data := x_msg_data||' -> Check_Object_Dependency ';
1892 ELSE
1893 x_msg_data := SQLERRM||' at Check_Object_Dependency ';
1894 END IF;
1895
1896 END Check_Object_Dependency;
1897
1898
1899 /*
1900 This function returns the first responsibility id for the form function.
1901 This is a specific case called from SONAR notifications.
1902 */
1903 FUNCTION get_form_function_resp_id (
1904 p_function_name IN VARCHAR2
1905 , p_user_id IN NUMBER
1906 )
1907 RETURN NUMBER
1908 IS
1909
1910 CURSOR c_menu_id IS
1911 SELECT menu_id
1912 FROM fnd_menu_entries fnd_mnu_ent, fnd_form_functions fnd_ff
1913 WHERE fnd_mnu_ent.function_id = fnd_ff.function_id
1914 AND fnd_ff.function_name = p_function_name;
1915 l_menu_id_rec c_menu_id%ROWTYPE;
1916
1917 l_function_id FND_FORM_FUNCTIONS.FUNCTION_ID%TYPE;
1918 l_resp_id FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE;
1919
1920 BEGIN
1921
1922 IF (p_function_name IS NOT NULL) THEN
1923
1924 FOR l_menu_id_rec IN c_menu_id LOOP
1925 l_resp_id := get_menu_resp_id
1926 ( p_sub_menu_id => l_menu_id_rec.menu_id
1927 , p_user_id => p_user_id
1928 , p_count => 0
1929 );
1930 IF (l_resp_id IS NOT NULL) THEN
1931 RETURN l_resp_id;
1932 END IF;
1933 END LOOP;
1934
1935 END IF;
1936
1937 RETURN -1;
1938
1939 EXCEPTION
1940 WHEN OTHERS THEN
1941 RETURN -1;
1942 END get_form_function_resp_id;
1943
1944
1945 /*
1946 Recursive function call to get the respId.
1947 */
1948 FUNCTION get_menu_resp_id (
1949 p_sub_menu_id IN NUMBER
1950 , p_user_id IN NUMBER
1951 , p_count IN NUMBER
1952 )
1953 RETURN NUMBER
1954 IS
1955
1956 CURSOR c_resp_id(l_menu_id NUMBER, l_user_id NUMBER) IS
1957 SELECT fnd_resp.responsibility_id
1958 FROM fnd_responsibility fnd_resp, fnd_menu_entries fnd_mnu_ent, fnd_user_resp_groups fnd_usr_resp
1959 WHERE fnd_resp.menu_id = fnd_mnu_ent.menu_id
1960 AND fnd_usr_resp.responsibility_id = fnd_resp.responsibility_id
1961 AND fnd_mnu_ent.menu_id = l_menu_id
1962 AND fnd_usr_resp.user_id = l_user_id
1963 AND fnd_usr_resp.start_date <= sysdate
1964 AND (fnd_usr_resp.end_date is null or fnd_usr_resp.end_date >= sysdate)
1965 AND fnd_resp.start_date <= sysdate
1966 AND (fnd_resp.end_date is null or fnd_resp.end_date >= sysdate)
1967 AND rownum < 2;
1968
1969 CURSOR c_sub_menu_id(l_sub_menu_id NUMBER) IS
1970 SELECT menu_id
1971 FROM fnd_menu_entries
1972 WHERE sub_menu_id = l_sub_menu_id;
1973 l_sub_menu_id_rec c_sub_menu_id%ROWTYPE;
1974
1975 l_resp_id FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE;
1976 l_count NUMBER;
1977
1978 BEGIN
1979
1980 l_count := p_count;
1981
1982 -- Just to avoid infinite loop we are taking this safety measure
1983 IF (l_count > 100) THEN
1984 RETURN -1;
1985 END IF;
1986
1987 IF (c_resp_id%ISOPEN) THEN
1988 CLOSE c_resp_id;
1989 END IF;
1990
1991 OPEN c_resp_id(p_sub_menu_id, p_user_id);
1992 FETCH c_resp_id INTO l_resp_id;
1993
1994 IF (c_resp_id%FOUND) THEN
1995 CLOSE c_resp_id;
1996 RETURN l_resp_id;
1997 END IF;
1998
1999 IF (c_resp_id%ISOPEN) THEN
2000 CLOSE c_resp_id;
2001 END IF;
2002
2003 FOR l_sub_menu_id_rec IN c_sub_menu_id(p_sub_menu_id) LOOP
2004 l_resp_id := get_menu_resp_id
2005 ( p_sub_menu_id => l_sub_menu_id_rec.menu_id
2006 , p_user_id => p_user_id
2007 , p_count => l_count + 1
2008 );
2009 IF (l_resp_id IS NOT NULL) THEN
2010 RETURN l_resp_id;
2011 END IF;
2012 END LOOP;
2013
2014 RETURN l_resp_id;
2015
2016 EXCEPTION
2017 WHEN OTHERS THEN
2018 IF (c_resp_id%ISOPEN) THEN
2019 CLOSE c_resp_id;
2020 END IF;
2021 RETURN -1;
2022 END get_menu_resp_id;
2023
2024
2025 /*
2026 Returns the first form function for the region code.
2027 */
2028 FUNCTION get_form_function_from_region (
2029 p_region_code IN VARCHAR2
2030 )
2031 RETURN VARCHAR2
2032 IS
2033
2034 CURSOR c_function_name(p_region_code VARCHAR2) IS
2035 SELECT function_name
2036 FROM fnd_form_functions
2037 WHERE ((type = 'JSP' AND (web_html_call LIKE 'bisviewm.jsp%' OR web_html_call like 'OA.jsp?page=/oracle/apps/bis/report/webui/BISReportPG%'))
2038 OR (type = 'WWW' AND LOWER(web_html_call) LIKE 'bisviewer.showreport%'))
2039 AND UPPER(parameters) LIKE UPPER('%pRegionCode=' || p_region_code || '%')
2040 AND rownum < 2;
2041
2042 l_function_name FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE;
2043
2044 BEGIN
2045
2046 IF (c_function_name%ISOPEN) THEN
2047 CLOSE c_function_name;
2048 END IF;
2049 OPEN c_function_name(p_region_code);
2050 FETCH c_function_name INTO l_function_name;
2051 IF (c_function_name%FOUND) THEN
2052 CLOSE c_function_name;
2053 RETURN l_function_name;
2054 END IF;
2055
2056 CLOSE c_function_name;
2057
2058 RETURN l_function_name;
2059
2060 EXCEPTION
2061 WHEN OTHERS THEN
2062 IF (c_function_name%ISOPEN) THEN
2063 CLOSE c_function_name;
2064 END IF;
2065 RETURN NULL;
2066 END get_form_function_from_region;
2067
2068 /*
2069 Returns responsibility id if the owner is a responsibility in wf_roles
2070 */
2071 FUNCTION is_owner_responsibility (
2072 p_owner IN VARCHAR2
2073 )
2074 RETURN NUMBER
2075 IS
2076 l_resp_id NUMBER;
2077 CURSOR c_resp(p_owner VARCHAR2) IS
2078 SELECT fnd_resp.responsibility_id
2079 FROM fnd_responsibility fnd_resp, wf_role_lov_vl wf
2080 WHERE fnd_resp.responsibility_id = wf.orig_system_id
2081 AND wf.name = p_owner
2082 AND wf.orig_system like 'FND_RESP%'
2083 AND rownum < 2; -- Just to avoid any failure here.
2084
2085 BEGIN
2086
2087 IF (c_resp%ISOPEN) THEN
2088 CLOSE c_resp;
2089 END IF;
2090 OPEN c_resp(p_owner);
2091 FETCH c_resp INTO l_resp_id;
2092 IF (c_resp%FOUND) THEN
2093 CLOSE c_resp;
2094 RETURN l_resp_id;
2095 END IF;
2096
2097 CLOSE c_resp;
2098
2099 RETURN l_resp_id;
2100
2101 EXCEPTION
2102 WHEN OTHERS THEN
2103 IF (c_resp%ISOPEN) THEN
2104 CLOSE c_resp;
2105 END IF;
2106 RETURN NULL;
2107 END is_owner_responsibility;
2108
2109
2110 /*
2111 Returns user id if the owner is a user in wf_roles
2112 */
2113
2114 FUNCTION is_owner_user (
2115 p_owner IN VARCHAR2
2116 )
2117 RETURN NUMBER
2118 IS
2119 l_user_id NUMBER;
2120 CURSOR c_user(p_owner VARCHAR2) IS
2121 SELECT fnd_usr.user_id
2122 FROM fnd_user fnd_usr, wf_role_lov_vl wf
2123 WHERE wf.name = p_owner
2124 AND ((wf.orig_system = 'FND_USR' AND fnd_usr.user_id = wf.orig_system_id) OR
2125 (wf.orig_system = 'PER' AND fnd_usr.employee_id = wf.orig_system_id)
2126 );
2127
2128 BEGIN
2129
2130 IF (c_user%ISOPEN) THEN
2131 CLOSE c_user;
2132 END IF;
2133 OPEN c_user(p_owner);
2134 FETCH c_user INTO l_user_id;
2135 IF (c_user%FOUND) THEN
2136 CLOSE c_user;
2137 RETURN l_user_id;
2138 END IF;
2139
2140 CLOSE c_user;
2141
2142 RETURN l_user_id;
2143
2144 EXCEPTION
2145 WHEN OTHERS THEN
2146 IF (c_user%ISOPEN) THEN
2147 CLOSE c_user;
2148 END IF;
2149 RETURN NULL;
2150 END is_owner_user;
2151
2152 /*
2153 Checks whether an object is created by 1,2 or 120 to 129 users
2154 p_created_By : CREATED_BY value of the object
2155 Returns 1 if the object is seeded
2156 Returns 0 otherwise
2157 Followed the logic from AFLDUTLB.pls
2158 */
2159
2160 FUNCTION is_Seeded (
2161 p_created_By IN NUMBER
2162 )
2163 RETURN NUMBER
2164 IS
2165 l_isSeeded NUMBER;
2166 BEGIN
2167 IF(p_created_By = 1 OR p_created_By = 2 OR (p_created_By >= 120 AND p_created_By <= 129)) THEN
2168 RETURN 1;
2169 END IF;
2170 RETURN 0;
2171 EXCEPTION
2172 WHEN OTHERS THEN
2173 RETURN 0;
2174 END is_Seeded;
2175
2176 /*
2177 Checks whether an object is created by 1,2 or 120 to 129 users
2178 p_created_By : CREATED_BY value of the object
2179 p_TrueValue : String to be returned if the object is seeded
2180 p_FalseValue : String to be returned if the object is not seeded
2181 Followed the logic from AFLDUTLB.pls
2182 */
2183
2184 FUNCTION is_Seeded (
2185 p_created_By IN NUMBER
2186 , p_TrueValue IN VARCHAR2
2187 , p_FalseValue IN VARCHAR2
2188 )
2189 RETURN VARCHAR2
2190 IS
2191 l_isSeeded NUMBER;
2192 BEGIN
2193 IF(p_created_By = 1 OR p_created_By = 2 OR (p_created_By >= 120 AND p_created_By <= 129)) THEN
2194 RETURN p_TrueValue;
2195 END IF;
2196 RETURN p_FalseValue;
2197 EXCEPTION
2198 WHEN OTHERS THEN
2199 RETURN p_FalseValue;
2200 END is_Seeded;
2201
2202 /*
2203 Called from PMFUtil.java to get the respId and secGrpId for a measure's report.
2204 p_function_name : BIS_INDICATORS.function_name
2205 p_region_code : region code from BIS_INDICATORS.actual_data_source
2206 p_owner : Target owner from wf_roles
2207 If the target owner is itself a responsibility, that will be used straightaway.
2208 If the owner is a user, we will use this user_id to get the responsibility for the form function.
2209 If the owner is neither a responsibility nor a user, then return -1.
2210 */
2211 PROCEDURE get_respId_for_measure_report (
2212 p_function_name IN VARCHAR2
2213 , p_region_code IN VARCHAR2
2214 , p_owner IN VARCHAR2
2215 , x_resp_id OUT NOCOPY NUMBER
2216 , x_sec_grp_id OUT NOCOPY NUMBER
2217 )
2218 IS
2219 l_resp_id NUMBER;
2220 l_user_id NUMBER;
2221 l_function_name FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE;
2222
2223 BEGIN
2224
2225 x_resp_id := -1;
2226
2227 l_resp_id := is_owner_responsibility(p_owner => p_owner);
2228
2229 IF (l_resp_id IS NOT NULL) THEN
2230
2231 x_resp_id := l_resp_id;
2232 x_sec_grp_id := get_sec_grp_id_for_resp_role(p_role_resp => p_owner);
2233
2234 ELSE
2235
2236 l_user_id := is_owner_user(p_owner => p_owner);
2237
2238 IF (l_user_id IS NOT NULL) THEN
2239
2240 l_function_name := p_function_name;
2241 IF ((l_function_name IS NULL) AND (p_region_code IS NOT NULL)) THEN
2242 l_function_name := get_form_function_from_region(p_region_code => p_region_code);
2243 END IF;
2244
2245 IF (l_function_name IS NOT NULL) THEN
2246 x_resp_id := get_form_function_resp_id
2247 ( p_function_name => l_function_name
2248 , p_user_id => l_user_id
2249 );
2250 x_sec_grp_id := get_sec_grp_id_for_user_role
2251 ( p_resp_id => x_resp_id
2252 , p_user_id => l_user_id
2253 );
2254 END IF;
2255 END IF;
2256 END IF;
2257
2258 EXCEPTION
2259 WHEN OTHERS THEN
2260 NULL;
2261 END get_respId_for_measure_report;
2262
2263 /*
2264 Takes as input user id and responsibility id, and returns the first
2265 security group id for this combination from FND_USER_RESP_GROUPS_DIRECT
2266 */
2267 FUNCTION get_sec_grp_id_for_user_role (
2268 p_resp_id IN NUMBER
2269 , p_user_id IN NUMBER
2270 )
2271 RETURN NUMBER
2272 IS
2273 l_sec_grp_id NUMBER;
2274
2275 CURSOR c_sec_grp_id IS
2276 SELECT security_group_id
2277 FROM fnd_user_resp_groups_direct
2278 WHERE user_id = p_user_id
2279 AND responsibility_id = p_resp_id
2280 AND rownum < 2;
2281 BEGIN
2282 l_sec_grp_id := -1;
2283
2284 IF (c_sec_grp_id%ISOPEN) THEN
2285 CLOSE c_sec_grp_id;
2286 END IF;
2287 OPEN c_sec_grp_id;
2288 FETCH c_sec_grp_id INTO l_sec_grp_id;
2289 IF (c_sec_grp_id%FOUND) THEN
2290 CLOSE c_sec_grp_id;
2291 RETURN l_sec_grp_id;
2292 END IF;
2293 CLOSE c_sec_grp_id;
2294
2295 RETURN l_sec_grp_id;
2296 EXCEPTION
2297 WHEN OTHERS THEN
2298 IF (c_sec_grp_id%ISOPEN) THEN
2299 CLOSE c_sec_grp_id;
2300 END IF;
2301 END get_sec_grp_id_for_user_role;
2302
2303 /*
2304 Takes as an input the role name which is assumed to be a responsibility.
2305 The role name is of the form FND_RESP|HRI|PKP_QAHRI2|8338. This API
2306 will parse this role name to get the SECURITY_GROUP_KEY which is the
2307 last part of the role name. Using this SECURITY_GROUP_KEY value we will
2308 find out the SECURITY_GROUP_ID using table FND_SECURITY_GROUPS_VL.
2309 */
2310 FUNCTION get_sec_grp_id_for_resp_role (
2311 p_role_resp IN VARCHAR2
2312 )
2313 RETURN NUMBER
2314 IS
2315 l_role_name VARCHAR2(320);
2316 l_sec_grp_key VARCHAR2(30);
2317 l_sec_grp_id NUMBER;
2318 l_index NUMBER;
2319
2320 CURSOR c_sec_grp_id IS
2321 SELECT security_group_id
2322 FROM fnd_security_groups
2323 WHERE security_group_key = l_sec_grp_key;
2324 BEGIN
2325 l_sec_grp_id := -1;
2326 l_role_name := p_role_resp;
2327
2328 l_index := INSTR(l_role_name, '|', -1); -- indexOf '|' from end
2329 IF (l_index > 0) THEN
2330 l_sec_grp_key := SUBSTR(l_role_name, l_index + 1, LENGTH(l_role_name));
2331 END IF;
2332
2333 IF (l_sec_grp_key IS NOT NULL) THEN
2334 IF (c_sec_grp_id%ISOPEN) THEN
2335 CLOSE c_sec_grp_id;
2336 END IF;
2337 OPEN c_sec_grp_id;
2338 FETCH c_sec_grp_id INTO l_sec_grp_id;
2339 IF (c_sec_grp_id%FOUND) THEN
2340 CLOSE c_sec_grp_id;
2341 RETURN l_sec_grp_id;
2342 END IF;
2343 CLOSE c_sec_grp_id;
2344 END IF;
2345
2346 RETURN l_sec_grp_id;
2347
2348 EXCEPTION
2349 WHEN OTHERS THEN
2350 IF (c_sec_grp_id%ISOPEN) THEN
2351 CLOSE c_sec_grp_id;
2352 END IF;
2353 RETURN -1;
2354 END get_sec_grp_id_for_resp_role;
2355
2356 /*
2357 This api will take comma seperated dim plus dim levels in the report and
2358 returns comma seperated invalid dim plus dim levels.
2359 */
2360
2361 FUNCTION inv_dim_dimlevel_rel (
2362 p_comma_sep_dim_dimlevel IN VARCHAR2
2363 )
2364 RETURN VARCHAR2
2365 IS
2366
2367 l_comma_sep_dim_dimlevel VARCHAR2(1000);
2368 l_pos NUMBER;
2369 l_dim_plus_dimlevel VARCHAR2(100);
2370 l_inv_dim_plus_dimlevel VARCHAR2(100);
2371 l_inv_comma_sep_dim_dimlevel VARCHAR2(1000);
2372 l_invalid VARCHAR2(1);
2373
2374 BEGIN
2375 l_comma_sep_dim_dimlevel := p_comma_sep_dim_dimlevel;
2376
2377 WHILE (l_comma_sep_dim_dimlevel IS NOT NULL) LOOP
2378
2379 l_pos := INSTR(l_comma_sep_dim_dimlevel, ',');
2380
2381 IF (l_pos > 0) THEN
2382
2383 l_dim_plus_dimlevel := TRIM(SUBSTR(l_comma_sep_dim_dimlevel, 1, l_pos - 1));
2384
2385 l_invalid := is_dim_plus_dimlevel_invalid(p_dim_plus_dimlevel => l_dim_plus_dimlevel);
2386
2387
2388 IF ( l_invalid = FND_API.G_TRUE ) THEN
2389
2390 IF (l_inv_comma_sep_dim_dimlevel IS NULL) THEN
2391 l_inv_comma_sep_dim_dimlevel := l_dim_plus_dimlevel;
2392 ELSE
2393 l_inv_comma_sep_dim_dimlevel := l_dim_plus_dimlevel || ', ' || l_inv_comma_sep_dim_dimlevel;
2394 END IF;
2395
2396 END IF;
2397
2398 l_comma_sep_dim_dimlevel := TRIM(SUBSTR(l_comma_sep_dim_dimlevel, l_pos + 1));
2399
2400 ELSE
2401
2402 l_invalid := is_dim_plus_dimlevel_invalid(p_dim_plus_dimlevel => l_comma_sep_dim_dimlevel);
2403
2404
2405 IF ( l_invalid = FND_API.G_TRUE ) THEN
2406
2407 IF (l_inv_comma_sep_dim_dimlevel IS NULL) THEN
2408 l_inv_comma_sep_dim_dimlevel := l_comma_sep_dim_dimlevel;
2409 ELSE
2410 l_inv_comma_sep_dim_dimlevel := l_comma_sep_dim_dimlevel || ', ' || l_inv_comma_sep_dim_dimlevel;
2411 END IF;
2412
2413 END IF;
2414
2415 l_comma_sep_dim_dimlevel := NULL;
2416
2417 END IF;
2418
2419 END LOOP;
2420
2421
2422 RETURN l_inv_comma_sep_dim_dimlevel;
2423
2424 EXCEPTION
2425 WHEN OTHERS THEN
2426 RETURN NULL;
2427
2428 END inv_dim_dimlevel_rel;
2429
2430
2431
2432
2433 /*
2434 This api checks whether the dim plus dim level is valid or not. If valid returns false
2435 */
2436
2437 FUNCTION is_dim_plus_dimlevel_invalid (
2438 p_dim_plus_dimlevel IN VARCHAR2
2439 )
2440 RETURN VARCHAR2
2441 IS
2442
2443 l_dim_plus_dimlevel VARCHAR2(100);
2444 l_pos NUMBER;
2445 l_dim bis_indicators.short_name%TYPE;
2446 l_dim_level bis_levels.short_name%TYPE;
2447 l_num NUMBER;
2448
2449 CURSOR c_dim_plus_dimlevel_rel_cursor(l_dim VARCHAR2, l_dim_level VARCHAR2) IS
2450 SELECT 1
2451 FROM bsc_sys_dim_groups_vl sys_dim_group, bsc_sys_dim_levels_vl sys_dim_levels, bsc_sys_dim_levels_by_group sys_dim_level_group, bis_levels bis_level, bis_dimensions bis_dim
2452 WHERE sys_dim_group.dim_group_id = sys_dim_level_group.dim_group_id
2453 AND sys_dim_levels.dim_level_id = sys_dim_level_group.dim_level_id
2454 AND sys_dim_group.dim_group_id = bis_dim.dim_grp_id
2455 AND sys_dim_levels.short_name = bis_level.short_name
2456 AND sys_dim_group.short_name = l_dim
2457 AND sys_dim_levels.short_name = l_dim_level;
2458
2459
2460 BEGIN
2461
2462 l_dim_plus_dimlevel := p_dim_plus_dimlevel;
2463
2464 l_pos := INSTR(l_dim_plus_dimlevel, '+');
2465
2466 IF (l_pos > 0) THEN
2467
2468 l_dim := TRIM(SUBSTR(l_dim_plus_dimlevel, 1, l_pos - 1));
2469 l_dim_level := TRIM(SUBSTR(l_dim_plus_dimlevel, l_pos + 1));
2470
2471
2472
2473 IF (c_dim_plus_dimlevel_rel_cursor%ISOPEN) THEN
2474 CLOSE c_dim_plus_dimlevel_rel_cursor;
2475 END IF;
2476
2477 OPEN c_dim_plus_dimlevel_rel_cursor(l_dim, l_dim_level);
2478
2479 FETCH c_dim_plus_dimlevel_rel_cursor INTO l_num;
2480
2481 IF (c_dim_plus_dimlevel_rel_cursor%FOUND) THEN
2482
2483
2484 IF (l_num > 0) THEN
2485 CLOSE c_dim_plus_dimlevel_rel_cursor;
2486 RETURN FND_API.G_FALSE;
2487 END IF;
2488
2489 ELSE
2490
2491 CLOSE c_dim_plus_dimlevel_rel_cursor;
2492 RETURN FND_API.G_TRUE;
2493
2494 END IF;
2495
2496
2497
2498 END IF;
2499
2500 RETURN FND_API.G_FALSE;
2501
2502 EXCEPTION
2503 WHEN OTHERS THEN
2504 IF (c_dim_plus_dimlevel_rel_cursor%ISOPEN) THEN
2505 CLOSE c_dim_plus_dimlevel_rel_cursor;
2506 END IF;
2507 RETURN FND_API.G_FALSE;
2508
2509 END is_dim_plus_dimlevel_invalid;
2510
2511 /*
2512 Returns last date of previous time period as x_prev_asofdate
2513 */
2514 PROCEDURE get_previous_asofdate
2515 ( p_dimensionlevel IN VARCHAR2
2516 , p_time_comparison_type IN VARCHAR2
2517 , p_asof_date IN DATE
2518 , x_prev_asofdate OUT NOCOPY DATE
2519 , x_return_status OUT NOCOPY VARCHAR2
2520 , x_msg_count OUT NOCOPY NUMBER
2521 , x_msg_data OUT NOCOPY VARCHAR2
2522 )
2523 IS
2524 l_sql VARCHAR2(1000);
2525 l_asof_date DATE;
2526 BEGIN
2527
2528 IF (p_asof_date IS NULL) THEN
2529 l_asof_date := SYSDATE;
2530 ELSE
2531 l_asof_date := p_asof_date;
2532 END IF;
2533
2534 IF ( p_time_comparison_type IS NULL OR p_time_comparison_type = 'TIME_COMPARISON_TYPE+SEQUENTIAL') THEN
2535
2536 IF (p_dimensionlevel = 'TIME+FII_TIME_WEEK') THEN
2537 l_sql := 'BEGIN :1 := FII_TIME_API.pwk_end(:2); END;'; -- Previous Week end date
2538 EXECUTE IMMEDIATE l_sql USING OUT x_prev_asofdate , IN l_asof_date;
2539 END IF;
2540 IF (p_dimensionlevel = 'TIME+FII_TIME_ENT_PERIOD') THEN
2541 l_sql := 'BEGIN :1 := FII_TIME_API.ent_pper_end(:2); END;'; -- Previous enterprise period end date
2542 EXECUTE IMMEDIATE l_sql USING OUT x_prev_asofdate , IN l_asof_date;
2543 END IF;
2544 IF (p_dimensionlevel = 'TIME+FII_TIME_ENT_QTR') THEN
2545 l_sql := 'BEGIN :1 := FII_TIME_API.ent_pqtr_end(:2); END;'; -- Previous enterprise quarter end date
2546 EXECUTE IMMEDIATE l_sql USING OUT x_prev_asofdate , IN l_asof_date;
2547 END IF;
2548 IF (p_dimensionlevel = 'TIME+FII_TIME_ENT_YEAR') THEN
2549 l_sql := 'BEGIN :1 := FII_TIME_API.ent_pyr_end(:2); END;'; -- Previous Enterprise year end date
2550 EXECUTE IMMEDIATE l_sql USING OUT x_prev_asofdate , IN l_asof_date;
2551 END IF;
2552 IF (p_dimensionlevel = 'TIME+FII_TIME_DAY') THEN
2553 x_prev_asofdate := l_asof_date - 1;
2554 END IF;
2555
2556 IF (p_dimensionlevel = 'TIME+FII_ROLLING_WEEK') THEN
2557 l_sql := 'BEGIN :1 := FII_TIME_API.rwk_start(:2); END;'; -- Rolling Week start date
2558 EXECUTE IMMEDIATE l_sql USING OUT x_prev_asofdate, IN l_asof_date;
2559 x_prev_asofdate := x_prev_asofdate - 1 ;
2560 END IF;
2561 IF (p_dimensionlevel = 'TIME+FII_ROLLING_MONTH') THEN
2562 l_sql := 'BEGIN :1 := FII_TIME_API.rmth_start(:2); END;'; -- Rolling Month start date
2563 EXECUTE IMMEDIATE l_sql USING OUT x_prev_asofdate, IN l_asof_date;
2564 x_prev_asofdate := x_prev_asofdate - 1 ;
2565 END IF;
2566 IF (p_dimensionlevel = 'TIME+FII_ROLLING_QTR') THEN
2567 l_sql := 'BEGIN :1 := FII_TIME_API.rqtr_start(:2); END;'; -- Rolling Quarter start date
2568 EXECUTE IMMEDIATE l_sql USING OUT x_prev_asofdate, IN l_asof_date;
2569 x_prev_asofdate := x_prev_asofdate - 1 ;
2570 END IF;
2571 IF (p_dimensionlevel = 'TIME+FII_ROLLING_YEAR') THEN
2572 l_sql := 'BEGIN :1 := FII_TIME_API.ryr_start(:2); END;'; -- Rolling Year start date
2573 EXECUTE IMMEDIATE l_sql USING OUT x_prev_asofdate, IN l_asof_date;
2574 x_prev_asofdate := x_prev_asofdate - 1 ;
2575 END IF;
2576
2577 ELSE -- p_time_comparison_type = 'TIME_COMPARISON_TYPE+YEARLY'
2578
2579 IF (p_dimensionlevel = 'TIME+FII_TIME_WEEK') THEN
2580 l_sql := 'BEGIN :1 := FII_TIME_API.lyswk_end(:2); END;'; -- Last year same week end date
2581 EXECUTE IMMEDIATE l_sql USING OUT x_prev_asofdate , IN l_asof_date;
2582 END IF;
2583 IF (p_dimensionlevel = 'TIME+FII_TIME_ENT_PERIOD') THEN
2584 l_sql := 'BEGIN :1 := FII_TIME_API.ent_lysper_end(:2); END;'; -- Last year same Enterprise period end date
2585 EXECUTE IMMEDIATE l_sql USING OUT x_prev_asofdate , IN l_asof_date;
2586 END IF;
2587 IF (p_dimensionlevel = 'TIME+FII_TIME_ENT_QTR') THEN
2588 l_sql := 'BEGIN :1 := FII_TIME_API.ent_lysqtr_end(:2); END;'; -- Last year same Enterprise quarter end date
2589 EXECUTE IMMEDIATE l_sql USING OUT x_prev_asofdate , IN l_asof_date;
2590 END IF;
2591 IF (p_dimensionlevel = 'TIME+FII_TIME_ENT_YEAR') THEN
2592 l_sql := 'BEGIN :1 := FII_TIME_API.ent_pyr_end(:2); END;'; -- Previous Enterprise year end date
2593 EXECUTE IMMEDIATE l_sql USING OUT x_prev_asofdate , IN l_asof_date;
2594 END IF;
2595 IF (p_dimensionlevel = 'TIME+FII_TIME_DAY') THEN
2596 x_prev_asofdate := add_months(l_asof_date, -12);
2597 END IF;
2598
2599 IF ( p_dimensionlevel = 'TIME+FII_ROLLING_WEEK' OR
2600 p_dimensionlevel = 'TIME+FII_ROLLING_MONTH' OR
2601 p_dimensionlevel = 'TIME+FII_ROLLING_QTR' OR
2602 p_dimensionlevel = 'TIME+FII_ROLLING_YEAR') THEN
2603 x_prev_asofdate := add_months(l_asof_date, -12);
2604 END IF;
2605
2606 END IF;
2607
2608 IF (x_prev_asofdate IS NULL OR (LENGTH(x_prev_asofdate) = 0)) THEN
2609 BIS_PMV_TIME_LEVELS_PVT.get_bis_common_start_date
2610 ( x_prev_asof_date => x_prev_asofdate
2611 , x_return_Status => x_return_status
2612 , x_msg_count => x_msg_count
2613 , x_msg_data => x_msg_data );
2614 END IF;
2615
2616 x_return_status := FND_API.G_RET_STS_SUCCESS;
2617
2618 EXCEPTION
2619 WHEN FND_API.G_EXC_ERROR THEN
2620 x_return_status := FND_API.G_RET_STS_ERROR;
2621 FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
2622 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2623 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2624 FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
2625 WHEN OTHERS THEN
2626 IF (x_prev_asofdate IS NULL or (LENGTH(x_prev_asofdate) = 0)) THEN
2627 BIS_PMV_TIME_LEVELS_PVT.get_bis_common_start_date
2628 ( x_prev_asof_date => x_prev_asofdate
2629 , x_return_Status => x_return_status
2630 , x_msg_count => x_msg_count
2631 , x_msg_data => x_msg_data );
2632 END IF;
2633 FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
2634 END get_previous_asofdate;
2635
2636 /* The following function returns measure name by taking RegionCode,Attribute1, Attribute2
2637 as parameters.
2638 */
2639
2640 FUNCTION get_measure_name(
2641 p_region_code IN VARCHAR2,
2642 p_attribute1 IN VARCHAR2,
2643 p_attribute2 IN VARCHAR2)
2644 RETURN VARCHAR2
2645 IS
2646 l_measure_name bis_indicators_vl.name%type;
2647 BEGIN
2648 IF (p_attribute1 = BIS_AK_REGION_PUB.C_MEASURE_NO_TARGET OR p_attribute1 = BIS_AK_REGION_PUB.C_MEASURE) THEN
2649 SELECT
2650 bis_measures.name INTO l_measure_name
2651 FROM
2652 bis_indicators_vl bis_measures
2653 WHERE
2654 bis_measures.short_name = p_attribute2;
2655 ELSIF (p_attribute1 = BIS_AK_REGION_PUB.C_CHANGE_MEASURE_NO_TARGET OR p_attribute1 = BIS_AK_REGION_PUB.C_COMPARE_TO_MEASURE_NO_TARGET OR p_attribute1 = BIS_AK_REGION_PUB.C_PERCENT_OF_TOTAL) THEN
2656 SELECT
2657 bis_measures.name INTO l_measure_name
2658 FROM
2659 bis_indicators_vl bis_measures,
2660 ak_region_items_vl akRegionItems
2661 WHERE
2662 akRegionItems.region_code = p_region_code AND
2663 akRegionItems.attribute_code = p_attribute2 AND
2664 akRegionItems.attribute2 = bis_measures.short_name;
2665 END IF;
2666 RETURN l_measure_name;
2667 EXCEPTION
2668 WHEN OTHERS THEN
2669 RETURN NULL;
2670 END get_measure_name;
2671
2672 PROCEDURE get_next_measure_data_source
2673 ( p_measure_short_name IN VARCHAR2
2674 , p_current_region_code IN VARCHAR2
2675 , p_current_region_appid IN NUMBER
2676 , x_next_region_code OUT NOCOPY VARCHAR2
2677 , x_next_region_appid OUT NOCOPY NUMBER
2678 , x_next_source_attrcode OUT NOCOPY VARCHAR2
2679 , x_next_source_appid OUT NOCOPY NUMBER
2680 , x_next_compare_attrcode OUT NOCOPY VARCHAR2
2681 , x_next_compare_appid OUT NOCOPY NUMBER
2682 , x_next_function_name OUT NOCOPY VARCHAR2 --Bug 5495960
2683 , x_next_enable_link OUT NOCOPY VARCHAR2 --Bug 5495960
2684 )
2685 IS
2686 --////////Filtering out the Simulation reports from the next available report ////////////////
2687
2688 CURSOR c_next_ds IS
2689 SELECT region_code, region_application_id, attribute_code, attribute_application_id
2690 FROM ak_region_items
2691 WHERE attribute2 = p_measure_short_name
2692 AND attribute1 IN ('MEASURE_NOTARGET', 'MEASURE')
2693 AND NOT(region_code = p_current_region_code AND region_application_id = p_current_region_appid)
2694 AND NVL(BIS_REPORT_PUB.getRegionDataSourceType(p_current_region_code),' ') <> 'MULTIPLE_DATA_SOURCE'
2695 AND BIS_UTIL.Is_Simulation_Report(region_code) <> FND_API.G_TRUE
2696 AND rownum < 2 ;
2697
2698 CURSOR c_compare_to_col(p_region_code VARCHAR2, p_region_appid NUMBER, p_measure_attrcode VARCHAR2) IS
2699 SELECT attribute_code, attribute_application_id
2700 FROM ak_region_items
2701 WHERE region_code = p_region_code
2702 AND region_application_id = p_region_appid
2703 AND attribute1 IN ('COMPARE_TO_MEASURE', 'COMPARE_TO_MEASURE_NO_TARGET')
2704 AND attribute2 = p_measure_attrcode ;
2705 BEGIN
2706
2707 IF (c_next_ds%ISOPEN) THEN
2708 CLOSE c_next_ds;
2709 END IF;
2710
2711 OPEN c_next_ds;
2712 FETCH c_next_ds INTO x_next_region_code, x_next_region_appid, x_next_source_attrcode, x_next_source_appid;
2713 IF (c_next_ds%FOUND) THEN
2714 IF (c_compare_to_col%ISOPEN) THEN
2715 CLOSE c_compare_to_col;
2716 END IF;
2717
2718 OPEN c_compare_to_col(x_next_region_code, x_next_region_appid, x_next_source_attrcode);
2719 FETCH c_compare_to_col INTO x_next_compare_attrcode, x_next_compare_appid;
2720 CLOSE c_compare_to_col;
2721
2722 x_next_function_name := get_form_function_from_region(x_next_region_code);
2723 IF x_next_function_name IS NOT NULL THEN
2724 x_next_enable_link := 'Y';
2725 END IF;
2726 END IF;
2727
2728 CLOSE c_next_ds;
2729
2730 EXCEPTION
2731 WHEN OTHERS THEN
2732 IF (c_next_ds%ISOPEN) THEN
2733 CLOSE c_next_ds;
2734 END IF;
2735 IF (c_compare_to_col%ISOPEN) THEN
2736 CLOSE c_compare_to_col;
2737 END IF;
2738 END get_next_measure_data_source;
2739
2740 /* The following functions returns a comma seperated list of dimensions
2741 for which a particular dimension object is associated */
2742
2743 FUNCTION get_dimen_by_dim_object
2744 ( p_dim_lev_short_name IN VARCHAR2
2745 ) RETURN VARCHAR2
2746 IS
2747 l_dim_names VARCHAR2(5000);
2748 CURSOR c_dimensions IS
2749 SELECT dim_group.name
2750 FROM
2751 bsc_sys_dim_levels_by_group dim_lev_by_group,
2752 bsc_sys_dim_levels_vl dim_lev,
2753 bsc_sys_dim_groups_vl dim_group
2754 WHERE
2755 dim_lev.short_name=p_dim_lev_short_name
2756 AND dim_lev_by_group.dim_level_id = dim_lev.dim_level_id
2757 AND dim_lev_by_group.dim_group_id = dim_group.dim_group_id
2758 AND bis_util.is_seeded(dim_group.created_by,'Y','N') = 'Y';
2759
2760 BEGIN
2761 FOR c_dimensions_rec IN c_dimensions LOOP
2762 IF (l_dim_names IS NULL) THEN
2763 l_dim_names := c_dimensions_rec.name;
2764 ELSE
2765 l_dim_names := l_dim_names || ', ' || c_dimensions_rec.name;
2766 END IF;
2767 END LOOP;
2768 RETURN l_dim_names;
2769 EXCEPTION
2770 WHEN OTHERS THEN
2771 RETURN NULL;
2772 END get_dimen_by_dim_object;
2773
2774
2775 /******************************************
2776 Get_Default_Value_From_Params : API is used to get the default values of
2777 the dimension objects from parameters which are stored
2778 in form function.
2779 Input : p_parameters : corresponds to the parameter column value
2780 p_attribute2 : attribute2 value corresponding to dim+dim_obj
2781 Creator : ashankar 26-03-07
2782 /******************************************/
2783
2784 FUNCTION Get_Default_Value_From_Params
2785 (
2786 p_parameters IN FND_FORM_FUNCTIONS_VL.parameters%TYPE
2787 , p_attribute2 IN AK_REGION_ITEMS_VL.attribute2%TYPE
2788 )RETURN VARCHAR2 IS
2789
2790 l_default_value VARCHAR2(1000);
2791 l_posPParamsStart NUMBER;
2792 l_posAttr2 NUMBER;
2793 l_posPParamsEnd NUMBER;
2794 l_start NUMBER;
2795 l_end NUMBER;
2796
2797 BEGIN
2798 l_default_value :=NULL;
2799
2800 IF((p_parameters IS NOT NULL) AND (p_attribute2 IS NOT NULL)) THEN
2801
2802 l_posPParamsStart := INSTR(p_parameters,BIS_UTIL.C_FF_PARAM_PARAMETERS);
2803 IF(l_posPParamsStart >=0) THEN
2804 l_posAttr2 := INSTR(p_parameters,REPLACE(p_attribute2,BIS_UTIL.C_CHAR_PLUS,BIS_UTIL.C_CHAR_CARROT) || BIS_UTIL.C_CHAR_AT_THE_RATE,l_posPParamsStart);
2805 l_posPParamsEnd := INSTR(p_parameters,BIS_UTIL.C_PARAM_SEP,l_posAttr2);
2806
2807 IF(l_posPParamsEnd=0)THEN
2808 l_posPParamsEnd := LENGTH(p_parameters);
2809 END IF;
2810
2811 IF(l_posAttr2 >0 AND l_posAttr2 < l_posPParamsEnd) THEN
2812
2813 l_start := INSTR(p_parameters,BIS_UTIL.C_CHAR_AT_THE_RATE,l_posAttr2) +1;
2814 l_end := INSTR(p_parameters,BIS_UTIL.C_CHAR_TILDE,l_posAttr2);
2815 IF(l_end =0)THEN
2816 l_end :=l_posPParamsEnd;
2817 END IF;
2818
2819 IF(l_end =l_start) THEN
2820 l_end := 1;
2821 ELSE
2822 l_end :=l_end-l_start;
2823 END IF;
2824
2825 l_default_value :=SUBSTR(p_parameters,l_start,l_end);
2826 END IF;
2827 END IF;
2828 END IF;
2829 RETURN l_default_value;
2830 EXCEPTION
2831 WHEN OTHERS THEN
2832 RETURN NULL;
2833 END Get_Default_Value_From_Params;
2834
2835 END BIS_UTIL;