[Home] [Help]
PACKAGE BODY: APPS.OKL_PAYMENT_APPLICATION_PVT
Source
1 PACKAGE BODY OKL_PAYMENT_APPLICATION_PVT AS
2 /* $Header: OKLRPYAB.pls 120.14 2009/05/11 05:56:41 rgooty ship $*/
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7
8 -- Global Variables
9 G_INIT_NUMBER NUMBER := -9999;
10 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_PAYMENT_APPLICATION_PVT';
11 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
12 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
13
14 -- Bug# 7661717 - Added Record type and table type
15 TYPE line_csr_rec IS RECORD (id NUMBER, capital_amount NUMBER);
16 TYPE line_csr_tbl IS TABLE OF line_csr_rec
17 INDEX BY BINARY_INTEGER;
18
19 subtype rgpv_rec_type IS OKL_RULE_PUB.rgpv_rec_type;
20 subtype rulv_rec_type IS OKL_RULE_PUB.rulv_rec_type;
21 subtype rgpv_tbl_type IS OKL_RULE_PUB.rgpv_tbl_type;
22 subtype rulv_tbl_type IS OKL_RULE_PUB.rulv_tbl_type;
23
24
25 ------------------------------------------------------------------------------
26 -- PROCEDURE Report_Error
27 -- It is a generalized routine to display error on Concurrent Manager Log file
28 -- Calls:
29 -- Called by:
30 ------------------------------------------------------------------------------
31
32 PROCEDURE Report_Error(
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2
35 ) IS
36
37 x_msg_index_out NUMBER;
38 x_msg_out VARCHAR2(2000);
39
40 BEGIN
41
42 okl_api.end_activity(
43 X_msg_count => x_msg_count,
44 X_msg_data => x_msg_data
45 );
46
47 FOR i in 1..x_msg_count
48 LOOP
49 FND_MSG_PUB.GET(
50 p_msg_index => i,
51 p_encoded => FND_API.G_FALSE,
52 p_data => x_msg_data,
53 p_msg_index_out => x_msg_index_out
54 );
55
56 END LOOP;
57 return;
58 EXCEPTION
59 WHEN OTHERS THEN
60 NULL;
61 END Report_Error;
62
63 ------------------------------------------------------------------------------
64 -- PROCEDURE check_line_rule
65 --
66 -- This procedure checks the presence of SLH and SLL at line level. Payment
67 -- application should not proceed if SLH and SLL are there at Line level already.
68 -- It returns the total such rule count, depending on which rest of the process
69 -- works.
70 --
71 -- Calls:
72 -- Called By:
73 ------------------------------------------------------------------------------
74 PROCEDURE check_line_rule (
75 x_return_status OUT NOCOPY VARCHAR2,
76 x_msg_count OUT NOCOPY NUMBER,
77 x_msg_data OUT NOCOPY VARCHAR2,
78 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
79 p_stream_id IN OKC_RULES_V.OBJECT1_ID1%TYPE,
80 x_rule_present OUT NOCOPY VARCHAR2) IS
81 l_proc_name VARCHAR2(35) := 'CHECK_LINE_RULE';
82
83 CURSOR lrule_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
84 p_strm_id OKC_RULES_V.OBJECT1_ID1%TYPE) IS
85 SELECT 'Y'
86 FROM okc_rules_v rule,
87 okc_rule_groups_v rg,
88 okc_k_lines_b cle,
89 okc_line_styles_b lsb
90 WHERE rule.dnz_chr_id = p_chr_id
91 AND rule_information_category IN ('LASLH','LASLL')
92 AND rule.object1_id1 = p_strm_id
93 AND rg.cle_id IS NOT NULL
94 AND rule.rgp_id = rg.id
95 -- next few lines bug fix along with property tax payment changes
96 AND cle.lse_id = lsb.id
97 AND lsb.lty_code = 'FREE_FORM1'
98 AND cle.id = rg.cle_id
99 -- added to handle abandon line
100 AND NOT EXISTS (
101 SELECT 'Y'
102 FROM okc_statuses_v okcsts,
103 okc_k_lines_b line
104 WHERE line.id = rg.cle_id
105 AND line.sts_code = okcsts.code
106 AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED'));
107
108
109 lrule_failed EXCEPTION;
110 l_flag VARCHAR2(1);
111
112 BEGIN
113 IF (G_DEBUG_ENABLED = 'Y') THEN
114 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
115 END IF;
116 x_return_status := OKC_API.G_RET_STS_SUCCESS;
117 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
118 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
119 END IF;
120 OPEN lrule_csr(p_chr_id,
121 p_stream_id);
122 FETCH lrule_csr INTO l_flag;
123 IF lrule_csr%NOTFOUND THEN
124 x_rule_present := 'N';
125 ELSE
126 x_rule_present := 'Y';
127 END IF;
128 CLOSE lrule_csr;
129
130 RETURN;
131 EXCEPTION
132 WHEN OTHERS THEN
133 IF lrule_csr%ISOPEN THEN
134 CLOSE lrule_csr;
135 END IF;
136 x_return_status := OKC_API.G_RET_STS_ERROR;
137 okl_api.set_message(
138 G_APP_NAME,
139 G_UNEXPECTED_ERROR,
140 'OKL_SQLCODE',
141 SQLCODE,
142 'OKL_SQLERRM',
143 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
144 );
145 END check_line_rule;
146
147
148 ------------------------------------------------------------------------------
149 -- PROCEDURE check_ro_subline_rule
150 --
151 -- This procedure checks the presence of SLH and SLL at sub-line line level.
152 -- for the given Rollover top line. Payment application should not proceed if
153 -- SLH and SLL are there at Sub-Line level already.
154 --
155 -- Calls:
156 -- Called By:
157 ------------------------------------------------------------------------------
158 PROCEDURE check_ro_subline_rule (
159 x_return_status OUT NOCOPY VARCHAR2,
160 x_msg_count OUT NOCOPY NUMBER,
161 x_msg_data OUT NOCOPY VARCHAR2,
162 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
163 p_cle_id IN OKL_K_LINES.ID%TYPE,
164 p_stream_id IN OKC_RULES_V.OBJECT1_ID1%TYPE,
165 x_rule_present OUT NOCOPY VARCHAR2) IS
166 l_proc_name VARCHAR2(35) := 'CHECK_RO_SUBLINE_RULE';
167
168 CURSOR l_ro_sl_rule_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
169 p_cle_id OKL_K_LINES.ID%TYPE,
170 p_strm_id OKC_RULES_V.OBJECT1_ID1%TYPE) IS
171
172 SELECT 'Y'
173 FROM okc_rules_v rule,
174 okc_rule_groups_v rg,
175 okc_k_lines_b cleb, -- Top Line
176 okc_k_lines_b cle, -- Sub Line
177 okc_line_styles_b lsb
178 WHERE rule.dnz_chr_id = p_chr_id
179 AND rule_information_category IN ('LASLH','LASLL')
180 AND rule.object1_id1 = p_strm_id
181 AND cleb.id = p_cle_id -- Top Line
182 AND cleb.id = cle.cle_id -- Top line equals subline cle_id
183 AND rg.cle_id = cle.id -- Equate with sub-line
184 AND rule.rgp_id = rg.id
185 AND cle.lse_id = lsb.id
186 AND lsb.lty_code = 'LINK_FEE_ASSET'
187 -- added to handle abandon line
188 AND NOT EXISTS (
189 SELECT 'Y'
190 FROM okc_statuses_v okcsts,
191 okc_k_lines_b line
192 WHERE line.id = rg.cle_id
193 AND line.sts_code = okcsts.code
194 AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED'));
195
196 lrule_failed EXCEPTION;
197 l_flag VARCHAR2(1);
198
199 BEGIN
200 IF (G_DEBUG_ENABLED = 'Y') THEN
201 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
202 END IF;
203 x_return_status := OKC_API.G_RET_STS_SUCCESS;
204 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
205 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
206 END IF;
207 OPEN l_ro_sl_rule_csr(p_chr_id, p_cle_id,
208 p_stream_id);
209 FETCH l_ro_sl_rule_csr INTO l_flag;
210 IF l_ro_sl_rule_csr%NOTFOUND THEN
211 x_rule_present := 'N';
212 ELSE
213 IF (l_flag IS NOT NULL ) THEN
214 x_rule_present := l_flag;
215 ELSE
216 x_rule_present := 'N';
217 END IF;
218 END IF;
219 CLOSE l_ro_sl_rule_csr;
220
221 RETURN;
222 EXCEPTION
223 WHEN OTHERS THEN
224 IF l_ro_sl_rule_csr%ISOPEN THEN
225 CLOSE l_ro_sl_rule_csr;
226 END IF;
227 x_return_status := OKC_API.G_RET_STS_ERROR;
228 okl_api.set_message(
229 G_APP_NAME,
230 G_UNEXPECTED_ERROR,
231 'OKL_SQLCODE',
232 SQLCODE,
233 'OKL_SQLERRM',
234 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
235 );
236 END check_ro_subline_rule;
237
238
239 ------------------------------------------------------------------------------
240 -- PROCEDURE get_fee_subline_cap_amount
241 --
242 -- This procedure returns Capital Amount from FEE Sub line corresponding
243 -- to Financial Asset Line, passed as parameter
244 --
245 -- Calls:
246 -- Called By:
247 ------------------------------------------------------------------------------
248 PROCEDURE get_fee_subline_cap_amount(
249 x_return_status OUT NOCOPY VARCHAR2,
250 x_msg_count OUT NOCOPY NUMBER,
251 x_msg_data OUT NOCOPY VARCHAR2,
252 p_fin_line_id IN NUMBER,
253 x_fee_cap_amt OUT NOCOPY NUMBER
254 ) IS
255
256 l_proc_name VARCHAR2(35) := 'GET_FEE_SUBLINE_CAP_AMOUNT';
257 l_cap_amount NUMBER;
258
259 CURSOR cap_csr (p_fin_line_id NUMBER) IS
260 SELECT SUM(NVL(okl1.capital_amount,0))
261 FROM okl_k_lines_full_v okl1, -- fee sub line
262 okl_k_lines_full_v okl2, -- fee top line
263 okc_k_items_v item1,
264 okc_k_items_v item2,
265 okl_strmtyp_source_v stream
266 WHERE item1.object1_id1 = TO_CHAR(p_fin_line_id) -- Bug 3830454
267 AND item1.jtot_object1_code = 'OKX_COVASST' -- Bug 3830454
268 AND stream.capitalize_yn = 'Y'
269 AND okl1.id = item1.cle_id
270 AND okl2.id = okl1.cle_id
271 AND okl2.id = item2.cle_id
272 AND item2.object1_id1 = stream.id1;
273
274 BEGIN
275 IF (G_DEBUG_ENABLED = 'Y') THEN
276 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
277 END IF;
278 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
279 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
280 END IF;
281 x_return_status := OKC_API.G_RET_STS_SUCCESS;
282
283 l_cap_amount := 0;
284 OPEN cap_csr(p_fin_line_id);
285 FETCH cap_csr INTO l_cap_amount;
286 IF cap_csr%NOTFOUND THEN
287 l_cap_amount := 0;
288 END IF;
289
290 CLOSE cap_csr;
291
292 x_fee_cap_amt := l_cap_amount;
293
294 RETURN;
295 END get_fee_subline_cap_amount;
296
297 PROCEDURE get_contract_rule_group(
298 p_api_version IN NUMBER,
299 p_init_msg_list IN VARCHAR2,
300 p_chr_id IN NUMBER,
301 p_cle_id IN NUMBER,
302 p_stream_id IN OKC_RULES_V.OBJECT1_ID1%TYPE,
303 p_rgd_code IN VARCHAR2,
304 x_return_status OUT NOCOPY VARCHAR2,
305 x_msg_count OUT NOCOPY NUMBER,
306 x_msg_data OUT NOCOPY VARCHAR2,
307 x_rgpv_tbl OUT NOCOPY rgpv_tbl_type,
308 x_rg_count OUT NOCOPY NUMBER) IS
309 l_No_RG_Found BOOLEAN DEFAULT TRUE;
310 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
311 l_api_name CONSTANT VARCHAR2(30) := 'GET_CONTRACT_RULE_GROUP';
312 l_api_version CONSTANT NUMBER := 1.0;
313 ---------------------------------------------------------------------------
314 -- FUNCTION get_rec for: OKC_RULE_GROUPS_V
315 ---------------------------------------------------------------------------
316 FUNCTION get_rgpv_tab (
317 p_chr_id IN NUMBER,
318 p_cle_id IN NUMBER,
319 p_rgd_code IN VARCHAR2,
320 p_stream_id IN NUMBER,
321 x_rg_count OUT NOCOPY NUMBER
322 ) RETURN rgpv_tbl_type IS
323 CURSOR okc_rgpv_csr (p_chr_id IN NUMBER,
324 p_cle_id IN NUMBER,
325 p_dnz_chr_id IN NUMBER,
326 p_strm_id IN OKC_RULES_V.OBJECT1_ID1%TYPE,
327 p_rgd_code IN VARCHAR2) IS
328 SELECT
329 rg.ID,
330 rg.OBJECT_VERSION_NUMBER,
331 rg.SFWT_FLAG,
332 rg.RGD_CODE,
333 rg.SAT_CODE,
334 rg.RGP_TYPE,
335 rg.CLE_ID,
336 rg.CHR_ID,
337 rg.DNZ_CHR_ID,
338 rg.PARENT_RGP_ID,
339 rg.COMMENTS,
340 rg.ATTRIBUTE_CATEGORY,
341 rg.ATTRIBUTE1,
342 rg.ATTRIBUTE2,
343 rg.ATTRIBUTE3,
344 rg.ATTRIBUTE4,
345 rg.ATTRIBUTE5,
346 rg.ATTRIBUTE6,
347 rg.ATTRIBUTE7,
348 rg.ATTRIBUTE8,
349 rg.ATTRIBUTE9,
350 rg.ATTRIBUTE10,
351 rg.ATTRIBUTE11,
352 rg.ATTRIBUTE12,
353 rg.ATTRIBUTE13,
354 rg.ATTRIBUTE14,
355 rg.ATTRIBUTE15,
356 rg.CREATED_BY,
357 rg.CREATION_DATE,
358 rg.LAST_UPDATED_BY,
359 rg.LAST_UPDATE_DATE,
360 rg.LAST_UPDATE_LOGIN
361 FROM Okc_Rule_Groups_V rg,
362 Okc_Rules_V rule
363 WHERE NVL(rg.chr_id,-9999) = p_chr_id
364 --AND NVL(rg.cle_id,-9999) = p_cle_id
365 --AND rg.dnz_chr_id = DECODE(p_chr_id,-9999,rg.dnz_chr_id,p_dnz_chr_id)
366 AND rg.dnz_chr_id = p_chr_id
367 AND rule.object1_id1 = p_strm_id
368 AND rgd_code = DECODE(p_rgd_code,NULL,rgd_code,p_rgd_code)
369 AND rule.rgp_id = rg.id;
370
371
372 l_rgpv_rec rgpv_rec_type;
373 l_rgpv_tab rgpv_tbl_type;
374 i NUMBER DEFAULT 0;
375 l_chr_id NUMBER;
376 l_cle_id NUMBER;
377 l_dnz_chr_id NUMBER;
378
379 BEGIN
380 IF p_chr_id IS NULL AND p_cle_id IS NOT NULL THEN
381 l_chr_id := -9999;
382 l_cle_id := p_cle_id;
383 l_dnz_chr_id := -9999;
384 ELSIF p_chr_id IS NULL AND p_cle_id IS NULL THEN
385 l_chr_id := -9999;
386 l_cle_id := -9999;
387 l_dnz_chr_id := -9999;
388 ELSIF p_chr_id IS NOT NULL AND p_cle_id IS NULL THEN
389 l_chr_id := p_chr_id;
390 l_cle_id := -9999;
391 l_dnz_chr_id := p_chr_id;
392 ELSIF p_chr_id IS NOT NULL AND p_cle_id IS NOT NULL THEN
393 l_chr_id := -9999;
394 l_cle_id := p_cle_id;
395 l_dnz_chr_id := p_chr_id;
396 END IF;
397
398 -- Get current database values
399 OPEN okc_rgpv_csr (l_chr_id,
400 l_cle_id,
401 l_dnz_chr_id,
402 p_stream_id,
403 p_rgd_code);
404 LOOP
405 FETCH okc_rgpv_csr INTO
406 l_rgpv_rec.ID,
407 l_rgpv_rec.OBJECT_VERSION_NUMBER,
408 l_rgpv_rec.SFWT_FLAG,
409 l_rgpv_rec.RGD_CODE,
410 l_rgpv_rec.SAT_CODE,
411 l_rgpv_rec.RGP_TYPE,
412 l_rgpv_rec.CLE_ID,
413 l_rgpv_rec.CHR_ID,
414 l_rgpv_rec.DNZ_CHR_ID,
415 l_rgpv_rec.PARENT_RGP_ID,
416 l_rgpv_rec.COMMENTS,
417 l_rgpv_rec.ATTRIBUTE_CATEGORY,
418 l_rgpv_rec.ATTRIBUTE1,
419 l_rgpv_rec.ATTRIBUTE2,
420 l_rgpv_rec.ATTRIBUTE3,
421 l_rgpv_rec.ATTRIBUTE4,
422 l_rgpv_rec.ATTRIBUTE5,
423 l_rgpv_rec.ATTRIBUTE6,
424 l_rgpv_rec.ATTRIBUTE7,
425 l_rgpv_rec.ATTRIBUTE8,
426 l_rgpv_rec.ATTRIBUTE9,
427 l_rgpv_rec.ATTRIBUTE10,
428 l_rgpv_rec.ATTRIBUTE11,
429 l_rgpv_rec.ATTRIBUTE12,
430 l_rgpv_rec.ATTRIBUTE13,
431 l_rgpv_rec.ATTRIBUTE14,
432 l_rgpv_rec.ATTRIBUTE15,
433 l_rgpv_rec.CREATED_BY,
434 l_rgpv_rec.CREATION_DATE,
435 l_rgpv_rec.LAST_UPDATED_BY,
436 l_rgpv_rec.LAST_UPDATE_DATE,
437 l_rgpv_rec.LAST_UPDATE_LOGIN;
438 EXIT WHEN okc_rgpv_csr%NOTFOUND;
439 i := okc_rgpv_csr%RowCount;
440 l_rgpv_tab(i) := l_rgpv_rec;
441 END LOOP;
442 CLOSE okc_rgpv_csr;
443 x_rg_count := i;
444 RETURN(l_rgpv_tab);
445 END get_rgpv_tab;
446
447 BEGIN
448
449 x_rgpv_tbl := get_rgpv_tab(p_chr_id => p_chr_id,
450 p_cle_id => p_cle_id,
451 p_rgd_code => p_rgd_code,
452 p_stream_id => p_stream_id,
453 x_rg_count => x_rg_count);
454 EXCEPTION
455 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
456 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(
457 p_api_name => l_api_name,
458 p_pkg_name => g_pkg_name,
459 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
460 x_msg_count => x_msg_count,
461 x_msg_data => x_msg_data,
462 p_api_type => g_api_type);
463
464 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
465 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(
466 p_api_name => l_api_name,
467 p_pkg_name => g_pkg_name,
468 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
469 x_msg_count => x_msg_count,
470 x_msg_data => x_msg_data,
471 p_api_type => g_api_type);
472
473 WHEN OTHERS THEN
474 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(
475 p_api_name => l_api_name,
476 p_pkg_name => g_pkg_name,
477 p_exc_name => 'OTHERS',
478 x_msg_count => x_msg_count,
479 x_msg_data => x_msg_data,
480 p_api_type => g_api_type);
481
482 END get_contract_rule_group;
483
484 PROCEDURE get_ro_fee_topln_rg(
485 p_api_version IN NUMBER,
486 p_init_msg_list IN VARCHAR2,
487 p_chr_id IN NUMBER,
488 p_cle_id IN NUMBER,
489 p_stream_id IN OKC_RULES_V.OBJECT1_ID1%TYPE,
490 p_rgd_code IN VARCHAR2,
491 x_return_status OUT NOCOPY VARCHAR2,
492 x_msg_count OUT NOCOPY NUMBER,
493 x_msg_data OUT NOCOPY VARCHAR2,
494 x_rgpv_tbl OUT NOCOPY rgpv_tbl_type,
495 x_rg_count OUT NOCOPY NUMBER) IS
496 l_No_RG_Found BOOLEAN DEFAULT TRUE;
497 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
498 l_api_name CONSTANT VARCHAR2(30) := 'GET_RO_FEE_TOPLN_RG';
499 l_api_version CONSTANT NUMBER := 1.0;
500 ---------------------------------------------------------------------------
501 -- FUNCTION get_rec for: OKC_RULE_GROUPS_V
502 ---------------------------------------------------------------------------
503 FUNCTION get_rgpv_tab (
504 p_chr_id IN NUMBER,
505 p_cle_id IN NUMBER,
506 p_rgd_code IN VARCHAR2,
507 p_stream_id IN NUMBER,
508 x_rg_count OUT NOCOPY NUMBER
509 ) RETURN rgpv_tbl_type IS
510 CURSOR okc_rgpv_csr (p_chr_id IN NUMBER,
511 p_cle_id IN NUMBER,
512 p_strm_id IN OKC_RULES_V.OBJECT1_ID1%TYPE,
513 p_rgd_code IN VARCHAR2) IS
514 SELECT
515 rg.ID,
516 rg.OBJECT_VERSION_NUMBER,
517 rg.SFWT_FLAG,
518 rg.RGD_CODE,
519 rg.SAT_CODE,
520 rg.RGP_TYPE,
521 rg.CLE_ID,
522 rg.CHR_ID,
523 rg.DNZ_CHR_ID,
524 rg.PARENT_RGP_ID,
525 rg.COMMENTS,
526 rg.ATTRIBUTE_CATEGORY,
527 rg.ATTRIBUTE1,
528 rg.ATTRIBUTE2,
529 rg.ATTRIBUTE3,
530 rg.ATTRIBUTE4,
531 rg.ATTRIBUTE5,
532 rg.ATTRIBUTE6,
533 rg.ATTRIBUTE7,
534 rg.ATTRIBUTE8,
535 rg.ATTRIBUTE9,
536 rg.ATTRIBUTE10,
537 rg.ATTRIBUTE11,
538 rg.ATTRIBUTE12,
539 rg.ATTRIBUTE13,
540 rg.ATTRIBUTE14,
541 rg.ATTRIBUTE15,
542 rg.CREATED_BY,
543 rg.CREATION_DATE,
544 rg.LAST_UPDATED_BY,
545 rg.LAST_UPDATE_DATE,
546 rg.LAST_UPDATE_LOGIN
547 FROM Okc_Rule_Groups_V rg,
548 Okc_Rules_V rule
549 WHERE rg.dnz_chr_id = p_chr_id
550 AND rg.cle_id = p_cle_id
551 AND rule.object1_id1 = to_char(p_strm_id)
552 AND rg.rgd_code = DECODE(p_rgd_code,NULL,rgd_code,p_rgd_code)
553 AND rule.rgp_id = rg.id;
554
555
556 l_rgpv_rec rgpv_rec_type;
557 l_rgpv_tab rgpv_tbl_type;
558 i NUMBER DEFAULT 0;
559 l_chr_id NUMBER;
560 l_cle_id NUMBER;
561 l_dnz_chr_id NUMBER;
562
563 BEGIN
564 IF p_chr_id IS NULL AND p_cle_id IS NOT NULL THEN
565 l_chr_id := -9999;
566 l_cle_id := p_cle_id;
567 l_dnz_chr_id := -9999;
568 ELSIF p_chr_id IS NULL AND p_cle_id IS NULL THEN
569 l_chr_id := -9999;
570 l_cle_id := -9999;
571 l_dnz_chr_id := -9999;
572 ELSIF p_chr_id IS NOT NULL AND p_cle_id IS NULL THEN
573 l_chr_id := p_chr_id;
574 l_cle_id := -9999;
575 l_dnz_chr_id := p_chr_id;
576 ELSIF p_chr_id IS NOT NULL AND p_cle_id IS NOT NULL THEN
577 l_chr_id := -9999;
578 l_cle_id := p_cle_id;
579 l_dnz_chr_id := p_chr_id;
580 END IF;
581
582 -- Get current database values
583 OPEN okc_rgpv_csr (l_dnz_chr_id,
584 l_cle_id,
585 p_stream_id,
586 p_rgd_code);
587 LOOP
588 FETCH okc_rgpv_csr INTO
589 l_rgpv_rec.ID,
590 l_rgpv_rec.OBJECT_VERSION_NUMBER,
591 l_rgpv_rec.SFWT_FLAG,
592 l_rgpv_rec.RGD_CODE,
593 l_rgpv_rec.SAT_CODE,
594 l_rgpv_rec.RGP_TYPE,
595 l_rgpv_rec.CLE_ID,
596 l_rgpv_rec.CHR_ID,
597 l_rgpv_rec.DNZ_CHR_ID,
598 l_rgpv_rec.PARENT_RGP_ID,
599 l_rgpv_rec.COMMENTS,
600 l_rgpv_rec.ATTRIBUTE_CATEGORY,
601 l_rgpv_rec.ATTRIBUTE1,
602 l_rgpv_rec.ATTRIBUTE2,
603 l_rgpv_rec.ATTRIBUTE3,
604 l_rgpv_rec.ATTRIBUTE4,
605 l_rgpv_rec.ATTRIBUTE5,
606 l_rgpv_rec.ATTRIBUTE6,
607 l_rgpv_rec.ATTRIBUTE7,
608 l_rgpv_rec.ATTRIBUTE8,
609 l_rgpv_rec.ATTRIBUTE9,
610 l_rgpv_rec.ATTRIBUTE10,
611 l_rgpv_rec.ATTRIBUTE11,
612 l_rgpv_rec.ATTRIBUTE12,
613 l_rgpv_rec.ATTRIBUTE13,
614 l_rgpv_rec.ATTRIBUTE14,
615 l_rgpv_rec.ATTRIBUTE15,
616 l_rgpv_rec.CREATED_BY,
617 l_rgpv_rec.CREATION_DATE,
618 l_rgpv_rec.LAST_UPDATED_BY,
619 l_rgpv_rec.LAST_UPDATE_DATE,
620 l_rgpv_rec.LAST_UPDATE_LOGIN;
621 EXIT WHEN okc_rgpv_csr%NOTFOUND;
622 i := okc_rgpv_csr%RowCount;
623 l_rgpv_tab(i) := l_rgpv_rec;
624 END LOOP;
625 CLOSE okc_rgpv_csr;
626 x_rg_count := i;
627 RETURN(l_rgpv_tab);
628 END get_rgpv_tab;
629
630 BEGIN
631
632 x_rgpv_tbl := get_rgpv_tab(p_chr_id => p_chr_id,
633 p_cle_id => p_cle_id,
634 p_rgd_code => p_rgd_code,
635 p_stream_id => p_stream_id,
636 x_rg_count => x_rg_count);
637 EXCEPTION
638 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
639 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(
640 p_api_name => l_api_name,
641 p_pkg_name => g_pkg_name,
642 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
643 x_msg_count => x_msg_count,
644 x_msg_data => x_msg_data,
645 p_api_type => g_api_type);
646
647 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
648 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(
649 p_api_name => l_api_name,
650 p_pkg_name => g_pkg_name,
651 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
652 x_msg_count => x_msg_count,
653 x_msg_data => x_msg_data,
654 p_api_type => g_api_type);
655
656 WHEN OTHERS THEN
657 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(
658 p_api_name => l_api_name,
659 p_pkg_name => g_pkg_name,
660 p_exc_name => 'OTHERS',
661 x_msg_count => x_msg_count,
662 x_msg_data => x_msg_data,
663 p_api_type => g_api_type);
664
665 END get_ro_fee_topln_rg;
666
667
668 PROCEDURE get_slh_rules(
669 p_rgpv_rec IN rgpv_rec_type,
670 p_rdf_code IN VARCHAR2,
671 p_stream_id IN OKC_RULES_V.OBJECT1_ID1%TYPE,
672 x_return_status OUT NOCOPY VARCHAR2,
673 x_msg_count OUT NOCOPY NUMBER,
674 x_msg_data OUT NOCOPY VARCHAR2,
675 x_rulv_tbl OUT NOCOPY rulv_tbl_type,
676 x_rule_count OUT NOCOPY NUMBER ) IS
677
678
679 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
680 l_api_name CONSTANT VARCHAR2(30) := 'GET_SLH_RULES';
681 l_api_version CONSTANT NUMBER := 1.0;
682 ---------------------------------------------------------------------------
683 -- FUNCTION get_rec for: OKC_RULES_V
684 ---------------------------------------------------------------------------
685 FUNCTION get_rulv_tab (
686 p_rgpv_rec IN rgpv_rec_type,
687 p_rdf_code IN VARCHAR2,
688 p_stream_id IN OKC_RULES_V.OBJECT1_ID1%TYPE,
689 x_Rule_Count OUT NOCOPY NUMBER
690 ) RETURN rulv_tbl_type IS
691 CURSOR okc_rulv_csr (p_rgp_id IN NUMBER,
692 p_strm_id OKC_RULES_V.OBJECT1_ID1%TYPE,
693 p_rdf_code IN VARCHAR2) IS
694 SELECT
695 ID,
696 OBJECT_VERSION_NUMBER,
697 SFWT_FLAG,
698 OBJECT1_ID1,
699 OBJECT2_ID1,
700 OBJECT3_ID1,
701 OBJECT1_ID2,
702 OBJECT2_ID2,
703 OBJECT3_ID2,
704 JTOT_OBJECT1_CODE,
705 JTOT_OBJECT2_CODE,
706 JTOT_OBJECT3_CODE,
707 DNZ_CHR_ID,
708 RGP_ID,
709 PRIORITY,
710 STD_TEMPLATE_YN,
711 COMMENTS,
712 WARN_YN,
713 ATTRIBUTE_CATEGORY,
714 ATTRIBUTE1,
715 ATTRIBUTE2,
716 ATTRIBUTE3,
717 ATTRIBUTE4,
718 ATTRIBUTE5,
719 ATTRIBUTE6,
720 ATTRIBUTE7,
721 ATTRIBUTE8,
722 ATTRIBUTE9,
723 ATTRIBUTE10,
724 ATTRIBUTE11,
725 ATTRIBUTE12,
726 ATTRIBUTE13,
727 ATTRIBUTE14,
728 ATTRIBUTE15,
729 CREATED_BY,
730 CREATION_DATE,
731 LAST_UPDATED_BY,
732 LAST_UPDATE_DATE,
733 LAST_UPDATE_LOGIN,
734 --TEXT,
735 RULE_INFORMATION_CATEGORY,
736 RULE_INFORMATION1,
737 RULE_INFORMATION2,
738 RULE_INFORMATION3,
739 RULE_INFORMATION4,
740 RULE_INFORMATION5,
741 RULE_INFORMATION6,
742 RULE_INFORMATION7,
743 RULE_INFORMATION8,
744 RULE_INFORMATION9,
745 RULE_INFORMATION10,
746 RULE_INFORMATION11,
747 RULE_INFORMATION12,
748 RULE_INFORMATION13,
749 RULE_INFORMATION14,
750 RULE_INFORMATION15,
751 TEMPLATE_YN,
752 ans_set_jtot_object_code,
753 ans_set_jtot_object_id1,
754 ans_set_jtot_object_id2,
755 DISPLAY_SEQUENCE
756 FROM Okc_Rules_V
757 WHERE okc_rules_v.rgp_id = p_rgp_id
758 AND object1_id1 = p_strm_id
759 AND RULE_INFORMATION_CATEGORY = DECODE(p_rdf_code,NULL,RULE_INFORMATION_CATEGORY,p_rdf_code);
760 l_rulv_rec rulv_rec_type;
761 l_rulv_tab rulv_tbl_type;
762 i NUMBER DEFAULT 0;
763 BEGIN
764
765 -- Get current database values
766 OPEN okc_rulv_csr (p_rgpv_rec.id,
767 p_stream_id,
768 p_rdf_code);
769 LOOP
770 FETCH okc_rulv_csr INTO
771 l_rulv_rec.ID,
772 l_rulv_rec.OBJECT_VERSION_NUMBER,
773 l_rulv_rec.SFWT_FLAG,
774 l_rulv_rec.OBJECT1_ID1,
775 l_rulv_rec.OBJECT2_ID1,
776 l_rulv_rec.OBJECT3_ID1,
777 l_rulv_rec.OBJECT1_ID2,
778 l_rulv_rec.OBJECT2_ID2,
779 l_rulv_rec.OBJECT3_ID2,
780 l_rulv_rec.JTOT_OBJECT1_CODE,
781 l_rulv_rec.JTOT_OBJECT2_CODE,
782 l_rulv_rec.JTOT_OBJECT3_CODE,
783 l_rulv_rec.DNZ_CHR_ID,
784 l_rulv_rec.RGP_ID,
785 l_rulv_rec.PRIORITY,
786 l_rulv_rec.STD_TEMPLATE_YN,
787 l_rulv_rec.COMMENTS,
788 l_rulv_rec.WARN_YN,
789 l_rulv_rec.ATTRIBUTE_CATEGORY,
790 l_rulv_rec.ATTRIBUTE1,
791 l_rulv_rec.ATTRIBUTE2,
792 l_rulv_rec.ATTRIBUTE3,
793 l_rulv_rec.ATTRIBUTE4,
794 l_rulv_rec.ATTRIBUTE5,
795 l_rulv_rec.ATTRIBUTE6,
796 l_rulv_rec.ATTRIBUTE7,
797 l_rulv_rec.ATTRIBUTE8,
798 l_rulv_rec.ATTRIBUTE9,
799 l_rulv_rec.ATTRIBUTE10,
800 l_rulv_rec.ATTRIBUTE11,
801 l_rulv_rec.ATTRIBUTE12,
802 l_rulv_rec.ATTRIBUTE13,
803 l_rulv_rec.ATTRIBUTE14,
804 l_rulv_rec.ATTRIBUTE15,
805 l_rulv_rec.CREATED_BY,
806 l_rulv_rec.CREATION_DATE,
807 l_rulv_rec.LAST_UPDATED_BY,
808 l_rulv_rec.LAST_UPDATE_DATE,
809 l_rulv_rec.LAST_UPDATE_LOGIN,
810 --l_rulv_rec.TEXT,
811 l_rulv_rec.RULE_INFORMATION_CATEGORY,
812 l_rulv_rec.RULE_INFORMATION1,
813 l_rulv_rec.RULE_INFORMATION2,
814 l_rulv_rec.RULE_INFORMATION3,
815 l_rulv_rec.RULE_INFORMATION4,
816 l_rulv_rec.RULE_INFORMATION5,
817 l_rulv_rec.RULE_INFORMATION6,
818 l_rulv_rec.RULE_INFORMATION7,
819 l_rulv_rec.RULE_INFORMATION8,
820 l_rulv_rec.RULE_INFORMATION9,
821 l_rulv_rec.RULE_INFORMATION10,
822 l_rulv_rec.RULE_INFORMATION11,
823 l_rulv_rec.RULE_INFORMATION12,
824 l_rulv_rec.RULE_INFORMATION13,
825 l_rulv_rec.RULE_INFORMATION14,
826 l_rulv_rec.RULE_INFORMATION15,
827 l_rulv_rec.TEMPLATE_YN,
828 l_rulv_rec.ans_set_jtot_object_code,
829 l_rulv_rec.ans_set_jtot_object_id1,
830 l_rulv_rec.ans_set_jtot_object_id2,
831 l_rulv_rec.DISPLAY_SEQUENCE ;
832 EXIT WHEN okc_rulv_csr%NOTFOUND;
833 i := okc_rulv_csr%RowCount;
834 l_rulv_tab(i) := l_rulv_rec;
835 END LOOP;
836 CLOSE okc_rulv_csr;
837 x_rule_count := i;
838 RETURN(l_rulv_tab);
839 END get_rulv_tab;
840 BEGIN
841
842 x_rulv_tbl := get_rulv_tab(p_rgpv_rec => p_rgpv_rec,
843 p_rdf_code => p_rdf_code,
844 p_stream_id => p_stream_id,
845 x_Rule_Count => x_rule_Count);
846 EXCEPTION
847 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
848 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(
849 p_api_name => l_api_name,
850 p_pkg_name => g_pkg_name,
851 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
852 x_msg_count => x_msg_count,
853 x_msg_data => x_msg_data,
854 p_api_type => g_api_type);
855
856 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
857 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(
858 p_api_name => l_api_name,
859 p_pkg_name => g_pkg_name,
860 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
861 x_msg_count => x_msg_count,
862 x_msg_data => x_msg_data,
863 p_api_type => g_api_type);
864
865 WHEN OTHERS THEN
866 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(
867 p_api_name => l_api_name,
868 p_pkg_name => g_pkg_name,
869 p_exc_name => 'OTHERS',
870 x_msg_count => x_msg_count,
871 x_msg_data => x_msg_data,
872 p_api_type => g_api_type);
873
874 END get_slh_rules;
875
876 ------------------------------------------------------------------------------
877 -- PROCEDURE get_sll_rules
878 --
879 -- This procedure retrieves all SLL related to given SLH rule under LALEVL Category
880 --
881 -- Calls:
882 -- Called By:
883 ------------------------------------------------------------------------------
884 PROCEDURE get_sll_rules(
885 x_return_status OUT NOCOPY VARCHAR2,
886 x_msg_count OUT NOCOPY NUMBER,
887 x_msg_data OUT NOCOPY VARCHAR2,
888 p_rgpv_rec IN rgpv_rec_type,
889 p_rdf_code IN VARCHAR2,
890 p_slh_id IN NUMBER,
891 x_rulv_tbl OUT NOCOPY rulv_tbl_type,
892 x_rule_count OUT NOCOPY NUMBER
893 ) IS
894 CURSOR okc_rulv_csr (p_rgp_id IN NUMBER,
895 p_rdf_code IN VARCHAR2,
896 p_slh_id IN NUMBER) IS
897 SELECT
898 ID,
899 OBJECT_VERSION_NUMBER,
900 SFWT_FLAG,
901 OBJECT1_ID1,
902 OBJECT2_ID1,
903 OBJECT3_ID1,
904 OBJECT1_ID2,
905 OBJECT2_ID2,
906 OBJECT3_ID2,
907 JTOT_OBJECT1_CODE,
908 JTOT_OBJECT2_CODE,
909 JTOT_OBJECT3_CODE,
910 DNZ_CHR_ID,
911 RGP_ID,
912 PRIORITY,
913 STD_TEMPLATE_YN,
914 COMMENTS,
915 WARN_YN,
916 ATTRIBUTE_CATEGORY,
917 ATTRIBUTE1,
918 ATTRIBUTE2,
919 ATTRIBUTE3,
920 ATTRIBUTE4,
921 ATTRIBUTE5,
922 ATTRIBUTE6,
923 ATTRIBUTE7,
924 ATTRIBUTE8,
925 ATTRIBUTE9,
926 ATTRIBUTE10,
927 ATTRIBUTE11,
928 ATTRIBUTE12,
929 ATTRIBUTE13,
930 ATTRIBUTE14,
931 ATTRIBUTE15,
932 CREATED_BY,
933 CREATION_DATE,
934 LAST_UPDATED_BY,
935 LAST_UPDATE_DATE,
936 LAST_UPDATE_LOGIN,
937 --TEXT,
938 RULE_INFORMATION_CATEGORY,
939 RULE_INFORMATION1,
940 RULE_INFORMATION2,
941 RULE_INFORMATION3,
942 RULE_INFORMATION4,
943 RULE_INFORMATION5,
944 RULE_INFORMATION6,
945 RULE_INFORMATION7,
946 RULE_INFORMATION8,
947 RULE_INFORMATION9,
948 RULE_INFORMATION10,
949 RULE_INFORMATION11,
950 RULE_INFORMATION12,
951 RULE_INFORMATION13,
952 RULE_INFORMATION14,
953 RULE_INFORMATION15,
954 TEMPLATE_YN,
955 ans_set_jtot_object_code,
956 ans_set_jtot_object_id1,
957 ans_set_jtot_object_id2,
958 DISPLAY_SEQUENCE
959 FROM Okc_Rules_V
960 WHERE okc_rules_v.rgp_id = p_rgp_id
961 AND okc_rules_v.object2_id1 = p_slh_id
962 AND RULE_INFORMATION_CATEGORY = decode(p_rdf_code,null,RULE_INFORMATION_CATEGORY,p_rdf_code);
963
964 l_rulv_rec rulv_rec_type;
965 i NUMBER default 0;
966 l_proc_name VARCHAR2(35) := 'GET_SLL_RULES';
967 BEGIN
968 IF (G_DEBUG_ENABLED = 'Y') THEN
969 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
970 END IF;
971
972 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
973 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
974 END IF;
975 -- Get current database values
976 OPEN okc_rulv_csr (p_rgpv_rec.id,
977 p_rdf_code,
978 p_slh_id);
979 LOOP
980 FETCH okc_rulv_csr INTO
981 l_rulv_rec.ID,
982 l_rulv_rec.OBJECT_VERSION_NUMBER,
983 l_rulv_rec.SFWT_FLAG,
984 l_rulv_rec.OBJECT1_ID1,
985 l_rulv_rec.OBJECT2_ID1,
986 l_rulv_rec.OBJECT3_ID1,
987 l_rulv_rec.OBJECT1_ID2,
988 l_rulv_rec.OBJECT2_ID2,
989 l_rulv_rec.OBJECT3_ID2,
990 l_rulv_rec.JTOT_OBJECT1_CODE,
991 l_rulv_rec.JTOT_OBJECT2_CODE,
992 l_rulv_rec.JTOT_OBJECT3_CODE,
993 l_rulv_rec.DNZ_CHR_ID,
994 l_rulv_rec.RGP_ID,
995 l_rulv_rec.PRIORITY,
996 l_rulv_rec.STD_TEMPLATE_YN,
997 l_rulv_rec.COMMENTS,
998 l_rulv_rec.WARN_YN,
999 l_rulv_rec.ATTRIBUTE_CATEGORY,
1000 l_rulv_rec.ATTRIBUTE1,
1001 l_rulv_rec.ATTRIBUTE2,
1002 l_rulv_rec.ATTRIBUTE3,
1003 l_rulv_rec.ATTRIBUTE4,
1004 l_rulv_rec.ATTRIBUTE5,
1005 l_rulv_rec.ATTRIBUTE6,
1006 l_rulv_rec.ATTRIBUTE7,
1007 l_rulv_rec.ATTRIBUTE8,
1008 l_rulv_rec.ATTRIBUTE9,
1009 l_rulv_rec.ATTRIBUTE10,
1010 l_rulv_rec.ATTRIBUTE11,
1011 l_rulv_rec.ATTRIBUTE12,
1012 l_rulv_rec.ATTRIBUTE13,
1013 l_rulv_rec.ATTRIBUTE14,
1014 l_rulv_rec.ATTRIBUTE15,
1015 l_rulv_rec.CREATED_BY,
1016 l_rulv_rec.CREATION_DATE,
1017 l_rulv_rec.LAST_UPDATED_BY,
1018 l_rulv_rec.LAST_UPDATE_DATE,
1019 l_rulv_rec.LAST_UPDATE_LOGIN,
1020 --l_rulv_rec.TEXT,
1021 l_rulv_rec.RULE_INFORMATION_CATEGORY,
1022 l_rulv_rec.RULE_INFORMATION1,
1023 l_rulv_rec.RULE_INFORMATION2,
1024 l_rulv_rec.RULE_INFORMATION3,
1025 l_rulv_rec.RULE_INFORMATION4,
1026 l_rulv_rec.RULE_INFORMATION5,
1027 l_rulv_rec.RULE_INFORMATION6,
1028 l_rulv_rec.RULE_INFORMATION7,
1029 l_rulv_rec.RULE_INFORMATION8,
1030 l_rulv_rec.RULE_INFORMATION9,
1031 l_rulv_rec.RULE_INFORMATION10,
1032 l_rulv_rec.RULE_INFORMATION11,
1033 l_rulv_rec.RULE_INFORMATION12,
1034 l_rulv_rec.RULE_INFORMATION13,
1035 l_rulv_rec.RULE_INFORMATION14,
1036 l_rulv_rec.RULE_INFORMATION15,
1037 l_rulv_rec.TEMPLATE_YN,
1038 l_rulv_rec.ans_set_jtot_object_code,
1039 l_rulv_rec.ans_set_jtot_object_id1,
1040 l_rulv_rec.ans_set_jtot_object_id2,
1041 l_rulv_rec.DISPLAY_SEQUENCE ;
1042 EXIT When okc_rulv_csr%NOTFOUND;
1043 i := okc_rulv_csr%RowCount;
1044 x_rulv_tbl(i) := l_rulv_rec;
1045 END LOOP;
1046 CLOSE okc_rulv_csr;
1047 x_rule_count := i;
1048
1049 RETURN;
1050
1051 END get_sll_rules;
1052
1053 ------------------------------------------------------------------------------
1054 -- PROCEDURE get_currency_precision
1055 --
1056 -- This procedure returns Precision for a currency attached at header level
1057 --
1058 -- Calls:
1059 -- Called By:
1060 ------------------------------------------------------------------------------
1061 PROCEDURE get_currency_precision (
1062 x_return_status OUT NOCOPY VARCHAR2,
1063 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
1064 x_precision OUT NOCOPY FND_CURRENCIES.PRECISION%TYPE
1065 ) IS
1066 CURSOR chr_csr (p_chr_id NUMBER) IS
1067 SELECT currency_code
1068 FROM okc_k_headers_v
1069 WHERE id = p_chr_id;
1070
1071 CURSOR curr_csr (p_curr_code VARCHAR2) IS
1072 SELECT precision
1073 FROM fnd_currencies
1074 WHERE currency_code = p_curr_code;
1075
1076 curr_failed EXCEPTION;
1077 l_currency_code FND_CURRENCIES.CURRENCY_CODE%TYPE;
1078 l_precision FND_CURRENCIES.PRECISION%TYPE;
1079
1080 BEGIN
1081
1082 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1083 OPEN chr_csr(p_chr_id);
1084 FETCH chr_csr INTO l_currency_code;
1085 IF chr_csr%NOTFOUND THEN
1086 okl_api.set_message(
1087 G_APP_NAME,
1088 G_LLA_CHR_ID
1089 );
1090 RAISE curr_failed;
1091 END IF;
1092
1093 CLOSE chr_csr;
1094
1095 OPEN curr_csr(l_currency_code);
1096 FETCH curr_csr INTO l_precision;
1097 IF curr_csr%NOTFOUND THEN
1098 okl_api.set_message(
1099 G_APP_NAME,
1100 G_INVALID_VALUE,
1101 'COL_NAME',
1102 'CURRENCY CODE'
1103 );
1104 RAISE curr_failed;
1105 END IF;
1106 CLOSE curr_csr;
1107
1108 x_precision := l_precision;
1109 RETURN;
1110
1111 EXCEPTION
1112 WHEN curr_failed THEN
1113 x_return_status := OKC_API.G_RET_STS_ERROR;
1114 END get_currency_precision;
1115
1116 ------------------------------------------------------------------------------
1117 -- PROCEDURE create_line_rule
1118 --
1119 -- This procedure creates line rule (SLH, SLL) after applying the payment
1120 -- amount. Calculation = (Rule Payment Amount * Line Capital Amount) /
1121 -- Total Capital Amount
1122 --
1123 -- Calls:
1124 -- Called By:
1125 ------------------------------------------------------------------------------
1126 PROCEDURE create_line_rule(
1127 x_return_status OUT NOCOPY VARCHAR2,
1128 x_msg_count OUT NOCOPY NUMBER,
1129 x_msg_data OUT NOCOPY VARCHAR2,
1130 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
1131 p_cle_id IN NUMBER,
1132 p_stream_id IN OKC_RULES_V.OBJECT1_ID1%TYPE,
1133 p_fin_line_capital_amount IN NUMBER,
1134 p_tot_capital_amount IN NUMBER,
1135 p_precision IN FND_CURRENCIES.PRECISION%TYPE,
1136 p_rgpv_tbl IN rgpv_tbl_type,
1137 p_rg_count IN NUMBER,
1138 x_slh_rulv_tbl_out OUT NOCOPY rulv_tbl_type,
1139 x_slh_count OUT NOCOPY NUMBER,
1140 x_sll_rulv_tbl_out OUT NOCOPY rulv_tbl_type,
1141 x_sll_count OUT NOCOPY NUMBER
1142 ) IS
1143 l_proc_name VARCHAR2(35) := 'CREATE_LINE_RULE';
1144
1145 CURSOR rg_csr (p_chr_id NUMBER,
1146 p_cle_id NUMBER) IS
1147 SELECT ID
1148 FROM okc_rule_groups_v
1149 WHERE dnz_chr_id = p_chr_id
1150 AND cle_id = p_cle_id
1151 AND rgd_code = 'LALEVL';
1152
1153 CURSOR fee_type_csr (p_chr_id NUMBER,
1154 p_cle_id NUMBER) IS
1155 SELECT FEE_TYPE
1156 FROM okc_k_lines_b CLEB, okl_k_lines KLE
1157 WHERE CLEB.dnz_chr_id = p_chr_id
1158 AND KLE.ID = p_cle_id
1159 AND KLE.ID = CLEB.ID;
1160
1161 -- _new indicates the rules that got created under Line as part of payment applications
1162 x_new_rgpv_rec rgpv_rec_type;
1163 x_new_slh_rulv_rec rulv_rec_type;
1164
1165 x_slh_rulv_tbl rulv_tbl_type;
1166 x_slh_rule_count NUMBER;
1167
1168 x_sll_rulv_tbl rulv_tbl_type;
1169 x_sll_rule_count NUMBER;
1170
1171 x_rulv_rec rulv_rec_type;
1172
1173 l_rgpv_rec rgpv_rec_type;
1174 l_slh_rulv_rec rulv_rec_type;
1175 l_sll_rulv_rec rulv_rec_type;
1176 l_fee_line_capital_amount NUMBER;
1177 l_line_capital_amount NUMBER;
1178 l_rgp_id NUMBER;
1179 l_fee_type OKL_K_LINES.FEE_TYPE%TYPE;
1180 rule_failed EXCEPTION;
1181
1182 BEGIN
1183 IF (G_DEBUG_ENABLED = 'Y') THEN
1184 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1185 END IF;
1186 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1187 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1188 END IF;
1189
1190 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1191
1192 -- Check to see presence of LELEVL Rule Group at line
1193 l_rgp_id := NULL;
1194 OPEN rg_csr(p_chr_id,
1195 p_cle_id);
1196 FETCH rg_csr INTO l_rgp_id;
1197 CLOSE rg_csr;
1198
1199 -- Get the fee type for the given contract and line
1200 l_fee_type := NULL;
1201 OPEN fee_type_csr(p_chr_id,
1202 p_cle_id);
1203 FETCH fee_type_csr INTO l_fee_type;
1204 CLOSE fee_type_csr;
1205
1206 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1207 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'RGP at Line: '||l_rgp_id);
1208 END IF;
1209
1210 IF (l_rgp_id IS NULL) THEN
1211 -- Create Rule Group for LALEVL
1212 l_rgpv_rec := p_rgpv_tbl(1);
1213 l_rgpv_rec.chr_id := NULL;
1214 l_rgpv_rec.cle_id := p_cle_id;
1215 l_rgpv_rec.dnz_chr_id := p_chr_id;
1216
1217 OKL_RULE_PUB.create_rule_group(
1218 p_api_version => 1.0,
1219 p_init_msg_list => OKC_API.G_FALSE,
1220 x_return_status => x_return_status,
1221 x_msg_count => x_msg_count,
1222 x_msg_data => x_msg_data,
1223 p_rgpv_rec => l_rgpv_rec,
1224 x_rgpv_rec => x_new_rgpv_rec
1225 );
1226 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1227 x_return_status := OKC_API.G_RET_STS_ERROR;
1228 raise rule_failed;
1229 END IF;
1230 ELSE
1231 x_new_rgpv_rec.id := l_rgp_id;
1232 END IF;
1233
1234 -- Get SLH Rule from Header
1235 get_slh_rules(
1236 p_rgpv_rec => p_rgpv_tbl(1),
1237 p_rdf_code => 'LASLH',
1238 p_stream_id => p_stream_id,
1239 x_return_status => x_return_status,
1240 x_msg_count => x_msg_count,
1241 x_msg_data => x_msg_data,
1242 x_rulv_tbl => x_slh_rulv_tbl,
1243 x_rule_count => x_slh_rule_count
1244 );
1245 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1246 RAISE rule_failed;
1247 END IF;
1248
1249 IF (x_slh_rule_count = 0) THEN
1250 okl_api.set_message(
1251 G_APP_NAME,
1252 G_NO_HEADER_PAYMENT
1253 );
1254 RAISE rule_failed;
1255 END IF;
1256
1257 x_slh_rulv_tbl_out := x_slh_rulv_tbl;
1258 x_slh_count := x_slh_rule_count;
1259
1260 FOR i IN 1..x_slh_rule_count
1261 LOOP
1262 l_slh_rulv_rec := x_slh_rulv_tbl(i);
1263 l_slh_rulv_rec.rgp_id := x_new_rgpv_rec.id;
1264 OKL_RULE_PUB.create_rule(
1265 p_api_version => 1.0,
1266 p_init_msg_list => OKC_API.G_FALSE,
1267 x_return_status => x_return_status,
1268 x_msg_count => x_msg_count,
1269 x_msg_data => x_msg_data,
1270 p_rulv_rec => l_slh_rulv_rec,
1271 x_rulv_rec => x_new_slh_rulv_rec
1272 );
1273 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1274 x_return_status := OKC_API.G_RET_STS_ERROR;
1275 raise rule_failed;
1276 END IF;
1277
1278 --okl_rule_apis_pvt.Get_Contract_Rules(
1279 -- Get SLL Rules from Header for a SLH
1280 get_sll_rules(
1281 x_return_status => x_return_status,
1282 x_msg_count => x_msg_count,
1283 x_msg_data => x_msg_data,
1284 p_rgpv_rec => p_rgpv_tbl(1),
1285 p_rdf_code => 'LASLL',
1286 p_slh_id => x_slh_rulv_tbl(i).id,
1287 x_rulv_tbl => x_sll_rulv_tbl,
1288 x_rule_count => x_sll_rule_count
1289 );
1290 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1291 RAISE rule_failed;
1292 END IF;
1293
1294 IF (x_sll_rule_count = 0) THEN
1295 okl_api.set_message(
1296 G_APP_NAME,
1297 G_NO_HEADER_PAYMENT
1298 );
1299 RAISE rule_failed;
1300 END IF;
1301
1302 x_sll_rulv_tbl_out := x_sll_rulv_tbl;
1303 x_sll_count := x_sll_rule_count;
1304
1305 -- Create a SLL rule under SLH created above
1306 FOR i IN 1..x_sll_rule_count
1307 LOOP
1308
1309 --
1310 -- Line Capital Amount :=
1311 -- Capital amount of this Line +
1312 -- Capital amount of corresponding LINK_ASSET_LINE (under FEE LINE)
1313 --
1314 -- Manu 9-Sep-2004 Calcualte Cap Amount if it is not Rollover fee.
1315 IF (l_fee_type <> 'ROLLOVER') THEN
1316 get_fee_subline_cap_amount(
1317 x_return_status => x_return_status,
1318 x_msg_count => x_msg_count,
1319 x_msg_data => x_msg_data,
1320 p_fin_line_id => p_cle_id,
1321 x_fee_cap_amt => l_fee_line_capital_amount
1322 );
1323 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1324 x_return_status := OKC_API.G_RET_STS_ERROR;
1325 raise rule_failed;
1326 END IF;
1327 END IF;
1328
1329 l_line_capital_amount := NVL(p_fin_line_capital_amount,0) +
1330 NVL(l_fee_line_capital_amount,0);
1331
1332 l_sll_rulv_rec := x_sll_rulv_tbl(i);
1333 l_sll_rulv_rec.rgp_id := x_new_rgpv_rec.id;
1334
1335 --
1336 -- Payment Stub logic
1337 --
1338 IF (l_sll_rulv_rec.rule_information6 IS NOT NULL) THEN
1339 l_sll_rulv_rec.rule_information6 :=
1340 ROUND(((l_sll_rulv_rec.rule_information6 * l_line_capital_amount) /
1341 p_tot_capital_amount),p_precision);
1342 END IF;
1343
1344 IF (l_sll_rulv_rec.rule_information8 IS NOT NULL) THEN
1345 l_sll_rulv_rec.rule_information8 :=
1346 ROUND(((l_sll_rulv_rec.rule_information8 * l_line_capital_amount) /
1347 p_tot_capital_amount),p_precision);
1348 END IF;
1349 l_sll_rulv_rec.object2_id1 := x_new_slh_rulv_rec.id;
1350
1351 OKL_RULE_PUB.create_rule(
1352 p_api_version => 1.0,
1353 p_init_msg_list => OKC_API.G_FALSE,
1354 x_return_status => x_return_status,
1355 x_msg_count => x_msg_count,
1356 x_msg_data => x_msg_data,
1357 p_rulv_rec => l_sll_rulv_rec,
1358 x_rulv_rec => x_rulv_rec
1359 );
1360 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1361 x_return_status := OKC_API.G_RET_STS_ERROR;
1362 raise rule_failed;
1363 END IF;
1364 END LOOP; -- SLL
1365
1366 END LOOP; -- SLH
1367
1368 RETURN;
1369
1370 EXCEPTION
1371 WHEN rule_failed THEN
1372 x_return_status := OKC_API.G_RET_STS_ERROR;
1373 WHEN OTHERS THEN
1374 okl_api.set_message(
1375 G_APP_NAME,
1376 G_UNEXPECTED_ERROR,
1377 'OKL_SQLCODE',
1378 SQLCODE,
1379 'OKL_SQLERRM',
1380 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
1381 );
1382 x_return_status := OKC_API.G_RET_STS_ERROR;
1383 END create_line_rule;
1384
1385
1386 ------------------------------------------------------------------------------
1387 -- PROCEDURE check_header_rule
1388 --
1389 -- This procedure retreives Header level rule groups and associated rule
1390 --
1391 -- Calls:
1392 -- Called By:
1393 ------------------------------------------------------------------------------
1394 PROCEDURE check_header_rule(
1395 x_return_status OUT NOCOPY VARCHAR2,
1396 x_msg_count OUT NOCOPY NUMBER,
1397 x_msg_data OUT NOCOPY VARCHAR2,
1398 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
1399 p_stream_id IN OKC_RULES_V.OBJECT1_ID1%TYPE,
1400 x_rgpv_tbl OUT NOCOPY rgpv_tbl_type,
1401 x_rg_count OUT NOCOPY NUMBER
1402 ) IS
1403 l_proc_name VARCHAR2(35) := 'CHECK_HEADER_RULE';
1404 check_header_failed EXCEPTION;
1405 l_rgpv_rec OKL_RULE_PUB.rgpv_rec_type;
1406 BEGIN
1407 IF (G_DEBUG_ENABLED = 'Y') THEN
1408 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1409 END IF;
1410 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1411 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1412 END IF;
1413
1414 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1415
1416 get_contract_rule_group(
1417 p_api_version => 1.0,
1418 p_init_msg_list => OKL_API.G_FALSE,
1419 p_chr_id => p_chr_id,
1420 p_cle_id => NULL,
1421 p_stream_id => p_stream_id,
1422 p_rgd_code => 'LALEVL',
1423 x_return_status => x_return_status,
1424 x_msg_count => x_msg_count,
1425 x_msg_data => x_msg_data,
1426 x_rgpv_tbl => x_rgpv_tbl,
1427 x_rg_count => x_rg_count
1428 );
1429
1430 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1431 RAISE check_header_failed;
1432 END IF;
1433
1434 RETURN;
1435
1436 EXCEPTION
1437 WHEN check_header_failed THEN
1438 okl_api.set_message(
1439 G_APP_NAME,
1440 G_UNEXPECTED_ERROR,
1441 'OKL_SQLCODE',
1442 SQLCODE,
1443 'OKL_SQLERRM',
1444 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
1445 );
1446 x_return_status := OKC_API.G_RET_STS_ERROR;
1447 END check_header_rule;
1448
1449 ------------------------------------------------------------------------------
1450 -- PROCEDURE get_total_capital_amount
1451 --
1452 -- This procedure return total capital amount for Financial Asset
1453 -- and Fee Top Line
1454 --
1455 -- Calls:
1456 -- Called By:
1457 ------------------------------------------------------------------------------
1458 PROCEDURE get_total_capital_amount(
1459 x_return_status OUT NOCOPY VARCHAR2,
1460 x_msg_count OUT NOCOPY NUMBER,
1461 x_msg_data OUT NOCOPY VARCHAR2,
1462 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
1463 x_tot_capital_amount OUT NOCOPY NUMBER
1464 ) IS
1465 l_proc_name VARCHAR2(35) := 'GET_TOTAL_CAPITAL_AMOUNT';
1466
1467 -- Total Capital amount from Financial Asset Top line
1468 CURSOR fin_cap_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
1469 SELECT SUM(NVL(line.capital_amount,0))
1470 FROM okl_k_lines_full_v line
1471 WHERE line.dnz_chr_id = p_chr_id
1472 AND EXISTS ( SELECT 'Y'
1473 FROM okc_line_styles_v style
1474 WHERE line.lse_id = style.id
1475 AND style.lty_code = 'FREE_FORM1'
1476 )
1477 -- added to handle abandon line
1478 AND NOT EXISTS (
1479 SELECT 'Y'
1480 FROM okc_statuses_v okcsts
1481 WHERE okcsts.code = line.sts_code
1482 AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED'));
1483
1484
1485 /* Cursor fee_cap_csr is Not required, Bug: 4598703
1486 * Capital amt at Asset is updated
1487 * with Capitalized Fee line amoutn change
1488 -- Total Capital amount from FEE Top Line
1489 */
1490
1491 l_fin_amount NUMBER;
1492 -- l_fee_amount NUMBER;
1493 cap_failed EXCEPTION;
1494
1495 BEGIN
1496 IF (G_DEBUG_ENABLED = 'Y') THEN
1497 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1498 END IF;
1499 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1500 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1501 END IF;
1502 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1503
1504 OPEN fin_cap_csr(p_chr_id);
1505 FETCH fin_cap_csr INTO l_fin_amount;
1506 IF fin_cap_csr%NOTFOUND THEN
1507 RAISE cap_failed;
1508 END IF;
1509 CLOSE fin_cap_csr;
1510
1511 x_tot_capital_amount := NVL(l_fin_amount,0);
1512
1513 IF (x_tot_capital_amount = 0) THEN
1514 RAISE cap_failed;
1515 END IF;
1516
1517 RETURN;
1518
1519 EXCEPTION
1520 WHEN cap_failed THEN
1521 IF fin_cap_csr%ISOPEN THEN
1522 CLOSE fin_cap_csr;
1523 END IF;
1524
1525 x_return_status := OKC_API.G_RET_STS_ERROR;
1526
1527 WHEN OTHERS THEN
1528 okl_api.set_message(
1529 G_APP_NAME,
1530 G_UNEXPECTED_ERROR,
1531 'OKL_SQLCODE',
1532 SQLCODE,
1533 'OKL_SQLERRM',
1534 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
1535 );
1536 x_return_status := OKC_API.G_RET_STS_ERROR;
1537 END get_total_capital_amount;
1538
1539
1540 -- Bug# 7661717 - Added - Start
1541 ------------------------------------------------------------------------------
1542 -- PROCEDURE split_payments
1543 --
1544 -- This procedure creates line rule (SLH, SLL) after applying the payment
1545 -- amount. Calculation = (Rule Payment Amount * Line Capital Amount) /
1546 -- Total Capital Amount
1547 --
1548 ------------------------------------------------------------------------------
1549 PROCEDURE split_payments(
1550 p_api_version IN NUMBER,
1551 p_init_msg_list IN VARCHAR2,
1552 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
1553 p_line_csr_tbl IN line_csr_tbl,
1554 p_stream_id IN OKC_RULES_V.OBJECT1_ID1%TYPE,
1555 p_tot_capital_amount IN NUMBER,
1556 p_precision IN FND_CURRENCIES.PRECISION%TYPE,
1557 p_rgpv_tbl IN rgpv_tbl_type,
1558 x_slh_rulv_tbl_out OUT NOCOPY rulv_tbl_type,
1559 x_slh_count OUT NOCOPY NUMBER,
1560 x_sll_rulv_tbl_out OUT NOCOPY rulv_tbl_type,
1561 x_sll_count OUT NOCOPY NUMBER,
1562 x_return_status OUT NOCOPY VARCHAR2,
1563 x_msg_count OUT NOCOPY NUMBER,
1564 x_msg_data OUT NOCOPY VARCHAR2
1565 ) IS
1566 l_api_name VARCHAR2(35) := 'split_payments';
1567
1568 CURSOR rg_csr (p_chr_id NUMBER,
1569 p_cle_id NUMBER) IS
1570 SELECT ID
1571 FROM okc_rule_groups_v
1572 WHERE dnz_chr_id = p_chr_id
1573 AND cle_id = p_cle_id
1574 AND rgd_code = 'LALEVL';
1575
1576 CURSOR fee_type_csr (p_chr_id NUMBER,
1577 p_cle_id NUMBER) IS
1578 SELECT FEE_TYPE
1579 FROM okc_k_lines_b CLEB, okl_k_lines KLE
1580 WHERE CLEB.dnz_chr_id = p_chr_id
1581 AND KLE.ID = p_cle_id
1582 AND KLE.ID = CLEB.ID;
1583
1584 TYPE kle_map_tbl IS TABLE OF NUMBER
1585 INDEX BY BINARY_INTEGER;
1586
1587 l_rgpv_rec rgpv_rec_type;
1588 l_slh_rulv_rec rulv_rec_type;
1589 x_slh_rulv_tbl OKL_RULE_PUB.rulv_tbl_type;
1590 l_sll_rulv_rec rulv_rec_type;
1591 l_sll_rulv_tbl OKL_RULE_PUB.rulv_tbl_type;
1592 x_sll_rulv_tbl rulv_tbl_type;
1593 x_sll_rulv_new_tbl rulv_tbl_type;
1594 l_kle_rgp_cache_tbl kle_map_tbl;
1595 l_kle_slh_cache_tbl kle_map_tbl;
1596
1597 x_new_rgpv_rec rgpv_rec_type;
1598 x_new_slh_rulv_rec rulv_rec_type;
1599 l_line_csr_tbl line_csr_tbl DEFAULT p_line_csr_tbl;
1600
1601 l_return_status VARCHAR2(1);
1602 x_slh_rule_count NUMBER;
1603 x_sll_rule_count NUMBER;
1604 l_rgp_id NUMBER;
1605 l_adj_index NUMBER;
1606 sll_indx NUMBER DEFAULT 0;
1607 l_adjustment NUMBER DEFAULT 0;
1608 l_slh_created BOOLEAN DEFAULT FALSE;
1609 l_khr_payment NUMBER;
1610 l_line_capital_amount NUMBER;
1611 l_asset_pymnt_total NUMBER;
1612 rule_failed EXCEPTION;
1613
1614 BEGIN
1615
1616 l_return_status := OKL_API.start_activity(l_api_name
1617 ,G_PKG_NAME
1618 ,p_init_msg_list
1619 ,p_api_version
1620 ,p_api_version
1621 ,'_PVT'
1622 ,x_return_status);
1623 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1624 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1625 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1626 RAISE OKL_API.G_EXCEPTION_ERROR;
1627 END IF;
1628 ---------------------------------------
1629 -- Get SLH - payment header information
1630 ---------------------------------------
1631 get_slh_rules(
1632 p_rgpv_rec => p_rgpv_tbl(1),
1633 p_rdf_code => 'LASLH',
1634 p_stream_id => p_stream_id,
1635 x_return_status => x_return_status,
1636 x_msg_count => x_msg_count,
1637 x_msg_data => x_msg_data,
1638 x_rulv_tbl => x_slh_rulv_tbl,
1639 x_rule_count => x_slh_rule_count
1640 );
1641 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1642 RAISE rule_failed;
1643 END IF;
1644
1645 IF (x_slh_rule_count = 0) THEN
1646 OKL_API.set_message(
1647 G_APP_NAME,
1648 G_NO_HEADER_PAYMENT
1649 );
1650 RAISE rule_failed;
1651 END IF;
1652
1653 x_slh_rulv_tbl_out := x_slh_rulv_tbl;
1654 x_slh_count := x_slh_rule_count;
1655
1656 ---------------------------------------
1657 -- Loop through each SLH record
1658 ---------------------------------------
1659 FOR i IN 1..x_slh_rule_count
1660 LOOP
1661 x_sll_rulv_tbl.DELETE;
1662 ---------------------------------------
1663 -- Get SLL Rules from Header for a SLH
1664 ---------------------------------------
1665 get_sll_rules(
1666 x_return_status => x_return_status,
1667 x_msg_count => x_msg_count,
1668 x_msg_data => x_msg_data,
1669 p_rgpv_rec => p_rgpv_tbl(1),
1670 p_rdf_code => 'LASLL',
1671 p_slh_id => x_slh_rulv_tbl(i).id,
1672 x_rulv_tbl => x_sll_rulv_tbl,
1673 x_rule_count => x_sll_rule_count
1674 );
1675 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1676 RAISE rule_failed;
1677 END IF;
1678
1679 IF (x_sll_rule_count = 0) THEN
1680 OKL_API.set_message(
1681 G_APP_NAME,
1682 G_NO_HEADER_PAYMENT
1683 );
1684 RAISE rule_failed;
1685 END IF;
1686
1687 x_sll_rulv_tbl_out := x_sll_rulv_tbl;
1688 x_sll_count := x_sll_rule_count;
1689
1690 -- Delete the SLH cache before start of processing new SLLs
1691 l_kle_slh_cache_tbl.DELETE;
1692
1693 ---------------------------------------
1694 -- Loop through each SLL record
1695 ---------------------------------------
1696 FOR j IN 1..x_sll_rule_count
1697 LOOP
1698 l_sll_rulv_rec := x_sll_rulv_tbl(j);
1699
1700 ---------------------------------------
1701 -- Note down contract level payment (be it stub or be it periodic payment)
1702 ---------------------------------------
1703 IF (l_sll_rulv_rec.rule_information6 IS NOT NULL) THEN
1704 l_khr_payment := l_sll_rulv_rec.rule_information6;
1705 END IF;
1706
1707 IF (l_sll_rulv_rec.rule_information8 IS NOT NULL) THEN
1708 l_khr_payment := l_sll_rulv_rec.rule_information8;
1709 END IF;
1710
1711 -- Initialize total of asset level payments to 0
1712 l_asset_pymnt_total := 0;
1713 l_adjustment := 0;
1714 l_adj_index := 1;
1715 sll_indx := 0;
1716 l_sll_rulv_tbl.DELETE;
1717 ---------------------------------------
1718 -- Loop through each asset
1719 ---------------------------------------
1720 FOR k IN l_line_csr_tbl.FIRST .. l_line_csr_tbl.LAST
1721 LOOP
1722 ---------------------------------------
1723 -- Create Rule Group once per asset if not present already
1724 ---------------------------------------
1725 -- Check to see presence of LELEVL Rule Group at line
1726 IF k > l_kle_rgp_cache_tbl.COUNT THEN
1727 l_rgp_id := NULL;
1728 OPEN rg_csr(p_chr_id,
1729 l_line_csr_tbl(k).id);
1730 FETCH rg_csr INTO l_rgp_id;
1731 CLOSE rg_csr;
1732
1733 IF (l_rgp_id IS NULL) THEN
1734 -- Create Rule Group for LALEVL
1735 l_rgpv_rec := p_rgpv_tbl(1);
1736 l_rgpv_rec.chr_id := NULL;
1737 l_rgpv_rec.cle_id := l_line_csr_tbl(k).id;
1738 l_rgpv_rec.dnz_chr_id := p_chr_id;
1739
1740 OKL_RULE_PUB.create_rule_group(
1741 p_api_version => 1.0,
1742 p_init_msg_list => OKC_API.G_FALSE,
1743 x_return_status => x_return_status,
1744 x_msg_count => x_msg_count,
1745 x_msg_data => x_msg_data,
1746 p_rgpv_rec => l_rgpv_rec,
1747 x_rgpv_rec => x_new_rgpv_rec
1748 );
1749 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1750 x_return_status := OKC_API.G_RET_STS_ERROR;
1751 raise rule_failed;
1752 END IF;
1753 ELSE
1754 x_new_rgpv_rec.id := l_rgp_id;
1755 END IF;
1756 -- Set the cache with this value
1757 l_kle_rgp_cache_tbl(k) := x_new_rgpv_rec.id;
1758 END IF; -- end of check for k > l_kle_rgp_cache_tbl.COUNT
1759 ---------------------------------------
1760 -- Create SLH for asset per SLH at contract level
1761 ---------------------------------------
1762 IF k > l_kle_slh_cache_tbl.COUNT THEN
1763 l_slh_rulv_rec := x_slh_rulv_tbl(i);
1764 l_slh_rulv_rec.rgp_id := l_kle_rgp_cache_tbl(k);
1765 OKL_RULE_PUB.create_rule(
1766 p_api_version => 1.0,
1767 p_init_msg_list => OKC_API.G_FALSE,
1768 x_return_status => x_return_status,
1769 x_msg_count => x_msg_count,
1770 x_msg_data => x_msg_data,
1771 p_rulv_rec => l_slh_rulv_rec,
1772 x_rulv_rec => x_new_slh_rulv_rec
1773 );
1774 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1775 x_return_status := OKC_API.G_RET_STS_ERROR;
1776 raise rule_failed;
1777 END IF;
1778
1779 -- set the SLH cache
1780 l_kle_slh_cache_tbl(k) := x_new_slh_rulv_rec.id;
1781 END IF;
1782
1783 l_sll_rulv_rec := x_sll_rulv_tbl(j);
1784 l_sll_rulv_rec.rgp_id := l_kle_rgp_cache_tbl(k);
1785 l_sll_rulv_rec.object2_id1 := l_kle_slh_cache_tbl(k);
1786
1787 ---------------------------------------
1788 -- calculate line level payment (be it stub or be it periodic payment)
1789 ---------------------------------------
1790 l_line_capital_amount := l_line_csr_tbl(k).capital_amount;
1791 IF (l_sll_rulv_rec.rule_information6 IS NOT NULL) THEN
1792 l_sll_rulv_rec.rule_information6 :=
1793 ROUND(((l_sll_rulv_rec.rule_information6 * l_line_capital_amount) /
1794 p_tot_capital_amount),p_precision);
1795 l_asset_pymnt_total := l_asset_pymnt_total + l_sll_rulv_rec.rule_information6;
1796 END IF;
1797
1798 IF (l_sll_rulv_rec.rule_information8 IS NOT NULL) THEN
1799 l_sll_rulv_rec.rule_information8 :=
1800 ROUND(((l_sll_rulv_rec.rule_information8 * l_line_capital_amount) /
1801 p_tot_capital_amount),p_precision);
1802 l_asset_pymnt_total := l_asset_pymnt_total + l_sll_rulv_rec.rule_information8;
1803 END IF;
1804 ---------------------------------------
1805 -- Identify asset with highest capital cost
1806 ---------------------------------------
1807 IF (l_line_capital_amount > l_line_csr_tbl(l_adj_index).capital_amount) THEN
1808 l_adj_index := k;
1809 END IF;
1810 -- Populate the table of SLLs
1811 sll_indx := sll_indx + 1;
1812 l_sll_rulv_tbl(sll_indx) := l_sll_rulv_rec;
1813 END LOOP; -- end of loop over assets
1814
1815 ---------------------------------------
1816 -- Calculate difference between contract level payment and temporary total
1817 ---------------------------------------
1818 l_adjustment := l_khr_payment - l_asset_pymnt_total;
1819 ---------------------------------------
1820 -- adjust this on the payment of the asset identified to be adjusted.
1821 ---------------------------------------
1822 IF l_adjustment <> 0 THEN
1823 IF (l_sll_rulv_tbl(l_adj_index).rule_information6 IS NOT NULL) THEN
1824 l_sll_rulv_tbl(l_adj_index).rule_information6 := l_sll_rulv_tbl(l_adj_index).rule_information6 + l_adjustment;
1825 ELSIF (l_sll_rulv_tbl(l_adj_index).rule_information8 IS NOT NULL) THEN
1826 l_sll_rulv_tbl(l_adj_index).rule_information8 := l_sll_rulv_tbl(l_adj_index).rule_information8 + l_adjustment;
1827 END IF;
1828 END IF;
1829
1830 ---------------------------------------
1831 -- Create all SLLs using the table of records of SLLs formed
1832 ---------------------------------------
1833 OKL_RULE_PUB.create_rule(
1834 p_api_version => 1.0,
1835 p_init_msg_list => OKC_API.G_FALSE,
1836 x_return_status => x_return_status,
1837 x_msg_count => x_msg_count,
1838 x_msg_data => x_msg_data,
1839 p_rulv_tbl => l_sll_rulv_tbl,
1840 x_rulv_tbl => x_sll_rulv_new_tbl
1841 );
1842 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1843 x_return_status := OKC_API.G_RET_STS_ERROR;
1844 raise rule_failed;
1845 END IF;
1846 END LOOP; -- end of SLL loop
1847 END LOOP; -- end of SLH loop
1848
1849 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1850 EXCEPTION
1851 WHEN OTHERS THEN
1852 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1853 (
1854 l_api_name,
1855 G_PKG_NAME,
1856 'OTHERS',
1857 x_msg_count,
1858 x_msg_data,
1859 '_PVT'
1860 );
1861 END split_payments;
1862 -- Bug# 7661717 - Added - End
1863
1864 ------------------------------------------------------------------------------
1865 -- PROCEDURE apply_payment
1866 --
1867 -- This procedure proportion-ed the payments accross Financial Asset Top Line
1868 -- and Fee Top Line
1869 --
1870 -- Calls:
1871 -- Called By:
1872 ------------------------------------------------------------------------------
1873 PROCEDURE apply_payment(
1874 p_api_version IN NUMBER,
1875 p_init_msg_list IN VARCHAR2,
1876 x_return_status OUT NOCOPY VARCHAR2,
1877 x_msg_count OUT NOCOPY NUMBER,
1878 x_msg_data OUT NOCOPY VARCHAR2,
1879 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
1880 p_stream_id IN OKC_RULES_V.OBJECT1_ID1%TYPE
1881 ) IS
1882 --Changed query for performance --dkagrawa
1883 CURSOR line_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
1884 SELECT okl.id, okl.capital_amount -- bug# 7661717
1885 FROM okl_k_lines_full_v okl,
1886 okc_line_styles_b ols,
1887 okc_statuses_b okcsts
1888 WHERE okl.dnz_chr_id = p_chr_id
1889 AND okl.lse_id = ols.id
1890 AND ols.lty_code = 'FREE_FORM1'
1891 AND okcsts.code = okl.sts_code
1892 AND okcsts.ste_code NOT IN ('EXPIRED','HOLD','CANCELLED','TERMINATED')
1893 ORDER BY okl.NAME; -- Added Order by for bug# 7661717
1894
1895 CURSOR rg_del_csr (p_chr_id NUMBER,
1896 p_rgp_id NUMBER) IS
1897 SELECT 'Y'
1898 FROM okc_rule_groups_v rg
1899 WHERE NOT EXISTS (SELECT 'Y'
1900 FROM okc_rules_v rule
1901 WHERE rule.rgp_id = rg.id
1902 )
1903 AND rg.dnz_chr_id = p_chr_id
1904 AND rg.rgd_code = 'LALEVL'
1905 AND rg.id = p_rgp_id;
1906
1907 l_api_name VARCHAR2(35) := 'apply_payment';
1908 l_proc_name VARCHAR2(35) := 'APPLY_PAYMENT';
1909 l_api_version CONSTANT NUMBER := 1;
1910 l_precision FND_CURRENCIES.PRECISION%TYPE;
1911
1912 l_del_yn VARCHAR2(1) := 'N';
1913 l_rgpv_tbl OKL_RULE_PUB.rgpv_tbl_type;
1914 l_slh_rulv_tbl OKL_RULE_PUB.rulv_tbl_type;
1915 l_sll_rulv_tbl OKL_RULE_PUB.rulv_tbl_type;
1916
1917 l_rg_count NUMBER := 0;
1918 x_slh_rule_count NUMBER;
1919 l_slh_rule_count NUMBER := 0;
1920 l_sll_rule_count NUMBER := 0;
1921
1922 l_rgpv_del_rec rgpv_rec_type;
1923 x_slh_rulv_tbl_out rulv_tbl_type;
1924 x_sll_rulv_tbl_out rulv_tbl_type;
1925
1926 x_slh_count NUMBER;
1927 x_sll_count NUMBER;
1928
1929 l_slh_rulv_del_tbl rulv_tbl_type;
1930 l_sll_rulv_del_tbl rulv_tbl_type;
1931
1932 l_tot_capital_amount NUMBER := 0;
1933 l_rule_present VARCHAR2(1);
1934
1935 l_line_csr_tbl line_csr_tbl; -- Added Bug# 7661717
1936 BEGIN -- main process begins here
1937 IF (G_DEBUG_ENABLED = 'Y') THEN
1938 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1939 END IF;
1940
1941 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1942 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1943 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1944 END IF;
1945 -- call START_ACTIVITY to create savepoint, check compatibility
1946 -- and initialize message list
1947 x_return_status := OKC_API.START_ACTIVITY(
1948 p_api_name => l_api_name,
1949 p_pkg_name => G_PKG_NAME,
1950 p_init_msg_list => p_init_msg_list,
1951 l_api_version => l_api_version,
1952 p_api_version => p_api_version,
1953 p_api_type => G_API_TYPE,
1954 x_return_status => x_return_status);
1955
1956 -- check if activity started successfully
1957 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1958 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1959 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1960 raise OKC_API.G_EXCEPTION_ERROR;
1961 END IF;
1962
1963 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1964 -- Check for Line Rule, if present don't proceed with Payment Application
1965 check_line_rule(
1966 x_return_status => x_return_status,
1967 x_msg_count => x_msg_count,
1968 x_msg_data => x_msg_data,
1969 p_chr_id => p_chr_id,
1970 p_stream_id => p_stream_id,
1971 x_rule_present => l_rule_present
1972 );
1973 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1974 RAISE OKC_API.G_EXCEPTION_ERROR;
1975 END IF;
1976
1977 IF (l_rule_present = 'Y') THEN -- Rule already present at Line level
1978 okl_api.set_message(
1979 G_APP_NAME,
1980 G_RULE_PRESENT_ERROR
1981 );
1982 RAISE OKC_API.G_EXCEPTION_ERROR;
1983 END IF;
1984
1985 check_header_rule(
1986 x_return_status => x_return_status,
1987 x_msg_count => x_msg_count,
1988 x_msg_data => x_msg_data,
1989 p_chr_id => p_chr_id,
1990 p_stream_id => p_stream_id,
1991 x_rgpv_tbl => l_rgpv_tbl,
1992 x_rg_count => l_rg_count
1993 );
1994
1995 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1996 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1997 END IF;
1998
1999 IF (l_rg_count = 0) THEN -- No Rule Groups
2000 okl_api.set_message(
2001 G_APP_NAME,
2002 G_NO_HEADER_PAYMENT
2003 );
2004 RAISE OKC_API.G_EXCEPTION_ERROR;
2005 END IF;
2006
2007 get_total_capital_amount(
2008 x_return_status => x_return_status,
2009 x_msg_count => x_msg_count,
2010 x_msg_data => x_msg_data,
2011 p_chr_id => p_chr_id,
2012 x_tot_capital_amount => l_tot_capital_amount
2013 );
2014
2015 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2016 okl_api.set_message(
2017 G_APP_NAME,
2018 G_CAPITAL_AMT_ERROR
2019 );
2020 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2021 END IF;
2022
2023 get_currency_precision(
2024 x_return_status => x_return_status,
2025 p_chr_id => p_chr_id,
2026 x_precision => l_precision
2027 );
2028 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2029 RAISE OKC_API.G_EXCEPTION_ERROR;
2030 END IF;
2031
2032 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2033 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'CAP :'||l_tot_capital_amount);
2034 END IF;
2035 -- Bug 7661717 : Start
2036 /*
2037 FOR line_rec IN line_csr(p_chr_id)
2038 LOOP
2039 -- Apply Payment across Lines (Fin Asset and Fee Top Line)
2040 create_line_rule(
2041 x_return_status => x_return_status,
2042 x_msg_count => x_msg_count,
2043 x_msg_data => x_msg_data,
2044 p_chr_id => p_chr_id,
2045 p_cle_id => line_rec.id,
2046 p_stream_id => p_stream_id,
2047 p_fin_line_capital_amount => line_rec.capital_amount,
2048 p_tot_capital_amount => l_tot_capital_amount,
2049 p_precision => l_precision,
2050 p_rgpv_tbl => l_rgpv_tbl,
2051 p_rg_count => l_rg_count,
2052 x_slh_rulv_tbl_out => x_slh_rulv_tbl_out,
2053 x_slh_count => x_slh_count,
2054 x_sll_rulv_tbl_out => x_sll_rulv_tbl_out,
2055 x_sll_count => x_sll_count
2056 );
2057 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2058 RAISE OKC_API.G_EXCEPTION_ERROR;
2059 END IF;
2060
2061 END LOOP;
2062 */ -- Bug 7661717 : Start
2063 -- Bug# 7661717 - Added - Start
2064 ---------------------------------------
2065 -- Get all of contract lines into a table of records
2066 ---------------------------------------
2067 OPEN line_csr(p_chr_id);
2068 FETCH line_csr BULK COLLECT INTO l_line_csr_tbl;
2069 CLOSE line_csr;
2070
2071 -- Call split payments method to split the SLH and SLLs among assets
2072 split_payments(
2073 p_api_version => p_api_version,
2074 p_init_msg_list => p_init_msg_list,
2075 p_chr_id => p_chr_id,
2076 p_line_csr_tbl => l_line_csr_tbl,
2077 p_stream_id => p_stream_id,
2078 p_tot_capital_amount => l_tot_capital_amount,
2079 p_precision => l_precision,
2080 p_rgpv_tbl => l_rgpv_tbl,
2081 x_slh_rulv_tbl_out => x_slh_rulv_tbl_out,
2082 x_slh_count => x_slh_count,
2083 x_sll_rulv_tbl_out => x_sll_rulv_tbl_out,
2084 x_sll_count => x_sll_count,
2085 x_return_status => x_return_status,
2086 x_msg_count => x_msg_count,
2087 x_msg_data => x_msg_data
2088 );
2089 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2090 RAISE OKC_API.G_EXCEPTION_ERROR;
2091 END IF;
2092 -- Bug# 7661717 - Added - End
2093
2094 FOR i IN 1..x_slh_count
2095 LOOP
2096 l_slh_rulv_del_tbl(i).id := x_slh_rulv_tbl_out(i).id;
2097 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2098 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'SLH delete: '||x_slh_rulv_tbl_out(i).id);
2099 END IF;
2100 END LOOP; -- SLH
2101 okl_rule_pub.delete_rule(
2102 p_api_version => 1.0,
2103 p_init_msg_list => p_init_msg_list,
2104 x_return_status => x_return_status,
2105 x_msg_count => x_msg_count,
2106 x_msg_data => x_msg_data,
2107 p_rulv_tbl => l_slh_rulv_del_tbl
2108 );
2109
2110 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2111 RAISE OKC_API.G_EXCEPTION_ERROR;
2112 END IF;
2113
2114 FOR i IN 1..x_sll_count
2115 LOOP
2116 l_sll_rulv_del_tbl(i).id := x_sll_rulv_tbl_out(i).id;
2117 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2118 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'SLL delete: '||x_sll_rulv_tbl_out(i).id);
2119 END IF;
2120 END LOOP; -- SLL
2121 okl_rule_pub.delete_rule(
2122 p_api_version => 1.0,
2123 p_init_msg_list => p_init_msg_list,
2124 x_return_status => x_return_status,
2125 x_msg_count => x_msg_count,
2126 x_msg_data => x_msg_data,
2127 p_rulv_tbl => l_sll_rulv_del_tbl
2128 );
2129
2130 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2131 RAISE OKC_API.G_EXCEPTION_ERROR;
2132 END IF;
2133
2134
2135 --
2136 -- Delete Header Payment Rule
2137 -- only if there is no other stream type present
2138 --
2139 FOR i IN 1..l_rg_count
2140 LOOP
2141 l_del_yn := 'N';
2142 OPEN rg_del_csr(p_chr_id, l_rgpv_tbl(i).id);
2143 FETCH rg_del_csr INTO l_del_yn;
2144 CLOSE rg_del_csr;
2145 IF (l_del_yn = 'Y') THEN
2146 l_rgpv_del_rec.id := l_rgpv_tbl(i).id;
2147 okl_rule_pub.delete_rule_group(
2148 p_api_version => 1.0,
2149 p_init_msg_list => p_init_msg_list,
2150 x_return_status => x_return_status,
2151 x_msg_count => x_msg_count,
2152 x_msg_data => x_msg_data,
2153 p_rgpv_rec => l_rgpv_del_rec
2154 );
2155 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2156 RAISE OKC_API.G_EXCEPTION_ERROR;
2157 END IF;
2158 END IF;
2159 END LOOP;
2160
2161 --Call End Activity
2162 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2163 x_msg_data => x_msg_data);
2164
2165
2166 EXCEPTION
2167 when OKC_API.G_EXCEPTION_ERROR then
2168 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2169 p_api_name => l_api_name,
2170 p_pkg_name => G_PKG_NAME,
2171 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2172 x_msg_count => x_msg_count,
2173 x_msg_data => x_msg_data,
2174 p_api_type => G_API_TYPE);
2175
2176 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2177 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2178 p_api_name => l_api_name,
2179 p_pkg_name => G_PKG_NAME,
2180 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2181 x_msg_count => x_msg_count,
2182 x_msg_data => x_msg_data,
2183 p_api_type => G_API_TYPE);
2184
2185 when OTHERS then
2186 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2187 p_api_name => l_api_name,
2188 p_pkg_name => G_PKG_NAME,
2189 p_exc_name => 'OTHERS',
2190 x_msg_count => x_msg_count,
2191 x_msg_data => x_msg_data,
2192 p_api_type => G_API_TYPE);
2193
2194 END apply_payment;
2195
2196
2197 ------------------------------------------------------------------------------
2198 -- PROCEDURE apply_propery_tax_payment
2199 --
2200 -- This procedure proportion-ed the payments accross Financial Asset Top Line
2201 -- for Property Tax Payment
2202 --
2203 -- Calls:
2204 -- Called By: OKL_LA_PAYMENTS_PVT
2205 ------------------------------------------------------------------------------
2206 PROCEDURE apply_propery_tax_payment(
2207 p_api_version IN NUMBER,
2208 p_init_msg_list IN VARCHAR2,
2209 x_return_status OUT NOCOPY VARCHAR2,
2210 x_msg_count OUT NOCOPY NUMBER,
2211 x_msg_data OUT NOCOPY VARCHAR2,
2212 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
2213 p_stream_id IN OKC_RULES_V.OBJECT1_ID1%TYPE
2214 ) IS
2215 CURSOR line_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
2216 SELECT okl.id, okl.capital_amount
2217 FROM okl_k_lines_full_v okl,
2218 okc_line_styles_v ols,
2219 okc_rule_groups_b rgp,
2220 okc_rules_b rul
2221 WHERE okl.dnz_chr_id = p_chr_id
2222 AND okl.lse_id = ols.id
2223 AND ols.lty_code = 'FREE_FORM1'
2224 --AND rul.rule_information_category = 'LAASTK'
2225 AND rul.rule_information_category = 'LAPRTX' -- Bug 3987623
2226 AND rul.dnz_chr_id = p_chr_id
2227 AND rul.rgp_id = rgp.id
2228 AND rul.rule_information1 = 'Y'
2229 AND NVL(rul.rule_information3,'XXX') IN ('ESTIMATED','ESTIMATED_AND_ACTUAL')
2230 AND rgp.rgd_code = 'LAASTX'
2231 AND rgp.dnz_chr_id = p_chr_id
2232 AND rgp.chr_id IS NULL
2233 AND rgp.cle_id = okl.id
2234 -- added to handle abandon line
2235 AND NOT EXISTS (
2236 SELECT 'Y'
2237 FROM okc_statuses_v okcsts
2238 WHERE okcsts.code = okl.sts_code
2239 AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED'));
2240
2241 line_rec line_csr%ROWTYPE;
2242
2243 CURSOR rg_del_csr (p_chr_id NUMBER,
2244 p_rgp_id NUMBER) IS
2245 SELECT 'Y'
2246 FROM okc_rule_groups_v rg
2247 WHERE NOT EXISTS (SELECT 'Y'
2248 FROM okc_rules_v rule
2249 WHERE rule.rgp_id = rg.id
2250 )
2251 AND rg.dnz_chr_id = p_chr_id
2252 AND rg.rgd_code = 'LALEVL'
2253 AND rg.id = p_rgp_id;
2254
2255 CURSOR line_cap_csr (p_chr_id NUMBER) IS
2256 SELECT SUM(NVL(line.oec,0)) tot_cap
2257 FROM okl_k_lines_full_v line,
2258 okc_line_styles_v style,
2259 okc_statuses_v okcsts,
2260 okc_rules_b rule,
2261 okc_rule_groups_b rgp
2262 WHERE line.dnz_chr_id = p_chr_id
2263 AND line.lse_id = style.id
2264 AND style.lty_code = 'FREE_FORM1'
2265 AND okcsts.code = line.sts_code
2266 AND okcsts.ste_code NOT IN ('EXPIRED','HOLD','CANCELLED','TERMINATED')
2267 AND rule.rgp_id = rgp.id
2268 AND rgp.dnz_chr_id = line.dnz_chr_id
2269 AND rgp.rgd_code = 'LAASTX'
2270 AND rule.rule_information_category = 'LAPRTX'
2271 AND rule.rule_information1 = 'Y'
2272 AND NVL(rule.rule_information3,'XXX') IN ('ESTIMATED','ESTIMATED_AND_ACTUAL')
2273 AND rgp.cle_id = line.id
2274 AND rgp.chr_id IS NULL;
2275
2276 l_api_name VARCHAR2(35) := 'apply_propery_tax_payment';
2277 l_proc_name VARCHAR2(35) := 'apply_propery_tax_payment';
2278 l_api_version CONSTANT NUMBER := 1;
2279 l_precision FND_CURRENCIES.PRECISION%TYPE;
2280
2281 l_del_yn VARCHAR2(1) := 'N';
2282 l_rgpv_tbl OKL_RULE_PUB.rgpv_tbl_type;
2283 l_slh_rulv_tbl OKL_RULE_PUB.rulv_tbl_type;
2284 l_sll_rulv_tbl OKL_RULE_PUB.rulv_tbl_type;
2285
2286 l_rg_count NUMBER := 0;
2287 l_slh_rule_count NUMBER := 0;
2288 l_sll_rule_count NUMBER := 0;
2289
2290 l_rgpv_del_rec rgpv_rec_type;
2291 x_slh_rulv_tbl_out rulv_tbl_type;
2292 x_sll_rulv_tbl_out rulv_tbl_type;
2293
2294 x_slh_count NUMBER;
2295 x_sll_count NUMBER;
2296
2297 l_slh_rulv_del_tbl rulv_tbl_type;
2298 l_sll_rulv_del_tbl rulv_tbl_type;
2299
2300 l_tot_capital_amount NUMBER := 0;
2301 l_rule_present VARCHAR2(1);
2302 rowCnt NUMBER DEFAULT 0;
2303 l_line_count NUMBER := 0;
2304
2305 BEGIN -- main process begins here
2306 IF (G_DEBUG_ENABLED = 'Y') THEN
2307 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2308 END IF;
2309
2310 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2311 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2312 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
2313 END IF;
2314 -- call START_ACTIVITY to create savepoint, check compatibility
2315 -- and initialize message list
2316 x_return_status := OKC_API.START_ACTIVITY(
2317 p_api_name => l_api_name,
2318 p_pkg_name => G_PKG_NAME,
2319 p_init_msg_list => p_init_msg_list,
2320 l_api_version => l_api_version,
2321 p_api_version => p_api_version,
2322 p_api_type => G_API_TYPE,
2323 x_return_status => x_return_status);
2324
2325 -- check if activity started successfully
2326 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2327 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2328 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
2329 raise OKC_API.G_EXCEPTION_ERROR;
2330 END IF;
2331
2332 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2333 -- Check for Line Rule, if present don't proceed with Payment Application
2334 check_line_rule(
2335 x_return_status => x_return_status,
2336 x_msg_count => x_msg_count,
2337 x_msg_data => x_msg_data,
2338 p_chr_id => p_chr_id,
2339 p_stream_id => p_stream_id,
2340 x_rule_present => l_rule_present
2341 );
2342 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2343 RAISE OKC_API.G_EXCEPTION_ERROR;
2344 END IF;
2345
2346 IF (l_rule_present = 'Y') THEN -- Rule already present at Line level
2347 okl_api.set_message(
2348 G_APP_NAME,
2349 G_RULE_PRESENT_ERROR
2350 );
2351 RAISE OKC_API.G_EXCEPTION_ERROR;
2352 END IF;
2353
2354 check_header_rule(
2355 x_return_status => x_return_status,
2356 x_msg_count => x_msg_count,
2357 x_msg_data => x_msg_data,
2358 p_chr_id => p_chr_id,
2359 p_stream_id => p_stream_id,
2360 x_rgpv_tbl => l_rgpv_tbl,
2361 x_rg_count => l_rg_count
2362 );
2363
2364 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2365 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2366 END IF;
2367
2368 IF (l_rg_count = 0) THEN -- No Rule Groups
2369 okl_api.set_message(
2370 G_APP_NAME,
2371 G_NO_HEADER_PAYMENT
2372 );
2373 RAISE OKC_API.G_EXCEPTION_ERROR;
2374 END IF;
2375
2376 l_tot_capital_amount := 0;
2377 FOR line_cap_rec IN line_cap_csr (p_chr_id)
2378 LOOP
2379 l_tot_capital_amount := line_cap_rec.tot_cap;
2380 END LOOP;
2381
2382 get_currency_precision(
2383 x_return_status => x_return_status,
2384 p_chr_id => p_chr_id,
2385 x_precision => l_precision
2386 );
2387 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2388 RAISE OKC_API.G_EXCEPTION_ERROR;
2389 END IF;
2390
2391 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2392 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'CAP :'||l_tot_capital_amount);
2393 END IF;
2394
2395 l_line_count := 0; -- number of lines eligible for Prop tax application
2396 FOR line_rec IN line_csr(p_chr_id)
2397 LOOP
2398 l_line_count := l_line_count + 1;
2399 -- Apply Payment across Lines (Fin Asset and Fee Top Line)
2400 create_line_rule(
2401 x_return_status => x_return_status,
2402 x_msg_count => x_msg_count,
2403 x_msg_data => x_msg_data,
2404 p_chr_id => p_chr_id,
2405 p_cle_id => line_rec.id,
2406 p_stream_id => p_stream_id,
2407 p_fin_line_capital_amount => line_rec.capital_amount,
2408 p_tot_capital_amount => l_tot_capital_amount,
2409 p_precision => l_precision,
2410 p_rgpv_tbl => l_rgpv_tbl,
2411 p_rg_count => l_rg_count,
2412 x_slh_rulv_tbl_out => x_slh_rulv_tbl_out,
2413 x_slh_count => x_slh_count,
2414 x_sll_rulv_tbl_out => x_sll_rulv_tbl_out,
2415 x_sll_count => x_sll_count
2416 );
2417 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2418 RAISE OKC_API.G_EXCEPTION_ERROR;
2419 END IF;
2420
2421 END LOOP;
2422
2423 IF ( l_line_count = 0 ) THEN
2424 okl_api.set_message(
2425 G_APP_NAME,
2426 'OKL_NO_PROP_TAX_AST'
2427 );
2428 RAISE OKC_API.G_EXCEPTION_ERROR;
2429 END IF;
2430
2431 FOR i IN 1..x_slh_count
2432 LOOP
2433 l_slh_rulv_del_tbl(i).id := x_slh_rulv_tbl_out(i).id;
2434 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2435 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'SLH delete: '||x_slh_rulv_tbl_out(i).id);
2436 END IF;
2437 END LOOP; -- SLH
2438 okl_rule_pub.delete_rule(
2439 p_api_version => 1.0,
2440 p_init_msg_list => p_init_msg_list,
2441 x_return_status => x_return_status,
2442 x_msg_count => x_msg_count,
2443 x_msg_data => x_msg_data,
2444 p_rulv_tbl => l_slh_rulv_del_tbl
2445 );
2446
2447 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2448 RAISE OKC_API.G_EXCEPTION_ERROR;
2449 END IF;
2450
2451 FOR i IN 1..x_sll_count
2452 LOOP
2453 l_sll_rulv_del_tbl(i).id := x_sll_rulv_tbl_out(i).id;
2454 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2455 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'SLL delete: '||x_sll_rulv_tbl_out(i).id);
2456 END IF;
2457 END LOOP; -- SLL
2458 okl_rule_pub.delete_rule(
2459 p_api_version => 1.0,
2460 p_init_msg_list => p_init_msg_list,
2461 x_return_status => x_return_status,
2462 x_msg_count => x_msg_count,
2463 x_msg_data => x_msg_data,
2464 p_rulv_tbl => l_sll_rulv_del_tbl
2465 );
2466
2467 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2468 RAISE OKC_API.G_EXCEPTION_ERROR;
2469 END IF;
2470
2471 --
2472 -- Delete Header Payment Rule
2473 -- only if there is no other stream type present
2474 --
2475 FOR i IN 1..l_rg_count
2476 LOOP
2477 l_del_yn := 'N';
2478 OPEN rg_del_csr(p_chr_id, l_rgpv_tbl(i).id);
2479 FETCH rg_del_csr INTO l_del_yn;
2480 CLOSE rg_del_csr;
2481 IF (l_del_yn = 'Y') THEN
2482 l_rgpv_del_rec.id := l_rgpv_tbl(i).id;
2483 okl_rule_pub.delete_rule_group(
2484 p_api_version => 1.0,
2485 p_init_msg_list => p_init_msg_list,
2486 x_return_status => x_return_status,
2487 x_msg_count => x_msg_count,
2488 x_msg_data => x_msg_data,
2489 p_rgpv_rec => l_rgpv_del_rec
2490 );
2491 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2492 RAISE OKC_API.G_EXCEPTION_ERROR;
2493 END IF;
2494 END IF;
2495 END LOOP;
2496
2497 --Call End Activity
2498 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2499 x_msg_data => x_msg_data);
2500
2501
2502 EXCEPTION
2503 when OKC_API.G_EXCEPTION_ERROR then
2504 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2505 p_api_name => l_api_name,
2506 p_pkg_name => G_PKG_NAME,
2507 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2508 x_msg_count => x_msg_count,
2509 x_msg_data => x_msg_data,
2510 p_api_type => G_API_TYPE);
2511
2512 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2513 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2514 p_api_name => l_api_name,
2515 p_pkg_name => G_PKG_NAME,
2516 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2517 x_msg_count => x_msg_count,
2518 x_msg_data => x_msg_data,
2519 p_api_type => G_API_TYPE);
2520
2521 when OTHERS then
2522 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2523 p_api_name => l_api_name,
2524 p_pkg_name => G_PKG_NAME,
2525 p_exc_name => 'OTHERS',
2526 x_msg_count => x_msg_count,
2527 x_msg_data => x_msg_data,
2528 p_api_type => G_API_TYPE);
2529
2530 END apply_propery_tax_payment;
2531
2532
2533 ------------------------------------------------------------------------------
2534 -- PROCEDURE apply_rollover_fee_payment
2535 --
2536 -- This procedure applies the payments proportionately accross Rollover
2537 -- Fee Sub-Lines (Assets attachecd to the Fee top line)
2538 -- for the Rollover Fee Top line.
2539 --
2540 -- Calls:
2541 -- Created By: Manu 09-Sep-2004
2542 -- Called By: OKL_LA_PAYMENTS_PVT
2543 ------------------------------------------------------------------------------
2544 PROCEDURE apply_rollover_fee_payment(
2545 p_api_version IN NUMBER,
2546 p_init_msg_list IN VARCHAR2,
2547 x_return_status OUT NOCOPY VARCHAR2,
2548 x_msg_count OUT NOCOPY NUMBER,
2549 x_msg_data OUT NOCOPY VARCHAR2,
2550 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
2551 p_kle_id IN OKC_K_LINES_B.ID%TYPE,-- Rollover Fee Top Line
2552 p_stream_id IN OKC_RULES_V.OBJECT1_ID1%TYPE
2553 ) IS
2554 CURSOR ro_fee_subline_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
2555 p_kle_id OKC_K_LINES_B.ID%TYPE) IS
2556 SELECT kle.id, kle.amount
2557 FROM okc_k_lines_b cleb,
2558 okl_k_lines kle
2559 WHERE cleb.dnz_chr_id = p_chr_id
2560 AND kle.ID = cleb.ID
2561 AND cleb.CLE_ID = p_kle_id
2562 -- AND cleb.sts_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED');
2563 AND NOT EXISTS (
2564 SELECT 'Y'
2565 FROM okc_statuses_v okcsts
2566 WHERE okcsts.code = cleb.sts_code
2567 AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED'));
2568
2569 -- Total Fee amount for the rollover fee top line.
2570
2571 CURSOR fee_amount_csr (p_kle_id OKL_K_LINES.ID%TYPE) IS
2572 SELECT NVL(kle.amount,0)
2573 FROM okl_k_lines kle
2574 WHERE kle.ID = p_kle_id;
2575
2576 -- Cursor to get fee top line name.
2577
2578 CURSOR fee_name_csr (p_kle_id OKL_K_LINES.ID%TYPE) IS
2579 SELECT kle.name
2580 FROM okc_k_lines_v kle
2581 WHERE kle.ID = p_kle_id;
2582
2583 CURSOR rg_del_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
2584 p_rgp_id NUMBER) IS
2585 SELECT 'Y'
2586 FROM okc_rule_groups_v rg
2587 WHERE NOT EXISTS (SELECT 'Y'
2588 FROM okc_rules_v rule
2589 WHERE rule.rgp_id = rg.id
2590 )
2591 AND rg.dnz_chr_id = p_chr_id
2592 AND rg.rgd_code = 'LALEVL'
2593 AND rg.id = p_rgp_id;
2594
2595 l_api_name VARCHAR2(35) := 'apply_rollover_fee_payment';
2596 l_proc_name VARCHAR2(35) := 'apply_rollover_fee_payment';
2597 l_api_version CONSTANT NUMBER := 1;
2598 l_precision FND_CURRENCIES.PRECISION%TYPE;
2599
2600 l_del_yn VARCHAR2(1) := 'N';
2601 l_rgpv_tbl OKL_RULE_PUB.rgpv_tbl_type;
2602 l_slh_rulv_tbl OKL_RULE_PUB.rulv_tbl_type;
2603 l_sll_rulv_tbl OKL_RULE_PUB.rulv_tbl_type;
2604
2605 l_rg_count NUMBER := 0;
2606 l_slh_rule_count NUMBER := 0;
2607 l_sll_rule_count NUMBER := 0;
2608
2609 l_rgpv_del_rec rgpv_rec_type;
2610 x_slh_rulv_tbl_out rulv_tbl_type;
2611 x_sll_rulv_tbl_out rulv_tbl_type;
2612
2613 x_slh_count NUMBER;
2614 x_sll_count NUMBER;
2615
2616 l_slh_rulv_del_tbl rulv_tbl_type;
2617 l_sll_rulv_del_tbl rulv_tbl_type;
2618
2619 l_tot_fee_amount NUMBER := 0;
2620 l_rule_present VARCHAR2(1);
2621 ro_fee_subline_rec ro_fee_subline_csr%ROWTYPE;
2622 l_fee_name OKC_K_LINES_V.NAME%TYPE;
2623 rowCnt NUMBER DEFAULT 0;
2624
2625 BEGIN -- main process begins here
2626 IF (G_DEBUG_ENABLED = 'Y') THEN
2627 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2628 END IF;
2629
2630 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2631 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2632 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
2633 END IF;
2634 -- call START_ACTIVITY to create savepoint, check compatibility
2635 -- and initialize message list
2636 x_return_status := OKC_API.START_ACTIVITY(
2637 p_api_name => l_api_name,
2638 p_pkg_name => G_PKG_NAME,
2639 p_init_msg_list => p_init_msg_list,
2640 l_api_version => l_api_version,
2641 p_api_version => p_api_version,
2642 p_api_type => G_API_TYPE,
2643 x_return_status => x_return_status);
2644
2645 -- check if activity started successfully
2646 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2647 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2648 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
2649 raise OKC_API.G_EXCEPTION_ERROR;
2650 END IF;
2651
2652 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2653 -- Check if payments already exists for fee-sublines for the given rollover
2654 -- fee top line, if present don't proceed with Payment Application
2655 check_ro_subline_rule(
2656 x_return_status => x_return_status,
2657 x_msg_count => x_msg_count,
2658 x_msg_data => x_msg_data,
2659 p_chr_id => p_chr_id,
2660 p_cle_id => p_kle_id,
2661 p_stream_id => p_stream_id,
2662 x_rule_present => l_rule_present
2663 );
2664 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2665 RAISE OKC_API.G_EXCEPTION_ERROR;
2666 END IF;
2667 IF (l_rule_present IS NOT NULL AND l_rule_present = 'Y') THEN -- Rule already present at Line level
2668 okl_api.set_message(
2669 G_APP_NAME,
2670 G_RULE_PRESENT_ERROR
2671 );
2672 RAISE OKC_API.G_EXCEPTION_ERROR;
2673 END IF;
2674
2675 get_ro_fee_topln_rg(
2676 p_api_version => 1.0,
2677 p_init_msg_list => OKL_API.G_FALSE,
2678 p_chr_id => p_chr_id,
2679 p_cle_id => p_kle_id,
2680 p_stream_id => p_stream_id,
2681 p_rgd_code => 'LALEVL',
2682 x_return_status => x_return_status,
2683 x_msg_count => x_msg_count,
2684 x_msg_data => x_msg_data,
2685 x_rgpv_tbl => l_rgpv_tbl,
2686 x_rg_count => l_rg_count);
2687 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2688 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2689 END IF;
2690
2691
2692 IF (l_rg_count = 0) THEN -- No Rule Groups
2693 okl_api.set_message(
2694 G_APP_NAME,
2695 G_NO_HEADER_PAYMENT
2696 );
2697 RAISE OKC_API.G_EXCEPTION_ERROR;
2698 END IF;
2699
2700 l_tot_fee_amount := 0;
2701 OPEN fee_amount_csr(p_kle_id => p_kle_id);
2702 FETCH fee_amount_csr INTO l_tot_fee_amount;
2703 CLOSE fee_amount_csr;
2704
2705
2706 get_currency_precision(
2707 x_return_status => x_return_status,
2708 p_chr_id => p_chr_id,
2709 x_precision => l_precision
2710 );
2711 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2712 RAISE OKC_API.G_EXCEPTION_ERROR;
2713 END IF;
2714
2715 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2716 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'CAP :'||l_tot_fee_amount);
2717 END IF;
2718
2719 OPEN fee_name_csr (p_kle_id);
2720 FETCH fee_name_csr INTO l_fee_name;
2721 CLOSE fee_name_csr;
2722
2723 OPEN ro_fee_subline_csr(p_chr_id, p_kle_id);
2724 LOOP
2725 FETCH ro_fee_subline_csr INTO
2726 ro_fee_subline_rec.id,
2727 ro_fee_subline_rec.amount;
2728 EXIT WHEN ro_fee_subline_csr%NOTFOUND;
2729 rowCnt := ro_fee_subline_csr%ROWCOUNT;
2730 -- Apply Payment across Sub-Lines (Fee Asset Lines)
2731 create_line_rule(
2732 x_return_status => x_return_status,
2733 x_msg_count => x_msg_count,
2734 x_msg_data => x_msg_data,
2735 p_chr_id => p_chr_id,
2736 p_cle_id => ro_fee_subline_rec.id,
2737 p_stream_id => p_stream_id,
2738 p_fin_line_capital_amount => ro_fee_subline_rec.amount, -- Sub-line fee Amount
2739 p_tot_capital_amount => l_tot_fee_amount, -- Total top Fee line Amount
2740 p_precision => l_precision,
2741 p_rgpv_tbl => l_rgpv_tbl,
2742 p_rg_count => l_rg_count,
2743 x_slh_rulv_tbl_out => x_slh_rulv_tbl_out,
2744 x_slh_count => x_slh_count,
2745 x_sll_rulv_tbl_out => x_sll_rulv_tbl_out,
2746 x_sll_count => x_sll_count
2747 );
2748 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2749 RAISE OKC_API.G_EXCEPTION_ERROR;
2750 END IF;
2751 END LOOP;
2752 CLOSE ro_fee_subline_csr;
2753
2754 -- NO sub-lines found, error out.
2755 IF ( rowCnt < 1 ) THEN
2756 okl_api.set_message(
2757 p_app_name => G_APP_NAME,
2758 p_msg_name => 'OKL_RO_NO_SUB_LNS',
2759 p_token1 => 'FEE_LINE',
2760 p_token1_value => l_fee_name
2761 );
2762 RAISE OKC_API.G_EXCEPTION_ERROR;
2763 END IF;
2764
2765 FOR i IN 1..x_slh_count
2766 LOOP
2767 l_slh_rulv_del_tbl(i).id := x_slh_rulv_tbl_out(i).id;
2768 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2769 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'SLH delete: '||x_slh_rulv_tbl_out(i).id);
2770 END IF;
2771 END LOOP; -- SLH
2772 okl_rule_pub.delete_rule(
2773 p_api_version => 1.0,
2774 p_init_msg_list => p_init_msg_list,
2775 x_return_status => x_return_status,
2776 x_msg_count => x_msg_count,
2777 x_msg_data => x_msg_data,
2778 p_rulv_tbl => l_slh_rulv_del_tbl
2779 );
2780
2781 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2782 RAISE OKC_API.G_EXCEPTION_ERROR;
2783 END IF;
2784
2785 FOR i IN 1..x_sll_count
2786 LOOP
2787 l_sll_rulv_del_tbl(i).id := x_sll_rulv_tbl_out(i).id;
2788 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2789 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'SLL delete: '||x_sll_rulv_tbl_out(i).id);
2790 END IF;
2791 END LOOP; -- SLL
2792 okl_rule_pub.delete_rule(
2793 p_api_version => 1.0,
2794 p_init_msg_list => p_init_msg_list,
2795 x_return_status => x_return_status,
2796 x_msg_count => x_msg_count,
2797 x_msg_data => x_msg_data,
2798 p_rulv_tbl => l_sll_rulv_del_tbl
2799 );
2800
2801 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2802 RAISE OKC_API.G_EXCEPTION_ERROR;
2803 END IF;
2804
2805
2806 --
2807 -- Delete Header Payment Rule
2808 -- only if there is no other stream type present
2809 --
2810 FOR i IN 1..l_rg_count
2811 LOOP
2812 l_del_yn := 'N';
2813 OPEN rg_del_csr(p_chr_id, l_rgpv_tbl(i).id);
2814 FETCH rg_del_csr INTO l_del_yn;
2815 CLOSE rg_del_csr;
2816 IF (l_del_yn = 'Y') THEN
2817 l_rgpv_del_rec.id := l_rgpv_tbl(i).id;
2818 okl_rule_pub.delete_rule_group(
2819 p_api_version => 1.0,
2820 p_init_msg_list => p_init_msg_list,
2821 x_return_status => x_return_status,
2822 x_msg_count => x_msg_count,
2823 x_msg_data => x_msg_data,
2824 p_rgpv_rec => l_rgpv_del_rec
2825 );
2826 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2827 RAISE OKC_API.G_EXCEPTION_ERROR;
2828 END IF;
2829 END IF;
2830 END LOOP;
2831
2832 --Call End Activity
2833 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2834 x_msg_data => x_msg_data);
2835
2836
2837 EXCEPTION
2838 when OKC_API.G_EXCEPTION_ERROR then
2839 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2840 p_api_name => l_api_name,
2841 p_pkg_name => G_PKG_NAME,
2842 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2843 x_msg_count => x_msg_count,
2844 x_msg_data => x_msg_data,
2845 p_api_type => G_API_TYPE);
2846
2847 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2848 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2849 p_api_name => l_api_name,
2850 p_pkg_name => G_PKG_NAME,
2851 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2852 x_msg_count => x_msg_count,
2853 x_msg_data => x_msg_data,
2854 p_api_type => G_API_TYPE);
2855
2856 when OTHERS then
2857 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2858 p_api_name => l_api_name,
2859 p_pkg_name => G_PKG_NAME,
2860 p_exc_name => 'OTHERS',
2861 x_msg_count => x_msg_count,
2862 x_msg_data => x_msg_data,
2863 p_api_type => G_API_TYPE);
2864
2865 END apply_rollover_fee_payment;
2866
2867 -- start: cklee: okl.h
2868 ------------------------------------------------------------------------------
2869 -- PROCEDURE apply_eligible_fee_payment
2870 --
2871 -- This procedure applies the payments proportionately accross
2872 -- Fee Sub-Lines (Assets attachecd to the Fee top line)
2873 -- for the Fee Top line.
2874 --
2875 -- Calls:
2876 -- Created By: cklee 22-Jun-2005
2877 -- Called By: OKL_LA_PAYMENTS_PVT
2878 ------------------------------------------------------------------------------
2879 PROCEDURE apply_eligible_fee_payment(
2880 p_api_version IN NUMBER,
2881 p_init_msg_list IN VARCHAR2,
2882 x_return_status OUT NOCOPY VARCHAR2,
2883 x_msg_count OUT NOCOPY NUMBER,
2884 x_msg_data OUT NOCOPY VARCHAR2,
2885 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
2886 p_kle_id IN OKC_K_LINES_B.ID%TYPE,-- Fee Top Line
2887 p_stream_id IN OKC_RULES_V.OBJECT1_ID1%TYPE
2888 ) IS
2889 CURSOR ro_fee_subline_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
2890 p_kle_id OKC_K_LINES_B.ID%TYPE) IS
2891 SELECT kle.id, kle.amount
2892 FROM okc_k_lines_b cleb,
2893 okl_k_lines kle
2894 WHERE cleb.dnz_chr_id = p_chr_id
2895 AND kle.ID = cleb.ID
2896 AND cleb.CLE_ID = p_kle_id
2897 -- AND cleb.sts_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED');
2898 AND NOT EXISTS (
2899 SELECT 'Y'
2900 FROM okc_statuses_v okcsts
2901 WHERE okcsts.code = cleb.sts_code
2902 AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED'));
2903
2904 -- Total Fee amount for the rollover fee top line.
2905
2906 CURSOR fee_amount_csr (p_kle_id OKL_K_LINES.ID%TYPE) IS
2907 SELECT NVL(kle.amount,0)
2908 FROM okl_k_lines kle
2909 WHERE kle.ID = p_kle_id;
2910
2911 -- Cursor to get fee top line name.
2912
2913 CURSOR fee_name_csr (p_kle_id OKL_K_LINES.ID%TYPE) IS
2914 SELECT kle.name
2915 FROM okc_k_lines_v kle
2916 WHERE kle.ID = p_kle_id;
2917
2918 CURSOR rg_del_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
2919 p_rgp_id NUMBER) IS
2920 SELECT 'Y'
2921 FROM okc_rule_groups_v rg
2922 WHERE NOT EXISTS (SELECT 'Y'
2923 FROM okc_rules_v rule
2924 WHERE rule.rgp_id = rg.id
2925 )
2926 AND rg.dnz_chr_id = p_chr_id
2927 AND rg.rgd_code = 'LALEVL'
2928 AND rg.id = p_rgp_id;
2929
2930 l_api_name VARCHAR2(35) := 'apply_eligible_fee_payment';
2931 l_proc_name VARCHAR2(35) := 'apply_eligible_fee_payment';
2932 l_api_version CONSTANT NUMBER := 1;
2933 l_precision FND_CURRENCIES.PRECISION%TYPE;
2934
2935 l_del_yn VARCHAR2(1) := 'N';
2936 l_rgpv_tbl OKL_RULE_PUB.rgpv_tbl_type;
2937 l_slh_rulv_tbl OKL_RULE_PUB.rulv_tbl_type;
2938 l_sll_rulv_tbl OKL_RULE_PUB.rulv_tbl_type;
2939
2940 l_rg_count NUMBER := 0;
2941 l_slh_rule_count NUMBER := 0;
2942 l_sll_rule_count NUMBER := 0;
2943
2944 l_rgpv_del_rec rgpv_rec_type;
2945 x_slh_rulv_tbl_out rulv_tbl_type;
2946 x_sll_rulv_tbl_out rulv_tbl_type;
2947
2948 x_slh_count NUMBER;
2949 x_sll_count NUMBER;
2950
2951 l_slh_rulv_del_tbl rulv_tbl_type;
2952 l_sll_rulv_del_tbl rulv_tbl_type;
2953
2954 l_tot_fee_amount NUMBER := 0;
2955 l_rule_present VARCHAR2(1);
2956 ro_fee_subline_rec ro_fee_subline_csr%ROWTYPE;
2957 l_fee_name OKC_K_LINES_V.NAME%TYPE;
2958 rowCnt NUMBER DEFAULT 0;
2959
2960 BEGIN -- main process begins here
2961 IF (G_DEBUG_ENABLED = 'Y') THEN
2962 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2963 END IF;
2964
2965 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2966 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2967 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
2968 END IF;
2969 -- call START_ACTIVITY to create savepoint, check compatibility
2970 -- and initialize message list
2971 x_return_status := OKC_API.START_ACTIVITY(
2972 p_api_name => l_api_name,
2973 p_pkg_name => G_PKG_NAME,
2974 p_init_msg_list => p_init_msg_list,
2975 l_api_version => l_api_version,
2976 p_api_version => p_api_version,
2977 p_api_type => G_API_TYPE,
2978 x_return_status => x_return_status);
2979
2980 -- check if activity started successfully
2981 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2982 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2983 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
2984 raise OKC_API.G_EXCEPTION_ERROR;
2985 END IF;
2986
2987 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2988 -- Check if payments already exists for fee-sublines for the given
2989 -- fee top line, if present don't proceed with Payment Application
2990 check_ro_subline_rule(
2991 x_return_status => x_return_status,
2992 x_msg_count => x_msg_count,
2993 x_msg_data => x_msg_data,
2994 p_chr_id => p_chr_id,
2995 p_cle_id => p_kle_id,
2996 p_stream_id => p_stream_id,
2997 x_rule_present => l_rule_present
2998 );
2999 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3000 RAISE OKC_API.G_EXCEPTION_ERROR;
3001 END IF;
3002 IF (l_rule_present IS NOT NULL AND l_rule_present = 'Y') THEN -- Rule already present at Line level
3003 okl_api.set_message(
3004 G_APP_NAME,
3005 G_RULE_PRESENT_ERROR
3006 );
3007 RAISE OKC_API.G_EXCEPTION_ERROR;
3008 END IF;
3009
3010 get_ro_fee_topln_rg(
3011 p_api_version => 1.0,
3012 p_init_msg_list => OKL_API.G_FALSE,
3013 p_chr_id => p_chr_id,
3014 p_cle_id => p_kle_id,
3015 p_stream_id => p_stream_id,
3016 p_rgd_code => 'LALEVL',
3017 x_return_status => x_return_status,
3018 x_msg_count => x_msg_count,
3019 x_msg_data => x_msg_data,
3020 x_rgpv_tbl => l_rgpv_tbl,
3021 x_rg_count => l_rg_count);
3022 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3023 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3024 END IF;
3025
3026
3027 IF (l_rg_count = 0) THEN -- No Rule Groups
3028 okl_api.set_message(
3029 G_APP_NAME,
3030 G_NO_HEADER_PAYMENT
3031 );
3032 RAISE OKC_API.G_EXCEPTION_ERROR;
3033 END IF;
3034
3035 l_tot_fee_amount := 0;
3036 OPEN fee_amount_csr(p_kle_id => p_kle_id);
3037 FETCH fee_amount_csr INTO l_tot_fee_amount;
3038 CLOSE fee_amount_csr;
3039
3040
3041 get_currency_precision(
3042 x_return_status => x_return_status,
3043 p_chr_id => p_chr_id,
3044 x_precision => l_precision
3045 );
3046 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3047 RAISE OKC_API.G_EXCEPTION_ERROR;
3048 END IF;
3049
3050 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3051 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'CAP :'||l_tot_fee_amount);
3052 END IF;
3053
3054 OPEN fee_name_csr (p_kle_id);
3055 FETCH fee_name_csr INTO l_fee_name;
3056 CLOSE fee_name_csr;
3057
3058 OPEN ro_fee_subline_csr(p_chr_id, p_kle_id);
3059 LOOP
3060 FETCH ro_fee_subline_csr INTO
3061 ro_fee_subline_rec.id,
3062 ro_fee_subline_rec.amount;
3063 EXIT WHEN ro_fee_subline_csr%NOTFOUND;
3064 rowCnt := ro_fee_subline_csr%ROWCOUNT;
3065 -- Apply Payment across Sub-Lines (Fee Asset Lines)
3066 create_line_rule(
3067 x_return_status => x_return_status,
3068 x_msg_count => x_msg_count,
3069 x_msg_data => x_msg_data,
3070 p_chr_id => p_chr_id,
3071 p_cle_id => ro_fee_subline_rec.id,
3072 p_stream_id => p_stream_id,
3073 p_fin_line_capital_amount => ro_fee_subline_rec.amount, -- Sub-line fee Amount
3074 p_tot_capital_amount => l_tot_fee_amount, -- Total top Fee line Amount
3075 p_precision => l_precision,
3076 p_rgpv_tbl => l_rgpv_tbl,
3077 p_rg_count => l_rg_count,
3078 x_slh_rulv_tbl_out => x_slh_rulv_tbl_out,
3079 x_slh_count => x_slh_count,
3080 x_sll_rulv_tbl_out => x_sll_rulv_tbl_out,
3081 x_sll_count => x_sll_count
3082 );
3083 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3084 RAISE OKC_API.G_EXCEPTION_ERROR;
3085 END IF;
3086 END LOOP;
3087 CLOSE ro_fee_subline_csr;
3088
3089 -- NO sub-lines found, error out.
3090 IF ( rowCnt < 1 ) THEN
3091 okl_api.set_message(
3092 p_app_name => G_APP_NAME,
3093 p_msg_name => 'OKL_RO_NO_SUB_LNS',
3094 p_token1 => 'FEE_LINE',
3095 p_token1_value => l_fee_name
3096 );
3097 RAISE OKC_API.G_EXCEPTION_ERROR;
3098 END IF;
3099
3100 FOR i IN 1..x_slh_count
3101 LOOP
3102 l_slh_rulv_del_tbl(i).id := x_slh_rulv_tbl_out(i).id;
3103 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3104 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'SLH delete: '||x_slh_rulv_tbl_out(i).id);
3105 END IF;
3106 END LOOP; -- SLH
3107 okl_rule_pub.delete_rule(
3108 p_api_version => 1.0,
3109 p_init_msg_list => p_init_msg_list,
3110 x_return_status => x_return_status,
3111 x_msg_count => x_msg_count,
3112 x_msg_data => x_msg_data,
3113 p_rulv_tbl => l_slh_rulv_del_tbl
3114 );
3115
3116 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3117 RAISE OKC_API.G_EXCEPTION_ERROR;
3118 END IF;
3119
3120 FOR i IN 1..x_sll_count
3121 LOOP
3122 l_sll_rulv_del_tbl(i).id := x_sll_rulv_tbl_out(i).id;
3123 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3124 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'SLL delete: '||x_sll_rulv_tbl_out(i).id);
3125 END IF;
3126 END LOOP; -- SLL
3127 okl_rule_pub.delete_rule(
3128 p_api_version => 1.0,
3129 p_init_msg_list => p_init_msg_list,
3130 x_return_status => x_return_status,
3131 x_msg_count => x_msg_count,
3132 x_msg_data => x_msg_data,
3133 p_rulv_tbl => l_sll_rulv_del_tbl
3134 );
3135
3136 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3137 RAISE OKC_API.G_EXCEPTION_ERROR;
3138 END IF;
3139
3140
3141 --
3142 -- Delete Header Payment Rule
3143 -- only if there is no other stream type present
3144 --
3145 FOR i IN 1..l_rg_count
3146 LOOP
3147 l_del_yn := 'N';
3148 OPEN rg_del_csr(p_chr_id, l_rgpv_tbl(i).id);
3149 FETCH rg_del_csr INTO l_del_yn;
3150 CLOSE rg_del_csr;
3151 IF (l_del_yn = 'Y') THEN
3152 l_rgpv_del_rec.id := l_rgpv_tbl(i).id;
3153 okl_rule_pub.delete_rule_group(
3154 p_api_version => 1.0,
3155 p_init_msg_list => p_init_msg_list,
3156 x_return_status => x_return_status,
3157 x_msg_count => x_msg_count,
3158 x_msg_data => x_msg_data,
3159 p_rgpv_rec => l_rgpv_del_rec
3160 );
3161 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
3162 RAISE OKC_API.G_EXCEPTION_ERROR;
3163 END IF;
3164 END IF;
3165 END LOOP;
3166
3167 --Call End Activity
3168 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3169 x_msg_data => x_msg_data);
3170
3171
3172 EXCEPTION
3173 when OKC_API.G_EXCEPTION_ERROR then
3174 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3175 p_api_name => l_api_name,
3176 p_pkg_name => G_PKG_NAME,
3177 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3178 x_msg_count => x_msg_count,
3179 x_msg_data => x_msg_data,
3180 p_api_type => G_API_TYPE);
3181
3182 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3183 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3184 p_api_name => l_api_name,
3185 p_pkg_name => G_PKG_NAME,
3186 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
3187 x_msg_count => x_msg_count,
3188 x_msg_data => x_msg_data,
3189 p_api_type => G_API_TYPE);
3190
3191 when OTHERS then
3192 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3193 p_api_name => l_api_name,
3194 p_pkg_name => G_PKG_NAME,
3195 p_exc_name => 'OTHERS',
3196 x_msg_count => x_msg_count,
3197 x_msg_data => x_msg_data,
3198 p_api_type => G_API_TYPE);
3199
3200 END apply_eligible_fee_payment;
3201 -- end: cklee: okl.h
3202
3203 ------------------------------------------------------------------------------
3204 -- PROCEDURE delete_payment
3205 --
3206 -- This procedure deletes Payments including SLH, SLL and if requires
3207 -- corresponding rule group too.
3208 --
3209 -- Calls:
3210 -- Called By:
3211 ------------------------------------------------------------------------------
3212 PROCEDURE delete_payment(
3213 p_api_version IN NUMBER,
3214 p_init_msg_list IN VARCHAR2,
3215 x_return_status OUT NOCOPY VARCHAR2,
3216 x_msg_count OUT NOCOPY NUMBER,
3217 x_msg_data OUT NOCOPY VARCHAR2,
3218 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
3219 p_rgp_id IN OKC_RULE_GROUPS_V.ID%TYPE,
3220 p_rule_id IN OKC_RULES_V.ID%TYPE
3221 ) IS
3222 l_api_name VARCHAR2(35) := 'delete_payment';
3223 l_proc_name VARCHAR2(35) := 'DELETE_PAYMENT';
3224 l_api_version CONSTANT NUMBER := 1;
3225
3226 CURSOR sll_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
3227 p_rgp_id OKC_RULE_GROUPS_V.ID%TYPE,
3228 p_rule_id OKC_RULES_V.ID%TYPE) IS
3229 SELECT id
3230 FROM okc_rules_v
3231 WHERE dnz_chr_id = p_chr_id
3232 AND rgp_id = p_rgp_id
3233 AND object2_id1 = p_rule_id
3234 AND rule_information_category = 'LASLL';
3235
3236 CURSOR rgp_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
3237 p_rgp_id OKC_RULE_GROUPS_V.ID%TYPE) IS
3238 SELECT 'Y'
3239 FROM okc_rules_v
3240 WHERE dnz_chr_id = p_chr_id
3241 AND rgp_id = p_rgp_id;
3242
3243 -- Fix Bug# 2819175
3244 CURSOR slh_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
3245 p_rule_id OKC_RULES_V.ID%TYPE) IS
3246 SELECT 'Y'
3247 FROM okc_rules_v
3248 WHERE id = p_rule_id
3249 AND dnz_chr_id = p_chr_id;
3250
3251 l_rulv_tbl rulv_tbl_type;
3252 l_rulv_rec rulv_rec_type;
3253 l_rgpv_rec rgpv_rec_type;
3254 i NUMBER;
3255 l_rule_present VARCHAR2(1);
3256 l_slh VARCHAR2(1);
3257
3258 BEGIN
3259 IF (G_DEBUG_ENABLED = 'Y') THEN
3260 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
3261 END IF;
3262
3263 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3264 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3265 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
3266 END IF;
3267
3268 -- call START_ACTIVITY to create savepoint, check compatibility
3269 -- and initialize message list
3270 x_return_status := OKC_API.START_ACTIVITY(
3271 p_api_name => l_api_name,
3272 p_pkg_name => G_PKG_NAME,
3273 p_init_msg_list => p_init_msg_list,
3274 l_api_version => l_api_version,
3275 p_api_version => p_api_version,
3276 p_api_type => G_API_TYPE,
3277 x_return_status => x_return_status);
3278
3279 -- check if activity started successfully
3280 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3281 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3282 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3283 raise OKC_API.G_EXCEPTION_ERROR;
3284 END IF;
3285
3286 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3287
3288 i := 0;
3289 FOR sll_rec IN sll_csr(p_chr_id,
3290 p_rgp_id,
3291 p_rule_id)
3292 LOOP
3293 i := i + 1;
3294 l_rulv_tbl(i).id := sll_rec.id;
3295 END LOOP;
3296
3297 IF (i > 0) THEN
3298 okl_rule_pub.delete_rule(
3299 p_api_version => 1.0,
3300 p_init_msg_list => OKC_API.G_FALSE,
3301 x_return_status => x_return_status,
3302 x_msg_count => x_msg_count,
3303 x_msg_data => x_msg_data,
3304 p_rulv_tbl => l_rulv_tbl
3305 );
3306 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3307 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3308 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3309 raise OKC_API.G_EXCEPTION_ERROR;
3310 END IF;
3311
3312 END IF;
3313
3314 l_slh := 'N';
3315 OPEN slh_csr(p_chr_id,
3316 p_rule_id);
3317 FETCH slh_csr INTO l_slh;
3318 CLOSE slh_csr;
3319
3320 IF (l_slh = 'Y') THEN
3321
3322 l_rulv_rec.id := p_rule_id; -- SLH Rule ID
3323 okl_rule_pub.delete_rule(
3324 p_api_version => 1.0,
3325 p_init_msg_list => OKC_API.G_FALSE,
3326 x_return_status => x_return_status,
3327 x_msg_count => x_msg_count,
3328 x_msg_data => x_msg_data,
3329 p_rulv_rec => l_rulv_rec
3330 );
3331 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3332 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3333 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3334 raise OKC_API.G_EXCEPTION_ERROR;
3335 END IF;
3336
3337 END IF;
3338
3339 l_rule_present := 'N';
3340 OPEN rgp_csr(p_chr_id,
3341 p_rgp_id);
3342 FETCH rgp_csr INTO l_rule_present;
3343 CLOSE rgp_csr;
3344
3345 IF (l_rule_present = 'N') THEN -- No more rules under this rule group
3346
3347 l_rgpv_rec.id := p_rgp_id;
3348 okl_rule_pub.delete_rule_group(
3349 p_api_version => p_api_version,
3350 p_init_msg_list => p_init_msg_list,
3351 x_return_status => x_return_status,
3352 x_msg_count => x_msg_count,
3353 x_msg_data => x_msg_data,
3354 p_rgpv_rec => l_rgpv_rec
3355 );
3356
3357 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3358 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3359 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3360 raise OKC_API.G_EXCEPTION_ERROR;
3361 END IF;
3362
3363 END IF;
3364
3365 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3366
3367 --Call End Activity
3368 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3369 x_msg_data => x_msg_data);
3370
3371
3372 EXCEPTION
3373 when OKC_API.G_EXCEPTION_ERROR then
3374 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3375 p_api_name => l_api_name,
3376 p_pkg_name => G_PKG_NAME,
3377 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3378 x_msg_count => x_msg_count,
3379 x_msg_data => x_msg_data,
3380 p_api_type => G_API_TYPE);
3381
3382 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3383 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3384 p_api_name => l_api_name,
3385 p_pkg_name => G_PKG_NAME,
3386 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
3387 x_msg_count => x_msg_count,
3388 x_msg_data => x_msg_data,
3389 p_api_type => G_API_TYPE);
3390
3391 when OTHERS then
3392 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3393 p_api_name => l_api_name,
3394 p_pkg_name => G_PKG_NAME,
3395 p_exc_name => 'OTHERS',
3396 x_msg_count => x_msg_count,
3397 x_msg_data => x_msg_data,
3398 p_api_type => G_API_TYPE);
3399
3400 END delete_payment;
3401
3402 END OKL_PAYMENT_APPLICATION_PVT;