The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT (NVL(term_duration,0) * 30) term_days
FROM okl_k_headers
WHERE id = p_contract_id;
SELECT Fnd_Date.canonical_to_date(rulb2.RULE_INFORMATION2) start_date,
rulb2.RULE_INFORMATION3 level_periods,
rulb2.RULE_INFORMATION7 stub_days,
rulb2.RULE_INFORMATION8 stub_amount,
rulb2.object1_id1 frequency
FROM okc_rule_groups_b rgpb,
okc_rules_b rulb,
okc_rules_b rulb2,
okl_strm_type_b styb
WHERE rgpb.chr_id = p_contract_id
AND rgpb.dnz_chr_id = p_contract_id
AND rgpb.rgd_code = 'LALEVL'
AND rulb.rgp_id = rgpb.id
AND rulb.rule_information_category = 'LASLH'
AND TO_CHAR(styb.id) = rulb.object1_id1
AND rulb2.object2_id1 = TO_CHAR(rulb.id)
AND rulb2.rgp_id = rgpb.id
AND rulb2.rule_information_category = 'LASLL'
AND styb.STREAM_TYPE_PURPOSE = 'VARIABLE_INTEREST_SCHEDULE'
ORDER BY start_date;
SELECT NVL(SUM(TO_NUMBER(rulb2.RULE_INFORMATION3) * DECODE(rulb2.object1_id1,'M',1,'Q',3,'S',6,'A',12) * 30),0)
FROM okc_rule_groups_b rgpb,
okc_rules_b rulb,
okc_rules_b rulb2,
okl_strm_type_b styb
WHERE rgpb.chr_id = p_contract_id
AND rgpb.dnz_chr_id = p_contract_id
AND rgpb.rgd_code = 'LALEVL'
AND rulb.rgp_id = rgpb.id
AND rulb.rule_information_category = 'LASLH'
AND TO_CHAR(styb.id) = rulb.object1_id1
AND rulb2.object2_id1 = TO_CHAR(rulb.id)
AND rulb2.rgp_id = rgpb.id
AND rulb2.rule_information_category = 'LASLL'
AND rulb2.RULE_INFORMATION3 IS NOT NULL
AND styb.STREAM_TYPE_PURPOSE = 'VARIABLE_INTEREST_SCHEDULE';
SELECT NVL(SUM(TO_NUMBER(rulb2.RULE_INFORMATION7)),0)
FROM okc_rule_groups_b rgpb,
okc_rules_b rulb,
okc_rules_b rulb2,
okl_strm_type_b styb
WHERE rgpb.chr_id = p_contract_id
AND rgpb.dnz_chr_id = p_contract_id
AND rgpb.rgd_code = 'LALEVL'
AND rulb.rgp_id = rgpb.id
AND rulb.rule_information_category = 'LASLH'
AND TO_CHAR(styb.id) = rulb.object1_id1
AND rulb2.object2_id1 = TO_CHAR(rulb.id)
AND rulb2.rgp_id = rgpb.id
AND rulb2.rule_information_category = 'LASLL'
AND rulb2.RULE_INFORMATION7 IS NOT NULL
AND styb.STREAM_TYPE_PURPOSE = 'VARIABLE_INTEREST_SCHEDULE';
SELECT nvl(SUM(nvl(sel_prin_pmt.amount, 0)),0) pmt_amt
FROM okl_strm_type_v sty_prin_pmt,
okl_streams_v stm_prin_pmt,
okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.khr_id = cp_khr_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date BETWEEN cp_start_date AND cp_due_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose = cp_stream_type_purpose ; -- 'PRINCIPAL_PAYMENT'
OKL_VARIABLE_INTEREST_PVT.g_vir_tbl.delete;
OKL_VARIABLE_INTEREST_PVT.g_vpb_tbl.delete;
OKL_VR_DATA_HANDLER_PVT.INSERT_VIR_PARAMS(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_TRUE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vir_tbl => OKL_VARIABLE_INTEREST_PVT.g_vir_tbl);
OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to INSERT_VIR_PARAMS');
OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to INSERT_VIR_PARAMS');
OKL_VR_DATA_HANDLER_PVT.INSERT_PRINCIPAL_BAL_TXN(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vpb_tbl => OKL_VARIABLE_INTEREST_PVT.g_vpb_tbl);
OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to INSERT_PRINCIPAL_BAL_TXN');
OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to INSERT_PRINCIPAL_BAL_TXN');
OKL_VARIABLE_INTEREST_PVT.g_vpb_tbl.delete;
SELECT nvl(SUM(nvl(sel_prin_pmt.amount, 0)),0) pmt_amt
FROM okl_strm_type_v sty_prin_pmt,
okl_streams_v stm_prin_pmt,
okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.khr_id = cp_khr_id
AND stm_prin_pmt.kle_id = cp_kle_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date BETWEEN cp_start_date AND cp_due_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose = cp_stream_type_purpose ; -- 'PRINCIPAL_PAYMENT'
SELECT nvl(SUM(nvl(sel_prin_pmt.amount, 0)),0) pmt_amt
FROM okl_strm_type_v sty_prin_pmt,
okl_streams_v stm_prin_pmt,
okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.khr_id = cp_khr_id
AND stm_prin_pmt.kle_id = cp_kle_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date BETWEEN cp_start_date AND cp_due_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sel_prin_pmt.sel_id IS NULL
AND sty_prin_pmt.stream_type_purpose = 'VARIABLE_LOAN_PAYMENT';
SELECT id
FROM okl_k_lines_full_v
WHERE chr_id = cp_khr_id
AND lse_id = OKL_VARIABLE_INTEREST_PVT.G_FIN_LINE_LTY_ID
AND nvl(date_terminated, cp_due_date + 1) > cp_due_date
ORDER BY id;
SELECT sel.id,
sel.stream_element_date,
sel.amount
FROM okl_strm_type_v sty,
okl_streams str,
okl_strm_elements sel
WHERE sel.stm_id = str.id
AND str.khr_id = cp_khr_id
AND str.kle_id = cp_kle_id
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND sel.stream_element_date <= cp_due_date
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
AND NOT EXISTS (
SELECT 'X'
FROM okl_strm_elements selc
WHERE selc.sel_id = sel.id)
ORDER BY stream_element_date;
SELECT nvl(SUM(nvl(sel_prin_pmt.amount, 0)),0) pmt_amt
FROM okl_strm_type_v sty_prin_pmt,
okl_streams_v stm_prin_pmt,
okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.khr_id = cp_khr_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date = cp_due_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose = cp_stream_type_purpose ; -- 'PRINCIPAL_PAYMENT'
SELECT cle.id
FROM okc_k_lines_b cle,
okc_statuses_b sts
WHERE cle.lse_id = OKL_VARIABLE_INTEREST_PVT.G_FIN_LINE_LTY_ID
AND cle.dnz_chr_id = p_contract_id
AND cle.chr_id = p_contract_id
AND cle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'CANCELLED');
OKL_VARIABLE_INTEREST_PVT.g_vpb_tbl.delete;
OKL_VARIABLE_INTEREST_PVT.g_vir_tbl.delete;
OKL_VR_DATA_HANDLER_PVT.INSERT_VIR_PARAMS(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_TRUE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vir_tbl => OKL_VARIABLE_INTEREST_PVT.g_vir_tbl);
OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to INSERT_VIR_PARAMS');
OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to INSERT_VIR_PARAMS');
OKL_VR_DATA_HANDLER_PVT.INSERT_PRINCIPAL_BAL_TXN(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vpb_tbl => OKL_VARIABLE_INTEREST_PVT.g_vpb_tbl);
OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to INSERT_PRINCIPAL_BAL_TXN');
OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to INSERT_PRINCIPAL_BAL_TXN');
OKL_VARIABLE_INTEREST_PVT.g_vpb_tbl.delete;
SELECT NVL(rul2.rule_information10,'N') arrears_yn
FROM okc_rule_groups_b rgp,
okc_rules_b rul1,
okc_rules_b rul2,
okl_strm_type_b sty
WHERE rul2.dnz_chr_id = rgp.dnz_chr_id
AND rul2.rule_information_category = 'LASLL'
AND rul2.rgp_id = rgp.id
AND rgp.cle_id IS NULL
AND rgp.chr_id = khrid
AND rgp.dnz_chr_id = khrid
AND rgp.rgd_code = 'LALEVL'
AND rul1.dnz_chr_id = rgp.dnz_chr_id
AND rul1.rule_information_category = 'LASLH'
AND rul1.rgp_id = rgp.id
AND TO_NUMBER(rul2.object2_id1) = rul1.id
AND TO_NUMBER(rul1.object1_id1) = sty.id
AND sty.stream_type_purpose = 'VARIABLE_INTEREST_SCHEDULE'
ORDER BY FND_DATE.canonical_to_date(rul2.rule_information2);
OKL_VR_DATA_HANDLER_PVT.UPDATE_LAST_INT_CAL_DATE(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_TRUE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_contract_id => OKL_VARIABLE_INTEREST_PVT.G_CONTRACT_ID,
p_last_interest_cal_date => l_due_date);
OKL_VARIABLE_INTEREST_PVT.print_error_message('Unexpected error raised in call to UPDATE_LAST_INT_CAL_DATE');
OKL_VARIABLE_INTEREST_PVT.print_error_message('Error raised in call to UPDATE_LAST_INT_CAL_DATE');