[Home] [Help]
942: okl_K_lines_full_v kle,
943: okc_statuses_b sts,
944: okl_strm_elements sel,
945: okl_streams stm,
946: okl_strm_type_b sty
947: WHERE kle.dnz_chr_id = p_chr_id
948: AND kle.id = p_kle_id
949: AND kle.sts_code = sts.code
950: AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD')
963: Cursor l_chr_rents_csr (p_chr_id NUMBER, p_kle_id NUMBER, p_date DATE, p_advance_or_arrears VARCHAR2) IS
964: SELECT NVL(SUM(sele.amount),0) amount
965: FROM okl_strm_elements sele,
966: okl_streams str,
967: okl_strm_type_b sty
968: WHERE sele.stm_id = str.id
969: AND str.sty_id = sty.id
970: AND sty.stream_type_purpose = 'RENT'
971: AND str.say_code = 'CURR'
1043: okl_K_lines_full_v kle,
1044: okc_statuses_b sts,
1045: okl_strm_elements sel,
1046: okl_streams stm,
1047: okl_strm_type_b sty
1048: WHERE kle.dnz_chr_id = p_chr_id
1049: AND kle.id = p_kle_id
1050: AND kle.sts_code = sts.code
1051: AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD')
1251: SELECT NVL(SUM(AR_REC.AMOUNT_APPLIED),0) AMOUNT
1252: FROM AR_RECEIVABLE_APPLICATIONS_ALL AR_REC,
1253: AR_PAYMENT_SCHEDULES_ALL AR_PAY,
1254: okl_bpd_tld_ar_lines_v TLD,
1255: OKL_STRM_TYPE_B STRM
1256: WHERE AR_REC.APPLY_DATE <= p_date -- TO BE REPLACED WITH APPLY DATE
1257: AND TLD.KHR_ID = p_chr_id --298074492502791815515340871034511143040
1258: AND TLD.STY_ID = STRM.ID
1259: AND STRM.STREAM_TYPE_PURPOSE = 'PRINCIPAL_PAYMENT'
1506: c_khr_type_rec c_khr_type%ROWTYPE;
1507:
1508: /* CURSOR c_principal_bal_cur(p_contract_id NUMBER, p_bal_date DATE) IS
1509: SELECT sum(sel.amount) amount
1510: FROM okl_strm_elements sel,okl_streams stm, okl_strm_type_b sty
1511: WHERE stm.khr_id = p_contract_id
1512: AND stm.active_yn = 'Y'
1513: AND stm.say_code = 'CURR'
1514: AND sty.id = stm.sty_id
1515: AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
1516: AND sel.stm_id = stm.id
1517: and trunc(sel.stream_element_date) = ( select trunc(max(sel.stream_element_date))
1518: FROM okl_strm_elements sel,okl_streams stm,
1519: okl_strm_type_b sty
1520: WHERE stm.khr_id = p_contract_id
1521: AND stm.active_yn = 'Y'
1522: AND stm.say_code = 'CURR'
1523: AND sty.id = stm.sty_id
1527:
1528: CURSOR c_stream_date_arr(p_contract_id NUMBER, p_bal_date DATE) IS
1529: select trunc(min(sel.stream_element_date))
1530: FROM okl_strm_elements sel,okl_streams stm,
1531: okl_strm_type_b sty
1532: WHERE stm.khr_id = p_contract_id
1533: AND stm.active_yn = 'Y'
1534: AND stm.say_code = 'CURR'
1535: AND sty.id = stm.sty_id
1910: AND val.datetime_valid < p_end_date);
1911:
1912: CURSOR c_calc_start_date (c_contract_id NUMBER)IS
1913: select min(sel.stream_element_date)
1914: FROM okl_strm_elements sel,okl_streams stm, okl_strm_type_b sty,
1915: okl_k_headers khr, okc_k_headers_b chr
1916: WHERE stm.khr_id = c_contract_id
1917: AND stm.active_yn = 'Y'
1918: AND stm.say_code = 'CURR'
2341: (Select distinct rgp.dnz_chr_id khr_id
2342: ,sll.object1_id1 Pay_freq
2343: from okc_rules_b sll,
2344: okc_rules_b slh,
2345: okl_strm_type_b styp,
2346: okc_rule_groups_b rgp
2347: where to_number(sll.object2_id1) = slh.id
2348: and sll.rule_information_category = 'LASLL'
2349: and sll.dnz_chr_id = rgp.dnz_chr_id
4281: okl_trx_ar_invoices_b tai,
4282: ar_payment_schedules_all aps,
4283: ar_receivable_applications_all raa,
4284: ar_cash_receipts_all cra,
4285: okl_strm_type_b sty,
4286: ar_distributions_all ad
4287: WHERE tai.trx_status_code = 'PROCESSED'
4288: AND tai.khr_id = p_contract_id
4289: AND tld.khr_id = p_contract_id
4312: okl_trx_ar_invoices_b tai,
4313: ar_payment_schedules_all aps,
4314: ar_receivable_applications_all raa,
4315: ar_cash_receipts_all cra,
4316: okl_strm_type_b sty
4317: WHERE tai.trx_status_code = 'PROCESSED'
4318: AND tai.khr_id = p_contract_id
4319: AND tld.khr_id = p_contract_id
4320: AND ractrl.customer_trx_id = aps.customer_trx_id
5313:
5314: CURSOR c_int_calc_date_csr(cp_khr_id NUMBER) IS SELECT (max(sel.stream_element_date) - 1) last_interest_calc_date
5315: FROM okl_streams stm,
5316: okl_strm_elements sel,
5317: okl_strm_type_b sty
5318: WHERE stm.khr_id = cp_khr_id
5319: AND stm.id = sel.stm_id
5320: AND stm.sty_id = sty.id
5321: AND (sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL' OR sty.stream_type_purpose = 'DAILY_INTEREST_INTEREST');
5444: CURSOR c_pay_freq(cp_khr_id NUMBER) IS
5445: select sll_rulb.object1_id1 pay_freq
5446: from okc_rules_b sll_rulb,
5447: okc_rules_b slh_rulb,
5448: okl_strm_type_b styb,
5449: okc_rule_groups_b rgpb
5450: where sll_rulb.rgp_id = rgpb.id
5451: and sll_rulb.rule_information_category = 'LASLL'
5452: and sll_rulb.dnz_chr_id = rgpb.dnz_chr_id
7795: --prior to date passed
7796: CURSOR l_daily_int_strm_csr(cp_khr_id NUMBER, cp_from_date DATE) IS
7797: SELECT 'Y' return_value
7798: FROM okl_streams stm,
7799: okl_strm_type_b sty,
7800: okl_strm_elements sel
7801: WHERE stm.khr_id = cp_khr_id
7802: AND stm.sty_id = sty.id
7803: AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
7813: --prior to date passed
7814: CURSOR l_prin_catch_strm_csr(cp_khr_id NUMBER, cp_from_date DATE) IS
7815: SELECT 'Y' return_value
7816: FROM okl_streams stm,
7817: okl_strm_type_b sty,
7818: okl_strm_elements sel
7819: WHERE stm.khr_id = cp_khr_id
7820: AND stm.sty_id = sty.id
7821: AND sty.stream_type_purpose = 'PRINCIPAL_CATCHUP'
7840: ,okl_trx_ar_invoices_b tai
7841: ,ar_payment_schedules_all aps
7842: ,ar_receivable_applications_all raa
7843: ,ar_cash_receipts_all cra
7844: ,okl_strm_type_b sty
7845: WHERE tai.trx_status_code = 'PROCESSED'
7846: AND tai.khr_id = cp_khr_id
7847: AND tld.khr_id = cp_khr_id
7848: AND ractrl.customer_trx_id = aps.customer_trx_id
8084: CURSOR l_daily_int_strm_csr(cp_khr_id NUMBER, cp_from_date DATE, cp_to_date DATE) IS
8085: SELECT sel.id source_id
8086: , 'OKL_STRM_ELEMENTS_V' source_table
8087: FROM okl_streams stm,
8088: okl_strm_type_b sty,
8089: okl_strm_elements sel
8090: WHERE stm.khr_id = cp_khr_id
8091: AND stm.sty_id = sty.id
8092: AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
8103: CURSOR l_prin_catch_strm_csr(cp_khr_id NUMBER, cp_from_date DATE, cp_to_date DATE) IS
8104: SELECT sel.id source_id
8105: , 'OKL_STRM_ELEMENTS_V' source_table
8106: FROM okl_streams stm,
8107: okl_strm_type_b sty,
8108: okl_strm_elements sel
8109: WHERE stm.khr_id = cp_khr_id
8110: AND stm.sty_id = sty.id
8111: AND sty.stream_type_purpose = 'PRINCIPAL_CATCHUP'
8133: ,okl_trx_ar_invoices_b tai
8134: ,ar_payment_schedules_all aps
8135: ,ar_receivable_applications_all raa
8136: ,ar_cash_receipts_all cra
8137: ,okl_strm_type_b sty
8138: WHERE tai.trx_status_code = 'PROCESSED'
8139: AND tai.khr_id = cp_khr_id
8140: AND tld.khr_id = cp_khr_id
8141: AND ractrl.customer_trx_id = aps.customer_trx_id
8950: slh_rulb.rule_information1 billing_schedule_type,
8951: slh_rulb.rule_information2 rate_type
8952: from okc_rules_b sll_rulb,
8953: okc_rules_b slh_rulb,
8954: okl_strm_type_b styb,
8955: okc_rule_groups_b rgpb
8956: where sll_rulb.rgp_id = rgpb.id
8957: and sll_rulb.rule_information_category = 'LASLL'
8958: and sll_rulb.dnz_chr_id = rgpb.dnz_chr_id
10269: /*CURSOR c_terms_cur (p_contract_id NUMBER, p_reamort_date DATE) IS
10270: SELECT max(sel.stream_element_date)
10271: FROM okl_strm_elements sel,
10272: okl_streams stm,
10273: okl_strm_type_b sty,
10274: okc_k_headers_b khr
10275: WHERE stm.khr_id = p_contract_id
10276: AND stm.active_yn = 'Y'
10277: AND stm.say_code = 'CURR'
11883: Cursor asset_billed_streams_csr (p_contract_id NUMBER, p_from_date DATE, p_to_date DATE) IS
11884: SELECT chrb.id contract_id, cleb.id kle_id,
11885: selb.stream_element_date, selb.amount amount
11886: FROM okc_k_headers_b chrb, okc_k_lines_b cleb,
11887: okc_line_styles_b lseb, okl_strm_type_b styb,
11888: okl_strm_elements selb, okl_streams stmb
11889: WHERE cleb.dnz_chr_id = chrb.id
11890: AND cleb.chr_id = chrb.id
11891: AND chrb.id = p_contract_id