[Home] [Help]
682: CURSOR residual_csr( chrId NUMBER, lineId NUMBER ) IS
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
700: CURSOR residual_csr_incl_terminated( chrId NUMBER, lineId NUMBER ) IS
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
904: l_lne_res_value NUMBER := 0.0;
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'
917: --Bug# 15992711 , Start
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'
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
1087: FROM okc_k_lines_b cle,
1088: okc_line_styles_b lse
1089: WHERE cle.id = p_cle_id
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
1112: CURSOR c_model_oec_calc(p_top_cle_id OKC_K_LINES_V.CLE_ID%TYPE,
1113: p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
1114: SELECT SUM(cle.price_unit * cim.number_of_items) oec
1115: FROM okc_subclass_top_line stl,
1116: okc_line_styles_b lse2,
1117: okc_line_styles_b lse1,
1118: okc_k_items_v cim,
1119: okc_k_lines_v cle
1120: WHERE cle.cle_id = p_top_cle_id
1113: p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
1114: SELECT SUM(cle.price_unit * cim.number_of_items) oec
1115: FROM okc_subclass_top_line stl,
1116: okc_line_styles_b lse2,
1117: okc_line_styles_b lse1,
1118: okc_k_items_v cim,
1119: okc_k_lines_v cle
1120: WHERE cle.cle_id = p_top_cle_id
1121: AND cle.dnz_chr_id = p_dnz_chr_id
1131: CURSOR c_addon_oec_calc(p_top_cle_id OKC_K_LINES_V.CLE_ID%TYPE,
1132: p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
1133: SELECT SUM(cle.price_unit* cim.number_of_items) oec
1134: FROM okc_subclass_top_line stl,
1135: okc_line_styles_b lse3,
1136: okc_line_styles_b lse2,
1137: okc_line_styles_b lse1,
1138: okc_k_items_v cim,
1139: okc_k_lines_b cle
1132: p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
1133: SELECT SUM(cle.price_unit* cim.number_of_items) oec
1134: FROM okc_subclass_top_line stl,
1135: okc_line_styles_b lse3,
1136: okc_line_styles_b lse2,
1137: okc_line_styles_b lse1,
1138: okc_k_items_v cim,
1139: okc_k_lines_b cle
1140: WHERE cle.dnz_chr_id = p_dnz_chr_id
1133: SELECT SUM(cle.price_unit* cim.number_of_items) oec
1134: FROM okc_subclass_top_line stl,
1135: okc_line_styles_b lse3,
1136: okc_line_styles_b lse2,
1137: okc_line_styles_b lse1,
1138: okc_k_items_v cim,
1139: okc_k_lines_b cle
1140: WHERE cle.dnz_chr_id = p_dnz_chr_id
1141: AND cle.dnz_chr_id = cim.dnz_chr_id
1150: AND stl.scs_code IN (G_LEASE_SCS_CODE,G_LOAN_SCS_CODE)
1151: --AND cle.cle_id IN (SELECT cle.id
1152: AND exists (SELECT 1 --cle.id
1153: FROM okc_subclass_top_line stlx,
1154: okc_line_styles_b lse2x,
1155: okc_line_styles_b lse1x,
1156: okc_k_lines_b clex
1157: WHERE clex.cle_id = p_top_cle_id
1158: AND clex.dnz_chr_id = p_dnz_chr_id
1151: --AND cle.cle_id IN (SELECT cle.id
1152: AND exists (SELECT 1 --cle.id
1153: FROM okc_subclass_top_line stlx,
1154: okc_line_styles_b lse2x,
1155: okc_line_styles_b lse1x,
1156: okc_k_lines_b clex
1157: WHERE clex.cle_id = p_top_cle_id
1158: AND clex.dnz_chr_id = p_dnz_chr_id
1159:
1232: p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE,
1233: p_line_type VARCHAR2) IS
1234: SELECT SUM(cle.price_unit * cim.number_of_items) oec
1235: FROM okc_subclass_top_line stl,
1236: okc_line_styles_b lse2,
1237: okc_line_styles_b lse1,
1238: okc_k_items_v cim,
1239: okc_k_lines_v cle
1240: WHERE cle.cle_id = p_top_cle_id
1233: p_line_type VARCHAR2) IS
1234: SELECT SUM(cle.price_unit * cim.number_of_items) oec
1235: FROM okc_subclass_top_line stl,
1236: okc_line_styles_b lse2,
1237: okc_line_styles_b lse1,
1238: okc_k_items_v cim,
1239: okc_k_lines_v cle
1240: WHERE cle.cle_id = p_top_cle_id
1241: AND cle.dnz_chr_id = p_dnz_chr_id
1674: IF l_line_type IN ('NEW','RELEASE') THEN
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'
1687:
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'
1712: ELSIF l_discount_incl_terminated THEN
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'
1723: AND sts.ste_code NOT IN ('EXPIRED', 'CANCELLED', 'HOLD');
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'
1780: CURSOR trdinval_csr( chrID NUMBER, lineID NUMBER) IS
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'
1794: CURSOR trdinval_csr_incl_terminated( chrID NUMBER, lineID NUMBER) IS
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'
1903: l_capred_value NUMBER := 0;
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'
1918:
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'
1934: --Bug# 15992711 , Start
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'
1951:
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'
2109: SELECT NVL(kle.capital_reduction,0) capital_reduction,
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'
2153: l_capred_value := this_row.capital_reduction;
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'
2174: END IF;
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'
2198: END IF;
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'
2219: END IF;
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'
2283: l_fees_value NUMBER := 0;
2284:
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
2300: AND STSB.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
2301:
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
2425:
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'
2436: AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
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'
2449: --Bug# 15992711 , Start
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'
2463:
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'
2611:
2612:
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
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
2635: WHERE cim.cle_id = kle.id
2636: AND ls.id = kle.lse_id
4792: /*
4793: CURSOR l_item_loc_csr (cp_fin_asset_cle_id NUMBER) IS
4794: SELECT DISTINCT LOA.loc_id location_id, LOC.postal_code -- 3682465
4795: FROM OKC_K_LINES_B KLE_FA,
4796: OKC_LINE_STYLES_B LSE_FA,
4797: OKC_K_LINES_B KLE_IL,
4798: OKC_LINE_STYLES_B LSE_IL,
4799: OKC_K_LINES_B KLE_IB,
4800: OKC_LINE_STYLES_B LSE_IB,
4794: SELECT DISTINCT LOA.loc_id location_id, LOC.postal_code -- 3682465
4795: FROM OKC_K_LINES_B KLE_FA,
4796: OKC_LINE_STYLES_B LSE_FA,
4797: OKC_K_LINES_B KLE_IL,
4798: OKC_LINE_STYLES_B LSE_IL,
4799: OKC_K_LINES_B KLE_IB,
4800: OKC_LINE_STYLES_B LSE_IB,
4801: OKC_K_ITEMS ITE,
4802: CSI_ITEM_INSTANCES CII,
4796: OKC_LINE_STYLES_B LSE_FA,
4797: OKC_K_LINES_B KLE_IL,
4798: OKC_LINE_STYLES_B LSE_IL,
4799: OKC_K_LINES_B KLE_IB,
4800: OKC_LINE_STYLES_B LSE_IB,
4801: OKC_K_ITEMS ITE,
4802: CSI_ITEM_INSTANCES CII,
4803: HZ_PARTY_SITES PSI,
4804: HZ_LOCATIONS LOC,
4826: AND loc.location_id = loa.location_id
4827: UNION
4828: SELECT DISTINCT LOA.loc_id location_id, LOC.postal_code -- 3682465
4829: FROM OKC_K_LINES_B KLE_FA,
4830: OKC_LINE_STYLES_B LSE_FA,
4831: OKC_K_LINES_B KLE_IL,
4832: OKC_LINE_STYLES_B LSE_IL,
4833: OKC_K_LINES_B KLE_IB,
4834: OKC_LINE_STYLES_B LSE_IB,
4828: SELECT DISTINCT LOA.loc_id location_id, LOC.postal_code -- 3682465
4829: FROM OKC_K_LINES_B KLE_FA,
4830: OKC_LINE_STYLES_B LSE_FA,
4831: OKC_K_LINES_B KLE_IL,
4832: OKC_LINE_STYLES_B LSE_IL,
4833: OKC_K_LINES_B KLE_IB,
4834: OKC_LINE_STYLES_B LSE_IB,
4835: OKC_K_ITEMS ITE,
4836: CSI_ITEM_INSTANCES CII,
4830: OKC_LINE_STYLES_B LSE_FA,
4831: OKC_K_LINES_B KLE_IL,
4832: OKC_LINE_STYLES_B LSE_IL,
4833: OKC_K_LINES_B KLE_IB,
4834: OKC_LINE_STYLES_B LSE_IB,
4835: OKC_K_ITEMS ITE,
4836: CSI_ITEM_INSTANCES CII,
4837: --HZ_PARTY_SITES PSI,
4838: HZ_LOCATIONS LOC,
6626: okl_bpd_tld_ar_lines_v LSM,
6627: -- OKC_K_HEADERS_B CHR, commenting unused table, ankushar Bug# 5499193
6628: /* 16-Aug-2007 ankushar end changes */
6629: OKC_K_LINES_B CLE,
6630: OKC_LINE_STYLES_B LSE
6631: WHERE
6632: LSM.KLE_ID = p_contract_line_id
6633: AND LSM.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
6634: AND APS.STATUS = 'OP'
6686: okl_bpd_tld_ar_lines_v LSM,
6687: -- OKC_K_HEADERS_B CHR, commenting unused table, ankushar Bug# 5499193
6688: /* 16-Aug-2007 ankushar end changes */
6689: OKC_K_LINES_B CLE,
6690: OKC_LINE_STYLES_B LSE
6691: WHERE
6692: LSM.KLE_ID = p_contract_line_id
6693: AND LSM.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
6694: AND APS.STATUS = 'OP'
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)
6902: AND sub_kle.id = sub_cle.id
6903: AND sub_cle.lse_id = sub_lse.id
7909: l_capz_int NUMBER := 0.0;
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'
7924: -- Bug# 15992711 , Start
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'
8023: l_capz_int NUMBER := 0.0;
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
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
8619: CLEB.LSE_ID = LSEB.ID AND
8620: LSEB.LTY_CODE = 'INVESTMENT' AND
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
8634: CLEB.LSE_ID = LSEB.ID AND
8635: LSEB.LTY_CODE = 'INVESTMENT' AND
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
8918: CLEB.LSE_ID = LSEB.ID AND
8919: LSEB.LTY_CODE = 'FEE' AND
9129: CURSOR l_cleb_csr (p_chr_id IN NUMBER) IS
9130: SELECT cleb.id
9131: FROM okc_k_lines_b cleb,
9132: okc_statuses_b stsb,
9133: okc_line_styles_b lseb
9134: WHERE cleb.chr_id = p_chr_id
9135: AND cleb.lse_id = lseb.id
9136: AND lseb.lty_code = 'FREE_FORM1'
9137: AND cleb.sts_code = stsb.code
9141: CURSOR l_cleb_termn_csr (p_chr_id IN NUMBER) IS
9142: SELECT cleb.id
9143: FROM okc_k_lines_b cleb,
9144: okc_statuses_b stsb,
9145: okc_line_styles_b lseb
9146: WHERE cleb.chr_id = p_chr_id
9147: AND cleb.lse_id = lseb.id
9148: AND lseb.lty_code = 'FREE_FORM1'
9149: AND cleb.sts_code = stsb.code
9154: CURSOR l_cleb_nr_csr (p_chr_id IN NUMBER, p_line_type VARCHAR2) IS
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'
9169: CURSOR l_cleb_nr_termn_csr (p_chr_id IN NUMBER, p_line_type VARCHAR2) IS
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'
10565: FROM okl_streams stm,
10566: okl_strm_type_b sty,
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'
10597: FROM okl_streams stm
10598: ,okl_strm_type_b sty
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
10631: FROM okl_streams stm
10632: ,okl_strm_type_b sty
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
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
11221: KLEB.ID = CLEB.ID AND
11222: CLEB.LSE_ID = LSEB.ID AND
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
11280: KLEB.ID = CLEB.ID AND
11281: CLEB.LSE_ID = LSEB.ID AND
12322:
12323: CURSOR l_finast_csr(p_chr_id IN NUMBER) IS
12324: SELECT fin_cle.id
12325: FROM okc_k_lines_b fin_cle,
12326: okc_line_styles_b lse,
12327: okc_statuses_b stsb
12328: WHERE fin_cle.dnz_chr_id = p_chr_id
12329: AND fin_cle.chr_id = p_chr_id
12330: AND fin_cle.lse_id = lse.id
12334:
12335: CURSOR l_finast_csr_incl_terminated(p_chr_id IN NUMBER) IS
12336: SELECT fin_cle.id
12337: FROM okc_k_lines_b fin_cle,
12338: okc_line_styles_b lse,
12339: okc_statuses_b stsb
12340: WHERE fin_cle.dnz_chr_id = p_chr_id
12341: AND fin_cle.chr_id = p_chr_id
12342: AND fin_cle.lse_id = lse.id
12440:
12441: CURSOR l_finast_csr(p_chr_id IN NUMBER) IS
12442: SELECT fin_cle.id
12443: FROM okc_k_lines_b fin_cle,
12444: okc_line_styles_b lse,
12445: okc_statuses_b stsb
12446: WHERE fin_cle.dnz_chr_id = p_chr_id
12447: AND fin_cle.chr_id = p_chr_id
12448: AND fin_cle.lse_id = lse.id
12452:
12453: CURSOR l_finast_csr_incl_terminated(p_chr_id IN NUMBER) IS
12454: SELECT fin_cle.id
12455: FROM okc_k_lines_b fin_cle,
12456: okc_line_styles_b lse,
12457: okc_statuses_b stsb
12458: WHERE fin_cle.dnz_chr_id = p_chr_id
12459: AND fin_cle.chr_id = p_chr_id
12460: AND fin_cle.lse_id = lse.id
12562: CURSOR l_cap_amnt_csr( ChrId NUMBER) IS
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
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
12589: KLEB.ID = CLEB.ID AND
12590: CLEB.LSE_ID = LSEB.ID AND
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
14578: AND str.sty_id = sty.id
14579: AND sty.stream_type_purpose = 'RENT'
14580: AND str.say_code = 'CURR'
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
14635: AND str.sty_id = sty.id
14636: AND UPPER(sty.stream_type_purpose) = 'LEASE_INCOME' -- pre-tax income has steam type purpose as lease_income
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
14684: AND str.sty_id = sty.id
14685: AND sty.stream_type_purpose = 'RENT'
14686: AND str.say_code = 'CURR'
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
14742: AND str.sty_id = sty.id
14743: AND UPPER(sty.stream_type_purpose) = 'LEASE_INCOME' -- pre-tax income has steam type purpose as lease_income
15608: -- Cursor to get the lty_code
15609: CURSOR get_lty_code(p_line_id NUMBER) IS
15610: SELECT lse.lty_code
15611: FROM okc_k_lines_b cle,
15612: okc_line_styles_b lse
15613: WHERE cle.id = p_line_id
15614: AND cle.lse_id = lse.id;
15615:
15616: -- Cursor to sum up asset cost for given Asset line
15617: CURSOR c_asset_cost(p_line_id NUMBER,
15618: p_dnz_chr_id NUMBER) IS
15619: SELECT SUM(cle.price_unit * cim.number_of_items) asset_cost
15620: FROM okc_subclass_top_line stl,
15621: okc_line_styles_b lse2,
15622: okc_line_styles_b lse1,
15623: okc_k_items_v cim,
15624: okc_k_lines_v cle
15625: WHERE cle.cle_id = p_line_id
15618: p_dnz_chr_id NUMBER) IS
15619: SELECT SUM(cle.price_unit * cim.number_of_items) asset_cost
15620: FROM okc_subclass_top_line stl,
15621: okc_line_styles_b lse2,
15622: okc_line_styles_b lse1,
15623: okc_k_items_v cim,
15624: okc_k_lines_v cle
15625: WHERE cle.cle_id = p_line_id
15626: AND cle.dnz_chr_id = p_dnz_chr_id
15637: CURSOR c_addon(p_line_id NUMBER,
15638: p_dnz_chr_id NUMBER) IS
15639: SELECT SUM(cle.price_unit* cim.number_of_items) add_on
15640: FROM okc_subclass_top_line stl,
15641: okc_line_styles_b lse3,
15642: okc_line_styles_b lse2,
15643: okc_line_styles_b lse1,
15644: okc_k_items_v cim,
15645: okc_k_lines_b cle
15638: p_dnz_chr_id NUMBER) IS
15639: SELECT SUM(cle.price_unit* cim.number_of_items) add_on
15640: FROM okc_subclass_top_line stl,
15641: okc_line_styles_b lse3,
15642: okc_line_styles_b lse2,
15643: okc_line_styles_b lse1,
15644: okc_k_items_v cim,
15645: okc_k_lines_b cle
15646: WHERE cle.dnz_chr_id = p_dnz_chr_id
15639: SELECT SUM(cle.price_unit* cim.number_of_items) add_on
15640: FROM okc_subclass_top_line stl,
15641: okc_line_styles_b lse3,
15642: okc_line_styles_b lse2,
15643: okc_line_styles_b lse1,
15644: okc_k_items_v cim,
15645: okc_k_lines_b cle
15646: WHERE cle.dnz_chr_id = p_dnz_chr_id
15647: AND cle.dnz_chr_id = cim.dnz_chr_id
15655: AND lse3.id = stl.lse_id
15656: AND stl.scs_code IN ('LEASE','LOAN')
15657: AND exists (SELECT 1
15658: FROM okc_subclass_top_line stlx,
15659: okc_line_styles_b lse2x,
15660: okc_line_styles_b lse1x,
15661: okc_k_lines_b clex
15662: WHERE clex.cle_id = p_line_id
15663: AND clex.dnz_chr_id = p_dnz_chr_id
15656: AND stl.scs_code IN ('LEASE','LOAN')
15657: AND exists (SELECT 1
15658: FROM okc_subclass_top_line stlx,
15659: okc_line_styles_b lse2x,
15660: okc_line_styles_b lse1x,
15661: okc_k_lines_b clex
15662: WHERE clex.cle_id = p_line_id
15663: AND clex.dnz_chr_id = p_dnz_chr_id
15664: AND clex.lse_id = lse1x.id
15672: --Cursor for Capitalized Fee
15673: CURSOR c_cap_fee(p_line_id VARCHAR2,
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
15696: SELECT NVL(kle.capital_reduction,0) capital_reduction,
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
15922: CURSOR c_addon_cost_csr (p_contract_id IN NUMBER,
15923: p_contract_line_id IN NUMBER) IS
15924: SELECT SUM(cle.price_unit* cim.number_of_items) add_on_cost
15925: FROM okc_subclass_top_line stl,
15926: okc_line_styles_b lse3,
15927: okc_line_styles_b lse2,
15928: okc_line_styles_b lse1,
15929: okc_k_items_v cim,
15930: okc_k_lines_b cle
15923: p_contract_line_id IN NUMBER) IS
15924: SELECT SUM(cle.price_unit* cim.number_of_items) add_on_cost
15925: FROM okc_subclass_top_line stl,
15926: okc_line_styles_b lse3,
15927: okc_line_styles_b lse2,
15928: okc_line_styles_b lse1,
15929: okc_k_items_v cim,
15930: okc_k_lines_b cle
15931: WHERE cle.dnz_chr_id = p_contract_id
15924: SELECT SUM(cle.price_unit* cim.number_of_items) add_on_cost
15925: FROM okc_subclass_top_line stl,
15926: okc_line_styles_b lse3,
15927: okc_line_styles_b lse2,
15928: okc_line_styles_b lse1,
15929: okc_k_items_v cim,
15930: okc_k_lines_b cle
15931: WHERE cle.dnz_chr_id = p_contract_id
15932: AND cle.dnz_chr_id = cim.dnz_chr_id
15940: AND lse3.id = stl.lse_id
15941: AND stl.scs_code IN ('LEASE','LOAN')
15942: AND exists (SELECT 1
15943: FROM okc_subclass_top_line stlx,
15944: okc_line_styles_b lse2x,
15945: okc_line_styles_b lse1x,
15946: okc_k_lines_b clex
15947: WHERE clex.cle_id = p_contract_line_id -- lse_id = 33>
15948: AND clex.dnz_chr_id = p_contract_id
15941: AND stl.scs_code IN ('LEASE','LOAN')
15942: AND exists (SELECT 1
15943: FROM okc_subclass_top_line stlx,
15944: okc_line_styles_b lse2x,
15945: okc_line_styles_b lse1x,
15946: okc_k_lines_b clex
15947: WHERE clex.cle_id = p_contract_line_id -- lse_id = 33>
15948: AND clex.dnz_chr_id = p_contract_id
15949: AND clex.lse_id = lse1x.id
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)
16051: AND sub_kle.id = sub_cle.id
16052: AND sub_cle.lse_id = sub_lse.id
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)
16171: AND sub_kle.id = sub_cle.id
16172: AND sub_cle.lse_id = sub_lse.id
16268: l_Asset_Fin_fees_amt NUMBER := 0;
16269:
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,
16290:
16291:
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,
16392: l_Asset_Roll_fees_amt NUMBER := 0;
16393:
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,
16414:
16415:
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,