[Home] [Help]
3338: FROM okc_k_lines_b cleb,
3339: okc_rule_groups_b rgpb,
3340: okc_rules_b rulb,
3341: okc_rules_b rulb2,
3342: okl_strm_type_b styb
3343: WHERE rgpb.chr_id IS NULL
3344: AND rgpb.dnz_chr_id = cleb.dnz_chr_id
3345: AND rgpb.cle_id = cleb.id
3346: AND cleb.dnz_chr_id = p_khr_id
3794:
3795: -- Bug 5055714. Commenting below derivation. Using utility provided instead.
3796: -- CURSOR principal_bal_csr(p_ctr_id NUMBER, p_start_date DATE, p_end_date DATE, p_prin_bal_id NUMBER) IS
3797: -- SELECT SUM(ste.amount)
3798: -- FROM OKL_STRM_TYPE_B sty,
3799: -- OKL_STREAMS stm,
3800: -- OKL_STRM_ELEMENTS ste
3801: -- WHERE stm.khr_id = p_ctr_id
3802: -- AND stm.active_yn = 'Y'
3809: -- cursor for retrieveing earlier principal balance amount if principal balance
3810: -- for given period is not found
3811: -- CURSOR prior_prin_bal_csr(p_ctr_id NUMBER, p_start_date DATE, p_prin_bal_id NUMBER) IS
3812: -- SELECT SUM(ste.amount)
3813: -- FROM OKL_STRM_TYPE_B sty,
3814: -- OKL_STREAMS stm,
3815: -- OKL_STRM_ELEMENTS ste
3816: -- WHERE stm.khr_id = p_ctr_id
3817: -- AND stm.active_yn = 'Y'
3819: -- AND sty.id = p_prin_bal_id
3820: -- AND stm.sty_id = sty.id
3821: -- AND ste.stm_id = stm.id
3822: -- AND ste.stream_element_date = (SELECT MAX(stream_element_date)
3823: -- FROM OKL_STRM_TYPE_B sty,
3824: -- OKL_STREAMS stm,
3825: -- OKL_STRM_ELEMENTS ste
3826: -- WHERE stm.khr_id = p_ctr_id
3827: -- AND stm.active_yn = 'Y'
4006: CURSOR get_unb_rec_csr(p_ctr_id NUMBER, p_date DATE, p_sty_id NUMBER) IS
4007: SELECT SUM(ste.amount)
4008: FROM OKL_STREAMS stm,
4009: OKL_STRM_ELEMENTS ste,
4010: OKL_STRM_TYPE_B sty
4011: WHERE stm.khr_id = p_ctr_id
4012: AND stm.sty_id = sty.id
4013: AND sty.id = p_sty_id
4014: AND stm.active_yn = 'Y'
4131: CURSOR get_unearn_rev_csr (p_ctr_id NUMBER, p_date DATE, p_rent_sty_id NUMBER, p_lease_inc_sty_id NUMBER) IS
4132: SELECT SUM(ste.amount)
4133: FROM OKL_STREAMS stm,
4134: OKL_STRM_ELEMENTS ste,
4135: OKL_STRM_TYPE_B sty
4136: WHERE stm.khr_id = p_ctr_id
4137: AND stm.sty_id = sty.id
4138: AND sty.id = p_lease_inc_sty_id
4139: AND stm.active_yn = 'Y'
4142: AND ste.stream_element_date >=
4143: (SELECT TRUNC(MIN(ste.stream_element_date),'MM')
4144: FROM OKL_STREAMS stm,
4145: OKL_STRM_ELEMENTS ste,
4146: OKL_STRM_TYPE_B sty
4147: WHERE stm.khr_id = p_ctr_id
4148: AND stm.sty_id = sty.id
4149: AND sty.id = l_rent_sty_id
4150: AND stm.active_yn = 'Y'
4154: AND ste.stream_element_date <=
4155: (SELECT LAST_DAY(MAX(ste.stream_element_date))
4156: FROM OKL_STREAMS stm,
4157: OKL_STRM_ELEMENTS ste,
4158: OKL_STRM_TYPE_B sty
4159: WHERE stm.khr_id = p_ctr_id
4160: AND stm.sty_id = sty.id
4161: AND sty.id = l_rent_sty_id
4162: AND stm.active_yn = 'Y'
4169: CURSOR get_unearn_rev_nr_csr (p_ctr_id NUMBER, p_date DATE, p_rent_sty_id NUMBER, p_lease_inc_sty_id NUMBER, p_line_type VARCHAR2) IS
4170: SELECT SUM(ste.amount)
4171: FROM OKL_STREAMS stm,
4172: OKL_STRM_ELEMENTS ste,
4173: OKL_STRM_TYPE_B sty,
4174: okc_k_lines_b cle,
4175: okl_k_lines kle
4176: WHERE stm.khr_id = p_ctr_id
4177: AND stm.sty_id = sty.id
4182: AND ste.stream_element_date >=
4183: (SELECT TRUNC(MIN(ste.stream_element_date),'MM')
4184: FROM OKL_STREAMS stm,
4185: OKL_STRM_ELEMENTS ste,
4186: OKL_STRM_TYPE_B sty,
4187: okc_k_lines_b cle,
4188: okl_k_lines kle
4189: WHERE stm.khr_id = p_ctr_id
4190: AND stm.sty_id = sty.id
4201: AND ste.stream_element_date <=
4202: (SELECT LAST_DAY(MAX(ste.stream_element_date))
4203: FROM OKL_STREAMS stm,
4204: OKL_STRM_ELEMENTS ste,
4205: OKL_STRM_TYPE_B sty,
4206: okc_k_lines_b cle,
4207: okl_k_lines kle
4208: WHERE stm.khr_id = p_ctr_id
4209: AND stm.sty_id = sty.id
4452: -- cursor to get unaccrued subsidy for a contract
4453: CURSOR unaccrued_subsidy_csr(p_ctr_id OKL_K_HEADERS_FULL_V.ID%TYPE, p_date DATE, p_subsidy_inc_id NUMBER) IS
4454: SELECT SUM(ste.amount)
4455: FROM OKL_STRM_ELEMENTS ste,
4456: OKL_STRM_TYPE_B sty,
4457: OKL_STREAMS stm
4458: WHERE stm.khr_id = p_ctr_id
4459: AND stm.sty_id = sty.id
4460: AND sty.id = p_subsidy_inc_id
5379: cp_stream_type_id NUMBER,
5380: cp_quote_eff_date DATE ) IS
5381: SELECT SUM (NVL (ste.amount, 0)) amount_due
5382: FROM okl_streams stm,
5383: okl_strm_type_b sty, -- SECHAWLA 04-MAR-03 Added this table to get the billable_yn flag
5384: okl_strm_elements ste
5385: WHERE stm.khr_id = cp_contract_id
5386: AND stm.kle_id = cp_contract_line_id
5387: AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
5493: cp_contract_line_id NUMBER,
5494: cp_stream_type_id NUMBER) IS
5495: SELECT SUM (NVL (ste.amount, 0)) amount_due
5496: FROM okl_streams stm,
5497: okl_strm_type_b sty, -- SECHAWLA 04-MAR-03 Added this table to get the billable_yn flag
5498: okl_strm_elements ste
5499: WHERE stm.khr_id = cp_contract_id
5500: AND stm.kle_id = cp_contract_line_id
5501: AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
5517: cp_stream_type_id NUMBER,
5518: cp_trx_date DATE) IS
5519: SELECT SUM (NVL (ste.amount, 0)) amount_due
5520: FROM okl_streams stm,
5521: okl_strm_type_b sty,
5522: okl_strm_elements ste
5523: WHERE stm.khr_id = cp_contract_id
5524: AND stm.kle_id = cp_contract_line_id
5525: AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
7224: AND rg.cle_id = c_line_id
7225: AND rg.rgd_code = 'LALEVL'
7226: AND rl.rule_information_category = 'LASLH'
7227: AND rl.dnz_chr_id = c_khr_id
7228: AND rl.object1_id1=(SELECT id FROM okl_strm_type_b WHERE code='RENT');
7229:
7230: CURSOR c_get_freq(c_line_id NUMBER,c_khr_id NUMBER,c_rent_slh_id NUMBER)
7231: IS
7232: SELECT rl.object1_id1 frequency
8291: l_total_percent NUMBER := 0;
8292: l_try_rsn OKL_POOL_TRANSACTIONS.TRANSACTION_REASON%TYPE;
8293:
8294: --Bug# 12557486
8295: l_rent_sty_purpose okl_strm_type_b.stream_type_purpose%TYPE := 'RENT';
8296: l_residual_sty_purpose okl_strm_type_b.stream_type_purpose%TYPE := 'RESIDUAL_VALUE';
8297: l_loan_pymt_sty_purpose okl_strm_type_b.stream_type_purpose%TYPE := 'LOAN_PAYMENT';
8298:
8299: BEGIN
8292: l_try_rsn OKL_POOL_TRANSACTIONS.TRANSACTION_REASON%TYPE;
8293:
8294: --Bug# 12557486
8295: l_rent_sty_purpose okl_strm_type_b.stream_type_purpose%TYPE := 'RENT';
8296: l_residual_sty_purpose okl_strm_type_b.stream_type_purpose%TYPE := 'RESIDUAL_VALUE';
8297: l_loan_pymt_sty_purpose okl_strm_type_b.stream_type_purpose%TYPE := 'LOAN_PAYMENT';
8298:
8299: BEGIN
8300:
8293:
8294: --Bug# 12557486
8295: l_rent_sty_purpose okl_strm_type_b.stream_type_purpose%TYPE := 'RENT';
8296: l_residual_sty_purpose okl_strm_type_b.stream_type_purpose%TYPE := 'RESIDUAL_VALUE';
8297: l_loan_pymt_sty_purpose okl_strm_type_b.stream_type_purpose%TYPE := 'LOAN_PAYMENT';
8298:
8299: BEGIN
8300:
8301: l_total_percent := 0;
9636: cp_date DATE,
9637: cp_sty_id NUMBER) IS
9638: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
9639: FROM okl_streams stm,
9640: okl_strm_type_b sty,
9641: okl_strm_elements ste
9642: ,okl_pool_contents pocb
9643: WHERE stm.khr_id = cp_contract_id
9644: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
9663: cp_sty_id NUMBER,
9664: cp_date DATE) IS --gboomina bug 4775555
9665: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
9666: FROM okl_streams stm,
9667: okl_strm_type_b sty,
9668: okl_strm_elements ste
9669: -- ,okl_pool_contents pocb
9670: WHERE stm.khr_id = cp_contract_id
9671: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
9688: cp_date DATE,
9689: cp_sty_id NUMBER) IS
9690: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
9691: FROM okl_streams stm,
9692: okl_strm_type_b sty,
9693: okl_strm_elements ste
9694: ,okl_pool_contents pocb
9695: WHERE stm.khr_id = cp_contract_id
9696: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
9714: cp_sty_id NUMBER,
9715: cp_date DATE) IS
9716: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
9717: FROM okl_streams stm,
9718: okl_strm_type_b sty,
9719: okl_strm_elements ste
9720: --,okl_pool_contents pocb
9721: WHERE stm.khr_id = cp_contract_id
9722: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
9909: cp_contract_line_id NUMBER,
9910: cp_sty_id NUMBER) IS
9911: SELECT NVL(SUM (NVL (ste.amount, 0)), 0) amount_due
9912: FROM okl_streams stm,
9913: okl_strm_type_b sty,
9914: okl_strm_elements ste,
9915: okl_pool_contents pocb
9916: WHERE stm.khr_id = cp_contract_id
9917: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
9934: cp_contract_line_id NUMBER,
9935: cp_sty_id NUMBER) IS
9936: SELECT NVL(SUM (NVL (ste.amount, 0)), 0) amount_due
9937: FROM okl_streams stm,
9938: okl_strm_type_b sty,
9939: okl_strm_elements ste,
9940: okl_pool_contents pocb
9941: WHERE stm.khr_id = cp_contract_id
9942: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
10060: cp_date DATE,
10061: cp_sty_id NUMBER) IS
10062: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
10063: FROM okl_streams stm,
10064: okl_strm_type_b sty,
10065: okl_strm_elements ste
10066: ,okl_pool_contents pocb
10067: WHERE stm.khr_id = cp_contract_id
10068: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
10085: cp_sty_id NUMBER,
10086: cp_date DATE) IS --gboomina bug 4775555
10087: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
10088: FROM okl_streams stm,
10089: okl_strm_type_b sty,
10090: okl_strm_elements ste
10091: ,okl_pool_contents pocb
10092: WHERE stm.khr_id = cp_contract_id
10093: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
10108: cp_date DATE,
10109: cp_sty_id NUMBER) IS
10110: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
10111: FROM okl_streams stm,
10112: okl_strm_type_b sty,
10113: okl_strm_elements ste,
10114: okl_pool_contents pocb
10115: WHERE stm.khr_id = cp_contract_id
10116: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
10133: cp_sty_id NUMBER,
10134: cp_date DATE) IS
10135: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
10136: FROM okl_streams stm,
10137: okl_strm_type_b sty,
10138: okl_strm_elements ste,
10139: okl_pool_contents pocb
10140: WHERE stm.khr_id = cp_contract_id
10141: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
10562: cp_fee_type VARCHAR2,
10563: cp_date DATE ) IS -- rmunjulu EDAT
10564: SELECT SUM(ste.amount) amount
10565: FROM okl_streams stm,
10566: okl_strm_type_b sty,
10567: okc_k_lines_b kle,
10568: okc_statuses_b kls,
10569: okc_line_styles_b lse,
10570: okl_strm_elements ste,
10594: cp_fee_type VARCHAR2,
10595: cp_date DATE ) IS -- rmunjulu EDAT
10596: SELECT SUM(ste.amount) amount
10597: FROM okl_streams stm
10598: ,okl_strm_type_b sty
10599: ,okc_k_lines_b kle
10600: ,okc_statuses_b kls
10601: ,okc_line_styles_b lse
10602: ,okl_strm_elements ste
10628: cp_asset_id NUMBER,
10629: cp_date DATE ) IS -- rmunjulu EDAT
10630: SELECT SUM(ste.amount) amount
10631: FROM okl_streams stm
10632: ,okl_strm_type_b sty
10633: ,okc_k_lines_b kle
10634: ,okc_statuses_b kls
10635: ,okc_line_styles_b lse
10636: ,okl_strm_elements ste
10916: ;
10917:
10918: CURSOR c_refund_subsidy(p_cle_id okc_k_lines_b.id%TYPE,
10919: p_date_terminated okc_k_lines_b.date_terminated%TYPE,
10920: p_sty_id okl_strm_type_b.id%TYPE) IS
10921: SELECT NVL(SUM(selb.amount),0)
10922: FROM
10923: okl_streams stmb,
10924: okl_strm_elements selb
10931: AND stmb.sty_id = p_sty_id
10932: ;
10933:
10934: r_subsidy_line c_subsidy_lines%ROWTYPE;
10935: l_dependent_sty_id OKL_STRM_TYPE_B.ID%TYPE; -- Bug 6353756
10936:
10937: BEGIN
10938:
10939: FOR r_subsidy_line IN c_subsidy_lines(p_contract_line_id) LOOP
11347: TAR.KHR_ID
11348: FROM okl_cnsld_ar_strms_b TAR
11349: WHERE TAR.receivables_invoice_id <> -99999
11350: AND exists (SELECT STY.ID
11351: FROM okl_strm_type_b STY
11352: WHERE STY.STREAM_TYPE_PURPOSE = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
11353: AND STY.ID = TAR.STY_ID);
11354:
11355: l_payment_tot NUMBER := 0;
11490: AND ccg.credit_id = p_credit_id
11491: AND ARL.receivables_invoice_id <> -99999
11492: AND ARL.interface_line_context = 'OKL_CONTRACTS'
11493: AND exists (SELECT STY.ID
11494: FROM okl_strm_type_b STY
11495: WHERE STY.STREAM_TYPE_PURPOSE = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
11496: AND STY.ID = ARL.STY_ID);
11497:
11498: l_payment_tot NUMBER := 0;
12800: cp_trx_date DATE) IS
12801: SELECT SUM (NVL (ste.amount, 0)) amount_due
12802: --FROM okl_streams stm,
12803: FROM okl_streams_rep_v stm,
12804: okl_strm_type_b sty,
12805: okl_strm_elements ste
12806: WHERE stm.khr_id = cp_contract_id
12807: AND stm.kle_id = cp_contract_line_id
12808: AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
12823: cp_stream_type_id NUMBER,
12824: cp_trx_date DATE) IS
12825: SELECT SUM (NVL (ste.amount, 0)) amount_due
12826: FROM okl_streams stm,
12827: okl_strm_type_b sty, -- Added this table to get the billable_yn flag
12828: okl_strm_elements ste
12829: WHERE stm.khr_id = cp_contract_id
12830: AND stm.kle_id = cp_contract_line_id
12831: AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
12859: cp_stream_type_id NUMBER,
12860: cp_trx_date DATE) IS
12861: SELECT nvl(ste.amount, 0) amount
12862: FROM okl_streams stm,
12863: okl_strm_type_b sty,
12864: okl_strm_elements ste
12865: WHERE stm.khr_id = cp_contract_id
12866: AND stm.kle_id = cp_contract_line_id
12867: AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
14081: CURSOR cr_unbilled_streams(c_contract_id IN NUMBER) IS
14082: SELECT NVL(sum(sel.amount),0)
14083: FROM okl_strm_elements sel,
14084: okl_streams stm,
14085: okl_strm_type_b sty
14086: WHERE stm.say_code = 'CURR'
14087: AND stm.active_yn = 'Y'
14088: AND stm.purpose_code is NULL
14089: AND stm.khr_id = c_contract_id