[Home] [Help]
2874: FROM okc_k_lines_b cleb,
2875: okc_rule_groups_b rgpb,
2876: okc_rules_b rulb,
2877: okc_rules_b rulb2,
2878: okl_strm_type_b styb
2879: WHERE rgpb.chr_id IS NULL
2880: AND rgpb.dnz_chr_id = cleb.dnz_chr_id
2881: AND rgpb.cle_id = cleb.id
2882: AND cleb.dnz_chr_id = p_khr_id
3330:
3331: -- Bug 5055714. Commenting below derivation. Using utility provided instead.
3332: -- CURSOR principal_bal_csr(p_ctr_id NUMBER, p_start_date DATE, p_end_date DATE, p_prin_bal_id NUMBER) IS
3333: -- SELECT SUM(ste.amount)
3334: -- FROM OKL_STRM_TYPE_B sty,
3335: -- OKL_STREAMS stm,
3336: -- OKL_STRM_ELEMENTS ste
3337: -- WHERE stm.khr_id = p_ctr_id
3338: -- AND stm.active_yn = 'Y'
3345: -- cursor for retrieveing earlier principal balance amount if principal balance
3346: -- for given period is not found
3347: -- CURSOR prior_prin_bal_csr(p_ctr_id NUMBER, p_start_date DATE, p_prin_bal_id NUMBER) IS
3348: -- SELECT SUM(ste.amount)
3349: -- FROM OKL_STRM_TYPE_B sty,
3350: -- OKL_STREAMS stm,
3351: -- OKL_STRM_ELEMENTS ste
3352: -- WHERE stm.khr_id = p_ctr_id
3353: -- AND stm.active_yn = 'Y'
3355: -- AND sty.id = p_prin_bal_id
3356: -- AND stm.sty_id = sty.id
3357: -- AND ste.stm_id = stm.id
3358: -- AND ste.stream_element_date = (SELECT MAX(stream_element_date)
3359: -- FROM OKL_STRM_TYPE_B sty,
3360: -- OKL_STREAMS stm,
3361: -- OKL_STRM_ELEMENTS ste
3362: -- WHERE stm.khr_id = p_ctr_id
3363: -- AND stm.active_yn = 'Y'
3535: CURSOR get_unb_rec_csr(p_ctr_id NUMBER, p_date DATE, p_sty_id NUMBER) IS
3536: SELECT SUM(ste.amount)
3537: FROM OKL_STREAMS stm,
3538: OKL_STRM_ELEMENTS ste,
3539: OKL_STRM_TYPE_B sty
3540: WHERE stm.khr_id = p_ctr_id
3541: AND stm.sty_id = sty.id
3542: AND sty.id = p_sty_id
3543: AND stm.active_yn = 'Y'
3660: CURSOR get_unearn_rev_csr (p_ctr_id NUMBER, p_date DATE, p_rent_sty_id NUMBER, p_lease_inc_sty_id NUMBER) IS
3661: SELECT SUM(ste.amount)
3662: FROM OKL_STREAMS stm,
3663: OKL_STRM_ELEMENTS ste,
3664: OKL_STRM_TYPE_B sty
3665: WHERE stm.khr_id = p_ctr_id
3666: AND stm.sty_id = sty.id
3667: AND sty.id = p_lease_inc_sty_id
3668: AND stm.active_yn = 'Y'
3671: AND ste.stream_element_date >=
3672: (SELECT TRUNC(MIN(ste.stream_element_date),'MM')
3673: FROM OKL_STREAMS stm,
3674: OKL_STRM_ELEMENTS ste,
3675: OKL_STRM_TYPE_B sty
3676: WHERE stm.khr_id = p_ctr_id
3677: AND stm.sty_id = sty.id
3678: AND sty.id = l_rent_sty_id
3679: AND stm.active_yn = 'Y'
3683: AND ste.stream_element_date <=
3684: (SELECT LAST_DAY(MAX(ste.stream_element_date))
3685: FROM OKL_STREAMS stm,
3686: OKL_STRM_ELEMENTS ste,
3687: OKL_STRM_TYPE_B sty
3688: WHERE stm.khr_id = p_ctr_id
3689: AND stm.sty_id = sty.id
3690: AND sty.id = l_rent_sty_id
3691: AND stm.active_yn = 'Y'
3889: -- cursor to get unaccrued subsidy for a contract
3890: CURSOR unaccrued_subsidy_csr(p_ctr_id OKL_K_HEADERS_FULL_V.ID%TYPE, p_date DATE, p_subsidy_inc_id NUMBER) IS
3891: SELECT SUM(ste.amount)
3892: FROM OKL_STRM_ELEMENTS ste,
3893: OKL_STRM_TYPE_B sty,
3894: OKL_STREAMS stm
3895: WHERE stm.khr_id = p_ctr_id
3896: AND stm.sty_id = sty.id
3897: AND sty.id = p_subsidy_inc_id
4794: cp_contract_line_id NUMBER,
4795: cp_stream_type_id NUMBER) IS
4796: SELECT SUM (NVL (ste.amount, 0)) amount_due
4797: FROM okl_streams stm,
4798: okl_strm_type_b sty, -- SECHAWLA 04-MAR-03 Added this table to get the billable_yn flag
4799: okl_strm_elements ste
4800: WHERE stm.khr_id = cp_contract_id
4801: AND stm.kle_id = cp_contract_line_id
4802: AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
4895: cp_contract_line_id NUMBER,
4896: cp_stream_type_id NUMBER) IS
4897: SELECT SUM (NVL (ste.amount, 0)) amount_due
4898: FROM okl_streams stm,
4899: okl_strm_type_b sty, -- SECHAWLA 04-MAR-03 Added this table to get the billable_yn flag
4900: okl_strm_elements ste
4901: WHERE stm.khr_id = cp_contract_id
4902: AND stm.kle_id = cp_contract_line_id
4903: AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
4919: cp_stream_type_id NUMBER,
4920: cp_trx_date DATE) IS
4921: SELECT SUM (NVL (ste.amount, 0)) amount_due
4922: FROM okl_streams stm,
4923: okl_strm_type_b sty,
4924: okl_strm_elements ste
4925: WHERE stm.khr_id = cp_contract_id
4926: AND stm.kle_id = cp_contract_line_id
4927: AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
6626: AND rg.cle_id = c_line_id
6627: AND rg.rgd_code = 'LALEVL'
6628: AND rl.rule_information_category = 'LASLH'
6629: AND rl.dnz_chr_id = c_khr_id
6630: AND rl.object1_id1=(SELECT id FROM okl_strm_type_b WHERE code='RENT');
6631:
6632: CURSOR c_get_freq(c_line_id NUMBER,c_khr_id NUMBER,c_rent_slh_id NUMBER)
6633: IS
6634: SELECT rl.object1_id1 frequency
8786: cp_date DATE,
8787: cp_sty_id NUMBER) IS
8788: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
8789: FROM okl_streams stm,
8790: okl_strm_type_b sty,
8791: okl_strm_elements ste
8792: ,okl_pool_contents pocb
8793: WHERE stm.khr_id = cp_contract_id
8794: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
8813: cp_sty_id NUMBER,
8814: cp_date DATE) IS --gboomina bug 4775555
8815: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
8816: FROM okl_streams stm,
8817: okl_strm_type_b sty,
8818: okl_strm_elements ste
8819: ,okl_pool_contents pocb
8820: WHERE stm.khr_id = cp_contract_id
8821: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
8837: cp_date DATE,
8838: cp_sty_id NUMBER) IS
8839: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
8840: FROM okl_streams stm,
8841: okl_strm_type_b sty,
8842: okl_strm_elements ste
8843: ,okl_pool_contents pocb
8844: WHERE stm.khr_id = cp_contract_id
8845: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
8863: cp_sty_id NUMBER,
8864: cp_date DATE) IS
8865: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
8866: FROM okl_streams stm,
8867: okl_strm_type_b sty,
8868: okl_strm_elements ste
8869: ,okl_pool_contents pocb
8870: WHERE stm.khr_id = cp_contract_id
8871: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
9057: cp_contract_line_id NUMBER,
9058: cp_sty_id NUMBER) IS
9059: SELECT NVL(SUM (NVL (ste.amount, 0)), 0) amount_due
9060: FROM okl_streams stm,
9061: okl_strm_type_b sty,
9062: okl_strm_elements ste,
9063: okl_pool_contents pocb
9064: WHERE stm.khr_id = cp_contract_id
9065: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
9082: cp_contract_line_id NUMBER,
9083: cp_sty_id NUMBER) IS
9084: SELECT NVL(SUM (NVL (ste.amount, 0)), 0) amount_due
9085: FROM okl_streams stm,
9086: okl_strm_type_b sty,
9087: okl_strm_elements ste,
9088: okl_pool_contents pocb
9089: WHERE stm.khr_id = cp_contract_id
9090: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
9208: cp_date DATE,
9209: cp_sty_id NUMBER) IS
9210: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
9211: FROM okl_streams stm,
9212: okl_strm_type_b sty,
9213: okl_strm_elements ste
9214: ,okl_pool_contents pocb
9215: WHERE stm.khr_id = cp_contract_id
9216: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
9233: cp_sty_id NUMBER,
9234: cp_date DATE) IS --gboomina bug 4775555
9235: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
9236: FROM okl_streams stm,
9237: okl_strm_type_b sty,
9238: okl_strm_elements ste
9239: ,okl_pool_contents pocb
9240: WHERE stm.khr_id = cp_contract_id
9241: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
9256: cp_date DATE,
9257: cp_sty_id NUMBER) IS
9258: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
9259: FROM okl_streams stm,
9260: okl_strm_type_b sty,
9261: okl_strm_elements ste,
9262: okl_pool_contents pocb
9263: WHERE stm.khr_id = cp_contract_id
9264: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
9281: cp_sty_id NUMBER,
9282: cp_date DATE) IS
9283: SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
9284: FROM okl_streams stm,
9285: okl_strm_type_b sty,
9286: okl_strm_elements ste,
9287: okl_pool_contents pocb
9288: WHERE stm.khr_id = cp_contract_id
9289: AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
9705: cp_fee_type VARCHAR2,
9706: cp_date DATE ) IS -- rmunjulu EDAT
9707: SELECT SUM(ste.amount) amount
9708: FROM okl_streams stm,
9709: okl_strm_type_b sty,
9710: okc_k_lines_b kle,
9711: okc_statuses_b kls,
9712: okc_line_styles_b lse,
9713: okl_strm_elements ste,
9737: cp_fee_type VARCHAR2,
9738: cp_date DATE ) IS -- rmunjulu EDAT
9739: SELECT SUM(ste.amount) amount
9740: FROM okl_streams stm
9741: ,okl_strm_type_b sty
9742: ,okc_k_lines_b kle
9743: ,okc_statuses_b kls
9744: ,okc_line_styles_b lse
9745: ,okl_strm_elements ste
9771: cp_asset_id NUMBER,
9772: cp_date DATE ) IS -- rmunjulu EDAT
9773: SELECT SUM(ste.amount) amount
9774: FROM okl_streams stm
9775: ,okl_strm_type_b sty
9776: ,okc_k_lines_b kle
9777: ,okc_statuses_b kls
9778: ,okc_line_styles_b lse
9779: ,okl_strm_elements ste
10059: ;
10060:
10061: CURSOR c_refund_subsidy(p_cle_id okc_k_lines_b.id%TYPE,
10062: p_date_terminated okc_k_lines_b.date_terminated%TYPE,
10063: p_sty_id okl_strm_type_b.id%TYPE) IS
10064: SELECT NVL(SUM(selb.amount),0)
10065: FROM
10066: okl_streams stmb,
10067: okl_strm_elements selb
10074: AND stmb.sty_id = p_sty_id
10075: ;
10076:
10077: r_subsidy_line c_subsidy_lines%ROWTYPE;
10078: l_dependent_sty_id OKL_STRM_TYPE_B.ID%TYPE; -- Bug 6353756
10079:
10080: BEGIN
10081:
10082: FOR r_subsidy_line IN c_subsidy_lines(p_contract_line_id) LOOP
10410: TAR.KHR_ID
10411: FROM okl_cnsld_ar_strms_b TAR
10412: WHERE TAR.receivables_invoice_id <> -99999
10413: AND exists (SELECT STY.ID
10414: FROM okl_strm_type_b STY
10415: WHERE STY.STREAM_TYPE_PURPOSE = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
10416: AND STY.ID = TAR.STY_ID);
10417:
10418: l_payment_tot NUMBER := 0;
10551: AND ccg.credit_id = p_credit_id
10552: AND ARL.receivables_invoice_id <> -99999
10553: AND ARL.interface_line_context = 'OKL_CONTRACTS'
10554: AND exists (SELECT STY.ID
10555: FROM okl_strm_type_b STY
10556: WHERE STY.STREAM_TYPE_PURPOSE = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
10557: AND STY.ID = ARL.STY_ID);
10558:
10559: l_payment_tot NUMBER := 0;
11712: cp_trx_date DATE) IS
11713: SELECT SUM (NVL (ste.amount, 0)) amount_due
11714: --FROM okl_streams stm,
11715: FROM okl_streams_rep_v stm,
11716: okl_strm_type_b sty,
11717: okl_strm_elements ste
11718: WHERE stm.khr_id = cp_contract_id
11719: AND stm.kle_id = cp_contract_line_id
11720: AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
11735: cp_stream_type_id NUMBER,
11736: cp_trx_date DATE) IS
11737: SELECT SUM (NVL (ste.amount, 0)) amount_due
11738: FROM okl_streams stm,
11739: okl_strm_type_b sty, -- Added this table to get the billable_yn flag
11740: okl_strm_elements ste
11741: WHERE stm.khr_id = cp_contract_id
11742: AND stm.kle_id = cp_contract_line_id
11743: AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
11771: cp_stream_type_id NUMBER,
11772: cp_trx_date DATE) IS
11773: SELECT nvl(ste.amount, 0) amount
11774: FROM okl_streams stm,
11775: okl_strm_type_b sty,
11776: okl_strm_elements ste
11777: WHERE stm.khr_id = cp_contract_id
11778: AND stm.kle_id = cp_contract_line_id
11779: AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
12849: CURSOR cr_unbilled_streams(c_contract_id IN NUMBER) IS
12850: SELECT NVL(sum(sel.amount),0)
12851: FROM okl_strm_elements sel,
12852: okl_streams stm,
12853: okl_strm_type_b sty
12854: WHERE stm.say_code = 'CURR'
12855: AND stm.active_yn = 'Y'
12856: AND stm.purpose_code is NULL
12857: AND stm.khr_id = c_contract_id