[Home] [Help]
PACKAGE BODY: APPS.BIS_UTILITIES_PVT
Source
1 PACKAGE BODY BIS_UTILITIES_PVT AS
2 /* $Header: BISVUTLB.pls 120.1 2005/12/28 06:06:51 ashankar noship $ */
3 --
4 G_IMG_SRC_DIR CONSTANT VARCHAR2(100) := '/OA_MEDIA/';
5
6
7 G_PXC_333333_IMG_SRC CONSTANT VARCHAR2(32000) := G_IMG_SRC_DIR||'BISPX333.gif';
8 G_PXC_666666_IMG_SRC CONSTANT VARCHAR2(32000) := G_IMG_SRC_DIR||'BISPX666.gif';
9 G_PXC_6699CC_IMG_SRC CONSTANT VARCHAR2(32000) := G_IMG_SRC_DIR||'BISPX69C.gif';
10 G_PXC_999999_IMG_SRC CONSTANT VARCHAR2(32000) := G_IMG_SRC_DIR||'BISPX999.gif';
11 G_PXC_CCCCCC_IMG_SRC CONSTANT VARCHAR2(32000) := G_IMG_SRC_DIR||'BISPXCCC.gif';
12 G_PXC_FFFFFF_IMG_SRC CONSTANT VARCHAR2(32000) := G_IMG_SRC_DIR||'BISPXFFF.gif';
13
14 --R12 specific
15 G_PXC_B3B7BA_IMG_SRC CONSTANT VARCHAR2(32000) := G_IMG_SRC_DIR||'BISPXB7B.gif';
16 G_PXC_AEDBEA_IMG_SRC CONSTANT VARCHAR2(32000) := G_IMG_SRC_DIR||'BISPXAED.gif';
17
18
19 G_GREY_IMAGE_SRC CONSTANT VARCHAR2(32000) :=
20 G_IMG_SRC_DIR||'BISPXCCC.gif';
21 G_LEFT_FLAT_EDGE_IMG_SRC CONSTANT VARCHAR2(32000) :=
22 G_IMG_SRC_DIR||'BISBSQRL.gif';
23 G_LEFT_ROUND_EDGE_IMG_SRC CONSTANT VARCHAR2(32000) :=
24 G_IMG_SRC_DIR||'BISBRNDL.gif';
25 G_RIGHT_FLAT_EDGE_IMG_SRC CONSTANT VARCHAR2(32000) :=
26 G_IMG_SRC_DIR||'BISBSQRR.gif';
27 G_RIGHT_ROUND_EDGE_IMG_SRC CONSTANT VARCHAR2(32000) :=
28 G_IMG_SRC_DIR||'BISBRNDR.gif';
29
30 --R12 specific
31 G_R12_LEFT_FLAT_EDGE_IMG_SRC CONSTANT VARCHAR2(32000) :=
32 G_IMG_SRC_DIR||'BISFLATL.gif';
33
34 G_R12_RIGHT_FLAT_EDGE_IMG_SRC CONSTANT VARCHAR2(32000) :=
35 G_IMG_SRC_DIR||'BISFLATR.gif';
36
37
38
39 G_TOP_OUTER_EDGE_IMG_SRC CONSTANT VARCHAR2(32000) := G_PXC_333333_IMG_SRC;
40
41 G_TOP_INNER_EDGE_IMG_SRC CONSTANT VARCHAR2(32000) := G_PXC_FFFFFF_IMG_SRC;
42
43 G_BOT_OUTER_EDGE_IMG_SRC CONSTANT VARCHAR2(32000) := G_PXC_333333_IMG_SRC;
44
45 G_BOT_INNER_EDGE_IMG_SRC CONSTANT VARCHAR2(32000) := G_PXC_666666_IMG_SRC;
46
47 --R12 specific
48
49 G_R12_TOP_OUTER_EDGE_IMG_SRC CONSTANT VARCHAR2(32000) := G_PXC_B3B7BA_IMG_SRC;
50 G_R12_BOT_OUTER_EDGE_IMG_SRC CONSTANT VARCHAR2(32000) := G_PXC_B3B7BA_IMG_SRC;
51 G_R12_BOT_INNER_EDGE_IMG_SRC CONSTANT VARCHAR2(32000) := G_PXC_AEDBEA_IMG_SRC;
52
53
54 G_TOP_LEFT_CURVE_IMG_SRC CONSTANT VARCHAR2(32000) :=
55 G_IMG_SRC_DIR||'BISTCLTL.gif';
56 G_TOP_RIGHT_CURVE_IMG_SRC CONSTANT VARCHAR2(32000) :=
57 G_IMG_SRC_DIR||'BISTCLTR.gif';
58 G_BOT_LEFT_CURVE_IMG_SRC CONSTANT VARCHAR2(32000) :=
59 G_IMG_SRC_DIR||'BISTCLBL.gif';
60 G_BOT_RIGHT_CURVE_IMG_SRC CONSTANT VARCHAR2(32000) :=
61 G_IMG_SRC_DIR||'BISTCLBR.gif';
62 --- NOTE: For he next four files, the BIS file has a white background
63 --- and the FND CURVE HAS TRANSPARENT.
64 G_TOP_LEFT_BLUE_CURVE_IMG_SRC CONSTANT VARCHAR2(32000) :=
65 --- G_IMG_SRC_DIR||'FNDRTWTL.gif';
66 G_IMG_SRC_DIR||'BISTCBTL.gif';
67 G_TOP_RIGHT_BLUE_CURVE_IMG_SRC CONSTANT VARCHAR2(32000) :=
68 --- G_IMG_SRC_DIR||'FNDRTWTR.gif';
69 G_IMG_SRC_DIR||'BISTCBTR.gif';
70 G_BOT_LEFT_BLUE_IMG_SRC CONSTANT VARCHAR2(32000) :=
71 --- G_IMG_SRC_DIR||'BISTCBBL.gif';
72 G_IMG_SRC_DIR||'FNDRTWBL.gif';
73 G_BOT_RIGHT_BLUE_IMG_SRC CONSTANT VARCHAR2(32000) :=
74 --- G_IMG_SRC_DIR||'BISTCBBR.gif';
75 G_IMG_SRC_DIR||'FNDRTWBR.gif';
76
77 G_BOT_LEFT_GREY_CURVE_IMG_SRC CONSTANT VARCHAR2(32000) :=
78 G_IMG_SRC_DIR||'BISTCGBL.gif';
79 G_BOT_RIGHT_GREY_CURVE_IMG_SRC CONSTANT VARCHAR2(32000) :=
80 G_IMG_SRC_DIR||'BISTCGBR.gif';
81
82 G_PXC_333333 CONSTANT VARCHAR2(1000) := '#333333';
83 G_PXC_666666 CONSTANT VARCHAR2(1000) := '#666666';
84 G_PXC_CCCCCC CONSTANT VARCHAR2(1000) := '#CCCCCC';
85 G_PXC_FFFFFF CONSTANT VARCHAR2(1000) := '#FFFFFF';
86 G_PXC_6699CC CONSTANT VARCHAR2(1000) := '#6699CC';
87 G_PXC_999999 CONSTANT VARCHAR2(1000) := '#999999';
88
89 G_TOP_OUTER_EDGE_COLOR CONSTANT VARCHAR2(1000) := G_PXC_333333;
90 G_TOP_INNER_EDGE_COLOR CONSTANT VARCHAR2(1000) := G_PXC_FFFFFF;
91 G_BOT_OUTER_EDGE_COLOR CONSTANT VARCHAR2(1000) := G_PXC_333333;
92 G_BOT_INNER_EDGE_COLOR CONSTANT VARCHAR2(1000) := G_PXC_666666;
93 G_BUTTON_BG_COLOR CONSTANT VARCHAR2(1000) := G_PXC_CCCCCC;
94
95
96 --
97 G_FF_SPACER_THICKNESS CONSTANT NUMBER := 3;
98 G_STD_SPACER_THICKNESS CONSTANT NUMBER := 10;
99 --G_BUTTON_HEIGHT CONSTANT NUMBER := 17;
100 --
101 G_GROUP_BOX_TABLE_WIDTH_PRCNT CONSTANT NUMBER := 100;
102
103 TYPE color_tbl_type is
104 table of varchar2(32000) index by BINARY_INTEGER;
105
106 TYPE imgsrc_tbl_type is
107 table of varchar2(32000) index by BINARY_INTEGER;
108
109 TYPE Target_level_Rec_Type IS RECORD
110 ( Target_Level_ID NUMBER
111 );
112 --
113 TYPE Target_level_Tbl_Type IS TABLE of Target_level_Rec_Type
114 INDEX BY BINARY_INTEGER;
115 --
116 --
117 TYPE Perf_Measure_Rec_Type IS RECORD
118 ( Measure_ID NUMBER
119 );
120 --
121 TYPE Perf_Measure_Tbl_Type IS TABLE of Perf_Measure_Rec_Type
122 INDEX BY BINARY_INTEGER;
123 --
124
125 -- Functions/ Procedures
126 Procedure Get_Time_Level_Value -- where p_date is between start and end dates.
127 ( p_source IN varchar2,
128 p_table_name IN varchar2,
129 p_value_col_name IN varchar2,
130 p_Org_Level_ID IN varchar2,
131 p_org_level_short_name IN varchar2,
132 p_flag IN varchar2,
133 p_date IN date,
134 x_time_value OUT NOCOPY varchar2
135 );
136 --
137 Procedure Get_Min_Max_Start_End_Dates -- get min start and max end date for a given
138 ( p_source IN varchar2, -- time level value.
139 p_view_name IN varchar2,
140 p_org_level_id IN varchar2,
141 p_org_level_short_name IN varchar2,
142 x_min_start_date OUT NOCOPY date,
143 x_max_end_date OUT NOCOPY date
144 );
145 --
146 FUNCTION escape_html(
147 p_input IN VARCHAR2
148 ,p_cr IN VARCHAR2
149 )
150 RETURN VARCHAR2;
151 --
152
153 FUNCTION getPrompt
154 ( p_region_code in varchar2
155 , p_attribute_code in varchar2
156 ) return varchar2
157 is
158
159 l_str varchar2(32000);
160 begin
161 --
162 l_str := icx_util.getPrompt( BIS_UTILITIES_PVT.G_BIS_APPLICATION_ID
163 , p_region_code
164 , BIS_UTILITIES_PVT.G_BIS_APPLICATION_ID
165 , p_attribute_code
166 );
167
168 if (l_str is null or Length(l_str)=0) then
169 l_str := p_attribute_code;
170 end if;
171
172 return l_str;
173 --
174 end getPrompt;
175
176 FUNCTION
177 getPrompt( p_attribute_code varchar2)
178 return varchar2
179 is
180 l_str varchar2(32000);
181 begin
182 --
183 l_str := getPrompt(G_BIS_REGION_CODE,p_attribute_code);
184
185 return l_str;
186 --
187 end getPrompt;
188 --
189 PROCEDURE PutHtmlNumberTextField
190 ( p_field_name varchar2
191 , p_number number
192 )
193 is
194 begin
195 --
196 if (p_number = FND_API.G_MISS_NUM) then
197 htp.formText(p_field_name);
198 else
199 htp.formText( cname => p_field_name
200 , cvalue=>p_number
201 );
202 end if;
203 --
204 end PutHtmlNumberTextField;
205 --
206 PROCEDURE PutHtmlNumberOptionField
207 ( p_number number
208 , p_selected varchar2
209 , p_value varchar2
210 )
211 is
212 begin
213 --
214 if (p_number <> FND_API.G_MISS_NUM) then
215 if (p_value is null) then
216 htp.formSelectOption(p_number);
217 else
218 htp.formSelectOption(p_number, p_selected, 'VALUE="'||p_value||'"');
219 end if;
220 end if;
221 --
222 end PutHtmlNumberOptionField;
223 --
224 PROCEDURE PutHtmlNumberHiddenField
225 ( p_field_name varchar2
226 , p_number number
227 )
228 is
229 begin
230 --
231 if (p_number = FND_API.G_MISS_NUM) then
232 htp.formHidden(p_field_name);
233 else
234 htp.formHidden( cname => p_field_name
235 , cvalue=> p_number
236 );
237 end if;
238 --
239 end PutHtmlNumberHiddenField;
240 --
241 PROCEDURE PutHtmlVarcharTextField
242 ( p_field_name varchar2
243 , p_varchar varchar2
244 )
245 is
246 begin
247 --
248 if (p_varchar = FND_API.G_MISS_NUM) then
249 htp.formText(p_field_name);
250 else
251 htp.formText( cname => p_field_name
252 , cvalue=>p_varchar
253 );
254 end if;
255 --
256 end PutHtmlVarcharTextField;
257 --
258 PROCEDURE PutHtmlVarcharOptionField
259 ( p_varchar varchar2
260 , p_selected varchar2 := NULL
261 , p_value varchar2 := NULL
262 )
263 is
264 begin
265 --
266 if (p_varchar <> BIS_UTILITIES_PUB.G_NULL_CHAR) then
267 if (p_value is null) then
268 htp.formSelectOption(p_varchar);
269 else
270 htp.formSelectOption(p_varchar, p_selected, 'VALUE="'||p_value||'"');
271 end if;
272 end if;
273 --
274 end PutHtmlVarcharOptionField;
275 --
276 PROCEDURE PutHtmlVarcharHiddenField
277 ( p_field_name varchar2
278 , p_varchar varchar2
279 )
280 is
281 begin
282 --
283 if (p_varchar = FND_API.G_MISS_CHAR) then
284 htp.formHidden(p_field_name);
285 else
286 htp.formHidden( cname => p_field_name
287 , cvalue=> p_varchar
288 );
289 end if;
290 --
291 end PutHtmlVarcharHiddenField;
292 --
293 --
294 -- function to get message from FND_MESSAGES
295 FUNCTION Get_FND_Message
296 ( p_message_name IN VARCHAR2
297 )
298 RETURN VARCHAR2
299 IS
300 BEGIN
301 FND_MESSAGE.set_name( BIS_UTILITIES_PVT.G_BIS_APPLICATION_SHORT_NAME
302 , p_message_name
303 );
304 RETURN FND_MESSAGE.get;
305 END Get_FND_Message;
306 --
307 --
308 FUNCTION Get_FND_Message
309 ( p_message_name IN VARCHAR2
310 , p_msg_param1 IN VARCHAR2
311 , p_msg_param1_val IN VARCHAR2
312 )
313 RETURN VARCHAR2
314 IS
315 BEGIN
316 FND_MESSAGE.set_name( BIS_UTILITIES_PVT.G_BIS_APPLICATION_SHORT_NAME
317 , p_message_name
318 );
319 FND_MESSAGE.set_token(p_msg_param1, p_msg_param1_val);
320 RETURN FND_MESSAGE.get;
321 END Get_FND_Message;
322 --
323 --
324 FUNCTION Get_FND_Message
325 ( p_message_name IN VARCHAR2
326 , p_msg_param1 IN VARCHAR2
327 , p_msg_param1_val IN VARCHAR2
328 , p_msg_param2 IN VARCHAR2
329 , p_msg_param2_val IN VARCHAR2
330 )
331 RETURN VARCHAR2
332 IS
333 BEGIN
334 FND_MESSAGE.set_name( BIS_UTILITIES_PVT.G_BIS_APPLICATION_SHORT_NAME
335 , p_message_name
336 );
337 FND_MESSAGE.set_token(p_msg_param1, p_msg_param1_val);
338 FND_MESSAGE.set_token(p_msg_param2, p_msg_param2_val);
339 RETURN FND_MESSAGE.get;
340 END Get_FND_Message;
341 --
342 --
343 FUNCTION Get_FND_Message
344 ( p_message_name IN VARCHAR2
345 , p_msg_param1 IN VARCHAR2
346 , p_msg_param1_val IN VARCHAR2
347 , p_msg_param2 IN VARCHAR2
348 , p_msg_param2_val IN VARCHAR2
349 , p_msg_param3 IN VARCHAR2
350 , p_msg_param3_val IN VARCHAR2
351 )
352 RETURN VARCHAR2
353 IS
354 BEGIN
355 FND_MESSAGE.set_name( BIS_UTILITIES_PVT.G_BIS_APPLICATION_SHORT_NAME
356 , p_message_name
357 );
358 FND_MESSAGE.set_token(p_msg_param1, p_msg_param1_val);
359 FND_MESSAGE.set_token(p_msg_param2, p_msg_param2_val);
360 FND_MESSAGE.set_token(p_msg_param3, p_msg_param3_val);
361 RETURN FND_MESSAGE.get;
362 END Get_FND_Message;
363 --
364 --
365 -- these procedure check and puts the error message on the message stack
366 --
367 PROCEDURE Add_Error_Message
368 ( p_error_msg_name IN VARCHAR2
369 , p_error_msg_level IN NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR
370 , p_error_proc_name IN VARCHAR2 := NULL
371 , p_error_type IN VARCHAR2 := BIS_UTILITIES_PUB.G_ERROR
372 , p_error_table IN BIS_UTILITIES_PUB.Error_Tbl_Type
373 , x_error_table OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
374 )
375 IS
376 --
377 l_error_rec BIS_UTILITIES_PUB.Error_Rec_Type;
378 --
379 BEGIN
380 IF (fnd_msg_pub.check_msg_level(p_error_msg_level) = TRUE) THEN
381 fnd_message.set_name(BIS_UTILITIES_PVT.G_BIS_APPLICATION_SHORT_NAME, p_error_msg_name);
382
383 l_error_rec.Error_Msg_ID := NULL;
384 l_error_rec.Error_Msg_Name := p_error_msg_name;
385 l_error_rec.Error_proc_Name := p_error_proc_name;
386 l_error_rec.Error_Description := fnd_message.get;
387 l_error_rec.Error_Type := p_error_type;
388 --
389 x_error_table := p_error_table;
390 x_error_table(x_error_table.COUNT + 1) := l_error_rec;
391
392 END IF;
393 END Add_Error_Message;
394
395 PROCEDURE Add_Error_Message
396 ( p_error_msg_name IN VARCHAR2
397 , p_error_msg_level IN NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR
398 , p_error_proc_name IN VARCHAR2 := NULL
399 , p_error_type IN VARCHAR2 := BIS_UTILITIES_PUB.G_ERROR
400 , p_token1 IN VARCHAR2
401 , p_value1 IN VARCHAR2
402 , p_error_table IN BIS_UTILITIES_PUB.Error_Tbl_Type
403 , x_error_table OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
404 )
405 IS
406 --
407 l_error_rec BIS_UTILITIES_PUB.Error_Rec_Type;
408 --
409 BEGIN
410
411 IF (fnd_msg_pub.check_msg_level(p_error_msg_level) = TRUE) THEN
412 fnd_message.set_name(BIS_UTILITIES_PVT.G_BIS_APPLICATION_SHORT_NAME, p_error_msg_name);
413 fnd_message.set_token(p_token1, p_value1);
414
415 -- mdamle 08/06/2003 - Added token and values
416 l_error_rec.Error_Token1 := p_token1;
417 l_error_rec.Error_Value1 := p_value1;
418
419 l_error_rec.Error_Msg_ID := NULL;
420 l_error_rec.Error_Msg_Name := p_error_msg_name;
421 l_error_rec.Error_proc_Name := p_error_proc_name;
422 l_error_rec.Error_Description := fnd_message.get;
423 l_error_rec.Error_Type := p_error_type;
424 --
425 x_error_table := p_error_table;
426 x_error_table(x_error_table.COUNT + 1) := l_error_rec;
427
428 END IF;
429
430 END Add_Error_Message;
431
432 PROCEDURE Add_Error_Message
433 ( p_error_msg_name IN VARCHAR2
434 , p_error_msg_level IN NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR
435 , p_error_proc_name IN VARCHAR2 := NULL
436 , p_error_type IN VARCHAR2 := BIS_UTILITIES_PUB.G_ERROR
437 , p_token1 IN VARCHAR2
438 , p_value1 IN VARCHAR2
439 , p_token2 IN VARCHAR2
440 , p_value2 IN VARCHAR2
441 , p_error_table IN BIS_UTILITIES_PUB.Error_Tbl_Type
442 , x_error_table OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
443 )
444 IS
445 --
446 l_error_rec BIS_UTILITIES_PUB.Error_Rec_Type;
447 --
448 BEGIN
449
450 IF (fnd_msg_pub.check_msg_level(p_error_msg_level) = TRUE) THEN
451 fnd_message.set_name(BIS_UTILITIES_PVT.G_BIS_APPLICATION_SHORT_NAME, p_error_msg_name);
452 fnd_message.set_token(p_token1, p_value1);
453 fnd_message.set_token(p_token2, p_value2);
454
455 -- mdamle 08/06/2003 - Added token and values
456 l_error_rec.Error_Token1 := p_token1;
457 l_error_rec.Error_Value1 := p_value1;
458 l_error_rec.Error_Token2 := p_token2;
459 l_error_rec.Error_Value2 := p_value2;
460
461 l_error_rec.Error_Msg_ID := NULL;
462 l_error_rec.Error_Msg_Name := p_error_msg_name;
463 l_error_rec.Error_proc_Name := p_error_proc_name;
464 l_error_rec.Error_Description := fnd_message.get;
465 l_error_rec.Error_Type := p_error_type;
466 --
467 x_error_table := p_error_table;
468 x_error_table(x_error_table.COUNT + 1) := l_error_rec;
469
470 END IF;
471 END Add_Error_Message;
472
473 PROCEDURE Add_Error_Message
474 ( p_error_msg_name IN VARCHAR2
475 , p_error_msg_level IN NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR
476 , p_error_proc_name IN VARCHAR2 := NULL
477 , p_error_type IN VARCHAR2 := BIS_UTILITIES_PUB.G_ERROR
478 , p_token1 IN VARCHAR2
479 , p_value1 IN VARCHAR2
480 , p_token2 IN VARCHAR2
481 , p_value2 IN VARCHAR2
482 , p_token3 IN VARCHAR2
483 , p_value3 IN VARCHAR2
484 , p_error_table IN BIS_UTILITIES_PUB.Error_Tbl_Type
485 , x_error_table OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
486 )
487 IS
488 --
489 l_error_rec BIS_UTILITIES_PUB.Error_Rec_Type;
490 --
491 BEGIN
492
493 IF (fnd_msg_pub.check_msg_level(p_error_msg_level) = TRUE) THEN
494 fnd_message.set_name(BIS_UTILITIES_PVT.G_BIS_APPLICATION_SHORT_NAME, p_error_msg_name);
495 fnd_message.set_token(p_token1, p_value1);
496 fnd_message.set_token(p_token2, p_value2);
497 fnd_message.set_token(p_token3, p_value3);
498
499 -- mdamle 08/06/2003 - Added token and values
500 l_error_rec.Error_Token1 := p_token1;
501 l_error_rec.Error_Value1 := p_value1;
502 l_error_rec.Error_Token2 := p_token2;
503 l_error_rec.Error_Value2 := p_value2;
504 l_error_rec.Error_Token3 := p_token3;
505 l_error_rec.Error_Value3 := p_value3;
506
507 l_error_rec.Error_Msg_ID := NULL;
508 l_error_rec.Error_Msg_Name := p_error_msg_name;
509 l_error_rec.Error_proc_Name := p_error_proc_name;
510 l_error_rec.Error_Description := fnd_message.get;
511 l_error_rec.Error_Type := p_error_type;
512 --
513 x_error_table := p_error_table;
514 x_error_table(x_error_table.COUNT + 1) := l_error_rec;
515
516 END IF;
517 END Add_Error_Message;
518
519 PROCEDURE Add_Error_Message
520 ( p_error_msg_id IN NUMBER := NULL
521 , p_error_description IN VARCHAR2 := NULL
522 , p_error_proc_name IN VARCHAR2 := NULL
523 , p_error_type IN VARCHAR2 := BIS_UTILITIES_PUB.G_ERROR
524 , p_error_table IN BIS_UTILITIES_PUB.Error_Tbl_Type
525 , x_error_table OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
526 )
527 IS
528 --
529 l_error_rec BIS_UTILITIES_PUB.Error_Rec_Type;
530 --
531 BEGIN
532
533 l_error_rec.Error_Msg_ID := p_error_msg_id;
534 l_error_rec.Error_Msg_Name := NULL;
535 l_error_rec.Error_proc_Name := p_error_proc_name;
536 l_error_rec.Error_Description := p_error_description;
537 l_error_rec.Error_Type := p_error_type;
538 --
539 x_error_table := p_error_table;
540 x_error_table(x_error_table.COUNT + 1) := l_error_rec;
541 END Add_Error_Message;
542 --
543 PROCEDURE Add_Error_Message
544 ( p_error_msg_name IN VARCHAR2
545 , p_error_msg_level IN NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR
546 , p_error_proc_name IN VARCHAR2 := NULL
547 , p_error_type IN VARCHAR2 := BIS_UTILITIES_PUB.G_ERROR
548 )
549 IS
550 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
551 l_error_tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
552 l_return_status VARCHAR2(32000);
553 BEGIN
554 BIS_UTILITIES_PVT.Add_Error_Message
555 ( p_error_msg_name
556 , p_error_msg_level
557 , p_error_proc_name
558 , p_error_type
559 , l_error_tbl_p
560 , l_error_tbl
561 );
562 BIS_ERROR_MESSAGE_PVT.Update_Error_Log( l_error_tbl(1)
563 , l_return_status
564 , l_error_tbl
565 );
566 END Add_error_Message;
567
568 PROCEDURE Add_Error_Message
569 ( p_error_msg_name IN VARCHAR2
570 , p_error_msg_level IN NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR
571 , p_error_proc_name IN VARCHAR2 := NULL
572 , p_error_type IN VARCHAR2 := BIS_UTILITIES_PUB.G_ERROR
573 , p_token1 IN VARCHAR2
574 , p_value1 IN VARCHAR2
575 )
576 IS
577 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
578 l_error_tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
579 l_return_status VARCHAR2(32000);
580 BEGIN
581 BIS_UTILITIES_PVT.Add_Error_Message
582 ( p_error_msg_name
583 , p_error_msg_level
584 , p_error_proc_name
585 , p_error_type
586 , p_token1
587 , p_value1
588 , l_error_tbl_p
589 , l_error_tbl
590 );
591 BIS_ERROR_MESSAGE_PVT.Update_Error_Log( l_error_tbl(1)
592 , l_return_status
593 , l_error_tbl
594 );
595 END Add_error_Message;
596
597 PROCEDURE Add_Error_Message
598 ( p_error_msg_name IN VARCHAR2
599 , p_error_msg_level IN NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR
600 , p_error_proc_name IN VARCHAR2 := NULL
601 , p_error_type IN VARCHAR2 := BIS_UTILITIES_PUB.G_ERROR
602 , p_token1 IN VARCHAR2
603 , p_value1 IN VARCHAR2
604 , p_token2 IN VARCHAR2
605 , p_value2 IN VARCHAR2
606 )
607 IS
608 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
609 l_error_tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
610 l_return_status VARCHAR2(32000);
611 BEGIN
612 BIS_UTILITIES_PVT.Add_Error_Message
613 ( p_error_msg_name
614 , p_error_msg_level
615 , p_error_proc_name
616 , p_error_type
617 , p_token1
618 , p_value1
619 , p_token2
620 , p_value2
621 , l_error_tbl_p
622 , l_error_tbl
623 );
624 BIS_ERROR_MESSAGE_PVT.Update_Error_Log( l_error_tbl(1)
625 , l_return_status
626 , l_error_tbl
627 );
628 END Add_error_Message;
629
630 PROCEDURE Add_Error_Message
631 ( p_error_msg_name IN VARCHAR2
632 , p_error_msg_level IN NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR
633 , p_error_proc_name IN VARCHAR2 := NULL
634 , p_error_type IN VARCHAR2 := BIS_UTILITIES_PUB.G_ERROR
635 , p_token1 IN VARCHAR2
636 , p_value1 IN VARCHAR2
637 , p_token2 IN VARCHAR2
638 , p_value2 IN VARCHAR2
639 , p_token3 IN VARCHAR2
640 , p_value3 IN VARCHAR2
641 )
642 IS
643 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
644 l_error_tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
645 l_return_status VARCHAR2(32000);
646 BEGIN
647 BIS_UTILITIES_PVT.Add_Error_Message
648 ( p_error_msg_name
649 , p_error_msg_level
650 , p_error_proc_name
651 , p_error_type
652 , p_token1
653 , p_value1
654 , p_token2
655 , p_value2
656 , p_token3
657 , p_value3
658 , l_error_tbl_p
659 , l_error_tbl
660 );
661 BIS_ERROR_MESSAGE_PVT.Update_Error_Log( l_error_tbl(1)
662 , l_return_status
663 , l_error_tbl
664 );
665 END Add_error_Message;
666
667 -- this procedure adds a message to the error table
668 PROCEDURE Add_Error_Message
669 ( p_error_msg_id IN NUMBER := NULL
670 , p_error_description IN VARCHAR2 := NULL
671 , p_error_proc_name IN VARCHAR2 := NULL
672 , p_error_type IN VARCHAR2 := BIS_UTILITIES_PUB.G_ERROR
673 )
674 IS
675 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
676 l_error_tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
677 l_return_status VARCHAR2(32000);
678 BEGIN
679 BIS_UTILITIES_PVT.Add_Error_Message
680 ( p_error_msg_id
681 , p_error_description
682 , p_error_proc_name
683 , p_error_type
684 , l_error_tbl_p
685 , l_error_tbl
686 );
687 BIS_ERROR_MESSAGE_PVT.Update_Error_Log( l_error_tbl(1)
688 , l_return_status
689 , l_error_tbl
690 );
691 END Add_error_Message;
692 --
693 PROCEDURE putStyle
694 IS
695 BEGIN
696 htp.p('
697 <STYLE TYPE="text/css">
698 FONT.button
699 {font-family: arial, sans-serif; color: black; text-decoration: none; font-size: 10pt}
700
701 FONT.disbutton
702 {font-family: arial, sans-serif; color: #666666; text-decoration: none; font-size: 10pt}
703
704 FONT.tableHeader
705 {font-family: arial, sans-serif; font-weight: bold; color: white; text-decoration: none; font-size: 10pt}
706
707 all.normal
708 {font-family: arial, sans-serif; color: black; font-size: 10pt}
709
710 all.bold
711 {font-family: arial, sans-serif; font-weight: bold; color: black; font-size: 10pt}
712
713 TD
714 {font-family: arial, sans-serif; color: black; font-size: 10pt}
715
716 TEXTAREA
717 {font-family: arial, sans-serif; color: black; font-size: 10pt}
718
719 FONT.normalLink
720 {font-family: arial, sans-serif; font-size: 10pt}
721 </STYLE>
722 ');
723 END putStyle;
724
725 -- This function will return a string
726 -- The string is a html table with all the images arranged
727 -- properly in this table according to the buttons desired
728 PROCEDURE GetButtonString
729 ( p_Button_table in HTML_Button_Tbl_Type
730 , x_str out NOCOPY varchar2
731 )
732 is
733 l_str varchar2(32000);
734 l_thickness number;
735 l_swan_enabled BOOLEAN;
736 l_left_flat_image_src VARCHAR2(1000);
737 l_top_outer_edge_img_src VARCHAR2(1000);
738 l_right_flat_edge_img_src VARCHAR2(1000);
739 l_bot_inner_edge_img_src VARCHAR2(1000);
740 l_bot_outer_edge_img_src VARCHAR2(1000);
741 BEGIN
742 --
743 l_swan_enabled := BIS_UTILITIES_PVT.checkSWANEnabled();
744 IF(l_swan_enabled)THEN
745 l_left_flat_image_src := '<img src="'||G_R12_LEFT_FLAT_EDGE_IMG_SRC||'">';
746 l_top_outer_edge_img_src := '<img src="'||G_R12_TOP_OUTER_EDGE_IMG_SRC||'">';
747 l_right_flat_edge_img_src := '<img src="'||G_R12_RIGHT_FLAT_EDGE_IMG_SRC||'">';
748 l_bot_inner_edge_img_src := '<img src="'||G_R12_BOT_INNER_EDGE_IMG_SRC||'">';
749 l_bot_outer_edge_img_src := '<img src ="'||G_R12_BOT_OUTER_EDGE_IMG_SRC||'">';
750 ELSE
751 l_left_flat_image_src := '<img src="'||G_LEFT_FLAT_EDGE_IMG_SRC||'">';
752 l_top_outer_edge_img_src := '<img src="'||G_TOP_OUTER_EDGE_IMG_SRC||'">';
753 l_right_flat_edge_img_src := '<img src="'||G_RIGHT_FLAT_EDGE_IMG_SRC||'">';
754 l_bot_inner_edge_img_src := '<img src="'||G_BOT_INNER_EDGE_IMG_SRC||'">';
755 l_bot_outer_edge_img_src := '<img src ="'||G_BOT_OUTER_EDGE_IMG_SRC||'">';
756 END IF;
757
758 x_str := htf.tableOpen( cborder => 'border=0'
759 , cattributes => 'cellpadding=0 cellspacing=0'
760 );
761 --
762 -- put in the first row of the table which will put in the from and back
763 -- ends and the top row
764 x_str := x_str || htf.tableRowOpen;
765 --
766 FOR i IN 1 .. p_Button_table.COUNT LOOP
767 IF (p_Button_table(i).left_edge = G_ROUND_EDGE) THEN
768 l_str := '<img src="'||G_LEFT_ROUND_EDGE_IMG_SRC||'">';
769 ELSIF (p_Button_table(i).left_edge = G_FLAT_EDGE) THEN
770 l_str := l_left_flat_image_src;
771 END IF;
772 x_str := x_str || htf.tableData( cvalue => l_str
773 , crowspan => 5
774 );
775 --
776 l_str := l_top_outer_edge_img_src;
777 x_str := x_str || htf.tableData
778 ( cvalue => l_str
779 -- , cattributes => 'bgcolor="'||G_TOP_OUTER_EDGE_COLOR||'"'
780 , cattributes => 'class="G_TOP_OUTER_EDGE_COLOR"'
781 );
782 --
783 --
784 if (p_Button_table(i).right_edge = G_ROUND_EDGE) then
785 l_str := '<img src="'||G_RIGHT_ROUND_EDGE_IMG_SRC||'">';
786 elsif (p_Button_table(i).right_edge = G_FLAT_EDGE) then
787 l_str := l_right_flat_edge_img_src;
788 end if;
789 x_str := x_str || htf.tableData( cvalue => l_str
790 , crowspan => 5
791 );
792 --
793 if (i < p_Button_table.COUNT) then
794 if ( p_Button_table(i).right_edge = G_FLAT_EDGE
795 AND p_Button_table(i+1).left_edge = G_FLAT_EDGE
796 ) then
797 l_thickness := G_FF_SPACER_THICKNESS;
798 else
799 l_thickness := G_STD_SPACER_THICKNESS;
800 end if;
801 --
802 x_str := x_str || htf.tableData( crowspan => 5
803 , cattributes=>'width="'||l_thickness||'"'
804 );
805 end if;
806 end loop;
807 x_str := x_str || htf.tableRowClose;
808 --
809 -- put the top inner white line
810 x_str := x_str || htf.tableRowOpen;
811 for i in 1 .. p_Button_table.COUNT loop
812 l_str := '<img src ="'||G_TOP_INNER_EDGE_IMG_SRC||'">';
813 x_str := x_str || htf.tableData
814 ( cvalue => l_str
815 -- , cattributes => 'bgcolor="'||G_TOP_INNER_EDGE_COLOR||'"'
816 , cattributes => 'class="G_TOP_INNER_EDGE_COLOR"'
817 );
818 end loop;
819 x_str := x_str || htf.tableRowClose;
820 --
821 -- put the images etc. for the button
822 x_str := x_str || htf.tableRowOpen;
823 for i in 1 .. p_Button_table.COUNT loop
824 --
825 if (p_Button_table(i).disabled = FND_API.G_TRUE) then
826 l_str := htf.FontOpen(cattributes => 'class=disbutton');
827 elsif(p_Button_table(i).disabled = FND_API.G_FALSE) then
828 l_str := htf.FontOpen(cattributes => 'class=button');
829 end if;
830 --
831 l_str := l_str || p_Button_table(i).label;
832 l_str := l_str || htf.fontClose;
833 --
834
835 if(p_Button_table(i).disabled = FND_API.G_FALSE) then
836 l_str := htf.anchor( curl => p_Button_table(i).href
837 , ctext => l_str
838 );
839 end if;
840 --
841 --l_str := 'class="G_BUTTON_BG_COLOR" height="'||G_BUTTON_HEIGHT || '"';
842 x_str := x_str || htf.tableData( cvalue => l_str
843 , cnowrap => 'Y'
844 /*, cattributes => 'bgcolor="'
845 --|| G_BUTTON_BG_COLOR
846 || G_BUTTON_BG_COLOR
847 || '" height="'
848 || G_BUTTON_HEIGHT||'"' */
849 /* , cattributes => 'class="G_BUTTON_BG_COLOR"
850 || " height="'
851 || G_BUTTON_HEIGHT||'"'*/
852 , cattributes => 'class="G_BUTTON_BG_COLOR" height="'||BIS_PORTLET_CUSTOM_PUB.c_BUTTON_HEIGHT || '"'
853 );
854 end loop;
855 x_str := x_str || htf.tableRowClose;
856 --
857 -- put the bottom inner line
858 x_str := x_str || htf.tableRowOpen;
859 for i in 1 .. p_Button_table.COUNT loop
860 l_str := l_bot_inner_edge_img_src;--'<img src ="'||G_BOT_INNER_EDGE_IMG_SRC||'">';
861 x_str := x_str ||
862 htf.tableData
863 ( cvalue => l_str
864 -- , cattributes => 'bgcolor="'||G_BOT_INNER_EDGE_COLOR||'"'
865 , cattributes => 'class="G_BOT_INNER_EDGE_COLOR"'
866 );
867 end loop;
868 x_str := x_str || htf.tableRowClose;
869 --
870 -- put the bottom outer line
871 x_str := x_str || htf.tableRowOpen;
872 for i in 1 .. p_Button_table.COUNT loop
873 null;
874 l_str := l_bot_outer_edge_img_src;--'<img src ="'||G_BOT_OUTER_EDGE_IMG_SRC||'">';
875 x_str := x_str ||
876 htf.tableData
877 ( cvalue => l_str
878 -- , cattributes => 'bgcolor="'||G_BOT_OUTER_EDGE_COLOR||'"'
879 , cattributes => 'class="G_BOT_OUTER_EDGE_COLOR"'
880 );
881 end loop;
882 x_str := x_str || htf.tableRowClose;
883 x_str := x_str || htf.tableClose;
884 --
885 end GetButtonString;
886 --
887 -- This function starts table with the
888 -- standard margins on left and right
889 -- takes in the number of columns and rows in the table
890 PROCEDURE tableOpen
891 ( p_num_row in NUMBER
892 , p_num_col in NUMBER
893 )
894 is
895 begin
896 htp.tableOpen( calign => 'CENTER'
897 , cborder => 'BORDER=0'
898 , cattributes => 'WIDTH="100%"'
899 );
900 htp.tableRowOpen;
901 htp.tableData( crowspan => p_num_row + 1
902 , cattributes => 'width="'
903 || BIS_UTILITIES_PVT.G_TABLE_LEFT_MARGIN_PERCENT
904 || '%"'
905 );
906 htp.tableData(ccolspan => p_num_col);
907 htp.tableData( crowspan => p_num_row + 1
908 , cattributes => 'width="'
909 || BIS_UTILITIES_PVT.G_TABLE_RIGHT_MARGIN_PERCENT
910 || '%"'
911 );
912 htp.tableRowClose;
913 end tableOpen;
914
915 PROCEDURE tableClose
916 is
917 begin
918 htp.tableClose;
919 end tableClose;
920
921 PROCEDURE putSaveFunction
922 ( p_form_name varchar2
923 , p_action_var varchar2
924 , p_str varchar2
925 , p_submit_form varchar2
926 )
927 is
928 begin
929 putFunction( p_form_name
930 , p_action_var
931 , p_str
932 , G_FUNCTION_SUBMIT_FORM_SAVE
933 , G_ACTION_SAVE
934 , p_submit_form
935 );
936 end putSaveFunction;
937
938 PROCEDURE putDeleteFunction
939 ( p_form_name varchar2
940 , p_action_var varchar2
941 , p_str varchar2
942 , p_submit_form varchar2
943 )
944 is
945 begin
946
947 htp.p('function ' || G_FUNCTION_SUBMIT_FORM_DELETE || '()
948 {
949 if (confirm("'
950 || BIS_UTILITIES_PVT.Get_FND_Message
951 ( p_message_name => 'BIS_CONFIRM_DELETE_MESSAGE' )
952 || '"))
953 {document.'
954 || p_form_name
955 ||'.'
956 || p_action_var
957 || '.value="'
958 || G_ACTION_DELETE
959 || '";'
960 );
961
962 if (p_str is not null) then
963 htp.p(p_str);
964 end if;
965
966 if (p_submit_form = FND_API.G_TRUE) then
967 htp.p('document.'|| p_form_name||'.submit();');
968 end if;
969
970 htp.p('}
971 }');
972
973
974 end putDeleteFunction;
975
976 PROCEDURE putNewFunction
977 ( p_form_name varchar2
978 , p_action_var varchar2
979 , p_str varchar2
980 , p_submit_form varchar2
981 )
982 is
983 begin
984 putFunction( p_form_name
985 , p_action_var
986 , p_str
987 , G_FUNCTION_SUBMIT_FORM_NEW
988 , G_ACTION_NEW
989 , p_submit_form
990 );
991 end putNewFunction;
992
993 PROCEDURE putUpdateFunction
994 ( p_form_name varchar2
995 , p_action_var varchar2
996 , p_str varchar2
997 , p_submit_form varchar2
998 )
999 is
1000 begin
1001 putFunction( p_form_name
1002 , p_action_var
1003 , p_str
1004 , G_FUNCTION_SUBMIT_FORM_UPDATE
1005 , G_ACTION_UPDATE
1006 , p_submit_form
1007 );
1008 end putUpdateFunction;
1009
1010 PROCEDURE putBackFunction
1011 ( p_form_name varchar2
1012 , p_action_var varchar2
1013 , p_str varchar2
1014 , p_submit_form varchar2
1015 )
1016 is
1017 begin
1018 putFunction( p_form_name
1019 , p_action_var
1020 , p_str
1021 , G_FUNCTION_SUBMIT_FORM_BACK
1022 , G_ACTION_BACK
1023 , p_submit_form
1024 );
1025 end putBackFunction;
1026
1027 PROCEDURE putNextFunction
1028 ( p_form_name varchar2
1029 , p_action_var varchar2
1030 , p_str varchar2
1031 , p_submit_form varchar2
1032 )
1033 is
1034 begin
1035 putFunction( p_form_name
1036 , p_action_var
1037 , p_str
1038 , G_FUNCTION_SUBMIT_FORM_NEXT
1039 , G_ACTION_NEXT
1040 , p_submit_form
1041 );
1042 end putNextFunction;
1043
1044 PROCEDURE putCancelFunction
1045 ( p_form_name varchar2
1046 , p_action_var varchar2
1047 , p_str varchar2
1048 , p_submit_form varchar2
1049 )
1050 is
1051 begin
1052 putFunction( p_form_name
1053 , p_action_var
1054 , p_str
1055 , G_FUNCTION_SUBMIT_FORM_CANCEL
1056 , G_ACTION_CANCEL
1057 , p_submit_form
1058 );
1059 end putCancelFunction;
1060
1061 PROCEDURE putRevertFunction
1062 ( p_form_name varchar2
1063 , p_action_var varchar2
1064 , p_str varchar2
1065 , p_submit_form varchar2
1066 )
1067 is
1068 begin
1069 putFunction( p_form_name
1070 , p_action_var
1071 , p_str
1072 , G_FUNCTION_SUBMIT_FORM_REVERT
1073 , G_ACTION_REVERT
1074 , p_submit_form
1075 );
1076 end putRevertFunction;
1077
1078 PROCEDURE putDoneFunction
1079 ( p_form_name varchar2
1080 , p_action_var varchar2
1081 , p_str varchar2
1082 , p_submit_form varchar2
1083 )
1084 is
1085 begin
1086 putFunction( p_form_name
1087 , p_action_var
1088 , p_str
1089 , G_FUNCTION_SUBMIT_FORM_DONE
1090 , G_ACTION_DONE
1091 , p_submit_form
1092 );
1093 end putDoneFunction;
1094
1095 PROCEDURE putOKFunction
1096 ( p_form_name varchar2
1097 , p_action_var varchar2
1098 , p_str varchar2
1099 , p_submit_form varchar2
1100 )
1101 is
1102 begin
1103 putFunction( p_form_name
1104 , p_action_var
1105 , p_str
1106 , G_FUNCTION_SUBMIT_FORM_OK
1107 , G_ACTION_OK
1108 , p_submit_form
1109 );
1110 end putOKFunction;
1111
1112 PROCEDURE putFunction
1113 ( p_form_name varchar2
1114 , p_action_var varchar2
1115 , p_str varchar2
1116 , p_function_name varchar2
1117 , p_action varchar2
1118 , p_submit_form varchar2
1119 )
1120 is
1121 begin
1122 htp.p('function '||p_function_name||'(){
1123 document.'|| p_form_name||'.'||p_action_var||'.value="'
1124 ||p_action||'";'
1125 );
1126
1127 if (p_str is not null) then
1128 htp.p(p_str);
1129 end if;
1130
1131 if (p_submit_form = FND_API.G_TRUE) then
1132 htp.p('document.'|| p_form_name||'.submit();');
1133 end if;
1134
1135 htp.p('}');
1136
1137 end putFunction;
1138
1139 PROCEDURE putVerticalSpacer(p_col_num NUMBER)
1140 is
1141 begin
1142 htp.tableRowOpen;
1143 htp.tableData( cvalue => '<img src='||G_PXC_FFFFFF_IMG_SRC||'>'
1144 , ccolspan => p_col_num
1145 , cattributes => 'bgcolor=#FFFFFF'
1146 );
1147 htp.tableRowClose;
1148 end putVerticalSpacer;
1149
1150 PROCEDURE putGreyLine(p_col_num NUMBER)
1151 is
1152 begin
1153 htp.tableRowOpen;
1154 htp.tableData( cvalue => htf.img( curl => G_PXC_CCCCCC_IMG_SRC
1155 , cattributes => 'height=1 width=100%')
1156 , ccolspan => p_col_num
1157 --- , cattributes => 'height=1 valign=bottom align=left bgcolor=#CCCCCC '
1158 );
1159 htp.tableRowClose;
1160 end putGreyLine;
1161 ---
1162 PROCEDURE getGroupBoxString
1163 ( p_title_string IN varchar2
1164 , p_title_bold IN varchar2 := FND_API.G_FALSE
1165 , p_data_string IN varchar2
1166 , x_str OUT NOCOPY varchar2
1167 )
1168 is
1169 begin
1170 x_str := htf.tableOpen( calign => 'CENTER'
1171 , cattributes => 'border=0 cellpadding=0 cellspacing=0
1172 width="'
1173 || G_GROUP_BOX_TABLE_WIDTH_PRCNT||'%"'
1174 );
1175 x_str := x_str || htf.tableRowOpen;
1176 x_str := x_str || htf.tableData(ccolspan=>3, cattributes=>'height="12"');
1177
1178 IF p_title_bold = FND_API.G_TRUE THEN
1179 x_str := x_str || htf.tableData( cvalue
1180 => htf.fontOpen(cattributes=>'class=bold')
1181 || '&'||'nbsp;'||p_title_string||'&'||'nbsp;'
1182 || htf.fontClose
1183 , crowspan => 3
1184 , cattributes => 'valign=bottom NOWRAP'
1185 );
1186 ELSE
1187 x_str := x_str || htf.tableData( cvalue
1188 => htf.fontOpen(cattributes=>'class=normal')
1189 || '&'||'nbsp;'||p_title_string||'&'||'nbsp;'
1190 || htf.fontClose
1191 , crowspan => 3
1192 , cattributes => 'valign=bottom NOWRAP'
1193 );
1194 END IF;
1195 x_str := x_str || htf.tableData(cattributes=>'width = 1000');
1196 x_str := x_str || htf.tableData(ccolspan=>3, cattributes=>'height=12');
1197 x_str := x_str || htf.tableRowClose;
1198
1199 x_str := x_str || htf.tableRowOpen;
1200 x_str := x_str || htf.tableData( cvalue =>htf.img(G_TOP_LEFT_CURVE_IMG_SRC)
1201 , ccolspan=>2
1202 , crowspan=>2
1203 , cattributes=>'height=1 width=1'
1204 );
1205 x_str := x_str || htf.tableData( cvalue => htf.img(G_PXC_666666_IMG_SRC)
1206 , cattributes => 'height=1 bgcolor='||
1207 G_PXC_666666
1208 );
1209 x_str := x_str || htf.tableData( cvalue => htf.img(G_PXC_666666_IMG_SRC)
1210 , cattributes => 'height=1 bgcolor='||
1211 G_PXC_666666
1212 );
1213 x_str := x_str || htf.tableData( cvalue =>htf.img(G_TOP_RIGHT_CURVE_IMG_SRC)
1214 , ccolspan=>2
1215 , crowspan=>2
1216 , cattributes=>'height=1 width=1'
1217 );
1218 x_str := x_str || htf.tableRowClose;
1219
1220 x_str := x_str || htf.tableRowOpen;
1221 x_str := x_str || htf.tableData( cattributes => 'height=5');
1222 x_str := x_str || htf.tableData( cattributes => 'height=5');
1223 x_str := x_str || htf.tableRowClose;
1224
1225 x_str := x_str || htf.tableRowOpen;
1226 x_str := x_str || htf.tableData( cvalue => htf.img(G_PXC_666666_IMG_SRC)
1227 , cattributes => 'width=1 bgcolor='
1228 || G_PXC_666666
1229 );
1230 x_str := x_str || htf.tableData( cattributes => 'width=5');
1231 x_str := x_str || htf.tableData( cattributes => 'width=10');
1232 x_str := x_str || htf.tableData( cvalue => p_data_string
1233 , ccolspan => 2
1234 , calign => 'CENTER'
1235 );
1236 x_str := x_str || htf.tableData(cattributes => 'width=5');
1237 x_str := x_str || htf.tableData(cattributes => 'width=1');
1238 x_str := x_str || htf.tableData( cvalue => htf.img(G_PXC_666666_IMG_SRC)
1239 , cattributes => 'width=1 bgcolor='
1240 || G_PXC_666666
1241 );
1242 x_str := x_str || htf.tableRowClose;
1243
1244 x_str := x_str || htf.tableRowOpen;
1245 x_str := x_str || htf.tableData(cvalue =>htf.img(G_BOT_LEFT_CURVE_IMG_SRC)
1246 , cattributes => 'height=1 width=1'
1247 , ccolspan => 2
1248 , crowspan => 2
1249 );
1250 x_str := x_str || htf.tableData( ccolspan => 3
1251 , cattributes => 'height=5'
1252 );
1253 x_str := x_str || htf.tableData(cvalue=>htf.img(G_BOT_RIGHT_CURVE_IMG_SRC)
1254 , cattributes => 'height=1 width=1'
1255 , ccolspan => 2
1256 , crowspan => 2
1257 );
1258 x_str := x_str || htf.tableRowClose;
1259
1260 x_str := x_str || htf.tableRowOpen;
1261 x_str := x_str || htf.tableData(cvalue=>htf.img(G_PXC_666666_IMG_SRC)
1262 , ccolspan => 3
1263 , cattributes =>'height=1 width=1000 bgcolor='
1264 || G_PXC_666666
1265 );
1266 x_str := x_str || htf.tableRowClose;
1267 x_str := x_str || htf.tableClose;
1268
1269 end getGroupBoxString;
1270
1271 PROCEDURE getGroupBoxString
1272 ( p_title_string IN varchar2
1273 , p_title_bold IN varchar2 := FND_API.G_FALSE
1274 , p_data_tbl IN BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1275 , x_data_tbl OUT NOCOPY BIS_UTILITIES_PUB.BIS_VARCHAR_TBL
1276 )
1277 IS
1278 l_str VARCHAR2(32000);
1279 BEGIN
1280
1281 l_str := htf.tableOpen( calign => 'CENTER'
1282 , cattributes => 'border=0 cellpadding=0 cellspacing=0
1283 width='
1284 || G_GROUP_BOX_TABLE_WIDTH_PRCNT||'%'
1285 );
1286 l_str := l_str || htf.tableRowOpen;
1287 l_str := l_str || htf.tableData(ccolspan=>3, cattributes=>'height=12');
1288
1289 IF p_title_bold = FND_API.G_TRUE THEN
1290 l_str := l_str || htf.tableData( cvalue
1291 => htf.fontOpen(cattributes=>'class=bold')
1292 || '&'||'nbsp;'||p_title_string||'&'||'nbsp;'
1293 || htf.fontClose
1294 , crowspan => 3
1295 , cattributes => 'valign=bottom NOWRAP'
1296 );
1297 ELSE
1298 l_str := l_str || htf.tableData( cvalue
1299 => htf.fontOpen(cattributes=>'class=normal')
1300 || '&'||'nbsp;'||p_title_string||'&'||'nbsp;'
1301 || htf.fontClose
1302 , crowspan => 3
1303 , cattributes => 'valign=bottom NOWRAP'
1304 );
1305 END IF;
1306
1307 l_str := l_str || htf.tableData(cattributes=>'width = 1000');
1308 l_str := l_str || htf.tableData(ccolspan=>3, cattributes=>'height=12');
1309 l_str := l_str || htf.tableRowClose;
1310
1311 l_str := l_str || htf.tableRowOpen;
1312 l_str := l_str || htf.tableData( cvalue =>htf.img(G_TOP_LEFT_CURVE_IMG_SRC)
1313 , ccolspan=>2
1314 , crowspan=>2
1315 , cattributes=>'height=1 width=1'
1316 );
1317 l_str := l_str || htf.tableData( cvalue => htf.img(G_PXC_666666_IMG_SRC)
1318 , cattributes => 'height=1 bgcolor='||
1319 G_PXC_666666
1320 );
1321 l_str := l_str || htf.tableData( cvalue => htf.img(G_PXC_666666_IMG_SRC)
1322 , cattributes => 'height=1 bgcolor='||
1323 G_PXC_666666
1324 );
1325 l_str := l_str || htf.tableData( cvalue =>htf.img(G_TOP_RIGHT_CURVE_IMG_SRC)
1326 , ccolspan=>2
1327 , crowspan=>2
1328 , cattributes=>'height=1 width=1'
1329 );
1330 l_str := l_str || htf.tableRowClose;
1331
1332 l_str := l_str || htf.tableRowOpen;
1333 l_str := l_str || htf.tableData( cattributes => 'height=5');
1334 l_str := l_str || htf.tableData( cattributes => 'height=5');
1335 l_str := l_str || htf.tableRowClose;
1336
1337 l_str := l_str || htf.tableRowOpen;
1338 l_str := l_str || htf.tableData( cvalue => htf.img(G_PXC_666666_IMG_SRC)
1339 , cattributes => 'width=1 bgcolor='
1340 || G_PXC_666666
1341 );
1342 l_str := l_str || htf.tableData( cattributes => 'width=5');
1343 l_str := l_str || htf.tableData( cattributes => 'width=10');
1344
1345 x_data_tbl(1) := l_str;
1346 x_data_tbl(2) := '<TD COLSPAN="2" ALIGN="CENTER">';
1347 for i in 1 .. p_data_tbl.COUNT LOOP
1348 x_data_tbl(i+2) := p_data_tbl(i);
1349 END LOOP;
1350 x_data_tbl(x_data_tbl.COUNT + 1) := '</TD>';
1351
1352 l_str := htf.tableData(cattributes => 'width=5');
1353 l_str := l_str || htf.tableData(cattributes => 'width=1');
1354 l_str := l_str || htf.tableData( cvalue => htf.img(G_PXC_666666_IMG_SRC)
1355 , cattributes => 'width=1 bgcolor='
1356 || G_PXC_666666
1357 );
1358 l_str := l_str || htf.tableRowClose;
1359
1360 l_str := l_str || htf.tableRowOpen;
1361 l_str := l_str || htf.tableData(cvalue =>htf.img(G_BOT_LEFT_CURVE_IMG_SRC)
1362 , cattributes => 'height=1 width=1'
1363 , ccolspan => 2
1364 , crowspan => 2
1365 );
1366 l_str := l_str || htf.tableData( ccolspan => 3
1367 , cattributes => 'height=5'
1368 );
1369 l_str := l_str || htf.tableData(cvalue=>htf.img(G_BOT_RIGHT_CURVE_IMG_SRC)
1370 , cattributes => 'height=1 width=1'
1371 , ccolspan => 2
1372 , crowspan => 2
1373 );
1374 l_str := l_str || htf.tableRowClose;
1375
1376 l_str := l_str || htf.tableRowOpen;
1377 l_str := l_str || htf.tableData(cvalue=>htf.img(G_PXC_666666_IMG_SRC)
1378 , ccolspan => 3
1379 , cattributes =>'height=1 width=1000 bgcolor='
1380 || G_PXC_666666
1381 );
1382 l_str := l_str || htf.tableRowClose;
1383 l_str := l_str || htf.tableClose;
1384
1385 x_data_tbl(x_data_tbl.COUNT + 1) := l_str;
1386
1387 END getGroupBoxString;
1388
1389 --- Obsolete Procedures
1390 --- 1) getRightSide
1391 --- 1) getLeftSide
1392 --- 1) getTopRightSide
1393 --- 1) getTopLeftSide
1394
1395
1396 PROCEDURE getLeftEdge
1397 ( p_row_num IN NUMBER
1398 , p_heading IN VARCHAR2
1399 , x_str OUT NOCOPY VARCHAR2
1400 )
1401 IS
1402 l_line_color VARCHAR2(32000);
1403 l_img_src VARCHAR2(32000);
1404 BEGIN
1405
1406 if (p_heading = FND_API.G_TRUE) then
1407
1408 --- rounded corners no longer needed
1409
1410 x_str := x_str || htf.tableData
1411 ( cvalue => htf.img(curl => G_PXC_6699CC_IMG_SRC
1412 , cattributes => 'width=6'
1413 )
1414 , calign => 'RIGHT'
1415 , ccolspan => 2
1416 , cattributes => ' bgcolor='||G_PXC_6699CC
1417 );
1418 --- end if;
1419 else
1420 if (mod(p_row_num, 2) = 1) then
1421 l_line_color := G_PXC_FFFFFF;
1422 l_img_src := G_PXC_FFFFFF_IMG_SRC;
1423 else
1424 l_line_color := G_PXC_CCCCCC;
1425 l_img_src := G_PXC_CCCCCC_IMG_SRC;
1426 end if;
1427
1428 x_str := x_str || htf.tableData
1429 ( cvalue => htf.img( curl => G_PXC_6699CC_IMG_SRC
1430 , cattributes => 'width=1'
1431 )
1432 , calign => 'RIGHT'
1433 , cattributes => ' bgcolor='||G_PXC_6699CC
1434 );
1435
1436 x_str := x_str || htf.tableData
1437 ( cvalue => htf.img( curl => l_img_src
1438 , cattributes => 'width=5'
1439 )
1440 , calign => 'RIGHT'
1441 , cattributes => ' bgcolor='||l_line_color
1442 );
1443 end if;
1444
1445 end getLeftEdge;
1446
1447 PROCEDURE getRightEdge
1448 ( p_row_num IN NUMBER
1449 , p_heading IN VARCHAR2
1450 , x_str OUT NOCOPY VARCHAR2
1451 )
1452 IS
1453 l_line_color VARCHAR2(32000);
1454 l_img_src VARCHAR2(32000);
1455 BEGIN
1456
1457 if (p_heading = FND_API.G_TRUE) then
1458 ---rounded corners no longer needed
1459 x_str := x_str || htf.tableData
1460 ( cvalue => htf.img(curl => G_PXC_6699CC_IMG_SRC
1461 , cattributes => 'width=6'
1462 )
1463 , calign => 'LEFT'
1464 , ccolspan => 2
1465 , cattributes => ' bgcolor='||G_PXC_6699CC
1466 );
1467 --- end if;
1468 else
1469 if (mod(p_row_num, 2) = 1) then
1470 l_line_color := G_PXC_FFFFFF;
1471 l_img_src := G_PXC_FFFFFF_IMG_SRC;
1472 else
1473 l_line_color := G_PXC_CCCCCC;
1474 l_img_src := G_PXC_CCCCCC_IMG_SRC;
1475 end if;
1476
1477 x_str := x_str || htf.tableData
1478 ( cvalue => htf.img( curl => l_img_src
1479 , cattributes => 'width=5'
1480 )
1481 , calign => 'RIGHT'
1482 , cattributes => ' bgcolor='||l_line_color
1483 );
1484
1485 x_str := x_str || htf.tableData
1486 ( cvalue => htf.img( curl => G_PXC_6699CC_IMG_SRC
1487 , cattributes => 'width=1'
1488 )
1489 , calign => 'RIGHT'
1490 , cattributes => ' width=1 bgcolor='||G_PXC_6699CC
1491 );
1492
1493 end if;
1494 end getRightEdge;
1495
1496 PROCEDURE getTopHeadingLine
1497 ( p_table IN HTML_Table_Element_Tbl_Type
1498 , p_row_num IN NUMBER
1499 , p_index IN NUMBER
1500 , p_heading IN VARCHAR2
1501 , p_width IN NUMBER
1502 , x_index OUT NOCOPY NUMBER
1503 , x_str OUT NOCOPY VARCHAR2
1504 )
1505 IS
1506 l_rec HTML_Table_Element_Rec_Type;
1507 l_str VARCHAR2(32000);
1508 l_color_table color_tbl_type;
1509 l_color VARCHAR2(32000);
1510 l_img_src VARCHAR2(32000);
1511 BEGIN
1512
1513 l_color_table(0) := G_PXC_CCCCCC;
1514 l_color_table(1) := G_PXC_FFFFFF;
1515
1516 x_index := p_index;
1517 l_rec := p_table(x_index);
1518
1519 x_str := htf.tableData( cvalue =>htf.img(G_TOP_RIGHT_BLUE_CURVE_IMG_SRC)
1520 , calign => 'RIGHT'
1521 , cattributes =>
1522 'valign=top width=6'
1523 );
1524
1525 while (l_rec.row_num = p_row_num) loop
1526 if(x_index > p_index) then
1527
1528 l_color := G_PXC_6699CC;
1529 l_img_src := G_PXC_6699CC_IMG_SRC;
1530
1531 x_str := x_str || htf.tableData
1532 ( cvalue => htf.img( curl => l_img_src
1533 , cattributes => 'width=1'
1534 )
1535 , calign => 'LEFT'
1536 , cattributes =>' width=1 bgcolor='||l_color
1537 );
1538 end if;
1539
1540 l_str := l_rec.display_name;
1541
1542 if BIS_UTILITIES_PVT.Value_Missing(l_str) = FND_API.G_TRUE
1543 OR BIS_UTILITIES_PVT.Value_NULL(l_str) = FND_API.G_TRUE then
1544 l_str := '&'||'nbsp';
1545 end if;
1546
1547 -- we have a separator line in between columns
1548 -- thus we need to increase the col span to accomodate
1549 -- rowspan is increased by 1 as we are putting two rows for heading
1550 x_str := x_str
1551 || htf.tableHeader( cvalue =>
1552 htf.fontOpen(cattributes=>'class=tableheader')
1553 || l_str
1554 || htf.fontClose
1555 , calign => l_rec.align
1556 , crowspan => l_rec.row_span + 1
1557 , ccolspan => l_rec.col_span + l_rec.col_span - 1
1558 , cattributes => l_rec.attributes||' BGCOLOR='
1559 ||G_PXC_6699CC
1560 );
1561
1562 x_index := x_index + 1;
1563 if (x_index > p_table.COUNT) then
1564 exit;
1565 end if;
1566
1567 l_rec := p_table(x_index);
1568 end loop;
1569
1570 x_str := htf.tableData( cvalue =>htf.img(G_TOP_RIGHT_BLUE_CURVE_IMG_SRC)
1571 , calign => 'LEFT'
1572 , cattributes =>
1573 'valign=top width=6'
1574 );
1575
1576 x_str := x_str || htf.tableRowClose;
1577 x_str := x_str || htf.tableRowOpen
1578 ( cvalign => 'TOP'
1579 , cattributes => 'height=19'
1580 );
1581
1582 x_str := x_str || htf.tableData
1583 ( cvalue => htf.img(G_PXC_6699CC_IMG_SRC)
1584 , calign => 'LEFT'
1585 , cattributes =>
1586 'valign=bottom'
1587 ||' bgcolor='||G_PXC_6699CC
1588 );
1589
1590 x_str := x_str || htf.tableData
1591 ( cvalue => htf.img(G_PXC_6699CC_IMG_SRC)
1592 , calign => 'LEFT'
1593 , cattributes =>
1594 'valign=bottom'
1595 ||' bgcolor='||G_PXC_6699CC
1596 );
1597
1598 END getTopHeadingLine;
1599
1600 PROCEDURE putRowData
1601 ( p_table IN HTML_Table_Element_Tbl_Type
1602 , p_row_num IN NUMBER
1603 , p_index IN NUMBER
1604 , p_heading IN VARCHAR2
1605 , p_width IN NUMBER
1606 , x_index OUT NOCOPY NUMBER
1607 , x_str OUT NOCOPY VARCHAR2
1608 )
1609 IS
1610 l_rec HTML_Table_Element_Rec_Type;
1611 l_str VARCHAR2(32000);
1612 l_color_table color_tbl_type;
1613 l_color VARCHAR2(32000);
1614 l_img_src VARCHAR2(32000);
1615 BEGIN
1616
1617 l_color_table(0) := G_PXC_CCCCCC;
1618 l_color_table(1) := G_PXC_FFFFFF;
1619
1620 x_index := p_index;
1621 l_rec := p_table(x_index);
1622
1623 getLeftEdge(p_row_num, p_heading, x_str);
1624 while (l_rec.row_num = p_row_num) loop
1625 if(x_index > p_index) then
1626 -- put the separation line in the beginning
1627 -- if(p_heading = FND_API.G_TRUE) then
1628 l_color := G_PXC_6699CC;
1629 l_img_src := G_PXC_6699CC_IMG_SRC;
1630 -- else
1631 -- l_color := G_PXC_999999;
1632 -- l_img_src := G_PXC_999999_IMG_SRC;
1633 -- end if;
1634
1635 x_str := x_str || htf.tableData
1636 ( cvalue => htf.img( curl => l_img_src
1637 , cattributes => 'width=1'
1638 )
1639 , calign => 'LEFT'
1640 , cattributes =>' width=1 bgcolor='||l_color
1641 );
1642 end if;
1643
1644 l_str := l_rec.display_name;
1645 if (l_rec.href is not null) then
1646 l_str := htf.anchor2( curl => l_rec.href
1647 , ctext => l_rec.display_name
1648 );
1649 end if;
1650
1651 if BIS_UTILITIES_PVT.Value_Missing(l_str) = FND_API.G_TRUE
1652 OR BIS_UTILITIES_PVT.Value_NULL(l_str) = FND_API.G_TRUE then
1653 l_str := '&'||'nbsp';
1654 end if;
1655
1656 -- l_str := 'xx';
1657 -- take care of the vertical separators between columns
1658 l_rec.col_span := l_rec.col_span + l_rec.col_span - 1;
1659 if (p_heading = FND_API.G_TRUE) then
1660 -- we have a separator line in between columns
1661 -- thus we need to increase the col span to accomodate
1662 x_str := x_str
1663 || htf.tableHeader( cvalue =>
1664 htf.fontOpen(
1665 cattributes=>'class=tableheader'
1666 )
1667 || l_str
1668 || htf.fontClose
1669 , calign => l_rec.align
1670 , crowspan => l_rec.row_span
1671 , ccolspan => l_rec.col_span
1672 , cattributes => l_rec.attributes||' BGCOLOR='
1673 ||G_PXC_6699CC
1674 );
1675
1676 else
1677 l_color := l_color_table(mod(p_row_num, 2));
1678 x_str := x_str || htf.tableData(cvalue =>
1679 htf.fontOpen(
1680 cattributes=>'class=normal'
1681 )
1682 || l_str
1683 || htf.fontClose
1684 , calign => l_rec.align
1685 , crowspan => l_rec.row_span
1686 , ccolspan => l_rec.col_span
1687 , cattributes => l_rec.attributes
1688 || ' BGCOLOR='||l_color
1689 );
1690 end if;
1691
1692 x_index := x_index + 1;
1693 if (x_index > p_table.COUNT) then
1694 exit;
1695 end if;
1696
1697 l_rec := p_table(x_index);
1698 end loop;
1699 getRightEdge(p_row_num, p_heading, l_str);
1700 x_str := x_str || l_str;
1701
1702 END putRowData;
1703
1704 PROCEDURE getTableString
1705 ( p_heading_table IN HTML_Table_Element_Tbl_Type
1706 , p_data_table IN HTML_Table_Element_Tbl_Type
1707 , p_head_row_count IN number
1708 , p_data_row_count IN number
1709 , p_col_count IN NUMBER
1710 , x_str OUT NOCOPY varchar2
1711 )
1712 is
1713
1714 l_row_pixel_height number := 19;
1715 l_table_corner_height number := 6;
1716
1717 l_bottom_right_img varchar2(32000) := G_BOT_RIGHT_CURVE_IMG_SRC;
1718 l_bottom_left_img varchar2(32000) := G_BOT_LEFT_CURVE_IMG_SRC;
1719 l_total_rows number := p_head_row_count + p_data_row_count + 1;
1720 l_row_height number := p_data_row_count * l_row_pixel_height;
1721 l_str varchar2(32000) := '';
1722 l_temp number;
1723
1724 l_color_table color_tbl_type;
1725 l_imgsrc_table imgsrc_tbl_type;
1726 l_cur_row number := 1;
1727 l_cur_row_p NUMBER := 1;
1728 l_width number;
1729
1730 BEGIN
1731 -- if (mod(p_data_row_count, 2) = 0 AND p_data_row_count > 0) then
1732 -- l_bottom_left_img := G_BOT_LEFT_BLUE_IMG_SRC;
1733 -- l_bottom_right_img := G_BOT_RIGHT_BLUE_IMG_SRC;
1734 -- end if;
1735
1736 -- open the top level table
1737 x_str := htf.tableOpen( cborder => 'border=0'
1738 , calign => 'CENTER'
1739 , cattributes => p_col_count ||
1740 ' align=center cellpadding=0 cellspacing=0 width=96%'
1741 );
1742
1743 l_width := (100-2)/p_col_count;
1744
1745 for i in 1 .. p_head_row_count loop
1746
1747 x_str := x_str || htf.tableRowOpen
1748 ( cvalign => 'TOP'
1749 , cattributes => 'height='||l_row_pixel_height
1750 );
1751
1752 l_cur_row_p := l_cur_row;
1753 putRowData( p_heading_table
1754 , i
1755 , l_cur_row_p
1756 , FND_API.G_TRUE
1757 , l_width
1758 , l_cur_row
1759 , l_str
1760 );
1761
1762 x_str := x_str || l_str;
1763 x_str := x_str || htf.tableRowClose;
1764
1765 end loop;
1766
1767 l_cur_row := 1;
1768
1769 for i in 1 .. p_data_row_count loop
1770
1771 x_str := x_str || htf.tableRowOpen
1772 ( cvalign => 'TOP'
1773 , cattributes => 'height='||l_row_pixel_height
1774 );
1775 l_cur_row_p := l_cur_row;
1776 putRowData( p_data_table
1777 , i
1778 , l_cur_row_p
1779 , FND_API.G_FALSE
1780 , l_width
1781 , l_cur_row
1782 , l_str
1783 );
1784
1785 x_str := x_str || l_str;
1786 x_str := x_str || htf.tableRowClose;
1787
1788 end loop;
1789
1790 x_str := x_str || htf.tableRowOpen;
1791
1792 --- Rounded corners not needed anymore
1793 x_str := x_str || htf.tableData
1794 ( cvalue => htf.img( curl => G_PXC_6699CC_IMG_SRC
1795 , cattributes => 'height=1'
1796 )
1797 , calign => 'LEFT'
1798 , ccolspan => 2*p_col_count +3
1799 , cattributes =>
1800 'valign=bottom height=1 bgcolor='||G_PXC_6699CC
1801 );
1802 x_str := x_str || htf.tableData( cvalue =>
1803 htf.img(G_PXC_FFFFFF_IMG_SRC)
1804 , ccolspan => 1
1805 );
1806 --- Rounded corners no longer required
1807 x_str := x_str || htf.tableRowClose;
1808
1809 x_str := x_str || htf.tableClose;
1810 end getTableString;
1811 --
1812 PROCEDURE getTableString
1813 ( p_heading_table IN HTML_Table_Element_Tbl_Type
1814 , p_data_table IN HTML_Table_Element_Tbl_Type
1815 , p_head_row_count IN number
1816 , p_data_row_count IN number
1817 , p_col_count IN NUMBER
1818 , x_str OUT NOCOPY HTML_Tablerow_Strings_Tbl_type
1819 )
1820 is
1821
1822 l_row_pixel_height number := 19;
1823 l_table_corner_height number := 6;
1824
1825 l_bottom_right_img varchar2(32000) := G_BOT_RIGHT_CURVE_IMG_SRC;
1826 l_bottom_left_img varchar2(32000) := G_BOT_LEFT_CURVE_IMG_SRC;
1827 l_total_rows number := p_head_row_count + p_data_row_count + 1;
1828 l_row_height number := p_data_row_count * l_row_pixel_height;
1829 l_str varchar2(32000) := '';
1830 l_temp number;
1831
1832 l_color_table color_tbl_type;
1833 l_imgsrc_table imgsrc_tbl_type;
1834 l_cur_row number := 1;
1835 l_cur_row_p NUMBER := 1;
1836 l_width number;
1837 l_string_ct number ;
1838 l_str1 varchar2(32000) := '';
1839
1840 BEGIN
1841 -- if (mod(p_data_row_count, 2) = 0 AND p_data_row_count > 0) then
1842 -- l_bottom_left_img := G_BOT_LEFT_BLUE_IMG_SRC;
1843 -- l_bottom_right_img := G_BOT_RIGHT_BLUE_IMG_SRC;
1844 -- end if;
1845
1846 -- open the top level table
1847 l_string_Ct := 1;
1848 x_str(l_string_ct) := htf.tableOpen( cborder => 'border=0'
1849 , calign => 'CENTER'
1850 , cattributes => p_col_count ||
1851 ' align=center cellpadding=0 cellspacing=0 width=96%'
1852 );
1853
1854 l_width := (100-2)/p_col_count;
1855
1856 for i in 1 .. p_head_row_count loop
1857 l_string_ct := l_string_ct + 1;
1858 l_str1 := '';
1859 l_str1 := l_str1 || htf.tableRowOpen
1860 ( cvalign => 'TOP'
1861 , cattributes => 'height='||l_row_pixel_height
1862 );
1863
1864 l_cur_row_p := l_cur_row;
1865 putRowData( p_heading_table
1866 , i
1867 , l_cur_row_p
1868 , FND_API.G_TRUE
1869 , l_width
1870 , l_cur_row
1871 , l_str
1872 );
1873 l_str1 := l_str1 || l_str;
1874 l_str1 := l_str1 || htf.tableRowClose;
1875 x_str(l_string_ct) := l_str1;
1876
1877
1878 end loop;
1879 l_cur_row := 1;
1880
1881 for i in 1 .. p_data_row_count loop
1882 l_string_ct := l_string_ct + 1;
1883 l_str1 := '';
1884 l_str1 := l_str1 || htf.tableRowOpen
1885 ( cvalign => 'TOP'
1886 , cattributes => 'height='||l_row_pixel_height
1887 );
1888 l_cur_row_p := l_cur_row;
1889 putRowData( p_data_table
1890 , i
1891 , l_cur_row_p
1892 , FND_API.G_FALSE
1893 , l_width
1894 , l_cur_row
1895 , l_str
1896 );
1897 l_str1 := l_str1 || l_str;
1898 l_str1 := l_str1 || htf.tableRowClose;
1899 x_str(l_string_ct) := l_str1;
1900 --x_str(l_string_ct) := x_str(l_string_ct) || l_str;
1901 --x_str(l_string_ct) := x_str(l_string_ct) || htf.tableRowClose;
1902
1903 end loop;
1904 l_string_ct := l_string_ct + 1;
1905 x_str(l_string_ct) := htf.tableRowOpen;
1906
1907 --- Rounded corners not needed anymore
1908 x_str(l_string_ct) := x_str(l_string_ct) || htf.tableData
1909 ( cvalue => htf.img( curl => G_PXC_6699CC_IMG_SRC
1910 , cattributes => 'height=1'
1911 )
1912 , calign => 'LEFT'
1913 , ccolspan => 2*p_col_count +3
1914 , cattributes =>
1915 'valign=bottom height=1 bgcolor='||G_PXC_6699CC
1916 );
1917 x_str(l_string_ct) := x_str(l_string_ct) || htf.tableData( cvalue =>
1918 htf.img(G_PXC_FFFFFF_IMG_SRC)
1919 , ccolspan => 1
1920 );
1921 --- Rounded corners no longer required
1922 x_str(l_string_ct) := x_str(l_string_ct) || htf.tableRowClose;
1923
1924 l_string_ct := l_string_ct + 1;
1925 x_str(l_string_ct) := htf.tableClose;
1926 end getTableString;
1927 --
1928 -- concatenate the two error tables into one
1929 PROCEDURE concatenateErrorTables
1930 ( p_error_Tbl1 IN BIS_UTILITIES_PUB.Error_Tbl_Type
1931 , p_error_Tbl2 IN BIS_UTILITIES_PUB.Error_Tbl_Type
1932 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1933 )
1934 IS
1935 --
1936 l_ind NUMBER;
1937 l_Count NUMBER;
1938 --
1939 BEGIN
1940 x_error_Tbl := p_error_Tbl1;
1941 l_Count := p_error_Tbl1.COUNT;
1942 FOR l_ind IN 1..p_error_Tbl2.COUNT LOOP
1943 x_error_Tbl(l_Count + l_ind) := p_error_Tbl2(l_ind);
1944 END LOOP;
1945 END concatenateErrorTables;
1946 --
1947
1948 -- function to return NULL if G_MISS_CHAR
1949 FUNCTION CheckMissChar
1950 ( p_char IN VARCHAR2
1951 )
1952 RETURN VARCHAR2
1953 IS
1954 BEGIN
1955 IF(p_char = FND_API.G_MISS_CHAR) THEN
1956 RETURN NULL;
1957 ELSE
1958 RETURN p_char;
1959 END IF;
1960 END CheckMissChar;
1961 --
1962 -- function to return NULL if G_MISS_NUM
1963 FUNCTION CheckMissNum
1964 ( p_num IN NUMBER
1965 )
1966 RETURN NUMBER
1967 IS
1968 BEGIN
1969 IF(p_num = FND_API.G_MISS_NUM) THEN
1970 RETURN NULL;
1971 ELSE
1972 RETURN p_num;
1973 END IF;
1974 END CheckMissNum;
1975 --
1976 FUNCTION CheckMissDate
1977 ( p_date IN DATE
1978 )
1979 RETURN DATE
1980 IS
1981 BEGIN
1982 IF(p_date = BIS_UTILITIES_PUB.G_NULL_DATE) THEN
1983 RETURN NULL;
1984 ELSE
1985 RETURN p_date;
1986 END IF;
1987 END CheckMissDate;
1988 --
1989
1990 FUNCTION PutNullString
1991 ( p_Str varchar2
1992 , p_align varchar2
1993 , p_rowspan NUMBER
1994 , p_colspan NUMBER
1995 )
1996 return VARCHAR2
1997 is
1998 begin
1999
2000 if BIS_UTILITIES_PVT.Value_Missing(p_str) = FND_API.G_TRUE
2001 OR BIS_UTILITIES_PVT.Value_NOT_NULL(p_str) = FND_API.G_FALSE then
2002 return htf.tableData( cvalue => '&'||'nbsp'
2003 , calign => p_align
2004 , crowspan => p_rowspan
2005 , ccolspan => p_colspan
2006 );
2007 else
2008 return htf.tableData( cvalue => p_str
2009 , calign => p_align
2010 , crowspan => p_rowspan
2011 , ccolspan => p_colspan
2012 );
2013 end if;
2014 end PutNullString;
2015
2016
2017
2018 FUNCTION Value_Missing_Or_Null( -- 2730145
2019 p_value IN VARCHAR )
2020 RETURN VARCHAR2
2021 IS
2022 BEGIN
2023 IF ((Value_Missing(p_value) = FND_API.G_TRUE)
2024 OR
2025 (Value_Null(p_value) = FND_API.G_TRUE)) THEN
2026 RETURN FND_API.G_TRUE;
2027 ELSE
2028 RETURN FND_API.G_FALSE;
2029 END IF;
2030 END Value_Missing_Or_Null;
2031
2032
2033
2034 FUNCTION Value_Missing_Or_Null( -- 2730145
2035 p_value IN NUMBER )
2036 RETURN VARCHAR2
2037 IS
2038 BEGIN
2039 IF ((Value_Missing(p_value) = FND_API.G_TRUE)
2040 OR
2041 (Value_Null(p_value) = FND_API.G_TRUE )) THEN
2042 RETURN FND_API.G_TRUE;
2043 ELSE
2044 RETURN FND_API.G_FALSE;
2045 END IF;
2046 END Value_Missing_Or_Null;
2047
2048
2049 FUNCTION Value_Missing_Or_Null( -- 2730145
2050 p_value IN DATE )
2051 RETURN VARCHAR2
2052 IS
2053 BEGIN
2054 IF ((Value_Missing(p_value) = FND_API.G_TRUE)
2055 OR
2056 (Value_Null(p_value) = FND_API.G_TRUE)) THEN
2057 RETURN FND_API.G_TRUE;
2058 ELSE
2059 RETURN FND_API.G_FALSE;
2060 END IF;
2061 END Value_Missing_Or_Null;
2062
2063
2064 FUNCTION Value_Not_Missing_Not_Null( -- 2730145
2065 p_value IN VARCHAR2 )
2066 RETURN VARCHAR2
2067 IS
2068 BEGIN
2069 IF ((Value_Not_Missing(p_value) = FND_API.G_TRUE)
2070 AND
2071 (Value_Not_Null(p_value) = FND_API.G_TRUE)) THEN
2072 RETURN FND_API.G_TRUE;
2073 ELSE
2074 RETURN FND_API.G_FALSE;
2075 END IF;
2076 END Value_Not_Missing_Not_Null;
2077
2078
2079 FUNCTION Value_Not_Missing_Not_Null(-- 2730145
2080 p_value IN NUMBER )
2081 RETURN VARCHAR2
2082 IS
2083 BEGIN
2084 IF ((Value_Not_Missing(p_value) = FND_API.G_TRUE)
2085 AND
2086 (Value_Not_Null(p_value) = FND_API.G_TRUE)) THEN
2087 RETURN FND_API.G_TRUE;
2088 ELSE
2089 RETURN FND_API.G_FALSE;
2090 END IF;
2091 END Value_Not_Missing_Not_Null;
2092
2093
2094 FUNCTION Value_Not_Missing_Not_Null(-- 2730145
2095 p_value IN DATE )
2096 RETURN VARCHAR2
2097 IS
2098 BEGIN
2099 IF ((Value_Not_Missing(p_value) = FND_API.G_TRUE)
2100 AND
2101 (Value_Not_Null(p_value) = FND_API.G_TRUE)) THEN
2102 RETURN FND_API.G_TRUE;
2103 ELSE
2104 RETURN FND_API.G_FALSE;
2105 END IF;
2106 END Value_Not_Missing_Not_Null;
2107
2108
2109 FUNCTION Value_Not_Missing(
2110 p_value IN VARCHAR2 )
2111 RETURN VARCHAR2
2112 IS
2113 BEGIN
2114 if ( (p_value IS NULL) OR (p_value = FND_API.G_MISS_CHAR) ) THEN --2694965
2115 return FND_API.G_FALSE;
2116 else
2117 return FND_API.G_TRUE;
2118 end if;
2119 END Value_Not_Missing;
2120
2121 FUNCTION Value_Not_Missing(
2122 p_value IN NUMBER )
2123 RETURN VARCHAR2
2124 IS
2125 BEGIN
2126 if ( (p_value IS NULL) OR (p_value = FND_API.G_MISS_NUM) ) THEN --2694965
2127 return FND_API.G_FALSE;
2128 else
2129 return FND_API.G_TRUE;
2130 end if;
2131 END Value_Not_Missing;
2132
2133 FUNCTION Value_Not_Missing(
2134 p_value IN DATE )
2135 RETURN VARCHAR2
2136 IS
2137 BEGIN
2138 if ( (p_value IS NULL) OR (p_value = FND_API.G_MISS_DATE) ) THEN --2694965
2139 return FND_API.G_FALSE;
2140 else
2141 return FND_API.G_TRUE;
2142 end if;
2143 END Value_Not_Missing;
2144
2145 FUNCTION Value_Missing(
2146 p_value IN VARCHAR2 )
2147 RETURN VARCHAR2
2148 IS
2149 BEGIN
2150 if (Value_Not_Missing(p_value) = FND_API.G_TRUE) then
2151 return FND_API.G_FALSE;
2152 else
2153 return FND_API.G_TRUE;
2154 end if;
2155 END Value_Missing;
2156
2157 FUNCTION Value_Missing(
2158 p_value IN NUMBER )
2159 RETURN VARCHAR2
2160 IS
2161 BEGIN
2162 if (Value_Not_Missing(p_value) = FND_API.G_TRUE) then
2163 return FND_API.G_FALSE;
2164 else
2165 return FND_API.G_TRUE;
2166 end if;
2167 END Value_Missing;
2168
2169 FUNCTION Value_Missing(
2170 p_value IN DATE )
2171 RETURN VARCHAR2
2172 IS
2173 BEGIN
2174 if (Value_Not_Missing(p_value) = FND_API.G_TRUE) then
2175 return FND_API.G_FALSE;
2176 else
2177 return FND_API.G_TRUE;
2178 end if;
2179 END Value_Missing;
2180
2181 FUNCTION Value_Not_NULL(
2182 p_value IN VARCHAR2 )
2183 RETURN VARCHAR2
2184 IS
2185 BEGIN
2186 if (p_value IS NULL) THEN
2187 return FND_API.G_FALSE;
2188 else
2189 return FND_API.G_TRUE;
2190 end if;
2191 END Value_Not_NULL;
2192
2193 FUNCTION Value_Not_NULL(
2194 p_value IN NUMBER )
2195 RETURN VARCHAR2
2196 IS
2197 BEGIN
2198 if (p_value IS NULL) THEN
2199 return FND_API.G_FALSE;
2200 else
2201 return FND_API.G_TRUE;
2202 end if;
2203 END Value_Not_NULL;
2204
2205 FUNCTION Value_Not_NULL(
2206 p_value IN DATE )
2207 RETURN VARCHAR2
2208 IS
2209 BEGIN
2210 if (p_value is NULL) THEN
2211 return FND_API.G_FALSE;
2212 else
2213 return FND_API.G_TRUE;
2214 end if;
2215 END Value_Not_NULL;
2216
2217 FUNCTION Value_NULL(
2218 p_value IN VARCHAR2 )
2219 RETURN VARCHAR2
2220 IS
2221 BEGIN
2222 if (Value_Not_NULL(p_value) = FND_API.G_TRUE) then
2223 return FND_API.G_FALSE;
2224 else
2225 return FND_API.G_TRUE;
2226 end if;
2227 END Value_NULL;
2228
2229 FUNCTION Value_NULL(
2230 p_value IN NUMBER )
2231 RETURN VARCHAR2
2232 IS
2233 BEGIN
2234 if (Value_Not_NULL(p_value) = FND_API.G_TRUE) then
2235 return FND_API.G_FALSE;
2236 else
2237 return FND_API.G_TRUE;
2238 end if;
2239 END Value_NULL;
2240
2241 FUNCTION Value_NULL(
2242 p_value IN DATE )
2243 RETURN VARCHAR2
2244 IS
2245 BEGIN
2246 if (Value_Not_NULL(p_value) = FND_API.G_TRUE) then
2247 return FND_API.G_FALSE;
2248 else
2249 return FND_API.G_TRUE;
2250 end if;
2251 END Value_NULL;
2252
2253
2254
2255
2256
2257 PROCEDURE Set_Debug_Flag
2258 IS
2259 BEGIN
2260 BIS_UTILITIES_PVT.G_DEBUG_FLAG := 0;
2261 END Set_Debug_Flag;
2262
2263 FUNCTION Convert_to_ID
2264 ( p_id NUMBER
2265 , p_short_name VARCHAR2
2266 , p_name VARCHAR2
2267 )
2268 return VARCHAR2
2269 is
2270 BEGIN
2271
2272 if (BIS_UTILITIES_PUB.Value_Missing(p_id) = FND_API.G_TRUE
2273 OR BIS_UTILITIES_PUB.Value_NULL(p_id) = FND_API.G_TRUE) then
2274 -- id is not there
2275
2276 if ((BIS_UTILITIES_PUB.Value_Missing(p_short_name) = FND_API.G_TRUE
2277 OR BIS_UTILITIES_PUB.Value_NULL(p_short_name)= FND_API.G_TRUE)
2278 AND (BIS_UTILITIES_PUB.Value_Missing(p_name) = FND_API.G_TRUE
2279 OR BIS_UTILITIES_PUB.Value_NULL(p_name) = FND_API.G_TRUE))
2280 then
2281 return FND_API.G_FALSE;
2282 else
2283 return FND_API.G_TRUE;
2284 end if;
2285 else
2286 return FND_API.G_FALSE;
2287 end if;
2288
2289 END Convert_to_ID;
2290 --
2291 --
2292 procedure Replace_String
2293 ( p_string IN VARCHAR2
2294 , x_string OUT NOCOPY VARCHAR2
2295 )
2296 IS
2297
2298 BEGIN
2299 select DECODE(p_string,
2300 NULL,'NULL',
2301 FND_API.G_MISS_CHAR,'NULL',
2302 ''''||p_string||'''')
2303 into x_string
2304 from dual;
2305
2306 END Replace_String;
2307 --
2308 Procedure Retrieve_User_Target_Level
2309 ( p_user_id IN NUMBER
2310 , x_Target_Level_Tbl OUT NOCOPY Target_Level_Tbl_Type
2311 , x_return_status OUT NOCOPY VARCHAR2
2312 )
2313 IS
2314
2315 l_temp_Target_Level_tbl BIS_Target_LEVEL_PUB.Target_Level_Tbl_Type;
2316 l_Target_Level_Tbl Target_level_Tbl_Type;
2317 l_return_status VARCHAR2(1000);
2318 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2319
2320 BEGIN
2321
2322 BIS_Target_LEVEL_PUB.Retrieve_User_Target_Levels
2323 ( p_api_version => 1.0
2324 , p_user_id => p_user_id
2325 , p_all_info => FND_API.G_FALSE
2326 , x_Target_Level_Tbl => l_temp_Target_Level_tbl
2327 , x_return_status => l_return_status
2328 , x_error_tbl => l_error_tbl
2329 );
2330
2331 FOR i IN 1..l_temp_Target_Level_tbl.COUNT LOOP
2332 l_Target_Level_tbl(l_Target_Level_tbl.COUNT+1).target_level_id
2333 := l_temp_Target_Level_tbl(i).target_level_id ;
2334 END LOOP;
2335
2336 x_target_level_tbl := l_target_level_tbl;
2337
2338 EXCEPTION
2339 when others then
2340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2341 BIS_UTILITIES_PVT.Add_Error_Message
2342 ( p_error_msg_id => SQLCODE
2343 , p_error_description => SQLERRM
2344 , p_error_proc_name => 'Retrieve_User_Target_Level'
2345 );
2346 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2347
2348 END Retrieve_User_Target_Level;
2349 --
2350 --
2351
2352 Procedure Retrieve_User_perf_measure
2353 ( p_user_id IN NUMBER
2354 , x_Perf_measure_Tbl OUT NOCOPY Perf_Measure_Tbl_Type
2355 , x_return_status OUT NOCOPY VARCHAR2
2356 )
2357 IS
2358
2359 l_Target_Level_tbl BIS_Target_LEVEL_PUB.Target_Level_Tbl_Type;
2360 l_return_status VARCHAR2(1000);
2361 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2362 l_perf_measure_tbl perf_measure_tbl_Type;
2363
2364 BEGIN
2365
2366 BIS_Target_LEVEL_PUB.Retrieve_User_Target_Levels
2367 ( p_api_version => 1.0
2368 , p_user_id => p_user_id
2369 , p_all_info => FND_API.G_FALSE
2370 , x_Target_Level_Tbl => l_Target_Level_tbl
2371 , x_return_status => l_return_status
2372 , x_error_tbl => l_error_tbl
2373 );
2374
2375 FOR i IN 1..l_Target_Level_tbl.COUNT LOOP
2376 l_perf_measure_tbl(l_perf_measure_tbl.COUNT+1).measure_id
2377 := l_Target_Level_tbl(i).measure_id;
2378 END LOOP;
2379
2380 x_perf_measure_tbl := l_perf_measure_tbl;
2381
2382 EXCEPTION
2383 when others then
2384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2385 BIS_UTILITIES_PVT.Add_Error_Message
2386 ( p_error_msg_id => SQLCODE
2387 , p_error_description => SQLERRM
2388 , p_error_proc_name => 'Retrieve_User_Perf_Measure'
2389 );
2390 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2391
2392 END Retrieve_User_Perf_Measure;
2393 --
2394 function target_level_where_clause
2395 return varchar2 is
2396
2397 l_target_level_Tbl target_level_Tbl_Type;
2398 l_where_clause VARCHAR2(32000) := 'target_level_id in (';
2399 l_return_status VARCHAR2(200);
2400
2401 begin
2402
2403 FND_MSG_PUB.initialize;
2404
2405 Retrieve_User_target_level
2406 ( p_user_id => icx_sec.getID(icx_sec.PV_USER_ID, '', icx_sec.g_session_id) --2751984
2407 , x_Target_Level_Tbl => l_Target_Level_Tbl
2408 , x_return_status => l_return_status);
2409
2410 if l_Target_Level_Tbl.COUNT <> 0 then
2411 --
2412 for i in l_Target_Level_Tbl.first .. l_Target_Level_Tbl.last
2413 loop
2414 --
2415 if i <> 1 then
2416 --
2417 l_where_clause := l_where_clause || ',';
2418 --
2419 end if;
2420 --
2421 l_where_clause := l_where_clause ||
2422 to_char(l_Target_Level_Tbl(i).Target_level_id);
2423 --
2424 end loop;
2425 --
2426 l_where_clause := l_where_clause || ')';
2427 --
2428 else
2429 l_where_clause := '';
2430 end if;
2431 --
2432 return (l_where_clause);
2433
2434 end Target_Level_where_clause;
2435 --
2436
2437 ----
2438 function target_level_where_clause
2439 (p_user_id IN NUMBER)
2440 return varchar2 is
2441
2442 l_target_level_Tbl target_level_Tbl_Type;
2443 l_where_clause VARCHAR2(32000) := 'target_level_id in (';
2444 l_return_status VARCHAR2(200);
2445
2446 begin
2447
2448 FND_MSG_PUB.initialize;
2449
2450 Retrieve_User_target_level
2451 ( p_user_id => p_user_id
2452 , x_Target_Level_Tbl => l_Target_Level_Tbl
2453 , x_return_status => l_return_status);
2454
2455 if l_Target_Level_Tbl.COUNT <> 0 then
2456 --
2457 for i in l_Target_Level_Tbl.first .. l_Target_Level_Tbl.last
2458 loop
2459 --
2460 if i <> 1 then
2461 --
2462 l_where_clause := l_where_clause || ',';
2463 --
2464 end if;
2465 --
2466 l_where_clause := l_where_clause ||
2467 to_char(l_Target_Level_Tbl(i).Target_level_id);
2468 --
2469 end loop;
2470 --
2471 l_where_clause := l_where_clause || ')';
2472 --
2473 else
2474 l_where_clause := '';
2475 end if;
2476 --
2477 return (l_where_clause);
2478
2479 end Target_Level_where_clause;
2480 --
2481 --
2482 function Perf_measure_where_clause
2483 return varchar2 is
2484
2485 l_Perf_measure_Tbl Perf_Measure_Tbl_Type;
2486 l_where_clause VARCHAR2(32000) := 'measure_id in (';
2487 l_return_status VARCHAR2(200);
2488
2489 begin
2490
2491 FND_MSG_PUB.initialize;
2492
2493 Retrieve_User_perf_measure
2494 ( p_user_id => icx_sec.getID(icx_sec.PV_USER_ID, '', icx_sec.g_session_id) --2751984
2495 , x_Perf_measure_Tbl => l_Perf_measure_Tbl
2496 , x_return_status => l_return_status);
2497
2498 if l_Perf_measure_Tbl.COUNT <> 0 then
2499 --
2500 for i in l_Perf_measure_Tbl.first .. l_Perf_measure_Tbl.last
2501 loop
2502 --
2503 if i <> 1 then
2504 --
2505 l_where_clause := l_where_clause || ',';
2506 --
2507 end if;
2508 --
2509 l_where_clause := l_where_clause ||
2510 to_char(l_perf_measure_Tbl(i).measure_id);
2511 --
2512 end loop;
2513 --
2514 l_where_clause := l_where_clause || ')';
2515 else
2516 l_where_clause := '';
2517 end if;
2518 --
2519 return (l_where_clause);
2520
2521 end perf_measure_where_clause;
2522
2523 --
2524 function Perf_measure_where_clause
2525 (p_user_id IN NUMBER)
2526 return varchar2 is
2527
2528 l_Perf_measure_Tbl Perf_Measure_Tbl_Type;
2529 l_where_clause VARCHAR2(32000) := 'measure_id in (';
2530 l_return_status VARCHAR2(200);
2531
2532 begin
2533
2534 FND_MSG_PUB.initialize;
2535
2536 Retrieve_User_perf_measure
2537 ( p_user_id => p_user_id
2538 , x_Perf_measure_Tbl => l_Perf_measure_Tbl
2539 , x_return_status => l_return_status);
2540
2541 if l_Perf_measure_Tbl.COUNT <> 0 then
2542 --
2543 for i in l_Perf_measure_Tbl.first .. l_Perf_measure_Tbl.last
2544 loop
2545 --
2546 if i <> 1 then
2547 --
2548 l_where_clause := l_where_clause || ',';
2549 --
2550 end if;
2551 --
2552 l_where_clause := l_where_clause ||
2553 to_char(l_perf_measure_Tbl(i).measure_id);
2554 --
2555 end loop;
2556 --
2557 l_where_clause := l_where_clause || ')';
2558 else
2559 l_where_clause := '';
2560 end if;
2561 --
2562 return (l_where_clause);
2563
2564 end perf_measure_where_clause;
2565
2566 --
2567 PROCEDURE resequence_dim_level_values
2568 (p_dim_values_rec IN BIS_TARGET_PUB.TARGET_REC_TYPE
2569 ,p_sequence_dir IN VARCHAR2
2570 ,x_dim_values_rec IN OUT NOCOPY BIS_TARGET_PUB.TARGET_REC_TYPE
2571 ,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.ERROR_TBL_TYPE
2572 )
2573 IS
2574 CURSOR c_seq(p_targetlevel_id in NUMBER, p_dim IN VARCHAR2) IS
2575 SELECT x.sequence_no
2576 FROM bis_indicator_dimensions x, bis_dimensions y, bis_target_levels z
2577 WHERE x.dimension_id = y.dimension_id
2578 AND y.short_name like p_dim
2579 AND x.indicator_id = z.indicator_id
2580 AND ((z.target_level_id = p_targetlevel_id and p_targetlevel_id is not null) OR
2581 (z.short_name = p_dim_values_rec.target_level_short_name
2582 and p_dim_values_rec.target_level_short_name IS NOT NULL))
2583 ;
2584 l_org_Seq NUMBER;
2585 l_time_Seq NUMBER;
2586 BEGIN
2587 x_dim_values_rec := p_dim_Values_rec;
2588 IF (p_sequence_dir = 'N') THEN
2589 IF ((BIS_UTILITIES_PVT.value_missing(p_dim_values_rec.org_level_value_id) = FND_API.G_FALSE ) OR
2590 (BIS_UTILITIES_PVT.value_missing(p_dim_values_rec.time_level_value_id) = FND_API.G_FALSE))
2591 THEN
2592 OPEN c_seq(p_dim_values_rec.target_level_id
2593 ,BIS_UTILITIES_PVT.GET_ORG_DIMENSION_NAME_TL(p_TargetLevelId => p_dim_values_rec.target_level_id
2594 ,p_TargetLevelName => NULL));
2595 FETCH c_seq INTO l_org_seq;
2596 CLOSE c_seq;
2597 OPEN c_seq(p_dim_values_rec.target_level_id
2598 ,BIS_UTILITIES_PVT.GET_TIME_DIMENSION_NAME_TL(p_TargetLevelId => p_dim_values_rec.target_level_id
2599 ,p_TargetLevelName => NULL));
2600 FETCH c_seq INTO l_time_seq;
2601 CLOSE c_seq;
2602 x_dim_values_rec := p_dim_Values_rec;
2603 IF (l_org_Seq = 1) THEN
2604 x_dim_values_rec.dim1_level_Value_id:= p_dim_values_rec.org_level_value_id ;
2605 x_dim_values_rec.dim1_level_value_name:= p_dim_values_rec.org_level_value_name ;
2606 END IF;
2607 IF (l_org_Seq = 2) THEN
2608 x_dim_values_rec.dim2_level_Value_id:= p_dim_values_rec.org_level_value_id ;
2609 x_dim_values_rec.dim2_level_value_name:= p_dim_values_rec.org_level_value_name ;
2610 END IF;
2611 IF (l_org_Seq = 3) THEN
2612 x_dim_values_rec.dim3_level_Value_id:= p_dim_values_rec.org_level_value_id ;
2613 x_dim_values_rec.dim3_level_value_name:= p_dim_values_rec.org_level_value_name ;
2614 END IF;
2615 IF (l_org_Seq = 4) THEN
2616 x_dim_values_rec.dim4_level_Value_id:= p_dim_values_rec.org_level_value_id ;
2617 x_dim_values_rec.dim4_level_value_name:= p_dim_values_rec.org_level_value_name ;
2618 END IF;
2619 IF (l_org_Seq = 5) THEN
2620 x_dim_values_rec.dim5_level_Value_id:= p_dim_values_rec.org_level_value_id ;
2621 x_dim_values_rec.dim5_level_value_name:= p_dim_values_rec.org_level_value_name ;
2622 END IF;
2623 IF (l_org_Seq = 6) THEN
2624 x_dim_values_rec.dim6_level_Value_id:= p_dim_values_rec.org_level_value_id ;
2625 x_dim_values_rec.dim6_level_value_name:= p_dim_values_rec.org_level_value_name ;
2626 END IF;
2627 IF (l_org_Seq = 7) THEN
2628 x_dim_values_rec.dim7_level_Value_id:= p_dim_values_rec.org_level_value_id ;
2629 x_dim_values_rec.dim7_level_value_name:= p_dim_values_rec.org_level_value_name ;
2630 END IF;
2631 IF (l_time_seq = 1) THEN
2632 x_dim_values_rec.dim1_level_Value_id:= p_dim_values_rec.time_level_value_id ;
2633 x_dim_values_rec.dim1_level_value_name:= p_dim_values_rec.time_level_value_name ;
2634 END IF;
2635 IF (l_time_seq = 2) THEN
2636 x_dim_values_rec.dim2_level_Value_id:= p_dim_values_rec.time_level_value_id ;
2637 x_dim_values_rec.dim2_level_value_name:= p_dim_values_rec.time_level_value_name ;
2638 END IF;
2639 IF (l_time_seq = 3) THEN
2640 x_dim_values_rec.dim3_level_Value_id:= p_dim_values_rec.time_level_value_id ;
2641 x_dim_values_rec.dim3_level_value_name:= p_dim_values_rec.time_level_value_name ;
2642 END IF;
2643 IF (l_time_seq = 4) THEN
2644 x_dim_values_rec.dim4_level_Value_id:= p_dim_values_rec.time_level_value_id ;
2645 x_dim_values_rec.dim4_level_value_name:= p_dim_values_rec.time_level_value_name ;
2646 END IF;
2647 IF (l_time_seq = 5) THEN
2648 x_dim_values_rec.dim5_level_Value_id:= p_dim_values_rec.time_level_value_id ;
2649 x_dim_values_rec.dim5_level_value_name:= p_dim_values_rec.time_level_value_name ;
2650 END IF;
2651 IF (l_time_seq = 6) THEN
2652 x_dim_values_rec.dim6_level_Value_id:= p_dim_values_rec.time_level_value_id ;
2653 x_dim_values_rec.dim6_level_value_name:= p_dim_values_rec.time_level_value_name ;
2654 END IF;
2655 IF (l_time_seq = 7) THEN
2656 x_dim_values_rec.dim7_level_Value_id:= p_dim_values_rec.time_level_value_id ;
2657 x_dim_values_rec.dim7_level_value_name:= p_dim_values_rec.time_level_value_name ;
2658 END IF;
2659 END IF;
2660 /*x_dim_values_rec := p_dim_Values_rec;
2661 IF (p_dim_values_rec.dim1_level_value_id = FND_API.G_MISS_CHAR) THEN
2662 IF ((p_dim_values_rec.dim2_level_value_id = FND_API.G_MISS_CHAR) AND
2663 (p_dim_values_rec.dim3_level_value_id = FND_API.G_MISS_CHAR) AND
2664 (p_dim_values_Rec.dim4_level_value_id = FND_API.G_MISS_CHAR) AND
2665 (p_dim_values_rec.dim5_level_value_id = FND_API.G_MISS_CHAR)) THEN
2666 x_dim_values_rec.dim1_level_value_id := p_dim_Values_rec.org_level_value_id;
2667 x_dim_values_rec.dim2_level_value_id := p_dim_Values_rec.time_level_value_id;
2668 RETURN;
2669 END IF;
2670 END IF;
2671 IF (p_dim_values_rec.dim2_level_value_id = FND_API.G_MISS_CHAR) THEN
2672 IF ((p_dim_values_rec.dim3_level_value_id = FND_API.G_MISS_CHAR) AND
2673 (p_dim_values_Rec.dim4_level_value_id = FND_API.G_MISS_CHAR) AND
2674 (p_dim_values_Rec.dim5_level_Value_id = FND_API.G_MISS_CHAR)) THEN
2675 x_Dim_values_rec.dim2_level_Value_id := p_dim_Values_rec.org_level_Value_id;
2676 x_dim_values_rec.dim3_level_value_id := p_dim_Values_rec.time_level_Value_id;
2677 RETURN;
2678 END IF;
2679 END IF;
2680 IF (p_dim_values_rec.dim3_level_value_id IS NULL) THEN
2681 IF ((p_dim_values_rec.dim4_level_value_id IS NULL) AND
2682 (p_dim_values_Rec.dim5_level_value_id IS NULL) ) THEN
2683 x_Dim_values_rec.dim3_level_Value_id := p_dim_Values_rec.org_level_Value_id;
2684 x_dim_values_rec.dim4_level_value_id := p_dim_Values_rec.time_level_Value_id;
2685 RETURN;
2686 END IF;
2687 END IF;
2688 IF (p_dim_values_rec.dim4_level_value_id IS NULL) THEN
2689 IF (p_dim_values_rec.dim5_level_value_id IS NULL) THEN
2690 x_Dim_values_rec.dim4_level_Value_id := p_dim_Values_rec.org_level_Value_id;
2691 x_dim_values_rec.dim5_level_value_id := p_dim_Values_rec.time_level_Value_id;
2692 RETURN;
2693 END IF;
2694 END IF;
2695 IF (p_dim_values_rec.dim5_level_value_id IS NULL) THEN
2696 x_Dim_values_rec.dim5_level_Value_id := p_dim_Values_rec.org_level_Value_id;
2697 x_dim_values_rec.dim6_level_value_id := p_dim_Values_rec.time_level_Value_id;
2698 RETURN;
2699 END IF;
2700 x_dim_values_rec.dim6_level_value_id := p_dim_Values_rec.org_level_value_id;
2701 x_dim_values_rec.dim7_level_value_id := p_dim_Values_rec.time_level_value_id;
2702 */
2703 END IF;
2704 IF (p_Sequence_dir = 'R') THEN
2705 --Get the sequence number for Org and Time Dimensions
2706 OPEN c_seq(p_dim_values_rec.target_level_id
2707 ,BIS_UTILITIES_PVT.GET_ORG_DIMENSION_NAME_TL(p_TargetLevelId => p_dim_values_rec.target_level_id
2708 ,p_TargetLevelName => NULL));
2709 FETCH c_seq INTO l_org_seq;
2710 CLOSE c_seq;
2711 OPEN c_seq(p_dim_values_rec.target_level_id
2712 ,BIS_UTILITIES_PVT.GET_TIME_DIMENSION_NAME_TL(p_TargetLevelId => p_dim_values_rec.target_level_id
2713 ,p_TargetLevelName => NULL));
2714 FETCH c_seq INTO l_time_seq;
2715 CLOSE c_seq;
2716 x_dim_values_rec := p_dim_Values_rec;
2717 IF (l_org_Seq = 1) THEN
2718 x_dim_values_rec.org_level_value_id := p_dim_values_rec.dim1_level_Value_id;
2719 x_dim_values_rec.org_level_value_name := p_dim_values_rec.dim1_level_value_name;
2720 END IF;
2721 IF (l_org_Seq = 2) THEN
2722 x_dim_values_rec.org_level_value_id := p_dim_values_rec.dim2_level_Value_id;
2723 x_dim_values_rec.org_level_value_name := p_dim_values_rec.dim2_level_value_name;
2724 END IF;
2725 IF (l_org_Seq = 3) THEN
2726 x_dim_values_rec.org_level_value_id := p_dim_values_rec.dim3_level_Value_id;
2727 x_dim_values_rec.org_level_value_name := p_dim_values_rec.dim3_level_value_name;
2728 END IF;
2729 IF (l_org_Seq = 4) THEN
2730 x_dim_values_rec.org_level_value_id := p_dim_values_rec.dim4_level_Value_id;
2731 x_dim_values_rec.org_level_value_name := p_dim_values_rec.dim4_level_value_name;
2732 END IF;
2733 IF (l_org_Seq = 5) THEN
2734 x_dim_values_rec.org_level_value_id := p_dim_values_rec.dim5_level_Value_id;
2735 x_dim_values_rec.org_level_value_name := p_dim_values_rec.dim5_level_value_name;
2736 END IF;
2737 IF (l_org_Seq = 6) THEN
2738 x_dim_values_rec.org_level_value_id := p_dim_values_rec.dim6_level_Value_id;
2739 x_dim_values_rec.org_level_value_name := p_dim_values_rec.dim6_level_value_name;
2740 END IF;
2741 IF (l_org_Seq = 7) THEN
2742 x_dim_values_rec.org_level_value_id := p_dim_values_rec.dim7_level_Value_id;
2743 x_dim_values_rec.org_level_value_name := p_dim_values_rec.dim7_level_value_name;
2744 END IF;
2745 IF (l_time_seq = 1) THEN
2746 x_dim_values_rec.time_level_value_id := p_dim_values_rec.dim1_level_Value_id;
2747 x_dim_values_rec.time_level_value_name := p_dim_values_rec.dim1_level_value_name;
2748 END IF;
2749 IF (l_time_seq = 2) THEN
2750 x_dim_values_rec.time_level_value_id := p_dim_values_rec.dim2_level_Value_id;
2751 x_dim_values_rec.time_level_value_name := p_dim_values_rec.dim2_level_value_name;
2752 END IF;
2753 IF (l_time_seq = 3) THEN
2754 x_dim_values_rec.time_level_value_id := p_dim_values_rec.dim3_level_Value_id;
2755 x_dim_values_rec.time_level_value_name := p_dim_values_rec.dim3_level_value_name;
2756 END IF;
2757 IF (l_time_seq = 4) THEN
2758 x_dim_values_rec.time_level_value_id := p_dim_values_rec.dim4_level_Value_id;
2759 x_dim_values_rec.time_level_value_name := p_dim_values_rec.dim4_level_value_name;
2760 END IF;
2761 IF (l_time_seq = 5) THEN
2762 x_dim_values_rec.time_level_value_id := p_dim_values_rec.dim5_level_Value_id;
2763 x_dim_values_rec.time_level_value_name := p_dim_values_rec.dim5_level_value_name;
2764 END IF;
2765 IF (l_time_seq = 6) THEN
2766 x_dim_values_rec.time_level_value_id := p_dim_values_rec.dim6_level_Value_id;
2767 x_dim_values_rec.time_level_value_name := p_dim_values_rec.dim6_level_value_name;
2768 END IF;
2769 IF (l_time_seq = 7) THEN
2770 x_dim_values_rec.time_level_value_id := p_dim_values_rec.dim7_level_Value_id;
2771 x_dim_values_rec.time_level_value_name := p_dim_values_rec.dim7_level_value_name;
2772 END IF;
2773 END IF;
2774
2775 EXCEPTION
2776 WHEN OTHERS
2777 THEN NULL;
2778 END;
2779 --
2780 PROCEDURE resequence_dim_levels
2781 (p_dim_level_rec IN BIS_TARGET_LEVEL_PUB.TARGET_LEVEL_REC_TYPE
2782 ,p_sequence_dir IN VARCHAR2
2783 ,x_dim_level_rec IN OUT NOCOPY BIS_TARGET_LEVEL_PUB.TARGET_LEVEL_REC_TYPE
2784 ,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.ERROR_TBL_TYPE
2785 )
2786 IS
2787 CURSOR c_seq(p_targetlevel_id in NUMBER, p_dim IN VARCHAR2) IS
2788 SELECT x.sequence_no
2789 FROM bis_indicator_dimensions x, bis_dimensions y, bis_target_levels z
2790 WHERE x.dimension_id = y.dimension_id
2791 AND y.short_name like p_dim
2792 AND x.indicator_id = z.indicator_id
2793 AND z.target_level_id = p_targetlevel_id;
2794 l_org_Seq NUMBER;
2795 l_time_Seq NUMBER;
2796 BEGIN
2797 x_dim_level_rec := p_Dim_level_rec;
2798 IF (p_sequence_dir = 'N') THEN
2799 IF ((p_Dim_level_rec.org_level_id <> FND_API.G_MISS_NUM) OR
2800 (p_dim_level_Rec.time_level_id <> FND_API.G_MISS_NUM)) THEN
2801 OPEN c_seq(x_dim_level_rec.target_level_id
2802 , BIS_UTILITIES_PVT.GET_ORG_DIMENSION_NAME_TL(p_TargetLevelId => x_dim_level_rec.target_level_id
2803 ,p_TargetLevelName => NULL));
2804 FETCH c_seq INTO l_org_seq;
2805 CLOSE c_seq;
2806 OPEN c_seq(x_dim_level_Rec.target_level_id
2807 , BIS_UTILITIES_PVT.GET_TIME_DIMENSION_NAME_TL(p_TargetLevelId => x_dim_level_Rec.target_level_id
2808 ,p_TargetLevelName => NULL));
2809 FETCH c_seq INTO l_time_seq;
2810 CLOSE c_seq;
2811 x_dim_level_rec := p_dim_level_rec;
2812 IF (l_org_seq = 1) THEN
2813 x_dim_level_rec.dimension1_level_id:= p_dim_level_rec.org_level_id ;
2814 x_dim_level_rec.dimension1_level_name:= p_dim_level_rec.org_level_name ;
2815 x_dim_level_rec.dimension1_level_short_name:= p_dim_level_rec.org_level_short_name ;
2816 END IF;
2817 IF (l_org_seq = 2) THEN
2818 x_dim_level_rec.dimension2_level_id:= p_dim_level_rec.org_level_id ;
2819 x_dim_level_rec.dimension2_level_name:= p_dim_level_rec.org_level_name ;
2820 x_dim_level_rec.dimension2_level_short_name:= p_dim_level_rec.org_level_short_name ;
2821 END IF;
2822 IF (l_org_seq = 3) THEN
2823 x_dim_level_rec.dimension3_level_id:= p_dim_level_rec.org_level_id ;
2824 x_dim_level_rec.dimension3_level_name:= p_dim_level_rec.org_level_name ;
2825 -- meastmon 08/14/2001 Fix this typing error ---------------------------
2826 -- x_dim_level_rec.dimension4_level_short_name:= p_dim_level_rec.org_level_short_name ;
2827 x_dim_level_rec.dimension3_level_short_name:= p_dim_level_rec.org_level_short_name ;
2828 -- ---------------------------------------------------------------------------
2829 END IF;
2830 -- meastmon 08/14/2001 Add condition for l_org_seq = 4 -------------------
2831 IF (l_org_seq = 4) THEN
2832 x_dim_level_rec.dimension4_level_id:= p_dim_level_rec.org_level_id ;
2833 x_dim_level_rec.dimension4_level_name:= p_dim_level_rec.org_level_name ;
2834 x_dim_level_rec.dimension4_level_short_name:= p_dim_level_rec.org_level_short_name ;
2835 END IF;
2836 -- ---------------------------------------------------------------------------
2837 IF (l_org_seq = 5) THEN
2838 x_dim_level_rec.dimension5_level_id:= p_dim_level_rec.org_level_id ;
2839 x_dim_level_rec.dimension5_level_name:= p_dim_level_rec.org_level_name ;
2840 x_dim_level_rec.dimension5_level_short_name:= p_dim_level_rec.org_level_short_name ;
2841 END IF;
2842 IF (l_org_seq = 6) THEN
2843 x_dim_level_rec.dimension6_level_id:= p_dim_level_rec.org_level_id ;
2844 x_dim_level_rec.dimension6_level_name:= p_dim_level_rec.org_level_name ;
2845 x_dim_level_rec.dimension6_level_short_name:= p_dim_level_rec.org_level_short_name ;
2846 END IF;
2847 IF (l_org_seq = 7) THEN
2848 x_dim_level_rec.dimension7_level_id:= p_dim_level_rec.org_level_id ;
2849 x_dim_level_rec.dimension7_level_name:= p_dim_level_rec.org_level_name ;
2850 x_dim_level_rec.dimension7_level_short_name:= p_dim_level_rec.org_level_short_name ;
2851 END IF;
2852 IF (l_time_seq = 1) THEN
2853 x_dim_level_rec.dimension1_level_id:= p_dim_level_rec.time_level_id ;
2854 x_dim_level_Rec.dimension1_level_name:= p_dim_level_rec.time_level_name ;
2855 x_dim_level_rec.dimension1_level_short_name:= p_dim_level_rec.time_level_short_name ;
2856 END IF;
2857 IF (l_time_seq = 2) THEN
2858 x_dim_level_rec.dimension2_level_id:= p_dim_level_rec.time_level_id ;
2859 x_dim_level_Rec.dimension2_level_name:= p_dim_level_rec.time_level_name ;
2860 x_dim_level_rec.dimension2_level_short_name:= p_dim_level_rec.time_level_short_name ;
2861 END IF;
2862 IF (l_time_seq = 3) THEN
2863 x_dim_level_rec.dimension3_level_id:= p_dim_level_rec.time_level_id ;
2864 x_dim_level_Rec.dimension3_level_name:= p_dim_level_rec.time_level_name ;
2865 x_dim_level_rec.dimension3_level_short_name:= p_dim_level_rec.time_level_short_name ;
2866 END IF;
2867 IF (l_time_seq = 4) THEN
2868 x_dim_level_rec.dimension4_level_id:= p_dim_level_rec.time_level_id ;
2869 x_dim_level_Rec.dimension4_level_name:= p_dim_level_rec.time_level_name ;
2870 x_dim_level_rec.dimension4_level_short_name:= p_dim_level_rec.time_level_short_name ;
2871 END IF;
2872 IF (l_time_seq = 5) THEN
2873 x_dim_level_rec.dimension5_level_id:= p_dim_level_rec.time_level_id ;
2874 x_dim_level_Rec.dimension5_level_name:= p_dim_level_rec.time_level_name ;
2875 x_dim_level_rec.dimension5_level_short_name:= p_dim_level_rec.time_level_short_name ;
2876 END IF;
2877 IF (l_time_seq = 6) THEN
2878 x_dim_level_rec.dimension6_level_id:= p_dim_level_rec.time_level_id ;
2879 x_dim_level_Rec.dimension6_level_name:= p_dim_level_rec.time_level_name ;
2880 x_dim_level_rec.dimension6_level_short_name:= p_dim_level_rec.time_level_short_name ;
2881 END IF;
2882 IF (l_time_seq = 7) THEN
2883 x_dim_level_rec.dimension7_level_id:= p_dim_level_rec.time_level_id ;
2884 x_dim_level_Rec.dimension7_level_name:= p_dim_level_rec.time_level_name ;
2885 x_dim_level_rec.dimension7_level_short_name:= p_dim_level_rec.time_level_short_name ;
2886 END IF;
2887 END IF;
2888 END IF;
2889 IF (p_sequence_dir = 'R' ) THEN
2890 OPEN c_seq(x_dim_level_rec.target_level_id
2891 , BIS_UTILITIES_PVT.GET_ORG_DIMENSION_NAME_TL(p_TargetLevelId => x_dim_level_rec.target_level_id
2892 ,p_TargetLevelName => NULL));
2893 FETCH c_seq INTO l_org_seq;
2894 CLOSE c_seq;
2895 OPEN c_seq(x_dim_level_Rec.target_level_id
2896 , BIS_UTILITIES_PVT.GET_TIME_DIMENSION_NAME_TL(p_TargetLevelId => x_dim_level_Rec.target_level_id
2897 ,p_TargetLevelName => NULL));
2898 FETCH c_seq INTO l_time_seq;
2899 CLOSE c_seq;
2900 x_dim_level_rec := p_dim_level_rec;
2901 IF (l_org_seq = 1) THEN
2902 x_dim_level_rec.org_level_id := p_dim_level_rec.dimension1_level_id;
2903 x_dim_level_rec.org_level_name := p_dim_level_rec.dimension1_level_name;
2904 x_dim_level_rec.org_level_short_name := p_dim_level_rec.dimension1_level_short_name;
2905 END IF;
2906 IF (l_org_seq = 2) THEN
2907 x_dim_level_rec.org_level_id := p_dim_level_rec.dimension2_level_id;
2908 x_dim_level_rec.org_level_name := p_dim_level_rec.dimension2_level_name;
2909 x_dim_level_rec.org_level_short_name := p_dim_level_rec.dimension2_level_short_name;
2910 END IF;
2911 IF (l_org_seq = 3) THEN
2912 x_dim_level_rec.org_level_id := p_dim_level_rec.dimension3_level_id;
2913 x_dim_level_rec.org_level_name := p_dim_level_rec.dimension3_level_name;
2914 x_dim_level_rec.org_level_short_name := p_dim_level_rec.dimension3_level_short_name;
2915 END IF;
2916 IF (l_org_seq = 4) THEN
2917 x_dim_level_rec.org_level_id := p_dim_level_rec.dimension4_level_id;
2918 x_dim_level_rec.org_level_name := p_dim_level_rec.dimension4_level_name;
2919 x_dim_level_rec.org_level_short_name := p_dim_level_rec.dimension4_level_short_name;
2920 END IF;
2921 IF (l_org_seq = 5) THEN
2922 x_dim_level_rec.org_level_id := p_dim_level_rec.dimension5_level_id;
2923 x_dim_level_rec.org_level_name := p_dim_level_rec.dimension5_level_name;
2924 x_dim_level_rec.org_level_short_name := p_dim_level_rec.dimension5_level_short_name;
2925 END IF;
2926 IF (l_org_seq = 6) THEN
2927 x_dim_level_rec.org_level_id := p_dim_level_rec.dimension6_level_id;
2928 x_dim_level_rec.org_level_name := p_dim_level_rec.dimension6_level_name;
2929 x_dim_level_rec.org_level_short_name := p_dim_level_rec.dimension6_level_short_name;
2930 END IF;
2931 IF (l_org_seq = 7) THEN
2932 x_dim_level_rec.org_level_id := p_dim_level_rec.dimension7_level_id;
2933 x_dim_level_rec.org_level_name := p_dim_level_rec.dimension7_level_name;
2934 x_dim_level_rec.org_level_short_name := p_dim_level_rec.dimension7_level_short_name;
2935 END IF;
2936 IF (l_time_seq = 1) THEN
2937 x_dim_level_rec.time_level_id := p_dim_level_rec.dimension1_level_id;
2938 x_dim_level_Rec.time_level_name := p_dim_level_rec.dimension1_level_name;
2939 x_dim_level_rec.time_level_short_name := p_dim_level_rec.dimension1_level_short_name;
2940 END IF;
2941 IF (l_time_seq = 2) THEN
2942 x_dim_level_rec.time_level_id := p_dim_level_rec.dimension2_level_id;
2943 x_dim_level_Rec.time_level_name := p_dim_level_rec.dimension2_level_name;
2944 x_dim_level_rec.time_level_short_name := p_dim_level_rec.dimension2_level_short_name;
2945 END IF;
2946 IF (l_time_seq = 3) THEN
2947 x_dim_level_rec.time_level_id := p_dim_level_rec.dimension3_level_id;
2948 x_dim_level_Rec.time_level_name := p_dim_level_rec.dimension3_level_name;
2949 x_dim_level_rec.time_level_short_name := p_dim_level_rec.dimension3_level_short_name;
2950 END IF;
2951 IF (l_time_seq = 4) THEN
2952 x_dim_level_rec.time_level_id := p_dim_level_rec.dimension4_level_id;
2953 x_dim_level_Rec.time_level_name := p_dim_level_rec.dimension4_level_name;
2954 x_dim_level_rec.time_level_short_name := p_dim_level_rec.dimension4_level_short_name;
2955 END IF;
2956 IF (l_time_seq = 5) THEN
2957 x_dim_level_rec.time_level_id := p_dim_level_rec.dimension5_level_id;
2958 x_dim_level_Rec.time_level_name := p_dim_level_rec.dimension5_level_name;
2959 x_dim_level_rec.time_level_short_name := p_dim_level_rec.dimension5_level_short_name;
2960 END IF;
2961 IF (l_time_seq = 6) THEN
2962 x_dim_level_rec.time_level_id := p_dim_level_rec.dimension6_level_id;
2963 x_dim_level_Rec.time_level_name := p_dim_level_rec.dimension6_level_name;
2964 x_dim_level_rec.time_level_short_name := p_dim_level_rec.dimension6_level_short_name;
2965 END IF;
2966 IF (l_time_seq = 7) THEN
2967 x_dim_level_rec.time_level_id := p_dim_level_rec.dimension7_level_id;
2968 x_dim_level_Rec.time_level_name := p_dim_level_rec.dimension7_level_name;
2969 x_dim_level_rec.time_level_short_name := p_dim_level_rec.dimension7_level_short_name;
2970 END IF;
2971 END IF;
2972 END;
2973 --
2974 PROCEDURE reseq_actual_dim_level_values
2975 (p_dim_values_Rec IN BIS_ACTUAL_PUB.Actual_rec_type
2976 ,p_Sequence_dir IN VARCHAR2
2977 ,x_dim_values_rec IN OUT NOCOPY BIS_ACTUAL_PUB.Actual_rec_type
2978 ,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.ERROR_TBL_TYPE
2979 )
2980 IS
2981 CURSOR c_seq(p_targetlevel_id in NUMBER, p_dim IN VARCHAR2) IS
2982 SELECT x.sequence_no
2983 FROM bis_indicator_dimensions x, bis_dimensions y, bis_target_levels z
2984 WHERE x.dimension_id = y.dimension_id
2985 AND y.short_name like p_dim
2986 AND x.indicator_id = z.indicator_id
2987 AND ((z.target_level_id = p_targetlevel_id and p_targetlevel_id is not null) OR
2988 (z.short_name = p_dim_values_rec.target_level_short_name
2989 and p_dim_values_rec.target_level_short_name IS NOT NULL))
2990 ;
2991 l_org_Seq NUMBER;
2992 l_time_Seq NUMBER;
2993 BEGIN
2994 x_dim_values_rec := p_Dim_values_rec;
2995 IF (p_sequence_dir = 'N') THEN
2996 IF ((BIS_UTILITIES_PVT.value_missing(p_dim_values_rec.org_level_value_id) = FND_API.G_FALSE ) OR
2997 (BIS_UTILITIES_PVT.value_missing(p_dim_values_rec.time_level_value_id) = FND_API.G_FALSE))
2998 THEN
2999 OPEN c_seq(p_dim_values_rec.target_level_id
3000 ,BIS_UTILITIES_PVT.GET_ORG_DIMENSION_NAME_TL(p_TargetLevelId => p_dim_values_rec.target_level_id
3001 , p_TargetLevelName => NULL));
3002 FETCH c_seq INTO l_org_seq;
3003 CLOSE c_seq;
3004 OPEN c_seq(p_dim_values_rec.target_level_id
3005 ,BIS_UTILITIES_PVT.GET_TIME_DIMENSION_NAME_TL(p_TargetLevelId => p_dim_values_rec.target_level_id
3006 ,p_TargetLevelName => NULL));
3007 FETCH c_seq INTO l_time_seq;
3008 CLOSE c_seq;
3009 x_dim_values_rec := p_dim_Values_rec;
3010 IF (l_org_Seq = 1) THEN
3011 x_dim_values_rec.dim1_level_Value_id:= p_dim_values_rec.org_level_value_id ;
3012 x_dim_values_rec.dim1_level_value_name:= p_dim_values_rec.org_level_value_name ;
3013 END IF;
3014 IF (l_org_Seq = 2) THEN
3015 x_dim_values_rec.dim2_level_Value_id:= p_dim_values_rec.org_level_value_id ;
3016 x_dim_values_rec.dim2_level_value_name:= p_dim_values_rec.org_level_value_name ;
3017 END IF;
3018 IF (l_org_Seq = 3) THEN
3019 x_dim_values_rec.dim3_level_Value_id:= p_dim_values_rec.org_level_value_id ;
3020 x_dim_values_rec.dim3_level_value_name:= p_dim_values_rec.org_level_value_name ;
3021 END IF;
3022 IF (l_org_Seq = 4) THEN
3023 x_dim_values_rec.dim4_level_Value_id:= p_dim_values_rec.org_level_value_id ;
3024 x_dim_values_rec.dim4_level_value_name:= p_dim_values_rec.org_level_value_name ;
3025 END IF;
3026 IF (l_org_Seq = 5) THEN
3027 x_dim_values_rec.dim5_level_Value_id:= p_dim_values_rec.org_level_value_id ;
3028 x_dim_values_rec.dim5_level_value_name:= p_dim_values_rec.org_level_value_name ;
3029 END IF;
3030 IF (l_org_Seq = 6) THEN
3031 x_dim_values_rec.dim6_level_Value_id:= p_dim_values_rec.org_level_value_id ;
3032 x_dim_values_rec.dim6_level_value_name:= p_dim_values_rec.org_level_value_name ;
3033 END IF;
3034 IF (l_org_Seq = 7) THEN
3035 x_dim_values_rec.dim7_level_Value_id:= p_dim_values_rec.org_level_value_id ;
3036 x_dim_values_rec.dim7_level_value_name:= p_dim_values_rec.org_level_value_name ;
3037 END IF;
3038 IF (l_time_seq = 1) THEN
3039 x_dim_values_rec.dim1_level_Value_id:= p_dim_values_rec.time_level_value_id ;
3040 x_dim_values_rec.dim1_level_value_name:= p_dim_values_rec.time_level_value_name ;
3041 END IF;
3042 IF (l_time_seq = 2) THEN
3043 x_dim_values_rec.dim2_level_Value_id:= p_dim_values_rec.time_level_value_id ;
3044 x_dim_values_rec.dim2_level_value_name:= p_dim_values_rec.time_level_value_name ;
3045 END IF;
3046 IF (l_time_seq = 3) THEN
3047 x_dim_values_rec.dim3_level_Value_id:= p_dim_values_rec.time_level_value_id ;
3048 x_dim_values_rec.dim3_level_value_name:= p_dim_values_rec.time_level_value_name ;
3049 END IF;
3050 IF (l_time_seq = 4) THEN
3051 x_dim_values_rec.dim4_level_Value_id:= p_dim_values_rec.time_level_value_id ;
3052 x_dim_values_rec.dim4_level_value_name:= p_dim_values_rec.time_level_value_name ;
3053 END IF;
3054 IF (l_time_seq = 5) THEN
3055 x_dim_values_rec.dim5_level_Value_id:= p_dim_values_rec.time_level_value_id ;
3056 x_dim_values_rec.dim5_level_value_name:= p_dim_values_rec.time_level_value_name ;
3057 END IF;
3058 IF (l_time_seq = 6) THEN
3059 x_dim_values_rec.dim6_level_Value_id:= p_dim_values_rec.time_level_value_id ;
3060 x_dim_values_rec.dim6_level_value_name:= p_dim_values_rec.time_level_value_name ;
3061 END IF;
3062 IF (l_time_seq = 7) THEN
3063 x_dim_values_rec.dim7_level_Value_id:= p_dim_values_rec.time_level_value_id ;
3064 x_dim_values_rec.dim7_level_value_name:= p_dim_values_rec.time_level_value_name ;
3065 END IF;
3066 END IF;
3067 END IF;
3068 EXCEPTION
3069 WHEN OTHERS THEN
3070 NULL;
3071 END;
3072 --
3073 -- mdamle 01/12/2001 - Resequence Indicator record
3074 PROCEDURE reseq_ind_dim_level_values
3075 (p_dim_values_Rec IN BIS_INDICATOR_REGION_PUB.Indicator_Region_Rec_Type
3076 ,p_Sequence_dir IN VARCHAR2
3077 ,x_dim_values_rec IN OUT NOCOPY BIS_INDICATOR_REGION_PUB.Indicator_Region_Rec_Type
3078 ,x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.ERROR_TBL_TYPE
3079 )
3080 IS
3081 CURSOR c_seq(p_targetlevel_id in NUMBER, p_dim IN VARCHAR2) IS
3082 SELECT x.sequence_no
3083 FROM bis_indicator_dimensions x, bis_dimensions y, bis_target_levels z
3084 WHERE x.dimension_id = y.dimension_id
3085 AND y.short_name like p_dim
3086 AND x.indicator_id = z.indicator_id
3087 AND ((z.target_level_id = p_targetlevel_id and p_targetlevel_id is not null) OR
3088 (z.short_name = p_dim_values_rec.target_level_short_name
3089 and p_dim_values_rec.target_level_short_name IS NOT NULL))
3090 ;
3091 l_org_Seq NUMBER;
3092 -- l_time_Seq NUMBER;
3093 BEGIN
3094 x_dim_values_rec := p_Dim_values_rec;
3095 IF (p_sequence_dir = 'N') THEN
3096
3097 IF ((BIS_UTILITIES_PVT.value_missing(p_dim_values_rec.org_level_value_id) = FND_API.G_FALSE ))
3098 -- OR (BIS_UTILITIES_PVT.value_missing(p_dim_values_rec.time_level_value_id) = FND_API.G_FALSE))
3099 THEN
3100 OPEN c_seq(p_dim_values_rec.target_level_id
3101 ,BIS_UTILITIES_PVT.GET_ORG_DIMENSION_NAME_TL(p_TargetLevelId => p_dim_values_rec.target_level_id
3102 ,p_TargetLevelName => NULL));
3103 FETCH c_seq INTO l_org_seq;
3104 CLOSE c_seq;
3105 -- OPEN c_seq(p_dim_values_rec.target_level_id
3106 -- ,BIS_UTILITIES_PVT.GET_TIME_DIMENSION_NAME_TL(p_TargetLevelId => p_dim_values_rec.target_level_i,p_TargetLevelName => NULL));
3107 -- FETCH c_seq INTO l_time_seq;
3108 -- CLOSE c_seq;
3109 x_dim_values_rec := p_dim_Values_rec;
3110 IF (l_org_Seq = 1) THEN
3111 x_dim_values_rec.dim1_level_Value_id:= p_dim_values_rec.org_level_value_id ;
3112 x_dim_values_rec.dim1_level_value_name:= p_dim_values_rec.org_level_value_name ;
3113 END IF;
3114 IF (l_org_Seq = 2) THEN
3115 x_dim_values_rec.dim2_level_Value_id:= p_dim_values_rec.org_level_value_id ;
3116 x_dim_values_rec.dim2_level_value_name:= p_dim_values_rec.org_level_value_name ;
3117 END IF;
3118 IF (l_org_Seq = 3) THEN
3119 x_dim_values_rec.dim3_level_Value_id:= p_dim_values_rec.org_level_value_id ;
3120 x_dim_values_rec.dim3_level_value_name:= p_dim_values_rec.org_level_value_name ;
3121 END IF;
3122 IF (l_org_Seq = 4) THEN
3123 x_dim_values_rec.dim4_level_Value_id:= p_dim_values_rec.org_level_value_id ;
3124 x_dim_values_rec.dim4_level_value_name:= p_dim_values_rec.org_level_value_name ;
3125 END IF;
3126 IF (l_org_Seq = 5) THEN
3127 x_dim_values_rec.dim5_level_Value_id:= p_dim_values_rec.org_level_value_id ;
3128 x_dim_values_rec.dim5_level_value_name:= p_dim_values_rec.org_level_value_name ;
3129 END IF;
3130 IF (l_org_Seq = 6) THEN
3131 x_dim_values_rec.dim6_level_Value_id:= p_dim_values_rec.org_level_value_id ;
3132 x_dim_values_rec.dim6_level_value_name:= p_dim_values_rec.org_level_value_name ;
3133 END IF;
3134 IF (l_org_Seq = 7) THEN
3135 x_dim_values_rec.dim7_level_Value_id:= p_dim_values_rec.org_level_value_id ;
3136 x_dim_values_rec.dim7_level_value_name:= p_dim_values_rec.org_level_value_name ;
3137 END IF;
3138 END IF;
3139 END IF;
3140 -- mdamle 06/29/2001 - Bug#1842840 - Added the 'R' condition
3141 IF (p_sequence_dir = 'R' ) THEN
3142 OPEN c_seq(x_dim_values_rec.target_level_id
3143 , BIS_UTILITIES_PVT.GET_ORG_DIMENSION_NAME_TL(p_TargetLevelId => x_dim_values_rec.target_level_id
3144 ,p_TargetLevelName => NULL));
3145 FETCH c_seq INTO l_org_seq;
3146 CLOSE c_seq;
3147 x_dim_values_rec := p_dim_values_rec;
3148 IF (l_org_seq = 1) THEN
3149 x_dim_values_rec.org_level_value_id := p_dim_values_rec.dim1_level_value_id;
3150 x_dim_values_rec.org_level_value_name := p_dim_values_rec.dim1_level_value_name;
3151 END IF;
3152 IF (l_org_seq = 2) THEN
3153 x_dim_values_rec.org_level_value_id := p_dim_values_rec.dim2_level_value_id;
3154 x_dim_values_rec.org_level_value_name := p_dim_values_rec.dim2_level_value_name;
3155 END IF;
3156 IF (l_org_seq = 3) THEN
3157 x_dim_values_rec.org_level_value_id := p_dim_values_rec.dim3_level_value_id;
3158 x_dim_values_rec.org_level_value_name := p_dim_values_rec.dim3_level_value_name;
3159 END IF;
3160 -- meastmon 08/14/2001 Add condition for l_org_seq = 4 -------------------
3161 IF (l_org_seq = 4) THEN
3162 x_dim_values_rec.org_level_value_id := p_dim_values_rec.dim4_level_value_id;
3163 x_dim_values_rec.org_level_value_name := p_dim_values_rec.dim4_level_value_name;
3164 END IF;
3165 -- ----------------------------------------------------------------------
3166 IF (l_org_seq = 5) THEN
3167 x_dim_values_rec.org_level_value_id := p_dim_values_rec.dim5_level_value_id;
3168 x_dim_values_rec.org_level_value_name := p_dim_values_rec.dim5_level_value_name;
3169 END IF;
3170 IF (l_org_seq = 6) THEN
3171 x_dim_values_rec.org_level_value_id := p_dim_values_rec.dim6_level_value_id;
3172 x_dim_values_rec.org_level_value_name := p_dim_values_rec.dim6_level_value_name;
3173 END IF;
3174 IF (l_org_seq = 7) THEN
3175 x_dim_values_rec.org_level_value_id := p_dim_values_rec.dim7_level_value_id;
3176 x_dim_values_rec.org_level_value_name := p_dim_values_rec.dim7_level_value_name;
3177 END IF;
3178 END IF;
3179
3180
3181 EXCEPTION
3182 WHEN OTHERS THEN
3183 NULL;
3184 END;
3185 --
3186 FUNCTION GET_TIME_DIMENSION_NAME
3187 (p_DimLevelId IN NUMBER := NULL
3188 ,p_DimLevelName IN VARCHAR2 := NULL
3189 )
3190 RETURN VARCHAR2
3191 IS
3192 ------------------------
3193 CURSOR c_dim_id IS
3194 SELECT source
3195 FROM bis_levels
3196 WHERE level_id = p_DimLevelId
3197 ;
3198
3199 CURSOR c_dim_name IS
3200 SELECT source
3201 FROM bis_levels
3202 WHERE short_name = p_DimLevelName
3203 ;
3204 ---------------------------
3205 l_source VARCHAR2(32000);
3206 BEGIN
3207 -----------------
3208 if (p_DimLevelId is NOT NULL)
3209 then
3210 OPEN c_dim_id;
3211 FETCH c_dim_id INTO l_Source;
3212 CLOSE c_dim_id;
3213 else
3214 OPEN c_dim_name;
3215 FETCH c_dim_name INTO l_Source;
3216 CLOSE c_dim_name;
3217 end if;
3218 -----------------
3219
3220 -- l_source := FND_PROFILE.value('BIS_SOURCE');
3221 IF (l_source = 'EDW')
3222 THEN
3223 RETURN 'EDW_TIME_M';
3224 END IF;
3225 IF (l_source = 'OLTP')
3226 THEN
3227 RETURN 'TIME';
3228 END IF;
3229
3230 END GET_TIME_DIMENSION_NAME;
3231 --
3232 FUNCTION GET_SOURCE_FROM_DIM_LEVEL
3233 (p_DimLevelId IN NUMBER := NULL
3234 ,p_DimLevelShortName IN VARCHAR2 := NULL
3235 )
3236 RETURN VARCHAR2
3237 IS
3238 ------------------------
3239 CURSOR c_dim_id IS
3240 SELECT source
3241 FROM bis_levels
3242 WHERE level_id = p_DimLevelId
3243 ;
3244
3245 CURSOR c_dim_name IS
3246 SELECT source
3247 FROM bis_levels
3248 WHERE short_name = p_DimLevelShortName
3249 ;
3250 ---------------------------
3251 l_source VARCHAR2(32000);
3252 BEGIN
3253 -----------------
3254 if (p_DimLevelId is NOT NULL)
3255 then
3256 OPEN c_dim_id;
3257 FETCH c_dim_id INTO l_Source;
3258 CLOSE c_dim_id;
3259 else
3260 OPEN c_dim_name;
3261 FETCH c_dim_name INTO l_Source;
3262 CLOSE c_dim_name;
3263 end if;
3264 -----------------
3265
3266 RETURN l_source;
3267
3268 EXCEPTION
3269 when others then
3270 BIS_UTILITIES_PUB.put_line(p_text => ' GET_SOURCE_FROM_DIM_LEVEL Error 0100 ' || sqlerrm ) ;
3271 END GET_SOURCE_FROM_DIM_LEVEL;
3272
3273 --
3274
3275 FUNCTION GET_ORG_DIMENSION_NAME
3276 (p_DimLevelId IN NUMBER := NULL
3277 ,p_DimLevelName IN VARCHAR2 := NULL
3278 )
3279 RETURN VARCHAR2
3280 IS
3281 ------------------------
3282 CURSOR c_dim_id IS
3283 SELECT source
3284 FROM bis_levels
3285 WHERE level_id = p_DimLevelId
3286 ;
3287
3288 CURSOR c_dim_name IS
3289 SELECT source
3290 FROM bis_levels
3291 WHERE short_name = p_DimLevelName
3292 ;
3293 ---------------------------
3294 l_source VARCHAR2(32000);
3295 BEGIN
3296
3297 -----------------
3298 if (p_DimLevelId is NOT NULL)
3299 then
3300 OPEN c_dim_id;
3301 FETCH c_dim_id INTO l_Source;
3302 CLOSE c_dim_id;
3303 else
3304 OPEN c_dim_name;
3305 FETCH c_dim_name INTO l_Source;
3306 CLOSE c_dim_name;
3307 end if;
3308 -----------------
3309 --l_source := FND_PROFILE.value('BIS_SOURCE');
3310 IF (l_source = 'EDW')
3311 THEN
3312 RETURN 'EDW_ORGANIZATION_M';
3313 END IF;
3314 IF (l_Source = 'OLTP')
3315 THEN
3316 RETURN 'ORGANIZATION';
3317 END IF;
3318
3319
3320 END GET_ORG_DIMENSION_NAME;
3321 --
3322 FUNCTION GET_INV_LOC_DIMENSION_NAME -- 2525408
3323 (p_DimLevelId IN NUMBER := NULL
3324 ,p_DimLevelName IN VARCHAR2 := NULL
3325 )
3326 RETURN VARCHAR2
3327 IS
3328 CURSOR c_dim_id IS
3329 SELECT source
3330 FROM bis_levels
3331 WHERE level_id = p_DimLevelId;
3332
3333 CURSOR c_dim_name IS
3334 SELECT source
3335 FROM bis_levels
3336 WHERE short_name = p_DimLevelName;
3337
3338 l_source VARCHAR2(32000);
3339 l_dim_name VARCHAR2(80);
3340
3341 BEGIN
3342 IF (p_DimLevelId is NOT NULL) THEN
3343 IF ( c_dim_id%ISOPEN ) THEN
3344 CLOSE c_dim_id;
3345 END IF;
3346 OPEN c_dim_id;
3347 FETCH c_dim_id INTO l_Source;
3348 CLOSE c_dim_id;
3349 ELSE
3350 IF ( c_dim_name%ISOPEN ) THEN
3351 CLOSE c_dim_id;
3352 END IF;
3353 OPEN c_dim_name;
3354 FETCH c_dim_name INTO l_Source;
3355 CLOSE c_dim_name;
3356 END IF;
3357 l_dim_name := BIS_UTILITIES_PVT.GET_INV_LOC_DIMENSION_NAME_SRC (p_source => l_source);
3358 RETURN l_dim_name;
3359
3360 EXCEPTION
3361 WHEN OTHERS THEN
3362 IF ( c_dim_id%ISOPEN ) THEN
3363 CLOSE c_dim_id;
3364 END IF;
3365 IF ( c_dim_name%ISOPEN ) THEN
3366 CLOSE c_dim_id;
3367 END IF;
3368 END GET_INV_LOC_DIMENSION_NAME;
3369 --
3370 FUNCTION GET_TIME_DIMENSION_NAME_TL
3371 (p_TargetLevelId IN NUMBER := NULL
3372 ,p_TargetLevelName IN VARCHAR2 := NULL
3373 )
3374 RETURN VARCHAR2
3375 IS
3376 ------------------------
3377 CURSOR c_dim_id IS
3378 SELECT source
3379 FROM bis_target_levels
3380 WHERE target_level_id = p_TargetLevelId
3381 ;
3382
3383 CURSOR c_dim_name IS
3384 SELECT source
3385 FROM bis_target_levels
3386 WHERE short_name = p_TargetLevelName
3387 ;
3388 ---------------------------
3389 l_source VARCHAR2(32000);
3390 BEGIN
3391 -----------------
3392 if (p_TargetLevelId is NOT NULL)
3393 then
3394 OPEN c_dim_id;
3395 FETCH c_dim_id INTO l_Source;
3396 CLOSE c_dim_id;
3397 else
3398 OPEN c_dim_name;
3399 FETCH c_dim_name INTO l_Source;
3400 CLOSE c_dim_name;
3401 end if;
3402 -----------------
3403
3404 -- l_source := FND_PROFILE.value('BIS_SOURCE');
3405 IF (l_source = 'EDW')
3406 THEN
3407 RETURN 'EDW_TIME_M';
3408 END IF;
3409 IF (l_source = 'OLTP')
3410 THEN
3411 RETURN 'TIME';
3412 END IF;
3413
3414 END GET_TIME_DIMENSION_NAME_TL;
3415 --
3416 FUNCTION GET_ORG_DIMENSION_NAME_TL
3417 (p_TargetLevelId IN NUMBER := NULL
3418 ,p_TargetLevelName IN VARCHAR2 := NULL
3419 )
3420 RETURN VARCHAR2
3421 IS
3422 l_dim_short_name VARCHAR2(80);
3423 l_source VARCHAR2(32000);
3424 l_return_status VARCHAR2(80);
3425 l_return_msg VARCHAR2(32000);
3426
3427 ------------------------
3428 CURSOR c_dim_id IS
3429 SELECT source
3430 FROM bis_target_levels
3431 WHERE target_level_id = p_TargetLevelId
3432 ;
3433
3434 CURSOR c_dim_name IS
3435 SELECT source
3436 FROM bis_target_levels
3437 WHERE short_name = p_TargetLevelName
3438 ;
3439 ---------------------------
3440
3441 BEGIN
3442
3443 -----------------
3444 if (p_TargetLevelId is NOT NULL)
3445 then
3446 OPEN c_dim_id;
3447 FETCH c_dim_id INTO l_Source;
3448 CLOSE c_dim_id;
3449 else
3450 OPEN c_dim_name;
3451 FETCH c_dim_name INTO l_Source;
3452 CLOSE c_dim_name;
3453 end if;
3454 -----------------
3455 --l_source := FND_PROFILE.value('BIS_SOURCE');
3456
3457 IF (l_source = 'EDW')
3458 THEN
3459 get_org_dim_name_tl_edw
3460 ( p_tgt_lvl_short_name => p_TargetLevelName,
3461 p_tgt_lvl_ID => p_TargetLevelId,
3462 x_dimension_short_name => l_dim_short_name,
3463 x_return_status => l_return_status,
3464 x_return_msg => l_return_msg);
3465
3466 -- BIS_UTILITIES_PUB.put_line(p_text => ' l_dim_short_name = ' || l_dim_short_name ) ;
3467 RETURN l_dim_short_name ; -- 'EDW_ORGANIZATION_M';
3468
3469 END IF;
3470 IF (l_Source = 'OLTP')
3471 THEN
3472 RETURN 'ORGANIZATION';
3473 END IF;
3474
3475
3476 END GET_ORG_DIMENSION_NAME_TL;
3477 --
3478
3479 PROCEDURE get_org_dim_name_tl_edw
3480 ( p_tgt_lvl_short_name IN VARCHAR2,
3481 p_tgt_lvl_ID IN NUMBER,
3482 x_dimension_short_name OUT NOCOPY VARCHAR2,
3483 x_return_status OUT NOCOPY VARCHAR2,
3484 x_return_msg OUT NOCOPY VARCHAR2) IS
3485
3486 l_tgt_lvl_short_name VARCHAR2(80);
3487 l_err_track NUMBER := 0;
3488 l_level_short_name VARCHAR2(80);
3489 l_dim_short_name VARCHAR2(80);
3490
3491 BEGIN
3492
3493 IF p_tgt_lvl_short_name IS NULL THEN
3494
3495 SELECT short_name
3496 INTO l_tgt_lvl_short_name
3497 FROM bis_target_levels
3498 WHERE target_level_id = p_tgt_lvl_ID;
3499
3500 ELSE
3501
3502 l_tgt_lvl_short_name := p_tgt_lvl_short_name;
3503
3504 END IF;
3505
3506 begin
3507
3508 SELECT BL.short_name -- 2735844
3509 INTO l_level_short_name
3510 FROM bis_levels BL,
3511 bis_target_levels BTL
3512 WHERE BTL.short_name = l_tgt_lvl_short_name
3513 AND
3514 ( BL.level_id = BTL.dimension1_level_id
3515 OR BL.level_id = BTL.dimension2_level_id
3516 OR BL.level_id = BTL.dimension3_level_id
3517 OR BL.level_id = BTL.dimension4_level_id
3518 OR BL.level_id = BTL.dimension5_level_id
3519 OR BL.level_id = BTL.dimension6_level_id
3520 OR BL.level_id = BTL.dimension7_level_id )
3521 AND BL.short_name -- 2735844
3522 IN ('EDW_MTL_ILDM_OU', 'EDW_MTL_ILDM_PLANT',
3523 'EDW_ORGA_OPER_UNIT', 'EDW_ORGA_ORG');
3524
3525 select short_name
3526 into l_dim_short_name
3527 from bis_dimensions where dimension_id in
3528 (select dimension_id from bis_levels where short_name = l_level_short_name);
3529
3530
3531 exception
3532 when others then
3533 l_dim_short_name := 'EDW_ORGANIZATION_M';
3534
3535 end;
3536
3537 x_dimension_short_name := l_dim_short_name;
3538
3539 l_err_track := 200;
3540
3541 EXCEPTION
3542 WHEN OTHERS THEN
3543 BIS_UTILITIES_PUB.put_line(p_text =>' Error in bisvutlb.get_org_dimension_name_tl_edw ' || l_err_track || sqlerrm);
3544 x_return_status := FND_API.G_RET_STS_ERROR;
3545 END get_org_dim_name_tl_edw;
3546
3547 --
3548
3549 FUNCTION GET_TIME_DIMENSION_NAME_SRC
3550 (p_source IN VARCHAR2
3551 )
3552 RETURN VARCHAR2
3553 IS
3554 BEGIN
3555 IF (p_source = 'EDW')
3556 THEN
3557 RETURN 'EDW_TIME_M';
3558 END IF;
3559 IF (p_Source = 'OLTP')
3560 THEN
3561 RETURN 'TIME';
3562 END IF;
3563 END GET_TIME_DIMENSION_NAME_SRC;
3564 --
3565 FUNCTION GET_ORG_DIMENSION_NAME_SRC
3566 (p_source IN VARCHAR2
3567 )
3568 RETURN VARCHAR2
3569 IS
3570 BEGIN
3571 IF (p_source = 'EDW')
3572 THEN
3573 RETURN 'EDW_ORGANIZATION_M';
3574 END IF;
3575 IF (p_Source = 'OLTP')
3576 THEN
3577 RETURN 'ORGANIZATION';
3578 END IF;
3579 END GET_ORG_DIMENSION_NAME_SRC;
3580 --
3581 FUNCTION GET_INV_LOC_DIMENSION_NAME_SRC -- 2525408
3582 (p_source IN VARCHAR2
3583 )
3584 RETURN VARCHAR2
3585 IS
3586 BEGIN
3587 IF ( p_source = 'EDW' ) THEN
3588 RETURN 'EDW_MTL_INVENTORY_LOC_M';
3589 END IF;
3590 IF ( p_Source = 'OLTP' ) THEN
3591 RETURN 'INVENTORY LOCATION';
3592 END IF;
3593 END GET_INV_LOC_DIMENSION_NAME_SRC;
3594 --
3595 FUNCTION IS_TOTAL_DIMLEVEL
3596 (p_dim_Level_short_name IN VARCHAR2
3597 , x_return_status OUT NOCOPY VARCHAR2
3598 )
3599 RETURN BOOLEAN
3600 IS
3601
3602 CURSOR c_dim_name IS
3603 SELECT source
3604 FROM bis_levels
3605 WHERE short_name = p_dim_Level_short_name
3606 ;
3607
3608 l_dimlevel_cut VARCHAR2(2000);
3609 l_source VARCHAR2(2000);
3610 l_length NUMBER;
3611 l_is_total BOOLEAN := FALSE;
3612
3613 BEGIN
3614
3615 OPEN c_dim_name;
3616 FETCH c_dim_name INTO l_Source;
3617 IF c_dim_name%NOTFOUND THEN
3618 CLOSE c_dim_name;
3619 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3620 RETURN FALSE;
3621 END IF;
3622
3623 -- found now
3624 CLOSE c_dim_name;
3625 l_length := length(p_dim_level_short_name);
3626
3627 IF (l_source = 'EDW') THEN
3628
3629 l_dimlevel_cut := substr(p_dim_level_short_name,(l_length-1),2 );
3630 --bug#2252888
3631 -- BIS_UTILITIES_PUB.put_line(p_text =>'Dimensional Cut : ' || l_dimlevel_cut );
3632
3633 if l_dimlevel_cut = '_A' then
3634
3635 l_is_total := TRUE;
3636
3637 end if;
3638
3639 ELSIF (l_source = 'OLTP') THEN
3640 IF ( l_length >= 5 ) THEN
3641
3642 l_dimlevel_cut := substr(p_dim_level_short_name,1, 5 );
3643
3644 IF l_dimlevel_cut = 'TOTAL' THEN
3645
3646 l_is_total := TRUE;
3647 END IF;
3648 END IF;
3649
3650 END IF;
3651
3652 RETURN l_is_total;
3653
3654 EXCEPTION
3655 when others then
3656 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3657
3658 BIS_UTILITIES_PVT.Add_Error_Message
3659 ( p_error_msg_id => SQLCODE
3660 , p_error_description => SQLERRM
3661 , p_error_proc_name => 'IS_TOTAL_DIMLEVEL'
3662 );
3663
3664 END IS_TOTAL_DIMLEVEL;
3665
3666
3667 FUNCTION GET_TOTAL_DIMLEVEL_NAME
3668 (p_dim_short_name IN VARCHAR2
3669 ,p_DimLevelId IN NUMBER := NULL
3670 ,p_DimLevelName IN VARCHAR2 := NULL
3671 )
3672 RETURN VARCHAR2
3673 IS
3674 ------------------------
3675 CURSOR c_dim_id IS
3676 SELECT source
3677 FROM bis_levels
3678 WHERE level_id = p_DimLevelId
3679 ;
3680
3681 CURSOR c_dim_name IS
3682 SELECT source
3683 FROM bis_levels
3684 WHERE short_name = p_DimLevelName
3685 ;
3686 ---------------------------
3687 l_total_name VARCHAR2(2000);
3688 l_source VARCHAR2(2000);
3689 BEGIN
3690 --This function just replaces or appends the characters to get the level short name
3691 -- for TOTAL level shortnames. It does not attempt to validate anything. This logic
3692 -- can be added if desired.
3693
3694 -----------------
3695 if (p_DimLevelId is NOT NULL)
3696 then
3697 OPEN c_dim_id;
3698 FETCH c_dim_id INTO l_Source;
3699 CLOSE c_dim_id;
3700 else
3701 OPEN c_dim_name;
3702 FETCH c_dim_name INTO l_Source;
3703 CLOSE c_dim_name;
3704 end if;
3705 -----------------
3706 -- 2617369, use newly created get_total_dimlevel_name_src
3707 l_total_name := BIS_UTILITIES_PVT.GET_TOTAL_DIMLEVEL_NAME_SRC(p_dim_short_name => p_dim_short_name
3708 ,p_source => l_source);
3709 RETURN l_total_name;
3710
3711 END GET_TOTAL_DIMLEVEL_NAME;
3712 --
3713
3714 FUNCTION GET_TOTAL_DIMLEVEL_NAME_TL
3715 (p_dim_short_name IN VARCHAR2
3716 ,p_TargetLevelId IN NUMBER := NULL
3717 ,p_TargetLevelName IN VARCHAR2 := NULL
3718 )
3719 RETURN VARCHAR2
3720 IS
3721 ------------------------
3722 CURSOR c_dim_id IS
3723 SELECT source
3724 FROM bis_target_levels
3725 WHERE target_level_id = p_TargetLevelId
3726 ;
3727
3728 CURSOR c_dim_name IS
3729 SELECT source
3730 FROM bis_target_levels
3731 WHERE short_name = p_TargetLevelName
3732 ;
3733 ---------------------------
3734 l_total_name VARCHAR2(2000);
3735 l_source VARCHAR2(2000);
3736 l_length NUMBER;
3737 BEGIN
3738 --This function just replaces or appends the characters to get the level short name
3739 -- for TOTAL level shortnames. It does not attempt to validate anything. This logic
3740 -- can be added if desired.
3741
3742 -----------------
3743 if (p_TargetLevelId is NOT NULL)
3744 then
3745 OPEN c_dim_id;
3746 FETCH c_dim_id INTO l_Source;
3747 CLOSE c_dim_id;
3748 else
3749 OPEN c_dim_name;
3750 FETCH c_dim_name INTO l_Source;
3751 CLOSE c_dim_name;
3752 end if;
3753 -----------------
3754
3755 -- l_source := FND_PROFILE.value('BIS_SOURCE');
3756 l_length := length(p_dim_short_name);
3757 IF (l_source = 'EDW')
3758 THEN
3759 l_total_name := substr(p_dim_short_name,1,(l_length-1) );
3760 l_total_name := l_total_name || 'A';
3761 END IF;
3762 IF (l_source = 'OLTP')
3763 THEN
3764 l_total_name := 'TOTAL_'||p_dim_short_name;
3765 END IF;
3766 RETURN l_total_name;
3767
3768 END GET_TOTAL_DIMLEVEL_NAME_TL;
3769
3770 --
3771 FUNCTION GET_TOTAL_DIMLEVEL_NAME_SRC -- 2617369
3772 (p_dim_short_name IN VARCHAR2
3773 ,p_source IN VARCHAR2
3774 )
3775 RETURN VARCHAR2
3776 IS
3777 l_total_name VARCHAR2(2000);
3778 l_length NUMBER;
3779
3780 BEGIN
3781
3782 l_length := length(p_dim_short_name);
3783 IF (p_source = 'EDW')
3784 THEN
3785 l_total_name := substr(p_dim_short_name,1,(l_length-1) );
3786 l_total_name := l_total_name || 'A';
3787 END IF;
3788 IF (p_source = 'OLTP')
3789 THEN
3790 l_total_name := 'TOTAL_'||p_dim_short_name;
3791 END IF;
3792 RETURN l_total_name;
3793
3794 END GET_TOTAL_DIMLEVEL_NAME_SRC;
3795 --
3796
3797 FUNCTION GET_TIME_SHORT_NAME
3798 (p_dim_level_id IN NUMBER
3799 )
3800 RETURN VARCHAR2
3801 IS
3802 l_time_level_short_name VARCHAR2(32000);
3803 BEGIN
3804
3805 SELECT SHORT_NAME
3806 INTO l_time_level_short_name
3807 FROM BIS_LEVELS
3808 WHERE LEVEL_ID = p_dim_level_id;
3809
3810 RETURN l_time_level_short_name;
3811
3812 END GET_TIME_SHORT_NAME;
3813
3814
3815
3816 FUNCTION GET_TIME_FROM
3817 ( p_duration IN NUMBER
3818 , p_table_name IN VARCHAR2
3819 , p_time IN VARCHAR2
3820 , p_id IN VARCHAR2
3821 , p_id_col_name IN VARCHAR2
3822 , p_value_col_name IN VARCHAR2
3823 , p_Org_Level_ID IN VARCHAR2
3824 , p_Org_Level_Short_name IN VARCHAR2
3825 , p_time_level_id IN NUMBER -- :=11, 99 on bis115dv -- 1740789 -- sashaik
3826 , p_time_level_sh_name IN VARCHAR2
3827 )
3828 RETURN VARCHAR2
3829 IS
3830 l_start_date DATE;
3831 l_end_date DATE;
3832 l_num NUMBER;
3833 l_date DATE;
3834 l_time_value VARCHAR2(240);
3835 v_value2 VARCHAR2(240);
3836 v_value3 VARCHAR2(240);
3837
3838 l_selectStmt VARCHAR2(32000);
3839
3840 l_source VARCHAR2(80);
3841
3842 l_star VARCHAR2(2) := '*';
3843
3844 TYPE tcursor IS REF CURSOR;
3845 l_cursor tcursor;
3846
3847 l_Org_Level_Id VARCHAR2(50) := null; -- 'XXX' ;
3848 l_Org_Level_Short_name VARCHAR2(50) := null; -- 'XXX' ;
3849 l_value VARCHAR2(240);
3850 l_min_start_date DATE;
3851 l_max_end_date DATE;
3852
3853
3854 BEGIN
3855
3856 -- BIS_UTILITIES_PUB.put_line(p_text => ' Inside get_time_from ' );
3857
3858 l_source := bis_utilities_pvt.GET_SOURCE_FROM_DIM_LEVEL
3859 (
3860 p_DimLevelId => p_time_level_id
3861 , p_DimLevelShortName => p_time_level_sh_name -- l_level_name
3862 );
3863
3864 BIS_UTILITIES_PUB.put_line(p_text =>' Source is '|| l_source );
3865
3866
3867 if ( l_source = 'OLTP' ) then
3868 bis_utilities_pvt.Get_Org_Info_Based_On_Source
3869 ( p_source => l_source,
3870 p_org_level_id => p_org_level_id,
3871 p_org_level_short_name => p_org_level_short_name,
3872 x_org_level_id => l_org_level_id,
3873 x_org_level_short_name => l_org_level_short_name
3874 );
3875 elsif ( l_source = 'EDW') then
3876 l_org_level_id := p_org_level_id;
3877 l_org_level_short_name := p_org_level_short_name;
3878 elsif ( l_source <> 'EDW') then
3879 BIS_UTILITIES_PUB.put_line(p_text => ' ERROR: GET_TIME_FROM : source can be only either OLTP or EDW ' );
3880 end if;
3881
3882
3883 -- bis_utilities_pvt.
3884 Get_Start_End_Dates
3885 ( p_source => l_source,
3886 p_view_name => p_table_name,
3887 p_id_col_name => p_id_col_name,
3888 p_id_value_name => p_id,
3889 p_org_level_id => l_org_level_id,
3890 p_org_level_short_name => l_org_level_short_name,
3891 x_start_date => l_start_date,
3892 x_end_date => l_end_date
3893 );
3894
3895
3896 l_num := l_end_date - l_start_date + 1;
3897
3898 l_num := l_num * p_duration;
3899
3900 l_date := l_start_date + l_num;
3901
3902
3903 -- bis_utilities_pvt.
3904 Get_Time_Level_Value
3905 ( p_source => l_source ,
3906 p_table_name => p_table_name,
3907 p_value_col_name => p_value_col_name,
3908 p_Org_Level_ID => l_Org_Level_ID,
3909 p_org_level_short_name => l_org_level_short_name,
3910 p_flag => 'BOTH',
3911 p_date => l_date,
3912 x_time_value => l_time_value
3913 );
3914
3915
3916 if ( l_time_value is null ) then
3917
3918 -- bis_utilities_pvt.
3919 Get_Min_Max_Start_End_Dates
3920 ( p_source => l_source,
3921 p_view_name => p_table_name,
3922 p_Org_Level_ID => l_Org_Level_ID,
3923 p_org_level_short_name => l_org_level_short_name,
3924 x_min_start_date => l_min_start_date,
3925 x_max_end_date => l_max_end_date
3926 );
3927
3928
3929 bis_utilities_pvt.Get_Time_Level_Value
3930 ( p_source => l_source,
3931 p_table_name => p_table_name,
3932 p_value_col_name => p_value_col_name,
3933 p_Org_Level_ID => l_Org_Level_ID,
3934 p_org_level_short_name => l_org_level_short_name,
3935 p_flag => 'START',
3936 p_date => l_min_start_date,
3937 x_time_value => l_time_value
3938 );
3939
3940 end if;
3941
3942
3943 -- v_from := l_time_value;
3944 BIS_UTILITIES_PUB.put_line(p_text => ' Time from = ' || l_time_value );
3945
3946 return l_time_value;
3947
3948
3949 Exception
3950 when others then
3951 BIS_UTILITIES_PUB.put_line(p_text =>'GET_TIME_FROM : SQL Statement is '|| l_selectStmt);
3952 BIS_UTILITIES_PUB.put_line(p_text =>'Error in Procedure BIS_UTILITIES_PVT.GET_TIME_FROM 1000 : '||sqlerrm);
3953
3954 END GET_TIME_FROM;
3955
3956
3957 FUNCTION GET_TIME_TO
3958 (p_duration IN NUMBER
3959 ,p_table_name IN VARCHAR2
3960 ,p_time IN VARCHAR2
3961 ,p_id IN VARCHAR2
3962 ,p_id_col_name IN VARCHAR2
3963 ,p_value_col_name IN VARCHAR2
3964 ,p_Org_Level_ID IN VARCHAR2
3965 ,p_Org_Level_Short_name IN VARCHAR2
3966 ,p_time_level_id IN NUMBER -- :=11, 99 on bis115dv -- 1740789 -- sashaik
3967 ,p_time_level_sh_name IN VARCHAR2
3968 )
3969 RETURN VARCHAR2
3970 IS
3971 l_start_date DATE;
3972 l_end_date DATE;
3973 l_num NUMBER;
3974 l_date DATE;
3975 l_value VARCHAR2(240);
3976 v_value2 VARCHAR2(240);
3977 v_value3 VARCHAR2(240);
3978
3979 l_time_value VARCHAR2(240);
3980
3981 l_selectStmt VARCHAR2(32000);
3982 v_Dummy INTEGER;
3983 v_to VARCHAR2(32000);
3984
3985 l_source VARCHAR2(80);
3986
3987 l_star VARCHAR2(2) := '*';
3988
3989 TYPE tcursor IS REF CURSOR;
3990 l_cursor tcursor;
3991
3992 l_Org_Level_Id VARCHAR2(50) := null; -- 'XXX' ;
3993 l_Org_Level_Short_name VARCHAR2(50) := null; -- 'XXX' ;
3994 l_min_start_date DATE;
3995 l_max_end_date DATE;
3996
3997 BEGIN
3998
3999 -- BIS_UTILITIES_PUB.put_line(p_text => ' Inside get_time_to ' );
4000
4001 -- BIS_UTILITIES_PUB.put_line(p_text => ' p_id ' || p_id ) ;
4002 -- BIS_UTILITIES_PUB.put_line(p_text => ' p_id_col_name ' || p_id_col_name ) ;
4003 -- BIS_UTILITIES_PUB.put_line(p_text => ' p_value_col_name ' || p_value_col_name ) ;
4004
4005
4006 l_source := bis_utilities_pvt.GET_SOURCE_FROM_DIM_LEVEL
4007 (
4008 p_DimLevelId => p_time_level_id
4009 , p_DimLevelShortName => p_time_level_sh_name -- l_level_name
4010 );
4011
4012
4013 if ( l_source = 'OLTP' ) then
4014 bis_utilities_pvt.Get_Org_Info_Based_On_Source
4015 ( p_source => l_source,
4016 p_org_level_id => p_org_level_id,
4017 p_org_level_short_name => p_org_level_short_name,
4018 x_org_level_id => l_org_level_id,
4019 x_org_level_short_name => l_org_level_short_name
4020 );
4021 elsif ( l_source = 'EDW') then
4022 l_org_level_id := p_org_level_id;
4023 l_org_level_short_name := p_org_level_short_name;
4024 elsif ( l_source <> 'EDW') then
4025 BIS_UTILITIES_PUB.put_line(p_text => ' ERROR: GET_TIME_TO : source can be only either OLTP or EDW ' );
4026 end if;
4027
4028 BIS_UTILITIES_PUB.put_line(p_text =>' Source is '|| l_source );
4029
4030
4031
4032 bis_utilities_pvt.Get_Start_End_Dates
4033 ( p_source => l_source,
4034 p_view_name => p_table_name,
4035 p_id_col_name => p_id_col_name,
4036 p_id_value_name => p_id,
4037 p_org_level_id => l_org_level_id,
4038 p_org_level_short_name => l_org_level_short_name,
4039 x_start_date => l_start_date,
4040 x_end_date => l_end_date
4041 );
4042
4043
4044 l_num := l_end_date - l_start_date + 1;
4045
4046 l_num := l_num * p_duration;
4047
4048 l_date := l_start_date + l_num;
4049
4050
4051 bis_utilities_pvt.Get_Time_Level_Value
4052 ( p_source => l_source ,
4053 p_table_name => p_table_name,
4054 p_value_col_name => p_value_col_name,
4055 p_Org_Level_ID => l_Org_Level_ID,
4056 p_org_level_short_name => l_org_level_short_name,
4057 p_flag => 'BOTH',
4058 p_date => l_date,
4059 x_time_value => l_time_value
4060 );
4061
4062
4063 if ( l_time_value is null ) then
4064
4065 bis_utilities_pvt.Get_Min_Max_Start_End_Dates
4066 ( p_source => l_source,
4067 p_view_name => p_table_name,
4068 p_Org_Level_ID => l_Org_Level_ID,
4069 p_org_level_short_name => l_org_level_short_name,
4070 x_min_start_date => l_min_start_date,
4071 x_max_end_date => l_max_end_date
4072 );
4073
4074
4075 bis_utilities_pvt.Get_Time_Level_Value
4076 ( p_source => l_source,
4077 p_table_name => p_table_name,
4078 p_value_col_name => p_value_col_name,
4079 p_Org_Level_ID => l_Org_Level_ID,
4080 p_org_level_short_name => l_org_level_short_name,
4081 p_flag => 'END',
4082 p_date => l_max_end_date,
4083 x_time_value => l_time_value
4084 );
4085
4086 end if;
4087
4088
4089 -- v_from := l_time_value;
4090 BIS_UTILITIES_PUB.put_line(p_text => ' Time to = ' || l_time_value );
4091
4092 return l_time_value;
4093
4094
4095 Exception
4096 when others then
4097 BIS_UTILITIES_PUB.put_line(p_text => ' l_selectStmt is : ' || l_selectStmt);
4098 BIS_UTILITIES_PUB.put_line(p_text =>'Error in Procedure BIS_UTILITIES_PVT.GET_TIME_TO : '||sqlerrm);
4099
4100 END GET_TIME_TO;
4101
4102
4103 --***************************************************************************************
4104 --***************************************************************************************
4105
4106
4107 Procedure Get_Org_Info_Based_On_Source -- what to do if org_id/short_name is missing.
4108 ( p_source IN varchar2,
4109 p_org_level_id IN varchar2,
4110 p_org_level_short_name IN varchar2,
4111 x_org_level_id OUT NOCOPY varchar2,
4112 x_org_level_short_name OUT NOCOPY varchar2
4113 )
4114 IS
4115
4116 l_org_level_id varchar2(80);
4117 l_org_level_short_name varchar2(80);
4118
4119 BEGIN
4120
4121 if ( p_source = 'OLTP' ) then
4122
4123 if ( bis_utilities_pub.value_not_missing ( p_org_level_id ) = FND_API.G_TRUE ) then
4124 l_Org_Level_Id := p_Org_Level_Id;
4125 else
4126 -- BIS_UTILITIES_PUB.put_line(p_text =>' org level id is missing ');
4127 l_Org_Level_Id := null;
4128 end if;
4129
4130 if ( bis_utilities_pub.value_not_missing ( p_org_level_short_name ) = FND_API.G_TRUE ) then
4131 l_org_level_short_name := p_org_level_short_name;
4132 else
4133 -- BIS_UTILITIES_PUB.put_line(p_text =>' org level short name is missing ');
4134 l_org_level_short_name := null;
4135 end if;
4136
4137 x_org_level_id := l_org_level_id;
4138 x_org_level_short_name := l_org_level_short_name;
4139
4140 end if;
4141
4142 Exception
4143
4144 when others then
4145 BIS_UTILITIES_PUB.put_line(p_text =>'Exception executing sql in Get_Org_Info_Based_On_Source 0100: '||sqlerrm);
4146
4147 END Get_Org_Info_Based_On_Source;
4148
4149
4150
4151 Procedure Get_Time_Level_Value_ID_Minus -- where (sysdate - p_sysdate_less) is between start and end dates..
4152 ( p_source IN varchar2,
4153 p_view_name IN varchar2,
4154 p_id_name IN varchar2,
4155 p_org_level_id IN varchar2,
4156 p_org_level_short_name IN varchar2,
4157 p_sysdate_less IN number,
4158 x_time_id OUT NOCOPY varchar2
4159 )
4160
4161 IS
4162
4163 l_date DATE;
4164 l_time_id VARCHAR2(32000) := NULL;
4165
4166 BEGIN
4167
4168
4169 l_date := trunc(sysdate) - p_sysdate_less;
4170
4171 Get_Time_Level_Value_ID_Date
4172 ( p_source => p_source,
4173 p_view_name => p_view_name,
4174 p_id_name => p_id_name,
4175 p_org_level_id => p_org_level_id,
4176 p_org_level_short_name => p_org_level_short_name,
4177 p_target_date => l_date,
4178 x_time_id => l_time_id
4179 );
4180
4181 x_time_id := l_time_id;
4182
4183 Exception
4184
4185 when others then
4186 BIS_UTILITIES_PUB.put_line(p_text =>'Exception executing sql in Get_Time_Level_Value_ID_Minus 0100: '||sqlerrm);
4187
4188 END Get_Time_Level_Value_ID_Minus;
4189
4190 Procedure Get_Time_Level_Value_ID_Date -- where target_date is between start and end dates..
4191 ( p_source IN varchar2, -- this and Get_Time_Level_Value_ID1 need to be combined.
4192 p_view_name IN varchar2,
4193 p_id_name IN varchar2,
4194 p_org_level_id IN varchar2,
4195 p_org_level_short_name IN varchar2,
4196 p_target_date IN date,
4197 x_time_id OUT NOCOPY varchar2
4198 )
4199 IS
4200 l_sql VARCHAR2(32000);
4201 l_time_id VARCHAR2(32000) := NULL;
4202 l_star VARCHAR2(2) := '*';
4203 l_star1 NUMBER := -9999;
4204
4205 --2684911
4206 CURSOR c_dim_lvl_sn( cp_lvl_values_view IN VARCHAR2) IS SELECT short_name FROM bis_levels WHERE
4207 level_values_view_name = cp_lvl_values_view ;
4208 l_short_name bis_levels.short_name%TYPE;
4209 l_time_lvl_dep_on_org NUMBER(3);
4210 l_is_dep_on_org BOOLEAN := FALSE;
4211
4212 TYPE tcursor IS REF CURSOR;
4213 l1_cursor tcursor;
4214 BEGIN
4215
4216 -- 2684911
4217 IF (c_dim_lvl_sn%ISOPEN) THEN
4218 CLOSE c_dim_lvl_sn;
4219 END IF;
4220
4221 OPEN c_dim_lvl_sn(cp_lvl_values_view => p_view_name ) ;
4222 FETCH c_dim_lvl_sn INTO l_short_name;
4223 CLOSE c_dim_lvl_sn;
4224
4225 l_time_lvl_dep_on_org := BIS_UTILITIES_PUB.is_time_dependent_on_org(p_time_lvl_short_name => l_short_name) ;
4226 IF (l_short_name IS NOT NULL AND l_time_lvl_dep_on_org = BIS_UTILITIES_PUB.G_TIME_IS_DEPEN_ON_ORG) THEN
4227 l_is_dep_on_org := TRUE;
4228 END IF;
4229
4230
4231 if p_source = 'EDW' then
4232 -- All rows in the view should have start/end date. If they don't, we want to avoid them.
4233 IF ( l_is_dep_on_org ) THEN -- 2684911
4234 l_sql := ' select ' || p_id_name
4235 || ' from ' || p_view_name
4236 || ' where :l_target_date between '
4237 || ' nvl(start_date, trunc(sysdate)+11) and nvl(end_date, trunc(sysdate)+10) '
4238 || ' and ' || p_id_name || ' not in (-1,0) '
4239 || ' and nvl(organization_id, :l_star1) = nvl(:p_Org_Level_ID, :l_star1) '
4240 || ' and nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) '
4241 || ' ORDER BY abs( nvl(trunc(start_date), trunc (sysdate)) - '
4242 || ' nvl(trunc(end_date), trunc(sysdate))) ';
4243 ELSE
4244 l_sql := ' select ' || p_id_name
4245 || ' from ' || p_view_name
4246 || ' where :l_target_date between '
4247 || ' nvl(start_date, trunc(sysdate)+11) and nvl(end_date, trunc(sysdate)+10) '
4248 || ' and ' || p_id_name || ' not in (-1,0) '
4249 || ' ORDER BY abs( nvl(trunc(start_date), trunc (sysdate)) - '
4250 || ' nvl(trunc(end_date), trunc(sysdate))) ';
4251 END IF;
4252 -- Query is supposed to return just one record. However we take the first one.
4253 BEGIN
4254
4255 IF (l_is_dep_on_org) THEN --2684911
4256 OPEN l1_cursor FOR l_sql using p_target_date , l_star1 ,p_Org_Level_ID, l_star1, l_star,p_Org_Level_Short_name , l_star ;
4257 FETCH l1_cursor INTO l_time_id;
4258 CLOSE l1_cursor;
4259 ELSE
4260 OPEN l1_cursor FOR l_sql using p_target_date;
4261 FETCH l1_cursor INTO l_time_id;
4262 CLOSE l1_cursor;
4263 END IF;
4264 EXCEPTION
4265 WHEN OTHERS THEN
4266 if l1_cursor%isopen then
4267 close l1_cursor;
4268 end if;
4269 BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_sql );
4270 BIS_UTILITIES_PUB.put_line(p_text =>'Exception executing sql in Get_Time_Level_Value_ID_Date 0100: '||sqlerrm);
4271 END;
4272 elsif p_source = 'OLTP' then
4273
4274 IF (l_is_dep_on_org) THEN --2684911
4275 l_sql := ' select distinct ' || p_id_name
4276 || ' from ' || p_view_name
4277 || ' where :l_target_date between '
4278 || ' nvl(start_date, trunc(sysdate)+11) and nvl(end_date, trunc(sysdate)+10) '
4279 || ' and nvl(organization_id, :l_star) = nvl(:p_Org_Level_ID, :l_star) '
4280 || ' and nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) ';
4281 ELSE
4282 l_sql := ' select distinct ' || p_id_name
4283 || ' from ' || p_view_name
4284 || ' where :l_target_date between '
4285 || ' nvl(start_date, trunc(sysdate)+11) and nvl(end_date, trunc(sysdate)+10) '
4286 || ' and rownum < 2';
4287 END IF;
4288
4289 BEGIN
4290 IF (l_is_dep_on_org) THEN --2684911
4291 EXECUTE IMMEDIATE l_sql INTO l_time_id
4292 using p_target_date, l_star, p_Org_Level_ID, l_star, l_star, p_Org_Level_Short_name, l_star;
4293 ELSE
4294 EXECUTE IMMEDIATE l_sql INTO l_time_id using p_target_date;
4295 END IF;
4296 EXCEPTION
4297 WHEN OTHERS THEN
4298 BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_sql );
4299 BIS_UTILITIES_PUB.put_line(p_text =>'Exception executing sql in Get_Time_Level_Value_ID_Date 0200: '||sqlerrm);
4300 END;
4301 end if;
4302 x_time_id := l_time_id;
4303 Exception
4304 when others then
4305 BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_sql );
4306 BIS_UTILITIES_PUB.put_line(p_text =>'Exception executing sql in Get_Time_Level_Value_ID_Date 0300: '||sqlerrm);
4307 IF (c_dim_lvl_sn%ISOPEN) THEN
4308 CLOSE c_dim_lvl_sn;
4309 END IF;
4310 END Get_Time_Level_Value_ID_Date;
4311
4312 Procedure Get_Time_Level_Value -- where p_date is between start and end dates.
4313 ( p_source IN varchar2,
4314 p_table_name IN varchar2,
4315 p_value_col_name IN varchar2,
4316 p_Org_Level_ID IN varchar2,
4317 p_org_level_short_name IN varchar2,
4318 p_flag IN varchar2,
4319 p_date IN date,
4320 x_time_value OUT NOCOPY varchar2
4321 )
4322 IS
4323
4324 l_selectStmt VARCHAR2(32000);
4325 l_time_value VARCHAR2(32000) := NULL;
4326 v_value2 VARCHAR2(3000) := NULL;
4327
4328 l_star VARCHAR2(2) := '*';
4329
4330 l_star1 NUMBER := -9999;
4331
4332 --2684911
4333 CURSOR c_dim_lvl_sn( cp_lvl_values_view IN VARCHAR2) IS SELECT short_name FROM bis_levels WHERE
4334 level_values_view_name = cp_lvl_values_view ;
4335 l_short_name bis_levels.short_name%TYPE;
4336 l_time_lvl_dep_on_org NUMBER(3);
4337 l_is_dep_on_org BOOLEAN := FALSE;
4338
4339
4340 TYPE tcursor IS REF CURSOR;
4341 l1_cursor tcursor;
4342
4343 BEGIN
4344
4345 -- 2684911
4346 IF (c_dim_lvl_sn%ISOPEN) THEN
4347 CLOSE c_dim_lvl_sn;
4348 END IF;
4349
4350 OPEN c_dim_lvl_sn(cp_lvl_values_view => p_table_name ) ;
4351 FETCH c_dim_lvl_sn INTO l_short_name;
4352 CLOSE c_dim_lvl_sn;
4353
4354 l_time_lvl_dep_on_org := BIS_UTILITIES_PUB.is_time_dependent_on_org(p_time_lvl_short_name => l_short_name) ;
4355 IF (l_short_name IS NOT NULL AND l_time_lvl_dep_on_org = BIS_UTILITIES_PUB.G_TIME_IS_DEPEN_ON_ORG) THEN
4356 l_is_dep_on_org := TRUE;
4357 END IF;
4358
4359 if p_source = 'EDW' then
4360
4361 l_selectStmt := ' SELECT ' || p_value_col_name ||
4362 ' FROM ' || p_table_name;
4363
4364 if (p_flag = 'BOTH') then
4365 l_selectStmt := l_selectStmt || ' WHERE END_DATE >= :l_date ' ||
4366 ' AND START_DATE <= :l_date ' ;
4367 elsif (p_flag = 'START') then
4368 l_selectStmt := l_selectStmt || ' WHERE START_DATE = :l_date ';
4369 elsif (p_flag = 'END') then
4370 l_selectStmt := l_selectStmt || ' WHERE END_DATE = :l_date ';
4371 else
4372 BIS_UTILITIES_PUB.put_line(p_text =>' Error Get_Time_Level_Value 0100: p_flag can be either START, END or BOTH, flag is ' || p_flag );
4373 end if;
4374
4375 IF (l_is_dep_on_org) THEN --2684911
4376 l_selectStmt := l_selectStmt ||
4377 ' AND nvl(organization_id, :l_star1) = nvl(:p_Org_Level_ID, :l_star1) ' ||
4378 ' AND nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) ';
4379 END IF;
4380
4381 l_selectStmt := l_selectStmt ||
4382 ' ORDER BY abs( nvl(trunc(end_date), trunc (sysdate)) - ' ||
4383 ' nvl(trunc(start_date), trunc(sysdate))) ';
4384
4385 BEGIN
4386
4387 if (p_flag = 'BOTH') then
4388 IF (l_is_dep_on_org) THEN
4389 OPEN l1_cursor FOR l_selectStmt
4390 using p_date, p_date,l_star1,p_Org_Level_ID,l_star1,l_star,p_Org_Level_Short_name,l_star;
4391 ELSE
4392 OPEN l1_cursor FOR l_selectStmt
4393 using p_date, p_date;
4394 END IF;
4395 else
4396 IF (l_is_dep_on_org) THEN --2684911
4397 OPEN l1_cursor FOR l_selectStmt
4398 using p_date ,l_star1,p_Org_Level_ID,l_star1,l_star,p_Org_Level_Short_name,l_star;
4399 ELSE
4400 OPEN l1_cursor FOR l_selectStmt
4401 using p_date;
4402 END IF;
4403 end if;
4404
4405 FETCH l1_cursor INTO v_value2;
4406 CLOSE l1_cursor;
4407
4408
4409 if v_value2 is null then
4410 BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_selectStmt );
4411 BIS_UTILITIES_PUB.put_line(p_text =>' Date is ' || p_date );
4412 end if;
4413
4414
4415 EXCEPTION
4416
4417 WHEN OTHERS THEN
4418 if l1_cursor%isopen then
4419 close l1_cursor;
4420 end if;
4421
4422 v_value2 := null;
4423
4424 BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_selectStmt );
4425 BIS_UTILITIES_PUB.put_line(p_text =>'Exception executing sql in Get_Time_Level_Value 0200: '||sqlerrm);
4426
4427 END;
4428
4429
4430 else -- if source = 'OLTP'
4431
4432 l_selectStmt := ' SELECT ' || p_value_col_name ||
4433 ' FROM ' || p_table_name;
4434
4435 if (p_flag = 'BOTH') then
4436 l_selectStmt := l_selectStmt || ' WHERE END_DATE >= :l_date ' ||
4437 ' AND START_DATE <= :l_date ' ;
4438 elsif (p_flag = 'START') then
4439 l_selectStmt := l_selectStmt || ' WHERE START_DATE = :l_date ';
4440 elsif (p_flag = 'END') then
4441 l_selectStmt := l_selectStmt || ' WHERE END_DATE = :l_date ';
4442 else
4443 BIS_UTILITIES_PUB.put_line(p_text =>' Error Get_Time_Level_Value: p_flag can be either START, END or BOTH, flag is ' || p_flag );
4444 end if;
4445
4446 IF (l_is_dep_on_org) THEN --2684911
4447 l_selectStmt := l_selectStmt ||
4448 ' AND nvl(organization_id, :l_star) = nvl(:p_Org_Level_ID, :l_star) ' ||
4449 ' AND nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) ';
4450
4451 ELSE
4452 l_selectStmt := l_selectStmt || ' AND rownum < 2 '; -- take the first row
4453 END IF;
4454
4455 Begin
4456
4457
4458 if (p_flag = 'BOTH') then
4459 IF (l_is_dep_on_org) THEN --2684911
4460 EXECUTE IMMEDIATE l_selectStmt INTO v_value2
4461 USING p_date, p_date, l_star, p_Org_Level_ID, l_star, l_star, p_Org_Level_Short_name, l_star;
4462 ELSE
4463 EXECUTE IMMEDIATE l_selectStmt INTO v_value2 USING p_date, p_date;
4464 END IF;
4465 else
4466 IF (l_is_dep_on_org) THEN --2684911
4467 EXECUTE IMMEDIATE l_selectStmt INTO v_value2
4468 USING p_date, l_star, p_Org_Level_ID, l_star, l_star, p_Org_Level_Short_name, l_star;
4469 ELSE
4470 EXECUTE IMMEDIATE l_selectStmt INTO v_value2 USING p_date;
4471 END IF;
4472 end if;
4473
4474 Exception
4475 when others then
4476 v_value2 := null;
4477
4478 -- BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_selectStmt );
4479 -- BIS_UTILITIES_PUB.put_line(p_text => ' l_date = ' || p_date );
4480 -- BIS_UTILITIES_PUB.put_line(p_text => ' flag = ' || p_flag );
4481 -- BIS_UTILITIES_PUB.put_line(p_text =>'Exception executing sql in Get_Time_Level_Value 0300: '||sqlerrm);
4482
4483 End;
4484
4485 end if;
4486
4487 x_time_value := v_value2;
4488
4489
4490 Exception
4491
4492 when others then
4493 BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_selectStmt );
4494 BIS_UTILITIES_PUB.put_line(p_text =>'Exception executing sql in Get_Time_Level_Value 0400: '||sqlerrm);
4495 IF (c_dim_lvl_sn%ISOPEN) THEN
4496 CLOSE c_dim_lvl_sn;
4497 END IF;
4498
4499 END Get_Time_Level_Value;
4500
4501
4502
4503 Procedure Get_Start_End_Dates -- where level_value_id = p_id_value_name
4504 ( p_source IN varchar2, -- and level_value = p_time_value
4505 p_view_name IN varchar2, -- need to merge this and Get_Start_End_Dates2
4506 p_id_col_name IN varchar2,
4507 p_id_value_name IN varchar2,
4508 -- p_value_col_name IN varchar2,
4509 -- p_time_value IN varchar2,
4510 p_org_level_id IN varchar2,
4511 p_org_level_short_name IN varchar2,
4512 x_start_date OUT NOCOPY date,
4513 x_end_date OUT NOCOPY date
4514 )
4515
4516 IS
4517
4518 l_start_date date;
4519 l_end_date date;
4520
4521 l_sql VARCHAR2(32000);
4522 l_star VARCHAR2(2) := '*';
4523
4524 l_star1 NUMBER := -9999;
4525
4526 --2684911
4527 CURSOR c_dim_lvl_sn( cp_lvl_values_view IN VARCHAR2) IS SELECT short_name FROM bis_levels WHERE
4528 level_values_view_name = cp_lvl_values_view ;
4529 l_short_name bis_levels.short_name%TYPE;
4530 l_time_lvl_dep_on_org NUMBER(3);
4531 l_is_dep_on_org BOOLEAN := FALSE;
4532
4533 TYPE tcursor IS REF CURSOR;
4534 l1_cursor tcursor;
4535
4536 BEGIN
4537 -- 2684911
4538 IF (c_dim_lvl_sn%ISOPEN) THEN
4539 CLOSE c_dim_lvl_sn;
4540 END IF;
4541
4542 OPEN c_dim_lvl_sn(cp_lvl_values_view => p_view_name ) ;
4543 FETCH c_dim_lvl_sn INTO l_short_name;
4544 CLOSE c_dim_lvl_sn;
4545
4546 l_time_lvl_dep_on_org := BIS_UTILITIES_PUB.is_time_dependent_on_org(p_time_lvl_short_name => l_short_name) ;
4547 IF (l_short_name IS NOT NULL AND l_time_lvl_dep_on_org = BIS_UTILITIES_PUB.G_TIME_IS_DEPEN_ON_ORG) THEN
4548 l_is_dep_on_org := TRUE;
4549 END IF;
4550
4551 if p_source = 'EDW' then
4552 IF (l_is_dep_on_org) THEN --2684911
4553 l_sql := ' SELECT START_DATE, END_DATE ' ||
4554 ' FROM ' || p_view_name ||
4555 ' WHERE '|| p_id_col_name || ' = :p_id' ||
4556 ' and nvl(organization_id, :l_star1) = nvl(:p_Org_Level_ID, :l_star1) '||
4557 ' and nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) '||
4558 -- ' AND ' || p_value_col_name || ' = :p_time_value ' ||
4559 ' ORDER BY abs( nvl(trunc(end_date), trunc (sysdate)) - ' ||
4560 ' nvl(trunc(start_date), trunc(sysdate))) ';
4561 ELSE
4562 l_sql := ' SELECT START_DATE, END_DATE ' ||
4563 ' FROM ' || p_view_name ||
4564 ' WHERE '|| p_id_col_name || ' = :p_id' ||
4565 -- ' AND ' || p_value_col_name || ' = :p_time_value ' ||
4566 ' ORDER BY abs( nvl(trunc(end_date), trunc (sysdate)) - ' ||
4567 ' nvl(trunc(start_date), trunc(sysdate))) ';
4568 END IF;
4569
4570 -- Query is supposed to return just one record. However we take the first one.
4571 BEGIN
4572
4573 IF (l_is_dep_on_org) THEN --2684911
4574 OPEN l1_cursor FOR l_sql using p_id_value_name,l_star1 , p_Org_Level_ID,l_star1 ,l_star,p_Org_Level_Short_name,l_star ;
4575 ELSE
4576 OPEN l1_cursor FOR l_sql using p_id_value_name;
4577 END IF;
4578
4579 FETCH l1_cursor INTO l_start_date, l_end_date;
4580 CLOSE l1_cursor;
4581
4582 if l_start_date is null then
4583 BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_sql );
4584 BIS_UTILITIES_PUB.put_line(p_text =>' Date is ' || p_id_value_name );
4585 end if;
4586
4587 EXCEPTION
4588
4589 WHEN OTHERS THEN
4590 if l1_cursor%isopen then
4591 close l1_cursor;
4592 end if;
4593
4594 BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_sql );
4595 BIS_UTILITIES_PUB.put_line(p_text => ' ID is ' || p_id_value_name );
4596 BIS_UTILITIES_PUB.put_line(p_text =>'Exception executing sql in Get_Start_End_Dates 0100: '||sqlerrm);
4597
4598 END;
4599
4600 elsif p_source = 'OLTP' then -- and substr(l_short_name, 1, 2) <> 'HR' then
4601
4602 IF (l_is_dep_on_org) THEN --2684911
4603 l_sql := ' SELECT DISTINCT START_DATE, END_DATE ' ||
4604 ' FROM ' || p_view_name ||
4605 ' WHERE '|| p_id_col_name || ' = :p_id'
4606 -- || ' and ' || p_value_col_name || ' = :p_time_value '
4607 ||' and nvl(organization_id, :l_star) = nvl(:p_Org_Level_ID, :l_star) '
4608 ||' and nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) ';
4609 ELSE
4610 l_sql := ' SELECT DISTINCT START_DATE, END_DATE ' ||
4611 ' FROM ' || p_view_name ||
4612 ' WHERE '|| p_id_col_name || ' = :p_id' ;
4613 END IF;
4614
4615 begin
4616
4617 IF (l_is_dep_on_org) THEN --2684911
4618 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date
4619 USING p_id_value_name, l_star, p_Org_Level_ID, l_star, l_star, p_Org_Level_Short_name, l_star;
4620 ELSE
4621 EXECUTE IMMEDIATE l_sql INTO l_start_date, l_end_date USING p_id_value_name;
4622 END IF;
4623
4624 Exception
4625 when others then
4626 BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_sql );
4627 BIS_UTILITIES_PUB.put_line(p_text => ' ID is ' || p_id_value_name );
4628 BIS_UTILITIES_PUB.put_line(p_text =>' Get_Start_End_Dates 0200: Error finding Start date, End date '||sqlerrm);
4629
4630 end;
4631
4632 end if;
4633
4634 x_start_date := l_start_date;
4635 x_end_date := l_end_date;
4636
4637 Exception
4638
4639 when others then
4640 BIS_UTILITIES_PUB.put_line(p_text => ' SQL is ' || l_sql );
4641 -- BIS_UTILITIES_PUB.put_line(p_text => ' l_date = ' || p_date );
4642 BIS_UTILITIES_PUB.put_line(p_text => ' Exception executing sql in Get_Start_End_Dates 0300: '||sqlerrm);
4643 IF (c_dim_lvl_sn%ISOPEN) THEN
4644 CLOSE c_dim_lvl_sn;
4645 END IF;
4646
4647 END Get_Start_End_Dates;
4648
4649
4650
4651 Procedure Get_Min_Max_Start_End_Dates -- get min start and max end date for a given
4652 ( p_source IN varchar2, -- time level value.
4653 p_view_name IN varchar2,
4654 p_org_level_id IN varchar2,
4655 p_org_level_short_name IN varchar2,
4656 x_min_start_date OUT NOCOPY date,
4657 x_max_end_date OUT NOCOPY date
4658 )
4659 IS
4660
4661 l_min_start_date date;
4662 l_max_end_date date;
4663
4664 l_selectStmt VARCHAR2(32000);
4665 l_star VARCHAR2(2) := '*';
4666
4667 TYPE tcursor IS REF CURSOR;
4668 l_cursor tcursor;
4669
4670 --2684911
4671 CURSOR c_dim_lvl_sn( cp_lvl_values_view IN VARCHAR2) IS SELECT short_name FROM bis_levels WHERE
4672 level_values_view_name = cp_lvl_values_view ;
4673 l_short_name bis_levels.short_name%TYPE;
4674 l_time_lvl_dep_on_org NUMBER(3);
4675 l_is_dep_on_org BOOLEAN := FALSE;
4676
4677 l_dim_lvl_view_name VARCHAR2(32000) := NULL;
4678 BEGIN
4679 -- 2684911
4680 IF (c_dim_lvl_sn%ISOPEN) THEN
4681 CLOSE c_dim_lvl_sn;
4682 END IF;
4683
4684 OPEN c_dim_lvl_sn(cp_lvl_values_view => p_view_name ) ;
4685 FETCH c_dim_lvl_sn INTO l_short_name;
4686 CLOSE c_dim_lvl_sn;
4687
4688 l_time_lvl_dep_on_org := BIS_UTILITIES_PUB.is_time_dependent_on_org(p_time_lvl_short_name => l_short_name) ;
4689 IF (l_short_name IS NOT NULL AND l_time_lvl_dep_on_org = BIS_UTILITIES_PUB.G_TIME_IS_DEPEN_ON_ORG) THEN
4690 l_is_dep_on_org := TRUE;
4691 END IF;
4692
4693 if p_source = 'EDW' then
4694 IF (l_is_dep_on_org) THEN --2684911
4695 l_selectStmt := ' SELECT MIN(START_DATE), MAX(END_DATE) ' ||
4696 ' FROM ' || p_view_name ||
4697 ' WHERE nvl(organization_id, :l_star) = nvl(:p_Org_Level_ID, :l_star) ' ||
4698 ' AND nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) ';
4699 ELSE
4700 l_selectStmt := ' SELECT MIN(START_DATE), MAX(END_DATE) ' ||
4701 ' FROM ' || p_view_name ;
4702 END IF;
4703 begin
4704
4705 IF (l_is_dep_on_org) THEN --2684911
4706 OPEN l_cursor FOR l_selectStmt USING l_star ,p_Org_Level_ID, l_star , l_star ,p_Org_Level_Short_name,l_star ;
4707 ELSE
4708 OPEN l_cursor FOR l_selectStmt ;
4709 END IF;
4710 FETCH l_cursor INTO l_min_start_date, l_max_end_date;
4711 CLOSE l_cursor;
4712
4713 if l_min_start_date is null then
4714 BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_selectStmt );
4715 BIS_UTILITIES_PUB.put_line(p_text =>' min start date is ' || l_min_start_date );
4716 end if;
4717
4718 exception
4719 when others then
4720
4721 if l_cursor%isopen then
4722 close l_cursor;
4723 end if;
4724
4725 BIS_UTILITIES_PUB.put_line(p_text => ' l_selectStmt is : ' || l_selectStmt);
4726 BIS_UTILITIES_PUB.put_line(p_text => ' Error Get_Min_Max_Start_End_Dates 100 : ' || sqlerrm );
4727 end;
4728
4729 elsif p_source = 'OLTP' then
4730
4731 IF (l_is_dep_on_org) THEN --2684911
4732 l_selectStmt := ' SELECT MIN(START_DATE), MAX(END_DATE) ' ||
4733 ' FROM ' || p_view_name ||
4734 ' WHERE nvl(organization_id, :l_star) = nvl(:p_Org_Level_ID, :l_star) ' ||
4735 ' AND nvl(organization_type, :l_star) = nvl(:p_Org_Level_Short_name, :l_star) ' ||
4736 ' AND start_date < end_date ';
4737 ELSE
4738 l_selectStmt := ' SELECT MIN(START_DATE), MAX(END_DATE) ' ||
4739 ' FROM ' || p_view_name ||
4740 ' WHERE start_date < end_date ';
4741 END IF;
4742
4743 begin
4744 IF (l_is_dep_on_org) THEN --2684911
4745 EXECUTE IMMEDIATE l_selectStmt INTO l_min_start_date, l_max_end_date
4746 USING l_star, p_Org_Level_ID, l_star, l_star, p_Org_Level_Short_name, l_star;
4747 ELSE
4748 EXECUTE IMMEDIATE l_selectStmt INTO l_min_start_date, l_max_end_date;
4749 END IF;
4750 -- BIS_UTILITIES_PUB.put_line(p_text => ' V end date ' || v_end_date ) ;
4751
4752 exception
4753 when others then
4754 BIS_UTILITIES_PUB.put_line(p_text => ' l_selectStmt is : ' || l_selectStmt);
4755 BIS_UTILITIES_PUB.put_line(p_text =>'Error in Get_Min_Max_Start_End_Dates 200 '||sqlerrm);
4756 end;
4757
4758 end if;
4759
4760 x_min_start_date := l_min_start_date;
4761 x_max_end_date := l_max_end_date;
4762
4763
4764 Exception
4765
4766 when others then
4767 BIS_UTILITIES_PUB.put_line(p_text =>' SQL is ' || l_selectStmt );
4768 BIS_UTILITIES_PUB.put_line(p_text =>' Exception executing sql in Get_Min_Max_Start_End_Dates 0300: '||sqlerrm);
4769 IF (c_dim_lvl_sn%ISOPEN) THEN
4770 CLOSE c_dim_lvl_sn;
4771 END IF;
4772 END Get_Min_Max_Start_End_Dates;
4773
4774
4775
4776 --***************************************************************************************
4777 --***************************************************************************************
4778
4779
4780 function target_level_where_clause
4781 (p_user_id IN NUMBER
4782 ,x_return_status OUT NOCOPY VARCHAR2
4783 ,x_msg_count OUT NOCOPY VARCHAR2
4784 ,x_msg_data OUT NOCOPY VARCHAR2
4785 )
4786 return varchar2 is
4787
4788 l_target_level_Tbl target_level_Tbl_Type;
4789 l_where_clause VARCHAR2(32000) := 'target_level_id in (';
4790 l_return_status VARCHAR2(200);
4791 x_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4792 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4793 begin
4794
4795 FND_MSG_PUB.initialize;
4796
4797 Retrieve_User_target_level
4798 ( p_user_id => p_user_id
4799 , x_Target_Level_Tbl => l_Target_Level_Tbl
4800 , x_return_status => l_return_status);
4801
4802 if l_Target_Level_Tbl.COUNT <> 0 then
4803 --
4804 for i in l_Target_Level_Tbl.first .. l_Target_Level_Tbl.last
4805 loop
4806 --
4807 if i <> 1 then
4808 --
4809 l_where_clause := l_where_clause || ',';
4810 --
4811 end if;
4812 --
4813 l_where_clause := l_where_clause ||
4814 to_char(l_Target_Level_Tbl(i).Target_level_id);
4815 --
4816 end loop;
4817 --
4818 l_where_clause := l_where_clause || ')';
4819 --
4820 else
4821 l_where_clause := '';
4822 end if;
4823 --
4824 x_return_status := l_return_status;
4825
4826 return (l_where_clause);
4827
4828 EXCEPTION
4829 WHEN FND_API.G_EXC_ERROR THEN
4830 x_return_status := FND_API.G_RET_STS_ERROR;
4831 l_error_tbl := x_error_tbl;
4832 BIS_UTILITIES_PVT.Add_Error_Message
4833 ( p_error_table => l_error_tbl
4834 , p_error_msg_id => SQLCODE
4835 , p_error_description => SQLERRM
4836 , x_error_table => x_error_Tbl
4837 );
4838 FND_MSG_PUB.Count_And_Get
4839 ( p_count => x_msg_count,
4840 p_data => x_msg_data
4841 );
4842 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4844 l_error_tbl := x_error_tbl;
4845 BIS_UTILITIES_PVT.Add_Error_Message
4846 ( p_error_table => l_error_tbl
4847 , p_error_msg_id => SQLCODE
4848 , p_error_description => SQLERRM
4849 , x_error_table => x_error_Tbl
4850 );
4851 FND_MSG_PUB.Count_And_Get
4852 ( p_count => x_msg_count,
4853 p_data => x_msg_data
4854 );
4855 WHEN OTHERS THEN
4856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4857 l_error_tbl := x_error_tbl;
4858 BIS_UTILITIES_PVT.Add_Error_Message
4859 ( p_error_table => l_error_tbl
4860 , p_error_msg_id => SQLCODE
4861 , p_error_description => SQLERRM
4862 , x_error_table => x_error_Tbl
4863 );
4864 FND_MSG_PUB.Count_And_Get
4865 ( p_count => x_msg_count,
4866 p_data => x_msg_data
4867 );
4868 end Target_Level_where_clause;
4869 --
4870
4871 --
4872 function Perf_measure_where_clause
4873 (p_user_id IN NUMBER
4874 ,x_return_status OUT NOCOPY VARCHAR2
4875 ,x_msg_count OUT NOCOPY VARCHAR2
4876 ,x_msg_data OUT NOCOPY VARCHAR2
4877 )
4878 return varchar2 is
4879
4880 l_Perf_measure_Tbl Perf_Measure_Tbl_Type;
4881 l_where_clause VARCHAR2(32000) := 'measure_id in (';
4882 l_return_status VARCHAR2(200);
4883 x_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4884 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4885
4886 begin
4887
4888 FND_MSG_PUB.initialize;
4889
4890 Retrieve_User_perf_measure
4891 ( p_user_id => p_user_id
4892 , x_Perf_measure_Tbl => l_Perf_measure_Tbl
4893 , x_return_status => l_return_status);
4894
4895 if l_Perf_measure_Tbl.COUNT <> 0 then
4896 --
4897 for i in l_Perf_measure_Tbl.first .. l_Perf_measure_Tbl.last
4898 loop
4899 --
4900 if i <> 1 then
4901 --
4902 l_where_clause := l_where_clause || ',';
4903 --
4904 end if;
4905 --
4906 l_where_clause := l_where_clause ||
4907 to_char(l_perf_measure_Tbl(i).measure_id);
4908 --
4909 end loop;
4910 --
4911 l_where_clause := l_where_clause || ')';
4912 else
4913 l_where_clause := '';
4914 end if;
4915 --
4916 x_return_status := l_return_status;
4917
4918 return (l_where_clause);
4919
4920 EXCEPTION
4921 WHEN FND_API.G_EXC_ERROR THEN
4922 x_return_status := FND_API.G_RET_STS_ERROR;
4923 l_error_tbl := x_error_tbl;
4924 BIS_UTILITIES_PVT.Add_Error_Message
4925 ( p_error_table => l_error_tbl
4926 , p_error_msg_id => SQLCODE
4927 , p_error_description => SQLERRM
4928 , x_error_table => x_error_Tbl
4929 );
4930 FND_MSG_PUB.Count_And_Get
4931 ( p_count => x_msg_count,
4932 p_data => x_msg_data
4933 );
4934 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4936 l_error_tbl := x_error_tbl;
4937 BIS_UTILITIES_PVT.Add_Error_Message
4938 ( p_error_table => l_error_tbl
4939 , p_error_msg_id => SQLCODE
4940 , p_error_description => SQLERRM
4941 , x_error_table => x_error_Tbl
4942 );
4943 FND_MSG_PUB.Count_And_Get
4944 ( p_count => x_msg_count,
4945 p_data => x_msg_data
4946 );
4947 WHEN OTHERS THEN
4948 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4949 l_error_tbl := x_error_tbl;
4950 BIS_UTILITIES_PVT.Add_Error_Message
4951 ( p_error_table => l_error_tbl
4952 , p_error_msg_id => SQLCODE
4953 , p_error_description => SQLERRM
4954 , x_error_table => x_error_Tbl
4955 );
4956 FND_MSG_PUB.Count_And_Get
4957 ( p_count => x_msg_count,
4958 p_data => x_msg_data
4959 );
4960 end perf_measure_where_clause;
4961
4962 FUNCTION Is_Rolling_Period_Level
4963 ( p_level_short_name IN VARCHAR2
4964 )
4965 RETURN NUMBER IS
4966 l_level_id NUMBER;
4967 BEGIN
4968
4969 SELECT level_id
4970 INTO l_level_id
4971 FROM bis_levels
4972 WHERE short_name = p_level_short_name
4973 AND source = 'OLTP'
4974 AND level_values_view_name IS NULL;
4975
4976 RETURN 1;
4977
4978 EXCEPTION
4979 WHEN OTHERS THEN
4980 RETURN 0;
4981 END;
4982
4983
4984 --
4985
4986 FUNCTION get_Roll_Period_Start_Date
4987 ( p_level_short_name IN VARCHAR2
4988 , p_end_date IN DATE
4989 ) RETURN DATE IS
4990
4991 l_temp_level bis_levels.short_name%TYPE; -- VARCHAR2(15);
4992 l_start_date DATE;
4993 l_dynamic_sql_str VARCHAR2(4000);
4994
4995 BEGIN
4996
4997 l_temp_level := substr ( p_level_short_name , 13 );
4998
4999 IF ( l_temp_level = 'WEEK' ) THEN
5000 l_dynamic_sql_str := 'BEGIN :1 := FII_TIME_API.rwk_start(:2); END;';
5001 -- RETURN FII_TIME_API.rwk_start ( p_end_date ) ;
5002 ELSIF ( l_temp_level = 'MONTH' ) THEN
5003 l_dynamic_sql_str := 'BEGIN :1 := FII_TIME_API.rmth_start(:2); END;';
5004 -- RETURN FII_TIME_API.rmth_start ( p_end_date ) ;
5005 ELSIF ( l_temp_level = 'QTR' ) THEN
5006 l_dynamic_sql_str := 'BEGIN :1 := FII_TIME_API.rqtr_start(:2); END;';
5007 -- RETURN FII_TIME_API.rqtr_start ( p_end_date ) ;
5008 ELSIF ( l_temp_level = 'YEAR' ) THEN
5009 l_dynamic_sql_str := 'BEGIN :1 := FII_TIME_API.ryr_start(:2); END;';
5010 -- RETURN FII_TIME_API.ryr_start ( p_end_date ) ;
5011 ELSE
5012 RETURN NULL;
5013 END IF;
5014
5015
5016 EXECUTE IMMEDIATE l_dynamic_sql_str using OUT l_start_date, IN p_end_date;
5017
5018 RETURN l_start_date;
5019
5020 EXCEPTION
5021 WHEN OTHERS THEN
5022 -- BIS_UTILITIES_PUB.put_line(p_text => ' Error in get_Roll_Period_Start_Date. ' ) ;
5023 RETURN NULL;
5024
5025 END get_Roll_Period_Start_Date;
5026
5027
5028 FUNCTION Get_FND_Lookup
5029 ( p_lookup_type IN VARCHAR2
5030 , p_lookup_code IN VARCHAR2
5031 )
5032 RETURN VARCHAR2
5033 IS
5034 l_meaning VARCHAR2(80);
5035
5036 BEGIN
5037 SELECT MEANING
5038 INTO l_meaning
5039 FROM FND_LOOKUP_VALUES_VL
5040 WHERE LOOKUP_TYPE = p_lookup_type
5041 AND LOOKUP_CODE = p_lookup_code;
5042
5043 RETURN l_meaning;
5044
5045 END Get_FND_Lookup;
5046 --
5047
5048
5049
5050 FUNCTION get_bis_jsp_path RETURN VARCHAR2 IS
5051
5052 l_servlet_agent varchar2(500) :=NULL;
5053 l_jsp_path VARCHAR2(500) := NULL;
5054 l_url VARCHAR2(500):=NULL;
5055
5056 BEGIN
5057
5058 l_servlet_agent := FND_WEB_CONFIG.JSP_AGENT;
5059 l_jsp_path := '';
5060
5061 if ( l_servlet_agent is null ) then -- 'APPS_SERVLET_AGENT' is null
5062 l_servlet_agent := FND_WEB_CONFIG.WEB_SERVER;
5063 l_jsp_path := 'OA_HTML/';
5064 end if;
5065
5066 l_url := l_servlet_agent || l_jsp_path;
5067
5068 RETURN l_url;
5069
5070 EXCEPTION
5071
5072 WHEN OTHERS THEN
5073 RETURN l_jsp_path;
5074
5075 END get_bis_jsp_path;
5076
5077 FUNCTION get_webdb_host RETURN VARCHAR2 -- 1898436
5078 IS
5079 ws_url VARCHAR2(2000);
5080 hostname VARCHAR2(2000);
5081 index1 NUMBER;
5082 index2 NUMBER;
5083
5084 BEGIN
5085
5086 ws_url := FND_WEB_CONFIG.WEB_SERVER; -- ex : 'http://ap100jvm.us.oracle.com:8724/';
5087
5088
5089 index1 := INSTRB(ws_url, '//', 1) + 2; -- skip 'http://'
5090 index2 := INSTRB(ws_url, ':', index1);
5091
5092
5093 IF index2 = 0 THEN -- ex : 'http://ap100jvm.us.oracle.com/';
5094 hostname := SUBSTRB(ws_url, index1, length(ws_url)-index1);
5095 ELSE
5096 hostname := SUBSTRB(ws_url, index1, index2-index1);
5097 END IF;
5098
5099
5100 RETURN hostname;
5101
5102 END get_webdb_host;
5103
5104
5105 FUNCTION get_webdb_port RETURN VARCHAR2
5106 IS
5107 ws_url VARCHAR2(2000);
5108 portno VARCHAR2(500);
5109 index1 NUMBER;
5110 index2 NUMBER;
5111
5112 BEGIN
5113
5114 ws_url := FND_WEB_CONFIG.WEB_SERVER; -- ex :'http://ap100jvm.us.oracle.com:8724/';
5115
5116
5117 index1 := INSTRB(ws_url, '//', 1) + 2; -- skip 'http://'
5118 index2 := INSTRB(ws_url, ':', index1);
5119
5120
5121 IF index2 = 0 THEN -- ex : 'http://ap100jvm.us.oracle.com/';
5122 portno := '80';
5123 ELSE
5124 portno := SUBSTRB(ws_url, index2+1, length(ws_url)-index2-1);
5125 END IF;
5126
5127 RETURN portno;
5128
5129 END get_webdb_port;
5130
5131
5132
5133 --
5134 -- Init debug log file calls get_debug_mode_profile, sets the value
5135 -- of debug flag (BIS_UTILITIES_PUB.G_IS_DEBUG) using set_debug_log_flag
5136 -- and then opens the log file using open_debug_log.
5137 --
5138 PROCEDURE init_debug_log -- 2694978
5139 ( p_file_name IN VARCHAR2
5140 , p_dir_name IN VARCHAR2
5141 , x_return_status OUT NOCOPY VARCHAR2
5142 , x_return_msg OUT NOCOPY VARCHAR2
5143 )
5144 IS
5145 l_return_status VARCHAR2(1000) := FND_API.G_RET_STS_SUCCESS;
5146 l_return_msg VARCHAR2(10000) := NULL;
5147 l_is_debug_mode BOOLEAN := FALSE;
5148
5149 BEGIN
5150
5151 get_debug_mode_profile
5152 ( x_is_debug_mode => l_is_debug_mode
5153 , x_return_status => l_return_status
5154 , x_return_msg => l_return_msg
5155 );
5156
5157
5158 -- l_is_debug_mode := TRUE; -- Test onnly, to be removed.
5159
5160 IF (l_is_debug_mode) THEN
5161 open_debug_log (
5162 p_file_name => p_file_name,
5163 p_dir_name => p_dir_name,
5164 x_return_status => l_return_status,
5165 x_return_msg => x_return_msg );
5166 END IF;
5167
5168 set_debug_log_flag
5169 ( p_is_true => l_is_debug_mode
5170 , x_return_status => l_return_status
5171 , x_return_msg => l_return_msg
5172 );
5173
5174 x_return_status := l_return_status;
5175 x_return_msg := l_return_msg;
5176
5177 EXCEPTION
5178 WHEN OTHERS THEN
5179 x_return_status := FND_API.G_RET_STS_ERROR;
5180 x_return_msg := 'Error in BIS_UTILITIES_PUB.init_debug_log : '|| SQLERRM;
5181
5182 END init_debug_log;
5183
5184
5185 PROCEDURE init_debug_flag -- 2694978
5186 ( x_return_status OUT NOCOPY VARCHAR2
5187 , x_return_msg OUT NOCOPY VARCHAR2
5188 )
5189 IS
5190 l_return_status VARCHAR2(1000) := FND_API.G_RET_STS_SUCCESS;
5191 l_return_msg VARCHAR2(10000) := NULL;
5192 l_is_debug_mode BOOLEAN := FALSE;
5193
5194 BEGIN
5195
5196 get_debug_mode_profile
5197 ( x_is_debug_mode => l_is_debug_mode
5198 , x_return_status => l_return_status
5199 , x_return_msg => l_return_msg
5200 );
5201
5202 -- l_is_debug_mode := TRUE; -- Test onnly, to be removed.
5203
5204 set_debug_log_flag
5205 ( p_is_true => l_is_debug_mode
5206 , x_return_status => l_return_status
5207 , x_return_msg => l_return_msg
5208 );
5209
5210 x_return_status := l_return_status;
5211 x_return_msg := l_return_msg;
5212
5213 EXCEPTION
5214 WHEN OTHERS THEN
5215 x_return_status := FND_API.G_RET_STS_ERROR;
5216 x_return_msg := 'Error in BIS_UTILITIES_PUB.init_debug_log : '|| SQLERRM;
5217
5218 END init_debug_flag;
5219
5220
5221
5222 PROCEDURE get_debug_mode_profile -- 2694978
5223 ( x_is_debug_mode OUT NOCOPY BOOLEAN
5224 , x_return_status OUT NOCOPY VARCHAR2
5225 , x_return_msg OUT NOCOPY VARCHAR2
5226 ) IS
5227 l_debug_mode VARCHAR2(10) := 'N';
5228 BEGIN
5229
5230 x_return_status := FND_API.G_RET_STS_SUCCESS;
5231 x_return_msg := NULL;
5232 x_is_debug_mode := FALSE;
5233
5234 l_debug_mode := NVL ( FND_PROFILE.value(BIS_UTILITIES_PUB.G_DEBUG_LOG_PROFILE) , 'N' ) ;
5235
5236 IF ( l_debug_mode = 'Y') THEN
5237 x_is_debug_mode := TRUE;
5238 END IF;
5239
5240 EXCEPTION
5241 WHEN OTHERS THEN
5242 x_return_status := FND_API.G_RET_STS_ERROR;
5243 x_return_msg := 'Error in setting debug log flag in BIS_UTILITIES_PVT.get_debug_mode_profile: '|| SQLERRM;
5244 x_is_debug_mode := FALSE;
5245 END get_debug_mode_profile;
5246
5247
5248 --
5249 -- The following api is called (once per program) to set the value of
5250 -- debug flag value.
5251 --
5252 PROCEDURE set_debug_log_flag ( -- 2694978
5253 p_is_true IN BOOLEAN
5254 , x_return_status OUT NOCOPY VARCHAR2
5255 , x_return_msg OUT NOCOPY VARCHAR2
5256 )
5257 IS
5258 l_return_status VARCHAR2(1000) := FND_API.G_RET_STS_SUCCESS;
5259 l_return_msg VARCHAR2(10000) := NULL;
5260 BEGIN
5261
5262 x_return_status := FND_API.G_RET_STS_SUCCESS;
5263 x_return_msg := NULL;
5264
5265 BIS_UTILITIES_PUB.G_IS_DEBUG_ON := NVL(p_is_true, FALSE);
5266
5267 EXCEPTION
5268 WHEN OTHERS THEN
5269 x_return_status := FND_API.G_RET_STS_ERROR;
5270 x_return_msg := 'Error in setting debug log flag in BIS_UTILITIES_PVT.set_debug_log_flag: '|| SQLERRM;
5271 END set_debug_log_flag;
5272
5273
5274
5275 FUNCTION is_debug_on -- 2694978
5276 RETURN BOOLEAN
5277 IS
5278 BEGIN
5279
5280 RETURN BIS_UTILITIES_PUB.G_IS_DEBUG_ON;
5281
5282 EXCEPTION
5283 WHEN OTHERS THEN
5284 RETURN FALSE;
5285 END is_debug_on;
5286
5287
5288 FUNCTION get_default_dir_name -- 2694978
5289 RETURN VARCHAR2
5290 IS
5291 l_default_dir_name VARCHAR2(512); -- v$parameter%VALUE; --
5292 BEGIN
5293
5294 SELECT vp.value
5295 INTO l_default_dir_name
5296 FROM v$parameter vp
5297 WHERE vp.name = BIS_UTILITIES_PUB.G_UTL_FILE_DIR; -- 'utl_file_dir';
5298
5299 IF (LENGTH(l_default_dir_name) > 0) THEN
5300 IF (INSTR(l_default_dir_name,',', 1, 1) > 0) THEN
5301
5302 l_default_dir_name := SUBSTR( l_default_dir_name,
5303 1,
5304 INSTR(l_default_dir_name,',', 1, 1) - 1
5305 );
5306
5307 RETURN l_default_dir_name;
5308
5309 ELSE
5310 RETURN l_default_dir_name;
5311 END IF;
5312 ELSE
5313 RETURN NULL;
5314 END IF;
5315
5316 EXCEPTION
5317 WHEN OTHERS THEN
5318 RETURN NULL;
5319 END get_default_dir_name;
5320
5321
5322 FUNCTION get_default_log_name -- 2694978
5323 RETURN VARCHAR2
5324 IS
5325 l_default_log_name VARCHAR2(300) := 'Test';
5326 l_temp_num NUMBER := 0;
5327 BEGIN
5328
5329 /*
5330 SELECT bis_debug_log_s.nextval
5331 INTO l_temp_num
5332 FROM dual;
5333 */
5334
5335 l_default_log_name := 'Test' || l_temp_num || '.log';
5336
5337 RETURN l_default_log_name;
5338
5339 EXCEPTION
5340 WHEN OTHERS THEN
5341 RETURN NULL;
5342 END get_default_log_name;
5343
5344
5345
5346 PROCEDURE open_debug_log ( -- 2694978
5347 p_file_name IN VARCHAR2,
5348 p_dir_name IN VARCHAR2,
5349 x_return_status OUT NOCOPY VARCHAR2,
5350 x_return_msg OUT NOCOPY VARCHAR2)
5351 IS
5352 l_default_dir_name VARCHAR2(512); -- v$parameter%VALUE;
5353 l_default_log_name VARCHAR2(512); -- v$parameter%VALUE;
5354
5355 BEGIN
5356 x_return_status := FND_API.G_RET_STS_SUCCESS;
5357 x_return_msg := NULL;
5358
5359 l_default_dir_name := get_default_dir_name; -- ();
5360 l_default_log_name := get_default_log_name; -- () || '.log';
5361
5362 IF ( (l_default_dir_name IS NULL ) AND (p_dir_name IS NULL)) THEN
5363 x_return_status := FND_API.G_RET_STS_ERROR;
5364 x_return_msg := 'Error in opening debug log in BIS_UTILITIES_PVT.open_debug_log: Directory for log file is null';
5365 RETURN;
5366 END IF;
5367
5368 IF ( (l_default_log_name IS NULL ) AND (p_file_name IS NULL)) THEN
5369 x_return_status := FND_API.G_RET_STS_ERROR;
5370 x_return_msg := 'Error in opening debug log in BIS_UTILITIES_PVT.open_debug_log: Log file name is null';
5371 RETURN;
5372 END IF;
5373
5374 l_default_dir_name := NVL(p_dir_name, l_default_dir_name) ;
5375 l_default_log_name := NVL(p_file_name, l_default_log_name) ;
5376
5377 BIS_DEBUG_LOG.setup_file(
5378 p_log_file => l_default_log_name
5379 , p_out_file => NULL -- We don't want to create an out file.
5380 , p_directory => l_default_dir_name
5381 );
5382
5383 EXCEPTION
5384 WHEN OTHERS THEN
5385 x_return_status := FND_API.G_RET_STS_ERROR;
5386 x_return_msg := 'Error in setting debug log flag in BIS_UTILITIES_PVT.open_debug_log: '|| SQLERRM;
5387
5388 END open_debug_log;
5389
5390
5391 PROCEDURE close_debug_log ( -- 2694978
5392 p_file_name IN VARCHAR2,
5393 p_dir_name IN VARCHAR2,
5394 x_return_status OUT NOCOPY VARCHAR2,
5395 x_return_msg OUT NOCOPY VARCHAR2)
5396 IS
5397 BEGIN
5398
5399 x_return_status := FND_API.G_RET_STS_SUCCESS;
5400 x_return_msg := NULL;
5401
5402 BIS_DEBUG_LOG.close;
5403
5404 EXCEPTION
5405 WHEN OTHERS THEN
5406 x_return_status := FND_API.G_RET_STS_ERROR;
5407 x_return_msg := 'Error in setting debug log flag in BIS_UTILITIES_PVT.close_debug_log: '|| SQLERRM;
5408 END close_debug_log;
5409
5410
5411
5412 PROCEDURE put(p_text IN VARCHAR2) -- 2694978
5413 IS
5414 l_is_debug_on BOOLEAN := FALSE;
5415 BEGIN
5416
5417 l_is_debug_on := BIS_UTILITIES_PVT.is_debug_on;
5418
5419 IF (l_is_debug_on) THEN
5420 IF (
5421 ( BIS_UTILITIES_PUB.Value_Not_Missing(p_text) = FND_API.G_TRUE )
5422 AND ( BIS_UTILITIES_PUB.Value_Not_Null(p_text) = FND_API.G_TRUE)
5423 ) THEN
5424 BIS_DEBUG_LOG.put(p_text => p_text);
5425 END IF;
5426 END IF;
5427
5428 EXCEPTION
5429 WHEN OTHERS THEN
5430 NULL;
5431 END put;
5432
5433
5434 PROCEDURE put_line(p_text IN VARCHAR2) -- 2694978
5435 IS
5436 l_is_debug_on BOOLEAN := FALSE;
5437 BEGIN
5438
5439 l_is_debug_on := BIS_UTILITIES_PVT.is_debug_on;
5440
5441 IF (l_is_debug_on) THEN
5442 IF (
5443 ( BIS_UTILITIES_PUB.Value_Not_Missing(p_text) = FND_API.G_TRUE )
5444 AND ( BIS_UTILITIES_PUB.Value_Not_Null(p_text) = FND_API.G_TRUE)
5445 ) THEN
5446 BIS_DEBUG_LOG.put_line(p_text => p_text);
5447 END IF;
5448 END IF;
5449
5450 EXCEPTION
5451 WHEN OTHERS THEN
5452 NULL;
5453 END put_line;
5454
5455 --------------------------------------------------------------------------
5456
5457 FUNCTION escape_html(
5458 p_input IN VARCHAR2
5459 )
5460 RETURN VARCHAR2
5461 IS
5462 l_amp VARCHAR2(1) := '&';
5463 BEGIN
5464 --RETURN escape_html(p_input, '<BR>');
5465
5466 --Bug#3944741: As FND_CSS_PKG.Encode() converts single quote into '
5467 --this creates problem in escaping the single quote in
5468 --ICXUtils.replace_onMouseOver_quotes(), hence reverting this conversion
5469 RETURN (REPLACE(FND_CSS_PKG.Encode(p_input), l_amp||'#39;', ''''));
5470 END escape_html;
5471
5472 --------------------------------------------------------------------------
5473 FUNCTION escape_html_input(
5474 p_input IN VARCHAR2
5475 )
5476 RETURN VARCHAR2
5477 IS
5478 BEGIN
5479 RETURN escape_html(p_input, '');
5480 END escape_html_input;
5481
5482 --------------------------------------------------------------------------
5483 FUNCTION escape_html(
5484 p_input IN VARCHAR2
5485 ,p_cr IN VARCHAR2
5486 )
5487 RETURN VARCHAR2
5488 IS
5489 BEGIN
5490 RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p_input, '&', '&'), '<', '<'), '>', '>'), '"', '"'), '\n', p_cr);
5491 END escape_html;
5492
5493 --------------------------------------------------------------------------
5494
5495
5496 FUNCTION is_valid_time_dimension_level (
5497 p_bis_dimlevel_id IN NUMBER := NULL
5498 , x_return_status OUT NOCOPY VARCHAR2
5499 )
5500 RETURN BOOLEAN
5501 IS
5502 CURSOR c_dims IS
5503 SELECT source ,dimension_short_name, dimension_level_short_name
5504 FROM bisfv_dimension_levels
5505 WHERE dimension_level_id = p_bis_dimlevel_id;
5506 l_source bisfv_dimension_levels.SOURCE%TYPE;
5507 l_dimshort_name bisfv_dimension_levels.DIMENSION_SHORT_NAME%TYPE;
5508 l_dimlevel_name bisfv_dimension_levels.DIMENSION_LEVEL_SHORT_NAME%TYPE;
5509 l_dimshortname_time VARCHAR2(32000);
5510 l_lvlshortname_total VARCHAR2(32000);
5511 BEGIN
5512 x_return_status := FND_API.G_RET_STS_SUCCESS;
5513 IF(c_dims%ISOPEN) THEN
5514 CLOSE c_dims;
5515 END IF;
5516 OPEN c_dims;
5517 FETCH c_dims INTO l_source,l_dimshort_name,l_dimlevel_name;
5518 IF (c_dims%NOTFOUND) THEN
5519 x_return_status := FND_API.G_RET_STS_ERROR;
5520 RAISE FND_API.G_EXC_ERROR;
5521 END IF;
5522 CLOSE c_dims;
5523 IF (l_source = 'EDW') THEN
5524 l_dimshortname_time := BIS_UTILITIES_PVT.GET_TIME_DIMENSION_NAME_SRC(p_source => l_source);
5525 l_lvlshortname_total := BIS_UTILITIES_PVT.GET_TOTAL_DIMLEVEL_NAME_SRC(p_dim_short_name=>l_dimshort_name
5526 ,p_source => l_source);
5527 IF ((l_dimshort_name = l_dimshortname_time) AND
5528 (l_dimlevel_name <> l_lvlshortname_total)) THEN
5529 RETURN TRUE;
5530 ELSE
5531 RETURN FALSE;
5532 END IF;
5533 ELSE
5534 l_dimshortname_time := BIS_UTILITIES_PVT.GET_TIME_DIMENSION_NAME_SRC(p_Source => l_source);
5535 IF (l_dimshort_name = l_dimshortname_time) THEN
5536 RETURN TRUE;
5537 ELSE
5538 RETURN FALSE;
5539 END IF;
5540 END IF;
5541 RETURN FALSE;
5542 EXCEPTION
5543 WHEN FND_API.G_EXC_ERROR THEN
5544 IF(c_dims%ISOPEN) THEN
5545 CLOSE c_dims;
5546 END IF;
5547 x_return_status := FND_API.G_RET_STS_ERROR;
5548 RETURN FALSE;
5549 WHEN OTHERS THEN
5550 IF(c_dims%ISOPEN) THEN
5551 CLOSE c_dims;
5552 END IF;
5553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5554 RETURN FALSE;
5555 END is_valid_time_dimension_level;
5556 --
5557
5558 FUNCTION filter_quotes (
5559 p_filter_string IN VARCHAR2
5560 )
5561 RETURN VARCHAR2
5562 IS
5563 BEGIN
5564 RETURN REPLACE(p_filter_string, '''', '''''');
5565 END filter_quotes;
5566 --
5567
5568 FUNCTION get_role_id (
5569 p_role_name IN VARCHAR2
5570 )
5571 RETURN NUMBER
5572 IS
5573 CURSOR c_role_id IS
5574 SELECT orig_system_id FROM wf_local_roles
5575 WHERE name = p_role_name
5576 AND ( orig_system LIKE 'FND_RESP%'
5577 OR orig_system = 'FND_USR' )
5578 AND rownum < 2;
5579 l_role_id NUMBER;
5580 BEGIN
5581 IF(c_role_id%ISOPEN) THEN
5582 CLOSE c_role_id;
5583 END IF;
5584 OPEN c_role_id;
5585 FETCH c_role_id INTO l_role_id;
5586 CLOSE c_role_id;
5587
5588 RETURN l_role_id;
5589 EXCEPTION
5590 WHEN OTHERS THEN
5591 IF(c_role_id%ISOPEN) THEN
5592 CLOSE c_role_id;
5593 END IF;
5594 RETURN l_role_id;
5595 END get_role_id;
5596
5597 FUNCTION getPMVReport (
5598 p_report_url IN VARCHAR2
5599 )
5600 RETURN CLOB
5601 IS
5602 vHTMLPieces utl_http.html_pieces;
5603 l_html_pieces VARCHAR2(32000);
5604 report_html CLOB;
5605 BEGIN
5606
5607 vHTMLPieces := utl_http.request_pieces(url => p_report_url,
5608 max_pieces => 32000);
5609
5610 FOR i IN 1 .. vHTMLPieces.count loop
5611 l_html_pieces := vHTMLpieces(i);
5612 IF(report_html IS NULL) THEN
5613 WF_NOTIFICATION.NewClob(report_html, l_html_pieces);
5614 ELSE
5615 WF_NOTIFICATION.WriteToClob(report_html,l_html_pieces);
5616 END IF;
5617 END LOOP;
5618
5619 RETURN report_html;
5620 END getPMVReport;
5621
5622 /******************************************
5623 NAME : checkSWANEnabled
5624 Decsription : This fucntion checks if SWAN is enabled or not.
5625 created by : ashankar 21-Dec-05
5626 /******************************************/
5627
5628 FUNCTION checkSWANEnabled
5629 RETURN BOOLEAN IS
5630 l_swan_enabled BOOLEAN;
5631 BEGIN
5632
5633 IF(BIS_PORTLET_CUSTOM_PUB.c_SWAN_ENABLED=FND_API.G_TRUE)THEN
5634 l_swan_enabled := TRUE;
5635 ELSE
5636 l_swan_enabled := FALSE;
5637 END IF;
5638
5639 RETURN l_swan_enabled;
5640
5641 END checkSWANEnabled;
5642
5643 END BIS_UTILITIES_PVT;