[Home] [Help]
PACKAGE: APPS.OKL_LA_STREAM_PVT
Source
1 Package OKL_LA_STREAM_PVT AUTHID CURRENT_USER as
2 /* $Header: OKLRSGAS.pls 120.22.12020000.3 2012/12/07 06:12:44 bkatraga 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
273 --Added capital_reduction_percent by bkatraga for bug 15942693
274 -- get the Contract line info
275 -- Modified by kthiruva on 02-Sep-05
276 -- Added trade-in amount and expected funding date to the Select clause of the
277 -- cursor for Pricing Impacts in ESG
278 CURSOR l_line_rec_csr(chrid NUMBER, lnetype VARCHAR2)
279 IS
280 SELECT kle.id,
281 kle.oec,
282 kle.residual_code,
283 kle.capital_amount,
284 kle.delivered_date,
285 kle.date_funding_required,
286 kle.residual_grnty_amount,
287 kle.date_funding,
288 kle.residual_value,
289 kle.date_delivery_expected,
290 kle.orig_system_id1 old_line_id,
291 kle.amount,
292 kle.price_negotiated,
293 kle.start_date,
294 kle.end_date,
295 kle.orig_system_id1,
296 kle.fee_type,
297 kle.initial_direct_cost,
298 tl.item_description,
299 tl.name,
300 sts.ste_code,
301 --Added for Pricing Impact
302 kle.tradein_amount,
303 kle.date_funding_expected,
304 -- Added by RGOOTY: ESG Down Payment
305 kle.capital_reduction,
306 kle.capitalize_down_payment_yn,
307 kle.orig_contract_line_id, --sechawla 10-jul-09 PRB ESg enhancements : added
308 kle.capital_reduction_percent
309 FROM okl_k_lines_full_v kle,
310 okc_line_styles_b lse,
311 okc_k_lines_tl tl,
312 okc_statuses_b sts
313 WHERE kle.lse_id = lse.id
314 AND lse.lty_code = lnetype
315 AND tl.id = kle.id
316 AND tl.language = userenv('LANG')
317 AND kle.dnz_chr_id = chrid
318 AND sts.code = kle.sts_code
319 --Start of bug#3121708 modification BAKUCHIB
320 AND sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
321 --End of bug#3121708 modification BAKUCHIB
322 -- get the Contract fee line info
323 -- nikshah -- Bug # 5484903 Fixed,
324 -- Removed CURSOR l_fee_csr
325 -- get the Contract fee line info
326 CURSOR l_subfee_csr(kleId NUMBER,
327 lnetype VARCHAR2,
328 obcode VARCHAR2)
329 IS
330 SELECT kle.id,
331 kle.amount,
332 kle.price_negotiated,
333 kle.start_date,
334 kle.capital_amount
335 FROM okl_k_lines_full_v kle,
336 okc_line_styles_b LS,
337 okc_k_items cim
338 WHERE ls.id = kle.lse_id
339 AND ls.lty_code = lnetype
340 AND cim.jtot_object1_code = obcode
341 AND kle.id = cim.cle_id
342 AND cim.object1_id1 = kleId;
343 -- get strm fee type
344 CURSOR fee_strm_type_csr (kleid NUMBER,
345 linestyle VARCHAR2 )
346 IS
347 SELECT tl.name strm_name,
348 sty.capitalize_yn capitalize_yn,
349 kle.id line_id,
350 sty.id styp_id,
351 sty.stream_type_class stream_type_class
352 FROM okl_strm_type_tl tl,
353 okl_strm_type_v sty,
354 okc_k_items cim,
355 okl_k_lines_full_v kle,
356 okc_line_styles_b ls
357 WHERE tl.id = sty.id
358 AND tl.language = 'US'
359 AND cim.cle_id = kle.id
360 AND ls.id = kle.lse_id
361 AND ls.lty_code = 'FEE'
362 AND cim.object1_id1 = sty.id
363 AND cim.object1_id2 = '#'
364 AND kle.id = kleid;
365
366
367 --sechawla 15-Sep-2010 - Bug# 10045043 : added cursor : begin
368 CURSOR serv_strm_type_csr (kleid NUMBER,
369 linestyle VARCHAR2 )
370 IS
371 SELECT tl.name strm_name, sty.STREAM_TYPE_PURPOSE,
372 sty.capitalize_yn capitalize_yn,
373 kle.id line_id,
374 sty.id styp_id,
375 sty.stream_type_class stream_type_class
376 FROM okl_strm_type_tl tl,
377 okl_strm_type_v sty,
378 okl_party_payment_hdr pph,
379 okl_k_lines_full_v kle,
380 okc_line_styles_b ls
381 WHERE tl.id = sty.id
382 AND tl.language = 'US'
383 AND ls.id = kle.lse_id
384 AND ls.lty_code = 'SOLD_SERVICE'
385 AND pph.passthru_stream_type_id = sty.id
386 AND pph.cle_id = kle.id
387 AND pph.dnz_chr_id = kle.dnz_chr_id
388 AND kle.id = kleid;
389 --sechawla 15-Sep-2010 - Bug# 10045043 : added cursor : end
390
391 -- get Stream name
392 CURSOR strm_name_csr (styid NUMBER)
393 IS
394 SELECT tl.name name,
395 stm.stream_type_class stream_type_class,
396 stm.stream_type_purpose,
397 tl.description alloc_basis,
398 stm.capitalize_yn capitalize_yn,
399 stm.periodic_yn periodic_yn
400 FROM okl_strm_type_b stm,
401 okl_strm_type_tl tl
402 WHERE tl.id = stm.id
403 AND tl.language = 'US'
404 AND stm.id = styid;
405 -- get the install based location
406 CURSOR ib_csr (chrId NUMBER)
407 IS
408 SELECT DISTINCT hl.country country
409 FROM hz_locations hl,
410 hz_party_sites hps,
411 hz_party_site_uses hpsu,
412 okl_txl_itm_insts iti,
413 okc_line_styles_b lse_ib,
414 okc_k_lines_b cle_ib
415 WHERE cle_ib.dnz_chr_id = chrId
416 AND cle_ib.lse_id = lse_ib.id
417 AND lse_ib.lty_code = 'INST_ITEM'
418 AND iti.kle_id = cle_ib.id
419 AND iti.object_id1_new = hpsu.party_site_use_id
420 AND iti.object_id2_new = '#'
421 AND hpsu.party_site_id = hps.party_site_id
422 AND hps.location_id = hl.location_id;
423 -- get the Stream element information
424 CURSOR l_strmele_csr(chrId NUMBER,
425 styid NUMBER)
426 IS
427 SELECT ele.date_billed,
428 ele.stream_element_date,
429 ele.amount,
430 ele.accrued_yn,
431 ele.comments,
432 str.transaction_number,
433 str.sgn_code sgn_code,
434 ele.stm_id stm_id,
435 ele.se_line_number se_line_number
436 FROM okl_strm_elements ele,
437 okl_streams str
438 WHERE ele.stm_id = str.id
439 AND str.khr_id = chrId
440 AND str.sty_id = styid
441 AND upper(str.say_code) = 'CURR'
442 AND upper(str.active_yn) = 'Y'
443 ORDER BY ele.stream_element_date;
444 -- get the Streams
445 CURSOR strm_csr(chrId NUMBER,
446 kleId NUMBER,
447 status VARCHAR2,
448 pp VARCHAR2,
449 styId NUMBER) IS
450 SELECT str.Id strm_id,
451 str.sty_id sty_id,
452 str.sgn_code sgn_code,
453 str.sgn_code alloc_yn,
454 str.comments alloc_basis,
455 str.transaction_number trn_num
456 FROM okl_streams str
457 WHERE str.say_code = status
458 AND str.khr_id = chrId
459 AND NVL(str.kle_id, -1) = kleId
460 AND str.sty_id = styId
461 AND NVL(str.purpose_code, 'ORIGIN') = pp;
462 --Modified by RGOOTY for bug 8540694
463 -- get the streams
464 CURSOR strms_csr(chrId NUMBER,
465 status VARCHAR2,
466 pp VARCHAR2 )
467 IS
468 SELECT /*+ index(STR stm_khr_fk_i)*/ STR.ID STRM_ID,
469 STR.KLE_ID,
470 STR.STY_ID STY_ID,
471 STR.SGN_CODE SGN_CODE,
472 STR.SGN_CODE ALLOC_YN,
473 STR.COMMENTS ALLOC_BASIS,
474 STR.TRANSACTION_NUMBER TRN_NUM,
475 STR.DATE_CURRENT
476 FROM OKL_STREAMS STR
477 WHERE STR.SAY_CODE = status
478 AND NVL(STR.PURPOSE_CODE,'ORIGIN') = pp
479 AND STR.KHR_ID = chrId
480 AND (
481 STR.KLE_ID IN
482 (SELECT /*+ index(KLE OKC_K_LINES_B_U1 )*/ KLE.ID
483 FROM OKC_K_LINES_B KLE,
484 OKC_LINE_STYLES_B LSE,
485 OKC_STATUSES_B STS
486 WHERE KLE.LSE_ID = LSE.ID
487 AND KLE.DNZ_CHR_ID = chrId
488 AND STS.CODE = KLE.STS_CODE
489 AND LSE.LTY_CODE NOT IN ('INSURANCE')
490 )
491 )
492 UNION
493 SELECT /*+ index(STR stm_khr_fk_i)*/ STR.ID STRM_ID,
494 STR.KLE_ID,
495 STR.STY_ID STY_ID,
496 STR.SGN_CODE SGN_CODE,
497 STR.SGN_CODE ALLOC_YN,
498 STR.COMMENTS ALLOC_BASIS,
499 STR.TRANSACTION_NUMBER TRN_NUM,
500 STR.DATE_CURRENT
501 FROM OKL_STREAMS STR
502 WHERE STR.SAY_CODE = status
503 AND NVL(STR.PURPOSE_CODE,'ORIGIN') = pp
504 AND STR.KHR_ID = chrId
505 AND STR.KLE_ID IS NULL;
506 -- get the lookup
507 CURSOR fnd_lookups_csr( lkp_type VARCHAR2, mng VARCHAR2 )
508 IS
509 SELECT description,
510 lookup_code
511 FROM fnd_lookup_values
512 WHERE language = 'US'
513 AND lookup_type = lkp_type
514 AND meaning = mng;
515
516
517 --Added by kthriuva for Variable Rate Project.
518 --Cursor to fetch the OEC of all assets put together in the Contract
519 CURSOR total_oec_csr(p_chr_id NUMBER)
520 IS
521 SELECT sum(kle.oec) total_oec
522 FROM okl_k_lines_full_v kle,
523 okc_line_styles_b lse,
524 okc_k_lines_tl tl,
525 okc_statuses_b sts
526 WHERE kle.lse_id = lse.id
527 AND lse.lty_code = 'FREE_FORM1'
528 AND tl.id = kle.id
529 AND tl.language = userenv('LANG')
530 AND kle.dnz_chr_id = p_chr_id
531 AND sts.code = kle.sts_code
532 AND sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
533
534
535
536 TYPE strmele_tbl_type IS TABLE OF l_strmele_csr%ROWTYPE INDEX BY BINARY_INTEGER;
537
538 Procedure allocate_streams(
539 p_api_version IN NUMBER,
540 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
541 x_return_status OUT NOCOPY VARCHAR2,
542 x_msg_count OUT NOCOPY NUMBER,
543 x_msg_data OUT NOCOPY VARCHAR2,
544 p_chr_id IN NUMBER) ;
545
546 Procedure generate_reporting_streams(
547 p_api_version IN NUMBER,
548 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
549 p_chr_id IN VARCHAR2,
550 p_report_deal_type IN VARCHAR2,
551 p_generation_context IN VARCHAR2,
552 p_skip_prc_engine IN VARCHAR2,
553 x_return_status OUT NOCOPY VARCHAR2,
554 x_msg_count OUT NOCOPY NUMBER,
555 x_msg_data OUT NOCOPY VARCHAR2,
556 x_request_id IN OUT NOCOPY NUMBER,
557 x_trans_status OUT NOCOPY VARCHAR2,
558 p_orp_code IN VARCHAR2);
559
560 Procedure generate_streams(
561 p_api_version IN NUMBER,
562 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
563 p_chr_id IN VARCHAR2,
564 p_generation_context IN VARCHAR2,
565 p_skip_prc_engine IN VARCHAR2,
566 x_return_status OUT NOCOPY VARCHAR2,
567 x_msg_count OUT NOCOPY NUMBER,
568 x_msg_data OUT NOCOPY VARCHAR2,
569 x_request_id OUT NOCOPY NUMBER,
570 x_trans_status OUT NOCOPY VARCHAR2);
571
572 Procedure process_streams(
573 p_api_version IN NUMBER,
574 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
575 x_return_status OUT NOCOPY VARCHAR2,
576 x_msg_count OUT NOCOPY NUMBER,
577 x_msg_data OUT NOCOPY VARCHAR2,
578 p_chr_id IN VARCHAR2,
579 p_process_yn IN VARCHAR2,
580 p_chr_yields IN yields_rec_type,
581 p_source_call IN VARCHAR2 DEFAULT 'ESG');
582
583 Procedure update_contract_yields(
584 p_api_version IN NUMBER,
585 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
586 x_return_status OUT NOCOPY VARCHAR2,
587 x_msg_count OUT NOCOPY NUMBER,
588 x_msg_data OUT NOCOPY VARCHAR2,
589 p_chr_id IN VARCHAR2,
590 p_chr_yields IN yields_rec_type);
591
592 Procedure extract_params_lease(
593 p_api_version IN NUMBER,
594 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
595 p_chr_id IN VARCHAR2,
596 x_return_status OUT NOCOPY VARCHAR2,
597 x_msg_count OUT NOCOPY NUMBER,
598 x_msg_data OUT NOCOPY VARCHAR2,
599 x_csm_lease_header OUT NOCOPY okl_create_streams_pub.csm_lease_rec_type,
600 x_csm_one_off_fee_tbl OUT NOCOPY okl_create_streams_pub.csm_one_off_fee_tbl_type,
601 x_csm_periodic_expenses_tbl OUT NOCOPY okl_create_streams_pub.csm_periodic_expenses_tbl_type,
602 x_csm_yields_tbl OUT NOCOPY okl_create_streams_pub.csm_yields_tbl_type,
603 x_req_stream_types_tbl OUT NOCOPY okl_create_streams_pub.csm_stream_types_tbl_type,
604 x_csm_line_details_tbl OUT NOCOPY okl_create_streams_pub.csm_line_details_tbl_type,
605 x_rents_tbl OUT NOCOPY okl_create_streams_pub.csm_periodic_expenses_tbl_type,
606 p_orp_code IN VARCHAR2 DEFAULT NULL );
607
608 Procedure extract_params_loan(
609 p_api_version IN NUMBER,
610 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
611 p_chr_id IN VARCHAR2,
612 x_return_status OUT NOCOPY VARCHAR2,
613 x_msg_count OUT NOCOPY NUMBER,
614 x_msg_data OUT NOCOPY VARCHAR2,
615 x_csm_loan_header OUT NOCOPY okl_create_streams_pvt.csm_loan_rec_type,
616 x_csm_loan_lines_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_line_tbl_type,
617 x_csm_loan_levels_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_level_tbl_type,
618 x_csm_one_off_fee_tbl OUT NOCOPY okl_create_streams_pub.csm_one_off_fee_tbl_type,
619 x_csm_periodic_expenses_tbl OUT NOCOPY okl_create_streams_pub.csm_periodic_expenses_tbl_type,
620 x_csm_yields_tbl OUT NOCOPY okl_create_streams_pub.csm_yields_tbl_type,
621 x_csm_stream_types_tbl OUT NOCOPY okl_create_streams_pub.csm_stream_types_tbl_type,
622 p_orp_code IN VARCHAR2 DEFAULT NULL );
623
624 Procedure GEN_INTR_EXTR_STREAM (
625 p_api_version IN NUMBER,
626 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
627 x_return_status OUT NOCOPY VARCHAR2,
628 x_msg_count OUT NOCOPY NUMBER,
629 x_msg_data OUT NOCOPY VARCHAR2,
630 p_khr_id IN OKC_K_HEADERS_B.ID%TYPE,
631 p_generation_ctx_code IN VARCHAR2,
632 x_trx_number OUT NOCOPY NUMBER,
633 x_trx_status OUT NOCOPY VARCHAR2);
634
635 PROCEDURE validate_payments(p_api_version IN NUMBER,
636 p_init_msg_list IN VARCHAR2,
637 x_return_status OUT NOCOPY VARCHAR2,
638 x_msg_count OUT NOCOPY NUMBER,
639 x_msg_data OUT NOCOPY VARCHAR2,
640 p_khr_id IN OKC_K_HEADERS_B.ID%TYPE,
641 p_paym_tbl IN OKL_STREAM_GENERATOR_PVT.payment_tbl_type);
642
643 PROCEDURE get_so_residual_value(p_khr_id IN NUMBER,
644 p_kle_id IN NUMBER,
645 p_subside_yn IN VARCHAR2 DEFAULT 'N',
646 x_return_status OUT NOCOPY VARCHAR2,
647 x_residual_value OUT NOCOPY NUMBER,
648 x_start_date OUT NOCOPY DATE);
649
650 PROCEDURE get_so_asset_oec(p_khr_id IN NUMBER,
651 p_kle_id IN NUMBER,
652 p_subside_yn IN VARCHAR2 DEFAULT 'N',
653 x_return_status OUT NOCOPY VARCHAR2,
654 x_asset_oec OUT NOCOPY NUMBER,
655 x_start_date OUT NOCOPY DATE);
656
657 PROCEDURE extract_params_so(
658 p_api_version IN NUMBER,
659 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
660 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
661 p_cle_id IN OKC_K_LINES_B.ID%TYPE,
662 x_return_status OUT NOCOPY VARCHAR2,
663 x_msg_count OUT NOCOPY NUMBER,
664 x_msg_data OUT NOCOPY VARCHAR2,
665 x_csm_lease_header OUT NOCOPY okl_create_streams_pub.csm_lease_rec_type,
666 x_csm_one_off_fee_tbl OUT NOCOPY okl_create_streams_pub.csm_one_off_fee_tbl_type,
667 x_csm_periodic_expenses_tbl OUT NOCOPY okl_create_streams_pub.csm_periodic_expenses_tbl_type,
668 x_csm_yields_tbl OUT NOCOPY okl_create_streams_pub.csm_yields_tbl_type,
669 x_req_stream_types_tbl OUT NOCOPY okl_create_streams_pub.csm_stream_types_tbl_type,
670 x_csm_line_details_tbl OUT NOCOPY okl_create_streams_pub.csm_line_details_tbl_type,
671 x_rents_tbl OUT NOCOPY okl_create_streams_pub.csm_periodic_expenses_tbl_type,
672 x_csm_loan_header OUT NOCOPY okl_create_streams_pvt.csm_loan_rec_type,
673 x_csm_loan_lines_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_line_tbl_type,
674 x_csm_loan_levels_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_level_tbl_type);
675
676 --Added new procedure for the Variable Rate Project
677 Procedure extract_params_loan_paydown(
678 p_api_version IN NUMBER,
679 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
680 p_chr_id IN VARCHAR2,
681 p_deal_type IN VARCHAR2,
682 p_paydown_type IN VARCHAR2,
683 p_paydown_date IN DATE,
684 p_paydown_amount IN NUMBER,
685 p_balance_type_code IN VARCHAR2,
686 x_return_status OUT NOCOPY VARCHAR2,
687 x_msg_count OUT NOCOPY NUMBER,
688 x_msg_data OUT NOCOPY VARCHAR2,
689 x_csm_loan_header OUT NOCOPY okl_create_streams_pvt.csm_loan_rec_type,
690 x_csm_loan_lines_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_line_tbl_type,
691 x_csm_loan_levels_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_level_tbl_type,
692 x_csm_one_off_fee_tbl OUT NOCOPY okl_create_streams_pub.csm_one_off_fee_tbl_type,
693 x_csm_periodic_expenses_tbl OUT NOCOPY okl_create_streams_pub.csm_periodic_expenses_tbl_type,
694 x_csm_yields_tbl OUT NOCOPY okl_create_streams_pub.csm_yields_tbl_type,
695 x_csm_stream_types_tbl OUT NOCOPY okl_create_streams_pub.csm_stream_types_tbl_type);
696
697 --Added by kthiruva for Bug 5161075
698 Procedure extract_params_loan_reamort(
699 p_api_version IN NUMBER,
700 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
701 p_chr_id IN VARCHAR2,
702 x_return_status OUT NOCOPY VARCHAR2,
703 x_msg_count OUT NOCOPY NUMBER,
704 x_msg_data OUT NOCOPY VARCHAR2,
705 x_csm_loan_header OUT NOCOPY okl_create_streams_pvt.csm_loan_rec_type,
706 x_csm_loan_lines_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_line_tbl_type,
707 x_csm_loan_levels_tbl OUT NOCOPY okl_create_streams_pvt.csm_loan_level_tbl_type,
708 x_csm_one_off_fee_tbl OUT NOCOPY okl_create_streams_pub.csm_one_off_fee_tbl_type,
709 x_csm_periodic_expenses_tbl OUT NOCOPY okl_create_streams_pub.csm_periodic_expenses_tbl_type,
710 x_csm_yields_tbl OUT NOCOPY okl_create_streams_pub.csm_yields_tbl_type,
711 x_csm_stream_types_tbl OUT NOCOPY okl_create_streams_pub.csm_stream_types_tbl_type);
712
713 --Added by srsreeni for bug 5699923
714 PROCEDURE RECREATE_TMT_LN_STRMS(
715 p_api_version IN NUMBER,
716 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
717 x_return_status OUT NOCOPY VARCHAR2,
718 x_msg_count OUT NOCOPY NUMBER,
719 x_msg_data OUT NOCOPY VARCHAR2,
720 p_chr_id IN VARCHAR2,
721 p_trx_number IN NUMBER,
722 p_source_call IN VARCHAR2 DEFAULT 'ESG');
723
724
725 --Added by bkatraga for bug 8399461
726 PROCEDURE get_pth_fee_due_amount(
727 p_chr_id IN NUMBER,
728 p_kle_id IN NUMBER,
729 p_prev_payout_date IN DATE,
730 p_payout_date IN DATE,
731 x_bill_amount OUT NOCOPY NUMBER,
732 x_return_status OUT NOCOPY VARCHAR2);
733
734 /************************************************************************
735 * API to upgrade the ESG Contracts to support the
736 * Prospective Rebooking
737 *************************************************************************/
738 PROCEDURE upgrade_esg_khr_for_prb(
739 p_chr_id IN VARCHAR2
740 ,x_return_status OUT NOCOPY VARCHAR2
741 ,x_msg_count OUT NOCOPY NUMBER
742 ,x_msg_data OUT NOCOPY VARCHAR2
743 ,x_request_id OUT NOCOPY NUMBER
744 ,x_trans_status OUT NOCOPY VARCHAR2
745 ,x_rep_request_id OUT NOCOPY NUMBER
746 ,x_rep_trans_status OUT NOCOPY VARCHAR2 );
747
748 /************************************************************************
749 * API to generate passthrough expense accrual streams
750 * For ISG and ESG contracts --Bug 8624532 by NIKSHAH
751 *************************************************************************/
752 PROCEDURE GENERATE_PASSTHRU_EXP_STREAMS(
753 p_api_version IN NUMBER
754 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
755 ,P_CHR_ID IN NUMBER
756 ,P_PURPOSE_CODE IN VARCHAR2
757 ,x_return_status OUT NOCOPY VARCHAR2
758 ,x_msg_count OUT NOCOPY NUMBER
759 ,x_msg_data OUT NOCOPY VARCHAR2 );
760
761
762
763
764 --sechawla 15-Sep-2010 - Bug# 10045043 : added procedure : begin
765 /************************************************************************
766 * API to generate passthrough service expense accrual streams
767 * For ISG and ESG contracts
768 *************************************************************************/
769
770 PROCEDURE GEN_PASSTHRU_SERV_EXP_STREAMS
771 (
772 p_api_version IN NUMBER,
773 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
774 P_CHR_ID IN NUMBER,
775 P_PURPOSE_CODE IN VARCHAR2,
776 x_return_status OUT NOCOPY VARCHAR2,
777 x_msg_count OUT NOCOPY NUMBER,
778 x_msg_data OUT NOCOPY VARCHAR2
779 );
780 --sechawla 15-Sep-2010 - Bug# 10045043 : added procedure : end
781
782 /************************************************************************
783 * API to identify and retrieve the khr_id
784 * of a original contract if the input contract is REBOOK copy
785 * Else input contract is returned as output
786 * Bug 12944979: Publishing this API.
787 *************************************************************************/
788 PROCEDURE retrive_orig_hdr_id( p_chr_id IN VARCHAR2
789 , x_chr_id OUT NOCOPY VARCHAR2
790 );
791
792 End OKL_LA_STREAM_PVT;