[Home] [Help]
PACKAGE BODY: APPS.OKL_LA_PAYMENTS_PVT
Source
1 PACKAGE BODY OKL_LA_PAYMENTS_PVT as
2 /* $Header: OKLRPYTB.pls 120.42.12020000.2 2012/08/07 12:21:23 bkatraga 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 --Bug# 13973475
593 FUNCTION get_sll_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 COUNT(1)
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_INFORMATION_CATEGORY = 'LASLL';
606
607 BEGIN
608 open SLL_CSR(p_rgp_id, p_slh_id, p_chr_id);
609 fetch SLL_CSR into l_count;
610 close SLL_CSR;
611 if(l_count is null) then
612 l_count := 0;
613 end if;
614 return l_count;
615 END get_sll_count;
616 --Bug# 13973475
617
618
619 FUNCTION verify_sec_deposit_count(
620 p_rgp_id IN NUMBER,
621 p_slh_id IN VARCHAR2,
622 p_chr_id IN NUMBER)
623 RETURN NUMBER IS
624 l_count NUMBER := null;
625 cursor SLL_CSR(P_RGP_ID IN NUMBER, P_SLH_ID IN VARCHAR2, P_CHR_ID IN NUMBER) is
626 SELECT SUM(TO_NUMBER(nvl(RULE_INFORMATION3,0)))
627 FROM OKL_LA_PAYMENTS_UV
628 WHERE RGP_ID = P_RGP_ID
629 AND OBJECT2_ID1 = P_SLH_ID
630 AND DNZ_CHR_ID = P_CHR_ID
631 AND RULE_INFORMATION3 is not null
632 AND RULE_INFORMATION_CATEGORY = 'LASLL';
633
634 BEGIN
635 open SLL_CSR(p_rgp_id, p_slh_id, p_chr_id);
636 fetch SLL_CSR into l_count;
637 close SLL_CSR;
638 if(l_count is null) then
639 l_count := 0;
640 end if;
641 return l_count;
642 END verify_sec_deposit_count;
643
644
645
646 FUNCTION get_payment_type(p_slh_id IN VARCHAR2)
647 RETURN VARCHAR2 IS
648 l_payment_code varchar2(150) := null;
649 l_slh_id number := null;
650 cursor PAYMENT_TYPE_CSR(P_SLH_ID IN VARCHAR2) is
651 -- SELECT STRM.CODE
652 SELECT STRM.STREAM_TYPE_PURPOSE
653 FROM OKC_RULES_B RUL,
654 OKL_STRM_TYPE_B STRM
655 WHERE RUL.ID = TO_NUMBER(P_SLH_ID)
656 AND STRM.ID = TO_NUMBER(RUL.OBJECT1_ID1);
657
658 BEGIN
659 if(p_slh_id is null or p_slh_id = '') then return ''; end if;
660 open PAYMENT_TYPE_CSR(p_slh_id);
661 fetch PAYMENT_TYPE_CSR into l_payment_code;
662 close PAYMENT_TYPE_CSR;
663 return l_payment_code;
664 END get_payment_type;
665
666
667 -- next one function property taxes
668
669 FUNCTION is_prop_tax_payment(p_stream_id IN VARCHAR2)
670 RETURN BOOLEAN IS
671 l_payment_code varchar2(150) := null;
672 l_slh_id number := null;
673 cursor PAYMENT_TYPE_CSR(P_ID IN VARCHAR2) is
674 SELECT STRM.STREAM_TYPE_PURPOSE
675 FROM OKL_STRMTYP_SOURCE_V STRM
676 WHERE TO_CHAR(STRM.ID1) = P_ID;
677
678 BEGIN
679 if(p_stream_id is null or p_stream_id = '') then return false; end if;
680 open PAYMENT_TYPE_CSR(p_stream_id);
681 fetch PAYMENT_TYPE_CSR into l_payment_code;
682 close PAYMENT_TYPE_CSR;
683 if(l_payment_code is not null and l_payment_code = 'ESTIMATED_PROPERTY_TAX') then
684 return true;
685 else
686 return false;
687 end if;
688 END is_prop_tax_payment;
689
690 FUNCTION is_ppd_payment(p_stream_id IN VARCHAR2)
691 RETURN BOOLEAN IS
692 l_flag varchar2(1) := null;
693 l_slh_id number := null;
694 cursor PAYMENT_TYPE_CSR(P_ID IN VARCHAR2) is
695 SELECT 'Y'
696 FROM OKL_STRMTYP_SOURCE_V STRM,
697 OKC_RULES_B RL
698 WHERE RL.ID = P_ID
699 AND TO_CHAR(STRM.ID1) = RL.OBJECT1_ID1
700 AND STRM.STREAM_TYPE_PURPOSE = 'UNSCHEDULED_PRINCIPAL_PAYMENT';
701
702 BEGIN
703 if(p_stream_id is null or p_stream_id = '') then return false; end if;
704 open PAYMENT_TYPE_CSR(p_stream_id);
705 fetch PAYMENT_TYPE_CSR into l_flag;
706 close PAYMENT_TYPE_CSR;
707 if(l_flag is not null and l_flag = 'Y') then
708 return true;
709 else
710 return false;
711 end if;
712 END is_ppd_payment;
713
714
715 ------------------------------------------------------------------------------
716 -- FUNCTION is_rollover_fee_payment
717 --
718 -- Function to check if the fee type of the fee top line is rollover.
719 --
720 -- Calls:
721 -- Created By: Manu 13-Sep-2004
722 -- Called By:
723 ------------------------------------------------------------------------------
724
725 FUNCTION is_rollover_fee_payment(p_service_or_fee_id IN
726 OKC_K_LINES_B.ID%TYPE) RETURN BOOLEAN IS
727
728 l_fee_type VARCHAR2(150) := NULL;
729 CURSOR fee_type_csr(P_ID IN OKC_K_LINES_B.ID%TYPE) IS
730 SELECT FEE_TYPE FROM okc_k_lines_b CLEB, okl_k_lines KLE
731 WHERE KLE.ID = P_ID
732 AND KLE.ID = CLEB.ID;
733
734 BEGIN
735 IF (p_service_or_fee_id IS NULL OR p_service_or_fee_id = '') THEN
736 RETURN FALSE;
737 END IF;
738 OPEN fee_type_csr(p_service_or_fee_id);
739 FETCH fee_type_csr into l_fee_type;
740 CLOSE fee_type_csr;
741 IF (l_fee_type IS NOT NULL AND l_fee_type = 'ROLLOVER') THEN
742 RETURN TRUE;
743 ELSE
744 RETURN FALSE;
745 END IF;
746 END is_rollover_fee_payment;
747
748 -- start: cklee: 06/22/05 -- okl.h
749 ------------------------------------------------------------------------------
750 -- FUNCTION is_eligible_fee_payment
751 --
752 -- Function to check if the fee type of the fee top line is eligible
753 --
754 -- Calls:
755 -- Created By: cklee
756 -- Called By:
757 ------------------------------------------------------------------------------
758
759 FUNCTION is_eligible_fee_payment(p_service_or_fee_id IN
760 OKC_K_LINES_B.ID%TYPE) RETURN BOOLEAN IS
761
762 l_fee_type VARCHAR2(150) := NULL;
763 CURSOR fee_type_csr(P_ID IN OKC_K_LINES_B.ID%TYPE) IS
764 SELECT FEE_TYPE FROM okl_k_lines KLE
765 WHERE KLE.ID = P_ID;
766
767 BEGIN
768 IF (p_service_or_fee_id IS NULL OR p_service_or_fee_id = '') THEN
769 RETURN FALSE;
770 END IF;
771 OPEN fee_type_csr(p_service_or_fee_id);
772 FETCH fee_type_csr into l_fee_type;
773 CLOSE fee_type_csr;
774 IF (l_fee_type IS NOT NULL AND l_fee_type IN ('MISCELLANEOUS','PASSTHROUGH','SECDEPOSIT','INCOME','FINANCED','ROLLOVER')) THEN
775 RETURN TRUE;
776 ELSE
777 RETURN FALSE;
778 END IF;
779 END is_eligible_fee_payment;
780
781 -- end: cklee: 6/22/05 -- okl.h
782
783
784 FUNCTION get_start_date(
785 p_chr_id IN NUMBER := NULL,
786 p_cle_id IN NUMBER := NULL,
787 p_rgp_id IN NUMBER := NULL )
788 RETURN DATE IS
789
790 l_chr_id OKC_K_HEADERS_B.ID%TYPE := p_chr_id;
791 l_cle_id OKC_K_LINES_B.ID%TYPE := p_cle_id;
792 l_rgp_id OKC_RULE_GROUPS_B.ID%TYPE := p_rgp_id;
793
794 l_start_date date;
795
796 cursor KHR_CSR(P_ID IN NUMBER) is
797 SELECT START_DATE
798 FROM
799 OKC_K_HEADERS_B
800 WHERE
801 ID = P_ID;
802
803 cursor CLE_CSR(P_ID IN NUMBER) is
804 SELECT START_DATE
805 FROM
806 OKC_K_LINES_B
807 WHERE
808 ID = P_ID;
809
810 cursor RGP_CSR(P_ID IN NUMBER) is
811 SELECT DNZ_CHR_ID, CLE_ID
812 FROM
813 OKC_RULE_GROUPS_B
814 WHERE
815 ID = P_ID;
816
817 BEGIN
818
819 -- if(l_rgp_id is not null and l_rgp_id <> OKL_API.G_MISS_NUM) then
820 if(l_rgp_id is not null) then
821 open RGP_CSR(l_rgp_id);
822 fetch RGP_CSR into l_chr_id, l_cle_id;
823 close RGP_CSR;
824 end if;
825
826 if(l_cle_id is not null) then
827 open CLE_CSR(l_cle_id);
828 fetch CLE_CSR into l_start_date;
829 close CLE_CSR;
830 if(l_start_date is not null) then
831 return l_start_date;
832 end if;
833 end if;
834 if(l_chr_id is null) then
835 return null;
836 end if;
837 open KHR_CSR(l_chr_id);
838 fetch KHR_CSR into l_start_date;
839 close KHR_CSR;
840 return l_start_date;
841
842 END get_start_date;
843
844 -- bug
845 -- gboomina Added for Bug 6152538
846 -- This function is used to find the end date of a line for
847 -- which we create payment
848 -- For Contract level payments, this will return contract end date and
849 -- For Line level payments, this will return respective line(Asset, Fee or Service)
850 -- end date for which we create payment.
851 FUNCTION get_line_end_date(p_rgp_id IN NUMBER )
852 RETURN DATE
853 IS
854 l_chr_id OKC_K_HEADERS_B.ID%TYPE := NULL;
855 l_cle_id OKC_K_LINES_B.ID%TYPE := NULL;
856 l_rgp_id OKC_RULE_GROUPS_B.ID%TYPE := p_rgp_id;
857
858 l_line_end_date date;
859
860 cursor khr_csr(p_id in number) is
861 select end_date
862 from okc_k_headers_b
863 where id = p_id;
864
865 cursor cle_csr(p_id in number) is
866 select end_date
867 from okc_k_lines_b
868 where id = p_id;
869
870 cursor rgp_csr(p_id in number) is
871 select dnz_chr_id, cle_id
872 from okc_rule_groups_b
873 where id = p_id;
874
875 BEGIN
876 if(l_rgp_id is not null) then
877 open rgp_csr(l_rgp_id);
878 fetch rgp_csr into l_chr_id, l_cle_id;
879 close rgp_csr;
880 end if;
881
882 if(l_cle_id is not null) then
883 open cle_csr(l_cle_id);
884 fetch cle_csr into l_line_end_date;
885 close cle_csr;
886 if(l_line_end_date is not null) then
887 return l_line_end_date;
888 end if;
889 end if;
890
891 if(l_chr_id is null) then
892 return null;
893 end if;
894 open khr_csr(l_chr_id);
895 fetch khr_csr into l_line_end_date;
896 close khr_csr;
897 return l_line_end_date;
898
899 END get_line_end_date;
900
901
902 FUNCTION get_end_date(
903 l_start_date IN DATE,
904 p_frequency IN VARCHAR2,
905 p_period IN NUMBER,
906 ---- cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
907 p_start_day IN NUMBER DEFAULT NULL,
908 p_contract_end_date IN DATE DEFAULT NULL --Bug#5441811
909 )
910 RETURN DATE IS
911 l_end_date date;
912 factor number := 0;
913 BEGIN
914 if(p_frequency = 'M') then
915 factor := 1;
916 elsif(p_frequency = 'Q') then
917 factor := 3;
918 elsif(p_frequency = 'S') then
919 factor := 6;
920 elsif(p_frequency = 'A') then
921 factor := 12;
922 end if;
923
924 -- start: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
925 --l_end_date := ADD_MONTHS(l_start_date, (factor * NVL(p_period,0)));
926 -- l_end_date := l_end_date - 1;
927
928 l_end_date := Okl_Lla_Util_Pvt.calculate_end_date(p_start_date => l_start_date,
929 p_months => factor * NVL(p_period,0),
930 p_start_day => p_start_day,
931 p_contract_end_date => p_contract_end_date );--Bug#5441811
932 -- end: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
933
934
935 return l_end_date;
936 EXCEPTION
937 WHEN OTHERS THEN
938 RETURN null;
939 END get_end_date;
940
941
942 FUNCTION get_display_end_date(
943 p_start_date IN VARCHAR2,
944 p_stub_days IN VARCHAR2,
945 p_frequency IN VARCHAR2,
946 p_period IN VARCHAR2,
947 ---- mvasudev,06-02-2005,Bug#4392051
948 p_start_day IN NUMBER,
949 p_contract_end_date IN DATE DEFAULT NULL --Bug#5441811
950 )
951 RETURN VARCHAR2 IS
952 l_end_date date;
953 l_end_date_disp varchar2(40);
954 BEGIN
955 if(p_stub_days is not null and p_stub_days <> OKL_API.G_MISS_CHAR) then -- end date for stub entry.
956 l_end_date := FND_DATE.canonical_to_date(p_start_date) + to_number(p_stub_days);
957 l_end_date := l_end_date - 1;
958 l_end_date_disp := OKL_LLA_UTIL_PVT.get_display_date(nvl(FND_DATE.date_to_canonical(l_end_date),''));
959 else -- end date for level entry.
960 -- cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
961 --l_end_date := get_end_date(FND_DATE.canonical_to_date(p_start_date), p_frequency, TO_NUMBER(NVL(p_period,0)));
962 --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);
963
964 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
965
966 l_end_date_disp := OKL_LLA_UTIL_PVT.get_display_date(NVL(FND_DATE.date_to_canonical(l_end_date),''));
967 end if;
968 return l_end_date_disp;
969 EXCEPTION
970 WHEN OTHERS THEN
971 RETURN '';
972 END get_display_end_date;
973
974 --START: 07-25-2005 cklee/mvasudev -- Fixed Bug#4392051/okl.h 4437938
975 FUNCTION get_start_day(
976 p_rule_id IN NUMBER
977 ,p_dnz_chr_id IN NUMBER
978 ,p_rgp_id IN NUMBER
979 ,p_slh_id IN NUMBER
980 ,p_start_date IN VARCHAR2)
981 RETURN NUMBER
982 IS
983
984 --Modified cursor for bug 6007644
985 --Added FND_DATE.canonical_to_date(RULE_INFORMATION2)+to_number(RULE_INFORMATION7) to return the recurrence date
986 --Also added FND_DATE.canonical_to_date(rule_information2) to covert the rule_information2 to date format
987 CURSOR l_okl_stub_start_csr
988 IS
989 SELECT MAX(FND_DATE.canonical_to_date(RULE_INFORMATION2)+to_number(RULE_INFORMATION7)) start_date
990 FROM OKC_RULES_V
991 WHERE rgp_id = p_rgp_id
992 AND dnz_chr_id = p_dnz_chr_id
993 AND object2_id1 = p_slh_id
994 AND id <> p_rule_id
995 AND rule_information_category = 'LASLL' --| 17-Jan-06 cklee Fixed bug#4956483 |
996 AND FND_DATE.canonical_to_date(rule_information2) < FND_DATE.canonical_to_date(p_start_date)
997 AND rule_information7 IS NOT NULL
998 ORDER BY start_date;
999
1000 CURSOR l_okl_chr_start_csr
1001 IS
1002 SELECT START_DATE
1003 FROM OKC_K_HEADERS_B
1004 WHERE ID = p_dnz_chr_id;
1005
1006 l_start_date DATE;
1007 l_sll_count NUMBER := 0;
1008 BEGIN
1009 OPEN l_okl_sll_count_csr(p_rgp_id,p_dnz_chr_id,p_slh_id);
1010 FETCH l_okl_sll_count_csr INTO l_sll_count;
1011 CLOSE l_okl_sll_count_csr;
1012
1013 IF (l_sll_count > 1 ) THEN
1014
1015 FOR l_okl_stub_start_rec IN l_okl_stub_start_csr
1016 LOOP
1017 l_start_date := l_okl_stub_start_rec.start_date;
1018 END LOOP;
1019
1020 IF l_start_date IS NULL THEN
1021 FOR l_okl_chr_start_rec IN l_okl_chr_start_csr
1022 LOOP
1023 l_start_date := l_okl_chr_start_rec.start_date;
1024 END LOOP;
1025 END IF;
1026
1027 IF l_start_date IS NOT NULL THEN
1028 RETURN (TO_CHAR(l_start_date,'DD'));
1029 END IF;
1030 ELSE
1031 RETURN NULL;
1032 END IF;
1033
1034
1035
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 RETURN ''; --Added for bug 6007644
1039 /*
1040 Commented so that the function would return days, which acts as an input parameter
1041 for OKL_LLA_UTIL_PVT.calculate_end_date. If the function returns NULL the
1042 calculate_end_date logic flows into calculating the contract end date logic.
1043 */
1044 END get_start_day;
1045 --END: 07-25-2005 cklee/mvasudev -- Fixed Bug#4392051/okl.h 4437938
1046
1047 FUNCTION get_order_sequence(
1048 p_sequence IN VARCHAR2)
1049 RETURN NUMBER IS
1050 l_sequence number;
1051 BEGIN
1052 if(p_sequence is null) then
1053 return 0;
1054 end if;
1055 l_sequence := to_number(p_sequence);
1056 return l_sequence;
1057 EXCEPTION
1058 WHEN OTHERS THEN
1059 RETURN 0;
1060 END get_order_sequence;
1061
1062
1063 --Added p_update_type by bkatraga for bug 13478101
1064 PROCEDURE calculate_details(
1065 p_api_version IN NUMBER,
1066 p_init_msg_list IN VARCHAR2,
1067 x_return_status OUT NOCOPY VARCHAR2,
1068 x_msg_count OUT NOCOPY NUMBER,
1069 x_msg_data OUT NOCOPY VARCHAR2,
1070 p_chr_id IN NUMBER,
1071 p_rgp_id IN NUMBER,
1072 p_slh_id IN VARCHAR2,
1073 structure IN VARCHAR2,
1074 frequency IN VARCHAR2,
1075 arrears IN VARCHAR2,
1076 --Bug# 6438785
1077 p_validate_date_yn IN VARCHAR2 DEFAULT 'Y',
1078 p_update_type IN VARCHAR2 DEFAULT NULL) IS
1079 i NUMBER := 0;
1080 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
1081 l_start_date date := null;
1082 l_prev_start_date date := null;
1083 l_end_date date := null;
1084 l_k_start_date date := null;
1085 l_chr_id number := null;
1086 factor number := null;
1087
1088 CURSOR sll_instances(P_RGP_ID IN NUMBER,
1089 P_CHR_ID IN NUMBER,
1090 P_SLH_ID IN VARCHAR2
1091 ) IS
1092 SELECT
1093 SLL.*,
1094 FND_DATE.canonical_to_date(nvl(SLL.RULE_INFORMATION2,null)) START_DATE,
1095 OKL_LA_PAYMENTS_PVT.get_order_sequence(SLL.RULE_INFORMATION1) SEQUENCE
1096 FROM OKC_RULES_B SLL
1097 WHERE
1098 SLL.DNZ_CHR_ID = P_CHR_ID
1099 AND SLL.RGP_ID = P_RGP_ID
1100 AND SLL.RULE_INFORMATION_CATEGORY = 'LASLL'
1101 AND SLL.OBJECT2_ID1 = P_SLH_ID
1102 ORDER BY START_DATE, SEQUENCE;
1103
1104 l_rulv_tbl sll_instances%ROWTYPE;
1105
1106 l_rulv_rec OKL_RULE_PUB.rulv_rec_type;
1107 lx_rulv_rec OKL_RULE_PUB.rulv_rec_type;
1108
1109 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1110 l_api_name CONSTANT VARCHAR2(30) := 'CALCULATE_DETAILS';
1111 l_api_version CONSTANT NUMBER := 1.0;
1112
1113 -- cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1114 l_start_day NUMBER;
1115 l_sll_count NUMBER := 0;
1116
1117 l_contract_end_date DATE; --Bug#5441811
1118 l_line_end_date DATE; -- Bug 6152538
1119
1120 --Added by bkatraga for bug 13478101
1121 l_structure OKC_RULES_B.rule_information5%TYPE := structure;
1122 l_frequency OKC_RULES_B.object1_id1%TYPE := frequency;
1123 l_arrears OKC_RULES_B.rule_information10%TYPE := 'Y';
1124 l_stub_rulv_rec OKL_RULE_PUB.rulv_rec_type;
1125 lx_stub_rulv_rec OKL_RULE_PUB.rulv_rec_type;
1126 --end bkatraga
1127
1128 begin
1129
1130 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1131 -- call START_ACTIVITY to create savepoint, check compatibility
1132 -- and initialize message list
1133 x_return_status := OKL_API.START_ACTIVITY(
1134 p_api_name => l_api_name,
1135 p_pkg_name => G_PKG_NAME,
1136 p_init_msg_list => p_init_msg_list,
1137 l_api_version => l_api_version,
1138 p_api_version => p_api_version,
1139 p_api_type => G_API_TYPE,
1140 x_return_status => x_return_status);
1141
1142 -- check if activity started successfully
1143 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1144 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1145 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
1146 raise OKL_API.G_EXCEPTION_ERROR;
1147 END IF;
1148 l_end_date := get_start_date(p_rgp_id => p_rgp_id);
1149
1150 -- START: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1151 OPEN l_okl_sll_count_csr(p_rgp_id,p_chr_id,p_slh_id);
1152 FETCH l_okl_sll_count_csr INTO l_sll_count;
1153 CLOSE l_okl_sll_count_csr;
1154
1155 IF (l_sll_count > 1 ) THEN
1156 l_start_day := TO_CHAR(l_end_date,'DD');
1157 END IF;
1158 -- END: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1159
1160 l_end_date := l_end_date - 1;
1161
1162 --For Bug#5441811 selecting the contract end date for calculating
1163 -- end dates in payment structure.
1164 FOR i IN ( SELECT end_date FROM okc_k_headers_b WHERE id = P_CHR_ID)
1165 LOOP
1166 l_contract_end_date := i.end_date;
1167 END LOOP;
1168 --Bug#5441811
1169
1170 FOR rule_rec in sll_instances(P_RGP_ID, P_CHR_ID, P_SLH_ID) loop
1171
1172 if( rule_rec.rule_information7 is not null and -- stub days
1173 rule_rec.rule_information7 <> OKL_API.G_MISS_CHAR ) then
1174 l_start_date := l_end_date + 1;
1175 l_end_date := l_start_date + to_number(rule_rec.rule_information7);
1176 l_end_date := l_end_date - 1;
1177 l_rulv_rec.rule_information2 := FND_DATE.date_to_canonical(l_start_date);
1178 l_rulv_rec.id := rule_rec.id;
1179
1180 -- START: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1181 IF (l_sll_count > 1 ) THEN
1182 l_start_day := TO_CHAR(l_end_date + 1,'DD');
1183 END IF;
1184 -- END: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1185
1186 -- nikshah Bug 7828786 - Start
1187 -- Added to avoid validating stub end date when this API is called
1188 -- at the time of updating the contract or line start dates
1189 IF p_validate_date_yn = 'Y' THEN
1190 -- Check whether stubs end date exceeds contract/line end date.
1191 l_line_end_date := get_line_end_date(p_rgp_id);
1192 if ( trunc(l_end_date) > trunc(l_line_end_date) ) then
1193 x_return_status := OKL_API.g_ret_sts_error;
1194 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1195 , p_msg_name => 'OKL_PAYMENT_DT_EXCEEDS_LINE_DT');
1196 raise OKL_API.G_EXCEPTION_ERROR;
1197 end if;
1198 END IF;
1199 -- nikshah Bug 7828786 - End
1200
1201 elsif(rule_rec.rule_information3 is not null and -- periods
1202 rule_rec.rule_information3 <> OKL_API.G_MISS_CHAR ) then
1203 l_start_date := l_end_date + 1;
1204 --l_end_date := get_end_date(l_start_date, rule_rec.OBJECT1_ID1, rule_rec.RULE_INFORMATION3);
1205 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
1206 l_rulv_rec.rule_information2 := FND_DATE.date_to_canonical(l_start_date);
1207 l_rulv_rec.id := rule_rec.id;
1208
1209 -- Bug# 6438785
1210 -- Added to avoid validating payment end date when this API is called
1211 -- at the time of updating the contract or line start dates
1212 IF p_validate_date_yn = 'Y' THEN
1213 -- gboomina Bug 6152538 - Start
1214 -- Check whether payments end date exceeds contract/line end date.
1215 l_line_end_date := get_line_end_date(p_rgp_id);
1216 if ( trunc(l_end_date) > trunc(l_line_end_date) ) then
1217 x_return_status := OKL_API.g_ret_sts_error;
1218 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1219 , p_msg_name => 'OKL_PAYMENT_DT_EXCEEDS_LINE_DT');
1220 raise OKL_API.G_EXCEPTION_ERROR;
1221 end if;
1222 -- gboomina Bug 6152538 - End
1223 END IF;
1224 -- Bug# 6438785
1225 end if;
1226
1227 --Added else by bkatraga for bug 13478101
1228 if (frequency is not null and frequency <> OKL_API.G_MISS_CHAR) then
1229 l_rulv_rec.jtot_object1_code := 'OKL_TUOM';
1230 l_rulv_rec.object1_id1 := frequency;
1231 else
1232 l_frequency := rule_rec.object1_id1;
1233 end if;
1234
1235 if (arrears is not null and arrears <> OKL_API.G_MISS_CHAR) then
1236 l_rulv_rec.rule_information10 := arrears;
1237 --Bug# 9966316: Update arrears to NULL when value is NULL
1238 elsif (arrears is null) then
1239 l_rulv_rec.rule_information10 := arrears;
1240 end if;
1241
1242 --Added else by bkatraga for bug 13478101
1243 if (structure is not null and structure <> OKL_API.G_MISS_CHAR) then
1244 l_rulv_rec.rule_information5 := structure;
1245 else
1246 l_structure := rule_rec.rule_information5;
1247 end if;
1248
1249 l_rulv_rec.rule_information1 := null;
1250
1251 OKL_RULE_PUB.update_rule(
1252 p_api_version => p_api_version,
1253 p_init_msg_list => p_init_msg_list,
1254 x_return_status => x_return_status,
1255 x_msg_count => x_msg_count,
1256 x_msg_data => x_msg_data,
1257 p_rulv_rec => l_rulv_rec,
1258 x_rulv_rec => lx_rulv_rec);
1259
1260
1261 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1262 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1263 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1264 RAISE OKL_API.G_EXCEPTION_ERROR;
1265 END IF;
1266
1267
1268 end loop;
1269 if(sll_instances%ISOPEN) then
1270 close sll_instances;
1271 end if;
1272 -- START: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1273 IF(l_okl_sll_count_csr%ISOPEN) THEN
1274 CLOSE l_okl_sll_count_csr;
1275 END IF;
1276 -- END: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1277
1278 --Added by bkatraga for bug 13478101
1279 l_payment_code := get_payment_type(to_char(p_slh_id));
1280 IF(l_payment_code = 'VARIABLE_INTEREST_SCHEDULE' AND l_end_date < l_contract_end_date
1281 AND (p_update_type IS NOT NULL AND p_update_type <> 'DELETE')) THEN
1282 l_stub_rulv_rec.id := null;
1283 l_stub_rulv_rec.rule_information2 := FND_DATE.date_to_canonical(l_end_date+1);
1284 l_stub_rulv_rec.rule_information7 := (l_contract_end_date - l_end_date);
1285 l_stub_rulv_rec.rule_information8 := 0;
1286 l_stub_rulv_rec.rule_information3 := null;
1287 l_stub_rulv_rec.rule_information6 := null;
1288
1289 l_stub_rulv_rec.jtot_object1_code := 'OKL_TUOM';
1290 l_stub_rulv_rec.object1_id1 := l_frequency;
1291 l_stub_rulv_rec.rule_information10 := l_arrears;
1292 l_stub_rulv_rec.rule_information5 := l_structure;
1293
1294 l_stub_rulv_rec.jtot_object2_code := 'OKL_STRMHDR';
1295 l_stub_rulv_rec.object2_id1 := p_slh_id;
1296 l_stub_rulv_rec.object2_id2 := '#';
1297 l_stub_rulv_rec.dnz_chr_id := p_chr_id;
1298 l_stub_rulv_rec.rgp_id := p_rgp_id;
1299 l_stub_rulv_rec.sfwt_flag := 'N';
1300 l_stub_rulv_rec.std_template_yn := 'N';
1301 l_stub_rulv_rec.warn_yn := 'N';
1302 l_stub_rulv_rec.rule_information_category := 'LASLL';
1303
1304 OKL_RULE_PUB.create_rule(
1305 p_api_version => p_api_version,
1306 p_init_msg_list => p_init_msg_list,
1307 x_return_status => x_return_status,
1308 x_msg_count => x_msg_count,
1309 x_msg_data => x_msg_data,
1310 p_rulv_rec => l_stub_rulv_rec,
1311 x_rulv_rec => lx_stub_rulv_rec);
1312 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1313 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1314 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1315 RAISE OKL_API.G_EXCEPTION_ERROR;
1316 END IF;
1317 END IF;
1318 --end bkatraga
1319
1320 --Call End Activity
1321 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1322 x_msg_data => x_msg_data);
1323
1324
1325 EXCEPTION
1326 when OKL_API.G_EXCEPTION_ERROR then
1327 -- START: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1328 if(sll_instances%ISOPEN) then
1329 close sll_instances;
1330 end if;
1331 IF(l_okl_sll_count_csr%ISOPEN) THEN
1332 CLOSE l_okl_sll_count_csr;
1333 END IF;
1334 -- END: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1335 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1336 p_api_name => l_api_name,
1337 p_pkg_name => G_PKG_NAME,
1338 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1339 x_msg_count => x_msg_count,
1340 x_msg_data => x_msg_data,
1341 p_api_type => G_API_TYPE);
1342
1343 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1344 -- START: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1345 if(sll_instances%ISOPEN) then
1346 close sll_instances;
1347 end if;
1348 IF(l_okl_sll_count_csr%ISOPEN) THEN
1349 CLOSE l_okl_sll_count_csr;
1350 END IF;
1351 -- END: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1352 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1353 p_api_name => l_api_name,
1354 p_pkg_name => G_PKG_NAME,
1355 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1356 x_msg_count => x_msg_count,
1357 x_msg_data => x_msg_data,
1358 p_api_type => G_API_TYPE);
1359
1360 when OTHERS then
1361 -- START: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1362 if(sll_instances%ISOPEN) then
1363 close sll_instances;
1364 end if;
1365 IF(l_okl_sll_count_csr%ISOPEN) THEN
1366 CLOSE l_okl_sll_count_csr;
1367 END IF;
1368 -- END: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
1369 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1370 p_api_name => l_api_name,
1371 p_pkg_name => G_PKG_NAME,
1372 p_exc_name => 'OTHERS',
1373 x_msg_count => x_msg_count,
1374 x_msg_data => x_msg_data,
1375 p_api_type => G_API_TYPE);
1376
1377
1378 END calculate_details;
1379
1380
1381 FUNCTION is_num(
1382 p_num IN VARCHAR2)
1383 RETURN boolean IS
1384 l_num number;
1385 BEGIN
1386 if(p_num is null or p_num = OKL_API.G_MISS_CHAR ) then
1387 return false;
1388 else
1389 l_num := to_number(p_num);
1390 return true;
1391 end if;
1392 EXCEPTION
1393 WHEN OTHERS THEN
1394 RETURN false;
1395 END is_num;
1396
1397
1398 PROCEDURE validate_payment(
1399 p_api_version IN NUMBER,
1400 p_init_msg_list IN VARCHAR2,
1401 x_return_status OUT NOCOPY VARCHAR2,
1402 x_msg_count OUT NOCOPY NUMBER,
1403 x_msg_data OUT NOCOPY VARCHAR2,
1404 p_rulv_rec IN rulv_rec_type,
1405 p_fee_line_type IN VARCHAR2,
1406 p_payment_type IN VARCHAR2,
1407 p_type IN VARCHAR2
1408 ) IS
1409
1410
1411 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1412 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_PAYMENTS';
1413 l_api_version CONSTANT NUMBER := 1.0;
1414
1415 i NUMBER := 0;
1416 l_rulv_rec rulv_rec_type := p_rulv_rec;
1417 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
1418 l_lty_code OKC_LINE_STYLES_V.LTY_CODE%TYPE := null;
1419
1420 l_days boolean := false;
1421 l_days_amt boolean := false;
1422 l_period boolean := false;
1423 l_period_amt boolean := false;
1424 -- Start fix for bug 7111749
1425 l_structure boolean := false;
1426 -- End fix for bug 7111749
1427 l_message VARCHAR2(1000);
1428
1429 begin
1430
1431 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1432 -- call START_ACTIVITY to create savepoint, check compatibility
1433 -- and initialize message list
1434 x_return_status := OKL_API.START_ACTIVITY(
1435 p_api_name => l_api_name,
1436 p_pkg_name => G_PKG_NAME,
1437 p_init_msg_list => p_init_msg_list,
1438 l_api_version => l_api_version,
1439 p_api_version => p_api_version,
1440 p_api_type => G_API_TYPE,
1441 x_return_status => x_return_status);
1442
1443 -- check if activity started successfully
1444 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1445 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1446 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
1447 raise OKL_API.G_EXCEPTION_ERROR;
1448 END IF;
1449
1450 if( p_rulv_rec.rule_information7 is not null and
1451 p_rulv_rec.rule_information7 <> OKL_API.G_MISS_CHAR) then
1452 l_days := true;
1453
1454 end if;
1455 if( p_rulv_rec.rule_information8 is not null and
1456 p_rulv_rec.rule_information8 <> OKL_API.G_MISS_CHAR) then
1457 l_days_amt := true;
1458 end if;
1459 if( p_rulv_rec.rule_information3 is not null and
1460 p_rulv_rec.rule_information3 <> OKL_API.G_MISS_CHAR) then
1461 l_period := true;
1462 end if;
1463 if( p_rulv_rec.rule_information6 is not null and
1464 p_rulv_rec.rule_information6 <> OKL_API.G_MISS_CHAR) then
1465 l_period_amt := true;
1466 end if;
1467 -- Start fix for bug 7111749
1468 -- Set l_structure TRUE if non-level
1469 if( p_rulv_rec.rule_information5 is not null and
1470 p_rulv_rec.rule_information5 <> OKL_API.G_MISS_CHAR) and
1471 (TRUNC(NVL(p_rulv_rec.rule_information5, '-1')) <> '0') then
1472 l_structure := true;
1473 end if;
1474 -- End fix for bug 7111749
1475
1476 if(p_payment_type = 'VIR_PAYMENT') then
1477 if(l_period and is_num(p_rulv_rec.rule_information3) and to_number(p_rulv_rec.rule_information3) > 0 ) then
1478 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1479 x_msg_data => x_msg_data);
1480 return;
1481 else
1482 x_return_status := OKL_API.g_ret_sts_error;
1483 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1484 , p_msg_name => l_message);
1485 raise OKL_API.G_EXCEPTION_ERROR;
1486 end if;
1487 end if;
1488
1489 if((l_days and l_period) or (l_days_amt and l_period_amt)) then
1490 l_message := 'OKL_LA_PYM_STUB_PERIOD';
1491 elsif((l_days and not l_days_amt) or (not l_days and l_days_amt)) then
1492 l_message := 'OKL_LA_PYM_STUB';
1493 elsif((l_period and not l_period_amt) or (not l_period and l_period_amt)) then
1494 l_message := 'OKL_LA_PYM_PERIOD';
1495 end if;
1496
1497 if(l_message is null and l_period ) then
1498 if( is_num(p_rulv_rec.rule_information3) and to_number(p_rulv_rec.rule_information3) > 0 ) then
1499 if( not is_num(p_rulv_rec.rule_information6) ) then
1500 l_message := 'OKL_LA_PYM_AMOUNT';
1501 end if;
1502 else
1503 l_message := 'OKL_LA_PYM_PERIOD_ZERO';
1504 end if;
1505 end if;
1506
1507 if(l_message is null and l_days ) then
1508 if( is_num(p_rulv_rec.rule_information7) and to_number(p_rulv_rec.rule_information7) > 0 ) then
1509 if( not is_num(p_rulv_rec.rule_information8) ) then
1510 l_message := 'OKL_LA_PYM_AMOUNT';
1511 end if;
1512 else
1513 l_message := 'OKL_LA_PYM_DAYS_ZERO';
1514 end if;
1515 end if;
1516 -- Start fix for bug 7111749
1517 l_payment_code := get_payment_type(l_rulv_rec.object2_id1);
1518 if (l_payment_code IN ('RENT', 'LOAN_PAYMENT', 'PRINCIPAL_PAYMENT')) then
1519 if(l_message is null and l_structure ) then
1520 if(l_days and l_days_amt) then
1521 l_message := 'OKL_QA_PAYMENT_STUB_NA';
1522 end if;
1523 end if;
1524 end if;
1525 -- End fix for bug 7111749
1526 if(l_message is not null) then
1527 x_return_status := OKL_API.g_ret_sts_error;
1528 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1529 , p_msg_name => l_message);
1530 raise OKL_API.G_EXCEPTION_ERROR;
1531 end if;
1532
1533
1534 if( p_fee_line_type is null or
1535 p_fee_line_type = '' or
1536 p_fee_line_type = OKL_API.G_MISS_CHAR ) then
1537
1538 --x_rulv_rec := l_rulv_rec;
1539
1540 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1541 x_msg_data => x_msg_data);
1542 return;
1543 end if;
1544
1545
1546 if(p_fee_line_type is not null and p_fee_line_type = 'SECDEPOSIT') then
1547
1548 l_payment_code := get_payment_type(l_rulv_rec.object2_id1);
1549 if( l_payment_code <> 'SECURITY_DEPOSIT') then -- cklee: 11/01/04, 12-03-2004
1550
1551 x_return_status := OKL_API.g_ret_sts_error;
1552 l_ak_prompt := Okl_Accounting_Util.Get_Message_Token(
1553 p_region_code => G_AK_REGION_NAME,
1554 p_attribute_code => 'OKL_LA_PYMTS_TYPE');
1555 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1556 , p_msg_name => 'OKL_LLA_PYMTS_SEC_PYMT'
1557 , p_token1 => 'COL_NAME'
1558 , p_token1_value => l_ak_prompt
1559 );
1560
1561 raise OKL_API.G_EXCEPTION_ERROR;
1562
1563 end if;
1564
1565 end if;
1566
1567
1568 if(p_fee_line_type is not null and p_fee_line_type = 'SECDEPOSIT') then
1569 l_lty_code := get_lty_code(l_rulv_rec.rgp_id);
1570 if(l_lty_code is not null and l_lty_code = 'LINK_FEE_ASSET') then
1571 x_return_status := OKL_API.g_ret_sts_error;
1572 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1573 , p_msg_name => 'OKL_LLA_PYMTS_FEE_NO_ASSET');
1574 raise OKL_API.G_EXCEPTION_ERROR;
1575 end if;
1576 end if;
1577
1578 --Call End Activity
1579 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1580 x_msg_data => x_msg_data);
1581
1582
1583 EXCEPTION
1584 when OKL_API.G_EXCEPTION_ERROR then
1585 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1586 p_api_name => l_api_name,
1587 p_pkg_name => G_PKG_NAME,
1588 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1589 x_msg_count => x_msg_count,
1590 x_msg_data => x_msg_data,
1591 p_api_type => G_API_TYPE);
1592
1593 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1594 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1595 p_api_name => l_api_name,
1596 p_pkg_name => G_PKG_NAME,
1597 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1598 x_msg_count => x_msg_count,
1599 x_msg_data => x_msg_data,
1600 p_api_type => G_API_TYPE);
1601
1602 when OTHERS then
1603 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1604 p_api_name => l_api_name,
1605 p_pkg_name => G_PKG_NAME,
1606 p_exc_name => 'OTHERS',
1607 x_msg_count => x_msg_count,
1608 x_msg_data => x_msg_data,
1609 p_api_type => G_API_TYPE);
1610
1611
1612 END validate_payment;
1613
1614
1615 FUNCTION get_subline_id(
1616 p_chr_id IN NUMBER,
1617 p_topline_id IN NUMBER,
1618 p_asset_id IN NUMBER)
1619 RETURN OKC_K_LINES_B.ID%TYPE IS
1620
1621 l_subline_id OKC_K_LINES_B.ID%TYPE := null;
1622 l_lty_code OKC_LINE_STYLES_b.LTY_CODE%TYPE := null;
1623
1624 cursor LTY_CODE_CSR(P_LINE_ID IN NUMBER) is
1625 SELECT LTY_CODE
1626 FROM
1627 OKC_LINE_STYLES_B LS, OKC_K_LINES_B L
1628 WHERE LS.ID = L.LSE_ID AND L.ID = P_LINE_ID;
1629
1630 cursor SERVICES_CSR(P_CHR_ID IN NUMBER, P_TOPLINE_ID IN NUMBER, P_ASSET_ID IN NUMBER) is
1631 SELECT KLINES1.ID
1632 FROM OKL_PYMTS_SERVICE_INSTS_UV SFINTS,OKC_K_LINES_B KLINES,
1633 OKC_K_LINES_B KLINES1, OKC_LINE_STYLES_B LS, OKC_K_ITEMS KITMS
1634 WHERE KLINES.ID = SFINTS.LINE_ID
1635 AND KLINES.ID = KLINES1.CLE_ID
1636 AND KLINES1.LSE_ID = LS.ID AND LS.LTY_CODE = 'LINK_SERV_ASSET'
1637 AND KITMS.CLE_ID = KLINES1.ID AND KITMS.JTOT_OBJECT1_CODE='OKX_COVASST'
1638 AND KLINES.DNZ_CHR_ID = P_CHR_ID AND KLINES.ID = P_TOPLINE_ID
1639 AND KITMS.OBJECT1_ID1 = P_ASSET_ID;
1640
1641
1642 cursor FEES_CSR(P_CHR_ID IN NUMBER, P_TOPLINE_ID IN NUMBER, P_ASSET_ID IN NUMBER) is
1643 SELECT KLINES1.ID
1644 FROM OKL_PYMTS_FEE_INSTS_UV SFINTS,OKC_K_LINES_B KLINES,
1645 OKC_K_LINES_B KLINES1, OKC_LINE_STYLES_B LS, OKC_K_ITEMS KITMS
1646 WHERE KLINES.ID = SFINTS.LINE_ID
1647 AND KLINES.ID = KLINES1.CLE_ID
1648 AND KLINES1.LSE_ID = LS.ID AND LS.LTY_CODE = 'LINK_FEE_ASSET'
1649 AND KITMS.CLE_ID = KLINES1.ID AND KITMS.JTOT_OBJECT1_CODE='OKX_COVASST'
1650 AND KLINES.DNZ_CHR_ID = P_CHR_ID AND KLINES.ID = P_TOPLINE_ID
1651 AND KITMS.OBJECT1_ID1 = P_ASSET_ID;
1652
1653 BEGIN
1654
1655 open LTY_CODE_CSR(p_topline_id);
1656 fetch LTY_CODE_CSR into l_lty_code;
1657 close LTY_CODE_CSR;
1658
1659
1660 if(l_lty_code = 'SOLD_SERVICE') then
1661 open SERVICES_CSR(p_chr_id, p_topline_id, p_asset_id);
1662 fetch SERVICES_CSR into l_subline_id;
1663 close SERVICES_CSR;
1664 else
1665 open FEES_CSR(p_chr_id, p_topline_id, p_asset_id);
1666 fetch FEES_CSR into l_subline_id;
1667 close FEES_CSR;
1668 end if;
1669 return l_subline_id;
1670 END get_subline_id;
1671
1672
1673 --Added parameter p_pym_tbl by bkatraga for bug 14111538
1674 --Added procedure by bkatraga for bug 13478101
1675 --This procedure will validate the Variable Interest Schedule payment
1676 PROCEDURE validate_vr_payment(
1677 p_api_version IN NUMBER,
1678 p_init_msg_list IN VARCHAR2,
1679 x_return_status OUT NOCOPY VARCHAR2,
1680 x_msg_count OUT NOCOPY NUMBER,
1681 x_msg_data OUT NOCOPY VARCHAR2,
1682 p_chr_id IN NUMBER,
1683 p_payment_id IN NUMBER,
1684 p_pym_hdr_rec IN pym_hdr_rec_type,
1685 p_pym_tbl IN pym_tbl_type) IS
1686
1687 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_VR_PAYMENT';
1688 l_api_version CONSTANT NUMBER := 1.0;
1689 l_stream_type_purpose OKL_STRM_TYPE_V.STREAM_TYPE_PURPOSE%type;
1690
1691 --Added by bkatraga for bug 14111538
1692 i NUMBER := 0;
1693 l_stream_type_name OKL_STRM_TYPE_V.NAME%TYPE;
1694
1695 --Added sty.name by bkatraga for bug 14111538
1696 CURSOR c_stream_type_purpose (p_sty_id number)
1697 IS
1698 SELECT sty.STREAM_TYPE_PURPOSE,
1699 sty.name
1700 FROM OKL_STRM_TYPE_V sty
1701 WHERE sty.ID = p_sty_id;
1702 BEGIN
1703 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1704 -- call START_ACTIVITY to create savepoint, check compatibility
1705 -- and initialize message list
1706 x_return_status := OKL_API.START_ACTIVITY(
1707 p_api_name => l_api_name,
1708 p_pkg_name => G_PKG_NAME,
1709 p_init_msg_list => p_init_msg_list,
1710 l_api_version => l_api_version,
1711 p_api_version => p_api_version,
1712 p_api_type => G_API_TYPE,
1713 x_return_status => x_return_status);
1714
1715 -- check if activity started successfully
1716 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1717 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1718 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
1719 raise OKL_API.G_EXCEPTION_ERROR;
1720 END IF;
1721
1722 --Added l_stream_type_name by bkatraga for bug 14111538
1723 -- get stream type purpose code
1724 OPEN c_stream_type_purpose(p_payment_id);
1725 FETCH c_stream_type_purpose into l_stream_type_purpose,l_stream_type_name;
1726 CLOSE c_stream_type_purpose;
1727
1728 IF(l_stream_type_purpose = 'VARIABLE_INTEREST_SCHEDULE') THEN
1729 IF(p_pym_hdr_rec.arrears = 'N') THEN
1730 --Modified by bkatraga for bug 14111538 to change the message code and token
1731 --Also added raise statement
1732 Okl_Api.set_message(
1733 p_app_name => G_APP_NAME,
1734 p_msg_name => 'OKL_ADVANCE_PMNT_NOT_ALLOWED',
1735 p_token1 => 'STRM_TYPE',
1736 p_token1_value => l_stream_type_name);
1737 RAISE OKL_API.G_EXCEPTION_ERROR;
1738 END IF;
1739 --Added by bkatraga for bug 14111538
1740 IF(p_pym_tbl.count > 0) THEN
1741 i := p_pym_tbl.FIRST;
1742 LOOP
1743 IF(p_pym_tbl(i).update_type <> 'DELETE') THEN
1744 IF((p_pym_tbl(i).STUB_AMOUNT <> OKL_API.G_MISS_CHAR AND p_pym_tbl(i).STUB_AMOUNT <> 0) OR
1745 (p_pym_tbl(i).AMOUNT <> OKL_API.G_MISS_CHAR AND p_pym_tbl(i).AMOUNT <> 0)) THEN
1746 Okl_Api.set_message(
1747 p_app_name => G_APP_NAME,
1748 p_msg_name => 'OKL_VAR_INT_AMOUNT_ZERO');
1749 RAISE OKL_API.G_EXCEPTION_ERROR;
1750 END IF;
1751 END IF;
1752 EXIT WHEN (i >= p_pym_tbl.LAST);
1753 i:= p_pym_tbl.NEXT(i);
1754 END LOOP;
1755 END IF;
1756 --end bkatraga
1757 END IF;
1758
1759 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1760 x_msg_data => x_msg_data);
1761 EXCEPTION
1762 when OKL_API.G_EXCEPTION_ERROR then
1763 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1764 p_api_name => l_api_name,
1765 p_pkg_name => G_PKG_NAME,
1766 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1767 x_msg_count => x_msg_count,
1768 x_msg_data => x_msg_data,
1769 p_api_type => G_API_TYPE);
1770
1771 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1772 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1773 p_api_name => l_api_name,
1774 p_pkg_name => G_PKG_NAME,
1775 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1776 x_msg_count => x_msg_count,
1777 x_msg_data => x_msg_data,
1778 p_api_type => G_API_TYPE);
1779
1780 when OTHERS then
1781 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1782 p_api_name => l_api_name,
1783 p_pkg_name => G_PKG_NAME,
1784 p_exc_name => 'OTHERS',
1785 x_msg_count => x_msg_count,
1786 x_msg_data => x_msg_data,
1787 p_api_type => G_API_TYPE);
1788 END validate_vr_payment;
1789 --end bkatraga
1790
1791
1792 PROCEDURE migrate_rec(
1793 p_chr_id IN NUMBER,
1794 p_rgp_id IN NUMBER,
1795 p_slh_id IN NUMBER,
1796 p_pym_hdr_rec IN pym_hdr_rec_type,
1797 p_pym_rec IN pym_rec_type,
1798 x_rulv_rec OUT NOCOPY rulv_rec_type) IS
1799
1800 l_rulv_rec rulv_rec_type;
1801 i NUMBER := 0;
1802 l_slh_id varchar2(300);
1803
1804 -- temp
1805 valid boolean := true;
1806
1807 Begin
1808
1809 if((p_pym_rec.stub_days is null or p_pym_rec.stub_days = OKL_API.G_MISS_CHAR) and
1810 (p_pym_rec.stub_amount is null or p_pym_rec.stub_amount = OKL_API.G_MISS_CHAR) and
1811 (p_pym_rec.period is null or p_pym_rec.period = OKL_API.G_MISS_CHAR) and
1812 (p_pym_rec.amount is null or p_pym_rec.amount = OKL_API.G_MISS_CHAR)) then
1813 valid := false;
1814 end if;
1815
1816 if(not valid) then
1817 x_rulv_rec := null;
1818 return;
1819 end if;
1820
1821 l_slh_id := to_char(p_slh_id);
1822 if(p_pym_rec.update_type = 'CREATE') then
1823 l_rulv_rec.id := null;
1824 elsif(p_pym_rec.update_type = 'UPDATE') then
1825 l_rulv_rec.id := p_pym_rec.rule_id;
1826 end if;
1827 if(p_pym_rec.update_type <> 'DELETE') then
1828
1829 l_rulv_rec.rule_information7 := p_pym_rec.stub_days;
1830 l_rulv_rec.rule_information8 := p_pym_rec.stub_amount;
1831 l_rulv_rec.rule_information3 := p_pym_rec.period;
1832 l_rulv_rec.rule_information6 := p_pym_rec.amount;
1833 l_rulv_rec.rule_information2 := p_pym_rec.sort_date;
1834 l_rulv_rec.jtot_object1_code := 'OKL_TUOM';
1835 l_rulv_rec.object1_id1 := p_pym_hdr_rec.frequency;
1836 l_rulv_rec.rule_information10 := p_pym_hdr_rec.arrears;
1837 l_rulv_rec.rule_information5 := p_pym_hdr_rec.structure;
1838 l_rulv_rec.jtot_object2_code := 'OKL_STRMHDR';
1839 l_rulv_rec.object2_id1 := l_slh_id;
1840 l_rulv_rec.object2_id2 := '#';
1841 l_rulv_rec.dnz_chr_id := p_chr_id;
1842 l_rulv_rec.rgp_id := p_rgp_id;
1843 l_rulv_rec.sfwt_flag := 'N';
1844 l_rulv_rec.std_template_yn := 'N';
1845 l_rulv_rec.warn_yn := 'N';
1846 l_rulv_rec.rule_information_category := 'LASLL';
1847 end if;
1848
1849
1850 x_rulv_rec := l_rulv_rec;
1851
1852 END migrate_rec;
1853
1854
1855 FUNCTION check_rec(p_pym_rec IN pym_rec_type)
1856 RETURN boolean IS
1857
1858 BEGIN
1859 if((p_pym_rec.stub_days is null or p_pym_rec.stub_days = OKL_API.G_MISS_CHAR) and
1860 (p_pym_rec.stub_amount is null or p_pym_rec.stub_amount = OKL_API.G_MISS_CHAR) and
1861 (p_pym_rec.period is null or p_pym_rec.period = OKL_API.G_MISS_CHAR) and
1862 (p_pym_rec.amount is null or p_pym_rec.amount = OKL_API.G_MISS_CHAR)) then
1863 return true;
1864 else
1865 return false;
1866 end if;
1867 END check_rec;
1868
1869
1870 PROCEDURE get_payment(
1871 p_api_version IN NUMBER,
1872 p_init_msg_list IN VARCHAR2,
1873 x_return_status OUT NOCOPY VARCHAR2,
1874 x_msg_count OUT NOCOPY NUMBER,
1875 x_msg_data OUT NOCOPY VARCHAR2,
1876 p_chr_id OKC_K_HEADERS_B.ID%TYPE,
1877 p_service_fee_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM,
1878 p_asset_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM,
1879 p_payment_id OKL_STRMTYP_SOURCE_V.ID1%TYPE,
1880 x_pym_level OUT NOCOPY VARCHAR2,
1881 x_slh_id OUT NOCOPY OKC_RULES_V.ID%TYPE,
1882 x_rgp_id OUT NOCOPY OKC_RULE_GROUPS_V.ID%TYPE,
1883 x_cle_id OUT NOCOPY OKC_K_LINES_B.ID%TYPE) IS
1884
1885 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1886 l_api_name CONSTANT VARCHAR2(30) := 'GET_PAYMENT';
1887 l_api_version CONSTANT NUMBER := 1.0;
1888
1889 l_pym_level VARCHAR2(30) := 'HEADER';
1890 l_subline_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
1891 l_slh_id OKC_RULES_V.ID%TYPE := OKL_API.G_MISS_NUM;
1892 l_rgp_id OKC_RULE_GROUPS_V.ID%TYPE := OKL_API.G_MISS_NUM;
1893 l_cle_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
1894
1895 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
1896
1897 ------------- temp
1898 Cursor get_dir is
1899 SELECT nvl(substrb(translate(ltrim(value),',',' '),
1900 1,
1901 instr(translate(ltrim(value),',',' '),' ') - 1),value)
1902 FROM v$parameter
1903 WHERE name = 'utl_file_dir';
1904
1905 l_TEMP_DIR varchar2(200);
1906 delimit varchar2(10) := ' ';
1907
1908 ------------- temp
1909
1910 cursor RGP_HDR_CSR(P_CHR_ID IN NUMBER) is
1911 SELECT
1912 ID
1913 FROM OKC_RULE_GROUPS_V WHERE
1914 DNZ_CHR_ID = P_CHR_ID AND CHR_ID = P_CHR_ID
1915 AND RGD_CODE = 'LALEVL'
1916 AND CLE_ID IS NULL;
1917
1918 cursor RGP_CLE_CSR(P_CHR_ID IN NUMBER, P_CLE_ID IN NUMBER) is
1919 SELECT
1920 ID
1921 FROM OKC_RULE_GROUPS_V RG WHERE
1922 RG.DNZ_CHR_ID = P_CHR_ID AND RG.CHR_ID IS NULL
1923 AND RGD_CODE = 'LALEVL'
1924 AND RG.CLE_ID = P_CLE_ID;
1925
1926 cursor SLH_CSR(P_RGP_ID IN NUMBER, P_PAYMENT_ID IN VARCHAR2) is
1927 SELECT
1928 ID
1929 FROM OKC_RULES_B SLH WHERE
1930 SLH.RGP_ID = P_RGP_ID AND
1931 -- bug 3377730.
1932 SLH.OBJECT1_ID1 = P_PAYMENT_ID;
1933 -- SLH.OBJECT1_ID1 = TO_CHAR(P_PAYMENT_ID);
1934
1935 begin
1936
1937 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1938 -- call START_ACTIVITY to create savepoint, check compatibility
1939 -- and initialize message list
1940 x_return_status := OKL_API.START_ACTIVITY(
1941 p_api_name => l_api_name,
1942 p_pkg_name => G_PKG_NAME,
1943 p_init_msg_list => p_init_msg_list,
1944 l_api_version => l_api_version,
1945 p_api_version => p_api_version,
1946 p_api_type => G_API_TYPE,
1947 x_return_status => x_return_status);
1948
1949 -- check if activity started successfully
1950 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1951 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1952 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
1953 raise OKL_API.G_EXCEPTION_ERROR;
1954 END IF;
1955
1956 /* -- temp
1957 open get_dir;
1958 fetch get_dir into l_temp_dir;
1959 if get_dir%notfound then
1960 null;
1961 end if;
1962 close get_dir;
1963 */
1964 --fnd_file.put_names(l_log, l_out, l_temp_dir);
1965 -- temp
1966
1967
1968 if( p_payment_id is null or p_payment_id = OKL_API.G_MISS_NUM) then
1969 x_return_status := OKL_API.g_ret_sts_error;
1970 OKL_API.SET_MESSAGE( p_app_name => g_app_name
1971 , p_msg_name => 'OKL_LLA_PMT_SELECT');
1972 raise OKL_API.G_EXCEPTION_ERROR;
1973 end if;
1974
1975
1976 if( p_asset_id is not null and p_asset_id <> OKL_API.G_MISS_NUM) then
1977 l_pym_level := 'ASSET';
1978 l_cle_id := p_asset_id;
1979 end if;
1980 if(p_service_fee_id is not null and p_service_fee_id <> OKL_API.G_MISS_NUM) then
1981 if(l_pym_level = 'ASSET') then
1982 l_pym_level := 'SUBLINE';
1983 else
1984 l_pym_level := 'SERVICE_FEE';
1985 l_cle_id := p_service_fee_id;
1986 end if;
1987 end if;
1988
1989
1990 if(l_pym_level = 'SUBLINE') then
1991 l_subline_id := get_subline_id(p_chr_id, p_service_fee_id, p_asset_id);
1992 l_cle_id := l_subline_id;
1993 end if;
1994
1995 if(l_pym_level = 'HEADER') then
1996 open RGP_HDR_CSR(p_chr_id);
1997 fetch RGP_HDR_CSR into l_rgp_id;
1998 close RGP_HDR_CSR;
1999 else
2000 open RGP_CLE_CSR(p_chr_id, l_cle_id);
2001 fetch RGP_CLE_CSR into l_rgp_id;
2002 close RGP_CLE_CSR;
2003 end if;
2004
2005 if(l_rgp_id is not null and l_rgp_id <> OKL_API.G_MISS_NUM) then
2006 open SLH_CSR(l_rgp_id, p_payment_id);
2007 fetch SLH_CSR into l_slh_id;
2008 close SLH_CSR;
2009
2010 end if;
2011
2012
2013 x_pym_level := l_pym_level;
2014 x_slh_id := l_slh_id;
2015 x_rgp_id := l_rgp_id;
2016 x_cle_id := l_cle_id;
2017
2018
2019 --Call End Activity
2020 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2021 x_msg_data => x_msg_data);
2022
2023 EXCEPTION
2024 when OKL_API.G_EXCEPTION_ERROR then
2025 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2026 p_api_name => l_api_name,
2027 p_pkg_name => G_PKG_NAME,
2028 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2029 x_msg_count => x_msg_count,
2030 x_msg_data => x_msg_data,
2031 p_api_type => G_API_TYPE);
2032
2033 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
2034 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2035 p_api_name => l_api_name,
2036 p_pkg_name => G_PKG_NAME,
2037 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2038 x_msg_count => x_msg_count,
2039 x_msg_data => x_msg_data,
2040 p_api_type => G_API_TYPE);
2041
2042 when OTHERS then
2043 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2044 p_api_name => l_api_name,
2045 p_pkg_name => G_PKG_NAME,
2046 p_exc_name => 'OTHERS',
2047 x_msg_count => x_msg_count,
2048 x_msg_data => x_msg_data,
2049 p_api_type => G_API_TYPE);
2050
2051 END get_payment;
2052
2053
2054 FUNCTION is_investor_fee_payment(p_chr_id IN NUMBER,
2055 p_payment IN NUMBER, p_rgp_id IN NUMBER)
2056 RETURN boolean IS
2057
2058 cursor inv_dtls_csr(p_chr_id IN NUMBER) is
2059 SELECT scs_code
2060 FROM OKC_K_HEADERS_B
2061 WHERE ID = p_chr_id ;
2062 CURSOR fee_line_amount_csr(p_rgp_id IN NUMBER) is
2063 SELECT kleb.amount amount
2064 FROM okc_k_lines_b cleb,
2065 okl_k_lines kleb,
2066 okc_line_styles_b lseb,
2067 okc_k_headers_b chrb,
2068 okc_rule_groups_b rg
2069 WHERE chrb.id = cleb.dnz_chr_id
2070 AND kleb.id = cleb.id
2071 AND cleb.lse_id = lseb.id
2072 AND lseb.lty_code = 'FEE'
2073 AND rg.cle_id = cleb.id
2074 AND rg.id = p_rgp_id;
2075
2076
2077
2078 l_inv_dtls inv_dtls_csr%ROWTYPE;
2079 l_fee_amount_dtls fee_line_amount_csr%ROWTYPE ;
2080
2081
2082 --l_ret_value VARCHAR2(1);
2083
2084 BEGIN
2085
2086 --l_ret_value := OKL_API.G_FALSE;
2087
2088 OPEN inv_dtls_csr(p_chr_id => p_chr_id);
2089 FETCH inv_dtls_csr INTO l_inv_dtls;
2090 CLOSE inv_dtls_csr;
2091
2092 IF l_inv_dtls.scs_code = 'INVESTOR' THEN
2093 OPEN fee_line_amount_csr(p_rgp_id => p_rgp_id);
2094 FETCH fee_line_amount_csr INTO l_fee_amount_dtls;
2095 CLOSE fee_line_amount_csr;
2096
2097
2098 IF(p_payment=l_fee_amount_dtls.amount) THEN
2099 return true;
2100 ELSE
2101 return false;
2102 END IF;
2103 ELSE
2104 return true;
2105 END IF ;
2106
2107
2108 RETURN true;
2109
2110 END is_investor_fee_payment;
2111
2112
2113 PROCEDURE process_payment(
2114 p_api_version IN NUMBER,
2115 p_init_msg_list IN VARCHAR2,
2116 x_return_status OUT NOCOPY VARCHAR2,
2117 x_msg_count OUT NOCOPY NUMBER,
2118 x_msg_data OUT NOCOPY VARCHAR2,
2119 p_chr_id OKC_K_HEADERS_B.ID%TYPE,
2120 p_service_fee_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM,
2121 p_asset_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM,
2122 p_payment_id OKL_STRMTYP_SOURCE_V.ID1%TYPE,
2123 p_pym_hdr_rec IN pym_hdr_rec_type,
2124 p_pym_tbl IN pym_tbl_type,
2125 p_update_type IN VARCHAR2,
2126 x_rulv_tbl OUT NOCOPY rulv_tbl_type) IS
2127
2128 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2129 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_PAYMENT';
2130 l_api_version CONSTANT NUMBER := 1.0;
2131 l_totalpayment NUMBER :=0 ;
2132 i NUMBER := 0;
2133 j NUMBER := 0;
2134 k NUMBER := 0;
2135 l NUMBER := 0;
2136 kount NUMBER := 0;
2137 empty_rec boolean := false;
2138
2139 l_start_date OKC_K_HEADERS_B.START_DATE%TYPE := null;
2140 l_org_id OKC_K_HEADERS_B.AUTHORING_ORG_ID%TYPE := null;
2141 l_ccode OKC_K_HEADERS_B.CURRENCY_CODE%TYPE := null;
2142
2143 l_crea_rulv_tbl rulv_tbl_type;
2144 l_updt_rulv_tbl rulv_tbl_type;
2145 l_delt_rulv_tbl rulv_tbl_type;
2146
2147 l_rulv_rec rulv_rec_type := NULL;
2148 lx_rulv_rec rulv_rec_type := NULL;
2149 l_rulv_rec2 rulv_rec_type := NULL;
2150
2151 lx_rulv_tbl rulv_tbl_type;
2152
2153 l_rgpv_rec OKL_RULE_PUB.rgpv_rec_type := NULL;
2154 lx_rgpv_rec OKL_RULE_PUB.rgpv_rec_type := NULL;
2155
2156 l_pym_level VARCHAR2(30) := 'HEADER';
2157 l_subline_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
2158 l_slh_id OKC_RULES_V.ID%TYPE := OKL_API.G_MISS_NUM;
2159 l_rgp_id OKC_RULE_GROUPS_V.ID%TYPE := OKL_API.G_MISS_NUM;
2160 l_cle_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
2161 l_stream_id OKC_RULES_V.OBJECT1_ID1%TYPE := OKL_API.G_MISS_CHAR;
2162
2163 l_fee_line_type OKL_K_FEE_LINES_UV.FEE_TYPE%TYPE := null;
2164 l_lty_code OKC_LINE_STYLES_V.LTY_CODE%TYPE := null;
2165 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
2166
2167 --Bug# 13973475
2168 l_pdt_parameter_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
2169 l_stream_type_purpose OKL_STRM_TYPE_B.stream_type_purpose%TYPE;
2170
2171 ------------- temp
2172 Cursor get_dir is
2173 SELECT nvl(substrb(translate(ltrim(value),',',' '),
2174 1,
2175 instr(translate(ltrim(value),',',' '),' ') - 1),value)
2176 FROM v$parameter
2177 WHERE name = 'utl_file_dir';
2178
2179 l_TEMP_DIR varchar2(200);
2180 delimit varchar2(10) := ' ';
2181
2182 ------------- temp
2183
2184 cursor CHR_DTLS_CSR(P_CHR_ID IN NUMBER) is
2185 SELECT
2186 -- bug
2187 -- START_DATE,
2188 -- bug
2189 A.AUTHORING_ORG_ID,
2190 A.CURRENCY_CODE,
2191 B.DEAL_TYPE -- Bug 4887014
2192 FROM OKC_K_HEADERS_B A, OKL_K_HEADERS B WHERE
2193 A.ID = P_CHR_ID
2194 AND A.ID = B.ID;
2195
2196 --Changed query for performance --dkagrawa
2197 CURSOR FEE_LINE_CSR(P_RGP_ID IN NUMBER) is
2198 SELECT kleb.fee_type fee_type
2199 FROM okc_k_lines_b cleb,
2200 okl_k_lines kleb,
2201 okc_line_styles_b lseb,
2202 okc_k_headers_b chrb,
2203 okc_rule_groups_b rg
2204 WHERE chrb.id = cleb.dnz_chr_id
2205 AND kleb.id = cleb.id
2206 AND cleb.lse_id = lseb.id
2207 AND lseb.lty_code = 'FEE'
2208 AND rg.cle_id = cleb.id
2209 AND rg.id = p_rgp_id;
2210
2211
2212 CURSOR INVALID_LINE_CSR(P_CLE_ID IN NUMBER) is
2213 SELECT 'Y'
2214 FROM OKC_STATUSES_V OKCS, OKC_K_LINES_B CLE
2215 WHERE CLE.STS_CODE = OKCS.CODE
2216 -- AND OKCS.STE_CODE IN ('EXPIRED','HOLD','CANCELLED','TERMINATED')
2217 AND OKCS.STE_CODE IN ('TERMINATED')
2218 AND CLE.ID = P_CLE_ID;
2219 -- added for bug 5115701 - start
2220 CURSOR GET_FEE_TYPE_SUB_CSR(P_CHR_ID IN NUMBER, p_service_fee_id IN NUMBER) is
2221 SELECT fee_line.fee_type
2222 FROM okl_k_lines_full_v l,
2223 okc_line_styles_v sty,
2224 okc_statuses_v sts,
2225 okl_k_lines fee_line
2226 WHERE l.lse_id = sty.id
2227 AND l.sts_code = sts.code
2228 AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED')
2229 AND sty.lty_code = 'LINK_FEE_ASSET'
2230 AND l.dnz_chr_id = P_CHR_ID
2231 AND l.cle_id = fee_line.id
2232 and fee_line.id = p_service_fee_id;
2233
2234 l_subline_fee_type okl_k_lines.fee_type%type;
2235 -- added for bug 5115701 - end
2236 l_invalid_line varchar2(1) := 'N';
2237
2238 l_chr_rec CHR_DTLS_CSR%ROWTYPE;
2239 l_deal_type OKL_K_HEADERS.DEAL_TYPE%TYPE;
2240
2241 -- R12B Authoring OA Migration
2242 l_upfront_tax_pymt_yn VARCHAR2(1);
2243
2244 CURSOR l_contract_csr(p_chr_id IN NUMBER) IS
2245 SELECT chrb.sts_code
2246 FROM okc_k_headers_b chrb
2247 WHERE chrb.id = p_chr_id;
2248
2249 l_contract_rec l_contract_csr%ROWTYPE;
2250
2251 --Added by bkatraga for bug 13478101
2252 l_update_type VARCHAR2(30);
2253
2254 begin
2255
2256
2257
2258 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2259 -- call START_ACTIVITY to create savepoint, check compatibility
2260 -- and initialize message list
2261 x_return_status := OKL_API.START_ACTIVITY(
2262 p_api_name => l_api_name,
2263 p_pkg_name => G_PKG_NAME,
2264 p_init_msg_list => p_init_msg_list,
2265 l_api_version => l_api_version,
2266 p_api_version => p_api_version,
2267 p_api_type => G_API_TYPE,
2268 x_return_status => x_return_status);
2269
2270 -- check if activity started successfully
2271 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2272 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2273 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2274 raise OKL_API.G_EXCEPTION_ERROR;
2275 END IF;
2276
2277 /* -- temp
2278 open get_dir;
2279 fetch get_dir into l_temp_dir;
2280 if get_dir%notfound then
2281 null;
2282 end if;
2283 close get_dir;
2284 */
2285 --fnd_file.put_names(l_log, l_out, l_temp_dir);
2286 -- temp
2287
2288 -- R12B Authoring OA Migration
2289 -- Check if the Payment is for an Upfront Tax Fee line
2290 /* 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
2291 x_return_status := OKL_API.g_ret_sts_error;
2292 OKL_API.SET_MESSAGE( p_app_name => g_app_name
2293 , p_msg_name => 'OKL_LA_INV_PAY_MISMATCH');
2294 raise OKL_API.G_EXCEPTION_ERROR;
2295 end if;
2296 */
2297 l_upfront_tax_pymt_yn := OKL_API.G_FALSE;
2298 IF (p_service_fee_id IS NOT NULL) THEN
2299 l_upfront_tax_pymt_yn := is_upfront_tax_fee_payment(p_chr_id => p_chr_id,
2300 p_cle_id => p_service_fee_id);
2301
2302 IF (l_upfront_tax_pymt_yn = OKL_API.G_TRUE) THEN
2303 OPEN l_contract_csr(p_chr_id => p_chr_id);
2304 FETCH l_contract_csr INTO l_contract_rec;
2305 CLOSE l_contract_csr;
2306
2307 END IF;
2308 END IF;
2309
2310 -- START: June 24, 2005 cklee: Modification for okl.h Sales Quote enhancement
2311 x_return_status := validate_capitalize_dp
2312 (p_asset_id => p_asset_id,
2313 p_service_fee_id => p_service_fee_id,
2314 p_payment_id => p_payment_id);
2315 --- Store the highest degree of error
2316 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2317 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2318 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2319 raise OKL_API.G_EXCEPTION_ERROR;
2320 END IF;
2321 -- END: June 24, 2005 cklee: Modification for okl.h Sales Quote enhancement
2322
2323 -- START: Setp 02, 2005 cklee: Variable rate ER for GE - 20
2324 x_return_status := validate_payment_type_asset
2325 (p_chr_id => p_chr_id,
2326 p_asset_id => p_asset_id,
2327 p_service_fee_id => p_service_fee_id,
2328 p_payment_id => p_payment_id);
2329 --- Store the highest degree of error
2330 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2331 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2332 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2333 raise OKL_API.G_EXCEPTION_ERROR;
2334 END IF;
2335 -- END: Setp 02, 2005 cklee: Variable rate ER for GE - 20
2336
2337 --Added parameter p_pym_tbl by bkatraga for bug 14111538
2338 --Added by bkatraga for bug 13478101
2339 validate_vr_payment(p_api_version => p_api_version,
2340 p_init_msg_list => p_init_msg_list,
2341 x_return_status => x_return_status,
2342 x_msg_count => x_msg_count,
2343 x_msg_data => x_msg_data,
2344 p_chr_id => p_chr_id,
2345 p_payment_id => p_payment_id,
2346 p_pym_hdr_rec => p_pym_hdr_rec,
2347 p_pym_tbl => p_pym_tbl);
2348
2349 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2350 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2351 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2352 raise OKL_API.G_EXCEPTION_ERROR;
2353 END IF;
2354 --end bkatraga
2355
2356 get_payment(
2357 p_api_version => p_api_version,
2358 p_init_msg_list => p_init_msg_list,
2359 x_return_status => x_return_status,
2360 x_msg_count => x_msg_count,
2361 x_msg_data => x_msg_data,
2362 p_chr_id => p_chr_id,
2363 p_service_fee_id => p_service_fee_id,
2364 p_asset_id => p_asset_id,
2365 p_payment_id => p_payment_id,
2366 x_pym_level => l_pym_level,
2367 x_slh_id => l_slh_id,
2368 x_rgp_id => l_rgp_id,
2369 x_cle_id => l_cle_id);
2370
2371
2372 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2373 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2374 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2375 raise OKL_API.G_EXCEPTION_ERROR;
2376 END IF;
2377
2378 --Bug# 4959361
2379 IF l_cle_id IS NOT NULL THEN
2380 OKL_LLA_UTIL_PVT.check_line_update_allowed
2381 (p_api_version => p_api_version,
2382 p_init_msg_list => p_init_msg_list,
2383 x_return_status => x_return_status,
2384 x_msg_count => x_msg_count,
2385 x_msg_data => x_msg_data,
2386 p_cle_id => l_cle_id);
2387
2388 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2389 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2390 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2391 RAISE OKL_API.G_EXCEPTION_ERROR;
2392 END IF;
2393 END IF;
2394 --Bug# 4959361
2395
2396 if(l_rgp_id is null or l_rgp_id = OKL_API.G_MISS_NUM) then
2397 if(l_pym_level = 'HEADER') then
2398 l_rgpv_rec.chr_id := p_chr_id;
2399 l_rgpv_rec.dnz_chr_id := p_chr_id;
2400 l_rgpv_rec.cle_id := null;
2401 else
2402 l_rgpv_rec.chr_id := null;
2403 l_rgpv_rec.dnz_chr_id := p_chr_id;
2404 l_rgpv_rec.cle_id := l_cle_id;
2405 end if;
2406
2407 l_rgpv_rec.rgd_code := 'LALEVL';
2408 l_rgpv_rec.rgp_type := 'KRG';
2409
2410
2411
2412
2413 OKL_RULE_PUB.create_rule_group(
2414 p_api_version => p_api_version,
2415 p_init_msg_list => p_init_msg_list,
2416 x_return_status => x_return_status,
2417 x_msg_count => x_msg_count,
2418 x_msg_data => x_msg_data,
2419 p_rgpv_rec => l_rgpv_rec,
2420 x_rgpv_rec => lx_rgpv_rec);
2421
2422
2423 l_rgp_id := lx_rgpv_rec.id;
2424
2425 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2426 RAISE OKL_API.G_EXCEPTION_ERROR;
2427 END IF;
2428 end if;
2429
2430
2431 IF(l_slh_id is null or l_slh_id = OKL_API.G_MISS_NUM) then
2432 l_rulv_rec := l_rulv_rec2;
2433 -- l_rulv_rec.object_version_number := l_rgr_rec.object_version_number;
2434 l_rulv_rec.dnz_chr_id := p_chr_id;
2435 l_rulv_rec.rgp_id := l_rgp_id;
2436 l_rulv_rec.jtot_object1_code := 'OKL_STRMTYP';
2437 l_rulv_rec.object1_id1 := p_payment_id;
2438 l_rulv_rec.std_template_yn := 'N';
2439 l_rulv_rec.warn_yn := 'N';
2440 l_rulv_rec.template_yn := 'N';
2441 l_rulv_rec.sfwt_flag := 'N';
2442 l_rulv_rec.rule_information_category := 'LASLH';
2443
2444 OKL_RULE_PUB.create_rule(
2445 p_api_version => p_api_version,
2446 p_init_msg_list => p_init_msg_list,
2447 x_return_status => x_return_status,
2448 x_msg_count => x_msg_count,
2449 x_msg_data => x_msg_data,
2450 p_rulv_rec => l_rulv_rec,
2451 x_rulv_rec => lx_rulv_rec);
2452
2453 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2454 RAISE OKL_API.G_EXCEPTION_ERROR;
2455 END IF;
2456 l_slh_id := lx_rulv_rec.id;
2457 end if;
2458
2459 OPEN FEE_LINE_CSR(l_rgp_id);
2460 FETCH FEE_LINE_CSR INTO l_fee_line_type;
2461 CLOSE FEE_LINE_CSR;
2462
2463 open CHR_DTLS_CSR(p_chr_id);
2464 -- bug
2465 fetch CHR_DTLS_CSR into l_org_id, l_ccode, l_deal_type;
2466 l_start_date := get_start_date(p_chr_id, l_cle_id);
2467 -- bug
2468 close CHR_DTLS_CSR;
2469 MO_GLOBAL.set_policy_context('S',l_org_id);
2470
2471
2472 j := 0;
2473 k := 0;
2474 l := 0;
2475
2476 if(p_pym_tbl.count > 0) then
2477 i := p_pym_tbl.FIRST;
2478 loop
2479 l_rulv_rec := null;
2480 empty_rec := check_rec(p_pym_tbl(i));
2481
2482 --Added by bkatraga for bug 13478101
2483 l_update_type := p_pym_tbl(i).update_type;
2484
2485 if(p_pym_tbl(i).update_type = 'DELETE' or (empty_rec
2486 and p_pym_tbl(i).rule_id is not null and p_pym_tbl(i).rule_id <> OKL_API.G_MISS_NUM)) then
2487 l_rulv_rec.id := p_pym_tbl(i).rule_id;
2488 l := l + 1;
2489 l_delt_rulv_tbl(l) := l_rulv_rec;
2490
2491 else if ( not empty_rec) then
2492
2493
2494 migrate_rec(
2495 p_chr_id => p_chr_id,
2496 p_rgp_id => l_rgp_id,
2497 p_slh_id => l_slh_id,
2498 p_pym_hdr_rec => p_pym_hdr_rec,
2499 p_pym_rec => p_pym_tbl(i),
2500 x_rulv_rec => l_rulv_rec);
2501
2502
2503 validate_payment(
2504 p_api_version => p_api_version,
2505 p_init_msg_list => p_init_msg_list,
2506 x_return_status => x_return_status,
2507 x_msg_count => x_msg_count,
2508 x_msg_data => x_msg_data,
2509 p_rulv_rec => l_rulv_rec,
2510 p_fee_line_type => l_fee_line_type,
2511 p_payment_type => p_update_type,
2512 p_type => p_pym_tbl(i).update_type);
2513
2514
2515 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2516 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2517 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2518 raise OKL_API.G_EXCEPTION_ERROR;
2519 END IF;
2520
2521 if(l_rulv_rec.rule_information8 is not null and is_num(l_rulv_rec.rule_information8)) then
2522 l_rulv_rec.rule_information8 := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(
2523 p_amount => to_number(l_rulv_rec.rule_information8),
2524 p_currency_code => l_ccode);
2525 l_totalpayment:=l_totalpayment+l_rulv_rec.rule_information8;
2526 end if;
2527
2528 if(l_rulv_rec.rule_information6 is not null and is_num(l_rulv_rec.rule_information6)) then
2529 l_rulv_rec.rule_information6 := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(
2530 p_amount => to_number(l_rulv_rec.rule_information6),
2531 p_currency_code => l_ccode);
2532 l_totalpayment:=l_totalpayment+(l_rulv_rec.rule_information6*l_rulv_rec.rule_information3);
2533 end if;
2534
2535 if(p_pym_tbl(i).update_type = 'CREATE') then
2536 j := j + 1;
2537 l_crea_rulv_tbl(j) := l_rulv_rec;
2538 else
2539 k := k + 1;
2540 l_updt_rulv_tbl(k) := l_rulv_rec;
2541 end if;
2542 end if;
2543 end if;
2544
2545 exit when (i >= p_pym_tbl.last);
2546 i:= p_pym_tbl.NEXT(i);
2547 end loop;
2548 end if;
2549 IF(not is_investor_fee_payment(p_chr_id =>p_chr_id, p_payment =>l_totalpayment , p_rgp_id => l_rgp_id )) then
2550 x_return_status := OKL_API.g_ret_sts_error;
2551 OKL_API.SET_MESSAGE( p_app_name => g_app_name
2552 , p_msg_name => 'OKL_LA_INV_PAY_MISMATCH');
2553 raise OKL_API.G_EXCEPTION_ERROR;
2554 end if;
2555
2556 if (l_delt_rulv_tbl.count > 0) then
2557 OKL_RULE_PUB.delete_rule(
2558 p_api_version => p_api_version,
2559 p_init_msg_list => p_init_msg_list,
2560 x_return_status => x_return_status,
2561 x_msg_count => x_msg_count,
2562 x_msg_data => x_msg_data,
2563 p_rulv_tbl => l_delt_rulv_tbl);
2564
2565 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2566 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2567 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2568 raise OKL_API.G_EXCEPTION_ERROR;
2569 END IF;
2570 end if;
2571
2572 if (l_crea_rulv_tbl.count > 0) then
2573 --Bug# 4861465
2574 i := l_crea_rulv_tbl.FIRST;
2575 --i := p_pym_tbl.FIRST;
2576 loop
2577 l_crea_rulv_tbl(i).rule_information1 := to_char(i);
2578 exit when (i >= l_crea_rulv_tbl.last);
2579 i:= l_crea_rulv_tbl.NEXT(i);
2580 end loop;
2581
2582 OKL_RULE_PUB.create_rule(
2583 p_api_version => p_api_version,
2584 p_init_msg_list => p_init_msg_list,
2585 x_return_status => x_return_status,
2586 x_msg_count => x_msg_count,
2587 x_msg_data => x_msg_data,
2588 p_rulv_tbl => l_crea_rulv_tbl,
2589 x_rulv_tbl => x_rulv_tbl);
2590
2591
2592
2593 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2594 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2595 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2596 RAISE OKL_API.G_EXCEPTION_ERROR;
2597 END IF;
2598 end if;
2599
2600 if (l_updt_rulv_tbl.count > 0) then
2601
2602 -- added for bug 5115701 - start
2603 -- check only if it's an asset sub line payment
2604 IF (p_asset_id IS NOT NULL AND
2605 p_asset_id <> OKL_API.G_MISS_NUM)
2606 AND
2607 (p_service_fee_id IS NOT NULL AND
2608 p_service_fee_id <> OKL_API.G_MISS_NUM)
2609 THEN
2610
2611 OPEN GET_FEE_TYPE_SUB_CSR(p_chr_id, p_service_fee_id);
2612 FETCH GET_FEE_TYPE_SUB_CSR INTO l_subline_fee_type;
2613 CLOSE GET_FEE_TYPE_SUB_CSR;
2614
2615
2616 IF(l_subline_fee_type IS NOT NULL AND l_subline_fee_type = 'FINANCED') then
2617
2618 x_return_status := OKL_API.g_ret_sts_error;
2619 OKL_API.SET_MESSAGE( p_app_name => g_app_name
2620 , p_msg_name => 'OKL_LLA_PYMTS_NO_UPDATE');
2621 raise OKL_API.G_EXCEPTION_ERROR;
2622 END IF;
2623 END IF;
2624 -- added for bug 5115701 - End
2625
2626 OKL_RULE_PUB.update_rule(
2627 p_api_version => p_api_version,
2628 p_init_msg_list => p_init_msg_list,
2629 x_return_status => x_return_status,
2630 x_msg_count => x_msg_count,
2631 x_msg_data => x_msg_data,
2632 p_rulv_tbl => l_updt_rulv_tbl,
2633 x_rulv_tbl => x_rulv_tbl);
2634
2635
2636 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2637 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2638 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2639 RAISE OKL_API.G_EXCEPTION_ERROR;
2640 END IF;
2641 end if;
2642
2643 if(l_pym_level <> 'HEADER') then
2644 OPEN INVALID_LINE_CSR(l_cle_id);
2645 FETCH INVALID_LINE_CSR INTO l_invalid_line;
2646 CLOSE INVALID_LINE_CSR;
2647 end if;
2648
2649 l_detail_count := get_sll_period_count(l_rgp_id,
2650 l_slh_id,
2651 p_chr_id);
2652 if( l_detail_count < 1 and l_invalid_line = 'N') then
2653 IF (l_deal_type NOT IN ('LOAN', 'LOAN-REVOLVING')) THEN -- Bug 4887014
2654 x_return_status := OKL_API.g_ret_sts_error;
2655 OKL_API.SET_MESSAGE( p_app_name => g_app_name
2656 , p_msg_name => 'OKL_LLA_PYM_ONE_PERIOD');
2657 raise OKL_API.G_EXCEPTION_ERROR;
2658 END IF;
2659 end if;
2660 if(l_fee_line_type is not null and l_fee_line_type = 'SECDEPOSIT') then
2661 l_detail_count := verify_sec_deposit_count(l_rgp_id,
2662 l_slh_id,
2663 p_chr_id);
2664 if( l_detail_count > 1 ) then
2665 x_return_status := OKL_API.g_ret_sts_error;
2666 OKL_API.SET_MESSAGE( p_app_name => g_app_name
2667 , p_msg_name => 'OKL_LLA_PYMTS_FEE_PERIOD');
2668 raise OKL_API.G_EXCEPTION_ERROR;
2669 end if;
2670 end if;
2671
2672
2673 --Bug# 13973475: Do not allow deletion of Variable Interest Schedule for
2674 -- contracts with Interest Calculation Basis as FLOAT
2675 l_stream_type_purpose := get_payment_type(l_slh_id);
2676 IF (l_stream_type_purpose = 'VARIABLE_INTEREST_SCHEDULE') THEN
2677
2678 OKL_K_RATE_PARAMS_PVT.get_product(
2679 p_api_version => p_api_version,
2680 p_init_msg_list => p_init_msg_list,
2681 x_return_status => x_return_status,
2682 x_msg_count => x_msg_count,
2683 x_msg_data => x_msg_data,
2684 p_khr_id => p_chr_id,
2685 x_pdt_parameter_rec => l_pdt_parameter_rec);
2686
2687 IF (l_pdt_parameter_rec.interest_calculation_basis = 'FLOAT') THEN
2688
2689 l_detail_count := get_sll_count(l_rgp_id,
2690 l_slh_id,
2691 p_chr_id);
2692 if( l_detail_count < 1 ) then
2693 x_return_status := OKL_API.g_ret_sts_error;
2694 OKL_API.SET_MESSAGE(p_app_name => g_app_name
2695 ,p_msg_name => 'OKL_LA_VAR_INT_SCHED_DEL');
2696 raise OKL_API.G_EXCEPTION_ERROR;
2697 end if;
2698
2699 END IF;
2700 END IF;
2701 --Bug# 13973475
2702
2703 --Added p_update_type by bkatraga for bug 13478101
2704 calculate_details(
2705 p_api_version => p_api_version,
2706 p_init_msg_list => p_init_msg_list,
2707 x_return_status => x_return_status,
2708 x_msg_count => x_msg_count,
2709 x_msg_data => x_msg_data,
2710 p_chr_id => p_chr_id,
2711 p_rgp_id => l_rgp_id,
2712 p_slh_id => l_slh_id,
2713 structure => p_pym_hdr_rec.STRUCTURE,
2714 frequency => p_pym_hdr_rec.FREQUENCY,
2715 arrears => p_pym_hdr_rec.ARREARS,
2716 p_update_type => l_update_type);
2717
2718
2719 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2720 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2721 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2722 RAISE OKL_API.G_EXCEPTION_ERROR;
2723 END IF;
2724
2725 lx_rulv_tbl(1).id := l_slh_id;
2726 lx_rulv_tbl(1).rgp_id := l_rgp_id;
2727 lx_rulv_tbl(1).dnz_chr_id := p_chr_id;
2728
2729 x_rulv_tbl := lx_rulv_tbl;
2730
2731 -- R12B Authoring OA Migration
2732 IF (l_upfront_tax_pymt_yn = OKL_API.G_TRUE) THEN
2733 IF (l_contract_rec.sts_code IN ('PASSED','COMPLETE','APPROVED','PENDING_APPROVAL')) THEN
2734
2735 OKL_LA_PAYMENTS_PVT.process_upfront_tax_pymt(
2736 p_api_version => p_api_version,
2737 p_init_msg_list => p_init_msg_list,
2738 x_return_status => x_return_status,
2739 x_msg_count => x_msg_count,
2740 x_msg_data => x_msg_data,
2741 p_chr_id => p_chr_id);
2742
2743 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2744 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2745 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2746 RAISE OKL_API.G_EXCEPTION_ERROR;
2747 END IF;
2748
2749 END IF;
2750 END IF;
2751
2752 -- Bug# 7440232
2753 -- Delete Interest Rate payments for FIXED/STREAMS
2754 -- Loans when all Principal payments are deleted
2755 OKL_LA_PAYMENTS_PVT.delete_interest_rate_params(
2756 p_api_version => p_api_version,
2757 p_init_msg_list => p_init_msg_list,
2758 x_return_status => x_return_status,
2759 x_msg_count => x_msg_count,
2760 x_msg_data => x_msg_data,
2761 p_chr_id => p_chr_id);
2762
2763 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2764 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2765 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2766 RAISE OKL_API.G_EXCEPTION_ERROR;
2767 END IF;
2768
2769 --Call End Activity
2770 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2771 x_msg_data => x_msg_data);
2772
2773 EXCEPTION
2774 when OKL_API.G_EXCEPTION_ERROR then
2775 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2776 p_api_name => l_api_name,
2777 p_pkg_name => G_PKG_NAME,
2778 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2779 x_msg_count => x_msg_count,
2780 x_msg_data => x_msg_data,
2781 p_api_type => G_API_TYPE);
2782
2783 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
2784 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2785 p_api_name => l_api_name,
2786 p_pkg_name => G_PKG_NAME,
2787 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2788 x_msg_count => x_msg_count,
2789 x_msg_data => x_msg_data,
2790 p_api_type => G_API_TYPE);
2791
2792 when OTHERS then
2793 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2794 p_api_name => l_api_name,
2795 p_pkg_name => G_PKG_NAME,
2796 p_exc_name => 'OTHERS',
2797 x_msg_count => x_msg_count,
2798 x_msg_data => x_msg_data,
2799 p_api_type => G_API_TYPE);
2800
2801 END process_payment;
2802
2803
2804 PROCEDURE variable_interest_payment(
2805 p_api_version IN NUMBER,
2806 p_init_msg_list IN VARCHAR2,
2807 x_return_status OUT NOCOPY VARCHAR2,
2808 x_msg_count OUT NOCOPY NUMBER,
2809 x_msg_data OUT NOCOPY VARCHAR2,
2810 p_chr_id IN NUMBER,
2811 x_rulv_tbl OUT NOCOPY rulv_tbl_type
2812 ) IS
2813 i NUMBER := 0;
2814 l_ak_prompt AK_ATTRIBUTES_VL.attribute_label_long%TYPE;
2815
2816 l_rulv_rec OKL_RULE_PUB.rulv_rec_type;
2817 lx_rulv_rec OKL_RULE_PUB.rulv_rec_type;
2818 -- x_rulv_tbl OKL_RULE_PUB.RULV_TBL_TYPE;
2819
2820 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2821 l_api_name CONSTANT VARCHAR2(30) := 'VARIABLE_INTEREST_PAYMENT';
2822 l_api_version CONSTANT NUMBER := 1.0;
2823
2824 l_payment_id NUMBER;
2825 l_pym_hdr_rec PYM_HDR_REC_TYPE;
2826 l_pym_tbl PYM_TBL_TYPE;
2827
2828 l_flag VARCHAR2(1) := 'N';
2829 l_term okl_k_headers.term_duration%TYPE;
2830
2831 l_pym_level VARCHAR2(30) := 'HEADER';
2832 l_slh_id OKC_RULES_V.ID%TYPE := OKL_API.G_MISS_NUM;
2833 l_rgp_id OKC_RULE_GROUPS_V.ID%TYPE := OKL_API.G_MISS_NUM;
2834 l_cle_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
2835
2836 -- x_return_status varchar2(1);
2837 x_primary_sty_id okl_strm_type_b.ID%TYPE;
2838
2839 /*
2840 *CURSOR VIR_PAYMENT (P_CHR_ID IN NUMBER) IS
2841 *SELECT 'Y', K.TERM_DURATION
2842 *FROM OKC_RULES_B IVAR, OKC_RULES_B INTP, OKC_RULE_GROUPS_B RG, OKL_K_HEADERS K
2843 *WHERE
2844 *IVAR.RULE_INFORMATION_CATEGORY = 'LAIVAR' AND IVAR.RULE_INFORMATION1 = 'FLOAT' AND
2845 *INTP.RULE_INFORMATION_CATEGORY = 'LAINTP' AND INTP.RULE_INFORMATION1 = 'Y' AND
2846 *RG.ID = IVAR.RGP_ID AND RG.RGD_CODE = 'LAIIND' AND RG.ID = INTP.RGP_ID
2847 *AND K.ID = P_CHR_ID AND RG.DNZ_CHR_ID = P_CHR_ID AND RG.CHR_ID = P_CHR_ID;
2848 */
2849
2850 CURSOR VIR_PAYMENT (P_CHR_ID IN NUMBER) IS
2851 SELECT IVAR.RULE_INFORMATION1 var_method,
2852 K.DEAL_TYPE deal_type,
2853 ICLC.RULE_INFORMATION5 calc_method,
2854 K.TERM_DURATION
2855 FROM
2856 OKC_RULES_B IVAR,
2857 OKC_RULES_B INTP,
2858 OKC_RULES_B ICLC,
2859 OKC_RULE_GROUPS_B RG,
2860 OKL_K_HEADERS K
2861 WHERE
2862 IVAR.RULE_INFORMATION_CATEGORY = 'LAIVAR'
2863 AND INTP.RULE_INFORMATION_CATEGORY = 'LAINTP'
2864 AND ICLC.RULE_INFORMATION_CATEGORY = 'LAICLC'
2865 AND INTP.RULE_INFORMATION1 = 'Y'
2866 AND RG.ID = IVAR.RGP_ID
2867 AND RG.RGD_CODE = 'LAIIND'
2868 AND RG.ID = INTP.RGP_ID
2869 AND RG.ID = ICLC.RGP_ID
2870 AND RG.CLE_ID IS NULL
2871 AND K.ID = P_CHR_ID
2872 AND RG.DNZ_CHR_ID = P_CHR_ID
2873 AND RG.CHR_ID = P_CHR_ID;
2874
2875 l_deal_type OKL_K_HEADERS.deal_type%TYPE;
2876 l_var_method OKC_RULES_B.RULE_INFORMATION1%TYPE;
2877 l_calc_method OKC_RULES_B.RULE_INFORMATION5%TYPE;
2878 l_data_found BOOLEAN;
2879
2880 /*
2881 cursor PAYMENT_TYPE is
2882 SELECT ID1
2883 FROM OKL_STRMTYP_SOURCE_V WHERE
2884 CODE = 'VARIABLE_INTEREST';
2885 */
2886
2887 begin
2888
2889 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2890 -- call START_ACTIVITY to create savepoint, check compatibility
2891 -- and initialize message list
2892 x_return_status := OKL_API.START_ACTIVITY(
2893 p_api_name => l_api_name,
2894 p_pkg_name => G_PKG_NAME,
2895 p_init_msg_list => p_init_msg_list,
2896 l_api_version => p_api_version,
2897 p_api_version => p_api_version,
2898 p_api_type => G_API_TYPE,
2899 x_return_status => x_return_status);
2900
2901 -- check if activity started successfully
2902 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2903 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2904 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2905 raise OKL_API.G_EXCEPTION_ERROR;
2906 END IF;
2907
2908
2909 --Bug 4018298
2910 OPEN VIR_PAYMENT(P_CHR_ID);
2911 FETCH VIR_PAYMENT INTO l_var_method, l_deal_type, l_calc_method, l_term;
2912 l_data_found := VIR_PAYMENT%FOUND;
2913 CLOSE VIR_PAYMENT;
2914 IF ( l_data_found
2915 AND ( l_deal_type = 'LOAN-REVOLVING' OR l_deal_type = 'LOAN' )
2916 AND l_var_method = 'FLOAT' AND l_calc_method = 'FORMULA') THEN
2917
2918 OKL_STREAMS_UTIL.get_primary_stream_type(
2919 p_khr_id => p_chr_id,
2920 p_primary_sty_purpose => 'VARIABLE_INTEREST',
2921 x_return_status => l_return_status,
2922 x_primary_sty_id => l_payment_id
2923 );
2924
2925 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2926 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2927 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2928 raise OKL_API.G_EXCEPTION_ERROR;
2929 END IF;
2930
2931 /*
2932 OPEN PAYMENT_TYPE;
2933 FETCH PAYMENT_TYPE INTO l_payment_id;
2934 CLOSE PAYMENT_TYPE;
2935 */
2936
2937 get_payment(
2938 p_api_version => p_api_version,
2939 p_init_msg_list => p_init_msg_list,
2940 x_return_status => x_return_status,
2941 x_msg_count => x_msg_count,
2942 x_msg_data => x_msg_data,
2943 p_chr_id => p_chr_id,
2944 p_service_fee_id => null,
2945 p_asset_id => null,
2946 p_payment_id => l_payment_id,
2947 x_pym_level => l_pym_level,
2948 x_slh_id => l_slh_id,
2949 x_rgp_id => l_rgp_id,
2950 x_cle_id => l_cle_id);
2951
2952
2953 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2954 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2955 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2956 raise OKL_API.G_EXCEPTION_ERROR;
2957 END IF;
2958
2959 if(l_slh_id is not null and l_slh_id <> OKL_API.G_MISS_NUM) then
2960 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2961 x_msg_data => x_msg_data);
2962 return;
2963 end if;
2964
2965 /*OPEN VIR_PAYMENT(P_CHR_ID);
2966 *FETCH VIR_PAYMENT INTO l_flag, l_term;
2967 *CLOSE VIR_PAYMENT;
2968 *
2969 *if(l_flag <> 'Y') then
2970 * OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2971 * x_msg_data => x_msg_data);
2972 * return;
2973 *end if;
2974 */
2975
2976 l_pym_hdr_rec.STRUCTURE := 0;
2977 l_pym_hdr_rec.STRUCTURE_NAME := NULL;
2978 l_pym_hdr_rec.FREQUENCY := 'M';
2979 l_pym_hdr_rec.FREQUENCY_NAME := NULL;
2980 l_pym_hdr_rec.ARREARS := 'Y';
2981 l_pym_hdr_rec.ARREARS_NAME := NULL;
2982
2983 l_pym_tbl(1).RULE_ID := NULL;
2984 l_pym_tbl(1).STUB_DAYS := NULL;
2985 l_pym_tbl(1).STUB_AMOUNT := NULL;
2986 -- l_pym_tbl(1).PERIOD := 1;
2987 l_pym_tbl(1).PERIOD := l_term;
2988 l_pym_tbl(1).AMOUNT := NULL;
2989 l_pym_tbl(1).SORT_DATE := NULL;
2990 l_pym_tbl(1).UPDATE_TYPE := 'CREATE';
2991
2992
2993 process_payment(
2994 p_api_version => p_api_version,
2995 p_init_msg_list => p_init_msg_list,
2996 x_return_status => x_return_status,
2997 x_msg_count => x_msg_count,
2998 x_msg_data => x_msg_data,
2999 p_chr_id => p_chr_id,
3000 p_service_fee_id => null,
3001 p_asset_id => null,
3002 p_payment_id => l_payment_id,
3003 p_pym_hdr_rec => l_pym_hdr_rec,
3004 p_pym_tbl => l_pym_tbl,
3005 p_update_type => 'VIR_PAYMENT',
3006 x_rulv_tbl => x_rulv_tbl);
3007
3008 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3009 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3010 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3011 raise OKL_API.G_EXCEPTION_ERROR;
3012 END IF;
3013 END IF;
3014
3015 -- ----------------------------------------
3016
3017 --Call End Activity
3018 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3019 x_msg_data => x_msg_data);
3020
3021
3022 EXCEPTION
3023 when OKL_API.G_EXCEPTION_ERROR then
3024 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3025 p_api_name => l_api_name,
3026 p_pkg_name => G_PKG_NAME,
3027 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3028 x_msg_count => x_msg_count,
3029 x_msg_data => x_msg_data,
3030 p_api_type => G_API_TYPE);
3031
3032 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
3033 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3034 p_api_name => l_api_name,
3035 p_pkg_name => G_PKG_NAME,
3036 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3037 x_msg_count => x_msg_count,
3038 x_msg_data => x_msg_data,
3039 p_api_type => G_API_TYPE);
3040
3041 when OTHERS then
3042 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3043 p_api_name => l_api_name,
3044 p_pkg_name => G_PKG_NAME,
3045 p_exc_name => 'OTHERS',
3046 x_msg_count => x_msg_count,
3047 x_msg_data => x_msg_data,
3048 p_api_type => G_API_TYPE);
3049
3050
3051 END variable_interest_payment;
3052
3053
3054
3055 PROCEDURE process_payment(
3056 p_api_version IN NUMBER,
3057 p_init_msg_list IN VARCHAR2,
3058 x_return_status OUT NOCOPY VARCHAR2,
3059 x_msg_count OUT NOCOPY NUMBER,
3060 x_msg_data OUT NOCOPY VARCHAR2,
3061 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
3062 p_service_fee_id IN OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM,
3063 p_asset_id IN OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM,
3064 p_payment_id IN OKL_STRMTYP_SOURCE_V.ID1%TYPE,
3065 p_update_type IN VARCHAR2,
3066 x_rulv_tbl OUT NOCOPY rulv_tbl_type) IS
3067
3068 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3069 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_PAYMENT2';
3070 l_api_version CONSTANT NUMBER := 1.0;
3071
3072 l_pym_level VARCHAR2(30) := 'HEADER';
3073 l_subline_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
3074 l_slh_id OKC_RULES_V.ID%TYPE := OKL_API.G_MISS_NUM;
3075 l_rgp_id OKC_RULE_GROUPS_V.ID%TYPE := OKL_API.G_MISS_NUM;
3076 l_cle_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
3077 l_stream_id OKL_STRMTYP_SOURCE_V.ID1%TYPE := OKL_API.G_MISS_NUM;
3078
3079 lx_rulv_tbl rulv_tbl_type;
3080
3081 -- R12B Authoring OA Migration
3082 l_upfront_tax_pymt_yn VARCHAR2(1);
3083
3084 CURSOR l_contract_csr(p_chr_id IN NUMBER) IS
3085 SELECT chrb.sts_code
3086 FROM okc_k_headers_b chrb
3087 WHERE chrb.id = p_chr_id;
3088
3089 l_contract_rec l_contract_csr%ROWTYPE;
3090
3091 begin
3092
3093 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3094 -- call START_ACTIVITY to create savepoint, check compatibility
3095 -- and initialize message list
3096 x_return_status := OKL_API.START_ACTIVITY(
3097 p_api_name => l_api_name,
3098 p_pkg_name => G_PKG_NAME,
3099 p_init_msg_list => p_init_msg_list,
3100 l_api_version => p_api_version,
3101 p_api_version => p_api_version,
3102 p_api_type => G_API_TYPE,
3103 x_return_status => x_return_status);
3104
3105 -- check if activity started successfully
3106 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3107 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3108 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3109 raise OKL_API.G_EXCEPTION_ERROR;
3110 END IF;
3111
3112 /* -- temp
3113 open get_dir;
3114 fetch get_dir into l_temp_dir;
3115 if get_dir%notfound then
3116 null;
3117 end if;
3118 close get_dir;
3119 */
3120 --fnd_file.put_names(l_log, l_out, l_temp_dir);
3121 -- temp
3122
3123 -- R12B Authoring OA Migration
3124 -- Check if the Payment is for an Upfront Tax Fee line
3125 l_upfront_tax_pymt_yn := OKL_API.G_FALSE;
3126 IF (p_service_fee_id IS NOT NULL) THEN
3127 l_upfront_tax_pymt_yn := is_upfront_tax_fee_payment(p_chr_id => p_chr_id,
3128 p_cle_id => p_service_fee_id);
3129
3130 IF (l_upfront_tax_pymt_yn = OKL_API.G_TRUE) THEN
3131 OPEN l_contract_csr(p_chr_id => p_chr_id);
3132 FETCH l_contract_csr INTO l_contract_rec;
3133 CLOSE l_contract_csr;
3134 END IF;
3135 END IF;
3136
3137 -- START: June 24, 2005 cklee: Modification for okl.h Sales Quote enhancement
3138 x_return_status := validate_capitalize_dp
3139 (p_asset_id => p_asset_id,
3140 p_service_fee_id => p_service_fee_id,
3141 p_payment_id => p_payment_id);
3142 --- Store the highest degree of error
3143 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3144 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3145 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3146 raise OKL_API.G_EXCEPTION_ERROR;
3147 END IF;
3148 -- END: June 24, 2005 cklee: Modification for okl.h Sales Quote enhancement
3149
3150 /* -- 4542290 Do not create var interest schedules. Will do from
3151 contract creation API
3152 if(p_update_type is not null and p_update_type = 'VIR_PAYMENT') then
3153 variable_interest_payment(
3154 p_api_version => p_api_version,
3155 p_init_msg_list => p_init_msg_list,
3156 x_return_status => x_return_status,
3157 x_msg_count => x_msg_count,
3158 x_msg_data => x_msg_data,
3159 p_chr_id => p_chr_id,
3160 x_rulv_tbl => lx_rulv_tbl
3161 );
3162
3163 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3164 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3165 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3166 raise OKL_API.G_EXCEPTION_ERROR;
3167 END IF;
3168 /*
3169 lx_rulv_tbl(1).id := l_slh_id;
3170 lx_rulv_tbl(1).rgp_id := l_rgp_id;
3171 lx_rulv_tbl(1).dnz_chr_id := p_chr_id;
3172 */
3173
3174 /* - 4542290
3175 x_rulv_tbl := lx_rulv_tbl;
3176
3177 --Call End Activity
3178 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3179 x_msg_data => x_msg_data);
3180 return;
3181
3182 end if; */
3183
3184 -- else
3185
3186 get_payment(
3187 p_api_version => p_api_version,
3188 p_init_msg_list => p_init_msg_list,
3189 x_return_status => x_return_status,
3190 x_msg_count => x_msg_count,
3191 x_msg_data => x_msg_data,
3192 p_chr_id => p_chr_id,
3193 p_service_fee_id => p_service_fee_id,
3194 p_asset_id => p_asset_id,
3195 p_payment_id => p_payment_id,
3196 x_pym_level => l_pym_level,
3197 x_slh_id => l_slh_id,
3198 x_rgp_id => l_rgp_id,
3199 x_cle_id => l_cle_id);
3200
3201
3202 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3203 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3204 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3205 raise OKL_API.G_EXCEPTION_ERROR;
3206 END IF;
3207
3208 --Bug# 4959361
3209 IF l_cle_id IS NOT NULL THEN
3210 OKL_LLA_UTIL_PVT.check_line_update_allowed
3211 (p_api_version => p_api_version,
3212 p_init_msg_list => p_init_msg_list,
3213 x_return_status => x_return_status,
3214 x_msg_count => x_msg_count,
3215 x_msg_data => x_msg_data,
3216 p_cle_id => l_cle_id);
3217
3218 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3219 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3220 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3221 RAISE OKL_API.G_EXCEPTION_ERROR;
3222 END IF;
3223 END IF;
3224 --Bug# 4959361
3225
3226 if(p_update_type is not null and p_update_type = 'DELETE') then
3227 if(l_slh_id is not null and l_slh_id <> OKL_API.G_MISS_NUM
3228 and l_rgp_id is not null and l_rgp_id <> OKL_API.G_MISS_NUM) then
3229
3230 OKL_PAYMENT_APPLICATION_PUB.delete_payment(
3231 p_api_version => p_api_version,
3232 p_init_msg_list => p_init_msg_list,
3233 x_return_status => x_return_status,
3234 x_msg_count => x_msg_count,
3235 x_msg_data => x_msg_data,
3236 p_chr_id => p_chr_id,
3237 p_rgp_id => l_rgp_id,
3238 p_rule_id => l_slh_id);
3239
3240 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3241 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3242 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3243 raise OKL_API.G_EXCEPTION_ERROR;
3244 END IF;
3245 else
3246 x_return_status := OKL_API.g_ret_sts_error;
3247 OKL_API.SET_MESSAGE( p_app_name => g_app_name
3248 , p_msg_name => 'OKL_LLA_PMT_SELECT');
3249 raise OKL_API.G_EXCEPTION_ERROR;
3250 end if;
3251 elsif(p_update_type is not null and p_update_type = 'APPLY') then
3252 if(p_chr_id is not null and p_chr_id <> OKL_API.G_MISS_NUM
3253 and p_payment_id is not null and p_payment_id <> OKL_API.G_MISS_NUM) then
3254 l_stream_id := to_char(p_payment_id);
3255 if is_prop_tax_payment(l_stream_id) then
3256 OKL_PAYMENT_APPLICATION_PVT.apply_propery_tax_payment(
3257 p_api_version => p_api_version,
3258 p_init_msg_list => p_init_msg_list,
3259 x_return_status => x_return_status,
3260 x_msg_count => x_msg_count,
3261 x_msg_data => x_msg_data,
3262 p_chr_id => p_chr_id,
3263 p_stream_id => l_stream_id);
3264 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3265 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3266 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3267 raise OKL_API.G_EXCEPTION_ERROR;
3268 END IF;
3269 /* Manu 13-Sep-2004. For Rollover Fee Payment */
3270 -- start: cklee: 6/22/05 -- okl.h
3271 -- elsif is_rollover_fee_payment(p_service_fee_id) then
3272 elsif is_eligible_fee_payment(p_service_fee_id) then
3273 -- end: cklee: 6/22/05 -- okl.h
3274 -- OKL_PAYMENT_APPLICATION_PVT.apply_rollover_fee_payment(
3275 OKL_PAYMENT_APPLICATION_PVT.apply_eligible_fee_payment(
3276 -- end: cklee: 6/22/05 -- okl.h
3277 p_api_version => p_api_version,
3278 p_init_msg_list => p_init_msg_list,
3279 x_return_status => x_return_status,
3280 x_msg_count => x_msg_count,
3281 x_msg_data => x_msg_data,
3282 p_chr_id => p_chr_id,
3283 p_kle_id => p_service_fee_id,
3284 p_stream_id => l_stream_id);
3285 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3286 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3287 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3288 raise OKL_API.G_EXCEPTION_ERROR;
3289 END IF;
3290 else
3291 OKL_PAYMENT_APPLICATION_PUB.apply_payment(
3292 p_api_version => p_api_version,
3293 p_init_msg_list => p_init_msg_list,
3294 x_return_status => x_return_status,
3295 x_msg_count => x_msg_count,
3296 x_msg_data => x_msg_data,
3297 p_chr_id => p_chr_id,
3298 p_stream_id => l_stream_id);
3299 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3300 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3301 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3302 raise OKL_API.G_EXCEPTION_ERROR;
3303 END IF;
3304 end if;
3305 else
3306 x_return_status := OKL_API.g_ret_sts_error;
3307 OKL_API.SET_MESSAGE( p_app_name => g_app_name
3308 , p_msg_name => 'OKL_LLA_PMT_SELECT');
3309 raise OKL_API.G_EXCEPTION_ERROR;
3310 end if;
3311 elsif(p_update_type is not null and p_update_type = 'CALCULATE') then
3312 if(l_slh_id is not null and l_slh_id <> OKL_API.G_MISS_NUM
3313 and l_rgp_id is not null and l_rgp_id <> OKL_API.G_MISS_NUM) then
3314
3315 --Bug# 10257857: Modified call to send in arrears as OKL_API.G_MISS_CHAR
3316 -- so that it does not get updated to NULL
3317 calculate_details(
3318 p_api_version => p_api_version,
3319 p_init_msg_list => p_init_msg_list,
3320 x_return_status => x_return_status,
3321 x_msg_count => x_msg_count,
3322 x_msg_data => x_msg_data,
3323 p_chr_id => p_chr_id,
3324 p_rgp_id => l_rgp_id,
3325 p_slh_id => l_slh_id,
3326 structure => null,
3327 frequency => null,
3328 arrears => OKL_API.G_MISS_CHAR);
3329
3330 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3331 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3332 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3333 RAISE OKL_API.G_EXCEPTION_ERROR;
3334 END IF;
3335
3336 else
3337 x_return_status := OKL_API.g_ret_sts_error;
3338 OKL_API.SET_MESSAGE( p_app_name => g_app_name
3339 , p_msg_name => 'OKL_LLA_PMT_SELECT');
3340 raise OKL_API.G_EXCEPTION_ERROR;
3341 end if;
3342 end if;
3343
3344 -- end if;
3345
3346 lx_rulv_tbl(1).id := l_slh_id;
3347 lx_rulv_tbl(1).rgp_id := l_rgp_id;
3348 lx_rulv_tbl(1).dnz_chr_id := p_chr_id;
3349
3350 x_rulv_tbl := lx_rulv_tbl;
3351
3352 -- R12B Authoring OA Migration
3353 IF (l_upfront_tax_pymt_yn = OKL_API.G_TRUE) THEN
3354 IF (l_contract_rec.sts_code IN ('PASSED','COMPLETE','APPROVED','PENDING_APPROVAL')) THEN
3355
3356 OKL_LA_PAYMENTS_PVT.process_upfront_tax_pymt(
3357 p_api_version => p_api_version,
3358 p_init_msg_list => p_init_msg_list,
3359 x_return_status => x_return_status,
3360 x_msg_count => x_msg_count,
3361 x_msg_data => x_msg_data,
3362 p_chr_id => p_chr_id);
3363
3364 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3365 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3366 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3367 RAISE OKL_API.G_EXCEPTION_ERROR;
3368 END IF;
3369
3370 END IF;
3371 END IF;
3372
3373 -- Bug# 7440232
3374 -- Delete Interest Rate payments for FIXED/STREAMS
3375 -- Loans when all Principal payments are deleted
3376 OKL_LA_PAYMENTS_PVT.delete_interest_rate_params(
3377 p_api_version => p_api_version,
3378 p_init_msg_list => p_init_msg_list,
3379 x_return_status => x_return_status,
3380 x_msg_count => x_msg_count,
3381 x_msg_data => x_msg_data,
3382 p_chr_id => p_chr_id);
3383
3384 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3385 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3386 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3387 RAISE OKL_API.G_EXCEPTION_ERROR;
3388 END IF;
3389
3390 --Call End Activity
3391 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3392 x_msg_data => x_msg_data);
3393
3394 EXCEPTION
3395 when OKL_API.G_EXCEPTION_ERROR then
3396 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3397 p_api_name => l_api_name,
3398 p_pkg_name => G_PKG_NAME,
3399 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3400 x_msg_count => x_msg_count,
3401 x_msg_data => x_msg_data,
3402 p_api_type => G_API_TYPE);
3403
3404 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
3405 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3406 p_api_name => l_api_name,
3407 p_pkg_name => G_PKG_NAME,
3408 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3409 x_msg_count => x_msg_count,
3410 x_msg_data => x_msg_data,
3411 p_api_type => G_API_TYPE);
3412
3413 when OTHERS then
3414 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3415 p_api_name => l_api_name,
3416 p_pkg_name => G_PKG_NAME,
3417 p_exc_name => 'OTHERS',
3418 x_msg_count => x_msg_count,
3419 x_msg_data => x_msg_data,
3420 p_api_type => G_API_TYPE);
3421
3422
3423 END process_payment;
3424
3425 PROCEDURE delete_payment(
3426 p_api_version IN NUMBER,
3427 p_init_msg_list IN VARCHAR2,
3428 x_return_status OUT NOCOPY VARCHAR2,
3429 x_msg_count OUT NOCOPY NUMBER,
3430 x_msg_data OUT NOCOPY VARCHAR2,
3431 p_del_pym_tbl IN pym_del_tbl_type,
3432 -- Bug #7498330
3433 p_source_trx IN VARCHAR2 DEFAULT 'NA') IS
3434
3435
3436 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3437 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PAYMENT';
3438 l_api_version CONSTANT NUMBER := 1.0;
3439
3440 i NUMBER := 0;
3441
3442 -- R12B Authoring OA Migration
3443 l_upfront_tax_pymt_yn VARCHAR2(1);
3444
3445 CURSOR l_contract_csr(p_chr_id IN NUMBER) IS
3446 SELECT chrb.sts_code
3447 FROM okc_k_headers_b chrb
3448 WHERE chrb.id = p_chr_id;
3449
3450 l_contract_rec l_contract_csr%ROWTYPE;
3451
3452 CURSOR l_pymt_cle_csr(p_rgp_id IN NUMBER) IS
3453 SELECT rgp.cle_id pymt_cle_id,
3454 cleb.cle_id parent_cle_id
3455 FROM okc_rule_groups_b rgp,
3456 okc_k_lines_b cleb
3457 WHERE rgp.id = p_rgp_id
3458 AND cleb.id = rgp.cle_id;
3459
3460 l_pymt_cle_rec l_pymt_cle_csr%ROWTYPE;
3461 l_cle_id OKC_K_LINES_B.id%TYPE;
3462 l_chr_id OKC_K_HEADERS_B.id%TYPE;
3463
3464 --Bug# 13973475
3465 l_pdt_parameter_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
3466 l_stream_type_purpose OKL_STRM_TYPE_B.stream_type_purpose%TYPE;
3467
3468 begin
3469
3470 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3471 -- call START_ACTIVITY to create savepoint, check compatibility
3472 -- and initialize message list
3473 x_return_status := OKL_API.START_ACTIVITY(
3474 p_api_name => l_api_name,
3475 p_pkg_name => G_PKG_NAME,
3476 p_init_msg_list => p_init_msg_list,
3477 l_api_version => l_api_version,
3478 p_api_version => p_api_version,
3479 p_api_type => G_API_TYPE,
3480 x_return_status => x_return_status);
3481
3482 -- check if activity started successfully
3483 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3484 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3485 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3486 raise OKL_API.G_EXCEPTION_ERROR;
3487 END IF;
3488
3489
3490 -- R12B Authoring OA Migration
3491 -- Check if the Payment is for an Upfront Tax Fee line
3492 l_upfront_tax_pymt_yn := OKL_API.G_FALSE;
3493
3494 if (p_del_pym_tbl.count > 0) then
3495 i := p_del_pym_tbl.FIRST;
3496 loop
3497 --bug # 7498330 added check for source trx is Termination (TQ)
3498 --added by rkuttiya
3499 IF p_source_trx <> 'TQ' THEN
3500 if( is_ppd_payment(p_del_pym_tbl(i).slh_id)) then
3501 x_return_status := OKL_API.g_ret_sts_error;
3502 OKL_API.SET_MESSAGE( p_app_name => g_app_name
3503 , p_msg_name => 'OKL_LA_PPD_PAYMENT');
3504 raise OKL_API.G_EXCEPTION_ERROR;
3505 end if;
3506 END IF;
3507
3508 -- R12B Authoring OA Migration
3509 -- Check if the Payment is for an Upfront Tax Fee line
3510 IF (p_del_pym_tbl(i).rgp_id IS NOT NULL) THEN
3511
3512 OPEN l_pymt_cle_csr(p_rgp_id => p_del_pym_tbl(i).rgp_id);
3513 FETCH l_pymt_cle_csr INTO l_pymt_cle_rec;
3514 CLOSE l_pymt_cle_csr;
3515
3516 l_cle_id := NVL(l_pymt_cle_rec.parent_cle_id,l_pymt_cle_rec.pymt_cle_id);
3517
3518 IF l_cle_id IS NOT NULL THEN
3519
3520 l_chr_id := p_del_pym_tbl(i).chr_id;
3521 IF (is_upfront_tax_fee_payment(p_chr_id => l_chr_id,
3522 p_cle_id => l_cle_id) = OKL_API.G_TRUE) THEN
3523 l_upfront_tax_pymt_yn := OKL_API.G_TRUE;
3524
3525 OPEN l_contract_csr(p_chr_id => l_chr_id);
3526 FETCH l_contract_csr INTO l_contract_rec;
3527 CLOSE l_contract_csr;
3528 END IF;
3529 END IF;
3530 END IF;
3531
3532 --Bug# 13973475: Do not allow deletion of Variable Interest Schedule for
3533 -- contracts with Interest Calculation Basis as FLOAT
3534 l_stream_type_purpose := get_payment_type(p_del_pym_tbl(i).slh_id);
3535 IF (l_stream_type_purpose = 'VARIABLE_INTEREST_SCHEDULE') THEN
3536
3537 OKL_K_RATE_PARAMS_PVT.get_product(
3538 p_api_version => p_api_version,
3539 p_init_msg_list => p_init_msg_list,
3540 x_return_status => x_return_status,
3541 x_msg_count => x_msg_count,
3542 x_msg_data => x_msg_data,
3543 p_khr_id => p_del_pym_tbl(i).chr_id,
3544 x_pdt_parameter_rec => l_pdt_parameter_rec);
3545
3546 IF (l_pdt_parameter_rec.interest_calculation_basis = 'FLOAT') THEN
3547 x_return_status := OKL_API.g_ret_sts_error;
3548 OKL_API.SET_MESSAGE(p_app_name => g_app_name
3549 ,p_msg_name => 'OKL_LA_VAR_INT_SCHED_DEL');
3550 RAISE OKL_API.G_EXCEPTION_ERROR;
3551 END IF;
3552 END IF;
3553 --Bug# 13973475
3554
3555 OKL_PAYMENT_APPLICATION_PUB.delete_payment(
3556 p_api_version => p_api_version,
3557 p_init_msg_list => p_init_msg_list,
3558 x_return_status => x_return_status,
3559 x_msg_count => x_msg_count,
3560 x_msg_data => x_msg_data,
3561 p_chr_id => p_del_pym_tbl(i).chr_id,
3562 p_rgp_id => p_del_pym_tbl(i).rgp_id,
3563 p_rule_id => p_del_pym_tbl(i).slh_id);
3564
3565
3566
3567 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3568 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3569 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3570 RAISE OKL_API.G_EXCEPTION_ERROR;
3571 END IF;
3572
3573 exit when (i >= p_del_pym_tbl.last);
3574 i:= p_del_pym_tbl.NEXT(i);
3575 end loop;
3576 end if;
3577
3578 -- R12B Authoring OA Migration
3579 IF (l_upfront_tax_pymt_yn = OKL_API.G_TRUE) THEN
3580 IF (l_contract_rec.sts_code IN ('PASSED','COMPLETE','APPROVED','PENDING_APPROVAL')) THEN
3581
3582 OKL_LA_PAYMENTS_PVT.process_upfront_tax_pymt(
3583 p_api_version => p_api_version,
3584 p_init_msg_list => p_init_msg_list,
3585 x_return_status => x_return_status,
3586 x_msg_count => x_msg_count,
3587 x_msg_data => x_msg_data,
3588 p_chr_id => l_chr_id);
3589
3590 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3591 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3592 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3593 RAISE OKL_API.G_EXCEPTION_ERROR;
3594 END IF;
3595
3596 END IF;
3597 END IF;
3598
3599 -- Bug# 7440232
3600 -- Delete Interest Rate payments for FIXED/STREAMS
3601 -- Loans when all Principal payments are deleted
3602 OKL_LA_PAYMENTS_PVT.delete_interest_rate_params(
3603 p_api_version => p_api_version,
3604 p_init_msg_list => p_init_msg_list,
3605 x_return_status => x_return_status,
3606 x_msg_count => x_msg_count,
3607 x_msg_data => x_msg_data,
3608 p_chr_id => p_del_pym_tbl(i).chr_id);
3609
3610 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3611 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3612 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3613 RAISE OKL_API.G_EXCEPTION_ERROR;
3614 END IF;
3615
3616 --Call End Activity
3617 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3618 x_msg_data => x_msg_data);
3619
3620
3621 EXCEPTION
3622 when OKL_API.G_EXCEPTION_ERROR then
3623 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3624 p_api_name => l_api_name,
3625 p_pkg_name => G_PKG_NAME,
3626 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3627 x_msg_count => x_msg_count,
3628 x_msg_data => x_msg_data,
3629 p_api_type => G_API_TYPE);
3630
3631 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
3632 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3633 p_api_name => l_api_name,
3634 p_pkg_name => G_PKG_NAME,
3635 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3636 x_msg_count => x_msg_count,
3637 x_msg_data => x_msg_data,
3638 p_api_type => G_API_TYPE);
3639
3640 when OTHERS then
3641 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3642 p_api_name => l_api_name,
3643 p_pkg_name => G_PKG_NAME,
3644 p_exc_name => 'OTHERS',
3645 x_msg_count => x_msg_count,
3646 x_msg_data => x_msg_data,
3647 p_api_type => G_API_TYPE);
3648
3649
3650 END delete_payment;
3651
3652 FUNCTION get_term(p_id IN NUMBER) RETURN NUMBER IS
3653 CURSOR contract_csr(p_contract_id NUMBER) IS
3654 SELECT TERM_DURATION
3655 FROM OKL_K_HEADERS
3656 WHERE ID = p_contract_id;
3657 begin
3658 for r IN contract_csr(p_id)
3659 LOOP
3660 return (r.TERM_DURATION);
3661 END LOOP;
3662 end;
3663
3664 PROCEDURE variable_interest_schedule(
3665 p_api_version IN NUMBER,
3666 p_init_msg_list IN VARCHAR2,
3667 x_return_status OUT NOCOPY VARCHAR2,
3668 x_msg_count OUT NOCOPY NUMBER,
3669 x_msg_data OUT NOCOPY VARCHAR2,
3670 p_chr_id IN NUMBER,
3671 x_rulv_tbl OUT NOCOPY rulv_tbl_type
3672 ) IS
3673 i NUMBER := 0;
3674
3675 l_rulv_rec OKL_RULE_PUB.rulv_rec_type;
3676 lx_rulv_rec OKL_RULE_PUB.rulv_rec_type;
3677 -- x_rulv_tbl OKL_RULE_PUB.RULV_TBL_TYPE;
3678
3679 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3680 l_api_name CONSTANT VARCHAR2(30) :='VARIABLE_INTEREST_SCHEDULE';
3681 l_api_version CONSTANT NUMBER := 1.0;
3682
3683 l_payment_id NUMBER;
3684 l_pym_hdr_rec PYM_HDR_REC_TYPE;
3685 l_pym_tbl PYM_TBL_TYPE;
3686
3687 l_term okl_k_headers.term_duration%TYPE;
3688
3689 l_pym_level VARCHAR2(30) := 'HEADER';
3690 l_slh_id OKC_RULES_V.ID%TYPE := OKL_API.G_MISS_NUM;
3691 l_rgp_id OKC_RULE_GROUPS_V.ID%TYPE := OKL_API.G_MISS_NUM;
3692 l_cle_id OKC_K_LINES_B.ID%TYPE := OKL_API.G_MISS_NUM;
3693
3694 x_primary_sty_id okl_strm_type_b.ID%TYPE;
3695
3696 l_deal_type OKL_K_HEADERS.deal_type%TYPE;
3697 l_interest_calculation_basis VARCHAR2(30);
3698 l_revenue_recognition_method VARCHAR2(30);
3699 l_data_found BOOLEAN;
3700 l_pdt_parameter_rec OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
3701
3702 -- 4895333
3703 CURSOR get_sts_code_csr(p_id NUMBER) IS
3704 SELECT sts_code
3705 FROM OKC_K_HEADERS_B
3706 WHERE ID = p_id;
3707 l_sts_code okc_k_headers_b.sts_code%type;
3708
3709 CURSOR var_int_sched_counter_csr(p_id NUMBER, p_stream_id NUMBER) IS
3710 SELECT COUNT(1) counter
3711 FROM OKC_RULES_B
3712 WHERE DNZ_CHR_ID = p_id
3713 AND rule_information_category = 'LASLH'
3714 AND object1_id1 = to_char(p_stream_id);
3715
3716 l_var_int_sched_counter NUMBER;
3717
3718 begin
3719 IF (G_DEBUG_ENABLED = 'Y') THEN
3720 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
3721 END IF;
3722
3723 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3724 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'In variable_interest_schedule... p_chr_id=' || p_chr_id);
3725 END IF;
3726 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3727 x_return_status := OKL_API.START_ACTIVITY(
3728 p_api_name => l_api_name,
3729 p_pkg_name => G_PKG_NAME,
3730 p_init_msg_list => p_init_msg_list,
3731 l_api_version => p_api_version,
3732 p_api_version => p_api_version,
3733 p_api_type => G_API_TYPE,
3734 x_return_status => x_return_status);
3735
3736 -- check if activity started successfully
3737 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3738 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3739 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3740 raise OKL_API.G_EXCEPTION_ERROR;
3741 END IF;
3742
3743
3744 /*
3745 OKL_K_RATE_PARAMS_PVT.get_product(
3746 p_api_version => p_api_version,
3747 p_init_msg_list => p_init_msg_list,
3748 x_return_status => x_return_status,
3749 x_msg_count => x_msg_count,
3750 x_msg_data => x_msg_data,
3751 p_khr_id => p_chr_id,
3752 x_pdt_parameter_rec => l_pdt_parameter_rec);
3753
3754 l_interest_calculation_basis :=l_pdt_parameter_rec.interest_calculation_basis;
3755 l_revenue_recognition_method :=l_pdt_parameter_rec.revenue_recognition_method;
3756 l_deal_type := l_pdt_parameter_rec.deal_type;
3757
3758 l_term := get_term(p_chr_id);
3759
3760 okl_debug_pub.logmessage('p_chr_id=' || p_chr_id);
3761 okl_debug_pub.logmessage('l_int_cal_basis=' || l_interest_calculation_basis);
3762 okl_debug_pub.logmessage('l_rev_rec_method=' || l_revenue_recognition_method);
3763 okl_debug_pub.logmessage('l_deal_type=' || l_deal_type);
3764 IF ( l_deal_type = 'LOAN' AND
3765 l_interest_calculation_basis IN ('FLOAT', 'CATCHUP/CLEANUP') ) OR
3766 ( l_deal_type = 'LOAN-REVOLVING' AND
3767 l_interest_calculation_basis = 'FLOAT' ) THEN
3768 */
3769
3770 -- 4722839
3771 l_term := get_term(p_chr_id);
3772
3773 OKL_STREAMS_UTIL.get_primary_stream_type(
3774 p_khr_id => p_chr_id,
3775 p_primary_sty_purpose => 'VARIABLE_INTEREST_SCHEDULE',
3776 x_return_status => l_return_status,
3777 x_primary_sty_id => l_payment_id
3778 );
3779
3780 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3781 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3782 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3783 raise OKL_API.G_EXCEPTION_ERROR;
3784 END IF;
3785
3786 -- 4895333
3787 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3788 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_payment_id=' || l_payment_id);
3789 END IF;
3790 FOR r IN get_sts_code_csr(p_chr_id)
3791 LOOP
3792 l_sts_code := r.sts_code;
3793 END LOOP;
3794 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3795 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_sts_code=' || l_sts_code);
3796
3797 END IF;
3798 FOR r_var_int_sched_counter_csr IN
3799 var_int_sched_counter_csr(p_chr_id, l_payment_id)
3800 LOOP
3801 l_var_int_sched_counter := r_var_int_sched_counter_csr.counter;
3802 END LOOP;
3803
3804 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3805 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_var_int_sched_counter=' || l_var_int_sched_counter);
3806 END IF;
3807 -- 4895333
3808 IF (l_var_int_sched_counter < 1) AND (l_sts_code <> 'BOOKED') THEN
3809 get_payment(
3810 p_api_version => p_api_version,
3811 p_init_msg_list => p_init_msg_list,
3812 x_return_status => x_return_status,
3813 x_msg_count => x_msg_count,
3814 x_msg_data => x_msg_data,
3815 p_chr_id => p_chr_id,
3816 p_service_fee_id => null,
3817 p_asset_id => null,
3818 p_payment_id => l_payment_id,
3819 x_pym_level => l_pym_level,
3820 x_slh_id => l_slh_id,
3821 x_rgp_id => l_rgp_id,
3822 x_cle_id => l_cle_id);
3823
3824
3825 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3826 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3827 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3828 raise OKL_API.G_EXCEPTION_ERROR;
3829 END IF;
3830
3831 if(l_slh_id is not null and l_slh_id <> OKL_API.G_MISS_NUM) then
3832 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3833 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_slh_id null... cant create...');
3834 END IF;
3835 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3836 x_msg_data => x_msg_data);
3837 return;
3838 end if;
3839
3840 l_pym_hdr_rec.STRUCTURE := 0;
3841 l_pym_hdr_rec.STRUCTURE_NAME := NULL;
3842 l_pym_hdr_rec.FREQUENCY := 'M';
3843 l_pym_hdr_rec.FREQUENCY_NAME := NULL;
3844 l_pym_hdr_rec.ARREARS := 'Y';
3845 l_pym_hdr_rec.ARREARS_NAME := NULL;
3846
3847 l_pym_tbl(1).RULE_ID := NULL;
3848 l_pym_tbl(1).STUB_DAYS := NULL;
3849 l_pym_tbl(1).STUB_AMOUNT := NULL;
3850 --l_pym_tbl(1).PERIOD := 1;
3851 l_pym_tbl(1).PERIOD := l_term;
3852 -- 4722839
3853 l_pym_tbl(1).AMOUNT := 0;
3854 l_pym_tbl(1).SORT_DATE := NULL;
3855 l_pym_tbl(1).UPDATE_TYPE := 'CREATE';
3856
3857
3858 process_payment(
3859 p_api_version => p_api_version,
3860 p_init_msg_list => p_init_msg_list,
3861 x_return_status => x_return_status,
3862 x_msg_count => x_msg_count,
3863 x_msg_data => x_msg_data,
3864 p_chr_id => p_chr_id,
3865 p_service_fee_id => null,
3866 p_asset_id => null,
3867 p_payment_id => l_payment_id,
3868 p_pym_hdr_rec => l_pym_hdr_rec,
3869 p_pym_tbl => l_pym_tbl,
3870 p_update_type => 'VIR_PAYMENT',
3871 x_rulv_tbl => x_rulv_tbl);
3872
3873 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3874 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3875 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3876 raise OKL_API.G_EXCEPTION_ERROR;
3877 END IF;
3878
3879 END IF; -- if counter < 1
3880 /* END IF; */
3881
3882 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3883 x_msg_data => x_msg_data);
3884
3885 EXCEPTION
3886 when OKL_API.G_EXCEPTION_ERROR then
3887 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3888 p_api_name => l_api_name,
3889 p_pkg_name => G_PKG_NAME,
3890 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3891 x_msg_count => x_msg_count,
3892 x_msg_data => x_msg_data,
3893 p_api_type => G_API_TYPE);
3894
3895 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
3896 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3897 p_api_name => l_api_name,
3898 p_pkg_name => G_PKG_NAME,
3899 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3900 x_msg_count => x_msg_count,
3901 x_msg_data => x_msg_data,
3902 p_api_type => G_API_TYPE);
3903
3904 when OTHERS then
3905 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3906 p_api_name => l_api_name,
3907 p_pkg_name => G_PKG_NAME,
3908 p_exc_name => 'OTHERS',
3909 x_msg_count => x_msg_count,
3910 x_msg_data => x_msg_data,
3911 p_api_type => G_API_TYPE);
3912
3913
3914 END variable_interest_schedule;
3915
3916 --Bug# 6438785
3917 -- Update the start dates for payments when the Contract start date
3918 -- or Line start date is changed.
3919 PROCEDURE update_pymt_start_date(
3920 p_api_version IN NUMBER,
3921 p_init_msg_list IN VARCHAR2,
3922 x_return_status OUT NOCOPY VARCHAR2,
3923 x_msg_count OUT NOCOPY NUMBER,
3924 x_msg_data OUT NOCOPY VARCHAR2,
3925 p_chr_id IN NUMBER,
3926 p_cle_id IN NUMBER DEFAULT NULL) IS
3927
3928 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3929 l_api_name CONSTANT VARCHAR2(30) :='UPDATE_PYMT_START_DATE';
3930 l_api_version CONSTANT NUMBER := 1.0;
3931
3932 CURSOR c_all_pymts_csr(p_chr_id NUMBER) IS
3933 SELECT rgp.id rgp_lalevl_id,
3934 slh.id rul_laslh_id,
3935 rgp.cle_id
3936 FROM okc_rule_groups_b rgp,
3937 okc_rules_b slh,
3938 okc_k_lines_b cle,
3939 okc_statuses_b sts
3940 WHERE rgp.dnz_chr_id = p_chr_id
3941 AND rgp.rgd_code = 'LALEVL'
3942 AND slh.rgp_id = rgp.id
3943 AND slh.dnz_chr_id = rgp.dnz_chr_id
3944 AND slh.rule_information_category = 'LASLH'
3945 AND cle.id (+) = rgp.cle_id
3946 AND cle.sts_code = sts.code (+)
3947 AND sts.ste_code (+) NOT IN ('EXPIRED','TERMINATED','CANCELLED');
3948
3949 CURSOR c_line_pymts_csr(p_cle_id NUMBER,
3950 p_chr_id NUMBER) IS
3951 SELECT rgp.id rgp_lalevl_id,
3952 slh.id rul_laslh_id,
3953 rgp.cle_id
3954 FROM okc_rule_groups_b rgp,
3955 okc_rules_b slh,
3956 okc_k_lines_b cle,
3957 okc_statuses_b sts
3958 WHERE rgp.dnz_chr_id = p_chr_id
3959 AND (rgp.cle_id = p_cle_id OR
3960 rgp.cle_id IN (SELECT cle_sub.id
3961 FROM okc_k_lines_b cle_sub
3962 WHERE cle_sub.cle_id = p_cle_id
3963 AND cle_sub.dnz_chr_id = p_chr_id))
3964 AND rgp.rgd_code = 'LALEVL'
3965 AND slh.rgp_id = rgp.id
3966 AND slh.dnz_chr_id = rgp.dnz_chr_id
3967 AND slh.rule_information_category = 'LASLH'
3968 AND cle.id = rgp.cle_id
3969 AND cle.sts_code = sts.code
3970 AND sts.ste_code NOT IN ('EXPIRED','TERMINATED','CANCELLED');
3971
3972 begin
3973 IF (G_DEBUG_ENABLED = 'Y') THEN
3974 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
3975 END IF;
3976
3977 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3978 x_return_status := OKL_API.START_ACTIVITY(
3979 p_api_name => l_api_name,
3980 p_pkg_name => G_PKG_NAME,
3981 p_init_msg_list => p_init_msg_list,
3982 l_api_version => p_api_version,
3983 p_api_version => p_api_version,
3984 p_api_type => G_API_TYPE,
3985 x_return_status => x_return_status);
3986
3987 -- check if activity started successfully
3988 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
3989 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3990 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
3991 raise OKL_API.G_EXCEPTION_ERROR;
3992 END IF;
3993
3994 IF p_cle_id IS NOT NULL THEN
3995
3996 FOR r_line_pymts_rec IN c_line_pymts_csr(p_chr_id => p_chr_id,
3997 p_cle_id => p_cle_id) LOOP
3998
3999 --Bug# 10257857: Modified call to send in arrears as OKL_API.G_MISS_CHAR
4000 -- so that it does not get updated to NULL
4001 calculate_details(
4002 p_api_version => p_api_version,
4003 p_init_msg_list => p_init_msg_list,
4004 x_return_status => x_return_status,
4005 x_msg_count => x_msg_count,
4006 x_msg_data => x_msg_data,
4007 p_chr_id => p_chr_id,
4008 p_rgp_id => r_line_pymts_rec.rgp_lalevl_id,
4009 p_slh_id => r_line_pymts_rec.rul_laslh_id,
4010 structure => NULL,
4011 frequency => NULL,
4012 arrears => OKL_API.G_MISS_CHAR,
4013 p_validate_date_yn => 'N');
4014
4015 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4016 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4017 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4018 RAISE OKL_API.G_EXCEPTION_ERROR;
4019 END IF;
4020
4021 END LOOP;
4022
4023 ELSE
4024
4025 FOR r_all_pymts_rec IN c_all_pymts_csr(p_chr_id => p_chr_id) LOOP
4026
4027 --Added p_update_type by bkatraga for bug 13478101
4028 --Bug# 10257857: Modified call to send in arrears as OKL_API.G_MISS_CHAR
4029 -- so that it does not get updated to NULL
4030 calculate_details(
4031 p_api_version => p_api_version,
4032 p_init_msg_list => p_init_msg_list,
4033 x_return_status => x_return_status,
4034 x_msg_count => x_msg_count,
4035 x_msg_data => x_msg_data,
4036 p_chr_id => p_chr_id,
4037 p_rgp_id => r_all_pymts_rec.rgp_lalevl_id,
4038 p_slh_id => r_all_pymts_rec.rul_laslh_id,
4039 structure => NULL,
4040 frequency => NULL,
4041 arrears => OKL_API.G_MISS_CHAR,
4042 p_validate_date_yn => 'N',
4043 p_update_type => 'UPDATE');
4044
4045 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4046 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4047 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4048 RAISE OKL_API.G_EXCEPTION_ERROR;
4049 END IF;
4050
4051 END LOOP;
4052 END IF;
4053
4054 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
4055 x_msg_data => x_msg_data);
4056
4057 EXCEPTION
4058 when OKL_API.G_EXCEPTION_ERROR then
4059 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4060 p_api_name => l_api_name,
4061 p_pkg_name => G_PKG_NAME,
4062 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4063 x_msg_count => x_msg_count,
4064 x_msg_data => x_msg_data,
4065 p_api_type => G_API_TYPE);
4066
4067 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
4068 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4069 p_api_name => l_api_name,
4070 p_pkg_name => G_PKG_NAME,
4071 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4072 x_msg_count => x_msg_count,
4073 x_msg_data => x_msg_data,
4074 p_api_type => G_API_TYPE);
4075
4076 when OTHERS then
4077 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
4078 p_api_name => l_api_name,
4079 p_pkg_name => G_PKG_NAME,
4080 p_exc_name => 'OTHERS',
4081 x_msg_count => x_msg_count,
4082 x_msg_data => x_msg_data,
4083 p_api_type => G_API_TYPE);
4084
4085
4086 END update_pymt_start_date;
4087
4088 END OKL_LA_PAYMENTS_PVT;