[Home] [Help]
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,
428: okc_statuses_b sts
429: WHERE sele.stm_id = str.id
430: AND str.sty_id = sty.id
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
457: AND str.sty_id = sty.id
458: --AND UPPER(sty.name) = 'RENT'
459: AND sty.stream_type_purpose = 'RENT'
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
555: WHERE sele.stm_id = str.id
556: AND str.sty_id = sty.id
2162:
2163: CURSOR srvc_strm_type_csr ( kleid NUMBER ) IS
2164: SELECT sty.capitalize_yn,
2165: sty.name
2166: FROM okl_strm_type_v sty,
2167: okc_k_items cim,
2168: okc_line_styles_b ls,
2169: okl_K_lines_full_v kle,
2170: okc_statuses_b sts
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
5189: AND stm.say_code = 'CURR'
5190: AND stm.active_yn = 'Y'
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
5209: AND stm.say_code = 'CURR' -- reporting streams are current
5210: AND stm.active_yn = 'N' -- reporting strems are inactive
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
5440: AND UPPER(sty.stream_type_purpose) = 'SECURITY_DEPOSIT'
5441: AND str.say_code = 'CURR'
5494:
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
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
5504: AND UPPER(sty.name) = 'RESIDUAL VALUE'
5505: AND str.say_code = 'CURR'
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
5578: AND UPPER(sty.stream_type_purpose) = 'RENT'
5579: AND str.say_code = 'CURR'
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
5595: --AND UPPER(sty.name) = 'RENT' --sechawla 04-dec-08 : remoevd
5596: AND UPPER(sty.stream_type_purpose) = 'RENT' --sechawla 04-dec-08 : added
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
5659: AND str.sty_id = sty.id
5660: AND UPPER(sty.stream_type_purpose) = 'LEASE_INCOME'
5661: AND str.say_code = 'CURR'
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
5676: AND UPPER(sty.name) = 'UNEARNED INCOME'
5677: AND str.say_code = 'CURR'
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
5692: AND str.sty_id = sty.id
5693: AND UPPER(sty.name) = 'PRE-TAX INCOME'
5694: AND str.say_code = 'CURR'
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
5709: AND stm.say_code = 'CURR'
5710: AND stm.active_yn = 'Y'
5930: /*
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
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
5940: AND str.sty_id = sty.id
5941: AND UPPER(sty.name) = 'PRINCIPAL BALANCE'
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
5965: AND stm.say_code = 'CURR'
5966: AND stm.active_yn = 'Y'
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'
5977: AND stm.active_yn = 'Y'
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
6157: AND stm.say_code = 'CURR'
6158: AND stm.active_yn = 'Y'
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'
6169: AND stm.active_yn = 'Y'
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
6185: AND stm.say_code = 'CURR'
6186: AND stm.active_yn = 'Y'
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'
6197: AND stm.active_yn = 'Y'
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
6590: AND stm.say_code = 'CURR'
6591: AND stm.active_yn = 'Y'
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
6610: AND stm.say_code = 'CURR'
6611: AND stm.active_yn = 'Y'
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
7442: WHERE stmb.khr_id = p_dnz_chr_id
7443: AND stmb.kle_id = p_kle_id
7444: AND stmb.sty_id = p_sty_id
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
7590: AND styv.stream_type_purpose = p_sty_purpose
7591: AND stmb.sty_id = styv.id
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
7607: AND styv.stream_type_purpose = p_sty_purpose
7608: AND stmb.sty_id = styv.id
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')
7838: AND STMB.SAY_CODE = 'CURR'
7839: AND STMB.ACTIVE_YN = 'Y'
7840: AND EXISTS (SELECT 1
7841: FROM OKL_POOL_CONTENTS POC,
7842: OKL_POOLS POL,
7843: OKL_STRM_TYPE_V STYS
7844: WHERE POC.POL_ID = POL.ID
7845: AND POC.KHR_ID = STMB.KHR_ID
7846: AND POL.KHR_ID = P_CONTRACT_ID
7847: AND POC.STY_ID = STYS.ID
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')
7861: AND STMB.SAY_CODE = 'CURR'
7861: AND STMB.SAY_CODE = 'CURR'
7862: AND STMB.ACTIVE_YN = 'Y'
7863: AND EXISTS (SELECT 1 FROM OKL_POOL_CONTENTS POC,
7864: OKL_POOLS POL,
7865: OKL_STRM_TYPE_V STYS
7866: WHERE POC.POL_ID = POL.ID
7867: AND POC.KHR_ID = STMB.KHR_ID
7868: AND POL.KHR_ID = P_CONTRACT_ID
7869: AND POC.STY_ID = STYS.ID
8054: IS
8055: SELECT
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
8075: IS
8076: SELECT
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
8285: p_grace_days IN NUMBER) IS
8286: SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
8287: FROM okl_cnsld_ar_strms_b ocas
8288: ,ar_payment_schedules aps
8289: , okl_strm_type_v sm
8290: WHERE ocas.khr_id = p_contract_id
8291: AND ocas.receivables_invoice_id = aps.customer_trx_id
8292: AND aps.class ='INV'
8293: AND (aps.due_date + p_grace_days) < SYSDATE
8299: p_grace_days IN NUMBER) IS
8300: SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
8301: FROM okl_bpd_tld_ar_lines_v ocas
8302: ,ar_payment_schedules aps
8303: , okl_strm_type_v sm
8304: WHERE ocas.khr_id = p_contract_id
8305: AND ocas.customer_trx_id = aps.customer_trx_id
8306: AND aps.class ='INV'
8307: AND (aps.due_date + p_grace_days) < SYSDATE
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,
8674: okc_statuses_b sts
8675: WHERE sele.stm_id = str.id
8676: AND str.sty_id = sty.id
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
8701: AND str.sty_id = sty.id
8702: --AND UPPER(sty.name) = 'AMORTIZED EXPENSE'
8703: AND sty.stream_type_purpose = 'AMORTIZED_FEE_EXPENSE'
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,
10190: okc_statuses_b sts
10191: WHERE sele.stm_id = str.id
10192: AND str.sty_id = sty.id
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
12038: AND stm.sty_id = sty.id
12039: AND stm.say_code = 'CURR'
12040: AND stm.active_yn = 'Y'
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
12060: AND stm.sty_id = sty.id
12061: AND stm.say_code = 'CURR' -- reporting streams are current
12062: AND stm.active_yn = 'N' -- reporting strems are inactive
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
12098: AND stm.sty_id = sty.id
12099: AND stm.say_code = 'CURR'
12100: AND stm.active_yn = 'Y'
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
13344: ,okc_line_styles_b style
13345: WHERE sele.stm_id = str.id
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
13400: ,okc_line_styles_b style
13401: ,okc_statuses_b sts
13402: WHERE sele.stm_id = str.id
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
13450: ,okc_line_styles_b style
13451: WHERE sele.stm_id = str.id
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
13507: ,okc_line_styles_b style
13508: -- ,okc_statuses_b sts
13509: WHERE sele.stm_id = str.id
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'
13571: AND stm.active_yn = 'Y'
13572: AND sty.billable_yn = 'Y'
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'
13587: AND stm.active_yn = 'Y'
13588: AND sty.billable_yn = 'Y'