[Home] [Help]
608:
609: CURSOR residual_csr( chrId NUMBER, lineId NUMBER ) IS
610: SELECT NVL(kle.residual_value,0) Value,
611: NVL(kle.residual_percentage,0) Percent
612: FROM OKC_LINE_STYLES_B LS,
613: okl_K_lines_full_v kle,
614: okc_statuses_b sts
615: WHERE LS.ID = KLE.LSE_ID
616: AND LS.LTY_CODE ='FREE_FORM1'
621:
622: CURSOR residual_csr_incl_terminated( chrId NUMBER, lineId NUMBER ) IS
623: SELECT NVL(kle.residual_value,0) Value,
624: NVL(kle.residual_percentage,0) Percent
625: FROM OKC_LINE_STYLES_B LS,
626: okl_K_lines_full_v kle,
627: okc_statuses_b sts
628: WHERE LS.ID = KLE.LSE_ID
629: AND LS.LTY_CODE ='FREE_FORM1'
818: l_lne_res_value NUMBER := 0.0;
819:
820: CURSOR lines_csr( chrId NUMBER ) IS
821: SELECT kle.id lineId
822: FROM OKC_LINE_STYLES_B LS,
823: okl_K_lines_full_v kle,
824: okc_statuses_b sts
825: WHERE LS.ID = KLE.LSE_ID
826: AND LS.LTY_CODE ='FREE_FORM1'
939: -- Cursor to get the lty_code
940: CURSOR get_lty_code(p_cle_id IN OKC_K_LINES_V.ID%TYPE) IS
941: SELECT lse.lty_code
942: FROM okc_k_lines_b cle,
943: okc_line_styles_b lse
944: WHERE cle.id = p_cle_id
945: AND cle.lse_id = lse.id;
946: -- Cursor to sum up oec for contract
947: CURSOR c_contract_oec_calc(p_dnz_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
967: CURSOR c_model_oec_calc(p_top_cle_id OKC_K_LINES_V.CLE_ID%TYPE,
968: p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
969: SELECT SUM(cle.price_unit * cim.number_of_items) oec
970: FROM okc_subclass_top_line stl,
971: okc_line_styles_b lse2,
972: okc_line_styles_b lse1,
973: okc_k_items_v cim,
974: okc_k_lines_v cle
975: WHERE cle.cle_id = p_top_cle_id
968: p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
969: SELECT SUM(cle.price_unit * cim.number_of_items) oec
970: FROM okc_subclass_top_line stl,
971: okc_line_styles_b lse2,
972: okc_line_styles_b lse1,
973: okc_k_items_v cim,
974: okc_k_lines_v cle
975: WHERE cle.cle_id = p_top_cle_id
976: AND cle.dnz_chr_id = p_dnz_chr_id
986: CURSOR c_addon_oec_calc(p_top_cle_id OKC_K_LINES_V.CLE_ID%TYPE,
987: p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
988: SELECT SUM(cle.price_unit* cim.number_of_items) oec
989: FROM okc_subclass_top_line stl,
990: okc_line_styles_b lse3,
991: okc_line_styles_b lse2,
992: okc_line_styles_b lse1,
993: okc_k_items_v cim,
994: okc_k_lines_b cle
987: p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
988: SELECT SUM(cle.price_unit* cim.number_of_items) oec
989: FROM okc_subclass_top_line stl,
990: okc_line_styles_b lse3,
991: okc_line_styles_b lse2,
992: okc_line_styles_b lse1,
993: okc_k_items_v cim,
994: okc_k_lines_b cle
995: WHERE cle.dnz_chr_id = p_dnz_chr_id
988: SELECT SUM(cle.price_unit* cim.number_of_items) oec
989: FROM okc_subclass_top_line stl,
990: okc_line_styles_b lse3,
991: okc_line_styles_b lse2,
992: okc_line_styles_b lse1,
993: okc_k_items_v cim,
994: okc_k_lines_b cle
995: WHERE cle.dnz_chr_id = p_dnz_chr_id
996: AND cle.dnz_chr_id = cim.dnz_chr_id
1005: AND stl.scs_code IN (G_LEASE_SCS_CODE,G_LOAN_SCS_CODE)
1006: --AND cle.cle_id IN (SELECT cle.id
1007: AND exists (SELECT 1 --cle.id
1008: FROM okc_subclass_top_line stlx,
1009: okc_line_styles_b lse2x,
1010: okc_line_styles_b lse1x,
1011: okc_k_lines_b clex
1012: WHERE clex.cle_id = p_top_cle_id
1013: AND clex.dnz_chr_id = p_dnz_chr_id
1006: --AND cle.cle_id IN (SELECT cle.id
1007: AND exists (SELECT 1 --cle.id
1008: FROM okc_subclass_top_line stlx,
1009: okc_line_styles_b lse2x,
1010: okc_line_styles_b lse1x,
1011: okc_k_lines_b clex
1012: WHERE clex.cle_id = p_top_cle_id
1013: AND clex.dnz_chr_id = p_dnz_chr_id
1014:
1409: -- AND Okl_Execute_Formula_Pub.g_additional_parameters(0).value = 'Y' THEN
1410: -- rmunjulu 4042892
1411: IF l_discount_incl_terminated THEN
1412: SELECT NVL(SUM(kle.tradein_amount),0) INTO l_tradeIn_value
1413: FROM OKC_LINE_STYLES_B LS,
1414: okl_K_lines_full_v kle,
1415: okc_statuses_b sts
1416: WHERE LS.ID = KLE.LSE_ID
1417: AND LS.LTY_CODE ='FREE_FORM1'
1419: AND kle.sts_code = sts.code
1420: AND sts.ste_code NOT IN ('EXPIRED', 'CANCELLED', 'HOLD');
1421: ELSE
1422: SELECT NVL(SUM(kle.tradein_amount),0) INTO l_tradeIn_value
1423: FROM OKC_LINE_STYLES_B LS,
1424: okl_K_lines_full_v kle,
1425: okc_statuses_b sts
1426: WHERE LS.ID = KLE.LSE_ID
1427: AND LS.LTY_CODE ='FREE_FORM1'
1476: CURSOR trdinval_csr( chrID NUMBER, lineID NUMBER) IS
1477: SELECT NVL(kle.tradein_amount,0.0) amnt,
1478: kle.dnz_chr_id chrId,
1479: kle.id lneId
1480: FROM OKC_LINE_STYLES_B LS,
1481: okl_K_lines_full_v kle,
1482: okc_statuses_b sts
1483: WHERE LS.ID = kLE.LSE_ID
1484: AND LS.LTY_CODE ='FREE_FORM1'
1490: CURSOR trdinval_csr_incl_terminated( chrID NUMBER, lineID NUMBER) IS
1491: SELECT NVL(kle.tradein_amount,0.0) amnt,
1492: kle.dnz_chr_id chrId,
1493: kle.id lneId
1494: FROM OKC_LINE_STYLES_B LS,
1495: okl_K_lines_full_v kle,
1496: okc_statuses_b sts
1497: WHERE LS.ID = kLE.LSE_ID
1498: AND LS.LTY_CODE ='FREE_FORM1'
1591: l_capred_value NUMBER := 0;
1592:
1593: CURSOR l_lines_csr( chrId NUMBER ) IS
1594: SELECT kle.id
1595: FROM okc_line_styles_b ls,
1596: okl_K_lines_full_v kle,
1597: okc_statuses_b sts
1598: WHERE ls.id = kle.lse_id
1599: AND ls.lty_code = 'FREE_FORM1'
1606:
1607:
1608: CURSOR l_lines_csr_incl_terminated( chrId NUMBER ) IS
1609: SELECT kle.id
1610: FROM okc_line_styles_b ls,
1611: okl_K_lines_full_v kle,
1612: okc_statuses_b sts
1613: WHERE ls.id = kle.lse_id
1614: AND ls.lty_code = 'FREE_FORM1'
1718: SELECT NVL(kle.capital_reduction,0) capital_reduction,
1719: NVL(kle.capital_reduction_percent,0) capital_reduction_percent,
1720: NVL(kle.CAPITALIZE_DOWN_PAYMENT_YN, 'N') CAPITALIZE_DOWN_PAYMENT_YN,
1721: sts.ste_code
1722: FROM OKC_LINE_STYLES_B LS,
1723: okl_K_lines_full_v kle,
1724: okc_statuses_b sts
1725: WHERE LS.ID = KLE.LSE_ID
1726: AND LS.LTY_CODE ='FREE_FORM1'
1762: l_capred_value := this_row.capital_reduction;
1763: END IF;
1764: END LOOP;
1765: /* SELECT NVL(kle.capital_reduction,0) INTO l_capred_value
1766: FROM OKC_LINE_STYLES_B LS,
1767: okl_K_lines_full_v kle,
1768: okc_statuses_b sts
1769: WHERE LS.ID = KLE.LSE_ID
1770: AND LS.LTY_CODE ='FREE_FORM1'
1783: END IF;
1784: END LOOP;
1785:
1786: /* SELECT NVL(kle.capital_reduction,0) INTO l_capred_value
1787: FROM OKC_LINE_STYLES_B LS,
1788: okl_K_lines_full_v kle,
1789: okc_statuses_b sts
1790: WHERE LS.ID = KLE.LSE_ID
1791: AND LS.LTY_CODE ='FREE_FORM1'
1807: END IF;
1808: END LOOP;
1809:
1810: /* SELECT NVL(kle.capital_reduction_percent,0) INTO l_capred_percent
1811: FROM OKC_LINE_STYLES_B LS,
1812: okl_K_lines_full_v kle,
1813: okc_statuses_b sts
1814: WHERE LS.ID = KLE.LSE_ID
1815: AND LS.LTY_CODE ='FREE_FORM1'
1828: END IF;
1829: END LOOP;
1830:
1831: /* SELECT NVL(kle.capital_reduction_percent,0) INTO l_capred_percent
1832: FROM OKC_LINE_STYLES_B LS,
1833: okl_K_lines_full_v kle,
1834: okc_statuses_b sts
1835: WHERE LS.ID = KLE.LSE_ID
1836: AND LS.LTY_CODE ='FREE_FORM1'
1892: l_fees_value NUMBER := 0;
1893:
1894: CURSOR l_fee_csr( kleId NUMBER) IS
1895: SELECT NVL(SUM(kle_cov.capital_amount),0) CapAmountLines
1896: FROM OKC_LINE_STYLES_B LSEB,
1897: OKC_K_ITEMS CIM,
1898: OKL_K_LINES KLE_COV,
1899: OKC_K_LINES_B CLEB_COV,
1900: OKC_STATUSES_B STSB
1909: AND STSB.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
1910:
1911: CURSOR l_fee_csr_incl_terminated( kleId NUMBER) IS
1912: SELECT NVL(SUM(kle_cov.capital_amount),0) CapAmountLines
1913: FROM OKC_LINE_STYLES_B LSEB,
1914: OKC_K_ITEMS CIM,
1915: OKL_K_LINES KLE_COV,
1916: OKC_K_LINES_B CLEB_COV,
1917: OKC_STATUSES_B STSB
2034:
2035:
2036: CURSOR l_lines_csr( chrId NUMBER ) IS
2037: SELECT kle.id
2038: FROM okc_line_styles_b ls,
2039: okl_K_lines_full_v kle,
2040: okc_statuses_b sts
2041: WHERE ls.id = kle.lse_id
2042: AND ls.lty_code = 'FREE_FORM1'
2045: AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
2046:
2047: CURSOR l_lines_csr_incl_terminated( chrId NUMBER ) IS
2048: SELECT kle.id
2049: FROM okc_line_styles_b ls,
2050: okl_K_lines_full_v kle,
2051: okc_statuses_b sts
2052: WHERE ls.id = kle.lse_id
2053: AND ls.lty_code = 'FREE_FORM1'
2147:
2148:
2149: CURSOR l_srvcline_csr ( kleId NUMBER ) IS
2150: SELECT NVL(SUM(kle.capital_amount),0) CapAmountSubLines
2151: FROM OKC_LINE_STYLES_B LS,
2152: okc_k_items cim,
2153: okl_K_lines_full_v kle,
2154: okc_statuses_b sts
2155: WHERE LS.ID = KLE.LSE_ID
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
2171: WHERE cim.cle_id = kle.id
2172: AND ls.id = kle.lse_id
4208: /*
4209: CURSOR l_item_loc_csr (cp_fin_asset_cle_id NUMBER) IS
4210: SELECT DISTINCT LOA.loc_id location_id, LOC.postal_code -- 3682465
4211: FROM OKC_K_LINES_B KLE_FA,
4212: OKC_LINE_STYLES_B LSE_FA,
4213: OKC_K_LINES_B KLE_IL,
4214: OKC_LINE_STYLES_B LSE_IL,
4215: OKC_K_LINES_B KLE_IB,
4216: OKC_LINE_STYLES_B LSE_IB,
4210: SELECT DISTINCT LOA.loc_id location_id, LOC.postal_code -- 3682465
4211: FROM OKC_K_LINES_B KLE_FA,
4212: OKC_LINE_STYLES_B LSE_FA,
4213: OKC_K_LINES_B KLE_IL,
4214: OKC_LINE_STYLES_B LSE_IL,
4215: OKC_K_LINES_B KLE_IB,
4216: OKC_LINE_STYLES_B LSE_IB,
4217: OKC_K_ITEMS ITE,
4218: CSI_ITEM_INSTANCES CII,
4212: OKC_LINE_STYLES_B LSE_FA,
4213: OKC_K_LINES_B KLE_IL,
4214: OKC_LINE_STYLES_B LSE_IL,
4215: OKC_K_LINES_B KLE_IB,
4216: OKC_LINE_STYLES_B LSE_IB,
4217: OKC_K_ITEMS ITE,
4218: CSI_ITEM_INSTANCES CII,
4219: HZ_PARTY_SITES PSI,
4220: HZ_LOCATIONS LOC,
4242: AND loc.location_id = loa.location_id
4243: UNION
4244: SELECT DISTINCT LOA.loc_id location_id, LOC.postal_code -- 3682465
4245: FROM OKC_K_LINES_B KLE_FA,
4246: OKC_LINE_STYLES_B LSE_FA,
4247: OKC_K_LINES_B KLE_IL,
4248: OKC_LINE_STYLES_B LSE_IL,
4249: OKC_K_LINES_B KLE_IB,
4250: OKC_LINE_STYLES_B LSE_IB,
4244: SELECT DISTINCT LOA.loc_id location_id, LOC.postal_code -- 3682465
4245: FROM OKC_K_LINES_B KLE_FA,
4246: OKC_LINE_STYLES_B LSE_FA,
4247: OKC_K_LINES_B KLE_IL,
4248: OKC_LINE_STYLES_B LSE_IL,
4249: OKC_K_LINES_B KLE_IB,
4250: OKC_LINE_STYLES_B LSE_IB,
4251: OKC_K_ITEMS ITE,
4252: CSI_ITEM_INSTANCES CII,
4246: OKC_LINE_STYLES_B LSE_FA,
4247: OKC_K_LINES_B KLE_IL,
4248: OKC_LINE_STYLES_B LSE_IL,
4249: OKC_K_LINES_B KLE_IB,
4250: OKC_LINE_STYLES_B LSE_IB,
4251: OKC_K_ITEMS ITE,
4252: CSI_ITEM_INSTANCES CII,
4253: --HZ_PARTY_SITES PSI,
4254: HZ_LOCATIONS LOC,
6028: okl_bpd_tld_ar_lines_v LSM,
6029: -- OKC_K_HEADERS_B CHR, commenting unused table, ankushar Bug# 5499193
6030: /* 16-Aug-2007 ankushar end changes */
6031: OKC_K_LINES_B CLE,
6032: OKC_LINE_STYLES_B LSE
6033: WHERE
6034: LSM.KLE_ID = p_contract_line_id
6035: AND LSM.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
6036: AND APS.STATUS = 'OP'
6088: okl_bpd_tld_ar_lines_v LSM,
6089: -- OKC_K_HEADERS_B CHR, commenting unused table, ankushar Bug# 5499193
6090: /* 16-Aug-2007 ankushar end changes */
6091: OKC_K_LINES_B CLE,
6092: OKC_LINE_STYLES_B LSE
6093: WHERE
6094: LSM.KLE_ID = p_contract_line_id
6095: AND LSM.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
6096: AND APS.STATUS = 'OP'
6297: SELECT NVL(SUM(NVL(sub_kle.subsidy_override_amount, sub_kle.amount)),0)
6298: FROM okl_subsidies_b subb,
6299: okl_k_lines sub_kle,
6300: okc_k_lines_b sub_cle,
6301: okc_line_styles_b sub_lse
6302: WHERE subb.id = sub_kle.subsidy_id
6303: AND subb.accounting_method_code = NVL(UPPER(c_accounting_method),subb.accounting_method_code)
6304: AND sub_kle.id = sub_cle.id
6305: AND sub_cle.lse_id = sub_lse.id
7311: l_capz_int NUMBER := 0.0;
7312:
7313: CURSOR capz_csr( chrId NUMBER ) IS
7314: SELECT NVL( SUM(kle.capitalized_interest), 0.0)
7315: FROM OKC_LINE_STYLES_B LS,
7316: OKL_K_LINES_FULL_V KLE,
7317: okc_statuses_b sts
7318: WHERE LS.ID = KLE.LSE_ID
7319: AND LS.LTY_CODE ='FREE_FORM1'
7374: l_capz_int NUMBER := 0.0;
7375:
7376: CURSOR capz_csr( chrId NUMBER, kleId NUMBER ) IS
7377: SELECT NVL( kle.capitalized_interest, 0.0)
7378: FROM OKC_LINE_STYLES_B LS,
7379: OKL_K_LINES_FULL_V KLE
7380: WHERE LS.ID = KLE.LSE_ID
7381: AND LS.LTY_CODE ='FREE_FORM1'
7382: AND KLE.DNZ_CHR_ID = chrId
7938: NVL(SUM(NVL(KLEB.AMOUNT,0)),0)
7939: FROM
7940: OKL_K_LINES KLEB,
7941: OKC_K_LINES_B CLEB,
7942: OKC_LINE_STYLES_B LSEB
7943: WHERE
7944: CLEB.ID = KLEB.ID AND
7945: CLEB.LSE_ID = LSEB.ID AND
7946: LSEB.LTY_CODE = 'INVESTMENT' AND
7953: NVL(SUM(NVL(KLEB.AMOUNT_STAKE,0)),0)
7954: FROM
7955: OKL_K_LINES KLEB,
7956: OKC_K_LINES_B CLEB,
7957: OKC_LINE_STYLES_B LSEB
7958: WHERE
7959: CLEB.ID = KLEB.ID AND
7960: CLEB.LSE_ID = LSEB.ID AND
7961: LSEB.LTY_CODE = 'INVESTMENT' AND
8237: NVL(SUM(NVL(KLEB.initial_direct_cost,0)),0)
8238: FROM
8239: OKL_K_LINES KLEB,
8240: OKC_K_LINES_B CLEB,
8241: OKC_LINE_STYLES_B LSEB
8242: WHERE
8243: KLEB.ID = CLEB.ID AND
8244: CLEB.LSE_ID = LSEB.ID AND
8245: LSEB.LTY_CODE = 'FEE' AND
8455: CURSOR l_cleb_csr (p_chr_id IN NUMBER) IS
8456: SELECT cleb.id
8457: FROM okc_k_lines_b cleb,
8458: okc_statuses_b stsb,
8459: okc_line_styles_b lseb
8460: WHERE cleb.chr_id = p_chr_id
8461: AND cleb.lse_id = lseb.id
8462: AND lseb.lty_code = 'FREE_FORM1'
8463: AND cleb.sts_code = stsb.code
8467: CURSOR l_cleb_termn_csr (p_chr_id IN NUMBER) IS
8468: SELECT cleb.id
8469: FROM okc_k_lines_b cleb,
8470: okc_statuses_b stsb,
8471: okc_line_styles_b lseb
8472: WHERE cleb.chr_id = p_chr_id
8473: AND cleb.lse_id = lseb.id
8474: AND lseb.lty_code = 'FREE_FORM1'
8475: AND cleb.sts_code = stsb.code
9708: FROM okl_streams stm,
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'
9740: FROM okl_streams stm
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
9774: FROM okl_streams stm
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
10277: NVL(SUM(NVL(KLEB.amount,0)),0)
10278: FROM
10279: OKL_K_LINES KLEB,
10280: OKC_K_LINES_B CLEB,
10281: OKC_LINE_STYLES_B LSEB,
10282: okc_statuses_b sts
10283: WHERE
10284: KLEB.ID = CLEB.ID AND
10285: CLEB.LSE_ID = LSEB.ID AND
10336: NVL(SUM(NVL(KLEB.amount,0)),0)
10337: FROM
10338: OKL_K_LINES KLEB,
10339: OKC_K_LINES_B CLEB,
10340: OKC_LINE_STYLES_B LSEB,
10341: okc_statuses_b sts
10342: WHERE
10343: KLEB.ID = CLEB.ID AND
10344: CLEB.LSE_ID = LSEB.ID AND
11234:
11235: CURSOR l_finast_csr(p_chr_id IN NUMBER) IS
11236: SELECT fin_cle.id
11237: FROM okc_k_lines_b fin_cle,
11238: okc_line_styles_b lse,
11239: okc_statuses_b stsb
11240: WHERE fin_cle.dnz_chr_id = p_chr_id
11241: AND fin_cle.chr_id = p_chr_id
11242: AND fin_cle.lse_id = lse.id
11246:
11247: CURSOR l_finast_csr_incl_terminated(p_chr_id IN NUMBER) IS
11248: SELECT fin_cle.id
11249: FROM okc_k_lines_b fin_cle,
11250: okc_line_styles_b lse,
11251: okc_statuses_b stsb
11252: WHERE fin_cle.dnz_chr_id = p_chr_id
11253: AND fin_cle.chr_id = p_chr_id
11254: AND fin_cle.lse_id = lse.id
11352:
11353: CURSOR l_finast_csr(p_chr_id IN NUMBER) IS
11354: SELECT fin_cle.id
11355: FROM okc_k_lines_b fin_cle,
11356: okc_line_styles_b lse,
11357: okc_statuses_b stsb
11358: WHERE fin_cle.dnz_chr_id = p_chr_id
11359: AND fin_cle.chr_id = p_chr_id
11360: AND fin_cle.lse_id = lse.id
11364:
11365: CURSOR l_finast_csr_incl_terminated(p_chr_id IN NUMBER) IS
11366: SELECT fin_cle.id
11367: FROM okc_k_lines_b fin_cle,
11368: okc_line_styles_b lse,
11369: okc_statuses_b stsb
11370: WHERE fin_cle.dnz_chr_id = p_chr_id
11371: AND fin_cle.chr_id = p_chr_id
11372: AND fin_cle.lse_id = lse.id
11474: CURSOR l_cap_amnt_csr( ChrId NUMBER) IS
11475: SELECT NVL(SUM(kle.capital_amount),0)
11476: --bug# 4899328
11477: --+ NVL(SUM(kle.capitalized_interest),0) CapAmountLines
11478: FROM OKC_LINE_STYLES_B LSEB,
11479: OKL_K_LINES KLE,
11480: OKC_K_LINES_B CLEB,
11481: OKC_STATUSES_B STSB
11482: WHERE LSEB.ID = CLEB.LSE_ID
11494: NVL(SUM(NVL(KLEB.amount,0)),0) ROLLOVER_AMOUNT
11495: FROM
11496: OKL_K_LINES KLEB,
11497: OKC_K_LINES_B CLEB,
11498: OKC_LINE_STYLES_B LSEB,
11499: OKC_STATUSES_B STS
11500: WHERE
11501: KLEB.ID = CLEB.ID AND
11502: CLEB.LSE_ID = LSEB.ID AND
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
13346: AND str.sty_id = sty.id
13347: AND sty.stream_type_purpose = 'RENT'
13348: AND str.say_code = 'CURR'
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
13403: AND str.sty_id = sty.id
13404: AND UPPER(sty.stream_type_purpose) = 'LEASE_INCOME' -- pre-tax income has steam type purpose as lease_income
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
13452: AND str.sty_id = sty.id
13453: AND sty.stream_type_purpose = 'RENT'
13454: AND str.say_code = 'CURR'
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
13510: AND str.sty_id = sty.id
13511: AND UPPER(sty.stream_type_purpose) = 'LEASE_INCOME' -- pre-tax income has steam type purpose as lease_income
14376: -- Cursor to get the lty_code
14377: CURSOR get_lty_code(p_line_id NUMBER) IS
14378: SELECT lse.lty_code
14379: FROM okc_k_lines_b cle,
14380: okc_line_styles_b lse
14381: WHERE cle.id = p_line_id
14382: AND cle.lse_id = lse.id;
14383:
14384: -- Cursor to sum up asset cost for given Asset line
14385: CURSOR c_asset_cost(p_line_id NUMBER,
14386: p_dnz_chr_id NUMBER) IS
14387: SELECT SUM(cle.price_unit * cim.number_of_items) asset_cost
14388: FROM okc_subclass_top_line stl,
14389: okc_line_styles_b lse2,
14390: okc_line_styles_b lse1,
14391: okc_k_items_v cim,
14392: okc_k_lines_v cle
14393: WHERE cle.cle_id = p_line_id
14386: p_dnz_chr_id NUMBER) IS
14387: SELECT SUM(cle.price_unit * cim.number_of_items) asset_cost
14388: FROM okc_subclass_top_line stl,
14389: okc_line_styles_b lse2,
14390: okc_line_styles_b lse1,
14391: okc_k_items_v cim,
14392: okc_k_lines_v cle
14393: WHERE cle.cle_id = p_line_id
14394: AND cle.dnz_chr_id = p_dnz_chr_id
14405: CURSOR c_addon(p_line_id NUMBER,
14406: p_dnz_chr_id NUMBER) IS
14407: SELECT SUM(cle.price_unit* cim.number_of_items) add_on
14408: FROM okc_subclass_top_line stl,
14409: okc_line_styles_b lse3,
14410: okc_line_styles_b lse2,
14411: okc_line_styles_b lse1,
14412: okc_k_items_v cim,
14413: okc_k_lines_b cle
14406: p_dnz_chr_id NUMBER) IS
14407: SELECT SUM(cle.price_unit* cim.number_of_items) add_on
14408: FROM okc_subclass_top_line stl,
14409: okc_line_styles_b lse3,
14410: okc_line_styles_b lse2,
14411: okc_line_styles_b lse1,
14412: okc_k_items_v cim,
14413: okc_k_lines_b cle
14414: WHERE cle.dnz_chr_id = p_dnz_chr_id
14407: SELECT SUM(cle.price_unit* cim.number_of_items) add_on
14408: FROM okc_subclass_top_line stl,
14409: okc_line_styles_b lse3,
14410: okc_line_styles_b lse2,
14411: okc_line_styles_b lse1,
14412: okc_k_items_v cim,
14413: okc_k_lines_b cle
14414: WHERE cle.dnz_chr_id = p_dnz_chr_id
14415: AND cle.dnz_chr_id = cim.dnz_chr_id
14423: AND lse3.id = stl.lse_id
14424: AND stl.scs_code IN ('LEASE','LOAN')
14425: AND exists (SELECT 1
14426: FROM okc_subclass_top_line stlx,
14427: okc_line_styles_b lse2x,
14428: okc_line_styles_b lse1x,
14429: okc_k_lines_b clex
14430: WHERE clex.cle_id = p_line_id
14431: AND clex.dnz_chr_id = p_dnz_chr_id
14424: AND stl.scs_code IN ('LEASE','LOAN')
14425: AND exists (SELECT 1
14426: FROM okc_subclass_top_line stlx,
14427: okc_line_styles_b lse2x,
14428: okc_line_styles_b lse1x,
14429: okc_k_lines_b clex
14430: WHERE clex.cle_id = p_line_id
14431: AND clex.dnz_chr_id = p_dnz_chr_id
14432: AND clex.lse_id = lse1x.id
14440: --Cursor for Capitalized Fee
14441: CURSOR c_cap_fee(p_line_id VARCHAR2,
14442: p_dnz_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
14443: SELECT SUM(kle_cov.capital_amount) Cap_fee
14444: FROM okc_line_styles_b lseb,
14445: okc_k_items cim,
14446: okl_k_lines kle_cov,
14447: okc_k_lines_b cleb_cov,
14448: okc_statuses_b stsb
14464: SELECT NVL(kle.capital_reduction,0) capital_reduction,
14465: NVL(kle.tradein_amount,0) tradein_amount,
14466: NVL(kle.capital_reduction_percent,0) capital_reduction_percent,
14467: kle.capitalize_down_payment_yn capitalize_down_payment_yn
14468: FROM okc_line_styles_b ls,
14469: okl_k_lines_full_v kle,
14470: okc_statuses_b sts
14471: WHERE kle.dnz_chr_id = p_dnz_chr_id
14472: AND kle.id = p_line_id
14690: CURSOR c_addon_cost_csr (p_contract_id IN NUMBER,
14691: p_contract_line_id IN NUMBER) IS
14692: SELECT SUM(cle.price_unit* cim.number_of_items) add_on_cost
14693: FROM okc_subclass_top_line stl,
14694: okc_line_styles_b lse3,
14695: okc_line_styles_b lse2,
14696: okc_line_styles_b lse1,
14697: okc_k_items_v cim,
14698: okc_k_lines_b cle
14691: p_contract_line_id IN NUMBER) IS
14692: SELECT SUM(cle.price_unit* cim.number_of_items) add_on_cost
14693: FROM okc_subclass_top_line stl,
14694: okc_line_styles_b lse3,
14695: okc_line_styles_b lse2,
14696: okc_line_styles_b lse1,
14697: okc_k_items_v cim,
14698: okc_k_lines_b cle
14699: WHERE cle.dnz_chr_id = p_contract_id
14692: SELECT SUM(cle.price_unit* cim.number_of_items) add_on_cost
14693: FROM okc_subclass_top_line stl,
14694: okc_line_styles_b lse3,
14695: okc_line_styles_b lse2,
14696: okc_line_styles_b lse1,
14697: okc_k_items_v cim,
14698: okc_k_lines_b cle
14699: WHERE cle.dnz_chr_id = p_contract_id
14700: AND cle.dnz_chr_id = cim.dnz_chr_id
14708: AND lse3.id = stl.lse_id
14709: AND stl.scs_code IN ('LEASE','LOAN')
14710: AND exists (SELECT 1
14711: FROM okc_subclass_top_line stlx,
14712: okc_line_styles_b lse2x,
14713: okc_line_styles_b lse1x,
14714: okc_k_lines_b clex
14715: WHERE clex.cle_id = p_contract_line_id -- lse_id = 33>
14716: AND clex.dnz_chr_id = p_contract_id
14709: AND stl.scs_code IN ('LEASE','LOAN')
14710: AND exists (SELECT 1
14711: FROM okc_subclass_top_line stlx,
14712: okc_line_styles_b lse2x,
14713: okc_line_styles_b lse1x,
14714: okc_k_lines_b clex
14715: WHERE clex.cle_id = p_contract_line_id -- lse_id = 33>
14716: AND clex.dnz_chr_id = p_contract_id
14717: AND clex.lse_id = lse1x.id
14812: SELECT NVL(SUM(sub_kle.amount),0)
14813: FROM okl_subsidies_b subb,
14814: okl_k_lines sub_kle,
14815: okc_k_lines_b sub_cle,
14816: okc_line_styles_b sub_lse
14817: WHERE subb.id = sub_kle.subsidy_id
14818: AND subb.accounting_method_code = NVL(UPPER(p_accounting_method),subb.accounting_method_code)
14819: AND sub_kle.id = sub_cle.id
14820: AND sub_cle.lse_id = sub_lse.id
14932: SELECT NVL(SUM(sub_kle.subsidy_override_amount),0)
14933: FROM okl_subsidies_b subb,
14934: okl_k_lines sub_kle,
14935: okc_k_lines_b sub_cle,
14936: okc_line_styles_b sub_lse
14937: WHERE subb.id = sub_kle.subsidy_id
14938: AND subb.accounting_method_code = NVL(UPPER(p_accounting_method),subb.accounting_method_code)
14939: AND sub_kle.id = sub_cle.id
14940: AND sub_cle.lse_id = sub_lse.id
15036: l_Asset_Fin_fees_amt NUMBER := 0;
15037:
15038: CURSOR l_fee_csr( c_chr_id IN NUMBER, c_fin_asset_line_id IN NUMBER) IS
15039: SELECT sum(kle_cov.amount) asset_fin_fee_amt
15040: FROM OKC_LINE_STYLES_B LSEB,
15041: OKC_K_ITEMS CIM,
15042: OKL_K_LINES KLE_COV,
15043: okl_k_lines fee_line,
15044: OKC_K_LINES_B CLEB_COV,
15058:
15059:
15060: CURSOR l_fee_csr_incl_terminated( c_chr_id IN NUMBER, c_fin_asset_line_id IN NUMBER) IS
15061: SELECT sum(kle_cov.amount) asset_fin_fee_amt
15062: FROM OKC_LINE_STYLES_B LSEB,
15063: OKC_K_ITEMS CIM,
15064: OKL_K_LINES KLE_COV,
15065: okl_k_lines fee_line,
15066: OKC_K_LINES_B CLEB_COV,
15160: l_Asset_Roll_fees_amt NUMBER := 0;
15161:
15162: CURSOR l_fee_csr( c_chr_id IN NUMBER, c_fin_asset_line_id IN NUMBER) IS
15163: SELECT sum(kle_cov.amount) asset_roll_fee_amt
15164: FROM OKC_LINE_STYLES_B LSEB,
15165: OKC_K_ITEMS CIM,
15166: OKL_K_LINES KLE_COV,
15167: okl_k_lines fee_line,
15168: OKC_K_LINES_B CLEB_COV,
15182:
15183:
15184: CURSOR l_fee_csr_incl_terminated( c_chr_id IN NUMBER, c_fin_asset_line_id IN NUMBER) IS
15185: SELECT sum(kle_cov.amount) asset_roll_fee_amt
15186: FROM OKC_LINE_STYLES_B LSEB,
15187: OKC_K_ITEMS CIM,
15188: OKL_K_LINES KLE_COV,
15189: okl_k_lines fee_line,
15190: OKC_K_LINES_B CLEB_COV,