[Home] [Help]
PACKAGE BODY: APPS.OKL_LTE_CHRG_PVT
Source
1 PACKAGE BODY OKL_LTE_CHRG_PVT AS
2 /* $Header: OKLRCHGB.pls 120.22 2009/05/07 15:18:36 nikshah ship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.FEES';
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_charge(
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
23
24 l_hd_id NUMBER;
25 l_found BOOLEAN;
26 l_api_version CONSTANT NUMBER := 1;
27 l_api_name CONSTANT VARCHAR2(30) := 'calculate_late_charge';
28 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
29 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
30 l_init_msg_list VARCHAR2(1) ;
31 l_msg_count NUMBER ;
32
33 i NUMBER := 0;
34
35 l_rec_inv_id OKL_CNSLD_AR_STRMS_B.RECEIVABLES_INVOICE_ID%type;
36 l_rec_inv_contract_id NUMBER;
37 l_amt_due_remaining AR_PAYMENT_SCHEDULES_ALL.AMOUNT_DUE_REMAINING%type;
38 l_due_date AR_PAYMENT_SCHEDULES_ALL.DUE_DATE%type;
39 l_khr_id OKL_CNSLD_AR_STRMS_B.KHR_ID%type;
40 l_sty_id OKL_STRM_TYPE_V.ID%TYPE;
41 l_stm_id OKL_STREAMS_V.ID%TYPE;
42 l_late_stm_id OKL_STREAMS_V.ID%TYPE;
43 l_se_line_number OKL_STRM_ELEMENTS_V.SE_LINE_NUMBER%TYPE;
44 l_amount NUMBER;
45 l_stream_purpose OKL_STRM_TYPE_V.stream_type_purpose%TYPE;
46 l_sec_rec_inv_id OKL_CNSLD_AR_STRMS_V.ID%TYPE;
47 l_error_flag BOOLEAN := FALSE;
48
49 --start code pgomes 12/18/2002
50 l_func_currency okl_k_headers_full_v.currency_code%TYPE := okl_accounting_util.get_func_curr_code;
51 l_contract_currency okl_k_headers_full_v.currency_code%TYPE;
52 l_prev_contract_currency okl_k_headers_full_v.currency_code%TYPE;
53 l_late_policy okl_late_policies_v.name%TYPE;
54 l_prev_late_policy okl_late_policies_v.name%TYPE;
55
56
57 l_late_chrg_amount okl_late_policies_b.late_chrg_amount%TYPE;
58 l_minimum_late_charge okl_late_policies_b.minimum_late_charge%TYPE;
59 l_maximum_late_charge okl_late_policies_b.maximum_late_charge%TYPE;
60 l_late_chrg_minimum_balance okl_late_policies_b.late_chrg_minimum_balance%TYPE;
61 l_late_chrg_fixed_yn okl_late_policies_v.late_chrg_fixed_yn%TYPE;
62 l_late_chrg_allowed_yn okl_late_policies_v.late_chrg_allowed_yn%TYPE;
63 l_late_chrg_rate okl_late_policies_v.late_chrg_rate%TYPE;
64
65 l_late_charge_amt okl_strm_elements_v.amount%TYPE;
66
67 l_currency_conversion_type okl_k_headers_full_v.currency_conversion_type%type;
68 l_currency_conversion_rate okl_k_headers_full_v.currency_conversion_rate%type;
69 l_currency_conversion_date okl_k_headers_full_v.currency_conversion_date%type;
70
71 l_last_updated_by NUMBER;
72 l_last_update_login NUMBER;
73 l_request_id NUMBER;
74 --end code pgomes 12/18/2002
75
76 l_lsmv_rec lsmv_rec_type;
77 lx_lsmv_rec lsmv_rec_type;
78
79
80 l_stmv_rec Okl_Streams_Pub.stmv_rec_type;
81 lx_stmv_rec Okl_Streams_Pub.stmv_rec_type;
82 l_init_stmv_rec Okl_Streams_Pub.stmv_rec_type;
83
84 l_selv_rec Okl_Sel_Pvt.selv_rec_type;
85 lx_selv_rec Okl_Sel_Pvt.selv_rec_type;
86 l_init_selv_rec Okl_Sel_Pvt.selv_rec_type;
87 l_sel_id Okl_strm_elements_v.sel_id%TYPE;
88 l_prev_khr_id okl_k_headers.id%TYPE; --dkagrawa added for bug# 4728636
89
90 --vdamerla bug 5474844
91 l_investor_disb_flag varchar2(1);
92
93 -- vdamerla bug 5474844 modified this cursor for R12 B Billing Architecture
94 --made changes to cursor data elements, table names, where clauses
95
96 --fetches late invoices which have never been charged a late fee
97 CURSOR l_late_invs_cur IS
98 select lte.name late_policy
99 ,khr.currency_code
100 ,lpt1.contract_number
101 ,lpt1.contract_id
102 ,NULL consolidated_invoice_id
103 ,lpt1.trx_number consolidated_invoice_number
104 ,sum(lpt1.amount_due_remaining) amount_due_remaining
105 ,max(lpt.due_date) due_date
106 from
107 (SELECT
108 PAY_SCH.DUE_DATE DUE_DATE, RACTRX.STATUS_TRX STATUS,
109 RACTRX.CUSTOMER_TRX_ID INVOICE_ID,
110 PAY_SCH.CLASS PAY_SCH_CLASS
111 FROM
112 RA_CUSTOMER_TRX_ALL RACTRX,
113 RA_CUST_TRX_TYPES_ALL RATRXTYPE,
114 AR_PAYMENT_SCHEDULES_ALL PAY_SCH
115 WHERE
116 RACTRX.CUST_TRX_TYPE_ID = RATRXTYPE.CUST_TRX_TYPE_ID
117 and RACTRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
118 and RACTRX.ORG_ID = RATRXTYPE.ORG_ID ) lpt
119 ,okl_bpd_ar_inv_lines_v lpt1
120 ,okl_k_headers_full_v khr
121 ,okc_rule_groups_b rgp
122 ,okc_rules_b rul
123 ,okc_rules_b rul_exm
124 ,okc_rules_b rul_hld
125 ,okl_late_policies_v lte
126 ,okl_strm_type_b stb --For bug 7356486
127 where lpt.invoice_id=lpt1.invoice_id
128 and lpt.pay_sch_class = 'INV'
129 AND lpt.STATUS = 'OP'
130 and nvl(lpt1.late_charge_ass_yn, 'N') = 'N'
131 and lpt1.contract_number = khr.contract_number
132 AND lpt1.sty_id = stb.id --For bug 7356486
133 AND lpt1.stream_type_id = stb.id --For bug 7356486
134 and khr.id = rgp.dnz_chr_id
135 and rgp.rgd_code = 'LALCGR'
136 and khr.id = rul.dnz_chr_id
137 and rgp.id = rul.rgp_id
138 and rul.rule_information_category = 'LALCPR'
139 and rul.rule_information1 = lte.id
140 and (lte.late_policy_type_code = 'LCT' or lte.late_policy_type_code = 'CHG')
141 and (trunc(lpt.due_date) + nvl(lte.late_chrg_grace_period, 0)) < trunc(sysdate)
142 and khr.id = rul_exm.dnz_chr_id
143 and rgp.id = rul_exm.rgp_id
144 and rul_exm.rule_information_category = 'LALCEX'
145 and NVL(rul_exm.rule_information1, 'N') = 'N'
146 and not exists (select 1 from okl_strm_type_exempt_v sty_exm
147 where lte.id = sty_exm.lpo_id
148 and lpt1.stream_type_id = sty_exm.sty_id
149 and NVL(sty_exm.late_policy_exempt_yn, 'N') = 'Y')
150 and khr.id = rul_hld.dnz_chr_id
151 and rgp.id = rul_hld.rgp_id
152 and rul_hld.rule_information_category = 'LAHUDT'
153 and TRUNC(NVL(FND_DATE.canonical_to_date(rul_hld.rule_information1), sysdate - 1)) < trunc(sysdate)
154 and khr.authoring_org_id = MO_GLOBAL.get_current_org_id
155 and stb.STREAM_TYPE_PURPOSE <> 'LATE_FEE' -- for bug 7295166 -- Don't charge late charge on late charge.
156 group by lte.name
157 ,khr.currency_code
158 ,lpt1.contract_number
159 ,lpt1.contract_id
160 ,null
161 ,lpt1.trx_number
162 ,lpt.due_date
163 UNION
164 select lte.name late_policy
165 ,khr.currency_code
166 ,CN.contract_number
167 ,ST.khr_id
168 ,HD.id
169 ,HD.consolidated_invoice_number
170 ,sum(PS.amount_due_remaining) amount_due_remaining
171 ,PS.due_date
172 from AR_PAYMENT_SCHEDULES_ALL PS
173 ,OKL_CNSLD_AR_STRMS_B ST
174 ,OKL_CNSLD_AR_HDRS_B HD
175 ,OKC_K_HEADERS_ALL_B CN
176 ,OKL_CNSLD_AR_LINES_B LN
177 ,OKL_STRM_TYPE_B SM
178 ,okl_k_headers_full_v khr
179 ,okc_rule_groups_b rgp
180 ,okc_rules_b rul
181 ,okc_rules_b rul_exm
182 ,okc_rules_b rul_hld
183 ,okl_late_policies_v lte
184 where PS.CLASS IN ('INV', 'CM')
185 AND ST.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID
186 AND LN.ID = ST.LLN_ID
187 AND HD.ID = LN.CNR_ID
188 AND SM.ID = ST.STY_ID
189 AND CN.ID = ST.KHR_ID
190 AND HD.ORG_ID = MO_GLOBAL.get_current_org_id
191 AND ps.class = 'INV'
192 AND ps.STATUS = 'OP'
193 and nvl(st.late_charge_ass_yn, 'N') = 'N'
194 and st.khr_id = khr.id
195 and khr.id = rgp.dnz_chr_id
196 and rgp.rgd_code = 'LALCGR'
197 and khr.id = rul.dnz_chr_id
198 and rgp.id = rul.rgp_id
199 and rul.rule_information_category = 'LALCPR'
200 and rul.rule_information1 = lte.id
201 and (lte.late_policy_type_code = 'LCT' or lte.late_policy_type_code = 'CHG')
202 and (trunc(ps.due_date) + nvl(lte.late_chrg_grace_period, 0)) < trunc(sysdate)
203 and khr.id = rul_exm.dnz_chr_id
204 and rgp.id = rul_exm.rgp_id
205 and rul_exm.rule_information_category = 'LALCEX'
206 and NVL(rul_exm.rule_information1, 'N') = 'N'
207 and not exists (select 1 from okl_strm_type_exempt_v sty_exm
208 where lte.id = sty_exm.lpo_id
209 and SM.id = sty_exm.sty_id
210 and NVL(sty_exm.late_policy_exempt_yn, 'N') = 'Y')
211 and khr.id = rul_hld.dnz_chr_id
212 and rgp.id = rul_hld.rgp_id
213 and rul_hld.rule_information_category = 'LAHUDT'
214 and TRUNC(NVL(FND_DATE.canonical_to_date(rul_hld.rule_information1), sysdate - 1)) < trunc(sysdate)
215 and khr.authoring_org_id = MO_GLOBAL.get_current_org_id
216 and SM.STREAM_TYPE_PURPOSE <> 'LATE_FEE' -- for bug 7295166 -- Don't charge late charge on late charge.
217 group by lte.name
218 ,khr.currency_code
219 ,CN.contract_number
220 ,ST.KHR_id
221 ,HD.id
222 ,HD.consolidated_invoice_number
223 ,PS.due_date;
224
225
226 --vdamerla bug 5474844 modified this cursor for R12 B Billing Architecture
227 --modified cursor data elements, table names, where clause, group by clause
228 --fetches late invoices which are late again
229
230 CURSOR l_late_invs_cur1 IS
231 SELECT lte.name late_policy
232 ,khr.currency_code
233 ,orig_arl.contract_number
234 ,orig_arl.contract_id contract_id
235 ,NULL consolidated_invoice_id
236 ,orig_arl.trx_number consolidated_invoice_number
237 ,sum(orig_arl.amount_due_remaining) amount_due_remaining
238 ,max(last_arv.due_date) due_date
239 FROM (SELECT
240 PAY_SCH.DUE_DATE DUE_DATE, RACTRX.STATUS_TRX STATUS,
241 RACTRX.CUSTOMER_TRX_ID INVOICE_ID,
242 PAY_SCH.CLASS PAY_SCH_CLASS
243 FROM
244 RA_CUSTOMER_TRX_ALL RACTRX,
245 RA_CUST_TRX_TYPES_ALL RATRXTYPE,
246 AR_PAYMENT_SCHEDULES_ALL PAY_SCH
247 WHERE
248 RACTRX.CUST_TRX_TYPE_ID = RATRXTYPE.CUST_TRX_TYPE_ID
249 and RACTRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
250 and RACTRX.ORG_ID = RATRXTYPE.ORG_ID ) orig_arv
251 ,(SELECT
252 PAY_SCH.DUE_DATE DUE_DATE, RACTRX.STATUS_TRX STATUS,
253 RACTRX.CUSTOMER_TRX_ID INVOICE_ID,
254 PAY_SCH.CLASS PAY_SCH_CLASS
255 FROM
256 RA_CUSTOMER_TRX_ALL RACTRX,
257 RA_CUST_TRX_TYPES_ALL RATRXTYPE,
258 AR_PAYMENT_SCHEDULES_ALL PAY_SCH
259 WHERE
260 RACTRX.CUST_TRX_TYPE_ID = RATRXTYPE.CUST_TRX_TYPE_ID
261 and RACTRX.CUSTOMER_TRX_ID = PAY_SCH.CUSTOMER_TRX_ID
262 and RACTRX.ORG_ID = RATRXTYPE.ORG_ID ) last_arv
263 ,okl_bpd_ar_inv_lines_v orig_arl
264 ,okl_bpd_ar_inv_lines_v last_arl
265 ,okl_strm_elements_v sel
266 ,okl_k_headers_full_v khr
267 ,okc_rule_groups_b rgp
268 ,okc_rules_b rul
269 ,okc_rules_b rul_exm
270 ,okc_rules_b rul_hld
271 ,okl_late_policies_v lte
272 WHERE last_arl.sel_id = sel.id
273 and orig_arv.pay_sch_class = 'INV'
274 AND orig_arv.STATUS = 'OP'
275 and orig_arl.receivables_invoice_id = sel.source_id
276 and sel.source_table='RA_CUSTOMER_TRX_LINES_ALL'
277 and orig_arl.late_charge_ass_yn = 'Y'
278 and nvl(last_arl.late_charge_ass_yn, 'N') = 'N'
279 and orig_arv.invoice_id = orig_arl.invoice_id
280 and last_arv.invoice_id = last_arl.invoice_id
281 and orig_arl.contract_id=last_arl.contract_id
282 and orig_arl.contract_id = khr.id
283 and khr.id = rgp.dnz_chr_id
284 and rgp.rgd_code = 'LALCGR'
285 and rgp.id = rul.rgp_id
286 and khr.id = rul.dnz_chr_id
287 and rul.rule_information_category = 'LALCPR'
288 and rul.rule_information1 = lte.id
289 and (lte.late_policy_type_code = 'LCT' or lte.late_policy_type_code = 'CHG')
290 and khr.id = rul_exm.dnz_chr_id
291 and rgp.id = rul_exm.rgp_id
292 and rul_exm.rule_information_category = 'LALCEX'
293 and NVL(rul_exm.rule_information1, 'N') = 'N'
294 and not exists (select 1 from okl_strm_type_exempt_v sty_exm
295 where lte.id = sty_exm.lpo_id
296 and orig_arl.stream_type_id = sty_exm.sty_id
297 and NVL(sty_exm.late_policy_exempt_yn, 'N') = 'Y')
298 and khr.id = rul_hld.dnz_chr_id
299 and rgp.id = rul_hld.rgp_id
300 and rul_hld.rule_information_category = 'LAHUDT'
301 and TRUNC(NVL(FND_DATE.canonical_to_date(rul_hld.rule_information1), sysdate - 1)) < trunc(sysdate)
302 and khr.authoring_org_id = MO_GLOBAL.get_current_org_id
303 and exists (select'x' from ra_customer_trx_lines_all trxl where trxl.customer_trx_id=orig_arv.invoice_id and
304 trxl.interface_line_attribute6=khr.contract_number)
305 and exists (select'x' from ra_customer_trx_lines_all trxl where trxl.customer_trx_id=last_arv.invoice_id and
306 trxl.interface_line_attribute6=khr.contract_number)
307
308 group by lte.name, khr.currency_code, orig_arl.contract_id,orig_arl.contract_number
309 ,orig_arl.receivables_invoice_number, orig_arl.receivables_invoice_id
310 ,orig_arl.amount_due_remaining, lte.late_chrg_grace_period
311 having (trunc(max(last_arv.due_date)) + nvl(lte.late_chrg_grace_period, 0)) < trunc(sysdate)
312 UNION
313 select lte.name late_policy
314 ,khr.currency_code
315 ,orig.contract_number
316 ,orig.contract_id
317 ,orig.consolidated_invoice_id
318 ,orig.consolidated_invoice_number
319 ,sum(orig.amount_due_remaining) amount_due_remaining
320 , max(last.due_date) due_date
321 from
322 (SELECT
323 HD.ID CONSOLIDATED_INVOICE_ID ,
324 HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER ,
325 ST.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID ,
326 ST.KHR_ID CONTRACT_ID ,
327 CN.CONTRACT_NUMBER CONTRACT_NUMBER ,
328 PS.DUE_DATE DUE_DATE ,
329 PS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING ,
330 ST.LATE_CHARGE_ASS_YN LATE_CHARGE_ASS_YN,
331 ST.SEL_ID SEL_ID,
332 PS.CLASS CLASS ,
333 SM.ID STREAM_TYPE_ID,
334 PS.STATUS STATUS
335 FROM AR_PAYMENT_SCHEDULES_ALL PS,
336 OKL_CNSLD_AR_STRMS_B ST,
337 OKL_CNSLD_AR_HDRS_B HD,
338 OKC_K_HEADERS_ALL_B CN,
339 OKL_CNSLD_AR_LINES_B LN,
340 OKL_STRM_TYPE_B SM
341 WHERE PS.CLASS IN ('INV', 'CM')
342 AND ST.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID
343 AND CN.ID = ST.KHR_ID
344 AND LN.ID = ST.LLN_ID
345 AND HD.ID = LN.CNR_ID
346 AND SM.ID = ST.STY_ID) orig
347 , (SELECT
348 HD.ID CONSOLIDATED_INVOICE_ID ,
349 HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER ,
350 ST.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID ,
351 ST.KHR_ID CONTRACT_ID ,
352 CN.CONTRACT_NUMBER CONTRACT_NUMBER ,
353 PS.DUE_DATE DUE_DATE ,
354 PS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING ,
355 ST.LATE_CHARGE_ASS_YN LATE_CHARGE_ASS_YN,
356 ST.SEL_ID SEL_ID,
357 PS.CLASS CLASS ,
358 SM.ID STREAM_TYPE_ID,
359 PS.STATUS STATUS
360 FROM AR_PAYMENT_SCHEDULES_ALL PS,
361 OKL_CNSLD_AR_STRMS_B ST,
362 OKL_CNSLD_AR_HDRS_B HD,
363 OKC_K_HEADERS_ALL_B CN,
364 OKL_CNSLD_AR_LINES_B LN,
365 OKL_STRM_TYPE_B SM
366 WHERE PS.CLASS IN ('INV', 'CM')
367 AND ST.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID
368 AND CN.ID = ST.KHR_ID
369 AND LN.ID = ST.LLN_ID
370 AND HD.ID = LN.CNR_ID
371 AND SM.ID = ST.STY_ID) last
372 ,okl_strm_elements_v sel
373 ,okl_k_headers_full_v khr
374 ,okc_rule_groups_b rgp
375 ,okc_rules_b rul
376 ,okc_rules_b rul_exm
377 ,okc_rules_b rul_hld
378 ,okl_late_policies_v lte
379 where last.sel_id = sel.id
380 and orig.class = 'INV'
381 and orig.status = 'OP'
382 and orig.consolidated_invoice_id = sel.source_id
383 and sel.source_table = 'OKL_CNSLD_AR_STRMS_V'
384 and orig.late_charge_ass_yn = 'Y'
385 and nvl(last.late_charge_ass_yn, 'N') = 'N'
386 and orig.contract_id = last.contract_id
387 and orig.contract_id = khr.id
388 and khr.id = rgp.dnz_chr_id
389 and rgp.rgd_code = 'LALCGR'
390 and rgp.id = rul.rgp_id
391 and khr.id = rul.dnz_chr_id
392 and rul.rule_information_category = 'LALCPR'
393 and rul.rule_information1 = lte.id
394 and (lte.late_policy_type_code = 'LCT' or lte.late_policy_type_code = 'CHG')
395 and khr.id = rul_exm.dnz_chr_id
396 and rgp.id = rul_exm.rgp_id
397 and rul_exm.rule_information_category = 'LALCEX'
398 and NVL(rul_exm.rule_information1, 'N') = 'N'
399 and not exists (select 1 from okl_strm_type_exempt_v sty_exm
400 where lte.id = sty_exm.lpo_id
401 and orig.stream_type_id = sty_exm.sty_id
402 and NVL(sty_exm.late_policy_exempt_yn, 'N') = 'Y')
403 and khr.id = rul_hld.dnz_chr_id
404 and rgp.id = rul_hld.rgp_id
405 and rul_hld.rule_information_category = 'LAHUDT'
406 and TRUNC(NVL(FND_DATE.canonical_to_date(rul_hld.rule_information1), sysdate - 1)) < trunc(sysdate)
407 -- and khr.authoring_org_id = NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
408 and khr.authoring_org_id = MO_GLOBAL.get_current_org_id
409 group by lte.name, khr.currency_code, orig.contract_id,orig.contract_number
410 ,orig.consolidated_invoice_id, orig.consolidated_invoice_number
411 , lte.late_chrg_grace_period
412 having (trunc(max(last.due_date)) + nvl(lte.late_chrg_grace_period, 0)) < trunc(sysdate);
413
414
415 CURSOR l_late_policy_cur(l_id IN VARCHAR2) IS
416 SELECT LATE_POLICY_TYPE_CODE
417 , LATE_CHRG_ALLOWED_YN
418 , LATE_CHRG_FIXED_YN
419 , NVL(LATE_CHRG_AMOUNT, 0) LATE_CHRG_AMOUNT
420 , NVL(LATE_CHRG_RATE, 0) LATE_CHRG_RATE
421 , NVL(LATE_CHRG_GRACE_PERIOD,0) LATE_CHRG_GRACE_PERIOD
422 , NVL(LATE_CHRG_MINIMUM_BALANCE, 0) LATE_CHRG_MINIMUM_BALANCE
423 , NVL(MINIMUM_LATE_CHARGE, 0) MINIMUM_LATE_CHARGE
424 , NVL(MAXIMUM_LATE_CHARGE, 9999999999) MAXIMUM_LATE_CHARGE
425 FROM OKL_LATE_POLICIES_V
426 WHERE NAME = l_id;
427
428 -- vdamerla: bug 5474844 Get the late charge data
429
430 -- cursor for consolidated invoices
431
432 CURSOR l_cons_lsm_cur(l_consolidated_invoice_id IN NUMBER, l_khr_id in number) IS
433 SELECT lsm.id
434 , LATE_CHARGE_ASS_YN
435 , LATE_CHARGE_ASSESS_DATE
436 FROM OKL_CNSLD_AR_STRMS_B lsm,
437 OKL_CNSLD_AR_LINES_B lln,
438 OKL_CNSLD_AR_HDRS_B cnr
439 WHERE cnr.id = l_consolidated_invoice_id
440 and lln.cnr_id = cnr.id
441 and lsm.lln_id = lln.id
442 and lsm.KHR_ID = l_khr_id
443 FOR UPDATE OF LATE_CHARGE_ASS_YN, LATE_CHARGE_ASSESS_DATE;
444
445
446 -- cursor for AR invoices
447 CURSOR l_AR_lsm_cur(l_id IN NUMBER,l_contract_id in number) IS
448 SELECT ID
449 , LATE_CHARGE_ASS_YN
450 , LATE_CHARGE_ASSESS_DATE
451 FROM OKL_BPD_AR_INV_LINES_V
452 WHERE RECEIVABLES_INVOICE_ID = l_id
453 AND CONTRACT_ID=l_contract_id
454 FOR UPDATE OF LATE_CHARGE_ASS_YN, LATE_CHARGE_ASSESS_DATE;
455
456 ------------------------------------------------------------
457 -- Transaction Number Cursor
458 ------------------------------------------------------------
459 CURSOR c_tran_num_csr IS
460 SELECT okl_sif_seq.nextval
461 FROM dual;
462
463 ------------------------------------------------------------
464 -- Stream Type Constants
465 ------------------------------------------------------------
466 cns_late_fee constant varchar2(50) := 'LATE_FEE';
467 cns_late_interest constant varchar2(50) := 'LATE_INTEREST';
468 cns_late_charge_payable constant varchar2(50) := 'INVESTOR_LATE_FEE_PAYABLE';
469 cns_AR_source_table constant varchar2(25) := 'RA_CUSTOMER_TRX_LINES_ALL';
470 cns_cons_source_table constant varchar2(25) := 'OKL_CNSLD_AR_STRMS_V';
471
472 /* ------------------------------------------------------------
473 -- Stream Id Cursor
474 ------------------------------------------------------------
475 CURSOR l_sty_id_cur(cp_name IN VARCHAR2) IS
476 SELECT id FROM okl_strm_type_v where name = cp_name;
477 */
478 ------------------------------------------------------------
479 -- Stream Cursor
480 ------------------------------------------------------------
481 -- cursor for AR invoices
482 CURSOR l_AR_stream_csr(cp_khr_id IN NUMBER
483 ,cp_sty_id IN NUMBER) IS
484 SELECT stm.id
485 FROM okl_streams_v stm
486 WHERE stm.khr_id = cp_khr_id
487 AND stm.sty_id = cp_sty_id
488 AND stm.say_code = 'CURR'
489 AND stm.active_yn = 'Y';
490
491 -- cursor for consolidated invoices
492 CURSOR l_cons_stream_csr(cp_khr_id IN NUMBER
493 ,cp_kle_id IN NUMBER
494 ,cp_sty_id IN NUMBER) IS
495 SELECT stm.id
496 FROM okl_streams_v stm
497 WHERE stm.khr_id = cp_khr_id
498 AND nvl(stm.kle_id, -99) = nvl(cp_kle_id, -99)
499 AND stm.sty_id = cp_sty_id
500 AND stm.say_code = 'CURR'
501 AND stm.active_yn = 'Y';
502 -- second cursor for consolidated invoices
503 CURSOR l_stream_csr(cp_khr_id IN NUMBER
504 ,cp_sty_id IN NUMBER) IS
505 SELECT stm.id
506 FROM okl_streams_v stm
507 WHERE stm.khr_id = cp_khr_id
508 AND stm.sty_id = cp_sty_id
509 AND stm.say_code = 'CURR'
510 AND stm.active_yn = 'Y';
511
512
513
514 ------------------------------------------------------------
515 -- Stream Element Line Number Cursor
516 ------------------------------------------------------------
517 CURSOR l_stream_line_nbr_csr(cp_stm_id IN NUMBER) IS
518 SELECT max(se_line_number) se_line_number
519 FROM okl_strm_elements_v
520 WHERE stm_id = cp_stm_id;
521
522
523
524 -- vdamerla bug 5474844
525 ------------------------------------------------------------
526 -- Receivable invoice cursor
527 ------------------------------------------------------------
528
529
530
531 CURSOR l_receivables_inv_csr (cp_contract_id number, cp_trx_number varchar2)
532 is
533 select RECEIVABLES_INVOICE_ID, CONTRACT_ID
534 from okl_bpd_ar_inv_lines_v
535 where contract_id=cp_contract_id
536 and trx_number=cp_trx_number;
537
538
539 ------------------------------------------------------------
540 -- Securitized streams Cursor
541 ------------------------------------------------------------
542
543
544 CURSOR c_sec_strm_cons_csr(l_consolidated_invoice_id IN NUMBER, l_khr_id in number) IS
545 select lsm.id cnsld_strm_id, pol.khr_id, ps.amount_due_remaining,lsm.kle_id
546 from
547 okl_cnsld_ar_strms_b lsm
548 , okl_cnsld_ar_hdrs_b cnr
549 , okl_cnsld_ar_lines_b lln
550 , okl_pool_contents_v pk
551 -- , okl_pools_v pol
552 , okl_pools pol
553 , ar_payment_schedules_all ps
554 , okl_strm_elements_v sel
555 where cnr.id = l_consolidated_invoice_id
556 and lln.cnr_id = cnr.id
557 and lsm.lln_id = lln.id
558 and lsm.khr_id = l_khr_id
559 and lsm.khr_id = pk.khr_id
560 and nvl(lsm.kle_id, -99) = nvl(pk.kle_id, -99)
561 and lsm.sty_id = pk.sty_id
562 and pk.pol_id = pol.id
563 and trunc(cnr.date_consolidated) between trunc(pk.streams_from_date) and trunc(pk.streams_to_date)
564 and lsm.receivables_invoice_id = ps.customer_trx_id
565 and ps.class = 'INV'
566 and ps.status = 'OP'
567 and lsm.sel_id = sel.id
568 and pk.status_code = 'ACTIVE' --Added by bkatraga for bug 6983321
569 and pk.stm_id = sel.stm_id
570 AND pk.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE; --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
571
572
573 CURSOR c_sec_strm_AR_csr(cp_contract_id in number, cp_con_inv_number IN VARCHAR2) IS
574 SELECT arl.id cnsld_strm_id
575 ,pol.khr_id, pk.kle_id kle_id
576 FROM okl_bpd_ar_inv_lines_v arl
577 ,okl_bpd_ar_invoices_v arv
578 ,okl_pool_contents_v pk
579 ,okl_pools pol
580 WHERE arl.trx_number = cp_con_inv_number
581 AND arl.contract_id = cp_contract_id
582 AND arl.contract_id = pk.khr_id
583 AND nvl(arl.contract_line_id, -99) = nvl(pk.kle_id, -99)
584 AND arl.sty_id = pk.sty_id
585 AND pk.pol_id = pol.id
586 AND arv.invoice_id = arl.invoice_id
587 AND trunc(arv.date_consolidated) between trunc(pk.streams_from_date) and trunc(pk.streams_to_date)
588 AND pk.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE; --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
589
590 ------------------------------------------------------------
591 -- Stream Type of Late Invoice Cursor
592 ------------------------------------------------------------
593 --rkuttiya modified this cursor for R12 B Billing Architecture
594 --modified view name, where clause
595
596 CURSOR c_strm_purpose(cp_stream_id IN NUMBER) IS
597 SELECT sty.stream_type_purpose
598 FROM okl_bpd_ar_inv_lines_v arl
599 ,okl_strm_type_b sty
600 WHERE arl.id = cp_stream_id
601 AND arl.sty_id = sty.id;
602
603 ------------------------------------------------------------
604 -- Source id of stream element of Late Invoice Cursor
605 ------------------------------------------------------------
606 cursor c_AR_source_sel(cp_contract_number varchar2, cp_trx_number varchar2) is
607 SELECT receivables_invoice_id
608 FROM okl_bpd_ar_inv_lines_v
609 WHERE contract_number = cp_contract_number
610 AND trx_number = cp_trx_number;
611 ---
612 --- END DECLARE
613 ---
614 BEGIN
615 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
616 G_PKG_NAME,
617 p_init_msg_list,
618 l_api_version,
619 p_api_version,
620 '_PVT',
621 x_return_status);
622
623
624 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
625 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
626 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
627 RAISE OKL_API.G_EXCEPTION_ERROR;
628 END IF;
629
630 l_init_msg_list := p_init_msg_list ;
631
632 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Start of Calculate Late Charges.');
633
634 SELECT FND_GLOBAL.USER_ID
635 ,FND_GLOBAL.LOGIN_ID
636 ,NVL(DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),null)
637 INTO l_last_updated_by
638 ,l_last_update_login
639 ,l_request_id
640 FROM dual;
641
642 /* -- for bug 7295166 - start
643 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Subsequent late charges');
644 FOR l_inv_cur IN l_late_invs_cur1
645 LOOP
646 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Processing:Never Late: Receivables invoice => '||l_inv_cur.consolidated_invoice_number||
647 ' ,due date=> '||l_inv_cur.DUE_DATE||' and Amount=> '||l_inv_cur.AMOUNT_DUE_REMAINING
648 ||' ,Contract => '||l_inv_cur.CONTRACT_NUMBER );
649 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Late policy => ' || l_inv_cur.late_policy
650 || ' ,Contract currency => ' || l_inv_cur.currency_code);
651
652 -- vdamerla bug 5474844
653 open l_receivables_inv_csr(l_inv_cur.CONTRACT_ID,l_inv_cur.consolidated_invoice_number);
654 fetch l_receivables_inv_csr into l_rec_inv_id,l_rec_inv_contract_id;
655 close l_receivables_inv_csr;
656
657 if l_inv_cur.CONSOLIDATED_INVOICE_ID is not null then
658 l_rec_inv_id := l_inv_cur.CONSOLIDATED_INVOICE_ID;
659 -- Get the amount_due_remaining
660 OKL_BILLING_UTIL_PVT.get_contract_invoice_balance(
661 p_api_version => 1.0
662 ,p_init_msg_list => OKL_API.G_FALSE
663 ,p_contract_number => l_inv_cur.CONTRACT_NUMBER
664 ,p_trx_number => l_inv_cur.consolidated_invoice_number
665 ,x_return_status => x_return_status
666 ,x_msg_count => x_msg_count
667 ,x_msg_data => x_msg_data
668 ,x_remaining_amount => l_amt_due_remaining);
669 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
670 l_error_flag := TRUE;
671 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Error fetching Consolidated Invoice Balance for contract: '
672 ||l_inv_cur.contract_number
673 ||' consolidated_invoice_number: '||l_inv_cur.consolidated_invoice_number);
674 ELSE
675 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Successfully fetched Consolidated Invoice Balance for contract: '
676 ||l_inv_cur.contract_number
677 ||' consolidated_invoice_number: '||l_inv_cur.consolidated_invoice_number
678 ||'Invoice Balance:'|| l_amt_due_remaining);
679 END IF;
680
681 else
682 l_amt_due_remaining := l_inv_cur.AMOUNT_DUE_REMAINING;
683 FND_FILE.PUT_LINE (FND_FILE.LOG, 'AR Invoice Balance => '||l_amt_due_remaining);
684 end if;
685
686 l_due_date := l_inv_cur.DUE_DATE;
687 l_khr_id := l_inv_cur.CONTRACT_ID;
688 l_contract_currency := l_inv_cur.currency_code;
689 l_late_policy := l_inv_cur.late_policy;
690
691
692 IF (nvl(l_late_policy, 'xxx') <> nvl(l_prev_late_policy, 'yyy') or
693 nvl(l_contract_currency, 'aaa') <> nvl(l_prev_contract_currency, 'bbb')) THEN
694 FOR l_lpo_cur IN l_late_policy_cur(l_late_policy)
695 LOOP
696 --start code pgomes 12/18/2002
697 IF (l_func_currency <> NVL(l_contract_currency, '000')) THEN
698 --convert late_chrg_amount to contract currency
699 OKL_ACCOUNTING_UTIL.convert_to_contract_currency(
700 p_khr_id => l_khr_id,
701 p_from_currency => l_func_currency,
702 p_transaction_date => sysdate,
703 p_amount => l_lpo_cur.late_chrg_amount,
704 x_contract_currency => l_contract_currency,
705 x_currency_conversion_type => l_currency_conversion_type,
706 x_currency_conversion_rate => l_currency_conversion_rate,
707 x_currency_conversion_date => l_currency_conversion_date,
708 x_converted_amount => l_late_chrg_amount);
709
710 l_late_chrg_amount := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(l_late_chrg_amount, l_contract_currency);
711
712 --convert minimum_late_charge to contract currency
713 OKL_ACCOUNTING_UTIL.convert_to_contract_currency(
714 p_khr_id => l_khr_id,
715 p_from_currency => l_func_currency,
716 p_transaction_date => sysdate,
717 p_amount => l_lpo_cur.minimum_late_charge,
718 x_contract_currency => l_contract_currency,
719 x_currency_conversion_type => l_currency_conversion_type,
720 x_currency_conversion_rate => l_currency_conversion_rate,
721 x_currency_conversion_date => l_currency_conversion_date,
722 x_converted_amount => l_minimum_late_charge);
723
724 l_minimum_late_charge := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(l_minimum_late_charge, l_contract_currency);
725
726 --convert maximum_late_charge to contract currency
727 OKL_ACCOUNTING_UTIL.convert_to_contract_currency(
728 p_khr_id => l_khr_id,
729 p_from_currency => l_func_currency,
730 p_transaction_date => sysdate,
731 p_amount => l_lpo_cur.maximum_late_charge,
732 x_contract_currency => l_contract_currency,
733 x_currency_conversion_type => l_currency_conversion_type,
734 x_currency_conversion_rate => l_currency_conversion_rate,
735 x_currency_conversion_date => l_currency_conversion_date,
736 x_converted_amount => l_maximum_late_charge);
737
738 l_maximum_late_charge := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(l_maximum_late_charge, l_contract_currency);
739
740 --convert late_chrg_minimum_balance to contract currency
741 OKL_ACCOUNTING_UTIL.convert_to_contract_currency(
742 p_khr_id => l_khr_id,
743 p_from_currency => l_func_currency,
744 p_transaction_date => sysdate,
745 p_amount => l_lpo_cur.late_chrg_minimum_balance,
746 x_contract_currency => l_contract_currency,
747 x_currency_conversion_type => l_currency_conversion_type,
748 x_currency_conversion_rate => l_currency_conversion_rate,
749 x_currency_conversion_date => l_currency_conversion_date,
750 x_converted_amount => l_late_chrg_minimum_balance);
751
752 l_late_chrg_minimum_balance := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(l_late_chrg_minimum_balance, l_contract_currency);
753 ELSE
754 l_late_chrg_amount := l_lpo_cur.late_chrg_amount;
755 l_minimum_late_charge := l_lpo_cur.minimum_late_charge;
756 l_maximum_late_charge := l_lpo_cur.maximum_late_charge;
757 l_late_chrg_minimum_balance := l_lpo_cur.late_chrg_minimum_balance;
758 END IF;
759
760 l_late_chrg_fixed_yn := l_lpo_cur.late_chrg_fixed_yn;
761 l_late_chrg_allowed_yn := l_lpo_cur.late_chrg_allowed_yn;
762 l_late_chrg_rate := l_lpo_cur.late_chrg_rate;
763
764 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Processing: LATE POLICY TYPE CODE => '||l_lpo_cur.LATE_POLICY_TYPE_CODE||
765 ' LATE CHRG MINIMUM BALANCE=> '||l_late_chrg_minimum_balance
766 ||' ,LATE CHRG GRACE PERIOD=> '||l_lpo_cur.LATE_CHRG_GRACE_PERIOD);
767 END LOOP;
768 l_prev_late_policy := l_late_policy;
769 l_prev_contract_currency := l_contract_currency;
770 END IF;
771 --end code pgomes 12/18/2002
772
773 -- pgomes 12/18/2002 start, changed code to consider converted charges
774
775 l_error_flag := FALSE;
776 l_late_stm_id := null;
777 IF(nvl(l_late_chrg_minimum_balance,0) <= l_amt_due_remaining) THEN
778
779 IF(l_late_chrg_fixed_yn = 'Y') THEN
780 l_late_charge_amt := l_late_chrg_amount;
781 l_investor_disb_flag := 'N';
782 ELSE --IF(l_lpo_cur.LATE_CHRG_FIXED_YN = 'N') THEN
783 l_amount := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(l_amt_due_remaining*(l_late_chrg_rate/100), l_contract_currency);
784 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Calculated late charge => '||l_amount);
785
786 IF(l_amount < nvl(l_minimum_late_charge,0)) THEN
787 l_late_charge_amt := l_minimum_late_charge;
788 l_investor_disb_flag := 'N';
789 ELSIF(l_amount > nvl(l_maximum_late_charge,0)) THEN
790 l_late_charge_amt := l_maximum_late_charge;
791 l_investor_disb_flag := 'N';
792 ELSE
793 l_late_charge_amt := l_amount;
794 l_investor_disb_flag := 'Y';
795 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_investor_disb_flag is set to '||l_investor_disb_flag );
796 END IF;
797 END IF;
798 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Applied late charge => '||l_late_charge_amt);
799 -- pgomes 12/18/2002 end, changed code to consider converted charges
800
801
802 ----------------------------------------------------------------
803 --PROCESSING FOR LATE CHARGE
804 ----------------------------------------------------------------
805
806 --get stream type id
807 l_sty_id := null;
808
809 Okl_Streams_Util.get_primary_stream_type(
810 p_khr_id => l_khr_id,
811 p_primary_sty_purpose => cns_late_fee,
812 x_return_status => l_return_status,
813 x_primary_sty_id => l_sty_id );
814
815 IF (l_return_status = 'S' ) THEN
816 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream Id for purpose LATE_FEE retrieved.');
817 ELSE
818 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Could not retrieve Stream Id for purpose LATE_FEE.');
819 l_error_flag := TRUE;
820 END IF;
821
822
823 --check for stream type id
824 IF NOT (l_error_flag) THEN
825 l_stm_id := null;
826 l_se_line_number := null;
827 -- vdamerla: bug 5474844 If consolidated invoices
828 if l_inv_cur.CONSOLIDATED_INVOICE_ID is not null then
829 OPEN l_stream_csr(l_inv_cur.contract_id, l_sty_id);
830 FETCH l_stream_csr INTO l_stm_id;
831 CLOSE l_stream_csr;
832 else
833 OPEN l_AR_stream_csr(l_inv_cur.contract_id, l_sty_id);
834 FETCH l_AR_stream_csr INTO l_stm_id;
835 CLOSE l_AR_stream_csr;
836 end if;
837
838 --create stream for late charge
839 IF (l_stm_id IS NULL) THEN
840 l_stmv_rec := l_init_stmv_rec;
841
842 OPEN c_tran_num_csr;
843 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
844 CLOSE c_tran_num_csr;
845
846 l_stmv_rec.sty_id := l_sty_id;
847 l_stmv_rec.khr_id := l_inv_cur.contract_id;
848 -- l_stmv_rec.sgn_code := 'MANL'; -- Bug 6472228
849 l_stmv_rec.sgn_code := G_LATE_SGN_CODE; -- Bug 6472228
850 l_stmv_rec.say_code := 'CURR';
851 l_stmv_rec.active_yn := 'Y';
852 l_stmv_rec.date_current := sysdate;
853 l_stmv_rec.comments := 'LATE FEE BILLING';
854
855 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Creating LATE FEE Stream');
856
857 Okl_Streams_Pub.create_streams(
858 p_api_version => p_api_version,
859 p_init_msg_list => p_init_msg_list,
860 x_return_status => x_return_status,
861 x_msg_count => x_msg_count,
862 x_msg_data => x_msg_data,
863 p_stmv_rec => l_stmv_rec,
864 x_stmv_rec => lx_stmv_rec);
865
866 l_stm_id := lx_stmv_rec.id;
867 l_se_line_number := 1;
868 --fix for bug 4439728
869 --save the parent stm id so that if creation of payable stm is unsuccessful
870 --then the parent stm can be invalidated
871 l_late_stm_id := l_stm_id;
872
873
874 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
875 l_error_flag := TRUE;
876 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Creating Stream for LATE FEE');
877 ELSE
878 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- SUCCESS: Creating Stream for LATE FEE');
879 END IF;
880 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream element line number => ' || l_se_line_number);
881 ELSE
882 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream for LATE FEE found');
883 open l_stream_line_nbr_csr(l_stm_id);
884 fetch l_stream_line_nbr_csr into l_se_line_number;
885 close l_stream_line_nbr_csr;
886 l_se_line_number := nvl(l_se_line_number,0) + 1;
887 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream element line number => ' || l_se_line_number);
888 END IF;
889
890 --create stream element for late charge
891 IF (l_stm_id IS NOT NULL) THEN
892 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Creating Stream Element for LATE FEE');
893 l_selv_rec := l_init_selv_rec;
894
895 l_selv_rec.stm_id := l_stm_id;
896 l_selv_rec.SE_LINE_NUMBER := l_se_line_number;
897 l_selv_rec.STREAM_ELEMENT_DATE := sysdate;
898 l_selv_rec.AMOUNT := l_late_charge_amt;
899 l_selv_rec.COMMENTS := 'LATE FEE BILLING ELEMENTS';
900 l_selv_rec.ACCRUED_YN := 'Y';
901
902
903 -- vdamerla bug 5474844 Added the following lines to get the source_id
904
905 if l_inv_cur.consolidated_invoice_id is not null then
906 l_selv_rec.source_id:=l_inv_cur.consolidated_invoice_id;
907 l_selv_rec.source_table := cns_cons_source_table;
908 else
909 OPEN c_AR_source_sel(l_inv_cur.contract_number, l_inv_cur.consolidated_invoice_number);
910 FETCH c_AR_source_sel INTO l_selv_rec.source_id;
911 CLOSE c_AR_source_sel;
912 l_selv_rec.source_table := cns_AR_source_table;
913 end if;
914
915
916
917
918
919 Okl_Sel_Pvt.insert_row(
920 p_api_version,
921 p_init_msg_list,
922 x_return_status,
923 x_msg_count,
924 x_msg_data,
925 l_selv_rec,
926 lx_selv_rec);
927
928 l_sel_id := lx_selv_rec.id;
929
930 --l_sec_rec_inv_id := lx_selv_rec.source_id;
931
932 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
933 l_error_flag := TRUE;
934 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Error Creating Stream Element for Contract: '
935
936 ||l_inv_cur.contract_number
937 ||' Stream Purpose: '||cns_late_fee
938 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
939 ||' Amount: '||l_late_charge_amt
940 ||'source_id:'||l_selv_rec.source_id);
941 ELSE
942
943 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Created Late Fee Stream Element for Contract: '
944 ||l_inv_cur.contract_number
945 ||' Stream Purpose: '||cns_late_fee
946 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
947 ||' Amount: '||l_late_charge_amt
948 );
949 END IF;
950 END IF;
951
952 ----------------------------------------------------------------
953 --PROCESSING FOR LATE CHARGE PAYABLE TO INVESTOR
954 ----------------------------------------------------------------
955 FND_FILE.PUT_LINE (FND_FILE.LOG, '(l_late_invs_cur) l_investor_disb_flag:'||l_investor_disb_flag);
956
957 IF nvl(l_investor_disb_flag,'N') = 'Y' then
958 IF l_inv_cur.consolidated_invoice_id is null then
959 FOR cur_sec_strm IN c_sec_strm_AR_csr(l_inv_cur.contract_id, l_inv_cur.consolidated_invoice_number)
960 LOOP
961 --get stream type id
962 l_sty_id := null;
963
964 Okl_Streams_Util.get_primary_stream_type(
965 p_khr_id => cur_sec_strm.khr_id,
966 p_primary_sty_purpose => cns_late_charge_payable,
967 x_return_status => l_return_status,
968 x_primary_sty_id => l_sty_id );
969
970 IF (l_return_status = 'S' ) THEN
971 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream Id for purpose INVESTOR_LATE_FEE_PAYABLE retrieved.');
972 ELSE
973 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Could not retrieve Stream Id for purpose INVESTOR_LATE_FEE_PAYABLE.');
974 l_error_flag := TRUE;
975 END IF;
976
977 --check for stream
978 IF NOT (l_error_flag) THEN
979 l_stm_id := null;
980 l_se_line_number := null;
981
982 OPEN l_AR_stream_csr(l_inv_cur.contract_id, l_sty_id);
983 FETCH l_AR_stream_csr INTO l_stm_id;
984 CLOSE l_AR_stream_csr;
985
986
987 --create stream for late charge payable
988 IF (l_stm_id IS NULL) THEN
989 l_stmv_rec := l_init_stmv_rec;
990
991 OPEN c_tran_num_csr;
992 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
993 CLOSE c_tran_num_csr;
994
995 l_stmv_rec.sty_id := l_sty_id;
996 l_stmv_rec.khr_id := l_inv_cur.contract_id;
997 l_stmv_rec.kle_id := cur_sec_strm.kle_id;
998 -- l_stmv_rec.sgn_code := 'MANL'; -- Bug 6472228
999 l_stmv_rec.sgn_code := G_LATE_SGN_CODE; -- Bug 6472228
1000 l_stmv_rec.say_code := 'CURR';
1001 l_stmv_rec.active_yn := 'Y';
1002 l_stmv_rec.date_current := sysdate;
1003 l_stmv_rec.comments := 'INVESTOR LATE FEE PAYABLE';
1004 IF (cur_sec_strm.khr_id IS NOT NULL) THEN
1005 l_stmv_rec.source_id := cur_sec_strm.khr_id;
1006 l_stmv_rec.source_table := 'OKL_K_HEADERS';
1007 END IF;
1008
1009 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Creating INVESTOR LATE FEE PAYABLE Stream');
1010
1011 Okl_Streams_Pub.create_streams(
1012 p_api_version => p_api_version,
1013 p_init_msg_list => p_init_msg_list,
1014 x_return_status => x_return_status,
1015 x_msg_count => x_msg_count,
1016 x_msg_data => x_msg_data,
1017 p_stmv_rec => l_stmv_rec,
1018 x_stmv_rec => lx_stmv_rec);
1019
1020 l_stm_id := lx_stmv_rec.id;
1021 l_se_line_number := 1;
1022
1023 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream element line number => ' || l_se_line_number);
1024 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1025 l_error_flag := TRUE;
1026 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Creating Stream for INVESTOR LATE FEE PAYABLE');
1027 ELSE
1028 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- SUCCESS: Creating Stream for INVESTOR LATE FEE PAYABLE');
1029 END IF;
1030 ELSE
1031 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream for INVESTOR LATE FEE PAYABLE found');
1032 open l_stream_line_nbr_csr(l_stm_id);
1033 fetch l_stream_line_nbr_csr into l_se_line_number;
1034 close l_stream_line_nbr_csr;
1035 l_se_line_number := nvl(l_se_line_number,0) + 1;
1036 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream element line number => ' || l_se_line_number);
1037 END IF;
1038
1039 --create stream element for late charge payable
1040 IF (l_stm_id IS NOT NULL) THEN
1041 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Creating INVESTOR LATE FEE PAYABLE Stream Elements');
1042 l_selv_rec := l_init_selv_rec;
1043
1044 l_selv_rec.stm_id := l_stm_id;
1045 l_selv_rec.SE_LINE_NUMBER := l_se_line_number;
1046 l_selv_rec.STREAM_ELEMENT_DATE := sysdate;
1047 l_selv_rec.AMOUNT := l_late_charge_amt;
1048 l_selv_rec.COMMENTS := 'INVESTOR LATE FEE PAYABLE ELEMENTS';
1049 l_selv_rec.ACCRUED_YN := 'Y';
1050 l_selv_rec.sel_id := l_sel_id;
1051 IF (cur_sec_strm.khr_id IS NOT NULL) THEN
1052 l_selv_rec.source_id := cur_sec_strm.khr_id;
1053 l_selv_rec.source_table := 'OKL_K_HEADERS';
1054 END IF;
1055
1056 Okl_Sel_Pvt.insert_row(
1057 p_api_version,
1058 p_init_msg_list,
1059 x_return_status,
1060 x_msg_count,
1061 x_msg_data,
1062 l_selv_rec,
1063 lx_selv_rec);
1064
1065 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1066 l_error_flag := TRUE;
1067 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Error Creating Payable Stream Element for Contract: '
1068 ||l_inv_cur.contract_number
1069 ||' Stream Purpose: '||cns_late_fee
1070 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
1071 ||' Amount: '||l_late_charge_amt);
1072 ELSE
1073
1074 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Created Investor Late Charge Payable Stream Element for Contract: '
1075 ||l_inv_cur.contract_number
1076 ||' Stream Purpose: '||cns_late_fee
1077 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
1078 ||' Amount: '||l_late_charge_amt
1079 );
1080 END IF;
1081 END IF;
1082 ELSE
1083 --fix for bug 4439728
1084 --deactivate late charge stream as payable stream creation was unsuccessful
1085 Update okl_streams
1086 set say_code = 'HIST'
1087 ,active_yn = 'N'
1088 ,last_updated_by = l_last_updated_by
1089 ,last_update_date = sysdate
1090 ,last_update_login = l_last_update_login
1091 ,request_id = l_request_id
1092 ,date_history = SYSDATE
1093 WHERE id = l_late_stm_id;
1094
1095 IF (SQL%NOTFOUND) THEN
1096 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ... Late charge stream not deactivated successfully as payable stream creation was unsuccessful.');
1097 ELSE
1098 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ... Late charge stream deactivated successfully as payable stream creation was unsuccessful.');
1099 END IF;
1100 END IF;
1101 END LOOP;
1102 END IF; -- if consolidated_invoice_id is null
1103
1104 IF l_inv_cur.consolidated_invoice_id is not null then
1105 FOR cur_sec_strm IN c_sec_strm_cons_csr(l_inv_cur.consolidated_invoice_id, l_inv_cur.contract_id)
1106 LOOP
1107 --get stream type id
1108 l_sty_id := null;
1109
1110 Okl_Streams_Util.get_primary_stream_type(
1111 p_khr_id => cur_sec_strm.khr_id,
1112 p_primary_sty_purpose => cns_late_charge_payable,
1113 x_return_status => l_return_status,
1114 x_primary_sty_id => l_sty_id );
1115
1116 IF (l_return_status = 'S' ) THEN
1117 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream Id for purpose INVESTOR_LATE_FEE_PAYABLE retrieved.');
1118 ELSE
1119 --Added by bkatraga for bug 5601733
1120 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Could not retrieve Stream Id for purpose INVESTOR_LATE_FEE_PAYABLE while processing Consolidated invoice => '||
1121 l_inv_cur.consolidated_INVOICE_NUMBER);
1122 --end bkatraga
1123 l_error_flag := TRUE;
1124 END IF;
1125
1126 --check for stream
1127 IF NOT (l_error_flag) THEN
1128 l_stm_id := null;
1129 l_se_line_number := null;
1130
1131 --Added by bkatraga for bug 5601733
1132 OPEN l_cons_stream_csr(l_inv_cur.contract_id, cur_sec_strm.kle_id,l_sty_id);
1133 FETCH l_cons_stream_csr INTO l_stm_id;
1134 CLOSE l_cons_stream_csr;
1135 --end bkatraga
1136
1137 --create stream for late charge payable
1138 IF ((l_stm_id IS NULL) or (l_stm_id = -99)) THEN --Added or clause by bkatraga for bug 5601733
1139 l_stmv_rec := l_init_stmv_rec;
1140
1141 OPEN c_tran_num_csr;
1142 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
1143 CLOSE c_tran_num_csr;
1144
1145 l_stmv_rec.sty_id := l_sty_id;
1146 l_stmv_rec.khr_id := l_inv_cur.contract_id;
1147 l_stmv_rec.kle_id := cur_sec_strm.kle_id; --Added by bkatraga for bug 5601733
1148 -- l_stmv_rec.sgn_code := 'MANL'; -- Bug 6472228
1149 l_stmv_rec.sgn_code := G_LATE_SGN_CODE; -- Bug 6472228
1150 l_stmv_rec.say_code := 'CURR';
1151 l_stmv_rec.active_yn := 'Y';
1152 l_stmv_rec.date_current := sysdate;
1153 l_stmv_rec.comments := 'INVESTOR LATE FEE PAYABLE';
1154 IF (cur_sec_strm.khr_id IS NOT NULL) THEN
1155 l_stmv_rec.source_id := cur_sec_strm.khr_id;
1156 l_stmv_rec.source_table := 'OKL_K_HEADERS';
1157 END IF;
1158
1159 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Creating INVESTOR LATE FEE PAYABLE Stream');
1160
1161 Okl_Streams_Pub.create_streams(
1162 p_api_version => p_api_version,
1163 p_init_msg_list => p_init_msg_list,
1164 x_return_status => x_return_status,
1165 x_msg_count => x_msg_count,
1166 x_msg_data => x_msg_data,
1167 p_stmv_rec => l_stmv_rec,
1168 x_stmv_rec => lx_stmv_rec);
1169
1170 l_stm_id := lx_stmv_rec.id;
1171 l_se_line_number := 1;
1172
1173 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream element line number => ' || l_se_line_number);
1174 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1175 l_error_flag := TRUE;
1176 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Creating Stream for INVESTOR LATE FEE PAYABLE');
1177 ELSE
1178 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- SUCCESS: Creating Stream for INVESTOR LATE FEE PAYABLE');
1179 END IF;
1180 ELSE
1181 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream for INVESTOR LATE FEE PAYABLE found');
1182 open l_stream_line_nbr_csr(l_stm_id);
1183 fetch l_stream_line_nbr_csr into l_se_line_number;
1184 close l_stream_line_nbr_csr;
1185 l_se_line_number := nvl(l_se_line_number,0) + 1;
1186 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream element line number => ' || l_se_line_number);
1187 END IF;
1188
1189 --create stream element for late charge payable
1190 IF (l_stm_id IS NOT NULL) THEN
1191 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Creating INVESTOR LATE FEE PAYABLE Stream Elements');
1192 l_selv_rec := l_init_selv_rec;
1193 l_selv_rec.stm_id := l_stm_id;
1194 l_selv_rec.SE_LINE_NUMBER := l_se_line_number;
1195 l_selv_rec.STREAM_ELEMENT_DATE := sysdate;
1196 --Added by bkatraga for bug 5601733
1197 l_selv_rec.AMOUNT := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(cur_sec_strm.amount_due_remaining*(l_late_chrg_rate/100), l_contract_currency);
1198 --end bkatraga
1199 l_selv_rec.COMMENTS := 'INVESTOR LATE FEE PAYABLE ELEMENTS';
1200 l_selv_rec.ACCRUED_YN := 'Y';
1201 l_selv_rec.sel_id := l_sel_id;
1202 IF (cur_sec_strm.khr_id IS NOT NULL) THEN
1203 l_selv_rec.source_id := cur_sec_strm.khr_id;
1204 l_selv_rec.source_table := 'OKL_K_HEADERS';
1205 END IF;
1206
1207 Okl_Sel_Pvt.insert_row(
1208 p_api_version,
1209 p_init_msg_list,
1210 x_return_status,
1211 x_msg_count,
1212 x_msg_data,
1213 l_selv_rec,
1214 lx_selv_rec);
1215
1216 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1217 l_error_flag := TRUE;
1218 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Error Creating Payable Stream Element for Contract: '
1219 ||l_inv_cur.contract_number
1220 ||' Stream Purpose: '||cns_late_fee
1221 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
1222 ||' Amount: '||l_late_charge_amt);
1223 ELSE
1224
1225 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Created Investor Late Charge Payable Stream Element for Contract: '
1226 ||l_inv_cur.contract_number
1227 ||' Stream Purpose: '||cns_late_fee
1228 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
1229 ||' Amount: '||l_late_charge_amt
1230 );
1231 END IF;
1232 END IF;
1233 ELSE
1234 --fix for bug 4439728
1235 --deactivate late charge stream as payable stream creation was unsuccessful
1236 Update okl_streams
1237 set say_code = 'HIST'
1238 ,active_yn = 'N'
1239 ,last_updated_by = l_last_updated_by
1240 ,last_update_date = sysdate
1241 ,last_update_login = l_last_update_login
1242 ,request_id = l_request_id
1243 ,date_history = SYSDATE
1244 WHERE id = l_late_stm_id;
1245
1246 IF (SQL%NOTFOUND) THEN
1247 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ... Late charge stream not deactivated successfully as payable stream creation was unsuccessful.');
1248 ELSE
1249 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ... Late charge stream deactivated successfully as payable stream creation was unsuccessful.');
1250 END IF;
1251
1252 END IF;
1253 END LOOP;
1254 END IF; -- if consolidated_invoice_id is not null
1255 END IF; -- l_investor_disb_flag
1256
1257
1258
1259 -- Start of wraper code generated automatically by Debug code generator for Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices
1260 IF NOT(l_error_flag) THEN
1261 -- if consolidated Invoice
1262 IF l_inv_cur.consolidated_invoice_id is not null then
1263 FOR l_lsm IN l_cons_lsm_cur(l_inv_cur.consolidated_invoice_id , l_khr_id) --Added by bkatraga for bug 5601733
1264 LOOP
1265 Update okl_cnsld_ar_strms_b
1266 set LATE_CHARGE_ASS_YN = 'Y'
1267 ,LATE_CHARGE_ASSESS_DATE = SYSDATE
1268 ,last_updated_by = l_last_updated_by
1269 ,last_update_date = sysdate
1270 ,last_update_login = l_last_update_login
1271 ,request_id = l_request_id
1272 WHERE CURRENT OF l_cons_lsm_cur; -- Made changes by bkatraga for bug 5601733
1273
1274 IF (SQL%NOTFOUND) THEN
1275 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ... Internal Consolidation Record Update Unsuccessful.');
1276 ELSE
1277 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ... Internal Consolidation Record Updated.');
1278 END IF;
1279
1280 END LOOP;
1281 ELSE
1282 -- if AR Invoice
1283
1284 FOR l_lsm IN l_AR_lsm_cur(l_rec_inv_id,l_rec_inv_contract_id)
1285 LOOP
1286
1287 Update okl_txd_ar_ln_dtls_b
1288 set LATE_CHARGE_ASS_YN = 'Y'
1289 ,LATE_CHARGE_ASSESS_DATE = SYSDATE
1290 ,last_updated_by = l_last_updated_by
1291 ,last_update_date = sysdate
1292 ,last_update_login = l_last_update_login
1293 ,request_id = l_request_id
1294 WHERE CURRENT OF l_AR_lsm_cur;
1295
1296 IF (SQL%NOTFOUND) THEN
1297 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ... Internal Consolidation Record Update Unsuccessful.');
1298 ELSE
1299 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ... Internal Consolidation Record Updated.');
1300 END IF;
1301
1302 END LOOP;
1303 END IF;
1304 END IF;
1305 END IF;
1306 END IF;
1307 --dkagrawa bug# 4728636 changes start
1308 IF l_prev_khr_id IS NULL THEN
1309 l_prev_khr_id := l_inv_cur.contract_id;
1310 END IF;
1311 IF l_prev_khr_id <> l_inv_cur.contract_id THEN
1312 IF NOT(l_error_flag) THEN
1313 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date ( l_prev_khr_id );
1314 END IF;
1315 l_prev_khr_id := l_inv_cur.contract_id;
1316 END IF;
1317 END LOOP;
1318 IF l_prev_khr_id IS NOT NULL THEN
1319 IF NOT(l_error_flag) THEN
1320 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date ( l_prev_khr_id );
1321 END IF;
1322 END IF;
1323 --dkagrawa bug# 4728636 changes end */ ---- for bug 7295166 -- Don't charge late charge on late charge.
1324
1325
1326 l_contract_currency := null;
1327 l_late_policy := null;
1328 l_prev_contract_currency := null;
1329 l_prev_late_policy := null;
1330 l_prev_khr_id := null; --dkagrawa bug# 4728636
1331
1332 FOR l_inv_cur IN l_late_invs_cur
1333 LOOP
1334 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Processing:Already Late: Receivables invoice => '||l_inv_cur.consolidated_invoice_number||
1335 ' ,due date=> '||l_inv_cur.DUE_DATE||' and Amount=> '||l_inv_cur.AMOUNT_DUE_REMAINING
1336 ||' ,Contract => '||l_inv_cur.CONTRACT_NUMBER);
1337 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Late policy => ' || l_inv_cur.late_policy
1338 || ' ,Contract currency => ' || l_inv_cur.currency_code);
1339 -- vdamerla bug 5474844
1340 open l_receivables_inv_csr(l_inv_cur.CONTRACT_ID,l_inv_cur.consolidated_invoice_number);
1341 fetch l_receivables_inv_csr into l_rec_inv_id,l_rec_inv_contract_id;
1342 close l_receivables_inv_csr;
1343
1344 if l_inv_cur.CONSOLIDATED_INVOICE_ID is not null then
1345 l_rec_inv_id := l_inv_cur.CONSOLIDATED_INVOICE_ID;
1346 -- Get the amount_due_remaining
1347 OKL_BILLING_UTIL_PVT.get_contract_invoice_balance(
1348 p_api_version => 1.0
1349 ,p_init_msg_list => OKL_API.G_FALSE
1350 ,p_contract_number => l_inv_cur.CONTRACT_NUMBER
1351 ,p_trx_number => l_inv_cur.consolidated_invoice_number
1352 ,x_return_status => x_return_status
1353 ,x_msg_count => x_msg_count
1354 ,x_msg_data => x_msg_data
1355 ,x_remaining_amount => l_amt_due_remaining);
1356 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1357 l_error_flag := TRUE;
1358 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Error fetching Consolidated Invoice Balance for contract: '
1359 ||l_inv_cur.contract_number
1360 ||' consolidated_invoice_number: '||l_inv_cur.consolidated_invoice_number);
1361 ELSE
1362 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Successfully fetched Consolidated Invoice Balance for contract: '
1363 ||l_inv_cur.contract_number
1364 ||' consolidated_invoice_number: '||l_inv_cur.consolidated_invoice_number
1365 ||'Invoice Balance:'|| l_amt_due_remaining);
1366
1367
1368 END IF;
1369
1370 FND_FILE.PUT_LINE (FND_FILE.LOG,' Contract Number => '||l_inv_cur.CONTRACT_NUMBER);
1371 FND_FILE.PUT_LINE (FND_FILE.LOG,'consolidated_invoice_number => '||l_inv_cur.consolidated_invoice_number);
1372 FND_FILE.PUT_LINE (FND_FILE.LOG,'Consolidated Invoice Balance => '||l_amt_due_remaining);
1373 else
1374 l_amt_due_remaining := l_inv_cur.AMOUNT_DUE_REMAINING;
1375 FND_FILE.PUT_LINE (FND_FILE.LOG, 'AR Invoice Balance => '||l_amt_due_remaining);
1376 end if;
1377
1378 l_due_date := l_inv_cur.DUE_DATE;
1379 l_khr_id := l_inv_cur.CONTRACT_ID;
1380 l_contract_currency := l_inv_cur.currency_code;
1381 l_late_policy := l_inv_cur.late_policy;
1382
1383 l_investor_disb_flag := 'N';
1384
1385
1386 -- IF (l_name IS NOT NULL) THEN
1387 IF (nvl(l_late_policy, 'xxx') <> nvl(l_prev_late_policy, 'yyy') or
1388 nvl(l_contract_currency, 'aaa') <> nvl(l_prev_contract_currency, 'bbb')) THEN
1389 FOR l_lpo_cur IN l_late_policy_cur(l_late_policy)
1390 LOOP
1391
1392 --start code pgomes 12/18/2002
1393 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Functional currency : ' || l_func_currency || ' Contract currency : ' || l_contract_currency);
1394 IF (l_func_currency <> NVL(l_contract_currency, '000')) THEN
1395 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Converting late charge amounts from functional to contract.');
1396 --convert late_chrg_amount to contract currency
1397 OKL_ACCOUNTING_UTIL.convert_to_contract_currency(
1398 p_khr_id => l_khr_id,
1399 p_from_currency => l_func_currency,
1400 p_transaction_date => sysdate,
1401 p_amount => l_lpo_cur.late_chrg_amount,
1402 x_contract_currency => l_contract_currency,
1403 x_currency_conversion_type => l_currency_conversion_type,
1404 x_currency_conversion_rate => l_currency_conversion_rate,
1405 x_currency_conversion_date => l_currency_conversion_date,
1406 x_converted_amount => l_late_chrg_amount);
1407
1408 l_late_chrg_amount := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(l_late_chrg_amount, l_contract_currency);
1409
1410 --convert minimum_late_charge to contract currency
1411 OKL_ACCOUNTING_UTIL.convert_to_contract_currency(
1412 p_khr_id => l_khr_id,
1413 p_from_currency => l_func_currency,
1414 p_transaction_date => sysdate,
1415 p_amount => l_lpo_cur.minimum_late_charge,
1416 x_contract_currency => l_contract_currency,
1417 x_currency_conversion_type => l_currency_conversion_type,
1418 x_currency_conversion_rate => l_currency_conversion_rate,
1419 x_currency_conversion_date => l_currency_conversion_date,
1420 x_converted_amount => l_minimum_late_charge);
1421
1422 l_minimum_late_charge := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(l_minimum_late_charge, l_contract_currency);
1423
1424 --convert maximum_late_charge to contract currency
1425 OKL_ACCOUNTING_UTIL.convert_to_contract_currency(
1426 p_khr_id => l_khr_id,
1427 p_from_currency => l_func_currency,
1428 p_transaction_date => sysdate,
1429 p_amount => l_lpo_cur.maximum_late_charge,
1430 x_contract_currency => l_contract_currency,
1431 x_currency_conversion_type => l_currency_conversion_type,
1432 x_currency_conversion_rate => l_currency_conversion_rate,
1433 x_currency_conversion_date => l_currency_conversion_date,
1434 x_converted_amount => l_maximum_late_charge);
1435
1436 l_maximum_late_charge := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(l_maximum_late_charge, l_contract_currency);
1437
1438 --convert late_chrg_minimum_balance to contract currency
1439 OKL_ACCOUNTING_UTIL.convert_to_contract_currency(
1440 p_khr_id => l_khr_id,
1441 p_from_currency => l_func_currency,
1442 p_transaction_date => sysdate,
1443 p_amount => l_lpo_cur.late_chrg_minimum_balance,
1444 x_contract_currency => l_contract_currency,
1445 x_currency_conversion_type => l_currency_conversion_type,
1446 x_currency_conversion_rate => l_currency_conversion_rate,
1447 x_currency_conversion_date => l_currency_conversion_date,
1448 x_converted_amount => l_late_chrg_minimum_balance);
1449
1450 l_late_chrg_minimum_balance := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(l_late_chrg_minimum_balance, l_contract_currency);
1451 ELSE
1452 l_late_chrg_amount := l_lpo_cur.late_chrg_amount;
1453 l_minimum_late_charge := l_lpo_cur.minimum_late_charge;
1454 l_maximum_late_charge := l_lpo_cur.maximum_late_charge;
1455 l_late_chrg_minimum_balance := l_lpo_cur.late_chrg_minimum_balance;
1456 END IF;
1457 --end code pgomes 12/18/2002
1458
1459 l_late_chrg_fixed_yn := l_lpo_cur.late_chrg_fixed_yn;
1460 l_late_chrg_allowed_yn := l_lpo_cur.late_chrg_allowed_yn;
1461 l_late_chrg_rate := l_lpo_cur.late_chrg_rate;
1462
1463 -- pgomes 12/18/2002 start, changed code to consider converted charges
1464 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Processing: LATE POLICY TYPE CODE => '||l_lpo_cur.LATE_POLICY_TYPE_CODE||
1465 ' LATE CHRG MINIMUM BALANCE=> '||l_late_chrg_minimum_balance
1466 ||' ,LATE CHRG GRACE PERIOD=> '||l_lpo_cur.LATE_CHRG_GRACE_PERIOD);
1467
1468 END LOOP;
1469 l_prev_late_policy := l_late_policy;
1470 l_prev_contract_currency := l_contract_currency;
1471 END IF;
1472
1473 l_error_flag := FALSE;
1474 l_late_stm_id := null;
1475 IF(nvl(l_late_chrg_minimum_balance,0) < l_amt_due_remaining) THEN
1476
1477 l_sty_id := null;
1478
1479 IF(l_late_chrg_fixed_yn = 'Y') THEN
1480 l_late_charge_amt := l_late_chrg_amount;
1481 l_investor_disb_flag := 'N';
1482 ELSE --(l_lpo_cur.LATE_CHRG_FIXED_YN = 'N') THEN
1483 l_amount := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(l_amt_due_remaining*(l_late_chrg_rate/100), l_contract_currency);
1484 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Calculated late charge => '||l_amount);
1485
1486 IF(l_amount < nvl(l_minimum_late_charge,0)) THEN
1487 l_late_charge_amt := l_minimum_late_charge;
1488 l_investor_disb_flag := 'N';
1489 ELSIF(l_amount > nvl(l_maximum_late_charge,0)) THEN
1490 l_late_charge_amt := l_maximum_late_charge;
1491 l_investor_disb_flag := 'N';
1492 ELSE
1493 l_late_charge_amt := l_amount;
1494 l_investor_disb_flag := 'Y';
1495 FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_investor_disb_flag is set to '||l_investor_disb_flag );
1496 END IF;
1497
1498 END IF;
1499
1500 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Applied late charge => '||l_late_charge_amt);
1501 -- pgomes 12/18/2002 end, changed code to consider converted charges
1502
1503
1504 ----------------------------------------------------------------
1505 --PROCESSING FOR LATE CHARGE
1506 ----------------------------------------------------------------
1507
1508 --get stream type id
1509 l_sty_id := null;
1510
1511 Okl_Streams_Util.get_primary_stream_type(
1512 p_khr_id => l_khr_id,
1513 p_primary_sty_purpose => cns_late_fee,
1514 x_return_status => l_return_status,
1515 x_primary_sty_id => l_sty_id );
1516
1517 IF (l_return_status = 'S' ) THEN
1518 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream Id for purpose LATE_FEE retrieved.');
1519 ELSE
1520 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Could not retrieve Stream Id for purpose LATE_FEE.');
1521 l_error_flag := TRUE;
1522 END IF;
1523
1524 --check for stream
1525 --check for stream type id
1526 IF NOT (l_error_flag) THEN
1527 l_stm_id := null;
1528 l_se_line_number := null;
1529
1530 if l_inv_cur.CONSOLIDATED_INVOICE_ID is not null then
1531 OPEN l_stream_csr(l_inv_cur.contract_id, l_sty_id);
1532 FETCH l_stream_csr INTO l_stm_id;
1533 CLOSE l_stream_csr;
1534 else
1535 OPEN l_AR_stream_csr(l_inv_cur.contract_id, l_sty_id);
1536 FETCH l_AR_stream_csr INTO l_stm_id;
1537 CLOSE l_AR_stream_csr;
1538 end if;
1539
1540 --create stream for late charge
1541 IF (l_stm_id IS NULL) THEN
1542 l_stmv_rec := l_init_stmv_rec;
1543
1544 OPEN c_tran_num_csr;
1545 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
1546 CLOSE c_tran_num_csr;
1547
1548 l_stmv_rec.sty_id := l_sty_id;
1549 l_stmv_rec.khr_id := l_inv_cur.contract_id;
1550 -- l_stmv_rec.sgn_code := 'MANL'; -- Bug 6472228
1551 l_stmv_rec.sgn_code := G_LATE_SGN_CODE; -- Bug 6472228
1552 l_stmv_rec.say_code := 'CURR';
1553 l_stmv_rec.active_yn := 'Y';
1554 l_stmv_rec.date_current := sysdate;
1555 l_stmv_rec.comments := 'LATE FEE BILLING';
1556
1557 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Creating LATE FEE Streams');
1558
1559 Okl_Streams_Pub.create_streams(
1560 p_api_version => p_api_version,
1561 p_init_msg_list => p_init_msg_list,
1562 x_return_status => x_return_status,
1563 x_msg_count => x_msg_count,
1564 x_msg_data => x_msg_data,
1565 p_stmv_rec => l_stmv_rec,
1566 x_stmv_rec => lx_stmv_rec);
1567
1568 l_stm_id := lx_stmv_rec.id;
1569 l_se_line_number := 1;
1570 --fix for bug 4439728
1571 --save the parent stm id so that if creation of payable stm is unsuccessful
1572 --then the parent stm can be invalidated
1573 l_late_stm_id := l_stm_id;
1574
1575 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream element line number => ' || l_se_line_number);
1576 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1577 l_error_flag := TRUE;
1578 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Creating Streams for LATE FEE');
1579 ELSE
1580 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- SUCCESS: Creating Streams for LATE FEE');
1581 END IF;
1582
1583 ELSE
1584 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream for LATE FEE found');
1585 open l_stream_line_nbr_csr(l_stm_id);
1586 fetch l_stream_line_nbr_csr into l_se_line_number;
1587 close l_stream_line_nbr_csr;
1588 l_se_line_number := nvl(l_se_line_number,0) + 1;
1589 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream element line number => ' || l_se_line_number);
1590 END IF;
1591
1592
1593 --create stream element for late charge
1594 IF (l_stm_id IS NOT NULL) THEN
1595 l_selv_rec := l_init_selv_rec;
1596
1597 l_selv_rec.stm_id := l_stm_id;
1598 l_selv_rec.SE_LINE_NUMBER := l_se_line_number;
1599 l_selv_rec.STREAM_ELEMENT_DATE := sysdate;
1600 l_selv_rec.AMOUNT := l_late_charge_amt;
1601 l_selv_rec.COMMENTS := 'LATE FEE BILLING ELEMENTS';
1602 l_selv_rec.ACCRUED_YN := 'Y';
1603
1604
1605 -- vdamerla bug 5474844
1606
1607 if l_inv_cur.consolidated_invoice_id is not null then
1608
1609 l_selv_rec.source_id:=l_inv_cur.consolidated_invoice_id;
1610 l_selv_rec.source_table := cns_cons_source_table;
1611 else
1612 OPEN c_AR_source_sel(l_inv_cur.contract_number, l_inv_cur.consolidated_invoice_number);
1613 FETCH c_AR_source_sel INTO l_selv_rec.source_id;
1614 CLOSE c_AR_source_sel;
1615 l_selv_rec.source_table := cns_AR_source_table;
1616 end if;
1617
1618 Okl_Sel_Pvt.insert_row(
1619 p_api_version,
1620 p_init_msg_list,
1621 x_return_status,
1622 x_msg_count,
1623 x_msg_data,
1624 l_selv_rec,
1625 lx_selv_rec);
1626
1627 l_sel_id := lx_selv_rec.id;
1628 l_sec_rec_inv_id := lx_selv_rec.source_id;
1629
1630 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1631 l_error_flag := TRUE;
1632 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Error Creating Stream Element for Contract: '
1633
1634 ||l_inv_cur.contract_number
1635 ||' Stream Purpose: '||cns_late_fee
1636 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
1637 ||' Amount: '||l_late_charge_amt
1638 ||'source_id:'||l_selv_rec.source_id);
1639 ELSE
1640
1641 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Created Late Fee Stream Element for Contract: '
1642 ||l_inv_cur.contract_number
1643 ||' Stream Purpose: '||cns_late_fee
1644 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
1645 ||' Amount: '||l_late_charge_amt
1646 );
1647 END IF;
1648 END IF;
1649
1650 ----------------------------------------------------------------
1651 --PROCESSING FOR LATE CHARGE PAYABLE TO INVESTOR
1652 ----------------------------------------------------------------
1653 FND_FILE.PUT_LINE (FND_FILE.LOG, '(l_late_invs_cur1) l_investor_disb_flag:'||l_investor_disb_flag);
1654 IF nvl(l_investor_disb_flag,'N') = 'Y' THEN
1655 IF l_inv_cur.consolidated_invoice_id is null then
1656 FOR cur_sec_strm IN c_sec_strm_AR_csr(l_inv_cur.contract_id, l_inv_cur.consolidated_invoice_number) LOOP
1657 --get stream type id
1658 l_sty_id := null;
1659
1660 Okl_Streams_Util.get_primary_stream_type(
1661 p_khr_id => cur_sec_strm.khr_id,
1662 p_primary_sty_purpose => cns_late_charge_payable,
1663 x_return_status => l_return_status,
1664 x_primary_sty_id => l_sty_id );
1665
1666 IF (l_return_status = 'S' ) THEN
1667 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream Id for purpose INVESTOR_LATE_FEE_PAYABLE retrieved.');
1668 ELSE
1669 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Could not retrieve Stream Id for purpose INVESTOR_LATE_FEE_PAYABLE.');
1670 l_error_flag := TRUE;
1671 END IF;
1672
1673 --check for stream
1674 IF NOT (l_error_flag) THEN
1675 l_stm_id := null;
1676 l_se_line_number := null;
1677
1678
1679 OPEN l_AR_stream_csr(l_inv_cur.contract_id, l_sty_id);
1680 FETCH l_AR_stream_csr INTO l_stm_id;
1681 CLOSE l_AR_stream_csr;
1682
1683
1684
1685 --create stream for late charge payable
1686 IF (l_stm_id IS NULL) THEN
1687 l_stmv_rec := l_init_stmv_rec;
1688
1689 OPEN c_tran_num_csr;
1690 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
1691 CLOSE c_tran_num_csr;
1692
1693 l_stmv_rec.sty_id := l_sty_id;
1694 l_stmv_rec.khr_id := l_inv_cur.contract_id;
1695 l_stmv_rec.kle_id := cur_sec_strm.kle_id;
1696 -- l_stmv_rec.sgn_code := 'MANL'; -- Bug 6472228
1697 l_stmv_rec.sgn_code := G_LATE_SGN_CODE; -- Bug 6472228
1698 l_stmv_rec.say_code := 'CURR';
1699 l_stmv_rec.active_yn := 'Y';
1700 l_stmv_rec.date_current := sysdate;
1701 l_stmv_rec.comments := 'INVESTOR LATE FEE PAYABLE';
1702 -- gboomina Bug 6797000 - Start
1703 -- Stamping source id and source table for INVESTOR LATE FEE PAYABLE
1704 -- streams which are used as a condn to pick these streams
1705 -- while running Investor Disbursement program
1706 IF (cur_sec_strm.khr_id IS NOT NULL) THEN
1707 l_stmv_rec.source_id := cur_sec_strm.khr_id;
1708 l_stmv_rec.source_table := 'OKL_K_HEADERS';
1709 END IF;
1710 -- gboomina Bug 6797000 - End
1711 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Creating INVESTOR LATE FEE PAYABLE Streams');
1712
1713 Okl_Streams_Pub.create_streams(
1714 p_api_version => p_api_version,
1715 p_init_msg_list => p_init_msg_list,
1716 x_return_status => x_return_status,
1717 x_msg_count => x_msg_count,
1718 x_msg_data => x_msg_data,
1719 p_stmv_rec => l_stmv_rec,
1720 x_stmv_rec => lx_stmv_rec);
1721
1722 l_stm_id := lx_stmv_rec.id;
1723 l_se_line_number := 1;
1724
1725 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream element line number => ' || l_se_line_number);
1726 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1727 l_error_flag := TRUE;
1728 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Creating Streams for INVESTOR LATE FEE PAYABLE');
1729 ELSE
1730 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- SUCCESS: Creating Streams for INVESTOR LATE FEE PAYABLE');
1731 END IF;
1732 ELSE
1733 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream for INVESTOR LATE FEE PAYABLE found');
1734 open l_stream_line_nbr_csr(l_stm_id);
1735 fetch l_stream_line_nbr_csr into l_se_line_number;
1736 close l_stream_line_nbr_csr;
1737 l_se_line_number := nvl(l_se_line_number,0) + 1;
1738 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream element line number => ' || l_se_line_number);
1739 END IF;
1740
1741 --create stream element for late charge payable
1742 IF (l_stm_id IS NOT NULL) THEN
1743 l_selv_rec := l_init_selv_rec;
1744
1745 l_selv_rec.stm_id := l_stm_id;
1746 l_selv_rec.SE_LINE_NUMBER := l_se_line_number;
1747 l_selv_rec.STREAM_ELEMENT_DATE := sysdate;
1748 l_selv_rec.AMOUNT := l_late_charge_amt;
1749 l_selv_rec.COMMENTS := 'INVESTOR LATE FEE PAYABLE ELEMENTS';
1750 l_selv_rec.ACCRUED_YN := 'Y';
1751 -- gboomina Bug 6797000 - Start
1752 -- Stamping source id and source table for INVESTOR LATE FEE PAYABLE
1753 -- streams which are used as a condn to pick these streams
1754 -- while running Investor Disbursement program
1755 IF (cur_sec_strm.khr_id IS NOT NULL) THEN
1756 l_selv_rec.source_id := cur_sec_strm.khr_id;
1757 l_selv_rec.source_table := 'OKL_K_HEADERS';
1758 END IF;
1759 -- gboomina Bug 6797000 - End
1760
1761 --@IMPORTANT@ Uncomment out this code once lsm_id is added to okl_strm_elements
1762 --l_selv_rec.lsm_id := cur_sec_strm.cnsld_strm_id;
1763 l_selv_rec.sel_id := l_sel_id;
1764
1765 Okl_Sel_Pvt.insert_row(
1766 p_api_version,
1767 p_init_msg_list,
1768 x_return_status,
1769 x_msg_count,
1770 x_msg_data,
1771 l_selv_rec,
1772 lx_selv_rec);
1773
1774 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1775 l_error_flag := TRUE;
1776 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Error Creating Payable Stream Element for Contract: '
1777 ||l_inv_cur.contract_number
1778 ||' Stream Purpose: '||cns_late_fee
1779 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
1780 ||' Amount: '||l_late_charge_amt);
1781 ELSE
1782
1783 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Created Investor Late Charge Payable Stream Element for Contract: '
1784 ||l_inv_cur.contract_number
1785 ||' Stream Purpose: '||cns_late_fee
1786 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
1787 ||' Amount: '||l_late_charge_amt
1788 );
1789 END IF;
1790 END IF;
1791 ELSE
1792 --fix for bug 4439728
1793 --deactivate late charge stream as payable stream creation was unsuccessful
1794 Update okl_streams
1795 set say_code = 'HIST'
1796 ,active_yn = 'N'
1797 ,last_updated_by = l_last_updated_by
1798 ,last_update_date = sysdate
1799 ,last_update_login = l_last_update_login
1800 ,request_id = l_request_id
1801 ,date_history = SYSDATE
1802 WHERE id = l_late_stm_id;
1803
1804 IF (SQL%NOTFOUND) THEN
1805 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ... Late charge stream not deactivated successfully as payable stream creation was unsuccessful.');
1806 ELSE
1807 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ... Late charge stream deactivated successfully as payable stream creation was unsuccessful.');
1808 END IF;
1809
1810 END IF;
1811
1812 END LOOP;
1813 END IF; -- if consolidated_invoice_id is null
1814
1815 IF l_inv_cur.consolidated_invoice_id is not null then
1816 FOR cur_sec_strm IN c_sec_strm_cons_csr(l_inv_cur.consolidated_invoice_id,
1817 l_inv_cur.contract_id) LOOP
1818 --get stream type id
1819 l_sty_id := null;
1820
1821 Okl_Streams_Util.get_primary_stream_type(
1822 p_khr_id => cur_sec_strm.khr_id,
1823 p_primary_sty_purpose => cns_late_charge_payable,
1824 x_return_status => l_return_status,
1825 x_primary_sty_id => l_sty_id );
1826
1827 IF (l_return_status = 'S' ) THEN
1828 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream Id for purpose INVESTOR_LATE_FEE_PAYABLE retrieved.');
1829 ELSE
1830 --Added by bkatraga for bug 5601733
1831 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Could not retrieve Stream Id for purpose INVESTOR_LATE_FEE_PAYABLE while processing Consolidated invoice => '||
1832 l_inv_cur.consolidated_INVOICE_NUMBER);
1833 --end bkatraga
1834 l_error_flag := TRUE;
1835 END IF;
1836
1837 --check for stream
1838 IF NOT (l_error_flag) THEN
1839 l_stm_id := null;
1840 l_se_line_number := null;
1841
1842 --Added by bkatraga for bug 5601733
1843 OPEN l_cons_stream_csr(l_inv_cur.contract_id, cur_sec_strm.kle_id,l_sty_id);
1844 FETCH l_cons_stream_csr INTO l_stm_id;
1845 CLOSE l_cons_stream_csr;
1846 --end bkatraga
1847
1848 --create stream for late charge payable
1849 IF ((l_stm_id IS NULL) or (l_stm_id = -99)) THEN --Added or clause by bkatraga for bug 5601733
1850 l_stmv_rec := l_init_stmv_rec;
1851
1852 OPEN c_tran_num_csr;
1853 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
1854 CLOSE c_tran_num_csr;
1855
1856 l_stmv_rec.sty_id := l_sty_id;
1857 l_stmv_rec.khr_id := l_inv_cur.contract_id;
1858 l_stmv_rec.kle_id := cur_sec_strm.kle_id; --Added by bkatraga for bug 5601733
1859 -- l_stmv_rec.sgn_code := 'MANL'; -- Bug 6472228
1860 l_stmv_rec.sgn_code := G_LATE_SGN_CODE; -- Bug 6472228
1861 l_stmv_rec.say_code := 'CURR';
1862 l_stmv_rec.active_yn := 'Y';
1863 l_stmv_rec.date_current := sysdate;
1864 l_stmv_rec.comments := 'INVESTOR LATE FEE PAYABLE';
1865 IF (cur_sec_strm.khr_id IS NOT NULL) THEN
1866 l_stmv_rec.source_id := cur_sec_strm.khr_id;
1867 l_stmv_rec.source_table := 'OKL_K_HEADERS';
1868 END IF;
1869
1870 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Creating INVESTOR LATE FEE PAYABLE Stream');
1871
1872 Okl_Streams_Pub.create_streams(
1873 p_api_version => p_api_version,
1874 p_init_msg_list => p_init_msg_list,
1875 x_return_status => x_return_status,
1876 x_msg_count => x_msg_count,
1877 x_msg_data => x_msg_data,
1878 p_stmv_rec => l_stmv_rec,
1879 x_stmv_rec => lx_stmv_rec);
1880
1881 l_stm_id := lx_stmv_rec.id;
1882 l_se_line_number := 1;
1883
1884 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream element line number => ' || l_se_line_number);
1885 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1886 l_error_flag := TRUE;
1887 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- ERROR: Creating Stream for INVESTOR LATE FEE PAYABLE');
1888 ELSE
1889 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- SUCCESS: Creating Stream for INVESTOR LATE FEE PAYABLE');
1890 END IF;
1891 ELSE
1892 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream for INVESTOR LATE FEE PAYABLE found');
1893 open l_stream_line_nbr_csr(l_stm_id);
1894 fetch l_stream_line_nbr_csr into l_se_line_number;
1895 close l_stream_line_nbr_csr;
1896 l_se_line_number := nvl(l_se_line_number,0) + 1;
1897 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Stream element line number => ' || l_se_line_number);
1898 END IF;
1899
1900 --create stream element for late charge payable
1901 IF (l_stm_id IS NOT NULL) THEN
1902 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Creating INVESTOR LATE FEE PAYABLE Stream Elements');
1903 l_selv_rec := l_init_selv_rec;
1904
1905 l_selv_rec.stm_id := l_stm_id;
1906 l_selv_rec.SE_LINE_NUMBER := l_se_line_number;
1907 l_selv_rec.STREAM_ELEMENT_DATE := sysdate;
1908 --Added by bkatraga for bug 5601733
1909 l_selv_rec.AMOUNT := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(cur_sec_strm.amount_due_remaining*(l_late_chrg_rate/100), l_contract_currency);
1910 --end bkatraga
1911 l_selv_rec.COMMENTS := 'INVESTOR LATE FEE PAYABLE ELEMENTS';
1912 l_selv_rec.ACCRUED_YN := 'Y';
1913 l_selv_rec.sel_id := l_sel_id;
1914 IF (cur_sec_strm.khr_id IS NOT NULL) THEN
1915 l_selv_rec.source_id := cur_sec_strm.khr_id;
1916 l_selv_rec.source_table := 'OKL_K_HEADERS';
1917 END IF;
1918
1919 Okl_Sel_Pvt.insert_row(
1920 p_api_version,
1921 p_init_msg_list,
1922 x_return_status,
1923 x_msg_count,
1924 x_msg_data,
1925 l_selv_rec,
1926 lx_selv_rec);
1927
1928 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1929 l_error_flag := TRUE;
1930 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Error Creating Payable Stream Element for Contract: '
1931 ||l_inv_cur.contract_number
1932 ||' Stream Purpose: '||cns_late_fee
1933 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
1934 ||' Amount: '||l_late_charge_amt);
1935 ELSE
1936
1937 FND_FILE.PUT_LINE (FND_FILE.LOG, ' -- Created Investor Late Charge Payable Stream Element for Contract: '
1938 ||l_inv_cur.contract_number
1939 ||' Stream Purpose: '||cns_late_fee
1940 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
1941 ||' Amount: '||l_late_charge_amt
1942 );
1943 END IF;
1944 END IF;
1945 ELSE
1946 --fix for bug 4439728
1947 --deactivate late charge stream as payable stream creation was unsuccessful
1948 Update okl_streams
1949 set say_code = 'HIST'
1950 ,active_yn = 'N'
1951 ,last_updated_by = l_last_updated_by
1952 ,last_update_date = sysdate
1953 ,last_update_login = l_last_update_login
1954 ,request_id = l_request_id
1955 ,date_history = SYSDATE
1956 WHERE id = l_late_stm_id;
1957
1958 IF (SQL%NOTFOUND) THEN
1959 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ... Late charge stream not deactivated successfully as payable stream creation was unsuccessful.');
1960 ELSE
1961 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ... Late charge stream deactivated successfully as payable stream creation was unsuccessful.');
1962 END IF;
1963 END IF;
1964 END LOOP;
1965 END IF; -- if consolidated_invoice_id is not null
1966 END IF; -- l_investor_disb_flag
1967
1968
1969 IF NOT(l_error_flag) THEN
1970 FOR l_lsm IN l_AR_lsm_cur(l_rec_inv_id,l_rec_inv_contract_id)
1971 LOOP
1972 Update okl_txd_ar_ln_dtls_b
1973 set LATE_CHARGE_ASS_YN = 'Y'
1974 , LATE_CHARGE_ASSESS_DATE = SYSDATE
1975 ,last_updated_by = l_last_updated_by
1976 ,last_update_date = sysdate
1977 ,last_update_login = l_last_update_login
1978 ,request_id = l_request_id
1979 WHERE CURRENT OF l_AR_lsm_cur;
1980
1981 -- IF (l_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
1982 IF (SQL%NOTFOUND) THEN
1983 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ... Internal Consolidation Record Update Unsuccessful.');
1984 ELSE
1985 FND_FILE.PUT_LINE (FND_FILE.LOG, ' ... Internal Consolidation Record Updated.');
1986 END IF;
1987 -- END IF;
1988
1989 END LOOP;
1990 END IF;
1991 END IF;
1992 END IF;
1993 --dkagrawa bug# 4728636 changes start
1994 IF l_prev_khr_id IS NULL THEN
1995 l_prev_khr_id := l_inv_cur.contract_id;
1996 END IF;
1997 IF l_prev_khr_id <> l_inv_cur.contract_id THEN
1998 IF NOT(l_error_flag) THEN
1999 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date ( l_prev_khr_id );
2000 END IF;
2001 l_prev_khr_id := l_inv_cur.contract_id;
2002 END IF;
2003 END LOOP;
2004 IF l_prev_khr_id IS NOT NULL THEN
2005 IF NOT(l_error_flag) THEN
2006 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date ( l_prev_khr_id );
2007 END IF;
2008 END IF;
2009 --dkagrawa bug# 4728636 changes end
2010
2011
2012 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'End of Calculate charges.');
2013 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2014 x_return_status := l_return_status;
2015
2016 EXCEPTION
2017 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2018 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error (EXCP) => '||SQLERRM);
2019 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
2020 (
2021 l_api_name,
2022 G_PKG_NAME,
2023 'Okl_Api.G_RET_STS_ERROR',
2024 x_msg_count,
2025 x_msg_data,
2026 '_PVT'
2027 );
2028 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2029 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error (UNEXCP) => '||SQLERRM);
2030 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2031 (
2032 l_api_name,
2033 G_PKG_NAME,
2034 'Okl_Api.G_RET_STS_UNEXP_ERROR',
2035 x_msg_count,
2036 x_msg_data,
2037 '_PVT'
2038 );
2039 WHEN OTHERS THEN
2040 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error (Other) => '||SQLERRM);
2041 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2042 (
2043 l_api_name,
2044 G_PKG_NAME,
2045 'OTHERS',
2046 x_msg_count,
2047 x_msg_data,
2048 '_PVT'
2049 );
2050 END calculate_late_charge;
2051
2052 END OKL_LTE_CHRG_PVT;