[Home] [Help]
110:
111: CURSOR c_total_amount_paid (p_sty_id NUMBER)
112: IS
113: SELECT COUNT(*)
114: FROM okl_strm_elements STRE, OKL_STREAMS STR
115: WHERE STR.ID = STRE.STM_ID
116: AND STR.STY_ID = p_sty_id
117: AND STRE.DATE_BILLED IS NOT NULL
118: AND STR.KHR_ID = p_contract_id
418:
419: CURSOR l_line_rents_csr (chrId NUMBER ) IS
420: SELECT NVL(str.link_hist_stream_id,-1) link_hist_stream_id,
421: NVL(SUM(sele.amount),0) amount
422: FROM okl_strm_elements sele,
423: okl_streams str,
424: --okl_strm_type_tl sty,
425: okl_strm_type_v sty,
426: okl_K_lines_full_v kle,
441: GROUP BY str.link_hist_stream_id;
442:
443: CURSOR l_line_rents_adj_csr (p_stm_id NUMBER) IS
444: SELECT NVL(SUM(sele.amount),0) amount
445: FROM okl_strm_elements sele
446: WHERE stm_id = p_stm_id
447: AND date_billed IS NOT NULL;
448:
449: CURSOR l_chr_rents_csr (chrId NUMBER ) IS
447: AND date_billed IS NOT NULL;
448:
449: CURSOR l_chr_rents_csr (chrId NUMBER ) IS
450: SELECT NVL(SUM(sele.amount),0) amount
451: FROM okl_strm_elements sele,
452: okl_streams str,
453: --okl_strm_type_tl sty
454: okl_strm_type_v sty
455: WHERE sele.stm_id = str.id
467: -- Considers either new or re-leased assets of a Contract based on p_line_type parameter
468: CURSOR l_line_rents_nr_csr (chrId NUMBER, p_line_type VARCHAR2 ) IS
469: SELECT NVL(str.link_hist_stream_id,-1) link_hist_stream_id,
470: NVL(SUM(sele.amount),0) amount
471: FROM okl_strm_elements sele,
472: okl_streams str,
473: --okl_strm_type_tl sty,
474: okl_strm_type_v sty,
475: okl_K_lines_full_v kle,
619: -- Note: User defined stream: This stream type has been identified as
620: -- "Not being used" and hence not modified with its purpose
621: --
622: SELECT NVL(SUM(sele.amount),0) INTO l_income
623: FROM okl_strm_elements sele,
624: okl_streams str,
625: okl_strm_type_v sty,
626: okl_K_lines_full_v kle,
627: okc_statuses_b sts
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'
3803: -- AND stm.say_code = 'CURR'
3804: -- AND sty.id = p_prin_bal_id
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'
3818: -- AND stm.say_code = 'CURR'
3819: -- AND sty.id = p_prin_bal_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'
3828: -- AND stm.say_code = 'CURR'
3829: -- AND sty.id = p_prin_bal_id
4005: -- modifying cursor for user defind streams project
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
4130: -- SGIYER. User defined streams project changes.09/22/04
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
4141: AND ste.stm_id = stm.id
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
4153: AND ste.date_billed IS NULL)
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
4168: -- Considers either new or re-leased assets of a Contract based on p_line_type parameter
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
4181: AND ste.stm_id = stm.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
4200: )
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
4451:
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
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)
5388: AND stm.active_yn = 'Y'
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)
5502: AND stm.active_yn = 'Y'
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)
5526: AND stm.active_yn = 'N' -- reporting strems are inactive
5778: --PAGARG 31-Dec-2004 Bug# 4097591
5779: --Instead of using stream name, join the sty id passed to cursor
5780: CURSOR line_csr (c_contract_line_id NUMBER, p_sty_id NUMBER) IS
5781: SELECT NVL(SUM(sel.amount),0)
5782: FROM okl_strm_elements sel,
5783: okl_streams stm,
5784: okl_strm_type_v sty
5785: WHERE sty.id = p_sty_id
5786: AND stm.sty_id = sty.id
5798: --PAGARG 31-Dec-2004 Bug# 4097591
5799: --Instead of using stream name, join the sty id passed to cursor
5800: CURSOR line_reporting_csr (c_contract_line_id IN NUMBER, cp_trx_date IN DATE, p_sty_id NUMBER) IS
5801: SELECT NVL(SUM(sel.amount),0)
5802: FROM okl_strm_elements sel,
5803: okl_streams stm,
5804: okl_strm_type_v sty
5805: WHERE sty.id = p_sty_id
5806: AND stm.sty_id = sty.id
6029:
6030: CURSOR C (p_contract_id NUMBER)
6031: IS
6032: SELECT NVL(SUM(sele.amount),0)
6033: FROM okl_strm_elements sele,
6034: okl_streams str,
6035: okl_strm_type_v sty
6036: WHERE sele.stm_id = str.id
6037: AND str.sty_id = sty.id
6093: --Commented this code by rvaduri for bug 3487920
6094: /*
6095: SELECT NVL(SUM(cs.amount),0)
6096: FROM okl_streams_v asv,okl_strm_type_v bs,
6097: okl_strm_elements_v cs,
6098: okl_streams str,
6099: okl_strm_type_v sty
6100: WHERE cs.stm_id = asv.id AND bs.id = asv.sty_id
6101: AND str.sty_id = sty.id
6167:
6168: CURSOR C (p_contract_id NUMBER)
6169: IS
6170: SELECT NVL(SUM(sele.amount),0)
6171: FROM okl_strm_elements sele,
6172: okl_streams str,
6173: okl_strm_type_v sty
6174: WHERE sele.stm_id = str.id
6175: AND str.sty_id = sty.id
6184:
6185: --Get all the future unbilled receivables for the line.
6186: CURSOR line (p_contract_id NUMBER, p_contract_line_id NUMBER) IS --sechawla 04-dec-08 : added p_contract_id
6187: SELECT NVL(SUM(sele.amount),0)
6188: FROM okl_strm_elements sele,
6189: okl_streams str,
6190: okl_strm_type_v sty
6191: WHERE sele.stm_id = str.id
6192: AND str.sty_id = sty.id
6248:
6249: CURSOR C (p_contract_id NUMBER)
6250: IS
6251: SELECT NVL(SUM(sele.amount),0)
6252: FROM okl_strm_elements sele,
6253: --okl_streams str, MGAAP 7263041
6254: okl_streams_rep_v str,
6255: okl_strm_type_v sty
6256: WHERE sele.stm_id = str.id
6265:
6266: --Commented by rvaduri for bug 3536862
6267: /*
6268: SELECT NVL(SUM(sele.amount),0)
6269: FROM okl_strm_elements sele,
6270: okl_streams str,
6271: okl_strm_type_v sty
6272: WHERE sele.stm_id = str.id
6273: AND str.sty_id = sty.id
6281: */
6282:
6283: CURSOR line (p_contract_line_id NUMBER) IS
6284: SELECT NVL(SUM(sele.amount),0)
6285: FROM okl_strm_elements sele,
6286: --okl_streams str, MGAAP 7263041
6287: okl_streams_rep_v str,
6288: okl_strm_type_v sty
6289: WHERE sele.stm_id = str.id
6298: --Commented by rvaduri for bug 3536862
6299: /*
6300: *
6301: SELECT NVL(SUM(sel.amount),0)
6302: FROM okl_strm_elements sel,
6303: okl_streams stm,
6304: okl_strm_type_v sty
6305: WHERE sty.name = 'PRE-TAX INCOME'
6306: AND stm.sty_id = sty.id
6529: CURSOR c (p_contract_id NUMBER)
6530: IS
6531: SELECT NVL(cs.amount,0)
6532: FROM okl_streams_v asv,okl_strm_type_v bs,
6533: okl_strm_elements_v cs,
6534: okl_streams str,
6535: okl_strm_type_v sty,
6536: okc_k_headers_v okh
6537: WHERE cs.stm_id = asv.id AND bs.id = asv.sty_id
6554: IS
6555: --It should be sum because we have to get the Principal balance on
6556: -- all the assets for the contract
6557: SELECT NVL(SUM(sel.amount),0)
6558: FROM okl_strm_elements sel,
6559: okl_streams stm,
6560: okl_strm_type_v sty
6561: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6562: AND stm.sty_id = sty.id
6566: AND stm.khr_id = p_contract_id
6567: AND sel.stm_id = stm.id
6568: AND sel.stream_element_date =
6569: ( SELECT NVL(MAX(sel.stream_element_date), SYSDATE)
6570: FROM okl_strm_elements sel,okl_streams stm,
6571: okl_strm_type_v sty
6572: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6573: AND stm.sty_id = sty.id
6574: AND stm.say_code = 'CURR'
6746: IS
6747: --It should be sum because we have to get the Principal balance on
6748: -- all the assets for the contract
6749: SELECT NVL(SUM(sel.amount),0)
6750: FROM okl_strm_elements sel,
6751: okl_streams stm,
6752: okl_strm_type_v sty
6753: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6754: AND stm.sty_id = sty.id
6758: AND stm.khr_id = p_contract_id
6759: AND sel.stm_id = stm.id
6760: AND sel.stream_element_date =
6761: ( SELECT NVL(MAX(sel.stream_element_date), SYSDATE)
6762: FROM okl_strm_elements sel,okl_streams stm,
6763: okl_strm_type_v sty
6764: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6765: AND stm.sty_id = sty.id
6766: AND stm.say_code = 'CURR'
6774: -- enhancement done for user defined streams impacts, bug 3924303
6775:
6776: CURSOR line (p_contract_line_id NUMBER, p_date DATE) IS
6777: SELECT NVL(sel.amount,0)
6778: FROM okl_strm_elements sel,
6779: okl_streams stm,
6780: okl_strm_type_v sty
6781: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6782: AND stm.sty_id = sty.id
6786: AND stm.kle_id = p_contract_line_id
6787: AND sel.stm_id = stm.id
6788: AND sel.stream_element_date =
6789: ( SELECT NVL(MAX(sel.stream_element_date), SYSDATE)
6790: FROM okl_strm_elements sel,okl_streams stm,
6791: okl_strm_type_v sty
6792: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6793: AND stm.sty_id = sty.id
6794: AND stm.say_code = 'CURR'
7179: IS
7180: SELECT sel.amount rent
7181: ,sel.stream_element_date due_date
7182: ,stm.kle_id
7183: FROM okl_strm_elements sel,
7184: okl_streams stm,
7185: okl_strm_type_v sty
7186: WHERE sty.stream_type_purpose = 'RENT'
7187: AND stm.sty_id = sty.id
7199: CURSOR line_unbilled_rent_csr(c_contract_line_id NUMBER)
7200: IS
7201: SELECT sel.amount rent
7202: ,sel.stream_element_date due_date
7203: FROM okl_strm_elements sel,
7204: okl_streams stm,
7205: okl_strm_type_v sty
7206: WHERE sty.stream_type_purpose = 'RENT'
7207: AND stm.sty_id = sty.id
8082: ,p_kle_id IN NUMBER
8083: ,p_sty_id IN NUMBER)
8084: IS
8085: SELECT NVL(SUM(selb.amount),0) total_amount
8086: FROM okl_strm_elements selb
8087: ,okl_streams stmb
8088: ,okl_pool_contents pocb
8089: ,okl_strm_type_v styv --ankushar --Bug 6594724
8090: ,okc_k_headers_b chrb --ankushar --Bug 6594724
8134: ,p_kle_id IN NUMBER
8135: ,p_sty_id IN NUMBER)
8136: IS
8137: SELECT NVL(SUM(selb.amount),0) total_amount
8138: FROM okl_strm_elements selb
8139: ,okl_streams stmb
8140: ,okl_pool_contents pocb
8141: WHERE stmb.khr_id = p_dnz_chr_id
8142: AND stmb.kle_id = p_kle_id
8232: -- LOAN_PAYMENT stream type purpose
8233: CURSOR l_okl_pv_amounts_csr(p_sty_purpose IN VARCHAR2, p_poc_sty_purpose IN VARCHAR2)
8234: IS
8235: SELECT NVL(SUM(selb.amount),0) total_amount
8236: FROM okl_strm_elements selb
8237: ,okl_streams stmb
8238: ,okl_strm_type_v styv
8239: ,okl_pool_contents pocb
8240: ,okl_strm_type_v styv1
8254: -- LOAN_PAYMENT stream type purpose
8255: CURSOR l_okl_pv_amounts_pending_csr(p_sty_purpose IN VARCHAR2, p_poc_sty_purpose IN VARCHAR2)
8256: IS
8257: SELECT NVL(SUM(selb.amount),0) total_amount
8258: FROM okl_strm_elements selb
8259: ,okl_streams stmb
8260: ,okl_strm_type_v styv
8261: ,okl_pool_contents pocb
8262: ,okl_strm_type_v styv1
8503: CURSOR c_rent_accrual(p_contract_id okc_k_headers_b.id%TYPE) IS
8504: SELECT NVL(SUM(SELB.AMOUNT),0)
8505: FROM
8506: OKL_STREAMS STMB,
8507: OKL_STRM_ELEMENTS SELB,
8508: OKL_STRM_TYPE_V STYV
8509: WHERE STMB.ID = SELB.STM_ID
8510: AND STMB.STY_ID = STYV.ID
8511: AND STYV.STREAM_TYPE_PURPOSE IN ('INVESTOR_PRETAX_INCOME','INVESTOR_RENTAL_ACCRUAL','INVESTOR_INTEREST_INCOME')
8526: CURSOR c_rent_accrual_pending(p_contract_id okc_k_headers_b.id%TYPE) IS
8527: SELECT NVL(SUM(SELB.AMOUNT),0)
8528: FROM
8529: OKL_STREAMS STMB,
8530: OKL_STRM_ELEMENTS SELB,
8531: OKL_STRM_TYPE_V STYV
8532: WHERE STMB.ID = SELB.STM_ID
8533: AND STMB.STY_ID = STYV.ID
8534: AND STYV.STREAM_TYPE_PURPOSE IN ('INVESTOR_PRETAX_INCOME','INVESTOR_RENTAL_ACCRUAL','INVESTOR_INTEREST_INCOME')
8730: NVL(SUM(NVL(selb.AMOUNT,0)),0) AMOUNT
8731: FROM
8732: okl_strm_type_v styv,
8733: okl_streams stmb,
8734: okl_strm_elements selb,
8735: okl_pool_contents pocb
8736: WHERE styv.stream_type_subclass = p_stm_sub_class
8737: AND styv.id = stmb.sty_id
8738: AND stmb.id = selb.stm_id
8751: NVL(SUM(NVL(selb.AMOUNT,0)),0) AMOUNT
8752: FROM
8753: okl_strm_type_v styv,
8754: okl_streams stmb,
8755: okl_strm_elements selb,
8756: okl_pool_contents pocb
8757: WHERE styv.stream_type_subclass = p_stm_sub_class
8758: AND styv.id = stmb.sty_id
8759: AND stmb.id = selb.stm_id
9515: --cursor to get sum of line level amortized expenses
9516: ---------------------------------------------------
9517: CURSOR l_line_amortexp_csr (chrId NUMBER ) IS
9518: SELECT NVL(SUM(sele.amount),0) amount
9519: FROM okl_strm_elements sele,
9520: okl_streams str,
9521: --okl_strm_type_tl sty,
9522: okl_strm_type_v sty,
9523: okl_K_lines_full_v kle,
9542: --Cursor to get sum of contract level amortized expenses
9543: -----------------------------------------------------
9544: CURSOR l_chr_amortexp_csr (chrId NUMBER) IS
9545: SELECT NVL(SUM(sele.amount),0) amount
9546: FROM okl_strm_elements sele,
9547: okl_streams str,
9548: --okl_strm_type_tl sty
9549: okl_strm_type_v sty
9550: WHERE sele.stm_id = str.id
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)
9645: AND stm.active_yn = 'Y'
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)
9672: AND stm.active_yn = 'Y'
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)
9697: AND stm.active_yn = 'Y'
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)
9723: AND stm.active_yn = 'Y'
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)
9918: AND stm.active_yn = 'Y'
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)
9943: AND stm.active_yn = 'Y'
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)
10069: AND stm.active_yn = 'Y'
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)
10094: AND stm.active_yn = 'Y'
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)
10117: AND stm.active_yn = 'Y'
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)
10142: AND stm.active_yn = 'Y'
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,
10571: okl_k_lines cle
10572: WHERE stm.khr_id = cp_chr_id
10573: AND stm.active_yn = 'Y'
10574: AND stm.say_code = 'CURR'
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
10603: ,okl_k_lines cle
10604: ,okc_k_lines_b cles
10605: WHERE stm.khr_id = cp_chr_id
10606: AND stm.active_yn = 'Y'
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
10637: ,okl_k_lines cle
10638: ,okc_k_lines_b cles
10639: ,okc_k_items cim
10640: WHERE stm.khr_id = cp_chr_id
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
10925: WHERE stmb.id = selb.stm_id
10926: AND stmb.say_code = 'CURR'
10927: AND stmb.active_yn = 'Y'
10928: AND selb.date_billed IS NULL
11038:
11039: CURSOR l_line_income_csr (p_chr_id NUMBER) IS
11040: SELECT NVL(str.link_hist_stream_id,-1) link_hist_stream_id,
11041: NVL(SUM(sele.amount),0) amount
11042: FROM okl_strm_elements sele,
11043: --okl_streams str, MGAAP 7263041
11044: okl_streams_rep_v str,
11045: okl_strm_type_v sty,
11046: okc_k_headers_b CHR,
11060: GROUP BY str.link_hist_stream_id;
11061:
11062: CURSOR l_line_income_adj_csr (p_stm_id NUMBER) IS
11063: SELECT NVL(SUM(sele.amount),0) amount
11064: FROM okl_strm_elements sele
11065: WHERE stm_id = p_stm_id
11066: AND NVL(accrued_yn,'N') = 'Y';
11067:
11068: --Bug# 15992711 , Start
11069: -- Considers either new or re-leased assets of a Contract based on p_line_type parameter
11070: CURSOR l_line_income_nr_csr (p_chr_id NUMBER, p_line_type VARCHAR2) IS
11071: SELECT NVL(str.link_hist_stream_id,-1) link_hist_stream_id,
11072: NVL(SUM(sele.amount),0) amount
11073: FROM okl_strm_elements sele,
11074: --okl_streams str, MGAAP 7263041
11075: okl_streams_rep_v str,
11076: okl_strm_type_v sty,
11077: okc_k_headers_b CHR,
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)
12809: AND stm.active_yn = 'Y'
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)
12832: AND stm.active_yn = 'N' -- reporting strems are inactive
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)
12868: AND stm.active_yn = 'Y'
13226: CURSOR line_csr (c_contract_line_id NUMBER,
13227: cp_trx_date DATE,
13228: p_sty_id NUMBER) IS
13229: SELECT NVL(SUM(sel.amount),0)
13230: FROM okl_strm_elements sel,
13231: --okl_streams stm, MGAAP 7263041
13232: okl_streams_rep_v stm,
13233: okl_strm_type_v sty
13234: WHERE sty.id = p_sty_id
13248: --PAGARG 31-Dec-2004 Bug# 4097591
13249: --Instead of using stream name, join the sty id passed to cursor
13250: CURSOR line_reporting_csr (c_contract_line_id IN NUMBER, cp_trx_date IN DATE, p_sty_id NUMBER) IS
13251: SELECT NVL(SUM(sel.amount),0)
13252: FROM okl_strm_elements sel,
13253: --okl_streams stm, MGAAP 7263041
13254: okl_streams_rep_v stm,
13255: okl_strm_type_v sty
13256: WHERE sty.id = p_sty_id
13286: CURSOR stream_element_csr (c_contract_line_id NUMBER,
13287: cp_trx_date DATE,
13288: p_sty_id NUMBER) IS
13289: SELECT NVL(sel.amount,0) amount
13290: FROM okl_strm_elements sel,
13291: --okl_streams stm, MGAAP 7263041
13292: okl_streams_rep_v stm,
13293: okl_strm_type_v sty
13294: WHERE sty.id = p_sty_id
13724: CURSOR c_balance (p_sty_id NUMBER, p_date DATE) IS
13725: SELECT
13726: sel.amount
13727: FROM
13728: okl_strm_elements sel,
13729: okl_streams stm
13730: WHERE
13731: stm.sty_id = p_sty_id
13732: AND stm.khr_id = p_contract_id
14079: RETURN NUMBER IS
14080:
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'
14567:
14568: -- get all unbilled receivables for all ACTIVE assets
14569: CURSOR cle_rents_csr(cp_contract_id okc_k_headers_b.id%TYPE) IS
14570: SELECT NVL(SUM(sele.amount),0)
14571: FROM okl_strm_elements sele
14572: ,okl_streams str
14573: ,okl_strm_type_v sty
14574: ,okc_k_lines_v line
14575: ,okc_statuses_b sts
14623: ,p_contract_line_id okc_k_lines_b.id%TYPE DEFAULT OKL_API.G_MISS_NUM) RETURN NUMBER IS
14624:
14625: CURSOR c_pre_tax_csr(cp_contract_id okc_k_headers_b.id%TYPE) IS
14626: SELECT NVL(SUM(sele.amount),0)
14627: FROM okl_strm_elements sele
14628: --,okl_streams str MGAAP 7263041
14629: ,okl_streams_rep_v str
14630: ,okl_strm_type_v sty
14631: ,okc_k_lines_b line
14673: -- get all unbilled receivables for all TERMINATED assets
14674: CURSOR cle_rents_csr(cp_contract_id okc_k_headers_b.id%TYPE
14675: ,cp_contract_line_id okc_k_lines_b.id%TYPE) IS
14676: SELECT NVL(SUM(sele.amount),0)
14677: FROM okl_strm_elements sele
14678: ,okl_streams str
14679: ,okl_strm_type_v sty
14680: ,okc_k_lines_v line
14681: -- ,okc_statuses_b sts
14730: FUNCTION cont_tmt_unearned_income(p_contract_id okc_k_headers_b.id%TYPE
14731: ,p_contract_line_id okc_k_lines_b.id%TYPE DEFAULT OKL_API.G_MISS_NUM) RETURN NUMBER IS
14732: CURSOR c_pre_tax_csr(cp_contract_id okc_k_headers_b.id%TYPE ,cp_contract_line_id okc_k_lines_b.id%TYPE) IS
14733: SELECT NVL(SUM(sele.amount),0)
14734: FROM okl_strm_elements sele
14735: --,okl_streams str MGAAP 7263041
14736: ,okl_streams_rep_v str
14737: ,okl_strm_type_v sty
14738: ,okc_k_lines_b line
14794: RETURN NUMBER IS
14795:
14796: CURSOR cr_next_payment_date(c_contract_id IN NUMBER) IS
14797: SELECT MIN(sel.stream_element_date)
14798: FROM okl_strm_elements sel,
14799: okl_streams stm,
14800: okl_strm_type_v sty
14801: WHERE stm.sty_id = sty.id
14802: AND stm.say_code = 'CURR'
14810:
14811: CURSOR cr_next_payment_amt(c_contract_id IN NUMBER,
14812: c_next_due_date IN DATE) IS
14813: SELECT NVL(sum(sel.amount),0)
14814: FROM okl_strm_elements sel,
14815: okl_streams stm,
14816: okl_strm_type_v sty
14817: WHERE stm.sty_id = sty.id
14818: AND stm.say_code = 'CURR'