[Home] [Help]
PACKAGE BODY: APPS.OKS_TIME_MEASURES_PUB
Source
1 package body OKS_TIME_MEASURES_PUB AS
2 /* $Header: OKSSTQTB.pls 120.14.12020000.2 2012/07/23 07:09:50 spingali ship $ */
3
4 FUNCTION days_in_month(p_date IN DATE)
5
6 return NUMBER
7 as
8
9 l_month NUMBER;
10 l_day NUMBER;
11 l_year NUMBER;
12 BEGIN
13 l_month := to_number(to_char(p_date,'MM'));
14 if l_month in (1,3,5,7,8,10,12)
15 then
16 l_day := 31;
17 elsif l_month in (4,6,9,11)
18 then
19 l_day := 30;
20 else
21 l_year := to_number(to_char(p_date,'YYYY'));
22 if mod(l_year,4)= 0
23 then
24 l_day := 29;
25 else
26 l_day := 28;
27 end if;
28 end if;
29 return l_day;
30 END;
31
32 ------------------------------------------------------------------------------
33
34 FUNCTION end_of_month (p_date IN DATE)
35
36 return BOOLEAN
37 as
38
39 BEGIN
40 if to_number(to_char(p_date,'DD')) = days_in_month(p_date)
41 then
42 return TRUE;
43 else
44 return FALSE;
45 end if;
46
47 END;
48 ----------------------------------------------------------------
49
50 FUNCTION get_year_from_days(p_start_date IN DATE DEFAULT NULL,
51 p_end_date IN DATE DEFAULT NULL,
52 p_no_of_days IN NUMBER)
53 return NUMBER
54 as
55 l_start_year NUMBER;
56 l_end_year NUMBER;
57 l_leapyear_days NUMBER := 0;
58 l_no_of_years NUMBER := 0;
59
60 BEGIN
61
62 l_start_year := to_number(to_char(p_start_date,'YYYY'));
63 l_end_year := to_number(to_char(p_end_date,'YYYY'));
64
65 for i in l_start_year..l_end_year
66 loop
67
68 if mod(i,4) = 0 then
69 if l_start_year = l_end_year then
70 l_leapyear_days := p_end_date - p_start_date +1;
71 else
72 if i = l_start_year then
73 l_leapyear_days := l_leapyear_days + to_date('31-12-'||to_char(l_start_year),'DD-MM-YYYY') - p_start_date +1;
74 elsif i = l_end_year then
75 l_leapyear_days := l_leapyear_days + p_end_date- to_date('01-01-'||to_char(l_end_year),'DD-MM-YYYY') +1;
76 else
77 l_leapyear_days := l_leapyear_days+366;
78 end if;
79 end if;
80 end if;
81 end loop;
82
83 l_no_of_years := ((p_no_of_days - l_leapyear_days)/365) + (l_leapyear_days/366);
84
85 return l_no_of_years;
86
87 END;
88
89 ------------------------------------------------------
90
91 FUNCTION get_uom_code(p_tce_code IN VARCHAR2
92 ,p_quantity IN NUMBER)
93 return VARCHAR2
94 as
95
96 Cursor cs_uom(cp_tce_code IN VARCHAR2
97 ,cp_quantity IN NUMBER)
98 is
99 select uom_code
100 from OKC_TIME_CODE_UNITS_V
101 where tce_code = cp_tce_code and quantity = cp_quantity
102 and active_flag = 'Y';
103
104 l_uom VARCHAR2(10);
105 invalid_uom_exception EXCEPTION;
106
107 BEGIN
108
109 Open cs_uom(p_tce_code,p_quantity);
110 Fetch cs_uom Into l_uom;
111
112 IF cs_uom%NOTFOUND
113 THEN
114 RAISE INVALID_UOM_EXCEPTION;
115 END IF;
116
117 Close cs_uom;
118
119 return l_uom;
120
121 EXCEPTION
122 WHEN
123 INVALID_UOM_EXCEPTION
124 THEN
125 OKC_API.SET_MESSAGE(p_app_name => 'OKS',
126 p_msg_name => 'OKS_INVD_UOM_CODE',
127 p_token1 => 'OKS_API_NAME',
128 p_token1_value => 'OKS_TIME_MEASURES_PUB.get_uom_code',
129 p_token2 => 'UOM_CODE',
130 p_token2_value => p_tce_code);
131 close cs_uom;
132 return 0;
133
134 END get_uom_code;
135 --------------------------
136
137 FUNCTION get_target_qty (p_start_date IN DATE DEFAULT NULL,
138 p_source_qty IN NUMBER,
139 p_source_uom IN VARCHAR2,
140 p_target_uom IN VARCHAR2,
141 p_round_dec IN NUMBER)
142 return NUMBER
143 as
144 CURSOR cs_validate_uom(p_uom_code IN VARCHAR2)
145 is
146 SELECT 1
147 FROM okx_units_of_measure_v
148 WHERE uom_code = p_uom_code;
149 cr_validate_uom cs_validate_uom%ROWTYPE;
150 l_target_qty NUMBER;
151 l_learyear_yn VARCHAR2(1);
152 l_start_date date;
153 l_end_date date;
154 l_no_days NUMBER;
155
156 l_day VARCHAR2(40);
157 l_month VARCHAR2(40);
158 l_year VARCHAR2(40);
159 l_week VARCHAR2(40);
160 l_minute VARCHAR2(40);
161 l_hour VARCHAR2(40);
162 -- Commented for the Bug # 3077436
163 -- l_sec VARCHAR2(40);
164 -- Bug # 3077436
165 l_quarter VARCHAR2(40);
166 l_multiplier NUMBER;
167
168 invalid_uom_exception EXCEPTION;
169
170 BEGIN
171
172 open cs_validate_uom(p_source_uom);
173 fetch cs_validate_uom into cr_validate_uom;
174 IF cs_validate_uom%NOTFOUND
175 THEN
176 RAISE INVALID_UOM_EXCEPTION;
177 END IF;
178
179 close cs_validate_uom;
180
181 open cs_validate_uom(p_target_uom);
182 fetch cs_validate_uom into cr_validate_uom;
183 IF cs_validate_uom%NOTFOUND
184 THEN
185 RAISE INVALID_UOM_EXCEPTION;
186 END IF;
187
188 close cs_validate_uom;
189
190 l_day := upper(get_uom_code('DAY',1));
191 l_month := upper(get_uom_code('MONTH',1));
192 l_year := upper(get_uom_code('YEAR',1));
193 l_week := upper(get_uom_code('DAY',7));
194 l_minute:=upper(get_uom_code('MINUTE',1));
195 l_hour:=upper(get_uom_code('HOUR',1));
196 -- Commented for the Bug # 3077436
197 -- l_sec:=upper(get_uom_code('SECOND',1));
198 -- Bug # 3077436
199 l_quarter :=upper(get_uom_code('MONTH',3));
200
201
202 IF p_source_uom = p_target_uom
203 THEN
204 l_target_qty := p_source_qty;
205 ELSE
206 IF p_start_date IS NOT NULL
207 THEN
208 l_start_date := p_start_date;
209 l_end_date := okc_time_util_pub.get_enddate(p_start_date,p_source_uom,p_source_qty);
210 ELSE
211 l_start_date := sysdate;
212 l_end_date := okc_time_util_pub.get_enddate(sysdate,p_source_uom,p_source_qty);
213 END IF;
214
215 l_no_days := l_end_date - l_start_date+1;
216
217 IF upper(p_source_uom) =l_year
218 THEN
219 IF upper(p_target_uom) = l_quarter
220 THEN
221 l_target_qty := p_source_qty*4;
222 ELSIF upper(p_target_uom) = l_month
223 THEN
224 l_target_qty := p_source_qty*12;
225 ELSE
226 -- Changed to get relationship from okc_time_code_units_b bug no:4255530
227 l_multiplier := get_con_factor(p_source_uom, p_target_uom);
228 IF l_multiplier is NULL THEN
229 l_target_qty := GET_QTY_FOR_DAYS(l_no_days,p_target_uom);
230 ELSE
231 l_target_qty := p_source_qty * l_multiplier;
232 END IF;
233 --end of change bug no:4255530
234 END IF;
235 ELSIF upper(p_source_uom) = l_month
236 THEN
237 IF upper(p_target_uom) = l_year
238 THEN
239 l_target_qty := p_source_qty/12;
240 ELSIF upper(p_target_uom) = l_quarter
241 THEN
242 l_target_qty := p_source_qty/3;
243 ELSE
244 -- Changed to get relationship from okc_time_code_units_b bug no:4255530
245 l_multiplier := get_con_factor(p_source_uom, p_target_uom);
246 IF l_multiplier is NULL THEN
247 l_target_qty := GET_QTY_FOR_DAYS(l_no_days,p_target_uom);
248 ELSE
249 l_target_qty := p_source_qty * l_multiplier;
250 END IF;
251 --end of change bug no:4255530
252 END IF;
253 ELSIF upper(p_source_uom) = l_quarter
254 THEN
255 IF upper(p_target_uom) = l_year
256 THEN
257 l_target_qty := p_source_qty/4;
258 ELSIF upper(p_target_uom) = l_month
259 THEN
260 l_target_qty := p_source_qty*3;
261 ELSE
262 -- Changed to get relationship from okc_time_code_units_b bug no:4255530
263 l_multiplier := get_con_factor(p_source_uom, p_target_uom);
264 IF l_multiplier is NULL THEN
265 l_target_qty := GET_QTY_FOR_DAYS(l_no_days,p_target_uom);
266 ELSE
267 l_target_qty := p_source_qty * l_multiplier;
268 END IF;
269 --end of change bug no:4255530
270 END IF;
271 ELSIF upper(p_source_uom) = l_day
272 THEN
273 IF upper(p_target_uom) = l_year
274 THEN
275 l_target_qty := get_year_from_days (l_start_date,l_end_date,l_no_days);
276 ELSIF upper(p_target_uom) = l_month
277 THEN
278 l_target_qty := months_between(l_end_date+1,l_start_date);
279 ELSIF upper(p_target_uom) = l_quarter
280 THEN
281 l_target_qty := (months_between(l_end_date+1,l_start_date))/3;
282 ELSE
283 -- Changed to get relationship from okc_time_code_units_b bug no:4255530
284 l_multiplier := get_con_factor(p_source_uom, p_target_uom);
285 IF l_multiplier is NULL THEN
286 l_target_qty := GET_QTY_FOR_DAYS(l_no_days,p_target_uom);
287 ELSE
288 l_target_qty := p_source_qty * l_multiplier;
289 END IF;
290 --end of change bug no:4255530
291 END IF;
292 -- Changed the condition for Bug # 3077436
293 --ELSIF upper(p_source_uom) in (l_week,l_day,l_hour,l_minute,l_sec)
294 ELSIF upper(p_source_uom) in (l_week,l_hour,l_minute)
295 -- Changed the condition for Bug # 3077436
296 THEN
297 IF upper(p_target_uom) = l_quarter
298 THEN
299 l_target_qty := (months_between(l_end_date+1,l_start_date))/3;
300 ELSIF upper(p_target_uom) = l_month
301 THEN
302 l_target_qty := months_between(l_end_date+1,l_start_date);
303 ELSE
304 -- Changed to get relationship from okc_time_code_units_b bug no:4255530
305 l_multiplier := get_con_factor(p_source_uom, p_target_uom);
306 IF l_multiplier is NULL THEN
307 l_target_qty := GET_QTY_FOR_DAYS(l_no_days,p_target_uom);
308 ELSE
309 l_target_qty := p_source_qty * l_multiplier;
310 END IF;
311 --end of change bug no:4255530
312 END IF;
313 END IF;
314 /* ELSIF upper(p_source_uom) = l_week
315 THEN
316 IF upper(p_target_uom) = l_month
317 THEN
318 l_target_qty := months_between(l_end_date+1,l_start_date);
319 ELSE
320 l_target_qty := GET_QTY_FOR_DAYS(l_no_days,p_target_uom);
321 END IF;
322 ELSIF upper(p_source_uom) = l_day
323 THEN
324 IF upper(p_target_uom) = l_month
325 THEN
326 l_target_qty := months_between(l_end_date+1,l_start_date);
327 ELSE
328 l_target_qty := GET_QTY_FOR_DAYS(l_no_days,p_target_uom);
329 END IF;
330 END IF;
331 */
332 /*
333 The following condn will meet if target_uom is a user defined uom and there
334 no common conv factor available bw source ,target uom in Map Time units form
335 bugno:4255530
336 */
337 IF (nvl(l_target_qty,0) =0 AND p_source_qty <> 0)
338 THEN
339 l_target_qty := (okc_time_util_pub.get_enddate(nvl(p_start_date,SYSDATE),p_source_uom,p_source_qty)-nvl(p_start_date,SYSDATE)+1)
340 /(okc_time_util_pub.get_enddate(nvl(p_start_date,SYSDATE),p_target_uom,1)-nvl(p_start_date,SYSDATE)+1);
341 END IF;
342 -- End bugno:4255530
343 END IF;
344
345 l_target_qty := round(l_target_qty,p_round_dec);
346 return l_target_qty;
347
348 EXCEPTION
349 WHEN
350 INVALID_UOM_EXCEPTION
351 THEN
352 OKC_API.SET_MESSAGE(p_app_name => 'OKS',
353 p_msg_name => 'OKS_INVD_UOM_CODE',
354 p_token1 => 'OKS_API_NAME',
355 p_token1_value => 'OKS_TIME_MEASURES_PUB.get_target_qty',
356 p_token2 => 'UOM_CODE',
357 p_token2_value => p_source_uom );
358 close cs_validate_uom;
359 return 0;
360 END get_target_qty;
361
362 -------------------------------------------------------------
363
364 PROCEDURE get_duration_uom ( p_start_date IN DATE
365 , p_end_date IN DATE
366 , x_duration OUT nocopy NUMBER
367 , x_timeunit OUT nocopy VARCHAR2
368 , x_return_status OUT nocopy VARCHAR2)
369 IS
370
371 l_counter number(12,6);
372 l_date date;
373 l_timeunit varchar2(10);
374 l_offset number := 0;
375 l_duration number := 0;
376 l_duration_wk number := 0;
377 l_duration_mth number := 0;
378
379 BEGIN
380 x_return_status := OKC_API.G_RET_STS_SUCCESS;
381 if p_end_date is NULL Then
382 x_duration := NULL;
383 x_timeunit := NULL;
384 return;
385 end if;
386 if p_start_date > p_end_date then
387 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
388 p_msg_name => G_DATE_ERROR,
389 p_token1 => G_COL_NAME_TOKEN,
390 p_token1_value => 'START_DATE');
391 x_return_status := OKC_API.G_RET_STS_ERROR;
392 return;
393 end if;
394
395 okc_time_util_pub.get_duration(p_start_date,p_end_date,l_duration,l_timeunit,x_return_status);
396 if x_return_status <> OKC_API.G_RET_STS_SUCCESS then
397 l_duration := NULL;
398 l_timeunit := NULL;
399 end if;
400
401 l_duration := get_target_qty(p_start_date,l_duration,l_timeunit,get_uom_code('DAY',1),2);
402
403 if l_duration >=7
404 then
405
406 l_duration_wk := get_target_qty(p_start_date,l_duration,get_uom_code('DAY',1),get_uom_code('DAY',7),2);
407 l_duration_mth := get_target_qty(p_start_date,l_duration,get_uom_code('DAY',1),get_uom_code('MONTH',1),2);
408 if l_duration_mth < 1 then
409 x_duration := l_duration_wk;
410 x_timeunit := get_uom_code('DAY',7);
411 elsif l_duration_mth >= 3 and l_duration_mth < 12
412 then
413 x_duration := get_target_qty(p_start_date,l_duration_mth,get_uom_code('MONTH',1),get_uom_code('MONTH',3),2);
414 x_timeunit := get_uom_code('MONTH',3);
415 elsif l_duration_mth >= 12
416 then
417 x_duration := get_target_qty(p_start_date,l_duration_mth,get_uom_code('MONTH',1),get_uom_code('YEAR',1),2);
418 x_timeunit := get_uom_code('YEAR',1);
419 elsif l_duration_mth >=1 and l_duration_mth < 3
420 then
421 x_duration := l_duration_mth; --get_target_qty(p_start_date,l_duration,l_timeunit,get_uom_code('MONTH',1),2);
422 x_timeunit := get_uom_code('MONTH',1);
423 else
424 x_duration := l_duration;
425 x_timeunit := l_timeunit;
426 end if;
427 else
428 x_duration := l_duration;
429 x_timeunit := l_timeunit;
430 end if;
431
432 END get_duration_uom;
433
434 --------------------------------------------------------------------------
435
436 FUNCTION GET_QTY_FOR_DAYS (p_no_days IN NUMBER,
437 p_target_uom IN VARCHAR2)
438 return NUMBER
439 as
440 l_target_qty NUMBER;
441 no_strdt_exception EXCEPTION;
442
443 l_day VARCHAR2(40);
444 l_month VARCHAR2(40);
445 l_year VARCHAR2(40);
446 l_week VARCHAR2(40);
447 l_minute VARCHAR2(40);
448 l_hour VARCHAR2(40);
449 -- Commented for the Bug # 3077436
450 -- l_sec VARCHAR2(40);
451 -- Bug # 3077436
452
453 BEGIN
454
455
456 IF p_no_days IS NULL then
457 RAISE NO_STRDT_EXCEPTION;
458 END IF;
459
460
461 l_day := upper(get_uom_code('DAY',1));
462 l_month := upper(get_uom_code('MONTH',1));
463 l_year := upper(get_uom_code('YEAR',1));
464 l_week := upper(get_uom_code('DAY',7));
465 l_minute:=upper(get_uom_code('MINUTE',1));
466 l_hour:=upper(get_uom_code('HOUR',1));
467 -- Commented for the Bug # 3077436
468 -- l_sec:=upper(get_uom_code('SECOND',1));
469 -- Bug # 3077436
470
471 IF upper(p_target_uom) = l_year
472 THEN
473 l_target_qty := p_no_days/365;
474 ELSIF upper(p_target_uom) = l_week
475 THEN
476 l_target_qty := p_no_days/7;
477 ELSIF upper(p_target_uom) = l_day
478 THEN
479 l_target_qty := p_no_days;
480 ELSIF upper(p_target_uom) = l_hour
481 THEN
482 l_target_qty := p_no_days*24;
483 ELSIF upper(p_target_uom) = l_minute
484 THEN
485 l_target_qty := p_no_days*1440;
486 -- Commented for the Bug # 3077436
487 -- ELSIF upper(p_target_uom) = l_sec
488 -- THEN
489 -- l_target_qty := p_no_days*86400;
490 -- Bug # 3077436
491 END IF;
492 return l_target_qty;
493 EXCEPTION
494 WHEN
495 NO_STRDT_EXCEPTION
496 THEN
497 OKC_API.set_message('OKS','OKS_START_DATE_REQD');
498 return 0;
499 END;
500
501 ------------------------------------------------------------------------
502 /* This function includes logic to calculate duration based on Period Start and Period Type
503 in addition to present way of calculating the duration
504 */
505 FUNCTION get_quantity(p_start_date IN DATE,
506 p_end_date IN DATE,
507 p_source_uom IN VARCHAR2 DEFAULT NULL,
508 p_period_type IN VARCHAR2 DEFAULT NULL, --New paramter
509 p_period_start IN VARCHAR2 DEFAULT NULL) --New parameter
510 return NUMBER
511 as
512
513
514 CURSOR cs_validate_uom(p_uom_code IN VARCHAR2)
515 is
516 SELECT 1
517 FROM MTL_UNITS_OF_MEASURE_TL
518 WHERE uom_code = p_uom_code
519 AND LANGUAGE = USERENV('LANG');
520
521 cr_validate_uom cs_validate_uom%ROWTYPE;
522
523
524 --------------------------------------------------
525 CURSOR get_tce_code(p_uom_code IN VARCHAR2)
526 is
527 SELECT tce_code
528 FROM OKC_TIME_CODE_UNITS_B
529 WHERE uom_code=p_uom_code;
530
531 ----------------------------------------------------
532
533 l_target_qty NUMBER;
534 l_source_qty NUMBER;
535 l_learyear_yn VARCHAR2(1);
536 l_start_date date;
537 l_end_date date;
538 l_chr_date VARCHAR2(20);
539 l_no_days NUMBER;
540 l_tce_code VARCHAR2(30);
541 l_period_start VARCHAR2(30);
542
543 l_period_uom_code VARCHAR2(80);
544 l_status VARCHAR2(80);
545
546 invalid_uom_exception EXCEPTION;
547 l_source_uom VARCHAR2(100) := NULL;
548
549
550 BEGIN
551
552 l_chr_date := to_char(p_start_date,'YYYY/MM/DD');
553 l_start_date := to_date(l_chr_date,'YYYY/MM/DD');
554 l_chr_date := to_char(p_end_date,'YYYY/MM/DD');
555 l_end_date := to_date(l_chr_date,'YYYY/MM/DD');
556
557 IF p_source_uom Is Null Then
558 l_source_uom := get_uom_code('MONTH',1);
559
560 Else
561 open cs_validate_uom(p_source_uom);
562 fetch cs_validate_uom into cr_validate_uom;
563
564 IF cs_validate_uom%NOTFOUND
565 THEN
566 RAISE INVALID_UOM_EXCEPTION;
567 END IF;
568
569 l_source_uom := p_source_uom;
570 close cs_validate_uom;
571
572 End If;
573
574 --for calendar month start, the uom should be defined in multiples of months
575 open get_tce_code(p_source_uom);
576 fetch get_tce_code into l_tce_code;
577 close get_tce_code;
578
579
580 IF (l_tce_code = 'YEAR') OR
581 (l_tce_code = 'MONTH')OR
582 (p_period_start IS NULL)
583 THEN
584 l_period_start := p_period_start;
585 ELSE
586 l_period_start := 'SERVICE';
587 END IF;
588
589 IF l_period_start ='CALENDAR' AND p_period_type IS NOT NULL
590 THEN
591 l_target_qty := get_target_qty_cal(p_start_date,
592 p_end_date,
593 p_source_uom,
594 p_period_type,
595 18);
596 ELSIF l_period_start ='SERVICE' AND p_period_type IS NOT NULL
597 THEN
598 l_target_qty := get_target_qty_service(p_start_date,
599 p_end_date,
600 p_source_uom,
601 p_period_type,
602 18);
603 ELSE
604
605 okc_time_util_pub.get_duration (
606 p_start_date => l_start_date,
607 p_end_date => l_end_date,
608 x_duration => l_source_qty,
609 x_timeunit => l_period_uom_code,
610 x_return_status => l_status);
611
612 l_target_qty := get_target_qty(l_start_date,
613 l_source_qty,
614 l_period_uom_code,
615 l_source_uom,
616 18);
617 END IF;
618 return l_target_qty;
619
620 EXCEPTION
621 WHEN
622 INVALID_UOM_EXCEPTION
623 THEN
624 OKC_API.SET_MESSAGE(p_app_name => 'OKS',
625 p_msg_name => 'OKS_INVD_UOM_CODE',
626 p_token1 => 'OKS_API_NAME',
627 p_token1_value => 'OKS_TIME_MEASURES_PUB.get_quantity',
628 p_token2 => 'UOM_CODE',
629 p_token2_value => p_source_uom );
630 close cs_validate_uom;
631 return 0;
632 WHEN OTHERS THEN
633 OKC_API.set_message(G_APP_NAME,
634 G_UNEXPECTED_ERROR,
635 G_SQLCODE_TOKEN,
636 SQLCODE,
637 G_SQLERRM_TOKEN,
638 SQLERRM);
639 return 0;
640
641
642 END get_quantity;
643
644 ------------------------------------------------------------------------
645 -- This function calculates the duration using partial period logic if period start is 'calendar'
646
647 FUNCTION get_target_qty_cal(p_start_date IN DATE,
648 p_end_date IN DATE,
649 p_price_uom IN VARCHAR2,
650 p_period_type IN VARCHAR2,
651 p_round_dec IN NUMBER)
652
653 return NUMBER
654 as
655
656 CURSOR cs_validate_uom(p_uom_code IN VARCHAR2)
657 is
658 SELECT 1
659 FROM MTL_UNITS_OF_MEASURE_TL
660 WHERE uom_code = p_uom_code
661 --AND uom_class = 'Time' commented for bug#5585356
662 AND LANGUAGE = USERENV('LANG');
663 cr_validate_uom cs_validate_uom%ROWTYPE;
664
665 l_full_periods NUMBER;
666 l_full_period_end_date DATE;
667 l_status VARCHAR2(80);
668 l_ppc_begin NUMBER;
669 l_ppc_end NUMBER;
670 l_period_tot_duration NUMBER;
671 l_full_period_start_date DATE;
672 l_ppc_end_start_date DATE;
673 l_month_end_date DATE;
674
675 invalid_period_type_exception EXCEPTION;
676 invalid_date_exception EXCEPTION;
677 invalid_uom_exception EXCEPTION;
678 BEGIN
679 IF upper(p_period_type) NOT IN ('ACTUAL','FIXED')
680 THEN
681 RAISE INVALID_PERIOD_TYPE_EXCEPTION;
682 END IF;
683 open cs_validate_uom(p_price_uom);
684 fetch cs_validate_uom into cr_validate_uom;
685 IF cs_validate_uom%NOTFOUND
686 THEN
687 RAISE INVALID_UOM_EXCEPTION;
688 END IF;
689 close cs_validate_uom;
690 IF (p_start_date IS NULL)OR(p_end_date IS NULL)OR(p_start_date > p_end_date)
691 THEN
692 RAISE INVALID_DATE_EXCEPTION;
693 END IF;
694 IF (trunc(p_start_date,'MM')= p_start_date)
695 THEN
696 l_ppc_begin :=0;
697 l_full_period_start_date :=p_start_date;
698 ELSE
699 l_month_end_date := last_day(p_start_date);
700 IF p_end_date <= l_month_end_date
701 THEN
702 l_ppc_begin :=get_partial_period_duration(p_start_date,
703 p_end_date,
704 p_price_uom,
705 p_period_type,
706 'CALENDAR');
707 IF l_ppc_begin = -1
708 THEN
709 return 0;
710 END IF;
711 return l_ppc_begin;
712 ELSE
713 l_ppc_begin :=get_partial_period_duration(p_start_date,
714 l_month_end_date,
715 p_price_uom,
716 p_period_type,
717 'CALENDAR');
718 IF l_ppc_begin = -1
719 THEN
720 return 0;
721 END IF;
722 l_full_period_start_date := l_month_end_date+1;
723 END IF;
724 END IF;
725
726 -- To find number of full periods between given date range
727 get_full_periods (
728 p_start_date => l_full_period_start_date,
729 p_end_date => p_end_date,
730 p_price_uom => p_price_uom,
731 x_full_periods => l_full_periods,
732 x_full_period_end_date => l_full_period_end_date,
733 x_return_status => l_status);
734
735 IF l_status = OKC_API.G_RET_STS_ERROR
736 THEN
737 return 0;
738 END IF;
739
740 IF l_full_periods = 0
741 THEN
742 l_ppc_end_start_date := l_full_period_start_date;
743 ELSE
744 l_ppc_end_start_date := l_full_period_end_date+1;
745 END IF;
746
747 IF l_full_period_end_date = p_end_date
748 THEN
749 l_ppc_end :=0;
750 ELSE
751 l_ppc_end := get_partial_period_duration(l_ppc_end_start_date,
752 p_end_date,
753 p_price_uom,
754 p_period_type,
755 'CALENDAR');
756 IF l_ppc_end = -1
757 THEN
758 return 0;
759 END IF;
760 END IF;
761
762 l_period_tot_duration := ROUND(l_ppc_begin+l_full_periods+l_ppc_end, p_round_dec);
763
764 return l_period_tot_duration;
765
766 EXCEPTION
767 WHEN
768 INVALID_PERIOD_TYPE_EXCEPTION
769 THEN
770 OKC_API.set_message(
771 p_app_name => G_APP_NAME,
772 p_msg_name => G_INVALID_VALUE,
773 p_token1 => G_COL_NAME_TOKEN,
774 p_token1_value => 'Period type');
775 return 0;
776 WHEN
777 INVALID_UOM_EXCEPTION
778 THEN
779 OKC_API.SET_MESSAGE(p_app_name => 'OKS',
780 p_msg_name => 'OKS_INVD_UOM_CODE',
781 p_token1 => 'OKS_API_NAME',
782 p_token1_value => 'OKS_TIME_MEASURES_PUB.get_target_qty_cal',
783 p_token2 => 'UOM_CODE',
784 p_token2_value => p_price_uom);
785
786 close cs_validate_uom;
787 return 0;
788 WHEN
789 INVALID_DATE_EXCEPTION
790 THEN
791 OKC_API.set_message('OKC','OKC_REP_INV_EFF_DATE_SD');
792 return 0;
793 WHEN OTHERS THEN
794 OKC_API.set_message(G_APP_NAME,
795 G_UNEXPECTED_ERROR,
796 G_SQLCODE_TOKEN,
797 SQLCODE,
798 G_SQLERRM_TOKEN,
799 SQLERRM);
800 return 0;
801
802 END get_target_qty_cal;
803
804 ------------------------------------------------------------------------
805 -- This function calculates the duration using partial period logic if period start is service
806
807 FUNCTION get_target_qty_service(p_start_date IN DATE,
808 p_end_date IN DATE,
809 p_price_uom IN VARCHAR2,
810 p_period_type IN VARCHAR2,
811 p_round_dec IN NUMBER)
812 return NUMBER
813 as
814 CURSOR cs_validate_uom(p_uom_code IN VARCHAR2)
815 is
816 SELECT 1
817 FROM MTL_UNITS_OF_MEASURE_TL
818 WHERE uom_code = p_uom_code
819 --AND uom_class = 'Time' commented for bug#5585356
820 AND LANGUAGE = USERENV('LANG');
821
822 cr_validate_uom cs_validate_uom%ROWTYPE;
823
824
825 l_full_periods NUMBER;
826 l_full_period_end_date DATE;
827 l_status VARCHAR2(80);
828 l_ppc_end_start_date DATE;
829 l_ppc_end NUMBER;
830 l_period_tot_duration NUMBER;
831 ---------------------------------------------
832 l_source_uom_quantity NUMBER;
833 l_source_tce_code VARCHAR2(30);
834 l_target_uom_quantity NUMBER;
835 l_target_tce_code VARCHAR2(30);
836 l_return_status VARCHAR2(1);
837 l_service_duration Number;
838 l_service_period Varchar2(30);
839 l_called_from Varchar2(20);
840 ------------------------------------------------
841
842 invalid_period_type_exception EXCEPTION;
843 invalid_date_exception EXCEPTION;
844 invalid_uom_exception EXCEPTION;
845
846 BEGIN
847
848 IF upper(p_period_type) NOT IN ('ACTUAL','FIXED')
849 THEN
850 RAISE INVALID_PERIOD_TYPE_EXCEPTION;
851 END IF;
852 open cs_validate_uom(p_price_uom);
853 fetch cs_validate_uom into cr_validate_uom;
854 IF cs_validate_uom%NOTFOUND
855 THEN
856 RAISE INVALID_UOM_EXCEPTION;
857 END IF;
858 close cs_validate_uom;
859 IF (p_start_date IS NULL)OR(p_end_date IS NULL)OR(p_start_date > p_end_date)
860 THEN
861 RAISE INVALID_DATE_EXCEPTION;
862 END IF;
863
864 -- To find number of full periods between given date range
865 get_full_periods (
866 p_start_date => p_start_date,
867 p_end_date => p_end_date,
868 p_price_uom => p_price_uom,
869 x_full_periods => l_full_periods,
870 x_full_period_end_date => l_full_period_end_date,
871 x_return_status => l_status);
872
873 IF l_status = OKC_API.G_RET_STS_ERROR
874 THEN
875 return 0;
876 END IF;
877
878 IF l_full_periods = 0
879 THEN
880 l_ppc_end_start_date := p_start_date;
881 ELSE
882 l_ppc_end_start_date := l_full_period_end_date+1;
883 END IF;
884
885 IF l_full_period_end_date = p_end_date
886 THEN
887 l_ppc_end :=0;
888 ELSE
889 l_ppc_end := get_partial_period_duration(l_ppc_end_start_date,
890 p_end_date,
891 p_price_uom,
892 p_period_type,
893 'SERVICE');
894 IF l_ppc_end = -1
895 THEN
896 return 0;
897 END IF;
898 END IF;
899 l_period_tot_duration := ROUND(l_full_periods + l_ppc_end,p_round_dec);
900
901 return l_period_tot_duration;
902
903 EXCEPTION
904 WHEN
905 INVALID_PERIOD_TYPE_EXCEPTION
906 THEN
907 OKC_API.set_message(
908 p_app_name => G_APP_NAME,
909 p_msg_name => G_INVALID_VALUE,
910 p_token1 => G_COL_NAME_TOKEN,
911 p_token1_value => 'Period type');
912
913 return 0;
914 WHEN
915 INVALID_UOM_EXCEPTION
916 THEN
917 OKC_API.SET_MESSAGE(p_app_name => 'OKS',
918 p_msg_name => 'OKS_INVD_UOM_CODE',
919 p_token1 => 'OKS_API_NAME',
920 p_token1_value => 'OKS_TIME_MEASURES_PUB.get_target_qty_service',
921 p_token2 => 'UOM_CODE',
922 p_token2_value => p_price_uom);
923 close cs_validate_uom;
924 return 0;
925 WHEN
926 INVALID_DATE_EXCEPTION
927 THEN
928 OKC_API.set_message('OKC','OKC_REP_INV_EFF_DATE_SD');
929 return 0;
930 WHEN OTHERS THEN
931 OKC_API.set_message(G_APP_NAME,
932 G_UNEXPECTED_ERROR,
933 G_SQLCODE_TOKEN,
934 SQLCODE,
935 G_SQLERRM_TOKEN,
936 SQLERRM);
937 return 0;
938
939
940 END get_target_qty_service;
941
942
943 ------------------------------------------------------------------------
944 /* This function return partial period quantity in terms of UOM. If UOM duration is more than
945 month, it will determine partial period using full months and partial month else determines
946 partial period using just days */
947
948 FUNCTION get_partial_period_duration (p_start_date IN DATE,
949 p_end_date IN DATE,
950 p_price_uom IN VARCHAR2,
951 p_period_type IN VARCHAR2,
952 p_period_start IN VARCHAR2)
953 return NUMBER
954 as
955
956 CURSOR cs_validate_uom(p_uom_code IN VARCHAR2)
957 is
958 SELECT 1
959 FROM MTL_UNITS_OF_MEASURE_TL
960 WHERE uom_code = p_uom_code
961 --AND uom_class = 'Time' commented for bug#5585356
962 AND LANGUAGE = USERENV('LANG');
963 cr_validate_uom cs_validate_uom%ROWTYPE;
964
965 l_months_in_uom NUMBER;
966 l_days_in_partial_period NUMBER;
967 l_full_months NUMBER;
968 l_partial_mth_start_date DATE;
969 l_days_in_partial_mth NUMBER;
970 l_days_in_mth NUMBER;
971 l_days_in_uom NUMBER;
972 l_duration_in_mths NUMBER;
973 l_partial_period NUMBER;
974 x_return_status VARCHAR2(10);
975 l_uom_quantity NUMBER;
976 l_tce_code VARCHAR2(10);
977
978 invalid_period_type_exception EXCEPTION;
979 invalid_period_start_exception EXCEPTION;
980 invalid_date_exception EXCEPTION;
981 invalid_uom_exception EXCEPTION;
982 BEGIN
983
984 IF upper(p_period_type) NOT IN ('ACTUAL','FIXED')
985 THEN
986 RAISE INVALID_PERIOD_TYPE_EXCEPTION;
987 END IF;
988
989 IF upper(p_period_start) NOT IN ('CALENDAR','SERVICE')
990 THEN
991 RAISE INVALID_PERIOD_START_EXCEPTION;
992 END IF;
993 open cs_validate_uom(p_price_uom);
994 fetch cs_validate_uom into cr_validate_uom;
995 IF cs_validate_uom%NOTFOUND
996 THEN
997 RAISE INVALID_UOM_EXCEPTION;
998 END IF;
999 close cs_validate_uom;
1000 IF p_start_date > p_end_date
1001 THEN
1002 RAISE INVALID_DATE_EXCEPTION;
1003 END IF;
1004 --mchoudha fix for bug#5199908
1005 OKS_BILL_UTIL_PUB.Get_Seeded_Timeunit
1006 (p_timeunit => p_price_uom,
1007 x_return_status => x_return_status,
1008 x_quantity => l_uom_quantity ,
1009 x_timeunit => l_tce_code);
1010 IF x_return_status <> 'S' THEN
1011 RAISE INVALID_UOM_EXCEPTION;
1012 END IF;
1013 --The following logic is to get other uoms conversion factor in Month
1014 l_months_in_uom := months_between(OKC_TIME_UTIL_PUB.get_enddate(TRUNC(p_start_date,'MM'),p_price_uom,1)+1 ,TRUNC(p_start_date,'MM'));
1015 IF(l_months_in_uom <1 AND l_tce_code='DAY')
1016 THEN
1017 l_days_in_partial_period := (p_end_date+1)- p_start_date;
1018 --l_days_in_uom := (OKC_TIME_UTIL_PUB.get_enddate(p_start_date,p_price_uom,1)+1) - p_start_date;
1019 l_days_in_uom := l_uom_quantity;
1020 l_partial_period := l_days_in_partial_period/l_days_in_uom;
1021 ELSE
1022 l_full_months := floor(months_between(p_end_date+1,p_start_date));
1023 --08-SEP-2005 mchoudha
1024 --start bug#4571411: Fixed issue 2
1025 IF(l_full_months = months_between(p_end_date+1,p_start_date) ) THEN
1026 l_days_in_partial_mth := 0;
1027 l_partial_mth_start_date := add_months(p_start_date,l_full_months);
1028 ELSE
1029 l_partial_mth_start_date := add_months(p_start_date,l_full_months);
1030 l_days_in_partial_mth := (p_end_date+1)- l_partial_mth_start_date;
1031 END IF;
1032 --end bug#4571411
1033 IF upper(p_period_type) = 'FIXED'
1034 THEN
1035 l_days_in_mth :=30;
1036 ELSE
1037 --mchoudha fix for bug#5199908
1038 --last day logic will be used for both Service and Calendar kind of scenarios
1039 --IF UPPER(p_period_start) = 'CALENDAR'
1040 --THEN
1041 l_days_in_mth := to_char(last_day(l_partial_mth_start_date),'dd');
1042 --ELSE
1043 -- l_days_in_mth := add_months(l_partial_mth_start_date,1) - l_partial_mth_start_date;
1044 --END IF;
1045 END IF;
1046 l_duration_in_mths := l_full_months+ l_days_in_partial_mth/l_days_in_mth;
1047 l_partial_period := l_duration_in_mths/l_months_in_uom;
1048 END IF;
1049
1050 return(l_partial_period);
1051 EXCEPTION
1052 WHEN
1053 INVALID_PERIOD_TYPE_EXCEPTION
1054 THEN
1055 OKC_API.set_message(
1056 p_app_name => G_APP_NAME,
1057 p_msg_name => G_INVALID_VALUE,
1058 p_token1 => G_COL_NAME_TOKEN,
1059 p_token1_value => 'Period Type');
1060 return -1;
1061 WHEN
1062 INVALID_PERIOD_START_EXCEPTION
1063 THEN
1064 OKC_API.set_message(
1065 p_app_name => G_APP_NAME,
1066 p_msg_name => G_INVALID_VALUE,
1067 p_token1 => G_COL_NAME_TOKEN,
1068 p_token1_value => 'Period Start');
1069 return -1;
1070 WHEN
1071 INVALID_UOM_EXCEPTION
1072 THEN
1073 OKC_API.SET_MESSAGE(p_app_name => 'OKS',
1074 p_msg_name => 'OKS_INVD_UOM_CODE',
1075 p_token1 => 'OKS_API_NAME',
1076 p_token1_value => 'OKS_TIME_MEASURES_PUB.get_partial_period_duration',
1077 p_token2 => 'UOM_CODE',
1078 p_token2_value => p_price_uom);
1079 close cs_validate_uom;
1080 return -1;
1081 WHEN
1082 INVALID_DATE_EXCEPTION
1083 THEN
1084 OKC_API.set_message('OKC','OKC_REP_INV_EFF_DATE_SD');
1085 return -1;
1086 WHEN OTHERS THEN
1087 OKC_API.set_message(G_APP_NAME,
1088 G_UNEXPECTED_ERROR,
1089 G_SQLCODE_TOKEN,
1090 SQLCODE,
1091 G_SQLERRM_TOKEN,
1092 SQLERRM);
1093 return -1;
1094
1095
1096 END get_partial_period_duration;
1097
1098
1099 ------------------------------------------------------------------------
1100 /* This procedure calculates number of full periods for given period (startdate,end date),price uom
1101 and also the last day of last full period. */
1102
1103 PROCEDURE get_full_periods (p_start_date IN DATE,
1104 p_end_date IN DATE,
1105 p_price_uom IN VARCHAR2,
1106 x_full_periods OUT NOCOPY NUMBER,
1107 x_full_period_end_date OUT NOCOPY DATE,
1108 x_return_status OUT NOCOPY VARCHAR2)
1109 is
1110
1111 CURSOR cs_validate_uom(p_uom_code IN VARCHAR2)
1112 is
1113 SELECT 1
1114 FROM MTL_UNITS_OF_MEASURE_TL
1115 WHERE uom_code = p_uom_code
1116 --AND uom_class = 'Time' commented for bug#5585356
1117 AND LANGUAGE = USERENV('LANG');
1118
1119 cr_validate_uom cs_validate_uom%ROWTYPE;
1120
1121
1122 l_day VARCHAR2(40);
1123 l_week VARCHAR2(40);
1124 l_month VARCHAR2(40);
1125 l_quarter VARCHAR2(40);
1126 l_halfyear VARCHAR2(40);
1127 l_year VARCHAR2(40);
1128 l_full_periods NUMBER;
1129 l_full_period_end_date DATE;
1130 l_confactor_mth NUMBER;
1131 l_confactor_day NUMBER;
1132 l_confactor_yr NUMBER;
1133
1134 invalid_date_exception EXCEPTION;
1135 invalid_uom_exception EXCEPTION;
1136 invalid_mapping_exception EXCEPTION;
1137 BEGIN
1138 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1139 open cs_validate_uom(p_price_uom);
1140 fetch cs_validate_uom into cr_validate_uom;
1141 IF cs_validate_uom%NOTFOUND
1142 THEN
1143 RAISE INVALID_UOM_EXCEPTION;
1144 END IF;
1145 close cs_validate_uom;
1146 IF p_start_date > p_end_date
1147 THEN
1148 RAISE INVALID_DATE_EXCEPTION;
1149 END IF;
1150
1151 l_day := upper(get_uom_code('DAY',1));
1152 l_week := upper(get_uom_code('DAY',7));
1153 l_month := upper(get_uom_code('MONTH',1));
1154 l_quarter :=upper(get_uom_code('MONTH',3));
1155 --commented for bug#5122566
1156 --l_halfyear:=upper(get_uom_code('MONTH',6));
1157 l_year := upper(get_uom_code('YEAR',1));
1158
1159 --08-SEP-2005 mchoudha
1160 --bug#4571411 : Fixed issue 2
1161 --return p_end_date as l_full_period_end_date if full periods are there
1162 --between p_start_date and p_end_date
1163
1164 IF upper(p_price_uom) = l_year
1165 THEN
1166 l_full_periods := floor(months_between(p_end_date+1,p_start_date)/12);
1167 IF l_full_periods = months_between(p_end_date+1,p_start_date)/12 THEN
1168 l_full_period_end_date := p_end_date;
1169 ELSE
1170 l_full_period_end_date := add_months(p_start_date,l_full_periods*12)-1;
1171 END IF;
1172 --commented for bug#5122566
1173 /*ELSIF upper(p_price_uom) = l_halfyear
1174 THEN
1175 l_full_periods := floor(months_between(p_end_date+1,p_start_date)/6);
1176 IF l_full_periods =months_between(p_end_date+1,p_start_date)/6 THEN
1177 l_full_period_end_date := p_end_date;
1178 ELSE
1179 l_full_period_end_date := add_months(p_start_date,l_full_periods*6)-1;
1180 END IF;*/
1181 ELSIF upper(p_price_uom) = l_quarter
1182 THEN
1183
1184 l_full_periods := floor(months_between(p_end_date+1,p_start_date)/3);
1185 IF l_full_periods = months_between(p_end_date+1,p_start_date)/3 THEN
1186 l_full_period_end_date := p_end_date;
1187 ELSE
1188 l_full_period_end_date := add_months(p_start_date,l_full_periods*3)-1;
1189 END IF;
1190
1191
1192 ELSIF upper(p_price_uom) = l_month
1193 THEN
1194 l_full_periods := floor(months_between(p_end_date+1,p_start_date));
1195 IF l_full_periods = months_between(p_end_date+1,p_start_date) THEN
1196 l_full_period_end_date := p_end_date;
1197 ELSE
1198 l_full_period_end_date := add_months(p_start_date,l_full_periods)-1;
1199 END IF;
1200 /* Added for Bug13964300*/
1201 IF l_full_period_end_date > p_end_date THEN
1202 l_full_periods := l_full_periods -1;
1203 l_full_period_end_date := add_months(p_start_date,l_full_periods)-1;
1204 END IF;
1205 /* End for Bug#13964300*/
1206 ELSIF upper(p_price_uom) = l_week
1207 THEN
1208 l_full_periods := FLOOR((p_end_date+1 -p_start_date)/7);
1209 l_full_period_end_date := (p_start_date + l_full_periods*7)-1;
1210 ELSIF upper(p_price_uom) = l_day
1211 THEN
1212 l_full_periods := (p_end_date+1 -p_start_date);
1213 l_full_period_end_date := p_end_date;
1214 ELSE
1215 l_full_periods := GET_QTY_FOR_DAYS(p_end_date+1-p_start_date,p_price_uom);
1216 IF l_full_periods is NOT NULL --HR,MIN are covered in ths loop
1217 THEN
1218 l_full_period_end_date := p_end_date;
1219 ELSE
1220 l_confactor_yr := get_con_factor(p_price_uom,l_year); --Bugno:4654304.Added by Jvorugan
1221 IF l_confactor_yr is NOT NULL --user defined tce in terms of year
1222 THEN
1223 l_full_periods := floor(months_between(p_end_date+1,p_start_date)/(l_confactor_yr*12));
1224 IF l_full_periods = months_between(p_end_date+1,p_start_date)/(l_confactor_yr*12) THEN
1225 l_full_period_end_date := p_end_date;
1226 ELSE
1227 l_full_period_end_date := add_months(p_start_date,l_full_periods*(l_confactor_yr*12))-1;
1228 END IF; -- End of changes for Bugno:4654304
1229 ELSE
1230 l_confactor_mth := get_con_factor(p_price_uom,l_month);
1231 IF l_confactor_mth is NOT NULL --user defined tce in terms of month
1232 THEN
1233 l_full_periods := floor(months_between(p_end_date+1,p_start_date)/l_confactor_mth);
1234 IF l_full_periods = months_between(p_end_date+1,p_start_date)/l_confactor_mth THEN
1235 l_full_period_end_date := p_end_date;
1236 ELSE
1237 l_full_period_end_date := add_months(p_start_date,l_full_periods*l_confactor_mth)-1;
1238 END IF;
1239
1240 ELSE
1241 l_confactor_day := get_con_factor(p_price_uom,l_day);
1242 IF l_confactor_day is NOT NULL --user defined tce in terms of day
1243 THEN
1244 l_full_periods := FLOOR((p_end_date+1 -p_start_date)/l_confactor_day);
1245 l_full_period_end_date := (p_start_date + l_full_periods*l_confactor_day)-1;
1246 ELSE
1247 RAISE INVALID_MAPPING_EXCEPTION; --No mapping available in tce
1248 END IF; --user defined in terms of day
1249 END IF; --user define din terms of month
1250 END IF; --user defined in terms of year
1251 END IF; --HR,MIN
1252 END IF; --outer if
1253 x_full_periods := l_full_periods;
1254 IF x_full_periods =0
1255 THEN
1256 x_full_period_end_date := NULL;
1257 ELSE
1258 x_full_period_end_date := l_full_period_end_date;
1259 END IF;
1260 EXCEPTION
1261 WHEN
1262 INVALID_UOM_EXCEPTION
1263 THEN
1264 OKC_API.SET_MESSAGE(p_app_name => 'OKS',
1265 p_msg_name => 'OKS_INVD_UOM_CODE',
1266 p_token1 => 'OKS_API_NAME',
1267 p_token1_value => 'OKS_TIME_MEASURES_PUB.get_full_periods',
1268 p_token2 => 'UOM_CODE',
1269 p_token2_value => p_price_uom );
1270 close cs_validate_uom;
1271 x_return_status := OKC_API.G_RET_STS_ERROR;
1272 x_full_periods := NULL;
1273 x_full_period_end_date := NULL;
1274 WHEN
1275 INVALID_DATE_EXCEPTION
1276 THEN
1277 OKC_API.set_message('OKC','OKC_REP_INV_EFF_DATE_SD');
1278 x_return_status := OKC_API.G_RET_STS_ERROR;
1279 x_full_periods := NULL;
1280 x_full_period_end_date := NULL;
1281 WHEN
1282 INVALID_MAPPING_EXCEPTION
1283 THEN
1284 OKC_API.set_message(
1285 p_app_name => G_APP_NAME,
1286 p_msg_name => 'OKS_INVALID_TIME_MAPPING',
1287 p_token1 => 'TOKEN1',
1288 p_token1_value => p_price_uom);
1289 x_return_status := OKC_API.G_RET_STS_ERROR;
1290 x_full_periods := NULL;
1291 x_full_period_end_date := NULL;
1292 WHEN OTHERS THEN
1293 OKC_API.set_message(G_APP_NAME,
1294 G_UNEXPECTED_ERROR,
1295 G_SQLCODE_TOKEN,
1296 SQLCODE,
1297 G_SQLERRM_TOKEN,
1298 SQLERRM);
1299 x_return_status := OKC_API.G_RET_STS_ERROR;
1300 x_full_periods := NULL;
1301 x_full_period_end_date := NULL;
1302
1303
1304 END get_full_periods;
1305
1306 ------------------------------------------------------------------------
1307 /* This function takes source_uom and target_uom as input parameters and searches
1308 for a common tce_code for both in okc_time_code_units_b. If it finds common tce_code
1309 it calculates source_uom in terms of the target_uom and returns the value. Else
1310 it returns null
1311 */
1312
1313 FUNCTION get_con_factor(p_source_uom IN VARCHAR2,
1314 p_target_uom IN VARCHAR2)
1315 return NUMBER
1316 as
1317 CURSOR get_common_uom(p_source_uom IN VARCHAR2,p_target_uom IN VARCHAR2)
1318 is
1319 SELECT tce_code,quantity
1320 FROM okc_time_code_units_b
1321 WHERE uom_code = p_source_uom
1322 AND tce_code in (SELECT tce_code FROM okc_time_code_units_b WHERE uom_code=p_target_uom)
1323 ORDER BY quantity ASC;
1324
1325 CURSOR time_code_unit(p_source_uom IN varchar2, p_target_uom IN VARCHAR2)
1326 is
1327 SELECT quantity
1328 FROM okc_time_code_units_b
1329 WHERE tce_code = p_source_uom
1330 AND active_flag = 'Y'
1331 AND uom_code = p_target_uom;
1332
1333 l_quantity1 NUMBER :=0;
1334 l_quantity2 NUMBER :=0;
1335 l_target_qty NUMBER;
1336 l_tce_code varchar2(40);
1337
1338 BEGIN
1339
1340 open get_common_uom(p_source_uom,p_target_uom);
1341 fetch get_common_uom into l_tce_code,l_quantity1;
1342 close get_common_uom;
1343 IF (l_quantity1 >0)
1344 THEN
1345 open time_code_unit(l_tce_code,p_target_uom);
1346 fetch time_code_unit into l_quantity2;
1347 close time_code_unit;
1348 IF(l_quantity2 >0) THEN
1349 l_target_qty:= (l_quantity1)/(l_quantity2);
1350 IF (l_target_qty > 0) THEN
1351 return l_target_qty;
1352 ELSE
1353 return NULL;
1354 END IF;
1355 ELSE
1356 return NULL;
1357 END IF;
1358 ELSE
1359 return NULL;
1360 END IF;
1361
1362 EXCEPTION
1363 WHEN OTHERS THEN
1364 OKC_API.set_message(G_APP_NAME,
1365 G_UNEXPECTED_ERROR,
1366 G_SQLCODE_TOKEN,
1367 SQLCODE,
1368 G_SQLERRM_TOKEN,
1369 SQLERRM);
1370 return NULL;
1371
1372
1373 END get_con_factor;
1374
1375 ------------------------------------------------------------------------
1376
1377 FUNCTION get_months_between(p_start_date IN DATE,
1378 p_end_date IN DATE)
1379 return NUMBER
1380 as
1381
1382 l_no_of_months NUMBER;
1383
1384 l_days_start NUMBER := days_in_month(p_start_date);
1385 l_days_end NUMBER := days_in_month(p_end_date);
1386 l_dd_start NUMBER ;
1387 l_dd_end NUMBER ;
1388
1389 BEGIN
1390
1391 l_dd_start := to_number(to_char(p_start_date,'DD'));
1392 l_dd_end := to_number(to_char(p_end_date,'DD'));
1393
1394 if end_of_month(p_start_date) and end_of_month(p_end_date)
1395 then
1396 l_no_of_months := months_between(p_end_date,p_start_date);
1397 elsif end_of_month(p_start_date)
1398 then
1399 if to_number(to_char(p_start_date,'MM')) = 2
1400 and l_dd_end >= l_dd_start
1401 then
1402 l_no_of_months:= floor(months_between(p_end_date,p_start_date))-1+(l_dd_end/l_days_end);
1403 else
1404 l_no_of_months:= floor(months_between(p_end_date,p_start_date))+(l_dd_end/l_days_end);
1405 end if;
1406 elsif end_of_month(p_end_date)
1407 then
1408 if to_number(to_char(p_end_date,'MM')) = 2
1409 and l_dd_start > l_dd_end
1410 then
1411 l_no_of_months:= ((l_days_start- l_dd_start)/l_days_start)+floor(months_between(p_end_date,p_start_date))+1;
1412 else
1413 l_no_of_months:= ((l_days_start- l_dd_start)/l_days_start)+floor(months_between(p_end_date,p_start_date));
1414 end if;
1415 elsif (l_dd_start = l_dd_end)
1416 then
1417 l_no_of_months := months_between(p_end_date,p_start_date);
1418 else
1419 l_no_of_months:= ((l_days_start- l_dd_start)/l_days_start)+floor(months_between(p_end_date,p_start_date))+(l_dd_end/l_days_end);
1420 end if;
1421
1422 return l_no_of_months;
1423
1424 END;
1425
1426 -----------------------------------------------------------------------------------
1427 END OKS_TIME_MEASURES_PUB;
1428