[Home] [Help]
PACKAGE BODY: APPS.BIX_UTIL_PKG
Source
1 PACKAGE BODY BIX_UTIL_PKG AS
2 /*$Header: bixxutlb.pls 115.36 2003/07/31 21:09:56 djambula ship $*/
3
4 FUNCTION get_hrmiss_frmt(seconds IN NUMBER) RETURN VARCHAR2
5 IS
6 BEGIN
7
8 IF (seconds IS NULL) THEN
9 RETURN NULL;
10 ELSIF (FLOOR(ROUND(seconds)/3600) > 99 ) THEN
11 RETURN FLOOR(ROUND(seconds)/3600)|| ':' ||
12 LPAD(FLOOR(MOD(ROUND(seconds),3600)/60),2,'0') || ':' ||
13 LPAD(MOD(ROUND(seconds),60),2,'0');
14 ELSE
15 RETURN LPAD(FLOOR(ROUND(seconds)/3600),2,'0')|| ':' ||
16 LPAD(FLOOR(MOD(ROUND(seconds),3600)/60),2,'0') || ':' ||
17 LPAD(MOD(ROUND(seconds),60),2,'0');
18 END IF;
19
20 END get_hrmiss_frmt;
21
22 FUNCTION get_hrmi_frmt(seconds IN NUMBER) RETURN VARCHAR2
23 IS
24 BEGIN
25
26 IF (seconds IS NULL) THEN
27 RETURN NULL;
28 ELSE
29 IF MOD(seconds,60) < 30 THEN
30 RETURN LPAD(FLOOR(seconds/3600),2,'0')|| ':' ||
31 LPAD(FLOOR(MOD(seconds,3600)/60),2,'0');
32 ELSE
33 RETURN LPAD(FLOOR(seconds/3600),2,'0')|| ':' ||
34 LPAD(FLOOR(MOD(seconds,3600)/60)+1,2,'0');
35 END IF;
36 END IF;
37
38 END get_hrmi_frmt;
39
40 PROCEDURE get_time_range(p_time_id in number, p_from_date out nocopy date, p_to_date out nocopy date) IS
41
42 l_sysdate DATE;
43
44 BEGIN
45
46 SELECT sysdate
47 INTO l_sysdate
48 FROM dual;
49
50 IF (p_time_id = 1) THEN
51 p_from_date := trunc(l_sysdate,'IW');
52 p_to_date := l_sysdate;
53 ELSIF (p_time_id = 2) THEN
54 p_from_date := trunc(l_sysdate,'IW') - 7;
55 p_to_date := sysdate - 7;
56 ELSIF (p_time_id = 3) THEN
57 p_from_date := trunc(l_sysdate,'MM');
58 p_to_date := l_sysdate;
59 ELSIF (p_time_id = 4) THEN
60 p_from_date := to_date('01-' || to_char(trunc(l_sysdate,'MM') - 1,'mm-yyyy'),'dd-mm-yyyy');
61 IF (to_char(last_day(p_from_date),'dd') >= to_char(l_sysdate,'dd')) THEN
62 p_to_date := to_date(to_char(l_sysdate,'dd') || '-' || to_char(p_from_date,'mm') || '-' ||
63 to_char(l_sysdate,'yyyy') , 'dd-mm-yyyy');
64 ELSE
65 p_to_date := last_day(p_from_date);
66 END IF;
67 ELSIF (p_time_id = 5) THEN
68 p_from_date := to_date('01/01/' || to_char(l_sysdate,'yyyy') , 'dd/mm/yyyy');
69 p_to_date := l_sysdate;
70 ELSIF (p_time_id = 6) THEN
71 p_from_date := to_date('01/01/' || to_char(to_number(to_char(l_sysdate,'yyyy')) - 1) , 'dd/mm/yyyy');
72 p_to_date := to_date(to_char(l_sysdate,'dd-mm-') ||
73 to_char(to_number(to_char(l_sysdate,'yyyy')) - 1) , 'dd-mm-yyyy');
74 END IF;
75
76 p_to_date := to_date(to_char(p_to_date,'dd/mm/yyyy') || ' 23:59:59', 'dd/mm/yyyy hh24:mi:ss');
77
78 EXCEPTION
79 WHEN OTHERS THEN
80 RAISE;
81 END get_time_range;
82
83
84 FUNCTION get_uwq_refresh_date
85 (p_context in varchar2 )
86 RETURN VARCHAR2 IS
87 l_max_date VARCHAR2(25);
88 l_uwq_max_date DATE;
89 l_collect_max_date DATE;
90
91 l_date_format VARCHAR2(50);
92
93 BEGIN
94
95 l_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
96 IF(l_date_format IS NULL) THEN
97 l_date_format := 'MM/DD/YYYY';
98 END IF;
99
100
101 select max(day)
102 into l_uwq_max_date
103 from bix_dm_uwq_agent_sum;
104
105 SELECT MAX(collect_end_date) INTO l_collect_max_date
106 FROM bix_dm_collect_log
107 WHERE object_name = 'BIX_DM_UWQ_GROUP_SUM';
108
109
110 IF ( (TRUNC(l_collect_max_date) - l_uwq_max_date ) > 0) THEN
111 l_max_date := to_char(l_uwq_max_date,l_date_format)|| ' 23:59:59';
112 ELSIF( (TRUNC(l_collect_max_date) - l_uwq_max_date ) = 0) THEN
113 l_max_date := to_char(l_uwq_max_date,l_date_format)|| to_char(l_collect_max_date,' HH24:MI:SS');
114 ELSE
115 l_max_date := to_char(l_uwq_max_date,l_date_format)|| ' 00:00:00';
116 END IF;
117
118
119 IF l_max_date IS NULL
120 THEN
121 RETURN NULL;
122 ELSE
123 RETURN l_max_date ;
124 END IF;
125
126 EXCEPTION
127 WHEN OTHERS
128 THEN
129 RETURN NULL;
130 END get_uwq_refresh_date;
131
132 FUNCTION get_calls_refresh_date
133 (p_context in varchar2 )
134 RETURN VARCHAR2 IS
135 l_max_date VARCHAR2(25);
136 l_date_format VARCHAR2(50);
137 BEGIN
138 l_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
139 IF(l_date_format IS NULL) THEN
140 l_date_format := 'MM/DD/YYYY';
141 END IF;
142 l_date_format := l_date_format||' HH24:MI:SS';
143 SELECT to_char(MAX(period_start_date_time),l_date_format)
144 INTO l_max_date
145 FROM bix_dm_agent_call_sum;
146 RETURN l_max_date;
147 EXCEPTION
148 WHEN OTHERS
149 THEN
150 RETURN NULL;
151 END get_calls_refresh_date;
152
153 FUNCTION bix_dm_get_footer(p_context in VARCHAR2 )
154 RETURN VARCHAR2 IS
155 l_max_date VARCHAR2(20);
156 l_date_format VARCHAR2(50);
157 BEGIN
158 l_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
159 IF(l_date_format IS NULL) THEN
160 l_date_format := 'MM/DD/YYYY';
161 END IF;
162 l_date_format := l_date_format||' HH24:MI:SS';
163 SELECT to_char(MAX(period_start_date_time),l_date_format)
164 INTO l_max_date
165 FROM bix_dm_agent_call_sum;
166 RETURN FND_MESSAGE.GET_STRING('BIX','BIX_DM_REFRESH_MSG') || ' '|| l_max_date;
167 EXCEPTION
168 WHEN OTHERS THEN
169 RETURN NULL;
170 END BIX_DM_GET_FOOTER;
171
172 FUNCTION bix_real_get_footer(p_context in VARCHAR2 )
173 RETURN VARCHAR2 IS
174 l_date_format VARCHAR2(50);
175 l_max_date VARCHAR2(20);
176 BEGIN
177 l_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
178 IF(l_date_format IS NULL) THEN
179 l_date_format := 'MM/DD/YYYY';
180 END IF;
181 l_date_format := l_date_format||' HH24:MI:SS';
182 l_max_date := to_char(sysdate, l_date_format);
183 RETURN FND_MESSAGE.GET_STRING('BIX','BIX_DM_REFRESH_MSG') || ' '|| l_max_date;
184 EXCEPTION
185 WHEN OTHERS THEN
186 RETURN NULL;
187 END BIX_REAL_GET_FOOTER;
188
189 FUNCTION get_uwq_footer(p_context in VARCHAR2 )
190 RETURN VARCHAR2 IS
191 l_max_date VARCHAR2(25);
192 l_uwq_max_date DATE;
193 l_collect_max_date DATE;
194
195 l_date_format VARCHAR2(50);
196
197 BEGIN
198
199 l_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
200 IF(l_date_format IS NULL) THEN
201 l_date_format := 'MM/DD/YYYY';
202 END IF;
203
204
205 select max(day)
206 into l_uwq_max_date
207 from bix_dm_uwq_agent_sum;
208
209 SELECT MAX(collect_end_date) INTO l_collect_max_date
210 FROM bix_dm_collect_log
211 WHERE object_name = 'BIX_DM_UWQ_GROUP_SUM';
212
213
214 IF ( (TRUNC(l_collect_max_date) - l_uwq_max_date ) > 0) THEN
215 l_max_date := to_char(l_uwq_max_date,l_date_format)|| ' 23:59:59';
216 ELSIF( (TRUNC(l_collect_max_date) - l_uwq_max_date ) = 0) THEN
217 l_max_date := to_char(l_uwq_max_date,l_date_format)|| to_char(l_collect_max_date,' HH24:MI:SS');
218 ELSE
219 l_max_date := to_char(l_uwq_max_date,l_date_format)|| ' 00:00:00';
220 END IF;
221
222 RETURN FND_MESSAGE.GET_STRING('BIX','BIX_DM_REFRESH_MSG') || ' '|| l_max_date;
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 RETURN NULL;
227 END GET_UWQ_FOOTER;
228
229 FUNCTION get_uwq_duration_footer(p_context in VARCHAR2 )
230 RETURN VARCHAR2 IS
231 l_max_date VARCHAR2(25);
232 l_uwq_max_date DATE;
233 l_collect_max_date DATE;
234
235 l_date_format VARCHAR2(50);
236
237 BEGIN
238
239 l_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
240 IF(l_date_format IS NULL) THEN
241 l_date_format := 'MM/DD/YYYY';
242 END IF;
243
244
245 select max(day)
246 into l_uwq_max_date
247 from bix_dm_uwq_agent_sum;
248
249 SELECT MAX(collect_end_date) INTO l_collect_max_date
250 FROM bix_dm_collect_log
251 WHERE object_name = 'BIX_DM_UWQ_GROUP_SUM';
252
253
254 IF ( (TRUNC(l_collect_max_date) - l_uwq_max_date ) > 0) THEN
255 l_max_date := to_char(l_uwq_max_date,l_date_format)|| ' 23:59:59';
256 ELSIF( (TRUNC(l_collect_max_date) - l_uwq_max_date ) = 0) THEN
257 l_max_date := to_char(l_uwq_max_date,l_date_format)|| to_char(l_collect_max_date,' HH24:MI:SS');
258 ELSE
259 l_max_date := to_char(l_uwq_max_date,l_date_format)|| ' 00:00:00';
260 END IF;
261
262 RETURN FND_MESSAGE.GET_STRING('BIX','BIX_DM_REFRESH_MSG') || ' '||
263 l_max_date || ' '||
264 FND_MESSAGE.GET_STRING('BIX','BIX_DM_DURATION_FORMAT');
265 EXCEPTION
266 WHEN OTHERS THEN
267 RETURN NULL;
268 END GET_UWQ_DURATION_FOOTER;
269
270 FUNCTION get_realtime_footer(p_context in VARCHAR2 )
271 RETURN VARCHAR2 IS
272 l_max_date VARCHAR2(20);
273 l_date_format VARCHAR2(50);
274 BEGIN
275 l_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
276 IF(l_date_format IS NULL) THEN
277 l_date_format := 'MM/DD/YYYY';
278 END IF;
279 l_date_format := l_date_format||' HH24:MI:SS';
280 SELECT to_char(sysdate,l_date_format)
281 INTO l_max_date
282 FROM dual;
283 RETURN FND_MESSAGE.GET_STRING('BIX','BIX_DM_REFRESH_MSG') || ' '|| l_max_date;
284 EXCEPTION
285 WHEN OTHERS THEN
286 RETURN NULL;
287 END GET_REALTIME_FOOTER;
288
289 procedure get_conversion_rate(p_from_currency IN VARCHAR2,
290 p_to_currency IN VARCHAR2,
291 p_conversion_date IN DATE,
292 p_conversion_type IN VARCHAR2,
293 p_denom_rate OUT nocopy NUMBER,
294 p_num_rate OUT nocopy NUMBER,
295 p_status OUT nocopy NUMBER) is
296 l_num_rate number ;
297 l_denom_rate number ;
298 l_conv_rate number ;
299 l_max_rollback_days number ;
300
301 BEGIN
302 IF p_from_currency = p_to_currency
303 THEN
304 l_denom_rate := 1;
305 l_num_rate := 1;
306 ELSE
307 l_max_rollback_days := fnd_profile.value('BIX_DM_CURR_MAX_ROLL_DAYS');
308
309 IF l_max_rollback_days is null
310 THEN
311 l_max_rollback_days := 0;
312 END IF;
313
314 gl_currency_api.get_closest_triangulation_rate( p_from_currency,
315 p_to_currency, p_conversion_date, p_conversion_type,
316 l_max_rollback_days, l_denom_rate, l_num_rate, l_conv_rate );
317 END IF;
318 p_num_rate := l_num_rate;
319 p_denom_rate := l_denom_rate;
320 p_status := 0;
321 EXCEPTION
322 WHEN gl_currency_api.NO_RATE THEN
323 p_status := -1;
324 raise;
325 WHEN gl_currency_api.INVALID_CURRENCY THEN
326 p_status := -2;
327 raise;
328 WHEN others THEN
329 raise;
330 END get_conversion_rate;
331
332 FUNCTION GET_PARAMETER_VALUE(p_param_str in varchar2,
333 p_param_name in varchar2,
334 p_param_sep in varchar2,
335 p_value_sep in varchar2)
336 RETURN VARCHAR2 IS
337 l_param_val VARCHAR2(240);
338 BEGIN
342 return NULL;
339 l_param_val := jtfb_dcf.get_parameter_value(p_param_str, p_param_name, p_param_sep, p_value_sep);
340
341 IF (l_param_val = 'NOT_FOUND') THEN
343 ELSE
344 return l_param_val;
345 END IF;
346
347 END GET_PARAMETER_VALUE;
348
349 FUNCTION get_icx_session_id RETURN NUMBER IS
350 l_session_id NUMBER;
351 BEGIN
352 SELECT icx_sec.g_session_id
353 INTO l_session_id
354 FROM dual;
355
356 return l_session_id;
357 EXCEPTION
358 WHEN OTHERS THEN
359 RAISE;
360 END get_icx_session_id;
361
362 FUNCTION get_null_lookup RETURN VARCHAR2 IS
363 l_meaning VARCHAR2(80);
364 BEGIN
365 l_meaning := NULL;
366
367 SELECT meaning
368 INTO l_meaning
369 FROM fnd_lookups
370 WHERE lookup_type = 'BIX_DM_NULL_DESC'
371 AND lookup_code = 'NULL';
372
373 return l_meaning;
374 EXCEPTION
375 WHEN NO_DATA_FOUND THEN
376 return NULL;
377 WHEN OTHERS THEN
378 RAISE;
379 END get_null_lookup;
380
381 PROCEDURE get_prev_period(p_period_set_name IN VARCHAR2,
382 p_period_type IN VARCHAR2,
383 p_date IN DATE,
384 p_period_start_date OUT nocopy DATE,
385 p_period_end_date OUT nocopy DATE) IS
386
387 l_curr_start_date DATE;
388 l_prev_end_date DATE;
389 no_of_days NUMBER;
390
391 BEGIN
392
393 SELECT b.start_date,
394 b.end_date,
395 a.start_date
396 INTO p_period_start_date,
397 l_prev_end_date,
398 l_curr_start_date
399 FROM gl_periods a, gl_periods b
400 WHERE a.period_set_name = p_period_set_name
401 AND a.period_type = p_period_type
402 AND a.adjustment_period_flag = 'N'
403 AND trunc(p_date) between a.start_date and a.end_date
404 AND b.period_set_name = a.period_set_name
405 AND b.period_type = a.period_type
406 AND b.adjustment_period_flag = 'N'
407 AND (a.start_date - 1) between b.start_date and b.end_date;
408
409 no_of_days := trunc(sysdate - l_curr_start_date);
410
411 IF ((p_period_start_date + no_of_days) > l_prev_end_date) THEN
412 p_period_end_date := l_prev_end_date;
413 ELSE
414 p_period_end_date := p_period_start_date + no_of_days;
415 END IF;
416
417 EXCEPTION
418 WHEN NO_DATA_FOUND THEN
419 p_period_start_date := to_date('01/01/1900','dd/mm/yyyy');
420 p_period_end_date := to_date('01/01/1900','dd/mm/yyyy');
421 WHEN OTHERS THEN
422 RAISE;
423 END get_prev_period;
424
425 PROCEDURE get_curr_period(p_period_set_name IN VARCHAR2,
426 p_period_type IN VARCHAR2,
427 p_date IN DATE,
428 p_period_start_date OUT nocopy DATE,
429 p_period_end_date OUT nocopy DATE) IS
430
431 BEGIN
432
433 SELECT start_date
434 INTO p_period_start_date
435 FROM gl_periods
436 WHERE period_set_name = p_period_set_name
437 AND period_type = p_period_type
438 AND adjustment_period_flag = 'N'
439 AND trunc(p_date) between start_date and end_date;
440
441 SELECT sysdate
442 INTO p_period_end_date
443 FROM dual;
444
445 EXCEPTION
446 WHEN NO_DATA_FOUND THEN
447 p_period_start_date := to_date('01/01/1900','dd/mm/yyyy');
448 p_period_end_date := to_date('01/01/1900','dd/mm/yyyy');
449 WHEN OTHERS THEN
450 RAISE;
451 END get_curr_period;
452
453
454 PROCEDURE get_time_period(p_period_ind IN VARCHAR2,
455 p_start_date IN VARCHAR2,
456 p_start_time IN VARCHAR2,
457 p_end_date IN VARCHAR2,
458 p_end_time IN VARCHAR2,
459 p_start_period OUT nocopy VARCHAR2,
460 p_end_period OUT nocopy VARCHAR2) IS
461 l_sysdate DATE;
462 l_time_id NUMBER;
463 l_start_date DATE;
464 l_start_time VARCHAR2(10);
465 l_end_date DATE;
466 l_end_time VARCHAR2(10);
467 l_nls_date_format VARCHAR2(50);
468 l_temp_date DATE;
469 l_period_set_name VARCHAR2(20);
470
471 BEGIN
472
473 IF (p_period_ind IS NULL) THEN
474 return;
475 END IF;
476
477 SELECT sysdate
478 INTO l_sysdate
479 FROM dual;
480
481 /*
482 ** Commented out ; will be required once we start implementing "Save as default"
483 IF (INSTR(p_period_ind,':') > 0) THEN
484 l_time_id := SUBSTR(p_period_ind, 1, INSTR(p_period_ind, ':') - 1);
485 l_nls_date_format := SUBSTR(p_period_ind, INSTR(p_period_ind, ':') + 1);
486 else
487 l_time_id := TO_NUMBER(p_period_ind);
488 l_nls_date_format := fnd_profile.value('ICX_DATE_FORMAT_MASK');
489 end if;
490 */
491
492 l_nls_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
493 l_time_id := TO_NUMBER(p_period_ind);
494 l_start_date := TO_DATE(p_start_date, l_nls_date_format);
495 l_end_date := TO_DATE(p_end_date, l_nls_date_format);
496
497 IF (p_start_time IS NOT NULL) THEN
498 l_start_time := LPAD(p_start_time,2,'0');
499 ELSE
503 IF (p_end_time IS NOT NULL) THEN
500 l_start_time := '00';
501 END IF;
502
504 l_end_time := LPAD(p_end_time,2,'0');
505 ELSE
506 l_end_time := '23';
507 END IF;
508
509 l_period_set_name := FND_PROFILE.VALUE('BIX_DM_REPORTING_CALENDAR');
510
511 IF (l_time_id = 11) THEN
512 /* Period Indicator = 11 indicates that user has selected today as reporting period */
513 p_start_period := to_char(l_sysdate, 'dd/mm/yyyy') || ' 00:00:00';
514 p_end_period := to_char(l_sysdate, 'dd/mm/yyyy') || ' 23:59:59';
515
516 ELSIF (l_time_id = 12) THEN
517 /* Period Indicator = 12 indicates that user has selected yesterday as reporting period */
518 p_start_period := to_char(l_sysdate-1, 'dd/mm/yyyy') || ' 00:00:00';
519 p_end_period := to_char(l_sysdate-1, 'dd/mm/yyyy') || ' 23:59:59';
520
521 ELSIF (l_time_id = 13) THEN
522 /* Period Indicator = 13 indicates that user has selected current week as reporting period */
523 p_start_period := to_char(trunc(l_sysdate,'IW'),'dd/mm/yyyy') || ' 00:00:00';
524 p_end_period := to_char(l_sysdate,'dd/mm/yyyy') || ' 23:59:59';
525
526 ELSIF (l_time_id = 14) THEN
527 /* Period Indicator = 14 indicates that user has selected previous week as reporting period */
528 p_start_period := to_char(trunc(l_sysdate,'IW') - 7,'dd/mm/yyyy') || ' 00:00:00';
529 p_end_period := to_char(l_sysdate - 7,'dd/mm/yyyy') || ' 23:59:59';
530
531 ELSIF (l_time_id = 15) THEN
532 /* Period indicator = 15 indicates user has selected current month as reporting period */
533 get_curr_period(l_period_set_name, 'Month', l_sysdate, l_start_date, l_end_date);
534 p_start_period := to_char(l_start_date,'dd/mm/yyyy ') || '00:00:00';
535 p_end_period := to_char(l_end_date,'dd/mm/yyyy ') || '23:59:59';
536
537 ELSIF (l_time_id = 16) THEN
538 /* Period indicator = 16 indicates user has selected previous month as reporting period */
539 get_prev_period(l_period_set_name, 'Month', l_sysdate, l_start_date, l_end_date);
540 p_start_period := to_char(l_start_date,'dd/mm/yyyy ') || '00:00:00';
541 p_end_period := to_char(l_end_date,'dd/mm/yyyy ') || '23:59:59';
542
543 ELSIF (l_time_id = 17) THEN
544 /* Period indicator = 17 indicates user has selected current quarter as reporting period */
545 get_curr_period(l_period_set_name, 'Quarter', l_sysdate, l_start_date, l_end_date);
546 p_start_period := to_char(l_start_date,'dd/mm/yyyy ') || '00:00:00';
547 p_end_period := to_char(l_end_date,'dd/mm/yyyy ') || '23:59:59';
548
549 ELSIF (l_time_id = 18) THEN
550 /* Period indicator = 18 indicates user has selected previous quarter as reporting period */
551 get_prev_period(l_period_set_name, 'Quarter', l_sysdate, l_start_date, l_end_date);
552 p_start_period := to_char(l_start_date,'dd/mm/yyyy ') || '00:00:00';
553 p_end_period := to_char(l_end_date,'dd/mm/yyyy ') || '23:59:59';
554
555 ELSIF (l_time_id = 19) THEN
556 /* Period indicator = 19 indicates user has selected current year as reporting period */
557 get_curr_period(l_period_set_name, 'Year', l_sysdate, l_start_date, l_end_date);
558 p_start_period := to_char(l_start_date,'dd/mm/yyyy ') || '00:00:00';
559 p_end_period := to_char(l_end_date,'dd/mm/yyyy ') || '23:59:59';
560
561 ELSIF (l_time_id = 20) THEN
562 /* Period indicator = 20 indicates user has selected previous year as reporting period */
563 get_prev_period(l_period_set_name, 'Year', l_sysdate, l_start_date, l_end_date);
564 p_start_period := to_char(l_start_date,'dd/mm/yyyy ') || '00:00:00';
565 p_end_period := to_char(l_end_date,'dd/mm/yyyy ') || '23:59:59';
566
567 ELSIF (l_time_id = 21) THEN
568 /* Period indicator = 21 indicates user has specified from date time and to date time */
569 p_start_period := to_char(l_start_date,'dd/mm/yyyy ') || l_start_time || ':00:00';
570 p_end_period := to_char(l_end_date,'dd/mm/yyyy ') || l_end_time || ':59:59';
571
572 END IF;
573
574 EXCEPTION
575 WHEN OTHERS THEN
576 RAISE;
577 END get_time_period;
578
579 procedure get_conversion_rate(p_from_currency IN VARCHAR2,
580 p_to_currency IN VARCHAR2,
581 p_conversion_type IN VARCHAR2,
582 p_denom_rate OUT nocopy NUMBER,
583 p_num_rate OUT nocopy NUMBER,
584 p_status OUT nocopy NUMBER) is
585 l_num_rate number ;
586 l_denom_rate number ;
587 l_conv_rate number ;
588 l_max_rollback_days number ;
589
590 BEGIN
591 IF p_from_currency = p_to_currency
592 THEN
593 l_denom_rate := 1;
594 l_num_rate := 1;
595 ELSE
596 l_max_rollback_days := fnd_profile.value('BIX_DM_CURR_MAX_ROLL_DAYS');
597
598 IF l_max_rollback_days is null
599 THEN
600 l_max_rollback_days := 0;
601 END IF;
602
603 gl_currency_api.get_closest_triangulation_rate( p_from_currency,
604 p_to_currency, sysdate, p_conversion_type,
605 l_max_rollback_days, l_denom_rate, l_num_rate, l_conv_rate );
606 END IF;
607 p_num_rate := l_num_rate;
608 p_denom_rate := l_denom_rate;
609 p_status := 0;
610 EXCEPTION
611 WHEN gl_currency_api.NO_RATE THEN
612 p_status := -1;
613 raise;
614 WHEN gl_currency_api.INVALID_CURRENCY THEN
618 raise;
615 p_status := -2;
616 raise;
617 WHEN others THEN
619 END get_conversion_rate;
620
621 FUNCTION bix_dm_get_agent_footer(p_context in VARCHAR2 ) RETURN VARCHAR2 IS
622 l_max_date VARCHAR2(20);
623 l_date_format VARCHAR2(50);
624 BEGIN
625
626 l_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
627
628 IF(l_date_format IS NULL) THEN
629 l_date_format := 'MM/DD/YYYY';
630 END IF;
631
632 l_date_format := l_date_format||' HH24:MI:SS';
633
634 SELECT to_char(MAX(period_start_date_time),l_date_format)
635 INTO l_max_date
636 FROM bix_dm_agent_sum;
637
638 RETURN FND_MESSAGE.GET_STRING('BIX','BIX_DM_REFRESH_MSG') || ' '|| l_max_date;
639
640 EXCEPTION
641 WHEN OTHERS THEN
642 RETURN NULL;
643 END BIX_DM_GET_AGENT_FOOTER;
644
645 FUNCTION bix_dm_get_call_footer(p_context in VARCHAR2 ) RETURN VARCHAR2 IS
646 l_max_date VARCHAR2(20);
647 l_date_format VARCHAR2(50);
648 BEGIN
649
650 l_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
651
652 IF(l_date_format IS NULL) THEN
653 l_date_format := 'MM/DD/YYYY';
654 END IF;
655
656 l_date_format := l_date_format||' HH24:MI:SS';
657
658 SELECT to_char(MAX(period_start_date_time),l_date_format)
659 INTO l_max_date
660 FROM bix_dm_call_sum;
661
662 RETURN FND_MESSAGE.GET_STRING('BIX','BIX_DM_REFRESH_MSG') || ' '|| l_max_date;
663
664 EXCEPTION
665 WHEN OTHERS THEN
666 RETURN NULL;
667 END BIX_DM_GET_CALL_FOOTER;
668
669 FUNCTION bix_dm_get_agent_refresh_date(p_context in VARCHAR2 ) RETURN VARCHAR2 IS
670 l_max_date VARCHAR2(20);
671 l_date_format VARCHAR2(50);
672 BEGIN
673
674 l_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
675
676 IF(l_date_format IS NULL) THEN
677 l_date_format := 'MM/DD/YYYY';
678 END IF;
679
680 l_date_format := l_date_format||' HH24:MI:SS';
681
682 SELECT to_char(MAX(period_start_date_time),l_date_format)
683 INTO l_max_date
684 FROM bix_dm_agent_sum;
685
686 RETURN l_max_date;
687
688 EXCEPTION
689 WHEN OTHERS THEN
690 RETURN NULL;
691 END BIX_DM_GET_AGENT_refresh_date;
692
693 FUNCTION bix_dm_get_call_refresh_date(p_context in VARCHAR2 ) RETURN VARCHAR2 IS
694 l_max_date VARCHAR2(20);
695 l_date_format VARCHAR2(50);
696 BEGIN
697
698 l_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
699
700 IF(l_date_format IS NULL) THEN
701 l_date_format := 'MM/DD/YYYY';
702 END IF;
703
704 l_date_format := l_date_format||' HH24:MI:SS';
705
706 SELECT to_char(MAX(period_start_date_time),l_date_format)
707 INTO l_max_date
708 FROM bix_dm_call_sum;
709
710 RETURN l_max_date;
711
712 EXCEPTION
713 WHEN OTHERS THEN
714 RETURN NULL;
715 END BIX_DM_GET_CALL_refresh_date;
716
717 FUNCTION get_start_date ( p_end_date in VARCHAR2,
718 p_period in VARCHAR2,
719 p_date_format in VARCHAR2,
720 p_numperiods in NUMBER
721 )
722 RETURN VARCHAR2 IS
723
724 l_max_date VARCHAR2(20);
725 l_rpt_calendar VARCHAR2(50);
726 l_ctl_date DATE;
727 l_start_date DATE;
728 l_period_type VARCHAR2(50);
729
730 BEGIN
731
732 /**
733 *** Note: to get the start date of the current period pass in a
734 *** p_numperiods of 0
735 **/
736
737 l_rpt_calendar:= FND_PROFILE.VALUE('BIX_DM_REPORTING_CALENDAR');
738
739 --IF upper(p_period) = 'DAY'
740 IF p_period = '5'
741 THEN
742 l_period_type := 'DAY';
743 SELECT to_date(p_end_date, p_date_format) - p_numperiods
744 INTO l_start_date
745 from dual;
746 --ELSIF upper(p_period) = 'WEEK'
747 ELSIF p_period = '1'
748 THEN
749 l_period_type := 'WEEK';
750 SELECT trunc(to_date(p_end_date,p_date_format),'IW') - (p_numperiods*7)
751 INTO l_start_date
752 FROM dual;
753 ELSIF ( p_period = '2'
754 OR p_period = '3'
755 OR p_period = '4'
756 )
757 THEN
758 l_ctl_date := to_date(p_end_date, p_date_format);
759
760 IF p_period = '2'
761 THEN
762 l_period_type := 'MONTH';
763 ELSIF p_period = '3'
764 THEN
765 l_period_type := 'QUARTER';
766 ELSIF p_period = '4'
767 THEN
768 l_period_type := 'YEAR';
769 END IF;
770
771 BEGIN
772
773 FOR i IN 1 ..p_numperiods+1
774 LOOP
775
776 SELECT start_date
777 INTO l_start_date
778 FROM gl_periods
779 WHERE upper(period_set_name) = upper(l_rpt_calendar)
780 AND upper(period_type) = upper(l_period_type)
781 AND adjustment_period_flag = 'N'
782 AND l_ctl_date BETWEEN start_date and end_date;
783
784 IF l_start_date IS NOT NULL
785 THEN
789 END IF;
786 l_ctl_date := l_start_date-1;
787 ELSE
788 EXIT;
790
791
792 END LOOP;
793
794 --
795 --If it is not able to find the 13th period for some reason
796 --
797 EXCEPTION
798 WHEN OTHERS
799 THEN
800 IF l_start_date IS NULL
801 THEN
802 l_start_date := l_ctl_date+1;
803 END IF;
804
805 END;
806
807 END IF; --end if for checking the period
808
809 IF l_start_date IS NULL
810 THEN
811 l_start_date := nvl(l_ctl_date+1,to_date(p_end_date, p_date_format));
812 END IF;
813
814 RETURN to_char(l_start_date, p_date_format);
815
816 EXCEPTION
817 WHEN OTHERS
818 THEN
819 l_start_date := to_date(p_end_date, p_date_format);
820 RETURN to_char(l_start_date, p_date_format);
821
822 END get_start_date;
823
824 FUNCTION get_group_by ( p_end_date in VARCHAR2,
825 p_period in VARCHAR2,
826 p_date_format in VARCHAR2
827 )
828 RETURN VARCHAR2 IS
829
830 l_rpt_calendar VARCHAR2(50);
831 l_group_by VARCHAR2(50);
832
833 BEGIN
834
835 /**
836 *** Note: to get the start date of the current period pass in a
837 *** p_numperiods of 0
838 **/
839
840 l_rpt_calendar:= FND_PROFILE.VALUE('BIX_DM_REPORTING_CALENDAR');
841
842 IF upper(p_period) = 'DAY'
843 THEN
844 l_group_by := p_end_date;
845 ELSIF upper(p_period) = 'WEEK'
846 THEN
847 SELECT 'Week of '
848 || to_char(trunc(to_date(p_end_date,p_date_format),'IW'),'DD-MON')
849 INTO l_group_by
850 FROM dual;
851 ELSIF upper(p_period) = 'MONTH'
852 THEN
853 SELECT to_char(start_date,'MON') ||'-'|| period_year
854 INTO l_group_by
855 FROM gl_periods
856 WHERE upper(period_set_name) = upper(l_rpt_calendar)
857 AND upper(period_type) = upper(p_period)
858 AND adjustment_period_flag = 'N'
859 AND p_end_date BETWEEN start_date and end_date;
860 ELSIF upper(p_period) = 'QUARTER'
861 THEN
862 SELECT period_year || '-Q' || period_num
863 INTO l_group_by
864 FROM gl_periods
865 WHERE upper(period_set_name) = upper(l_rpt_calendar)
866 AND upper(period_type) = upper(p_period)
867 AND adjustment_period_flag = 'N'
868 AND p_end_date BETWEEN start_date and end_date;
869 END IF;
870
871 RETURN l_group_by;
872
873 EXCEPTION
874 WHEN OTHERS
875 THEN
876 NULL;
877
878 END get_group_by;
879
880 END BIX_UTIL_PKG;