[Home] [Help]
13: select 'T' flag
14: from dual where exists
15: (
16: select 1
17: from okc_k_lines_b kle,
18: okc_line_styles_b lse,
19: okc_statuses_b sts
20: where KLE.LSE_ID = LSE.ID
21: and lse.lty_code = 'SOLD_SERVICE'
777: FND_NUMBER.canonical_to_number(sll.rule_information6) amount,
778: TO_NUMBER(sll.rule_information7) stub_days,
779: TO_NUMBER(sll.rule_information8) stub_amount
780: FROM okc_k_headers_b chr_so,
781: okc_k_lines_b cle,
782: okl_k_lines kle,
783: okc_line_styles_b lse,
784: okc_rules_b sll,
785: okc_rules_b slh,
810: to_char(cle.id) kleId,
811: kle.amount fee_amount,
812: cle.start_date fee_start_date
813: FROM okc_k_headers_b chr_so,
814: okc_k_lines_b cle,
815: okl_k_lines kle,
816: okc_line_styles_b lse
817: WHERE chr_so.id = p_khr_id
818: and cle.sts_code in( 'COMPLETE', 'INCOMPLETE')--'ENTERED'
3565: SELECT COUNT(stm.id),
3566: stm.kle_id,
3567: stm.sty_id
3568: FROM okl_streams stm,
3569: okc_k_lines_b cle,
3570: okc_line_styles_b lse
3571: WHERE stm.khr_id = p_khr_id
3572: AND stm.say_code = 'WORK'
3573: AND NVL(stm.purpose_code, '-99') = p_purpose_code
4048: DECODE(rul2.rule_information10, 'Y', 'ARREARS', 'ADVANCE') advance_arrears
4049: FROM okc_rule_groups_b rgp,
4050: okc_rules_b rul1,
4051: okc_rules_b rul2,
4052: okc_k_lines_b cle,
4053: okl_k_lines kle,
4054: okc_line_styles_b lse,
4055: okl_strm_type_b sty
4056: WHERE rul1.rgp_id= rgp.id
4107: DECODE(sll.object1_id1, 'M', 12, 'Q', 4, 'S', 2, 'A', 1) periods_per_year
4108: FROM okl_streams stm,
4109: okl_strm_type_b sty,
4110: okl_strm_elements sel,
4111: okc_k_lines_b cle,
4112: okc_line_styles_b lse,
4113: okc_rules_b sll,
4114: okc_rules_b slh,
4115: okc_rule_groups_b rgp
5248: kle.fee_type fee_type
5249: FROM okc_rule_groups_b rgp,
5250: okc_rules_b rul1,
5251: okc_rules_b rul2,
5252: okc_k_lines_b cle,
5253: okl_k_lines kle,
5254: okc_line_styles_b lse
5255: WHERE
5256: rul2.dnz_chr_id = p_khr_id
5296: CURSOR c_fin_assets IS
5297: SELECT kle.id,
5298: NVL(kle.residual_value, 0) residual_value,
5299: cle.start_date
5300: FROM okc_k_lines_b cle,
5301: okl_k_lines kle,
5302: okc_line_styles_b lse
5303: WHERE cle.chr_id = p_khr_id
5304: AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED')
5893: CURSOR c_assets IS
5894: SELECT cle.id cle_id,
5895: cle.start_date start_date,
5896: NVL(cle.date_terminated, cle.end_date) end_date
5897: FROM okc_k_lines_b cle,
5898: okc_line_styles_b lse
5899: WHERE cle.dnz_chr_id = p_khr_id
5900: AND cle.sts_code IN ('PASSED', 'COMPLETE')
5901: -- Removing 'TERMINATED' Status Lines as Partial Terminated Asset Accruals
5913: TO_NUMBER(rul2.rule_information8) stub_amount
5914: FROM okc_rule_groups_b rgp,
5915: okc_rules_b rul1,
5916: okc_rules_b rul2,
5917: okc_k_lines_b cle,
5918: okl_strm_type_b sty
5919: WHERE rul2.dnz_chr_id = p_khr_id
5920: AND rul2.rule_information_category = 'LASLL'
5921: AND rul2.rgp_id = rgp.id
6203: l_strm_index NUMBER;
6204: l_sel_index NUMBER;
6205: l_prev_stm_id OKL_STREAMS.ID%TYPE;
6206: l_orig_chr_id OKC_K_HEADERS_B.ID%TYPE;
6207: l_new_stm_cle_id OKC_K_LINES_B.ID%TYPE;
6208:
6209: -- Declare records/table types
6210: l_stmv_tbl okl_streams_pub.stmv_tbl_type;
6211: lx_stmv_tbl okl_streams_pub.stmv_tbl_type;
6220: , lse.lty_code line_type -- 'FREE_FORM1'
6221: , cle.id kle_id
6222: , NULL fee_id
6223: , cle.date_terminated date_terminated
6224: FROM okc_k_lines_b cle
6225: , okc_line_styles_b lse
6226: , okc_statuses_b sts
6227: WHERE cle.dnz_chr_id = cp_chr_id
6228: AND lse.id = cle.lse_id
6235: , lnk_lse.lty_code line_type -- 'LINK_FEE_ASSET'
6236: , lnk_cle.id kle_id
6237: , fee_cle.id fee_id
6238: , lnk_cle.date_terminated date_terminated
6239: FROM okc_k_lines_b lnk_cle
6240: , okl_k_lines fee_kle
6241: , okc_k_lines_b fee_cle
6242: , okc_line_styles_b lnk_lse
6243: , okc_statuses_b sts
6237: , fee_cle.id fee_id
6238: , lnk_cle.date_terminated date_terminated
6239: FROM okc_k_lines_b lnk_cle
6240: , okl_k_lines fee_kle
6241: , okc_k_lines_b fee_cle
6242: , okc_line_styles_b lnk_lse
6243: , okc_statuses_b sts
6244: WHERE lnk_cle.dnz_chr_id = cp_chr_id
6245: AND fee_cle.dnz_chr_id = cp_chr_id
6256: , cle1.id kle_id
6257: , NULL fee_id
6258: , cle1.date_terminated date_terminated
6259: FROM okl_streams stm
6260: , okc_k_lines_b cle1
6261: , okc_line_styles_b lse1
6262: , okc_statuses_b sts1
6263: WHERE stm.khr_id = cp_chr_id
6264: AND stm.say_code = 'CURR'
6273: -- Cursor to get Current Streams and elements for a particular line of a contract
6274: -- Added ISG generated stream type purposes so that these streams will not be
6275: -- copied over during mass/online rebook. Streams will be regenerated by ISG
6276: CURSOR c_streams (cp_chr_id OKC_K_HEADERS_B.ID%TYPE
6277: , cp_cle_id OKC_K_LINES_B.ID%TYPE
6278: , cp_date_terminated DATE
6279: , cp_purpose_code okl_streams.purpose_code%TYPE) IS
6280: SELECT STM.ID STREAM_ID
6281: , STM.STY_ID
6357: -- Get Line ID in rebook copy that was copied from LINE the original contract
6358: -- cp_rbk_chr_id is the rebook copy contract
6359: -- cp_ole_id is original contract line ID
6360: CURSOR c_get_orig_line_id (cp_rbk_chr_id IN okc_k_headers_b.id%type
6361: , cp_ole_id IN OKC_K_LINES_B.ID%TYPE) is
6362: SELECT cle.id new_cle_id
6363: FROM okc_k_lines_b cle
6364: ,okc_k_lines_b ole
6365: WHERE cle.dnz_chr_id=cp_rbk_chr_id
6359: -- cp_ole_id is original contract line ID
6360: CURSOR c_get_orig_line_id (cp_rbk_chr_id IN okc_k_headers_b.id%type
6361: , cp_ole_id IN OKC_K_LINES_B.ID%TYPE) is
6362: SELECT cle.id new_cle_id
6363: FROM okc_k_lines_b cle
6364: ,okc_k_lines_b ole
6365: WHERE cle.dnz_chr_id=cp_rbk_chr_id
6366: and ole.id = cp_ole_id
6367: and ole.lse_id = cle.lse_id
6360: CURSOR c_get_orig_line_id (cp_rbk_chr_id IN okc_k_headers_b.id%type
6361: , cp_ole_id IN OKC_K_LINES_B.ID%TYPE) is
6362: SELECT cle.id new_cle_id
6363: FROM okc_k_lines_b cle
6364: ,okc_k_lines_b ole
6365: WHERE cle.dnz_chr_id=cp_rbk_chr_id
6366: and ole.id = cp_ole_id
6367: and ole.lse_id = cle.lse_id
6368: and ole.id=cle.orig_system_id1;
6417: CURSOR check_accrual_previous_csr IS
6418: SELECT NVL(CHK_ACCRUAL_PREVIOUS_MNTH_YN,'N')
6419: FROM OKL_SYSTEM_PARAMS;
6420:
6421: CURSOR chk_curr_terminated_line(p_kle_id IN OKC_K_LINES_B.ID%TYPE)
6422: IS
6423: SELECT 'Y'
6424: FROM okc_k_headers_b CHR,
6425: okl_trx_contracts ktrx,
6966: p_kle_id IN NUMBER )
6967: IS
6968: SELECT cle.orig_system_id1 orig_kle_id
6969: ,chr.orig_system_id1 orig_khr_id
6970: FROM okc_k_lines_b cle
6971: ,okc_k_headers_b chr
6972: WHERE cle.id = p_kle_id
6973: AND cle.dnz_chr_id = chr.id
6974: AND cle.orig_system_id1 IS NOT NULL;
7039: ) - 1 ) end_date
7040: FROM okc_rules_b rul,
7041: okc_rules_b rul2,
7042: okc_rule_groups_b rgp,
7043: okc_k_lines_b cle,
7044: okl_k_lines kle
7045: WHERE cle.id = p_kle_id
7046: AND cle.sts_code IN ('NEW', 'INCOMPLETE', 'PASSED', 'COMPLETE')
7047: AND kle.id = cle.id
8035: TO_NUMBER(rul2.rule_information8) stub_amount
8036: FROM okc_rule_groups_b rgp,
8037: okc_rules_b rul1,
8038: okc_rules_b rul2,
8039: okc_k_lines_b cle,
8040: okl_k_lines kle,
8041: okc_line_styles_b lse,
8042: okl_strm_type_b sty
8043: WHERE rul2.dnz_chr_id = p_khr_id
8077: kle.fee_type fee_type,
8078: kle.origination_income origination_income,
8079: kle.amount fee_amount,
8080: 'INCOME_AMORT' fee_line_level_type
8081: FROM okc_k_lines_b cle,
8082: okl_k_lines kle,
8083: okc_line_styles_b lse,
8084: okl_strm_type_b sty,
8085: okc_rule_groups_b rgp,
8118: DECODE(kle.fee_type, 'INCOME', 'INCOME_ACCRUED', 'FEE_PAYMENT') fee_line_level_type
8119: FROM okc_rule_groups_b rgp,
8120: okc_rules_b rul1,
8121: okc_rules_b rul2,
8122: okc_k_lines_b cle,
8123: okl_k_lines kle,
8124: okc_line_styles_b lse,
8125: okl_strm_type_b sty
8126: WHERE
8183: lse.lty_code,
8184: kle.amount,
8185: kle.capital_amount,
8186: kle.capitalized_interest
8187: FROM okc_k_lines_b cle,
8188: okl_k_lines kle,
8189: okc_line_styles_b lse
8190: WHERE cle.dnz_chr_id = p_khr_id
8191: -- 12944979: For Terminated lines, copy_accrual_streams_isg will syncup
8223: Select nvl(slh.object1_id1, -1) styId
8224: From OKC_RULE_GROUPS_B crg,
8225: OKC_RULES_B crl,
8226: okc_rules_b slh,
8227: okc_K_lines_b cle_lnk,
8228: okl_K_lines kle_roll
8229: Where slh.rgp_id = crg.id
8230: and crg.RGD_CODE = 'LALEVL'
8231: and crl.RULE_INFORMATION_CATEGORY = 'LASLL'
8257: CURSOR c_financed_fees IS
8258: SELECT kle.id,
8259: nvl(kle.fee_type, 'LINK_FEE_ASSET') fee_type,
8260: lse.lty_code
8261: FROM okc_k_lines_b cle,
8262: okl_k_lines kle,
8263: okc_line_styles_b lse
8264: WHERE cle.dnz_chr_id = p_khr_id
8265: AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED')
8469: FROM DUAL
8470: WHERE EXISTS
8471: (
8472: SELECT 1
8473: FROM okc_k_lines_b cle
8474: WHERE cle.dnz_chr_id = cp_khr_id
8475: -- 12944979: For Terminated lines, copy_accrual_streams_isg will syncup
8476: AND cle.sts_code = 'TERMINATED'
8477: );
13488: Select 'Y' What
13489: from dual where Exists(
13490: SELECT nvl(kle.fee_type, 'XYZ'),
13491: nvl(lse.lty_code, 'XYZ')
13492: FROM okc_k_lines_b cle,
13493: okl_k_lines kle,
13494: okc_line_styles_b lse
13495: WHERE cle.dnz_chr_id = p_khr_id
13496: AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED')
14075: kle.fee_type fee_type
14076: FROM okc_rule_groups_b rgp,
14077: okc_rules_b rul1,
14078: okc_rules_b rul2,
14079: okc_k_lines_b cle,
14080: okl_k_lines kle,
14081: okc_line_styles_b lse
14082: WHERE
14083: rul2.dnz_chr_id = p_khr_id
14305: kle.fee_type fee_type
14306: FROM okc_rule_groups_b rgp,
14307: okc_rules_b rul1,
14308: okc_rules_b rul2,
14309: okc_k_lines_b cle,
14310: okl_k_lines kle,
14311: okc_line_styles_b lse,
14312: okl_strm_type_b sty
14313: WHERE