[Home] [Help]
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,
427: okc_statuses_b sts
428: WHERE sele.stm_id = str.id
429: AND str.sty_id = sty.id
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
456: AND str.sty_id = sty.id
457: --AND UPPER(sty.name) = 'RENT'
458: AND sty.stream_type_purpose = 'RENT'
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,
476: okc_statuses_b sts
477: WHERE sele.stm_id = str.id
478: AND str.sty_id = sty.id
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
628: WHERE sele.stm_id = str.id
629: AND str.sty_id = sty.id
2626:
2627: CURSOR srvc_strm_type_csr ( kleid NUMBER ) IS
2628: SELECT sty.capitalize_yn,
2629: sty.name
2630: FROM okl_strm_type_v sty,
2631: okc_k_items cim,
2632: okc_line_styles_b ls,
2633: okl_K_lines_full_v kle,
2634: okc_statuses_b sts
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
5787: AND stm.say_code = 'CURR'
5788: AND stm.active_yn = 'Y'
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
5807: AND stm.say_code = 'CURR' -- reporting streams are current
5808: AND stm.active_yn = 'N' -- reporting strems are inactive
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
6038: AND UPPER(sty.stream_type_purpose) = 'SECURITY_DEPOSIT'
6039: AND str.say_code = 'CURR'
6092:
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
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
6102: AND UPPER(sty.name) = 'RESIDUAL VALUE'
6103: AND str.say_code = 'CURR'
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
6176: AND UPPER(sty.stream_type_purpose) = 'RENT'
6177: AND str.say_code = 'CURR'
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
6193: --AND UPPER(sty.name) = 'RENT' --sechawla 04-dec-08 : remoevd
6194: AND UPPER(sty.stream_type_purpose) = 'RENT' --sechawla 04-dec-08 : added
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
6257: AND str.sty_id = sty.id
6258: AND UPPER(sty.stream_type_purpose) = 'LEASE_INCOME'
6259: AND str.say_code = 'CURR'
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
6274: AND UPPER(sty.name) = 'UNEARNED INCOME'
6275: AND str.say_code = 'CURR'
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
6290: AND str.sty_id = sty.id
6291: AND UPPER(sty.name) = 'PRE-TAX INCOME'
6292: AND str.say_code = 'CURR'
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
6307: AND stm.say_code = 'CURR'
6308: AND stm.active_yn = 'Y'
6528: /*
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
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
6538: AND str.sty_id = sty.id
6539: AND UPPER(sty.name) = 'PRINCIPAL BALANCE'
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
6563: AND stm.say_code = 'CURR'
6564: AND stm.active_yn = 'Y'
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'
6575: AND stm.active_yn = 'Y'
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
6755: AND stm.say_code = 'CURR'
6756: AND stm.active_yn = 'Y'
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'
6767: AND stm.active_yn = 'Y'
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
6783: AND stm.say_code = 'CURR'
6784: AND stm.active_yn = 'Y'
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'
6795: AND stm.active_yn = 'Y'
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
7188: AND stm.say_code = 'CURR'
7189: AND stm.active_yn = 'Y'
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
7208: AND stm.say_code = 'CURR'
7209: AND stm.active_yn = 'Y'
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
8091: WHERE stmb.khr_id = p_dnz_chr_id
8092: AND stmb.kle_id = p_kle_id
8093: AND stmb.sty_id = p_sty_id
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
8241: WHERE stmb.source_id = p_chr_id
8242: AND styv.stream_type_purpose = p_sty_purpose
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
8241: WHERE stmb.source_id = p_chr_id
8242: AND styv.stream_type_purpose = p_sty_purpose
8243: AND stmb.sty_id = styv.id
8244: AND selb.stm_id = stmb.id
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
8263: WHERE stmb.source_id = p_chr_id
8264: AND styv.stream_type_purpose = p_sty_purpose
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
8263: WHERE stmb.source_id = p_chr_id
8264: AND styv.stream_type_purpose = p_sty_purpose
8265: AND stmb.sty_id = styv.id
8266: AND selb.stm_id = stmb.id
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')
8512: AND STMB.SAY_CODE = 'CURR'
8513: AND STMB.ACTIVE_YN = 'Y'
8514: AND EXISTS (SELECT 1
8515: FROM OKL_POOL_CONTENTS POC,
8516: OKL_POOLS POL,
8517: OKL_STRM_TYPE_V STYS
8518: WHERE POC.POL_ID = POL.ID
8519: AND POC.KHR_ID = STMB.KHR_ID
8520: AND POL.KHR_ID = P_CONTRACT_ID
8521: AND POC.STY_ID = STYS.ID
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')
8535: AND STMB.SAY_CODE = 'CURR'
8535: AND STMB.SAY_CODE = 'CURR'
8536: AND STMB.ACTIVE_YN = 'Y'
8537: AND EXISTS (SELECT 1 FROM OKL_POOL_CONTENTS POC,
8538: OKL_POOLS POL,
8539: OKL_STRM_TYPE_V STYS
8540: WHERE POC.POL_ID = POL.ID
8541: AND POC.KHR_ID = STMB.KHR_ID
8542: AND POL.KHR_ID = P_CONTRACT_ID
8543: AND POC.STY_ID = STYS.ID
8728: IS
8729: SELECT
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
8749: IS
8750: SELECT
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
8959: p_grace_days IN NUMBER) IS
8960: SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
8961: FROM okl_cnsld_ar_strms_b ocas
8962: ,ar_payment_schedules aps
8963: , okl_strm_type_v sm
8964: WHERE ocas.khr_id = p_contract_id
8965: AND ocas.receivables_invoice_id = aps.customer_trx_id
8966: AND aps.class ='INV'
8967: AND (aps.due_date + p_grace_days) < SYSDATE
8973: p_grace_days IN NUMBER) IS
8974: SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
8975: FROM okl_bpd_tld_ar_lines_v ocas
8976: ,ar_payment_schedules aps
8977: , okl_strm_type_v sm
8978: WHERE ocas.khr_id = p_contract_id
8979: AND ocas.customer_trx_id = aps.customer_trx_id
8980: AND aps.class ='INV'
8981: AND (aps.due_date + p_grace_days) < SYSDATE
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,
9524: okc_statuses_b sts
9525: WHERE sele.stm_id = str.id
9526: AND str.sty_id = sty.id
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
9551: AND str.sty_id = sty.id
9552: --AND UPPER(sty.name) = 'AMORTIZED EXPENSE'
9553: AND sty.stream_type_purpose = 'AMORTIZED_FEE_EXPENSE'
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,
11047: okc_statuses_b sts
11048: WHERE sele.stm_id = str.id
11049: AND str.sty_id = sty.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,
11078: okc_statuses_b sts,
11079: okc_k_lines_b cle,
11080: okl_k_lines kle
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
13235: AND stm.sty_id = sty.id
13236: AND stm.say_code = 'CURR'
13237: AND stm.active_yn = 'Y'
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
13257: AND stm.sty_id = sty.id
13258: AND stm.say_code = 'CURR' -- reporting streams are current
13259: AND stm.active_yn = 'N' -- reporting strems are inactive
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
13295: AND stm.sty_id = sty.id
13296: AND stm.say_code = 'CURR'
13297: AND stm.active_yn = 'Y'
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
14576: ,okc_line_styles_b style
14577: WHERE sele.stm_id = str.id
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
14632: ,okc_line_styles_b style
14633: ,okc_statuses_b sts
14634: WHERE sele.stm_id = str.id
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
14682: ,okc_line_styles_b style
14683: WHERE sele.stm_id = str.id
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
14739: ,okc_line_styles_b style
14740: -- ,okc_statuses_b sts
14741: WHERE sele.stm_id = str.id
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'
14803: AND stm.active_yn = 'Y'
14804: AND sty.billable_yn = 'Y'
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'
14819: AND stm.active_yn = 'Y'
14820: AND sty.billable_yn = 'Y'