[Home] [Help]
PACKAGE BODY: APPS.OKL_CPY_PDT_RULS_PVT
Source
1 PACKAGE BODY OKL_CPY_PDT_RULS_PVT AS
2 /* $Header: OKLRPCOB.pls 120.3 2006/09/25 13:24:19 dkagrawa noship $ */
3 --------------------------------------------------------------------------------
4 --Function to get rule template record
5 --------------------------------------------------------------------------------
6 FUNCTION get_rulv_rec (
7 p_rul_id IN NUMBER,
8 x_no_data_found OUT NOCOPY BOOLEAN
9 ) RETURN OKC_RULE_PUB.rulv_rec_type IS
10 CURSOR okc_rulv_csr (p_id IN NUMBER) IS
11 SELECT
12 ID,
13 OBJECT_VERSION_NUMBER,
14 SFWT_FLAG,
15 OBJECT1_ID1,
16 OBJECT2_ID1,
17 OBJECT3_ID1,
18 OBJECT1_ID2,
19 OBJECT2_ID2,
20 OBJECT3_ID2,
21 JTOT_OBJECT1_CODE,
22 JTOT_OBJECT2_CODE,
23 JTOT_OBJECT3_CODE,
24 DNZ_CHR_ID,
25 RGP_ID,
26 PRIORITY,
27 STD_TEMPLATE_YN,
28 COMMENTS,
29 WARN_YN,
30 ATTRIBUTE_CATEGORY,
31 ATTRIBUTE1,
32 ATTRIBUTE2,
33 ATTRIBUTE3,
34 ATTRIBUTE4,
35 ATTRIBUTE5,
36 ATTRIBUTE6,
37 ATTRIBUTE7,
38 ATTRIBUTE8,
39 ATTRIBUTE9,
40 ATTRIBUTE10,
41 ATTRIBUTE11,
42 ATTRIBUTE12,
43 ATTRIBUTE13,
44 ATTRIBUTE14,
45 ATTRIBUTE15,
46 CREATED_BY,
47 CREATION_DATE,
48 LAST_UPDATED_BY,
49 LAST_UPDATE_DATE,
50 LAST_UPDATE_LOGIN,
51 TEXT,
52 RULE_INFORMATION_CATEGORY,
53 RULE_INFORMATION1,
54 RULE_INFORMATION2,
55 RULE_INFORMATION3,
56 RULE_INFORMATION4,
57 RULE_INFORMATION5,
58 RULE_INFORMATION6,
59 RULE_INFORMATION7,
60 RULE_INFORMATION8,
61 RULE_INFORMATION9,
62 RULE_INFORMATION10,
63 RULE_INFORMATION11,
64 RULE_INFORMATION12,
65 RULE_INFORMATION13,
66 RULE_INFORMATION14,
67 RULE_INFORMATION15,
68 TEMPLATE_YN,
69 ans_set_jtot_object_code,
70 ans_set_jtot_object_id1,
71 ans_set_jtot_object_id2,
72 DISPLAY_SEQUENCE
73 FROM Okc_Rules_V
74 WHERE okc_rules_v.id = p_id;
75
76 l_rulv_rec OKC_RULE_PUB.rulv_rec_type;
77 --
78 l_proc varchar2(72) := g_pkg_name||'get_rec';
79 --
80 BEGIN
81 x_no_data_found := TRUE;
82 -- Get current database values
83 OPEN okc_rulv_csr (p_rul_id);
84 FETCH okc_rulv_csr INTO
85 l_rulv_rec.ID,
86 l_rulv_rec.OBJECT_VERSION_NUMBER,
87 l_rulv_rec.SFWT_FLAG,
88 l_rulv_rec.OBJECT1_ID1,
89 l_rulv_rec.OBJECT2_ID1,
90 l_rulv_rec.OBJECT3_ID1,
91 l_rulv_rec.OBJECT1_ID2,
92 l_rulv_rec.OBJECT2_ID2,
93 l_rulv_rec.OBJECT3_ID2,
94 l_rulv_rec.JTOT_OBJECT1_CODE,
95 l_rulv_rec.JTOT_OBJECT2_CODE,
96 l_rulv_rec.JTOT_OBJECT3_CODE,
97 l_rulv_rec.DNZ_CHR_ID,
98 l_rulv_rec.RGP_ID,
99 l_rulv_rec.PRIORITY,
100 l_rulv_rec.STD_TEMPLATE_YN,
101 l_rulv_rec.COMMENTS,
102 l_rulv_rec.WARN_YN,
103 l_rulv_rec.ATTRIBUTE_CATEGORY,
104 l_rulv_rec.ATTRIBUTE1,
105 l_rulv_rec.ATTRIBUTE2,
106 l_rulv_rec.ATTRIBUTE3,
107 l_rulv_rec.ATTRIBUTE4,
108 l_rulv_rec.ATTRIBUTE5,
109 l_rulv_rec.ATTRIBUTE6,
110 l_rulv_rec.ATTRIBUTE7,
111 l_rulv_rec.ATTRIBUTE8,
112 l_rulv_rec.ATTRIBUTE9,
113 l_rulv_rec.ATTRIBUTE10,
114 l_rulv_rec.ATTRIBUTE11,
115 l_rulv_rec.ATTRIBUTE12,
116 l_rulv_rec.ATTRIBUTE13,
117 l_rulv_rec.ATTRIBUTE14,
118 l_rulv_rec.ATTRIBUTE15,
119 l_rulv_rec.CREATED_BY,
120 l_rulv_rec.CREATION_DATE,
121 l_rulv_rec.LAST_UPDATED_BY,
122 l_rulv_rec.LAST_UPDATE_DATE,
123 l_rulv_rec.LAST_UPDATE_LOGIN,
124 l_rulv_rec.TEXT,
125 l_rulv_rec.RULE_INFORMATION_CATEGORY,
126 l_rulv_rec.RULE_INFORMATION1,
127 l_rulv_rec.RULE_INFORMATION2,
128 l_rulv_rec.RULE_INFORMATION3,
129 l_rulv_rec.RULE_INFORMATION4,
130 l_rulv_rec.RULE_INFORMATION5,
131 l_rulv_rec.RULE_INFORMATION6,
132 l_rulv_rec.RULE_INFORMATION7,
133 l_rulv_rec.RULE_INFORMATION8,
134 l_rulv_rec.RULE_INFORMATION9,
135 l_rulv_rec.RULE_INFORMATION10,
136 l_rulv_rec.RULE_INFORMATION11,
137 l_rulv_rec.RULE_INFORMATION12,
138 l_rulv_rec.RULE_INFORMATION13,
139 l_rulv_rec.RULE_INFORMATION14,
140 l_rulv_rec.RULE_INFORMATION15,
141 l_rulv_rec.TEMPLATE_YN,
142 l_rulv_rec.ans_set_jtot_object_code,
143 l_rulv_rec.ans_set_jtot_object_id1,
144 l_rulv_rec.ans_set_jtot_object_id2,
145 l_rulv_rec.DISPLAY_SEQUENCE ;
146 x_no_data_found := okc_rulv_csr%NOTFOUND;
147 CLOSE okc_rulv_csr;
148 RETURN(l_rulv_rec);
149 END get_rulv_rec;
150 --------------------------------------------------------------------------------
151 --local procedure to create selected option values
152 --------------------------------------------------------------------------------
153 Procedure create_slctd_popv(p_api_version IN NUMBER,
154 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
155 x_return_status OUT NOCOPY VARCHAR2,
156 x_msg_count OUT NOCOPY NUMBER,
157 x_msg_data OUT NOCOPY VARCHAR2,
158 p_khr_id IN NUMBER,
159 p_pov_id IN NUMBER
160 ) IS
161 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
162 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SLCTD_POPV';
163 l_api_version CONSTANT NUMBER := 1.0;
164
165 l_cspv_rec OKL_CONTRACT_PROD_OPTNS_PUB.cspv_rec_type;
166 l_cspv_rec_out OKL_CONTRACT_PROD_OPTNS_PUB.cspv_rec_type;
167 Begin
168 --call start activity to set savepoint
169 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
170 p_init_msg_list,
171 '_PVT',
172 x_return_status);
173 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
174 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
175 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
176 RAISE OKL_API.G_EXCEPTION_ERROR;
177 END IF;
178
179 --call selected options create api
180 l_cspv_rec.khr_id := p_khr_id;
181 l_cspv_rec.pov_id := p_pov_id;
182
183 OKL_CONTRACT_PROD_OPTNS_PUB.create_contract_option(
184 p_api_version => p_api_version,
185 p_init_msg_list => p_init_msg_list,
186 x_return_status => x_return_status,
187 x_msg_count => x_msg_count,
188 x_msg_data => x_msg_data,
189 p_cspv_rec => l_cspv_rec,
190 x_cspv_rec => l_cspv_rec_out);
191
192 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
193 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
194 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
195 RAISE OKL_API.G_EXCEPTION_ERROR;
196 END IF;
197
198 -- Bug# 3477560
199 --cascade edit status on to lines
200 okl_contract_status_pub.cascade_lease_status_edit
201 (p_api_version => p_api_version,
202 p_init_msg_list => p_init_msg_list,
203 x_return_status => x_return_status,
204 x_msg_count => x_msg_count,
205 x_msg_data => x_msg_data,
206 p_chr_id => p_khr_id);
207
208 If x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR Then
209 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
210 Elsif x_return_status = OKL_API.G_RET_STS_ERROR Then
211 raise OKL_API.G_EXCEPTION_ERROR;
212 End If;
213
214 --Call end Activity
215 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
216
217 EXCEPTION
218 When OKL_API.G_EXCEPTION_ERROR Then
219 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
220 (
221 l_api_name,
222 G_PKG_NAME,
223 'OKL_API.G_RET_STS_ERROR',
224 x_msg_count,
225 x_msg_data,
226 '_PVT'
227 );
228 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
229 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
230 (
231 l_api_name,
232 G_PKG_NAME,
233 'OKL_API.G_RET_STS_UNEXP_ERROR',
234 x_msg_count,
235 x_msg_data,
236 '_PVT'
237 );
238 WHEN OTHERS THEN
239 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
240 (
241 l_api_name,
242 G_PKG_NAME,
243 'OTHERS',
244 x_msg_count,
245 x_msg_data,
246 '_PVT'
247 );
248 END create_slctd_popv;
249 --------------------------------------------------------------------------------
250 --local api to copy the rule to contract header
251 --------------------------------------------------------------------------------
252 procedure chk_and_cpy_rul_hdr(p_api_version IN NUMBER,
253 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
254 x_return_status OUT NOCOPY VARCHAR2,
255 x_msg_count OUT NOCOPY NUMBER,
256 x_msg_data OUT NOCOPY VARCHAR2,
257 p_khr_id IN NUMBER,
258 p_rulv_rec IN OKC_RULE_PUB.rulv_rec_type,
259 p_rgd_code IN OKL_OPT_RULES.rgr_rgd_code%TYPE,
260 p_rdf_code IN OKL_OPT_RULES.rgr_rdf_code%TYPE,
261 p_copy_or_enter_flag IN OKL_OPV_RULES.copy_or_enter_flag%TYPE) IS
262
263 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
264 l_api_name CONSTANT VARCHAR2(30) := 'CHK_AND_CPY_RUL_HDR';
265 l_api_version CONSTANT NUMBER := 1.0;
266
267 --cursor to ceck if rule group is already attched at the k hdr level
268 CURSOR chk_rgp_csr (p_khr_id IN NUMBER,
269 p_rgd_code IN VARCHAR2) IS
270 select rgp.id
271 from okc_rule_groups_b rgp
272 where rgp.rgd_code = p_rgd_code
273 and rgp.chr_id = p_khr_id;
274
275 l_rgp_id OKC_RULE_GROUPS_B.ID%TYPE default Null;
276
277 --cursor to check if rule exists at the k_hdr level
278 CURSOR chk_rul_csr (p_khr_id IN NUMBER,
279 p_rgp_id IN NUMBER,
280 p_rdf_code IN VARCHAR2) IS
281 select rul.id
282 from okc_rules_b rul
283 where rul.rgp_id = p_rgp_id
284 and rul.dnz_chr_id = p_khr_id
285 and rul.rule_information_category = p_rdf_code;
286
287 l_rul_id OKC_RULES_B.ID%TYPE default Null;
288
289 l_rgpv_rec OKC_RULE_PUB.rgpv_rec_type;
290 l_rgpv_rec_out OKC_RULE_PUB.rgpv_rec_type;
291 l_rulv_rec OKC_RULE_PUB.rulv_rec_type;
292 l_rulv_rec_out OKC_RULE_PUB.rulv_rec_type;
293
294 Begin
295 --call start activity to set savepoint
296 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
297 p_init_msg_list,
298 '_PVT',
299 x_return_status);
300 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
301 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
302 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
303 RAISE OKL_API.G_EXCEPTION_ERROR;
304 END IF;
305 --check if rule group is already there on contract header
306 l_rgp_id := Null;
307 Open chk_rgp_csr (p_khr_id => p_khr_id,
308 p_rgd_code => p_rgd_code);
309 Fetch chk_rgp_csr into l_rgp_id;
310 If chk_rgp_csr%NOTFOUND THEN
311 --create rgp
312 --initialize rgpv_rec
313 l_rgpv_rec.rgd_code := p_rgd_code;
314 l_rgpv_rec.chr_id := p_khr_id;
315 l_rgpv_rec.dnz_chr_id := p_khr_id;
316 l_rgpv_rec.rgp_type := 'KRG';
317 okc_rule_pub.create_rule_group(
318 p_api_version => p_api_version,
319 p_init_msg_list => p_init_msg_list,
320 x_return_status => x_return_status,
321 x_msg_count => x_msg_count,
322 x_msg_data => x_msg_count,
323 p_rgpv_rec => l_rgpv_rec,
324 x_rgpv_rec => l_rgpv_rec_out);
325
326 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
327 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
328 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
329 RAISE OKL_API.G_EXCEPTION_ERROR;
330 END IF;
331
332 --create rule
333 l_rulv_rec := p_rulv_rec;
334
335 l_rulv_rec.id := OKL_API.G_MISS_NUM; -- so that new id could be generated
336 l_rulv_rec.rgp_id := l_rgpv_rec_out.id;
337 l_rulv_rec.template_yn := Null; --this rule will no longer be template
338 l_rulv_rec.dnz_chr_id := p_khr_id;
339 l_rulv_rec.std_template_yn := 'N';
340 okc_rule_pub.create_rule(
341 p_api_version => p_api_version,
342 p_init_msg_list => p_init_msg_list,
343 x_return_status => x_return_status,
344 x_msg_count => x_msg_count,
345 x_msg_data => x_msg_data,
346 p_rulv_rec => l_rulv_rec,
347 x_rulv_rec => l_rulv_rec_out);
348
349 --dbms_output.put_line('rule created'||to_char(l_rulv_rec_out.id));
350
351 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
352 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
353 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
354 RAISE OKL_API.G_EXCEPTION_ERROR;
355 END IF;
356
357 Else
358 --do not copy the rule group
359 --check if rule exists
360 l_rul_id := Null;
361 Open chk_rul_csr ( p_khr_id => p_khr_id,
362 p_rgp_id => l_rgp_id,
363 p_rdf_code => p_rdf_code);
364 Fetch chk_rul_csr into l_rul_id;
365 If chk_rul_csr%NOTFOUND Then
366 --create rule record
367 --create rule
368 l_rulv_rec := p_rulv_rec;
369
370 l_rulv_rec.id := OKL_API.G_MISS_NUM; -- so that new id could be generated
371 l_rulv_rec.rgp_id := l_rgp_id;
372 l_rulv_rec.template_yn := Null; --this rule will no longer be template
373 l_rulv_rec.dnz_chr_id := p_khr_id;
374 l_rulv_rec.std_template_yn := 'N';
375
376 okc_rule_pub.create_rule(
377 p_api_version => p_api_version,
378 p_init_msg_list => p_init_msg_list,
379 x_return_status => x_return_status,
380 x_msg_count => x_msg_count,
381 x_msg_data => x_msg_data,
382 p_rulv_rec => l_rulv_rec,
383 x_rulv_rec => l_rulv_rec_out);
384
385 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
386 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
387 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
388 RAISE OKL_API.G_EXCEPTION_ERROR;
389 END IF;
390
391 Else
392 --rule record exists
393 -- do not create rule record
394 null;
395 End If;
396 Close chk_rul_csr;
397 End If;
398 close chk_rgp_csr;
399
400 --Call end Activity
401 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
402
403 EXCEPTION
404 When OKL_API.G_EXCEPTION_ERROR Then
405 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
406 (
407 l_api_name,
408 G_PKG_NAME,
409 'OKL_API.G_RET_STS_ERROR',
410 x_msg_count,
411 x_msg_data,
412 '_PVT'
413 );
414 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
415 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
416 (
417 l_api_name,
418 G_PKG_NAME,
419 'OKL_API.G_RET_STS_UNEXP_ERROR',
420 x_msg_count,
421 x_msg_data,
422 '_PVT'
423 );
424 WHEN OTHERS THEN
425 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
426 (
427 l_api_name,
428 G_PKG_NAME,
429 'OTHERS',
430 x_msg_count,
431 x_msg_data,
432 '_PVT'
433 );
434 End chk_and_cpy_rul_hdr;
435 --------------------------------------------------------------------------------
436 --local api to copy the rule to contract line
437 --------------------------------------------------------------------------------
438 procedure chk_and_cpy_rul_line(p_api_version IN NUMBER,
439 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
440 x_return_status OUT NOCOPY VARCHAR2,
441 x_msg_count OUT NOCOPY NUMBER,
442 x_msg_data OUT NOCOPY VARCHAR2,
443 p_khr_id IN NUMBER,
444 p_rulv_rec IN OKC_RULE_PUB.rulv_rec_type,
445 p_rgd_code IN OKL_OPT_RULES.rgr_rgd_code%TYPE,
446 p_rdf_code IN OKL_OPT_RULES.rgr_rdf_code%TYPE,
447 p_lse_id IN OKL_OPT_RULES.lrg_lse_id%TYPE,
448 p_copy_or_enter_flag IN OKL_OPV_RULES.copy_or_enter_flag%TYPE) IS
449
450 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
451 l_api_name CONSTANT VARCHAR2(30) := 'CHK_AND_CPY_RUL_LINE';
452 l_api_version CONSTANT NUMBER := 1.0;
453
454 --cursor to check if there is a lse_id line for k
455 CURSOR chk_lse_csr (p_khr_id IN NUMBER,
456 p_lse_id IN NUMBER) IS
457 select cle.id
458 from okc_k_lines_b cle,
459 okc_statuses_b sts
460 where cle.dnz_chr_id = p_khr_id
461 and cle.lse_id = p_lse_id
462 and cle.sts_code = sts.code
463 and sts.code not in ('ACTIVE','HOLD','EXPIRED','TERMINATED','CANCELED')
464 and nvl(cle.end_date,sysdate+1) > sysdate;
465
466 l_kle_id OKC_K_LINES_B.ID%TYPE;
467
468 --cursor to ceck if rule group is already attached at the k line level
469 CURSOR chk_rgp_csr (p_khr_id IN NUMBER,
470 p_kle_id IN NUMBER,
471 p_rgd_code IN VARCHAR2) IS
472 select rgp.id
473 from okc_rule_groups_b rgp
474 where rgp.rgd_code = p_rgd_code
475 and rgp.dnz_chr_id = p_khr_id
476 and rgp.cle_id = p_kle_id;
477
478 l_rgp_id OKC_RULE_GROUPS_B.ID%TYPE default Null;
479
480 --cursor to check if rule exists at the k_line level
481 CURSOR chk_rul_csr (p_khr_id IN NUMBER,
482 p_rgp_id IN NUMBER,
483 p_rdf_code IN VARCHAR2) IS
484 select rul.id
485 from okc_rules_b rul
486 where rul.rgp_id = p_rgp_id
487 and rul.dnz_chr_id = p_khr_id
488 and rul.rule_information_category = p_rdf_code;
489
490 l_rul_id OKC_RULES_B.ID%TYPE default Null;
491
492 l_rgpv_rec OKC_RULE_PUB.rgpv_rec_type;
493 l_rgpv_rec_out OKC_RULE_PUB.rgpv_rec_type;
494
495 l_rulv_rec OKC_RULE_PUB.rulv_rec_type;
496 l_rulv_rec_out OKC_RULE_PUB.rulv_rec_type;
497
498 Begin
499 --call start activity to set savepoint
500 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
501 p_init_msg_list,
502 '_PVT',
503 x_return_status);
504 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
505 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
506 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
507 RAISE OKL_API.G_EXCEPTION_ERROR;
508 END IF;
509 --check if eligble to modify line of lse lse_id exists on k hdt
510 l_kle_id := Null;
511 Open chk_lse_csr (p_khr_id => p_khr_id,
512 p_lse_id => p_lse_id);
513 Fetch chk_lse_csr into l_kle_id;
514 If chk_lse_csr%NOTFOUND Then
515 --line of lse type does not exist on contract do nothing
516 Null;
517 Else
518 --check if rule group is already there on contract header
519 l_rgp_id := Null;
520 Open chk_rgp_csr (p_khr_id => p_khr_id,
521 p_kle_id => l_kle_id,
522 p_rgd_code => p_rgd_code);
523 Fetch chk_rgp_csr into l_rgp_id;
524 If chk_rgp_csr%NOTFOUND THEN
525 --create rgp
526 --initialize rgpv_rec
527 l_rgpv_rec.rgd_code := p_rgd_code;
528 l_rgpv_rec.cle_id := l_kle_id;
529 l_rgpv_rec.dnz_chr_id := p_khr_id;
530 l_rgpv_rec.rgp_type := 'KRG';
531
532 okc_rule_pub.create_rule_group(
533 p_api_version => p_api_version,
534 p_init_msg_list => p_init_msg_list,
535 x_return_status => x_return_status,
536 x_msg_count => x_msg_count,
537 x_msg_data => x_msg_count,
538 p_rgpv_rec => l_rgpv_rec,
539 x_rgpv_rec => l_rgpv_rec_out);
540
541 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
542 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
543 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
544 RAISE OKL_API.G_EXCEPTION_ERROR;
545 END IF;
546
547 --create rule
548 l_rulv_rec := p_rulv_rec;
549
550 l_rulv_rec.id := OKL_API.G_MISS_NUM; -- so that new id could be generated
551 l_rulv_rec.rgp_id := l_rgpv_rec_out.id;
552 l_rulv_rec.template_yn := Null; --this rule will no longer be template
553 l_rulv_rec.dnz_chr_id := p_khr_id;
554 l_rulv_rec.std_template_yn := 'N';
555
556 okc_rule_pub.create_rule(
557 p_api_version => p_api_version,
558 p_init_msg_list => p_init_msg_list,
559 x_return_status => x_return_status,
560 x_msg_count => x_msg_count,
561 x_msg_data => x_msg_data,
562 p_rulv_rec => l_rulv_rec,
563 x_rulv_rec => l_rulv_rec_out);
564
565 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
566 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
567 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
568 RAISE OKL_API.G_EXCEPTION_ERROR;
569 END IF;
570
571 Else
572 --do not copy the rule group
573 --check if rule exists
574 l_rul_id := Null;
575 Open chk_rul_csr ( p_khr_id => p_khr_id,
576 p_rgp_id => l_rgp_id,
577 p_rdf_code => p_rdf_code);
578 Fetch chk_rul_csr into l_rul_id;
579 If chk_rul_csr%NOTFOUND Then
580 --create rule record
581 --create rule
582 l_rulv_rec := p_rulv_rec;
583
584 l_rulv_rec.id := OKL_API.G_MISS_NUM; -- so that new id could be generated
585 l_rulv_rec.rgp_id := l_rgp_id;
586 l_rulv_rec.template_yn := Null; --this rule will no longer be template
587 l_rulv_rec.dnz_chr_id := p_khr_id;
588 l_rulv_rec.std_template_yn := 'N';
589
590 okc_rule_pub.create_rule(
591 p_api_version => p_api_version,
592 p_init_msg_list => p_init_msg_list,
593 x_return_status => x_return_status,
594 x_msg_count => x_msg_count,
595 x_msg_data => x_msg_data,
596 p_rulv_rec => l_rulv_rec,
597 x_rulv_rec => l_rulv_rec_out);
598
599 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
600 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
601 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
602 RAISE OKL_API.G_EXCEPTION_ERROR;
603 END IF;
604
605 Else
606 --rule record exists
607 -- do not create rule record
608 null;
609 End If;
610 Close chk_rul_csr;
611 End If;
612 close chk_rgp_csr;
613 End If;
614 Close chk_lse_csr;
615 --Call end Activity
616 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
617
618 EXCEPTION
619 When OKL_API.G_EXCEPTION_ERROR Then
620 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
621 (
622 l_api_name,
623 G_PKG_NAME,
624 'OKL_API.G_RET_STS_ERROR',
625 x_msg_count,
626 x_msg_data,
627 '_PVT'
628 );
629 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
630 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
631 (
632 l_api_name,
633 G_PKG_NAME,
634 'OKL_API.G_RET_STS_UNEXP_ERROR',
635 x_msg_count,
636 x_msg_data,
637 '_PVT'
638 );
639 WHEN OTHERS THEN
640 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
641 (
642 l_api_name,
643 G_PKG_NAME,
644 'OTHERS',
645 x_msg_count,
646 x_msg_data,
647 '_PVT'
648 );
649 End chk_and_cpy_rul_line;
650 --------------------------------------------------------------------------------
651 Procedure Copy_Product_Rules(p_api_version IN NUMBER,
652 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
653 x_return_status OUT NOCOPY VARCHAR2,
654 x_msg_count OUT NOCOPY NUMBER,
655 x_msg_data OUT NOCOPY VARCHAR2,
656 p_khr_id IN NUMBER,
657 p_pov_id IN NUMBER) is
658
659 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
660 l_api_name CONSTANT VARCHAR2(30) := 'COPY_PRODUCT_RULES';
661 l_api_version CONSTANT NUMBER := 1.0;
662
663 --cursor to fetch values required from contract header
664 CURSOR l_khr_csr(p_khr_id IN NUMBER) is
665 SELECT chr.scs_code,
666 chr.authoring_org_id,
667 chr.buy_or_sell,
668 chr.inv_organization_id,
669 chr.start_date,
670 chr.end_date,
671 chr.sts_code,
672 khr.pdt_id
673 FROM OKL_K_HEADERS khr,
674 OKC_K_HEADERS_B chr
675 WHERE khr.id = chr.id
676 AND chr.id = p_khr_id;
677
678 l_khr_rec l_khr_csr%ROWTYPE;
679
680 --cursor to get selected product options
681 CURSOR l_slctd_opt_csr (p_khr_id IN NUMBER, p_pov_id IN NUMBER) is
682 SELECT ID,
683 POV_ID, --required
684 KHR_ID -- fk to okc_k_headers_b.id
685 FROM OKL_SLCTD_OPTNS
686 WHERE KHR_ID = p_khr_id
687 and POV_ID = p_pov_id;
688
689 l_slctd_opt_rec l_slctd_opt_csr%ROWTYPE;
690
691 --cursor to get product option values
692 CURSOR l_pdt_opt_vals_csr (p_pov_id IN NUMBER) is
693 SELECT pov.OVE_ID, --required fk to okl_opt_values (okl_opv_rules_v.ove_id)
694 pov.ID,
695 pov.PON_ID,
696 pon.opt_id --product option id fk okl_pdt_opts
697 FROM OKL_PDT_OPT_VALS pov,
698 OKL_PDT_OPTS pon
699 WHERE pov.ID = p_pov_id --(p_pov id will be pov id fetched in last csr)
700 AND nvl(pov.FROM_DATE,sysdate) <= sysdate
701 AND nvl(pov.TO_DATE,sysdate+1) > sysdate
702 AND pon.id = pov.pon_id
703 AND nvl(pon.FROM_DATE,sysdate) <= sysdate
704 AND nvl(pon.TO_DATE,sysdate+1) > sysdate;
705
706
707 l_pdt_opt_vals_rec l_pdt_opt_vals_csr%ROWTYPE;
708
709
710 --cursor to fetch option value rules
711 CURSOR l_opv_ruls_csr (p_ove_id IN NUMBER) is
712 SELECT ovd.OVE_ID, -- fk to okl_opt_values (okl_opv_rules_v.ove_id)
713 ovd.ID,
714 ovd.ORL_ID, --required fk to okl_opt_ruls
715 ovd.CONTEXT_INTENT, --should be same as khr intent
716 ovd.COPY_OR_ENTER_FLAG, --if it 'CPY' copy to k and do not allow modfn, else sllow mdfn
717 ovd.CONTEXT_INV_ORG, --should be same as khr inv org
718 ovd.CONTEXT_ORG, --should be same as contract org_id
719 ovd.CONTEXT_ASSET_BOOK, --what chk on this??
720 ovd.INDIVIDUAL_INSTRUCTIONS
721 FROM OKL_OPV_RULES ovd,
722 OKL_OPT_VALUES ove
723 WHERE ovd.ove_id = ove.id
724 AND nvl(ove.FROM_DATE,sysdate) <= sysdate
725 AND nvl(ove.TO_DATE,sysdate+1) > sysdate
726 AND ove.ID = p_ove_id; --(p_ove id will be ove id fetched in last csr)
727
728 l_opv_ruls_rec l_opv_ruls_csr%ROWTYPE;
729
730 --cursor to get rule id from option value rule templates (okl_ovd_rul_tmls)
731 CURSOR l_ovd_rul_tmls_csr (p_ovd_id IN NUMBER) is
732 SELECT RUL_ID, --required fk to okc_rules_v.id
733 OVD_ID --fk to okl_opv_rules.id
734 FROM OKL_OVD_RUL_TMLS
735 WHERE OVD_ID = p_ovd_id; --(p_ovd_id will be id fetched in the last cursor)
736
737 l_ovd_rul_tmls_rec l_ovd_rul_tmls_csr%ROWTYPE;
738
739 --cursor to see if the option value rule is meant for 'LEASE' subclass
740 -- line styles and rgd-rdf codes
741
742 --1.--effectivity check for header level rules
743 CURSOR l_opt_ruls_khr_csr(p_opt_id IN NUMBER,
744 p_rdf_code IN VARCHAR2,
745 p_scs_code IN VARCHAR2) is
746 SELECT orl.SRD_ID_FOR,
747 orl.LRG_SRD_ID,
748 orl.LRG_LSE_ID,
749 orl.RGR_RGD_CODE,
750 orl.RGR_RDF_CODE
751 FROM OKL_OPT_RULES orl,
752 OKC_SUBCLASS_RG_DEFS srd
753 WHERE srd.id = orl.SRD_ID_FOR
754 AND srd.rgd_code = orl.rgr_rgd_code
755 AND srd.scs_code = p_scs_code
756 AND nvl(srd.start_date,sysdate) <= sysdate
757 AND nvl(srd.end_date,sysdate+1) > sysdate
758 AND orl.rgr_rdf_code = p_rdf_code
759 AND orl.lrg_srd_id is null
760 AND orl.lrg_lse_id is null
761 AND orl.opt_id = p_opt_id; --(p_orl_id is ovd.ORL_ID fetched in l_opv_ruls_csr)
762
763
764 l_opt_ruls_khr_rec l_opt_ruls_khr_csr%ROWTYPE;
765
766 --2.--effecitivity check for lse level rules
767 CURSOR l_opt_ruls_kle_csr(p_opt_id IN NUMBER,
768 p_rdf_code IN VARCHAR2,
769 p_scs_code IN VARCHAR2) is
770 SELECT orl.SRD_ID_FOR,
771 orl.LRG_SRD_ID,
772 orl.LRG_LSE_ID,
773 orl.RGR_RGD_CODE,
774 orl.RGR_RDF_CODE
775 FROM OKL_OPT_RULES orl,
776 OKC_SUBCLASS_RG_DEFS srd,
777 OKC_LSE_RULE_GROUPS lrg
778 WHERE srd.id = lrg.SRD_ID
779 AND srd.rgd_code = orl.rgr_rgd_code
780 AND srd.scs_code = p_scs_code
781 AND nvl(srd.start_date,sysdate) <= sysdate
782 AND nvl(srd.end_date,sysdate+1) > sysdate
783 AND lrg.lse_id = orl.lrg_lse_id
784 AND lrg.srd_id = orl.lrg_srd_id
785 AND orl.rgr_rdf_code = p_rdf_code
786 AND orl.srd_id_for is null
787 AND orl.opt_id = p_opt_id; --(p_orl_id is ovd.ORL_ID fetched in l_opv_ruls_csr)
788
789
790 l_opt_ruls_kle_rec l_opt_ruls_kle_csr%ROWTYPE;
791
792 l_rulv_rec OKC_RULE_PUB.rulv_rec_type;
793 l_no_data_found Boolean Default TRUE;
794
795 BEGIN
796 --call start activity to set savepoint
797 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
798 p_init_msg_list,
799 '_PVT',
800 x_return_status);
801 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
802 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
803 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
804 RAISE OKL_API.G_EXCEPTION_ERROR;
805 END IF;
806
807 Open l_khr_csr(p_khr_id => p_khr_id);
808 Fetch l_khr_csr into l_khr_rec;
809 If l_khr_csr%NOTFOUND Then
810 --contract not found
811 Null;
812 Else
813 --check fo sts code
814 --if not booked and in any of te terminated stages get selected options
815
816 Open l_slctd_opt_csr(p_khr_id => p_khr_id, p_pov_id => p_pov_id);
817 Fetch l_slctd_opt_csr into l_slctd_opt_rec;
818 If l_slctd_opt_csr%NOTFOUND Then
819 --create the selected option value
820 create_slctd_popv(p_api_version => p_api_version,
821 p_init_msg_list => p_init_msg_list,
822 x_return_status => x_return_status,
823 x_msg_count => x_msg_count,
824 x_msg_data => x_msg_data,
825 p_khr_id => p_khr_id,
826 p_pov_id => p_pov_id
827 );
828 --dbms_output.put_line('After creating the selected product option :'||x_return_status);
829 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
830 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
831 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
832 RAISE OKL_API.G_EXCEPTION_ERROR;
833 END IF;
834 End If;
835 Close l_slctd_opt_csr;
836
837 --get product option values
838 OPEN l_pdt_opt_vals_csr (p_pov_id => p_pov_id);
839 Loop
840 Fetch l_pdt_opt_vals_csr into l_pdt_opt_vals_rec;
841 Exit when l_pdt_opt_vals_csr%NOTFOUND;
842 --get option value rules
843 OPEN l_opv_ruls_csr (p_ove_id => l_pdt_opt_vals_rec.ove_id);
844 Loop
845 Fetch l_opv_ruls_csr into l_opv_ruls_rec;
846 Exit when l_opv_ruls_csr%NOTFOUND;
847 If nvl(l_opv_ruls_rec.context_intent,'XX') <> nvl(l_khr_rec.buy_or_sell,'ZZ') OR
848 nvl(l_opv_ruls_rec.context_org,-9999) <> nvl(l_khr_rec.authoring_org_id,-6666) OR
849 nvl(l_opv_ruls_rec.context_inv_org,-9999) <> nvl(l_khr_rec.inv_organization_id,-6666) Then
850 --dbms_output.put_line('Intent :'||l_opv_ruls_rec.context_intent);
851 --dbms_output.put_line('Org :'||to_char(l_opv_ruls_rec.context_org));
852 --dbms_output.put_line('Org :'||to_char(l_opv_ruls_rec.context_inv_org));
853 --dbms_output.put_line('Not copying because unable to match context');
854 --Exit;
855 NULL;
856 Else
857 --open the rule templates csr
858 --dbms_output.put_line('copying matched context');
859 --dbms_output.put_line('Intent :'||l_opv_ruls_rec.context_intent);
860 --dbms_output.put_line('Org :'||to_char(l_opv_ruls_rec.context_org));
861 --dbms_output.put_line('Org :'||to_char(l_opv_ruls_rec.context_inv_org));
862 OPEN l_ovd_rul_tmls_csr(p_ovd_id => l_opv_ruls_rec.ID);
863 Loop
864 Fetch l_ovd_rul_tmls_csr into l_ovd_rul_tmls_rec;
865 Exit when l_ovd_rul_tmls_csr%NOTFOUND;
866 l_rulv_rec := get_rulv_rec(p_rul_id => l_ovd_rul_tmls_rec.rul_id,
867 x_no_data_found => l_no_data_found);
868
869 IF l_no_data_found Then
870 --no rule template found to copy
871 null;
872 --dbms_output.put_line('No Template Found');
873 --should we raise an error here
874 ELSE
875 --check for effecitvity at header level
876 --dbms_output.put_line('product option value '||to_char(l_pdt_opt_vals_rec.opt_id));
877 --dbms_output.put_line('product rule code '||l_rulv_rec.rule_information_category);
878 --dbms_output.put_line('product scs code '||l_khr_rec.scs_code);
879 Open l_opt_ruls_khr_csr(p_opt_id => l_pdt_opt_vals_rec.opt_id,
880 p_rdf_code => l_rulv_rec.rule_information_category,
881 p_scs_code => l_khr_rec.scs_code);
882 Loop
883 Fetch l_opt_ruls_khr_csr into l_opt_ruls_khr_rec;
884 --check for applicability of the rule
885 Exit when l_opt_ruls_khr_csr%NOTFOUND;
886 --Else
887 --copy the rule group and rule at header level
888 --do not copy if the rule group exists
889 --do not copy if the rule group exists for the rule group
890 --dbms_output.put_line('rgd code '||l_opt_ruls_khr_rec.rgr_rgd_code);
891 --dbms_output.put_line('rdf code '||l_opt_ruls_khr_rec.rgr_rdf_code);
892 chk_and_cpy_rul_hdr(p_api_version => p_api_version,
893 p_init_msg_list => p_init_msg_list,
894 x_return_status => x_return_status,
895 x_msg_count => x_msg_count,
896 x_msg_data => x_msg_data,
897 p_khr_id => p_khr_id,
898 p_rulv_rec => l_rulv_rec,
899 p_rgd_code => l_opt_ruls_khr_rec.rgr_rgd_code,
900 p_rdf_code => l_opt_ruls_khr_rec.rgr_rdf_code,
901 p_copy_or_enter_flag => l_opv_ruls_rec.Copy_or_enter_flag);
902 --dbms_output.put_line('after trying to copy rules'||x_return_status);
903 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
904 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
905 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
906 RAISE OKL_API.G_EXCEPTION_ERROR;
907 END IF;
908 --End If;
909 End Loop;
910 --dbms_output.put_line('Exiting out of l_opt_ruls_khr_csr');
911 Close l_opt_ruls_khr_csr;
912
913 --check for applicability at line level
914
915 Open l_opt_ruls_kle_csr(p_opt_id => l_pdt_opt_vals_rec.opt_id,
916 p_rdf_code => l_rulv_rec.rule_information_category,
917 p_scs_code => l_khr_rec.scs_code);
918 Loop
919 Fetch l_opt_ruls_kle_csr into l_opt_ruls_kle_rec;
920 --check for applicability of the rule
921 Exit when l_opt_ruls_kle_csr%NOTFOUND;
922 --Else
923 --copy the rule group and rule at line level
924 --do not copy if the rule group exists
925 --do not copy if the rule group exists for the rule group
926 chk_and_cpy_rul_line(p_api_version => p_api_version,
927 p_init_msg_list => p_init_msg_list,
928 x_return_status => x_return_status,
929 x_msg_count => x_msg_count,
930 x_msg_data => x_msg_data,
931 p_khr_id => p_khr_id,
932 p_rulv_rec => l_rulv_rec,
933 p_rgd_code => l_opt_ruls_kle_rec.rgr_rgd_code,
934 p_rdf_code => l_opt_ruls_kle_rec.rgr_rdf_code,
935 p_lse_id => l_opt_ruls_kle_rec.lrg_lse_id,
936 p_copy_or_enter_flag => l_opv_ruls_rec.Copy_or_enter_flag);
937
938 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
939 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
940 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
941 RAISE OKL_API.G_EXCEPTION_ERROR;
942 END IF;
943 --End If;
944 End Loop;
945 --dbms_output.put_line('Exiting out of l_opt_ruls_kle_csr');
946 Close l_opt_ruls_kle_csr;
947 END IF; -- rule template record not found in okc_rule_v
948 End Loop;
949 --dbms_output.put_line('Exiting out of l_ovd_rul_tmls_csr');
950 CLOSE l_ovd_rul_tmls_csr;
951 End If;
952 End Loop;
953 --dbms_output.put_line('Exiting out of l_opv_ruls_csr');
954 CLOSE l_opv_ruls_csr;
955 End Loop;
956 --dbms_output.put_line('Exiting out of l_pdt_opt_vals_csr');
957 CLOSE l_pdt_opt_vals_csr;
958 --End Loop;
959 --dbms_output.put_line('Exiting out of l_slctd_opt_csr');
960 --Close l_slctd_opt_csr;
961 End If;
962 --dbms_output.put_line('Exiting out of l_khr_csr');
963 Close l_khr_csr;
964
965 --Call end Activity
966 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
967
968 EXCEPTION
969 When OKL_API.G_EXCEPTION_ERROR Then
970 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
971 (
972 l_api_name,
973 G_PKG_NAME,
974 'OKL_API.G_RET_STS_ERROR',
975 x_msg_count,
976 x_msg_data,
977 '_PVT'
978 );
979 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
980 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
981 (
982 l_api_name,
983 G_PKG_NAME,
984 'OKL_API.G_RET_STS_UNEXP_ERROR',
985 x_msg_count,
986 x_msg_data,
987 '_PVT'
988 );
989 WHEN OTHERS THEN
990 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
991 (
992 l_api_name,
993 G_PKG_NAME,
994 'OTHERS',
995 x_msg_count,
996 x_msg_data,
997 '_PVT'
998 );
999 End Copy_Product_Rules;
1000 END OKL_CPY_PDT_RULS_PVT;