[Home] [Help]
PACKAGE BODY: APPS.OKS_SUBSCRIPTION_SCH_PVT
Source
1 Package Body OKS_SUBSCRIPTION_SCH_PVT AS
2 /* $Header: OKSSBSHB.pls 120.0 2005/05/25 18:34:34 appldev noship $ */
3
4
5 Procedure Fill_var_num(p_def in varchar2,
6 p_type in varchar2,
7 x_var_num_tbl out NOCOPY var_tbl,
8 x_return_status OUT NOCOPY Varchar2) ;
9
10 Procedure Create_Yearly_tbl(
11 p_start_dt IN date
12 , p_end_dt IN date
13 , p_offset_dy IN NUMBER
14 , p_freq IN Varchar2
15 , p_yr_pattern_tbl IN var_tbl
16 , x_rec_tbl IN OUT NOCOPY del_tbl
17 , x_return_status OUT NOCOPY Varchar2
18 );
19
20
21 Procedure Create_Monthly_tbl(
22 p_start_dt IN date
23 , p_end_dt IN date
24 , p_offset_dy IN NUMBER
25 , p_freq IN Varchar2
26 , p_mth_pattern_tbl IN var_tbl
27 , p_yr_pattern_tbl IN var_tbl
28 , x_rec_tbl IN OUT NOCOPY del_tbl
29 , x_return_status OUT NOCOPY Varchar2
30 );
31
32 Procedure Create_Weekly_tbl(
33 p_start_dt IN date
34 , p_end_dt IN date
35 , p_offset_dy IN NUMBER
36 , p_freq IN Varchar2
37 , p_mth_pattern_tbl IN var_tbl
38 , p_yr_pattern_tbl IN var_tbl
39 , p_wk_pattern_tbl IN var_tbl
40 , x_rec_tbl IN OUT NOCOPY del_tbl
41 , x_return_status OUT NOCOPY Varchar2
42 );
43
44
45
46 Procedure Create_wday_tbl(
47 p_start_dt IN date
48 , p_end_dt IN date
49 , p_offset_dy IN NUMBER
50 , p_freq IN Varchar2
51 , p_mth_pattern_tbl IN var_tbl
52 , p_yr_pattern_tbl IN var_tbl
53 , p_wk_pattern_tbl IN var_tbl
54 , p_wd_pattern_tbl IN var_tbl
55 , x_rec_tbl IN OUT NOCOPY del_tbl
56 , x_return_status OUT NOCOPY Varchar2
57 );
58
59
60 Procedure Create_day_tbl(
61 p_start_dt IN date
62 , p_end_dt IN date
63 , p_offset_dy IN NUMBER
64 , p_freq IN Varchar2
65 , p_mth_pattern_tbl IN var_tbl
66 , p_yr_pattern_tbl IN var_tbl
67 , p_day_pattern_tbl IN var_tbl
68 , x_rec_tbl IN OUT NOCOPY del_tbl
69 , x_return_status OUT NOCOPY Varchar2
70 );
71
72
73
74
75
76
77
78
79 Procedure Calc_Delivery_date
80 (
81 p_start_dt IN date
82 , p_end_dt IN date
83 , p_offset_dy IN NUMBER
84 , p_freq IN Varchar2
85 , p_pattern_tbl IN pattern_tbl
86 , x_delivery_tbl OUT NOCOPY del_tbl
87 , x_return_status OUT NOCOPY Varchar2
88 ) IS
89
90 l_type varchar2(20);
91 l_def varchar2(100);
92 l_info_tbl var_tbl;
93 l_rec_tbl del_tbl;
94 l_yr_tbl var_tbl;
95 l_mth_tbl var_tbl;
96 l_wk_tbl var_tbl;
97 l_wdy_tbl var_tbl;
98 l_dy_tbl var_tbl;
99 l_low_yr varchar2(4);
100 l_high_yr varchar2(4);
101 l_ind number;
102
103 BEGIN
104
105 x_return_status := OKC_API.G_RET_STS_SUCCESS;
106
107 If p_start_dt > p_end_dt THEN
108 RETURN;
109 END IF;
110
111
112 if p_pattern_tbl.count <= 0 THEN
113 RETURN;
114 END IF;
115
116 -----errorout_ad('p_pattern_tbl.count = ' || p_pattern_tbl.count);
117
118 FOR I IN 1..p_pattern_tbl.count
119 LOOP
120
121 IF i = 1 THEN
122 l_ind := p_pattern_tbl.FIRST;
123 ELSE
124 l_ind := p_pattern_tbl.next(l_ind);
125
126 END IF;
127
128 IF p_pattern_tbl(l_ind).yr_pattern = '*' THEN
129
130 SELECT TO_CHAR(p_start_dt,'YYYY') INTO l_low_yr FROM DUAL;
131 SELECT TO_CHAR(p_end_dt,'YYYY') INTO l_high_yr FROM DUAL;
132
133 if l_low_yr = l_high_yr THEN
134 l_def := l_low_yr;
135 ELSE
136
137 l_def := l_low_yr || '-' || l_high_yr ;
138 END IF;
139
140 ELSE
141 l_def := p_pattern_tbl(l_ind).yr_pattern;
142 END IF;
143 -----errorout_ad('l_def passed for year tbl = ' || l_def);
144
145 Fill_var_num(
146 p_def => l_def,
147 p_type => 'YR',
148 x_var_num_tbl => l_yr_tbl,
149 x_return_status => x_return_status);
150
151 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
152 OKC_API.set_message(G_PKG_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'YEAR PATTERN NOT BUILD.');
153 RAISE G_EXCEPTION_HALT_VALIDATION;
154 END IF;
155 -----errorout_ad('l_yr_tbl count = ' || l_yr_tbl.count);
156
157 IF p_freq = 'Y' THEN
158
159 Create_Yearly_tbl(
160 p_start_dt => p_start_dt,
161 p_end_dt => p_end_dt,
162 p_offset_dy => nvl(p_offset_dy,0),
163 p_freq => p_freq,
164 p_yr_pattern_tbl => l_yr_tbl,
165 x_rec_tbl => l_rec_tbl,
166 x_return_status => x_return_status);
167
168 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
169 OKC_API.set_message(G_PKG_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'YEARLY SCHEDULE NOT BUILD.');
170 RAISE G_EXCEPTION_HALT_VALIDATION;
171 END IF;
172
173 ELSE ---p_freq <> 'Y'
174
175 Fill_var_num(
176 p_def => p_pattern_tbl(l_ind).mth_pattern,
177 p_type => 'MTH',
178 x_var_num_tbl => l_mth_tbl,
179 x_return_status => x_return_status);
180
181
182 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
183 OKC_API.set_message(G_PKG_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'MONTH PATTERN NOT BUILD.');
184 RAISE G_EXCEPTION_HALT_VALIDATION;
185 END IF;
186 -----errorout_ad('l_mth_tbl count = ' || l_mth_tbl.count);
187
188 IF p_freq = 'M' THEN
189
190 Create_Monthly_tbl(
191 p_start_dt => p_start_dt,
192 p_end_dt => p_end_dt,
193 p_offset_dy => nvl(p_offset_dy,0),
194 p_freq => p_freq,
195 p_mth_pattern_tbl => l_mth_tbl,
196 p_yr_pattern_tbl => l_yr_tbl,
197 x_rec_tbl => l_rec_tbl,
198 x_return_status => x_return_status);
199
200 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
201 OKC_API.set_message(G_PKG_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'MONTHLY SCHEDULE NOT BUILD.');
202 RAISE G_EXCEPTION_HALT_VALIDATION;
203 END IF;
204
205 ELSIF p_freq = 'W' THEN
206
207 Fill_var_num(
208 p_def => p_pattern_tbl(l_ind).week_pattern,
209 p_type => 'WK',
210 x_var_num_tbl => l_wk_tbl,
211 x_return_status => x_return_status);
212
213 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
214 OKC_API.set_message(G_PKG_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'WEEKLY PATTERN NOT BUILD.');
215 RAISE G_EXCEPTION_HALT_VALIDATION;
216 END IF;
217
218 Create_Weekly_tbl(
219 p_start_dt => p_start_dt,
220 p_end_dt => p_end_dt,
221 p_offset_dy => nvl(p_offset_dy,0),
222 p_freq => p_freq,
223 p_mth_pattern_tbl => l_mth_tbl,
224 p_yr_pattern_tbl => l_yr_tbl,
225 p_wk_pattern_tbl => l_wk_tbl,
226 x_rec_tbl => l_rec_tbl,
227 x_return_status => x_return_status);
228
229 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
230 OKC_API.set_message(G_PKG_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'WEEKLY SCHEDULE NOT BUILD.');
231 RAISE G_EXCEPTION_HALT_VALIDATION;
232 END IF;
233
234 ELSIF p_freq = 'D' THEN
235
236 IF p_pattern_tbl(l_ind).day_pattern IS NOT NULL THEN ---then wk and wdy will be null.
237 Fill_var_num(
238 p_def => p_pattern_tbl(l_ind).day_pattern,
239 p_type => 'DY',
240 x_var_num_tbl => l_dy_tbl,
241 x_return_status => x_return_status);
242
243 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
244 OKC_API.set_message(G_PKG_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'DAILY PATTERN NOT BUILD.');
245 RAISE G_EXCEPTION_HALT_VALIDATION;
246 END IF;
247
248 Create_day_tbl(
249 p_start_dt => p_start_dt,
250 p_end_dt => p_end_dt,
251 p_offset_dy => nvl(p_offset_dy,0),
252 p_freq => p_freq,
253 p_mth_pattern_tbl => l_mth_tbl,
254 p_yr_pattern_tbl => l_yr_tbl,
255 p_day_pattern_tbl => l_dy_tbl,
256 x_rec_tbl => l_rec_tbl,
257 x_return_status => x_return_status);
258
259 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
260 OKC_API.set_message(G_PKG_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'DAILY SCHEDULE NOT BUILD.');
261 RAISE G_EXCEPTION_HALT_VALIDATION;
262 END IF;
263
264 ELSE ---day pattern is null
265 Fill_var_num(
266 p_def => p_pattern_tbl(l_ind).week_pattern,
267 p_type => 'WK',
268 x_var_num_tbl => l_wk_tbl,
269 x_return_status => x_return_status);
270
271 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
272 OKC_API.set_message(G_PKG_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'WEEKLY PATTERN NOT BUILD.');
273 RAISE G_EXCEPTION_HALT_VALIDATION;
274 END IF;
275
276
277 Fill_var_num(
278 p_def => p_pattern_tbl(l_ind).wday_pattern,
279 p_type => 'WDY',
280 x_var_num_tbl => l_wdy_tbl,
281 x_return_status => x_return_status);
282
283 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
284 OKC_API.set_message(G_PKG_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'WEEK DAY PATTERN NOT BUILD.');
285 RAISE G_EXCEPTION_HALT_VALIDATION;
286 END IF;
287
288
289 Create_wday_tbl(
290 p_start_dt => p_start_dt,
291 p_end_dt => p_end_dt,
292 p_offset_dy => nvl(p_offset_dy,0),
293 p_freq => p_freq,
294 p_mth_pattern_tbl => l_mth_tbl,
295 p_yr_pattern_tbl => l_yr_tbl,
296 p_wk_pattern_tbl => l_wk_tbl,
297 p_wd_pattern_tbl => l_wdy_tbl,
298 x_rec_tbl => l_rec_tbl,
299 x_return_status => x_return_status);
300
301 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
302 OKC_API.set_message(G_PKG_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'WEEK DAY SCHEDULE NOT BUILD.');
303 RAISE G_EXCEPTION_HALT_VALIDATION;
304 END IF;
305
306 END IF; --end of day_pattern IS NOT NULL
307
308 END IF; ----end of p_freq
309 END IF; ---end of p_freq <> 'Y'
310 END LOOP;
311
312
313
314 -----errorout_ad('Create TBL status = ' || x_return_status);
315 -----errorout_ad('l_rec_tbl count outside loop = '|| l_rec_tbl.count);
316
317 if l_rec_tbl.count > 0 then
318
319
320 FOR i IN 1..l_rec_tbl.count LOOP
321
322 IF i = 1 THEN
323 l_ind := l_rec_tbl.FIRST;
324 ELSE
325 l_ind := l_rec_tbl.next(l_ind);
326 END IF;
327
328 x_delivery_tbl(i).delivery_date := l_rec_tbl(l_ind).delivery_date;
329 x_delivery_tbl(i).start_date := l_rec_tbl(l_ind).start_date;
330 x_delivery_tbl(i).end_date := l_rec_tbl(l_ind).end_date;
331
332 -----errorout_ad('delivery_date = ' || l_rec_tbl(l_ind).delivery_date);
333
334 /*just for test
335 INSERT INTO Oks_test_tbl
336 (delivery_date,start_date,end_date)
337 values (l_rec_tbl(l_ind).delivery_date,l_rec_tbl(l_ind).start_date,l_rec_tbl(l_ind).end_date);*/
338
339 END LOOP;
340 end if;
341
342
343 EXCEPTION
344 WHEN G_EXCEPTION_HALT_VALIDATION THEN
345 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
346 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
347 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
348 RAISE OKC_API.G_EXCEPTION_ERROR;
349 END IF;
350 WHEN OTHERS THEN
351 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME_OKC,
352 p_msg_name => G_UNEXPECTED_ERROR,
353 p_token1 => G_SQLCODE_TOKEN,
354 p_token1_value => sqlcode,
355 p_token2 => G_SQLERRM_TOKEN,
356 p_token2_value => sqlerrm);
357
358 x_return_status := G_RET_STS_UNEXP_ERROR;
359
360 END Calc_Delivery_date;
361
362
363
364 Procedure Fill_var_num(p_def in varchar2,
365 p_type in varchar2,
366 x_var_num_tbl out NOCOPY var_tbl,
367 x_return_status OUT NOCOPY Varchar2)
368 IS
369
370 l_value varchar2(20);
371 l_rem_def varchar2(100);
372 l_len number;
373 l_pos number;
374 l_pos_hypen number;
375 l_hypen_str varchar2(50);
376 i number;
377 l_low_val number;
378 l_high_val number;
379
380 BEGIN
381
382 x_return_status := OKC_API.G_RET_STS_SUCCESS;
383
384 i := 1;
385
386 IF p_def IS NULL THEN
387 RETURN;
388 END IF;
389
390 IF p_def = '*' THEN
391 i := 1;
392 if p_type = 'MTH' THEN
393 l_low_val := 1;
394 l_high_val := 12;
395 ELSif p_type = 'WK' THEN
396 l_low_val := 1;
397 l_high_val := 5;
398 ELSif p_type = 'WDY' THEN
399 l_low_val := 1;
400 l_high_val := 7;
401
402 ELSif p_type = 'DY' THEN
403 l_low_val := 1;
404 l_high_val := 31;
405 END IF;
406
407 for val in l_low_val..l_high_val loop
408
409 IF val < 10 THEN
410 x_var_num_tbl(i).num_item := '0' || TO_CHAR(val);
411 ELSE
412 x_var_num_tbl(i).num_item := TO_CHAR(val);
413 END IF;
414 i := i + 1;
415 end loop;
416 -----errorout_ad('x_var_num_tbl.COUNT = ' || x_var_num_tbl.COUNT);
417 return;
418 end if;
419
420 l_rem_def := p_def;
421
422 l_len := length(l_rem_def) ;
423
424 if l_len = 0 then
425 return;
426 end if;
427
428 loop
429
430 -----errorout_ad('l_rem_def = ' || l_rem_def);
431
432 l_pos := instr(l_rem_def,',', 1,1);
433
434 -----errorout_ad('l_pos = ' || l_pos);
435 if l_pos = 0 then
436
437 l_pos_hypen := instr(l_rem_def,'-', 1,1);
438
439 if l_pos_hypen = 0 then
440 -----errorout_ad('coming in ');
441 IF TO_NUMBER(l_rem_def) < 10 and LENGTH(l_rem_def) = 1 THEN
442 x_var_num_tbl(i).num_item := '0' || l_rem_def;
443 ELSE
444 x_var_num_tbl(i).num_item := l_rem_def;
445 END IF;
446 i := i + 1;
447 else
448
449 l_low_val := to_number(substr(l_rem_def,1, (l_pos_hypen - 1)));
450
451 l_high_val := to_number(substr(l_rem_def, (l_pos_hypen +1),(l_len-l_pos_hypen)));
452
453 for val in l_low_val..l_high_val loop
454 IF val < 10 AND LENGTH(TO_CHAR(val)) = 1 THEN
455 x_var_num_tbl(i).num_item := '0' || TO_CHAR(val);
456 ELSE
457 x_var_num_tbl(i).num_item := TO_CHAR(val);
458 END IF;
459
460 i := i + 1;
461 end loop;
462 end if; ----end of l_pos_hypen=0
463 l_len := 0;
464
465 else ----l_pos > 0
466
467
468 l_value := substr(l_rem_def,1, (l_pos - 1));
469
470 -----errorout_ad('l_value = ' || l_value);
471
472 l_pos_hypen := instr(l_value,'-', 1,1);
473
474 if l_pos_hypen = 0 then
475
476 IF to_number(l_value) < 10 AND LENGTH(l_value) = 1 THEN
477 x_var_num_tbl(i).num_item := '0' || l_value;
478 ELSE
479 x_var_num_tbl(i).num_item := l_value;
480 END IF;
481
482 i := i + 1;
483 else
484
485 l_low_val := to_number(substr(l_value,1, (l_pos_hypen - 1)));
486
487 l_high_val := to_number(substr(l_value, (l_pos_hypen +1),(length(l_value)-l_pos_hypen)));
488
489 for val in l_low_val..l_high_val loop
490 IF val < 10 AND LENGTH(TO_CHAR(val)) = 1 THEN
491 x_var_num_tbl(i).num_item := '0' || TO_CHAR(val);
492 ELSE
493 x_var_num_tbl(i).num_item := TO_CHAR(val);
494 END IF;
495 i := i + 1;
496 end loop;
497
498
499 end if; ----end of l_pos_hypen=0
500 l_value := substr(l_rem_def, (l_pos + 1), (l_len-l_pos)); --remaing string
501
502 l_rem_def := l_value;
503 l_len := length(l_rem_def) ;
504
505
506
507 END IF; ----end of l_pos = 0
508 EXIT WHEN l_pos <= 0;
509
510 end loop;
511
512 EXCEPTION
513 WHEN G_EXCEPTION_HALT_VALIDATION THEN
514 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
515 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
516 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
517 RAISE OKC_API.G_EXCEPTION_ERROR;
518 END IF;
519 WHEN OTHERS THEN
520 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME_OKC,
521 p_msg_name => G_UNEXPECTED_ERROR,
522 p_token1 => G_SQLCODE_TOKEN,
523 p_token1_value => sqlcode,
524 p_token2 => G_SQLERRM_TOKEN,
525 p_token2_value => sqlerrm);
526
527 x_return_status := G_RET_STS_UNEXP_ERROR;
528
529 end Fill_var_num;
530
531 Procedure Create_Yearly_tbl(
532 p_start_dt IN date
533 , p_end_dt IN date
534 , p_offset_dy IN NUMBER
535 , p_freq IN Varchar2
536 , p_yr_pattern_tbl IN var_tbl
537 , x_rec_tbl IN OUT NOCOPY del_tbl
538 , x_return_status OUT NOCOPY Varchar2
539 )
540
541 IS
542
543 l_dt_str VARCHAR2(10);
544 l_act_dt DATE;
545 l_index NUMBER;
546
547
548 BEGIN
549
550 x_return_status := OKC_API.G_RET_STS_SUCCESS;
551
552 IF (p_yr_pattern_tbl.COUNT = 0) THEN
553 RETURN;
554 END IF;
555
556 FOR l_yr IN p_yr_pattern_tbl.FIRST..p_yr_pattern_tbl.LAST
557 LOOP
558
559 l_dt_str := p_yr_pattern_tbl(l_yr).num_item || '0101' ;
560 -----errorout_ad('l_dt_str = ' || l_dt_str);
561
562 l_index := to_number(l_dt_str) + p_offset_dy;
563
564
565 l_act_dt := TO_DATE(l_dt_str, 'YYYYMMDD');
566 -----errorout_ad('l_act_dt = ' || l_act_dt);
567
568
569 IF (l_act_dt < p_start_dt) OR (l_act_dt > p_end_dt) THEN
570 NULL;
571
572 ELSE
573
574 IF NOT x_rec_tbl.EXISTS(l_index) then
575 -----errorout_ad('index doe not exist = ' || l_index);
576 IF (l_act_dt + p_offset_dy) < p_start_dt THEN
577 x_rec_tbl(l_index).delivery_date := p_start_dt;
578 ELSIF (l_act_dt + p_offset_dy) > p_end_dt THEN
579 x_rec_tbl(l_index).delivery_date := p_end_dt;
580 ELSE
581 x_rec_tbl(l_index).delivery_date := l_act_dt + p_offset_dy;
582 END IF;
583 x_rec_tbl(l_index).start_date := l_act_dt;
584 x_rec_tbl(l_index).end_date := TO_DATE(p_yr_pattern_tbl(l_yr).num_item || '1231', 'YYYYMMDD');
585
586 END IF; ---end of already exists.
587 END IF;
588
589 END LOOP; ---YR LOOP
590
591 EXCEPTION
592 WHEN G_EXCEPTION_HALT_VALIDATION THEN
593 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
594 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
595 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
596 RAISE OKC_API.G_EXCEPTION_ERROR;
597 END IF;
598 WHEN OTHERS THEN
599 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME_OKC,
600 p_msg_name => G_UNEXPECTED_ERROR,
601 p_token1 => G_SQLCODE_TOKEN,
602 p_token1_value => sqlcode,
603 p_token2 => G_SQLERRM_TOKEN,
604 p_token2_value => sqlerrm);
605
606 x_return_status := G_RET_STS_UNEXP_ERROR;
607
608
609
610 END Create_Yearly_tbl;
611
612
613
614 Procedure Create_Monthly_tbl(
615 p_start_dt IN date
616 , p_end_dt IN date
617 , p_offset_dy IN NUMBER
618 , p_freq IN Varchar2
619 , p_mth_pattern_tbl IN var_tbl
620 , p_yr_pattern_tbl IN var_tbl
621 , x_rec_tbl IN OUT NOCOPY del_tbl
622 , x_return_status OUT NOCOPY Varchar2
623 )
624
625 IS
626
627 l_dt_str VARCHAR2(10);
628 l_act_dt DATE;
629 l_index NUMBER;
630 l_mth_last_dt date;
631
632
633 BEGIN
634
635 x_return_status := OKC_API.G_RET_STS_SUCCESS;
636
637 IF (p_yr_pattern_tbl.COUNT = 0) OR (p_mth_pattern_tbl.COUNT = 0 ) THEN
638 RETURN;
639 END IF;
640
641 FOR l_yr IN p_yr_pattern_tbl.FIRST..p_yr_pattern_tbl.LAST
642 LOOP
643
644 FOR l_mth IN p_mth_pattern_tbl.FIRST..p_mth_pattern_tbl.LAST
645 LOOP
646 l_dt_str := p_yr_pattern_tbl(l_yr).num_item || p_mth_pattern_tbl(l_mth).num_item || '01' ;
647 -----errorout_ad('l_dt_str = ' || l_dt_str);
648
649 l_index := to_number(l_dt_str) + p_offset_dy;
650
651
652 l_act_dt := TO_DATE(l_dt_str, 'YYYYMMDD');
653 -----errorout_ad('l_act_dt = ' || l_act_dt);
654
655
656 IF (l_act_dt < p_start_dt) OR (l_act_dt > p_end_dt) THEN
657 NULL;
658
659 ELSE
660
661 IF NOT x_rec_tbl.EXISTS(l_index) then
662 -----errorout_ad('index doe not exist = ' || l_index);
663 IF (l_act_dt + p_offset_dy) < p_start_dt THEN
664 x_rec_tbl(l_index).delivery_date := p_start_dt;
665 ELSIF (l_act_dt + p_offset_dy) > p_end_dt THEN
666 x_rec_tbl(l_index).delivery_date := p_end_dt;
667 ELSE
668 x_rec_tbl(l_index).delivery_date := l_act_dt + p_offset_dy;
669 END IF;
670
671 SELECT LAST_DAY(l_act_dt) INTO l_mth_last_dt FROM dual;
672 x_rec_tbl(l_index).start_date := l_act_dt;
673 x_rec_tbl(l_index).end_date := l_mth_last_dt;
674
675 END IF; ---end of already exists.
676 END IF;
677
678 END LOOP; ---MTH LOOP
679 END LOOP; ---YR LOOP
680
681 EXCEPTION
682 WHEN G_EXCEPTION_HALT_VALIDATION THEN
683 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
684 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
685 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
686 RAISE OKC_API.G_EXCEPTION_ERROR;
687 END IF;
688 WHEN OTHERS THEN
689 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME_OKC,
690 p_msg_name => G_UNEXPECTED_ERROR,
691 p_token1 => G_SQLCODE_TOKEN,
692 p_token1_value => sqlcode,
693 p_token2 => G_SQLERRM_TOKEN,
694 p_token2_value => sqlerrm);
695
696 x_return_status := G_RET_STS_UNEXP_ERROR;
697
698
699
700 END Create_Monthly_tbl;
701
702
703 Procedure Create_Weekly_tbl(
704 p_start_dt IN date
705 , p_end_dt IN date
706 , p_offset_dy IN NUMBER
707 , p_freq IN Varchar2
708 , p_mth_pattern_tbl IN var_tbl
709 , p_yr_pattern_tbl IN var_tbl
710 , p_wk_pattern_tbl IN var_tbl
711 , x_rec_tbl IN OUT NOCOPY del_tbl
712 , x_return_status OUT NOCOPY Varchar2
713 )
714 IS
715
716 l_mth_yr VARCHAR2(6);
717 l_act_dt DATE;
718 l_index NUMBER;
719
720
721 BEGIN
722
723 x_return_status := OKC_API.G_RET_STS_SUCCESS;
724
725 IF (p_yr_pattern_tbl.COUNT = 0) OR (p_mth_pattern_tbl.COUNT = 0 ) OR (p_wk_pattern_tbl.COUNT = 0) THEN
726 RETURN;
727 END IF;
728
729 FOR l_yr IN p_yr_pattern_tbl.FIRST..p_yr_pattern_tbl.LAST LOOP
730
731 FOR l_mth IN p_mth_pattern_tbl.FIRST..p_mth_pattern_tbl.LAST LOOP
732
733 FOR l_wk IN p_wk_pattern_tbl.FIRST..p_wk_pattern_tbl.LAST LOOP
734
735 l_mth_yr := p_mth_pattern_tbl(l_mth).num_item || p_yr_pattern_tbl(l_yr).num_item ;
736
737 l_act_dt := GET_WD_DATE(mmyyyy => l_mth_yr,
738 week => TO_NUMBER(p_wk_pattern_tbl(l_wk).num_item),
739 dow => 1) ;
740
741 ---dow harcoded for sunday
742
743 IF l_act_dt IS NOT NULL THEN
744
745 l_index := to_number(TO_CHAR(l_act_dt,'YYYYMMDD')) + p_offset_dy;
746
747
748 IF (l_act_dt < p_start_dt) OR (l_act_dt > p_end_dt) THEN
749 NULL;
750
751 ELSE
752
753 IF NOT x_rec_tbl.EXISTS(l_index) then
754
755 IF (l_act_dt + p_offset_dy) < p_start_dt THEN
756 x_rec_tbl(l_index).delivery_date:= p_start_dt;
757 ELSIF (l_act_dt + p_offset_dy) > p_end_dt THEN
758 x_rec_tbl(l_index).delivery_date := p_end_dt;
759 ELSE
760 x_rec_tbl(l_index).delivery_date := l_act_dt + p_offset_dy;
761 END IF;
762
763 x_rec_tbl(l_index).start_date := l_act_dt; --start of wk sunday
764 x_rec_tbl(l_index).end_date := l_act_dt + 6; ---end of wk sat.
765 end if; --end of already exists.
766
767 END IF; ---end of l_act_dt < p_start_dt) OR (l_act_dt > p_end_dt)
768
769 END IF; ---l_act_dt null
770
771 END LOOP; --WK LOOP
772 END LOOP; ---MTH LOOP
773 END LOOP; ---YR LOOP
774
775 EXCEPTION
776 WHEN G_EXCEPTION_HALT_VALIDATION THEN
777 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
778 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
779 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
780 RAISE OKC_API.G_EXCEPTION_ERROR;
781 END IF;
782 WHEN OTHERS THEN
783 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME_OKC,
784 p_msg_name => G_UNEXPECTED_ERROR,
785 p_token1 => G_SQLCODE_TOKEN,
786 p_token1_value => sqlcode,
787 p_token2 => G_SQLERRM_TOKEN,
788 p_token2_value => sqlerrm);
789
790 x_return_status := G_RET_STS_UNEXP_ERROR;
791
792
793
794 END Create_Weekly_tbl;
795
796 Procedure Create_day_tbl(
797 p_start_dt IN date
798 , p_end_dt IN date
799 , p_offset_dy IN NUMBER
800 , p_freq IN Varchar2
801 , p_mth_pattern_tbl IN var_tbl
802 , p_yr_pattern_tbl IN var_tbl
803 , p_day_pattern_tbl IN var_tbl
804 , x_rec_tbl IN OUT NOCOPY del_tbl
805 , x_return_status OUT NOCOPY Varchar2
806 )
807 IS
808 l_dt_str VARCHAR2(10);
809 l_act_dt DATE;
810 l_index NUMBER;
811 l_first_dt VARCHAR2(8);
812 l_last_dt DATE;
813 l_max VARCHAR2(10);
814
815
816 BEGIN
817
818 x_return_status := OKC_API.G_RET_STS_SUCCESS;
819
820 IF (p_yr_pattern_tbl.COUNT = 0) OR (p_mth_pattern_tbl.COUNT = 0 ) OR (p_day_pattern_tbl.COUNT = 0) THEN
821 RETURN;
822 END IF;
823
824 FOR l_yr IN p_yr_pattern_tbl.FIRST..p_yr_pattern_tbl.LAST LOOP
825
826 FOR l_mth IN p_mth_pattern_tbl.FIRST..p_mth_pattern_tbl.LAST LOOP
827
828 l_first_dt := p_yr_pattern_tbl(l_yr).num_item || p_mth_pattern_tbl(l_mth).num_item || '01';
829
830 SELECT LAST_DAY(TO_DATE(l_first_dt,'YYYYMMDD')) INTO l_last_dt FROM dual; --last day of mth
831
832 l_max := TO_CHAR(l_last_dt,'YYYYMMDD');
833
834 FOR l_dy IN p_day_pattern_tbl.FIRST..p_day_pattern_tbl.LAST LOOP
835
836 l_dt_str := p_yr_pattern_tbl(l_yr).num_item || p_mth_pattern_tbl(l_mth).num_item
837 || p_day_pattern_tbl(l_dy).num_item ;
838
839 l_index := to_number(l_dt_str) + p_offset_dy;
840
841 IF TO_NUMBER(l_max) < TO_NUMBER(l_dt_str) THEN --dt_str is greater then last date of mth
842 NULL;
843 ELSE
844 l_act_dt := TO_DATE(l_dt_str, 'YYYYMMDD');
845
846
847 IF (l_act_dt < p_start_dt) OR (l_act_dt > p_end_dt) THEN
848 NULL;
849
850 ELSE
851
852 IF NOT x_rec_tbl.EXISTS(l_index) then
853
854
855 IF (l_act_dt + p_offset_dy) < p_start_dt THEN
856 x_rec_tbl(l_index).delivery_date := p_start_dt;
857 ELSIF (l_act_dt + p_offset_dy) > p_end_dt THEN
858 x_rec_tbl(l_index).delivery_date := p_end_dt;
859 ELSE
860 x_rec_tbl(l_index).delivery_date := l_act_dt + p_offset_dy;
861 END IF;
862 ---start and end date will be date without offset
863 x_rec_tbl(l_index).start_date := l_act_dt;
864 x_rec_tbl(l_index).end_date := l_act_dt;
865 END IF; ---end of chk if l_index item already exists.
866 END IF;
867
868 END IF; ---end of dt should not pass beyond last dy of mth
869 END LOOP; ----day loop
870 END LOOP; ---MTH LOOP
871 END LOOP; ---YR LOOP
872
873 EXCEPTION
874 WHEN G_EXCEPTION_HALT_VALIDATION THEN
875 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
876 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
877 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
878 RAISE OKC_API.G_EXCEPTION_ERROR;
879 END IF;
880 WHEN OTHERS THEN
881 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME_OKC,
882 p_msg_name => G_UNEXPECTED_ERROR,
883 p_token1 => G_SQLCODE_TOKEN,
884 p_token1_value => sqlcode,
885 p_token2 => G_SQLERRM_TOKEN,
886 p_token2_value => sqlerrm);
887
888 x_return_status := G_RET_STS_UNEXP_ERROR;
889
890
891
892 END Create_Day_tbl;
893
894 Procedure Create_wday_tbl(
895 p_start_dt IN date
896 , p_end_dt IN date
897 , p_offset_dy IN NUMBER
898 , p_freq IN Varchar2
899 , p_mth_pattern_tbl IN var_tbl
900 , p_yr_pattern_tbl IN var_tbl
901 , p_wk_pattern_tbl IN var_tbl
902 , p_wd_pattern_tbl IN var_tbl
903 , x_rec_tbl IN OUT NOCOPY del_tbl
904 , x_return_status OUT NOCOPY Varchar2
905 )
906 IS
907
908 l_mth_yr VARCHAR2(6);
909 l_act_dt DATE;
910 l_index NUMBER;
911
912
913 BEGIN
914
915 x_return_status := OKC_API.G_RET_STS_SUCCESS;
916
917 IF (p_yr_pattern_tbl.COUNT = 0) OR (p_mth_pattern_tbl.COUNT = 0 ) OR (p_wk_pattern_tbl.COUNT = 0) or
918 (p_wd_pattern_tbl.count = 0)THEN
919
920 RETURN;
921 END IF;
922
923 FOR l_yr IN p_yr_pattern_tbl.FIRST..p_yr_pattern_tbl.LAST LOOP
924
925 FOR l_mth IN p_mth_pattern_tbl.FIRST..p_mth_pattern_tbl.LAST LOOP
926
927 FOR l_wk IN p_wk_pattern_tbl.FIRST..p_wk_pattern_tbl.LAST LOOP
928
929 FOR l_wdy IN p_wd_pattern_tbl.FIRST..p_wd_pattern_tbl.LAST LOOP
930
931
932
933 l_mth_yr := p_mth_pattern_tbl(l_mth).num_item || p_yr_pattern_tbl(l_yr).num_item ;
934
935 l_act_dt := GET_WD_DATE(mmyyyy => l_mth_yr,
936 week => TO_NUMBER(p_wk_pattern_tbl(l_wk).num_item),
937 dow => TO_NUMBER(p_wd_pattern_tbl(l_wdy).num_item) );
938
939 ---dow number from wday pattern tbl and sunday = 1
940
941 IF l_act_dt IS NOT NULL THEN ---that day of week exists in month
942
943 l_index := to_number(TO_CHAR(l_act_dt,'YYYYMMDD')) + p_offset_dy;
944
945
946 IF (l_act_dt < p_start_dt) OR (l_act_dt > p_end_dt) THEN
947 NULL;
948
949 ELSE
950
951 IF NOT x_rec_tbl.EXISTS(l_index) then
952
953 IF (l_act_dt + p_offset_dy) < p_start_dt THEN
954 x_rec_tbl(l_index).delivery_date := p_start_dt;
955 ELSIF (l_act_dt + p_offset_dy) > p_end_dt THEN
956 x_rec_tbl(l_index).delivery_date := p_end_dt;
957 ELSE
958 x_rec_tbl(l_index).delivery_date := l_act_dt + p_offset_dy;
959 END IF;
960 ---start and end date will be date without offset
961 x_rec_tbl(l_index).start_date := l_act_dt;
962 x_rec_tbl(l_index).end_date := l_act_dt;
963 END IF; --end of already exists.
964
965 END IF; ---end of l_act_dt < p_start_dt) OR (l_act_dt > p_end_dt)
966
967 END IF; ---l_act_dt null
968
969 END LOOP; --wday loop
970
971 END LOOP; --WK LOOP
972 END LOOP; ---MTH LOOP
973 END LOOP; ---YR LOOP
974
975 EXCEPTION
976 WHEN G_EXCEPTION_HALT_VALIDATION THEN
977 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
978 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
979 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
980 RAISE OKC_API.G_EXCEPTION_ERROR;
981 END IF;
982 WHEN OTHERS THEN
983 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME_OKC,
984 p_msg_name => G_UNEXPECTED_ERROR,
985 p_token1 => G_SQLCODE_TOKEN,
986 p_token1_value => sqlcode,
987 p_token2 => G_SQLERRM_TOKEN,
988 p_token2_value => sqlerrm);
989
990 x_return_status := G_RET_STS_UNEXP_ERROR;
991
992
993
994 END Create_Wday_tbl;
995
996
997
998
999
1000 FUNCTION GET_WD_DATE(mmyyyy IN VARCHAR2,
1001 week IN NUMBER,
1002 dow IN NUMBER) RETURN DATE
1003 IS
1004 first_weekday NUMBER;
1005 first_dow DATE;
1006 retdate DATE;
1007
1008
1009
1010 BEGIN
1011 first_weekday := to_char(to_date('01'||mmyyyy,'DDMMYYYY'),'D');
1012
1013 if first_weekday <= dow then
1014 first_dow := to_date('01'||mmyyyy,'DDMMYYYY')+ (dow-first_weekday);
1015
1016 else
1017 first_dow := to_date('01'||mmyyyy,'DDMMYYYY')+ (dow-first_weekday+7);
1018
1019 end if;
1020
1021 retdate := first_dow + ((week-1)*7);
1022
1023
1024 If to_char(retdate,'MMYYYY') <> mmyyyy Then
1025 retdate := NULL;
1026 End If;
1027
1028 return retdate;
1029
1030
1031 END GET_WD_DATE;
1032
1033 end OKS_SUBSCRIPTION_SCH_PVT;