1 package body BSC_BSC_XTD_PKG AS
2 /*$Header: BSCRPTCB.pls 120.12 2006/02/14 11:56:38 vsurendr noship $*/
3
4 --public
5 procedure create_rpt_key_table(
6 p_user_id number,
7 p_table_name out nocopy varchar2,
8 p_error_message out nocopy varchar2
9 )is
10 l_stmt varchar2(2000);
11 l_col_list varchar2(1000);
12 Begin
13 --clear cache, added for bug 4652655
14 if g_num_kpi_xtd is not null and g_num_kpi_xtd>0 then
15 g_kpi_xtd.delete;
16 end if;
17 g_num_kpi_xtd:=0;
18 --Changed by Arun, 10/14/2005 due to perf. concerns raised by Mandar, Bug 4676527
19 p_table_name := 'bsc_rpt_keys';
20
21 Exception when others then
22 if sqlcode=-00955 then
23 --do nothing
24 return;
25 end if;
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
35 procedure drop_rpt_key_table(
36 p_user_id number,
37 p_error_message out nocopy varchar2
38 )is
39 l_stmt varchar2(1000);
40 cursor c1 is SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE 'BSC_RPT_KEYS_%' AND TEMPORARY='Y' AND OWNER = BSC_APPS.get_user_schema;
41 l_table varchar2(100);
42 Begin
43 -- Changed by Arun for bug/enh 4676527
44 delete bsc_rpt_keys;
45 --clear cache, added for bug 4652655
46 if g_num_kpi_xtd is not null and g_num_kpi_xtd>0 then
47 g_kpi_xtd.delete;
48 end if;
49 g_num_kpi_xtd:=0;
50 Exception when others then
51 null;
52 End;
53
54 --public
55 procedure delete_rpt_keys(
56 p_table_name varchar2,
57 p_session_id number,
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);
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;
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;
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
87 /*
88 ---------------------------------
89 Public api. called from front end tools like iviewer and pmv
90 backward compatibility. no rolling supported, only xtd
91 ---------------------------------
92 */
93 procedure populate_rpt_keys(
94 p_table_name varchar2,
95 p_session_id number,
96 p_kpi number,
97 p_report_date varchar2,
98 p_xtd_period varchar2,
99 p_xtd_year varchar2,
100 p_xtd_periodicity number,
101 p_option_string varchar2,
102 p_error_message out nocopy varchar2
103 )is
104 Begin
105 populate_rpt_keys(p_table_name,p_session_id,p_kpi,p_report_date,p_xtd_period,p_xtd_year,p_xtd_periodicity,
106 'XTD',p_option_string,p_error_message);
107 Exception when others then
108 g_status_message:=sqlerrm;
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 ---------------------------------
118 Public api. called from front end tools like iviewer and pmv
119 rolling vs std xtd specified
120 ---------------------------------
121 */
122 procedure populate_rpt_keys(
123 p_table_name varchar2,
124 p_session_id number,
125 p_kpi number,
126 p_report_date varchar2,
127 p_xtd_period varchar2,
128 p_xtd_year varchar2,
129 p_xtd_periodicity number,
130 p_xtd_type varchar2,--ROLLING vs XTD
131 p_option_string varchar2,
132 p_error_message out nocopy varchar2
133 )is
134 ---------------------
135 l_report_date date_tabletype;
136 l_xtd_period number_tabletype;
137 l_xtd_year number_tabletype;
138 l_num_report_date number;
139 ll_report_date varchar_tabletype;--temp
140 ll_num_report_date number;--temp
141 ll_report_date_flag boolean_tabletype;
142 ---------------------
143 e_error EXCEPTION;
144 l_num number;
145 ---------------------
146 Begin
147 g_session_id:=p_session_id;
148 g_option_string:=p_option_string;
149 g_status:=0;
150 if g_num_kpi_xtd is null then
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;
160
161 --parse the dates out
162 if parse_values(p_report_date,':',ll_report_date,ll_num_report_date)=false then
163 raise e_error;
164 end if;
165 if parse_values(p_xtd_period,':',l_xtd_period,l_num)=false then
166 raise e_error;
167 end if;
168 if l_num<ll_num_report_date then
169 for i in l_num+1..ll_num_report_date loop
170 l_xtd_period(i):=l_xtd_period(i-1);
171 end loop;
172 end if;
173 if parse_values(p_xtd_year,':',l_xtd_year,l_num)=false then
174 raise e_error;
175 end if;
176 if l_num<ll_num_report_date then
177 for i in l_num+1..ll_num_report_date loop
178 l_xtd_year(i):=l_xtd_year(i-1);
179 end loop;
180 end if;
181 --first see if data is already cached
182 for i in 1..ll_num_report_date loop
183 ll_report_date_flag(i):=true;
184 for j in 1..g_num_kpi_xtd loop
185 if g_kpi_xtd(j).session_id=p_session_id and g_kpi_xtd(j).kpi=p_kpi
186 and g_kpi_xtd(j).report_date=to_date(ll_report_date(i),'MM/DD/YYYY')
187 and g_kpi_xtd(j).xtd_periodicity=p_xtd_periodicity then
188 ll_report_date_flag(i):=false;
189 exit;
190 end if;
191 end loop;
192 end loop;
193 l_num_report_date:=0;
194 for i in 1..ll_num_report_date loop
195 if ll_report_date_flag(i) then
196 l_num_report_date:=l_num_report_date+1;
197 l_report_date(l_num_report_date):=to_date(ll_report_date(i),'MM/DD/YYYY');
198 l_xtd_period(l_num_report_date):=l_xtd_period(i);
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;
208 if l_num_report_date>0 then
209 if populate_rpt_keys(
210 p_table_name,
211 p_session_id,
212 p_kpi,
213 l_report_date,
214 l_num_report_date,
215 l_xtd_period,
216 l_xtd_year,
217 p_xtd_periodicity,
218 p_xtd_type
219 )=false then
220 raise e_error;
221 end if;
222 end if;
223 --Reverting back enh. after discussion b/w Arun/Kiran/Pramod/Venu
224 --added by arun for enh 4708622
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;
234 p_error_message:=g_status_message;
235 rollback;
236 when others then
237 g_status_message:=sqlerrm;
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(
247 p_table_name varchar2,--name of the rpt_cal_keys table
248 p_session_id number,
249 p_kpi number,
250 p_report_date date_tabletype,
251 p_num_report_date number,
252 p_xtd_period number_tabletype,--use p_num_report_date for count
253 p_xtd_year number_tabletype,--use p_num_report_date for count
254 p_xtd_periodicity number,
255 p_xtd_type varchar2
256 )return boolean is
257 ------
258 cursor c1(p_kpi number) is
259 select bsc_kpi_periodicities.periodicity_id,calendar_id,period_type_id,db_column_name,num_of_periods
260 from bsc_kpi_periodicities ,bsc_sys_periodicities
261 where indicator=p_kpi and bsc_kpi_periodicities.periodicity_id=bsc_sys_periodicities.periodicity_id
262 order by indicator,display_order desc;
263 cursor c2(p_xtd_periodicity number) is
264 select xtd_pattern from bsc_sys_periodicities where periodicity_id=p_xtd_periodicity;
265 cursor c3(p_calendar_id number) is
266 select decode(edw_calendar_id,1001,decode(edw_calendar_type_id,1,'DBI-ENT',null),null) from bsc_sys_calendars_b
267 where calendar_id=p_calendar_id;
268 ------
269 l_periodicity number_tabletype;
270 l_period_type_id number_tabletype;
271 l_period_column_name varchar_tabletype;
272 l_period_num_of_periods number_tabletype;--needed for rolling
273 l_num_periodicity number;
274 --
275 --3919980
276 l_orig_periodicity number_tabletype;
277 l_orig_period_type_id number_tabletype;
278 l_orig_period_column_name varchar_tabletype;
279 l_orig_period_num_of_periods number_tabletype;--needed for rolling
280 l_orig_num_periodicity number;
281 --
282 l_periodicity_string varchar2(200);
283 l_xtd_pattern varchar2(4000);
284 l_xtd_pattern_value number;--this is what is used to BITAND
285 l_hier varchar2(4000);
286 l_calendar_id number;
287 l_pattern varchar_tabletype;
288 l_num_pattern number;
289 ------
290 l_index number;
291 l_start number;
292 l_end number;
293 l_num number;
294 l_count number;
295 l_max_count number:=200;
296 l_found boolean;
297 l_found2 boolean;
298 ------
299 l_pattern_period_pattern number_tabletype;
300 l_pattern_period_periodicity number_tabletype;
301 l_pattern_period_missing boolean_tabletype;
302 l_num_pattern_period number;
303 l_use_pattern boolean;
304 l_pattern_to_use number;
305 l_periodicity_missing boolean;
306 ------
307 --just temp storage
308 ll_temp_period_pattern number_tabletype;--pattern number
309 ll_temp_period_periodicity number_tabletype;--the periodicity in the pattern
310 ll_temp_period_missing boolean_tabletype;--missing or not
311 ll_num_temp_pattern_period number:=0;
312 ------
313 l_rank number_tabletype;
314 l_max_rank number;
315 l_min_rank number;
316 l_max_rank_index number;
317 l_min_rank_index number;
318 ----
319 --support for rolling xtd
320 l_xtd_count number;
321 l_roll_count number;
322 ----
323 e_error exception;
324 l_cal_type varchar2(40);
325 Begin
326 --if this is first time
327 l_periodicity_missing:=false;
328 l_num_periodicity:=1;
329 l_periodicity_string:=null;
330 open c1(p_kpi);
331 loop
332 fetch c1 into l_periodicity(l_num_periodicity),l_calendar_id,l_period_type_id(l_num_periodicity),
333 l_period_column_name(l_num_periodicity),l_period_num_of_periods(l_num_periodicity);
334 exit when c1%notfound;
335 l_num_periodicity:=l_num_periodicity+1;
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;
345 end if;
346 /*
347 4449784: we do not need Day to date. if p_xtd_periodicity is daily, we just need the key to the days record in the MV,
348 not all the keys that can give day to date
349 we can either fix the xtd pattern or the code here. its best we fix it here. xtd pattern can be saved if someone really
350 needs day to date. if daily periodicity, we simply insert one row for this day.
351 open issue: is this functionality needed for the lowest level periodicity. say a kpi is monthly, quarterly, yearly. user chooses
352 MTD. do they expect to see non xtd value, ie, just the month aggregation? for now, we do this only for daily periodicity
353 */
354 if is_daily_periodicity(p_xtd_periodicity) then
355 populate_rpt_keys_daily(p_table_name,p_session_id,l_calendar_id,p_report_date,p_num_report_date,p_xtd_period,p_xtd_year,p_xtd_periodicity,p_xtd_type);
356 else
357 --3919980
358 if p_xtd_type='ROLLING' then
359 l_orig_periodicity:=l_periodicity;
360 l_orig_period_type_id:=l_period_type_id;
361 l_orig_period_column_name:=l_period_column_name;
362 l_orig_period_num_of_periods:=l_period_num_of_periods;
363 l_orig_num_periodicity:=l_num_periodicity;
364 end if;
365 --see if this is a avg measure.
366 if instr(g_option_string,'AVG MEASURE')>0 then
367 --this is an avg column. this means we can only have daily periodicity in the reporting
368 --table
369 --in this case we only perserve 2 periodicities, the xtd and daily
370 declare
371 ll_periodicity number_tabletype;
372 ll_period_type_id number_tabletype;
373 ll_period_column_name varchar_tabletype;
374 ll_num_periodicity number;
375 begin
376 ll_num_periodicity:=0;
377 for i in 1..l_num_periodicity loop
378 if l_periodicity(i)=p_xtd_periodicity then
379 ll_num_periodicity:=ll_num_periodicity+1;
380 ll_periodicity(ll_num_periodicity):=l_periodicity(i);
381 ll_period_type_id(ll_num_periodicity):=l_period_type_id(i);
382 ll_period_column_name(ll_num_periodicity):=l_period_column_name(i);
383 exit;
384 end if;
385 end loop;
386 for i in 1..l_num_periodicity loop
387 if l_period_type_id(i)=1 and l_periodicity(i)<>p_xtd_periodicity then --this is daily
388 ll_num_periodicity:=ll_num_periodicity+1;
389 ll_periodicity(ll_num_periodicity):=l_periodicity(i);
390 ll_period_type_id(ll_num_periodicity):=l_period_type_id(i);
391 ll_period_column_name(ll_num_periodicity):=l_period_column_name(i);
392 exit;
393 end if;
394 end loop;
395 l_num_periodicity:=ll_num_periodicity;
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;
405 end if;
406 end;
407 end if;
408 if l_num_periodicity=0 then
409 g_status_message:='No periodicities found for KPI '||p_kpi;
410 g_status:=-1;
411 return false;
412 end if;
413 for i in 1..l_num_periodicity loop
414 l_periodicity_string:=l_periodicity_string||l_periodicity(i)||',';
415 end loop;
416 l_periodicity_string:=','||l_periodicity_string;
417 open c2(p_xtd_periodicity);
418 fetch c2 into l_xtd_pattern;
419 close c2;
420 if l_xtd_pattern is null then
421 g_status_message:='No XTD pattern found for 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
431 open c3(l_calendar_id);
432 fetch c3 into l_cal_type;
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;
442 return false;
443 end if;
444 l_xtd_pattern_value:=null;
445 --first try the fast way
446 for i in 1..l_num_pattern loop
447 l_index:=instr(l_pattern(i),l_periodicity_string);
448 if l_index>0 then
449 l_start:=instr(l_pattern(i),':',l_index)+1;
450 l_end:=length(l_pattern(i))+1;
451 l_xtd_pattern_value:=substr(l_pattern(i),l_start,l_end-l_start);
452 l_hier:=substr(l_pattern(i),1,l_start-2);
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.
462 declare
463 l_period_parsed number;
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;
473 l_index:=instr(l_pattern(i),','||p_xtd_periodicity||',');
474 l_num_pattern_periodicity:=0;
475 if l_index>0 then
476 --parse out all the periodicities in the pattern starting from p_xtd_periodicity
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);
486 if l_start<=0 then
487 exit;
488 end if;
489 l_start:=l_start+1;
490 l_end:=instr(l_pattern(i),',',l_start);
491 if l_end>0 then
492 l_num_pattern_periodicity:=l_num_pattern_periodicity+1;
493 l_pattern_periodicity(l_num_pattern_periodicity):=substr(l_pattern(i),l_start,l_end-l_start);
494 else
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;
504 end if;
505 --for each pattern, check the periodicities in the kpi and see if they exist and give the rank
506 if l_num_pattern_periodicity > 0 then
507 for j in 1..l_num_pattern_periodicity loop
508 l_found:=false;
509 for k in 1..l_num_periodicity loop
510 if l_periodicity(k)=l_pattern_periodicity(j) then
511 l_found:=true;
512 exit;
513 end if;
514 end loop;
515 if l_found then
516 l_rank(i):=l_rank(i)+1;
517 else
518 l_rank(i):=0;
519 exit;
520 end if;
521 end loop;
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;
531 l_max_rank:=0;
532 l_max_rank_index:=0;
533 for i in 1..l_num_pattern loop
534 if l_rank(i)>l_max_rank then
535 l_max_rank:=l_rank(i);
536 l_max_rank_index:=i;
537 end if;
538 end loop;
539 if l_max_rank_index>0 then
540 l_start:=instr(l_pattern(l_max_rank_index),':')+1;
541 l_end:=length(l_pattern(l_max_rank_index))+1;
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 -----------------------------------------------------
551 if l_xtd_pattern_value is null then
552 /*due to 2 reasons.
553 1. the periodicities arranged in some other 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
563 l_start:=2;
564 l_count:=0;
565 loop
566 l_end:=instr(l_pattern(i),',',l_start);
567 if l_end<=0 then
568 exit;
569 end if;
570 l_num:=substr(l_pattern(i),l_start,l_end-l_start);
571 --l_num_pattern_period will only be used in the section for missing
572 --periodicity
573 l_num_pattern_period:=l_num_pattern_period+1;
574 l_pattern_period_pattern(l_num_pattern_period):=i;
575 l_pattern_period_periodicity(l_num_pattern_period):=l_num;
576 l_pattern_period_missing(l_num_pattern_period):=false;
577 l_found2:=false;--we have to look at at contigous pattern
578 for j in 1..l_num_periodicity loop
579 if l_periodicity(j)=l_num then
580 l_count:=l_count+1;
581 l_found2:=true;
582 exit;
583 end if;
584 end loop;
585 if l_found2=false then
586 l_count:=0;
587 end if;
588 if l_count=l_num_periodicity then
589 --contigous periodicities found. end of search
590 l_start:=instr(l_pattern(i),':',l_end)+1;
591 l_end:=length(l_pattern(i))+1;
592 l_xtd_pattern_value:=substr(l_pattern(i),l_start,l_end-l_start);
593 l_hier:=substr(l_pattern(i),1,l_start-2);
594 l_found:=true;
595 exit;
596 end if;
597 l_start:=l_end+1;
598 end loop;
599 if l_found then
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 ---------------------------------------
609 --find the missing periodicities
610 --need more complex logic due to Bug 3294193
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');
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;
626 if instr(l_pattern(i),','||p_xtd_periodicity||',')>0 then
627 l_found:=false;
628 for j in 1..l_num_pattern_period loop
629 if l_pattern_period_pattern(j)=i then
630 if l_found then --this starts only after we are past p_xtd_periodicity in the de-norm array
631 l_found2:=false;
632 for k in 1..l_num_periodicity loop
633 if l_pattern_period_periodicity(j)=l_periodicity(k) then
634 l_found2:=true;
635 exit;
636 end if;
637 end loop;
638 if l_found2=false then
639 l_rank(i):=l_rank(i)+1;
640 end if;
641 end if;
642 if l_pattern_period_periodicity(j)=p_xtd_periodicity then
643 l_found:=true;
644 end if;
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;
654 l_min_rank_index:=0;
655 for i in 1..l_num_pattern loop
656 if l_rank(i)>0 and l_rank(i)<l_min_rank then
657 l_min_rank:=l_rank(i);
658 l_min_rank_index:=i;
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 '||
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(
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
680 if l_pattern_period_pattern(i)=l_pattern_to_use then
681 l_pattern_period_missing(i):=true;
682 for j in 1..l_num_periodicity loop
683 if l_periodicity(j)=l_pattern_period_periodicity(i) then
684 l_pattern_period_missing(i):=false;
685 exit;
686 end if;
687 end loop;
688 end if;
689 end loop;
690 for i in 1..l_num_pattern_period loop
691 if l_pattern_period_pattern(i)=l_pattern_to_use then
692 ll_num_temp_pattern_period:=ll_num_temp_pattern_period+1;
693 ll_temp_period_pattern(ll_num_temp_pattern_period):=l_pattern_period_pattern(i);
694 ll_temp_period_periodicity(ll_num_temp_pattern_period):=l_pattern_period_periodicity(i);
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;
704 end loop;
705 end if;
706 end if;--if l_xtd_pattern_value is null then
707 ---------------------------------------
708 --missing periodicities handled inside insert_rpt_cal_keys
709 for i in 1..p_num_report_date loop
710 if insert_rpt_cal_keys(
711 p_table_name,
712 p_report_date(i),
713 p_report_date(i),--this is inserted into report_date col in bsc_rpt_keys_table
714 p_xtd_periodicity,
715 p_xtd_period(i),
716 p_xtd_year(i),
717 l_hier,
718 l_xtd_pattern_value,
719 l_calendar_id,
720 'N',
721 l_periodicity_missing,
722 ll_temp_period_periodicity,
723 ll_temp_period_missing,
724 ll_num_temp_pattern_period
725 )=false then
726 return false;
727 end if;
728 end loop;
729 if p_xtd_type='ROLLING' then
730 if populate_rolling_rpt_keys(
731 p_table_name,
732 p_session_id,
733 l_hier,
734 l_xtd_pattern_value,
735 l_calendar_id,
736 p_report_date,
737 p_num_report_date,
738 p_xtd_period,
739 p_xtd_year,
740 p_xtd_periodicity,
741 l_orig_periodicity,--3919980
742 l_orig_period_num_of_periods,--3919980
743 l_orig_num_periodicity,--3919980
744 l_periodicity_missing,
745 ll_temp_period_periodicity,
746 ll_temp_period_missing,
747 ll_num_temp_pattern_period
748 )=false then
749 raise e_error;
750 end if;
751 end if;
752 end if;
753 --add to the global variables
754 for i in 1..p_num_report_date loop
755 if g_num_kpi_xtd=0 then
756 g_kpi_xtd:=xtd_record_table();
757 end if;
758 g_num_kpi_xtd:=g_num_kpi_xtd+1;
759 g_kpi_xtd.extend;
760 g_kpi_xtd(g_num_kpi_xtd).session_id:=p_session_id;
761 g_kpi_xtd(g_num_kpi_xtd).kpi:=p_kpi;
762 g_kpi_xtd(g_num_kpi_xtd).report_date:=p_report_date(i);
763 g_kpi_xtd(g_num_kpi_xtd).xtd_periodicity:=p_xtd_periodicity;
764 /*
765 here we are not caching if its xtd or rolling xtd. we dont want to have a situation
766 where there is rolling xtd and xtd for the same as of date
767 pmv is not filtering on the bsc_rpt_keys table with rolling_flag
768 so if pmv wants to go from rolling xtd on a as of date to xtd on the same as of date, they
769 must first clean up the table
770 */
771 end loop;
772 return true;
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;
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
788 /*
789 Bug 4449784
790 */
791 function is_daily_periodicity(p_periodicity number) return boolean is
792 l_period_type_id number;
793 Begin
794 select period_type_id into l_period_type_id from bsc_sys_periodicities where periodicity_id=p_periodicity;
795 if l_period_type_id=1 then
796 return true;
797 else
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 /*
807 Bug 4449784
808 */
809 procedure populate_rpt_keys_daily(
810 p_table_name varchar2,--name of the rpt_cal_keys table
811 p_session_id number,
812 p_calendar_id number,
813 p_report_date date_tabletype,
814 p_num_report_date number,
815 p_xtd_period number_tabletype,--use p_num_report_date for count
816 p_xtd_year number_tabletype,--use p_num_report_date for count
817 p_xtd_periodicity number,
818 p_xtd_type varchar2
819 ) is
820 l_stmt varchar2(10000);
821 Begin
822 for i in 1..p_num_report_date loop
823 l_stmt:='insert into bsc_rpt_keys(session_id,report_date,xtd_periodicity,'||
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,
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
840 BSC_im_utils.write_to_log_file_n('Error in populate_rpt_keys_daily '||sqlerrm);
841 end if;
842 raise;
843 End;
844
845 function insert_rpt_cal_keys(
846 p_table_name varchar2,
847 p_report_date date,--used to join with bsc_reporting_calendar
848 p_report_date_insert date,--used to insert into bsc_rpt_keys table
849 p_xtd_periodicity number,
850 p_xtd_period number,
851 p_xtd_year number,
852 p_hier varchar2,
853 p_xtd_pattern number,
854 p_calendar_id number,
855 p_roll_flag varchar2,
856 p_periodicity_missing boolean,
857 p_period_periodicity number_tabletype,
858 p_period_missing boolean_tabletype,
859 p_num_pattern_period number
860 )return boolean is
861 ----------
862 l_status number;
863 l_stmt varchar2(4000);
864 ----------
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
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
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.
886 --we keep it here for added security
887 if l_status=-1 then
888 return false;
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||
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,'||
904 'xtd_period,xtd_year,period,year,period_type_id,periodicity_id,period_flag,day_count,rolling_flag,last_update_date) '||
905 'select :1,:2,:3,:4,:5,period,year,period_type_id,periodicity_id,0,day_count,rolling_flag,'||
906 'sysdate from bsc_reporting_calendar where '||
907 'calendar_id=:6 and report_date=:7 and hierarchy=:8 and rolling_flag=:9 '||
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
917 --period_type_id of 1 is daily
918 l_stmt:='update '||p_table_name||' set period_flag=1 '||
919 'where session_id=:1 '||
920 'and report_date=:2 '||
921 'and xtd_periodicity=:3 '||
922 'and period_type_id=1 '||
923 'and rolling_flag=:4 '||
924 'and period=(select max(period) '||
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
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
942 /*
943 two aspects to keep in mind
944 - p_xtd_periodicity cannot be outside the periodicity of the kpi
945 - all kpi periodicity must be in the periodicity of the pattern
946 */
947 l_start:=1;
948 for i in 1..p_num_pattern_period loop
949 if p_period_periodicity(i)=p_xtd_periodicity then
950 l_start:=i+1;
951 exit;
952 end if;
953 end loop;
954 for i in l_start..p_num_pattern_period loop
955 if p_period_missing(i) then
956 for j in i+1..p_num_pattern_period loop
957 if p_period_missing(j)=false then
958 if correct_rpt_cal_keys(
959 p_table_name,
960 p_report_date_insert,
961 p_xtd_periodicity,
962 p_period_periodicity(i),--missing
963 p_period_periodicity(j),--present
964 p_calendar_id,
965 p_roll_flag)=false then
966 --'N')=false then --3919980
967 return false;
968 end if;
969 exit;
970 end if;
971 end loop;
972 end if;
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
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
990 function correct_rpt_cal_keys(
991 p_table_name varchar2,
992 p_report_date date,
993 p_xtd_periodicity number,
994 p_periodicity_missing number,
995 p_periodicity_present number,
996 p_calendar_id number,
997 p_roll_flag varchar2
998 )return boolean is
999 --------
1000 cursor c1(p_periodicity_id number) is
1001 select period_type_id,periodicity_id,db_column_name from bsc_sys_periodicities where periodicity_id=p_periodicity_id;
1002 --------
1003 l_pid_missing number;
1004 l_periodicity_missing number;
1005 l_db_column_missing varchar2(200);
1006 l_pid_present number;
1007 l_periodicity_present number;
1008 l_db_column_present varchar2(200);
1009 -----
1010 l_stmt varchar2(8000);
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);
1020 fetch c1 into l_pid_missing,l_periodicity_missing,l_db_column_missing;
1021 close c1;
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;
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) '||
1034 'select rpt.session_id,rpt.report_date,rpt.xtd_periodicity,'||
1035 'rpt.xtd_period,rpt.xtd_year,cal.'||l_db_column_present||',rpt.year,:2,:3,0,cal.day_count,rpt.rolling_flag,:4 from '||
1036 '(select '||l_db_column_present||','||l_db_column_missing||',year,count(*) day_count from bsc_db_calendar where '||
1037 'calendar_id=:5 group by '||l_db_column_present||','||l_db_column_missing||',year) cal,'||
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
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
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;
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;
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
1076 /*
1077 if rolling is specified, further processing needed
1078 logic...
1079 get xtd_count (from day_count)
1080 if xtd_count=g_roll_count --30 day of month for rolling month etc
1081 XTD
1082 else
1083 load RTD
1084 get roll_count
1085 if xtd_count<g_roll_count --most of the time
1086 XTD+RTD
1087 elsif xtd_count>g_roll_count and roll_count<=g_roll_count --31 of month
1088 RTD
1089 else --very few cases. only 91 day for qtr
1090 --complicated...need to subtract
1091 break up all periodicities to days and then subtract
1092 end if;
1093 end if;
1094 */
1095 function populate_rolling_rpt_keys(
1096 p_table_name varchar2,--name of the rpt_cal_keys table
1097 p_session_id number,
1098 p_hier varchar2,
1099 p_xtd_pattern varchar2,
1100 p_calendar_id number,
1101 p_report_date date_tabletype,
1102 p_num_report_date number,
1103 p_xtd_period number_tabletype,--use p_num_report_date for count
1104 p_xtd_year number_tabletype,--use p_num_report_date for count
1105 p_xtd_periodicity number,
1106 p_periodicity number_tabletype,
1107 p_period_num_of_periods number_tabletype,
1108 p_num_periodicity number,
1109 p_periodicity_missing boolean,
1110 p_period_periodicity number_tabletype,
1111 p_period_missing boolean_tabletype,
1112 p_num_pattern_period number
1113 )return boolean is
1114 --
1115 l_xtd_count number;
1116 l_rtd_count number;
1117 --
1118 ----
1119 l_num_periods number;
1120 l_roll_range number;
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);
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
1136 l_num_periods:=p_period_num_of_periods(i);
1137 exit;
1138 end if;
1139 end loop;
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;
1149 elsif l_num_periods=4 then
1150 l_roll_range:=g_roll_qtr_range;
1151 elsif l_num_periods=12 then
1152 l_roll_range:=g_roll_period_range;
1153 elsif l_num_periods>=52 and l_num_periods<=55 then
1154 l_roll_range:=g_roll_week_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 --
1164 l_xtd_count:=get_day_count(p_table_name,p_session_id,p_report_date(i),'N');
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;
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
1180 if insert_rpt_cal_keys(
1181 p_table_name,
1182 l_roll_date,
1183 p_report_date(i),--this is inserted in report_date col of bsc_rpt_keys table
1184 p_xtd_periodicity,
1185 p_xtd_period(i),
1186 p_xtd_year(i),
1187 p_hier,
1188 p_xtd_pattern,
1189 p_calendar_id,
1190 'Y',--'Y'
1191 p_periodicity_missing,
1192 p_period_periodicity,
1193 p_period_missing,
1194 p_num_pattern_period
1195 )=false then
1196 return false;
1197 end if;
1198 if l_xtd_count<l_roll_range then
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
1206 --3919980
1207 -----------------
1208 /*ideally we can check generically to see if xtd count + rtd count matches the roll range. but for perf, since only few cases will need the
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
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),
1223 p_xtd_year(i),p_hier,p_xtd_pattern,p_calendar_id,p_periodicity_missing,p_period_periodicity,p_period_missing,p_num_pattern_period);
1224 end if;
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
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),
1239 p_xtd_year(i),p_hier,p_xtd_pattern,p_calendar_id,p_periodicity_missing,p_period_periodicity,p_period_missing,p_num_pattern_period);
1240 end if;
1241 end if;
1242 end if;
1243 -----------------
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
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
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;
1266 end if;
1267 end loop;
1268 return true;
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
1278 procedure correct_rolling_data_92_91(
1279 p_table_name varchar2
1280 ) is
1281 --
1282 l_stmt varchar2(4000);
1283 --
1284 Begin
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 '||
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 '||
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;
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
1315 --3919980
1316 /*4968072 : the earlier logic of inserting
1317 for i in 1..(p_reqd_count-p_current_count) loop
1318 execute immediate l_stmt using l_period+i...
1319 is not correct. consider jan 2 2006. we want rolling wtd. week starts on jan 2. jan 2 - 6 gives dec 27. we do wtd as of jan 2 and
1320 then rwtd as of dec 27. we miss out jan 1. earlier logic was assuming that we are considering the case where week in 2005 is ending before
1321 dec 31.
1322 best soln is to have a generic day filler given 2 dates. this can be expensive because we have to go to db calendar
1323 this api is called only in the year boundary for weeks on rolling mtd for mar 1 (missing out feb completely). so the api will be specific
1324 --
1325 after thinking through, this algo will be implented
1326 xtd date =A and Rtd=B
1327 --|-----|-----||----|----|---
1328 B Bs YB As A
1329 or
1330 --|-----|---------|----|---
1331 B Bs As A
1332 or
1333 --|-----|--------|---
1334 B Bs=As A
1335
1336 Bs=start period for B. As=start period for A.
1337 we get daycount for A. subtract to get As. we get daycount for B, add to get Bs
1338 we follow this logic
1339 Bs=Bs+1
1340 if Bs<As
1341 loop
1342 RTD(Bs)
1343 get daycount for Bs=Ds
1344 Bs=Bs+Ds
1345 if Bs>=As
1346 exit
1347 end if
1348 end loop
1349 end if
1350 This makes the api absolutely generic. given 2 dates, it fill any missing values
1351 */
1352 procedure correct_rolling_data(
1353 p_table_name varchar2,
1354 p_session_id number,
1355 p_xtd_report_date date,
1356 p_rtd_report_date date,
1357 p_xtd_periodicity number,
1358 p_xtd_period number,
1359 p_xtd_year number,
1360 p_hier varchar2,
1361 p_xtd_pattern number,
1362 p_calendar_id number,
1363 p_periodicity_missing boolean,
1364 p_period_periodicity number_tabletype,
1365 p_period_missing boolean_tabletype,
1366 p_num_pattern_period number
1367 ) is
1368 --
1369 l_prev_day_count number;
1370 l_day_count number;
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;
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<l_As_date then
1388 loop
1389 if insert_rpt_cal_keys(
1390 p_table_name,
1391 l_Bs_date,
1392 p_xtd_report_date,--this is inserted in report_date col of bsc_rpt_keys table
1393 p_xtd_periodicity,
1394 p_xtd_period,
1395 p_xtd_year,
1396 p_hier,
1397 p_xtd_pattern,
1398 p_calendar_id,
1399 'Y',
1400 p_periodicity_missing,
1401 p_period_periodicity,
1402 p_period_missing,
1403 p_num_pattern_period
1404 )=false then
1405 raise g_exception;
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;
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;
1421 else
1422 exit;
1423 end if;
1424 end loop;
1425 end if;
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
1435 function get_day_count(
1436 p_table_name varchar2,
1437 p_session_id number,
1438 p_report_date date,
1439 p_roll_flag varchar2) return number is
1440 --
1441 TYPE CurTyp IS REF CURSOR;
1442 cv CurTyp;
1443 l_stmt varchar2(5000);
1444 --
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;
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
1460 BSC_im_utils.write_to_log_file_n(sqlerrm);
1461 end if;
1462 return null;
1463 End;
1464
1465 procedure delete_table(
1466 p_table_name varchar2,
1467 p_session_id number,
1468 p_report_date date,
1469 p_roll_flag varchar2) is
1470 --
1471 l_stmt varchar2(5000);
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 ');
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
1489 function parse_values(
1490 p_list varchar2,
1491 p_separator varchar2,
1492 p_names out nocopy number_tabletype,
1493 p_number_names out nocopy number) return boolean is
1494 l_names varchar_tabletype;
1495 Begin
1496 if parse_values(p_list,p_separator,l_names,p_number_names)=false then
1497 return false;
1498 end if;
1499 for i in 1..p_number_names loop
1500 p_names(i):=l_names(i);
1501 end loop;
1502 return true;
1503 Exception when others then
1504 g_status_message:=sqlerrm;
1505 return false;
1506 End;
1507 function parse_values(
1508 p_list varchar2,
1509 p_separator varchar2,
1510 p_names out nocopy varchar_tabletype,
1511 p_number_names out nocopy number) return boolean is
1512 l_start number;
1513 l_end number;
1514 l_len number;
1515 Begin
1516 p_number_names:=0;
1517 if p_list is null then
1518 return true;
1519 end if;
1520 l_len:=length(p_list);
1521 if l_len<=0 then
1522 return true;
1523 end if;
1524 if instr(p_list,p_separator)=0 then
1525 p_number_names:=1;
1526 p_names(p_number_names):=ltrim(rtrim(p_list));
1527 return true;
1528 end if;
1529 l_start:=1;
1530 loop
1531 l_end:=instr(p_list,p_separator,l_start);
1532 if l_end=0 then
1533 l_end:=l_len+1;
1534 end if;
1535 p_number_names:=p_number_names+1;
1536 p_names(p_number_names):=ltrim(rtrim(substr(p_list,l_start,(l_end-l_start))));
1537 l_start:=l_end+1;
1538 if l_end>=l_len then
1539 exit;
1540 end if;
1541 end loop;
1542 return true;
1543 Exception when others then
1544 g_status_message:=sqlerrm;
1545 return false;
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
1555 function get_time return varchar2 is
1556 begin
1557 return ' '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
1558 Exception when others then
1559 null;
1560 End;
1561
1562 procedure get_bsc_fnd_owner is
1563 l_status varchar2(2000);
1564 l_industry varchar2(2000);
1565 e_error exception;
1566 Begin
1567 if FND_INSTALLATION.Get_App_Info('BSC',l_status, l_industry, g_bsc_owner)=false then
1568 raise e_error;
1569 end if;
1570 if FND_INSTALLATION.Get_App_Info('FND',l_status, l_industry, g_fnd_owner)=false then
1571 raise e_error;
1572 end if;
1573 Exception
1574 when e_error then
1575 raise;
1576 when others then
1577 g_status_message:=sqlerrm;
1578 raise;
1579 End;
1580
1581 procedure init is
1582 Begin
1583 if g_file is null then
1584 if instr(g_option_string,'DEBUG LOG')>0 then
1585 open_file;
1586 g_file:=true;
1587 g_debug:=true;
1588 else
1589 g_file:=false;
1590 g_debug:=false;
1591 end if;
1592 end if;
1593 Exception when others then
1594 g_status_message:=sqlerrm;
1595 raise;
1596 End;
1597 END BSC_BSC_XTD_PKG;
[Home] [Help]