[Home] [Help]
PACKAGE BODY: APPS.OKL_LTE_INT_PVT
Source
1 PACKAGE BODY OKL_LTE_INT_PVT AS
2 /* $Header: OKLRLINB.pls 120.20.12010000.2 2008/11/18 20:59:33 cklee ship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.INTEREST';
5 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 L_LEVEL_PROCEDURE NUMBER;
7 IS_DEBUG_PROCEDURE_ON BOOLEAN;
8 -- End of wraper code generated automatically by Debug code generator
9
10 -- Bug 6472228 - Added constant for Late fee/interest calculation SGN_CODE
11 G_LATE_SGN_CODE CONSTANT VARCHAR2(10) := 'LATE_CALC';
12
13
14 PROCEDURE calculate_late_interest(
15 p_api_version IN NUMBER
16 ,p_init_msg_list IN VARCHAR2
17 ,x_return_status OUT NOCOPY VARCHAR2
18 ,x_msg_count OUT NOCOPY NUMBER
19 ,x_msg_data OUT NOCOPY VARCHAR2
20 ) IS
21
22 l_api_version CONSTANT NUMBER := 1;
23 l_api_name CONSTANT VARCHAR2(30) := 'calculate_late_interest';
24 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
25 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
26 l_init_msg_list VARCHAR2(1) ;
27 l_msg_count NUMBER ;
28
29 l_amt_applied AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%type;
30 l_due_date AR_PAYMENT_SCHEDULES_ALL.DUE_DATE%type;
31 l_khr_id OKL_CNSLD_AR_STRMS_V.KHR_ID%type;
32 l_apply_date AR_RECEIVABLE_APPLICATIONS_ALL.APPLY_DATE%TYPE;
33 l_cnsld_id OKL_CNSLD_AR_STRMS_V.ID%type;
34 l_amount NUMBER;
35 l_interest_amount NUMBER;
36
37 --start code pgomes 12/18/2002
38 l_func_currency okl_k_headers_full_v.currency_code%TYPE := okl_accounting_util.get_func_curr_code;
39 l_contract_currency okl_k_headers_full_v.currency_code%TYPE;
40 l_prev_contract_currency okl_k_headers_full_v.currency_code%TYPE;
41 l_late_policy okl_late_policies_v.name%TYPE;
42 l_prev_late_policy okl_late_policies_v.name%TYPE;
43
44 l_minimum_late_interest okl_late_policies_b.minimum_late_interest%TYPE;
45 l_maximum_late_interest okl_late_policies_b.maximum_late_interest%TYPE;
46 l_late_int_minimum_balance okl_late_policies_b.late_int_minimum_balance%TYPE;
47
48 l_currency_conversion_type okl_k_headers_full_v.currency_conversion_type%type;
49 l_currency_conversion_rate okl_k_headers_full_v.currency_conversion_rate%type;
50 l_currency_conversion_date okl_k_headers_full_v.currency_conversion_date%type;
51
52 l_last_updated_by NUMBER;
53 l_last_update_login NUMBER;
54 l_request_id NUMBER;
55 --end code pgomes 12/18/2002
56 l_prev_khr_id okl_k_headers.id%TYPE; --dkagrawa added for bug# 4728636
57
58 ------------------------------------------------------------
59 -- Declare variables to call Accounting Engine.
60 ------------------------------------------------------------
61 p_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
62
63 -- Variables for Late Interest rules Call
64 l_late_int_fixed_yn OKL_LATE_POLICIES_V.LATE_INT_FIXED_YN%TYPE;
65 l_late_int_rate OKL_LATE_POLICIES_V.LATE_INT_RATE%TYPE;
66 l_adder_rate OKL_LATE_POLICIES_V.ADDER_RATE%TYPE;
67 l_index_rate OKL_INDEX_VALUES.VALUE%TYPE;
68 l_days_in_year OKL_LATE_POLICIES_V.DAYS_IN_YEAR%TYPE;
69 l_days_calc NUMBER := 0;
70 l_held_until_date DATE;
71
72 l_sty_id OKL_STRM_TYPE_V.ID%TYPE;
73 l_stm_id OKL_STREAMS_V.ID%TYPE;
74 l_stream_purpose OKL_STRM_TYPE_V.stream_type_purpose%TYPE;
75 l_sec_stream_id OKL_CNSLD_AR_STRMS_V.ID%TYPE;
76 l_se_line_number OKL_STRM_ELEMENTS_V.SE_LINE_NUMBER%TYPE;
77 l_error_flag BOOLEAN := FALSE;
78
79 l_stmv_rec Okl_Streams_Pub.stmv_rec_type;
80 lx_stmv_rec Okl_Streams_Pub.stmv_rec_type;
81 l_init_stmv_rec Okl_Streams_Pub.stmv_rec_type;
82
83 l_selv_rec Okl_Sel_Pvt.selv_rec_type;
84 lx_selv_rec Okl_Sel_Pvt.selv_rec_type;
85 l_init_selv_rec Okl_Sel_Pvt.selv_rec_type;
86 l_sel_id Okl_strm_elements_v.sel_id%TYPE;
87
88 ------------------------------------------------------------
89 --Consolidated invoices for Late Interest Cursor
90 ------------------------------------------------------------
91 --rkuttiya modified this cursor for R12 B Billing Architecture
92 -- vdamerla: bug:6342067 : Modified cursor for per contract per invoice
93 -- gboomina Bug 6797022 - Start
94 -- Modified this cursor to pick contracts with no held until date defined
95 CURSOR l_late_invs_cur IS
96 SELECT LTE.name late_policy
97 , KHR.currency_code
98 , ARL.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID
99 , KHR.contract_number
100 , ARL.CONTRACT_ID CONTRACT_ID
101 , sum(NVL(ARL.AMOUNT_DUE_ORIGINAL,0)) AMOUNT_APPLIED
102 , max(greatest(TRUNC((FND_DATE.canonical_to_date(NVL(rul_hld.rule_information1,AR_PAY.DUE_DATE)))),TRUNC(AR_PAY.DUE_DATE))) DUE_DATE
103 , max(TRUNC(AR_REC.APPLY_DATE)) APPLY_DATE
104 ,max('AR-INVOICE') invoice_flag
105 FROM AR_RECEIVABLE_APPLICATIONS_ALL AR_REC
106 , AR_PAYMENT_SCHEDULES_ALL AR_PAY
107 , OKL_BPD_AR_INV_LINES_V ARL
108 , OKL_K_HEADERS_FULL_V KHR
109 , OKC_RULE_GROUPS_B RGP
110 , OKC_RULES_B RUL
111 , okc_rules_b rul_exm
112 ,okc_rules_b rul_hld
113 , OKL_LATE_POLICIES_V LTE
114 WHERE
115 AR_REC.APPLY_DATE > (AR_PAY.DUE_DATE + nvl(LTE.late_int_grace_period, 0))
116 AND ARL.RECEIVABLES_INVOICE_ID = AR_PAY.CUSTOMER_TRX_ID
117 AND AR_PAY.PAYMENT_SCHEDULE_ID = AR_REC.APPLIED_PAYMENT_SCHEDULE_ID
118 AND AR_PAY.CLASS = 'INV'
119 AND AR_PAY.status = 'CL'
120 AND AR_REC.STATUS = 'APP'
121 AND AR_REC.APPLICATION_TYPE = 'CASH'
122 AND ARL.CONTRACT_ID = khr.id
123 AND ARL.late_int_assess_date IS NULL
124 AND NVL(ARL.late_int_ass_yn, 'N') = 'N'
125 and khr.id = rgp.dnz_chr_id
126 and rgp.rgd_code = 'LALIGR'
127 and khr.id = rul.dnz_chr_id
128 and rgp.id = rul.rgp_id
129 and rul.rule_information_category = 'LALCIN'
130 and rul.rule_information1 = lte.id
131 and (lte.late_policy_type_code = 'LCT' or lte.late_policy_type_code = 'INT')
132 and khr.id = rul_exm.dnz_chr_id
133 and rgp.id = rul_exm.rgp_id
134 and rul_exm.rule_information_category = 'LALIEX'
135 and NVL(rul_exm.rule_information1, 'N') = 'N'
136 and not exists (select 1 from okl_strm_type_exempt_v sty_exm
137 where lte.id = sty_exm.lpo_id
138 and ARL.sty_id = sty_exm.sty_id
139 and NVL(sty_exm.late_policy_exempt_yn, 'N') = 'Y')
140 and khr.id = rul_hld.dnz_chr_id
141 and rgp.id = rul_hld.rgp_id
142 and rul_hld.rule_information_category = 'LAHUDT'
143 and TRUNC(NVL(FND_DATE.canonical_to_date(NVL(rul_hld.rule_information1,sysdate)), sysdate - 1)) < trunc(sysdate)
144 and ((TRUNC((FND_DATE.canonical_to_date(rul_hld.rule_information1))) IS NOT NULL
145 AND TRUNC((FND_DATE.canonical_to_date(rul_hld.rule_information1))) < TRUNC(AR_REC.APPLY_DATE))
146 OR TRUNC(AR_REC.APPLY_DATE) IS NULL
147 OR rul_hld.rule_information1 IS NULL)
148 group by
149 LTE.name
150 , KHR.currency_code
151 , KHR.contract_number
152 , ARL.CONTRACT_ID
153 , ARL.RECEIVABLES_INVOICE_ID
154 ,'AR-INVOICE'
155 union
156 SELECT LTE.name late_policy
157 , KHR.currency_code
158 , CNSLD.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID
159 , KHR.contract_number
160 , CNSLD.KHR_ID CONTRACT_ID
161 , sum(NVL(AR_REC.AMOUNT_APPLIED,0)) AMOUNT_APPLIED
162 , max(greatest(TRUNC((FND_DATE.canonical_to_date(NVL(rul_hld.rule_information1,AR_PAY.DUE_DATE)))),TRUNC(AR_PAY.DUE_DATE))) DUE_DATE
163 , max(TRUNC(AR_REC.APPLY_DATE)) APPLY_DATE
164 , max('CONS-INVOICE') invoice_flag
165 FROM AR_RECEIVABLE_APPLICATIONS_ALL AR_REC
166 , AR_PAYMENT_SCHEDULES_ALL AR_PAY
167 , OKL_CNSLD_AR_STRMS_B CNSLD
168 , OKL_K_HEADERS_FULL_V KHR
169 , OKC_RULE_GROUPS_B RGP
170 , OKC_RULES_B RUL
171 , okc_rules_b rul_exm
172 ,okc_rules_b rul_hld
173 , OKL_LATE_POLICIES_V LTE
174 WHERE
175 AR_REC.APPLY_DATE > (AR_PAY.DUE_DATE + nvl(LTE.late_int_grace_period, 0)) AND
176 CNSLD.RECEIVABLES_INVOICE_ID = AR_PAY.CUSTOMER_TRX_ID
177 AND AR_PAY.PAYMENT_SCHEDULE_ID = AR_REC.APPLIED_PAYMENT_SCHEDULE_ID
178 AND AR_PAY.CLASS = 'INV'
179 AND AR_PAY.status = 'CL'
180 AND AR_REC.STATUS = 'APP'
181 AND AR_REC.APPLICATION_TYPE = 'CASH'
182 and CNSLD.KHR_ID = khr.id
183 AND CNSLD.late_int_assess_date IS NULL
184 AND NVL(CNSLD.late_int_ass_yn, 'N') = 'N'
185 and khr.id = rgp.dnz_chr_id
186 and rgp.rgd_code = 'LALIGR'
187 and khr.id = rul.dnz_chr_id
188 and rgp.id = rul.rgp_id
189 and rul.rule_information_category = 'LALCIN'
190 and rul.rule_information1 = lte.id
191 and (lte.late_policy_type_code = 'LCT' or lte.late_policy_type_code = 'INT')
192 and khr.id = rul_exm.dnz_chr_id
193 and khr.authoring_org_id = MO_GLOBAL.get_current_org_id
194 and rgp.id = rul_exm.rgp_id
195 and rul_exm.rule_information_category = 'LALIEX'
196 and NVL(rul_exm.rule_information1, 'N') = 'N'
197 and not exists (select 1 from okl_strm_type_exempt_v sty_exm
198 where lte.id = sty_exm.lpo_id
199 and CNSLD.sty_id = sty_exm.sty_id
200 and NVL(sty_exm.late_policy_exempt_yn, 'N') = 'Y')
201 and khr.id = rul_hld.dnz_chr_id
202 and rgp.id = rul_hld.rgp_id
203 and rul_hld.rule_information_category = 'LAHUDT'
204 and TRUNC(NVL(FND_DATE.canonical_to_date(NVL(rul_hld.rule_information1,sysdate)), sysdate - 1)) < trunc(sysdate)
205 and ((TRUNC((FND_DATE.canonical_to_date(rul_hld.rule_information1))) IS NOT NULL
206 AND TRUNC((FND_DATE.canonical_to_date(rul_hld.rule_information1))) < TRUNC(AR_REC.APPLY_DATE))
207 OR TRUNC(AR_REC.APPLY_DATE) IS NULL
208 OR rul_hld.rule_information1 IS NULL)
209 group by LTE.name
210 , KHR.currency_code
211 , KHR.contract_number
212 , CNSLD.KHR_ID
213 , CNSLD.RECEIVABLES_INVOICE_ID
214 ,'CONS-INVOICE';
215 -- gboomina Bug 6797022 - End
216
217 ------------------------------------------------------------
218 -- Late Policy Cursor
219 ------------------------------------------------------------
220 CURSOR l_late_policy_cur(cp_name IN VARCHAR2) IS
221 SELECT LTE.LATE_POLICY_TYPE_CODE, LTE.LATE_INT_ALLOWED_YN, LTE.LATE_INT_FIXED_YN
222 , NVL(LTE.LATE_INT_RATE,0) LATE_INT_RATE
223 , NVL(LTE.ADDER_RATE,0) ADDER_RATE
224 , NVL(LTE.LATE_INT_GRACE_PERIOD,0) LATE_INT_GRACE_PERIOD
225 , NVL(LTE.LATE_INT_MINIMUM_BALANCE,0) LATE_INT_MINIMUM_BALANCE
226 , NVL(LTE.MINIMUM_LATE_INTEREST,0) MINIMUM_LATE_INTEREST
227 , NVL(LTE.MAXIMUM_LATE_INTEREST,9999999999) MAXIMUM_LATE_INTEREST
228 , NVL(IDX.value,0) INDEX_RATE
229 , NVL(LTE.DAYS_IN_YEAR, 'ACTUAL') DAYS_IN_YEAR
230 FROM OKL_LATE_POLICIES_V LTE
231 , OKL_INDEX_VALUES IDX
232 WHERE LTE.NAME = cp_name
233 AND LTE.idx_id = IDX.idx_id(+)
234 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(IDX.DATETIME_VALID, SYSDATE)) AND TRUNC(NVL(IDX.DATETIME_INVALID, SYSDATE));
235
236
237 ------------------------------------------------------------
238 -- Consolidated stream update Cursor
239 ------------------------------------------------------------
240
241 -- vdamerla: bug:6342067 : Get the late int data
242
243 -- cursor for consolidated invoices
244
245 CURSOR l_cons_lsm_cur(l_RECEIVABLES_INVOICE_ID IN NUMBER, l_khr_id in number) IS
246 SELECT lsm.id
247 , lsm.LATE_CHARGE_ASS_YN
248 , lsm.LATE_CHARGE_ASSESS_DATE
249 FROM OKL_CNSLD_AR_STRMS_B lsm,
250 OKL_CNSLD_AR_LINES_B lln,
251 OKL_CNSLD_AR_HDRS_B cnr,
252 okl_bpd_leasing_payment_trx_v lpt
253 WHERE
254 lpt.RECEIVABLES_INVOICE_ID=l_RECEIVABLES_INVOICE_ID
255 and cnr.id = lpt.consolidated_invoice_id
256 and lln.cnr_id = cnr.id
257 and lsm.lln_id = lln.id
258 and lsm.KHR_ID = l_khr_id
259 FOR UPDATE OF lsm.LATE_CHARGE_ASS_YN, lsm.LATE_CHARGE_ASSESS_DATE;
260
261
262 -- cursor for AR invoices
263 CURSOR l_AR_lsm_cur(l_id IN NUMBER,l_contract_id in number) IS
264 SELECT ID
265 , LATE_CHARGE_ASS_YN
266 , LATE_CHARGE_ASSESS_DATE
267 FROM OKL_BPD_AR_INV_LINES_V
268 WHERE RECEIVABLES_INVOICE_ID = l_id
269 AND CONTRACT_ID=l_contract_id
270 FOR UPDATE OF LATE_CHARGE_ASS_YN, LATE_CHARGE_ASSESS_DATE;
271
272
273 ------------------------------------------------------------
274 -- Transaction Number Cursor
275 ------------------------------------------------------------
276 CURSOR c_tran_num_csr IS
277 SELECT okl_sif_seq.nextval
278 FROM dual;
279
280 ------------------------------------------------------------
281 -- Stream Type Constants
282 ------------------------------------------------------------
283 cns_late_interest constant varchar2(50) := 'LATE_INTEREST';
284 cns_late_fee constant varchar2(50) := 'LATE_FEE';
285 cns_late_interest_payable constant varchar2(50) := 'INVESTOR_LATE_INTEREST_PAY';
286 cns_source_table constant varchar2(25) := 'OKL_CNSLD_AR_STRMS_V';
287
288 /* ------------------------------------------------------------
289 -- Stream Id Cursor
290 ------------------------------------------------------------
291 CURSOR l_sty_id_cur(cp_purpose IN VARCHAR2) IS
292 SELECT id FROM okl_strm_type_b where stream_type_purpose = cp_purpose;
293 */
294 ------------------------------------------------------------
295 -- Stream Cursor
296 ------------------------------------------------------------
297
298 -- vdamerla: bug:6342067 : Modified cursor for Billing impact chnages
299 -- cursor for AR invoices
300 CURSOR l_AR_stream_csr(cp_khr_id IN NUMBER
301 ,cp_sty_id IN NUMBER) IS
302 SELECT stm.id
303 FROM okl_streams_v stm
304 WHERE stm.khr_id = cp_khr_id
305 AND stm.sty_id = cp_sty_id
306 AND stm.say_code = 'CURR'
307 AND stm.active_yn = 'Y';
308
309 -- cursor for consolidated invoices
310 CURSOR l_cons_stream_csr(cp_khr_id IN NUMBER
311 ,cp_kle_id IN NUMBER
312 ,cp_sty_id IN NUMBER) IS
313 SELECT stm.id
314 FROM okl_streams_v stm
315 WHERE stm.khr_id = cp_khr_id
316 AND nvl(stm.kle_id, -99) = nvl(cp_kle_id, -99)
317 AND stm.sty_id = cp_sty_id
318 AND stm.say_code = 'CURR'
319 AND stm.active_yn = 'Y';
320
321
322
323
324 ------------------------------------------------------------
325 -- Stream Element Line Number Cursor
326 ------------------------------------------------------------
327 CURSOR l_stream_line_nbr_csr(cp_stm_id IN NUMBER) IS
328 SELECT nvl(max(se_line_number), 0) se_line_number
329 FROM okl_strm_elements_v
330 WHERE stm_id = cp_stm_id;
331
332 ------------------------------------------------------------
333 -- Securitized streams Cursor
334 ------------------------------------------------------------
335 --rkuttiya modified this cursor for R12 B Billing Architecture
336 --modified data elements, table name, where clause
337 -- vdamerla: bug:6342067 : Modified cursor for Billing impact chnages
338
339 CURSOR c_sec_strm_cons_csr(l_khr_id in number, l_cons_rec_inv_id IN NUMBER ) IS
340 select lsm.id cnsld_strm_id,
341 pol.khr_id,lsm.kle_id
342 from okl_cnsld_ar_strms_b lsm
343 , okl_cnsld_ar_hdrs_b cnr
344 , okl_cnsld_ar_lines_b lln
345 , okl_pool_contents_v pk
346 , okl_pools pol
347 where lsm.RECEIVABLES_INVOICE_ID = l_cons_rec_inv_id
348 and lln.cnr_id = cnr.id
349 and lsm.lln_id = lln.id
350 and lsm.khr_id = l_khr_id
351 and lsm.khr_id = pk.khr_id
352 and nvl(lsm.kle_id, -99) = nvl(pk.kle_id, -99)
353 and lsm.sty_id = pk.sty_id
354 and pk.pol_id = pol.id
355 and pol.status_code='ACTIVE' -- Added vdamerla for bug 6064374
356 and pk.status_code = 'ACTIVE' --Added by bkatraga for bug 6983321
357 and trunc(cnr.date_consolidated) between trunc(pk.streams_from_date) and trunc(pk.streams_to_date)
358 AND pk.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE; --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
359
360 CURSOR c_sec_strm_AR_csr(cp_contract_id in number, cp_con_rec_inv_id IN VARCHAR2) IS
361 SELECT arl.id cnsld_strm_id
362 ,pol.khr_id, pk.kle_id kle_id
363 FROM okl_bpd_ar_inv_lines_v arl
364 ,okl_bpd_ar_invoices_v arv
365 ,okl_pool_contents_v pk
366 ,okl_pools pol
367 WHERE arl.RECEIVABLES_INVOICE_ID = cp_con_rec_inv_id
368 AND arl.contract_id = cp_contract_id
369 AND arl.contract_id = pk.khr_id
370 AND nvl(arl.contract_line_id, -99) = nvl(pk.kle_id, -99)
371 AND arl.sty_id = pk.sty_id
372 AND pk.pol_id = pol.id
373 and pol.status_code='ACTIVE' -- Added vdamerla for bug 6064374
374 and pk.status_code = 'ACTIVE' --Added by bkatraga for bug 6983321
375 AND arv.invoice_id = arl.invoice_id
376 AND trunc(arv.date_consolidated) between trunc(pk.streams_from_date) and trunc(pk.streams_to_date)
377 AND pk.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE; --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
378
379 ------------------------------------------------------------
380 -- Source id of stream element of Late Invoice Cursor
381 ------------------------------------------------------------
382 --rkuttiya modified this cursor for R12 B Billing Architecture
383 --Modified data elements, view name, where clause
384
385 CURSOR c_src_sel(cp_stream_id IN NUMBER) IS
386 SELECT sel.source_id
387 FROM okl_bpd_ar_inv_lines_v arl
388 ,okl_strm_elements_v sel
389 WHERE arl.id = cp_stream_id
390 AND arl.sel_id = sel.id;
391
392
393
394 BEGIN
395 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
396 G_PKG_NAME,
397 p_init_msg_list,
398 l_api_version,
399 p_api_version,
400 '_PVT',
401 x_return_status);
402
403
404 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
405 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
406 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
407 RAISE OKL_API.G_EXCEPTION_ERROR;
408 END IF;
409
410 l_init_msg_list := p_init_msg_list ;
411
412 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Start of Calculate Late Interest.');
413
414 SELECT FND_GLOBAL.USER_ID
415 ,FND_GLOBAL.LOGIN_ID
416 ,NVL(DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),null)
417 INTO l_last_updated_by
418 ,l_last_update_login
419 ,l_request_id
420 FROM dual;
421
422 FOR l_inv_cur IN l_late_invs_cur
423 LOOP
424 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Processing: RECEIVABLES INVOICE ID => '||l_inv_cur.RECEIVABLES_INVOICE_ID||
425 ' ,due date=> '||l_inv_cur.DUE_DATE|| ' ,payment application date=> '||l_inv_cur.APPLY_DATE||' and Amount=> '||l_inv_cur.AMOUNT_APPLIED
426 ||' ,Contract Id=> '||l_inv_cur.contract_number);
427 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Late policy => ' || l_inv_cur.late_policy
428 || ' ,Contract currency => ' || l_inv_cur.currency_code);
429 l_amt_applied := l_inv_cur.AMOUNT_APPLIED;
430 l_due_date := l_inv_cur.due_date;
431 l_khr_id := l_inv_cur.CONTRACT_ID;
432 l_apply_date := l_inv_cur.APPLY_DATE;
433 l_contract_currency := l_inv_cur.currency_code;
434 l_late_policy := l_inv_cur.late_policy;
435
436
437 IF (nvl(l_late_policy, 'xxx') <> nvl(l_prev_late_policy, 'yyy') or
438 nvl(l_contract_currency, 'aaa') <> nvl(l_prev_contract_currency, 'bbb')) THEN
439 FOR l_lpo_cur IN l_late_policy_cur(l_late_policy)
440 LOOP
441 --start code pgomes 12/18/2002
442 IF (l_func_currency <> NVL(l_contract_currency, '000')) THEN
443 --convert minimum_late_interest to contract currency
444 OKL_ACCOUNTING_UTIL.convert_to_contract_currency(
445 p_khr_id => l_khr_id,
446 p_from_currency => l_func_currency,
447 p_transaction_date => sysdate,
448 p_amount => l_lpo_cur.minimum_late_interest,
449 x_contract_currency => l_contract_currency,
450 x_currency_conversion_type => l_currency_conversion_type,
451 x_currency_conversion_rate => l_currency_conversion_rate,
452 x_currency_conversion_date => l_currency_conversion_date,
453 x_converted_amount => l_minimum_late_interest);
454
455 l_minimum_late_interest := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(l_minimum_late_interest, l_contract_currency);
456
457 --convert maximum_late_interest to contract currency
458 OKL_ACCOUNTING_UTIL.convert_to_contract_currency(
459 p_khr_id => l_khr_id,
460 p_from_currency => l_func_currency,
461 p_transaction_date => sysdate,
462 p_amount => l_lpo_cur.maximum_late_interest,
463 x_contract_currency => l_contract_currency,
464 x_currency_conversion_type => l_currency_conversion_type,
465 x_currency_conversion_rate => l_currency_conversion_rate,
466 x_currency_conversion_date => l_currency_conversion_date,
467 x_converted_amount => l_maximum_late_interest);
468
469 l_maximum_late_interest := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(l_maximum_late_interest, l_contract_currency);
470
471 --convert late_int_minimum_balance to contract currency
472 OKL_ACCOUNTING_UTIL.convert_to_contract_currency(
473 p_khr_id => l_khr_id,
474 p_from_currency => l_func_currency,
475 p_transaction_date => sysdate,
476 p_amount => l_lpo_cur.late_int_minimum_balance,
477 x_contract_currency => l_contract_currency,
478 x_currency_conversion_type => l_currency_conversion_type,
479 x_currency_conversion_rate => l_currency_conversion_rate,
480 x_currency_conversion_date => l_currency_conversion_date,
481 x_converted_amount => l_late_int_minimum_balance);
482
483 l_late_int_minimum_balance := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(l_late_int_minimum_balance, l_contract_currency);
484 ELSE
485 l_minimum_late_interest := l_lpo_cur.minimum_late_interest;
486 l_maximum_late_interest := l_lpo_cur.maximum_late_interest;
487 l_late_int_minimum_balance := l_lpo_cur.late_int_minimum_balance;
488 END IF;
489 l_late_int_fixed_yn := l_lpo_cur.late_int_fixed_yn;
490 l_late_int_rate := l_lpo_cur.late_int_rate;
491 l_adder_rate := l_lpo_cur.adder_rate;
492 l_index_rate := l_lpo_cur.index_rate;
493 l_days_in_year := l_lpo_cur.days_in_year;
494 --end code pgomes 12/18/2002
495
496 -- pgomes 12/18/2002 start, changed code to consider converted amounts
497 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- LATE POLICY TYPE CODE => '||l_lpo_cur.LATE_POLICY_TYPE_CODE||
498 ' LATE INT MINIMUM BALANCE=> '||l_late_int_minimum_balance
499 ||' ,LATE INT GRACE PERIOD=> '||l_lpo_cur.LATE_INT_GRACE_PERIOD);
500 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- INTEREST RATE => ' || l_late_int_rate ||
501 ' ADDER RATE => ' || l_adder_rate || ' INDEX RATE => ' || l_index_rate);
502 END LOOP;
503 l_prev_late_policy := l_late_policy;
504 l_prev_contract_currency := l_contract_currency;
505 END IF;
506
507 l_error_flag := FALSE;
508 IF(l_late_int_minimum_balance < l_amt_applied) THEN
509 --FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Stream Type Id => '||l_sty_id);
510 -- 365 to be replaced by no. of days from the rule
511 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Days past due => '||(l_apply_date - l_due_date));
512 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Days in a year code => '||l_days_in_year);
513
514 BEGIN
515 IF (upper(l_days_in_year) = 'ACTUAL') THEN
516 l_days_calc := add_months(trunc(l_due_date, 'YEAR'), 12) - trunc(l_due_date, 'YEAR');
517 ELSE
518 l_days_calc := to_number(l_days_in_year);
519 END IF;
520
521 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Days in a year => '||l_days_calc);
522 EXCEPTION
523 WHEN OTHERS THEN
524 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Calculating Days in a year.');
525 END;
526
527 IF(l_late_int_fixed_yn = 'Y') THEN
528 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Fixed: Interest rate applied => '||(l_late_int_rate+l_adder_rate));
529 l_amount := ((l_amt_applied*(l_late_int_rate+l_adder_rate)*(l_apply_date-l_due_date))/100)/l_days_calc;
530 l_amount := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(l_amount, l_contract_currency);
531 ELSE
532 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Variable: Interest rate applied => '||(l_index_rate+l_adder_rate));
533 l_amount := ((l_amt_applied*(l_index_rate+l_adder_rate)*(l_apply_date-l_due_date))/100)/l_days_calc;
534 l_amount := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(l_amount, l_contract_currency);
535 END IF;
536 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Calculated late interest => '||l_amount);
537
538 IF(l_amount < l_minimum_late_interest) THEN
539 l_interest_amount := l_minimum_late_interest;
540 ELSIF(l_amount > l_maximum_late_interest) THEN
541 l_interest_amount := l_maximum_late_interest;
542 ELSE
543 l_interest_amount := l_amount;
544 END IF;
545 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Applied late interest => '||l_interest_amount);
546 -- pgomes 12/18/2002 end, changed code to consider converted amountss
547
548
549 IF l_interest_amount > 0 then
550
551 ----------------------------------------------------------------
552 --PROCESSING FOR LATE INTEREST
553 ----------------------------------------------------------------
554
555 --get stream type id
556 l_sty_id := null;
557
558 Okl_Streams_Util.get_primary_stream_type(
559 p_khr_id => l_khr_id,
560 p_primary_sty_purpose => cns_late_interest,
561 x_return_status => l_return_status,
562 x_primary_sty_id => l_sty_id );
563
564 IF (l_return_status = 'S' ) THEN
565 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream Id for purpose LATE_INTEREST retrieved.');
566 ELSE
567 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Could not retrieve Stream Id for purpose LATE_INTEREST.');
568 END IF;
569
570 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
571 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
572 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
573 RAISE Okl_Api.G_EXCEPTION_ERROR;
574 END IF;
575 --check for stream
576 l_stm_id := null;
577 l_se_line_number := null;
578
579 OPEN l_AR_stream_csr(l_inv_cur.contract_id, l_sty_id);
580 FETCH l_AR_stream_csr INTO l_stm_id;
581 CLOSE l_AR_stream_csr;
582
583 --create stream for late interest
584 IF (l_stm_id IS NULL) THEN
585 l_stmv_rec := l_init_stmv_rec;
586
587 OPEN c_tran_num_csr;
588 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
589 CLOSE c_tran_num_csr;
590
591 l_stmv_rec.sty_id := l_sty_id;
592 l_stmv_rec.khr_id := l_inv_cur.contract_id;
593 -- l_stmv_rec.sgn_code := 'MANL' -- bug 6472228
594 l_stmv_rec.sgn_code := G_LATE_SGN_CODE; -- bug 6472228
595 l_stmv_rec.say_code := 'CURR';
596 l_stmv_rec.active_yn := 'Y';
597 l_stmv_rec.date_current := sysdate;
598 l_stmv_rec.comments := 'LATE INTEREST BILLING';
599
600 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating LATE INTEREST Stream');
601
602 Okl_Streams_Pub.create_streams(
603 p_api_version => p_api_version,
604 p_init_msg_list => p_init_msg_list,
605 x_return_status => x_return_status,
606 x_msg_count => x_msg_count,
607 x_msg_data => x_msg_data,
608 p_stmv_rec => l_stmv_rec,
609 x_stmv_rec => lx_stmv_rec);
610
611 l_stm_id := lx_stmv_rec.id;
612 l_se_line_number := 1;
613
614 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
615 l_error_flag := TRUE;
616 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Stream for LATE INTEREST');
617 ELSE
618 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- SUCCESS: Creating Stream for LATE INTEREST');
619 END IF;
620 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream element line number => ' || l_se_line_number);
621 ELSE
622 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream for LATE INTEREST found');
623 open l_stream_line_nbr_csr(l_stm_id);
624 fetch l_stream_line_nbr_csr into l_se_line_number;
625 close l_stream_line_nbr_csr;
626 l_se_line_number := l_se_line_number + 1;
627 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream element line number => ' || l_se_line_number);
628 END IF;
629
630 --create stream element for late interest
631 IF (l_stm_id IS NOT NULL) THEN
632 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating Stream Element for LATE INTEREST');
633 l_selv_rec := l_init_selv_rec;
634
635 l_selv_rec.stm_id := l_stm_id;
636 l_selv_rec.SE_LINE_NUMBER := l_se_line_number;
637 l_selv_rec.STREAM_ELEMENT_DATE := sysdate;
638 l_selv_rec.AMOUNT := l_interest_amount;
639 l_selv_rec.COMMENTS := 'LATE INTEREST BILLING ELEMENTS';
640 l_selv_rec.ACCRUED_YN := 'Y';
641
642 l_selv_rec.source_table := cns_source_table;
643
644 l_selv_rec.source_id :=l_inv_cur.RECEIVABLES_INVOICE_ID;
645
646 Okl_Sel_Pvt.insert_row(
647 p_api_version,
648 p_init_msg_list,
649 x_return_status,
650 x_msg_count,
651 x_msg_data,
652 l_selv_rec,
653 lx_selv_rec);
654
655 l_sel_id := lx_selv_rec.id;
656 l_sec_stream_id := lx_selv_rec.source_id;
657
658 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
659 l_error_flag := TRUE;
660 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Error Creating Stream Element for Contract: '
661 ||l_inv_cur.contract_number
662 ||' Stream Purpose: '||cns_late_interest
663 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
664 ||' Amount: '||l_interest_amount);
665 ELSE
666
667 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Created Late Interest Stream Element for Contract: '
668 ||l_inv_cur.contract_number
669 ||' Stream Purpose: '||cns_late_interest
670 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
671 ||' Amount: '||l_interest_amount
672 );
673 END IF;
674 END IF;
675
676 ----------------------------------------------------------------
677 --PROCESSING FOR LATE INTEREST PAYABLE TO INVESTOR
678 ----------------------------------------------------------------
679 if l_inv_cur.invoice_flag = 'AR-INVOICE' then
680 FOR cur_sec_strm IN c_sec_strm_AR_csr(l_inv_cur.contract_id, l_inv_cur.RECEIVABLES_INVOICE_ID) LOOP
681 --get stream type id
682 l_sty_id := null;
683
684 Okl_Streams_Util.get_primary_stream_type(
685 p_khr_id => cur_sec_strm.khr_id,
686 p_primary_sty_purpose => cns_late_interest_payable,
687 x_return_status => l_return_status,
688 x_primary_sty_id => l_sty_id );
689
690 IF (l_return_status = 'S' ) THEN
691 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream Id for purpose INVESTOR_LATE_INTEREST_PAY retrieved.');
692 ELSE
693 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Could not retrieve Stream Id for purpose INVESTOR_LATE_INTEREST_PAY.');
694 END IF;
695
696 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
697 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
698 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
699 RAISE Okl_Api.G_EXCEPTION_ERROR;
700 END IF;
701 --check for stream
702 l_stm_id := null;
703 l_se_line_number := null;
704
705 OPEN l_AR_stream_csr(l_inv_cur.contract_id, l_sty_id);
706 FETCH l_AR_stream_csr INTO l_stm_id;
707 CLOSE l_AR_stream_csr;
708
709 --create stream for late interest payable
710 IF (l_stm_id IS NULL) THEN
711 l_stmv_rec := l_init_stmv_rec;
712
713 OPEN c_tran_num_csr;
714 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
715 CLOSE c_tran_num_csr;
716
717 l_stmv_rec.sty_id := l_sty_id;
718 l_stmv_rec.khr_id := l_inv_cur.contract_id;
719 -- l_stmv_rec.sgn_code := 'MANL'; -- bug 6472228
720 l_stmv_rec.sgn_code := G_LATE_SGN_CODE; -- bug 6472228
721 l_stmv_rec.say_code := 'CURR';
722 l_stmv_rec.active_yn := 'Y';
723 l_stmv_rec.date_current := sysdate;
724 l_stmv_rec.comments := 'INVESTOR LATE INTEREST PAYABLE';
725 IF (cur_sec_strm.khr_id IS NOT NULL) THEN
726 l_stmv_rec.source_id := cur_sec_strm.khr_id;
727 l_stmv_rec.source_table := 'OKL_K_HEADERS';
728 END IF;
729
730 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating INVESTOR LATE INTEREST PAYABLE Stream');
731
732 Okl_Streams_Pub.create_streams(
733 p_api_version => p_api_version,
734 p_init_msg_list => p_init_msg_list,
735 x_return_status => x_return_status,
736 x_msg_count => x_msg_count,
737 x_msg_data => x_msg_data,
738 p_stmv_rec => l_stmv_rec,
739 x_stmv_rec => lx_stmv_rec);
740
741 l_stm_id := lx_stmv_rec.id;
742 l_se_line_number := 1;
743
744 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream element line number => ' || l_se_line_number);
745 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
746 l_error_flag := TRUE;
747 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Stream for INVESTOR LATE INTEREST PAYABLE');
748 ELSE
749 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- SUCCESS: Creating Stream for INVESTOR LATE INTEREST PAYABLE');
750 END IF;
751 ELSE
752 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream for INVESTOR LATE INTEREST PAYABLE found');
753 open l_stream_line_nbr_csr(l_stm_id);
754 fetch l_stream_line_nbr_csr into l_se_line_number;
755 close l_stream_line_nbr_csr;
756 l_se_line_number := l_se_line_number + 1;
757 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream element line number => ' || l_se_line_number);
758 END IF;
759
760 --create stream element for late interest payable
761 IF (l_stm_id IS NOT NULL) THEN
762 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating INVESTOR LATE INTEREST PAYABLE Stream Elements');
763 l_selv_rec := l_init_selv_rec;
764 l_selv_rec.stm_id := l_stm_id;
765 l_selv_rec.SE_LINE_NUMBER := l_se_line_number;
766 l_selv_rec.STREAM_ELEMENT_DATE := sysdate;
767 l_selv_rec.AMOUNT := l_interest_amount;
768 l_selv_rec.COMMENTS := 'INVESTOR LATE INTEREST PAYABLE ELEMENTS';
769 l_selv_rec.ACCRUED_YN := 'Y';
770 l_selv_rec.sel_id := l_sel_id;
771 IF (cur_sec_strm.khr_id IS NOT NULL) THEN
772 l_selv_rec.source_id := cur_sec_strm.khr_id;
773 l_selv_rec.source_table := 'OKL_K_HEADERS';
774 END IF;
775
776 Okl_Sel_Pvt.insert_row(
777 p_api_version,
778 p_init_msg_list,
779 x_return_status,
780 x_msg_count,
781 x_msg_data,
782 l_selv_rec,
783 lx_selv_rec);
784
785 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
786 l_error_flag := TRUE;
787 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Error Creating Payable Stream Element for Contract: '
788 ||l_inv_cur.contract_number
789 ||' Stream Purpose: '||cns_late_interest
790 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
791 ||' Amount: '||l_interest_amount);
792 ELSE
793 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Created Investor Late Interest Payable Stream Element for Contract: '
794 ||l_inv_cur.contract_number
795 ||' Stream Purpose: '||cns_late_interest
796 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
797 ||' Amount: '||l_interest_amount
798 );
799 END IF;
800 END IF;
801 END LOOP;
802 ELSE
803
804 FOR cur_sec_strm IN c_sec_strm_cons_csr(l_inv_cur.contract_id, l_inv_cur.RECEIVABLES_INVOICE_ID) LOOP
805 --get stream type id
806 l_sty_id := null;
807
808 Okl_Streams_Util.get_primary_stream_type(
809 p_khr_id => cur_sec_strm.khr_id,
810 p_primary_sty_purpose => cns_late_interest_payable,
811 x_return_status => l_return_status,
812 x_primary_sty_id => l_sty_id );
813
814 IF (l_return_status = 'S' ) THEN
815 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream Id for purpose INVESTOR_LATE_INTEREST_PAY retrieved.');
816 ELSE
817 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Could not retrieve Stream Id for purpose INVESTOR_LATE_INTEREST_PAY.');
818 END IF;
819
820 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
821 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
822 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
823 RAISE Okl_Api.G_EXCEPTION_ERROR;
824 END IF;
825 --check for stream
826 l_stm_id := null;
827 l_se_line_number := null;
828
829 OPEN l_cons_stream_csr(l_inv_cur.contract_id,cur_sec_strm.kle_id,l_sty_id);
830 FETCH l_cons_stream_csr INTO l_stm_id;
831 CLOSE l_cons_stream_csr;
832
833 --create stream for late interest payable
834 IF ((l_stm_id IS NULL) or (l_stm_id = -99))THEN
835 l_stmv_rec := l_init_stmv_rec;
836
837 OPEN c_tran_num_csr;
838 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
839 CLOSE c_tran_num_csr;
840
841 l_stmv_rec.sty_id := l_sty_id;
842 l_stmv_rec.khr_id := l_inv_cur.contract_id;
843 -- l_stmv_rec.sgn_code := 'MANL'; -- bug 6472228
844 l_stmv_rec.sgn_code := G_LATE_SGN_CODE; -- bug 6472228
845 l_stmv_rec.say_code := 'CURR';
846 l_stmv_rec.active_yn := 'Y';
847 l_stmv_rec.date_current := sysdate;
848 l_stmv_rec.comments := 'INVESTOR LATE INTEREST PAYABLE';
849 IF (cur_sec_strm.khr_id IS NOT NULL) THEN
850 l_stmv_rec.source_id := cur_sec_strm.khr_id;
851 l_stmv_rec.source_table := 'OKL_K_HEADERS';
852 END IF;
853
854 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating INVESTOR LATE INTEREST PAYABLE Stream');
855
856 Okl_Streams_Pub.create_streams(
857 p_api_version => p_api_version,
858 p_init_msg_list => p_init_msg_list,
859 x_return_status => x_return_status,
860 x_msg_count => x_msg_count,
861 x_msg_data => x_msg_data,
862 p_stmv_rec => l_stmv_rec,
863 x_stmv_rec => lx_stmv_rec);
864
865 l_stm_id := lx_stmv_rec.id;
866 l_se_line_number := 1;
867
868 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream element line number => ' || l_se_line_number);
869 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
870 l_error_flag := TRUE;
871 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Stream for INVESTOR LATE INTEREST PAYABLE');
872 ELSE
873 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- SUCCESS: Creating Stream for INVESTOR LATE INTEREST PAYABLE');
874 END IF;
875 ELSE
876 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream for INVESTOR LATE INTEREST PAYABLE found');
877 open l_stream_line_nbr_csr(l_stm_id);
878 fetch l_stream_line_nbr_csr into l_se_line_number;
879 close l_stream_line_nbr_csr;
880 l_se_line_number := l_se_line_number + 1;
881 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream element line number => ' || l_se_line_number);
882 END IF;
883
884 --create stream element for late interest payable
885 IF (l_stm_id IS NOT NULL) THEN
886 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating INVESTOR LATE INTEREST PAYABLE Stream Elements');
887 l_selv_rec := l_init_selv_rec;
888 l_selv_rec.stm_id := l_stm_id;
889 l_selv_rec.SE_LINE_NUMBER := l_se_line_number;
890 l_selv_rec.STREAM_ELEMENT_DATE := sysdate;
891 l_selv_rec.AMOUNT := l_interest_amount;
892 l_selv_rec.COMMENTS := 'INVESTOR LATE INTEREST PAYABLE ELEMENTS';
893 l_selv_rec.ACCRUED_YN := 'Y';
894 l_selv_rec.sel_id := l_sel_id;
895 IF (cur_sec_strm.khr_id IS NOT NULL) THEN
896 l_selv_rec.source_id := cur_sec_strm.khr_id;
897 l_selv_rec.source_table := 'OKL_K_HEADERS';
898 END IF;
899
900 Okl_Sel_Pvt.insert_row(
901 p_api_version,
902 p_init_msg_list,
903 x_return_status,
904 x_msg_count,
905 x_msg_data,
906 l_selv_rec,
907 lx_selv_rec);
908
909 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
910 l_error_flag := TRUE;
911 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Error Creating Payable Stream Element for Contract: '
912 ||l_inv_cur.contract_number
913 ||' Stream Purpose: '||cns_late_interest
914 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
915 ||' Amount: '||l_interest_amount);
916 ELSE
917
918 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Created Investor Late Interest Payable Stream Element for Contract: '
919 ||l_inv_cur.contract_number
920 ||' Stream Purpose: '||cns_late_interest
921 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
922 ||' Amount: '||l_interest_amount
923 );
924 END IF;
925 END IF;
926 END LOOP;
927 END IF;
928 END IF; -- END IF l_interest_amount > 0
929
930 IF l_inv_cur.INVOICE_FLAG = 'AR-INVOICE' THEN
931 IF NOT(l_error_flag) THEN
932 FOR l_lsm IN l_AR_lsm_cur(l_inv_cur.RECEIVABLES_INVOICE_ID, l_inv_cur.CONTRACT_ID)
933 LOOP
934 Update OKL_TXD_AR_LN_DTLS_B
935 set LATE_INT_ASS_YN = 'Y'
936 , LATE_INT_ASSESS_DATE = SYSDATE
937 ,last_updated_by = l_last_updated_by
938 ,last_update_date = sysdate
939 ,last_update_login = l_last_update_login
940 ,request_id = l_request_id
941 WHERE CURRENT OF l_AR_lsm_cur;
942
943 -- IF (l_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
944 IF (SQL%NOTFOUND) THEN
945 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Internal Consolidation Record Update Unsuccessful.');
946 ELSE
947 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Internal Consolidation Record Updated.');
948 END IF;
949 -- END IF;
950
951 END LOOP;
952 END IF;
953 END IF;
954 IF l_inv_cur.INVOICE_FLAG = 'CONS-INVOICE' THEN
955 IF NOT(l_error_flag) THEN
956 FOR l_lsm IN l_cons_lsm_cur(l_inv_cur.RECEIVABLES_INVOICE_ID, l_inv_cur.CONTRACT_ID)
957 LOOP
958 Update okl_cnsld_ar_strms_b
959 set LATE_INT_ASS_YN = 'Y'
960 , LATE_INT_ASSESS_DATE = SYSDATE
961 ,last_updated_by = l_last_updated_by
962 ,last_update_date = sysdate
963 ,last_update_login = l_last_update_login
964 ,request_id = l_request_id
965 WHERE CURRENT OF l_cons_lsm_cur;
966
967 -- IF (l_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
968 IF (SQL%NOTFOUND) THEN
969 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Internal Consolidation Record Update Unsuccessful.');
970 ELSE
971 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Internal Consolidation Record Updated.');
972 END IF;
973 -- END IF;
974
975 END LOOP;
976 END IF;
977 END IF;
978
979
980 END IF;
981 --dkagrawa bug# 4728636 changes start
982 IF l_prev_khr_id IS NULL THEN
983 l_prev_khr_id := l_inv_cur.contract_id;
984 END IF;
985 IF l_prev_khr_id <> l_inv_cur.contract_id THEN
986 IF NOT(l_error_flag) THEN
987 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date ( l_prev_khr_id );
988 END IF;
989 l_prev_khr_id := l_inv_cur.contract_id;
990 END IF;
991 END LOOP;
992 IF l_prev_khr_id IS NOT NULL THEN
993 IF NOT(l_error_flag) THEN
994 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date ( l_prev_khr_id );
995 END IF;
996 END IF;
997 --dkagrawa bug# 4728636 changes end
998
999
1000 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'End of Calculate Late Interest.');
1001 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1002 EXCEPTION
1003 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1004 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (EXCP) => '||SQLERRM);
1005 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
1006 (
1007 l_api_name,
1008 G_PKG_NAME,
1009 'Okl_Api.G_RET_STS_ERROR',
1010 x_msg_count,
1011 x_msg_data,
1012 '_PVT'
1013 );
1014 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1015 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (UNEXCP) => '||SQLERRM);
1016 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1017 (
1018 l_api_name,
1019 G_PKG_NAME,
1020 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1021 x_msg_count,
1022 x_msg_data,
1023 '_PVT'
1024 );
1025 WHEN OTHERS THEN
1026 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (Others) => '||SQLERRM);
1027 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1028 (
1029 l_api_name,
1030 G_PKG_NAME,
1031 'OTHERS',
1032 x_msg_count,
1033 x_msg_data,
1034 '_PVT'
1035 );
1036 END calculate_late_interest;
1037
1038 END OKL_LTE_INT_PVT;
1039