[Home] [Help]
PACKAGE BODY: APPS.OKL_EVERGREEN_BILLING_PVT
Source
1 Package BODY Okl_Evergreen_Billing_Pvt AS
2 /* $Header: OKLREGBB.pls 120.19.12020000.4 2012/11/22 09:34:26 venkatho ship $ */
3
4 -- Start of Variables Declaration as part of Bug# 14119181
5 ----------------------------------------------------------------------------
6 -- Variables For Debugging and Logging
7 ----------------------------------------------------------------------------
8 G_MODULE VARCHAR2(40) := 'LEASE.EVERGREEN';
9 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
10 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
11 -- End of Variables Declaration as part of Bug# 14119181
12
13 ------------------------------------------------------------------
14 -- Procedure BIL_EVERGREEN_STREAMS to bill for Evergreen Streams
15 ------------------------------------------------------------------
16
17 PROCEDURE BILL_EVERGREEN_STREAMS
18 (p_api_version IN NUMBER
19 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
20 ,x_return_status OUT NOCOPY VARCHAR2
21 ,x_msg_count OUT NOCOPY NUMBER
22 ,x_msg_data OUT NOCOPY VARCHAR2
23 ,p_contract_number IN VARCHAR2 DEFAULT NULL
24 ,p_from_bill_date IN DATE DEFAULT NULL
25 ,p_to_bill_date IN DATE DEFAULT NULL) IS
26
27 ------------------------------------------------------------
28 -- Pick Evergreen Contract Id's
29 ------------------------------------------------------------
30 CURSOR evergreen_contracts_csr ( p_contract_number VARCHAR2 ) IS
31 SELECT oklh.id khr_id,
32 okch.contract_number,
33 nvl(stm.kle_id, -99) kle_id
34 FROM okl_k_headers oklh,
35 okc_k_headers_b okch,
36 okc_statuses_b khs,
37 okl_streams stm
38 WHERE oklh.id = okch.id
39 AND okch.contract_number = NVL (p_contract_number, okch.contract_number)
40 AND okch.scs_code IN ('LEASE', 'LOAN')
41 AND okch.sts_code = 'EVERGREEN'
42 AND khs.code = okch.sts_code
43 AND oklh.id = stm.khr_id
44 AND EXISTS (SELECT 1 FROM okl_strm_type_v sty
45 WHERE stm.sty_id = sty.id
46 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
47 --AND sty.name IN ('RENT', 'SERVICE AND MAINTENANCE', 'ESTIMATED PERSONAL PROPERTY TAX'))
48 AND sty.stream_type_purpose IN ('RENT', 'SERVICE_PAYMENT', 'ESTIMATED_PROPERTY_TAX', 'FEE_PAYMENT'))
49 AND (stm.kle_id is not null and EXISTS (SELECT 1 FROM OKC_K_LINES_B CLE
50 WHERE cle.dnz_chr_id = oklh.id
51 AND cle.id = stm.kle_id
52 AND cle.sts_code = 'EVERGREEN') OR stm.kle_id IS NULL)
53 GROUP BY oklh.id,
54 okch.contract_number,
55 nvl(stm.kle_id, -99);
56
57
58 -- Below Cursor distinct_evergreen_contracts is added as part of Bug# 14119181
59 ------------------------------------------------------------
60 -- Pick Distinct Evergreen Contract Id's for Bankruptcy check
61 ------------------------------------------------------------
62 CURSOR distinct_evergreen_contracts( p_contract_number VARCHAR2 ) IS
63 SELECT distinct oklh.id khr_id,
64 okch.contract_number,
65 iex_wf_ban_status_pub.get_turninvoff(hca.party_id,hca.cust_account_id,SYSDATE) bkrpcy_sts
66 FROM okl_k_headers oklh,
67 okc_k_headers_b okch,
68 okc_statuses_b khs,
69 okl_streams stm,
70 hz_cust_accounts hca
71 WHERE oklh.id = okch.id
72 AND okch.contract_number = NVL (p_contract_number, okch.contract_number)
73 AND okch.cust_acct_id = hca.cust_account_id
74 AND hca.status = 'A'
75 AND okch.scs_code IN ('LEASE', 'LOAN')
76 AND okch.sts_code = 'EVERGREEN'
77 AND khs.code = okch.sts_code
78 AND oklh.id = stm.khr_id
79 AND EXISTS (SELECT 1 FROM okl_strm_type_v sty
80 WHERE stm.sty_id = sty.id
81 AND sty.stream_type_purpose IN ('RENT', 'SERVICE_PAYMENT', 'ESTIMATED_PROPERTY_TAX', 'FEE_PAYMENT'))
82 AND (stm.kle_id is not null and EXISTS ( SELECT 1
83 FROM OKC_K_LINES_B CLE
84 WHERE cle.dnz_chr_id = oklh.id
85 AND cle.id = stm.kle_id
86 AND cle.sts_code = 'EVERGREEN'
87 )
88 OR stm.kle_id IS NULL
89 )
90 GROUP BY oklh.id,
91 okch.contract_number,
92 iex_wf_ban_status_pub.get_turninvoff(hca.party_id,hca.cust_account_id,SYSDATE);
93
94 ------------------------------------------------------------
95 -- Extract all streams to be billed
96 ------------------------------------------------------------
97 CURSOR c_stm_id ( p_khr_id NUMBER, p_kle_id NUMBER) IS
98 SELECT khr.contract_number contract_number,
99 stm.kle_id, --added by pgomes
100 stm.id stm_id,
101 sty.stream_type_purpose sty_name
102 FROM okl_k_headers_full_v khr,
103 okl_streams stm,
104 okl_strm_type_v sty
105 WHERE khr.id = p_khr_id
106 AND stm.khr_id = khr.id
107 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
108 AND stm.sty_id = sty.id
109 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
110 --AND sty.name IN ('SERVICE AND MAINTENANCE EVERGREEN', 'EVERGREEN RENT', 'ESTIMATED PERSONAL PROPERTY TAX EVERGREEN')
111 AND sty.stream_type_purpose IN ('SERVICE_RENEWAL', 'RENEWAL_RENT', 'RENEWAL_PROPERTY_TAX', 'FEE_RENEWAL')
112 AND stm.say_code = 'CURR'
113 AND stm.active_yn = 'Y';
114
115 ------------------------------------------------------------
116 -- Get Count of all streams
117 ------------------------------------------------------------
118 CURSOR c_stm_count_csr ( p_khr_id NUMBER, p_kle_id NUMBER) IS
119 SELECT count(*)
120 FROM okl_streams stm,
121 okl_strm_type_v sty
122 WHERE stm.khr_id = p_khr_id
123 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
124 AND stm.sty_id = sty.id
125 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
126 --AND sty.name IN ('SERVICE AND MAINTENANCE EVERGREEN', 'EVERGREEN RENT', 'ESTIMATED PERSONAL PROPERTY TAX EVERGREEN')
127 AND sty.stream_type_purpose IN ('SERVICE_RENEWAL', 'RENEWAL_RENT', 'RENEWAL_PROPERTY_TAX', 'FEE_RENEWAL')
128 AND stm.say_code = 'CURR'
129 AND stm.active_yn = 'Y';
130
131 ------------------------------------------------------------
132 -- Get Count of specific stream type
133 ------------------------------------------------------------
134 CURSOR c_sty_count_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2 ) IS
135 SELECT count(*)
136 FROM okl_streams stm,
137 okl_strm_type_v sty
138 WHERE stm.khr_id = p_khr_id
139 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
140 AND stm.sty_id = sty.id
141 AND stm.say_code = 'CURR'
142 AND stm.active_yn = 'Y'
143 AND sty.stream_type_purpose = p_sty_purpose;
144
145 ------------------------------------------------------------
146 -- Get Copy parameters
147 ------------------------------------------------------------
148 /*CURSOR c_copy_params_csr ( p_khr_id NUMBER) IS
149 SELECT stm.kle_id, stm.say_code, stm.active_yn
150 FROM okl_streams stm,
151 okl_strm_type_v sty
152 WHERE stm.khr_id = p_khr_id
153 AND stm.sty_id = sty.id
154 AND stm.say_code = 'CURR'
155 AND stm.active_yn = 'Y'
156 AND sty.name IN ('RENT');*/
157
158 ------------------------------------------------------------
159 -- Get Sty Id
160 ------------------------------------------------------------
161 /*CURSOR get_sty_id_csr ( p_sty_purpose VARCHAR2 ) IS
162 SELECT id
163 FROM okl_strm_type_v
164 WHERE stream_type_purpose = p_sty_purpose; */
165
166 ------------------------------------------------------------
167 -- Get Sty Id for a line and purpose
168 ------------------------------------------------------------
169 CURSOR get_prim_sty_id_csr( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2 ) IS
170 SELECT stm.sty_id
171 FROM okl_streams stm,
172 okl_strm_type_v sty
173 WHERE stm.khr_id = p_khr_id
174 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
175 AND stm.sty_id = sty.id
176 AND stm.say_code = 'CURR'
177 AND stm.active_yn = 'Y'
178 AND sty.stream_type_purpose = p_sty_purpose;
179
180 ------------------------------------------------------------
181 -- Get Stm attributes
182 ------------------------------------------------------------
183 /*CURSOR get_stm_attrs_csr ( p_khr_id NUMBER, p_sty_name VARCHAR2 ) IS
184 SELECT stm.kle_id
185 FROM okl_streams stm,
186 okl_strm_type_v sty
187 WHERE stm.khr_id = p_khr_id
188 AND stm.sty_id = sty.id
189 AND stm.say_code = 'CURR'
190 AND stm.active_yn = 'Y'
191 AND sty.name = p_sty_name; */
192
193 ------------------------------------------------------------
194 -- Upper Bound for Rental/S And M Date
195 ------------------------------------------------------------
196 CURSOR upper_rental_date_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2) IS
197 SELECT TRUNC(MAX( ste.STREAM_ELEMENT_DATE )) upper_stream_date
198 FROM okl_strm_elements ste
199 WHERE ste.stm_id IN (
200 SELECT stm.id
201 FROM okl_streams stm,
202 okl_strm_type_v sty
203 WHERE stm.khr_id = p_khr_id
204 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
205 AND stm.sty_id = sty.id
206 AND stm.say_code = 'CURR'
207 AND stm.active_yn = 'Y'
208 AND sty.stream_type_purpose = p_sty_purpose);
209
210 ------------------------------------------------------------
211 -- Lower Bound for Rental/S And M Date
212 ------------------------------------------------------------
213 CURSOR lower_rental_date_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2, p_max_date DATE) IS
214 SELECT TRUNC(MAX( ste.STREAM_ELEMENT_DATE )) lower_stream_date
215 FROM okl_strm_elements ste
216 WHERE ste.stream_element_date <= p_max_date
217 AND ste.stm_id IN (
218 SELECT stm.id
219 FROM okl_streams stm,
220 okl_strm_type_v sty
221 WHERE stm.khr_id = p_khr_id
222 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
223 AND stm.sty_id = sty.id
224 AND stm.say_code = 'CURR'
225 AND stm.active_yn = 'Y'
226 AND sty.stream_type_purpose = p_sty_purpose);
227
228 ------------------------------------------------------------
229 -- Billing Amount
230 ------------------------------------------------------------
231 CURSOR bill_amt_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2) IS
232 /*bug#6060813 27-Sep-2007 bill_amt_csr changed to pick the last billed stream for each
233 contract line and not the least amount billed during the life of the
234 contract line. Ordered the stream element dates in descending order and picked the
235 the amount for the max stream element date excluding the stream element for stub
236 payment */
237 SELECT ste.amount
238 FROM okl_strm_elements ste,
239 (
240 SELECT stm.id, to_number(rule_information6) amt
241 FROM okc_rules_b a,
242 okc_rule_groups_b b,
243 okl_streams stm,
244 okl_strm_type_v sty
245 WHERE a.dnz_chr_id = p_khr_id
246 AND a.rgp_id = b.id
247 AND b.rgd_code = 'LALEVL'
248 AND a.rule_information_category = 'LASLL'
249 AND stm.kle_id = b.cle_id
250 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
251 AND stm.sty_id = sty.id
252 AND stm.say_code = 'CURR'
253 AND stm.active_yn = 'Y'
254 AND sty.stream_type_purpose = p_sty_purpose
255 AND rule_information6 IS NOT NULL
256 ) strules
257 WHERE ste.stm_id = strules.id
258 AND ste.amount = strules.amt
259 AND ste.date_billed IS NOT NULL
260 ORDER BY ste.stream_element_date DESC;
261
262 /*
263 CURSOR bill_amt_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2) IS
264 SELECT MIN (ste.amount) amount
265 FROM okl_strm_elements ste
266 WHERE ste.stm_id IN (
267 SELECT stm.id
268 FROM okl_streams stm,
269 okl_strm_type_v sty
270 WHERE stm.khr_id = p_khr_id
271 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
272 AND stm.sty_id = sty.id
273 AND stm.say_code = 'CURR'
274 AND stm.active_yn = 'Y'
275 AND sty.stream_type_purpose = p_sty_purpose);
276
277 */
278 ------------------------------------------------------------
279 -- Check Evergreen elements exist
280 ------------------------------------------------------------
281 CURSOR evergreen_element_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2) IS
282 SELECT MAX( ste.STREAM_ELEMENT_DATE ) evergreen_element_date
283 FROM okl_strm_elements ste
284 WHERE ste.stm_id IN (
285 SELECT stm.id
286 FROM okl_streams stm,
287 okl_strm_type_v sty
288 WHERE stm.khr_id = p_khr_id
289 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
290 AND stm.sty_id = sty.id
291 AND stm.say_code = 'CURR'
292 AND stm.active_yn = 'Y'
293 AND sty.stream_type_purpose = p_sty_purpose);
294
295 ------------------------------------------------------------
296 -- Transaction Number Cursor
297 ------------------------------------------------------------
298 CURSOR c_tran_num_csr IS
299 SELECT okl_sif_seq.nextval
300 FROM dual;
301
302 ------------------------------------------------------------
303 -- Billing Frequency Cursor
304 ------------------------------------------------------------
305 --changed for rules migration
306 CURSOR c_bill_freq_csr( p_khr_id NUMBER ) IS
307 SELECT object1_id1
308 FROM OKC_RULES_B rul,
309 Okc_rule_groups_B rgp
310 WHERE rul.rgp_id = rgp.id AND
311 rgp.rgd_code = 'LALEVL' AND
312 rgp.chr_id IS NULL AND
313 rul.rule_information_category = 'LASLL' AND
314 rgp.dnz_chr_id = p_khr_id;
315
316 -----------------------------------------------------------
317 -- Max Line Number
318 ------------------------------------------------------------
319 CURSOR max_line_num_csr (p_stm_id NUMBER) IS
320 SELECT max(se_line_number)
321 FROM okl_strm_elements
322 WHERE stm_id = p_stm_id;
323
324 ------------------------------------------------------------
325 -- To Check if a stream element already exists
326 ------------------------------------------------------------
327 CURSOR stm_rec_exists_csr (p_stm_id NUMBER, p_sel_date DATE) IS
328 SELECT count(*)
329 FROM okl_strm_elements
330 WHERE stm_id = p_stm_id
331 AND trunc(STREAM_ELEMENT_DATE) = trunc(p_sel_date);
332
333 ------------------------------------------------------------
334 -- To Check if previously unbilled stream elements exist
335 ------------------------------------------------------------
336 CURSOR prev_unbilled_csr (p_stm_id NUMBER, p_sel_date DATE) IS
337 SELECT count(*)
338 FROM okl_strm_elements
339 WHERE stm_id = p_stm_id
340 AND trunc(STREAM_ELEMENT_DATE) <= trunc(p_sel_date)
341 AND date_billed is NULL;
342
343 ----------------------------------------------------------------------------------------------------
344 -- To get last stream element for RENT or SERVICE AND MAINTENANCE or ESTIMATED PERSONAL PROPERTY TAX
345 -- or FEE
346 ----------------------------------------------------------------------------------------------------
347 CURSOR c_last_strm_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2) IS
348 SELECT * FROM (
349 SELECT ste.id
350 ,ste.stream_element_date
351 ,stm.khr_id
352 ,stm.kle_id
353 ,stm.sty_id
354 FROM okl_strm_elements_v ste
355 ,okl_streams_v stm
356 ,okl_strm_type_v sty
357 WHERE ste.stm_id = stm.id
358 AND stm.khr_id = p_khr_id
359 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
360 AND stm.sty_id = sty.id
361 AND sty.stream_type_purpose = p_sty_purpose
362 ORDER BY ste.stream_element_date DESC
363 )
364 WHERE ROWNUM = 1;
365
366 ------------------------------------------------------------
367 -- Find out whether the stream was securitized
368 ------------------------------------------------------------
369 CURSOR c_sec_strm_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_id NUMBER, p_stream_element_date DATE) IS
370 select distinct khr.id khr_id
371 from okl_pool_contents_v pol
372 , OKL_POOLS pool
373 ,okl_k_headers_full_v khr
374 where pol.khr_id = p_khr_id
375 and nvl(pol.kle_id, -99) = nvl(p_kle_id, -99)
376 and pol.sty_id = p_sty_id
377 and trunc(p_stream_element_date) between trunc(pol.streams_from_date) and trunc(pol.streams_to_date)
378 and pol.pol_id = pool.id
379 and pool.khr_id = khr.id
380 AND pol.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE; --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
381
382 ------------------------------------------------------------
383 -- Stream Cursor
384 ------------------------------------------------------------
385 CURSOR l_stream_csr(cp_khr_id IN NUMBER
386 ,cp_kle_id IN NUMBER
387 ,cp_sty_id IN NUMBER) IS
388 SELECT stm.id
389 FROM okl_streams_v stm
390 WHERE stm.khr_id = cp_khr_id
391 AND nvl(stm.kle_id, -99) = nvl(cp_kle_id, -99)
392 AND stm.sty_id = cp_sty_id
393 AND stm.say_code = 'CURR'
394 AND stm.active_yn = 'Y';
395
396 ------------------------------------------------------------
397 -- Stream Element Line Number Cursor
398 ------------------------------------------------------------
399 CURSOR l_stream_line_nbr_csr(cp_stm_id IN NUMBER) IS
400 SELECT max(se_line_number) se_line_number
401 FROM okl_strm_elements_v
402 WHERE stm_id = cp_stm_id;
403
404 ------------------------------------------------------------
405 -- Initialise constants
406 ------------------------------------------------------------
407
408 l_def_desc CONSTANT VARCHAR2(30) := 'Regular Stream Billing';
409 l_line_code CONSTANT VARCHAR2(30) := 'LINE';
410 l_init_status CONSTANT VARCHAR2(30) := 'ENTERED';
411 l_final_status CONSTANT VARCHAR2(30) := 'SUBMITTED';
412 l_trx_type_name CONSTANT VARCHAR2(30) := 'Billing';
413 l_trx_type_lang CONSTANT VARCHAR2(30) := 'US';
414 l_date_entered CONSTANT DATE := SYSDATE;
415 l_zero_amount CONSTANT NUMBER := 0;
416 l_first_line CONSTANT NUMBER := 1;
417 l_line_step CONSTANT NUMBER := 1;
418 l_def_no_val CONSTANT NUMBER := -1;
419 l_null_kle_id CONSTANT NUMBER := -2;
420
421 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
422 cns_inv_evrgrn_rent_pay constant varchar2(50) := 'INVESTOR_EVERGREEN_RENT_PAY';
423 --not used since Service and Maintenance is not disbursed to investors
424 cns_inv_sm_pay constant varchar2(50) := 'INVESTOR SERVICE AND MAINTENANCE PAY';
425
426 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
427 cns_evergreen_rent constant varchar2(50) := 'RENEWAL_RENT';
428 cns_rent constant varchar2(50) := 'RENT';
429
430 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
431 cns_sm_evergreen constant varchar2(50) := 'SERVICE_RENEWAL';
432 cns_sm constant varchar2(50) := 'SERVICE_PAYMENT';
433
434 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
435 cns_ept_evergreen constant varchar2(50) := 'RENEWAL_PROPERTY_TAX';
436 cns_ept constant varchar2(50) := 'ESTIMATED_PROPERTY_TAX';
437
438 --change for User Defined Streams, by pjgomes, on 22 Feb 2005
439 cns_fee_evergreen constant varchar2(50) := 'FEE_RENEWAL';
440 cns_fee constant varchar2(50) := 'FEE_PAYMENT';
441
442 -- Stream elements
443 p_selv_rec Okl_Streams_Pub.selv_rec_type;
444 x_selv_rec Okl_Streams_Pub.selv_rec_type;
445
446 l_selv_rec Okl_Sel_Pvt.selv_rec_type;
447 lx_selv_rec Okl_Sel_Pvt.selv_rec_type;
448 l_init_selv_rec Okl_Sel_Pvt.selv_rec_type;
449
450 ------------------------------------------------------------
451 -- Declare local variables used in the program
452 ------------------------------------------------------------
453
454 l_khr_id okl_k_headers.id%TYPE;
455 l_kle_id okl_streams.kle_id%TYPE;
456 l_sty_id okl_strm_type_v.id%TYPE;
457 l_evrgrn_strm_purpose okl_strm_type_v.stream_type_purpose%TYPE;
458 l_evrgrn_prim_strm_purpose okl_strm_type_v.stream_type_purpose%TYPE;
459 l_se_line_number OKL_STRM_ELEMENTS_V.SE_LINE_NUMBER%TYPE;
460 l_stm_id OKL_STREAMS_V.ID%TYPE;
461 l_sel_id Okl_strm_elements_v.sel_id%TYPE;
462
463 l_amount NUMBER;
464
465 l_billing_frequency NUMBER;
466
467 l_Stream_bill_date DATE;
468 l_last_Stream_bill_date DATE;
469
470 l_upper_date DATE;
471 l_lower_date DATE;
472
473 l_evergreen_date DATE;
474
475 create_flag VARCHAR2(1);
476 create_payable_flag VARCHAR2(1) := 'N';
477
478 ------------------------------------------------------------
479 -- Declare variables required by APIs
480 ------------------------------------------------------------
481
482 l_api_version CONSTANT NUMBER := 1;
483 l_api_name CONSTANT VARCHAR2(30) := 'BILL_EVERGREEN_STREAMS';
484 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
485
486 -- Streams Record
487 l_stmv_rec Okl_Streams_Pub.stmv_rec_type;
488 lx_stmv_rec Okl_Streams_Pub.stmv_rec_type;
489 l_init_stmv_rec Okl_Streams_Pub.stmv_rec_type;
490
491 -- Temporary variables
492 l_evergreen_rent_count NUMBER;
493 l_contract_rent_count NUMBER;
494 l_evergreen_sm_count NUMBER;
495 l_contract_sm_count NUMBER;
496 l_evergreen_ept_count NUMBER;
497 l_contract_ept_count NUMBER;
498 l_evergreen_fee_count NUMBER;
499 l_contract_fee_count NUMBER;
500
501 l_count NUMBER;
502 l_max_line_num NUMBER;
503 l_rec_exists_cnt NUMBER;
504 l_prev_unbilled_cnt NUMBER;
505 l_investor_agrmt_id NUMBER;
506 l_bill_freq OKC_RULES_B.object1_id1%TYPE;
507 l_primary_sty_id NUMBER;
508 l_primary_for_dep_sty_id NUMBER;
509 l_prev_khr_id okl_k_headers.id%TYPE; --dkagrawa added for bug# 4728636
510
511 -----------------------------------------------------
512 -- Error Processing Variables
513 -----------------------------------------------------
514 l_error_message VARCHAR2(1000);
515 l_error_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
516
517 -- fmiao for bug 4961860
518 -- Cursor to check if the residual value exists in the pool or not
519 CURSOR check_res_in_pool(p_khr_id NUMBER) IS
520 SELECT 'Y'
521 FROM dual
522 WHERE EXISTS(
523 SELECT 1
524 FROM OKL_POOLS pool,
525 okl_pool_contents_v poc,
526 okl_strm_type_v sty
527 WHERE pool.khr_id = p_khr_id AND
528 pool.id = poc.pol_id AND
529 poc.sty_id = sty.id AND
530 sty.stream_type_purpose = 'RESIDUAL_VALUE'
531 AND poc.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE ); --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
532
533 l_res_in_pool VARCHAR2(1);
534 l_evrgrn_psthrgh_flg NUMBER := 0;
535 -- end fmiao for bug 4961860
536
537 l_gen_streams VARCHAR2(1); -- Bug# 11787205
538
539
540
541 BEGIN
542
543 ------------------------------------------------------------
544 -- Start processing
545 ------------------------------------------------------------
546
547 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
548
549 l_return_status := Okl_Api.START_ACTIVITY(
550 p_api_name => l_api_name,
551 p_pkg_name => G_PKG_NAME,
552 p_init_msg_list => p_init_msg_list,
553 l_api_version => l_api_version,
554 p_api_version => p_api_version,
555 p_api_type => '_PVT',
556 x_return_status => l_return_status);
557
558 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
559 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
560 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
561 RAISE Okl_Api.G_EXCEPTION_ERROR;
562 END IF;
563
564 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '=========================================================================================');
565 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ******* Start Evergreen Processing *******');
566 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '=========================================================================================');
567
568 -- Start of code added by Venkatho as part of Bug# 14119181
569 -- Below FOR LOOP distinct_evergreen_khrs is added by venkatho as part of Bankruptcy check
570 FOR distinct_evergreen_khrs IN distinct_evergreen_contracts ( p_contract_number ) -- Added by Venkatho as part of Bug# 14119181
571 LOOP
572 -- Added by Venkatho as part of Bug# 14119181
573 BEGIN
574
575 IF (distinct_evergreen_khrs.bkrpcy_sts = 'Y')
576 THEN
577 IF (G_DEBUG_ENABLED = 'Y') THEN
578 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
579 END IF;
580
581 IF (G_IS_DEBUG_STATEMENT_ON = TRUE) THEN
582 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Invoices of contract => ' || distinct_evergreen_khrs.contract_number || ' not billed due to bankruptcy.');
583 END IF;
584 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Invoices of contract => ' || distinct_evergreen_khrs.contract_number || ' not billed due to bankruptcy.');
585 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Invoices of contract => ' || distinct_evergreen_khrs.contract_number || ' not billed due to bankruptcy.');
586
587 ELSIF (distinct_evergreen_khrs.bkrpcy_sts = 'N' AND distinct_evergreen_khrs.contract_number IS NOT NULL)
588 THEN
589
590 --FOR evergreen_contracts IN evergreen_contracts_csr ( p_contract_number ) LOOP -- Commented by venkatho as part of Bug# 14119181
591 FOR evergreen_contracts IN evergreen_contracts_csr ( distinct_evergreen_khrs.contract_number ) LOOP -- Added by venkatho as part of Bug# 14119181
592 -- End of code added by Venkatho as part of Bug# 14119181
593 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-----------------------------------------------------------------------------------------');
594 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Processing Contract: '||evergreen_contracts.contract_number);
595 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-----------------------------------------------------------------------------------------');
596
597 l_khr_id := NULL;
598 l_kle_id := NULL;
599 l_khr_id := evergreen_contracts.khr_id;
600 l_kle_id := evergreen_contracts.kle_id;
601
602 ------------------------------------------------
603 -- Check if this contract has evergreen streams
604 ------------------------------------------------
605 l_count := 0;
606 OPEN c_stm_count_csr ( l_khr_id, l_kle_id );
607 FETCH c_stm_count_csr INTO l_count;
608 CLOSE c_stm_count_csr;
609
610 --check to see if evergreen rent, evergreen s and m, evergreen ept, evergreen fee exist for khr, kle
611 IF l_count < 4 THEN
612
613 -- Check and insert Evergreen Rent record
614 l_evergreen_rent_count := 0;
615 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
616 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_evergreen_rent );
617 FETCH c_sty_count_csr INTO l_evergreen_rent_count;
618 CLOSE c_sty_count_csr;
619
620 IF l_evergreen_rent_count > 0 THEN
621 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ' || cns_evergreen_rent || ' Streams exist for this contract.');
622 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Proceeding to creation of stream elements.');
623 ELSE
624 l_contract_rent_count := 0;
625
626 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
627 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_rent );
628 FETCH c_sty_count_csr INTO l_contract_rent_count;
629 CLOSE c_sty_count_csr;
630
631 IF l_contract_rent_count > 0 THEN
632 -- Null out records
633 l_stmv_rec := l_init_stmv_rec;
634 lx_stmv_rec := l_init_stmv_rec;
635 l_sty_id := NULL;
636 l_primary_sty_id := NULL;
637 --l_kle_id := NULL;
638
639 ----------------------------------
640 -- Evergreen Billing
641 ----------------------------------
642 /*OPEN get_sty_id_csr ( cns_evergreen_rent );
643 FETCH get_sty_id_csr INTO l_sty_id;
644 CLOSE get_sty_id_csr;*/
645
646 ------------------------------------------------------
647 --Get the sty id for RENT
648 --This sty id will be used as the primary sty id for
649 --obtaining the sty id for EVERGREEN RENT
650 ------------------------------------------------------
651 OPEN get_prim_sty_id_csr(l_khr_id, l_kle_id, cns_rent);
652 FETCH get_prim_sty_id_csr INTO l_primary_sty_id;
653 CLOSE get_prim_sty_id_csr;
654 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Sty id for RENT: ' || l_primary_sty_id);
655
656 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
657 OKL_STREAMS_UTIL.get_dependent_stream_type(p_khr_id => l_khr_id
658 ,p_primary_sty_id => l_primary_sty_id
659 ,p_dependent_sty_purpose => cns_evergreen_rent
660 ,x_return_status => l_return_status
661 ,x_dependent_sty_id => l_sty_id);
662
663 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
664 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_evergreen_rent);
665 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
666 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
667 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_evergreen_rent);
668 RAISE Okl_Api.G_EXCEPTION_ERROR;
669 END IF;
670 --OPEN get_stm_attrs_csr ( l_khr_id , 'RENT' );
671 --LOOP
672 -- FETCH get_stm_attrs_csr INTO l_kle_id;
673
674 /*IF get_stm_attrs_csr%NOTFOUND THEN
675 CLOSE get_stm_attrs_csr;
676 EXIT;
677 END IF;*/
678
679 OPEN c_tran_num_csr;
680 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
681 CLOSE c_tran_num_csr;
682
683
684 l_stmv_rec.sty_id := l_sty_id;
685 l_stmv_rec.khr_id := l_khr_id;
686 IF (l_kle_id <> -99) THEN
687 l_stmv_rec.kle_id := l_kle_id;
688 ELSE
689 l_stmv_rec.kle_id := null;
690 END IF;
691
692 l_stmv_rec.sgn_code := 'MANL';
693 l_stmv_rec.say_code := 'CURR';
694 l_stmv_rec.active_yn := 'Y';
695 l_stmv_rec.date_current := sysdate;
696 l_stmv_rec.comments := 'Evergreen Billing';
697
698 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating EVERGREEN RENT Streams');
699
700 Okl_Streams_Pub.create_streams(
701 p_api_version => p_api_version,
702 p_init_msg_list => p_init_msg_list,
703 x_return_status => x_return_status,
704 x_msg_count => x_msg_count,
705 x_msg_data => x_msg_data,
706 p_stmv_rec => l_stmv_rec,
707 x_stmv_rec => lx_stmv_rec);
708
709 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
710 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for EVERGREEN RENT');
711 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
712 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
713 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for EVERGREEN RENT');
714 RAISE Okl_Api.G_EXCEPTION_ERROR;
715 END IF;
716 /*END LOOP;
717 CLOSE get_stm_attrs_csr;*/
718 ELSE
719 NULL;
720 END IF;
721 END IF;
722
723 -- Check and insert Evergreen Service and Maintenance record
724 l_evergreen_sm_count := 0;
725 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
726 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_sm_evergreen);
727 FETCH c_sty_count_csr INTO l_evergreen_sm_count;
728 CLOSE c_sty_count_csr;
729
730 IF l_evergreen_sm_count > 0 THEN
731 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ' || cns_sm_evergreen || ' Streams exist for this contract.');
732 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Proceeding to creation of stream elements.');
733 ELSE
734 l_contract_sm_count := 0;
735
736 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
737 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_sm );
738 FETCH c_sty_count_csr INTO l_contract_sm_count;
739 CLOSE c_sty_count_csr;
740
741 IF l_contract_sm_count > 0 THEN
742
743 -- Null out records
744 l_stmv_rec := l_init_stmv_rec;
745 lx_stmv_rec := l_init_stmv_rec;
746 l_sty_id := NULL;
747 l_primary_sty_id := NULL;
748 --l_kle_id := NULL;
749
750 ----------------------------------
751 -- Evergreen Billing
752 ----------------------------------
753 /*OPEN get_sty_id_csr ( cns_sm_evergreen );
754 FETCH get_sty_id_csr INTO l_sty_id;
755 CLOSE get_sty_id_csr;*/
756
757 ------------------------------------------------------
758 --Get the sty id for SERVICE AND MAINTENANCE
759 --This sty id will be used as the primary sty id for
760 --obtaining the sty id for EVERGREEN SERVICE AND MAINTENANCE
761 ------------------------------------------------------
762 OPEN get_prim_sty_id_csr(l_khr_id, l_kle_id, cns_sm);
763 FETCH get_prim_sty_id_csr INTO l_primary_sty_id;
764 CLOSE get_prim_sty_id_csr;
765 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Sty id for SERVICE AND MAINTENANCE: ' || l_primary_sty_id);
766
767 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
768 OKL_STREAMS_UTIL.get_dependent_stream_type(p_khr_id => l_khr_id
769 ,p_primary_sty_id => l_primary_sty_id
770 ,p_dependent_sty_purpose => cns_sm_evergreen
771 ,x_return_status => l_return_status
772 ,x_dependent_sty_id => l_sty_id);
773
774 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
775 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_sm_evergreen);
776 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
777 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
778 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_sm_evergreen);
779 RAISE Okl_Api.G_EXCEPTION_ERROR;
780 END IF;
781
782 /*OPEN get_stm_attrs_csr ( l_khr_id , 'SERVICE AND MAINTENANCE' );
783 LOOP
784 FETCH get_stm_attrs_csr INTO l_kle_id;
785
786 IF get_stm_attrs_csr%NOTFOUND THEN
787 CLOSE get_stm_attrs_csr;
788 EXIT;
789 END IF;*/
790
791 OPEN c_tran_num_csr;
792 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
793 CLOSE c_tran_num_csr;
794
795
796 l_stmv_rec.sty_id := l_sty_id;
797 l_stmv_rec.khr_id := l_khr_id;
798 IF (l_kle_id <> -99) THEN
799 l_stmv_rec.kle_id := l_kle_id;
800 ELSE
801 l_stmv_rec.kle_id := null;
802 END IF;
803 l_stmv_rec.sgn_code := 'MANL';
804 l_stmv_rec.say_code := 'CURR';
805 l_stmv_rec.active_yn := 'Y';
806 l_stmv_rec.date_current := sysdate;
807 l_stmv_rec.comments := 'Evergreen Billing';
808
809
810 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating SERVICE AND MAINTENANCE EVERGREEN Streams');
811 Okl_Streams_Pub.create_streams(
812 p_api_version => p_api_version,
813 p_init_msg_list => p_init_msg_list,
814 x_return_status => x_return_status,
815 x_msg_count => x_msg_count,
816 x_msg_data => x_msg_data,
817 p_stmv_rec => l_stmv_rec,
818 x_stmv_rec => lx_stmv_rec);
819
820 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
821 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for SERVICE AND MAINTENANCE EVERGREEN');
822 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
823 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
824 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for SERVICE AND MAINTENANCE EVERGREEN');
825 RAISE Okl_Api.G_EXCEPTION_ERROR;
826 END IF;
827 /*END LOOP;
828 CLOSE c_tran_num_csr;*/
829 ELSE
830 null;
831 END IF;
832 END IF;
833
834 -- Check and insert Estimated Personal Property Tax Evergreen record
835 l_evergreen_ept_count := 0;
836 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
837 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_ept_evergreen );
838 FETCH c_sty_count_csr INTO l_evergreen_ept_count;
839 CLOSE c_sty_count_csr;
840
841 IF l_evergreen_ept_count > 0 THEN
842 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ' || cns_ept_evergreen || ' Streams exist for this contract.');
843 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Proceeding to creation of stream elements.');
844 ELSE
845 l_contract_ept_count := 0;
846
847 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
848 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_ept );
849 FETCH c_sty_count_csr INTO l_contract_ept_count;
850 CLOSE c_sty_count_csr;
851
852 IF l_contract_ept_count > 0 THEN
853 -- Null out records
854 l_stmv_rec := l_init_stmv_rec;
855 lx_stmv_rec := l_init_stmv_rec;
856 l_sty_id := NULL;
857 l_primary_sty_id := NULL;
858 --l_kle_id := NULL;
859
860 ----------------------------------
861 -- Evergreen Billing
862 ----------------------------------
863 /*OPEN get_sty_id_csr ( cns_ept_evergreen );
864 FETCH get_sty_id_csr INTO l_sty_id;
865 CLOSE get_sty_id_csr;*/
866
867 ------------------------------------------------------
868 --Get the sty id for ESTIMATED PROPERTY TAX
869 --This sty id will be used as the primary sty id for
870 --obtaining the sty id for EVERGREEN ESTIMATED PROPERTY TAX
871 ------------------------------------------------------
872 OPEN get_prim_sty_id_csr(l_khr_id, l_kle_id, cns_ept);
873 FETCH get_prim_sty_id_csr INTO l_primary_sty_id;
874 CLOSE get_prim_sty_id_csr;
875 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Sty id for ESTIMATED PROPERTY TAX: ' || l_primary_sty_id);
876
877
878 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
879 OKL_STREAMS_UTIL.get_dependent_stream_type(p_khr_id => l_khr_id
880 ,p_primary_sty_id => l_primary_sty_id
881 ,p_dependent_sty_purpose => cns_ept_evergreen
882 ,x_return_status => l_return_status
883 ,x_dependent_sty_id => l_sty_id);
884
885 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
886 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_ept_evergreen);
887 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
888 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
889 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_ept_evergreen);
890 RAISE Okl_Api.G_EXCEPTION_ERROR;
891 END IF;
892
893 OPEN c_tran_num_csr;
894 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
895 CLOSE c_tran_num_csr;
896
897
898 l_stmv_rec.sty_id := l_sty_id;
899 l_stmv_rec.khr_id := l_khr_id;
900 IF (l_kle_id <> -99) THEN
901 l_stmv_rec.kle_id := l_kle_id;
902 ELSE
903 l_stmv_rec.kle_id := null;
904 END IF;
905
906 l_stmv_rec.sgn_code := 'MANL';
907 l_stmv_rec.say_code := 'CURR';
908 l_stmv_rec.active_yn := 'Y';
909 l_stmv_rec.date_current := sysdate;
910 l_stmv_rec.comments := 'Evergreen Billing';
911
912 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating ESTIMATED PERSONAL PROPERTY TAX EVERGREEN Streams');
913
914 Okl_Streams_Pub.create_streams(
915 p_api_version => p_api_version,
916 p_init_msg_list => p_init_msg_list,
917 x_return_status => x_return_status,
918 x_msg_count => x_msg_count,
919 x_msg_data => x_msg_data,
920 p_stmv_rec => l_stmv_rec,
921 x_stmv_rec => lx_stmv_rec);
922
923 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
924 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for ESTIMATED PERSONAL PROPERTY TAX EVERGREEN');
925 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
926 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
927 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for ESTIMATED PERSONAL PROPERTY TAX EVERGREEN');
928 RAISE Okl_Api.G_EXCEPTION_ERROR;
929 END IF;
930 /*END LOOP;
931 CLOSE get_stm_attrs_csr;*/
932 ELSE
933 NULL;
934 END IF;
935 END IF;
936
937 -- Check and insert Evergreen Fee record
938 l_evergreen_fee_count := 0;
939
940 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_fee_evergreen);
941 FETCH c_sty_count_csr INTO l_evergreen_fee_count;
942 CLOSE c_sty_count_csr;
943
944 IF l_evergreen_fee_count > 0 THEN
945 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ' || cns_fee_evergreen || ' Streams exist for this contract.');
946 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Proceeding to creation of stream elements.');
947 ELSE
948 l_contract_fee_count := 0;
949
950 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_fee );
951 FETCH c_sty_count_csr INTO l_contract_fee_count;
952 CLOSE c_sty_count_csr;
953
954 IF l_contract_fee_count > 0 THEN
955
956 -- Bug# 11787205: start
957 check_fee_evergreen_bill(x_return_status => l_return_status,
958 p_chr_id => l_khr_id,
959 p_kle_id => l_kle_id,
960 x_gen_streams => l_gen_streams);
961
962 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
963 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-- ERROR: Checking evergreen billing eligibility for: ' || l_kle_id);
964 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
965 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
966 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-- ERROR: Checking evergreen billing eligibility for: ' || l_kle_id);
967 RAISE Okl_Api.G_EXCEPTION_ERROR;
968 END IF;
969
970 if l_gen_streams = 'Y' then -- { Bug# 11787205: end
971
972 -- Null out records
973 l_stmv_rec := l_init_stmv_rec;
974 lx_stmv_rec := l_init_stmv_rec;
975 l_sty_id := NULL;
976 l_primary_sty_id := NULL;
977 --l_kle_id := NULL;
978
979 ----------------------------------
980 -- Evergreen Billing
981 ----------------------------------
982
983 ------------------------------------------------------
984 --Get the sty id for FEE
985 --This sty id will be used as the primary sty id for
986 --obtaining the sty id for EVERGREEN FEE
987 ------------------------------------------------------
988 OPEN get_prim_sty_id_csr(l_khr_id, l_kle_id, cns_fee);
989 FETCH get_prim_sty_id_csr INTO l_primary_sty_id;
990 CLOSE get_prim_sty_id_csr;
991 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Sty id for FEE: ' || l_primary_sty_id);
992
993 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
994 OKL_STREAMS_UTIL.get_dependent_stream_type(p_khr_id => l_khr_id
995 ,p_primary_sty_id => l_primary_sty_id
996 ,p_dependent_sty_purpose => cns_fee_evergreen
997 ,x_return_status => l_return_status
998 ,x_dependent_sty_id => l_sty_id);
999
1000 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1001 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_fee_evergreen);
1002 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1003 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1004 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_fee_evergreen);
1005 RAISE Okl_Api.G_EXCEPTION_ERROR;
1006 END IF;
1007
1008
1009 OPEN c_tran_num_csr;
1010 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
1011 CLOSE c_tran_num_csr;
1012
1013
1014 l_stmv_rec.sty_id := l_sty_id;
1015 l_stmv_rec.khr_id := l_khr_id;
1016 IF (l_kle_id <> -99) THEN
1017 l_stmv_rec.kle_id := l_kle_id;
1018 ELSE
1019 l_stmv_rec.kle_id := null;
1020 END IF;
1021 l_stmv_rec.sgn_code := 'MANL';
1022 l_stmv_rec.say_code := 'CURR';
1023 l_stmv_rec.active_yn := 'Y';
1024 l_stmv_rec.date_current := sysdate;
1025 l_stmv_rec.comments := 'Evergreen Billing';
1026
1027
1028 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating FEE EVERGREEN Streams');
1029 Okl_Streams_Pub.create_streams(
1030 p_api_version => p_api_version,
1031 p_init_msg_list => p_init_msg_list,
1032 x_return_status => x_return_status,
1033 x_msg_count => x_msg_count,
1034 x_msg_data => x_msg_data,
1035 p_stmv_rec => l_stmv_rec,
1036 x_stmv_rec => lx_stmv_rec);
1037
1038 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1039 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for FEE EVERGREEN');
1040 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1041 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1042 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for FEE EVERGREEN');
1043 RAISE Okl_Api.G_EXCEPTION_ERROR;
1044 END IF;
1045 /*END LOOP;
1046 CLOSE c_tran_num_csr;*/
1047 -- Bug# 11787205: start
1048 END IF; -- if l_gen_streams = 'Y'}
1049 -- Bug# 11787205: end
1050 ELSE
1051 null;
1052 END IF;
1053 END IF;
1054
1055 ELSE
1056 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Streams for Evergreen Billing exist for this contract.');
1057 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Proceeding to creation of stream elements for Evergreen Billing.');
1058 END IF;
1059
1060 FOR stms IN c_stm_id( l_khr_id, l_kle_id ) LOOP
1061
1062 l_error_message := NULL;
1063 l_error_status := Okl_Api.G_RET_STS_SUCCESS;
1064
1065 -----------------------------------------
1066 -- Initialize the date fields to null
1067 -----------------------------------------
1068
1069 l_amount := NULL;
1070 l_Stream_bill_date := NULL;
1071 l_upper_date := NULL;
1072 l_lower_date := NULL;
1073 l_evrgrn_strm_purpose := NULL;
1074 l_evrgrn_prim_strm_purpose := NULL;
1075 create_payable_flag := 'N';
1076
1077 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1078 IF (stms.sty_name = cns_evergreen_rent) THEN
1079
1080 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1081 OPEN upper_rental_date_csr ( l_khr_id , l_kle_id, cns_rent );
1082 FETCH upper_rental_date_csr INTO l_upper_date;
1083 CLOSE upper_rental_date_csr;
1084
1085 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1086 OPEN lower_rental_date_csr ( l_khr_id , l_kle_id, cns_rent, l_upper_date );
1087 FETCH lower_rental_date_csr INTO l_lower_date;
1088 CLOSE lower_rental_date_csr;
1089
1090 -------------------------------
1091 -- Fetch Billing Amount
1092 -------------------------------
1093 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1094 OPEN bill_amt_csr ( l_khr_id , l_kle_id, cns_rent );
1095 FETCH bill_amt_csr INTO l_amount;
1096 CLOSE bill_amt_csr;
1097
1098 ----------------------------------------------------------------
1099 --GET THE LAST RENT STREAM ELEMENT DETAILS
1100 ----------------------------------------------------------------
1101 create_payable_flag := 'N';
1102 l_evrgrn_strm_purpose := null;
1103 l_evrgrn_prim_strm_purpose := null;
1104 l_investor_agrmt_id := null;
1105
1106 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Contract id: ' || l_khr_id || ' Line id: ' || l_kle_id);
1107 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1108 FOR cur_last_strm IN c_last_strm_csr ( l_khr_id , l_kle_id, cns_rent) LOOP
1109 ----------------------------------------------------------------
1110 --CHECK IF THE LAST RENT STREAM WAS SECURITIZED
1111 ----------------------------------------------------------------
1112 FOR cur_sec_strm IN c_sec_strm_csr ( cur_last_strm.khr_id , cur_last_strm.kle_id , cur_last_strm.sty_id , cur_last_strm.stream_element_date ) LOOP
1113 create_payable_flag := 'Y';
1114 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1115 l_evrgrn_strm_purpose := cns_inv_evrgrn_rent_pay;
1116 l_evrgrn_prim_strm_purpose := cns_evergreen_rent;
1117 l_investor_agrmt_id := cur_sec_strm.khr_id;
1118 exit;
1119 END LOOP;
1120 exit;
1121 END LOOP;
1122 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1123 ELSIF (stms.sty_name = cns_sm_evergreen) THEN -- The stream is 'SERVICE AND MAINTENANCE EVERGREEN'
1124 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1125 OPEN upper_rental_date_csr ( l_khr_id , l_kle_id , cns_sm );
1126 FETCH upper_rental_date_csr INTO l_upper_date;
1127 CLOSE upper_rental_date_csr;
1128
1129 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1130 OPEN lower_rental_date_csr ( l_khr_id , l_kle_id , cns_sm, l_upper_date );
1131 FETCH lower_rental_date_csr INTO l_lower_date;
1132 CLOSE lower_rental_date_csr;
1133
1134 -------------------------------
1135 -- Fetch Billing Amount
1136 -------------------------------
1137 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1138 OPEN bill_amt_csr ( l_khr_id , l_kle_id , cns_sm );
1139 FETCH bill_amt_csr INTO l_amount;
1140 CLOSE bill_amt_csr;
1141
1142 ----------------------------------------------------------------
1143 --GET THE LAST SERVICE AND MAINTENANCE STREAM ELEMENT DETAILS
1144 ----------------------------------------------------------------
1145 create_payable_flag := 'N';
1146 l_evrgrn_strm_purpose := null;
1147 l_evrgrn_prim_strm_purpose := null;
1148 l_investor_agrmt_id := null;
1149 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Contract id: ' || l_khr_id || ' Line id: ' || l_kle_id);
1150 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1151 FOR cur_last_strm IN c_last_strm_csr ( l_khr_id , l_kle_id, cns_sm) LOOP
1152 ----------------------------------------------------------------
1153 --CHECK IF THE LAST SERVICE AND MAINTENANCE STREAM WAS SECURITIZED
1154 ----------------------------------------------------------------
1155 FOR cur_sec_strm IN c_sec_strm_csr ( cur_last_strm.khr_id , cur_last_strm.kle_id , cur_last_strm.sty_id , cur_last_strm.stream_element_date ) LOOP
1156 create_payable_flag := 'Y';
1157 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1158 l_evrgrn_strm_purpose := cns_inv_sm_pay;
1159 l_evrgrn_prim_strm_purpose := cns_sm_evergreen;
1160 l_investor_agrmt_id := cur_sec_strm.khr_id;
1161 exit;
1162 END LOOP;
1163 exit;
1164 END LOOP;
1165 ELSIF (stms.sty_name = cns_ept_evergreen) THEN-- The stream is 'ESTIMATED PERSONAL PROPERTY TAX EVERGREEN'
1166
1167 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1168 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1169 OPEN upper_rental_date_csr ( l_khr_id , l_kle_id, cns_ept );
1170 FETCH upper_rental_date_csr INTO l_upper_date;
1171 CLOSE upper_rental_date_csr;
1172
1173 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1174 OPEN lower_rental_date_csr ( l_khr_id , l_kle_id, cns_ept, l_upper_date );
1175 FETCH lower_rental_date_csr INTO l_lower_date;
1176 CLOSE lower_rental_date_csr;
1177
1178 -------------------------------
1179 -- Fetch Billing Amount
1180 -------------------------------
1181 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1182 OPEN bill_amt_csr ( l_khr_id , l_kle_id, cns_ept );
1183 FETCH bill_amt_csr INTO l_amount;
1184 CLOSE bill_amt_csr;
1185
1186 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Contract id: ' || l_khr_id || ' Line id: ' || l_kle_id);
1187
1188 create_payable_flag := 'N';
1189 l_evrgrn_strm_purpose := null;
1190 l_evrgrn_prim_strm_purpose := null;
1191 ELSE -- The stream is 'FEE EVERGREEN'
1192
1193 OPEN upper_rental_date_csr ( l_khr_id , l_kle_id, cns_fee );
1194 FETCH upper_rental_date_csr INTO l_upper_date;
1195 CLOSE upper_rental_date_csr;
1196
1197 OPEN lower_rental_date_csr ( l_khr_id , l_kle_id, cns_fee, l_upper_date );
1198 FETCH lower_rental_date_csr INTO l_lower_date;
1199 CLOSE lower_rental_date_csr;
1200
1201 -------------------------------
1202 -- Fetch Billing Amount
1203 -------------------------------
1204 OPEN bill_amt_csr ( l_khr_id , l_kle_id, cns_fee );
1205 FETCH bill_amt_csr INTO l_amount;
1206 CLOSE bill_amt_csr;
1207
1208 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Contract id: ' || l_khr_id || ' Line id: ' || l_kle_id);
1209
1210 create_payable_flag := 'N';
1211 l_evrgrn_strm_purpose := null;
1212 l_evrgrn_prim_strm_purpose := null;
1213 END IF;
1214
1215
1216 ------------------------------------------------------------
1217 --Check if Stream Elements exist already
1218 ------------------------------------------------------------
1219 l_evergreen_date := NULL;
1220
1221 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1222 OPEN evergreen_element_csr ( l_khr_id , l_kle_id , stms.sty_name );
1223 FETCH evergreen_element_csr INTO l_evergreen_date;
1224 CLOSE evergreen_element_csr;
1225
1226 l_last_Stream_bill_date := NULL;
1227 IF l_evergreen_date IS NOT NULL THEN
1228 l_last_Stream_bill_date := l_evergreen_date;
1229 ELSE -- No evergreen elements exist.
1230 l_last_Stream_bill_date := l_upper_date;
1231 END IF;
1232
1233 ------------------------------------------------------------
1234 --Determine billing frequency
1235 ------------------------------------------------------------
1236
1237 l_bill_freq := NULL;
1238 OPEN c_bill_freq_csr ( l_khr_id );
1239 FETCH c_bill_freq_csr INTO l_bill_freq;
1240 CLOSE c_bill_freq_csr;
1241
1242 ------------------------------------------
1243 -- Add frequency to date
1244 ------------------------------------------
1245 l_Stream_bill_date := NULL;
1246 --dkagrawa BUG#4604842 start
1247 -- calling okl_stream_generator_pvt.add_months_new to determine the next bill date
1248 l_billing_frequency := NULL;
1249 IF l_bill_freq = 'A' THEN
1250 l_billing_frequency := 12;
1251 ELSIF l_bill_freq = 'S' THEN
1252 l_billing_frequency := 6;
1253 ELSIF l_bill_freq = 'Q' THEN
1254 l_billing_frequency := 3;
1255 ELSIF l_bill_freq = 'M' THEN
1256 l_billing_frequency := 1;
1257 ELSE
1258 l_error_message := 'Invalid Billing Frequency. ';
1259 l_error_status := Okl_Api.G_RET_STS_ERROR;
1260 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: '||l_error_message);
1261 END IF;
1262 IF l_billing_frequency IS NOT NULL THEN
1263 OKL_STREAM_GENERATOR_PVT.add_months_new(p_start_date => l_last_Stream_bill_date,
1264 p_months_after => l_billing_frequency,
1265 x_date => l_Stream_bill_date,
1266 x_return_status => x_return_status);
1267 END IF;
1268 --dkagrawa BUG#4604842 end
1269
1270
1271 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' => Last Billed: '||l_last_Stream_bill_date);
1272 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' => Next Determined: '||l_Stream_bill_date);
1273
1274 ------------------------------------------------------------
1275 -- If the program has a from and to date supplied the
1276 -- evergreen stream element must be between the two
1277 ------------------------------------------------------------
1278 create_flag := 'Y';
1279
1280 IF p_from_bill_date IS NOT NULL THEN
1281 IF (l_Stream_bill_date < p_from_bill_date) THEN
1282 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Bill_DATE is less than supplied From Date.');
1283 create_flag := 'N';
1284 END IF;
1285 END IF;
1286
1287 IF p_to_bill_date IS NOT NULL THEN
1288 IF (l_Stream_bill_date > p_to_bill_date) THEN
1289 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Bill_DATE is greater than supplied To Date.');
1290 create_flag := 'N';
1291 END IF;
1292 END IF;
1293
1294 -- Check if there is an unbilled Stream element
1295 -- outstanding for this contract
1296
1297 IF ( create_flag = 'Y' ) THEN
1298 l_prev_unbilled_cnt:= 0;
1299
1300 OPEN prev_unbilled_csr ( stms.stm_id , l_last_Stream_bill_date );
1301 FETCH prev_unbilled_csr INTO l_prev_unbilled_cnt;
1302 CLOSE prev_unbilled_csr;
1303 -- CHEck if an unbilled stream element exists for the same date
1304 -- and set the create flag
1305 IF l_prev_unbilled_cnt > 0 THEN
1306 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Previously Unbilled Stream Elements Exist.');
1307 create_flag := 'N';
1308 END IF;
1309 END IF;
1310
1311 -- Check if there is an unbilled Stream element
1312 -- with the same date
1313 IF ( create_flag = 'Y' ) THEN
1314
1315 l_rec_exists_cnt := 0;
1316
1317 OPEN stm_rec_exists_csr( stms.stm_id , l_Stream_bill_date );
1318 FETCH stm_rec_exists_csr INTO l_rec_exists_cnt;
1319 CLOSE stm_rec_exists_csr;
1320 -- CHEck if an unbilled stream element exists for the same date
1321 -- and set the create flag
1322 IF l_rec_exists_cnt > 0 THEN
1323 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Duplicate Stream Element.');
1324 create_flag := 'N';
1325 END IF;
1326 END IF;
1327
1328 ------------------------------------------------------------
1329 --Proceed to Create if within Date Ranges
1330 ------------------------------------------------------------
1331
1332 IF ( create_flag = 'Y' ) THEN
1333
1334 ------------------------------------------------------------
1335 --Create Stream elements for evergreen
1336 ------------------------------------------------------------
1337 IF ( l_amount IS NOT NULL AND l_amount > 0) THEN
1338
1339 l_max_line_num := 0;
1340 OPEN max_line_num_csr ( stms.stm_id );
1341 FETCH max_line_num_csr INTO l_max_line_num;
1342 CLOSE max_line_num_csr;
1343
1344 p_selv_rec.stm_id := stms.stm_id;
1345 p_selv_rec.SE_LINE_NUMBER := NVL( l_max_line_num, 0 ) + 1;
1346 p_selv_rec.STREAM_ELEMENT_DATE := l_Stream_bill_date;
1347 p_selv_rec.AMOUNT := l_amount;
1348 p_selv_rec.COMMENTS := 'EVERGREEN BILLING ELEMENTS';
1349 p_selv_rec.ACCRUED_YN := 'Y';
1350
1351 Okl_Sel_Pvt.insert_row(
1352 p_api_version,
1353 p_init_msg_list,
1354 x_return_status,
1355 x_msg_count,
1356 x_msg_data,
1357 p_selv_rec,
1358 x_selv_rec);
1359
1360 l_sel_id := x_selv_rec.id;
1361
1362 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' --Evergreen Stream Element id: ' || l_sel_id);
1363 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1364 l_error_message := 'Error Creating Stream Element for Contract: '
1365 ||stms.contract_number
1366 ||' Stream: '||stms.sty_name
1367 ||' Bill Date: '||l_Stream_bill_date
1368 ||' Amount: '||l_amount;
1369 l_error_status := Okl_Api.G_RET_STS_ERROR;
1370 --RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1371 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1372 l_error_message := 'Error Creating Stream Element for Contract: '
1373 ||stms.contract_number
1374 ||' Stream: '||stms.sty_name
1375 ||' Bill Date: '||l_Stream_bill_date
1376 ||' Amount: '||l_amount;
1377 l_error_status := Okl_Api.G_RET_STS_ERROR;
1378 --RAISE Okl_Api.G_EXCEPTION_ERROR;
1379 ELSE
1380
1381 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Created Evergreen Stream Element for Contract: '
1382 ||stms.contract_number
1383 ||' Stream: '||stms.sty_name
1384 ||' Bill Date: '||l_Stream_bill_date
1385 ||' Amount: '||l_amount
1386 );
1387 END IF;
1388
1389 IF l_evrgrn_strm_purpose IS NOT NULL AND l_error_status = Okl_Api.G_RET_STS_SUCCESS THEN
1390
1391 -- Added by fmiao for bug 4961860
1392 l_evrgrn_psthrgh_flg := 0;
1393 IF(l_evrgrn_strm_purpose = 'INVESTOR_EVERGREEN_RENT_PAY') THEN
1394 OPEN check_res_in_pool(l_investor_agrmt_id);
1395 FETCH check_res_in_pool INTO l_res_in_pool;
1396 CLOSE check_res_in_pool;
1397 IF(l_res_in_pool IS NULL OR l_res_in_pool <> 'Y') THEN
1398 l_evrgrn_psthrgh_flg := 1;
1399 END IF;
1400 END IF;
1401 -- end fmiao for bug 4961860
1402
1403 IF(l_evrgrn_psthrgh_flg = 0) THEN
1404 -- Added by fmiao for bug 4961860
1405 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- EVERGREEN PAYABLE STREAM TYPE: ' || l_evrgrn_strm_purpose);
1406 ----------------------------------------------------------------
1407 --PROCESSING FOR EVERGREEN STREAM TYPE PAYABLE TO INVESTOR
1408 ----------------------------------------------------------------
1409 --get stream type id
1410 l_sty_id := null;
1411 l_primary_sty_id := NULL;
1412
1413 /*OPEN get_sty_id_csr(l_evrgrn_strm_purpose);
1414 FETCH get_sty_id_csr INTO l_sty_id;
1415 CLOSE get_sty_id_csr;*/
1416
1417 ------------------------------------------------------
1418 --Get the sty id for Evergreen Payable
1419 ------------------------------------------------------
1420 /*OPEN get_prim_sty_id_csr(l_khr_id, l_kle_id, l_evrgrn_prim_strm_purpose);
1421 FETCH get_prim_sty_id_csr INTO l_primary_sty_id;
1422 CLOSE get_prim_sty_id_csr;
1423 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Sty id for ' || l_evrgrn_prim_strm_purpose || ': ' || l_primary_sty_id); */
1424
1425 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1426 OKL_STREAMS_UTIL.get_primary_stream_type(p_khr_id => l_investor_agrmt_id
1427 ,p_primary_sty_purpose => l_evrgrn_strm_purpose
1428 ,x_return_status => l_return_status
1429 ,x_primary_sty_id => l_sty_id);
1430
1431 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1432 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || l_evrgrn_strm_purpose);
1433 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1434 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1435 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || l_evrgrn_strm_purpose);
1436 RAISE Okl_Api.G_EXCEPTION_ERROR;
1437 END IF;
1438
1439 --check for stream
1440 l_stm_id := null;
1441 l_se_line_number := null;
1442
1443 OPEN l_stream_csr(l_khr_id, l_kle_id, l_sty_id);
1444 FETCH l_stream_csr INTO l_stm_id;
1445 CLOSE l_stream_csr;
1446
1447 --create stream for evergreen payable
1448 IF (l_stm_id IS NULL) THEN
1449 l_stmv_rec := l_init_stmv_rec;
1450
1451 OPEN c_tran_num_csr;
1452 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
1453 CLOSE c_tran_num_csr;
1454
1455 l_stmv_rec.sty_id := l_sty_id;
1456 l_stmv_rec.khr_id := l_khr_id;
1457 --l_stmv_rec.kle_id := l_kle_id;
1458 IF (l_kle_id <> -99) THEN
1459 l_stmv_rec.kle_id := l_kle_id;
1460 ELSE
1461 l_stmv_rec.kle_id := null;
1462 END IF;
1463 l_stmv_rec.sgn_code := 'MANL';
1464 l_stmv_rec.say_code := 'CURR';
1465 l_stmv_rec.active_yn := 'Y';
1466 l_stmv_rec.date_current := sysdate;
1467 l_stmv_rec.comments := l_evrgrn_strm_purpose;
1468 IF (l_investor_agrmt_id IS NOT NULL) THEN
1469 l_stmv_rec.source_id := l_investor_agrmt_id;
1470 l_stmv_rec.source_table := 'OKL_K_HEADERS';
1471 END IF;
1472
1473 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating ' || l_evrgrn_strm_purpose || ' Stream');
1474
1475 Okl_Streams_Pub.create_streams(
1476 p_api_version => p_api_version,
1477 p_init_msg_list => p_init_msg_list,
1478 x_return_status => x_return_status,
1479 x_msg_count => x_msg_count,
1480 x_msg_data => x_msg_data,
1481 p_stmv_rec => l_stmv_rec,
1482 x_stmv_rec => lx_stmv_rec);
1483
1484 l_stm_id := lx_stmv_rec.id;
1485 l_se_line_number := 1;
1486
1487 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream element line number => ' || l_se_line_number);
1488 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1489 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Stream for ' || l_evrgrn_strm_purpose);
1490 RAISE Okl_Api.G_EXCEPTION_ERROR;
1491 ELSE
1492 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- SUCCESS: Creating Stream for ' || l_evrgrn_strm_purpose);
1493 END IF;
1494 ELSE
1495 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream for ' || l_evrgrn_strm_purpose || ' found');
1496 open l_stream_line_nbr_csr(l_stm_id);
1497 fetch l_stream_line_nbr_csr into l_se_line_number;
1498 close l_stream_line_nbr_csr;
1499 l_se_line_number := l_se_line_number + 1;
1500 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream element line number => ' || l_se_line_number);
1501 END IF;
1502
1503 --create stream element for evergreen stream payable
1504 IF (l_stm_id IS NOT NULL) THEN
1505 l_selv_rec := l_init_selv_rec;
1506 l_selv_rec.stm_id := l_stm_id;
1507 l_selv_rec.SE_LINE_NUMBER := l_se_line_number;
1508 l_selv_rec.STREAM_ELEMENT_DATE := sysdate;
1509 l_selv_rec.AMOUNT := l_amount;
1510 l_selv_rec.COMMENTS := l_evrgrn_strm_purpose || ' ELEMENTS';
1511 l_selv_rec.ACCRUED_YN := 'Y';
1512
1513 l_selv_rec.sel_id := l_sel_id;
1514 IF (l_investor_agrmt_id IS NOT NULL) THEN
1515 l_selv_rec.source_id := l_investor_agrmt_id;
1516 l_selv_rec.source_table := 'OKL_K_HEADERS';
1517 END IF;
1518
1519 Okl_Sel_Pvt.insert_row(
1520 p_api_version,
1521 p_init_msg_list,
1522 x_return_status,
1523 x_msg_count,
1524 x_msg_data,
1525 l_selv_rec,
1526 lx_selv_rec);
1527
1528 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1529 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Error Creating Payable Stream Element for Contract: '
1530 || evergreen_contracts.contract_number
1531 ||' Stream: '||l_evrgrn_strm_purpose
1532 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
1533 ||' Amount: '||l_amount);
1534 RAISE Okl_Api.G_EXCEPTION_ERROR;
1535 ELSE
1536
1537 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Created Investor Payable Stream Element for Contract: '
1538 || evergreen_contracts.contract_number
1539 ||' Stream: '||l_evrgrn_strm_purpose
1540 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
1541 ||' Amount: '||l_amount
1542 );
1543 END IF;
1544 END IF;
1545 END IF; -- Added by fmiao for bug 4961860
1546 END IF;
1547
1548 END IF;
1549 END IF;
1550
1551 IF l_error_status <> OKL_API.G_RET_STS_SUCCESS THEN
1552 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: '||l_error_message);
1553 END IF;
1554
1555 END LOOP;
1556 --dkagrawa bug# 4728636 changes start
1557 IF l_prev_khr_id IS NULL THEN
1558 l_prev_khr_id := evergreen_contracts.khr_id;
1559 END IF;
1560 IF l_prev_khr_id <> evergreen_contracts.khr_id THEN
1561 IF (l_error_status = OKL_API.G_RET_STS_SUCCESS) THEN
1562 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date ( l_prev_khr_id );
1563 END IF;
1564 l_prev_khr_id := evergreen_contracts.khr_id;
1565 END IF;
1566 END LOOP;
1567 IF l_prev_khr_id IS NOT NULL THEN
1568 IF (l_error_status = OKL_API.G_RET_STS_SUCCESS) THEN
1569 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date ( l_prev_khr_id );
1570 END IF;
1571 END IF;
1572 --dkagrawa bug# 4728636 changes end
1573
1574 END IF; -- (distinct_evergreen_khrs.bkrpcy_sts = 'Y') Added as part of Bug# 14119181
1575
1576 END; -- Added as part of Bug# 14119181
1577 END LOOP; -- FOR distinct_evergreen_khrs added as part of Bug# 14119181
1578
1579 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '=========================================================================================');
1580 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ******* End Evergreen Processing *******');
1581 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '=========================================================================================');
1582
1583 ------------------------------------------------------------
1584 -- End processing
1585 ------------------------------------------------------------
1586
1587
1588 x_return_status := l_return_status;
1589 Okl_Api.END_ACTIVITY (
1590 x_msg_count => x_msg_count,
1591 x_msg_data => x_msg_data);
1592
1593
1594 EXCEPTION
1595
1596 ------------------------------------------------------------
1597 -- Exception handling
1598 ------------------------------------------------------------
1599
1600 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1601
1602 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1603 p_api_name => l_api_name,
1604 p_pkg_name => G_PKG_NAME,
1605 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
1606 x_msg_count => x_msg_count,
1607 x_msg_data => x_msg_data,
1608 p_api_type => '_PVT');
1609
1610 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1611
1612 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1613 p_api_name => l_api_name,
1614 p_pkg_name => G_PKG_NAME,
1615 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1616 x_msg_count => x_msg_count,
1617 x_msg_data => x_msg_data,
1618 p_api_type => '_PVT');
1619
1620 WHEN OTHERS THEN
1621
1622 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1623 p_api_name => l_api_name,
1624 p_pkg_name => G_PKG_NAME,
1625 p_exc_name => 'OTHERS',
1626 x_msg_count => x_msg_count,
1627 x_msg_data => x_msg_data,
1628 p_api_type => '_PVT');
1629
1630 END BILL_EVERGREEN_STREAMS;
1631
1632
1633 -- Bug# 11787205: start
1634 PROCEDURE check_fee_evergreen_bill (
1635 x_return_status OUT NOCOPY VARCHAR2,
1636 p_chr_id IN NUMBER,
1637 p_kle_id IN NUMBER DEFAULT NULL,
1638 x_gen_streams OUT NOCOPY VARCHAR2
1639 ) IS
1640
1641 l_api_version CONSTANT NUMBER := 1;
1642 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_FEE_EVERGREEN_BILL';
1643 x_msg_count NUMBER;
1644 x_msg_data VARCHAR2(2000);
1645
1646 CURSOR c_assets_csr(p_chr_id OKL_K_HEADERS.KHR_ID%TYPE) IS
1647 SELECT kle.name,
1648 kle.id,
1649 kle.fee_type,
1650 ls.lty_code,
1651 kle.sts_code
1652 FROM OKL_K_LINES_FULL_V kle,
1653 OKC_LINE_STYLES_B ls,
1654 OKC_STATUSES_B sts
1655 WHERE kle.lse_id = ls.id
1656 AND ls.lty_code = 'FREE_FORM1'
1657 AND kle.dnz_chr_id = p_chr_id
1658 AND sts.code = kle.sts_code
1659 AND sts.ste_code not in ( 'HOLD', 'EXPIRED', 'CANCELLED', 'TERMINATED');
1660
1661 CURSOR c_fee_lines_csr(p_chr_id OKL_K_HEADERS.KHR_ID%TYPE) IS
1662 SELECT kle.name,
1663 kle.id,
1664 kle.fee_type
1665 FROM OKL_K_LINES_FULL_V kle,
1666 OKC_LINE_STYLES_B ls,
1667 OKC_STATUSES_B sts
1668 WHERE kle.lse_id = ls.id
1669 AND ls.lty_code = 'FEE'
1670 AND kle.dnz_chr_id = p_chr_id
1671 AND sts.code = kle.sts_code
1672 AND ((p_kle_id is not null and kle.id = p_kle_id) OR p_kle_id is null)
1673 AND kle.fee_type NOT IN ('FINANCED','ROLLOVER')
1674 AND sts.ste_code not in ( 'HOLD', 'EXPIRED', 'CANCELLED');
1675
1676 cursor l_sll_csr( p_shid NUMBER,
1677 p_rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
1678 p_rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
1679 p_chrId NUMBER,
1680 p_cleId NUMBER ) IS
1681 select crl.id slh_id,
1682 DECODE(crl.object1_id1,'M',1,'Q',3,'S',6,'A',12) frequency_factor,
1683 FND_DATE.canonical_to_date(crl.RULE_INFORMATION2) start_date,
1684 TO_NUMBER(crl.RULE_INFORMATION3) periods,
1685 crl.RULE_INFORMATION5 structure,
1686 TO_NUMBER(crl.RULE_INFORMATION7) stub_days,
1687 crl.RULE_INFORMATION10 advance_arrears
1688 from OKC_RULE_GROUPS_B crg,
1689 OKC_RULES_B crl
1690 where crl.object2_id1 = p_shid
1691 and crl.rgp_id = crg.id
1692 and crg.RGD_CODE = p_rgcode
1693 and crl.RULE_INFORMATION_CATEGORY = p_rlcat
1694 and crg.dnz_chr_id = p_chrId
1695 and nvl(crg.cle_id,-1) = nvl(p_cleId, -1)
1696 and FND_DATE.canonical_to_date(crl.rule_information2) = (select max(FND_DATE.canonical_to_date(crl.RULE_INFORMATION2))
1697 from okc_rules_b crl2
1698 where crl2.rgp_id = crl.rgp_id
1699 and crl2.RULE_INFORMATION_CATEGORY = crl.RULE_INFORMATION_CATEGORY);
1700
1701 cursor l_asset_rl_csr1( p_rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
1702 p_rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
1703 p_chrId NUMBER,
1704 p_cleId NUMBER ) IS
1705 select crl.id slh_id,
1706 crl.object1_id1,
1707 crl.RULE_INFORMATION1,
1708 crl.RULE_INFORMATION2,
1709 crl.RULE_INFORMATION3,
1710 crl.RULE_INFORMATION5,
1711 crl.RULE_INFORMATION6,
1712 crl.RULE_INFORMATION7,
1713 crl.RULE_INFORMATION8,
1714 crl.RULE_INFORMATION10
1715 from OKC_RULE_GROUPS_B crg,
1716 OKC_RULES_B crl,
1717 OKL_STRM_TYPE_B STY
1718 where crl.rgp_id = crg.id
1719 and crg.RGD_CODE = p_rgcode
1720 and crl.RULE_INFORMATION_CATEGORY = p_rlcat
1721 and crg.dnz_chr_id = p_chrId
1722 and nvl(crg.cle_id,-1) = p_cleId
1723 and crl.object1_id1 = sty.id
1724 and sty.stream_type_purpose = 'RENT'
1725 order by crl.RULE_INFORMATION1;
1726
1727 l_asset_rl_rec l_asset_rl_csr1%ROWTYPE;
1728
1729 cursor l_rl_csr1( rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
1730 rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
1731 chrId NUMBER,
1732 cleId NUMBER ) IS
1733 select crl.id slh_id,
1734 crl.object1_id1,
1735 crl.RULE_INFORMATION1,
1736 crl.RULE_INFORMATION2,
1737 crl.RULE_INFORMATION3,
1738 crl.RULE_INFORMATION5,
1739 crl.RULE_INFORMATION6,
1740 crl.RULE_INFORMATION7,
1741 crl.RULE_INFORMATION8,
1742 crl.RULE_INFORMATION10
1743 from OKC_RULE_GROUPS_B crg,
1744 OKC_RULES_B crl
1745 where crl.rgp_id = crg.id
1746 and crg.RGD_CODE = rgcode
1747 and crl.RULE_INFORMATION_CATEGORY = rlcat
1748 and crg.dnz_chr_id = chrId
1749 and nvl(crg.cle_id,-1) = cleId
1750 order by crl.RULE_INFORMATION1;
1751
1752 CURSOR c_arrears_option_at_sgt_csr (p_pdt_id NUMBER)
1753 IS
1754 SELECT tst.isg_arrears_pay_dates_option
1755 FROM okl_st_gen_tmpt_sets tst,
1756 okl_ae_tmpt_sets aes,
1757 okl_products pdt
1758 WHERE pdt.id = p_pdt_id
1759 AND aes.id = pdt.aes_id
1760 AND tst.id = aes.gts_id;
1761
1762 CURSOR l_hdr_pdt_csr(p_chrId NUMBER)
1763 IS
1764 SELECT pdt.id pid,
1765 NVL(pdt.reporting_pdt_id, -1) report_pdt_id
1766 FROM okc_k_headers_v chr,
1767 okl_k_headers khr,
1768 okl_products_v pdt
1769 WHERE khr.id = chr.id
1770 AND chr.id = p_chrId
1771 AND khr.pdt_id = pdt.id(+);
1772
1773 l_rl_rec1 l_rl_csr1%ROWTYPE;
1774 l_sll_rec l_sll_csr%ROWTYPE;
1775
1776 TYPE t_ast_rec IS TABLE OF l_sll_csr%ROWTYPE index by binary_integer;
1777 t_ast_rec_tbl t_ast_rec;
1778 t_sll_rec_tbl t_ast_rec;
1779
1780 l_fee_period_start_date DATE;
1781 l_fee_period_end_date DATE;
1782 l_arrears_pay_dates_option VARCHAR2(60);
1783 l_pdt_id_rec l_hdr_pdt_csr%ROWTYPE;
1784
1785 TYPE t_fee_name IS TABLE OF OKL_STRM_TYPE_TL.NAME%TYPE INDEX BY BINARY_INTEGER;
1786 TYPE t_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1787
1788 t_fee_name_tbl t_fee_name;
1789 t_pymt_st_date_tbl t_date;
1790 t_pymt_end_date_tbl t_date;
1791 t_fee_st_date_tbl t_date;
1792 t_fee_end_date_tbl t_date;
1793
1794 l_pymt_period_st_date DATE;
1795 l_pymt_period_end_date DATE;
1796
1797 l_counter NUMBER;
1798 l_found varchar2(1);
1799 l_found_fee okl_strm_type_tl.name%TYPE;
1800 l_calling_module varchar2(15);
1801 l_generate_streams varchar2(1);
1802 l_return_status varchar2(1);
1803 l_evergreen_eligible VARCHAR2(1);
1804 l_app_name CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
1805
1806 BEGIN
1807
1808 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1809
1810 if p_kle_id is null then
1811 l_calling_module := 'QA CHECKER';
1812 else
1813 l_calling_module := 'EVERGREEN';
1814 end if;
1815
1816 OPEN l_hdr_pdt_csr(p_chr_id);
1817 FETCH l_hdr_pdt_csr INTO l_pdt_id_rec;
1818 CLOSE l_hdr_pdt_csr;
1819
1820
1821 OPEN c_arrears_option_at_sgt_csr(l_pdt_id_rec.pid);
1822 FETCH c_arrears_option_at_sgt_csr INTO l_arrears_pay_dates_option;
1823 CLOSE c_arrears_option_at_sgt_csr;
1824
1825 -- check if the contract is eligible for evergreen. if eligible do further processing
1826 begin
1827
1828 select nvl(rule_information1, 'N')
1829 into l_evergreen_eligible
1830 from okc_rules_b
1831 where rule_information_category = 'LAEVEL'
1832 and dnz_chr_id = p_chr_id
1833 and not exists (select '1'
1834 from okl_k_headers
1835 where deal_type in ('LOAN', 'LOAN-REVOLVING')
1836 and id = p_chr_id);
1837 exception
1838 when no_data_found then
1839 l_evergreen_eligible := 'N';
1840 end;
1841
1842 if l_evergreen_eligible = 'Y' then -- if contract is eligible for evergreen {
1843
1844 --FREE_FORM1
1845 l_counter := 0;
1846
1847 FOR l_assets in c_assets_csr(p_chr_id)
1848 LOOP
1849 OPEN l_asset_rl_csr1('LALEVL', 'LASLH', to_number(p_chr_id), l_assets.id);
1850 FETCH l_asset_rl_csr1 into l_asset_rl_rec;
1851 IF l_asset_rl_csr1%FOUND then
1852 OPEN l_sll_csr(l_asset_rl_rec.slh_id, 'LALEVL', 'LASLL', p_chr_id, l_assets.id);
1853 FETCH l_sll_csr bulk collect into t_ast_rec_tbl;
1854 CLOSE l_sll_csr;
1855
1856 IF t_ast_rec_tbl.count > 0 THEN
1857 FOR i IN t_ast_rec_tbl.first..t_ast_rec_tbl.last loop
1858 l_counter := l_counter + 1;
1859 IF (t_ast_rec_tbl(i).stub_days IS NOT NULL) THEN
1860 t_pymt_st_date_tbl(l_counter) := t_ast_rec_tbl(i).start_date;
1861 t_pymt_end_date_tbl(l_counter) := TRUNC(t_ast_rec_tbl(i).start_date+ t_ast_rec_tbl(i).stub_days)-1;
1862 ELSE
1863 t_pymt_st_date_tbl(l_counter) := ADD_MONTHS(t_ast_rec_tbl(i).start_date, TO_NUMBER(t_ast_rec_tbl(i).frequency_factor) * (t_ast_rec_tbl(i).periods-1));
1864 t_pymt_end_date_tbl(l_counter) := ADD_MONTHS(t_ast_rec_tbl(i).start_date, TO_NUMBER(t_ast_rec_tbl(i).frequency_factor) * (t_ast_rec_tbl(i).periods))-1;
1865 END IF;
1866 END LOOP;
1867 END IF;
1868 END IF;
1869 CLOSE l_asset_rl_csr1;
1870 END LOOP;
1871
1872
1873 l_counter := 0;
1874 FOR l_lne IN c_fee_lines_csr(p_chr_id)
1875 LOOP
1876 OPEN l_rl_csr1 ( 'LALEVL', 'LASLH', TO_NUMBER(p_chr_id), l_lne.id);
1877 FETCH l_rl_csr1 INTO l_rl_rec1;
1878 IF l_rl_csr1%FOUND THEN
1879 OPEN l_sll_csr (l_rl_rec1.slh_id, 'LALEVL', 'LASLL', p_chr_id, l_lne.id);
1880 FETCH l_sll_csr BULK COLLECT INTO t_sll_rec_tbl;
1881 CLOSE l_sll_csr;
1882
1883 IF t_sll_rec_tbl.count > 0 then
1884 FOR i in t_sll_rec_tbl.first..t_sll_rec_tbl.last loop
1885 l_counter := l_counter + 1;
1886 t_fee_name_tbl(l_counter) := l_lne.name;
1887 IF(t_sll_rec_tbl(i).stub_days IS NOT NULL) THEN
1888 t_fee_st_date_tbl(l_counter) := t_sll_rec_tbl(i).start_date;
1889 t_fee_end_date_tbl(l_counter) := TRUNC(t_sll_rec_tbl(i).start_date+t_sll_rec_tbl(i).stub_days)-1;
1890 ELSE
1891 t_fee_st_date_tbl(l_counter) := ADD_MONTHS(t_sll_rec_tbl(i).start_date, TO_NUMBER(t_sll_rec_tbl(i).frequency_factor) * (t_sll_rec_tbl(i).periods-1));
1892 t_fee_end_date_tbl(l_counter) := ADD_MONTHS(t_sll_rec_tbl(i).start_date, TO_NUMBER(t_sll_rec_tbl(i).frequency_factor) * (t_sll_rec_tbl(i).periods))-1;
1893 END IF;
1894 END LOOP;
1895 END IF;
1896
1897 END IF;
1898 CLOSE l_rl_csr1;
1899 END LOOP;
1900
1901 x_gen_streams := 'N';
1902
1903 if t_fee_end_date_tbl.count > 0 and t_pymt_end_date_tbl.count > 0 then
1904 l_found := 'N';
1905 for i in t_fee_end_date_tbl.first..t_fee_end_date_tbl.last loop
1906 for j in t_pymt_end_date_tbl.first..t_pymt_end_date_tbl.last loop
1907 if ( t_fee_end_date_tbl(i) > t_pymt_end_date_tbl(j)
1908 OR
1909 ( t_fee_end_date_tbl(i) >= t_pymt_st_date_tbl(j) and
1910 t_fee_end_date_tbl(i) <= t_pymt_end_date_tbl(j) )
1911 ) then
1912 l_found := 'Y';
1913 l_found_fee := t_fee_name_tbl(i);
1914 if l_calling_module = 'QA CHECKER' then
1915 -- set error message here for QA checker
1916 OKL_API.set_message(
1917 p_app_name => L_APP_NAME,
1918 p_msg_name => 'OKL_QA_EVERGREEN_STRM_WRN',
1919 p_token1 => 'FEE_LINE_NAME',
1920 p_token1_value => l_found_fee);
1921 x_return_status := OKL_API.G_RET_STS_ERROR;
1922 /*else
1923 exit;*/ -- bug 13627069
1924 end if;
1925 exit; -- bug 13627069
1926 end if;
1927 end loop;
1928 end loop;
1929 end if;
1930
1931 if l_found = 'Y' then
1932 -- set the generate streams flag here
1933 x_gen_streams := 'Y';
1934 end if;
1935
1936 t_sll_rec_tbl.delete;
1937 t_fee_st_date_tbl.delete;
1938 t_fee_end_date_tbl.delete;
1939
1940 end if; --> if contract is eligible for evergreen}
1941
1942 --x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1943
1944 EXCEPTION
1945 WHEN OTHERS THEN
1946 -- store SQL error message on message stack
1947 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1948 p_api_name => l_api_name,
1949 p_pkg_name => G_PKG_NAME,
1950 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1951 x_msg_count => x_msg_count,
1952 x_msg_data => x_msg_data,
1953 p_api_type => '_PVT');
1954
1955 -- Bug# 11787205: end
1956 END CHECK_FEE_EVERGREEN_BILL;
1957
1958 END Okl_Evergreen_Billing_Pvt;