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