[Home] [Help]
PACKAGE BODY: APPS.OKL_VP_RULE_PVT
Source
1 PACKAGE BODY OKL_VP_RULE_PVT AS
2 /* $Header: OKLRRLGB.pls 120.7 2005/11/14 04:55:51 gboomina noship $ */
3 G_EXCEPTION_CANNOT_DELETE EXCEPTION;
4 G_CANNOT_DELETE_MASTER CONSTANT VARCHAR2(200) := 'OKC_CANNOT_DELETE_MASTER';
5 G_API_TYPE VARCHAR2(3) := 'PVT';
6
7 PROCEDURE create_rule_group(
8 p_api_version IN NUMBER,
9 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_msg_count OUT NOCOPY NUMBER,
12 x_msg_data OUT NOCOPY VARCHAR2,
13 p_rgpv_rec IN rgpv_rec_type,
14 x_rgpv_rec OUT NOCOPY rgpv_rec_type)
15 IS
16 l_api_name CONSTANT VARCHAR2(30) := 'create_rule_group';
17 l_cnt_rg NUMBER(9) := 0;
18 CURSOR csr_chr_rg_cnt IS
19 SELECT count('X')
20 FROM okc_rule_groups_b
21 WHERE rgd_code = p_rgpv_rec.rgd_code
22 AND (dnz_chr_id = p_rgpv_rec.chr_id and cle_id IS NULL)
23 AND id <> NVL(p_rgpv_rec.id,-1);
24
25 BEGIN
26 -- Not null Validation for Terms and Conditions
27 IF ((p_rgpv_rec.rgd_code = OKL_API.G_MISS_CHAR) OR (p_rgpv_rec.rgd_code IS NULL)) THEN
28 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_TERMS_AND_COND_REQUIRED');
29 x_return_status :=okl_api.g_ret_sts_error;
30 RAISE OKL_API.G_EXCEPTION_ERROR;
31
32 END IF;
33
34 -- Same Terms and Condition can be attched only 1 time to the contract.
35 IF p_rgpv_rec.chr_id IS NOT NULL THEN
36 OPEN csr_chr_rg_cnt;
37 FETCH csr_chr_rg_cnt INTO l_cnt_rg;
38 CLOSE csr_chr_rg_cnt;
39
40 IF l_cnt_rg <> 0 THEN
41 --set error message
42 OKC_API.set_message(
43 p_app_name => G_APP_NAME,
44 p_msg_name => 'OKL_DUP_TERMS_AND_COND',
45 p_token1 => 'RULEGROUP',
46 p_token1_value => p_rgpv_rec.rgd_code);
47
48 -- notify caller of an error
49 x_return_status := OKC_API.G_RET_STS_ERROR;
50 -- halt validation
51 RAISE OKL_API.G_EXCEPTION_ERROR;
52 END IF;
53 END IF;
54
55 OKL_OKC_MIGRATION_A_PVT.insert_row(
56 p_api_version => p_api_version,
57 p_init_msg_list => p_init_msg_list,
58 x_return_status => x_return_status,
59 x_msg_count => x_msg_count,
60 x_msg_data => x_msg_data,
61 p_rgpv_rec => p_rgpv_rec,
62 x_rgpv_rec => x_rgpv_rec);
63
64 -- Bug# 3477560
65 IF (p_rgpv_rec.dnz_chr_id is NOT NULL) AND
66 (p_rgpv_rec.dnz_chr_id <> OKL_API.G_MISS_NUM) THEN
67
68 --cascade edit status on to lines
69 okl_contract_status_pub.cascade_lease_status_edit
70 (p_api_version => p_api_version,
71 p_init_msg_list => p_init_msg_list,
72 x_return_status => x_return_status,
73 x_msg_count => x_msg_count,
74 x_msg_data => x_msg_data,
75 p_chr_id => p_rgpv_rec.dnz_chr_id);
76
77 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
78 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
79 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
80 raise OKL_API.G_EXCEPTION_ERROR;
81 End If;
82 END IF;
83
84 EXCEPTION
85 WHEN OKL_API.G_EXCEPTION_ERROR THEN
86 x_return_status := OKC_API.G_RET_STS_ERROR;
87 /* x_return_status := OKL_API.HANDLE_EXCEPTIONS
88 (p_api_name => l_api_name
89 ,p_pkg_name => G_PKG_NAME
90 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
91 ,x_msg_count => x_msg_count
92 ,x_msg_data => x_msg_data
93 ,p_api_type => '_PVT'
94 );
95 */
96 WHEN OTHERS THEN
97 -- store SQL error message on message stack
98 OKC_API.SET_MESSAGE(
99 p_app_name => G_APP_NAME,
100 p_msg_name => G_UNEXPECTED_ERROR,
101 p_token1 => G_SQLCODE_TOKEN,
102 p_token1_value => SQLCODE,
103 p_token2 => G_SQLERRM_TOKEN,
104 p_token2_value => SQLERRM);
105 -- notify caller of an error as UNEXPETED error
106 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
107 END create_rule_group;
108
109 PROCEDURE update_rule_group(
110 p_api_version IN NUMBER,
111 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
112 x_return_status OUT NOCOPY VARCHAR2,
113 x_msg_count OUT NOCOPY NUMBER,
114 x_msg_data OUT NOCOPY VARCHAR2,
115 p_rgpv_rec IN rgpv_rec_type,
116 x_rgpv_rec OUT NOCOPY rgpv_rec_type) IS
117
118 -- gboomina: Added for bug 4723775 to populate mandatory values
119 -- which are getting nullified in the pl/sql rosetta wrapper call
120 -- START of code changes
121 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
122 CURSOR csr_init_attr_column(p_id IN NUMBER) IS
123 SELECT chr_id,
124 dnz_chr_id,
125 created_by,
126 creation_date
127 FROM okc_rule_groups_b
128 WHERE ID = p_id ;
129
130 l_chr_id okc_k_headers_b.id%TYPE;
131 l_dnz_chr_id okc_k_headers_b.id%TYPE;
132 l_created_by okc_rule_groups_b.created_by%type;
133 l_creation_date okc_rule_groups_b.creation_date%type;
134 -- END of code changes for bug 4723775
135
136 BEGIN
137 -- gboomina: bug fix for populating the mandatory fields that are
138 -- accidentally being nullified in the pl/sql wrapper of okl_vp_rule_pub_w
139 -- since these fields are not updatable in the ui, derive the values from the
140 -- database only when the passed in id ( rgp_id ) is not null and is also not
141 -- equal to okl_api.g_miss_num
142 -- START of code changes for bug 4723775
143 IF(p_rgpv_rec.id IS NOT NULL AND p_rgpv_rec.id <> OKL_API.G_MISS_NUM)THEN
144 OPEN csr_init_attr_column(p_rgpv_rec.id);
145 FETCH csr_init_attr_column INTO l_chr_id, l_dnz_chr_id, l_created_by, l_creation_date;
146 CLOSE csr_init_attr_column;
147
148 l_rgpv_rec.chr_id := l_chr_id;
149 l_rgpv_rec.dnz_chr_id := l_dnz_chr_id;
150 l_rgpv_rec.created_by := l_created_by;
151 l_rgpv_rec.creation_date := l_creation_date;
152
153 OKL_OKC_MIGRATION_A_PVT.update_row(
154 p_api_version => p_api_version,
155 p_init_msg_list => p_init_msg_list,
156 x_return_status => x_return_status,
157 x_msg_count => x_msg_count,
158 x_msg_data => x_msg_data,
159 p_rgpv_rec => l_rgpv_rec,
160 x_rgpv_rec => x_rgpv_rec);
161
162 IF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
163 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
164 ELSIF x_return_status = OKL_API.G_RET_STS_ERROR THEN
165 RAISE OKL_API.G_EXCEPTION_ERROR;
166 END IF;
167 END IF;
168 -- END of code changes for bug 4723775
169
170 -- Bug# 3477560
171 IF (x_rgpv_rec.dnz_chr_id is NOT NULL) AND
172 (x_rgpv_rec.dnz_chr_id <> OKL_API.G_MISS_NUM) THEN
173 --cascade edit status on to lines
174 okl_contract_status_pub.cascade_lease_status_edit
175 (p_api_version => p_api_version,
176 p_init_msg_list => p_init_msg_list,
177 x_return_status => x_return_status,
178 x_msg_count => x_msg_count,
179 x_msg_data => x_msg_data,
180 p_chr_id => x_rgpv_rec.dnz_chr_id);
181
182 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
183 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
184 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
185 raise OKL_API.G_EXCEPTION_ERROR;
186 End If;
187 END IF;
188
189 EXCEPTION
190 WHEN OKL_API.G_EXCEPTION_ERROR THEN
191 x_return_status := OKC_API.G_RET_STS_ERROR;
192
193 WHEN OTHERS THEN
194 -- store SQL error message on message stack
195 OKC_API.SET_MESSAGE(
196 p_app_name => G_APP_NAME,
197 p_msg_name => G_UNEXPECTED_ERROR,
198 p_token1 => G_SQLCODE_TOKEN,
199 p_token1_value => SQLCODE,
200 p_token2 => G_SQLERRM_TOKEN,
201 p_token2_value => SQLERRM);
202 -- notify caller of an error as UNEXPETED error
203 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
204 END update_rule_group;
205
206 PROCEDURE delete_rule_group(
207 p_api_version IN NUMBER,
208 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
209 x_return_status OUT NOCOPY VARCHAR2,
210 x_msg_count OUT NOCOPY NUMBER,
211 x_msg_data OUT NOCOPY VARCHAR2,
212 p_rgpv_rec IN rgpv_rec_type) IS
213 i NUMBER;
214
215
216 -- Bug# 3477560
217 ln_chr_id OKC_RULE_GROUPS_B.DNZ_CHR_ID%TYPE;
218 CURSOR get_chr_id(p_rgd_id OKC_RULE_GROUPS_B.ID%TYPE)
219 IS
220 SELECT to_char(rgd.dnz_chr_id)
221 FROM okc_rule_groups_b rgd
222 WHERE rgd.id = p_rgd_id;
223
224 BEGIN
225
226 -- Bug# 3477560
227 OPEN get_chr_id(p_rgpv_rec.Id);
228 FETCH get_chr_id INTO ln_chr_id;
229 CLOSE get_chr_id;
230
231 OKL_OKC_MIGRATION_A_PVT.delete_row(
232 p_api_version => p_api_version,
233 p_init_msg_list => p_init_msg_list,
234 x_return_status => x_return_status,
235 x_msg_count => x_msg_count,
236 x_msg_data => x_msg_data,
237 p_rgpv_rec => p_rgpv_rec);
238
239 -- Bug# 3477560
240 IF (ln_chr_id is NOT NULL) AND
241 (ln_chr_id <> OKL_API.G_MISS_NUM) THEN
242
243 --cascade edit status on to lines
244 okl_contract_status_pub.cascade_lease_status_edit
245 (p_api_version => p_api_version,
246 p_init_msg_list => p_init_msg_list,
247 x_return_status => x_return_status,
248 x_msg_count => x_msg_count,
249 x_msg_data => x_msg_data,
250 p_chr_id => ln_chr_id);
251
252 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
253 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
254 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
255 raise OKL_API.G_EXCEPTION_ERROR;
256 End If;
257 END IF;
258
259 EXCEPTION
260 WHEN G_EXCEPTION_CANNOT_DELETE THEN
261 -- store SQL error message on message stack
262 OKC_API.SET_MESSAGE(
263 p_app_name => G_APP_NAME,
264 p_msg_name => G_CANNOT_DELETE_MASTER);
265 -- notify caller of an error
266 x_return_status := OKC_API.G_RET_STS_ERROR;
267
268 WHEN OKL_API.G_EXCEPTION_ERROR THEN
269 x_return_status := OKC_API.G_RET_STS_ERROR;
270
271 WHEN OTHERS THEN
272 -- store SQL error message on message stack
273 OKC_API.SET_MESSAGE(
274 p_app_name => G_APP_NAME,
275 p_msg_name => G_UNEXPECTED_ERROR,
276 p_token1 => G_SQLCODE_TOKEN,
277 p_token1_value => SQLCODE,
278 p_token2 => G_SQLERRM_TOKEN,
279 p_token2_value => SQLERRM);
280 -- notify caller of an error as UNEXPETED error
281 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
282
283 END delete_rule_group;
284
285 PROCEDURE process_vrs_rules(
286 p_api_version IN NUMBER,
287 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_msg_count OUT NOCOPY NUMBER,
290 x_msg_data OUT NOCOPY VARCHAR2,
291 p_chr_id IN NUMBER,
292 p_rgp_id IN NUMBER,
293 p_vrs_tbl IN vrs_tbl_type) IS
294
295 l_api_name VARCHAR2(30) := 'process_vrs_rules';
296 l_api_version CONSTANT NUMBER := 1.0;
297
298 i NUMBER;
299 l_rgp_id number;
300 l_cpl_id VARCHAR2(250) := null;
301 l_rle_code VARCHAR2(50) := null;
302
303 lp_vrs_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
304 lx_vrs_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
305
306 lp_vrs_rulv_rec Okl_Rule_Pub.rulv_rec_type;
307 lx_vrs_rulv_rec Okl_Rule_Pub.rulv_rec_type;
308
309 cursor RULE_GROUP_CSR(P_CHR_ID IN NUMBER, P_RGD_CODE IN VARCHAR2) is
310 SELECT ID
311 FROM OKC_RULE_GROUPS_B
312 WHERE CHR_ID = P_CHR_ID AND
313 DNZ_CHR_ID = P_CHR_ID AND
314 CLE_ID IS NULL AND
315 RGD_CODE = P_RGD_CODE;
316
317 cursor RULE_CSR(P_RUL_ID IN NUMBER) is
318 SELECT RULE_INFORMATION1
319 FROM OKC_RULES_B
320 WHERE ID = P_RUL_ID;
321
322 cursor RLE_CSR(P_CPL_ID IN VARCHAR2) is
323 SELECT RLE_CODE
324 FROM OKC_K_PARTY_ROLES_B
325 WHERE ID = TO_NUMBER(P_CPL_ID);
326
327 BEGIN
328
329 x_return_status := OKC_API.START_ACTIVITY(
330 p_api_name => l_api_name,
331 p_pkg_name => g_pkg_name,
332 p_init_msg_list => p_init_msg_list,
333 l_api_version => l_api_version,
334 p_api_version => p_api_version,
335 p_api_type => g_api_type,
336 x_return_status => x_return_status);
337
338 -- check if activity started successfully
339 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
340 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
341 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
342 RAISE OKC_API.G_EXCEPTION_ERROR;
343 END IF;
344
345 l_rgp_id := null;
346 open RULE_GROUP_CSR(p_chr_id,'VGLRS');
347 fetch RULE_GROUP_CSR into l_rgp_id;
348 close RULE_GROUP_CSR;
349
350 IF (l_rgp_id IS NULL) THEN
351
352 lp_vrs_rgpv_rec.id := NULL;
353 lp_vrs_rgpv_rec.rgd_code := 'VGLRS';
354 lp_vrs_rgpv_rec.dnz_chr_id := p_chr_id;
355 lp_vrs_rgpv_rec.chr_id := p_chr_id;
356 lp_vrs_rgpv_rec.rgp_type := 'KRG';
357
358 OKL_RULE_PUB.create_rule_group(
359 p_api_version => p_api_version,
360 p_init_msg_list => p_init_msg_list,
361 x_return_status => x_return_status,
362 x_msg_count => x_msg_count,
363 x_msg_data => x_msg_data,
364 p_rgpv_rec => lp_vrs_rgpv_rec,
365 x_rgpv_rec => lx_vrs_rgpv_rec);
366
367 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
368 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
369 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
370 RAISE OKC_API.G_EXCEPTION_ERROR;
371 END IF;
372
373 l_rgp_id := lx_vrs_rgpv_rec.id;
374
375 END IF;
376
377 If (p_vrs_tbl.COUNT > 0) Then
378
379 i := p_vrs_tbl.FIRST;
380
381 IF(p_rgp_id IS NOT NULL) THEN
382 l_rgp_id := p_rgp_id;
383 END IF;
384
385 LOOP
386
387 IF (p_vrs_tbl(i).rul_id IS NULL ) THEN
388
389 lp_vrs_rulv_rec.id := NULL;
390 lp_vrs_rulv_rec.rgp_id := l_rgp_id;
391 lp_vrs_rulv_rec.rule_information_category := 'VGLRSP';
392 lp_vrs_rulv_rec.dnz_chr_id := p_chr_id;
393 lp_vrs_rulv_rec.rule_information1 := p_vrs_tbl(i).rule_info1;
394 lp_vrs_rulv_rec.rule_information2 := p_vrs_tbl(i).rule_info2;
395 lp_vrs_rulv_rec.WARN_YN := 'N';
396 lp_vrs_rulv_rec.STD_TEMPLATE_YN := 'N';
397
398 OKL_RULE_PUB.create_rule(
399 p_api_version => p_api_version,
400 p_init_msg_list => p_init_msg_list,
401 x_return_status => x_return_status,
402 x_msg_count => x_msg_count,
403 x_msg_data => x_msg_data,
404 p_rulv_rec => lp_vrs_rulv_rec,
405 x_rulv_rec => lx_vrs_rulv_rec);
406
407 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
408 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
409 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
410 RAISE OKC_API.G_EXCEPTION_ERROR;
411 END IF;
412
413 lx_vrs_rulv_rec.id := lx_vrs_rulv_rec.id;
414
415 ELSIF (p_vrs_tbl(i).rul_id IS NOT NULL ) THEN
416
417 l_cpl_id := null;
418 OPEN RULE_CSR(p_vrs_tbl(i).rul_id);
419 FETCH RULE_CSR into l_cpl_id;
420 CLOSE RULE_CSR;
421
422 IF(l_cpl_id IS NOT NULL) THEN
423 l_rle_code := null;
424 OPEN RLE_CSR(l_cpl_id);
425 FETCH RLE_CSR into l_rle_code;
426 CLOSE RLE_CSR;
427 END IF;
428
429 IF(l_rle_code IS NOT NULL AND l_rle_code = 'LESSOR') THEN
430 IF(p_vrs_tbl(i).rle_code <> 'LESSOR' ) THEN
431 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
432 p_msg_name => 'OKL_INVALID_ROLE_UPDATE');
433 x_return_status := OKC_API.g_ret_sts_error;
434 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
435 END IF;
436 END IF;
437
438 lp_vrs_rulv_rec.id := p_vrs_tbl(i).rul_id;
439 lp_vrs_rulv_rec.rgp_id := l_rgp_id;
440 lp_vrs_rulv_rec.rule_information_category := 'VGLRSP';
441 lp_vrs_rulv_rec.dnz_chr_id := p_chr_id;
442 lp_vrs_rulv_rec.rule_information1 := p_vrs_tbl(i).rule_info1;
443 lp_vrs_rulv_rec.rule_information2 := p_vrs_tbl(i).rule_info2;
444 lp_vrs_rulv_rec.WARN_YN := 'N';
445 lp_vrs_rulv_rec.STD_TEMPLATE_YN := 'N';
446
447 OKL_RULE_PUB.update_rule(
448 p_api_version => p_api_version,
449 p_init_msg_list => p_init_msg_list,
450 x_return_status => x_return_status,
451 x_msg_count => x_msg_count,
452 x_msg_data => x_msg_data,
453 p_rulv_rec => lp_vrs_rulv_rec,
454 x_rulv_rec => lx_vrs_rulv_rec);
455
456 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
457 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
458 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
459 RAISE OKC_API.G_EXCEPTION_ERROR;
460 END IF;
461
462 END IF;
463
464
465 EXIT WHEN (i = p_vrs_tbl.LAST);
466 i := p_vrs_tbl.NEXT(i);
467 END LOOP;
468
469
470 END IF;
471
472 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
473
474 EXCEPTION
475 WHEN OKC_API.G_EXCEPTION_ERROR THEN
476 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
477 p_api_name => l_api_name,
478 p_pkg_name => g_pkg_name,
479 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
480 x_msg_count => x_msg_count,
481 x_msg_data => x_msg_data,
482 p_api_type => g_api_type);
483
484 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
485 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
486 p_api_name => l_api_name,
487 p_pkg_name => g_pkg_name,
488 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
489 x_msg_count => x_msg_count,
490 x_msg_data => x_msg_data,
491 p_api_type => g_api_type);
492
493 WHEN OTHERS THEN
494 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
495 p_api_name => l_api_name,
496 p_pkg_name => g_pkg_name,
497 p_exc_name => 'OTHERS',
498 x_msg_count => x_msg_count,
499 x_msg_data => x_msg_data,
500 p_api_type => g_api_type);
501 END;
502
503 PROCEDURE delete_vrs_rule(
504 p_api_version IN NUMBER,
505 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
506 x_return_status OUT NOCOPY VARCHAR2,
507 x_msg_count OUT NOCOPY NUMBER,
508 x_msg_data OUT NOCOPY VARCHAR2,
509 p_chr_id IN NUMBER,
510 p_rul_id IN NUMBER) IS
511
512 l_api_name VARCHAR2(30) := 'delete_vrs_rule';
513 l_api_version CONSTANT NUMBER := 1.0;
514
515 i NUMBER;
516 l_cpl_id VARCHAR2(250) := null;
517 l_rle_code VARCHAR2(50) := null;
518
519 lp_vrs_rulv_rec Okl_Rule_Pub.rulv_rec_type;
520
521 cursor RULE_CSR(P_RUL_ID IN NUMBER) is
522 SELECT RULE_INFORMATION1
523 FROM OKC_RULES_B
524 WHERE ID = P_RUL_ID;
525
526 cursor RLE_CSR(P_CPL_ID IN VARCHAR2) is
527 SELECT RLE_CODE
528 FROM OKC_K_PARTY_ROLES_B
529 WHERE ID = TO_NUMBER(P_CPL_ID);
530
531 BEGIN
532
533 x_return_status := OKC_API.START_ACTIVITY(
534 p_api_name => l_api_name,
535 p_pkg_name => g_pkg_name,
536 p_init_msg_list => p_init_msg_list,
537 l_api_version => l_api_version,
538 p_api_version => p_api_version,
539 p_api_type => g_api_type,
540 x_return_status => x_return_status);
541
542 -- check if activity started successfully
543 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
544 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
545 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
546 RAISE OKC_API.G_EXCEPTION_ERROR;
547 END IF;
548
549 l_cpl_id := null;
550 OPEN RULE_CSR(p_rul_id);
551 FETCH RULE_CSR into l_cpl_id;
552 CLOSE RULE_CSR;
553
554 IF(l_cpl_id is NOT NULL) THEN
555 l_rle_code := null;
556 OPEN RLE_CSR(l_cpl_id);
557 FETCH RLE_CSR into l_rle_code;
558 CLOSE RLE_CSR;
559 END IF;
560
561 IF(l_rle_code IS NOT NULL AND l_rle_code = 'LESSOR') THEN
562 x_return_status := OKC_API.g_ret_sts_error;
563 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
564 p_msg_name => 'OKL_INVALID_ROLE_DELETE');
565 RAISE OKC_API.G_EXCEPTION_ERROR;
566 END IF;
567
568 lp_vrs_rulv_rec.id := p_rul_id;
569
570 OKL_RULE_PUB.delete_rule(
571 p_api_version => p_api_version,
572 p_init_msg_list => p_init_msg_list,
573 x_return_status => x_return_status,
574 x_msg_count => x_msg_count,
575 x_msg_data => x_msg_data,
576 p_rulv_rec => lp_vrs_rulv_rec
577 );
578
579 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
580 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
581 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
582 RAISE OKC_API.G_EXCEPTION_ERROR;
583 END IF;
584
585
586 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
587
588 EXCEPTION
589 WHEN OKC_API.G_EXCEPTION_ERROR THEN
590 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
591 p_api_name => l_api_name,
592 p_pkg_name => g_pkg_name,
593 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
594 x_msg_count => x_msg_count,
595 x_msg_data => x_msg_data,
596 p_api_type => g_api_type);
597
598 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
599 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
600 p_api_name => l_api_name,
601 p_pkg_name => g_pkg_name,
602 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
603 x_msg_count => x_msg_count,
604 x_msg_data => x_msg_data,
605 p_api_type => g_api_type);
606
607 WHEN OTHERS THEN
608 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
609 p_api_name => l_api_name,
610 p_pkg_name => g_pkg_name,
611 p_exc_name => 'OTHERS',
612 x_msg_count => x_msg_count,
613 x_msg_data => x_msg_data,
614 p_api_type => g_api_type);
615 END;
616
617 PROCEDURE validate_vrs_percent(
618 p_api_version IN NUMBER,
619 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
620 x_return_status OUT NOCOPY VARCHAR2,
621 x_msg_count OUT NOCOPY NUMBER,
622 x_msg_data OUT NOCOPY VARCHAR2,
623 p_chr_id IN NUMBER
624 ) IS
625
626 l_api_name VARCHAR2(30) := 'validate_vrs_percent';
627 l_api_version CONSTANT NUMBER := 1.0;
628
629 l_percent NUMBER;
630
631 cursor RESI_PERCENT_CSR is
632 SELECT sum(to_number(nvl(RULE_INFORMATION2,0)))
633 FROM OKC_RULES_B rul,
634 okc_rule_groups_b rgp
635 WHERE rgp.ID = rul.rgp_id
636 AND rgp.dnz_chr_id = rul.dnz_chr_id
637 AND rgp.chr_id = p_chr_id;
638
639 BEGIN
640
641 x_return_status := OKC_API.START_ACTIVITY(
642 p_api_name => l_api_name,
643 p_pkg_name => g_pkg_name,
644 p_init_msg_list => p_init_msg_list,
645 l_api_version => l_api_version,
646 p_api_version => p_api_version,
647 p_api_type => g_api_type,
648 x_return_status => x_return_status);
649
650 -- check if activity started successfully
651 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
652 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
653 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
654 RAISE OKC_API.G_EXCEPTION_ERROR;
655 END IF;
656
657 l_percent := null;
658 OPEN RESI_PERCENT_CSR;
659 FETCH RESI_PERCENT_CSR into l_percent;
660 CLOSE RESI_PERCENT_CSR;
661
662 IF(l_percent IS NULL OR l_percent <> 100) THEN
663 x_return_status := OKC_API.g_ret_sts_error;
664 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
665 p_msg_name => 'OKL_VN_INCORRECT_RESIDUAL');
666 RAISE OKC_API.G_EXCEPTION_ERROR;
667 END IF;
668
669 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
670
671 EXCEPTION
672 WHEN OKC_API.G_EXCEPTION_ERROR THEN
673 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
674 p_api_name => l_api_name,
675 p_pkg_name => g_pkg_name,
676 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
677 x_msg_count => x_msg_count,
678 x_msg_data => x_msg_data,
679 p_api_type => g_api_type);
680
681 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
682 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
683 p_api_name => l_api_name,
684 p_pkg_name => g_pkg_name,
685 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
686 x_msg_count => x_msg_count,
687 x_msg_data => x_msg_data,
688 p_api_type => g_api_type);
689
690 WHEN OTHERS THEN
691 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
692 p_api_name => l_api_name,
693 p_pkg_name => g_pkg_name,
694 p_exc_name => 'OTHERS',
695 x_msg_count => x_msg_count,
696 x_msg_data => x_msg_data,
697 p_api_type => g_api_type);
698 END;
699
700 END OKL_VP_RULE_PVT;