1 PACKAGE BODY IBE_BI_PMV_UTIL_PVT AS
2 /* $Header: IBEVBIUTILB.pls 120.2 2005/09/16 05:46:07 appldev ship $ */
3
4 TYPE curr_code_type is table of varchar2(15) index by binary_integer;
5 TYPE curr_count_type is table of number index by binary_integer;
6
7 /** Global variable to cache currency code for each minisite **/
8 g_msite_curr_table curr_code_type;
9
10 /** Global variable to cache count of currency for each minisite **/
11 g_msite_curr_count_table curr_count_type;
12
13 g_userid number := -9999;
14
15 /** Global variable to cache currency count when 'ALL' minisite is selected **/
16 g_all_curr_count number := -9999;
17
18 /** Global variable to cache currency code when 'ALL' minisite is selected **/
19 g_all_curr_code varchar2(15);
20
21 /* APPS-IT performance Bug# 4435729. Initialise global currency, secondary currency and MO: Security Profile */
22
23 g_gp_currency varchar2(30) := bis_common_parameters.get_currency_code;
24 g_gs_currency varchar2(30) := bis_common_parameters.get_secondary_currency_code;
25 g_mo_sec_profile number := fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL');
26
27 FUNCTION GET_CURR_CODE(id IN varchar2,p_MSITE_ID IN varchar2) return varchar2 is
28
29 x_out_var varchar2(20);
30 l_out_var varchar2(20);
31
32 l_msite_id number;
33 l_count number;
34 BEGIN
35
36
37 IF (id = 'FII_GLOBAL1') THEN
38 x_out_var := id;
39 return x_out_var;
40 END if ;
41
42 IF (id = 'FII_GLOBAL2') THEN
43 x_out_var := id;
44 return x_out_var;
45 END if ;
46
47 IF p_MSITE_ID = 'ALL' THEN
48
49 -- Check if currency count for ALL stores is cached or not
50 -- Since 'ALL' may mean different stores for different users, we are caching the count and currency code for a given user id
51 IF g_all_curr_count = -9999 or fnd_global.user_id <> g_userid THEN
52
53 g_userid := fnd_global.user_id;
54
55 SELECT COUNT(distinct f_curr.currency_code) new_count_number
56 INTO g_all_curr_count
57 FROM ibe_bi_currency_v f_curr
58 WHERE not exists (select b.org_id
59 from ibe_bi_msiteorg_mv b
60 where b.msite_id = f_Curr.msite_id
61 MINUS
62 select a.organization_id
63 from per_organization_list a
64 where a.security_profile_id =
65 g_mo_sec_profile);
66 END IF;
67
68 IF g_all_curr_count > 1 THEN
69 x_out_var := NULL;
70 ELSE
71
72 -- Check if currency code for all is already in cache or not
73 -- Since 'ALL' may mean different stores for different users, we are caching the count and currency code for a given user id
74 IF g_all_curr_code IS NULL or fnd_global.user_id <> g_userid THEN
75 g_userid := fnd_global.user_id;
76 select distinct currency_code
77 into g_all_curr_code
78 FROM ibe_bi_currency_v f_curr
79 WHERE currency_code <> g_gp_currency
80 and currency_code <> g_gs_currency
81 and not exists (select b.org_id
82 from ibe_bi_msiteorg_mv b
83 where b.msite_id = f_Curr.msite_id
84 MINUS
85 select a.organization_id
86 from per_organization_list a
87 where a.security_profile_id =
88 g_mo_sec_profile);
89 END IF;
90 x_out_var := g_all_curr_code;
91 END IF;
92
93 ELSE
94
95 /** convert the varchar field to numeric **/
96 l_msite_id := to_number(p_msite_id);
97
98 /** cache the currency count for a given msite_id **/
99 IF g_msite_curr_count_table.EXISTS(l_msite_id)THEN
100 l_count := g_msite_curr_count_table(l_msite_id);
101 ELSE
102 SELECT count(distinct currency_code) count_number
103 INTO g_msite_curr_count_table(l_msite_id)
104 FROM ibe_bi_currency_v
105 WHERE msite_id = l_msite_id;
106
107 l_count := g_msite_curr_count_table(l_msite_id);
108 END IF;
109
110 IF l_count > 1 THEN
111 x_out_var := NULL;
112 ELSE
113 /** cache the functional currency code for a given msite_id **/
114 IF g_msite_curr_table.EXISTS(l_msite_id) THEN
115 x_out_var := g_msite_curr_table(l_msite_id);
116 ELSE
117 select currency_code
118 into g_msite_curr_table(l_msite_id)
119 FROM ibe_bi_currency_v
120 WHERE msite_id = to_number(l_MSITE_ID)
121 and currency_code <> g_gp_currency
122 and currency_code <> g_gs_currency;
123 x_out_var := g_msite_curr_table(l_msite_id);
124
125 END IF;
126
127 END IF;
128
129 END IF;
130
131 RETURN (x_out_var);
132
133 EXCEPTION WHEN OTHERS THEN
134 RETURN 'xxx';
135
136 END GET_CURR_CODE;
137
138 FUNCTION GET_RECORD_TYPE_ID(p_period_type IN VARCHAR2)
139 RETURN NUMBER IS
140 l_record_type_id NUMBER;
141
142 BEGIN
143
144 IF(p_period_type = 'FII_TIME_ENT_YEAR') THEN
145 l_record_type_id := 119;
146 ELSIF(p_period_type = 'FII_TIME_ENT_QTR') THEN
147 l_record_type_id := 55;
148 ELSIF(p_period_type = 'FII_TIME_ENT_PERIOD') THEN
149 l_record_type_id := 23;
150 ELSE
151 l_record_type_id := 11;
152 END IF;
153
154 RETURN l_record_type_id;
155
156
157 END GET_RECORD_TYPE_ID;
158
159 -- Function to get the previous date.. required for calculating the % change
160
161 FUNCTION GET_PREV_DATE(p_asof_date IN DATE, p_period_type VARCHAR2, p_comparison_type VARCHAR2)
162 RETURN DATE IS
163 l_prev_date DATE;
164 BEGIN
165
166 -- If Sequential
167 IF(p_comparison_type = 'SEQUENTIAL') THEN
168 IF(p_period_type = 'FII_TIME_ENT_YEAR') THEN
169 l_prev_date := FII_TIME_API.ent_sd_lyr_end(p_asof_date);
170 ELSIF(p_period_type = 'FII_TIME_ENT_QTR') THEN
171 l_prev_date := FII_TIME_API.ent_sd_pqtr_end(p_asof_date);
172 ELSIF(p_period_type = 'FII_TIME_ENT_PERIOD') THEN
173 l_prev_date := FII_TIME_API.ent_sd_pper_end(p_asof_date);
174 ELSE
175
176 l_prev_date := FII_TIME_API.sd_pwk(p_asof_date);
177 END IF;
178 ELSE -- comparison type Year by Year
179 IF(p_period_type = 'FII_TIME_ENT_YEAR') THEN
180 l_prev_date := FII_TIME_API.ent_sd_lyr_end(p_asof_date);
181 ELSIF(p_period_type = 'FII_TIME_ENT_QTR') THEN
182 l_prev_date := FII_TIME_API.ent_sd_lysqtr_end(p_asof_date);
183 ELSIF(p_period_type = 'FII_TIME_ENT_PERIOD') THEN
184 l_prev_date := FII_TIME_API.ent_sd_lysper_end(p_asof_date);
185 ELSE
186 l_prev_date := FII_TIME_API.sd_lyswk(p_asof_date);
187 END IF;
188 END IF;
189
190
191 RETURN l_prev_date;
192
193 END GET_PREV_DATE;
194
195 PROCEDURE ENT_YR_SPAN(p_asof_date IN Date,
196 x_timespan OUT NOCOPY Number,
197 x_sequence OUT NOCOPY Number)
198 AS
199
200 Cursor c_year(p_date Date)
201 IS
202 Select (end_date - p_date) timespan,
203 sequence
204 From FII_TIME_ENT_YEAR
205 Where p_date BETWEEN start_date AND end_date;
206 Begin
207 Open c_year(p_asof_date);
208 Fetch c_year INTO x_timespan,x_sequence;
209 Close c_year;
210
211 END ENT_YR_SPAN;
212
213 PROCEDURE ENT_QTR_SPAN(p_asof_date IN DATE,
214 p_comparator IN VARCHAR2,
215 x_cur_start OUT NOCOPY DATE,
216 x_prev_start OUT NOCOPY DATE,
217 x_mid_start OUT NOCOPY DATE,
218 x_cur_year OUT NOCOPY NUMBER,
219 x_prev_year OUT NOCOPY NUMBER,
220 x_timespan OUT NOCOPY NUMBER)
221 AS
222 Cursor c_qtr(p_date Date) IS
223 SELECT end_date - p_date timespan,
224 sequence,
225 ent_year_id,
226 start_date
227 FROM FII_TIME_ENT_qtr
228 WHERE p_date BETWEEN start_date AND end_date;
229
230 Cursor c_min_date(p_sequence NUMBER,p_year_id NUMBER)
231 IS
232 SELECT start_date
233 FROM FII_TIME_ENT_qtr
234 WHERE sequence = p_sequence
235 AND ent_year_id = p_year_id;
236
237 l_timespan NUMBER := 0;
238 l_sequence NUMBER := 0;
239 l_year_id NUMBER := 0;
240 l_start_date DATE;
241 l_prev_seq NUMBER := 0;
242 l_prev_yr NUMBER := 0;
243 l_prev_start DATE;
244 Begin
245 OPEN c_qtr(p_asof_date);
246 FETCH c_qtr INTO l_timespan,l_sequence,l_year_id,l_start_date;
247 CLOSE c_qtr;
248
249 If(l_sequence = 4)
250 THEN
251 l_prev_seq := 1;
252
253 l_prev_yr := l_year_id - 1;
254 Elsif(l_sequence < 4)
255 THEN
256 l_prev_seq := l_sequence + 1;
257 l_prev_yr := l_year_id - 2;
258 End If;
259
260 Open c_min_date(l_prev_seq,l_prev_yr);
261 FETCH c_min_date INTO l_prev_start;
262 CLOSE c_min_date;
263
264 x_cur_start := TO_DATE(TO_CHAR(l_start_date,'DD/MM/RRRR'),'DD/MM/RRRR');
265 x_prev_start:= TO_DATE(TO_CHAR(l_prev_start,'DD/MM/YYYY'),'DD/MM/RRRR');
266
267 x_cur_year := l_year_id;
268 x_prev_year := l_prev_yr;
269 x_timespan := l_timespan;
270
271 If(p_comparator = 'YEARLY')
272 THEN
273 OPEN c_min_date(l_sequence,l_year_id-1);
274 FETCH c_min_date INTO x_mid_start;
275 CLOSE c_min_date;
276 END IF;
277 END ENT_QTR_SPAN;
278
279 PROCEDURE ENT_PRD_SPAN(p_asof_date IN DATE,
280
281 p_comparator IN VARCHAR2,
282 x_cur_start OUT NOCOPY DATE,
283 x_prev_start OUT NOCOPY DATE,
284 x_mid_start OUT NOCOPY DATE,
285 x_cur_year OUT NOCOPY NUMBER,
286 x_prev_year OUT NOCOPY NUMBER,
287 x_timespan OUT NOCOPY NUMBER)
288 As
289 Cursor c_prd(p_date Date)
290 IS
291 SELECT end_date - p_date timespan,
292 sequence,
293 ent_year_id,
294
295 start_date
296 FROM FII_TIME_ENT_period
297 WHERE p_date BETWEEN start_date AND end_date;
298
299 Cursor c_min_date(p_sequence NUMBER,
300 p_year_id NUMBER)
301 IS
302 SELECT start_date
303 FROM FII_TIME_ENT_PERIOD
304 WHERE sequence = p_sequence
305 AND ent_year_id = p_year_id;
306
307 l_timespan NUMBER := 0;
308
309 l_sequence NUMBER := 0;
310 l_year_id NUMBER := 0;
311 l_start_date DATE;
312 l_prev_seq NUMBER := 0;
313 l_prev_yr NUMBER := 0;
314 l_prev_start DATE;
315 Begin
316 OPEN c_prd(p_asof_date);
317 FETCH c_prd INTO l_timespan,l_sequence,l_year_id,l_start_date;
318 CLOSE c_prd;
319
320 If(l_sequence = 12)
321 Then
322
323 l_prev_seq := 1;
324 l_prev_yr := l_year_id - 1;
325 Elsif(l_sequence < 12)
326 Then
327 l_prev_seq := l_sequence + 1;
328 l_prev_yr := l_year_id - 2;
329 End If;
330
331 Open c_min_date(l_prev_seq,l_prev_yr);
332 FETCH c_min_date INTO l_prev_start;
333 CLOSE c_min_date;
334
335 x_cur_start := l_start_date;
336
337 x_prev_start:= l_prev_start;
338 x_cur_year := l_year_id;
339 x_prev_year := l_prev_yr;
340 x_timespan := l_timespan;
341
342 OPEN c_min_date(l_sequence,l_year_id-1);
343 FETCH c_min_date INTO x_mid_start;
344 CLOSE c_min_date;
345 END ENT_PRD_SPAN;
346
347 PROCEDURE WEEK_SPAN(p_asof_date IN DATE,
348 p_comparator IN VARCHAR2,
349 x_cur_start OUT NOCOPY DATE,
350 x_prev_start OUT NOCOPY DATE,
351 x_pcur_start OUT NOCOPY DATE,
352 x_pprev_start OUT NOCOPY DATE,
353 x_timespan OUT NOCOPY NUMBER)
354 AS
355 Cursor c_week(p_date Date)
356 IS
357 select w.end_date - p_date timespan,
358 w.start_date,
359 w.sequence,
360 p.year445_id
361 from fii_time_week w, fii_time_p445 p
362
363 where w.period445_id = p.period445_id
364 and p_date between w.start_date and w.end_date ;
365
366 Cursor c_lyswk(p_sequence NUMBER,p_year NUMBER)
367 IS
368 SELECT w.start_date
369 from fii_time_week w, fii_time_p445 p
370 where w.period445_id = p.period445_id
371 and w.sequence = p_sequence
372 and p.year445_id = p_year-1;
373
374 l_timespan NUMBER := 0;
375 l_cur_start DATE ;
376
377 l_prev_start DATE ;
378 l_sequence NUMBER;
379 l_cur_year NUMBER;
380 l_pcur_start DATE;
381 Begin
382
383 OPEN c_week(p_asof_date);
384 FETCH c_week INTO l_timespan, l_cur_start,l_sequence,l_cur_year;
385 CLOSE c_week;
386
387 l_prev_start := l_cur_start - 84; --(12*7)
388
389 -- set the out variables
390
391 x_cur_start := l_cur_start;
392 x_prev_start:= l_prev_start;
393 x_timespan := l_timespan;
394
395 If(p_comparator = 'YEARLY')
396 Then
397 Open c_lyswk(p_sequence => l_sequence,p_year => l_cur_year);
398 Fetch c_lyswk Into l_pcur_start;
399 Close c_lyswk;
400
401 x_pcur_start := l_pcur_start;
402 x_pprev_start := l_pcur_start-(12*7);
403 End if;
404
405 END WEEK_SPAN;
406
407 END IBE_BI_PMV_UTIL_PVT;
[Home] [Help]