[Home] [Help]
PACKAGE BODY: APPS.OKC_CONDITIONS_PVT
Source
1 package body okc_conditions_pvt as
2 /* $Header: OKCCCNHB.pls 120.0 2005/05/25 23:07:45 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 PROCEDURE add_language IS
7 Begin
8 okc_cnh_pvt.add_language;
9 okc_cnl_pvt.add_language;
10 End;
11
12 --Object type procedure for insert
13 PROCEDURE create_cond_hdrs(
14 p_api_version IN NUMBER,
15 p_init_msg_list IN VARCHAR2 ,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 p_cnhv_rec IN cnhv_rec_type,
20 p_cnlv_tbl IN cnlv_tbl_type,
21 x_cnhv_rec OUT NOCOPY cnhv_rec_type,
22 x_cnlv_tbl OUT NOCOPY cnlv_tbl_type) IS
23
24 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
25 l_cnhv_rec cnhv_rec_type;
26 l_cnlv_tbl cnlv_tbl_type := p_cnlv_tbl;
27 i NUMBER;
28 begin
29 --Populate the Master
30 create_cond_hdrs(
31 p_api_version,
32 p_init_msg_list,
33 x_return_status,
34 x_msg_count,
35 x_msg_data,
36 p_cnhv_rec,
37 x_cnhv_rec);
38 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
39 raise G_EXCEPTION_HALT_VALIDATION;
40 ELSE
41 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
42 l_return_status := x_return_status;
43 END IF;
44 END IF;
45
46 -- Populate the foreign key for the detail
47 IF (l_cnlv_tbl.COUNT > 0) THEN
48 i := l_cnlv_tbl.FIRST;
49 LOOP
50 l_cnlv_tbl(i).cnh_id := x_cnhv_rec.id;
51 EXIT WHEN (i = l_cnlv_tbl.LAST);
52 i := l_cnlv_tbl.NEXT(i);
53 END LOOP;
54 END IF;
55
56 --Populate the detail
57 create_cond_lines(
58 p_api_version,
59 p_init_msg_list,
60 x_return_status,
61 x_msg_count,
62 x_msg_data,
63 l_cnlv_tbl,
64 x_cnlv_tbl);
65 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
66 raise G_EXCEPTION_HALT_VALIDATION;
67 ELSE
68 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
69 l_return_status := x_return_status;
70 END IF;
71 END IF;
72
73 EXCEPTION
74 WHEN G_EXCEPTION_HALT_VALIDATION THEN
75 Null;
76
77 WHEN OTHERS THEN
78 OKC_API.set_message(p_app_name => g_app_name,
79 p_msg_name => g_unexpected_error,
80 p_token1 => g_sqlcode_token,
81 p_token1_value => sqlcode,
82 p_token2 => g_sqlerrm_token,
83 p_token2_value => sqlerrm);
84 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
85 End create_cond_hdrs;
86
87 --Object type procedure for update
88 PROCEDURE update_cond_hdrs(
89 p_api_version IN NUMBER,
90 p_init_msg_list IN VARCHAR2 ,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2,
94 p_cnhv_rec IN cnhv_rec_type,
95 p_cnlv_tbl IN cnlv_tbl_type,
96 x_cnhv_rec OUT NOCOPY cnhv_rec_type,
97 x_cnlv_tbl OUT NOCOPY cnlv_tbl_type) IS
98 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
99 begin
100 --Update the Master
101 update_cond_hdrs(
102 p_api_version,
103 p_init_msg_list,
104 x_return_status,
105 x_msg_count,
106 x_msg_data,
107 p_cnhv_rec,
108 x_cnhv_rec);
109 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
110 raise G_EXCEPTION_HALT_VALIDATION;
111 ELSE
112 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
113 l_return_status := x_return_status;
114 END IF;
115 END IF;
116
117 --Update the detail
118 update_cond_lines(
119 p_api_version,
120 p_init_msg_list,
121 x_return_status,
122 x_msg_count,
123 x_msg_data,
124 p_cnlv_tbl,
125 x_cnlv_tbl);
126 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
127 raise G_EXCEPTION_HALT_VALIDATION;
128 ELSE
129 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
130 l_return_status := x_return_status;
131 END IF;
132 END IF;
133
134 EXCEPTION
135 WHEN G_EXCEPTION_HALT_VALIDATION THEN
136 Null;
137
138 WHEN OTHERS THEN
139 OKC_API.set_message(p_app_name => g_app_name,
140 p_msg_name => g_unexpected_error,
141 p_token1 => g_sqlcode_token,
142 p_token1_value => sqlcode,
143 p_token2 => g_sqlerrm_token,
144 p_token2_value => sqlerrm);
145 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
146 end update_cond_hdrs;
147
148 --Object type procedure for validate
149 PROCEDURE validate_cond_hdrs(
150 p_api_version IN NUMBER,
151 p_init_msg_list IN VARCHAR2 ,
152 x_return_status OUT NOCOPY VARCHAR2,
153 x_msg_count OUT NOCOPY NUMBER,
154 x_msg_data OUT NOCOPY VARCHAR2,
155 p_cnhv_rec IN cnhv_rec_type,
156 p_cnlv_tbl IN cnlv_tbl_type) IS
157 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
158 begin
159 --Validate the Master
160 validate_cond_hdrs(
161 p_api_version,
162 p_init_msg_list,
163 x_return_status,
164 x_msg_count,
165 x_msg_data,
166 p_cnhv_rec);
167 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
168 raise G_EXCEPTION_HALT_VALIDATION;
169 ELSE
170 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
171 l_return_status := x_return_status;
172 END IF;
173 END IF;
174
175 --Validate the Detail
176 validate_cond_lines(
177 p_api_version,
178 p_init_msg_list,
179 x_return_status,
180 x_msg_count,
181 x_msg_data,
182 p_cnlv_tbl);
183 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
184 raise G_EXCEPTION_HALT_VALIDATION;
185 ELSE
186 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
187 l_return_status := x_return_status;
188 END IF;
189 END IF;
190
191 EXCEPTION
192 WHEN G_EXCEPTION_HALT_VALIDATION THEN
193 Null;
194
195 WHEN OTHERS THEN
196 OKC_API.set_message(p_app_name => g_app_name,
197 p_msg_name => g_unexpected_error,
198 p_token1 => g_sqlcode_token,
199 p_token1_value => sqlcode,
200 p_token2 => g_sqlerrm_token,
201 p_token2_value => sqlerrm);
202 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
203 End validate_cond_hdrs;
204
205 -- Procedure for updating the minor version
206
207 PROCEDURE update_minor_version(p_chr_id IN NUMBER) IS
208 l_api_version NUMBER := 1;
209 l_init_msg_list VARCHAR2(1) := 'F';
210 x_return_status VARCHAR2(1);
211 x_msg_count NUMBER ;
212 x_msg_data VARCHAR2(2000);
213 x_out_rec OKC_CVM_PVT.cvmv_rec_type;
214 l_cvmv_rec OKC_CVM_PVT.cvmv_rec_type;
215 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
216
217 BEGIN
218 -- assign/populate contract header id
219 l_cvmv_rec.chr_id := p_chr_id;
220
221 OKC_CVM_PVT.update_contract_version(
222 p_api_version => l_api_version,
223 p_init_msg_list => l_init_msg_list,
224 x_return_status => x_return_status,
225 x_msg_count => x_msg_count,
226 x_msg_data => x_msg_data,
227 p_cvmv_rec => l_cvmv_rec,
228 x_cvmv_rec => x_out_rec);
229
230 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
231 RAISE G_EXCEPTION_HALT_VALIDATION;
232 ELSE
233 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
234 l_return_status := x_return_status;
235 END IF;
236 END IF;
237 EXCEPTION
238 WHEN G_EXCEPTION_HALT_VALIDATION THEN
239 NULL;
240 WHEN OTHERS THEN
241 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
242 p_msg_name => g_unexpected_error,
243 p_token1 => g_sqlcode_token,
244 p_token1_value => sqlcode,
245 p_token2 => g_sqlerrm_token,
246 p_token2_value => sqlerrm);
247 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
248 END update_minor_version;
249
250 --Procedures for Condition Header
251
252 PROCEDURE create_cond_hdrs(
253 p_api_version IN NUMBER,
254 p_init_msg_list IN VARCHAR2 ,
255 x_return_status OUT NOCOPY VARCHAR2,
256 x_msg_count OUT NOCOPY NUMBER,
257 x_msg_data OUT NOCOPY VARCHAR2,
258 p_cnhv_rec IN cnhv_rec_type,
259 x_cnhv_rec OUT NOCOPY cnhv_rec_type) IS
260 begin
261 okc_cnh_pvt.insert_row(
262 p_api_version,
263 p_init_msg_list,
264 x_return_status,
265 x_msg_count,
266 x_msg_data,
267 p_cnhv_rec,
268 x_cnhv_rec);
269
270 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
271 Update_Minor_Version(p_cnhv_rec.dnz_chr_id);
272 END IF;
273
274 End create_cond_hdrs;
275
276 PROCEDURE create_cond_hdrs(
277 p_api_version IN NUMBER,
278 p_init_msg_list IN VARCHAR2 ,
279 x_return_status OUT NOCOPY VARCHAR2,
280 x_msg_count OUT NOCOPY NUMBER,
281 x_msg_data OUT NOCOPY VARCHAR2,
282 p_cnhv_tbl IN cnhv_tbl_type,
283 x_cnhv_tbl OUT NOCOPY cnhv_tbl_type) IS
284 begin
285 okc_cnh_pvt.insert_row(
286 p_api_version,
287 p_init_msg_list,
288 x_return_status,
289 x_msg_count,
290 x_msg_data,
291 p_cnhv_tbl,
292 x_cnhv_tbl);
293 End create_cond_hdrs;
294
295 PROCEDURE lock_cond_hdrs(
296 p_api_version IN NUMBER,
297 p_init_msg_list IN VARCHAR2 ,
298 x_return_status OUT NOCOPY VARCHAR2,
299 x_msg_count OUT NOCOPY NUMBER,
300 x_msg_data OUT NOCOPY VARCHAR2,
301 p_cnhv_rec IN cnhv_rec_type) IS
302 begin
303 okc_cnh_pvt.lock_row(
304 p_api_version,
305 p_init_msg_list,
306 x_return_status,
307 x_msg_count,
308 x_msg_data,
309 p_cnhv_rec);
310 End lock_cond_hdrs;
311
312 PROCEDURE lock_cond_hdrs(
313 p_api_version IN NUMBER,
314 p_init_msg_list IN VARCHAR2 ,
315 x_return_status OUT NOCOPY VARCHAR2,
316 x_msg_count OUT NOCOPY NUMBER,
317 x_msg_data OUT NOCOPY VARCHAR2,
318 p_cnhv_tbl IN cnhv_tbl_type) IS
319 begin
320 okc_cnh_pvt.lock_row(
321 p_api_version,
322 p_init_msg_list,
323 x_return_status,
324 x_msg_count,
325 x_msg_data,
326 p_cnhv_tbl);
327 End lock_cond_hdrs;
328
329 PROCEDURE update_cond_hdrs(
330 p_api_version IN NUMBER,
331 p_init_msg_list IN VARCHAR2 ,
332 x_return_status OUT NOCOPY VARCHAR2,
333 x_msg_count OUT NOCOPY NUMBER,
334 x_msg_data OUT NOCOPY VARCHAR2,
335 p_cnhv_rec IN cnhv_rec_type,
336 x_cnhv_rec OUT NOCOPY cnhv_rec_type) IS
337 begin
338 okc_cnh_pvt.update_row(
339 p_api_version,
340 p_init_msg_list,
341 x_return_status,
342 x_msg_count,
343 x_msg_data,
344 p_cnhv_rec,
345 x_cnhv_rec);
346
347 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
348 Update_Minor_Version(p_cnhv_rec.dnz_chr_id);
349 END IF;
350 End update_cond_hdrs;
351
352 PROCEDURE update_cond_hdrs(
353 p_api_version IN NUMBER,
354 p_init_msg_list IN VARCHAR2 ,
355 x_return_status OUT NOCOPY VARCHAR2,
356 x_msg_count OUT NOCOPY NUMBER,
357 x_msg_data OUT NOCOPY VARCHAR2,
358 p_cnhv_tbl IN cnhv_tbl_type,
359 x_cnhv_tbl OUT NOCOPY cnhv_tbl_type) IS
360 begin
361 okc_cnh_pvt.update_row(
362 p_api_version,
363 p_init_msg_list,
364 x_return_status,
365 x_msg_count,
366 x_msg_data,
367 p_cnhv_tbl,
368 x_cnhv_tbl);
369 End update_cond_hdrs;
370
371 --Procedure for Cascade Delete
372 PROCEDURE delete_cond_hdrs(
373 p_api_version IN NUMBER,
374 p_init_msg_list IN VARCHAR2 ,
375 x_return_status OUT NOCOPY VARCHAR2,
376 x_msg_count OUT NOCOPY NUMBER,
377 x_msg_data OUT NOCOPY VARCHAR2,
378 p_cnhv_rec IN cnhv_rec_type) IS
379
380 l_dummy VARCHAR2(1) ;
381 l_row_found BOOLEAN := TRUE ;
382 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
383 i NUMBER := 0;
384 l_cnlv_tbl cnlv_tbl_type;
385 l_ocev_tbl ocev_tbl_type;
386 --Bug 3122962
387 Cursor tve_cur is
388 select '1'
389 from okc_timevalues tve
390 where tve.cnh_id = p_cnhv_rec.id
391 and tve.tve_type = 'TGN';
392
393 Cursor cnl_cur is
394 select cnl.id
395 from okc_condition_lines_v cnl
396 where cnl.cnh_id = p_cnhv_rec.id;
397
398 Cursor oce_cur is
399 select oce.id
400 from okc_outcomes_v oce
401 where oce.cnh_id = p_cnhv_rec.id;
402
403 begin
404 -- check if condition has time value attached to it, if it is attached
405 -- then raise error message
406 OPEN tve_cur;
407 FETCH tve_cur INTO l_dummy;
408 l_row_found := tve_cur%FOUND;
409 CLOSE tve_cur;
410 IF (l_row_found) THEN
411 -- put error message
412 OKC_API.set_message(G_APP_NAME, 'OKC_TIMEVALUE_EXIST');
413 x_return_status := OKC_API.G_RET_STS_ERROR ;
414 raise G_EXCEPTION_HALT_VALIDATION;
415 ELSE
416 --populate the Foreign key of the outcomes
417 For oce_rec in oce_cur loop
418 i := i + 1;
419 l_ocev_tbl(i).id := oce_rec.id;
420 End loop;
421
422 --Delete outcomes
423 IF l_ocev_tbl.COUNT > 0 THEN
424 -- call Public delete procedure for outcomes
425 okc_outcome_pub.delete_outcome(
426 p_api_version,
427 p_init_msg_list,
428 x_return_status,
429 x_msg_count,
430 x_msg_data,
431 l_ocev_tbl);
432 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
433 raise G_EXCEPTION_HALT_VALIDATION;
434 ELSE
435 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
436 l_return_status := x_return_status;
437 END IF;
438 END IF;
439 END IF;
440
441 --populate the Foreign key of the detail
442 For cnl_rec in cnl_cur loop
443 i := i + 1;
444 l_cnlv_tbl(i).id := cnl_rec.id;
445 End loop;
446
447 --Delete the details
448 -- call Public delete procedure
449 IF l_cnlv_tbl.COUNT > 0 THEN
450 okc_conditions_pub.delete_cond_lines(
451 p_api_version,
452 p_init_msg_list,
453 x_return_status,
454 x_msg_count,
455 x_msg_data,
456 l_cnlv_tbl);
457 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
458 raise G_EXCEPTION_HALT_VALIDATION;
459 ELSE
460 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
461 l_return_status := x_return_status;
462 END IF;
463 END IF;
464 END IF;
465
466 --Delete the Master
467 okc_cnh_pvt.delete_row(
468 p_api_version,
469 p_init_msg_list,
470 x_return_status,
471 x_msg_count,
472 x_msg_data,
473 p_cnhv_rec);
474 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
475 raise G_EXCEPTION_HALT_VALIDATION;
476 ELSE
477 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
478 l_return_status := x_return_status;
479 END IF;
480 END IF;
481 END IF;
482
483 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
484 Update_Minor_Version(p_cnhv_rec.dnz_chr_id);
485 END IF;
486
487 EXCEPTION
488 WHEN G_EXCEPTION_HALT_VALIDATION THEN
489 Null;
490
491 WHEN OTHERS THEN
492 OKC_API.set_message(p_app_name => g_app_name,
493 p_msg_name => g_unexpected_error,
494 p_token1 => g_sqlcode_token,
495 p_token1_value => sqlcode,
496 p_token2 => g_sqlerrm_token,
497 p_token2_value => sqlerrm);
498 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
499 End delete_cond_hdrs;
500
501 PROCEDURE delete_cond_hdrs(
502 p_api_version IN NUMBER,
503 p_init_msg_list IN VARCHAR2 ,
504 x_return_status OUT NOCOPY VARCHAR2,
505 x_msg_count OUT NOCOPY NUMBER,
506 x_msg_data OUT NOCOPY VARCHAR2,
507 p_cnhv_tbl IN cnhv_tbl_type) IS
508 i NUMBER :=0;
509 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
510 begin
511 --Initialize the return status
512 x_return_status := OKC_API.G_RET_STS_SUCCESS;
513
514 IF (p_cnhv_tbl.COUNT > 0) THEN
515 i := p_cnhv_tbl.FIRST;
516 LOOP
517 delete_cond_hdrs(
518 p_api_version,
519 p_init_msg_list,
520 x_return_status,
521 x_msg_count,
522 x_msg_data,
523 p_cnhv_tbl(i));
524 EXIT WHEN (i = p_cnhv_tbl.LAST);
525 i := p_cnhv_tbl.NEXT(i);
526 END LOOP;
527 END IF;
528 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
529 raise G_EXCEPTION_HALT_VALIDATION;
530 ELSE
531 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
532 l_return_status := x_return_status;
533 END IF;
534 END IF;
535 EXCEPTION
536 WHEN G_EXCEPTION_HALT_VALIDATION THEN
537 Null;
538
539 WHEN OTHERS THEN
540 OKC_API.set_message(p_app_name => g_app_name,
541 p_msg_name => g_unexpected_error,
542 p_token1 => g_sqlcode_token,
543 p_token1_value => sqlcode,
544 p_token2 => g_sqlerrm_token,
545 p_token2_value => sqlerrm);
546 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
547 End delete_cond_hdrs;
548
549 PROCEDURE validate_cond_hdrs(
550 p_api_version IN NUMBER,
551 p_init_msg_list IN VARCHAR2 ,
552 x_return_status OUT NOCOPY VARCHAR2,
553 x_msg_count OUT NOCOPY NUMBER,
554 x_msg_data OUT NOCOPY VARCHAR2,
555 p_cnhv_rec IN cnhv_rec_type) IS
556 begin
557 okc_cnh_pvt.validate_row(
558 p_api_version,
559 p_init_msg_list,
560 x_return_status,
561 x_msg_count,
562 x_msg_data,
563 p_cnhv_rec);
564 End validate_cond_hdrs;
565
566 PROCEDURE validate_cond_hdrs(
567 p_api_version IN NUMBER,
568 p_init_msg_list IN VARCHAR2 ,
569 x_return_status OUT NOCOPY VARCHAR2,
570 x_msg_count OUT NOCOPY NUMBER,
571 x_msg_data OUT NOCOPY VARCHAR2,
572 p_cnhv_tbl IN cnhv_tbl_type) IS
573 begin
574 okc_cnh_pvt.validate_row(
575 p_api_version,
576 p_init_msg_list,
577 x_return_status,
578 x_msg_count,
579 x_msg_data,
580 p_cnhv_tbl);
581 End validate_cond_hdrs;
582
583 /*****************************************************************/
584 --Procedures for Condition Lines
585
586 PROCEDURE create_cond_lines(
587 p_api_version IN NUMBER,
588 p_init_msg_list IN VARCHAR2 ,
589 x_return_status OUT NOCOPY VARCHAR2,
590 x_msg_count OUT NOCOPY NUMBER,
591 x_msg_data OUT NOCOPY VARCHAR2,
592 p_cnlv_rec IN cnlv_rec_type,
593 x_cnlv_rec OUT NOCOPY cnlv_rec_type) IS
594 begin
595 okc_cnl_pvt.insert_row(
596 p_api_version,
597 p_init_msg_list,
598 x_return_status,
599 x_msg_count,
600 x_msg_data,
601 p_cnlv_rec,
602 x_cnlv_rec);
603
604 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
605 Update_Minor_Version(p_cnlv_rec.dnz_chr_id);
606 END IF;
607 End create_cond_lines;
608
609 PROCEDURE create_cond_lines(
610 p_api_version IN NUMBER,
611 p_init_msg_list IN VARCHAR2 ,
612 x_return_status OUT NOCOPY VARCHAR2,
613 x_msg_count OUT NOCOPY NUMBER,
614 x_msg_data OUT NOCOPY VARCHAR2,
615 p_cnlv_tbl IN cnlv_tbl_type,
616 x_cnlv_tbl OUT NOCOPY cnlv_tbl_type) IS
617 begin
618 okc_cnl_pvt.insert_row(
619 p_api_version,
620 p_init_msg_list,
621 x_return_status,
622 x_msg_count,
623 x_msg_data,
624 p_cnlv_tbl,
625 x_cnlv_tbl);
626 End create_cond_lines;
627
628 PROCEDURE lock_cond_lines(
629 p_api_version IN NUMBER,
630 p_init_msg_list IN VARCHAR2 ,
631 x_return_status OUT NOCOPY VARCHAR2,
632 x_msg_count OUT NOCOPY NUMBER,
633 x_msg_data OUT NOCOPY VARCHAR2,
634 p_cnlv_rec IN cnlv_rec_type) IS
635 begin
636 okc_cnl_pvt.lock_row(
637 p_api_version,
638 p_init_msg_list,
639 x_return_status,
640 x_msg_count,
641 x_msg_data,
642 p_cnlv_rec);
643 End lock_cond_lines;
644
645 PROCEDURE lock_cond_lines(
646 p_api_version IN NUMBER,
647 p_init_msg_list IN VARCHAR2 ,
648 x_return_status OUT NOCOPY VARCHAR2,
649 x_msg_count OUT NOCOPY NUMBER,
650 x_msg_data OUT NOCOPY VARCHAR2,
651 p_cnlv_tbl IN cnlv_tbl_type) IS
652 begin
653 okc_cnl_pvt.lock_row(
654 p_api_version,
655 p_init_msg_list,
656 x_return_status,
657 x_msg_count,
658 x_msg_data,
659 p_cnlv_tbl);
660 End lock_cond_lines;
661
662 PROCEDURE update_cond_lines(
663 p_api_version IN NUMBER,
664 p_init_msg_list IN VARCHAR2 ,
665 x_return_status OUT NOCOPY VARCHAR2,
666 x_msg_count OUT NOCOPY NUMBER,
667 x_msg_data OUT NOCOPY VARCHAR2,
668 p_cnlv_rec IN cnlv_rec_type,
669 x_cnlv_rec OUT NOCOPY cnlv_rec_type) IS
670 begin
671 okc_cnl_pvt.update_row(
672 p_api_version,
673 p_init_msg_list,
674 x_return_status,
675 x_msg_count,
676 x_msg_data,
677 p_cnlv_rec,
678 x_cnlv_rec);
679
680 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
681 Update_Minor_Version(p_cnlv_rec.dnz_chr_id);
682 END IF;
683 End update_cond_lines;
684
685 PROCEDURE update_cond_lines(
686 p_api_version IN NUMBER,
687 p_init_msg_list IN VARCHAR2 ,
688 x_return_status OUT NOCOPY VARCHAR2,
689 x_msg_count OUT NOCOPY NUMBER,
690 x_msg_data OUT NOCOPY VARCHAR2,
691 p_cnlv_tbl IN cnlv_tbl_type,
692 x_cnlv_tbl OUT NOCOPY cnlv_tbl_type) IS
693 begin
694 okc_cnl_pvt.update_row(
695 p_api_version,
696 p_init_msg_list,
697 x_return_status,
698 x_msg_count,
699 x_msg_data,
700 p_cnlv_tbl,
701 x_cnlv_tbl);
702 End update_cond_lines;
703
704 PROCEDURE delete_cond_lines(
705 p_api_version IN NUMBER,
706 p_init_msg_list IN VARCHAR2 ,
707 x_return_status OUT NOCOPY VARCHAR2,
708 x_msg_count OUT NOCOPY NUMBER,
709 x_msg_data OUT NOCOPY VARCHAR2,
710 p_cnlv_rec IN cnlv_rec_type) IS
711
712 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
713 i NUMBER := 0;
714 l_fepv_tbl fepv_tbl_type;
715
716 Cursor fep_cur is
717 select fep.id
718 from okc_function_expr_params_v fep
719 where fep.cnl_id = p_cnlv_rec.id;
720 begin
721 -- Populate the Foreign key of the detail
722 For fep_rec in fep_cur loop
723 i := i + 1;
724 l_fepv_tbl(i).id := fep_rec.id;
725 End loop;
726 --Delete the fep details
727 --call public delete procedure
728 IF l_fepv_tbl.COUNT > 0 THEN
729 okc_conditions_pub.delete_func_exprs(
730 p_api_version,
731 p_init_msg_list,
732 x_return_status,
733 x_msg_count,
734 x_msg_data,
735 l_fepv_tbl);
736 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
737 raise G_EXCEPTION_HALT_VALIDATION;
738 ELSE
739 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
740 l_return_status := x_return_status;
741 END IF;
742 END IF;
743 END IF;
744
745 --Delete the condition lines
746 okc_cnl_pvt.delete_row(
747 p_api_version,
748 p_init_msg_list,
749 x_return_status,
750 x_msg_count,
751 x_msg_data,
752 p_cnlv_rec);
753 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
754 raise G_EXCEPTION_HALT_VALIDATION;
755 ELSE
756 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
757 l_return_status := x_return_status;
758 END IF;
759 END IF;
760
761 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
762 Update_Minor_Version(p_cnlv_rec.dnz_chr_id);
763 END IF;
764 EXCEPTION
765 WHEN G_EXCEPTION_HALT_VALIDATION THEN
766 Null;
767
768 WHEN OTHERS THEN
769 OKC_API.set_message(p_app_name => g_app_name,
770 p_msg_name => g_unexpected_error,
771 p_token1 => g_sqlcode_token,
772 p_token1_value => sqlcode,
773 p_token2 => g_sqlerrm_token,
774 p_token2_value => sqlerrm);
775 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
776 End delete_cond_lines;
777
778 PROCEDURE delete_cond_lines(
779 p_api_version IN NUMBER,
780 p_init_msg_list IN VARCHAR2 ,
781 x_return_status OUT NOCOPY VARCHAR2,
782 x_msg_count OUT NOCOPY NUMBER,
783 x_msg_data OUT NOCOPY VARCHAR2,
784 p_cnlv_tbl IN cnlv_tbl_type) IS
785 begin
786 okc_cnl_pvt.delete_row(
787 p_api_version,
788 p_init_msg_list,
789 x_return_status,
790 x_msg_count,
791 x_msg_data,
792 p_cnlv_tbl);
793
794 End delete_cond_lines;
795
796 PROCEDURE validate_cond_lines(
797 p_api_version IN NUMBER,
798 p_init_msg_list IN VARCHAR2 ,
799 x_return_status OUT NOCOPY VARCHAR2,
800 x_msg_count OUT NOCOPY NUMBER,
801 x_msg_data OUT NOCOPY VARCHAR2,
802 p_cnlv_rec IN cnlv_rec_type) IS
803 begin
804 okc_cnl_pvt.validate_row(
805 p_api_version,
806 p_init_msg_list,
807 x_return_status,
808 x_msg_count,
809 x_msg_data,
810 p_cnlv_rec);
811 End validate_cond_lines;
812
813 PROCEDURE validate_cond_lines(
814 p_api_version IN NUMBER,
815 p_init_msg_list IN VARCHAR2 ,
816 x_return_status OUT NOCOPY VARCHAR2,
817 x_msg_count OUT NOCOPY NUMBER,
818 x_msg_data OUT NOCOPY VARCHAR2,
819 p_cnlv_tbl IN cnlv_tbl_type) IS
820 begin
821 okc_cnl_pvt.validate_row(
822 p_api_version,
823 p_init_msg_list,
824 x_return_status,
825 x_msg_count,
826 x_msg_data,
827 p_cnlv_tbl);
828 End validate_cond_lines;
829
830 /*****************************************************************/
831 --Procedures for Action Attribute Values
832
833 PROCEDURE create_act_att_vals(
834 p_api_version IN NUMBER,
835 p_init_msg_list IN VARCHAR2 ,
836 x_return_status OUT NOCOPY VARCHAR2,
837 x_msg_count OUT NOCOPY NUMBER,
838 x_msg_data OUT NOCOPY VARCHAR2,
839 p_aavv_rec IN aavv_rec_type,
840 x_aavv_rec OUT NOCOPY aavv_rec_type) IS
841 begin
842 okc_aav_pvt.insert_row(
843 p_api_version,
844 p_init_msg_list,
845 x_return_status,
846 x_msg_count,
847 x_msg_data,
848 p_aavv_rec,
849 x_aavv_rec);
850 End create_act_att_vals;
851
852 PROCEDURE create_act_att_vals(
853 p_api_version IN NUMBER,
854 p_init_msg_list IN VARCHAR2 ,
855 x_return_status OUT NOCOPY VARCHAR2,
856 x_msg_count OUT NOCOPY NUMBER,
857 x_msg_data OUT NOCOPY VARCHAR2,
858 p_aavv_tbl IN aavv_tbl_type,
859 x_aavv_tbl OUT NOCOPY aavv_tbl_type) IS
860 begin
861 okc_aav_pvt.insert_row(
862 p_api_version,
863 p_init_msg_list,
864 x_return_status,
865 x_msg_count,
866 x_msg_data,
867 p_aavv_tbl,
868 x_aavv_tbl);
869 End create_act_att_vals;
870
871 PROCEDURE delete_act_att_vals(
872 p_api_version IN NUMBER,
873 p_init_msg_list IN VARCHAR2 ,
874 x_return_status OUT NOCOPY VARCHAR2,
875 x_msg_count OUT NOCOPY NUMBER,
876 x_msg_data OUT NOCOPY VARCHAR2,
877 p_aavv_rec IN aavv_rec_type) IS
878 begin
879 okc_aav_pvt.delete_row(
880 p_api_version,
881 p_init_msg_list,
882 x_return_status,
883 x_msg_count,
884 x_msg_data,
885 p_aavv_rec);
886 End delete_act_att_vals;
887
888 PROCEDURE delete_act_att_vals(
889 p_api_version IN NUMBER,
890 p_init_msg_list IN VARCHAR2 ,
891 x_return_status OUT NOCOPY VARCHAR2,
892 x_msg_count OUT NOCOPY NUMBER,
893 x_msg_data OUT NOCOPY VARCHAR2,
894 p_aavv_tbl IN aavv_tbl_type) IS
895 begin
896 okc_aav_pvt.delete_row(
897 p_api_version,
898 p_init_msg_list,
899 x_return_status,
900 x_msg_count,
901 x_msg_data,
902 p_aavv_tbl);
903 End delete_act_att_vals;
904
905 PROCEDURE validate_act_att_vals(
906 p_api_version IN NUMBER,
907 p_init_msg_list IN VARCHAR2 ,
908 x_return_status OUT NOCOPY VARCHAR2,
909 x_msg_count OUT NOCOPY NUMBER,
910 x_msg_data OUT NOCOPY VARCHAR2,
911 p_aavv_rec IN aavv_rec_type) IS
912 begin
913 okc_aav_pvt.validate_row(
914 p_api_version,
915 p_init_msg_list,
916 x_return_status,
917 x_msg_count,
918 x_msg_data,
919 p_aavv_rec);
920 End validate_act_att_vals;
921
922 PROCEDURE validate_act_att_vals(
923 p_api_version IN NUMBER,
924 p_init_msg_list IN VARCHAR2 ,
925 x_return_status OUT NOCOPY VARCHAR2,
926 x_msg_count OUT NOCOPY NUMBER,
927 x_msg_data OUT NOCOPY VARCHAR2,
928 p_aavv_tbl IN aavv_tbl_type) IS
929 begin
930 okc_aav_pvt.validate_row(
931 p_api_version,
932 p_init_msg_list,
933 x_return_status,
934 x_msg_count,
935 x_msg_data,
936 p_aavv_tbl);
937 End validate_act_att_vals;
938
939 /*****************************************************************/
940 --Procedures for Condition Occurrence
941
942 PROCEDURE create_cond_occurs(
943 p_api_version IN NUMBER,
944 p_init_msg_list IN VARCHAR2 ,
945 x_return_status OUT NOCOPY VARCHAR2,
946 x_msg_count OUT NOCOPY NUMBER,
947 x_msg_data OUT NOCOPY VARCHAR2,
948 p_coev_rec IN coev_rec_type,
949 x_coev_rec OUT NOCOPY coev_rec_type) IS
950 begin
951 okc_coe_pvt.insert_row(
952 p_api_version,
953 p_init_msg_list,
954 x_return_status,
955 x_msg_count,
956 x_msg_data,
957 p_coev_rec,
958 x_coev_rec);
959 End create_cond_occurs;
960
961 PROCEDURE create_cond_occurs(
962 p_api_version IN NUMBER,
963 p_init_msg_list IN VARCHAR2 ,
964 x_return_status OUT NOCOPY VARCHAR2,
965 x_msg_count OUT NOCOPY NUMBER,
966 x_msg_data OUT NOCOPY VARCHAR2,
967 p_coev_tbl IN coev_tbl_type,
968 x_coev_tbl OUT NOCOPY coev_tbl_type) IS
969 begin
970 okc_coe_pvt.insert_row(
971 p_api_version,
972 p_init_msg_list,
973 x_return_status,
974 x_msg_count,
975 x_msg_data,
976 p_coev_tbl,
977 x_coev_tbl);
978 End create_cond_occurs;
979
980 PROCEDURE delete_cond_occurs(
981 p_api_version IN NUMBER,
982 p_init_msg_list IN VARCHAR2 ,
983 x_return_status OUT NOCOPY VARCHAR2,
984 x_msg_count OUT NOCOPY NUMBER,
985 x_msg_data OUT NOCOPY VARCHAR2,
986 p_coev_rec IN coev_rec_type) IS
987 begin
988 okc_coe_pvt.delete_row(
989 p_api_version,
990 p_init_msg_list,
991 x_return_status,
992 x_msg_count,
993 x_msg_data,
994 p_coev_rec);
995 End delete_cond_occurs;
996
997 PROCEDURE delete_cond_occurs(
998 p_api_version IN NUMBER,
999 p_init_msg_list IN VARCHAR2 ,
1000 x_return_status OUT NOCOPY VARCHAR2,
1001 x_msg_count OUT NOCOPY NUMBER,
1002 x_msg_data OUT NOCOPY VARCHAR2,
1003 p_coev_tbl IN coev_tbl_type) IS
1004 begin
1005 okc_coe_pvt.delete_row(
1006 p_api_version,
1007 p_init_msg_list,
1008 x_return_status,
1009 x_msg_count,
1010 x_msg_data,
1011 p_coev_tbl);
1012 End delete_cond_occurs;
1013
1014 PROCEDURE validate_cond_occurs(
1015 p_api_version IN NUMBER,
1016 p_init_msg_list IN VARCHAR2 ,
1017 x_return_status OUT NOCOPY VARCHAR2,
1018 x_msg_count OUT NOCOPY NUMBER,
1019 x_msg_data OUT NOCOPY VARCHAR2,
1020 p_coev_rec IN coev_rec_type) IS
1021 begin
1022 okc_coe_pvt.validate_row(
1023 p_api_version,
1024 p_init_msg_list,
1025 x_return_status,
1026 x_msg_count,
1027 x_msg_data,
1028 p_coev_rec);
1029 End validate_cond_occurs;
1030
1031 PROCEDURE validate_cond_occurs(
1032 p_api_version IN NUMBER,
1033 p_init_msg_list IN VARCHAR2 ,
1034 x_return_status OUT NOCOPY VARCHAR2,
1035 x_msg_count OUT NOCOPY NUMBER,
1036 x_msg_data OUT NOCOPY VARCHAR2,
1037 p_coev_tbl IN coev_tbl_type) IS
1038 begin
1039 okc_coe_pvt.validate_row(
1040 p_api_version,
1041 p_init_msg_list,
1042 x_return_status,
1043 x_msg_count,
1044 x_msg_data,
1045 p_coev_tbl);
1046 End validate_cond_occurs;
1047
1048 /*****************************************************************/
1049 --Procedures for Action Attribute Lookups
1050
1051 PROCEDURE create_act_att_lkps(
1052 p_api_version IN NUMBER,
1053 p_init_msg_list IN VARCHAR2 ,
1054 x_return_status OUT NOCOPY VARCHAR2,
1055 x_msg_count OUT NOCOPY NUMBER,
1056 x_msg_data OUT NOCOPY VARCHAR2,
1057 p_aalv_rec IN aalv_rec_type,
1058 x_aalv_rec OUT NOCOPY aalv_rec_type) IS
1059 begin
1060 okc_aal_pvt.insert_row(
1061 p_api_version,
1062 p_init_msg_list,
1063 x_return_status,
1064 x_msg_count,
1065 x_msg_data,
1066 p_aalv_rec,
1067 x_aalv_rec);
1068 End create_act_att_lkps;
1069
1070 PROCEDURE create_act_att_lkps(
1071 p_api_version IN NUMBER,
1072 p_init_msg_list IN VARCHAR2 ,
1073 x_return_status OUT NOCOPY VARCHAR2,
1074 x_msg_count OUT NOCOPY NUMBER,
1075 x_msg_data OUT NOCOPY VARCHAR2,
1076 p_aalv_tbl IN aalv_tbl_type,
1077 x_aalv_tbl OUT NOCOPY aalv_tbl_type) IS
1078 begin
1079 okc_aal_pvt.insert_row(
1080 p_api_version,
1081 p_init_msg_list,
1082 x_return_status,
1083 x_msg_count,
1084 x_msg_data,
1085 p_aalv_tbl,
1086 x_aalv_tbl);
1087 End create_act_att_lkps;
1088
1089 PROCEDURE lock_act_att_lkps(
1090 p_api_version IN NUMBER,
1091 p_init_msg_list IN VARCHAR2 ,
1092 x_return_status OUT NOCOPY VARCHAR2,
1093 x_msg_count OUT NOCOPY NUMBER,
1094 x_msg_data OUT NOCOPY VARCHAR2,
1095 p_aalv_rec IN aalv_rec_type) IS
1096 begin
1097 okc_aal_pvt.lock_row(
1098 p_api_version,
1099 p_init_msg_list,
1100 x_return_status,
1101 x_msg_count,
1102 x_msg_data,
1103 p_aalv_rec);
1104 End lock_act_att_lkps;
1105
1106 PROCEDURE lock_act_att_lkps(
1107 p_api_version IN NUMBER,
1108 p_init_msg_list IN VARCHAR2 ,
1109 x_return_status OUT NOCOPY VARCHAR2,
1110 x_msg_count OUT NOCOPY NUMBER,
1111 x_msg_data OUT NOCOPY VARCHAR2,
1112 p_aalv_tbl IN aalv_tbl_type) IS
1113 begin
1114 okc_aal_pvt.lock_row(
1115 p_api_version,
1116 p_init_msg_list,
1117 x_return_status,
1118 x_msg_count,
1119 x_msg_data,
1120 p_aalv_tbl);
1121 End lock_act_att_lkps;
1122
1123 PROCEDURE update_act_att_lkps(
1124 p_api_version IN NUMBER,
1125 p_init_msg_list IN VARCHAR2 ,
1126 x_return_status OUT NOCOPY VARCHAR2,
1127 x_msg_count OUT NOCOPY NUMBER,
1128 x_msg_data OUT NOCOPY VARCHAR2,
1129 p_aalv_rec IN aalv_rec_type,
1130 x_aalv_rec OUT NOCOPY aalv_rec_type) IS
1131 begin
1132 okc_aal_pvt.update_row(
1133 p_api_version,
1134 p_init_msg_list,
1135 x_return_status,
1136 x_msg_count,
1137 x_msg_data,
1138 p_aalv_rec,
1139 x_aalv_rec);
1140 End update_act_att_lkps;
1141
1142 PROCEDURE update_act_att_lkps(
1143 p_api_version IN NUMBER,
1144 p_init_msg_list IN VARCHAR2 ,
1145 x_return_status OUT NOCOPY VARCHAR2,
1146 x_msg_count OUT NOCOPY NUMBER,
1147 x_msg_data OUT NOCOPY VARCHAR2,
1148 p_aalv_tbl IN aalv_tbl_type,
1149 x_aalv_tbl OUT NOCOPY aalv_tbl_type) IS
1150 begin
1151 okc_aal_pvt.update_row(
1152 p_api_version,
1153 p_init_msg_list,
1154 x_return_status,
1155 x_msg_count,
1156 x_msg_data,
1157 p_aalv_tbl,
1158 x_aalv_tbl);
1159 End update_act_att_lkps;
1160
1161 PROCEDURE delete_act_att_lkps(
1162 p_api_version IN NUMBER,
1163 p_init_msg_list IN VARCHAR2 ,
1164 x_return_status OUT NOCOPY VARCHAR2,
1165 x_msg_count OUT NOCOPY NUMBER,
1166 x_msg_data OUT NOCOPY VARCHAR2,
1167 p_aalv_rec IN aalv_rec_type) IS
1168 begin
1169 okc_aal_pvt.delete_row(
1170 p_api_version,
1171 p_init_msg_list,
1172 x_return_status,
1173 x_msg_count,
1174 x_msg_data,
1175 p_aalv_rec);
1176 End delete_act_att_lkps;
1177
1178 PROCEDURE delete_act_att_lkps(
1179 p_api_version IN NUMBER,
1180 p_init_msg_list IN VARCHAR2 ,
1181 x_return_status OUT NOCOPY VARCHAR2,
1182 x_msg_count OUT NOCOPY NUMBER,
1183 x_msg_data OUT NOCOPY VARCHAR2,
1184 p_aalv_tbl IN aalv_tbl_type) IS
1185 begin
1186 okc_aal_pvt.delete_row(
1187 p_api_version,
1188 p_init_msg_list,
1189 x_return_status,
1190 x_msg_count,
1191 x_msg_data,
1192 p_aalv_tbl);
1193 End delete_act_att_lkps;
1194
1195 PROCEDURE validate_act_att_lkps(
1196 p_api_version IN NUMBER,
1197 p_init_msg_list IN VARCHAR2 ,
1198 x_return_status OUT NOCOPY VARCHAR2,
1199 x_msg_count OUT NOCOPY NUMBER,
1200 x_msg_data OUT NOCOPY VARCHAR2,
1201 p_aalv_rec IN aalv_rec_type) IS
1202 begin
1203 okc_aal_pvt.validate_row(
1204 p_api_version,
1205 p_init_msg_list,
1206 x_return_status,
1207 x_msg_count,
1208 x_msg_data,
1209 p_aalv_rec);
1210 End validate_act_att_lkps;
1211
1212 PROCEDURE validate_act_att_lkps(
1213 p_api_version IN NUMBER,
1214 p_init_msg_list IN VARCHAR2 ,
1215 x_return_status OUT NOCOPY VARCHAR2,
1216 x_msg_count OUT NOCOPY NUMBER,
1217 x_msg_data OUT NOCOPY VARCHAR2,
1218 p_aalv_tbl IN aalv_tbl_type) IS
1219 begin
1220 okc_aal_pvt.validate_row(
1221 p_api_version,
1222 p_init_msg_list,
1223 x_return_status,
1224 x_msg_count,
1225 x_msg_data,
1226 p_aalv_tbl);
1227 End validate_act_att_lkps;
1228
1229 /*****************************************************************/
1230 --Procedures for Function Expression Parameters
1231
1232 PROCEDURE create_func_exprs(
1233 p_api_version IN NUMBER,
1234 p_init_msg_list IN VARCHAR2 ,
1235 x_return_status OUT NOCOPY VARCHAR2,
1236 x_msg_count OUT NOCOPY NUMBER,
1237 x_msg_data OUT NOCOPY VARCHAR2,
1238 p_fepv_rec IN fepv_rec_type,
1239 x_fepv_rec OUT NOCOPY fepv_rec_type) IS
1240 begin
1241 okc_fep_pvt.insert_row(
1242 p_api_version,
1243 p_init_msg_list,
1244 x_return_status,
1245 x_msg_count,
1246 x_msg_data,
1247 p_fepv_rec,
1248 x_fepv_rec);
1249
1250 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1251 Update_Minor_Version(p_fepv_rec.dnz_chr_id);
1252 END IF;
1253 End create_func_exprs;
1254
1255 PROCEDURE create_func_exprs(
1256 p_api_version IN NUMBER,
1257 p_init_msg_list IN VARCHAR2 ,
1258 x_return_status OUT NOCOPY VARCHAR2,
1259 x_msg_count OUT NOCOPY NUMBER,
1260 x_msg_data OUT NOCOPY VARCHAR2,
1261 p_fepv_tbl IN fepv_tbl_type,
1262 x_fepv_tbl OUT NOCOPY fepv_tbl_type) IS
1263 begin
1264 okc_fep_pvt.insert_row(
1265 p_api_version,
1266 p_init_msg_list,
1267 x_return_status,
1268 x_msg_count,
1269 x_msg_data,
1270 p_fepv_tbl,
1271 x_fepv_tbl);
1272 End create_func_exprs;
1273
1274 PROCEDURE lock_func_exprs(
1275 p_api_version IN NUMBER,
1276 p_init_msg_list IN VARCHAR2 ,
1277 x_return_status OUT NOCOPY VARCHAR2,
1278 x_msg_count OUT NOCOPY NUMBER,
1279 x_msg_data OUT NOCOPY VARCHAR2,
1280 p_fepv_rec IN fepv_rec_type) IS
1281 begin
1282 okc_fep_pvt.lock_row(
1283 p_api_version,
1284 p_init_msg_list,
1285 x_return_status,
1286 x_msg_count,
1287 x_msg_data,
1288 p_fepv_rec);
1289 End lock_func_exprs;
1290
1291 PROCEDURE lock_func_exprs(
1292 p_api_version IN NUMBER,
1293 p_init_msg_list IN VARCHAR2 ,
1294 x_return_status OUT NOCOPY VARCHAR2,
1295 x_msg_count OUT NOCOPY NUMBER,
1296 x_msg_data OUT NOCOPY VARCHAR2,
1297 p_fepv_tbl IN fepv_tbl_type) IS
1298 begin
1299 okc_fep_pvt.lock_row(
1300 p_api_version,
1301 p_init_msg_list,
1302 x_return_status,
1303 x_msg_count,
1304 x_msg_data,
1305 p_fepv_tbl);
1306 End lock_func_exprs;
1307
1308 PROCEDURE update_func_exprs(
1309 p_api_version IN NUMBER,
1310 p_init_msg_list IN VARCHAR2 ,
1311 x_return_status OUT NOCOPY VARCHAR2,
1312 x_msg_count OUT NOCOPY NUMBER,
1313 x_msg_data OUT NOCOPY VARCHAR2,
1314 p_fepv_rec IN fepv_rec_type,
1315 x_fepv_rec OUT NOCOPY fepv_rec_type) IS
1316 begin
1317 okc_fep_pvt.update_row(
1318 p_api_version,
1319 p_init_msg_list,
1320 x_return_status,
1321 x_msg_count,
1322 x_msg_data,
1323 p_fepv_rec,
1324 x_fepv_rec);
1325
1326 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1327 Update_Minor_Version(p_fepv_rec.dnz_chr_id);
1328 END IF;
1329 End update_func_exprs;
1330
1331 PROCEDURE update_func_exprs(
1332 p_api_version IN NUMBER,
1333 p_init_msg_list IN VARCHAR2 ,
1334 x_return_status OUT NOCOPY VARCHAR2,
1335 x_msg_count OUT NOCOPY NUMBER,
1336 x_msg_data OUT NOCOPY VARCHAR2,
1337 p_fepv_tbl IN fepv_tbl_type,
1338 x_fepv_tbl OUT NOCOPY fepv_tbl_type) IS
1339 begin
1340 okc_fep_pvt.update_row(
1341 p_api_version,
1342 p_init_msg_list,
1343 x_return_status,
1344 x_msg_count,
1345 x_msg_data,
1346 p_fepv_tbl,
1347 x_fepv_tbl);
1348 End update_func_exprs;
1349
1350 PROCEDURE delete_func_exprs(
1351 p_api_version IN NUMBER,
1352 p_init_msg_list IN VARCHAR2 ,
1353 x_return_status OUT NOCOPY VARCHAR2,
1354 x_msg_count OUT NOCOPY NUMBER,
1355 x_msg_data OUT NOCOPY VARCHAR2,
1356 p_fepv_rec IN fepv_rec_type) IS
1357 begin
1358 okc_fep_pvt.delete_row(
1359 p_api_version,
1360 p_init_msg_list,
1361 x_return_status,
1362 x_msg_count,
1363 x_msg_data,
1364 p_fepv_rec);
1365
1366 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1367 Update_Minor_Version(p_fepv_rec.dnz_chr_id);
1368 END IF;
1369 End delete_func_exprs;
1370
1371 PROCEDURE delete_func_exprs(
1372 p_api_version IN NUMBER,
1373 p_init_msg_list IN VARCHAR2 ,
1374 x_return_status OUT NOCOPY VARCHAR2,
1375 x_msg_count OUT NOCOPY NUMBER,
1376 x_msg_data OUT NOCOPY VARCHAR2,
1377 p_fepv_tbl IN fepv_tbl_type) IS
1378 begin
1379 okc_fep_pvt.delete_row(
1380 p_api_version,
1381 p_init_msg_list,
1382 x_return_status,
1383 x_msg_count,
1384 x_msg_data,
1385 p_fepv_tbl);
1386 End delete_func_exprs;
1387
1388 PROCEDURE validate_func_exprs(
1389 p_api_version IN NUMBER,
1390 p_init_msg_list IN VARCHAR2 ,
1391 x_return_status OUT NOCOPY VARCHAR2,
1392 x_msg_count OUT NOCOPY NUMBER,
1393 x_msg_data OUT NOCOPY VARCHAR2,
1394 p_fepv_rec IN fepv_rec_type) IS
1395 begin
1396 okc_fep_pvt.validate_row(
1397 p_api_version,
1398 p_init_msg_list,
1399 x_return_status,
1400 x_msg_count,
1401 x_msg_data,
1402 p_fepv_rec);
1403 End validate_func_exprs;
1404
1405 PROCEDURE validate_func_exprs(
1406 p_api_version IN NUMBER,
1407 p_init_msg_list IN VARCHAR2 ,
1408 x_return_status OUT NOCOPY VARCHAR2,
1409 x_msg_count OUT NOCOPY NUMBER,
1410 x_msg_data OUT NOCOPY VARCHAR2,
1411 p_fepv_tbl IN fepv_tbl_type) IS
1412 begin
1413 okc_fep_pvt.validate_row(
1414 p_api_version,
1415 p_init_msg_list,
1416 x_return_status,
1417 x_msg_count,
1418 x_msg_data,
1419 p_fepv_tbl);
1420 End validate_func_exprs;
1421
1422 PROCEDURE valid_condition_lines(
1423 p_cnh_id IN okc_condition_headers_b.id%TYPE,
1424 x_string OUT NOCOPY VARCHAR2,
1425 x_valid_flag OUT NOCOPY VARCHAR2)
1426 IS
1427
1428 CURSOR cnl_cur
1429 IS
1430 SELECT cnl.sortseq sortseq,
1431 cnl.cnl_type cnl_type,
1432 cnl.aae_id aae_id,
1433 cnl.pdf_id pdf_id,
1434 cnl.cnh_id cnh_id,
1435 cnl.left_parenthesis left_parenthesis,
1436 cnl.left_ctr_master_id left_ctr_master_id,
1437 cnl.left_counter_id left_counter_id,
1438 cnl.relational_operator relational_operator,
1439 cnl.right_ctr_master_id right_ctr_master_id,
1440 cnl.right_counter_id right_counter_id,
1441 cnl.right_operand right_operand,
1442 cnl.right_parenthesis right_parenthesis,
1443 cnl.logical_operator logical_operator
1444 FROM okc_condition_lines_b cnl
1445 WHERE cnl.cnh_id = p_cnh_id
1446 ORDER BY cnl.sortseq;
1447 cnl_rec cnl_cur%ROWTYPE;
1448
1449 CURSOR aae_cur( a IN NUMBER)
1450 IS
1451 SELECT name
1452 FROM okc_action_attributes_v
1453 WHERE id = a;
1454 aae_rec aae_cur%ROWTYPE;
1455
1456 CURSOR pdf_cur(b IN NUMBER)
1457 IS
1458 SELECT name
1459 FROM okc_process_defs_v
1460 WHERE id = b;
1461 pdf_rec pdf_cur%ROWTYPE;
1462
1463 CURSOR ctr_cur(x IN NUMBER)
1464 IS
1465 SELECT name
1466 FROM okx_counters_v
1467 WHERE counter_id = x;
1468 ctr_rec ctr_cur%ROWTYPE;
1469
1470 l_string VARCHAR2(32000):= 'select sysdate from dual where ';
1471 l_cursor NUMBER;
1472 left_value VARCHAR2(100);
1473 right_value VARCHAR2(100);
1474 l_valid_flag VARCHAR2(1);
1475 l_dummy INTEGER;
1476 line_found boolean := FALSE;
1477
1478 BEGIN
1479
1480 --build a string of expressions from the condition lines,
1481 --sorted by sequence number.
1482 -- assign constant values to the variable
1483 --parse all condition lines for the current record's header id and
1484 --return Y/N
1485
1486 ------------------------------------------
1487 ------------------------------------------
1488 l_cursor := DBMS_SQL.open_cursor;
1489 ------------------------------------------
1490 ------------------------------------------
1491
1492 OPEN cnl_cur;
1493 LOOP
1494 FETCH cnl_cur INTO cnl_rec;
1495 IF cnl_cur%NOTFOUND THEN
1496 IF line_found THEN
1497 -- open cursor and parse the string
1498 DBMS_SQL.parse (l_cursor,l_string,DBMS_SQL.native);
1499 DBMS_SQL.close_cursor(l_cursor);
1500 x_valid_flag := 'Y';
1501 EXIT;
1502 ELSE
1503 -- if there are no lines then set the flag to Y
1504 x_valid_flag := 'Y';
1505 EXIT;
1506 END IF;
1507 ELSE
1508 line_found := TRUE;
1509 IF cnl_rec.cnl_type IN ('GEX','CEX') THEN
1510 l_string := l_string||nvl(cnl_rec.left_parenthesis,' ')||
1511 ':left_value'||'='||
1512 ':right_value'||nvl(cnl_rec.right_parenthesis,' ')||
1513 nvl(cnl_rec.logical_operator,' ');
1514 ELSIF cnl_rec.cnl_type = 'FEX' THEN
1515 l_string := l_string||nvl(cnl_rec.left_parenthesis,' ')||
1516 ':left_value'||'='||
1517 ':right_value'||nvl(cnl_rec.right_parenthesis,' ')||
1518 nvl(cnl_rec.logical_operator,' ');
1519 END IF;
1520 END IF;
1521 END LOOP;
1522 CLOSE cnl_cur;
1523
1524 EXCEPTION
1525 WHEN OTHERS THEN
1526 DBMS_SQL.close_cursor(l_cursor);
1527 -- if the string is not valid then set the flag to N
1528 l_valid_flag := 'N';
1529 x_valid_flag := l_valid_flag;
1530 END valid_condition_lines;
1531
1532 END okc_conditions_pvt;