[Home] [Help]
PACKAGE BODY: APPS.OKL_CREATE_STREAMS_PVT
Source
1 PACKAGE BODY OKL_CREATE_STREAMS_PVT AS
2 /* $Header: OKLRCSMB.pls 120.38.12020000.2 2012/08/07 11:32:15 bkatraga ship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7 -- Start of wraper code generated automatically by Debug code generator
8 L_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_create_streams_pvt'; -- '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
14 ---------------------------------------------------------------------------
15 -- FUNCTION initialize
16 ---------------------------------------------------------------------------
17 PROCEDURE initialize
18 IS
19 BEGIN
20 g_asset_ids.DELETE;
21 g_periodic_expenses_indexes.DELETE;
22 g_periodic_incomes_indexes.DELETE;
23 g_rents_indexes.DELETE;
24 g_sfe_ids.DELETE;
25 g_sil_ids.DELETE;
26 g_siy_names.DELETE;
27
28
29
30
31 END initialize;
32
33 ---------------------------------------------------------------------
34 -- PROCEDURE adjust_get_sil_id for adjusting indices
35 -- for g_sil_idS.
36 -- Private procedure called from get_sil_id.
37 -- Created by sgorantl
38 ----------------------------------------------------------------------
39
40 PROCEDURE adjust_get_sil_id(
41 p_array_table IN OUT NOCOPY sil_id_tbl_type
42 ) IS
43 l_tbl_count NUMBER:=0;
44 l_nxt_row NUMBER:=0;
45 BEGIN
46
47 l_tbl_count := p_array_table.COUNT;
48 For i in 1..l_tbl_count
49 LOOP
50 IF p_array_table.EXISTS(i) THEN
51
52 NULL;
53 ELSE
54 l_nxt_row := p_array_table.NEXT(i);
55 p_array_table(i) := p_array_table(l_nxt_row);
56
57 p_array_table.DELETE(l_nxt_row);
58 END IF;
59 END LOOP;
60 END;
61
62 ---------------------------------------------------------------------------
63 -- FUNCTION get_sil_id
64 -- Gets the SIL_ID for a corresponding Asset_ID passed
65 ---------------------------------------------------------------------------
66 PROCEDURE get_sil_id(p_asset_id IN NUMBER
67 ,x_sil_id OUT NOCOPY NUMBER)
68 IS
69 j NUMBER;
70 BEGIN
71 -- adjust indices for g_sil_idS.
72 adjust_get_sil_id(g_sil_idS);
73 x_sil_id := 0;
74
75 IF g_sil_ids IS NOT NULL THEN
76
77 FOR j IN 1..g_sil_ids.count
78 LOOP
79 IF p_asset_id = g_sil_ids(j).kle_asset_id
80 THEN
81 x_sil_id := g_sil_ids(j).sil_id;
82 END IF;
83 EXIT WHEN(x_sil_id <> 0);
84 END LOOP;
85 END IF;
86 END get_sil_id;
87
88 ---------------------------------------------------------------------------
89 -- FUNCTION get_sfe_id
90 -- Gets the SFE_ID for a corresponding Fee_ID passed
91 ---------------------------------------------------------------------------
92 PROCEDURE get_sfe_id(p_fee_id IN NUMBER
93 ,p_stream_type_id IN NUMBER
94 ,x_sfe_id OUT NOCOPY NUMBER)
95 IS
96 j NUMBER;
97 l_xfe_id NUMBER;
98 l_sfe_count NUMBER;
99 BEGIN
100 x_sfe_id := 0;
101 l_xfe_id := 0;
102 l_sfe_count := g_sfe_ids.COUNT;
103 IF g_sfe_ids IS NOT NULL THEN
104 FOR j IN 1..g_sfe_ids.COUNT LOOP
105 IF p_fee_id = g_sfe_ids(j).kle_fee_id AND
106 g_sfe_ids(j).stream_type_id IS NULL THEN-- smahapat added for fee type solution
107 x_sfe_id := g_sfe_ids(j).sfe_id;
108 g_sfe_ids(j).stream_type_id := p_stream_type_id; -- smahapat added for fee type solution
109 ELSIF p_fee_id = g_sfe_ids(j).kle_fee_id AND
110 g_sfe_ids(j).stream_type_id IS NOT NULL THEN
111 l_xfe_id := g_sfe_ids(j).sfe_id;
112 END IF;
113 EXIT WHEN(x_sfe_id <> 0);
114 END LOOP;
115 -- special fix for amortized fee income
116 -- addition of a new line for sfe - sty combination
117
118 -- apart from sfe - sty combination for income fee payment
119 IF x_sfe_id = 0 AND l_xfe_id <> 0 THEN
120 g_sfe_ids(l_sfe_count+1).sfe_id := l_xfe_id;
121 x_sfe_id := l_xfe_id;
122 g_sfe_ids(l_sfe_count+1).stream_type_id := p_stream_type_id;
123 END IF;
124 END IF;
125 END get_sfe_id;
126 ---------------------------------------------------------------------------
127 -- FUNCTION get_line_index
128 -- Gets a Unique Index for a corresponding Asset_ID passed
129 ---------------------------------------------------------------------------
130 PROCEDURE get_line_index(p_line_id IN NUMBER,
131
132 x_line_index OUT NOCOPY NUMBER)
133
134 IS
135
136 BEGIN
137 -- Populate a table to hold asset_ids and the corresponding indices
138 -- so that same asset_id will use the same Asset_index
139 x_line_index := -1; -- Initialize
140 -- If exists, Get the Assigned Index and Use
141 FOR j IN 1..g_asset_ids.COUNT
142 LOOP
143 IF g_asset_ids(j).id = p_line_id
144 THEN
145 x_line_index := g_asset_ids(j).idx;
146 END IF;
147 EXIT WHEN(x_line_index >= 0);
148 END LOOP;
149 -- If Not Exists, Assign a New Index and Use
150 IF x_line_index < 0 THEN
151 x_line_index := g_asset_ids.COUNT;
152 g_asset_ids(x_line_index+1).id := p_line_id;
153 g_asset_ids(x_line_index+1).idx := x_line_index;
154 END IF;
155 END get_line_index;
156 ---------------------------------------------------------------------------
157
158 -- FUNCTION get_fee_index
159 -- Gets a Unique Index for a corresponding Fee_Index_Key passed
160 ---------------------------------------------------------------------------
161 PROCEDURE get_fee_index(p_fee_index_key IN VARCHAR2,
162 p_index_tbl IN OUT NOCOPY periodic_index_tbl_type,
163
164 x_fee_index OUT NOCOPY NUMBER)
165 IS
166 BEGIN
167 x_fee_index := -1;
168 -- Populate a table to hold fee_ids and the corresponding indices
169 -- so that same fee_id will use the same Fee_index
170 -- If exists, Get the Assigned Index and Use
171
172 FOR j IN 1..p_index_tbl.COUNT
173 LOOP
174
175 IF p_index_tbl(j).description = p_fee_index_key
176 THEN
177 x_fee_index := p_index_tbl(j).idx;
178 END IF;
179 EXIT WHEN(x_fee_index >= 0);
180 END LOOP;
181
182 -- If Not Exists, Assign a New Index and Use
183 IF x_fee_index < 0 THEN
184 x_fee_index := p_index_tbl.COUNT;
185 p_index_tbl(x_fee_index+1).description := p_fee_index_key;
186 p_index_tbl(x_fee_index+1).idx := x_fee_index;
187 END IF;
188 END get_fee_index;
189 ---------------------------------------------------------------------------
190 -- FUNCTION get_siy_index
191 -- Gets a Unique Index for a corresponding SIY_Index_Key passed
192 ---------------------------------------------------------------------------
193 PROCEDURE get_siy_index(p_siy_index_key IN VARCHAR2,
194 x_siy_index OUT NOCOPY NUMBER)
195 IS
196 BEGIN
197 -- Populate a table to hold asset_ids and the corresponding indices
198 -- so that same asset_id will use the same Asset_index
199 x_siy_index := -1; -- Initialize
200 -- If exists, Get the Assigned Index and Use
201 FOR j IN 1..g_siy_names.COUNT
202 LOOP
203 IF g_siy_names(j).description = p_siy_index_key
204 THEN
205 x_siy_index := g_siy_names(j).idx;
206 END IF;
207 EXIT WHEN(x_siy_index >= 0);
208 END LOOP;
209 -- If Not Exists, Assign a New Index and Use
210 IF x_siy_index < 0 THEN
211 x_siy_index := g_siy_names.COUNT;
212 g_siy_names(x_siy_index+1).description := p_siy_index_key;
213 g_siy_names(x_siy_index+1).idx := x_siy_index;
214 END IF;
215 END get_siy_index;
216
217
218 ---------------------------------------------------------------------
219 -- PROCEDURE adjust_index_one_off_fee for adjusting indices
220 -- for One off fee table.
221 -- Private procedure called from insert_finance_fee_for_lease AND
222 -- insert_finance_fee_for_loan.
223 -- Created by sgorantl
224 ----------------------------------------------------------------------
225
226 PROCEDURE adjust_index_one_off_fee(
227 p_array_table IN OUT NOCOPY csm_one_off_fee_tbl_type
228 ) IS
229 l_tbl_count NUMBER:=0;
230 l_nxt_row NUMBER:=0;
231 BEGIN
232
233 l_tbl_count := p_array_table.COUNT;
234 For i in 1..l_tbl_count
235
236 LOOP
237 IF p_array_table.EXISTS(i) THEN
238
239 NULL;
240 ELSE
241 l_nxt_row := p_array_table.NEXT(i);
242 p_array_table(i) := p_array_table(l_nxt_row);
243 p_array_table.DELETE(l_nxt_row);
244 END IF;
245 END LOOP;
246 END;
247
248 ---------------------------------------------------------------------
249 -- PROCEDURE adjust_index_one_off_fee for adjusting indices
250 -- for perodic expences table.
251 -- Private procedure called from insert_finance_fee_for_lease AND
252 -- insert_finance_fee_for_loan.
253 -- Created by sgorantl
254 ----------------------------------------------------------------------
255
256 PROCEDURE adjust_index_periodic_expense(
257 p_array_table IN OUT NOCOPY csm_periodic_expenses_tbl_type
258 ) IS
259 l_tbl_count number:=0;
260 l_nxt_row number:=0;
261
262 BEGIN
263 l_tbl_count := p_array_table.COUNT;
264 For i IN 1..l_tbl_count
265 LOOP
266 IF p_array_table.EXISTS(i) THEN
267 NULL;
268
269 ELSE
270 l_nxt_row := p_array_table.NEXT(i);
271 p_array_table(i) := p_array_table(l_nxt_row);
272 p_array_table.DELETE(l_nxt_row);
273 END IF;
274 END LOOP;
275 END;
276
277 ---------------------------------------------------------------------------
278 -- FUNCTION assign_header_details
279 -- Assigns the Header related data for LEASE TYPE Header Rec.
280
281 ---------------------------------------------------------------------------
282 FUNCTION assign_header_details(
283 p_lease_header_rec IN csm_lease_rec_type
284 ,x_return_status OUT NOCOPY VARCHAR2
285 ) RETURN sifv_rec_type
286 IS
287 CURSOR l_okl_prc_template_csr(p_pdt_id NUMBER, l_date DATE)
288 IS
289 SELECT template_name,template_path
290 FROM OKL_PRD_PRICE_TMPLS
291 WHERE pdt_id = p_pdt_id
292 AND l_date BETWEEN start_date AND NVL(end_date,l_date);
293
294 --smahapat 11/10/02 multi-gaap - addition
295 CURSOR l_sif_csr(l_contract_id NUMBER, l_trx_number NUMBER)
296 IS
297 SELECT id
298 FROM okl_stream_interfaces
299 WHERE khr_id = l_contract_id AND transaction_number = l_trx_number
300 AND sis_code IN (G_SIS_HDR_INSERTED, G_SIS_DATA_ENTERED, G_SIS_PROCESSING_REQUEST,G_SIS_RET_DATA_RECEIVED,G_SIS_PROCESS_COMPLETE);
301 --smahapat addition end
302
303 --sechawla 27-nov-09 9001267 : begin
304 CURSOR l_get_report_pdt_csr(p_khr_id NUMBER)
305 IS
306 SELECT pdt.reporting_pdt_id
307 FROM okl_k_headers khr,
308 okl_products_v pdt
309 WHERE khr.id = p_khr_id
310 AND khr.pdt_id = pdt.id(+);
311
312 l_report_pdt_id NUMBER;
313 --sechawla 27-nov-09 9001267 : end
314
315 lp_sifv_rec sifv_rec_type;
316 l_sys_date DATE := TRUNC(SYSDATE);
317 ---------------------------------------------------------------------------
318 -- FUNCTION get_deal_type
319 -- Gets the Corresponding Deal Type based on the ORP Code
320 ---------------------------------------------------------------------------
321 FUNCTION get_deal_type(p_orp_code IN VARCHAR2)
322 RETURN VARCHAR2
323 IS
324 l_deal_type VARCHAR2(4) := '';
325 BEGIN
326 IF p_orp_code IN ( G_ORP_CODE_BOOKING, G_ORP_CODE_UPGRADE ) THEN
327 l_deal_type := Okl_Invoke_Pricing_Engine_Pvt.G_XMLG_TRX_SUBTYPE_LS_BOOK_OUT;
328 ELSIF p_orp_code = G_ORP_CODE_RESTRUCTURE_AM
329
330 OR p_orp_code = G_ORP_CODE_RESTRUCTURE_CS
331 OR p_orp_code = G_ORP_CODE_RENEWAL
332 OR p_orp_code = G_ORP_CODE_VARIABLE_INTEREST
333 THEN
334 l_deal_type := Okl_Invoke_Pricing_Engine_Pvt.G_XMLG_TRX_SUBTYPE_LS_REST_OUT;
335 ELSIF p_orp_code = G_ORP_CODE_QUOTE THEN
336 l_deal_type := Okl_Invoke_Pricing_Engine_Pvt.G_XMLG_TRX_SUBTYPE_LS_QUOT_OUT;
337 END IF;
338 RETURN l_deal_type;
339 END get_deal_type;
340 BEGIN
341 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
342 lp_sifv_rec.khr_id := p_lease_header_rec.khr_id;
343 lp_sifv_rec.sif_mode := p_lease_header_rec.sif_mode;
344 lp_sifv_rec.country := p_lease_header_rec.country;
345
346 lp_sifv_rec.sis_code := G_SIS_HDR_INSERTED;
347 lp_sifv_rec.orp_code := p_lease_header_rec.orp_code;
348 lp_sifv_rec.date_payments_commencement := p_lease_header_rec.date_payments_commencement;
349
350 lp_sifv_rec.security_deposit_amount := p_lease_header_rec.security_deposit_amount;
351 lp_sifv_rec.date_sec_deposit_collected := p_lease_header_rec.date_sec_deposit_collected;
352 lp_sifv_rec.fasb_acct_treatment_method := p_lease_header_rec.fasb_acct_treatment_method;
353
354 lp_sifv_rec.adjust := p_lease_header_rec.adjust;
355 lp_sifv_rec.adjustment_method := p_lease_header_rec.adjustment_method;
356 lp_sifv_rec.date_processed := SYSDATE;
357 lp_sifv_rec.irs_tax_treatment_method := p_lease_header_rec.irs_tax_treatment_method;
358 lp_sifv_rec.date_delivery := p_lease_header_rec.date_delivery;
359 lp_sifv_rec.implicit_interest_rate := p_lease_header_rec.implicit_interest_rate;
360 lp_sifv_rec.rvi_rate := p_lease_header_rec.rvi_rate;
361 lp_sifv_rec.rvi_yn := p_lease_header_rec.rvi_yn;
362 lp_sifv_rec.date_delivery := p_lease_header_rec.date_payments_commencement;
363 lp_sifv_rec.term := p_lease_header_rec.term;
364 lp_sifv_rec.structure := p_lease_header_rec.structure;
365 /*
366 -- If "Booking"
367 IF p_lease_header_rec.orp_code = G_ORP_CODE_BOOKING THEN
368 lp_sifv_rec.deal_type := Okl_Invoke_Pricing_Engine_Pvt.G_XMLG_TRX_SUBTYPE_LS_BOOK_OUT;
369 -- Else If "ReStructure"
370 ELSIF p_lease_header_rec.orp_code = G_ORP_CODE_RESTRUCTURE_AM
371 OR p_lease_header_rec.orp_code = G_ORP_CODE_RESTRUCTURE_CS THEN
372 lp_sifv_rec.deal_type := Okl_Invoke_Pricing_Engine_Pvt.G_XMLG_TRX_SUBTYPE_LS_REST_OUT;
373 END IF;
374 */
375 lp_sifv_rec.deal_type := get_deal_type(p_lease_header_rec.orp_code);
376 lp_sifv_rec.pricing_template_name := 'DEFAULT';
377
378 -- sechawla 27-nov-09 9001267 : begin
379 IF p_lease_header_rec.purpose_code = 'REPORT' THEN
380 OPEN l_get_report_pdt_csr(p_lease_header_rec.khr_id);
381 FETCH l_get_report_pdt_csr INTO l_report_pdt_id;
382 CLOSE l_get_report_pdt_csr;
383
384 FOR l_okl_prc_template IN l_okl_prc_template_csr(l_report_pdt_id,l_sys_date)
385 LOOP
386 lp_sifv_rec.pricing_template_name := l_okl_prc_template.template_path || l_okl_prc_template.template_name;
387 END LOOP;
388 ELSE
389 FOR l_okl_prc_template IN l_okl_prc_template_csr(p_lease_header_rec.pdt_id,l_sys_date)
390 LOOP
391 lp_sifv_rec.pricing_template_name := l_okl_prc_template.template_path || l_okl_prc_template.template_name;
392 END LOOP;
393 END IF;
394 -- sechawla 27-nov-09 9001267 : end
395
396
397 -- mvasudev , 07/08/2002
398 -- Mandatory Checks moved here from TAPI(OKL_SIF_PVT) to get rid of
399 -- cyclic dependancy of OKL_SIF_PVT with OKL_INVOKE_PRICING_ENGINE_PVT
400
401 IF lp_sifv_rec.deal_type = OKL_INVOKE_PRICING_ENGINE_PVT.G_XMLG_TRX_SUBTYPE_LS_REST_OUT
402 AND (p_lease_header_rec.Jtot_Object1_Code IS NULL OR p_lease_header_rec.Jtot_Object1_Code = OKC_API.G_MISS_CHAR)
403 THEN
404 lp_sifv_rec.jtot_object1_code := p_lease_header_rec.jtot_object1_code;
405 /* smahapat for fee type soln
406 OKL_API.SET_MESSAGE(p_app_name => G_OKC_APP,
407 p_msg_name => G_REQUIRED_VALUE,
408 p_token1 => G_COL_NAME_TOKEN,
409 p_token1_value => 'JTOT_OBJECT1_CODE'
410 );
411 x_return_status := Okc_Api.G_RET_STS_ERROR;
412 RAISE G_EXCEPTION_ERROR;
413
414 */
415 ELSE
416 lp_sifv_rec.jtot_object1_code := p_lease_header_rec.jtot_object1_code;
417 END IF;
418 IF lp_sifv_rec.deal_type = OKL_INVOKE_PRICING_ENGINE_PVT.G_XMLG_TRX_SUBTYPE_LS_REST_OUT
419 AND (p_lease_header_rec.object1_id1 IS NULL OR p_lease_header_rec.object1_id1 = OKC_API.G_MISS_CHAR)
420 THEN
421 lp_sifv_rec.object1_id1 := p_lease_header_rec.object1_id1;
422 /* smahapat for fee type soln
423 OKL_API.SET_MESSAGE(p_app_name => G_OKC_APP,
424 p_msg_name => G_REQUIRED_VALUE,
425 p_token1 => G_COL_NAME_TOKEN,
426 p_token1_value => 'OBJECT1_ID1'
427 );
428 x_return_status := Okc_Api.G_RET_STS_ERROR;
429 RAISE G_EXCEPTION_ERROR;
430 */
431 ELSE
432 lp_sifv_rec.object1_id1 := p_lease_header_rec.object1_id1;
433 END IF;
434 -- mvasudev, Bug#2650599
435 --lp_sifv_rec.sif_id := p_lease_header_rec.sif_id;
436 lp_sifv_rec.purpose_code := p_lease_header_rec.purpose_code;
437 -- end, mvasudev, Bug#2650599
438 --smahapat 11/10/02 multi-gaap - addition
439 IF (p_lease_header_rec.purpose_code = G_PURPOSE_CODE_REPORT)
440 THEN
441 --the parent transaction number is being passed in sif_id by caller currently
442 FOR l_sif_data IN l_sif_csr(p_lease_header_rec.khr_id,p_lease_header_rec.sif_id)
443 LOOP
444
445 IF l_sif_csr%ROWCOUNT = 1 THEN
446 lp_sifv_rec.sif_id := l_sif_data.id;
447 ELSE
448 OKL_API.SET_MESSAGE(p_app_name => G_OKC_APP,
449 p_msg_name => G_REQUIRED_VALUE,
450 p_token1 => G_COL_NAME_TOKEN,
451 p_token1_value => 'SIF_ID');
452 x_return_status := Okc_Api.G_RET_STS_ERROR;
453 RAISE G_EXCEPTION_ERROR;
454 END IF;
455 END LOOP;
456 END IF;
457 --smahapat addition end
458 RETURN lp_sifv_rec;
459 EXCEPTION
460 WHEN G_EXCEPTION_ERROR THEN
461
462 x_return_status := G_RET_STS_ERROR;
463 IF l_okl_prc_template_csr%ISOPEN THEN
464 CLOSE l_okl_prc_template_csr;
465 END IF;
466
467 IF l_sif_csr%ISOPEN THEN
468 CLOSE l_sif_csr;
469 END IF;
470 RETURN NULL;
471
472 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
473 x_return_status := G_RET_STS_UNEXP_ERROR;
474 IF l_okl_prc_template_csr%ISOPEN THEN
475 CLOSE l_okl_prc_template_csr;
476 END IF;
477 RETURN NULL;
478 WHEN OTHERS THEN
479 -- store SQL error message on message stack
480 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
481 p_msg_name => G_UNEXPECTED_ERROR,
482 p_token1 => G_SQLCODE_TOKEN,
483 p_token1_value => SQLCODE,
484 p_token2 => G_SQLERRM_TOKEN,
485 p_token2_value => SQLERRM);
486 x_return_status := G_RET_STS_UNEXP_ERROR;
487 IF l_okl_prc_template_csr%ISOPEN THEN
488 CLOSE l_okl_prc_template_csr;
489 END IF;
490 RETURN NULL;
491 END assign_header_details;
492 ---------------------------------------------------------------------------
493 -- FUNCTION assign_header_details
494 ---------------------------------------------------------------------------
495 FUNCTION assign_header_details(
496
497 p_loan_header_rec IN csm_loan_rec_type
498 ,x_return_status OUT NOCOPY VARCHAR2
499 ) RETURN sifv_rec_type
500 IS
501 CURSOR l_okl_prc_template_csr(p_pdt_id NUMBER, l_date DATE)
502 IS
503 SELECT template_name,template_path
504 FROM OKL_PRD_PRICE_TMPLS
505
506 WHERE pdt_id = p_pdt_id
507 AND l_date BETWEEN start_date AND NVL(end_date,l_date);
508 --smahapat 11/10/02 multi-gaap - addition
509 CURSOR l_sif_csr(l_contract_id NUMBER, l_trx_number NUMBER)
510 IS
511 SELECT id
512 FROM okl_stream_interfaces
513 WHERE khr_id = l_contract_id AND transaction_number = l_trx_number
514 AND sis_code IN (G_SIS_HDR_INSERTED, G_SIS_DATA_ENTERED, G_SIS_PROCESSING_REQUEST,G_SIS_RET_DATA_RECEIVED,G_SIS_PROCESS_COMPLETE);
515 --smahapat addition end
516 lp_sifv_rec sifv_rec_type;
517 l_sys_date DATE := TRUNC(SYSDATE);
518 ---------------------------------------------------------------------------
519
520 -- FUNCTION get_deal_type
521 -- Gets the Corresponding Deal Type based on the ORP Code
522 ---------------------------------------------------------------------------
523 FUNCTION get_deal_type(p_orp_code IN VARCHAR2)
524 RETURN VARCHAR2
525 IS
526 l_deal_type VARCHAR2(4) := '';
527 BEGIN
528 IF p_orp_code IN ( G_ORP_CODE_BOOKING, G_ORP_CODE_UPGRADE ) THEN
529 l_deal_type := Okl_Invoke_Pricing_Engine_Pvt.G_XMLG_TRX_SUBTYPE_LN_BOOK_OUT;
530 ELSIF p_orp_code = G_ORP_CODE_RESTRUCTURE_AM
531 OR p_orp_code = G_ORP_CODE_RESTRUCTURE_CS
532 OR p_orp_code = G_ORP_CODE_RENEWAL
533 THEN
534 l_deal_type := Okl_Invoke_Pricing_Engine_Pvt.G_XMLG_TRX_SUBTYPE_LN_REST_OUT;
535 ELSIF p_orp_code = G_ORP_CODE_QUOTE OR p_orp_code = G_ORP_CODE_VARIABLE_INTEREST THEN
536 l_deal_type := Okl_Invoke_Pricing_Engine_Pvt.G_XMLG_TRX_SUBTYPE_LN_QUOT_OUT;
537 END IF;
538 RETURN l_deal_type;
539 END get_deal_type;
540 BEGIN
541 lp_sifv_rec.khr_id := p_loan_header_rec.khr_id;
542 lp_sifv_rec.sif_mode := G_MODE_LENDER;
543 lp_sifv_rec.sis_code := G_SIS_HDR_INSERTED;
544
545 lp_sifv_rec.country := p_loan_header_rec.country;
546 lp_sifv_rec.orp_code := p_loan_header_rec.orp_code;
547 lp_sifv_rec.date_payments_commencement := p_loan_header_rec.date_payments_commencement;
548 lp_sifv_rec.security_deposit_amount := p_loan_header_rec.security_deposit_amount;
549 lp_sifv_rec.date_sec_deposit_collected := p_loan_header_rec.date_sec_deposit_collected;
550 --smahapat 10/30/03 for loan quote
551 lp_sifv_rec.adjust := p_loan_header_rec.adjust;
552 lp_sifv_rec.adjustment_method := p_loan_header_rec.adjustment_method;
553 lp_sifv_rec.date_processed := SYSDATE;
554 -- Need to removed once FASB Attribute is made NULLABLE
555 lp_sifv_rec.fasb_acct_treatment_method := 'Unknown';
556 lp_sifv_rec.irs_tax_treatment_method := 'Unknown';
557 lp_sifv_rec.total_funding := p_loan_header_rec.total_lending;
558 lp_sifv_rec.date_delivery := p_loan_header_rec.date_start;
559 lp_sifv_rec.lending_rate := p_loan_header_rec.lending_rate;
560 lp_sifv_rec.pricing_template_name := 'DEFAULT';
561 FOR l_okl_prc_template IN l_okl_prc_template_csr(p_loan_header_rec.pdt_id,l_sys_date)
562
563 LOOP
564 lp_sifv_rec.pricing_template_name := l_okl_prc_template.template_path || l_okl_prc_template.template_name;
565 END LOOP;
566 /*
567 -- If "Booking"
568
569 IF p_loan_header_rec.orp_code = G_ORP_CODE_BOOKING THEN
570 lp_sifv_rec.deal_type := Okl_Invoke_Pricing_Engine_Pvt.G_XMLG_TRX_SUBTYPE_LN_BOOK_OUT;
571 -- Else If "ReStructure"
572 ELSIF p_loan_header_rec.orp_code = G_ORP_CODE_RESTRUCTURE_AM
573 OR p_loan_header_rec.orp_code = G_ORP_CODE_RESTRUCTURE_CS THEN
574
575 lp_sifv_rec.deal_type := Okl_Invoke_Pricing_Engine_Pvt.G_XMLG_TRX_SUBTYPE_LN_REST_OUT;
576 END IF;
577 */
578 lp_sifv_rec.deal_type := get_deal_type(p_loan_header_rec.orp_code);
579 -- mvasudev , 07/08/2002
580 -- Mandatory Checks moved here from TAPI(OKL_SIF_PVT) to get rid of
581 -- cyclic dependancy of OKL_SIF_PVT with OKL_INVOKE_PRICING_ENGINE_PVT
582 IF lp_sifv_rec.deal_type = OKL_INVOKE_PRICING_ENGINE_PVT.G_XMLG_TRX_SUBTYPE_LS_REST_OUT
583 AND (p_loan_header_rec.Jtot_Object1_Code IS NULL OR p_loan_header_rec.Jtot_Object1_Code = OKC_API.G_MISS_CHAR)
584 THEN
585 OKL_API.SET_MESSAGE(p_app_name => G_OKC_APP,
586 p_msg_name => G_REQUIRED_VALUE,
587 p_token1 => G_COL_NAME_TOKEN,
588 p_token1_value => 'JTOT_OBJECT1_CODE'
589 );
590 x_return_status := Okc_Api.G_RET_STS_ERROR;
591 RAISE G_EXCEPTION_ERROR;
592 ELSE
593 lp_sifv_rec.jtot_object1_code := p_loan_header_rec.jtot_object1_code;
594 END IF;
595 IF lp_sifv_rec.deal_type = OKL_INVOKE_PRICING_ENGINE_PVT.G_XMLG_TRX_SUBTYPE_LS_REST_OUT
596 AND (p_loan_header_rec.object1_id1 IS NULL OR p_loan_header_rec.object1_id1 = OKC_API.G_MISS_CHAR)
597 THEN
598 OKL_API.SET_MESSAGE(p_app_name => G_OKC_APP,
599 p_msg_name => G_REQUIRED_VALUE,
600
601 p_token1 => G_COL_NAME_TOKEN,
602 p_token1_value => 'OBJECT1_ID1'
603 );
604 x_return_status := Okc_Api.G_RET_STS_ERROR;
605 RAISE G_EXCEPTION_ERROR;
606 ELSE
607 lp_sifv_rec.object1_id1 := p_loan_header_rec.object1_id1;
608 END IF;
609 -- mvasudev, Bug#2650599
610
611 --lp_sifv_rec.sif_id := p_loan_header_rec.sif_id;
612 lp_sifv_rec.purpose_code := p_loan_header_rec.purpose_code;
613 -- end, mvasudev, Bug#2650599
614 --smahapat 11/10/02 multi-gaap - addition
615 IF (p_loan_header_rec.purpose_code = G_PURPOSE_CODE_REPORT)
616 THEN
617 --the parent transaction number is being passed in sif_id by caller currently
618 FOR l_sif_data IN l_sif_csr(p_loan_header_rec.khr_id,p_loan_header_rec.sif_id)
619 LOOP
620 IF l_sif_csr%ROWCOUNT = 1 THEN
621 lp_sifv_rec.sif_id := l_sif_data.id;
622 ELSE
623 OKL_API.SET_MESSAGE(p_app_name => G_OKC_APP,
624 p_msg_name => G_REQUIRED_VALUE,
625
626 p_token1 => G_COL_NAME_TOKEN,
627 p_token1_value => 'SIF_ID');
628 x_return_status := Okc_Api.G_RET_STS_ERROR;
629 RAISE G_EXCEPTION_ERROR;
630 END IF;
631 END LOOP;
632 END IF;
633 --smahapat addition end
634
635 RETURN lp_sifv_rec;
636 EXCEPTION
637 WHEN G_EXCEPTION_ERROR THEN
638 x_return_status := G_RET_STS_ERROR;
639 IF l_okl_prc_template_csr%ISOPEN THEN
640 CLOSE l_okl_prc_template_csr;
641 END IF;
642 RETURN NULL;
643 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
644
645 x_return_status := G_RET_STS_UNEXP_ERROR;
646 IF l_okl_prc_template_csr%ISOPEN THEN
647 CLOSE l_okl_prc_template_csr;
648 END IF;
649 RETURN NULL;
650 WHEN OTHERS THEN
651 -- store SQL error message on message stack
652 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
653 p_msg_name => G_UNEXPECTED_ERROR,
654 p_token1 => G_SQLCODE_TOKEN,
655 p_token1_value => SQLCODE,
656 p_token2 => G_SQLERRM_TOKEN,
657 p_token2_value => SQLERRM);
658 x_return_status := G_RET_STS_UNEXP_ERROR;
659 IF l_okl_prc_template_csr%ISOPEN THEN
660 CLOSE l_okl_prc_template_csr;
661 END IF;
662 RETURN NULL;
663
664 END assign_header_details;
665 ---------------------------------------------------------------------------
666 -- PROCEDURE insert_asset_lines
667 -- Assigns and Inserts Asset Line details for this Contract
668 ---------------------------------------------------------------------------
669 PROCEDURE insert_asset_lines(
670
671 p_api_version IN NUMBER
672 ,p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
673 ,p_sif_id IN NUMBER
674 ,p_csm_line_details_tbl IN csm_line_details_tbl_type
675 ,x_return_status OUT NOCOPY VARCHAR2
676 ,x_msg_count OUT NOCOPY NUMBER
677
678 ,x_msg_data OUT NOCOPY VARCHAR2
679 ) IS
680
681 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
682 cursor get_rebook_type is
683 select nvl(amort_inc_adj_rev_dt_yn, 'N')
684 from okl_sys_acct_opts;
685
686 l_PROSPECTIVE_REBOOK_YN VARCHAR2(1);
687
688 lp_silv_rec silv_rec_type;
689 lx_silv_rec silv_rec_type;
690 i NUMBER := 0;
691 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
692 l_index NUMBER := -1;
693 BEGIN
694 l_return_status := G_RET_STS_SUCCESS;
695
696 --sechawla 10-jul-09 PRB ESG enhancements : begin
697 OPEN get_rebook_type;
698 FETCH get_rebook_type INTO l_PROSPECTIVE_REBOOK_YN;
699 CLOSE get_rebook_type;
700 --sechawla 10-jul-09 PRB ESG enhancements : end
701
702 FOR i IN 1..p_csm_line_details_tbl.COUNT
703 LOOP
704 --lp_silv_rec.index_number := i;
705 l_index := i-1;
706 lp_silv_rec.index_number := l_index;
707 lp_silv_rec.sif_id := p_sif_id;
708 lp_silv_rec.kle_id := p_csm_line_details_tbl(i).kle_asset_id;
709
710 --sechawla 10-jul-09 PRB ESG enhancements : begin
711 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' then
712 lp_silv_rec.orig_contract_line_id := p_csm_line_details_tbl(i).orig_contract_line_id;
713 --END IF;
714 --sechawla 10-jul-09 PRB ESG enhancements : end
715
716 lp_silv_rec.sil_type := G_SIL_TYPE_LEASE;
717 lp_silv_rec.description := p_csm_line_details_tbl(i).description;
718 lp_silv_rec.asset_cost := p_csm_line_details_tbl(i).asset_cost;
719 lp_silv_rec.date_delivery := p_csm_line_details_tbl(i).date_delivery;
720 lp_silv_rec.date_funding := p_csm_line_details_tbl(i).date_funding;
721 lp_silv_rec.residual_amount := p_csm_line_details_tbl(i).residual_amount;
722 lp_silv_rec.residual_date := p_csm_line_details_tbl(i).residual_date;
723 lp_silv_rec.fed_depre_method := p_csm_line_details_tbl(i).fed_depre_method;
724 lp_silv_rec.fed_depre_basis_percent := p_csm_line_details_tbl(i).fed_depre_basis_percent;
725 lp_silv_rec.date_fed_depre := p_csm_line_details_tbl(i).date_fed_depre;
726 lp_silv_rec.fed_depre_term := p_csm_line_details_tbl(i).fed_depre_term;
727 lp_silv_rec.fed_depre_salvage := p_csm_line_details_tbl(i).fed_depre_salvage;
728 lp_silv_rec.fed_depre_adr_conve := p_csm_line_details_tbl(i).fed_depre_adr_conve;
729 lp_silv_rec.fed_depre_dmnshing_value_rate := p_csm_line_details_tbl(i).fed_depre_dmnshing_value_rate;
730 lp_silv_rec.state_depre_method := p_csm_line_details_tbl(i).state_depre_method;
731 lp_silv_rec.state_depre_basis_percent := p_csm_line_details_tbl(i).state_depre_basis_percent;
732 lp_silv_rec.date_state_depre := p_csm_line_details_tbl(i).date_state_depre;
733 lp_silv_rec.state_depre_term := p_csm_line_details_tbl(i).state_depre_term;
734 lp_silv_rec.state_depre_salvage := p_csm_line_details_tbl(i).state_depre_salvage;
735 lp_silv_rec.state_depre_adr_convent := p_csm_line_details_tbl(i).state_depre_adr_convent;
736 lp_silv_rec.state_depre_dmnshing_value_rt := p_csm_line_details_tbl(i).state_depre_dmnshing_value_rt;
737
738 lp_silv_rec.book_method := p_csm_line_details_tbl(i).book_method;
739 lp_silv_rec.book_basis_percent := p_csm_line_details_tbl(i).book_basis_percent;
740 lp_silv_rec.date_book := p_csm_line_details_tbl(i).date_book;
741 lp_silv_rec.book_term := p_csm_line_details_tbl(i).book_term;
742 lp_silv_rec.book_salvage := p_csm_line_details_tbl(i).book_salvage;
743 lp_silv_rec.book_adr_convention := p_csm_line_details_tbl(i).book_adr_convention;
744 lp_silv_rec.book_depre_dmnshing_value_rt := p_csm_line_details_tbl(i).book_depre_dmnshing_value_rt;
745 lp_silv_rec.residual_guarantee_method := p_csm_line_details_tbl(i).residual_guarantee_method;
746 lp_silv_rec.residual_guarantee_amount := p_csm_line_details_tbl(i).residual_guarantee_amount;
747 lp_silv_rec.purchase_option := p_csm_line_details_tbl(i).purchase_option;
748 lp_silv_rec.purchase_option_amount := p_csm_line_details_tbl(i).purchase_option_amount;
749 lp_silv_rec.residual_guarantee_type := p_csm_line_details_tbl(i).guarantee_type;
750 lp_silv_rec.down_payment_amount := p_csm_line_details_tbl(i).down_payment_amount;
751 lp_silv_rec.capitalize_down_payment_yn := p_csm_line_details_tbl(i).capitalize_down_payment_yn;
752 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Lines_Pub.insert_sif_lines
753 IF(L_DEBUG_ENABLED='Y') THEN
754 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
755
756 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
757 END IF;
758 IF(IS_DEBUG_PROCEDURE_ON) THEN
759 BEGIN
760 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Lines_Pub.insert_sif_lines ');
761 END;
762 END IF;
763 Okl_Sif_Lines_Pub.insert_sif_lines(
764 p_api_version => p_api_version
765 ,p_init_msg_list => p_init_msg_list
766 ,x_return_status => l_return_status
767
768 ,x_msg_count => x_msg_count
769 ,x_msg_data => x_msg_data
770 ,p_silv_rec => lp_silv_rec
771 ,x_silv_rec => lx_silv_rec);
772 IF(IS_DEBUG_PROCEDURE_ON) THEN
773 BEGIN
774 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Lines_Pub.insert_sif_lines ');
775 END;
776 END IF;
777 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Lines_Pub.insert_sif_lines
778 IF l_return_status = G_RET_STS_ERROR THEN
779 RAISE G_EXCEPTION_ERROR;
780 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
781 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
782 END IF;
783 g_sil_ids(i).kle_asset_id := p_csm_line_details_tbl(i).kle_asset_id;
784 g_sil_ids(i).sil_id := lx_silv_rec.id;
785 END LOOP;
786
787 x_return_status := l_return_status;
788 EXCEPTION
789 WHEN G_EXCEPTION_ERROR THEN
790 x_return_status := G_RET_STS_ERROR;
791 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
792 x_return_status := G_RET_STS_UNEXP_ERROR;
793 WHEN OTHERS THEN
794
795 -- store SQL error message on message stack
796 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
797 p_msg_name => G_UNEXPECTED_ERROR,
798 p_token1 => G_SQLCODE_TOKEN,
799 p_token1_value => SQLCODE,
800 p_token2 => G_SQLERRM_TOKEN,
801 p_token2_value => SQLERRM);
802
803 x_return_status := G_RET_STS_UNEXP_ERROR;
804 END insert_asset_lines;
805
806
807
808 ---------------------------------------------------------------------------
809 -- FUNCTION assign_one_off_fees
810 -- Assigns One Off Fee details for this Contract
811 -- Can Occur both at the Header and Asset but always unique
812 ---------------------------------------------------------------------------
813 FUNCTION assign_one_off_fees(
814 p_sif_id IN NUMBER,
815 p_csm_one_off_fee_tbl IN csm_one_off_fee_tbl_type,
816 x_return_status OUT NOCOPY VARCHAR2
817 ) RETURN sfev_tbl_type
818 IS
819 CURSOR l_okl_sil_pk_csr(p_kle_id IN OKL_SIF_LINES_V.KLE_ID%TYPE) IS
820 SELECT ID
821 FROM OKL_SIF_LINES
822 WHERE kle_id = p_kle_id;
823 l_sfev_one_off_tbl sfev_tbl_type;
824
825 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
826 cursor get_rebook_type is
827 select nvl(amort_inc_adj_rev_dt_yn, 'N')
828 from okl_sys_acct_opts;
829
830 --sechawla 24-Jul-09 PRB ESG Enhancements : get orig_contract_line_id for the subsidy line
831 cursor get_orig_subsidy_line_id(cp_subsidy_line_id in number) is
832 select orig_contract_line_id
833 from okl_k_lines
834 where id = cp_subsidy_line_id;
835
836 l_PROSPECTIVE_REBOOK_YN VARCHAR2(1);
837
838 i NUMBER := 0;
839 j NUMBER := 0;
840 fee_index NUMBER := 0;
841 sec_dep_index NUMBER := 0;
842 subsidy_index NUMBER := 0;
843 s_count NUMBER := 0;
844 BEGIN
845 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
846
847 --sechawla 10-jul-09 PRB ESG enhancements : begin
848 OPEN get_rebook_type;
849 FETCH get_rebook_type INTO l_PROSPECTIVE_REBOOK_YN;
850 CLOSE get_rebook_type;
851 --sechawla 10-jul-09 PRB ESG enhancements : end
852
853 FOR i IN 1..p_csm_one_off_fee_tbl.COUNT
854 LOOP
855 l_sfev_one_off_tbl(i).sif_id := p_sif_id;
856 l_sfev_one_off_tbl(i).kle_id := p_csm_one_off_fee_tbl(i).kle_fee_id;
857
858 --sechawla 15-jul-09 PRB ESG Enhancements : assign orig_contract_line_id
859 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' then
860 l_sfev_one_off_tbl(i).orig_contract_line_id := p_csm_one_off_fee_tbl(i).orig_contract_line_id;
861 --END IF;
862
863 -- smahapat for fee type solution - sec deposit
864 l_sfev_one_off_tbl(i).DESCRIPTION := p_csm_one_off_fee_tbl(i).description;
865 IF p_csm_one_off_fee_tbl(i).fee_type = 'SECDEPOSIT' THEN
866 l_sfev_one_off_tbl(i).sfe_type := G_SFE_TYPE_SECURITY_DEPOSIT;
867 l_sfev_one_off_tbl(i).date_start := p_csm_one_off_fee_tbl(i).date_start;
868 l_sfev_one_off_tbl(i).fee_index_number := sec_dep_index; --smahapat added for fee type soln
869
870 sec_dep_index := sec_dep_index + 1;
871 ELSIF p_csm_one_off_fee_tbl(i).other_type = G_SFE_TYPE_SUBSIDY THEN
872 --suresh gorantla
873 l_sfev_one_off_tbl(i).sfe_type := G_SFE_TYPE_SUBSIDY;
874 --l_sfev_one_off_tbl(i).DESCRIPTION := 'SUBSIDY INCOME ACCRUAL';
875 l_sfev_one_off_tbl(i).DESCRIPTION := p_csm_one_off_fee_tbl(i).description;
876 l_sfev_one_off_tbl(i).kle_id := p_csm_one_off_fee_tbl(i).other_type_id;
877
878 --sechawla 15-jul-09 PRB ESG Enhancements : assign orig_contract_line_id
879 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' then
880 --sechawla 24-jul-09 For subsidy line in okl_sif_fees, orig_contract_line_id
881 -- should be the orig_contract_line_id corresponding to subsidy line, not financial asset line
882 open get_orig_subsidy_line_id(p_csm_one_off_fee_tbl(i).other_type_id);
883 fetch get_orig_subsidy_line_id into l_sfev_one_off_tbl(i).orig_contract_line_id;
884 close get_orig_subsidy_line_id;
885
886 --l_sfev_one_off_tbl(i).orig_contract_line_id := p_csm_one_off_fee_tbl(i).orig_contract_line_id; --sechawla 24-jul-09
887 --END IF;
888 --l_sfev_one_off_tbl(i).kle_id := p_csm_one_off_fee_tbl(i).kle_asset_id;
889 l_sfev_one_off_tbl(i).date_start := p_csm_one_off_fee_tbl(i).date_start;
890 l_sfev_one_off_tbl(i).rate := p_csm_one_off_fee_tbl(i).rate;
891 l_sfev_one_off_tbl(i).fee_index_number := subsidy_index;
892 subsidy_index := subsidy_index + 1;
893 else
894 l_sfev_one_off_tbl(i).sfe_type := G_SFE_TYPE_ONE_OFF;
895 /* Populate FEE_INDEX
896 -- Same FeeID is not going to occur twice
897 -- across the Assets or across the Hierarchy(Header and Asset)
898 -- So TBL index is good enough
899
900 */
901 --l_sfev_one_off_tbl(i).fee_index_number := i-1;
902 l_sfev_one_off_tbl(i).fee_index_number := fee_index; -- smahapat added for fee type
903
904 fee_index := fee_index + 1; -- smahapat added for fee type
905 END IF;
906 ---l_sfev_one_off_tbl(i).description := p_csm_one_off_fee_tbl(i).description;
907 l_sfev_one_off_tbl(i).date_paid := p_csm_one_off_fee_tbl(i).date_paid;
908 l_sfev_one_off_tbl(i).amount := p_csm_one_off_fee_tbl(i).amount;
909 l_sfev_one_off_tbl(i).idc_accounting_flag := p_csm_one_off_fee_tbl(i).idc_accounting_flag ;
910 l_sfev_one_off_tbl(i).income_or_expense := p_csm_one_off_fee_tbl(i).income_or_expense;
911 l_sfev_one_off_tbl(i).advance_or_arrears := G_ADVANCE;
912
913 /*
914 -- If the Fee is for a specific Asset, Store the corresponding SIL_ID.
915 -- Useful for Inbound API to map it back to the specific Asset
916 */
917 IF p_csm_one_off_fee_tbl(i).kle_asset_id IS NOT NULL
918 AND p_csm_one_off_fee_tbl(i).kle_asset_id <> Okc_Api.G_MISS_NUM
919 THEN
920
921 get_sil_id(p_csm_one_off_fee_tbl(i).kle_asset_id,l_sfev_one_off_tbl(i).sil_id);
922 IF l_sfev_one_off_tbl(i).sil_id = 0 THEN
923
924
925 Okl_Api.SET_MESSAGE(p_app_name => G_OKC_APP,
926 p_msg_name => G_INVALID_VALUE,
927 p_token1 => G_COL_NAME_TOKEN,
928 p_token1_value => 'KLE_ASSET_ID'
929 );
930 RAISE G_EXCEPTION_ERROR;
931 END IF;
932 END IF;
933
934 s_count := s_count + 1;
935 END LOOP;
936 RETURN l_sfev_one_off_tbl;
937 EXCEPTION
938 WHEN G_EXCEPTION_ERROR THEN
939 x_return_status := G_RET_STS_ERROR;
940 l_sfev_one_off_tbl.DELETE;
941 RETURN l_sfev_one_off_tbl;
942
943 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
944 x_return_status := G_RET_STS_UNEXP_ERROR;
945 l_sfev_one_off_tbl.DELETE;
946 RETURN l_sfev_one_off_tbl;
947 WHEN OTHERS THEN
948 -- store SQL error message on message stack
949 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
950 p_msg_name => G_UNEXPECTED_ERROR,
951 p_token1 => G_SQLCODE_TOKEN,
952 p_token1_value => SQLCODE,
953 p_token2 => G_SQLERRM_TOKEN,
954 p_token2_value => SQLERRM);
955 x_return_status := G_RET_STS_UNEXP_ERROR;
956 l_sfev_one_off_tbl.DELETE;
957 RETURN l_sfev_one_off_tbl;
958 END assign_one_off_fees;
959
960 ---suresh
961
962
963
964 ---------------------------------------------------------------------------
965
966 -- FUNCTION assign_periodic_expenses
967 -- Assigns Recurring Fee Details for this Contract
968 -- Can Occur both at the Header and Asset but always unique
969 ---------------------------------------------------------------------------
970 FUNCTION assign_periodic_expenses(
971 p_sif_id IN NUMBER ,
972 p_csm_periodic_expenses_tbl IN csm_periodic_expenses_tbl_type,
973 x_return_status OUT NOCOPY VARCHAR2
974 ) RETURN sfev_tbl_type
975 IS
976 l_sfev_periodic_tbl sfev_tbl_type;
977 i NUMBER := 0;
978 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
979 cursor get_rebook_type is
980 select nvl(amort_inc_adj_rev_dt_yn, 'N')
981 from okl_sys_acct_opts;
982
983 l_PROSPECTIVE_REBOOK_YN VARCHAR2(1);
984
985 BEGIN
986
987 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
988
989 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
990 OPEN get_rebook_type ;
991 FETCh get_rebook_type INTO l_PROSPECTIVE_REBOOK_YN ;
992 CLOSE get_rebook_type;
993
994
995 FOR i IN 1..p_csm_periodic_expenses_tbl.COUNT
996 LOOP
997 l_sfev_periodic_tbl(i).sif_id := p_sif_id;
998 l_sfev_periodic_tbl(i).kle_id := p_csm_periodic_expenses_tbl(i).kle_fee_id;
999
1000 --sechawla 16-JUL-09 PRB ESG enhancements : assign orig_contract_line_id
1001 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' THEN
1002 l_sfev_periodic_tbl(i).orig_contract_line_id := p_csm_periodic_expenses_tbl(i).orig_contract_line_id;
1003 --END IF;
1004
1005 l_sfev_periodic_tbl(i).description := p_csm_periodic_expenses_tbl(i).description;
1006 l_sfev_periodic_tbl(i).income_or_expense := p_csm_periodic_expenses_tbl(i).income_or_expense;
1007 l_sfev_periodic_tbl(i).date_start := p_csm_periodic_expenses_tbl(i).date_start;
1008 l_sfev_periodic_tbl(i).level_index_number := p_csm_periodic_expenses_tbl(i).level_index_number-1;
1009 l_sfev_periodic_tbl(i).level_type := p_csm_periodic_expenses_tbl(i).level_type;
1010 l_sfev_periodic_tbl(i).number_of_periods := p_csm_periodic_expenses_tbl(i).number_of_periods;
1011 l_sfev_periodic_tbl(i).amount := p_csm_periodic_expenses_tbl(i).amount;
1012 l_sfev_periodic_tbl(i).period := p_csm_periodic_expenses_tbl(i).period;
1013 l_sfev_periodic_tbl(i).advance_or_arrears := p_csm_periodic_expenses_tbl(i).advance_or_arrears;
1014 l_sfev_periodic_tbl(i).lock_level_step := p_csm_periodic_expenses_tbl(i).lock_level_step;
1015 -- 06/13/2002
1016 l_sfev_periodic_tbl(i).structure := p_csm_periodic_expenses_tbl(i).structure;
1017 l_sfev_periodic_tbl(i).cash_effect_yn := p_csm_periodic_expenses_tbl(i).cash_effect_yn;
1018 l_sfev_periodic_tbl(i).tax_effect_yn := p_csm_periodic_expenses_tbl(i).tax_effect_yn;
1019 l_sfev_periodic_tbl(i).days_in_month := p_csm_periodic_expenses_tbl(i).days_in_month;
1020 l_sfev_periodic_tbl(i).days_in_year := p_csm_periodic_expenses_tbl(i).days_in_year;
1021 -- RGOOTY: Bug 7552496: Start
1022 l_sfev_periodic_tbl(i).date_paid := p_csm_periodic_expenses_tbl(i).date_paid;
1023 -- RGOOTY: Bug 7552496: End
1024 /*
1025 -- If the Fee is for a specific Asset, Store the corresponding SIL_ID
1026 -- and the Asset Index as well.
1027 -- Useful for Inbound API to map it back to the specific Asset
1028 */
1029 IF p_csm_periodic_expenses_tbl(i).kle_asset_id IS NOT NULL
1030
1031
1032 AND p_csm_periodic_expenses_tbl(i).kle_asset_id <> Okc_Api.G_MISS_NUM
1033 THEN
1034 get_sil_id(p_csm_periodic_expenses_tbl(i).kle_asset_id,l_sfev_periodic_tbl(i).sil_id);
1035 IF l_sfev_periodic_tbl(i).sil_id = 0 THEN
1036 Okl_Api.SET_MESSAGE(p_app_name => G_OKC_APP,
1037 p_msg_name => G_INVALID_VALUE,
1038 p_token1 => G_COL_NAME_TOKEN,
1039 p_token1_value => 'KLE_ASSET_ID'
1040 );
1041 RAISE G_EXCEPTION_ERROR;
1042 END IF;
1043 get_line_index(p_csm_periodic_expenses_tbl(i).kle_asset_id,l_sfev_periodic_tbl(i).level_line_number);
1044 END IF;
1045 /* Assign Fee_Index
1046 -- Each of these Levels is grouped under specific Fee
1047 -- which is again ,if under an Asset, grouped under specific AssetID.
1048 -- Levels --> Fees --> (Assets) --> Header
1049 */
1050 -- 04/21/2002
1051 --l_sfev_periodic_tbl(i).sfe_type := G_SFE_TYPE_PERIODIC_EXPENSE;
1052
1053 IF p_csm_periodic_expenses_tbl(i).income_or_expense = G_EXPENSE
1054 THEN
1055 l_sfev_periodic_tbl(i).sfe_type := G_SFE_TYPE_PERIODIC_EXPENSE;
1056 get_fee_index(p_csm_periodic_expenses_tbl(i).description || p_csm_periodic_expenses_tbl(i).kle_asset_id || p_csm_periodic_expenses_tbl(i).kle_fee_id, -- added for fee type soln to take care of duplicate streams
1057 g_periodic_expenses_indexes,
1058 l_sfev_periodic_tbl(i).fee_index_number);
1059 ELSIF p_csm_periodic_expenses_tbl(i).income_or_expense = G_INCOME THEN
1060 l_sfev_periodic_tbl(i).sfe_type := G_SFE_TYPE_PERIODIC_INCOME;
1061 get_fee_index(p_csm_periodic_expenses_tbl(i).description || p_csm_periodic_expenses_tbl(i).kle_asset_id || p_csm_periodic_expenses_tbl(i).kle_fee_id, -- added for fee type soln to take care of duplicate streams
1062
1063 g_periodic_incomes_indexes,
1064 l_sfev_periodic_tbl(i).fee_index_number);
1065 END IF;
1066 -- END, 04/21/2002
1067 END LOOP;
1068 RETURN l_sfev_periodic_tbl;
1069 EXCEPTION
1070 WHEN G_EXCEPTION_ERROR THEN
1071 x_return_status := G_RET_STS_ERROR;
1072
1073 l_sfev_periodic_tbl.DELETE;
1074 RETURN l_sfev_periodic_tbl;
1075 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1076 x_return_status := G_RET_STS_UNEXP_ERROR;
1077 l_sfev_periodic_tbl.DELETE;
1078 RETURN l_sfev_periodic_tbl;
1079 WHEN OTHERS THEN
1080 -- store SQL error message on message stack
1081 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1082 p_msg_name => G_UNEXPECTED_ERROR,
1083 p_token1 => G_SQLCODE_TOKEN,
1084 p_token1_value => SQLCODE,
1085 p_token2 => G_SQLERRM_TOKEN,
1086 p_token2_value => SQLERRM);
1087 x_return_status := G_RET_STS_UNEXP_ERROR;
1088 l_sfev_periodic_tbl.DELETE;
1089 RETURN l_sfev_periodic_tbl;
1090 END assign_periodic_expenses;
1091 ---------------------------------------------------------------------------
1092 -- FUNCTION assign_rent_details
1093 -- Assigns the Rent Details for this Contract (for Assets only)
1094 ---------------------------------------------------------------------------
1095 FUNCTION assign_rent_details(
1096
1097 p_sif_id IN NUMBER ,
1098 p_csm_periodic_expenses_tbl IN csm_periodic_expenses_tbl_type,
1099 x_return_status OUT NOCOPY VARCHAR2
1100 ) RETURN sfev_tbl_type
1101 IS
1102 l_sfev_periodic_tbl sfev_tbl_type;
1103 i NUMBER := 0;
1104 --Added bu kthiruva on 02-Dec-2005
1105 --Bug 4766555 - Start of Changes
1106 j NUMBER := 0;
1107 --Bug 4766555 - End of Changes
1108
1109 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
1110 cursor get_rebook_type is
1111 select nvl(amort_inc_adj_rev_dt_yn, 'N')
1112 from okl_sys_acct_opts;
1113
1114 l_PROSPECTIVE_REBOOK_YN VARCHAR2(1);
1115
1116 BEGIN
1117 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1118 --Added bu kthiruva on 02-Dec-2005
1119 --The p_csm_periodic_expenses_tbl should be traversed from FIRST to LAST
1120 --and not from 1 to tbl.COUNT
1121 --Bug 4766555 - Start of Changes
1122 j := p_csm_periodic_expenses_tbl.FIRST;
1123
1124 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
1125 OPEN get_rebook_type;
1126 FETCH get_rebook_type INTO l_PROSPECTIVE_REBOOK_YN ;
1127 CLOSE get_rebook_type;
1128
1129 FOR i IN 1..p_csm_periodic_expenses_tbl.COUNT
1130 LOOP
1131 l_sfev_periodic_tbl(i).sif_id := p_sif_id;
1132 l_sfev_periodic_tbl(i).kle_id := p_csm_periodic_expenses_tbl(j).kle_fee_id;
1133 l_sfev_periodic_tbl(i).sfe_type := G_SFE_TYPE_RENT;
1134 l_sfev_periodic_tbl(i).description := p_csm_periodic_expenses_tbl(j).description;
1135 l_sfev_periodic_tbl(i).income_or_expense := p_csm_periodic_expenses_tbl(j).income_or_expense;
1136 l_sfev_periodic_tbl(i).date_start := p_csm_periodic_expenses_tbl(j).date_start;
1137 l_sfev_periodic_tbl(i).level_index_number := p_csm_periodic_expenses_tbl(j).level_index_number-1;
1138 l_sfev_periodic_tbl(i).level_type := p_csm_periodic_expenses_tbl(j).level_type;
1139 l_sfev_periodic_tbl(i).number_of_periods := p_csm_periodic_expenses_tbl(j).number_of_periods;
1140 l_sfev_periodic_tbl(i).amount := p_csm_periodic_expenses_tbl(j).amount;
1141 l_sfev_periodic_tbl(i).period := p_csm_periodic_expenses_tbl(j).period;
1142 l_sfev_periodic_tbl(i).advance_or_arrears := p_csm_periodic_expenses_tbl(j).advance_or_arrears ;
1143 l_sfev_periodic_tbl(i).lock_level_step := p_csm_periodic_expenses_tbl(j).lock_level_step;
1144 l_sfev_periodic_tbl(i).query_level_yn := p_csm_periodic_expenses_tbl(j).query_level_yn;
1145 -- 06/13/2002
1146 l_sfev_periodic_tbl(i).structure := p_csm_periodic_expenses_tbl(j).structure;
1147 l_sfev_periodic_tbl(i).rate := p_csm_periodic_expenses_tbl(j).rate;
1148 l_sfev_periodic_tbl(i).days_in_month := p_csm_periodic_expenses_tbl(j).days_in_month;
1149 l_sfev_periodic_tbl(i).days_in_year := p_csm_periodic_expenses_tbl(j).days_in_year;
1150 l_sfev_periodic_tbl(i).down_payment_amount := p_csm_periodic_expenses_tbl(j).down_payment_amount;
1151
1152 --sechawla 14-JUL-09 PRB ESG enhancements : assign orig_contract_line_id
1153 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' THEN
1154 l_sfev_periodic_tbl(i).orig_contract_line_id := p_csm_periodic_expenses_tbl(j).orig_contract_line_id;
1155 --END IF;
1156
1157 /*
1158 -- For each Rent, Store the corresponding SIL_ID.
1159 -- Useful for Inbound API to map it back to the specific Asset
1160 */
1161
1162 IF p_csm_periodic_expenses_tbl(j).kle_asset_id IS NOT NULL
1163 AND p_csm_periodic_expenses_tbl(j).kle_asset_id <> Okc_Api.G_MISS_NUM
1164 THEN
1165 get_sil_id(p_csm_periodic_expenses_tbl(j).kle_asset_id,l_sfev_periodic_tbl(i).sil_id);
1166 IF l_sfev_periodic_tbl(i).sil_id = 0 THEN
1167 Okl_Api.SET_MESSAGE(p_app_name => G_OKC_APP,
1168 p_msg_name => G_INVALID_VALUE,
1169
1170 p_token1 => G_COL_NAME_TOKEN,
1171 p_token1_value => 'KLE_ASSET_ID'
1172 );
1173 RAISE G_EXCEPTION_ERROR;
1174 END IF;
1175 get_line_index(p_csm_periodic_expenses_tbl(j).kle_asset_id,l_sfev_periodic_tbl(i).level_line_number);
1176 END IF;
1177 get_fee_index(p_csm_periodic_expenses_tbl(j).description || p_csm_periodic_expenses_tbl(j).kle_asset_id,
1178 g_rents_indexes,
1179 l_sfev_periodic_tbl(i).fee_index_number);
1180 j := p_csm_periodic_expenses_tbl.NEXT(j);
1181 --Bug 4766555 - End of Changes
1182 END LOOP;
1183 RETURN l_sfev_periodic_tbl;
1184 EXCEPTION
1185
1186 WHEN G_EXCEPTION_ERROR THEN
1187 x_return_status := G_RET_STS_ERROR;
1188 l_sfev_periodic_tbl.DELETE;
1189 RETURN l_sfev_periodic_tbl;
1190 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1191 x_return_status := G_RET_STS_UNEXP_ERROR;
1192 l_sfev_periodic_tbl.DELETE;
1193 RETURN l_sfev_periodic_tbl;
1194 WHEN OTHERS THEN
1195 -- store SQL error message on message stack
1196
1197 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1198 p_msg_name => G_UNEXPECTED_ERROR,
1199 p_token1 => G_SQLCODE_TOKEN,
1200 p_token1_value => SQLCODE,
1201 p_token2 => G_SQLERRM_TOKEN,
1202 p_token2_value => SQLERRM);
1203 x_return_status := G_RET_STS_UNEXP_ERROR;
1204 l_sfev_periodic_tbl.DELETE;
1205 RETURN l_sfev_periodic_tbl;
1206 END assign_rent_details;
1207
1208 ---------------------------------------------------------------------------
1209 -- FUNCTION assign_yield_details
1210 ---------------------------------------------------------------------------
1211 FUNCTION assign_yield_details(
1212 p_sif_id IN NUMBER ,
1213 p_csm_yields_tbl IN csm_yields_tbl_type,
1214 x_return_status OUT NOCOPY VARCHAR2
1215 ) RETURN siyv_tbl_type
1216 IS
1217 l_siyv_tbl siyv_tbl_type;
1218 i NUMBER := 0;
1219 BEGIN
1220 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1221 FOR i IN 1..p_csm_yields_tbl.COUNT
1222 LOOP
1223 l_siyv_tbl(i).sif_id := p_sif_id;
1224 l_siyv_tbl(i).yield_name := p_csm_yields_tbl(i).yield_name;
1225 l_siyv_tbl(i).method := p_csm_yields_tbl(i).method;
1226 l_siyv_tbl(i).array_type := p_csm_yields_tbl(i).array_type;
1227 l_siyv_tbl(i).roe_type := p_csm_yields_tbl(i).roe_type;
1228 l_siyv_tbl(i).roe_base := p_csm_yields_tbl(i).roe_base;
1229 l_siyv_tbl(i).compounded_method := p_csm_yields_tbl(i).compounded_method;
1230 l_siyv_tbl(i).target_value := p_csm_yields_tbl(i).target_value;
1231 l_siyv_tbl(i).nominal_yn := p_csm_yields_tbl(i).nominal_yn;
1232 /* Translate Nominal_yn
1233
1234 IF p_csm_yields_tbl(i).nominal_yn IS NOT NULL
1235 AND p_csm_yields_tbl(i).nominal_yn <> OKC_API.G_MISS_CHAR
1236 THEN
1237 IF p_csm_yields_tbl(i).nominal_yn = G_FND_YES
1238 THEN
1239 l_siyv_tbl(i).nominal_yn := G_CSM_TRUE;
1240 ELSIF p_csm_yields_tbl(i).nominal_yn = G_FND_NO
1241 THEN
1242 l_siyv_tbl(i).nominal_yn := G_CSM_FALSE;
1243 END IF;
1244 END IF;
1245 */
1246 -- mvasudev, 06/26/2002, sno
1247 l_siyv_tbl(i).siy_type := p_csm_yields_tbl(i).siy_type;
1248 get_siy_index(p_csm_yields_tbl(i).siy_type || p_csm_yields_tbl(i).yield_name
1249
1250 ,l_siyv_tbl(i).index_number);
1251 END LOOP;
1252 RETURN l_siyv_tbl;
1253 EXCEPTION
1254 WHEN G_EXCEPTION_ERROR THEN
1255 x_return_status := G_RET_STS_ERROR;
1256 l_siyv_tbl.DELETE;
1257 RETURN l_siyv_tbl;
1258 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1259 x_return_status := G_RET_STS_UNEXP_ERROR;
1260 l_siyv_tbl.DELETE;
1261 RETURN l_siyv_tbl;
1262
1263 WHEN OTHERS THEN
1264 -- store SQL error message on message stack
1265 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1266 p_msg_name => G_UNEXPECTED_ERROR,
1267 p_token1 => G_SQLCODE_TOKEN,
1268 p_token1_value => SQLCODE,
1269 p_token2 => G_SQLERRM_TOKEN,
1270 p_token2_value => SQLERRM);
1271 x_return_status := G_RET_STS_UNEXP_ERROR;
1272 l_siyv_tbl.DELETE;
1273 RETURN l_siyv_tbl;
1274 END assign_yield_details;
1275 ---------------------------------------------------------------------------
1276
1277 -- FUNCTION assign_stream_types
1278 ---------------------------------------------------------------------------
1279 FUNCTION assign_stream_types(
1280 p_sif_id IN NUMBER ,
1281 p_csm_stream_types_tbl IN csm_stream_types_tbl_type,
1282 x_return_status OUT NOCOPY VARCHAR2
1283 ) RETURN sitv_tbl_type
1284 IS
1285 CURSOR l_okl_sfe_ids_csr(p_sif_id IN OKL_SIF_FEES.sif_id%TYPE)
1286
1287 IS
1288 SELECT DISTINCT
1289 KLE_ID, ID
1290 FROM OKL_SIF_FEES
1291 WHERE SIF_ID = p_sif_id
1292 AND kle_id IS NOT NULL;
1293 -- gboomina commented Bug#4508077
1294 /*
1295 -- bug 3548593 smahapat added to filter out one time expenses
1296 -- since streams are not generated
1297 AND id NOT IN (
1298 SELECT id FROM OKL_SIF_FEES
1299 WHERE sfe_type = 'SFO'
1300
1301 AND INCOME_OR_EXPENSE = 'EXPENSE'
1302 );
1303 */
1304 -- satya commented
1305 --AND SFE_TYPE NOT IN ('SFO'); -- smahapat added for fee type soln
1306 CURSOR l_okl_sty_name_csr(p_sty_id IN okl_strm_type_tl.id%TYPE)
1307 IS
1308 SELECT NAME
1309 FROM OKL_STRM_TYPE_TL
1310 WHERE ID = p_sty_id
1311 AND LANGUAGE = USERENV('LANG'); -- smahapat fixed bug# 3323146
1312 CURSOR l_okl_sil_sfe_ids_csr(p_sif_id IN OKL_SIF_FEES.sif_id%TYPE,
1313
1314 p_sil_id IN OKL_SIF_FEES.sil_id%TYPE,
1315 p_description IN OKL_SIF_FEES.description%TYPE)
1316 IS
1317 SELECT
1318 ID
1319 FROM OKL_SIF_FEES
1320 WHERE SIF_ID = p_sif_id
1321 AND SIL_ID = p_sil_id
1322 AND (level_index_number = 0 OR level_index_number IS NULL)
1323 AND DESCRIPTION = p_description
1324 -- bug 3548593 smahapat added to filter out one time expenses
1325 -- since streams are not generated
1326 AND id NOT IN (
1327 SELECT id FROM OKL_SIF_FEES
1328 WHERE sfe_type = 'SFO'
1329 AND INCOME_OR_EXPENSE = 'EXPENSE'
1330 );
1331 l_sitv_tbl sitv_tbl_type;
1332 i NUMBER := 0;
1333 l_sty_name VARCHAR2(150);
1334 l_token1_value VARCHAR2(150);
1335 BEGIN
1336 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1337
1338 FOR l_okl_sfe_ids IN l_okl_sfe_ids_csr(p_sif_id)
1339 LOOP
1340 i := i + 1;
1341 g_sfe_ids(i).kle_fee_id := l_okl_sfe_ids.kle_id;
1342 g_sfe_ids(i).sfe_id := l_okl_sfe_ids.id;
1343
1344
1345 END LOOP;
1346
1347
1348 FOR i IN 1..p_csm_stream_types_tbl.COUNT
1349 LOOP
1350 l_sty_name := NULL;
1351 l_sitv_tbl(i).sif_id := p_sif_id;
1352 l_sitv_tbl(i).sty_id := p_csm_stream_types_tbl(i).stream_type_id;
1353 l_sitv_tbl(i).pricing_name := p_csm_stream_types_tbl(i).pricing_name;
1354 IF p_csm_stream_types_tbl(i).kle_asset_id IS NOT NULL
1355 AND p_csm_stream_types_tbl(i).kle_asset_id <> Okc_Api.G_MISS_NUM
1356 THEN
1357
1358 get_sil_id(p_csm_stream_types_tbl(i).kle_asset_id,l_sitv_tbl(i).sil_id);
1359
1360 IF l_sitv_tbl(i).sil_id = 0 THEN
1361 Okl_Api.SET_MESSAGE(p_app_name => G_OKC_APP,
1362 p_msg_name => G_INVALID_VALUE,
1363 p_token1 => G_COL_NAME_TOKEN,
1364
1365 p_token1_value => 'KLE_ASSET_ID'
1366 );
1367 RAISE G_EXCEPTION_ERROR;
1368 END IF;
1369 -- While given an 'AssetLineID', the api looks for the corresponding fee entries
1370 -- in SFE tables looking up with SFE.DESCRIPTION
1371 -- (which is actually the "StreamTypeName" got from the sty_id of this rec).
1372 -- 05/03/2002,mvasudev
1373
1374 OPEN l_okl_sty_name_csr(p_csm_stream_types_tbl(i).stream_type_id);
1375 FETCH l_okl_sty_name_csr INTO l_sty_name;
1376 CLOSE l_okl_sty_name_csr;
1377 /* -- 06/13/2002
1378 -- assign sfe_id
1379 SELECT
1380 ID
1381 INTO l_sitv_tbl(i).sfe_id
1382 FROM OKL_SIF_FEES
1383 WHERE SIF_ID = p_sif_id
1384 AND SIL_ID = l_sitv_tbl(i).sil_id
1385 --AND SFE_TYPE = 'SFR' -- 04/10/2002
1386 -- 04/23/2002,mvasudev
1387 AND (level_index_number = 0 OR level_index_number IS NULL)
1388 -- 05/03/2002,mvasudev
1389 AND DESCRIPTION = l_sty_name;
1390
1391 */
1392 OPEN l_okl_sil_sfe_ids_csr(p_sif_id, l_sitv_tbl(i).sil_id,l_sty_name);
1393 FETCH l_okl_sil_sfe_ids_csr INTO l_sitv_tbl(i).sfe_id;
1394 CLOSE l_okl_sil_sfe_ids_csr;
1395 ELSIF p_csm_stream_types_tbl(i).kle_fee_id IS NOT NULL
1396 AND p_csm_stream_types_tbl(i).kle_fee_id <> Okc_Api.G_MISS_NUM
1397 THEN
1398 get_sfe_id(p_csm_stream_types_tbl(i).kle_fee_id,p_csm_stream_types_tbl(i).stream_type_id,l_sitv_tbl(i).sfe_id);
1399 -- smahapat added for fee type soln
1400 IF l_sitv_tbl(i).sfe_id = 0 THEN
1401 -- akjain added for bug # 2442036, getting token value from AK Prompts
1402 l_token1_value := Okl_Accounting_Util.Get_Message_Token(p_region_code => G_AK_REGION_NAME,
1403
1404 p_attribute_code => 'OKL_FEE_LINE_ID'
1405
1406 );
1407
1408 Okl_Api.SET_MESSAGE(p_app_name => G_OKC_APP,
1409 p_msg_name => G_INVALID_VALUE,
1410 p_token1 => G_COL_NAME_TOKEN,
1411 p_token1_value => l_token1_value
1412 );
1413 RAISE G_EXCEPTION_ERROR;
1414 END IF;
1415 END IF;
1416
1417 END LOOP;
1418 RETURN l_sitv_tbl;
1419 EXCEPTION
1420 WHEN G_EXCEPTION_ERROR THEN
1421 x_return_status := G_RET_STS_ERROR;
1422 l_sitv_tbl.DELETE;
1423 IF l_okl_sty_name_csr%isopen THEN
1424 CLOSE l_okl_sty_name_csr;
1425 END IF;
1426 IF l_okl_sil_sfe_ids_csr%isopen THEN
1427 CLOSE l_okl_sty_name_csr;
1428 END IF;
1429 IF l_okl_sfe_ids_csr%isopen THEN
1430 CLOSE l_okl_sty_name_csr;
1431 END IF;
1432 RETURN l_sitv_tbl;
1433 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1434 x_return_status := G_RET_STS_UNEXP_ERROR;
1435 l_sitv_tbl.DELETE;
1436 IF l_okl_sty_name_csr%isopen THEN
1437 CLOSE l_okl_sty_name_csr;
1438
1439 END IF;
1440 IF l_okl_sil_sfe_ids_csr%isopen THEN
1441 CLOSE l_okl_sty_name_csr;
1442 END IF;
1443 IF l_okl_sfe_ids_csr%isopen THEN
1444 CLOSE l_okl_sty_name_csr;
1445 END IF;
1446 RETURN l_sitv_tbl;
1447 WHEN OTHERS THEN
1448
1449 -- store SQL error message on message stack
1450 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1451 p_msg_name => G_UNEXPECTED_ERROR,
1452 p_token1 => G_SQLCODE_TOKEN,
1453 p_token1_value => SQLCODE,
1454 p_token2 => G_SQLERRM_TOKEN,
1455 p_token2_value => SQLERRM);
1456 x_return_status := G_RET_STS_UNEXP_ERROR;
1457 l_sitv_tbl.DELETE;
1458 IF l_okl_sty_name_csr%isopen THEN
1459 CLOSE l_okl_sty_name_csr;
1460 END IF;
1461 IF l_okl_sil_sfe_ids_csr%isopen THEN
1462 CLOSE l_okl_sty_name_csr;
1463 END IF;
1464 IF l_okl_sfe_ids_csr%isopen THEN
1465 CLOSE l_okl_sty_name_csr;
1466
1467 END IF;
1468 RETURN l_sitv_tbl;
1469 END assign_stream_types;
1470
1471 ---------------------------------------------------------------------------
1472 -- PROCEDURE insert_loan_lines
1473 -- Assigns and Inserts Loan Line details for this Contract
1474 ---------------------------------------------------------------------------
1475 PROCEDURE insert_loan_lines(
1476 p_api_version IN NUMBER
1477 ,p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
1478 ,p_sif_id IN NUMBER
1479 ,p_csm_loan_lines_tbl IN csm_loan_line_tbl_type
1480 ,x_return_status OUT NOCOPY VARCHAR2
1481 ,x_msg_count OUT NOCOPY NUMBER
1482 ,x_msg_data OUT NOCOPY VARCHAR2
1483 ) IS
1484
1485 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
1486 cursor get_rebook_type is
1487 select nvl(amort_inc_adj_rev_dt_yn, 'N')
1488 from okl_sys_acct_opts;
1489
1490 l_PROSPECTIVE_REBOOK_YN VARCHAR2(1);
1491
1492 lp_silv_rec silv_rec_type;
1493 lx_silv_rec silv_rec_type;
1494 i NUMBER := 0;
1495 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1496 l_index NUMBER := -1;
1497 l_sil_index NUMBER := 0;
1498 l_count NUMBER := 0;
1499
1500 BEGIN
1501 l_return_status := G_RET_STS_SUCCESS;
1502
1503 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
1504 OPEN get_rebook_type ;
1505 FETCH get_rebook_type INTO l_PROSPECTIVE_REBOOK_YN;
1506 CLOSE get_rebook_type;
1507
1508 FOR i IN 1..p_csm_loan_lines_tbl.COUNT
1509 LOOP
1510 --lp_silv_rec.index_number := i;
1511 l_index := i-1;
1512 lp_silv_rec.index_number := l_index;
1513 lp_silv_rec.sif_id := p_sif_id;
1514 lp_silv_rec.kle_id := p_csm_loan_lines_tbl(i).kle_loan_id;
1515
1516
1517 --sechawla 10-jul-09 : PRB ESG enhancements : begin
1518 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' then
1519 lp_silv_rec.orig_contract_line_id := p_csm_loan_lines_tbl(i).orig_contract_line_id;
1520 --END IF;
1521 --sechawla 10-jul-09 : PRB ESG enhancements : end
1522
1523 lp_silv_rec.sil_type := G_SIL_TYPE_LOAN;
1524 --Added by kthiruva on 15-Nov-2005 for the Down Payment CR
1525 --Bug 4738011 - Start of Changes
1526 lp_silv_rec.down_payment_amount := p_csm_loan_lines_tbl(i).down_payment_amount;
1527 lp_silv_rec.capitalize_down_payment_yn := p_csm_loan_lines_tbl(i).capitalize_down_payment_yn;
1528 --Bug 4738011 - End of Changes
1529 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Lines_Pub.insert_sif_lines
1530 IF(L_DEBUG_ENABLED='Y') THEN
1531 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
1532 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
1533 END IF;
1534 IF(IS_DEBUG_PROCEDURE_ON) THEN
1535 BEGIN
1536 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Lines_Pub.insert_sif_lines ');
1537 END;
1538 END IF;
1539 Okl_Sif_Lines_Pub.insert_sif_lines(
1540 p_api_version => p_api_version
1541 ,p_init_msg_list => p_init_msg_list
1542 ,x_return_status => l_return_status
1543 ,x_msg_count => x_msg_count
1544 ,x_msg_data => x_msg_data
1545 ,p_silv_rec => lp_silv_rec
1546 ,x_silv_rec => lx_silv_rec);
1547 IF(IS_DEBUG_PROCEDURE_ON) THEN
1548 BEGIN
1549 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Lines_Pub.insert_sif_lines ');
1550 END;
1551 END IF;
1552 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Lines_Pub.insert_sif_lines
1553 IF l_return_status = G_RET_STS_ERROR THEN
1554 RAISE G_EXCEPTION_ERROR;
1555 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1556 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1557 END IF;
1558 --satya changed 08/28 for fin fee
1559 --l_count := g_sil_ids.count;
1560 l_count := g_sil_ids.count + 1;
1561 IF i = 1 then
1562 l_sil_index := l_count + 1;
1563 ELSE
1564 l_sil_index := l_count + i;
1565 END IF;
1566 g_sil_ids(l_sil_index).kle_asset_id := p_csm_loan_lines_tbl(i).kle_loan_id;
1567 g_sil_ids(l_sil_index).sil_id := lx_silv_rec.id;
1568 --g_sil_ids(l_sil_index).kle_asset_id := p_csm_loan_lines_tbl(i).kle_loan_id;
1569 --g_sil_ids(l_sil_index).sil_id := lx_silv_rec.id;
1570 --satya change end 08/28 for fin fee
1571 END LOOP;
1572 x_return_status := l_return_status;
1573 EXCEPTION
1574 WHEN G_EXCEPTION_ERROR THEN
1575 x_return_status := G_RET_STS_ERROR;
1576 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1577 x_return_status := G_RET_STS_UNEXP_ERROR;
1578 WHEN OTHERS THEN
1579 -- store SQL error message on message stack
1580 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1581 p_msg_name => G_UNEXPECTED_ERROR,
1582 p_token1 => G_SQLCODE_TOKEN,
1583 p_token1_value => SQLCODE,
1584 p_token2 => G_SQLERRM_TOKEN,
1585 p_token2_value => SQLERRM);
1586 x_return_status := G_RET_STS_UNEXP_ERROR;
1587 END insert_loan_lines;
1588 -- 04/21/2002
1589 ---------------------------------------------------------------------------
1590 -- FUNCTION assign_loan_lines
1591 -- Assigns Loan Line Details for this Contract
1592 ---------------------------------------------------------------------------
1593 FUNCTION assign_loan_levels(
1594 p_sif_id IN NUMBER,
1595 p_csm_loan_levels_tbl IN csm_loan_level_tbl_type,
1596 p_object1_id1 IN NUMBER DEFAULT 0,
1597 x_return_status OUT NOCOPY VARCHAR2
1598 ) RETURN sfev_tbl_type
1599 IS
1600 l_sfev_periodic_tbl sfev_tbl_type;
1601 i NUMBER := 0;
1602 l_object1_id1 NUMBER;
1603 --Added by kthiruva on 07-Dec-2005
1604 --Bug 4766555 - Start of Changes
1605 j NUMBER := 0;
1606 --Bug 4766555 - End of Changes
1607
1608 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
1609 cursor get_rebook_type is
1610 select nvl(amort_inc_adj_rev_dt_yn, 'N')
1611 from okl_sys_acct_opts;
1612
1613 l_PROSPECTIVE_REBOOK_YN VARCHAR2(1);
1614
1615 BEGIN
1616 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1617
1618 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
1619 OPEN get_rebook_type ;
1620 FETCH get_rebook_type INTO l_PROSPECTIVE_REBOOK_YN;
1621 CLOSE get_rebook_type;
1622
1623 --Added by kthiruva on 07-Dec-2005
1624 --The p_csm_loan_levels_tbl should be traversed from FIRST to LAST
1625 --and not from 1 to tbl.COUNT
1626 --Bug 4766555 - Start of Changes
1627 j := p_csm_loan_levels_tbl.FIRST;
1628 FOR i IN 1..p_csm_loan_levels_tbl.COUNT
1629 LOOP
1630 l_sfev_periodic_tbl(i).sif_id := p_sif_id;
1631
1632 --sechawla 15-Jul-09 ESG PRB Enhancements : populate orig_contract_line_id
1633 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' THEN
1634 l_sfev_periodic_tbl(i).orig_contract_line_id := p_csm_loan_levels_tbl(j).orig_contract_line_id;
1635 --END IF;
1636
1637 l_sfev_periodic_tbl(i).sfe_type := G_SFE_TYPE_LOAN;
1638 l_sfev_periodic_tbl(i).description := p_csm_loan_levels_tbl(j).description;
1639 l_sfev_periodic_tbl(i).level_type := p_csm_loan_levels_tbl(j).level_type;
1640 l_sfev_periodic_tbl(i).amount := p_csm_loan_levels_tbl(j).amount;
1641 l_sfev_periodic_tbl(i).days_in_month := p_csm_loan_levels_tbl(j).days_in_month;
1642 l_sfev_periodic_tbl(i).days_in_year := p_csm_loan_levels_tbl(j).days_in_year;
1643 --Added by mansrini for the VR build
1644 l_sfev_periodic_tbl(i).balance_type_code := p_csm_loan_levels_tbl(j).balance_type_code;
1645
1646 IF p_csm_loan_levels_tbl(j).level_type <> G_SFE_LEVEL_FUNDING THEN
1647 l_sfev_periodic_tbl(i).income_or_expense := p_csm_loan_levels_tbl(j).income_or_expense;
1648 l_sfev_periodic_tbl(i).level_index_number := p_csm_loan_levels_tbl(j).level_index_number-1;
1649 l_sfev_periodic_tbl(i).number_of_periods := p_csm_loan_levels_tbl(j).number_of_periods;
1650 l_sfev_periodic_tbl(i).period := p_csm_loan_levels_tbl(j).period;
1651 --04/23/2002
1652 l_sfev_periodic_tbl(i).date_start := p_csm_loan_levels_tbl(j).date_start;
1653 l_sfev_periodic_tbl(i).advance_or_arrears := p_csm_loan_levels_tbl(j).advance_or_arrears;
1654 l_sfev_periodic_tbl(i).lock_level_step := p_csm_loan_levels_tbl(j).lock_level_step;
1655 l_sfev_periodic_tbl(i).rate := p_csm_loan_levels_tbl(j).rate;
1656
1657 -- 06/13/2002
1658 l_sfev_periodic_tbl(i).structure := p_csm_loan_levels_tbl(j).structure;
1659 --08/20/02 akjain
1660 l_sfev_periodic_tbl(i).query_level_yn := p_csm_loan_levels_tbl(j).query_level_yn;
1661 ELSE
1662 -- Default the mandatory attributes to some value and Disregard it while Map-Processing
1663 -- as These are not useful in case of "Funding" Level
1664 l_sfev_periodic_tbl(i).income_or_expense := G_EXPENSE;
1665 ---satya changed for PPD oct 2004
1666
1667 l_sfev_periodic_tbl(i).level_index_number := p_csm_loan_levels_tbl(j).level_index_number-1;
1668
1669 l_sfev_periodic_tbl(i).number_of_periods := 0;
1670 l_sfev_periodic_tbl(i).period := 'M';
1671 l_sfev_periodic_tbl(i).date_start := SYSDATE;
1672 l_sfev_periodic_tbl(i).date_start := p_csm_loan_levels_tbl(j).date_start;
1673 l_sfev_periodic_tbl(i).advance_or_arrears := G_ADVANCE;
1674
1675 --Added by kthiruva on 13-Apr-2006
1676 --Retaining the value of query_level_yn that has been set already
1677 --Bug 5090060 - Start of Changes
1678 l_sfev_periodic_tbl(i).query_level_yn := p_csm_loan_levels_tbl(j).query_level_yn;
1679 --Bug 5090060 - End of Changes
1680
1681 END IF;
1682 /*
1683 -- For each Rent, Store the corresponding SIL_ID.
1684 -- Useful for Inbound API to map it back to the specific Asset
1685 */
1686
1687 IF p_csm_loan_levels_tbl(j).kle_loan_id IS NOT NULL
1688 AND p_csm_loan_levels_tbl(j).kle_loan_id <> Okc_Api.G_MISS_NUM
1689 THEN
1690
1691 get_sil_id(p_csm_loan_levels_tbl(j).kle_loan_id,l_sfev_periodic_tbl(i).sil_id);
1692
1693
1694
1695 IF l_sfev_periodic_tbl(i).sil_id = 0 THEN
1696
1697 Okl_Api.SET_MESSAGE(p_app_name => G_OKC_APP,
1698 p_msg_name => G_INVALID_VALUE,
1699 p_token1 => G_COL_NAME_TOKEN,
1700 p_token1_value => 'KLE_ASSET_ID'
1701 );
1702 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1703 END IF;
1704 /* Assign Line_Index
1705 -- Each of these Levels is , necessarily, grouped under specific AssetID.
1706 -- LoanLevels --> LoanLines --> Header
1707 */
1708 get_line_index(p_csm_loan_levels_tbl(j).kle_loan_id,l_sfev_periodic_tbl(i).level_line_number);
1709 -- make asset_index and fee_index same, assuming one-to-one correspondence
1710 l_sfev_periodic_tbl(i).fee_index_number := l_sfev_periodic_tbl(i).level_line_number;
1711 ELSE
1712 --smahapat for quotes only 10/30/03
1713 l_sfev_periodic_tbl(i).fee_index_number := 0;
1714 END IF;
1715 j := p_csm_loan_levels_tbl.NEXT(j);
1716 END LOOP;
1717 --Bug 4766555 - End of Changes
1718
1719 RETURN l_sfev_periodic_tbl;
1720
1721 EXCEPTION
1722 WHEN G_EXCEPTION_ERROR THEN
1723 x_return_status := G_RET_STS_ERROR;
1724 l_sfev_periodic_tbl.DELETE;
1725 RETURN l_sfev_periodic_tbl;
1726 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1727 x_return_status := G_RET_STS_UNEXP_ERROR;
1728 l_sfev_periodic_tbl.DELETE;
1729 RETURN l_sfev_periodic_tbl;
1730 WHEN OTHERS THEN
1731
1732 -- store SQL error message on message stack
1733 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1734 p_msg_name => G_UNEXPECTED_ERROR,
1735 p_token1 => G_SQLCODE_TOKEN,
1736 p_token1_value => SQLCODE,
1737 p_token2 => G_SQLERRM_TOKEN,
1738 p_token2_value => SQLERRM);
1739 x_return_status := G_RET_STS_UNEXP_ERROR;
1740 l_sfev_periodic_tbl.DELETE;
1741
1742 RETURN l_sfev_periodic_tbl;
1743 END assign_loan_levels;
1744 -- end,04/21/2002
1745
1746
1747
1748 /*=========================================================================================+
1749 | -- PROCEDURE insert_rollover_fee_for_lease |
1750 | -- This procedure takes care of inserting rollover fee loan lines with in |
1751 | -- a lease. It checks for the fee type 'FINACED' to identify rollover fee. |
1752 | -- Assigns and Inserts Loan Line AND LOAN LEVEL details for this Contract |
1753 +==========================================================================================*/
1754
1755 PROCEDURE insert_rollover_fee_for_lease(
1756 p_api_version IN NUMBER
1757 ,p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
1758
1759 ,p_sif_id IN NUMBER
1760 ,p_csm_one_off_fee_tbl IN csm_one_off_fee_tbl_type
1761 ,p_csm_periodic_expenses_tbl IN csm_periodic_expenses_tbl_type
1762 ,x_csm_one_off_fee_tbl OUT NOCOPY csm_one_off_fee_tbl_type
1763 ,x_csm_periodic_expenses_tbl OUT NOCOPY csm_periodic_expenses_tbl_type
1764 ,x_return_status OUT NOCOPY VARCHAR2
1765 ,x_msg_count OUT NOCOPY NUMBER
1766 ,x_msg_data OUT NOCOPY VARCHAR2
1767 ) IS
1768
1769 lp_silv_rec silv_rec_type;
1770 lx_silv_rec silv_rec_type;
1771 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1772 l_csm_loan_lines_tbl csm_loan_line_tbl_type;
1773 l_csm_loan_levels_tbl csm_loan_level_tbl_type;
1774
1775 lp_sfev_loan_levels_tbl sfev_tbl_type;
1776
1777 lx_sfev_loan_levels_tbl sfev_tbl_type;
1778
1779 l_index NUMBER := -1;
1780 l_periodic_expense_count NUMBER:=0;
1781 l_periodic_expense_counter NUMBER:=0;
1782 l_loan_line_counter NUMBER:=0;
1783 l_loan_level_counter NUMBER:=0;
1784 l_rec_count NUMBER:=0;
1785
1786 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
1787 cursor get_rebook_type is
1788 select nvl(amort_inc_adj_rev_dt_yn, 'N')
1789 from okl_sys_acct_opts;
1790
1791 l_PROSPECTIVE_REBOOK_YN VARCHAR2(1);
1792 BEGIN
1793 l_return_status := G_RET_STS_SUCCESS;
1794
1795 -- assgning the input structures to local structures
1796 x_csm_one_off_fee_tbl := p_csm_one_off_fee_tbl;
1797
1798 x_csm_periodic_expenses_tbl := p_csm_periodic_expenses_tbl;
1799
1800 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
1801 OPEN get_rebook_type;
1802 FETCH get_rebook_type INTO l_PROSPECTIVE_REBOOK_YN;
1803 CLOSE get_rebook_type;
1804
1805 FOR i IN 1..x_csm_one_off_fee_tbl.COUNT
1806 LOOP
1807 IF x_csm_one_off_fee_tbl(i).fee_type = 'ROLLOVER' THEN
1808 l_rec_count := l_rec_count + 1;
1809
1810 -- fill the loan line table
1811 l_loan_line_counter := l_loan_line_counter + 1;
1812 l_csm_loan_lines_tbl(l_loan_line_counter).kle_loan_id := x_csm_one_off_fee_tbl(i).kle_fee_id;
1813
1814 --sechawla 17-Jul-09 : PRB ESG Enhancements : assign orig_contract_line_id
1815 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' THEN
1816 l_csm_loan_lines_tbl(l_loan_level_counter).orig_contract_line_id := x_csm_one_off_fee_tbl(i).orig_contract_line_id;
1817 --END IF;
1818
1819 -- fill the loan level funding part from one off fee table
1820 l_loan_level_counter := l_loan_level_counter + 1;
1821
1822 l_csm_loan_levels_tbl(l_loan_level_counter).description := x_csm_one_off_fee_tbl(i).description;
1823 l_csm_loan_levels_tbl(l_loan_level_counter).date_start := x_csm_one_off_fee_tbl(i).date_paid;
1824 l_csm_loan_levels_tbl(l_loan_level_counter).kle_loan_id := x_csm_one_off_fee_tbl(i).kle_fee_id;
1825
1826 --sechawla 17-Jul-09 : PRB ESG Enhancements : assign orig_contract_line_id
1827 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' THEN
1828 l_csm_loan_levels_tbl(l_loan_level_counter).orig_contract_line_id := x_csm_one_off_fee_tbl(i).orig_contract_line_id;
1829 --END IF;
1830
1831 l_csm_loan_levels_tbl(l_loan_level_counter).level_index_number := 1;
1832 l_csm_loan_levels_tbl(l_loan_level_counter).level_type := G_SFE_LEVEL_FUNDING;
1833 l_csm_loan_levels_tbl(l_loan_level_counter).amount := x_csm_one_off_fee_tbl(i).amount;
1834 l_csm_loan_levels_tbl(l_loan_level_counter).income_or_expense := x_csm_one_off_fee_tbl(i).income_or_expense;
1835
1836 -- get the matching row in periodic expense table
1837 l_periodic_expense_count := x_csm_periodic_expenses_tbl.last;
1838 l_periodic_expense_counter := x_csm_periodic_expenses_tbl.first;
1839 loop
1840 IF x_csm_periodic_expenses_tbl(l_periodic_expense_counter).kle_fee_id = x_csm_one_off_fee_tbl(i).kle_fee_id THEN
1841
1842 -- fill the loan level payment part from periodic expense table
1843 l_loan_level_counter := l_loan_level_counter + 1;
1844 l_csm_loan_levels_tbl(l_loan_level_counter).description := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).description;
1845 l_csm_loan_levels_tbl(l_loan_level_counter).date_start := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).date_start;
1846 l_csm_loan_levels_tbl(l_loan_level_counter).kle_loan_id := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).kle_fee_id;
1847
1848 --sechawla 17-Jul-09 : PRB ESG Enhancements : assign orig_contract_line_id
1849 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' THEN
1850 l_csm_loan_levels_tbl(l_loan_level_counter).orig_contract_line_id := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).orig_contract_line_id;
1851 --END IF;
1852
1853 l_csm_loan_levels_tbl(l_loan_level_counter).level_index_number := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).level_index_number + 1;
1854
1855 l_csm_loan_levels_tbl(l_loan_level_counter).level_type := G_SFE_LEVEL_PAYMENT;
1856 l_csm_loan_levels_tbl(l_loan_level_counter).number_of_periods := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).number_of_periods;
1857 l_csm_loan_levels_tbl(l_loan_level_counter).amount := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).amount;
1858 l_csm_loan_levels_tbl(l_loan_level_counter).lock_level_step := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).lock_level_step;
1859 --l_csm_loan_levels_tbl(l_loan_level_counter).rate := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).rate;
1860 l_csm_loan_levels_tbl(l_loan_level_counter).period := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).period;
1861 l_csm_loan_levels_tbl(l_loan_level_counter).advance_or_arrears := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).advance_or_arrears;
1862 l_csm_loan_levels_tbl(l_loan_level_counter).income_or_expense := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).income_or_expense;
1863 l_csm_loan_levels_tbl(l_loan_level_counter).structure := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).structure;
1864 l_csm_loan_levels_tbl(l_loan_level_counter).query_level_yn := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).query_level_yn;
1865
1866 -- delete processed rows from periodic expense table
1867 x_csm_periodic_expenses_tbl.delete(l_periodic_expense_counter);
1868 END IF;
1869
1870 IF l_periodic_expense_counter = l_periodic_expense_count then
1871 EXIT;
1872 ELSE
1873 l_periodic_expense_counter := x_csm_periodic_expenses_tbl.next(l_periodic_expense_counter);
1874 END IF;
1875
1876 End loop;
1877
1878 -- delete processed rows from one off fee
1879 x_csm_one_off_fee_tbl.DELETE(i);
1880 END IF;
1881 END LOOP;
1882
1883 IF (l_rec_count > 0) THEN
1884
1885 -- adjust the indices for one off AND perodic expence tables
1886
1887 adjust_index_one_off_fee(x_csm_one_off_fee_tbl);
1888 adjust_index_periodic_expense(x_csm_periodic_expenses_tbl);
1889
1890 IF l_csm_loan_lines_tbl IS NOT NULL THEN
1891
1892 insert_loan_lines(p_api_version => p_api_version,
1893 p_init_msg_list => p_init_msg_list,
1894 p_sif_id => p_sif_id,
1895 p_csm_loan_lines_tbl => l_csm_loan_lines_tbl,
1896 x_return_status => l_return_status,
1897 x_msg_count => x_msg_count,
1898 x_msg_data => x_msg_data);
1899
1900 IF l_return_status = G_RET_STS_ERROR THEN
1901 RAISE G_EXCEPTION_ERROR;
1902 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1903
1904
1905 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1906 END IF;
1907 END IF;
1908
1909 IF l_csm_loan_levels_tbl IS NOT NULL THEN
1910 lp_sfev_loan_levels_tbl := assign_loan_levels(p_sif_id => p_sif_id,
1911 p_csm_loan_levels_tbl => l_csm_loan_levels_tbl,
1912 x_return_status => l_return_status
1913 );
1914
1915 IF l_return_status = G_RET_STS_ERROR THEN
1916 RAISE G_EXCEPTION_ERROR;
1917 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1918 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1919 END IF;
1920 Okl_Sif_Fees_Pub.insert_sif_fees(
1921 p_api_version => p_api_version
1922 ,p_init_msg_list => p_init_msg_list
1923
1924 ,x_return_status => l_return_status
1925
1926 ,x_msg_count => x_msg_count
1927 ,x_msg_data => x_msg_data
1928 ,p_sfev_tbl => lp_sfev_loan_levels_tbl
1929 ,x_sfev_tbl => lx_sfev_loan_levels_tbl);
1930
1931
1932 IF l_return_status = G_RET_STS_ERROR THEN
1933 RAISE G_EXCEPTION_ERROR;
1934 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1935 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1936
1937 END IF;
1938 END IF;
1939 END IF;
1940
1941 EXCEPTION
1942 WHEN G_EXCEPTION_ERROR THEN
1943 x_return_status := G_RET_STS_ERROR;
1944 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1945 x_return_status := G_RET_STS_UNEXP_ERROR;
1946 WHEN OTHERS THEN
1947 -- store SQL error message on message stack
1948 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1949 p_msg_name => G_UNEXPECTED_ERROR,
1950 p_token1 => G_SQLCODE_TOKEN,
1951 p_token1_value => SQLCODE,
1952 p_token2 => G_SQLERRM_TOKEN,
1953 p_token2_value => SQLERRM);
1954 x_return_status := G_RET_STS_UNEXP_ERROR;
1955
1956 END insert_rollover_fee_for_lease;
1957
1958
1959
1960
1961
1962 /*=========================================================================================+
1963 | -- PROCEDURE insert_finance_fee_for_lease |
1964 | -- This procedure takes care of inserting finance fee loan lines with in |
1965 | -- a lease. It checks for the fee type 'FINACED' to identify finance fee. |
1966 | -- Assigns and Inserts Loan Line AND LOAN LEVEL details for this Contract |
1967 +==========================================================================================*/
1968
1969 PROCEDURE insert_finance_fee_for_lease(
1970 p_api_version IN NUMBER
1971 ,p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
1972 ,p_sif_id IN NUMBER
1973 ,p_csm_one_off_fee_tbl IN csm_one_off_fee_tbl_type --this should have orig_contract_line_id populated
1974 ,p_csm_periodic_expenses_tbl IN csm_periodic_expenses_tbl_type
1975 ,x_csm_one_off_fee_tbl OUT NOCOPY csm_one_off_fee_tbl_type
1976 ,x_csm_periodic_expenses_tbl OUT NOCOPY csm_periodic_expenses_tbl_type
1977 ,x_return_status OUT NOCOPY VARCHAR2
1978 ,x_msg_count OUT NOCOPY NUMBER
1979 ,x_msg_data OUT NOCOPY VARCHAR2
1980 ) IS
1981
1982 lp_silv_rec silv_rec_type;
1983 lx_silv_rec silv_rec_type;
1984 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1985 l_csm_loan_lines_tbl csm_loan_line_tbl_type;
1986 l_csm_loan_levels_tbl csm_loan_level_tbl_type;
1987
1988 lp_sfev_loan_levels_tbl sfev_tbl_type;
1989 lx_sfev_loan_levels_tbl sfev_tbl_type;
1990
1991 l_index NUMBER := -1;
1992 l_periodic_expense_count NUMBER:=0;
1993 l_periodic_expense_counter NUMBER:=0;
1994 l_loan_line_counter NUMBER:=0;
1995 l_loan_level_counter NUMBER:=0;
1996 l_rec_count NUMBER:=0;
1997
1998 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
1999 cursor get_rebook_type is
2000 select nvl(amort_inc_adj_rev_dt_yn, 'N')
2001 from okl_sys_acct_opts;
2002
2003 l_PROSPECTIVE_REBOOK_YN VARCHAR2(1);
2004
2005
2006 BEGIN
2007
2008 l_return_status := G_RET_STS_SUCCESS;
2009
2010
2011
2012 -- assgning the input structures to local structures
2013 x_csm_one_off_fee_tbl := p_csm_one_off_fee_tbl;
2014
2015 x_csm_periodic_expenses_tbl := p_csm_periodic_expenses_tbl;
2016
2017 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
2018 OPEN get_rebook_type ;
2019 FETCH get_rebook_type INTO l_PROSPECTIVE_REBOOK_YN;
2020 CLOSE get_rebook_type;
2021
2022 FOR i IN 1..x_csm_one_off_fee_tbl.COUNT
2023 LOOP
2024 IF x_csm_one_off_fee_tbl(i).fee_type in(okl_maintain_fee_pvt.G_FT_FINANCED,'ROLLOVER') THEN
2025 l_rec_count := l_rec_count + 1;
2026
2027 -- fill the loan line table
2028 l_loan_line_counter := l_loan_line_counter + 1;
2029 l_csm_loan_lines_tbl(l_loan_line_counter).kle_loan_id := x_csm_one_off_fee_tbl(i).kle_fee_id;
2030
2031 --sechawla 17-Jul-09 : PRB ESG Enhancements : assign orig_contract_line_id
2032 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' THEN
2033 l_csm_loan_lines_tbl(l_loan_line_counter).orig_contract_line_id := x_csm_one_off_fee_tbl(i).orig_contract_line_id;
2034 --END IF;
2035 -- fill the loan level funding part from one off fee table
2036 l_loan_level_counter := l_loan_level_counter + 1;
2037
2038 l_csm_loan_levels_tbl(l_loan_level_counter).description := x_csm_one_off_fee_tbl(i).description;
2039 l_csm_loan_levels_tbl(l_loan_level_counter).date_start := x_csm_one_off_fee_tbl(i).date_paid;
2040 l_csm_loan_levels_tbl(l_loan_level_counter).kle_loan_id := x_csm_one_off_fee_tbl(i).kle_fee_id;
2041
2042 --sechawla 17-Jul-09 : PRb ESG Enhancements : assign orig_contract_line_id
2043 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' THEN
2044 l_csm_loan_levels_tbl(l_loan_level_counter).orig_contract_line_id := x_csm_one_off_fee_tbl(i).orig_contract_line_id;
2045 --END IF;
2046
2047 l_csm_loan_levels_tbl(l_loan_level_counter).level_index_number := 1;
2048 l_csm_loan_levels_tbl(l_loan_level_counter).level_type := G_SFE_LEVEL_FUNDING;
2049 l_csm_loan_levels_tbl(l_loan_level_counter).amount := x_csm_one_off_fee_tbl(i).amount;
2050 l_csm_loan_levels_tbl(l_loan_level_counter).income_or_expense := x_csm_one_off_fee_tbl(i).income_or_expense;
2051
2052 -- Bug 14089534 fix. Vsgandhi
2053 If x_csm_periodic_expenses_tbl.count >0 Then
2054
2055 -- get the matching row in periodic expense table
2056 l_periodic_expense_count := x_csm_periodic_expenses_tbl.last;
2057 l_periodic_expense_counter := x_csm_periodic_expenses_tbl.first;
2058 loop
2059 IF x_csm_periodic_expenses_tbl(l_periodic_expense_counter).kle_fee_id = x_csm_one_off_fee_tbl(i).kle_fee_id THEN
2060
2061 -- fill the loan level payment part from periodic expense table
2062 l_loan_level_counter := l_loan_level_counter + 1;
2063 l_csm_loan_levels_tbl(l_loan_level_counter).description := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).description;
2064 l_csm_loan_levels_tbl(l_loan_level_counter).date_start := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).date_start;
2065 l_csm_loan_levels_tbl(l_loan_level_counter).kle_loan_id := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).kle_fee_id;
2066
2067 --sechawla 17-Jul-09 : PRb ESG Enhancements : assign orig_contract_line_id
2068 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' THEN
2069 l_csm_loan_levels_tbl(l_loan_level_counter).orig_contract_line_id := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).orig_contract_line_id;
2070 --END IF;
2071 l_csm_loan_levels_tbl(l_loan_level_counter).level_index_number := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).level_index_number + 1;
2072
2073 l_csm_loan_levels_tbl(l_loan_level_counter).level_type := G_SFE_LEVEL_PAYMENT;
2074 l_csm_loan_levels_tbl(l_loan_level_counter).number_of_periods := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).number_of_periods;
2075 l_csm_loan_levels_tbl(l_loan_level_counter).amount := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).amount;
2076 l_csm_loan_levels_tbl(l_loan_level_counter).lock_level_step := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).lock_level_step;
2077 --l_csm_loan_levels_tbl(l_loan_level_counter).rate := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).rate;
2078 l_csm_loan_levels_tbl(l_loan_level_counter).period := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).period;
2079 l_csm_loan_levels_tbl(l_loan_level_counter).advance_or_arrears := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).advance_or_arrears;
2080 l_csm_loan_levels_tbl(l_loan_level_counter).income_or_expense := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).income_or_expense;
2081 l_csm_loan_levels_tbl(l_loan_level_counter).structure := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).structure;
2082 l_csm_loan_levels_tbl(l_loan_level_counter).query_level_yn := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).query_level_yn;
2083
2084 -- delete processed rows from periodic expense table
2085 x_csm_periodic_expenses_tbl.delete(l_periodic_expense_counter);
2086 END IF;
2087
2088 IF l_periodic_expense_counter = l_periodic_expense_count then
2089 EXIT;
2090 ELSE
2091 l_periodic_expense_counter := x_csm_periodic_expenses_tbl.next(l_periodic_expense_counter);
2092 END IF;
2093
2094 End loop;
2095 END IF; -- vsgandhi
2096 -- delete processed rows from one off fee
2097 x_csm_one_off_fee_tbl.DELETE(i);
2098 END IF;
2099 END LOOP;
2100
2101 IF (l_rec_count > 0) THEN
2102
2103 -- adjust the indices for one off AND perodic expence tables
2104 adjust_index_one_off_fee(x_csm_one_off_fee_tbl);
2105 adjust_index_periodic_expense(x_csm_periodic_expenses_tbl);
2106
2107 IF l_csm_loan_lines_tbl IS NOT NULL THEN
2108
2109 insert_loan_lines(p_api_version => p_api_version, --come back here
2110 p_init_msg_list => p_init_msg_list,
2111 p_sif_id => p_sif_id,
2112 p_csm_loan_lines_tbl => l_csm_loan_lines_tbl,
2113 x_return_status => l_return_status,
2114 x_msg_count => x_msg_count,
2115 x_msg_data => x_msg_data);
2116
2117 /*insert into err_msgs_log values ('insert_loan_lines x_return_status'||x_return_status);
2118 commit;
2119 */
2120 IF l_return_status = G_RET_STS_ERROR THEN
2121 RAISE G_EXCEPTION_ERROR;
2122
2123 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2124 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2125 END IF;
2126 END IF;
2127
2128 IF l_csm_loan_levels_tbl IS NOT NULL THEN
2129 lp_sfev_loan_levels_tbl := assign_loan_levels(p_sif_id => p_sif_id, --here 333
2130 p_csm_loan_levels_tbl => l_csm_loan_levels_tbl,
2131 x_return_status => l_return_status
2132
2133 );
2134
2135 /*
2136 insert into err_msgs_log values ('assign_loan_levels x_return_status'||x_return_status);
2137 commit;
2138 */
2139 IF l_return_status = G_RET_STS_ERROR THEN
2140 RAISE G_EXCEPTION_ERROR;
2141 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2142 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2143 END IF;
2144 Okl_Sif_Fees_Pub.insert_sif_fees(
2145 p_api_version => p_api_version
2146
2147 ,p_init_msg_list => p_init_msg_list
2148
2149 ,x_return_status => l_return_status
2150
2151 ,x_msg_count => x_msg_count
2152 ,x_msg_data => x_msg_data
2153 ,p_sfev_tbl => lp_sfev_loan_levels_tbl
2154 ,x_sfev_tbl => lx_sfev_loan_levels_tbl);
2155
2156
2157 /*insert into err_msgs_log values ('Okl_Sif_Fees_Pub.insert_sif_fees x_return_status'||x_return_status);
2158 commit;
2159 */
2160
2161 IF l_return_status = G_RET_STS_ERROR THEN
2162 RAISE G_EXCEPTION_ERROR;
2163 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2164 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2165
2166 END IF;
2167 END IF;
2168 END IF;
2169
2170 EXCEPTION
2171 WHEN G_EXCEPTION_ERROR THEN
2172 x_return_status := G_RET_STS_ERROR;
2173 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2174 x_return_status := G_RET_STS_UNEXP_ERROR;
2175 WHEN OTHERS THEN
2176 -- store SQL error message on message stack
2177 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
2178 p_msg_name => G_UNEXPECTED_ERROR,
2179 p_token1 => G_SQLCODE_TOKEN,
2180 p_token1_value => SQLCODE,
2181 p_token2 => G_SQLERRM_TOKEN,
2182 p_token2_value => SQLERRM);
2183 x_return_status := G_RET_STS_UNEXP_ERROR;
2184
2185 END insert_finance_fee_for_lease;
2186
2187
2188 /*========================================================================================+
2189 | -- PROCEDURE insert_finance_fee_for_loan |
2190 | -- This procedure takes care of inserting finance fee loan lines with in |
2191 | -- a loan. It checks for the fee type 'FINACED' to identify finance fee. |
2192 | -- Assigns and Inserts Loan Line AND LOAN LEVEL details for this Contract |
2193 +=========================================================================================*/
2194
2195 PROCEDURE insert_finance_fee_for_loan(
2196 p_api_version IN NUMBER
2197 ,p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
2198 ,p_sif_id IN NUMBER
2199 ,p_csm_one_off_fee_tbl IN csm_one_off_fee_tbl_type
2200 ,p_csm_periodic_expenses_tbl IN csm_periodic_expenses_tbl_type
2201 ,p_csm_loan_lines_tbl IN csm_loan_line_tbl_type
2202 ,p_csm_loan_levels_tbl IN csm_loan_level_tbl_type
2203 ,x_csm_one_off_fee_tbl OUT NOCOPY csm_one_off_fee_tbl_type
2204 ,x_csm_periodic_expenses_tbl OUT NOCOPY csm_periodic_expenses_tbl_type
2205 ,x_csm_loan_lines_tbl OUT NOCOPY csm_loan_line_tbl_type
2206 ,x_csm_loan_levels_tbl OUT NOCOPY csm_loan_level_tbl_type
2207 ,x_return_status OUT NOCOPY VARCHAR2
2208 ,x_msg_count OUT NOCOPY NUMBER
2209
2210 ,x_msg_data OUT NOCOPY VARCHAR2
2211 ) IS
2212
2213 lp_silv_rec silv_rec_type;
2214 lx_silv_rec silv_rec_type;
2215 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2216
2217 lp_sfev_loan_levels_tbl sfev_tbl_type;
2218 lx_sfev_loan_levels_tbl sfev_tbl_type;
2219
2220 l_index NUMBER := -1;
2221 l_periodic_expense_count NUMBER:=0;
2222 l_periodic_expense_counter NUMBER:=0;
2223 l_loan_line_counter NUMBER := p_csm_loan_lines_tbl.LAST;
2224 l_loan_level_counter NUMBER := p_csm_loan_levels_tbl.LAST;
2225
2226 l_rec_count NUMBER:=0;
2227
2228 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
2229 cursor get_rebook_type is
2230 select nvl(amort_inc_adj_rev_dt_yn, 'N')
2231 from okl_sys_acct_opts;
2232
2233 l_PROSPECTIVE_REBOOK_YN VARCHAR2(1);
2234
2235 BEGIN
2236 l_return_status := G_RET_STS_SUCCESS;
2237
2238
2239
2240 -- assgning the input structures to local structures
2241 x_csm_one_off_fee_tbl := p_csm_one_off_fee_tbl;
2242 x_csm_periodic_expenses_tbl := p_csm_periodic_expenses_tbl;
2243
2244 x_csm_loan_lines_tbl := p_csm_loan_lines_tbl;
2245 x_csm_loan_levels_tbl := p_csm_loan_levels_tbl;
2246
2247 -- sechawla 20-Jul-09 PRB ESG Enhancements : check rebook option
2248 OPEN get_rebook_type ;
2249 FETCh get_rebook_type INTO l_PROSPECTIVE_REBOOK_YN;
2250 CLOSE get_rebook_type;
2251
2252
2253 FOR i IN 1..x_csm_one_off_fee_tbl.COUNT
2254 LOOP
2255
2256 IF x_csm_one_off_fee_tbl(i).fee_type in(okl_maintain_fee_pvt.G_FT_FINANCED,'ROLLOVER') THEN
2257 l_rec_count := l_rec_count + 1;
2258
2259 -- fill the loan line table
2260 l_loan_line_counter := l_loan_line_counter + 1;
2261 x_csm_loan_lines_tbl(l_loan_line_counter).kle_loan_id := x_csm_one_off_fee_tbl(i).kle_fee_id;
2262
2263 --sechawla 24-JUL-09 PRB ESG Enhancements : populate orig_contract_line_id
2264 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' THEN
2265
2266 --sechawla 12-aug-09 8788914 : changed table index l_loan_level_counter to l_loan_line_counter
2267 x_csm_loan_lines_tbl(l_loan_line_counter).orig_contract_line_id := x_csm_one_off_fee_tbl(i).orig_contract_line_id;
2268 --END IF;
2269
2270 -- fill the loan level funding part from one off fee table
2271 l_loan_level_counter := l_loan_level_counter + 1;
2272
2273
2274 x_csm_loan_levels_tbl(l_loan_level_counter).description := x_csm_one_off_fee_tbl(i).description;
2275 x_csm_loan_levels_tbl(l_loan_level_counter).date_start := x_csm_one_off_fee_tbl(i).date_paid;
2276
2277 x_csm_loan_levels_tbl(l_loan_level_counter).kle_loan_id := x_csm_one_off_fee_tbl(i).kle_fee_id;
2278
2279 --sechawla 15-JUL-09 PRB ESG Enhancements : populate orig_contract_line_id
2280 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' THEN
2281 x_csm_loan_levels_tbl(l_loan_level_counter).orig_contract_line_id := x_csm_one_off_fee_tbl(i).orig_contract_line_id;
2282 --END IF;
2283
2284
2285 x_csm_loan_levels_tbl(l_loan_level_counter).level_index_number := 1;
2286 x_csm_loan_levels_tbl(l_loan_level_counter).level_type := G_SFE_LEVEL_FUNDING;
2287 x_csm_loan_levels_tbl(l_loan_level_counter).amount := x_csm_one_off_fee_tbl(i).amount;
2288
2289 x_csm_loan_levels_tbl(l_loan_level_counter).income_or_expense := x_csm_one_off_fee_tbl(i).income_or_expense;
2290 -- get the matching row in periodic expense table
2291 l_periodic_expense_count := x_csm_periodic_expenses_tbl.last;
2292 l_periodic_expense_counter := x_csm_periodic_expenses_tbl.first;
2293 loop
2294 IF x_csm_periodic_expenses_tbl(l_periodic_expense_counter).kle_fee_id = x_csm_one_off_fee_tbl(i).kle_fee_id THEN
2295 -- fill the loan level payment part from periodic expense table
2296 l_loan_level_counter := l_loan_level_counter + 1;
2297 x_csm_loan_levels_tbl(l_loan_level_counter).description := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).description;
2298 x_csm_loan_levels_tbl(l_loan_level_counter).date_start := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).date_start;
2299 x_csm_loan_levels_tbl(l_loan_level_counter).kle_loan_id := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).kle_fee_id;
2300
2301 --sechawla 15-JUL-09 PRB ESG Enhancements : populate orig_contract_line_id
2302 --IF l_PROSPECTIVE_REBOOK_YN = 'Y' THEN
2303 x_csm_loan_levels_tbl(l_loan_level_counter).orig_contract_line_id := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).orig_contract_line_id;
2304 --END IF;
2305
2306 x_csm_loan_levels_tbl(l_loan_level_counter).level_index_number := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).level_index_number + 1;
2307 x_csm_loan_levels_tbl(l_loan_level_counter).level_type := G_SFE_LEVEL_PAYMENT;
2308 x_csm_loan_levels_tbl(l_loan_level_counter).number_of_periods := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).number_of_periods;
2309 x_csm_loan_levels_tbl(l_loan_level_counter).amount := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).amount;
2310 --x_csm_loan_levels_tbl(l_loan_level_counter).lock_level_step := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).lock_level_step;
2311 x_csm_loan_levels_tbl(l_loan_level_counter).lock_level_step := 'AMOUNT';
2312 --l_csm_loan_levels_tbl(l_loan_level_counter).rate := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).rate;
2313 x_csm_loan_levels_tbl(l_loan_level_counter).period := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).period;
2314 x_csm_loan_levels_tbl(l_loan_level_counter).advance_or_arrears := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).advance_or_arrears;
2315 x_csm_loan_levels_tbl(l_loan_level_counter).income_or_expense := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).income_or_expense;
2316 x_csm_loan_levels_tbl(l_loan_level_counter).structure := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).structure;
2317 x_csm_loan_levels_tbl(l_loan_level_counter).query_level_yn := x_csm_periodic_expenses_tbl(l_periodic_expense_counter).query_level_yn;
2318 -- delete processed rows from periodic expense table
2319 x_csm_periodic_expenses_tbl.delete(l_periodic_expense_counter);
2320 END IF;
2321
2322 IF l_periodic_expense_counter = l_periodic_expense_count then
2323 EXIT;
2324 ELSE
2325 l_periodic_expense_counter := x_csm_periodic_expenses_tbl.next(l_periodic_expense_counter);
2326
2327 END IF;
2328 End loop;
2329
2330 -- delete processed rows from one off fee
2331 x_csm_one_off_fee_tbl.DELETE(i);
2332 END IF;
2333 END LOOP;
2334 IF (l_rec_count > 0) THEN
2335
2336 -- adjust the indices for one off AND perodic expence tables.
2337 adjust_index_one_off_fee(x_csm_one_off_fee_tbl);
2338 adjust_index_periodic_expense(x_csm_periodic_expenses_tbl);
2339
2340 END IF;
2341 x_return_status := l_return_status;
2342
2343 EXCEPTION
2344 WHEN G_EXCEPTION_ERROR THEN
2345 x_return_status := G_RET_STS_ERROR;
2346 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
2347 x_return_status := G_RET_STS_UNEXP_ERROR;
2348 WHEN OTHERS THEN
2349
2350 -- store SQL error message on message stack
2351 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
2352 p_msg_name => G_UNEXPECTED_ERROR,
2353 p_token1 => G_SQLCODE_TOKEN,
2354 p_token1_value => SQLCODE,
2355 p_token2 => G_SQLERRM_TOKEN,
2356 p_token2_value => SQLERRM);
2357 x_return_status := G_RET_STS_UNEXP_ERROR;
2358
2359 END insert_finance_fee_for_loan;
2360
2361 PROCEDURE Update_Pricing_Param (
2362 p_api_version IN NUMBER
2363 ,p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
2364 ,p_trans_id IN NUMBER
2365 ,x_sif_id OUT NOCOPY NUMBER
2366 ,x_khr_id OUT NOCOPY NUMBER
2367 ,x_return_status OUT NOCOPY VARCHAR2
2368 ,x_msg_count OUT NOCOPY NUMBER
2369 ,x_msg_data OUT NOCOPY VARCHAR2
2370 )
2371 IS
2372
2373 CURSOR update_sif_id_csr(p_trans_id NUMBER)
2374 IS
2375 SELECT id, khr_id
2376 FROM okl_stream_interfaces
2377 WHERE transaction_number = p_trans_id;
2378
2379 l_sif_id NUMBER;
2380 l_khr_id NUMBER;
2381 BEGIN
2382 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2383
2384 OPEN update_sif_id_csr(p_trans_id);
2385 FETCH update_sif_id_csr into l_sif_id, l_khr_id;
2386 IF update_sif_id_csr%NOTFOUND THEN
2387 okl_api.set_message(p_app_name => G_APP_NAME,
2388 p_msg_name => 'Transaction Number Not Found');
2389
2390 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2391 END IF;
2392
2393 CLOSE update_sif_id_csr;
2394
2395 IF (l_sif_id IS NOT NULL) AND (l_khr_id IS NOT NULL) THEN
2396 UPDATE OKL_SIF_PRICING_PARAMS
2397 SET SIF_ID = l_sif_id
2398 WHERE SIF_ID IS NULL AND KHR_ID = l_khr_id;
2399 x_sif_id := l_sif_id;
2400 x_khr_id := l_khr_id;
2401 END IF;
2402
2403 EXCEPTION
2404 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2405 IF update_sif_id_csr%ISOPEN THEN
2406 CLOSE update_sif_id_csr;
2407 END IF;
2408 x_return_status := Okl_Api.G_RET_STS_ERROR ;
2409
2410 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2411 IF update_sif_id_csr%ISOPEN THEN
2412 CLOSE update_sif_id_csr;
2413 END IF;
2414 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2415
2416 WHEN OTHERS THEN
2417 IF update_sif_id_csr%ISOPEN THEN
2418 CLOSE update_sif_id_csr;
2419 END IF;
2420 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
2421
2422
2423 END;
2424
2425
2426
2427
2428 ---------------------------------------------------------------------------
2429 -- PROCEDURE Create_Streams_Lease_Book
2430 ---------------------------------------------------------------------------
2431 PROCEDURE Create_Streams_Lease_Book (
2432
2433 p_api_version IN NUMBER
2434 ,p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
2435
2436 ,p_skip_prc_engine IN VARCHAR2 DEFAULT G_FALSE
2437 ,p_csm_lease_header IN csm_lease_rec_type
2438 ,p_csm_one_off_fee_tbl IN csm_one_off_fee_tbl_type
2439 ,p_csm_periodic_expenses_tbl IN csm_periodic_expenses_tbl_type
2440 ,p_csm_yields_tbl IN csm_yields_tbl_type
2441 ,p_csm_stream_types_tbl IN csm_stream_types_tbl_type
2442 ,p_csm_line_details_tbl IN csm_line_details_tbl_type
2443 ,p_rents_tbl IN csm_periodic_expenses_tbl_type
2444 ,x_trans_id OUT NOCOPY NUMBER
2445 ,x_trans_status OUT NOCOPY VARCHAR2
2446 ,x_return_status OUT NOCOPY VARCHAR2
2447 ,x_msg_count OUT NOCOPY NUMBER
2448 ,x_msg_data OUT NOCOPY VARCHAR2
2449 )
2450 IS
2451 l_api_name CONSTANT VARCHAR2(30) := 'Create_Streams_Lease_Book';
2452 lp_sifv_rec sifv_rec_type;
2453 lx_sifv_rec sifv_rec_type;
2454 lx_sifv_status_rec sifv_rec_type;
2455 lp_sfev_rent_tbl sfev_tbl_type;
2456
2457 lx_sfev_rent_tbl sfev_tbl_type;
2458 lp_sfev_one_off_tbl sfev_tbl_type;
2459 lx_sfev_one_off_tbl sfev_tbl_type;
2460
2461 lp_sfev_periodic_tbl sfev_tbl_type;
2462 lx_sfev_periodic_tbl sfev_tbl_type;
2463 lp_siyv_tbl siyv_tbl_type;
2464 lx_siyv_tbl siyv_tbl_type;
2465 lp_sitv_tbl sitv_tbl_type;
2466 lx_sitv_tbl sitv_tbl_type;
2467 -- new structures for finance fee
2468
2469 x_csm_one_off_fee_tbl csm_one_off_fee_tbl_type;
2470 x_csm_periodic_expenses_tbl csm_periodic_expenses_tbl_type;
2471 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2472 l_api_version CONSTANT NUMBER := 1;
2473 l_sif_id NUMBER;
2474 l_khr_id NUMBER;
2475 l_pending BOOLEAN := FALSE;
2476 ---------------------------------------------------------------------------
2477 -- FUNCTION pending_request_exists
2478 -- Checks if any request is pending for the specified ContractNumber
2479 ---------------------------------------------------------------------------
2480 FUNCTION pending_request_exists(
2481 p_khr_id IN NUMBER
2482 --smahapat 11/10/02 multi-gaap - addition
2483 ,p_purpose_code IN OKL_STREAM_INTERFACES_V.PURPOSE_CODE%TYPE
2484 --smahapat addition end
2485 ,x_return_status OUT NOCOPY VARCHAR2
2486 ) RETURN BOOLEAN
2487 IS
2488 CURSOR l_okl_sif_status_csr(p_khr_id IN OKL_STREAM_INTERFACES_V.KHR_ID%TYPE) IS
2489 SELECT '1' FROM dual
2490 WHERE EXISTS
2491 (SELECT '1'
2492 FROM OKL_STREAM_INTERFACES
2493 WHERE khr_id = p_khr_id
2494 AND SIS_CODE IN (G_SIS_HDR_INSERTED, G_SIS_DATA_ENTERED, G_SIS_PROCESSING_REQUEST,G_SIS_RET_DATA_RECEIVED)
2495 );
2496 --smahapat 11/10/02 multi-gaap - addition
2497 CURSOR l_okl_sif_rpt_status_csr(p_khr_id IN OKL_STREAM_INTERFACES_V.KHR_ID%TYPE) IS
2498
2499 SELECT '1' FROM dual
2500 WHERE EXISTS
2501 (SELECT '1'
2502 FROM OKL_STREAM_INTERFACES
2503 WHERE khr_id = p_khr_id AND purpose_code = G_PURPOSE_CODE_REPORT
2504 AND SIS_CODE IN (G_SIS_HDR_INSERTED, G_SIS_DATA_ENTERED, G_SIS_PROCESSING_REQUEST,G_SIS_RET_DATA_RECEIVED)
2505 );
2506 --smahapat addition end
2507 l_pending BOOLEAN DEFAULT FALSE;
2508 BEGIN
2509 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
2510 IF p_khr_id IS NOT NULL THEN
2511 --smahapat 11/10/02 multi-gaap - addition
2512 IF p_purpose_code IS NOT NULL AND p_purpose_code = G_PURPOSE_CODE_REPORT THEN
2513 FOR l_sif_rpt_csr IN l_okl_sif_rpt_status_csr(p_khr_id)
2514 LOOP
2515 l_pending := TRUE;
2516 END LOOP;
2517 ELSE
2518 --smahapat addition end
2519 FOR l_sif_csr IN l_okl_sif_status_csr(p_khr_id)
2520 LOOP
2521 l_pending := TRUE;
2522
2523 END LOOP;
2524 END IF; --smahapat 11/10/02 multi-gaap - addition
2525 END IF;
2526 RETURN(l_pending);
2527 EXCEPTION
2528 WHEN OTHERS THEN
2529 -- store SQL error message on message stack
2530 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
2531 p_msg_name => G_UNEXPECTED_ERROR,
2532 p_token1 => G_SQLCODE_TOKEN,
2533
2534 p_token1_value => SQLCODE,
2535 p_token2 => G_SQLERRM_TOKEN,
2536 p_token2_value => SQLERRM);
2537
2538 x_return_status := G_RET_STS_UNEXP_ERROR;
2539 RETURN NULL;
2540 END pending_request_exists;
2541 BEGIN
2542 --Added by kthiruva for Logging Purposes
2543 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2544 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inside procedure create_streams_lease_book');
2545 END IF;
2546 l_return_status := G_RET_STS_SUCCESS;
2547 initialize;
2548
2549
2550 -- Check for any pending request for this Contract and
2551 -- Error out if there does exist a request that is not completed
2552 l_pending := pending_request_exists(p_khr_id => p_csm_lease_header.khr_id
2553 --smahapat 11/10/02 multi-gaap - addition
2554 ,p_purpose_code => p_csm_lease_header.purpose_code
2555 ,x_return_status => l_return_status);
2556 --Added by kthiruva
2557 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2558 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to pending_request_exists, the return status is :'||l_return_status);
2559 END IF;
2560 IF l_return_status = G_RET_STS_ERROR THEN
2561 RAISE G_EXCEPTION_ERROR;
2562 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2563 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2564 END IF;
2565 IF(l_pending) THEN
2566 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
2567 p_msg_name => G_OKL_CSM_PENDING
2568
2569 );
2570 l_return_status := G_RET_STS_ERROR;
2571 RAISE G_EXCEPTION_ERROR;
2572 ELSE
2573 --Added by kthiruva for Debugging
2574 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2575 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Prior to the call to assign_header_details');
2576 END IF;
2577 /* assign Transaction Header Data */
2578 lp_sifv_rec := assign_header_details( p_lease_header_rec => p_csm_lease_header
2579 ,x_return_status => l_return_status
2580 );
2581 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2582 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to assign_header_details, the return status is :'||l_return_status);
2583 END IF;
2584 IF l_return_status = G_RET_STS_ERROR THEN
2585 RAISE G_EXCEPTION_ERROR;
2586 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2587 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2588 END IF;
2589 -- Insert Transaction Header Data
2590
2591 -- Start of wraper code generated automatically by Debug code generator for Okl_Stream_Interfaces_Pub.insert_stream_interfaces
2592 IF(L_DEBUG_ENABLED='Y') THEN
2593 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
2594 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
2595 END IF;
2596 IF(IS_DEBUG_PROCEDURE_ON) THEN
2597 BEGIN
2598 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Stream_Interfaces_Pub.insert_stream_interfaces ');
2599 END;
2600 END IF;
2601
2602 Okl_Stream_Interfaces_Pub.insert_stream_interfaces(
2603 p_api_version => p_api_version
2604 ,p_init_msg_list => p_init_msg_list
2605 ,x_return_status => l_return_status
2606 ,x_msg_count => x_msg_count
2607 ,x_msg_data => x_msg_data
2608 ,p_sifv_rec => lp_sifv_rec
2609 ,x_sifv_rec => lx_sifv_rec);
2610 --Added by kthiruva for Debugging
2611 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2612 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Stream_Interfaces_Pub.insert_stream_interfaces, return status is:'||l_return_status);
2613 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'The transaction number of the request is :'||lx_sifv_rec.transaction_number);
2614 END IF;
2615
2616 IF(IS_DEBUG_PROCEDURE_ON) THEN
2617 BEGIN
2618 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Stream_Interfaces_Pub.insert_stream_interfaces ');
2619 END;
2620 END IF;
2621
2622 -- End of wraper code generated automatically by Debug code generator for Okl_Stream_Interfaces_Pub.insert_stream_interfaces
2623 IF l_return_status = G_RET_STS_ERROR THEN
2624 RAISE G_EXCEPTION_ERROR;
2625 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2626 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2627 END IF;
2628 -- Get the Interface Header ID
2629
2630 l_sif_id := lx_sifv_rec.id;
2631 /* Assign line Level Transaction Details*/
2632
2633 IF p_csm_line_details_tbl IS NOT NULL THEN
2634 insert_asset_lines(p_api_version => p_api_version,
2635 p_init_msg_list => p_init_msg_list,
2636 p_sif_id => l_sif_id,
2637 p_csm_line_details_tbl => p_csm_line_details_tbl,
2638 x_return_status => l_return_status,
2639 x_msg_count => x_msg_count,
2640 x_msg_data => x_msg_data);
2641 --Added by kthiruva for Debugging
2642 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2643 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to insert_asset_lines, the return status is :'||l_return_status);
2644 END IF;
2645
2646 IF l_return_status = G_RET_STS_ERROR THEN
2647 RAISE G_EXCEPTION_ERROR;
2648 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2649 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2650 END IF;
2651
2652
2653 END IF;
2654
2655 /*Create Finance fee for lease booking*/
2656 IF p_csm_one_off_fee_tbl IS NOT NULL and p_csm_periodic_expenses_tbl IS NOT NULL THEN
2657 -- Bug 14089534 fix. vsgandhi
2658 -- bug 13381622
2659 -- If p_csm_periodic_expenses_tbl.count > 0 then
2660 insert_finance_fee_for_lease(p_api_version => p_api_version,
2661 p_init_msg_list => p_init_msg_list,
2662 p_sif_id => l_sif_id,
2663 p_csm_one_off_fee_tbl => p_csm_one_off_fee_tbl,
2664 p_csm_periodic_expenses_tbl => p_csm_periodic_expenses_tbl,
2665
2666 x_csm_one_off_fee_tbl => x_csm_one_off_fee_tbl,
2667 x_csm_periodic_expenses_tbl => x_csm_periodic_expenses_tbl,
2668 x_return_status => l_return_status,
2669 x_msg_count => x_msg_count,
2670 x_msg_data => x_msg_data);
2671 --Added by kthiruva for Debugging
2672 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2673 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to insert_finance_fee_for_lease, the return status is :'||l_return_status);
2674 END IF;
2675
2676
2677 IF l_return_status = G_RET_STS_ERROR THEN
2678 RAISE G_EXCEPTION_ERROR;
2679 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2680 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2681 END IF;
2682
2683 --End if;
2684 END IF;
2685
2686 /* Assign Rent Details*/
2687 IF p_rents_tbl IS NOT NULL THEN
2688 lp_sfev_rent_tbl := assign_rent_details(p_sif_id => l_sif_id,
2689 p_csm_periodic_expenses_tbl => p_rents_tbl,
2690 x_return_status => l_return_status
2691 );
2692 --Added by kthiruva for Debugging
2693 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2694 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to assign_rent_details, the return status is :'||l_return_status);
2695 END IF;
2696
2697 IF l_return_status = G_RET_STS_ERROR THEN
2698 RAISE G_EXCEPTION_ERROR;
2699 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2700
2701 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2702 END IF;
2703 -- Insert Rent Details
2704 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
2705 IF(IS_DEBUG_PROCEDURE_ON) THEN
2706
2707 BEGIN
2708 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
2709 END;
2710 END IF;
2711
2712
2713 Okl_Sif_Fees_Pub.insert_sif_fees(
2714 p_api_version => p_api_version
2715 ,p_init_msg_list => p_init_msg_list
2716 ,x_return_status => l_return_status
2717 ,x_msg_count => x_msg_count
2718 ,x_msg_data => x_msg_data
2719 ,p_sfev_tbl => lp_sfev_rent_tbl
2720 ,x_sfev_tbl => lx_sfev_rent_tbl);
2721 --Added by kthiruva for Debugging
2722 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2723 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Sif_Fees_Pub.insert_sif_fees, the return status is :'||l_return_status);
2724 END IF;
2725
2726 IF(IS_DEBUG_PROCEDURE_ON) THEN
2727 BEGIN
2728 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
2729 END;
2730 END IF;
2731
2732 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
2733
2734
2735 IF l_return_status = G_RET_STS_ERROR THEN
2736 RAISE G_EXCEPTION_ERROR;
2737 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2738
2739 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2740 END IF;
2741 END IF;
2742
2743 /* Assign One-Off Fee Details */
2744
2745 /*
2746 FOR i IN x_csm_one_off_fee_tbl.FIRST..x_csm_one_off_fee_tbl.LAST LOOP
2747 IF x_csm_one_off_fee_tbl(i).kle_asset_id IS NOT NULL OR
2748 x_csm_one_off_fee_tbl(i).kle_asset_id <> OKL_API.G_MISS_NUM THEN
2749 END IF;
2750 IF x_csm_one_off_fee_tbl(i).kle_fee_id IS NOT NULL OR
2751 x_csm_one_off_fee_tbl(i).kle_fee_id <> OKL_API.G_MISS_NUM THEN
2752 END IF;
2753 IF x_csm_one_off_fee_tbl(i).other_type_id IS NOT NULL OR
2754 x_csm_one_off_fee_tbl(i).other_type_id <> OKL_API.G_MISS_NUM THEN
2755 END IF;
2756 END LOOP;
2757 */
2758
2759 IF p_csm_one_off_fee_tbl IS NOT NULL THEN
2760
2761 lp_sfev_one_off_tbl := assign_one_off_fees(p_sif_id => l_sif_id,
2762 p_csm_one_off_fee_tbl => x_csm_one_off_fee_tbl,
2763 x_return_status => l_return_status
2764 );
2765 --Added by kthiruva for Debugging
2766 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2767 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to assign_one_off_fees, the return status is :'||l_return_status);
2768 END IF;
2769
2770
2771 IF l_return_status = G_RET_STS_ERROR THEN
2772
2773 RAISE G_EXCEPTION_ERROR;
2774 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2775 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2776 END IF;
2777
2778 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
2779 IF(IS_DEBUG_PROCEDURE_ON) THEN
2780 BEGIN
2781 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
2782 END;
2783 END IF;
2784
2785 Okl_Sif_Fees_Pub.insert_sif_fees(
2786
2787 p_api_version => p_api_version
2788 ,p_init_msg_list => p_init_msg_list
2789 ,x_return_status => l_return_status
2790 ,x_msg_count => x_msg_count
2791 ,x_msg_data => x_msg_data
2792 ,p_sfev_tbl => lp_sfev_one_off_tbl
2793 ,x_sfev_tbl => lx_sfev_one_off_tbl);
2794 --Added by kthiruva for Debugging
2795 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2796 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Sif_Fees_Pub.insert_sif_fees, the return status is :'||l_return_status);
2797 END IF;
2798
2799
2800
2801 IF(IS_DEBUG_PROCEDURE_ON) THEN
2802 BEGIN
2803 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
2804 END;
2805 END IF;
2806 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
2807 IF l_return_status = G_RET_STS_ERROR THEN
2808 RAISE G_EXCEPTION_ERROR;
2809 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2810 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2811 END IF;
2812 END IF;
2813
2814 /* Assign Periodic Fee Details*/
2815 IF p_csm_periodic_expenses_tbl IS NOT NULL THEN
2816
2817 lp_sfev_periodic_tbl := assign_periodic_expenses(p_sif_id => l_sif_id,
2818 p_csm_periodic_expenses_tbl => x_csm_periodic_expenses_tbl,
2819 x_return_status => l_return_status
2820 );
2821 --Added by kthiruva for Debugging
2822 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2823 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to assign_periodic_expenses, the return status is :'||l_return_status);
2824 END IF;
2825
2826
2827 IF l_return_status = G_RET_STS_ERROR THEN
2828 RAISE G_EXCEPTION_ERROR;
2829
2830 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2831 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2832 END IF;
2833 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
2834 IF(IS_DEBUG_PROCEDURE_ON) THEN
2835 BEGIN
2836 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
2837 END;
2838 END IF;
2839
2840 Okl_Sif_Fees_Pub.insert_sif_fees(
2841 p_api_version => p_api_version
2842
2843 ,p_init_msg_list => p_init_msg_list
2844 ,x_return_status => l_return_status
2845 ,x_msg_count => x_msg_count
2846 ,x_msg_data => x_msg_data
2847 ,p_sfev_tbl => lp_sfev_periodic_tbl
2848 ,x_sfev_tbl => lx_sfev_periodic_tbl);
2849 --Added by kthiruva for Debugging
2850 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2851 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Sif_Fees_Pub.insert_sif_fees, the return status is :'||l_return_status);
2852 END IF;
2853
2854
2855 IF(IS_DEBUG_PROCEDURE_ON) THEN
2856 BEGIN
2857 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
2858 END;
2859 END IF;
2860 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
2861 IF l_return_status = G_RET_STS_ERROR THEN
2862 RAISE G_EXCEPTION_ERROR;
2863 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2864 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2865 END IF;
2866 END IF;
2867 /* Assign Yield Data */
2868 IF p_csm_yields_tbl IS NOT NULL THEN
2869 lp_siyv_tbl := assign_yield_details(p_sif_id => l_sif_id,
2870 p_csm_yields_tbl => p_csm_yields_tbl
2871 ,x_return_status => l_return_status
2872
2873 );
2874 --Added by kthiruva for Debugging
2875 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2876 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to assign_yield_details, the return status is :'||l_return_status);
2877 END IF;
2878
2879 IF l_return_status = G_RET_STS_ERROR THEN
2880 RAISE G_EXCEPTION_ERROR;
2881
2882 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2883 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2884 END IF;
2885 -- Insert Yield Data corresponding to this Transaction
2886 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Yields_Pub.insert_sif_yields
2887 IF(IS_DEBUG_PROCEDURE_ON) THEN
2888 BEGIN
2889 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Yields_Pub.insert_sif_yields ');
2890 END;
2891 END IF;
2892 Okl_Sif_Yields_Pub.insert_sif_yields(
2893 p_api_version => p_api_version
2894 ,p_init_msg_list => p_init_msg_list
2895 ,x_return_status => l_return_status
2896 ,x_msg_count => x_msg_count
2897 ,x_msg_data => x_msg_data
2898 ,p_siyv_tbl => lp_siyv_tbl
2899
2900 ,x_siyv_tbl => lx_siyv_tbl);
2901 --Added by kthiruva for Debugging
2902 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2903 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Sif_Yields_Pub.insert_sif_yields, the return status is :'||l_return_status);
2904 END IF;
2905
2906
2907 IF(IS_DEBUG_PROCEDURE_ON) THEN
2908 BEGIN
2909 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Yields_Pub.insert_sif_yields ');
2910 END;
2911 END IF;
2912
2913 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Yields_Pub.insert_sif_yields
2914 IF l_return_status = G_RET_STS_ERROR THEN
2915 RAISE G_EXCEPTION_ERROR;
2916 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2917 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2918 END IF;
2919 END IF;
2920 /* Assign StreamTypes needed for this Transaction */
2921
2922 IF p_csm_stream_types_tbl IS NOT NULL THEN
2923 lp_sitv_tbl := assign_stream_types(p_sif_id => l_sif_id,
2924 p_csm_stream_types_tbl => p_csm_stream_types_tbl
2925 ,x_return_status => l_return_status
2926 );
2927 --Added by kthiruva for Debugging
2928 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2929 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to assign_stream_types, the return status is :'||l_return_status);
2930 END IF;
2931
2932
2933 IF l_return_status = G_RET_STS_ERROR THEN
2934 RAISE G_EXCEPTION_ERROR;
2935 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2936 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2937 END IF;
2938 -- Insert StreamTypes corresponding to this Transaction
2939 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Stream_Types_Pub.insert_sif_stream_types
2940 IF(IS_DEBUG_PROCEDURE_ON) THEN
2941 BEGIN
2942
2943 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Stream_Types_Pub.insert_sif_stream_types ');
2944 END;
2945 END IF;
2946
2947 Okl_Sif_Stream_Types_Pub.insert_sif_stream_types(
2948 p_api_version => p_api_version
2949 ,p_init_msg_list => p_init_msg_list
2950
2951 ,x_return_status => l_return_status
2952 ,x_msg_count => x_msg_count
2953 ,x_msg_data => x_msg_data
2954 ,p_sitv_tbl => lp_sitv_tbl
2955 ,x_sitv_tbl => lx_sitv_tbl);
2956 --Added by kthiruva for Debugging
2957 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2958 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Sif_Stream_Types_Pub.insert_sif_stream_types, the return status is :'||l_return_status);
2959 END IF;
2960
2961
2962
2963 IF(IS_DEBUG_PROCEDURE_ON) THEN
2964 BEGIN
2965 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Stream_Types_Pub.insert_sif_stream_types ');
2966 END;
2967 END IF;
2968 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Stream_Types_Pub.insert_sif_stream_types
2969 IF l_return_status = G_RET_STS_ERROR THEN
2970 RAISE G_EXCEPTION_ERROR;
2971 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2972 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
2973 END IF;
2974 END IF;
2975
2976 IF(IS_DEBUG_PROCEDURE_ON) THEN
2977 BEGIN
2978
2979 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Update_Pricing_Param ');
2980 END;
2981 END IF;
2982
2983
2984 Update_Pricing_Param (
2985 p_api_version => p_api_version
2986 ,p_init_msg_list => p_init_msg_list
2987 ,p_trans_id => lx_sifv_rec.transaction_number
2988 ,x_sif_id => l_sif_id
2989 ,x_khr_id => l_khr_id
2990 ,x_return_status => l_return_status
2991 ,x_msg_count => x_msg_count
2992 ,x_msg_data => x_msg_data
2993 );
2994 --Added by kthiruva for Debugging
2995 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2996 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Update_Pricing_Param, the return status is :'||l_return_status);
2997 END IF;
2998
2999 IF(IS_DEBUG_PROCEDURE_ON) THEN
3000 BEGIN
3001 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Update_Pricing_Param ');
3002 END;
3003 END IF;
3004 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Stream_Types_Pub.insert_sif_stream_types
3005 IF l_return_status = G_RET_STS_ERROR THEN
3006 RAISE G_EXCEPTION_ERROR;
3007 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3008 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3009 END IF;
3010
3011
3012
3013 IF p_skip_prc_engine <> G_TRUE THEN
3014 --Added by kthiruva for Debugging
3015 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3016 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'The value of p_skip_prc_engine is :'||p_skip_prc_engine);
3017 END IF;
3018 Invoke_Pricing_Engine(
3019 p_api_version => p_api_version,
3020 p_init_msg_list => p_init_msg_list,
3021 p_sifv_rec => lx_sifv_rec,
3022 x_sifv_rec => lx_sifv_status_rec,
3023 x_return_status => l_return_status,
3024 x_msg_count => x_msg_count,
3025 x_msg_data => x_msg_data);
3026 --Added by kthiruva for Debugging
3027 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3028 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Invoke_Pricing_Engine, the return status is :'||l_return_status);
3029 END IF;
3030
3031 IF l_return_status = G_RET_STS_ERROR THEN
3032 RAISE G_EXCEPTION_ERROR;
3033
3034 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3035 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3036 END IF;
3037 x_trans_id := lx_sifv_status_rec.transaction_number;
3038
3039 x_trans_status := lx_sifv_status_rec.sis_code;
3040 ELSE
3041 x_trans_id := lx_sifv_rec.transaction_number;
3042 x_trans_status := lx_sifv_rec.sis_code;
3043 END IF;
3044 END IF;
3045 x_return_status := l_return_status;
3046
3047 EXCEPTION
3048
3049 WHEN G_EXCEPTION_ERROR THEN
3050 x_return_status := G_RET_STS_ERROR;
3051 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
3052 x_return_status := G_RET_STS_UNEXP_ERROR;
3053 WHEN OTHERS THEN
3054 -- store SQL error message on message stack
3055 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
3056 p_msg_name => G_UNEXPECTED_ERROR,
3057 p_token1 => G_SQLCODE_TOKEN,
3058 p_token1_value => SQLCODE,
3059 p_token2 => G_SQLERRM_TOKEN,
3060 p_token2_value => SQLERRM);
3061 x_return_status := G_RET_STS_UNEXP_ERROR;
3062
3063 END Create_Streams_Lease_Book;
3064
3065 ---------------------------------------------------------------------------
3066 -- PROCEDURE Create_Streams_Loan_Book
3067 ---------------------------------------------------------------------------
3068 PROCEDURE Create_Streams_Loan_Book (
3069
3070 p_api_version IN NUMBER
3071 ,p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
3072 ,p_skip_prc_engine IN VARCHAR2 DEFAULT G_FALSE
3073 ,p_csm_loan_header IN csm_loan_rec_type
3074 -- 04/21/2002
3075 ,p_csm_loan_lines_tbl IN csm_loan_line_tbl_type
3076 ,p_csm_loan_levels_tbl IN csm_loan_level_tbl_type
3077 ,p_csm_one_off_fee_tbl IN csm_one_off_fee_tbl_type
3078 ,p_csm_periodic_expenses_tbl IN csm_periodic_expenses_tbl_type
3079 -- end,04/21/2002
3080 ,p_csm_yields_tbl IN csm_yields_tbl_type
3081 ,p_csm_stream_types_tbl IN csm_stream_types_tbl_type
3082 ,x_trans_id OUT NOCOPY NUMBER
3083 ,x_trans_status OUT NOCOPY VARCHAR2
3084 ,x_return_status OUT NOCOPY VARCHAR2
3085 ,x_msg_count OUT NOCOPY NUMBER
3086 ,x_msg_data OUT NOCOPY VARCHAR2
3087 )
3088 IS
3089 l_api_name CONSTANT VARCHAR2(30) := 'Create_Streams_Loan_Book';
3090 l_api_version CONSTANT NUMBER := 1;
3091 lp_sifv_rec sifv_rec_type;
3092 lx_sifv_rec sifv_rec_type;
3093 lx_sifv_status_rec sifv_rec_type;
3094 lp_sfev_loan_levels_tbl sfev_tbl_type;
3095
3096 lx_sfev_loan_levels_tbl sfev_tbl_type;
3097 lp_sfev_one_off_tbl sfev_tbl_type;
3098 lx_sfev_one_off_tbl sfev_tbl_type;
3099 lp_sfev_periodic_expenses_tbl sfev_tbl_type;
3100 lx_sfev_periodic_expenses_tbl sfev_tbl_type;
3101 lp_sfev_periodic_incomes_tbl sfev_tbl_type;
3102 lx_sfev_periodic_incomes_tbl sfev_tbl_type;
3103 lp_siyv_tbl siyv_tbl_type;
3104
3105 lx_siyv_tbl siyv_tbl_type;
3106 lp_sitv_tbl sitv_tbl_type;
3107 lx_sitv_tbl sitv_tbl_type;
3108 -- new structures for finance fee
3109
3110 x_csm_one_off_fee_tbl csm_one_off_fee_tbl_type;
3111 x_csm_periodic_expenses_tbl csm_periodic_expenses_tbl_type;
3112 x_csm_loan_lines_tbl csm_loan_line_tbl_type;
3113 x_csm_loan_levels_tbl csm_loan_level_tbl_type;
3114 l_sif_id NUMBER;
3115 l_khr_id NUMBER;
3116
3117 l_pending BOOLEAN := FALSE;
3118 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
3119 ---------------------------------------------------------------------------
3120 -- FUNCTION pending_request_exists
3121 -- Checks if any request is pending for the specified ContractNumber
3122 ---------------------------------------------------------------------------
3123 FUNCTION pending_request_exists(
3124 p_khr_id IN NUMBER
3125 --smahapat 11/10/02 multi-gaap - addition
3126 ,p_purpose_code IN OKL_STREAM_INTERFACES_V.PURPOSE_CODE%TYPE
3127 --smahapat addition end
3128 ,x_return_status OUT NOCOPY VARCHAR2
3129 ) RETURN BOOLEAN
3130 IS
3131 CURSOR l_okl_sif_status_csr(p_khr_id IN OKL_STREAM_INTERFACES_V.KHR_ID%TYPE) IS
3132 SELECT '1' FROM dual
3133 WHERE EXISTS
3134 (SELECT '1'
3135 FROM OKL_STREAM_INTERFACES
3136 WHERE khr_id = p_khr_id
3137 AND SIS_CODE IN (G_SIS_HDR_INSERTED, G_SIS_DATA_ENTERED, G_SIS_PROCESSING_REQUEST,G_SIS_RET_DATA_RECEIVED)
3138 );
3139 --smahapat 11/10/02 multi-gaap - addition
3140
3141
3142 CURSOR l_okl_sif_rpt_status_csr(p_khr_id IN OKL_STREAM_INTERFACES_V.KHR_ID%TYPE) IS
3143 SELECT '1' FROM dual
3144 WHERE EXISTS
3145 (SELECT '1'
3146 FROM OKL_STREAM_INTERFACES
3147 WHERE khr_id = p_khr_id AND purpose_code = G_PURPOSE_CODE_REPORT
3148 AND SIS_CODE IN (G_SIS_HDR_INSERTED, G_SIS_DATA_ENTERED, G_SIS_PROCESSING_REQUEST,G_SIS_RET_DATA_RECEIVED)
3149 );
3150 --smahapat addition end
3151 l_pending BOOLEAN DEFAULT FALSE;
3152 BEGIN
3153 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
3154 IF p_khr_id IS NOT NULL THEN
3155
3156 --smahapat 11/10/02 multi-gaap - addition
3157 IF p_purpose_code IS NOT NULL AND p_purpose_code = G_PURPOSE_CODE_REPORT THEN
3158 FOR l_sif_rpt_csr IN l_okl_sif_rpt_status_csr(p_khr_id)
3159 LOOP
3160 l_pending := TRUE;
3161 END LOOP;
3162 ELSE
3163 --smahapat addition end
3164 FOR l_sif_csr IN l_okl_sif_status_csr(p_khr_id)
3165 LOOP
3166 l_pending := TRUE;
3167 END LOOP;
3168 END IF; --smahapat 11/10/02 multi-gaap - addition
3169 END IF;
3170 RETURN(l_pending);
3171 EXCEPTION
3172
3173 WHEN OTHERS THEN
3174 -- store SQL error message on message stack
3175 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
3176 p_msg_name => G_UNEXPECTED_ERROR,
3177 p_token1 => G_SQLCODE_TOKEN,
3178 p_token1_value => SQLCODE,
3179 p_token2 => G_SQLERRM_TOKEN,
3180 p_token2_value => SQLERRM);
3181 x_return_status := G_RET_STS_UNEXP_ERROR;
3182 RETURN NULL;
3183 END pending_request_exists;
3184 BEGIN
3185 --Added by kthiruva for Debug Logging
3186 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3187 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inside the call to create_Streams_loan_book');
3188 END IF;
3189
3190 l_return_status := G_RET_STS_SUCCESS;
3191 initialize;
3192 -- Check for any pending request for this Contract and
3193 -- Error out if there does exist a request that is not completed
3194 l_pending := pending_request_exists(p_khr_id => p_csm_loan_header.khr_id
3195 --smahapat 11/10/02 multi-gaap - addition
3196 ,p_purpose_code => p_csm_loan_header.purpose_code
3197 ,x_return_status => l_return_status);
3198 --Added by kthiruva for Debug Logging
3199 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3200 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to pending_request_exists,return status is :'||l_return_status);
3201 END IF;
3202 IF l_return_status = G_RET_STS_ERROR THEN
3203 RAISE G_EXCEPTION_ERROR;
3204 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3205 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3206 END IF;
3207 IF(l_pending) THEN
3208
3209 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
3210 p_msg_name => G_OKL_CSM_PENDING
3211 );
3212 l_return_status := G_RET_STS_ERROR;
3213 RAISE G_EXCEPTION_ERROR;
3214 ELSE
3215 /* assign Transaction Header Data */
3216 lp_sifv_rec := assign_header_details( p_loan_header_rec => p_csm_loan_header
3217 ,x_return_status => l_return_status
3218
3219 );
3220 --Added by kthiruva for Debug Logging
3221 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3222 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to assign_header_details,return status is :'||l_return_status);
3223 END IF;
3224 IF l_return_status = G_RET_STS_ERROR THEN
3225 RAISE G_EXCEPTION_ERROR;
3226 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3227 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3228
3229 END IF;
3230 -- Insert Transaction Header Data
3231 -- Start of wraper code generated automatically by Debug code generator for Okl_Stream_Interfaces_Pub.insert_stream_interfaces
3232 IF(L_DEBUG_ENABLED='Y') THEN
3233 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
3234 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
3235 END IF;
3236 IF(IS_DEBUG_PROCEDURE_ON) THEN
3237 BEGIN
3238 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Stream_Interfaces_Pub.insert_stream_interfaces ');
3239 END;
3240 END IF;
3241 Okl_Stream_Interfaces_Pub.insert_stream_interfaces(
3242 p_api_version => p_api_version
3243 ,p_init_msg_list => p_init_msg_list
3244 ,x_return_status => l_return_status
3245 ,x_msg_count => x_msg_count
3246 ,x_msg_data => x_msg_data
3247 ,p_sifv_rec => lp_sifv_rec
3248 ,x_sifv_rec => lx_sifv_rec);
3249 --Added by kthiruva for Debug Logging
3250 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3251 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Stream_Interfaces_Pub.insert_stream_interfaces,return status is :'||l_return_status);
3252 END IF;
3253
3254 IF(IS_DEBUG_PROCEDURE_ON) THEN
3255 BEGIN
3256 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Stream_Interfaces_Pub.insert_stream_interfaces ');
3257 END;
3258
3259 END IF;
3260 -- End of wraper code generated automatically by Debug code generator for Okl_Stream_Interfaces_Pub.insert_stream_interfaces
3261 IF l_return_status = G_RET_STS_ERROR THEN
3262 RAISE G_EXCEPTION_ERROR;
3263 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3264
3265 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3266 END IF;
3267 -- Get the Interface Header ID
3268 l_sif_id := lx_sifv_rec.id;
3269 -- 04/21/2002
3270
3271 IF p_csm_one_off_fee_tbl IS NOT NULL and p_csm_periodic_expenses_tbl IS NOT NULL THEN
3272
3273 insert_finance_fee_for_loan(p_api_version => p_api_version,
3274 p_init_msg_list => p_init_msg_list,
3275 p_sif_id => l_sif_id,
3276 p_csm_one_off_fee_tbl => p_csm_one_off_fee_tbl,
3277 p_csm_periodic_expenses_tbl => p_csm_periodic_expenses_tbl,
3278 p_csm_loan_lines_tbl => p_csm_loan_lines_tbl, --input param
3279
3280 p_csm_loan_levels_tbl => p_csm_loan_levels_tbl,
3281 x_csm_one_off_fee_tbl => x_csm_one_off_fee_tbl,
3282 x_csm_periodic_expenses_tbl => x_csm_periodic_expenses_tbl,
3283 x_csm_loan_lines_tbl => x_csm_loan_lines_tbl, --output param
3284 x_csm_loan_levels_tbl => x_csm_loan_levels_tbl,
3285 x_return_status => l_return_status,
3286 x_msg_count => x_msg_count,
3287 x_msg_data => x_msg_data);
3288 --Added by kthiruva for Debug Logging
3289 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3290 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to insert_finance_fee_for_loan,return status is :'||l_return_status);
3291 END IF;
3292
3293 IF l_return_status = G_RET_STS_ERROR THEN
3294 RAISE G_EXCEPTION_ERROR;
3295 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3296
3297 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3298 END IF;
3299
3300 END IF;
3301
3302 /* Assign Loan Line Details*/
3303 IF p_csm_loan_lines_tbl IS NOT NULL THEN
3304 insert_loan_lines(p_api_version => p_api_version,
3305 p_init_msg_list => p_init_msg_list,
3306 p_sif_id => l_sif_id,
3307 p_csm_loan_lines_tbl => x_csm_loan_lines_tbl,
3308 x_return_status => l_return_status,
3309 x_msg_count => x_msg_count,
3310 x_msg_data => x_msg_data);
3311 --Added by kthiruva for Debug Logging
3312 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3313 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to insert_loan_lines,return status is :'||l_return_status);
3314 END IF;
3315
3316 IF l_return_status = G_RET_STS_ERROR THEN
3317 RAISE G_EXCEPTION_ERROR;
3318 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3319 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3320 END IF;
3321 END IF;
3322 /* Assign Loan Levels*/
3323 IF p_csm_loan_levels_tbl IS NOT NULL THEN
3324 lp_sfev_loan_levels_tbl := assign_loan_levels(p_sif_id => l_sif_id,
3325 p_csm_loan_levels_tbl => x_csm_loan_levels_tbl,
3326 x_return_status => l_return_status
3327 );
3328 --Added by kthiruva for Debug Logging
3329 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3330 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to assign_loan_levels,return status is :'||l_return_status);
3331 END IF;
3332
3333 IF l_return_status = G_RET_STS_ERROR THEN
3334
3335 RAISE G_EXCEPTION_ERROR;
3336 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3337 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3338 END IF;
3339 -- Insert Loan Levels
3340 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
3341 IF(IS_DEBUG_PROCEDURE_ON) THEN
3342 BEGIN
3343 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
3344 END;
3345
3346 END IF;
3347
3348 Okl_Sif_Fees_Pub.insert_sif_fees(
3349 p_api_version => p_api_version
3350 ,p_init_msg_list => p_init_msg_list
3351 ,x_return_status => l_return_status
3352 ,x_msg_count => x_msg_count
3353
3354 ,x_msg_data => x_msg_data
3355 ,p_sfev_tbl => lp_sfev_loan_levels_tbl
3356 ,x_sfev_tbl => lx_sfev_loan_levels_tbl);
3357 --Added by kthiruva for Debug Logging
3358 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3359 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Sif_Fees_Pub.insert_sif_fees,return status is :'||l_return_status);
3360 END IF;
3361
3362 IF(IS_DEBUG_PROCEDURE_ON) THEN
3363 BEGIN
3364 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
3365 END;
3366 END IF;
3367 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
3368 IF l_return_status = G_RET_STS_ERROR THEN
3369 RAISE G_EXCEPTION_ERROR;
3370 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3371 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3372 END IF;
3373 END IF;
3374 /* Assign One-Off Fee Details */
3375 IF p_csm_one_off_fee_tbl IS NOT NULL THEN
3376 lp_sfev_one_off_tbl := assign_one_off_fees(p_sif_id => l_sif_id,
3377 p_csm_one_off_fee_tbl => x_csm_one_off_fee_tbl,
3378 x_return_status => l_return_status
3379
3380 );
3381 --Added by kthiruva for Debug Logging
3382 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3383 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to assign_one_off_fees,return status is :'||l_return_status);
3384 END IF;
3385
3386 IF l_return_status = G_RET_STS_ERROR THEN
3387 RAISE G_EXCEPTION_ERROR;
3388 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3389 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3390 END IF;
3391 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
3392 IF(IS_DEBUG_PROCEDURE_ON) THEN
3393 BEGIN
3394 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
3395
3396 END;
3397 END IF;
3398 Okl_Sif_Fees_Pub.insert_sif_fees(
3399 p_api_version => p_api_version
3400 ,p_init_msg_list => p_init_msg_list
3401 ,x_return_status => l_return_status
3402 ,x_msg_count => x_msg_count
3403 ,x_msg_data => x_msg_data
3404 ,p_sfev_tbl => lp_sfev_one_off_tbl
3405 ,x_sfev_tbl => lx_sfev_one_off_tbl);
3406 --Added by kthiruva for Debug Logging
3407 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3408 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Sif_Fees_Pub.insert_sif_fees,return status is :'||l_return_status);
3409 END IF;
3410
3411
3412 IF(IS_DEBUG_PROCEDURE_ON) THEN
3413 BEGIN
3414 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
3415 END;
3416 END IF;
3417
3418 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
3419 IF l_return_status = G_RET_STS_ERROR THEN
3420 RAISE G_EXCEPTION_ERROR;
3421 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3422 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3423 END IF;
3424
3425 END IF;
3426 /* Assign Periodic Expense Details*/
3427 IF p_csm_periodic_expenses_tbl IS NOT NULL THEN
3428 lp_sfev_periodic_expenses_tbl := assign_periodic_expenses(p_sif_id => l_sif_id,
3429 p_csm_periodic_expenses_tbl => x_csm_periodic_expenses_tbl,
3430 x_return_status => l_return_status
3431 );
3432 --Added by kthiruva for Debug Logging
3433 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3434 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to assign_periodic_expenses,return status is :'||l_return_status);
3435 END IF;
3436
3437 IF l_return_status = G_RET_STS_ERROR THEN
3438 RAISE G_EXCEPTION_ERROR;
3439 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3440 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3441 END IF;
3442 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
3443 IF(IS_DEBUG_PROCEDURE_ON) THEN
3444 BEGIN
3445 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
3446 END;
3447 END IF;
3448 Okl_Sif_Fees_Pub.insert_sif_fees(
3449 p_api_version => p_api_version
3450 ,p_init_msg_list => p_init_msg_list
3451 ,x_return_status => l_return_status
3452 ,x_msg_count => x_msg_count
3453 ,x_msg_data => x_msg_data
3454 ,p_sfev_tbl => lp_sfev_periodic_expenses_tbl
3455 ,x_sfev_tbl => lx_sfev_periodic_expenses_tbl);
3456 --Added by kthiruva for Debug Logging
3457 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3458 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Sif_Fees_Pub.insert_sif_fees,return status is :'||l_return_status);
3459 END IF;
3460
3461
3462 IF(IS_DEBUG_PROCEDURE_ON) THEN
3463 BEGIN
3464
3465 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
3466 END;
3467 END IF;
3468 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
3469 IF l_return_status = G_RET_STS_ERROR THEN
3470 RAISE G_EXCEPTION_ERROR;
3471 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3472 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3473 END IF;
3474 END IF;
3475 /* Assign Periodic Income Details*
3476
3477 IF p_csm_periodic_incomes_tbl IS NOT NULL THEN
3478 lp_sfev_periodic_incomes_tbl := assign_periodic_incomes(p_sif_id => l_sif_id,
3479 p_csm_periodic_incomes_tbl => p_csm_periodic_incomes_tbl,
3480 x_return_status => l_return_status
3481 );
3482 IF l_return_status = G_RET_STS_ERROR THEN
3483 RAISE G_EXCEPTION_ERROR;
3484
3485 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3486 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3487 END IF;
3488 OKL_SIF_FEES_PUB.insert_sif_fees(
3489 p_api_version => p_api_version
3490 ,p_init_msg_list => p_init_msg_list
3491 ,x_return_status => l_return_status
3492 ,x_msg_count => x_msg_count
3493 ,x_msg_data => x_msg_data
3494 ,p_sfev_tbl => lp_sfev_periodic_incomes_tbl
3495 ,x_sfev_tbl => lx_sfev_periodic_incomes_tbl);
3496 IF l_return_status = G_RET_STS_ERROR THEN
3497 RAISE G_EXCEPTION_ERROR;
3498 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3499 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3500 END IF;
3501
3502 END IF;
3503 */
3504 -- end, 04/21/2002
3505 /* Assign Yield Data */
3506 IF p_csm_yields_tbl IS NOT NULL THEN
3507 lp_siyv_tbl := assign_yield_details(p_sif_id => l_sif_id,
3508 p_csm_yields_tbl => p_csm_yields_tbl
3509 ,x_return_status => l_return_status
3510 );
3511 --Added by kthiruva for Debug Logging
3512 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3513 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to assign_yield_details,return status is :'||l_return_status);
3514 END IF;
3515
3516 IF l_return_status = G_RET_STS_ERROR THEN
3517 RAISE G_EXCEPTION_ERROR;
3518 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3519 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3520 END IF;
3521 -- Insert Yield Data corresponding to this Transaction
3522
3523 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Yields_Pub.insert_sif_yields
3524 IF(IS_DEBUG_PROCEDURE_ON) THEN
3525 BEGIN
3526 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Yields_Pub.insert_sif_yields ');
3527 END;
3528 END IF;
3529 Okl_Sif_Yields_Pub.insert_sif_yields(
3530 p_api_version => p_api_version
3531 ,p_init_msg_list => p_init_msg_list
3532 ,x_return_status => l_return_status
3533 ,x_msg_count => x_msg_count
3534 ,x_msg_data => x_msg_data
3535 ,p_siyv_tbl => lp_siyv_tbl
3536 ,x_siyv_tbl => lx_siyv_tbl);
3537 --Added by kthiruva for Debug Logging
3538 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3539 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Sif_Yields_Pub.insert_sif_yields,return status is :'||l_return_status);
3540 END IF;
3541
3542 IF(IS_DEBUG_PROCEDURE_ON) THEN
3543 BEGIN
3544
3545 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Yields_Pub.insert_sif_yields ');
3546
3547 END;
3548 END IF;
3549 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Yields_Pub.insert_sif_yields
3550 IF l_return_status = G_RET_STS_ERROR THEN
3551 RAISE G_EXCEPTION_ERROR;
3552 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3553 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3554 END IF;
3555 END IF;
3556 /* Assign StreamTypes needed for this Transaction */
3557 IF p_csm_stream_types_tbl IS NOT NULL THEN
3558 lp_sitv_tbl := assign_stream_types(p_sif_id => l_sif_id,
3559 p_csm_stream_types_tbl => p_csm_stream_types_tbl,
3560 x_return_status => l_return_status
3561 );
3562 --Added by kthiruva for Debug Logging
3563 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3564 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to assign_stream_types,return status is :'||l_return_status);
3565 END IF;
3566
3567 IF l_return_status = G_RET_STS_ERROR THEN
3568 RAISE G_EXCEPTION_ERROR;
3569 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3570 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3571 END IF;
3572 -- Insert StreamTypes corresponding to this Transaction
3573 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Stream_Types_Pub.insert_sif_stream_types
3574 IF(IS_DEBUG_PROCEDURE_ON) THEN
3575 BEGIN
3576 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Stream_Types_Pub.insert_sif_stream_types ');
3577 END;
3578 END IF;
3579 Okl_Sif_Stream_Types_Pub.insert_sif_stream_types(
3580 p_api_version => p_api_version
3581
3582 ,p_init_msg_list => p_init_msg_list
3583 ,x_return_status => l_return_status
3584 ,x_msg_count => x_msg_count
3585 ,x_msg_data => x_msg_data
3586 ,p_sitv_tbl => lp_sitv_tbl
3587 ,x_sitv_tbl => lx_sitv_tbl);
3588 --Added by kthiruva for Debug Logging
3589 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3590 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Okl_Sif_Stream_Types_Pub.insert_sif_stream_types,return status is :'||l_return_status);
3591 END IF;
3592
3593
3594 IF(IS_DEBUG_PROCEDURE_ON) THEN
3595 BEGIN
3596 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Stream_Types_Pub.insert_sif_stream_types ');
3597 END;
3598 END IF;
3599 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Stream_Types_Pub.insert_sif_stream_types
3600
3601 IF l_return_status = G_RET_STS_ERROR THEN
3602 RAISE G_EXCEPTION_ERROR;
3603 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3604 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3605 END IF;
3606 END IF;
3607
3608 IF(IS_DEBUG_PROCEDURE_ON) THEN
3609 BEGIN
3610
3611 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Update_Pricing_Param ');
3612 END;
3613 END IF;
3614
3615
3616 Update_Pricing_Param (
3617 p_api_version => p_api_version
3618 ,p_init_msg_list => p_init_msg_list
3619 ,p_trans_id => lx_sifv_rec.transaction_number
3620 ,x_sif_id => l_sif_id
3621 ,x_khr_id => l_khr_id
3622 ,x_return_status => l_return_status
3623 ,x_msg_count => x_msg_count
3624 ,x_msg_data => x_msg_data
3625 );
3626 --Added by kthiruva for Debug Logging
3627 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3628 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Update_Pricing_Param,return status is :'||l_return_status);
3629 END IF;
3630
3631 IF(IS_DEBUG_PROCEDURE_ON) THEN
3632 BEGIN
3633 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Update_Pricing_Param ');
3634 END;
3635 END IF;
3636 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Stream_Types_Pub.insert_sif_stream_types
3637 IF l_return_status = G_RET_STS_ERROR THEN
3638 RAISE G_EXCEPTION_ERROR;
3639 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3640 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3641 END IF;
3642
3643 IF p_skip_prc_engine <> G_TRUE THEN
3644 --Added by kthiruva for Debug Logging
3645 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3646 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'The value of p_skip_prc_engine is '||p_skip_prc_engine);
3647 END IF;
3648 Invoke_Pricing_Engine(
3649
3650 p_api_version => p_api_version,
3651 p_init_msg_list => p_init_msg_list,
3652 p_sifv_rec => lx_sifv_rec,
3653 x_sifv_rec => lx_sifv_status_rec,
3654 x_return_status => l_return_status,
3655 x_msg_count => x_msg_count,
3656 x_msg_data => x_msg_data);
3657 --Added by kthiruva for Debug Logging
3658 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3659 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After the call to Invoke_Pricing_Engine,return status is :'||l_return_status);
3660 END IF;
3661
3662 IF l_return_status = G_RET_STS_ERROR THEN
3663 RAISE G_EXCEPTION_ERROR;
3664 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3665 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3666 END IF;
3667
3668 x_trans_id := lx_sifv_status_rec.transaction_number;
3669 x_trans_status := lx_sifv_status_rec.sis_code;
3670 ELSE
3671 x_trans_id := lx_sifv_rec.transaction_number;
3672 x_trans_status := lx_sifv_rec.sis_code;
3673 END IF;
3674 END IF;
3675 x_return_status := l_return_status;
3676 EXCEPTION
3677 WHEN G_EXCEPTION_ERROR THEN
3678 x_return_status := G_RET_STS_ERROR;
3679 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
3680 x_return_status := G_RET_STS_UNEXP_ERROR;
3681 WHEN OTHERS THEN
3682 -- store SQL error message on message stack
3683 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
3684 p_msg_name => G_UNEXPECTED_ERROR,
3685 p_token1 => G_SQLCODE_TOKEN,
3686 p_token1_value => SQLCODE,
3687 p_token2 => G_SQLERRM_TOKEN,
3688
3689 p_token2_value => SQLERRM);
3690 x_return_status := G_RET_STS_UNEXP_ERROR;
3691 END Create_Streams_Loan_Book;
3692 PROCEDURE Invoke_Pricing_Engine(
3693 p_api_version IN NUMBER
3694 ,p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
3695 ,p_sifv_rec IN sifv_rec_type
3696 ,x_sifv_rec OUT NOCOPY sifv_rec_type
3697 ,x_return_status OUT NOCOPY VARCHAR2
3698 ,x_msg_count OUT NOCOPY NUMBER
3699 ,x_msg_data OUT NOCOPY VARCHAR2
3700 )
3701 IS
3702 lp_sifv_rec sifv_rec_type;
3703
3704 lx_sifv_rec sifv_rec_type;
3705 l_api_name CONSTANT VARCHAR2(30) := 'Invoke_Pricing_Engine';
3706 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
3707
3708 l_api_version CONSTANT NUMBER := 1;
3709 l_trx_sub_type VARCHAR2(20);
3710 BEGIN
3711 lp_sifv_rec := p_sifv_rec;
3712 -- get the deal type / trx sub type to be passed to the invoke pricing engine api
3713 l_trx_sub_type := lp_sifv_rec.deal_type;
3714 -- Set Status before Calling PrcEngine API
3715 lp_sifv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD
3716 HH24MISS'), 'YYYYMMDD HH24MISS');
3717 --srsreeni Bug5996152 start
3718 -- lp_sifv_rec.sis_code := G_SIS_DATA_ENTERED;
3719 lp_sifv_rec.sis_code := G_SIS_PROCESSING_REQUEST;
3720 --srsreeni Bug5996152 end
3721 -- Start of wraper code generated automatically by Debug code generator for Okl_Stream_Interfaces_Pub.update_stream_interfaces
3722 IF(L_DEBUG_ENABLED='Y') THEN
3723 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
3724 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
3725 END IF;
3726 IF(IS_DEBUG_PROCEDURE_ON) THEN
3727 BEGIN
3728 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Stream_Interfaces_Pub.update_stream_interfaces ');
3729 END;
3730 END IF;
3731
3732 Okl_Stream_Interfaces_Pub.update_stream_interfaces(
3733 p_api_version => p_api_version
3734 ,p_init_msg_list => p_init_msg_list
3735 ,x_return_status => l_return_status
3736 ,x_msg_count => x_msg_count
3737 ,x_msg_data => x_msg_data
3738
3739 ,p_sifv_rec => lp_sifv_rec
3740 ,x_sifv_rec => lx_sifv_rec);
3741
3742 IF(IS_DEBUG_PROCEDURE_ON) THEN
3743 BEGIN
3744 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Stream_Interfaces_Pub.update_stream_interfaces ');
3745 END;
3746 END IF;
3747 -- End of wraper code generated automatically by Debug code generator for Okl_Stream_Interfaces_Pub.update_stream_interfaces
3748 IF l_return_status = G_RET_STS_ERROR THEN
3749 RAISE G_EXCEPTION_ERROR;
3750 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3751 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3752 END IF;
3753
3754 lp_sifv_rec := lx_sifv_rec;
3755 -- Call STTA Invocation API
3756 -- Start of wraper code generated automatically by Debug code generator for Okl_Invoke_Pricing_Engine_Pub.generate_streams_st
3757 IF(IS_DEBUG_PROCEDURE_ON) THEN
3758 BEGIN
3759 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Invoke_Pricing_Engine_Pub.generate_streams_st ');
3760 END;
3761 END IF;
3762
3763 Okl_Invoke_Pricing_Engine_Pub.generate_streams_st(
3764
3765 p_api_version => p_api_version,
3766 p_init_msg_list => p_init_msg_list,
3767 p_xmlg_trx_type => Okl_Invoke_Pricing_Engine_Pvt.G_XMLG_TRX_TYPE,
3768 p_xmlg_trx_sub_type => l_trx_sub_type,
3769 p_sifv_rec => lp_sifv_rec,
3770 x_return_status => l_return_status,
3771 x_msg_count => x_msg_count,
3772 x_msg_data => x_msg_data);
3773
3774 IF(IS_DEBUG_PROCEDURE_ON) THEN
3775 BEGIN
3776 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Invoke_Pricing_Engine_Pub.generate_streams_st ');
3777 END;
3778 END IF;
3779 -- End of wraper code generated automatically by Debug code generator for Okl_Invoke_Pricing_Engine_Pub.generate_streams_st
3780 IF l_return_status = G_RET_STS_ERROR THEN
3781 RAISE G_EXCEPTION_ERROR;
3782 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3783 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3784 END IF;
3785 --srsreeni Bug 5996152 start
3786 /* lp_sifv_rec.date_processed := TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD
3787 HH24MISS'), 'YYYYMMDD HH24MISS');
3788 lp_sifv_rec.sis_code := G_SIS_PROCESSING_REQUEST;
3789 -- Start of wraper code generated automatically by Debug code generator for Okl_Stream_Interfaces_Pub.update_stream_interfaces
3790 IF(IS_DEBUG_PROCEDURE_ON) THEN
3791 BEGIN
3792 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Stream_Interfaces_Pub.update_stream_interfaces ');
3793 END;
3794 END IF;
3795
3796 Okl_Stream_Interfaces_Pub.update_stream_interfaces(
3797 p_api_version => p_api_version
3798 ,p_init_msg_list => p_init_msg_list
3799 ,x_return_status => l_return_status
3800 ,x_msg_count => x_msg_count
3801
3802 ,x_msg_data => x_msg_data
3803 ,p_sifv_rec => lp_sifv_rec
3804 ,x_sifv_rec => lx_sifv_rec);
3805
3806 IF(IS_DEBUG_PROCEDURE_ON) THEN
3807 BEGIN
3808 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Stream_Interfaces_Pub.update_stream_interfaces ');
3809 END;
3810 END IF;
3811 -- End of wraper code generated automatically by Debug code generator for Okl_Stream_Interfaces_Pub.update_stream_interfaces
3812 IF l_return_status = G_RET_STS_ERROR THEN
3813
3814 RAISE G_EXCEPTION_ERROR;
3815 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
3816 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
3817 END IF;*/
3818 --srsreeni Bug5996152 ends
3819 x_sifv_rec := lx_sifv_rec;
3820 x_return_status := l_return_status;
3821
3822 EXCEPTION
3823 WHEN G_EXCEPTION_ERROR THEN
3824
3825 x_return_status := G_RET_STS_ERROR;
3826 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
3827 x_return_status := G_RET_STS_UNEXP_ERROR;
3828 WHEN OTHERS THEN
3829 -- store SQL error message on message stack
3830 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
3831 p_msg_name => G_UNEXPECTED_ERROR,
3832 p_token1 => G_SQLCODE_TOKEN,
3833 p_token1_value => SQLCODE,
3834 p_token2 => G_SQLERRM_TOKEN,
3835 p_token2_value => SQLERRM);
3836 x_return_status := G_RET_STS_UNEXP_ERROR;
3837 END Invoke_Pricing_Engine;
3838 ---------------------------------------------------------------------------
3839 -- FUNCTION assign_target_details
3840 ---------------------------------------------------------------------------
3841 FUNCTION assign_target_details(
3842 p_sif_id IN NUMBER ,
3843 p_csm_yields_tbl IN csm_yields_tbl_type,
3844 x_return_status OUT NOCOPY VARCHAR2
3845 ) RETURN siyv_tbl_type
3846 IS
3847
3848 l_siyv_tbl siyv_tbl_type;
3849 i NUMBER := 0;
3850 l_target_count NUMBER := 0;
3851 BEGIN
3852 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
3853 FOR i IN 1..p_csm_yields_tbl.COUNT
3854 LOOP
3855 l_siyv_tbl(i).sif_id := p_sif_id;
3856 l_siyv_tbl(i).yield_name := p_csm_yields_tbl(i).yield_name;
3857 l_siyv_tbl(i).method := p_csm_yields_tbl(i).method;
3858 l_siyv_tbl(i).array_type := p_csm_yields_tbl(i).array_type;
3859 l_siyv_tbl(i).roe_type := p_csm_yields_tbl(i).roe_type;
3860 l_siyv_tbl(i).roe_base := p_csm_yields_tbl(i).roe_base;
3861 l_siyv_tbl(i).compounded_method := p_csm_yields_tbl(i).compounded_method;
3862
3863 l_siyv_tbl(i).nominal_yn := p_csm_yields_tbl(i).nominal_yn;
3864 l_siyv_tbl(i).target_value := p_csm_yields_tbl(i).target_value;
3865 l_siyv_tbl(i).pre_tax_yn := p_csm_yields_tbl(i).pre_tax_yn;
3866 IF p_csm_yields_tbl(i).target_value IS NOT NULL AND
3867 p_csm_yields_tbl(i).target_value <> OKC_API.G_MISS_NUM
3868 THEN
3869 l_target_count := l_target_count + 1;
3870 END IF;
3871 IF l_target_count > 1 THEN
3872 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
3873
3874 p_msg_name => G_OKL_MULTIPLE_TARGET_VALUES);
3875 RAISE G_EXCEPTION_ERROR;
3876 END IF;
3877 -- mvasudev, 06/26/2002, sno
3878
3879 l_siyv_tbl(i).siy_type := p_csm_yields_tbl(i).siy_type;
3880 get_siy_index(p_csm_yields_tbl(i).siy_type || p_csm_yields_tbl(i).yield_name
3881 ,l_siyv_tbl(i).index_number);
3882 END LOOP;
3883 RETURN l_siyv_tbl;
3884 EXCEPTION
3885 WHEN G_EXCEPTION_ERROR THEN
3886 x_return_status := G_RET_STS_ERROR;
3887 l_siyv_tbl.DELETE;
3888 RETURN l_siyv_tbl;
3889 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
3890 x_return_status := G_RET_STS_UNEXP_ERROR;
3891 l_siyv_tbl.DELETE;
3892 RETURN l_siyv_tbl;
3893 WHEN OTHERS THEN
3894 -- store SQL error message on message stack
3895 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
3896 p_msg_name => G_UNEXPECTED_ERROR,
3897 p_token1 => G_SQLCODE_TOKEN,
3898 p_token1_value => SQLCODE,
3899 p_token2 => G_SQLERRM_TOKEN,
3900 p_token2_value => SQLERRM);
3901 x_return_status := G_RET_STS_UNEXP_ERROR;
3902 l_siyv_tbl.DELETE;
3903 RETURN l_siyv_tbl;
3904 END assign_target_details;
3905 PROCEDURE Create_Streams_Lease_Restr (
3906 p_api_version IN NUMBER
3907 ,p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
3908 ,p_skip_prc_engine IN VARCHAR2 DEFAULT G_FALSE
3909
3910 ,p_csm_lease_header IN csm_lease_rec_type
3911 ,p_csm_one_off_fee_tbl IN csm_one_off_fee_tbl_type
3912 ,p_csm_periodic_expenses_tbl IN csm_periodic_expenses_tbl_type
3913 ,p_csm_yields_tbl IN csm_yields_tbl_type
3914 ,p_csm_stream_types_tbl IN csm_stream_types_tbl_type
3915 ,p_csm_line_details_tbl IN csm_line_details_tbl_type
3916
3917 ,p_rents_tbl IN csm_periodic_expenses_tbl_type
3918 ,x_trans_id OUT NOCOPY NUMBER
3919 ,x_trans_status OUT NOCOPY VARCHAR2
3920 ,x_return_status OUT NOCOPY VARCHAR2
3921 ,x_msg_count OUT NOCOPY NUMBER
3922 ,x_msg_data OUT NOCOPY VARCHAR2
3923 )
3924 IS
3925 l_api_name CONSTANT VARCHAR2(30) := 'Create_Streams_Lease_Restr';
3926 lp_sifv_rec sifv_rec_type;
3927 lx_sifv_rec sifv_rec_type;
3928 lx_sifv_status_rec sifv_rec_type;
3929 lp_sfev_rent_tbl sfev_tbl_type;
3930
3931 lx_sfev_rent_tbl sfev_tbl_type;
3932 lp_sfev_one_off_tbl sfev_tbl_type;
3933 lx_sfev_one_off_tbl sfev_tbl_type;
3934 lp_sfev_periodic_tbl sfev_tbl_type;
3935 lx_sfev_periodic_tbl sfev_tbl_type;
3936 lp_siyv_tbl siyv_tbl_type;
3937 lx_siyv_tbl siyv_tbl_type;
3938 lp_sitv_tbl sitv_tbl_type;
3939 lx_sitv_tbl sitv_tbl_type;
3940 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
3941 l_api_version CONSTANT NUMBER := 1;
3942 l_sif_id NUMBER;
3943 l_khr_id NUMBER;
3944 l_pending BOOLEAN := FALSE;
3945 l_pricing_engine okl_st_gen_tmpt_sets.pricing_engine%TYPE;
3946 ---------------------------------------------------------------------------
3947 -- FUNCTION pending_request_exists
3948 -- Checks if any request is pending for the specified ContractNumber
3949 ---------------------------------------------------------------------------
3950 FUNCTION pending_request_exists(
3951
3952 p_csm_lease_header IN csm_lease_rec_type
3953 ,x_return_status OUT NOCOPY VARCHAR2
3954 ) RETURN BOOLEAN
3955 IS
3956 CURSOR l_okl_sif_status_csr(p_csm_lease_header csm_lease_rec_type) IS
3957 SELECT '1' FROM dual
3958 WHERE EXISTS
3959 (SELECT '1'
3960 FROM OKL_STREAM_INTERFACES
3961 WHERE jtot_object1_code = p_csm_lease_header.jtot_object1_code
3962 AND object1_id1 = p_csm_lease_header.object1_id1
3963 AND SIS_CODE IN (G_SIS_HDR_INSERTED, G_SIS_DATA_ENTERED, G_SIS_PROCESSING_REQUEST)
3964 );
3965 l_pending BOOLEAN DEFAULT FALSE;
3966 BEGIN
3967
3968 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
3969 --IF p_csm_lease_header IS NOT NULL THEN
3970 FOR l_sif_csr IN l_okl_sif_status_csr(p_csm_lease_header)
3971 LOOP
3972 l_pending := TRUE;
3973 END LOOP;
3974 --END IF;
3975 RETURN(l_pending);
3976 EXCEPTION
3977 WHEN OTHERS THEN
3978 -- store SQL error message on message stack
3979
3980
3981 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
3982 p_msg_name => G_UNEXPECTED_ERROR,
3983 p_token1 => G_SQLCODE_TOKEN,
3984 p_token1_value => SQLCODE,
3985 p_token2 => G_SQLERRM_TOKEN,
3986 p_token2_value => SQLERRM);
3987 x_return_status := G_RET_STS_UNEXP_ERROR;
3988 RETURN NULL;
3989 END pending_request_exists;
3990 BEGIN
3991 l_return_status := G_RET_STS_SUCCESS;
3992 initialize;
3993 -- Check for any pending request for this Contract and
3994 -- Error out if there does exist a request that is not completed
3995 l_pending := pending_request_exists(p_csm_lease_header => p_csm_lease_header
3996 ,x_return_status => l_return_status);
3997 IF l_return_status = G_RET_STS_ERROR THEN
3998 RAISE G_EXCEPTION_ERROR;
3999 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4000 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4001 END IF;
4002 IF(l_pending) THEN
4003 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
4004 p_msg_name => G_OKL_CSM_PENDING
4005 );
4006 l_return_status := G_RET_STS_ERROR;
4007 RAISE G_EXCEPTION_ERROR;
4008 ELSE
4009 -- gboomina Bug 4659724 start
4010 OKL_STREAMS_UTIL.get_pricing_engine(
4011 p_khr_id => p_csm_lease_header.khr_id,
4012 x_pricing_engine => l_pricing_engine,
4013 x_return_status => x_return_status);
4014
4015 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
4016 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4017 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
4018 raise OKL_API.G_EXCEPTION_ERROR;
4019 END IF;
4020
4021 IF ( l_pricing_engine = 'INTERNAL') THEN
4022 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
4023 p_msg_name => G_OKL_INT_PRIC_RESTR_NA
4024 );
4025 l_return_status := G_RET_STS_ERROR;
4026 RAISE G_EXCEPTION_ERROR;
4027 END IF;
4028 -- gboomina Bug 4659724 end
4029
4030 /* assign Transaction Header Data */
4031 lp_sifv_rec := assign_header_details( p_lease_header_rec => p_csm_lease_header
4032 ,x_return_status => l_return_status
4033 );
4034 IF l_return_status = G_RET_STS_ERROR THEN
4035 RAISE G_EXCEPTION_ERROR;
4036 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4037
4038 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4039 END IF;
4040
4041 -- Insert Transaction Header Data
4042 -- Start of wraper code generated automatically by Debug code generator for Okl_Stream_Interfaces_Pub.insert_stream_interfaces
4043 IF(L_DEBUG_ENABLED='Y') THEN
4044 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
4045 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
4046 END IF;
4047 IF(IS_DEBUG_PROCEDURE_ON) THEN
4048 BEGIN
4049 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Stream_Interfaces_Pub.insert_stream_interfaces ');
4050 END;
4051 END IF;
4052 Okl_Stream_Interfaces_Pub.insert_stream_interfaces(
4053 p_api_version => p_api_version
4054 ,p_init_msg_list => p_init_msg_list
4055 ,x_return_status => l_return_status
4056 ,x_msg_count => x_msg_count
4057 ,x_msg_data => x_msg_data
4058 ,p_sifv_rec => lp_sifv_rec
4059
4060 ,x_sifv_rec => lx_sifv_rec);
4061 IF(IS_DEBUG_PROCEDURE_ON) THEN
4062 BEGIN
4063 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Stream_Interfaces_Pub.insert_stream_interfaces ');
4064 END;
4065 END IF;
4066 -- End of wraper code generated automatically by Debug code generator for Okl_Stream_Interfaces_Pub.insert_stream_interfaces
4067 IF l_return_status = G_RET_STS_ERROR THEN
4068 RAISE G_EXCEPTION_ERROR;
4069 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4070 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4071 END IF;
4072 -- Get the Interface Header ID
4073 l_sif_id := lx_sifv_rec.id;
4074
4075 /* Assign line Level Transaction Details*/
4076 IF p_csm_line_details_tbl IS NOT NULL THEN
4077 insert_asset_lines(p_api_version => p_api_version,
4078 p_init_msg_list => p_init_msg_list,
4079 p_sif_id => l_sif_id,
4080 p_csm_line_details_tbl => p_csm_line_details_tbl,
4081 x_return_status => l_return_status,
4082 x_msg_count => x_msg_count,
4083 x_msg_data => x_msg_data);
4084 IF l_return_status = G_RET_STS_ERROR THEN
4085 RAISE G_EXCEPTION_ERROR;
4086 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4087 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4088 END IF;
4089 END IF;
4090 /* Assign Rent Details*/
4091
4092 IF p_rents_tbl IS NOT NULL THEN
4093 lp_sfev_rent_tbl := assign_rent_details(p_sif_id => l_sif_id,
4094 p_csm_periodic_expenses_tbl => p_rents_tbl,
4095 x_return_status => l_return_status
4096 );
4097 IF l_return_status = G_RET_STS_ERROR THEN
4098 RAISE G_EXCEPTION_ERROR;
4099 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4100 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4101
4102 END IF;
4103 -- Insert Rent Details
4104 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
4105
4106 IF(IS_DEBUG_PROCEDURE_ON) THEN
4107 BEGIN
4108 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
4109 END;
4110 END IF;
4111
4112 Okl_Sif_Fees_Pub.insert_sif_fees(
4113 p_api_version => p_api_version
4114 ,p_init_msg_list => p_init_msg_list
4115 ,x_return_status => l_return_status
4116 ,x_msg_count => x_msg_count
4117 ,x_msg_data => x_msg_data
4118 ,p_sfev_tbl => lp_sfev_rent_tbl
4119 ,x_sfev_tbl => lx_sfev_rent_tbl);
4120 IF(IS_DEBUG_PROCEDURE_ON) THEN
4121 BEGIN
4122 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
4123 END;
4124 END IF;
4125 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
4126 IF l_return_status = G_RET_STS_ERROR THEN
4127 RAISE G_EXCEPTION_ERROR;
4128 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4129 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4130 END IF;
4131 END IF;
4132 /* Assign One-Off Fee Details */
4133 IF p_csm_one_off_fee_tbl IS NOT NULL THEN
4134 lp_sfev_one_off_tbl := assign_one_off_fees(p_sif_id => l_sif_id,
4135 p_csm_one_off_fee_tbl => p_csm_one_off_fee_tbl,
4136 x_return_status => l_return_status
4137 );
4138 IF l_return_status = G_RET_STS_ERROR THEN
4139 RAISE G_EXCEPTION_ERROR;
4140 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4141 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4142
4143 END IF;
4144
4145 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
4146 IF(IS_DEBUG_PROCEDURE_ON) THEN
4147 BEGIN
4148 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
4149 END;
4150 END IF;
4151 Okl_Sif_Fees_Pub.insert_sif_fees(
4152 p_api_version => p_api_version
4153 ,p_init_msg_list => p_init_msg_list
4154 ,x_return_status => l_return_status
4155 ,x_msg_count => x_msg_count
4156 ,x_msg_data => x_msg_data
4157 ,p_sfev_tbl => lp_sfev_one_off_tbl
4158 ,x_sfev_tbl => lx_sfev_one_off_tbl);
4159 IF(IS_DEBUG_PROCEDURE_ON) THEN
4160 BEGIN
4161 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
4162 END;
4163 END IF;
4164 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
4165 IF l_return_status = G_RET_STS_ERROR THEN
4166
4167 RAISE G_EXCEPTION_ERROR;
4168 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4169 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4170 END IF;
4171 END IF;
4172 /* Assign Periodic Fee Details*/
4173 IF p_csm_periodic_expenses_tbl IS NOT NULL THEN
4174 lp_sfev_periodic_tbl := assign_periodic_expenses(p_sif_id => l_sif_id,
4175 p_csm_periodic_expenses_tbl => p_csm_periodic_expenses_tbl,
4176 x_return_status => l_return_status
4177
4178 );
4179 IF l_return_status = G_RET_STS_ERROR THEN
4180 RAISE G_EXCEPTION_ERROR;
4181 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4182 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4183 END IF;
4184 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
4185 IF(IS_DEBUG_PROCEDURE_ON) THEN
4186 BEGIN
4187 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
4188 END;
4189 END IF;
4190 Okl_Sif_Fees_Pub.insert_sif_fees(
4191 p_api_version => p_api_version
4192 ,p_init_msg_list => p_init_msg_list
4193 ,x_return_status => l_return_status
4194 ,x_msg_count => x_msg_count
4195
4196 ,x_msg_data => x_msg_data
4197 ,p_sfev_tbl => lp_sfev_periodic_tbl
4198 ,x_sfev_tbl => lx_sfev_periodic_tbl);
4199 IF(IS_DEBUG_PROCEDURE_ON) THEN
4200 BEGIN
4201 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
4202
4203 END;
4204 END IF;
4205 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
4206 IF l_return_status = G_RET_STS_ERROR THEN
4207 RAISE G_EXCEPTION_ERROR;
4208 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4209 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4210
4211 END IF;
4212 END IF;
4213 /* Assign Yield Data */
4214 IF p_csm_yields_tbl IS NOT NULL THEN
4215 lp_siyv_tbl := assign_target_details(p_sif_id => l_sif_id,
4216 p_csm_yields_tbl => p_csm_yields_tbl
4217 ,x_return_status => l_return_status
4218 );
4219 IF l_return_status = G_RET_STS_ERROR THEN
4220 RAISE G_EXCEPTION_ERROR;
4221 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4222 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4223 END IF;
4224 -- Insert Yield Data corresponding to this Transaction
4225 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Yields_Pub.insert_sif_yields
4226 IF(IS_DEBUG_PROCEDURE_ON) THEN
4227 BEGIN
4228 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Yields_Pub.insert_sif_yields ');
4229 END;
4230 END IF;
4231 Okl_Sif_Yields_Pub.insert_sif_yields(
4232 p_api_version => p_api_version
4233 ,p_init_msg_list => p_init_msg_list
4234 ,x_return_status => l_return_status
4235 ,x_msg_count => x_msg_count
4236 ,x_msg_data => x_msg_data
4237 ,p_siyv_tbl => lp_siyv_tbl
4238 ,x_siyv_tbl => lx_siyv_tbl);
4239 IF(IS_DEBUG_PROCEDURE_ON) THEN
4240 BEGIN
4241 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Yields_Pub.insert_sif_yields ');
4242 END;
4243
4244 END IF;
4245 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Yields_Pub.insert_sif_yields
4246 IF l_return_status = G_RET_STS_ERROR THEN
4247 RAISE G_EXCEPTION_ERROR;
4248 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4249
4250 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4251 END IF;
4252 END IF;
4253 /* Assign StreamTypes needed for this Transaction */
4254 IF p_csm_stream_types_tbl IS NOT NULL THEN
4255 lp_sitv_tbl := assign_stream_types(p_sif_id => l_sif_id,
4256 p_csm_stream_types_tbl => p_csm_stream_types_tbl
4257 ,x_return_status => l_return_status
4258 );
4259 IF l_return_status = G_RET_STS_ERROR THEN
4260 RAISE G_EXCEPTION_ERROR;
4261 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4262 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4263 END IF;
4264 -- Insert StreamTypes corresponding to this Transaction
4265 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Stream_Types_Pub.insert_sif_stream_types
4266 IF(IS_DEBUG_PROCEDURE_ON) THEN
4267 BEGIN
4268 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Stream_Types_Pub.insert_sif_stream_types ');
4269 END;
4270 END IF;
4271 Okl_Sif_Stream_Types_Pub.insert_sif_stream_types(
4272
4273 p_api_version => p_api_version
4274 ,p_init_msg_list => p_init_msg_list
4275 ,x_return_status => l_return_status
4276 ,x_msg_count => x_msg_count
4277 ,x_msg_data => x_msg_data
4278 ,p_sitv_tbl => lp_sitv_tbl
4279
4280 ,x_sitv_tbl => lx_sitv_tbl);
4281 IF(IS_DEBUG_PROCEDURE_ON) THEN
4282 BEGIN
4283 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Stream_Types_Pub.insert_sif_stream_types ');
4284 END;
4285 END IF;
4286 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Stream_Types_Pub.insert_sif_stream_types
4287 IF l_return_status = G_RET_STS_ERROR THEN
4288 RAISE G_EXCEPTION_ERROR;
4289 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4290 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4291 END IF;
4292 END IF;
4293
4294 IF(IS_DEBUG_PROCEDURE_ON) THEN
4295 BEGIN
4296
4297 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Update_Pricing_Param ');
4298 END;
4299 END IF;
4300
4301
4302 Update_Pricing_Param (
4303 p_api_version => p_api_version
4304 ,p_init_msg_list => p_init_msg_list
4305 ,p_trans_id => lx_sifv_rec.transaction_number
4306 ,x_sif_id => l_sif_id
4307 ,x_khr_id => l_khr_id
4308 ,x_return_status => l_return_status
4309 ,x_msg_count => x_msg_count
4310 ,x_msg_data => x_msg_data
4311 );
4312 IF(IS_DEBUG_PROCEDURE_ON) THEN
4313 BEGIN
4314 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Update_Pricing_Param ');
4315 END;
4316 END IF;
4317 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Stream_Types_Pub.insert_sif_stream_types
4318 IF l_return_status = G_RET_STS_ERROR THEN
4319 RAISE G_EXCEPTION_ERROR;
4320 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4321 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4322 END IF;
4323
4324 IF p_skip_prc_engine <> G_TRUE THEN
4325 Invoke_Pricing_Engine(
4326 p_api_version => p_api_version,
4327 p_init_msg_list => p_init_msg_list,
4328 p_sifv_rec => lx_sifv_rec,
4329
4330 x_sifv_rec => lx_sifv_status_rec,
4331 x_return_status => l_return_status,
4332 x_msg_count => x_msg_count,
4333
4334 x_msg_data => x_msg_data);
4335 IF l_return_status = G_RET_STS_ERROR THEN
4336 RAISE G_EXCEPTION_ERROR;
4337 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4338 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4339 END IF;
4340 x_trans_id := lx_sifv_status_rec.transaction_number;
4341 x_trans_status := lx_sifv_status_rec.sis_code;
4342 ELSE
4343 x_trans_id := lx_sifv_rec.transaction_number;
4344 x_trans_status := lx_sifv_rec.sis_code;
4345
4346 END IF;
4347 END IF;
4348 x_return_status := l_return_status;
4349 EXCEPTION
4350 WHEN G_EXCEPTION_ERROR THEN
4351 x_return_status := G_RET_STS_ERROR;
4352 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
4353 x_return_status := G_RET_STS_UNEXP_ERROR;
4354 WHEN OTHERS THEN
4355 -- store SQL error message on message stack
4356 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
4357 p_msg_name => G_UNEXPECTED_ERROR,
4358 p_token1 => G_SQLCODE_TOKEN,
4359 p_token1_value => SQLCODE,
4360 p_token2 => G_SQLERRM_TOKEN,
4361 p_token2_value => SQLERRM);
4362 x_return_status := G_RET_STS_UNEXP_ERROR;
4363 END Create_Streams_Lease_Restr;
4364 -- This api is common for the Loan Restructures, Variable Interest Rate and Quote scenarios.
4365 -- akjain 08-20-2002
4366 PROCEDURE Create_Streams_Loan_Restr (
4367 p_api_version IN NUMBER
4368 ,p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
4369 ,p_skip_prc_engine IN VARCHAR2 DEFAULT G_FALSE
4370 ,p_csm_loan_header IN csm_loan_rec_type
4371 ,p_csm_loan_lines_tbl IN csm_loan_line_tbl_type
4372 ,p_csm_loan_levels_tbl IN csm_loan_level_tbl_type
4373 ,p_csm_one_off_fee_tbl IN csm_one_off_fee_tbl_type
4374 ,p_csm_periodic_expenses_tbl IN csm_periodic_expenses_tbl_type
4375
4376 ,p_csm_yields_tbl IN csm_yields_tbl_type
4377 ,p_csm_stream_types_tbl IN csm_stream_types_tbl_type
4378 ,x_trans_id OUT NOCOPY NUMBER
4379 ,x_trans_status OUT NOCOPY VARCHAR2
4380 ,x_return_status OUT NOCOPY VARCHAR2
4381 ,x_msg_count OUT NOCOPY NUMBER
4382 ,x_msg_data OUT NOCOPY VARCHAR2
4383 )
4384 IS
4385
4386 l_api_name CONSTANT VARCHAR2(30) := 'Create_Streams_Loan_Restr';
4387 l_api_version CONSTANT NUMBER := 1;
4388 lp_sifv_rec sifv_rec_type;
4389 lx_sifv_rec sifv_rec_type;
4390 lx_sifv_status_rec sifv_rec_type;
4391 lp_sfev_loan_levels_tbl sfev_tbl_type;
4392 lx_sfev_loan_levels_tbl sfev_tbl_type;
4393 lp_sfev_one_off_tbl sfev_tbl_type;
4394 lx_sfev_one_off_tbl sfev_tbl_type;
4395 lp_sfev_periodic_expenses_tbl sfev_tbl_type;
4396 lx_sfev_periodic_expenses_tbl sfev_tbl_type;
4397 lp_sfev_periodic_incomes_tbl sfev_tbl_type;
4398 lx_sfev_periodic_incomes_tbl sfev_tbl_type;
4399 lp_siyv_tbl siyv_tbl_type;
4400 lx_siyv_tbl siyv_tbl_type;
4401 lp_sitv_tbl sitv_tbl_type;
4402 lx_sitv_tbl sitv_tbl_type;
4403 l_sif_id NUMBER;
4404 l_khr_id NUMBER;
4405 l_pending BOOLEAN := FALSE;
4406 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
4407 l_pricing_engine okl_st_gen_tmpt_sets.pricing_engine%TYPE;
4408
4409 -- new structures for financed fee
4410 x_csm_one_off_fee_tbl csm_one_off_fee_tbl_type;
4411 x_csm_periodic_expenses_tbl csm_periodic_expenses_tbl_type;
4412 x_csm_loan_lines_tbl csm_loan_line_tbl_type;
4413 x_csm_loan_levels_tbl csm_loan_level_tbl_type;
4414 ---------------------------------------------------------------------------
4415 -- FUNCTION pending_request_exists
4416 -- Checks if any request is pending for the specified ContractNumber
4417
4418 ---------------------------------------------------------------------------
4419 FUNCTION pending_request_exists(
4420
4421 p_csm_loan_header IN csm_loan_rec_type
4422 ,x_return_status OUT NOCOPY VARCHAR2
4423 ) RETURN BOOLEAN
4424 IS
4425 CURSOR l_okl_sif_restr_status_csr(p_csm_loan_header csm_loan_rec_type) IS
4426 SELECT '1' FROM dual
4427 WHERE EXISTS
4428 (SELECT '1'
4429 FROM OKL_STREAM_INTERFACES
4430 WHERE jtot_object1_code = p_csm_loan_header.jtot_object1_code
4431 AND object1_id1 = p_csm_loan_header.object1_id1
4432 AND SIS_CODE IN (G_SIS_HDR_INSERTED, G_SIS_DATA_ENTERED, G_SIS_PROCESSING_REQUEST)
4433 );
4434 CURSOR l_okl_sif_quote_status_csr(p_csm_loan_header csm_loan_rec_type) IS
4435 SELECT '1' FROM dual
4436 WHERE EXISTS
4437 (SELECT '1'
4438 FROM OKL_STREAM_INTERFACES
4439 WHERE khr_id = p_csm_loan_header.khr_id
4440
4441
4442 AND SIS_CODE IN (G_SIS_HDR_INSERTED, G_SIS_DATA_ENTERED, G_SIS_PROCESSING_REQUEST,G_SIS_RET_DATA_RECEIVED)
4443 );
4444 l_pending BOOLEAN DEFAULT FALSE;
4445 BEGIN
4446 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
4447 IF p_csm_loan_header.orp_code IN( G_ORP_CODE_RESTRUCTURE_AM,G_ORP_CODE_RESTRUCTURE_CS,G_ORP_CODE_RENEWAL)
4448 THEN
4449 FOR l_sif_csr IN l_okl_sif_restr_status_csr(p_csm_loan_header)
4450 LOOP
4451 l_pending := TRUE;
4452 END LOOP;
4453 ELSIF p_csm_loan_header.orp_code IN (G_ORP_CODE_QUOTE,G_ORP_CODE_VARIABLE_INTEREST) THEN
4454 FOR l_sif_csr IN l_okl_sif_quote_status_csr(p_csm_loan_header)
4455 LOOP
4456 l_pending := TRUE;
4457
4458 END LOOP;
4459 END IF;
4460 RETURN(l_pending);
4461 EXCEPTION
4462 WHEN OTHERS THEN
4463 -- store SQL error message on message stack
4464 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
4465 p_msg_name => G_UNEXPECTED_ERROR,
4466 p_token1 => G_SQLCODE_TOKEN,
4467 p_token1_value => SQLCODE,
4468 p_token2 => G_SQLERRM_TOKEN,
4469 p_token2_value => SQLERRM);
4470 x_return_status := G_RET_STS_UNEXP_ERROR;
4471 RETURN NULL;
4472 END pending_request_exists;
4473 BEGIN
4474
4475 l_return_status := G_RET_STS_SUCCESS;
4476 initialize;
4477 -- Check for any pending request for this Contract and
4478 -- Error out if there does exist a request that is not completed
4479 l_pending := pending_request_exists(p_csm_loan_header => p_csm_loan_header
4480 ,x_return_status => l_return_status);
4481 IF l_return_status = G_RET_STS_ERROR THEN
4482 RAISE G_EXCEPTION_ERROR;
4483 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4484
4485 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4486 END IF;
4487 IF(l_pending) THEN
4488 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
4489 p_msg_name => G_OKL_CSM_PENDING
4490 );
4491 l_return_status := G_RET_STS_ERROR;
4492 RAISE G_EXCEPTION_ERROR;
4493 ELSE
4494
4495 -- gboomina Bug 4659724 start
4496 OKL_STREAMS_UTIL.get_pricing_engine(
4497 p_khr_id => p_csm_loan_header.khr_id,
4498 x_pricing_engine => l_pricing_engine,
4499 x_return_status => x_return_status);
4500
4501 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
4502 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4503 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
4504 raise OKL_API.G_EXCEPTION_ERROR;
4505 END IF;
4506
4507 IF ( l_pricing_engine = 'INTERNAL') THEN
4508 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
4509 p_msg_name => G_OKL_INT_PRIC_RESTR_NA
4510 );
4511 l_return_status := G_RET_STS_ERROR;
4512 RAISE G_EXCEPTION_ERROR;
4513 END IF;
4514 -- gboomina Bug 4659724 end
4515
4516 /* assign Transaction Header Data */
4517 lp_sifv_rec := assign_header_details( p_loan_header_rec => p_csm_loan_header
4518 ,x_return_status => l_return_status
4519 );
4520
4521 IF l_return_status = G_RET_STS_ERROR THEN
4522 RAISE G_EXCEPTION_ERROR;
4523 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4524 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4525 END IF;
4526 -- Insert Transaction Header Data
4527 -- Start of wraper code generated automatically by Debug code generator for Okl_Stream_Interfaces_Pub.insert_stream_interfaces
4528 IF(L_DEBUG_ENABLED='Y') THEN
4529 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
4530 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
4531 END IF;
4532 IF(IS_DEBUG_PROCEDURE_ON) THEN
4533 BEGIN
4534 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Stream_Interfaces_Pub.insert_stream_interfaces ');
4535 END;
4536 END IF;
4537
4538 Okl_Stream_Interfaces_Pub.insert_stream_interfaces(
4539 p_api_version => p_api_version
4540 ,p_init_msg_list => p_init_msg_list
4541 ,x_return_status => l_return_status
4542 ,x_msg_count => x_msg_count
4543 ,x_msg_data => x_msg_data
4544
4545 ,p_sifv_rec => lp_sifv_rec
4546
4547 ,x_sifv_rec => lx_sifv_rec);
4548
4549 IF(IS_DEBUG_PROCEDURE_ON) THEN
4550 BEGIN
4551 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Stream_Interfaces_Pub.insert_stream_interfaces ');
4552 END;
4553 END IF;
4554 -- End of wraper code generated automatically by Debug code generator for Okl_Stream_Interfaces_Pub.insert_stream_interfaces
4555 IF l_return_status = G_RET_STS_ERROR THEN
4556 RAISE G_EXCEPTION_ERROR;
4557 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4558 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4559 END IF;
4560 -- Get the Interface Header ID
4561 l_sif_id := lx_sifv_rec.id;
4562
4563 --smahapat for bug 4131347 start
4564 IF p_csm_one_off_fee_tbl IS NOT NULL and p_csm_periodic_expenses_tbl IS NOT NULL THEN
4565
4566 insert_finance_fee_for_loan(p_api_version => p_api_version,
4567 p_init_msg_list => p_init_msg_list,
4568 p_sif_id => l_sif_id,
4569 p_csm_one_off_fee_tbl => p_csm_one_off_fee_tbl,
4570 p_csm_periodic_expenses_tbl => p_csm_periodic_expenses_tbl,
4571 p_csm_loan_lines_tbl => p_csm_loan_lines_tbl,
4572 p_csm_loan_levels_tbl => p_csm_loan_levels_tbl,
4573 x_csm_one_off_fee_tbl => x_csm_one_off_fee_tbl,
4574 x_csm_periodic_expenses_tbl => x_csm_periodic_expenses_tbl,
4575 x_csm_loan_lines_tbl => x_csm_loan_lines_tbl,
4576 x_csm_loan_levels_tbl => x_csm_loan_levels_tbl,
4577 x_return_status => l_return_status,
4578 x_msg_count => x_msg_count,
4579 x_msg_data => x_msg_data);
4580 IF l_return_status = G_RET_STS_ERROR THEN
4581 RAISE G_EXCEPTION_ERROR;
4582 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4583
4584 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4585 END IF;
4586
4587 END IF;
4588 --smahapat for bug 4131347 end
4589
4590 IF p_csm_loan_lines_tbl IS NOT NULL THEN
4591
4592 /* Assign Loan Line Details*/
4593 insert_loan_lines(p_api_version => p_api_version,
4594 p_init_msg_list => p_init_msg_list,
4595 p_sif_id => l_sif_id,
4596 p_csm_loan_lines_tbl => x_csm_loan_lines_tbl,
4597 --smahapat changed for bug 4131347
4598 x_return_status => l_return_status,
4599 x_msg_count => x_msg_count,
4600 x_msg_data => x_msg_data);
4601
4602 IF l_return_status = G_RET_STS_ERROR THEN
4603 RAISE G_EXCEPTION_ERROR;
4604 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4605 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4606 END IF;
4607 END IF;
4608 /* Assign Loan Levels*/
4609 IF p_csm_loan_levels_tbl IS NOT NULL THEN
4610
4611
4612 lp_sfev_loan_levels_tbl := assign_loan_levels(p_sif_id => l_sif_id,
4613 p_csm_loan_levels_tbl => x_csm_loan_levels_tbl,
4614 p_object1_id1 => lx_sifv_rec.object1_id1,
4615 x_return_status => l_return_status
4616 );
4617
4618 IF l_return_status = G_RET_STS_ERROR THEN
4619 RAISE G_EXCEPTION_ERROR;
4620 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4621 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4622 END IF;
4623 -- Insert Loan Levels
4624 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
4625 IF(IS_DEBUG_PROCEDURE_ON) THEN
4626 BEGIN
4627 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
4628 END;
4629 END IF;
4630
4631 --Added by kthiruva for populating the method to be used by the Balance Tag
4632 -- In the case of Reamortization, we always balance for Payments
4633 -- In the caes of a Paydown we can balance for Term or for Payments.
4634 add_balance_information(x_sfev_tbl => lp_sfev_loan_levels_tbl,
4635 x_return_status => l_return_status );
4636
4637 IF l_return_status = G_RET_STS_ERROR THEN
4638 RAISE G_EXCEPTION_ERROR;
4639 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4640 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4641 END IF;
4642 --End of Changes
4643
4644
4645
4646 Okl_Sif_Fees_Pub.insert_sif_fees(
4647 p_api_version => p_api_version
4648 ,p_init_msg_list => p_init_msg_list
4649 ,x_return_status => l_return_status
4650
4651 ,x_msg_count => x_msg_count
4652 ,x_msg_data => x_msg_data
4653 ,p_sfev_tbl => lp_sfev_loan_levels_tbl
4654 ,x_sfev_tbl => lx_sfev_loan_levels_tbl);
4655
4656 IF(IS_DEBUG_PROCEDURE_ON) THEN
4657 BEGIN
4658 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
4659 END;
4660 END IF;
4661 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
4662 IF l_return_status = G_RET_STS_ERROR THEN
4663 RAISE G_EXCEPTION_ERROR;
4664 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4665 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4666 END IF;
4667 END IF;
4668
4669 /* Assign One-Off Fee Details */
4670 IF p_csm_one_off_fee_tbl IS NOT NULL THEN
4671 lp_sfev_one_off_tbl := assign_one_off_fees(p_sif_id => l_sif_id,
4672 p_csm_one_off_fee_tbl => x_csm_one_off_fee_tbl,
4673 x_return_status => l_return_status
4674 );
4675 IF l_return_status = G_RET_STS_ERROR THEN
4676 RAISE G_EXCEPTION_ERROR;
4677 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4678 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4679 END IF;
4680 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
4681 IF(IS_DEBUG_PROCEDURE_ON) THEN
4682 BEGIN
4683 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
4684 END;
4685 END IF;
4686 Okl_Sif_Fees_Pub.insert_sif_fees(
4687 p_api_version => p_api_version
4688 ,p_init_msg_list => p_init_msg_list
4689
4690 ,x_return_status => l_return_status
4691 ,x_msg_count => x_msg_count
4692 ,x_msg_data => x_msg_data
4693 ,p_sfev_tbl => lp_sfev_one_off_tbl
4694 ,x_sfev_tbl => lx_sfev_one_off_tbl);
4695
4696 IF(IS_DEBUG_PROCEDURE_ON) THEN
4697 BEGIN
4698 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
4699 END;
4700 END IF;
4701 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
4702 IF l_return_status = G_RET_STS_ERROR THEN
4703 RAISE G_EXCEPTION_ERROR;
4704 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4705 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4706
4707 END IF;
4708 END IF;
4709 /* Assign Periodic Expense Details*/
4710 IF p_csm_periodic_expenses_tbl IS NOT NULL THEN
4711 lp_sfev_periodic_expenses_tbl := assign_periodic_expenses(p_sif_id => l_sif_id,
4712
4713 p_csm_periodic_expenses_tbl => x_csm_periodic_expenses_tbl,
4714 x_return_status => l_return_status
4715 );
4716 IF l_return_status = G_RET_STS_ERROR THEN
4717 RAISE G_EXCEPTION_ERROR;
4718 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4719 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4720 END IF;
4721 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
4722 IF(IS_DEBUG_PROCEDURE_ON) THEN
4723 BEGIN
4724 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
4725 END;
4726 END IF;
4727 Okl_Sif_Fees_Pub.insert_sif_fees(
4728 p_api_version => p_api_version
4729 ,p_init_msg_list => p_init_msg_list
4730
4731 ,x_return_status => l_return_status
4732 ,x_msg_count => x_msg_count
4733 ,x_msg_data => x_msg_data
4734 ,p_sfev_tbl => lp_sfev_periodic_expenses_tbl
4735 ,x_sfev_tbl => lx_sfev_periodic_expenses_tbl);
4736 IF(IS_DEBUG_PROCEDURE_ON) THEN
4737 BEGIN
4738 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
4739 END;
4740 END IF;
4741 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
4742 IF l_return_status = G_RET_STS_ERROR THEN
4743 RAISE G_EXCEPTION_ERROR;
4744 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4745 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4746 END IF;
4747 END IF;
4748 /* Assign Yield Data */
4749 IF p_csm_yields_tbl IS NOT NULL THEN
4750 lp_siyv_tbl := assign_yield_details(p_sif_id => l_sif_id,
4751
4752 p_csm_yields_tbl => p_csm_yields_tbl
4753 ,x_return_status => l_return_status
4754 );
4755 IF l_return_status = G_RET_STS_ERROR THEN
4756 RAISE G_EXCEPTION_ERROR;
4757 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4758 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4759 END IF;
4760 -- Insert Yield Data corresponding to this Transaction
4761 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Yields_Pub.insert_sif_yields
4762 IF(IS_DEBUG_PROCEDURE_ON) THEN
4763 BEGIN
4764 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Yields_Pub.insert_sif_yields ');
4765 END;
4766 END IF;
4767 Okl_Sif_Yields_Pub.insert_sif_yields(
4768 p_api_version => p_api_version
4769 ,p_init_msg_list => p_init_msg_list
4770 ,x_return_status => l_return_status
4771 ,x_msg_count => x_msg_count
4772 ,x_msg_data => x_msg_data
4773
4774 ,p_siyv_tbl => lp_siyv_tbl
4775 ,x_siyv_tbl => lx_siyv_tbl);
4776 IF(IS_DEBUG_PROCEDURE_ON) THEN
4777 BEGIN
4778 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Yields_Pub.insert_sif_yields ');
4779 END;
4780 END IF;
4781 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Yields_Pub.insert_sif_yields
4782 IF l_return_status = G_RET_STS_ERROR THEN
4783 RAISE G_EXCEPTION_ERROR;
4784 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4785 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4786 END IF;
4787 END IF;
4788 /* Assign StreamTypes needed for this Transaction */
4789 IF p_csm_stream_types_tbl IS NOT NULL THEN
4790 lp_sitv_tbl := assign_stream_types(p_sif_id => l_sif_id,
4791
4792 p_csm_stream_types_tbl => p_csm_stream_types_tbl,
4793 x_return_status => l_return_status
4794 );
4795 IF l_return_status = G_RET_STS_ERROR THEN
4796 RAISE G_EXCEPTION_ERROR;
4797 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4798 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4799 END IF;
4800 -- Insert StreamTypes corresponding to this Transaction
4801
4802 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Stream_Types_Pub.insert_sif_stream_types
4803 IF(IS_DEBUG_PROCEDURE_ON) THEN
4804 BEGIN
4805 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Stream_Types_Pub.insert_sif_stream_types ');
4806
4807 END;
4808 END IF;
4809 Okl_Sif_Stream_Types_Pub.insert_sif_stream_types(
4810 p_api_version => p_api_version
4811 ,p_init_msg_list => p_init_msg_list
4812 ,x_return_status => l_return_status
4813 ,x_msg_count => x_msg_count
4814 ,x_msg_data => x_msg_data
4815
4816 ,p_sitv_tbl => lp_sitv_tbl
4817 ,x_sitv_tbl => lx_sitv_tbl);
4818 IF(IS_DEBUG_PROCEDURE_ON) THEN
4819 BEGIN
4820 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Stream_Types_Pub.insert_sif_stream_types ');
4821 END;
4822 END IF;
4823 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Stream_Types_Pub.insert_sif_stream_types
4824 IF l_return_status = G_RET_STS_ERROR THEN
4825 RAISE G_EXCEPTION_ERROR;
4826 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4827 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4828 END IF;
4829 END IF;
4830 IF(IS_DEBUG_PROCEDURE_ON) THEN
4831 BEGIN
4832
4833 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Update_Pricing_Param ');
4834 END;
4835 END IF;
4836
4837
4838 Update_Pricing_Param (
4839 p_api_version => p_api_version
4840 ,p_init_msg_list => p_init_msg_list
4841 ,p_trans_id => lx_sifv_rec.transaction_number
4842 ,x_sif_id => l_sif_id
4843 ,x_khr_id => l_khr_id
4844 ,x_return_status => l_return_status
4845 ,x_msg_count => x_msg_count
4846 ,x_msg_data => x_msg_data
4847 );
4848 IF(IS_DEBUG_PROCEDURE_ON) THEN
4849 BEGIN
4850 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Update_Pricing_Param ');
4851 END;
4852 END IF;
4853 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Stream_Types_Pub.insert_sif_stream_types
4854 IF l_return_status = G_RET_STS_ERROR THEN
4855 RAISE G_EXCEPTION_ERROR;
4856 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4857 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4858 END IF;
4859
4860 IF p_skip_prc_engine <> G_TRUE THEN
4861 Invoke_Pricing_Engine(
4862 p_api_version => p_api_version,
4863 p_init_msg_list => p_init_msg_list,
4864
4865 p_sifv_rec => lx_sifv_rec,
4866 x_sifv_rec => lx_sifv_status_rec,
4867 x_return_status => l_return_status,
4868 x_msg_count => x_msg_count,
4869 x_msg_data => x_msg_data);
4870 IF l_return_status = G_RET_STS_ERROR THEN
4871 RAISE G_EXCEPTION_ERROR;
4872 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4873 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4874 END IF;
4875 x_trans_id := lx_sifv_status_rec.transaction_number;
4876 x_trans_status := lx_sifv_status_rec.sis_code;
4877 ELSE
4878 x_trans_id := lx_sifv_rec.transaction_number;
4879 x_trans_status := lx_sifv_rec.sis_code;
4880 END IF;
4881 END IF;
4882
4883 x_return_status := l_return_status;
4884 EXCEPTION
4885 WHEN G_EXCEPTION_ERROR THEN
4886 x_return_status := G_RET_STS_ERROR;
4887 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
4888 x_return_status := G_RET_STS_UNEXP_ERROR;
4889 WHEN OTHERS THEN
4890 -- store SQL error message on message stack
4891 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
4892 p_msg_name => G_UNEXPECTED_ERROR,
4893 p_token1 => G_SQLCODE_TOKEN,
4894 p_token1_value => SQLCODE,
4895 p_token2 => G_SQLERRM_TOKEN,
4896 p_token2_value => SQLERRM);
4897 x_return_status := G_RET_STS_UNEXP_ERROR;
4898 END Create_Streams_Loan_Restr;
4899 ---------------------------------------------------------------------------
4900 -- PROCEDURE Create_Streams_Lease_Quote
4901 ---------------------------------------------------------------------------
4902 PROCEDURE Create_Streams_Lease_Quote (
4903 p_api_version IN NUMBER
4904 ,p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
4905 ,p_skip_prc_engine IN VARCHAR2 DEFAULT G_FALSE
4906 ,p_csm_lease_header IN csm_lease_rec_type
4907 ,p_csm_one_off_fee_tbl IN csm_one_off_fee_tbl_type
4908
4909 ,p_csm_periodic_expenses_tbl IN csm_periodic_expenses_tbl_type
4910 ,p_csm_yields_tbl IN csm_yields_tbl_type
4911 ,p_csm_stream_types_tbl IN csm_stream_types_tbl_type
4912 ,p_csm_line_details_tbl IN csm_line_details_tbl_type
4913 ,p_rents_tbl IN csm_periodic_expenses_tbl_type
4914 ,x_trans_id OUT NOCOPY NUMBER
4915 ,x_trans_status OUT NOCOPY VARCHAR2
4916 ,x_return_status OUT NOCOPY VARCHAR2
4917 ,x_msg_count OUT NOCOPY NUMBER
4918 ,x_msg_data OUT NOCOPY VARCHAR2
4919 )
4920 IS
4921 l_api_name CONSTANT VARCHAR2(30) := 'Create_Streams_Lease_Quote';
4922 lp_sifv_rec sifv_rec_type;
4923
4924 lx_sifv_rec sifv_rec_type;
4925 lx_sifv_status_rec sifv_rec_type;
4926 lp_sfev_rent_tbl sfev_tbl_type;
4927 lx_sfev_rent_tbl sfev_tbl_type;
4928 lp_sfev_one_off_tbl sfev_tbl_type;
4929 lx_sfev_one_off_tbl sfev_tbl_type;
4930 lp_sfev_periodic_tbl sfev_tbl_type;
4931 lx_sfev_periodic_tbl sfev_tbl_type;
4932 lp_siyv_tbl siyv_tbl_type;
4933 lx_siyv_tbl siyv_tbl_type;
4934 lp_sitv_tbl sitv_tbl_type;
4935 lx_sitv_tbl sitv_tbl_type;
4936
4937 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
4938
4939 -- new structures for Rollover fee
4940 x_csm_one_off_fee_tbl csm_one_off_fee_tbl_type;
4941 x_csm_periodic_expenses_tbl csm_periodic_expenses_tbl_type;
4942
4943 l_api_version CONSTANT NUMBER := 1;
4944 l_sif_id NUMBER;
4945 l_pending BOOLEAN := FALSE;
4946 ---------------------------------------------------------------------------
4947 -- FUNCTION pending_request_exists
4948 -- Checks if any request is pending for the specified ContractNumber
4949 ---------------------------------------------------------------------------
4950 FUNCTION pending_request_exists(
4951 p_khr_id IN NUMBER
4952
4953 ,x_return_status OUT NOCOPY VARCHAR2
4954 ) RETURN BOOLEAN
4955 IS
4956 CURSOR l_okl_sif_status_csr(p_khr_id IN OKL_STREAM_INTERFACES_V.KHR_ID%TYPE) IS
4957 SELECT '1' FROM dual
4958 WHERE EXISTS
4959 (SELECT '1'
4960 FROM OKL_STREAM_INTERFACES
4961 WHERE khr_id = p_khr_id
4962 AND SIS_CODE IN (G_SIS_HDR_INSERTED, G_SIS_DATA_ENTERED, G_SIS_PROCESSING_REQUEST,G_SIS_RET_DATA_RECEIVED)
4963 );
4964 l_pending BOOLEAN DEFAULT FALSE;
4965 BEGIN
4966 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
4967 IF p_khr_id IS NOT NULL THEN
4968 FOR l_sif_csr IN l_okl_sif_status_csr(p_khr_id)
4969 LOOP
4970 l_pending := TRUE;
4971 END LOOP;
4972
4973 END IF;
4974 RETURN(l_pending);
4975 EXCEPTION
4976 WHEN OTHERS THEN
4977 -- store SQL error message on message stack
4978 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
4979 p_msg_name => G_UNEXPECTED_ERROR,
4980 p_token1 => G_SQLCODE_TOKEN,
4981 p_token1_value => SQLCODE,
4982 p_token2 => G_SQLERRM_TOKEN,
4983
4984 p_token2_value => SQLERRM);
4985 x_return_status := G_RET_STS_UNEXP_ERROR;
4986 RETURN NULL;
4987 END pending_request_exists;
4988 BEGIN
4989 l_return_status := G_RET_STS_SUCCESS;
4990 initialize;
4991 -- Check for any pending request for this Contract and
4992 -- Error out if there does exist a request that is not completed
4993 l_pending := pending_request_exists(p_khr_id => p_csm_lease_header.khr_id
4994 ,x_return_status => l_return_status);
4995 IF l_return_status = G_RET_STS_ERROR THEN
4996 RAISE G_EXCEPTION_ERROR;
4997 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
4998 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
4999 END IF;
5000 IF(l_pending) THEN
5001 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
5002 p_msg_name => G_OKL_CSM_PENDING
5003 );
5004 l_return_status := G_RET_STS_ERROR;
5005 RAISE G_EXCEPTION_ERROR;
5006 ELSE
5007 /* assign Transaction Header Data */
5008 lp_sifv_rec := assign_header_details( p_lease_header_rec => p_csm_lease_header
5009 ,x_return_status => l_return_status
5010 );
5011 IF l_return_status = G_RET_STS_ERROR THEN
5012 RAISE G_EXCEPTION_ERROR;
5013 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
5014 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5015 END IF;
5016 -- Insert Transaction Header Data
5017
5018 -- Start of wraper code generated automatically by Debug code generator for Okl_Stream_Interfaces_Pub.insert_stream_interfaces
5019 IF(L_DEBUG_ENABLED='Y') THEN
5020 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
5021 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
5022 END IF;
5023 IF(IS_DEBUG_PROCEDURE_ON) THEN
5024 BEGIN
5025
5026 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Stream_Interfaces_Pub.insert_stream_interfaces ');
5027 END;
5028 END IF;
5029
5030 Okl_Stream_Interfaces_Pub.insert_stream_interfaces(
5031 p_api_version => p_api_version
5032 ,p_init_msg_list => p_init_msg_list
5033 ,x_return_status => l_return_status
5034 ,x_msg_count => x_msg_count
5035 ,x_msg_data => x_msg_data
5036
5037 ,p_sifv_rec => lp_sifv_rec
5038 ,x_sifv_rec => lx_sifv_rec);
5039 IF(IS_DEBUG_PROCEDURE_ON) THEN
5040 BEGIN
5041 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Stream_Interfaces_Pub.insert_stream_interfaces ');
5042 END;
5043 END IF;
5044 -- End of wraper code generated automatically by Debug code generator for Okl_Stream_Interfaces_Pub.insert_stream_interfaces
5045 IF l_return_status = G_RET_STS_ERROR THEN
5046 RAISE G_EXCEPTION_ERROR;
5047 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
5048
5049 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5050 END IF;
5051 -- Get the Interface Header ID
5052 l_sif_id := lx_sifv_rec.id;
5053 /* Assign line Level Transaction Details*/
5054 IF p_csm_line_details_tbl IS NOT NULL THEN
5055
5056 insert_asset_lines(p_api_version => p_api_version,
5057 p_init_msg_list => p_init_msg_list,
5058 p_sif_id => l_sif_id,
5059 p_csm_line_details_tbl => p_csm_line_details_tbl,
5060 x_return_status => l_return_status,
5061 x_msg_count => x_msg_count,
5062 x_msg_data => x_msg_data);
5063 IF l_return_status = G_RET_STS_ERROR THEN
5064 RAISE G_EXCEPTION_ERROR;
5065 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
5066 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5067 END IF;
5068 END IF;
5069
5070
5071
5072 /*Create rollover fee for lease quote*/
5073 IF p_csm_one_off_fee_tbl IS NOT NULL and p_csm_periodic_expenses_tbl IS NOT NULL THEN
5074 -- Bug 14089534 fix. vsgandhi
5075 -- bug 13381622
5076 -- If p_csm_periodic_expenses_tbl.count > 0 then
5077 insert_finance_fee_for_lease(p_api_version => p_api_version,
5078 p_init_msg_list => p_init_msg_list,
5079 p_sif_id => l_sif_id,
5080 p_csm_one_off_fee_tbl => p_csm_one_off_fee_tbl,
5081 p_csm_periodic_expenses_tbl => p_csm_periodic_expenses_tbl,
5082 x_csm_one_off_fee_tbl => x_csm_one_off_fee_tbl,
5083 x_csm_periodic_expenses_tbl => x_csm_periodic_expenses_tbl,
5084 x_return_status => l_return_status,
5085 x_msg_count => x_msg_count,
5086
5087 x_msg_data => x_msg_data);
5088
5089 IF l_return_status = G_RET_STS_ERROR THEN
5090 RAISE G_EXCEPTION_ERROR;
5091 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
5092 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5093 END IF;
5094 -- End if;
5095 END IF;
5096
5097
5098 /* Assign Rent Details*/
5099 IF p_rents_tbl IS NOT NULL THEN
5100 lp_sfev_rent_tbl := assign_rent_details(p_sif_id => l_sif_id,
5101 p_csm_periodic_expenses_tbl => p_rents_tbl,
5102 x_return_status => l_return_status
5103 );
5104
5105 IF l_return_status = G_RET_STS_ERROR THEN
5106 RAISE G_EXCEPTION_ERROR;
5107 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
5108 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5109 END IF;
5110 -- Insert Rent Details
5111 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
5112 IF(IS_DEBUG_PROCEDURE_ON) THEN
5113 BEGIN
5114 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
5115 END;
5116 END IF;
5117
5118 Okl_Sif_Fees_Pub.insert_sif_fees(
5119 p_api_version => p_api_version
5120 ,p_init_msg_list => p_init_msg_list
5121 ,x_return_status => l_return_status
5122 ,x_msg_count => x_msg_count
5123 ,x_msg_data => x_msg_data
5124 ,p_sfev_tbl => lp_sfev_rent_tbl
5125 ,x_sfev_tbl => lx_sfev_rent_tbl);
5126 IF(IS_DEBUG_PROCEDURE_ON) THEN
5127 BEGIN
5128 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
5129 END;
5130
5131 END IF;
5132 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
5133 IF l_return_status = G_RET_STS_ERROR THEN
5134 RAISE G_EXCEPTION_ERROR;
5135 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
5136 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5137 END IF;
5138 END IF;
5139
5140 /* Assign One-Off Fee Details */
5141 IF p_csm_one_off_fee_tbl IS NOT NULL THEN
5142 lp_sfev_one_off_tbl := assign_one_off_fees(p_sif_id => l_sif_id,
5143 p_csm_one_off_fee_tbl => p_csm_one_off_fee_tbl,
5144 x_return_status => l_return_status
5145 );
5146 IF l_return_status = G_RET_STS_ERROR THEN
5147 RAISE G_EXCEPTION_ERROR;
5148 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
5149 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5150 END IF;
5151
5152 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
5153 IF(IS_DEBUG_PROCEDURE_ON) THEN
5154 BEGIN
5155 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
5156 END;
5157 END IF;
5158 Okl_Sif_Fees_Pub.insert_sif_fees(
5159 p_api_version => p_api_version
5160 ,p_init_msg_list => p_init_msg_list
5161 ,x_return_status => l_return_status
5162 ,x_msg_count => x_msg_count
5163 ,x_msg_data => x_msg_data
5164 ,p_sfev_tbl => lp_sfev_one_off_tbl
5165 ,x_sfev_tbl => lx_sfev_one_off_tbl);
5166 IF(IS_DEBUG_PROCEDURE_ON) THEN
5167 BEGIN
5168 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
5169 END;
5170 END IF;
5171 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
5172 IF l_return_status = G_RET_STS_ERROR THEN
5173 RAISE G_EXCEPTION_ERROR;
5174 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
5175 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5176 END IF;
5177 END IF;
5178 /* Assign Periodic Fee Details*/
5179 IF p_csm_periodic_expenses_tbl IS NOT NULL THEN
5180 lp_sfev_periodic_tbl := assign_periodic_expenses(p_sif_id => l_sif_id, --here 222
5181
5182 p_csm_periodic_expenses_tbl => p_csm_periodic_expenses_tbl,
5183 x_return_status => l_return_status
5184 );
5185
5186 IF l_return_status = G_RET_STS_ERROR THEN
5187 RAISE G_EXCEPTION_ERROR;
5188
5189 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
5190 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5191 END IF;
5192 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
5193 IF(IS_DEBUG_PROCEDURE_ON) THEN
5194 BEGIN
5195 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
5196 END;
5197 END IF;
5198 Okl_Sif_Fees_Pub.insert_sif_fees(
5199 p_api_version => p_api_version
5200 ,p_init_msg_list => p_init_msg_list
5201 ,x_return_status => l_return_status
5202 ,x_msg_count => x_msg_count
5203 ,x_msg_data => x_msg_data
5204 ,p_sfev_tbl => lp_sfev_periodic_tbl
5205 ,x_sfev_tbl => lx_sfev_periodic_tbl);
5206 IF(IS_DEBUG_PROCEDURE_ON) THEN
5207 BEGIN
5208
5209 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Fees_Pub.insert_sif_fees ');
5210 END;
5211 END IF;
5212 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Fees_Pub.insert_sif_fees
5213 IF l_return_status = G_RET_STS_ERROR THEN
5214 RAISE G_EXCEPTION_ERROR;
5215 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
5216 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5217 END IF;
5218 END IF;
5219 /* Assign Yield Data */
5220 IF p_csm_yields_tbl IS NOT NULL THEN
5221 lp_siyv_tbl := assign_target_details(p_sif_id => l_sif_id,
5222 p_csm_yields_tbl => p_csm_yields_tbl
5223 ,x_return_status => l_return_status
5224 );
5225 IF l_return_status = G_RET_STS_ERROR THEN
5226 RAISE G_EXCEPTION_ERROR;
5227 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
5228 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5229 END IF;
5230 -- Insert Yield Data corresponding to this Transaction
5231 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Yields_Pub.insert_sif_yields
5232
5233 IF(IS_DEBUG_PROCEDURE_ON) THEN
5234 BEGIN
5235 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Yields_Pub.insert_sif_yields ');
5236 END;
5237 END IF;
5238 Okl_Sif_Yields_Pub.insert_sif_yields(
5239
5240 p_api_version => p_api_version
5241 ,p_init_msg_list => p_init_msg_list
5242 ,x_return_status => l_return_status
5243 ,x_msg_count => x_msg_count
5244 ,x_msg_data => x_msg_data
5245 ,p_siyv_tbl => lp_siyv_tbl
5246 ,x_siyv_tbl => lx_siyv_tbl);
5247 IF(IS_DEBUG_PROCEDURE_ON) THEN
5248 BEGIN
5249 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Yields_Pub.insert_sif_yields ');
5250 END;
5251 END IF;
5252 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Yields_Pub.insert_sif_yields
5253 IF l_return_status = G_RET_STS_ERROR THEN
5254 RAISE G_EXCEPTION_ERROR;
5255 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
5256
5257 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5258 END IF;
5259 END IF;
5260 /* Assign StreamTypes needed for this Transaction */
5261 IF p_csm_stream_types_tbl IS NOT NULL THEN
5262 lp_sitv_tbl := assign_stream_types(p_sif_id => l_sif_id,
5263 p_csm_stream_types_tbl => p_csm_stream_types_tbl
5264 ,x_return_status => l_return_status
5265 );
5266 IF l_return_status = G_RET_STS_ERROR THEN
5267 RAISE G_EXCEPTION_ERROR;
5268 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
5269 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5270 END IF;
5271 -- Insert StreamTypes corresponding to this Transaction
5272 -- Start of wraper code generated automatically by Debug code generator for Okl_Sif_Stream_Types_Pub.insert_sif_stream_types
5273 IF(IS_DEBUG_PROCEDURE_ON) THEN
5274 BEGIN
5275 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCSMB.pls call Okl_Sif_Stream_Types_Pub.insert_sif_stream_types ');
5276 END;
5277 END IF;
5278 Okl_Sif_Stream_Types_Pub.insert_sif_stream_types(
5279 p_api_version => p_api_version
5280 ,p_init_msg_list => p_init_msg_list
5281 ,x_return_status => l_return_status
5282 ,x_msg_count => x_msg_count
5283 ,x_msg_data => x_msg_data
5284 ,p_sitv_tbl => lp_sitv_tbl
5285 ,x_sitv_tbl => lx_sitv_tbl);
5286 IF(IS_DEBUG_PROCEDURE_ON) THEN
5287
5288
5289
5290 BEGIN
5291 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCSMB.pls call Okl_Sif_Stream_Types_Pub.insert_sif_stream_types ');
5292 END;
5293 END IF;
5294 -- End of wraper code generated automatically by Debug code generator for Okl_Sif_Stream_Types_Pub.insert_sif_stream_types
5295 IF l_return_status = G_RET_STS_ERROR THEN
5296 RAISE G_EXCEPTION_ERROR;
5297 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
5298 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5299 END IF;
5300 END IF;
5301 IF p_skip_prc_engine <> G_TRUE THEN
5302 Invoke_Pricing_Engine(
5303 p_api_version => p_api_version,
5304 p_init_msg_list => p_init_msg_list,
5305 p_sifv_rec => lx_sifv_rec,
5306 x_sifv_rec => lx_sifv_status_rec,
5307 x_return_status => l_return_status,
5308 x_msg_count => x_msg_count,
5309 x_msg_data => x_msg_data);
5310 IF l_return_status = G_RET_STS_ERROR THEN
5311 RAISE G_EXCEPTION_ERROR;
5312
5313 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
5314 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
5315 END IF;
5316 x_trans_id := lx_sifv_status_rec.transaction_number;
5317 x_trans_status := lx_sifv_status_rec.sis_code;
5318 ELSE
5319 x_trans_id := lx_sifv_rec.transaction_number;
5320 x_trans_status := lx_sifv_rec.sis_code;
5321 END IF;
5322 END IF;
5323 x_return_status := l_return_status;
5324 EXCEPTION
5325 WHEN G_EXCEPTION_ERROR THEN
5326 x_return_status := G_RET_STS_ERROR;
5327 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
5328 x_return_status := G_RET_STS_UNEXP_ERROR;
5329 WHEN OTHERS THEN
5330 -- store SQL error message on message stack
5331 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
5332 p_msg_name => G_UNEXPECTED_ERROR,
5333 p_token1 => G_SQLCODE_TOKEN,
5334
5335 p_token1_value => SQLCODE,
5336 p_token2 => G_SQLERRM_TOKEN,
5337 p_token2_value => SQLERRM);
5338 x_return_status := G_RET_STS_UNEXP_ERROR;
5339
5340 END Create_Streams_Lease_Quote;
5341
5342 PROCEDURE add_balance_information(x_sfev_tbl IN OUT NOCOPY sfev_tbl_type,
5343 x_return_status OUT NOCOPY VARCHAR2)
5344 IS
5345 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
5346 BEGIN
5347 FOR i in x_sfev_tbl.FIRST .. x_sfev_tbl.LAST
5348 LOOP
5349 IF x_sfev_tbl(i).balance_type_code is NULL
5350 THEN x_sfev_tbl(i).balance_type_code := G_BALANCE_PAYMENT;
5351 END IF;
5352 END LOOP;
5353 x_return_status := l_return_status;
5354 END add_balance_information;
5355
5356
5357 END Okl_Create_Streams_Pvt ;