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