[Home] [Help]
PACKAGE BODY: APPS.OKS_PM_PROGRAMS_PVT
Source
1 PACKAGE BODY OKS_PM_PROGRAMS_PVT AS
2 /* $Header: OKSRPMPB.pls 120.13 2007/12/24 10:21:51 rriyer ship $ */
3
4 PROCEDURE GENERATE_SCHEDULE(
5 p_api_version IN NUMBER,
6 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
7 x_return_status OUT NOCOPY VARCHAR2,
8 x_msg_count OUT NOCOPY NUMBER,
9 x_msg_data OUT NOCOPY VARCHAR2,
10 p_periods IN NUMBER,
11 p_start_date IN DATE,
12 p_end_date IN DATE,
13 p_duration IN NUMBER,
14 p_period IN VARCHAR2,
15 p_first_sch_date IN DATE,
16 x_periods OUT NOCOPY NUMBER,
17 x_last_date OUT NOCOPY DATE,
18 x_pms_tbl OUT NOCOPY pms_tbl_type) IS
19
20 l_api_version CONSTANT NUMBER := 1.0;
21 l_init_msg_list CONSTANT VARCHAR2(1):= 'F';
22 l_return_status VARCHAR2(1);
23 l_msg_count NUMBER;
24 l_msg_data VARCHAR2(2000):=null;
25 l_msg_index_out Number;
26 l_api_name CONSTANT VARCHAR2(30) := 'generate schedule';
27 l_schedule_date DATE;
28 i NUMBER;
29 l_period_ctr NUMBER := p_periods;
30 -----------------------------------------
31 BEGIN
32 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
33 p_init_msg_list,
34 '_PVT',
35 x_return_status);
36 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
37 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
38 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
39 RAISE OKC_API.G_EXCEPTION_ERROR;
40 END IF;
41
42
43 i:=1;
44 -- l_schedule_date := nvl(p_first_sch_date,okc_time_util_pub.get_enddate(p_start_date,p_period,p_duration));
45 l_schedule_date := p_first_sch_date;
46
47
48 if trunc(l_schedule_date) >= trunc(p_end_date) then
49 l_schedule_date := p_end_date;
50 end if;
51
52
53 x_pms_tbl(i).schedule_date := l_schedule_date;
54 x_last_date := l_schedule_date;
55 x_periods := 1;
56 if l_period_ctr is not null then
57 l_period_ctr := l_period_ctr - 1;
58 end if;
59
60 IF trunc(l_schedule_date) <> trunc(p_end_date) THEN
61
62 LOOP
63
64 l_schedule_date := okc_time_util_pub.get_enddate(l_schedule_date+1,p_period,p_duration);
65
66 if trunc(l_schedule_date) > trunc(p_end_date) OR
67 (l_period_ctr is not null AND l_period_ctr = 0 ) then
68 exit;
69 else
70 x_periods := x_periods + 1;
71 if l_period_ctr is not null then
72 l_period_ctr := l_period_ctr - 1;
73 end if;
74 i:=i+1;
75 x_pms_tbl(i).schedule_date :=l_schedule_date;
76 x_last_date := l_schedule_date;
77 end if;
78
79 END LOOP;
80
81 END IF;
82
83 IF NOT l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
84 RAISE G_EXCEPTION_HALT_VALIDATION;
85 END IF;
86
87 x_return_status := l_return_status;
88
89 EXCEPTION
90 WHEN G_EXCEPTION_HALT_VALIDATION THEN
91 x_return_status := l_return_status ;
92
93
94 WHEN OTHERS THEN
95 OKC_API.SET_MESSAGE(
96 p_app_name => g_app_name,
97 p_msg_name => g_unexpected_error,
98 p_token1 => g_sqlcode_token,
99 p_token1_value => sqlcode,
100 p_token2 => g_sqlerrm_token,
101 p_token2_value => sqlerrm);
102 -- notify caller of an error as UNEXPETED error
103 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
104 x_msg_count :=l_msg_count;
105 END generate_schedule;
106
107
108
109
110 /*--for upgrade from Phase I
111 PROCEDURE update_pmp_rule_id(p_api_version IN NUMBER,
112 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
113 x_return_status OUT NOCOPY VARCHAR2,
114 x_msg_count OUT NOCOPY NUMBER,
115 x_msg_data OUT NOCOPY VARCHAR2)
116 IS
117 CURSOR cu_pm_schedule IS
118 select
119 pms.id,
120 to_number(pml.object1_id1) pmp_rule_id
121 from
122 okc_rules_b pml,
123 oks_pm_schedules pms
124 where
125 rule_information_category ='PML'
126 and pml.id =pms.rule_id;
127 l_pms_tbl_in oks_pms_pvt.oks_pm_schedules_v_tbl_type;
128 l_pms_tbl_out oks_pms_pvt.oks_pm_schedules_v_tbl_type;
129 l_api_version CONSTANT NUMBER := 1;
130 l_api_name CONSTANT VARCHAR2(30) := 'V_POPULATE_ACTIVITIES';
131
132 l_return_status VARCHAR2(1);
133 l_msg_count NUMBER;
134 l_msg_data VARCHAR2(2000);
135 l_init_msg_list CONSTANT VARCHAR2(1):= 'F';
136
137 ctr NUMBER;
138 BEGIN
139 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
140 G_PKG_NAME,
141 p_init_msg_list,
142 l_api_version,
143 p_api_version,
144 '_PVT',
145 x_return_status);
146 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
147 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
148 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
149 RAISE OKC_API.G_EXCEPTION_ERROR;
150 END IF;
151
152 ctr :=1;
153 FOR cr_pm_schedule IN cu_pm_schedule LOOP
154 l_pms_tbl_in(ctr).id := cr_pm_schedule.id;
155 l_pms_tbl_in(ctr).pmp_rule_id := cr_pm_schedule.pmp_rule_id;
156 ctr := ctr+1;
157 END LOOP;
158 OKS_PMS_PVT.update_row(
159 p_api_version => l_api_version,
160 p_init_msg_list => l_init_msg_list,
161 x_return_status => l_return_status ,
162 x_msg_count => l_msg_count ,
163 x_msg_data => l_msg_data ,
164 p_oks_pm_schedules_v_tbl => l_pms_tbl_in,
165 x_oks_pm_schedules_v_tbl =>l_pms_tbl_out);
166 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
167 EXCEPTION
168 WHEN OKC_API.G_EXCEPTION_ERROR THEN
169 ROLLBACK ;
170 x_return_status := OKC_API.HANDLE_EXCEPTIONS
171 (
172 l_api_name,
173 G_PKG_NAME,
174 'OKC_API.G_RET_STS_ERROR',
175 x_msg_count,
176 x_msg_data,
177 '_PVT'
178 );
179
180 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
181 ROLLBACK ;
182 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
183 (
184 l_api_name,
185 G_PKG_NAME,
186 'OKC_API.G_RET_STS_UNEXP_ERROR',
187 x_msg_count,
188 x_msg_data,
189 '_PVT'
190 );
191 WHEN OTHERS THEN
192 ROLLBACK ;
193 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
194 (
195 l_api_name,
196 G_PKG_NAME,
197 'OTHERS',
198 x_msg_count,
199 x_msg_data,
200 '_PVT'
201 );
202 END update_pmp_rule_id;
203 */
204
205 PROCEDURE CREATE_PM_PROGRAM_SCHEDULE
206 (p_api_version IN NUMBER,
207 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
208 x_return_status OUT NOCOPY VARCHAR2,
209 x_msg_count OUT NOCOPY NUMBER,
210 x_msg_data OUT NOCOPY VARCHAR2,
211 p_template_cle_id IN NUMBER,
212 p_cle_id IN NUMBER,
213 p_cov_start_date IN DATE,
214 p_cov_end_date IN DATE) --instantiated line id)
215 IS
216 --CK RUL
217
218
219 CURSOR cu_line IS
220 SELECT
221 id,object_version_number,dnz_chr_id
222 from oks_k_lines_b
223 where cle_id=p_cle_id;
224 cr_line cu_line%ROWTYPE;
225 CURSOR cu_activities IS
226 SELECT
227 ID,
228 ACTIVITY_ID,
229 SELECT_YN,
230 CONF_REQ_YN,
231 SCH_EXISTS_YN,
232 --02/13 PROGRAM_ID,
233 CLE_ID
234 DNZ_CHR_ID
235 FROM
236 OKS_PM_ACTIVITIES_V
237 WHERE cle_id= p_template_cle_id;
238 cr_activities cu_activities%ROWTYPE;
239
240 CURSOR cu_prog_stream_levels IS
241 SELECT
242 SEQUENCE_NUMBER ,
243 NUMBER_OF_OCCURENCES ,
244 START_DATE ,
245 END_DATE ,
246 FREQUENCY ,
247 FREQUENCY_UOM ,
248 OFFSET_DURATION ,
249 OFFSET_UOM,
250 AUTOSCHEDULE_YN,
251 --02/13 PROGRAM_ID ,
252 ACTIVITY_LINE_ID,
253 CLE_ID
254 DNZ_CHR_ID
255 FROM
256 oks_pm_stream_levels_v
257 WHERE cle_id = p_template_cle_id
258 AND ACTIVITY_LINE_ID is null
259 ORDER by SEQUENCE_NUMBER;
260 cr_prog_stream_levels cu_prog_stream_levels%ROWTYPE;
261
262 CURSOR cu_act_stream_levels(cp_activity_line_id IN NUMBER) IS
263 SELECT
264 SEQUENCE_NUMBER ,
265 NUMBER_OF_OCCURENCES ,
266 START_DATE ,
267 END_DATE ,
268 FREQUENCY ,
269 FREQUENCY_UOM ,
270 OFFSET_DURATION ,
271 OFFSET_UOM,
272 AUTOSCHEDULE_YN,
273 --02/13 PROGRAM_ID ,
274 ACTIVITY_LINE_ID,
275 CLE_ID
276 DNZ_CHR_ID
277 FROM
278 oks_pm_stream_levels_v
279 WHERE cle_id = p_template_cle_id
280 AND ACTIVITY_LINE_ID = cp_ACTIVITY_LINE_ID
281 ORDER by SEQUENCE_NUMBER;
282 cr_act_stream_levels cu_act_stream_levels%ROWTYPE;
283 -- Added part of 12.0 to validate the PM program exist before generating schedule
284 CURSOR CU_CHECK_PM_PROGRAM_EXIST IS
285 SELECT pm_program_id
286 FROM OKS_K_LINES_B
287 WHERE CLE_ID = p_template_cle_id;
288 CR_CHECK_PM_PROGRAM_EXIST CU_CHECK_PM_PROGRAM_EXIST%ROWTYPE;
289
290 -- Added by jvorugan for Bug:5080930
291 cursor get_max_seq_no is
292 select max(to_number(SEQUENCE_NUMBER))
293 from oks_pm_stream_levels_V
294 where cle_id =p_template_cle_id
295 and ACTIVITY_LINE_ID is null;
296
297 cursor get_max_act_seq_no(cp_activity_line_id IN NUMBER) is
298 select max(to_number(SEQUENCE_NUMBER))
299 from oks_pm_stream_levels_V
300 where cle_id = p_template_cle_id
301 and ACTIVITY_LINE_ID = cp_ACTIVITY_LINE_ID;
302
303 cursor check_renew(p_target_chr_id NUMBER)is
304 SELECT 'Y'
305 FROM okc_operation_instances op,
306 okc_class_operations cls,
307 okc_subclasses_b sl,
308 okc_operation_lines ol
309 WHERE ol.subject_chr_id = p_target_chr_id
310 And op.id = ol.oie_id
311 AND op.cop_id = cls.id
312 And cls.cls_code = sl.cls_code
313 And sl.code = 'SERVICE'
314 And cls.opn_code in ('RENEWAL')
315 AND ROWNUM=1;
316
317 --End by Jvorugan for bug:5080930
318 --------------------------------------------------------
319 l_pms_tbl pms_tbl_type;
320 l_row_cnt NUMBER;
321 l_api_version CONSTANT NUMBER := 1.0;
322 l_init_msg_list CONSTANT VARCHAR2(1):= 'F';
323 l_return_status VARCHAR2(1) := 'S';
324 l_msg_count NUMBER;
325 l_msg_data VARCHAR2(2000):=null;
326 l_duration number;
327 l_timeunit varchar2(30);
328 --
329 l_pmlrulvrec_Out oks_pml_pvt.pmlv_rec_type;
330 l_pmarulv_tbl_out oks_pma_pvt.pmav_tbl_type;
331 l_pmschvtbl_In OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
332 l_pmschvtbl_Out OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
333 --
334 l_pmarulvrec_Out oks_pma_pvt.pmav_rec_type;
335 l_pmarulvrec_In oks_pma_pvt.pmav_rec_type;
336 l_pmarulv_tbl oks_pma_pvt.pmav_tbl_type;
337
338
339 l_Rule_Id NUMBER;
340 l_pmlrulv_tbl oks_pml_pvt.pmlv_tbl_type;
341 l_pmlrulv_tbl_out oks_pml_pvt.pmlv_tbl_type;
342 l_klnv_tbl_in oks_kln_pvt.klnv_tbl_type;
343 l_klnv_tbl_out oks_kln_pvt.klnv_tbl_type;
344 l_sort_ret_status VARCHAR2(1) := 'S';
345 x_sch_ret_status VARCHAR2(1) := 'S';
346 x_periods NUMBER;
347 x_pms_tbl pms_tbl_type;
348 l_start_date DATE;
349 x_last_date DATE;
350 l_first_sch_date DATE;
351 l_sch_end_date DATE;
352 l_pmarule_id number := null;
353 l_pml_lastpmarule_id number := null;
354 l_tmppmarule_id number := null;
355 pma_ctr NUMBER ;
356 pml_ctr NUMBER;
357 l_chr_id NUMBER;
358 l_line_id NUMBER;
359 l_obj_version_number NUMBER;
360 l_seq_max_length NUMBER;
361 l_seq_act_max_length NUMBER;
362 l_check_renew varchar2(1):='N';
363 l_periods NUMBER;
364 BEGIN
365 IF (G_DEBUG_ENABLED = 'Y') THEN
366 okc_debug.Set_Indentation('Create_PM_Program_Schedule');
367 okc_debug.log('Entered Create_PM_Program_Schedule', 3);
368 END IF;
369
370 OPEN CU_CHECK_PM_PROGRAM_EXIST;
371 FETCH CU_CHECK_PM_PROGRAM_EXIST INTO CR_CHECK_PM_PROGRAM_EXIST;
372 IF CU_CHECK_PM_PROGRAM_EXIST%NOTFOUND then
373 raise G_EXCEPTION_HALT_VALIDATION;
374 end if;
375
376 OPEN CU_LINE;
377 LOOP
378 FETCH CU_LINE INTO CR_LINE;
379 EXIT WHEN CU_LINE%NOTFOUND;
380 l_line_id := cr_line.id;
381 l_chr_id := cr_line.dnz_chr_id;
382 l_obj_version_number := cr_line.object_version_number;
383 END LOOP;
384 CLOSE CU_LINE;
385
386 -- Added by Jvorugan for Bug:5080930
387 open check_renew(l_chr_id);
388 fetch check_renew into l_check_renew;
389 Close check_renew;
390
391 IF l_check_renew = 'Y' THEN
392 open get_max_seq_no;
393 fetch get_max_seq_no into l_seq_max_length;
394 close get_max_seq_no;
395 END IF;
396 -- End of changes by Jvorugan for Bug:5080930
397
398 l_start_date := p_cov_start_date;
399 --CK RUL Create program stream levels and schedules
400 pml_ctr :=1;
401 FOR cr_prog_stream_levels IN cu_prog_stream_levels LOOP
402 l_pmlrulv_tbl(pml_ctr).SEQUENCE_NUMBER := cr_prog_stream_levels.SEQUENCE_NUMBER;
403 l_pmlrulv_tbl(pml_ctr).NUMBER_OF_OCCURENCES := cr_prog_stream_levels.NUMBER_OF_OCCURENCES;
404 l_pmlrulv_tbl(pml_ctr).START_DATE := cr_prog_stream_levels.START_DATE;
405 l_pmlrulv_tbl(pml_ctr).END_DATE := cr_prog_stream_levels.END_DATE;
406 l_pmlrulv_tbl(pml_ctr).FREQUENCY := cr_prog_stream_levels.FREQUENCY;
407 l_pmlrulv_tbl(pml_ctr).FREQUENCY_UOM := cr_prog_stream_levels.FREQUENCY_UOM;
408 l_pmlrulv_tbl(pml_ctr).OFFSET_DURATION := cr_prog_stream_levels.OFFSET_DURATION;
409 l_pmlrulv_tbl(pml_ctr).OFFSET_UOM := cr_prog_stream_levels.OFFSET_UOM;
410 l_pmlrulv_tbl(pml_ctr).AUTOSCHEDULE_YN := cr_prog_stream_levels.AUTOSCHEDULE_YN;
411 --02/13 l_pmlrulv_tbl(pml_ctr).PROGRAM_ID := cr_prog_stream_levels.PROGRAM_ID;
412 l_pmlrulv_tbl(pml_ctr).CLE_ID := p_cle_id;
413 l_pmlrulv_tbl(pml_ctr).DNZ_CHR_ID := l_chr_id;
414 --CK 08/15 pml_ctr :=pml_ctr+1;
415 -- CK RUL
416 --CK RUL l_start_date := p_cov_start_date;
417 l_pmlrulv_tbl(pml_ctr).START_DATE := l_start_date;
418
419
420
421 l_first_sch_date := nvl(okc_time_util_pub.get_enddate(l_start_date,
422 l_pmlrulv_tbl(pml_ctr).offset_uom,
423 l_pmlrulv_tbl(pml_ctr).offset_duration) + 1, l_start_date);
424 l_duration := NULL;
425 l_timeunit := NULL;
426
427 --The following if handles the case where the template offset may give a first schedule date beyond coverage end date
428 --In such case, the new offset and duration is calculated and first schedule date is coverage end date
429
430 IF ((l_pmlrulv_tbl(pml_ctr).offset_uom IS NOT NULL) and
431 (l_pmlrulv_tbl(pml_ctr).offset_duration IS NOT NULL) and
432 (trunc(l_first_sch_date) >= trunc(p_cov_end_date))) THEN
433
434 l_first_sch_date := trunc(p_cov_end_date);
435
436 okc_time_util_pub.get_duration(
437 p_start_date => l_start_date,
438 p_end_date => l_first_sch_date - 1,
439 x_duration => l_duration,
440 x_timeunit => l_timeunit,
441 x_return_status => l_return_status);
442
443 IF NOT l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
444 RAISE G_EXCEPTION_HALT_VALIDATION;
445 END IF;
446
447 END IF;
448 /* This handles the following scenario -In the else, if the number of occurences (period) for
449 the last stream level in template may be null*/
450 IF to_number(l_pmlrulv_tbl(pml_ctr).number_of_occurences) IS NOT NULL THEN
451
452 l_sch_end_date := okc_time_util_pub.get_enddate(l_first_sch_date,
453 l_pmlrulv_tbl(pml_ctr).frequency_uom,
454 to_number(l_pmlrulv_tbl(pml_ctr).frequency)*
455 to_number(l_pmlrulv_tbl(pml_ctr).number_of_occurences));
456
457 IF trunc(l_sch_end_date) >= trunc(p_cov_end_date) then
458 l_sch_end_date := trunc(p_cov_end_date);
459 end if;
460
461 ELSE
462 l_sch_end_date := trunc(p_cov_end_date);
463 END IF;
464 l_pmlrulv_tbl(pml_ctr).END_DATE := l_sch_end_date;
465
466 -- Added by Jvorugan for Bug:5080930
467 l_periods := to_number(l_pmlrulv_tbl(pml_ctr).NUMBER_OF_OCCURENCES);
468 IF l_check_renew ='Y'
469 THEN
470 IF to_number(l_pmlrulv_tbl(pml_ctr).SEQUENCE_NUMBER) = nvl(l_seq_max_length,0) THEN
471 l_periods :=NULL;
472 END IF;
473 END IF;
474 -- End of changes by Jvorugan
475 x_pms_tbl.DELETE;
476 GENERATE_SCHEDULE(
477 p_api_version => l_api_version,
478 p_init_msg_list => l_init_msg_list,
479 x_return_status => x_sch_ret_status,
480 x_msg_count => l_msg_count,
481 x_msg_data => l_msg_data,
482 p_periods => l_periods, --Nodified by Jvorugan for Bug:5080930 to_number(l_pmlrulv_tbl(pml_ctr).NUMBER_OF_OCCURENCES),
483 p_start_date => p_cov_start_date,
484 p_end_date => p_cov_end_date,
485 p_duration => to_number(l_pmlrulv_tbl(pml_ctr).FREQUENCY),
486 p_period => l_pmlrulv_tbl(pml_ctr).FREQUENCY_UOM,
487 p_first_sch_date => l_first_sch_date,
488 x_periods => x_periods,
489 x_last_date => x_last_date,
490 x_pms_tbl => x_pms_tbl);
491
492 IF NOT x_sch_ret_status = OKC_API.G_RET_STS_SUCCESS THEN
493 RAISE G_EXCEPTION_HALT_VALIDATION;
494 END IF;
495 l_pmlrulv_tbl(pml_ctr).NUMBER_OF_OCCURENCES := x_periods;
496 -- Added by Jvorugan for Bug:5191017
497 IF l_check_renew ='Y'
498 THEN
499 IF to_number(l_pmlrulv_tbl(pml_ctr).number_of_occurences) IS NOT NULL THEN
500
501 l_sch_end_date := okc_time_util_pub.get_enddate(l_first_sch_date,
502 l_pmlrulv_tbl(pml_ctr).frequency_uom,
503 to_number(l_pmlrulv_tbl(pml_ctr).frequency)*
504 to_number(l_pmlrulv_tbl(pml_ctr).number_of_occurences));
505
506 if trunc(l_sch_end_date) >= trunc(p_cov_end_date) then
507 l_sch_end_date := trunc(p_cov_end_date);
508 end if;
509
510 ELSE
511 l_sch_end_date := trunc(p_cov_end_date);
512 END IF;
513 l_pmlrulv_tbl(pml_ctr).END_DATE := l_sch_end_date;
514 END IF;
515 -- End of changes by Jvorugan
516
517
518 --Insert program stream levels
519 oks_pml_pvt.insert_row(
520 p_api_version => l_api_version,
521 p_init_msg_list => l_init_msg_list,
522 x_return_status =>l_return_status,
523 x_msg_count =>l_msg_count,
524 x_msg_data =>l_msg_data,
525 p_pmlv_rec =>l_pmlrulv_tbl(pml_ctr),
526 x_pmlv_rec =>l_pmlrulvrec_Out);
527 IF (G_DEBUG_ENABLED = 'Y') THEN
528 okc_debug.log('After oks_pml_pvt insert_row', 3);
529 END IF;
530
531 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS
532 THEN
533 OKC_API.set_message
534 (G_APP_NAME,
535 G_REQUIRED_VALUE,
536 G_COL_NAME_TOKEN,
537 'create program stream levels');
538 Raise G_EXCEPTION_HALT_VALIDATION;
539 END IF;
540 l_pmschvtbl_In.delete;
541 l_pmschvtbl_Out.delete; --CK 09/02
542 --Populate program schedules table of record
543 FOR j in x_pms_tbl.FIRST..x_pms_tbl.LAST LOOP
544 l_pmschvtbl_In(j).id := okc_p_util.raw_to_number(sys_guid()) ;
545 --02/13 l_pmschvtbl_In(j).program_id := l_pmlrulvrec_Out.program_id;
546 l_pmschvtbl_In(j).object_version_number := l_pmlrulvrec_out.object_version_number;
547 l_pmschvtbl_In(j).dnz_chr_id := l_pmlrulvrec_out.dnz_chr_id;
548 l_pmschvtbl_In(j).cle_id := p_cle_id;
549 l_pmschvtbl_In(j).sch_sequence := j;
550 l_pmschvtbl_In(j).schedule_date := x_pms_tbl(j).schedule_date;
551 l_pmschvtbl_In(j).schedule_date_from := NULL;
552 l_pmschvtbl_In(j).schedule_date_to := NULL;
553 l_pmschvtbl_In(j).stream_line_id := l_pmlrulvrec_Out.id;
554 END LOOP;
555 -- Insert program schedules
556 OKS_PMS_PVT.insert_row
557 (p_api_version => l_api_version,
558 p_init_msg_list => l_init_msg_list,
559 x_return_status => l_return_status,
560 x_msg_count => l_msg_count,
561 x_msg_data => l_msg_data,
562 p_oks_pm_schedules_v_tbl => l_pmschvtbl_In,
563 x_oks_pm_schedules_v_tbl => l_pmschvtbl_Out);
564 IF (G_DEBUG_ENABLED = 'Y') THEN
565 okc_debug.log('After OKS_PMS_PVT insert_row', 3);
566 END IF;
567 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS
568 THEN
569 OKC_API.set_message
570 (G_APP_NAME,
571 G_REQUIRED_VALUE,
572 G_COL_NAME_TOKEN,
573 'create program schedules ');
574
575 Raise G_EXCEPTION_HALT_VALIDATION;
576 END IF;
577 --Update schedule exists as'Y'
578 IF l_return_status = OKC_API.G_RET_STS_SUCCESS AND
579 pml_ctr = l_pmlrulv_tbl.FIRST THEN
580 init_oks_k_line(l_klnv_tbl_in);
581 l_klnv_tbl_in(1).id := l_line_id;
582 l_klnv_tbl_in(1).object_version_number := l_obj_version_number;
583 l_klnv_tbl_in(1).PM_SCH_EXISTS_YN := 'Y';
584
585 OKS_CONTRACT_LINE_PUB.UPDATE_LINE(p_api_version => l_api_version,
586 p_init_msg_list => l_init_msg_list,
587 x_return_status => l_return_status,
588 x_msg_count => l_msg_count,
589 x_msg_data => l_msg_data,
590 p_klnv_tbl => l_klnv_tbl_in,
591 x_klnv_tbl => l_klnv_tbl_out,
592 p_validate_yn => 'N');
593 IF (G_DEBUG_ENABLED = 'Y') THEN
594 okc_debug.log('After OKS_CONTRACT_LINE_PUB UPDATE_LINE', 3);
595 END IF;
596
597 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
598 OKC_API.set_message
599 (G_APP_NAME,
600 G_REQUIRED_VALUE,
601 G_COL_NAME_TOKEN,
602 'update Oks_k_lines_b ');
603
604 Raise G_EXCEPTION_HALT_VALIDATION;
605 END IF;
606
607 END IF;
608
609 pml_ctr :=pml_ctr+1;
610 l_start_date :=l_sch_end_date +1;
611 --CK 09/02
612 IF trunc(l_sch_end_date) >= trunc(p_cov_end_date) then
613 exit;
614 END IF;
615 END LOOP;
616 /*CK RUL -- Insert program schedules
617 OKS_PMS_PVT.insert_row
618 (p_api_version => l_api_version,
619 p_init_msg_list => l_init_msg_list,
620 x_return_status => l_return_status,
621 x_msg_count => l_msg_count,
622 x_msg_data => l_msg_data,
623 p_oks_pm_schedules_v_tbl => l_pmschvtbl_In,
624 x_oks_pm_schedules_v_tbl => l_pmschvtbl_Out);
625
626
627 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS
628 THEN
629 OKC_API.set_message
630 (G_APP_NAME,
631 G_REQUIRED_VALUE,
632 G_COL_NAME_TOKEN,
633 'create program schedules ');
634
635 Raise G_EXCEPTION_HALT_VALIDATION;
636 END IF;
637 */
638
639 --CK RUL Create activities
640 pma_ctr :=1;
641 pml_ctr :=1;
642 -- l_pmarulv_tbl.delete;
643 FOR cr_activities IN cu_activities LOOP
644 l_pmarulv_tbl(pma_ctr).ACTIVITY_ID := cr_activities.ACTIVITY_ID;
645 l_pmarulv_tbl(pma_ctr).SELECT_YN := cr_activities.SELECT_YN;
646 l_pmarulv_tbl(pma_ctr).CONF_REQ_YN := cr_activities.CONF_REQ_YN;
647 l_pmarulv_tbl(pma_ctr).SCH_EXISTS_YN := cr_activities.SCH_EXISTS_YN;
648 --02/13 l_pmarulv_tbl(pma_ctr).PROGRAM_ID := cr_activities.PROGRAM_ID;
649 l_pmarulv_tbl(pma_ctr).CLE_ID := p_cle_id;
650 l_pmarulv_tbl(pma_ctr).DNZ_CHR_ID := l_chr_id;
651 --
652
653 /*CK 09/27 oks_pma_pvt.insert_row(
654 p_api_version => l_api_version,
655 p_init_msg_list => l_init_msg_list,
656 x_return_status =>l_return_status,
657 x_msg_count =>l_msg_count,
658 x_msg_data =>l_msg_data,
659 p_pmav_rec =>l_pmarulv_tbl(pma_ctr),
660 x_pmav_rec =>l_pmarulvrec_Out);
661 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS
662 THEN
663 OKC_API.set_message
664 (G_APP_NAME,
665 G_REQUIRED_VALUE,
666 G_COL_NAME_TOKEN,
667 'create pm activities');
668
669 Raise G_EXCEPTION_HALT_VALIDATION;
670 END IF;*/
671 --CK 08/18
672 pml_ctr :=1;
673 l_start_date := p_cov_start_date;
674 OPEN cu_act_stream_levels(cr_activities.id);
675 LOOP
676 FETCH cu_act_stream_levels INTO cr_act_stream_levels;
677 IF cu_act_stream_levels%FOUND THEN
678 --update pm activities with schedule exists
679 l_pmarulv_tbl(pma_ctr).SCH_EXISTS_YN := 'Y';
680 EXIT;
681 END IF;
682
683 EXIT WHEN cu_act_stream_levels%NOTFOUND;
684 END LOOP;
685 CLOSE cu_act_stream_levels;
686 oks_pma_pvt.insert_row(
687 p_api_version => l_api_version,
688 p_init_msg_list => l_init_msg_list,
689 x_return_status =>l_return_status,
690 x_msg_count =>l_msg_count,
691 x_msg_data =>l_msg_data,
692 p_pmav_rec =>l_pmarulv_tbl(pma_ctr),
693 x_pmav_rec =>l_pmarulvrec_Out);
694 IF (G_DEBUG_ENABLED = 'Y') THEN
695 okc_debug.log('After oks_pma_pvt insert_row', 3);
696 END IF;
697 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS
698 THEN
699 OKC_API.set_message
700 (G_APP_NAME,
701 G_REQUIRED_VALUE,
702 G_COL_NAME_TOKEN,
703 'create pm activities');
704
705 Raise G_EXCEPTION_HALT_VALIDATION;
706 END IF;
707 -- Added by Jvorugan for Bug:5080930
708 IF l_check_renew = 'Y' THEN
709 open get_max_act_seq_no(cr_activities.id);
710 fetch get_max_act_seq_no into l_seq_act_max_length;
711 close get_max_act_seq_no;
712 END IF;
713 -- End of changes by Jvorguan
714
715 FOR cr_act_stream_levels IN cu_act_stream_levels(cr_activities.id) LOOP
716
717 l_pmlrulv_tbl(pml_ctr).SEQUENCE_NUMBER := cr_act_stream_levels.SEQUENCE_NUMBER;
718 l_pmlrulv_tbl(pml_ctr).NUMBER_OF_OCCURENCES := cr_act_stream_levels.NUMBER_OF_OCCURENCES;
719 l_pmlrulv_tbl(pml_ctr).START_DATE := cr_act_stream_levels.START_DATE;
720 l_pmlrulv_tbl(pml_ctr).END_DATE := cr_act_stream_levels.END_DATE;
721 l_pmlrulv_tbl(pml_ctr).FREQUENCY := cr_act_stream_levels.FREQUENCY;
722 l_pmlrulv_tbl(pml_ctr).FREQUENCY_UOM := cr_act_stream_levels.FREQUENCY_UOM;
723 l_pmlrulv_tbl(pml_ctr).OFFSET_DURATION := cr_act_stream_levels.OFFSET_DURATION;
724 l_pmlrulv_tbl(pml_ctr).OFFSET_UOM := cr_act_stream_levels.OFFSET_UOM;
725 l_pmlrulv_tbl(pml_ctr).AUTOSCHEDULE_YN := cr_act_stream_levels.AUTOSCHEDULE_YN;
726 l_pmlrulv_tbl(pml_ctr).ACTIVITY_LINE_ID := l_pmarulvrec_Out.Id;
727 --02/12 l_pmlrulv_tbl(pml_ctr).PROGRAM_ID := cr_act_stream_levels.PROGRAM_ID;
728 l_pmlrulv_tbl(pml_ctr).CLE_ID := p_cle_id;
729
730 l_pmlrulv_tbl(pml_ctr).DNZ_CHR_ID := l_chr_id;
731 --CK RUL l_start_date := p_cov_start_date;
732 l_pmlrulv_tbl(pml_ctr).START_DATE := l_start_date;
733
734
735
736 l_first_sch_date := nvl(okc_time_util_pub.get_enddate(l_start_date,
737 l_pmlrulv_tbl(pml_ctr).offset_uom,
738 l_pmlrulv_tbl(pml_ctr).offset_duration) + 1, l_start_date);
739 l_duration := NULL;
740 l_timeunit := NULL;
741
742 IF ((l_pmlrulv_tbl(pml_ctr).offset_uom IS NOT NULL) and
743 (l_pmlrulv_tbl(pml_ctr).offset_duration IS NOT NULL) and
744 (trunc(l_first_sch_date) >= trunc(p_cov_end_date))) THEN
745
746 l_first_sch_date := trunc(p_cov_end_date);
747
748 okc_time_util_pub.get_duration(
749 p_start_date => l_start_date,
750 p_end_date => l_first_sch_date - 1,
751 x_duration => l_duration,
752 x_timeunit => l_timeunit,
753 x_return_status => l_return_status);
754
755 IF NOT l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
756 RAISE G_EXCEPTION_HALT_VALIDATION;
757 END IF;
758
759 END IF;
760
761 IF to_number(l_pmlrulv_tbl(pml_ctr).number_of_occurences) IS NOT NULL THEN
762
763 l_sch_end_date := okc_time_util_pub.get_enddate(l_first_sch_date,
764 l_pmlrulv_tbl(pml_ctr).frequency_uom,
765 to_number(l_pmlrulv_tbl(pml_ctr).frequency)*
766 to_number(l_pmlrulv_tbl(pml_ctr).number_of_occurences));
767
768 IF trunc(l_sch_end_date) >= trunc(p_cov_end_date) then
769 l_sch_end_date := trunc(p_cov_end_date);
770 end if;
771
772 ELSE
773 l_sch_end_date := trunc(p_cov_end_date);
774 END IF;
775 l_pmlrulv_tbl(pml_ctr).END_DATE := l_sch_end_date;
776
777 --Added by Jvorugan for bug:5080930
778 l_periods := to_number(l_pmlrulv_tbl(pml_ctr).NUMBER_OF_OCCURENCES);
779 IF l_check_renew ='Y'
780 THEN
781 IF to_number(l_pmlrulv_tbl(pml_ctr).SEQUENCE_NUMBER) = nvl(l_seq_act_max_length,0) THEN
782 l_periods :=NULL;
783 END IF;
784 END IF;
785 -- End of changes by Jvorugan
786
787 x_pms_tbl.DELETE; --CK 09/02
788
789 GENERATE_SCHEDULE(
790 p_api_version => l_api_version,
791 p_init_msg_list => l_init_msg_list,
792 x_return_status => x_sch_ret_status,
793 x_msg_count => l_msg_count,
794 x_msg_data => l_msg_data,
795 p_periods => l_periods, -- Modified by Jvorugan for Bug:5080930 to_number(l_pmlrulv_tbl(pml_ctr).NUMBER_OF_OCCURENCES),
796 p_start_date => p_cov_start_date,
797 p_end_date => p_cov_end_date,
798 p_duration => to_number(l_pmlrulv_tbl(pml_ctr).FREQUENCY),
799 p_period => l_pmlrulv_tbl(pml_ctr).FREQUENCY_UOM,
800 p_first_sch_date => l_first_sch_date,
801 x_periods => x_periods,
802 x_last_date => x_last_date,
803 x_pms_tbl => x_pms_tbl);
804 IF NOT x_sch_ret_status = OKC_API.G_RET_STS_SUCCESS THEN
805 RAISE G_EXCEPTION_HALT_VALIDATION;
806 END IF;
807 l_pmlrulv_tbl(pml_ctr).NUMBER_OF_OCCURENCES := x_periods;
808 -- Added by Jvorugan for Bug:5511803
809 IF l_check_renew ='Y'
810 THEN
811 IF to_number(l_pmlrulv_tbl(pml_ctr).NUMBER_OF_OCCURENCES) IS NOT NULL THEN
812
813 l_sch_end_date := okc_time_util_pub.get_enddate(l_first_sch_date,
814 l_pmlrulv_tbl(pml_ctr).frequency_uom,
815 to_number(l_pmlrulv_tbl(pml_ctr).frequency)*
816 to_number(l_pmlrulv_tbl(pml_ctr).number_of_occurences));
817
818 if trunc(l_sch_end_date) >= trunc(p_cov_end_date) then
819 l_sch_end_date := trunc(p_cov_end_date);
820 end if;
821
822 ELSE
823 l_sch_end_date := trunc(p_cov_end_date);
824 END IF;
825 l_pmlrulv_tbl(pml_ctr).END_DATE := l_sch_end_date;
826 END IF;
827 -- End of changes by Jvorugan
828
829
830 oks_pml_pvt.insert_row(
831 p_api_version => l_api_version,
832 p_init_msg_list => l_init_msg_list,
833 x_return_status =>l_return_status,
834 x_msg_count =>l_msg_count,
835 x_msg_data =>l_msg_data,
836 p_pmlv_rec =>l_pmlrulv_tbl(pml_ctr),
837 x_pmlv_rec =>l_pmlrulvrec_Out);
838 IF (G_DEBUG_ENABLED = 'Y') THEN
839 okc_debug.log('After act oks_pml_pvt insert_row', 3);
840 END IF;
841 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS
842 THEN
843 OKC_API.set_message
844 (G_APP_NAME,
845 G_REQUIRED_VALUE,
846 G_COL_NAME_TOKEN,
847 'create activity stream levels');
848 Raise G_EXCEPTION_HALT_VALIDATION;
849 END IF;
850 l_pmschvtbl_In.delete;
851 l_pmschvtbl_Out.delete; --
852
853 FOR j in x_pms_tbl.FIRST..x_pms_tbl.LAST LOOP
854 l_pmschvtbl_In(j).id := okc_p_util.raw_to_number(sys_guid()) ;
855 --02/12 l_pmschvtbl_In(j).program_id := l_pmlrulvrec_Out.program_id;
856 l_pmschvtbl_In(j).object_version_number := l_pmlrulvrec_out.object_version_number;
857 l_pmschvtbl_In(j).dnz_chr_id := l_pmlrulvrec_out.dnz_chr_id;
858 l_pmschvtbl_In(j).cle_id := p_cle_id;
859 l_pmschvtbl_In(j).sch_sequence := j;
860 l_pmschvtbl_In(j).schedule_date := x_pms_tbl(j).schedule_date;
861 l_pmschvtbl_In(j).schedule_date_from := NULL;
862 l_pmschvtbl_In(j).schedule_date_to := NULL;
863 l_pmschvtbl_In(j).stream_line_id := l_pmlrulvrec_Out.id;
864 l_pmschvtbl_In(j).activity_line_id := l_pmarulvrec_Out.id;
865 END LOOP;
866 OKS_PMS_PVT.insert_row
867 (p_api_version => l_api_version,
868 p_init_msg_list => l_init_msg_list,
869 x_return_status => l_return_status,
870 x_msg_count => l_msg_count,
871 x_msg_data => l_msg_data,
872 p_oks_pm_schedules_v_tbl => l_pmschvtbl_In,
873 x_oks_pm_schedules_v_tbl => l_pmschvtbl_Out);
874
875 IF (G_DEBUG_ENABLED = 'Y') THEN
876 okc_debug.log('After act OKS_PMS_PVT insert_row', 3);
877 END IF;
878 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS
879 THEN
880 OKC_API.set_message
881 (G_APP_NAME,
882 G_REQUIRED_VALUE,
883 G_COL_NAME_TOKEN,
884 'create activity schedules ');
885
886 Raise G_EXCEPTION_HALT_VALIDATION;
887 END IF;
888
889 /*ck 09/27
890 oks_pma_pvt.update_row(
891 p_api_version => l_api_version,
892 p_init_msg_list => l_init_msg_list,
893 x_return_status =>l_return_status,
894 x_msg_count =>l_msg_count,
895 x_msg_data =>l_msg_data,
896 p_pmav_rec =>l_pmarulv_tbl(pma_ctr),
897 x_pmav_rec =>l_pmarulvrec_Out);
898 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS
899 THEN
900 OKC_API.set_message
901 (G_APP_NAME,
902 G_REQUIRED_VALUE,
903 G_COL_NAME_TOKEN,
904 'UPDATE pm activities');
905
906 Raise G_EXCEPTION_HALT_VALIDATION;
907 END IF;
908 */
909 pml_ctr :=pml_ctr+1;
910 l_start_date :=l_sch_end_date +1;
911 IF trunc(l_sch_end_date) >= trunc(p_cov_end_date) then
912 exit;
913 END IF;
914
915 END LOOP;
916
917 pma_ctr :=pma_ctr+1;
918 END LOOP;
919 /* -- Insert act schedules
920 OKS_PMS_PVT.insert_row
921 (p_api_version => l_api_version,
922 p_init_msg_list => l_init_msg_list,
923 x_return_status => l_return_status,
924 x_msg_count => l_msg_count,
925 x_msg_data => l_msg_data,
926 p_oks_pm_schedules_v_tbl => l_pmschvtbl_In,
927 x_oks_pm_schedules_v_tbl => l_pmschvtbl_Out);
928
929
930 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS
931 THEN
932 OKC_API.set_message
933 (G_APP_NAME,
934 G_REQUIRED_VALUE,
935 G_COL_NAME_TOKEN,
936 'create activity schedules ');
937
938 Raise G_EXCEPTION_HALT_VALIDATION;
939 END IF;
940
941 */
942
943 IF (G_DEBUG_ENABLED = 'Y') THEN
944 okc_debug.log('Exiting Create_PM_Program_Schedule', 3);
945 okc_debug.Reset_Indentation;
946 END IF;
947 x_return_status := l_return_status;
948 x_msg_count := l_msg_count;
949 x_msg_data := l_msg_data;
950
951 EXCEPTION
952 WHEN G_EXCEPTION_HALT_VALIDATION THEN
953 x_return_status := l_return_status ;
954 IF (G_DEBUG_ENABLED = 'Y') THEN
955 okc_debug.log('Exiting Create_PM_Program_Schedule'||l_return_Status, 3);
956 okc_debug.Reset_Indentation;
957 END IF;
958 WHEN OTHERS THEN
959 OKC_API.SET_MESSAGE(
960 p_app_name => g_app_name,
961 p_msg_name => g_unexpected_error,
962 p_token1 => g_sqlcode_token,
963 p_token1_value => sqlcode,
964 p_token2 => g_sqlerrm_token,
965 p_token2_value => sqlerrm);
966
967 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
968 x_msg_count :=l_msg_count;
969 IF (G_DEBUG_ENABLED = 'Y') THEN
970 okc_debug.log('Exiting Create_PM_Program_Schedule'||sqlerrm, 3);
971 okc_debug.Reset_Indentation;
972 END IF;
973 END CREATE_PM_PROGRAM_SCHEDULE;
974
975
976
977 PROCEDURE REFRESH_PM_PROGRAM_SCHEDULE
978 (p_api_version IN NUMBER,
979 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
980 x_return_status OUT NOCOPY VARCHAR2,
981 x_msg_count OUT NOCOPY NUMBER,
982 x_msg_data OUT NOCOPY VARCHAR2,
983 p_cov_tbl IN okc_contract_pub.clev_tbl_type,
984 p_pmlrulv_tbl IN oks_pml_pvt.pmlv_tbl_type,
985 x_pmlrulv_tbl OUT NOCOPY oks_pml_pvt.pmlv_tbl_type,
986 x_pmschv_tbl OUT NOCOPY pmsch_refresh_tbl_type)-- OKS_PMS_PVT.oks_pm_schedules_v_tbl_type)
987 IS
988
989 l_pms_tbl pms_tbl_type;
990 l_api_version CONSTANT NUMBER := 1.0;
991 l_init_msg_list CONSTANT VARCHAR2(1):= 'F';
992 l_return_status VARCHAR2(1);
993 l_msg_count NUMBER;
994 l_msg_data VARCHAR2(4000):='msg_data: ';
995 l_pmschvtbl_In pmsch_refresh_tbl_type; --OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
996
997 l_Rule_Id NUMBER;
998
999 l_pmlrulv_tbl oks_pml_pvt.pmlv_tbl_type;
1000 l_pmlrulv_tbl_out oks_pml_pvt.pmlv_tbl_type;
1001 l_pmlrulv_tbl_02 oks_pml_pvt.pmlv_tbl_type;
1002 l_sort_ret_status VARCHAR2(1) := 'S';
1003 x_sch_ret_status VARCHAR2(1) := 'S';
1004 x_periods NUMBER;
1005 x_pms_tbl pms_tbl_type;
1006 l_start_date DATE;
1007 x_last_date DATE;
1008 l_first_sch_date DATE;
1009 l_sch_end_date DATE;
1010 g_pml_end_date DATE;
1011 i NUMBER := 0;
1012 j NUMBER := 0;
1013 k NUMBER := 0;
1014 pmlout_ctr NUMBER := 0;
1015 l_duration number;
1016 l_timeunit varchar2(30);
1017
1018
1019 PROCEDURE Sort_PML_seq
1020 (P_Input_Tab IN oks_pml_pvt.pmlv_tbl_type
1021 ,X_Output_Tab OUT NOCOPY oks_pml_pvt.pmlv_tbl_type
1022 ,X_Return_Status OUT NOCOPY VARCHAR2) IS
1023
1024 Lx_Sort_Tab oks_pml_pvt.pmlv_tbl_type:= P_Input_Tab;
1025 Lx_Return_Status VARCHAR2(1) := 'S';
1026
1027 Li_TableIdx_Out BINARY_INTEGER;
1028 Li_TableIdx_In BINARY_INTEGER;
1029
1030 Lx_Temp_Seq oks_pml_pvt.pmlv_rec_type;
1031
1032 Lv_Composit_Val1 NUMBER;
1033 Lv_Composit_Val2 NUMBER;
1034
1035 BEGIN
1036
1037 Li_TableIdx_Out := Lx_Sort_Tab.FIRST;
1038
1039 WHILE Li_TableIdx_Out IS NOT NULL LOOP
1040
1041 Li_TableIdx_In := Li_TableIdx_Out;
1042
1043 WHILE Li_TableIdx_In IS NOT NULL LOOP
1044
1045 Lv_Composit_Val1 := to_number(Lx_Sort_Tab(Li_TableIdx_Out).SEQUENCE_NUMBER);
1046 Lv_Composit_Val2 := to_number(Lx_Sort_Tab(Li_TableIdx_In).SEQUENCE_NUMBER);
1047
1048 IF Lv_Composit_Val1 > Lv_Composit_Val2 THEN
1049
1050 Lx_Temp_Seq := Lx_Sort_Tab(Li_TableIdx_Out);
1051 Lx_Sort_Tab(Li_TableIdx_Out) := Lx_Sort_Tab(Li_TableIdx_In);
1052 Lx_Sort_Tab(Li_TableIdx_In) := Lx_Temp_Seq;
1053
1054 END IF;
1055
1056 Li_TableIdx_In := Lx_Sort_Tab.NEXT(Li_TableIdx_In);
1057
1058 END LOOP;
1059
1060 Li_TableIdx_Out := Lx_Sort_Tab.NEXT(Li_TableIdx_Out);
1061
1062 END LOOP;
1063
1064 X_Output_Tab := Lx_Sort_Tab;
1065 X_Return_Status := Lx_Return_Status;
1066
1067 EXCEPTION
1068
1069 WHEN OTHERS THEN
1070
1071 X_Return_Status := 'U';
1072
1073 END Sort_PML_seq;
1074
1075 BEGIN
1076
1077 -- UI already checks if Stream level(PML rule) effectivities are not overlapping.
1078 -- Sorting to Check for gaps in Stream level(PML rule) effectivities
1079 -- l_msg_data := l_msg_data||'p_pmlrulv_tbl.COUNT'||'; ';
1080 IF p_pmlrulv_tbl.COUNT > 1 then
1081
1082 Sort_PML_seq
1083 (P_Input_Tab => p_pmlrulv_tbl
1084 ,X_Output_Tab => l_pmlrulv_tbl
1085 ,X_Return_Status => l_sort_ret_status);
1086
1087
1088 IF NOT l_sort_ret_status = OKC_API.G_RET_STS_SUCCESS THEN
1089 RAISE G_EXCEPTION_HALT_VALIDATION;
1090 END IF;
1091 -- l_msg_data := l_msg_data||'Sort_PML_StDt : '||l_sort_ret_status||'; ';
1092
1093 ELSIF p_pmlrulv_tbl.COUNT = 1 then
1094 l_pmlrulv_tbl := p_pmlrulv_tbl;
1095 END IF;
1096
1097 -- Checking for gaps in Stream level(PML rule) effectivities
1098
1099 /* Commented for Bug # 6152133 (FP Bug for 6114565) */
1100 /* FOR i in l_pmlrulv_tbl.FIRST..l_pmlrulv_tbl.FIRST LOOP
1101
1102 IF i = 1 THEN
1103 g_pml_end_date := to_date(l_pmlrulv_tbl(i).END_DATE,'YYYY/MM/DD');
1104 ELSE
1105 IF (to_date(l_pmlrulv_tbl(i).START_DATE,'YYYY/MM/DD') - g_pml_end_date) > 1 THEN
1106 RAISE G_EXCEPTION_HALT_VALIDATION; -- return with error
1107 ELSE
1108 g_pml_end_date := to_date(l_pmlrulv_tbl(i).END_DATE,'YYYY/MM/DD');
1109 END IF;
1110 END IF;
1111
1112 END LOOP;
1113 */
1114 /* Comment Ends */
1115 l_start_date := p_cov_tbl(1).start_date;
1116
1117
1118 FOR i in l_pmlrulv_tbl.FIRST..l_pmlrulv_tbl.LAST LOOP
1119
1120
1121 l_first_sch_date := nvl(okc_time_util_pub.get_enddate(l_start_date,
1122 l_pmlrulv_tbl(i).OFFSET_UOM,
1123 l_pmlrulv_tbl(i).OFFSET_DURATION) + 1,l_start_date);
1124
1125 l_duration := NULL;
1126 l_timeunit := NULL;
1127
1128 IF ((l_pmlrulv_tbl(i).OFFSET_UOM IS NOT NULL) and
1129 (l_pmlrulv_tbl(i).OFFSET_DURATION IS NOT NULL) and
1130 (trunc(l_first_sch_date) >= trunc(p_cov_tbl(1).end_date))) THEN
1131
1132 l_first_sch_date := trunc(p_cov_tbl(1).end_date);
1133
1134 okc_time_util_pub.get_duration(
1135 p_start_date => l_start_date,
1136 p_end_date => l_first_sch_date - 1,
1137 x_duration => l_duration,
1138 x_timeunit => l_timeunit,
1139 x_return_status => l_return_status);
1140
1141 IF NOT l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1142 RAISE G_EXCEPTION_HALT_VALIDATION;
1143 END IF;
1144
1145 END IF;
1146
1147
1148
1149 l_sch_end_date := okc_time_util_pub.get_enddate(l_first_sch_date,
1150 l_pmlrulv_tbl(i).FREQUENCY_UOM,
1151 to_number(l_pmlrulv_tbl(i).FREQUENCY)*
1152 to_number(l_pmlrulv_tbl(i).NUMBER_OF_OCCURENCES));
1153 IF trunc(l_sch_end_date) >= trunc(p_cov_tbl(1).end_date) then
1154 l_sch_end_date := trunc(p_cov_tbl(1).end_date);
1155 end if;
1156
1157 x_pms_tbl.DELETE;
1158
1159 GENERATE_SCHEDULE(
1160 p_api_version => l_api_version,
1161 p_init_msg_list => l_init_msg_list,
1162 x_return_status => x_sch_ret_status,
1163 x_msg_count => l_msg_count,
1164 x_msg_data => l_msg_data,
1165 p_periods => to_number(l_pmlrulv_tbl(i).NUMBER_OF_OCCURENCES),
1166 p_start_date => l_start_date,
1167 p_end_date => p_cov_tbl(1).end_date,
1168 p_duration => to_number(l_pmlrulv_tbl(i).FREQUENCY),
1169 p_period => l_pmlrulv_tbl(i).FREQUENCY_UOM,
1170 p_first_sch_date => l_first_sch_date,
1171 x_periods => x_periods,
1172 x_last_date => x_last_date,
1173 x_pms_tbl => x_pms_tbl);
1174
1175 IF NOT x_sch_ret_status = OKC_API.G_RET_STS_SUCCESS THEN
1176 RAISE G_EXCEPTION_HALT_VALIDATION;
1177 END IF;
1178
1179 -- l_msg_data := l_msg_data||'l_pmlrulv_tbl_out(i).id : '||l_pmlrulv_tbl_out(i).id||'; ';
1180
1181 l_pmlrulv_tbl_out(i) := l_pmlrulv_tbl(i);
1182 l_pmlrulv_tbl_out(i).id := l_pmlrulv_tbl(i).id;
1183 l_pmlrulv_tbl_out(i).object_version_number := l_pmlrulv_tbl(i).object_version_number;
1184 l_pmlrulv_tbl_out(i).dnz_chr_id := l_pmlrulv_tbl(i).dnz_chr_id;
1185 --02/12 l_pmlrulv_tbl_out(i).PROGRAM_ID := l_pmlrulv_tbl(i).PROGRAM_ID;
1186 l_pmlrulv_tbl_out(i).SEQUENCE_NUMBER := l_pmlrulv_tbl(i).SEQUENCE_NUMBER;
1187
1188 l_pmlrulv_tbl_out(i).NUMBER_OF_OCCURENCES := x_periods;
1189 l_pmlrulv_tbl_out(i).start_date := l_start_date;
1190 l_pmlrulv_tbl_out(i).end_date := l_sch_end_date;
1191 l_pmlrulv_tbl_out(i).FREQUENCY := l_pmlrulv_tbl(i).FREQUENCY;
1192 l_pmlrulv_tbl_out(i).FREQUENCY_UOM := l_pmlrulv_tbl(i).FREQUENCY_UOM;
1193
1194 IF l_duration IS NOT NULL and l_timeunit IS NOT NULL THEN
1195 l_pmlrulv_tbl_out(i).OFFSET_DURATION := to_char(l_duration);
1196 l_pmlrulv_tbl_out(i).OFFSET_UOM := l_timeunit;
1197 ELSE
1198 l_pmlrulv_tbl_out(i).OFFSET_DURATION := l_pmlrulv_tbl(i).OFFSET_DURATION;
1199 l_pmlrulv_tbl_out(i).OFFSET_UOM := l_pmlrulv_tbl(i).OFFSET_UOM;
1200 END IF;
1201
1202 l_pmlrulv_tbl_out(i).AUTOSCHEDULE_YN := 'Y';
1203
1204 FOR j in x_pms_tbl.FIRST..x_pms_tbl.LAST LOOP
1205
1206
1207 l_pmschvtbl_In(j+k).seq_no := to_number(l_pmlrulv_tbl(i).SEQUENCE_NUMBER);
1208 l_pmschvtbl_In(j+k).stream_line_id := l_pmlrulv_tbl(i).id;
1209 l_pmschvtbl_In(j+k).object_version_number := l_pmlrulv_tbl(i).object_version_number;
1210 l_pmschvtbl_In(j+k).dnz_chr_id := l_pmlrulv_tbl(i).dnz_chr_id;
1211 --02/12 l_pmschvtbl_In(j+k).program_id := l_pmlrulv_tbl(i).program_id;
1212 l_pmschvtbl_In(j+k).cle_id := p_cov_tbl(1).id;
1213 l_pmschvtbl_In(j+k).sch_sequence := j;
1214 l_pmschvtbl_In(j+k).schedule_date := x_pms_tbl(j).schedule_date;
1215 l_pmschvtbl_In(j+k).schedule_date_from := NULL;
1216 l_pmschvtbl_In(j+k).schedule_date_to := NULL;
1217
1218 if l_pmlrulv_tbl(i).activity_line_id is not null then
1219 l_pmschvtbl_In(j+k).activity_line_id := to_number(l_pmlrulv_tbl(i).activity_line_id);--CK change
1220 end if;
1221 END LOOP;
1222 k := k + x_pms_tbl.LAST;
1223
1224
1225 IF trunc(l_sch_end_date) >= trunc(p_cov_tbl(1).end_date) then
1226 -- l_msg_data := l_msg_data||'in exit '||'; ';
1227 exit;
1228 else
1229 l_start_date := l_sch_end_date + 1;
1230 end if;
1231
1232 END LOOP;
1233
1234 l_pmlrulv_tbl.DELETE;
1235 x_pmlrulv_tbl := l_pmlrulv_tbl_out;
1236 x_pmschv_tbl := l_pmschvtbl_In;
1237 x_return_status := l_return_status;
1238 x_msg_count := l_msg_count;
1239 x_msg_data := l_msg_data;
1240
1241 EXCEPTION
1242 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1243 x_return_status := l_return_status ;
1244
1245 WHEN OTHERS THEN
1246 OKC_API.SET_MESSAGE(
1247 p_app_name => g_app_name,
1248 p_msg_name => g_unexpected_error,
1249 p_token1 => g_sqlcode_token,
1250 p_token1_value => sqlcode,
1251 p_token2 => g_sqlerrm_token,
1252 p_token2_value => sqlerrm);
1253
1254 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1255
1256 END REFRESH_PM_PROGRAM_SCHEDULE;
1257
1258 PROCEDURE RENEW_PM_PROGRAM_SCHEDULE
1259 (p_api_version IN NUMBER,
1260 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1261 x_return_status OUT NOCOPY VARCHAR2,
1262 x_msg_count OUT NOCOPY NUMBER,
1263 x_msg_data OUT NOCOPY VARCHAR2,
1264 p_contract_line_id IN NUMBER)
1265 IS
1266 --CK RUL
1267 l_cov_start_date DATE;
1268 l_cov_end_date DATE;
1269 l_orig_cov_id NUMBER;
1270 CURSOR CU_ORIG_COV_ID IS
1271 SELECT
1272 orig_system_id1 id
1273 FROM
1274 okc_k_lines_b
1275 WHERE
1276 id=p_contract_line_id;
1277 -- AND lse_id IN (2,15,20); commented by jvorugan as pm is associated with contract line
1278 cr_orig_cov_id cu_orig_cov_id%ROWTYPE;
1279
1280 CURSOR Cur_NewCovDet IS
1281 SELECT ID,
1282 START_DATE , --COV.START_DATE START_DATE,
1283 END_DATE --COV.END_DATE END_DATE,
1284 FROM OKC_K_LINES_B
1285 WHERE id=p_contract_line_id;
1286 -- AND lse_id IN (2,15,20); commented by jvorugan as pm is associated with contract line
1287
1288 NewCovDet_Rec Cur_NewCovDet%ROWTYPE;
1289 l_old_cle_id NUMBER;
1290
1291 l_row_cnt NUMBER;
1292 l_api_version CONSTANT NUMBER := 1.0;
1293 l_init_msg_list CONSTANT VARCHAR2(1):= 'F';
1294 l_return_status VARCHAR2(1):='S'; --ck
1295 l_msg_count NUMBER;
1296 l_msg_data VARCHAR2(2000):=null;
1297 l_cov_rec okc_contract_pub.clev_rec_type;
1298
1299 BEGIN
1300 IF (G_DEBUG_ENABLED = 'Y') THEN
1301 okc_debug.Set_Indentation('Renew_PM_Program_Schedule');
1302 okc_debug.log('Entered Renew_PM_Program_Schedule', 3);
1303 END IF;
1304 --CK RUL
1305 FOR NewCovDet_Rec IN Cur_NewCovDet
1306 LOOP
1307 l_cov_rec.id :=NewCovDet_Rec.Id;
1308 l_cov_rec.Start_Date :=NewCovDet_Rec.Start_Date;
1309 l_cov_rec.End_Date :=NewCovDet_Rec.End_Date;
1310 END LOOP;
1311 OPEN cu_orig_cov_id;
1312 LOOP
1313 FETCH cu_orig_cov_id INTO cr_orig_cov_id;
1314 EXIT WHEN cu_orig_cov_id%NOTFOUND;
1315 l_orig_cov_id :=cr_orig_cov_id.id;
1316 END LOOP;
1317 CLOSE cu_orig_cov_id;
1318 CREATE_PM_PROGRAM_SCHEDULE
1319 (p_api_version => l_api_version,
1320 p_init_msg_list => l_init_msg_list,
1321 x_return_status => l_return_status,
1322 x_msg_count => l_msg_count,
1323 x_msg_data => l_msg_data,
1324 p_template_cle_id =>l_orig_cov_id, --pass coverage line id of original contract
1325 p_cle_id =>l_cov_rec.id ,--p_contract_line_id, --pass coverage line id of new contract
1326 p_cov_start_date => l_cov_rec.Start_Date,
1327 p_cov_end_date => l_cov_rec.End_Date);
1328 IF (G_DEBUG_ENABLED = 'Y') THEN
1329 okc_debug.log('After CREATE_PM_PROGRAM_SCHEDULE', 3);
1330 END IF;
1331
1332 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS
1333 THEN
1334 OKC_API.set_message
1335 (G_APP_NAME,
1336 G_REQUIRED_VALUE,
1337 G_COL_NAME_TOKEN,
1338 'create activities streamlevels and schedules');
1339
1340 Raise G_EXCEPTION_HALT_VALIDATION;
1341 END IF;
1342
1343 IF (G_DEBUG_ENABLED = 'Y') THEN
1344 okc_debug.log('Exiting Renew_PM_Program_Schedule', 3);
1345 okc_debug.Reset_Indentation;
1346 END IF;
1347
1348 x_return_status := l_return_status;
1349 x_msg_count := l_msg_count;
1350 x_msg_data := l_msg_data;
1351
1352 EXCEPTION
1353 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1354 x_return_status := l_return_status ;
1355 IF (G_DEBUG_ENABLED = 'Y') THEN
1356 okc_debug.log('Exiting Renew_PM_Program_Schedule'||l_return_Status, 3);
1357 okc_debug.Reset_Indentation;
1358 END IF;
1359
1360 WHEN OTHERS THEN
1361 OKC_API.SET_MESSAGE(
1362 p_app_name => g_app_name,
1363 p_msg_name => g_unexpected_error,
1364 p_token1 => g_sqlcode_token,
1365 p_token1_value => sqlcode,
1366 p_token2 => g_sqlerrm_token,
1367 p_token2_value => sqlerrm);
1368
1369 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1370 x_msg_count :=l_msg_count;
1371 IF (G_DEBUG_ENABLED = 'Y') THEN
1372 okc_debug.log('Exiting Renew_PM_Program_Schedule'||l_return_Status, 3);
1373 okc_debug.Reset_Indentation;
1374 END IF;
1375
1376 END RENEW_PM_PROGRAM_SCHEDULE;
1377
1378
1379 --CK CODE FOR RULES CHANGES
1380 PROCEDURE POPULATE_SCHEDULE
1381 (p_api_version IN NUMBER,
1382 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1383 x_return_status OUT NOCOPY VARCHAR2,
1384 x_msg_count OUT NOCOPY NUMBER,
1385 x_msg_data OUT NOCOPY VARCHAR2,
1386 p_pmlrulv_tbl IN oks_pml_pvt.pmlv_tbl_type,
1387 p_sch_tbl IN OKS_PMS_PVT.oks_pm_schedules_v_tbl_type,
1388 p_pma_tbl IN pma_tbl_type,
1389 p_is_template IN VARCHAR2)
1390 IS
1391 l_pmlrulv_tbl oks_pml_pvt.pmlv_tbl_type;
1392 l_pmlrulv_tbl_out oks_pml_pvt.pmlv_tbl_type;
1393 l_pmarulv_rec oks_pma_pvt.pmav_rec_type;
1394 l_pmarulv_rec_out oks_pma_pvt.pmav_rec_type;
1395 l_api_version CONSTANT NUMBER := 1.0;
1396 l_init_msg_list CONSTANT VARCHAR2(1):= 'F';
1397 l_return_status VARCHAR2(1);
1398 l_msg_count NUMBER;
1399 l_msg_data VARCHAR2(2000):=null;
1400 l_msg_index_out Number;
1401 l_api_name CONSTANT VARCHAR2(30) := 'populate schedule';
1402 l_pmlrulvrec_Out oks_pml_pvt.pmlv_rec_type;
1403 l_sch_tbl OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
1404 l_sch_tbl_out OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
1405 l_pml_index NUMBER;
1406 l_pml_indexin NUMBER;
1407 l_pma_index NUMBER;
1408 l_sch_index NUMBER;
1409 l_sch_indexin NUMBER;
1410 CURSOR cu_obj_version(cp_activity_line_id NUMBER)
1411 IS
1412 SELECT
1413 object_version_number
1414 FROM
1415 OKS_PM_ACTIVITIES
1416 WHERE id=cp_activity_line_id;
1417 -- cr_obj_version cu_obj_version%ROWTYPE;
1418 l_object_version_number NUMBER;
1419 BEGIN
1420 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1421 p_init_msg_list,
1422 '_PVT',
1423 x_return_status);
1424 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1425 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1426 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1427 RAISE OKC_API.G_EXCEPTION_ERROR;
1428 END IF;
1429
1430
1431 l_pml_index:=1;
1432 l_pma_index:=1;
1433 WHILe l_pma_index <=p_pma_tbl.COUNT LOOP
1434 l_pml_indexin:=1;
1435 WHILE l_pml_indexin <= p_pmlrulv_tbl.COUNT LOOP
1436 l_pmlrulv_tbl(l_pml_index).SEQUENCE_NUMBER := p_pmlrulv_tbl(l_pml_indexin).SEQUENCE_NUMBER;
1437 l_pmlrulv_tbl(l_pml_index).NUMBER_OF_OCCURENCES := p_pmlrulv_tbl(l_pml_indexin).NUMBER_OF_OCCURENCES;
1438 l_pmlrulv_tbl(l_pml_index).START_DATE := p_pmlrulv_tbl(l_pml_indexin).START_DATE;
1439 l_pmlrulv_tbl(l_pml_index).END_DATE := p_pmlrulv_tbl(l_pml_indexin).END_DATE;
1440 l_pmlrulv_tbl(l_pml_index).FREQUENCY := p_pmlrulv_tbl(l_pml_indexin).FREQUENCY;
1441 l_pmlrulv_tbl(l_pml_index).FREQUENCY_UOM := p_pmlrulv_tbl(l_pml_indexin).FREQUENCY_UOM;
1442 l_pmlrulv_tbl(l_pml_index).OFFSET_DURATION := p_pmlrulv_tbl(l_pml_indexin).OFFSET_DURATION;
1443 l_pmlrulv_tbl(l_pml_index).OFFSET_UOM := p_pmlrulv_tbl(l_pml_indexin).OFFSET_UOM;
1444 l_pmlrulv_tbl(l_pml_index).AUTOSCHEDULE_YN := p_pmlrulv_tbl(l_pml_indexin).AUTOSCHEDULE_YN;
1445 --02/12 l_pmlrulv_tbl(l_pml_index).PROGRAM_ID := p_pmlrulv_tbl(l_pml_indexin).PROGRAM_ID;
1446 l_pmlrulv_tbl(l_pml_index).CLE_ID := p_pmlrulv_tbl(l_pml_indexin).CLE_ID;
1447 l_pmlrulv_tbl(l_pml_index).dnz_chr_id := p_pmlrulv_tbl(l_pml_indexin).dnz_chr_id ;
1448 l_pmlrulv_tbl(l_pml_index).ACTIVITY_LINE_ID := p_pma_tbl(l_pma_index).activity_line_id;
1449
1450
1451 oks_pml_pvt.insert_row(
1452 p_api_version => l_api_version,
1453 p_init_msg_list => l_init_msg_list,
1454 x_return_status =>l_return_status,
1455 x_msg_count =>l_msg_count,
1456 x_msg_data =>l_msg_data,
1457 p_pmlv_rec =>l_pmlrulv_tbl(l_pml_index),
1458 x_pmlv_rec =>l_pmlrulvrec_Out);
1459 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS
1460 THEN
1461 OKC_API.set_message
1462 (G_APP_NAME,
1463 G_REQUIRED_VALUE,
1464 G_COL_NAME_TOKEN,
1465 'create program stream levels');
1466 Raise G_EXCEPTION_HALT_VALIDATION;
1467 END IF;
1468
1469
1470 --Create schedules
1471 l_sch_indexin :=1;
1472 --reset table as dupl values are created
1473 l_sch_tbl.delete;
1474 IF p_is_template='N' THEN
1475 l_sch_index :=1;
1476 WHILE l_sch_indexin <=p_sch_tbl.COUNT LOOP
1477
1478 IF (p_pmlrulv_tbl(l_pml_indexin).id = p_sch_tbl(l_sch_indexin).stream_line_id)THEN
1479 l_sch_tbl(l_sch_index).schedule_date := p_sch_tbl(l_sch_indexin).schedule_date;
1480 l_sch_tbl(l_sch_index).schedule_date_from := p_sch_tbl(l_sch_indexin).schedule_date_from;
1481 l_sch_tbl(l_sch_index).schedule_date_to := p_sch_tbl(l_sch_indexin).schedule_date_to;
1482 l_sch_tbl(l_sch_index).dnz_chr_id := p_sch_tbl(l_sch_indexin).dnz_chr_id ;
1483 l_sch_tbl(l_sch_index).cle_id := p_sch_tbl(l_sch_indexin).cle_id ;
1484 l_sch_tbl(l_sch_index).sch_sequence := p_sch_tbl(l_sch_indexin).sch_sequence;
1485 l_sch_tbl(l_sch_index).activity_line_id := p_pma_tbl(l_pma_index).activity_line_id;
1486 l_sch_tbl(l_sch_index).stream_line_id := l_pmlrulvrec_Out.id;
1487 --02/12 l_sch_tbl(l_sch_index).program_id := p_sch_tbl(l_sch_indexin).program_id;
1488 l_sch_index :=l_sch_index+1;
1489 END IF;
1490 l_sch_indexin := l_sch_indexin +1;
1491
1492 END LOOP;
1493 OKS_PMS_PVT.insert_row(
1494 p_api_version => l_api_version,
1495 p_init_msg_list => l_init_msg_list,
1496 x_return_status => l_return_status ,
1497 x_msg_count => l_msg_count ,
1498 x_msg_data => l_msg_data ,
1499 p_oks_pm_schedules_v_tbl => l_sch_tbl,
1500 x_oks_pm_schedules_v_tbl =>l_sch_tbl_out);
1501 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS
1502 THEN
1503 OKC_API.set_message
1504 (G_APP_NAME,
1505 G_REQUIRED_VALUE,
1506 G_COL_NAME_TOKEN,
1507 'create activity schedules');
1508 Raise G_EXCEPTION_HALT_VALIDATION;
1509 END IF;
1510 --03/11 added to update activity schedule exists to 'Y'
1511 OPEN cu_obj_version(p_pma_tbl(l_pma_index).activity_line_id);
1512 FETCH cu_obj_version INTO l_object_version_number;
1513 CLOSE cu_obj_version;
1514 l_pmarulv_rec.id :=p_pma_tbl(l_pma_index).activity_line_id;
1515 l_pmarulv_rec.SCH_EXISTS_YN := 'Y';
1516 l_pmarulv_rec.OBJECT_VERSION_NUMBER:=l_object_version_number;
1517 oks_pma_pvt.update_row(
1518 p_api_version => l_api_version,
1519 p_init_msg_list => l_init_msg_list,
1520 x_return_status =>l_return_status,
1521 x_msg_count =>l_msg_count,
1522 x_msg_data =>l_msg_data,
1523 p_pmav_rec =>l_pmarulv_rec,
1524 x_pmav_rec =>l_pmarulv_rec_Out);
1525 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS
1526 THEN
1527 OKC_API.set_message
1528 (G_APP_NAME,
1529 G_REQUIRED_VALUE,
1530 G_COL_NAME_TOKEN,
1531 'update schedule exists');
1532 Raise G_EXCEPTION_HALT_VALIDATION;
1533 END IF;
1534
1535 END IF; --end of schedules
1536 l_pml_indexin:=l_pml_indexin+1;
1537 END LOOP;
1538 l_pma_index:=l_pma_index+1;
1539 l_pml_index:=l_pml_index+1;
1540 END LOOP;
1541 x_return_status := l_return_status;
1542 EXCEPTION
1543 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1544 x_return_status := l_return_status ;
1545
1546 WHEN OTHERS THEN
1547 OKC_API.SET_MESSAGE(
1548 p_app_name => g_app_name,
1549 p_msg_name => g_unexpected_error,
1550 p_token1 => g_sqlcode_token,
1551 p_token1_value => sqlcode,
1552 p_token2 => g_sqlerrm_token,
1553 p_token2_value => sqlerrm);
1554 -- notify caller of an error as UNEXPETED error
1555 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1556 x_msg_count :=l_msg_count;
1557
1558 END POPULATE_SCHEDULE;
1559
1560
1561 /* Logic for adjust - rules rearchitecture
1562 *** Populate a table of records with id for oks_k_lines or oks_pm_activities and type
1563 *** Loop through the table of records
1564 ***Logic for adjusting schedule dates remains the same
1565 */
1566 /*
1567
1568 Possible input parameters combinations:
1569 1. p_new_start_date NOT NULL and p_new_end_date NOT NULL
1570 2. p_new_start_date NOT NULL and p_new_end_date NULL
1571 3. p_new_start_date NULL and p_new_end_date NOT NULL
1572
1573 Possible scenarios For input combination 1 :
1574
1575 1. p_new_start_date and p_new_end_date are such that they fall
1576 in between two consecutive schedules without touching ,after the last schedule or,
1577 before the first schedule.
1578
1579
1580 2. p_new_start_date and p_new_end_date are such that they fall
1581 in between two schedules or, is within a schedule and p_new_start_date is less than
1582 the schedule_date_from of the leading schedule and p_new_end_date is less than
1583 the schedule_date_to of the trailing schedule or, of the leading schedule itself.
1584
1585 solution:
1586
1587 new leading schedule means the schedule for which p_new_start_date <= schedule_date_to/schedule_date
1588 new trailing schedule means the schedule for which p_new_end_date >= schedule_date_from/schedule_date
1589
1590 delete all schedules and rules if new effectivity does not touch any of the existing schedules
1591 thereafter create a rule and a schedules based on the old rule nearest to the new effectivity
1592 and exit.
1593
1594 delete all schedules till new leading schedule and after new trailing schedule
1595
1596 delete all rules till rule of new leading schedule and rule after rule of new trailing schedule
1597
1598 update the new leading rule start date and the new trailing rule end date
1599
1600 update the new leading rule offset for all rules
1601 if p_new_start_date < new leading schedule
1602 (except when new leading schedule is first schedule of the first rule and the rule is autoscheduled)
1603
1604 create schedules and the rule offset by calculating backdates based on the first rule info and the p_new_start_date
1605 if p_new_start_date < new leading schedule and
1606 new leading schedule is first schedule of the first rule and the rule is autoscheduled
1607
1608 thereafter change the schedule sequences and the rule periods based on new number of
1609 schedules for the rule
1610
1611 create schedules by calculating forward dates based on the last rule info and the p_new_end_date
1612 if p_new_end_date > new trailing schedule and
1613 new trailing schedule is last schedule of the last rule and the rule is autoscheduled
1614
1615 thereafter change rule periods based on new number of schedules for the rule
1616
1617 update new leading schedule schedule_date_from
1618 if p_new_start_date >= new leading schedule schedule_date_from
1619
1620 therafter if not first schedule of the rule
1621 update schedule sequence for all the schedules of the rule for the leading schedule
1622 update periods for the rule of the new leading schedule
1623
1624 update new trailing schedule schedule_date_to
1625 if p_new_end_date <= new trailing schedule schedule_date_to
1626
1627 therafter if not last schedule of the rule
1628 update periods for the rule of the new trailing schedule
1629
1630 */
1631 PROCEDURE ADJUST_PM_PROGRAM_SCHEDULE
1632 (p_api_version IN NUMBER,
1633 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1634 p_contract_line_id IN NUMBER,
1635 p_new_start_date IN DATE,
1636 p_new_end_date IN DATE,
1637 x_return_status OUT NOCOPY VARCHAR2,
1638 x_msg_count OUT NOCOPY NUMBER,
1639 x_msg_data OUT NOCOPY VARCHAR2)
1640 IS
1641
1642 CURSOR CU_CLE_ID is
1643 select id
1644 from okc_k_lines_b
1645 where id=p_contract_line_id;
1646 -- and lse_id in (2,15,20); commented by jvorugan as pm is associated with contract line
1647 CR_CLE_ID CU_CLE_ID%ROWTYPE;
1648 --not used
1649 /*CURSOR CU_STREAM_LEVELS IS
1650 SELECT SEQUENCE_NUMBER ,
1651 NUMBER_OF_OCCURENCES ,
1652 START_DATE ,
1653 END_DATE ,
1654 FREQUENCY ,
1655 FREQUENCY_UOM ,
1656 OFFSET_DURATION ,
1657 OFFSET_UOM,
1658 AUTOSCHEDULE_YN,
1659 PROGRAM_ID ,
1660 ACTIVITY_LINE_ID,
1661 CLE_ID
1662 DNZ_CHR_ID
1663 FROM OKS_PM_STREAM_LEVELS_V PML
1664 WHERE CLE_ID=(select id from okc_k_lines_b where cle_id=p_contract_line_id
1665 and lse_id in (2,15,20));
1666
1667 CR_STREAM_LEVELS CU_STREAM_LEVELS%ROWTYPE;*/
1668
1669 CURSOR CU_NewCovDet IS
1670 SELECT ID,
1671 START_DATE, --COV.START_DATE START_DATE,
1672 END_DATE --COV.END_DATE END_DATE,
1673 FROM OKC_K_LINES_B
1674 WHERE ID = p_contract_line_id;
1675 -- and lse_id in (2,15,20); commented by jvorugan as pm is associated with contract line
1676
1677 CR_NewCovDet Cu_NewCovDet%ROWTYPE;
1678
1679 CURSOR Cu_NewPMSch(cp_stream_line_id IN NUMBER) IS
1680 SELECT ID,
1681 RULE_ID,
1682 PMA_RULE_ID,
1683 PMP_RULE_ID,
1684 OBJECT_VERSION_NUMBER,
1685 DNZ_CHR_ID,
1686 CLE_ID,
1687 SCH_SEQUENCE,
1688 SCHEDULE_DATE,
1689 SCHEDULE_DATE_FROM,
1690 SCHEDULE_DATE_TO,
1691 CREATED_BY,
1692 CREATION_DATE,
1693 LAST_UPDATED_BY,
1694 LAST_UPDATE_DATE,
1695 LAST_UPDATE_LOGIN,
1696 ACTIVITY_LINE_ID ,
1697 STREAM_LINE_ID ,
1698 SECURITY_GROUP_ID ,
1699 PROGRAM_APPLICATION_ID ,
1700 PROGRAM_ID ,
1701 PROGRAM_UPDATE_DATE,
1702 REQUEST_ID
1703 FROM OKS_PM_SCHEDULES
1704 WHERE STREAM_LINE_ID=cp_stream_line_id;
1705
1706 CR_NewPMSch Cu_NewPMSch%ROWTYPE;
1707 --not used
1708 /* CURSOR Cu_PMSch IS
1709 SELECT ID,
1710 RULE_ID,
1711 PMA_RULE_ID,
1712 PMP_RULE_ID,
1713 OBJECT_VERSION_NUMBER,
1714 DNZ_CHR_ID,
1715 CLE_ID,
1716 SCH_SEQUENCE,
1717 SCHEDULE_DATE,
1718 SCHEDULE_DATE_FROM,
1719 SCHEDULE_DATE_TO,
1720 CREATED_BY,
1721 CREATION_DATE,
1722 LAST_UPDATED_BY,
1723 LAST_UPDATE_DATE,
1724 LAST_UPDATE_LOGIN,
1725 ACTIVITY_LINE_ID ,
1726 STREAM_LINE_ID ,
1727 SECURITY_GROUP_ID ,
1728 PROGRAM_APPLICATION_ID ,
1729 PROGRAM_ID ,
1730 PROGRAM_UPDATE_DATE,
1731 REQUEST_ID
1732 FROM OKS_PM_SCHEDULES
1733 WHERE CLE_ID =(select id from okc_k_lines_b where cle_id=p_contract_line_id
1734 and lse_id in (2,15,20))
1735 order by nvl(schedule_date,schedule_date_from);*/
1736
1737 CURSOR Cu_PMSch_PMPPML IS
1738 SELECT ID,
1739 RULE_ID,
1740 PMA_RULE_ID,
1741 PMP_RULE_ID,
1742 OBJECT_VERSION_NUMBER,
1743 DNZ_CHR_ID,
1744 CLE_ID,
1745 SCH_SEQUENCE,
1746 SCHEDULE_DATE,
1747 SCHEDULE_DATE_FROM,
1748 SCHEDULE_DATE_TO,
1749 CREATED_BY,
1750 CREATION_DATE,
1751 LAST_UPDATED_BY,
1752 LAST_UPDATE_DATE,
1753 LAST_UPDATE_LOGIN,
1754 ACTIVITY_LINE_ID ,
1755 STREAM_LINE_ID ,
1756 SECURITY_GROUP_ID ,
1757 PROGRAM_APPLICATION_ID ,
1758 PROGRAM_ID ,
1759 PROGRAM_UPDATE_DATE,
1760 REQUEST_ID
1761 FROM OKS_PM_SCHEDULES
1762 WHERE CLE_ID= p_contract_line_id --(select id from okc_k_lines_b where cle_id=p_contract_line_id
1763 --and lse_id in (2,15,20)) commented by jvorugan as pm is associated with contract line
1764 and ACTIVITY_LINE_ID is null
1765 order by nvl(schedule_date,schedule_date_from);
1766
1767 CURSOR Cu_PMSch_PMAPML(P_ACTIVITY_LINE_ID IN NUMBER) IS
1768 SELECT ID,
1769 RULE_ID,
1770 PMA_RULE_ID,
1771 PMP_RULE_ID,
1772 OBJECT_VERSION_NUMBER,
1773 DNZ_CHR_ID,
1774 CLE_ID,
1775 SCH_SEQUENCE,
1776 SCHEDULE_DATE,
1777 SCHEDULE_DATE_FROM,
1778 SCHEDULE_DATE_TO,
1779 CREATED_BY,
1780 CREATION_DATE,
1781 LAST_UPDATED_BY,
1782 LAST_UPDATE_DATE,
1783 LAST_UPDATE_LOGIN,
1784 ACTIVITY_LINE_ID ,
1785 STREAM_LINE_ID ,
1786 SECURITY_GROUP_ID ,
1787 PROGRAM_APPLICATION_ID ,
1788 PROGRAM_ID ,
1789 PROGRAM_UPDATE_DATE,
1790 REQUEST_ID
1791 FROM OKS_PM_SCHEDULES
1792 WHERE ACTIVITY_LINE_ID = P_ACTIVITY_LINE_ID
1793 order by nvl(schedule_date,schedule_date_from);
1794 --CK RUL REVISIT THIS
1795 CURSOR CU_PROGRAM IS
1796 SELECT id,'PMP' TYPE
1797 FROM OKS_K_LINES_B
1798 WHERE CLE_ID= p_contract_line_id;--(select id from okc_k_lines_b where cle_id=p_contract_line_id
1799 -- and lse_id in (2,15,20)); commented by jvorugan as pm is associated with contract line
1800
1801 CR_PROGRAM CU_PROGRAM%ROWTYPE;
1802
1803 CURSOR CU_ACTIVITIES IS
1804 SELECT id,'PMA' TYPE
1805 FROM OKS_PM_ACTIVITIES
1806 WHERE CLE_ID= p_contract_line_id;
1807 -- and lse_id in (2,15,20)); commented by jvorugan as pm is associated with contract line
1808
1809 CR_ACTIVITIES CU_ACTIVITIES%ROWTYPE;
1810
1811
1812 CURSOR CU_PM_STREAM_LEVELS IS
1813 SELECT
1814 ID,
1815 SEQUENCE_NUMBER ,
1816 NUMBER_OF_OCCURENCES ,
1817 START_DATE ,
1818 END_DATE ,
1819 FREQUENCY ,
1820 FREQUENCY_UOM ,
1821 OFFSET_DURATION ,
1822 OFFSET_UOM,
1823 AUTOSCHEDULE_YN,
1824 --02/12 PROGRAM_ID ,
1825 ACTIVITY_LINE_ID,
1826 CLE_ID,
1827 DNZ_CHR_ID,
1828 OBJECT_VERSION_NUMBER
1829 FROM OKS_PM_STREAM_LEVELS_V PML
1830 WHERE CLE_ID=p_contract_line_id
1831 -- and lse_id in (2,15,20)) commented by jvorugan as pm is associated with contract line
1832 AND ACTIVITY_LINE_ID IS NULL
1833 ORDER BY ACTIVITY_LINE_ID,SEQUENCE_NUMBER;
1834
1835 CURSOR CU_PMA_STREAM_LEVELS(cp_activity_line_id IN NUMBER) IS
1836 SELECT
1837 ID,
1838 SEQUENCE_NUMBER ,
1839 NUMBER_OF_OCCURENCES ,
1840 START_DATE ,
1841 END_DATE ,
1842 FREQUENCY ,
1843 FREQUENCY_UOM ,
1844 OFFSET_DURATION ,
1845 OFFSET_UOM,
1846 AUTOSCHEDULE_YN,
1847 --02/12 PROGRAM_ID ,
1848 ACTIVITY_LINE_ID,
1849 CLE_ID,
1850 DNZ_CHR_ID,
1851 OBJECT_VERSION_NUMBER
1852 FROM OKS_PM_STREAM_LEVELS_V PML
1853 WHERE CLE_ID=p_contract_line_id
1854 -- and lse_id in (2,15,20)) commented by jvorugan as pm is associated with contract line
1855 AND ACTIVITY_LINE_ID = cp_activity_line_id
1856 ORDER BY ACTIVITY_LINE_ID,SEQUENCE_NUMBER;
1857
1858
1859 l_pm_tbl pm_tbl_type;
1860 l_pmlrulv_tbl OKS_PML_PVT.pmlv_tbl_type;
1861 l_rulv_tbl_in OKS_PML_PVT.pmlv_tbl_type;
1862 l_rulv_Tbl_Out OKS_PML_PVT.pmlv_tbl_type;
1863 l_pmlrulv_tbl_del OKS_PML_PVT.pmlv_tbl_type;
1864
1865 -- l_pmlrulv_tbl_in OKC_RULE_PUB.rulv_tbl_type;
1866 -- l_pmlrulv_tbl_out OKC_RULE_PUB.rulv_tbl_type;
1867 l_pmlrulv_tbl_ins OKS_PML_PVT.pmlv_tbl_type;
1868 l_pmlrulv_tbl_ins_out OKS_PML_PVT.pmlv_tbl_type;
1869 l_pmlrulv_tbl_upd OKS_PML_PVT.pmlv_tbl_type;
1870 l_pmlrulv_tbl_upd_out OKS_PML_PVT.pmlv_tbl_type;
1871 l_pmlrulv_tbl_rfr OKS_PML_PVT.pmlv_tbl_type;
1872 l_pmlrulv_tbl_start OKS_PML_PVT.pmlv_tbl_type;
1873 l_pmlrulv_tbl_end OKS_PML_PVT.pmlv_tbl_type;
1874
1875
1876
1877 l_rule_act_tbl OKS_PM_PROGRAMS_PVT.rule_act_tbl;
1878
1879 pml_ctr NUMBER :=0;
1880 pml_del_ctr NUMBER :=0;
1881 pml_upd_ctr NUMBER :=0;
1882 pms_del_ctr NUMBER :=0;
1883 pms_upd_ctr NUMBER :=0;
1884 pms_ins_ctr NUMBER :=0;
1885 pml_rfr_ctr NUMBER :=0;
1886 pml_ins_ctr NUMBER :=0;
1887 pml_deleted varchar2(1);
1888 pms_delrec_exists varchar2(1);
1889 l_msg_index NUMBER;
1890 l_start_rule_seq NUMBER ;
1891 l_end_rule_seq NUMBER ;
1892 l_first_rule_id NUMBER :=0;
1893 l_last_rule_id NUMBER :=0;
1894 l_pmschv_end_lst_seq NUMBER :=0;
1895 l_duration number;
1896 l_timeunit varchar2(30);
1897 l_next_sch_seq number;
1898
1899
1900 l_pms_tbl pms_tbl_type;
1901 l_row_cnt NUMBER;
1902 l_api_version CONSTANT NUMBER := 1.0;
1903 l_init_msg_list CONSTANT VARCHAR2(1):= 'F';
1904 l_return_status VARCHAR2(1):= 'S';
1905 l_msg_count NUMBER;
1906 l_msg_data VARCHAR2(2000):=null;
1907 -- l_pmprulvrec_Out OKC_RULE_PUB.rulv_rec_type;
1908 -- l_pmlrulvrec_Out OKC_RULE_PUB.rulv_rec_type;
1909 l_pmschvtbl_In OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
1910 l_pmschvtbl_Out OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
1911 l_cov_rec okc_contract_pub.clev_rec_type;
1912 l_pmschvtbl_Del OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
1913 l_pmschvtbl_Upd OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
1914 l_pmschvtbl_Upd_out OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
1915 l_pmschvrec_prv OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
1916 l_pmschv_start OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
1917 l_pmschv_end OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
1918 l_pmschvtbl_Ins OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
1919 l_pmschvtbl_Ins_out OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
1920 l_pmschv_tbl OKS_PMS_PVT.oks_pm_schedules_v_tbl_type;
1921
1922
1923
1924 l_Rule_Id NUMBER;
1925 k NUMBER := 0;
1926 l_frst_sch_date DATE;
1927 l_last_sch_date DATE;
1928 l_prev_sch_date DATE;
1929 l_next_sch_date DATE;
1930
1931 l_sort_ret_status VARCHAR2(1) := 'S';
1932 x_sch_ret_status VARCHAR2(1) := 'S';
1933 x_periods NUMBER;
1934 x_pms_tbl pms_tbl_type;
1935 l_start_date DATE;
1936 x_last_date DATE;
1937 l_first_sch_date DATE;
1938 l_sch_end_date DATE;
1939 c_rgp_id NUMBER;
1940 c_chr_id NUMBER;
1941
1942 --CK RUL Add init procedure here for stream level record
1943 l_pm_ctr NUMBER;
1944 l_cle_id NUMBER;
1945 BEGIN
1946 IF (G_DEBUG_ENABLED = 'Y') THEN
1947 okc_debug.Set_Indentation('Adjust_PM_Program_Schedule');
1948 okc_debug.log('Entered Adjust_PM_Program_Schedule', 3);
1949 END IF;
1950 --CK RUL
1951 OPEN CU_CLE_ID;
1952 LOOP
1953 FETCH CU_CLE_ID INTO CR_CLE_ID;
1954 EXIT WHEN CU_CLE_ID%NOTFOUND;
1955 l_cle_id := cr_cle_id.id;
1956 END LOOP;
1957 CLOSE CU_CLE_ID;
1958 FOR CR_NewCovDet IN CU_NewCovDet
1959 LOOP
1960
1961 l_cov_rec.id :=CR_NewCovDet.Id;
1962 l_cov_rec.Start_Date :=CR_NewCovDet.Start_Date;
1963 l_cov_rec.End_Date :=CR_NewCovDet.End_Date;
1964 l_pm_ctr:=1;
1965 FOR CR_PROGRAM IN CU_PROGRAM LOOP
1966 l_pm_tbl(l_pm_ctr) :=cr_program;
1967 l_pm_ctr :=l_pm_ctr+1;
1968 END LOOP;
1969 FOR CR_ACTIVITIES IN CU_ACTIVITIES LOOP
1970 l_pm_tbl(l_pm_ctr) :=cr_activities;
1971 l_pm_ctr :=l_pm_ctr+1;
1972 END LOOP;
1973 --pml_ctr :=1;
1974 l_pmschv_start.DELETE;
1975 l_pmschv_end.DELETE;
1976 k := 0;
1977 FOR i in 1..l_pm_tbl.count LOOP
1978 --CK 09/18
1979
1980 pml_ctr :=0;
1981 pml_del_ctr :=0;
1982 pml_upd_ctr :=0;
1983 pms_del_ctr :=0;
1984 pms_upd_ctr :=0;
1985 pms_ins_ctr :=0;
1986 pml_rfr_ctr :=0;
1987 pml_ins_ctr :=0;
1988
1989 l_rulv_tbl_in.delete;
1990 l_pmlrulv_tbl.delete;
1991 -- l_pmlrulv_tbl_in.delete;
1992 -- l_pmlrulv_tbl_out.delete;
1993 l_pmlrulv_tbl_del.delete;
1994 l_pmlrulv_tbl_ins.delete;
1995 l_pmlrulv_tbl_ins_out.delete;
1996 l_pmlrulv_tbl_upd.delete;
1997 l_pmlrulv_tbl_upd_out.delete;
1998 l_pmlrulv_tbl_rfr.delete;
1999 l_pmlrulv_tbl_start.delete;
2000 l_pmlrulv_tbl_end.delete;
2001
2002 l_start_rule_seq :=0;
2003 l_end_rule_seq :=0;
2004 l_first_rule_id :=0;
2005 l_last_rule_id :=0;
2006 l_pmschv_end_lst_seq :=0;
2007 l_duration :=null;
2008 l_timeunit :=null;
2009 l_next_sch_seq :=0;
2010
2011 l_pms_tbl.delete;
2012 l_row_cnt :=0;
2013
2014 l_rulv_Tbl_In.delete;
2015 l_rulv_Tbl_Out.delete;
2016 l_pmschvtbl_In.delete;
2017 l_pmschvtbl_Out.delete;
2018 -- l_cov_rec.delete;
2019 l_pmschvtbl_Del.delete;
2020 l_pmschvtbl_Upd.delete;
2021 l_pmschvtbl_Upd_out.delete;
2022 l_pmschv_start.delete;
2023 l_pmschv_end.delete;
2024 l_pmschvtbl_Ins.delete;
2025 l_pmschvtbl_Ins_out.delete;
2026 l_pmschv_tbl.delete;
2027
2028 k := 0;
2029 l_prev_sch_date := null;
2030 l_next_sch_date := null;
2031
2032 l_first_sch_date := null;
2033
2034
2035
2036
2037 --ck 09/18
2038 if l_pm_tbl(i).type = 'PMP' then
2039
2040
2041 FOR CR_PM_STREAM_LEVELS IN CU_PM_STREAM_LEVELS
2042 LOOP
2043 -- Init_Rulv(l_rulv_tbl_in);
2044 pml_ctr :=pml_ctr+1;
2045 l_rulv_tbl_in(pml_ctr).ID := cr_pm_stream_levels.ID;
2046 l_rulv_tbl_in(pml_ctr).SEQUENCE_NUMBER := cr_pm_stream_levels.SEQUENCE_NUMBER;
2047 l_rulv_tbl_in(pml_ctr).NUMBER_OF_OCCURENCES := cr_pm_stream_levels.NUMBER_OF_OCCURENCES;
2048 l_rulv_tbl_in(pml_ctr).START_DATE := cr_pm_stream_levels.START_DATE;
2049 l_rulv_tbl_in(pml_ctr).END_DATE := cr_pm_stream_levels.END_DATE;
2050 l_rulv_tbl_in(pml_ctr).FREQUENCY := cr_pm_stream_levels.FREQUENCY;
2051 l_rulv_tbl_in(pml_ctr).FREQUENCY_UOM := cr_pm_stream_levels.FREQUENCY_UOM;
2052 l_rulv_tbl_in(pml_ctr).OFFSET_DURATION := cr_pm_stream_levels.OFFSET_DURATION;
2053 l_rulv_tbl_in(pml_ctr).OFFSET_UOM := cr_pm_stream_levels.OFFSET_UOM;
2054 l_rulv_tbl_in(pml_ctr).AUTOSCHEDULE_YN := cr_pm_stream_levels.AUTOSCHEDULE_YN;
2055 --02/12 l_rulv_tbl_in(pml_ctr).PROGRAM_ID := cr_pm_stream_levels.PROGRAM_ID;
2056 l_rulv_tbl_in(pml_ctr).CLE_ID := l_cle_id;
2057 l_rulv_tbl_in(pml_ctr).DNZ_CHR_ID := cr_pm_stream_levels.DNZ_CHR_ID;
2058 l_rulv_tbl_in(pml_ctr).OBJECT_VERSION_NUMBER:= cr_pm_stream_levels.OBJECT_VERSION_NUMBER;
2059 END LOOP;
2060 elsif l_pm_tbl(i).type = 'PMA' then
2061
2062 FOR CR_PMA_STREAM_LEVELS IN CU_PMA_STREAM_LEVELS(l_pm_tbl(i).id)
2063 LOOP
2064 pml_ctr :=pml_ctr+1;
2065 l_rulv_tbl_in(pml_ctr).ID := cr_pma_stream_levels.ID;
2066 l_rulv_tbl_in(pml_ctr).SEQUENCE_NUMBER := cr_pma_stream_levels.SEQUENCE_NUMBER;
2067 l_rulv_tbl_in(pml_ctr).NUMBER_OF_OCCURENCES := cr_pma_stream_levels.NUMBER_OF_OCCURENCES;
2068 l_rulv_tbl_in(pml_ctr).START_DATE := cr_pma_stream_levels.START_DATE;
2069 l_rulv_tbl_in(pml_ctr).END_DATE := cr_pma_stream_levels.END_DATE;
2070 l_rulv_tbl_in(pml_ctr).FREQUENCY := cr_pma_stream_levels.FREQUENCY;
2071 l_rulv_tbl_in(pml_ctr).FREQUENCY_UOM := cr_pma_stream_levels.FREQUENCY_UOM;
2072 l_rulv_tbl_in(pml_ctr).OFFSET_DURATION := cr_pma_stream_levels.OFFSET_DURATION;
2073 l_rulv_tbl_in(pml_ctr).OFFSET_UOM := cr_pma_stream_levels.OFFSET_UOM;
2074 l_rulv_tbl_in(pml_ctr).AUTOSCHEDULE_YN := cr_pma_stream_levels.AUTOSCHEDULE_YN;
2075 --02/12 l_rulv_tbl_in(pml_ctr).PROGRAM_ID := cr_pma_stream_levels.PROGRAM_ID;
2076 l_rulv_tbl_in(pml_ctr).ACTIVITY_LINE_ID := cr_pma_stream_levels.ACTIVITY_LINE_ID;
2077 l_rulv_tbl_in(pml_ctr).CLE_ID := l_cle_id;
2078 l_rulv_tbl_in(pml_ctr).DNZ_CHR_ID := cr_pma_stream_levels.DNZ_CHR_ID;
2079 l_rulv_tbl_in(pml_ctr).OBJECT_VERSION_NUMBER:= cr_pma_stream_levels.OBJECT_VERSION_NUMBER;
2080
2081
2082
2083 END LOOP;
2084
2085 end if; --ck 09/15
2086
2087
2088 l_pmschv_start.DELETE;
2089 l_pmschv_end.DELETE;
2090
2091 k := 0;
2092
2093 if l_pm_tbl(i).type= 'PMP' then
2094
2095
2096 FOR CR_PMSch_PMPPML IN Cu_PMSch_PMPPML LOOP
2097
2098 --To get first schedule in the range of schedules to be updated
2099 IF trunc(p_new_start_Date) IS NOT NULL AND
2100 trunc(p_new_start_Date) <= nvl(CR_PMSch_PMPPML.schedule_date,CR_PMSch_PMPPML.schedule_date_to) THEN
2101 IF l_pmschv_start.COUNT = 0 THEN
2102 l_pmschv_start(1) := CR_PMSch_PMPPML;
2103 END IF;
2104 END IF;
2105
2106 --To get last schedule in the range of schedules to be updated
2107 IF trunc(p_new_end_Date) IS NOT NULL AND
2108 trunc(p_new_end_Date) >= nvl(CR_PMSch_PMPPML.schedule_date,CR_PMSch_PMPPML.schedule_date_from) THEN
2109 l_pmschv_end.DELETE;
2110 l_pmschv_end(1) := CR_PMSch_PMPPML;
2111 END IF;
2112
2113 k := k + 1;
2114 l_pmschv_tbl(k) := CR_PMSch_PMPPML;
2115
2116 END LOOP;
2117 elsif l_pm_tbl(i).type= 'PMA' then
2118
2119
2120 FOR Cr_PMSch_PMAPML in Cu_PMSch_PMAPML(l_pm_tbl(i).Id) LOOP
2121
2122
2123 --To get first schedule in the range of schedules to be updated
2124 IF trunc(p_new_start_Date) IS NOT NULL AND
2125 trunc(p_new_start_Date) <= nvl(Cr_PMSch_PMAPML.schedule_date,Cr_PMSch_PMAPML.schedule_date_to) THEN
2126 IF l_pmschv_start.COUNT = 0 THEN
2127 l_pmschv_start(1) := Cr_PMSch_PMAPML;
2128 END IF;
2129 END IF;
2130
2131 --To get last schedule in the range of schedules to be updated
2132 IF trunc(p_new_end_Date) IS NOT NULL AND
2133 trunc(p_new_end_Date) >= nvl(Cr_PMSch_PMAPML.schedule_date,Cr_PMSch_PMAPML.schedule_date_from) THEN
2134 l_pmschv_end.DELETE;
2135 l_pmschv_end(1) := Cr_PMSch_PMAPML;
2136 END IF;
2137
2138 k := k + 1;
2139 l_pmschv_tbl(k) := Cr_PMSch_PMAPML;
2140
2141 END LOOP;
2142
2143 end if;
2144 -- added new dtd feb 13 2003 ends
2145
2146 k := 0;
2147
2148 l_pmschv_end_lst_seq := -99;
2149 IF l_pmschv_end.COUNT > 0 THEN
2150 FOR Cr_NewPMSch in Cu_NewPMSch(l_pmschv_end(1).stream_line_id) LOOP
2151
2152 l_pmschv_end_lst_seq := Cr_NewPMSch.sch_sequence;
2153 END LOOP;
2154 END IF;
2155
2156 l_start_rule_seq := NULL;
2157 l_end_rule_seq := NULL;
2158 l_pmlrulv_tbl_start.DELETE;
2159 l_pmlrulv_tbl_end.DELETE;
2160
2161
2162 IF l_rulv_tbl_in.COUNT > 0 THEN
2163 FOR i in l_rulv_tbl_in.FIRST..l_rulv_tbl_in.LAST LOOP
2164
2165 IF trunc(p_new_start_date) IS NOT NULL AND l_pmschv_start.COUNT =1 AND l_pmschv_start(1).STREAM_LINE_id = l_rulv_tbl_in(i).id THEN
2166 l_start_rule_seq := l_rulv_tbl_in(i).SEQUENCE_NUMBER;
2167 l_pmlrulv_tbl_start(1) := l_rulv_tbl_in(i);
2168 END IF;
2169
2170 IF trunc(p_new_end_date) IS NOT NULL AND l_pmschv_end.COUNT =1 AND l_pmschv_end(1).STREAM_LINE_id = l_rulv_tbl_in(i).id THEN
2171 l_end_rule_seq := l_rulv_tbl_in(i).SEQUENCE_NUMBER;
2172 l_pmlrulv_tbl_end(1) := l_rulv_tbl_in(i);
2173 END IF;
2174
2175 IF i = l_rulv_tbl_in.FIRST THEN
2176 l_first_rule_id := l_rulv_tbl_in(i).id;
2177 END IF;
2178 IF i = l_rulv_tbl_in.LAST THEN
2179 l_last_rule_id := l_rulv_tbl_in(i).id;
2180 END IF;
2181
2182 END LOOP;
2183 END IF;
2184
2185
2186 IF l_rulv_tbl_in.COUNT > 0 THEN
2187 IF trunc(p_new_start_date) IS NOT NULL AND trunc(p_new_end_date) IS NOT NULL THEN
2188 IF (l_start_rule_seq IS NOT NULL AND
2189 l_end_rule_seq IS NOT NULL AND
2190 l_start_rule_seq = l_end_rule_seq AND
2191 l_pmschv_start(1).sch_sequence > l_pmschv_end(1).sch_sequence) --Range of relevent schedules contain no schedule
2192 OR
2193 (l_start_rule_seq IS NOT NULL AND
2194 l_end_rule_seq IS NOT NULL AND
2195 l_start_rule_seq <> l_end_rule_seq AND
2196 l_start_rule_seq > l_end_rule_seq) -- Case when start date and end date falls between 2 schedules (spanning rules)
2197 OR
2198 (l_start_rule_seq IS NOT NULL AND -- Case when start date and end date falls before all existing schedules
2199 l_end_rule_seq IS NULL)
2200 OR
2201 (l_start_rule_seq IS NULL AND --Case when start date and end date falls in future to all existing schedules
2202 l_end_rule_seq IS NOT NULL) THEN
2203
2204 pml_del_ctr := 0;
2205 pms_del_ctr := 0;
2206
2207
2208 FOR i in l_rulv_tbl_in.FIRST..l_rulv_tbl_in.LAST LOOP
2209 pml_del_ctr := pml_del_ctr + 1;
2210 l_pmlrulv_tbl_del(pml_del_ctr) := l_rulv_tbl_in(i);
2211 FOR NewPMSch_Rec in Cu_NewPMSch(l_rulv_tbl_in(i).id) LOOP
2212 pms_del_ctr := pms_del_ctr + 1;
2213 l_pmschvtbl_Del(pms_del_ctr) := NewPMSch_Rec;
2214 END LOOP;
2215 END LOOP;
2216 pml_rfr_ctr := 0;
2217 pml_rfr_ctr := pml_rfr_ctr + 1;
2218
2219 IF l_start_rule_seq IS NOT NULL THEN
2220 l_pmlrulv_tbl_rfr(pml_rfr_ctr) := l_pmlrulv_tbl_start(1);
2221 ELSIF l_end_rule_seq IS NOT NULL THEN
2222 l_pmlrulv_tbl_rfr(pml_rfr_ctr) := l_pmlrulv_tbl_end(1);
2223 END IF;
2224
2225 pms_ins_ctr := 1;
2226 pml_ins_ctr := 1;
2227 l_first_sch_date := NULL;
2228 l_next_sch_date := NULL;
2229
2230 l_first_sch_date := nvl(okc_time_util_pub.get_enddate(trunc(p_new_start_date),
2231 l_pmlrulv_tbl_rfr(pml_rfr_ctr).offset_uom,
2232 l_pmlrulv_tbl_rfr(pml_rfr_ctr).offset_duration) + 1,
2233 trunc(p_new_start_date));
2234
2235 IF l_pmschv_start.COUNT > 0 THEN
2236 l_pmschvtbl_ins(pms_ins_ctr) := l_pmschv_start(1);
2237 ELSE
2238 l_pmschvtbl_ins(pms_ins_ctr) := l_pmschv_end(1);
2239 END IF;
2240 IF l_first_sch_date > trunc(p_new_end_date) THEN
2241
2242 l_pmschvtbl_ins(pms_ins_ctr).schedule_date := trunc(p_new_end_date);
2243
2244 ELSE
2245 l_pmschvtbl_ins(pms_ins_ctr).schedule_date := l_first_sch_date;
2246
2247
2248 l_pmschvtbl_ins(pms_ins_ctr).sch_sequence := 1;
2249 l_next_sch_date := okc_time_util_pub.get_enddate(trunc(l_first_sch_date)+1,
2250 l_pmlrulv_tbl_rfr(pml_rfr_ctr).frequency_uom,
2251 l_pmlrulv_tbl_rfr(pml_rfr_ctr).frequency);
2252
2253 WHILE 1 = 1 LOOP
2254
2255 IF l_next_sch_date <= trunc(p_new_end_date) THEN
2256 pms_ins_ctr := pms_ins_ctr + 1;
2257 IF l_pmschv_start.COUNT > 0 THEN
2258 l_pmschvtbl_ins(pms_ins_ctr) := l_pmschv_start(1);
2259 ELSE
2260 l_pmschvtbl_ins(pms_ins_ctr) := l_pmschv_end(1);
2261 END IF;
2262 l_pmschvtbl_ins(pms_ins_ctr).schedule_date := l_next_sch_date;
2263 l_pmschvtbl_ins(pms_ins_ctr).sch_sequence := pms_ins_ctr;
2264 ELSE
2265 l_pmlrulv_tbl_ins(1) := l_pmlrulv_tbl_rfr(pml_rfr_ctr);
2266 l_pmlrulv_tbl_ins(1).id := NULL;
2267 l_pmlrulv_tbl_ins(1).number_of_occurences := pms_ins_ctr;
2268 l_pmlrulv_tbl_ins(1).start_date := p_new_start_date;
2269 l_pmlrulv_tbl_ins(1).end_date := p_new_end_date;
2270 l_pmlrulv_tbl_ins(1).autoschedule_yn := 'Y';
2271
2272 EXIT;
2273
2274 END IF;
2275 l_next_sch_date := okc_time_util_pub.get_enddate(trunc(l_next_sch_date)+1,
2276 l_pmlrulv_tbl_rfr(pml_rfr_ctr).FREQUENCY_UOM,
2277 l_pmlrulv_tbl_rfr(pml_rfr_ctr).FREQUENCY);
2278
2279 END LOOP;
2280
2281 END IF;
2282
2283 OKS_PMS_PVT.delete_row(
2284 p_api_version => l_api_version,
2285 p_init_msg_list => l_init_msg_list,
2286 x_return_status => l_return_status,
2287 x_msg_count => l_msg_count,
2288 x_msg_data => l_msg_data,
2289 p_oks_pm_schedules_v_tbl => l_pmschvtbl_Del);
2290 IF (G_DEBUG_ENABLED = 'Y') THEN
2291 okc_debug.log('After oks_pms_pvt delete_row', 3);
2292 END IF;
2293
2294 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2295 OKC_API.set_message
2296 (G_APP_NAME,
2297 G_REQUIRED_VALUE,
2298 G_COL_NAME_TOKEN,
2299 'delete pm schedules');
2300
2301 Raise G_EXCEPTION_HALT_VALIDATION;
2302 END IF;
2303
2304 oks_pml_pvt.delete_row(
2305 p_api_version => l_api_version,
2306 p_init_msg_list => l_init_msg_list,
2307 x_return_status => l_return_status,
2308 x_msg_count => l_msg_count,
2309 x_msg_data => l_msg_data,
2310 p_pmlv_tbl => l_pmlrulv_tbl_del);
2311
2312 IF (G_DEBUG_ENABLED = 'Y') THEN
2313 okc_debug.log('After oks_pml_pvt delete_row', 3);
2314 END IF;
2315
2316 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2317 OKC_API.set_message
2318 (G_APP_NAME,
2319 G_REQUIRED_VALUE,
2320 G_COL_NAME_TOKEN,
2321 'delete pml rules');
2322
2323 Raise G_EXCEPTION_HALT_VALIDATION;
2324 END IF;
2325
2326
2327 OKS_PMS_PVT.insert_row(
2328 p_api_version => l_api_version,
2329 p_init_msg_list => l_init_msg_list,
2330 x_return_status => l_return_status,
2331 x_msg_count => l_msg_count,
2332 x_msg_data => l_msg_data,
2333 p_oks_pm_schedules_v_tbl => l_pmschvtbl_ins,
2334 x_oks_pm_schedules_v_tbl => l_pmschvtbl_ins_out);
2335
2336 IF (G_DEBUG_ENABLED = 'Y') THEN
2337 okc_debug.log('After oks_pms_pvt insert_row', 3);
2338 END IF;
2339
2340 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2341 OKC_API.set_message
2342 (G_APP_NAME,
2343 G_REQUIRED_VALUE,
2344 G_COL_NAME_TOKEN,
2345 'insert pm schedules');
2346
2347 Raise G_EXCEPTION_HALT_VALIDATION;
2348 END IF;
2349
2350 oks_pml_pvt.insert_row(
2351 p_api_version => l_api_version,
2352 p_init_msg_list => l_init_msg_list,
2353 x_return_status => l_return_status,
2354 x_msg_count => l_msg_count,
2355 x_msg_data => l_msg_data,
2356 p_pmlv_tbl => l_pmlrulv_tbl_ins,
2357 x_pmlv_tbl => l_pmlrulv_tbl_ins_out);
2358
2359 IF (G_DEBUG_ENABLED = 'Y') THEN
2360 okc_debug.log('After oks_pml_pvt insert_row', 3);
2361 END IF;
2362 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2363 OKC_API.set_message
2364 (G_APP_NAME,
2365 G_REQUIRED_VALUE,
2366 G_COL_NAME_TOKEN,
2367 'insert pml rules');
2368
2369 Raise G_EXCEPTION_HALT_VALIDATION;
2370 END IF;
2371
2372
2373 l_next_sch_date := NULL;
2374 l_first_sch_date := NULL;
2375 pms_ins_ctr := 0;
2376 pml_ins_ctr := 0;
2377 pms_del_ctr := 0;
2378 pml_del_ctr := 0;
2379
2380 ELSE -- both start date and end date passed end
2381
2382 pms_del_ctr := 0;
2383 pml_del_ctr := 0;
2384 FOR i in l_rulv_tbl_in.FIRST..l_rulv_tbl_in.LAST LOOP
2385 IF to_number(l_rulv_tbl_in(i).SEQUENCE_NUMBER) < l_start_rule_seq OR
2386 to_number(l_rulv_tbl_in(i).SEQUENCE_NUMBER) > l_end_rule_seq THEN
2387 pml_del_ctr := pml_del_ctr + 1;
2388 l_pmlrulv_tbl_del(pml_del_ctr) := l_rulv_tbl_in(i);
2389 END IF;
2390 FOR CR_NewPMSch in Cu_NewPMSch(l_rulv_tbl_in(i).id) LOOP
2391 IF trunc(p_new_start_date) > nvl(CR_NewPMSch.schedule_date,CR_NewPMSch.schedule_date_to) OR
2392 trunc(p_new_end_date) < nvl(CR_NewPMSch.schedule_date,CR_NewPMSch.schedule_date_from) THEN
2393 pms_del_ctr := pms_del_ctr + 1;
2394 l_pmschvtbl_Del(pms_del_ctr) := CR_NewPMSch;
2395 END IF;
2396 END LOOP;
2397 END LOOP;
2398
2399 IF l_pmschvtbl_Del.COUNT > 0 THEN
2400
2401 OKS_PMS_PVT.delete_row(
2402 p_api_version => l_api_version,
2403 p_init_msg_list => l_init_msg_list,
2404 x_return_status => l_return_status,
2405 x_msg_count => l_msg_count,
2406 x_msg_data => l_msg_data,
2407 p_oks_pm_schedules_v_tbl => l_pmschvtbl_Del);
2408 IF (G_DEBUG_ENABLED = 'Y') THEN
2409 okc_debug.log('After oks_pms_pvt delete_row', 3);
2410 END IF;
2411 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2412 OKC_API.set_message
2413 (G_APP_NAME,
2414 G_REQUIRED_VALUE,
2415 G_COL_NAME_TOKEN,
2416 'delete pm schedules');
2417
2418 Raise G_EXCEPTION_HALT_VALIDATION;
2419 END IF;
2420
2421 END IF;
2422
2423 IF l_pmlrulv_tbl_del.COUNT > 0 THEN
2424 oks_pml_pvt.delete_row(
2425 p_api_version => l_api_version,
2426 p_init_msg_list => l_init_msg_list,
2427 x_return_status => l_return_status,
2428 x_msg_count => l_msg_count,
2429 x_msg_data => l_msg_data,
2430 p_pmlv_tbl => l_pmlrulv_tbl_del);
2431
2432 IF (G_DEBUG_ENABLED = 'Y') THEN
2433 okc_debug.log('After oks_pml_pvt delete_row', 3);
2434 END IF;
2435
2436 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2437 OKC_API.set_message
2438 (G_APP_NAME,
2439 G_REQUIRED_VALUE,
2440 G_COL_NAME_TOKEN,
2441 'delete pml rules');
2442
2443 Raise G_EXCEPTION_HALT_VALIDATION;
2444 END IF;
2445
2446 END IF;
2447 pms_del_ctr := 0;
2448 pml_del_ctr := 0;
2449
2450
2451 IF trunc(p_new_start_date) <= nvl(l_pmschv_start(1).schedule_date,l_pmschv_start(1).schedule_date_from) AND
2452 trunc(p_new_end_date) <= nvl(l_pmschv_end(1).schedule_date,l_pmschv_end(1).schedule_date_to) THEN
2453
2454
2455 IF NOT (l_pmschv_start(1).sch_sequence = 1 AND
2456 l_pmlrulv_tbl_start(1).id = l_first_rule_id AND
2457 l_pmlrulv_tbl_start(1).AUTOSCHEDULE_YN = 'Y') THEN
2458 IF trunc(p_new_start_date) <> trunc(nvl(l_pmschv_start(1).schedule_date,
2459 l_pmschv_start(1).schedule_date_from)) THEN
2460 okc_time_util_pub.get_duration(
2461 p_start_date => trunc(p_new_start_date),
2462 p_end_date => nvl(l_pmschv_start(1).schedule_date,
2463 l_pmschv_start(1).schedule_date_from) - 1,
2464 x_duration => l_duration,
2465 x_timeunit => l_timeunit,
2466 x_return_status => l_return_status);
2467
2468 IF NOT l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2469 RAISE G_EXCEPTION_HALT_VALIDATION;
2470 END IF;
2471
2472 ELSE
2473
2474 l_duration := NULL;
2475 l_timeunit := NULL;
2476
2477 END IF;
2478
2479 l_pmlrulv_tbl_start(1).OFFSET_DURATION := l_duration;
2480 l_pmlrulv_tbl_start(1).OFFSET_UOM := l_timeunit;
2481
2482 IF l_pmlrulv_tbl_start(1).id = l_pmlrulv_tbl_end(1).id THEN
2483
2484 l_pmlrulv_tbl_end(1).OFFSET_DURATION := l_duration;
2485 l_pmlrulv_tbl_end(1).OFFSET_UOM := l_timeunit;
2486
2487 END IF;
2488
2489 ELSIF (l_pmschv_start(1).sch_sequence = 1 AND
2490 l_pmlrulv_tbl_start(1).id = l_first_rule_id AND
2491 l_pmlrulv_tbl_start(1).AUTOSCHEDULE_YN = 'Y') THEN
2492
2493 l_pmschvtbl_Ins.DELETE;
2494 pms_ins_ctr := 0;
2495 pms_upd_ctr := 0;
2496
2497 l_prev_sch_date := trunc(okc_time_util_pub.get_enddate(l_pmschv_start(1).schedule_date - 1,
2498 l_pmlrulv_tbl_start(1).frequency_uom,
2499 -1*(to_number(l_pmlrulv_tbl_start(1).frequency))));
2500
2501 WHILE 1 = 1 LOOP
2502
2503 IF l_prev_sch_date >= trunc(p_new_start_date) THEN
2504
2505 pms_ins_ctr := pms_ins_ctr + 1;
2506 l_pmschvtbl_Ins(pms_ins_ctr) := l_pmschv_start(1);
2507 l_pmschvtbl_Ins(pms_ins_ctr).id := NULL;
2508 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date := l_prev_sch_date;
2509 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date_from := NULL;
2510 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date_to := NULL;
2511
2512 ELSE
2513
2514
2515 IF l_pmschvtbl_Ins.COUNT > 0 THEN
2516
2517 IF trunc(p_new_start_date) <> trunc(l_pmschvtbl_Ins(pms_ins_ctr).schedule_date) THEN
2518
2519 okc_time_util_pub.get_duration(
2520 p_start_date => trunc(p_new_start_date),
2521 p_end_date => l_pmschvtbl_Ins(pms_ins_ctr).schedule_date - 1,
2522 x_duration => l_duration,
2523 x_timeunit => l_timeunit,
2524 x_return_status => l_return_status);
2525
2526 ELSE
2527
2528 l_duration := NULL;
2529 l_timeunit := NULL;
2530
2531 END IF;
2532
2533
2534 ELSE
2535
2536 IF trunc(p_new_start_date) <> trunc(nvl(l_pmschv_start(1).schedule_date,
2537 l_pmschv_start(1).schedule_date_from)) THEN
2538
2539 okc_time_util_pub.get_duration(
2540 p_start_date => trunc(p_new_start_date),
2541 p_end_date => nvl(l_pmschv_start(1).schedule_date,
2542 l_pmschv_start(1).schedule_date_from) - 1,
2543 x_duration => l_duration,
2544 x_timeunit => l_timeunit,
2545 x_return_status => l_return_status);
2546
2547 ELSE
2548
2549 l_duration := NULL;
2550 l_timeunit := NULL;
2551
2552 END IF;
2553
2554 END IF;
2555
2556 IF NOT l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2557 RAISE G_EXCEPTION_HALT_VALIDATION;
2558 END IF;
2559
2560 l_pmlrulv_tbl_start(1).OFFSET_DURATION := l_duration;
2561 l_pmlrulv_tbl_start(1).OFFSET_UOM := l_timeunit;
2562
2563
2564 IF l_pmlrulv_tbl_start(1).id = l_pmlrulv_tbl_end(1).id THEN
2565
2566 l_pmlrulv_tbl_end(1).OFFSET_DURATION := l_duration;
2567 l_pmlrulv_tbl_end(1).OFFSET_UOM := l_timeunit;
2568
2569 END IF;
2570
2571 EXIT;
2572
2573 END IF;
2574
2575 l_prev_sch_date := trunc(okc_time_util_pub.get_enddate(l_prev_sch_date - 1,
2576 l_pmlrulv_tbl_start(1).frequency_uom,
2577 -1*(to_number(l_pmlrulv_tbl_start(1).frequency))));
2578
2579 END LOOP;
2580
2581 END IF;
2582
2583
2584 IF l_pmschvtbl_ins.COUNT > 0 THEN
2585
2586 OKS_PMS_PVT.insert_row(
2587 p_api_version => l_api_version,
2588 p_init_msg_list => l_init_msg_list,
2589 x_return_status => l_return_status,
2590 x_msg_count => l_msg_count,
2591 x_msg_data => l_msg_data,
2592 p_oks_pm_schedules_v_tbl => l_pmschvtbl_ins,
2593 x_oks_pm_schedules_v_tbl => l_pmschvtbl_ins_out);
2594 IF (G_DEBUG_ENABLED = 'Y') THEN
2595 okc_debug.log('After oks_pms_pvt insert_row', 3);
2596 END IF;
2597
2598 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2599 OKC_API.set_message
2600 (G_APP_NAME,
2601 G_REQUIRED_VALUE,
2602 G_COL_NAME_TOKEN,
2603 'inserting PM schedules');
2604
2605 Raise G_EXCEPTION_HALT_VALIDATION;
2606 END IF;
2607
2608 END IF;
2609 pms_ins_ctr := 0;
2610 pms_upd_ctr := 0;
2611 l_next_sch_seq := 0;
2612 l_pmschvtbl_Upd.DELETE;
2613
2614 FOR CR_NewPMSch in Cu_NewPMSch(l_pmlrulv_tbl_start(1).id) LOOP
2615 IF (l_pmschv_start(1).stream_line_id <> l_pmschv_end(1).stream_line_id)
2616 OR
2617 (l_pmschv_start(1).stream_line_id = l_pmschv_end(1).stream_line_id AND
2618 nvl(CR_NewPMSch.schedule_date,CR_NewPMSch.schedule_date_to) <=
2619 nvl(l_pmschv_end(1).schedule_date,l_pmschv_end(1).schedule_date_to)) THEN
2620
2621 pms_upd_ctr := pms_upd_ctr + 1;
2622 l_pmschvtbl_Upd(pms_upd_ctr) := CR_NewPMSch;
2623 l_next_sch_seq := l_next_sch_seq + 1;
2624 l_pmschvtbl_Upd(pms_upd_ctr).sch_sequence := l_next_sch_seq;
2625
2626 ELSE
2627 EXIT;
2628 END IF;
2629
2630 END LOOP;
2631
2632 IF l_pmschv_end(1).schedule_date_to IS NOT NULL THEN
2633 IF l_pmschv_start(1).stream_line_id <> l_pmschv_end(1).stream_line_id THEN
2634 pms_upd_ctr := pms_upd_ctr + 1;
2635 l_pmschvtbl_Upd(pms_upd_ctr) := l_pmschv_end(1);
2636 END IF;
2637 l_pmschvtbl_Upd(pms_upd_ctr).schedule_date_to := trunc(p_new_end_date);
2638 END IF;
2639
2640 IF l_pmschvtbl_Upd.COUNT > 0 THEN
2641 OKS_PMS_PVT.update_row(
2642 p_api_version => l_api_version,
2643 p_init_msg_list => l_init_msg_list,
2644 x_return_status => l_return_status,
2645 x_msg_count => l_msg_count,
2646 x_msg_data => l_msg_data,
2647 p_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
2648 x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
2649
2650 IF (G_DEBUG_ENABLED = 'Y') THEN
2651 okc_debug.log('After oks_pms_pvt update_row', 3);
2652 END IF;
2653
2654 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2655 OKC_API.set_message
2656 (G_APP_NAME,
2657 G_REQUIRED_VALUE,
2658 G_COL_NAME_TOKEN,
2659 'updating PM schedules');
2660
2661 Raise G_EXCEPTION_HALT_VALIDATION;
2662 END IF;
2663 pms_upd_ctr := 0;
2664 END IF;
2665
2666 IF l_pmlrulv_tbl_start(1).id = l_pmlrulv_tbl_end(1).id THEN
2667 l_pmlrulv_tbl_end(1).start_date := p_new_start_date;
2668 l_pmlrulv_tbl_start(1).end_date := p_new_end_date;
2669 END IF;
2670 l_pmlrulv_tbl_start(1).start_date := p_new_start_date;
2671 l_pmlrulv_tbl_end(1).end_date := p_new_end_date;
2672
2673 l_pmlrulv_tbl_start(1).number_of_occurences := l_pmschvtbl_upd.COUNT;
2674
2675 IF l_pmschv_start(1).stream_line_id = l_pmschv_end(1).stream_line_id THEN
2676 l_pmlrulv_tbl_end(1).number_of_occurences := l_pmschvtbl_upd.COUNT;
2677 ELSIF l_pmschv_start(1).stream_line_id <> l_pmschv_end(1).stream_line_id THEN
2678 l_pmlrulv_tbl_end(1).number_of_occurences := l_pmschv_end(1).sch_sequence;
2679 END IF;
2680
2681
2682 ELSIF trunc(p_new_start_date) <= nvl(l_pmschv_start(1).schedule_date,l_pmschv_start(1).schedule_date_from) AND
2683 trunc(p_new_end_date) >= nvl(l_pmschv_end(1).schedule_date,l_pmschv_end(1).schedule_date_to) THEN
2684
2685 IF NOT (l_pmschv_start(1).sch_sequence = 1 AND
2686 l_pmlrulv_tbl_start(1).id = l_first_rule_id AND
2687 l_pmlrulv_tbl_start(1).AUTOSCHEDULE_YN = 'Y') THEN
2688
2689 IF trunc(p_new_start_date) <> trunc(nvl(l_pmschv_start(1).schedule_date,
2690 l_pmschv_start(1).schedule_date_from)) THEN
2691 okc_time_util_pub.get_duration(
2692 p_start_date => trunc(p_new_start_date),
2693 p_end_date => nvl(l_pmschv_start(1).schedule_date,
2694 l_pmschv_start(1).schedule_date_from) - 1,
2695 x_duration => l_duration,
2696 x_timeunit => l_timeunit,
2697 x_return_status => l_return_status);
2698
2699 IF NOT l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2700 RAISE G_EXCEPTION_HALT_VALIDATION;
2701 END IF;
2702
2703 ELSE
2704
2705 l_duration := NULL;
2706 l_timeunit := NULL;
2707
2708 END IF;
2709
2710
2711 l_pmlrulv_tbl_start(1).OFFSET_DURATION := l_duration;
2712 l_pmlrulv_tbl_start(1).OFFSET_UOM := l_timeunit;
2713
2714 IF l_pmlrulv_tbl_start(1).id = l_pmlrulv_tbl_end(1).id THEN
2715
2716 l_pmlrulv_tbl_end(1).OFFSET_DURATION := l_duration;
2717 l_pmlrulv_tbl_end(1).OFFSET_UOM := l_timeunit;
2718
2719 END IF;
2720
2721 ELSIF (l_pmschv_start(1).sch_sequence = 1 AND
2722 l_pmlrulv_tbl_start(1).id = l_first_rule_id AND
2723 l_pmlrulv_tbl_start(1).AUTOSCHEDULE_YN = 'Y') THEN
2724
2725
2726 l_pmschvtbl_Ins.DELETE;
2727 pms_ins_ctr := 0;
2728 pms_upd_ctr := 0;
2729
2730 l_prev_sch_date := trunc(okc_time_util_pub.get_enddate(l_pmschv_start(1).schedule_date - 1,
2731 l_pmlrulv_tbl_start(1).frequency_uom,
2732 -1*(to_number(l_pmlrulv_tbl_start(1).frequency))));
2733
2734 WHILE 1 = 1 LOOP
2735
2736 IF l_prev_sch_date >= trunc(p_new_start_date) THEN
2737
2738 pms_ins_ctr := pms_ins_ctr + 1;
2739 l_pmschvtbl_Ins(pms_ins_ctr) := l_pmschv_start(1);
2740 l_pmschvtbl_Ins(pms_ins_ctr).id := NULL;
2741 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date := l_prev_sch_date;
2742 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date_from := NULL;
2743 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date_to := NULL;
2744 ELSE
2745
2746
2747 IF l_pmschvtbl_Ins.COUNT > 0 THEN
2748
2749 IF trunc(p_new_start_date) <> trunc(l_pmschvtbl_Ins(pms_ins_ctr).schedule_date) THEN
2750
2751 okc_time_util_pub.get_duration(
2752 p_start_date => trunc(p_new_start_date),
2753 p_end_date => l_pmschvtbl_Ins(pms_ins_ctr).schedule_date - 1,
2754 x_duration => l_duration,
2755 x_timeunit => l_timeunit,
2756 x_return_status => l_return_status);
2757
2758 ELSE
2759
2760 l_duration := NULL;
2761 l_timeunit := NULL;
2762
2763 END IF;
2764
2765
2766 ELSE
2767
2768 IF trunc(p_new_start_date) <> trunc(nvl(l_pmschv_start(1).schedule_date,
2769 l_pmschv_start(1).schedule_date_from)) THEN
2770
2771 okc_time_util_pub.get_duration(
2772 p_start_date => trunc(p_new_start_date),
2773 p_end_date => nvl(l_pmschv_start(1).schedule_date,
2774 l_pmschv_start(1).schedule_date_from) - 1,
2775 x_duration => l_duration,
2776 x_timeunit => l_timeunit,
2777 x_return_status => l_return_status);
2778
2779 ELSE
2780
2781 l_duration := NULL;
2782 l_timeunit := NULL;
2783
2784 END IF;
2785
2786 END IF;
2787
2788 IF NOT l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2789 RAISE G_EXCEPTION_HALT_VALIDATION;
2790 END IF;
2791
2792 l_pmlrulv_tbl_start(1).OFFSET_DURATION := l_duration;
2793 l_pmlrulv_tbl_start(1).OFFSET_UOM := l_timeunit;
2794
2795
2796 IF l_pmlrulv_tbl_start(1).id = l_pmlrulv_tbl_end(1).id THEN
2797
2798 l_pmlrulv_tbl_end(1).OFFSET_DURATION := l_duration;
2799 l_pmlrulv_tbl_end(1).OFFSET_UOM := l_timeunit;
2800
2801 END IF;
2802
2803 EXIT;
2804
2805 END IF;
2806
2807 l_prev_sch_date := trunc(okc_time_util_pub.get_enddate(l_prev_sch_date - 1,
2808 l_pmlrulv_tbl_start(1).frequency_uom,
2809 -1*(to_number(l_pmlrulv_tbl_start(1).frequency))));
2810
2811 END LOOP;
2812
2813 END IF;
2814
2815
2816 IF l_pmschvtbl_ins.COUNT > 0 THEN
2817
2818 OKS_PMS_PVT.insert_row(
2819 p_api_version => l_api_version,
2820 p_init_msg_list => l_init_msg_list,
2821 x_return_status => l_return_status,
2822 x_msg_count => l_msg_count,
2823 x_msg_data => l_msg_data,
2824 p_oks_pm_schedules_v_tbl => l_pmschvtbl_ins,
2825 x_oks_pm_schedules_v_tbl => l_pmschvtbl_ins_out);
2826 IF (G_DEBUG_ENABLED = 'Y') THEN
2827 okc_debug.log('After oks_pms_pvt insert_row', 3);
2828 END IF;
2829 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2830 OKC_API.set_message
2831 (G_APP_NAME,
2832 G_REQUIRED_VALUE,
2833 G_COL_NAME_TOKEN,
2834 'inserting PM schedules');
2835
2836 Raise G_EXCEPTION_HALT_VALIDATION;
2837 END IF;
2838
2839 END IF;
2840
2841 l_pmschvtbl_ins.DELETE;
2842
2843
2844 IF (l_pmschv_end(1).sch_sequence = l_pmschv_end_lst_seq AND
2845 l_pmlrulv_tbl_end(1).id = l_last_rule_id AND
2846 l_pmlrulv_tbl_end(1).AUTOSCHEDULE_YN = 'Y') THEN
2847
2848
2849 l_pmschvtbl_Ins.DELETE;
2850 pms_ins_ctr := 0;
2851
2852 l_next_sch_date := trunc(okc_time_util_pub.get_enddate(l_pmschv_end(1).schedule_date + 1,
2853 l_pmlrulv_tbl_end(1).frequency_uom,
2854 to_number(l_pmlrulv_tbl_end(1).frequency)));
2855
2856
2857
2858 WHILE 1 = 1 LOOP
2859
2860
2861
2862 IF l_next_sch_date <= trunc(p_new_end_date) THEN
2863
2864 pms_ins_ctr := pms_ins_ctr + 1;
2865 l_pmschvtbl_Ins(pms_ins_ctr) := l_pmschv_end(1);
2866 l_pmschvtbl_Ins(pms_ins_ctr).id := NULL;
2867 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date := l_next_sch_date;
2868 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date_from := NULL;
2869 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date_to := NULL;
2870 l_pmschvtbl_Ins(pms_ins_ctr).sch_sequence := l_pmschv_end(1).sch_sequence + pms_ins_ctr;
2871
2872 ELSE
2873
2874 l_pmlrulv_tbl_end(1).number_of_occurences := l_pmschv_end(1).sch_sequence + pms_ins_ctr;
2875
2876 EXIT;
2877
2878 END IF;
2879
2880 l_next_sch_date := trunc(okc_time_util_pub.get_enddate(l_next_sch_date + 1,
2881 l_pmlrulv_tbl_end(1).frequency_uom,
2882 to_number(l_pmlrulv_tbl_end(1).frequency)));
2883
2884
2885
2886 END LOOP;
2887
2888 END IF;
2889
2890
2891 IF l_pmschvtbl_Ins.COUNT > 0 THEN
2892
2893 OKS_PMS_PVT.insert_row(
2894 p_api_version => l_api_version,
2895 p_init_msg_list => l_init_msg_list,
2896 x_return_status => l_return_status,
2897 x_msg_count => l_msg_count,
2898 x_msg_data => l_msg_data,
2899 p_oks_pm_schedules_v_tbl => l_pmschvtbl_ins,
2900 x_oks_pm_schedules_v_tbl => l_pmschvtbl_ins_out);
2901
2902 IF (G_DEBUG_ENABLED = 'Y') THEN
2903 okc_debug.log('After oks_pms_pvt insert_row', 3);
2904 END IF;
2905
2906 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2907 OKC_API.set_message
2908 (G_APP_NAME,
2909 G_REQUIRED_VALUE,
2910 G_COL_NAME_TOKEN,
2911 'inserting PM schedules');
2912
2913 Raise G_EXCEPTION_HALT_VALIDATION;
2914 END IF;
2915
2916 END IF;
2917
2918
2919 pms_ins_ctr := 0;
2920 pms_upd_ctr := 0;
2921 l_next_sch_seq := 0;
2922 l_pmschvtbl_Upd.DELETE;
2923
2924 FOR CR_NewPMSch in Cu_NewPMSch(l_pmlrulv_tbl_start(1).id) LOOP
2925
2926 pms_upd_ctr := pms_upd_ctr + 1;
2927 l_pmschvtbl_Upd(pms_upd_ctr) := CR_NewPMSch;
2928 l_next_sch_seq := l_next_sch_seq + 1;
2929 l_pmschvtbl_Upd(pms_upd_ctr).sch_sequence := l_next_sch_seq;
2930
2931 -- ELSE
2932
2933 -- EXIT;
2934
2935 -- END IF;
2936
2937 END LOOP;
2938
2939 IF l_pmschvtbl_Upd.COUNT > 0 THEN
2940 OKS_PMS_PVT.update_row(
2941 p_api_version => l_api_version,
2942 p_init_msg_list => l_init_msg_list,
2943 x_return_status => l_return_status,
2944 x_msg_count => l_msg_count,
2945 x_msg_data => l_msg_data,
2946 p_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
2947 x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
2948
2949 IF (G_DEBUG_ENABLED = 'Y') THEN
2950 okc_debug.log('After oks_pms_pvt update_row', 3);
2951 END IF;
2952
2953 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2954 OKC_API.set_message
2955 (G_APP_NAME,
2956 G_REQUIRED_VALUE,
2957 G_COL_NAME_TOKEN,
2958 'updating PM schedules');
2959
2960 Raise G_EXCEPTION_HALT_VALIDATION;
2961 END IF;
2962 pms_upd_ctr := 0;
2963 END IF;
2964
2965 IF l_pmlrulv_tbl_start(1).id = l_pmlrulv_tbl_end(1).id THEN
2966 l_pmlrulv_tbl_end(1).start_date := p_new_start_date;
2967 l_pmlrulv_tbl_start(1).end_date := p_new_end_date;
2968 END IF;
2969 l_pmlrulv_tbl_start(1).start_date := p_new_start_date;
2970 l_pmlrulv_tbl_end(1).end_date := p_new_end_date;
2971
2972 l_pmlrulv_tbl_start(1).number_of_occurences := l_pmschvtbl_upd.COUNT;
2973
2974 IF l_pmschv_start(1).stream_line_id = l_pmschv_end(1).stream_line_id THEN
2975 l_pmlrulv_tbl_end(1).number_of_occurences := l_pmschvtbl_upd.COUNT;
2976 ELSIF l_pmschv_start(1).stream_line_id <> l_pmschv_end(1).stream_line_id AND
2977 NOT (l_pmschv_end(1).sch_sequence = l_pmschv_end_lst_seq AND
2978 l_pmlrulv_tbl_end(1).id = l_last_rule_id AND
2979 l_pmlrulv_tbl_end(1).AUTOSCHEDULE_YN = 'Y') THEN
2980 l_pmlrulv_tbl_end(1).number_of_occurences := l_pmschv_end(1).sch_sequence;
2981 END IF;
2982
2983 pms_ins_ctr := 0;
2984 pms_upd_ctr := 0;
2985
2986
2987 ELSIF trunc(p_new_start_date) >= nvl(l_pmschv_start(1).schedule_date,l_pmschv_start(1).schedule_date_from) AND
2988 trunc(p_new_end_date) >= nvl(l_pmschv_end(1).schedule_date,l_pmschv_end(1).schedule_date_to) THEN
2989
2990 --dbms_output.put_line('117');
2991
2992
2993 l_pmschvtbl_ins.DELETE;
2994
2995 IF (l_pmschv_end(1).sch_sequence = l_pmschv_end_lst_seq AND
2996 l_pmlrulv_tbl_end(1).id = l_last_rule_id AND
2997 l_pmlrulv_tbl_end(1).AUTOSCHEDULE_YN = 'Y') THEN
2998
2999
3000 l_pmschvtbl_Ins.DELETE;
3001 pms_ins_ctr := 0;
3002
3003 l_next_sch_date := trunc(okc_time_util_pub.get_enddate(l_pmschv_end(1).schedule_date + 1,
3004 l_pmlrulv_tbl_end(1).frequency_uom,
3005 to_number(l_pmlrulv_tbl_end(1).frequency)));
3006
3007 WHILE 1 = 1 LOOP
3008
3009
3010
3011 IF l_next_sch_date <= trunc(p_new_end_date) THEN
3012
3013 pms_ins_ctr := pms_ins_ctr + 1;
3014 l_pmschvtbl_Ins(pms_ins_ctr) := l_pmschv_end(1);
3015 l_pmschvtbl_Ins(pms_ins_ctr).id := NULL;
3016 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date := l_next_sch_date;
3017 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date_from := NULL;
3018 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date_to := NULL;
3019 l_pmschvtbl_Ins(pms_ins_ctr).sch_sequence := l_pmschv_end(1).sch_sequence + pms_ins_ctr;
3020
3021 ELSE
3022
3023 l_pmlrulv_tbl_end(1).number_of_occurences := l_pmschv_end(1).sch_sequence + pms_ins_ctr;
3024
3025 EXIT;
3026
3027 END IF;
3028
3029 l_next_sch_date := trunc(okc_time_util_pub.get_enddate(l_next_sch_date + 1,
3030 l_pmlrulv_tbl_end(1).frequency_uom,
3031 to_number(l_pmlrulv_tbl_end(1).frequency)));
3032
3033 END LOOP;
3034
3035 END IF;
3036
3037
3038 IF l_pmschvtbl_Ins.COUNT > 0 THEN
3039
3040 OKS_PMS_PVT.insert_row(
3041 p_api_version => l_api_version,
3042 p_init_msg_list => l_init_msg_list,
3043 x_return_status => l_return_status,
3044 x_msg_count => l_msg_count,
3045 x_msg_data => l_msg_data,
3046 p_oks_pm_schedules_v_tbl => l_pmschvtbl_ins,
3047 x_oks_pm_schedules_v_tbl => l_pmschvtbl_ins_out);
3048
3049 IF (G_DEBUG_ENABLED = 'Y') THEN
3050 okc_debug.log('After oks_pms_pvt insert_row', 3);
3051 END IF;
3052
3053 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3054 OKC_API.set_message
3055 (G_APP_NAME,
3056 G_REQUIRED_VALUE,
3057 G_COL_NAME_TOKEN,
3058 'inserting PM schedules');
3059
3060 Raise G_EXCEPTION_HALT_VALIDATION;
3061 END IF;
3062
3063 END IF;
3064
3065 l_pmschvtbl_Ins.DELETE;
3066 pms_ins_ctr := 0;
3067
3068 pms_upd_ctr := 0;
3069 l_next_sch_seq := 0;
3070 l_pmschvtbl_Upd.DELETE;
3071
3072 FOR CR_NewPMSch in Cu_NewPMSch(l_pmlrulv_tbl_start(1).id) LOOP
3073 IF (l_pmschv_start(1).stream_line_id <> l_pmschv_end(1).stream_line_id)
3074 OR
3075 (l_pmschv_start(1).stream_line_id = l_pmschv_end(1).stream_line_id AND
3076 nvl(CR_NewPMSch.schedule_date,CR_NewPMSch.schedule_date_to) <=
3077 nvl(l_pmschv_end(1).schedule_date,l_pmschv_end(1).schedule_date_to)) THEN
3078
3079 pms_upd_ctr := pms_upd_ctr + 1;
3080 l_pmschvtbl_Upd(pms_upd_ctr) := CR_NewPMSch;
3081 l_next_sch_seq := l_next_sch_seq + 1;
3082 l_pmschvtbl_Upd(pms_upd_ctr).sch_sequence := l_next_sch_seq;
3083
3084 ELSE
3085 EXIT;
3086 END IF;
3087
3088 END LOOP;
3089
3090 IF l_pmschv_start(1).schedule_date_from IS NOT NULL THEN
3091 l_pmschvtbl_Upd(1).schedule_date_from := trunc(p_new_start_date);
3092 END IF;
3093
3094
3095 IF l_pmschvtbl_Upd.COUNT > 0 THEN
3096 OKS_PMS_PVT.update_row(
3097 p_api_version => l_api_version,
3098 p_init_msg_list => l_init_msg_list,
3099 x_return_status => l_return_status,
3100 x_msg_count => l_msg_count,
3101 x_msg_data => l_msg_data,
3102 p_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
3103 x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
3104
3105 IF (G_DEBUG_ENABLED = 'Y') THEN
3106 okc_debug.log('After oks_pms_pvt update_row', 3);
3107 END IF;
3108 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3109 OKC_API.set_message
3110 (G_APP_NAME,
3111 G_REQUIRED_VALUE,
3112 G_COL_NAME_TOKEN,
3113 'updating PM schedules');
3114
3115 Raise G_EXCEPTION_HALT_VALIDATION;
3116 END IF;
3117 pms_upd_ctr := 0;
3118 END IF;
3119
3120 pms_upd_ctr := 0;
3121 l_next_sch_seq := 0;
3122
3123
3124 IF l_pmlrulv_tbl_start(1).id = l_pmlrulv_tbl_end(1).id THEN
3125 l_pmlrulv_tbl_end(1).start_date := p_new_start_date;
3126 l_pmlrulv_tbl_start(1).end_date := p_new_end_date;
3127 END IF;
3128 l_pmlrulv_tbl_start(1).start_date := p_new_start_date;
3129 l_pmlrulv_tbl_end(1).end_date := p_new_end_date;
3130
3131 l_pmlrulv_tbl_start(1).number_of_occurences := l_pmschvtbl_upd.COUNT;
3132
3133 IF l_pmschv_start(1).stream_line_id = l_pmschv_end(1).stream_line_id THEN
3134 l_pmlrulv_tbl_end(1).number_of_occurences := l_pmschvtbl_upd.COUNT;
3135 ELSIF l_pmschv_start(1).stream_line_id <> l_pmschv_end(1).stream_line_id AND
3136 NOT (l_pmschv_end(1).sch_sequence = l_pmschv_end_lst_seq AND
3137 l_pmlrulv_tbl_end(1).id = l_last_rule_id AND
3138 l_pmlrulv_tbl_end(1).AUTOSCHEDULE_YN = 'Y') THEN
3139 l_pmlrulv_tbl_end(1).number_of_occurences := l_pmschv_end(1).sch_sequence;
3140 END IF;
3141
3142 l_pmschvtbl_Upd.DELETE;
3143
3144
3145 ELSIF trunc(p_new_start_date) >= nvl(l_pmschv_start(1).schedule_date,l_pmschv_start(1).schedule_date_from) AND
3146 trunc(p_new_end_date) <= nvl(l_pmschv_end(1).schedule_date,l_pmschv_end(1).schedule_date_to) THEN
3147
3148 --dbms_output.put_line('118');
3149
3150 pms_upd_ctr := 0;
3151 l_next_sch_seq := 0;
3152 l_pmschvtbl_Upd.DELETE;
3153
3154 FOR CR_NewPMSch in Cu_NewPMSch(l_pmlrulv_tbl_start(1).id) LOOP
3155 IF (l_pmschv_start(1).stream_line_id <> l_pmschv_end(1).stream_line_id)
3156 OR
3157 (l_pmschv_start(1).stream_line_id = l_pmschv_end(1).stream_line_id AND
3158 nvl(CR_NewPMSch.schedule_date,CR_NewPMSch.schedule_date_to) <=
3159 nvl(l_pmschv_end(1).schedule_date,l_pmschv_end(1).schedule_date_to)) THEN
3160
3161 pms_upd_ctr := pms_upd_ctr + 1;
3162 l_pmschvtbl_Upd(pms_upd_ctr) := CR_NewPMSch;
3163 l_next_sch_seq := l_next_sch_seq + 1;
3164 l_pmschvtbl_Upd(pms_upd_ctr).sch_sequence := l_next_sch_seq;
3165
3166 ELSE
3167 EXIT;
3168 END IF;
3169
3170 END LOOP;
3171
3172 IF l_pmschv_end(1).schedule_date_to IS NOT NULL THEN
3173 IF l_pmschv_start(1).stream_line_id <> l_pmschv_end(1).stream_line_id THEN
3174 pms_upd_ctr := pms_upd_ctr + 1;
3175 l_pmschvtbl_Upd(pms_upd_ctr) := l_pmschv_end(1);
3176 END IF;
3177 l_pmschvtbl_Upd(pms_upd_ctr).schedule_date_to := trunc(p_new_end_date);
3178 END IF;
3179
3180
3181 IF l_pmschv_start(1).schedule_date_from IS NOT NULL THEN
3182 l_pmschvtbl_Upd(1).schedule_date_from := trunc(p_new_start_date);
3183 END IF;
3184
3185
3186 IF l_pmschvtbl_Upd.COUNT > 0 THEN
3187 OKS_PMS_PVT.update_row(
3188 p_api_version => l_api_version,
3189 p_init_msg_list => l_init_msg_list,
3190 x_return_status => l_return_status,
3191 x_msg_count => l_msg_count,
3192 x_msg_data => l_msg_data,
3193 p_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
3194 x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
3195 IF (G_DEBUG_ENABLED = 'Y') THEN
3196 okc_debug.log('After oks_pms_pvt update_row', 3);
3197 END IF;
3198 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3199 OKC_API.set_message
3200 (G_APP_NAME,
3201 G_REQUIRED_VALUE,
3202 G_COL_NAME_TOKEN,
3203 'updating PM schedules');
3204
3205 Raise G_EXCEPTION_HALT_VALIDATION;
3206 END IF;
3207 pms_upd_ctr := 0;
3208 END IF;
3209
3210 pms_upd_ctr := 0;
3211 l_next_sch_seq := 0;
3212
3213
3214 IF l_pmlrulv_tbl_start(1).id = l_pmlrulv_tbl_end(1).id THEN
3215 l_pmlrulv_tbl_end(1).start_date := p_new_start_date;
3216 l_pmlrulv_tbl_start(1).end_date := p_new_end_date;
3217 END IF;
3218 l_pmlrulv_tbl_start(1).start_date := p_new_start_date;
3219 l_pmlrulv_tbl_end(1).end_date := p_new_end_date;
3220
3221 IF l_pmschv_end(1).schedule_date_to IS NOT NULL THEN
3222 IF l_pmschv_start(1).stream_line_id <> l_pmschv_end(1).stream_line_id THEN
3223 l_pmlrulv_tbl_start(1).number_of_occurences := l_pmschvtbl_upd.COUNT -1 ;
3224 ELSE
3225 l_pmlrulv_tbl_start(1).number_of_occurences := l_pmschvtbl_upd.COUNT;
3226 END IF;
3227 END IF;
3228
3229
3230 IF l_pmschv_start(1).stream_line_id = l_pmschv_end(1).stream_line_id THEN
3231 l_pmlrulv_tbl_end(1).number_of_occurences := l_pmschvtbl_upd.COUNT;
3232 ELSIF l_pmschv_start(1).stream_line_id <> l_pmschv_end(1).stream_line_id THEN
3233 l_pmlrulv_tbl_end(1).number_of_occurences := l_pmschv_end(1).sch_sequence;
3234 END IF;
3235
3236 l_pmschvtbl_Upd.DELETE;
3237
3238
3239 END IF;
3240
3241 ------dbms_output.put_line('19');
3242
3243 pml_upd_ctr := 0;
3244
3245 pml_upd_ctr := pml_upd_ctr + 1;
3246 l_pmlrulv_tbl_upd(pml_upd_ctr) := l_pmlrulv_tbl_start(1);
3247 l_pmlrulv_tbl_upd(pml_upd_ctr).object_version_number :=l_pmlrulv_tbl_start(1).object_version_number;
3248 --CK 09/18
3249 IF l_pmlrulv_tbl_start(1).id <> l_pmlrulv_tbl_end(1).id THEN
3250 pml_upd_ctr := pml_upd_ctr + 1;
3251 l_pmlrulv_tbl_upd(pml_upd_ctr) := l_pmlrulv_tbl_end(1);
3252 l_pmlrulv_tbl_upd(pml_upd_ctr).object_version_number :=l_pmlrulv_tbl_end(1).object_version_number;
3253 END IF;
3254
3255 oks_pml_pvt.update_row(
3256 p_api_version => l_api_version,
3257 p_init_msg_list => l_init_msg_list,
3258 x_return_status => l_return_status,
3259 x_msg_count => l_msg_count,
3260 x_msg_data => l_msg_data,
3261 p_pmlv_tbl => l_pmlrulv_tbl_upd,
3262 x_pmlv_tbl => l_pmlrulv_tbl_upd_out);
3263 IF (G_DEBUG_ENABLED = 'Y') THEN
3264 okc_debug.log('After oks_pml_pvt update_row', 3);
3265 END IF;
3266 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3267 OKC_API.set_message
3268 (G_APP_NAME,
3269 G_REQUIRED_VALUE,
3270 G_COL_NAME_TOKEN,
3271 'updating pml rules');
3272
3273 Raise G_EXCEPTION_HALT_VALIDATION;
3274 END IF;
3275
3276 pml_upd_ctr := 0;
3277
3278
3279 END IF;
3280
3281
3282 ELSIF trunc(p_new_start_date) IS NOT NULL AND trunc(p_new_end_date) IS NULL THEN
3283
3284
3285 pms_del_ctr := 0;
3286 pml_del_ctr := 0;
3287
3288 FOR i in l_rulv_tbl_in.FIRST..l_rulv_tbl_in.LAST LOOP
3289 IF to_number(l_rulv_tbl_in(i).SEQUENCE_NUMBER) < l_start_rule_seq THEN
3290 pml_del_ctr := pml_del_ctr + 1;
3291 l_pmlrulv_tbl_del(pml_del_ctr) := l_rulv_tbl_in(i);
3292 END IF;
3293 FOR Cr_NewPMSch in Cu_NewPMSch(l_rulv_tbl_in(i).id) LOOP
3294 IF trunc(p_new_start_date) > nvl(Cr_NewPMSch.schedule_date,Cr_NewPMSch.schedule_date_to) THEN
3295 pms_del_ctr := pms_del_ctr + 1;
3296 l_pmschvtbl_Del(pms_del_ctr) := Cr_NewPMSch;
3297 END IF;
3298 END LOOP;
3299 END LOOP;
3300
3301 IF l_pmschvtbl_Del.COUNT > 0 THEN
3302 OKS_PMS_PVT.delete_row(
3303 p_api_version => l_api_version,
3304 p_init_msg_list => l_init_msg_list,
3305 x_return_status => l_return_status,
3306 x_msg_count => l_msg_count,
3307 x_msg_data => l_msg_data,
3308 p_oks_pm_schedules_v_tbl => l_pmschvtbl_Del);
3309 IF (G_DEBUG_ENABLED = 'Y') THEN
3310 okc_debug.log('After oks_pms_pvt delete_row', 3);
3311 END IF;
3312 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3313 OKC_API.set_message
3314 (G_APP_NAME,
3315 G_REQUIRED_VALUE,
3316 G_COL_NAME_TOKEN,
3317 'delete pm schedules');
3318
3319 Raise G_EXCEPTION_HALT_VALIDATION;
3320 END IF;
3321
3322 END IF;
3323
3324 IF l_pmlrulv_tbl_del.COUNT > 0 THEN
3325 oks_pml_pvt.delete_row(
3326 p_api_version => l_api_version,
3327 p_init_msg_list => l_init_msg_list,
3328 x_return_status => l_return_status,
3329 x_msg_count => l_msg_count,
3330 x_msg_data => l_msg_data,
3331 p_pmlv_tbl => l_pmlrulv_tbl_del);
3332
3333 IF (G_DEBUG_ENABLED = 'Y') THEN
3334 okc_debug.log('After oks_pml_pvt delete_row', 3);
3335 END IF;
3336
3337 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3338 OKC_API.set_message
3339 (G_APP_NAME,
3340 G_REQUIRED_VALUE,
3341 G_COL_NAME_TOKEN,
3342 'delete pml rules');
3343
3344 Raise G_EXCEPTION_HALT_VALIDATION;
3345 END IF;
3346
3347 END IF;
3348 pms_del_ctr := 0;
3349 pml_del_ctr := 0;
3350
3351 IF l_start_rule_seq IS NULL THEN -- not possible
3352 null;
3353 ELSE
3354
3355 IF trunc(p_new_start_date) <= nvl(l_pmschv_start(1).schedule_date,l_pmschv_start(1).schedule_date_from) THEN
3356 IF (l_pmlrulv_tbl_start(1).id <> l_first_rule_id) OR
3357 (l_pmschv_start(1).sch_sequence <> 1 AND
3358 l_pmlrulv_tbl_start(1).id = l_first_rule_id ) THEN
3359 l_pmlrulv_tbl_start(1).start_date := p_new_start_date;
3360
3361 END IF;
3362
3363 pms_upd_ctr := 0;
3364
3365 IF l_pmschv_start(1).sch_sequence <> 1 THEN
3366 l_next_sch_seq := 1;
3367
3368 FOR Cr_NewPMSch in Cu_NewPMSch(l_pmschv_start(1).stream_line_id) LOOP
3369
3370 IF Cr_NewPMSch.sch_sequence >= l_pmschv_start(1).sch_sequence THEN
3371
3372 pms_upd_ctr := pms_upd_ctr + 1;
3373 l_pmschvtbl_Upd(pms_upd_ctr) := Cr_NewPMSch;
3374 l_pmschvtbl_Upd(pms_upd_ctr).sch_sequence := l_next_sch_seq;
3375 l_next_sch_seq := l_next_sch_seq + 1;
3376
3377 ELSE
3378 EXIT;
3379 END IF;
3380 END LOOP;
3381
3382 OKS_PMS_PVT.update_row(
3383 p_api_version => l_api_version,
3384 p_init_msg_list => l_init_msg_list,
3385 x_return_status => l_return_status,
3386 x_msg_count => l_msg_count,
3387 x_msg_data => l_msg_data,
3388 p_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
3389 x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
3390
3391 IF (G_DEBUG_ENABLED = 'Y') THEN
3392 okc_debug.log('After oks_pms_pvt update_row', 3);
3393 END IF;
3394
3395 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3396 OKC_API.set_message
3397 (G_APP_NAME,
3398 G_REQUIRED_VALUE,
3399 G_COL_NAME_TOKEN,
3400 'updating PM schedules');
3401
3402 Raise G_EXCEPTION_HALT_VALIDATION;
3403 END IF;
3404
3405 pms_upd_ctr := 0;
3406 END IF;
3407
3408 IF NOT (l_pmschv_start(1).sch_sequence = 1 AND
3409 l_pmlrulv_tbl_start(1).id = l_first_rule_id AND
3410 l_pmlrulv_tbl_start(1).AUTOSCHEDULE_YN = 'Y') THEN
3411
3412
3413 IF trunc(p_new_start_date) <> trunc(nvl(l_pmschv_start(1).schedule_date,
3414 l_pmschv_start(1).schedule_date_from)) THEN
3415 okc_time_util_pub.get_duration(
3416 p_start_date => trunc(p_new_start_date),
3417 p_end_date => nvl(l_pmschv_start(1).schedule_date,
3418 l_pmschv_start(1).schedule_date_from) - 1,
3419 x_duration => l_duration,
3420 x_timeunit => l_timeunit,
3421 x_return_status => l_return_status);
3422 IF NOT l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3423 RAISE G_EXCEPTION_HALT_VALIDATION;
3424 END IF;
3425
3426 ELSE
3427
3428 l_duration := NULL;
3429 l_timeunit := NULL;
3430
3431 END IF;
3432
3433
3434 IF NOT l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3435 RAISE G_EXCEPTION_HALT_VALIDATION;
3436 END IF;
3437
3438 l_pmlrulv_tbl_start(1).OFFSET_DURATION := l_duration;
3439 l_pmlrulv_tbl_start(1).OFFSET_UOM := l_timeunit;
3440 l_pmlrulv_tbl_start(1).start_date := p_new_start_date;
3441
3442 ELSIF (l_pmschv_start(1).sch_sequence = 1 AND
3443 l_pmlrulv_tbl_start(1).id = l_first_rule_id AND
3444 l_pmlrulv_tbl_start(1).AUTOSCHEDULE_YN = 'Y') THEN
3445
3446 l_pmlrulv_tbl_start(1).start_date := p_new_start_date;
3447
3448 l_pmschvtbl_Ins.DELETE;
3449 l_prev_sch_date := NULL;
3450
3451 pms_ins_ctr := 0;
3452 pms_upd_ctr := 0;
3453
3454 l_prev_sch_date := trunc(okc_time_util_pub.get_enddate(l_pmschv_start(1).schedule_date - 1,
3455 l_pmlrulv_tbl_start(1).frequency_uom,
3456 -1*(to_number(l_pmlrulv_tbl_start(1).frequency))));
3457
3458 WHILE 1 = 1 LOOP
3459
3460
3461 IF l_prev_sch_date >= trunc(p_new_start_date) THEN
3462
3463 pms_ins_ctr := pms_ins_ctr + 1;
3464 l_pmschvtbl_Ins(pms_ins_ctr) := l_pmschv_start(1);
3465 l_pmschvtbl_Ins(pms_ins_ctr).id := NULL;
3466 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date := l_prev_sch_date;
3467 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date_from := NULL;
3468 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date_to := NULL;
3469 l_pmschvtbl_Ins(pms_ins_ctr).sch_sequence := NULL;
3470 ELSE
3471
3472
3473
3474 IF l_pmschvtbl_Ins.COUNT > 0 THEN
3475
3476
3477
3478 FOR i in l_pmschvtbl_Ins.FIRST..l_pmschvtbl_Ins.LAST LOOP
3479 l_pmschvtbl_Ins(i).sch_sequence := pms_ins_ctr - i + 1;
3480 END LOOP;
3481
3482 l_next_sch_seq := l_pmschvtbl_Ins.COUNT + 1;
3483
3484 FOR Cr_NewPMSch in Cu_NewPMSch(l_pmlrulv_tbl_start(1).id) LOOP
3485 pms_upd_ctr := pms_upd_ctr + 1;
3486 l_pmschvtbl_Upd(pms_upd_ctr) := Cr_NewPMSch;
3487 l_pmschvtbl_Upd(pms_upd_ctr).sch_sequence := l_next_sch_seq;
3488 l_next_sch_seq := l_next_sch_seq + 1;
3489 END LOOP;
3490
3491 l_pmlrulv_tbl_start(1).number_of_occurences := l_next_sch_seq - 1;
3492
3493
3494 IF trunc(p_new_start_date) <> trunc(l_pmschvtbl_Ins(pms_ins_ctr).schedule_date) THEN
3495
3496 okc_time_util_pub.get_duration(
3497 p_start_date => trunc(p_new_start_date),
3498 p_end_date => l_pmschvtbl_Ins(pms_ins_ctr).schedule_date - 1,
3499 x_duration => l_duration,
3500 x_timeunit => l_timeunit,
3501 x_return_status => l_return_status);
3502
3503 ELSE
3504
3505 l_duration := NULL;
3506 l_timeunit := NULL;
3507
3508 END IF;
3509
3510 ELSE
3511
3512 IF trunc(p_new_start_date) <> trunc(nvl(l_pmschv_start(1).schedule_date,
3513 l_pmschv_start(1).schedule_date_from)) THEN
3514
3515 okc_time_util_pub.get_duration(
3516 p_start_date => trunc(p_new_start_date),
3517 p_end_date => nvl(l_pmschv_start(1).schedule_date,
3518 l_pmschv_start(1).schedule_date_from) - 1,
3519 x_duration => l_duration,
3520 x_timeunit => l_timeunit,
3521 x_return_status => l_return_status);
3522
3523 ELSE
3524
3525 l_duration := NULL;
3526 l_timeunit := NULL;
3527
3528 END IF;
3529 END IF;
3530
3531
3532 IF NOT l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3533 RAISE G_EXCEPTION_HALT_VALIDATION;
3534 END IF;
3535
3536 l_pmlrulv_tbl_start(1).OFFSET_DURATION := l_duration;
3537 l_pmlrulv_tbl_start(1).OFFSET_UOM := l_timeunit;
3538
3539 EXIT;
3540
3541 END IF;
3542
3543 l_prev_sch_date := trunc(okc_time_util_pub.get_enddate(l_prev_sch_date - 1,
3544 l_pmlrulv_tbl_start(1).frequency_uom,
3545 -1*(to_number(l_pmlrulv_tbl_start(1).frequency))));
3546
3547 END LOOP;
3548
3549 OKS_PMS_PVT.insert_row(
3550 p_api_version => l_api_version,
3551 p_init_msg_list => l_init_msg_list,
3552 x_return_status => l_return_status,
3553 x_msg_count => l_msg_count,
3554 x_msg_data => l_msg_data,
3555 p_oks_pm_schedules_v_tbl => l_pmschvtbl_ins,
3556 x_oks_pm_schedules_v_tbl => l_pmschvtbl_ins_out);
3557 IF (G_DEBUG_ENABLED = 'Y') THEN
3558 okc_debug.log('After oks_pms_pvt insert_row', 3);
3559 END IF;
3560 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3561 OKC_API.set_message
3562 (G_APP_NAME,
3563 G_REQUIRED_VALUE,
3564 G_COL_NAME_TOKEN,
3565 'insert pm schedules');
3566
3567 Raise G_EXCEPTION_HALT_VALIDATION;
3568 END IF;
3569
3570 OKS_PMS_PVT.update_row(
3571 p_api_version => l_api_version,
3572 p_init_msg_list => l_init_msg_list,
3573 x_return_status => l_return_status,
3574 x_msg_count => l_msg_count,
3575 x_msg_data => l_msg_data,
3576 p_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
3577 x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
3578
3579 IF (G_DEBUG_ENABLED = 'Y') THEN
3580 okc_debug.log('After oks_pms_pvt update_row', 3);
3581 END IF;
3582 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3583 OKC_API.set_message
3584 (G_APP_NAME,
3585 G_REQUIRED_VALUE,
3586 G_COL_NAME_TOKEN,
3587 'update pm schedules');
3588
3589 Raise G_EXCEPTION_HALT_VALIDATION;
3590 END IF;
3591
3592 pms_ins_ctr := 0;
3593 pms_upd_ctr := 0;
3594
3595
3596 l_prev_sch_date := NULL;
3597
3598 END IF;
3599
3600
3601 l_pmlrulv_tbl_start(1).number_of_occurences :=
3602 l_pmlrulv_tbl_start(1).number_of_occurences - l_pmschv_start(1).sch_sequence + 1;
3603
3604
3605 pml_upd_ctr := 0;
3606
3607 pml_upd_ctr := pml_upd_ctr + 1;
3608 l_pmlrulv_tbl_upd(pml_upd_ctr) := l_pmlrulv_tbl_start(1);
3609
3610 oks_pml_pvt.update_row(
3611 p_api_version => l_api_version,
3612 p_init_msg_list => l_init_msg_list,
3613 x_return_status => l_return_status,
3614 x_msg_count => l_msg_count,
3615 x_msg_data => l_msg_data,
3616 p_pmlv_tbl => l_pmlrulv_tbl_upd,
3617 x_pmlv_tbl => l_pmlrulv_tbl_upd_out);
3618 IF (G_DEBUG_ENABLED = 'Y') THEN
3619 okc_debug.log('After oks_pml_pvt update_row', 3);
3620 END IF;
3621 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3622 OKC_API.set_message
3623 (G_APP_NAME,
3624 G_REQUIRED_VALUE,
3625 G_COL_NAME_TOKEN,
3626 'updating pml rules');
3627
3628 Raise G_EXCEPTION_HALT_VALIDATION;
3629 END IF;
3630
3631 pml_upd_ctr := 0;
3632
3633 ELSIF trunc(p_new_start_date) >= nvl(l_pmschv_start(1).schedule_date,l_pmschv_start(1).schedule_date_from) THEN
3634
3635 l_pmlrulv_tbl_start(1).start_date := p_new_start_date;
3636
3637 pms_upd_ctr := 0;
3638
3639 IF l_pmschv_start(1).sch_sequence <> 1 THEN
3640
3641 l_next_sch_seq := 1;
3642
3643 FOR CR_NewPMSch in Cu_NewPMSch(l_pmschv_start(1).stream_line_id) LOOP
3644
3645 IF CR_NewPMSch.sch_sequence >= l_pmschv_start(1).sch_sequence THEN
3646
3647 pms_upd_ctr := pms_upd_ctr + 1;
3648 l_pmschvtbl_Upd(pms_upd_ctr) := CR_NewPMSch;
3649 l_pmschvtbl_Upd(pms_upd_ctr).sch_sequence := l_next_sch_seq;
3650 l_next_sch_seq := l_next_sch_seq + 1;
3651
3652 ELSE
3653 EXIT;
3654 END IF;
3655 END LOOP;
3656
3657 OKS_PMS_PVT.update_row(
3658 p_api_version => l_api_version,
3659 p_init_msg_list => l_init_msg_list,
3660 x_return_status => l_return_status,
3661 x_msg_count => l_msg_count,
3662 x_msg_data => l_msg_data,
3663 p_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
3664 x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
3665 IF (G_DEBUG_ENABLED = 'Y') THEN
3666 okc_debug.log('After oks_pms_pvt update_row', 3);
3667 END IF;
3668
3669 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3670 OKC_API.set_message
3671 (G_APP_NAME,
3672 G_REQUIRED_VALUE,
3673 G_COL_NAME_TOKEN,
3674 'updating PM schedules');
3675
3676 Raise G_EXCEPTION_HALT_VALIDATION;
3677 END IF;
3678
3679 pms_upd_ctr := 0;
3680
3681 END IF;
3682
3683 l_pmlrulv_tbl_start(1).OFFSET_DURATION := NULL;
3684 l_pmlrulv_tbl_start(1).OFFSET_UOM := NULL;
3685
3686 l_pmlrulv_tbl_start(1).number_of_occurences :=
3687 -- to_char(to_number(l_pmlrulv_tbl_start(1).number_of_occurences) - l_pmschv_start(1).sch_sequence + 1);
3688 l_pmlrulv_tbl_start(1).number_of_occurences - l_pmschv_start(1).sch_sequence + 1;
3689
3690
3691 pml_upd_ctr := 0;
3692
3693 pml_upd_ctr := pml_upd_ctr + 1;
3694 l_pmlrulv_tbl_upd(pml_upd_ctr) := l_pmlrulv_tbl_start(1);
3695
3696 oks_pml_pvt.update_row(
3697 p_api_version => l_api_version,
3698 p_init_msg_list => l_init_msg_list,
3699 x_return_status => l_return_status,
3700 x_msg_count => l_msg_count,
3701 x_msg_data => l_msg_data,
3702 p_pmlv_tbl => l_pmlrulv_tbl_upd,
3703 x_pmlv_tbl => l_pmlrulv_tbl_upd_out);
3704
3705 IF (G_DEBUG_ENABLED = 'Y') THEN
3706 okc_debug.log('After oks_pml_pvt update_row', 3);
3707 END IF;
3708
3709 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3710 OKC_API.set_message
3711 (G_APP_NAME,
3712 G_REQUIRED_VALUE,
3713 G_COL_NAME_TOKEN,
3714 'updating pml rules');
3715
3716 Raise G_EXCEPTION_HALT_VALIDATION;
3717 END IF;
3718
3719 pml_upd_ctr := 0;
3720
3721 END IF;
3722
3723 END IF;
3724
3725 ELSIF trunc(p_new_start_date) IS NULL AND trunc(p_new_end_date) IS NOT NULL THEN
3726
3727 pms_del_ctr := 0;
3728 pml_del_ctr := 0;
3729
3730 FOR i in l_rulv_tbl_in.FIRST..l_rulv_tbl_in.LAST LOOP
3731 IF to_number(l_rulv_tbl_in(i).sequence_number) > l_end_rule_seq THEN
3732 pml_del_ctr := pml_del_ctr + 1;
3733 l_pmlrulv_tbl_del(pml_del_ctr) := l_rulv_tbl_in(i);
3734 END IF;
3735 FOR CR_NewPMSch in Cu_NewPMSch(l_rulv_tbl_in(i).id) LOOP
3736 IF trunc(p_new_end_date) < nvl(CR_NewPMSch.schedule_date,CR_NewPMSch.schedule_date_from) THEN
3737 pms_del_ctr := pms_del_ctr + 1;
3738 l_pmschvtbl_Del(pms_del_ctr) := CR_NewPMSch;
3739 END IF;
3740 END LOOP;
3741 END LOOP;
3742
3743 OKS_PMS_PVT.delete_row(
3744 p_api_version => l_api_version,
3745 p_init_msg_list => l_init_msg_list,
3746 x_return_status => l_return_status,
3747 x_msg_count => l_msg_count,
3748 x_msg_data => l_msg_data,
3749 p_oks_pm_schedules_v_tbl => l_pmschvtbl_Del);
3750
3751 IF (G_DEBUG_ENABLED = 'Y') THEN
3752 okc_debug.log('After oks_pms_pvt delete_row', 3);
3753 END IF;
3754
3755 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3756 OKC_API.set_message
3757 (G_APP_NAME,
3758 G_REQUIRED_VALUE,
3759 G_COL_NAME_TOKEN,
3760 'delete pm schedules');
3761
3762 Raise G_EXCEPTION_HALT_VALIDATION;
3763 END IF;
3764
3765 oks_pml_pvt.delete_row(
3766 p_api_version => l_api_version,
3767 p_init_msg_list => l_init_msg_list,
3768 x_return_status => l_return_status,
3769 x_msg_count => l_msg_count,
3770 x_msg_data => l_msg_data,
3771 p_pmlv_tbl => l_pmlrulv_tbl_del);
3772
3773
3774 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3775 OKC_API.set_message
3776 (G_APP_NAME,
3777 G_REQUIRED_VALUE,
3778 G_COL_NAME_TOKEN,
3779 'delete pml rules');
3780
3781 Raise G_EXCEPTION_HALT_VALIDATION;
3782 END IF;
3783
3784 pms_del_ctr := 0;
3785 pml_del_ctr := 0;
3786
3787
3788 IF l_end_rule_seq IS NULL THEN -- not possible
3789 null;
3790 ELSE
3791
3792
3793 IF trunc(p_new_end_date) <= nvl(l_pmschv_end(1).schedule_date,l_pmschv_end(1).schedule_date_to) THEN
3794
3795 l_pmlrulv_tbl_upd(1) := l_pmlrulv_tbl_end(1);
3796 l_pmlrulv_tbl_upd(1).number_of_occurences := l_pmschv_end(1).sch_sequence;
3797 l_pmlrulv_tbl_upd(1).end_date := p_new_end_date;
3798
3799 IF l_pmschv_end(1).schedule_date_to IS NOT NULL THEN
3800 l_pmschvtbl_upd(1) := l_pmschv_end(1);
3801 l_pmschvtbl_upd(1).schedule_date_to := trunc(p_new_end_date);
3802 END IF;
3803
3804
3805
3806 OKS_PMS_PVT.update_row(
3807 p_api_version => l_api_version,
3808 p_init_msg_list => l_init_msg_list,
3809 x_return_status => l_return_status,
3810 x_msg_count => l_msg_count,
3811 x_msg_data => l_msg_data,
3812 p_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
3813 x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
3814 IF (G_DEBUG_ENABLED = 'Y') THEN
3815 okc_debug.log('After oks_pms_pvt update_row', 3);
3816 END IF;
3817 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3818 OKC_API.set_message
3819 (G_APP_NAME,
3820 G_REQUIRED_VALUE,
3821 G_COL_NAME_TOKEN,
3822 'update pm schedules');
3823
3824 Raise G_EXCEPTION_HALT_VALIDATION;
3825 END IF;
3826
3827
3828 oks_pml_pvt.update_row(
3829 p_api_version => l_api_version,
3830 p_init_msg_list => l_init_msg_list,
3831 x_return_status => l_return_status,
3832 x_msg_count => l_msg_count,
3833 x_msg_data => l_msg_data,
3834 p_pmlv_tbl => l_pmlrulv_tbl_upd,
3835 x_pmlv_tbl => l_pmlrulv_tbl_upd_out);
3836
3837
3838
3839
3840 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3841 OKC_API.set_message
3842 (G_APP_NAME,
3843 G_REQUIRED_VALUE,
3844 G_COL_NAME_TOKEN,
3845 'updating pml rules');
3846
3847 Raise G_EXCEPTION_HALT_VALIDATION;
3848 END IF;
3849
3850 pml_upd_ctr := 0;
3851
3852 ELSIF trunc(p_new_end_date) >= nvl(l_pmschv_end(1).schedule_date,l_pmschv_end(1).schedule_date_to) THEN
3853
3854
3855 l_pmlrulv_tbl_upd(1) := l_pmlrulv_tbl_end(1);
3856 l_pmlrulv_tbl_upd(1).number_of_occurences := l_pmschv_end(1).sch_sequence;
3857 l_pmlrulv_tbl_upd(1).end_date := p_new_end_date;
3858
3859 IF (l_pmschv_end(1).sch_sequence = l_pmschv_end_lst_seq AND
3860 l_pmlrulv_tbl_end(1).id = l_last_rule_id AND
3861 l_pmlrulv_tbl_end(1).AUTOSCHEDULE_YN = 'Y') THEN
3862
3863 l_pmschvtbl_Ins.DELETE;
3864 pms_ins_ctr := 0;
3865
3866 l_next_sch_date := trunc(okc_time_util_pub.get_enddate(l_pmschv_end(1).schedule_date + 1,
3867 l_pmlrulv_tbl_end(1).frequency_uom,
3868 to_number(l_pmlrulv_tbl_end(1).frequency)));
3869
3870 WHILE 1 = 1 LOOP
3871
3872
3873 IF l_next_sch_date <= trunc(p_new_end_date) THEN
3874
3875 pms_ins_ctr := pms_ins_ctr + 1;
3876 l_pmschvtbl_Ins(pms_ins_ctr) := l_pmschv_end(1);
3877 l_pmschvtbl_Ins(pms_ins_ctr).id := NULL;
3878 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date := l_next_sch_date;
3879 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date_from := NULL;
3880 l_pmschvtbl_Ins(pms_ins_ctr).schedule_date_to := NULL;
3881 l_pmschvtbl_Ins(pms_ins_ctr).sch_sequence := l_pmschv_end(1).sch_sequence + pms_ins_ctr;
3882
3883 ELSE
3884
3885 l_pmlrulv_tbl_upd(1).number_of_occurences := l_pmschv_end(1).sch_sequence + pms_ins_ctr;
3886
3887 EXIT;
3888
3889 END IF;
3890
3891 l_next_sch_date := trunc(okc_time_util_pub.get_enddate(l_next_sch_date + 1,
3892 l_pmlrulv_tbl_end(1).frequency_uom,
3893 to_number(l_pmlrulv_tbl_end(1).frequency)));
3894
3895 END LOOP;
3896
3897 l_next_sch_date := NULL;
3898
3899
3900 OKS_PMS_PVT.insert_row(
3901 p_api_version => l_api_version,
3902 p_init_msg_list => l_init_msg_list,
3903 x_return_status => l_return_status,
3904 x_msg_count => l_msg_count,
3905 x_msg_data => l_msg_data,
3906 p_oks_pm_schedules_v_tbl => l_pmschvtbl_ins,
3907 x_oks_pm_schedules_v_tbl => l_pmschvtbl_ins_out);
3908 IF (G_DEBUG_ENABLED = 'Y') THEN
3909 okc_debug.log('After oks_pms_pvt insert_row', 3);
3910 END IF;
3911
3912 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3913 OKC_API.set_message
3914 (G_APP_NAME,
3915 G_REQUIRED_VALUE,
3916 G_COL_NAME_TOKEN,
3917 'update pm schedules');
3918
3919 Raise G_EXCEPTION_HALT_VALIDATION;
3920 END IF;
3921
3922
3923 END IF;
3924
3925 oks_pml_pvt.update_row(
3926 p_api_version => l_api_version,
3927 p_init_msg_list => l_init_msg_list,
3928 x_return_status => l_return_status,
3929 x_msg_count => l_msg_count,
3930 x_msg_data => l_msg_data,
3931 p_pmlv_tbl => l_pmlrulv_tbl_upd,
3932 x_pmlv_tbl => l_pmlrulv_tbl_upd_out);
3933
3934 IF (G_DEBUG_ENABLED = 'Y') THEN
3935 okc_debug.log('After oks_pml_pvt update_row', 3);
3936 END IF;
3937
3938 IF Not l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3939 OKC_API.set_message
3940 (G_APP_NAME,
3941 G_REQUIRED_VALUE,
3942 G_COL_NAME_TOKEN,
3943 'updating pml rules');
3944
3945 Raise G_EXCEPTION_HALT_VALIDATION;
3946 END IF;
3947
3948
3949
3950 END IF;
3951
3952 END IF;
3953
3954 END IF;
3955
3956 END IF;
3957
3958 end loop;
3959
3960 end loop;
3961 IF (G_DEBUG_ENABLED = 'Y') THEN
3962 okc_debug.log('Exiting Adjust_PM_Program_Schedule', 3);
3963 okc_debug.Reset_Indentation;
3964 END IF;
3965
3966 x_return_status := l_return_status;
3967 x_msg_count := l_msg_count;
3968 x_msg_data := l_msg_data;
3969
3970 EXCEPTION
3971 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3972 x_return_status := l_return_status ;
3973 IF (G_DEBUG_ENABLED = 'Y') THEN
3974 okc_debug.log('Exiting Adjust_PM_Program_Schedule'||l_return_Status, 3);
3975 okc_debug.Reset_Indentation;
3976 END IF;
3977 WHEN OTHERS THEN
3978 OKC_API.SET_MESSAGE(
3979 p_app_name => g_app_name,
3980 p_msg_name => g_unexpected_error,
3981 p_token1 => g_sqlcode_token,
3982 p_token1_value => sqlcode,
3983 p_token2 => g_sqlerrm_token,
3984 p_token2_value => sqlerrm);
3985 ------------dbms_output.put_line('Value of l_return_status6='||l_return_status||substr(sqlerrm,1,200));
3986 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3987 x_msg_count :=l_msg_count;
3988 IF (G_DEBUG_ENABLED = 'Y') THEN
3989 okc_debug.log('Exiting Adjust_PM_Program_Schedule'||sqlerrm, 3);
3990 okc_debug.Reset_Indentation;
3991 END IF;
3992
3993 END ADJUST_PM_PROGRAM_SCHEDULE;
3994
3995 PROCEDURE migrate_to_program
3996 ( p_start_rowid IN ROWID,
3997 p_end_rowid IN ROWID,
3998 p_api_version IN NUMBER,
3999 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4000 x_msg_count OUT NOCOPY NUMBER,
4001 x_return_status OUT NOCOPY VARCHAR2,
4002 x_msg_data OUT NOCOPY VARCHAR2)
4003 IS
4004 BEGIN
4005 --stubbed out as it is no longer being used
4006 null;
4007 END migrate_to_program;
4008
4009 PROCEDURE migrate_to_activities
4010 ( p_start_rowid IN ROWID,
4011 p_end_rowid IN ROWID,
4012 p_api_version IN NUMBER,
4013 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4014 x_msg_count OUT NOCOPY NUMBER,
4015 x_return_status OUT NOCOPY VARCHAR2,
4016 x_msg_data OUT NOCOPY VARCHAR2)
4017
4018 IS
4019 BEGIN
4020 --stubbed out as it is no longer being used
4021 null;
4022 END migrate_to_activities;
4023
4024 -- procedure called from CHECK_PM_MATCH
4025 Procedure CHECK_PM_STREAM_MATCH
4026 ( p_api_version IN NUMBER,
4027 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4028 x_return_status OUT NOCOPY VARCHAR2,
4029 x_msg_count OUT NOCOPY NUMBER,
4030 x_msg_data OUT NOCOPY VARCHAR2,
4031 P_Source_coverage_Line_Id IN NUMBER,
4032 P_Target_coverage_Line_Id IN NUMBER,
4033 P_Source_activity_Line_Id IN NUMBER,
4034 P_Target_activity_Line_Id IN NUMBER,
4035 x_pm_stream_match OUT NOCOPY VARCHAR2)
4036 IS
4037
4038
4039 CURSOR Cu_get_PML(cp_cov_line_id IN NUMBER) IS
4040 SELECT SEQUENCE_NUMBER ,
4041 NUMBER_OF_OCCURENCES ,
4042 FREQUENCY ,
4043 FREQUENCY_UOM ,
4044 nvl(OFFSET_DURATION,0) OFFSET_DURATION ,
4045 nvl(OFFSET_UOM,0) OFFSET_UOM ,
4046 AUTOSCHEDULE_YN,
4047 ACTIVITY_LINE_ID,
4048 CLE_ID
4049 DNZ_CHR_ID
4050 FROM OKS_PM_STREAM_LEVELS_V PML
4051 WHERE CLE_ID=cp_cov_line_id
4052 and activity_line_id is null
4053 ORDER BY SEQUENCE_NUMBER;
4054
4055 CURSOR Cu_get_ActPML(cp_activity_line_id IN NUMBER) IS
4056 SELECT SEQUENCE_NUMBER ,
4057 NUMBER_OF_OCCURENCES ,
4058 FREQUENCY ,
4059 FREQUENCY_UOM ,
4060 nvl(OFFSET_DURATION,0) OFFSET_DURATION ,
4061 nvl(OFFSET_UOM,0) OFFSET_UOM ,
4062 AUTOSCHEDULE_YN,
4063 ACTIVITY_LINE_ID,
4064 CLE_ID
4065 DNZ_CHR_ID
4066 FROM OKS_PM_STREAM_LEVELS_V PML
4067 WHERE activity_line_id =cp_activity_line_id
4068 ORDER BY SEQUENCE_NUMBER;
4069
4070
4071 src_pml_index NUMBER :=0;
4072 tgt_pml_index NUMBER :=0;
4073 src_pml_index1 NUMBER :=0;
4074 tgt_pml_index1 NUMBER := 0;
4075 l_source_pml_tbl oks_pml_pvt.pmlv_tbl_type;
4076 l_target_pml_tbl oks_pml_pvt.pmlv_tbl_type;
4077 l_pml_match VARCHAR2(1);
4078 G_MISMATCH EXCEPTION ;
4079
4080 BEGIN
4081 --Compare Program Stream Levels
4082 --
4083 IF P_SOURCE_ACTIVITY_LINE_ID IS NULL THEN
4084 FOR CR_get_PML IN CU_get_PML(P_Source_coverage_Line_Id) LOOP
4085 src_pml_index :=src_pml_index +1;
4086 l_source_pml_tbl(src_pml_index).SEQUENCE_NUMBER := CR_get_PML.SEQUENCE_NUMBER;
4087 l_source_pml_tbl(src_pml_index).NUMBER_OF_OCCURENCES:= CR_get_PML.NUMBER_OF_OCCURENCES;
4088 l_source_pml_tbl(src_pml_index).FREQUENCY:= CR_get_PML.FREQUENCY;
4089 l_source_pml_tbl(src_pml_index).FREQUENCY_UOM:= CR_get_PML.FREQUENCY_UOM;
4090 l_source_pml_tbl(src_pml_index).OFFSET_DURATION:= CR_get_PML.OFFSET_DURATION;
4091 l_source_pml_tbl(src_pml_index).OFFSET_UOM:= CR_get_PML.OFFSET_UOM;
4092 l_source_pml_tbl(src_pml_index).AUTOSCHEDULE_YN:= CR_get_PML.AUTOSCHEDULE_YN;
4093 END LOOP;
4094
4095 FOR CR_get_PML IN CU_get_PML(P_Target_coverage_Line_Id) LOOP
4096 tgt_pml_index :=tgt_pml_index +1;
4097 l_target_pml_tbl(tgt_pml_index).SEQUENCE_NUMBER := CR_get_PML.SEQUENCE_NUMBER;
4098 l_target_pml_tbl(tgt_pml_index).NUMBER_OF_OCCURENCES:= CR_get_PML.NUMBER_OF_OCCURENCES;
4099 l_target_pml_tbl(tgt_pml_index).FREQUENCY:= CR_get_PML.FREQUENCY;
4100 l_target_pml_tbl(tgt_pml_index).FREQUENCY_UOM:= CR_get_PML.FREQUENCY_UOM;
4101 l_target_pml_tbl(tgt_pml_index).OFFSET_DURATION:= CR_get_PML.OFFSET_DURATION;
4102 l_target_pml_tbl(tgt_pml_index).OFFSET_UOM:= CR_get_PML.OFFSET_UOM;
4103 l_target_pml_tbl(tgt_pml_index).AUTOSCHEDULE_YN:= CR_get_PML.AUTOSCHEDULE_YN;
4104 END LOOP;
4105 ELSE
4106 FOR CR_get_ActPML IN CU_get_ActPML(P_Source_activity_Line_Id) LOOP
4107 src_pml_index :=src_pml_index +1;
4108 l_source_pml_tbl(src_pml_index).SEQUENCE_NUMBER := CR_get_ActPML.SEQUENCE_NUMBER;
4109 l_source_pml_tbl(src_pml_index).NUMBER_OF_OCCURENCES:= CR_get_ActPML.NUMBER_OF_OCCURENCES;
4110 l_source_pml_tbl(src_pml_index).FREQUENCY:= CR_get_ActPML.FREQUENCY;
4111 l_source_pml_tbl(src_pml_index).FREQUENCY_UOM:= CR_get_ActPML.FREQUENCY_UOM;
4112 l_source_pml_tbl(src_pml_index).OFFSET_DURATION:= CR_get_ActPML.OFFSET_DURATION;
4113 l_source_pml_tbl(src_pml_index).OFFSET_UOM:= CR_get_ActPML.OFFSET_UOM;
4114 l_source_pml_tbl(src_pml_index).AUTOSCHEDULE_YN:= CR_get_ActPML.AUTOSCHEDULE_YN;
4115 END LOOP;
4116
4117 FOR CR_get_ActPML IN CU_get_ActPML(P_Target_activity_Line_Id) LOOP
4118 tgt_pml_index :=tgt_pml_index +1;
4119 l_target_pml_tbl(tgt_pml_index).SEQUENCE_NUMBER := CR_get_ActPML.SEQUENCE_NUMBER;
4120 l_target_pml_tbl(tgt_pml_index).NUMBER_OF_OCCURENCES:= CR_get_ActPML.NUMBER_OF_OCCURENCES;
4121 l_target_pml_tbl(tgt_pml_index).FREQUENCY:= CR_get_ActPML.FREQUENCY;
4122 l_target_pml_tbl(tgt_pml_index).FREQUENCY_UOM:= CR_get_ActPML.FREQUENCY_UOM;
4123 l_target_pml_tbl(tgt_pml_index).OFFSET_DURATION:= CR_get_ActPML.OFFSET_DURATION;
4124 l_target_pml_tbl(tgt_pml_index).OFFSET_UOM:= CR_get_ActPML.OFFSET_UOM;
4125 l_target_pml_tbl(tgt_pml_index).AUTOSCHEDULE_YN:= CR_get_ActPML.AUTOSCHEDULE_YN;
4126 END LOOP;
4127 END IF;
4128 IF l_source_pml_tbl.count <> l_target_pml_tbl.count Then
4129 RAISE G_MISMATCH ;
4130 END IF;
4131 IF l_source_pml_tbl.count >0 then
4132 FOR src_pml_index1 IN l_source_pml_tbl.FIRST..l_source_pml_tbl.LAST
4133 LOOP
4134 IF l_source_pml_tbl(src_pml_index1).NUMBER_OF_OCCURENCES= l_target_pml_tbl(src_pml_index1).NUMBER_OF_OCCURENCES
4135 AND l_source_pml_tbl(src_pml_index1).FREQUENCY= l_target_pml_tbl(src_pml_index1).FREQUENCY
4136 AND l_source_pml_tbl(src_pml_index1).FREQUENCY_UOM=l_target_pml_tbl(src_pml_index1).FREQUENCY_UOM
4137 AND l_source_pml_tbl(src_pml_index1).OFFSET_DURATION= l_target_pml_tbl(src_pml_index1).OFFSET_DURATION
4138 AND l_source_pml_tbl(src_pml_index1).OFFSET_UOM= l_target_pml_tbl(src_pml_index1).OFFSET_UOM
4139 AND l_source_pml_tbl(src_pml_index1).AUTOSCHEDULE_YN= l_target_pml_tbl(src_pml_index1).AUTOSCHEDULE_YN THEN
4140 l_pml_match :='Y';
4141 ELSE
4142 l_pml_match :='N';
4143 exit;
4144 END IF;
4145 END LOOP;
4146 IF l_pml_match ='N' THEN
4147 RAISE G_MISMATCH ;
4148 END IF;
4149 END IF;
4150
4151 l_source_pml_tbl.delete;
4152 l_target_pml_tbl.delete;
4153 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
4154 x_pm_stream_match:= 'Y';
4155 Exception
4156
4157 When G_MISMATCH THEN
4158 x_pm_stream_match:= 'N';
4159 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
4160
4161 WHEN OTHERS THEN
4162 OKC_API.set_message(G_APP_NAME,
4163 G_UNEXPECTED_ERROR,
4164 G_SQLCODE_TOKEN,
4165 SQLCODE,
4166 G_SQLERRM_TOKEN,
4167 SQLERRM);
4168 -- notify caller of an UNEXPECTED error
4169 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4170 x_pm_stream_match:= 'E';
4171
4172
4173 END CHECK_PM_STREAM_MATCH;
4174
4175 Procedure CHECK_PM_MATCH
4176 ( p_api_version IN NUMBER,
4177 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4178 x_return_status OUT NOCOPY VARCHAR2,
4179 x_msg_count OUT NOCOPY NUMBER,
4180 x_msg_data OUT NOCOPY VARCHAR2,
4181 P_Source_coverage_Line_Id IN NUMBER,
4182 P_Target_coverage_Line_Id IN NUMBER,
4183 x_pm_match OUT NOCOPY VARCHAR2)
4184 IS
4185
4186 CURSOR CU_get_PMA(cp_cov_line_id NUMBER) IS
4187 SELECT
4188 ID ,
4189 CLE_ID,
4190 ACTIVITY_ID,
4191 SELECT_YN,
4192 CONF_REQ_YN,
4193 SCH_EXISTS_YN
4194 FROM
4195 oks_pm_activities
4196 WHERE
4197 cle_id=cp_cov_line_id;
4198
4199 -- GLOBAL VARIABLES
4200 src_pma_index NUMBER :=0;
4201 tgt_pma_index NUMBER :=0;
4202 src_pma_index1 NUMBER :=0;
4203 tgt_pma_index1 NUMBER :=0;
4204
4205 l_source_pma_tbl oks_pma_pvt.pmav_tbl_type;
4206 l_target_pma_tbl oks_pma_pvt.pmav_tbl_type;
4207
4208 G_MISMATCH EXCEPTION ;
4209 --07/28/2004
4210 l_pma_match VARCHAR2(1);
4211 l_pm_stream_match VARCHAR2(1);
4212 l_api_version CONSTANT NUMBER := 1.0;
4213 l_init_msg_list CONSTANT VARCHAR2(1):= 'F';
4214 l_return_status VARCHAR2(1);
4215 l_msg_count NUMBER;
4216 l_msg_data VARCHAR2(2000):=null;
4217 l_Source_coverage_Line_Id NUMBER;
4218 l_Target_coverage_Line_Id NUMBER;
4219
4220 BEGIN
4221 --Compare Program Stream Levels
4222 --
4223 l_Source_coverage_Line_Id :=P_Source_coverage_Line_Id;
4224 l_Target_coverage_Line_Id :=P_Target_coverage_Line_Id;
4225 check_pm_stream_match
4226 ( p_api_version => l_api_version,
4227 p_init_msg_list => l_init_msg_list,
4228 x_return_status => l_return_status,
4229 x_msg_count => l_msg_count,
4230 x_msg_data => l_msg_data,
4231 P_Source_coverage_Line_Id => l_Source_coverage_Line_Id,
4232 P_Target_coverage_Line_Id => l_Target_coverage_Line_Id,
4233 P_Source_activity_Line_Id => null,
4234 P_Target_activity_Line_Id => null,
4235 x_pm_stream_match => l_pm_stream_match);
4236
4237 IF l_pm_stream_match <> 'Y' THEN
4238 Raise G_MISMATCH;
4239 END IF;
4240
4241 --Compare Activity lines
4242 FOR CR_get_PMA IN Cu_get_PMA(P_Source_coverage_Line_Id) LOOP
4243 src_pma_index :=src_pma_index+1;
4244 l_source_pma_tbl(src_pma_index).ID := CR_get_PMA.ID;
4245 l_source_pma_tbl(src_pma_index).ACTIVITY_ID := CR_get_PMA.ACTIVITY_ID;
4246 l_source_pma_tbl(src_pma_index).SELECT_YN := CR_get_PMA.SELECT_YN;
4247 l_source_pma_tbl(src_pma_index).CONF_REQ_YN := CR_get_PMA.CONF_REQ_YN;
4248 l_source_pma_tbl(src_pma_index).SCH_EXISTS_YN := CR_get_PMA.SCH_EXISTS_YN;
4249 END LOOP;
4250 FOR CR_get_PMA IN Cu_get_PMA(P_target_coverage_Line_Id) LOOP
4251 tgt_pma_index :=tgt_pma_index+1;
4252 l_target_pma_tbl(tgt_pma_index).ID := CR_get_PMA.ID;
4253 l_target_pma_tbl(tgt_pma_index).ACTIVITY_ID := CR_get_PMA.ACTIVITY_ID;
4254 l_target_pma_tbl(tgt_pma_index).SELECT_YN := CR_get_PMA.SELECT_YN;
4255 l_target_pma_tbl(tgt_pma_index).CONF_REQ_YN := CR_get_PMA.CONF_REQ_YN;
4256 l_target_pma_tbl(tgt_pma_index).SCH_EXISTS_YN := CR_get_PMA.SCH_EXISTS_YN;
4257 END LOOP;
4258 IF l_source_pma_tbl.count <> l_target_pma_tbl.count Then
4259 RAISE G_MISMATCH ;
4260 END IF ;
4261 IF l_source_pma_tbl.count >0 then
4262 FOR src_pma_index1 IN l_source_pma_tbl.FIRST..l_source_pma_tbl.LAST
4263 LOOP
4264 FOR tgt_pma_index1 IN l_target_pma_tbl.FIRST..l_target_pma_tbl.LAST
4265 LOOP
4266 IF l_source_pma_tbl(src_pma_index1).ACTIVITY_ID = l_target_pma_tbl(tgt_pma_index1).ACTIVITY_ID
4267 AND l_source_pma_tbl(src_pma_index1).SELECT_YN = l_target_pma_tbl(tgt_pma_index1).SELECT_YN
4268 AND l_source_pma_tbl(src_pma_index1).CONF_REQ_YN = l_target_pma_tbl(tgt_pma_index1).CONF_REQ_YN
4269 AND l_source_pma_tbl(src_pma_index1).SCH_EXISTS_YN = l_target_pma_tbl(tgt_pma_index1).SCH_EXISTS_YN THEN
4270 l_pma_match :='Y';
4271 check_pm_stream_match
4272 ( p_api_version => l_api_version,
4273 p_init_msg_list => l_init_msg_list,
4274 x_return_status => l_return_status,
4275 x_msg_count => l_msg_count,
4276 x_msg_data => l_msg_data,
4277 P_Source_coverage_Line_Id => l_Source_coverage_Line_Id,
4278 P_Target_coverage_Line_Id => l_Target_coverage_Line_Id,
4279 P_Source_activity_Line_Id => l_source_pma_tbl(src_pma_index1).ID,
4280 P_Target_activity_Line_Id => l_target_pma_tbl(tgt_pma_index1).ID,
4281 x_pm_stream_match => l_pm_stream_match);
4282
4283 IF l_pm_stream_match <> 'Y' THEN
4284 Raise G_MISMATCH;
4285 ELSE
4286 exit;
4287 END IF;
4288 ELSE
4289 l_pma_match :='N';
4290 END IF;
4291 END LOOP;
4292 IF l_pma_match ='N' THEN
4293 RAISE G_MISMATCH ;
4294 END IF;
4295 END LOOP;
4296 END IF ;
4297 --===
4298 l_source_pma_tbl.delete;
4299 l_target_pma_tbl.delete;
4300
4301 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
4302 x_pm_match:= 'Y';
4303 Exception
4304
4305 When G_MISMATCH THEN
4306 x_pm_match:= 'N';
4307 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
4308
4309 WHEN OTHERS THEN
4310 OKC_API.set_message(G_APP_NAME,
4311 G_UNEXPECTED_ERROR,
4312 G_SQLCODE_TOKEN,
4313 SQLCODE,
4314 G_SQLERRM_TOKEN,
4315 SQLERRM);
4316 -- notify caller of an UNEXPECTED error
4317 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4318 x_pm_match:= 'E';
4319
4320
4321 END CHECK_PM_MATCH;
4322
4323 --Procedure modified on 02/19/04 to check if program/activity has been terminated
4324 PROCEDURE CHECK_PM_PROGRAM_EFFECTIVITY (
4325 x_return_status OUT NOCOPY VARCHAR2,
4326 p_chr_id IN NUMBER
4327 )
4328 IS
4329 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4330 l_dummy VARCHAR2(1) := '?';
4331 l_count NUMBER := 0;
4332 l_prog_term_flag VARCHAR2(1);
4333 CURSOR l_cle_csr(p_chr_id IN NUMBER) IS
4334 SELECT srvcle.id
4335 , srvcle.line_number
4336 FROM
4337 OKC_K_LINES_B srvcle,
4338 OKC_K_LINES_B covcle,
4339 OKS_K_LINES_B cov
4340 WHERE
4341 srvcle.chr_id = p_chr_id
4342 and srvcle.id=covcle.cle_id
4343 and covcle.id=cov.cle_id
4344 and cov.pm_program_id is not null
4345 and covcle.lse_id in (2,15,20);
4346
4347 l_cle_rec l_cle_csr%ROWTYPE;
4348
4349
4350 CURSOR l_prog_csr(p_cle_id IN NUMBER) IS
4351 select 'x' terminate
4352 FROM okc_k_lines_b cle,
4353 oks_k_lines_b pmp,
4354 okx_pm_programs_v opv
4355 WHERE cle.id = pmp.cle_id
4356 and cle.dnz_chr_id = pmp.dnz_chr_id
4357 and pmp.pm_program_id= opv.id1
4358 and cle.cle_id=p_cle_id
4359 -- and opv.mr_status_code ='TERMINATED';
4360 and opv.mr_status_code in ('TERMINATED','TERMINATE_PENDING');
4361 l_prog_rec l_prog_csr%ROWTYPE;
4362
4363 CURSOR l_act_csr(p_cle_id IN NUMBER) IS
4364 select 'x' terminate
4365 FROM okc_k_lines_b cle,
4366 oks_k_lines_b pmp,
4367 oks_pm_activities oksact,
4368 okx_pm_activities_v act
4369 WHERE cle.id = pmp.cle_id
4370 and cle.dnz_chr_id = pmp.dnz_chr_id
4371 --changed for using index and pmp.pm_program_id= oksact.program_id
4372 and pmp.cle_id= oksact.cle_id
4373 and oksact.activity_id=act.id1
4374 and cle.cle_id=p_cle_id
4375 -- and act.mr_status_code = 'TERMINATED'
4376 and act.mr_status_code in ('TERMINATED','TERMINATE_PENDING')
4377 and rownum <2;
4378 l_act_rec l_act_csr%ROWTYPE;
4379
4380 BEGIN
4381 -- initialize return status
4382 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4383
4384 OPEN l_cle_csr(p_chr_id);
4385
4386 LOOP
4387 FETCH l_cle_csr INTO l_cle_rec;
4388 EXIT WHEN l_cle_csr%NOTFOUND;
4389 l_prog_term_flag :='N';
4390 OPEN l_prog_csr(l_cle_rec.id);
4391 FETCH l_prog_csr into l_prog_rec;
4392 If l_prog_csr%FOUND THEN
4393 If l_prog_rec.terminate is not null THEN
4394 OKC_API.set_message(
4395 p_app_name => G_APP_NAME_OKS,
4396 p_msg_name => 'OKS_PM_PROGRAM_EFFECTIVITY',
4397 p_token1 => 'CONTRACT_LINE',
4398 p_token1_value =>l_cle_rec.line_number);
4399 -- notify caller of an error
4400 --modified to return 'E' instead of 'W' x_return_status := 'W'; --OKC_API.G_RET_STS_ERROR;
4401 x_return_status := 'E'; --OKC_API.G_RET_STS_ERROR;
4402 l_prog_term_flag :='Y';
4403 End if;--03/01
4404 END IF;
4405 CLOSE l_prog_csr;
4406 IF l_prog_term_flag <> 'Y' THEN --03/01
4407 OPEN l_act_csr(l_cle_rec.id);
4408 FETCH l_act_csr into l_act_rec;
4409 If l_act_csr%FOUND THEN
4410 If l_act_rec.terminate is not null THEN
4411 OKC_API.set_message(
4412 p_app_name => G_APP_NAME_OKS,
4413 p_msg_name => 'OKS_PM_PROGRAM_EFFECTIVITY',
4414 -- token replaced p_token1 => 'LINE_NUMBER',
4415 p_token1 => 'CONTRACT_LINE',
4416 p_token1_value =>l_cle_rec.line_number);
4417 -- notify caller of an error
4418 --modified to return 'E' instead of 'W' x_return_status := 'W'; --OKC_API.G_RET_STS_ERROR;
4419 x_return_status := 'E';
4420 End If;
4421 End if;
4422 CLOSE l_act_csr;
4423 END IF;--03/01
4424 END LOOP;
4425 CLOSE l_cle_csr;
4426
4427 EXCEPTION
4428 WHEN G_EXCEPTION_HALT_VALIDATION THEN
4429 -- no processing necessary; validation can continue with next column
4430 NULL;
4431 WHEN OTHERS THEN
4432 -- store SQL error message on message stack
4433
4434 OKC_API.SET_MESSAGE(
4435 p_app_name => G_APP_NAME,
4436 p_msg_name => G_UNEXPECTED_ERROR,
4437 p_token1 => G_SQLCODE_TOKEN,
4438 p_token1_value => SQLCODE,
4439 p_token2 => G_SQLERRM_TOKEN,
4440 p_token2_value => SQLERRM);
4441 -- notify caller of an error as UNEXPETED error
4442 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4443 -- verify that cursor was closed
4444 IF l_cle_csr%ISOPEN THEN
4445 CLOSE l_cle_csr;
4446 END IF;
4447 IF l_prog_csr%ISOPEN THEN
4448 CLOSE l_prog_csr;
4449 END IF;
4450 END CHECK_PM_PROGRAM_EFFECTIVITY;
4451
4452 --new procedure for QA check with return as 'Error'
4453 PROCEDURE CHECK_PM_REQUIRED_VALUES (
4454 x_return_status OUT NOCOPY VARCHAR2,
4455 p_chr_id IN NUMBER
4456 )
4457 IS
4458 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4459 l_dummy VARCHAR2(1) := '?';
4460 l_count NUMBER := 0;
4461 l_prog_term_flag VARCHAR2(1);
4462 l_prog1_term_flag VARCHAR2(1);
4463 CURSOR l_cle_csr(p_chr_id IN NUMBER) IS
4464 SELECT srvcle.id
4465 , srvcle.line_number
4466 FROM
4467 OKC_K_LINES_B srvcle,
4468 OKC_K_LINES_B covcle,
4469 OKS_K_LINES_B cov
4470 WHERE
4471 srvcle.chr_id = p_chr_id
4472 and srvcle.id=covcle.cle_id
4473 and covcle.id=cov.cle_id
4474 and cov.pm_program_id is not null
4475 and covcle.lse_id in (2,15,20);
4476
4477 l_cle_rec l_cle_csr%ROWTYPE;
4478
4479 CURSOR l_prog_csr(p_cle_id IN NUMBER) IS
4480 select opv.mr_header_id
4481 FROM okc_k_lines_b cle,
4482 oks_k_lines_b pmp,
4483 --okx_pm_programs_v opv
4484 ahl_mr_headers_b opv
4485 WHERE cle.id = pmp.cle_id
4486 and cle.dnz_chr_id = pmp.dnz_chr_id
4487 and pmp.pm_program_id= opv.mr_header_id
4488 and cle.cle_id=p_cle_id
4489 and trunc(opv.effective_from) > trunc(cle.start_date);
4490 l_prog_rec l_prog_csr%ROWTYPE;
4491
4492 CURSOR l_act_csr(p_cle_id IN NUMBER) IS
4493 select act.mr_header_id
4494 FROM okc_k_lines_b cle,
4495 oks_k_lines_b pmp,
4496 oks_pm_activities oksact,
4497 ahl_mr_headers_b act
4498 WHERE cle.id = pmp.cle_id
4499 and cle.dnz_chr_id = pmp.dnz_chr_id
4500 and pmp.cle_id= oksact.cle_id
4501 and oksact.activity_id=act.mr_header_id
4502 and cle.cle_id=p_cle_id
4503 and trunc(act.effective_from) > trunc(cle.start_date)
4504 and rownum <2;
4505 l_act_rec l_act_csr%ROWTYPE;
4506 BEGIN
4507 -- initialize return status
4508 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4509
4510 OPEN l_cle_csr(p_chr_id);
4511
4512 LOOP
4513 FETCH l_cle_csr INTO l_cle_rec;
4514 EXIT WHEN l_cle_csr%NOTFOUND;
4515 l_prog_term_flag :='N';
4516 OPEN l_prog_csr(l_cle_rec.id);
4517 FETCH l_prog_csr into l_prog_rec;
4518 If l_prog_csr%FOUND THEN
4519 If l_prog_rec.mr_header_id is not null THEN
4520 OKC_API.set_message(
4521 p_app_name => G_APP_NAME_OKS,
4522 p_msg_name => 'OKS_PM_MR_EFFECTIVITY',
4523 p_token1 => 'CONTRACT_LINE',
4524 p_token1_value =>l_cle_rec.line_number);
4525 -- notify caller of an error
4526 x_return_status := 'E'; --OKC_API.G_RET_STS_ERROR;
4527 l_prog_term_flag :='Y';
4528 End if;
4529 END IF;
4530 CLOSE l_prog_csr;
4531 IF l_prog_term_flag <> 'Y' THEN
4532 OPEN l_act_csr(l_cle_rec.id);
4533 FETCH l_act_csr into l_act_rec;
4534 If l_act_csr%FOUND THEN
4535 If l_act_rec.mr_header_id is not null THEN
4536 OKC_API.set_message(
4537 p_app_name => G_APP_NAME_OKS,
4538 p_msg_name => 'OKS_PM_MR_EFFECTIVITY',
4539 p_token1 => 'CONTRACT_LINE',
4540 p_token1_value =>l_cle_rec.line_number);
4541 -- notify caller of an error
4542 x_return_status := 'E'; --OKC_API.G_RET_STS_ERROR;
4543 End If;
4544 End if;
4545 CLOSE l_act_csr;
4546 END IF;
4547 END LOOP;
4548 CLOSE l_cle_csr;
4549
4550 EXCEPTION
4551 WHEN G_EXCEPTION_HALT_VALIDATION THEN
4552 -- no processing necessary; validation can continue with next column
4553 NULL;
4554 WHEN OTHERS THEN
4555 -- store SQL error message on message stack
4556
4557 OKC_API.SET_MESSAGE(
4558 p_app_name => G_APP_NAME,
4559 p_msg_name => G_UNEXPECTED_ERROR,
4560 p_token1 => G_SQLCODE_TOKEN,
4561 p_token1_value => SQLCODE,
4562 p_token2 => G_SQLERRM_TOKEN,
4563 p_token2_value => SQLERRM);
4564 -- notify caller of an error as UNEXPETED error
4565 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4566 -- verify that cursor was closed
4567 IF l_cle_csr%ISOPEN THEN
4568 CLOSE l_cle_csr;
4569 END IF;
4570 IF l_prog_csr%ISOPEN THEN
4571 CLOSE l_prog_csr;
4572 END IF;
4573 END CHECK_PM_REQUIRED_VALUES;
4574
4575
4576 PROCEDURE check_pm_schedule (
4577 x_return_status OUT NOCOPY VARCHAR2,
4578 p_chr_id IN NUMBER
4579 )
4580 IS
4581 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4582 l_dummy VARCHAR2(1) := '?';
4583 l_count NUMBER := 0;
4584 /* Modified by jvorugan for Bug:5215180
4585 CURSOR l_cle_csr(p_chr_id IN NUMBER) IS
4586 SELECT srvcle.id
4587 , srvcle.line_number
4588 FROM
4589 OKC_K_LINES_B srvcle,
4590 OKC_K_LINES_B covcle,
4591 OKS_K_LINES_B cov
4592 WHERE
4593 srvcle.chr_id = p_chr_id
4594 and srvcle.id=covcle.cle_id
4595 and covcle.id=cov.cle_id
4596 and cov.pm_program_id is not null
4597 and covcle.lse_id in (2,15,20);
4598 */
4599
4600 CURSOR l_cle_csr(p_chr_id IN NUMBER) IS
4601 SELECT srvcle.id
4602 , srvcle.line_number
4603 FROM
4604 OKC_K_LINES_B srvcle,
4605 OKS_K_LINES_B cov
4606 WHERE
4607 srvcle.chr_id = p_chr_id
4608 and srvcle.id=cov.cle_id
4609 and cov.pm_program_id is not null
4610 and srvcle.lse_id in (1,14,19);
4611
4612
4613 /* CURSOR l_cle_csr(p_chr_id IN NUMBER) IS
4614 SELECT cle.id
4615 , lse.lty_code
4616 , cle.name
4617 , cle.start_date
4618 , cle.end_date
4619 , cim.jtot_object1_code
4620 , cim.object1_id1
4621 , cim.object1_id2
4622 , cle.line_number
4623 FROM OKC_K_ITEMS cim,
4624 OKC_LINE_STYLES_B lse,
4625 OKC_K_LINES_V cle
4626 WHERE cim.cle_id = cle.id
4627 and lse.id = cle.lse_id
4628 and cle.chr_id = p_chr_id
4629 and cle.lse_id in (2,15,20);*/
4630
4631 l_cle_rec l_cle_csr%ROWTYPE;
4632 --not needed
4633 /* CURSOR l_prog_csr(p_cle_id IN NUMBER) IS
4634 SELECT pmp.id
4635 FROM okc_k_lines_b cle,oks_k_lines_b pmp
4636 WHERE cle.id = pmp.cle_id
4637 and cle.dnz_chr_id = pmp.dnz_chr_id
4638 and cle.cle_id=p_cle_id
4639 and pmp.pm_program_id is not null;
4640
4641 l_prog_rec l_prog_csr%ROWTYPE;*/
4642
4643 /* Modified by Jvorugan for Bug:5215180
4644 CURSOR l_sch_csr(p_cle_id IN NUMBER) IS
4645
4646 SELECT pms.id
4647 FROM okc_k_lines_b cle,
4648 oks_pm_schedules_v pms
4649 WHERE cle.id = pms.cle_id
4650 and cle.cle_id=p_cle_id
4651 --added condition
4652 and pms.activity_line_id is null;
4653 */
4654 CURSOR l_sch_csr(p_cle_id IN NUMBER) IS
4655
4656 SELECT pms.id
4657 FROM oks_pm_schedules_v pms
4658 WHERE pms.cle_id = p_cle_id
4659 --added condition
4660 and pms.activity_line_id is null;
4661
4662 l_sch_rec l_sch_csr%ROWTYPE;
4663 --CK
4664 /* Modified by Jvorugan for Bug:5215180
4665 CURSOR l_act_csr(p_cle_id IN NUMBER) IS
4666 SELECT pma.id
4667 FROM okc_k_lines_b cle,
4668 oks_pm_activities pma
4669 WHERE cle.id = pma.cle_id
4670 and cle.cle_id=p_cle_id;
4671 */
4672 CURSOR l_act_csr(p_cle_id IN NUMBER) IS
4673 SELECT pma.id
4674 FROM oks_pm_activities pma
4675 WHERE pma.cle_id = p_cle_id;
4676 l_act_rec l_act_csr%ROWTYPE;
4677
4678
4679 /* Modified by JVorugan for Bug:5215180
4680 CURSOR l_actsch_csr(p_cle_id IN NUMBER) IS
4681 SELECT pms.id
4682 FROM okc_k_lines_b cle,
4683 oks_pm_schedules_v pms
4684 WHERE cle.id = pms.cle_id
4685 and cle.cle_id=p_cle_id
4686 and activity_line_id is not null;
4687 */
4688 CURSOR l_actsch_csr(p_cle_id IN NUMBER) IS
4689 SELECT pms.id
4690 FROM oks_pm_schedules_v pms
4691 WHERE pms.cle_id = p_cle_id
4692 and activity_line_id is not null;
4693
4694
4695 l_actsch_rec l_actsch_csr%ROWTYPE;
4696
4697
4698 BEGIN
4699 -- initialize return status
4700 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4701
4702 OPEN l_cle_csr(p_chr_id);
4703
4704 LOOP
4705
4706 FETCH l_cle_csr INTO l_cle_rec;
4707 EXIT WHEN l_cle_csr%NOTFOUND;
4708 /* OPEN l_prog_csr(l_cle_rec.id);
4709 FETCH l_prog_csr into l_prog_rec;
4710 If (l_prog_csr%FOUND) Then
4711 */
4712 OPEN l_sch_csr(l_cle_rec.id);
4713 FETCH l_sch_csr into l_sch_rec;
4714 If l_sch_csr%NOTFOUND Then
4715
4716 OKC_API.set_message(
4717 p_app_name => G_APP_NAME_OKS,
4718 p_msg_name => 'OKS_PM_REQUIRED_SCHEDULE',
4719 --03/23 modified token
4720 -- p_token1 => 'LINE_NUMBER',
4721 p_token1 => 'CONTRACT_LINE',
4722 p_token1_value =>l_cle_rec.line_number);
4723
4724 -- notify caller of an error
4725 --modified to return 'E' instead of 'W' x_return_status := 'W'; --OKC_API.G_RET_STS_ERROR;
4726 x_return_status := 'E';
4727
4728 End If;
4729 -- END IF;
4730
4731 IF l_sch_csr%ISOPEN THEN
4732 CLOSE l_sch_csr;
4733 END IF;
4734 -- CLOSE l_prog_csr;
4735 END LOOP;
4736
4737 --CK
4738 OPEN l_act_csr(l_cle_rec.id);
4739 FETCH l_act_csr into l_act_rec;
4740 If (l_act_csr%FOUND) Then
4741 OPEN l_actsch_csr(l_cle_rec.id);
4742 FETCH l_actsch_csr into l_actsch_rec;
4743 If (l_actsch_csr%NOTFOUND) Then
4744
4745 OKC_API.set_message(
4746 p_app_name => G_APP_NAME_OKS,
4747 p_msg_name => 'OKS_PMACT_REQUIRED_SCHEDULE',
4748 --03/23 modified token
4749 -- p_token1 => 'LINE_NUMBER',
4750 p_token1 => 'CONTRACT_LINE',
4751 p_token1_value =>l_cle_rec.line_number);
4752 -- notify caller of an error
4753 --modified to return 'E' instead of 'W' x_return_status := 'W'; --OKC_API.G_RET_STS_ERROR;
4754 x_return_status := 'E'; --OKC_API.G_RET_STS_ERROR;
4755 End If;
4756 CLOSE l_actsch_csr;
4757 END if;
4758 CLOSE l_act_csr;
4759 --CK
4760 CLOSE l_cle_csr;
4761
4762 EXCEPTION
4763 WHEN G_EXCEPTION_HALT_VALIDATION THEN
4764 -- no processing necessary; validation can continue with next column
4765 NULL;
4766 WHEN OTHERS THEN
4767 -- store SQL error message on message stack
4768
4769 OKC_API.SET_MESSAGE(
4770 p_app_name => G_APP_NAME,
4771 p_msg_name => G_UNEXPECTED_ERROR,
4772 p_token1 => G_SQLCODE_TOKEN,
4773 p_token1_value => SQLCODE,
4774 p_token2 => G_SQLERRM_TOKEN,
4775 p_token2_value => SQLERRM);
4776 -- notify caller of an error as UNEXPETED error
4777 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4778 -- verify that cursor was closed
4779 IF l_cle_csr%ISOPEN THEN
4780 CLOSE l_cle_csr;
4781 END IF;
4782 /* IF l_prog_csr%ISOPEN THEN
4783 CLOSE l_prog_csr;
4784 END IF;*/
4785
4786 IF l_sch_csr%ISOPEN THEN
4787 CLOSE l_sch_csr;
4788 END IF;
4789
4790 END check_pm_schedule;
4791
4792 --02/18 added new qa check to check for new activities added to program in CMRO
4793 PROCEDURE check_pm_new_activities(
4794 x_return_status OUT NOCOPY VARCHAR2,
4795 p_chr_id IN NUMBER
4796 )
4797 IS
4798 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4799 l_dummy VARCHAR2(1) := '?';
4800 l_count NUMBER := 0;
4801 l_act_tbl act_tbl_type;
4802 /* Modified by Jvorugan for Bug:5215180
4803 CURSOR l_cle_csr(p_chr_id IN NUMBER) IS
4804 SELECT srvcle.id
4805 , srvcle.line_number
4806 FROM
4807 OKC_K_LINES_B srvcle,
4808 OKC_K_LINES_B covcle,
4809 OKS_K_LINES_B cov
4810 WHERE
4811 srvcle.chr_id = p_chr_id
4812 and srvcle.id=covcle.cle_id
4813 and covcle.id=cov.cle_id
4814 and cov.pm_program_id is not null
4815 and covcle.lse_id in (2,15,20);
4816 */
4817 CURSOR l_cle_csr(p_chr_id IN NUMBER) IS
4818 SELECT srvcle.id
4819 , srvcle.line_number
4820 FROM
4821 OKC_K_LINES_B srvcle,
4822 OKS_K_LINES_B cov
4823 WHERE
4824 srvcle.chr_id = p_chr_id
4825 and srvcle.id=cov.cle_id
4826 and cov.pm_program_id is not null
4827 and srvcle.lse_id in (1,14,19);
4828
4829
4830 l_cle_rec l_cle_csr%ROWTYPE;
4831
4832 --Cursor to fetch activities for the program defined in CMRO
4833 CURSOR l_prog_csr(p_cle_id IN NUMBER) IS
4834 SELECT act.id1 activity_id
4835 FROM okc_k_lines_b cle,
4836 oks_k_lines_b okscle,
4837 okx_pm_activities_v act
4838 WHERE cle.id = okscle.cle_id
4839 and cle.dnz_chr_id = okscle.dnz_chr_id
4840 and okscle.pm_program_id= act.program_id
4841 and cle.id =p_cle_id
4842 -- modified by Jvorugan for Bug:5215180 and cle.cle_id=p_cle_id
4843 and act.mr_status_code='COMPLETE';
4844 l_prog_rec l_prog_csr%ROWTYPE;
4845
4846 --Cursor to fetch activities for the program associated with the service line
4847 CURSOR l_act_csr(p_cle_id IN NUMBER) IS
4848 SELECT oksact.activity_id
4849 FROM okc_k_lines_b cle,
4850 oks_k_lines_b okscle,
4851 oks_pm_activities_v oksact
4852 WHERE cle.id = okscle.cle_id
4853 and cle.dnz_chr_id = okscle.dnz_chr_id
4854 and cle.id=oksact.cle_id
4855 and cle.dnz_chr_id=oksact.dnz_chr_id
4856 and cle.id=p_cle_id;
4857 -- Modified by Jvorugan for Bug:5215180 and cle.cle_id=p_cle_id;
4858
4859 l_act_rec l_act_csr%ROWTYPE;
4860 l_act_ctr NUMBER;
4861 l_act_ctr1 NUMBER;
4862 l_act_exists VARCHAR2(1):='Y';
4863 BEGIN
4864 -- initialize return status
4865 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4866
4867 OPEN l_cle_csr(p_chr_id);
4868
4869 LOOP
4870 FETCH l_cle_csr INTO l_cle_rec;
4871 EXIT WHEN l_cle_csr%NOTFOUND;
4872 l_act_tbl.DELETE;
4873 OPEN l_prog_csr(l_cle_rec.id);
4874 l_act_ctr :=0;
4875 LOOP
4876 l_act_ctr :=l_act_ctr+1;
4877 FETCH l_prog_csr into l_act_tbl(l_act_ctr).activity_id;
4878 EXIT WHEN l_prog_csr%NOTFOUND;
4879 END LOOP;
4880 CLOSE l_prog_csr;
4881 l_act_ctr1 :=1;
4882 IF l_act_tbl.count > 0 THEN
4883 FOR l_act_ctr1 in 1..l_act_tbl.count LOOP
4884 OPEN l_act_csr(l_cle_rec.id);
4885 LOOP
4886 FETCH l_act_csr into l_act_rec;
4887 IF l_act_tbl(l_act_ctr1).activity_id=l_act_rec.activity_id THEN
4888 l_act_exists :='Y';
4889 exit;
4890 ELSE
4891 l_act_exists :='N';
4892 END IF;
4893 -- l_act_ctr1 :=l_act_ctr1+1;
4894 EXIT WHEN l_act_csr%NOTFOUND;
4895 END LOOP;
4896 CLOSE l_act_csr;
4897 -- END LOOP;
4898 IF l_act_exists ='N' THEN
4899 OKC_API.set_message(
4900 p_app_name => G_APP_NAME_OKS,
4901 p_msg_name => 'OKS_PM_NEW_ACTIVITIES',
4902 -- 03/23 changed token p_token1 => 'LINE_NUMBER',
4903 p_token1 => 'CONTRACT_LINE',
4904 p_token1_value =>l_cle_rec.line_number);
4905 -- notify caller of an error
4906 --modified to return 'E' instead of 'W' x_return_status := 'W'; --OKC_API.G_RET_STS_ERROR;
4907 x_return_status := 'E'; --OKC_API.G_RET_STS_ERROR;
4908 END IF;
4909 END LOOP;
4910 END IF;
4911 END LOOP;
4912 CLOSE l_cle_csr;
4913
4914 EXCEPTION
4915 WHEN G_EXCEPTION_HALT_VALIDATION THEN
4916 -- no processing necessary; validation can continue with next column
4917 NULL;
4918 WHEN OTHERS THEN
4919 -- store SQL error message on message stack
4920
4921 OKC_API.SET_MESSAGE(
4922 p_app_name => G_APP_NAME,
4923 p_msg_name => G_UNEXPECTED_ERROR,
4924 p_token1 => G_SQLCODE_TOKEN,
4925 p_token1_value => SQLCODE,
4926 p_token2 => G_SQLERRM_TOKEN,
4927 p_token2_value => SQLERRM);
4928 -- notify caller of an error as UNEXPETED error
4929 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4930 -- verify that cursor was closed
4931 IF l_cle_csr%ISOPEN THEN
4932 CLOSE l_cle_csr;
4933 END IF;
4934 IF l_prog_csr%ISOPEN THEN
4935 CLOSE l_prog_csr;
4936 END IF;
4937 END check_pm_new_activities;
4938
4939 PROCEDURE UNDO_PM_LINE(
4940 p_api_version IN NUMBER,
4941 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4942 x_return_status OUT NOCOPY VARCHAR2,
4943 x_msg_count OUT NOCOPY NUMBER,
4944 x_msg_data OUT NOCOPY VARCHAR2,
4945 p_cle_id IN NUMBER--COVERAGE LINE ID
4946 ) IS
4947
4948 CURSOR CU_GET_PMA IS
4949 SELECT ID FROM OKS_PM_ACTIVITIES
4950 WHERE CLE_ID = p_cle_id;
4951
4952 CURSOR CU_GET_PML IS
4953 SELECT ID FROM OKS_PM_STREAM_LEVELS
4954 WHERE CLE_ID = p_cle_id;
4955
4956 CURSOR CU_GET_SCH IS
4957 SELECT ID FROM OKS_PM_SCHEDULES
4958 WHERE CLE_ID = p_cle_id;
4959 l_pmav_tbl OKS_PMA_PVT.pmav_tbl_type ;
4960 l_pmlv_tbl OKS_PML_PVT.pmlv_tbl_type ;
4961 l_pm_schedules_v_tbl OKS_PMS_PVT.oks_pm_schedules_v_tbl_type ;
4962 l_pma_index NUMBER :=0;
4963 l_pml_index NUMBER :=0;
4964 l_sch_index NUMBER :=0;
4965 l_api_version CONSTANT NUMBER := 1.0;
4966 l_init_msg_list CONSTANT VARCHAR2(1):= 'F';
4967 l_return_status VARCHAR2(1);
4968 l_msg_count NUMBER;
4969 l_msg_data VARCHAR2(2000):=null;
4970 l_msg_index_out Number;
4971 l_api_name CONSTANT VARCHAR2(30) := 'UNDO PM LINE';
4972
4973 BEGIN
4974 IF (G_DEBUG_ENABLED = 'Y') THEN
4975 okc_debug.Set_Indentation('Undo_PM_Line');
4976 okc_debug.log('Entered Undo_PM_Line', 3);
4977 END IF;
4978 x_return_status:=OKC_API.G_Ret_Sts_Success;
4979 --Deleting activities
4980 FOR CR_GET_PMA IN CU_GET_PMA
4981 LOOP
4982 l_pma_index:= l_pma_index + 1 ;
4983 l_pmav_tbl(l_pma_index).id:= CR_GET_PMA.ID;
4984 END LOOP ;
4985
4986 IF l_pmav_tbl.count <> 0 then
4987 OKS_PMA_PVT.delete_row(
4988 p_api_version => l_api_version,
4989 p_init_msg_list => l_init_msg_list,
4990 x_return_status => l_return_status,
4991 x_msg_count => l_msg_count,
4992 x_msg_data => l_msg_data,
4993 p_pmav_tbl => l_pmav_tbl);
4994 END IF ;
4995 IF (G_DEBUG_ENABLED = 'Y') THEN
4996 okc_debug.log('After OKS_PMA_PVT delete_row', 3);
4997 END IF;
4998
4999 IF NOT nvl(l_return_status,'S') = OKC_API.G_RET_STS_SUCCESS THEN
5000 x_return_status := OKC_API.G_RET_STS_ERROR;
5001 OKC_API.Set_Message(G_APP_NAME,G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'ERROR IN DELETING PM Activities');
5002 RETURN;
5003 END IF;
5004 --Deleting stream levels
5005 FOR CR_GET_PML IN CU_GET_PML
5006 LOOP
5007 l_pml_index:= l_pml_index + 1 ;
5008 l_pmlv_tbl(l_pml_index).id:= CR_GET_PML.ID;
5009 END LOOP ;
5010
5011 IF l_pmlv_tbl.count <> 0 then
5012 OKS_PML_PVT.delete_row(
5013 p_api_version => l_api_version,
5014 p_init_msg_list => l_init_msg_list,
5015 x_return_status => l_return_status,
5016 x_msg_count => l_msg_count,
5017 x_msg_data => l_msg_data,
5018 p_pmlv_tbl => l_pmlv_tbl);
5019 END IF ;
5020
5021 IF (G_DEBUG_ENABLED = 'Y') THEN
5022 okc_debug.log('After OKS_PML_PVT delete_row', 3);
5023 END IF;
5024
5025 IF NOT nvl(l_return_status,'S') = OKC_API.G_RET_STS_SUCCESS THEN
5026 x_return_status := OKC_API.G_RET_STS_ERROR;
5027 OKC_API.Set_Message(G_APP_NAME,G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'ERROR IN DELETING PM Stream Levels');
5028 RETURN;
5029 END IF;
5030 --Deleting schedules
5031 FOR CR_GET_SCH IN CU_GET_SCH
5032 LOOP
5033 l_sch_index:= l_sch_index + 1 ;
5034 l_pm_schedules_v_tbl(l_sch_index).id:= CR_GET_SCH.ID;
5035 END LOOP ;
5036
5037 IF l_pm_schedules_v_tbl.count <> 0 then
5038 OKS_PMS_PVT.delete_row(
5039 p_api_version => l_api_version,
5040 p_init_msg_list => l_init_msg_list,
5041 x_return_status => l_return_status,
5042 x_msg_count => l_msg_count,
5043 x_msg_data => l_msg_data,
5044 p_oks_pm_schedules_v_tbl => l_pm_schedules_v_tbl);
5045 END IF ;
5046
5047 IF (G_DEBUG_ENABLED = 'Y') THEN
5048 okc_debug.log('After OKS_PMS_PVT delete_row', 3);
5049 END IF;
5050
5051 IF NOT nvl(l_return_status,'S') = OKC_API.G_RET_STS_SUCCESS THEN
5052 x_return_status := OKC_API.G_RET_STS_ERROR;
5053 OKC_API.Set_Message(G_APP_NAME,G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'ERROR IN DELETING PM_SCHEDULES');
5054 RETURN;
5055 END IF;
5056 IF (G_DEBUG_ENABLED = 'Y') THEN
5057 okc_debug.log('Exiting Undo_PM_Line', 3);
5058 okc_debug.Reset_Indentation;
5059 END IF;
5060
5061 EXCEPTION
5062 WHEN OTHERS THEN
5063 x_msg_count :=l_msg_count;
5064 OKC_API.SET_MESSAGE(
5065 p_app_name => g_app_name,
5066 p_msg_name => g_unexpected_error,
5067 p_token1 => g_sqlcode_token,
5068 p_token1_value => sqlcode,
5069 p_token2 => g_sqlerrm_token,
5070 p_token2_value => sqlerrm);
5071 -- notify caller of an error as UNEXPETED error
5072 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5073 IF (G_DEBUG_ENABLED = 'Y') THEN
5074 okc_debug.log('Exiting Undo_PM_Line'||sqlerrm, 3);
5075 okc_debug.Reset_Indentation;
5076 END IF;
5077 END UNDO_PM_LINE;
5078
5079 PROCEDURE INIT_OKS_K_LINE(x_klnv_tbl OUT NOCOPY OKS_KLN_PVT.klnv_tbl_type)
5080 IS
5081
5082
5083 BEGIN
5084
5085
5086 x_klnv_tbl(1).ID := OKC_API.G_MISS_NUM;
5087 x_klnv_tbl(1).CLE_ID := OKC_API.G_MISS_NUM;
5088 x_klnv_tbl(1).DNZ_CHR_ID := OKC_API.G_MISS_NUM;
5089 x_klnv_tbl(1).DISCOUNT_LIST := OKC_API.G_MISS_NUM;
5090 x_klnv_tbl(1).ACCT_RULE_ID := OKC_API.G_MISS_NUM;
5091 x_klnv_tbl(1).PAYMENT_TYPE := OKC_API.G_MISS_CHAR;
5092 x_klnv_tbl(1).CC_NO := OKC_API.G_MISS_CHAR;
5093 x_klnv_tbl(1).CC_EXPIRY_DATE := OKC_API.G_MISS_DATE;
5094 x_klnv_tbl(1).CC_BANK_ACCT_ID := OKC_API.G_MISS_NUM;
5095 x_klnv_tbl(1).CC_AUTH_CODE := OKC_API.G_MISS_CHAR;
5096 x_klnv_tbl(1).COMMITMENT_ID := OKC_API.G_MISS_NUM;
5097 x_klnv_tbl(1).LOCKED_PRICE_LIST_ID := OKC_API.G_MISS_NUM;
5098 x_klnv_tbl(1).USAGE_EST_YN := OKC_API.G_MISS_CHAR;
5099 x_klnv_tbl(1).USAGE_EST_METHOD := OKC_API.G_MISS_CHAR;
5100 x_klnv_tbl(1).USAGE_EST_START_DATE := OKC_API.G_MISS_DATE;
5101 x_klnv_tbl(1).TERMN_METHOD := OKC_API.G_MISS_CHAR;
5102 x_klnv_tbl(1).UBT_AMOUNT := OKC_API.G_MISS_NUM;
5103 x_klnv_tbl(1).CREDIT_AMOUNT := OKC_API.G_MISS_NUM;
5104 x_klnv_tbl(1).SUPPRESSED_CREDIT := OKC_API.G_MISS_NUM;
5105 x_klnv_tbl(1).OVERRIDE_AMOUNT := OKC_API.G_MISS_NUM;
5106 x_klnv_tbl(1).GRACE_DURATION := OKC_API.G_MISS_NUM;
5107 x_klnv_tbl(1).GRACE_PERIOD := OKC_API.G_MISS_CHAR;
5108 x_klnv_tbl(1).INV_PRINT_FLAG := OKC_API.G_MISS_CHAR;
5109 x_klnv_tbl(1).PRICE_UOM := OKC_API.G_MISS_CHAR;
5110 x_klnv_tbl(1).TAX_AMOUNT := OKC_API.G_MISS_NUM;
5111 x_klnv_tbl(1).TAX_INCLUSIVE_YN := OKC_API.G_MISS_CHAR;
5112 x_klnv_tbl(1).TAX_STATUS := OKC_API.G_MISS_CHAR;
5113 x_klnv_tbl(1).TAX_CODE := OKC_API.G_MISS_NUM;
5114 x_klnv_tbl(1).TAX_EXEMPTION_ID := OKC_API.G_MISS_NUM;
5115 x_klnv_tbl(1).IB_TRANS_TYPE := OKC_API.G_MISS_CHAR;
5116 x_klnv_tbl(1).IB_TRANS_DATE := OKC_API.G_MISS_DATE;
5117 x_klnv_tbl(1).PROD_PRICE := OKC_API.G_MISS_NUM;
5118 x_klnv_tbl(1).SERVICE_PRICE := OKC_API.G_MISS_NUM;
5119 x_klnv_tbl(1).CLVL_LIST_PRICE := OKC_API.G_MISS_NUM;
5120 x_klnv_tbl(1).CLVL_QUANTITY := OKC_API.G_MISS_NUM;
5121 x_klnv_tbl(1).CLVL_EXTENDED_AMT := OKC_API.G_MISS_NUM;
5122 x_klnv_tbl(1).CLVL_UOM_CODE := OKC_API.G_MISS_CHAR;
5123 x_klnv_tbl(1).TOPLVL_OPERAND_CODE := OKC_API.G_MISS_CHAR;
5124 x_klnv_tbl(1).TOPLVL_OPERAND_VAL := OKC_API.G_MISS_NUM;
5125 x_klnv_tbl(1).TOPLVL_QUANTITY := OKC_API.G_MISS_NUM;
5126 x_klnv_tbl(1).TOPLVL_UOM_CODE := OKC_API.G_MISS_CHAR;
5127 x_klnv_tbl(1).TOPLVL_ADJ_PRICE := OKC_API.G_MISS_NUM;
5128 x_klnv_tbl(1).TOPLVL_PRICE_QTY := OKC_API.G_MISS_NUM;
5129 x_klnv_tbl(1).AVERAGING_INTERVAL := OKC_API.G_MISS_NUM;
5130 x_klnv_tbl(1).SETTLEMENT_INTERVAL := OKC_API.G_MISS_CHAR;
5131 x_klnv_tbl(1).MINIMUM_QUANTITY := OKC_API.G_MISS_NUM;
5132 x_klnv_tbl(1).DEFAULT_QUANTITY := OKC_API.G_MISS_NUM;
5133 x_klnv_tbl(1).AMCV_FLAG := OKC_API.G_MISS_CHAR;
5134 x_klnv_tbl(1).FIXED_QUANTITY := OKC_API.G_MISS_NUM;
5135 x_klnv_tbl(1).USAGE_DURATION := OKC_API.G_MISS_NUM;
5136 x_klnv_tbl(1).USAGE_PERIOD := OKC_API.G_MISS_CHAR;
5137 x_klnv_tbl(1).LEVEL_YN := OKC_API.G_MISS_CHAR;
5138 x_klnv_tbl(1).USAGE_TYPE := OKC_API.G_MISS_CHAR;
5139 x_klnv_tbl(1).UOM_QUANTIFIED := OKC_API.G_MISS_CHAR;
5140 x_klnv_tbl(1).BASE_READING := OKC_API.G_MISS_NUM;
5141 x_klnv_tbl(1).BILLING_SCHEDULE_TYPE := OKC_API.G_MISS_CHAR;
5142 x_klnv_tbl(1).COVERAGE_TYPE := OKC_API.G_MISS_CHAR;
5143 x_klnv_tbl(1).EXCEPTION_COV_ID := OKC_API.G_MISS_NUM;
5144 x_klnv_tbl(1).LIMIT_UOM_QUANTIFIED := OKC_API.G_MISS_CHAR;
5145 x_klnv_tbl(1).DISCOUNT_AMOUNT := OKC_API.G_MISS_NUM;
5146 x_klnv_tbl(1).DISCOUNT_PERCENT := OKC_API.G_MISS_NUM;
5147 x_klnv_tbl(1).OFFSET_DURATION := OKC_API.G_MISS_NUM;
5148 x_klnv_tbl(1).OFFSET_PERIOD := OKC_API.G_MISS_CHAR;
5149 x_klnv_tbl(1).INCIDENT_SEVERITY_ID := OKC_API.G_MISS_NUM;
5150 x_klnv_tbl(1).PDF_ID := OKC_API.G_MISS_NUM;
5151 x_klnv_tbl(1).WORK_THRU_YN := OKC_API.G_MISS_CHAR;
5152 x_klnv_tbl(1).REACT_ACTIVE_YN := OKC_API.G_MISS_CHAR;
5153 x_klnv_tbl(1).TRANSFER_OPTION := OKC_API.G_MISS_CHAR;
5154 x_klnv_tbl(1).PROD_UPGRADE_YN := OKC_API.G_MISS_CHAR;
5155 x_klnv_tbl(1).INHERITANCE_TYPE := OKC_API.G_MISS_CHAR;
5156 x_klnv_tbl(1).PM_PROGRAM_ID := OKC_API.G_MISS_NUM;
5157 x_klnv_tbl(1).PM_CONF_REQ_YN := OKC_API.G_MISS_CHAR;
5158 x_klnv_tbl(1).PM_SCH_EXISTS_YN := OKC_API.G_MISS_CHAR;
5159 x_klnv_tbl(1).ALLOW_BT_DISCOUNT := OKC_API.G_MISS_CHAR;
5160 x_klnv_tbl(1).APPLY_DEFAULT_TIMEZONE := OKC_API.G_MISS_CHAR;
5161 x_klnv_tbl(1).SYNC_DATE_INSTALL := OKC_API.G_MISS_CHAR;
5162 x_klnv_tbl(1).SFWT_FLAG := OKC_API.G_MISS_CHAR;
5163 x_klnv_tbl(1).OBJECT_VERSION_NUMBER := OKC_API.G_MISS_NUM;
5164 x_klnv_tbl(1).SECURITY_GROUP_ID := OKC_API.G_MISS_NUM;
5165 x_klnv_tbl(1).REQUEST_ID := OKC_API.G_MISS_NUM;
5166 x_klnv_tbl(1).CREATED_BY := OKC_API.G_MISS_NUM;
5167 x_klnv_tbl(1).CREATION_DATE := OKC_API.G_MISS_DATE;
5168 x_klnv_tbl(1).LAST_UPDATED_BY := OKC_API.G_MISS_NUM;
5169 x_klnv_tbl(1).LAST_UPDATE_DATE := OKC_API.G_MISS_DATE;
5170 x_klnv_tbl(1).LAST_UPDATE_LOGIN := OKC_API.G_MISS_NUM;
5171 END init_oks_k_line;
5172
5173
5174 PROCEDURE Version_PM(
5175 p_api_version IN NUMBER,
5176 p_init_msg_list IN VARCHAR2,
5177 x_return_status OUT NOCOPY VARCHAR2,
5178 x_msg_count OUT NOCOPY NUMBER,
5179 x_msg_data OUT NOCOPY VARCHAR2,
5180 p_chr_id IN NUMBER,
5181 p_major_version IN NUMBER) IS
5182
5183 l_chr_id CONSTANT NUMBER := p_chr_id;
5184 l_major_version CONSTANT NUMBER := p_major_version;
5185 l_return_Status VARCHAR2(1);
5186
5187 BEGIN
5188
5189
5190 l_return_Status := OKS_PMA_PVT.Create_Version(
5191 p_id => l_chr_id,
5192 p_major_version =>l_major_version);
5193
5194 l_return_Status := OKS_PML_PVT.Create_Version(
5195 p_id => l_chr_id,
5196 p_major_version =>l_major_version);
5197
5198 l_return_Status := OKS_PMS_PVT.Create_Version(
5199 p_id => l_chr_id,
5200 p_major_version =>l_major_version);
5201
5202 x_return_status := OKC_API.G_RET_STS_SUCCESS;
5203
5204 EXCEPTION
5205
5206 WHEN OTHERS THEN
5207
5208 OKC_API.SET_MESSAGE(
5209 p_app_name => g_app_name,
5210 p_msg_name => g_unexpected_error,
5211 p_token1 => g_sqlcode_token,
5212 p_token1_value => sqlcode,
5213 p_token2 => g_sqlerrm_token,
5214 p_token2_value => sqlerrm);
5215
5216 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5217
5218 END Version_PM;
5219
5220
5221 PROCEDURE Restore_PM(
5222 p_api_version IN NUMBER,
5223 p_init_msg_list IN VARCHAR2,
5224 x_return_status OUT NOCOPY VARCHAR2,
5225 x_msg_count OUT NOCOPY NUMBER,
5226 x_msg_data OUT NOCOPY VARCHAR2,
5227 p_chr_id IN NUMBER)
5228 IS --take out p_major_version from here from pub/pvt spec and body
5229
5230 l_chr_id CONSTANT NUMBER := p_chr_id;
5231 l_major_version CONSTANT NUMBER := -1; --p_major_version;
5232 l_return_Status VARCHAR2(1);
5233
5234 BEGIN
5235
5236
5237 l_return_Status := OKS_PMA_PVT.Restore_Version(
5238 p_id => l_chr_id,
5239 p_major_version =>l_major_version);
5240
5241 l_return_Status := OKS_PML_PVT.Restore_Version(
5242 p_id => l_chr_id,
5243 p_major_version =>l_major_version);
5244
5245 l_return_Status := OKS_PMS_PVT.Restore_Version(
5246 p_id => l_chr_id,
5247 p_major_version =>l_major_version);
5248
5249 x_return_status := OKC_API.G_RET_STS_SUCCESS;
5250
5251 EXCEPTION
5252
5253 WHEN OTHERS THEN
5254
5255 OKC_API.SET_MESSAGE(
5256 p_app_name => g_app_name,
5257 p_msg_name => g_unexpected_error,
5258 p_token1 => g_sqlcode_token,
5259 p_token1_value => sqlcode,
5260 p_token2 => g_sqlerrm_token,
5261 p_token2_value => sqlerrm);
5262
5263 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5264
5265 END Restore_PM;
5266
5267
5268
5269 PROCEDURE Delete_PMHistory(
5270 p_api_version IN NUMBER,
5271 p_init_msg_list IN VARCHAR2,
5272 x_return_status OUT NOCOPY VARCHAR2,
5273 x_msg_count OUT NOCOPY NUMBER,
5274 x_msg_data OUT NOCOPY VARCHAR2,
5275 p_chr_id IN NUMBER) IS
5276
5277 l_chr_id CONSTANT NUMBER := p_chr_id;
5278 l_return_Status VARCHAR2(1);
5279
5280 BEGIN
5281
5282 DELETE OKS_PM_SCHEDULES
5283 WHERE dnz_chr_id = l_chr_id;
5284
5285 DELETE OKS_PM_STREAM_LEVELS
5286 WHERE dnz_chr_id = l_chr_id;
5287
5288 DELETE OKS_PM_ACTIVITIES
5289 WHERE dnz_chr_id = l_chr_id;
5290
5291 x_return_status := OKC_API.G_RET_STS_SUCCESS;
5292
5293 EXCEPTION
5294
5295 WHEN OTHERS THEN
5296
5297 OKC_API.SET_MESSAGE(
5298 p_app_name => g_app_name,
5299 p_msg_name => g_unexpected_error,
5300 p_token1 => g_sqlcode_token,
5301 p_token1_value => sqlcode,
5302 p_token2 => g_sqlerrm_token,
5303 p_token2_value => sqlerrm);
5304
5305 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5306
5307 END Delete_PMHistory;
5308
5309 PROCEDURE Delete_PMSaved_Version(
5310 p_api_version IN NUMBER,
5311 p_init_msg_list IN VARCHAR2,
5312 x_return_status OUT NOCOPY VARCHAR2,
5313 x_msg_count OUT NOCOPY NUMBER,
5314 x_msg_data OUT NOCOPY VARCHAR2,
5315 p_chr_id IN NUMBER) IS
5316
5317 l_api_version NUMBER := 1;
5318 l_init_msg_list VARCHAR2(1) DEFAULT OKC_API.G_FALSE;
5319 l_return_status VARCHAR2(1);
5320 l_return_msg VARCHAR2(2000);
5321 l_msg_count NUMBER;
5322 l_msg_data VARCHAR2(2000);
5323 l_api_name VARCHAR2(30):= 'Delete_Saved_Version';
5324 l_chr_id CONSTANT NUMBER := p_chr_id;
5325
5326 BEGIN
5327
5328 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5329 p_init_msg_list,
5330 '_PUB',
5331 x_return_status);
5332
5333 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
5334 THEN
5335 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5336 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
5337 THEN
5338 raise OKC_API.G_EXCEPTION_ERROR;
5339 ELSIF l_return_status IS NULL
5340 THEN
5341 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5342 END IF;
5343
5344
5345 DELETE OKS_PM_SCHEDULES_H
5346 WHERE dnz_chr_id = l_chr_id
5347 And major_version = -1;
5348
5349 DELETE OKS_PM_STREAM_LEVELS_H
5350 WHERE dnz_chr_id = l_chr_id
5351 And major_version = -1;
5352
5353 DELETE OKS_PM_ACTIVITIES_H
5354 WHERE dnz_chr_id = l_chr_id
5355 And major_version = -1;
5356
5357 x_Return_status:=l_Return_Status;
5358
5359 EXCEPTION
5360 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5361 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5362 (l_api_name,
5363 G_PKG_NAME,
5364 'OKC_API.G_RET_STS_ERROR',
5365 x_msg_count,
5366 x_msg_data,
5367 '_PUB');
5368 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5369 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5370 (l_api_name,
5371 G_PKG_NAME,
5372 'OKC_API.G_RET_STS_UNEXP_ERROR',
5373 x_msg_count,
5374 x_msg_data,
5375 '_PUB');
5376 WHEN OTHERS THEN
5377 OKC_API.set_message(p_app_name => g_app_name,
5378 p_msg_name => g_unexpected_error,
5379 p_token1 => g_sqlcode_token,
5380 p_token1_value => sqlcode,
5381 p_token2 => g_sqlerrm_token,
5382 p_token2_value => sqlerrm);
5383 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5384 ROLLBACK ;
5385 END Delete_PMSaved_Version;
5386
5387 /* New procedure for copying PM for coverage template. This procedure is called by
5388 oks_coverages_pvt.copy_coverage while copying the standard coverage template. */
5389
5390 PROCEDURE Copy_PM_Template (
5391 p_api_version IN NUMBER,
5392 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5393 x_return_status OUT NOCOPY VARCHAR2,
5394 x_msg_count OUT NOCOPY NUMBER,
5395 x_msg_data OUT NOCOPY VARCHAR2,
5396 p_old_coverage_id IN NUMBER,
5397 p_new_coverage_id IN NUMBER) IS --instantiated cle id
5398
5399 ---------------------------------------------------------
5400
5401 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5402 l_old_cle_id NUMBER;
5403 l_new_cle_id NUMBER;
5404
5405 -- This function is to insert values into oks_pm_activities
5406 FUNCTION CREATE_OKS_PM_ACTIVITIES(p_new_cle_id NUMBER,
5407 p_old_cle_id NUMBER) return VARCHAR2 IS
5408
5409 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5410
5411 BEGIN
5412
5413 INSERT INTO oks_pm_activities
5414 ( ID,
5415 CLE_ID,
5416 DNZ_CHR_ID,
5417 ACTIVITY_ID,
5418 SELECT_YN,
5419 CONF_REQ_YN,
5420 SCH_EXISTS_YN,
5421 PROGRAM_APPLICATION_ID,
5422 PROGRAM_ID,
5423 PROGRAM_UPDATE_DATE,
5424 OBJECT_VERSION_NUMBER,
5425 SECURITY_GROUP_ID,
5426 REQUEST_ID,
5427 CREATED_BY,
5428 CREATION_DATE,
5429 LAST_UPDATED_BY,
5430 LAST_UPDATE_DATE,
5431 LAST_UPDATE_LOGIN,
5432 -- SERVICE_LINE_ID,
5433 ORIG_SYSTEM_ID1,
5434 ORIG_SYSTEM_SOURCE_CODE,
5435 ORIG_SYSTEM_REFERENCE1 )
5436 SELECT
5437 okc_p_util.raw_to_number(sys_guid()),
5438 p_new_cle_id CLE_ID,
5439 DNZ_CHR_ID,
5440 ACTIVITY_ID,
5441 SELECT_YN,
5442 CONF_REQ_YN,
5443 SCH_EXISTS_YN,
5444 PROGRAM_APPLICATION_ID,
5445 PROGRAM_ID,
5446 PROGRAM_UPDATE_DATE,
5447 OBJECT_VERSION_NUMBER,
5448 SECURITY_GROUP_ID,
5449 REQUEST_ID,
5450 FND_GLOBAL.USER_ID CREATED_BY,
5451 SYSDATE CREATION_DATE,
5452 FND_GLOBAL.USER_ID LAST_UPDATED_BY,
5453 SYSDATE LAST_UPDATE_DATE,
5454 FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN,
5455 -- SERVICE_LINE_ID,
5456 ID ORIG_SYSTEM_ID1,
5457 ORIG_SYSTEM_SOURCE_CODE,
5458 ORIG_SYSTEM_REFERENCE1
5459 FROM oks_pm_activities
5460 WHERE cle_id =p_old_cle_id;
5461
5462 RETURN l_return_status;
5463
5464 EXCEPTION
5465 WHEN others THEN
5466 OKC_API.set_message(G_APP_NAME,
5467 G_UNEXPECTED_ERROR,
5468 G_SQLCODE_TOKEN,
5469 SQLCODE,
5470 G_SQLERRM_TOKEN,
5471 SQLERRM);
5472 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5473 return x_return_status;
5474
5475 END CREATE_OKS_PM_ACTIVITIES;
5476
5477 -- This function is to insert values into oks_pm_stream_levels
5478 FUNCTION CREATE_OKS_PM_STREAM_LEVELS(p_new_cle_id NUMBER,
5479 p_old_cle_id NUMBER) return VARCHAR2 IS
5480
5481 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5482
5483 BEGIN
5484
5485 INSERT INTO oks_pm_stream_levels
5486 ( ID,
5487 CLE_ID,
5488 DNZ_CHR_ID,
5489 ACTIVITY_LINE_ID,
5490 SEQUENCE_NUMBER,
5491 NUMBER_OF_OCCURENCES,
5492 START_DATE,
5493 END_DATE,
5494 FREQUENCY,
5495 FREQUENCY_UOM,
5496 OFFSET_DURATION,
5497 OFFSET_UOM,
5498 AUTOSCHEDULE_YN,
5499 PROGRAM_APPLICATION_ID,
5500 PROGRAM_ID,
5501 PROGRAM_UPDATE_DATE,
5502 OBJECT_VERSION_NUMBER,
5503 SECURITY_GROUP_ID,
5504 REQUEST_ID,
5505 CREATED_BY,
5506 CREATION_DATE,
5507 LAST_UPDATED_BY,
5508 LAST_UPDATE_DATE,
5509 LAST_UPDATE_LOGIN,
5510 -- TOLERANCE_BEFORE,
5511 -- TOLERANCE_AFTER,
5512 -- SERVICE_LINE_ID,
5513 ORIG_SYSTEM_ID1,
5514 ORIG_SYSTEM_SOURCE_CODE,
5515 ORIG_SYSTEM_REFERENCE1 )
5516 SELECT
5517 okc_p_util.raw_to_number(sys_guid()),
5518 p_new_cle_id CLE_ID,
5519 DNZ_CHR_ID,
5520 DECODE(ACTIVITY_LINE_ID,NULL,NULL,(SELECT id from oks_pm_activities where ORIG_SYSTEM_ID1 =ACTIVITY_LINE_ID and cle_id =p_new_cle_id)),
5521 SEQUENCE_NUMBER,
5522 NUMBER_OF_OCCURENCES,
5523 START_DATE,
5524 END_DATE,
5525 FREQUENCY,
5526 FREQUENCY_UOM,
5527 OFFSET_DURATION,
5528 OFFSET_UOM,
5529 AUTOSCHEDULE_YN,
5530 PROGRAM_APPLICATION_ID,
5531 PROGRAM_ID,
5532 PROGRAM_UPDATE_DATE,
5533 OBJECT_VERSION_NUMBER,
5534 SECURITY_GROUP_ID,
5535 REQUEST_ID,
5536 FND_GLOBAL.USER_ID CREATED_BY,
5537 SYSDATE CREATION_DATE,
5538 FND_GLOBAL.USER_ID LAST_UPDATED_BY,
5539 SYSDATE LAST_UPDATE_DATE,
5540 FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN,
5541 -- TOLERANCE_BEFORE,
5542 -- TOLERANCE_AFTER,
5543 -- SERVICE_LINE_ID,
5544 ID ORIG_SYSTEM_ID1,
5545 ORIG_SYSTEM_SOURCE_CODE,
5546 ORIG_SYSTEM_REFERENCE1
5547 FROM oks_pm_stream_levels
5548 WHERE cle_id=p_old_cle_id;
5549 RETURN l_return_status;
5550
5551 EXCEPTION
5552 WHEN others THEN
5553 OKC_API.set_message(G_APP_NAME,
5554 G_UNEXPECTED_ERROR,
5555 G_SQLCODE_TOKEN,
5556 SQLCODE,
5557 G_SQLERRM_TOKEN,
5558 SQLERRM);
5559 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5560 return x_return_status;
5561
5562 END CREATE_OKS_PM_STREAM_LEVELS;
5563
5564
5565 BEGIN
5566
5567 IF (G_DEBUG_ENABLED = 'Y') THEN
5568 okc_debug.Set_Indentation('Copy_PM_Template');
5569 okc_debug.log('Entered Copy_PM_Template', 3);
5570 END IF;
5571 l_return_status := CREATE_OKS_PM_ACTIVITIES(p_new_coverage_id,p_old_coverage_id);
5572 IF (G_DEBUG_ENABLED = 'Y') THEN
5573 okc_debug.log('After CREATE_OKS_PM_ACTIVITIES'||l_return_status, 3);
5574 END IF;
5575 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS
5576 THEN
5577 RAISE G_EXCEPTION_HALT_VALIDATION;
5578 END IF;
5579 l_return_status := CREATE_OKS_PM_STREAM_LEVELS(p_new_coverage_id,p_old_coverage_id);
5580 IF (G_DEBUG_ENABLED = 'Y') THEN
5581 okc_debug.log('After CREATE_OKS_PM_STREAM_LEVELS'||l_return_status, 3);
5582 END IF;
5583
5584 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS
5585 THEN
5586 RAISE G_EXCEPTION_HALT_VALIDATION;
5587 END IF;
5588
5589 x_return_status := l_return_status;
5590
5591 EXCEPTION
5592 WHEN G_EXCEPTION_HALT_VALIDATION THEN
5593 x_return_status := l_return_status ;
5594 WHEN others THEN
5595 OKC_API.set_message(G_APP_NAME,
5596 G_UNEXPECTED_ERROR,
5597 G_SQLCODE_TOKEN,
5598 SQLCODE,
5599 G_SQLERRM_TOKEN,
5600 SQLERRM);
5601 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5602
5603 END Copy_PM_Template;
5604
5605
5606
5607 END OKS_PM_PROGRAMS_PVT;