[Home] [Help]
PACKAGE BODY: APPS.BIS_COLLECTION_UTILITIES
Source
1 package body BIS_COLLECTION_UTILITIES AS
2 /*$Header: BISDBUTB.pls 120.4 2006/09/07 14:38:26 amitgupt noship $*/
3
4 PROCEDURE UPDATE_DATE IS
5 l_count number;
6 BEGIN
7
8 SELECT count(1) INTO l_count from BIS_SYSTEM_DATE;
9
10 IF (l_count = 0) THEN /* INSERT DATE */
11
12 INSERT INTO BIS_SYSTEM_DATE(current_date_id)
13 values (trunc(sysdate));
14
15 ELSIF (l_count = 1) THEN /* Exists, update if needed */
16
17 UPDATE BIS_SYSTEM_DATE
18 set current_date_id = trunc(sysdate)
19 WHERE trunc(sysdate) <> trunc(current_date_id);
20
21 END IF;
22
23 END;
24
25
26 /*---------------------------------------------------------------------
27
28 In case the log directory isnt passed and the EDW_LOGFILE_DIR profile
29 option has also not been set, where do I write the log file ?
30
31 Doing so by parsing the 'utl_file_dir' init.ora parameter and scanning
32 for the word log and getting that string out.
33
34 ---------------------------------------------------------------------*/
35
36
37 Function getUtlFileDir return VARCHAR2 IS
38 l_dir VARCHAR2(1000);
39 l_utl_dir VARCHAR2(100);
40 l_count NUMBER := 0;
41 l_log_begin NUMBER := 0;
42 l_log_end NUMBER := 0;
43 l_comma_pos NUMBER := 0;
44 stmt VARCHAR2(200);
45 cid NUMBER;
46 l_dummy NUMBER;
47
48 BEGIN
49 SELECT value into l_dir
50 FROM v$parameter where upper(name) = 'UTL_FILE_DIR';
51
52 l_log_begin := INSTR(l_dir, ',');
53
54 IF (l_log_begin = 0) THEN /* then get the first string */
55 l_utl_dir := l_dir;
56 ELSE
57 l_utl_dir := substr(l_dir, 1, INSTR(l_dir, ',') - 1);
58 END IF;
59
60 RETURN l_utl_dir;
61
62 END;
63
64 Procedure enableParallelDML IS
65 BEGIN
66
67 execute immediate 'alter session enable parallel dml';
68 END;
69
70 Procedure disableParallelDML IS
71 BEGIN
72
73 execute immediate 'alter session disable parallel dml';
74 END;
75
76
77 FUNCTION SETUP( p_object_name IN VARCHAR2,
78 p_parallel IN NUMBER) RETURN BOOLEAN IS
79
80 l_dir VARCHAR2 (400);
81 l_bis VARCHAR2(20) := 'BIS';
82 BEGIN
83
84 commit; /* commit open txns so that alter session works regardless*/
85
86 -- Bug#5400214 :: This API is not required as FND takes care of the Log/Output file
87 -- names on its own; plus these apis causes the same name to be re-used
88 -- l_dir := getUtlFileDir;
89 --
90 -- IF l_dir IS NULL
91 -- THEN
92 -- l_dir := '/sqlcom/log';
93 -- END IF;
94 -- put_names (
95 -- p_object_name
96 -- || '.log',
97 -- p_object_name
98 -- || '.out',
99 -- l_dir
100 -- );
101
102 --if fnd_profile.value('EDW_DEBUG') = 'Y' or
103 -- FND_LOG.G_CURRENT_RUNTIME_LEVEL=FND_LOG.LEVEL_STATEMENT then
104 if FND_LOG.TEST( FND_LOG.LEVEL_STATEMENT , l_bis || '.' || p_object_name ) then
105 g_debug := true;
106 else
107 g_debug := false;
108 end if;
109
110 g_object_name:= p_object_name;
111 g_start_date:=sysdate;
112
113 g_parallel := floor(p_parallel);
114
115 IF (g_parallel is null) THEN
116 BEGIN
117 g_parallel := fnd_profile.value('EDW_PARALLEL_SRC');
118 EXCEPTION when others then
119 g_parallel := 1;
120 END;
121 END IF;
122
123
124 IF (g_parallel IS NULL OR g_parallel <1) THEN
125 g_parallel := 1;
126 END IF;
127
128
129 if g_parallel > 1 then /* removed as per performance team's suggestion */
130 null;
131 --execute immediate 'alter session enable parallel dml';
132 --execute immediate 'alter session force parallel dml parallel '|| g_parallel;
133 end if;
134
135 enableParallelDML;
136
137 /* Update the System Date table if necessary */
138
139 UPDATE_DATE ;
140
141 commit;
142
143 g_concurrent_id:=FND_GLOBAL.conc_request_id;
144 return true;
145 Exception when others then
146 g_status_message:=sqlerrm;
147 log('Exception in SETUP '||sqlerrm,0);
148 raise;
149 return false;
150 END SETUP;
151
152 /*
153 * Added for enhancement 3428371
154 */
155 PROCEDURE WRITE_BIS_REFRESH_LOG(
156 p_status IN BOOLEAN,
157 p_count IN NUMBER ,
158 p_message IN VARCHAR2 ,
159 p_period_from IN DATE ,
160 p_period_to IN DATE ,
161 p_attribute1 IN VARCHAR2 ,
162 p_attribute2 IN VARCHAR2 ,
163 p_attribute3 IN VARCHAR2 ,
164 p_attribute4 IN VARCHAR2 ,
165 p_attribute5 IN VARCHAR2 ,
166 p_attribute6 IN VARCHAR2 ,
167 p_attribute7 IN VARCHAR2 ,
168 p_attribute8 IN VARCHAR2 ,
169 p_attribute9 IN VARCHAR2 ,
170 p_attribute10 IN VARCHAR2 ) IS --??? para type
171 l_stmt VARCHAR2 (5000);
172 l_object_type VARCHAR2 (30);
173 l_status VARCHAR2 (40);
174 TYPE curtyp IS REF CURSOR;
175 cv curtyp;
176 BEGIN
177
178 g_concurrent_id:=FND_GLOBAL.conc_program_id;
179 g_request_id:=FND_GLOBAL.conc_request_id;
180 g_user_id := fnd_global.user_id;
181 g_login_id := fnd_global.login_id;
182 if p_status then l_status:='SUCCESS';
183 else l_status:='FAILURE';
184 end if;
185
186 insert into bis_refresh_log(
187 Request_id,
188 Concurrent_id,
189 Object_name,
190 Status,
191 Start_date,
192 Period_from,
193 Period_to,
194 Number_processed_record,
195 Exception_message,
196 Creation_date,
197 Created_by,
198 Last_update_date,
199 Last_update_login,
200 Last_updated_by,
201 Attribute1, Attribute2, Attribute3, Attribute4,
202 Attribute5, Attribute6, Attribute7, Attribute8,
203 Attribute9, Attribute10 )
204 values
205 (g_request_id,
206 g_concurrent_id,
207 g_object_name,
208 l_status,
209 g_start_date,
210 p_period_from,
211 p_period_to,
212 p_count,
213 p_message,
214 sysdate,
215 g_user_id,
216 sysdate,
217 g_login_id,
218 g_user_id,
219 p_attribute1, p_attribute2, p_attribute3, p_attribute4,
220 p_attribute5, p_attribute6, p_attribute7, p_attribute8,
221 p_attribute9, p_attribute10 );
222 commit;
223 Exception when others then
224 g_status_message:=sqlerrm;
225 log('Exception in WRITE_BIS_REFRESH_LOG '||sqlerrm,0);
226 END WRITE_BIS_REFRESH_LOG;
227
228 ----This function checks if the program exists or not
229 function program_exist(p_program_short_name in varchar2, p_program_application_id in number) return varchar2 is
230 l_exist_flag varchar2(1);
231 begin
232 l_exist_flag:='N';
233 select 'Y'
234 into l_exist_flag
235 from fnd_concurrent_programs
236 where concurrent_program_name=p_program_short_name
237 and application_id=p_program_application_id;
238 return l_exist_flag;
239 exception
240 when no_data_found then
241 l_exist_flag:='N';
242 return l_exist_flag;
243 when others then
244 raise;
245 end;
246
247 PROCEDURE WRAPUP(
248 p_status IN BOOLEAN,
249 p_count IN NUMBER ,
250 p_message IN VARCHAR2 ,
251 p_period_from IN DATE ,
252 p_period_to IN DATE ,
253 p_attribute1 IN VARCHAR2 ,
254 p_attribute2 IN VARCHAR2 ,
255 p_attribute3 IN VARCHAR2 ,
256 p_attribute4 IN VARCHAR2 ,
257 p_attribute5 IN VARCHAR2 ,
258 p_attribute6 IN VARCHAR2 ,
259 p_attribute7 IN VARCHAR2 ,
260 p_attribute8 IN VARCHAR2 ,
261 p_attribute9 IN VARCHAR2 ,
262 p_attribute10 IN VARCHAR2 ) IS --??? para type
263 l_stmt VARCHAR2 (5000);
264 l_object_type VARCHAR2 (30);
265 l_status VARCHAR2 (40);
266 errbuf varchar2(2000);
267 retcode number;
268 l_option_string varchar2(30);
269 l_cursor_id integer;
270 l_rows integer:=0;
271 TYPE curtyp IS REF CURSOR;
272 cv curtyp;
273 l_temp boolean;
274 BEGIN
275 commit;
276 disableParallelDML;
277
278 WRITE_BIS_REFRESH_LOG(
279 p_status,
280 p_count,
281 p_message,
282 p_period_from,
283 p_period_to,
284 p_attribute1,
285 p_attribute2,
286 p_attribute3,
287 p_attribute4,
288 p_attribute5,
289 p_attribute6,
290 p_attribute7,
291 p_attribute8,
292 p_attribute9,
293 p_attribute10);
294 -- Bug#5400214 :: This API is not required as FND takes care of the Log/Output file
295 -- names on its own; plus these apis causes the same name to be re-used
296 -- FND_FILE.RELEASE_NAMES(g_object_name||'.log', g_object_name||'.out');
297 commit;
298
299
300 ---The following code is added for KPI end to end support
301 ---call "Import BIS Time Dimension into BSC" so that whenever time dimension
302 ---is updated, this program is also being called to make data consistent
303 if g_object_name='FII_DBI_TIME_M' and program_exist('BSC_IMP_BIS_TIME_BSC',271)='Y' then
304 begin
305 BIS_COLLECTION_UTILITIES.put_line('calling "Import BIS Time Dimension into BSC program"');
306 if g_debug then
307 l_option_string:='DEBUG LOG';
308 else
309 l_option_string:=null;
310 end if;
311 /**
312 l_stmt := 'BEGIN BSC_DBI_CALENDAR.load_dbi_cal_into_bsc(:errbuf, :retcode,:option_string); END;';
313 l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
314 DBMS_SQL.parse(l_cursor_id,l_stmt,DBMS_SQL.V7);
315 DBMS_SQL.bind_variable(l_cursor_id,'errbuf',errbuf, 32767);
316 DBMS_SQL.bind_variable(l_cursor_id,'retcode',retcode, 200);
317 DBMS_SQL.bind_variable(l_cursor_id,'option_string',l_option_string, 32767);
318 l_rows:=DBMS_SQL.execute(l_cursor_id);
319 DBMS_SQL.close_cursor(l_cursor_id);
320 **/
321 l_stmt := 'BEGIN BSC_DBI_CALENDAR.load_dbi_cal_into_bsc(:1, :2,:3); END;';
322 execute immediate l_stmt using OUT errbuf,OUT retcode,IN l_option_string;
323 BIS_COLLECTION_UTILITIES.put_line('Done ' || 'BSC_DBI_CALENDAR.load_dbi_cal_into_bsc');
324 BIS_COLLECTION_UTILITIES.put_line('********************************************************');
325 exception
326 when others then
327 BIS_COLLECTION_UTILITIES.put_line('Exception happens in BSC_DBI_CALENDAR.load_dbi_cal_into_bsc '||sqlerrm);
328 l_temp:=fnd_concurrent.set_completion_status('WARNING' ,NULL);
329 end;
330 end if;
331
332 Exception when others then
333 g_status_message:=sqlerrm;
334 log('Exception in WRAPUP '||sqlerrm,0);
335 END WRAPUP;
336
337 function get_last_refresh_period(p_object_name in varchar2) return varchar2 is
338 l_date date;
339 l_date_disp varchar2(100);
340 begin
341 /* will NOT raise a no_data_found because of MAX */
342
343 SELECT MAX(period_to) INTO l_date
344 FROM bis_refresh_log
345 WHERE object_name = p_object_name AND
346 status='SUCCESS' AND
347 last_update_date =
348 (SELECT MAX(last_update_date)
349 FROM bis_refresh_log
350 WHERE object_name= p_object_name AND
351 status='SUCCESS' ) ;
352
353 IF (l_date IS NULL) THEN
354 l_date:= to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'), 'mm/dd/yyyy');
355 END IF;
356
357 l_date_disp := fnd_date.date_to_displaydt (l_date);
358 return l_date_disp;
359
360 Exception
361 WHEN OTHERS THEN
362 g_status_message:=sqlerrm;
363 log('Exception in GET_LAST_REFRESH_PERIOD '||sqlerrm,0);
367 p_object_name IN VARCHAR2,
364 end get_last_refresh_period;
365
366 procedure get_last_refresh_dates(
368 p_start_date OUT NOCOPY DATE,
369 p_end_date OUT NOCOPY DATE,
370 p_period_from OUT NOCOPY DATE,
371 p_period_to OUT NOCOPY DATE
372 ) is
373 cursor last_refresh_date_cursor(p_obj_name varchar2) is
374 select start_date, last_update_date, period_from, period_to
375 from bis_refresh_log
376 where object_name = p_obj_name and status='SUCCESS'
377 and last_update_date =( select max(last_update_date)
378 from bis_refresh_log
379 where object_name= p_obj_name and status='SUCCESS' ) ;
380 begin
381 open last_refresh_date_cursor(p_object_name);
382 fetch last_refresh_date_cursor into p_start_date, p_end_date, p_period_from, p_period_to;
383 if(last_refresh_date_cursor%ROWCOUNT < 1) then
384 p_start_date:=null;
385 p_end_date:=null;
386 p_period_from:=null;
387 p_period_to:=null;
388 end if;
389 close last_refresh_date_cursor;
390
391 Exception
392 WHEN NO_DATA_FOUND THEN
393 p_start_date:=null;
394 p_end_date:=null;
395 p_period_from:=null;
396 p_period_to:=null;
397 WHEN OTHERS THEN
398 g_status_message:=sqlerrm;
399 log('Exception in GET_LAST_REFRESH_DATES '||sqlerrm,0);
400 end get_last_refresh_dates;
401
402 procedure get_last_user_attributes(
403 p_object_name IN VARCHAR2,
404 p_attribute_table OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE,
405 p_count OUT NOCOPY NUMBER ) is
406 l_allattribute varchar2(2400);
407 begin
408 p_count:=0;
409
410 select attribute1, attribute2, attribute3, attribute4, attribute5,
411 attribute6,attribute7, attribute8, attribute9, attribute10,
412 attribute1||attribute2||attribute3||attribute4||attribute5||
413 attribute6||attribute7||attribute8||attribute9||attribute10
414 into p_attribute_table(1), p_attribute_table(2), p_attribute_table(3),
415 p_attribute_table(4), p_attribute_table(5), p_attribute_table(6),
416 p_attribute_table(7), p_attribute_table(8), p_attribute_table(9),
417 p_attribute_table(10), l_allattribute
418 from bis_refresh_log
419 where object_name = p_object_name and status='SUCCESS'
420 and last_update_date =( select max(last_update_date)
421 from bis_refresh_log
422 where object_name= p_object_name and status='SUCCESS' ) ;
423
424 if (l_allattribute is null) then p_count:=0;
425 else p_count:=10;
426 end if;
427
428 Exception
429 WHEN NO_DATA_FOUND THEN
430 p_count:=0;
431 p_attribute_table.delete;
432 WHEN OTHERS THEN
433 p_count:=0;
434 p_attribute_table.delete;
435 g_status_message:=sqlerrm;
436 log('Exception in GET_LAST_USER_ATTRIBUTES '||sqlerrm,0);
437
438 end get_last_user_attributes;
439
440 procedure log(
441 p_message IN VARCHAR2,
442 p_indenting IN NUMBER) is
443 l_message varchar2(2000):=null;
444 begin
445 for i in 1..p_indenting loop
446 l_message:=' '||l_message;
447 end loop;
448 l_message:=l_message||p_message;
449 put_line (l_message);
450 end log;
451
452 /*
453 * Added for FND_LOG uptaking
454 */
455 procedure log(
456 p_message IN VARCHAR2,
457 p_indenting IN NUMBER,
458 p_severity NUMBER) is
459 l_message varchar2(2000):=null;
460 begin
461 for i in 1..p_indenting loop
462 l_message:=' '||l_message;
463 end loop;
464 l_message:=l_message||p_message;
465 put_line (l_message, p_severity);
466 end log;
467
468 procedure debug(
469 p_message IN VARCHAR2,
470 p_indenting IN NUMBER) is
471 l_message varchar2(2000):=null;
472 begin
473 IF g_debug then
474 for i in 1..p_indenting loop
475 l_message:=' '||l_message;
476 end loop;
477 l_message:=l_message||p_message;
478 put_line (l_message, FND_LOG.LEVEL_STATEMENT);
479 END IF;
480 end debug;
481
482
483 /*
484 * Added for FND_LOG uptaking
485 */
486 procedure debug(
487 p_message IN VARCHAR2,
488 p_indenting IN NUMBER,
489 p_severity NUMBER) is
490 l_message varchar2(2000):=null;
491 begin
492 IF g_debug then
493 for i in 1..p_indenting loop
494 l_message:=' '||l_message;
495 end loop;
496 l_message:=l_message||p_message;
497 put_line (l_message ,p_severity);
498 END IF;
499 end debug;
500
501 procedure out(
502 p_message IN VARCHAR2,
503 p_indenting IN NUMBER) is
504 l_message varchar2(2000):=null;
505 begin
506 for i in 1..p_indenting loop
507 l_message:=' '||l_message;
508 end loop;
509 l_message:=l_message||p_message;
510 put_line_out (l_message);
511 end out;
512
513 PROCEDURE put_names(
514 p_log_file VARCHAR2,
515 p_out_file VARCHAR2,
516 p_directory VARCHAR2 /* Obsoleted */) IS
517 l_dir VARCHAR2(100) := null;
518
519 BEGIN
520
521 -- Need to add a call to getUtlFileDir as this is a public API and
522 -- teams may call this directly with invalid directories
523
524 l_dir := getUtlFileDir;
525 FND_FILE.PUT_NAMES(p_log_file, p_out_file, l_dir);
526
527 END put_names;
528
529 /*
530 * Added for FND_LOG uptaking
531 */
532 PROCEDURE put_line(
533 p_text VARCHAR2,
534 p_severity NUMBER) IS
535 BEGIN
536 put_conc_log(p_text);
537 put_fnd_log(p_text , p_severity);
538 END put_line;
539
540
541
545 put_fnd_log(p_text , FND_LOG.LEVEL_EXCEPTION);
542 PROCEDURE put_line(p_text VARCHAR2) IS
543 BEGIN
544 put_conc_log(p_text);
546 END put_line;
547
548
549 /*
550 * Added for FND_LOG uptaking
551 */
552 PROCEDURE put_conc_log(p_text VARCHAR2) IS
553 l_len number;
554 l_start number:=1;
555 l_end number:=1;
556 last_reached boolean:=false;
557 BEGIN
558 if p_text is null or p_text='' then
559 return;
560 end if;
561 l_len:=nvl(length(p_text),0);
562 if l_len <=0 then
563 return;
564 end if;
565 while true loop
566 l_end:=l_start+250;
567 if l_end >= l_len then
568 l_end:=l_len;
569 last_reached:=true;
570 end if;
571 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(p_text, l_start, 250));
572 l_start:=l_start+250;
573 if last_reached then
574 exit;
575 end if;
576 end loop;
577 END put_conc_log;
578
579 /*
580 * Added for FND_LOG uptaking
581 */
582 PROCEDURE put_fnd_log(p_text VARCHAR2, p_severity NUMBER) IS
583 l_len number;
584 l_start number:=1;
585 l_end number:=1;
586 last_reached boolean:=false;
587 l_bis VARCHAR2(20) := 'BIS';
588
589 BEGIN
590 if p_text is null or
591 p_text='' or
592 FND_LOG.G_CURRENT_RUNTIME_LEVEL<FND_LOG.LEVEL_STATEMENT
593 then
594 return;
595 end if;
596
597 l_len:=nvl(length(p_text),0);
598 if l_len <=0 then
599 return;
600 end if;
601 while true loop
602 l_end:=l_start+250;
603 if l_end >= l_len then
604 l_end:=l_len;
605 last_reached:=true;
606 end if;
607 if p_severity>=FND_LOG.G_CURRENT_RUNTIME_LEVEL then
608 FND_LOG.STRING(p_severity, l_bis || '.' || g_object_name ,substr(p_text, l_start, 250));
609 end if;
610 l_start:=l_start+250;
611 if last_reached then
612 exit;
613 end if;
614 end loop;
615 END put_fnd_log;
616
617 PROCEDURE put_line_out(p_text VARCHAR2) IS
618 l_len number;
619 l_start number:=1;
620 l_end number:=1;
621 last_reached boolean:=false;
622 BEGIN
623 if p_text is null or p_text='' then
624 return;
625 end if;
626 l_len:=nvl(length(p_text),0);
627 if l_len <=0 then
628 return;
629 end if;
630 while true loop
631 l_end:=l_start+250;
632 if l_end >= l_len then
633 l_end:=l_len;
634 last_reached:=true;
635 end if;
636 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,substr(p_text, l_start, 250));
637 l_start:=l_start+250;
638 if last_reached then
639 exit;
640 end if;
641 end loop;
642 END put_line_out;
643
644
645 ----the following code is for fixing bug 3326023
646 function get_user_rate_type(p_rate_type in varchar2) return varchar2 is
647 cursor c_user_rate_type is
648 SELECT user_conversion_type
649 FROM gl_daily_conversion_types
650 WHERE conversion_type = p_rate_type;
651
652 l_user_rate_type varchar2(30);
653
654 BEGIN
655 open c_user_rate_type;
656 fetch c_user_rate_type into l_user_rate_type;
657 if c_user_rate_type%notfound then
658 l_user_rate_type:=p_rate_type;
659 end if;
660 close c_user_rate_type;
661 return l_user_rate_type;
662 END;
663
664 FUNCTION getMissingRateHeader return VARCHAR2 IS
665 l_msg varchar2(3000) := null;
666 l_newline varchar2(10) := '
667 ';
668 l_temp varchar2(1000) := null;
669 BEGIN
670
671
672 fnd_message.set_name('BIS','BIS_DBI_CURR_OUTPUT_HDR');
673 l_msg := fnd_message.get || l_newline;
674
675
676 fnd_message.set_name('BIS','BIS_DBI_COL_RATE_TYPE');
677 l_temp:=substr(fnd_message.get, 1,g_length_rate_type );
678 l_temp := l_temp|| substr(g_space, 1, g_length_rate_type - length(l_temp))||g_indenting;
679 l_msg := l_msg || l_temp;
680
681
682
683 fnd_message.set_name('BIS','BIS_DBI_COL_FROM_CURRENCY');
684 l_temp := substr(fnd_message.get, 1, g_length_from_currency);
685 l_temp := l_temp || substr(g_space, 1, g_length_from_currency - length(l_temp)) || g_indenting;
686 l_msg := l_msg || l_temp;
687
688 fnd_message.set_name('BIS','BIS_DBI_COL_TO_CURRENCY');
689 l_temp:=substr(fnd_message.get, 1,g_length_to_currency );
690 l_temp := l_temp || substr(g_space, 1, g_length_to_currency - length(l_temp)) || g_indenting;
691 l_msg := l_msg || l_temp;
692
693 fnd_message.set_name('BIS','BIS_DBI_COL_DATE');
694 l_temp:=substr(fnd_message.get, 1,g_length_date );
695 l_temp := l_temp || substr(g_space, 1, g_length_date - length(l_temp));
696 l_msg := l_msg || l_temp || l_newline;
697
698 l_temp := substr(g_line, 1, g_length_rate_type)||g_indenting||
699 substr(g_line, 1, g_length_from_currency)||g_indenting||
700 substr(g_line, 1, g_length_to_currency)||g_indenting||
701 substr(g_line, 1, g_length_date);
702
703 /*'------------'||g_indenting ||'-----------------'||g_indenting||
704 '---------------'||g_indenting||'-------------';*/
705 l_msg := l_msg || l_temp||l_newline;
706
707 return l_msg;
708 END;
709
710
711 FUNCTION getMissingRateText(
712 p_rate_type IN VARCHAR2, /* Rate type */
713 p_from_currency IN VARCHAR2, /* From Currency */
714 p_to_currency in VARCHAR2, /* To Currency */
715 p_date IN DATE, /* Date in default format */
716 p_date_override IN VARCHAR2) return VARCHAR2 /* Formatted date, will output this instead of p_date */
717 IS
718
719 l_msg varchar2(1000) := null;
720 l_temp varchar2(1000) := null;
721 l_user_rate_type varchar2(30):=null;
722
723 BEGIN
727 l_msg:=substr(l_user_rate_type, 1,g_length_rate_type );
724 l_user_rate_type:=get_user_rate_type(p_rate_type);
725
726 ---l_msg:=substr(p_rate_type, 1,g_length_rate_type );
728
729 l_msg := l_msg || substr(g_space, 1, g_length_rate_type - length(l_msg))|| g_indenting;
730
731
732 l_temp:=substr(p_from_currency, 1, g_length_from_currency);
733 l_temp := l_temp || substr(g_space, 1, g_length_from_currency - length(l_temp)) || g_indenting;
734 l_msg := l_msg||l_temp;
735
736
737 l_temp:=substr(p_to_currency, 1,g_length_to_currency );
738 l_temp := l_temp || substr(g_space, 1, g_length_to_currency - length(l_temp)) || g_indenting;
739 l_msg := l_msg ||l_temp;
740
741
742 IF (p_date_override IS NULL) THEN
743 l_temp:=substr(fnd_date.date_to_displayDT(p_date), 1,g_length_date );
744 ELSE
745 l_temp := substr(p_date_override, 1,g_length_date );
746 END IF;
747
748 l_temp := l_temp || substr(g_space, 1, g_length_date - length(l_temp)) || g_indenting;
749 l_msg := l_msg||l_temp;
750
751 return l_msg;
752
753 END;
754
755
756 Procedure writeMissingRateHeader
757 IS
758 l_msg varchar2(3000):=null;
759
760 BEGIN
761
762
763 fnd_message.set_name('BIS','BIS_DBI_CURR_OUTPUT_HDR');
764 l_msg := fnd_message.get;
765 fnd_file.put_line(FND_FILE.OUTPUT, l_msg);
766 fnd_file.put_line(FND_FILE.OUTPUT, '');
767
768
769 fnd_message.set_name('BIS','BIS_DBI_COL_RATE_TYPE');
770 l_msg:=substr(fnd_message.get, 1,g_length_rate_type );
771 l_msg := l_msg|| substr(g_space, 1, g_length_rate_type - length(l_msg))||g_indenting;
772 fnd_file.put(FND_FILE.OUTPUT, l_msg);
773
774
775
776 fnd_message.set_name('BIS','BIS_DBI_COL_FROM_CURRENCY');
777 l_msg:=substr(fnd_message.get, 1, g_length_from_currency);
778 l_msg := l_msg || substr(g_space, 1, g_length_from_currency - length(l_msg)) || g_indenting;
779 fnd_file.put(FND_FILE.OUTPUT, l_msg);
780
781
782 fnd_message.set_name('BIS','BIS_DBI_COL_TO_CURRENCY');
783 l_msg:=substr(fnd_message.get, 1,g_length_to_currency );
784 l_msg := l_msg || substr(g_space, 1, g_length_to_currency - length(l_msg)) || g_indenting;
785 fnd_file.put(FND_FILE.OUTPUT, l_msg);
786
787
788 fnd_message.set_name('BIS','BIS_DBI_COL_DATE');
789 l_msg:=substr(fnd_message.get, 1,g_length_date );
790 l_msg := l_msg || substr(g_space, 1, g_length_date - length(l_msg));
791 fnd_file.put_line(FND_FILE.OUTPUT, l_msg);
792
793
794 /*fnd_file.put_line(FND_FILE.OUTPUT,
795 '------------'||g_indenting ||'-----------------'||g_indenting||
796 '---------------'||g_indenting||'-------------');
797 */
798 l_msg := substr(g_line, 1, g_length_rate_type)||g_indenting||
799 substr(g_line, 1, g_length_from_currency)||g_indenting||
800 substr(g_line, 1, g_length_to_currency)||g_indenting||
801 substr(g_line, 1, g_length_date);
802
803 fnd_file.put_line(FND_FILE.OUTPUT, l_msg);
804
805
806 END;
807
808 /*
809 Rate Type From Currency To Currency Date
810 ------------ ----------------- --------------- -------------
811 */
812
813
814
815 Procedure writeMissingRate(p_rate_type IN VARCHAR2, p_from_currency IN VARCHAR2, p_to_currency in VARCHAR2, p_date IN DATE, p_date_override IN VARCHAR2)
816 IS
817 l_msg varchar2(1000) := null;
818 l_user_rate_type varchar2(30);
819
820 BEGIN
821 ----the following code is for fixing bug 3326023
822 l_user_rate_type:=get_user_rate_type(p_rate_type);
823 ----l_msg:=substr(p_rate_type, 1,g_length_rate_type );
824 l_msg:=substr(l_user_rate_type, 1,g_length_rate_type );
825 l_msg := l_msg || substr(g_space, 1, g_length_rate_type - length(l_msg))|| g_indenting;
826 fnd_file.put(FND_FILE.OUTPUT, l_msg);
827
828
829 l_msg:=substr(p_from_currency, 1, g_length_from_currency);
830 l_msg := l_msg || substr(g_space, 1, g_length_from_currency - length(l_msg)) || g_indenting;
831 fnd_file.put(FND_FILE.OUTPUT, l_msg);
832
833
834 l_msg:=substr(p_to_currency, 1,g_length_to_currency );
835 l_msg := l_msg || substr(g_space, 1, g_length_to_currency - length(l_msg)) || g_indenting;
836 fnd_file.put(FND_FILE.OUTPUT, l_msg);
837
838
839 IF (p_date_override IS NULL) THEN
840 l_msg:=substr(fnd_date.date_to_displayDT(p_date), 1,g_length_date );
841 ELSE
842 l_msg := substr(p_date_override, 1,g_length_date );
843 END IF;
844
845 l_msg := l_msg || substr(g_space, 1, g_length_date - length(l_msg)) || g_indenting;
846 fnd_file.put_line(FND_FILE.OUTPUT, l_msg);
847
848
849 END;
850
851
852
853
854
855
856 Procedure deleteLogForObject(p_object_name varchar2) IS
857
858 BEGIN
859 EXECUTE IMMEDIATE 'delete from bis_refresh_log where upper(object_name)=upper(:1) and error_type is null' using p_object_name;
860
861 END;
862
863 FUNCTION getAppsSchema return VARCHAR2 IS
864
865 l_schema varchar2(100);
866 TYPE curtyp IS REF CURSOR;
867 cv curtyp;
868
869 BEGIN
870
871 OPEN cv for 'SELECT ORACLE_USERNAME from fnd_oracle_userid where oracle_id=900';
872 FETCH cv into l_schema;
873 CLOSE cv;
874
875 return l_schema;
876
877 END;
878
879
880
881
882 /* Missing UOM header */
883
884 Procedure writeMissingUOMHeader
885 IS
886 l_msg varchar2(3000):=null;
887 BEGIN
888
889 fnd_message.set_name('BIS','BIS_DBI_UOM_OUTPUT_HDR');
890 l_msg := fnd_message.get;
891 fnd_file.put_line(FND_FILE.OUTPUT, l_msg);
892 fnd_file.put_line(FND_FILE.OUTPUT, '');
893
894 fnd_message.set_name('BIS','BIS_DBI_COL_FROM_UOM');
898
895 l_msg:=substr(fnd_message.get, 1, g_length_from_to_uom);
896 l_msg := l_msg || substr(g_space, 1, g_length_from_to_uom - length(l_msg)) || g_indenting;
897 fnd_file.put(FND_FILE.OUTPUT, l_msg);
899 fnd_message.set_name('BIS','BIS_DBI_COL_TO_UOM');
900 l_msg:=substr(fnd_message.get, 1,g_length_from_to_uom);
901 l_msg := l_msg || substr(g_space, 1, g_length_from_to_uom - length(l_msg)) || g_indenting;
902 fnd_file.put(FND_FILE.OUTPUT, l_msg);
903
904 fnd_message.set_name('BIS','BIS_DBI_COL_INVENTORY_ITEM');
905 l_msg:=substr(fnd_message.get, 1, g_length_inventory_item );
906 l_msg := l_msg || substr(g_space, 1, g_length_inventory_item - length(l_msg));
907 fnd_file.put_line(FND_FILE.OUTPUT, l_msg);
908
909 l_msg := substr(g_line, 1, g_length_from_to_uom)||g_indenting||
910 substr(g_line, 1, g_length_from_to_uom)||g_indenting||
911 substr(g_line, 1, g_length_inventory_item);
912
913 fnd_file.put_line(FND_FILE.OUTPUT, l_msg);
914 END;
915
916 /*
917 From UOM To UOM Inventory Item
918 --------------- --------------- -------------------
919 */
920
921
922 /* Write the actual Missing UOM values */
923
924 Procedure writeMissingUOM(
925 p_from_uom IN VARCHAR2,
926 p_to_uom in VARCHAR2,
927 p_inventory_item IN VARCHAR2)
928 IS
929 l_msg varchar2(1000) := null;
930 BEGIN
931 l_msg:=substr(p_from_uom, 1, g_length_from_to_uom);
932 l_msg := l_msg || substr(g_space, 1, g_length_from_to_uom - length(l_msg)) || g_indenting;
933 fnd_file.put(FND_FILE.OUTPUT, l_msg);
934
935 l_msg:=substr(p_to_uom, 1,g_length_from_to_uom );
936 l_msg := l_msg || substr(g_space, 1, g_length_from_to_uom - length(l_msg)) || g_indenting;
937 fnd_file.put(FND_FILE.OUTPUT, l_msg);
938
939 l_msg := substr(p_inventory_item, 1,g_length_inventory_item);
940 l_msg := l_msg || substr(g_space, 1, g_length_inventory_item - length(l_msg)) || g_indenting;
941 fnd_file.put_line(FND_FILE.OUTPUT, l_msg);
942 END;
943
944
945 /* Missing UOM header */
946
947 Procedure writeMissingContractHeader
948 IS
949 l_msg varchar2(3000):=null;
950 l_tmp varchar2(300):= null;
951 BEGIN
952
953 fnd_message.set_name('BIS','BIS_DBI_CONTRACT_OUTPUT_HDR');
954 l_msg := fnd_message.get;
955 fnd_file.put_line(FND_FILE.OUTPUT, l_msg);
956 fnd_file.put_line(FND_FILE.OUTPUT, '');
957
958 l_msg := '';
959 fnd_message.set_name('BIS','BIS_DBI_COL_CONTRACT_NO');
960 l_tmp:=substr(fnd_message.get, 1, g_length_contract_no);
961 l_msg := l_msg ||l_tmp|| substr(g_space, 1, g_length_contract_no - length(l_tmp)) || g_indenting;
962
963 fnd_message.set_name('BIS','BIS_DBI_COL_STATUS');
964 l_tmp:=substr(fnd_message.get, 1, g_length_status);
965 l_msg := l_msg || l_tmp||substr(g_space, 1, g_length_status - length(l_tmp)) || g_indenting;
966
967 fnd_message.set_name('BIS','BIS_DBI_COL_RATE_TYPE');
968 l_tmp:=substr(fnd_message.get, 1,g_length_rate_type );
969 l_msg := l_msg|| l_tmp||substr(g_space, 1, g_length_rate_type - length(l_tmp))||g_indenting;
970
971
972 fnd_message.set_name('BIS','BIS_DBI_COL_FROM_CURRENCY');
973 l_tmp:=substr(fnd_message.get, 1, g_length_from_currency);
974 l_msg := l_msg || l_tmp||substr(g_space, 1, g_length_from_currency - length(l_tmp)) || g_indenting;
975
976 fnd_message.set_name('BIS','BIS_DBI_COL_TO_CURRENCY');
977 l_tmp:=substr(fnd_message.get, 1,g_length_to_currency );
978 l_msg := l_msg ||l_tmp|| substr(g_space, 1, g_length_to_currency - length(l_tmp)) || g_indenting;
979
980 fnd_message.set_name('BIS','BIS_DBI_COL_DATE');
981 l_tmp:=substr(fnd_message.get, 1,g_length_date );
982 l_msg := l_msg ||l_tmp|| substr(g_space, 1, g_length_date - length(l_tmp)) || g_indenting;
983
984 fnd_message.set_name('BIS','BIS_DBI_COL_CONTRACT_ID');
985 l_tmp:=substr(fnd_message.get, 1,g_length_contract_id );
986 l_msg := l_msg ||l_tmp|| substr(g_space, 1, g_length_contract_id - length(l_tmp));
987
988 ---fnd_file.put_line(fnd_file.LOG, 'L_MSG IS : '||l_msg);
989 fnd_file.put_line(FND_FILE.OUTPUT, l_msg);
990
991
992 l_msg := substr(g_line, 1, g_length_contract_no)||g_indenting||
993 substr(g_line, 1, g_length_status)||g_indenting||
994 substr(g_line, 1, g_length_rate_type)||g_indenting||
995 substr(g_line, 1, g_length_from_currency)||g_indenting||
996 substr(g_line, 1, g_length_to_currency)||g_indenting||
997 substr(g_line, 1, g_length_date)||g_indenting||
998 substr(g_line, 1, g_length_contract_id);
999
1000 fnd_file.put_line(FND_FILE.OUTPUT, l_msg);
1001 END;
1002
1003 /*
1004 From UOM To UOM Inventory Item
1005 --------------- --------------- -------------------
1006 */
1007
1008
1009 /* Write the actual Missing UOM values */
1010
1011 Procedure writeMissingContract(
1012 P_contract_no IN VARCHAR2, /* Contract Number*/
1013 P_contract_status IN VARCHAR2,/* Contract Status*/
1014 p_contract_id IN VARCHAR2, /* Contract ID */
1015 p_rate_type IN VARCHAR2, /* Rate type */
1016 p_from_currency IN VARCHAR2, /* From Currency */
1017 p_to_currency in VARCHAR2, /* To Currency */
1018 p_date IN DATE, /* Date in default format */
1019 p_date_override IN VARCHAR2) /* Formatted date, will output this instead of p_date */
1020 IS
1021 l_msg varchar2(1000) := null;
1022 l_user_rate_type varchar2(30);
1023
1024 BEGIN
1025
1026 l_msg:=substr(p_contract_no, 1, g_length_contract_no);
1027 l_msg := l_msg || substr(g_space, 1, g_length_contract_no - length(l_msg)) || g_indenting;
1028 fnd_file.put(FND_FILE.OUTPUT, l_msg);
1029
1030 l_msg:=substr(p_contract_status, 1,g_length_status );
1031 l_msg := l_msg || substr(g_space, 1, g_length_status - length(l_msg)) || g_indenting;
1032 fnd_file.put(FND_FILE.OUTPUT, l_msg);
1033
1034 ----the following code is for bug 4260678
1038
1035 l_user_rate_type:=get_user_rate_type(p_rate_type);
1036 --l_msg:=substr(p_rate_type, 1,g_length_rate_type );
1037 l_msg:=substr(l_user_rate_type, 1,g_length_rate_type );
1039 l_msg := l_msg || substr(g_space, 1, g_length_rate_type - length(l_msg))|| g_indenting;
1040 fnd_file.put(FND_FILE.OUTPUT, l_msg);
1041
1042
1043 l_msg:=substr(p_from_currency, 1, g_length_from_currency);
1044 l_msg := l_msg || substr(g_space, 1, g_length_from_currency - length(l_msg)) || g_indenting;
1045 fnd_file.put(FND_FILE.OUTPUT, l_msg);
1046
1047
1048 l_msg:=substr(p_to_currency, 1,g_length_to_currency );
1049 l_msg := l_msg || substr(g_space, 1, g_length_to_currency - length(l_msg)) || g_indenting;
1050 fnd_file.put(FND_FILE.OUTPUT, l_msg);
1051
1052
1053 IF (p_date_override IS NULL) THEN
1054 l_msg:=substr(fnd_date.date_to_displayDT(p_date), 1,g_length_date );
1055 ELSE
1056 l_msg := substr(p_date_override, 1,g_length_date );
1057 END IF;
1058
1059 l_msg := l_msg || substr(g_space, 1, g_length_date - length(l_msg)) || g_indenting;
1060 fnd_file.put(FND_FILE.OUTPUT, l_msg);
1061
1062
1063 l_msg := substr(p_contract_id, 1,g_length_contract_id);
1064 l_msg := l_msg || substr(g_space, 1, g_length_contract_id - length(l_msg)) || g_indenting;
1065 fnd_file.put_line(FND_FILE.OUTPUT, l_msg);
1066 END;
1067
1068 /*
1069 * Added for enhancement 3183157
1070 */
1071 function get_last_failure_period(p_object_name in varchar2) return varchar2 is
1072 l_date date;
1073 l_date_disp varchar2(100);
1074 l_proc VARCHAR2(100) := 'BIS.BIS_COLLECTION_UTILITIES.get_last_failure_period';
1075 begin
1076 SELECT MAX(period_to) INTO l_date
1077 FROM bis_refresh_log
1078 WHERE object_name = p_object_name AND
1079 status='FAILURE' AND
1080 last_update_date = (
1081 SELECT MAX(last_update_date)
1082 FROM bis_refresh_log
1083 WHERE object_name= p_object_name AND
1084 status='FAILURE' ) ;
1085 IF (l_date IS NULL) THEN
1086 l_date:= to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'), 'mm/dd/yyyy');
1087 END IF;
1088 l_date_disp := fnd_date.date_to_displaydt (l_date);
1089 return l_date_disp;
1090 Exception WHEN OTHERS THEN
1091 /*Generic Exception Handling block.*/
1092 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1093 fnd_message.set_token('ERRNO' ,SQLCODE);
1094 fnd_message.set_token('REASON', SQLERRM);
1095 fnd_message.set_token('ROUTINE', l_proc);
1096 put_line(fnd_message.get, fnd_log.LEVEL_UNEXPECTED);
1097 RAISE;
1098 end get_last_failure_period;
1099
1100 /*
1101 * Overloaded get_last_failure_period for bug#4365064 to have 2 OUT params
1102 * 1. p_period_from
1103 * 2. p_period_to
1104 */
1105 PROCEDURE get_last_failure_period(
1106 p_object_name in varchar2,
1107 p_period_from OUT NOCOPY varchar2,
1108 p_period_to OUT NOCOPY varchar2
1109 ) is
1110 l_date_from date;
1111 l_date_to date;
1112
1113 l_proc VARCHAR2(100) := 'BIS.BIS_COLLECTION_UTILITIES.get_last_failure_period';
1114 begin
1115 SELECT period_to, period_from INTO l_date_to,l_date_from
1116 FROM bis_refresh_log
1117 WHERE object_name = p_object_name AND
1118 status='FAILURE' AND
1119 last_update_date = (
1120 SELECT MAX(last_update_date)
1121 FROM bis_refresh_log
1122 WHERE object_name= p_object_name AND
1123 status='FAILURE' )AND
1124 rownum = 1
1125 ORDER BY period_to desc ;
1126 IF (l_date_to IS NULL) THEN
1127 l_date_to:= to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'), 'mm/dd/yyyy');
1128 END IF;
1129 IF (l_date_from IS NULL) THEN
1130 l_date_from:= to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'), 'mm/dd/yyyy');
1131 END IF;
1132 p_period_to := fnd_date.date_to_displaydt (l_date_to);
1133 p_period_from := fnd_date.date_to_displaydt (l_date_from);
1134 Exception WHEN OTHERS THEN
1135 /*Generic Exception Handling block.*/
1136 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1137 fnd_message.set_token('ERRNO' ,SQLCODE);
1138 fnd_message.set_token('REASON', SQLERRM);
1139 fnd_message.set_token('ROUTINE', l_proc);
1140 put_line(fnd_message.get, fnd_log.LEVEL_UNEXPECTED);
1141 RAISE;
1142 end get_last_failure_period;
1143
1144 END BIS_COLLECTION_UTILITIES;