[Home] [Help]
1691: IS
1692: -- cursor to fetch the abosrbed fee line with purpose as RVI
1693: CURSOR rvi_fee_csr IS
1694: SELECT KLE.ID ID
1695: FROM OKL_K_LINES KLE, OKC_K_LINES_B CLE, OKC_LINE_STYLES_B LSE
1696: WHERE CLE.CHR_ID = p_chr_id
1697: AND CLE.DNZ_CHR_ID = p_chr_id
1698: AND CLE.ID = KLE.ID
1699: AND CLE.LSE_ID = LSE.ID
2134: IS
2135: SELECT 'Y'
2136: FROM dual
2137: WHERE EXISTS (SELECT 'Y'
2138: FROM okc_k_lines_b cle,
2139: okc_line_styles_b lse
2140: WHERE cle.dnz_chr_id = p_chr_id
2141: AND cle.lse_id = lse.id
2142: AND cle.sts_code = 'TERMINATED'
2147: IS
2148: SELECT 'Y'
2149: FROM dual
2150: WHERE EXISTS (SELECT 'Y'
2151: FROM okc_k_lines_b cle,
2152: okc_line_styles_b lse
2153: WHERE cle.dnz_chr_id = p_chr_id
2154: AND cle.id = p_cle_id
2155: AND cle.lse_id = lse.id
2359: -- So we look for Fee lines in the Table of Records
2360: CURSOR c_fee_line_csr(p_khr_id OKC_K_HEADERS_B.ID%TYPE)
2361: IS
2362: SELECT cle.id id
2363: FROM okc_k_lines_b cle,
2364: okc_line_styles_b lse
2365: WHERE dnz_chr_id = p_khr_id
2366: AND cle.lse_id = lse.id
2367: AND lse.lty_code = 'FEE';
2623: lx_selv_tbl okl_streams_pvt.selv_tbl_type;
2624: l_stmv_rec okl_streams_pub.stmv_rec_type;
2625: lx_stmv_rec okl_streams_pub.stmv_rec_type;
2626: --srsreeni bug 5869271 start add
2627: l_old_cle_id okc_k_lines_b.ID%TYPE;
2628: l_old_sty_id okl_strm_type_b.ID%TYPE;
2629: l_old_str_stmv_rec okl_streams_pub.stmv_rec_type;
2630: l_old_acr_stmv_rec okl_streams_pub.stmv_rec_type;
2631: l_last_se_line_number OKL_STRM_ELEMENTS_V.SE_LINE_NUMBER%TYPE;
2667: kle.residual_value residual_value
2668: FROM okc_rule_groups_b rgp,
2669: okc_rules_b rul1,
2670: okc_rules_b rul2,
2671: okc_k_lines_b cle,
2672: okl_k_lines kle,
2673: okc_line_styles_b lse
2674: WHERE rul2.dnz_chr_id = p_chr_id
2675: AND rul2.rule_information_category = 'LASLL'
4519: CURSOR l_line_csr( p_chr_id NUMBER )
4520: IS
4521: SELECT kle.id kle_id,
4522: kle.start_date
4523: FROM okc_k_lines_b kle,
4524: okc_line_styles_b lse
4525: WHERE kle.lse_id = lse.id
4526: AND lse.lty_code in ( 'FREE_FORM1', 'FEE', 'SOLD_SERVICE', 'LINK_SERV_ASSET')--bug# 3343133 (ssiruvol)
4527: AND kle.dnz_chr_id = p_chr_id;
5526: ln_sub_exists NUMBER := 0;
5527: ln_esg_isg NUMBER := 0;
5528:
5529: -- Validate weather the stream generation is done thru internal/external stream generation
5530: CURSOR validate_esg_isg (p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
5531: IS
5532: SELECT 1
5533: FROM dual
5534: WHERE EXISTS (SELECT 1
5536: WHERE khr_id= p_dnz_chr_id
5537: AND say_code = 'WORK'
5538: AND sgn_code = 'STMP');
5539: -- Validate the contract has Subsidy line
5540: CURSOR validate_sub_line (p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
5541: IS
5542: SELECT 1
5543: FROM dual
5544: WHERE EXISTS (SELECT '1'
5542: SELECT 1
5543: FROM dual
5544: WHERE EXISTS (SELECT '1'
5545: FROM okl_subsidies_b subb,
5546: okc_k_lines_b cle_fin,
5547: okc_line_styles_b lse_fin,
5548: okc_k_lines_b cle,
5549: okl_k_headers khr,
5550: okl_k_lines kle,
5544: WHERE EXISTS (SELECT '1'
5545: FROM okl_subsidies_b subb,
5546: okc_k_lines_b cle_fin,
5547: okc_line_styles_b lse_fin,
5548: okc_k_lines_b cle,
5549: okl_k_headers khr,
5550: okl_k_lines kle,
5551: okc_line_styles_b lse
5552: WHERE cle.dnz_chr_id = p_dnz_chr_id
6403:
6404: -- vdamerla Forward Ported Bug6149951 starts
6405:
6406: l_orig_chr_id OKC_K_HEADERS_B.ID%TYPE; -- smadhava - Bug# 6149951
6407: l_new_stm_cle_id OKC_K_LINES_B.ID%TYPE; -- smadhava - Bug# 6149951
6408:
6409: -- vdamerla Forward Ported Bug6149951 ends
6410:
6411: -- Declare records/table types
6418: CURSOR c_lnk_fin_rol_trm_fee (cp_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
6419: SELECT LNK_CLE.ID LNK_ID
6420: , FEE_CLE.ID FEE_ID
6421: , LNK_CLE.DATE_TERMINATED
6422: FROM OKC_K_LINES_B LNK_CLE
6423: , OKC_LINE_STYLES_B LNK_LSE
6424: , OKL_K_LINES FEE_KLE
6425: , OKC_K_LINES_B FEE_CLE
6426: , OKC_STATUSES_B STS
6421: , LNK_CLE.DATE_TERMINATED
6422: FROM OKC_K_LINES_B LNK_CLE
6423: , OKC_LINE_STYLES_B LNK_LSE
6424: , OKL_K_LINES FEE_KLE
6425: , OKC_K_LINES_B FEE_CLE
6426: , OKC_STATUSES_B STS
6427: WHERE LNK_CLE.CLE_ID = FEE_CLE.ID
6428: AND LNK_CLE.DNZ_CHR_ID = cp_chr_id
6429: AND FEE_CLE.DNZ_CHR_ID = cp_chr_id
6441: CURSOR c_tmt_asset_loan (cp_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
6442: SELECT CLE.ID LINE_ID
6443: , CLE.DATE_TERMINATED
6444: FROM OKL_K_HEADERS KHR
6445: , OKC_K_LINES_B CLE
6446: , OKC_LINE_STYLES_B LSE
6447: , OKC_STATUSES_B STS
6448: WHERE CLE.DNZ_CHR_ID = KHR.ID
6449: AND LSE.ID = CLE.LSE_ID
6464: -- Added ISG generated stream type purposes so that these streams will not be
6465: -- copied over during mass/online rebook. Streams will be regenerated by ISG
6466:
6467: CURSOR c_streams (cp_chr_id OKC_K_HEADERS_B.ID%TYPE
6468: , cp_cle_id OKC_K_LINES_B.ID%TYPE
6469: , cp_date_terminated DATE
6470: , cp_trx_number okl_streams.transaction_number%TYPE) IS
6471: SELECT STM.ID STREAM_ID
6472: , STM.STY_ID
6542: CURSOR l_get_curr_streams_lines
6543: IS
6544: SELECT distinct stm.kle_id
6545: FROM okl_streams stm,
6546: okc_k_lines_b ln
6547: WHERE stm.khr_id = p_chr_id
6548: AND stm.say_code = 'CURR'
6549: AND stm.kle_id = ln.id
6550: AND ln.STS_CODE = 'TERMINATED'
6553: where si.transaction_number = p_trx_number));
6554:
6555: --Cursor to get the line terminated date
6556: --Modified by vdamerla for bug 6344223
6557: CURSOR l_get_line_term_date(p_line_id OKC_K_LINES_B.ID%TYPE)
6558: IS
6559: SELECT date_terminated
6560: FROM okc_k_lines_b
6561: WHERE id = p_line_id;
6556: --Modified by vdamerla for bug 6344223
6557: CURSOR l_get_line_term_date(p_line_id OKC_K_LINES_B.ID%TYPE)
6558: IS
6559: SELECT date_terminated
6560: FROM okc_k_lines_b
6561: WHERE id = p_line_id;
6562:
6563: --To get the streams for which kle_id is null
6564: --Also streams related to Investor and Interest Calc Prog should not be copied over as these streams are not historized
6611: -- Get Line ID in rebook copy that was copied from LINE the original contract
6612: -- cp_rbk_chr_id is the rebook copy contract
6613: -- cp_ole_id is original contract line ID
6614: CURSOR c_get_orig_line_id (cp_rbk_chr_id IN okc_k_headers_b.id%type
6615: , cp_ole_id IN OKC_K_LINES_B.ID%TYPE) is
6616: SELECT cle.id new_cle_id
6617: FROM okc_k_lines_b cle
6618: ,okc_k_lines_b ole
6619: WHERE cle.dnz_chr_id=cp_rbk_chr_id
6613: -- cp_ole_id is original contract line ID
6614: CURSOR c_get_orig_line_id (cp_rbk_chr_id IN okc_k_headers_b.id%type
6615: , cp_ole_id IN OKC_K_LINES_B.ID%TYPE) is
6616: SELECT cle.id new_cle_id
6617: FROM okc_k_lines_b cle
6618: ,okc_k_lines_b ole
6619: WHERE cle.dnz_chr_id=cp_rbk_chr_id
6620: and ole.id = cp_ole_id
6621: and ole.lse_id = cle.lse_id
6614: CURSOR c_get_orig_line_id (cp_rbk_chr_id IN okc_k_headers_b.id%type
6615: , cp_ole_id IN OKC_K_LINES_B.ID%TYPE) is
6616: SELECT cle.id new_cle_id
6617: FROM okc_k_lines_b cle
6618: ,okc_k_lines_b ole
6619: WHERE cle.dnz_chr_id=cp_rbk_chr_id
6620: and ole.id = cp_ole_id
6621: and ole.lse_id = cle.lse_id
6622: and ole.id=cle.orig_system_id1;
6659: CURSOR check_accrual_previous_csr IS
6660: SELECT NVL(CHK_ACCRUAL_PREVIOUS_MNTH_YN,'N')
6661: FROM OKL_SYSTEM_PARAMS_ALL;
6662:
6663: CURSOR chk_curr_terminated_line(p_kle_id IN OKC_K_LINES_B.ID%TYPE) IS
6664: SELECT 'Y'
6665: FROM okc_k_headers_b CHR,
6666: okl_trx_contracts ktrx,
6667: okl_trx_contracts trx,
7121: FROM okl_sif_ret_levels lev,
7122: okl_sif_rets ret,
7123: okl_stream_interfaces sif,
7124: okl_sif_lines line,
7125: okc_k_lines_b cle,
7126: okl_k_lines kle,
7127: okc_line_styles_b lse,
7128: okl_sif_fees fee
7129: WHERE lev.sir_id = ret.id
8090: -- get the Contract line info
8091: CURSOR l_line_rec_csr(chrid NUMBER)
8092: IS
8093: SELECT cle.id
8094: FROM okc_k_lines_b cle,
8095: okc_line_styles_b lse,
8096: okc_statuses_b sts
8097: WHERE cle.dnz_chr_id = chrid
8098: AND cle.lse_id = lse.id
8104: -- Get all valid fee lines
8105: CURSOR l_fee_type_csr ( chrId NUMBER, feeId NUMBER ) IS
8106: SELECT kle.fee_type
8107: FROM okl_k_lines kle,
8108: okc_k_lines_b cle,
8109: okc_line_styles_b lse,
8110: okc_statuses_b sts
8111: WHERE cle.lse_id = lse.id
8112: AND lse.lty_code = 'FEE'
8121: -- To check if the chr_id has Financed fee feetype defined
8122: CURSOR l_fin_type_csr (chrId NUMBER)
8123: IS
8124: SELECT cle.id fin_id
8125: FROM okc_k_lines_b cle,
8126: okc_line_styles_b lse,
8127: okl_k_lines kle,
8128: okc_statuses_b sts
8129: WHERE cle.dnz_chr_id = chrId
8137: -- Code required for rollover quote- HKPATEL
8138: CURSOR l_rollover_csr(chrId NUMBER)
8139: IS
8140: SELECT cle.id fin_id
8141: FROM okc_k_lines_b cle,
8142: okc_line_styles_b lse,
8143: okl_k_lines kle,
8144: okc_statuses_b sts
8145: WHERE cle.dnz_chr_id = chrId
8152:
8153: CURSOR l_link_rollover_csr(cleId NUMBER)
8154: IS
8155: SELECT okc.id kle_id
8156: FROM okc_k_lines_b okc,
8157: okl_k_lines kle ,
8158: okc_line_styles_b lse
8159: WHERE okc.cle_id = cleId
8160: AND okc.lse_id = lse.id
8164:
8165: -- To get the Subsidy Income Accural stream type id
8166: -- and also fetch the sub line id
8167: /*
8168: CURSOR get_sub_line_csr (p_dnz_chr_id okc_k_lines_b.dnz_chr_id%TYPE)
8169: IS
8170: SELECT TO_NUMBER(sgn.value) sty_id,
8171: cle.id
8172: FROM okl_sgn_translations sgn,
8174: okl_subsidies_v sub,
8175: okl_k_lines kle,
8176: okc_statuses_b sts,
8177: okc_line_styles_b lse,
8178: okc_k_lines_b cle
8179: WHERE cle.dnz_chr_id = p_dnz_chr_id
8180: AND cle.lse_id = lse.id
8181: AND lse.lty_code = 'SUBSIDY'
8182: AND sts.code = cle.sts_code
8186: AND sgn.object1_id1 = TO_CHAR(sty.id)
8187: AND sgn.jtot_object1_code = 'OKL_STRMTYP'
8188: AND kle.subsidy_id = sub.id;
8189: */
8190: CURSOR get_sub_line_csr(p_dnz_chr_id IN okc_k_lines_b.dnz_chr_id%TYPE)
8191: IS
8192: SELECT kle.sty_id,
8193: cle.id
8194: --srsreeni Bug5699923 Start
8194: --srsreeni Bug5699923 Start
8195: ,sts.ste_code
8196: --srsreeni Bug5699923 End
8197: FROM okl_k_lines kle,
8198: okc_k_lines_b cle,
8199: okl_subsidies_v sub,
8200: okc_statuses_b sts,
8201: okc_line_styles_b lse
8202: WHERE cle.dnz_chr_id = p_dnz_chr_id
8883: l_csm_one_off_fee_tbl okl_create_streams_pub.csm_one_off_fee_tbl_type := px_csm_one_off_fee_tbl;
8884: i BINARY_INTEGER := 0;
8885: lv_acc_mtd_code okl_subsidies_b.accounting_method_code%TYPE := NULL;
8886: lv_strm_desc VARCHAR2(200);
8887: ln_sub_id okc_k_lines_b.id%TYPE := NULL;
8888: ln_dummy NUMBER := 0;
8889: ln_cap_wos okl_k_lines.capital_amount%TYPE := 0;
8890: ln_cap_amt okl_k_lines.capital_amount%TYPE := 0;
8891: ln_cap_dis okl_k_lines.capital_amount%TYPE := 0;
8893: l_sty_id NUMBER;
8894: l_dep_sty_id NUMBER;
8895: l_return_status VARCHAR2(1);
8896: -- get Asset lines for the contract
8897: CURSOR get_asset_lines_csr (p_chr_id okc_k_lines_b.id%TYPE)
8898: IS
8899: SELECT cle.id,
8900: NVL(kle.date_funding,cle.start_date) date_funding
8901: FROM okc_k_lines_b cle,
8897: CURSOR get_asset_lines_csr (p_chr_id okc_k_lines_b.id%TYPE)
8898: IS
8899: SELECT cle.id,
8900: NVL(kle.date_funding,cle.start_date) date_funding
8901: FROM okc_k_lines_b cle,
8902: okl_k_lines kle,
8903: okc_line_styles_b lse
8904: WHERE cle.dnz_chr_id = p_chr_id
8905: AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED')
8911: IS
8912: SELECT subb.accounting_method_code,
8913: cle.id
8914: FROM okl_subsidies_b subb,
8915: okc_k_lines_b cle_fin,
8916: okc_line_styles_b lse_fin,
8917: okc_k_lines_b cle,
8918: okl_k_lines kle,
8919: okc_line_styles_b lse
8913: cle.id
8914: FROM okl_subsidies_b subb,
8915: okc_k_lines_b cle_fin,
8916: okc_line_styles_b lse_fin,
8917: okc_k_lines_b cle,
8918: okl_k_lines kle,
8919: okc_line_styles_b lse
8920: WHERE cle.cle_id = p_line_id
8921: AND cle.id = kle.id
8925: AND cle_fin.id = cle.cle_id
8926: AND cle_fin.lse_id = lse_fin.id
8927: AND lse_fin.lty_code = 'FREE_FORM1';
8928: -- Validate the contract has Subsidy line
8929: CURSOR validate_sub_line (p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
8930: IS
8931: SELECT 1
8932: FROM dual
8933: WHERE EXISTS (SELECT '1'
8931: SELECT 1
8932: FROM dual
8933: WHERE EXISTS (SELECT '1'
8934: FROM okl_subsidies_b subb,
8935: okc_k_lines_b cle_fin,
8936: okc_line_styles_b lse_fin,
8937: okc_k_lines_b cle,
8938: okl_k_lines kle,
8939: okc_line_styles_b lse
8933: WHERE EXISTS (SELECT '1'
8934: FROM okl_subsidies_b subb,
8935: okc_k_lines_b cle_fin,
8936: okc_line_styles_b lse_fin,
8937: okc_k_lines_b cle,
8938: okl_k_lines kle,
8939: okc_line_styles_b lse
8940: WHERE cle.dnz_chr_id = p_dnz_chr_id
8941: AND cle.cle_id = cle_fin.id
8948: AND cle_fin.lse_id = lse_fin.id
8949: AND lse_fin.lty_code = 'FREE_FORM1');
8950: -- To get the Subsidy Income Accural Description
8951: -- Changed for bug 4044089 by HKPATEL 12/02/2004
8952: CURSOR get_sub_income_strm_csr (p_dnz_chr_id IN okc_k_lines_b.dnz_chr_id%TYPE,
8953: p_sub_cle_id IN okc_k_lines_b.id%TYPE)
8954: IS
8955:
8956: SELECT kle.sty_id
8949: AND lse_fin.lty_code = 'FREE_FORM1');
8950: -- To get the Subsidy Income Accural Description
8951: -- Changed for bug 4044089 by HKPATEL 12/02/2004
8952: CURSOR get_sub_income_strm_csr (p_dnz_chr_id IN okc_k_lines_b.dnz_chr_id%TYPE,
8953: p_sub_cle_id IN okc_k_lines_b.id%TYPE)
8954: IS
8955:
8956: SELECT kle.sty_id
8957: FROM okl_k_lines kle,
8954: IS
8955:
8956: SELECT kle.sty_id
8957: FROM okl_k_lines kle,
8958: okc_k_lines_b cle,
8959: okc_line_styles_b lse,
8960: okl_subsidies_v sub
8961:
8962: WHERE cle.dnz_chr_id = p_dnz_chr_id
8972: okl_strm_type_b sty,
8973: okl_subsidies_v sub,
8974: okl_k_lines kle,
8975: okc_line_styles_b lse,
8976: okc_k_lines_b cle
8977: WHERE cle.dnz_chr_id = p_dnz_chr_id
8978: AND cle.id = p_sub_cle_id
8979: AND cle.lse_id = lse.id
8980: AND lse.lty_code = 'SUBSIDY'
9502: lse.lty_code lty_code
9503: -- srsreeni - Bug#5699923 - Added - Start
9504: , sts.ste_code
9505: -- srsreeni - Bug#5699923 - Added - End
9506: FROM okc_k_lines_b okc,
9507: okl_k_lines kle ,
9508: okc_line_styles_b lse
9509: ,okc_statuses_b sts
9510: WHERE okc.cle_id = cleId
9524: kle.fee_purpose_code fee_purpose_code,
9525: styt.id sty_id
9526: FROM okl_strm_type_tl styt,
9527: okc_k_items cim,
9528: okc_k_lines_b cle,
9529: okl_k_lines kle
9530: WHERE cle.dnz_chr_id = p_chr_d
9531: AND cle.id = p_cle_id
9532: AND cle.id = kle.id
17516: IS
17517: SELECT 1
17518: FROM dual
17519: WHERE EXISTS (SELECT 1
17520: FROM okc_k_lines_b cle,
17521: okc_line_styles_b lse,
17522: okl_k_lines kle
17523: WHERE cle.dnz_chr_id = chrId
17524: AND cle.lse_id = lse.id
17583: hz_party_sites hps,
17584: hz_party_site_uses hpsu,
17585: okl_txl_itm_insts iti,
17586: okc_line_styles_b lse_ib,
17587: okc_k_lines_b cle_ib
17588: WHERE cle_ib.dnz_chr_id = chrId
17589: AND cle_ib.lse_id = lse_ib.id
17590: AND lse_ib.lty_code = 'INST_ITEM'
17591: AND iti.kle_id = cle_ib.id
17649: CURSOR get_dis_amt_csr_lease(p_chr_id NUMBER, p_cle_id NUMBER)
17650: IS
17651: SELECT kle_sub.amount, kle_sub.SUBSIDY_OVERRIDE_AMOUNT
17652: FROM okl_subsidies_b subb,
17653: okc_k_lines_b cle_fin,
17654: okc_line_styles_b lse_fin,
17655: okc_k_lines_b cle_sub,
17656: okl_k_lines kle_sub,
17657: okc_line_styles_b lse_sub
17651: SELECT kle_sub.amount, kle_sub.SUBSIDY_OVERRIDE_AMOUNT
17652: FROM okl_subsidies_b subb,
17653: okc_k_lines_b cle_fin,
17654: okc_line_styles_b lse_fin,
17655: okc_k_lines_b cle_sub,
17656: okl_k_lines kle_sub,
17657: okc_line_styles_b lse_sub
17658: WHERE cle_fin.id = p_cle_id
17659: AND cle_fin.dnz_chr_id = p_chr_id
17829: l_days_in_yr_code OKL_ST_GEN_TMPT_SETS.days_in_yr_code%type;
17830:
17831: -- Start fix for bug 6858487
17832: l_orig_chr_id OKC_K_HEADERS_B.ID%TYPE;
17833: l_orig_cle_id OKC_K_LINES_B.ID%TYPE;
17834:
17835: -- Get Line ID in rebook copy that was copied from LINE the original contract
17836: -- cp_rbk_chr_id is the rebook copy contract
17837: -- cp_ole_id is original contract line ID
17835: -- Get Line ID in rebook copy that was copied from LINE the original contract
17836: -- cp_rbk_chr_id is the rebook copy contract
17837: -- cp_ole_id is original contract line ID
17838: CURSOR c_get_orig_line_id (cp_rbk_chr_id IN okc_k_headers_b.id%type
17839: , cp_ole_id IN OKC_K_LINES_B.ID%TYPE) is
17840: SELECT --cle.id new_cle_id
17841: cle.orig_system_id1
17842: FROM okc_k_lines_b cle
17843: ,okc_k_lines_b ole
17838: CURSOR c_get_orig_line_id (cp_rbk_chr_id IN okc_k_headers_b.id%type
17839: , cp_ole_id IN OKC_K_LINES_B.ID%TYPE) is
17840: SELECT --cle.id new_cle_id
17841: cle.orig_system_id1
17842: FROM okc_k_lines_b cle
17843: ,okc_k_lines_b ole
17844: WHERE cle.dnz_chr_id=cp_rbk_chr_id
17845: and cle.id = cp_ole_id
17846: and ole.lse_id = cle.lse_id
17839: , cp_ole_id IN OKC_K_LINES_B.ID%TYPE) is
17840: SELECT --cle.id new_cle_id
17841: cle.orig_system_id1
17842: FROM okc_k_lines_b cle
17843: ,okc_k_lines_b ole
17844: WHERE cle.dnz_chr_id=cp_rbk_chr_id
17845: and cle.id = cp_ole_id
17846: and ole.lse_id = cle.lse_id
17847: and ole.id=cle.orig_system_id1;
20804: -- Get Passthrough Fee Lines
20805: CURSOR c_get_psth_csr(p_khr_id okl_k_headers.id%TYPE)
20806: IS
20807: SELECT cle.id
20808: FROM okc_k_lines_b cle,
20809: okl_k_lines kle,
20810: okc_line_styles_b lse
20811: WHERE cle.dnz_chr_id = p_khr_id
20812: AND cle.lse_id = lse.id
20833: -- Get all the Top lines for p_khr_id
20834: CURSOR c_get_all_lines_csr(p_khr_id okl_k_headers.id%TYPE)
20835: IS
20836: SELECT cle.id line_id
20837: FROM okc_k_lines_b cle,
20838: okc_line_styles_b lse
20839: WHERE cle.dnz_chr_id = p_khr_id
20840: AND cle.lse_id = lse.id
20841: AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED')
21403: cle_so.START_DATE
21404: FROM okc_k_headers_b chr_rv,
21405: okc_line_styles_b lse_rv,
21406: okl_k_lines kle_rv,
21407: okc_k_lines_b cle_rv,
21408: okc_rules_b rul_rv,
21409: okc_rule_groups_b rgp_rv,
21410: okc_line_styles_b lse_so,
21411: okc_k_lines_b cle_so,
21407: okc_k_lines_b cle_rv,
21408: okc_rules_b rul_rv,
21409: okc_rule_groups_b rgp_rv,
21410: okc_line_styles_b lse_so,
21411: okc_k_lines_b cle_so,
21412: okc_rules_b rul_so,
21413: okc_rule_groups_b rgp_so
21414: WHERE rgp_so.cle_id = p_kle_id
21415: AND rgp_so.dnz_chr_id = p_khr_id
21526: cle_so.start_date
21527: FROM okc_k_headers_b chr_rv,
21528: okc_line_styles_b lse_rv,
21529: okl_k_lines kle_rv,
21530: okc_k_lines_b cle_rv,
21531: okc_rules_b rul_rv,
21532: okc_rule_groups_b rgp_rv,
21533: okc_line_styles_b lse_so,
21534: okc_k_lines_b cle_so,
21530: okc_k_lines_b cle_rv,
21531: okc_rules_b rul_rv,
21532: okc_rule_groups_b rgp_rv,
21533: okc_line_styles_b lse_so,
21534: okc_k_lines_b cle_so,
21535: okc_rules_b rul_so,
21536: okc_rule_groups_b rgp_so
21537: WHERE rgp_so.cle_id = p_kle_id
21538: AND rgp_so.dnz_chr_id = p_khr_id
21806: PROCEDURE extract_params_so(
21807: p_api_version IN NUMBER,
21808: p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
21809: p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
21810: p_cle_id IN OKC_K_LINES_B.ID%TYPE,
21811: x_return_status OUT NOCOPY VARCHAR2,
21812: x_msg_count OUT NOCOPY NUMBER,
21813: x_msg_data OUT NOCOPY VARCHAR2,
21814: x_csm_lease_header OUT NOCOPY okl_create_streams_pub.csm_lease_rec_type,
21917: hz_party_sites hps,
21918: hz_party_site_uses hpsu,
21919: okl_txl_itm_insts iti,
21920: okc_line_styles_b lse_ib,
21921: okc_k_lines_b cle_ib
21922: WHERE cle_ib.dnz_chr_id = chrId
21923: AND cle_ib.lse_id = lse_ib.id
21924: AND lse_ib.lty_code = 'INST_ITEM'
21925: AND iti.kle_id = cle_ib.id
22134: IS
22135: SELECT 1
22136: FROM dual
22137: WHERE EXISTS (SELECT 1
22138: FROM okc_k_lines_b cle,
22139: okc_line_styles_b lse,
22140: okl_k_lines kle
22141: WHERE cle.dnz_chr_id = chrId
22142: AND cle.lse_id = lse.id
22155: CURSOR get_cap_fee_amt(p_chr_id okc_k_headers_b.id%TYPE)
22156: IS
22157: SELECT NVL(kle.amount, 0) total_cap_fee
22158: FROM okl_k_lines kle,
22159: okc_k_lines_b cle,
22160: okc_k_items cim,
22161: okl_strm_type_b sty
22162: WHERE cle.dnz_chr_id = p_chr_id
22163: AND cle.id = kle.id
22172: -- Start of BAKUCHIB modification bug#3121708
22173: -- Get diff in days between contract end_date
22174: -- and max payment leve(LASLL) end_date
22175: CURSOR c_diff_days_csr(p_chr_id okc_k_headers_b.id%TYPE,
22176: p_cle_id okc_k_lines_b.id%TYPE,
22177: p_slh_id okc_rule_groups_b.id%TYPE)
22178: IS
22179: SELECT chrv.end_date chr_end_date,
22180: MAX(FND_DATE.canonical_to_date(sll.rule_information2)) pay_start_date,