[Home] [Help]
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
430: --AND UPPER(sty.name) = 'RENT'
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
479: --AND UPPER(sty.name) = 'RENT'
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
630: AND UPPER(sty.name) = 'UNEARNED INCOME'
683: SELECT NVL(kle.residual_value,0) Value,
684: NVL(kle.residual_percentage,0) Percent
685: ,ls.lty_code lty_Code --added bug 7439724
686: FROM OKC_LINE_STYLES_B LS,
687: okl_K_lines_full_v kle,
688: okc_statuses_b sts
689: WHERE LS.ID = KLE.LSE_ID
690: --Modified bug 7439724
691: AND LS.LTY_CODE in ('FREE_FORM1',
701: SELECT NVL(kle.residual_value,0) Value,
702: NVL(kle.residual_percentage,0) Percent
703: ,ls.lty_code lty_Code --added bug 7439724
704: FROM OKC_LINE_STYLES_B LS,
705: okl_K_lines_full_v kle,
706: okc_statuses_b sts
707: WHERE LS.ID = KLE.LSE_ID
708: --Modified bug 7439724
709: AND LS.LTY_CODE in ('FREE_FORM1',
905:
906: CURSOR lines_csr( chrId NUMBER ) IS
907: SELECT kle.id lineId
908: FROM OKC_LINE_STYLES_B LS,
909: okl_K_lines_full_v kle,
910: okc_statuses_b sts
911: WHERE LS.ID = KLE.LSE_ID
912: AND LS.LTY_CODE ='FREE_FORM1'
913: AND KLE.DNZ_CHR_ID = chrId
918: -- Considers either new or re-leased assets of a Contract based on p_line_type parameter
919: CURSOR lines_nr_csr( chrId NUMBER, p_line_type VARCHAR2 ) IS
920: SELECT kle.id lineId
921: FROM OKC_LINE_STYLES_B LS,
922: okl_K_lines_full_v kle,
923: okc_statuses_b sts
924: WHERE LS.ID = KLE.LSE_ID
925: AND LS.LTY_CODE ='FREE_FORM1'
926: AND KLE.DNZ_CHR_ID = chrId
1074: G_LEASE_SCS_CODE OKC_K_HEADERS_V.SCS_CODE%TYPE := 'LEASE';
1075: G_LOAN_SCS_CODE OKC_K_HEADERS_V.SCS_CODE%TYPE := 'LOAN';
1076: l_return_status VARCHAR2(3) := Okl_Api.G_RET_STS_SUCCESS;
1077: l_api_name CONSTANT VARCHAR2(30) := 'FUNCTION_OEC_CALC';
1078: ln_contract_oec OKL_K_LINES_V.OEC%TYPE := 0;
1079: lv_lty_code OKC_LINE_STYLES_V.LTY_CODE%TYPE;
1080: ln_model_line_oec OKL_K_LINES_V.OEC%TYPE := 0;
1081: ln_addon_line_oec OKL_K_LINES_V.OEC%TYPE := 0;
1082: ln_total_line_oec OKL_K_LINES_V.OEC%TYPE := 0;
1076: l_return_status VARCHAR2(3) := Okl_Api.G_RET_STS_SUCCESS;
1077: l_api_name CONSTANT VARCHAR2(30) := 'FUNCTION_OEC_CALC';
1078: ln_contract_oec OKL_K_LINES_V.OEC%TYPE := 0;
1079: lv_lty_code OKC_LINE_STYLES_V.LTY_CODE%TYPE;
1080: ln_model_line_oec OKL_K_LINES_V.OEC%TYPE := 0;
1081: ln_addon_line_oec OKL_K_LINES_V.OEC%TYPE := 0;
1082: ln_total_line_oec OKL_K_LINES_V.OEC%TYPE := 0;
1083: l_capred_incl_terminated BOOLEAN := FALSE;
1084: -- Cursor to get the lty_code
1077: l_api_name CONSTANT VARCHAR2(30) := 'FUNCTION_OEC_CALC';
1078: ln_contract_oec OKL_K_LINES_V.OEC%TYPE := 0;
1079: lv_lty_code OKC_LINE_STYLES_V.LTY_CODE%TYPE;
1080: ln_model_line_oec OKL_K_LINES_V.OEC%TYPE := 0;
1081: ln_addon_line_oec OKL_K_LINES_V.OEC%TYPE := 0;
1082: ln_total_line_oec OKL_K_LINES_V.OEC%TYPE := 0;
1083: l_capred_incl_terminated BOOLEAN := FALSE;
1084: -- Cursor to get the lty_code
1085: CURSOR get_lty_code(p_cle_id IN OKC_K_LINES_V.ID%TYPE) IS
1078: ln_contract_oec OKL_K_LINES_V.OEC%TYPE := 0;
1079: lv_lty_code OKC_LINE_STYLES_V.LTY_CODE%TYPE;
1080: ln_model_line_oec OKL_K_LINES_V.OEC%TYPE := 0;
1081: ln_addon_line_oec OKL_K_LINES_V.OEC%TYPE := 0;
1082: ln_total_line_oec OKL_K_LINES_V.OEC%TYPE := 0;
1083: l_capred_incl_terminated BOOLEAN := FALSE;
1084: -- Cursor to get the lty_code
1085: CURSOR get_lty_code(p_cle_id IN OKC_K_LINES_V.ID%TYPE) IS
1086: SELECT lse.lty_code
1090: AND cle.lse_id = lse.id;
1091: -- Cursor to sum up oec for contract
1092: CURSOR c_contract_oec_calc(p_dnz_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
1093: SELECT SUM(kle.oec) oec
1094: FROM OKL_K_LINES_V kle,
1095: OKC_K_LINES_V cle,
1096: OKC_K_HEADERS_V CHR
1097: WHERE CHR.id = p_dnz_chr_id
1098: AND CHR.id = cle.dnz_chr_id
1100: AND cle.id = kle.id;
1101:
1102: CURSOR c_contract_oec_calc_incl_term(p_dnz_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
1103: SELECT SUM(kle.oec) oec
1104: FROM OKL_K_LINES_V kle,
1105: OKC_K_LINES_V cle,
1106: OKC_K_HEADERS_V CHR
1107: WHERE CHR.id = p_dnz_chr_id
1108: AND CHR.id = cle.dnz_chr_id
1176:
1177: --cursor to get expected asset value for contract
1178: cursor l_chr_expcost_trmn_csr(p_chr_id in number) is
1179: SELECT SUM(kle.expected_asset_cost) expected_asset_cost
1180: FROM OKL_K_LINES kle,
1181: OKC_K_LINES_B cleb
1182: WHERE kle.id = cleb.id
1183: AND cleb.dnz_chr_id = p_chr_id
1184: AND cleb.lse_id = 33
1185: AND cleb.sts_code NOT IN ( 'ABANDONED', 'EXPIRED', 'CANCELLED', 'HOLD');
1186:
1187: cursor l_chr_expcost_csr(p_chr_id in number) is
1188: SELECT SUM(kle.expected_asset_cost) expected_asset_cost
1189: FROM OKL_K_LINES kle,
1190: OKC_K_LINES_B cleb
1191: WHERE kle.id = cleb.id
1192: AND cleb.dnz_chr_id = p_chr_id
1193: AND cleb.lse_id = 33
1196:
1197: --cursor to get expected asset value for asset
1198: cursor l_cle_expcost_csr (p_cle_id in number) is
1199: Select nvl(kle.expected_asset_cost,0) expected_asset_cost
1200: from okl_k_lines kle
1201: where kle.id = p_cle_id;
1202:
1203: --Bug# 15992711 , Start
1204: -- Considers either new or re-leased assets of a Contract based on p_line_type parameter
1204: -- Considers either new or re-leased assets of a Contract based on p_line_type parameter
1205: CURSOR c_nr_con_oec_calc_incl_term(p_dnz_chr_id OKC_K_HEADERS_V.ID%TYPE,
1206: p_line_type VARCHAR2) IS
1207: SELECT SUM(kle.oec) oec
1208: FROM OKL_K_LINES_V kle,
1209: OKC_K_LINES_V cle,
1210: OKC_K_HEADERS_V CHR
1211: WHERE CHR.id = p_dnz_chr_id
1212: AND CHR.id = cle.dnz_chr_id
1217:
1218: CURSOR c_nr_contract_oec_calc(p_dnz_chr_id OKC_K_HEADERS_V.ID%TYPE,
1219: p_line_type VARCHAR2) IS
1220: SELECT SUM(kle.oec) oec
1221: FROM OKL_K_LINES_V kle,
1222: OKC_K_LINES_V cle,
1223: OKC_K_HEADERS_V CHR
1224: WHERE CHR.id = p_dnz_chr_id
1225: AND CHR.id = cle.dnz_chr_id
1675:
1676: IF l_discount_incl_terminated THEN
1677: SELECT NVL(SUM(kle.tradein_amount),0) INTO l_tradeIn_value
1678: FROM OKC_LINE_STYLES_B LS,
1679: okl_K_lines_full_v kle,
1680: okc_statuses_b sts
1681: WHERE LS.ID = KLE.LSE_ID
1682: AND LS.LTY_CODE ='FREE_FORM1'
1683: AND KLE.dnz_chr_iD = p_chr_id
1688: ELSE
1689:
1690: SELECT NVL(SUM(kle.tradein_amount),0) INTO l_tradeIn_value
1691: FROM OKC_LINE_STYLES_B LS,
1692: okl_K_lines_full_v kle,
1693: okc_statuses_b sts
1694: WHERE LS.ID = KLE.LSE_ID
1695: AND LS.LTY_CODE ='FREE_FORM1'
1696: AND KLE.dnz_chr_iD = p_chr_id
1713: --Bug# 15992711 , End
1714:
1715: SELECT NVL(SUM(kle.tradein_amount),0) INTO l_tradeIn_value
1716: FROM OKC_LINE_STYLES_B LS,
1717: okl_K_lines_full_v kle,
1718: okc_statuses_b sts
1719: WHERE LS.ID = KLE.LSE_ID
1720: AND LS.LTY_CODE ='FREE_FORM1'
1721: AND KLE.dnz_chr_iD = p_chr_id
1724:
1725: ELSE
1726: SELECT NVL(SUM(kle.tradein_amount),0) INTO l_tradeIn_value
1727: FROM OKC_LINE_STYLES_B LS,
1728: okl_K_lines_full_v kle,
1729: okc_statuses_b sts
1730: WHERE LS.ID = KLE.LSE_ID
1731: AND LS.LTY_CODE ='FREE_FORM1'
1732: AND KLE.dnz_chr_iD = p_chr_id
1781: SELECT NVL(kle.tradein_amount,0.0) amnt,
1782: kle.dnz_chr_id chrId,
1783: kle.id lneId
1784: FROM OKC_LINE_STYLES_B LS,
1785: okl_K_lines_full_v kle,
1786: okc_statuses_b sts
1787: WHERE LS.ID = kLE.LSE_ID
1788: AND LS.LTY_CODE ='FREE_FORM1'
1789: AND kLE.dnz_chr_id = chrID
1795: SELECT NVL(kle.tradein_amount,0.0) amnt,
1796: kle.dnz_chr_id chrId,
1797: kle.id lneId
1798: FROM OKC_LINE_STYLES_B LS,
1799: okl_K_lines_full_v kle,
1800: okc_statuses_b sts
1801: WHERE LS.ID = kLE.LSE_ID
1802: AND LS.LTY_CODE ='FREE_FORM1'
1803: AND kLE.dnz_chr_id = chrID
1904:
1905: CURSOR l_lines_csr( chrId NUMBER ) IS
1906: SELECT kle.id
1907: FROM okc_line_styles_b ls,
1908: okl_K_lines_full_v kle,
1909: okc_statuses_b sts
1910: WHERE ls.id = kle.lse_id
1911: AND ls.lty_code = 'FREE_FORM1'
1912: AND kle.dnz_chr_id = chrId
1919:
1920: CURSOR l_lines_csr_incl_terminated( chrId NUMBER ) IS
1921: SELECT kle.id
1922: FROM okc_line_styles_b ls,
1923: okl_K_lines_full_v kle,
1924: okc_statuses_b sts
1925: WHERE ls.id = kle.lse_id
1926: AND ls.lty_code = 'FREE_FORM1'
1927: AND kle.dnz_chr_id = chrId
1935: -- Considers either new or re-leased assets of a Contract based on p_line_type parameter
1936: CURSOR l_lines_nr_csr( chrId NUMBER, p_line_type VARCHAR2 ) IS
1937: SELECT kle.id
1938: FROM okc_line_styles_b ls,
1939: okl_K_lines_full_v kle,
1940: okc_statuses_b sts
1941: WHERE ls.id = kle.lse_id
1942: AND ls.lty_code = 'FREE_FORM1'
1943: AND kle.dnz_chr_id = chrId
1952: -- Considers either new or re-leased assets of a Contract based on p_line_type parameter
1953: CURSOR l_lines_nr_csr_incl_term( chrId NUMBER, p_line_type VARCHAR2 ) IS
1954: SELECT kle.id
1955: FROM okc_line_styles_b ls,
1956: okl_K_lines_full_v kle,
1957: okc_statuses_b sts
1958: WHERE ls.id = kle.lse_id
1959: AND ls.lty_code = 'FREE_FORM1'
1960: AND kle.dnz_chr_id = chrId
2110: NVL(kle.capital_reduction_percent,0) capital_reduction_percent,
2111: NVL(kle.CAPITALIZE_DOWN_PAYMENT_YN, 'N') CAPITALIZE_DOWN_PAYMENT_YN,
2112: sts.ste_code
2113: FROM OKC_LINE_STYLES_B LS,
2114: okl_K_lines_full_v kle,
2115: okc_statuses_b sts
2116: WHERE LS.ID = KLE.LSE_ID
2117: AND LS.LTY_CODE ='FREE_FORM1'
2118: AND KLE.dnz_chr_id = p_chr_id
2154: END IF;
2155: END LOOP;
2156: /* SELECT NVL(kle.capital_reduction,0) INTO l_capred_value
2157: FROM OKC_LINE_STYLES_B LS,
2158: okl_K_lines_full_v kle,
2159: okc_statuses_b sts
2160: WHERE LS.ID = KLE.LSE_ID
2161: AND LS.LTY_CODE ='FREE_FORM1'
2162: AND KLE.dnz_chr_id = p_chr_id
2175: END LOOP;
2176:
2177: /* SELECT NVL(kle.capital_reduction,0) INTO l_capred_value
2178: FROM OKC_LINE_STYLES_B LS,
2179: okl_K_lines_full_v kle,
2180: okc_statuses_b sts
2181: WHERE LS.ID = KLE.LSE_ID
2182: AND LS.LTY_CODE ='FREE_FORM1'
2183: AND KLE.dnz_chr_id = p_chr_id
2199: END LOOP;
2200:
2201: /* SELECT NVL(kle.capital_reduction_percent,0) INTO l_capred_percent
2202: FROM OKC_LINE_STYLES_B LS,
2203: okl_K_lines_full_v kle,
2204: okc_statuses_b sts
2205: WHERE LS.ID = KLE.LSE_ID
2206: AND LS.LTY_CODE ='FREE_FORM1'
2207: AND KLE.dnz_chr_id = p_chr_id
2220: END LOOP;
2221:
2222: /* SELECT NVL(kle.capital_reduction_percent,0) INTO l_capred_percent
2223: FROM OKC_LINE_STYLES_B LS,
2224: okl_K_lines_full_v kle,
2225: okc_statuses_b sts
2226: WHERE LS.ID = KLE.LSE_ID
2227: AND LS.LTY_CODE ='FREE_FORM1'
2228: AND KLE.dnz_chr_id = p_chr_id
2285: CURSOR l_fee_csr( kleId NUMBER) IS
2286: SELECT NVL(SUM(kle_cov.capital_amount),0) CapAmountLines
2287: FROM OKC_LINE_STYLES_B LSEB,
2288: OKC_K_ITEMS CIM,
2289: OKL_K_LINES KLE_COV,
2290: OKC_K_LINES_B CLEB_COV,
2291: OKC_STATUSES_B STSB
2292: WHERE LSEB.ID = CLEB_COV.LSE_ID
2293: AND LSEB.lty_code = 'LINK_FEE_ASSET'
2302: CURSOR l_fee_csr_incl_terminated( kleId NUMBER) IS
2303: SELECT NVL(SUM(kle_cov.capital_amount),0) CapAmountLines
2304: FROM OKC_LINE_STYLES_B LSEB,
2305: OKC_K_ITEMS CIM,
2306: OKL_K_LINES KLE_COV,
2307: OKC_K_LINES_B CLEB_COV,
2308: OKC_STATUSES_B STSB
2309: WHERE LSEB.ID = CLEB_COV.LSE_ID
2310: AND LSEB.lty_code = 'LINK_FEE_ASSET'
2426:
2427: CURSOR l_lines_csr( chrId NUMBER ) IS
2428: SELECT kle.id
2429: FROM okc_line_styles_b ls,
2430: okl_K_lines_full_v kle,
2431: okc_statuses_b sts
2432: WHERE ls.id = kle.lse_id
2433: AND ls.lty_code = 'FREE_FORM1'
2434: AND kle.dnz_chr_id = chrId
2437:
2438: CURSOR l_lines_csr_incl_terminated( chrId NUMBER ) IS
2439: SELECT kle.id
2440: FROM okc_line_styles_b ls,
2441: okl_K_lines_full_v kle,
2442: okc_statuses_b sts
2443: WHERE ls.id = kle.lse_id
2444: AND ls.lty_code = 'FREE_FORM1'
2445: AND kle.dnz_chr_id = chrId
2450: -- Considers either new or re-leased assets of a Contract based on p_line_type parameter
2451: CURSOR l_lines_nr_csr( chrId NUMBER, p_line_type VARCHAR2 ) IS
2452: SELECT kle.id
2453: FROM okc_line_styles_b ls,
2454: okl_K_lines_full_v kle,
2455: okc_statuses_b sts
2456: WHERE ls.id = kle.lse_id
2457: AND ls.lty_code = 'FREE_FORM1'
2458: AND kle.dnz_chr_id = chrId
2464: -- Considers either new or re-leased assets of a Contract based on p_line_type parameter
2465: CURSOR l_lines_nr_csr_incl_term( chrId NUMBER, p_line_type VARCHAR2 ) IS
2466: SELECT kle.id
2467: FROM okc_line_styles_b ls,
2468: okl_K_lines_full_v kle,
2469: okc_statuses_b sts
2470: WHERE ls.id = kle.lse_id
2471: AND ls.lty_code = 'FREE_FORM1'
2472: AND kle.dnz_chr_id = chrId
2613: CURSOR l_srvcline_csr ( kleId NUMBER ) IS
2614: SELECT NVL(SUM(kle.capital_amount),0) CapAmountSubLines
2615: FROM OKC_LINE_STYLES_B LS,
2616: okc_k_items cim,
2617: okl_K_lines_full_v kle,
2618: okc_statuses_b sts
2619: WHERE LS.ID = KLE.LSE_ID
2620: AND ls.lty_code = 'LINK_SERV_ASSET'
2621: AND cim.jtot_object1_code = 'OKX_COVASST'
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
2635: WHERE cim.cle_id = kle.id
2636: AND ls.id = kle.lse_id
2637: AND ls.lty_code = 'SOLD_SERVICE'
3092:
3093: CURSOR C (p_contract_id NUMBER)
3094: IS
3095: SELECT NVL(SUM(A.amount),0)
3096: FROM OKL_K_LINES_FULL_V A
3097: WHERE A.dnz_chr_id = p_contract_id
3098: AND A.credit_nature = 'NEW'
3099: AND NVL(TRUNC(A.start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) -- fixed trunc issues
3100: ;
3141:
3142: CURSOR C (p_contract_id NUMBER)
3143: IS
3144: SELECT NVL(SUM(A.amount),0)
3145: FROM OKL_K_LINES_FULL_V A
3146: WHERE A.dnz_chr_id = p_contract_id
3147: AND A.credit_nature = 'ADD'
3148: AND NVL(TRUNC(A.start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) -- fixed trunc issues
3149: ;
3190:
3191: CURSOR C (p_contract_id NUMBER)
3192: IS
3193: SELECT NVL(SUM(A.amount),0)
3194: FROM OKL_K_LINES_FULL_V A
3195: WHERE A.dnz_chr_id = p_contract_id
3196: AND A.credit_nature = 'REDUCE'
3197: AND NVL(TRUNC(A.start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) -- fixed trunc issues
3198: ;
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
4177: AND stm.sty_id = sty.id
4178: AND sty.id = p_lease_inc_sty_id
4179: AND stm.active_yn = 'Y'
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
4190: AND stm.sty_id = sty.id
4191: AND sty.id = l_rent_sty_id
4192: AND stm.active_yn = 'Y'
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
4209: AND stm.sty_id = sty.id
4210: AND sty.id = l_rent_sty_id
4211: AND stm.active_yn = 'Y'
4373: WHERE id = p_ctr_id;
4374:
4375: CURSOR get_ung_res_csr(p_ctr_id NUMBER) IS
4376: SELECT SUM(NVL(RESIDUAL_VALUE,0)) - SUM(NVL(RESIDUAL_GRNTY_AMOUNT, 0))
4377: FROM OKL_K_LINES_FULL_V
4378: WHERE DNZ_CHR_ID = p_ctr_id;
4379:
4380: BEGIN
4381: OPEN contract_num_csr(p_khr_id);
6085: CURSOR C (p_contract_id NUMBER)
6086:
6087: IS
6088: SELECT NVL(SUM(RESIDUAL_VALUE),0)
6089: FROM okl_k_lines_full_v
6090: WHERE dnz_chr_id= p_contract_id
6091: AND sts_code <> 'TERMINATED';
6092:
6093: --Commented this code by rvaduri for bug 3487920
6109:
6110: CURSOR line(p_contract_line_id NUMBER)
6111: IS
6112: SELECT NVL(RESIDUAL_VALUE,0)
6113: FROM okl_k_lines
6114: WHERE id = p_contract_line_id;
6115:
6116:
6117: BEGIN
6893: -- passing accounting method as input parameter
6894: CURSOR l_sub_csr(c_contract_id IN NUMBER,c_accounting_method IN VARCHAR2) IS
6895: SELECT NVL(SUM(NVL(sub_kle.subsidy_override_amount, sub_kle.amount)),0)
6896: FROM okl_subsidies_b subb,
6897: okl_k_lines sub_kle,
6898: okc_k_lines_b sub_cle,
6899: okc_line_styles_b sub_lse
6900: WHERE subb.id = sub_kle.subsidy_id
6901: AND subb.accounting_method_code = NVL(UPPER(c_accounting_method),subb.accounting_method_code)
7910:
7911: CURSOR capz_csr( chrId NUMBER ) IS
7912: SELECT NVL( SUM(kle.capitalized_interest), 0.0)
7913: FROM OKC_LINE_STYLES_B LS,
7914: OKL_K_LINES_FULL_V KLE,
7915: okc_statuses_b sts
7916: WHERE LS.ID = KLE.LSE_ID
7917: AND LS.LTY_CODE ='FREE_FORM1'
7918: AND KLE.DNZ_CHR_ID = chrId
7925: -- Considers either new or re-leased assets of a Contract based on p_line_type parameter
7926: CURSOR capz_nr_csr( chrId NUMBER, p_line_type VARCHAR2) IS
7927: SELECT NVL( SUM(kle.capitalized_interest), 0.0)
7928: FROM OKC_LINE_STYLES_B LS,
7929: OKL_K_LINES_FULL_V KLE,
7930: okc_statuses_b sts
7931: WHERE LS.ID = KLE.LSE_ID
7932: AND LS.LTY_CODE ='FREE_FORM1'
7933: AND KLE.DNZ_CHR_ID = chrId
8024:
8025: CURSOR capz_csr( chrId NUMBER, kleId NUMBER ) IS
8026: SELECT NVL( kle.capitalized_interest, 0.0)
8027: FROM OKC_LINE_STYLES_B LS,
8028: OKL_K_LINES_FULL_V KLE
8029: WHERE LS.ID = KLE.LSE_ID
8030: AND LS.LTY_CODE ='FREE_FORM1'
8031: AND KLE.DNZ_CHR_ID = chrId
8032: AND KLE.id = kleId;
8277: */
8278: CURSOR l_okl_percent_stake_csr(p_sty_subclass IN VARCHAR2)
8279: IS
8280: SELECT DISTINCT kleb.percent_stake,clet.id
8281: FROM okl_k_lines kleb,
8282: okc_k_lines_b clet,
8283: okc_k_lines_b cles
8284: WHERE kleb.id = cles.id
8285: AND cles.cle_id = clet.id
8610: CURSOR c_amt_stake(p_contract_id okc_k_headers_b.id%TYPE) IS
8611: SELECT
8612: NVL(SUM(NVL(KLEB.AMOUNT,0)),0)
8613: FROM
8614: OKL_K_LINES KLEB,
8615: OKC_K_LINES_B CLEB,
8616: OKC_LINE_STYLES_B LSEB
8617: WHERE
8618: CLEB.ID = KLEB.ID AND
8625: CURSOR c_add_amt_stake(p_contract_id okc_k_headers_b.id%TYPE) IS
8626: SELECT
8627: NVL(SUM(NVL(KLEB.AMOUNT_STAKE,0)),0)
8628: FROM
8629: OKL_K_LINES KLEB,
8630: OKC_K_LINES_B CLEB,
8631: OKC_LINE_STYLES_B LSEB
8632: WHERE
8633: CLEB.ID = KLEB.ID AND
8714: -- get revenue share by subclass
8715: CURSOR l_okl_percent_stake_csr(p_sty_subclass IN VARCHAR2)
8716: IS
8717: SELECT DISTINCT kleb.percent_stake,clet.id
8718: FROM okl_k_lines kleb,
8719: okc_k_lines_b clet,
8720: okc_k_lines_b cles
8721: WHERE kleb.id = cles.id
8722: AND cles.cle_id = clet.id
8909: CURSOR sum_idc_csr(l_dnz_chr_id okc_k_headers_b.id%TYPE) IS
8910: SELECT
8911: NVL(SUM(NVL(KLEB.initial_direct_cost,0)),0)
8912: FROM
8913: OKL_K_LINES KLEB,
8914: OKC_K_LINES_B CLEB,
8915: OKC_LINE_STYLES_B LSEB
8916: WHERE
8917: KLEB.ID = CLEB.ID AND
9155: SELECT cleb.id
9156: FROM okc_k_lines_b cleb,
9157: okc_statuses_b stsb,
9158: okc_line_styles_b lseb,
9159: okl_k_lines kle
9160: WHERE cleb.chr_id = p_chr_id
9161: AND cleb.lse_id = lseb.id
9162: AND lseb.lty_code = 'FREE_FORM1'
9163: AND cleb.sts_code = stsb.code
9170: SELECT cleb.id
9171: FROM okc_k_lines_b cleb,
9172: okc_statuses_b stsb,
9173: okc_line_styles_b lseb,
9174: okl_k_lines kle
9175: WHERE cleb.chr_id = p_chr_id
9176: AND cleb.lse_id = lseb.id
9177: AND lseb.lty_code = 'FREE_FORM1'
9178: AND cleb.sts_code = stsb.code
9184: CURSOR l_line_nr_sts_csr (p_cle_id IN NUMBER, p_line_type VARCHAR2) IS
9185: SELECT stsb.ste_code
9186: FROM okc_statuses_b stsb,
9187: okc_k_lines_b cleb,
9188: okl_k_lines kle
9189: WHERE stsb.code = cleb.sts_code
9190: AND cleb.id = p_cle_id
9191: AND cleb.id = kle.id
9192: AND NVL(kle.re_lease_yn,'N') = DECODE(p_line_type,'NEW','N','RELEASE','Y');
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
9527: --AND UPPER(sty.name) = 'AMORTIZED EXPENSE'
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'
10575: AND ste.stm_id = stm.id
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'
10607: AND stm.say_code = 'CURR'
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
10641: AND stm.active_yn = 'Y'
10825: when ROUND(MONTHS_BETWEEN(chr.end_date, cle.start_date)) <= 12 then NVL(kle.oec,0) * .2
10826: else NVL(kle.oec,0) * .1
10827: end) subsidy_amount
10828: from okc_k_lines_b cle,
10829: okl_k_lines kle,
10830: okc_k_headers_b chr
10831: where chr.id = cle.dnz_chr_id
10832: and kle.id = cle.id
10833: and cle.id = p_cle_id -- FREE_FORM1 (FIN)
10836: CURSOR c_subsidy_amount(p_cle_id okc_k_lines_b.id%TYPE) IS
10837: SELECT ROUND(MONTHS_BETWEEN(CHR.end_date, cle.start_date)) months,
10838: NVL(kle.oec,0) oec
10839: FROM okc_k_lines_b cle,
10840: okl_k_lines kle,
10841: okc_k_headers_b CHR
10842: WHERE CHR.id = cle.dnz_chr_id
10843: AND kle.id = cle.id
10844: AND cle.id = p_cle_id -- FREE_FORM1 (FIN)
10903: sub_kle.sty_id sty_id, /* Bug 6353756 */
10904: top_cle.dnz_chr_id chr_id
10905: FROM --okl_sgn_translations sgn,
10906: okl_subsidies_b sub,
10907: okl_k_lines sub_kle,
10908: okc_k_lines_b sub_cle,
10909: okc_k_lines_b top_cle
10910: WHERE --sgn.jtot_object1_code = 'OKL_STRMTYP'
10911: --AND sgn.object1_id1 = TO_CHAR(sub_kle.sty_id) AND
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
11081: WHERE sele.stm_id = str.id
11082: AND str.sty_id = sty.id
11083: --AND UPPER(sty.name) = 'PRE-TAX INCOME'
11084: AND sty.stream_type_purpose = 'LEASE_INCOME'
11212: CURSOR sum_fin_fee_csr(l_dnz_chr_id okc_k_headers_b.id%TYPE) IS
11213: SELECT
11214: NVL(SUM(NVL(KLEB.amount,0)),0)
11215: FROM
11216: OKL_K_LINES KLEB,
11217: OKC_K_LINES_B CLEB,
11218: OKC_LINE_STYLES_B LSEB,
11219: okc_statuses_b sts
11220: WHERE
11271: CURSOR sum_abs_fee_csr(l_dnz_chr_id okc_k_headers_b.id%TYPE) IS
11272: SELECT
11273: NVL(SUM(NVL(KLEB.amount,0)),0)
11274: FROM
11275: OKL_K_LINES KLEB,
11276: OKC_K_LINES_B CLEB,
11277: OKC_LINE_STYLES_B LSEB,
11278: okc_statuses_b sts
11279: WHERE
11825: okc_statuses_b stsb,
11826: fa_additions fad,
11827: fa_book_controls fbc,
11828: fa_books fab,
11829: okl_k_lines_v kle
11830: WHERE fin_ast_cle.id = p_cle_id
11831: AND fin_ast_cle.dnz_chr_id = p_chr_id
11832: AND fin_ast_cle.chr_id = p_chr_id
11833: AND fin_ast_cle.sts_code = stsb.code
11858: okc_statuses_b stsb,
11859: fa_additions fad,
11860: fa_book_controls fbc,
11861: fa_books fab,
11862: okl_k_lines_v kle
11863: WHERE fin_ast_cle.id = p_cle_id
11864: AND fin_ast_cle.dnz_chr_id = p_chr_id
11865: AND fin_ast_cle.chr_id = p_chr_id
11866: AND fin_ast_cle.sts_code = stsb.code
12563: SELECT NVL(SUM(kle.capital_amount),0)
12564: --bug# 4899328
12565: --+ NVL(SUM(kle.capitalized_interest),0) CapAmountLines
12566: FROM OKC_LINE_STYLES_B LSEB,
12567: OKL_K_LINES KLE,
12568: OKC_K_LINES_B CLEB,
12569: OKC_STATUSES_B STSB
12570: WHERE LSEB.ID = CLEB.LSE_ID
12571: AND LSEB.lty_code = 'FREE_FORM1'
12580: CURSOR l_rollover_fee_csr(l_dnz_chr_id okc_k_headers_b.id%TYPE) IS
12581: SELECT
12582: NVL(SUM(NVL(KLEB.amount,0)),0) ROLLOVER_AMOUNT
12583: FROM
12584: OKL_K_LINES KLEB,
12585: OKC_K_LINES_B CLEB,
12586: OKC_LINE_STYLES_B LSEB,
12587: OKC_STATUSES_B STS
12588: WHERE
13811: CURSOR c_fin_fees IS
13812: SELECT cle.id
13813: FROM
13814: okc_k_lines_b cle,
13815: okl_k_lines kle,
13816: okc_k_headers_b chr
13817: WHERE
13818: chr.id = p_contract_id
13819: AND cle.chr_id = chr.id
13859: CURSOR c_fin_fees IS
13860: SELECT cle.id
13861: FROM
13862: okc_k_lines_b cle,
13863: okl_k_lines kle,
13864: okc_k_headers_b chr
13865: WHERE
13866: chr.id = p_contract_id
13867: AND cle.chr_id = chr.id
14125: RETURN NUMBER IS
14126:
14127: CURSOR get_asset_residual_csr (p_kle_id IN NUMBER) IS
14128: SELECT nvl(KLE.residual_value,0) residual_value
14129: FROM OKL_K_LINES KLE
14130: WHERE KLE.id = p_kle_id;
14131:
14132: CURSOR get_asset_sales_proceeds_csr (p_retirement_id IN NUMBER) IS
14133: SELECT nvl(RET.proceeds_of_sale,0) sales_proceeds
15674: p_dnz_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
15675: SELECT SUM(kle_cov.capital_amount) Cap_fee
15676: FROM okc_line_styles_b lseb,
15677: okc_k_items cim,
15678: okl_k_lines kle_cov,
15679: okc_k_lines_b cleb_cov,
15680: okc_statuses_b stsb
15681: WHERE lseb.id = cleb_cov.lse_id
15682: AND lseb.lty_code = 'LINK_FEE_ASSET'
15697: NVL(kle.tradein_amount,0) tradein_amount,
15698: NVL(kle.capital_reduction_percent,0) capital_reduction_percent,
15699: kle.capitalize_down_payment_yn capitalize_down_payment_yn
15700: FROM okc_line_styles_b ls,
15701: okl_k_lines_full_v kle,
15702: okc_statuses_b sts
15703: WHERE kle.dnz_chr_id = p_dnz_chr_id
15704: AND kle.id = p_line_id
15705: AND ls.id = kle.lse_id
16042: p_fin_asset_line_id IN NUMBER,
16043: p_accounting_method IN VARCHAR2) IS
16044: SELECT NVL(SUM(sub_kle.amount),0)
16045: FROM okl_subsidies_b subb,
16046: okl_k_lines sub_kle,
16047: okc_k_lines_b sub_cle,
16048: okc_line_styles_b sub_lse
16049: WHERE subb.id = sub_kle.subsidy_id
16050: AND subb.accounting_method_code = NVL(UPPER(p_accounting_method),subb.accounting_method_code)
16162: p_fin_asset_line_id IN NUMBER,
16163: p_accounting_method IN VARCHAR2) IS
16164: SELECT NVL(SUM(sub_kle.subsidy_override_amount),0)
16165: FROM okl_subsidies_b subb,
16166: okl_k_lines sub_kle,
16167: okc_k_lines_b sub_cle,
16168: okc_line_styles_b sub_lse
16169: WHERE subb.id = sub_kle.subsidy_id
16170: AND subb.accounting_method_code = NVL(UPPER(p_accounting_method),subb.accounting_method_code)
16270: CURSOR l_fee_csr( c_chr_id IN NUMBER, c_fin_asset_line_id IN NUMBER) IS
16271: SELECT sum(kle_cov.amount) asset_fin_fee_amt
16272: FROM OKC_LINE_STYLES_B LSEB,
16273: OKC_K_ITEMS CIM,
16274: OKL_K_LINES KLE_COV,
16275: okl_k_lines fee_line,
16276: OKC_K_LINES_B CLEB_COV,
16277: OKC_STATUSES_B STSB
16278: WHERE LSEB.ID = CLEB_COV.LSE_ID
16271: SELECT sum(kle_cov.amount) asset_fin_fee_amt
16272: FROM OKC_LINE_STYLES_B LSEB,
16273: OKC_K_ITEMS CIM,
16274: OKL_K_LINES KLE_COV,
16275: okl_k_lines fee_line,
16276: OKC_K_LINES_B CLEB_COV,
16277: OKC_STATUSES_B STSB
16278: WHERE LSEB.ID = CLEB_COV.LSE_ID
16279: AND LSEB.lty_code = 'LINK_FEE_ASSET'
16292: CURSOR l_fee_csr_incl_terminated( c_chr_id IN NUMBER, c_fin_asset_line_id IN NUMBER) IS
16293: SELECT sum(kle_cov.amount) asset_fin_fee_amt
16294: FROM OKC_LINE_STYLES_B LSEB,
16295: OKC_K_ITEMS CIM,
16296: OKL_K_LINES KLE_COV,
16297: okl_k_lines fee_line,
16298: OKC_K_LINES_B CLEB_COV,
16299: OKC_STATUSES_B STSB
16300: WHERE LSEB.ID = CLEB_COV.LSE_ID
16293: SELECT sum(kle_cov.amount) asset_fin_fee_amt
16294: FROM OKC_LINE_STYLES_B LSEB,
16295: OKC_K_ITEMS CIM,
16296: OKL_K_LINES KLE_COV,
16297: okl_k_lines fee_line,
16298: OKC_K_LINES_B CLEB_COV,
16299: OKC_STATUSES_B STSB
16300: WHERE LSEB.ID = CLEB_COV.LSE_ID
16301: AND LSEB.lty_code = 'LINK_FEE_ASSET'
16394: CURSOR l_fee_csr( c_chr_id IN NUMBER, c_fin_asset_line_id IN NUMBER) IS
16395: SELECT sum(kle_cov.amount) asset_roll_fee_amt
16396: FROM OKC_LINE_STYLES_B LSEB,
16397: OKC_K_ITEMS CIM,
16398: OKL_K_LINES KLE_COV,
16399: okl_k_lines fee_line,
16400: OKC_K_LINES_B CLEB_COV,
16401: OKC_STATUSES_B STSB
16402: WHERE LSEB.ID = CLEB_COV.LSE_ID
16395: SELECT sum(kle_cov.amount) asset_roll_fee_amt
16396: FROM OKC_LINE_STYLES_B LSEB,
16397: OKC_K_ITEMS CIM,
16398: OKL_K_LINES KLE_COV,
16399: okl_k_lines fee_line,
16400: OKC_K_LINES_B CLEB_COV,
16401: OKC_STATUSES_B STSB
16402: WHERE LSEB.ID = CLEB_COV.LSE_ID
16403: AND LSEB.lty_code = 'LINK_FEE_ASSET'
16416: CURSOR l_fee_csr_incl_terminated( c_chr_id IN NUMBER, c_fin_asset_line_id IN NUMBER) IS
16417: SELECT sum(kle_cov.amount) asset_roll_fee_amt
16418: FROM OKC_LINE_STYLES_B LSEB,
16419: OKC_K_ITEMS CIM,
16420: OKL_K_LINES KLE_COV,
16421: okl_k_lines fee_line,
16422: OKC_K_LINES_B CLEB_COV,
16423: OKC_STATUSES_B STSB
16424: WHERE LSEB.ID = CLEB_COV.LSE_ID
16417: SELECT sum(kle_cov.amount) asset_roll_fee_amt
16418: FROM OKC_LINE_STYLES_B LSEB,
16419: OKC_K_ITEMS CIM,
16420: OKL_K_LINES KLE_COV,
16421: okl_k_lines fee_line,
16422: OKC_K_LINES_B CLEB_COV,
16423: OKC_STATUSES_B STSB
16424: WHERE LSEB.ID = CLEB_COV.LSE_ID
16425: AND LSEB.lty_code = 'LINK_FEE_ASSET'