eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BSC_BSC_XTD_PKG dependencies on BSC_IM_UTILS

Line 30: BSC_im_utils.write_to_log_file_n('Error in create_rpt_key_table '||g_status_message);

26: g_status_message:=sqlerrm;
27: g_status:=-1;
28: p_error_message:=g_status_message;
29: if g_file and g_debug then
30: BSC_im_utils.write_to_log_file_n('Error in create_rpt_key_table '||g_status_message);
31: end if;
32: End;
33:
34: --public

Line 62: BSC_im_utils.write_to_log_file_n('delete '||p_table_name||' where session_id='||p_session_id||get_time);

58: p_error_message out nocopy varchar2
59: )is
60: Begin
61: if g_file and g_debug then
62: BSC_im_utils.write_to_log_file_n('delete '||p_table_name||' where session_id='||p_session_id||get_time);
63: end if;
64: execute immediate 'delete '||p_table_name||' where session_id=:1' using p_session_id;
65: if g_file and g_debug then
66: BSC_im_utils.write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);

Line 66: BSC_im_utils.write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);

62: BSC_im_utils.write_to_log_file_n('delete '||p_table_name||' where session_id='||p_session_id||get_time);
63: end if;
64: execute immediate 'delete '||p_table_name||' where session_id=:1' using p_session_id;
65: if g_file and g_debug then
66: BSC_im_utils.write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
67: end if;
68: if g_num_kpi_xtd is not null and g_num_kpi_xtd>0 then
69: g_kpi_xtd.delete;
70: end if;

Line 73: BSC_im_utils.write_to_log_file_n('---------------------------------------');

69: g_kpi_xtd.delete;
70: end if;
71: g_num_kpi_xtd:=0;
72: if g_file and g_debug then
73: BSC_im_utils.write_to_log_file_n('---------------------------------------');
74: end if;
75: Exception when others then
76: if sqlcode=-06531 then
77: null;

Line 82: BSC_im_utils.write_to_log_file_n(g_status_message);

78: else
79: g_status_message:=sqlerrm;
80: p_error_message:=g_status_message;
81: if g_file and g_debug then
82: BSC_im_utils.write_to_log_file_n(g_status_message);
83: end if;
84: end if;
85: End;
86:

Line 113: BSC_im_utils.write_to_log_file_n(p_error_message);

109: g_status:=-1;
110: p_error_message:=g_status_message;
111: rollback;
112: if g_file and g_debug then
113: BSC_im_utils.write_to_log_file_n(p_error_message);
114: end if;
115: End;
116: /*
117: ---------------------------------

Line 155: BSC_im_utils.write_to_log_file_n('populate_rpt_keys p_table_name='||p_table_name||',p_session_id='||p_session_id||

151: g_num_kpi_xtd:=0;
152: end if;
153: init;
154: if g_file and g_debug then
155: BSC_im_utils.write_to_log_file_n('populate_rpt_keys p_table_name='||p_table_name||',p_session_id='||p_session_id||
156: ',p_kpi='||p_kpi||','||'p_report_date='||p_report_date||',p_xtd_period='||p_xtd_period||',p_xtd_year='||
157: p_xtd_year||',p_xtd_periodicity='||p_xtd_periodicity||',p_xtd_type='||p_xtd_type||
158: ',g_option_string='||g_option_string||get_time);
159: end if;

Line 203: BSC_im_utils.write_to_log_file_n('Looking at the following dates, periods and year');

199: l_xtd_year(l_num_report_date):=l_xtd_year(i);
200: end if;
201: end loop;
202: if g_file and g_debug then
203: BSC_im_utils.write_to_log_file_n('Looking at the following dates, periods and year');
204: for i in 1..l_num_report_date loop
205: BSC_im_utils.write_to_log_file(l_report_date(i)||' '||l_xtd_period(i)||' '||l_xtd_year(i));
206: end loop;
207: end if;

Line 205: BSC_im_utils.write_to_log_file(l_report_date(i)||' '||l_xtd_period(i)||' '||l_xtd_year(i));

201: end loop;
202: if g_file and g_debug then
203: BSC_im_utils.write_to_log_file_n('Looking at the following dates, periods and year');
204: for i in 1..l_num_report_date loop
205: BSC_im_utils.write_to_log_file(l_report_date(i)||' '||l_xtd_period(i)||' '||l_xtd_year(i));
206: end loop;
207: end if;
208: if l_num_report_date>0 then
209: if populate_rpt_keys(

Line 229: BSC_im_utils.write_to_log_file_n('---------------------------------------');

225: --if g_debug then
226: -- copy_to_debug_table;
227: --end if;
228: if g_file and g_debug then
229: BSC_im_utils.write_to_log_file_n('---------------------------------------');
230: end if;
231: Exception
232: when e_error then
233: g_status:=-1;

Line 242: BSC_im_utils.write_to_log_file_n(p_error_message);

238: g_status:=-1;
239: p_error_message:=g_status_message;
240: rollback;
241: if g_file and g_debug then
242: BSC_im_utils.write_to_log_file_n(p_error_message);
243: end if;
244: End;
245:
246: function populate_rpt_keys(

Line 340: BSC_im_utils.write_to_log_file_n('The kpi periodicities for '||p_kpi||' (calendar='||l_calendar_id||')');

336: end loop;
337: close c1;
338: l_num_periodicity:=l_num_periodicity-1;
339: if g_file and g_debug then
340: BSC_im_utils.write_to_log_file_n('The kpi periodicities for '||p_kpi||' (calendar='||l_calendar_id||')');
341: for i in 1..l_num_periodicity loop
342: BSC_im_utils.write_to_log_file(l_periodicity(i)||' '||l_period_type_id(i)||' '||
343: l_period_column_name(i)||' '||l_period_num_of_periods(i));
344: end loop;

Line 342: BSC_im_utils.write_to_log_file(l_periodicity(i)||' '||l_period_type_id(i)||' '||

338: l_num_periodicity:=l_num_periodicity-1;
339: if g_file and g_debug then
340: BSC_im_utils.write_to_log_file_n('The kpi periodicities for '||p_kpi||' (calendar='||l_calendar_id||')');
341: for i in 1..l_num_periodicity loop
342: BSC_im_utils.write_to_log_file(l_periodicity(i)||' '||l_period_type_id(i)||' '||
343: l_period_column_name(i)||' '||l_period_num_of_periods(i));
344: end loop;
345: end if;
346: /*

Line 400: BSC_im_utils.write_to_log_file_n('This is avg column. The kpi periodicities');

396: l_periodicity:=ll_periodicity;
397: l_period_type_id:=ll_period_type_id;
398: l_period_column_name:=ll_period_column_name;
399: if g_file and g_debug then
400: BSC_im_utils.write_to_log_file_n('This is avg column. The kpi periodicities');
401: for i in 1..l_num_periodicity loop
402: BSC_im_utils.write_to_log_file(l_periodicity(i)||' '||l_period_type_id(i)||' '||
403: l_period_column_name(i));
404: end loop;

Line 402: BSC_im_utils.write_to_log_file(l_periodicity(i)||' '||l_period_type_id(i)||' '||

398: l_period_column_name:=ll_period_column_name;
399: if g_file and g_debug then
400: BSC_im_utils.write_to_log_file_n('This is avg column. The kpi periodicities');
401: for i in 1..l_num_periodicity loop
402: BSC_im_utils.write_to_log_file(l_periodicity(i)||' '||l_period_type_id(i)||' '||
403: l_period_column_name(i));
404: end loop;
405: end if;
406: end;

Line 426: BSC_im_utils.write_to_log_file_n('The XTD pattern for xtd periodicity '||p_xtd_periodicity);

422: g_status:=-1;
423: return false;
424: end if;
425: if g_file and g_debug then
426: BSC_im_utils.write_to_log_file_n('The XTD pattern for xtd periodicity '||p_xtd_periodicity);
427: BSC_im_utils.write_to_log_file(l_xtd_pattern);
428: end if;
429: --3919980
430: if p_xtd_type='ROLLING' and instr(g_option_string,'AVG MEASURE')>0 then

Line 427: BSC_im_utils.write_to_log_file(l_xtd_pattern);

423: return false;
424: end if;
425: if g_file and g_debug then
426: BSC_im_utils.write_to_log_file_n('The XTD pattern for xtd periodicity '||p_xtd_periodicity);
427: BSC_im_utils.write_to_log_file(l_xtd_pattern);
428: end if;
429: --3919980
430: if p_xtd_type='ROLLING' and instr(g_option_string,'AVG MEASURE')>0 then
431: open c3(l_calendar_id);

Line 437: BSC_im_utils.write_to_log_file_n('The XTD pattern after filtering for DBI-ENT '||l_xtd_pattern);

433: if l_cal_type='DBI-ENT' then
434: l_xtd_pattern:=substr(l_xtd_pattern,instr(l_xtd_pattern,';',-1,2)+1);
435: end if;
436: if g_file and g_debug then
437: BSC_im_utils.write_to_log_file_n('The XTD pattern after filtering for DBI-ENT '||l_xtd_pattern);
438: end if;
439: end if;
440: if parse_values(l_xtd_pattern,';',l_pattern,l_num_pattern)=false then
441: g_status:=-1;

Line 457: BSC_im_utils.write_to_log_file_n('XTD pattern value for BITAND '||l_xtd_pattern_value||',l_hier='||l_hier);

453: exit;
454: end if;
455: end loop;
456: if g_file and g_debug then
457: BSC_im_utils.write_to_log_file_n('XTD pattern value for BITAND '||l_xtd_pattern_value||',l_hier='||l_hier);
458: end if;
459: if l_xtd_pattern_value is null then
460: --Bug 3294193----------------------------------------
461: --try the more complex approach.

Line 468: BSC_im_utils.write_to_log_file_n('Try more complex approach...');

464: l_pattern_periodicity number_tabletype;--the periodicities in a pattern
465: l_num_pattern_periodicity number;
466: begin
467: if g_file and g_debug then
468: BSC_im_utils.write_to_log_file_n('Try more complex approach...');
469: end if;
470: for i in 1..l_num_pattern loop
471: l_rank(i):=0;
472: l_index:=0;

Line 481: BSC_im_utils.write_to_log_file_n('Infinite loop detected');

477: l_start:=l_index+1;
478: loop
479: l_count:=l_count+1;
480: if l_count>l_max_count then
481: BSC_im_utils.write_to_log_file_n('Infinite loop detected');
482: g_status_message:='Infinite loop detected';
483: return false;
484: end if;
485: l_start:=instr(l_pattern(i),',',l_start);

Line 499: BSC_im_utils.write_to_log_file_n('periodicities parsed out from '||l_pattern(i)||' are ');

495: exit;
496: end if;
497: end loop;
498: if g_file and g_debug then
499: BSC_im_utils.write_to_log_file_n('periodicities parsed out from '||l_pattern(i)||' are ');
500: for j in 1..l_num_pattern_periodicity loop
501: BSC_im_utils.write_to_log_file(l_pattern_periodicity(j));
502: end loop;
503: end if;

Line 501: BSC_im_utils.write_to_log_file(l_pattern_periodicity(j));

497: end loop;
498: if g_file and g_debug then
499: BSC_im_utils.write_to_log_file_n('periodicities parsed out from '||l_pattern(i)||' are ');
500: for j in 1..l_num_pattern_periodicity loop
501: BSC_im_utils.write_to_log_file(l_pattern_periodicity(j));
502: end loop;
503: end if;
504: end if;
505: --for each pattern, check the periodicities in the kpi and see if they exist and give the rank

Line 526: BSC_im_utils.write_to_log_file_n('The ranks');

522: end if;
523: --now we have all the ranks. see which one is the largest
524: end loop;
525: if g_file and g_debug then
526: BSC_im_utils.write_to_log_file_n('The ranks');
527: for i in 1..l_num_pattern loop
528: BSC_im_utils.write_to_log_file(l_pattern(i)||' '||l_rank(i));
529: end loop;
530: end if;

Line 528: BSC_im_utils.write_to_log_file(l_pattern(i)||' '||l_rank(i));

524: end loop;
525: if g_file and g_debug then
526: BSC_im_utils.write_to_log_file_n('The ranks');
527: for i in 1..l_num_pattern loop
528: BSC_im_utils.write_to_log_file(l_pattern(i)||' '||l_rank(i));
529: end loop;
530: end if;
531: l_max_rank:=0;
532: l_max_rank_index:=0;

Line 546: BSC_im_utils.write_to_log_file_n('XTD pattern value for BITAND '||l_xtd_pattern_value||',l_hier='||l_hier);

542: l_xtd_pattern_value:=substr(l_pattern(l_max_rank_index),l_start,l_end-l_start);
543: l_hier:=substr(l_pattern(l_max_rank_index),1,l_start-2);
544: end if;
545: if g_file and g_debug then
546: BSC_im_utils.write_to_log_file_n('XTD pattern value for BITAND '||l_xtd_pattern_value||',l_hier='||l_hier);
547: end if;
548: end;
549: end if;
550: -----------------------------------------------------

Line 558: BSC_im_utils.write_to_log_file_n('See if periodicities are in random order...');

554: 2. missing periodicity
555: */
556: --see if periodicity is arranged in some other order
557: if g_file and g_debug then
558: BSC_im_utils.write_to_log_file_n('See if periodicities are in random order...');
559: end if;
560: l_found:=false;
561: l_num_pattern_period:=0;
562: for i in 1..l_num_pattern loop

Line 604: BSC_im_utils.write_to_log_file_n('After looking for periodicities in different order '||

600: exit;
601: end if;
602: end loop;
603: if g_file and g_debug then
604: BSC_im_utils.write_to_log_file_n('After looking for periodicities in different order '||
605: 'XTD pattern value for BITAND '||l_xtd_pattern_value||',l_hier='||l_hier);
606: end if;
607: end if;
608: ---------------------------------------

Line 615: BSC_im_utils.write_to_log_file_n('Check for missing periodicity...');

611: if l_xtd_pattern_value is null then
612: --there is missing periodicity
613: --l_period_missing
614: if g_file and g_debug then
615: BSC_im_utils.write_to_log_file_n('Check for missing periodicity...');
616: end if;
617: l_periodicity_missing:=true;--this is used as a main flag to indiate that there is missing periodicity
618: if g_file and g_debug then
619: BSC_im_utils.write_to_log_file_n('The denormalized array of pattern and the periodicity in it');

Line 619: BSC_im_utils.write_to_log_file_n('The denormalized array of pattern and the periodicity in it');

615: BSC_im_utils.write_to_log_file_n('Check for missing periodicity...');
616: end if;
617: l_periodicity_missing:=true;--this is used as a main flag to indiate that there is missing periodicity
618: if g_file and g_debug then
619: BSC_im_utils.write_to_log_file_n('The denormalized array of pattern and the periodicity in it');
620: for i in 1..l_num_pattern_period loop
621: BSC_im_utils.write_to_log_file(l_pattern_period_pattern(i)||' '||l_pattern_period_periodicity(i));
622: end loop;
623: end if;

Line 621: BSC_im_utils.write_to_log_file(l_pattern_period_pattern(i)||' '||l_pattern_period_periodicity(i));

617: l_periodicity_missing:=true;--this is used as a main flag to indiate that there is missing periodicity
618: if g_file and g_debug then
619: BSC_im_utils.write_to_log_file_n('The denormalized array of pattern and the periodicity in it');
620: for i in 1..l_num_pattern_period loop
621: BSC_im_utils.write_to_log_file(l_pattern_period_pattern(i)||' '||l_pattern_period_periodicity(i));
622: end loop;
623: end if;
624: for i in 1..l_num_pattern loop
625: l_rank(i):=0;

Line 649: BSC_im_utils.write_to_log_file_n('The rank for pattern '||l_pattern(i)||' '||l_rank(i));

645: end if;
646: end loop;
647: end if;
648: if g_file and g_debug then
649: BSC_im_utils.write_to_log_file_n('The rank for pattern '||l_pattern(i)||' '||l_rank(i));
650: end if;
651: end loop;
652: --find min rank
653: l_min_rank:=1000000;

Line 663: BSC_im_utils.write_to_log_file_n(g_status_message);

659: end if;
660: end loop;
661: if l_min_rank_index=0 then
662: g_status_message:='Could not find any pattern to use. Min rank=0 ';
663: BSC_im_utils.write_to_log_file_n(g_status_message);
664: return false;
665: end if;
666: if g_file and g_debug then
667: BSC_im_utils.write_to_log_file_n('Min rank '||l_min_rank||' and min rank pattern '||

Line 667: BSC_im_utils.write_to_log_file_n('Min rank '||l_min_rank||' and min rank pattern '||

663: BSC_im_utils.write_to_log_file_n(g_status_message);
664: return false;
665: end if;
666: if g_file and g_debug then
667: BSC_im_utils.write_to_log_file_n('Min rank '||l_min_rank||' and min rank pattern '||
668: l_pattern(l_min_rank_index));
669: end if;
670: l_pattern_to_use:=l_min_rank_index;
671: l_xtd_pattern_value:=substr(l_pattern(l_pattern_to_use),instr(l_pattern(l_pattern_to_use),':')+1,length(

Line 675: BSC_im_utils.write_to_log_file_n('After looking for Missing periodicities '||

671: l_xtd_pattern_value:=substr(l_pattern(l_pattern_to_use),instr(l_pattern(l_pattern_to_use),':')+1,length(
672: l_pattern(l_pattern_to_use)));
673: l_hier:=substr(l_pattern(l_pattern_to_use),1,instr(l_pattern(l_pattern_to_use),':')-1);
674: if g_file and g_debug then
675: BSC_im_utils.write_to_log_file_n('After looking for Missing periodicities '||
676: 'XTD pattern value for BITAND '||l_xtd_pattern_value||',l_hier='||l_hier);
677: end if;
678: --find missing periodicities
679: for i in 1..l_num_pattern_period loop

Line 699: BSC_im_utils.write_to_log_file_n('Missing periodicities');

695: ll_temp_period_missing(ll_num_temp_pattern_period):=l_pattern_period_missing(i);
696: end if;
697: end loop;
698: if g_file and g_debug then
699: BSC_im_utils.write_to_log_file_n('Missing periodicities');
700: for i in 1..l_num_pattern_period loop
701: if l_pattern_period_pattern(i)=l_pattern_to_use and l_pattern_period_missing(i) then
702: BSC_im_utils.write_to_log_file(l_pattern_period_periodicity(i));
703: end if;

Line 702: BSC_im_utils.write_to_log_file(l_pattern_period_periodicity(i));

698: if g_file and g_debug then
699: BSC_im_utils.write_to_log_file_n('Missing periodicities');
700: for i in 1..l_num_pattern_period loop
701: if l_pattern_period_pattern(i)=l_pattern_to_use and l_pattern_period_missing(i) then
702: BSC_im_utils.write_to_log_file(l_pattern_period_periodicity(i));
703: end if;
704: end loop;
705: end if;
706: end if;--if l_xtd_pattern_value is null then

Line 777: BSC_im_utils.write_to_log_file_n(g_status_message);

773: Exception
774: when e_error then
775: g_status_message:=sqlerrm;
776: if g_file and g_debug then
777: BSC_im_utils.write_to_log_file_n(g_status_message);
778: end if;
779: raise;
780: when others then
781: g_status_message:=sqlerrm;

Line 783: BSC_im_utils.write_to_log_file_n(g_status_message);

779: raise;
780: when others then
781: g_status_message:=sqlerrm;
782: if g_file and g_debug then
783: BSC_im_utils.write_to_log_file_n(g_status_message);
784: end if;
785: raise;
786: End;
787:

Line 802: BSC_im_utils.write_to_log_file_n('Error in is_daily_periodicity '||sqlerrm);

798: return false;
799: end if;
800: Exception when others then
801: if g_file and g_debug then
802: BSC_im_utils.write_to_log_file_n('Error in is_daily_periodicity '||sqlerrm);
803: end if;
804: raise;
805: End;
806: /*

Line 828: BSC_im_utils.write_to_log_file(l_stmt||' using '||

824: 'xtd_period,xtd_year,period,year,period_type_id,periodicity_id,period_flag,day_count,rolling_flag,last_update_date) '||
825: 'select :1,:2,:3,:4,:5,max(period),max(year),1,:6,1,1,''N'',sysdate from bsc_reporting_calendar where '||
826: 'calendar_id=:7 and report_date=:8 and period_type_id=1 and rolling_flag=''N''';
827: if g_file and g_debug then
828: BSC_im_utils.write_to_log_file(l_stmt||' using '||
829: p_session_id||' '||p_report_date(i)||' '||p_xtd_periodicity||' '||p_xtd_period(i)||' '||p_xtd_year(i)||' '||p_xtd_periodicity||' '||
830: p_calendar_id||' '||p_report_date(i));
831: end if;
832: execute immediate l_stmt using p_session_id,p_report_date(i),p_xtd_periodicity,p_xtd_period(i),p_xtd_year(i),p_xtd_periodicity,

Line 835: BSC_im_utils.write_to_log_file_n('Inserted(Daily) '||sql%rowcount||' rows '||get_time);

831: end if;
832: execute immediate l_stmt using p_session_id,p_report_date(i),p_xtd_periodicity,p_xtd_period(i),p_xtd_year(i),p_xtd_periodicity,
833: p_calendar_id,p_report_date(i);
834: if g_file and g_debug then
835: BSC_im_utils.write_to_log_file_n('Inserted(Daily) '||sql%rowcount||' rows '||get_time);
836: end if;
837: end loop;
838: Exception when others then
839: if g_file and g_debug then

Line 840: BSC_im_utils.write_to_log_file_n('Error in populate_rpt_keys_daily '||sqlerrm);

836: end if;
837: end loop;
838: Exception when others then
839: if g_file and g_debug then
840: BSC_im_utils.write_to_log_file_n('Error in populate_rpt_keys_daily '||sqlerrm);
841: end if;
842: raise;
843: End;
844:

Line 869: BSC_im_utils.write_to_log_file_n('In insert_rpt_cal_keys p_xtd_periodicity='||

865: l_start number;
866: --
867: Begin
868: if g_file and g_debug then
869: BSC_im_utils.write_to_log_file_n('In insert_rpt_cal_keys p_xtd_periodicity='||
870: p_xtd_periodicity||',p_hier='||p_hier||
871: ',p_xtd_pattern='||p_xtd_pattern||',p_calendar_id='||p_calendar_id||',p_roll_flag='||p_roll_flag||get_time);
872: BSC_im_utils.write_to_log_file(p_report_date||' '||p_report_date_insert||' '||p_xtd_period||' '||p_xtd_year);
873: if p_periodicity_missing then

Line 872: BSC_im_utils.write_to_log_file(p_report_date||' '||p_report_date_insert||' '||p_xtd_period||' '||p_xtd_year);

868: if g_file and g_debug then
869: BSC_im_utils.write_to_log_file_n('In insert_rpt_cal_keys p_xtd_periodicity='||
870: p_xtd_periodicity||',p_hier='||p_hier||
871: ',p_xtd_pattern='||p_xtd_pattern||',p_calendar_id='||p_calendar_id||',p_roll_flag='||p_roll_flag||get_time);
872: BSC_im_utils.write_to_log_file(p_report_date||' '||p_report_date_insert||' '||p_xtd_period||' '||p_xtd_year);
873: if p_periodicity_missing then
874: BSC_im_utils.write_to_log_file('p_periodicity_missing=TRUE');
875: else
876: BSC_im_utils.write_to_log_file('p_periodicity_missing=FALSE');

Line 874: BSC_im_utils.write_to_log_file('p_periodicity_missing=TRUE');

870: p_xtd_periodicity||',p_hier='||p_hier||
871: ',p_xtd_pattern='||p_xtd_pattern||',p_calendar_id='||p_calendar_id||',p_roll_flag='||p_roll_flag||get_time);
872: BSC_im_utils.write_to_log_file(p_report_date||' '||p_report_date_insert||' '||p_xtd_period||' '||p_xtd_year);
873: if p_periodicity_missing then
874: BSC_im_utils.write_to_log_file('p_periodicity_missing=TRUE');
875: else
876: BSC_im_utils.write_to_log_file('p_periodicity_missing=FALSE');
877: end if;
878: for i in 1..p_num_pattern_period loop

Line 876: BSC_im_utils.write_to_log_file('p_periodicity_missing=FALSE');

872: BSC_im_utils.write_to_log_file(p_report_date||' '||p_report_date_insert||' '||p_xtd_period||' '||p_xtd_year);
873: if p_periodicity_missing then
874: BSC_im_utils.write_to_log_file('p_periodicity_missing=TRUE');
875: else
876: BSC_im_utils.write_to_log_file('p_periodicity_missing=FALSE');
877: end if;
878: for i in 1..p_num_pattern_period loop
879: BSC_im_utils.write_to_log_file('p_period_periodicity(i)='||p_period_periodicity(i));
880: if p_period_missing(i) then

Line 879: BSC_im_utils.write_to_log_file('p_period_periodicity(i)='||p_period_periodicity(i));

875: else
876: BSC_im_utils.write_to_log_file('p_periodicity_missing=FALSE');
877: end if;
878: for i in 1..p_num_pattern_period loop
879: BSC_im_utils.write_to_log_file('p_period_periodicity(i)='||p_period_periodicity(i));
880: if p_period_missing(i) then
881: BSC_im_utils.write_to_log_file('p_period_missing(i)=TRUE');
882: end if;
883: end loop;

Line 881: BSC_im_utils.write_to_log_file('p_period_missing(i)=TRUE');

877: end if;
878: for i in 1..p_num_pattern_period loop
879: BSC_im_utils.write_to_log_file('p_period_periodicity(i)='||p_period_periodicity(i));
880: if p_period_missing(i) then
881: BSC_im_utils.write_to_log_file('p_period_missing(i)=TRUE');
882: end if;
883: end loop;
884: end if;
885: --with the global g_kpi_xtd, check_rpt_cal_keys is really not reqd.

Line 893: BSC_im_utils.write_to_log_file_n('insert into bsc_rpt_keys(session_id,report_date,'||

889: end if;
890: if l_status is null then
891: --need to load into the cal keys table
892: if g_file and g_debug then
893: BSC_im_utils.write_to_log_file_n('insert into bsc_rpt_keys(session_id,report_date,'||
894: 'xtd_periodicity,xtd_period,xtd_year,period,year,period_type_id,periodicity_id,day_count,rolling_flag,last_update_date) '||
895: 'select '||g_session_id||',p_report_date_insert,'||p_xtd_periodicity||',p_xtd_period,p_xtd_year,'||
896: 'period,year,period_type_id,periodicity_id,day_count,rolling_flag,sysdate from bsc_reporting_calendar where '||
897: 'calendar_id='||p_calendar_id||' and report_date=p_report_date and hierarchy='||p_hier||

Line 899: BSC_im_utils.write_to_log_file(p_report_date||' '||p_report_date_insert||' '||p_xtd_period||' '||p_xtd_year);

895: 'select '||g_session_id||',p_report_date_insert,'||p_xtd_periodicity||',p_xtd_period,p_xtd_year,'||
896: 'period,year,period_type_id,periodicity_id,day_count,rolling_flag,sysdate from bsc_reporting_calendar where '||
897: 'calendar_id='||p_calendar_id||' and report_date=p_report_date and hierarchy='||p_hier||
898: 'and rolling_flag='||p_roll_flag||' and bitand(record_type_id,'||p_xtd_pattern||')=record_type_id');
899: BSC_im_utils.write_to_log_file(p_report_date||' '||p_report_date_insert||' '||p_xtd_period||' '||p_xtd_year);
900: end if;
901: --need to make compatible with 8i. cannot have forall stmt. 8i does not allow execute immediate inside forall
902: --forall i in 1..p_num_report_date
903: execute immediate 'insert into bsc_rpt_keys(session_id,report_date,xtd_periodicity,'||

Line 912: BSC_im_utils.write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);

908: 'and bitand(record_type_id,:10)=record_type_id'
909: using g_session_id,p_report_date_insert,p_xtd_periodicity,p_xtd_period,p_xtd_year,p_calendar_id,
910: p_report_date,p_hier,p_roll_flag,p_xtd_pattern;
911: if g_file and g_debug then
912: BSC_im_utils.write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
913: end if;
914: if p_roll_flag<>'Y' then
915: --update the period flag for the current day
916: --get the max of the period where the period_type_id is 1 or daily

Line 929: BSC_im_utils.write_to_log_file_n(l_stmt||' using '||g_session_id||',p_report_date_insert,'||p_xtd_periodicity||

925: 'from '||p_table_name||' where session_id=:5 '||
926: 'and report_date=:6 '||
927: 'and xtd_periodicity=:7 and period_type_id=1 and rolling_flag=:8)';
928: if g_file and g_debug then
929: BSC_im_utils.write_to_log_file_n(l_stmt||' using '||g_session_id||',p_report_date_insert,'||p_xtd_periodicity||
930: p_roll_flag||','||g_session_id||',p_report_date_insert,'||p_xtd_periodicity||','||p_roll_flag);
931: end if;
932: --need to make compatible with 8i. cannot have forall stmt. 8i does not allow execute immediate inside forall
933: --forall i in 1..p_num_report_date

Line 937: BSC_im_utils.write_to_log_file_n('Updated '||sql%rowcount||' rows '||get_time);

933: --forall i in 1..p_num_report_date
934: execute immediate l_stmt using g_session_id,p_report_date_insert,p_xtd_periodicity,p_roll_flag,
935: g_session_id,p_report_date_insert,p_xtd_periodicity,p_roll_flag;
936: if g_file and g_debug then
937: BSC_im_utils.write_to_log_file_n('Updated '||sql%rowcount||' rows '||get_time);
938: end if;
939: end if;
940: if p_periodicity_missing then
941: --we need to do post processing

Line 977: BSC_im_utils.write_to_log_file_n('Data already in rpt cal keys.');

973: end loop;
974: end if;
975: else
976: if g_file and g_debug then
977: BSC_im_utils.write_to_log_file_n('Data already in rpt cal keys.');
978: end if;
979: end if;
980: return true;
981: Exception when others then

Line 985: BSC_im_utils.write_to_log_file_n(g_status_message);

981: Exception when others then
982: g_status_message:=sqlerrm;
983: g_status:=-1;
984: if g_file and g_debug then
985: BSC_im_utils.write_to_log_file_n(g_status_message);
986: end if;
987: return false;
988: End;
989:

Line 1015: BSC_im_utils.write_to_log_file_n('In correct_rpt_cal_keys,p_xtd_periodicity='||p_xtd_periodicity||

1011: -----
1012: --
1013: Begin
1014: if g_file and g_debug then
1015: BSC_im_utils.write_to_log_file_n('In correct_rpt_cal_keys,p_xtd_periodicity='||p_xtd_periodicity||
1016: ',p_periodicity_missing='||p_periodicity_missing||',p_periodicity_present='||p_periodicity_present||
1017: ',p_calendar_id='||p_calendar_id);
1018: end if;
1019: open c1(p_periodicity_missing);

Line 1026: BSC_im_utils.write_to_log_file_n('Missing period type id and db column name');

1022: open c1(p_periodicity_present);
1023: fetch c1 into l_pid_present,l_periodicity_present,l_db_column_present;
1024: close c1;
1025: if g_file and g_debug then
1026: BSC_im_utils.write_to_log_file_n('Missing period type id and db column name');
1027: BSC_im_utils.write_to_log_file(l_pid_missing||' '||l_periodicity_missing||' '||l_db_column_missing);
1028: BSC_im_utils.write_to_log_file_n('Present period type id and db column name');
1029: BSC_im_utils.write_to_log_file(l_pid_present||' '||l_periodicity_present||' '||l_db_column_present);
1030: end if;

Line 1027: BSC_im_utils.write_to_log_file(l_pid_missing||' '||l_periodicity_missing||' '||l_db_column_missing);

1023: fetch c1 into l_pid_present,l_periodicity_present,l_db_column_present;
1024: close c1;
1025: if g_file and g_debug then
1026: BSC_im_utils.write_to_log_file_n('Missing period type id and db column name');
1027: BSC_im_utils.write_to_log_file(l_pid_missing||' '||l_periodicity_missing||' '||l_db_column_missing);
1028: BSC_im_utils.write_to_log_file_n('Present period type id and db column name');
1029: BSC_im_utils.write_to_log_file(l_pid_present||' '||l_periodicity_present||' '||l_db_column_present);
1030: end if;
1031: l_stmt:='insert into bsc_rpt_keys(session_id,report_date,xtd_periodicity,xtd_period,xtd_year,period,year,'||

Line 1028: BSC_im_utils.write_to_log_file_n('Present period type id and db column name');

1024: close c1;
1025: if g_file and g_debug then
1026: BSC_im_utils.write_to_log_file_n('Missing period type id and db column name');
1027: BSC_im_utils.write_to_log_file(l_pid_missing||' '||l_periodicity_missing||' '||l_db_column_missing);
1028: BSC_im_utils.write_to_log_file_n('Present period type id and db column name');
1029: BSC_im_utils.write_to_log_file(l_pid_present||' '||l_periodicity_present||' '||l_db_column_present);
1030: end if;
1031: l_stmt:='insert into bsc_rpt_keys(session_id,report_date,xtd_periodicity,xtd_period,xtd_year,period,year,'||
1032: --'period_type_id,periodicity_id,period_flag,period_day_count,last_update_date) '||

Line 1029: BSC_im_utils.write_to_log_file(l_pid_present||' '||l_periodicity_present||' '||l_db_column_present);

1025: if g_file and g_debug then
1026: BSC_im_utils.write_to_log_file_n('Missing period type id and db column name');
1027: BSC_im_utils.write_to_log_file(l_pid_missing||' '||l_periodicity_missing||' '||l_db_column_missing);
1028: BSC_im_utils.write_to_log_file_n('Present period type id and db column name');
1029: BSC_im_utils.write_to_log_file(l_pid_present||' '||l_periodicity_present||' '||l_db_column_present);
1030: end if;
1031: l_stmt:='insert into bsc_rpt_keys(session_id,report_date,xtd_periodicity,xtd_period,xtd_year,period,year,'||
1032: --'period_type_id,periodicity_id,period_flag,period_day_count,last_update_date) '||
1033: 'period_type_id,periodicity_id,period_flag,day_count,rolling_flag,last_update_date) '||

Line 1042: BSC_im_utils.write_to_log_file_n(l_stmt||' using '||l_pid_present||','||l_periodicity_present||',sysdate,'||

1038: p_table_name||' rpt where cal.'||l_db_column_missing||'='||
1039: 'rpt.period and cal.year=rpt.year and rpt.period_type_id=:6 and rpt.report_date=:7 and rpt.session_id=:8 and '||
1040: 'rpt.xtd_periodicity=:9 and rpt.rolling_flag=:10';
1041: if g_file and g_debug then
1042: BSC_im_utils.write_to_log_file_n(l_stmt||' using '||l_pid_present||','||l_periodicity_present||',sysdate,'||
1043: p_calendar_id||','||l_pid_missing||','||p_report_date||','||g_session_id||','||p_xtd_periodicity||','||p_roll_flag);
1044: BSC_im_utils.write_to_log_file(p_report_date);
1045: end if;
1046: --need to make compatible with 8i. cannot have forall stmt. 8i does not allow execute immediate inside forall

Line 1044: BSC_im_utils.write_to_log_file(p_report_date);

1040: 'rpt.xtd_periodicity=:9 and rpt.rolling_flag=:10';
1041: if g_file and g_debug then
1042: BSC_im_utils.write_to_log_file_n(l_stmt||' using '||l_pid_present||','||l_periodicity_present||',sysdate,'||
1043: p_calendar_id||','||l_pid_missing||','||p_report_date||','||g_session_id||','||p_xtd_periodicity||','||p_roll_flag);
1044: BSC_im_utils.write_to_log_file(p_report_date);
1045: end if;
1046: --need to make compatible with 8i. cannot have forall stmt. 8i does not allow execute immediate inside forall
1047: --forall i in 1..p_num_report_date
1048: execute immediate l_stmt using l_pid_present,l_periodicity_present,sysdate,p_calendar_id,

Line 1051: BSC_im_utils.write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);

1047: --forall i in 1..p_num_report_date
1048: execute immediate l_stmt using l_pid_present,l_periodicity_present,sysdate,p_calendar_id,
1049: l_pid_missing,p_report_date,g_session_id,p_xtd_periodicity,p_roll_flag;
1050: if g_file and g_debug then
1051: BSC_im_utils.write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
1052: end if;
1053: l_stmt:='delete '||p_table_name||' where session_id=:1 and report_date=:2 and xtd_periodicity=:3 and '||
1054: 'period_type_id=:4 and rolling_flag=:5';
1055: if g_file and g_debug then

Line 1056: BSC_im_utils.write_to_log_file_n(l_stmt||' '||g_session_id||',p_report_date,'||p_xtd_periodicity||

1052: end if;
1053: l_stmt:='delete '||p_table_name||' where session_id=:1 and report_date=:2 and xtd_periodicity=:3 and '||
1054: 'period_type_id=:4 and rolling_flag=:5';
1055: if g_file and g_debug then
1056: BSC_im_utils.write_to_log_file_n(l_stmt||' '||g_session_id||',p_report_date,'||p_xtd_periodicity||
1057: l_pid_missing||','||p_roll_flag);
1058: BSC_im_utils.write_to_log_file(p_report_date);
1059: end if;
1060: --need to make compatible with 8i. cannot have forall stmt. 8i does not allow execute immediate inside forall

Line 1058: BSC_im_utils.write_to_log_file(p_report_date);

1054: 'period_type_id=:4 and rolling_flag=:5';
1055: if g_file and g_debug then
1056: BSC_im_utils.write_to_log_file_n(l_stmt||' '||g_session_id||',p_report_date,'||p_xtd_periodicity||
1057: l_pid_missing||','||p_roll_flag);
1058: BSC_im_utils.write_to_log_file(p_report_date);
1059: end if;
1060: --need to make compatible with 8i. cannot have forall stmt. 8i does not allow execute immediate inside forall
1061: --forall i in 1..p_num_report_date
1062: execute immediate l_stmt using g_session_id,p_report_date,p_xtd_periodicity,l_pid_missing,p_roll_flag;

Line 1064: BSC_im_utils.write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);

1060: --need to make compatible with 8i. cannot have forall stmt. 8i does not allow execute immediate inside forall
1061: --forall i in 1..p_num_report_date
1062: execute immediate l_stmt using g_session_id,p_report_date,p_xtd_periodicity,l_pid_missing,p_roll_flag;
1063: if g_file and g_debug then
1064: BSC_im_utils.write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
1065: end if;
1066: return true;
1067: Exception when others then
1068: g_status_message:=sqlerrm;

Line 1071: BSC_im_utils.write_to_log_file_n(g_status_message);

1067: Exception when others then
1068: g_status_message:=sqlerrm;
1069: g_status:=-1;
1070: if g_file and g_debug then
1071: BSC_im_utils.write_to_log_file_n(g_status_message);
1072: end if;
1073: return false;
1074: End;
1075:

Line 1125: BSC_im_utils.write_to_log_file_n('In populate_rolling_rpt_keys');

1121: l_roll_date date;
1122: ----
1123: Begin
1124: if g_file and g_debug then
1125: BSC_im_utils.write_to_log_file_n('In populate_rolling_rpt_keys');
1126: end if;
1127: --get xtd count
1128: if g_debug then
1129: BSC_im_utils.write_to_log_file_n('p_xtd_periodicity='||p_xtd_periodicity);

Line 1129: BSC_im_utils.write_to_log_file_n('p_xtd_periodicity='||p_xtd_periodicity);

1125: BSC_im_utils.write_to_log_file_n('In populate_rolling_rpt_keys');
1126: end if;
1127: --get xtd count
1128: if g_debug then
1129: BSC_im_utils.write_to_log_file_n('p_xtd_periodicity='||p_xtd_periodicity);
1130: for i in 1..p_num_periodicity loop
1131: BSC_im_utils.write_to_log_file('p_periodicity(i)='||p_periodicity(i)||',p_period_num_of_periods(i)='||p_period_num_of_periods(i));
1132: end loop;
1133: end if;

Line 1131: BSC_im_utils.write_to_log_file('p_periodicity(i)='||p_periodicity(i)||',p_period_num_of_periods(i)='||p_period_num_of_periods(i));

1127: --get xtd count
1128: if g_debug then
1129: BSC_im_utils.write_to_log_file_n('p_xtd_periodicity='||p_xtd_periodicity);
1130: for i in 1..p_num_periodicity loop
1131: BSC_im_utils.write_to_log_file('p_periodicity(i)='||p_periodicity(i)||',p_period_num_of_periods(i)='||p_period_num_of_periods(i));
1132: end loop;
1133: end if;
1134: for i in 1..p_num_periodicity loop
1135: if p_xtd_periodicity=p_periodicity(i) then

Line 1144: BSC_im_utils.write_to_log_file_n('l_num_periods ='||l_num_periods);

1140: if l_num_periods is null then
1141: null;
1142: end if;
1143: if g_debug then
1144: BSC_im_utils.write_to_log_file_n('l_num_periods ='||l_num_periods);
1145: end if;
1146: ---------hard coded part------------------
1147: if l_num_periods=1 then --rolling year
1148: l_roll_range:=g_roll_year_range;

Line 1159: BSC_im_utils.write_to_log_file('l_roll_range='||l_roll_range);

1155: else
1156: return false;
1157: end if;
1158: if g_file and g_debug then
1159: BSC_im_utils.write_to_log_file('l_roll_range='||l_roll_range);
1160: end if;
1161: ------------------------------------------
1162: for i in 1..p_num_report_date loop
1163: --

Line 1169: BSC_im_utils.write_to_log_file_n('Only XTD');

1165: --
1166: if l_xtd_count=l_roll_range then
1167: --XTD
1168: if g_file and g_debug then
1169: BSC_im_utils.write_to_log_file_n('Only XTD');
1170: end if;
1171: return true;
1172: else
1173: l_roll_date:=p_report_date(i)-l_roll_range+1;

Line 1175: BSC_im_utils.write_to_log_file('l_roll_date='||l_roll_date);

1171: return true;
1172: else
1173: l_roll_date:=p_report_date(i)-l_roll_range+1;
1174: if g_file and g_debug then
1175: BSC_im_utils.write_to_log_file('l_roll_date='||l_roll_date);
1176: end if;
1177: --load RTD
1178: --for rolling, there should be no missing periodicities since the calendar will have all
1179: --the periodicities, daily,weekly,montly,quarterly and yearly

Line 1201: BSC_im_utils.write_to_log_file_n('XTD+RTD...most common case');

1197: end if;
1198: if l_xtd_count 1199: --xtd+rtd. this will be most common case
1200: if g_file and g_debug then
1201: BSC_im_utils.write_to_log_file_n('XTD+RTD...most common case');
1202: end if;
1203: --if the type is rolling and this is week, need to make sure that
1204: --l_rtd_count+l_xtd_count=7. we need to test for this only if
1205: --l_roll_date is between 26 dec and 31 dec

Line 1213: BSC_im_utils.write_to_log_file_n('WTD and l_roll_date >= 26 dec');

1209: test, we will check specifically these cases . the api correct_rolling_data is generic though*/
1210: if l_roll_range=g_roll_week_range then
1211: if l_roll_date > to_date('12/25/'||to_char(l_roll_date,'YYYY'),'MM/DD/YYYY') then
1212: if g_file and g_debug then
1213: BSC_im_utils.write_to_log_file_n('WTD and l_roll_date >= 26 dec');
1214: end if;
1215: l_rtd_count:=get_day_count(p_table_name,p_session_id,p_report_date(i),'Y');
1216: if l_xtd_count+l_rtd_count < g_roll_week_range then
1217: if g_debug then

Line 1218: BSC_im_utils.write_to_log_file('l_xtd_count('||l_xtd_count||')+l_rtd_count('||l_rtd_count||') < '||

1214: end if;
1215: l_rtd_count:=get_day_count(p_table_name,p_session_id,p_report_date(i),'Y');
1216: if l_xtd_count+l_rtd_count < g_roll_week_range then
1217: if g_debug then
1218: BSC_im_utils.write_to_log_file('l_xtd_count('||l_xtd_count||')+l_rtd_count('||l_rtd_count||') < '||
1219: 'g_roll_week_range('||g_roll_week_range||')');
1220: end if;
1221: --4968072
1222: correct_rolling_data(p_table_name,p_session_id,p_report_date(i),l_roll_date,p_xtd_periodicity,p_xtd_period(i),

Line 1229: BSC_im_utils.write_to_log_file_n('MTD and l_roll_date = 31 Jan');

1225: end if;
1226: elsif l_roll_range=g_roll_period_range then /*mtd and march 1 and feb contains 28 days. we skip the test for feb 28 days */
1227: if l_roll_date = to_date('01/31/'||to_char(l_roll_date,'YYYY'),'MM/DD/YYYY') then
1228: if g_file and g_debug then
1229: BSC_im_utils.write_to_log_file_n('MTD and l_roll_date = 31 Jan');
1230: end if;
1231: l_rtd_count:=get_day_count(p_table_name,p_session_id,p_report_date(i),'Y');
1232: if l_xtd_count+l_rtd_count < g_roll_period_range then
1233: if g_debug then

Line 1234: BSC_im_utils.write_to_log_file('l_xtd_count('||l_xtd_count||')+l_rtd_count('||l_rtd_count||') < '||

1230: end if;
1231: l_rtd_count:=get_day_count(p_table_name,p_session_id,p_report_date(i),'Y');
1232: if l_xtd_count+l_rtd_count < g_roll_period_range then
1233: if g_debug then
1234: BSC_im_utils.write_to_log_file('l_xtd_count('||l_xtd_count||')+l_rtd_count('||l_rtd_count||') < '||
1235: 'g_roll_period_range('||g_roll_period_range||')');
1236: end if;
1237: --4968072
1238: correct_rolling_data(p_table_name,p_session_id,p_report_date(i),l_roll_date,p_xtd_periodicity,p_xtd_period(i),

Line 1248: BSC_im_utils.write_to_log_file_n('RTD count='||l_rtd_count);

1244: else
1245: --now, get the RTD count
1246: l_rtd_count:=get_day_count(p_table_name,p_session_id,p_report_date(i),'Y');
1247: if g_file and g_debug then
1248: BSC_im_utils.write_to_log_file_n('RTD count='||l_rtd_count);
1249: end if;
1250: if l_rtd_count<=l_roll_range then --like 31 mar or 92 day of qtr, 366 day of year
1251: --only RTD, delete XTD
1252: if g_file and g_debug then

Line 1253: BSC_im_utils.write_to_log_file_n('Only RTD');

1249: end if;
1250: if l_rtd_count<=l_roll_range then --like 31 mar or 92 day of qtr, 366 day of year
1251: --only RTD, delete XTD
1252: if g_file and g_debug then
1253: BSC_im_utils.write_to_log_file_n('Only RTD');
1254: end if;
1255: delete_table(p_table_name,p_session_id,p_report_date(i),'N');
1256: else
1257: --l_xtd_count>l_roll_range and l_rtd_count>l_roll_range

Line 1261: BSC_im_utils.write_to_log_file_n('Complex...XTD + Delete OR RTD + Delete');

1257: --l_xtd_count>l_roll_range and l_rtd_count>l_roll_range
1258: --complex case. very few. like 91 day of a 92 day qtr. neither fully xtd or rtd
1259: --first, delete the RTD
1260: if g_file and g_debug then
1261: BSC_im_utils.write_to_log_file_n('Complex...XTD + Delete OR RTD + Delete');
1262: end if;
1263: correct_rolling_data_92_91(p_table_name);
1264: end if;
1265: end if;

Line 1273: BSC_im_utils.write_to_log_file_n(g_status_message);

1269: Exception when others then
1270: g_status_message:=sqlerrm;
1271: g_status:=-1;
1272: if g_file and g_debug then
1273: BSC_im_utils.write_to_log_file_n(g_status_message);
1274: end if;
1275: return false;
1276: End;
1277:

Line 1289: BSC_im_utils.write_to_log_file_n(l_stmt);

1285: --delete the row for month Sep for roll=Y
1286: l_stmt:='delete '||p_table_name||' where period_type_id=32 and rolling_flag=''Y'' and period in '||
1287: '(select max(period) from '||p_table_name||' where period_type_id=32 and rolling_flag=''Y'')';
1288: if g_file and g_debug then
1289: BSC_im_utils.write_to_log_file_n(l_stmt);
1290: end if;
1291: execute immediate l_stmt;
1292: --delete the row for month Aug for roll=Y. aug is present for roll=N
1293: l_stmt:='delete '||p_table_name||' where period_type_id=32 and rolling_flag=''Y'' and period in '||

Line 1296: BSC_im_utils.write_to_log_file_n(l_stmt);

1292: --delete the row for month Aug for roll=Y. aug is present for roll=N
1293: l_stmt:='delete '||p_table_name||' where period_type_id=32 and rolling_flag=''Y'' and period in '||
1294: '(select max(period) from '||p_table_name||' where period_type_id=32 and rolling_flag=''Y'')';
1295: if g_file and g_debug then
1296: BSC_im_utils.write_to_log_file_n(l_stmt);
1297: end if;
1298: execute immediate l_stmt;
1299: --delete the row for July for roll=N
1300: l_stmt:='delete '||p_table_name||' where period_type_id=32 and rolling_flag=''N'' and period in '||

Line 1303: BSC_im_utils.write_to_log_file_n(l_stmt);

1299: --delete the row for July for roll=N
1300: l_stmt:='delete '||p_table_name||' where period_type_id=32 and rolling_flag=''N'' and period in '||
1301: '(select min(period) from '||p_table_name||' where period_type_id=32 and rolling_flag=''N'')';
1302: if g_file and g_debug then
1303: BSC_im_utils.write_to_log_file_n(l_stmt);
1304: end if;
1305: execute immediate l_stmt;
1306: Exception when others then
1307: g_status_message:=sqlerrm;

Line 1310: BSC_im_utils.write_to_log_file_n('Error in correct_rolling_data_92_91 '||g_status_message);

1306: Exception when others then
1307: g_status_message:=sqlerrm;
1308: g_status:=-1;
1309: if g_file and g_debug then
1310: BSC_im_utils.write_to_log_file_n('Error in correct_rolling_data_92_91 '||g_status_message);
1311: end if;
1312: raise;
1313: End;
1314:

Line 1375: BSC_im_utils.write_to_log_file_n('In correct_rolling_data xtd date='||to_char(p_xtd_report_date,'MM/DD/YYYY')||

1371: l_Bs_date date;
1372: l_As_date date;
1373: Begin
1374: if g_file and g_debug then
1375: BSC_im_utils.write_to_log_file_n('In correct_rolling_data xtd date='||to_char(p_xtd_report_date,'MM/DD/YYYY')||
1376: ', rtd date='||to_char(p_rtd_report_date,'MM/DD/YYYY')||' and periodicity='||p_xtd_periodicity);
1377: end if;
1378: l_day_count:=get_day_count(p_table_name,p_session_id,p_xtd_report_date,'N');
1379: l_As_date:=p_xtd_report_date-l_day_count+1;

Line 1384: BSC_im_utils.write_to_log_file('l_As_date='||to_char(l_As_date,'MM/DD/YYYY')||', l_Bs_date='||to_char(l_Bs_date,'MM/DD/YYYY'));

1380: l_day_count:=get_day_count(p_table_name,p_session_id,p_xtd_report_date,'Y');
1381: l_prev_day_count:=l_day_count;
1382: l_Bs_date:=p_rtd_report_date+l_day_count-1;
1383: if g_file and g_debug then
1384: BSC_im_utils.write_to_log_file('l_As_date='||to_char(l_As_date,'MM/DD/YYYY')||', l_Bs_date='||to_char(l_Bs_date,'MM/DD/YYYY'));
1385: end if;
1386: l_Bs_date:=l_Bs_date+1;
1387: if l_Bs_date 1388: loop

Line 1410: BSC_im_utils.write_to_log_file('Loop done , day count='||l_day_count);

1406: end if;
1407: l_day_count:=get_day_count(p_table_name,p_session_id,p_xtd_report_date,'Y');
1408: l_day_count:=l_day_count-l_prev_day_count;
1409: if g_file and g_debug then
1410: BSC_im_utils.write_to_log_file('Loop done , day count='||l_day_count);
1411: end if;
1412: if l_day_count>0 then
1413: l_prev_day_count:=l_prev_day_count+l_day_count;
1414: l_Bs_date:=l_Bs_date+l_day_count;

Line 1416: BSC_im_utils.write_to_log_file('New l_Bs_date='||to_char(l_Bs_date,'MM/DD/YYYY'));

1412: if l_day_count>0 then
1413: l_prev_day_count:=l_prev_day_count+l_day_count;
1414: l_Bs_date:=l_Bs_date+l_day_count;
1415: if g_file and g_debug then
1416: BSC_im_utils.write_to_log_file('New l_Bs_date='||to_char(l_Bs_date,'MM/DD/YYYY'));
1417: end if;
1418: if l_Bs_date>=l_As_date then
1419: exit;
1420: end if;

Line 1430: BSC_im_utils.write_to_log_file_n('Error in correct_rolling_data '||g_status_message);

1426: Exception when others then
1427: g_status_message:=sqlerrm;
1428: g_status:=-1;
1429: if g_file and g_debug then
1430: BSC_im_utils.write_to_log_file_n('Error in correct_rolling_data '||g_status_message);
1431: end if;
1432: raise;
1433: End;
1434:

Line 1449: BSC_im_utils.write_to_log_file_n(l_stmt||' '||p_session_id||' '||p_report_date||' '||p_roll_flag);

1445: l_xtd_count number;
1446: Begin
1447: l_stmt:='select sum(day_count) from '||p_table_name||' where session_id=:1 and report_date=:2 and rolling_flag=:3';
1448: if g_file and g_debug then
1449: BSC_im_utils.write_to_log_file_n(l_stmt||' '||p_session_id||' '||p_report_date||' '||p_roll_flag);
1450: end if;
1451: open cv for l_stmt using p_session_id,p_report_date,p_roll_flag;
1452: fetch cv into l_xtd_count;
1453: close cv;

Line 1455: BSC_im_utils.write_to_log_file('l_xtd_count='||l_xtd_count);

1451: open cv for l_stmt using p_session_id,p_report_date,p_roll_flag;
1452: fetch cv into l_xtd_count;
1453: close cv;
1454: if g_file and g_debug then
1455: BSC_im_utils.write_to_log_file('l_xtd_count='||l_xtd_count);
1456: end if;
1457: return l_xtd_count;
1458: Exception when others then
1459: if g_file and g_debug then

Line 1460: BSC_im_utils.write_to_log_file_n(sqlerrm);

1456: end if;
1457: return l_xtd_count;
1458: Exception when others then
1459: if g_file and g_debug then
1460: BSC_im_utils.write_to_log_file_n(sqlerrm);
1461: end if;
1462: return null;
1463: End;
1464:

Line 1476: BSC_im_utils.write_to_log_file_n(l_stmt||' '||p_session_id||' '||p_report_date||' '||p_roll_flag);

1472: --
1473: Begin
1474: l_stmt:='delete '||p_table_name||' where session_id=:1 and report_date=:2 and rolling_flag=:3';
1475: if g_file and g_debug then
1476: BSC_im_utils.write_to_log_file_n(l_stmt||' '||p_session_id||' '||p_report_date||' '||p_roll_flag);
1477: end if;
1478: execute immediate l_stmt using p_session_id,p_report_date,p_roll_flag;
1479: if g_file and g_debug then
1480: BSC_im_utils.write_to_log_file_n('Deleted '||sql%rowcount||' rows ');

Line 1480: BSC_im_utils.write_to_log_file_n('Deleted '||sql%rowcount||' rows ');

1476: BSC_im_utils.write_to_log_file_n(l_stmt||' '||p_session_id||' '||p_report_date||' '||p_roll_flag);
1477: end if;
1478: execute immediate l_stmt using p_session_id,p_report_date,p_roll_flag;
1479: if g_file and g_debug then
1480: BSC_im_utils.write_to_log_file_n('Deleted '||sql%rowcount||' rows ');
1481: end if;
1482: Exception when others then
1483: if g_file and g_debug then
1484: BSC_im_utils.write_to_log_file_n(sqlerrm);

Line 1484: BSC_im_utils.write_to_log_file_n(sqlerrm);

1480: BSC_im_utils.write_to_log_file_n('Deleted '||sql%rowcount||' rows ');
1481: end if;
1482: Exception when others then
1483: if g_file and g_debug then
1484: BSC_im_utils.write_to_log_file_n(sqlerrm);
1485: end if;
1486: raise;
1487: End;
1488:

Line 1550: BSC_IM_UTILS.open_file('TEST');

1546: End;
1547:
1548: procedure open_file is
1549: Begin
1550: BSC_IM_UTILS.open_file('TEST');
1551: Exception when others then
1552: raise;
1553: End;
1554: