[Home] [Help]
PACKAGE BODY: APPS.OKL_EVERGREEN_BILLING_PVT
Source
1 Package BODY Okl_Evergreen_Billing_Pvt AS
2 /* $Header: OKLREGBB.pls 120.17 2007/12/07 10:03:24 varangan noship $ */
3
4 ------------------------------------------------------------------
5 -- Procedure BIL_EVERGREEN_STREAMS to bill for Evergreen Streams
6 ------------------------------------------------------------------
7
8 PROCEDURE BILL_EVERGREEN_STREAMS
9 (p_api_version IN NUMBER
10 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
11 ,x_return_status OUT NOCOPY VARCHAR2
12 ,x_msg_count OUT NOCOPY NUMBER
13 ,x_msg_data OUT NOCOPY VARCHAR2
14 ,p_contract_number IN VARCHAR2 DEFAULT NULL
15 ,p_from_bill_date IN DATE DEFAULT NULL
16 ,p_to_bill_date IN DATE DEFAULT NULL) IS
17
18 ------------------------------------------------------------
19 -- Pick Evergreen Contract Id's
20 ------------------------------------------------------------
21 CURSOR evergreen_contracts_csr ( p_contract_number VARCHAR2 ) IS
22 SELECT oklh.id khr_id,
23 okch.contract_number,
24 nvl(stm.kle_id, -99) kle_id
25 FROM okl_k_headers oklh,
26 okc_k_headers_b okch,
27 okc_statuses_b khs,
28 okl_streams stm
29 WHERE oklh.id = okch.id
30 AND okch.contract_number = NVL (p_contract_number, okch.contract_number)
31 AND okch.scs_code IN ('LEASE', 'LOAN')
32 AND okch.sts_code = 'EVERGREEN'
33 AND khs.code = okch.sts_code
34 AND oklh.id = stm.khr_id
35 AND EXISTS (SELECT 1 FROM okl_strm_type_v sty
36 WHERE stm.sty_id = sty.id
37 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
38 --AND sty.name IN ('RENT', 'SERVICE AND MAINTENANCE', 'ESTIMATED PERSONAL PROPERTY TAX'))
39 AND sty.stream_type_purpose IN ('RENT', 'SERVICE_PAYMENT', 'ESTIMATED_PROPERTY_TAX', 'FEE_PAYMENT'))
40 AND (stm.kle_id is not null and EXISTS (SELECT 1 FROM OKC_K_LINES_B CLE
41 WHERE cle.dnz_chr_id = oklh.id
42 AND cle.id = stm.kle_id
43 AND cle.sts_code = 'EVERGREEN') OR stm.kle_id IS NULL)
44 GROUP BY oklh.id,
45 okch.contract_number,
46 nvl(stm.kle_id, -99);
47
48 ------------------------------------------------------------
49 -- Extract all streams to be billed
50 ------------------------------------------------------------
51 CURSOR c_stm_id ( p_khr_id NUMBER, p_kle_id NUMBER) IS
52 SELECT khr.contract_number contract_number,
53 stm.kle_id, --added by pgomes
54 stm.id stm_id,
55 sty.stream_type_purpose sty_name
56 FROM okl_k_headers_full_v khr,
57 okl_streams stm,
58 okl_strm_type_v sty
59 WHERE khr.id = p_khr_id
60 AND stm.khr_id = khr.id
61 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
62 AND stm.sty_id = sty.id
63 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
64 --AND sty.name IN ('SERVICE AND MAINTENANCE EVERGREEN', 'EVERGREEN RENT', 'ESTIMATED PERSONAL PROPERTY TAX EVERGREEN')
65 AND sty.stream_type_purpose IN ('SERVICE_RENEWAL', 'RENEWAL_RENT', 'RENEWAL_PROPERTY_TAX', 'FEE_RENEWAL')
66 AND stm.say_code = 'CURR'
67 AND stm.active_yn = 'Y';
68
69 ------------------------------------------------------------
70 -- Get Count of all streams
71 ------------------------------------------------------------
72 CURSOR c_stm_count_csr ( p_khr_id NUMBER, p_kle_id NUMBER) IS
73 SELECT count(*)
74 FROM okl_streams stm,
75 okl_strm_type_v sty
76 WHERE stm.khr_id = p_khr_id
77 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
78 AND stm.sty_id = sty.id
79 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
80 --AND sty.name IN ('SERVICE AND MAINTENANCE EVERGREEN', 'EVERGREEN RENT', 'ESTIMATED PERSONAL PROPERTY TAX EVERGREEN')
81 AND sty.stream_type_purpose IN ('SERVICE_RENEWAL', 'RENEWAL_RENT', 'RENEWAL_PROPERTY_TAX', 'FEE_RENEWAL')
82 AND stm.say_code = 'CURR'
83 AND stm.active_yn = 'Y';
84
85 ------------------------------------------------------------
86 -- Get Count of specific stream type
87 ------------------------------------------------------------
88 CURSOR c_sty_count_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2 ) IS
89 SELECT count(*)
90 FROM okl_streams stm,
91 okl_strm_type_v sty
92 WHERE stm.khr_id = p_khr_id
93 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
94 AND stm.sty_id = sty.id
95 AND stm.say_code = 'CURR'
96 AND stm.active_yn = 'Y'
97 AND sty.stream_type_purpose = p_sty_purpose;
98
99 ------------------------------------------------------------
100 -- Get Copy parameters
101 ------------------------------------------------------------
102 /*CURSOR c_copy_params_csr ( p_khr_id NUMBER) IS
103 SELECT stm.kle_id, stm.say_code, stm.active_yn
104 FROM okl_streams stm,
105 okl_strm_type_v sty
106 WHERE stm.khr_id = p_khr_id
107 AND stm.sty_id = sty.id
108 AND stm.say_code = 'CURR'
109 AND stm.active_yn = 'Y'
110 AND sty.name IN ('RENT');*/
111
112 ------------------------------------------------------------
113 -- Get Sty Id
114 ------------------------------------------------------------
115 /*CURSOR get_sty_id_csr ( p_sty_purpose VARCHAR2 ) IS
116 SELECT id
117 FROM okl_strm_type_v
118 WHERE stream_type_purpose = p_sty_purpose; */
119
120 ------------------------------------------------------------
121 -- Get Sty Id for a line and purpose
122 ------------------------------------------------------------
123 CURSOR get_prim_sty_id_csr( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2 ) IS
124 SELECT stm.sty_id
125 FROM okl_streams stm,
126 okl_strm_type_v sty
127 WHERE stm.khr_id = p_khr_id
128 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
129 AND stm.sty_id = sty.id
130 AND stm.say_code = 'CURR'
131 AND stm.active_yn = 'Y'
132 AND sty.stream_type_purpose = p_sty_purpose;
133
134 ------------------------------------------------------------
135 -- Get Stm attributes
136 ------------------------------------------------------------
137 /*CURSOR get_stm_attrs_csr ( p_khr_id NUMBER, p_sty_name VARCHAR2 ) IS
138 SELECT stm.kle_id
139 FROM okl_streams stm,
140 okl_strm_type_v sty
141 WHERE stm.khr_id = p_khr_id
142 AND stm.sty_id = sty.id
143 AND stm.say_code = 'CURR'
144 AND stm.active_yn = 'Y'
145 AND sty.name = p_sty_name; */
146
147 ------------------------------------------------------------
148 -- Upper Bound for Rental/S And M Date
149 ------------------------------------------------------------
150 CURSOR upper_rental_date_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2) IS
151 SELECT TRUNC(MAX( ste.STREAM_ELEMENT_DATE )) upper_stream_date
152 FROM okl_strm_elements ste
153 WHERE ste.stm_id IN (
154 SELECT stm.id
155 FROM okl_streams stm,
156 okl_strm_type_v sty
157 WHERE stm.khr_id = p_khr_id
158 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
159 AND stm.sty_id = sty.id
160 AND stm.say_code = 'CURR'
161 AND stm.active_yn = 'Y'
162 AND sty.stream_type_purpose = p_sty_purpose);
163
164 ------------------------------------------------------------
165 -- Lower Bound for Rental/S And M Date
166 ------------------------------------------------------------
167 CURSOR lower_rental_date_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2, p_max_date DATE) IS
168 SELECT TRUNC(MAX( ste.STREAM_ELEMENT_DATE )) lower_stream_date
169 FROM okl_strm_elements ste
170 WHERE ste.stream_element_date <= p_max_date
171 AND ste.stm_id IN (
172 SELECT stm.id
173 FROM okl_streams stm,
174 okl_strm_type_v sty
175 WHERE stm.khr_id = p_khr_id
176 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
177 AND stm.sty_id = sty.id
178 AND stm.say_code = 'CURR'
179 AND stm.active_yn = 'Y'
180 AND sty.stream_type_purpose = p_sty_purpose);
181
182 ------------------------------------------------------------
183 -- Billing Amount
184 ------------------------------------------------------------
185 CURSOR bill_amt_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2) IS
186 /*bug#6060813 27-Sep-2007 bill_amt_csr changed to pick the last billed stream for each
187 contract line and not the least amount billed during the life of the
188 contract line. Ordered the stream element dates in descending order and picked the
189 the amount for the max stream element date excluding the stream element for stub
190 payment */
191 SELECT ste.amount
192 FROM okl_strm_elements ste,
193 (
194 SELECT stm.id, to_number(rule_information6) amt
195 FROM okc_rules_b a,
196 okc_rule_groups_b b,
197 okl_streams stm,
198 okl_strm_type_v sty
199 WHERE a.dnz_chr_id = p_khr_id
200 AND a.rgp_id = b.id
201 AND b.rgd_code = 'LALEVL'
202 AND a.rule_information_category = 'LASLL'
203 AND stm.kle_id = b.cle_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 AND rule_information6 IS NOT NULL
210 ) strules
211 WHERE ste.stm_id = strules.id
212 AND ste.amount = strules.amt
213 AND ste.date_billed IS NOT NULL
214 ORDER BY ste.stream_element_date DESC;
215
216 /*
217 CURSOR bill_amt_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2) IS
218 SELECT MIN (ste.amount) amount
219 FROM okl_strm_elements ste
220 WHERE ste.stm_id IN (
221 SELECT stm.id
222 FROM okl_streams stm,
223 okl_strm_type_v sty
224 WHERE stm.khr_id = p_khr_id
225 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
226 AND stm.sty_id = sty.id
227 AND stm.say_code = 'CURR'
228 AND stm.active_yn = 'Y'
229 AND sty.stream_type_purpose = p_sty_purpose);
230
231 */
232 ------------------------------------------------------------
233 -- Check Evergreen elements exist
234 ------------------------------------------------------------
235 CURSOR evergreen_element_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2) IS
236 SELECT MAX( ste.STREAM_ELEMENT_DATE ) evergreen_element_date
237 FROM okl_strm_elements ste
238 WHERE ste.stm_id IN (
239 SELECT stm.id
240 FROM okl_streams stm,
241 okl_strm_type_v sty
242 WHERE stm.khr_id = p_khr_id
243 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
244 AND stm.sty_id = sty.id
245 AND stm.say_code = 'CURR'
246 AND stm.active_yn = 'Y'
247 AND sty.stream_type_purpose = p_sty_purpose);
248
249 ------------------------------------------------------------
250 -- Transaction Number Cursor
251 ------------------------------------------------------------
252 CURSOR c_tran_num_csr IS
253 SELECT okl_sif_seq.nextval
254 FROM dual;
255
256 ------------------------------------------------------------
257 -- Billing Frequency Cursor
258 ------------------------------------------------------------
259 --changed for rules migration
260 CURSOR c_bill_freq_csr( p_khr_id NUMBER ) IS
261 SELECT object1_id1
262 FROM OKC_RULES_B rul,
263 Okc_rule_groups_B rgp
264 WHERE rul.rgp_id = rgp.id AND
265 rgp.rgd_code = 'LALEVL' AND
266 rgp.chr_id IS NULL AND
267 rul.rule_information_category = 'LASLL' AND
268 rgp.dnz_chr_id = p_khr_id;
269
270 -----------------------------------------------------------
271 -- Max Line Number
272 ------------------------------------------------------------
273 CURSOR max_line_num_csr (p_stm_id NUMBER) IS
274 SELECT max(se_line_number)
275 FROM okl_strm_elements
276 WHERE stm_id = p_stm_id;
277
278 ------------------------------------------------------------
279 -- To Check if a stream element already exists
280 ------------------------------------------------------------
281 CURSOR stm_rec_exists_csr (p_stm_id NUMBER, p_sel_date DATE) IS
282 SELECT count(*)
283 FROM okl_strm_elements
284 WHERE stm_id = p_stm_id
285 AND trunc(STREAM_ELEMENT_DATE) = trunc(p_sel_date);
286
287 ------------------------------------------------------------
288 -- To Check if previously unbilled stream elements exist
289 ------------------------------------------------------------
290 CURSOR prev_unbilled_csr (p_stm_id NUMBER, p_sel_date DATE) IS
291 SELECT count(*)
292 FROM okl_strm_elements
293 WHERE stm_id = p_stm_id
294 AND trunc(STREAM_ELEMENT_DATE) <= trunc(p_sel_date)
295 AND date_billed is NULL;
296
297 ----------------------------------------------------------------------------------------------------
298 -- To get last stream element for RENT or SERVICE AND MAINTENANCE or ESTIMATED PERSONAL PROPERTY TAX
299 -- or FEE
300 ----------------------------------------------------------------------------------------------------
301 CURSOR c_last_strm_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_purpose VARCHAR2) IS
302 SELECT * FROM (
303 SELECT ste.id
304 ,ste.stream_element_date
305 ,stm.khr_id
306 ,stm.kle_id
307 ,stm.sty_id
308 FROM okl_strm_elements_v ste
309 ,okl_streams_v stm
310 ,okl_strm_type_v sty
311 WHERE ste.stm_id = stm.id
312 AND stm.khr_id = p_khr_id
313 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
314 AND stm.sty_id = sty.id
315 AND sty.stream_type_purpose = p_sty_purpose
316 ORDER BY ste.stream_element_date DESC
317 )
318 WHERE ROWNUM = 1;
319
320 ------------------------------------------------------------
321 -- Find out whether the stream was securitized
322 ------------------------------------------------------------
323 CURSOR c_sec_strm_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_id NUMBER, p_stream_element_date DATE) IS
324 select distinct khr.id khr_id
325 from okl_pool_contents_v pol
326 , OKL_POOLS pool
327 ,okl_k_headers_full_v khr
328 where pol.khr_id = p_khr_id
329 and nvl(pol.kle_id, -99) = nvl(p_kle_id, -99)
330 and pol.sty_id = p_sty_id
331 and trunc(p_stream_element_date) between trunc(pol.streams_from_date) and trunc(pol.streams_to_date)
332 and pol.pol_id = pool.id
333 and pool.khr_id = khr.id
334 AND pol.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE; --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
335
336 ------------------------------------------------------------
337 -- Stream Cursor
338 ------------------------------------------------------------
339 CURSOR l_stream_csr(cp_khr_id IN NUMBER
340 ,cp_kle_id IN NUMBER
341 ,cp_sty_id IN NUMBER) IS
342 SELECT stm.id
343 FROM okl_streams_v stm
344 WHERE stm.khr_id = cp_khr_id
345 AND nvl(stm.kle_id, -99) = nvl(cp_kle_id, -99)
346 AND stm.sty_id = cp_sty_id
347 AND stm.say_code = 'CURR'
348 AND stm.active_yn = 'Y';
349
350 ------------------------------------------------------------
351 -- Stream Element Line Number Cursor
352 ------------------------------------------------------------
353 CURSOR l_stream_line_nbr_csr(cp_stm_id IN NUMBER) IS
354 SELECT max(se_line_number) se_line_number
355 FROM okl_strm_elements_v
356 WHERE stm_id = cp_stm_id;
357
358 ------------------------------------------------------------
359 -- Initialise constants
360 ------------------------------------------------------------
361
362 l_def_desc CONSTANT VARCHAR2(30) := 'Regular Stream Billing';
363 l_line_code CONSTANT VARCHAR2(30) := 'LINE';
364 l_init_status CONSTANT VARCHAR2(30) := 'ENTERED';
365 l_final_status CONSTANT VARCHAR2(30) := 'SUBMITTED';
366 l_trx_type_name CONSTANT VARCHAR2(30) := 'Billing';
367 l_trx_type_lang CONSTANT VARCHAR2(30) := 'US';
368 l_date_entered CONSTANT DATE := SYSDATE;
369 l_zero_amount CONSTANT NUMBER := 0;
370 l_first_line CONSTANT NUMBER := 1;
371 l_line_step CONSTANT NUMBER := 1;
372 l_def_no_val CONSTANT NUMBER := -1;
373 l_null_kle_id CONSTANT NUMBER := -2;
374
375 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
376 cns_inv_evrgrn_rent_pay constant varchar2(50) := 'INVESTOR_EVERGREEN_RENT_PAY';
377 --not used since Service and Maintenance is not disbursed to investors
378 cns_inv_sm_pay constant varchar2(50) := 'INVESTOR SERVICE AND MAINTENANCE PAY';
379
380 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
381 cns_evergreen_rent constant varchar2(50) := 'RENEWAL_RENT';
382 cns_rent constant varchar2(50) := 'RENT';
383
384 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
385 cns_sm_evergreen constant varchar2(50) := 'SERVICE_RENEWAL';
386 cns_sm constant varchar2(50) := 'SERVICE_PAYMENT';
387
388 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
389 cns_ept_evergreen constant varchar2(50) := 'RENEWAL_PROPERTY_TAX';
390 cns_ept constant varchar2(50) := 'ESTIMATED_PROPERTY_TAX';
391
392 --change for User Defined Streams, by pjgomes, on 22 Feb 2005
393 cns_fee_evergreen constant varchar2(50) := 'FEE_RENEWAL';
394 cns_fee constant varchar2(50) := 'FEE_PAYMENT';
395
396 -- Stream elements
397 p_selv_rec Okl_Streams_Pub.selv_rec_type;
398 x_selv_rec Okl_Streams_Pub.selv_rec_type;
399
400 l_selv_rec Okl_Sel_Pvt.selv_rec_type;
401 lx_selv_rec Okl_Sel_Pvt.selv_rec_type;
402 l_init_selv_rec Okl_Sel_Pvt.selv_rec_type;
403
404 ------------------------------------------------------------
405 -- Declare local variables used in the program
406 ------------------------------------------------------------
407
408 l_khr_id okl_k_headers.id%TYPE;
409 l_kle_id okl_streams.kle_id%TYPE;
410 l_sty_id okl_strm_type_v.id%TYPE;
411 l_evrgrn_strm_purpose okl_strm_type_v.stream_type_purpose%TYPE;
412 l_evrgrn_prim_strm_purpose okl_strm_type_v.stream_type_purpose%TYPE;
413 l_se_line_number OKL_STRM_ELEMENTS_V.SE_LINE_NUMBER%TYPE;
414 l_stm_id OKL_STREAMS_V.ID%TYPE;
415 l_sel_id Okl_strm_elements_v.sel_id%TYPE;
416
417 l_amount NUMBER;
418
419 l_billing_frequency NUMBER;
420
421 l_Stream_bill_date DATE;
422 l_last_Stream_bill_date DATE;
423
424 l_upper_date DATE;
425 l_lower_date DATE;
426
427 l_evergreen_date DATE;
428
429 create_flag VARCHAR2(1);
430 create_payable_flag VARCHAR2(1) := 'N';
431
432 ------------------------------------------------------------
433 -- Declare variables required by APIs
434 ------------------------------------------------------------
435
436 l_api_version CONSTANT NUMBER := 1;
437 l_api_name CONSTANT VARCHAR2(30) := 'BILL_EVERGREEN_STREAMS';
438 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
439
440 -- Streams Record
441 l_stmv_rec Okl_Streams_Pub.stmv_rec_type;
442 lx_stmv_rec Okl_Streams_Pub.stmv_rec_type;
443 l_init_stmv_rec Okl_Streams_Pub.stmv_rec_type;
444
445 -- Temporary variables
446 l_evergreen_rent_count NUMBER;
447 l_contract_rent_count NUMBER;
448 l_evergreen_sm_count NUMBER;
449 l_contract_sm_count NUMBER;
450 l_evergreen_ept_count NUMBER;
451 l_contract_ept_count NUMBER;
452 l_evergreen_fee_count NUMBER;
453 l_contract_fee_count NUMBER;
454
455 l_count NUMBER;
456 l_max_line_num NUMBER;
457 l_rec_exists_cnt NUMBER;
458 l_prev_unbilled_cnt NUMBER;
459 l_investor_agrmt_id NUMBER;
460 l_bill_freq OKC_RULES_B.object1_id1%TYPE;
461 l_primary_sty_id NUMBER;
462 l_primary_for_dep_sty_id NUMBER;
463 l_prev_khr_id okl_k_headers.id%TYPE; --dkagrawa added for bug# 4728636
464
465 -----------------------------------------------------
466 -- Error Processing Variables
467 -----------------------------------------------------
468 l_error_message VARCHAR2(1000);
469 l_error_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
470
471 -- fmiao for bug 4961860
472 -- Cursor to check if the residual value exists in the pool or not
473 CURSOR check_res_in_pool(p_khr_id NUMBER) IS
474 SELECT 'Y'
475 FROM dual
476 WHERE EXISTS(
477 SELECT 1
478 FROM OKL_POOLS pool,
479 okl_pool_contents_v poc,
480 okl_strm_type_v sty
481 WHERE pool.khr_id = p_khr_id AND
482 pool.id = poc.pol_id AND
483 poc.sty_id = sty.id AND
484 sty.stream_type_purpose = 'RESIDUAL_VALUE'
485 AND poc.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE ); --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
486
487 l_res_in_pool VARCHAR2(1);
488 l_evrgrn_psthrgh_flg NUMBER := 0;
489 -- end fmiao for bug 4961860
490
491
492 BEGIN
493
494 ------------------------------------------------------------
495 -- Start processing
496 ------------------------------------------------------------
497
498 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
499
500 l_return_status := Okl_Api.START_ACTIVITY(
501 p_api_name => l_api_name,
502 p_pkg_name => G_PKG_NAME,
503 p_init_msg_list => p_init_msg_list,
504 l_api_version => l_api_version,
505 p_api_version => p_api_version,
506 p_api_type => '_PVT',
507 x_return_status => l_return_status);
508
509 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
510 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
511 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
512 RAISE Okl_Api.G_EXCEPTION_ERROR;
513 END IF;
514
515 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '=========================================================================================');
516 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ******* Start Evergreen Processing *******');
517 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '=========================================================================================');
518
519
520 FOR evergreen_contracts IN evergreen_contracts_csr ( p_contract_number ) LOOP
521 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-----------------------------------------------------------------------------------------');
522 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Processing Contract: '||evergreen_contracts.contract_number);
523 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-----------------------------------------------------------------------------------------');
524
525 l_khr_id := NULL;
526 l_kle_id := NULL;
527 l_khr_id := evergreen_contracts.khr_id;
528 l_kle_id := evergreen_contracts.kle_id;
529
530 ------------------------------------------------
531 -- Check if this contract has evergreen streams
532 ------------------------------------------------
533 l_count := 0;
534 OPEN c_stm_count_csr ( l_khr_id, l_kle_id );
535 FETCH c_stm_count_csr INTO l_count;
536 CLOSE c_stm_count_csr;
537
538 --check to see if evergreen rent, evergreen s and m, evergreen ept, evergreen fee exist for khr, kle
539 IF l_count < 4 THEN
540
541 -- Check and insert Evergreen Rent record
542 l_evergreen_rent_count := 0;
543 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
544 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_evergreen_rent );
545 FETCH c_sty_count_csr INTO l_evergreen_rent_count;
546 CLOSE c_sty_count_csr;
547
548 IF l_evergreen_rent_count > 0 THEN
549 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ' || cns_evergreen_rent || ' Streams exist for this contract.');
550 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Proceeding to creation of stream elements.');
551 ELSE
552 l_contract_rent_count := 0;
553
554 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
555 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_rent );
556 FETCH c_sty_count_csr INTO l_contract_rent_count;
557 CLOSE c_sty_count_csr;
558
559 IF l_contract_rent_count > 0 THEN
560 -- Null out records
561 l_stmv_rec := l_init_stmv_rec;
562 lx_stmv_rec := l_init_stmv_rec;
563 l_sty_id := NULL;
564 l_primary_sty_id := NULL;
565 --l_kle_id := NULL;
566
567 ----------------------------------
568 -- Evergreen Billing
569 ----------------------------------
570 /*OPEN get_sty_id_csr ( cns_evergreen_rent );
571 FETCH get_sty_id_csr INTO l_sty_id;
572 CLOSE get_sty_id_csr;*/
573
574 ------------------------------------------------------
575 --Get the sty id for RENT
576 --This sty id will be used as the primary sty id for
577 --obtaining the sty id for EVERGREEN RENT
578 ------------------------------------------------------
579 OPEN get_prim_sty_id_csr(l_khr_id, l_kle_id, cns_rent);
580 FETCH get_prim_sty_id_csr INTO l_primary_sty_id;
581 CLOSE get_prim_sty_id_csr;
582 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Sty id for RENT: ' || l_primary_sty_id);
583
584 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
585 OKL_STREAMS_UTIL.get_dependent_stream_type(p_khr_id => l_khr_id
586 ,p_primary_sty_id => l_primary_sty_id
587 ,p_dependent_sty_purpose => cns_evergreen_rent
588 ,x_return_status => l_return_status
589 ,x_dependent_sty_id => l_sty_id);
590
591 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
592 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_evergreen_rent);
593 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
594 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
595 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_evergreen_rent);
596 RAISE Okl_Api.G_EXCEPTION_ERROR;
597 END IF;
598 --OPEN get_stm_attrs_csr ( l_khr_id , 'RENT' );
599 --LOOP
600 -- FETCH get_stm_attrs_csr INTO l_kle_id;
601
602 /*IF get_stm_attrs_csr%NOTFOUND THEN
603 CLOSE get_stm_attrs_csr;
604 EXIT;
605 END IF;*/
606
607 OPEN c_tran_num_csr;
608 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
609 CLOSE c_tran_num_csr;
610
611
612 l_stmv_rec.sty_id := l_sty_id;
613 l_stmv_rec.khr_id := l_khr_id;
614 IF (l_kle_id <> -99) THEN
615 l_stmv_rec.kle_id := l_kle_id;
616 ELSE
617 l_stmv_rec.kle_id := null;
618 END IF;
619
620 l_stmv_rec.sgn_code := 'MANL';
621 l_stmv_rec.say_code := 'CURR';
622 l_stmv_rec.active_yn := 'Y';
623 l_stmv_rec.date_current := sysdate;
624 l_stmv_rec.comments := 'Evergreen Billing';
625
626 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating EVERGREEN RENT Streams');
627
628 Okl_Streams_Pub.create_streams(
629 p_api_version => p_api_version,
630 p_init_msg_list => p_init_msg_list,
631 x_return_status => x_return_status,
632 x_msg_count => x_msg_count,
633 x_msg_data => x_msg_data,
634 p_stmv_rec => l_stmv_rec,
635 x_stmv_rec => lx_stmv_rec);
636
637 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
638 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for EVERGREEN RENT');
639 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
640 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
641 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for EVERGREEN RENT');
642 RAISE Okl_Api.G_EXCEPTION_ERROR;
643 END IF;
644 /*END LOOP;
645 CLOSE get_stm_attrs_csr;*/
646 ELSE
647 NULL;
648 END IF;
649 END IF;
650
651 -- Check and insert Evergreen Service and Maintenance record
652 l_evergreen_sm_count := 0;
653 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
654 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_sm_evergreen);
655 FETCH c_sty_count_csr INTO l_evergreen_sm_count;
656 CLOSE c_sty_count_csr;
657
658 IF l_evergreen_sm_count > 0 THEN
659 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ' || cns_sm_evergreen || ' Streams exist for this contract.');
660 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Proceeding to creation of stream elements.');
661 ELSE
662 l_contract_sm_count := 0;
663
664 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
665 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_sm );
666 FETCH c_sty_count_csr INTO l_contract_sm_count;
667 CLOSE c_sty_count_csr;
668
669 IF l_contract_sm_count > 0 THEN
670
671 -- Null out records
672 l_stmv_rec := l_init_stmv_rec;
673 lx_stmv_rec := l_init_stmv_rec;
674 l_sty_id := NULL;
675 l_primary_sty_id := NULL;
676 --l_kle_id := NULL;
677
678 ----------------------------------
679 -- Evergreen Billing
680 ----------------------------------
681 /*OPEN get_sty_id_csr ( cns_sm_evergreen );
682 FETCH get_sty_id_csr INTO l_sty_id;
683 CLOSE get_sty_id_csr;*/
684
685 ------------------------------------------------------
686 --Get the sty id for SERVICE AND MAINTENANCE
687 --This sty id will be used as the primary sty id for
688 --obtaining the sty id for EVERGREEN SERVICE AND MAINTENANCE
689 ------------------------------------------------------
690 OPEN get_prim_sty_id_csr(l_khr_id, l_kle_id, cns_sm);
691 FETCH get_prim_sty_id_csr INTO l_primary_sty_id;
692 CLOSE get_prim_sty_id_csr;
693 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Sty id for SERVICE AND MAINTENANCE: ' || l_primary_sty_id);
694
695 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
696 OKL_STREAMS_UTIL.get_dependent_stream_type(p_khr_id => l_khr_id
697 ,p_primary_sty_id => l_primary_sty_id
698 ,p_dependent_sty_purpose => cns_sm_evergreen
699 ,x_return_status => l_return_status
700 ,x_dependent_sty_id => l_sty_id);
701
702 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
703 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_sm_evergreen);
704 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
705 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
706 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_sm_evergreen);
707 RAISE Okl_Api.G_EXCEPTION_ERROR;
708 END IF;
709
710 /*OPEN get_stm_attrs_csr ( l_khr_id , 'SERVICE AND MAINTENANCE' );
711 LOOP
712 FETCH get_stm_attrs_csr INTO l_kle_id;
713
714 IF get_stm_attrs_csr%NOTFOUND THEN
715 CLOSE get_stm_attrs_csr;
716 EXIT;
717 END IF;*/
718
719 OPEN c_tran_num_csr;
720 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
721 CLOSE c_tran_num_csr;
722
723
724 l_stmv_rec.sty_id := l_sty_id;
725 l_stmv_rec.khr_id := l_khr_id;
726 IF (l_kle_id <> -99) THEN
727 l_stmv_rec.kle_id := l_kle_id;
728 ELSE
729 l_stmv_rec.kle_id := null;
730 END IF;
731 l_stmv_rec.sgn_code := 'MANL';
732 l_stmv_rec.say_code := 'CURR';
733 l_stmv_rec.active_yn := 'Y';
734 l_stmv_rec.date_current := sysdate;
735 l_stmv_rec.comments := 'Evergreen Billing';
736
737
738 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating SERVICE AND MAINTENANCE EVERGREEN Streams');
739 Okl_Streams_Pub.create_streams(
740 p_api_version => p_api_version,
741 p_init_msg_list => p_init_msg_list,
742 x_return_status => x_return_status,
743 x_msg_count => x_msg_count,
744 x_msg_data => x_msg_data,
745 p_stmv_rec => l_stmv_rec,
746 x_stmv_rec => lx_stmv_rec);
747
748 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
749 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for SERVICE AND MAINTENANCE EVERGREEN');
750 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
751 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
752 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for SERVICE AND MAINTENANCE EVERGREEN');
753 RAISE Okl_Api.G_EXCEPTION_ERROR;
754 END IF;
755 /*END LOOP;
756 CLOSE c_tran_num_csr;*/
757 ELSE
758 null;
759 END IF;
760 END IF;
761
762 -- Check and insert Estimated Personal Property Tax Evergreen record
763 l_evergreen_ept_count := 0;
764 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
765 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_ept_evergreen );
766 FETCH c_sty_count_csr INTO l_evergreen_ept_count;
767 CLOSE c_sty_count_csr;
768
769 IF l_evergreen_ept_count > 0 THEN
770 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ' || cns_ept_evergreen || ' Streams exist for this contract.');
771 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Proceeding to creation of stream elements.');
772 ELSE
773 l_contract_ept_count := 0;
774
775 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
776 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_ept );
777 FETCH c_sty_count_csr INTO l_contract_ept_count;
778 CLOSE c_sty_count_csr;
779
780 IF l_contract_ept_count > 0 THEN
781 -- Null out records
782 l_stmv_rec := l_init_stmv_rec;
783 lx_stmv_rec := l_init_stmv_rec;
784 l_sty_id := NULL;
785 l_primary_sty_id := NULL;
786 --l_kle_id := NULL;
787
788 ----------------------------------
789 -- Evergreen Billing
790 ----------------------------------
791 /*OPEN get_sty_id_csr ( cns_ept_evergreen );
792 FETCH get_sty_id_csr INTO l_sty_id;
793 CLOSE get_sty_id_csr;*/
794
795 ------------------------------------------------------
796 --Get the sty id for ESTIMATED PROPERTY TAX
797 --This sty id will be used as the primary sty id for
798 --obtaining the sty id for EVERGREEN ESTIMATED PROPERTY TAX
799 ------------------------------------------------------
800 OPEN get_prim_sty_id_csr(l_khr_id, l_kle_id, cns_ept);
801 FETCH get_prim_sty_id_csr INTO l_primary_sty_id;
802 CLOSE get_prim_sty_id_csr;
803 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Sty id for ESTIMATED PROPERTY TAX: ' || l_primary_sty_id);
804
805
806 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
807 OKL_STREAMS_UTIL.get_dependent_stream_type(p_khr_id => l_khr_id
808 ,p_primary_sty_id => l_primary_sty_id
809 ,p_dependent_sty_purpose => cns_ept_evergreen
810 ,x_return_status => l_return_status
811 ,x_dependent_sty_id => l_sty_id);
812
813 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
814 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_ept_evergreen);
815 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
816 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
817 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_ept_evergreen);
818 RAISE Okl_Api.G_EXCEPTION_ERROR;
819 END IF;
820
821 OPEN c_tran_num_csr;
822 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
823 CLOSE c_tran_num_csr;
824
825
826 l_stmv_rec.sty_id := l_sty_id;
827 l_stmv_rec.khr_id := l_khr_id;
828 IF (l_kle_id <> -99) THEN
829 l_stmv_rec.kle_id := l_kle_id;
830 ELSE
831 l_stmv_rec.kle_id := null;
832 END IF;
833
834 l_stmv_rec.sgn_code := 'MANL';
835 l_stmv_rec.say_code := 'CURR';
836 l_stmv_rec.active_yn := 'Y';
837 l_stmv_rec.date_current := sysdate;
838 l_stmv_rec.comments := 'Evergreen Billing';
839
840 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating ESTIMATED PERSONAL PROPERTY TAX EVERGREEN Streams');
841
842 Okl_Streams_Pub.create_streams(
843 p_api_version => p_api_version,
844 p_init_msg_list => p_init_msg_list,
845 x_return_status => x_return_status,
846 x_msg_count => x_msg_count,
847 x_msg_data => x_msg_data,
848 p_stmv_rec => l_stmv_rec,
849 x_stmv_rec => lx_stmv_rec);
850
851 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
852 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for ESTIMATED PERSONAL PROPERTY TAX EVERGREEN');
853 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
854 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
855 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for ESTIMATED PERSONAL PROPERTY TAX EVERGREEN');
856 RAISE Okl_Api.G_EXCEPTION_ERROR;
857 END IF;
858 /*END LOOP;
859 CLOSE get_stm_attrs_csr;*/
860 ELSE
861 NULL;
862 END IF;
863 END IF;
864
865 -- Check and insert Evergreen Fee record
866 l_evergreen_fee_count := 0;
867
868 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_fee_evergreen);
869 FETCH c_sty_count_csr INTO l_evergreen_fee_count;
870 CLOSE c_sty_count_csr;
871
872 IF l_evergreen_fee_count > 0 THEN
873 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ' || cns_fee_evergreen || ' Streams exist for this contract.');
874 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Proceeding to creation of stream elements.');
875 ELSE
876 l_contract_fee_count := 0;
877
878 OPEN c_sty_count_csr ( l_khr_id, l_kle_id, cns_fee );
879 FETCH c_sty_count_csr INTO l_contract_fee_count;
880 CLOSE c_sty_count_csr;
881
882 IF l_contract_fee_count > 0 THEN
883
884 -- Null out records
885 l_stmv_rec := l_init_stmv_rec;
886 lx_stmv_rec := l_init_stmv_rec;
887 l_sty_id := NULL;
888 l_primary_sty_id := NULL;
889 --l_kle_id := NULL;
890
891 ----------------------------------
892 -- Evergreen Billing
893 ----------------------------------
894
895 ------------------------------------------------------
896 --Get the sty id for FEE
897 --This sty id will be used as the primary sty id for
898 --obtaining the sty id for EVERGREEN FEE
899 ------------------------------------------------------
900 OPEN get_prim_sty_id_csr(l_khr_id, l_kle_id, cns_fee);
901 FETCH get_prim_sty_id_csr INTO l_primary_sty_id;
902 CLOSE get_prim_sty_id_csr;
903 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Sty id for FEE: ' || l_primary_sty_id);
904
905 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
906 OKL_STREAMS_UTIL.get_dependent_stream_type(p_khr_id => l_khr_id
907 ,p_primary_sty_id => l_primary_sty_id
908 ,p_dependent_sty_purpose => cns_fee_evergreen
909 ,x_return_status => l_return_status
910 ,x_dependent_sty_id => l_sty_id);
911
912 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
913 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_fee_evergreen);
914 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
915 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
916 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || cns_fee_evergreen);
917 RAISE Okl_Api.G_EXCEPTION_ERROR;
918 END IF;
919
920
921 OPEN c_tran_num_csr;
922 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
923 CLOSE c_tran_num_csr;
924
925
926 l_stmv_rec.sty_id := l_sty_id;
927 l_stmv_rec.khr_id := l_khr_id;
928 IF (l_kle_id <> -99) THEN
929 l_stmv_rec.kle_id := l_kle_id;
930 ELSE
931 l_stmv_rec.kle_id := null;
932 END IF;
933 l_stmv_rec.sgn_code := 'MANL';
934 l_stmv_rec.say_code := 'CURR';
935 l_stmv_rec.active_yn := 'Y';
936 l_stmv_rec.date_current := sysdate;
937 l_stmv_rec.comments := 'Evergreen Billing';
938
939
940 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating FEE EVERGREEN Streams');
941 Okl_Streams_Pub.create_streams(
942 p_api_version => p_api_version,
943 p_init_msg_list => p_init_msg_list,
944 x_return_status => x_return_status,
945 x_msg_count => x_msg_count,
946 x_msg_data => x_msg_data,
947 p_stmv_rec => l_stmv_rec,
948 x_stmv_rec => lx_stmv_rec);
949
950 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
951 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for FEE EVERGREEN');
952 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
953 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
954 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Streams for FEE EVERGREEN');
955 RAISE Okl_Api.G_EXCEPTION_ERROR;
956 END IF;
957 /*END LOOP;
958 CLOSE c_tran_num_csr;*/
959 ELSE
960 null;
961 END IF;
962 END IF;
963
964 ELSE
965 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Streams for Evergreen Billing exist for this contract.');
966 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Proceeding to creation of stream elements for Evergreen Billing.');
967 END IF;
968
969 FOR stms IN c_stm_id( l_khr_id, l_kle_id ) LOOP
970
971 l_error_message := NULL;
972 l_error_status := Okl_Api.G_RET_STS_SUCCESS;
973
974 -----------------------------------------
975 -- Initialize the date fields to null
976 -----------------------------------------
977
978 l_amount := NULL;
979 l_Stream_bill_date := NULL;
980 l_upper_date := NULL;
981 l_lower_date := NULL;
982 l_evrgrn_strm_purpose := NULL;
983 l_evrgrn_prim_strm_purpose := NULL;
984 create_payable_flag := 'N';
985
986 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
987 IF (stms.sty_name = cns_evergreen_rent) THEN
988
989 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
990 OPEN upper_rental_date_csr ( l_khr_id , l_kle_id, cns_rent );
991 FETCH upper_rental_date_csr INTO l_upper_date;
992 CLOSE upper_rental_date_csr;
993
994 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
995 OPEN lower_rental_date_csr ( l_khr_id , l_kle_id, cns_rent, l_upper_date );
996 FETCH lower_rental_date_csr INTO l_lower_date;
997 CLOSE lower_rental_date_csr;
998
999 -------------------------------
1000 -- Fetch Billing Amount
1001 -------------------------------
1002 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1003 OPEN bill_amt_csr ( l_khr_id , l_kle_id, cns_rent );
1004 FETCH bill_amt_csr INTO l_amount;
1005 CLOSE bill_amt_csr;
1006
1007 ----------------------------------------------------------------
1008 --GET THE LAST RENT STREAM ELEMENT DETAILS
1009 ----------------------------------------------------------------
1010 create_payable_flag := 'N';
1011 l_evrgrn_strm_purpose := null;
1012 l_evrgrn_prim_strm_purpose := null;
1013 l_investor_agrmt_id := null;
1014
1015 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Contract id: ' || l_khr_id || ' Line id: ' || l_kle_id);
1016 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1017 FOR cur_last_strm IN c_last_strm_csr ( l_khr_id , l_kle_id, cns_rent) LOOP
1018 ----------------------------------------------------------------
1019 --CHECK IF THE LAST RENT STREAM WAS SECURITIZED
1020 ----------------------------------------------------------------
1021 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
1022 create_payable_flag := 'Y';
1023 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1024 l_evrgrn_strm_purpose := cns_inv_evrgrn_rent_pay;
1025 l_evrgrn_prim_strm_purpose := cns_evergreen_rent;
1026 l_investor_agrmt_id := cur_sec_strm.khr_id;
1027 exit;
1028 END LOOP;
1029 exit;
1030 END LOOP;
1031 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1032 ELSIF (stms.sty_name = cns_sm_evergreen) THEN -- The stream is 'SERVICE AND MAINTENANCE EVERGREEN'
1033 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1034 OPEN upper_rental_date_csr ( l_khr_id , l_kle_id , cns_sm );
1035 FETCH upper_rental_date_csr INTO l_upper_date;
1036 CLOSE upper_rental_date_csr;
1037
1038 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1039 OPEN lower_rental_date_csr ( l_khr_id , l_kle_id , cns_sm, l_upper_date );
1040 FETCH lower_rental_date_csr INTO l_lower_date;
1041 CLOSE lower_rental_date_csr;
1042
1043 -------------------------------
1044 -- Fetch Billing Amount
1045 -------------------------------
1046 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1047 OPEN bill_amt_csr ( l_khr_id , l_kle_id , cns_sm );
1048 FETCH bill_amt_csr INTO l_amount;
1049 CLOSE bill_amt_csr;
1050
1051 ----------------------------------------------------------------
1052 --GET THE LAST SERVICE AND MAINTENANCE STREAM ELEMENT DETAILS
1053 ----------------------------------------------------------------
1054 create_payable_flag := 'N';
1055 l_evrgrn_strm_purpose := null;
1056 l_evrgrn_prim_strm_purpose := null;
1057 l_investor_agrmt_id := null;
1058 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Contract id: ' || l_khr_id || ' Line id: ' || l_kle_id);
1059 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1060 FOR cur_last_strm IN c_last_strm_csr ( l_khr_id , l_kle_id, cns_sm) LOOP
1061 ----------------------------------------------------------------
1062 --CHECK IF THE LAST SERVICE AND MAINTENANCE STREAM WAS SECURITIZED
1063 ----------------------------------------------------------------
1064 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
1065 create_payable_flag := 'Y';
1066 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1067 l_evrgrn_strm_purpose := cns_inv_sm_pay;
1068 l_evrgrn_prim_strm_purpose := cns_sm_evergreen;
1069 l_investor_agrmt_id := cur_sec_strm.khr_id;
1070 exit;
1071 END LOOP;
1072 exit;
1073 END LOOP;
1074 ELSIF (stms.sty_name = cns_ept_evergreen) THEN-- The stream is 'ESTIMATED PERSONAL PROPERTY TAX EVERGREEN'
1075
1076 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1077 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1078 OPEN upper_rental_date_csr ( l_khr_id , l_kle_id, cns_ept );
1079 FETCH upper_rental_date_csr INTO l_upper_date;
1080 CLOSE upper_rental_date_csr;
1081
1082 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1083 OPEN lower_rental_date_csr ( l_khr_id , l_kle_id, cns_ept, l_upper_date );
1084 FETCH lower_rental_date_csr INTO l_lower_date;
1085 CLOSE lower_rental_date_csr;
1086
1087 -------------------------------
1088 -- Fetch Billing Amount
1089 -------------------------------
1090 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1091 OPEN bill_amt_csr ( l_khr_id , l_kle_id, cns_ept );
1092 FETCH bill_amt_csr INTO l_amount;
1093 CLOSE bill_amt_csr;
1094
1095 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Contract id: ' || l_khr_id || ' Line id: ' || l_kle_id);
1096
1097 create_payable_flag := 'N';
1098 l_evrgrn_strm_purpose := null;
1099 l_evrgrn_prim_strm_purpose := null;
1100 ELSE -- The stream is 'FEE EVERGREEN'
1101
1102 OPEN upper_rental_date_csr ( l_khr_id , l_kle_id, cns_fee );
1103 FETCH upper_rental_date_csr INTO l_upper_date;
1104 CLOSE upper_rental_date_csr;
1105
1106 OPEN lower_rental_date_csr ( l_khr_id , l_kle_id, cns_fee, l_upper_date );
1107 FETCH lower_rental_date_csr INTO l_lower_date;
1108 CLOSE lower_rental_date_csr;
1109
1110 -------------------------------
1111 -- Fetch Billing Amount
1112 -------------------------------
1113 OPEN bill_amt_csr ( l_khr_id , l_kle_id, cns_fee );
1114 FETCH bill_amt_csr INTO l_amount;
1115 CLOSE bill_amt_csr;
1116
1117 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Contract id: ' || l_khr_id || ' Line id: ' || l_kle_id);
1118
1119 create_payable_flag := 'N';
1120 l_evrgrn_strm_purpose := null;
1121 l_evrgrn_prim_strm_purpose := null;
1122 END IF;
1123
1124
1125 ------------------------------------------------------------
1126 --Check if Stream Elements exist already
1127 ------------------------------------------------------------
1128 l_evergreen_date := NULL;
1129
1130 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1131 OPEN evergreen_element_csr ( l_khr_id , l_kle_id , stms.sty_name );
1132 FETCH evergreen_element_csr INTO l_evergreen_date;
1133 CLOSE evergreen_element_csr;
1134
1135 l_last_Stream_bill_date := NULL;
1136 IF l_evergreen_date IS NOT NULL THEN
1137 l_last_Stream_bill_date := l_evergreen_date;
1138 ELSE -- No evergreen elements exist.
1139 l_last_Stream_bill_date := l_upper_date;
1140 END IF;
1141
1142 ------------------------------------------------------------
1143 --Determine billing frequency
1144 ------------------------------------------------------------
1145
1146 l_bill_freq := NULL;
1147 OPEN c_bill_freq_csr ( l_khr_id );
1148 FETCH c_bill_freq_csr INTO l_bill_freq;
1149 CLOSE c_bill_freq_csr;
1150
1151 ------------------------------------------
1152 -- Add frequency to date
1153 ------------------------------------------
1154 l_Stream_bill_date := NULL;
1155 --dkagrawa BUG#4604842 start
1156 -- calling okl_stream_generator_pvt.add_months_new to determine the next bill date
1157 l_billing_frequency := NULL;
1158 IF l_bill_freq = 'A' THEN
1159 l_billing_frequency := 12;
1160 ELSIF l_bill_freq = 'S' THEN
1161 l_billing_frequency := 6;
1162 ELSIF l_bill_freq = 'Q' THEN
1163 l_billing_frequency := 3;
1164 ELSIF l_bill_freq = 'M' THEN
1165 l_billing_frequency := 1;
1166 ELSE
1167 l_error_message := 'Invalid Billing Frequency. ';
1168 l_error_status := Okl_Api.G_RET_STS_ERROR;
1169 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: '||l_error_message);
1170 END IF;
1171 IF l_billing_frequency IS NOT NULL THEN
1172 OKL_STREAM_GENERATOR_PVT.add_months_new(p_start_date => l_last_Stream_bill_date,
1173 p_months_after => l_billing_frequency,
1174 x_date => l_Stream_bill_date,
1175 x_return_status => x_return_status);
1176 END IF;
1177 --dkagrawa BUG#4604842 end
1178
1179
1180 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' => Last Billed: '||l_last_Stream_bill_date);
1181 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' => Next Determined: '||l_Stream_bill_date);
1182
1183 ------------------------------------------------------------
1184 -- If the program has a from and to date supplied the
1185 -- evergreen stream element must be between the two
1186 ------------------------------------------------------------
1187 create_flag := 'Y';
1188
1189 IF p_from_bill_date IS NOT NULL THEN
1190 IF (l_Stream_bill_date < p_from_bill_date) THEN
1191 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Bill_DATE is less than supplied From Date.');
1192 create_flag := 'N';
1193 END IF;
1194 END IF;
1195
1196 IF p_to_bill_date IS NOT NULL THEN
1197 IF (l_Stream_bill_date > p_to_bill_date) THEN
1198 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Bill_DATE is greater than supplied To Date.');
1199 create_flag := 'N';
1200 END IF;
1201 END IF;
1202
1203 -- Check if there is an unbilled Stream element
1204 -- outstanding for this contract
1205
1206 IF ( create_flag = 'Y' ) THEN
1207 l_prev_unbilled_cnt:= 0;
1208
1209 OPEN prev_unbilled_csr ( stms.stm_id , l_last_Stream_bill_date );
1210 FETCH prev_unbilled_csr INTO l_prev_unbilled_cnt;
1211 CLOSE prev_unbilled_csr;
1212 -- CHEck if an unbilled stream element exists for the same date
1213 -- and set the create flag
1214 IF l_prev_unbilled_cnt > 0 THEN
1215 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Previously Unbilled Stream Elements Exist.');
1216 create_flag := 'N';
1217 END IF;
1218 END IF;
1219
1220 -- Check if there is an unbilled Stream element
1221 -- with the same date
1222 IF ( create_flag = 'Y' ) THEN
1223
1224 l_rec_exists_cnt := 0;
1225
1226 OPEN stm_rec_exists_csr( stms.stm_id , l_Stream_bill_date );
1227 FETCH stm_rec_exists_csr INTO l_rec_exists_cnt;
1228 CLOSE stm_rec_exists_csr;
1229 -- CHEck if an unbilled stream element exists for the same date
1230 -- and set the create flag
1231 IF l_rec_exists_cnt > 0 THEN
1232 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Duplicate Stream Element.');
1233 create_flag := 'N';
1234 END IF;
1235 END IF;
1236
1237 ------------------------------------------------------------
1238 --Proceed to Create if within Date Ranges
1239 ------------------------------------------------------------
1240
1241 IF ( create_flag = 'Y' ) THEN
1242
1243 ------------------------------------------------------------
1244 --Create Stream elements for evergreen
1245 ------------------------------------------------------------
1246 IF ( l_amount IS NOT NULL AND l_amount > 0) THEN
1247
1248 l_max_line_num := 0;
1249 OPEN max_line_num_csr ( stms.stm_id );
1250 FETCH max_line_num_csr INTO l_max_line_num;
1251 CLOSE max_line_num_csr;
1252
1253 p_selv_rec.stm_id := stms.stm_id;
1254 p_selv_rec.SE_LINE_NUMBER := NVL( l_max_line_num, 0 ) + 1;
1255 p_selv_rec.STREAM_ELEMENT_DATE := l_Stream_bill_date;
1256 p_selv_rec.AMOUNT := l_amount;
1257 p_selv_rec.COMMENTS := 'EVERGREEN BILLING ELEMENTS';
1258 p_selv_rec.ACCRUED_YN := 'Y';
1259
1260 Okl_Sel_Pvt.insert_row(
1261 p_api_version,
1262 p_init_msg_list,
1263 x_return_status,
1264 x_msg_count,
1265 x_msg_data,
1266 p_selv_rec,
1267 x_selv_rec);
1268
1269 l_sel_id := x_selv_rec.id;
1270
1271 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' --Evergreen Stream Element id: ' || l_sel_id);
1272 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1273 l_error_message := 'Error Creating Stream Element for Contract: '
1274 ||stms.contract_number
1275 ||' Stream: '||stms.sty_name
1276 ||' Bill Date: '||l_Stream_bill_date
1277 ||' Amount: '||l_amount;
1278 l_error_status := Okl_Api.G_RET_STS_ERROR;
1279 --RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1280 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1281 l_error_message := 'Error Creating Stream Element for Contract: '
1282 ||stms.contract_number
1283 ||' Stream: '||stms.sty_name
1284 ||' Bill Date: '||l_Stream_bill_date
1285 ||' Amount: '||l_amount;
1286 l_error_status := Okl_Api.G_RET_STS_ERROR;
1287 --RAISE Okl_Api.G_EXCEPTION_ERROR;
1288 ELSE
1289
1290 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Created Evergreen Stream Element for Contract: '
1291 ||stms.contract_number
1292 ||' Stream: '||stms.sty_name
1293 ||' Bill Date: '||l_Stream_bill_date
1294 ||' Amount: '||l_amount
1295 );
1296 END IF;
1297
1298 IF l_evrgrn_strm_purpose IS NOT NULL AND l_error_status = Okl_Api.G_RET_STS_SUCCESS THEN
1299
1300 -- Added by fmiao for bug 4961860
1301 l_evrgrn_psthrgh_flg := 0;
1302 IF(l_evrgrn_strm_purpose = 'INVESTOR_EVERGREEN_RENT_PAY') THEN
1303 OPEN check_res_in_pool(l_investor_agrmt_id);
1304 FETCH check_res_in_pool INTO l_res_in_pool;
1305 CLOSE check_res_in_pool;
1306 IF(l_res_in_pool IS NULL OR l_res_in_pool <> 'Y') THEN
1307 l_evrgrn_psthrgh_flg := 1;
1308 END IF;
1309 END IF;
1310 -- end fmiao for bug 4961860
1311
1312 IF(l_evrgrn_psthrgh_flg = 0) THEN
1313 -- Added by fmiao for bug 4961860
1314 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- EVERGREEN PAYABLE STREAM TYPE: ' || l_evrgrn_strm_purpose);
1315 ----------------------------------------------------------------
1316 --PROCESSING FOR EVERGREEN STREAM TYPE PAYABLE TO INVESTOR
1317 ----------------------------------------------------------------
1318 --get stream type id
1319 l_sty_id := null;
1320 l_primary_sty_id := NULL;
1321
1322 /*OPEN get_sty_id_csr(l_evrgrn_strm_purpose);
1323 FETCH get_sty_id_csr INTO l_sty_id;
1324 CLOSE get_sty_id_csr;*/
1325
1326 ------------------------------------------------------
1327 --Get the sty id for Evergreen Payable
1328 ------------------------------------------------------
1329 /*OPEN get_prim_sty_id_csr(l_khr_id, l_kle_id, l_evrgrn_prim_strm_purpose);
1330 FETCH get_prim_sty_id_csr INTO l_primary_sty_id;
1331 CLOSE get_prim_sty_id_csr;
1332 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Sty id for ' || l_evrgrn_prim_strm_purpose || ': ' || l_primary_sty_id); */
1333
1334 --change for User Defined Streams, by pjgomes, on 18 Oct 2004
1335 OKL_STREAMS_UTIL.get_primary_stream_type(p_khr_id => l_investor_agrmt_id
1336 ,p_primary_sty_purpose => l_evrgrn_strm_purpose
1337 ,x_return_status => l_return_status
1338 ,x_primary_sty_id => l_sty_id);
1339
1340 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1341 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || l_evrgrn_strm_purpose);
1342 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1343 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1344 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Obtaining sty id for: ' || l_evrgrn_strm_purpose);
1345 RAISE Okl_Api.G_EXCEPTION_ERROR;
1346 END IF;
1347
1348 --check for stream
1349 l_stm_id := null;
1350 l_se_line_number := null;
1351
1352 OPEN l_stream_csr(l_khr_id, l_kle_id, l_sty_id);
1353 FETCH l_stream_csr INTO l_stm_id;
1354 CLOSE l_stream_csr;
1355
1356 --create stream for evergreen payable
1357 IF (l_stm_id IS NULL) THEN
1358 l_stmv_rec := l_init_stmv_rec;
1359
1360 OPEN c_tran_num_csr;
1361 FETCH c_tran_num_csr INTO l_stmv_rec.transaction_number;
1362 CLOSE c_tran_num_csr;
1363
1364 l_stmv_rec.sty_id := l_sty_id;
1365 l_stmv_rec.khr_id := l_khr_id;
1366 --l_stmv_rec.kle_id := l_kle_id;
1367 IF (l_kle_id <> -99) THEN
1368 l_stmv_rec.kle_id := l_kle_id;
1369 ELSE
1370 l_stmv_rec.kle_id := null;
1371 END IF;
1372 l_stmv_rec.sgn_code := 'MANL';
1373 l_stmv_rec.say_code := 'CURR';
1374 l_stmv_rec.active_yn := 'Y';
1375 l_stmv_rec.date_current := sysdate;
1376 l_stmv_rec.comments := l_evrgrn_strm_purpose;
1377 IF (l_investor_agrmt_id IS NOT NULL) THEN
1378 l_stmv_rec.source_id := l_investor_agrmt_id;
1379 l_stmv_rec.source_table := 'OKL_K_HEADERS';
1380 END IF;
1381
1382 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Creating ' || l_evrgrn_strm_purpose || ' Stream');
1383
1384 Okl_Streams_Pub.create_streams(
1385 p_api_version => p_api_version,
1386 p_init_msg_list => p_init_msg_list,
1387 x_return_status => x_return_status,
1388 x_msg_count => x_msg_count,
1389 x_msg_data => x_msg_data,
1390 p_stmv_rec => l_stmv_rec,
1391 x_stmv_rec => lx_stmv_rec);
1392
1393 l_stm_id := lx_stmv_rec.id;
1394 l_se_line_number := 1;
1395
1396 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream element line number => ' || l_se_line_number);
1397 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1398 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: Creating Stream for ' || l_evrgrn_strm_purpose);
1399 RAISE Okl_Api.G_EXCEPTION_ERROR;
1400 ELSE
1401 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- SUCCESS: Creating Stream for ' || l_evrgrn_strm_purpose);
1402 END IF;
1403 ELSE
1404 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream for ' || l_evrgrn_strm_purpose || ' found');
1405 open l_stream_line_nbr_csr(l_stm_id);
1406 fetch l_stream_line_nbr_csr into l_se_line_number;
1407 close l_stream_line_nbr_csr;
1408 l_se_line_number := l_se_line_number + 1;
1409 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Stream element line number => ' || l_se_line_number);
1410 END IF;
1411
1412 --create stream element for evergreen stream payable
1413 IF (l_stm_id IS NOT NULL) THEN
1414 l_selv_rec := l_init_selv_rec;
1415 l_selv_rec.stm_id := l_stm_id;
1416 l_selv_rec.SE_LINE_NUMBER := l_se_line_number;
1417 l_selv_rec.STREAM_ELEMENT_DATE := sysdate;
1418 l_selv_rec.AMOUNT := l_amount;
1419 l_selv_rec.COMMENTS := l_evrgrn_strm_purpose || ' ELEMENTS';
1420 l_selv_rec.ACCRUED_YN := 'Y';
1421
1422 l_selv_rec.sel_id := l_sel_id;
1423 IF (l_investor_agrmt_id IS NOT NULL) THEN
1424 l_selv_rec.source_id := l_investor_agrmt_id;
1425 l_selv_rec.source_table := 'OKL_K_HEADERS';
1426 END IF;
1427
1428 Okl_Sel_Pvt.insert_row(
1429 p_api_version,
1430 p_init_msg_list,
1431 x_return_status,
1432 x_msg_count,
1433 x_msg_data,
1434 l_selv_rec,
1435 lx_selv_rec);
1436
1437 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
1438 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Error Creating Payable Stream Element for Contract: '
1439 || evergreen_contracts.contract_number
1440 ||' Stream: '||l_evrgrn_strm_purpose
1441 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
1442 ||' Amount: '||l_amount);
1443 RAISE Okl_Api.G_EXCEPTION_ERROR;
1444 ELSE
1445
1446 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Created Investor Payable Stream Element for Contract: '
1447 || evergreen_contracts.contract_number
1448 ||' Stream: '||l_evrgrn_strm_purpose
1449 ||' Bill Date: '||l_selv_rec.STREAM_ELEMENT_DATE
1450 ||' Amount: '||l_amount
1451 );
1452 END IF;
1453 END IF;
1454 END IF; -- Added by fmiao for bug 4961860
1455 END IF;
1456
1457 END IF;
1458 END IF;
1459
1460 IF l_error_status <> OKL_API.G_RET_STS_SUCCESS THEN
1461 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- ERROR: '||l_error_message);
1462 END IF;
1463
1464 END LOOP;
1465 --dkagrawa bug# 4728636 changes start
1466 IF l_prev_khr_id IS NULL THEN
1467 l_prev_khr_id := evergreen_contracts.khr_id;
1468 END IF;
1469 IF l_prev_khr_id <> evergreen_contracts.khr_id THEN
1470 IF (l_error_status = OKL_API.G_RET_STS_SUCCESS) THEN
1471 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date ( l_prev_khr_id );
1472 END IF;
1473 l_prev_khr_id := evergreen_contracts.khr_id;
1474 END IF;
1475 END LOOP;
1476 IF l_prev_khr_id IS NOT NULL THEN
1477 IF (l_error_status = OKL_API.G_RET_STS_SUCCESS) THEN
1478 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date ( l_prev_khr_id );
1479 END IF;
1480 END IF;
1481 --dkagrawa bug# 4728636 changes end
1482
1483 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '=========================================================================================');
1484 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ******* End Evergreen Processing *******');
1485 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '=========================================================================================');
1486
1487 ------------------------------------------------------------
1488 -- End processing
1489 ------------------------------------------------------------
1490
1491
1492 x_return_status := l_return_status;
1493 Okl_Api.END_ACTIVITY (
1494 x_msg_count => x_msg_count,
1495 x_msg_data => x_msg_data);
1496
1497
1498 EXCEPTION
1499
1500 ------------------------------------------------------------
1501 -- Exception handling
1502 ------------------------------------------------------------
1503
1504 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1505
1506 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1507 p_api_name => l_api_name,
1508 p_pkg_name => G_PKG_NAME,
1509 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
1510 x_msg_count => x_msg_count,
1511 x_msg_data => x_msg_data,
1512 p_api_type => '_PVT');
1513
1514 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1515
1516 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1517 p_api_name => l_api_name,
1518 p_pkg_name => G_PKG_NAME,
1519 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1520 x_msg_count => x_msg_count,
1521 x_msg_data => x_msg_data,
1522 p_api_type => '_PVT');
1523
1524 WHEN OTHERS THEN
1525
1526 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1527 p_api_name => l_api_name,
1528 p_pkg_name => G_PKG_NAME,
1529 p_exc_name => 'OTHERS',
1530 x_msg_count => x_msg_count,
1531 x_msg_data => x_msg_data,
1532 p_api_type => '_PVT');
1533
1534 END BILL_EVERGREEN_STREAMS;
1535
1536
1537 END Okl_Evergreen_Billing_Pvt;