[Home] [Help]
PACKAGE BODY: APPS.OKL_LA_PAYMENTS_PVT
Source
1 PACKAGE BODY OKL_LA_PAYMENTS_PVT as
2 /* $Header: OKLRPYTB.pls 120.35.12010000.6 2009/01/22 09:22:09 nikshah ship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7
8 --G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
9 --G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
10 G_EXCEPTION_HALT_PROCESSING exception;
11 G_EXCEPTION_STOP_VALIDATION exception;
12
13
14 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_LA_PAYMENTS_PVT';
15 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
16 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
17 l_api_name VARCHAR2(35) := 'LA_PAYMENTS';
18
19 l_detail_count NUMBER := 0;
20 l_payment_code varchar2(150) := null;
21
22 -- start: June 24, 2005 cklee: Modification for okl.h Sales Quote enhancement
23 ----------------------------------------------------------------------------
24 -- Global Exception
25 ----------------------------------------------------------------------------
26 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
27 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(30) := 'OKL_UNEXPECTED_ERROR';
28 G_SQLERRM_TOKEN CONSTANT VARCHAR2(30) := 'OKL_SQLERRM';
29 G_SQLCODE_TOKEN CONSTANT VARCHAR2(30) := 'OKL_SQLCODE';
30
31 -- start: Sept 02, 2005 cklee: Modification for GE - 20 variable rate ER
32 G_OKL_LLA_VAR_RATE_ERROR CONSTANT VARCHAR2(30) := 'OKL_LLA_VAR_RATE_ERROR';
33 G_LEASE_TYPE CONSTANT VARCHAR2(30) := 'LEASE_TYPE';
34 G_INT_BASIS CONSTANT VARCHAR2(30) := 'INT_BASIS';
35 G_OKL_LLA_VAR_RATE_PAYMENT1 CONSTANT VARCHAR2(30) := 'OKL_LLA_VAR_RATE_PAYMENT1';
36 G_OKL_LLA_VAR_RATE_PAYMENT2 CONSTANT VARCHAR2(30) := 'OKL_LLA_VAR_RATE_PAYMENT2';
37 G_OKL_LLA_VAR_RATE_PAYMENT3 CONSTANT VARCHAR2(30) := 'OKL_LLA_VAR_RATE_PAYMENT3';
38 G_OKL_LLA_VAR_RATE_PAYMENT4 CONSTANT VARCHAR2(30) := 'OKL_LLA_VAR_RATE_PAYMENT4';
39 -- end: Sept 02, 2005 cklee: Modification for GE - 20 variable rate ER
40
41 -- start: 07-25-2005 cklee/mvasudev -- Fixed Bug#4392051/okl.h 4437938
42 CURSOR l_okl_sll_count_csr(p_rgp_id IN NUMBER,
43 p_chr_id IN NUMBER,
44 p_slh_id IN VARCHAR2)
45 IS
46 SELECT COUNT(1)
47 FROM okc_rules_b sll
48 WHERE sll.dnz_chr_id = p_chr_id
49 AND sll.rgp_id = p_rgp_id
50 AND sll.rule_information_category = 'LASLL' --| 17-Jan-06 cklee Fixed bug#4956483 |
51 AND sll.object2_id1 = p_slh_id;
52 -- end: 07-25-2005 cklee/mvasudev -- Fixed Bug#4392051/okl.h 4437938
53
54 -- Authoring OA Migration
55 --------------------------------------------------------------------------
56 ----- Check if the payment is for an Upfront Tax Fee line
57 --------------------------------------------------------------------------
58 FUNCTION is_upfront_tax_fee_payment(p_chr_id IN NUMBER,
59 p_cle_id IN NUMBER)
60 RETURN VARCHAR2 IS
61
62 CURSOR l_fee_csr(p_cle_id IN NUMBER) IS
63 SELECT kle.fee_purpose_code
64 FROM okl_k_lines kle
65 WHERE kle.id = p_cle_id;
66
67 l_fee_rec l_fee_csr%ROWTYPE;
68
69 l_ret_value VARCHAR2(1);
70
71 BEGIN
72
73 l_ret_value := OKL_API.G_FALSE;
74
75 OPEN l_fee_csr(p_cle_id => p_cle_id);
76 FETCH l_fee_csr INTO l_fee_rec;
77 CLOSE l_fee_csr;
78
79 IF l_fee_rec.fee_purpose_code = 'SALESTAX' THEN
80 l_ret_value := OKL_API.G_TRUE;
81 END IF;
82
83 RETURN l_ret_value;
84
85 END is_upfront_tax_fee_payment;
86
87 --------------------------------------------------------------------------
88 -- Perform Upfront Tax Fee Payment validations and update booking status
89 --------------------------------------------------------------------------
90 PROCEDURE process_upfront_tax_pymt(
91 p_api_version IN NUMBER,
92 p_init_msg_list IN VARCHAR2,
93 x_return_status OUT NOCOPY VARCHAR2,
94 x_msg_count OUT NOCOPY NUMBER,
95 x_msg_data OUT NOCOPY VARCHAR2,
96 p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
97
98 l_upfront_tax_prog_sts OKL_BOOK_CONTROLLER_TRX.progress_status%TYPE;
99
100 BEGIN
101
102 -- Validate upfront tax fee payments
103 OKL_LA_SALES_TAX_PVT.validate_upfront_tax_fee(
104 p_api_version => p_api_version,
105 p_init_msg_list => p_init_msg_list,
106 x_return_status => x_return_status,
107 x_msg_count => x_msg_count,
108 x_msg_data => x_msg_data,
109 p_chr_id => p_chr_id);
110
111 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
112 l_upfront_tax_prog_sts := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_ERROR;
113 x_return_status := OKL_API.G_RET_STS_SUCCESS;
114 ELSE
115 l_upfront_tax_prog_sts := OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE;
116 END IF;
117
118 --Update Contract Status to Passed
119 OKL_CONTRACT_STATUS_PUB.update_contract_status(
120 p_api_version => p_api_version,
121 p_init_msg_list => p_init_msg_list,
122 x_return_status => x_return_status,
123 x_msg_count => x_msg_count,
124 x_msg_data => x_msg_data,
125 p_khr_status => 'PASSED',
126 p_chr_id => p_chr_id);
127
128 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
129 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
130 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
131 RAISE OKL_API.G_EXCEPTION_ERROR;
132 END IF;
133
134 --call to cascade status on to lines
135 OKL_CONTRACT_STATUS_PUB.cascade_lease_status
136 (p_api_version => p_api_version,
137 p_init_msg_list => p_init_msg_list,
138 x_return_status => x_return_status,
139 x_msg_count => x_msg_count,
140 x_msg_data => x_msg_data,
141 p_chr_id => p_chr_id);
142
143 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
144 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
145 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
146 RAISE OKL_API.G_EXCEPTION_ERROR;
147 END IF;
148
149 -- Update status of Validate Contract process to Complete
150 OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
151 p_api_version => p_api_version,
152 p_init_msg_list => p_init_msg_list,
153 x_return_status => x_return_status,
154 x_msg_count => x_msg_count,
155 x_msg_data => x_msg_data,
156 p_khr_id => p_chr_id ,
157 p_prog_short_name => OKL_BOOK_CONTROLLER_PVT.G_VALIDATE_CONTRACT ,
158 p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE);
159
160 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
161 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
162 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
163 RAISE OKL_API.G_EXCEPTION_ERROR;
164 END IF;
165
166 -- Update status of Calculate Upfront Tax process to Complete or Error
167 -- based on the results of OKL_LA_SALES_TAX_PVT.validate_upfront_tax_fee
168 OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
169 p_api_version => p_api_version,
170 p_init_msg_list => p_init_msg_list,
171 x_return_status => x_return_status,
172 x_msg_count => x_msg_count,
173 x_msg_data => x_msg_data,
174 p_khr_id => p_chr_id ,
175 p_prog_short_name => OKL_BOOK_CONTROLLER_PVT.G_CALC_UPFRONT_TAX ,
176 p_progress_status => l_upfront_tax_prog_sts);
177
178 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
179 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
180 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
181 RAISE OKL_API.G_EXCEPTION_ERROR;
182 END IF;
183
184 EXCEPTION
185 WHEN OKL_API.G_EXCEPTION_ERROR THEN
186 x_return_status := OKL_API.G_RET_STS_ERROR;
187
188 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
189 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
190
191 WHEN OTHERS THEN
192 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
193
194 END process_upfront_tax_pymt;
195
196 --Bug# 7440232
197 --------------------------------------------------------------------------
198 -- Delete Interest Rate parameters if the Interest Calculation Basis is
199 -- Fixed, Revenue Recognition Method is Streams and there are no Principal
200 -- Payments defined
201 --------------------------------------------------------------------------
202 PROCEDURE delete_interest_rate_params(
203 p_api_version IN NUMBER,
204 p_init_msg_list IN VARCHAR2,
205 x_return_status OUT NOCOPY VARCHAR2,
206 x_msg_count OUT NOCOPY NUMBER,
207 x_msg_data OUT NOCOPY VARCHAR2,
208 p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
209
210 CURSOR c_rates_csr(p_chr_id IN NUMBER,
211 p_parameter_type_code IN VARCHAR2) IS
212 SELECT rate.effective_from_date
213 FROM okl_k_rate_params rate
214 WHERE rate.khr_id = p_chr_id
215 AND rate.parameter_type_code = p_parameter_type_code;
216
217 CURSOR c_principal_pymts_yn(p_chr_id IN NUMBER) IS
218 SELECT 'Y'
219 FROM okc_rule_groups_b rgp,
220 okc_rules_b rul,
221 okl_strm_type_b sty,
222 okc_k_lines_b cle
223 WHERE rgp.rgd_code = 'LALEVL'
224 AND rgp.dnz_chr_id = p_chr_id
225 AND rul.dnz_chr_id = rgp.dnz_chr_id
226 AND rul.rgp_id = rgp.id
227 AND rul.rule_information_category = 'LASLH'
228 AND rul.object1_id1 = sty.id
229 AND rul.jtot_object1_code = 'OKL_STRMTYP'
230 AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
231 AND rgp.cle_id = cle.id (+)
232 AND cle.sts_code (+) <> 'ABANDONED';
233
234 l_principal_pymts_yn VARCHAR2(1);
235 l_count NUMBER;
236 l_krpdel_tbl OKL_K_RATE_PARAMS_PVT.krpdel_tbl_type;
237 l_pdt_params_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
238
239 BEGIN
240
241 OKL_K_RATE_PARAMS_PVT.get_product(
242 p_api_version => p_api_version,
243 p_init_msg_list => p_init_msg_list,
244 x_return_status => x_return_status,
245 x_msg_count => x_msg_count,
246 x_msg_data => x_msg_data,
247 p_khr_id => p_chr_id,
248 x_pdt_parameter_rec => l_pdt_params_rec);
249
250 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
251 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
252 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
253 RAISE OKL_API.G_EXCEPTION_ERROR;
254 END IF;
255
256 IF (l_pdt_params_rec.interest_calculation_basis = 'FIXED' AND
257 l_pdt_params_rec.revenue_recognition_method = 'STREAMS') THEN
258
259 l_principal_pymts_yn := 'N';
260 OPEN c_principal_pymts_yn(p_chr_id => p_chr_id);
261 FETCH c_principal_pymts_yn INTO l_principal_pymts_yn;
262 CLOSE c_principal_pymts_yn;
263
264 IF (l_principal_pymts_yn = 'N') THEN
265
266 l_count := 0;
267 FOR l_rates_rec IN c_rates_csr(p_chr_id, 'ACTUAL') LOOP
268 l_count := l_count + 1;
269 l_krpdel_tbl(l_count).khr_id := p_chr_id;
270 l_krpdel_tbl(l_count).effective_from_date := l_rates_rec.effective_from_date;
271 l_krpdel_tbl(l_count).rate_type := 'INTEREST_RATE_PARAMS';
272 END LOOP;
273
274 IF l_count > 0 THEN
275 OKL_K_RATE_PARAMS_PVT.delete_k_rate_params(
276 p_api_version => p_api_version,
277 p_init_msg_list => p_init_msg_list,
278 x_return_status => x_return_status,
279 x_msg_count => x_msg_count,
280 x_msg_data => x_msg_data,
281 p_krpdel_tbl => l_krpdel_tbl);
282
283 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
284 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
285 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
286 RAISE OKL_API.G_EXCEPTION_ERROR;
287 END IF;
288 END IF;
289 END IF;
290 END IF;
291
292 EXCEPTION
293 WHEN OKL_API.G_EXCEPTION_ERROR THEN
294 x_return_status := OKL_API.G_RET_STS_ERROR;
295
296 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
297 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
298
299 WHEN OTHERS THEN
300 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
301
302 END delete_interest_rate_params;
303
304 -- start: Sept 02, 2005 cklee: Modification for GE - 20 variable rate ER
305 --------------------------------------------------------------------------
306 ----- Validate stream/payment type for an asset line
307 --------------------------------------------------------------------------
308 FUNCTION validate_payment_type_asset
309 (p_chr_id number,
310 p_asset_id number,
311 p_service_fee_id number,
312 p_payment_id number
313 ) RETURN VARCHAR2
314 IS
315 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
316 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
317 l_dummy number;
318 l_api_version NUMBER := 1.0;
319 x_msg_count NUMBER;
320 x_msg_data VARCHAR2(4000);
321 l_init_msg_list VARCHAR2(10) := OKL_API.G_FALSE;
322
323 l_stream_type_purpose OKL_STRM_TYPE_V.STREAM_TYPE_PURPOSE%type;
324
325 l_book_class OKL_PRODUCT_PARAMETERS_V.DEAL_TYPE%TYPE;
326 l_interest_calc_basis OKL_PRODUCT_PARAMETERS_V.INTEREST_CALCULATION_BASIS%TYPE;
327
328 l_pdt_params_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
329
330 CURSOR c_stream_type_purpose (p_sty_id number)
331 IS
332 SELECT sty.STREAM_TYPE_PURPOSE
333 FROM OKL_STRM_TYPE_V sty
334 WHERE sty.ID = p_sty_id
335 ;
336
337 BEGIN
338
339 -- check only if it's an asset line payment
340 IF (p_asset_id IS NOT NULL AND
341 p_asset_id <> OKL_API.G_MISS_NUM)
342 AND
343 (p_service_fee_id IS NULL OR
344 p_service_fee_id = OKL_API.G_MISS_NUM)
345 THEN
346
347 -- get stream type purpose code
348 open c_stream_type_purpose(p_payment_id);
349 fetch c_stream_type_purpose into l_stream_type_purpose;
350 close c_stream_type_purpose;
351
352 -- get product information: Book classification and interest_calc_basis
353 OKL_K_RATE_PARAMS_PVT.get_product(
354 p_api_version => l_api_version,
355 p_init_msg_list => l_init_msg_list,
356 x_return_status => x_return_status,
357 x_msg_count => x_msg_count,
358 x_msg_data => x_msg_data,
359 p_khr_id => p_chr_id,
360 x_pdt_parameter_rec => l_pdt_params_rec);
361
362 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
363 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
364 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
365 RAISE OKL_API.G_EXCEPTION_ERROR;
366 END IF;
367
368 l_book_class := l_pdt_params_rec.deal_type;
369 l_interest_calc_basis := l_pdt_params_rec.interest_calculation_basis;
370
371 -- scenario 1:
372 -- Book Classification: Operating Lease, Direct Finance Lease, Sales Type Lease
373 -- Interest Calculation Basis: Fixed, Reamort, Float Factors
374 -- Payment/Stream Type Not in the following: Rent, Estimated Property Tax, and Down Payment
375 IF l_book_class IN ('LEASEOP', 'LEASEDF', 'LEASEST') AND
376 l_interest_calc_basis IN ('FIXED','FLOAT_FACTORS', 'REAMORT') AND
377 l_stream_type_purpose NOT IN ('RENT', 'ESTIMATED_PROPERTY_TAX', 'DOWN_PAYMENT') THEN
378
379 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
380 ,p_msg_name => G_OKL_LLA_VAR_RATE_PAYMENT1);
381
382 RAISE G_EXCEPTION_HALT_VALIDATION;
383 END IF;
384
385 -- scenario 2:
386 -- Book Classification: Loan
387 -- Interest Calculation Basis: Fixed, Float, Catchup / Cleanup, and Reamort
388 -- Payment/Stream Type Not in the following: Rent, Estimated Property Tax, Down Payment, and Principal Payment
389 --modified by rkuttiya for bug # 7498330 to include unscheduled principal
390 -- payment unscheduled loan payment
391 IF l_book_class = 'LOAN' AND
392 l_interest_calc_basis IN ('FIXED','FLOAT', 'CATCHUP/CLEANUP', 'REAMORT') AND
393 l_stream_type_purpose NOT IN ('RENT', 'ESTIMATED_PROPERTY_TAX', 'DOWN_PAYMENT', 'PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT','UNSCHEDULED_LOAN_PAYMENT') THEN
394
395 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
396 ,p_msg_name => G_OKL_LLA_VAR_RATE_PAYMENT2);
397
398 RAISE G_EXCEPTION_HALT_VALIDATION;
399 END IF;
400
401 -- scenario 3:
402 -- Book Classification: Loan-Revolving
403 -- Interest Calculation Basis: Float
404 -- Payment/Stream Type is not null
405 IF l_book_class = 'LOAN-REVOLVING' AND
406 l_interest_calc_basis IN ('FLOAT') AND
407 l_stream_type_purpose IS NOT NULL THEN
408
409 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
410 ,p_msg_name => G_OKL_LLA_VAR_RATE_PAYMENT3);
411
412 RAISE G_EXCEPTION_HALT_VALIDATION;
413 END IF;
414
415 -- scenario 4:
416 -- Book Classification: Loan
417 -- Interest Calculation Basis: Fixed, Float, Catchup / Cleanup, and Reamort
418 -- Payment/Stream Type is Principal Payment
419 IF l_book_class = 'LOAN' AND
420 l_interest_calc_basis IN ('FIXED','FLOAT', 'CATCHUP/CLEANUP', 'REAMORT') AND
421 l_stream_type_purpose IN ('PRINCIPAL_PAYMENT') THEN
422
423 IF NOT (NVL(okl_streams_util.get_pricing_engine(p_chr_id), 'INTERNAL') = 'EXTERNAL') THEN
424
425 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
426 ,p_msg_name => G_OKL_LLA_VAR_RATE_PAYMENT4);
427
428 RAISE G_EXCEPTION_HALT_VALIDATION;
429 END IF;
430
431 END IF;
432
433 END IF;
434
435 RETURN l_return_status;
436
437 EXCEPTION
438 WHEN G_EXCEPTION_HALT_VALIDATION THEN
439 l_return_status := OKL_API.G_RET_STS_ERROR;
440 RETURN l_return_status;
441 WHEN OTHERS THEN
442 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
443 OKL_API.Set_Message(p_app_name => G_APP_NAME,
444 p_msg_name => G_UNEXPECTED_ERROR,
445 p_token1 => G_SQLCODE_TOKEN,
446 p_token1_value => SQLCODE,
447 p_token2 => G_SQLERRM_TOKEN,
448 p_token2_value => SQLERRM);
449 RETURN l_return_status;
450 END validate_payment_type_asset;
451 -- end: Sept 02, 2005 cklee: Modification for GE - 20 variable rate ER
452
453
454 -- start: June 24, 2005 cklee: Modification for okl.h Sales Quote enhancement
455 --------------------------------------------------------------------------
456 ----- Validate Capitalize for an asset line
457 --------------------------------------------------------------------------
458 FUNCTION validate_capitalize_dp
459 (p_asset_id number,
460 p_service_fee_id number,
461 p_payment_id number
462 ) RETURN VARCHAR2
463 IS
464 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
465 l_dummy number;
466
467 l_downpayment_sty_found boolean := false;
468
469 CURSOR c_is_donwpayment_sty (p_sty_id number)
470 IS
471 SELECT sty.STREAM_TYPE_PURPOSE
472 FROM OKL_STRM_TYPE_V sty
473 WHERE sty.ID = p_sty_id
474 ;
475
476 CURSOR c_is_capitalize (p_asset_id number)
477 IS
478 SELECT NVL(cle.CAPITALIZE_DOWN_PAYMENT_YN,'N') CAPITALIZE_DOWN_PAYMENT_YN,
479 cle.DOWN_PAYMENT_RECEIVER_CODE
480 FROM okl_k_lines cle
481 WHERE cle.id = p_asset_id
482 ;
483
484 BEGIN
485
486 FOR this_r IN c_is_donwpayment_sty(p_payment_id) LOOP
487
488 -- check only if it's an asset line payment
489 IF (p_asset_id IS NOT NULL AND
490 p_asset_id <> OKL_API.G_MISS_NUM)
491 AND
492 (p_service_fee_id IS NULL OR
493 p_service_fee_id = OKL_API.G_MISS_NUM)
494 THEN
495
496 IF (this_r.STREAM_TYPE_PURPOSE = 'DOWN_PAYMENT') THEN
497
498 FOR this_row IN c_is_capitalize(p_asset_id) LOOP
499
500 IF NOT(this_row.CAPITALIZE_DOWN_PAYMENT_YN = 'N' AND
501 this_row.DOWN_PAYMENT_RECEIVER_CODE = 'LESSOR') THEN
502 OKL_API.Set_Message(p_app_name => G_APP_NAME,
503 p_msg_name => 'OKL_LA_CAPITALIZE_DOWNPAYMENT');
504
505 RAISE G_EXCEPTION_HALT_VALIDATION;
506 END IF;
507 END LOOP; -- dummy loop, return one row only
508
509 END IF;
510
511 ELSE -- downpayemnt only allow for asset line payment
512
513 IF (this_r.STREAM_TYPE_PURPOSE = 'DOWN_PAYMENT') THEN
514
515 OKL_API.Set_Message(p_app_name => G_APP_NAME,
516 p_msg_name => 'OKL_LA_DOWNPAYMENT_STY_CODE');
517 RAISE G_EXCEPTION_HALT_VALIDATION;
518 END IF;
519
520 END IF;
521
522 END LOOP; -- dummy loop, return one row only
523
524 RETURN l_return_status;
525
526 EXCEPTION
527 WHEN G_EXCEPTION_HALT_VALIDATION THEN
528 l_return_status := OKL_API.G_RET_STS_ERROR;
529 RETURN l_return_status;
530 WHEN OTHERS THEN
531 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
532 OKL_API.Set_Message(p_app_name => G_APP_NAME,
533 p_msg_name => G_UNEXPECTED_ERROR,
534 p_token1 => G_SQLCODE_TOKEN,
535 p_token1_value => SQLCODE,
536 p_token2 => G_SQLERRM_TOKEN,
537 p_token2_value => SQLERRM);
538 RETURN l_return_status;
539 END validate_capitalize_dp;
540 -- end: June 24, 2005 cklee: Modification for okl.h Sales Quote enhancement
541
542
543 -- l_log VARCHAR2(25) := 'pym.log';
544 -- l_out varchar2(25) := 'pym.out';
545
546 FUNCTION get_lty_code(
547 p_rgp_id IN NUMBER)
548 RETURN OKC_LINE_STYLES_V.LTY_CODE%TYPE IS
549 l_lty_code OKC_LINE_STYLES_V.LTY_CODE%TYPE := null;
550 cursor LINE_STYLE_CSR(P_RGP_ID IN NUMBER) is
551 SELECT LS.LTY_CODE
552 FROM
553 OKC_RULE_GROUPS_V RG, OKL_K_LINES_V LN, OKC_LINE_STYLES_V LS
554 WHERE
555 RG.ID = P_RGP_ID AND
556 RG.CLE_ID = LN.ID AND
557 LN.ID = LS.ID;
558
559 BEGIN
560 open LINE_STYLE_CSR(p_rgp_id);
561 fetch LINE_STYLE_CSR into l_lty_code;
562 close LINE_STYLE_CSR;
563 return l_lty_code;
564 END get_lty_code;
565
566
567 FUNCTION get_sll_period_count(
568 p_rgp_id IN NUMBER,
569 p_slh_id IN VARCHAR2,
570 p_chr_id IN NUMBER)
571 RETURN NUMBER IS
572 l_count NUMBER := null;
573 cursor SLL_CSR(P_RGP_ID IN NUMBER, P_SLH_ID IN VARCHAR2, P_CHR_ID IN NUMBER) is
574 SELECT COUNT(1)
575 FROM OKL_LA_PAYMENTS_UV
576 WHERE RGP_ID = P_RGP_ID
577 AND OBJECT2_ID1 = P_SLH_ID
578 AND DNZ_CHR_ID = P_CHR_ID
579 AND RULE_INFORMATION3 IS NOT NULL
580 AND RULE_INFORMATION_CATEGORY = 'LASLL';
581
582 BEGIN
583 open SLL_CSR(p_rgp_id, p_slh_id, p_chr_id);
584 fetch SLL_CSR into l_count;
585 close SLL_CSR;
586 if(l_count is null) then
587 l_count := 0;
588 end if;
589 return l_count;
590 END get_sll_period_count;
591
592
593 FUNCTION verify_sec_deposit_count(
594 p_rgp_id IN NUMBER,
595 p_slh_id IN VARCHAR2,
596 p_chr_id IN NUMBER)
597 RETURN NUMBER IS
598 l_count NUMBER := null;
599 cursor SLL_CSR(P_RGP_ID IN NUMBER, P_SLH_ID IN VARCHAR2, P_CHR_ID IN NUMBER) is
600 SELECT SUM(TO_NUMBER(nvl(RULE_INFORMATION3,0)))
601 FROM OKL_LA_PAYMENTS_UV
602 WHERE RGP_ID = P_RGP_ID
603 AND OBJECT2_ID1 = P_SLH_ID
604 AND DNZ_CHR_ID = P_CHR_ID
605 AND RULE_INFORMATION3 is not null
606 AND RULE_INFORMATION_CATEGORY = 'LASLL';
607
608 BEGIN
609 open SLL_CSR(p_rgp_id, p_slh_id, p_chr_id);
610 fetch SLL_CSR into l_count;
611 close SLL_CSR;
612 if(l_count is null) then
613 l_count := 0;
614 end if;
615 return l_count;
616 END verify_sec_deposit_count;
617
618
619
620 FUNCTION get_payment_type(p_slh_id IN VARCHAR2)
621 RETURN VARCHAR2 IS
622 l_payment_code varchar2(150) := null;
623 l_slh_id number := null;
624 cursor PAYMENT_TYPE_CSR(P_SLH_ID IN VARCHAR2) is
625 -- SELECT STRM.CODE
626 SELECT STRM.STREAM_TYPE_PURPOSE
627 FROM OKC_RULES_B RUL,
628 OKL_STRM_TYPE_B STRM
629 WHERE RUL.ID = TO_NUMBER(P_SLH_ID)
630 AND STRM.ID = TO_NUMBER(RUL.OBJECT1_ID1);
631
632 BEGIN
633 if(p_slh_id is null or p_slh_id = '') then return ''; end if;
634 open PAYMENT_TYPE_CSR(p_slh_id);
635 fetch PAYMENT_TYPE_CSR into l_payment_code;
636 close PAYMENT_TYPE_CSR;
637 return l_payment_code;
638 END get_payment_type;
639
640
641 -- next one function property taxes
642
643 FUNCTION is_prop_tax_payment(p_stream_id IN VARCHAR2)
644 RETURN BOOLEAN IS
645 l_payment_code varchar2(150) := null;
646 l_slh_id number := null;
647 cursor PAYMENT_TYPE_CSR(P_ID IN VARCHAR2) is
648 SELECT STRM.STREAM_TYPE_PURPOSE
649 FROM OKL_STRMTYP_SOURCE_V STRM
650 WHERE TO_CHAR(STRM.ID1) = P_ID;
651
652 BEGIN
653 if(p_stream_id is null or p_stream_id = '') then return false; end if;
654 open PAYMENT_TYPE_CSR(p_stream_id);
655 fetch PAYMENT_TYPE_CSR into l_payment_code;
656 close PAYMENT_TYPE_CSR;
657 if(l_payment_code is not null and l_payment_code = 'ESTIMATED_PROPERTY_TAX') then
658 return true;
659 else
660 return false;
661 end if;
662 END is_prop_tax_payment;
663
664 FUNCTION is_ppd_payment(p_stream_id IN VARCHAR2)
665 RETURN BOOLEAN IS
666 l_flag varchar2(1) := null;
667 l_slh_id number := null;
668 cursor PAYMENT_TYPE_CSR(P_ID IN VARCHAR2) is
669 SELECT 'Y'
670 FROM OKL_STRMTYP_SOURCE_V STRM,
671 OKC_RULES_B RL
672 WHERE RL.ID = P_ID
673 AND TO_CHAR(STRM.ID1) = RL.OBJECT1_ID1
674 AND STRM.STREAM_TYPE_PURPOSE = 'UNSCHEDULED_PRINCIPAL_PAYMENT';
675
676 BEGIN
677 if(p_stream_id is null or p_stream_id = '') then return false; end if;
678 open PAYMENT_TYPE_CSR(p_stream_id);
679 fetch PAYMENT_TYPE_CSR into l_flag;
680 close PAYMENT_TYPE_CSR;
681 if(l_flag is not null and l_flag = 'Y') then
682 return true;
683 else
684 return false;
685 end if;
686 END is_ppd_payment;
687
688
689 ------------------------------------------------------------------------------
690 -- FUNCTION is_rollover_fee_payment
691 --
692 -- Function to check if the fee type of the fee top line is rollover.
693 --
694 -- Calls:
695 -- Created By: Manu 13-Sep-2004
696 -- Called By:
697 ------------------------------------------------------------------------------
698
699 FUNCTION is_rollover_fee_payment(p_service_or_fee_id IN
700 OKC_K_LINES_B.ID%TYPE) RETURN BOOLEAN IS
701
702 l_fee_type VARCHAR2(150) := NULL;
703 CURSOR fee_type_csr(P_ID IN OKC_K_LINES_B.ID%TYPE) IS
704 SELECT FEE_TYPE FROM okc_k_lines_b CLEB, okl_k_lines KLE
705 WHERE KLE.ID = P_ID
706 AND KLE.ID = CLEB.ID;
707
708 BEGIN
709 IF (p_service_or_fee_id IS NULL OR p_service_or_fee_id = '') THEN
710 RETURN FALSE;
711 END IF;
712 OPEN fee_type_csr(p_service_or_fee_id);
713 FETCH fee_type_csr into l_fee_type;
714 CLOSE fee_type_csr;
715 IF (l_fee_type IS NOT NULL AND l_fee_type = 'ROLLOVER') THEN
716 RETURN TRUE;
717 ELSE
718 RETURN FALSE;
719 END IF;
720 END is_rollover_fee_payment;
721
722 -- start: cklee: 06/22/05 -- okl.h
723 ------------------------------------------------------------------------------
724 -- FUNCTION is_eligible_fee_payment
725 --
726 -- Function to check if the fee type of the fee top line is eligible
727 --
728 -- Calls:
729 -- Created By: cklee
730 -- Called By:
731 ------------------------------------------------------------------------------
732
733 FUNCTION is_eligible_fee_payment(p_service_or_fee_id IN
734 OKC_K_LINES_B.ID%TYPE) RETURN BOOLEAN IS
735
736 l_fee_type VARCHAR2(150) := NULL;
737 CURSOR fee_type_csr(P_ID IN OKC_K_LINES_B.ID%TYPE) IS
738 SELECT FEE_TYPE FROM okl_k_lines KLE
739 WHERE KLE.ID = P_ID;
740
741 BEGIN
742 IF (p_service_or_fee_id IS NULL OR p_service_or_fee_id = '') THEN
743 RETURN FALSE;
744 END IF;
745 OPEN fee_type_csr(p_service_or_fee_id);
746 FETCH fee_type_csr into l_fee_type;
747 CLOSE fee_type_csr;
748 IF (l_fee_type IS NOT NULL AND l_fee_type IN ('MISCELLANEOUS','PASSTHROUGH','SECDEPOSIT','INCOME','FINANCED','ROLLOVER')) THEN
749 RETURN TRUE;
750 ELSE
751 RETURN FALSE;
752 END IF;
753 END is_eligible_fee_payment;
754
755 -- end: cklee: 6/22/05 -- okl.h
756
757
758 FUNCTION get_start_date(
759 p_chr_id IN NUMBER := NULL,
760 p_cle_id IN NUMBER := NULL,
761 p_rgp_id IN NUMBER := NULL )
762 RETURN DATE IS
763
764 l_chr_id OKC_K_HEADERS_B.ID%TYPE := p_chr_id;
765 l_cle_id OKC_K_LINES_B.ID%TYPE := p_cle_id;
766 l_rgp_id OKC_RULE_GROUPS_B.ID%TYPE := p_rgp_id;
767
768 l_start_date date;
769
770 cursor KHR_CSR(P_ID IN NUMBER) is
771 SELECT START_DATE
772 FROM
773 OKC_K_HEADERS_B
774 WHERE
775 ID = P_ID;
776
777 cursor CLE_CSR(P_ID IN NUMBER) is
778 SELECT START_DATE
779 FROM
780 OKC_K_LINES_B
781 WHERE
782 ID = P_ID;
783
784 cursor RGP_CSR(P_ID IN NUMBER) is
785 SELECT DNZ_CHR_ID, CLE_ID
786 FROM
787 OKC_RULE_GROUPS_B
788 WHERE
789 ID = P_ID;
790
791 BEGIN
792
793 -- if(l_rgp_id is not null and l_rgp_id <> OKL_API.G_MISS_NUM) then
794 if(l_rgp_id is not null) then
795 open RGP_CSR(l_rgp_id);
796 fetch RGP_CSR into l_chr_id, l_cle_id;
797 close RGP_CSR;
798 end if;
799
800 if(l_cle_id is not null) then
801 open CLE_CSR(l_cle_id);
802 fetch CLE_CSR into l_start_date;
803 close CLE_CSR;
804 if(l_start_date is not null) then
805 return l_start_date;
806 end if;
807 end if;
808 if(l_chr_id is null) then
809 return null;
810 end if;
811 open KHR_CSR(l_chr_id);
812 fetch KHR_CSR into l_start_date;
813 close KHR_CSR;
814 return l_start_date;
815
816 END get_start_date;
817
818 -- bug
819 -- gboomina Added for Bug 6152538
820 -- This function is used to find the end date of a line for
821 -- which we create payment
822 -- For Contract level payments, this will return contract end date and
823 -- For Line level payments, this will return respective line(Asset, Fee or Service)
824 -- end date for which we create payment.
825 FUNCTION get_line_end_date(p_rgp_id IN NUMBER )
826 RETURN DATE
827 IS
828 l_chr_id OKC_K_HEADERS_B.ID%TYPE := NULL;
829 l_cle_id OKC_K_LINES_B.ID%TYPE := NULL;
830 l_rgp_id OKC_RULE_GROUPS_B.ID%TYPE := p_rgp_id;
831
832 l_line_end_date date;
833
834 cursor khr_csr(p_id in number) is
835 select end_date
836 from okc_k_headers_b
837 where id = p_id;
838
839 cursor cle_csr(p_id in number) is
840 select end_date
841 from okc_k_lines_b
842 where id = p_id;
843
844 cursor rgp_csr(p_id in number) is
845 select dnz_chr_id, cle_id
846 from okc_rule_groups_b
847 where id = p_id;
848
849 BEGIN
850 if(l_rgp_id is not null) then
851 open rgp_csr(l_rgp_id);
852 fetch rgp_csr into l_chr_id, l_cle_id;
853 close rgp_csr;
854 end if;
855
856 if(l_cle_id is not null) then
857 open cle_csr(l_cle_id);
858 fetch cle_csr into l_line_end_date;
859 close cle_csr;
860 if(l_line_end_date is not null) then
861 return l_line_end_date;
862 end if;
863 end if;
864
865 if(l_chr_id is null) then
866 return null;
867 end if;
868 open khr_csr(l_chr_id);
869 fetch khr_csr into l_line_end_date;
870 close khr_csr;
871 return l_line_end_date;
872
873 END get_line_end_date;
874
875
876 FUNCTION get_end_date(
877 l_start_date IN DATE,
878 p_frequency IN VARCHAR2,
879 p_period IN NUMBER,
880 ---- cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
881 p_start_day IN NUMBER DEFAULT NULL,
882 p_contract_end_date IN DATE DEFAULT NULL --Bug#5441811
883 )
884 RETURN DATE IS
885 l_end_date date;
886 factor number := 0;
887 BEGIN
888 if(p_frequency = 'M') then
889 factor := 1;
890 elsif(p_frequency = 'Q') then
891 factor := 3;
892 elsif(p_frequency = 'S') then
893 factor := 6;
894 elsif(p_frequency = 'A') then
895 factor := 12;
896 end if;
897
898 -- start: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
899 --l_end_date := ADD_MONTHS(l_start_date, (factor * NVL(p_period,0)));
900 -- l_end_date := l_end_date - 1;
901
902 l_end_date := Okl_Lla_Util_Pvt.calculate_end_date(p_start_date => l_start_date,
903 p_months => factor * NVL(p_period,0),
904 p_start_day => p_start_day,
905 p_contract_end_date => p_contract_end_date );--Bug#5441811
906 -- end: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
907
908
909 return l_end_date;
910 EXCEPTION
911 WHEN OTHERS THEN
912 RETURN null;
913 END get_end_date;
914
915
916 FUNCTION get_display_end_date(
917 p_start_date IN VARCHAR2,
918 p_stub_days IN VARCHAR2,
919 p_frequency IN VARCHAR2,
920 p_period IN VARCHAR2,
921 ---- mvasudev,06-02-2005,Bug#4392051
922 p_start_day IN NUMBER,
923 p_contract_end_date IN DATE DEFAULT NULL --Bug#5441811
924 )
925 RETURN VARCHAR2 IS
926 l_end_date date;
927 l_end_date_disp varchar2(40);
928 BEGIN
929 if(p_stub_days is not null and p_stub_days <> OKL_API.G_MISS_CHAR) then -- end date for stub entry.
930 l_end_date := FND_DATE.canonical_to_date(p_start_date) + to_number(p_stub_days);
931 l_end_date := l_end_date - 1;
932 l_end_date_disp := OKL_LLA_UTIL_PVT.get_display_date(nvl(FND_DATE.date_to_canonical(l_end_date),''));
933 else -- end date for level entry.
934 -- cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
935 --l_end_date := get_end_date(FND_DATE.canonical_to_date(p_start_date), p_frequency, TO_NUMBER(NVL(p_period,0)));
936 --l_end_date := get_end_date(FND_DATE.canonical_to_date(p_start_date), p_frequency, TO_NUMBER(NVL(p_period,0)),p_start_day);
937
938 l_end_date := get_end_date(FND_DATE.canonical_to_date(p_start_date), p_frequency, TO_NUMBER(NVL(p_period,0)),p_start_day, p_contract_end_date); --Bug#5441811
939
940 l_end_date_disp := OKL_LLA_UTIL_PVT.get_display_date(NVL(FND_DATE.date_to_canonical(l_end_date),''));
941 end if;
942 return l_end_date_disp;
943 EXCEPTION
944 WHEN OTHERS THEN
945 RETURN '';
946 END get_display_end_date;
947
948 --START: 07-25-2005 cklee/mvasudev -- Fixed Bug#4392051/okl.h 4437938
949 FUNCTION get_start_day(
950 p_rule_id IN NUMBER
951 ,p_dnz_chr_id IN NUMBER
952 ,p_rgp_id IN NUMBER
953 ,p_slh_id IN NUMBER
954 ,p_start_date IN VARCHAR2)
955 RETURN NUMBER
956 IS
957
958 --Modified cursor for bug 6007644
959 --Added FND_DATE.canonical_to_date(RULE_INFORMATION2)+to_number(RULE_INFORMATION7) to return the recurrence date
960 --Also added FND_DATE.canonical_to_date(rule_information2) to covert the rule_information2 to date format
961 CURSOR l_okl_stub_start_csr
962 IS
963 SELECT MAX(FND_DATE.canonical_to_date(RULE_INFORMATION2)+to_number(RULE_INFORMATION7)) start_date
964 FROM OKC_RULES_V
965 WHERE rgp_id = p_rgp_id
966 AND dnz_chr_id = p_dnz_chr_id
967 AND object2_id1 = p_slh_id
968 AND id <> p_rule_id
969 AND rule_information_category = 'LASLL' --| 17-Jan-06 cklee Fixed bug#4956483 |
970 AND FND_DATE.canonical_to_date(rule_information2) < FND_DATE.canonical_to_date(p_start_date)
971 AND rule_information7 IS NOT NULL
972 ORDER BY start_date;
973
974 CURSOR l_okl_chr_start_csr
975 IS
976 SELECT START_DATE
977 FROM OKC_K_HEADERS_B
978 WHERE ID = p_dnz_chr_id;
979
980 l_start_date DATE;
981 l_sll_count NUMBER := 0;
982 BEGIN
983 OPEN l_okl_sll_count_csr(p_rgp_id,p_dnz_chr_id,p_slh_id);
984 FETCH l_okl_sll_count_csr INTO l_sll_count;
985 CLOSE l_okl_sll_count_csr;
986
987 IF (l_sll_count > 1 ) THEN
988
989 FOR l_okl_stub_start_rec IN l_okl_stub_start_csr
990 LOOP
991 l_start_date := l_okl_stub_start_rec.start_date;
992 END LOOP;
993
994 IF l_start_date IS NULL THEN
995 FOR l_okl_chr_start_rec IN l_okl_chr_start_csr
996 LOOP
997 l_start_date := l_okl_chr_start_rec.start_date;
998 END LOOP;
999 END IF;
1000
1001 IF l_start_date IS NOT NULL THEN
1002 RETURN (TO_CHAR(l_start_date,'DD'));
1003 END IF;
1004 ELSE
1005 RETURN NULL;
1006 END IF;
1007
1008
1009
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 RETURN ''; --Added for bug 6007644
1013 /*
1014 Commented so that the function would return days, which acts as an input parameter
1015 for OKL_LLA_UTIL_PVT.calculate_end_date. If the function returns NULL the
1016 calculate_end_date logic flows into calculating the contract end date logic.
1017 */
1018 END get_start_day;
1019 --END: 07-25-2005 cklee/mvasudev -- Fixed Bug#4392051/okl.h 4437938
1020
1021 FUNCTION get_order_sequence(
1022 p_sequence IN VARCHAR2)
1023 RETURN NUMBER IS
1024 l_sequence number;
1025 BEGIN
1026 if(p_sequence is null) then
1027 return 0;
1028 end if;
1029 l_sequence := to_number(p_sequence);
1030 return l_sequence;
1031 EXCEPTION
1032 WHEN OTHERS THEN
1033 RETURN 0;
1034 END get_order_sequence;
1035
1036
1037 PROCEDURE calculate_details(
1038 p_api_version IN NUMBER,
1039 p_init_msg_list IN VARCHAR2,
1040 x_return_status OUT NOCOPY VARCHAR2,
1041 x_msg_count OUT NOCOPY NUMBER,
1042 x_msg_data OUT NOCOPY VARCHAR2,
1043 p_chr_id IN NUMBER,
1044 p_rgp_id IN NUMBER,
1045 p_slh_id IN VARCHAR2,
1046 structure IN VARCHAR2,
1047 frequency IN VARCHAR2,
1048 arrears IN VARCHAR2,
1049 --Bug# 6438785
1050 p_validate_date_yn IN VARCHAR2 DEFAULT 'Y') IS
1051 i NUMBER := 0;
1052 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
1053 l_start_date date := null;
1054 l_prev_start_date date := null;
1055 l_end_date date := null;
1056 l_k_start_date date := null;
1057 l_chr_id number := null;
1058 factor number := null;
1059
1060 CURSOR sll_instances(P_RGP_ID IN NUMBER,
1061 P_CHR_ID IN NUMBER,
1062 P_SLH_ID IN VARCHAR2
1063 ) IS
1064 SELECT
1065 SLL.*,
1066 FND_DATE.canonical_to_date(nvl(SLL.RULE_INFORMATION2,null)) START_DATE,
1067 OKL_LA_PAYMENTS_PVT.get_order_sequence(SLL.RULE_INFORMATION1) SEQUENCE
1068 FROM OKC_RULES_B SLL
1069 WHERE
1070 SLL.DNZ_CHR_ID = P_CHR_ID
1071 AND SLL.RGP_ID = P_RGP_ID
1072 AND SLL.RULE_INFORMATION_CATEGORY = 'LASLL'
1073 AND SLL.OBJECT2_ID1 = P_SLH_ID
1074 ORDER BY START_DATE, SEQUENCE;
1075
1076 l_rulv_tbl sll_instances%ROWTYPE;
1077
1078 l_rulv_rec OKL_RULE_PUB.rulv_rec_type;
1079 lx_rulv_rec OKL_RULE_PUB.rulv_rec_type;
1080
1081 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1082 l_api_name CONSTANT VARCHAR2(30) := 'CALCULATE_DETAILS';
1083 l_api_version CONSTANT NUMBER := 1.0;
1084
1085 -- cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1086 l_start_day NUMBER;
1087 l_sll_count NUMBER := 0;
1088
1089 l_contract_end_date DATE; --Bug#5441811
1090 l_line_end_date DATE; -- Bug 6152538
1091
1092 begin
1093
1094 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1095 -- call START_ACTIVITY to create savepoint, check compatibility
1096 -- and initialize message list
1097 x_return_status := OKL_API.START_ACTIVITY(
1098 p_api_name => l_api_name,
1099 p_pkg_name => G_PKG_NAME,
1100 p_init_msg_list => p_init_msg_list,
1101 l_api_version => l_api_version,
1102 p_api_version => p_api_version,
1103 p_api_type => G_API_TYPE,
1104 x_return_status => x_return_status);
1105
1106 -- check if activity started successfully
1107 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1108 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1109 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
1110 raise OKL_API.G_EXCEPTION_ERROR;
1111 END IF;
1112 l_end_date := get_start_date(p_rgp_id => p_rgp_id);
1113
1114 -- START: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1115 OPEN l_okl_sll_count_csr(p_rgp_id,p_chr_id,p_slh_id);
1116 FETCH l_okl_sll_count_csr INTO l_sll_count;
1117 CLOSE l_okl_sll_count_csr;
1118
1119 IF (l_sll_count > 1 ) THEN
1120 l_start_day := TO_CHAR(l_end_date,'DD');
1121 END IF;
1122 -- END: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1123
1124 l_end_date := l_end_date - 1;
1125
1126 --For Bug#5441811 selecting the contract end date for calculating
1127 -- end dates in payment structure.
1128 FOR i IN ( SELECT end_date FROM okc_k_headers_b WHERE id = P_CHR_ID)
1129 LOOP
1130 l_contract_end_date := i.end_date;
1131 END LOOP;
1132 --Bug#5441811
1133
1134 FOR rule_rec in sll_instances(P_RGP_ID, P_CHR_ID, P_SLH_ID) loop
1135
1136 if( rule_rec.rule_information7 is not null and -- stub days
1137 rule_rec.rule_information7 <> OKL_API.G_MISS_CHAR ) then
1138 l_start_date := l_end_date + 1;
1139 l_end_date := l_start_date + to_number(rule_rec.rule_information7);
1140 l_end_date := l_end_date - 1;
1141 l_rulv_rec.rule_information2 := FND_DATE.date_to_canonical(l_start_date);
1142 l_rulv_rec.id := rule_rec.id;
1143
1144 -- START: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1145 IF (l_sll_count > 1 ) THEN
1146 l_start_day := TO_CHAR(l_end_date + 1,'DD');
1147 END IF;
1148 -- END: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1149
1150 -- nikshah Bug 7828786 - Start
1151 -- Added to avoid validating stub end date when this API is called
1152 -- at the time of updating the contract or line start dates
1153 IF p_validate_date_yn = 'Y' THEN
1154 -- Check whether stubs end date exceeds contract/line end date.
1155 l_line_end_date := get_line_end_date(p_rgp_id);
1156 if ( trunc(l_end_date) > trunc(l_line_end_date) ) then
1157 x_return_status := OKL_API.g_ret_sts_error;
1158 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1159 , p_msg_name => 'OKL_PAYMENT_DT_EXCEEDS_LINE_DT');
1160 raise OKL_API.G_EXCEPTION_ERROR;
1161 end if;
1162 END IF;
1163 -- nikshah Bug 7828786 - End
1164
1165 elsif(rule_rec.rule_information3 is not null and -- periods
1166 rule_rec.rule_information3 <> OKL_API.G_MISS_CHAR ) then
1167 l_start_date := l_end_date + 1;
1168 --l_end_date := get_end_date(l_start_date, rule_rec.OBJECT1_ID1, rule_rec.RULE_INFORMATION3);
1169 l_end_date := get_end_date(l_start_date, rule_rec.OBJECT1_ID1, rule_rec.RULE_INFORMATION3,l_start_day, l_contract_end_date);--Bug#5441811
1170 l_rulv_rec.rule_information2 := FND_DATE.date_to_canonical(l_start_date);
1171 l_rulv_rec.id := rule_rec.id;
1172
1173 -- Bug# 6438785
1174 -- Added to avoid validating payment end date when this API is called
1175 -- at the time of updating the contract or line start dates
1176 IF p_validate_date_yn = 'Y' THEN
1177 -- gboomina Bug 6152538 - Start
1178 -- Check whether payments end date exceeds contract/line end date.
1179 l_line_end_date := get_line_end_date(p_rgp_id);
1180 if ( trunc(l_end_date) > trunc(l_line_end_date) ) then
1181 x_return_status := OKL_API.g_ret_sts_error;
1182 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1183 , p_msg_name => 'OKL_PAYMENT_DT_EXCEEDS_LINE_DT');
1184 raise OKL_API.G_EXCEPTION_ERROR;
1185 end if;
1186 -- gboomina Bug 6152538 - End
1187 END IF;
1188 -- Bug# 6438785
1189 end if;
1190
1191
1192 if (frequency is not null and frequency <> OKL_API.G_MISS_CHAR) then
1193 l_rulv_rec.jtot_object1_code := 'OKL_TUOM';
1194 l_rulv_rec.object1_id1 := frequency;
1195 end if;
1196 if (arrears is not null and arrears <> OKL_API.G_MISS_CHAR) then
1197 l_rulv_rec.rule_information10 := arrears;
1198 end if;
1199 if (structure is not null and structure <> OKL_API.G_MISS_CHAR) then
1200 l_rulv_rec.rule_information5 := structure;
1201 end if;
1202
1203 l_rulv_rec.rule_information1 := null;
1204
1205 OKL_RULE_PUB.update_rule(
1206 p_api_version => p_api_version,
1207 p_init_msg_list => p_init_msg_list,
1208 x_return_status => x_return_status,
1209 x_msg_count => x_msg_count,
1210 x_msg_data => x_msg_data,
1211 p_rulv_rec => l_rulv_rec,
1212 x_rulv_rec => lx_rulv_rec);
1213
1214
1215 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1216 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1217 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1218 RAISE OKL_API.G_EXCEPTION_ERROR;
1219 END IF;
1220
1221
1222 end loop;
1223 if(sll_instances%ISOPEN) then
1224 close sll_instances;
1225 end if;
1226 -- START: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1227 IF(l_okl_sll_count_csr%ISOPEN) THEN
1228 CLOSE l_okl_sll_count_csr;
1229 END IF;
1230 -- END: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1231
1232 --Call End Activity
1233 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1234 x_msg_data => x_msg_data);
1235
1236
1237 EXCEPTION
1238 when OKL_API.G_EXCEPTION_ERROR then
1239 -- START: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1240 if(sll_instances%ISOPEN) then
1241 close sll_instances;
1242 end if;
1243 IF(l_okl_sll_count_csr%ISOPEN) THEN
1244 CLOSE l_okl_sll_count_csr;
1245 END IF;
1246 -- END: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1247 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1248 p_api_name => l_api_name,
1249 p_pkg_name => G_PKG_NAME,
1250 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1251 x_msg_count => x_msg_count,
1252 x_msg_data => x_msg_data,
1253 p_api_type => G_API_TYPE);
1254
1255 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1256 -- START: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1257 if(sll_instances%ISOPEN) then
1258 close sll_instances;
1259 end if;
1260 IF(l_okl_sll_count_csr%ISOPEN) THEN
1261 CLOSE l_okl_sll_count_csr;
1262 END IF;
1263 -- END: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1264 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1265 p_api_name => l_api_name,
1266 p_pkg_name => G_PKG_NAME,
1267 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1268 x_msg_count => x_msg_count,
1269 x_msg_data => x_msg_data,
1270 p_api_type => G_API_TYPE);
1271
1272 when OTHERS then
1273 -- START: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1274 if(sll_instances%ISOPEN) then
1275 close sll_instances;
1276 end if;
1277 IF(l_okl_sll_count_csr%ISOPEN) THEN
1278 CLOSE l_okl_sll_count_csr;
1279 END IF;
1280 -- END: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1281 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1282 p_api_name => l_api_name,
1283 p_pkg_name => G_PKG_NAME,
1284 p_exc_name => 'OTHERS',
1285 x_msg_count => x_msg_count,
1286 x_msg_data => x_msg_data,
1287 p_api_type => G_API_TYPE);
1288
1289
1290 END calculate_details;
1291
1292
1293 FUNCTION is_num(
1294 p_num IN VARCHAR2)
1295 RETURN boolean IS
1296 l_num number;
1297 BEGIN
1298 if(p_num is null or p_num = OKL_API.G_MISS_CHAR ) then
1299 return false;
1300 else
1301 l_num := to_number(p_num);
1302 return true;
1303 end if;
1304 EXCEPTION
1305 WHEN OTHERS THEN
1306 RETURN false;
1307 END is_num;
1308
1309
1310 PROCEDURE validate_payment(
1311 p_api_version IN NUMBER,
1312 p_init_msg_list IN VARCHAR2,
1313 x_return_status OUT NOCOPY VARCHAR2,
1314 x_msg_count OUT NOCOPY NUMBER,
1315 x_msg_data OUT NOCOPY VARCHAR2,
1316 p_rulv_rec IN rulv_rec_type,
1317 p_fee_line_type IN VARCHAR2,
1318 p_payment_type IN VARCHAR2,
1319 p_type IN VARCHAR2
1320 ) IS
1321
1322
1323 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1324 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_PAYMENTS';
1325 l_api_version CONSTANT NUMBER := 1.0;
1326
1327 i NUMBER := 0;
1328 l_rulv_rec rulv_rec_type := p_rulv_rec;
1329 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
1330 l_lty_code OKC_LINE_STYLES_V.LTY_CODE%TYPE := null;
1331
1332 l_days boolean := false;
1333 l_days_amt boolean := false;
1334 l_period boolean := false;
1335 l_period_amt boolean := false;
1336 -- Start fix for bug 7111749
1337 l_structure boolean := false;
1338 -- End fix for bug 7111749
1339 l_message VARCHAR2(1000);
1340
1341 begin
1342
1343 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1344 -- call START_ACTIVITY to create savepoint, check compatibility
1345 -- and initialize message list
1346 x_return_status := OKL_API.START_ACTIVITY(
1347 p_api_name => l_api_name,
1348 p_pkg_name => G_PKG_NAME,
1349 p_init_msg_list => p_init_msg_list,
1350 l_api_version => l_api_version,
1351 p_api_version => p_api_version,
1352 p_api_type => G_API_TYPE,
1353 x_return_status => x_return_status);
1354
1355 -- check if activity started successfully
1356 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1357 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1358 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
1359 raise OKL_API.G_EXCEPTION_ERROR;
1360 END IF;
1361
1362 if( p_rulv_rec.rule_information7 is not null and
1363 p_rulv_rec.rule_information7 <> OKL_API.G_MISS_CHAR) then
1364 l_days := true;
1365
1366 end if;
1367 if( p_rulv_rec.rule_information8 is not null and
1368 p_rulv_rec.rule_information8 <> OKL_API.G_MISS_CHAR) then
1369 l_days_amt := true;
1370 end if;
1371 if( p_rulv_rec.rule_information3 is not null and
1372 p_rulv_rec.rule_information3 <> OKL_API.G_MISS_CHAR) then
1373 l_period := true;
1374 end if;
1375 if( p_rulv_rec.rule_information6 is not null and
1376 p_rulv_rec.rule_information6 <> OKL_API.G_MISS_CHAR) then
1377 l_period_amt := true;
1378 end if;
1379 -- Start fix for bug 7111749
1380 -- Set l_structure TRUE if non-level
1381 if( p_rulv_rec.rule_information5 is not null and
1382 p_rulv_rec.rule_information5 <> OKL_API.G_MISS_CHAR) and
1383 (TRUNC(NVL(p_rulv_rec.rule_information5, '-1')) <> '0') then
1384 l_structure := true;
1385 end if;
1386 -- End fix for bug 7111749
1387
1388 if(p_payment_type = 'VIR_PAYMENT') then
1389 if(l_period and is_num(p_rulv_rec.rule_information3) and to_number(p_rulv_rec.rule_information3) > 0 ) then
1390 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1391 x_msg_data => x_msg_data);
1392 return;
1393 else
1394 x_return_status := OKL_API.g_ret_sts_error;
1395 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1396 , p_msg_name => l_message);
1397 raise OKL_API.G_EXCEPTION_ERROR;
1398 end if;
1399 end if;
1400
1401 if((l_days and l_period) or (l_days_amt and l_period_amt)) then
1402 l_message := 'OKL_LA_PYM_STUB_PERIOD';
1403 elsif((l_days and not l_days_amt) or (not l_days and l_days_amt)) then
1404 l_message := 'OKL_LA_PYM_STUB';
1405 elsif((l_period and not l_period_amt) or (not l_period and l_period_amt)) then
1406 l_message := 'OKL_LA_PYM_PERIOD';
1407 end if;
1408
1409 if(l_message is null and l_period ) then
1410 if( is_num(p_rulv_rec.rule_information3) and to_number(p_rulv_rec.rule_information3) > 0 ) then
1411 if( not is_num(p_rulv_rec.rule_information6) ) then
1412 l_message := 'OKL_LA_PYM_AMOUNT';
1413 end if;
1414 else
1415 l_message := 'OKL_LA_PYM_PERIOD_ZERO';
1416 end if;
1417 end if;
1418
1419 if(l_message is null and l_days ) then
1420 if( is_num(p_rulv_rec.rule_information7) and to_number(p_rulv_rec.rule_information7) > 0 ) then
1421 if( not is_num(p_rulv_rec.rule_information8) ) then
1422 l_message := 'OKL_LA_PYM_AMOUNT';
1423 end if;
1424 else
1425 l_message := 'OKL_LA_PYM_DAYS_ZERO';
1426 end if;
1427 end if;
1428 -- Start fix for bug 7111749
1429 l_payment_code := get_payment_type(l_rulv_rec.object2_id1);
1430 if (l_payment_code IN ('RENT', 'LOAN_PAYMENT', 'PRINCIPAL_PAYMENT')) then
1431 if(l_message is null and l_structure ) then
1432 if(l_days and l_days_amt) then
1433 l_message := 'OKL_QA_PAYMENT_STUB_NA';
1434 end if;
1435 end if;
1436 end if;
1437 -- End fix for bug 7111749
1438 if(l_message is not null) then
1439 x_return_status := OKL_API.g_ret_sts_error;
1440 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1441 , p_msg_name => l_message);
1442 raise OKL_API.G_EXCEPTION_ERROR;
1443 end if;
1444
1445
1446 if( p_fee_line_type is null or
1447 p_fee_line_type = '' or
1448 p_fee_line_type = OKL_API.G_MISS_CHAR ) then
1449
1450 --x_rulv_rec := l_rulv_rec;
1451
1452 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1453 x_msg_data => x_msg_data);
1454 return;
1455 end if;
1456
1457
1458 if(p_fee_line_type is not null and p_fee_line_type = 'SECDEPOSIT') then
1459
1460 l_payment_code := get_payment_type(l_rulv_rec.object2_id1);
1461 if( l_payment_code <> 'SECURITY_DEPOSIT') then -- cklee: 11/01/04, 12-03-2004
1462
1463 x_return_status := OKL_API.g_ret_sts_error;
1464 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
1465 p_region_code => G_AK_REGION_NAME,
1466 p_attribute_code => 'OKL_LA_PYMTS_TYPE');
1467 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1468 , p_msg_name => 'OKL_LLA_PYMTS_SEC_PYMT'
1469 , p_token1 => 'COL_NAME'
1470 , p_token1_value => l_ak_prompt
1471 );
1472
1473 raise OKL_API.G_EXCEPTION_ERROR;
1474
1475 end if;
1476
1477 end if;
1478
1479
1480 if(p_fee_line_type is not null and p_fee_line_type = 'SECDEPOSIT') then
1481 l_lty_code := get_lty_code(l_rulv_rec.rgp_id);
1482 if(l_lty_code is not null and l_lty_code = 'LINK_FEE_ASSET') then
1483 x_return_status := OKL_API.g_ret_sts_error;
1484 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1485 , p_msg_name => 'OKL_LLA_PYMTS_FEE_NO_ASSET');
1486 raise OKL_API.G_EXCEPTION_ERROR;
1487 end if;
1488 end if;
1489
1490 --Call End Activity
1491 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1492 x_msg_data => x_msg_data);
1493
1494
1495 EXCEPTION
1496 when OKL_API.G_EXCEPTION_ERROR then
1497 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1498 p_api_name => l_api_name,
1499 p_pkg_name => G_PKG_NAME,
1500 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1501 x_msg_count => x_msg_count,
1502 x_msg_data => x_msg_data,
1503 p_api_type => G_API_TYPE);
1504
1505 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1506 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1507 p_api_name => l_api_name,
1508 p_pkg_name => G_PKG_NAME,
1509 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1510 x_msg_count => x_msg_count,
1511 x_msg_data => x_msg_data,
1512 p_api_type => G_API_TYPE);
1513
1514 when OTHERS then
1515 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1516 p_api_name => l_api_name,
1517 p_pkg_name => G_PKG_NAME,
1518 p_exc_name => 'OTHERS',
1519 x_msg_count => x_msg_count,
1520 x_msg_data => x_msg_data,
1521 p_api_type => G_API_TYPE);
1522
1523
1524 END validate_payment;
1525
1526
1527 FUNCTION get_subline_id(
1528 p_chr_id IN NUMBER,
1529 p_topline_id IN NUMBER,
1530 p_asset_id IN NUMBER)
1531 RETURN OKC_K_LINES_B.ID%TYPE IS
1532
1533 l_subline_id OKC_K_LINES_B.ID%TYPE := null;
1534 l_lty_code OKC_LINE_STYLES_b.LTY_CODE%TYPE := null;
1535
1536 cursor LTY_CODE_CSR(P_LINE_ID IN NUMBER) is
1537 SELECT LTY_CODE
1538 FROM
1539 OKC_LINE_STYLES_B LS, OKC_K_LINES_B L
1540 WHERE LS.ID = L.LSE_ID AND L.ID = P_LINE_ID;
1541
1542 cursor SERVICES_CSR(P_CHR_ID IN NUMBER, P_TOPLINE_ID IN NUMBER, P_ASSET_ID IN NUMBER) is
1543 SELECT KLINES1.ID
1544 FROM OKL_PYMTS_SERVICE_INSTS_UV SFINTS,OKC_K_LINES_B KLINES,
1545 OKC_K_LINES_B KLINES1, OKC_LINE_STYLES_B LS, OKC_K_ITEMS KITMS
1546 WHERE KLINES.ID = SFINTS.LINE_ID
1547 AND KLINES.ID = KLINES1.CLE_ID
1548 AND KLINES1.LSE_ID = LS.ID AND LS.LTY_CODE = 'LINK_SERV_ASSET'
1549 AND KITMS.CLE_ID = KLINES1.ID AND KITMS.JTOT_OBJECT1_CODE='OKX_COVASST'
1550 AND KLINES.DNZ_CHR_ID = P_CHR_ID AND KLINES.ID = P_TOPLINE_ID
1551 AND KITMS.OBJECT1_ID1 = P_ASSET_ID;
1552
1553
1554 cursor FEES_CSR(P_CHR_ID IN NUMBER, P_TOPLINE_ID IN NUMBER, P_ASSET_ID IN NUMBER) is
1555 SELECT KLINES1.ID
1556 FROM OKL_PYMTS_FEE_INSTS_UV SFINTS,OKC_K_LINES_B KLINES,
1557 OKC_K_LINES_B KLINES1, OKC_LINE_STYLES_B LS, OKC_K_ITEMS KITMS
1558 WHERE KLINES.ID = SFINTS.LINE_ID
1559 AND KLINES.ID = KLINES1.CLE_ID
1560 AND KLINES1.LSE_ID = LS.ID AND LS.LTY_CODE = 'LINK_FEE_ASSET'
1561 AND KITMS.CLE_ID = KLINES1.ID AND KITMS.JTOT_OBJECT1_CODE='OKX_COVASST'
1562 AND KLINES.DNZ_CHR_ID = P_CHR_ID AND KLINES.ID = P_TOPLINE_ID
1563 AND KITMS.OBJECT1_ID1 = P_ASSET_ID;
1564
1565 BEGIN
1566
1567 open LTY_CODE_CSR(p_topline_id);
1568 fetch LTY_CODE_CSR into l_lty_code;
1569 close LTY_CODE_CSR;
1570
1571
1572 if(l_lty_code = 'SOLD_SERVICE') then
1573 open SERVICES_CSR(p_chr_id, p_topline_id, p_asset_id);
1574 fetch SERVICES_CSR into l_subline_id;
1575 close SERVICES_CSR;
1576 else
1577 open FEES_CSR(p_chr_id, p_topline_id, p_asset_id);
1578 fetch FEES_CSR into l_subline_id;
1579 close FEES_CSR;
1580 end if;
1581 return l_subline_id;
1582 END get_subline_id;
1583
1584
1585 PROCEDURE migrate_rec(
1586 p_chr_id IN NUMBER,
1587 p_rgp_id IN NUMBER,
1588 p_slh_id IN NUMBER,
1589 p_pym_hdr_rec IN pym_hdr_rec_type,
1590 p_pym_rec IN pym_rec_type,
1591 x_rulv_rec OUT NOCOPY rulv_rec_type) IS
1592
1593 l_rulv_rec rulv_rec_type;
1594 i NUMBER := 0;
1595 l_slh_id varchar2(300);
1596
1597 -- temp
1598 valid boolean := true;
1599
1600 Begin
1601
1602 if((p_pym_rec.stub_days is null or p_pym_rec.stub_days = OKL_API.G_MISS_CHAR) and
1603 (p_pym_rec.stub_amount is null or p_pym_rec.stub_amount = OKL_API.G_MISS_CHAR) and
1604 (p_pym_rec.period is null or p_pym_rec.period = OKL_API.G_MISS_CHAR) and
1605 (p_pym_rec.amount is null or p_pym_rec.amount = OKL_API.G_MISS_CHAR)) then
1606 valid := false;
1607 end if;
1608
1609 if(not valid) then
1610 x_rulv_rec := null;
1611 return;
1612 end if;
1613
1614 l_slh_id := to_char(p_slh_id);
1615 if(p_pym_rec.update_type = 'CREATE') then
1616 l_rulv_rec.id := null;
1617 elsif(p_pym_rec.update_type = 'UPDATE') then
1618 l_rulv_rec.id := p_pym_rec.rule_id;
1619 end if;
1620 if(p_pym_rec.update_type <> 'DELETE') then
1621
1622 l_rulv_rec.rule_information7 := p_pym_rec.stub_days;
1623 l_rulv_rec.rule_information8 := p_pym_rec.stub_amount;
1624 l_rulv_rec.rule_information3 := p_pym_rec.period;
1625 l_rulv_rec.rule_information6 := p_pym_rec.amount;
1626 l_rulv_rec.rule_information2 := p_pym_rec.sort_date;
1627 l_rulv_rec.jtot_object1_code := 'OKL_TUOM';
1628 l_rulv_rec.object1_id1 := p_pym_hdr_rec.frequency;
1629 l_rulv_rec.rule_information10 := p_pym_hdr_rec.arrears;
1630 l_rulv_rec.rule_information5 := p_pym_hdr_rec.structure;
1631 l_rulv_rec.jtot_object2_code := 'OKL_STRMHDR';
1632 l_rulv_rec.object2_id1 := l_slh_id;
1633 l_rulv_rec.object2_id2 := '#';
1634 l_rulv_rec.dnz_chr_id := p_chr_id;
1635 l_rulv_rec.rgp_id := p_rgp_id;
1636 l_rulv_rec.sfwt_flag := 'N';
1637 l_rulv_rec.std_template_yn := 'N';
1638 l_rulv_rec.warn_yn := 'N';
1639 l_rulv_rec.rule_information_category := 'LASLL';
1640 end if;
1641
1642
1643 x_rulv_rec := l_rulv_rec;
1644
1645 END migrate_rec;
1646
1647
1648 FUNCTION check_rec(p_pym_rec IN pym_rec_type)
1649 RETURN boolean IS
1650
1651 BEGIN
1652 if((p_pym_rec.stub_days is null or p_pym_rec.stub_days = OKL_API.G_MISS_CHAR) and
1653 (p_pym_rec.stub_amount is null or p_pym_rec.stub_amount = OKL_API.G_MISS_CHAR) and
1654 (p_pym_rec.period is null or p_pym_rec.period = OKL_API.G_MISS_CHAR) and
1655 (p_pym_rec.amount is null or p_pym_rec.amount = OKL_API.G_MISS_CHAR)) then
1656 return true;
1657 else
1658 return false;
1659 end if;
1660 END check_rec;
1661
1662
1663 PROCEDURE get_payment(
1664 p_api_version IN NUMBER,
1665 p_init_msg_list IN VARCHAR2,
1666 x_return_status OUT NOCOPY VARCHAR2,
1667 x_msg_count OUT NOCOPY NUMBER,
1668 x_msg_data OUT NOCOPY VARCHAR2,
1669 p_chr_id OKC_K_HEADERS_B.ID%TYPE,
1670 p_service_fee_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM,
1671 p_asset_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM,
1672 p_payment_id OKL_STRMTYP_SOURCE_V.ID1%TYPE,
1673 x_pym_level OUT NOCOPY VARCHAR2,
1674 x_slh_id OUT NOCOPY OKC_RULES_V.ID%TYPE,
1675 x_rgp_id OUT NOCOPY OKC_RULE_GROUPS_V.ID%TYPE,
1676 x_cle_id OUT NOCOPY OKC_K_LINES_B.ID%TYPE) IS
1677
1678 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1679 l_api_name CONSTANT VARCHAR2(30) := 'GET_PAYMENT';
1680 l_api_version CONSTANT NUMBER := 1.0;
1681
1682 l_pym_level VARCHAR2(30) := 'HEADER';
1683 l_subline_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
1684 l_slh_id OKC_RULES_V.ID%TYPE := OKL_API.G_MISS_NUM;
1685 l_rgp_id OKC_RULE_GROUPS_V.ID%TYPE := OKL_API.G_MISS_NUM;
1686 l_cle_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
1687
1688 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
1689
1690 ------------- temp
1691 Cursor get_dir is
1692 SELECT nvl(substrb(translate(ltrim(value),',',' '),
1693 1,
1694 instr(translate(ltrim(value),',',' '),' ') - 1),value)
1695 FROM v$parameter
1696 WHERE name = 'utl_file_dir';
1697
1698 l_TEMP_DIR varchar2(200);
1699 delimit varchar2(10) := ' ';
1700
1701 ------------- temp
1702
1703 cursor RGP_HDR_CSR(P_CHR_ID IN NUMBER) is
1704 SELECT
1705 ID
1706 FROM OKC_RULE_GROUPS_V WHERE
1707 DNZ_CHR_ID = P_CHR_ID AND CHR_ID = P_CHR_ID
1708 AND RGD_CODE = 'LALEVL'
1709 AND CLE_ID IS NULL;
1710
1711 cursor RGP_CLE_CSR(P_CHR_ID IN NUMBER, P_CLE_ID IN NUMBER) is
1712 SELECT
1713 ID
1714 FROM OKC_RULE_GROUPS_V RG WHERE
1715 RG.DNZ_CHR_ID = P_CHR_ID AND RG.CHR_ID IS NULL
1716 AND RGD_CODE = 'LALEVL'
1717 AND RG.CLE_ID = P_CLE_ID;
1718
1719 cursor SLH_CSR(P_RGP_ID IN NUMBER, P_PAYMENT_ID IN VARCHAR2) is
1720 SELECT
1721 ID
1722 FROM OKC_RULES_B SLH WHERE
1723 SLH.RGP_ID = P_RGP_ID AND
1724 -- bug 3377730.
1725 SLH.OBJECT1_ID1 = P_PAYMENT_ID;
1726 -- SLH.OBJECT1_ID1 = TO_CHAR(P_PAYMENT_ID);
1727
1728 begin
1729
1730 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1731 -- call START_ACTIVITY to create savepoint, check compatibility
1732 -- and initialize message list
1733 x_return_status := OKL_API.START_ACTIVITY(
1734 p_api_name => l_api_name,
1735 p_pkg_name => G_PKG_NAME,
1736 p_init_msg_list => p_init_msg_list,
1737 l_api_version => l_api_version,
1738 p_api_version => p_api_version,
1739 p_api_type => G_API_TYPE,
1740 x_return_status => x_return_status);
1741
1742 -- check if activity started successfully
1743 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1744 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1745 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
1746 raise OKL_API.G_EXCEPTION_ERROR;
1747 END IF;
1748
1749 /* -- temp
1750 open get_dir;
1751 fetch get_dir into l_temp_dir;
1752 if get_dir%notfound then
1753 null;
1754 end if;
1755 close get_dir;
1756 */
1757 --fnd_file.put_names(l_log, l_out, l_temp_dir);
1758 -- temp
1759
1760
1761 if( p_payment_id is null or p_payment_id = OKL_API.G_MISS_NUM) then
1762 x_return_status := OKL_API.g_ret_sts_error;
1763 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1764 , p_msg_name => 'OKL_LLA_PMT_SELECT');
1765 raise OKL_API.G_EXCEPTION_ERROR;
1766 end if;
1767
1768
1769 if( p_asset_id is not null and p_asset_id <> OKL_API.G_MISS_NUM) then
1770 l_pym_level := 'ASSET';
1771 l_cle_id := p_asset_id;
1772 end if;
1773 if(p_service_fee_id is not null and p_service_fee_id <> OKL_API.G_MISS_NUM) then
1774 if(l_pym_level = 'ASSET') then
1775 l_pym_level := 'SUBLINE';
1776 else
1777 l_pym_level := 'SERVICE_FEE';
1778 l_cle_id := p_service_fee_id;
1779 end if;
1780 end if;
1781
1782
1783 if(l_pym_level = 'SUBLINE') then
1784 l_subline_id := get_subline_id(p_chr_id, p_service_fee_id, p_asset_id);
1785 l_cle_id := l_subline_id;
1786 end if;
1787
1788 if(l_pym_level = 'HEADER') then
1789 open RGP_HDR_CSR(p_chr_id);
1790 fetch RGP_HDR_CSR into l_rgp_id;
1791 close RGP_HDR_CSR;
1792 else
1793 open RGP_CLE_CSR(p_chr_id, l_cle_id);
1794 fetch RGP_CLE_CSR into l_rgp_id;
1795 close RGP_CLE_CSR;
1796 end if;
1797
1798 if(l_rgp_id is not null and l_rgp_id <> OKL_API.G_MISS_NUM) then
1799 open SLH_CSR(l_rgp_id, p_payment_id);
1800 fetch SLH_CSR into l_slh_id;
1801 close SLH_CSR;
1802
1803 end if;
1804
1805
1806 x_pym_level := l_pym_level;
1807 x_slh_id := l_slh_id;
1808 x_rgp_id := l_rgp_id;
1809 x_cle_id := l_cle_id;
1810
1811
1812 --Call End Activity
1813 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1814 x_msg_data => x_msg_data);
1815
1816 EXCEPTION
1817 when OKL_API.G_EXCEPTION_ERROR then
1818 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1819 p_api_name => l_api_name,
1820 p_pkg_name => G_PKG_NAME,
1821 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1822 x_msg_count => x_msg_count,
1823 x_msg_data => x_msg_data,
1824 p_api_type => G_API_TYPE);
1825
1826 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1827 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1828 p_api_name => l_api_name,
1829 p_pkg_name => G_PKG_NAME,
1830 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1831 x_msg_count => x_msg_count,
1832 x_msg_data => x_msg_data,
1833 p_api_type => G_API_TYPE);
1834
1835 when OTHERS then
1836 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1837 p_api_name => l_api_name,
1838 p_pkg_name => G_PKG_NAME,
1839 p_exc_name => 'OTHERS',
1840 x_msg_count => x_msg_count,
1841 x_msg_data => x_msg_data,
1842 p_api_type => G_API_TYPE);
1843
1844 END get_payment;
1845
1846
1847 FUNCTION is_investor_fee_payment(p_chr_id IN NUMBER,
1848 p_payment IN NUMBER, p_rgp_id IN NUMBER)
1849 RETURN boolean IS
1850
1851 cursor inv_dtls_csr(p_chr_id IN NUMBER) is
1852 SELECT scs_code
1853 FROM OKC_K_HEADERS_B
1854 WHERE ID = p_chr_id ;
1855 CURSOR fee_line_amount_csr(p_rgp_id IN NUMBER) is
1856 SELECT kleb.amount amount
1857 FROM okc_k_lines_b cleb,
1858 okl_k_lines kleb,
1859 okc_line_styles_b lseb,
1860 okc_k_headers_b chrb,
1861 okc_rule_groups_b rg
1862 WHERE chrb.id = cleb.dnz_chr_id
1863 AND kleb.id = cleb.id
1864 AND cleb.lse_id = lseb.id
1865 AND lseb.lty_code = 'FEE'
1866 AND rg.cle_id = cleb.id
1867 AND rg.id = p_rgp_id;
1868
1869
1870
1871 l_inv_dtls inv_dtls_csr%ROWTYPE;
1872 l_fee_amount_dtls fee_line_amount_csr%ROWTYPE ;
1873
1874
1875 --l_ret_value VARCHAR2(1);
1876
1877 BEGIN
1878
1879 --l_ret_value := OKL_API.G_FALSE;
1880
1881 OPEN inv_dtls_csr(p_chr_id => p_chr_id);
1882 FETCH inv_dtls_csr INTO l_inv_dtls;
1883 CLOSE inv_dtls_csr;
1884
1885 IF l_inv_dtls.scs_code = 'INVESTOR' THEN
1886 OPEN fee_line_amount_csr(p_rgp_id => p_rgp_id);
1887 FETCH fee_line_amount_csr INTO l_fee_amount_dtls;
1888 CLOSE fee_line_amount_csr;
1889
1890
1891 IF(p_payment=l_fee_amount_dtls.amount) THEN
1892 return true;
1893 ELSE
1894 return false;
1895 END IF;
1896 ELSE
1897 return true;
1898 END IF ;
1899
1900
1901 RETURN true;
1902
1903 END is_investor_fee_payment;
1904
1905
1906 PROCEDURE process_payment(
1907 p_api_version IN NUMBER,
1908 p_init_msg_list IN VARCHAR2,
1909 x_return_status OUT NOCOPY VARCHAR2,
1910 x_msg_count OUT NOCOPY NUMBER,
1911 x_msg_data OUT NOCOPY VARCHAR2,
1912 p_chr_id OKC_K_HEADERS_B.ID%TYPE,
1913 p_service_fee_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM,
1914 p_asset_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM,
1915 p_payment_id OKL_STRMTYP_SOURCE_V.ID1%TYPE,
1916 p_pym_hdr_rec IN pym_hdr_rec_type,
1917 p_pym_tbl IN pym_tbl_type,
1918 p_update_type IN VARCHAR2,
1919 x_rulv_tbl OUT NOCOPY rulv_tbl_type) IS
1920
1921 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1922 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_PAYMENT';
1923 l_api_version CONSTANT NUMBER := 1.0;
1924 l_totalpayment NUMBER :=0 ;
1925 i NUMBER := 0;
1926 j NUMBER := 0;
1927 k NUMBER := 0;
1928 l NUMBER := 0;
1929 kount NUMBER := 0;
1930 empty_rec boolean := false;
1931
1932 l_start_date OKC_K_HEADERS_B.START_DATE%TYPE := null;
1933 l_org_id OKC_K_HEADERS_B.AUTHORING_ORG_ID%TYPE := null;
1934 l_ccode OKC_K_HEADERS_B.CURRENCY_CODE%TYPE := null;
1935
1936 l_crea_rulv_tbl rulv_tbl_type;
1937 l_updt_rulv_tbl rulv_tbl_type;
1938 l_delt_rulv_tbl rulv_tbl_type;
1939
1940 l_rulv_rec rulv_rec_type := NULL;
1941 lx_rulv_rec rulv_rec_type := NULL;
1942 l_rulv_rec2 rulv_rec_type := NULL;
1943
1944 lx_rulv_tbl rulv_tbl_type;
1945
1946 l_rgpv_rec OKL_RULE_PUB.rgpv_rec_type := NULL;
1947 lx_rgpv_rec OKL_RULE_PUB.rgpv_rec_type := NULL;
1948
1949 l_pym_level VARCHAR2(30) := 'HEADER';
1950 l_subline_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
1951 l_slh_id OKC_RULES_V.ID%TYPE := OKL_API.G_MISS_NUM;
1952 l_rgp_id OKC_RULE_GROUPS_V.ID%TYPE := OKL_API.G_MISS_NUM;
1953 l_cle_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
1954 l_stream_id OKC_RULES_V.OBJECT1_ID1%TYPE := OKL_API.G_MISS_CHAR;
1955
1956 l_fee_line_type OKL_K_FEE_LINES_UV.FEE_TYPE%TYPE := null;
1957 l_lty_code OKC_LINE_STYLES_V.LTY_CODE%TYPE := null;
1958 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
1959
1960 ------------- temp
1961 Cursor get_dir is
1962 SELECT nvl(substrb(translate(ltrim(value),',',' '),
1963 1,
1964 instr(translate(ltrim(value),',',' '),' ') - 1),value)
1965 FROM v$parameter
1966 WHERE name = 'utl_file_dir';
1967
1968 l_TEMP_DIR varchar2(200);
1969 delimit varchar2(10) := ' ';
1970
1971 ------------- temp
1972
1973 cursor CHR_DTLS_CSR(P_CHR_ID IN NUMBER) is
1974 SELECT
1975 -- bug
1976 -- START_DATE,
1977 -- bug
1978 A.AUTHORING_ORG_ID,
1979 A.CURRENCY_CODE,
1980 B.DEAL_TYPE -- Bug 4887014
1981 FROM OKC_K_HEADERS_B A, OKL_K_HEADERS B WHERE
1982 A.ID = P_CHR_ID
1983 AND A.ID = B.ID;
1984
1985 --Changed query for performance --dkagrawa
1986 CURSOR FEE_LINE_CSR(P_RGP_ID IN NUMBER) is
1987 SELECT kleb.fee_type fee_type
1988 FROM okc_k_lines_b cleb,
1989 okl_k_lines kleb,
1990 okc_line_styles_b lseb,
1991 okc_k_headers_b chrb,
1992 okc_rule_groups_b rg
1993 WHERE chrb.id = cleb.dnz_chr_id
1994 AND kleb.id = cleb.id
1995 AND cleb.lse_id = lseb.id
1996 AND lseb.lty_code = 'FEE'
1997 AND rg.cle_id = cleb.id
1998 AND rg.id = p_rgp_id;
1999
2000
2001 CURSOR INVALID_LINE_CSR(P_CLE_ID IN NUMBER) is
2002 SELECT 'Y'
2003 FROM OKC_STATUSES_V OKCS, OKC_K_LINES_B CLE
2004 WHERE CLE.STS_CODE = OKCS.CODE
2005 -- AND OKCS.STE_CODE IN ('EXPIRED','HOLD','CANCELLED','TERMINATED')
2006 AND OKCS.STE_CODE IN ('TERMINATED')
2007 AND CLE.ID = P_CLE_ID;
2008 -- added for bug 5115701 - start
2009 CURSOR GET_FEE_TYPE_SUB_CSR(P_CHR_ID IN NUMBER, p_service_fee_id IN NUMBER) is
2010 SELECT fee_line.fee_type
2011 FROM okl_k_lines_full_v l,
2012 okc_line_styles_v sty,
2013 okc_statuses_v sts,
2014 okl_k_lines fee_line
2015 WHERE l.lse_id = sty.id
2016 AND l.sts_code = sts.code
2017 AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED')
2018 AND sty.lty_code = 'LINK_FEE_ASSET'
2019 AND l.dnz_chr_id = P_CHR_ID
2020 AND l.cle_id = fee_line.id
2021 and fee_line.id = p_service_fee_id;
2022
2023 l_subline_fee_type okl_k_lines.fee_type%type;
2024 -- added for bug 5115701 - end
2025 l_invalid_line varchar2(1) := 'N';
2026
2027 l_chr_rec CHR_DTLS_CSR%ROWTYPE;
2028 l_deal_type OKL_K_HEADERS.DEAL_TYPE%TYPE;
2029
2030 -- R12B Authoring OA Migration
2031 l_upfront_tax_pymt_yn VARCHAR2(1);
2032
2033 CURSOR l_contract_csr(p_chr_id IN NUMBER) IS
2034 SELECT chrb.sts_code
2035 FROM okc_k_headers_b chrb
2036 WHERE chrb.id = p_chr_id;
2037
2038 l_contract_rec l_contract_csr%ROWTYPE;
2039
2040 begin
2041
2042
2043
2044 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2045 -- call START_ACTIVITY to create savepoint, check compatibility
2046 -- and initialize message list
2047 x_return_status := OKL_API.START_ACTIVITY(
2048 p_api_name => l_api_name,
2049 p_pkg_name => G_PKG_NAME,
2050 p_init_msg_list => p_init_msg_list,
2051 l_api_version => l_api_version,
2052 p_api_version => p_api_version,
2053 p_api_type => G_API_TYPE,
2054 x_return_status => x_return_status);
2055
2056 -- check if activity started successfully
2057 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2058 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2059 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2060 raise OKL_API.G_EXCEPTION_ERROR;
2061 END IF;
2062
2063 /* -- temp
2064 open get_dir;
2065 fetch get_dir into l_temp_dir;
2066 if get_dir%notfound then
2067 null;
2068 end if;
2069 close get_dir;
2070 */
2071 --fnd_file.put_names(l_log, l_out, l_temp_dir);
2072 -- temp
2073
2074 -- R12B Authoring OA Migration
2075 -- Check if the Payment is for an Upfront Tax Fee line
2076 /* IF(not is_investor_fee_payment(p_chr_id =>p_chr_id, p_cle_id => p_service_fee_id , p_rgp_id => l_rulv_rec.rgp_id )) then
2077 x_return_status := OKL_API.g_ret_sts_error;
2078 OKL_API.SET_MESSAGE( p_app_name => g_app_name
2079 , p_msg_name => 'OKL_LA_INV_PAY_MISMATCH');
2080 raise OKL_API.G_EXCEPTION_ERROR;
2081 end if;
2082 */
2083 l_upfront_tax_pymt_yn := OKL_API.G_FALSE;
2084 IF (p_service_fee_id IS NOT NULL) THEN
2085 l_upfront_tax_pymt_yn := is_upfront_tax_fee_payment(p_chr_id => p_chr_id,
2086 p_cle_id => p_service_fee_id);
2087
2088 IF (l_upfront_tax_pymt_yn = OKL_API.G_TRUE) THEN
2089 OPEN l_contract_csr(p_chr_id => p_chr_id);
2090 FETCH l_contract_csr INTO l_contract_rec;
2091 CLOSE l_contract_csr;
2092
2093 END IF;
2094 END IF;
2095
2096 -- START: June 24, 2005 cklee: Modification for okl.h Sales Quote enhancement
2097 x_return_status := validate_capitalize_dp
2098 (p_asset_id => p_asset_id,
2099 p_service_fee_id => p_service_fee_id,
2100 p_payment_id => p_payment_id);
2101 --- Store the highest degree of error
2102 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2103 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2104 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2105 raise OKL_API.G_EXCEPTION_ERROR;
2106 END IF;
2107 -- END: June 24, 2005 cklee: Modification for okl.h Sales Quote enhancement
2108
2109 -- START: Setp 02, 2005 cklee: Variable rate ER for GE - 20
2110 x_return_status := validate_payment_type_asset
2111 (p_chr_id => p_chr_id,
2112 p_asset_id => p_asset_id,
2113 p_service_fee_id => p_service_fee_id,
2114 p_payment_id => p_payment_id);
2115 --- Store the highest degree of error
2116 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2117 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2118 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2119 raise OKL_API.G_EXCEPTION_ERROR;
2120 END IF;
2121 -- END: Setp 02, 2005 cklee: Variable rate ER for GE - 20
2122
2123 get_payment(
2124 p_api_version => p_api_version,
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_chr_id => p_chr_id,
2130 p_service_fee_id => p_service_fee_id,
2131 p_asset_id => p_asset_id,
2132 p_payment_id => p_payment_id,
2133 x_pym_level => l_pym_level,
2134 x_slh_id => l_slh_id,
2135 x_rgp_id => l_rgp_id,
2136 x_cle_id => l_cle_id);
2137
2138
2139 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2140 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2141 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2142 raise OKL_API.G_EXCEPTION_ERROR;
2143 END IF;
2144
2145 --Bug# 4959361
2146 IF l_cle_id IS NOT NULL THEN
2147 OKL_LLA_UTIL_PVT.check_line_update_allowed
2148 (p_api_version => p_api_version,
2149 p_init_msg_list => p_init_msg_list,
2150 x_return_status => x_return_status,
2151 x_msg_count => x_msg_count,
2152 x_msg_data => x_msg_data,
2153 p_cle_id => l_cle_id);
2154
2155 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2156 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2157 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2158 RAISE OKL_API.G_EXCEPTION_ERROR;
2159 END IF;
2160 END IF;
2161 --Bug# 4959361
2162
2163 if(l_rgp_id is null or l_rgp_id = OKL_API.G_MISS_NUM) then
2164 if(l_pym_level = 'HEADER') then
2165 l_rgpv_rec.chr_id := p_chr_id;
2166 l_rgpv_rec.dnz_chr_id := p_chr_id;
2167 l_rgpv_rec.cle_id := null;
2168 else
2169 l_rgpv_rec.chr_id := null;
2170 l_rgpv_rec.dnz_chr_id := p_chr_id;
2171 l_rgpv_rec.cle_id := l_cle_id;
2172 end if;
2173
2174 l_rgpv_rec.rgd_code := 'LALEVL';
2175 l_rgpv_rec.rgp_type := 'KRG';
2176
2177
2178
2179
2180 OKL_RULE_PUB.create_rule_group(
2181 p_api_version => p_api_version,
2182 p_init_msg_list => p_init_msg_list,
2183 x_return_status => x_return_status,
2184 x_msg_count => x_msg_count,
2185 x_msg_data => x_msg_data,
2186 p_rgpv_rec => l_rgpv_rec,
2187 x_rgpv_rec => lx_rgpv_rec);
2188
2189
2190 l_rgp_id := lx_rgpv_rec.id;
2191
2192 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2193 RAISE OKL_API.G_EXCEPTION_ERROR;
2194 END IF;
2195 end if;
2196
2197
2198 IF(l_slh_id is null or l_slh_id = OKL_API.G_MISS_NUM) then
2199 l_rulv_rec := l_rulv_rec2;
2200 -- l_rulv_rec.object_version_number := l_rgr_rec.object_version_number;
2201 l_rulv_rec.dnz_chr_id := p_chr_id;
2202 l_rulv_rec.rgp_id := l_rgp_id;
2203 l_rulv_rec.jtot_object1_code := 'OKL_STRMTYP';
2204 l_rulv_rec.object1_id1 := p_payment_id;
2205 l_rulv_rec.std_template_yn := 'N';
2206 l_rulv_rec.warn_yn := 'N';
2207 l_rulv_rec.template_yn := 'N';
2208 l_rulv_rec.sfwt_flag := 'N';
2209 l_rulv_rec.rule_information_category := 'LASLH';
2210
2211 OKL_RULE_PUB.create_rule(
2212 p_api_version => p_api_version,
2213 p_init_msg_list => p_init_msg_list,
2214 x_return_status => x_return_status,
2215 x_msg_count => x_msg_count,
2216 x_msg_data => x_msg_data,
2217 p_rulv_rec => l_rulv_rec,
2218 x_rulv_rec => lx_rulv_rec);
2219
2220 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2221 RAISE OKL_API.G_EXCEPTION_ERROR;
2222 END IF;
2223 l_slh_id := lx_rulv_rec.id;
2224 end if;
2225
2226 OPEN FEE_LINE_CSR(l_rgp_id);
2227 FETCH FEE_LINE_CSR INTO l_fee_line_type;
2228 CLOSE FEE_LINE_CSR;
2229
2230 open CHR_DTLS_CSR(p_chr_id);
2231 -- bug
2232 fetch CHR_DTLS_CSR into l_org_id, l_ccode, l_deal_type;
2233 l_start_date := get_start_date(p_chr_id, l_cle_id);
2234 -- bug
2235 close CHR_DTLS_CSR;
2236 MO_GLOBAL.set_policy_context('S',l_org_id);
2237
2238
2239 j := 0;
2240 k := 0;
2241 l := 0;
2242
2243 if(p_pym_tbl.count > 0) then
2244 i := p_pym_tbl.FIRST;
2245 loop
2246 l_rulv_rec := null;
2247 empty_rec := check_rec(p_pym_tbl(i));
2248
2249 if(p_pym_tbl(i).update_type = 'DELETE' or (empty_rec
2250 and p_pym_tbl(i).rule_id is not null and p_pym_tbl(i).rule_id <> OKL_API.G_MISS_NUM)) then
2251 l_rulv_rec.id := p_pym_tbl(i).rule_id;
2252 l := l + 1;
2253 l_delt_rulv_tbl(l) := l_rulv_rec;
2254
2255 else if ( not empty_rec) then
2256
2257
2258 migrate_rec(
2259 p_chr_id => p_chr_id,
2260 p_rgp_id => l_rgp_id,
2261 p_slh_id => l_slh_id,
2262 p_pym_hdr_rec => p_pym_hdr_rec,
2263 p_pym_rec => p_pym_tbl(i),
2264 x_rulv_rec => l_rulv_rec);
2265
2266
2267 validate_payment(
2268 p_api_version => p_api_version,
2269 p_init_msg_list => p_init_msg_list,
2270 x_return_status => x_return_status,
2271 x_msg_count => x_msg_count,
2272 x_msg_data => x_msg_data,
2273 p_rulv_rec => l_rulv_rec,
2274 p_fee_line_type => l_fee_line_type,
2275 p_payment_type => p_update_type,
2276 p_type => p_pym_tbl(i).update_type);
2277
2278
2279 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2280 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2281 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2282 raise OKL_API.G_EXCEPTION_ERROR;
2283 END IF;
2284
2285 if(l_rulv_rec.rule_information8 is not null and is_num(l_rulv_rec.rule_information8)) then
2286 l_rulv_rec.rule_information8 := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(
2287 p_amount => to_number(l_rulv_rec.rule_information8),
2288 p_currency_code => l_ccode);
2289 l_totalpayment:=l_totalpayment+l_rulv_rec.rule_information8;
2290 end if;
2291
2292 if(l_rulv_rec.rule_information6 is not null and is_num(l_rulv_rec.rule_information6)) then
2293 l_rulv_rec.rule_information6 := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(
2294 p_amount => to_number(l_rulv_rec.rule_information6),
2295 p_currency_code => l_ccode);
2296 l_totalpayment:=l_totalpayment+(l_rulv_rec.rule_information6*l_rulv_rec.rule_information3);
2297 end if;
2298
2299 if(p_pym_tbl(i).update_type = 'CREATE') then
2300 j := j + 1;
2301 l_crea_rulv_tbl(j) := l_rulv_rec;
2302 else
2303 k := k + 1;
2304 l_updt_rulv_tbl(k) := l_rulv_rec;
2305 end if;
2306 end if;
2307 end if;
2308
2309 exit when (i >= p_pym_tbl.last);
2310 i:= p_pym_tbl.NEXT(i);
2311 end loop;
2312 end if;
2313 IF(not is_investor_fee_payment(p_chr_id =>p_chr_id, p_payment =>l_totalpayment , p_rgp_id => l_rgp_id )) then
2314 x_return_status := OKL_API.g_ret_sts_error;
2315 OKL_API.SET_MESSAGE( p_app_name => g_app_name
2316 , p_msg_name => 'OKL_LA_INV_PAY_MISMATCH');
2317 raise OKL_API.G_EXCEPTION_ERROR;
2318 end if;
2319
2320 if (l_delt_rulv_tbl.count > 0) then
2321 OKL_RULE_PUB.delete_rule(
2322 p_api_version => p_api_version,
2323 p_init_msg_list => p_init_msg_list,
2324 x_return_status => x_return_status,
2325 x_msg_count => x_msg_count,
2326 x_msg_data => x_msg_data,
2327 p_rulv_tbl => l_delt_rulv_tbl);
2328
2329 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2330 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2331 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2332 raise OKL_API.G_EXCEPTION_ERROR;
2333 END IF;
2334 end if;
2335
2336 if (l_crea_rulv_tbl.count > 0) then
2337 --Bug# 4861465
2338 i := l_crea_rulv_tbl.FIRST;
2339 --i := p_pym_tbl.FIRST;
2340 loop
2341 l_crea_rulv_tbl(i).rule_information1 := to_char(i);
2342 exit when (i >= l_crea_rulv_tbl.last);
2343 i:= l_crea_rulv_tbl.NEXT(i);
2344 end loop;
2345
2346 OKL_RULE_PUB.create_rule(
2347 p_api_version => p_api_version,
2348 p_init_msg_list => p_init_msg_list,
2349 x_return_status => x_return_status,
2350 x_msg_count => x_msg_count,
2351 x_msg_data => x_msg_data,
2352 p_rulv_tbl => l_crea_rulv_tbl,
2353 x_rulv_tbl => x_rulv_tbl);
2354
2355
2356
2357 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2358 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2359 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2360 RAISE OKL_API.G_EXCEPTION_ERROR;
2361 END IF;
2362 end if;
2363
2364 if (l_updt_rulv_tbl.count > 0) then
2365
2366 -- added for bug 5115701 - start
2367 -- check only if it's an asset sub line payment
2368 IF (p_asset_id IS NOT NULL AND
2369 p_asset_id <> OKL_API.G_MISS_NUM)
2370 AND
2371 (p_service_fee_id IS NOT NULL AND
2372 p_service_fee_id <> OKL_API.G_MISS_NUM)
2373 THEN
2374
2375 OPEN GET_FEE_TYPE_SUB_CSR(p_chr_id, p_service_fee_id);
2376 FETCH GET_FEE_TYPE_SUB_CSR INTO l_subline_fee_type;
2377 CLOSE GET_FEE_TYPE_SUB_CSR;
2378
2379
2380 IF(l_subline_fee_type IS NOT NULL AND l_subline_fee_type = 'FINANCED') then
2381
2382 x_return_status := OKL_API.g_ret_sts_error;
2383 OKL_API.SET_MESSAGE( p_app_name => g_app_name
2384 , p_msg_name => 'OKL_LLA_PYMTS_NO_UPDATE');
2385 raise OKL_API.G_EXCEPTION_ERROR;
2386 END IF;
2387 END IF;
2388 -- added for bug 5115701 - End
2389
2390 OKL_RULE_PUB.update_rule(
2391 p_api_version => p_api_version,
2392 p_init_msg_list => p_init_msg_list,
2393 x_return_status => x_return_status,
2394 x_msg_count => x_msg_count,
2395 x_msg_data => x_msg_data,
2396 p_rulv_tbl => l_updt_rulv_tbl,
2397 x_rulv_tbl => x_rulv_tbl);
2398
2399
2400 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2401 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2402 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2403 RAISE OKL_API.G_EXCEPTION_ERROR;
2404 END IF;
2405 end if;
2406
2407 if(l_pym_level <> 'HEADER') then
2408 OPEN INVALID_LINE_CSR(l_cle_id);
2409 FETCH INVALID_LINE_CSR INTO l_invalid_line;
2410 CLOSE INVALID_LINE_CSR;
2411 end if;
2412
2413 l_detail_count := get_sll_period_count(l_rgp_id,
2414 l_slh_id,
2415 p_chr_id);
2416 if( l_detail_count < 1 and l_invalid_line = 'N') then
2417 IF (l_deal_type NOT IN ('LOAN', 'LOAN-REVOLVING')) THEN -- Bug 4887014
2418 x_return_status := OKL_API.g_ret_sts_error;
2419 OKL_API.SET_MESSAGE( p_app_name => g_app_name
2420 , p_msg_name => 'OKL_LLA_PYM_ONE_PERIOD');
2421 raise OKL_API.G_EXCEPTION_ERROR;
2422 END IF;
2423 end if;
2424 if(l_fee_line_type is not null and l_fee_line_type = 'SECDEPOSIT') then
2425 l_detail_count := verify_sec_deposit_count(l_rgp_id,
2426 l_slh_id,
2427 p_chr_id);
2428 if( l_detail_count > 1 ) then
2429 x_return_status := OKL_API.g_ret_sts_error;
2430 OKL_API.SET_MESSAGE( p_app_name => g_app_name
2431 , p_msg_name => 'OKL_LLA_PYMTS_FEE_PERIOD');
2432 raise OKL_API.G_EXCEPTION_ERROR;
2433 end if;
2434 end if;
2435
2436
2437 calculate_details(
2438 p_api_version => p_api_version,
2439 p_init_msg_list => p_init_msg_list,
2440 x_return_status => x_return_status,
2441 x_msg_count => x_msg_count,
2442 x_msg_data => x_msg_data,
2443 p_chr_id => p_chr_id,
2444 p_rgp_id => l_rgp_id,
2445 p_slh_id => l_slh_id,
2446 structure => p_pym_hdr_rec.STRUCTURE,
2447 frequency => p_pym_hdr_rec.FREQUENCY,
2448 arrears => p_pym_hdr_rec.ARREARS);
2449
2450
2451 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2452 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2453 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2454 RAISE OKL_API.G_EXCEPTION_ERROR;
2455 END IF;
2456
2457 lx_rulv_tbl(1).id := l_slh_id;
2458 lx_rulv_tbl(1).rgp_id := l_rgp_id;
2459 lx_rulv_tbl(1).dnz_chr_id := p_chr_id;
2460
2461 x_rulv_tbl := lx_rulv_tbl;
2462
2463 -- R12B Authoring OA Migration
2464 IF (l_upfront_tax_pymt_yn = OKL_API.G_TRUE) THEN
2465 IF (l_contract_rec.sts_code IN ('PASSED','COMPLETE','APPROVED','PENDING_APPROVAL')) THEN
2466
2467 OKL_LA_PAYMENTS_PVT.process_upfront_tax_pymt(
2468 p_api_version => p_api_version,
2469 p_init_msg_list => p_init_msg_list,
2470 x_return_status => x_return_status,
2471 x_msg_count => x_msg_count,
2472 x_msg_data => x_msg_data,
2473 p_chr_id => p_chr_id);
2474
2475 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2476 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2477 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2478 RAISE OKL_API.G_EXCEPTION_ERROR;
2479 END IF;
2480
2481 END IF;
2482 END IF;
2483
2484 -- Bug# 7440232
2485 -- Delete Interest Rate payments for FIXED/STREAMS
2486 -- Loans when all Principal payments are deleted
2487 OKL_LA_PAYMENTS_PVT.delete_interest_rate_params(
2488 p_api_version => p_api_version,
2489 p_init_msg_list => p_init_msg_list,
2490 x_return_status => x_return_status,
2491 x_msg_count => x_msg_count,
2492 x_msg_data => x_msg_data,
2493 p_chr_id => p_chr_id);
2494
2495 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2496 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2497 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2498 RAISE OKL_API.G_EXCEPTION_ERROR;
2499 END IF;
2500
2501 --Call End Activity
2502 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2503 x_msg_data => x_msg_data);
2504
2505 EXCEPTION
2506 when OKL_API.G_EXCEPTION_ERROR then
2507 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2508 p_api_name => l_api_name,
2509 p_pkg_name => G_PKG_NAME,
2510 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2511 x_msg_count => x_msg_count,
2512 x_msg_data => x_msg_data,
2513 p_api_type => G_API_TYPE);
2514
2515 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
2516 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2517 p_api_name => l_api_name,
2518 p_pkg_name => G_PKG_NAME,
2519 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2520 x_msg_count => x_msg_count,
2521 x_msg_data => x_msg_data,
2522 p_api_type => G_API_TYPE);
2523
2524 when OTHERS then
2525 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2526 p_api_name => l_api_name,
2527 p_pkg_name => G_PKG_NAME,
2528 p_exc_name => 'OTHERS',
2529 x_msg_count => x_msg_count,
2530 x_msg_data => x_msg_data,
2531 p_api_type => G_API_TYPE);
2532
2533 END process_payment;
2534
2535
2536 PROCEDURE variable_interest_payment(
2537 p_api_version IN NUMBER,
2538 p_init_msg_list IN VARCHAR2,
2539 x_return_status OUT NOCOPY VARCHAR2,
2540 x_msg_count OUT NOCOPY NUMBER,
2541 x_msg_data OUT NOCOPY VARCHAR2,
2542 p_chr_id IN NUMBER,
2543 x_rulv_tbl OUT NOCOPY rulv_tbl_type
2544 ) IS
2545 i NUMBER := 0;
2546 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
2547
2548 l_rulv_rec OKL_RULE_PUB.rulv_rec_type;
2549 lx_rulv_rec OKL_RULE_PUB.rulv_rec_type;
2550 -- x_rulv_tbl OKL_RULE_PUB.RULV_TBL_TYPE;
2551
2552 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2553 l_api_name CONSTANT VARCHAR2(30) := 'VARIABLE_INTEREST_PAYMENT';
2554 l_api_version CONSTANT NUMBER := 1.0;
2555
2556 l_payment_id NUMBER;
2557 l_pym_hdr_rec PYM_HDR_REC_TYPE;
2558 l_pym_tbl PYM_TBL_TYPE;
2559
2560 l_flag VARCHAR2(1) := 'N';
2561 l_term okl_k_headers.term_duration%TYPE;
2562
2563 l_pym_level VARCHAR2(30) := 'HEADER';
2564 l_slh_id OKC_RULES_V.ID%TYPE := OKL_API.G_MISS_NUM;
2565 l_rgp_id OKC_RULE_GROUPS_V.ID%TYPE := OKL_API.G_MISS_NUM;
2566 l_cle_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
2567
2568 -- x_return_status varchar2(1);
2569 x_primary_sty_id okl_strm_type_b.ID%TYPE;
2570
2571 /*
2572 *CURSOR VIR_PAYMENT (P_CHR_ID IN NUMBER) IS
2573 *SELECT 'Y', K.TERM_DURATION
2574 *FROM OKC_RULES_B IVAR, OKC_RULES_B INTP, OKC_RULE_GROUPS_B RG, OKL_K_HEADERS K
2575 *WHERE
2576 *IVAR.RULE_INFORMATION_CATEGORY = 'LAIVAR' AND IVAR.RULE_INFORMATION1 = 'FLOAT' AND
2577 *INTP.RULE_INFORMATION_CATEGORY = 'LAINTP' AND INTP.RULE_INFORMATION1 = 'Y' AND
2578 *RG.ID = IVAR.RGP_ID AND RG.RGD_CODE = 'LAIIND' AND RG.ID = INTP.RGP_ID
2579 *AND K.ID = P_CHR_ID AND RG.DNZ_CHR_ID = P_CHR_ID AND RG.CHR_ID = P_CHR_ID;
2580 */
2581
2582 CURSOR VIR_PAYMENT (P_CHR_ID IN NUMBER) IS
2583 SELECT IVAR.RULE_INFORMATION1 var_method,
2584 K.DEAL_TYPE deal_type,
2585 ICLC.RULE_INFORMATION5 calc_method,
2586 K.TERM_DURATION
2587 FROM
2588 OKC_RULES_B IVAR,
2589 OKC_RULES_B INTP,
2590 OKC_RULES_B ICLC,
2591 OKC_RULE_GROUPS_B RG,
2592 OKL_K_HEADERS K
2593 WHERE
2594 IVAR.RULE_INFORMATION_CATEGORY = 'LAIVAR'
2595 AND INTP.RULE_INFORMATION_CATEGORY = 'LAINTP'
2596 AND ICLC.RULE_INFORMATION_CATEGORY = 'LAICLC'
2597 AND INTP.RULE_INFORMATION1 = 'Y'
2598 AND RG.ID = IVAR.RGP_ID
2599 AND RG.RGD_CODE = 'LAIIND'
2600 AND RG.ID = INTP.RGP_ID
2601 AND RG.ID = ICLC.RGP_ID
2602 AND RG.CLE_ID IS NULL
2603 AND K.ID = P_CHR_ID
2604 AND RG.DNZ_CHR_ID = P_CHR_ID
2605 AND RG.CHR_ID = P_CHR_ID;
2606
2607 l_deal_type OKL_K_HEADERS.deal_type%TYPE;
2608 l_var_method OKC_RULES_B.RULE_INFORMATION1%TYPE;
2609 l_calc_method OKC_RULES_B.RULE_INFORMATION5%TYPE;
2610 l_data_found BOOLEAN;
2611
2612 /*
2613 cursor PAYMENT_TYPE is
2614 SELECT ID1
2615 FROM OKL_STRMTYP_SOURCE_V WHERE
2616 CODE = 'VARIABLE_INTEREST';
2617 */
2618
2619 begin
2620
2621 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2622 -- call START_ACTIVITY to create savepoint, check compatibility
2623 -- and initialize message list
2624 x_return_status := OKL_API.START_ACTIVITY(
2625 p_api_name => l_api_name,
2626 p_pkg_name => G_PKG_NAME,
2627 p_init_msg_list => p_init_msg_list,
2628 l_api_version => p_api_version,
2629 p_api_version => p_api_version,
2630 p_api_type => G_API_TYPE,
2631 x_return_status => x_return_status);
2632
2633 -- check if activity started successfully
2634 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2635 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2636 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2637 raise OKL_API.G_EXCEPTION_ERROR;
2638 END IF;
2639
2640
2641 --Bug 4018298
2642 OPEN VIR_PAYMENT(P_CHR_ID);
2643 FETCH VIR_PAYMENT INTO l_var_method, l_deal_type, l_calc_method, l_term;
2644 l_data_found := VIR_PAYMENT%FOUND;
2645 CLOSE VIR_PAYMENT;
2646 IF ( l_data_found
2647 AND ( l_deal_type = 'LOAN-REVOLVING' OR l_deal_type = 'LOAN' )
2648 AND l_var_method = 'FLOAT' AND l_calc_method = 'FORMULA') THEN
2649
2650 OKL_STREAMS_UTIL.get_primary_stream_type(
2651 p_khr_id => p_chr_id,
2652 p_primary_sty_purpose => 'VARIABLE_INTEREST',
2653 x_return_status => l_return_status,
2654 x_primary_sty_id => l_payment_id
2655 );
2656
2657 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2658 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2659 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2660 raise OKL_API.G_EXCEPTION_ERROR;
2661 END IF;
2662
2663 /*
2664 OPEN PAYMENT_TYPE;
2665 FETCH PAYMENT_TYPE INTO l_payment_id;
2666 CLOSE PAYMENT_TYPE;
2667 */
2668
2669 get_payment(
2670 p_api_version => p_api_version,
2671 p_init_msg_list => p_init_msg_list,
2672 x_return_status => x_return_status,
2673 x_msg_count => x_msg_count,
2674 x_msg_data => x_msg_data,
2675 p_chr_id => p_chr_id,
2676 p_service_fee_id => null,
2677 p_asset_id => null,
2678 p_payment_id => l_payment_id,
2679 x_pym_level => l_pym_level,
2680 x_slh_id => l_slh_id,
2681 x_rgp_id => l_rgp_id,
2682 x_cle_id => l_cle_id);
2683
2684
2685 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2686 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2687 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2688 raise OKL_API.G_EXCEPTION_ERROR;
2689 END IF;
2690
2691 if(l_slh_id is not null and l_slh_id <> OKL_API.G_MISS_NUM) then
2692 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2693 x_msg_data => x_msg_data);
2694 return;
2695 end if;
2696
2697 /*OPEN VIR_PAYMENT(P_CHR_ID);
2698 *FETCH VIR_PAYMENT INTO l_flag, l_term;
2699 *CLOSE VIR_PAYMENT;
2700 *
2701 *if(l_flag <> 'Y') then
2702 * OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2703 * x_msg_data => x_msg_data);
2704 * return;
2705 *end if;
2706 */
2707
2708 l_pym_hdr_rec.STRUCTURE := 0;
2709 l_pym_hdr_rec.STRUCTURE_NAME := NULL;
2710 l_pym_hdr_rec.FREQUENCY := 'M';
2711 l_pym_hdr_rec.FREQUENCY_NAME := NULL;
2712 l_pym_hdr_rec.ARREARS := 'Y';
2713 l_pym_hdr_rec.ARREARS_NAME := NULL;
2714
2715 l_pym_tbl(1).RULE_ID := NULL;
2716 l_pym_tbl(1).STUB_DAYS := NULL;
2717 l_pym_tbl(1).STUB_AMOUNT := NULL;
2718 -- l_pym_tbl(1).PERIOD := 1;
2719 l_pym_tbl(1).PERIOD := l_term;
2720 l_pym_tbl(1).AMOUNT := NULL;
2721 l_pym_tbl(1).SORT_DATE := NULL;
2722 l_pym_tbl(1).UPDATE_TYPE := 'CREATE';
2723
2724
2725 process_payment(
2726 p_api_version => p_api_version,
2727 p_init_msg_list => p_init_msg_list,
2728 x_return_status => x_return_status,
2729 x_msg_count => x_msg_count,
2730 x_msg_data => x_msg_data,
2731 p_chr_id => p_chr_id,
2732 p_service_fee_id => null,
2733 p_asset_id => null,
2734 p_payment_id => l_payment_id,
2735 p_pym_hdr_rec => l_pym_hdr_rec,
2736 p_pym_tbl => l_pym_tbl,
2737 p_update_type => 'VIR_PAYMENT',
2738 x_rulv_tbl => x_rulv_tbl);
2739
2740 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2741 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2742 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2743 raise OKL_API.G_EXCEPTION_ERROR;
2744 END IF;
2745 END IF;
2746
2747 -- ----------------------------------------
2748
2749 --Call End Activity
2750 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2751 x_msg_data => x_msg_data);
2752
2753
2754 EXCEPTION
2755 when OKL_API.G_EXCEPTION_ERROR then
2756 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2757 p_api_name => l_api_name,
2758 p_pkg_name => G_PKG_NAME,
2759 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2760 x_msg_count => x_msg_count,
2761 x_msg_data => x_msg_data,
2762 p_api_type => G_API_TYPE);
2763
2764 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
2765 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2766 p_api_name => l_api_name,
2767 p_pkg_name => G_PKG_NAME,
2768 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2769 x_msg_count => x_msg_count,
2770 x_msg_data => x_msg_data,
2771 p_api_type => G_API_TYPE);
2772
2773 when OTHERS then
2774 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2775 p_api_name => l_api_name,
2776 p_pkg_name => G_PKG_NAME,
2777 p_exc_name => 'OTHERS',
2778 x_msg_count => x_msg_count,
2779 x_msg_data => x_msg_data,
2780 p_api_type => G_API_TYPE);
2781
2782
2783 END variable_interest_payment;
2784
2785
2786
2787 PROCEDURE process_payment(
2788 p_api_version IN NUMBER,
2789 p_init_msg_list IN VARCHAR2,
2790 x_return_status OUT NOCOPY VARCHAR2,
2791 x_msg_count OUT NOCOPY NUMBER,
2792 x_msg_data OUT NOCOPY VARCHAR2,
2793 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
2794 p_service_fee_id IN OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM,
2795 p_asset_id IN OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM,
2796 p_payment_id IN OKL_STRMTYP_SOURCE_V.ID1%TYPE,
2797 p_update_type IN VARCHAR2,
2798 x_rulv_tbl OUT NOCOPY rulv_tbl_type) IS
2799
2800 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2801 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_PAYMENT2';
2802 l_api_version CONSTANT NUMBER := 1.0;
2803
2804 l_pym_level VARCHAR2(30) := 'HEADER';
2805 l_subline_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
2806 l_slh_id OKC_RULES_V.ID%TYPE := OKL_API.G_MISS_NUM;
2807 l_rgp_id OKC_RULE_GROUPS_V.ID%TYPE := OKL_API.G_MISS_NUM;
2808 l_cle_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
2809 l_stream_id OKL_STRMTYP_SOURCE_V.ID1%TYPE := OKL_API.G_MISS_NUM;
2810
2811 lx_rulv_tbl rulv_tbl_type;
2812
2813 -- R12B Authoring OA Migration
2814 l_upfront_tax_pymt_yn VARCHAR2(1);
2815
2816 CURSOR l_contract_csr(p_chr_id IN NUMBER) IS
2817 SELECT chrb.sts_code
2818 FROM okc_k_headers_b chrb
2819 WHERE chrb.id = p_chr_id;
2820
2821 l_contract_rec l_contract_csr%ROWTYPE;
2822
2823 begin
2824
2825 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2826 -- call START_ACTIVITY to create savepoint, check compatibility
2827 -- and initialize message list
2828 x_return_status := OKL_API.START_ACTIVITY(
2829 p_api_name => l_api_name,
2830 p_pkg_name => G_PKG_NAME,
2831 p_init_msg_list => p_init_msg_list,
2832 l_api_version => p_api_version,
2833 p_api_version => p_api_version,
2834 p_api_type => G_API_TYPE,
2835 x_return_status => x_return_status);
2836
2837 -- check if activity started successfully
2838 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2839 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2840 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2841 raise OKL_API.G_EXCEPTION_ERROR;
2842 END IF;
2843
2844 /* -- temp
2845 open get_dir;
2846 fetch get_dir into l_temp_dir;
2847 if get_dir%notfound then
2848 null;
2849 end if;
2850 close get_dir;
2851 */
2852 --fnd_file.put_names(l_log, l_out, l_temp_dir);
2853 -- temp
2854
2855 -- R12B Authoring OA Migration
2856 -- Check if the Payment is for an Upfront Tax Fee line
2857 l_upfront_tax_pymt_yn := OKL_API.G_FALSE;
2858 IF (p_service_fee_id IS NOT NULL) THEN
2859 l_upfront_tax_pymt_yn := is_upfront_tax_fee_payment(p_chr_id => p_chr_id,
2860 p_cle_id => p_service_fee_id);
2861
2862 IF (l_upfront_tax_pymt_yn = OKL_API.G_TRUE) THEN
2863 OPEN l_contract_csr(p_chr_id => p_chr_id);
2864 FETCH l_contract_csr INTO l_contract_rec;
2865 CLOSE l_contract_csr;
2866 END IF;
2867 END IF;
2868
2869 -- START: June 24, 2005 cklee: Modification for okl.h Sales Quote enhancement
2870 x_return_status := validate_capitalize_dp
2871 (p_asset_id => p_asset_id,
2872 p_service_fee_id => p_service_fee_id,
2873 p_payment_id => p_payment_id);
2874 --- Store the highest degree of error
2875 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2876 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2877 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2878 raise OKL_API.G_EXCEPTION_ERROR;
2879 END IF;
2880 -- END: June 24, 2005 cklee: Modification for okl.h Sales Quote enhancement
2881
2882 /* -- 4542290 Do not create var interest schedules. Will do from
2883 contract creation API
2884 if(p_update_type is not null and p_update_type = 'VIR_PAYMENT') then
2885 variable_interest_payment(
2886 p_api_version => p_api_version,
2887 p_init_msg_list => p_init_msg_list,
2888 x_return_status => x_return_status,
2889 x_msg_count => x_msg_count,
2890 x_msg_data => x_msg_data,
2891 p_chr_id => p_chr_id,
2892 x_rulv_tbl => lx_rulv_tbl
2893 );
2894
2895 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2896 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2897 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2898 raise OKL_API.G_EXCEPTION_ERROR;
2899 END IF;
2900 /*
2901 lx_rulv_tbl(1).id := l_slh_id;
2902 lx_rulv_tbl(1).rgp_id := l_rgp_id;
2903 lx_rulv_tbl(1).dnz_chr_id := p_chr_id;
2904 */
2905
2906 /* - 4542290
2907 x_rulv_tbl := lx_rulv_tbl;
2908
2909 --Call End Activity
2910 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2911 x_msg_data => x_msg_data);
2912 return;
2913
2914 end if; */
2915
2916 -- else
2917
2918 get_payment(
2919 p_api_version => p_api_version,
2920 p_init_msg_list => p_init_msg_list,
2921 x_return_status => x_return_status,
2922 x_msg_count => x_msg_count,
2923 x_msg_data => x_msg_data,
2924 p_chr_id => p_chr_id,
2925 p_service_fee_id => p_service_fee_id,
2926 p_asset_id => p_asset_id,
2927 p_payment_id => p_payment_id,
2928 x_pym_level => l_pym_level,
2929 x_slh_id => l_slh_id,
2930 x_rgp_id => l_rgp_id,
2931 x_cle_id => l_cle_id);
2932
2933
2934 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2935 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2936 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2937 raise OKL_API.G_EXCEPTION_ERROR;
2938 END IF;
2939
2940 --Bug# 4959361
2941 IF l_cle_id IS NOT NULL THEN
2942 OKL_LLA_UTIL_PVT.check_line_update_allowed
2943 (p_api_version => p_api_version,
2944 p_init_msg_list => p_init_msg_list,
2945 x_return_status => x_return_status,
2946 x_msg_count => x_msg_count,
2947 x_msg_data => x_msg_data,
2948 p_cle_id => l_cle_id);
2949
2950 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2951 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2952 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2953 RAISE OKL_API.G_EXCEPTION_ERROR;
2954 END IF;
2955 END IF;
2956 --Bug# 4959361
2957
2958 if(p_update_type is not null and p_update_type = 'DELETE') then
2959 if(l_slh_id is not null and l_slh_id <> OKL_API.G_MISS_NUM
2960 and l_rgp_id is not null and l_rgp_id <> OKL_API.G_MISS_NUM) then
2961 OKL_PAYMENT_APPLICATION_PUB.delete_payment(
2962 p_api_version => p_api_version,
2963 p_init_msg_list => p_init_msg_list,
2964 x_return_status => x_return_status,
2965 x_msg_count => x_msg_count,
2966 x_msg_data => x_msg_data,
2967 p_chr_id => p_chr_id,
2968 p_rgp_id => l_rgp_id,
2969 p_rule_id => l_slh_id);
2970
2971 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2972 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2973 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2974 raise OKL_API.G_EXCEPTION_ERROR;
2975 END IF;
2976 else
2977 x_return_status := OKL_API.g_ret_sts_error;
2978 OKL_API.SET_MESSAGE( p_app_name => g_app_name
2979 , p_msg_name => 'OKL_LLA_PMT_SELECT');
2980 raise OKL_API.G_EXCEPTION_ERROR;
2981 end if;
2982 elsif(p_update_type is not null and p_update_type = 'APPLY') then
2983 if(p_chr_id is not null and p_chr_id <> OKL_API.G_MISS_NUM
2984 and p_payment_id is not null and p_payment_id <> OKL_API.G_MISS_NUM) then
2985 l_stream_id := to_char(p_payment_id);
2986 if is_prop_tax_payment(l_stream_id) then
2987 OKL_PAYMENT_APPLICATION_PVT.apply_propery_tax_payment(
2988 p_api_version => p_api_version,
2989 p_init_msg_list => p_init_msg_list,
2990 x_return_status => x_return_status,
2991 x_msg_count => x_msg_count,
2992 x_msg_data => x_msg_data,
2993 p_chr_id => p_chr_id,
2994 p_stream_id => l_stream_id);
2995 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2996 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2997 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2998 raise OKL_API.G_EXCEPTION_ERROR;
2999 END IF;
3000 /* Manu 13-Sep-2004. For Rollover Fee Payment */
3001 -- start: cklee: 6/22/05 -- okl.h
3002 -- elsif is_rollover_fee_payment(p_service_fee_id) then
3003 elsif is_eligible_fee_payment(p_service_fee_id) then
3004 -- end: cklee: 6/22/05 -- okl.h
3005 -- OKL_PAYMENT_APPLICATION_PVT.apply_rollover_fee_payment(
3006 OKL_PAYMENT_APPLICATION_PVT.apply_eligible_fee_payment(
3007 -- end: cklee: 6/22/05 -- okl.h
3008 p_api_version => p_api_version,
3009 p_init_msg_list => p_init_msg_list,
3010 x_return_status => x_return_status,
3011 x_msg_count => x_msg_count,
3012 x_msg_data => x_msg_data,
3013 p_chr_id => p_chr_id,
3014 p_kle_id => p_service_fee_id,
3015 p_stream_id => l_stream_id);
3016 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3017 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3018 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3019 raise OKL_API.G_EXCEPTION_ERROR;
3020 END IF;
3021 else
3022 OKL_PAYMENT_APPLICATION_PUB.apply_payment(
3023 p_api_version => p_api_version,
3024 p_init_msg_list => p_init_msg_list,
3025 x_return_status => x_return_status,
3026 x_msg_count => x_msg_count,
3027 x_msg_data => x_msg_data,
3028 p_chr_id => p_chr_id,
3029 p_stream_id => l_stream_id);
3030 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3031 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3032 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3033 raise OKL_API.G_EXCEPTION_ERROR;
3034 END IF;
3035 end if;
3036 else
3037 x_return_status := OKL_API.g_ret_sts_error;
3038 OKL_API.SET_MESSAGE( p_app_name => g_app_name
3039 , p_msg_name => 'OKL_LLA_PMT_SELECT');
3040 raise OKL_API.G_EXCEPTION_ERROR;
3041 end if;
3042 elsif(p_update_type is not null and p_update_type = 'CALCULATE') then
3043 if(l_slh_id is not null and l_slh_id <> OKL_API.G_MISS_NUM
3044 and l_rgp_id is not null and l_rgp_id <> OKL_API.G_MISS_NUM) then
3045
3046 calculate_details(
3047 p_api_version => p_api_version,
3048 p_init_msg_list => p_init_msg_list,
3049 x_return_status => x_return_status,
3050 x_msg_count => x_msg_count,
3051 x_msg_data => x_msg_data,
3052 p_chr_id => p_chr_id,
3053 p_rgp_id => l_rgp_id,
3054 p_slh_id => l_slh_id,
3055 structure => null,
3056 frequency => null,
3057 arrears => null);
3058
3059 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3060 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3061 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3062 RAISE OKL_API.G_EXCEPTION_ERROR;
3063 END IF;
3064
3065 else
3066 x_return_status := OKL_API.g_ret_sts_error;
3067 OKL_API.SET_MESSAGE( p_app_name => g_app_name
3068 , p_msg_name => 'OKL_LLA_PMT_SELECT');
3069 raise OKL_API.G_EXCEPTION_ERROR;
3070 end if;
3071 end if;
3072
3073 -- end if;
3074
3075 lx_rulv_tbl(1).id := l_slh_id;
3076 lx_rulv_tbl(1).rgp_id := l_rgp_id;
3077 lx_rulv_tbl(1).dnz_chr_id := p_chr_id;
3078
3079 x_rulv_tbl := lx_rulv_tbl;
3080
3081 -- R12B Authoring OA Migration
3082 IF (l_upfront_tax_pymt_yn = OKL_API.G_TRUE) THEN
3083 IF (l_contract_rec.sts_code IN ('PASSED','COMPLETE','APPROVED','PENDING_APPROVAL')) THEN
3084
3085 OKL_LA_PAYMENTS_PVT.process_upfront_tax_pymt(
3086 p_api_version => p_api_version,
3087 p_init_msg_list => p_init_msg_list,
3088 x_return_status => x_return_status,
3089 x_msg_count => x_msg_count,
3090 x_msg_data => x_msg_data,
3091 p_chr_id => p_chr_id);
3092
3093 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3094 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3095 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3096 RAISE OKL_API.G_EXCEPTION_ERROR;
3097 END IF;
3098
3099 END IF;
3100 END IF;
3101
3102 -- Bug# 7440232
3103 -- Delete Interest Rate payments for FIXED/STREAMS
3104 -- Loans when all Principal payments are deleted
3105 OKL_LA_PAYMENTS_PVT.delete_interest_rate_params(
3106 p_api_version => p_api_version,
3107 p_init_msg_list => p_init_msg_list,
3108 x_return_status => x_return_status,
3109 x_msg_count => x_msg_count,
3110 x_msg_data => x_msg_data,
3111 p_chr_id => p_chr_id);
3112
3113 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3114 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3115 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3116 RAISE OKL_API.G_EXCEPTION_ERROR;
3117 END IF;
3118
3119 --Call End Activity
3120 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3121 x_msg_data => x_msg_data);
3122
3123 EXCEPTION
3124 when OKL_API.G_EXCEPTION_ERROR then
3125 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3126 p_api_name => l_api_name,
3127 p_pkg_name => G_PKG_NAME,
3128 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3129 x_msg_count => x_msg_count,
3130 x_msg_data => x_msg_data,
3131 p_api_type => G_API_TYPE);
3132
3133 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
3134 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3135 p_api_name => l_api_name,
3136 p_pkg_name => G_PKG_NAME,
3137 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3138 x_msg_count => x_msg_count,
3139 x_msg_data => x_msg_data,
3140 p_api_type => G_API_TYPE);
3141
3142 when OTHERS then
3143 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3144 p_api_name => l_api_name,
3145 p_pkg_name => G_PKG_NAME,
3146 p_exc_name => 'OTHERS',
3147 x_msg_count => x_msg_count,
3148 x_msg_data => x_msg_data,
3149 p_api_type => G_API_TYPE);
3150
3151
3152 END process_payment;
3153
3154 PROCEDURE delete_payment(
3155 p_api_version IN NUMBER,
3156 p_init_msg_list IN VARCHAR2,
3157 x_return_status OUT NOCOPY VARCHAR2,
3158 x_msg_count OUT NOCOPY NUMBER,
3159 x_msg_data OUT NOCOPY VARCHAR2,
3160 p_del_pym_tbl IN pym_del_tbl_type,
3161 -- Bug #7498330
3162 p_source_trx IN VARCHAR2 DEFAULT 'NA') IS
3163
3164
3165 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3166 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PAYMENT';
3167 l_api_version CONSTANT NUMBER := 1.0;
3168
3169 i NUMBER := 0;
3170
3171 -- R12B Authoring OA Migration
3172 l_upfront_tax_pymt_yn VARCHAR2(1);
3173
3174 CURSOR l_contract_csr(p_chr_id IN NUMBER) IS
3175 SELECT chrb.sts_code
3176 FROM okc_k_headers_b chrb
3177 WHERE chrb.id = p_chr_id;
3178
3179 l_contract_rec l_contract_csr%ROWTYPE;
3180
3181 CURSOR l_pymt_cle_csr(p_rgp_id IN NUMBER) IS
3182 SELECT rgp.cle_id pymt_cle_id,
3183 cleb.cle_id parent_cle_id
3184 FROM okc_rule_groups_b rgp,
3185 okc_k_lines_b cleb
3186 WHERE rgp.id = p_rgp_id
3187 AND cleb.id = rgp.cle_id;
3188
3189 l_pymt_cle_rec l_pymt_cle_csr%ROWTYPE;
3190 l_cle_id OKC_K_LINES_B.id%TYPE;
3191 l_chr_id OKC_K_HEADERS_B.id%TYPE;
3192
3193 begin
3194
3195 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3196 -- call START_ACTIVITY to create savepoint, check compatibility
3197 -- and initialize message list
3198 x_return_status := OKL_API.START_ACTIVITY(
3199 p_api_name => l_api_name,
3200 p_pkg_name => G_PKG_NAME,
3201 p_init_msg_list => p_init_msg_list,
3202 l_api_version => l_api_version,
3203 p_api_version => p_api_version,
3204 p_api_type => G_API_TYPE,
3205 x_return_status => x_return_status);
3206
3207 -- check if activity started successfully
3208 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3209 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3210 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3211 raise OKL_API.G_EXCEPTION_ERROR;
3212 END IF;
3213
3214
3215 -- R12B Authoring OA Migration
3216 -- Check if the Payment is for an Upfront Tax Fee line
3217 l_upfront_tax_pymt_yn := OKL_API.G_FALSE;
3218
3219 if (p_del_pym_tbl.count > 0) then
3220 i := p_del_pym_tbl.FIRST;
3221 loop
3222 --bug # 7498330 added check for source trx is Termination (TQ)
3223 --added by rkuttiya
3224 IF p_source_trx <> 'TQ' THEN
3225 if( is_ppd_payment(p_del_pym_tbl(i).slh_id)) then
3226 x_return_status := OKL_API.g_ret_sts_error;
3227 OKL_API.SET_MESSAGE( p_app_name => g_app_name
3228 , p_msg_name => 'OKL_LA_PPD_PAYMENT');
3229 raise OKL_API.G_EXCEPTION_ERROR;
3230 end if;
3231 END IF;
3232
3233 -- R12B Authoring OA Migration
3234 -- Check if the Payment is for an Upfront Tax Fee line
3235 IF (p_del_pym_tbl(i).rgp_id IS NOT NULL) THEN
3236
3237 OPEN l_pymt_cle_csr(p_rgp_id => p_del_pym_tbl(i).rgp_id);
3238 FETCH l_pymt_cle_csr INTO l_pymt_cle_rec;
3239 CLOSE l_pymt_cle_csr;
3240
3241 l_cle_id := NVL(l_pymt_cle_rec.parent_cle_id,l_pymt_cle_rec.pymt_cle_id);
3242
3243 IF l_cle_id IS NOT NULL THEN
3244
3245 l_chr_id := p_del_pym_tbl(i).chr_id;
3246 IF (is_upfront_tax_fee_payment(p_chr_id => l_chr_id,
3247 p_cle_id => l_cle_id) = OKL_API.G_TRUE) THEN
3248 l_upfront_tax_pymt_yn := OKL_API.G_TRUE;
3249
3250 OPEN l_contract_csr(p_chr_id => l_chr_id);
3251 FETCH l_contract_csr INTO l_contract_rec;
3252 CLOSE l_contract_csr;
3253 END IF;
3254 END IF;
3255 END IF;
3256
3257 OKL_PAYMENT_APPLICATION_PUB.delete_payment(
3258 p_api_version => p_api_version,
3259 p_init_msg_list => p_init_msg_list,
3260 x_return_status => x_return_status,
3261 x_msg_count => x_msg_count,
3262 x_msg_data => x_msg_data,
3263 p_chr_id => p_del_pym_tbl(i).chr_id,
3264 p_rgp_id => p_del_pym_tbl(i).rgp_id,
3265 p_rule_id => p_del_pym_tbl(i).slh_id);
3266
3267
3268
3269 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3270 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3271 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3272 RAISE OKL_API.G_EXCEPTION_ERROR;
3273 END IF;
3274
3275 exit when (i >= p_del_pym_tbl.last);
3276 i:= p_del_pym_tbl.NEXT(i);
3277 end loop;
3278 end if;
3279
3280 -- R12B Authoring OA Migration
3281 IF (l_upfront_tax_pymt_yn = OKL_API.G_TRUE) THEN
3282 IF (l_contract_rec.sts_code IN ('PASSED','COMPLETE','APPROVED','PENDING_APPROVAL')) THEN
3283
3284 OKL_LA_PAYMENTS_PVT.process_upfront_tax_pymt(
3285 p_api_version => p_api_version,
3286 p_init_msg_list => p_init_msg_list,
3287 x_return_status => x_return_status,
3288 x_msg_count => x_msg_count,
3289 x_msg_data => x_msg_data,
3290 p_chr_id => l_chr_id);
3291
3292 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3293 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3294 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3295 RAISE OKL_API.G_EXCEPTION_ERROR;
3296 END IF;
3297
3298 END IF;
3299 END IF;
3300
3301 -- Bug# 7440232
3302 -- Delete Interest Rate payments for FIXED/STREAMS
3303 -- Loans when all Principal payments are deleted
3304 OKL_LA_PAYMENTS_PVT.delete_interest_rate_params(
3305 p_api_version => p_api_version,
3306 p_init_msg_list => p_init_msg_list,
3307 x_return_status => x_return_status,
3308 x_msg_count => x_msg_count,
3309 x_msg_data => x_msg_data,
3310 p_chr_id => p_del_pym_tbl(i).chr_id);
3311
3312 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3313 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3314 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3315 RAISE OKL_API.G_EXCEPTION_ERROR;
3316 END IF;
3317
3318 --Call End Activity
3319 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3320 x_msg_data => x_msg_data);
3321
3322
3323 EXCEPTION
3324 when OKL_API.G_EXCEPTION_ERROR then
3325 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3326 p_api_name => l_api_name,
3327 p_pkg_name => G_PKG_NAME,
3328 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3329 x_msg_count => x_msg_count,
3330 x_msg_data => x_msg_data,
3331 p_api_type => G_API_TYPE);
3332
3333 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
3334 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3335 p_api_name => l_api_name,
3336 p_pkg_name => G_PKG_NAME,
3337 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3338 x_msg_count => x_msg_count,
3339 x_msg_data => x_msg_data,
3340 p_api_type => G_API_TYPE);
3341
3342 when OTHERS then
3343 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3344 p_api_name => l_api_name,
3345 p_pkg_name => G_PKG_NAME,
3346 p_exc_name => 'OTHERS',
3347 x_msg_count => x_msg_count,
3348 x_msg_data => x_msg_data,
3349 p_api_type => G_API_TYPE);
3350
3351
3352 END delete_payment;
3353
3354 FUNCTION get_term(p_id IN NUMBER) RETURN NUMBER IS
3355 CURSOR contract_csr(p_contract_id NUMBER) IS
3356 SELECT TERM_DURATION
3357 FROM OKL_K_HEADERS
3358 WHERE ID = p_contract_id;
3359 begin
3360 for r IN contract_csr(p_id)
3361 LOOP
3362 return (r.TERM_DURATION);
3363 END LOOP;
3364 end;
3365
3366 PROCEDURE variable_interest_schedule(
3367 p_api_version IN NUMBER,
3368 p_init_msg_list IN VARCHAR2,
3369 x_return_status OUT NOCOPY VARCHAR2,
3370 x_msg_count OUT NOCOPY NUMBER,
3371 x_msg_data OUT NOCOPY VARCHAR2,
3372 p_chr_id IN NUMBER,
3373 x_rulv_tbl OUT NOCOPY rulv_tbl_type
3374 ) IS
3375 i NUMBER := 0;
3376
3377 l_rulv_rec OKL_RULE_PUB.rulv_rec_type;
3378 lx_rulv_rec OKL_RULE_PUB.rulv_rec_type;
3379 -- x_rulv_tbl OKL_RULE_PUB.RULV_TBL_TYPE;
3380
3381 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3382 l_api_name CONSTANT VARCHAR2(30) :='VARIABLE_INTEREST_SCHEDULE';
3383 l_api_version CONSTANT NUMBER := 1.0;
3384
3385 l_payment_id NUMBER;
3386 l_pym_hdr_rec PYM_HDR_REC_TYPE;
3387 l_pym_tbl PYM_TBL_TYPE;
3388
3389 l_term okl_k_headers.term_duration%TYPE;
3390
3391 l_pym_level VARCHAR2(30) := 'HEADER';
3392 l_slh_id OKC_RULES_V.ID%TYPE := OKL_API.G_MISS_NUM;
3393 l_rgp_id OKC_RULE_GROUPS_V.ID%TYPE := OKL_API.G_MISS_NUM;
3394 l_cle_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
3395
3396 x_primary_sty_id okl_strm_type_b.ID%TYPE;
3397
3398 l_deal_type OKL_K_HEADERS.deal_type%TYPE;
3399 l_interest_calculation_basis VARCHAR2(30);
3400 l_revenue_recognition_method VARCHAR2(30);
3401 l_data_found BOOLEAN;
3402 l_pdt_parameter_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
3403
3404 -- 4895333
3405 CURSOR get_sts_code_csr(p_id NUMBER) IS
3406 SELECT sts_code
3407 FROM OKC_K_HEADERS_B
3408 WHERE ID = p_id;
3409 l_sts_code okc_k_headers_b.sts_code%type;
3410
3411 CURSOR var_int_sched_counter_csr(p_id NUMBER, p_stream_id NUMBER) IS
3412 SELECT COUNT(1) counter
3413 FROM OKC_RULES_B
3414 WHERE DNZ_CHR_ID = p_id
3415 AND rule_information_category = 'LASLH'
3416 AND object1_id1 = to_char(p_stream_id);
3417
3418 l_var_int_sched_counter NUMBER;
3419
3420 begin
3421 IF (G_DEBUG_ENABLED = 'Y') THEN
3422 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
3423 END IF;
3424
3425 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3426 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'In variable_interest_schedule... p_chr_id=' || p_chr_id);
3427 END IF;
3428 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3429 x_return_status := OKL_API.START_ACTIVITY(
3430 p_api_name => l_api_name,
3431 p_pkg_name => G_PKG_NAME,
3432 p_init_msg_list => p_init_msg_list,
3433 l_api_version => p_api_version,
3434 p_api_version => p_api_version,
3435 p_api_type => G_API_TYPE,
3436 x_return_status => x_return_status);
3437
3438 -- check if activity started successfully
3439 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3440 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3441 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3442 raise OKL_API.G_EXCEPTION_ERROR;
3443 END IF;
3444
3445
3446 /*
3447 OKL_K_RATE_PARAMS_PVT.get_product(
3448 p_api_version => p_api_version,
3449 p_init_msg_list => p_init_msg_list,
3450 x_return_status => x_return_status,
3451 x_msg_count => x_msg_count,
3452 x_msg_data => x_msg_data,
3453 p_khr_id => p_chr_id,
3454 x_pdt_parameter_rec => l_pdt_parameter_rec);
3455
3456 l_interest_calculation_basis :=l_pdt_parameter_rec.interest_calculation_basis;
3457 l_revenue_recognition_method :=l_pdt_parameter_rec.revenue_recognition_method;
3458 l_deal_type := l_pdt_parameter_rec.deal_type;
3459
3460 l_term := get_term(p_chr_id);
3461
3462 okl_debug_pub.logmessage('p_chr_id=' || p_chr_id);
3463 okl_debug_pub.logmessage('l_int_cal_basis=' || l_interest_calculation_basis);
3464 okl_debug_pub.logmessage('l_rev_rec_method=' || l_revenue_recognition_method);
3465 okl_debug_pub.logmessage('l_deal_type=' || l_deal_type);
3466 IF ( l_deal_type = 'LOAN' AND
3467 l_interest_calculation_basis IN ('FLOAT', 'CATCHUP/CLEANUP') ) OR
3468 ( l_deal_type = 'LOAN-REVOLVING' AND
3469 l_interest_calculation_basis = 'FLOAT' ) THEN
3470 */
3471
3472 -- 4722839
3473 l_term := get_term(p_chr_id);
3474
3475 OKL_STREAMS_UTIL.get_primary_stream_type(
3476 p_khr_id => p_chr_id,
3477 p_primary_sty_purpose => 'VARIABLE_INTEREST_SCHEDULE',
3478 x_return_status => l_return_status,
3479 x_primary_sty_id => l_payment_id
3480 );
3481
3482 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3483 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3484 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3485 raise OKL_API.G_EXCEPTION_ERROR;
3486 END IF;
3487
3488 -- 4895333
3489 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3490 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_payment_id=' || l_payment_id);
3491 END IF;
3492 FOR r IN get_sts_code_csr(p_chr_id)
3493 LOOP
3494 l_sts_code := r.sts_code;
3495 END LOOP;
3496 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3497 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_sts_code=' || l_sts_code);
3498
3499 END IF;
3500 FOR r_var_int_sched_counter_csr IN
3501 var_int_sched_counter_csr(p_chr_id, l_payment_id)
3502 LOOP
3503 l_var_int_sched_counter := r_var_int_sched_counter_csr.counter;
3504 END LOOP;
3505
3506 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3507 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_var_int_sched_counter=' || l_var_int_sched_counter);
3508 END IF;
3509 -- 4895333
3510 IF (l_var_int_sched_counter < 1) AND (l_sts_code <> 'BOOKED') THEN
3511 get_payment(
3512 p_api_version => p_api_version,
3513 p_init_msg_list => p_init_msg_list,
3514 x_return_status => x_return_status,
3515 x_msg_count => x_msg_count,
3516 x_msg_data => x_msg_data,
3517 p_chr_id => p_chr_id,
3518 p_service_fee_id => null,
3519 p_asset_id => null,
3520 p_payment_id => l_payment_id,
3521 x_pym_level => l_pym_level,
3522 x_slh_id => l_slh_id,
3523 x_rgp_id => l_rgp_id,
3524 x_cle_id => l_cle_id);
3525
3526
3527 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3528 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3529 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3530 raise OKL_API.G_EXCEPTION_ERROR;
3531 END IF;
3532
3533 if(l_slh_id is not null and l_slh_id <> OKL_API.G_MISS_NUM) then
3534 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3535 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_slh_id null... cant create...');
3536 END IF;
3537 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3538 x_msg_data => x_msg_data);
3539 return;
3540 end if;
3541
3542 l_pym_hdr_rec.STRUCTURE := 0;
3543 l_pym_hdr_rec.STRUCTURE_NAME := NULL;
3544 l_pym_hdr_rec.FREQUENCY := 'M';
3545 l_pym_hdr_rec.FREQUENCY_NAME := NULL;
3546 l_pym_hdr_rec.ARREARS := 'Y';
3547 l_pym_hdr_rec.ARREARS_NAME := NULL;
3548
3549 l_pym_tbl(1).RULE_ID := NULL;
3550 l_pym_tbl(1).STUB_DAYS := NULL;
3551 l_pym_tbl(1).STUB_AMOUNT := NULL;
3552 --l_pym_tbl(1).PERIOD := 1;
3553 l_pym_tbl(1).PERIOD := l_term;
3554 -- 4722839
3555 l_pym_tbl(1).AMOUNT := 0;
3556 l_pym_tbl(1).SORT_DATE := NULL;
3557 l_pym_tbl(1).UPDATE_TYPE := 'CREATE';
3558
3559
3560 process_payment(
3561 p_api_version => p_api_version,
3562 p_init_msg_list => p_init_msg_list,
3563 x_return_status => x_return_status,
3564 x_msg_count => x_msg_count,
3565 x_msg_data => x_msg_data,
3566 p_chr_id => p_chr_id,
3567 p_service_fee_id => null,
3568 p_asset_id => null,
3569 p_payment_id => l_payment_id,
3570 p_pym_hdr_rec => l_pym_hdr_rec,
3571 p_pym_tbl => l_pym_tbl,
3572 p_update_type => 'VIR_PAYMENT',
3573 x_rulv_tbl => x_rulv_tbl);
3574
3575 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3576 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3577 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3578 raise OKL_API.G_EXCEPTION_ERROR;
3579 END IF;
3580
3581 END IF; -- if counter < 1
3582 /* END IF; */
3583
3584 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3585 x_msg_data => x_msg_data);
3586
3587 EXCEPTION
3588 when OKL_API.G_EXCEPTION_ERROR then
3589 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3590 p_api_name => l_api_name,
3591 p_pkg_name => G_PKG_NAME,
3592 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3593 x_msg_count => x_msg_count,
3594 x_msg_data => x_msg_data,
3595 p_api_type => G_API_TYPE);
3596
3597 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
3598 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3599 p_api_name => l_api_name,
3600 p_pkg_name => G_PKG_NAME,
3601 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3602 x_msg_count => x_msg_count,
3603 x_msg_data => x_msg_data,
3604 p_api_type => G_API_TYPE);
3605
3606 when OTHERS then
3607 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3608 p_api_name => l_api_name,
3609 p_pkg_name => G_PKG_NAME,
3610 p_exc_name => 'OTHERS',
3611 x_msg_count => x_msg_count,
3612 x_msg_data => x_msg_data,
3613 p_api_type => G_API_TYPE);
3614
3615
3616 END variable_interest_schedule;
3617
3618 --Bug# 6438785
3619 -- Update the start dates for payments when the Contract start date
3620 -- or Line start date is changed.
3621 PROCEDURE update_pymt_start_date(
3622 p_api_version IN NUMBER,
3623 p_init_msg_list IN VARCHAR2,
3624 x_return_status OUT NOCOPY VARCHAR2,
3625 x_msg_count OUT NOCOPY NUMBER,
3626 x_msg_data OUT NOCOPY VARCHAR2,
3627 p_chr_id IN NUMBER,
3628 p_cle_id IN NUMBER DEFAULT NULL) IS
3629
3630 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3631 l_api_name CONSTANT VARCHAR2(30) :='UPDATE_PYMT_START_DATE';
3632 l_api_version CONSTANT NUMBER := 1.0;
3633
3634 CURSOR c_all_pymts_csr(p_chr_id NUMBER) IS
3635 SELECT rgp.id rgp_lalevl_id,
3636 slh.id rul_laslh_id,
3637 rgp.cle_id
3638 FROM okc_rule_groups_b rgp,
3639 okc_rules_b slh,
3640 okc_k_lines_b cle,
3641 okc_statuses_b sts
3642 WHERE rgp.dnz_chr_id = p_chr_id
3643 AND rgp.rgd_code = 'LALEVL'
3644 AND slh.rgp_id = rgp.id
3645 AND slh.dnz_chr_id = rgp.dnz_chr_id
3646 AND slh.rule_information_category = 'LASLH'
3647 AND cle.id (+) = rgp.cle_id
3648 AND cle.sts_code = sts.code (+)
3649 AND sts.ste_code (+) NOT IN ('EXPIRED','TERMINATED','CANCELLED');
3650
3651 CURSOR c_line_pymts_csr(p_cle_id NUMBER,
3652 p_chr_id NUMBER) IS
3653 SELECT rgp.id rgp_lalevl_id,
3654 slh.id rul_laslh_id,
3655 rgp.cle_id
3656 FROM okc_rule_groups_b rgp,
3657 okc_rules_b slh,
3658 okc_k_lines_b cle,
3659 okc_statuses_b sts
3660 WHERE rgp.dnz_chr_id = p_chr_id
3661 AND (rgp.cle_id = p_cle_id OR
3662 rgp.cle_id IN (SELECT cle_sub.id
3663 FROM okc_k_lines_b cle_sub
3664 WHERE cle_sub.cle_id = p_cle_id
3665 AND cle_sub.dnz_chr_id = p_chr_id))
3666 AND rgp.rgd_code = 'LALEVL'
3667 AND slh.rgp_id = rgp.id
3668 AND slh.dnz_chr_id = rgp.dnz_chr_id
3669 AND slh.rule_information_category = 'LASLH'
3670 AND cle.id = rgp.cle_id
3671 AND cle.sts_code = sts.code
3672 AND sts.ste_code NOT IN ('EXPIRED','TERMINATED','CANCELLED');
3673
3674 begin
3675 IF (G_DEBUG_ENABLED = 'Y') THEN
3676 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
3677 END IF;
3678
3679 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3680 x_return_status := OKL_API.START_ACTIVITY(
3681 p_api_name => l_api_name,
3682 p_pkg_name => G_PKG_NAME,
3683 p_init_msg_list => p_init_msg_list,
3684 l_api_version => p_api_version,
3685 p_api_version => p_api_version,
3686 p_api_type => G_API_TYPE,
3687 x_return_status => x_return_status);
3688
3689 -- check if activity started successfully
3690 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3691 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3692 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3693 raise OKL_API.G_EXCEPTION_ERROR;
3694 END IF;
3695
3696 IF p_cle_id IS NOT NULL THEN
3697
3698 FOR r_line_pymts_rec IN c_line_pymts_csr(p_chr_id => p_chr_id,
3699 p_cle_id => p_cle_id) LOOP
3700
3701 calculate_details(
3702 p_api_version => p_api_version,
3703 p_init_msg_list => p_init_msg_list,
3704 x_return_status => x_return_status,
3705 x_msg_count => x_msg_count,
3706 x_msg_data => x_msg_data,
3707 p_chr_id => p_chr_id,
3708 p_rgp_id => r_line_pymts_rec.rgp_lalevl_id,
3709 p_slh_id => r_line_pymts_rec.rul_laslh_id,
3710 structure => NULL,
3711 frequency => NULL,
3712 arrears => NULL,
3713 p_validate_date_yn => 'N');
3714
3715 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3716 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3717 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3718 RAISE OKL_API.G_EXCEPTION_ERROR;
3719 END IF;
3720
3721 END LOOP;
3722
3723 ELSE
3724
3725 FOR r_all_pymts_rec IN c_all_pymts_csr(p_chr_id => p_chr_id) LOOP
3726
3727 calculate_details(
3728 p_api_version => p_api_version,
3729 p_init_msg_list => p_init_msg_list,
3730 x_return_status => x_return_status,
3731 x_msg_count => x_msg_count,
3732 x_msg_data => x_msg_data,
3733 p_chr_id => p_chr_id,
3734 p_rgp_id => r_all_pymts_rec.rgp_lalevl_id,
3735 p_slh_id => r_all_pymts_rec.rul_laslh_id,
3736 structure => NULL,
3737 frequency => NULL,
3738 arrears => NULL,
3739 p_validate_date_yn => 'N');
3740
3741 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3742 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3743 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3744 RAISE OKL_API.G_EXCEPTION_ERROR;
3745 END IF;
3746
3747 END LOOP;
3748 END IF;
3749
3750 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3751 x_msg_data => x_msg_data);
3752
3753 EXCEPTION
3754 when OKL_API.G_EXCEPTION_ERROR then
3755 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3756 p_api_name => l_api_name,
3757 p_pkg_name => G_PKG_NAME,
3758 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3759 x_msg_count => x_msg_count,
3760 x_msg_data => x_msg_data,
3761 p_api_type => G_API_TYPE);
3762
3763 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
3764 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3765 p_api_name => l_api_name,
3766 p_pkg_name => G_PKG_NAME,
3767 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3768 x_msg_count => x_msg_count,
3769 x_msg_data => x_msg_data,
3770 p_api_type => G_API_TYPE);
3771
3772 when OTHERS then
3773 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3774 p_api_name => l_api_name,
3775 p_pkg_name => G_PKG_NAME,
3776 p_exc_name => 'OTHERS',
3777 x_msg_count => x_msg_count,
3778 x_msg_data => x_msg_data,
3779 p_api_type => G_API_TYPE);
3780
3781
3782 END update_pymt_start_date;
3783
3784 END OKL_LA_PAYMENTS_PVT;