[Home] [Help]
1075: rulb2.RULE_INFORMATION7 stub_days,
1076: rulb2.RULE_INFORMATION8 stub_amount,
1077: rulb2.RULE_INFORMATION10 arrear_yn
1078: FROM okc_k_lines_b cleb,
1079: okc_rule_groups_b rgpb,
1080: okc_rules_b rulb,
1081: okc_rules_b rulb2,
1082: okl_strm_type_b styb
1083: WHERE rgpb.chr_id IS NULL
1360: l_stream_type_name OKL_STRM_TYPE_V.NAME%TYPE := NULL;
1361:
1362: --Added OKL_STRM_TYPE_V and stty.name by bkatraga for bug 13478101
1363: CURSOR l_pmnt_strm_hdr(
1364: rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
1365: rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
1366: chrId NUMBER) IS
1367: SELECT crg.cle_id,
1368: crl.id,
1376: crl.RULE_INFORMATION6,
1377: crl.RULE_INFORMATION10,
1378: crl.RULE_INFORMATION11,
1379: stty.name
1380: FROM OKC_RULE_GROUPS_B crg,
1381: OKC_RULES_B crl,
1382: OKL_STRM_TYPE_V stty
1383: WHERE stty.id = crl.object1_id1
1384: AND stty.stream_type_purpose IN (
1392:
1393: l_pmnt_strm_hdr_rec l_pmnt_strm_hdr%ROWTYPE;
1394:
1395: CURSOR l_pmnt_strm_check(
1396: rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
1397: rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
1398: pmnt_strm_purpose OKL_STRM_TYPE_B.STREAM_TYPE_PURPOSE%TYPE,
1399: chrId NUMBER) IS
1400: SELECT crg.cle_id,
1407: crl.RULE_INFORMATION5,
1408: crl.RULE_INFORMATION6,
1409: crl.RULE_INFORMATION10,
1410: crl.RULE_INFORMATION11
1411: FROM OKC_RULE_GROUPS_B crg,
1412: OKC_RULES_B crl,
1413: OKL_STRM_TYPE_B stty
1414: WHERE stty.id = crl.object1_id1
1415: AND stty.stream_type_purpose = pmnt_strm_purpose
1418: AND crl.RULE_INFORMATION_CATEGORY = rlcat
1419: AND crg.dnz_chr_id = chrId;
1420:
1421: CURSOR l_pmnt_strm_check2(
1422: rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
1423: rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
1424: pmnt_strm_purpose OKL_STRM_TYPE_B.STREAM_TYPE_PURPOSE%TYPE,
1425: chrId NUMBER) IS
1426: SELECT crg.cle_id,
1433: crl.RULE_INFORMATION5,
1434: crl.RULE_INFORMATION6,
1435: crl.RULE_INFORMATION10,
1436: crl.RULE_INFORMATION11
1437: FROM OKC_RULE_GROUPS_B crg,
1438: OKC_RULES_B crl,
1439: OKL_STRM_TYPE_B stty
1440: WHERE stty.id = crl.object1_id1
1441: AND stty.stream_type_purpose = pmnt_strm_purpose
1523:
1524: CURSOR rate_delay_csr(p_id NUMBER) IS
1525: SELECT TO_NUMBER(RULE_INFORMATION3)
1526: FROM OKC_RULES_B RULE,
1527: OKC_RULE_GROUPS_B RGP
1528: WHERE RGP.ID = RULE.RGP_ID
1529: AND RGP.DNZ_CHR_ID = p_id
1530: AND RGD_CODE = 'LABILL'
1531: AND RULE_INFORMATION_CATEGORY = 'LAINVD';
2626: calc_meth VARCHAR2(256);
2627: l_deal_type VARCHAR2(256);
2628:
2629: --Bug#3931587
2630: CURSOR l_pmnt_strm_check(rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
2631: rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
2632: --pmnt_strm_name OKL_STRMTYP_SOURCE_V.NAME%TYPE,
2633: pmnt_strm_purpose OKL_STRM_TYPE_B.STREAM_TYPE_PURPOSE%TYPE,
2634: chrId NUMBER) IS
2642: crl.RULE_INFORMATION5,
2643: crl.RULE_INFORMATION6,
2644: crl.RULE_INFORMATION10,
2645: crl.RULE_INFORMATION11
2646: FROM OKC_RULE_GROUPS_B crg,
2647: OKC_RULES_B crl,
2648: OKL_STRM_TYPE_B stty
2649: WHERE stty.id = crl.object1_id1
2650: --and stty.code = pmnt_strm_name
3373: TO_NUMBER(sll.rule_information7) - 1) end_date,
3374: TO_NUMBER(sll.rule_information6) amount
3375: FROM okc_rules_b sll,
3376: okc_rules_b slh,
3377: okc_rule_groups_b rgp
3378: WHERE rgp.dnz_chr_id = p_chr_id
3379: AND rgp.cle_id = p_cle_id
3380: AND rgp.rgd_code = 'LALEVL'
3381: AND slh.rgp_id = rgp.id
3491: style.name line_style
3492: FROM okl_k_lines_full_v line,
3493: okc_line_styles_v style,
3494: okc_statuses_v sts,
3495: okc_rule_groups_b rgp,
3496: okc_rules_b rule
3497: WHERE rgp.id = rule.rgp_id
3498: AND line.id = rgp.cle_id
3499: AND line.lse_id = style.id
3509: -- Added cursor - bug# 8399461
3510: --Cursor to get the fee payment frequency
3511: CURSOR c_feepayment_freq(khrId NUMBER, cleId NUMBER) IS
3512: SELECT rul.object1_id1 frequency
3513: FROM okc_rule_groups_b rgp,
3514: okc_rules_b rul
3515: WHERE rgp.dnz_chr_id = khrId
3516: AND rgp.cle_id = cleId
3517: AND rgp.RGD_CODE = 'LALEVL'
4169: AND kle.dnz_chr_id = chrid
4170: AND sts.code = kle.sts_code
4171: AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
4172:
4173: CURSOR l_assetrl_csr( rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
4174: rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
4175: chrId NUMBER,
4176: cleId NUMBER) IS
4177: SELECT crl.RULE_INFORMATION1,
4175: chrId NUMBER,
4176: cleId NUMBER) IS
4177: SELECT crl.RULE_INFORMATION1,
4178: crl.RULE_INFORMATION3
4179: FROM OKC_RULE_GROUPS_B crg,
4180: OKC_RULES_B crl
4181: WHERE crl.rgp_id = crg.id
4182: AND crg.RGD_CODE = rgcode
4183: AND crl.RULE_INFORMATION_CATEGORY = rlcat
4183: AND crl.RULE_INFORMATION_CATEGORY = rlcat
4184: AND crg.dnz_chr_id = chrId
4185: AND crg.cle_id = cleId;
4186:
4187: CURSOR l_strm_asset_csr(rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
4188: rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
4189: pmnt_strm_purpose OKL_STRM_TYPE_B.STREAM_TYPE_PURPOSE%TYPE,
4190: chrId NUMBER,
4191: cleId NUMBER) IS
4190: chrId NUMBER,
4191: cleId NUMBER) IS
4192: SELECT crl.id,
4193: crl.object1_id1
4194: FROM OKC_RULE_GROUPS_B crg,
4195: OKC_RULES_B crl,
4196: OKL_STRM_TYPE_B stty
4197: WHERE stty.id = TO_NUMBER(crl.object1_id1)
4198: AND stty.stream_type_purpose = pmnt_strm_purpose
4203: AND crg.dnz_chr_id = chrId;
4204:
4205: CURSOR est_head_csr (p_chr_id NUMBER) IS
4206: SELECT 'Y'
4207: FROM okc_rule_groups_b rgp,
4208: okc_rules_b rule,
4209: okl_strm_type_b stty
4210: WHERE rgp.id = rule.rgp_id
4211: AND stty.id = TO_NUMBER(rule.object1_id1)
4376: SELECT
4377: 'Y' only_stub,
4378: rule.object2_id1
4379: FROM okc_rules_b rule,
4380: okc_rule_groups_b rgp
4381: WHERE rgp.id = rule.rgp_id
4382: AND rgp.rgd_code = 'LALEVL'
4383: AND rgp.chr_id = p_chr_id
4384: AND rgp.dnz_chr_id = rgp.chr_id
4398: style.name,
4399: rule.object2_id1,
4400: 'Y' stub_only
4401: FROM okc_rules_b rule,
4402: okc_rule_groups_b rgp,
4403: okc_k_lines_b line,
4404: okc_line_styles_v style,
4405: okc_statuses_b sts
4406: WHERE rgp.id = rule.rgp_id
5157: AND to_number(SLH.OBJECT1_ID1) = STY.ID ) STREAM_TYPE
5158: --styt.name stream_type
5159: FROM okc_rules_b sll,
5160: okc_rules_b slh,
5161: okc_rule_groups_b rgp
5162: --okl_strm_type_b sty,
5163: --okl_strm_type_tl styt
5164: WHERE rgp.dnz_chr_id = p_khr_id
5165: AND rgp.cle_id = p_kle_id
5182: DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) mpp,
5183: styt.name stream_type
5184: FROM okc_rules_b sll,
5185: okc_rules_b slh,
5186: okc_rule_groups_b rgp,
5187: okl_strm_type_b sty,
5188: okl_strm_type_tl styt
5189: WHERE rgp.dnz_chr_id = p_khr_id
5190: AND rgp.cle_id = p_kle_id
5208: AND to_number(RULE.OBJECT1_ID1) = STY.ID ) STREAM_TYPE,
5209: rule.id rule_id,
5210: rgp.id rgp_id
5211: FROM okc_rules_b rule,
5212: okc_rule_groups_b rgp
5213: --okl_strm_type_b sty, Bug 4929573
5214: --okl_strm_type_tl styt
5215: WHERE rgp.cle_id = p_kle_id
5216: AND rgp.dnz_chr_id = p_khr_id
5221: --AND styt.LANGUAGE = USERENV('LANG')
5222: --AND sty.id = styt.id;
5223:
5224: CURSOR c_strm_sll_ext (p_rule_id OKC_RULES_B.ID%TYPE,
5225: p_rgp_id OKC_RULE_GROUPS_B.ID%TYPE) IS
5226: SELECT sll.rule_information2 start_date,
5227: sll.rule_information3 periods,
5228: sll.rule_information7 stub_day,
5229: DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) mpp
5258: p_kle_id OKC_K_LINES_B.ID%TYPE) IS
5259: SELECT COUNT(sll.id)
5260: FROM okc_rules_b sll,
5261: okc_rules_b slh,
5262: okc_rule_groups_b rgp,
5263: okl_strm_type_b sty,
5264: okl_strm_type_tl styt
5265: WHERE rgp.dnz_chr_id = p_khr_id
5266: AND rgp.cle_id = p_kle_id
5273: AND TO_CHAR(slh.id) = sll.object2_id1
5274: AND sll.rule_information_category = 'LASLL'; --| 17-Jan-06 cklee Fixed bug#4956483 |
5275:
5276: CURSOR c_strm_sll_ext_count (p_rule_id OKC_RULES_B.ID%TYPE,
5277: p_rgp_id OKC_RULE_GROUPS_B.ID%TYPE) IS
5278: SELECT COUNT(sll.id)
5279: FROM okc_rules_b sll
5280: WHERE sll.rgp_id = p_rgp_id
5281: AND sll.object2_id1 = TO_CHAR(p_rule_id)
6219: TO_NUMBER(SLL.rule_information3)*DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12)) - 1) end_date,
6220: styt.name stream_type
6221: FROM okc_rules_b sll,
6222: okc_rules_b slh,
6223: okc_rule_groups_b rgp,
6224: okl_strm_type_b sty,
6225: okl_strm_type_tl styt
6226: WHERE rgp.dnz_chr_id = chrID
6227: AND rgp.cle_id = feeLine
6242: feeLine IN OKL_K_LINES.ID%TYPE) IS
6243:
6244: SELECT slh.id
6245: FROM okc_rules_b slh,
6246: okc_rule_groups_b rgp,
6247: okl_strm_type_b sty,
6248: okl_strm_type_tl styt
6249: WHERE rgp.dnz_chr_id = chrID
6250: AND rgp.cle_id = feeLine
6662: TO_NUMBER(SLL.rule_information3)*DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12)) - 1) end_date,
6663: styt.name stream_type
6664: FROM okc_rules_b sll,
6665: okc_rules_b slh,
6666: okc_rule_groups_b rgp,
6667: okl_strm_type_b sty,
6668: okl_strm_type_tl styt
6669: WHERE rgp.dnz_chr_id = chrID
6670: AND rgp.cle_id = feeLine
6685: feeLine IN OKL_K_LINES.ID%TYPE) IS
6686:
6687: SELECT slh.id
6688: FROM okc_rules_b slh,
6689: okc_rule_groups_b rgp,
6690: okl_strm_type_b sty,
6691: okl_strm_type_tl styt
6692: WHERE rgp.dnz_chr_id = chrID
6693: AND rgp.cle_id = feeLine
7367: and deal_type = 'LOAN';
7368:
7369: CURSOR get_payment_lns (p_chr_id IN NUMBER) IS
7370: SELECT rgp.id,rgp.cle_id
7371: FROM OKC_RULE_GROUPS_B rgp,
7372: OKC_RULES_B crl2,
7373: okc_k_lines_b cleb,
7374: OKL_STRM_TYPE_B stty
7375: WHERE stty.id = crl2.object1_id1
7804: SELECT crg.cle_id,
7805: crl.id,
7806: crl.object1_id1,
7807: crl.RULE_INFORMATION6
7808: FROM OKC_RULE_GROUPS_B crg,
7809: OKC_RULES_B crl,
7810: OKL_STRM_TYPE_B stty
7811: WHERE stty.id = crl.object1_id1
7812: AND stty.stream_type_purpose = 'DOWN_PAYMENT'
7822: crl2.object1_id2,
7823: crl2.rule_information2,
7824: NVL(crl2.rule_information3,0) rule_information3,
7825: NVL(crl2.rule_information6,0) rule_information6
7826: FROM OKC_RULES_B crl1, OKC_RULES_B crl2, OKC_RULE_GROUPS_B rgp
7827: WHERE crl1.id = crl2.object2_id1
7828: AND crl1.id = p_id
7829: AND rgp.cle_id = p_cle_id
7830: AND rgp.id = crl1.rgp_id
9550: *
9551: * l_synd_rec l_synd_csr%ROWTYPE;
9552: *
9553: */
9554: CURSOR l_rl_csr( rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
9555: rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
9556: chrId OKC_RULE_GROUPS_B.DNZ_CHR_ID%TYPE ) IS
9557: SELECT crl.RULE_INFORMATION1, crl.RULE_INFORMATION2
9558: FROM OKC_RULE_GROUPS_B crg, OKC_RULES_B crl
9552: *
9553: */
9554: CURSOR l_rl_csr( rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
9555: rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
9556: chrId OKC_RULE_GROUPS_B.DNZ_CHR_ID%TYPE ) IS
9557: SELECT crl.RULE_INFORMATION1, crl.RULE_INFORMATION2
9558: FROM OKC_RULE_GROUPS_B crg, OKC_RULES_B crl
9559: WHERE crl.rgp_id = crg.id
9560: AND crg.RGD_CODE = rgcode
9554: CURSOR l_rl_csr( rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
9555: rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
9556: chrId OKC_RULE_GROUPS_B.DNZ_CHR_ID%TYPE ) IS
9557: SELECT crl.RULE_INFORMATION1, crl.RULE_INFORMATION2
9558: FROM OKC_RULE_GROUPS_B crg, OKC_RULES_B crl
9559: WHERE crl.rgp_id = crg.id
9560: AND crg.RGD_CODE = rgcode
9561: AND crl.RULE_INFORMATION_CATEGORY = rlcat
9562: AND crg.dnz_chr_id = chrId;
10375: l_count NUMBER := 0;
10376: l_row_notfound BOOLEAN;
10377: l_token VARCHAR2(2000);
10378:
10379: CURSOR l_rl_csr( rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
10380: rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
10381: chrid OKC_RULE_GROUPS_B.DNZ_CHR_ID%TYPE ) IS
10382: SELECT crl.RULE_INFORMATION1, crl.RULE_INFORMATION2
10383: FROM OKC_RULE_GROUPS_B crg, OKC_RULES_B crl
10377: l_token VARCHAR2(2000);
10378:
10379: CURSOR l_rl_csr( rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
10380: rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
10381: chrid OKC_RULE_GROUPS_B.DNZ_CHR_ID%TYPE ) IS
10382: SELECT crl.RULE_INFORMATION1, crl.RULE_INFORMATION2
10383: FROM OKC_RULE_GROUPS_B crg, OKC_RULES_B crl
10384: WHERE crl.rgp_id = crg.id
10385: AND crg.RGD_CODE = rgcode
10379: CURSOR l_rl_csr( rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
10380: rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
10381: chrid OKC_RULE_GROUPS_B.DNZ_CHR_ID%TYPE ) IS
10382: SELECT crl.RULE_INFORMATION1, crl.RULE_INFORMATION2
10383: FROM OKC_RULE_GROUPS_B crg, OKC_RULES_B crl
10384: WHERE crl.rgp_id = crg.id
10385: AND crg.RGD_CODE = rgcode
10386: AND crl.RULE_INFORMATION_CATEGORY = rlcat
10387: AND crg.dnz_chr_id = chrid;
11190: CURSOR pmnt_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE,
11191: p_line_type OKC_LINE_STYLES_B.LTY_CODE%TYPE) IS
11192: SELECT COUNT(1)
11193: FROM okc_rules_b rule,
11194: okc_rule_groups_b rgp,
11195: okc_k_lines_b line,
11196: okc_line_styles_b style,
11197: okl_strm_type_b strm
11198: WHERE rgp.dnz_chr_id = p_chr_id
11446: AND style.lty_code = 'FREE_FORM1'
11447: AND line.sts_code = sts.code
11448: AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
11449:
11450: CURSOR tax_rule_csr (rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
11451: rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
11452: chrId NUMBER,
11453: cleId NUMBER) IS
11454: SELECT crl.id slh_id,
11460: crl.RULE_INFORMATION5,
11461: crl.RULE_INFORMATION6,
11462: crl.RULE_INFORMATION7,
11463: crl.RULE_INFORMATION10
11464: FROM OKC_RULE_GROUPS_B crg,
11465: OKC_RULES_B crl
11466: WHERE crl.rgp_id = crg.id
11467: AND crg.RGD_CODE = rgcode
11468: AND crl.RULE_INFORMATION_CATEGORY = rlcat
11992: --Bug#3877032
11993: CURSOR l_invoice_format_csr IS
11994: SELECT rule.rule_information1
11995: FROM okc_rules_b rule,
11996: okc_rule_groups_b rgp
11997: WHERE rule.rgp_id = rgp.id
11998: AND rgp.dnz_chr_id = p_chr_id
11999: AND rgp.chr_id = p_chr_id
12000: AND rgp.rgd_code = 'LABILL'
12254: SELECT styt.name stream_type,
12255: rule.id rule_id,
12256: rgp.id rgp_id
12257: FROM okc_rules_b rule,
12258: okc_rule_groups_b rgp,
12259: okl_strm_type_b sty,
12260: okl_strm_type_tl styt
12261: WHERE NVL(rgp.cle_id, -1) = p_kle_id
12262: AND rgp.dnz_chr_id = p_khr_id
12267: AND styt.LANGUAGE = USERENV('LANG')
12268: AND sty.id = styt.id;
12269:
12270: CURSOR l_strm_sll_csr (p_rule_id OKC_RULES_B.ID%TYPE,
12271: p_rgp_id OKC_RULE_GROUPS_B.ID%TYPE) IS
12272: SELECT Fnd_Date.canonical_to_date(sll.rule_information2) start_date,
12273: sll.rule_information1 seq,
12274: sll.rule_information6 amt,
12275: sll.rule_information7 stub_day,
13941: rgp_rbk.id rbk_rgp_id,
13942: slh_orig.id orig_slh_id,
13943: rgp_orig.id orig_rgp_id
13944: FROM okc_rules_b slh_rbk,
13945: okc_rule_groups_b rgp_rbk,
13946: okc_rules_b slh_orig,
13947: okc_rule_groups_b rgp_orig
13948: WHERE rgp_rbk.dnz_chr_id = p_rbk_chr_id
13949: AND rgp_rbk.cle_id = p_rbk_cle_id
13943: rgp_orig.id orig_rgp_id
13944: FROM okc_rules_b slh_rbk,
13945: okc_rule_groups_b rgp_rbk,
13946: okc_rules_b slh_orig,
13947: okc_rule_groups_b rgp_orig
13948: WHERE rgp_rbk.dnz_chr_id = p_rbk_chr_id
13949: AND rgp_rbk.cle_id = p_rbk_cle_id
13950: AND rgp_rbk.rgd_code = 'LALEVL'
13951: AND slh_rbk.rgp_id = rgp_rbk.id