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