[Home] [Help]
111:
112: CURSOR c_total_amount_paid (p_sty_id NUMBER)
113: IS
114: SELECT COUNT(*)
115: FROM okl_strm_elements STRE, OKL_STREAMS STR
116: WHERE STR.ID = STRE.STM_ID
117: AND STR.STY_ID = p_sty_id
118: AND STRE.DATE_BILLED IS NOT NULL
119: AND STR.KHR_ID = p_contract_id
419:
420: CURSOR l_line_rents_csr (chrId NUMBER ) IS
421: SELECT NVL(str.link_hist_stream_id,-1) link_hist_stream_id,
422: NVL(SUM(sele.amount),0) amount
423: FROM okl_strm_elements sele,
424: okl_streams str,
425: --okl_strm_type_tl sty,
426: okl_strm_type_v sty,
427: okl_K_lines_full_v kle,
442: GROUP BY str.link_hist_stream_id;
443:
444: CURSOR l_line_rents_adj_csr (p_stm_id NUMBER) IS
445: SELECT NVL(SUM(sele.amount),0) amount
446: FROM okl_strm_elements sele
447: WHERE stm_id = p_stm_id
448: AND date_billed IS NOT NULL;
449:
450: CURSOR l_chr_rents_csr (chrId NUMBER ) IS
448: AND date_billed IS NOT NULL;
449:
450: CURSOR l_chr_rents_csr (chrId NUMBER ) IS
451: SELECT NVL(SUM(sele.amount),0) amount
452: FROM okl_strm_elements sele,
453: okl_streams str,
454: --okl_strm_type_tl sty
455: okl_strm_type_v sty
456: WHERE sele.stm_id = str.id
546: -- Note: User defined stream: This stream type has been identified as
547: -- "Not being used" and hence not modified with its purpose
548: --
549: SELECT NVL(SUM(sele.amount),0) INTO l_income
550: FROM okl_strm_elements sele,
551: okl_streams str,
552: okl_strm_type_v sty,
553: okl_K_lines_full_v kle,
554: okc_statuses_b sts
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'
3339: -- AND stm.say_code = 'CURR'
3340: -- AND sty.id = p_prin_bal_id
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'
3354: -- AND stm.say_code = 'CURR'
3355: -- AND sty.id = p_prin_bal_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'
3364: -- AND stm.say_code = 'CURR'
3365: -- AND sty.id = p_prin_bal_id
3534: -- modifying cursor for user defind streams project
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
3659: -- SGIYER. User defined streams project changes.09/22/04
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
3670: AND ste.stm_id = stm.id
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
3682: AND ste.date_billed IS NULL)
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
3888:
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
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)
4803: AND stm.active_yn = 'Y'
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)
4904: AND stm.active_yn = 'Y'
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)
4928: AND stm.active_yn = 'N' -- reporting strems are inactive
5180: --PAGARG 31-Dec-2004 Bug# 4097591
5181: --Instead of using stream name, join the sty id passed to cursor
5182: CURSOR line_csr (c_contract_line_id NUMBER, p_sty_id NUMBER) IS
5183: SELECT NVL(SUM(sel.amount),0)
5184: FROM okl_strm_elements sel,
5185: okl_streams stm,
5186: okl_strm_type_v sty
5187: WHERE sty.id = p_sty_id
5188: AND stm.sty_id = sty.id
5200: --PAGARG 31-Dec-2004 Bug# 4097591
5201: --Instead of using stream name, join the sty id passed to cursor
5202: CURSOR line_reporting_csr (c_contract_line_id IN NUMBER, cp_trx_date IN DATE, p_sty_id NUMBER) IS
5203: SELECT NVL(SUM(sel.amount),0)
5204: FROM okl_strm_elements sel,
5205: okl_streams stm,
5206: okl_strm_type_v sty
5207: WHERE sty.id = p_sty_id
5208: AND stm.sty_id = sty.id
5431:
5432: CURSOR C (p_contract_id NUMBER)
5433: IS
5434: SELECT NVL(SUM(sele.amount),0)
5435: FROM okl_strm_elements sele,
5436: okl_streams str,
5437: okl_strm_type_v sty
5438: WHERE sele.stm_id = str.id
5439: AND str.sty_id = sty.id
5495: --Commented this code by rvaduri for bug 3487920
5496: /*
5497: SELECT NVL(SUM(cs.amount),0)
5498: FROM okl_streams_v asv,okl_strm_type_v bs,
5499: okl_strm_elements_v cs,
5500: okl_streams str,
5501: okl_strm_type_v sty
5502: WHERE cs.stm_id = asv.id AND bs.id = asv.sty_id
5503: AND str.sty_id = sty.id
5569:
5570: CURSOR C (p_contract_id NUMBER)
5571: IS
5572: SELECT NVL(SUM(sele.amount),0)
5573: FROM okl_strm_elements sele,
5574: okl_streams str,
5575: okl_strm_type_v sty
5576: WHERE sele.stm_id = str.id
5577: AND str.sty_id = sty.id
5586:
5587: --Get all the future unbilled receivables for the line.
5588: CURSOR line (p_contract_id NUMBER, p_contract_line_id NUMBER) IS --sechawla 04-dec-08 : added p_contract_id
5589: SELECT NVL(SUM(sele.amount),0)
5590: FROM okl_strm_elements sele,
5591: okl_streams str,
5592: okl_strm_type_v sty
5593: WHERE sele.stm_id = str.id
5594: AND str.sty_id = sty.id
5650:
5651: CURSOR C (p_contract_id NUMBER)
5652: IS
5653: SELECT NVL(SUM(sele.amount),0)
5654: FROM okl_strm_elements sele,
5655: --okl_streams str, MGAAP 7263041
5656: okl_streams_rep_v str,
5657: okl_strm_type_v sty
5658: WHERE sele.stm_id = str.id
5667:
5668: --Commented by rvaduri for bug 3536862
5669: /*
5670: SELECT NVL(SUM(sele.amount),0)
5671: FROM okl_strm_elements sele,
5672: okl_streams str,
5673: okl_strm_type_v sty
5674: WHERE sele.stm_id = str.id
5675: AND str.sty_id = sty.id
5683: */
5684:
5685: CURSOR line (p_contract_line_id NUMBER) IS
5686: SELECT NVL(SUM(sele.amount),0)
5687: FROM okl_strm_elements sele,
5688: --okl_streams str, MGAAP 7263041
5689: okl_streams_rep_v str,
5690: okl_strm_type_v sty
5691: WHERE sele.stm_id = str.id
5700: --Commented by rvaduri for bug 3536862
5701: /*
5702: *
5703: SELECT NVL(SUM(sel.amount),0)
5704: FROM okl_strm_elements sel,
5705: okl_streams stm,
5706: okl_strm_type_v sty
5707: WHERE sty.name = 'PRE-TAX INCOME'
5708: AND stm.sty_id = sty.id
5931: CURSOR c (p_contract_id NUMBER)
5932: IS
5933: SELECT NVL(cs.amount,0)
5934: FROM okl_streams_v asv,okl_strm_type_v bs,
5935: okl_strm_elements_v cs,
5936: okl_streams str,
5937: okl_strm_type_v sty,
5938: okc_k_headers_v okh
5939: WHERE cs.stm_id = asv.id AND bs.id = asv.sty_id
5956: IS
5957: --It should be sum because we have to get the Principal balance on
5958: -- all the assets for the contract
5959: SELECT NVL(SUM(sel.amount),0)
5960: FROM okl_strm_elements sel,
5961: okl_streams stm,
5962: okl_strm_type_v sty
5963: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
5964: AND stm.sty_id = sty.id
5968: AND stm.khr_id = p_contract_id
5969: AND sel.stm_id = stm.id
5970: AND sel.stream_element_date =
5971: ( SELECT NVL(MAX(sel.stream_element_date), SYSDATE)
5972: FROM okl_strm_elements sel,okl_streams stm,
5973: okl_strm_type_v sty
5974: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
5975: AND stm.sty_id = sty.id
5976: AND stm.say_code = 'CURR'
6148: IS
6149: --It should be sum because we have to get the Principal balance on
6150: -- all the assets for the contract
6151: SELECT NVL(SUM(sel.amount),0)
6152: FROM okl_strm_elements sel,
6153: okl_streams stm,
6154: okl_strm_type_v sty
6155: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6156: AND stm.sty_id = sty.id
6160: AND stm.khr_id = p_contract_id
6161: AND sel.stm_id = stm.id
6162: AND sel.stream_element_date =
6163: ( SELECT NVL(MAX(sel.stream_element_date), SYSDATE)
6164: FROM okl_strm_elements sel,okl_streams stm,
6165: okl_strm_type_v sty
6166: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6167: AND stm.sty_id = sty.id
6168: AND stm.say_code = 'CURR'
6176: -- enhancement done for user defined streams impacts, bug 3924303
6177:
6178: CURSOR line (p_contract_line_id NUMBER, p_date DATE) IS
6179: SELECT NVL(sel.amount,0)
6180: FROM okl_strm_elements sel,
6181: okl_streams stm,
6182: okl_strm_type_v sty
6183: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6184: AND stm.sty_id = sty.id
6188: AND stm.kle_id = p_contract_line_id
6189: AND sel.stm_id = stm.id
6190: AND sel.stream_element_date =
6191: ( SELECT NVL(MAX(sel.stream_element_date), SYSDATE)
6192: FROM okl_strm_elements sel,okl_streams stm,
6193: okl_strm_type_v sty
6194: WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
6195: AND stm.sty_id = sty.id
6196: AND stm.say_code = 'CURR'
6581: IS
6582: SELECT sel.amount rent
6583: ,sel.stream_element_date due_date
6584: ,stm.kle_id
6585: FROM okl_strm_elements sel,
6586: okl_streams stm,
6587: okl_strm_type_v sty
6588: WHERE sty.stream_type_purpose = 'RENT'
6589: AND stm.sty_id = sty.id
6601: CURSOR line_unbilled_rent_csr(c_contract_line_id NUMBER)
6602: IS
6603: SELECT sel.amount rent
6604: ,sel.stream_element_date due_date
6605: FROM okl_strm_elements sel,
6606: okl_streams stm,
6607: okl_strm_type_v sty
6608: WHERE sty.stream_type_purpose = 'RENT'
6609: AND stm.sty_id = sty.id
7433: ,p_kle_id IN NUMBER
7434: ,p_sty_id IN NUMBER)
7435: IS
7436: SELECT NVL(SUM(selb.amount),0) total_amount
7437: FROM okl_strm_elements selb
7438: ,okl_streams stmb
7439: ,okl_pool_contents pocb
7440: ,okl_strm_type_v styv --ankushar --Bug 6594724
7441: ,okc_k_headers_b chrb --ankushar --Bug 6594724
7485: ,p_kle_id IN NUMBER
7486: ,p_sty_id IN NUMBER)
7487: IS
7488: SELECT NVL(SUM(selb.amount),0) total_amount
7489: FROM okl_strm_elements selb
7490: ,okl_streams stmb
7491: ,okl_pool_contents pocb
7492: WHERE stmb.khr_id = p_dnz_chr_id
7493: AND stmb.kle_id = p_kle_id
7581: -- mvasudev, 09/29/2004, Bug#3909240
7582: CURSOR l_okl_pv_amounts_csr(p_sty_purpose IN VARCHAR2)
7583: IS
7584: SELECT NVL(SUM(selb.amount),0) total_amount
7585: FROM okl_strm_elements selb
7586: ,okl_streams stmb
7587: ,okl_strm_type_v styv
7588: ,okl_pool_contents pocb
7589: WHERE stmb.source_id = p_chr_id
7598:
7599: CURSOR l_okl_pv_amounts_pending_csr(p_sty_purpose IN VARCHAR2)
7600: IS
7601: SELECT NVL(SUM(selb.amount),0) total_amount
7602: FROM okl_strm_elements selb
7603: ,okl_streams stmb
7604: ,okl_strm_type_v styv
7605: ,okl_pool_contents pocb
7606: WHERE stmb.source_id = p_chr_id
7829: CURSOR c_rent_accrual(p_contract_id okc_k_headers_b.id%TYPE) IS
7830: SELECT NVL(SUM(SELB.AMOUNT),0)
7831: FROM
7832: OKL_STREAMS STMB,
7833: OKL_STRM_ELEMENTS SELB,
7834: OKL_STRM_TYPE_V STYV
7835: WHERE STMB.ID = SELB.STM_ID
7836: AND STMB.STY_ID = STYV.ID
7837: AND STYV.STREAM_TYPE_PURPOSE IN ('INVESTOR_PRETAX_INCOME','INVESTOR_RENTAL_ACCRUAL','INVESTOR_INTEREST_INCOME')
7852: CURSOR c_rent_accrual_pending(p_contract_id okc_k_headers_b.id%TYPE) IS
7853: SELECT NVL(SUM(SELB.AMOUNT),0)
7854: FROM
7855: OKL_STREAMS STMB,
7856: OKL_STRM_ELEMENTS SELB,
7857: OKL_STRM_TYPE_V STYV
7858: WHERE STMB.ID = SELB.STM_ID
7859: AND STMB.STY_ID = STYV.ID
7860: AND STYV.STREAM_TYPE_PURPOSE IN ('INVESTOR_PRETAX_INCOME','INVESTOR_RENTAL_ACCRUAL','INVESTOR_INTEREST_INCOME')
8056: NVL(SUM(NVL(selb.AMOUNT,0)),0) AMOUNT
8057: FROM
8058: okl_strm_type_v styv,
8059: okl_streams stmb,
8060: okl_strm_elements selb,
8061: okl_pool_contents pocb
8062: WHERE styv.stream_type_subclass = p_stm_sub_class
8063: AND styv.id = stmb.sty_id
8064: AND stmb.id = selb.stm_id
8077: NVL(SUM(NVL(selb.AMOUNT,0)),0) AMOUNT
8078: FROM
8079: okl_strm_type_v styv,
8080: okl_streams stmb,
8081: okl_strm_elements selb,
8082: okl_pool_contents pocb
8083: WHERE styv.stream_type_subclass = p_stm_sub_class
8084: AND styv.id = stmb.sty_id
8085: AND stmb.id = selb.stm_id
8665: --cursor to get sum of line level amortized expenses
8666: ---------------------------------------------------
8667: CURSOR l_line_amortexp_csr (chrId NUMBER ) IS
8668: SELECT NVL(SUM(sele.amount),0) amount
8669: FROM okl_strm_elements sele,
8670: okl_streams str,
8671: --okl_strm_type_tl sty,
8672: okl_strm_type_v sty,
8673: okl_K_lines_full_v kle,
8692: --Cursor to get sum of contract level amortized expenses
8693: -----------------------------------------------------
8694: CURSOR l_chr_amortexp_csr (chrId NUMBER) IS
8695: SELECT NVL(SUM(sele.amount),0) amount
8696: FROM okl_strm_elements sele,
8697: okl_streams str,
8698: --okl_strm_type_tl sty
8699: okl_strm_type_v sty
8700: WHERE sele.stm_id = str.id
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)
8795: AND stm.active_yn = 'Y'
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)
8822: AND stm.active_yn = 'Y'
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)
8846: AND stm.active_yn = 'Y'
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)
8872: AND stm.active_yn = 'Y'
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)
9066: AND stm.active_yn = 'Y'
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)
9091: AND stm.active_yn = 'Y'
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)
9217: AND stm.active_yn = 'Y'
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)
9242: AND stm.active_yn = 'Y'
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)
9265: AND stm.active_yn = 'Y'
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)
9290: AND stm.active_yn = 'Y'
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,
9714: okl_k_lines cle
9715: WHERE stm.khr_id = cp_chr_id
9716: AND stm.active_yn = 'Y'
9717: AND stm.say_code = 'CURR'
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
9746: ,okl_k_lines cle
9747: ,okc_k_lines_b cles
9748: WHERE stm.khr_id = cp_chr_id
9749: AND stm.active_yn = 'Y'
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
9780: ,okl_k_lines cle
9781: ,okc_k_lines_b cles
9782: ,okc_k_items cim
9783: WHERE stm.khr_id = cp_chr_id
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
10068: WHERE stmb.id = selb.stm_id
10069: AND stmb.say_code = 'CURR'
10070: AND stmb.active_yn = 'Y'
10071: AND selb.date_billed IS NULL
10181:
10182: CURSOR l_line_income_csr (p_chr_id NUMBER) IS
10183: SELECT NVL(str.link_hist_stream_id,-1) link_hist_stream_id,
10184: NVL(SUM(sele.amount),0) amount
10185: FROM okl_strm_elements sele,
10186: --okl_streams str, MGAAP 7263041
10187: okl_streams_rep_v str,
10188: okl_strm_type_v sty,
10189: okc_k_headers_b CHR,
10203: GROUP BY str.link_hist_stream_id;
10204:
10205: CURSOR l_line_income_adj_csr (p_stm_id NUMBER) IS
10206: SELECT NVL(SUM(sele.amount),0) amount
10207: FROM okl_strm_elements sele
10208: WHERE stm_id = p_stm_id
10209: AND NVL(accrued_yn,'N') = 'Y';
10210:
10211: l_line_income_amount NUMBER;
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)
11721: AND stm.active_yn = 'Y'
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)
11744: AND stm.active_yn = 'N' -- reporting strems are inactive
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)
11780: AND stm.active_yn = 'Y'
12029: CURSOR line_csr (c_contract_line_id NUMBER,
12030: cp_trx_date DATE,
12031: p_sty_id NUMBER) IS
12032: SELECT NVL(SUM(sel.amount),0)
12033: FROM okl_strm_elements sel,
12034: --okl_streams stm, MGAAP 7263041
12035: okl_streams_rep_v stm,
12036: okl_strm_type_v sty
12037: WHERE sty.id = p_sty_id
12051: --PAGARG 31-Dec-2004 Bug# 4097591
12052: --Instead of using stream name, join the sty id passed to cursor
12053: CURSOR line_reporting_csr (c_contract_line_id IN NUMBER, cp_trx_date IN DATE, p_sty_id NUMBER) IS
12054: SELECT NVL(SUM(sel.amount),0)
12055: FROM okl_strm_elements sel,
12056: --okl_streams stm, MGAAP 7263041
12057: okl_streams_rep_v stm,
12058: okl_strm_type_v sty
12059: WHERE sty.id = p_sty_id
12089: CURSOR stream_element_csr (c_contract_line_id NUMBER,
12090: cp_trx_date DATE,
12091: p_sty_id NUMBER) IS
12092: SELECT NVL(sel.amount,0) amount
12093: FROM okl_strm_elements sel,
12094: --okl_streams stm, MGAAP 7263041
12095: okl_streams_rep_v stm,
12096: okl_strm_type_v sty
12097: WHERE sty.id = p_sty_id
12492: CURSOR c_balance (p_sty_id NUMBER, p_date DATE) IS
12493: SELECT
12494: sel.amount
12495: FROM
12496: okl_strm_elements sel,
12497: okl_streams stm
12498: WHERE
12499: stm.sty_id = p_sty_id
12500: AND stm.khr_id = p_contract_id
12847: RETURN NUMBER IS
12848:
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'
13335:
13336: -- get all unbilled receivables for all ACTIVE assets
13337: CURSOR cle_rents_csr(cp_contract_id okc_k_headers_b.id%TYPE) IS
13338: SELECT NVL(SUM(sele.amount),0)
13339: FROM okl_strm_elements sele
13340: ,okl_streams str
13341: ,okl_strm_type_v sty
13342: ,okc_k_lines_v line
13343: ,okc_statuses_b sts
13391: ,p_contract_line_id okc_k_lines_b.id%TYPE DEFAULT OKL_API.G_MISS_NUM) RETURN NUMBER IS
13392:
13393: CURSOR c_pre_tax_csr(cp_contract_id okc_k_headers_b.id%TYPE) IS
13394: SELECT NVL(SUM(sele.amount),0)
13395: FROM okl_strm_elements sele
13396: --,okl_streams str MGAAP 7263041
13397: ,okl_streams_rep_v str
13398: ,okl_strm_type_v sty
13399: ,okc_k_lines_b line
13441: -- get all unbilled receivables for all TERMINATED assets
13442: CURSOR cle_rents_csr(cp_contract_id okc_k_headers_b.id%TYPE
13443: ,cp_contract_line_id okc_k_lines_b.id%TYPE) IS
13444: SELECT NVL(SUM(sele.amount),0)
13445: FROM okl_strm_elements sele
13446: ,okl_streams str
13447: ,okl_strm_type_v sty
13448: ,okc_k_lines_v line
13449: -- ,okc_statuses_b sts
13498: FUNCTION cont_tmt_unearned_income(p_contract_id okc_k_headers_b.id%TYPE
13499: ,p_contract_line_id okc_k_lines_b.id%TYPE DEFAULT OKL_API.G_MISS_NUM) RETURN NUMBER IS
13500: 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
13501: SELECT NVL(SUM(sele.amount),0)
13502: FROM okl_strm_elements sele
13503: --,okl_streams str MGAAP 7263041
13504: ,okl_streams_rep_v str
13505: ,okl_strm_type_v sty
13506: ,okc_k_lines_b line
13562: RETURN NUMBER IS
13563:
13564: CURSOR cr_next_payment_date(c_contract_id IN NUMBER) IS
13565: SELECT MIN(sel.stream_element_date)
13566: FROM okl_strm_elements sel,
13567: okl_streams stm,
13568: okl_strm_type_v sty
13569: WHERE stm.sty_id = sty.id
13570: AND stm.say_code = 'CURR'
13578:
13579: CURSOR cr_next_payment_amt(c_contract_id IN NUMBER,
13580: c_next_due_date IN DATE) IS
13581: SELECT NVL(sum(sel.amount),0)
13582: FROM okl_strm_elements sel,
13583: okl_streams stm,
13584: okl_strm_type_v sty
13585: WHERE stm.sty_id = sty.id
13586: AND stm.say_code = 'CURR'