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