[Home] [Help]
PACKAGE BODY: APPS.OKI_BUT_PVT
Source
1 PACKAGE BODY oki_but_pvt as
2 /* $Header: OKIRBUTB.pls 115.10 2002/12/19 19:36:16 brrao noship $*/
3
4 --------------------------------------------------------------------------------
5 -- Modification History
6 -- 04-Jan-2002 mezra Created
7 -- 20-Mar-2002 mezra Added logic to retrieve title at contract level.
8 -- 27-Mar-2002 mezra Added new procedure and functions to support
9 -- scaling factor
10 -- 04-Apr-2002 mezra Moved dbdrv command to top of file.
11 -- Synched branch with mainline.
12 -- 26-NOV-2002 rpotnuru NOCOPY Changes
13 -- 19-DEC-2002 brrao UTF-8 Changes to Organization_name
14 --------------------------------------------------------------------------------
15
16 --------------------------------------------------------------------------------
17 -- Function to get the refresh date.
18
19 --------------------------------------------------------------------------------
20 FUNCTION get_rfh_date
21 ( p_name IN VARCHAR2
22 ) RETURN VARCHAR2 IS
23
24 -- Cursor declaration
25
26 -- Cursor to get the refresh date
27 CURSOR rfh_csr
28 ( p_name IN VARCHAR2
29 ) IS
30 SELECT INITCAP(RTRIM(TO_CHAR(rfh.program_update_date, 'MONTH')))
31 , TO_CHAR(rfh.program_update_date,'DD, RRRR HH24:MI')
32 FROM oki_refreshs rfh
33 WHERE UPPER(rfh.object_name) = UPPER(p_name)
34 ;
35
36 l_month VARCHAR2(40) := NULL ;
37 l_time VARCHAR2(40) := NULL ;
38 l_datetime VARCHAR2(80) := NULL ;
39
40 l_message VARCHAR2(50) := NULL ;
41
42 l_object_refreshed VARCHAR2(30) := NULL ;
43
44 BEGIN
45 -- Get name of the object that that has been refreshed
46 l_object_refreshed := jtfb_dcf.get_parameter_value(p_name,'P_OBJECT_REFRESHED');
47
48 OPEN rfh_csr( l_object_refreshed ) ;
49 FETCH rfh_csr INTO l_month, l_time ;
50 IF (rfh_csr%NOTFOUND) OR (l_month is NULL) THEN
51 -- Get the standard message for no refresh date found
52 fnd_message.set_name( application => 'OKI'
53 , name => 'OKI_GET_RFH_DATE_FAILURE');
54 ELSE
55 -- Refresh date found
56 l_datetime := l_month || ' ' || l_time ;
57
58 -- Get the standard message for refresh date found
59 fnd_message.set_name( application => 'OKI'
60 , name => 'OKI_GET_RFH_DATE_SUCCESS');
61
62 fnd_message.set_token( token => 'DATETIME'
63 , value => l_datetime);
64 END IF;
65 CLOSE rfh_csr ;
66
67 l_message := fnd_message.get;
68
69 return l_message ;
70
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 -- return the error number to the calling program;
75 l_message := substr(sqlerrm, 1, (instr(sqlerrm, ':') - 1) ) ;
76 return l_message ;
77
78 END get_rfh_date ;
79
80 --------------------------------------------------------------------------------
81 -- Function to get the period set name based on the user's profile.
82
83 --------------------------------------------------------------------------------
84 FUNCTION get_period_set
85 ( p_profile_value IN VARCHAR2
86 ) RETURN VARCHAR2 IS
87
88 --Local variable declarion
89
90 -- The default period set name from the user's profile.
91 l_period_set_name VARCHAR2(15) ;
92
93 -- The message id when an error occurs
94 l_message_id VARCHAR2(40) := null ;
95
96 BEGIN
97
98 l_period_set_name := fnd_profile.value( 'OKI_DEFAULT_PERIOD_SET' ) ;
99
100 return l_period_set_name ;
101
102 EXCEPTION
103 WHEN OTHERS THEN
104 -- return the error number to the calling program;
105 l_message_id := substr(sqlerrm, 1, (instr(sqlerrm, ':') - 1) ) ;
106 return l_message_id ;
107
108 END get_period_set ;
109
110 --------------------------------------------------------------------------------
111 -- Function to get the period type based on the user's profile.
112
113 --------------------------------------------------------------------------------
114 FUNCTION get_period_type
115 ( p_profile_value IN VARCHAR2
116 ) RETURN VARCHAR2 IS
117
118 --Local variable declarion
119
120 -- The default period type from the user's profile.
121 l_period_type VARCHAR2(15) ;
122
123 -- The message id when an error occurs
124 l_message_id VARCHAR2(40) := null ;
125
126 BEGIN
127
128 l_period_type := fnd_profile.value( 'OKI_DEFAULT_PERIOD_TYPE' ) ;
129
130 return l_period_type ;
131
132 EXCEPTION
133 WHEN OTHERS THEN
134 -- return the error number to the calling program;
135 l_message_id := substr(sqlerrm, 1, (instr(sqlerrm, ':') - 1) ) ;
136 return l_message_id ;
137
138 END get_period_type ;
139
140
141 --------------------------------------------------------------------------------
142 -- Function to get the default the period name based on the user's profile:
143 -- period set and period type
144
145 --------------------------------------------------------------------------------
146 FUNCTION get_period_name
147 ( p_profile_value IN VARCHAR2
148 ) RETURN VARCHAR2 IS
149
150 --Local variable declarion
151
152 -- The default period type from the user's profile.
153 l_period_type VARCHAR2(15) ;
154
155 -- The default period set name from the user's profile.
156 l_period_set_name VARCHAR2(15) ;
157
158
159 l_period_name VARCHAR2(15) ;
160
161 -- Use sysdate as the default date
162 l_default_date DATE := TRUNC(sysdate) ;
163
164 -- The message id when an error occurs
165 l_message_id VARCHAR2(40) := null ;
166
167 -- Cursor declaration
168
169 -- Cursor to get the period name based on the user's profile:
170 -- period set name, period type
171 CURSOR l_period_name_csr
172 ( p_period_set_name IN VARCHAR2
173 , p_period_type IN VARCHAR2
174 , p_default_date IN DATE
175 ) IS
176 SELECT glpr.period_name period_name
177 FROM gl_periods glpr
178 WHERE p_period_set_name = glpr.period_set_name
179 AND p_period_type = glpr.period_type
180 AND glpr.adjustment_period_flag = 'N'
181 AND p_default_date BETWEEN TRUNC(glpr.start_date)
182 AND TRUNC(glpr.end_date)
183 ;
184 rec_l_period_name_csr l_period_name_csr%ROWTYPE ;
185
186 BEGIN
187
188 l_period_set_name := oki_but_pvt.get_period_set(NULL) ;
189 l_period_type := oki_but_pvt.get_period_type(NULL) ;
190
191 OPEN l_period_name_csr ( l_period_set_name, l_period_type,
192 l_default_date ) ;
193 FETCH l_period_name_csr INTO rec_l_period_name_csr ;
194 IF l_period_name_csr%NOTFOUND THEN
195 RAISE NO_DATA_FOUND ;
196 ELSE
197 l_period_name := rec_l_period_name_csr.period_name ;
198 END IF ;
199 CLOSE l_period_name_csr ;
200
201 return l_period_name ;
202
203 EXCEPTION
204 WHEN NO_DATA_FOUND THEN
205 -- return the error number to the calling program;
206 l_message_id := substr(sqlerrm, 1, (instr(sqlerrm, ':') - 1) ) ;
207 return l_message_id ;
208
209 WHEN OTHERS THEN
210 -- return the error number to the calling program;
211 l_message_id := substr(sqlerrm, 1, (instr(sqlerrm, ':') - 1) ) ;
212 return l_message_id ;
213
214 END get_period_name ;
215
216 --------------------------------------------------------------------------------
217 -- Function that returns the column labels for the renewal aging report
218 --------------------------------------------------------------------------------
219
220 FUNCTION get_aging_label(p_col_pos IN VARCHAR2) return varchar2 IS
221
222 -- Local variable declaration
223 l_retval VARCHAR2(60) := NULL ;
224 l_aging_range NUMBER := NULL ;
225 l_start_age_group VARCHAR2(10) := NULL ;
226 l_end_age_group VARCHAR2(10) := NULL ;
227 l_separator varchar2(1) := '-' ;
228
229 l_label_postfix CONSTANT VARCHAR2(5) := 'Days' ;
230
231 BEGIN
232 l_aging_range := to_number(fnd_profile.value('OKI_AGING_RANGE')) ;
233
234 -- In cases where the aging range is not defined, return a
235 -- generic column label
236 IF (p_col_pos IS NULL) or (l_aging_range IS NULL) THEN
237 l_retval := 'Age Group' ;
238 return l_retval ;
239 END IF ;
240
241 IF p_col_pos = '1' THEN
242 l_start_age_group := '0' ;
243 l_end_age_group := to_char(l_start_age_group + l_aging_range) ;
244 ELSIF p_col_pos = '2' THEN
245 l_start_age_group := to_char(l_aging_range + 1) ;
246 l_end_age_group := to_char(l_aging_range * 2) ;
247 ELSIF p_col_pos = '3' THEN
248 l_start_age_group := to_char((l_aging_range * 2) + 1) ;
249 l_end_age_group := to_char(l_aging_range * 3) ;
250 ELSIF p_col_pos = '4' THEN
251 l_start_age_group := to_char((l_aging_range * 3) + 1) ;
252 l_separator := '+' ;
253 END IF ;
254
255 l_retval := l_start_age_group || l_separator || l_end_age_group ||
256 ' ' || l_label_postfix ;
257 return l_retval ;
258
259 END get_aging_label ;
260
261
262 --------------------------------------------------------------------------------
263 -- Function that returns the column labels for the first column of the
264 -- renewal aging report
265 --------------------------------------------------------------------------------
266 FUNCTION get_aging_label1(p_col_pos IN VARCHAR2) return varchar2 IS
267
268 -- Indicates it's the first column
269 l_col_pos CONSTANT VARCHAR2(1) := 1 ;
270 l_retval VARCHAR2(60) := NULL ;
271
272 -- The message id when an error occurs
273 l_message_id VARCHAR2(40) := null ;
274
275
276 BEGIN
277 -- Retrieve the column label for the first column
278 l_retval := oki_but_pvt.get_aging_label( p_col_pos => l_col_pos ) ;
279
280 return l_retval ;
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 -- return the error number to the calling program;
285 l_message_id := substr(sqlerrm, 1, (instr(sqlerrm, ':') - 1) ) ;
286 return l_message_id ;
287
288 END get_aging_label1 ;
289
290
291 --------------------------------------------------------------------------------
292 -- Function that returns the column labels for the second column of the
293 -- renewal aging report
294 --------------------------------------------------------------------------------
295 FUNCTION get_aging_label2(p_col_pos IN VARCHAR2) return varchar2 IS
296
297 -- Indicates it's the second column
298 l_col_pos CONSTANT VARCHAR2(1) := 2 ;
299 l_retval VARCHAR2(60) := NULL ;
300
301 -- The message id when an error occurs
302 l_message_id VARCHAR2(40) := null ;
303
304
305 BEGIN
306 -- Retrieve the column label for the second column
307 l_retval := oki_but_pvt.get_aging_label( p_col_pos => l_col_pos ) ;
308
309 return l_retval ;
310
311 EXCEPTION
312 WHEN OTHERS THEN
313 -- return the error number to the calling program;
314 l_message_id := substr(sqlerrm, 1, (instr(sqlerrm, ':') - 1) ) ;
315 return l_message_id ;
316
317 END get_aging_label2 ;
318
319 --------------------------------------------------------------------------------
320 -- Function that returns the column labels for the third column of the
321 -- renewal aging report
322 --------------------------------------------------------------------------------
323 FUNCTION get_aging_label3(p_col_pos IN VARCHAR2) return varchar2 IS
324
325 -- Indicates it's the third column
326 l_col_pos CONSTANT VARCHAR2(1) := 3 ;
327 l_retval VARCHAR2(60) := NULL ;
328
329 -- The message id when an error occurs
330 l_message_id VARCHAR2(40) := null ;
331
332
333 BEGIN
334 -- Retrieve the column label for the third column
335 l_retval := oki_but_pvt.get_aging_label( p_col_pos => l_col_pos ) ;
336
337 return l_retval ;
338
339 EXCEPTION
340 WHEN OTHERS THEN
341 -- return the error number to the calling program;
342 l_message_id := substr(sqlerrm, 1, (instr(sqlerrm, ':') - 1) ) ;
343 return l_message_id ;
344
345 END get_aging_label3 ;
346
347 --------------------------------------------------------------------------------
348 -- Function that returns the column labels for the fourth column of the
349 -- renewal aging report
350 --------------------------------------------------------------------------------
351 FUNCTION get_aging_label4(p_col_pos IN VARCHAR2) return varchar2 IS
352
353 -- Indicates it's the fourth column
354 l_col_pos CONSTANT VARCHAR2(1) := 4 ;
355 l_retval VARCHAR2(60) := NULL ;
356
357 -- The message id when an error occurs
358 l_message_id VARCHAR2(40) := null ;
359
360
361 BEGIN
362 -- Retrieve the column label for the fourth column
363 l_retval := oki_but_pvt.get_aging_label( p_col_pos => l_col_pos ) ;
364
365 return l_retval ;
366
367 EXCEPTION
368 WHEN OTHERS THEN
369 -- return the error number to the calling program;
370 l_message_id := substr(sqlerrm, 1, (instr(sqlerrm, ':') - 1) ) ;
371 return l_message_id ;
372
373 END get_aging_label4 ;
374
375 --------------------------------------------------------------------------------
376 -- Function that returns the either the start age age value or the end age
377 -- value of the age group.
378 --------------------------------------------------------------------------------
379 FUNCTION get_start_end_age_val
380 ( p_start_end_pos IN VARCHAR2
381 , p_col_pos IN VARCHAR2
382 ) return varchar2 IS
383
384 l_age_value VARCHAR2(10) := NULL ;
385 l_aging_range NUMBER := NULL ;
386 BEGIN
387 l_aging_range := to_number(fnd_profile.value('OKI_AGING_RANGE')) ;
388
389 IF p_start_end_pos = 'START' THEN
390 -- Calculate the start age of the aging group
391 IF p_col_pos = '1' THEN
392 l_age_value := 0 ;
393 ELSIF p_col_pos = '2' THEN
394 l_age_value := to_char(l_aging_range + 1) ;
395 ELSIF p_col_pos = '3' THEN
396 l_age_value := to_char((l_aging_range * 2) + 1) ;
397 ELSIF p_col_pos = '4' THEN
398 l_age_value := to_char((l_aging_range * 3) + 1) ;
399 END IF ;
400
401 ELSIF p_start_end_pos = 'END' THEN
402 -- Calculate the end age of the aging group
403 IF p_col_pos = '1' THEN
404 l_age_value := to_char(l_aging_range) ;
405 ELSIF p_col_pos = '2' THEN
406 l_age_value := to_char(l_aging_range * 2) ;
407 ELSIF p_col_pos = '3' THEN
408 l_age_value := to_char(l_aging_range * 3) ;
409 END IF;
410 END IF ;
411
412 return l_age_value ;
413 END get_start_end_age_val ;
414
415 --------------------------------------------------------------------------------
416 -- Function that returns the title for a bin.
417 --------------------------------------------------------------------------------
418 FUNCTION get_bin_title
419 ( p_grouping IN VARCHAR2
420 , p_bin_name IN VARCHAR2
421 , p_code IN VARCHAR2
422 ) return varchar2 IS
423
424 l_prefix VARCHAR2(30) := NULL ;
425 l_postfix VARCHAR2(30) := NULL ;
426 l_separator vARCHAR2(5) := NULL ;
427 l_title VARCHAR2(100) := NULL ;
428 BEGIN
429 IF p_grouping = 'Aging' THEN
430 IF p_bin_name IN ('OKI_RAG_ORG_AG1_RPT', 'OKI_RAG_ORG_AG2_RPT',
431 'OKI_RAG_ORG_AG3_RPT', 'OKI_RAG_ORG_AG4_RPT') THEN
432 l_prefix := 'Renewal Aging' ;
433 l_separator := ' - ' ;
434 l_postfix := oki_but_pvt.get_aging_label(p_code) ;
435 END IF ;
436 END IF ;
437
438
439 l_title := l_prefix || l_separator || l_postfix ;
440 return l_title ;
441
442 END get_bin_title ;
443 --------------------------------------------------------------------------------
444 -- Function that returns the title for a bin.
445 --------------------------------------------------------------------------------
446 FUNCTION get_bin_title2
447 ( p_param IN VARCHAR2
448 ) RETURN VARCHAR2 IS
449
450 l_context VARCHAR2(1000);
451 l_code VARCHAR2(30) ;
452 l_title_value VARCHAR2(60);
453
454 BEGIN
455 l_context := jtfb_dcf.get_parameter_value( p_param, 'pContext') ;
456 l_code := substr(l_context, (instr(l_context, ':', 1, 1 ) + 1 ),
457 ((instr(l_context, ':', 1, 2 )) - (instr(l_context, ':', 1, 1 ) + 1 ))) ;
458 IF l_code = 'BACTK' THEN
459 l_title_value := 'Beginning active contracts by organization ' ;
460 ELSIF l_code = 'EXPINQTR' THEN
461 l_title_value := 'Expiring during quarter by organization ' ;
462 ELSIF l_code = 'BKLGKRNW' THEN
463 l_title_value := 'Backlog contracts renewed by organization ' ;
464 ELSIF l_code = 'KRNW' THEN
465 l_title_value := 'Quarter contracts renewed by organization ' ;
466 ELSIF l_code = 'NEWBUS' THEN
467 l_title_value := 'New business by organization' ;
468 ELSIF l_code = 'TRMNK' THEN
469 l_title_value := 'Terminated contracts by organization' ;
470 END IF ;
471
472 return l_title_value ;
473
474 END get_bin_title2 ;
475
476 --------------------------------------------------------------------------------
477 -- Function that returns the title for contracts bin.
478 --------------------------------------------------------------------------------
479 FUNCTION get_top_n_k_title
480 ( p_param IN VARCHAR2
481 ) RETURN VARCHAR2 IS
485 l_title_value VARCHAR2(200) ;
482
483 l_context VARCHAR2(1000);
484 l_code VARCHAR2(30) ;
486 l_prefix VARCHAR2(60) := 'Top 10 ' ;
487 l_org_id VARCHAR2(40) ;
488 l_org_name VARCHAR2(240) := NULL ;
489
490 CURSOR l_get_org_name_csr
491 ( p_org_id IN NUMBER
492 ) IS
493 SELECT oru.name
494 FROM hr_all_organization_units oru
495 WHERE oru.organization_id = p_org_id
496 ;
497 rec_l_get_org_name_csr l_get_org_name_csr%ROWTYPE ;
498
499 BEGIN
500 l_context := jtfb_dcf.get_parameter_value( p_param, 'pContext') ;
501
502 l_code := substr(l_context, (instr(l_context, ':', 1, 1 ) + 1 ),
503 ((instr(l_context, ':', 1, 2 )) - (instr(l_context, ':', 1, 1 ) + 1 ))) ;
504 l_org_id := substr(l_context, (instr(l_context, ':', 1, 3 ) + 1 )) ;
505
506 OPEN l_get_org_name_csr( TO_NUMBER(l_org_id) ) ;
507 FETCH l_get_org_name_csr INTO rec_l_get_org_name_csr ;
508 l_org_name := rec_l_get_org_name_csr.name ;
509 CLOSE l_get_org_name_csr ;
510
511 IF l_code = 'BACTK' THEN
512 l_title_value := l_prefix || 'beginning active contracts: ' || l_org_name ;
513 ELSIF l_code = 'EXPINQTR' THEN
514 l_title_value := l_prefix || 'expiring during quarter: ' || l_org_name ;
515 ELSIF l_code = 'BKLGKRNW' THEN
516 l_title_value := l_prefix || 'backlog contracts renewed: ' || l_org_name ;
517 ELSIF l_code = 'KRNW' THEN
518 l_title_value := l_prefix || 'quarter contracts renewed: ' || l_org_name ;
519 ELSIF l_code = 'NEWBUS' THEN
520 l_title_value := l_prefix || 'new business: ' || l_org_name ;
521 ELSIF l_code = 'TRMNK' THEN
522 l_title_value := l_prefix || 'terminated contracts: ' || l_org_name ;
523 ELSIF l_code = 'TACTK' THEN
524 l_title_value := l_prefix || 'total active contracts: ' || l_org_name ;
525 END IF ;
526
527 return l_title_value ;
528
529 END get_top_n_k_title ;
530 --------------------------------------------------------------------------------
531 -- Function that returns the title for the bin.
532 --
533 --------------------------------------------------------------------------------
534 FUNCTION get_title_for_bin
535 ( p_param IN VARCHAR2
536 ) RETURN VARCHAR2 IS
537
538 l_bin_name VARCHAR2(1000) ;
539 l_param1 VARCHAR2(1000) ;
540 l_title VARCHAR2(200) := NULL ;
541 l_title_prefix VARCHAR2(100) := NULL ;
542 l_title_suffix VARCHAR2(100) := NULL ;
543 l_title_var1 VARCHAR2(100) := NULL ;
544
545 BEGIN
546 l_bin_name := jtfb_dcf.get_parameter_value(p_param, 'P_BIN_NAME') ;
547 l_param1 := jtfb_dcf.get_parameter_value(p_param, 'P_SCALING_FACTOR') ;
548 IF l_bin_name = 'OKI_EXPIRATION_GRAPH' THEN
549 -- set up for expiration graph title
550 l_title_prefix := 'Expiration to Renewal Graph' ;
551 IF l_param1 = 1000 THEN
552 l_title_var1 := '(in thousands)' ;
553 ELSIF l_param1 = 10000 THEN
554 l_title_var1 := '(in tens thousands)' ;
555 ELSIF l_param1 = 100000 THEN
556 l_title_var1 := '(in hundred thousands)' ;
557 ELSIF l_param1 = 1000000 THEN
558 l_title_var1 := '(in millions)' ;
559 ELSIF l_param1 = 10000000 THEN
560 l_title_var1 := '(in ten millions)' ;
561 ELSIF l_param1 = 100000000 THEN
562 l_title_var1 := '(in hundred millions)' ;
563 END IF ;
564 l_title := l_title_prefix || ' ' || l_title_var1 ||
565 ' ' || l_title_suffix ;
566 END IF ;
567 return l_title ;
568 END get_title_for_bin ;
569
570 --------------------------------------------------------------------------------
571 -- Function to return the scaling factor
572 --
573 --------------------------------------------------------------------------------
574 FUNCTION get_scaling_factor RETURN VARCHAR2 IS
575 BEGIN
576 return g_scaling_factor ;
577 END get_scaling_factor ;
578
579 --------------------------------------------------------------------------------
580 -- Function to retrieve the scaling factor from the bin parameter
581 --
582 --------------------------------------------------------------------------------
583 PROCEDURE set_scaling_factor
584 ( p_param IN VARCHAR2
585 ) IS
586 l_message VARCHAR2(50) := NULL ;
587 BEGIN
588 g_scaling_factor := jtfb_dcf.get_parameter_value(p_param, 'P_SCALING_FACTOR') ;
589 END set_scaling_factor ;
590 --------------------------------------------------------------------------------
591 -- Function to get the default value for the build summary date.
592
593 --------------------------------------------------------------------------------
594 FUNCTION dflt_summary_build_date
595 ( p_name IN VARCHAR2
596 ) RETURN VARCHAR2 IS
597
598
599 l_summary_build_date VARCHAR2(60) := NULL ;
600
601 l_message VARCHAR2(50) := NULL ;
602
603
604 BEGIN
605 l_summary_build_date := TO_CHAR(TRUNC(sysdate - 1),
606 fnd_profile.value('ICX_DATE_FORMAT_MASK')) ;
607 return l_summary_build_date ;
608
609
610 EXCEPTION
611 WHEN OTHERS THEN
612 -- return the error number to the calling program;
613 l_message := substr(sqlerrm, 1, (instr(sqlerrm, ':') - 1) ) ;
614 return l_message ;
615
616 END dflt_summary_build_date ;
617
618
619 END oki_but_pvt ;