[Home] [Help]
PACKAGE BODY: APPS.OKL_PROCESS_STREAMS_PVT
Source
1 PACKAGE BODY OKL_PROCESS_STREAMS_PVT AS
2 /* $Header: OKLRPSRB.pls 120.46 2007/09/06 08:29:51 pagarg noship $ */
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 AND
791 RETURN_STREAMS.index_number = LINES.index_number
792 AND
793 SYSDATE BETWEEN STREAM_TYPE.START_DATE AND NVL(STREAM_TYPE.END_DATE, SYSDATE)
794 and exists(
795 select 1
796 from
797 OKL_SIF_STREAM_TYPES REQUESTED_STREAMS
798 where
799 REQUESTED_STREAMS.sil_id = lines.id
800 and REQUESTED_STREAMS.sfe_id is NULL
801 and stream_type.id = REQUESTED_STREAMS.sty_id)
802 -- Begin mansrini for Bug 5111058 (Fwd port Bug 5061024)
803 -- This query will pick the primary streams of purpose Subsidy
804 UNION ALL
805 SELECT distinct
806 STREAM_TYPE.CODE,
807 RETURN_STREAMS.SEQUENCE_NUMBER,
808 RETURN_STREAMS.SRE_DATE,
809 RETURN_STREAMS.AMOUNT,
810 RETURN_STREAMS.INDEX_NUMBER ASSET_INDEX_NUMBER,
811 STREAM_TYPE.ID stream_type_id,
812 HEADER.KHR_ID,
813
814 LINES.KLE_ID KLE_ID
815 FROM
816 OKL_STRM_TYPE_B STREAM_TYPE,
817 OKL_SIF_RET_STRMS RETURN_STREAMS,
818 OKL_SIF_RETS RETURN_HEADER,
819 OKL_STREAM_INTERFACES HEADER,
820 OKL_SIF_LINES LINES,
821 OKL_SIF_FEES FEES
822 WHERE
823 RETURN_HEADER.transaction_number = p_trx_number
824 AND
825 RETURN_HEADER.id = RETURN_STREAMS.sir_id
826 AND
827 RETURN_STREAMS.index_number = FEES.fee_index_number
828 AND
829 FEES.SIL_ID = LINES.ID
830 AND
831 HEADER.transaction_number = RETURN_HEADER.transaction_number
832 AND
833 LINES.SIF_ID = HEADER.ID
834 AND
835 STREAM_TYPE.CODE = RETURN_STREAMS.STREAM_TYPE_NAME
836 AND
837 STREAM_TYPE.STREAM_TYPE_PURPOSE = 'SUBSIDY'
838 AND
839 SYSDATE BETWEEN STREAM_TYPE.START_DATE AND NVL(STREAM_TYPE.END_DATE, SYSDATE)
840 AND EXISTS(
841 SELECT 1
842 FROM
843 OKL_SIF_STREAM_TYPES REQUESTED_STREAMS
844 WHERE
845 REQUESTED_STREAMS.sfe_id = fees.id
846 AND stream_type.id = REQUESTED_STREAMS.sty_id
847 AND HEADER.ID = REQUESTED_STREAMS.sif_id
848 AND (REQUESTED_STREAMS.sil_id IS NULL OR LINES.ID = REQUESTED_STREAMS.sil_id)
849 )
850 -- end mansrini for Bug 5111058 (Fwd port Bug 5061024)
851
852 ORDER BY stream_type_id, ASSET_INDEX_NUMBER, kle_id;
853 -- added order by to resolve Asset Mapping
854 -- modified order by for Bug # 2403426
855 CURSOR yield_data_csr(p_trx_number NUMBER) IS
856 SELECT
857 YIELD_NAME,
858 EFFECTIVE_PRE_TAX_YIELD,
859 EFFECTIVE_AFTER_TAX_YIELD,
860 NOMINAL_PRE_TAX_YIELD,
861 NOMINAL_AFTER_TAX_YIELD,
862 IMPLICIT_INTEREST_RATE
863 FROM
864 OKL_SIF_RETS
865 WHERE
866 transaction_number = p_trx_number;
867 -- define cursor to check any Exceptions in the Inbound Interface Tables
868 CURSOR exception_data_csr(p_trx_number NUMBER)
869 IS
870 SELECT
871 SRMB.ID,
872 SRMB.ERROR_CODE,
873 SRMB.ERROR_MESSAGE,
874 SRMB.TAG_NAME,
875 SRMB.TAG_ATTRIBUTE_NAME,
876 SRMB.TAG_ATTRIBUTE_VALUE,
877 SRMB.DESCRIPTION
878 FROM
879 OKL_SIF_RETS SIRB,
880 OKL_SIF_RET_ERRORS SRMB
881 WHERE
882 SIRB.TRANSACTION_NUMBER = p_trx_number
883 AND
884 SIRB.ID = SRMB.SIR_ID;
885 -- cursor to update transaction status in the OKL_STREAM_INTERFACES table
886 CURSOR sif_data_csr (p_transaction_number IN NUMBER) IS
887 SELECT
888
889 ID,
890 ORP_CODE,
891 LOG_FILE,
892 SECURITY_DEPOSIT_AMOUNT
893 FROM Okl_Stream_Interfaces
894 WHERE okl_stream_interfaces.transaction_number = p_transaction_number;
895 CURSOR sirv_data_csr (p_trnsaction_numner IN NUMBER) IS
896 SELECT
897 ID,
898 TRANSACTION_NUMBER,
899 SRT_CODE,
900 EFFECTIVE_PRE_TAX_YIELD,
901 YIELD_NAME,
902 INDEX_NUMBER,
903 EFFECTIVE_AFTER_TAX_YIELD,
904 NOMINAL_PRE_TAX_YIELD,
905 NOMINAL_AFTER_TAX_YIELD,
906 STREAM_INTERFACE_ATTRIBUTE01,
907 STREAM_INTERFACE_ATTRIBUTE02,
908 STREAM_INTERFACE_ATTRIBUTE03,
909 STREAM_INTERFACE_ATTRIBUTE04,
910 STREAM_INTERFACE_ATTRIBUTE05,
911 STREAM_INTERFACE_ATTRIBUTE06,
912 STREAM_INTERFACE_ATTRIBUTE07,
913 STREAM_INTERFACE_ATTRIBUTE08,
914 STREAM_INTERFACE_ATTRIBUTE09,
915 STREAM_INTERFACE_ATTRIBUTE10,
916 STREAM_INTERFACE_ATTRIBUTE11,
917 STREAM_INTERFACE_ATTRIBUTE12,
918 STREAM_INTERFACE_ATTRIBUTE13,
919 STREAM_INTERFACE_ATTRIBUTE14,
920 STREAM_INTERFACE_ATTRIBUTE15,
921 OBJECT_VERSION_NUMBER,
922 CREATED_BY,
923 LAST_UPDATED_BY,
924 CREATION_DATE,
925 LAST_UPDATE_DATE,
926 LAST_UPDATE_LOGIN,
927 IMPLICIT_INTEREST_RATE,
928 DATE_PROCESSED,
929 REQUEST_ID,
930 PROGRAM_APPLICATION_ID,
931 PROGRAM_ID,
932 PROGRAM_UPDATE_DATE
933 FROM Okl_Sif_Rets
934 WHERE okl_sif_rets.transaction_number = p_trnsaction_numner;
935 -- smahapat 11/10/02 multi-gaap -- addition
936 CURSOR reporting_streams_csr(p_trx_number NUMBER) IS
937 SELECT purpose_code
938 FROM okl_stream_interfaces
939 WHERE transaction_number = p_trx_number;
940
941 CURSOR reporting_product_csr(p_trx_number NUMBER) IS
942 SELECT c.reporting_pdt_id
943 FROM okl_k_headers a, okl_stream_interfaces b, okl_products_v c
944 WHERE b.transaction_number = p_trx_number
945 AND a.id = b.khr_id
946 AND c.id = a.pdt_id;
947 reporting_product_rec reporting_product_csr%ROWTYPE;
948
949 lx_pdt_parameter_rec pdt_param_rec_type;
950 l_pdtv_rec pdtv_rec_type;
951 lx_no_data_found BOOLEAN;
952
953 CURSOR chk_for_subsidy_csr(p_trx_number NUMBER) IS
954 SELECT
955 '1'
956 FROM
957 OKL_SIF_FEES SFEB,
958 OKL_STREAM_INTERFACES SIFB
959 WHERE
960 SIFB.ID = SFEB.SIF_ID AND
961 transaction_number = p_trx_number AND
962 SFE_TYPE = 'SFB' ORDER BY FEE_INDEX_NUMBER;
963
964 l_reporting_streams VARCHAR2(1) := OKL_API.G_FALSE;
965 l_process_yn VARCHAR2(1) := OKL_API.G_TRUE;
966 l_chk_subsidy VARCHAR2(1) := 'x';
967 -- smahapat addition end
968 yield_csr yield_data_csr%ROWTYPE;
969 stream_csr stream_data_csr%ROWTYPE;
970 --first_stream_rec stream_data_csr%ROWTYPE;
971 exception_data exception_data_csr%ROWTYPE;
972
973 l_msg_index_out NUMBER;
974 -- Bug 4196515: Start
975 CURSOR get_org_id(p_chr_id okc_k_headers_b.id%TYPE)
976 IS
977 SELECT authoring_org_id,
978 currency_code
979 FROM okc_k_headers_b
980 WHERE id = p_chr_id;
981
982 CURSOR get_precision(p_currency_code OKC_K_HEADERS_B.CURRENCY_CODE%TYPE)
983 IS
984 SELECT PRECISION
985 FROM fnd_currencies_vl
986 WHERE currency_code = p_currency_code
987 AND enabled_flag = 'Y'
988 AND NVL(start_date_active, SYSDATE) <= SYSDATE
989 AND NVL(end_date_active, SYSDATE) >= SYSDATE;
990
991 CURSOR get_rounding_rule
992 IS
993 SELECT stm_rounding_rule
994 FROM OKL_SYS_ACCT_OPTS;
995
996 CURSOR get_rnd_diff_lookup(p_lookup_type fnd_lookups.lookup_type%TYPE)
997 IS
998 SELECT b.stm_apply_rounding_difference
999 FROM fnd_lookups a,
1000 OKL_SYS_ACCT_OPTS b
1001 WHERE a.lookup_type = p_lookup_type
1002 AND a.lookup_code = b.stm_apply_rounding_difference;
1003
1004 --Added by KTHIRUVA for ESG Performance Imporvement on 03-May-2005
1005 --Bug 4346646 - Start of Changes
1006 CURSOR SERVICE_LINES_EXIST(P_KHR_ID OKC_K_HEADERS_B.ID%TYPE)
1007 IS
1008 SELECT 1
1009 FROM OKC_K_HEADERS_B KHR,
1010 OKC_K_LINES_B CLE,
1011 OKC_LINE_STYLES_B LSE
1012 WHERE CLE.CHR_ID = KHR.ID
1013 AND KHR.ID = p_khr_id
1014 AND CLE.LSE_ID = LSE.ID
1015 AND LSE.LTY_CODE IN ('SOLD_SERVICE','LINK_SERV_ASSET');
1016
1017 l_service_line_found NUMBER;
1018 --Bug 4346646 - End of Changes
1019
1020 l_org_id okc_k_headers_b.authoring_org_id%TYPE;
1021 l_currency_code okc_k_headers_b.currency_code%TYPE;
1022 l_diff_lookup_code fnd_lookups.lookup_code%TYPE;
1023 l_precision NUMBER;
1024 l_rounding_rule okl_sys_acct_opts.ael_rounding_rule%TYPE;
1025 l_first_rec CHAR := 'T';
1026
1027 G_RND_DIFF_LOOKUP_TYPE CONSTANT fnd_lookups.lookup_type%TYPE := 'OKL_STRM_APPLY_ROUNDING_DIFF';
1028 G_NO_MATCH_REC CONSTANT VARCHAR2(30) := 'OKL_LLA_NO_MATCHING_RECORD';
1029 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
1030 -- Bug 4196515: End
1031
1032 --Added by kthiruva on 15-May-2005 for Streams Performance
1033 --Bug 4346646-Start of Changes
1034 l_stmv_tbl stmv_tbl_type;
1035 x_stmv_tbl stmv_tbl_type;
1036 full_selv_tbl selv_tbl_type;
1037 l_selv_count NUMBER;
1038 k NUMBER;
1039 --Bug 4346646-End of Changes
1040
1041 BEGIN
1042 IF (G_DEBUG_ENABLED = 'Y') THEN
1043 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1044 END IF;
1045 --Added by kthiruva for Debugging
1046 L_DEBUG_ENABLED := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
1047 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1048 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Transaction number is :'||p_transaction_number);
1049 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inside procedure process_stream_results');
1050 END IF;
1051
1052
1053 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1054 p_pkg_name => 'OKL_PROCESS_STREAMS_PVT',
1055 p_init_msg_list => p_init_msg_list,
1056 l_api_version => l_api_version,
1057 p_api_version => p_api_version,
1058 p_api_type => '_PVT',
1059 x_return_status => l_return_status);
1060 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1061 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1062 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1063 RAISE G_EXCEPTION_ERROR;
1064 END IF;
1065
1066 x_return_status := G_RET_STS_SUCCESS;
1067 --Before Processing Stream Results check for any exceptions in the Inbound Interface Tables
1068 FOR exception_data in exception_data_csr(p_transaction_number)
1069 LOOP
1070 IF(l_message_count = 0)
1071 THEN
1072 l_error_message_line := 'REQUEST ID = ' || p_transaction_number || ' TIME PROCESSED = '|| to_char(SYSDATE,'YYYYMMDD HH24MISS');
1073 l_error_message_line := l_error_message_line || G_NEW_LINE;
1074 l_error_message_line := l_error_message_line || 'Errors returned from Pricing Engine :- ';
1075 l_error_message_line := l_error_message_line || G_NEW_LINE;
1076 l_error_message_line := l_error_message_line || 'ERROR CODE :: ' || exception_data.ERROR_CODE;
1077 l_error_message_line := l_error_message_line || G_NEW_LINE;
1078 l_error_message_line := l_error_message_line || 'ERROR MESSAGE :: ' || exception_data.ERROR_MESSAGE;
1079 l_error_message_line := l_error_message_line || G_NEW_LINE;
1080 l_error_message_line := l_error_message_line || 'XML TAG :: ' || exception_data.TAG_NAME ;
1081 l_error_message_line := l_error_message_line || G_NEW_LINE;
1082 l_message_count := l_message_count + 1;
1083 ELSE
1084 l_error_message_line := NULL;
1085 l_error_message_line := l_error_message_line || 'ERROR CODE :: ' || exception_data.ERROR_CODE;
1086 l_error_message_line := l_error_message_line || G_NEW_LINE;
1087 l_error_message_line := l_error_message_line || 'ERROR MESSAGE :: ' || exception_data.ERROR_MESSAGE;
1088 l_error_message_line := l_error_message_line || G_NEW_LINE;
1089 l_error_message_line := l_error_message_line || 'XML TAG :: ' || exception_data.TAG_NAME ;
1090 l_error_message_line := l_error_message_line || G_NEW_LINE;
1091 -- Not used in this release
1092 /*
1093
1094 l_error_message_line := l_error_message_line || 'XML TAG ATTRIBUTE :: ' || exception_data.TAG_ATTRIBUTE_NAME ;
1095 l_error_message_line := l_error_message_line || G_NEW_LINE;
1096 l_error_message_line := l_error_message_line || 'XML TAG ATTRIBUTE VALUE :: ' || exception_data.TAG_ATTRIBUTE_VALUE ;
1097 l_error_message_line := l_error_message_line || G_NEW_LINE;
1098 l_error_message_line := l_error_message_line || 'DETAILS :: ' || exception_data.DESCRIPTION ;
1099 l_error_message_line := l_error_message_line || G_NEW_LINE;
1100 */
1101 l_message_count := l_message_count + 1;
1102 END IF;
1103 l_error_message_tbl(l_message_count) := l_error_message_line;
1104 END LOOP;
1105
1106 IF( l_message_count > 0)
1107 THEN
1108 l_exception_data_found := TRUE;
1109 FND_MESSAGE.SET_NAME ( G_APP_NAME, 'OKL_STREAM_GENERATION_ERROR');
1110 FND_MESSAGE.SET_TOKEN(TOKEN => 'FILE_NAME',
1111 VALUE => 'OKLSTXMLG_' || p_transaction_number || '.log',
1112 TRANSLATE => TRUE);
1113 l_msg_text := FND_MESSAGE.GET;
1114 l_comments := l_msg_text;
1115 l_error_message_tbl(l_message_count + 1) := 'End Errors returned from Pricing Engine' ;
1116
1117 OKL_STREAMS_UTIL.LOG_MESSAGE(p_msgs_tbl => l_error_message_tbl,
1118 p_translate => G_FALSE,
1119 p_file_name => 'OKLSTXMLG_' || p_transaction_number || '.log' ,
1120 x_return_status => l_return_status );
1121 ELSE
1122 l_comments := NULL;
1123 END IF;
1124
1125 FOR reporting_streams_data IN reporting_streams_csr(p_transaction_number)
1126 LOOP
1127 IF (reporting_streams_data.purpose_code = G_PURPOSE_CODE_REPORT)
1128 THEN
1129 l_reporting_streams := OKL_API.G_TRUE;
1130 END IF;
1131 END LOOP;
1132
1133 -- Bug 4196515: Start
1134 -- Making sure that no records are present
1135 i := 0;
1136 l_selv_tbl := dummy_selv_tbl;
1137 l_stmv_rec := NULL;
1138 l_khr_id := NULL;
1139 FOR stream_csr IN stream_data_csr(p_transaction_number)
1140 LOOP
1141 -- Checking whether if this rec is the first rec.
1142 IF( l_first_rec = 'T' )
1143 THEN
1144 l_stmv_rec.sty_id := stream_csr.stream_type_id;
1145 l_stmv_rec.khr_id := stream_csr.khr_id;
1146 l_stmv_rec.kle_id := stream_csr.kle_id;
1147 l_stmv_rec.sgn_code := G_STREAM_GENERATOR;
1148 l_stmv_rec.say_code := G_STREAM_ACTIVITY_WORK;
1149 l_stmv_rec.active_yn := G_STREAM_ACTIVE_YN;
1150 l_stmv_rec.date_working := SYSDATE ;
1151 l_stmv_rec.comments := l_comments ;
1152 IF (l_reporting_streams = OKL_API.G_TRUE)
1153 THEN
1154 l_stmv_rec.purpose_code := G_PURPOSE_CODE_REPORT;
1155 END IF;
1156 l_khr_id := TO_CHAR(stream_csr.khr_id);
1157 l_stmv_rec.transaction_number := p_transaction_number;
1158
1159 --Added by kthiruva on 15-May-2005 for Streams Performance
1160 --Bug 4346646 - Start of Changes
1161 i := i + 1;
1162 l_stmv_tbl(i) := l_stmv_rec ;
1163 --Bug 4346646 - End of Changes
1164
1165 -- Got the First Record, change the Flag
1166 l_first_rec := 'F';
1167 END IF;
1168
1169 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))
1170 THEN
1171 -- populate the child records
1172 j := j + 1;
1173 l_selv_tbl(j).stream_element_date := stream_csr.sre_date;
1174 l_selv_tbl(j).amount := stream_csr.amount;
1175 l_selv_tbl(j).se_line_number := p_line_number;
1176 --Added by kthiruva on 15-May-2005 for Streams Performance
1177 --Bug 4346646 - Start of Changes
1178 l_selv_tbl(j).parent_index := i;
1179 --Bug 4346646 - End of Changes
1180 p_line_number := p_line_number + 1;
1181 ELSE
1182 -- call the insert API for STREAMS
1183 IF(IS_DEBUG_PROCEDURE_ON) THEN
1184 BEGIN
1185 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call Okl_Streams_Pub.create_streams ');
1186 END;
1187 END IF;
1188
1189 --Modified by kthiruva for Streams Performance on 15-May-2005
1190 --Bug 4346646 - Start of Changes
1191
1192 --Obtaining the values of the system options that need to be passed to the rounding call
1193 okl_streams_util.get_acc_options( p_khr_id => l_khr_id,
1194 x_org_id => l_org_id,
1195 x_precision => l_precision,
1196 x_currency_code => l_currency_code,
1197 x_rounding_rule => l_rounding_rule,
1198 x_apply_rnd_diff => l_diff_lookup_code,
1199 x_return_status => x_return_status );
1200
1201 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1202 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1203 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1204 RAISE G_EXCEPTION_ERROR;
1205 END IF;
1206
1207 x_return_status := Okl_Streams_Util.round_streams_amount_esg(p_api_version => p_api_version,
1208 p_init_msg_list => p_init_msg_list,
1209 x_msg_count => x_msg_count,
1210 x_msg_data => x_msg_data,
1211 p_chr_id => l_khr_id,
1212 p_selv_tbl => l_selv_tbl,
1213 x_selv_tbl => lx_selv_tbl,
1214 p_org_id => l_org_id,
1215 p_precision => l_precision,
1216 p_currency_code => l_currency_code,
1217 p_rounding_rule => l_rounding_rule,
1218 p_apply_rnd_diff => l_diff_lookup_code);
1219 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1220 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1221 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1222 RAISE G_EXCEPTION_ERROR;
1223 END IF;
1224
1225 -- This call basically accepts the stream element table returned by the rounding procedure
1226 -- and accumulates all the stream elements in full_selv_tbl that is passed to the create streams call
1227 okl_streams_util.accumulate_strm_elements(p_stm_index_no => null,
1228 p_selv_tbl => lx_selv_tbl,
1229 x_full_selv_tbl => full_selv_tbl,
1230 x_return_status => x_return_status
1231 );
1232
1233 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1234 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1235 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1236 RAISE G_EXCEPTION_ERROR;
1237 END IF;
1238
1239 --Bug 4346646 - End of Changes
1240
1241 IF(IS_DEBUG_PROCEDURE_ON) THEN
1242 BEGIN
1243 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call Okl_Streams_Pub.create_streams ');
1244 END;
1245 END IF;
1246
1247 j := 1 ;
1248 l_selv_tbl := dummy_selv_tbl;
1249 -- populate the header record
1250 l_stmv_rec.sty_id := stream_csr.stream_type_id;
1251 l_stmv_rec.khr_id := stream_csr.khr_id;
1252 l_stmv_rec.kle_id := stream_csr.kle_id;
1253 l_stmv_rec.sgn_code := G_STREAM_GENERATOR;
1254 l_stmv_rec.say_code := G_STREAM_ACTIVITY_WORK;
1255 l_stmv_rec.active_yn := G_STREAM_ACTIVE_YN;
1256 l_stmv_rec.date_working := SYSDATE ;
1257 l_stmv_rec.transaction_number := p_transaction_number;
1258 l_stmv_rec.comments := l_comments;
1259 --Added by kthiruva on 15-May-2005 for Streams Performance
1260 --Bug 4346646 - Start of Changes
1261 i := i + 1;
1262 l_stmv_tbl(i) := l_stmv_rec ;
1263 --Bug 4346646 - End of Changes
1264
1265 -- smahapat 11/10/02 multi-gaap -- addition
1266 IF (l_reporting_streams = OKL_API.G_TRUE)
1267 THEN
1268 l_stmv_rec.purpose_code := G_PURPOSE_CODE_REPORT;
1269 END IF;
1270 -- smahapat addition end
1271 -- populate the first line of this header record
1272 l_selv_tbl(j).stream_element_date := stream_csr.sre_date;
1273 l_selv_tbl(j).amount := stream_csr.amount;
1274 --Added by kthiruva on 15-May-2005 for Streams Performance
1275 --Bug 4346646 - Start of Changes
1276 l_selv_tbl(j).parent_index := i;
1277 --Bug 4346646 - End of Changes
1278 l_selv_tbl(j).se_line_number := p_first_line_number;
1279 p_line_number := p_first_line_number + 1;
1280 END IF;
1281 END LOOP;
1282 -- insert the last record from the CURSOR
1283 -- Start of wraper code generated automatically by Debug code generator for Okl_Streams_Pub.create_streams
1284 IF( l_khr_id IS NOT NULL )
1285 THEN
1286 IF(IS_DEBUG_PROCEDURE_ON) THEN
1287 BEGIN
1288 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call Okl_Streams_Pub.create_streams ');
1289 END;
1290 END IF;
1291
1292 --Modified by kthiruva on 15-May-2005 for Streams Performance
1293 --Bug 4346646 - Start of Changes
1294
1295 --Added by kthiruva for Debug Logging
1296 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1297 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to Okl_Streams_Util.round_streams_amount_esg');
1298 END IF;
1299 --Making the round amount call for the stream elements of the last stream header
1300 x_return_status := Okl_Streams_Util.round_streams_amount_esg(p_api_version => p_api_version,
1301 p_init_msg_list => p_init_msg_list,
1302 x_msg_count => x_msg_count,
1303 x_msg_data => x_msg_data,
1304 p_chr_id => l_khr_id,
1305 p_selv_tbl => l_selv_tbl,
1306 x_selv_tbl => lx_selv_tbl,
1307 p_org_id => l_org_id,
1308 p_precision => l_precision,
1309 p_currency_code => l_currency_code,
1310 p_rounding_rule => l_rounding_rule,
1311 p_apply_rnd_diff => l_diff_lookup_code);
1312
1313 --Added by kthiruva for Debug Logging
1314 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1315 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);
1316 END IF;
1317
1318 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1319 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1320 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1321 RAISE G_EXCEPTION_ERROR;
1322 END IF;
1323
1324 --Accumulating the stream elements of the last stream header to the existing full_selv_tbl
1325 okl_streams_util.accumulate_strm_elements(p_stm_index_no => null,
1326 p_selv_tbl => lx_selv_tbl,
1327 x_full_selv_tbl => full_selv_tbl,
1328 x_return_status => x_return_status
1329 );
1330 --Added by kthiruva for Debug Logging
1331 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1332 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);
1333 END IF;
1334
1335 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1336 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1337 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1338 RAISE G_EXCEPTION_ERROR;
1339 END IF;
1340
1341 --Calling the new method that accepts a table of stream headers and a table of stream elements
1342 -- Bulk insert calls are made for both the stream headers and stream elements.
1343 Okl_Streams_Pub.create_streams_perf(p_api_version
1344 ,p_init_msg_list
1345 ,x_return_status
1346 ,x_msg_count
1347 ,x_msg_data
1348 ,l_stmv_tbl -- arajagop changed
1349 ,full_selv_tbl --satya changed 10/17/03
1350 ,x_stmv_tbl
1351 ,x_selv_tbl);
1352 --Bug 4346646 - End of Changes
1353 --Added by kthiruva for Debug Logging
1354 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1355 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);
1356 END IF;
1357
1358
1359 IF(IS_DEBUG_PROCEDURE_ON) THEN
1360 BEGIN
1361 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call Okl_Streams_Pub.create_streams ');
1362 END;
1363 END IF;
1364 -- End of wraper code generated automatically by Debug code generator for Okl_Streams_Pub.create_streams
1365 IF (x_return_status = G_RET_STS_UNEXP_ERROR)
1366 THEN
1367 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1368 ELSIF (x_return_status = G_RET_STS_ERROR)
1369 THEN
1370 RAISE G_EXCEPTION_ERROR;
1371 END IF;
1372
1373 OPEN chk_for_subsidy_csr(p_transaction_number);
1374 FETCH chk_for_subsidy_csr INTO l_chk_subsidy;
1375 CLOSE chk_for_subsidy_csr;
1376 --Added by kthiruva for Debug Logging
1377 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1378 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Value of l_chk_subsidy is :'||l_chk_subsidy);
1379 END IF;
1380
1381 -- TODO : check what yield has to be assigned
1382 -- Update Yields at contract header
1383 FOR yield_csr IN yield_data_csr(p_transaction_number)
1384 LOOP
1385 l_yield_data_flag := 1;
1386 -- assign the value of implicit_interest_rate :Only exactly 1 record will have value of implicit_interest_rate
1387 IF (l_chk_subsidy = '1')
1388 THEN
1389 -- YIELD NAMES ARE HARD CODED HERE , NEED TO VERIFY WITH THE RETURNING YIELDS NAMES FROM ST
1390 IF(yield_csr.yield_name = 'PTIRRWS' )
1391 THEN
1392 l_khr_yields_rec.PRE_TAX_IRR := yield_csr.effective_pre_tax_yield;
1393 IF(yield_csr.implicit_interest_rate IS NOT NULL)
1394 THEN
1395 l_khr_yields_rec.implicit_interest_rate := yield_csr.implicit_interest_rate;
1396 END IF;
1397 ELSIF(yield_csr.yield_name = 'NATWS')
1398 THEN
1399 l_khr_yields_rec.AFTER_TAX_IRR := yield_csr.effective_pre_tax_yield;
1400 ELSIF(yield_csr.yield_name = 'BookingWS')
1401 THEN
1402 l_khr_yields_rec.AFTER_TAX_YIELD := yield_csr.effective_pre_tax_yield;
1403 l_khr_yields_rec.PRE_TAX_YIELD := NULL;
1404 ELSIF(yield_csr.yield_name = 'PTIRR' )
1405 THEN
1406 l_khr_yields_rec.SUB_PRE_TAX_IRR := yield_csr.effective_pre_tax_yield;
1407 IF(yield_csr.implicit_interest_rate IS NOT NULL)
1408 THEN
1409 l_khr_yields_rec.sub_impl_interest_rate := yield_csr.IMPLICIT_INTEREST_RATE;
1410 END IF;
1411 ELSIF(yield_csr.yield_name = 'NAT')
1412 THEN
1413 l_khr_yields_rec.SUB_AFTER_TAX_IRR := yield_csr.effective_pre_tax_yield;
1414 ELSIF(yield_csr.yield_name = 'Booking')
1415 THEN
1416 l_khr_yields_rec.SUB_AFTER_TAX_YIELD := yield_csr.effective_pre_tax_yield;
1417 l_khr_yields_rec.PRE_TAX_YIELD := NULL;
1418 END IF;
1419 ELSE
1420 IF(yield_csr.implicit_interest_rate IS NOT NULL)
1421 THEN
1422 l_khr_yields_rec.implicit_interest_rate := yield_csr.implicit_interest_rate;
1423 END IF;
1424 -- YIELD NAMES ARE HARD CODED HERE , NEED TO VERIFY WITH THE RETURNING YIELDS NAMES FROM ST
1425 IF(yield_csr.yield_name = 'PTIRR' )
1426 THEN
1427 l_khr_yields_rec.PRE_TAX_IRR := yield_csr.effective_pre_tax_yield;
1428 l_khr_yields_rec.implicit_interest_rate := yield_csr.implicit_interest_rate;
1429 ELSIF(yield_csr.yield_name = 'NAT')
1430 THEN
1431 l_khr_yields_rec.AFTER_TAX_IRR := yield_csr.effective_pre_tax_yield;
1432 ELSIF(yield_csr.yield_name = 'Booking')
1433 THEN
1434 l_khr_yields_rec.AFTER_TAX_YIELD := yield_csr.effective_pre_tax_yield;
1435 l_khr_yields_rec.PRE_TAX_YIELD := NULL;
1436 END IF;
1437 END IF;
1438 END LOOP;
1439 -- generate streams for the SERVICE LINES
1440 -- Start of wraper code generated automatically by Debug code generator for OKL_PROCESS_STREAMS_PVT.GEN_SERV_MAIN_LINE_STRMS
1441 IF(IS_DEBUG_PROCEDURE_ON) THEN
1442 BEGIN
1443 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_PROCESS_STREAMS_PVT.GEN_SERV_MAIN_LINE_STRMS ');
1444 END;
1445 END IF;
1446
1447 --start smahapat bugfix# 2790695
1448 --Generate accrual streams for service lines
1449 lx_pdt_parameter_rec := NULL;
1450 IF (l_reporting_streams = OKL_API.G_TRUE) THEN
1451 OPEN reporting_product_csr(p_transaction_number);
1452 FETCH reporting_product_csr INTO reporting_product_rec;
1453 CLOSE reporting_product_csr;
1454 --Added by kthiruva for Debug Logging
1455 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1456 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Reporting Product present.Calling OKL_SETUPPRODUCTS_PVT.Getpdt_parameters');
1457 END IF;
1458
1459 l_pdtv_rec.id := reporting_product_rec.reporting_pdt_id;
1460 OKL_SETUPPRODUCTS_PVT.Getpdt_parameters(
1461 p_api_version => p_api_version,
1462 p_init_msg_list => p_init_msg_list,
1463 x_return_status => x_return_status,
1464 x_msg_count => x_msg_count,
1465 x_msg_data => x_msg_data,
1466 p_pdtv_rec => l_pdtv_rec,
1467 x_no_data_found => lx_no_data_found,
1468 p_pdt_parameter_rec => lx_pdt_parameter_rec);
1469
1470 --Added by kthiruva for Debug Logging
1471 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1472 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);
1473 END IF;
1474 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1475 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1476 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1477 RAISE OKL_API.G_EXCEPTION_ERROR;
1478 END IF;
1479 END IF;
1480
1481 --Added by kthiruva for ESG Performance Improvement on 03-May-2005
1482 --Bug 4346646 - Start of Changes
1483 --This cursordetermines whether there are any Service Lines defined on the contract.
1484 OPEN SERVICE_LINES_EXIST(l_khr_id);
1485 FETCH SERVICE_LINES_EXIST INTO l_service_line_found;
1486 --Make the call to the ISG API to generate Service Line Streams only if service lines
1487 --are defined on the contract
1488 IF SERVICE_LINES_EXIST%FOUND THEN
1489 --Bug 4346646 - End of Changes
1490 --Added by kthiruva for Debug Logging
1491 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1492 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Service Lines Exist');
1493 END IF;
1494 okl_stream_generator_pub.GENERATE_STREAMS(
1495 p_api_version => p_api_version,
1496 p_init_msg_list => p_init_msg_list,
1497 p_khr_id => l_khr_id,
1498 p_compute_irr => OKL_API.G_FALSE,
1499 p_generation_type => 'SERVICE_LINES',
1500 p_reporting_book_class => lx_pdt_parameter_rec.deal_type,
1501 x_pre_tax_irr => x_pre_tax_irr,
1502 x_return_status => x_return_status,
1503 x_msg_count => x_msg_count,
1504 x_msg_data => x_msg_data);
1505 --Added by kthiruva for Debug Logging
1506 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1507 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);
1508 END IF;
1509
1510 IF (x_return_status = G_RET_STS_UNEXP_ERROR)
1511 THEN
1512 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1513 ELSIF (x_return_status = G_RET_STS_ERROR)
1514 THEN
1515 RAISE G_EXCEPTION_ERROR;
1516 END IF;
1517 --end smahapat bugfix# 2790695
1518 --Bug 4346646 - Start of Changes
1519 END IF;
1520 CLOSE SERVICE_LINES_EXIST;
1521 --Bug 4346646 - End of Changes
1522 --Added by kthiruva for Debug Logging
1523 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1524 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to generating Residual Value streams');
1525 END IF;
1526 --Generate accrual streams for residual streams internally
1527 okl_stream_generator_pub.GENERATE_STREAMS(
1528 p_api_version => p_api_version,
1529 p_init_msg_list => p_init_msg_list,
1530 p_khr_id => l_khr_id,
1531 p_compute_irr => OKL_API.G_FALSE,
1532 p_generation_type => 'RESIDUAL VALUE',
1533 p_reporting_book_class => lx_pdt_parameter_rec.deal_type,
1534 x_pre_tax_irr => x_pre_tax_irr,
1535 x_return_status => x_return_status,
1536 x_msg_count => x_msg_count,
1537 x_msg_data => x_msg_data);
1538 --Added by kthiruva for Debug Logging
1539 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1540 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);
1541 END IF;
1542
1543 IF (x_return_status = G_RET_STS_UNEXP_ERROR)
1544 THEN
1545 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1546 ELSIF (x_return_status = G_RET_STS_ERROR)
1547 THEN
1548 RAISE G_EXCEPTION_ERROR;
1549 END IF;
1550 --end Generate accrual streams for residual streams internally
1551
1552 FOR sif_data in sif_data_csr(p_transaction_number)
1553 LOOP
1554 lp_sifv_rec.id := sif_data.id;
1555 lp_sifv_rec.ORP_CODE := sif_data.ORP_CODE;
1556 l_security_deposit_amt := sif_data.SECURITY_DEPOSIT_AMOUNT;
1557 END LOOP;
1558 -- generate SECURITY DEPOSIT Streams
1559 IF(l_security_deposit_amt IS NOT NULL)
1560 THEN
1561 -- generate streams for the SECURITY DEPOSIT FEES
1562 -- Start of wraper code generated automatically by Debug code generator for OKL_PROCESS_STREAMS_PVT.GEN_SEC_DEP_STRMS
1563 IF(IS_DEBUG_PROCEDURE_ON) THEN
1564 BEGIN
1565 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_PROCESS_STREAMS_PVT.GEN_SEC_DEP_STRMS ');
1566 END;
1567 END IF;
1568 --Added by kthiruva for Debug Logging
1569 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1570 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Security deposit amount is not null');
1571 END IF;
1572
1573 OKL_PROCESS_STREAMS_PVT.GEN_SEC_DEP_STRMS(p_api_version => p_api_version
1574 ,p_init_msg_list => p_init_msg_list
1575 ,p_khr_id => l_khr_id
1576 ,p_transaction_number => p_transaction_number
1577 ,p_reporting_streams => l_reporting_streams
1578 ,x_return_status => x_return_status
1579 ,x_msg_count => x_msg_count
1580 ,x_msg_data => x_msg_data);
1581 --Added by kthiruva for Debug Logging
1582 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1583 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);
1584 END IF;
1585
1586 IF(IS_DEBUG_PROCEDURE_ON) THEN
1587 BEGIN
1588 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_PROCESS_STREAMS_PVT.GEN_SEC_DEP_STRMS ');
1589 END;
1590 END IF;
1591 -- End of wraper code generated automatically by Debug code generator for OKL_PROCESS_STREAMS_PVT.GEN_SEC_DEP_STRMS
1592 IF (x_return_status = G_RET_STS_UNEXP_ERROR)
1593 THEN
1594 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1595 ELSIF (x_return_status = G_RET_STS_ERROR)
1596 THEN
1597 RAISE G_EXCEPTION_ERROR;
1598 END IF;
1599 END IF;
1600
1601 --Added by srsreeni for bug 5699923
1602 -- Invoke the procedure OKL_LA_STREAM_PVT.RECREATE_TMT_LN_STRMS
1603 -- Streams are no longer requested through Pricing Engine
1604 -- for TERMINATED LOAN COMPONENTS like FINANCED, ROLLOVER FEE
1605 -- and LOAN ASSET LINES. Instead, the CURRENT streams are copied
1606 -- over as WORKING STREAMS. On creating WORKING streams here, the
1607 -- code after this takes care of HISTORIZING current streams and
1608 -- making the newly created WORK streams to CURRENT.
1609 OKL_LA_STREAM_PVT.RECREATE_TMT_LN_STRMS(
1610 p_api_version => p_api_version,
1611 p_init_msg_list => p_init_msg_list,
1612 x_return_status => x_return_status,
1613 x_msg_count => x_msg_count,
1614 x_msg_data => x_msg_data,
1615 p_chr_id => l_khr_id,
1616 p_trx_number => p_transaction_number);
1617
1618 IF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1619 RAISE OKL_API.G_EXCEPTION_ERROR;
1620 ELSIF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1621 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1622 END IF;
1623 --end srsreeni for bug 5699923
1624
1625 -- Start of wraper code generated automatically by Debug code generator for OKL_LA_STREAM_PVT.process_streams
1626 IF(IS_DEBUG_PROCEDURE_ON) THEN
1627
1628 BEGIN
1629 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_LA_STREAM_PVT.process_streams ');
1630 END;
1631 END IF;
1632 --Added by kthiruva for Debug Logging
1633 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1634 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_LA_STREAM_PVT.process_streams');
1635 END IF;
1636
1637 OKL_LA_STREAM_PVT.process_streams(
1638 p_api_version,
1639 p_init_msg_list,
1640 x_return_status,
1641 x_msg_count,
1642 x_msg_data,
1643 l_khr_id,
1644 l_process_yn,
1645 l_khr_yields_rec);
1646 --Added by kthiruva for Debug Logging
1647 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1648 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);
1649 END IF;
1650
1651 IF(IS_DEBUG_PROCEDURE_ON) THEN
1652 BEGIN
1653 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_LA_STREAM_PVT.process_streams ');
1654 END;
1655 END IF;
1656 -- End of wraper code generated automatically by Debug code generator for OKL_LA_STREAM_PVT.process_streams
1657 /*
1658 Okl_la_Stream_Pub.update_contract_yields(p_api_version,
1659 p_init_msg_list ,
1660 x_return_status ,
1661 x_msg_count,
1662 x_msg_data,
1663 l_khr_id ,
1664 l_khr_yields_rec);
1665 */
1666 IF (x_return_status = G_RET_STS_UNEXP_ERROR)
1667 THEN
1668 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1669 ELSIF (x_return_status = G_RET_STS_ERROR)
1670 THEN
1671 RAISE G_EXCEPTION_ERROR;
1672 END IF;
1673
1674 -- update the status in the Out bound Interface Tables
1675 FOR sir_data in sirv_data_csr(p_transaction_number)
1676 LOOP
1677 l_sirv_rec.id := sir_data.id;
1678 IF( l_exception_data_found = TRUE)
1679 THEN
1680 l_sirv_rec.srt_code := 'PROCESS_COMPLETE_ERRORS';
1681 ELSE
1682 l_sirv_rec.srt_code := 'PROCESS_COMPLETE';
1683 END IF;
1684 l_sirv_rec.date_processed := to_date(to_char(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
1685 -- Start of wraper code generated automatically by Debug code generator for OKL_SIF_RETS_PUB.update_sif_rets
1686 IF(IS_DEBUG_PROCEDURE_ON) THEN
1687 BEGIN
1688 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_SIF_RETS_PUB.update_sif_rets ');
1689 END;
1690 END IF;
1691 --Added by kthiruva for Debug Logging
1692 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1693 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_SIF_RETS_PUB.update_sif_rets');
1694 END IF;
1695 OKL_SIF_RETS_PUB.update_sif_rets(p_api_version => p_api_version,
1696 p_init_msg_list => p_init_msg_list,
1697 x_return_status => l_return_status,
1698 x_msg_count => x_msg_count,
1699 x_msg_data => x_msg_data,
1700 p_sirv_rec => l_sirv_rec,
1701 x_sirv_rec => p_sirv_rec);
1702 --Added by kthiruva for Debug Logging
1703 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1704 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);
1705 END IF;
1706
1707 IF(IS_DEBUG_PROCEDURE_ON) THEN
1708 BEGIN
1709 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_SIF_RETS_PUB.update_sif_rets ');
1710 END;
1711 END IF;
1712 -- End of wraper code generated automatically by Debug code generator for OKL_SIF_RETS_PUB.update_sif_rets
1713
1714 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)
1715 THEN
1716 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1717 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1718 RAISE OKL_API.G_EXCEPTION_ERROR;
1719 END IF;
1720 END LOOP;
1721 -- update the status in the In bound Interface Tables
1722 lp_sifv_rec.date_processed := to_date(to_char(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
1723 IF( l_exception_data_found = TRUE)
1724 THEN
1725 lp_sifv_rec.sis_code := 'PROCESS_COMPLETE_ERRORS';
1726 lp_sifv_rec.log_file := 'OKLSTXMLG_' || p_transaction_number || '.log ';
1727 ELSE
1728 lp_sifv_rec.sis_code := 'PROCESS_COMPLETE';
1729 END IF;
1730 -- Start of wraper code generated automatically by Debug code generator for OKL_STREAM_INTERFACES_PUB.update_stream_interfaces
1731 IF(IS_DEBUG_PROCEDURE_ON) THEN
1732 BEGIN
1733 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_STREAM_INTERFACES_PUB.update_stream_interfaces ');
1734 END;
1735 END IF;
1736 --Added by kthiruva for Debug Logging
1737 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1738 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to OKL_STREAM_INTERFACES_PUB.update_stream_interfaces');
1739 END IF;
1740 OKL_STREAM_INTERFACES_PUB.update_stream_interfaces(
1741 p_api_version => p_api_version
1742 ,p_init_msg_list => p_init_msg_list
1743 ,x_return_status => l_return_status
1744 ,x_msg_count => x_msg_count
1745 ,x_msg_data => x_msg_data
1746 ,p_sifv_rec => lp_sifv_rec
1747 ,x_sifv_rec => lx_sifv_rec);
1748 --Added by kthiruva for Debug Logging
1749 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1750 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);
1751 END IF;
1752
1753 IF(IS_DEBUG_PROCEDURE_ON) THEN
1754 BEGIN
1755 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_STREAM_INTERFACES_PUB.update_stream_interfaces ');
1756 END;
1757 END IF;
1758 -- End of wraper code generated automatically by Debug code generator for OKL_STREAM_INTERFACES_PUB.update_stream_interfaces
1759 IF l_return_status = G_RET_STS_ERROR THEN
1760 RAISE G_EXCEPTION_ERROR;
1761 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1762 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1763 END IF;
1764
1765 END IF;
1766 -- Bug 4196515: Emd
1767
1768 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1769 x_msg_data => x_msg_data);
1770
1771 EXCEPTION
1772 WHEN G_EXCEPTION_ERROR
1773 THEN
1774 IF(stream_data_csr%ISOPEN)
1775 THEN
1776 CLOSE stream_data_csr;
1777 END IF;
1778 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1779 p_pkg_name => G_PKG_NAME,
1780 p_exc_name => G_EXC_NAME_ERROR,
1781 x_msg_count => x_msg_count,
1782 x_msg_data => x_msg_data,
1783 p_api_type => '_PVT');
1784 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1785 IF(stream_data_csr%ISOPEN)
1786 THEN
1787 CLOSE stream_data_csr;
1788 END IF;
1789 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1790 p_pkg_name => G_PKG_NAME,
1791 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
1792 x_msg_count => x_msg_count,
1793 x_msg_data => x_msg_data,
1794 p_api_type => '_PVT');
1795 WHEN OTHERS THEN
1796 IF(stream_data_csr%ISOPEN)
1797 THEN
1798 CLOSE stream_data_csr;
1799 END IF;
1800 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1801 p_pkg_name => G_PKG_NAME,
1802 p_exc_name => G_EXC_NAME_OTHERS,
1803 x_msg_count => x_msg_count,
1804 x_msg_data => x_msg_data,
1805 p_api_type => '_PVT');
1806 END PROCESS_STREAM_RESULTS;
1807 -- INFO:
1808 -- This Procedure updates the SAY_CODE of existing Streams for a Contract to HISTORY from WORKING
1809 -- END INFO
1810 PROCEDURE UPDATE_STREAMS_ACTIVITY(p_api_version IN NUMBER
1811 ,p_init_msg_list IN VARCHAR2
1812 ,x_return_status OUT NOCOPY VARCHAR2
1813 ,x_msg_count OUT NOCOPY NUMBER
1814 ,x_msg_data OUT NOCOPY VARCHAR2
1815 ,p_khr_id IN NUMBER)
1816 IS
1817 l_api_name CONSTANT VARCHAR2(40) := 'UPDATE_STREAMS_ACTIVITY';
1818 l_api_version CONSTANT NUMBER := 1.0;
1819 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1820 i NUMBER;
1821 stmv_tbl stmv_tbl_type;
1822 x_stmv_tbl stmv_tbl_type;
1823 CURSOR streams_csr(l_khr_id NUMBER)
1824 IS
1825 SELECT
1826 ID,
1827
1828 SAY_CODE
1829 FROM
1830 OKL_STREAMS_V
1831 WHERE
1832 KHR_ID = l_khr_id
1833 AND
1834 SAY_CODE = G_STREAM_ACTIVITY_WORK;
1835 stm_csr streams_csr%ROWTYPE;
1836 stm_csr_rec streams_csr%ROWTYPE;
1837 BEGIN
1838 x_return_status := G_RET_STS_SUCCESS;
1839 -- populate all the records with new Activity Code
1840 FOR stm_csr IN streams_csr(p_khr_id ) LOOP
1841 -- Update the activity code to HISTORY
1842 stmv_tbl(i).id := stm_csr.id;
1843 stmv_tbl(i).say_code := G_STREAM_ACTIVITY_HIST ;
1844 stmv_tbl(i).date_history := SYSDATE;
1845 END LOOP;
1846 -- call the update for all the records
1847 -- Start of wraper code generated automatically by Debug code generator for Okl_Streams_Pub.update_streams
1848 IF(L_DEBUG_ENABLED='Y') THEN
1849 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
1850 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
1851 END IF;
1852 IF(IS_DEBUG_PROCEDURE_ON) THEN
1853 BEGIN
1854 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call Okl_Streams_Pub.update_streams ');
1855 END;
1856 END IF;
1857 Okl_Streams_Pub.update_streams(l_api_version
1858 ,p_init_msg_list
1859 ,x_return_status
1860 ,x_msg_count
1861 ,x_msg_data
1862 ,stmv_tbl
1863 ,x_stmv_tbl);
1864 IF(IS_DEBUG_PROCEDURE_ON) THEN
1865 BEGIN
1866 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call Okl_Streams_Pub.update_streams ');
1867 END;
1868 END IF;
1869 -- End of wraper code generated automatically by Debug code generator for Okl_Streams_Pub.update_streams
1870 EXCEPTION
1871 WHEN G_EXCEPTION_ERROR THEN
1872 x_return_status := G_RET_STS_ERROR;
1873 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1874 x_return_status := G_RET_STS_UNEXP_ERROR;
1875 WHEN OTHERS THEN
1876 x_return_status := G_RET_STS_UNEXP_ERROR;
1877 -- store SQL error message on message stack for caller
1878 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1879 p_msg_name => G_UNEXPECTED_ERROR,
1880 p_token1 => G_SQLCODE_TOKEN,
1881 p_token1_value => sqlcode,
1882 p_token2 => G_SQLERRM_TOKEN,
1883 p_token2_value => sqlerrm );
1884 -- x_msg_data := 'Unexpected Database Error';
1885 END UPDATE_STREAMS_ACTIVITY;
1886 ------------------------------------------------------------------------------------------
1887 -- PROCEDURE
1888 -- Creates a logfile based upon the exceptions found in the inbound interface tables
1889 -- These exceptions are the ones returned by the Pricing Engine. To understand the logfile
1890 -- some exposure to XML is a must.
1891 -- Inputs: p_transaction_number - the id of the transaction
1892 -- Output: A file by the name 'OKLSTXMLG_1234.log' where 1234 is the id of the transaction
1893 ------------------------------------------------------------------------------------------
1894 PROCEDURE GENERATE_ERROR_LOGFILE(p_transaction_number NUMBER
1895 ,x_return_status VARCHAR2
1896 ) IS
1897 -- define cursor to check any Exceptions in the Inbound Interface Tables
1898 CURSOR exception_data_csr(p_trx_number NUMBER) IS
1899 SELECT
1900 SRMB.ID,
1901 SRMB.ERROR_CODE,
1902 SRMB.ERROR_MESSAGE,
1903 SRMB.TAG_NAME,
1904 SRMB.TAG_ATTRIBUTE_NAME,
1905 SRMB.TAG_ATTRIBUTE_VALUE,
1906 SRMB.DESCRIPTION
1907 FROM
1908 OKL_SIF_RETS SIRB,
1909 OKL_SIF_RET_ERRORS SRMB
1910 WHERE
1911 SIRB.TRANSACTION_NUMBER = p_trx_number
1912 AND
1913 SIRB.ID = SRMB.SIR_ID;
1914 l_message_count NUMBER := 0;
1915 l_error_message_line VARCHAR2(4000) := NULL;
1916 l_error_message_tbl LOG_MSG_TBL_TYPE;
1917 l_exception_data_found BOOLEAN := FALSE;
1918 l_msg_text fnd_new_messages.MESSAGE_TEXT%TYPE;
1919 l_comments VARCHAR2(4000) := NULL;
1920 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1921 BEGIN
1922 FOR exception_data in exception_data_csr(p_transaction_number)
1923 LOOP
1924 IF(l_message_count = 0)
1925 THEN
1926 l_error_message_line := 'REQUEST ID = ' || p_transaction_number || ' TIME PROCESSED = '|| to_char(SYSDATE,'YYYYMMDD HH24MISS');
1927
1928 l_error_message_line := l_error_message_line || G_NEW_LINE;
1929 l_error_message_line := l_error_message_line || 'Errors returned from Pricing Engine :- ';
1930 l_error_message_line := l_error_message_line || G_NEW_LINE;
1931 l_error_message_line := l_error_message_line || 'ERROR CODE :: ' || exception_data.ERROR_CODE;
1932 l_error_message_line := l_error_message_line || G_NEW_LINE;
1933 l_error_message_line := l_error_message_line || 'ERROR MESSAGE :: ' || exception_data.ERROR_MESSAGE;
1934 l_error_message_line := l_error_message_line || G_NEW_LINE;
1935 l_error_message_line := l_error_message_line || 'XML TAG :: ' || exception_data.TAG_NAME ;
1936 l_error_message_line := l_error_message_line || G_NEW_LINE;
1937 l_message_count := l_message_count + 1;
1938 ELSE
1939 l_error_message_line := NULL;
1940 l_error_message_line := l_error_message_line || 'ERROR CODE :: ' || exception_data.ERROR_CODE;
1941 l_error_message_line := l_error_message_line || G_NEW_LINE;
1942 l_error_message_line := l_error_message_line || 'ERROR MESSAGE :: ' || exception_data.ERROR_MESSAGE;
1943 l_error_message_line := l_error_message_line || G_NEW_LINE;
1944 l_error_message_line := l_error_message_line || 'XML TAG :: ' || exception_data.TAG_NAME ;
1945 l_error_message_line := l_error_message_line || G_NEW_LINE;
1946 -- Not used in this release
1947 /*
1948 l_error_message_line := l_error_message_line || 'XML TAG ATTRIBUTE :: ' || exception_data.TAG_ATTRIBUTE_NAME ;
1949 l_error_message_line := l_error_message_line || G_NEW_LINE;
1950 l_error_message_line := l_error_message_line || 'XML TAG ATTRIBUTE VALUE :: ' || exception_data.TAG_ATTRIBUTE_VALUE ;
1951 l_error_message_line := l_error_message_line || G_NEW_LINE;
1952 l_error_message_line := l_error_message_line || 'DETAILS :: ' || exception_data.DESCRIPTION ;
1953 l_error_message_line := l_error_message_line || G_NEW_LINE;
1954 */
1955 l_message_count := l_message_count + 1;
1956 END IF;
1957 l_error_message_tbl(l_message_count) := l_error_message_line;
1958 END LOOP;
1959 IF( l_message_count > 0)
1960 THEN
1961 l_exception_data_found := TRUE;
1962 FND_MESSAGE.SET_NAME ( G_APP_NAME, 'OKL_STREAM_GENERATION_ERROR');
1963 FND_MESSAGE.SET_TOKEN(TOKEN => 'FILE_NAME',
1964 VALUE => 'OKLSTXMLG_' || p_transaction_number || '.log',
1965 TRANSLATE => TRUE);
1966 l_msg_text := FND_MESSAGE.GET;
1967 l_comments := l_msg_text;
1968 l_error_message_tbl(l_message_count + 1) := 'End Errors returned from Pricing Engine' ;
1969 OKL_STREAMS_UTIL.LOG_MESSAGE(p_msgs_tbl => l_error_message_tbl,
1970 p_translate => G_FALSE,
1971 p_file_name => 'OKLSTXMLG_' || p_transaction_number || '.log' ,
1972 x_return_status => l_return_status );
1973 ELSE
1974 l_comments := NULL;
1975 END IF;
1976 END GENERATE_ERROR_LOGFILE;
1977 ------------------------------------------------------------------------------------------
1978 -- PROCEDURE
1979 ------------------------------------------------------------------------------------------
1980 PROCEDURE UPDATE_STATUSES(p_transaction_number NUMBER
1981 ,x_return_status VARCHAR2
1982 ) IS
1983 lp_sirv_rec sirv_rec_type;
1984 lx_sirv_rec sirv_rec_type;
1985 lx_msg_data VARCHAR2(400);
1986 lp_api_version NUMBER := 1.0;
1987 lp_init_msg_list VARCHAR2(1) := 'F';
1988 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1989 l_exception_data_found BOOLEAN := FALSE;
1990 lx_msg_count NUMBER;
1991 lp_sifv_rec OKL_STREAM_INTERFACES_PUB.SIFV_REC_TYPE;
1992 lx_sifv_rec OKL_STREAM_INTERFACES_PUB.SIFV_REC_TYPE;
1993 CURSOR sirv_data_csr (p_trx_number IN NUMBER) IS
1994 SELECT
1995 ID,
1996 TRANSACTION_NUMBER,
1997 SRT_CODE,
1998 EFFECTIVE_PRE_TAX_YIELD,
1999 YIELD_NAME,
2000 INDEX_NUMBER,
2001 EFFECTIVE_AFTER_TAX_YIELD,
2002 NOMINAL_PRE_TAX_YIELD,
2003 NOMINAL_AFTER_TAX_YIELD,
2004 STREAM_INTERFACE_ATTRIBUTE01,
2005 STREAM_INTERFACE_ATTRIBUTE02,
2006 STREAM_INTERFACE_ATTRIBUTE03,
2007 STREAM_INTERFACE_ATTRIBUTE04,
2008 STREAM_INTERFACE_ATTRIBUTE05,
2009 STREAM_INTERFACE_ATTRIBUTE06,
2010 STREAM_INTERFACE_ATTRIBUTE07,
2011 STREAM_INTERFACE_ATTRIBUTE08,
2012 STREAM_INTERFACE_ATTRIBUTE09,
2013 STREAM_INTERFACE_ATTRIBUTE10,
2014 STREAM_INTERFACE_ATTRIBUTE11,
2015 STREAM_INTERFACE_ATTRIBUTE12,
2016 STREAM_INTERFACE_ATTRIBUTE13,
2017 STREAM_INTERFACE_ATTRIBUTE14,
2018 STREAM_INTERFACE_ATTRIBUTE15,
2019 OBJECT_VERSION_NUMBER,
2020 CREATED_BY,
2021 LAST_UPDATED_BY,
2022 CREATION_DATE,
2023 LAST_UPDATE_DATE,
2024 LAST_UPDATE_LOGIN,
2025 IMPLICIT_INTEREST_RATE,
2026 DATE_PROCESSED,
2027
2028 REQUEST_ID,
2029 PROGRAM_APPLICATION_ID,
2030 PROGRAM_ID,
2031 PROGRAM_UPDATE_DATE
2032 FROM Okl_Sif_Rets
2033 WHERE okl_sif_rets.transaction_number = p_trx_number;
2034 CURSOR sif_data_csr (p_transaction_number IN NUMBER) IS
2035 SELECT
2036 ID,
2037 ORP_CODE,
2038 LOG_FILE
2039 FROM Okl_Stream_Interfaces
2040 WHERE okl_stream_interfaces.transaction_number = p_transaction_number;
2041 -- define cursor to check any Exceptions in the Inbound Interface Tables
2042 CURSOR exception_data_csr(p_trx_number NUMBER) IS
2043 SELECT
2044 SRMB.ID
2045 FROM
2046 OKL_SIF_RETS SIRB,
2047 OKL_SIF_RET_ERRORS SRMB
2048 WHERE
2049 SIRB.TRANSACTION_NUMBER = p_trx_number
2050 AND
2051 SIRB.ID = SRMB.SIR_ID;
2052 BEGIN
2053 --check for errors
2054 FOR exception_data in exception_data_csr(p_transaction_number)
2055 LOOP
2056 l_exception_data_found := TRUE;
2057 EXIT;
2058 END LOOP;
2059 -- update the status in the Out bound Interface Tables
2060 FOR sir_data in sirv_data_csr(p_transaction_number)
2061 LOOP
2062 lp_sirv_rec.id := sir_data.id;
2063 IF(l_exception_data_found = TRUE)
2064 THEN
2065 lp_sirv_rec.srt_code := 'PROCESS_COMPLETE_ERRORS';
2066 ELSE
2067 lp_sirv_rec.srt_code := 'PROCESS_COMPLETE';
2068 END IF;
2069 lp_sirv_rec.date_processed := to_date(to_char(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
2070 -- Start of wraper code generated automatically by Debug code generator for OKL_SIF_RETS_PUB.update_sif_rets
2071 IF(L_DEBUG_ENABLED='Y') THEN
2072 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
2073 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
2074 END IF;
2075 IF(IS_DEBUG_PROCEDURE_ON) THEN
2076 BEGIN
2077 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_SIF_RETS_PUB.update_sif_rets ');
2078 END;
2079 END IF;
2080 OKL_SIF_RETS_PUB.update_sif_rets(p_api_version => lp_api_version,
2081 p_init_msg_list => lp_init_msg_list,
2082 x_return_status => l_return_status,
2083 x_msg_count => lx_msg_count,
2084 x_msg_data => lx_msg_data,
2085 p_sirv_rec => lp_sirv_rec,
2086 x_sirv_rec => lx_sirv_rec);
2087 IF(IS_DEBUG_PROCEDURE_ON) THEN
2088 BEGIN
2089 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_SIF_RETS_PUB.update_sif_rets ');
2090 END;
2091 END IF;
2092 -- End of wraper code generated automatically by Debug code generator for OKL_SIF_RETS_PUB.update_sif_rets
2093 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2094 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2095 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2096 RAISE OKL_API.G_EXCEPTION_ERROR;
2097 END IF;
2098 END LOOP;
2099 -- update the status in the In bound Interface Tables
2100 FOR sif_data in sif_data_csr(p_transaction_number)
2101 LOOP
2102 lp_sifv_rec.id := sif_data.id;
2103 lp_sifv_rec.ORP_CODE := sif_data.ORP_CODE;
2104 END LOOP;
2105 lp_sifv_rec.date_processed := to_date(to_char(SYSDATE,'YYYYMMDD HH24MISS'), 'YYYYMMDD HH24MISS');
2106 IF(l_exception_data_found = TRUE)
2107 THEN
2108 -- lp_sifv_rec.sis_code := 'PROCESS_COMPLETE';
2109 lp_sifv_rec.sis_code := 'PROCESS_COMPLETE_ERRORS';
2110 lp_sifv_rec.stream_interface_attribute03 := 'OKLSTXMLG_' || p_transaction_number || '.log ';
2111 ELSE
2112 lp_sifv_rec.sis_code := 'PROCESS_COMPLETE';
2113 END IF;
2114 -- Start of wraper code generated automatically by Debug code generator for OKL_STREAM_INTERFACES_PUB.update_stream_interfaces
2115 IF(IS_DEBUG_PROCEDURE_ON) THEN
2116 BEGIN
2117 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_STREAM_INTERFACES_PUB.update_stream_interfaces ');
2118 END;
2119 END IF;
2120 OKL_STREAM_INTERFACES_PUB.update_stream_interfaces(p_api_version => lp_api_version
2121 ,p_init_msg_list => lp_init_msg_list
2122 ,x_return_status => l_return_status
2123 ,x_msg_count => lx_msg_count
2124 ,x_msg_data => lx_msg_data
2125 ,p_sifv_rec => lp_sifv_rec
2126 ,x_sifv_rec => lx_sifv_rec);
2127
2128 IF(IS_DEBUG_PROCEDURE_ON) THEN
2129 BEGIN
2130 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_STREAM_INTERFACES_PUB.update_stream_interfaces ');
2131 END;
2132 END IF;
2133 -- End of wraper code generated automatically by Debug code generator for OKL_STREAM_INTERFACES_PUB.update_stream_interfaces
2134 IF l_return_status = G_RET_STS_ERROR THEN
2135 RAISE G_EXCEPTION_ERROR;
2136 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2137 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2138 END IF;
2139 END UPDATE_STATUSES;
2140 ------------------------------------------------------------------------------------------
2141 -- PROCEDURE
2142 ------------------------------------------------------------------------------------------
2143 PROCEDURE PROCESS_REST_STRM_RESLTS(p_api_version IN NUMBER
2144 ,p_init_msg_list IN VARCHAR2
2145 ,p_transaction_number IN NUMBER
2146 ,x_return_status OUT NOCOPY VARCHAR2
2147 ,x_msg_count OUT NOCOPY NUMBER
2148 ,x_msg_data OUT NOCOPY VARCHAR2)
2149 IS
2150 l_return_status VARCHAR(1) := G_RET_STS_SUCCESS;
2151 l_api_name CONSTANT VARCHAR2(40) := 'PROCESS_REST_STRM_RESLTS';
2152 l_api_version CONSTANT NUMBER := 1.0;
2153 l_srlv_tbl srlv_tbl_type;
2154 l_yields_tbl yields_tbl_type;
2155 -- l_yields_tbl OKL_CREATE_STREAMS_PUB.csm_yields_tbl_type;
2156 l_sir_id NUMBER;
2157 i NUMBER;
2158 l_chr_id NUMBER;
2159 l_object1_id1 VARCHAR2(40);
2160 CURSOR sirv_data_csr (p_trx_number NUMBER) IS
2161 SELECT
2162 ID
2163 FROM okl_sif_rets
2164 WHERE okl_sif_rets.transaction_number = p_trx_number
2165 AND
2166 INDEX_NUMBER = 0;
2167 CURSOR srlv_data_csr (p_sir_id IN NUMBER) IS
2168 SELECT
2169 ID,
2170 LEVEL_INDEX_NUMBER,
2171 NUMBER_OF_PERIODS,
2172 SIR_ID,
2173 INDEX_NUMBER,
2174 LEVEL_TYPE,
2175 AMOUNT,
2176 ADVANCE_OR_ARREARS,
2177 PERIOD,
2178 LOCK_LEVEL_STEP,
2179 DAYS_IN_PERIOD,
2180 FIRST_PAYMENT_DATE,
2181 STREAM_INTERFACE_ATTRIBUTE1,
2182 STREAM_INTERFACE_ATTRIBUTE2,
2183 STREAM_INTERFACE_ATTRIBUTE3,
2184 STREAM_INTERFACE_ATTRIBUTE4,
2185 STREAM_INTERFACE_ATTRIBUTE5,
2186 STREAM_INTERFACE_ATTRIBUTE6,
2187 STREAM_INTERFACE_ATTRIBUTE7,
2188 STREAM_INTERFACE_ATTRIBUTE8,
2189 STREAM_INTERFACE_ATTRIBUTE9,
2190 STREAM_INTERFACE_ATTRIBUTE10,
2191 STREAM_INTERFACE_ATTRIBUTE11,
2192 STREAM_INTERFACE_ATTRIBUTE12,
2193 STREAM_INTERFACE_ATTRIBUTE13,
2194 STREAM_INTERFACE_ATTRIBUTE14,
2195 STREAM_INTERFACE_ATTRIBUTE15
2196 FROM OKL_SIF_RET_LEVELS
2197 WHERE SIR_ID = p_sir_id;
2198 CURSOR yields_data_csr (p_trx_number NUMBER) IS
2199 SELECT
2200 SIRB.EFFECTIVE_PRE_TAX_YIELD,
2201 SIRB.EFFECTIVE_AFTER_TAX_YIELD,
2202 SIRB.NOMINAL_PRE_TAX_YIELD,
2203 SIRB.NOMINAL_AFTER_TAX_YIELD,
2204 SIRB.IMPLICIT_INTEREST_RATE,
2205 SIYB.YIELD_NAME,
2206 SIYB.METHOD,
2207 SIYB.ARRAY_TYPE,
2208 SIYB.ROE_TYPE,
2209 SIYB.ROE_BASE,
2210 SIYB.COMPOUNDED_METHOD,
2211 SIYB.TARGET_VALUE,
2212 SIYB.INDEX_NUMBER,
2213 SIYB.NOMINAL_YN,
2214 SIYB.PRE_TAX_YN
2215 FROM OKL_SIF_RETS SIRB, OKL_SIF_YIELDS SIYB, OKL_STREAM_INTERFACES SIFB
2216 WHERE SIRB.TRANSACTION_NUMBER = p_trx_number
2217 AND SIFB.TRANSACTION_NUMBER = p_trx_number
2218 AND SIYB.SIF_ID = SIFB.ID
2219 AND SIRB.INDEX_NUMBER = SIYB.INDEX_NUMBER;
2220 CURSOR sif_data_csr (p_trx_number NUMBER ) IS
2221 SELECT
2222 SIFB.OBJECT1_ID1,
2223 SIFB.KHR_ID
2224 FROM
2225 OKL_STREAM_INTERFACES SIFB
2226 WHERE
2227
2228 SIFB.TRANSACTION_NUMBER = p_trx_number;
2229 BEGIN
2230 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
2231 p_pkg_name => 'OKL_PROCESS_STREAMS_PVT',
2232 p_init_msg_list => p_init_msg_list,
2233 l_api_version => l_api_version,
2234 p_api_version => p_api_version,
2235 p_api_type => '_PVT',
2236 x_return_status => l_return_status);
2237 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2238 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2239 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2240 RAISE G_EXCEPTION_ERROR;
2241 END IF;
2242 --Before Processing Stream Results check for any exceptions in the Inbound Interface Tables
2243 GENERATE_ERROR_LOGFILE(p_transaction_number => p_transaction_number
2244 ,x_return_status => l_return_status);
2245 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2246 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2247 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2248 RAISE G_EXCEPTION_ERROR;
2249 END IF;
2250 FOR sifv_data in sif_data_csr(p_transaction_number)
2251 LOOP
2252 l_chr_id := sifv_data.khr_id;
2253 l_object1_id1 := sifv_data.object1_id1;
2254 END LOOP;
2255 -- fetch records from
2256 FOR sirv_data in sirv_data_csr(p_transaction_number)
2257 LOOP
2258 l_sir_id := sirv_data.id;
2259 END LOOP;
2260 i := 1;
2261 FOR srlv_data in srlv_data_csr(l_sir_id)
2262 LOOP
2263 l_srlv_tbl(i) := null;
2264 l_srlv_tbl(i).ID := srlv_data.ID;
2265 l_srlv_tbl(i).LEVEL_INDEX_NUMBER := srlv_data.LEVEL_INDEX_NUMBER;
2266 l_srlv_tbl(i).NUMBER_OF_PERIODS := srlv_data.NUMBER_OF_PERIODS;
2267 l_srlv_tbl(i).SIR_ID := srlv_data.SIR_ID;
2268 l_srlv_tbl(i).INDEX_NUMBER := srlv_data.INDEX_NUMBER;
2269 l_srlv_tbl(i).LEVEL_TYPE := srlv_data.LEVEL_TYPE;
2270 l_srlv_tbl(i).AMOUNT := srlv_data.AMOUNT;
2271 l_srlv_tbl(i).ADVANCE_OR_ARREARS := srlv_data.ADVANCE_OR_ARREARS;
2272 l_srlv_tbl(i).PERIOD := srlv_data.PERIOD;
2273 l_srlv_tbl(i).LOCK_LEVEL_STEP := srlv_data.LOCK_LEVEL_STEP;
2274 l_srlv_tbl(i).DAYS_IN_PERIOD := srlv_data.DAYS_IN_PERIOD;
2275 l_srlv_tbl(i).FIRST_PAYMENT_DATE := srlv_data.FIRST_PAYMENT_DATE;
2276 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE1 := srlv_data.STREAM_INTERFACE_ATTRIBUTE1;
2277 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE2 := srlv_data.STREAM_INTERFACE_ATTRIBUTE2;
2278 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE3 := srlv_data.STREAM_INTERFACE_ATTRIBUTE3;
2279 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE4 := srlv_data.STREAM_INTERFACE_ATTRIBUTE4;
2280 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE5 := srlv_data.STREAM_INTERFACE_ATTRIBUTE5;
2281 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE6 := srlv_data.STREAM_INTERFACE_ATTRIBUTE6;
2282 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE7 := srlv_data.STREAM_INTERFACE_ATTRIBUTE7;
2283 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE8 := srlv_data.STREAM_INTERFACE_ATTRIBUTE8;
2284 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE9 := srlv_data.STREAM_INTERFACE_ATTRIBUTE9;
2285 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE10 := srlv_data.STREAM_INTERFACE_ATTRIBUTE10;
2286 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE11 := srlv_data.STREAM_INTERFACE_ATTRIBUTE11;
2287 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE12 := srlv_data.STREAM_INTERFACE_ATTRIBUTE12;
2288 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE13 := srlv_data.STREAM_INTERFACE_ATTRIBUTE13;
2289 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE14 := srlv_data.STREAM_INTERFACE_ATTRIBUTE14;
2290 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE15 := srlv_data.STREAM_INTERFACE_ATTRIBUTE15;
2291 i := i + 1;
2292 END LOOP;
2293 i := 1;
2294 FOR yields_data in yields_data_csr(p_transaction_number)
2295 LOOP
2296 l_yields_tbl(i) := null;
2297 l_yields_tbl(i).yield_name := yields_data.yield_name;
2298 -- l_yields_tbl(i).yield_name := 'PTIRR';
2299 l_yields_tbl(i).value := yields_data.effective_pre_tax_yield;
2300 -- l_yields_tbl(i).effective_after_tax_yield := yields_data.effective_after_tax_yield;
2301 -- l_yields_tbl(i).nominal_pre_tax_yield := yields_data.nominal_pre_tax_yield;
2302 -- l_yields_tbl(i).nominal_after_tax_yield := yields_data.nominal_after_tax_yield;
2303 l_yields_tbl(i).implicit_interest_rate := yields_data.implicit_interest_rate;
2304 l_yields_tbl(i).method := yields_data.method;
2305 l_yields_tbl(i).array_type := yields_data.array_type;
2306 l_yields_tbl(i).roe_type := yields_data.roe_type;
2307 l_yields_tbl(i).roe_base := yields_data.roe_base;
2308 l_yields_tbl(i).compounded_method := yields_data.compounded_method;
2309 l_yields_tbl(i).target_value := yields_data.target_value;
2310 l_yields_tbl(i).index_number := yields_data.index_number;
2311 l_yields_tbl(i).nominal_yn := yields_data.nominal_yn;
2312 l_yields_tbl(i).pre_tax_yn := yields_data.pre_tax_yn;
2313 i := i + 1;
2314 END LOOP;
2315 -- call the restructure api for processing results
2316 -- Start of wraper code generated automatically by Debug code generator for OKL_AM_RESTRUCTURE_RENTS_PVT.process_results
2317 IF(L_DEBUG_ENABLED='Y') THEN
2318 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
2319 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
2320 END IF;
2321 IF(IS_DEBUG_PROCEDURE_ON) THEN
2322 BEGIN
2323 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_AM_RESTRUCTURE_RENTS_PVT.process_results ');
2324 END;
2325 END IF;
2326 OKL_AM_RESTRUCTURE_RENTS_PVT.process_results(p_api_version => l_api_version,
2327
2328 p_init_msg_list => p_init_msg_list,
2329 p_generation_context => 'RSAM',
2330 p_jtot_object1_code => 'OKL_TRX_QUOTES_B',
2331 p_object1_id1 => l_object1_id1,
2332 p_chr_id => l_chr_id,
2333 p_rent_tbl => l_srlv_tbl,
2334 p_yield_tbl => l_yields_tbl,
2335 x_return_status => l_return_status,
2336 x_msg_count => x_msg_count,
2337 x_msg_data => x_msg_data);
2338 IF(IS_DEBUG_PROCEDURE_ON) THEN
2339 BEGIN
2340 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_AM_RESTRUCTURE_RENTS_PVT.process_results ');
2341 END;
2342 END IF;
2343 -- End of wraper code generated automatically by Debug code generator for OKL_AM_RESTRUCTURE_RENTS_PVT.process_results
2344 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2345 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2346 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2347 RAISE G_EXCEPTION_ERROR;
2348 END IF;
2349 -- update statuses in the inbound and outbound interface tables
2350 UPDATE_STATUSES(p_transaction_number => p_transaction_number
2351 ,x_return_status => l_return_status);
2352 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2353 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2354 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2355 RAISE G_EXCEPTION_ERROR;
2356 END IF;
2357 x_return_status := l_return_status;
2358 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2359 x_msg_data => x_msg_data);
2360 EXCEPTION
2361 WHEN G_EXCEPTION_ERROR THEN
2362 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2363 p_pkg_name => G_PKG_NAME,
2364 p_exc_name => G_EXC_NAME_ERROR,
2365 x_msg_count => x_msg_count,
2366 x_msg_data => x_msg_data,
2367 p_api_type => '_PVT');
2368 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2369 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2370 p_pkg_name => G_PKG_NAME,
2371 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
2372 x_msg_count => x_msg_count,
2373 x_msg_data => x_msg_data,
2374 p_api_type => '_PVT');
2375 WHEN OTHERS THEN
2376 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2377 p_pkg_name => G_PKG_NAME,
2378 p_exc_name => G_EXC_NAME_OTHERS,
2379 x_msg_count => x_msg_count,
2380 x_msg_data => x_msg_data,
2381 p_api_type => '_PVT');
2382 END PROCESS_REST_STRM_RESLTS;
2383 ------------------------------------------------------------------------------------------
2384 -- PROCEDURE
2385 ------------------------------------------------------------------------------------------
2386 PROCEDURE PROCESS_QUOT_STRM_RESLTS(p_api_version IN NUMBER
2387 ,p_init_msg_list IN VARCHAR2
2388 ,p_transaction_number IN NUMBER
2389 ,x_return_status OUT NOCOPY VARCHAR2
2390 ,x_msg_count OUT NOCOPY NUMBER
2391 ,x_msg_data OUT NOCOPY VARCHAR2)
2392 IS
2393 l_return_status VARCHAR(1) := G_RET_STS_SUCCESS;
2394 l_api_name CONSTANT VARCHAR2(40) := 'PROCESS_QUOT_STRM_RESLTS';
2395 l_api_version CONSTANT NUMBER := 1.0;
2396 l_srlv_tbl srlv_tbl_type;
2397 l_yields_tbl yields_tbl_type;
2398 l_sir_id NUMBER;
2399 i NUMBER;
2400 l_chr_id NUMBER;
2401 l_sis_code VARCHAR2(30);
2402 l_amount NUMBER;
2403 CURSOR sirv_data_csr (p_trx_number NUMBER) IS
2404 SELECT
2405 ID
2406 FROM okl_sif_rets
2407 WHERE okl_sif_rets.transaction_number = p_trx_number
2408 AND INDEX_NUMBER = 0;
2409 CURSOR srlv_data_csr (p_sir_id IN NUMBER) IS
2410 SELECT
2411 ID,
2412 LEVEL_INDEX_NUMBER,
2413 NUMBER_OF_PERIODS,
2414 SIR_ID,
2415 INDEX_NUMBER,
2416 LEVEL_TYPE,
2417 AMOUNT,
2418 ADVANCE_OR_ARREARS,
2419 PERIOD,
2420 LOCK_LEVEL_STEP,
2421 DAYS_IN_PERIOD,
2422 FIRST_PAYMENT_DATE,
2423 STREAM_INTERFACE_ATTRIBUTE1,
2424 STREAM_INTERFACE_ATTRIBUTE2,
2425 STREAM_INTERFACE_ATTRIBUTE3,
2426 STREAM_INTERFACE_ATTRIBUTE4,
2427
2428 STREAM_INTERFACE_ATTRIBUTE5,
2429 STREAM_INTERFACE_ATTRIBUTE6,
2430 STREAM_INTERFACE_ATTRIBUTE7,
2431 STREAM_INTERFACE_ATTRIBUTE8,
2432 STREAM_INTERFACE_ATTRIBUTE9,
2433 STREAM_INTERFACE_ATTRIBUTE10,
2434 STREAM_INTERFACE_ATTRIBUTE11,
2435 STREAM_INTERFACE_ATTRIBUTE12,
2436 STREAM_INTERFACE_ATTRIBUTE13,
2437 STREAM_INTERFACE_ATTRIBUTE14,
2438 STREAM_INTERFACE_ATTRIBUTE15
2439 FROM OKL_SIF_RET_LEVELS
2440 WHERE SIR_ID = p_sir_id;
2441 CURSOR yields_data_csr (p_trx_number NUMBER) IS
2442 SELECT
2443 SIRB.EFFECTIVE_PRE_TAX_YIELD,
2444 SIRB.EFFECTIVE_AFTER_TAX_YIELD,
2445 SIRB.NOMINAL_PRE_TAX_YIELD,
2446 SIRB.NOMINAL_AFTER_TAX_YIELD,
2447 SIRB.IMPLICIT_INTEREST_RATE,
2448 SIYB.YIELD_NAME,
2449 SIYB.METHOD,
2450 SIYB.ARRAY_TYPE,
2451 SIYB.ROE_TYPE,
2452 SIYB.ROE_BASE,
2453 SIYB.COMPOUNDED_METHOD,
2454 SIYB.TARGET_VALUE,
2455 SIYB.INDEX_NUMBER,
2456 SIYB.NOMINAL_YN,
2457 SIYB.PRE_TAX_YN
2458 FROM OKL_SIF_RETS SIRB, OKL_SIF_YIELDS SIYB, OKL_STREAM_INTERFACES SIFB
2459 WHERE SIRB.TRANSACTION_NUMBER = p_trx_number
2460 AND SIFB.TRANSACTION_NUMBER = p_trx_number
2461 AND SIYB.SIF_ID = SIFB.ID
2462 AND SIRB.INDEX_NUMBER = SIYB.INDEX_NUMBER;
2463 CURSOR sif_data_csr (p_trx_number NUMBER ) IS
2464 SELECT
2465 SIFB.SIS_CODE,
2466 SIFB.KHR_ID
2467 FROM
2468 OKL_STREAM_INTERFACES SIFB
2469 WHERE
2470 SIFB.TRANSACTION_NUMBER = p_trx_number;
2471 BEGIN
2472 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
2473 p_pkg_name => 'OKL_PROCESS_STREAMS_PVT',
2474 p_init_msg_list => p_init_msg_list,
2475 l_api_version => l_api_version,
2476 p_api_version => p_api_version,
2477 p_api_type => '_PVT',
2478 x_return_status => l_return_status);
2479 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2480 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2481 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2482 RAISE G_EXCEPTION_ERROR;
2483 END IF;
2484 FOR sifv_data in sif_data_csr(p_transaction_number)
2485 LOOP
2486 l_chr_id := sifv_data.khr_id;
2487 l_sis_code := sifv_data.sis_code;
2488 END LOOP;
2489 --Before Processing Stream Results check for any exceptions in the Inbound Interface Tables
2490 GENERATE_ERROR_LOGFILE(p_transaction_number => p_transaction_number
2491 ,x_return_status => l_return_status);
2492 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2493 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2494 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2495 RAISE G_EXCEPTION_ERROR;
2496 END IF;
2497 -- fetch records from
2498 FOR sirv_data in sirv_data_csr(p_transaction_number)
2499 LOOP
2500 l_sir_id := sirv_data.id;
2501 END LOOP;
2502 i := 1;
2503 FOR srlv_data in srlv_data_csr(l_sir_id)
2504 LOOP
2505 l_srlv_tbl(i) := null;
2506 l_srlv_tbl(i).ID := srlv_data.ID;
2507 l_srlv_tbl(i).LEVEL_INDEX_NUMBER := srlv_data.LEVEL_INDEX_NUMBER;
2508 l_srlv_tbl(i).NUMBER_OF_PERIODS := srlv_data.NUMBER_OF_PERIODS;
2509 l_srlv_tbl(i).SIR_ID := srlv_data.SIR_ID;
2510 l_srlv_tbl(i).INDEX_NUMBER := srlv_data.INDEX_NUMBER;
2511 l_srlv_tbl(i).LEVEL_TYPE := srlv_data.LEVEL_TYPE;
2512 -- akjain 08/30/2002
2513 -- added to format the amount
2514 l_amount := srlv_data.AMOUNT;
2515 l_srlv_tbl(i).AMOUNT := format_number(l_amount, l_chr_id);
2516 l_srlv_tbl(i).ADVANCE_OR_ARREARS := srlv_data.ADVANCE_OR_ARREARS;
2517 l_srlv_tbl(i).PERIOD := srlv_data.PERIOD;
2518 l_srlv_tbl(i).LOCK_LEVEL_STEP := srlv_data.LOCK_LEVEL_STEP;
2519 l_srlv_tbl(i).DAYS_IN_PERIOD := srlv_data.DAYS_IN_PERIOD;
2520 l_srlv_tbl(i).FIRST_PAYMENT_DATE := srlv_data.FIRST_PAYMENT_DATE;
2521 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE1 := srlv_data.STREAM_INTERFACE_ATTRIBUTE1;
2522 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE2 := srlv_data.STREAM_INTERFACE_ATTRIBUTE2;
2523 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE3 := srlv_data.STREAM_INTERFACE_ATTRIBUTE3;
2524 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE4 := srlv_data.STREAM_INTERFACE_ATTRIBUTE4;
2525 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE5 := srlv_data.STREAM_INTERFACE_ATTRIBUTE5;
2526 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE6 := srlv_data.STREAM_INTERFACE_ATTRIBUTE6;
2527
2528 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE7 := srlv_data.STREAM_INTERFACE_ATTRIBUTE7;
2529 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE8 := srlv_data.STREAM_INTERFACE_ATTRIBUTE8;
2530 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE9 := srlv_data.STREAM_INTERFACE_ATTRIBUTE9;
2531 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE10 := srlv_data.STREAM_INTERFACE_ATTRIBUTE10;
2532 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE11 := srlv_data.STREAM_INTERFACE_ATTRIBUTE11;
2533 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE12 := srlv_data.STREAM_INTERFACE_ATTRIBUTE12;
2534 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE13 := srlv_data.STREAM_INTERFACE_ATTRIBUTE13;
2535 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE14 := srlv_data.STREAM_INTERFACE_ATTRIBUTE14;
2536 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE15 := srlv_data.STREAM_INTERFACE_ATTRIBUTE15;
2537 i := i + 1;
2538 END LOOP;
2539 i := 1;
2540 FOR yields_data in yields_data_csr(p_transaction_number)
2541 LOOP
2542 l_yields_tbl(i) := null;
2543 l_yields_tbl(i).yield_name := yields_data.yield_name;
2544 l_yields_tbl(i).value := yields_data.effective_pre_tax_yield;
2545
2546
2547 --l_yields_tbl(i).effective_after_tax_yield := yields_data.effective_after_tax_yield;
2548 --l_yields_tbl(i).nominal_pre_tax_yield := yields_data.nominal_pre_tax_yield;
2549 --l_yields_tbl(i).nominal_after_tax_yield := yields_data.nominal_after_tax_yield;
2550 l_yields_tbl(i).implicit_interest_rate := yields_data.implicit_interest_rate;
2551 l_yields_tbl(i).method := yields_data.method;
2552 l_yields_tbl(i).array_type := yields_data.array_type;
2553 l_yields_tbl(i).roe_type := yields_data.roe_type;
2554 l_yields_tbl(i).roe_base := yields_data.roe_base;
2555 l_yields_tbl(i).compounded_method := yields_data.compounded_method;
2556 l_yields_tbl(i).target_value := yields_data.target_value;
2557 l_yields_tbl(i).index_number := yields_data.index_number;
2558 l_yields_tbl(i).nominal_yn := yields_data.nominal_yn;
2559 l_yields_tbl(i).pre_tax_yn := yields_data.pre_tax_yn;
2560 i := i + 1;
2561 END LOOP;
2562 -- call the restructure api for processing results
2563 -- Start of wraper code generated automatically by Debug code generator for okl_solve_for_rent_pvt.process_results
2564 /*
2565 IF(L_DEBUG_ENABLED='Y') THEN
2566 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
2567 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
2568 END IF;
2569 IF(IS_DEBUG_PROCEDURE_ON) THEN
2570 BEGIN
2571 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call okl_solve_for_rent_pvt.process_results ');
2572 END;
2573 END IF;
2574 okl_solve_for_rent_pvt.process_results(p_api_version => l_api_version,
2575 p_init_msg_list => p_init_msg_list,
2576 p_chr_id => l_chr_id,
2577 p_trans_status => l_sis_code,
2578 p_trans_number => p_transaction_number,
2579 p_rent_tbl => l_srlv_tbl,
2580 p_yield_tbl => l_yields_tbl,
2581 x_return_status => l_return_status,
2582 x_msg_count => x_msg_count,
2583 x_msg_data => x_msg_data);
2584 IF(IS_DEBUG_PROCEDURE_ON) THEN
2585 BEGIN
2586 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call okl_solve_for_rent_pvt.process_results ');
2587 END;
2588 END IF;
2589 -- End of wraper code generated automatically by Debug code generator for okl_solve_for_rent_pvt.process_results
2590 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2591 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2592 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2593 RAISE G_EXCEPTION_ERROR;
2594 END IF;
2595 */
2596 -- update statuses in the inbound and outbound interface tables
2597 UPDATE_STATUSES(p_transaction_number => p_transaction_number
2598 ,x_return_status => l_return_status);
2599 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2600 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2601 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2602 RAISE G_EXCEPTION_ERROR;
2603 END IF;
2604 x_return_status := l_return_status;
2605 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2606 x_msg_data => x_msg_data);
2607 EXCEPTION
2608 WHEN G_EXCEPTION_ERROR THEN
2609 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2610 p_pkg_name => G_PKG_NAME,
2611 p_exc_name => G_EXC_NAME_ERROR,
2612 x_msg_count => x_msg_count,
2613 x_msg_data => x_msg_data,
2614 p_api_type => '_PVT');
2615 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2616 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2617 p_pkg_name => G_PKG_NAME,
2618 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
2619 x_msg_count => x_msg_count,
2620 x_msg_data => x_msg_data,
2621 p_api_type => '_PVT');
2622 WHEN OTHERS THEN
2623
2624 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2625 p_pkg_name => G_PKG_NAME,
2626 p_exc_name => G_EXC_NAME_OTHERS,
2627 x_msg_count => x_msg_count,
2628 x_msg_data => x_msg_data,
2629
2630 p_api_type => '_PVT');
2631 END PROCESS_QUOT_STRM_RESLTS;
2632 ------------------------------------------------------------------------------------------
2633 -- PROCEDURE
2634 ------------------------------------------------------------------------------------------
2635 PROCEDURE PROCESS_RENW_STRM_RESLTS(p_api_version IN NUMBER
2636 ,p_init_msg_list IN VARCHAR2
2637 ,p_transaction_number IN NUMBER
2638 ,x_return_status OUT NOCOPY VARCHAR2
2639 ,x_msg_count OUT NOCOPY NUMBER
2640 ,x_msg_data OUT NOCOPY VARCHAR2)
2641 IS
2642 l_return_status VARCHAR(1) := G_RET_STS_SUCCESS;
2643 l_api_name CONSTANT VARCHAR2(40) := 'PROCESS_RENW_STRM_RESLTS';
2644 l_api_version CONSTANT NUMBER := 1.0;
2645 l_srlv_tbl srlv_tbl_type;
2646 l_yields_tbl yields_tbl_type;
2647 l_sir_id NUMBER;
2648 i NUMBER;
2649 p_trqv_rec trqv_rec_type;
2650 x_trqv_rec trqv_rec_type;
2651 p_payment_tbl payment_tbl_type;
2652 l_khr_id NUMBER;
2653 l_object1_id1 okl_stream_interfaces.OBJECT1_ID1%TYPE;
2654 l_sis_code okl_stream_interfaces.SIS_CODE%TYPE;
2655 l_number_months NUMBER;
2656
2657 --Modified by kthiruva for the VR build
2658 CURSOR ppd_data_csr (p_trx_number NUMBER) IS
2659 SELECT
2660 SRLB.ID,
2661 SRLB.LEVEL_INDEX_NUMBER,
2662 SRLB.NUMBER_OF_PERIODS,
2663 SRLB.SIR_ID,
2664 SRLB.INDEX_NUMBER,
2665 SRLB.LEVEL_TYPE,
2666 SRLB.AMOUNT,
2667 SRLB.ADVANCE_OR_ARREARS,
2668 SRLB.PERIOD,
2669 SRLB.LOCK_LEVEL_STEP,
2670 SRLB.DAYS_IN_PERIOD,
2671 SRLB.FIRST_PAYMENT_DATE,
2672 SIFB.KHR_ID,
2673 SILB.KLE_ID
2674 FROM OKL_SIF_RET_LEVELS SRLB, OKL_SIF_RETS SIRB, OKL_STREAM_INTERFACES SIFB,
2675 OKL_SIF_LINES SILB
2676 WHERE SIFB.TRANSACTION_NUMBER = p_trx_number
2677 AND SIRB.TRANSACTION_NUMBER = SIFB.TRANSACTION_NUMBER
2678 AND SILB.SIF_ID = SIFB.ID
2679 AND SRLB.SIR_ID = SIRB.ID
2680 AND SRLB.INDEX_NUMBER = SILB.INDEX_NUMBER
2681 AND SRLB.LEVEL_TYPE in ('Payment','Principal')
2682 AND SRLB.LOCK_LEVEL_STEP = 'N';
2683
2684 CURSOR srlv_data_csr (p_trx_number NUMBER) IS
2685 SELECT
2686 OKL_SIF_RET_LEVELS.ID,
2687 OKL_SIF_RET_LEVELS.LEVEL_INDEX_NUMBER,
2688 OKL_SIF_RET_LEVELS.NUMBER_OF_PERIODS,
2689 OKL_SIF_RET_LEVELS.SIR_ID,
2690 OKL_SIF_RET_LEVELS.INDEX_NUMBER,
2691 OKL_SIF_RET_LEVELS.LEVEL_TYPE,
2692 OKL_SIF_RET_LEVELS.AMOUNT,
2693 OKL_SIF_RET_LEVELS.ADVANCE_OR_ARREARS,
2694 OKL_SIF_RET_LEVELS.PERIOD,
2695 OKL_SIF_RET_LEVELS.LOCK_LEVEL_STEP,
2696 OKL_SIF_RET_LEVELS.DAYS_IN_PERIOD,
2697 OKL_SIF_RET_LEVELS.FIRST_PAYMENT_DATE,
2698 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE1,
2699 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE2,
2700 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE3,
2701 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE4,
2702 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE5,
2703 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE6,
2704 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE7,
2705 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE8,
2706 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE9,
2707 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE10,
2708 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE11,
2709 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE12,
2710 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE13,
2711 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE14,
2712 OKL_SIF_RET_LEVELS.STREAM_INTERFACE_ATTRIBUTE15
2713 FROM OKL_SIF_RET_LEVELS, OKL_SIF_RETS
2714 WHERE OKL_SIF_RETS.transaction_number = p_trx_number
2715 AND OKL_SIF_RETS.INDEX_NUMBER = 0
2716 AND OKL_SIF_RET_LEVELS.SIR_ID = OKL_SIF_RETS.ID
2717 AND OKL_SIF_RET_LEVELS.LEVEL_TYPE = 'Payment'
2718 AND OKL_SIF_RET_LEVELS.LOCK_LEVEL_STEP = 'N';
2719 CURSOR sirv_data_csr (p_trx_number NUMBER) IS
2720 SELECT
2721 ID
2722 FROM okl_sif_rets
2723 WHERE okl_sif_rets.transaction_number = p_trx_number;
2724 CURSOR sifv_data_csr (p_trx_number IN NUMBER) IS
2725 SELECT
2726 OBJECT1_ID1,
2727 SIS_CODE
2728 FROM OKL_STREAM_INTERFACES
2729 WHERE TRANSACTION_NUMBER = p_trx_number;
2730
2731 --Added by kthiruva for VR build
2732 --This cursor checks if the adjusting stub returned by the Inbound XML
2733 --was created for a stub or for a period ic payment
2734 CURSOR check_stub_csr(p_chr_id NUMBER,
2735 p_cle_id NUMBER,
2736 p_date DATE,
2737 p_slh_id NUMBER)
2738 IS
2739 SELECT TO_NUMBER(crl.rule_information7) stub_days
2740 FROM okc_rule_groups_b crg,
2741 okc_rules_b crl
2742 WHERE crl.rgp_id = crg.id
2743 AND crl.object2_id1 = p_slh_id
2744 AND crg.rgd_code = 'LALEVL'
2745 AND crl.rule_information_category = 'LASLL'
2746 AND crg.dnz_chr_id = p_chr_id
2747 AND crg.cle_id = p_cle_id
2748 AND FND_DATE.canonical_to_date(crl.rule_information2)+TO_NUMBER(crl.rule_information7) = p_date;
2749
2750 --Added by kthiruva on 12-Dec-2005
2751 --This cursor obtains the SLH id of the payment corresponding to a kle_id
2752 --Bug 4878162 - Start of Changes
2753 CURSOR get_slh_csr(p_chr_id NUMBER,
2754 p_cle_id NUMBER)
2755 IS
2756 SELECT crl.id slh_id
2757 FROM okc_rule_groups_b crg,
2758 okc_rules_b crl
2759 WHERE crl.rgp_id = crg.id
2760 AND crg.rgd_code = 'LALEVL'
2761 AND crl.rule_information_category = 'LASLH'
2762 AND crg.dnz_chr_id = p_chr_id
2763 AND crg.cle_id = p_cle_id
2764 ORDER BY crl.rule_information1;
2765
2766 CURSOR get_freq_csr(p_chr_id NUMBER,
2767 p_cle_id NUMBER,
2768 p_slh_id NUMBER)
2769 IS
2770 SELECT crl.object1_id1 frequency
2771 FROM okc_rule_groups_b crg,
2772 okc_rules_b crl
2773 WHERE crl.rgp_id = crg.id
2774 AND crl.object2_id1 = p_slh_id
2775 AND crg.rgd_code = 'LALEVL'
2776 AND crl.rule_information_category = 'LASLL'
2777 AND crg.dnz_chr_id = p_chr_id
2778 AND crg.cle_id = p_cle_id;
2779
2780 l_slh_id NUMBER;
2781 l_frequency VARCHAR2(1);
2782 --Bug 4878162 - End of Changes
2783
2784 l_end_accrual_date DATE;
2785 l_stub_days NUMBER;
2786 --kthiruva - End of Changes for VR build
2787
2788 BEGIN
2789 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
2790 p_pkg_name => 'OKL_PROCESS_STREAMS_PVT',
2791 p_init_msg_list => p_init_msg_list,
2792 l_api_version => l_api_version,
2793 p_api_version => p_api_version,
2794 p_api_type => '_PVT',
2795 x_return_status => l_return_status);
2796 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2797 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2798 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2799 RAISE G_EXCEPTION_ERROR;
2800 END IF;
2801 --Before Processing Stream Results check for any exceptions in the Inbound Interface Tables
2802 GENERATE_ERROR_LOGFILE(p_transaction_number => p_transaction_number
2803 ,x_return_status => l_return_status);
2804 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2805 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2806 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2807 RAISE G_EXCEPTION_ERROR;
2808 END IF;
2809 -- update statuses in the inbound and outbound interface tables
2810 UPDATE_STATUSES(p_transaction_number => p_transaction_number
2811 ,x_return_status => l_return_status);
2812 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2813 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2814 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2815 RAISE G_EXCEPTION_ERROR;
2816 END IF;
2817 -- check if request is for Principal Paydown
2818 OPEN sifv_data_csr(p_transaction_number);
2819 FETCH sifv_data_csr INTO l_object1_id1, l_sis_code;
2820 CLOSE sifv_data_csr;
2821
2822 IF (okl_cs_principal_paydown_pvt.check_if_ppd(l_object1_id1)='Y') THEN
2823 i := 1;
2824 FOR ppd_data in ppd_data_csr(p_transaction_number)
2825 LOOP
2826 l_khr_id := ppd_data.KHR_ID;
2827 p_payment_tbl(i).khr_id := ppd_data.KHR_ID;
2828 p_payment_tbl(i).kle_id := ppd_data.KLE_ID;
2829 --Modified by kthiruva on 12-Dec-2005
2830 -- A value of 'Y' for advance_or_arrears denotes that
2831 --the paynent is in Arrears
2832 --Bug 4878162 - Start of Changes
2833 IF (ppd_data.ADVANCE_OR_ARREARS = 'Y') THEN
2834 p_payment_tbl(i).arrears_yn := 'Y';
2835 ELSE
2836 p_payment_tbl(i).arrears_yn := 'N';
2837 END IF;
2838 --Bug 4878162 - End of Changes
2839 okl_st_code_conversions.reverse_translate_periodicity(
2840 ppd_data.PERIOD,
2841 p_payment_tbl(i).frequency);
2842 IF (ppd_data.PERIOD = 'Stub') THEN
2843 IF (ppd_data.ADVANCE_OR_ARREARS = 'N') THEN
2844 l_end_accrual_date := ppd_data.first_payment_date + ppd_data.days_in_period;
2845 ELSE
2846 l_end_accrual_date := ppd_data.first_payment_date ;
2847 END IF;
2848 --Added by kthiruva on 12-Dec-2005
2849 --Obtaining the payment header information
2850 --Bug 4878162 - Start of Changes
2851 OPEN get_slh_csr(ppd_data.khr_id,
2852 ppd_data.kle_id);
2853 FETCH get_slh_csr INTO l_slh_id;
2854 IF get_slh_csr%FOUND THEN
2855 --A stub encountered could be the adjusting stub created for
2856 --either a periodic payment or for a stub.
2857 OPEN check_Stub_csr(ppd_data.khr_id,
2858 ppd_data.kle_id,
2859 l_end_accrual_date,
2860 l_slh_id);
2861 FETCH check_stub_csr INTO l_stub_days;
2862 --If the cursor returns a value then the adjusting stub is due to a stub in
2863 --the original payment plan.
2864 --Else it is a stub created for a periodic payment plan.
2865 IF check_stub_csr%FOUND THEN
2866 p_payment_tbl(i).stub_days := l_stub_days;
2867 p_payment_tbl(i).stub_amount := ppd_data.AMOUNT;
2868 ELSE
2869 p_payment_tbl(i).periods := ppd_data.NUMBER_OF_PERIODS;
2870 p_payment_tbl(i).amount := ppd_data.AMOUNT;
2871 OPEN get_freq_csr(ppd_data.khr_id,
2872 ppd_data.kle_id,
2873 l_slh_id);
2874 FETCH get_freq_csr INTO l_frequency;
2875 IF get_freq_csr%FOUND THEN
2876 p_payment_tbl(i).frequency := l_frequency;
2877 END IF;
2878 CLOSE get_freq_csr;
2879 END IF;
2880 --Added by kthiruva on 02-Dec-2005
2881 --Bug 4777531 - Start of Changes
2882 CLOSE check_Stub_csr;
2883 --Bug 4777531 - End of changes
2884 END IF;
2885 CLOSE get_slh_csr;
2886 --Bug 4878162 - End of Changes
2887 ELSE
2888 p_payment_tbl(i).periods := ppd_data.NUMBER_OF_PERIODS;
2889 p_payment_tbl(i).amount := ppd_data.AMOUNT;
2890 END IF;
2891 -- Bug 4047717 back out payment dates if in arrears to match SLL dates
2892 IF (p_payment_tbl(i).arrears_yn = 'Y' AND
2893 p_payment_tbl(i).frequency <> 'T') THEN
2894 IF (p_payment_tbl(i).frequency = 'M') THEN
2895 l_number_months := -1;
2896 ELSIF (p_payment_tbl(i).frequency = 'Q') THEN
2897 l_number_months := -3;
2898 ELSIF (p_payment_tbl(i).frequency = 'S') THEN
2899 l_number_months := -6;
2900 ELSIF (p_payment_tbl(i).frequency = 'A') THEN
2901 l_number_months := -12;
2902 END IF;
2903 p_payment_tbl(i).start_date := ADD_MONTHS(ppd_data.FIRST_PAYMENT_DATE,l_number_months);
2904 ELSIF (p_payment_tbl(i).arrears_yn = 'Y' AND
2905 p_payment_tbl(i).frequency = 'T') THEN
2906 p_payment_tbl(i).start_date := ppd_data.FIRST_PAYMENT_DATE - p_payment_tbl(i).stub_days;
2907 ELSE
2908 p_payment_tbl(i).start_date := ppd_data.FIRST_PAYMENT_DATE;
2909 END IF;
2910 i := i + 1;
2911 END LOOP;
2912 okl_cs_principal_paydown_pvt.store_esg_payments(p_api_version => p_api_version
2913 ,p_init_msg_list => p_init_msg_list
2914 ,x_return_status => l_return_status
2915 ,x_msg_count => x_msg_count
2916 ,x_msg_data => x_msg_data
2917 ,p_ppd_request_id => l_object1_id1
2918 ,p_ppd_khr_id => l_khr_id
2919 ,p_payment_tbl => p_payment_tbl);
2920 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2921 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2922 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2923 RAISE G_EXCEPTION_ERROR;
2924 END IF;
2925 ELSE
2926 -- fetch records for the RENT LEVELS
2927 FOR srlv_data in srlv_data_csr(p_transaction_number)
2928 LOOP
2929 p_trqv_rec.amount := srlv_data.AMOUNT;
2930 END LOOP;
2931
2932 FOR sifv_data in sifv_data_csr(p_transaction_number)
2933 LOOP
2934 p_trqv_rec.id := sifv_data.OBJECT1_ID1;
2935 p_trqv_rec.request_status_code := sifv_data.SIS_CODE;
2936 END LOOP;
2937 -- call the restructure api for processing results
2938 -- Start of wraper code generated automatically by Debug code generator for okl_cs_lease_renewal_pub.update_lrnw_request
2939 IF(L_DEBUG_ENABLED='Y') THEN
2940 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
2941 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
2942 END IF;
2943 IF(IS_DEBUG_PROCEDURE_ON) THEN
2944 BEGIN
2945 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call okl_cs_lease_renewal_pub.update_lrnw_request ');
2946 END;
2947 END IF;
2948 okl_cs_lease_renewal_pub.update_lrnw_request(p_api_version => p_api_version
2949 ,p_init_msg_list => p_init_msg_list
2950 ,x_return_status => l_return_status
2951 ,x_msg_count => x_msg_count
2952 ,x_msg_data => x_msg_data
2953 ,p_trqv_rec => p_trqv_rec
2954 ,x_trqv_rec => x_trqv_rec);
2955 IF(IS_DEBUG_PROCEDURE_ON) THEN
2956 BEGIN
2957 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call okl_cs_lease_renewal_pub.update_lrnw_request ');
2958 END;
2959 END IF;
2960 -- End of wraper code generated automatically by Debug code generator for okl_cs_lease_renewal_pub.update_lrnw_request
2961 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
2962 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2963 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
2964 RAISE G_EXCEPTION_ERROR;
2965 END IF;
2966 END IF;
2967 x_return_status := l_return_status;
2968 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2969 x_msg_data => x_msg_data);
2970 EXCEPTION
2971 WHEN G_EXCEPTION_ERROR THEN
2972 --Added by kthiruva on 02-Dec-2005
2973 --Bug 4777531 - Start of Changes
2974 IF(check_stub_csr%ISOPEN)
2975 THEN
2976 CLOSE check_stub_csr;
2977 END IF;
2978 --Bug 4777531 - End of Changes
2979 --Added by kthiruva on 02-Dec-2005
2980 --Bug 4878162 - Start of Changes
2981 IF(get_slh_csr%ISOPEN)
2982 THEN
2983 CLOSE get_slh_csr;
2984 END IF;
2985 IF(get_freq_csr%ISOPEN)
2986 THEN
2987 CLOSE get_freq_csr;
2988 END IF;
2989 --Bug 4878162 - End of Changes
2990 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
2991 p_pkg_name => G_PKG_NAME,
2992 p_exc_name => G_EXC_NAME_ERROR,
2993 x_msg_count => x_msg_count,
2994 x_msg_data => x_msg_data,
2995 p_api_type => '_PVT');
2996 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2997 --Added by kthiruva on 02-Dec-2005
2998 --Bug 4777531 - Start of Changes
2999 IF(check_stub_csr%ISOPEN)
3000 THEN
3001 CLOSE check_stub_csr;
3002 END IF;
3003 --Bug 4777531 - End of Changes
3004 --Added by kthiruva on 02-Dec-2005
3005 --Bug 4878162 - Start of Changes
3006 IF(get_slh_csr%ISOPEN)
3007 THEN
3008 CLOSE get_slh_csr;
3009 END IF;
3010 IF(get_freq_csr%ISOPEN)
3011 THEN
3012 CLOSE get_freq_csr;
3013 END IF;
3014 --Bug 4878162 - End of Changes
3015 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3016 p_pkg_name => G_PKG_NAME,
3017 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
3018 x_msg_count => x_msg_count,
3019 x_msg_data => x_msg_data,
3020 p_api_type => '_PVT');
3021 WHEN OTHERS THEN
3022 --Added by kthiruva on 02-Dec-2005
3023 --Bug 4777531 - Start of Changes
3024 IF(check_stub_csr%ISOPEN)
3025 THEN
3026 CLOSE check_stub_csr;
3027 END IF;
3028 --Bug 4777531 - End of Changes
3029 --Added by kthiruva on 02-Dec-2005
3030 --Bug 4878162 - Start of Changes
3031 IF(get_slh_csr%ISOPEN)
3032 THEN
3033 CLOSE get_slh_csr;
3034 END IF;
3035 IF(get_freq_csr%ISOPEN)
3036 THEN
3037 CLOSE get_freq_csr;
3038 END IF;
3039 --Bug 4878162 - End of Changes
3040 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3041 p_pkg_name => G_PKG_NAME,
3042 p_exc_name => G_EXC_NAME_OTHERS,
3043 x_msg_count => x_msg_count,
3044 x_msg_data => x_msg_data,
3045 p_api_type => '_PVT');
3046 END PROCESS_RENW_STRM_RESLTS;
3047
3048 PROCEDURE reorganise_payment_tbl(p_srlv_tbl IN srlv_tbl_type
3049 ,x_srlv_tbl OUT NOCOPY srlv_tbl_type)
3050 IS
3051 l_srlv_tbl srlv_tbl_type := p_srlv_tbl;
3052 l_temp_srlv_tbl srlv_tbl_type ;
3053 i NUMBER := 0;
3054 j NUMBER := 0;
3055 BEGIN
3056 j := l_srlv_tbl.FIRST;
3057 IF l_srlv_tbl.COUNT > 0 THEN
3058 --Assigning the first record as is
3059 l_temp_srlv_tbl(i).ID := l_srlv_tbl(j).ID;
3060 l_temp_srlv_tbl(i).LEVEL_INDEX_NUMBER := l_srlv_tbl(j).LEVEL_INDEX_NUMBER;
3061 l_temp_srlv_tbl(i).NUMBER_OF_PERIODS := l_srlv_tbl(j).NUMBER_OF_PERIODS;
3062 l_temp_srlv_tbl(i).SIR_ID := l_srlv_tbl(j).SIR_ID;
3063 l_temp_srlv_tbl(i).INDEX_NUMBER := l_srlv_tbl(j).INDEX_NUMBER;
3064 l_temp_srlv_tbl(i).LEVEL_TYPE := l_srlv_tbl(j).LEVEL_TYPE;
3065 l_temp_srlv_tbl(i).AMOUNT := l_srlv_tbl(j).AMOUNT;
3066 l_temp_srlv_tbl(i).ADVANCE_OR_ARREARS := l_srlv_tbl(j).ADVANCE_OR_ARREARS;
3067 l_temp_srlv_tbl(i).PERIOD := l_srlv_tbl(j).PERIOD;
3068 l_temp_srlv_tbl(i).LOCK_LEVEL_STEP := l_srlv_tbl(j).LOCK_LEVEL_STEP;
3069 l_temp_srlv_tbl(i).DAYS_IN_PERIOD := l_srlv_tbl(j).DAYS_IN_PERIOD;
3070 l_temp_srlv_tbl(i).FIRST_PAYMENT_DATE := l_srlv_tbl(j).FIRST_PAYMENT_DATE;
3071 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE1 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE1;
3072 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE2 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE2;
3073 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE3 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE3;
3074 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE4 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE4;
3075 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE5 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE5;
3076 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE6 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE6;
3077 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE7 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE7;
3078 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE8 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE8;
3079 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE9 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE9;
3080 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE10 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE10;
3081 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE11 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE11;
3082 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE12 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE12;
3083 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE13 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE13;
3084 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE14 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE14;
3085 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE15 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE15;
3086 END IF;
3087
3088 LOOP
3089 j := l_srlv_tbl.NEXT(j);
3090 --Added by kthiruva on 15-Jun-2006 for Bug 5286917
3091 EXIT WHEN j is NULL;
3092 --End of Changes
3093 IF (l_temp_srlv_tbl(i).INDEX_NUMBER = l_srlv_tbl(j).INDEX_NUMBER AND
3094 l_temp_srlv_tbl(i).AMOUNT = l_srlv_tbl(j).AMOUNT AND
3095 l_temp_srlv_tbl(i).PERIOD = l_srlv_tbl(j).PERIOD) THEN
3096 l_temp_srlv_tbl(i).NUMBER_OF_PERIODS := l_temp_srlv_tbl(i).NUMBER_OF_PERIODS +
3097 l_srlv_tbl(j).NUMBER_OF_PERIODS;
3098 ELSE
3099 i := i + 1;
3100 l_temp_srlv_tbl(i).ID := l_srlv_tbl(j).ID;
3101 l_temp_srlv_tbl(i).LEVEL_INDEX_NUMBER := l_srlv_tbl(j).LEVEL_INDEX_NUMBER;
3102 l_temp_srlv_tbl(i).NUMBER_OF_PERIODS := l_srlv_tbl(j).NUMBER_OF_PERIODS;
3103 l_temp_srlv_tbl(i).SIR_ID := l_srlv_tbl(j).SIR_ID;
3104 l_temp_srlv_tbl(i).INDEX_NUMBER := l_srlv_tbl(j).INDEX_NUMBER;
3105 l_temp_srlv_tbl(i).LEVEL_TYPE := l_srlv_tbl(j).LEVEL_TYPE;
3106 l_temp_srlv_tbl(i).AMOUNT := l_srlv_tbl(j).AMOUNT;
3107 l_temp_srlv_tbl(i).ADVANCE_OR_ARREARS := l_srlv_tbl(j).ADVANCE_OR_ARREARS;
3108 l_temp_srlv_tbl(i).PERIOD := l_srlv_tbl(j).PERIOD;
3109 l_temp_srlv_tbl(i).LOCK_LEVEL_STEP := l_srlv_tbl(j).LOCK_LEVEL_STEP;
3110 l_temp_srlv_tbl(i).DAYS_IN_PERIOD := l_srlv_tbl(j).DAYS_IN_PERIOD;
3111 l_temp_srlv_tbl(i).FIRST_PAYMENT_DATE := l_srlv_tbl(j).FIRST_PAYMENT_DATE;
3112 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE1 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE1;
3113 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE2 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE2;
3114 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE3 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE3;
3115 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE4 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE4;
3116 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE5 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE5;
3117 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE6 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE6;
3118 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE7 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE7;
3119 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE8 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE8;
3120 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE9 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE9;
3121 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE10 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE10;
3122 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE11 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE11;
3123 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE12 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE12;
3124 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE13 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE13;
3125 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE14 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE14;
3126 l_temp_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE15 := l_srlv_tbl(j).STREAM_INTERFACE_ATTRIBUTE15;
3127 END IF;
3128 END LOOP;
3129 x_srlv_tbl := l_temp_srlv_tbl;
3130 END reorganise_payment_tbl;
3131
3132 ------------------------------------------------------------------------------------------
3133 -- PROCEDURE
3134 ------------------------------------------------------------------------------------------
3135 PROCEDURE PROCESS_VIRP_STRM_RESLTS(p_api_version IN NUMBER
3136 ,p_init_msg_list IN VARCHAR2
3137 ,p_transaction_number IN NUMBER
3138 ,x_return_status OUT NOCOPY VARCHAR2
3139 ,x_msg_count OUT NOCOPY NUMBER
3140 ,x_msg_data OUT NOCOPY VARCHAR2)
3141 IS
3142 l_return_status VARCHAR(1) := G_RET_STS_SUCCESS;
3143 l_api_name CONSTANT VARCHAR2(40) := 'PROCESS_VIRP_STRM_RESLTS';
3144 l_api_version CONSTANT NUMBER := 1.0;
3145 l_srlv_tbl srlv_tbl_type;
3146 --Added by kthiruva for Bug 5161075
3147 l_tmp_srlv_tbl srlv_tbl_type;
3148 l_yields_tbl yields_tbl_type;
3149 l_sir_id NUMBER;
3150 l_chr_id NUMBER;
3151 l_transaction_status varchar2(30);
3152 i NUMBER;
3153
3154 --Modified by kthiruva on 13-Apr-2006 for Bug 5090060
3155 -- Where condition added
3156 CURSOR srlv_data_csr (p_sir_id NUMBER,
3157 p_trx_number NUMBER) IS
3158 SELECT
3159 SRLB.ID,
3160 SRLB.LEVEL_INDEX_NUMBER,
3161 SRLB.NUMBER_OF_PERIODS,
3162 SRLB.SIR_ID,
3163 SRLB.INDEX_NUMBER,
3164 SRLB.LEVEL_TYPE,
3165 SRLB.AMOUNT,
3166 SRLB.ADVANCE_OR_ARREARS,
3167 SRLB.PERIOD,
3168 SRLB.LOCK_LEVEL_STEP,
3169 SRLB.DAYS_IN_PERIOD,
3170 SRLB.FIRST_PAYMENT_DATE,
3171 SRLB.STREAM_INTERFACE_ATTRIBUTE1,
3172 SRLB.STREAM_INTERFACE_ATTRIBUTE2,
3173 SRLB.STREAM_INTERFACE_ATTRIBUTE3,
3174 SRLB.STREAM_INTERFACE_ATTRIBUTE4,
3175 SRLB.STREAM_INTERFACE_ATTRIBUTE5,
3176 SRLB.STREAM_INTERFACE_ATTRIBUTE6,
3177 SRLB.STREAM_INTERFACE_ATTRIBUTE7,
3178 SRLB.STREAM_INTERFACE_ATTRIBUTE8,
3179 SRLB.STREAM_INTERFACE_ATTRIBUTE9,
3180 SRLB.STREAM_INTERFACE_ATTRIBUTE10,
3181 SRLB.STREAM_INTERFACE_ATTRIBUTE11,
3182 SRLB.STREAM_INTERFACE_ATTRIBUTE12,
3183 SRLB.STREAM_INTERFACE_ATTRIBUTE13,
3184 SRLB.STREAM_INTERFACE_ATTRIBUTE14,
3185 SRLB.STREAM_INTERFACE_ATTRIBUTE15,
3186 SIFB.KHR_ID,
3187 SILB.KLE_ID
3188 FROM OKL_SIF_RET_LEVELS SRLB, OKL_SIF_RETS SIRB,
3189 OKL_STREAM_INTERFACES SIFB,OKL_SIF_LINES SILB
3190 WHERE SRLB.SIR_ID = p_sir_id
3191 AND SIFB.TRANSACTION_NUMBER = p_trx_number
3192 AND SIRB.TRANSACTION_NUMBER = SIFB.TRANSACTION_NUMBER
3193 AND SILB.SIF_ID = SIFB.ID
3194 AND SRLB.SIR_ID = SIRB.ID
3195 AND SRLB.INDEX_NUMBER = SILB.INDEX_NUMBER
3196 AND SRLB.LEVEL_TYPE IN ('Payment','Principal')
3197 AND SRLB.LOCK_LEVEL_STEP = 'N';
3198
3199 CURSOR sirv_data_csr (p_trx_number NUMBER) IS
3200 SELECT
3201 ID
3202 FROM okl_sif_rets
3203 WHERE okl_sif_rets.transaction_number = p_trx_number
3204 AND OKL_SIF_RETS.INDEX_NUMBER = 0;
3205 CURSOR sifv_data_csr (p_trx_number IN NUMBER) IS
3206 SELECT
3207 SIS_CODE,
3208 KHR_ID
3209 FROM OKL_STREAM_INTERFACES
3210 WHERE TRANSACTION_NUMBER = p_trx_number;
3211
3212 --Added by kthiruva for Bug 5161075
3213 --This cursor checks if the adjusting stub returned by the Inbound XML
3214 --was created for a stub or for a period ic payment
3215 CURSOR check_stub_csr(p_chr_id NUMBER,
3216 p_cle_id NUMBER,
3217 p_date DATE,
3218 p_slh_id NUMBER)
3219 IS
3220 SELECT TO_NUMBER(crl.rule_information7) stub_days
3221 FROM okc_rule_groups_b crg,
3222 okc_rules_b crl
3223 WHERE crl.rgp_id = crg.id
3224 AND crl.object2_id1 = p_slh_id
3225 AND crg.rgd_code = 'LALEVL'
3226 AND crl.rule_information_category = 'LASLL'
3227 AND crg.dnz_chr_id = p_chr_id
3228 AND crg.cle_id = p_cle_id
3229 AND FND_DATE.canonical_to_date(crl.rule_information2)+TO_NUMBER(crl.rule_information7) = p_date;
3230
3231 --This cursor obtains the SLH id of the payment corresponding to a kle_id
3232 CURSOR get_slh_csr(p_chr_id NUMBER,
3233 p_cle_id NUMBER)
3234 IS
3235 SELECT crl.id slh_id
3236 FROM okc_rule_groups_b crg,
3237 okc_rules_b crl
3238 WHERE crl.rgp_id = crg.id
3239 AND crg.rgd_code = 'LALEVL'
3240 AND crl.rule_information_category = 'LASLH'
3241 AND crg.dnz_chr_id = p_chr_id
3242 AND crg.cle_id = p_cle_id
3243 ORDER BY crl.rule_information1;
3244
3245 CURSOR get_freq_csr(p_chr_id NUMBER,
3246 p_cle_id NUMBER,
3247 p_slh_id NUMBER)
3248 IS
3249 SELECT crl.object1_id1 frequency
3250 FROM okc_rule_groups_b crg,
3251 okc_rules_b crl
3252 WHERE crl.rgp_id = crg.id
3253 AND crl.object2_id1 = p_slh_id
3254 AND crg.rgd_code = 'LALEVL'
3255 AND crl.rule_information_category = 'LASLL'
3256 AND crg.dnz_chr_id = p_chr_id
3257 AND crg.cle_id = p_cle_id;
3258
3259 l_slh_id NUMBER;
3260 l_frequency VARCHAR2(1);
3261 l_end_accrual_date DATE;
3262 l_stub_days NUMBER;
3263 --kthiruva - End of Changes for Bug 5161075
3264
3265
3266 --Added by kthiruva on 11-Nov-2005 for the VR build
3267 --Bug 4726209 - Start of Changes
3268 l_payment_start_date DATE;
3269 l_number_of_months NUMBER := 0;
3270 --Bug 4726209 - End of Changes
3271 --Added by kthiruva on 13-Apr-2006 for Bug 5090060
3272 level_indx_count NUMBER := 1;
3273 asset_indx_number NUMBER ;
3274
3275 BEGIN
3276 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
3277 p_pkg_name => 'OKL_PROCESS_STREAMS_PVT',
3278 p_init_msg_list => p_init_msg_list,
3279 l_api_version => l_api_version,
3280 p_api_version => p_api_version,
3281 p_api_type => '_PVT',
3282 x_return_status => l_return_status);
3283 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3284 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3285 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3286 RAISE G_EXCEPTION_ERROR;
3287 END IF;
3288 --Before Processing Stream Results check for any exceptions in the Inbound Interface Tables
3289 GENERATE_ERROR_LOGFILE(p_transaction_number => p_transaction_number
3290 ,x_return_status => l_return_status);
3291 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3292 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3293 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3294 RAISE G_EXCEPTION_ERROR;
3295 END IF;
3296 -- update statuses in the inbound and outbound interface tables
3297 UPDATE_STATUSES(p_transaction_number => p_transaction_number
3298 ,x_return_status => l_return_status);
3299 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3300 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3301 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3302 RAISE G_EXCEPTION_ERROR;
3303 END IF;
3304 -- fetch records from
3305 FOR sirv_data in sirv_data_csr(p_transaction_number)
3306 LOOP
3307 l_sir_id := sirv_data.id;
3308 END LOOP;
3309 i := 1;
3310 FOR srlv_data in srlv_data_csr(l_sir_id,p_transaction_number)
3311 LOOP
3312 l_srlv_tbl(i) := null;
3313 l_srlv_tbl(i).ID := srlv_data.ID;
3314 l_srlv_tbl(i).LEVEL_INDEX_NUMBER := srlv_data.LEVEL_INDEX_NUMBER;
3315 l_srlv_tbl(i).NUMBER_OF_PERIODS := srlv_data.NUMBER_OF_PERIODS;
3316 l_srlv_tbl(i).SIR_ID := srlv_data.SIR_ID;
3317 l_srlv_tbl(i).INDEX_NUMBER := srlv_data.INDEX_NUMBER;
3318 l_srlv_tbl(i).LEVEL_TYPE := srlv_data.LEVEL_TYPE;
3319 l_srlv_tbl(i).AMOUNT := srlv_data.AMOUNT;
3320 l_srlv_tbl(i).ADVANCE_OR_ARREARS := srlv_data.ADVANCE_OR_ARREARS;
3321 l_srlv_tbl(i).PERIOD := srlv_data.PERIOD;
3322 l_srlv_tbl(i).LOCK_LEVEL_STEP := srlv_data.LOCK_LEVEL_STEP;
3323 l_srlv_tbl(i).DAYS_IN_PERIOD := srlv_data.DAYS_IN_PERIOD;
3324 --We are interested in querying up and passing back all unlocked payment
3325 --If a PPD has been made on the contract, it would be locked, but the ajdusting stub created
3326 --for the PPD would be unlocked.
3327 IF (srlv_data.PERIOD = 'Stub') THEN
3328 IF (srlv_data.ADVANCE_OR_ARREARS = 'N') THEN
3329 l_end_accrual_date := srlv_data.first_payment_date + srlv_data.days_in_period;
3330 ELSE
3331 l_end_accrual_date := srlv_data.first_payment_date ;
3332 END IF;
3333 --Obtaining the payment header information
3334 OPEN get_slh_csr(srlv_data.khr_id,
3335 srlv_data.kle_id);
3336 FETCH get_slh_csr INTO l_slh_id;
3337 IF get_slh_csr%FOUND THEN
3338 --A stub encountered could be the adjusting stub created for
3339 --either a periodic payment or for a stub.
3340 OPEN check_Stub_csr(srlv_data.khr_id,
3341 srlv_data.kle_id,
3342 l_end_accrual_date,
3343 l_slh_id);
3344 FETCH check_stub_csr INTO l_stub_days;
3345 --If the cursor returns a value then the adjusting stub is due to a stub in
3346 --the original payment plan.
3347 --Else it is a stub created for a periodic payment plan.
3348 IF check_stub_csr%FOUND THEN
3349 l_srlv_tbl(i).DAYS_IN_PERIOD := l_stub_days;
3350 ELSE
3351 l_srlv_tbl(i).NUMBER_OF_PERIODS := srlv_data.NUMBER_OF_PERIODS;
3352 OPEN get_freq_csr(srlv_data.khr_id,
3353 srlv_data.kle_id,
3354 l_slh_id);
3355 FETCH get_freq_csr INTO l_frequency;
3356 IF get_freq_csr%FOUND THEN
3357 IF l_frequency = 'M' THEN
3358 l_srlv_tbl(i).PERIOD := 'Monthly';
3359 ELSIF l_frequency = 'Q' THEN
3360 l_srlv_tbl(i).PERIOD := 'Quarterly';
3361 ELSIF l_frequency = 'S' THEN
3362 l_srlv_tbl(i).PERIOD := 'Semiannual';
3363 ELSIF l_frequency = 'A' THEN
3364 l_srlv_tbl(i).PERIOD := 'Annual';
3365 END IF;
3366 END IF;
3367 CLOSE get_freq_csr;
3368 END IF;
3369 CLOSE check_Stub_csr;
3370 END IF;
3371 CLOSE get_slh_csr;
3372 END IF;
3373
3374 --Modified by kthiruva on 11-Nov-2005 for the VR build
3375 --When the payment is in ARREARS, SuperTrump returns the due date
3376 --The requirement is however to pass the contract start date
3377 --Bug 4726209 - Start of Changes
3378 IF l_srlv_tbl(i).ADVANCE_OR_ARREARS = 'N' THEN
3379 l_srlv_tbl(i).FIRST_PAYMENT_DATE := srlv_data.FIRST_PAYMENT_DATE;
3380 ELSIF l_srlv_tbl(i).ADVANCE_OR_ARREARS = 'Y' THEN
3381
3382 IF l_srlv_tbl(i).PERIOD = 'Stub' THEN
3383 l_srlv_tbl(i).FIRST_PAYMENT_DATE := srlv_data.FIRST_PAYMENT_DATE - l_srlv_tbl(i).DAYS_IN_PERIOD;
3384 ELSE
3385 IF l_srlv_tbl(i).PERIOD = 'Annual' THEN
3386 l_number_of_months := G_MINUS_TWELVE;
3387 ELSIF l_srlv_tbl(i).PERIOD = 'Semiannual' THEN
3388 l_number_of_months := G_MINUS_SIX;
3389 ELSIF l_srlv_tbl(i).PERIOD = 'Quarterly' THEN
3390 l_number_of_months := G_MINUS_THREE;
3391 ELSIF l_srlv_tbl(i).PERIOD = 'Monthly' THEN
3392 l_number_of_months := G_MINUS_ONE;
3393 END IF;
3394
3395 Okl_Stream_Generator_Pvt.add_months_new(p_start_Date => srlv_data.FIRST_PAYMENT_DATE,
3396 p_months_after => l_number_of_months,
3397 x_date => l_payment_start_date,
3398 x_return_Status => l_return_status);
3399
3400 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3401 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3402 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3403 RAISE G_EXCEPTION_ERROR;
3404 END IF;
3405 l_srlv_tbl(i).FIRST_PAYMENT_DATE := l_payment_start_date;
3406 END IF;
3407 END IF;
3408 --Bug 4726209 - End of Changes
3409
3410 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE1 := srlv_data.STREAM_INTERFACE_ATTRIBUTE1;
3411 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE2 := srlv_data.STREAM_INTERFACE_ATTRIBUTE2;
3412 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE3 := srlv_data.STREAM_INTERFACE_ATTRIBUTE3;
3413 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE4 := srlv_data.STREAM_INTERFACE_ATTRIBUTE4;
3414 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE5 := srlv_data.STREAM_INTERFACE_ATTRIBUTE5;
3415 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE6 := srlv_data.STREAM_INTERFACE_ATTRIBUTE6;
3416 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE7 := srlv_data.STREAM_INTERFACE_ATTRIBUTE7;
3417 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE8 := srlv_data.STREAM_INTERFACE_ATTRIBUTE8;
3418 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE9 := srlv_data.STREAM_INTERFACE_ATTRIBUTE9;
3419 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE10 := srlv_data.STREAM_INTERFACE_ATTRIBUTE10;
3420 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE11 := srlv_data.STREAM_INTERFACE_ATTRIBUTE11;
3421 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE12 := srlv_data.STREAM_INTERFACE_ATTRIBUTE12;
3422 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE13 := srlv_data.STREAM_INTERFACE_ATTRIBUTE13;
3423 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE14 := srlv_data.STREAM_INTERFACE_ATTRIBUTE14;
3424 l_srlv_tbl(i).STREAM_INTERFACE_ATTRIBUTE15 := srlv_data.STREAM_INTERFACE_ATTRIBUTE15;
3425 i := i + 1;
3426 END LOOP;
3427
3428 asset_indx_number := l_srlv_tbl(l_srlv_tbl.FIRST).index_number;
3429 FOR i in l_srlv_tbl.FIRST..l_srlv_tbl.LAST
3430 LOOP
3431 IF (asset_indx_number = l_srlv_tbl(i).INDEX_NUMBER) THEN
3432 l_srlv_tbl(i).LEVEL_INDEX_NUMBER := level_indx_count;
3433 level_indx_count := level_indx_count + 1;
3434 ELSE
3435 asset_indx_number := l_srlv_tbl(i).INDEX_NUMBER;
3436 level_indx_count := 1;
3437 l_srlv_tbl(i).LEVEL_INDEX_NUMBER := level_indx_count;
3438 level_indx_count := level_indx_count + 1;
3439 END IF;
3440 END LOOP;
3441
3442 i := 1;
3443 FOR sifv_data in sifv_data_csr(p_transaction_number)
3444 LOOP
3445 l_chr_id := sifv_data.khr_id;
3446 l_transaction_status := sifv_data.SIS_CODE;
3447 END LOOP;
3448
3449 --Reorganising the payment table
3450 reorganise_payment_tbl(p_srlv_tbl => l_srlv_tbl,
3451 x_srlv_tbl => l_tmp_srlv_tbl);
3452
3453
3454 -- call the restructure api for processing results
3455 -- Start of wraper code generated automatically by Debug code generator for OKL_VARIABLE_INTEREST_PUB.var_int_rent_level
3456
3457 IF(L_DEBUG_ENABLED='Y') THEN
3458 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
3459 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
3460 END IF;
3461 IF(IS_DEBUG_PROCEDURE_ON) THEN
3462 BEGIN
3463 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPSRB.pls call OKL_VARIABLE_INTEREST_PUB.var_int_rent_level ');
3464 END;
3465 END IF;
3466 OKL_VARIABLE_INTEREST_PUB.var_int_rent_level(p_api_version => p_api_version
3467 ,p_init_msg_list => p_init_msg_list
3468 ,x_return_status => l_return_status
3469 ,x_msg_count => x_msg_count
3470 ,x_msg_data => x_msg_data
3471 ,p_chr_id => l_chr_id
3472 ,p_trx_id => p_transaction_number
3473 ,p_trx_status => l_transaction_status
3474 ,p_rent_tbl => l_tmp_srlv_tbl );
3475 IF(IS_DEBUG_PROCEDURE_ON) THEN
3476 BEGIN
3477 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPSRB.pls call OKL_VARIABLE_INTEREST_PUB.var_int_rent_level ');
3478 END;
3479 END IF;
3480 -- End of wraper code generated automatically by Debug code generator for OKL_VARIABLE_INTEREST_PUB.var_int_rent_level
3481 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
3482 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3483 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
3484 RAISE G_EXCEPTION_ERROR;
3485 END IF;
3486 x_return_status := l_return_status;
3487 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3488 x_msg_data => x_msg_data);
3489 EXCEPTION
3490 WHEN G_EXCEPTION_ERROR THEN
3491 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3492 p_pkg_name => G_PKG_NAME,
3493 p_exc_name => G_EXC_NAME_ERROR,
3494 x_msg_count => x_msg_count,
3495 x_msg_data => x_msg_data,
3496 p_api_type => '_PVT');
3497 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
3498 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3499 p_pkg_name => G_PKG_NAME,
3500 p_exc_name => G_EXC_NAME_UNEXP_ERROR,
3501 x_msg_count => x_msg_count,
3502 x_msg_data => x_msg_data,
3503 p_api_type => '_PVT');
3504 WHEN OTHERS THEN
3505 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
3506 p_pkg_name => G_PKG_NAME,
3507 p_exc_name => G_EXC_NAME_OTHERS,
3508 x_msg_count => x_msg_count,
3509 x_msg_data => x_msg_data,
3510 p_api_type => '_PVT');
3511 END PROCESS_VIRP_STRM_RESLTS;
3512 PROCEDURE ENQUEUE_MESSAGE( p_transaction_type IN varchar2,
3513 p_transaction_subtype IN varchar2,
3514 p_doc_number IN varchar2,
3515 p_prc_eng_url IN VARCHAR2,
3516 x_return_status OUT NOCOPY varchar2)
3517 IS
3518 i_tmp clob;
3519 v_message system.ecxmsg;
3520 v_enqueueoptions dbms_aq.enqueue_options_t;
3521 v_messageproperties dbms_aq.message_properties_t;
3522 v_msgid raw(16);
3523 c_nummessages CONSTANT INTEGER :=1;
3524 i_amount number;
3525 i_message raw(32767);
3526 i_buffer varchar2(32767);
3527 i_chunksize pls_integer := 32767;
3528 i_offset pls_integer;
3529 l_party_id varchar2(10) := null;
3530 l_party_type varchar2(10) := '';
3531 l_party_site_id varchar2(100);
3532 l_prc_eng_url varchar2(100);
3533 cursor xml_data_csr( p_trx_number NUMBER) IS
3534 SELECT
3535 IN_XML
3536 FROM
3537 OKL_STREAM_TRX_DATA
3538 WHERE
3539 transaction_number = p_trx_number;
3540 BEGIN
3541 savepoint enq_message;
3542 x_return_status := G_RET_STS_ERROR;
3543 FOR xml_data in xml_data_csr(p_doc_number)
3544 LOOP
3545 i_tmp := xml_data.in_xml;
3546 END LOOP;
3547 IF (p_prc_eng_url is null) THEN
3548 l_prc_eng_url := G_PROTOCOL_ADDRESS;
3549 ELSE
3550 l_prc_eng_url := p_prc_eng_url;
3551 END IF;
3552 -- select xmlfile into i_tmp from clobtest where ID = p_doc_number;
3553 l_party_site_id := FND_PROFILE.VALUE('OKL_ST_PRCENG_NAME');
3554 v_message := system.ecxmsg (message_type => G_MSG_TYPE,
3555 message_standard => G_MSG_STD,
3556
3557 transaction_type => p_transaction_type,
3558 transaction_subtype => p_transaction_subtype,
3559 document_number => p_doc_number,
3560 partyid => l_party_id,
3561 party_site_id => l_party_site_id,
3562 party_type => l_party_type,
3563 protocol_type => G_PROTOCOL_TYPE,
3564 protocol_address => l_prc_eng_url,
3565 username => NULL,
3566 password => NULL,
3567 payload => i_tmp,
3568 attribute1 => NULL,
3569 attribute2 => NULL,
3570 attribute3 => NULL,
3571 attribute4 => NULL,
3572 attribute5 => NULL);
3573 for v_counter in 1..c_nummessages
3574 loop
3575 -- Enqueue
3576 dbms_aq.enqueue
3577 (
3578 queue_name=> G_INBOUND_QUEUE,
3579 enqueue_options=>v_enqueueoptions,
3580 message_properties=>v_messageproperties,
3581 payload=>v_message,
3582 msgid=>v_msgid
3583 );
3584 end loop;
3585 -- start listener for inbound queue
3586 wf_event.listen(G_INBOUND_QUEUE);
3587 -- start listener on transaction queue
3588 wf_event.listen(G_TRANSACTION_QUEUE);
3589 --delete from OKL_STREAM_TRX_DATA where transaction_number = p_doc_number;
3590 x_return_status := G_RET_STS_SUCCESS;
3591 commit;
3592 exception
3593 when others then
3594 rollback to enq_message;
3595 x_return_status := G_RET_STS_ERROR;
3596 END ENQUEUE_MESSAGE;
3597 END OKL_PROCESS_STREAMS_PVT;