1 Package BODY OKC_TIME_RES_PVT AS
2 /* $Header: OKCCRESB.pls 120.2 2006/02/24 15:11:51 smallya noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 ---------------------------------------------------------------------------
7 -- Procedures and Functions
8 ---------------------------------------------------------------------------
9 PROCEDURE Get_K_Effectivity(
10 p_chr_id IN NUMBER,
11 p_cle_id IN NUMBER,
12 x_start_date OUT NOCOPY DATE,
13 x_end_date OUT NOCOPY DATE,
14 x_return_status OUT NOCOPY VARCHAR2) IS
15
16 CURSOR chr_csr(p_chr_id IN NUMBER) is
17 select start_date, end_date
18 from okc_k_headers_b
19 where id = p_chr_id;
20
21 CURSOR cle_csr(p_cle_id IN NUMBER) is
22 select start_date, end_date
23 from okc_k_lines_b
24 where id = p_cle_id;
25
26 l_row_not_found BOOLEAN := TRUE;
27 l_chr_rec chr_csr%ROWTYPE;
28 l_cle_rec cle_csr%ROWTYPE;
29 l_token_value varchar2(10) := OKC_API.G_MISS_CHAR;
30 BEGIN
31
32 /* Get the effectivity of the contract header or lines */
33
34 x_start_date := NULL;
35 x_end_date := NULL;
36 x_return_status := OKC_API.G_RET_STS_SUCCESS;
37 IF p_cle_id is NOT NULL and
38 p_cle_id <> OKC_API.G_MISS_NUM Then
39 l_token_value := 'CLE_ID';
40 OPEN cle_csr(p_cle_id);
41 FETCH cle_csr into l_cle_rec;
42 l_row_not_found := cle_csr%NOTFOUND;
43 CLOSE cle_csr;
44 IF (l_row_not_found) THEN
45 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CLE_ID');
46 x_return_status := OKC_API.G_RET_STS_ERROR;
47 return;
48 ELSE
49 x_start_date := l_cle_rec.start_date;
50 x_end_date := l_cle_rec.end_date;
51 END IF;
52 ELSIF p_chr_id is NOT NULL and
53 p_chr_id <> OKC_API.G_MISS_NUM THEN
54 l_token_value := 'CHR_ID';
55 OPEN chr_csr(p_chr_id);
56 FETCH chr_csr into l_chr_rec;
57 l_row_not_found := chr_csr%NOTFOUND;
58 CLOSE chr_csr;
59 IF (l_row_not_found) THEN
60 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CHR_ID');
61 x_return_status := OKC_API.G_RET_STS_ERROR;
62 return;
63 ELSE
64 x_start_date := l_chr_rec.start_date;
65 x_end_date := l_chr_rec.end_date;
66 END IF;
67 END IF;
68 -- The following block is added for Bug#2386569 regarding initialization for perpetual contracts
69 IF x_start_date IS NULL Then
70 x_start_date := to_date('01010001','ddmmyyyy');
71 end if;
72 IF x_end_date IS NULL Then
73 x_end_date := to_date('31124000','ddmmyyyy');
74 end if;
75
76 EXCEPTION
77 WHEN OTHERS THEN
78 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
79 p_msg_name => g_unexpected_error,
80 p_token1 => g_sqlcode_token,
81 p_token1_value => sqlcode,
82 p_token2 => g_col_name_token,
83 p_token2_value => l_token_value,
84 p_token3 => g_sqlerrm_token,
85 p_token3_value => sqlerrm);
86 -- notify caller of an UNEXPECTED error
87 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
88 END Get_K_Effectivity;
89
90 PROCEDURE Get_Timezone(
91 p_tze_id IN NUMBER,
92 x_tze_name OUT NOCOPY VARCHAR2,
93 x_return_status OUT NOCOPY VARCHAR2) IS
94 CURSOR okc_timezone_csr is
95 SELECT global_timezone_name
96 from okx_timezones_v
97 where timezone_id = p_tze_id;
98 l_okc_timezone_rec okc_timezone_csr%ROWTYPE;
99 item_not_found_error EXCEPTION;
100 l_row_notfound BOOLEAN := TRUE;
101 BEGIN
102
103 -- Get the timezone name from the timezone id which is also required for the JTF_TASKS API
104
105 x_return_status := OKC_API.G_RET_STS_SUCCESS;
106 OPEN okc_timezone_csr;
107 FETCH okc_timezone_csr INTO l_okc_timezone_rec;
108 l_row_notfound := okc_timezone_csr%NOTFOUND;
109 CLOSE okc_timezone_csr;
110 IF (l_row_notfound) THEN
111 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TZE_ID');
112 RAISE item_not_found_error;
113 END IF;
114 x_tze_name := l_okc_timezone_rec.global_timezone_name;
115 EXCEPTION
116 WHEN item_not_found_error THEN
117 x_return_status := OKC_API.G_RET_STS_ERROR;
118 WHEN OTHERS THEN
119 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
120 p_msg_name => g_unexpected_error,
121 p_token1 => g_sqlcode_token,
122 p_token1_value => sqlcode,
123 p_token2 => g_col_name_token,
124 p_token2_value => 'TZE_ID',
125 p_token3 => g_sqlerrm_token,
126 p_token3_value => sqlerrm);
127 -- notify caller of an UNEXPECTED error
128 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
129 End Get_Timezone;
130
131 PROCEDURE Create_RTV_N_Tasks(
132 x_return_status OUT NOCOPY VARCHAR2,
133 p_api_version IN NUMBER,
134 p_init_msg_list IN VARCHAR2 ,
135 p_tve_id IN NUMBER,
136 p_date IN DATE,
137 p_cutoff_date IN DATE ,
138 p_coe_id IN NUMBER ,
139 p_tze_id IN NUMBER,
140 p_tze_name VARCHAR2) IS
141 l_api_version CONSTANT NUMBER := 1;
142 l_api_name CONSTANT VARCHAR2(30) := 'Create_RTV_N_Tasks';
143 l_msg_count NUMBER;
144 x_msg_count NUMBER;
145 x_task_id NUMBER;
146 l_msg_data VARCHAR2(2000);
147 x_msg_data VARCHAR2(2000);
148 l_rtvv_rec OKC_TIME_PUB.rtvv_rec_type;
149 x_rtvv_rec OKC_TIME_PUB.rtvv_rec_type;
150 BEGIN
151 x_return_status := OKC_API.START_ACTIVITY(l_api_name,
152 p_init_msg_list,
153 '_COMPLEX',
154 x_return_status);
155 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
156 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
157 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
158 raise OKC_API.G_EXCEPTION_ERROR;
159 END IF;
160
161 -- Cutoff Date is the date which will actually be the start date for the time resolving window
162
163 x_return_status := OKC_API.G_RET_STS_SUCCESS;
164 if p_cutoff_date is NOT NULL Then
165 if p_date < p_cutoff_date then
166 return;
167 end if;
168 end if;
169 l_rtvv_rec.tve_id := p_tve_id;
170 l_rtvv_rec.datetime := p_date;
171 l_rtvv_rec.coe_id := p_coe_id;
172 -- Creating Resolved timevalues
173 OKC_TIME_PUB.CREATE_RESOLVED_TIMEVALUES(
174 p_api_version,
175 p_init_msg_list,
176 x_return_status,
177 x_msg_count,
178 x_msg_data,
179 l_rtvv_rec,
180 x_rtvv_rec );
181
182 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
183 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
184 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
185 raise OKC_API.G_EXCEPTION_ERROR;
186 END IF;
187
188 /* if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
189 return;
190 end if;*/
191
192 -- Creating Tasks
193 OKC_TASK_PUB.CREATE_TASK(
194 p_api_version,
195 p_init_msg_list,
196 'F',
197 x_rtvv_rec.id,
198 p_tze_id,
199 p_tze_name,
200 p_tve_id,
201 p_date,
202 x_return_status,
203 x_msg_count,
204 x_msg_data,
205 x_task_id);
206
207 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
208 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
209 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
210 raise OKC_API.G_EXCEPTION_ERROR;
211 END IF;
212
213 OKC_API.END_ACTIVITY(l_msg_count, l_msg_data);
214
215 EXCEPTION
216 WHEN OKC_API.G_EXCEPTION_ERROR THEN
217 x_return_status := OKC_API.HANDLE_EXCEPTIONS
218 (l_api_name,
219 G_PKG_NAME,
220 'OKC_API.G_RET_STS_ERROR',
221 x_msg_count,
222 x_msg_data,
223 '_COMPLEX');
224 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
225 x_return_status := OKC_API.HANDLE_EXCEPTIONS
226 (l_api_name,
227 G_PKG_NAME,
228 'OKC_API.G_RET_STS_UNEXP_ERROR',
229 x_msg_count,
230 x_msg_data,
231 '_COMPLEX');
232 WHEN OTHERS THEN
233 x_return_status := OKC_API.HANDLE_EXCEPTIONS
234 (l_api_name,
235 G_PKG_NAME,
236 'OTHERS',
237 x_msg_count,
238 x_msg_data,
239 '_COMPLEX');
240
241 END Create_RTV_N_Tasks;
242
243 PROCEDURE Res_day_of_week(
244 x_return_status OUT NOCOPY VARCHAR2,
245 p_api_version IN NUMBER,
246 p_init_msg_list IN VARCHAR2 ,
247 p_tve_id IN NUMBER,
248 p_tze_id IN NUMBER,
249 p_tze_name IN VARCHAR2,
250 p_start_date IN DATE,
251 p_end_date IN DATE,
252 p_cutoff_date IN DATE ,
253 p_nth IN NUMBER,
254 p_daynum_of_week IN NUMBER,
255 p_hour IN NUMBER,
256 p_minute IN NUMBER,
257 p_second IN NUMBER) IS
258 l_daynum_of_week number := OKC_API.G_MISS_NUM;
259 l_daynum_offset number := OKC_API.G_MISS_NUM;
260 l_date date := OKC_API.G_MISS_DATE;
261 l_start_day_of_month date := OKC_API.G_MISS_DATE;
262 l_nth NUMBER := p_nth;
263 x_msg_count NUMBER;
264 x_task_id NUMBER;
265 x_msg_data VARCHAR2(2000);
266 BEGIN
267
268 /* resolving day of the week to an actual date
269 e.g. 5th Monday between 1st Jan 2000 and 30th june 2000 will be 31st Jan 2000 and 29 th May 2000
270 */
271
272 x_return_status := OKC_API.G_RET_STS_SUCCESS;
273 if l_nth = 0 or l_nth is NULL then
274 l_daynum_of_week := to_char(p_start_date,'D');
275 l_daynum_offset := p_daynum_of_week - l_daynum_of_week ;
276 if l_daynum_offset < 0 then
277 l_daynum_offset := 7 + l_daynum_offset;
278 end if;
279 l_date := to_date(to_char(p_start_date + l_daynum_offset,'mmddyyyy') ||
280 lpad(p_hour,2,'0') ||
281 lpad(p_minute,2,'0') ||
282 lpad(p_second,2,'0'), 'mmddyyyyhh24miss');
283 if l_date > p_end_date then
284 x_return_status := OKC_API.G_RET_STS_SUCCESS;
285 return;
286 end if;
287 while (l_date <= p_end_date) loop
288 Create_RTV_N_Tasks(
289 x_return_status,
290 p_api_version,
291 p_init_msg_list,
292 p_tve_id,
293 l_date,
294 p_cutoff_date,
295 NULL,
296 p_tze_id,
297 p_tze_name);
298 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
299 exit;
300 end if;
301 l_date := l_date + 7;
302 end loop;
303 else
304
305 /* nth is the frequency i.e. 99 means last e.g. nth=99, day_of_week=1 will mean Last Sunday */
306
307 if l_nth = 99 then
308 l_nth := 5;
309 end if;
310 l_start_day_of_month := to_date(to_char(p_start_date,'mmyyyy'),'mmyyyy');
311 while l_date <= p_end_date loop
312 l_daynum_of_week := to_char(l_start_day_of_month,'D');
313 l_daynum_offset := p_daynum_of_week - l_daynum_of_week ;
314 if l_daynum_offset < 0 then
315 l_daynum_offset := 7 + l_daynum_offset;
316 end if;
317 l_daynum_offset := l_daynum_offset + (l_nth - 1) * 7;
318 l_date := to_date(to_char(l_start_day_of_month + l_daynum_offset,'mmddyyyy') ||
319 lpad(p_hour,2,'0') ||
320 lpad(p_minute,2,'0') ||
321 lpad(p_second,2,'0'), 'mmddyyyyhh24miss');
322 while (to_char(l_date,'MM') <> to_char(l_start_day_of_month,'MM')) loop
323 l_date := l_date - 7;
324 end loop;
325 if l_date >= p_start_date and l_date <= p_end_date then
326 Create_RTV_N_Tasks(
327 x_return_status,
328 p_api_version,
329 p_init_msg_list,
330 p_tve_id,
331 l_date,
332 p_cutoff_date,
333 NULL,
334 p_tze_id,
335 p_tze_name);
336 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
337 exit;
338 end if;
339 end if;
340 l_start_day_of_month := add_months(l_start_day_of_month,1);
341 end loop;
342 end if;
343 END Res_day_of_week;
344
345 PROCEDURE Res_Time_Events (
346 x_return_status OUT NOCOPY VARCHAR2,
347 p_api_version IN NUMBER,
348 p_init_msg_list IN VARCHAR2 ,
349 p_cnh_id IN NUMBER,
350 p_coe_id IN NUMBER,
351 p_date IN date) IS
352 item_not_found_error EXCEPTION;
353 CURSOR okc_tgn_csr (p_cnh_id IN NUMBER)
354 IS
355 SELECT id, tze_id
356 FROM OKC_TIMEVALUES tgn
357 WHERE cnh_id = p_cnh_id
358 AND tve_type = 'TGN';
359
360 CURSOR okc_tal_csr (p_id IN NUMBER)
361 IS
362 SELECT id, tve_type, duration, operator, before_after, datetime, tve_id_offset, uom_code, tze_id
363 FROM Okc_Timevalues
364 connect by tve_id_offset = prior id
365 start with tve_id_offset = p_id;
366
367 l_okc_tal_rec okc_tal_csr%ROWTYPE;
368 l_okc_tgn_rec okc_tgn_csr%ROWTYPE;
369 l_date date;
370 l_end_date date := NULL; -- bug#2337567 -- default value
371 l_tze_name OKX_TIMEZONES_V.global_timezone_name%TYPE;
372 x_task_id NUMBER;
373 l_tze_id NUMBER;
374 l_tve_id NUMBER;
375 x_msg_count NUMBER;
376 x_msg_data VARCHAR2(2000);
377 l_found BOOLEAN := FALSE;
378 l_cutoff_date DATE := NULL;
379 BEGIN
380
381 /*
382 Resolve timevalues related to an event e.g 10 days after Contract Signing. Will be triggered only when the
383 event occurs
384 */
385
386 x_return_status := OKC_API.G_RET_STS_SUCCESS;
387 l_date := NULL;
388 OPEN okc_tgn_csr(p_cnh_id);
389 LOOP
390 FETCH okc_tgn_csr INTO l_okc_tgn_rec;
391 EXIT WHEN okc_tgn_csr%NOTFOUND;
392 l_found := FALSE; -- bug#2337567 -- moved up from bottom
393 l_end_date := NULL; -- bug#2337567 -- the initialization was lost
394 l_date := p_date;
395 FOR l_okc_tal_rec in okc_tal_csr(l_okc_tgn_rec.id)
396 LOOP
397 l_found := TRUE;
398 if l_okc_tal_rec.before_after = 'B' then
399 l_okc_tal_rec.duration := -1 * l_okc_tal_rec.duration;
400 end if;
401 l_end_date := OKC_TIME_UTIL_PUB.GET_ENDDATE(
402 l_date,
403 l_okc_tal_rec.uom_code,
404 l_okc_tal_rec.duration);
405 if l_end_date is NULL Then
406 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'END_DATE');
407 x_return_status := OKC_API.G_RET_STS_ERROR;
408 exit;
409 end if;
410 if ((l_end_date - l_date) >= 1) then
411 l_date := l_end_date + 1;
412 else
413 l_date := l_end_date + (86399/86400);
414 end if;
415 l_tve_id := l_okc_tal_rec.id;
416 l_tze_id := l_okc_tal_rec.tze_id;
417 END LOOP;
418 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
419 exit;
420 END IF;
421 IF l_end_date is NULL THEN
422 if l_found THEN
423 goto next_record;
424 else
425 l_end_date := p_date;
426 l_tve_id := l_okc_tgn_rec.id;
427 l_tze_id := l_okc_tgn_rec.tze_id;
428 END IF;
429 END IF;
430 if l_tze_id is NOT NULL and
431 l_tze_id <> OKC_API.G_MISS_NUM then
432 Get_Timezone(l_tze_id, l_tze_name, x_return_status);
433 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
434 exit;
435 end if;
436 else
437 l_tze_name := NULL;
438 end if;
439 Create_RTV_N_Tasks(
440 x_return_status,
441 p_api_version,
442 p_init_msg_list,
443 l_tve_id,
444 l_end_date,
445 l_cutoff_date,
446 p_coe_id,
447 l_tze_id,
448 l_tze_name);
449 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
450 exit;
451 end if;
452 << next_record>>
453 null; -- bug#2337567 -- see initialization in head of the loop
454 END LOOP;
455 EXCEPTION
456 WHEN OTHERS THEN
457 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
458 p_msg_name => g_unexpected_error,
459 p_token1 => g_sqlcode_token,
460 p_token1_value => sqlcode,
461 p_token2 => g_col_name_token,
462 p_token2_value => 'TVE_ID',
463 p_token3 => g_sqlerrm_token,
464 p_token3_value => sqlerrm);
465 -- notify caller of an UNEXPECTED error
466 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
467 END Res_Time_Events ;
468
469 PROCEDURE Res_TPG_Delimited (
470 x_return_status OUT NOCOPY VARCHAR2,
471 p_api_version IN NUMBER,
472 p_init_msg_list IN VARCHAR2 ,
473 p_tve_id IN NUMBER,
474 p_tze_id IN NUMBER,
475 p_tze_name VARCHAR2,
476 p_month IN NUMBER,
477 p_day IN NUMBER,
478 p_nth IN NUMBER,
479 p_day_of_week IN VARCHAR2,
480 p_hour IN NUMBER,
481 p_minute IN NUMBER,
482 p_second IN NUMBER,
483 p_start_date IN date,
484 p_end_date IN date,
485 p_cutoff_date IN date) IS
486 l_month NUMBER := OKC_API.G_MISS_NUM;
487 l_start_date1 date := OKC_API.G_MISS_DATE;
488 l_end_date1 date := OKC_API.G_MISS_DATE;
489 l_start_date date := OKC_API.G_MISS_DATE;
490 l_end_date date := OKC_API.G_MISS_DATE;
491 l_date date := OKC_API.G_MISS_DATE;
492 l_daynum_of_week NUMBER;
493 x_msg_count NUMBER;
494 x_msg_data VARCHAR2(2000);
495 x_task_id NUMBER;
496 BEGIN
497
498 /*
499 Resolve all generic dates e.g. Last day of every month or, 15th of every month etc.
500 Nth is the frequency i.e. 99 means last e.g. nth=99, day_of_week=1 will mean Last Sunday.
501 Also p_day=99 means the last day of the month
502 */
503
504 x_return_status := OKC_API.G_RET_STS_SUCCESS;
505 /* Find the start and end dates if not passed and store them in l_start_date1 and l_end_date1 */
506 if (p_start_date is null or p_start_date = OKC_API.G_MISS_DATE) then
507 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'START_DATE');
508 x_return_status := OKC_API.G_RET_STS_ERROR;
509 return;
510 elsif (p_end_date is null or p_end_date = OKC_API.G_MISS_DATE) then
511 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'END_DATE');
512 x_return_status := OKC_API.G_RET_STS_ERROR;
513 return;
514 else
515 if p_start_date > p_end_date then
516 x_return_status := OKC_API.G_RET_STS_ERROR;
517 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'START_DATE');
518 return;
519 end if;
520 l_start_date1 := p_start_date;
521 l_end_date1 := p_end_date;
522 end if;
523 if (p_day_of_week is NOT NULL and p_day_of_week <> OKC_API.G_MISS_CHAR) then
524 if p_day_of_week = 'SUN' then
525 l_daynum_of_week := 1;
526 elsif p_day_of_week = 'MON' then
527 l_daynum_of_week := 2;
528 elsif p_day_of_week = 'TUE' then
529 l_daynum_of_week := 3;
530 elsif p_day_of_week = 'WED' then
531 l_daynum_of_week := 4;
532 elsif p_day_of_week = 'THU' then
533 l_daynum_of_week := 5;
534 elsif p_day_of_week = 'FRI' then
535 l_daynum_of_week := 6;
536 elsif p_day_of_week = 'SAT' then
537 l_daynum_of_week := 7;
538 end if;
539 end if;
540 If p_month is not null then
541 l_month := to_char(l_start_date1,'MM');
542 if l_month = p_month then
543 l_start_date := l_start_date1;
544 elsif l_month < p_month then
545 l_start_date := to_date('01'||lpad(p_month,2,0)||to_char(l_start_date1,'YYYY'),'ddmmyyyy');
546 elsif l_month > p_month then
547 l_start_date := to_date('01'||lpad(p_month,2,0)||to_char(to_number(to_char(l_start_date1,'YYYY')) +1),'ddmmyyyy');
548 end if;
549 l_end_date := last_day(l_start_date);
550 if l_end_date1 < l_end_date then
551 l_end_date := l_end_date1;
552 end if;
553 if l_start_date > l_end_date then
554 /* Will never be resolved */
555 return;
556 end if;
557 while (l_start_date <= l_end_date1) loop
558 If p_day_of_week is not null then
559 Res_day_of_week(x_return_status,
560 p_api_version,
561 p_init_msg_list,
562 p_tve_id,
563 p_tze_id,
564 p_tze_name,
565 l_start_date,
566 l_end_date,
567 p_cutoff_date,
568 p_nth,
569 l_daynum_of_week,
570 p_hour,
571 p_minute,
572 p_second);
573 elsif p_day is not null and
574 p_day <> 99 then
575 l_date := to_date(lpad(p_day,2,'0') ||
576 to_char(l_start_date,'mmyyyy') ||
577 lpad(p_hour,2,'0') ||
578 lpad(p_minute,2,'0') ||
579 lpad(p_second,2,'0'),'ddmmyyyyhh24miss');
580 if l_date >= l_start_date then
581 Create_RTV_N_Tasks(
582 x_return_status,
583 p_api_version,
584 p_init_msg_list,
585 p_tve_id,
586 l_date,
587 p_cutoff_date,
588 NULL,
589 p_tze_id,
590 p_tze_name);
591 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
592 exit;
593 end if;
594 end if;
595 elsif p_day = 99 then
596 l_date := last_day(to_date( to_char(l_start_date,'mmyyyy') ||
597 lpad(p_hour,2,'0') ||
598 lpad(p_minute,2,'0') ||
599 lpad(p_second,2,'0'),'mmyyyyhh24miss'));
600 Create_RTV_N_Tasks(
601 x_return_status,
602 p_api_version,
603 p_init_msg_list,
604 p_tve_id,
605 l_date,
606 p_cutoff_date,
607 NULL,
608 p_tze_id,
609 p_tze_name);
610 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
611 exit;
612 end if;
613 end if;
614 l_start_date := to_date('01'||lpad(p_month,2,0)||to_char(to_number(to_char(l_start_date,'yyyy'))+1),'ddmmyyyy');
615 l_end_date := last_day(l_start_date);
616 if l_end_date1 < l_end_date then
617 l_end_date := l_end_date1;
618 end if;
619 end loop;
620 end if;
621 If p_day_of_week is not null and
622 p_month is null then
623 Res_day_of_week(x_return_status,
624 p_api_version,
625 p_init_msg_list,
626 p_tve_id,
627 p_tze_id,
628 p_tze_name,
629 l_start_date1,
630 l_end_date1,
631 p_cutoff_date,
632 p_nth,
633 l_daynum_of_week,
634 p_hour,
635 p_minute,
636 p_second);
637 elsif p_day is not null and
638 p_day <> 99 and
639 p_month is null then
640 l_start_date := l_start_date1;
641 l_date := to_date(lpad(p_day,2,'0') ||
642 to_char(l_start_date,'mmyyyy') ||
643 lpad(p_hour,2,'0') ||
644 lpad(p_minute,2,'0') ||
645 lpad(p_second,2,'0'),'ddmmyyyyhh24miss');
646 while (l_date <= l_end_date1) loop
647 if l_date >= l_start_date1 then
648 Create_RTV_N_Tasks(
649 x_return_status,
650 p_api_version,
651 p_init_msg_list,
652 p_tve_id,
653 l_date,
654 p_cutoff_date,
655 NULL,
656 p_tze_id,
657 p_tze_name);
658 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
659 exit;
660 end if;
661 end if;
662 l_date := add_months(l_date,1);
663 end loop;
664 elsif p_day = 99 and
665 p_month is null then
666 l_start_date := l_start_date1;
667 l_date := last_day(to_date( to_char(l_start_date,'mmyyyy') ||
668 lpad(p_hour,2,'0') ||
669 lpad(p_minute,2,'0') ||
670 lpad(p_second,2,'0'),'mmyyyyhh24miss'));
671 while (l_date <= l_end_date1) loop
672 Create_RTV_N_Tasks(
673 x_return_status,
674 p_api_version,
675 p_init_msg_list,
676 p_tve_id,
677 l_date,
678 p_cutoff_date,
679 NULL,
680 p_tze_id,
681 p_tze_name);
682 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
683 exit;
684 end if;
685 l_date := add_months(l_date,1);
686 end loop;
687 elsif (p_day is null and /* Every day */
688 p_month is null and
689 p_day_of_week is null) then
690 l_start_date := l_start_date1;
691 l_date := to_date( to_char(l_start_date,'yyyymmdd') ||
692 lpad(p_hour,2,'0') ||
693 lpad(p_minute,2,'0') ||
694 lpad(p_second,2,'0'),'yyyymmddhh24miss');
695 while (l_date <= l_end_date1) loop
696 Create_RTV_N_Tasks(
697 x_return_status,
698 p_api_version,
699 p_init_msg_list,
700 p_tve_id,
701 l_date,
702 p_cutoff_date,
703 NULL,
704 p_tze_id,
705 p_tze_name);
706 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
707 exit;
708 end if;
709 l_date := l_date + 1;
710 end loop;
711 end if;
712 EXCEPTION
713 WHEN OTHERS THEN
714 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
715 p_msg_name => g_unexpected_error,
716 p_token1 => g_sqlcode_token,
717 p_token1_value => sqlcode,
718 p_token2 => g_col_name_token,
719 p_token2_value => 'TVE_ID',
720 p_token3 => g_sqlerrm_token,
721 p_token3_value => sqlerrm);
722 -- notify caller of an UNEXPECTED error
723 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
724 END Res_TPG_Delimited ;
725
726 PROCEDURE Res_TPG_Delimited (
727 x_return_status OUT NOCOPY VARCHAR2,
728 p_api_version IN NUMBER,
729 p_init_msg_list IN VARCHAR2 ,
730 p_tve_id IN NUMBER,
731 p_start_date IN date,
732 p_end_date IN date,
733 p_cutoff_date IN date) IS
734 item_not_found_error EXCEPTION;
735 l_row_notfound BOOLEAN := TRUE;
736 l_month NUMBER := OKC_API.G_MISS_NUM;
737 l_start_date1 date := OKC_API.G_MISS_DATE;
738 l_end_date1 date := OKC_API.G_MISS_DATE;
739 l_start_date date := OKC_API.G_MISS_DATE;
740 l_end_date date := OKC_API.G_MISS_DATE;
741 l_date date := OKC_API.G_MISS_DATE;
742 x_task_id NUMBER;
743 l_tze_name OKX_TIMEZONES_V.global_timezone_name%TYPE;
744 CURSOR okc_tgd_csr (p_tve_id IN NUMBER)
745 IS
746 SELECT month, day, nth, day_of_week, hour, minute, second, tze_id
747 FROM OKC_TIMEVALUES
748 WHERE id = p_tve_id
749 and tve_type = 'TGD';
750 l_okc_tgd_rec okc_tgd_csr%ROWTYPE;
751 BEGIN
752
753 /*
754 Resolve all generic dates e.g. Last day of every month or, 15th of every month etc.
755 */
756
757 x_return_status := OKC_API.G_RET_STS_SUCCESS;
758 /* Find the start and end dates if not passed and store them in l_start_date1 and l_end_date1 */
759 if (p_start_date is null or p_start_date = OKC_API.G_MISS_DATE) then
760 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'START_DATE');
761 x_return_status := OKC_API.G_RET_STS_ERROR;
762 return;
763 elsif (p_end_date is null or p_end_date = OKC_API.G_MISS_DATE) then
764 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'END_DATE');
765 x_return_status := OKC_API.G_RET_STS_ERROR;
766 return;
767 else
768 if p_start_date > p_end_date then
769 x_return_status := OKC_API.G_RET_STS_ERROR;
770 OKC_API.set_message(G_APP_NAME, 'OKC_INVALID_END_DATE');
771 return;
772 end if;
773 l_start_date1 := p_start_date;
774 l_end_date1 := p_end_date;
775 end if;
776 OPEN okc_tgd_csr(p_tve_id);
777 FETCH okc_tgd_csr INTO l_okc_tgd_rec;
778 l_row_notfound := okc_tgd_csr%NOTFOUND;
779 CLOSE okc_tgd_csr;
780 IF (l_row_notfound) THEN
781 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TVE_ID');
782 RAISE item_not_found_error;
783 END IF;
784 if l_okc_tgd_rec.tze_id is NOT NULL and
785 l_okc_tgd_rec.tze_id <> OKC_API.G_MISS_NUM then
786 Get_Timezone(l_okc_tgd_rec.tze_id, l_tze_name, x_return_status);
787 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
788 return;
789 end if;
790 else
791 l_tze_name := NULL;
792 end if;
793 Res_TPG_Delimited (
794 x_return_status,
795 p_api_version,
796 p_init_msg_list,
797 p_tve_id,
798 l_okc_tgd_rec.tze_id,
799 l_tze_name,
800 l_okc_tgd_rec.month,
801 l_okc_tgd_rec.day,
802 l_okc_tgd_rec.nth,
803 l_okc_tgd_rec.day_of_week,
804 l_okc_tgd_rec.hour,
805 l_okc_tgd_rec.minute,
806 l_okc_tgd_rec.second,
807 p_start_date,
808 p_end_date,
809 p_cutoff_date) ;
810 EXCEPTION
811 WHEN item_not_found_error THEN
812 x_return_status := OKC_API.G_RET_STS_ERROR;
813 WHEN OTHERS THEN
814 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
815 p_msg_name => g_unexpected_error,
816 p_token1 => g_sqlcode_token,
817 p_token1_value => sqlcode,
818 p_token2 => g_col_name_token,
819 p_token2_value => 'TVE_ID',
820 p_token3 => g_sqlerrm_token,
821 p_token3_value => sqlerrm);
822 -- notify caller of an UNEXPECTED error
823 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
824 END Res_TPG_Delimited ;
825
826 PROCEDURE Res_Cycle (
827 x_return_status OUT NOCOPY VARCHAR2,
828 p_api_version IN NUMBER,
829 p_init_msg_list IN VARCHAR2 ,
830 p_tve_id IN NUMBER,
831 p_tze_id IN NUMBER,
832 p_tze_name IN VARCHAR2,
833 p_start_date IN date,
834 p_end_date IN date,
835 p_cutoff_date IN date) IS
836 CURSOR okc_spn_csr (p_id IN NUMBER)
837 IS
838 SELECT id, active_yn, duration, uom_code
839 FROM Okc_Span
840 connect by prior id = spn_id
841 start with ((spn_id is NULL) or (spn_id = OKC_API.G_MISS_NUM)) and
842 tve_id = p_id;
843 type l_okc_spn_tbl_type is table of okc_spn_csr%ROWTYPE index by binary_integer;
844 item_not_found_error EXCEPTION;
845 l_row_notfound BOOLEAN := TRUE;
846 l_end_date date;
847 l_start_date date;
848 i NUMBER;
849 l_okc_spn_tbl l_okc_spn_tbl_type;
850 x_msg_count NUMBER;
851 x_msg_data VARCHAR2(2000);
852 x_task_id NUMBER;
853 BEGIN
854
855 /*
856 Resolve all recurring dates e.g. Every 6 months
857 */
858
859 x_return_status := OKC_API.G_RET_STS_SUCCESS;
860 /* Find the start and end dates if not passed and store them in l_start_date1 and l_end_date1 */
861 if (p_start_date is null or p_start_date = OKC_API.G_MISS_DATE) then
862 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'START_DATE');
863 x_return_status := OKC_API.G_RET_STS_ERROR;
864 return;
865 elsif (p_end_date is null or p_end_date = OKC_API.G_MISS_DATE) then
866 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'END_DATE');
867 x_return_status := OKC_API.G_RET_STS_ERROR;
868 return;
869 else
870 if p_start_date > p_end_date then
871 x_return_status := OKC_API.G_RET_STS_ERROR;
872 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'START_DATE');
873 return;
874 end if;
875 end if;
876 l_start_date := p_start_date;
877 l_end_date := p_start_date;
878
879 /* one recurring timevalue may comprise of many spans */
880
881 OPEN okc_spn_csr(p_tve_id);
882 i := 0;
883 LOOP
884 FETCH okc_spn_csr INTO l_okc_spn_tbl(i);
885 EXIT WHEN okc_spn_csr%NOTFOUND;
886 i:=i+1;
887 END LOOP;
888 CLOSE okc_spn_csr;
889 IF (l_okc_spn_tbl.COUNT > 0) THEN
890 while l_end_date <= p_end_date
891 LOOP
892 i := l_okc_spn_tbl.FIRST;
893 LOOP
894 l_end_date := okc_time_util_pub.get_enddate(l_start_date, l_okc_spn_tbl(i).uom_code,l_okc_spn_tbl(i).duration);
895 if (l_end_date > p_end_date) THEN
896 exit;
897 end if;
898 if l_okc_spn_tbl(i).active_yn = 'Y' then
899 Create_RTV_N_Tasks(
900 x_return_status,
901 p_api_version,
902 p_init_msg_list,
903 p_tve_id,
904 l_end_date,
905 p_cutoff_date,
906 NULL,
907 p_tze_id,
908 p_tze_name);
909 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
910 exit;
911 end if;
912 end if;
913 if ((l_end_date - l_start_date) >= 1) then
914 l_start_date := l_end_date + 1;
915 else
916 l_start_date := l_end_date + (86399/86400);
917 end if;
918 EXIT WHEN (i = l_okc_spn_tbl.LAST);
919 i := l_okc_spn_tbl.NEXT(i);
920 END LOOP;
921 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
922 exit;
923 END IF;
924 if (l_end_date > p_end_date) THEN
925 exit;
926 end if;
927 END LOOP;
928 ELSE
929 x_return_status := OKC_API.G_RET_STS_ERROR;
930 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SPN_ID');
931 return;
932 END IF;
933 EXCEPTION
934 WHEN item_not_found_error THEN
935 x_return_status := OKC_API.G_RET_STS_ERROR;
936 WHEN OTHERS THEN
937 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
938 p_msg_name => g_unexpected_error,
939 p_token1 => g_sqlcode_token,
940 p_token1_value => sqlcode,
941 p_token2 => g_col_name_token,
942 p_token2_value => 'TVE_ID',
943 p_token3 => g_sqlerrm_token,
944 p_token3_value => sqlerrm);
945 -- notify caller of an UNEXPECTED error
946 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
947 END Res_Cycle ;
948
949 -- The parameters p_resolved_until_date is the date till which the timevalues will be resolved
950 -- p_cutoff_date is the threshold date below which dates resolved will be ignored.
951 -- In other words dates will be resolved between p_cutoff_date and the least of (p_resolved_until_date,end date of K header/Line)
952
953
954 PROCEDURE Res_Time_K(
955 p_chr_id IN NUMBER,
956 p_resolved_until_date IN DATE ,
957 p_cutoff_date IN DATE,
958 p_api_version IN NUMBER,
959 p_init_msg_list IN VARCHAR2 ,
960 x_return_status OUT NOCOPY VARCHAR2) IS
961
962 TYPE rule_csr_type is REF CURSOR;
963 rule_csr rule_csr_type;
964
965 TYPE tve_id_csr_type is REF CURSOR;
966 tve_id_csr tve_id_csr_type;
967
968 -- The following cursor is changed to nvl for dates for Bug#2386569 regarding initialization for perpetual contracts
969 CURSOR limited_by_csr(p_tve_id IN NUMBER) is
970 select nvl(ise.start_date,to_date('01010001','ddmmyyyy')) start_date,
971 nvl(ise.end_date,to_date('31124000','ddmmyyyy')) end_date,
972 tze_id
973 from okc_time_ia_startend_val_v ise
974 where ise.id = p_tve_id;
975
976 l_list_of_rules VARCHAR2(4000);
977 l_list_of_rules1 VARCHAR2(4000);
978 l_list_of_tve_id VARCHAR2(4000);
979 l_row_not_found BOOLEAN := TRUE;
980 l_limited_by_rec limited_by_csr%ROWTYPE;
981 l_start_date DATE;
982 l_end_date DATE;
983 l_k_start_date DATE;
984 l_k_end_date DATE;
985 l_rul_id NUMBER;
986 l_chr_id NUMBER;
987 l_cle_id NUMBER;
988 l_tve_id NUMBER;
989 l_tve_id_limited NUMBER;
990 l_tve_type OKC_TIMEVALUES.tve_type%type;
991 l_sql_string varchar2(4000);
992 l_datetime date;
993 x_msg_count NUMBER;
994 x_msg_data VARCHAR2(2000);
995 x_task_id NUMBER;
996 l_rule_type OKC_RULES_V.rule_information_category%type;
997 l_app_id number;
998 l_rule_df_name varchar2(40);
999 l_col_val_table OKC_TIME_UTIL_PUB.t_col_vals;
1000 l_no_of_cols number;
1001 l_tze_id number;
1002 l_tze_name OKX_TIMEZONES_V.global_timezone_name%TYPE;
1003 p_isev_ext_rec OKC_TIME_PUB.isev_ext_rec_type;
1004 x_isev_ext_rec OKC_TIME_PUB.isev_ext_rec_type;
1005 l_pending_further_resolving VARCHAR2(1) := 'X';
1006 l_over_further_resolving VARCHAR2(1) := 'X';
1007
1008 BEGIN
1009
1010 /*
1011 Resolve all timevalues related to a contract (excluding the event ones).
1012 */
1013
1014 /* Get the application_id and get the rule definition names */
1015
1016 l_app_id := OKC_TIME_UTIL_PUB.get_app_id;
1017 if l_app_id is null then
1018 return;
1019 end if;
1020
1021 l_rule_df_name := OKC_TIME_UTIL_PUB.get_rule_df_name;
1022 if l_rule_df_name is null then
1023 return;
1024 end if;
1025
1026 /* Get all the rule types (e.g. NTN) from metadata which are related to timevalues.*/
1027 /* Get all the rule types (e.g. NTN) from metadata which are related to tasks.*/
1028
1029 l_list_of_rules := OKC_TIME_UTIL_PUB.get_rule_defs_using_vs(l_app_id,l_rule_df_name,'OKC_TIMEVALUES');
1030 l_list_of_rules1 := OKC_TIME_UTIL_PUB.get_rule_defs_using_vs(l_app_id,l_rule_df_name,'OKC_TASK_RS');
1031
1032 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1033
1034 /* For these rules, get their ids using the contract header (line) id. */
1035
1036 l_sql_string := 'select r.id, rg.chr_id, rg.cle_id, r.rule_information_category ' ||
1037 'from okc_rules_b r, okc_rule_groups_b rg '||
1038 'where r.dnz_chr_id = :p_chr_id ' ||
1039 'and rg.id = r.rgp_id ' ||
1040 'and r.rule_information_category in '|| l_list_of_rules ||
1041 'and r.rule_information_category in '|| l_list_of_rules1 ;
1042 open rule_csr for l_sql_string using p_chr_id;
1043 loop
1044 fetch rule_csr into l_rul_id, l_chr_id, l_cle_id, l_rule_type;
1045 exit when rule_csr%NOTFOUND;
1046
1047 /* Get all the timevalues associated with the rule. Currently there is only one tve_id per rule. May be extended in
1048 future. API is flexible to handle this */
1049
1050 l_list_of_tve_id := OKC_TIME_UTIL_PUB.get_tve_ids(l_app_id,l_rule_df_name,l_rule_type,'OKC_TIMEVALUES',
1051 l_rul_id);
1052 if l_list_of_tve_id is NULL Then
1053 goto next_row1;
1054 end if;
1055 l_sql_string :=
1056 'select id, tve_id_limited, tve_type, datetime, tze_id ' ||
1057 'from okc_timevalues ' ||
1058 'where ((tve_type in ( ''TGD'',''TAV''))'||
1059 ' or (tve_type = ''CYL'' and interval_yn = ''N'')) ' ||
1060 'and id in '|| l_list_of_tve_id;
1061 open tve_id_csr for l_sql_string;
1062 loop
1063 fetch tve_id_csr into l_tve_id, l_tve_id_limited, l_tve_type, l_datetime, l_tze_id;
1064 exit when tve_id_csr%NOTFOUND;
1065
1066 /* Absolute Time Value will have only one date */
1067
1068 IF l_tve_type = 'TAV' then
1069 if l_tze_id is NOT NULL and
1070 l_tze_id <> OKC_API.G_MISS_NUM then
1071 Get_Timezone(l_tze_id, l_tze_name, x_return_status);
1072 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1073 exit;
1074 end if;
1075 else
1076 l_tze_name := NULL;
1077 end if;
1078 Create_RTV_N_Tasks(
1079 x_return_status,
1080 p_api_version,
1081 p_init_msg_list,
1082 l_tve_id,
1083 l_datetime,
1084 p_cutoff_date,
1085 NULL,
1086 l_tze_id,
1087 l_tze_name);
1088 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1089 exit;
1090 end if;
1091 goto next_row;
1092 end if;
1093
1094 /* If timevalue is not limited use the Contract headers and lines effectivity */
1095
1096 IF l_tve_id_limited is NULL or
1097 l_tve_id_limited = OKC_API.G_MISS_NUM then
1098 Get_K_Effectivity(
1099 l_chr_id,
1100 l_cle_id,
1101 l_start_date,
1102 l_end_date,
1103 x_return_status);
1104 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1105 exit;
1106 end if;
1107 IF l_cle_id is NOT NULL and
1108 l_cle_id <> OKC_API.G_MISS_NUM Then
1109 null;
1110 ELSIF l_chr_id is NOT NULL and
1111 l_chr_id <> OKC_API.G_MISS_NUM Then
1112
1113 /* If the contract is perpetual or for a very large duration, resolve this only until the derived
1114 resolved until date to avoid too many resolved timevalues being created. this check has to be
1115 done for each rule tied to a contract header.
1116 */
1117
1118 if l_end_date >= p_resolved_until_date Then
1119 l_pending_further_resolving := '1';
1120 elsif l_end_date < p_resolved_until_date Then
1121 l_over_further_resolving := '1';
1122 end if;
1123 end if;
1124 if l_end_date > p_resolved_until_date then
1125 l_end_date := p_resolved_until_date;
1126 end if;
1127
1128 /* For generic and recurring call the respective procedures to create resolved timevalues */
1129 if l_start_date <= p_resolved_until_date then
1130
1131 If l_tve_type = 'TGD' then
1132 Res_TPG_Delimited ( x_return_status,p_api_version,p_init_msg_list,l_tve_id, l_start_date, l_end_date, p_cutoff_date);
1133 elsif l_tve_type = 'CYL' then
1134 Res_Cycle ( x_return_status,p_api_version,p_init_msg_list,l_tve_id, l_tze_id, l_tze_name, l_start_date, l_end_date, p_cutoff_date);
1135 end if;
1136 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1137 exit;
1138 end if;
1139 end if;
1140 ELSE
1141
1142 /* If timevalue is limited get the effectivity of the timevalue using tve_id_limited */
1143
1144 OPEN limited_by_csr(l_tve_id_limited);
1145 FETCH limited_by_csr into l_limited_by_rec;
1146 l_row_not_found := limited_by_csr%NOTFOUND;
1147 CLOSE limited_by_csr;
1148 IF (l_row_not_found) THEN
1149 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TVE_ID');
1150 x_return_status := OKC_API.G_RET_STS_ERROR;
1151 ELSE
1152 l_start_date := l_limited_by_rec.start_date;
1153 l_end_date := l_limited_by_rec.end_date;
1154
1155 /*
1156 A timevalue is created using the timevalue editor for an unsigned contract such that the start date is null
1157 (assuming to be the start of the contract) but the end date is a valid limiting date (not the end date of the
1158 contract). In order to store that as a tve_id_limited, the start_date can be set to the start_date of the
1159 contract header/line. But the contract is not yet signed, therfore a change to the start_date would mean a
1160 change in the start date of the tve_id_limited.
1161 In order to overcome this problem, we are storing the start_date as 01010001 and we will update this to the
1162 actual start date of the contract once the contract is signed/approved and this procedure is called.
1163 The same can be said to be the reason for storing end_date as 31124000 where the end_date is entered as null
1164 but the start date is an actual limiting date.
1165
1166 BETTER SOLUTION: Change datamodel to have the K effectivity pointing to a timevalue and then the limited
1167 timevalue will also be pointing to the same start date as referenced by the contract start date.
1168 */
1169
1170 if l_start_date = to_date('01010001','ddmmyyyy') or
1171 l_end_date = to_date('31124000','ddmmyyyy') Then
1172 Get_K_Effectivity(
1173 l_chr_id,
1174 l_cle_id,
1175 l_k_start_date,
1176 l_k_end_date,
1177 x_return_status);
1178 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1179 exit;
1180 end if;
1181 if l_start_date = to_date('01010001','ddmmyyyy') Then
1182 l_start_date := l_k_start_date;
1183 if l_start_date > l_end_date Then
1184 l_start_date := l_end_date;
1185 end if;
1186 end if;
1187 if l_end_date = to_date('31124000','ddmmyyyy') Then
1188 l_end_date := l_k_end_date;
1189 if l_start_date > l_end_date Then
1190 l_end_date := l_start_date;
1191 end if;
1192 end if;
1193 p_isev_ext_rec.start_date := l_start_date;
1194 p_isev_ext_rec.end_date := l_end_date;
1195 p_isev_ext_rec.id := l_tve_id_limited;
1196 OKC_TIME_pub.update_ia_startend(
1197 p_api_version,
1198 p_init_msg_list,
1199 x_return_status,
1200 x_msg_count,
1201 x_msg_data,
1202 p_isev_ext_rec,
1203 x_isev_ext_rec);
1204 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1205 exit;
1206 end if;
1207 end if;
1208
1209 /* For contract headers, if the end date > derived resolved until date, use the derived_resolved until date
1210 as a limiting date for time resolving timevalue and also update the contract header to reflect this value.
1211 Otherwise reset the resolved until date of the contract header to NULL
1212 */
1213
1214 IF l_cle_id is NOT NULL and
1215 l_cle_id <> OKC_API.G_MISS_NUM Then
1216 null;
1217 ELSIF l_chr_id is NOT NULL and
1218 l_chr_id <> OKC_API.G_MISS_NUM Then
1219 if l_end_date > p_resolved_until_date Then
1220 l_pending_further_resolving := '1';
1221 elsif l_end_date < p_resolved_until_date Then
1222 l_over_further_resolving := '1';
1223 end if;
1224 end if;
1225 if l_end_date > p_resolved_until_date then
1226 l_end_date := p_resolved_until_date;
1227 end if;
1228
1229 /* For generic and recurring call the respective procedures to create resolved timevalues */
1230 if l_start_date <= p_resolved_until_date then
1231
1232 If l_tve_type = 'TGD' then
1233 Res_TPG_Delimited ( x_return_status,p_api_version,p_init_msg_list,l_tve_id, l_start_date, l_end_date, p_cutoff_date);
1234 elsif l_tve_type = 'CYL' then
1235 Res_Cycle ( x_return_status,p_api_version,p_init_msg_list,l_tve_id, l_tze_id, l_tze_name, l_start_date, l_end_date, p_cutoff_date);
1236 end if;
1237 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1238 exit;
1239 end if;
1240 end if;
1241 END IF;
1242 END IF;
1243 <<next_row>>
1244 null;
1245 end loop;
1246 close tve_id_csr;
1247 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1248 exit;
1249 END IF;
1250 <<next_row1>>
1251 null;
1252 end loop;
1253 close rule_csr;
1254 if l_pending_further_resolving = '1' Then
1255 update okc_k_headers_b
1256 set resolved_until = p_resolved_until_date
1257 where id = l_chr_id;
1258 elsif l_over_further_resolving = '1' Then
1259 update okc_k_headers_b
1260 set resolved_until = NULL
1261 where id = l_chr_id;
1262 end if;
1263 l_pending_further_resolving := 'X';
1264 l_over_further_resolving := 'X';
1265 EXCEPTION
1266 WHEN OTHERS THEN
1267 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1268 p_msg_name => g_unexpected_error,
1269 p_token1 => g_sqlcode_token,
1270 p_token1_value => sqlcode,
1271 p_token2 => g_col_name_token,
1272 p_token2_value => 'CHR_ID',
1273 p_token3 => g_sqlerrm_token,
1274 p_token3_value => sqlerrm);
1275 -- notify caller of an UNEXPECTED error
1276 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1277 END Res_Time_K;
1278
1279 PROCEDURE Res_Time_New_K(
1280 p_chr_id IN NUMBER,
1281 p_api_version IN NUMBER,
1282 p_init_msg_list IN VARCHAR2 ,
1283 x_return_status OUT NOCOPY VARCHAR2) IS
1284
1285 BEGIN
1286 Res_Time_K(p_chr_id => p_chr_id,
1287 p_resolved_until_date => add_months(sysdate,(OKC_TIME_RES_PVT.RESOLVE_UNTIL_MONTHS +
1288 OKC_TIME_RES_PVT.PICKUP_UPTO_MONTHS)) ,
1289 p_cutoff_date => sysdate,
1290 p_api_version => p_api_version,
1291 p_init_msg_list => p_init_msg_list,
1292 x_return_status => x_return_status);
1293 END Res_Time_New_K;
1294
1295 PROCEDURE Res_Time_Extnd_K(
1296 p_chr_id IN NUMBER,
1297 p_cle_id IN NUMBER,
1298 p_start_date IN DATE,
1299 p_end_date IN DATE,
1300 p_api_version IN NUMBER,
1301 p_init_msg_list IN VARCHAR2 ,
1302 x_return_status OUT NOCOPY VARCHAR2) IS
1303
1304 TYPE rgp_csr_type is REF CURSOR;
1305 rgp_csr rgp_csr_type;
1306
1307 TYPE tve_id_csr_type is REF CURSOR;
1308 tve_id_csr tve_id_csr_type;
1309
1310 -- The following cursor is changed to nvl for dates for Bug#2386569 regarding initialization for perpetual contracts
1311 CURSOR limited_by_csr(p_tve_id IN NUMBER) is
1312 select nvl(ise.start_date,to_date('01010001','ddmmyyyy')) start_date,
1313 nvl(ise.end_date,to_date('31124000','ddmmyyyy')) end_date,
1314 tze_id
1315 from okc_time_ia_startend_val_v ise
1316 where ise.id = p_tve_id;
1317
1318
1319 CURSOR resolved_until_csr(p_cle_id IN NUMBER) is
1320 SELECT resolved_until from okc_k_headers_b h, okc_k_lines_b l
1321 where h.id = l.chr_id
1322 and l.id = p_cle_id;
1323
1324 l_list_of_rules VARCHAR2(4000);
1325 l_list_of_rules1 VARCHAR2(4000);
1326 l_list_of_tve_id VARCHAR2(4000);
1327 l_row_not_found BOOLEAN := TRUE;
1328 l_tve_id NUMBER;
1329 l_rul_id NUMBER;
1330 l_tve_type OKC_TIMEVALUES.tve_type%type;
1331 l_datetime date;
1332 x_msg_count NUMBER;
1333 x_msg_data VARCHAR2(2000);
1334 l_sql_string varchar2(4000);
1335 l_rule_type OKC_RULES_V.rule_information_category%type;
1336 l_app_id number;
1337 l_rule_df_name varchar2(40);
1338 l_col_val_table OKC_TIME_UTIL_PUB.t_col_vals;
1339 l_no_of_cols number;
1340 l_tze_id number;
1341 x_task_id NUMBER;
1342 l_tze_name OKX_TIMEZONES_V.global_timezone_name%TYPE;
1343 l_resolved_until_date DATE;
1344 l_cutoff_date DATE := NULL;
1345 l_end_date DATE;
1346 l_start_date DATE;
1347 l_tve_id_limited NUMBER;
1348 l_limited_by_rec limited_by_csr%ROWTYPE;
1349 l_pending_further_resolving VARCHAR2(1) := 'X';
1350 l_over_further_resolving VARCHAR2(1) := 'X';
1351
1352 BEGIN
1353
1354 /*
1355 Call this procedure for extending lines and headers.
1356 NOTE: Extending header will not automatically resolve timevalues for lines. Has to be called for extending lines
1357 as well. p_chr_id and p_cle_id are mutually exclusive.
1358 */
1359
1360 l_end_date := nvl(p_end_date, to_date('12314000','mmddyyyy'));
1361 l_cutoff_date := p_start_date;
1362 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1363
1364 /* Get the application_id and get the rule definition names */
1365
1366 l_app_id := OKC_TIME_UTIL_PUB.get_app_id;
1367 if l_app_id is null then
1368 return;
1369 end if;
1370 l_rule_df_name := OKC_TIME_UTIL_PUB.get_rule_df_name;
1371 if l_rule_df_name is null then
1372 return;
1373 end if;
1374
1375 /*get all the rule types (e.g. NTN) from metadata which are related to timevalues.*/
1376 /*get all the rule types (e.g. NTN) from metadata which are related to tasks.*/
1377
1378 l_list_of_rules := OKC_TIME_UTIL_PUB.get_rule_defs_using_vs(l_app_id,l_rule_df_name,'OKC_TIMEVALUES');
1379 l_list_of_rules1 := OKC_TIME_UTIL_PUB.get_rule_defs_using_vs(l_app_id,l_rule_df_name,'OKC_TASK_RS');
1380
1381 /* For these rules, get their ids using the contract header (line) id. */
1382
1383
1384 l_sql_string := 'select r.id, r.rule_information_category ' ||
1385 'from okc_rules_b r, okc_rule_groups_b rg '||
1386 'where ((rg.dnz_chr_id = :p_chr_id and rg.cle_id IS NULL) or ' ||
1387 ' (rg.cle_id = :p_cle_id)) and ' ||
1388 ' r.rgp_id = rg.id ' ||
1389 'and r.rule_information_category in '|| l_list_of_rules ||
1390 'and r.rule_information_category in '|| l_list_of_rules1 ;
1391
1392 /* for extending lines, ensure that the resolved until date (if present) of the headers is met */
1393
1394 if p_cle_id is NOT NULL AND
1395 p_cle_id <> OKC_API.G_MISS_NUM Then
1396 OPEN resolved_until_csr(p_cle_id);
1397 FETCH resolved_until_csr INTO l_resolved_until_date;
1398 CLOSE resolved_until_csr;
1399 IF l_resolved_until_date is NOT NULL then
1400 if l_end_date > l_resolved_until_date then
1401 l_end_date := l_resolved_until_date;
1402 end if;
1403 end if;
1404 elsif p_chr_id is NOT NULL AND
1405 p_chr_id <> OKC_API.G_MISS_NUM Then
1406 l_resolved_until_date := add_months(sysdate,OKC_TIME_RES_PVT.RESOLVE_UNTIL_MONTHS);
1407 if l_end_date <= l_resolved_until_date then
1408 l_over_further_resolving := '1';
1409 else
1410 l_end_date := l_resolved_until_date;
1411 l_pending_further_resolving := '1';
1412 end if;
1413 end if;
1414 open rgp_csr for l_sql_string using p_chr_id, p_cle_id;
1415 loop
1416 FETCH rgp_csr into l_rul_id, l_rule_type;
1417 exit when rgp_csr%NOTFOUND;
1418
1419 /* Get all the timevalues associated with the rule. Currently there is only one tve_id per rule. May be extended in
1420 future. API is flexible to handle this */
1421
1422 l_list_of_tve_id := OKC_TIME_UTIL_PUB.get_tve_ids(l_app_id,l_rule_df_name,l_rule_type,'OKC_TIMEVALUES',
1423 l_rul_id);
1424 if l_list_of_tve_id is NULL Then
1425 goto next_row;
1426 end if;
1427 l_sql_string :=
1428 'select id, tve_id_limited, tve_type, datetime, tze_id ' ||
1429 'from okc_timevalues ' ||
1430 'where ((tve_type in ( ''TGD'',''TAV''))'||
1431 ' or (tve_type = ''CYL'' and interval_yn = ''N'')) ' ||
1432 'and id in '|| l_list_of_tve_id;
1433 open tve_id_csr for l_sql_string;
1434 loop
1435 fetch tve_id_csr into l_tve_id, l_tve_id_limited, l_tve_type, l_datetime, l_tze_id;
1436 exit when tve_id_csr%NOTFOUND;
1437 /* Absolute Time Value will have only one date */
1438
1439 IF l_tve_type = 'TAV' then
1440 if l_tze_id is NOT NULL and
1441 l_tze_id <> OKC_API.G_MISS_NUM then
1442 Get_Timezone(l_tze_id, l_tze_name, x_return_status);
1443 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1444 exit;
1445 end if;
1446 else
1447 l_tze_name := NULL;
1448 end if;
1449 Create_RTV_N_Tasks(
1450 x_return_status,
1451 p_api_version,
1452 p_init_msg_list,
1453 l_tve_id,
1454 l_datetime,
1455 l_cutoff_date,
1456 NULL,
1457 l_tze_id,
1458 l_tze_name);
1459 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1460 exit;
1461 end if;
1462
1463 /* For generic and recurring call the respective procedures to create resolved timevalues */
1464 ELSE
1465 l_start_date := p_start_date;
1466 if l_tve_id_limited is NOT NULL Then
1467 OPEN limited_by_csr(l_tve_id_limited);
1468 FETCH limited_by_csr into l_limited_by_rec;
1469 l_row_not_found := limited_by_csr%NOTFOUND;
1470 CLOSE limited_by_csr;
1471 IF (l_row_not_found) THEN
1472 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TVE_ID');
1473 x_return_status := OKC_API.G_RET_STS_ERROR;
1474 exit;
1475 END IF;
1476 if l_limited_by_rec.start_date > p_start_date Then
1477 l_start_date := l_limited_by_rec.start_date;
1478 end if;
1479 if l_limited_by_rec.end_date < l_end_date Then
1480 l_end_date := l_limited_by_rec.end_date;
1481 end if;
1482 end if;
1483 if l_start_date > l_end_date then
1484 exit;
1485 end if;
1486 l_cutoff_date := l_start_date;
1487 IF l_tve_type = 'TGD' then
1488 Res_TPG_Delimited ( x_return_status,p_api_version,p_init_msg_list,l_tve_id, l_start_date, l_end_date, l_cutoff_date);
1489 ELSE
1490 Res_Cycle ( x_return_status,p_api_version,p_init_msg_list,l_tve_id, l_tze_id, l_tze_name, l_start_date, l_end_date, l_cutoff_date);
1491 end if;
1492 end if;
1493 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1494 exit;
1495 end if;
1496 end loop;
1497 close tve_id_csr;
1498 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1499 exit;
1500 END IF;
1501 <<next_row>>
1502 null;
1503 end loop;
1504 if l_pending_further_resolving = '1' Then
1505 update okc_k_headers_b
1506 set resolved_until = l_resolved_until_date
1507 where id = p_chr_id;
1508 elsif l_over_further_resolving = '1' Then
1509 update okc_k_headers_b
1510 set resolved_until = NULL
1511 where id = p_chr_id;
1512 end if;
1513 l_pending_further_resolving := 'X';
1514 l_over_further_resolving := 'X';
1515 CLOSE rgp_csr;
1516 EXCEPTION
1517 WHEN OTHERS THEN
1518 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1519 p_msg_name => g_unexpected_error,
1520 p_token1 => g_sqlcode_token,
1521 p_token1_value => sqlcode,
1522 p_token2 => g_col_name_token,
1523 p_token2_value => 'CHR_ID',
1524 p_token3 => g_sqlerrm_token,
1525 p_token3_value => sqlerrm);
1526 -- notify caller of an UNEXPECTED error
1527 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1528 END Res_Time_Extnd_K;
1529
1530 PROCEDURE Res_Time_Termnt_K(
1531 p_chr_id IN NUMBER,
1532 p_cle_id IN NUMBER,
1533 p_end_date IN DATE,
1534 p_api_version IN NUMBER,
1535 p_init_msg_list IN VARCHAR2 ,
1536 x_return_status OUT NOCOPY VARCHAR2) IS
1537
1538 TYPE rgp_csr_type is REF CURSOR;
1539 rgp_csr rgp_csr_type;
1540
1541 TYPE tve_id_csr_type is REF CURSOR;
1542 tve_id_csr tve_id_csr_type;
1543
1544 CURSOR resolved_until_csr(p_chr_id IN NUMBER) is
1545 SELECT resolved_until from okc_k_headers_b h
1546 where h.id = p_chr_id;
1547
1548 l_list_of_rules VARCHAR2(4000);
1549 l_list_of_rules1 VARCHAR2(4000);
1550 l_list_of_tve_id VARCHAR2(4000);
1551 l_row_not_found BOOLEAN := TRUE;
1552 l_tve_id NUMBER;
1553 l_rul_id NUMBER;
1554 l_rtv_id NUMBER;
1555 l_tve_type OKC_TIMEVALUES.tve_type%type;
1556 l_sql_string varchar2(4000);
1557 l_rtvv_rec OKC_TIME_PUB.rtvv_rec_type;
1558 x_msg_count NUMBER;
1559 x_msg_data VARCHAR2(2000);
1560 l_app_id number;
1561 l_rule_type OKC_RULES_V.rule_information_category%type;
1562 l_rule_df_name varchar2(40);
1563 l_col_val_table OKC_TIME_UTIL_PUB.t_col_vals;
1564 l_no_of_cols number;
1565 l_resolved_until_date date;
1566
1567 BEGIN
1568
1569 /*
1570 Call this procedure for terminating lines and headers.
1571 NOTE: terminating header will not automatically delete resolved timevalues for lines.
1572 Has to be called for terminating lines as well. p_chr_id and p_cle_id are mutually exclusive.
1573 */
1574
1575 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1576
1577 /* Get the application_id and get the rule definition names */
1578
1579 l_app_id := OKC_TIME_UTIL_PUB.get_app_id;
1580 if l_app_id is null then
1581 return;
1582 end if;
1583
1584 l_rule_df_name := OKC_TIME_UTIL_PUB.get_rule_df_name;
1585 if l_rule_df_name is null then
1586 return;
1587 end if;
1588
1589 /*get all the rule types (e.g. NTN) from metadata which are related to timevalues.*/
1590 /*get all the rule types (e.g. NTN) from metadata which are related to tasks.*/
1591
1592 l_list_of_rules := OKC_TIME_UTIL_PUB.get_rule_defs_using_vs(l_app_id,l_rule_df_name,'OKC_TIMEVALUES');
1593 l_list_of_rules1 := OKC_TIME_UTIL_PUB.get_rule_defs_using_vs(l_app_id,l_rule_df_name,'OKC_TASK_RS');
1594
1595 /* For these rules, get their ids using the contract header (line) id. */
1596
1597 l_sql_string := 'select r.id, r.rule_information_category ' ||
1598 'from okc_rules_b r, okc_rule_groups_b rg '||
1599 'where ((rg.dnz_chr_id = :p_chr_id and rg.cle_id IS NULL) or ' ||
1600 ' (rg.cle_id = :p_cle_id)) and ' ||
1601 ' r.rgp_id = rg.id ' ||
1602 'and r.rule_information_category in '|| l_list_of_rules ||
1603 'and r.rule_information_category in '|| l_list_of_rules1 ;
1604 open rgp_csr for l_sql_string using p_chr_id, p_cle_id;
1605 loop
1606 FETCH rgp_csr into l_rul_id, l_rule_type;
1607 exit when rgp_csr%NOTFOUND;
1608
1609 /* Get all the timevalues associated with the rule. Currently there is only one tve_id per rule. May be extended in
1610 future. API is flexible to handle this */
1611
1612 l_list_of_tve_id := OKC_TIME_UTIL_PUB.get_tve_ids(l_app_id,l_rule_df_name,l_rule_type,'OKC_TIMEVALUES',
1613 l_rul_id);
1614 if l_list_of_tve_id is NULL Then
1615 goto next_row;
1616 end if;
1617
1618 /* Get all resolved timevalues */
1619
1620 l_sql_string :=
1621 'select rtv.id rtv_id ' ||
1622 'from okc_timevalues tve, okc_resolved_timevalues rtv ' ||
1623 'where rtv.tve_id = tve.id ' ||
1624 'and rtv.datetime >= :p_end_date ' ||
1625 'and tve.id in '|| l_list_of_tve_id ;
1626 open tve_id_csr for l_sql_string using p_end_date;
1627 loop
1628 fetch tve_id_csr into l_rtv_id;
1629 exit when tve_id_csr%NOTFOUND;
1630 l_rtvv_rec.id := l_rtv_id;
1631 OKC_TASK_PUB.DELETE_TASK(
1632 p_api_version,
1633 p_init_msg_list,
1634 'F',
1635 NULL,
1636 l_rtv_id,
1637 x_return_status,
1638 x_msg_count,
1639 x_msg_data);
1640 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1641 exit;
1642 end if;
1643 OKC_TIME_PUB.DELETE_RESOLVED_TIMEVALUES(
1644 p_api_version,
1645 p_init_msg_list,
1646 x_return_status,
1647 x_msg_count,
1648 x_msg_data,
1649 l_rtvv_rec);
1650 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1651 exit;
1652 end if;
1653 end loop;
1654 close tve_id_csr;
1655 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1656 exit;
1657 END IF;
1658 <<next_row>>
1659 null;
1660 end loop;
1661 CLOSE rgp_csr;
1662
1663 /* For contract headers, check if the contract has a resolved until date. If it exists, then check if this date is >
1664 termination date (p_end_date). If yes, then reset the resolved until date as there is no need for any further
1665 resolving of timevalues */
1666
1667 if p_chr_id is NOT NULL AND
1668 p_chr_id <> OKC_API.G_MISS_NUM Then
1669 OPEN resolved_until_csr(p_chr_id);
1670 FETCH resolved_until_csr INTO l_resolved_until_date;
1671 CLOSE resolved_until_csr;
1672 IF l_resolved_until_date is NOT NULL then
1673 if p_end_date <= l_resolved_until_date then
1674 update okc_k_headers_b
1675 set resolved_until = NULL
1676 where id = p_chr_id;
1677 end if;
1678 end if;
1679 end if;
1680 EXCEPTION
1681 WHEN OTHERS THEN
1682 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1683 p_msg_name => g_unexpected_error,
1684 p_token1 => g_sqlcode_token,
1685 p_token1_value => sqlcode,
1686 p_token2 => g_col_name_token,
1687 p_token2_value => 'CHR_ID',
1688 p_token3 => g_sqlerrm_token,
1689 p_token3_value => sqlerrm);
1690 -- notify caller of an UNEXPECTED error
1691 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1692 END Res_Time_Termnt_K;
1693
1694 FUNCTION Check_Res_Time_N_tasks(
1695 p_tve_id IN NUMBER,
1696 p_date IN DATE)
1697 return BOOLEAN IS
1698 l_dummy VARCHAR2(1) ;
1699 l_rowfound BOOLEAN := TRUE;
1700
1701 -- Replaced name with seeded id to avoid transaltion issue - Bug 1683539
1702 -- Reed OKCSCHRULE - Contract Schedule Rule - Bug 1683539
1703 CURSOR Check_RTV_Tasks_csr (p_tve_id IN NUMBER, p_date IN DATE) IS
1704 SELECT '1' from OKC_RESOLVED_TIMEVALUES
1705 WHERE tve_id = p_tve_id AND
1706 datetime <= p_date
1707 UNION ALL
1708 SELECT '1' from OKC_RESOLVED_TIMEVALUES rtv, JTF_TASKS_B t, JTF_TASK_TYPES_VL tt
1709 WHERE SOURCE_OBJECT_ID = rtv.id AND
1710 t.TASK_TYPE_ID = tt.TASK_TYPE_ID AND
1711 tve_id = p_tve_id AND
1712 tt.task_type_id = 23 AND
1713 --tt.name = 'OKCSCHRULE' AND
1714 ACTUAL_START_DATE <= p_date;
1715 BEGIN
1716 OPEN Check_RTV_Tasks_csr(p_tve_id, p_date);
1717 FETCH Check_RTV_Tasks_csr into l_dummy;
1718 l_rowfound := Check_RTV_Tasks_csr%FOUND;
1719 CLOSE Check_RTV_Tasks_csr;
1720 return l_rowfound;
1721
1722 END Check_Res_Time_N_tasks;
1723
1724 PROCEDURE Delete_Res_Time_N_Tasks(
1725 p_tve_id IN NUMBER,
1726 p_date IN DATE,
1727 p_api_version IN NUMBER,
1728 p_init_msg_list IN VARCHAR2 ,
1729 x_return_status OUT NOCOPY VARCHAR2) IS
1730
1731 CURSOR rtv_csr(p_tve_id IN NUMBER, p_date IN DATE) is
1732 select rtv.id rtv_id
1733 from okc_timevalues tve, okc_resolved_timevalues rtv
1734 where rtv.tve_id = tve.id
1735 and rtv.datetime >= p_date
1736 and tve.id = p_tve_id ;
1737
1738 l_rtv_rec rtv_csr%ROWTYPE;
1739 l_rtvv_rec OKC_TIME_PUB.rtvv_rec_type;
1740 x_msg_count NUMBER;
1741 x_msg_data VARCHAR2(2000);
1742 BEGIN
1743 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1744 open rtv_csr (p_tve_id, p_date);
1745 loop
1746 fetch rtv_csr into l_rtv_rec;
1747 exit when rtv_csr%NOTFOUND;
1748 l_rtvv_rec.id := l_rtv_rec.rtv_id;
1749 OKC_TASK_PUB.DELETE_TASK(
1750 p_api_version,
1751 p_init_msg_list,
1752 'F',
1753 NULL,
1754 l_rtv_rec.rtv_id,
1755 x_return_status,
1756 x_msg_count,
1757 x_msg_data);
1758 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1759 exit;
1760 end if;
1761 OKC_TIME_PUB.DELETE_RESOLVED_TIMEVALUES(
1762 p_api_version,
1763 p_init_msg_list,
1764 x_return_status,
1765 x_msg_count,
1766 x_msg_data,
1767 l_rtvv_rec);
1768 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1769 exit;
1770 end if;
1771 end loop;
1772 close rtv_csr;
1773 EXCEPTION
1774 WHEN OTHERS THEN
1775 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1776 p_msg_name => g_unexpected_error,
1777 p_token1 => g_sqlcode_token,
1778 p_token1_value => sqlcode,
1779 p_token2 => g_col_name_token,
1780 p_token2_value => 'RTV_ID',
1781 p_token3 => g_sqlerrm_token,
1782 p_token3_value => sqlerrm);
1783 -- notify caller of an UNEXPECTED error
1784 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1785 END Delete_Res_Time_N_Tasks;
1786
1787 PROCEDURE Create_Res_Time_N_Tasks(
1788 p_tve_id IN NUMBER,
1789 p_start_date IN DATE,
1790 p_end_date IN DATE ,
1791 p_api_version IN NUMBER,
1792 p_init_msg_list IN VARCHAR2 ,
1793 x_return_status OUT NOCOPY VARCHAR2) IS
1794
1795 CURSOR tve_id_csr(p_tve_id IN NUMBER) is
1796 select id, tve_type, datetime, tze_id
1797 from okc_timevalues
1798 where
1799 id = p_tve_id;
1800
1801 l_tve_rec tve_id_csr%ROWTYPE;
1802 x_msg_count NUMBER;
1803 x_msg_data VARCHAR2(2000);
1804 x_task_id NUMBER;
1805 l_tze_name OKX_TIMEZONES_V.global_timezone_name%TYPE;
1806 l_row_not_found BOOLEAN := TRUE;
1807 l_cutoff_date DATE := NULL;
1808 BEGIN
1809 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1810 open tve_id_csr(p_tve_id);
1811 fetch tve_id_csr into l_tve_rec;
1812 l_row_not_found := tve_id_csr%NOTFOUND;
1813 close tve_id_csr;
1814 if l_row_not_found then
1815 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TVE_ID');
1816 x_return_status := OKC_API.G_RET_STS_ERROR;
1817 return;
1818 end if;
1819 IF l_tve_rec.tve_type = 'TAV' then
1820 if l_tve_rec.tze_id is NOT NULL and
1821 l_tve_rec.tze_id <> OKC_API.G_MISS_NUM then
1822 Get_Timezone(l_tve_rec.tze_id, l_tze_name, x_return_status);
1823 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1824 return;
1825 end if;
1826 else
1827 l_tze_name := NULL;
1828 end if;
1829 Create_RTV_N_Tasks(
1830 x_return_status,
1831 p_api_version,
1832 p_init_msg_list,
1833 l_tve_rec.id,
1834 l_tve_rec.datetime,
1835 l_cutoff_date,
1836 NULL,
1837 l_tve_rec.tze_id,
1838 l_tze_name);
1839 if x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1840 return;
1841 end if;
1842 ELSIF l_tve_rec.tve_type = 'TGD' then
1843 Res_TPG_Delimited ( x_return_status,p_api_version,p_init_msg_list,l_tve_rec.id, p_start_date, p_end_date, l_cutoff_date);
1844 ELSIF l_tve_rec.tve_type = 'CYL' then
1845 Res_Cycle ( x_return_status,p_api_version,p_init_msg_list,l_tve_rec.id, l_tve_rec.tze_id, l_tze_name, p_start_date, p_end_date, l_cutoff_date);
1846 end if;
1847 EXCEPTION
1848 WHEN OTHERS THEN
1849 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1850 p_msg_name => g_unexpected_error,
1851 p_token1 => g_sqlcode_token,
1852 p_token1_value => sqlcode,
1853 p_token2 => g_col_name_token,
1854 p_token2_value => 'TVE_ID',
1855 p_token3 => g_sqlerrm_token,
1856 p_token3_value => sqlerrm);
1857 -- notify caller of an UNEXPECTED error
1858 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1859 END Create_Res_Time_N_Tasks;
1860
1861 PROCEDURE Batch_Resolve_Time_N_Tasks IS
1862 CURSOR c_chr IS
1863 SELECT contract_number, id, resolved_until from OKC_K_HEADERS_B
1864 where resolved_until < add_months(sysdate, OKC_TIME_RES_PVT.PICKUP_UPTO_MONTHS)
1865 and resolved_until >= sysdate;
1866 x_return_status VARCHAR2(1);
1867 l_init_msg_list VARCHAR2(1) := 'F';
1868 l_api_version NUMBER := 1.0;
1869 l_resolved_until_date DATE;
1870 BEGIN
1871
1872 /* This is a nightly concurrent job to trigger re-resoving all
1873 contracts which need re-resolving as their resolved until_date
1874 falls in the PICKUP_UPTO_MONTHS window.
1875 If the end date is less than the derived resolved until date,
1876 reset the resolved until dates for the contract
1877 to null and will never be picked up.
1878 */
1879
1880 -- l_resolved_until_date := add_months(sysdate, OKC_TIME_RES_PVT.RESOLVE_UNTIL_MONTHS);
1881 for chr_rec in c_chr LOOP
1882 l_resolved_until_date := add_months(chr_rec.resolved_until, OKC_TIME_RES_PVT.RESOLVE_UNTIL_MONTHS);
1883 Res_Time_K(p_chr_id => chr_rec.id,
1884 p_resolved_until_date => l_resolved_until_date,
1885 p_cutoff_date => chr_rec.resolved_until,
1886 p_api_version => l_api_version,
1887 p_init_msg_list => l_init_msg_list,
1888 x_return_status => x_return_status);
1889 end loop;
1890 END Batch_Resolve_Time_N_Tasks;
1891
1892 PROCEDURE time_resolver(errbuf OUT NOCOPY VARCHAR2,
1893 retcode OUT NOCOPY VARCHAR2) IS
1894
1895 l_api_name CONSTANT VARCHAR2(30) := 'time_resolver';
1896 l_api_version CONSTANT VARCHAR2(30) := 1.0;
1897 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1898 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1899 l_msg_count NUMBER;
1900 l_msg_data VARCHAR2(1000);
1901 l_init_msg_list VARCHAR2(3) := 'F';
1902 E_Resource_Busy EXCEPTION;
1903 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1904
1905 BEGIN
1906 -- call start_activity to create savepoint, check comptability
1907 -- and initialize message list
1908 l_return_status := OKC_API.START_ACTIVITY(l_api_name
1909 ,l_init_msg_list
1910 ,'_PROCESS'
1911 ,x_return_status
1912 );
1913 --Initialize the return code
1914 retcode := 0;
1915
1916 OKC_TIME_RES_PVT.Batch_Resolve_Time_N_Tasks;
1917
1918 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1919 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1920 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1921 RAISE OKC_API.G_EXCEPTION_ERROR;
1922 -- ELSIF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1923 -- commit;
1924 END IF;
1925
1926 OKC_API.END_ACTIVITY(l_msg_count, l_msg_data);
1927
1928 EXCEPTION
1929 WHEN E_Resource_Busy THEN
1930 l_return_status := okc_api.g_ret_sts_error;
1931 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1932 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1933 retcode := 2;
1934 errbuf := substr(sqlerrm,1,200);
1935 l_return_status := OKC_API.HANDLE_EXCEPTIONS
1936 (l_api_name,
1937 G_PKG_NAME,
1938 'OKC_API.G_RET_STS_ERROR',
1939 l_msg_count,
1940 l_msg_data,
1941 '_COMPLEX');
1942 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1943 retcode := 2;
1944 errbuf := substr(sqlerrm,1,200);
1945 l_return_status := OKC_API.HANDLE_EXCEPTIONS
1946 (l_api_name,
1947 G_PKG_NAME,
1948 'OKC_API.G_RET_STS_UNEXP_ERROR',
1949 l_msg_count,
1950 l_msg_data,
1951 '_COMPLEX');
1952 WHEN OTHERS THEN
1953 retcode := 2;
1954 errbuf := substr(sqlerrm,1,200);
1955 l_return_status := OKC_API.HANDLE_EXCEPTIONS
1956 (l_api_name,
1957 G_PKG_NAME,
1958 'OTHERS',
1959 l_msg_count,
1960 l_msg_data,
1961 '_COMPLEX');
1962 END time_resolver;
1963
1964 END OKC_TIME_RES_PVT;
[Home] [Help]