[Home] [Help]
PACKAGE BODY: APPS.OKS_COVERAGES_PUB
Source
1 PACKAGE BODY OKS_COVERAGES_PUB AS
2 /* $Header: OKSPMCVB.pls 120.0 2005/05/25 18:12:24 appldev noship $*/
3 PROCEDURE CREATE_ACTUAL_COVERAGE(
4 p_api_version IN NUMBER,
5 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
6 x_return_status OUT NOCOPY VARCHAR2,
7 x_msg_count OUT NOCOPY NUMBER,
8 x_msg_data OUT NOCOPY VARCHAR2,
9 P_ac_rec_in IN ac_rec_TYPE,
10 p_restricted_update IN VARCHAR2 DEFAULT 'F',
11 x_Actual_coverage_id OUT NOCOPY NUMBER) IS
12
13 l_ac_rec_in AC_REC_TYPE;
14 l_api_name CONSTANT VARCHAR2(30) := 'create_actual_coverage';
15 l_return_status VARCHAR2(1);
16 BEGIN
17 l_ac_rec_in := p_ac_rec_in;
18 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
19 p_init_msg_list,
20 '_PUB',
21 x_return_status);
22 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
23 THEN
24 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
25 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
26 THEN
27 raise OKC_API.G_EXCEPTION_ERROR;
28 ELSIF l_return_status IS NULL
29 THEN
30 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
31 END IF;
32 -- Call to Complex API procedure
33 oks_coverages_pvt.create_actual_coverage(
34 p_api_version,
35 p_init_msg_list,
36 l_return_status,
37 x_msg_count,
38 x_msg_data,
39 l_ac_rec_in,
40 p_restricted_update,
41 x_actual_coverage_Id);
42 --dbms_output.put_line('in cov pvt'||l_return_status);
43 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
44 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
45 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
46 raise OKC_API.G_EXCEPTION_ERROR;
47 ELSIF l_return_status IS NULL
48 THEN
49 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
50 END IF;
51 x_Return_status:=l_Return_Status;
52 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
53 EXCEPTION
54 WHEN OKC_API.G_EXCEPTION_ERROR THEN
55 x_return_status := OKC_API.HANDLE_EXCEPTIONS
56 (l_api_name,
57 G_PKG_NAME,
58 'OKC_API.G_RET_STS_ERROR',
59 x_msg_count,
60 x_msg_data,
61 '_PUB');
62 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
63 x_return_status := OKC_API.HANDLE_EXCEPTIONS
64 (l_api_name,
65 G_PKG_NAME,
66 'OKC_API.G_RET_STS_UNEXP_ERROR',
67 x_msg_count,
68 x_msg_data,
69 '_PUB');
70 WHEN G_EXCEPTION_HALT_VALIDATION THEN
71 NULL;
72 WHEN OTHERS THEN
73 OKC_API.set_message(p_app_name => g_app_name,
74 p_msg_name => g_unexpected_error,
75 p_token1 => g_sqlcode_token,
76 p_token1_value => sqlcode,
77 p_token2 => g_sqlerrm_token,
78 p_token2_value => sqlerrm);
79 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
80 END create_actual_coverage;
81 PROCEDURE Undo_Header(
82 p_api_version IN NUMBER,
83 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
84 x_return_status OUT NOCOPY VARCHAR2,
85 x_msg_count OUT NOCOPY NUMBER,
86 x_msg_data OUT NOCOPY VARCHAR2,
87 P_Header_id IN NUMBER) IS
88 l_api_name CONSTANT VARCHAR2(30) := 'Undo_Header';
89 l_return_status VARCHAR2(1);
90
91 BEGIN
92 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
93 p_init_msg_list,
94 '_PUB',
95 x_return_status);
96 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
97 THEN
98 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
99 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
100 THEN
101 raise OKC_API.G_EXCEPTION_ERROR;
102 ELSIF l_return_status IS NULL
103 THEN
104 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
105 END IF;
106
107 -- Call to Complex API procedure
108 oks_coverages_pvt.Undo_Header(
109 p_api_version,
110 p_init_msg_list,
111 l_return_status,
112 x_msg_count,
113 x_msg_data,
114 P_Header_id);
115 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
116 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
117 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
118 raise OKC_API.G_EXCEPTION_ERROR;
119 ELSIF l_return_status IS NULL
120 THEN
121 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
122
123 END IF;
124 x_Return_status:=l_Return_Status;
125 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
126 EXCEPTION
127 WHEN OKC_API.G_EXCEPTION_ERROR THEN
128 x_return_status := OKC_API.HANDLE_EXCEPTIONS
129 (l_api_name,
130 G_PKG_NAME,
131 'OKC_API.G_RET_STS_ERROR',
132 x_msg_count,
133 x_msg_data,
134 '_PUB');
135 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
136 x_return_status := OKC_API.HANDLE_EXCEPTIONS
137 (l_api_name,
138 G_PKG_NAME,
139 'OKC_API.G_RET_STS_UNEXP_ERROR',
140 x_msg_count,
141 x_msg_data,
142 '_PUB');
143 WHEN G_EXCEPTION_HALT_VALIDATION THEN
144 NULL;
145 WHEN OTHERS THEN
146 OKC_API.set_message(p_app_name => g_app_name,
147 p_msg_name => g_unexpected_error,
148 p_token1 => g_sqlcode_token,
149 p_token1_value => sqlcode,
150 p_token2 => g_sqlerrm_token,
151 p_token2_value => sqlerrm);
152 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
153 END Undo_Header;
154 PROCEDURE Undo_Line(
155 p_api_version IN NUMBER,
156 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
157 x_return_status OUT NOCOPY VARCHAR2,
158 x_msg_count OUT NOCOPY NUMBER,
159 x_msg_data OUT NOCOPY VARCHAR2,
160 P_Line_Id IN NUMBER) Is
161 l_api_name CONSTANT VARCHAR2(30) := 'Undo_Line';
162 l_return_status VARCHAR2(1);
163 BEGIN
164 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
165 p_init_msg_list,
166 '_PUB',
167 x_return_status);
168 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
169 THEN
170 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
171 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
172 THEN
173 raise OKC_API.G_EXCEPTION_ERROR;
174 ELSIF l_return_status IS NULL
175 THEN
176 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
177 END IF;
178 /*
179 -- Call to Complex API procedure
180 oks_coverages_pvt.Undo_Line(
181 p_api_version,
182 p_init_msg_list,
183 l_return_status,
184 x_msg_count,
185 x_msg_data,
186 P_Line_id);
187 */
188
189 --Added
190 OKS_SETUP_UTIL_PUB.Delete_Contract_Line(
191 p_api_version ,
192 p_init_msg_list ,
193 p_line_id ,
194 x_return_status,
195 x_msg_count ,
196 x_msg_data );
197
198
199
200
201
202 l_Return_status:=x_Return_Status;
203 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
204 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
205 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
206 raise OKC_API.G_EXCEPTION_ERROR;
207 ELSIF l_return_status IS NULL
208 THEN
209 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
210 END IF;
211 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
212 EXCEPTION
213 WHEN OKC_API.G_EXCEPTION_ERROR THEN
214 x_return_status := OKC_API.HANDLE_EXCEPTIONS
215 (l_api_name,
216 G_PKG_NAME,
217 'OKC_API.G_RET_STS_ERROR',
218 x_msg_count,
219 x_msg_data,
220 '_PUB');
221 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
222 x_return_status := OKC_API.HANDLE_EXCEPTIONS
223 (l_api_name,
224 G_PKG_NAME,
225 'OKC_API.G_RET_STS_UNEXP_ERROR',
226 x_msg_count,
227 x_msg_data,
228 '_PUB');
229
230 WHEN G_EXCEPTION_HALT_VALIDATION THEN
231 NULL;
232 WHEN OTHERS THEN
233 OKC_API.set_message(p_app_name => g_app_name,
234 p_msg_name => g_unexpected_error,
235 p_token1 => g_sqlcode_token,
236 p_token1_value => sqlcode,
237 p_token2 => g_sqlerrm_token,
238 p_token2_value => sqlerrm);
239 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
240 END Undo_Line;
241
242
243 PROCEDURE Update_cov_eff(
244 p_api_version IN NUMBER,
245 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
246 x_return_status OUT NOCOPY VARCHAR2,
247 x_msg_count OUT NOCOPY NUMBER,
248 x_msg_data OUT NOCOPY VARCHAR2,
249 P_service_Line_Id IN NUMBER,
250 p_new_start_date IN DATE,
251 p_new_end_date IN DATE)
252 IS
253
254 l_api_name CONSTANT VARCHAR2(30) := 'Update_cov_eff';
255 l_return_status VARCHAR2(1);
256 BEGIN
257 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
258 p_init_msg_list,
259 '_PUB',
260 x_return_status);
261 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
262 THEN
263 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
264 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
265 THEN
266 raise OKC_API.G_EXCEPTION_ERROR;
267 ELSIF l_return_status IS NULL
268 THEN
269 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
270 END IF;
271 -- dbms_output.put_line('Before calling update_cov Value of l_return_status='||l_return_status);
272 -- Call to Complex API procedure
273 oks_coverages_pvt.Update_coverage_effectivity(
274 p_api_version,
275 p_init_msg_list,
276 l_return_status,
277 x_msg_count,
278 x_msg_data,
279 P_service_Line_id,
280 p_new_start_date,
281 p_new_end_date);
282 -- dbms_output.put_line('After calling update_cov Value of l_return_status='||l_return_status);
283
284 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
285 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
286 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
287 raise OKC_API.G_EXCEPTION_ERROR;
288 ELSIF l_return_status IS NULL
289 THEN
290 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
291 END IF;
292 X_Return_status:=l_Return_Status;
293 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
294
295 EXCEPTION
296 WHEN OKC_API.G_EXCEPTION_ERROR THEN
297 x_return_status := OKC_API.HANDLE_EXCEPTIONS
298 (l_api_name,
299 G_PKG_NAME,
300 'OKC_API.G_RET_STS_ERROR',
301 x_msg_count,
302 x_msg_data,
303 '_PUB');
304 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
305 x_return_status := OKC_API.HANDLE_EXCEPTIONS
306 (l_api_name,
307 G_PKG_NAME,
308 'OKC_API.G_RET_STS_UNEXP_ERROR',
309 x_msg_count,
310 x_msg_data,
311 '_PUB');
312
313 WHEN G_EXCEPTION_HALT_VALIDATION THEN
314 NULL;
315 WHEN OTHERS THEN
316 OKC_API.set_message(p_app_name => g_app_name,
317 p_msg_name => g_unexpected_error,
318 p_token1 => g_sqlcode_token,
319 p_token1_value => sqlcode,
320 p_token2 => g_sqlerrm_token,
321 p_token2_value => sqlerrm);
322 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
323 END Update_cov_eff;
324
325
326 PROCEDURE INSTANTIATE_COVERAGE(
327 p_api_version IN NUMBER,
328 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
329 x_return_status OUT NOCOPY VARCHAR2,
330 x_msg_count OUT NOCOPY NUMBER,
331 x_msg_data OUT NOCOPY VARCHAR2,
332 P_service_Line_Id IN NUMBER,
333 x_actual_coverage_id OUT NOCOPY NUMBER) IS
334
335 l_api_version CONSTANT NUMBER := 1.0;
336 l_init_msg_list CONSTANT VARCHAR2(1) := 'T';
337 l_return_status VARCHAR2(3);
338 l_return_msg VARCHAR2(2000);
339 l_msg_count NUMBER;
340 l_msg_data VARCHAR2(2000);
341 l_msg_index_out Number;
342 l_commit Varchar2(2000):='F';
343 l_ac_rec_type oks_coverages_pub.ac_rec_type;
344 l_api_name CONSTANT VARCHAR2(30) := 'Instantiate_Coverage';
345
346 CURSOR Cur_covtmpl_id(p_cle_id IN NUMBER) Is
347 SELECT oxs.Coverage_template_Id,
348 cle1.start_date,
349 cle1.end_date
350 FROM OKC_K_LINES_B cle1,
351 OKC_K_ITEMS cim ,
352 OKX_SYSTEM_ITEMS_V oxs
353 WHERE cle1.Lse_Id IN (1,19,14)
354 AND cim.cle_Id=cle1.Id
355 AND oxs.Id1=cim.object1_Id1
356 AND oxs.Id2=cim.object1_id2
357 AND cle1.Id = p_cle_id;
358
359 BEGIN
360
361 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
362 p_init_msg_list,
363 '_PUB',
364 x_return_status);
365
366 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
367 THEN
368 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
369 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
370 THEN
371 raise OKC_API.G_EXCEPTION_ERROR;
372 ELSIF l_return_status IS NULL
373 THEN
374 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
375 END IF;
376
377 l_ac_rec_type.Svc_cle_Id := P_service_line_Id;
378
379 -- getting coverage temp id
380
381 FOR Cur_covtmpl_rec in Cur_covtmpl_id(P_service_line_Id) LOOP
382
383 l_ac_rec_type.Tmp_cle_Id := Cur_covtmpl_rec.Coverage_template_Id;
384 l_ac_rec_type.Start_date := Cur_covtmpl_rec.start_date;
385 l_ac_rec_type.End_Date := Cur_covtmpl_rec.end_date;
386
387 END LOOP;
388
389 OKS_COVERAGES_PUB.CREATE_ACTUAL_COVERAGE(
390 p_api_version => l_api_version,
391 p_init_msg_list => l_init_msg_list,
392 x_return_status => l_return_status,
393 x_msg_count => l_msg_count,
394 x_msg_data => l_msg_data,
395 P_ac_rec_in => l_ac_rec_type,
396 p_restricted_update => 'F',
397 x_Actual_coverage_id => x_Actual_coverage_id);
398
399 --dbms_output.put_line('Value of l_return_status='||l_return_status);
400
401 /* IF l_msg_count > 0
402 THEN
403 FOR i in 1..l_msg_count
404 LOOP
405 fnd_msg_pub.get (p_msg_index => -1,
406 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
407 p_data => l_msg_data,
408 p_msg_index_out => l_msg_index_out);
409
410 --dbms_output.put_line('Value of l_msg_data='||l_msg_data);
411
412 END LOOP;
413 END IF;
414 */
415 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
416 THEN
417 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
418 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
419 THEN
420 raise OKC_API.G_EXCEPTION_ERROR;
421 ELSIF l_return_status IS NULL
422 THEN
423 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
424 END IF;
425
426 --dbms_output.put_line('Value of x_Actual_coverage_id='||TO_CHAR(x_Actual_coverage_id));
427
428 EXCEPTION
429 WHEN OKC_API.G_EXCEPTION_ERROR THEN
430 x_return_status := OKC_API.HANDLE_EXCEPTIONS
431 (l_api_name,
432 G_PKG_NAME,
433 'OKC_API.G_RET_STS_ERROR',
434 x_msg_count,
435 x_msg_data,
436 '_PUB');
437 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
438 x_return_status := OKC_API.HANDLE_EXCEPTIONS
439 (l_api_name,
440 G_PKG_NAME,
441 'OKC_API.G_RET_STS_UNEXP_ERROR',
442 x_msg_count,
443 x_msg_data,
444 '_PUB');
445
446 WHEN G_EXCEPTION_HALT_VALIDATION THEN
447 NULL;
448 WHEN OTHERS THEN
449 OKC_API.set_message(p_app_name => g_app_name,
450 p_msg_name => g_unexpected_error,
451 p_token1 => g_sqlcode_token,
452 p_token1_value => sqlcode,
453 p_token2 => g_sqlerrm_token,
454 p_token2_value => sqlerrm);
455 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
456
457
458 END INSTANTIATE_COVERAGE;
459
460
461 PROCEDURE DELETE_COVERAGE(
462 p_api_version IN NUMBER,
463 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
464 x_return_status OUT NOCOPY VARCHAR2,
465 x_msg_count OUT NOCOPY NUMBER,
466 x_msg_data OUT NOCOPY VARCHAR2,
467 P_service_Line_Id IN NUMBER) IS
468
469 l_api_version CONSTANT NUMBER := 1.0;
470 l_init_msg_list CONSTANT VARCHAR2(1) := 'T';
471 l_return_status VARCHAR2(3);
472 l_return_msg VARCHAR2(2000);
473 l_msg_count NUMBER;
474 l_msg_data VARCHAR2(2000);
475 l_msg_index_out Number;
476 l_commit Varchar2(2000):='F';
477 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Coverage';
478
479 CURSOR Cur_cov_Id IS
480 SELECT id
481 FROM OKC_K_LINES_B
482 WHERE CLE_ID = P_service_Line_Id
483 AND LSE_ID in ( 2,15,20);
484
485 BEGIN
486
487 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
488 p_init_msg_list,
489 '_PUB',
490 x_return_status);
491 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
492 THEN
493 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
494 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
495 THEN
496 raise OKC_API.G_EXCEPTION_ERROR;
497 ELSIF l_return_status IS NULL
498 THEN
499 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
500 END IF;
501
502 FOR Cov_Id_rec IN Cur_cov_Id LOOP
503 /*
504 OKS_COVERAGES_PUB.Undo_Line(
505 p_api_version => l_api_version,
506 p_init_msg_list => l_init_msg_list,
507 x_return_status => l_return_status,
508 x_msg_count => l_msg_count,
509 x_msg_data => l_msg_data,
510 P_Line_Id => Cov_Id_rec.Id);
511
512 --dbms_output.put_line('status:'||l_return_status);
513 */
514
515 /* Valiate Status added */
516
517
518
519 /*OKS_COVERAGES_PVT.Undo_Line(
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_validate_status => 'N',
526 P_Line_Id => Cov_Id_rec.Id);*/
527
528 --03/16/04 chkrishn modified to call api with no validate status
529 OKS_COVERAGES_PVT.Undo_Line(l_api_version, l_init_msg_list,l_return_status,l_msg_count,l_msg_data,Cov_Id_rec.Id);
530
531
532 /*
533 IF l_msg_count > 0
534 THEN
535 FOR i in 1..l_msg_count
536 LOOP
537 fnd_msg_pub.get (p_msg_index => -1,
538 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
539 p_data => l_msg_data,
540 p_msg_index_out => l_msg_index_out);
541
542 --dbms_output.put_line('Value of l_msg_data='||l_msg_data);
543
544 END LOOP;
545 END IF;
546 */
547 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
548 THEN
549 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
550 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
551 THEN
552 raise OKC_API.G_EXCEPTION_ERROR;
553 ELSIF l_return_status IS NULL
554 THEN
555 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
556 END IF;
557
558
559 END LOOP;
560
561 EXCEPTION
562 WHEN OKC_API.G_EXCEPTION_ERROR THEN
563 x_return_status := OKC_API.HANDLE_EXCEPTIONS
564 (l_api_name,
565 G_PKG_NAME,
566 'OKC_API.G_RET_STS_ERROR',
567 x_msg_count,
568 x_msg_data,
569 '_PUB');
570 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
571 x_return_status := OKC_API.HANDLE_EXCEPTIONS
572 (l_api_name,
573 G_PKG_NAME,
574 'OKC_API.G_RET_STS_UNEXP_ERROR',
575 x_msg_count,
576 x_msg_data,
577 '_PUB');
578 WHEN G_EXCEPTION_HALT_VALIDATION THEN
579 NULL;
580 WHEN OTHERS THEN
581 OKC_API.set_message(p_app_name => g_app_name,
582 p_msg_name => g_unexpected_error,
583 p_token1 => g_sqlcode_token,
584 p_token1_value => sqlcode,
585 p_token2 => g_sqlerrm_token,
586 p_token2_value => sqlerrm);
587 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
588
589
590 END DELETE_COVERAGE;
591
592
593 Procedure CHECK_COVERAGE_MATCH
594 ( p_api_version IN NUMBER,
595 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
596 x_return_status OUT NOCOPY VARCHAR2,
597 x_msg_count OUT NOCOPY NUMBER,
598 x_msg_data OUT NOCOPY VARCHAR2,
599 P_Source_contract_Line_Id IN NUMBER,
600 P_Target_contract_Line_Id IN NUMBER,
601 x_coverage_match OUT NOCOPY VARCHAR2) IS
602
603 l_api_version CONSTANT NUMBER := 1.0;
604 l_init_msg_list CONSTANT VARCHAR2(1) := 'T';
605 l_return_status VARCHAR2(3);
606 l_return_msg VARCHAR2(2000);
607 l_msg_count NUMBER;
608 l_msg_data VARCHAR2(2000);
609 l_msg_index_out Number;
610 l_commit Varchar2(2000):='F';
611 --l_api_name CONSTANT VARCHAR2(30) := 'Delete_Coverage';
612 l_api_name CONSTANT VARCHAR2(30) := 'Check_Coverage_Match';
613
614 BEGIN
615
616 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
617 p_init_msg_list,
618 '_PUB',
619 x_return_status);
620 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
621 THEN
622 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
623 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
624 THEN
625 raise OKC_API.G_EXCEPTION_ERROR;
626 ELSIF l_return_status IS NULL
627 THEN
628 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
629 END IF;
630 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
631 THEN
632 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
633 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
634 THEN
635 raise OKC_API.G_EXCEPTION_ERROR;
636 ELSIF l_return_status IS NULL
637 THEN
638 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
639 END IF;
640
641 OKS_COVERAGES_PVT.CHECK_COVERAGE_MATCH
642 (p_api_version => l_api_version,
643 p_init_msg_list => l_init_msg_list,
644 x_return_status => l_return_status,
645 x_msg_count => l_msg_count,
646 x_msg_data => l_msg_data,
647 P_Source_contract_Line_Id => P_Source_contract_Line_Id,
648 P_Target_contract_Line_Id => P_Target_contract_Line_Id,
649 x_coverage_match => x_coverage_match);
650 EXCEPTION
651 WHEN OKC_API.G_EXCEPTION_ERROR THEN
652 x_return_status := OKC_API.HANDLE_EXCEPTIONS
653 (l_api_name,
654 G_PKG_NAME,
655 'OKC_API.G_RET_STS_ERROR',
656 x_msg_count,
657 x_msg_data,
658 '_PUB');
659 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
660 x_return_status := OKC_API.HANDLE_EXCEPTIONS
661 (l_api_name,
662 G_PKG_NAME,
663 'OKC_API.G_RET_STS_UNEXP_ERROR',
664 x_msg_count,
665 x_msg_data,
666 '_PUB');
667 WHEN G_EXCEPTION_HALT_VALIDATION THEN
668 NULL;
669 WHEN OTHERS THEN
670 OKC_API.set_message(p_app_name => g_app_name,
671 p_msg_name => g_unexpected_error,
672 p_token1 => g_sqlcode_token,
673 p_token1_value => sqlcode,
674 p_token2 => g_sqlerrm_token,
675 p_token2_value => sqlerrm);
676 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
677 END CHECK_COVERAGE_MATCH;
678
679 PROCEDURE CREATE_ADJUSTED_COVERAGE(
680 p_api_version IN NUMBER,
681 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
682 x_return_status OUT NOCOPY VARCHAR2,
683 x_msg_count OUT NOCOPY NUMBER,
684 x_msg_data OUT NOCOPY VARCHAR2,
685 P_Source_contract_Line_Id IN NUMBER,
686 P_Target_contract_Line_Id IN NUMBER,
687 x_Actual_coverage_id OUT NOCOPY NUMBER) IS
688
689 l_ac_rec_in AC_REC_TYPE;
690 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_ADJUSTED_COVERAGE';
691 l_return_status VARCHAR2(1);
692
693 BEGIN
694
695 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
696 p_init_msg_list,
697 '_PUB',
698 x_return_status);
699
700 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
701 THEN
702 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
703 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
704 THEN
705 raise OKC_API.G_EXCEPTION_ERROR;
706 ELSIF l_return_status IS NULL
707 THEN
708 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
709 END IF;
710
711 -- Call to Complex API procedure
712
713 oks_coverages_pvt.CREATE_ADJUSTED_COVERAGE(
714 p_api_version,
715 p_init_msg_list,
716 l_return_status,
717 x_msg_count,
718 x_msg_data,
719 P_Source_contract_Line_Id ,
720 P_Target_contract_Line_Id,
721 x_actual_coverage_Id);
722
723
724 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
725 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
726 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
727 raise OKC_API.G_EXCEPTION_ERROR;
728 ELSIF l_return_status IS NULL
729 THEN
730 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
731 END IF;
732
733 x_Return_status:=l_Return_Status;
734 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
735
736 EXCEPTION
737 WHEN OKC_API.G_EXCEPTION_ERROR THEN
738 x_return_status := OKC_API.HANDLE_EXCEPTIONS
739 (l_api_name,
740 G_PKG_NAME,
741 'OKC_API.G_RET_STS_ERROR',
742 x_msg_count,
743 x_msg_data,
744 '_PUB');
745 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
746 x_return_status := OKC_API.HANDLE_EXCEPTIONS
747 (l_api_name,
748 G_PKG_NAME,
749 'OKC_API.G_RET_STS_UNEXP_ERROR',
750 x_msg_count,
751 x_msg_data,
752 '_PUB');
753 WHEN G_EXCEPTION_HALT_VALIDATION THEN
754 NULL;
755 WHEN OTHERS THEN
756 OKC_API.set_message(p_app_name => g_app_name,
757 p_msg_name => g_unexpected_error,
758 p_token1 => g_sqlcode_token,
759 p_token1_value => sqlcode,
760 p_token2 => g_sqlerrm_token,
761 p_token2_value => sqlerrm);
762 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
763 END CREATE_ADJUSTED_COVERAGE;
764
765
766 PROCEDURE OKS_BILLRATE_MAPPING(
767 p_api_version IN NUMBER ,
768 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
769 p_business_process_id IN NUMBER,
770 p_time_labor_tbl_in IN OKS_COVERAGES_PVT.time_labor_tbl,
771 x_return_status OUT NOCOPY VARCHAR2,
772 x_msg_count OUT NOCOPY NUMBER,
773 x_msg_data OUT NOCOPY VARCHAR2) IS
774
775
776 l_api_version NUMBER := 1;
777 l_init_msg_list VARCHAR2(1) DEFAULT OKC_API.G_FALSE;
778 l_return_status VARCHAR2(1);
779 l_return_msg VARCHAR2(2000);
780 l_msg_count NUMBER;
781 l_msg_data VARCHAR2(2000);
782 l_api_name VARCHAR2(30):= 'OKS_BILLRATE_MAPPING';
783
784
785 BEGIN
786
787 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
788 p_init_msg_list,
789 '_PUB',
790 x_return_status);
791 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
792 THEN
793 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
794 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
795 THEN
796 raise OKC_API.G_EXCEPTION_ERROR;
797 ELSIF l_return_status IS NULL
798 THEN
799 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
800 END IF;
801
802 -- call the oks_billrate_mapping PVT API
803
804 OKS_COVERAGES_PVT.OKS_BILLRATE_MAPPING(
805 p_api_version,
806 p_init_msg_list,
807 p_business_process_id,
808 p_time_labor_tbl_in,
809 x_return_status,
810 x_msg_count,
811 x_msg_data);
812 x_Return_status:=l_Return_Status;
813 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
814
815 EXCEPTION
816
817 WHEN OKC_API.G_EXCEPTION_ERROR THEN
818 x_return_status := OKC_API.HANDLE_EXCEPTIONS
819 (l_api_name,
820 G_PKG_NAME,
821 'OKC_API.G_RET_STS_ERROR',
822 x_msg_count,
823 x_msg_data,
824 '_PUB');
825 ROLLBACK ;
826 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
827 x_return_status := OKC_API.HANDLE_EXCEPTIONS
828 (l_api_name,
829 G_PKG_NAME,
830 'OKC_API.G_RET_STS_UNEXP_ERROR',
831 x_msg_count,
832 x_msg_data,
833 '_PUB');
834 ROLLBACK ;
835 WHEN OTHERS THEN
836 OKC_API.set_message(p_app_name => g_app_name,
837 p_msg_name => g_unexpected_error,
838 p_token1 => g_sqlcode_token,
839 p_token1_value => sqlcode,
840 p_token2 => g_sqlerrm_token,
841 p_token2_value => sqlerrm);
842 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
843 ROLLBACK ;
844
845 END OKS_BILLRATE_MAPPING ;
846
847
848
849 PROCEDURE Version_Coverage(
850 p_api_version IN NUMBER,
851 p_init_msg_list IN VARCHAR2,
852 x_return_status OUT NOCOPY VARCHAR2,
853 x_msg_count OUT NOCOPY NUMBER,
854 x_msg_data OUT NOCOPY VARCHAR2,
855 p_chr_id IN NUMBER,
856 p_major_version IN NUMBER) IS
857 l_api_version NUMBER := 1;
858 l_init_msg_list VARCHAR2(1) DEFAULT OKC_API.G_FALSE;
859 l_return_status VARCHAR2(1);
860 l_return_msg VARCHAR2(2000);
861 l_msg_count NUMBER;
862 l_msg_data VARCHAR2(2000);
863 l_api_name VARCHAR2(30):= 'version_Coverage';
864
865 BEGIN
866 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
867 p_init_msg_list,
868 '_PUB',
869 x_return_status);
870
871 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
872 THEN
873 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
874 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
875 THEN
876 raise OKC_API.G_EXCEPTION_ERROR;
877 ELSIF l_return_status IS NULL
878 THEN
879 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
880 END IF;
881
882
883 OKS_COVERAGES_PVT.version_Coverage(
884 p_api_version,
885 p_init_msg_list,
886 x_return_status,
887 x_msg_count,
888 x_msg_data,
889 p_chr_id,
890 p_major_version);
891
892 x_Return_status:=l_Return_Status;
893
894
895
896
897 EXCEPTION
898 WHEN OKC_API.G_EXCEPTION_ERROR THEN
899 x_return_status := OKC_API.HANDLE_EXCEPTIONS
900 (l_api_name,
901 G_PKG_NAME,
902 'OKC_API.G_RET_STS_ERROR',
903 x_msg_count,
904 x_msg_data,
905 '_PUB');
906 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
907 x_return_status := OKC_API.HANDLE_EXCEPTIONS
908 (l_api_name,
909 G_PKG_NAME,
910 'OKC_API.G_RET_STS_UNEXP_ERROR',
911 x_msg_count,
912 x_msg_data,
913 '_PUB');
914 WHEN OTHERS THEN
915 OKC_API.set_message(p_app_name => g_app_name,
916 p_msg_name => g_unexpected_error,
917 p_token1 => g_sqlcode_token,
918 p_token1_value => sqlcode,
919 p_token2 => g_sqlerrm_token,
920 p_token2_value => sqlerrm);
921 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
922 ROLLBACK ;
923
924
925 END Version_Coverage;
926
927
928 PROCEDURE Restore_Coverage(
929 p_api_version IN NUMBER,
930 p_init_msg_list IN VARCHAR2,
931 x_return_status OUT NOCOPY VARCHAR2,
932 x_msg_count OUT NOCOPY NUMBER,
933 x_msg_data OUT NOCOPY VARCHAR2,
934 p_chr_id IN NUMBER) IS
935
936 l_api_version NUMBER := 1;
937 l_init_msg_list VARCHAR2(1) DEFAULT OKC_API.G_FALSE;
938 l_return_status VARCHAR2(1);
939 l_return_msg VARCHAR2(2000);
940 l_msg_count NUMBER;
941 l_msg_data VARCHAR2(2000);
942 l_api_name VARCHAR2(30):= 'Restore_Coverage';
943 BEGIN
944
945 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
946 p_init_msg_list,
947 '_PUB',
948 x_return_status);
949
950 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
951 THEN
952 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
953 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
954 THEN
955 raise OKC_API.G_EXCEPTION_ERROR;
956 ELSIF l_return_status IS NULL
957 THEN
958 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
959 END IF;
960
961 OKS_COVERAGES_PVT.Restore_Coverage(
962 p_api_version,
963 p_init_msg_list,
964 x_return_status,
965 x_msg_count,
966 x_msg_data,
967 p_chr_id);
968
969 x_Return_status:=l_Return_Status;
970
971
972 EXCEPTION
973 WHEN OKC_API.G_EXCEPTION_ERROR THEN
974 x_return_status := OKC_API.HANDLE_EXCEPTIONS
975 (l_api_name,
976 G_PKG_NAME,
977 'OKC_API.G_RET_STS_ERROR',
978 x_msg_count,
979 x_msg_data,
980 '_PUB');
981 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
982 x_return_status := OKC_API.HANDLE_EXCEPTIONS
983 (l_api_name,
984 G_PKG_NAME,
985 'OKC_API.G_RET_STS_UNEXP_ERROR',
986 x_msg_count,
987 x_msg_data,
988 '_PUB');
989 WHEN OTHERS THEN
990 OKC_API.set_message(p_app_name => g_app_name,
991 p_msg_name => g_unexpected_error,
992 p_token1 => g_sqlcode_token,
993 p_token1_value => sqlcode,
994 p_token2 => g_sqlerrm_token,
995 p_token2_value => sqlerrm);
996 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
997 ROLLBACK ;
998 END Restore_Coverage;
999
1000
1001
1002 PROCEDURE Delete_History(
1003 p_api_version IN NUMBER,
1004 p_init_msg_list IN VARCHAR2,
1005 x_return_status OUT NOCOPY VARCHAR2,
1006 x_msg_count OUT NOCOPY NUMBER,
1007 x_msg_data OUT NOCOPY VARCHAR2,
1008 p_chr_id IN NUMBER) IS
1009
1010 l_api_version NUMBER := 1;
1011 l_init_msg_list VARCHAR2(1) DEFAULT OKC_API.G_FALSE;
1012 l_return_status VARCHAR2(1);
1013 l_return_msg VARCHAR2(2000);
1014 l_msg_count NUMBER;
1015 l_msg_data VARCHAR2(2000);
1016 l_api_name VARCHAR2(30):= 'Delete_History';
1017 BEGIN
1018
1019 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1020 p_init_msg_list,
1021 '_PUB',
1022 x_return_status);
1023
1024 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
1025 THEN
1026 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1027 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
1028 THEN
1029 raise OKC_API.G_EXCEPTION_ERROR;
1030 ELSIF l_return_status IS NULL
1031 THEN
1032 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1033 END IF;
1034
1035 OKS_COVERAGES_PVT.DELETE_HISTORY (
1036 p_api_version,
1037 p_init_msg_list,
1038 x_return_status,
1039 x_msg_count,
1040 x_msg_data,
1041 p_chr_id);
1042
1043 x_Return_status:=l_Return_Status;
1044
1045
1046 EXCEPTION
1047 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1048 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1049 (l_api_name,
1050 G_PKG_NAME,
1051 'OKC_API.G_RET_STS_ERROR',
1052 x_msg_count,
1053 x_msg_data,
1054 '_PUB');
1055 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1056 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1057 (l_api_name,
1058 G_PKG_NAME,
1059 'OKC_API.G_RET_STS_UNEXP_ERROR',
1060 x_msg_count,
1061 x_msg_data,
1062 '_PUB');
1063 WHEN OTHERS THEN
1064 OKC_API.set_message(p_app_name => g_app_name,
1065 p_msg_name => g_unexpected_error,
1066 p_token1 => g_sqlcode_token,
1067 p_token1_value => sqlcode,
1068 p_token2 => g_sqlerrm_token,
1069 p_token2_value => sqlerrm);
1070 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1071 ROLLBACK ;
1072 END DELETE_HISTORY;
1073
1074 PROCEDURE Delete_Saved_Version(
1075 p_api_version IN NUMBER,
1076 p_init_msg_list IN VARCHAR2,
1077 x_return_status OUT NOCOPY VARCHAR2,
1078 x_msg_count OUT NOCOPY NUMBER,
1079 x_msg_data OUT NOCOPY VARCHAR2,
1080 p_chr_id IN NUMBER) IS
1081
1082 l_api_version NUMBER := 1;
1083 l_init_msg_list VARCHAR2(1) DEFAULT OKC_API.G_FALSE;
1084 l_return_status VARCHAR2(1);
1085 l_return_msg VARCHAR2(2000);
1086 l_msg_count NUMBER;
1087 l_msg_data VARCHAR2(2000);
1088 l_api_name VARCHAR2(30):= 'Delete_Saved_Version';
1089 BEGIN
1090
1091 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1092 p_init_msg_list,
1093 '_PUB',
1094 x_return_status);
1095
1096 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR)
1097 THEN
1098 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1099 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR)
1100 THEN
1101 raise OKC_API.G_EXCEPTION_ERROR;
1102 ELSIF l_return_status IS NULL
1103 THEN
1104 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1105 END IF;
1106
1107 OKS_COVERAGES_PVT.Delete_Saved_Version (
1108 p_api_version,
1109 p_init_msg_list,
1110 x_return_status,
1111 x_msg_count,
1112 x_msg_data,
1113 p_chr_id);
1114
1115 x_Return_status:=l_Return_Status;
1116
1117 EXCEPTION
1118 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1119 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1120 (l_api_name,
1121 G_PKG_NAME,
1122 'OKC_API.G_RET_STS_ERROR',
1123 x_msg_count,
1124 x_msg_data,
1125 '_PUB');
1126 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1127 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1128 (l_api_name,
1129 G_PKG_NAME,
1130 'OKC_API.G_RET_STS_UNEXP_ERROR',
1131 x_msg_count,
1132 x_msg_data,
1133 '_PUB');
1134 WHEN OTHERS THEN
1135 OKC_API.set_message(p_app_name => g_app_name,
1136 p_msg_name => g_unexpected_error,
1137 p_token1 => g_sqlcode_token,
1138 p_token1_value => sqlcode,
1139 p_token2 => g_sqlerrm_token,
1140 p_token2_value => sqlerrm);
1141 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1142 ROLLBACK ;
1143 END;
1144
1145 END OKS_COVERAGES_PUB;