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