[Home] [Help]
PACKAGE BODY: APPS.OKC_TIME_UTIL_PVT
Source
1 PACKAGE BODY OKC_TIME_UTIL_PVT AS
2 /* $Header: OKCCTULB.pls 120.3 2005/12/30 10:29:53 skekkar noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 ----------------------------------------------------------------------------
6 -- The following procedure derives the most suitable period and duration based
7 -- on a start and end date.
8 ----------------------------------------------------------------------------
9
10 PROCEDURE get_seeded_timeunit (
11 p_timeunit in varchar2,
12 x_return_status out nocopy varchar2,
13 x_quantity out nocopy number,
14 x_timeunit out nocopy varchar2) is
15
16 /* Since UOM CODE is unique in MTL_UNITS_OF MEASURE we are not checking the
17 class.This procedure should also be able to handle historical data. Therefore
18 we are checking the active rows first instead of filtering using
19 active_flag = Y */
20 CURSOR time_code_unit_csr (p_uom_code IN varchar2) IS
21 SELECT tce_code, quantity
22 FROM okc_time_code_units_v
23 WHERE uom_code = p_uom_code
24 ORDER BY decode(active_flag,'Y',1,2);
25
26 /* Commented for bug 1787982
27 CURSOR time_code_unit_csr (p_uom_code IN varchar2) is
28 select tce_code, quantity
29 from okc_time_code_units_v
30 where uom_code = p_uom_code; */
31
32 l_row_not_found BOOLEAN := TRUE;
33 time_code_unit_rec time_code_unit_csr%ROWTYPE;
34 item_not_found_error EXCEPTION;
35 BEGIN
36 x_return_status := OKC_API.G_RET_STS_SUCCESS;
37 OPEN time_code_unit_csr(p_timeunit);
38 FETCH time_code_unit_csr into time_code_unit_rec;
39 l_row_not_found := time_code_unit_csr%NOTFOUND;
40 CLOSE time_code_unit_csr;
41 IF (l_row_not_found) THEN
42 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'uom_code');
43 RAISE item_not_found_error;
44 ELSE
45 x_timeunit := time_code_unit_rec.tce_code;
46 x_quantity := time_code_unit_rec.quantity;
47 END IF;
48 EXCEPTION
49 WHEN item_not_found_error THEN
50 x_return_status := OKC_API.G_RET_STS_ERROR;
51 WHEN OTHERS THEN
52 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
53 p_msg_name => g_unexpected_error,
54 p_token1 => g_sqlcode_token,
55 p_token1_value => sqlcode,
56 p_token2 => g_col_name_token,
57 p_token2_value => 'uom_code',
58 p_token3 => g_sqlerrm_token,
59 p_token3_value => sqlerrm);
60 -- notify caller of an UNEXPECTED error
61 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
62 end get_seeded_timeunit;
63
64 PROCEDURE get_uom_code (
65 p_timeunit in varchar2,
66 p_duration in number,
67 x_return_status out nocopy varchar2,
68 x_timeunit out nocopy varchar2,
69 x_duration out nocopy NUMBER) is
70
71 /*
72 CURSOR time_code_unit_csr (p_timeunit IN varchar2) is
73 SELECT uom_code, quantity
74 FROM okc_time_code_units_b
75 WHERE tce_code = p_timeunit
76 AND active_flag = 'Y'
77 AND quantity = 1
78 */
79 /* The following SQL clause was changed by msengupt to handle displaying in a higher User's Unit instead of the Standard Seeded Base Units.
80 e.g. If the user has defined Quarter as 3 Month (Seeded) and also Month as 1 Month (Seeded) in the Time Code Units,
81 the period and duration between 1/1/2000 and 6/30/2001 will be returned as 18 Months by the earlier approach. Now with the
82 query being modified to add an OR clause with Mod, The user's entry of Quarter will be considered and the query will return 6 Quarter - Bug#1821715
83 */
84 CURSOR time_code_unit_csr (p_timeunit IN varchar2, p_duration IN NUMBER) is
85 SELECT TCU.uom_code, TCU.quantity
86 --Bug 3262128 FROM okc_time_code_units_b
87 FROM okc_time_code_units_b TCU,okx_units_of_measure_v UOM
88 WHERE TCU.tce_code = p_timeunit
89 AND TCU.active_flag = 'Y'
90 --Bug 3262128 added condition to check for disable_date of UOM
91 and nvl(UOM.disable_date,trunc(sysdate)) >= trunc(sysdate)
92 AND TCU.UOM_CODE = UOM.UOM_CODE
93 AND (TCU.quantity = 1 OR mod(p_duration,TCU.quantity) = 0)
94 ORDER BY TCU.quantity desc;
95
96 /* Commented for bug 1787982
97 CURSOR time_code_unit_csr (p_timeunit IN varchar2) is
98 select uom_code
99 from okc_time_code_units_v
100 where tce_code = p_timeunit
101 and quantity = 1; */
102
103 l_row_not_found BOOLEAN := TRUE;
104 time_code_unit_rec time_code_unit_csr%ROWTYPE;
105 item_not_found_error EXCEPTION;
106 BEGIN
107 x_return_status := OKC_API.G_RET_STS_SUCCESS;
108 OPEN time_code_unit_csr(p_timeunit, p_duration);
109 FETCH time_code_unit_csr into time_code_unit_rec;
110 l_row_not_found := time_code_unit_csr%NOTFOUND;
111 CLOSE time_code_unit_csr;
112 IF (l_row_not_found) THEN
113 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'uom_code');
114 RAISE item_not_found_error;
115 ELSE
116 x_timeunit := time_code_unit_rec.uom_code;
117 if time_code_unit_rec.quantity > 1 Then
118 x_duration := p_duration/time_code_unit_rec.quantity;
119 else
120 x_duration := p_duration;
121 end if;
122 END IF;
123 EXCEPTION
124 WHEN item_not_found_error THEN
125 x_return_status := OKC_API.G_RET_STS_ERROR;
126 WHEN OTHERS THEN
127 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
128 p_msg_name => g_unexpected_error,
129 p_token1 => g_sqlcode_token,
130 p_token1_value => sqlcode,
131 p_token2 => g_col_name_token,
132 p_token2_value => 'uom_code',
133 p_token3 => g_sqlerrm_token,
134 p_token3_value => sqlerrm);
135 -- notify caller of an UNEXPECTED error
136 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
137 end get_uom_code;
138
139 PROCEDURE get_duration(
140 p_start_date in date,
141 p_end_date in date,
142 x_duration out nocopy number,
143 x_timeunit out nocopy varchar2,
144 x_return_status out nocopy varchar2) is
145 l_counter number(12,6);
146 l_date date;
147 l_timeunit varchar2(10);
148 l_offset number := 0;
149 p_duration number := 0;
150 begin
151 x_return_status := OKC_API.G_RET_STS_SUCCESS;
152 --Bug 3272514 Set the x_return_status to error if the end date is null
153 if p_end_date is NULL Then
154 x_duration := NULL;
155 x_timeunit := NULL;
156 x_return_status := OKC_API.G_RET_STS_ERROR;
157 return;
158 end if;
159 if p_start_date > p_end_date then
160 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
161 p_msg_name => G_DATE_ERROR,
162 p_token1 => G_COL_NAME_TOKEN,
163 p_token1_value => 'START_DATE');
164 x_return_status := OKC_API.G_RET_STS_ERROR;
165 return;
166 end if;
167 /*
168 if to_char(p_start_date,'DDMM') = '2902' and
169 to_char(p_end_date,'DDMM') = '2802'
170 Then
171 l_timeunit := 'YEAR';
172 p_duration := to_number(to_char(p_end_date,'YYYY')) -
173 to_number(to_char(p_start_date,'YYYY'));
174 get_uom_code(l_timeunit,p_duration,x_return_status,x_timeunit,x_duration);
175 if x_return_status <> OKC_API.G_RET_STS_SUCCESS then
176 x_duration := NULL;
177 end if;
178 return;
179 end if;
180 */
181 if (p_end_date - p_start_date) < 1 and
182 (p_end_date <> p_start_date) then
183 l_offset := round((p_end_date - p_start_date)*86400,6);
184 if mod(l_offset,3600) = 0 then
185 l_timeunit := 'HOUR';
186 p_duration := l_offset/3600;
187 elsif mod(l_offset,60)= 0 then
188 l_timeunit := 'MINUTE';
189 p_duration := l_offset/60;
190 else
191 l_timeunit := 'SECOND';
192 p_duration := l_offset;
193 end if;
194 get_uom_code(l_timeunit,p_duration,x_return_status,x_timeunit,x_duration);
195 if x_return_status <> OKC_API.G_RET_STS_SUCCESS then
196 x_duration := NULL;
197 end if;
198 return;
199 end if;
200 -- for l_counter in 1..100000000000 loop
201 l_counter := 1;
202 LOOP
203 l_date := add_months(l_counter,p_start_date) -1;
204 if p_end_date < l_date then
205 l_timeunit := 'DAY';
206 p_duration := trunc(p_end_date) - trunc(p_start_date) + 1;
207 exit;
208 elsif p_end_date = l_date then
209 /*
210 if to_char(p_end_date,'DDMM') <> '2902' Then
211 if mod(l_counter,12) = 0 then
212 l_timeunit := 'YEAR';
213 p_duration := l_counter/12;
214 exit;
215 else
216 l_timeunit := 'MONTH';
217 p_duration := l_counter;
218 exit;
219 end if;
220 else
221 */
222 if mod(l_counter,12) = 0 then
223 l_timeunit := 'YEAR';
224 p_duration := l_counter/12;
225 exit;
226 -- Added for Bug 1846434
227 else
228 l_timeunit := 'MONTH';
229 p_duration := l_counter;
230 -- Commented for Bug 1846434
231 --l_timeunit := 'DAY';
232 --p_duration := trunc(p_end_date) - trunc(p_start_date) + 1;
233 exit;
234 end if;
235 -- end if;
236 end if;
237 l_counter := l_counter+1;
238 end loop;
239 get_uom_code(l_timeunit,p_duration,x_return_status,x_timeunit,x_duration);
240 if x_return_status <> OKC_API.G_RET_STS_SUCCESS then
241 x_duration := NULL;
242 end if;
243 END get_duration;
244
245 ----------------------------------------------------------------------------
246 -- The following function returns the end date based on a start,duration and
247 -- period.
248 ----------------------------------------------------------------------------
249 FUNCTION get_enddate(
250 p_start_date in date,
251 p_timeunit varchar2,
252 p_duration number)
253 return date is
254 l_end_date date;
255 l_year number;
256 l_timeunit varchar2(10);
257 l_duration number;
258 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
259 begin
260 if p_timeunit is NULL and
261 p_duration is NULL Then
262 return (NULL);
263 end if;
264 get_seeded_timeunit(p_timeunit,x_return_status,l_duration, l_timeunit);
265 if x_return_status <> OKC_API.G_RET_STS_SUCCESS then
266 return (NULL);
267 end if;
268 l_duration := p_duration * l_duration;
269 if l_timeunit = 'YEAR' Then
270 /*
271 and to_char(p_start_date,'DDMM') = '2902'
272 Then
273 if l_duration > 0 then
274 l_year := to_number(to_char(p_start_date,'YYYY')) + l_duration;
275 l_end_date := to_date('2802'||l_year||to_char(p_start_date,'hh24miss'),'ddmmyyyyhh24miss');
276 elsif l_duration < 0 then
277 l_year := to_number(to_char(p_start_date,'YYYY')) + l_duration;
278 l_end_date := to_date('0103'||l_year||to_char(p_start_date,'hh24miss'),'ddmmyyyyhh24miss');
279 elsif l_duration = 0 then
280 l_end_date := p_start_date;
281 end if;
282 return(l_end_date);
283 */
284 if l_duration > 0 then
285 l_end_date := add_months(p_start_date,(l_duration)*12)-1;
286 elsif l_duration < 0 then
287 l_end_date := add_months(p_start_date,(l_duration)*12)+1;
288 elsif l_duration = 0 then
289 l_end_date := p_start_date;
290 end if;
291 return(l_end_date);
292 end if;
293 if l_timeunit = 'MONTH' then
294 if l_duration > 0 then
295 l_end_date := add_months(p_start_date,l_duration)-1;
296 elsif l_duration < 0 then
297 l_end_date := add_months(p_start_date,l_duration)+1;
298 elsif l_duration = 0 then
299 l_end_date := p_start_date;
300 end if;
301 return(l_end_date);
302 elsif l_timeunit = 'DAY' then
303 if l_duration > 0 then
304 l_end_date := p_start_date + l_duration - 1;
305 elsif l_duration < 0 then
306 l_end_date := p_start_date + l_duration + 1; -- added on 03/08/2002
307 elsif l_duration = 0 then
308 l_end_date := p_start_date;
309 end if;
310 return(l_end_date);
311 elsif l_timeunit = 'HOUR' then
312 if l_duration > 0 then
313 l_end_date := p_start_date + ((l_duration * 3600) - 1)/86400;
314 elsif l_duration < 0 then
315 l_end_date := p_start_date + (l_duration * 3600)/86400;
316 elsif l_duration = 0 then
317 l_end_date := p_start_date;
318 end if;
319 return(l_end_date);
320 elsif l_timeunit = 'MINUTE' then
321 if l_duration > 0 then
322 l_end_date := p_start_date + ((l_duration * 60) -1)/86400 - 1;
323 elsif l_duration < 0 then
324 l_end_date := p_start_date + (l_duration * 60)/86400;
325 elsif l_duration = 0 then
326 l_end_date := p_start_date;
327 end if;
328 return(l_end_date);
329 elsif l_timeunit = 'SECOND' then
330 if l_duration > 0 then
331 l_end_date := p_start_date + l_duration/86400 - 1;
332 elsif l_duration < 0 then
333 l_end_date := p_start_date + l_duration/86400;
334 elsif l_duration = 0 then
335 l_end_date := p_start_date;
336 end if;
337 return(l_end_date);
338 elsif l_timeunit = 'YEAR' then
339 if l_duration > 0 then
340 l_year := to_number(to_char(p_start_date,'YYYY')) + l_duration;
341 l_end_date := to_date(to_char(p_start_date,'DDMMHH24MISS') || l_year,'ddmmhh24missyyyy') -1;
342 elsif l_duration < 0 then
343 l_year := to_number(to_char(p_start_date,'YYYY')) + l_duration;
344 l_end_date := to_date(to_char(p_start_date,'DDMMHH24MISS') || l_year,'ddmmhh24missyyyy') +1;
345 elsif l_duration = 0 then
346 l_end_date := p_start_date;
347 end if;
348 return(l_end_date);
349 else
350 return(NULL);
351 end if;
352 EXCEPTION --BUG:3595566 Exception block added to catch
353 when OTHERS then -- unhandled exceptions.
354 if SQLCODE=-1841 then
355 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
356 p_msg_name => G_DATE_ERROR,
357 p_token1 => sqlcode,
358 p_token1_value => sqlerrm);
359 else
360 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
361 p_msg_name => g_unexpected_error,
362 p_token1 => sqlcode,
363 p_token1_value => sqlerrm);
364 end if;
365
366 END get_enddate;
367
368 function get_app_id
369 return NUMBER
370 IS
371 l_app_id NUMBER;
372 CURSOR c_app IS
373 SELECT application_id FROM fnd_application WHERE application_short_name = 'OKC';
374 BEGIN
375 for v_app in c_app
376 loop
377 l_app_id := v_app.application_id;
378 end loop;
379 return l_app_id;
380 END;
381
382 -- /striping/
383 function get_app_id(rule_code in varchar2)
384 return NUMBER
385 IS
386 BEGIN
387 return okc_rld_pvt.get_appl_id(rule_code);
388 END;
389
390 function get_rule_df_name
391 return varchar2
392 IS
393 BEGIN
394 return 'OKC Rule Developer DF';
395 END;
396
397 -- /striping/
398 function get_rule_df_name(rule_code in varchar2)
399 return varchar2
400 IS
401 BEGIN
402 return okc_rld_pvt.get_dff_name(rule_code);
403 END;
404
405 function get_rule_defs_using_vs(
406 p_app_id IN NUMBER,
407 p_dff_name IN VARCHAR2,
408 p_fvs_name IN VARCHAR2)
409 return varchar2
410 is
411 return_string varchar2(400);
412
413 CURSOR c_rule_dff (p_app_id IN NUMBER, p_dff_name IN VARCHAR2, p_fvs_name IN VARCHAR2) IS
414 SELECT
415 dff.form_left_prompt prompt,
416 dff.required_flag,
417 dff.display_size,
418 fvs.flex_value_set_name,
419 dff.descriptive_flex_context_code rdf_code
420 FROM
421 fnd_descr_flex_col_usage_vl dff,
422 fnd_flex_value_sets fvs
423 WHERE
424 fvs.flex_value_set_id = dff.flex_value_set_id and
425 --- need to select based on application id and descriptive flexfield name
426 dff.application_id = p_app_id and
427 dff.descriptive_flexfield_name = p_dff_name and
428 fvs.flex_value_set_name = p_fvs_name;
429 begin
430
431 for v_rule_dff in c_rule_dff(p_app_id, p_dff_name, p_fvs_name)
432 loop
433 -- only add it to the string if it is not already there
434
435 if return_string is null then
436 return_string := '''' || v_rule_dff.rdf_code || '''';
437 else
438 if instr(return_string,v_rule_dff.rdf_code) = 0 then
439 return_string := return_string || ',''' || v_rule_dff.rdf_code || '''';
440 end if;
441 end if;
442 end loop;
443
444 if return_string is not null then
445 return_string := '(' || return_string || ')';
446 end if;
447
448 return return_string;
449 end;
450
451 PROCEDURE get_dff_column_values (
452 p_app_id IN NUMBER,
453 p_dff_name IN VARCHAR2,
454 p_rdf_code IN VARCHAR2,
455 p_fvs_name IN VARCHAR2,
456 p_rule_id IN NUMBER,
457 p_col_vals OUT NOCOPY t_col_vals,
458 p_no_of_cols OUT NOCOPY NUMBER
459 )
460 IS
461 l_select_string varchar2(2000);
462 l_return_string varchar2(1000);
463 l_parse_string varchar2(1000);
464 l_value varchar2(450);
465
466 l_number_of_columns number := 0;
467
468 TYPE t_rule_cur IS REF CURSOR;
469 c_rule t_rule_cur;
470
471 CURSOR c_rule_dff (p_app_id IN NUMBER, p_dff_name IN VARCHAR2, p_rdf_code IN VARCHAR2, p_fvs_name IN VARCHAR2) IS
472 SELECT
473 dff.form_left_prompt prompt,
474 dff.required_flag,
475 dff.display_size,
476 fvs.flex_value_set_name,
477 dff.application_column_name
478 FROM
479 fnd_descr_flex_col_usage_vl dff,
480 fnd_flex_value_sets fvs
481 WHERE
482 fvs.flex_value_set_id = dff.flex_value_set_id and
483 --- need to select based on application id and descriptive flexfield name
484 dff.descriptive_flex_context_code = p_rdf_code and
485 dff.application_id = p_app_id and
486 dff.descriptive_flexfield_name = p_dff_name and
487 fvs.flex_value_set_name = p_fvs_name
488 ORDER BY
489 dff.column_seq_num;
490 begin
491
492 -- determine the timevalue columns to use
493
494 for v_rule_dff in c_rule_dff(p_app_id, p_dff_name, p_rdf_code, p_fvs_name)
495 loop
496 -- only add it to the string if it is not already there
497
498 if l_select_string is null then
499 l_select_string := v_rule_dff.application_column_name;
500 l_number_of_columns := 1;
501 p_col_vals(l_number_of_columns).col_name := v_rule_dff.application_column_name;
502 else
503 if instr(l_select_string,v_rule_dff.application_column_name) = 0 then
504 l_select_string := l_select_string ||
505 ' ||'',''|| ' || v_rule_dff.application_column_name;
506 l_number_of_columns := l_number_of_columns + 1;
507 p_col_vals(l_number_of_columns).col_name := v_rule_dff.application_column_name;
508 end if;
509 end if;
510 end loop;
511
512 -- get the dates for the rule
513
514 if l_select_string is not null and l_number_of_columns > 0 then
515 l_select_string := 'SELECT ' || l_select_string || ' FROM OKC_RULES_B WHERE ID = :ID';
516
517 open c_rule
518 for l_select_string
519 using p_rule_id;
520
521 fetch c_rule into l_return_string;
522
523 close c_rule;
524
525 end if;
526
527 -- parse the return string
528 l_parse_string := l_return_string;
529 l_return_string := null;
530 for i in 1 .. l_number_of_columns
531 loop
532 l_value := null;
533 declare
534 l_comma_idx number;
535 begin
536 if i < l_number_of_columns then
537
538 l_comma_idx := instr(l_parse_string,',');
539 if l_comma_idx > 1 then
540 l_value := substr(l_parse_string,1,l_comma_idx-1);
541 if l_comma_idx < length(l_parse_string) then
542 l_parse_string := substr(l_parse_string,l_comma_idx+1,length(l_parse_string));
543 else
544 l_parse_string := null;
545 end if;
546 else
547 l_value := null;
548 if length(l_parse_string) > 1 then
549 l_parse_string := substr(l_parse_string,2,length(l_parse_string));
550 else
551 l_parse_string := null;
552 end if;
553 end if;
554
555 else
556 l_value := l_parse_string;
557 end if;
558
559 end;
560 p_col_vals(i).col_value := l_value;
561
562 end loop;
563 p_no_of_cols := l_number_of_columns;
564
565 end;
566
567 function get_tve_ids (
568 p_app_id IN NUMBER,
569 p_dff_name IN VARCHAR2,
570 p_rdf_code IN VARCHAR2,
571 p_fvs_name IN VARCHAR2,
572 p_rule_id IN NUMBER)
573 return varchar2
574 is
575 l_select_string varchar2(2000);
576 l_return_string varchar2(1000);
577
578 TYPE t_rule_cur IS REF CURSOR;
579 c_rule t_rule_cur;
580
581 CURSOR c_rule_dff (p_app_id IN NUMBER, p_dff_name IN VARCHAR2, p_rdf_code IN VARCHAR2, p_fvs_name IN VARCHAR2) IS
582 SELECT
583 dff.form_left_prompt prompt,
584 dff.required_flag,
585 dff.display_size,
586 fvs.flex_value_set_name,
587 dff.application_column_name
588 FROM
589 fnd_descr_flex_col_usage_vl dff,
590 fnd_flex_value_sets fvs
591 WHERE
592 fvs.flex_value_set_id = dff.flex_value_set_id and
593 --- need to select based on application id and descriptive flexfield name
594 dff.descriptive_flex_context_code = p_rdf_code and
595 dff.application_id = p_app_id and
596 dff.descriptive_flexfield_name = p_dff_name and
597 fvs.flex_value_set_name = p_fvs_name
598 ORDER BY
599 dff.column_seq_num;
600 begin
601
602 -- determine the timevalue columns to use
603
604 for v_rule_dff in c_rule_dff(p_app_id, p_dff_name, p_rdf_code, p_fvs_name)
605 loop
606 -- only add it to the string if it is not already there
607
608 if l_select_string is null then
609 l_select_string := v_rule_dff.application_column_name ;
610 else
611 if instr(l_select_string,v_rule_dff.application_column_name) = 0 then
612 l_select_string := l_select_string ||
613 ' || ' || v_rule_dff.application_column_name;
614 end if;
615 end if;
616 end loop;
617
618 -- get the timevalues for the rule
619
620 if l_select_string is not null then
621 l_select_string := 'SELECT ' || l_select_string || ' FROM OKC_RULES_B WHERE ID = :ID';
622
623 open c_rule
624 for l_select_string
625 using p_rule_id;
626
627 fetch c_rule into l_return_string;
628
629 close c_rule;
630
631 end if;
632
633 if l_return_string is not null then
634 l_return_string := '(' || l_return_string || ')';
635 end if;
636
637 return l_return_string;
638 end;
639
640 -- Bug#2249285: New functions added to check offsets in months and days for Renewal of Keep Duraion Lines
641
642 FUNCTION get_uom_code(p_timeunit IN VARCHAR2) return VARCHAR2 IS
643
644 CURSOR time_code_unit_csr(p_timeunit IN VARCHAR2) IS
645 SELECT uom_code
646 FROM okc_time_code_units_b
647 WHERE tce_code = p_timeunit
648 AND active_flag = 'Y'
649 AND quantity = 1;
650
651 l_row_not_found BOOLEAN := TRUE;
652 time_code_unit_rec time_code_unit_csr%ROWTYPE;
653 item_not_found_error EXCEPTION;
654 x_timeunit VARCHAR2(40);
655 BEGIN
656 OPEN time_code_unit_csr(p_timeunit);
657 FETCH time_code_unit_csr into time_code_unit_rec;
658 l_row_not_found := time_code_unit_csr%NOTFOUND;
659 CLOSE time_code_unit_csr;
660 IF (l_row_not_found) THEN
661 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'uom_code');
662 RAISE item_not_found_error;
663 ELSE
664 x_timeunit := time_code_unit_rec.uom_code;
665 return x_timeunit;
666 END IF;
667 EXCEPTION
668 WHEN item_not_found_error THEN
669 NULL;
670 WHEN OTHERS THEN
671 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
672 p_msg_name => g_unexpected_error,
673 p_token1 => g_sqlcode_token,
674 p_token1_value => sqlcode,
675 p_token2 => g_col_name_token,
676 p_token2_value => 'uom_code',
677 p_token3 => g_sqlerrm_token,
678 p_token3_value => sqlerrm);
679 -- notify caller of an UNEXPECTED error
680 end get_uom_code;
681
682 PROCEDURE get_oracle_months_and_days(
683 p_start_date in date,
684 p_end_date in date,
685 x_month_duration out nocopy number,
686 x_day_duration out nocopy number,
687 x_return_status out nocopy varchar2) is
688 l_counter number(12,6);
689 l_date date;
690 l_previous_date date;
691 l_timeunit varchar2(10);
692 l_offset number := 0;
693 p_duration number := 0;
694 begin
695 x_return_status := OKC_API.G_RET_STS_SUCCESS;
696 if p_end_date is NULL Then
697 x_month_duration := NULL;
698 x_day_duration := NULL;
699 return;
700 end if;
701 if p_start_date > p_end_date then
702 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
703 p_msg_name => G_DATE_ERROR,
704 p_token1 => G_COL_NAME_TOKEN,
705 p_token1_value => 'START_DATE');
706 x_return_status := OKC_API.G_RET_STS_ERROR;
707 return;
708 end if;
709 if (p_end_date - p_start_date) < 1 then
710 x_day_duration := 0;
711 x_month_duration := 0;
712 return;
713 end if;
714 for l_counter in 0..100000 loop
715 l_date := add_months(l_counter,p_start_date);
716 if p_end_date < l_date then
717 if (((trunc(l_date) - trunc(l_previous_date)))/2) <= (trunc(p_end_date) - trunc(l_previous_date)) then
718 x_month_duration := l_counter;
719 x_day_duration := trunc(p_end_date) - trunc(l_date);
720 --Begin: Bug 4437843 Additional Leap year check added
721 if to_char(last_day(p_end_date),'DDMM') = '2902' THEN
722 x_day_duration := x_day_duration + 1;
723 end if;
724 --End: Bug 4437843 Additional Leap year check added
725 else
726 x_month_duration := l_counter-1;
727 x_day_duration := trunc(p_end_date) - trunc(l_previous_date);
728 end if;
729 exit;
730 elsif p_end_date = l_date then
731 x_month_duration := l_counter;
732 x_day_duration := 0;
733 exit;
734 end if;
735 l_previous_date := l_date;
736 end loop;
737 END get_oracle_months_and_days;
738
739 ----------------------------------------------------------------------------
740 -- The following procedure derives the most suitable SEEDED period and duration based
741 -- on a start and end date.
742 -- This procedure is called by oks_reprice_pvt for prorating price
743 -- bug 4919611 ( base bug 4919612)
744 ----------------------------------------------------------------------------
745
746 PROCEDURE get_pricing_duration(
747 p_start_date in date,
748 p_end_date in date,
749 x_duration out nocopy number,
750 x_timeunit out nocopy varchar2,
751 x_return_status out nocopy varchar2) is
752 l_counter number(12,6);
753 l_date date;
754 l_timeunit varchar2(10);
755 l_offset number := 0;
756 p_duration number := 0;
757 begin
758 x_return_status := OKC_API.G_RET_STS_SUCCESS;
759 --Bug 3272514 Set the x_return_status to error if the end date is null
760 if p_end_date is NULL Then
761 x_duration := NULL;
762 x_timeunit := NULL;
763 x_return_status := OKC_API.G_RET_STS_ERROR;
764 return;
765 end if;
766 if p_start_date > p_end_date then
767 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
768 p_msg_name => G_DATE_ERROR,
769 p_token1 => G_COL_NAME_TOKEN,
770 p_token1_value => 'START_DATE');
771 x_return_status := OKC_API.G_RET_STS_ERROR;
772 return;
773 end if;
774 /*
775 if to_char(p_start_date,'DDMM') = '2902' and
776 to_char(p_end_date,'DDMM') = '2802'
777 Then
778 l_timeunit := 'YEAR';
779 p_duration := to_number(to_char(p_end_date,'YYYY')) -
780 to_number(to_char(p_start_date,'YYYY'));
781 get_uom_code(l_timeunit,p_duration,x_return_status,x_timeunit,x_duration);
782 if x_return_status <> OKC_API.G_RET_STS_SUCCESS then
783 x_duration := NULL;
784 end if;
785 return;
786 end if;
787 */
788 if (p_end_date - p_start_date) < 1 and
789 (p_end_date <> p_start_date) then
790 l_offset := round((p_end_date - p_start_date)*86400,6);
791 if mod(l_offset,3600) = 0 then
792 l_timeunit := 'HOUR';
793 p_duration := l_offset/3600;
794 elsif mod(l_offset,60)= 0 then
795 l_timeunit := 'MINUTE';
796 p_duration := l_offset/60;
797 else
798 l_timeunit := 'SECOND';
799 p_duration := l_offset;
800 end if;
801 get_uom_code(l_timeunit,p_duration,x_return_status,x_timeunit,x_duration);
802 if x_return_status <> OKC_API.G_RET_STS_SUCCESS then
803 x_duration := NULL;
804 end if;
805 return;
806 end if;
807 -- for l_counter in 1..100000000000 loop
808 l_counter := 1;
809 LOOP
810 l_date := add_months(l_counter,p_start_date) -1;
811 if p_end_date < l_date then
812 l_timeunit := 'DAY';
813 p_duration := trunc(p_end_date) - trunc(p_start_date) + 1;
814 exit;
815 elsif p_end_date = l_date then
816 /*
817 if to_char(p_end_date,'DDMM') <> '2902' Then
818 if mod(l_counter,12) = 0 then
819 l_timeunit := 'YEAR';
820 p_duration := l_counter/12;
821 exit;
822 else
823 l_timeunit := 'MONTH';
824 p_duration := l_counter;
825 exit;
826 end if;
827 else
828 */
829 if mod(l_counter,12) = 0 then
830 l_timeunit := 'YEAR';
831 p_duration := l_counter/12;
832 exit;
833 -- Added for Bug 1846434
834 else
835 l_timeunit := 'MONTH';
836 p_duration := l_counter;
837 -- Commented for Bug 1846434
838 --l_timeunit := 'DAY';
839 --p_duration := trunc(p_end_date) - trunc(p_start_date) + 1;
840 exit;
841 end if;
842 -- end if;
843 end if;
844 l_counter := l_counter+1;
845 end loop;
846 -- for pricing proration, always get period which is seeded and not user defined
847 -- added for pricing call bug 4919586 ( base bug 4917510)
848 x_duration := p_duration;
849 x_timeunit := l_timeunit;
850 -- get_uom_code(l_timeunit,p_duration,x_return_status,x_timeunit,x_duration);
851 if x_return_status <> OKC_API.G_RET_STS_SUCCESS then
852 x_duration := NULL;
853 end if;
854 END get_pricing_duration;
855
856 END OKC_TIME_UTIL_PVT;