[Home] [Help]
938: l_capred_incl_terminated BOOLEAN := FALSE;
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
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
997: AND cle.id = cim.cle_id
998: AND cle.lse_id = lse1.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:
1015: AND clex.lse_id = lse1x.id
1032: --cursor to get expected asset value for contract
1033: cursor l_chr_expcost_trmn_csr(p_chr_id in number) is
1034: SELECT SUM(kle.expected_asset_cost) expected_asset_cost
1035: FROM OKL_K_LINES kle,
1036: OKC_K_LINES_B cleb
1037: WHERE kle.id = cleb.id
1038: AND cleb.dnz_chr_id = p_chr_id
1039: AND cleb.lse_id = 33
1040: AND cleb.sts_code NOT IN ( 'ABANDONED', 'EXPIRED', 'CANCELLED', 'HOLD');
1041:
1042: cursor l_chr_expcost_csr(p_chr_id in number) is
1043: SELECT SUM(kle.expected_asset_cost) expected_asset_cost
1044: FROM OKL_K_LINES kle,
1045: OKC_K_LINES_B cleb
1046: WHERE kle.id = cleb.id
1047: AND cleb.dnz_chr_id = p_chr_id
1048: AND cleb.lse_id = 33
1049: AND cleb.sts_code NOT IN ( 'ABANDONED', 'TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
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
1901: WHERE LSEB.ID = CLEB_COV.LSE_ID
1902: AND LSEB.lty_code = 'LINK_FEE_ASSET'
1903: AND CIM.jtot_object1_code = 'OKX_COVASST'
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
1918: WHERE LSEB.ID = CLEB_COV.LSE_ID
1919: AND LSEB.lty_code = 'LINK_FEE_ASSET'
1920: AND CIM.jtot_object1_code = 'OKX_COVASST'
2870:
2871: CURSOR adv_arr_csr IS
2872: SELECT
2873: rulb2.RULE_INFORMATION10 arrears_yn
2874: FROM okc_k_lines_b cleb,
2875: okc_rule_groups_b rgpb,
2876: okc_rules_b rulb,
2877: okc_rules_b rulb2,
2878: okl_strm_type_b styb
4207: -- from _uv
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,
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,
4217: OKC_K_ITEMS ITE,
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,
4219: HZ_PARTY_SITES PSI,
4241: AND psu.site_use_type = 'INSTALL_AT'
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,
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,
4251: OKC_K_ITEMS ITE,
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,
4253: --HZ_PARTY_SITES PSI,
5775: --Cursor to get the parent line id for a contract
5776: CURSOR parent_line_id_csr (p_contract_id NUMBER)
5777: IS
5778: SELECT line.id parent_line_id
5779: FROM okc_k_lines_b line
5780: ,okc_line_styles_v lse
5781: WHERE line.lse_id=lse.id
5782: AND lse.lty_code= 'FREE_FORM1'
5783: AND line.sts_code <> 'ABANDONED'
6027: */
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
6087: */
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
6296: CURSOR l_sub_csr(c_contract_id IN NUMBER,c_accounting_method IN VARCHAR2) IS
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
6405: --Cursor to get the parent line id for a contract
6406: CURSOR parent_line_id_csr (p_contract_id NUMBER)
6407: IS
6408: SELECT line.id parent_line_id
6409: FROM okc_k_lines_b line
6410: ,okc_line_styles_v lse
6411: WHERE line.lse_id=lse.id
6412: AND lse.lty_code= 'FREE_FORM1'
6413: AND line.sts_code <> 'ABANDONED'
7619: CURSOR l_okl_percent_stake_csr(p_sty_subclass IN VARCHAR2)
7620: IS
7621: SELECT DISTINCT kleb.percent_stake,clet.id
7622: FROM okl_k_lines kleb,
7623: okc_k_lines_b clet,
7624: okc_k_lines_b cles
7625: WHERE kleb.id = cles.id
7626: AND cles.cle_id = clet.id
7627: AND clet.dnz_chr_id = p_chr_id
7620: IS
7621: SELECT DISTINCT kleb.percent_stake,clet.id
7622: FROM okl_k_lines kleb,
7623: okc_k_lines_b clet,
7624: okc_k_lines_b cles
7625: WHERE kleb.id = cles.id
7626: AND cles.cle_id = clet.id
7627: AND clet.dnz_chr_id = p_chr_id
7628: --AND kleb.sty_id = styb.id
7937: SELECT
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
7952: SELECT
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
8041: CURSOR l_okl_percent_stake_csr(p_sty_subclass IN VARCHAR2)
8042: IS
8043: SELECT DISTINCT kleb.percent_stake,clet.id
8044: FROM okl_k_lines kleb,
8045: okc_k_lines_b clet,
8046: okc_k_lines_b cles
8047: WHERE kleb.id = cles.id
8048: AND cles.cle_id = clet.id
8049: AND clet.dnz_chr_id = p_contract_id
8042: IS
8043: SELECT DISTINCT kleb.percent_stake,clet.id
8044: FROM okl_k_lines kleb,
8045: okc_k_lines_b clet,
8046: okc_k_lines_b cles
8047: WHERE kleb.id = cles.id
8048: AND cles.cle_id = clet.id
8049: AND clet.dnz_chr_id = p_contract_id
8050: AND kleb.stream_type_subclass = p_sty_subclass;
8236: SELECT
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
8444: --cursor to get line_sts
8445: CURSOR l_line_sts_csr (p_cle_id IN NUMBER) IS
8446: SELECT stsb.ste_code
8447: FROM okc_statuses_b stsb,
8448: okc_k_lines_b cleb
8449: WHERE stsb.code = cleb.sts_code
8450: AND cleb.id = p_cle_id;
8451:
8452: l_line_sts_rec l_line_sts_csr%ROWTYPE;
8453:
8454: --cursor to get financial asset lines on a contract (without terminated lines)
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
8465:
8466: --cursor to get financial asset lines on a contract (with terminated lines)
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
8476: AND stsb.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED');
8477:
8478: l_cle_id okc_k_lines_b.ID%TYPE;
8479:
8480:
8481: BEGIN
8482:
9706: cp_date DATE ) IS -- rmunjulu EDAT
9707: SELECT SUM(ste.amount) amount
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
9738: cp_date DATE ) IS -- rmunjulu EDAT
9739: SELECT SUM(ste.amount) amount
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
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
9749: AND stm.active_yn = 'Y'
9750: AND stm.say_code = 'CURR'
9751: AND ste.stm_id = stm.id
9772: cp_date DATE ) IS -- rmunjulu EDAT
9773: SELECT SUM(ste.amount) amount
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
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
9783: WHERE stm.khr_id = cp_chr_id
9784: AND stm.active_yn = 'Y'
9785: AND stm.say_code = 'CURR'
9962:
9963: l_amount NUMBER;
9964:
9965: /* case not available
9966: CURSOR c_subsidy_amount(p_cle_id okc_k_lines_b.id%TYPE) IS
9967: select (case
9968: when ROUND(MONTHS_BETWEEN(chr.end_date, cle.start_date)) <= 12 then NVL(kle.oec,0) * .2
9969: else NVL(kle.oec,0) * .1
9970: end) subsidy_amount
9967: select (case
9968: when ROUND(MONTHS_BETWEEN(chr.end_date, cle.start_date)) <= 12 then NVL(kle.oec,0) * .2
9969: else NVL(kle.oec,0) * .1
9970: end) subsidy_amount
9971: from okc_k_lines_b cle,
9972: okl_k_lines kle,
9973: okc_k_headers_b chr
9974: where chr.id = cle.dnz_chr_id
9975: and kle.id = cle.id
9975: and kle.id = cle.id
9976: and cle.id = p_cle_id -- FREE_FORM1 (FIN)
9977: ;
9978: */
9979: CURSOR c_subsidy_amount(p_cle_id okc_k_lines_b.id%TYPE) IS
9980: SELECT ROUND(MONTHS_BETWEEN(CHR.end_date, cle.start_date)) months,
9981: NVL(kle.oec,0) oec
9982: FROM okc_k_lines_b cle,
9983: okl_k_lines kle,
9978: */
9979: CURSOR c_subsidy_amount(p_cle_id okc_k_lines_b.id%TYPE) IS
9980: SELECT ROUND(MONTHS_BETWEEN(CHR.end_date, cle.start_date)) months,
9981: NVL(kle.oec,0) oec
9982: FROM okc_k_lines_b cle,
9983: okl_k_lines kle,
9984: okc_k_headers_b CHR
9985: WHERE CHR.id = cle.dnz_chr_id
9986: AND kle.id = cle.id
10038: l_amount NUMBER := 0;
10039: l_tot_amount NUMBER := 0;
10040: l_return_status VARCHAR2(1);
10041:
10042: CURSOR c_subsidy_lines(p_cle_id okc_k_lines_b.id%TYPE) IS
10043: SELECT sub.accounting_method_code,
10044: top_cle.date_terminated,
10045: --TO_NUMBER(sgn.value) sty_id
10046: sub_kle.sty_id sty_id, /* Bug 6353756 */
10047: top_cle.dnz_chr_id chr_id
10048: FROM --okl_sgn_translations sgn,
10049: okl_subsidies_b sub,
10050: okl_k_lines sub_kle,
10051: okc_k_lines_b sub_cle,
10052: okc_k_lines_b top_cle
10053: WHERE --sgn.jtot_object1_code = 'OKL_STRMTYP'
10054: --AND sgn.object1_id1 = TO_CHAR(sub_kle.sty_id) AND
10055: sub.id = sub_kle.subsidy_id
10048: FROM --okl_sgn_translations sgn,
10049: okl_subsidies_b sub,
10050: okl_k_lines sub_kle,
10051: okc_k_lines_b sub_cle,
10052: okc_k_lines_b top_cle
10053: WHERE --sgn.jtot_object1_code = 'OKL_STRMTYP'
10054: --AND sgn.object1_id1 = TO_CHAR(sub_kle.sty_id) AND
10055: sub.id = sub_kle.subsidy_id
10056: AND sub_cle.cle_id = top_cle.id
10057: AND sub_kle.id = sub_cle.id
10058: AND sub_cle.cle_id = p_cle_id -- FREE_FORM1
10059: ;
10060:
10061: CURSOR c_refund_subsidy(p_cle_id okc_k_lines_b.id%TYPE,
10062: p_date_terminated okc_k_lines_b.date_terminated%TYPE,
10063: p_sty_id okl_strm_type_b.id%TYPE) IS
10064: SELECT NVL(SUM(selb.amount),0)
10065: FROM
10058: AND sub_cle.cle_id = p_cle_id -- FREE_FORM1
10059: ;
10060:
10061: CURSOR c_refund_subsidy(p_cle_id okc_k_lines_b.id%TYPE,
10062: p_date_terminated okc_k_lines_b.date_terminated%TYPE,
10063: p_sty_id okl_strm_type_b.id%TYPE) IS
10064: SELECT NVL(SUM(selb.amount),0)
10065: FROM
10066: okl_streams stmb,
10276: SELECT
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
10335: SELECT
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
11233: x_msg_data VARCHAR2(256);
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
11245: AND stsb.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED');
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
11351: x_msg_data VARCHAR2(256);
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
11363: AND stsb.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED');
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
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
11483: AND LSEB.lty_code = 'FREE_FORM1'
11484: AND KLE.id = CLEB.ID
11493: SELECT
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
11789: --currency code cursor added to derive currency code for the particular line
11790: --which will be used for rounding amount
11791: CURSOR currency_code_csr (p_kle_id NUMBER ) IS
11792: SELECT currency_code
11793: FROM okc_k_lines_b
11794: WHERE id = p_kle_id;
11795: --declaring proration factor , currency code and rounding rule variables
11796: l_proration_factor NUMBER;
11797: l_currency_code okc_k_lines_b.currency_code%TYPE;
11793: FROM okc_k_lines_b
11794: WHERE id = p_kle_id;
11795: --declaring proration factor , currency code and rounding rule variables
11796: l_proration_factor NUMBER;
11797: l_currency_code okc_k_lines_b.currency_code%TYPE;
11798: l_parent_strm_amt NUMBER;
11799: l_parent_strm_rounded_amt NUMBER;
11800: l_parent_strm_rounded_tot_amt NUMBER;
11801: --declaring other local variables
12106: --currency code cursor added to derive currency code for the particular line
12107: --which will be used for rounding amount
12108: CURSOR currency_code_csr (p_kle_id NUMBER ) IS
12109: SELECT currency_code
12110: FROM okc_k_lines_b
12111: WHERE id = p_kle_id;
12112: --declaring proration factor , currency code and rounding rule variables
12113: l_proration_factor NUMBER;
12114: l_currency_code okc_k_lines_b.currency_code%TYPE;
12110: FROM okc_k_lines_b
12111: WHERE id = p_kle_id;
12112: --declaring proration factor , currency code and rounding rule variables
12113: l_proration_factor NUMBER;
12114: l_currency_code okc_k_lines_b.currency_code%TYPE;
12115: l_parent_strm_amt NUMBER;
12116: l_parent_strm_rounded_amt NUMBER;
12117: l_parent_strm_rounded_tot_amt NUMBER;
12118: --declaring other local variables
12578:
12579: CURSOR c_fin_fees IS
12580: SELECT cle.id
12581: FROM
12582: okc_k_lines_b cle,
12583: okl_k_lines kle,
12584: okc_k_headers_b chr
12585: WHERE
12586: chr.id = p_contract_id
12626:
12627: CURSOR c_fin_fees IS
12628: SELECT cle.id
12629: FROM
12630: okc_k_lines_b cle,
12631: okl_k_lines kle,
12632: okc_k_headers_b chr
12633: WHERE
12634: chr.id = p_contract_id
12689: SELECT nvl(fab.cost,0) current_cost
12690: FROM fa_books fab,
12691: fa_book_controls fbc,
12692: okc_k_items_v itm,
12693: okc_k_lines_b kle,
12694: okc_line_styles_v lse
12695: WHERE fbc.book_class = 'CORPORATE'
12696: AND fab.book_type_code = fbc.book_type_code
12697: AND fab.asset_id = itm.object1_id1
13330: -- sjalasut, Rebook Change Control Enhancement START
13331:
13332: -- function that returns the sum of unbilled RENT for all active assets on the rebook copy of the contract
13333: FUNCTION cont_rbk_unbilled_receivables(p_contract_id okc_k_headers_b.id%TYPE
13334: ,p_contract_line_id okc_k_lines_b.id%TYPE DEFAULT OKL_API.G_MISS_NUM) RETURN NUMBER IS
13335:
13336: -- get all unbilled receivables for all ACTIVE assets
13337: CURSOR cle_rents_csr(cp_contract_id okc_k_headers_b.id%TYPE) IS
13338: SELECT NVL(SUM(sele.amount),0)
13387: END cont_rbk_unbilled_receivables;
13388:
13389: -- function that returns the sum of pre-tax income that was not accrued for all active assets on the rebook copy of the contract
13390: FUNCTION cont_rbk_unearned_income(p_contract_id okc_k_headers_b.id%TYPE
13391: ,p_contract_line_id okc_k_lines_b.id%TYPE DEFAULT OKL_API.G_MISS_NUM) RETURN NUMBER IS
13392:
13393: CURSOR c_pre_tax_csr(cp_contract_id okc_k_headers_b.id%TYPE) IS
13394: SELECT NVL(SUM(sele.amount),0)
13395: FROM okl_strm_elements sele
13395: FROM okl_strm_elements sele
13396: --,okl_streams str MGAAP 7263041
13397: ,okl_streams_rep_v str
13398: ,okl_strm_type_v sty
13399: ,okc_k_lines_b line
13400: ,okc_line_styles_b style
13401: ,okc_statuses_b sts
13402: WHERE sele.stm_id = str.id
13403: AND str.sty_id = sty.id
13436:
13437: -- returns sum of rent not billed for all terminated assets
13438: -- do not send p_contract_line_id as okl_api.g_miss_num
13439: FUNCTION cont_tmt_unbilled_receivables(p_contract_id okc_k_headers_b.id%TYPE
13440: ,p_contract_line_id okc_k_lines_b.id%TYPE DEFAULT OKL_API.G_MISS_NUM) RETURN NUMBER IS
13441: -- get all unbilled receivables for all TERMINATED assets
13442: CURSOR cle_rents_csr(cp_contract_id okc_k_headers_b.id%TYPE
13443: ,cp_contract_line_id okc_k_lines_b.id%TYPE) IS
13444: SELECT NVL(SUM(sele.amount),0)
13439: FUNCTION cont_tmt_unbilled_receivables(p_contract_id okc_k_headers_b.id%TYPE
13440: ,p_contract_line_id okc_k_lines_b.id%TYPE DEFAULT OKL_API.G_MISS_NUM) RETURN NUMBER IS
13441: -- get all unbilled receivables for all TERMINATED assets
13442: CURSOR cle_rents_csr(cp_contract_id okc_k_headers_b.id%TYPE
13443: ,cp_contract_line_id okc_k_lines_b.id%TYPE) IS
13444: SELECT NVL(SUM(sele.amount),0)
13445: FROM okl_strm_elements sele
13446: ,okl_streams str
13447: ,okl_strm_type_v sty
13495:
13496: -- returns sum of pre tax income not accrued for all terminated assets
13497: -- do not send p_contract_line_id as okl_api.g_miss_num
13498: FUNCTION cont_tmt_unearned_income(p_contract_id okc_k_headers_b.id%TYPE
13499: ,p_contract_line_id okc_k_lines_b.id%TYPE DEFAULT OKL_API.G_MISS_NUM) RETURN NUMBER IS
13500: CURSOR c_pre_tax_csr(cp_contract_id okc_k_headers_b.id%TYPE ,cp_contract_line_id okc_k_lines_b.id%TYPE) IS
13501: SELECT NVL(SUM(sele.amount),0)
13502: FROM okl_strm_elements sele
13503: --,okl_streams str MGAAP 7263041
13496: -- returns sum of pre tax income not accrued for all terminated assets
13497: -- do not send p_contract_line_id as okl_api.g_miss_num
13498: FUNCTION cont_tmt_unearned_income(p_contract_id okc_k_headers_b.id%TYPE
13499: ,p_contract_line_id okc_k_lines_b.id%TYPE DEFAULT OKL_API.G_MISS_NUM) RETURN NUMBER IS
13500: CURSOR c_pre_tax_csr(cp_contract_id okc_k_headers_b.id%TYPE ,cp_contract_line_id okc_k_lines_b.id%TYPE) IS
13501: SELECT NVL(SUM(sele.amount),0)
13502: FROM okl_strm_elements sele
13503: --,okl_streams str MGAAP 7263041
13504: ,okl_streams_rep_v str
13502: FROM okl_strm_elements sele
13503: --,okl_streams str MGAAP 7263041
13504: ,okl_streams_rep_v str
13505: ,okl_strm_type_v sty
13506: ,okc_k_lines_b line
13507: ,okc_line_styles_b style
13508: -- ,okc_statuses_b sts
13509: WHERE sele.stm_id = str.id
13510: AND str.sty_id = sty.id
14375:
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:
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
14416: AND cle.id = cim.cle_id
14417: AND cle.lse_id = lse1.id
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
14433: AND lse1x.lty_code = 'ITEM'
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
14449: WHERE lseb.id = cleb_cov.lse_id
14450: AND lseb.lty_code = 'LINK_FEE_ASSET'
14451: AND cim.jtot_object1_code = 'OKX_COVASST'
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
14701: AND cle.id = cim.cle_id
14702: AND cle.lse_id = lse1.id
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
14718: AND lse1x.lty_code = 'ITEM' -- G_MODEL_LINE_LTY_CODE
14811: p_accounting_method IN VARCHAR2) IS
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
14931: p_accounting_method IN VARCHAR2) IS
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
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,
15045: OKC_STATUSES_B STSB
15046: WHERE LSEB.ID = CLEB_COV.LSE_ID
15047: AND LSEB.lty_code = 'LINK_FEE_ASSET'
15048: AND CIM.jtot_object1_code = 'OKX_COVASST'
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,
15067: OKC_STATUSES_B STSB
15068: WHERE LSEB.ID = CLEB_COV.LSE_ID
15069: AND LSEB.lty_code = 'LINK_FEE_ASSET'
15070: AND CIM.jtot_object1_code = 'OKX_COVASST'
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,
15169: OKC_STATUSES_B STSB
15170: WHERE LSEB.ID = CLEB_COV.LSE_ID
15171: AND LSEB.lty_code = 'LINK_FEE_ASSET'
15172: AND CIM.jtot_object1_code = 'OKX_COVASST'
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,
15191: OKC_STATUSES_B STSB
15192: WHERE LSEB.ID = CLEB_COV.LSE_ID
15193: AND LSEB.lty_code = 'LINK_FEE_ASSET'
15194: AND CIM.jtot_object1_code = 'OKX_COVASST'