[Home] [Help]
PACKAGE BODY: APPS.OKL_PROCESS_STREAMS_PVT
Source
1 PACKAGE BODY OKL_PROCESS_STREAMS_PVT AS
2 /* $Header: OKLRPSRB.pls 120.47.12020000.4 2012/10/23 06:08:47 bkatraga ship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7 -- Start of wraper code generated automatically by Debug code generator
8 L_MODULE VARCHAR2(40) := 'LEASE.STREAMS';
9 L_DEBUG_ENABLED VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
10 L_LEVEL_PROCEDURE NUMBER;
11 IS_DEBUG_PROCEDURE_ON BOOLEAN;
12 -- End of wraper code generated automatically by Debug code generator
13 --| : 10-08-04 GKADARKA -- Fixes for bug 3909261 -Start |
14
15 G_WF_EVT_KHR_GEN_STRMS CONSTANT VARCHAR2(61) := 'oracle.apps.okl.la.lease_contract.stream_generation_completed';
16 G_WF_ITM_CONTRACT_ID CONSTANT VARCHAR2(15) := 'CONTRACT_ID';
17 G_WF_ITM_CONTRACT_PROCESS CONSTANT VARCHAR2(20) := 'CONTRACT_PROCESS';
18 --| : 10-08-04 GKADARKA -- Fixes for bug 3909261 -Start |
19
20 --Added by kthiruva on 11-Nov-2005 for the VR build
21 --Bug 4726209 - Start of Changes
22 G_MINUS_ONE CONSTANT NUMBER := -1;
23 G_MINUS_THREE CONSTANT NUMBER := -3;
24 G_MINUS_SIX CONSTANT NUMBER := -6;
25 G_MINUS_TWELVE CONSTANT NUMBER := -12;
26 --Bug 4726209 - End of Changes
27
28 FUNCTION Format_Number
29 (p_amount IN NUMBER,
30 p_contract_id IN NUMBER)
31 RETURN NUMBER
32 AS
33 l_rounding_rule VARCHAR2(30);
34 l_rounded_amount NUMBER := 0;
35 l_currency_code VARCHAR2(30);
36 l_precision NUMBER;
37 l_pos_dot NUMBER;
38 l_to_add NUMBER := 1;
39 -- modify this parameter to apply rounding to the amount
40 l_apply_rounding_rule BOOLEAN := FALSE;
41 CURSOR rule_cur IS
42 SELECT ael_rounding_rule
43 FROM OKL_SYS_ACCT_OPTS;
44 CURSOR currency_cur IS
45 SELECT currency_code
46 FROM okc_k_headers_v
47 WHERE id = p_contract_id;
48 CURSOR prec_cur (l_currency_code VARCHAR2) IS
49 SELECT precision
50 FROM fnd_currencies_vl
51 WHERE currency_code = l_currency_code
52 AND enabled_flag = 'Y'
53 AND NVL(start_date_active, SYSDATE) <= SYSDATE
54 AND NVL(end_date_active, SYSDATE) >= SYSDATE;
55 BEGIN
56 l_rounded_amount := p_amount;
57 FOR rule_rec IN rule_cur LOOP
58 l_rounding_rule := rule_rec.ael_rounding_rule;
59 END LOOP;
60 FOR currency_rec IN currency_cur LOOP
61 l_currency_code := currency_rec.currency_code;
62 END LOOP;
63 FOR prec_rec IN prec_cur (l_currency_code) LOOP
64 l_precision := prec_rec.precision;
65 END LOOP;
66 IF( l_apply_rounding_rule = TRUE )
67 THEN
68 IF (l_rounding_rule = 'UP') THEN
69 l_pos_dot := INSTR(TO_CHAR(p_amount),'.') ;
70 IF (l_pos_dot > 0) AND (SUBSTR(p_amount, l_pos_dot + l_precision+1, 1) IS NOT NULL) THEN
71 FOR i IN 1..l_precision LOOP
72 l_to_add := l_to_add/10;
73 END LOOP;
74 l_rounded_amount := p_amount + l_to_add;
75 ELSE
76 l_rounded_amount := p_amount;
77
78 END IF;
79 l_rounded_amount := TRUNC(l_rounded_amount,l_precision );
80 ELSIF l_rounding_rule = 'DOWN' THEN
81 l_rounded_amount := TRUNC(p_amount, l_precision);
82 ELSIF l_rounding_rule = 'NEAREST' THEN
83 l_rounded_amount := ROUND(p_amount, l_precision );
84 END IF;
85 ELSE
86 l_rounded_amount := TRUNC(l_rounded_amount,l_precision );
87 END IF;
88 RETURN l_rounded_amount;
89 EXCEPTION
90 WHEN OTHERS THEN
91 RETURN TO_NUMBER(NULL);
92 END Format_Number;
93 FUNCTION calculate_present_value(p_future_amount IN NUMBER,
94 p_discount_rate IN NUMBER,
95 p_periods_per_year IN NUMBER,
96 p_total_periods IN NUMBER
97 )
98 RETURN NUMBER
99 AS
100 l_temp_amount NUMBER;
101 l_denominator NUMBER := 1;
102 l_present_value NUMBER;
103 l_counter NUMBER;
104 BEGIN
105 l_temp_amount := 1 + p_discount_rate / p_periods_per_year;
106 l_denominator := POWER(l_temp_amount, p_total_periods);
107 l_present_value := p_future_amount / l_denominator;
108 RETURN l_present_value;
109 EXCEPTION
110 WHEN OTHERS THEN
111 RETURN TO_NUMBER(NULL);
112 END calculate_present_value;
113 -- added to generate SECURITY DEPOSIT streams
114 -- akjain 08-10-2002
115 PROCEDURE GEN_SEC_DEP_STRMS(p_api_version IN NUMBER
116 ,p_init_msg_list IN VARCHAR2
117 ,p_khr_id IN NUMBER
118 ,p_transaction_number IN NUMBER
119 ,p_reporting_streams IN VARCHAR2
120 ,x_return_status OUT NOCOPY VARCHAR2
121 ,x_msg_count OUT NOCOPY NUMBER
122 ,x_msg_data OUT NOCOPY VARCHAR2)
123 IS
124 cursor k_line_id_csr(p_khr_id NUMBER) is
125 select cle.id kle_id
126 from
127 okc_k_headers_b chr,
128 okc_k_lines_b cle,
129 okc_line_styles_b lse,
130 okc_k_items cim,
131 okl_strm_type_b sty
132 where
133 chr.id = p_khr_id
134 and chr.id = cle.dnz_chr_id
135 and cle.lse_id = lse.id
136 and lse.lty_code = 'FEE'
137 and cim.cle_id = cle.id
138 and cim.dnz_chr_id = cle.dnz_chr_id
139 and sty.code = 'SECURITY DEPOSIT'
140 and cim.object1_id1 = sty.id;
141 cursor rule_grp_csr(p_khr_id NUMBER, p_kle_id NUMBER) is
142 SELECT
143 KHRB.ID CHR_ID,
144 KHRB.CONTRACT_NUMBER CONTRACT_NUMBER,
145 KLIN.ID SERVICE_FEE_ID,
146 CRLB.OBJECT1_ID1 STY_ID,
147 CRGB.ID RULE_GROUP_ID,
148 CRLB.ID SLH_ID
149 FROM
150 OKC_K_HEADERS_B KHRB,
151 OKC_RULE_GROUPS_B CRGB,
152 OKC_RULES_B CRLB,
153 OKC_K_LINES_V KLIN,
154 OKL_STRM_TYPE_B STYB
155 WHERE
156 KHRB.ID = p_khr_id
157 AND CRGB.DNZ_CHR_ID = KHRB.ID
158 AND CRGB.RGD_CODE = 'LALEVL'
159 AND CRLB.RULE_INFORMATION_CATEGORY = 'SLH'
160 AND CRLB.RGP_ID = CRGB.ID
161 AND KLIN.ID = p_kle_id
162 AND CRGB.CLE_ID = KLIN.ID
163 AND STYB.CODE = 'SECURITY DEPOSIT'
164 AND STYB.ID = CRLB.OBJECT1_ID1;
165 cursor rules_csr( rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
166 rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
167 chrId NUMBER,
168 slh_id NUMBER
169 )
170 IS
171 SELECT CRLB.ID SLL_ID,
172 CRLB.OBJECT1_ID1,
173 CRLB.RULE_INFORMATION1,
174 CRLB.RULE_INFORMATION2,
175 CRLB.RULE_INFORMATION3,
176 CRLB.RULE_INFORMATION5,
177
178 CRLB.RULE_INFORMATION6,
179 CRLB.RULE_INFORMATION10
180 FROM OKC_RULE_GROUPS_B CRGB,
181 OKC_RULES_B CRLB
182 WHERE CRGB.ID = slh_id
183 AND CRLB.RGP_ID = CRGB.ID
184 AND CRGB.RGD_CODE = RGCODE
185 AND CRLB.RULE_INFORMATION_CATEGORY = RLCAT
186 AND CRGB.DNZ_CHR_ID = CHRID
187 ORDER BY CRLB.RULE_INFORMATION1;
188 l_kle_id NUMBER;
189 l_slh_id NUMBER;
190 l_sty_id NUMBER;
191 l_stmv_rec stmv_rec_type;
192 l_selv_tbl selv_tbl_type;
193 x_stmv_rec stmv_rec_type;
194 x_selv_tbl selv_tbl_type;
195 dummy_selv_tbl selv_tbl_type;
196 l_frequency VARCHAR2(40);
197 l_number_of_periods NUMBER;
198 l_amount NUMBER;
199 l_arrear_yn VARCHAR2(30);
200 l_date_start DATE;
201 l_months_per_period NUMBER := 1;
202 l_num_of_adv_payments NUMBER;
203 i NUMBER;
204 j NUMBER := 0;
205 k NUMBER ;
206 BEGIN
207 FOR k_line_id_data_csr in k_line_id_csr(p_khr_id)
208 LOOP
209 l_kle_id := k_line_id_data_csr.kle_id;
210 -- select the Rule Group ID ( SLH_ID) for SERVICE AND MAINTAINCE LINE
211 FOR rule_grp_data_csr in rule_grp_csr(p_khr_id, l_kle_id)
212 LOOP
213 l_slh_id := rule_grp_data_csr.RULE_GROUP_ID;
214 -- populate the header record
215 l_stmv_rec.sty_id := rule_grp_data_csr.STY_ID;
216 l_stmv_rec.khr_id := p_khr_id;
217 l_stmv_rec.kle_id := rule_grp_data_csr.SERVICE_FEE_ID;
218 l_stmv_rec.sgn_code := G_STREAM_GENERATOR_MANL;
219 l_stmv_rec.say_code := G_STREAM_ACTIVITY_WORK;
220 l_stmv_rec.active_yn := G_STREAM_ACTIVE_YN;
221 l_stmv_rec.date_working := SYSDATE ;
222 l_stmv_rec.transaction_number := p_transaction_number;
223 l_stmv_rec.comments := null;
224 -- smahapat 11/10/02 multi-gaap -- addition
225 IF (p_reporting_streams = OKL_API.G_TRUE)
226 THEN
227 l_stmv_rec.purpose_code := G_PURPOSE_CODE_REPORT;
228 END IF;
229 -- smahapat addition end
230
231 FOR rules_data_csr in rules_csr('LALEVL', 'SLL', p_khr_id, l_slh_id)
232 LOOP
233 IF( rules_data_csr.RULE_INFORMATION2 IS NOT NULL and rules_data_csr.RULE_INFORMATION2 <> OKL_API.G_MISS_CHAR)
234 THEN
235 -- Get all the Rules for this rule group
236 l_frequency := rules_data_csr.object1_id1 ;
237 l_date_start := FND_DATE.CANONICAL_TO_DATE(rules_data_csr.RULE_INFORMATION2);
238 l_number_of_periods := TO_NUMBER(rules_data_csr.RULE_INFORMATION3) ;
239 l_amount := TO_NUMBER(rules_data_csr.RULE_INFORMATION6) ;
240 l_arrear_yn := NVL(rules_data_csr.RULE_INFORMATION10, 'N');
241 l_num_of_adv_payments := TO_NUMBER(rules_data_csr.RULE_INFORMATION5) ;
242 -- if the payments are in ARREAR then adjust the start date
243 IF(l_frequency = 'A')
244 THEN
245 l_months_per_period := 12;
246 ELSIF(l_frequency = 'S')
247 THEN
248 l_months_per_period := 6;
249 ELSIF(l_frequency = 'Q')
250 THEN
251 l_months_per_period := 3;
252 ELSIF(l_frequency = 'M')
253 THEN
254 l_months_per_period := 1;
255 END IF ;
256 IF(l_arrear_yn = 'N')
257 THEN
258 l_date_start := l_date_start;
259 ELSE
260 l_date_start := ADD_MONTHS(l_date_start, l_months_per_period);
261 END IF;
262 -- expand the Payment Levels into Streams
263 FOR i in 1..l_number_of_periods
264 LOOP
265 j := j + 1;
266 IF(i = 1)
267 THEN
268 l_selv_tbl(j).stream_element_date := l_date_start;
269 ELSE
270 l_selv_tbl(j).stream_element_date := ADD_MONTHS(l_selv_tbl(j - 1).stream_element_date, l_months_per_period);
271 END IF;
272 l_selv_tbl(j).amount := l_amount;
273 l_selv_tbl(j).se_line_number := j;
274 k := i;
275 END LOOP;
276 -- modify the payment amounts based on number of advance payments
277
278 IF(l_num_of_adv_payments IS NOT NULL)
279 THEN
280 IF(l_num_of_adv_payments = 1)
281 THEN
282 l_selv_tbl(j - k + 1).amount := l_selv_tbl(j - k + 1).amount * 2;
283 -- last payment should be 0
284 l_selv_tbl(j).amount := 0;
285 ELSIF(l_num_of_adv_payments = 2)
286 THEN
287 l_selv_tbl(j - k + 1).amount := l_selv_tbl(j - k + 1).amount * 3;
288 -- last 2 payments should be 0
289 l_selv_tbl(j -1).amount := 0;
290 l_selv_tbl(j).amount := 0;
291 ELSIF(l_num_of_adv_payments = 3)
292 THEN
293 l_selv_tbl(j - k + 1).amount := l_selv_tbl(j - k + 1).amount * 4;
294 -- last 3 payments should be 0
295 l_selv_tbl(j - 2).amount := 0;
296 l_selv_tbl(j - 1).amount := 0;
297 l_selv_tbl(j ).amount := 0;
298 END IF;
299 END IF;
300 END IF;
301 END LOOP;
302 -- Start of wraper code generated automatically by Debug code generator for Okl_Streams_Pub.create_streams
303 IF(L_DEBUG_ENABLED='Y') THEN
304 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
305 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
306 END IF;
307 IF(IS_DEBUG_PROCEDURE_ON) THEN
308 BEGIN
309 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call Okl_Streams_Pub.create_streams ');
310 END;
311 END IF;
312 Okl_Streams_Pub.create_streams(p_api_version
313 ,p_init_msg_list
314 ,x_return_status
315 ,x_msg_count
316 ,x_msg_data
317 ,l_stmv_rec
318 ,l_selv_tbl
319 ,x_stmv_rec
320 ,x_selv_tbl);
321 IF(IS_DEBUG_PROCEDURE_ON) THEN
322 BEGIN
323 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call Okl_Streams_Pub.create_streams ');
324 END;
325 END IF;
326 -- End of wraper code generated automatically by Debug code generator for Okl_Streams_Pub.create_streams
327 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
328 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
329 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
330 RAISE G_EXCEPTION_ERROR;
331 END IF;
332 END LOOP;
333 END LOOP;
334 EXCEPTION
335 WHEN G_EXCEPTION_ERROR THEN
336 x_return_status := G_RET_STS_ERROR;
337 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
338 p_msg_name => G_UNEXPECTED_ERROR,
339 p_token1 => G_SQLCODE_TOKEN,
340 p_token1_value => SQLCODE,
341 p_token2 => G_SQLERRM_TOKEN,
342 p_token2_value => SQLERRM );
343 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
344 x_return_status := G_RET_STS_UNEXP_ERROR;
345 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
346 p_msg_name => G_UNEXPECTED_ERROR,
347 p_token1 => G_SQLCODE_TOKEN,
348 p_token1_value => SQLCODE,
349 p_token2 => G_SQLERRM_TOKEN,
350 p_token2_value => SQLERRM );
351 WHEN OTHERS THEN
352 x_return_status := G_RET_STS_UNEXP_ERROR;
353 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
354 p_msg_name => G_UNEXPECTED_ERROR,
355 p_token1 => G_SQLCODE_TOKEN,
356 p_token1_value => SQLCODE,
357 p_token2 => G_SQLERRM_TOKEN,
358 p_token2_value => SQLERRM );
359 END GEN_SEC_DEP_STRMS;
360
361 -- Added procedure to resolve Bug # 2389224
362 -- Procedure to calculate Streams for the Service lines of a contract
363 PROCEDURE GEN_SERV_MAIN_LINE_STRMS(p_api_version IN NUMBER
364 ,p_init_msg_list IN VARCHAR2
365 ,p_khr_id IN NUMBER
366 ,p_transaction_number IN NUMBER
367 ,p_reporting_streams IN VARCHAR2
368 ,x_return_status OUT NOCOPY VARCHAR2
369 ,x_msg_count OUT NOCOPY NUMBER
370 ,x_msg_data OUT NOCOPY VARCHAR2)
371 IS
372 cursor rule_grp_csr(p_khr_id NUMBER) is
373 SELECT
374 KHRB.ID CHR_ID,
375 KHRB.CONTRACT_NUMBER CONTRACT_NUMBER,
376 KLIN.ID SERVICE_FEE_ID,
377 CRLB.OBJECT1_ID1 STY_ID,
378 CRGB.ID RULE_GROUP_ID,
379 CRLB.ID SLH_ID
380 FROM
381 OKC_K_HEADERS_B KHRB,
382 OKC_RULE_GROUPS_B CRGB,
383 OKC_RULES_B CRLB,
384 OKC_K_LINES_V KLIN,
385 OKC_LINE_STYLES_V LSTL
386 WHERE
387 KHRB.ID = p_khr_id
388 AND CRGB.DNZ_CHR_ID = KHRB.ID
389 AND CRGB.RGD_CODE = 'LALEVL'
390 AND CRLB.RULE_INFORMATION_CATEGORY = 'SLH'
391 AND CRLB.RGP_ID = CRGB.ID
392 AND CRGB.CLE_ID = KLIN.ID
393 AND KLIN.LSE_ID = LSTL.ID
394 AND LSTL.LTY_CODE = 'SOLD_SERVICE'
395 --smahapat 03/04/03 bug 2823581
396 AND KLIN.STS_CODE IN ('PASSED','COMPLETE');
397 cursor rules_csr( rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
398 rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
399 chrId NUMBER,
400 slh_id NUMBER
401 )
402 IS
403 SELECT CRLB.ID SLL_ID,
404 CRLB.OBJECT1_ID1,
405 CRLB.RULE_INFORMATION1,
406
407 CRLB.RULE_INFORMATION2,
408 CRLB.RULE_INFORMATION3,
409 CRLB.RULE_INFORMATION5,
410 CRLB.RULE_INFORMATION6,
411 CRLB.RULE_INFORMATION10
412 FROM OKC_RULE_GROUPS_B CRGB,
413 OKC_RULES_B CRLB
414 WHERE CRGB.ID = slh_id
415 AND CRLB.RGP_ID = CRGB.ID
416 AND CRGB.RGD_CODE = RGCODE
417 AND CRLB.RULE_INFORMATION_CATEGORY = RLCAT
418 AND CRGB.DNZ_CHR_ID = CHRID
419 ORDER BY CRLB.RULE_INFORMATION1;
420 l_slh_id NUMBER;
421 l_sty_id NUMBER;
422 l_stmv_rec stmv_rec_type;
423 l_selv_tbl selv_tbl_type;
424 x_stmv_rec stmv_rec_type;
425 x_selv_tbl selv_tbl_type;
426 dummy_selv_tbl selv_tbl_type;
427 l_frequency VARCHAR2(40);
428 l_number_of_periods NUMBER;
429 l_amount NUMBER;
430 l_arrear_yn VARCHAR2(30);
431 l_date_start DATE;
432 l_months_per_period NUMBER := 1;
433 l_num_of_adv_payments NUMBER;
434 i NUMBER;
435 j NUMBER := 0;
436 k NUMBER ;
437 BEGIN
438 -- select the Rule Group ID ( SLH_ID) for SERVICE AND MAINTAINCE LINE
439 FOR rule_grp_data_csr in rule_grp_csr(p_khr_id)
440 LOOP
441 l_slh_id := rule_grp_data_csr.RULE_GROUP_ID;
442 -- populate the header record
443 l_stmv_rec.sty_id := rule_grp_data_csr.STY_ID;
444 l_stmv_rec.khr_id := p_khr_id;
445 l_stmv_rec.kle_id := rule_grp_data_csr.SERVICE_FEE_ID;
446 l_stmv_rec.sgn_code := G_STREAM_GENERATOR_MANL;
447 l_stmv_rec.say_code := G_STREAM_ACTIVITY_WORK;
448 l_stmv_rec.active_yn := G_STREAM_ACTIVE_YN;
449 l_stmv_rec.date_working := SYSDATE ;
450 l_stmv_rec.transaction_number := p_transaction_number;
451 l_stmv_rec.comments := null;
452 -- smahapat 11/10/02 multi-gaap -- addition
453 IF (p_reporting_streams = OKL_API.G_TRUE)
454 THEN
455 l_stmv_rec.purpose_code := G_PURPOSE_CODE_REPORT;
456 END IF;
457 -- smahapat addition end
458
459 FOR rules_data_csr in rules_csr('LALEVL', 'SLL', p_khr_id, l_slh_id)
460 LOOP
461 IF( rules_data_csr.RULE_INFORMATION2 IS NOT NULL and rules_data_csr.RULE_INFORMATION2 <> OKL_API.G_MISS_CHAR)
462 THEN
463 -- Get all the Rules for this rule group
464 l_frequency := rules_data_csr.object1_id1 ;
465 l_date_start := FND_DATE.CANONICAL_TO_DATE(rules_data_csr.RULE_INFORMATION2);
466 l_number_of_periods := TO_NUMBER(rules_data_csr.RULE_INFORMATION3) ;
467 l_amount := TO_NUMBER(rules_data_csr.RULE_INFORMATION6) ;
468 l_arrear_yn := NVL(rules_data_csr.RULE_INFORMATION10, 'N');
469 l_num_of_adv_payments := TO_NUMBER(rules_data_csr.RULE_INFORMATION5) ;
470 -- if the payments are in ARREAR then adjust the start date
471 IF(l_frequency = 'A')
472 THEN
473 l_months_per_period := 12;
474 ELSIF(l_frequency = 'S')
475 THEN
476 l_months_per_period := 6;
477 ELSIF(l_frequency = 'Q')
478 THEN
479 l_months_per_period := 3;
480 ELSIF(l_frequency = 'M')
481 THEN
482 l_months_per_period := 1;
483 END IF ;
484 IF(l_arrear_yn = 'N')
485 THEN
486 l_date_start := l_date_start;
487 ELSE
488 l_date_start := ADD_MONTHS(l_date_start, l_months_per_period);
489 END IF;
490 -- Bug#2821088 BAKUCHIB Start
491 l_selv_tbl.delete;
492 j := 0;
493 -- Bug#2821088 BAKUCHIB End
494 -- expand the Payment Levels into Streams
495 FOR i in 1..l_number_of_periods
496 LOOP
497 j := j + 1;
498 IF(i = 1)
499 THEN
500 l_selv_tbl(j).stream_element_date := l_date_start;
501 ELSE
502 l_selv_tbl(j).stream_element_date := ADD_MONTHS(l_selv_tbl(j - 1).stream_element_date, l_months_per_period);
503 END IF;
504 l_selv_tbl(j).amount := l_amount;
505 l_selv_tbl(j).se_line_number := j;
506
507 k := i;
508 END LOOP;
509 -- modify the payment amounts based on number of advance payments
510 IF(l_num_of_adv_payments IS NOT NULL)
511 THEN
512 IF(l_num_of_adv_payments = 1)
513 THEN
514 l_selv_tbl(j - k + 1).amount := l_selv_tbl(j - k + 1).amount * 2;
515 -- last payment should be 0
516 l_selv_tbl(j).amount := 0;
517 ELSIF(l_num_of_adv_payments = 2)
518 THEN
519 l_selv_tbl(j - k + 1).amount := l_selv_tbl(j - k + 1).amount * 3;
520 -- last 2 payments should be 0
521 l_selv_tbl(j -1).amount := 0;
522 l_selv_tbl(j).amount := 0;
523 ELSIF(l_num_of_adv_payments = 3)
524 THEN
525 l_selv_tbl(j - k + 1).amount := l_selv_tbl(j - k + 1).amount * 4;
526 -- last 3 payments should be 0
527 l_selv_tbl(j - 2).amount := 0;
528 l_selv_tbl(j - 1).amount := 0;
529 l_selv_tbl(j ).amount := 0;
530 END IF;
531 END IF;
532 END IF;
533 END LOOP;
534 -- Start of wraper code generated automatically by Debug code generator for Okl_Streams_Pub.create_streams
535 IF(L_DEBUG_ENABLED='Y') THEN
536 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
537 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
538 END IF;
539 IF(IS_DEBUG_PROCEDURE_ON) THEN
540 BEGIN
541 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call Okl_Streams_Pub.create_streams ');
542 END;
543 END IF;
544 Okl_Streams_Pub.create_streams(p_api_version
545 ,p_init_msg_list
546 ,x_return_status
547 ,x_msg_count
548 ,x_msg_data
549 ,l_stmv_rec
550 ,l_selv_tbl
551 ,x_stmv_rec
552 ,x_selv_tbl);
553 IF(IS_DEBUG_PROCEDURE_ON) THEN
554 BEGIN
555 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call Okl_Streams_Pub.create_streams ');
556 END;
557 END IF;
558 -- End of wraper code generated automatically by Debug code generator for Okl_Streams_Pub.create_streams
559 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
560 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
561 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
562 RAISE G_EXCEPTION_ERROR;
563 END IF;
564 END LOOP;
565 EXCEPTION
566 WHEN G_EXCEPTION_ERROR THEN
567 x_return_status := G_RET_STS_ERROR;
568 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
569 p_msg_name => G_UNEXPECTED_ERROR,
570 p_token1 => G_SQLCODE_TOKEN,
571 p_token1_value => SQLCODE,
572 p_token2 => G_SQLERRM_TOKEN,
573 p_token2_value => SQLERRM );
574 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
575 x_return_status := G_RET_STS_UNEXP_ERROR;
576 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
577 p_msg_name => G_UNEXPECTED_ERROR,
578 p_token1 => G_SQLCODE_TOKEN,
579 p_token1_value => SQLCODE,
580 p_token2 => G_SQLERRM_TOKEN,
581 p_token2_value => SQLERRM );
582 WHEN OTHERS THEN
583 x_return_status := G_RET_STS_UNEXP_ERROR;
584 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
585 p_msg_name => G_UNEXPECTED_ERROR,
586 p_token1 => G_SQLCODE_TOKEN,
587 p_token1_value => SQLCODE,
588 p_token2 => G_SQLERRM_TOKEN,
589 p_token2_value => SQLERRM );
590 END GEN_SERV_MAIN_LINE_STRMS;
591 PROCEDURE PROCESS_STREAM_RESULTS(p_api_version IN NUMBER
592 ,p_init_msg_list IN VARCHAR2
593 ,p_transaction_number IN NUMBER
594 ,x_return_status OUT NOCOPY VARCHAR2
595 ,x_msg_count OUT NOCOPY NUMBER
596 ,x_msg_data OUT NOCOPY VARCHAR2)
597 IS
598 l_api_name CONSTANT VARCHAR2(40) := 'PROCESS_STREAM_RESULTS';
599 l_api_version CONSTANT NUMBER := 1.0;
600 l_row_count NUMBER;
601 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
602 l_stmv_rec stmv_rec_type;
603 l_selv_tbl selv_tbl_type;
604 lx_selv_tbl selv_tbl_type;
605 x_stmv_rec stmv_rec_type;
606
607 x_selv_tbl selv_tbl_type;
608 dummy_selv_tbl selv_tbl_type;
609 l_khr_yields_rec Okl_la_Stream_Pvt.yields_rec_type;
610 l_khr_id VARCHAR2(240);
611 l_strm_type_id OKL_STRM_TYPE_V.ID%TYPE;
612 i NUMBER := 0;
613 j NUMBER := 0;
614 p_line_number NUMBER := 1;
615 p_first_line_number NUMBER := 1;
616 l_yield_data_flag NUMBER := 0;
617 lp_sifv_rec OKL_STREAM_INTERFACES_PUB.SIFV_REC_TYPE;
618 lx_sifv_rec OKL_STREAM_INTERFACES_PUB.SIFV_REC_TYPE;
619 l_sirv_rec sirv_rec_type;
620 p_sirv_rec sirv_rec_type;
621 l_no_data_found BOOLEAN := FALSE;
622 l_exception_data_found BOOLEAN := FALSE;
623 l_comments VARCHAR2(4000) := NULL;
624 l_msg_text fnd_new_messages.MESSAGE_TEXT%TYPE;
625 l_error_message_line VARCHAR2(4000) := NULL;
626 l_error_message_tbl LOG_MSG_TBL_TYPE;
627 l_message_count NUMBER := 0;
628 -- l_formatted_amount NUMBER := 0;
629 p_deal_type VARCHAR2(30);
630 l_security_deposit_amt NUMBER;
631
632 x_pre_tax_irr NUMBER; --smahapat bugfix# 2790695
633
634 CURSOR stream_data_csr(p_trx_number NUMBER)
635 IS
636 -- contract level bare minimum streams
637 SELECT
638 STREAM_TYPE.CODE,
639 RETURN_STREAMS.SEQUENCE_NUMBER,
640 RETURN_STREAMS.SRE_DATE,
641 RETURN_STREAMS.AMOUNT,
642 RETURN_STREAMS.INDEX_NUMBER ASSET_INDEX_NUMBER,
643 STREAM_TYPE.ID stream_type_id,
644 HEADER.KHR_ID,
645 TO_NUMBER(NULL) KLE_ID
646 FROM
647 OKL_SIF_RET_STRMS RETURN_STREAMS,
648 OKL_SIF_RETS RETURN_HEADER,
649 OKL_STRM_TYPE_B STREAM_TYPE,
650 OKL_STREAM_INTERFACES HEADER
651 WHERE
652 RETURN_HEADER.id = RETURN_STREAMS.sir_id
653 AND RETURN_HEADER.transaction_number =p_trx_number
654 AND RETURN_STREAMS.stream_type_name = STREAM_TYPE.CODE
655 AND HEADER.transaction_number = p_trx_number
656 AND HEADER.transaction_number = RETURN_HEADER.transaction_number
657 AND RETURN_STREAMS.index_number IS NULL
658 AND SYSDATE BETWEEN STREAM_TYPE.START_DATE AND NVL(STREAM_TYPE.END_DATE, SYSDATE)
659 UNION ALL
660 -- contract level fees
661 SELECT distinct
662 STREAM_TYPE.CODE,
663 RETURN_STREAMS.SEQUENCE_NUMBER,
664 RETURN_STREAMS.SRE_DATE,
665 RETURN_STREAMS.AMOUNT,
666 RETURN_STREAMS.INDEX_NUMBER ASSET_INDEX_NUMBER,
667 STREAM_TYPE.ID stream_type_id,
668 HEADER.KHR_ID,
669 -- added akjain 06-13-2002
670 -- select kle_fee_id as well from the OKL_SIF_FEES table
671 FEES.KLE_ID KLE_ID
672 -- TO_NUMBER(NULL)
673 FROM
674 OKL_STRM_TYPE_B STREAM_TYPE,
675 OKL_SIF_RET_STRMS RETURN_STREAMS,
676 OKL_SIF_RETS RETURN_HEADER,
677 OKL_STREAM_INTERFACES HEADER,
678 -- OKL_SIF_LINES LINES,
679 OKL_SIF_FEES FEES
680 WHERE
681 RETURN_HEADER.transaction_number = p_trx_number
682 AND
683 RETURN_HEADER.id = RETURN_STREAMS.sir_id
684 AND
685 RETURN_STREAMS.stream_type_name = STREAM_TYPE.CODE
686 AND RETURN_STREAMS.index_number = FEES.fee_index_number
687 AND
688 FEES.SIL_ID IS NULL
689 --Modified by kthiruva on 05-May-2005 for the backporting of mainline bug 4294425
690 --Start of Changes
691 --AND FEES.DESCRIPTION = RETURN_STREAMS.STREAM_TYPE_NAME
692 --End of Changes
693 --srsreeni Bug 5890437 start
694 -- added for bug # 2498794
695 -- AND (FEES.LEVEL_INDEX_NUMBER = 0 OR (FEES.LEVEL_INDEX_NUMBER IS NULL OR FEES.SFE_TYPE = 'SFO'))
696 --srsreeni Bug 5890437 end
697 AND
698 FEES.sif_id = HEADER.id
699 AND
700 HEADER.transaction_number = p_trx_number
701 AND HEADER.transaction_number = RETURN_HEADER.transaction_number
702 AND SYSDATE BETWEEN STREAM_TYPE.START_DATE AND NVL(STREAM_TYPE.END_DATE, SYSDATE)
703 -- Added by kthiruva on 05-May-2005 for the backporting of mainline Bug 4294425
704 -- Start of Changes
705 AND EXISTS(
706 SELECT 1
707 FROM
708 OKL_SIF_STREAM_TYPES REQUESTED_STREAMS
709 WHERE
710 REQUESTED_STREAMS.sfe_id = fees.id
711 AND REQUESTED_STREAMS.sil_id is NULL
712 AND stream_type.id = REQUESTED_STREAMS.sty_id
713 AND HEADER.ID = REQUESTED_STREAMS.sif_id ) --dkagrawa added for bug# 4638281
714 --End of Changes
715 UNION ALL
716 -- asset level streams
717 SELECT distinct
718 STREAM_TYPE.CODE,
719 RETURN_STREAMS.SEQUENCE_NUMBER,
720 RETURN_STREAMS.SRE_DATE,
721 RETURN_STREAMS.AMOUNT,
722 RETURN_STREAMS.INDEX_NUMBER ASSET_INDEX_NUMBER,
723 STREAM_TYPE.ID stream_type_id,
724 HEADER.KHR_ID,
725
726 LINES.KLE_ID KLE_ID
727 FROM
728 OKL_STRM_TYPE_B STREAM_TYPE,
729 OKL_SIF_RET_STRMS RETURN_STREAMS,
730 OKL_SIF_RETS RETURN_HEADER,
731 OKL_STREAM_INTERFACES HEADER,
732 OKL_SIF_LINES LINES,
733 OKL_SIF_FEES FEES
734 WHERE
735 RETURN_HEADER.transaction_number = p_trx_number
736 AND
737 RETURN_HEADER.id = RETURN_STREAMS.sir_id
738 --Modified by kthiruva on 12-May-2005 for Streams Performance
739 --Bug 4346646 - Start of changes
740 AND FEES.DESCRIPTION = STREAM_TYPE.CODE
741 --Bug 4346646 - End of Changes
742 AND RETURN_STREAMS.index_number = FEES.fee_index_number
743 AND FEES.DESCRIPTION = RETURN_STREAMS.STREAM_TYPE_NAME
744 AND
745 FEES.SIL_ID = LINES.ID
746 AND
747 HEADER.transaction_number = p_trx_number
748 AND HEADER.transaction_number = RETURN_HEADER.transaction_number
749 AND
750 LINES.SIF_ID = HEADER.ID
751 AND SYSDATE BETWEEN STREAM_TYPE.START_DATE AND NVL(STREAM_TYPE.END_DATE, SYSDATE)
752 AND EXISTS(
753 SELECT 1
754 FROM
755 OKL_SIF_STREAM_TYPES REQUESTED_STREAMS
756 WHERE
757 REQUESTED_STREAMS.sfe_id = fees.id
758 AND stream_type.id = REQUESTED_STREAMS.sty_id
759 AND HEADER.ID = REQUESTED_STREAMS.sif_id
760 AND ( REQUESTED_STREAMS.sil_id IS NULL OR LINES.ID = REQUESTED_STREAMS.sil_id )
761 )
762 -- added for bare Asset level streams
763 UNION ALL
764 SELECT distinct
765 STREAM_TYPE.CODE,
766 RETURN_STREAMS.SEQUENCE_NUMBER,
767 RETURN_STREAMS.SRE_DATE,
768 RETURN_STREAMS.AMOUNT,
769 RETURN_STREAMS.INDEX_NUMBER ASSET_INDEX_NUMBER,
770 STREAM_TYPE.ID stream_type_id,
771 HEADER.KHR_ID,
772 LINES.KLE_ID KLE_ID
773 FROM
774 OKL_STRM_TYPE_B STREAM_TYPE,
775 OKL_SIF_RET_STRMS RETURN_STREAMS,
776 OKL_SIF_RETS RETURN_HEADER,
777 OKL_STREAM_INTERFACES HEADER,
778 OKL_SIF_LINES LINES
779 WHERE
780 RETURN_HEADER.transaction_number = p_trx_number
781 AND
782 HEADER.transaction_number = p_trx_number
783 AND HEADER.transaction_number = RETURN_HEADER.transaction_number
784 AND
785 LINES.SIF_ID = HEADER.ID
786 AND
787 RETURN_HEADER.id = RETURN_STREAMS.sir_id
788 AND
789 RETURN_STREAMS.stream_type_name = STREAM_TYPE.CODE
790 --Added by RGOOTY for bug 9004849
791 AND
792 STREAM_TYPE.STREAM_TYPE_PURPOSE <> 'ESTIMATED_PROPERTY_TAX'
793 --end RGOOTY
794 AND
795 RETURN_STREAMS.index_number = LINES.index_number
796 AND
797 SYSDATE BETWEEN STREAM_TYPE.START_DATE AND NVL(STREAM_TYPE.END_DATE, SYSDATE)
798 and exists(
799 select 1
800 from
801 OKL_SIF_STREAM_TYPES REQUESTED_STREAMS
802 where
803 REQUESTED_STREAMS.sil_id = lines.id
804 and REQUESTED_STREAMS.sfe_id is NULL
805 and stream_type.id = REQUESTED_STREAMS.sty_id)
806 --Added by RGOOTY for bug 9004849
807 --Estimated Property Tax streams
808 UNION ALL
809 SELECT distinct
810 STREAM_TYPE.CODE,
811 RETURN_STREAMS.SEQUENCE_NUMBER,
812 RETURN_STREAMS.SRE_DATE,
813 RETURN_STREAMS.AMOUNT,
814 LINES.INDEX_NUMBER ASSET_INDEX_NUMBER,
815 STREAM_TYPE.ID stream_type_id,
816 HEADER.KHR_ID,
817 LINES.KLE_ID KLE_ID
818 FROM
819 OKL_STRM_TYPE_B STREAM_TYPE,
820 OKL_SIF_RET_STRMS RETURN_STREAMS,
821 OKL_SIF_RETS RETURN_HEADER,
822 OKL_STREAM_INTERFACES HEADER,
823 OKL_SIF_LINES LINES,
824 OKL_SIF_FEES FEES
825 WHERE
826 RETURN_HEADER.transaction_number = p_trx_number
827 AND
828 HEADER.transaction_number = p_trx_number
829 AND HEADER.transaction_number = RETURN_HEADER.transaction_number
830 AND
831 LINES.SIF_ID = HEADER.ID
832 AND
833 RETURN_HEADER.id = RETURN_STREAMS.sir_id
834 AND
835 RETURN_STREAMS.stream_type_name = STREAM_TYPE.CODE
836 AND
837 STREAM_TYPE.STREAM_TYPE_PURPOSE = 'ESTIMATED_PROPERTY_TAX'
838 AND
839 RETURN_STREAMS.index_number = FEES.FEE_index_number
840 AND
841 FEES.SIL_ID = LINES.ID
842 AND
843 STREAM_TYPE.STREAM_TYPE_PURPOSE = FEES.DESCRIPTION
844 AND
845 SYSDATE BETWEEN STREAM_TYPE.START_DATE AND NVL(STREAM_TYPE.END_DATE, SYSDATE)
846 and exists(
847 select 1
848 from
849 OKL_SIF_STREAM_TYPES REQUESTED_STREAMS
850 where
851 REQUESTED_STREAMS.sil_id = lines.id
852 and REQUESTED_STREAMS.sfe_id is NULL
853 and stream_type.id = REQUESTED_STREAMS.sty_id)
854 --end RGOOTY for bug 9004849
855 -- Begin mansrini for Bug 5111058 (Fwd port Bug 5061024)
856 -- This query will pick the primary streams of purpose Subsidy
857 UNION ALL
858 SELECT distinct
859 STREAM_TYPE.CODE,
860 RETURN_STREAMS.SEQUENCE_NUMBER,
861 RETURN_STREAMS.SRE_DATE,
862 RETURN_STREAMS.AMOUNT,
863 RETURN_STREAMS.INDEX_NUMBER ASSET_INDEX_NUMBER,
864 STREAM_TYPE.ID stream_type_id,
865 HEADER.KHR_ID,
866
867 LINES.KLE_ID KLE_ID
868 FROM
869 OKL_STRM_TYPE_B STREAM_TYPE,
870 OKL_SIF_RET_STRMS RETURN_STREAMS,
871 OKL_SIF_RETS RETURN_HEADER,
872 OKL_STREAM_INTERFACES HEADER,
873 OKL_SIF_LINES LINES,
874 OKL_SIF_FEES FEES
875 WHERE
876 RETURN_HEADER.transaction_number = p_trx_number
877 AND
878 RETURN_HEADER.id = RETURN_STREAMS.sir_id
879 AND
880 RETURN_STREAMS.index_number = FEES.fee_index_number
881 AND
882 FEES.SIL_ID = LINES.ID
883 AND
884 HEADER.transaction_number = RETURN_HEADER.transaction_number
885 AND
886 LINES.SIF_ID = HEADER.ID
887 AND
888 STREAM_TYPE.CODE = RETURN_STREAMS.STREAM_TYPE_NAME
889 AND
890 STREAM_TYPE.STREAM_TYPE_PURPOSE = 'SUBSIDY'
891 AND
892 SYSDATE BETWEEN STREAM_TYPE.START_DATE AND NVL(STREAM_TYPE.END_DATE, SYSDATE)
893 AND EXISTS(
894 SELECT 1
895 FROM
896 OKL_SIF_STREAM_TYPES REQUESTED_STREAMS
897 WHERE
898 REQUESTED_STREAMS.sfe_id = fees.id
899 AND stream_type.id = REQUESTED_STREAMS.sty_id
900 AND HEADER.ID = REQUESTED_STREAMS.sif_id
901 AND (REQUESTED_STREAMS.sil_id IS NULL OR LINES.ID = REQUESTED_STREAMS.sil_id)
902 )
903 -- end mansrini for Bug 5111058 (Fwd port Bug 5061024)
904
905 ORDER BY stream_type_id, ASSET_INDEX_NUMBER, kle_id;
906 -- added order by to resolve Asset Mapping
907 -- modified order by for Bug # 2403426
908 CURSOR yield_data_csr(p_trx_number NUMBER) IS
909 SELECT
910 YIELD_NAME,
911 EFFECTIVE_PRE_TAX_YIELD,
912 EFFECTIVE_AFTER_TAX_YIELD,
913 NOMINAL_PRE_TAX_YIELD,
914 NOMINAL_AFTER_TAX_YIELD,
915 IMPLICIT_INTEREST_RATE
916 FROM
917 OKL_SIF_RETS
918 WHERE
919 transaction_number = p_trx_number;
920 -- define cursor to check any Exceptions in the Inbound Interface Tables
921 CURSOR exception_data_csr(p_trx_number NUMBER)
922 IS
923 SELECT
924 SRMB.ID,
925 SRMB.ERROR_CODE,
926 SRMB.ERROR_MESSAGE,
927 SRMB.TAG_NAME,
928 SRMB.TAG_ATTRIBUTE_NAME,
929 SRMB.TAG_ATTRIBUTE_VALUE,
930 SRMB.DESCRIPTION
931 FROM
932 OKL_SIF_RETS SIRB,
933 OKL_SIF_RET_ERRORS SRMB
934 WHERE
935 SIRB.TRANSACTION_NUMBER = p_trx_number
936 AND
937 SIRB.ID = SRMB.SIR_ID;
938 -- cursor to update transaction status in the OKL_STREAM_INTERFACES table
939 CURSOR sif_data_csr (p_transaction_number IN NUMBER) IS
940 SELECT
941
942 ID,
943 ORP_CODE,
944 LOG_FILE,
945 SECURITY_DEPOSIT_AMOUNT
946 FROM Okl_Stream_Interfaces
947 WHERE okl_stream_interfaces.transaction_number = p_transaction_number;
948 CURSOR sirv_data_csr (p_trnsaction_numner IN NUMBER) IS
949 SELECT
950 ID,
951 TRANSACTION_NUMBER,
952 SRT_CODE,
953 EFFECTIVE_PRE_TAX_YIELD,
954 YIELD_NAME,
955 INDEX_NUMBER,
956 EFFECTIVE_AFTER_TAX_YIELD,
957 NOMINAL_PRE_TAX_YIELD,
958 NOMINAL_AFTER_TAX_YIELD,
959 STREAM_INTERFACE_ATTRIBUTE01,
960 STREAM_INTERFACE_ATTRIBUTE02,
961 STREAM_INTERFACE_ATTRIBUTE03,
962 STREAM_INTERFACE_ATTRIBUTE04,
963 STREAM_INTERFACE_ATTRIBUTE05,
964 STREAM_INTERFACE_ATTRIBUTE06,
965 STREAM_INTERFACE_ATTRIBUTE07,
966 STREAM_INTERFACE_ATTRIBUTE08,
967 STREAM_INTERFACE_ATTRIBUTE09,
968 STREAM_INTERFACE_ATTRIBUTE10,
969 STREAM_INTERFACE_ATTRIBUTE11,
970 STREAM_INTERFACE_ATTRIBUTE12,
971 STREAM_INTERFACE_ATTRIBUTE13,
972 STREAM_INTERFACE_ATTRIBUTE14,
973 STREAM_INTERFACE_ATTRIBUTE15,
974 OBJECT_VERSION_NUMBER,
975 CREATED_BY,
976 LAST_UPDATED_BY,
977 CREATION_DATE,
978 LAST_UPDATE_DATE,
979 LAST_UPDATE_LOGIN,
980 IMPLICIT_INTEREST_RATE,
981 DATE_PROCESSED,
982 REQUEST_ID,
983 PROGRAM_APPLICATION_ID,
984 PROGRAM_ID,
985 PROGRAM_UPDATE_DATE
986 FROM Okl_Sif_Rets
987 WHERE okl_sif_rets.transaction_number = p_trnsaction_numner;
988 -- smahapat 11/10/02 multi-gaap -- addition
989 CURSOR reporting_streams_csr(p_trx_number NUMBER) IS
990 SELECT purpose_code
991 FROM okl_stream_interfaces
992 WHERE transaction_number = p_trx_number;
993
994 CURSOR reporting_product_csr(p_trx_number NUMBER) IS
995 SELECT c.reporting_pdt_id
996 FROM okl_k_headers a, okl_stream_interfaces b, okl_products_v c
997 WHERE b.transaction_number = p_trx_number
998 AND a.id = b.khr_id
999 AND c.id = a.pdt_id;
1000 reporting_product_rec reporting_product_csr%ROWTYPE;
1001
1002 lx_pdt_parameter_rec pdt_param_rec_type;
1003 l_pdtv_rec pdtv_rec_type;
1004 lx_no_data_found BOOLEAN;
1005
1006 CURSOR chk_for_subsidy_csr(p_trx_number NUMBER) IS
1007 SELECT
1008 '1'
1009 FROM
1010 OKL_SIF_FEES SFEB,
1011 OKL_STREAM_INTERFACES SIFB
1012 WHERE
1013 SIFB.ID = SFEB.SIF_ID AND
1014 transaction_number = p_trx_number AND
1015 SFE_TYPE = 'SFB' ORDER BY FEE_INDEX_NUMBER;
1016
1017 l_reporting_streams VARCHAR2(1) := OKL_API.G_FALSE;
1018 l_process_yn VARCHAR2(1) := OKL_API.G_TRUE;
1019 l_chk_subsidy VARCHAR2(1) := 'x';
1020 -- smahapat addition end
1021 yield_csr yield_data_csr%ROWTYPE;
1022 stream_csr stream_data_csr%ROWTYPE;
1023 --first_stream_rec stream_data_csr%ROWTYPE;
1024 exception_data exception_data_csr%ROWTYPE;
1025
1026 l_msg_index_out NUMBER;
1027 -- Bug 4196515: Start
1028 CURSOR get_org_id(p_chr_id okc_k_headers_b.id%TYPE)
1029 IS
1030 SELECT authoring_org_id,
1031 currency_code
1032 FROM okc_k_headers_b
1033 WHERE id = p_chr_id;
1034
1035 CURSOR get_precision(p_currency_code OKC_K_HEADERS_B.CURRENCY_CODE%TYPE)
1036 IS
1037 SELECT PRECISION
1038 FROM fnd_currencies_vl
1039 WHERE currency_code = p_currency_code
1040 AND enabled_flag = 'Y'
1041 AND NVL(start_date_active, SYSDATE) <= SYSDATE
1042 AND NVL(end_date_active, SYSDATE) >= SYSDATE;
1043
1044 CURSOR get_rounding_rule
1045 IS
1046 SELECT stm_rounding_rule
1047 FROM OKL_SYS_ACCT_OPTS;
1048
1049 CURSOR get_rnd_diff_lookup(p_lookup_type fnd_lookups.lookup_type%TYPE)
1050 IS
1051 SELECT b.stm_apply_rounding_difference
1052 FROM fnd_lookups a,
1053 OKL_SYS_ACCT_OPTS b
1054 WHERE a.lookup_type = p_lookup_type
1055 AND a.lookup_code = b.stm_apply_rounding_difference;
1056
1057 --Added by KTHIRUVA for ESG Performance Imporvement on 03-May-2005
1058 --Bug 4346646 - Start of Changes
1059 CURSOR SERVICE_LINES_EXIST(P_KHR_ID OKC_K_HEADERS_B.ID%TYPE)
1060 IS
1061 SELECT 1
1062 FROM OKC_K_HEADERS_B KHR,
1063 OKC_K_LINES_B CLE,
1064 OKC_LINE_STYLES_B LSE
1065 WHERE CLE.CHR_ID = KHR.ID
1066 AND KHR.ID = p_khr_id
1067 AND CLE.LSE_ID = LSE.ID
1068 AND LSE.LTY_CODE IN ('SOLD_SERVICE','LINK_SERV_ASSET');
1069
1070 l_service_line_found NUMBER;
1071 --Bug 4346646 - End of Changes
1072
1073 l_org_id okc_k_headers_b.authoring_org_id%TYPE;
1074 l_currency_code okc_k_headers_b.currency_code%TYPE;
1075 l_diff_lookup_code fnd_lookups.lookup_code%TYPE;
1076 l_precision NUMBER;
1077 l_rounding_rule okl_sys_acct_opts.ael_rounding_rule%TYPE;
1078 l_first_rec CHAR := 'T';
1079
1080 G_RND_DIFF_LOOKUP_TYPE CONSTANT fnd_lookups.lookup_type%TYPE := 'OKL_STRM_APPLY_ROUNDING_DIFF';
1081 G_NO_MATCH_REC CONSTANT VARCHAR2(30) := 'OKL_LLA_NO_MATCHING_RECORD';
1082 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
1083 -- Bug 4196515: End
1084
1085 --Added by kthiruva on 15-May-2005 for Streams Performance
1086 --Bug 4346646-Start of Changes
1087 l_stmv_tbl stmv_tbl_type;
1088 x_stmv_tbl stmv_tbl_type;
1089 full_selv_tbl selv_tbl_type;
1090 l_selv_count NUMBER;
1091 k NUMBER;
1092 --Bug 4346646-End of Changes
1093
1094 BEGIN
1095 IF (G_DEBUG_ENABLED = 'Y') THEN
1096 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1097 END IF;
1098 --Added by kthiruva for Debugging
1099 L_DEBUG_ENABLED := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
1100 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1101 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Transaction number is :'||p_transaction_number);
1102 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inside procedure process_stream_results');
1103 END IF;
1104
1105
1106 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1107 p_pkg_name => 'OKL_PROCESS_STREAMS_PVT',
1108 p_init_msg_list => p_init_msg_list,
1109 l_api_version => l_api_version,
1110 p_api_version => p_api_version,
1111 p_api_type => '_PVT',
1112 x_return_status => l_return_status);
1113 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1114 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1115 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1116 RAISE G_EXCEPTION_ERROR;
1117 END IF;
1118
1119 x_return_status := G_RET_STS_SUCCESS;
1120 --Before Processing Stream Results check for any exceptions in the Inbound Interface Tables
1121 FOR exception_data in exception_data_csr(p_transaction_number)
1122 LOOP
1123 IF(l_message_count = 0)
1124 THEN
1125 l_error_message_line := 'REQUEST ID = ' || p_transaction_number || ' TIME PROCESSED = '|| to_char(SYSDATE,'YYYYMMDD HH24MISS');
1126 l_error_message_line := l_error_message_line || G_NEW_LINE;
1127 l_error_message_line := l_error_message_line || 'Errors returned from Pricing Engine :- ';
1128 l_error_message_line := l_error_message_line || G_NEW_LINE;
1129 l_error_message_line := l_error_message_line || 'ERROR CODE :: ' || exception_data.ERROR_CODE;
1130 l_error_message_line := l_error_message_line || G_NEW_LINE;
1131 l_error_message_line := l_error_message_line || 'ERROR MESSAGE :: ' || exception_data.ERROR_MESSAGE;
1132 l_error_message_line := l_error_message_line || G_NEW_LINE;
1133 l_error_message_line := l_error_message_line || 'XML TAG :: ' || exception_data.TAG_NAME ;
1134 l_error_message_line := l_error_message_line || G_NEW_LINE;
1135 l_message_count := l_message_count + 1;
1136 ELSE
1137 l_error_message_line := NULL;
1138 l_error_message_line := l_error_message_line || 'ERROR CODE :: ' || exception_data.ERROR_CODE;
1139 l_error_message_line := l_error_message_line || G_NEW_LINE;
1140 l_error_message_line := l_error_message_line || 'ERROR MESSAGE :: ' || exception_data.ERROR_MESSAGE;
1141 l_error_message_line := l_error_message_line || G_NEW_LINE;
1142 l_error_message_line := l_error_message_line || 'XML TAG :: ' || exception_data.TAG_NAME ;
1143 l_error_message_line := l_error_message_line || G_NEW_LINE;
1144 -- Not used in this release
1145 /*
1146
1147 l_error_message_line := l_error_message_line || 'XML TAG ATTRIBUTE :: ' || exception_data.TAG_ATTRIBUTE_NAME ;
1148 l_error_message_line := l_error_message_line || G_NEW_LINE;
1149 l_error_message_line := l_error_message_line || 'XML TAG ATTRIBUTE VALUE :: ' || exception_data.TAG_ATTRIBUTE_VALUE ;
1150 l_error_message_line := l_error_message_line || G_NEW_LINE;
1151 l_error_message_line := l_error_message_line || 'DETAILS :: ' || exception_data.DESCRIPTION ;
1152 l_error_message_line := l_error_message_line || G_NEW_LINE;
1153 */
1154 l_message_count := l_message_count + 1;
1155 END IF;
1156 l_error_message_tbl(l_message_count) := l_error_message_line;
1157 END LOOP;
1158
1159 IF( l_message_count > 0)
1160 THEN
1161 l_exception_data_found := TRUE;
1162 FND_MESSAGE.SET_NAME ( G_APP_NAME, 'OKL_STREAM_GENERATION_ERROR');
1163 FND_MESSAGE.SET_TOKEN(TOKEN => 'FILE_NAME',
1164 VALUE => 'OKLSTXMLG_' || p_transaction_number || '.log',
1165 TRANSLATE => TRUE);
1166 l_msg_text := FND_MESSAGE.GET;
1167 l_comments := l_msg_text;
1168 l_error_message_tbl(l_message_count + 1) := 'End Errors returned from Pricing Engine' ;
1169
1170 OKL_STREAMS_UTIL.LOG_MESSAGE(p_msgs_tbl => l_error_message_tbl,
1171 p_translate => G_FALSE,
1172 p_file_name => 'OKLSTXMLG_' || p_transaction_number || '.log' ,
1173 x_return_status => l_return_status );
1174 ELSE
1175 l_comments := NULL;
1176 END IF;
1177
1178 FOR reporting_streams_data IN reporting_streams_csr(p_transaction_number)
1179 LOOP
1180 IF (reporting_streams_data.purpose_code = G_PURPOSE_CODE_REPORT)
1181 THEN
1182 l_reporting_streams := OKL_API.G_TRUE;
1183 END IF;
1184 END LOOP;
1185
1186 -- Bug 4196515: Start
1187 -- Making sure that no records are present
1188 i := 0;
1189 l_selv_tbl := dummy_selv_tbl;
1190 l_stmv_rec := NULL;
1191 l_khr_id := NULL;
1192 FOR stream_csr IN stream_data_csr(p_transaction_number)
1193 LOOP
1194 -- Checking whether if this rec is the first rec.
1195 IF( l_first_rec = 'T' )
1196 THEN
1197 l_stmv_rec.sty_id := stream_csr.stream_type_id;
1198 l_stmv_rec.khr_id := stream_csr.khr_id;
1199 l_stmv_rec.kle_id := stream_csr.kle_id;
1200 l_stmv_rec.sgn_code := G_STREAM_GENERATOR;
1201 l_stmv_rec.say_code := G_STREAM_ACTIVITY_WORK;
1202 l_stmv_rec.active_yn := G_STREAM_ACTIVE_YN;
1203 l_stmv_rec.date_working := SYSDATE ;
1204 l_stmv_rec.comments := l_comments ;
1205 IF (l_reporting_streams = OKL_API.G_TRUE)
1206 THEN
1207 l_stmv_rec.purpose_code := G_PURPOSE_CODE_REPORT;
1208 END IF;
1209 l_khr_id := TO_CHAR(stream_csr.khr_id);
1210 l_stmv_rec.transaction_number := p_transaction_number;
1211
1212 --Added by kthiruva on 15-May-2005 for Streams Performance
1213 --Bug 4346646 - Start of Changes
1214 i := i + 1;
1215 l_stmv_tbl(i) := l_stmv_rec ;
1216 --Bug 4346646 - End of Changes
1217
1218 -- Got the First Record, change the Flag
1219 l_first_rec := 'F';
1220 END IF;
1221
1222 IF (l_stmv_rec.sty_id = stream_csr.stream_type_id AND (l_stmv_rec.kle_id IS NULL OR l_stmv_rec.kle_id = stream_csr.kle_id))
1223 THEN
1224 -- populate the child records
1225 j := j + 1;
1226 l_selv_tbl(j).stream_element_date := stream_csr.sre_date;
1227 l_selv_tbl(j).amount := stream_csr.amount;
1228 l_selv_tbl(j).se_line_number := p_line_number;
1229 --Added by kthiruva on 15-May-2005 for Streams Performance
1230 --Bug 4346646 - Start of Changes
1231 l_selv_tbl(j).parent_index := i;
1232 --Bug 4346646 - End of Changes
1233 p_line_number := p_line_number + 1;
1234 ELSE
1235 -- call the insert API for STREAMS
1236 IF(IS_DEBUG_PROCEDURE_ON) THEN
1237 BEGIN
1238 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call Okl_Streams_Pub.create_streams ');
1239 END;
1240 END IF;
1241
1242 --Modified by kthiruva for Streams Performance on 15-May-2005
1243 --Bug 4346646 - Start of Changes
1244
1245 --Obtaining the values of the system options that need to be passed to the rounding call
1246 okl_streams_util.get_acc_options( p_khr_id => l_khr_id,
1247 x_org_id => l_org_id,
1248 x_precision => l_precision,
1249 x_currency_code => l_currency_code,
1250 x_rounding_rule => l_rounding_rule,
1251 x_apply_rnd_diff => l_diff_lookup_code,
1252 x_return_status => x_return_status );
1253
1254 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1255 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1256 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1257 RAISE G_EXCEPTION_ERROR;
1258 END IF;
1259
1260 x_return_status := Okl_Streams_Util.round_streams_amount_esg(p_api_version => p_api_version,
1261 p_init_msg_list => p_init_msg_list,
1262 x_msg_count => x_msg_count,
1263 x_msg_data => x_msg_data,
1264 p_chr_id => l_khr_id,
1265 p_selv_tbl => l_selv_tbl,
1266 x_selv_tbl => lx_selv_tbl,
1267 p_org_id => l_org_id,
1268 p_precision => l_precision,
1269 p_currency_code => l_currency_code,
1270 p_rounding_rule => l_rounding_rule,
1271 p_apply_rnd_diff => l_diff_lookup_code);
1272 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1273 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1274 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1275 RAISE G_EXCEPTION_ERROR;
1276 END IF;
1277
1278 -- This call basically accepts the stream element table returned by the rounding procedure
1279 -- and accumulates all the stream elements in full_selv_tbl that is passed to the create streams call
1280 okl_streams_util.accumulate_strm_elements(p_stm_index_no => null,
1281 p_selv_tbl => lx_selv_tbl,
1282 x_full_selv_tbl => full_selv_tbl,
1283 x_return_status => x_return_status
1284 );
1285
1286 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1287 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1288 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1289 RAISE G_EXCEPTION_ERROR;
1290 END IF;
1291
1292 --Bug 4346646 - End of Changes
1293
1294 IF(IS_DEBUG_PROCEDURE_ON) THEN
1295 BEGIN
1296 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call Okl_Streams_Pub.create_streams ');
1297 END;
1298 END IF;
1299
1300 j := 1 ;
1301 l_selv_tbl := dummy_selv_tbl;
1302 -- populate the header record
1303 l_stmv_rec.sty_id := stream_csr.stream_type_id;
1304 l_stmv_rec.khr_id := stream_csr.khr_id;
1305 l_stmv_rec.kle_id := stream_csr.kle_id;
1306 l_stmv_rec.sgn_code := G_STREAM_GENERATOR;
1307 l_stmv_rec.say_code := G_STREAM_ACTIVITY_WORK;
1308 l_stmv_rec.active_yn := G_STREAM_ACTIVE_YN;
1309 l_stmv_rec.date_working := SYSDATE ;
1310 l_stmv_rec.transaction_number := p_transaction_number;
1311 l_stmv_rec.comments := l_comments;
1312 --Added by kthiruva on 15-May-2005 for Streams Performance
1313 --Bug 4346646 - Start of Changes
1314 i := i + 1;
1315 l_stmv_tbl(i) := l_stmv_rec ;
1316 --Bug 4346646 - End of Changes
1317
1318 -- smahapat 11/10/02 multi-gaap -- addition
1319 IF (l_reporting_streams = OKL_API.G_TRUE)
1320 THEN
1321 l_stmv_rec.purpose_code := G_PURPOSE_CODE_REPORT;
1322 END IF;
1323 -- smahapat addition end
1324 -- populate the first line of this header record
1325 l_selv_tbl(j).stream_element_date := stream_csr.sre_date;
1326 l_selv_tbl(j).amount := stream_csr.amount;
1327 --Added by kthiruva on 15-May-2005 for Streams Performance
1328 --Bug 4346646 - Start of Changes
1329 l_selv_tbl(j).parent_index := i;
1330 --Bug 4346646 - End of Changes
1331 l_selv_tbl(j).se_line_number := p_first_line_number;
1332 p_line_number := p_first_line_number + 1;
1333 END IF;
1334 END LOOP;
1335 -- insert the last record from the CURSOR
1336 -- Start of wraper code generated automatically by Debug code generator for Okl_Streams_Pub.create_streams
1337 IF( l_khr_id IS NOT NULL )
1338 THEN
1339 IF(IS_DEBUG_PROCEDURE_ON) THEN
1340 BEGIN
1341 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call Okl_Streams_Pub.create_streams ');
1342 END;
1343 END IF;
1344
1345 --Modified by kthiruva on 15-May-2005 for Streams Performance
1346 --Bug 4346646 - Start of Changes
1347
1348 --Added by kthiruva for Debug Logging
1349 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1350 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to Okl_Streams_Util.round_streams_amount_esg');
1351 END IF;
1352 --Making the round amount call for the stream elements of the last stream header
1353 x_return_status := Okl_Streams_Util.round_streams_amount_esg(p_api_version => p_api_version,
1354 p_init_msg_list => p_init_msg_list,
1355 x_msg_count => x_msg_count,
1356 x_msg_data => x_msg_data,
1357 p_chr_id => l_khr_id,
1358 p_selv_tbl => l_selv_tbl,
1359 x_selv_tbl => lx_selv_tbl,
1360 p_org_id => l_org_id,
1361 p_precision => l_precision,
1362 p_currency_code => l_currency_code,
1363 p_rounding_rule => l_rounding_rule,
1364 p_apply_rnd_diff => l_diff_lookup_code);
1365
1366 --Added by kthiruva for Debug Logging
1367 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1368 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Streams_Util.round_streams_amount_esg, return status is :'||x_return_status);
1369 END IF;
1370
1371 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1372 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1373 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1374 RAISE G_EXCEPTION_ERROR;
1375 END IF;
1376
1377 --Accumulating the stream elements of the last stream header to the existing full_selv_tbl
1378 okl_streams_util.accumulate_strm_elements(p_stm_index_no => null,
1379 p_selv_tbl => lx_selv_tbl,
1380 x_full_selv_tbl => full_selv_tbl,
1381 x_return_status => x_return_status
1382 );
1383 --Added by kthiruva for Debug Logging
1384 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1385 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Streams_Util.accumulate_strm_elements, return status is :'||x_return_status);
1386 END IF;
1387
1388 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1389 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1390 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1391 RAISE G_EXCEPTION_ERROR;
1392 END IF;
1393
1394 --Calling the new method that accepts a table of stream headers and a table of stream elements
1395 -- Bulk insert calls are made for both the stream headers and stream elements.
1396 Okl_Streams_Pub.create_streams_perf(p_api_version
1397 ,p_init_msg_list
1398 ,x_return_status
1399 ,x_msg_count
1400 ,x_msg_data
1401 ,l_stmv_tbl -- arajagop changed
1402 ,full_selv_tbl --satya changed 10/17/03
1403 ,x_stmv_tbl
1404 ,x_selv_tbl);
1405 --Bug 4346646 - End of Changes
1406 --Added by kthiruva for Debug Logging
1407 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1408 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Streams_Pub.create_streams_perf, return status is :'||x_return_status);
1409 END IF;
1410
1411
1412 IF(IS_DEBUG_PROCEDURE_ON) THEN
1413 BEGIN
1414 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call Okl_Streams_Pub.create_streams ');
1415 END;
1416 END IF;
1417 -- End of wraper code generated automatically by Debug code generator for Okl_Streams_Pub.create_streams
1418 IF (x_return_status = G_RET_STS_UNEXP_ERROR)
1419 THEN
1420 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1421 ELSIF (x_return_status = G_RET_STS_ERROR)
1422 THEN
1423 RAISE G_EXCEPTION_ERROR;
1424 END IF;
1425
1426 OPEN chk_for_subsidy_csr(p_transaction_number);
1427 FETCH chk_for_subsidy_csr INTO l_chk_subsidy;
1428 CLOSE chk_for_subsidy_csr;
1429 --Added by kthiruva for Debug Logging
1430 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1431 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Value of l_chk_subsidy is :'||l_chk_subsidy);
1432 END IF;
1433
1434 -- TODO : check what yield has to be assigned
1435 -- Update Yields at contract header
1436 FOR yield_csr IN yield_data_csr(p_transaction_number)
1437 LOOP
1438 l_yield_data_flag := 1;
1439 -- assign the value of implicit_interest_rate :Only exactly 1 record will have value of implicit_interest_rate
1440 IF (l_chk_subsidy = '1')
1441 THEN
1442 -- YIELD NAMES ARE HARD CODED HERE , NEED TO VERIFY WITH THE RETURNING YIELDS NAMES FROM ST
1443 IF(yield_csr.yield_name = 'PTIRRWS' )
1444 THEN
1445 l_khr_yields_rec.PRE_TAX_IRR := yield_csr.effective_pre_tax_yield;
1446 IF(yield_csr.implicit_interest_rate IS NOT NULL)
1447 THEN
1448 l_khr_yields_rec.implicit_interest_rate := yield_csr.implicit_interest_rate;
1449 END IF;
1450 ELSIF(yield_csr.yield_name = 'NATWS')
1451 THEN
1452 l_khr_yields_rec.AFTER_TAX_IRR := yield_csr.effective_pre_tax_yield;
1453 ELSIF(yield_csr.yield_name = 'BookingWS')
1454 THEN
1455 l_khr_yields_rec.AFTER_TAX_YIELD := yield_csr.effective_pre_tax_yield;
1456 l_khr_yields_rec.PRE_TAX_YIELD := NULL;
1457 ELSIF(yield_csr.yield_name = 'PTIRR' )
1458 THEN
1459 l_khr_yields_rec.SUB_PRE_TAX_IRR := yield_csr.effective_pre_tax_yield;
1460 IF(yield_csr.implicit_interest_rate IS NOT NULL)
1461 THEN
1462 l_khr_yields_rec.sub_impl_interest_rate := yield_csr.IMPLICIT_INTEREST_RATE;
1463 END IF;
1464 ELSIF(yield_csr.yield_name = 'NAT')
1465 THEN
1466 l_khr_yields_rec.SUB_AFTER_TAX_IRR := yield_csr.effective_pre_tax_yield;
1467 ELSIF(yield_csr.yield_name = 'Booking')
1468 THEN
1469 l_khr_yields_rec.SUB_AFTER_TAX_YIELD := yield_csr.effective_pre_tax_yield;
1470 l_khr_yields_rec.PRE_TAX_YIELD := NULL;
1471 END IF;
1472 ELSE
1473 IF(yield_csr.implicit_interest_rate IS NOT NULL)
1474 THEN
1475 l_khr_yields_rec.implicit_interest_rate := yield_csr.implicit_interest_rate;
1476 END IF;
1477 -- YIELD NAMES ARE HARD CODED HERE , NEED TO VERIFY WITH THE RETURNING YIELDS NAMES FROM ST
1478 IF(yield_csr.yield_name = 'PTIRR' )
1479 THEN
1480 l_khr_yields_rec.PRE_TAX_IRR := yield_csr.effective_pre_tax_yield;
1481 l_khr_yields_rec.implicit_interest_rate := yield_csr.implicit_interest_rate;
1482 ELSIF(yield_csr.yield_name = 'NAT')
1483 THEN
1484 l_khr_yields_rec.AFTER_TAX_IRR := yield_csr.effective_pre_tax_yield;
1485 ELSIF(yield_csr.yield_name = 'Booking')
1486 THEN
1487 l_khr_yields_rec.AFTER_TAX_YIELD := yield_csr.effective_pre_tax_yield;
1488 l_khr_yields_rec.PRE_TAX_YIELD := NULL;
1489 END IF;
1490 END IF;
1491 END LOOP;
1492 -- generate streams for the SERVICE LINES
1493 -- Start of wraper code generated automatically by Debug code generator for OKL_PROCESS_STREAMS_PVT.GEN_SERV_MAIN_LINE_STRMS
1494 IF(IS_DEBUG_PROCEDURE_ON) THEN
1495 BEGIN
1496 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_PROCESS_STREAMS_PVT.GEN_SERV_MAIN_LINE_STRMS ');
1497 END;
1498 END IF;
1499
1500 --start smahapat bugfix# 2790695
1501 --Generate accrual streams for service lines
1502 lx_pdt_parameter_rec := NULL;
1503 IF (l_reporting_streams = OKL_API.G_TRUE) THEN
1504 OPEN reporting_product_csr(p_transaction_number);
1505 FETCH reporting_product_csr INTO reporting_product_rec;
1506 CLOSE reporting_product_csr;
1507 --Added by kthiruva for Debug Logging
1508 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1509 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Reporting Product present.Calling OKL_SETUPPRODUCTS_PVT.Getpdt_parameters');
1510 END IF;
1511
1512 l_pdtv_rec.id := reporting_product_rec.reporting_pdt_id;
1513 OKL_SETUPPRODUCTS_PVT.Getpdt_parameters(
1514 p_api_version => p_api_version,
1515 p_init_msg_list => p_init_msg_list,
1516 x_return_status => x_return_status,
1517 x_msg_count => x_msg_count,
1518 x_msg_data => x_msg_data,
1519 p_pdtv_rec => l_pdtv_rec,
1520 x_no_data_found => lx_no_data_found,
1521 p_pdt_parameter_rec => lx_pdt_parameter_rec);
1522
1523 --Added by kthiruva for Debug Logging
1524 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1525 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to OKL_SETUPPRODUCTS_PVT.Getpdt_parameters, return status is :'|| x_return_status);
1526 END IF;
1527 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1528 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1529 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1530 RAISE OKL_API.G_EXCEPTION_ERROR;
1531 END IF;
1532 END IF;
1533
1534 --Added by kthiruva for ESG Performance Improvement on 03-May-2005
1535 --Bug 4346646 - Start of Changes
1536 --This cursordetermines whether there are any Service Lines defined on the contract.
1537 OPEN SERVICE_LINES_EXIST(l_khr_id);
1538 FETCH SERVICE_LINES_EXIST INTO l_service_line_found;
1539 --Make the call to the ISG API to generate Service Line Streams only if service lines
1540 --are defined on the contract
1541 IF SERVICE_LINES_EXIST%FOUND THEN
1542 --Bug 4346646 - End of Changes
1543 --Added by kthiruva for Debug Logging
1544 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1545 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Service Lines Exist');
1546 END IF;
1547 okl_stream_generator_pub.GENERATE_STREAMS(
1548 p_api_version => p_api_version,
1549 p_init_msg_list => p_init_msg_list,
1550 p_khr_id => l_khr_id,
1551 p_compute_irr => OKL_API.G_FALSE,
1552 p_generation_type => 'SERVICE_LINES',
1553 p_reporting_book_class => lx_pdt_parameter_rec.deal_type,
1554 x_pre_tax_irr => x_pre_tax_irr,
1555 x_return_status => x_return_status,
1556 x_msg_count => x_msg_count,
1557 x_msg_data => x_msg_data);
1558 --Added by kthiruva for Debug Logging
1559 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1560 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to okl_stream_generator_pub.GENERATE_STREAMS, return status is :'|| x_return_status);
1561 END IF;
1562
1563 IF (x_return_status = G_RET_STS_UNEXP_ERROR)
1564 THEN
1565 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1566 ELSIF (x_return_status = G_RET_STS_ERROR)
1567 THEN
1568 RAISE G_EXCEPTION_ERROR;
1569 END IF;
1570 --end smahapat bugfix# 2790695
1571 --Bug 4346646 - Start of Changes
1572 END IF;
1573 CLOSE SERVICE_LINES_EXIST;
1574 --Bug 4346646 - End of Changes
1575 --Added by kthiruva for Debug Logging
1576 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1577 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to generating Residual Value streams');
1578 END IF;
1579 --Generate accrual streams for residual streams internally
1580 okl_stream_generator_pub.GENERATE_STREAMS(
1581 p_api_version => p_api_version,
1582 p_init_msg_list => p_init_msg_list,
1583 p_khr_id => l_khr_id,
1584 p_compute_irr => OKL_API.G_FALSE,
1585 p_generation_type => 'RESIDUAL VALUE',
1586 p_reporting_book_class => lx_pdt_parameter_rec.deal_type,
1587 x_pre_tax_irr => x_pre_tax_irr,
1588 x_return_status => x_return_status,
1589 x_msg_count => x_msg_count,
1590 x_msg_data => x_msg_data);
1591 --Added by kthiruva for Debug Logging
1592 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1593 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to okl_stream_generator_pub.GENERATE_STREAMS, return status is :'|| x_return_status);
1594 END IF;
1595
1596 IF (x_return_status = G_RET_STS_UNEXP_ERROR)
1597 THEN
1598 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1599 ELSIF (x_return_status = G_RET_STS_ERROR)
1600 THEN
1601 RAISE G_EXCEPTION_ERROR;
1602 END IF;
1603 --end Generate accrual streams for residual streams internally
1604
1605 FOR sif_data in sif_data_csr(p_transaction_number)
1606 LOOP
1607 lp_sifv_rec.id := sif_data.id;
1608 lp_sifv_rec.ORP_CODE := sif_data.ORP_CODE;
1609 l_security_deposit_amt := sif_data.SECURITY_DEPOSIT_AMOUNT;
1610 END LOOP;
1611 -- generate SECURITY DEPOSIT Streams
1612 IF(l_security_deposit_amt IS NOT NULL)
1613 THEN
1614 -- generate streams for the SECURITY DEPOSIT FEES
1615 -- Start of wraper code generated automatically by Debug code generator for OKL_PROCESS_STREAMS_PVT.GEN_SEC_DEP_STRMS
1616 IF(IS_DEBUG_PROCEDURE_ON) THEN
1617 BEGIN
1618 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_PROCESS_STREAMS_PVT.GEN_SEC_DEP_STRMS ');
1619 END;
1620 END IF;
1621 --Added by kthiruva for Debug Logging
1622 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1623 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Security deposit amount is not null');
1624 END IF;
1625
1626 OKL_PROCESS_STREAMS_PVT.GEN_SEC_DEP_STRMS(p_api_version => p_api_version
1627 ,p_init_msg_list => p_init_msg_list
1628 ,p_khr_id => l_khr_id
1629 ,p_transaction_number => p_transaction_number
1630 ,p_reporting_streams => l_reporting_streams
1631 ,x_return_status => x_return_status
1632 ,x_msg_count => x_msg_count
1633 ,x_msg_data => x_msg_data);
1634 --Added by kthiruva for Debug Logging
1635 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1636 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to OKL_PROCESS_STREAMS_PVT.GEN_SEC_DEP_STRMS, return status is :'|| x_return_status);
1637 END IF;
1638
1639 IF(IS_DEBUG_PROCEDURE_ON) THEN
1640 BEGIN
1641 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_PROCESS_STREAMS_PVT.GEN_SEC_DEP_STRMS ');
1642 END;
1643 END IF;
1644 -- End of wraper code generated automatically by Debug code generator for OKL_PROCESS_STREAMS_PVT.GEN_SEC_DEP_STRMS
1645 IF (x_return_status = G_RET_STS_UNEXP_ERROR)
1646 THEN
1647 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1648 ELSIF (x_return_status = G_RET_STS_ERROR)
1649 THEN
1650 RAISE G_EXCEPTION_ERROR;
1651 END IF;
1652 END IF;
1653
1654 --Added by srsreeni for bug 5699923
1655 -- Invoke the procedure OKL_LA_STREAM_PVT.RECREATE_TMT_LN_STRMS
1656 -- Streams are no longer requested through Pricing Engine
1657 -- for TERMINATED LOAN COMPONENTS like FINANCED, ROLLOVER FEE
1658 -- and LOAN ASSET LINES. Instead, the CURRENT streams are copied
1659 -- over as WORKING STREAMS. On creating WORKING streams here, the
1660 -- code after this takes care of HISTORIZING current streams and
1661 -- making the newly created WORK streams to CURRENT.
1662 OKL_LA_STREAM_PVT.RECREATE_TMT_LN_STRMS(
1663 p_api_version => p_api_version,
1664 p_init_msg_list => p_init_msg_list,
1665 x_return_status => x_return_status,
1666 x_msg_count => x_msg_count,
1667 x_msg_data => x_msg_data,
1668 p_chr_id => l_khr_id,
1669 p_trx_number => p_transaction_number);
1670
1671 IF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1672 RAISE OKL_API.G_EXCEPTION_ERROR;
1673 ELSIF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1674 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1675 END IF;
1676 --end srsreeni for bug 5699923
1677
1678 -- Start of wraper code generated automatically by Debug code generator for OKL_LA_STREAM_PVT.process_streams
1679 IF(IS_DEBUG_PROCEDURE_ON) THEN
1680
1681 BEGIN
1682 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_LA_STREAM_PVT.process_streams ');
1683 END;
1684 END IF;
1685 --Added by kthiruva for Debug Logging
1686 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1687 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_LA_STREAM_PVT.process_streams');
1688 END IF;
1689
1690 OKL_LA_STREAM_PVT.process_streams(
1691 p_api_version,
1692 p_init_msg_list,
1693 x_return_status,
1694 x_msg_count,
1695 x_msg_data,
1696 l_khr_id,
1697 l_process_yn,
1698 l_khr_yields_rec);
1699 --Added by kthiruva for Debug Logging
1700 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1701 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to OKL_LA_STREAM_PVT.process_streams, return status is :'|| x_return_status);
1702 END IF;
1703
1704 IF(IS_DEBUG_PROCEDURE_ON) THEN
1705 BEGIN
1706 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_LA_STREAM_PVT.process_streams ');
1707 END;
1708 END IF;
1709 -- End of wraper code generated automatically by Debug code generator for OKL_LA_STREAM_PVT.process_streams
1710 /*
1711 Okl_la_Stream_Pub.update_contract_yields(p_api_version,
1712 p_init_msg_list ,
1713 x_return_status ,
1714 x_msg_count,
1715 x_msg_data,
1716 l_khr_id ,
1717 l_khr_yields_rec);
1718 */
1719 IF (x_return_status = G_RET_STS_UNEXP_ERROR)
1720 THEN
1721 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1722 ELSIF (x_return_status = G_RET_STS_ERROR)
1723 THEN
1724 RAISE G_EXCEPTION_ERROR;
1725 END IF;
1726
1727 -- update the status in the Out bound Interface Tables
1728 FOR sir_data in sirv_data_csr(p_transaction_number)
1729 LOOP
1730 l_sirv_rec.id := sir_data.id;
1731 IF( l_exception_data_found = TRUE)
1732 THEN
1733 l_sirv_rec.srt_code := 'PROCESS_COMPLETE_ERRORS';
1734 ELSE
1735 l_sirv_rec.srt_code := 'PROCESS_COMPLETE';
1736 END IF;
1737 l_sirv_rec.date_processed := to_date(to_char(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
1738 -- Start of wraper code generated automatically by Debug code generator for OKL_SIF_RETS_PUB.update_sif_rets
1739 IF(IS_DEBUG_PROCEDURE_ON) THEN
1740 BEGIN
1741 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_SIF_RETS_PUB.update_sif_rets ');
1742 END;
1743 END IF;
1744 --Added by kthiruva for Debug Logging
1745 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1746 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_SIF_RETS_PUB.update_sif_rets');
1747 END IF;
1748 OKL_SIF_RETS_PUB.update_sif_rets(p_api_version => p_api_version,
1749 p_init_msg_list => p_init_msg_list,
1750 x_return_status => l_return_status,
1751 x_msg_count => x_msg_count,
1752 x_msg_data => x_msg_data,
1753 p_sirv_rec => l_sirv_rec,
1754 x_sirv_rec => p_sirv_rec);
1755 --Added by kthiruva for Debug Logging
1756 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1757 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to OKL_SIF_RETS_PUB.update_sif_rets, return status is :'|| x_return_status);
1758 END IF;
1759
1760 IF(IS_DEBUG_PROCEDURE_ON) THEN
1761 BEGIN
1762 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_SIF_RETS_PUB.update_sif_rets ');
1763 END;
1764 END IF;
1765 -- End of wraper code generated automatically by Debug code generator for OKL_SIF_RETS_PUB.update_sif_rets
1766
1767 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)
1768 THEN
1769 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1770 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1771 RAISE OKL_API.G_EXCEPTION_ERROR;
1772 END IF;
1773 END LOOP;
1774 -- update the status in the In bound Interface Tables
1775 lp_sifv_rec.date_processed := to_date(to_char(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
1776 IF( l_exception_data_found = TRUE)
1777 THEN
1778 lp_sifv_rec.sis_code := 'PROCESS_COMPLETE_ERRORS';
1779 lp_sifv_rec.log_file := 'OKLSTXMLG_' || p_transaction_number || '.log ';
1780 ELSE
1781 lp_sifv_rec.sis_code := 'PROCESS_COMPLETE';
1782 END IF;
1783 -- Start of wraper code generated automatically by Debug code generator for OKL_STREAM_INTERFACES_PUB.update_stream_interfaces
1784 IF(IS_DEBUG_PROCEDURE_ON) THEN
1785 BEGIN
1786 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_STREAM_INTERFACES_PUB.update_stream_interfaces ');
1787 END;
1788 END IF;
1789 --Added by kthiruva for Debug Logging
1790 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1791 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_STREAM_INTERFACES_PUB.update_stream_interfaces');
1792 END IF;
1793 OKL_STREAM_INTERFACES_PUB.update_stream_interfaces(
1794 p_api_version => p_api_version
1795 ,p_init_msg_list => p_init_msg_list
1796 ,x_return_status => l_return_status
1797 ,x_msg_count => x_msg_count
1798 ,x_msg_data => x_msg_data
1799 ,p_sifv_rec => lp_sifv_rec
1800 ,x_sifv_rec => lx_sifv_rec);
1801 --Added by kthiruva for Debug Logging
1802 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1803 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to OKL_STREAM_INTERFACES_PUB.update_stream_interfaces, return status is :'|| x_return_status);
1804 END IF;
1805
1806 IF(IS_DEBUG_PROCEDURE_ON) THEN
1807 BEGIN
1808 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_STREAM_INTERFACES_PUB.update_stream_interfaces ');
1809 END;
1810 END IF;
1811 -- End of wraper code generated automatically by Debug code generator for OKL_STREAM_INTERFACES_PUB.update_stream_interfaces
1812 IF l_return_status = G_RET_STS_ERROR THEN
1813 RAISE G_EXCEPTION_ERROR;
1814 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1815 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1816 END IF;
1817
1818 END IF;
1819 -- Bug 4196515: Emd
1820
1821 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1822 x_msg_data => x_msg_data);
1823
1824 EXCEPTION
1825 WHEN G_EXCEPTION_ERROR
1826 THEN
1827 IF(stream_data_csr%ISOPEN)
1828 THEN
1829 CLOSE stream_data_csr;
1830 END IF;
1831 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1832 p_pkg_name => G_PKG_NAME,
1833 p_exc_name => G_EXC_NAME_ERROR,
1834 x_msg_count => x_msg_count,
1835 x_msg_data => x_msg_data,
1836 p_api_type => '_PVT');
1837 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1838 IF(stream_data_csr%ISOPEN)
1839 THEN
1840 CLOSE stream_data_csr;
1841 END IF;
1842 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1843 p_pkg_name => G_PKG_NAME,
1844 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1845 x_msg_count => x_msg_count,
1846 x_msg_data => x_msg_data,
1847 p_api_type => '_PVT');
1848 WHEN OTHERS THEN
1849 IF(stream_data_csr%ISOPEN)
1850 THEN
1851 CLOSE stream_data_csr;
1852 END IF;
1853 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1854 p_pkg_name => G_PKG_NAME,
1855 p_exc_name => G_EXC_NAME_OTHERS,
1856 x_msg_count => x_msg_count,
1857 x_msg_data => x_msg_data,
1858 p_api_type => '_PVT');
1859 END PROCESS_STREAM_RESULTS;
1860 -- INFO:
1861 -- This Procedure updates the SAY_CODE of existing Streams for a Contract to HISTORY from WORKING
1862 -- END INFO
1863 PROCEDURE UPDATE_STREAMS_ACTIVITY(p_api_version IN NUMBER
1864 ,p_init_msg_list IN VARCHAR2
1865 ,x_return_status OUT NOCOPY VARCHAR2
1866 ,x_msg_count OUT NOCOPY NUMBER
1867 ,x_msg_data OUT NOCOPY VARCHAR2
1868 ,p_khr_id IN NUMBER)
1869 IS
1870 l_api_name CONSTANT VARCHAR2(40) := 'UPDATE_STREAMS_ACTIVITY';
1871 l_api_version CONSTANT NUMBER := 1.0;
1872 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1873 i NUMBER;
1874 stmv_tbl stmv_tbl_type;
1875 x_stmv_tbl stmv_tbl_type;
1876 CURSOR streams_csr(l_khr_id NUMBER)
1877 IS
1878 SELECT
1879 ID,
1880
1881 SAY_CODE
1882 FROM
1883 OKL_STREAMS_V
1884 WHERE
1885 KHR_ID = l_khr_id
1886 AND
1887 SAY_CODE = G_STREAM_ACTIVITY_WORK;
1888 stm_csr streams_csr%ROWTYPE;
1889 stm_csr_rec streams_csr%ROWTYPE;
1890 BEGIN
1891 x_return_status := G_RET_STS_SUCCESS;
1892 -- populate all the records with new Activity Code
1893 FOR stm_csr IN streams_csr(p_khr_id ) LOOP
1894 -- Update the activity code to HISTORY
1895 stmv_tbl(i).id := stm_csr.id;
1896 stmv_tbl(i).say_code := G_STREAM_ACTIVITY_HIST ;
1897 stmv_tbl(i).date_history := SYSDATE;
1898 END LOOP;
1899 -- call the update for all the records
1900 -- Start of wraper code generated automatically by Debug code generator for Okl_Streams_Pub.update_streams
1901 IF(L_DEBUG_ENABLED='Y') THEN
1902 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
1903 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
1904 END IF;
1905 IF(IS_DEBUG_PROCEDURE_ON) THEN
1906 BEGIN
1907 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call Okl_Streams_Pub.update_streams ');
1908 END;
1909 END IF;
1910 Okl_Streams_Pub.update_streams(l_api_version
1911 ,p_init_msg_list
1912 ,x_return_status
1913 ,x_msg_count
1914 ,x_msg_data
1915 ,stmv_tbl
1916 ,x_stmv_tbl);
1917 IF(IS_DEBUG_PROCEDURE_ON) THEN
1918 BEGIN
1919 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call Okl_Streams_Pub.update_streams ');
1920 END;
1921 END IF;
1922 -- End of wraper code generated automatically by Debug code generator for Okl_Streams_Pub.update_streams
1923 EXCEPTION
1924 WHEN G_EXCEPTION_ERROR THEN
1925 x_return_status := G_RET_STS_ERROR;
1926 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1927 x_return_status := G_RET_STS_UNEXP_ERROR;
1928 WHEN OTHERS THEN
1929 x_return_status := G_RET_STS_UNEXP_ERROR;
1930 -- store SQL error message on message stack for caller
1931 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1932 p_msg_name => G_UNEXPECTED_ERROR,
1933 p_token1 => G_SQLCODE_TOKEN,
1934 p_token1_value => sqlcode,
1935 p_token2 => G_SQLERRM_TOKEN,
1936 p_token2_value => sqlerrm );
1937 -- x_msg_data := 'Unexpected Database Error';
1938 END UPDATE_STREAMS_ACTIVITY;
1939 ------------------------------------------------------------------------------------------
1940 -- PROCEDURE
1941 -- Creates a logfile based upon the exceptions found in the inbound interface tables
1942 -- These exceptions are the ones returned by the Pricing Engine. To understand the logfile
1943 -- some exposure to XML is a must.
1944 -- Inputs: p_transaction_number - the id of the transaction
1945 -- Output: A file by the name 'OKLSTXMLG_1234.log' where 1234 is the id of the transaction
1946 ------------------------------------------------------------------------------------------
1947 PROCEDURE GENERATE_ERROR_LOGFILE(p_transaction_number NUMBER
1948 ,x_return_status VARCHAR2
1949 ) IS
1950 -- define cursor to check any Exceptions in the Inbound Interface Tables
1951 CURSOR exception_data_csr(p_trx_number NUMBER) IS
1952 SELECT
1953 SRMB.ID,
1954 SRMB.ERROR_CODE,
1955 SRMB.ERROR_MESSAGE,
1956 SRMB.TAG_NAME,
1957 SRMB.TAG_ATTRIBUTE_NAME,
1958 SRMB.TAG_ATTRIBUTE_VALUE,
1959 SRMB.DESCRIPTION
1960 FROM
1961 OKL_SIF_RETS SIRB,
1962 OKL_SIF_RET_ERRORS SRMB
1963 WHERE
1964 SIRB.TRANSACTION_NUMBER = p_trx_number
1965 AND
1966 SIRB.ID = SRMB.SIR_ID;
1967 l_message_count NUMBER := 0;
1968 l_error_message_line VARCHAR2(4000) := NULL;
1969 l_error_message_tbl LOG_MSG_TBL_TYPE;
1970 l_exception_data_found BOOLEAN := FALSE;
1971 l_msg_text fnd_new_messages.MESSAGE_TEXT%TYPE;
1972 l_comments VARCHAR2(4000) := NULL;
1973 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1974 BEGIN
1975 FOR exception_data in exception_data_csr(p_transaction_number)
1976 LOOP
1977 IF(l_message_count = 0)
1978 THEN
1979 l_error_message_line := 'REQUEST ID = ' || p_transaction_number || ' TIME PROCESSED = '|| to_char(SYSDATE,'YYYYMMDD HH24MISS');
1980
1981 l_error_message_line := l_error_message_line || G_NEW_LINE;
1982 l_error_message_line := l_error_message_line || 'Errors returned from Pricing Engine :- ';
1983 l_error_message_line := l_error_message_line || G_NEW_LINE;
1984 l_error_message_line := l_error_message_line || 'ERROR CODE :: ' || exception_data.ERROR_CODE;
1985 l_error_message_line := l_error_message_line || G_NEW_LINE;
1986 l_error_message_line := l_error_message_line || 'ERROR MESSAGE :: ' || exception_data.ERROR_MESSAGE;
1987 l_error_message_line := l_error_message_line || G_NEW_LINE;
1988 l_error_message_line := l_error_message_line || 'XML TAG :: ' || exception_data.TAG_NAME ;
1989 l_error_message_line := l_error_message_line || G_NEW_LINE;
1990 l_message_count := l_message_count + 1;
1991 ELSE
1992 l_error_message_line := NULL;
1993 l_error_message_line := l_error_message_line || 'ERROR CODE :: ' || exception_data.ERROR_CODE;
1994 l_error_message_line := l_error_message_line || G_NEW_LINE;
1995 l_error_message_line := l_error_message_line || 'ERROR MESSAGE :: ' || exception_data.ERROR_MESSAGE;
1996 l_error_message_line := l_error_message_line || G_NEW_LINE;
1997 l_error_message_line := l_error_message_line || 'XML TAG :: ' || exception_data.TAG_NAME ;
1998 l_error_message_line := l_error_message_line || G_NEW_LINE;
1999 -- Not used in this release
2000 /*
2001 l_error_message_line := l_error_message_line || 'XML TAG ATTRIBUTE :: ' || exception_data.TAG_ATTRIBUTE_NAME ;
2002 l_error_message_line := l_error_message_line || G_NEW_LINE;
2003 l_error_message_line := l_error_message_line || 'XML TAG ATTRIBUTE VALUE :: ' || exception_data.TAG_ATTRIBUTE_VALUE ;
2004 l_error_message_line := l_error_message_line || G_NEW_LINE;
2005 l_error_message_line := l_error_message_line || 'DETAILS :: ' || exception_data.DESCRIPTION ;
2006 l_error_message_line := l_error_message_line || G_NEW_LINE;
2007 */
2008 l_message_count := l_message_count + 1;
2009 END IF;
2010 l_error_message_tbl(l_message_count) := l_error_message_line;
2011 END LOOP;
2012 IF( l_message_count > 0)
2013 THEN
2014 l_exception_data_found := TRUE;
2015 FND_MESSAGE.SET_NAME ( G_APP_NAME, 'OKL_STREAM_GENERATION_ERROR');
2016 FND_MESSAGE.SET_TOKEN(TOKEN => 'FILE_NAME',
2017 VALUE => 'OKLSTXMLG_' || p_transaction_number || '.log',
2018 TRANSLATE => TRUE);
2019 l_msg_text := FND_MESSAGE.GET;
2020 l_comments := l_msg_text;
2021 l_error_message_tbl(l_message_count + 1) := 'End Errors returned from Pricing Engine' ;
2022 OKL_STREAMS_UTIL.LOG_MESSAGE(p_msgs_tbl => l_error_message_tbl,
2023 p_translate => G_FALSE,
2024 p_file_name => 'OKLSTXMLG_' || p_transaction_number || '.log' ,
2025 x_return_status => l_return_status );
2026 ELSE
2027 l_comments := NULL;
2028 END IF;
2029 END GENERATE_ERROR_LOGFILE;
2030 ------------------------------------------------------------------------------------------
2031 -- PROCEDURE
2032 ------------------------------------------------------------------------------------------
2033 PROCEDURE UPDATE_STATUSES(p_transaction_number NUMBER
2034 ,x_return_status VARCHAR2
2035 ) IS
2036 lp_sirv_rec sirv_rec_type;
2037 lx_sirv_rec sirv_rec_type;
2038 lx_msg_data VARCHAR2(400);
2039 lp_api_version NUMBER := 1.0;
2040 lp_init_msg_list VARCHAR2(1) := 'F';
2041 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
2042 l_exception_data_found BOOLEAN := FALSE;
2043 lx_msg_count NUMBER;
2044 lp_sifv_rec OKL_STREAM_INTERFACES_PUB.SIFV_REC_TYPE;
2045 lx_sifv_rec OKL_STREAM_INTERFACES_PUB.SIFV_REC_TYPE;
2046 CURSOR sirv_data_csr (p_trx_number IN NUMBER) IS
2047 SELECT
2048 ID,
2049 TRANSACTION_NUMBER,
2050 SRT_CODE,
2051 EFFECTIVE_PRE_TAX_YIELD,
2052 YIELD_NAME,
2053 INDEX_NUMBER,
2054 EFFECTIVE_AFTER_TAX_YIELD,
2055 NOMINAL_PRE_TAX_YIELD,
2056 NOMINAL_AFTER_TAX_YIELD,
2057 STREAM_INTERFACE_ATTRIBUTE01,
2058 STREAM_INTERFACE_ATTRIBUTE02,
2059 STREAM_INTERFACE_ATTRIBUTE03,
2060 STREAM_INTERFACE_ATTRIBUTE04,
2061 STREAM_INTERFACE_ATTRIBUTE05,
2062 STREAM_INTERFACE_ATTRIBUTE06,
2063 STREAM_INTERFACE_ATTRIBUTE07,
2064 STREAM_INTERFACE_ATTRIBUTE08,
2065 STREAM_INTERFACE_ATTRIBUTE09,
2066 STREAM_INTERFACE_ATTRIBUTE10,
2067 STREAM_INTERFACE_ATTRIBUTE11,
2068 STREAM_INTERFACE_ATTRIBUTE12,
2069 STREAM_INTERFACE_ATTRIBUTE13,
2070 STREAM_INTERFACE_ATTRIBUTE14,
2071 STREAM_INTERFACE_ATTRIBUTE15,
2072 OBJECT_VERSION_NUMBER,
2073 CREATED_BY,
2074 LAST_UPDATED_BY,
2075 CREATION_DATE,
2076 LAST_UPDATE_DATE,
2077 LAST_UPDATE_LOGIN,
2078 IMPLICIT_INTEREST_RATE,
2079 DATE_PROCESSED,
2080
2081 REQUEST_ID,
2082 PROGRAM_APPLICATION_ID,
2083 PROGRAM_ID,
2084 PROGRAM_UPDATE_DATE
2085 FROM Okl_Sif_Rets
2086 WHERE okl_sif_rets.transaction_number = p_trx_number;
2087 CURSOR sif_data_csr (p_transaction_number IN NUMBER) IS
2088 SELECT
2089 ID,
2090 ORP_CODE,
2091 LOG_FILE
2092 FROM Okl_Stream_Interfaces
2093 WHERE okl_stream_interfaces.transaction_number = p_transaction_number;
2094 -- define cursor to check any Exceptions in the Inbound Interface Tables
2095 CURSOR exception_data_csr(p_trx_number NUMBER) IS
2096 SELECT
2097 SRMB.ID
2098 FROM
2099 OKL_SIF_RETS SIRB,
2100 OKL_SIF_RET_ERRORS SRMB
2101 WHERE
2102 SIRB.TRANSACTION_NUMBER = p_trx_number
2103 AND
2104 SIRB.ID = SRMB.SIR_ID;
2105 BEGIN
2106 --check for errors
2107 FOR exception_data in exception_data_csr(p_transaction_number)
2108 LOOP
2109 l_exception_data_found := TRUE;
2110 EXIT;
2111 END LOOP;
2112 -- update the status in the Out bound Interface Tables
2113 FOR sir_data in sirv_data_csr(p_transaction_number)
2114 LOOP
2115 lp_sirv_rec.id := sir_data.id;
2116 IF(l_exception_data_found = TRUE)
2117 THEN
2118 lp_sirv_rec.srt_code := 'PROCESS_COMPLETE_ERRORS';
2119 ELSE
2120 lp_sirv_rec.srt_code := 'PROCESS_COMPLETE';
2121 END IF;
2122 lp_sirv_rec.date_processed := to_date(to_char(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
2123 -- Start of wraper code generated automatically by Debug code generator for OKL_SIF_RETS_PUB.update_sif_rets
2124 IF(L_DEBUG_ENABLED='Y') THEN
2125 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
2126 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
2127 END IF;
2128 IF(IS_DEBUG_PROCEDURE_ON) THEN
2129 BEGIN
2130 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_SIF_RETS_PUB.update_sif_rets ');
2131 END;
2132 END IF;
2133 OKL_SIF_RETS_PUB.update_sif_rets(p_api_version => lp_api_version,
2134 p_init_msg_list => lp_init_msg_list,
2135 x_return_status => l_return_status,
2136 x_msg_count => lx_msg_count,
2137 x_msg_data => lx_msg_data,
2138 p_sirv_rec => lp_sirv_rec,
2139 x_sirv_rec => lx_sirv_rec);
2140 IF(IS_DEBUG_PROCEDURE_ON) THEN
2141 BEGIN
2142 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_SIF_RETS_PUB.update_sif_rets ');
2143 END;
2144 END IF;
2145 -- End of wraper code generated automatically by Debug code generator for OKL_SIF_RETS_PUB.update_sif_rets
2146 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2147 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2148 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2149 RAISE OKL_API.G_EXCEPTION_ERROR;
2150 END IF;
2151 END LOOP;
2152 -- update the status in the In bound Interface Tables
2153 FOR sif_data in sif_data_csr(p_transaction_number)
2154 LOOP
2155 lp_sifv_rec.id := sif_data.id;
2156 lp_sifv_rec.ORP_CODE := sif_data.ORP_CODE;
2157 END LOOP;
2158 lp_sifv_rec.date_processed := to_date(to_char(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
2159 IF(l_exception_data_found = TRUE)
2160 THEN
2161 -- lp_sifv_rec.sis_code := 'PROCESS_COMPLETE';
2162 lp_sifv_rec.sis_code := 'PROCESS_COMPLETE_ERRORS';
2163 lp_sifv_rec.stream_interface_attribute03 := 'OKLSTXMLG_' || p_transaction_number || '.log ';
2164 ELSE
2165 lp_sifv_rec.sis_code := 'PROCESS_COMPLETE';
2166 END IF;
2167 -- Start of wraper code generated automatically by Debug code generator for OKL_STREAM_INTERFACES_PUB.update_stream_interfaces
2168 IF(IS_DEBUG_PROCEDURE_ON) THEN
2169 BEGIN
2170 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_STREAM_INTERFACES_PUB.update_stream_interfaces ');
2171 END;
2172 END IF;
2173 OKL_STREAM_INTERFACES_PUB.update_stream_interfaces(p_api_version => lp_api_version
2174 ,p_init_msg_list => lp_init_msg_list
2175 ,x_return_status => l_return_status
2176 ,x_msg_count => lx_msg_count
2177 ,x_msg_data => lx_msg_data
2178 ,p_sifv_rec => lp_sifv_rec
2179 ,x_sifv_rec => lx_sifv_rec);
2180
2181 IF(IS_DEBUG_PROCEDURE_ON) THEN
2182 BEGIN
2183 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_STREAM_INTERFACES_PUB.update_stream_interfaces ');
2184 END;
2185 END IF;
2186 -- End of wraper code generated automatically by Debug code generator for OKL_STREAM_INTERFACES_PUB.update_stream_interfaces
2187 IF l_return_status = G_RET_STS_ERROR THEN
2188 RAISE G_EXCEPTION_ERROR;
2189 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2190 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2191 END IF;
2192 END UPDATE_STATUSES;
2193 ------------------------------------------------------------------------------------------
2194 -- PROCEDURE
2195 ------------------------------------------------------------------------------------------
2196 PROCEDURE PROCESS_REST_STRM_RESLTS(p_api_version IN NUMBER
2197 ,p_init_msg_list IN VARCHAR2
2198 ,p_transaction_number IN NUMBER
2199 ,x_return_status OUT NOCOPY VARCHAR2
2200 ,x_msg_count OUT NOCOPY NUMBER
2201 ,x_msg_data OUT NOCOPY VARCHAR2)
2202 IS
2203 l_return_status VARCHAR(1) := G_RET_STS_SUCCESS;
2204 l_api_name CONSTANT VARCHAR2(40) := 'PROCESS_REST_STRM_RESLTS';
2205 l_api_version CONSTANT NUMBER := 1.0;
2206 l_srlv_tbl srlv_tbl_type;
2207 l_yields_tbl yields_tbl_type;
2208 -- l_yields_tbl OKL_CREATE_STREAMS_PUB.csm_yields_tbl_type;
2209 l_sir_id NUMBER;
2210 i NUMBER;
2211 l_chr_id NUMBER;
2212 l_object1_id1 VARCHAR2(40);
2213 CURSOR sirv_data_csr (p_trx_number NUMBER) IS
2214 SELECT
2215 ID
2216 FROM okl_sif_rets
2217 WHERE okl_sif_rets.transaction_number = p_trx_number
2218 AND
2219 INDEX_NUMBER = 0;
2220 CURSOR srlv_data_csr (p_sir_id IN NUMBER) IS
2221 SELECT
2222 ID,
2223 LEVEL_INDEX_NUMBER,
2224 NUMBER_OF_PERIODS,
2225 SIR_ID,
2226 INDEX_NUMBER,
2227 LEVEL_TYPE,
2228 AMOUNT,
2229 ADVANCE_OR_ARREARS,
2230 PERIOD,
2231 LOCK_LEVEL_STEP,
2232 DAYS_IN_PERIOD,
2233 FIRST_PAYMENT_DATE,
2234 STREAM_INTERFACE_ATTRIBUTE1,
2235 STREAM_INTERFACE_ATTRIBUTE2,
2236 STREAM_INTERFACE_ATTRIBUTE3,
2237 STREAM_INTERFACE_ATTRIBUTE4,
2238 STREAM_INTERFACE_ATTRIBUTE5,
2239 STREAM_INTERFACE_ATTRIBUTE6,
2240 STREAM_INTERFACE_ATTRIBUTE7,
2241 STREAM_INTERFACE_ATTRIBUTE8,
2242 STREAM_INTERFACE_ATTRIBUTE9,
2243 STREAM_INTERFACE_ATTRIBUTE10,
2244 STREAM_INTERFACE_ATTRIBUTE11,
2245 STREAM_INTERFACE_ATTRIBUTE12,
2246 STREAM_INTERFACE_ATTRIBUTE13,
2247 STREAM_INTERFACE_ATTRIBUTE14,
2248 STREAM_INTERFACE_ATTRIBUTE15
2249 FROM OKL_SIF_RET_LEVELS
2250 WHERE SIR_ID = p_sir_id;
2251 CURSOR yields_data_csr (p_trx_number NUMBER) IS
2252 SELECT
2253 SIRB.EFFECTIVE_PRE_TAX_YIELD,
2254 SIRB.EFFECTIVE_AFTER_TAX_YIELD,
2255 SIRB.NOMINAL_PRE_TAX_YIELD,
2256 SIRB.NOMINAL_AFTER_TAX_YIELD,
2257 SIRB.IMPLICIT_INTEREST_RATE,
2258 SIYB.YIELD_NAME,
2259 SIYB.METHOD,
2260 SIYB.ARRAY_TYPE,
2261 SIYB.ROE_TYPE,
2262 SIYB.ROE_BASE,
2263 SIYB.COMPOUNDED_METHOD,
2264 SIYB.TARGET_VALUE,
2265 SIYB.INDEX_NUMBER,
2266 SIYB.NOMINAL_YN,
2267 SIYB.PRE_TAX_YN
2268 FROM OKL_SIF_RETS SIRB, OKL_SIF_YIELDS SIYB, OKL_STREAM_INTERFACES SIFB
2269 WHERE SIRB.TRANSACTION_NUMBER = p_trx_number
2270 AND SIFB.TRANSACTION_NUMBER = p_trx_number
2271 AND SIYB.SIF_ID = SIFB.ID
2272 AND SIRB.INDEX_NUMBER = SIYB.INDEX_NUMBER;
2273 CURSOR sif_data_csr (p_trx_number NUMBER ) IS
2274 SELECT
2275 SIFB.OBJECT1_ID1,
2276 SIFB.KHR_ID
2277 FROM
2278 OKL_STREAM_INTERFACES SIFB
2279 WHERE
2280
2281 SIFB.TRANSACTION_NUMBER = p_trx_number;
2282 BEGIN
2283 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
2284 p_pkg_name => 'OKL_PROCESS_STREAMS_PVT',
2285 p_init_msg_list => p_init_msg_list,
2286 l_api_version => l_api_version,
2287 p_api_version => p_api_version,
2288 p_api_type => '_PVT',
2289 x_return_status => l_return_status);
2290 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2291 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2292 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2293 RAISE G_EXCEPTION_ERROR;
2294 END IF;
2295 --Before Processing Stream Results check for any exceptions in the Inbound Interface Tables
2296 GENERATE_ERROR_LOGFILE(p_transaction_number => p_transaction_number
2297 ,x_return_status => l_return_status);
2298 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2299 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2300 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2301 RAISE G_EXCEPTION_ERROR;
2302 END IF;
2303 FOR sifv_data in sif_data_csr(p_transaction_number)
2304 LOOP
2305 l_chr_id := sifv_data.khr_id;
2306 l_object1_id1 := sifv_data.object1_id1;
2307 END LOOP;
2308 -- fetch records from
2309 FOR sirv_data in sirv_data_csr(p_transaction_number)
2310 LOOP
2311 l_sir_id := sirv_data.id;
2312 END LOOP;
2313 i := 1;
2314 FOR srlv_data in srlv_data_csr(l_sir_id)
2315 LOOP
2316 l_srlv_tbl(i) := null;
2317 l_srlv_tbl(i).ID := srlv_data.ID;
2318 l_srlv_tbl(i).LEVEL_INDEX_NUMBER := srlv_data.LEVEL_INDEX_NUMBER;
2319 l_srlv_tbl(i).NUMBER_OF_PERIODS := srlv_data.NUMBER_OF_PERIODS;
2320 l_srlv_tbl(i).SIR_ID := srlv_data.SIR_ID;
2321 l_srlv_tbl(i).INDEX_NUMBER := srlv_data.INDEX_NUMBER;
2322 l_srlv_tbl(i).LEVEL_TYPE := srlv_data.LEVEL_TYPE;
2323 l_srlv_tbl(i).AMOUNT := srlv_data.AMOUNT;
2324 l_srlv_tbl(i).ADVANCE_OR_ARREARS := srlv_data.ADVANCE_OR_ARREARS;
2325 l_srlv_tbl(i).PERIOD := srlv_data.PERIOD;
2326 l_srlv_tbl(i).LOCK_LEVEL_STEP := srlv_data.LOCK_LEVEL_STEP;
2327 l_srlv_tbl(i).DAYS_IN_PERIOD := srlv_data.DAYS_IN_PERIOD;
2328 l_srlv_tbl(i).FIRST_PAYMENT_DATE := srlv_data.FIRST_PAYMENT_DATE;
2329 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE1 := srlv_data.STREAM_INTERFACE_ATTRIBUTE1;
2330 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE2 := srlv_data.STREAM_INTERFACE_ATTRIBUTE2;
2331 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE3 := srlv_data.STREAM_INTERFACE_ATTRIBUTE3;
2332 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE4 := srlv_data.STREAM_INTERFACE_ATTRIBUTE4;
2333 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE5 := srlv_data.STREAM_INTERFACE_ATTRIBUTE5;
2334 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE6 := srlv_data.STREAM_INTERFACE_ATTRIBUTE6;
2335 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE7 := srlv_data.STREAM_INTERFACE_ATTRIBUTE7;
2336 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE8 := srlv_data.STREAM_INTERFACE_ATTRIBUTE8;
2337 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE9 := srlv_data.STREAM_INTERFACE_ATTRIBUTE9;
2338 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE10 := srlv_data.STREAM_INTERFACE_ATTRIBUTE10;
2339 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE11 := srlv_data.STREAM_INTERFACE_ATTRIBUTE11;
2340 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE12 := srlv_data.STREAM_INTERFACE_ATTRIBUTE12;
2341 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE13 := srlv_data.STREAM_INTERFACE_ATTRIBUTE13;
2342 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE14 := srlv_data.STREAM_INTERFACE_ATTRIBUTE14;
2343 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE15 := srlv_data.STREAM_INTERFACE_ATTRIBUTE15;
2344 i := i + 1;
2345 END LOOP;
2346 i := 1;
2347 FOR yields_data in yields_data_csr(p_transaction_number)
2348 LOOP
2349 l_yields_tbl(i) := null;
2350 l_yields_tbl(i).yield_name := yields_data.yield_name;
2351 -- l_yields_tbl(i).yield_name := 'PTIRR';
2352 l_yields_tbl(i).value := yields_data.effective_pre_tax_yield;
2353 -- l_yields_tbl(i).effective_after_tax_yield := yields_data.effective_after_tax_yield;
2354 -- l_yields_tbl(i).nominal_pre_tax_yield := yields_data.nominal_pre_tax_yield;
2355 -- l_yields_tbl(i).nominal_after_tax_yield := yields_data.nominal_after_tax_yield;
2356 l_yields_tbl(i).implicit_interest_rate := yields_data.implicit_interest_rate;
2357 l_yields_tbl(i).method := yields_data.method;
2358 l_yields_tbl(i).array_type := yields_data.array_type;
2359 l_yields_tbl(i).roe_type := yields_data.roe_type;
2360 l_yields_tbl(i).roe_base := yields_data.roe_base;
2361 l_yields_tbl(i).compounded_method := yields_data.compounded_method;
2362 l_yields_tbl(i).target_value := yields_data.target_value;
2363 l_yields_tbl(i).index_number := yields_data.index_number;
2364 l_yields_tbl(i).nominal_yn := yields_data.nominal_yn;
2365 l_yields_tbl(i).pre_tax_yn := yields_data.pre_tax_yn;
2366 i := i + 1;
2367 END LOOP;
2368 -- call the restructure api for processing results
2369 -- Start of wraper code generated automatically by Debug code generator for OKL_AM_RESTRUCTURE_RENTS_PVT.process_results
2370 IF(L_DEBUG_ENABLED='Y') THEN
2371 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
2372 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
2373 END IF;
2374 IF(IS_DEBUG_PROCEDURE_ON) THEN
2375 BEGIN
2376 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_AM_RESTRUCTURE_RENTS_PVT.process_results ');
2377 END;
2378 END IF;
2379 OKL_AM_RESTRUCTURE_RENTS_PVT.process_results(p_api_version => l_api_version,
2380
2381 p_init_msg_list => p_init_msg_list,
2382 p_generation_context => 'RSAM',
2383 p_jtot_object1_code => 'OKL_TRX_QUOTES_B',
2384 p_object1_id1 => l_object1_id1,
2385 p_chr_id => l_chr_id,
2386 p_rent_tbl => l_srlv_tbl,
2387 p_yield_tbl => l_yields_tbl,
2388 x_return_status => l_return_status,
2389 x_msg_count => x_msg_count,
2390 x_msg_data => x_msg_data);
2391 IF(IS_DEBUG_PROCEDURE_ON) THEN
2392 BEGIN
2393 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_AM_RESTRUCTURE_RENTS_PVT.process_results ');
2394 END;
2395 END IF;
2396 -- End of wraper code generated automatically by Debug code generator for OKL_AM_RESTRUCTURE_RENTS_PVT.process_results
2397 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2398 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2399 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2400 RAISE G_EXCEPTION_ERROR;
2401 END IF;
2402 -- update statuses in the inbound and outbound interface tables
2403 UPDATE_STATUSES(p_transaction_number => p_transaction_number
2404 ,x_return_status => l_return_status);
2405 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2406 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2407 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2408 RAISE G_EXCEPTION_ERROR;
2409 END IF;
2410 x_return_status := l_return_status;
2411 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2412 x_msg_data => x_msg_data);
2413 EXCEPTION
2414 WHEN G_EXCEPTION_ERROR THEN
2415 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2416 p_pkg_name => G_PKG_NAME,
2417 p_exc_name => G_EXC_NAME_ERROR,
2418 x_msg_count => x_msg_count,
2419 x_msg_data => x_msg_data,
2420 p_api_type => '_PVT');
2421 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2422 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2423 p_pkg_name => G_PKG_NAME,
2424 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
2425 x_msg_count => x_msg_count,
2426 x_msg_data => x_msg_data,
2427 p_api_type => '_PVT');
2428 WHEN OTHERS THEN
2429 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2430 p_pkg_name => G_PKG_NAME,
2431 p_exc_name => G_EXC_NAME_OTHERS,
2432 x_msg_count => x_msg_count,
2433 x_msg_data => x_msg_data,
2434 p_api_type => '_PVT');
2435 END PROCESS_REST_STRM_RESLTS;
2436 ------------------------------------------------------------------------------------------
2437 -- PROCEDURE
2438 ------------------------------------------------------------------------------------------
2439 PROCEDURE PROCESS_QUOT_STRM_RESLTS(p_api_version IN NUMBER
2440 ,p_init_msg_list IN VARCHAR2
2441 ,p_transaction_number IN NUMBER
2442 ,x_return_status OUT NOCOPY VARCHAR2
2443 ,x_msg_count OUT NOCOPY NUMBER
2444 ,x_msg_data OUT NOCOPY VARCHAR2)
2445 IS
2446 l_return_status VARCHAR(1) := G_RET_STS_SUCCESS;
2447 l_api_name CONSTANT VARCHAR2(40) := 'PROCESS_QUOT_STRM_RESLTS';
2448 l_api_version CONSTANT NUMBER := 1.0;
2449 l_srlv_tbl srlv_tbl_type;
2450 l_yields_tbl yields_tbl_type;
2451 l_sir_id NUMBER;
2452 i NUMBER;
2453 l_chr_id NUMBER;
2454 l_sis_code VARCHAR2(30);
2455 l_amount NUMBER;
2456 CURSOR sirv_data_csr (p_trx_number NUMBER) IS
2457 SELECT
2458 ID
2459 FROM okl_sif_rets
2460 WHERE okl_sif_rets.transaction_number = p_trx_number
2461 AND INDEX_NUMBER = 0;
2462 CURSOR srlv_data_csr (p_sir_id IN NUMBER) IS
2463 SELECT
2464 ID,
2465 LEVEL_INDEX_NUMBER,
2466 NUMBER_OF_PERIODS,
2467 SIR_ID,
2468 INDEX_NUMBER,
2469 LEVEL_TYPE,
2470 AMOUNT,
2471 ADVANCE_OR_ARREARS,
2472 PERIOD,
2473 LOCK_LEVEL_STEP,
2474 DAYS_IN_PERIOD,
2475 FIRST_PAYMENT_DATE,
2476 STREAM_INTERFACE_ATTRIBUTE1,
2477 STREAM_INTERFACE_ATTRIBUTE2,
2478 STREAM_INTERFACE_ATTRIBUTE3,
2479 STREAM_INTERFACE_ATTRIBUTE4,
2480
2481 STREAM_INTERFACE_ATTRIBUTE5,
2482 STREAM_INTERFACE_ATTRIBUTE6,
2483 STREAM_INTERFACE_ATTRIBUTE7,
2484 STREAM_INTERFACE_ATTRIBUTE8,
2485 STREAM_INTERFACE_ATTRIBUTE9,
2486 STREAM_INTERFACE_ATTRIBUTE10,
2487 STREAM_INTERFACE_ATTRIBUTE11,
2488 STREAM_INTERFACE_ATTRIBUTE12,
2489 STREAM_INTERFACE_ATTRIBUTE13,
2490 STREAM_INTERFACE_ATTRIBUTE14,
2491 STREAM_INTERFACE_ATTRIBUTE15
2492 FROM OKL_SIF_RET_LEVELS
2493 WHERE SIR_ID = p_sir_id;
2494 CURSOR yields_data_csr (p_trx_number NUMBER) IS
2495 SELECT
2496 SIRB.EFFECTIVE_PRE_TAX_YIELD,
2497 SIRB.EFFECTIVE_AFTER_TAX_YIELD,
2498 SIRB.NOMINAL_PRE_TAX_YIELD,
2499 SIRB.NOMINAL_AFTER_TAX_YIELD,
2500 SIRB.IMPLICIT_INTEREST_RATE,
2501 SIYB.YIELD_NAME,
2502 SIYB.METHOD,
2503 SIYB.ARRAY_TYPE,
2504 SIYB.ROE_TYPE,
2505 SIYB.ROE_BASE,
2506 SIYB.COMPOUNDED_METHOD,
2507 SIYB.TARGET_VALUE,
2508 SIYB.INDEX_NUMBER,
2509 SIYB.NOMINAL_YN,
2510 SIYB.PRE_TAX_YN
2511 FROM OKL_SIF_RETS SIRB, OKL_SIF_YIELDS SIYB, OKL_STREAM_INTERFACES SIFB
2512 WHERE SIRB.TRANSACTION_NUMBER = p_trx_number
2513 AND SIFB.TRANSACTION_NUMBER = p_trx_number
2514 AND SIYB.SIF_ID = SIFB.ID
2515 AND SIRB.INDEX_NUMBER = SIYB.INDEX_NUMBER;
2516 CURSOR sif_data_csr (p_trx_number NUMBER ) IS
2517 SELECT
2518 SIFB.SIS_CODE,
2519 SIFB.KHR_ID
2520 FROM
2521 OKL_STREAM_INTERFACES SIFB
2522 WHERE
2523 SIFB.TRANSACTION_NUMBER = p_trx_number;
2524 BEGIN
2525 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
2526 p_pkg_name => 'OKL_PROCESS_STREAMS_PVT',
2527 p_init_msg_list => p_init_msg_list,
2528 l_api_version => l_api_version,
2529 p_api_version => p_api_version,
2530 p_api_type => '_PVT',
2531 x_return_status => l_return_status);
2532 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2533 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2534 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2535 RAISE G_EXCEPTION_ERROR;
2536 END IF;
2537 FOR sifv_data in sif_data_csr(p_transaction_number)
2538 LOOP
2539 l_chr_id := sifv_data.khr_id;
2540 l_sis_code := sifv_data.sis_code;
2541 END LOOP;
2542 --Before Processing Stream Results check for any exceptions in the Inbound Interface Tables
2543 GENERATE_ERROR_LOGFILE(p_transaction_number => p_transaction_number
2544 ,x_return_status => l_return_status);
2545 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2546 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2547 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2548 RAISE G_EXCEPTION_ERROR;
2549 END IF;
2550 -- fetch records from
2551 FOR sirv_data in sirv_data_csr(p_transaction_number)
2552 LOOP
2553 l_sir_id := sirv_data.id;
2554 END LOOP;
2555 i := 1;
2556 FOR srlv_data in srlv_data_csr(l_sir_id)
2557 LOOP
2558 l_srlv_tbl(i) := null;
2559 l_srlv_tbl(i).ID := srlv_data.ID;
2560 l_srlv_tbl(i).LEVEL_INDEX_NUMBER := srlv_data.LEVEL_INDEX_NUMBER;
2561 l_srlv_tbl(i).NUMBER_OF_PERIODS := srlv_data.NUMBER_OF_PERIODS;
2562 l_srlv_tbl(i).SIR_ID := srlv_data.SIR_ID;
2563 l_srlv_tbl(i).INDEX_NUMBER := srlv_data.INDEX_NUMBER;
2564 l_srlv_tbl(i).LEVEL_TYPE := srlv_data.LEVEL_TYPE;
2565 -- akjain 08/30/2002
2566 -- added to format the amount
2567 l_amount := srlv_data.AMOUNT;
2568 l_srlv_tbl(i).AMOUNT := format_number(l_amount, l_chr_id);
2569 l_srlv_tbl(i).ADVANCE_OR_ARREARS := srlv_data.ADVANCE_OR_ARREARS;
2570 l_srlv_tbl(i).PERIOD := srlv_data.PERIOD;
2571 l_srlv_tbl(i).LOCK_LEVEL_STEP := srlv_data.LOCK_LEVEL_STEP;
2572 l_srlv_tbl(i).DAYS_IN_PERIOD := srlv_data.DAYS_IN_PERIOD;
2573 l_srlv_tbl(i).FIRST_PAYMENT_DATE := srlv_data.FIRST_PAYMENT_DATE;
2574 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE1 := srlv_data.STREAM_INTERFACE_ATTRIBUTE1;
2575 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE2 := srlv_data.STREAM_INTERFACE_ATTRIBUTE2;
2576 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE3 := srlv_data.STREAM_INTERFACE_ATTRIBUTE3;
2577 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE4 := srlv_data.STREAM_INTERFACE_ATTRIBUTE4;
2578 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE5 := srlv_data.STREAM_INTERFACE_ATTRIBUTE5;
2579 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE6 := srlv_data.STREAM_INTERFACE_ATTRIBUTE6;
2580
2581 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE7 := srlv_data.STREAM_INTERFACE_ATTRIBUTE7;
2582 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE8 := srlv_data.STREAM_INTERFACE_ATTRIBUTE8;
2583 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE9 := srlv_data.STREAM_INTERFACE_ATTRIBUTE9;
2584 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE10 := srlv_data.STREAM_INTERFACE_ATTRIBUTE10;
2585 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE11 := srlv_data.STREAM_INTERFACE_ATTRIBUTE11;
2586 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE12 := srlv_data.STREAM_INTERFACE_ATTRIBUTE12;
2587 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE13 := srlv_data.STREAM_INTERFACE_ATTRIBUTE13;
2588 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE14 := srlv_data.STREAM_INTERFACE_ATTRIBUTE14;
2589 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE15 := srlv_data.STREAM_INTERFACE_ATTRIBUTE15;
2590 i := i + 1;
2591 END LOOP;
2592 i := 1;
2593 FOR yields_data in yields_data_csr(p_transaction_number)
2594 LOOP
2595 l_yields_tbl(i) := null;
2596 l_yields_tbl(i).yield_name := yields_data.yield_name;
2597 l_yields_tbl(i).value := yields_data.effective_pre_tax_yield;
2598
2599
2600 --l_yields_tbl(i).effective_after_tax_yield := yields_data.effective_after_tax_yield;
2601 --l_yields_tbl(i).nominal_pre_tax_yield := yields_data.nominal_pre_tax_yield;
2602 --l_yields_tbl(i).nominal_after_tax_yield := yields_data.nominal_after_tax_yield;
2603 l_yields_tbl(i).implicit_interest_rate := yields_data.implicit_interest_rate;
2604 l_yields_tbl(i).method := yields_data.method;
2605 l_yields_tbl(i).array_type := yields_data.array_type;
2606 l_yields_tbl(i).roe_type := yields_data.roe_type;
2607 l_yields_tbl(i).roe_base := yields_data.roe_base;
2608 l_yields_tbl(i).compounded_method := yields_data.compounded_method;
2609 l_yields_tbl(i).target_value := yields_data.target_value;
2610 l_yields_tbl(i).index_number := yields_data.index_number;
2611 l_yields_tbl(i).nominal_yn := yields_data.nominal_yn;
2612 l_yields_tbl(i).pre_tax_yn := yields_data.pre_tax_yn;
2613 i := i + 1;
2614 END LOOP;
2615 -- call the restructure api for processing results
2616 -- Start of wraper code generated automatically by Debug code generator for okl_solve_for_rent_pvt.process_results
2617 /*
2618 IF(L_DEBUG_ENABLED='Y') THEN
2619 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
2620 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
2621 END IF;
2622 IF(IS_DEBUG_PROCEDURE_ON) THEN
2623 BEGIN
2624 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call okl_solve_for_rent_pvt.process_results ');
2625 END;
2626 END IF;
2627 okl_solve_for_rent_pvt.process_results(p_api_version => l_api_version,
2628 p_init_msg_list => p_init_msg_list,
2629 p_chr_id => l_chr_id,
2630 p_trans_status => l_sis_code,
2631 p_trans_number => p_transaction_number,
2632 p_rent_tbl => l_srlv_tbl,
2633 p_yield_tbl => l_yields_tbl,
2634 x_return_status => l_return_status,
2635 x_msg_count => x_msg_count,
2636 x_msg_data => x_msg_data);
2637 IF(IS_DEBUG_PROCEDURE_ON) THEN
2638 BEGIN
2639 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call okl_solve_for_rent_pvt.process_results ');
2640 END;
2641 END IF;
2642 -- End of wraper code generated automatically by Debug code generator for okl_solve_for_rent_pvt.process_results
2643 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2644 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2645 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2646 RAISE G_EXCEPTION_ERROR;
2647 END IF;
2648 */
2649 -- update statuses in the inbound and outbound interface tables
2650 UPDATE_STATUSES(p_transaction_number => p_transaction_number
2651 ,x_return_status => l_return_status);
2652 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2653 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2654 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2655 RAISE G_EXCEPTION_ERROR;
2656 END IF;
2657 x_return_status := l_return_status;
2658 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2659 x_msg_data => x_msg_data);
2660 EXCEPTION
2661 WHEN G_EXCEPTION_ERROR THEN
2662 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2663 p_pkg_name => G_PKG_NAME,
2664 p_exc_name => G_EXC_NAME_ERROR,
2665 x_msg_count => x_msg_count,
2666 x_msg_data => x_msg_data,
2667 p_api_type => '_PVT');
2668 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2669 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2670 p_pkg_name => G_PKG_NAME,
2671 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
2672 x_msg_count => x_msg_count,
2673 x_msg_data => x_msg_data,
2674 p_api_type => '_PVT');
2675 WHEN OTHERS THEN
2676
2677 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2678 p_pkg_name => G_PKG_NAME,
2679 p_exc_name => G_EXC_NAME_OTHERS,
2680 x_msg_count => x_msg_count,
2681 x_msg_data => x_msg_data,
2682
2683 p_api_type => '_PVT');
2684 END PROCESS_QUOT_STRM_RESLTS;
2685 ------------------------------------------------------------------------------------------
2686 -- PROCEDURE
2687 ------------------------------------------------------------------------------------------
2688 PROCEDURE PROCESS_RENW_STRM_RESLTS(p_api_version IN NUMBER
2689 ,p_init_msg_list IN VARCHAR2
2690 ,p_transaction_number IN NUMBER
2691 ,x_return_status OUT NOCOPY VARCHAR2
2692 ,x_msg_count OUT NOCOPY NUMBER
2693 ,x_msg_data OUT NOCOPY VARCHAR2)
2694 IS
2695 l_return_status VARCHAR(1) := G_RET_STS_SUCCESS;
2696 l_api_name CONSTANT VARCHAR2(40) := 'PROCESS_RENW_STRM_RESLTS';
2697 l_api_version CONSTANT NUMBER := 1.0;
2698 l_srlv_tbl srlv_tbl_type;
2699 l_yields_tbl yields_tbl_type;
2700 l_sir_id NUMBER;
2701 i NUMBER;
2702 p_trqv_rec trqv_rec_type;
2703 x_trqv_rec trqv_rec_type;
2704 p_payment_tbl payment_tbl_type;
2705 l_khr_id NUMBER;
2706 l_object1_id1 okl_stream_interfaces.OBJECT1_ID1%TYPE;
2707 l_sis_code okl_stream_interfaces.SIS_CODE%TYPE;
2708 l_number_months NUMBER;
2709
2710 --Modified by kthiruva for the VR build
2711 CURSOR ppd_data_csr (p_trx_number NUMBER) IS
2712 SELECT
2713 SRLB.ID,
2714 SRLB.LEVEL_INDEX_NUMBER,
2715 SRLB.NUMBER_OF_PERIODS,
2716 SRLB.SIR_ID,
2717 SRLB.INDEX_NUMBER,
2718 SRLB.LEVEL_TYPE,
2719 SRLB.AMOUNT,
2720 SRLB.ADVANCE_OR_ARREARS,
2721 SRLB.PERIOD,
2722 SRLB.LOCK_LEVEL_STEP,
2723 SRLB.DAYS_IN_PERIOD,
2724 SRLB.FIRST_PAYMENT_DATE,
2725 SIFB.KHR_ID,
2726 SILB.KLE_ID
2727 FROM OKL_SIF_RET_LEVELS SRLB, OKL_SIF_RETS SIRB, OKL_STREAM_INTERFACES SIFB,
2728 OKL_SIF_LINES SILB
2729 WHERE SIFB.TRANSACTION_NUMBER = p_trx_number
2730 AND SIRB.TRANSACTION_NUMBER = SIFB.TRANSACTION_NUMBER
2731 AND SILB.SIF_ID = SIFB.ID
2732 AND SRLB.SIR_ID = SIRB.ID
2733 AND SRLB.INDEX_NUMBER = SILB.INDEX_NUMBER
2734 AND SRLB.LEVEL_TYPE in ('Payment','Principal')
2735 AND SRLB.LOCK_LEVEL_STEP = 'N';
2736
2737 CURSOR srlv_data_csr (p_trx_number NUMBER) IS
2738 SELECT
2739 OKL_SIF_RET_LEVELS.ID,
2740 OKL_SIF_RET_LEVELS.LEVEL_INDEX_NUMBER,
2741 OKL_SIF_RET_LEVELS.NUMBER_OF_PERIODS,
2742 OKL_SIF_RET_LEVELS.SIR_ID,
2743 OKL_SIF_RET_LEVELS.INDEX_NUMBER,
2744 OKL_SIF_RET_LEVELS.LEVEL_TYPE,
2745 OKL_SIF_RET_LEVELS.AMOUNT,
2746 OKL_SIF_RET_LEVELS.ADVANCE_OR_ARREARS,
2747 OKL_SIF_RET_LEVELS.PERIOD,
2748 OKL_SIF_RET_LEVELS.LOCK_LEVEL_STEP,
2749 OKL_SIF_RET_LEVELS.DAYS_IN_PERIOD,
2750 OKL_SIF_RET_LEVELS.FIRST_PAYMENT_DATE,
2751 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE1,
2752 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE2,
2753 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE3,
2754 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE4,
2755 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE5,
2756 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE6,
2757 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE7,
2758 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE8,
2759 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE9,
2760 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE10,
2761 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE11,
2762 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE12,
2763 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE13,
2764 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE14,
2765 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE15
2766 FROM OKL_SIF_RET_LEVELS, OKL_SIF_RETS
2767 WHERE OKL_SIF_RETS.transaction_number = p_trx_number
2768 AND OKL_SIF_RETS.INDEX_NUMBER = 0
2769 AND OKL_SIF_RET_LEVELS.SIR_ID = OKL_SIF_RETS.ID
2770 AND OKL_SIF_RET_LEVELS.LEVEL_TYPE = 'Payment'
2771 AND OKL_SIF_RET_LEVELS.LOCK_LEVEL_STEP = 'N';
2772 CURSOR sirv_data_csr (p_trx_number NUMBER) IS
2773 SELECT
2774 ID
2775 FROM okl_sif_rets
2776 WHERE okl_sif_rets.transaction_number = p_trx_number;
2777 CURSOR sifv_data_csr (p_trx_number IN NUMBER) IS
2778 SELECT
2779 OBJECT1_ID1,
2780 SIS_CODE
2781 FROM OKL_STREAM_INTERFACES
2782 WHERE TRANSACTION_NUMBER = p_trx_number;
2783
2784 --Added by kthiruva for VR build
2785 --This cursor checks if the adjusting stub returned by the Inbound XML
2786 --was created for a stub or for a period ic payment
2787 CURSOR check_stub_csr(p_chr_id NUMBER,
2788 p_cle_id NUMBER,
2789 p_date DATE,
2790 p_slh_id NUMBER)
2791 IS
2792 SELECT TO_NUMBER(crl.rule_information7) stub_days
2793 FROM okc_rule_groups_b crg,
2794 okc_rules_b crl
2795 WHERE crl.rgp_id = crg.id
2796 AND crl.object2_id1 = p_slh_id
2797 AND crg.rgd_code = 'LALEVL'
2798 AND crl.rule_information_category = 'LASLL'
2799 AND crg.dnz_chr_id = p_chr_id
2800 AND crg.cle_id = p_cle_id
2801 AND FND_DATE.canonical_to_date(crl.rule_information2)+TO_NUMBER(crl.rule_information7) = p_date;
2802
2803 --Added by kthiruva on 12-Dec-2005
2804 --This cursor obtains the SLH id of the payment corresponding to a kle_id
2805 --Bug 4878162 - Start of Changes
2806 CURSOR get_slh_csr(p_chr_id NUMBER,
2807 p_cle_id NUMBER)
2808 IS
2809 SELECT crl.id slh_id
2810 FROM okc_rule_groups_b crg,
2811 okc_rules_b crl
2812 WHERE crl.rgp_id = crg.id
2813 AND crg.rgd_code = 'LALEVL'
2814 AND crl.rule_information_category = 'LASLH'
2815 AND crg.dnz_chr_id = p_chr_id
2816 AND crg.cle_id = p_cle_id
2817 ORDER BY crl.rule_information1;
2818
2819 CURSOR get_freq_csr(p_chr_id NUMBER,
2820 p_cle_id NUMBER,
2821 p_slh_id NUMBER)
2822 IS
2823 SELECT crl.object1_id1 frequency
2824 FROM okc_rule_groups_b crg,
2825 okc_rules_b crl
2826 WHERE crl.rgp_id = crg.id
2827 AND crl.object2_id1 = p_slh_id
2828 AND crg.rgd_code = 'LALEVL'
2829 AND crl.rule_information_category = 'LASLL'
2830 AND crg.dnz_chr_id = p_chr_id
2831 AND crg.cle_id = p_cle_id;
2832
2833 l_slh_id NUMBER;
2834 l_frequency VARCHAR2(1);
2835 --Bug 4878162 - End of Changes
2836
2837 l_end_accrual_date DATE;
2838 l_stub_days NUMBER;
2839 --kthiruva - End of Changes for VR build
2840
2841 BEGIN
2842 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
2843 p_pkg_name => 'OKL_PROCESS_STREAMS_PVT',
2844 p_init_msg_list => p_init_msg_list,
2845 l_api_version => l_api_version,
2846 p_api_version => p_api_version,
2847 p_api_type => '_PVT',
2848 x_return_status => l_return_status);
2849 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2850 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2851 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2852 RAISE G_EXCEPTION_ERROR;
2853 END IF;
2854 --Before Processing Stream Results check for any exceptions in the Inbound Interface Tables
2855 GENERATE_ERROR_LOGFILE(p_transaction_number => p_transaction_number
2856 ,x_return_status => l_return_status);
2857 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2858 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2859 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2860 RAISE G_EXCEPTION_ERROR;
2861 END IF;
2862 -- update statuses in the inbound and outbound interface tables
2863 UPDATE_STATUSES(p_transaction_number => p_transaction_number
2864 ,x_return_status => l_return_status);
2865 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2866 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2867 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2868 RAISE G_EXCEPTION_ERROR;
2869 END IF;
2870 -- check if request is for Principal Paydown
2871 OPEN sifv_data_csr(p_transaction_number);
2872 FETCH sifv_data_csr INTO l_object1_id1, l_sis_code;
2873 CLOSE sifv_data_csr;
2874
2875 IF (okl_cs_principal_paydown_pvt.check_if_ppd(l_object1_id1)='Y') THEN
2876 i := 1;
2877 FOR ppd_data in ppd_data_csr(p_transaction_number)
2878 LOOP
2879 l_khr_id := ppd_data.KHR_ID;
2880 p_payment_tbl(i).khr_id := ppd_data.KHR_ID;
2881 p_payment_tbl(i).kle_id := ppd_data.KLE_ID;
2882 --Modified by kthiruva on 12-Dec-2005
2883 -- A value of 'Y' for advance_or_arrears denotes that
2884 --the paynent is in Arrears
2885 --Bug 4878162 - Start of Changes
2886 IF (ppd_data.ADVANCE_OR_ARREARS = 'Y') THEN
2887 p_payment_tbl(i).arrears_yn := 'Y';
2888 ELSE
2889 p_payment_tbl(i).arrears_yn := 'N';
2890 END IF;
2891 --Bug 4878162 - End of Changes
2892 okl_st_code_conversions.reverse_translate_periodicity(
2893 ppd_data.PERIOD,
2894 p_payment_tbl(i).frequency);
2895 IF (ppd_data.PERIOD = 'Stub') THEN
2896 IF (ppd_data.ADVANCE_OR_ARREARS = 'N') THEN
2897 l_end_accrual_date := ppd_data.first_payment_date + ppd_data.days_in_period;
2898 ELSE
2899 l_end_accrual_date := ppd_data.first_payment_date ;
2900 END IF;
2901 --Added by kthiruva on 12-Dec-2005
2902 --Obtaining the payment header information
2903 --Bug 4878162 - Start of Changes
2904 OPEN get_slh_csr(ppd_data.khr_id,
2905 ppd_data.kle_id);
2906 FETCH get_slh_csr INTO l_slh_id;
2907 IF get_slh_csr%FOUND THEN
2908 --A stub encountered could be the adjusting stub created for
2909 --either a periodic payment or for a stub.
2910 OPEN check_Stub_csr(ppd_data.khr_id,
2911 ppd_data.kle_id,
2912 l_end_accrual_date,
2913 l_slh_id);
2914 FETCH check_stub_csr INTO l_stub_days;
2915 --If the cursor returns a value then the adjusting stub is due to a stub in
2916 --the original payment plan.
2917 --Else it is a stub created for a periodic payment plan.
2918 IF check_stub_csr%FOUND THEN
2919 p_payment_tbl(i).stub_days := l_stub_days;
2920 p_payment_tbl(i).stub_amount := ppd_data.AMOUNT;
2921 ELSE
2922 p_payment_tbl(i).periods := ppd_data.NUMBER_OF_PERIODS;
2923 p_payment_tbl(i).amount := ppd_data.AMOUNT;
2924 OPEN get_freq_csr(ppd_data.khr_id,
2925 ppd_data.kle_id,
2926 l_slh_id);
2927 FETCH get_freq_csr INTO l_frequency;
2928 IF get_freq_csr%FOUND THEN
2929 p_payment_tbl(i).frequency := l_frequency;
2930 END IF;
2931 CLOSE get_freq_csr;
2932 END IF;
2933 --Added by kthiruva on 02-Dec-2005
2934 --Bug 4777531 - Start of Changes
2935 CLOSE check_Stub_csr;
2936 --Bug 4777531 - End of changes
2937 END IF;
2938 CLOSE get_slh_csr;
2939 --Bug 4878162 - End of Changes
2940 ELSE
2941 p_payment_tbl(i).periods := ppd_data.NUMBER_OF_PERIODS;
2942 p_payment_tbl(i).amount := ppd_data.AMOUNT;
2943 END IF;
2944 -- Bug 4047717 back out payment dates if in arrears to match SLL dates
2945 IF (p_payment_tbl(i).arrears_yn = 'Y' AND
2946 p_payment_tbl(i).frequency <> 'T') THEN
2947 IF (p_payment_tbl(i).frequency = 'M') THEN
2948 l_number_months := -1;
2949 ELSIF (p_payment_tbl(i).frequency = 'Q') THEN
2950 l_number_months := -3;
2951 ELSIF (p_payment_tbl(i).frequency = 'S') THEN
2952 l_number_months := -6;
2953 ELSIF (p_payment_tbl(i).frequency = 'A') THEN
2954 l_number_months := -12;
2955 END IF;
2956 p_payment_tbl(i).start_date := ADD_MONTHS(ppd_data.FIRST_PAYMENT_DATE,l_number_months);
2957 ELSIF (p_payment_tbl(i).arrears_yn = 'Y' AND
2958 p_payment_tbl(i).frequency = 'T') THEN
2959 p_payment_tbl(i).start_date := ppd_data.FIRST_PAYMENT_DATE - p_payment_tbl(i).stub_days;
2960 ELSE
2961 p_payment_tbl(i).start_date := ppd_data.FIRST_PAYMENT_DATE;
2962 END IF;
2963 i := i + 1;
2964 END LOOP;
2965 okl_cs_principal_paydown_pvt.store_esg_payments(p_api_version => p_api_version
2966 ,p_init_msg_list => p_init_msg_list
2967 ,x_return_status => l_return_status
2968 ,x_msg_count => x_msg_count
2969 ,x_msg_data => x_msg_data
2970 ,p_ppd_request_id => l_object1_id1
2971 ,p_ppd_khr_id => l_khr_id
2972 ,p_payment_tbl => p_payment_tbl);
2973 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2974 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2975 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2976 RAISE G_EXCEPTION_ERROR;
2977 END IF;
2978 ELSE
2979 -- fetch records for the RENT LEVELS
2980 FOR srlv_data in srlv_data_csr(p_transaction_number)
2981 LOOP
2982 p_trqv_rec.amount := srlv_data.AMOUNT;
2983 END LOOP;
2984
2985 FOR sifv_data in sifv_data_csr(p_transaction_number)
2986 LOOP
2987 p_trqv_rec.id := sifv_data.OBJECT1_ID1;
2988 p_trqv_rec.request_status_code := sifv_data.SIS_CODE;
2989 END LOOP;
2990 -- call the restructure api for processing results
2991 -- Start of wraper code generated automatically by Debug code generator for okl_cs_lease_renewal_pub.update_lrnw_request
2992 IF(L_DEBUG_ENABLED='Y') THEN
2993 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
2994 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
2995 END IF;
2996 IF(IS_DEBUG_PROCEDURE_ON) THEN
2997 BEGIN
2998 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call okl_cs_lease_renewal_pub.update_lrnw_request ');
2999 END;
3000 END IF;
3001 okl_cs_lease_renewal_pub.update_lrnw_request(p_api_version => p_api_version
3002 ,p_init_msg_list => p_init_msg_list
3003 ,x_return_status => l_return_status
3004 ,x_msg_count => x_msg_count
3005 ,x_msg_data => x_msg_data
3006 ,p_trqv_rec => p_trqv_rec
3007 ,x_trqv_rec => x_trqv_rec);
3008 IF(IS_DEBUG_PROCEDURE_ON) THEN
3009 BEGIN
3010 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call okl_cs_lease_renewal_pub.update_lrnw_request ');
3011 END;
3012 END IF;
3013 -- End of wraper code generated automatically by Debug code generator for okl_cs_lease_renewal_pub.update_lrnw_request
3014 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3015 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3016 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3017 RAISE G_EXCEPTION_ERROR;
3018 END IF;
3019 END IF;
3020 x_return_status := l_return_status;
3021 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3022 x_msg_data => x_msg_data);
3023 EXCEPTION
3024 WHEN G_EXCEPTION_ERROR THEN
3025 --Added by kthiruva on 02-Dec-2005
3026 --Bug 4777531 - Start of Changes
3027 IF(check_stub_csr%ISOPEN)
3028 THEN
3029 CLOSE check_stub_csr;
3030 END IF;
3031 --Bug 4777531 - End of Changes
3032 --Added by kthiruva on 02-Dec-2005
3033 --Bug 4878162 - Start of Changes
3034 IF(get_slh_csr%ISOPEN)
3035 THEN
3036 CLOSE get_slh_csr;
3037 END IF;
3038 IF(get_freq_csr%ISOPEN)
3039 THEN
3040 CLOSE get_freq_csr;
3041 END IF;
3042 --Bug 4878162 - End of Changes
3043 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3044 p_pkg_name => G_PKG_NAME,
3045 p_exc_name => G_EXC_NAME_ERROR,
3046 x_msg_count => x_msg_count,
3047 x_msg_data => x_msg_data,
3048 p_api_type => '_PVT');
3049 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
3050 --Added by kthiruva on 02-Dec-2005
3051 --Bug 4777531 - Start of Changes
3052 IF(check_stub_csr%ISOPEN)
3053 THEN
3054 CLOSE check_stub_csr;
3055 END IF;
3056 --Bug 4777531 - End of Changes
3057 --Added by kthiruva on 02-Dec-2005
3058 --Bug 4878162 - Start of Changes
3059 IF(get_slh_csr%ISOPEN)
3060 THEN
3061 CLOSE get_slh_csr;
3062 END IF;
3063 IF(get_freq_csr%ISOPEN)
3064 THEN
3065 CLOSE get_freq_csr;
3066 END IF;
3067 --Bug 4878162 - End of Changes
3068 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3069 p_pkg_name => G_PKG_NAME,
3070 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
3071 x_msg_count => x_msg_count,
3072 x_msg_data => x_msg_data,
3073 p_api_type => '_PVT');
3074 WHEN OTHERS THEN
3075 --Added by kthiruva on 02-Dec-2005
3076 --Bug 4777531 - Start of Changes
3077 IF(check_stub_csr%ISOPEN)
3078 THEN
3079 CLOSE check_stub_csr;
3080 END IF;
3081 --Bug 4777531 - End of Changes
3082 --Added by kthiruva on 02-Dec-2005
3083 --Bug 4878162 - Start of Changes
3084 IF(get_slh_csr%ISOPEN)
3085 THEN
3086 CLOSE get_slh_csr;
3087 END IF;
3088 IF(get_freq_csr%ISOPEN)
3089 THEN
3090 CLOSE get_freq_csr;
3091 END IF;
3092 --Bug 4878162 - End of Changes
3093 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3094 p_pkg_name => G_PKG_NAME,
3095 p_exc_name => G_EXC_NAME_OTHERS,
3096 x_msg_count => x_msg_count,
3097 x_msg_data => x_msg_data,
3098 p_api_type => '_PVT');
3099 END PROCESS_RENW_STRM_RESLTS;
3100
3101 PROCEDURE reorganise_payment_tbl(p_srlv_tbl IN srlv_tbl_type
3102 ,x_srlv_tbl OUT NOCOPY srlv_tbl_type)
3103 IS
3104 l_srlv_tbl srlv_tbl_type := p_srlv_tbl;
3105 l_temp_srlv_tbl srlv_tbl_type ;
3106 i NUMBER := 0;
3107 j NUMBER := 0;
3108 BEGIN
3109 j := l_srlv_tbl.FIRST;
3110 IF l_srlv_tbl.COUNT > 0 THEN
3111 --Assigning the first record as is
3112 l_temp_srlv_tbl(i).ID := l_srlv_tbl(j).ID;
3113 l_temp_srlv_tbl(i).LEVEL_INDEX_NUMBER := l_srlv_tbl(j).LEVEL_INDEX_NUMBER;
3114 l_temp_srlv_tbl(i).NUMBER_OF_PERIODS := l_srlv_tbl(j).NUMBER_OF_PERIODS;
3115 l_temp_srlv_tbl(i).SIR_ID := l_srlv_tbl(j).SIR_ID;
3116 l_temp_srlv_tbl(i).INDEX_NUMBER := l_srlv_tbl(j).INDEX_NUMBER;
3117 l_temp_srlv_tbl(i).LEVEL_TYPE := l_srlv_tbl(j).LEVEL_TYPE;
3118 l_temp_srlv_tbl(i).AMOUNT := l_srlv_tbl(j).AMOUNT;
3119 l_temp_srlv_tbl(i).ADVANCE_OR_ARREARS := l_srlv_tbl(j).ADVANCE_OR_ARREARS;
3120 l_temp_srlv_tbl(i).PERIOD := l_srlv_tbl(j).PERIOD;
3121 l_temp_srlv_tbl(i).LOCK_LEVEL_STEP := l_srlv_tbl(j).LOCK_LEVEL_STEP;
3122 l_temp_srlv_tbl(i).DAYS_IN_PERIOD := l_srlv_tbl(j).DAYS_IN_PERIOD;
3123 l_temp_srlv_tbl(i).FIRST_PAYMENT_DATE := l_srlv_tbl(j).FIRST_PAYMENT_DATE;
3124 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE1 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE1;
3125 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE2 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE2;
3126 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE3 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE3;
3127 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE4 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE4;
3128 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE5 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE5;
3129 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE6 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE6;
3130 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE7 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE7;
3131 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE8 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE8;
3132 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE9 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE9;
3133 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE10 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE10;
3134 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE11 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE11;
3135 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE12 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE12;
3136 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE13 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE13;
3137 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE14 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE14;
3138 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE15 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE15;
3139 END IF;
3140
3141 LOOP
3142 j := l_srlv_tbl.NEXT(j);
3143 --Added by kthiruva on 15-Jun-2006 for Bug 5286917
3144 EXIT WHEN j is NULL;
3145 --End of Changes
3146 IF (l_temp_srlv_tbl(i).INDEX_NUMBER = l_srlv_tbl(j).INDEX_NUMBER AND
3147 l_temp_srlv_tbl(i).AMOUNT = l_srlv_tbl(j).AMOUNT AND
3148 l_temp_srlv_tbl(i).PERIOD = l_srlv_tbl(j).PERIOD) THEN
3149 l_temp_srlv_tbl(i).NUMBER_OF_PERIODS := l_temp_srlv_tbl(i).NUMBER_OF_PERIODS +
3150 l_srlv_tbl(j).NUMBER_OF_PERIODS;
3151 ELSE
3152 i := i + 1;
3153 l_temp_srlv_tbl(i).ID := l_srlv_tbl(j).ID;
3154 l_temp_srlv_tbl(i).LEVEL_INDEX_NUMBER := l_srlv_tbl(j).LEVEL_INDEX_NUMBER;
3155 l_temp_srlv_tbl(i).NUMBER_OF_PERIODS := l_srlv_tbl(j).NUMBER_OF_PERIODS;
3156 l_temp_srlv_tbl(i).SIR_ID := l_srlv_tbl(j).SIR_ID;
3157 l_temp_srlv_tbl(i).INDEX_NUMBER := l_srlv_tbl(j).INDEX_NUMBER;
3158 l_temp_srlv_tbl(i).LEVEL_TYPE := l_srlv_tbl(j).LEVEL_TYPE;
3159 l_temp_srlv_tbl(i).AMOUNT := l_srlv_tbl(j).AMOUNT;
3160 l_temp_srlv_tbl(i).ADVANCE_OR_ARREARS := l_srlv_tbl(j).ADVANCE_OR_ARREARS;
3161 l_temp_srlv_tbl(i).PERIOD := l_srlv_tbl(j).PERIOD;
3162 l_temp_srlv_tbl(i).LOCK_LEVEL_STEP := l_srlv_tbl(j).LOCK_LEVEL_STEP;
3163 l_temp_srlv_tbl(i).DAYS_IN_PERIOD := l_srlv_tbl(j).DAYS_IN_PERIOD;
3164 l_temp_srlv_tbl(i).FIRST_PAYMENT_DATE := l_srlv_tbl(j).FIRST_PAYMENT_DATE;
3165 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE1 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE1;
3166 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE2 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE2;
3167 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE3 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE3;
3168 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE4 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE4;
3169 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE5 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE5;
3170 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE6 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE6;
3171 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE7 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE7;
3172 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE8 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE8;
3173 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE9 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE9;
3174 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE10 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE10;
3175 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE11 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE11;
3176 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE12 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE12;
3177 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE13 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE13;
3178 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE14 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE14;
3179 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE15 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE15;
3180 END IF;
3181 END LOOP;
3182 x_srlv_tbl := l_temp_srlv_tbl;
3183 END reorganise_payment_tbl;
3184
3185 ------------------------------------------------------------------------------------------
3186 -- PROCEDURE
3187 ------------------------------------------------------------------------------------------
3188 PROCEDURE PROCESS_VIRP_STRM_RESLTS(p_api_version IN NUMBER
3189 ,p_init_msg_list IN VARCHAR2
3190 ,p_transaction_number IN NUMBER
3191 ,x_return_status OUT NOCOPY VARCHAR2
3192 ,x_msg_count OUT NOCOPY NUMBER
3193 ,x_msg_data OUT NOCOPY VARCHAR2)
3194 IS
3195 l_return_status VARCHAR(1) := G_RET_STS_SUCCESS;
3196 l_api_name CONSTANT VARCHAR2(40) := 'PROCESS_VIRP_STRM_RESLTS';
3197 l_api_version CONSTANT NUMBER := 1.0;
3198 l_srlv_tbl srlv_tbl_type;
3199 --Added by kthiruva for Bug 5161075
3200 l_tmp_srlv_tbl srlv_tbl_type;
3201 l_yields_tbl yields_tbl_type;
3202 l_sir_id NUMBER;
3203 l_chr_id NUMBER;
3204 l_transaction_status varchar2(30);
3205 i NUMBER;
3206
3207 --Added SIF_ID and SIL_ID by bkatraga for bug 14377607
3208 --Added ORDER BY clause by bkatraga for bug 14352560
3209 --Modified by kthiruva on 13-Apr-2006 for Bug 5090060
3210 -- Where condition added
3211 CURSOR srlv_data_csr (p_sir_id NUMBER,
3212 p_trx_number NUMBER) IS
3213 SELECT
3214 SRLB.ID,
3215 SRLB.LEVEL_INDEX_NUMBER,
3216 SRLB.NUMBER_OF_PERIODS,
3217 SRLB.SIR_ID,
3218 SRLB.INDEX_NUMBER,
3219 SRLB.LEVEL_TYPE,
3220 SRLB.AMOUNT,
3221 SRLB.ADVANCE_OR_ARREARS,
3222 SRLB.PERIOD,
3223 SRLB.LOCK_LEVEL_STEP,
3224 SRLB.DAYS_IN_PERIOD,
3225 SRLB.FIRST_PAYMENT_DATE,
3226 SRLB.STREAM_INTERFACE_ATTRIBUTE1,
3227 SRLB.STREAM_INTERFACE_ATTRIBUTE2,
3228 SRLB.STREAM_INTERFACE_ATTRIBUTE3,
3229 SRLB.STREAM_INTERFACE_ATTRIBUTE4,
3230 SRLB.STREAM_INTERFACE_ATTRIBUTE5,
3231 SRLB.STREAM_INTERFACE_ATTRIBUTE6,
3232 SRLB.STREAM_INTERFACE_ATTRIBUTE7,
3233 SRLB.STREAM_INTERFACE_ATTRIBUTE8,
3234 SRLB.STREAM_INTERFACE_ATTRIBUTE9,
3235 SRLB.STREAM_INTERFACE_ATTRIBUTE10,
3236 SRLB.STREAM_INTERFACE_ATTRIBUTE11,
3237 SRLB.STREAM_INTERFACE_ATTRIBUTE12,
3238 SRLB.STREAM_INTERFACE_ATTRIBUTE13,
3239 SRLB.STREAM_INTERFACE_ATTRIBUTE14,
3240 SRLB.STREAM_INTERFACE_ATTRIBUTE15,
3241 SIFB.KHR_ID,
3242 SILB.KLE_ID,
3243 SIFB.ID SIF_ID,
3244 SILB.ID SIL_ID
3245 FROM OKL_SIF_RET_LEVELS SRLB, OKL_SIF_RETS SIRB,
3246 OKL_STREAM_INTERFACES SIFB,OKL_SIF_LINES SILB
3247 WHERE SRLB.SIR_ID = p_sir_id
3248 AND SIFB.TRANSACTION_NUMBER = p_trx_number
3249 AND SIRB.TRANSACTION_NUMBER = SIFB.TRANSACTION_NUMBER
3250 AND SILB.SIF_ID = SIFB.ID
3251 AND SRLB.SIR_ID = SIRB.ID
3252 AND SRLB.INDEX_NUMBER = SILB.INDEX_NUMBER
3253 AND SRLB.LEVEL_TYPE IN ('Payment','Principal')
3254 AND SRLB.LOCK_LEVEL_STEP = 'N'
3255 ORDER BY KLE_ID,LEVEL_INDEX_NUMBER;
3256
3257 CURSOR sirv_data_csr (p_trx_number NUMBER) IS
3258 SELECT
3259 ID
3260 FROM okl_sif_rets
3261 WHERE okl_sif_rets.transaction_number = p_trx_number
3262 AND OKL_SIF_RETS.INDEX_NUMBER = 0;
3263 CURSOR sifv_data_csr (p_trx_number IN NUMBER) IS
3264 SELECT
3265 SIS_CODE,
3266 KHR_ID
3267 FROM OKL_STREAM_INTERFACES
3268 WHERE TRANSACTION_NUMBER = p_trx_number;
3269
3270 --Added by kthiruva for Bug 5161075
3271 --This cursor checks if the adjusting stub returned by the Inbound XML
3272 --was created for a stub or for a period ic payment
3273 CURSOR check_stub_csr(p_chr_id NUMBER,
3274 p_cle_id NUMBER,
3275 p_date DATE,
3276 p_slh_id NUMBER)
3277 IS
3278 SELECT TO_NUMBER(crl.rule_information7) stub_days
3279 FROM okc_rule_groups_b crg,
3280 okc_rules_b crl
3281 WHERE crl.rgp_id = crg.id
3282 AND crl.object2_id1 = p_slh_id
3283 AND crg.rgd_code = 'LALEVL'
3284 AND crl.rule_information_category = 'LASLL'
3285 AND crg.dnz_chr_id = p_chr_id
3286 AND crg.cle_id = p_cle_id
3287 AND FND_DATE.canonical_to_date(crl.rule_information2)+TO_NUMBER(crl.rule_information7) = p_date;
3288
3289 --This cursor obtains the SLH id of the payment corresponding to a kle_id
3290 CURSOR get_slh_csr(p_chr_id NUMBER,
3291 p_cle_id NUMBER)
3292 IS
3293 SELECT crl.id slh_id
3294 FROM okc_rule_groups_b crg,
3295 okc_rules_b crl
3296 WHERE crl.rgp_id = crg.id
3297 AND crg.rgd_code = 'LALEVL'
3298 AND crl.rule_information_category = 'LASLH'
3299 AND crg.dnz_chr_id = p_chr_id
3300 AND crg.cle_id = p_cle_id
3301 ORDER BY crl.rule_information1;
3302
3303 CURSOR get_freq_csr(p_chr_id NUMBER,
3304 p_cle_id NUMBER,
3305 p_slh_id NUMBER)
3306 IS
3307 SELECT crl.object1_id1 frequency
3308 FROM okc_rule_groups_b crg,
3309 okc_rules_b crl
3310 WHERE crl.rgp_id = crg.id
3311 AND crl.object2_id1 = p_slh_id
3312 AND crg.rgd_code = 'LALEVL'
3313 AND crl.rule_information_category = 'LASLL'
3314 AND crg.dnz_chr_id = p_chr_id
3315 AND crg.cle_id = p_cle_id;
3316
3317 l_slh_id NUMBER;
3318 l_frequency VARCHAR2(1);
3319 l_end_accrual_date DATE;
3320 l_stub_days NUMBER;
3321 --kthiruva - End of Changes for Bug 5161075
3322
3323 --Added by bkatraga for bug 14377607
3324 CURSOR get_start_date_csr(P_SIF_ID NUMBER,
3325 P_SIL_ID NUMBER,
3326 P_LEVEL_INDX_NUM NUMBER)
3327 IS
3328 SELECT TRUNC(DATE_START)
3329 FROM OKL_SIF_FEES
3330 WHERE DESCRIPTION IN('RENT', 'PRINCIPAL_PAYMENT')
3331 AND LEVEL_INDEX_NUMBER = P_LEVEL_INDX_NUM
3332 AND SIL_ID = P_SIL_ID
3333 AND SIF_ID = P_SIF_ID;
3334
3335 l_start_date DATE;
3336 --end bkatraga for bug 14377607
3337
3338 --Added by kthiruva on 11-Nov-2005 for the VR build
3339 --Bug 4726209 - Start of Changes
3340 l_payment_start_date DATE;
3341 l_number_of_months NUMBER := 0;
3342 --Bug 4726209 - End of Changes
3343 --Added by kthiruva on 13-Apr-2006 for Bug 5090060
3344 level_indx_count NUMBER := 1;
3345 asset_indx_number NUMBER ;
3346
3347 --Added by bkatraga for bug 13447258
3348 l_csm_loan_level_tbl okl_create_streams_pvt.csm_loan_level_tbl_type;
3349 l_child_trx_id NUMBER;
3350 --end bkatraga
3351
3352 BEGIN
3353 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
3354 p_pkg_name => 'OKL_PROCESS_STREAMS_PVT',
3355 p_init_msg_list => p_init_msg_list,
3356 l_api_version => l_api_version,
3357 p_api_version => p_api_version,
3358 p_api_type => '_PVT',
3359 x_return_status => l_return_status);
3360 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3361 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3362 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3363 RAISE G_EXCEPTION_ERROR;
3364 END IF;
3365 --Before Processing Stream Results check for any exceptions in the Inbound Interface Tables
3366 GENERATE_ERROR_LOGFILE(p_transaction_number => p_transaction_number
3367 ,x_return_status => l_return_status);
3368 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3369 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3370 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3371 RAISE G_EXCEPTION_ERROR;
3372 END IF;
3373 -- update statuses in the inbound and outbound interface tables
3374 UPDATE_STATUSES(p_transaction_number => p_transaction_number
3375 ,x_return_status => l_return_status);
3376 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3377 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3378 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3379 RAISE G_EXCEPTION_ERROR;
3380 END IF;
3381 -- fetch records from
3382 FOR sirv_data in sirv_data_csr(p_transaction_number)
3383 LOOP
3384 l_sir_id := sirv_data.id;
3385 END LOOP;
3386 i := 1;
3387 FOR srlv_data in srlv_data_csr(l_sir_id,p_transaction_number)
3388 LOOP
3389 l_srlv_tbl(i) := null;
3390 l_srlv_tbl(i).ID := srlv_data.ID;
3391 l_srlv_tbl(i).LEVEL_INDEX_NUMBER := srlv_data.LEVEL_INDEX_NUMBER;
3392 l_srlv_tbl(i).NUMBER_OF_PERIODS := srlv_data.NUMBER_OF_PERIODS;
3393 l_srlv_tbl(i).SIR_ID := srlv_data.SIR_ID;
3394 l_srlv_tbl(i).INDEX_NUMBER := srlv_data.INDEX_NUMBER;
3395 l_srlv_tbl(i).LEVEL_TYPE := srlv_data.LEVEL_TYPE;
3396 l_srlv_tbl(i).AMOUNT := srlv_data.AMOUNT;
3397 l_srlv_tbl(i).ADVANCE_OR_ARREARS := srlv_data.ADVANCE_OR_ARREARS;
3398 l_srlv_tbl(i).PERIOD := srlv_data.PERIOD;
3399 l_srlv_tbl(i).LOCK_LEVEL_STEP := srlv_data.LOCK_LEVEL_STEP;
3400 l_srlv_tbl(i).DAYS_IN_PERIOD := srlv_data.DAYS_IN_PERIOD;
3401
3402 --Added by bkatraga for bug 14377607
3403 OPEN get_start_date_csr(srlv_data.SIF_ID,
3404 srlv_data.SIL_ID,
3405 srlv_data.LEVEL_INDEX_NUMBER);
3406 FETCH get_start_date_csr INTO l_start_date;
3407 CLOSE get_start_date_csr;
3408
3409 IF (srlv_data.PERIOD = 'Stub') THEN
3410 --Obtaining the payment header information
3411 OPEN get_slh_csr(srlv_data.khr_id,
3412 srlv_data.kle_id);
3413 FETCH get_slh_csr INTO l_slh_id;
3414 IF get_slh_csr%FOUND THEN
3415 OPEN check_stub_csr(srlv_data.khr_id,
3416 srlv_data.kle_id,
3417 l_start_date,
3418 l_slh_id);
3419 FETCH check_stub_csr INTO l_stub_days;
3420 CLOSE check_Stub_csr;
3421 l_srlv_tbl(i).DAYS_IN_PERIOD := l_stub_days;
3422 l_srlv_tbl(i).FIRST_PAYMENT_DATE := l_start_date - l_stub_days;
3423 END IF;
3424 CLOSE get_slh_csr;
3425 ELSE
3426 l_srlv_tbl(i).FIRST_PAYMENT_DATE := l_start_date;
3427 END IF;
3428 --end bkatraga for bug 14377607
3429
3430 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE1 := srlv_data.STREAM_INTERFACE_ATTRIBUTE1;
3431 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE2 := srlv_data.STREAM_INTERFACE_ATTRIBUTE2;
3432 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE3 := srlv_data.STREAM_INTERFACE_ATTRIBUTE3;
3433 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE4 := srlv_data.STREAM_INTERFACE_ATTRIBUTE4;
3434 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE5 := srlv_data.STREAM_INTERFACE_ATTRIBUTE5;
3435 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE6 := srlv_data.STREAM_INTERFACE_ATTRIBUTE6;
3436 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE7 := srlv_data.STREAM_INTERFACE_ATTRIBUTE7;
3437 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE8 := srlv_data.STREAM_INTERFACE_ATTRIBUTE8;
3438 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE9 := srlv_data.STREAM_INTERFACE_ATTRIBUTE9;
3439 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE10 := srlv_data.STREAM_INTERFACE_ATTRIBUTE10;
3440 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE11 := srlv_data.STREAM_INTERFACE_ATTRIBUTE11;
3441 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE12 := srlv_data.STREAM_INTERFACE_ATTRIBUTE12;
3442 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE13 := srlv_data.STREAM_INTERFACE_ATTRIBUTE13;
3443 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE14 := srlv_data.STREAM_INTERFACE_ATTRIBUTE14;
3444 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE15 := srlv_data.STREAM_INTERFACE_ATTRIBUTE15;
3445 i := i + 1;
3446 END LOOP;
3447
3448 asset_indx_number := l_srlv_tbl(l_srlv_tbl.FIRST).index_number;
3449 FOR i in l_srlv_tbl.FIRST..l_srlv_tbl.LAST
3450 LOOP
3451 IF (asset_indx_number = l_srlv_tbl(i).INDEX_NUMBER) THEN
3452 l_srlv_tbl(i).LEVEL_INDEX_NUMBER := level_indx_count;
3453 level_indx_count := level_indx_count + 1;
3454 ELSE
3455 asset_indx_number := l_srlv_tbl(i).INDEX_NUMBER;
3456 level_indx_count := 1;
3457 l_srlv_tbl(i).LEVEL_INDEX_NUMBER := level_indx_count;
3458 level_indx_count := level_indx_count + 1;
3459 END IF;
3460 END LOOP;
3461
3462 i := 1;
3463 FOR sifv_data in sifv_data_csr(p_transaction_number)
3464 LOOP
3465 l_chr_id := sifv_data.khr_id;
3466 l_transaction_status := sifv_data.SIS_CODE;
3467 END LOOP;
3468
3469 --Reorganising the payment table
3470 reorganise_payment_tbl(p_srlv_tbl => l_srlv_tbl,
3471 x_srlv_tbl => l_tmp_srlv_tbl);
3472
3473
3474 -- call the restructure api for processing results
3475 -- Start of wraper code generated automatically by Debug code generator for OKL_VARIABLE_INTEREST_PUB.var_int_rent_level
3476
3477 IF(L_DEBUG_ENABLED='Y') THEN
3478 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
3479 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
3480 END IF;
3481 IF(IS_DEBUG_PROCEDURE_ON) THEN
3482 BEGIN
3483 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_VARIABLE_INTEREST_PUB.var_int_rent_level ');
3484 END;
3485 END IF;
3486 --Made change by bkatraga for bug 13447258 to call the new Reamort API
3487 OKL_VAR_INT_REAMORT_PVT.var_int_rent_level(p_api_version => p_api_version
3488 ,p_init_msg_list => p_init_msg_list
3489 ,x_return_status => l_return_status
3490 ,x_msg_count => x_msg_count
3491 ,x_msg_data => x_msg_data
3492 ,p_chr_id => l_chr_id
3493 ,p_trx_id => p_transaction_number
3494 ,p_trx_status => l_transaction_status
3495 ,p_rent_tbl => l_tmp_srlv_tbl
3496 ,p_csm_loan_level_tbl => l_csm_loan_level_tbl
3497 ,x_child_trx_id => l_child_trx_id);
3498 IF(IS_DEBUG_PROCEDURE_ON) THEN
3499 BEGIN
3500 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_VARIABLE_INTEREST_PUB.var_int_rent_level ');
3501 END;
3502 END IF;
3503 -- End of wraper code generated automatically by Debug code generator for OKL_VARIABLE_INTEREST_PUB.var_int_rent_level
3504 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3505 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3506 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3507 RAISE G_EXCEPTION_ERROR;
3508 END IF;
3509 x_return_status := l_return_status;
3510 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3511 x_msg_data => x_msg_data);
3512 EXCEPTION
3513 WHEN G_EXCEPTION_ERROR THEN
3514 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3515 p_pkg_name => G_PKG_NAME,
3516 p_exc_name => G_EXC_NAME_ERROR,
3517 x_msg_count => x_msg_count,
3518 x_msg_data => x_msg_data,
3519 p_api_type => '_PVT');
3520 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
3521 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3522 p_pkg_name => G_PKG_NAME,
3523 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
3524 x_msg_count => x_msg_count,
3525 x_msg_data => x_msg_data,
3526 p_api_type => '_PVT');
3527 WHEN OTHERS THEN
3528 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3529 p_pkg_name => G_PKG_NAME,
3530 p_exc_name => G_EXC_NAME_OTHERS,
3531 x_msg_count => x_msg_count,
3532 x_msg_data => x_msg_data,
3533 p_api_type => '_PVT');
3534 END PROCESS_VIRP_STRM_RESLTS;
3535 PROCEDURE ENQUEUE_MESSAGE( p_transaction_type IN varchar2,
3536 p_transaction_subtype IN varchar2,
3537 p_doc_number IN varchar2,
3538 p_prc_eng_url IN VARCHAR2,
3539 x_return_status OUT NOCOPY varchar2)
3540 IS
3541 i_tmp clob;
3542 v_message system.ecxmsg;
3543 v_enqueueoptions dbms_aq.enqueue_options_t;
3544 v_messageproperties dbms_aq.message_properties_t;
3545 v_msgid raw(16);
3546 c_nummessages CONSTANT INTEGER :=1;
3547 i_amount number;
3548 i_message raw(32767);
3549 i_buffer varchar2(32767);
3550 i_chunksize pls_integer := 32767;
3551 i_offset pls_integer;
3552 l_party_id varchar2(10) := null;
3553 l_party_type varchar2(10) := '';
3554 l_party_site_id varchar2(100);
3555 l_prc_eng_url varchar2(100);
3556 cursor xml_data_csr( p_trx_number NUMBER) IS
3557 SELECT
3558 IN_XML
3559 FROM
3560 OKL_STREAM_TRX_DATA
3561 WHERE
3562 transaction_number = p_trx_number;
3563 BEGIN
3564 savepoint enq_message;
3565 x_return_status := G_RET_STS_ERROR;
3566 FOR xml_data in xml_data_csr(p_doc_number)
3567 LOOP
3568 i_tmp := xml_data.in_xml;
3569 END LOOP;
3570 IF (p_prc_eng_url is null) THEN
3571 l_prc_eng_url := G_PROTOCOL_ADDRESS;
3572 ELSE
3573 l_prc_eng_url := p_prc_eng_url;
3574 END IF;
3575 -- select xmlfile into i_tmp from clobtest where ID = p_doc_number;
3576 l_party_site_id := FND_PROFILE.VALUE('OKL_ST_PRCENG_NAME');
3577 v_message := system.ecxmsg (message_type => G_MSG_TYPE,
3578 message_standard => G_MSG_STD,
3579
3580 transaction_type => p_transaction_type,
3581 transaction_subtype => p_transaction_subtype,
3582 document_number => p_doc_number,
3583 partyid => l_party_id,
3584 party_site_id => l_party_site_id,
3585 party_type => l_party_type,
3586 protocol_type => G_PROTOCOL_TYPE,
3587 protocol_address => l_prc_eng_url,
3588 username => NULL,
3589 password => NULL,
3590 payload => i_tmp,
3591 attribute1 => NULL,
3592 attribute2 => NULL,
3593 attribute3 => NULL,
3594 attribute4 => NULL,
3595 attribute5 => NULL);
3596 for v_counter in 1..c_nummessages
3597 loop
3598 -- Enqueue
3599 dbms_aq.enqueue
3600 (
3601 queue_name=> G_INBOUND_QUEUE,
3602 enqueue_options=>v_enqueueoptions,
3603 message_properties=>v_messageproperties,
3604 payload=>v_message,
3605 msgid=>v_msgid
3606 );
3607 end loop;
3608 -- start listener for inbound queue
3609 wf_event.listen(G_INBOUND_QUEUE);
3610 -- start listener on transaction queue
3611 wf_event.listen(G_TRANSACTION_QUEUE);
3612 --delete from OKL_STREAM_TRX_DATA where transaction_number = p_doc_number;
3613 x_return_status := G_RET_STS_SUCCESS;
3614 commit;
3615 exception
3616 when others then
3617 rollback to enq_message;
3618 x_return_status := G_RET_STS_ERROR;
3619 END ENQUEUE_MESSAGE;
3620 END OKL_PROCESS_STREAMS_PVT;