[Home] [Help]
1670: IS
1671: -- cursor to fetch the abosrbed fee line with purpose as RVI
1672: CURSOR rvi_fee_csr IS
1673: SELECT KLE.ID ID
1674: FROM OKL_K_LINES KLE, OKC_K_LINES_B CLE, OKC_LINE_STYLES_B LSE
1675: WHERE CLE.CHR_ID = p_chr_id
1676: AND CLE.DNZ_CHR_ID = p_chr_id
1677: AND CLE.ID = KLE.ID
1678: AND CLE.LSE_ID = LSE.ID
2113: IS
2114: SELECT 'Y'
2115: FROM dual
2116: WHERE EXISTS (SELECT 'Y'
2117: FROM okc_k_lines_b cle,
2118: okc_line_styles_b lse
2119: WHERE cle.dnz_chr_id = p_chr_id
2120: AND cle.lse_id = lse.id
2121: AND cle.sts_code = 'TERMINATED'
2126: IS
2127: SELECT 'Y'
2128: FROM dual
2129: WHERE EXISTS (SELECT 'Y'
2130: FROM okc_k_lines_b cle,
2131: okc_line_styles_b lse
2132: WHERE cle.dnz_chr_id = p_chr_id
2133: AND cle.id = p_cle_id
2134: AND cle.lse_id = lse.id
2338: -- So we look for Fee lines in the Table of Records
2339: CURSOR c_fee_line_csr(p_khr_id OKC_K_HEADERS_B.ID%TYPE)
2340: IS
2341: SELECT cle.id id
2342: FROM okc_k_lines_b cle,
2343: okc_line_styles_b lse
2344: WHERE dnz_chr_id = p_khr_id
2345: AND cle.lse_id = lse.id
2346: AND lse.lty_code = 'FEE';
3038: lx_selv_tbl okl_streams_pvt.selv_tbl_type;
3039: l_stmv_rec okl_streams_pub.stmv_rec_type;
3040: lx_stmv_rec okl_streams_pub.stmv_rec_type;
3041: --srsreeni bug 5869271 start add
3042: l_old_cle_id okc_k_lines_b.ID%TYPE;
3043: l_old_sty_id okl_strm_type_b.ID%TYPE;
3044: l_old_str_stmv_rec okl_streams_pub.stmv_rec_type;
3045: l_old_acr_stmv_rec okl_streams_pub.stmv_rec_type;
3046: l_last_se_line_number OKL_STRM_ELEMENTS_V.SE_LINE_NUMBER%TYPE;
3083: kle.residual_value residual_value
3084: FROM okc_rule_groups_b rgp,
3085: okc_rules_b rul1,
3086: okc_rules_b rul2,
3087: okc_k_lines_b cle,
3088: okl_k_lines kle,
3089: okc_line_styles_b lse
3090: WHERE rul2.dnz_chr_id = p_chr_id
3091: AND rul2.rule_information_category = 'LASLL'
5618: CURSOR l_line_csr( p_chr_id NUMBER )
5619: IS
5620: SELECT kle.id kle_id,
5621: kle.start_date
5622: FROM okc_k_lines_b kle,
5623: okc_line_styles_b lse
5624: WHERE kle.lse_id = lse.id
5625: AND lse.lty_code in ( 'FREE_FORM1', 'FEE', 'SOLD_SERVICE', 'LINK_SERV_ASSET')--bug# 3343133 (ssiruvol)
5626: AND kle.dnz_chr_id = p_chr_id;
6160: FROM okc_rule_groups_b crg,
6161: okc_rules_b crl,
6162: okc_rules_b crl1,
6163: -- Bug 7653893 : RGOOTY
6164: okc_k_lines_b cle
6165: WHERE crl.rgp_id = crg.id
6166: AND crg.rgd_code = 'LALEVL'
6167: AND crl.rule_information_category = 'LASLL'
6168: AND crl1.rule_information_category = 'LASLH'
6636: ln_sub_exists NUMBER := 0;
6637: ln_esg_isg NUMBER := 0;
6638:
6639: -- Validate weather the stream generation is done thru internal/external stream generation
6640: CURSOR validate_esg_isg (p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
6641: IS
6642: SELECT 1
6643: FROM dual
6644: WHERE EXISTS (SELECT 1
6646: WHERE khr_id= p_dnz_chr_id
6647: AND say_code = 'WORK'
6648: AND sgn_code = 'STMP');
6649: -- Validate the contract has Subsidy line
6650: CURSOR validate_sub_line (p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
6651: IS
6652: SELECT 1
6653: FROM dual
6654: WHERE EXISTS (SELECT '1'
6652: SELECT 1
6653: FROM dual
6654: WHERE EXISTS (SELECT '1'
6655: FROM okl_subsidies_b subb,
6656: okc_k_lines_b cle_fin,
6657: okc_line_styles_b lse_fin,
6658: okc_k_lines_b cle,
6659: okl_k_headers khr,
6660: okl_k_lines kle,
6654: WHERE EXISTS (SELECT '1'
6655: FROM okl_subsidies_b subb,
6656: okc_k_lines_b cle_fin,
6657: okc_line_styles_b lse_fin,
6658: okc_k_lines_b cle,
6659: okl_k_headers khr,
6660: okl_k_lines kle,
6661: okc_line_styles_b lse
6662: WHERE cle.dnz_chr_id = p_dnz_chr_id
6848: CURSOR c_tmt_assets (cp_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
6849: SELECT CLE.ID LINE_ID
6850: , TRUNC(CLE.DATE_TERMINATED) TERMINATION_DATE
6851: FROM OKL_K_HEADERS KHR
6852: , OKC_K_LINES_B CLE
6853: , OKC_LINE_STYLES_B LSE
6854: , OKC_STATUSES_B STS
6855: WHERE CLE.DNZ_CHR_ID = KHR.ID
6856: AND LSE.ID = CLE.LSE_ID
6860: AND KHR.ID = cp_chr_id;
6861:
6862: --Cursor to get the streams for terminated asset upto termination date
6863: CURSOR c_streams (cp_chr_id OKC_K_HEADERS_B.ID%TYPE
6864: , cp_cle_id OKC_K_LINES_B.ID%TYPE
6865: , cp_date_terminated DATE
6866: , cp_trx_number okl_streams.transaction_number%TYPE) IS
6867: SELECT STM.ID STREAM_ID
6868: , STM.STY_ID
6897: l_stmv_tbl okl_streams_pub.stmv_tbl_type;
6898: lx_stmv_tbl okl_streams_pub.stmv_tbl_type;
6899: l_selv_tbl okl_streams_pub.selv_tbl_type;
6900: lx_selv_tbl okl_streams_pub.selv_tbl_type;
6901: l_kle_id OKC_K_LINES_B.ID%TYPE;
6902:
6903: BEGIN
6904:
6905: x_return_status := OKL_API.G_RET_STS_SUCCESS;
7015: --Cursor to get the terminated assets
7016: CURSOR c_tmt_assets (cp_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
7017: SELECT CLE.ID LINE_ID
7018: FROM OKL_K_HEADERS KHR
7019: , OKC_K_LINES_B CLE
7020: , OKC_LINE_STYLES_B LSE
7021: , OKC_STATUSES_B STS
7022: WHERE CLE.DNZ_CHR_ID = KHR.ID
7023: AND LSE.ID = CLE.LSE_ID
7027: AND KHR.ID = cp_chr_id;
7028:
7029: --Cursor to get the stream ids for terminated asset
7030: CURSOR c_streams (cp_chr_id OKC_K_HEADERS_B.ID%TYPE
7031: , cp_cle_id OKC_K_LINES_B.ID%TYPE
7032: , cp_purpose VARCHAR2) IS
7033: SELECT STM.ID STREAM_ID
7034: FROM OKL_STREAMS STM
7035: , OKL_STRM_TYPE_B STY
7840:
7841: -- vdamerla Forward Ported Bug6149951 starts
7842:
7843: l_orig_chr_id OKC_K_HEADERS_B.ID%TYPE; -- smadhava - Bug# 6149951
7844: l_new_stm_cle_id OKC_K_LINES_B.ID%TYPE; -- smadhava - Bug# 6149951
7845:
7846: -- vdamerla Forward Ported Bug6149951 ends
7847:
7848: -- Declare records/table types
7855: CURSOR c_lnk_fin_rol_trm_fee (cp_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
7856: SELECT LNK_CLE.ID LNK_ID
7857: , FEE_CLE.ID FEE_ID
7858: , LNK_CLE.DATE_TERMINATED
7859: FROM OKC_K_LINES_B LNK_CLE
7860: , OKC_LINE_STYLES_B LNK_LSE
7861: , OKL_K_LINES FEE_KLE
7862: , OKC_K_LINES_B FEE_CLE
7863: , OKC_STATUSES_B STS
7858: , LNK_CLE.DATE_TERMINATED
7859: FROM OKC_K_LINES_B LNK_CLE
7860: , OKC_LINE_STYLES_B LNK_LSE
7861: , OKL_K_LINES FEE_KLE
7862: , OKC_K_LINES_B FEE_CLE
7863: , OKC_STATUSES_B STS
7864: WHERE LNK_CLE.CLE_ID = FEE_CLE.ID
7865: AND LNK_CLE.DNZ_CHR_ID = cp_chr_id
7866: AND FEE_CLE.DNZ_CHR_ID = cp_chr_id
7878: CURSOR c_tmt_asset_loan (cp_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
7879: SELECT CLE.ID LINE_ID
7880: , CLE.DATE_TERMINATED
7881: FROM OKL_K_HEADERS KHR
7882: , OKC_K_LINES_B CLE
7883: , OKC_LINE_STYLES_B LSE
7884: , OKC_STATUSES_B STS
7885: WHERE CLE.DNZ_CHR_ID = KHR.ID
7886: AND LSE.ID = CLE.LSE_ID
7901: -- Added ISG generated stream type purposes so that these streams will not be
7902: -- copied over during mass/online rebook. Streams will be regenerated by ISG
7903:
7904: CURSOR c_streams (cp_chr_id OKC_K_HEADERS_B.ID%TYPE
7905: , cp_cle_id OKC_K_LINES_B.ID%TYPE
7906: , cp_date_terminated DATE
7907: , cp_trx_number okl_streams.transaction_number%TYPE) IS
7908: SELECT STM.ID STREAM_ID
7909: , STM.STY_ID
7980: CURSOR l_get_curr_streams_lines
7981: IS
7982: SELECT distinct stm.kle_id
7983: FROM okl_streams stm,
7984: okc_k_lines_b ln
7985: WHERE stm.khr_id = p_chr_id
7986: AND stm.say_code = 'CURR'
7987: AND stm.kle_id = ln.id
7988: AND ln.STS_CODE = 'TERMINATED'
7991: where si.transaction_number = p_trx_number));
7992:
7993: --Cursor to get the line terminated date
7994: --Modified by vdamerla for bug 6344223
7995: CURSOR l_get_line_term_date(p_line_id OKC_K_LINES_B.ID%TYPE)
7996: IS
7997: SELECT date_terminated
7998: FROM okc_k_lines_b
7999: WHERE id = p_line_id;
7994: --Modified by vdamerla for bug 6344223
7995: CURSOR l_get_line_term_date(p_line_id OKC_K_LINES_B.ID%TYPE)
7996: IS
7997: SELECT date_terminated
7998: FROM okc_k_lines_b
7999: WHERE id = p_line_id;
8000:
8001: --To get the streams for which kle_id is null
8002: --Also streams related to Investor and Interest Calc Prog should not be copied over as these streams are not historized
8049: -- Get Line ID in rebook copy that was copied from LINE the original contract
8050: -- cp_rbk_chr_id is the rebook copy contract
8051: -- cp_ole_id is original contract line ID
8052: CURSOR c_get_orig_line_id (cp_rbk_chr_id IN okc_k_headers_b.id%type
8053: , cp_ole_id IN OKC_K_LINES_B.ID%TYPE) is
8054: SELECT cle.id new_cle_id
8055: FROM okc_k_lines_b cle
8056: ,okc_k_lines_b ole
8057: WHERE cle.dnz_chr_id=cp_rbk_chr_id
8051: -- cp_ole_id is original contract line ID
8052: CURSOR c_get_orig_line_id (cp_rbk_chr_id IN okc_k_headers_b.id%type
8053: , cp_ole_id IN OKC_K_LINES_B.ID%TYPE) is
8054: SELECT cle.id new_cle_id
8055: FROM okc_k_lines_b cle
8056: ,okc_k_lines_b ole
8057: WHERE cle.dnz_chr_id=cp_rbk_chr_id
8058: and ole.id = cp_ole_id
8059: and ole.lse_id = cle.lse_id
8052: CURSOR c_get_orig_line_id (cp_rbk_chr_id IN okc_k_headers_b.id%type
8053: , cp_ole_id IN OKC_K_LINES_B.ID%TYPE) is
8054: SELECT cle.id new_cle_id
8055: FROM okc_k_lines_b cle
8056: ,okc_k_lines_b ole
8057: WHERE cle.dnz_chr_id=cp_rbk_chr_id
8058: and ole.id = cp_ole_id
8059: and ole.lse_id = cle.lse_id
8060: and ole.id=cle.orig_system_id1;
8098: SELECT NVL(CHK_ACCRUAL_PREVIOUS_MNTH_YN,'N')
8099: FROM OKL_SYSTEM_PARAMS_ALL;
8100:
8101:
8102: CURSOR chk_curr_terminated_line(p_kle_id IN OKC_K_LINES_B.ID%TYPE) IS
8103: SELECT 'Y'
8104: FROM okc_k_headers_b CHR,
8105: okl_trx_contracts ktrx,
8106: okl_trx_contracts trx,
8615: FROM okl_sif_ret_levels lev,
8616: okl_sif_rets ret,
8617: okl_stream_interfaces sif,
8618: okl_sif_lines line,
8619: okc_k_lines_b cle,
8620: okl_k_lines kle,
8621: okc_line_styles_b lse,
8622: okl_sif_fees fee
8623: WHERE lev.sir_id = ret.id
9610: -- get the Contract line info
9611: CURSOR l_line_rec_csr(chrid NUMBER)
9612: IS
9613: SELECT cle.id
9614: FROM okc_k_lines_b cle,
9615: okc_line_styles_b lse,
9616: okc_statuses_b sts
9617: WHERE cle.dnz_chr_id = chrid
9618: AND cle.lse_id = lse.id
9624: -- Get all valid fee lines
9625: CURSOR l_fee_type_csr ( chrId NUMBER, feeId NUMBER ) IS
9626: SELECT kle.fee_type
9627: FROM okl_k_lines kle,
9628: okc_k_lines_b cle,
9629: okc_line_styles_b lse,
9630: okc_statuses_b sts
9631: WHERE cle.lse_id = lse.id
9632: AND lse.lty_code = 'FEE'
9641: -- To check if the chr_id has Financed fee feetype defined
9642: CURSOR l_fin_type_csr (chrId NUMBER)
9643: IS
9644: SELECT cle.id fin_id
9645: FROM okc_k_lines_b cle,
9646: okc_line_styles_b lse,
9647: okl_k_lines kle,
9648: okc_statuses_b sts
9649: WHERE cle.dnz_chr_id = chrId
9657: -- Code required for rollover quote- HKPATEL
9658: CURSOR l_rollover_csr(chrId NUMBER)
9659: IS
9660: SELECT cle.id fin_id
9661: FROM okc_k_lines_b cle,
9662: okc_line_styles_b lse,
9663: okl_k_lines kle,
9664: okc_statuses_b sts
9665: WHERE cle.dnz_chr_id = chrId
9672:
9673: CURSOR l_link_rollover_csr(cleId NUMBER)
9674: IS
9675: SELECT okc.id kle_id
9676: FROM okc_k_lines_b okc,
9677: okl_k_lines kle ,
9678: okc_line_styles_b lse
9679: WHERE okc.cle_id = cleId
9680: AND okc.lse_id = lse.id
9684:
9685: -- To get the Subsidy Income Accural stream type id
9686: -- and also fetch the sub line id
9687: /*
9688: CURSOR get_sub_line_csr (p_dnz_chr_id okc_k_lines_b.dnz_chr_id%TYPE)
9689: IS
9690: SELECT TO_NUMBER(sgn.value) sty_id,
9691: cle.id
9692: FROM okl_sgn_translations sgn,
9694: okl_subsidies_v sub,
9695: okl_k_lines kle,
9696: okc_statuses_b sts,
9697: okc_line_styles_b lse,
9698: okc_k_lines_b cle
9699: WHERE cle.dnz_chr_id = p_dnz_chr_id
9700: AND cle.lse_id = lse.id
9701: AND lse.lty_code = 'SUBSIDY'
9702: AND sts.code = cle.sts_code
9706: AND sgn.object1_id1 = TO_CHAR(sty.id)
9707: AND sgn.jtot_object1_code = 'OKL_STRMTYP'
9708: AND kle.subsidy_id = sub.id;
9709: */
9710: CURSOR get_sub_line_csr(p_dnz_chr_id IN okc_k_lines_b.dnz_chr_id%TYPE)
9711: IS
9712: SELECT kle.sty_id,
9713: cle.id
9714: --srsreeni Bug5699923 Start
9714: --srsreeni Bug5699923 Start
9715: ,sts.ste_code
9716: --srsreeni Bug5699923 End
9717: FROM okl_k_lines kle,
9718: okc_k_lines_b cle,
9719: okl_subsidies_v sub,
9720: okc_statuses_b sts,
9721: okc_line_styles_b lse
9722: WHERE cle.dnz_chr_id = p_dnz_chr_id
10424: l_csm_one_off_fee_tbl okl_create_streams_pub.csm_one_off_fee_tbl_type := px_csm_one_off_fee_tbl;
10425: i BINARY_INTEGER := 0;
10426: lv_acc_mtd_code okl_subsidies_b.accounting_method_code%TYPE := NULL;
10427: lv_strm_desc VARCHAR2(200);
10428: ln_sub_id okc_k_lines_b.id%TYPE := NULL;
10429: ln_dummy NUMBER := 0;
10430: ln_cap_wos okl_k_lines.capital_amount%TYPE := 0;
10431: ln_cap_amt okl_k_lines.capital_amount%TYPE := 0;
10432: ln_cap_dis okl_k_lines.capital_amount%TYPE := 0;
10434: l_sty_id NUMBER;
10435: l_dep_sty_id NUMBER;
10436: l_return_status VARCHAR2(1);
10437: -- get Asset lines for the contract
10438: CURSOR get_asset_lines_csr (p_chr_id okc_k_lines_b.id%TYPE)
10439: IS
10440: SELECT cle.id,
10441: NVL(kle.date_funding,cle.start_date) date_funding ,
10442: kle.orig_contract_line_id --sechawla 15-jun-09 PRB ESG Enhancement : added orig_contract_line_id
10439: IS
10440: SELECT cle.id,
10441: NVL(kle.date_funding,cle.start_date) date_funding ,
10442: kle.orig_contract_line_id --sechawla 15-jun-09 PRB ESG Enhancement : added orig_contract_line_id
10443: FROM okc_k_lines_b cle,
10444: okl_k_lines kle,
10445: okc_line_styles_b lse
10446: WHERE cle.dnz_chr_id = p_chr_id
10447: AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED')
10455: IS
10456: SELECT subb.accounting_method_code,
10457: cle.id
10458: FROM okl_subsidies_b subb,
10459: okc_k_lines_b cle_fin,
10460: okc_line_styles_b lse_fin,
10461: okc_k_lines_b cle,
10462: okl_k_lines kle,
10463: okc_line_styles_b lse
10457: cle.id
10458: FROM okl_subsidies_b subb,
10459: okc_k_lines_b cle_fin,
10460: okc_line_styles_b lse_fin,
10461: okc_k_lines_b cle,
10462: okl_k_lines kle,
10463: okc_line_styles_b lse
10464: WHERE cle.cle_id = p_line_id
10465: AND cle.id = kle.id
10470: AND cle_fin.id = cle.cle_id
10471: AND cle_fin.lse_id = lse_fin.id
10472: AND lse_fin.lty_code = 'FREE_FORM1';
10473: -- Validate the contract has Subsidy line
10474: CURSOR validate_sub_line (p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
10475: IS
10476: SELECT 1
10477: FROM dual
10478: WHERE EXISTS (SELECT '1'
10476: SELECT 1
10477: FROM dual
10478: WHERE EXISTS (SELECT '1'
10479: FROM okl_subsidies_b subb,
10480: okc_k_lines_b cle_fin,
10481: okc_line_styles_b lse_fin,
10482: okc_k_lines_b cle,
10483: okl_k_lines kle,
10484: okc_line_styles_b lse
10478: WHERE EXISTS (SELECT '1'
10479: FROM okl_subsidies_b subb,
10480: okc_k_lines_b cle_fin,
10481: okc_line_styles_b lse_fin,
10482: okc_k_lines_b cle,
10483: okl_k_lines kle,
10484: okc_line_styles_b lse
10485: WHERE cle.dnz_chr_id = p_dnz_chr_id
10486: AND cle.cle_id = cle_fin.id
10493: AND cle_fin.lse_id = lse_fin.id
10494: AND lse_fin.lty_code = 'FREE_FORM1');
10495: -- To get the Subsidy Income Accural Description
10496: -- Changed for bug 4044089 by HKPATEL 12/02/2004
10497: CURSOR get_sub_income_strm_csr (p_dnz_chr_id IN okc_k_lines_b.dnz_chr_id%TYPE,
10498: p_sub_cle_id IN okc_k_lines_b.id%TYPE)
10499: IS
10500:
10501: SELECT kle.sty_id
10494: AND lse_fin.lty_code = 'FREE_FORM1');
10495: -- To get the Subsidy Income Accural Description
10496: -- Changed for bug 4044089 by HKPATEL 12/02/2004
10497: CURSOR get_sub_income_strm_csr (p_dnz_chr_id IN okc_k_lines_b.dnz_chr_id%TYPE,
10498: p_sub_cle_id IN okc_k_lines_b.id%TYPE)
10499: IS
10500:
10501: SELECT kle.sty_id
10502: FROM okl_k_lines kle,
10499: IS
10500:
10501: SELECT kle.sty_id
10502: FROM okl_k_lines kle,
10503: okc_k_lines_b cle,
10504: okc_line_styles_b lse,
10505: okl_subsidies_v sub
10506:
10507: WHERE cle.dnz_chr_id = p_dnz_chr_id
10517: okl_strm_type_b sty,
10518: okl_subsidies_v sub,
10519: okl_k_lines kle,
10520: okc_line_styles_b lse,
10521: okc_k_lines_b cle
10522: WHERE cle.dnz_chr_id = p_dnz_chr_id
10523: AND cle.id = p_sub_cle_id
10524: AND cle.lse_id = lse.id
10525: AND lse.lty_code = 'SUBSIDY'
11090: , sts.ste_code
11091: -- srsreeni - Bug#5699923 - Added - End
11092: , kle.orig_contract_line_id ,--sechawla 15-jul-09 PRB ESG enhancements : added orig_contract_line_id
11093: okc.start_date --11-Sep-09 sechawla 8798556 : added
11094: FROM okc_k_lines_b okc,
11095: okl_k_lines kle ,
11096: okc_line_styles_b lse
11097: ,okc_statuses_b sts
11098: WHERE okc.cle_id = cleId
11112: kle.fee_purpose_code fee_purpose_code,
11113: styt.id sty_id
11114: FROM okl_strm_type_tl styt,
11115: okc_k_items cim,
11116: okc_k_lines_b cle,
11117: okl_k_lines kle
11118: WHERE cle.dnz_chr_id = p_chr_d
11119: AND cle.id = p_cle_id
11120: AND cle.id = kle.id
20150: IS
20151: SELECT 1
20152: FROM dual
20153: WHERE EXISTS (SELECT 1
20154: FROM okc_k_lines_b cle,
20155: okc_line_styles_b lse,
20156: okl_k_lines kle
20157: WHERE cle.dnz_chr_id = chrId
20158: AND cle.lse_id = lse.id
20217: hz_party_sites hps,
20218: hz_party_site_uses hpsu,
20219: okl_txl_itm_insts iti,
20220: okc_line_styles_b lse_ib,
20221: okc_k_lines_b cle_ib
20222: WHERE cle_ib.dnz_chr_id = chrId
20223: AND cle_ib.lse_id = lse_ib.id
20224: AND lse_ib.lty_code = 'INST_ITEM'
20225: AND iti.kle_id = cle_ib.id
20287: CURSOR get_dis_amt_csr_lease(p_chr_id NUMBER, p_cle_id NUMBER)
20288: IS
20289: SELECT kle_sub.amount, kle_sub.SUBSIDY_OVERRIDE_AMOUNT
20290: FROM okl_subsidies_b subb,
20291: okc_k_lines_b cle_fin,
20292: okc_line_styles_b lse_fin,
20293: okc_k_lines_b cle_sub,
20294: okl_k_lines kle_sub,
20295: okc_line_styles_b lse_sub
20289: SELECT kle_sub.amount, kle_sub.SUBSIDY_OVERRIDE_AMOUNT
20290: FROM okl_subsidies_b subb,
20291: okc_k_lines_b cle_fin,
20292: okc_line_styles_b lse_fin,
20293: okc_k_lines_b cle_sub,
20294: okl_k_lines kle_sub,
20295: okc_line_styles_b lse_sub
20296: WHERE cle_fin.id = p_cle_id
20297: AND cle_fin.dnz_chr_id = p_chr_id
20467: l_days_in_yr_code OKL_ST_GEN_TMPT_SETS.days_in_yr_code%type;
20468:
20469: -- Start fix for bug 6858487
20470: l_orig_chr_id OKC_K_HEADERS_B.ID%TYPE;
20471: l_orig_cle_id OKC_K_LINES_B.ID%TYPE;
20472:
20473: -- Get Line ID in rebook copy that was copied from LINE the original contract
20474: -- cp_rbk_chr_id is the rebook copy contract
20475: -- cp_ole_id is original contract line ID
20473: -- Get Line ID in rebook copy that was copied from LINE the original contract
20474: -- cp_rbk_chr_id is the rebook copy contract
20475: -- cp_ole_id is original contract line ID
20476: CURSOR c_get_orig_line_id (cp_rbk_chr_id IN okc_k_headers_b.id%type
20477: , cp_ole_id IN OKC_K_LINES_B.ID%TYPE) is
20478: SELECT --cle.id new_cle_id
20479: cle.orig_system_id1
20480: FROM okc_k_lines_b cle
20481: ,okc_k_lines_b ole
20476: CURSOR c_get_orig_line_id (cp_rbk_chr_id IN okc_k_headers_b.id%type
20477: , cp_ole_id IN OKC_K_LINES_B.ID%TYPE) is
20478: SELECT --cle.id new_cle_id
20479: cle.orig_system_id1
20480: FROM okc_k_lines_b cle
20481: ,okc_k_lines_b ole
20482: WHERE cle.dnz_chr_id=cp_rbk_chr_id
20483: and cle.id = cp_ole_id
20484: and ole.lse_id = cle.lse_id
20477: , cp_ole_id IN OKC_K_LINES_B.ID%TYPE) is
20478: SELECT --cle.id new_cle_id
20479: cle.orig_system_id1
20480: FROM okc_k_lines_b cle
20481: ,okc_k_lines_b ole
20482: WHERE cle.dnz_chr_id=cp_rbk_chr_id
20483: and cle.id = cp_ole_id
20484: and ole.lse_id = cle.lse_id
20485: and ole.id=cle.orig_system_id1;
23640: -- Get Passthrough Fee Lines
23641: CURSOR c_get_psth_csr(p_khr_id okl_k_headers.id%TYPE)
23642: IS
23643: SELECT cle.id
23644: FROM okc_k_lines_b cle,
23645: okl_k_lines kle,
23646: okc_line_styles_b lse
23647: WHERE cle.dnz_chr_id = p_khr_id
23648: AND cle.lse_id = lse.id
23669: -- Get all the Top lines for p_khr_id
23670: CURSOR c_get_all_lines_csr(p_khr_id okl_k_headers.id%TYPE)
23671: IS
23672: SELECT cle.id line_id
23673: FROM okc_k_lines_b cle,
23674: okc_line_styles_b lse
23675: WHERE cle.dnz_chr_id = p_khr_id
23676: AND cle.lse_id = lse.id
23677: AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED')
24246: cle_so.START_DATE
24247: FROM okc_k_headers_b chr_rv,
24248: okc_line_styles_b lse_rv,
24249: okl_k_lines kle_rv,
24250: okc_k_lines_b cle_rv,
24251: okc_rules_b rul_rv,
24252: okc_rule_groups_b rgp_rv,
24253: okc_line_styles_b lse_so,
24254: okc_k_lines_b cle_so,
24250: okc_k_lines_b cle_rv,
24251: okc_rules_b rul_rv,
24252: okc_rule_groups_b rgp_rv,
24253: okc_line_styles_b lse_so,
24254: okc_k_lines_b cle_so,
24255: okc_rules_b rul_so,
24256: okc_rule_groups_b rgp_so
24257: WHERE rgp_so.cle_id = p_kle_id
24258: AND rgp_so.dnz_chr_id = p_khr_id
24369: cle_so.start_date
24370: FROM okc_k_headers_b chr_rv,
24371: okc_line_styles_b lse_rv,
24372: okl_k_lines kle_rv,
24373: okc_k_lines_b cle_rv,
24374: okc_rules_b rul_rv,
24375: okc_rule_groups_b rgp_rv,
24376: okc_line_styles_b lse_so,
24377: okc_k_lines_b cle_so,
24373: okc_k_lines_b cle_rv,
24374: okc_rules_b rul_rv,
24375: okc_rule_groups_b rgp_rv,
24376: okc_line_styles_b lse_so,
24377: okc_k_lines_b cle_so,
24378: okc_rules_b rul_so,
24379: okc_rule_groups_b rgp_so
24380: WHERE rgp_so.cle_id = p_kle_id
24381: AND rgp_so.dnz_chr_id = p_khr_id
24649: PROCEDURE extract_params_so(
24650: p_api_version IN NUMBER,
24651: p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
24652: p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
24653: p_cle_id IN OKC_K_LINES_B.ID%TYPE,
24654: x_return_status OUT NOCOPY VARCHAR2,
24655: x_msg_count OUT NOCOPY NUMBER,
24656: x_msg_data OUT NOCOPY VARCHAR2,
24657: x_csm_lease_header OUT NOCOPY okl_create_streams_pub.csm_lease_rec_type,
24760: hz_party_sites hps,
24761: hz_party_site_uses hpsu,
24762: okl_txl_itm_insts iti,
24763: okc_line_styles_b lse_ib,
24764: okc_k_lines_b cle_ib
24765: WHERE cle_ib.dnz_chr_id = chrId
24766: AND cle_ib.lse_id = lse_ib.id
24767: AND lse_ib.lty_code = 'INST_ITEM'
24768: AND iti.kle_id = cle_ib.id
24977: IS
24978: SELECT 1
24979: FROM dual
24980: WHERE EXISTS (SELECT 1
24981: FROM okc_k_lines_b cle,
24982: okc_line_styles_b lse,
24983: okl_k_lines kle
24984: WHERE cle.dnz_chr_id = chrId
24985: AND cle.lse_id = lse.id
24998: CURSOR get_cap_fee_amt(p_chr_id okc_k_headers_b.id%TYPE)
24999: IS
25000: SELECT NVL(kle.amount, 0) total_cap_fee
25001: FROM okl_k_lines kle,
25002: okc_k_lines_b cle,
25003: okc_k_items cim,
25004: okl_strm_type_b sty
25005: WHERE cle.dnz_chr_id = p_chr_id
25006: AND cle.id = kle.id
25015: -- Start of BAKUCHIB modification bug#3121708
25016: -- Get diff in days between contract end_date
25017: -- and max payment leve(LASLL) end_date
25018: CURSOR c_diff_days_csr(p_chr_id okc_k_headers_b.id%TYPE,
25019: p_cle_id okc_k_lines_b.id%TYPE,
25020: p_slh_id okc_rule_groups_b.id%TYPE)
25021: IS
25022: SELECT chrv.end_date chr_end_date,
25023: MAX(FND_DATE.canonical_to_date(sll.rule_information2)) pay_start_date,