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.
PACKAGE BODY: APPS.BSC_DIMENSION_KEYS_PKG

Source


1 Package Body BSC_DIMENSION_KEYS_PKG AS
2 /* $Header: BSCDKEYB.pls 120.0 2005/09/15 23:40 appldev noship $ */
3 
4 
5 g_keys_table  varchar2(100);
6 g_session_id  number;
7 -- Non XTD
8 g_periodicities BIS_PMV_PAGE_PARAMETER_TBL;
9 -- XTD specific
10 g_xtd_type varchar2(100);
11 g_xtd_periodicity number;
12 g_xtd_date  dbms_sql.varchar2_table;
13 g_xtd_period  dbms_sql.number_table;
14 g_xtd_year  dbms_sql.number_table;
15 
16 g_dimensions BIS_PMV_PAGE_PARAMETER_TBL;
17 g_measures BIS_PMV_PAGE_PARAMETER_TBL;
18 g_user_periods TabTimePeriods;
19 
20 PROCEDURE initialize_parameters(
21 p_parameters IN BIS_PMV_PAGE_PARAMETER_TBL) IS
22 BEGIN
23   g_periodicities := BIS_PMV_PAGE_PARAMETER_TBL();
24   g_periodicities.delete;
25   g_dimensions := BIS_PMV_PAGE_PARAMETER_TBL();
26   g_dimensions.delete;
27   g_measures := BIS_PMV_PAGE_PARAMETER_TBL();
28   g_measures.delete;
29 
30   FOR i IN 1..p_parameters.count LOOP
31     if p_parameters(i).Parameter_name='PERIODICITY' then
32       --dbms_output.put_line('g_periodicities:start');
33       g_periodicities.extend;
34       g_periodicities(g_periodicities.last):=p_parameters(i);
35       --dbms_output.put_line('g_periodicities:added'||g_periodicities.last);
36     elsif p_parameters(i).Parameter_name='KEYS TABLE' then
37       --dbms_output.put_line('keys table');
38       g_keys_table := p_parameters(i).parameter_value;
39       --dbms_output.put_line('g_keys_table added:'||g_keys_table);
40     elsif p_parameters(i).Parameter_name='SESSION ID' then
41       --dbms_output.put_line('g_session_id:start');
42       g_session_id := nvl(p_parameters(i).parameter_id, to_number(p_parameters(i).parameter_value));
43       --dbms_output.put_line('g_session_id:'||g_session_id);
44     elsif p_parameters(i).Parameter_name='XTD DATE' then
45       --dbms_output.put_line('g_xtd_date start');
46       g_xtd_date(g_xtd_date.count+1) := p_parameters(i).parameter_value;
47       --dbms_output.put_line('g_xtd_date:'||g_xtd_date(g_xtd_date.count));
48     elsif p_parameters(i).Parameter_name='XTD PERIOD' then
49       --dbms_output.put_line('g_xtd_period start');
50       g_xtd_period(g_xtd_period.count+1) := nvl(p_parameters(i).parameter_id, p_parameters(i).parameter_value);
51       --dbms_output.put_line('g_xtd_period end');
52 	elsif p_parameters(i).Parameter_name='XTD YEAR' then
53 	  --dbms_output.put_line('g_xtd_year start');
54       g_xtd_year(g_xtd_year.count+1) := nvl(p_parameters(i).parameter_id, p_parameters(i).parameter_value);
55       --dbms_output.put_line('g_xtd_year end');
56     elsif p_parameters(i).Parameter_name='XTD TYPE' then
57       --dbms_output.put_line('g_xtd_type start');
58       g_xtd_type := p_parameters(i).parameter_value;
59       --dbms_output.put_line('g_xtd_type end');
60     elsif p_parameters(i).Parameter_name='XTD PERIODICITY' then
61       --dbms_output.put_line('g_xtd_periodicity start');
62       g_xtd_periodicity := nvl(p_parameters(i).parameter_id, p_parameters(i).parameter_value);
63       --dbms_output.put_line('g_xtd_periodicity end');
64     elsif p_parameters(i).Parameter_name='DIMENSION'then
65       --dbms_output.put_line('Dimension start');
66       g_dimensions.extend;
67       g_dimensions(g_dimensions.last):=p_parameters(i);
68       --dbms_output.put_line('Dimension end '||g_dimensions.last);
69     elsif p_parameters(i).Parameter_name='MEASURE'then
70       --dbms_output.put_line('Measure start');
71       g_measures.extend;
72       g_measures(g_measures.last):=p_parameters(i);
73       --dbms_output.put_line('Measure end '||g_measures.last);
74     end if;
75   END LOOP;
76   exception when others then
77     --dbms_output.put_line('Exception in initialize_parameters:'||sqlerrm);
78     raise;
79 END;
80 
81 Procedure populate_xtd_keys(
82 p_kpi varchar2,
83 p_dim_set varchar2,
84 p_option_string varchar2,
85 p_error_message OUT nocopy varchar2) is
86 begin
87   for i in 1..g_xtd_date.count loop
88     bsc_bsc_xtd_pkg.populate_rpt_keys(
89                                 g_keys_table,
90                                 g_session_id,
91                                 p_kpi,
92                                 g_xtd_date(i),
93                                 g_xtd_period(i),
94                                 g_xtd_year(i),
95                                 g_xtd_periodicity,
96                                 g_xtd_type,
97                                 p_option_string,
98                                 p_error_message);
99   end loop;
100   --dbms_output.put_line('Completed populate_xtd_keys:'||p_error_message);
101   exception when others then
102     --dbms_output.put_line('Exception in populate_xtd_keys:'||sqlerrm);
103     raise;
104 end;
105 
106 function get_periodicity_info(p_periodicity_id number,
107   p_calendar_id OUT NOCOPY NUMBER,
108   p_custom OUT NOCOPY number,
109   p_db_column_name OUT NOCOPY VARCHAR2,
110   p_error_message OUT NOCOPY VARCHAR2)
111   return boolean is
112 cursor cType IS
113 select calendar_id, custom_code, db_column_name from bsc_sys_periodicities
114 where periodicity_id=p_periodicity_id
115 -- ignore these type of periodicities, they are std periodicites but dont have a db_column_name
116 and periodicity_type not in (11,12);
117 begin
118   open cType;
119   fetch cType into p_calendar_id, p_custom, p_db_column_name;
120   close cType;
121   if (p_custom is null or p_db_column_name is null) then
122     p_error_message := 'Invalid Periodicity id '||p_periodicity_id;
123     return false;
124   end if;
125   return true;
126   exception when others then
127     --dbms_output.put_line('Exception in get_periodicity_info:'||sqlerrm);
128     raise;
129 end;
130 
131 procedure split_string (p_string varchar2, p_separator varchar2, p_first out nocopy varchar2, p_second out nocopy varchar2) is
132 l_position number;
133 begin
134   p_first := p_string;
135   p_second := p_string;
136   l_position := instr(p_string, p_separator);
137   if (l_position > 0) then
138     p_first := substr(p_string, 1, l_position-1);
139     if instr(p_string, p_separator, l_position+length(p_separator)+1)>0 then
140       raise reporting_keys_exception;
141     end if;
142     p_second := substr(p_string, l_position+length(p_separator), length(p_string));
143   else  -- single value like 100.2004 with no TO
144     p_first := p_string;
145     p_second := p_string;
146   end if;
147   exception when others then
148     --dbms_output.put_line('Exception in split_string:'||sqlerrm);
149     raise;
150 end;
151 
152 function get_parsed_periodicities(p_index number) return TimePeriod IS
153 l_value varchar2(1000);
154 l_from varchar2(1000);
155 l_to varchar2(1000);
156 l_to_position number;
157 l_time_period TimePeriod;
158 l_start_period varchar2(100);
159 l_start_year varchar2(100);
160 l_end_period varchar2(100);
161 l_end_year varchar2(100);
162 
163 begin
164   l_value := g_periodicities(p_index).parameter_value;
165   split_string(l_value, ' TO ', l_from, l_to);
166   split_string(l_from, '.', l_start_period, l_start_year);
167   split_string(l_to, '.', l_end_period, l_end_year);
168   --dbms_output.put_line('start period='||l_start_period||', start_year='||l_start_year||', end period='||l_end_period||', end year='||l_end_year);
169   l_time_period.start_period := to_number(l_start_period);
170   l_time_period.start_year := to_number(l_start_year);
171   l_time_period.end_period := to_number(l_end_period);
172   l_time_period.end_year := to_number(l_end_year);
173   return l_time_period;
174   exception when others then
175     --dbms_output.put_line('Exception in get_parsed_periodicities:'||sqlerrm);
176     raise;
177 end;
178 
179 Function parse_periods return TabTimePeriods is
180 
181 l_period TimePeriod;
182 
183 l_create_new_period boolean;
184 l_periods TabTimePeriods;
185 
186 begin
187   for i in 1..g_periodicities.count loop
188     l_periods (l_periods .count+1) := get_parsed_periodicities(i);
189   end loop;
190   return l_periods;
191   exception when others then
192     --dbms_output.put_line('Exception in parse_periods:'||sqlerrm);
193     raise;
194 end;
195 
196 Procedure populate_nonxtd_keys(
197 p_kpi varchar2,
198 p_dim_set varchar2,
199 p_option_string varchar2,
200 p_error_message OUT nocopy varchar2) is
201 l_periodicity_id number;
202 l_custom number;
203 l_db_column_name varchar2(100);
204 l_calendar_id number;
205 
206 l_period_start_period dbms_sql.number_table;
207 l_period_start_year dbms_sql.number_table;
208 l_period_end_period dbms_sql.number_table;
209 l_period_end_year dbms_sql.number_table;
210 l_stmt varchar2(1000);
211 
212 begin
213   l_periodicity_id := g_periodicities(1).parameter_id;
214   if (get_periodicity_info(l_periodicity_id, l_calendar_id, l_custom, l_db_column_name, p_error_message)=false) then
215     --dbms_output.put_line('get_periodicity_info returned false : '||p_error_message);
216     raise reporting_keys_exception;
217   end if;
218   g_user_periods := parse_periods;
219   for i in 1..g_user_periods.count loop
220     l_period_start_period(i) := g_user_periods(i).start_period;
221 	l_period_start_year(i) := g_user_periods(i).start_year;
222     l_period_end_period(i) := g_user_periods(i).end_period;
223 	l_period_end_year(i) := g_user_periods(i).end_year;
224   end loop;
225 
226   if (l_custom=0) then -- std periodicites, goto db calendar
227     l_stmt := 'INSERT into '||g_keys_table||'(
228                SESSION_ID,
229                REPORT_DATE,
230                XTD_PERIOD,
231                XTD_YEAR,
232                XTD_PERIODICITY,
233                PERIOD,
234                YEAR,
235                PERIOD_TYPE_ID,
236                PERIODICITY_ID,
237                PERIOD_FLAG,
238                DAY_COUNT,
239                ROLLING_FLAG,
240                LAST_UPDATE_DATE)
241               select
242                 :1,
243                 null,
244                 null,
245                 null,
246                 null, '||
247                 l_db_column_name||
248                 ', year,
249                 null,
250                 :2,
251                 null,
252                 null,
253                 :3,
254                 sysdate
255               from bsc_db_calendar
256              where year between :4 and :5
257                and '||l_db_column_name||' between :6 and :7
258                and calendar_id = :8';
259     forall i in 1..g_user_periods.count
260       execute immediate l_stmt using g_session_id, l_periodicity_id, 'N',
261       l_period_start_year(i), l_period_end_year(i), l_period_start_period(i),
262       l_period_end_period(i), l_calendar_id;
263   else -- custom periodicitie, goto bsc_sys_periods
264       l_stmt := 'INSERT into '||g_keys_table||'(
265                SESSION_ID,
266                REPORT_DATE,
267                XTD_PERIOD,
268                XTD_YEAR,
269                XTD_PERIODICITY,
270                PERIOD,
271                YEAR,
272                PERIOD_TYPE_ID,
273                PERIODICITY_ID,
274                PERIOD_FLAG,
275                DAY_COUNT,
276                ROLLING_FLAG,
277                LAST_UPDATE_DATE)
278               select
279                 :1,
280                 null,
281                 period_id,
282                 year,
283                 :2,
284                 period_id,
285                 year,
286                 null,
287                 :3,
288                  0,
289                  null,
290                 :4,
291                 sysdate
292               from bsc_sys_periods
293              where
294               year between :5 and :6
295                and period_id between :7 and :8
296                and periodicity_id = :9';
297     forall i in 1..g_user_periods.count
298       execute immediate l_stmt using g_session_id, l_periodicity_id, l_periodicity_id, 'N',
299       l_period_start_year(i), l_period_end_year(i), l_period_start_period(i), l_period_end_period(i), l_periodicity_id;
300   end if;
301   -- mark the last period as 1
302   execute immediate 'update '||g_keys_table||' set period_flag=1 where period = (select max(period) from '||g_keys_table||' group by periodicity_id)';
303   exception when others then
304     --dbms_output.put_line('Exception in populate_nonxtd_keys:'||sqlerrm);
305     raise;
306 end;
307 
308 function is_measure_short_name(p_measure varchar2) return boolean is
309 l_count number;
310 begin
311   select count(1) into l_count from bsc_sys_measures where short_name=p_measure;
312   if (l_count > 0) then
313     return true;
314   end if;
315   return false;
316 end;
317 
318 Procedure Limit_AW(p_kpi varchar2, p_dim_set number) is
319 
320 l_parameters BIS_PMV_PAGE_PARAMETER_TBL;
321 l_parameter BIS_PMV_PAGE_PARAMETER_REC;
322 b_pmv_measure boolean;
323 begin
324   l_parameters := BIS_PMV_PAGE_PARAMETER_TBL();
325   -- rearrange data for AW call
326   --dbms_output.put_line('going to check dimensions');
327   --dbms_output.put_line('count='||g_dimensions.count );
328   for i in 1..g_dimensions.count loop
329     --dbms_output.put_line('0');
330     l_parameter := g_dimensions(i);
331     l_parameters.extend;
332     --dbms_output.put_line('1 '||g_dimensions(i).dimension);
333     l_parameter.parameter_name := g_dimensions(i).dimension;
334     --dbms_output.put_line('2');
335     l_parameter.parameter_id :=  g_dimensions(i).parameter_id;
336     --dbms_output.put_line('3');
337     l_parameter.parameter_value := g_dimensions(i).parameter_value;
338     --dbms_output.put_line('4');
339     l_parameter.dimension := g_dimensions(i).parameter_name;--'DIMENSION'
340     l_parameters(l_parameters.last) := l_parameter;
341   end loop;
342    --dbms_output.put_line('going to check measures');
343   for i in 1..g_measures.count loop
344     l_parameter := g_measures(i);
345     l_parameters.extend;
346     if (i=1) then
347       b_pmv_measure := is_measure_short_name(g_measures(i).parameter_value);
348     end if;
349     l_parameter.parameter_name := g_measures(i).parameter_value;
350     l_parameter.parameter_id :=  g_measures(i).parameter_id;
351     l_parameter.parameter_value := g_measures(i).parameter_value;
352     l_parameter.dimension := g_measures(i).parameter_name;--'MEASURE'
353     l_parameters(l_parameters.last) := l_parameter;
354   end loop;
355   -- xtd parameters
356   if (g_xtd_type is not null) then -- XTD query
357     l_parameters.extend;
358     l_parameter.parameter_name := g_keys_table;
359     l_parameter.parameter_id :=  null;
360     l_parameter.parameter_value := null;
361     l_parameter.dimension := 'XTD KEYS TABLE';
362     l_parameters(l_parameters.last) := l_parameter;
363     l_parameters.extend;
364     l_parameter.parameter_name := g_session_id;
365     l_parameter.parameter_id :=  null;
366     l_parameter.parameter_value := null;
367     l_parameter.dimension := 'XTD SESSION ID';
368     l_parameters(l_parameters.last) := l_parameter;
369     for i in 1..g_xtd_date.count loop
370       l_parameters.extend;
371       l_parameter.parameter_name := g_xtd_date(i);
372       l_parameter.parameter_id :=  null;
373       l_parameter.parameter_value := null;
374       l_parameter.dimension := 'XTD REPORT DATE';
375       l_parameters(l_parameters.last) := l_parameter;
376     end loop;
377   else-- non XTD query
378     for i in 1..g_periodicities.count loop
379       l_parameters.extend;
380       l_parameter.parameter_name := g_periodicities(i).parameter_id;
381       l_parameter.parameter_id :=  null;
382       l_parameter.parameter_value := g_periodicities(i).parameter_value;
383       l_parameter.dimension := g_periodicities(i).parameter_value;
384       l_parameters(l_parameters.last) := l_parameter;
385     end loop;
386   end if;
387   --dbms_output.put_line('Calling aw_read.limit :'||bsc_mo_helper_pkg.get_time);
388   if (b_pmv_measure) then
389     bsc_aw_read.limit_dimensions_pmv(p_kpi, p_dim_set, l_parameters);
390   else
391     bsc_aw_read.limit_dimensions(p_kpi, p_dim_set, l_parameters);
392   end if;
393   --dbms_output.put_line('Calling aw_read.limit :'||bsc_mo_helper_pkg.get_time);
394 
395 end;
396 
397 Procedure initialize_query (
398 p_kpi varchar2,
399 p_dim_set varchar2,
400 p_parameters BIS_PMV_PAGE_PARAMETER_TBL,
401 p_option_string varchar2,
402 p_error_message out nocopy varchar2
403 ) is
404 --
405 l_status varchar2(400);
406 l_start number;
407 l_end number;
408 Begin
409   --dbms_output.put_line('Initialize Query :'||bsc_mo_helper_pkg.get_time);
410   l_start := dbms_utility.get_time;
411   initialize_parameters(p_parameters);
412   if (g_xtd_type is not null) then -- XTD query
413     populate_xtd_keys(p_kpi, p_dim_set, p_option_string, p_error_message);
414   else
415     populate_nonxtd_keys(p_kpi, p_dim_set, p_option_string, p_error_message);
416   end if;
417   l_end := dbms_utility.get_time;
418   --dbms_output.put_line('calling internal limit api, time so far = '||(l_end-l_start));
419    l_start := dbms_utility.get_time;
420   limit_aw(p_kpi, p_dim_set);
421    l_end := dbms_utility.get_time;
422    --dbms_output.put_line('completed internal limit api, time so far = '||(l_end-l_start));
423 exception when others then
424     --dbms_output.put_line('Exception in initialize_query:'||sqlerrm);
425     raise;
426 End;
427 END BSC_DIMENSION_KEYS_PKG;