[Home] [Help]
PACKAGE: APPS.OKL_LA_STREAM_PVT
Source
1 Package OKL_LA_STREAM_PVT as
2 /* $Header: OKLRSGAS.pls 120.13.12010000.2 2008/10/01 21:59:02 rkuttiya ship $ */
3 -- Global variables
4 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_LA_STREAM_PVT';
5 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
6
7 -- Store the yields types
8 TYPE yields_type IS RECORD (
9 pre_Tax_yield okl_k_headers.pre_tax_yield%type,
10 after_tax_yield okl_k_headers.after_tax_yield%type,
11 pre_tax_irr okl_k_headers.pre_tax_irr%type,
12 after_tax_irr okl_k_headers.after_tax_irr%type,
13 implicit_interest_rate okl_k_headers.implicit_interest_rate%type,
14 sub_pre_Tax_yield okl_k_headers.sub_pre_tax_yield%type,
15 sub_after_tax_yield okl_k_headers.sub_after_tax_yield%type,
16 sub_pre_tax_irr okl_k_headers.sub_pre_tax_irr%TYPE,
17 sub_after_tax_irr okl_k_headers.sub_after_tax_irr%type,
18 sub_impl_interest_rate okl_k_headers.sub_impl_interest_rate%type);
19 SUBTYPE yields_rec_type IS yields_type;
20
21 TYPE strm_rec IS RECORD (
22 id OKL_STRM_TYPE_B.ID%TYPE ,
23 pricing_name OKL_ST_GEN_TMPT_LNS.PRICING_NAME%TYPE);
24 SUBTYPE strm_rec_type IS strm_rec;
25 TYPE okl_strm_type_id_tbl_type IS TABLE OF strm_rec
26 INDEX BY BINARY_INTEGER;
27
28
29 CURSOR l_hdr_pdt_csr(chrId NUMBER)
30 IS
31 SELECT chr.orig_system_source_code,
32 chr.start_date,
33 chr.end_date,
34 chr.template_yn,
35 chr.authoring_org_id,
36 khr.expected_delivery_date,
37 chr.inv_organization_id,
38 khr.deal_type,
39 pdt.id pid,
40 NVL(pdt.reporting_pdt_id, -1) report_pdt_id,
41 chr.currency_code currency_code,
42 khr.term_duration term
43 FROM okc_k_headers_v chr,
44 okl_k_headers khr,
45 okl_products_v pdt
46 WHERE khr.id = chr.id
47 AND chr.id = chrId
48 AND khr.pdt_id = pdt.id(+);
49
50
51
52 -- Get the stream type id
53 CURSOR l_strmid_csr (strmName VARCHAR2)
54 IS
55 SELECT id styid
56 FROM okl_strm_type_tl
57 WHERE LANGUAGE = 'US'
58 AND NAME = strmName;
59 -- get the prorate convention code
60 CURSOR l_adrconv_csr (bkCode VARCHAR2,
61 assNo VARCHAR2)
62 IS
63 SELECT fa.prorate_convention_code
64 FROM fa_books fa,
65 okx_asset_lines_v xle
66 WHERE fa.transaction_header_id_out IS NULL
67 AND fa.book_type_code = bkCode
68 AND fa.asset_id = xle.asset_id
69 AND xle.asset_number = assNo;
70 -- get the setup values for tax book and coporate book
71 CURSOR l_txtrans_csr (Book Varchar2)
72 IS
73 SELECT trns.value,
74 books.book_type_code
75 FROM okl_sgn_translations trns,
76 fa_book_controls books
77 WHERE trns.jtot_object1_code = 'FA_BOOK_CONTROLS'
78 AND trns.object1_id1 = books.book_type_code
79 AND books.book_type_code = Book
80 AND trns.sgn_code = 'STMP';
81 -- Get the transaction types
82 CURSOR Transaction_Type_csr (p_transaction_type IN okl_trx_types_v.name%TYPE)
83 IS
84 SELECT id trx_try_id
85 FROM okl_trx_types_tl
86 WHERE NAME = p_transaction_type
87 AND LANGUAGE = 'US';
88 -- get the transaction contract information
89 CURSOR trx_csr(khrId NUMBER,
90 tcntype VARCHAR2,
91 status VARCHAR2)
92 IS
93 SELECT txh.ID headertransid
94 FROM okl_trx_contracts txh
95 WHERE txh.tcn_type = tcntype
96 AND txh.khr_id = khrId
97 --rkuttiya added for 12.1.1 Multi GAAP
98 AND txh.representation_type = 'PRIMARY'
99 --
100 AND txh.tsu_code = status;
101 -- get the txl transaction information
102 CURSOR l_tx_csr(ass VARCHAR2)
103 IS
104 SELECT txl.life_in_months,
105 txl.corporate_book,
106 txl.deprn_method,
107 txl.in_service_date,
108 txl.salvage_value,
109 txl.percent_salvage_value,
110 txl.depreciation_cost,
111 mth.id1,
112 ct.prorate_convention_code
113 FROM okl_txl_assets_b txl,
114 okx_asst_dep_methods_v mth,
115 okx_ast_ct_bk_dfs_v ct
116 WHERE mth.method_code = txl.deprn_method
117 AND mth.life_in_months = txl.life_in_months
118 AND ct.category_id = txl.depreciation_id
119 AND ct.book_type_code = txl.corporate_book
120 AND txl.asset_number = ass
121 -- Start of Bug#3388812 Modification - BAKUCHIB
122 AND txl.in_service_date BETWEEN ct.start_dpis AND NVL(ct.end_dpis,txl.in_service_date);
123 -- End of Bug#3388812 Modification - BAKUCHIB
124 -- get the txd transaction information
125 CURSOR l_txd_csr(ass VARCHAR2)
126 IS
127 SELECT txd.cost,
128 txd.deprn_method_tax,
129 txd.life_in_months_tax,
130 txd.salvage_value,
131 txd.tax_book,
132 mth.id1
133 FROM okl_txd_assets_v txd,
134 okx_asst_dep_methods_v mth
135 WHERE mth.method_code = txd.deprn_method_tax
136 AND mth.life_in_months = txd.life_in_months_tax
137 AND txd.asset_number = ass;
138 -- get the stream id
139 CURSOR l_strm_id_csr(khrid NUMBER)
140 IS
141 SELECT lsm.id
142 FROM okl_streams lsm
143 WHERE lsm.khr_id = khrid;
144 -- get the Header rule information
145 CURSOR l_hdrrl_csr(rgcode okc_rule_groups_b.rgd_code%TYPE,
146 rlcat okc_rules_b.rule_information_category%TYPE,
147 chrId NUMBER)
148 IS
149 SELECT crl.object1_id1,
150 crl.rule_information1,
151 crl.rule_information2,
152 crl.rule_information3,
153 crl.rule_information4,
154 crl.rule_information5,
155 crl.rule_information6,
156 crl.rule_information10,
157 crl.rule_information13,
158 crl.rule_information11
159 FROM okc_rule_groups_b crg,
160 okc_rules_b crl
161 WHERE crl.rgp_id = crg.id
162 AND crg.rgd_code = rgcode
163 AND crl.rule_information_category = rlcat
164 AND crg.dnz_chr_id = chrId;
165 -- get the self referencing Line based rule information
166 CURSOR l_rl_csr(rlgpId NUMBER,
167 rgcode okc_rule_groups_b.rgd_code%TYPE,
168 rlcat okc_rules_b.rule_information_category%TYPE,
169 chrId NUMBER,
170 cleId NUMBER )
171 IS
172 SELECT crl.object1_id1,
173 crl.rule_information1,
174 crl.rule_information2,
175 crl.rule_information3,
176 crl.rule_information5,
177 crl.rule_information6,
178 --start bug#2757289 bakuchib
179 crl.rule_information7,
180 crl.rule_information8,
181 --end bug#2757289 bakuchib
182 crl.rule_information13,
183 crl.rule_information10,
184 DECODE(crl.object1_id1,'M',1,'Q',3,'S',6,'A',12) decoded_object1_id1
185 FROM okc_rule_groups_b crg,
186 okc_rules_b crl
187 WHERE crl.rgp_id = crg.id
188 AND crl.object2_id1 = rlgpId
189 AND crg.rgd_code = rgcode
190 AND crl.rule_information_category = rlcat
191 AND crg.dnz_chr_id = chrId
192 AND crg.cle_id = cleId
193 -- ORDER BY crl.rule_information1;
194 --start bug#2757289 bakuchib
195 ORDER BY FND_DATE.canonical_to_date(crl.rule_information2);
196 --end bug#2757289 bakuchib
197 -- get the Line rule information
198 CURSOR l_rl_csr2(rgcode okc_rule_groups_b.rgd_code%TYPE,
199 rlcat okc_rules_b.rule_information_category%TYPE,
200 chrId NUMBER,
201 cleId NUMBER)
202 IS
203 SELECT crl.id slh_id,
204 crl.object1_id1,
205 crl.rule_information1,
206 crl.rule_information2,
207 crl.rule_information3,
208 crl.rule_information5,
209 crl.rule_information6,
210 --start bug#2757289 bakuchib
211 crl.rule_information7,
212 crl.rule_information8,
213 crl.rule_information13,
214 --end bug#2757289 bakuchib
215 crl.rule_information10
216 FROM okc_rule_groups_b crg,
217 okc_rules_b crl
218 WHERE crl.rgp_id = crg.id
219 AND crg.rgd_code = rgcode
220 AND crl.rule_information_category = rlcat
221 AND crg.dnz_chr_id = chrId
222 AND crg.cle_id = cleId
223 ORDER BY crl.rule_information1;
224 -- get the Line rule information
225 CURSOR l_rl_csr1(rgcode okc_rule_groups_b.rgd_code%TYPE,
226 rlcat okc_rules_b.rule_information_category%TYPE,
227 chrId NUMBER,
228 cleId NUMBER )
229 IS
230 SELECT crl.id slh_id,
231 crl.object1_id1,
232 crl.rule_information1,
233 crl.rule_information2,
234 crl.rule_information3,
235 crl.rule_information5,
236 crl.rule_information6,
237 --start bug#2757289 bakuchib
238 crl.rule_information7,
239 crl.rule_information8,
240 crl.rule_information13,
241 --end bug#2757289 bakuchib
242 crl.rule_information10
243 FROM okc_rule_groups_b crg,
244 okc_rules_b crl
245 WHERE crl.rgp_id = crg.id
246 AND crg.rgd_code = rgcode
247 AND crl.rule_information_category = rlcat
248 AND crg.dnz_chr_id = chrId
249 AND crg.cle_id = cleId
250 ORDER BY crl.rule_information1;
251 -- get the Contract Header info
252 CURSOR l_hdr_csr(chrId NUMBER)
253 IS
254 SELECT chr.orig_system_source_code,
255 chr.start_date,
256 chr.end_date,
257 chr.template_yn,
258 chr.authoring_org_id,
259 khr.expected_delivery_date,
260 chr.inv_organization_id,
261 khr.deal_type,
262 pdt.id pid,
263 NVL(pdt.reporting_pdt_id, -1) report_pdt_id,
264 chr.currency_code currency_code,
265 khr.term_duration term
266 FROM okc_k_headers_v chr,
267 okl_k_headers khr,
268 okl_products_v pdt
269 WHERE khr.id = chr.id
270 AND chr.id = chrId
271 AND khr.pdt_id = pdt.id(+);
272 -- get the Contract line info
273 -- Modified by kthiruva on 02-Sep-05
274 -- Added trade-in amount and expected funding date to the Select clause of the
275 -- cursor for Pricing Impacts in ESG
276 CURSOR l_line_rec_csr(chrid NUMBER, lnetype VARCHAR2)
277 IS
278 SELECT kle.id,
279 kle.oec,
280 kle.residual_code,
281 kle.capital_amount,
282 kle.delivered_date,
283 kle.date_funding_required,
284 kle.residual_grnty_amount,
285 kle.date_funding,
286 kle.residual_value,
287 kle.date_delivery_expected,
288 kle.orig_system_id1 old_line_id,
289 kle.amount,
290 kle.price_negotiated,
291 kle.start_date,
292 kle.end_date,
293 kle.orig_system_id1,
294 kle.fee_type,
295 kle.initial_direct_cost,
296 tl.item_description,
297 tl.name,
298 sts.ste_code,
299 --Added for Pricing Impact
300 kle.tradein_amount,
301 kle.date_funding_expected,
302 -- Added by RGOOTY: ESG Down Payment
303 kle.capital_reduction,
304 kle.capitalize_down_payment_yn
305 FROM okl_k_lines_full_v kle,
306 okc_line_styles_b lse,
307 okc_k_lines_tl tl,
308 okc_statuses_b sts
309 WHERE kle.lse_id = lse.id
310 AND lse.lty_code = lnetype
311 AND tl.id = kle.id
312 AND tl.language = userenv('LANG')
313 AND kle.dnz_chr_id = chrid
314 AND sts.code = kle.sts_code
315 --Start of bug#3121708 modification BAKUCHIB
316 AND sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
317 --End of bug#3121708 modification BAKUCHIB
318 -- get the Contract fee line info
319 -- nikshah -- Bug # 5484903 Fixed,
320 -- Removed CURSOR l_fee_csr
321 -- get the Contract fee line info
322 CURSOR l_subfee_csr(kleId NUMBER,
323 lnetype VARCHAR2,
324 obcode VARCHAR2)
325 IS
326 SELECT kle.id,
327 kle.amount,
328 kle.price_negotiated,
329 kle.start_date,
330 kle.capital_amount
331 FROM okl_k_lines_full_v kle,
332 okc_line_styles_b LS,
333 okc_k_items cim
334 WHERE ls.id = kle.lse_id
335 AND ls.lty_code = lnetype
336 AND cim.jtot_object1_code = obcode
337 AND kle.id = cim.cle_id
338 AND cim.object1_id1 = kleId;
339 -- get strm fee type
340 CURSOR fee_strm_type_csr (kleid NUMBER,
341 linestyle VARCHAR2 )
342 IS
343 SELECT tl.name strm_name,
344 sty.capitalize_yn capitalize_yn,
345 kle.id line_id,
346 sty.id styp_id,
347 sty.stream_type_class stream_type_class
348 FROM okl_strm_type_tl tl,
349 okl_strm_type_v sty,
350 okc_k_items cim,
351 okl_k_lines_full_v kle,
352 okc_line_styles_b ls
353 WHERE tl.id = sty.id
354 AND tl.language = 'US'
355 AND cim.cle_id = kle.id
356 AND ls.id = kle.lse_id
357 AND ls.lty_code = 'FEE'
358 AND cim.object1_id1 = sty.id
359 AND cim.object1_id2 = '#'
360 AND kle.id = kleid;
361 -- get Stream name
362 CURSOR strm_name_csr (styid NUMBER)
363 IS
364 SELECT tl.name name,
365 stm.stream_type_class stream_type_class,
366 stm.stream_type_purpose,
367 tl.description alloc_basis,
368 stm.capitalize_yn capitalize_yn,
369 stm.periodic_yn periodic_yn
370 FROM okl_strm_type_b stm,
371 okl_strm_type_tl tl
372 WHERE tl.id = stm.id
373 AND tl.language = 'US'
374 AND stm.id = styid;
375 -- get the install based location
376 CURSOR ib_csr (chrId NUMBER)
377 IS
378 SELECT DISTINCT hl.country country
379 FROM hz_locations hl,
380 hz_party_sites hps,
381 hz_party_site_uses hpsu,
382 okl_txl_itm_insts iti,
383 okc_line_styles_b lse_ib,
384 okc_k_lines_b cle_ib
385 WHERE cle_ib.dnz_chr_id = chrId
386 AND cle_ib.lse_id = lse_ib.id
387 AND lse_ib.lty_code = 'INST_ITEM'
388 AND iti.kle_id = cle_ib.id
389 AND iti.object_id1_new = hpsu.party_site_use_id
390 AND iti.object_id2_new = '#'
391 AND hpsu.party_site_id = hps.party_site_id
392 AND hps.location_id = hl.location_id;
393 -- get the Stream element information
394 CURSOR l_strmele_csr(chrId NUMBER,
395 styid NUMBER)
396 IS
397 SELECT ele.date_billed,
398 ele.stream_element_date,
399 ele.amount,
400 ele.accrued_yn,
401 ele.comments,
402 str.transaction_number,
403 str.sgn_code sgn_code,
404 ele.stm_id stm_id,
405 ele.se_line_number se_line_number
406 FROM okl_strm_elements ele,
407 okl_streams str
408 WHERE ele.stm_id = str.id
409 AND str.khr_id = chrId
410 AND str.sty_id = styid
411 AND upper(str.say_code) = 'CURR'
412 AND upper(str.active_yn) = 'Y'
413 ORDER BY ele.stream_element_date;
414 -- get the Streams
415 CURSOR strm_csr(chrId NUMBER,
416 kleId NUMBER,
417 status VARCHAR2,
418 pp VARCHAR2,
419 styId NUMBER) IS
420 SELECT str.Id strm_id,
421 str.sty_id sty_id,
422 str.sgn_code sgn_code,
423 str.sgn_code alloc_yn,
424 str.comments alloc_basis,
425 str.transaction_number trn_num
426 FROM okl_streams str
427 WHERE str.say_code = status
428 AND str.khr_id = chrId
429 AND NVL(str.kle_id, -1) = kleId
430 AND str.sty_id = styId
431 AND NVL(str.purpose_code, 'ORIGIN') = pp;
432 -- get the streams
433 CURSOR strms_csr(chrId NUMBER,
434 status VARCHAR2,
435 pp VARCHAR2 )
436 IS
437 SELECT str.id strm_id,
438 str.kle_id,
439 str.sty_id sty_id,
440 str.sgn_code sgn_code,
441 str.sgn_code alloc_yn,
442 str.comments alloc_basis,
443 str.transaction_number trn_num,
444 str.date_current
445 FROM okl_streams str
446 WHERE str.say_code = status
447 AND NVL(str.purpose_code,'ORIGIN') = pp
448 AND str.khr_id = chrId
449 AND (NVL( str.kle_id, -1) = -1 OR
450 str.kle_id IN (SELECT kle.id
451 FROM okl_k_lines_full_v kle,
452 okc_line_styles_b lse,
453 okc_statuses_b sts
454 WHERE KLE.LSE_ID = LSE.ID
455 AND kle.dnz_chr_id = chrId
456 AND sts.code = kle.sts_code
457 AND lse.lty_code NOT IN ('INSURANCE')));
458 -- get the lookup
459 CURSOR fnd_lookups_csr( lkp_type VARCHAR2, mng VARCHAR2 )
460 IS
461 SELECT description,
462 lookup_code
463 FROM fnd_lookup_values
464 WHERE language = 'US'
465 AND lookup_type = lkp_type
466 AND meaning = mng;
467
468
469 --Added by kthriuva for Variable Rate Project.
470 --Cursor to fetch the OEC of all assets put together in the Contract
471 CURSOR total_oec_csr(p_chr_id NUMBER)
472 IS
473 SELECT sum(kle.oec) total_oec
474 FROM okl_k_lines_full_v kle,
475 okc_line_styles_b lse,
476 okc_k_lines_tl tl,
477 okc_statuses_b sts
478 WHERE kle.lse_id = lse.id
479 AND lse.lty_code = 'FREE_FORM1'
480 AND tl.id = kle.id
481 AND tl.language = userenv('LANG')
482 AND kle.dnz_chr_id = p_chr_id
483 AND sts.code = kle.sts_code
484 AND sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
485
486 TYPE strmele_tbl_type IS TABLE OF l_strmele_csr%ROWTYPE INDEX BY BINARY_INTEGER;
487
488 Procedure allocate_streams(
489 p_api_version IN NUMBER,
490 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
491 x_return_status OUT NOCOPY VARCHAR2,
492 x_msg_count OUT NOCOPY NUMBER,
493 x_msg_data OUT NOCOPY VARCHAR2,
494 p_chr_id IN NUMBER) ;
495
496 Procedure generate_reporting_streams(
497 p_api_version IN NUMBER,
498 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
499 p_chr_id IN VARCHAR2,
500 p_report_deal_type IN VARCHAR2,
501 p_generation_context IN VARCHAR2,
502 p_skip_prc_engine IN VARCHAR2,
503 x_return_status OUT NOCOPY VARCHAR2,
504 x_msg_count OUT NOCOPY NUMBER,
505 x_msg_data OUT NOCOPY VARCHAR2,
506 x_request_id IN OUT NOCOPY NUMBER,
507 x_trans_status OUT NOCOPY VARCHAR2);
508
509 Procedure generate_streams(
510 p_api_version IN NUMBER,
511 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
512 p_chr_id IN VARCHAR2,
513 p_generation_context IN VARCHAR2,
514 p_skip_prc_engine IN VARCHAR2,
515 x_return_status OUT NOCOPY VARCHAR2,
516 x_msg_count OUT NOCOPY NUMBER,
517 x_msg_data OUT NOCOPY VARCHAR2,
518 x_request_id OUT NOCOPY NUMBER,
519 x_trans_status OUT NOCOPY VARCHAR2);
520
521 Procedure process_streams(
522 p_api_version IN NUMBER,
523 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
524 x_return_status OUT NOCOPY VARCHAR2,
525 x_msg_count OUT NOCOPY NUMBER,
526 x_msg_data OUT NOCOPY VARCHAR2,
527 p_chr_id IN VARCHAR2,
528 p_process_yn IN VARCHAR2,
529 p_chr_yields IN yields_rec_type,
530 p_source_call IN VARCHAR2 DEFAULT 'ESG');
531
532 Procedure update_contract_yields(
533 p_api_version IN NUMBER,
534 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
535 x_return_status OUT NOCOPY VARCHAR2,
536 x_msg_count OUT NOCOPY NUMBER,
537 x_msg_data OUT NOCOPY VARCHAR2,
538 p_chr_id IN VARCHAR2,
539 p_chr_yields IN yields_rec_type);
540
541 Procedure extract_params_lease(
542 p_api_version IN NUMBER,
543 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
544 p_chr_id IN VARCHAR2,
545 x_return_status OUT NOCOPY VARCHAR2,
546 x_msg_count OUT NOCOPY NUMBER,
547 x_msg_data OUT NOCOPY VARCHAR2,
548 x_csm_lease_header OUT NOCOPY okl_create_streams_pub.csm_lease_rec_type,
549 x_csm_one_off_fee_tbl OUT NOCOPY okl_create_streams_pub.csm_one_off_fee_tbl_type,
550 x_csm_periodic_expenses_tbl OUT NOCOPY okl_create_streams_pub.csm_periodic_expenses_tbl_type,
551 x_csm_yields_tbl OUT NOCOPY okl_create_streams_pub.csm_yields_tbl_type,
552 x_req_stream_types_tbl OUT NOCOPY okl_create_streams_pub.csm_stream_types_tbl_type,
553 x_csm_line_details_tbl OUT NOCOPY okl_create_streams_pub.csm_line_details_tbl_type,
554 x_rents_tbl OUT NOCOPY okl_create_streams_pub.csm_periodic_expenses_tbl_type);
555
556 Procedure extract_params_loan(
557 p_api_version IN NUMBER,
558 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
559 p_chr_id IN VARCHAR2,
560 x_return_status OUT NOCOPY VARCHAR2,
561 x_msg_count OUT NOCOPY NUMBER,
562 x_msg_data OUT NOCOPY VARCHAR2,
563 x_csm_loan_header OUT NOCOPY okl_create_streams_pvt.csm_loan_rec_type,
564 x_csm_loan_lines_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_line_tbl_type,
565 x_csm_loan_levels_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_level_tbl_type,
566 x_csm_one_off_fee_tbl OUT NOCOPY okl_create_streams_pub.csm_one_off_fee_tbl_type,
567 x_csm_periodic_expenses_tbl OUT NOCOPY okl_create_streams_pub.csm_periodic_expenses_tbl_type,
568 x_csm_yields_tbl OUT NOCOPY okl_create_streams_pub.csm_yields_tbl_type,
569 x_csm_stream_types_tbl OUT NOCOPY okl_create_streams_pub.csm_stream_types_tbl_type);
570
571 Procedure GEN_INTR_EXTR_STREAM (
572 p_api_version IN NUMBER,
573 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
574 x_return_status OUT NOCOPY VARCHAR2,
575 x_msg_count OUT NOCOPY NUMBER,
576 x_msg_data OUT NOCOPY VARCHAR2,
577 p_khr_id IN OKC_K_HEADERS_B.ID%TYPE,
578 p_generation_ctx_code IN VARCHAR2,
579 x_trx_number OUT NOCOPY NUMBER,
580 x_trx_status OUT NOCOPY VARCHAR2);
581
582 PROCEDURE validate_payments(p_api_version IN NUMBER,
583 p_init_msg_list IN VARCHAR2,
584 x_return_status OUT NOCOPY VARCHAR2,
585 x_msg_count OUT NOCOPY NUMBER,
586 x_msg_data OUT NOCOPY VARCHAR2,
587 p_khr_id IN OKC_K_HEADERS_B.ID%TYPE,
588 p_paym_tbl IN OKL_STREAM_GENERATOR_PVT.payment_tbl_type);
589
590 PROCEDURE get_so_residual_value(p_khr_id IN NUMBER,
591 p_kle_id IN NUMBER,
592 p_subside_yn IN VARCHAR2 DEFAULT 'N',
593 x_return_status OUT NOCOPY VARCHAR2,
594 x_residual_value OUT NOCOPY NUMBER,
595 x_start_date OUT NOCOPY DATE);
596
597 PROCEDURE get_so_asset_oec(p_khr_id IN NUMBER,
598 p_kle_id IN NUMBER,
599 p_subside_yn IN VARCHAR2 DEFAULT 'N',
600 x_return_status OUT NOCOPY VARCHAR2,
601 x_asset_oec OUT NOCOPY NUMBER,
602 x_start_date OUT NOCOPY DATE);
603
604 PROCEDURE extract_params_so(
605 p_api_version IN NUMBER,
606 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
607 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
608 p_cle_id IN OKC_K_LINES_B.ID%TYPE,
609 x_return_status OUT NOCOPY VARCHAR2,
610 x_msg_count OUT NOCOPY NUMBER,
611 x_msg_data OUT NOCOPY VARCHAR2,
612 x_csm_lease_header OUT NOCOPY okl_create_streams_pub.csm_lease_rec_type,
613 x_csm_one_off_fee_tbl OUT NOCOPY okl_create_streams_pub.csm_one_off_fee_tbl_type,
614 x_csm_periodic_expenses_tbl OUT NOCOPY okl_create_streams_pub.csm_periodic_expenses_tbl_type,
615 x_csm_yields_tbl OUT NOCOPY okl_create_streams_pub.csm_yields_tbl_type,
616 x_req_stream_types_tbl OUT NOCOPY okl_create_streams_pub.csm_stream_types_tbl_type,
617 x_csm_line_details_tbl OUT NOCOPY okl_create_streams_pub.csm_line_details_tbl_type,
618 x_rents_tbl OUT NOCOPY okl_create_streams_pub.csm_periodic_expenses_tbl_type,
619 x_csm_loan_header OUT NOCOPY okl_create_streams_pvt.csm_loan_rec_type,
620 x_csm_loan_lines_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_line_tbl_type,
621 x_csm_loan_levels_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_level_tbl_type);
622
623 --Added new procedure for the Variable Rate Project
624 Procedure extract_params_loan_paydown(
625 p_api_version IN NUMBER,
626 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
627 p_chr_id IN VARCHAR2,
628 p_deal_type IN VARCHAR2,
629 p_paydown_type IN VARCHAR2,
630 p_paydown_date IN DATE,
631 p_paydown_amount IN NUMBER,
632 p_balance_type_code IN VARCHAR2,
633 x_return_status OUT NOCOPY VARCHAR2,
634 x_msg_count OUT NOCOPY NUMBER,
635 x_msg_data OUT NOCOPY VARCHAR2,
636 x_csm_loan_header OUT NOCOPY okl_create_streams_pvt.csm_loan_rec_type,
637 x_csm_loan_lines_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_line_tbl_type,
638 x_csm_loan_levels_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_level_tbl_type,
639 x_csm_one_off_fee_tbl OUT NOCOPY okl_create_streams_pub.csm_one_off_fee_tbl_type,
640 x_csm_periodic_expenses_tbl OUT NOCOPY okl_create_streams_pub.csm_periodic_expenses_tbl_type,
641 x_csm_yields_tbl OUT NOCOPY okl_create_streams_pub.csm_yields_tbl_type,
642 x_csm_stream_types_tbl OUT NOCOPY okl_create_streams_pub.csm_stream_types_tbl_type);
643
644 --Added by kthiruva for Bug 5161075
645 Procedure extract_params_loan_reamort(
646 p_api_version IN NUMBER,
647 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
648 p_chr_id IN VARCHAR2,
649 x_return_status OUT NOCOPY VARCHAR2,
650 x_msg_count OUT NOCOPY NUMBER,
651 x_msg_data OUT NOCOPY VARCHAR2,
652 x_csm_loan_header OUT NOCOPY okl_create_streams_pvt.csm_loan_rec_type,
653 x_csm_loan_lines_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_line_tbl_type,
654 x_csm_loan_levels_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_level_tbl_type,
655 x_csm_one_off_fee_tbl OUT NOCOPY okl_create_streams_pub.csm_one_off_fee_tbl_type,
656 x_csm_periodic_expenses_tbl OUT NOCOPY okl_create_streams_pub.csm_periodic_expenses_tbl_type,
657 x_csm_yields_tbl OUT NOCOPY okl_create_streams_pub.csm_yields_tbl_type,
658 x_csm_stream_types_tbl OUT NOCOPY okl_create_streams_pub.csm_stream_types_tbl_type);
659
660 --Added by srsreeni for bug 5699923
661 PROCEDURE RECREATE_TMT_LN_STRMS(
662 p_api_version IN NUMBER,
663 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
664 x_return_status OUT NOCOPY VARCHAR2,
665 x_msg_count OUT NOCOPY NUMBER,
666 x_msg_data OUT NOCOPY VARCHAR2,
667 p_chr_id IN VARCHAR2,
668 p_trx_number IN NUMBER,
669 p_source_call IN VARCHAR2 DEFAULT 'ESG');
670
671 End OKL_LA_STREAM_PVT;