[Home] [Help]
PACKAGE BODY: APPS.BIS_TARGET_UTIL
Source
1 PACKAGE BODY BIS_TARGET_UTIL AS
2 /* $Header: BISUTRGB.pls 115.24 99/07/17 16:11:36 porting shi $ */
3
4 -- Functions/ Procedures
5 Procedure Get_Level_name
6 (p_level_id IN Number
7 ,x_level_name OUT Varchar2
8 ,x_return_status OUT Varchar2
9 ,x_msg_count OUT Number
10 ,x_msg_data OUT Varchar2)
11 is
12 l_rtn_val Varchar2(80);
13 Begin
14 x_return_status := FND_API.G_RET_STS_SUCCESS;
15 select short_name
16 into x_level_name
17 from BIS_levels
18 where level_id = p_level_id;
19 EXCEPTION
20
21 WHEN OTHERS THEN
22
23 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
24
25 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
26 THEN
27 FND_MSG_PUB.Add_Exc_Msg
28 ( G_PKG_NAME
29 , 'Get_level_name'
30 );
31 END IF;
32
33 -- Get message count and data
34
35 FND_MSG_PUB.Count_And_Get
36 ( p_count => x_msg_count
37 , p_data => x_msg_data
38 );
39 end Get_Level_name;
40
41 Procedure Create_Ind_Level_View
42 (p_ind_level_name IN Varchar2
43 ,p_msg_init IN Varchar2 default FND_API.G_TRUE
44 ,x_return_status OUT Varchar2
45 ,x_msg_count OUT Number
46 ,x_msg_data OUT Varchar2)
47 is
48 l_cursor Integer;
49 l_sql_result Integer := 0;
50
51 l_view_from_stmt Varchar2(2000) := ' from BIS_target_values val, '||
52 'BIS_business_plans plan,BIS_target_levels lvl ';
53
54 l_view_from_stmt2 Varchar2(2000);
55
56 l_view_from_stmt3 Varchar2(2000) := ' val.target_level_id = lvl.target_level_id ' ||
57 ' and val.plan_id = plan.plan_id' ;
58
59 l_level_col_list1 Varchar2(2000) :=
60 'wf_process,plan_id, plan_short_name,' ||
61 'version_no,current_plan_flag' ;
62 l_level_col_list2 Varchar2(2000) :=
63 ',target, range1_low, range1_high,' ||
64 'range2_low, range2_high, range3_low, range3_high,' ||
65 'role1_id,role2_id,role3_id,role1,role2,role3';
66 l_level_col_list3 Varchar2(2000);
67 l_level_select_list1 Varchar2(2000) :=
68 'lvl.wf_process,PLan.plan_id, plan.short_name,' ||
69 'plan.version_no,plan.current_plan_flag' ;
70 l_level_select_list2 Varchar2(2000) :=
71 ',val.target, val.range1_low, val.range1_high,' ||
72 'val.range2_low, val.range2_high, val.range3_low, val.range3_high,' ||
73 'val.role1_id,'||
74 'val.role2_id,'||
75 'val.role3_id,'||
76 'val.role1,'||
77 'val.role2,'||
78 'val.role3';
79 l_level_select_list3 Varchar2(2000);
80 l_trg_lvl_rec BIS_TARGET_LEVELS%ROWTYPE;
81 l_level_name Varchar2(30);
82 l_period_type Varchar2(15);
83 l_year number(15);
84 l_calendar Varchar2(15);
85 l_return_status Varchar2(30);
86 l_msg Varchar2(100);
87 l_file Varchar2(20);
88 Begin
89 BIS_debug_pub.Debug_OFF;
90
91 l_return_status := FND_API.G_RET_STS_SUCCESS;
92
93 -- Initialize message list.
94 if p_msg_init = FND_API.G_TRUE then
95 FND_MSG_PUB.initialize;
96 end if;
97
98 BIS_debug_pub.add('Selecting row from BIS_target_levels for : '
99 || p_ind_level_name);
100
101 select *
102 into l_trg_lvl_rec
103 from BIS_target_levels
104 where short_name = p_ind_level_name;
105
106 -- Time dimension
107 BIS_debug_pub.add('Select Level Name For Time');
108 if l_trg_lvl_rec.time_level_id is not null then
109 Get_Level_name
110 (p_level_id => l_trg_lvl_rec.time_level_id
111 ,x_level_name => l_level_name
112 ,x_return_status => l_return_status
113 ,x_msg_count => x_msg_count
114 ,x_msg_data => x_msg_data);
115
116 BIS_debug_pub.add('The return status is ' || l_return_status || ': and'
117 || ' time level name is :' || l_level_name);
118
119 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
121 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
122 RAISE FND_API.G_EXC_ERROR;
123 END IF;
124
125 if UPPER(substr(l_level_name,1,5)) <> 'TOTAL' then
126 l_level_col_list1 := l_level_col_list1 || ',' || 'Calendar_' ||replace(l_level_name,' ','_');
127 l_level_col_list3 := l_level_col_list3||', Period_Value, Year, Calendar';
128 l_view_from_stmt3 := ' and '||l_view_from_stmt3;
129
130 if UPPER(substr(l_level_name,1,2)) = 'HR' then
131 l_level_select_list3 := ','||' substr(val.time_level_value,instr(val.time_level_value,''+'',1,1)+1) '
132 ||', '|| ' substr(val.time_level_value,-4), '
133 || 'substr(val.time_level_value,1,instr(val.time_level_value,''+'',1,1)-1)';
134
135 l_view_from_stmt2 := 'and substr(time_level_value,1,2) '||' = ''HR''';
136
137 else
138 l_level_select_list3 := ','||'substr(val.time_level_value,instr(val.time_level_value,''+'',1,1)+1) '
139 ||', '||'to_number(' ||'gl.period_year'||')'||','
140 || 'substr(val.time_level_value,1,instr(val.time_level_value,''+'',1,1)-1)';
141
142 l_view_from_stmt := l_view_from_stmt ||', gl_periods gl ';
143 l_view_from_stmt2 := ' and gl.period_set_name = '
144 ||'substr(val.time_level_value,1,instr(val.time_level_value,''+'',1,1)-1)';
145
146 l_view_from_stmt2 := l_view_from_stmt2||' and gl.period_name = '
147 || 'substr(val.time_level_value,instr(val.time_level_value,''+'',1,1)+1) ';
148 end if;
149
150 -- WHY DOES TOTAL_TIME SHOW?
151 else l_level_col_list1 := l_level_col_list1 || ',' ||replace(l_level_name,' ','_');
152 l_view_from_stmt3 := 'and '||l_view_from_stmt3;
153
154 end if;
155 l_level_select_list1 := l_level_select_list1 || ',' || 'val.time_level_value';
156 l_view_from_stmt := l_view_from_stmt || 'where lvl.short_name = '''
157 || p_ind_level_name || '''';
158 end if;
159
160 -- Org Dimension
161 if l_trg_lvl_rec.org_level_id is not null then
162 BIS_debug_pub.add('Select Level Name For Org');
163 Get_Level_name
164 (p_level_id => l_trg_lvl_rec.org_level_id
165 ,x_level_name => l_level_name
166 ,x_return_status => l_return_status
167 ,x_msg_count => x_msg_count
168 ,x_msg_data => x_msg_data);
169 BIS_debug_pub.add('The return status is ' || l_return_status || ': and'
170 || ' org level name is:' || l_level_name);
171 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
173 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
174 RAISE FND_API.G_EXC_ERROR;
175 END IF;
176 if substr(upper(l_level_name),1,5) <> 'TOTAL' then
177 l_level_col_list1 := l_level_col_list1
178 || ',' || replace(l_level_name,' ','_');
179 l_level_select_list1 := l_level_select_list1
180 || ',' || 'val.org_level_value';
181 end if;
182 end if;
183 -- Dimesion 1
184 if l_trg_lvl_rec.dimension1_level_id is not null then
185 BIS_debug_pub.add('Select Level Name For Dimension 1');
186 Get_Level_name
187 (p_level_id => l_trg_lvl_rec.dimension1_level_id
188 ,x_level_name => l_level_name
189 ,x_return_status => l_return_status
190 ,x_msg_count => x_msg_count
191 ,x_msg_data => x_msg_data);
192 BIS_debug_pub.add('The return status is ' || l_return_status || ': and'
193 || ' Dimension1 level name is:' || l_level_name);
194 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
197 RAISE FND_API.G_EXC_ERROR;
198 END IF;
199 if substr(upper(l_level_name),1,5) <> 'TOTAL' then
200 l_level_col_list1 := l_level_col_list1 || ','
201 || replace(l_level_name,' ','_');
202 l_level_select_list1 := l_level_select_list1 || ','
203 || 'val.dimension1_level_value';
204 end if;
205 end if;
206 -- Dimesion 2
207 if l_trg_lvl_rec.dimension2_level_id is not null then
208 BIS_debug_pub.add('Select Level Name For Dimension 2');
209 Get_Level_name
210 (p_level_id => l_trg_lvl_rec.dimension2_level_id
211 ,x_level_name => l_level_name
212 ,x_return_status => l_return_status
213 ,x_msg_count => x_msg_count
214 ,x_msg_data => x_msg_data);
215 BIS_debug_pub.add('The return status is ' || l_return_status || ': and'
216 || ' Dimension2 level name is:' || l_level_name);
217 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
219 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
220 RAISE FND_API.G_EXC_ERROR;
221 END IF;
222 if substr(upper(l_level_name),1,5) <> 'TOTAL' then
223 l_level_col_list1 := l_level_col_list1 || ','
224 || replace(l_level_name,' ','_');
225 l_level_select_list1 := l_level_select_list1 || ','
226 || 'val.dimension2_level_value';
227 end if;
228 end if;
229 -- Dimesion 3
230 if l_trg_lvl_rec.dimension3_level_id is not null then
231 BIS_debug_pub.add('Select Level Name For Dimension 3');
232 Get_Level_name
233 (p_level_id => l_trg_lvl_rec.dimension3_level_id
234 ,x_level_name => l_level_name
235 ,x_return_status => l_return_status
236 ,x_msg_count => x_msg_count
237 ,x_msg_data => x_msg_data);
238 BIS_debug_pub.add('The return status is ' || l_return_status || ': and'
239 || ' Dimension3 level name is:' || l_level_name);
240 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
241 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
242 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
243 RAISE FND_API.G_EXC_ERROR;
244 END IF;
245 if substr(upper(l_level_name),1,5) <> 'TOTAL' then
246 l_level_col_list1 := l_level_col_list1 || ','
247 || replace(l_level_name,' ','_');
248 l_level_select_list1 := l_level_select_list1 || ','
249 || 'val.dimension3_level_value';
250 end if;
251 end if;
252 -- Dimesion 4
253 if l_trg_lvl_rec.dimension4_level_id is not null then
254 BIS_debug_pub.add('Select Level Name For Dimension 4');
255 Get_Level_name
256 (p_level_id => l_trg_lvl_rec.dimension4_level_id
257 ,x_level_name => l_level_name
258 ,x_return_status => l_return_status
259 ,x_msg_count => x_msg_count
260 ,x_msg_data => x_msg_data);
261 BIS_debug_pub.add('The return status is ' || l_return_status || ': and'
262 || ' Dimension4 level name is:' || l_level_name);
263 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
265 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
266 RAISE FND_API.G_EXC_ERROR;
267 END IF;
268 if substr(upper(l_level_name),1,5) <> 'TOTAL' then
269 l_level_col_list1 := l_level_col_list1 || ','
270 || replace(l_level_name,' ','_');
271 l_level_select_list1 := l_level_select_list1 || ','
272 || 'val.dimension4_level_value';
273 end if;
274 end if;
275 -- Dimesion 5
276 if l_trg_lvl_rec.dimension5_level_id is not null then
277 BIS_debug_pub.add('Select Level Name For Dimension 5');
278 Get_Level_name
279 (p_level_id => l_trg_lvl_rec.dimension5_level_id
280 ,x_level_name => l_level_name
281 ,x_return_status => l_return_status
282 ,x_msg_count => x_msg_count
283 ,x_msg_data => x_msg_data);
284 BIS_debug_pub.add('The return status is ' || l_return_status || ': and'
285 || ' Dimension5 level name is:' || l_level_name);
286 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
288 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
289 RAISE FND_API.G_EXC_ERROR;
290 END IF;
291 if substr(upper(l_level_name),1,5) <> 'TOTAL' then
292 l_level_col_list1 := l_level_col_list1 || ','
293 || replace(l_level_name,' ','_');
294 l_level_select_list1 := l_level_select_list1
295 || ',' || 'val.dimension5_level_value';
296 end if;
297 end if;
298
299 -- Open cursor
300 l_cursor := dbms_sql.open_cursor;
301
302 -- parse the statment
303 BIS_debug_pub.add('sql statement: ' || ' create or replace force view bis_'
304 || substr(replace(p_ind_level_name,' ','_'),1,24)
305 || '_v(' );
306 BIS_debug_pub.add(l_level_col_list1 || l_level_col_list2);
307 BIS_debug_pub.add(l_level_col_list3|| ') as select ' );
308 BIS_debug_pub.add(l_level_select_list1 || l_level_select_list2|| l_level_select_list3);
309 BIS_debug_pub.add(l_view_from_stmt);
310 BIS_debug_pub.add(substr(l_view_from_stmt2,1,150));
311 BIS_debug_pub.add(substr(l_view_from_stmt2,151));
312
313 dbms_sql.parse(c => l_cursor
314 ,statement=> ' create or replace force view bis_'
315 || substr(replace(p_ind_level_name,' ','_'),1,24)
316 || '_v(' || l_level_col_list1
317 || l_level_col_list2
318 || l_level_col_list3 || ') as select '
319 || l_level_select_list1
320 || l_level_select_list2
321 || l_level_select_list3
322 || ' ' || l_view_from_stmt ||' '||l_view_from_stmt2||' '||l_view_from_stmt3
323 ,language_flag => DBMS_SQL.Native);
324
325 -- Execute the cursor
326 l_sql_result := dbms_sql.execute(l_cursor);
327 -- Close the cursor
328 dbms_sql.close_cursor(l_cursor);
329 -- check for sql execution result
330 BIS_debug_pub.add('The view creation sql execution result is :'
331 || to_char(l_sql_result));
332
333 if nvl(l_sql_result,-1) <> 0 then
334 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
335 THEN
336 FND_MSG_PUB.Add_Exc_Msg
337 ( G_PKG_NAME
338 , 'Create_Ind_Level_View'
339 );
340 END IF;
341 raise FND_API.G_EXC_UNEXPECTED_ERROR;
342 else
343
344 /* fnd_message.set_name('_','IND_LEVEL_VIEW_CREATED');
345 fnd_message.set_token('VIEW_NAME','BIS_'
346 || substr(replace(p_ind_level_name,' ','_'),1,24)|| '_v');
347 fnd_message.set_token('FOR_LEVEL',p_ind_level_name);
348 fnd_msg_pub.add;
349 */
350 null;
351 end if;
352 FND_MSG_PUB.Count_And_Get
353 ( p_count => x_msg_count
354 , p_data => x_msg_data
355 );
356 x_return_status := l_return_status;
357
358 EXCEPTION
359
360 WHEN FND_API.G_EXC_ERROR THEN
361
362 x_return_status := FND_API.G_RET_STS_ERROR;
363
364 -- Get message count and data
365
366 FND_MSG_PUB.Count_And_Get
367 ( p_count => x_msg_count
368 , p_data => x_msg_data
369 );
370
371 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
372
373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
374
375 -- Get message count and data
376
377 FND_MSG_PUB.Count_And_Get
378 ( p_count => x_msg_count
379 , p_data => x_msg_data
380 );
381
382 WHEN OTHERS THEN
383
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
385
386 FND_MSG_PUB.Add_Exc_Msg
387 ( G_PKG_NAME
388 , 'Create_Ind_Level_View'
389 );
390
391 -- Get message count and data
392
393 FND_MSG_PUB.Count_And_Get
394 ( p_count => x_msg_count
395 , p_data => x_msg_data
396 );
397
398 END Create_Ind_Level_View;
399
400 Procedure Create_Indicator_views
401 (p_indicator_name IN Varchar2
402 ,p_msg_init IN Varchar2 default FND_API.G_TRUE
403 ,x_return_status OUT Varchar2
404 ,x_msg_count OUT Number
405 ,x_msg_data OUT Varchar2)
406 is
407 Cursor C_levels(p_indicator_name Varchar2) is
408 select lvl.short_name
409 from BIS_target_levels lvl,
410 BIS_indicators ind
411 where lvl.indicator_id = ind.indicator_id
412 and ind.short_name = p_indicator_name;
413 l_return_status varchar2(10);
414 Begin
418
415 BIS_debug_pub.add('In Create Indicator Views');
416 BIS_debug_pub.add('Select Levels for Indicator:' || p_indicator_name);
417 l_return_status := FND_API.G_RET_STS_SUCCESS;
419 -- Initialize message list.
420 if p_msg_init = FND_API.G_TRUE then
421 FND_MSG_PUB.initialize;
422 end if;
423
424 for r1 in c_levels(p_indicator_name) loop
425 BIS_debug_pub.add('Calling Create Level force View for Level:'
426 || r1.short_name);
427 Create_Ind_Level_View
428 (p_ind_level_name => r1.short_name
429 ,p_msg_init => fnd_api.g_false
430 ,x_return_status=> l_return_status
431 ,x_msg_count => x_msg_count
432 ,x_msg_data => x_msg_data
433 );
434 end loop;
435 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
437 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
438 RAISE FND_API.G_EXC_ERROR;
439 END IF;
440 x_return_status := l_return_status;
441 EXCEPTION
442
443 WHEN FND_API.G_EXC_ERROR THEN
444
445 x_return_status := FND_API.G_RET_STS_ERROR;
446
447 -- Get message count and data
448
449 FND_MSG_PUB.Count_And_Get
450 ( p_count => x_msg_count
451 , p_data => x_msg_data
452 );
453
454 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
455
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
457
458 -- Get message count and data
459
460 FND_MSG_PUB.Count_And_Get
461 ( p_count => x_msg_count
462 , p_data => x_msg_data
463 );
464
465 WHEN OTHERS THEN
466
467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
468
469 FND_MSG_PUB.Add_Exc_Msg
470 ( G_PKG_NAME
471 , 'Create_Indicator_views'
472 );
473
474 -- Get message count and data
475
476 FND_MSG_PUB.Count_And_Get
477 ( p_count => x_msg_count
478 , p_data => x_msg_data
479 );
480
481 END Create_Indicator_views;
482
483 Procedure Create_BIS_views
484 (p_msg_init IN Varchar2 default FND_API.G_TRUE
485 ,x_return_status OUT Varchar2
486 ,x_msg_count OUT Number
487 ,x_msg_data OUT Varchar2)
488 is
489 Cursor C_indicator is
490 select short_name
491 from BIS_indicators ;
492 l_return_status varchar2(10);
493 Begin
494 BIS_debug_pub.add('In Create BIS Views');
495 l_return_status := FND_API.G_RET_STS_SUCCESS;
496
497 -- Initialize message list.
498 if p_msg_init = FND_API.G_TRUE then
499 FND_MSG_PUB.initialize;
500 end if;
501
502 for r1 in c_indicator loop
503 BIS_debug_pub.add('Calling Create BIS force View for Indicator:'
504 || r1.short_name);
505 Create_Indicator_views
506 (p_indicator_name => r1.short_name
507 ,p_msg_init => fnd_api.g_false
508 ,x_return_status=> l_return_status
509 ,x_msg_count => x_msg_count
510 ,x_msg_data => x_msg_data
511 );
512 end loop;
513 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
515 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
516 RAISE FND_API.G_EXC_ERROR;
517 END IF;
518 x_return_status := l_return_status;
519 EXCEPTION
520
521 WHEN FND_API.G_EXC_ERROR THEN
522
523 x_return_status := FND_API.G_RET_STS_ERROR;
524
525 -- Get message count and data
526
527 FND_MSG_PUB.Count_And_Get
528 ( p_count => x_msg_count
529 , p_data => x_msg_data
530 );
531
532 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
533
534 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
535
536 -- Get message count and data
537
538 FND_MSG_PUB.Count_And_Get
539 ( p_count => x_msg_count
540 , p_data => x_msg_data
541 );
542
543 WHEN OTHERS THEN
544
545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
546
547 FND_MSG_PUB.Add_Exc_Msg
548 ( G_PKG_NAME
549 , 'Create_BIS_views'
550 );
551
552 -- Get message count and data
553
554 FND_MSG_PUB.Count_And_Get
555 ( p_count => x_msg_count
556 , p_data => x_msg_data
557 );
558
559 END Create_BIS_views;
560
561 Procedure Drop_Ind_Level_view
562 (p_target_level_id IN Number
563 ,x_return_status OUT Varchar2
564 ,x_msg_count OUT Number
565 ,x_msg_data OUT Varchar2)
566 IS
567 l_view_name Varchar2(30);
568 l_cursor Integer;
569 l_sql_result number;
570 l_rtn_val Varchar2(80);
571 begin
572 x_return_status := FND_API.G_RET_STS_SUCCESS;
573
574 select short_name into l_view_name from bis_target_levels
575 where target_level_id = p_target_level_id;
576 -- open cursor
577 l_cursor := dbms_sql.open_cursor;
578
579 -- parse statement
580 dbms_sql.parse(c => l_cursor
581 ,statement => 'drop view BIS_'|| l_view_name ||'_V'
582 ,language_flag => DBMS_SQL.Native);
583
584 -- execuse cursor
588 dbms_sql.close_cursor(l_cursor);
585 l_sql_result := dbms_sql.execute(l_cursor);
586
587 -- close cursor
589 if nvl(l_sql_result,-1) <> 0 then
590 NULL;
591 -- dbms_output.put_line('Error');
592 else
593 NULL;
594 -- dbms_output.put_line('View dropped');
595 end if;
596 EXCEPTION
597
598 WHEN OTHERS THEN
599
600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
601
602 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
603 THEN
604 FND_MSG_PUB.Add_Exc_Msg
605 (G_PKG_NAME
606 ,'Drop_Ind_Level_View');
607 END IF;
608
609 -- get message count and data
610 FND_MSG_PUB.Count_And_Get
611 (p_count => x_msg_count
612 ,p_data => x_msg_data
613 );
614
615 END Drop_Ind_Level_view;
616
617
618 Procedure Get_Dimension_Display_Value
619 (p_dim_level_id IN number
620 ,p_dim_level_value_id IN Varchar2
621 ,x_dim_level_value_name OUT Varchar2
622 ,x_return_status OUT Varchar2
623 ,x_msg_count OUT Number
624 ,x_msg_data OUT Varchar2)
625 IS
626 l_view_name Varchar2(80);
627 v_value Varchar2(80);
628 l_cursor Integer;
629 l_value_id Varchar2(40);
630 l_select_stmt Varchar2(2000);
631 l_sql_result Integer := 0;
632 begin
633 x_return_status := FND_API.G_RET_STS_SUCCESS;
634
635 select level_values_view_name into l_view_name
636 from bis_levels where level_id = p_dim_level_id;
637
638 l_cursor := dbms_sql.open_cursor;
639
640 l_select_stmt := 'select value from '|| l_view_name
641 ||' where id = :dim_level_value_id';
642
643 dbms_sql.parse(c => l_cursor,
644 statement => l_select_stmt,
645 language_flag => DBMS_SQL.Native);
646
647 dbms_sql.bind_variable(l_cursor, ':dim_level_value_id',p_dim_level_value_id);
648 dbms_sql.define_column(l_cursor,1,v_value,80);
649 l_sql_result := dbms_sql.execute_and_fetch(l_cursor,TRUE);
650 dbms_sql.column_value(l_cursor,1,v_value);
651
652 x_dim_level_value_name := v_value;
653
654 dbms_sql.close_cursor(l_cursor);
655
656 EXCEPTION
657
658 WHEN OTHERS THEN
659
660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
661
662 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
663 THEN
664 FND_MSG_PUB.Add_Exc_Msg
665 (G_PKG_NAME
666 ,'Get_Dimension_Display_Value');
667 END IF;
668
669 -- get message count and data
670 FND_MSG_PUB.Count_And_Get
671 (p_count => x_msg_count
672 ,p_data => x_msg_data
673 );
674 end Get_Dimension_Display_Value;
675
676 Procedure Validate_Resp_Org
677 (p_dim_level_id IN number
678 ,p_responsibility_id IN number
679 ,p_organization_id IN number
680 ,p_user_id IN number
681 ,x_return_status OUT Varchar2
682 ,x_msg_count OUT Number
683 ,x_msg_data OUT Varchar2)
684 IS
685 l_view_name Varchar2(80);
686 l_resp_id number;
687 l_org_id number;
688 l_user_id number;
689 l_cursor Integer;
690 l_value_id Varchar2(40);
691 l_select_stmt Varchar2(2000);
692 l_sql_result Integer := 0;
693 begin
694
695 x_return_status := FND_API.G_RET_STS_SUCCESS;
696
697 select level_values_view_name into l_view_name
698 from bis_levels where level_id = p_dim_level_id;
699 --dbms_output.put_line('view name: '||l_view_name);
700
701 l_cursor := DBMS_SQL.OPEN_CURSOR;
702
703 l_select_stmt := 'select v.responsibility_id, v.id, f.user_id '
704 ||' from '||l_view_name||' v, fnd_user_responsibility f '
705 ||' where v.responsibility_id = :responsibility_id '
706 ||' and v.id = :organization_id '
707 ||' and f.user_id = :user_id '
708 ||' and v.responsibility_id = f.responsibility_id '
709 ||' and f.start_date <= SYSDATE and nvl(f.end_date,SYSDATE) >= SYSDATE ';
710
711 DBMS_SQL.PARSE(c => l_cursor,
712 statement => l_select_stmt,
713 language_flag => DBMS_SQL.NATIVE);
714
715 DBMS_SQL.BIND_VARIABLE(l_cursor, ':responsibility_id',p_responsibility_id);
716 DBMS_SQL.BIND_VARIABLE(l_cursor, ':organization_id',p_organization_id);
717 DBMS_SQL.BIND_VARIABLE(l_cursor, ':user_id',p_user_id);
718
719 DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_resp_id);
720 DBMS_SQL.DEFINE_COLUMN(l_cursor, 2, l_org_id);
721 DBMS_SQL.DEFINE_COLUMN(l_cursor, 3, l_user_id);
722
723 l_sql_result := DBMS_SQL.EXECUTE(l_cursor);
724
725 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_resp_id);
726 DBMS_SQL.COLUMN_VALUE(l_cursor, 2, l_org_id);
727 DBMS_SQL.COLUMN_VALUE(l_cursor, 3, l_user_id);
728
729 if DBMS_SQL.FETCH_ROWS(l_cursor) = 0 then
730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
731 else
732 --dbms_output.put_line('resp id : '||to_char(l_resp_id));
733 --dbms_output.put_line('org id : '||to_char(l_org_id));
734 --dbms_output.put_line('user id : '||to_char(l_user_id));
735
736
737 DBMS_SQL.CLOSE_CURSOR(l_cursor);
738
739 end if;
740
741 EXCEPTION
742
743 WHEN OTHERS THEN
744
745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
746
747 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
748 THEN
749 FND_MSG_PUB.Add_Exc_Msg
750 (G_PKG_NAME
751 ,'Validate_Resp_Org');
752 END IF;
753
754 -- get message count and data
755 FND_MSG_PUB.Count_And_Get
756 (p_count => x_msg_count
757 ,p_data => x_msg_data
758 );
759
760 end Validate_Resp_Org;
761
762 END BIS_TARGET_UTIL;